package com.repository.impl;

import com.entity.Sprint;
import com.entity.Task;
import com.repository.SprintsRepository;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * Implementation for the sprint repository.
 *
 * @author Laurentiu Marcut
 */
public class SprintsRepositoryImpl extends BaseRepositoryImpl implements SprintsRepository {

    private static final String SELECT = "select * from sprint s left join task t on t.sprint = s.id where s.project = ? and s.id = ? ";
    private static final String SELECT_ALL = "SELECT * FROM sprint s " +
                                             "LEFT JOIN task t ON t.sprint = s.id " +
                                             "WHERE s.project = ? ORDER BY s.id ASC, t.priority ASC, t.created_date ASC ";
    private static final String SELECT_ALL_IDS = "select distinct s.id from sprint s inner join task t on s.id = t.sprint and s.project = t.project " +
                                                                "where s.project = ? and s.state = 'C' ORDER BY s.end_date desc ";
    private static final String SELECT_TOTAL_SPRINTS = "select max(id) as total from sprint where project = ? ";
    private static final String INSERT = "insert into sprint(id, project) values(? ,?) ";
    private static final String DELETE = "delete from sprint where id = ? and project = ? ";
    private static final String UPDATE_START_DATE = "update sprint set start_date = ? where id = ? and project = ? ";
    private static final String UPDATE_END_DATE = "update sprint set end_date = ? where id = ? and project = ? ";
    private static final String UPDATE_TOTAL_CAPACITY = "update sprint set total_capacity = ? where id = ? and project = ? ";
    private static final String UPDATE_MEMBERS = "update sprint set members = ? where id = ? and project = ? ";
    private static final String UPDATE_STATE = "update sprint set state = ? where id = ? and project = ? ";

