package DAO.impl;

import DAO.JDBCUtils;
import DAO.AdviceDAO;
import model.Advice;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AdviceDAOImpl implements AdviceDAO {

    @Override
    public void save(Advice advice) throws SQLException {
        final String INSERT = "INSERT INTO advice (student_id, professor_id, start_date, end_date) VALUES (?, ?, ?, ?)";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(INSERT)) {
            ps.setInt(1, advice.getstudent_id());
            ps.setInt(2, advice.getprofessor_id());
            ps.setDate(3, advice.getstart_date());
            ps.setDate(4, advice.getend_date());
            ps.executeUpdate();
        }
    }

    @Override
    public void update(Advice advice) throws SQLException {
        final String UPDATE = "UPDATE advice SET start_date = ?, end_date = ? WHERE student_id = ? AND professor_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(UPDATE)) {
            ps.setDate(1, advice.getstart_date());
            ps.setDate(2, advice.getend_date());
            ps.setInt(3, advice.getstudent_id());
            ps.setInt(4, advice.getprofessor_id());
            ps.executeUpdate();
        }
    }

    @Override
    public void delete(int studentId, int professorId) throws SQLException {
        final String DELETE = "DELETE FROM advice WHERE student_id = ? AND professor_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(DELETE)) {
            ps.setInt(1, studentId);
            ps.setInt(2, professorId);
            ps.executeUpdate();
        }
    }

    @Override
    public Advice getById(int studentId, int professorId) throws SQLException {
        final String SELECT_BY_ID = "SELECT * FROM advice WHERE student_id = ? AND professor_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SELECT_BY_ID)) {
            ps.setInt(1, studentId);
            ps.setInt(2, professorId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new Advice(
                            rs.getInt("student_id"),
                            rs.getInt("professor_id"),
                            rs.getDate("start_date"),
                            rs.getDate("end_date")
                    );
                }
            }
        }
        return null;
    }

    @Override
    public List<Advice> getAll() throws SQLException {
        List<Advice> list = new ArrayList<>();
        final String SELECT_ALL = "SELECT * FROM advice";
        try (Connection conn = JDBCUtils.getConnection();
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(SELECT_ALL)) {
            while (rs.next()) {
                list.add(new Advice(
                        rs.getInt("student_id"),
                        rs.getInt("professor_id"),
                        rs.getDate("start_date"),
                        rs.getDate("end_date")
                ));
            }
        }
        return list;
    }
}