<?php

class Classifications {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    // --- Data Type Management (dsps_tbl_typedatasource) ---

    /**
     * Adds a new data type.
     *
     * @param string $name_en English name of the data type.
     * @param string|null $name_kh Khmer name of the data type.
     * @param int $reg_by User ID who registered it.
     * @return bool True on success.
     * @throws Exception If a database error occurs or name already exists.
     */
    public function addDataType(string $name_en, ?string $name_kh, int $reg_by): bool {
        $sql = "INSERT INTO dsps_tbl_typedatasource (dspstds_name_en, dspstds_name_kh, dspstds_reg_by)
                VALUES (:name_en, :name_kh, :reg_by)";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':name_en', $name_en);
            $stmt->bindParam(':name_kh', $name_kh);
            $stmt->bindParam(':reg_by', $reg_by);
            return $stmt->execute();
        } catch (PDOException $e) {
            if ($e->getCode() == '23000') { // Integrity constraint violation (duplicate entry)
                throw new Exception("Data Type with this English name already exists.");
            }
            error_log("Error adding data type: " . $e->getMessage());
            throw new Exception("Could not add data type. Please try again later.");
        }
    }

    /**
     * Updates an existing data type.
     *
     * @param int $id ID of the data type to update.
     * @param string $name_en New English name.
     * @param string|null $name_kh New Khmer name.
     * @param int $mod_by User ID who modified it.
     * @return bool True on success.
     * @throws Exception If a database error occurs or name already exists.
     */
    public function updateDataType(int $id, string $name_en, ?string $name_kh, int $mod_by): bool {
        $sql = "UPDATE dsps_tbl_typedatasource
                SET dspstds_name_en = :name_en, dspstds_name_kh = :name_kh,
                    dspstds_mod_datetime = CURRENT_TIMESTAMP, dspstds_reg_by = :mod_by
                WHERE pkdspstds_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':name_en', $name_en);
            $stmt->bindParam(':name_kh', $name_kh);
            $stmt->bindParam(':mod_by', $mod_by);
            $stmt->bindParam(':id', $id);
            return $stmt->execute();
        } catch (PDOException $e) {
            if ($e->getCode() == '23000') {
                throw new Exception("Data Type with this English name already exists.");
            }
            error_log("Error updating data type (ID: $id): " . $e->getMessage());
            throw new Exception("Could not update data type. Please try again later.");
        }
    }

    /**
     * Deletes a data type.
     *
     * @param int $id ID of the data type to delete.
     * @return bool True on success.
     * @throws Exception If a database error occurs or data type is in use.
     */
    public function deleteDataType(int $id): bool {
        // Check if any data sources are using this data type
        $checkSql = "SELECT COUNT(*) FROM dsps_tbl_datasource WHERE fkdspstds_id = :id";
        $stmtCheck = $this->pdo->prepare($checkSql);
        $stmtCheck->bindParam(':id', $id);
        $stmtCheck->execute();
        if ($stmtCheck->fetchColumn() > 0) {
            throw new Exception("Cannot delete Data Type: It is currently used by one or more data sources.");
        }

        $sql = "DELETE FROM dsps_tbl_typedatasource WHERE pkdspstds_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':id', $id);
            return $stmt->execute();
        } catch (PDOException $e) {
            error_log("Error deleting data type (ID: $id): " . $e->getMessage());
            throw new Exception("Could not delete data type. Please try again later.");
        }
    }

    /**
     * Retrieves a single data type by ID.
     *
     * @param int $id ID of the data type.
     * @return array|false Data type data or false if not found.
     * @throws Exception If a database error occurs.
     */
    public function getDataTypeById(int $id) {
        $sql = "SELECT * FROM dsps_tbl_typedatasource WHERE pkdspstds_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':id', $id);
            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching data type by ID ($id): " . $e->getMessage());
            throw new Exception("Could not retrieve data type. Please try again later.");
        }
    }

    /**
     * Retrieves all data types.
     *
     * @return array An array of data type data.
     * @throws Exception If a database error occurs.
     */
    public function getAllDataTypes(): array {
        $sql = "SELECT * FROM dsps_tbl_typedatasource ORDER BY dspstds_name_en ASC";
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching all data types: " . $e->getMessage());
            throw new Exception("Could not retrieve data types. Please try again later.");
        }
    }

    /**
     * Gets the total count of data types.
     *
     * @return int The total number of data types.
     * @throws Exception If a database error occurs.
     */
    public function getTotalDataTypes(): int {
        $sql = "SELECT COUNT(*) FROM dsps_tbl_typedatasource";
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchColumn();
        } catch (PDOException $e) {
            error_log("Error getting total data types count: " . $e->getMessage());
            throw new Exception("Could not retrieve data type count. Please try again later.");
        }
    }

    // --- Category Management (dsps_tbl_dspscategory) ---

    /**
     * Adds a new category.
     *
     * @param string $title_en English title of the category.
     * @param string|null $details Details about the category.
     * @param int $reg_by User ID who registered it.
     * @return bool True on success.
     * @throws Exception If a database error occurs or title already exists.
     */
    public function addCategory(string $title_en, ?string $details, int $reg_by): bool {
        $sql = "INSERT INTO dsps_tbl_dspscategory (dspscate_title_en, dspscate_details, dspscate_reg_by)
                VALUES (:title_en, :details, :reg_by)";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':title_en', $title_en);
            $stmt->bindParam(':details', $details);
            $stmt->bindParam(':reg_by', $reg_by);
            return $stmt->execute();
        } catch (PDOException $e) {
            if ($e->getCode() == '23000') {
                throw new Exception("Category with this English title already exists.");
            }
            error_log("Error adding category: " . $e->getMessage());
            throw new Exception("Could not add category. Please try again later.");
        }
    }

    /**
     * Updates an existing category.
     *
     * @param int $id ID of the category to update.
     * @param string $title_en New English title.
     * @param string|null $details New details.
     * @param int $mod_by User ID who modified it.
     * @return bool True on success.
     * @throws Exception If a database error occurs or title already exists.
     */
    public function updateCategory(int $id, string $title_en, ?string $details, int $mod_by): bool {
        $sql = "UPDATE dsps_tbl_dspscategory
                SET dspscate_title_en = :title_en, dspscate_details = :details,
                    dspscate_mod_datetime = CURRENT_TIMESTAMP, dspscate_reg_by = :mod_by
                WHERE pkdspscate_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':title_en', $title_en);
            $stmt->bindParam(':details', $details);
            $stmt->bindParam(':mod_by', $mod_by);
            $stmt->bindParam(':id', $id);
            return $stmt->execute();
        } catch (PDOException $e) {
            if ($e->getCode() == '23000') {
                throw new Exception("Category with this English title already exists.");
            }
            error_log("Error updating category (ID: $id): " . $e->getMessage());
            throw new Exception("Could not update category. Please try again later.");
        }
    }

    /**
     * Deletes a category.
     *
     * @param int $id ID of the category to delete.
     * @return bool True on success.
     * @throws Exception If a database error occurs or category is in use.
     */
    public function deleteCategory(int $id): bool {
        // Check if any data sources are using this category
        $checkSql = "SELECT COUNT(*) FROM dsps_tbl_datasource WHERE fkdspscate_id = :id";
        $stmtCheck = $this->pdo->prepare($checkSql);
        $stmtCheck->bindParam(':id', $id);
        $stmtCheck->execute();
        if ($stmtCheck->fetchColumn() > 0) {
            throw new Exception("Cannot delete Category: It is currently used by one or more data sources.");
        }

        $sql = "DELETE FROM dsps_tbl_dspscategory WHERE pkdspscate_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':id', $id);
            return $stmt->execute();
        } catch (PDOException $e) {
            error_log("Error deleting category (ID: $id): " . $e->getMessage());
            throw new Exception("Could not delete category. Please try again later.");
        }
    }

    /**
     * Retrieves a single category by ID.
     *
     * @param int $id ID of the category.
     * @return array|false Category data or false if not found.
     * @throws Exception If a database error occurs.
     */
    public function getCategoryById(int $id) {
        $sql = "SELECT * FROM dsps_tbl_dspscategory WHERE pkdspscate_id = :id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':id', $id);
            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching category by ID ($id): " . $e->getMessage());
            throw new Exception("Could not retrieve category. Please try again later.");
        }
    }

    /**
     * Retrieves all categories.
     *
     * @return array An array of category data.
     * @throws Exception If a database error occurs.
     */
    public function getAllCategories(): array {
        $sql = "SELECT * FROM dsps_tbl_dspscategory ORDER BY dspscate_title_en ASC";
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching all categories: " . $e->getMessage());
            throw new Exception("Could not retrieve categories. Please try again later.");
        }
    }

    /**
     * Gets the total count of categories.
     *
     * @return int The total number of categories.
     * @throws Exception If a database error occurs.
     */
    public function getTotalCategories(): int {
        $sql = "SELECT COUNT(*) FROM dsps_tbl_dspscategory";
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchColumn();
        } catch (PDOException $e) {
            error_log("Error getting total categories count: " . $e->getMessage());
            throw new Exception("Could not retrieve category count. Please try again later.");
        }
    }
}