<?php
/**
 * FIXED:
 * - Single Source of Truth for product data
 * - Full product loading for edit (categories, variants, images, layout)
 * - Unified create/update helpers
 * - No breaking changes for index/create/home
 */

namespace Core\App\Models;

use PDO;

class ProductModel
{
    private const SEO_COLUMN_DEFINITIONS = [
        'meta_title' => "ALTER TABLE products ADD COLUMN meta_title VARCHAR(255) NULL AFTER title",
        'meta_description' => "ALTER TABLE products ADD COLUMN meta_description TEXT NULL AFTER meta_title",
        'og_title' => "ALTER TABLE products ADD COLUMN og_title VARCHAR(255) NULL AFTER meta_description",
        'og_description' => "ALTER TABLE products ADD COLUMN og_description TEXT NULL AFTER og_title",
        'og_image' => "ALTER TABLE products ADD COLUMN og_image VARCHAR(255) NULL AFTER og_description",
        'schema_brand' => "ALTER TABLE products ADD COLUMN schema_brand VARCHAR(190) NULL AFTER og_image",
        'schema_gtin' => "ALTER TABLE products ADD COLUMN schema_gtin VARCHAR(100) NULL AFTER schema_brand",
        'schema_mpn' => "ALTER TABLE products ADD COLUMN schema_mpn VARCHAR(100) NULL AFTER schema_gtin",
    ];

    protected PDO $db;
    private function normalizeVariantField(?string $value): ?string
    {
        if ($value === null) return null;
        $value = trim((string)$value);
        return $value === '' ? null : $value;
    }

