<?php
/**
 * Product Model
 * Maps to `products`, `product_images`, `product_variations`, `product_skus`
 */

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

class Product extends Model {
    protected string $table = 'products';

    /**
     * Search and Filter Products for Customer Catalog
     */
    public function search(array $filters = [], int $limit = 16, int $offset = 0): array {
        [$whereSql, $params] = $this->buildFilterConditions($filters);

        $sort = $filters['sort'] ?? 'latest';
        $orderBy = "p.created_at DESC";
        switch ($sort) {
            case 'price_asc':
                $orderBy = "p.price ASC";
                break;
            case 'price_desc':
                $orderBy = "p.price DESC";
                break;
            case 'popular':
                $orderBy = "p.stock DESC, p.created_at DESC";
                break;
            case 'latest':
            default:
                $orderBy = "p.created_at DESC";
                break;
        }

        $sql = "SELECT p.*, s.store_name, s.store_slug, s.logo as store_logo, s.verified_at as store_verified,
                c.name as category_name, c.slug as category_slug,
                (SELECT image_path FROM `product_images` pi WHERE pi.product_id = p.id ORDER BY pi.is_main DESC, pi.sort_order ASC LIMIT 1) as gallery_image
                FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$whereSql}
                ORDER BY {$orderBy}
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Count products matching filters
     */
    public function countSearch(array $filters = []): int {
        [$whereSql, $params] = $this->buildFilterConditions($filters);

        $sql = "SELECT COUNT(*) FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$whereSql}";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();

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

    private function buildFilterConditions(array $filters): array {
        $conditions = [
            "p.status = 'active'",
            "p.deleted_at IS NULL",
            "s.status = 'active'",
            "s.deleted_at IS NULL"
        ];
        $params = [];

        // Keyword Search (Name, Description, Badges, Store Name, Category Name, Variation Options, SKUs)
        if (!empty($filters['q'])) {
            $rawQuery = trim($filters['q']);
            $words = preg_split('/\s+/', $rawQuery);
            $words = array_filter($words, fn($w) => mb_strlen(trim($w)) > 0);

            if (!empty($words)) {
                $wordConditions = [];
                $wIdx = 0;
                foreach ($words as $word) {
                    $term = '%' . $word . '%';
                    $pName   = ":kw_n_{$wIdx}";
                    $pDesc   = ":kw_d_{$wIdx}";
                    $pBadge  = ":kw_b_{$wIdx}";
                    $pStore  = ":kw_s_{$wIdx}";
                    $pCat    = ":kw_c_{$wIdx}";
                    $pSku    = ":kw_sku_{$wIdx}";
                    $pVarOpt = ":kw_vo_{$wIdx}";

                    $wordConditions[] = "(
                        p.name LIKE {$pName}
                        OR p.description LIKE {$pDesc}
                        OR p.badges LIKE {$pBadge}
                        OR s.store_name LIKE {$pStore}
                        OR c.name LIKE {$pCat}
                        OR p.id IN (SELECT psk.product_id FROM product_skus psk WHERE psk.variation_summary LIKE {$pSku})
                        OR p.id IN (SELECT pvo.product_id FROM product_variation_options pvo WHERE pvo.name LIKE {$pVarOpt})
                    )";

                    $params[$pName]   = $term;
                    $params[$pDesc]   = $term;
                    $params[$pBadge]  = $term;
                    $params[$pStore]  = $term;
                    $params[$pCat]    = $term;
                    $params[$pSku]    = $term;
                    $params[$pVarOpt] = $term;
                    $wIdx++;
                }

                $conditions[] = "(" . implode(" AND ", $wordConditions) . ")";
            }
        }

        // Category Filter
        if (!empty($filters['category_id'])) {
            $catId = (int)$filters['category_id'];
            $conditions[] = "(p.category_id = :cat_id OR p.category_id IN (SELECT cat_child.id FROM categories cat_child WHERE cat_child.parent_id = :cat_id_child))";
            $params[':cat_id'] = $catId;
            $params[':cat_id_child'] = $catId;
        } elseif (!empty($filters['category_slug'])) {
            $conditions[] = "(c.slug = :cat_slug OR c.parent_id IN (SELECT cat_parent.id FROM categories cat_parent WHERE cat_parent.slug = :cat_slug_parent))";
            $params[':cat_slug'] = $filters['category_slug'];
            $params[':cat_slug_parent'] = $filters['category_slug'];
        }

        // Store Filter
        if (!empty($filters['store_id'])) {
            $conditions[] = "p.store_id = :store_id";
            $params[':store_id'] = (int)$filters['store_id'];
        } elseif (!empty($filters['store_slug'])) {
            $conditions[] = "s.store_slug = :store_slug";
            $params[':store_slug'] = $filters['store_slug'];
        }

        // Price Filter
        if (isset($filters['min_price']) && is_numeric($filters['min_price']) && (float)$filters['min_price'] > 0) {
            $conditions[] = "p.price >= :min_price";
            $params[':min_price'] = (float)$filters['min_price'];
        }

        if (isset($filters['max_price']) && is_numeric($filters['max_price']) && (float)$filters['max_price'] > 0) {
            $conditions[] = "p.price <= :max_price";
            $params[':max_price'] = (float)$filters['max_price'];
        }

        // Stock availability filter
        if (!empty($filters['in_stock_only'])) {
            $conditions[] = "p.stock > 0";
        }

        return [implode(" AND ", $conditions), $params];
    }

