<?php
/**
 * GET /api/productos/buscar.php
 * Search products with pagination and location filtering
 * Returns pure JSON (frontend handles rendering)
 */
require_once '../../config/database.php';
require_once '../../includes/helpers.php';

$pdo = getConnection();

$query     = trim($_GET['query'] ?? '');
$page      = max(1, (int)($_GET['page'] ?? 1));
$limit     = max(1, min(50, (int)($_GET['limit'] ?? 10)));
$offset    = ($page - 1) * $limit;
$lugarId   = isset($_GET['lugar_id']) && is_numeric($_GET['lugar_id']) ? (int)$_GET['lugar_id'] : null;
$id        = isset($_GET['id']) && is_numeric($_GET['id']) ? (int)$_GET['id'] : null;
$status    = $_GET['status'] ?? 'all'; // 'all', 'updated', 'not_updated'

// --- Filter Logic (2025+) ---
$statusFilter = "";
if ($status === 'updated') {
    $statusFilter = " AND EXISTS (SELECT 1 FROM producto_lugares_compra plc2 WHERE plc2.producto_id = p.id AND (plc2.fecha_ultima_compra >= '2025-01-01' OR plc2.fecha_creacion >= '2025-01-01'))";
} elseif ($status === 'not_updated') {
    $statusFilter = " AND NOT EXISTS (SELECT 1 FROM producto_lugares_compra plc2 WHERE plc2.producto_id = p.id AND (plc2.fecha_ultima_compra >= '2025-01-01' OR plc2.fecha_creacion >= '2025-01-01'))";
}

// Determine target location (General as fallback)
$targetLugarId = $lugarId;
$isGeneralView = false;
if (!$targetLugarId) {
    $stmtGen = $pdo->query("SELECT id FROM lugarcompra WHERE nombre = 'General' LIMIT 1");
    $gen = $stmtGen->fetch();
    if ($gen) {
        $targetLugarId = (int)$gen['id'];
        $isGeneralView = true;
    }
}

// Base query fields
$fields = "p.id, p.CodigoInterno, p.CodigoBarra, p.NombreComercial, p.NombreGenerico, p.laboratorio,
    COALESCE(pp_u.precio_venta, 0) as PrecioVentaUnidad,
    COALESCE(pp_blister.precio_venta, 0) as PrecioVentaBlister,
    COALESCE(pp_caja.precio_venta, 0) as PrecioVentaMayor,
    0 as PrecioVentaMixto,
    COALESCE(pcp.costo_promedio_unidad_base, 0) as CostoUnidad,
    0 as CostoBlister, 0 as CostoMayor, 0 as CostoMixto,
    '' as FechaIngreso, '' as FechaVencimiento, COALESCE(inv.stock_total, 0) as existencia, p.exento_iva, '' as presentaciones_venta,
    0 as id_lugarCompra, p.FuncionMedica,
    COALESCE(pp_caja.factor_conversion, 1) as FactorCaja,
    COALESCE(pp_blister.factor_conversion, 1) as FactorBlister";

$from = "FROM productos p
    LEFT JOIN inventario_sucursal inv ON p.id = inv.producto_id AND inv.sucursal_id = 1
    LEFT JOIN productos_costo_promedio pcp ON p.id = pcp.producto_id
    LEFT JOIN producto_presentaciones pp_u ON p.id = pp_u.producto_id AND pp_u.presentacion_id = 1 AND pp_u.activo = 1
    LEFT JOIN producto_presentaciones pp_caja ON p.id = pp_caja.producto_id AND pp_caja.presentacion_id = 3 AND pp_caja.activo = 1
    LEFT JOIN producto_presentaciones pp_blister ON p.id = pp_blister.producto_id AND pp_blister.presentacion_id = 2 AND pp_blister.activo = 1";

$products = [];
$totalProducts = 0;