    private function ensureVariantRow(int $productId, ?string $size, ?string $color): void
    {
        $size  = $this->normalizeVariantField($size);
        $color = $this->normalizeVariantField($color);

        $stmt = $this->db->prepare("
            INSERT INTO product_variant_stock (product_id, size, color, quantity)
            SELECT ?, ?, ?, 0 FROM DUAL
            WHERE NOT EXISTS (
                SELECT 1 FROM product_variant_stock
                WHERE product_id = ?
                  AND (size  = ? OR (size  IS NULL AND ? IS NULL))
                  AND (color = ? OR (color IS NULL AND ? IS NULL))
            )
        ");
        $stmt->execute([$productId, $size, $color, $productId, $size, $size, $color, $color]);
    }

    protected array $columnsCache = [];

    public function __construct()
    {
        $this->db = db();
        // All SEO columns (meta_title, meta_description, og_title, og_description,
        // og_image, schema_brand, schema_gtin, schema_mpn) are defined in arscript_A.sql.
        // Schema is managed exclusively through the install SQL — no runtime ALTER TABLE.
    }

    /* =====================================================
     * Get all products (ADMIN) — use only for small sets
     * For admin listings prefer getPaginated()
     * ===================================================== */
    public function getAll(): array
    {
        $stmt = $this->db->query("
            SELECT *
            FROM products
            ORDER BY created_at DESC
        ");

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

    /* =====================================================
     * Get products for sitemap — minimal fields, active only
     * Uses a per-batch cursor to avoid loading all rows into
     * memory on large catalogs.
     * ===================================================== */
    public function getForSitemap(int $limit = 1000, int $offset = 0): array
    {
        $stmt = $this->db->prepare("
            SELECT id, slug, updated_at, featured_image, title, images
            FROM products
            WHERE status = 'active'
            ORDER BY id ASC
            LIMIT ? OFFSET ?
        ");
        $stmt->execute([$limit, $offset]);
        return $this->decodeJsonFields($stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    /* =====================================================
     * Get paginated products for admin listing
     * @return array ['items'=>[...], 'total'=>int, 'pages'=>int, 'page'=>int, 'perPage'=>int]
     * ===================================================== */
    public function getPaginated(int $page = 1, int $perPage = 30, array $filters = []): array
    {
        $page    = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset  = ($page - 1) * $perPage;

        $where  = [];
        $params = [];

        if (!empty($filters['search'])) {
            $where[]  = '(title LIKE :search OR sku LIKE :search)';
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        if (isset($filters['category_id']) && $filters['category_id'] > 0) {
            $where[]  = 'category_id = :category_id';
            $params[':category_id'] = (int)$filters['category_id'];
        }

        if (isset($filters['status']) && $filters['status'] !== '') {
            $where[]  = 'status = :status';
            $params[':status'] = $filters['status'];
        }

        $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        $countStmt = $this->db->prepare("SELECT COUNT(*) FROM products {$whereClause}");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $params[':limit']  = $perPage;
        $params[':offset'] = $offset;

        $stmt = $this->db->prepare(
            "SELECT * FROM products {$whereClause} ORDER BY created_at DESC LIMIT :limit OFFSET :offset"
        );
        foreach ($params as $key => $val) {
            if ($key === ':limit' || $key === ':offset') {
                $stmt->bindValue($key, $val, \PDO::PARAM_INT);
            } else {
                $stmt->bindValue($key, $val, \PDO::PARAM_STR);
            }
        }
        $stmt->execute();

        return [
            'items'   => $this->decodeJsonFields($stmt->fetchAll(\PDO::FETCH_ASSOC)),
            'total'   => $total,
            'pages'   => (int)ceil($total / $perPage),
            'page'    => $page,
            'perPage' => $perPage,
        ];
    }

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

        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            return null;
        }

        return $this->decodeJsonFields([$row])[0];
    }

    /* =====================================================
     * Get product FULL (EDIT)
     * ===================================================== */
    public function getFullById(int $id): ?array
    {
        $product = $this->getById($id);
        if (!$product) {
            return null;
        }

        // Categories
        $stmt = $this->db->prepare("
            SELECT category_id
            FROM product_categories
            WHERE product_id = ?
        ");
        $stmt->execute([$id]);
        $product['categories'] = $stmt->fetchAll(PDO::FETCH_COLUMN);

        // Ensure layout toggles structure
        if (
            !isset($product['layout']) ||
            !is_array($product['layout']) ||
            !isset($product['layout']['toggles'])
        ) {
            $product['layout'] = [
                'toggles' => [
                    'sticky_buy' => 0,
                    'whatsapp'   => 0,
                    'reviews'    => 0,
                    'countdown'  => 0,
                ]
            ];
        }

        return $product;
    }

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

    // ===============================
    // Columns (layout INCLUDED ALWAYS)
    // ===============================
    $columns = ['title'];
    foreach (array_keys(self::SEO_COLUMN_DEFINITIONS) as $seoColumn) {
        $columns[] = $seoColumn;
    }
    $columns = array_merge($columns, [
        'slug',
        'short_description',
        'description',
                    'landing_content',
'price',
        'sale_price',
        'discount_type',
        'discount_value',
        'discount_start',
        'discount_end',
        'badge_text',
        'currency',
        'sku',
        'barcode',
        'stock_quantity',
        'stock_alert',
        'images',
        'featured_image',
        'variants',
        'attributes',
        'status',
        'is_featured',
        'layout',
        'offers'
    ]);

    $sql = "INSERT INTO products (" . implode(',', $columns) . ") VALUES (:" . implode(', :', $columns) . ")";
    $stmt = $this->db->prepare($sql);

    // ===============================
    // Base params
    // ===============================
    $params = $this->buildParams($data, $slug);

    // ===============================
    // 🔑 ADD MISSING PARAMS
    // ===============================
    if (!array_key_exists(':attributes', $params)) {
        $params[':attributes'] = null;
    }

    // layout param (JSON or NULL)
    $params[':layout'] = isset($data['layout'])
        ? json_encode($data['layout'], JSON_UNESCAPED_UNICODE)
        : null;

    $stmt->execute($this->filterParamsForStatement($sql, $params));

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


    /* =====================================================
     * Create FULL
     * ===================================================== */
    public function createFull(array $data): int
    {
        $id = $this->create($data);
        $this->syncCategories($id, $data['categories'] ?? []);
        return $id;
    }

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

        $hasLayout = $this->hasColumn('layout');
        $hasOffers = $this->hasColumn('offers');

        $sets = [
            'title = :title',
            'slug = :slug',
            'short_description = :short_description',
            'description = :description',
                        'landing_content = :landing_content',
'price = :price',
            'sale_price = :sale_price',
            'discount_type = :discount_type',
            'discount_value = :discount_value',
            'discount_start = :discount_start',
            'discount_end = :discount_end',
            'badge_text = :badge_text',
            'currency = :currency',
            'sku = :sku',
            'barcode = :barcode',
            'stock_quantity = :stock_quantity',
            'stock_alert = :stock_alert',
            'images = :images',
            'featured_image = :featured_image',
            'variants = :variants',
            'attributes = :attributes',
            'status = :status',
            'is_featured = :is_featured'
        ];

        foreach (array_keys(self::SEO_COLUMN_DEFINITIONS) as $seoColumn) {
            $sets[] = $seoColumn . ' = :' . $seoColumn;
        }

        if ($hasLayout) {
            $sets[] = 'layout = :layout';
        }
        if ($hasOffers) {
            $sets[] = 'offers = :offers';
        }

        $sql = "UPDATE products SET " . implode(', ', $sets) . " WHERE id = :id";
        $stmt = $this->db->prepare($sql);

        $params = $this->buildParams($data, $slug);
        $params[':id'] = $id;
        if ($hasLayout) {
            $params[':layout'] = isset($data['layout'])
                ? json_encode($data['layout'], JSON_UNESCAPED_UNICODE)
                : null;
        }

        return $stmt->execute($this->filterParamsForStatement($sql, $params));
    }

    /* =====================================================
     * Update FULL
     * ===================================================== */
    public function updateFull(int $id, array $data): void
    {
        if (!isset($data['layout'])) {
            $current = $this->getById($id);
            $data['layout'] = $current['layout'] ?? null;
        }

        $this->update($id, $data);
        $this->syncCategories($id, $data['categories'] ?? []);
    }

    /* =====================================================
     * Delete FULL
     * ===================================================== */
    public function deleteFull(int $id): void
    {
        $this->db->prepare(
            "DELETE FROM product_categories WHERE product_id = ?"
        )->execute([$id]);

        $this->db->prepare(
            "DELETE FROM products WHERE id = ?"
        )->execute([$id]);
    }

    /* =====================================================
     * Sync Categories
     * ===================================================== */
    public function syncCategories(int $productId, array $categoryIds): void
    {
        $this->db->prepare(
            "DELETE FROM product_categories WHERE product_id = ?"
        )->execute([$productId]);

        if (empty($categoryIds)) {
            return;
        }

        $stmt = $this->db->prepare(
            "INSERT INTO product_categories (product_id, category_id)
             VALUES (?, ?)"
        );

        foreach ($categoryIds as $cid) {
            $stmt->execute([$productId, (int)$cid]);
        }
    }

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

    private function buildParams(array $data, string $slug): array
    {
        return [
            ':title'             => trim($data['title'] ?? ''),
            ':meta_title'        => $this->nullableString($data['meta_title'] ?? null),
            ':meta_description'  => $this->nullableString($data['meta_description'] ?? null),
            ':og_title'          => $this->nullableString($data['og_title'] ?? null),
            ':og_description'    => $this->nullableString($data['og_description'] ?? null),
            ':og_image'          => $this->nullableString($data['og_image'] ?? null),
            ':schema_brand'      => $this->nullableString($data['schema_brand'] ?? null),
            ':schema_gtin'       => $this->nullableString($data['schema_gtin'] ?? null),
            ':schema_mpn'        => $this->nullableString($data['schema_mpn'] ?? null),
            ':slug'              => $slug,
            ':short_description' => $data['short_description'] ?? null,
            ':description'       => $data['description'] ?? null,
            ':landing_content'   => $data['landing_content'] ?? null,
            ':price'             => $data['price'] ?? 0,
            ':sale_price'        => $data['sale_price'] ?? null,
            ':discount_type'     => $data['discount_type'] ?? null,
            ':discount_value'    => $data['discount_value'] ?? null,
            ':discount_start'    => $data['discount_start'] ?? null,
            ':discount_end'      => $data['discount_end'] ?? null,
            ':badge_text'        => $data['badge_text'] ?? null,
            ':currency'          => $data['currency'] ?? 'MAD',
            ':sku'               => $data['sku'] ?? null,
            ':barcode'           => $data['barcode'] ?? null,
            ':stock_quantity'    => $data['stock_quantity'] ?? 0,
            ':stock_alert'       => $data['stock_alert'] ?? 5,
            ':images'            => isset($data['images']) ? json_encode($data['images']) : null,
            ':featured_image'    => $data['featured_image'] ?? null,
            ':variants'          => isset($data['variants']) ? json_encode($data['variants']) : null,
            ':attributes'        => isset($data['attributes']) ? json_encode($data['attributes']) : null,
            ':status'            => $data['status'] ?? 'active',
            ':is_featured'       => !empty($data['is_featured']) ? 1 : 0,
            ':offers'            => isset($data['offers']) ? json_encode($data['offers'], JSON_UNESCAPED_UNICODE) : null,
        ];
    }

    private function filterParamsForStatement(string $sql, array $params): array
    {
        preg_match_all('/:([a-zA-Z_][a-zA-Z0-9_]*)/', $sql, $matches);
        $namedPlaceholders = $matches[0] ?? [];

        if ($namedPlaceholders === []) {
            return array_values($params);
        }

        $allowed = [];
        foreach ($namedPlaceholders as $placeholder) {
            if (array_key_exists($placeholder, $params)) {
                $allowed[$placeholder] = $params[$placeholder];
            }
        }

        return $allowed;
    }

    private function nullableString($value): ?string
    {
        if ($value === null) {
            return null;
        }

        $value = trim((string)$value);
        return $value === '' ? null : $value;
    }

    private function decodeJsonFields(array $rows): array
    {
        foreach ($rows as &$row) {
            foreach (['images','variants','attributes','layout','offers'] as $field) {
                if (array_key_exists($field, $row)) {
                    $row[$field] = $row[$field]
                        ? (json_decode($row[$field], true) ?: [])
                        : [];
                }
            }
        }
        return $rows;
    }

    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, '-') ?: 'product';

        $slug = $base;
        $i = 1;

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

        return $slug;
    }

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

        if ($ignoreId !== null) {
            $sql .= " AND id != :ignore_id";
            $params[':ignore_id'] = $ignoreId;
        }

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

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

    private function hasColumn(string $column): bool
    {
        if (array_key_exists($column, $this->columnsCache)) {
            return $this->columnsCache[$column];
        }

        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM `products`");
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($columns as $col) {
                if (isset($col['Field'])) {
                    $this->columnsCache[$col['Field']] = true;
                }
            }

            return $this->columnsCache[$column] ?? false;
        } catch (\Throwable $e) {
            return $this->columnsCache[$column] = false;
        }
    }
    
 /* =====================================================
 * Backward-compatible methods for HomeController
 * (RESTORES OLD BEHAVIOR SAFELY)
 * ===================================================== */

public function getForHome(int $limit = 12): array
{
    $stmt = $this->db->prepare("
        SELECT *
        FROM products
        WHERE status = 'active'
        ORDER BY created_at DESC
        LIMIT ?
    ");
    $stmt->bindValue(1, $limit, PDO::PARAM_INT);
    $stmt->execute();

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

public function getFeaturedForHome(int $limit = 12): array
{
    $stmt = $this->db->prepare("
        SELECT *
        FROM products
        WHERE status = 'active'
          AND is_featured = 1
        ORDER BY created_at DESC
        LIMIT ?
    ");
    $stmt->bindValue(1, $limit, PDO::PARAM_INT);
    $stmt->execute();

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

public function getByCategoryForHome(int $categoryId, int $limit = 12): array
{
    $stmt = $this->db->prepare("
        SELECT p.*
        FROM products p
        INNER JOIN product_categories pc
            ON pc.product_id = p.id
        WHERE p.status = 'active'
          AND pc.category_id = ?
        ORDER BY p.created_at DESC
        LIMIT ?
    ");
    $stmt->bindValue(1, $categoryId, PDO::PARAM_INT);
    $stmt->bindValue(2, $limit, PDO::PARAM_INT);
    $stmt->execute();

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

public function searchForHome(string $search = '', int $limit = 48, string $source = 'latest', ?int $categoryId = null): array
{
    $search = trim($search);
    $limit = max(1, min(48, $limit));

    $where = ["p.status = :status"];
    $params = [
        ':status' => 'active',
    ];

    if ($search !== '') {
        $where[] = '(p.title LIKE :search_title OR p.short_description LIKE :search_desc OR p.sku LIKE :search_sku)';
        $like = '%' . $search . '%';
        $params[':search_title'] = $like;
        $params[':search_desc'] = $like;
        $params[':search_sku'] = $like;
    }

    if ($source === 'featured') {
        $where[] = 'p.is_featured = :is_featured';
        $params[':is_featured'] = 1;
    }

    $join = '';
    if ($source === 'category' && $categoryId !== null && $categoryId > 0) {
        $join = ' INNER JOIN product_categories pc ON pc.product_id = p.id ';
        $where[] = 'pc.category_id = :category_id';
        $params[':category_id'] = $categoryId;
    }

    $sql = "SELECT DISTINCT p.* FROM products p{$join} WHERE " . implode(' AND ', $where) . ' ORDER BY p.created_at DESC LIMIT :limit';
    $stmt = $this->db->prepare($sql);

    foreach ($params as $key => $value) {
        $type = is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR;
        $stmt->bindValue($key, $value, $type);
    }
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();

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


public function getBySlug(string $slug): ?array
{
    $slug = trim(rawurldecode($slug));

    if ($slug === '') {
        return null;
    }

    $stmt = $this->db->prepare("
        SELECT *
        FROM products
        WHERE slug = ?
          AND status = 'active'
        LIMIT 1
    ");
    $stmt->execute([$slug]);

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

    if (!$row) {
        return null;
    }

    return $this->decodeJsonFields([$row])[0];
}
public function getActivePaginated(int $page = 1, int $perPage = 12, string $search = ''): array
{
    $page = max(1, $page);
    $perPage = max(1, min(48, $perPage));
    $offset = ($page - 1) * $perPage;
    $search = trim($search);

    $where = "status = 'active'";
    $params = [];
    if ($search !== '') {
        $where .= " AND (title LIKE :search_title OR short_description LIKE :search_desc OR sku LIKE :search_sku)";
        $like = '%' . $search . '%';
        $params[':search_title'] = $like;
        $params[':search_desc']  = $like;
        $params[':search_sku']   = $like;
    }

    $countStmt = $this->db->prepare("SELECT COUNT(*) FROM products WHERE {$where}");
    foreach ($params as $k => $v) {
        $countStmt->bindValue($k, $v, PDO::PARAM_STR);
    }
    $countStmt->execute();
    $count = (int)$countStmt->fetchColumn();

    $stmt = $this->db->prepare("SELECT * FROM products WHERE {$where} ORDER BY created_at DESC LIMIT :limit OFFSET :offset");
    foreach ($params as $k => $v) {
        $stmt->bindValue($k, $v, PDO::PARAM_STR);
    }
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    return [
        'items' => $this->decodeJsonFields($stmt->fetchAll(PDO::FETCH_ASSOC)),
        'total' => $count,
        'page' => $page,
        'per_page' => $perPage,
        'last_page' => max(1, (int)ceil($count / $perPage)),
    ];
}

public function getByCategorySlugPaginated(string $slug, int $page = 1, int $perPage = 12): array
{
    $page = max(1, $page);
    $perPage = max(1, min(48, $perPage));
    $offset = ($page - 1) * $perPage;

    $countStmt = $this->db->prepare("SELECT COUNT(*) FROM products p INNER JOIN product_categories pc ON pc.product_id = p.id INNER JOIN categories c ON c.id = pc.category_id WHERE p.status = :status AND c.slug = :slug AND c.status = :category_status");
    $countStmt->bindValue(':status', 'active', PDO::PARAM_STR);
    $countStmt->bindValue(':slug', $slug, PDO::PARAM_STR);
    $countStmt->bindValue(':category_status', 'active', PDO::PARAM_STR);
    $countStmt->execute();
    $count = (int)$countStmt->fetchColumn();

    $stmt = $this->db->prepare("SELECT DISTINCT p.* FROM products p INNER JOIN product_categories pc ON pc.product_id = p.id INNER JOIN categories c ON c.id = pc.category_id WHERE p.status = :status AND c.slug = :slug AND c.status = :category_status ORDER BY p.created_at DESC LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':status', 'active', PDO::PARAM_STR);
    $stmt->bindValue(':slug', $slug, PDO::PARAM_STR);
    $stmt->bindValue(':category_status', 'active', PDO::PARAM_STR);
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    return [
        'items' => $this->decodeJsonFields($stmt->fetchAll(PDO::FETCH_ASSOC)),
        'total' => $count,
        'page' => $page,
        'per_page' => $perPage,
        'last_page' => max(1, (int)ceil($count / $perPage)),
    ];
}

    /* =====================================================
     * STOCK MANAGEMENT
     * ===================================================== */

    /** جلب المخزون الإجمالي للمنتج */
    public function getStock(int $productId): int
    {
        $stmt = $this->db->prepare("SELECT stock_quantity FROM products WHERE id = ? LIMIT 1");
        $stmt->execute([$productId]);
        return (int)($stmt->fetchColumn() ?? 0);
    }

    /** تحديث المخزون الإجمالي */
    public function setStock(int $productId, int $qty): void
    {
        $this->db->prepare("UPDATE products SET stock_quantity = ? WHERE id = ?")
                 ->execute([$qty, $productId]);
    }

    /** إنقاص المخزون عند الطلب — مع حماية من السالب */
    public function decreaseStock(int $productId, int $qty = 1): bool
    {
        $stmt = $this->db->prepare("
            UPDATE products
            SET stock_quantity = GREATEST(0, stock_quantity - ?)
            WHERE id = ?
        ");
        return $stmt->execute([$qty, $productId]);
    }

    /** إعادة المخزون عند الإلغاء/الرفض */
    public function increaseStock(int $productId, int $qty = 1): bool
    {
        $stmt = $this->db->prepare("
            UPDATE products SET stock_quantity = stock_quantity + ? WHERE id = ?
        ");
        return $stmt->execute([$qty, $productId]);
    }

    /** جلب مخزون خيار معين (مقاس + لون) */
    public function getVariantStock(int $productId, ?string $size, ?string $color): int
    {
        $size  = $this->normalizeVariantField($size);
        $color = $this->normalizeVariantField($color);
        $stmt = $this->db->prepare("
            SELECT quantity FROM product_variant_stock
            WHERE product_id = ?
              AND (size  = ? OR (size  IS NULL AND ? IS NULL))
              AND (color = ? OR (color IS NULL AND ? IS NULL))
            LIMIT 1
        ");
        $stmt->execute([$productId, $size, $size, $color, $color]);
        return (int)($stmt->fetchColumn() ?? 0);
    }

    /** تحديث/إنشاء مخزون خيار */
    public function setVariantStock(int $productId, ?string $size, ?string $color, int $qty): void
    {
        $size  = $this->normalizeVariantField($size);
        $color = $this->normalizeVariantField($color);
        $this->db->prepare("
            INSERT INTO product_variant_stock (product_id, size, color, quantity)
            VALUES (?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE quantity = VALUES(quantity)
        ")->execute([$productId, $size, $color, $qty]);
    }

    /** إنقاص مخزون خيار */
    public function decreaseVariantStock(int $productId, ?string $size, ?string $color, int $qty = 1): void
    {
        $size  = $this->normalizeVariantField($size);
        $color = $this->normalizeVariantField($color);
        $this->ensureVariantRow($productId, $size, $color);
        $this->db->prepare("
            UPDATE product_variant_stock
            SET quantity = GREATEST(0, quantity - ?)
            WHERE product_id = ?
              AND (size  = ? OR (size  IS NULL AND ? IS NULL))
              AND (color = ? OR (color IS NULL AND ? IS NULL))
        ")->execute([$qty, $productId, $size, $size, $color, $color]);
    }

    /** إعادة مخزون خيار */
    public function increaseVariantStock(int $productId, ?string $size, ?string $color, int $qty = 1): void
    {
        $size  = $this->normalizeVariantField($size);
        $color = $this->normalizeVariantField($color);
        $this->ensureVariantRow($productId, $size, $color);
        $this->db->prepare("
            UPDATE product_variant_stock
            SET quantity = quantity + ?
            WHERE product_id = ?
              AND (size  = ? OR (size  IS NULL AND ? IS NULL))
              AND (color = ? OR (color IS NULL AND ? IS NULL))
        ")->execute([$qty, $productId, $size, $size, $color, $color]);
    }

    /** جلب كل مخزون الخيارات لمنتج معين */
    public function getAllVariantStock(int $productId): array
    {
        $stmt = $this->db->prepare("
            SELECT size, color, quantity
            FROM product_variant_stock
            WHERE product_id = ?
        ");
        $stmt->execute([$productId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /** حفظ/تحديث كل مخزون الخيارات دفعة واحدة */
    public function syncVariantStock(int $productId, array $stockData): void
    {
        // stockData = [ ['size'=>'M','color'=>'أحمر','qty'=>10], ... ]
        foreach ($stockData as $item) {
            $this->setVariantStock(
                $productId,
                $item['size']  ?? null,
                $item['color'] ?? null,
                (int)($item['qty'] ?? 0)
            );
        }
    }

    /** فحص هل المخزون متاح (مع أو بدون خيارات) */
    public function isInStock(int $productId, ?string $size = null, ?string $color = null, int $qty = 1): bool
    {
        // إذا كان هناك خيار محدد
        if ($size !== null || $color !== null) {
            return $this->getVariantStock($productId, $size, $color) >= $qty;
        }
        return $this->getStock($productId) >= $qty;
    }

    /** الحصول على حد التنبيه للمنتج */
    public function getStockAlert(int $productId): int
    {
        $stmt = $this->db->prepare("SELECT stock_alert FROM products WHERE id = ? LIMIT 1");
        $stmt->execute([$productId]);
        return (int)($stmt->fetchColumn() ?? 5);
    }

    /** فحص ما إذا كان المخزون أقل من حد التنبيه */
    public function isBelowAlert(int $productId): bool
    {
        $stmt = $this->db->prepare("
            SELECT (stock_quantity <= stock_alert) FROM products WHERE id = ? LIMIT 1
        ");
        $stmt->execute([$productId]);
        return (bool)$stmt->fetchColumn();
    }

}