package DAO.impl;

import DAO.JDBCUtils;
import DAO.UniversityDAO;
import model.University;

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

public class UniversityDAOImpl implements UniversityDAO {
    @Override
    public List<University> getAll() throws SQLException {
        final String SELECT_ALL_UNIVERSITY = "SELECT * FROM university;";

        List<University> universityList = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_UNIVERSITY)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String location = rs.getString("location");
                Boolean IsPrivate = rs.getBoolean("IsPrivate");

                System.out.println(id + ", " + name + ", " + location + ", " + IsPrivate);

                University university = new University(id, name, location, IsPrivate);
                universityList.add(university);
            }
        } catch (SQLException e) {
            System.err.println("Error updating Faculty: " + e.getMessage());
            throw e;
            //JDBCUtils.printSQLException(e);
        }
        return universityList;
    }

    @Override
    public List<University> getUniversitiesInSkopje() throws SQLException {
        final String SELECT_IN_SKOPJE = "SELECT * FROM University WHERE location = 'Skopje, MK';";
        List<University> universities = new ArrayList<>();

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_IN_SKOPJE)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String location = rs.getString("location");
                Boolean IsPrivate = rs.getBoolean("IsPrivate");

                universities.add(new University(id, name, location, IsPrivate));
            }
        } catch (SQLException e) {
            System.err.println("Error fetching universities: " + e.getMessage());
            throw e;
        }
        return universities;
    }

    @Override
    public void update(University university) throws SQLException {
        final String UPDATE_UNIVERSITY_SQL = "UPDATE university SET name = ?, location = ?, IsPrivate = ? WHERE id = ?;";

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_UNIVERSITY_SQL)) {
            preparedStatement.setString(1, university.getName());
            preparedStatement.setString(2, university.getLocation());
            preparedStatement.setBoolean(3, university.getIsPrivate());            preparedStatement.setLong(4, university.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            JDBCUtils.printSQLException(e);
        }
    }


    @Override
    public University save(University university) throws SQLException {
        final String INSERT_UNIVERSITY_SQL = "INSERT INTO university (name, location, IsPrivate) VALUES (?, ?, ?);";
        University savedUniversity = null;

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_UNIVERSITY_SQL, PreparedStatement.RETURN_GENERATED_KEYS)) {

            preparedStatement.setString(1, university.getName());
            preparedStatement.setString(2, university.getLocation());
            preparedStatement.setBoolean(3, university.getIsPrivate());
            int affectedRows = preparedStatement.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating university failed, no rows affected.");
            }

            try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    long generatedId = generatedKeys.getLong(1);
                    savedUniversity = new University(generatedId, university.getName(), university.getLocation(), university.getIsPrivate());
                } else {
                    throw new SQLException("Creating university failed, no ID obtained.");
                }
            }
        } catch (SQLException e) {
            System.err.println("Error saving University: " + e.getMessage());
            throw e;
        }
        return savedUniversity;
    }

    @Override
    public University getByID(Long id) throws SQLException {
        final String SELECT_BY_ID = "SELECT * FROM university WHERE id = ?;";
        University university = 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");
                Boolean isPrivate = rs.getBoolean("IsPrivate");
                university = new University(id, name, location, isPrivate);
                System.out.println("Fetched: " + university);
            } else {
                System.out.println("No University found with ID " + id);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return university;
    }


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

        try (Connection connection = JDBCUtils.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_UNIVERSITY_SQL)) {
            preparedStatement.setLong(1, id);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("University with ID " + id + " was deleted successfully!");
            } else {
                System.out.println("No University found with ID " + id);
            }
        } catch (SQLException e) {
            System.err.println("Error updating University: " + e.getMessage());
            throw e;
            //JDBCUtils.printSQLException(e);
        }
    }
}