try {
    if ($id) {
        // --- Single Product Fetch ---
        $sql = "SELECT $fields $from WHERE p.id = :id";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        $products = $stmt->fetchAll();
        $totalProducts = count($products);
    } elseif (!empty($query)) {
        // --- TIER 1: Exact code match ---
        $sql = "SELECT $fields $from WHERE (p.CodigoInterno = :q OR p.CodigoBarra = :q2) $statusFilter LIMIT :lim OFFSET :off";
        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':q', $query);
        $stmt->bindValue(':q2', $query);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $products = $stmt->fetchAll();

        if (!empty($products)) {
            $cnt = $pdo->prepare("SELECT COUNT(*) FROM productos p WHERE (p.CodigoInterno = :q OR p.CodigoBarra = :q2) $statusFilter");
            $cnt->execute(['q' => $query, 'q2' => $query]);
            $totalProducts = (int)$cnt->fetchColumn();
        } else {
            // --- TIER 2: Phrase match ---
            $like = "%{$query}%";
            $phraseWhere = "(p.NombreComercial LIKE :p1 OR p.NombreGenerico LIKE :p2 OR p.laboratorio LIKE :p3) $statusFilter";

            $cnt = $pdo->prepare("SELECT COUNT(*) FROM productos p WHERE $phraseWhere");
            $cnt->execute(['p1' => $like, 'p2' => $like, 'p3' => $like]);
            $totalProducts = (int)$cnt->fetchColumn();

            if ($totalProducts > 0) {
                $sql = "SELECT $fields $from WHERE $phraseWhere LIMIT :lim OFFSET :off";
                $stmt = $pdo->prepare($sql);
                $stmt->bindValue(':p1', $like);
                $stmt->bindValue(':p2', $like);
                $stmt->bindValue(':p3', $like);
                $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
                $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
                $stmt->execute();
                $products = $stmt->fetchAll();
            } else {
                // --- TIER 3: Term-by-term match ---
                $terms = array_filter(explode(' ', $query));
                if (!empty($terms)) {
                    $whereParts = [];
                    $params = [];
                    $i = 0;
                    foreach ($terms as $term) {
                        $key = "t{$i}";
                        $whereParts[] = "(p.NombreComercial LIKE :{$key}a OR p.NombreGenerico LIKE :{$key}b OR p.laboratorio LIKE :{$key}c OR p.CodigoInterno LIKE :{$key}d)";
                        $likeTerm = "%{$term}%";
                        $params["{$key}a"] = $likeTerm;
                        $params["{$key}b"] = $likeTerm;
                        $params["{$key}c"] = $likeTerm;
                        $params["{$key}d"] = $likeTerm;
                        $i++;
                    }
                    $whereSQL = "(" . implode(' AND ', $whereParts) . ") $statusFilter";

                    $cnt = $pdo->prepare("SELECT COUNT(*) FROM productos p WHERE $whereSQL");
                    $cnt->execute($params);
                    $totalProducts = (int)$cnt->fetchColumn();

                    $sql = "SELECT $fields $from WHERE $whereSQL LIMIT $limit OFFSET $offset";
                    $stmt = $pdo->prepare($sql);
                    $stmt->execute($params);
                    $products = $stmt->fetchAll();
                }
            }
        }
    } else {
        // --- ALL products ---
        $whereAll = "1=1 $statusFilter";
        $totalProducts = (int)$pdo->query("SELECT COUNT(*) FROM productos p WHERE $whereAll")->fetchColumn();
        $sql = "SELECT $fields $from WHERE $whereAll LIMIT :lim OFFSET :off";
        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $products = $stmt->fetchAll();
    }

    // --- EAGER LOAD: Hydrate location data ---
    if (!empty($products)) {
        $ids = array_column($products, 'id');
        $idsStr = implode(',', array_map('intval', $ids));

        $hydration = [];
        foreach ($ids as $pid) {
            $hydration[$pid] = [
                'lugares'       => [],
                'stock_total'   => 0,
                'costo_general' => null,
                'costo_ultimo'  => null,
                'specific'      => null,
            ];
        }

        $sqlH = "SELECT plc.*, lc.nombre as lugar_nombre
                 FROM producto_lugares_compra plc
                 JOIN lugarcompra lc ON plc.lugar_compra_id = lc.id
                 WHERE plc.producto_id IN ($idsStr) AND plc.activo = 1
                 ORDER BY plc.fecha_ultima_compra DESC, plc.id DESC";
        $resH = $pdo->query($sqlH);

        while ($r = $resH->fetch()) {
            $pid = (int)$r['producto_id'];
            $lid = (int)$r['lugar_compra_id'];
            $lName = $r['lugar_nombre'];

            // Unique locations list
            $key = "$lid:$lName";
            if (!in_array($key, array_column($hydration[$pid]['lugares'], 'key'))) {
                $hydration[$pid]['lugares'][] = [
                    'key'    => $key,
                    'id'     => $lid,
                    'nombre' => $lName,
                ];
            }

            // Stock (exclude General)
            if ($lName !== 'General') {
                $hydration[$pid]['stock_total'] += (int)$r['stock_facturado'] + (int)$r['stock_no_facturado'];
            }

            // General cost (first found = most recent)
            if ($lName === 'General' && $hydration[$pid]['costo_general'] === null) {
                $hydration[$pid]['costo_general'] = (float)($r['costo_unidad'] ?? 0);
            }

            // Latest non-General cost
            if ($lName !== 'General' && $hydration[$pid]['costo_ultimo'] === null) {
                $hydration[$pid]['costo_ultimo'] = (float)($r['costo_unidad'] ?? 0);
            }

            // Target location specific data
            if ($targetLugarId && $lid === $targetLugarId && $hydration[$pid]['specific'] === null) {
                $hydration[$pid]['specific'] = [
                    'costo_unidad'      => (float)$r['costo_unidad'],
                    'costo_blister'     => (float)$r['costo_blister'],
                    'costo_mayor'       => (float)$r['costo_mayor'],
                    'costo_mixto'       => (float)$r['costo_mixto'],
                    'stock_facturado'   => (int)$r['stock_facturado'],
                    'stock_no_facturado'=> (int)$r['stock_no_facturado'],
                    'fecha_ingreso'     => $r['fecha_ingreso'],
                    'fecha_ultima_compra' => $r['fecha_ultima_compra'],
                    'fecha_vencimiento' => $r['fecha_vencimiento'],
                    'exento_iva'        => (int)$r['exento_iva'],
                ];
            }

            // Keep track of the literal max update date for the product
            $hydration[$pid]['fecha_actualizacion'] = max($hydration[$pid]['fecha_actualizacion'] ?? '', $r['fecha_ultima_compra'] ?? '', $r['fecha_ingreso'] ?? '');
        }

        // Merge hydration into products
        foreach ($products as &$p) {
            $pid = (int)$p['id'];
            $h = $hydration[$pid];
            $p['lugares'] = $h['lugares'];
            $p['stock_total'] = $h['stock_total'];
            $p['costo_general'] = $h['costo_general'];
            $p['costo_ultimo'] = $h['costo_ultimo'];
            $p['fecha_actualizacion'] = $h['fecha_actualizacion'] ?? null;

            if ($h['specific']) {
                $p['specific'] = $h['specific'];
            }
        }
        unset($p);
    }

    $totalPages = $totalProducts > 0 ? (int)ceil($totalProducts / $limit) : 0;

    json_success([
        'products'    => $products,
        'pagination'  => [
            'page'        => $page,
            'limit'       => $limit,
            'total'       => $totalProducts,
            'total_pages' => $totalPages,
        ],
        'target_lugar_id' => $targetLugarId,
        'is_general_view' => $isGeneralView,
    ]);

} catch (Exception $e) {
    json_error('Error en búsqueda: ' . $e->getMessage());
}
