package com.repository.impl;

import com.entity.Project;
import com.repository.ProjectRepository;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * Implementation for the project repository.
 *
 * @author Laurentiu Marcut
 */
public class ProjectRepositoryImpl extends BaseRepositoryImpl implements ProjectRepository {

    private static final String SELECT_ALL = "SELECT * FROM member_project mp INNER JOIN project p ON mp.PROJECT = p.PROJECT INNER JOIN user u on u.EMAIL = p.CREATED_BY " +
                                            "    WHERE mp.MEMBER = ? ORDER BY CREATED_DATE ASC ";
    private static final String INSERT = "insert into project (project, created_by) VALUES (?, ?) ";
    private static final String DELETE = "delete from project where project = ? ";

    @Override
    public List<Project> selectAll(String member) throws SQLException {
        List<Project> projects = new ArrayList<>();
        Project project;

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

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    project = new Project();
                    project.setName(rs.getString("project"));
                    project.setCreatedBy(rs.getString("u.first_name") + " " + rs.getString("u.last_name"));
                    project.setCreatedDate(rs.getDate("created_date"));
                    project.setFinishedDate(rs.getDate("finished_date"));
                    project.setLastOpenDate(rs.getDate("last_open_date"));

                    projects.add(project);
                }
            }
        }

        return projects;
    }

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

            st.executeUpdate();
        }
    }

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

            st.executeUpdate();
        }
    }

}