    /**
     * Get Product Detail with Store and Category
     */
    public function findDetailByIdOrSlug($idOrSlug): ?array {
        $field = is_numeric($idOrSlug) ? 'p.id' : 'p.slug';

        $sql = "SELECT p.*, s.store_name, s.store_slug, s.logo as store_logo, s.cover_image as store_cover, 
                s.phone as store_phone, s.address as store_address, s.verified_at as store_verified,
                c.name as category_name, c.slug as category_slug
                FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$field} = :id_or_slug AND p.deleted_at IS NULL AND s.status = 'active'
                LIMIT 1";

        return $this->fetchOne($sql, [':id_or_slug' => $idOrSlug]);
    }

    /**
     * Seller: Get Products of a specific Store
     */
    public function getSellerProducts(int $storeId, array $filters = [], int $limit = 20, int $offset = 0): array {
        $conditions = ["p.store_id = :store_id", "p.deleted_at IS NULL"];
        $params = [':store_id' => $storeId];

        if (!empty($filters['q'])) {
            $conditions[] = "(p.name LIKE :kw OR p.description LIKE :kw)";
            $params[':kw'] = '%' . trim($filters['q']) . '%';
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = "p.status = :status";
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['category_id'])) {
            $conditions[] = "p.category_id = :cat_id";
            $params[':cat_id'] = (int)$filters['category_id'];
        }

        if (!empty($filters['stock_state'])) {
            if ($filters['stock_state'] === 'out_of_stock') {
                $conditions[] = "p.stock <= 0";
            } elseif ($filters['stock_state'] === 'low_stock') {
                $conditions[] = "p.stock > 0 AND p.stock <= 5";
            }
        }

        $whereSql = implode(" AND ", $conditions);

        $sql = "SELECT p.*, c.name as category_name,
                (SELECT image_path FROM `product_images` pi WHERE pi.product_id = p.id ORDER BY pi.is_main DESC, pi.sort_order ASC LIMIT 1) as gallery_image
                FROM `products` p
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$whereSql}
                ORDER BY p.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function countSellerProducts(int $storeId, array $filters = []): int {
        $conditions = ["p.store_id = :store_id", "p.deleted_at IS NULL"];
        $params = [':store_id' => $storeId];

        if (!empty($filters['q'])) {
            $conditions[] = "(p.name LIKE :kw OR p.description LIKE :kw)";
            $params[':kw'] = '%' . trim($filters['q']) . '%';
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = "p.status = :status";
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['category_id'])) {
            $conditions[] = "p.category_id = :cat_id";
            $params[':cat_id'] = (int)$filters['category_id'];
        }

        $whereSql = implode(" AND ", $conditions);

        $sql = "SELECT COUNT(*) FROM `products` p WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();

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

    public function findSellerProduct(int $productId, int $storeId): ?array {
        $sql = "SELECT p.*, c.name as category_name 
                FROM `products` p
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE p.id = :id AND p.store_id = :store_id AND p.deleted_at IS NULL
                LIMIT 1";
        return $this->fetchOne($sql, [':id' => $productId, ':store_id' => $storeId]);
    }

    /**
     * Create Product with Images, Variations, and SKUs transactionally
     */
    public function createSellerProduct(array $productData, array $images = [], array $variations = [], array $skus = []): int {
        $this->db->beginTransaction();
        try {
            $productId = $this->insert($productData);

            // Insert Images with ordering and main flag
            if (!empty($images)) {
                $sqlImg = "INSERT INTO `product_images` (`product_id`, `image_path`, `is_main`, `sort_order`, `created_at`) VALUES (:pid, :img, :main, :sort, NOW())";
                $stmtImg = $this->db->prepare($sqlImg);
                foreach ($images as $idx => $img) {
                    if (is_array($img)) {
                        $imgPath = $img['image_path'] ?? $img['path'] ?? '';
                        $isMain = !empty($img['is_main']) ? 1 : 0;
                        $sortOrder = isset($img['sort_order']) ? (int)$img['sort_order'] : $idx;
                    } else {
                        $imgPath = (string)$img;
                        $isMain = ($idx === 0) ? 1 : 0;
                        $sortOrder = $idx;
                    }

                    if (!empty($imgPath)) {
                        $stmtImg->execute([
                            ':pid' => $productId,
                            ':img' => $imgPath,
                            ':main' => $isMain,
                            ':sort' => $sortOrder
                        ]);
                    }
                }
            }

            // Insert Variations & Options
            if (!empty($variations)) {
                $sqlVar = "INSERT INTO `product_variations` (`product_id`, `name`, `sort_order`) VALUES (:pid, :name, :sort)";
                $stmtVar = $this->db->prepare($sqlVar);

                $sqlOpt = "INSERT INTO `product_variation_options` (`variation_id`, `product_id`, `name`, `image`, `price_modifier`) VALUES (:vid, :pid, :name, :img, :mod)";
                $stmtOpt = $this->db->prepare($sqlOpt);

                foreach ($variations as $vIdx => $var) {
                    if (empty($var['name'])) continue;
                    $stmtVar->execute([
                        ':pid' => $productId,
                        ':name' => $var['name'],
                        ':sort' => $vIdx
                    ]);
                    $variationId = (int)$this->db->lastInsertId();

                    if (!empty($var['options'])) {
                        foreach ($var['options'] as $opt) {
                            $optName = is_array($opt) ? trim($opt['name'] ?? '') : trim($opt);
                            if (empty($optName)) continue;
                            $stmtOpt->execute([
                                ':vid'  => $variationId,
                                ':pid'  => $productId,
                                ':name' => $optName,
                                ':img'  => is_array($opt) ? ($opt['image'] ?? null) : null,
                                ':mod'  => is_array($opt) ? (float)($opt['price_modifier'] ?? 0) : 0
                            ]);
                        }
                    }
                }
            }

            // Insert SKUs
            if (!empty($skus)) {
                $sqlSku = "INSERT INTO `product_skus` (`product_id`, `sku`, `variation_summary`, `price`, `stock`, `status`, `last_updated`) VALUES (:pid, :sku, :summary, :price, :stock, :status, NOW())";
                $stmtSku = $this->db->prepare($sqlSku);
                foreach ($skus as $sku) {
                    $stmtSku->execute([
                        ':pid' => $productId,
                        ':sku' => $sku['sku'] ?? ('SKU-' . strtoupper(substr(bin2hex(random_bytes(3)), 0, 6))),
                        ':summary' => $sku['variation_summary'] ?? '',
                        ':price' => $sku['price'] ?? $productData['price'],
                        ':stock' => $sku['stock'] ?? $productData['stock'],
                        ':status' => ((int)($sku['stock'] ?? 0) <= 0) ? 'out_of_stock' : (((int)$sku['stock'] <= 5) ? 'low_stock' : 'available')
                    ]);
                }
            }

            $this->db->commit();
            return $productId;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /**
     * Update Product Details, Gallery Images Ordering & Deletions, Variations & SKUs
     */
    public function updateSellerProduct(
        int $productId, 
        int $storeId, 
        array $productData, 
        array $newImages = [], 
        ?array $variations = null, 
        ?array $skus = null,
        array $deleteImageIds = [],
        array $existingImageUpdates = []
    ): bool {
        $this->db->beginTransaction();
        try {
            // Delete specified existing images
            if (!empty($deleteImageIds)) {
                $placeholders = implode(',', array_fill(0, count($deleteImageIds), '?'));
                $params = array_merge([$productId], array_map('intval', $deleteImageIds));
                $stmtDel = $this->db->prepare("DELETE FROM `product_images` WHERE `product_id` = ? AND `id` IN ({$placeholders})");
                $stmtDel->execute($params);
            }

            // Update existing images sort_order & is_main
            if (!empty($existingImageUpdates)) {
                $stmtUpImg = $this->db->prepare("UPDATE `product_images` SET `sort_order` = :sort, `is_main` = :main WHERE `id` = :id AND `product_id` = :pid");
                foreach ($existingImageUpdates as $up) {
                    $stmtUpImg->execute([
                        ':sort' => (int)($up['sort_order'] ?? 0),
                        ':main' => !empty($up['is_main']) ? 1 : 0,
                        ':id'   => (int)$up['id'],
                        ':pid'  => $productId
                    ]);
                }
            }

            // Insert newly added gallery images with custom ordering & main flag
            if (!empty($newImages)) {
                $sqlImg = "INSERT INTO `product_images` (`product_id`, `image_path`, `is_main`, `sort_order`, `created_at`) VALUES (:pid, :img, :main, :sort, NOW())";
                $stmtImg = $this->db->prepare($sqlImg);
                foreach ($newImages as $idx => $img) {
                    if (is_array($img)) {
                        $imgPath = $img['image_path'] ?? $img['path'] ?? '';
                        $isMain = !empty($img['is_main']) ? 1 : 0;
                        $sortOrder = isset($img['sort_order']) ? (int)$img['sort_order'] : (90 + $idx);
                    } else {
                        $imgPath = (string)$img;
                        $isMain = 0;
                        $sortOrder = 90 + $idx;
                    }

                    if (!empty($imgPath)) {
                        $stmtImg->execute([
                            ':pid' => $productId,
                            ':img' => $imgPath,
                            ':main' => $isMain,
                            ':sort' => $sortOrder
                        ]);
                    }
                }
            }

            // If main_image is not explicitly given in productData or needs sync from product_images:
            $stmtCover = $this->db->prepare("SELECT image_path FROM `product_images` WHERE `product_id` = :pid ORDER BY `is_main` DESC, `sort_order` ASC, `id` ASC LIMIT 1");
            $stmtCover->execute([':pid' => $productId]);
            $resolvedCover = $stmtCover->fetchColumn();
            if ($resolvedCover) {
                $productData['main_image'] = $resolvedCover;
            }

            $this->update($productId, $productData);

            // Sync Variations if provided
            if ($variations !== null) {
                // Remove old variations and options for this product
                $this->db->prepare("DELETE FROM `product_variation_options` WHERE `product_id` = :pid")->execute([':pid' => $productId]);
                $this->db->prepare("DELETE FROM `product_variations` WHERE `product_id` = :pid")->execute([':pid' => $productId]);

                if (!empty($variations)) {
                    $sqlVar = "INSERT INTO `product_variations` (`product_id`, `name`, `sort_order`) VALUES (:pid, :name, :sort)";
                    $stmtVar = $this->db->prepare($sqlVar);

                    $sqlOpt = "INSERT INTO `product_variation_options` (`variation_id`, `product_id`, `name`, `image`, `price_modifier`) VALUES (:vid, :pid, :name, :img, :mod)";
                    $stmtOpt = $this->db->prepare($sqlOpt);

                    foreach ($variations as $vIdx => $var) {
                        if (empty($var['name'])) continue;
                        $stmtVar->execute([
                            ':pid' => $productId,
                            ':name' => $var['name'],
                            ':sort' => $vIdx
                        ]);
                        $variationId = (int)$this->db->lastInsertId();

                        if (!empty($var['options'])) {
                            foreach ($var['options'] as $opt) {
                                $optName = is_array($opt) ? trim($opt['name'] ?? '') : trim($opt);
                                if (empty($optName)) continue;
                                $stmtOpt->execute([
                                    ':vid'  => $variationId,
                                    ':pid'  => $productId,
                                    ':name' => $optName,
                                    ':img'  => is_array($opt) ? ($opt['image'] ?? null) : null,
                                    ':mod'  => is_array($opt) ? (float)($opt['price_modifier'] ?? 0) : 0
                                ]);
                            }
                        }
                    }
                }
            }

            // Sync SKUs if provided
            if ($skus !== null) {
                $this->db->prepare("DELETE FROM `product_skus` WHERE `product_id` = :pid")->execute([':pid' => $productId]);

                if (!empty($skus)) {
                    $sqlSku = "INSERT INTO `product_skus` (`product_id`, `sku`, `variation_summary`, `price`, `stock`, `status`, `last_updated`) VALUES (:pid, :sku, :summary, :price, :stock, :status, NOW())";
                    $stmtSku = $this->db->prepare($sqlSku);
                    foreach ($skus as $sku) {
                        $stmtSku->execute([
                            ':pid' => $productId,
                            ':sku' => $sku['sku'] ?? ('SKU-' . strtoupper(substr(bin2hex(random_bytes(3)), 0, 6))),
                            ':summary' => $sku['variation_summary'] ?? '',
                            ':price' => $sku['price'] ?? $productData['price'],
                            ':stock' => $sku['stock'] ?? $productData['stock'],
                            ':status' => ((int)($sku['stock'] ?? 0) <= 0) ? 'out_of_stock' : (((int)$sku['stock'] <= 5) ? 'low_stock' : 'available')
                        ]);
                    }
                }
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function softDeleteSellerProduct(int $productId, int $storeId): bool {
        $sql = "UPDATE `products` SET `deleted_at` = NOW(), `status` = 'deleted' WHERE `id` = :id AND `store_id` = :store_id";
        $stmt = $this->query($sql, [':id' => $productId, ':store_id' => $storeId]);
        return $stmt->rowCount() > 0;
    }

    public function deleteProductImage(int $imageId, int $productId): ?string {
        $sql = "SELECT image_path FROM `product_images` WHERE id = :id AND product_id = :pid LIMIT 1";
        $img = $this->fetchOne($sql, [':id' => $imageId, ':pid' => $productId]);

        if ($img) {
            $sqlDel = "DELETE FROM `product_images` WHERE id = :id";
            $this->query($sqlDel, [':id' => $imageId]);
            return $img['image_path'];
        }

        return null;
    }

    /**
     * Get Product Gallery Images
     */
    public function getImages(int $productId): array {
        $sql = "SELECT * FROM `product_images` WHERE product_id = :product_id ORDER BY is_main DESC, sort_order ASC";
        return $this->fetchAll($sql, [':product_id' => $productId]);
    }

    /**
     * Get Product Variations and Options
     */
    public function getVariations(int $productId): array {
        $sqlVars = "SELECT * FROM `product_variations` WHERE product_id = :product_id ORDER BY sort_order ASC, id ASC";
        $variations = $this->fetchAll($sqlVars, [':product_id' => $productId]);

        foreach ($variations as &$var) {
            $sqlOpts = "SELECT * FROM `product_variation_options` WHERE variation_id = :var_id ORDER BY id ASC";
            $var['options'] = $this->fetchAll($sqlOpts, [':var_id' => (int)$var['id']]);
        }

        return $variations;
    }

    /**
     * Get Product SKUs
     */
    public function getSkus(int $productId): array {
        $sql = "SELECT * FROM `product_skus` WHERE product_id = :product_id";
        return $this->fetchAll($sql, [':product_id' => $productId]);
    }

    /**
     * Quick Update Product Main Stock
     */
    public function quickUpdateStock(int $productId, int $storeId, int $newStock): bool {
        $sql = "UPDATE `products` SET `stock` = :stock, `updated_at` = NOW() 
                WHERE `id` = :id AND `store_id` = :store_id AND `deleted_at` IS NULL";
        $stmt = $this->query($sql, [
            ':stock' => max(0, $newStock),
            ':id' => $productId,
            ':store_id' => $storeId
        ]);
        return $stmt->rowCount() > 0;
    }

    /**
     * Quick Update SKU Stock
     */
    public function quickUpdateSkuStock(int $skuId, int $productId, int $newStock): bool {
        $status = ($newStock <= 0) ? 'out_of_stock' : (($newStock <= 5) ? 'low_stock' : 'available');
        $sql = "UPDATE `product_skus` SET `stock` = :stock, `status` = :status, `last_updated` = NOW() 
                WHERE `id` = :id AND `product_id` = :pid";
        $stmt = $this->query($sql, [
            ':stock' => max(0, $newStock),
            ':status' => $status,
            ':id' => $skuId,
            ':pid' => $productId
        ]);
        return $stmt->rowCount() > 0;
    }

    // =========================================================
    // Admin Product Moderation Methods
    // =========================================================

    public function getAdminProducts(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminProductConditions($filters);

        $sql = "SELECT p.*, s.store_name, s.store_slug, c.name as category_name,
                u.username as seller_username,
                (SELECT image_path FROM `product_images` pi WHERE pi.product_id = p.id ORDER BY pi.is_main DESC, pi.sort_order ASC LIMIT 1) as main_image
                FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                JOIN `users` u ON s.user_id = u.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$whereSql}
                ORDER BY
                  CASE p.status WHEN 'pending' THEN 0 WHEN 'draft' THEN 1 ELSE 2 END ASC,
                  p.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countAdminProducts(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminProductConditions($filters);
        $sql = "SELECT COUNT(*) FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildAdminProductConditions(array $filters): array {
        $conditions = ['p.status != \'deleted\''];
        $params = [];

        if (!empty($filters['q'])) {
            $conditions[] = '(p.name LIKE :q OR p.slug LIKE :q OR s.store_name LIKE :q)';
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = 'p.status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['category_id'])) {
            $conditions[] = 'p.category_id = :cat_id';
            $params[':cat_id'] = (int)$filters['category_id'];
        }
        if (!empty($filters['store_id'])) {
            $conditions[] = 'p.store_id = :store_id';
            $params[':store_id'] = (int)$filters['store_id'];
        }

        return [implode(' AND ', $conditions), $params];
    }

    /**
     * Admin product moderation: change status with reason
     * Valid transitions: pending->active, pending->rejected, active->hidden, hidden->active
     */
    public function moderateProduct(int $productId, string $newStatus, ?string $reason = null): bool {
        $updateData = [
            ':status'     => $newStatus,
            ':updated_at' => date('Y-m-d H:i:s'),
            ':id'         => $productId,
        ];
        $reasonSql = '';
        if ($reason !== null) {
            $updateData[':moderation_note'] = $reason;
            $reasonSql = ', `moderation_note` = :moderation_note';
        }
        $sql = "UPDATE `products` SET `status` = :status, `updated_at` = :updated_at{$reasonSql} WHERE `id` = :id";
        $stmt = $this->query($sql, $updateData);
        return $stmt->rowCount() > 0;
    }

    public function getAdminProductDetail(int $productId): ?array {
        $sql = "SELECT p.*, s.store_name, s.store_slug, c.name as category_name,
                u.username as seller_username, u.email as seller_email
                FROM `products` p
                JOIN `stores` s ON p.store_id = s.id
                JOIN `users` u ON s.user_id = u.id
                LEFT JOIN `categories` c ON p.category_id = c.id
                WHERE p.id = :id LIMIT 1";
        return $this->fetchOne($sql, [':id' => $productId]);
    }

    public function getPlatformProductStats(): array {
        $sql = "SELECT
                COUNT(*) as total_products,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_products,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_review,
                SUM(CASE WHEN status = 'hidden' THEN 1 ELSE 0 END) as hidden_products,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_products
                FROM `products` WHERE status != 'deleted' AND deleted_at IS NULL";
        return $this->fetchOne($sql) ?: [];
    }
}