package DAO.impl;

import DAO.JDBCUtils;
import DAO.SubjectDAO;
import model.Subject;

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

public class SubjectDAOImpl implements SubjectDAO {

    @Override
    public List<Subject> getAll() throws SQLException {
        final String SELECT_ALL = "SELECT id, name, semester, credits FROM Subject";
        List<Subject> subjectList = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL);
             ResultSet rs = preparedStatement.executeQuery()) {

            while (rs.next()) {
                subjectList.add(mapRowToSubject(rs));
            }
        } catch (SQLException e) {
            System.err.println("Error fetching all Subjects: " + e.getMessage());
            throw e;
        }
        return subjectList;
    }

    @Override
    public List<Subject> getSubjectsByProfessorId(Long professorId) throws SQLException {
        final String QUERY = "SELECT id, name, semester, credits FROM Subject WHERE professorid = ?";
        List<Subject> subjectList = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(QUERY)) {

            ps.setLong(1, professorId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    subjectList.add(mapRowToSubject(rs));
                }
            }
        }
        return subjectList;
    }

    @Override
    public Subject getByID(Long id) throws SQLException {
        final String QUERY = "SELECT id, name, semester, credits FROM Subject WHERE id = ?";
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(QUERY)) {

            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return mapRowToSubject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public void update(Subject subject) throws SQLException {
        final String UPDATE_SQL = "UPDATE Subject SET name = ?, semester = ?, credits = ?, facultyid = ? WHERE id = ?";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(UPDATE_SQL)) {

            ps.setString(1, subject.getName());
            // UPDATED: Now uses setInt because both model and DB are int
            ps.setInt(2, subject.getSemester());
            ps.setInt(3, subject.getCredits());
            ps.setLong(4, subject.getFacultyid());
            ps.setLong(5, subject.getId());

            ps.executeUpdate();
        }
    }

    @Override
    public void save(Subject subject) throws SQLException {
        final String INSERT_SQL = "INSERT INTO Subject (name, semester, credits, facultyid) VALUES (?, ?, ?, ?)";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(INSERT_SQL)) {

            ps.setString(1, subject.getName());
            ps.setInt(2, subject.getSemester());
            ps.setInt(3, subject.getCredits());
            ps.setLong(4, subject.getFacultyid());

            ps.executeUpdate();
        }
    }

    @Override
    public void delete(Long id) throws SQLException {
        final String DELETE_SQL = "DELETE FROM Subject WHERE id = ?";
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(DELETE_SQL)) {
            ps.setLong(1, id);
            ps.executeUpdate();
        }
    }

    private Subject mapRowToSubject(ResultSet rs) throws SQLException {
        Long id = rs.getLong("id");
        String name = rs.getString("name");
        // UPDATED: Read as int from ResultSet
        int semester = rs.getInt("semester");
        int credits = rs.getInt("credits");

        // Returns new Subject(Long, String, int, int, Object)
        return new Subject(id, name, semester, credits, null);
    }

    @Override
    public List<Subject> getPassedSubjectsByStudentId(Long studentId) throws SQLException {
        final String QUERY = "SELECT sub.id, sub.name, sub.semester, sub.credits " +
                "FROM Subject sub " +
                "JOIN student_subject ss ON sub.id = ss.subject_id " +
                "WHERE ss.student_id = ?";

        List<Subject> subjects = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(QUERY)) {

            ps.setLong(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    subjects.add(mapRowToSubject(rs));
                }
            }
        }
        return subjects;
    }
}