package edu.pdx.cs.joy.jdbc;

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

/**
 * Data Access Object implementation for managing Department entities in the database.
 * Demonstrates JDBC operations with auto-generated keys: CREATE, READ, UPDATE, DELETE.
 */
public class DepartmentDAOImpl implements DepartmentDAO {

  private final Connection connection;

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

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

  /**
   * Creates the departments 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 departments (" +
        "  id IDENTITY PRIMARY KEY," +
        "  name VARCHAR(255) NOT NULL" +
        ")"
      );
    }
  }

  /**
   * Saves a department to the database.
   * The department's ID will be automatically generated by the database.
   *
   * @param department the department to save
   * @throws SQLException if a database error occurs
   */
  @Override
  public void save(Department department) throws SQLException {
    String sql = "INSERT INTO departments (name) VALUES (?)";

    try (PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
      statement.setString(1, department.getName());
      statement.executeUpdate();

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

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

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

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

    return null;
  }

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

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

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

    return null;
  }

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

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

    return departments;
  }

  /**
   * Extracts a Department object from the current row of a ResultSet.
   *
   * @param resultSet the result set positioned at a department row
   * @return a Department object with data from the result set
   * @throws SQLException if a database error occurs
   */
  private Department extractDepartmentFromResultSet(ResultSet resultSet) throws SQLException {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    return new Department(id, name);
  }

  /**
   * Updates an existing department in the database.
   *
   * @param department the department to update
   * @throws SQLException if a database error occurs
   */
  @Override
  public void update(Department department) throws SQLException {
    String sql = "UPDATE departments SET name = ? WHERE id = ?";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, department.getName());
      statement.setInt(2, department.getId());
      int rowsAffected = statement.executeUpdate();

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

  /**
   * Deletes a department from the database by ID.
   *
   * @param id the ID of the department to delete
   * @throws SQLException if a database error occurs
   */
  @Override
  public void delete(int id) throws SQLException {
    String sql = "DELETE FROM departments 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 department found with ID: " + id);
      }
    }
  }
}