package DAO.impl;

import DAO.FacultyDAO;
import DAO.JDBCUtils;
import model.Faculty;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

public class FacultyDAOImpl implements FacultyDAO {

    @Override
    public List<Faculty> getAll() throws SQLException {

        final String SELECT_ALL_Faculty = "SELECT * FROM Faculty;";

        List<Faculty> facultyList = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_Faculty)) {
            ResultSet rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Long id = rs.getLong("id");
                String name = rs.getString("name");
                String location = rs.getString("location");
                String study_field= rs.getString("study_field");
                Long university_id = rs.getLong("university_id");

                Faculty faculty = new Faculty(id, name, location, study_field,university_id);
                facultyList.add(faculty);
                System.out.println(id + ", " + name + ", " + location + ", " + study_field);
            }
        } catch (SQLException e) {
            System.err.println("Error updating Faculty: " + e.getMessage());
            throw e;
        }

        return facultyList;
    }

    @Override
    public Faculty getByID(Long id) throws SQLException {
        final String SELECT_BY_ID = "SELECT * FROM Faculty WHERE id = ?;";
                Faculty faculty = null;
        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BY_ID)) {
            preparedStatement.setLong(1, id);
            ResultSet rs = preparedStatement.executeQuery();
            if(rs.next()) {
                String name = rs.getString("name");
                String location = rs.getString("location");
                String study_field= rs.getString("study_field");
                Long university_id = rs.getLong("university_id");

                System.out.println(id + ", " + name + ", " + location + ", " + study_field);
                faculty = new Faculty(id, name, location, study_field, university_id);
            }
            else {
                System.out.println("No Faculty found with ID " + id);
            }
        }
        catch (SQLException e) {
            System.err.println("Error updating Faculty: " + e.getMessage());
            throw e;
            //JDBCUtils.printSQLException(e);
        }

        return faculty;
    }

    @Override
    public void update(Faculty faculty) throws SQLException {

        final String UPDATE_Faculty_SQL = "UPDATE Faculty SET name = ?, location = ?, study_field = ?::study_field_enum, university_id = ? WHERE id = ?";


        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_Faculty_SQL)) {
            preparedStatement.setString(1, faculty.getName());
            preparedStatement.setString(2, faculty.getLocation());
            preparedStatement.setString(3, faculty.getStudy_field());
            preparedStatement.setLong(4, faculty.getUniversityId());
            preparedStatement.setLong(5, faculty.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Error updating Faculty: " + e.getMessage());
            throw e;
        }
    }

    @Override
    public void save(Faculty faculty) throws SQLException {
        final String INSERT_Faculty_SQL = "INSERT INTO faculty (name, location, study_field, university_id) VALUES (?, ?, ?::study_field_enum, ?)";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_Faculty_SQL)) {
            preparedStatement.setString(1, faculty.getName());
            preparedStatement.setString(2, faculty.getLocation());
            preparedStatement.setString(3, faculty.getStudy_field());
            preparedStatement.setLong(4, faculty.getUniversityId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Error inserting Faculty: " + e.getMessage());
            throw e;
        }
    }



    @Override
    public void delete(Long id) throws SQLException {
        final String DELETE_PROFS = "DELETE FROM professor WHERE facultyid = ?;";
        final String DELETE_FACULTY = "DELETE FROM Faculty WHERE id = ?;";

        try (Connection connection = JDBCUtils.getConnection()) {
            connection.setAutoCommit(false);

            try (PreparedStatement st1 = connection.prepareStatement(DELETE_PROFS);
                 PreparedStatement st2 = connection.prepareStatement(DELETE_FACULTY)) {

                st1.setLong(1, id);
                st1.executeUpdate();

                st2.setLong(1, id);
                int rowsAffected = st2.executeUpdate();

                connection.commit();

                if (rowsAffected > 0) {
                    System.out.println("Faculty with ID " + id + " was deleted successfully!");
                }
            } catch (SQLException e) {
                connection.rollback();
                throw e;
            }
        }
    }

}