package com.repository.impl;

import com.entity.Member;
import com.repository.MemberProjectRepository;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * Implementation for the project member repository.
 *
 * @author Laurentiu Marcut
 */
public class MemberProjectRepositoryImpl extends BaseRepositoryImpl implements MemberProjectRepository {

    private static final String SELECT_ALL = "select * from member_project mp INNER JOIN user u on mp.member = u.email where project = ? ORDER BY joined_date ASC ";
    private static final String SELECT = "select * from member_project mp INNER JOIN user u on mp.member = u.email where project = ? and member = ? ";
    private static final String INSERT = "insert into member_project (project, member, role) VALUES (?, ?, ?) ";
    private static final String DELETE = "delete from member_project where project = ? and member = ? ";


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

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

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    Member member = new Member();
                    member.setEmail(rs.getString("email"));
                    member.setFirstName(rs.getString("first_name"));
                    member.setLastName(rs.getString("last_name"));
                    member.setBirthDate(rs.getDate("birth_date"));
                    member.setRole(rs.getString("role"));
                    member.setProject(rs.getString("project"));
                    member.setJoinedDate(rs.getDate("joined_date"));
                    member.setLastOpenDate(rs.getDate("last_open_date"));
                    members.add(member);
                }
            }
        }

        return members;
    }

    @Override
    public Member select(String project, String member) throws SQLException {
        Member memberObj = new Member();

        try (PreparedStatement st = super.getConnection().prepareStatement(MemberProjectRepositoryImpl.SELECT)) {
            st.setString(1, project);
            st.setString(2, member);

            try (ResultSet rs = st.executeQuery()) {
                if (rs.next()) {
                    memberObj.setEmail(rs.getString("email"));
                    memberObj.setFirstName(rs.getString("first_name"));
                    memberObj.setLastName(rs.getString("last_name"));
                    memberObj.setBirthDate(rs.getDate("birth_date"));
                    memberObj.setRole(rs.getString("role"));
                    memberObj.setProject(rs.getString("project"));
                    memberObj.setJoinedDate(rs.getDate("joined_date"));
                    memberObj.setLastOpenDate(rs.getDate("last_open_date"));
                }
            }
        }

        return memberObj;
    }

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

            st.executeUpdate();
        }
    }

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

            st.executeUpdate();
        }
    }
}
