package edu.pdx.cs.joy.jdbc;

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

/**
 * Data Access Object implementation for managing Course entities in the database.
 * Demonstrates basic JDBC operations: CREATE, READ, UPDATE.
 */
public class CourseDAOImpl implements CourseDAO {

  private final Connection connection;

  /**
   * Creates a new CourseDAOImpl with the specified database connection.
   *
   * @param connection the database connection to use
   */
  public CourseDAOImpl(Connection connection) {
    this.connection = connection;
  }

  /**
   * Drops the courses table from the database if it exists.
   *
   * @param connection the database connection to use
   * @throws SQLException if a database error occurs
   */
  public static void dropTable(Connection connection) throws SQLException {
    try (Statement statement = connection.createStatement()) {
      statement.execute("DROP TABLE IF EXISTS courses");
    }
  }

  /**
   * Creates the courses table in the database.
   *
   * @param connection the database connection to use
   * @throws SQLException if a database error occurs
   */
  public static void createTable(Connection connection) throws SQLException {
    try (Statement statement = connection.createStatement()) {
      statement.execute(
        "CREATE TABLE courses (" +
        "  id IDENTITY PRIMARY KEY," +
        "  title VARCHAR(255) NOT NULL," +
        "  department_id INTEGER NOT NULL," +
        "  credits INTEGER NOT NULL," +
        "  FOREIGN KEY (department_id) REFERENCES departments(id)" +
        ")"
      );
    }
  }

  /**
   * Saves a course to the database.
   * The course's ID will be automatically generated by the database and set on the object.
   *
   * @param course the course to save
   * @throws SQLException if a database error occurs
   */
  @Override
  public void save(Course course) throws SQLException {
    String sql = "INSERT INTO courses (title, department_id, credits) VALUES (?, ?, ?)";

    try (PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
      statement.setString(1, course.getTitle());
      statement.setInt(2, course.getDepartmentId());
      statement.setInt(3, course.getCredits());
      statement.executeUpdate();

      // Retrieve the auto-generated ID and set it on the course object
      try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
        if (generatedKeys.next()) {
          int generatedId = generatedKeys.getInt(1);
          course.setId(generatedId);
        } else {
          throw new SQLException("Creating course failed, no ID obtained.");
        }
      }
    }
  }

  /**
   * Finds a course by its title.
   *
   * @param title the title to search for
   * @return the course with the given title, or null if not found
   * @throws SQLException if a database error occurs
   */
  @Override
  public Course findByTitle(String title) throws SQLException {
    String sql = "SELECT id, title, department_id, credits FROM courses WHERE title = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, title);

      try (ResultSet resultSet = statement.executeQuery()) {
        if (resultSet.next()) {
          return extractCourseFromResultSet(resultSet);
        }
      }
    }

    return null;
  }

  /**
   * Finds all courses associated with a specific department.
   *
   * @param departmentId the department ID to search for
   * @return a list of courses in the department
   * @throws SQLException if a database error occurs
   */
  @Override
  public List<Course> findByDepartmentId(int departmentId) throws SQLException {
    List<Course> courses = new ArrayList<>();
    String sql = "SELECT id, title, department_id, credits FROM courses WHERE department_id = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setInt(1, departmentId);

      try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
          courses.add(extractCourseFromResultSet(resultSet));
        }
      }
    }

    return courses;
  }

  /**
   * Extracts a Course object from the current row of a ResultSet.
   *
   * @param resultSet the result set positioned at a course row
   * @return a Course object with data from the result set
   * @throws SQLException if a database error occurs
   */
  private Course extractCourseFromResultSet(ResultSet resultSet) throws SQLException {
    int id = resultSet.getInt("id");
    String title = resultSet.getString("title");
    int departmentId = resultSet.getInt("department_id");
    int credits = resultSet.getInt("credits");

    Course course = new Course(title, departmentId, credits);
    course.setId(id);
    return course;
  }

  /**
   * Updates an existing course in the database.
   * Uses the course's ID to identify which record to update.
   *
   * @param course the course to update
   * @throws SQLException if a database error occurs
   */
  @Override
  public void update(Course course) throws SQLException {
    String sql = "UPDATE courses SET title = ?, department_id = ?, credits = ? WHERE id = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, course.getTitle());
      statement.setInt(2, course.getDepartmentId());
      statement.setInt(3, course.getCredits());
      statement.setInt(4, course.getId());

      int rowsAffected = statement.executeUpdate();
      if (rowsAffected == 0) {
        throw new SQLException("Update failed, no course found with ID: " + course.getId());
      }
    }
  }
}