package DAO.impl;

import DAO.JDBCUtils;
import DAO.ProfessorDAO;
import model.Professor;
import model.Student;
import model.University;

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

public class ProfessorDAOImpl implements ProfessorDAO {

    public ProfessorDAOImpl() {
    }

    @Override
    public List<Professor> getall() throws SQLException {
        // We use a LEFT JOIN to fetch the affiliated university information if it exists
        final String SELECT_ALL = "SELECT p.*, a.university_id FROM Professor p " +
                "LEFT JOIN affiliated a ON p.id = a.professor_id";
        List<Professor> professorList = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                professorList.add(mapRowToProfessor(rs));
            }
        } catch (SQLException e) {
            System.err.println("Error fetching all professors: " + e.getMessage());
            throw e;
        }
        return professorList;
    }

    @Override
    public List<Professor> getAllSortedById() throws SQLException {
        final String SELECT_SORTED = "SELECT p.*, a.university_id FROM Professor p " +
                "LEFT JOIN affiliated a ON p.id = a.professor_id ORDER BY p.id ASC";
        List<Professor> professors = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_SORTED)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                professors.add(mapRowToProfessor(rs));
            }
        } catch (SQLException e) {
            System.err.println("Error fetching sorted professors: " + e.getMessage());
            throw e;
        }
        return professors;
    }

    @Override
    public Professor getByID(Long id) throws SQLException {
        final String SQL = "SELECT p.*, a.university_id FROM Professor p " +
                "LEFT JOIN affiliated a ON p.id = a.professor_id WHERE p.id = ?";
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(SQL)) {
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return mapRowToProfessor(rs);
            }
            return null;
        }
    }

    @Override
    public void update(Professor professor) throws SQLException {
        final String UPDATE_PROF = "UPDATE Professor SET name = ?, surname = ?, age = ?, facultyid = ? WHERE id = ?";
        final String DELETE_AFFILIATION = "DELETE FROM affiliated WHERE professor_id = ?";
        final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";

        try (Connection conn = JDBCUtils.getConnection()) {
            conn.setAutoCommit(false);
            try {
                try (PreparedStatement ps = conn.prepareStatement(UPDATE_PROF)) {
                    ps.setString(1, professor.getName());
                    ps.setString(2, professor.getSurname());
                    ps.setInt(3, professor.getAge());
                    ps.setLong(4, professor.getFacultyid());
                    ps.setLong(5, professor.getId());
                    ps.executeUpdate();
                }

                try (PreparedStatement psDel = conn.prepareStatement(DELETE_AFFILIATION)) {
                    psDel.setLong(1, professor.getId());
                    psDel.executeUpdate();
                }

                // CRITICAL FIX: Check if university AND its ID are not null
                if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
                    try (PreparedStatement psIns = conn.prepareStatement(INSERT_AFFILIATION)) {
                        psIns.setLong(1, professor.getUniversity().getId());
                        psIns.setLong(2, professor.getId());
                        psIns.executeUpdate();
                    }
                }
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }

    @Override
    public void save(Professor professor) throws SQLException {
        final String INSERT_PROF = "INSERT INTO Professor (name, surname, age, facultyid) VALUES (?, ?, ?, ?)";
        final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";

        try (Connection conn = JDBCUtils.getConnection()) {
            conn.setAutoCommit(false);
            try (PreparedStatement ps = conn.prepareStatement(INSERT_PROF, Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, professor.getName());
                ps.setString(2, professor.getSurname());
                ps.setInt(3, professor.getAge());
                ps.setLong(4, professor.getFacultyid());
                ps.executeUpdate();

                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    long newProfId = rs.getLong(1);

                    // CRITICAL FIX: Check if university AND its ID are not null
                    if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
                        try (PreparedStatement psAff = conn.prepareStatement(INSERT_AFFILIATION)) {
                            psAff.setLong(1, professor.getUniversity().getId());
                            psAff.setLong(2, newProfId);
                            psAff.executeUpdate();
                        }
                    }
                }
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }

    @Override
    public void delete(Long id) {
        // ON DELETE CASCADE in DB handles the affiliated table automatically
        String sql = "DELETE FROM Professor WHERE id = ?";
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setLong(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Error deleting professor: " + e.getMessage());
        }
    }

    private Professor mapRowToProfessor(ResultSet rs) throws SQLException {
        Professor p = new Professor(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getString("surname"),
                rs.getInt("age"),
                rs.getLong("facultyid"),
                null // Initialize with null University, set below
        );

        long univId = rs.getLong("university_id");
        if (!rs.wasNull()) {
            University u = new University();
            u.setId(univId);
            p.setUniversity(u);
        }
        return p;
    }

    @Override
    public Map<String, Double> findAverageAgeByFaculty() {
        String query = "SELECT f.name as faculty_name, AVG(p.age) as average_age " +
                "FROM professor p JOIN faculty f ON f.id = p.facultyid " +
                "GROUP BY f.name";
        Map<String, Double> result = new HashMap<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement stmt = connection.prepareStatement(query)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                result.put(rs.getString("faculty_name"), rs.getDouble("average_age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Student> findStudentsByUniversityId(int universityId) {
        String query = "SELECT s.id, s.name, s.surname, s.location, s.studentindex, s.facultyid " +
                "FROM student s " +
                "JOIN faculty f ON s.facultyid = f.id " +
                "JOIN university u ON f.university_id = u.id " +
                "WHERE u.id = ?";
        List<Student> students = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setInt(1, universityId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                students.add(new Student(
                        rs.getLong("id"), rs.getString("name"), rs.getString("surname"),
                        rs.getString("location"), rs.getInt("studentindex"), rs.getLong("facultyid")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return students;
    }

    @Override
    public List<Student> findStudentsByFacultyId(int facultyId) {
        final String QUERY = "SELECT id, name, surname, location, studentindex, facultyid FROM student WHERE facultyid = ?";
        List<Student> students = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(QUERY)) {
            stmt.setInt(1, facultyId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                students.add(new Student(
                        rs.getLong("id"), rs.getString("name"), rs.getString("surname"),
                        rs.getString("location"), rs.getInt("studentindex"), rs.getLong("facultyid")
                ));
            }
        } catch (SQLException e) {
            System.err.println("Error in findStudentsByFacultyId: " + e.getMessage());
        }
        return students;
    }

    @Override
    public Map<String, Integer> findProfessorCountBySubject() {
        String query = "SELECT s.name AS subject_name, COUNT(p.id) AS professor_count " +
                "FROM subject s LEFT JOIN professor p ON s.professorid = p.id GROUP BY s.name";
        Map<String, Integer> result = new HashMap<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement stmt = connection.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                result.put(rs.getString("subject_name"), rs.getInt("professor_count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Map<Integer, Double> findCompletedSemesterGPA() {
        String query = "SELECT s.studentindex, AVG(ss.final_grade) AS semester_gpa " +
                "FROM student s " +
                "JOIN student_subject ss ON s.id = ss.student_id " +
                "JOIN subject sub ON ss.subject_id = sub.id " +
                "WHERE sub.semester = 2 " +
                "  AND ss.status = 'Completed' " +
                "  AND ss.final_grade IS NOT NULL " +
                "GROUP BY s.id, s.studentindex, s.name, s.surname " +
                "HAVING COUNT(ss.subject_id) = ( " +
                "    SELECT COUNT(*) " +
                "    FROM student_subject ss2 " +
                "    WHERE ss2.student_id = s.id " +
                ") " +
                "ORDER BY semester_gpa DESC";

        Map<Integer, Double> result = new HashMap<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement stmt = connection.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                result.put(rs.getInt("studentindex"), rs.getDouble("semester_gpa"));
            }
        } catch (SQLException e) {
            System.err.println("Error in findCompletedSemesterGPA: " + e.getMessage());
            e.printStackTrace();
        }
        return result;
    }
    @Override
    public List<Map<String, Object>> findTopPerformersByEnrollmentYear() {
        String query = "WITH StudentGPA AS ( " +
                "    SELECT s.studentindex, s.name || ' ' || s.surname AS student_name, " +
                "           EXTRACT(YEAR FROM ss.enrollment_date) AS enrollment_year, " +
                "           AVG(ss.final_grade) AS cumulative_gpa, " +
                "           RANK() OVER (PARTITION BY EXTRACT(YEAR FROM ss.enrollment_date) ORDER BY AVG(ss.final_grade) DESC) as gpa_rank " +
                "    FROM student s " +
                "    JOIN student_subject ss ON s.id = ss.student_id " +
                "    WHERE ss.final_grade IS NOT NULL " +
                "    GROUP BY s.studentindex, s.name, s.surname, EXTRACT(YEAR FROM ss.enrollment_date) " +
                ") " +
                "SELECT enrollment_year, studentindex, student_name, cumulative_gpa " +
                "FROM StudentGPA " +
                "WHERE gpa_rank = 1 " +
                "ORDER BY enrollment_year DESC";

        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement stmt = connection.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("enrollment_year", rs.getInt("enrollment_year"));
                row.put("student_index", rs.getInt("studentindex"));
                row.put("student_name", rs.getString("student_name"));
                row.put("cumulative_gpa", rs.getDouble("cumulative_gpa"));
                resultList.add(row);
            }
        } catch (SQLException e) {
            System.err.println("Error in findTopPerformersByEnrollmentYear: " + e.getMessage());
            e.printStackTrace();
        }
        return resultList;
    }
}