package DAO.impl;

import DAO.JDBCUtils;
import DAO.StudentDAO;
import model.Student;
import model.Subject;

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

public class StudentDAOImpl implements StudentDAO {

    @Override
    public List<Student> getAll() throws SQLException {
        final String SELECT_ALL_STUDENT = "SELECT id, name, surname, location, studentindex, facultyid FROM student";

        List<Student> studentList = new ArrayList<>();

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

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String location = rs.getString("location");
                int studentindex = rs.getInt("studentindex");
                long facultyid = rs.getLong("facultyid");

                Student student = new Student(id, name, surname, location, studentindex, facultyid);
                studentList.add(student);
            }
        } catch (SQLException e) {
            System.err.println("Error fetching students: " + e.getMessage());
            throw e;
        }
        return studentList;
    }

    @Override
    public List<Student> getStudentsWithOddLengthNames() throws SQLException {
        final String QUERY = "SELECT id, name, surname, location, studentindex, facultyid FROM student WHERE LENGTH(name) % 2 = 1";
        List<Student> students = new ArrayList<>();

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

            ResultSet rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Long id = rs.getLong("id");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String location = rs.getString("location");
                int studentindex = rs.getInt("studentindex");
                Long facultyid = rs.getObject("facultyid") != null ? rs.getLong("facultyid") : null;

                students.add(new Student(id, name, surname, location, studentindex, facultyid));
            }
        }
        return students;
    }

    @Override
    public Student getStudentById(Long id) throws SQLException {
        final String QUERY = "SELECT id, name, surname, location, studentindex, facultyid FROM student WHERE id = ?";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(QUERY)) {
            preparedStatement.setLong(1, id);
            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String location = rs.getString("location");
                int studentindex = rs.getInt("studentindex");
                Long facultyid = rs.getLong("facultyid");

                return new Student(id, name, surname, location, studentindex, facultyid);
            }
        }
        return null;
    }

    @Override
    public Student getByID(Long id) throws SQLException {
        return getStudentById(id);
    }

    @Override
    public void save(Student student) throws SQLException {
        final String INSERT_SQL = "INSERT INTO Student (name, surname, location, studentindex, facultyid) VALUES (?, ?, ?, ?, ?)";

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

            ps.setString(1, student.getName());
            ps.setString(2, student.getSurname());
            ps.setString(3, student.getLocation());
            ps.setInt(4, student.getStudentindex()); // Matched to your model
            ps.setLong(5, student.getFacultyid());    // Matched to your model
            ps.executeUpdate();

            ResultSet generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                student.setId(generatedKeys.getLong(1));
            }
        }
    }

    @Override
    public void update(Student student) throws SQLException {
        final String UPDATE_SQL = "UPDATE Student SET name = ?, surname = ?, location = ?, studentindex = ?, facultyid = ? WHERE id = ?";

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

            ps.setString(1, student.getName());
            ps.setString(2, student.getSurname());
            ps.setString(3, student.getLocation());
            ps.setInt(4, student.getStudentindex());
            ps.setLong(5, student.getFacultyid());
            ps.setLong(6, student.getId());
            ps.executeUpdate();
        }
    }

    @Override
    public void delete(Long id) throws SQLException {
        final String DELETE_STUDENT_SQL = "DELETE FROM Student WHERE id = ?";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement ps = connection.prepareStatement(DELETE_STUDENT_SQL)) {
            ps.setLong(1, id);
            ps.executeUpdate();
        }
    }
}