    @Override
    public Sprint select(String project, int id) throws SQLException {
        Sprint sprint = new Sprint();

        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.SELECT)) {
            st.setString(1, project);
            st.setInt(2, id);

            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    sprint.setId(rs.getInt("s.id"));
                    sprint.setProject(rs.getString("s.project"));
                    sprint.setCreatedBy(rs.getString("s.created_by"));
                    sprint.setStartDate(rs.getDate("s.start_date"));
                    sprint.setEndDate(rs.getDate("s.end_date"));
                    sprint.setTotalCapacity(rs.getInt("s.total_capacity"));
                    sprint.setMembers(rs.getInt("s.members"));
                    sprint.setState(rs.getString("s.state"));

                    List<Task> tasks = new ArrayList<>();
                    Task task = new Task();
                    task.setId(rs.getInt("t.id"));
                    task.setProject(rs.getString("t.project"));
                    task.setType(rs.getString("t.type"));
                    task.setTitle(rs.getString("t.title"));
                    task.setState(rs.getString("t.state"));
                    task.setPoints(rs.getInt("t.points"));
                    task.setDescription(rs.getString("t.description"));
                    task.setSprint(rs.getInt("t.sprint"));
                    task.setCreatedBy(rs.getString("t.created_by"));
                    task.setAssignedTo(rs.getString("t.assigned_to"));
                    task.setCreatedDate(rs.getDate("t.created_date"));
                    task.setUpdateDate(rs.getDate("t.update_date"));
                    task.setCloseDate(rs.getDate("t.close_date"));
                    tasks.add(task);

                    while (rs.next()) {
                        task = new Task();
                        task.setId(rs.getInt("t.id"));
                        task.setProject(rs.getString("t.project"));
                        task.setType(rs.getString("t.type"));
                        task.setTitle(rs.getString("t.title"));
                        task.setState(rs.getString("t.state"));
                        task.setPoints(rs.getInt("t.points"));
                        task.setDescription(rs.getString("t.description"));
                        task.setSprint(rs.getInt("t.sprint"));
                        task.setCreatedBy(rs.getString("t.created_by"));
                        task.setAssignedTo(rs.getString("t.assigned_to"));
                        task.setCreatedDate(rs.getDate("t.created_date"));
                        task.setUpdateDate(rs.getDate("t.update_date"));
                        task.setCloseDate(rs.getDate("t.close_date"));
                        tasks.add(task);
                    }

                    sprint.setTasks(tasks);
                }
            }
        }

        return sprint;
    }

    @Override
    public List<Sprint> selectAll(String project) throws SQLException {
        List<Sprint> sprints = new ArrayList<>();

        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.SELECT_ALL)) {
            st.setString(1, project);

            try (ResultSet rs = st.executeQuery()) {
                Sprint sprint = new Sprint();

                while (rs.next()) {
                    if (sprint.getId() == rs.getInt("s.id") && rs.getInt("t.id") > 0) {
                        Task task = new Task();
                        task.setId(rs.getInt("t.id"));
                        task.setProject(rs.getString("t.project"));
                        task.setType(rs.getString("t.type"));
                        task.setTitle(rs.getString("t.title"));
                        task.setState(rs.getString("t.state"));
                        task.setPoints(rs.getInt("t.points"));
                        task.setDescription(rs.getString("t.description"));
                        task.setSprint(rs.getInt("t.sprint"));
                        task.setCreatedBy(rs.getString("t.created_by"));
                        task.setAssignedTo(rs.getString("t.assigned_to"));
                        task.setCreatedDate(rs.getDate("t.created_date"));
                        task.setUpdateDate(rs.getDate("t.update_date"));

                        sprints.get(sprints.size()-1).getTasks().add(task);
                    } else {
                        sprint = new Sprint();
                        sprint.setId(rs.getInt("s.id"));
                        sprint.setProject(rs.getString("s.project"));
                        sprint.setCreatedBy(rs.getString("s.created_by"));
                        sprint.setStartDate(rs.getDate("s.start_date"));
                        sprint.setEndDate(rs.getDate("s.end_date"));
                        sprint.setTotalCapacity(rs.getInt("s.total_capacity"));
                        sprint.setMembers(rs.getInt("s.members"));
                        sprint.setState(rs.getString("s.state"));

                        if (rs.getInt("t.id") > 0) {
                            Task task = new Task();
                            task.setId(rs.getInt("t.id"));
                            task.setProject(rs.getString("t.project"));
                            task.setType(rs.getString("t.type"));
                            task.setTitle(rs.getString("t.title"));
                            task.setState(rs.getString("t.state"));
                            task.setPoints(rs.getInt("t.points"));
                            task.setDescription(rs.getString("t.description"));
                            task.setSprint(rs.getInt("t.sprint"));
                            task.setCreatedBy(rs.getString("t.created_by"));
                            task.setAssignedTo(rs.getString("t.assigned_to"));
                            task.setCreatedDate(rs.getDate("t.created_date"));
                            task.setUpdateDate(rs.getDate("t.update_date"));
                            sprint.getTasks().add(task);
                        }

                        sprints.add(sprint);
                    }
                }
            }
        }

        return sprints;
    }

    @Override
    public List<Integer> selectAllIds(String project) throws SQLException {
        List<Integer> sprints = new ArrayList<>();

        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.SELECT_ALL_IDS)) {
            st.setString(1, project);

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    sprints.add(rs.getInt("s.id"));
                }
            }
        }

        return sprints;
    }

    @Override
    public int selectTotalSprints(String project) throws SQLException {
        int totalSprints = 0;

        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.SELECT_TOTAL_SPRINTS)) {
            st.setString(1, project);

            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    totalSprints = rs.getInt("total");
                }
            }
        }

        return totalSprints;
    }

    @Override
    public void insert(int id, String project) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.INSERT)) {
            st.setInt(1, id);
            st.setString(2, project);

            st.executeUpdate();
        }
    }

    @Override
    public void delete(int id, String project) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.DELETE)) {
            st.setInt(1, id);
            st.setString(2, project);

            st.executeUpdate();
        }
    }

    @Override
    public void updateStartDate(int id, String project, Date startDate) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.UPDATE_START_DATE)) {
            st.setDate(1, startDate);
            st.setInt(2, id);
            st.setString(3, project);

            st.executeUpdate();
        }
    }

    @Override
    public void updateEndDate(int id, String project, Date endDate) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.UPDATE_END_DATE)) {
            st.setDate(1, endDate);
            st.setInt(2, id);
            st.setString(3, project);

            st.executeUpdate();
        }
    }

    @Override
    public void updateTotalCapacity(int id, String project, int totalCapacity) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.UPDATE_TOTAL_CAPACITY)) {
            st.setInt(1, totalCapacity);
            st.setInt(2, id);
            st.setString(3, project);

            st.executeUpdate();
        }
    }

    @Override
    public void updateMembers(int id, String project, int members) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.UPDATE_MEMBERS)) {
            st.setInt(1, members);
            st.setInt(2, id);
            st.setString(3, project);

            st.executeUpdate();
        }
    }

    @Override
    public void updateState(String project, int id, String state) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(SprintsRepositoryImpl.UPDATE_STATE)) {
            st.setString(1, state);
            st.setInt(2, id);
            st.setString(3, project);

            st.executeUpdate();
        }
    }

}
