package DAO.impl;

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

public class StudentSubjectDAOImpl {

    public void save(StudentSubject ss) throws SQLException {
        final String SQL = "INSERT INTO student_subject (student_id, subject_id, enrollment_date, status, final_grade, absences_count) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, ss.getStudent_id());
            ps.setLong(2, ss.getSubject_id());
            ps.setDate(3, Date.valueOf(ss.getEnrollment_date()));
            ps.setString(4, ss.getStatus());
            ps.setObject(5, ss.getFinal_grade()); // Handles null grade automatically
            ps.setInt(6, ss.getAbsences_count());
            ps.executeUpdate();
        }
    }

    public List<StudentSubject> getAll() throws SQLException {
        List<StudentSubject> list = new ArrayList<>();
        final String SQL = "SELECT * FROM student_subject";
        try (Connection conn = JDBCUtils.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(SQL)) {
            while (rs.next()) {
                list.add(mapRow(rs));
            }
        }
        return list;
    }

    public StudentSubject getById(Long ss_id) throws SQLException {
        final String SQL = "SELECT * FROM student_subject WHERE ss_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, ss_id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) return mapRow(rs);
        }
        return null;
    }

    public void update(StudentSubject ss) throws SQLException {
        final String SQL = "UPDATE student_subject SET status = ?, final_grade = ?, absences_count = ? WHERE ss_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setString(1, ss.getStatus());
            ps.setObject(2, ss.getFinal_grade());
            ps.setInt(3, ss.getAbsences_count());
            ps.setLong(4, ss.getSs_id());
            ps.executeUpdate();
        }
    }

    public void delete(Long ss_id) throws SQLException {
        final String SQL = "DELETE FROM student_subject WHERE ss_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, ss_id);
            ps.executeUpdate();
        }
    }

    private StudentSubject mapRow(ResultSet rs) throws SQLException {
        return new StudentSubject(
                rs.getLong("ss_id"),
                rs.getLong("student_id"),
                rs.getLong("subject_id"),
                rs.getDate("enrollment_date").toLocalDate(),
                rs.getString("status"),
                (Integer) rs.getObject("final_grade"),
                rs.getInt("absences_count")
        );
    }
}