package edu.pdx.cs.joy.jdbc;

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

/**
 * Data Access Object implementation for managing AcademicTerm entities in the database.
 * Demonstrates JDBC operations with date fields: CREATE, READ, UPDATE, DELETE.
 */
public class AcademicTermDAOImpl implements AcademicTermDAO {

  private final Connection connection;

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

  /**
   * Drops the academic_terms 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 academic_terms");
    }
  }

  /**
   * Creates the academic_terms table in the database if it does not already exist.
   *
   * @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 IF NOT EXISTS academic_terms (" +
        "  id IDENTITY PRIMARY KEY," +
        "  name VARCHAR(255) NOT NULL," +
        "  start_date DATE NOT NULL," +
        "  end_date DATE NOT NULL" +
        ")"
      );
    }
  }

  /**
   * Saves an academic term to the database.
   * The term's ID will be automatically generated by the database and set on the object.
   *
   * @param term the academic term to save
   * @throws SQLException if a database error occurs
   */
  @Override
  public void save(AcademicTerm term) throws SQLException {
    String sql = "INSERT INTO academic_terms (name, start_date, end_date) VALUES (?, ?, ?)";

    try (PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
      statement.setString(1, term.getName());
      statement.setDate(2, Date.valueOf(term.getStartDate()));
      statement.setDate(3, Date.valueOf(term.getEndDate()));
      statement.executeUpdate();

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

  /**
   * Finds an academic term by its ID.
   *
   * @param id the ID to search for
   * @return the academic term with the given ID, or null if not found
   * @throws SQLException if a database error occurs
   */
  @Override
  public AcademicTerm findById(int id) throws SQLException {
    String sql = "SELECT id, name, start_date, end_date FROM academic_terms WHERE id = ?";

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

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

    return null;
  }

  /**
   * Finds an academic term by its name.
   *
   * @param name the name to search for
   * @return the academic term with the given name, or null if not found
   * @throws SQLException if a database error occurs
   */
  @Override
  public AcademicTerm findByName(String name) throws SQLException {
    String sql = "SELECT id, name, start_date, end_date FROM academic_terms WHERE name = ?";

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

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

    return null;
  }

  /**
   * Finds all academic terms in the database.
   *
   * @return a list of all academic terms
   * @throws SQLException if a database error occurs
   */
  @Override
  public List<AcademicTerm> findAll() throws SQLException {
    List<AcademicTerm> terms = new ArrayList<>();
    String sql = "SELECT id, name, start_date, end_date FROM academic_terms ORDER BY start_date";

    try (Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sql)) {
      while (resultSet.next()) {
        terms.add(extractAcademicTermFromResultSet(resultSet));
      }
    }

    return terms;
  }

  /**
   * Updates an existing academic term in the database.
   * Uses the term's ID to identify which record to update.
   *
   * @param term the academic term to update
   * @throws SQLException if a database error occurs
   */
  @Override
  public void update(AcademicTerm term) throws SQLException {
    String sql = "UPDATE academic_terms SET name = ?, start_date = ?, end_date = ? WHERE id = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, term.getName());
      statement.setDate(2, Date.valueOf(term.getStartDate()));
      statement.setDate(3, Date.valueOf(term.getEndDate()));
      statement.setInt(4, term.getId());

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

  /**
   * Deletes an academic term from the database by ID.
   *
   * @param id the ID of the academic term to delete
   * @throws SQLException if a database error occurs
   */
  @Override
  public void delete(int id) throws SQLException {
    String sql = "DELETE FROM academic_terms WHERE id = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setInt(1, id);
      int rowsAffected = statement.executeUpdate();

      if (rowsAffected == 0) {
        throw new SQLException("Delete failed, no academic term found with ID: " + id);
      }
    }
  }

  /**
   * Extracts an AcademicTerm object from the current row of a ResultSet.
   *
   * @param resultSet the result set positioned at an academic term row
   * @return an AcademicTerm object with data from the result set
   * @throws SQLException if a database error occurs
   */
  private AcademicTerm extractAcademicTermFromResultSet(ResultSet resultSet) throws SQLException {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    Date startDate = resultSet.getDate("start_date");
    Date endDate = resultSet.getDate("end_date");

    AcademicTerm term = new AcademicTerm(name, startDate.toLocalDate(), endDate.toLocalDate());
    term.setId(id);
    return term;
  }
}