<?php
require_once __DIR__ . '/../../../core/Database.php';

class Subcategory extends Database {

    public function createSubcategory($subcategory_name, $category_id) {
        $sql = "INSERT INTO subcategories (subcategory_name, category_id) VALUES (?, ?)";
        return $this->executeNonQuery($sql, [$subcategory_name, $category_id]);
    }

    public function getSubcategories() {
        $sql = "SELECT s.*, c.category_name FROM subcategories s JOIN categories c ON s.category_id = c.category_id ORDER BY s.subcategory_name";
        return $this->executeQuery($sql);
    }

    public function getSubcategoriesByCategoryId($category_id) {
        $sql = "SELECT * FROM subcategories WHERE category_id = ? ORDER BY subcategory_name";
        return $this->executeQuery($sql, [$category_id]);
    }

    public function getSubcategory($subcategory_id) {
        $sql = "SELECT * FROM subcategories WHERE subcategory_id = ?";
        return $this->executeQuerySingle($sql, [$subcategory_id]);
    }

    public function updateSubcategory($subcategory_id, $subcategory_name, $category_id) {
        $sql = "UPDATE subcategories SET subcategory_name = ?, category_id = ? WHERE subcategory_id = ?";
        return $this->executeNonQuery($sql, [$subcategory_name, $category_id, $subcategory_id]);
    }

    public function deleteSubcategory($subcategory_id) {
        require_once __DIR__ . '/../../client/classes/Proposal.php';
        $proposal = new Proposal();
        $proposals = $proposal->getProposalsBySubcategoryId($subcategory_id);

        foreach ($proposals as $prop) {
            $proposal->deleteProposal($prop['proposal_id']);
        }

        $sql = "DELETE FROM subcategories WHERE subcategory_id = ?";
        return $this->executeNonQuery($sql, [$subcategory_id]);
    }
}
?>