<?php
class Category {
    private $conn;
    private $table_name = "categories";

    public $id;
    public $name;
    public $slug;
    public $description;
    public $image_url;
    public $created_at;
    public $updated_at;

    public function __construct($db) {
        $this->conn = $db;
    }

    // Get all categories
    public function getCategories() {
        $query = "SELECT 
                    c.id, c.name, c.slug, c.description, c.image_url,
                    COUNT(p.id) as product_count
                  FROM " . $this->table_name . " c
                  LEFT JOIN products p ON c.id = p.category_id AND p.is_active = 1
                  GROUP BY c.id, c.name, c.slug, c.description, c.image_url
                  ORDER BY c.name";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Get category by slug
    public function getCategoryBySlug($slug) {
        $query = "SELECT 
                    c.id, c.name, c.slug, c.description, c.image_url,
                    COUNT(p.id) as product_count
                  FROM " . $this->table_name . " c
                  LEFT JOIN products p ON c.id = p.category_id AND p.is_active = 1
                  WHERE c.slug = :slug
                  GROUP BY c.id, c.name, c.slug, c.description, c.image_url";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':slug', $slug);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Create category
    public function create() {
        $query = "INSERT INTO " . $this->table_name . "
                  SET name = :name, slug = :slug, description = :description,
                      image_url = :image_url, created_at = NOW(), updated_at = NOW()";

        $stmt = $this->conn->prepare($query);

        // Sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->slug = htmlspecialchars(strip_tags($this->slug));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->image_url = htmlspecialchars(strip_tags($this->image_url));

        // Bind values
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':slug', $this->slug);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':image_url', $this->image_url);

        return $stmt->execute();
    }

    // Update category
    public function update() {
        $query = "UPDATE " . $this->table_name . "
                  SET name = :name, slug = :slug, description = :description,
                      image_url = :image_url, updated_at = NOW()
                  WHERE id = :id";

        $stmt = $this->conn->prepare($query);

        // Sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->slug = htmlspecialchars(strip_tags($this->slug));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->image_url = htmlspecialchars(strip_tags($this->image_url));
        $this->id = htmlspecialchars(strip_tags($this->id));

        // Bind values
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':slug', $this->slug);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':image_url', $this->image_url);
        $stmt->bindParam(':id', $this->id);

        return $stmt->execute();
    }

    // Delete category
    public function delete() {
        // Check if category has products
        $checkQuery = "SELECT COUNT(*) FROM products WHERE category_id = :id AND is_active = 1";
        $checkStmt = $this->conn->prepare($checkQuery);
        $checkStmt->bindParam(':id', $this->id);
        $checkStmt->execute();
        
        if ($checkStmt->fetchColumn() > 0) {
            return false; // Cannot delete category with products
        }

        $query = "DELETE FROM " . $this->table_name . " WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $this->id = htmlspecialchars(strip_tags($this->id));
        $stmt->bindParam(':id', $this->id);
        return $stmt->execute();
    }
}
?>