package com.repository.impl;

import com.entity.Task;
import com.repository.TaskRepository;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * Implementation for the task repository.
 *
 * @author Laurentiu Marcut
 */
public class TaskRepositoryImpl extends BaseRepositoryImpl implements TaskRepository {

    private static final String SELECT = "select * from task where id = ? ";
    private static final String SELECT_ALL = "select * from task where project = ? order by priority asc, created_date asc ";
    private static final String SELECT_ALL_WITHOUT_SPRINT = "SELECT * FROM task WHERE project = ? AND sprint IS NULL and state in ('To do', 'In progress', 'Blocked') ORDER BY priority ASC, created_date ASC ";
    private String SELECT_ALL_BY_TAGS = "SELECT DISTINCT t.id, t.project, t.priority, t.type, t.title, t.state, t.points, t.description, " +
                                                "t.sprint, t.created_by, t.assigned_to, t.created_date, t.update_date " +
                                                "FROM task t INNER JOIN task_tags tg ON t.id = tg.task " +
                                                "WHERE t.project = ? AND tg.tag in (list) ORDER BY t.priority ASC, t.created_date ASC ";
    private static final String SELECT_ALL_TAGS = "select * from task_tags where task = ? order by created_date asc ";
    private static final String INSERT = "insert into task (project,  priority, type, title, state, points, description, created_by, assigned_to) " +
                                                    "values (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
    private static final String DELETE = "delete from task where id = ? ";
    private static final String INSERT_TAG = "insert into task_tags (task, tag) values (?, ?) ";
    private static final String DELETE_TAG = "delete from task_tags where task = ? and tag = ? ";
    private static final String INCREASE_PRIORITIES = "update task set priority = priority + 1 where project = ? and priority >= ? and priority < ? ";
    private static final String DECREASE_PRIORITIES = "update task set priority = priority - 1 where project = ? and priority > ? and priority <= ? ";
    private static final String DECREASE_PRIORITIES_BEFORE_DELETE_TASK = "update task set priority = priority - 1 where project = ? and priority > ? ";
    private static final String UPDATE_PRIORITY = "update task set priority = ? where id = ? ";
    private static final String UPDATE_POINTS = "update task set points = ? where id = ? ";
    private static final String UPDATE_ASSIGNED_TO = "update task set assigned_to = ? where id = ? ";
    private static final String UPDATE_STATE = "update task set state = ?, close_date = ? where id = ? ";
    private static final String UPDATE_SPRINT = "update task set sprint = ? where id = ? ";
    private static final String SELECT_TOTAL_PRIORITIES = "select max(priority) as total_priority from task where project = ? ";

