<?php

namespace Core\App\Models;

use PDO;

class CategoryModel
{
    protected PDO $db;

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

    /* =====================================================
     * Get all categories
     * ===================================================== */
    public function getAll(): array
    {
        $stmt = $this->db->query("
            SELECT *
            FROM categories
            ORDER BY created_at DESC
        ");

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

    /* =====================================================
     * Get category by ID
     * ===================================================== */
    public function getById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM categories
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->execute([$id]);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }
    /* =====================================================
 * Get category IDs by product
 * ===================================================== */
public function getIdsByProduct(int $productId): array
{
    $stmt = $this->db->prepare("
        SELECT category_id
        FROM product_categories
        WHERE product_id = ?
    ");
    $stmt->execute([$productId]);

    return array_column(
        $stmt->fetchAll(PDO::FETCH_ASSOC),
        'category_id'
    );
}


    public function getBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM categories WHERE slug = ? AND status = 'active' LIMIT 1");
        $stmt->execute([$slug]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getActive(): array
    {
        $stmt = $this->db->query("SELECT * FROM categories WHERE status = 'active' ORDER BY created_at DESC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /* =====================================================
     * Create category
     * ===================================================== */
    public function create(array $data): int
    {
        $name = trim($data['name'] ?? '');
        $slug = $this->generateUniqueSlug($data['slug'] ?? $name);

        $stmt = $this->db->prepare("
            INSERT INTO categories (name, slug, image, status, created_at)
            VALUES (:name, :slug, :image, :status, NOW())
        ");

        $stmt->execute([
            ':name'   => $name,
            ':slug'   => $slug,
            ':image'  => $data['image'] ?? null,
            ':status' => $data['status'] ?? 'active',
        ]);

        return (int) $this->db->lastInsertId();
    }

    /* =====================================================
     * Update category
     * ===================================================== */
    public function update(int $id, array $data): bool
    {
        $name = trim($data['name'] ?? '');
        $slug = $this->generateUniqueSlug(
            $data['slug'] ?? $name,
            $id
        );

        $stmt = $this->db->prepare("
            UPDATE categories SET
                name = :name,
                slug = :slug,
                image = :image,
                status = :status
            WHERE id = :id
        ");

        return $stmt->execute([
            ':id'     => $id,
            ':name'   => $name,
            ':slug'   => $slug,
            ':image'  => $data['image'] ?? null,
            ':status' => $data['status'] ?? 'active',
        ]);
    }

    /* =====================================================
     * Delete category
     * ===================================================== */
    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare("
            DELETE FROM categories WHERE id = ?
        ");
        return $stmt->execute([$id]);
    }

    /* =====================================================
     * Helpers
     * ===================================================== */

    private function generateUniqueSlug(string $source, ?int $ignoreId = null): string
    {
        $base = trim(mb_strtolower($source));
        $base = preg_replace('/[^\p{L}\p{N}]+/u', '-', $base);
        $base = trim($base, '-');

        if ($base === '') {
            $base = 'category';
        }

        $slug = $base;
        $i = 1;

        while ($this->slugExists($slug, $ignoreId)) {
            $i++;
            $slug = $base . '-' . $i;
        }

        return $slug;
    }

    private function slugExists(string $slug, ?int $ignoreId = null): bool
    {
        $sql = "SELECT id FROM categories WHERE slug = ?";
        $params = [$slug];

        if ($ignoreId) {
            $sql .= " AND id != ?";
            $params[] = $ignoreId;
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return (bool) $stmt->fetchColumn();
    }
    
}
