/**
     * Find product by slug.
     */
    public function findBySlug(string $slug): ?array
    {
        return Database::fetch("
            SELECT
                p.*,
                c.name AS category_name,
                b.name AS brand_name,
                v.business_name AS vendor_name
            FROM products p
            LEFT JOIN categories c ON c.id = p.category_id
            LEFT JOIN brands b ON b.id = p.brand_id
            LEFT JOIN vendors v ON v.id = p.vendor_id
            WHERE p.slug = ?
            LIMIT 1
        ", [$slug]);
    }

    /**
     * Featured products.
     */
    public function featured(int $limit = 12): array
    {
        return Database::fetchAll("
            SELECT
                p.*,
                c.name AS category_name,
                b.name AS brand_name,
                v.business_name AS vendor_name
            FROM products p
            LEFT JOIN categories c ON c.id = p.category_id
            LEFT JOIN brands b ON b.id = p.brand_id
            LEFT JOIN vendors v ON v.id = p.vendor_id
            WHERE p.status='active'
              AND p.featured=1
            ORDER BY p.id DESC
            LIMIT {$limit}
        ");
    }

    /**
     * Latest active products.
     */
    public function latest(int $limit = 12): array
    {
        return Database::fetchAll("
            SELECT
                p.*,
                c.name AS category_name,
                b.name AS brand_name,
                v.business_name AS vendor_name
            FROM products p
            LEFT JOIN categories c ON c.id = p.category_id
            LEFT JOIN brands b ON b.id = p.brand_id
            LEFT JOIN vendors v ON v.id = p.vendor_id
            WHERE p.status='active'
            ORDER BY p.id DESC
            LIMIT {$limit}
        ");
    }

    /**
     * Related products.
     */
    public function related(
        int $categoryId,
        int $productId,
        int $limit = 8
    ): array {
        return Database::fetchAll("
            SELECT *
            FROM products
            WHERE category_id=?
              AND id<>?
              AND status='active'
            ORDER BY RAND()
            LIMIT {$limit}
        ", [
            $categoryId,
            $productId
        ]);
    }

    /**
     * Products by category.
     */
    public function categoryProducts(string $slug): array
    {
        return Database::fetchAll("
            SELECT
                p.*,
                c.name AS category_name
            FROM products p
            INNER JOIN categories c
                ON c.id=p.category_id
            WHERE c.slug=?
              AND p.status='active'
            ORDER BY p.id DESC
        ", [$slug]);
    }

    /**
     * Best sellers.
     * Placeholder until order statistics are implemented.
     */
    public function bestSellers(int $limit = 12): array
    {
        return $this->featured($limit);
    }