    @Override
    public Task select(int task) throws SQLException {
        Task taskObj = new Task();

        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.SELECT)) {
            st.setInt(1, task);

            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    taskObj = this.getTaskFromResultSet(rs);
                }
            }
        }

        return taskObj;
    }

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

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

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    Task task = this.getTaskFromResultSet(rs);
                    tasks.add(task);
                }
            }
        }

        return tasks;
    }

    @Override
    public List<Task> selectAllWithoutSprint(String project) throws SQLException {
        List<Task> tasks = new ArrayList<>();

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

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    Task task = this.getTaskFromResultSet(rs);
                    tasks.add(task);
                }
            }
        }

        return tasks;
    }


    @Override
    public List<Task> selectAllByTags(String project, List<String> tags) throws SQLException {
        List<Task> tasks = new ArrayList<>();
        String query = new String(SELECT_ALL_BY_TAGS);
        String subQuery = "";

        for (String tag : tags) {
            subQuery += "?,";
        }

        subQuery = subQuery.substring(0, subQuery.length()-1);
        query = StringUtils.replace(query, "list", subQuery);

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

            for (int i=0; i<tags.size(); i++) {
                st.setString(i+2, tags.get(i));
            }

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    Task task = this.getTaskFromResultSet(rs);
                    tasks.add(task);
                }
            }
        }

        return tasks;
    }

    private Task getTaskFromResultSet(ResultSet rs) throws SQLException {
        Task task = new Task();
        task.setId(rs.getInt("id"));
        task.setProject(rs.getString("project"));
        task.setType(rs.getString("type"));
        task.setTitle(rs.getString("title"));
        task.setState(rs.getString("state"));
        task.setPoints(rs.getInt("points"));
        task.setDescription(rs.getString("description"));
        task.setSprint(rs.getInt("sprint"));
        task.setCreatedBy(rs.getString("created_by"));
        task.setAssignedTo(rs.getString("assigned_to"));
        task.setCreatedDate(rs.getDate("created_date"));
        task.setUpdateDate(rs.getDate("update_date"));

        return task;
    }

    @Override
    public List<String> selectAllTags(int task) throws SQLException {
        List<String> tags = new ArrayList<>();

        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.SELECT_ALL_TAGS)) {
            st.setInt(1, task);

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    tags.add(rs.getString("tag"));
                }
            }
        }

        return tags;
    }

    @Override
    public int insert(Task task) throws SQLException {
        int taskId;

        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.INSERT, Statement.RETURN_GENERATED_KEYS)) {
            st.setString(1, task.getProject());
            st.setInt(2, task.getPriority());
            st.setString(3, task.getType());
            st.setString(4, task.getTitle());
            st.setString(5, task.getState());
            st.setInt(6, task.getPoints());
            st.setString(7, task.getDescription());
            st.setString(8, task.getCreatedBy());
            st.setString(9, task.getAssignedTo());

            int affectedRows = st.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Fali insertinf task. ");
            }

            try (ResultSet rs = st.getGeneratedKeys()) {
                if (rs.next()) {
                    taskId = rs.getInt(1);
                } else {
                    throw new SQLException("No id task found. ");
                }
            }
        }

        return taskId;
    }

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

            st.executeUpdate();
        }
    }


    @Override
    public void insertTag(int task, String tag) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.INSERT_TAG)) {
            st.setInt(1, task);
            st.setString(2, tag);

            st.executeUpdate();
        }
    }

    @Override
    public void deleteTag(int task, String tag) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.DELETE_TAG)) {
            st.setInt(1, task);
            st.setString(2, tag);

            st.executeUpdate();
        }
    }

    @Override
    public void increasePriorities(String project, int previousPriority, int currentPriority) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.INCREASE_PRIORITIES)) {
            st.setString(1, project);
            st.setInt(2, currentPriority);
            st.setInt(3, previousPriority);

            st.executeUpdate();
        }
    }

    @Override
    public void decreasePriorities(String project, int previousPriority, int currentPriority) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.DECREASE_PRIORITIES)) {
            st.setString(1, project);
            st.setInt(2, previousPriority);
            st.setInt(3, currentPriority);

            st.executeUpdate();
        }
    }

    @Override
    public void decreasePrioritiesBeforeDeleteTask(String project, int priority)throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.DECREASE_PRIORITIES_BEFORE_DELETE_TASK)) {
            st.setString(1, project);
            st.setInt(2, priority);

            st.executeUpdate();
        }
    }

    @Override
    public void updatePriority(int task, int currentPriority) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.UPDATE_PRIORITY)) {
            st.setInt(1, currentPriority);
            st.setInt(2, task);

            st.executeUpdate();
        }
    }

    @Override
    public void updatePoints(int task, int points) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.UPDATE_POINTS)) {
            st.setInt(1, points);
            st.setInt(2, task);

            st.executeUpdate();
        }
    }

    @Override
    public void updateAssignedTo(int task, String assignedTo) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.UPDATE_ASSIGNED_TO)) {
            if (StringUtils.isEmpty(assignedTo)) {
                st.setNull(1, Types.VARCHAR);
            } else {
                st.setString(1, assignedTo);
            }

            st.setInt(2, task);

            st.executeUpdate();
        }
    }

    @Override
    public void updateState(int task, String state) throws SQLException {
        Date closeDate = "Done".equals(state) ? new Date(Calendar.getInstance().getTimeInMillis()) : null;

        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.UPDATE_STATE)) {
            st.setString(1, state);
            st.setDate(2, closeDate);
            st.setInt(3, task);

            st.executeUpdate();
        }
    }

    @Override
    public void updateSprint(int task, int sprint) throws SQLException {
        try (PreparedStatement st = super.getConnection().prepareStatement(TaskRepositoryImpl.UPDATE_SPRINT)) {
            if (sprint == 0) {
                st.setNull(1, Types.INTEGER);
            } else {
                st.setInt(1, sprint);
            }

            st.setInt(2, task);

            st.executeUpdate();
        }
    }


    @Override
    public int selectTotalPriorities(String project) throws SQLException {
        int totalPriorities = 0;

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

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

        return totalPriorities;
    }

}
