<?php
declare(strict_types=1);
namespace Repositories;

final class ProductRepository extends BaseRepository
{
    public function all(array $filters): array
    {
        $params = [];
        $where = ['p.status = "active"'];
        if (!empty($filters['search'])) { $where[] = '(p.name LIKE :search OR p.short_description LIKE :search)'; $params['search'] = '%' . $filters['search'] . '%'; }
        if (!empty($filters['category'])) { $where[] = 'c.slug = :category'; $params['category'] = $filters['category']; }
        if (!empty($filters['min_price'])) { $where[] = 'p.price >= :min_price'; $params['min_price'] = $filters['min_price']; }
        if (!empty($filters['max_price'])) { $where[] = 'p.price <= :max_price'; $params['max_price'] = $filters['max_price']; }
        $limit = max(1, min(50, (int)($filters['limit'] ?? 12)));
        $offset = max(0, ((int)($filters['page'] ?? 1) - 1) * $limit);
        $sql = 'SELECT p.*, c.name category_name, c.slug category_slug,
                (SELECT image_url FROM product_images pi WHERE pi.product_id=p.id ORDER BY sort_order ASC LIMIT 1) primary_image
                FROM products p JOIN categories c ON c.id=p.category_id WHERE ' . implode(' AND ', $where) . '
                ORDER BY p.is_featured DESC, p.created_at DESC LIMIT ' . $limit . ' OFFSET ' . $offset;
        return $this->fetchAll($sql, $params);
    }

    public function findBySlug(string $slug): ?array
    {
        return $this->fetchOne('SELECT p.*, c.name category_name, c.slug category_slug FROM products p JOIN categories c ON c.id=p.category_id WHERE p.slug=:slug AND p.status="active" LIMIT 1', ['slug' => $slug]);
    }

    public function images(int $productId): array
    {
        return $this->fetchAll('SELECT * FROM product_images WHERE product_id=:id ORDER BY sort_order ASC', ['id' => $productId]);
    }

    public function related(int $productId, int $categoryId): array
    {
        return $this->fetchAll('SELECT id,name,slug,price,mrp,short_description,(SELECT image_url FROM product_images pi WHERE pi.product_id=products.id ORDER BY sort_order ASC LIMIT 1) primary_image FROM products WHERE id<>:id AND category_id=:category_id AND status="active" LIMIT 6', ['id' => $productId, 'category_id' => $categoryId]);
    }

    public function findManyByIds(array $ids): array
    {
        if (!$ids) return [];
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        return $this->fetchAll("SELECT id,name,slug,price,stock_quantity FROM products WHERE id IN ($placeholders) AND status='active'", array_values($ids));
    }
}
