<?php
/**
 * Category Model
 * Maps to `categories` table
 */

require_once __DIR__ . '/../Core/Model.php';

class Category extends Model {
    protected string $table = 'categories';

    public function getActiveCategories(): array {
        $sql = "SELECT c.*, 
                (SELECT COUNT(*) FROM `products` p WHERE p.category_id = c.id AND p.status = 'active' AND p.deleted_at IS NULL) as product_count
                FROM `categories` c 
                WHERE c.status = 'active' AND c.deleted_at IS NULL 
                ORDER BY c.level ASC, c.name ASC";
        return $this->fetchAll($sql);
    }

    public function getCategoryTree(): array {
        $all = $this->getActiveCategories();
        $tree = [];
        $indexed = [];

        foreach ($all as $cat) {
            $cat['children'] = [];
            $indexed[$cat['id']] = $cat;
        }

        foreach ($indexed as $id => &$cat) {
            if ($cat['parent_id'] && isset($indexed[$cat['parent_id']])) {
                $indexed[$cat['parent_id']]['children'][] = &$cat;
            } else {
                $tree[] = &$cat;
            }
        }

        return $tree;
    }

    public function findBySlug(string $slug): ?array {
        $sql = "SELECT * FROM `categories` WHERE `slug` = :slug AND `status` = 'active' AND `deleted_at` IS NULL LIMIT 1";
        return $this->fetchOne($sql, [':slug' => $slug]);
    }

    // =========================================================
    // Admin Management Methods
    // =========================================================

    public function getAllCategories(bool $includeDeleted = false): array {
        $where = $includeDeleted ? '1=1' : 'c.deleted_at IS NULL';
        $sql = "SELECT c.*,
                (SELECT COUNT(*) FROM `products` p WHERE p.category_id = c.id AND p.deleted_at IS NULL) as product_count,
                parent.name as parent_name
                FROM `categories` c
                LEFT JOIN `categories` parent ON c.parent_id = parent.id
                WHERE {$where}
                ORDER BY c.level ASC, c.parent_id ASC, c.name ASC";
        return $this->fetchAll($sql);
    }

    public function findAdmin(int $id): ?array {
        $sql = "SELECT c.*, parent.name as parent_name
                FROM `categories` c
                LEFT JOIN `categories` parent ON c.parent_id = parent.id
                WHERE c.id = :id LIMIT 1";
        return $this->fetchOne($sql, [':id' => $id]);
    }

    public function slugExistsAdmin(string $slug, ?int $excludeId = null): bool {
        $sql = "SELECT id FROM `categories` WHERE slug = :slug";
        $params = [':slug' => $slug];
        if ($excludeId) {
            $sql .= ' AND id != :eid';
            $params[':eid'] = $excludeId;
        }
        $sql .= ' LIMIT 1';
        return (bool)$this->fetchOne($sql, $params);
    }

    public function createCategory(array $data): int {
        $data['created_at'] = date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->insert($data);
    }

    public function updateCategory(int $id, array $data): bool {
        $data['updated_at'] = date('Y-m-d H:i:s');
        $sets = implode(', ', array_map(fn($k) => "`{$k}` = :{$k}", array_keys($data)));
        $sql = "UPDATE `categories` SET {$sets} WHERE `id` = :id";
        $params = array_merge($data, ['id' => $id]);
        $stmt = $this->query($sql, $params);
        return $stmt->rowCount() > 0;
    }

    public function disableCategory(int $id): bool {
        $stmt = $this->query(
            "UPDATE `categories` SET `status` = 'disabled', `updated_at` = NOW() WHERE `id` = :id AND `deleted_at` IS NULL",
            [':id' => $id]
        );
        return $stmt->rowCount() > 0;
    }

    public function enableCategory(int $id): bool {
        $stmt = $this->query(
            "UPDATE `categories` SET `status` = 'active', `updated_at` = NOW() WHERE `id` = :id",
            [':id' => $id]
        );
        return $stmt->rowCount() > 0;
    }

    public function softDeleteCategory(int $id): bool {
        // Reassign products to null category before deleting
        $this->query("UPDATE `products` SET `category_id` = NULL WHERE `category_id` = :id", [':id' => $id]);
        $stmt = $this->query(
            "UPDATE `categories` SET `status` = 'deleted', `deleted_at` = NOW() WHERE `id` = :id",
            [':id' => $id]
        );
        return $stmt->rowCount() > 0;
    }

    public function restoreCategory(int $id): bool {
        $stmt = $this->query(
            "UPDATE `categories` SET `status` = 'active', `deleted_at` = NULL, `updated_at` = NOW() WHERE `id` = :id",
            [':id' => $id]
        );
        return $stmt->rowCount() > 0;
    }
}