package DAO.impl;

import DAO.JDBCUtils;
import model.ProfessorSubject;
import model.Professor;
import model.Subject;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProfessorSubjectDAOImpl {

    public void assign(Long professor_id, Long subject_id) throws SQLException {
        final String SQL = "INSERT INTO subject_professor (professor_id, subject_id) VALUES (?, ?)";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, professor_id);
            ps.setLong(2, subject_id);
            ps.executeUpdate();
        }
    }

   public void remove(Long professor_id, Long subject_id) throws SQLException {
        final String SQL = "DELETE FROM subject_professor WHERE professor_id = ? AND subject_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, professor_id);
            ps.setLong(2, subject_id);
            ps.executeUpdate();
        }
    }

    public List<Subject> getSubjectsByProfessor(Long professor_id) throws SQLException {
        List<Subject> subjects = new ArrayList<>();
        final String SQL = "SELECT s.* FROM subject s " +
                "JOIN subject_professor ps ON s.id = ps.subject_id " +
                "WHERE ps.professor_id = ?";

        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, professor_id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                subjects.add(new Subject(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getInt("semester"),
                        rs.getInt("credits"),
                        rs.getLong("facultyid")
                ));
            }
        }
        return subjects;
    }

    public List<Professor> getProfessorsBySubject(Long subject_id) throws SQLException {
        List<Professor> professors = new ArrayList<>();
        final String SQL = "SELECT p.* FROM professor p " +
                "JOIN subject_professor ps ON p.id = ps.professor_id " +
                "WHERE ps.subject_id = ?";

        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL)) {
            ps.setLong(1, subject_id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                professors.add(new Professor(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("surname"),
                        rs.getInt("age"),
                        rs.getLong("facultyid"),
                        null
                ));
            }
        }
        return professors;
    }
}