<?php
declare(strict_types=1);

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../core/Response.php';

$db = Database::getInstance();

/**
 * ==============================
 * PAGINATION
 * ==============================
 */
$page   = max(1, (int)($_GET['page'] ?? 1));
$limit  = max(1, min(50, (int)($_GET['limit'] ?? 10)));
$offset = ($page - 1) * $limit;

/**
 * ==============================
 * FILTERS
 * ==============================
 */
$where  = [];
$params = [];

if (!empty($_GET['category_id'])) {
    $where[] = 'p.category_id = :category_id';
    $params['category_id'] = (int)$_GET['category_id'];
}

if (!empty($_GET['primary_sleep_id'])) {
    $where[] = 'p.primary_sleep_id = :primary_sleep_id';
    $params['primary_sleep_id'] = (int)$_GET['primary_sleep_id'];
}

if (!empty($_GET['secondary_sleep_id'])) {
    $where[] = 'p.secondary_sleep_id = :secondary_sleep_id';
    $params['secondary_sleep_id'] = (int)$_GET['secondary_sleep_id'];
}

if (!empty($_GET['status'])) {
    $where[] = 'p.status = :status';
    $params['status'] = $_GET['status'];
}

if (!empty($_GET['approval_status'])) {
    $where[] = 'p.approval_status = :approval_status';
    $params['approval_status'] = $_GET['approval_status'];
}

if (!empty($_GET['search'])) {
    $where[] = 'p.product_name LIKE :search';
    $params['search'] = '%' . trim($_GET['search']) . '%';
}

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

/**
 * ==============================
 * TOTAL COUNT
 * ==============================
 */
$countStmt = $db->prepare("
    SELECT COUNT(*) 
    FROM retail_products p
    $whereSql
");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

/**
 * ==============================
 * FETCH PRODUCTS
 * ==============================
 */
$stmt = $db->prepare("
    SELECT
        p.id,
        p.product_name,
        p.slug,
        p.category_id,
        c.category_name,

        p.primary_sleep_id,
        p.secondary_sleep_id,

        p.short_description,
        p.full_description,
        p.brochure_file,

        p.meta_title,
        p.meta_description,
        p.meta_keywords,
        p.seo_schema,
        p.ai_seo_content,

        p.status,
        p.approval_status,
        p.rejection_reason,

        p.created_at,
        p.updated_at
    FROM retail_products p
    JOIN retail_categories c ON c.id = p.category_id
    $whereSql
    ORDER BY p.created_at DESC
    LIMIT :limit OFFSET :offset
");

foreach ($params as $key => $val) {
    $stmt->bindValue(':' . $key, $val);
}
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

/**
 * ==============================
 * ATTACH VARIANTS & IMAGES
 * ==============================
 */
$productIds = array_column($products, 'id');

$variantsByProduct = [];
$imagesByProduct   = [];

if (!empty($productIds)) {

    $in = implode(',', array_fill(0, count($productIds), '?'));

    /**
     * VARIANTS
     */
    $variantStmt = $db->prepare("
        SELECT
            id,
            product_id,
            variant_name,
            sku,
            size,
            firmness,
            thickness,
            mrp,
            selling_price,
            specifications,
            status
        FROM retail_product_variants
        WHERE product_id IN ($in)
        ORDER BY sort_order ASC
    ");
    $variantStmt->execute($productIds);

    foreach ($variantStmt->fetchAll(PDO::FETCH_ASSOC) as $v) {
        $v['specifications'] = $v['specifications']
            ? json_decode($v['specifications'], true)
            : null;

        $variantsByProduct[$v['product_id']][] = $v;
    }

    /**
     * IMAGES
     */
    $imageStmt = $db->prepare("
        SELECT
            product_id,
            variant_id,
            image_path,
            alt_text,
            caption,
            is_featured
        FROM retail_product_images
        WHERE product_id IN ($in)
          AND status = 'Active'
        ORDER BY is_featured DESC, sort_order ASC
    ");
    $imageStmt->execute($productIds);

    foreach ($imageStmt->fetchAll(PDO::FETCH_ASSOC) as $img) {
        $imagesByProduct[$img['product_id']][] = $img;
    }
}

/**
 * ==============================
 * MERGE DATA
 * ==============================
 */
foreach ($products as &$product) {
    $product['variants'] = $variantsByProduct[$product['id']] ?? [];
    $product['images']   = $imagesByProduct[$product['id']] ?? [];
}

/**
 * ==============================
 * RESPONSE
 * ==============================
 */
Response::success('Retail products fetched successfully', [
    'pagination' => [
        'total' => $total,
        'page'  => $page,
        'limit' => $limit
    ],
    'products' => $products
]);
