<?php
header('Content-Type: application/json; charset=utf-8');
require_once '../db_connection.php';

// --- Parámetros de Paginación y Búsqueda ---
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 15;
$offset = ($page - 1) * $limit;
$searchTerm = isset($_GET['term']) ? trim($_GET['term']) : '';

// --- Construcción de la Consulta Dinámica ---
$whereClauses = [];
$where_params = [];
$where_types = '';
$order_by = 'ORDER BY NombreComercial ASC';
$order_by_params = [];
$order_by_types = '';

if (!empty($searchTerm)) {
    $searchWords = explode(' ', $searchTerm);
    $relevance_score_clauses = [];

    foreach ($searchWords as $word) {
        if (empty(trim($word))) continue;
        
        $likeWord = "%{$word}%";
        $startsWithWord = "{$word}%";
        $exactWord = $word;

        // Add conditions for WHERE clause (AND for multiple words)
        $wordWhereClauses = [];
        $wordWhereClauses[] = "NombreComercial LIKE ?";
        $where_params[] = $likeWord;
        $wordWhereClauses[] = "NombreGenerico LIKE ?";
        $where_params[] = $likeWord;
        $wordWhereClauses[] = "CodigoInterno LIKE ?";
        $where_params[] = $likeWord;
        $wordWhereClauses[] = "CodigoBarra LIKE ?";
        $where_params[] = $likeWord;
        $wordWhereClauses[] = "laboratorio LIKE ?";
        $where_params[] = $likeWord;
        $where_types .= 'sssss';
        $whereClauses[] = "(" . implode(' OR ', $wordWhereClauses) . ")";

        // Add relevance scoring for ORDER BY
        $relevance_score_clauses[] = "CASE 
            WHEN NombreComercial = ? THEN 100 
            WHEN CodigoInterno = ? THEN 90 
            WHEN CodigoBarra = ? THEN 85 
            WHEN NombreComercial LIKE ? THEN 80 
            WHEN CodigoInterno LIKE ? THEN 75 
            WHEN CodigoBarra LIKE ? THEN 70 
            WHEN NombreGenerico LIKE ? THEN 60 
            WHEN laboratorio LIKE ? THEN 50 
            ELSE 0 
        END";
        $order_by_params[] = $exactWord; // NombreComercial exact
        $order_by_params[] = $exactWord; // CodigoInterno exact
        $order_by_params[] = $exactWord; // CodigoBarra exact
        $order_by_params[] = $startsWithWord; // NombreComercial starts with
        $order_by_params[] = $startsWithWord; // CodigoInterno starts with
        $order_by_params[] = $startsWithWord; // CodigoBarra starts with
        $order_by_params[] = $likeWord; // NombreGenerico like
        $order_by_params[] = $likeWord; // laboratorio like
        $order_by_types .= 'ssssssss';
    }
    
    if (!empty($relevance_score_clauses)) {
        $order_by = 'ORDER BY (' . implode(' + ', $relevance_score_clauses) . ') DESC, NombreComercial ASC';
    }
}

$whereSql = '';
if (!empty($whereClauses)) {
    $whereSql = 'WHERE ' . implode(' AND ', $whereClauses);
}

// --- Conteo Total de Registros (para paginación) ---
$countQuery = "SELECT COUNT(id) as total FROM productos $whereSql";
$stmtCount = $conn->prepare($countQuery);

if (!empty($where_params)) {
    $stmtCount->bind_param($where_types, ...$where_params);
}

$stmtCount->execute();
$resultCount = $stmtCount->get_result();
$totalRows = $resultCount->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);
$stmtCount->close();

// --- Consulta Principal de Productos ---
$query = "SELECT 
            id, CodigoInterno, CodigoBarra, NombreComercial, NombreGenerico, 
            laboratorio, CostoUnidad, CostoBlister, CostoMayor, CostoMixto, 
            PrecioVentaUnidad, PrecioVentaBlister, PrecioVentaMayor, PrecioVentaMixto, 
            FechaIngreso, FechaVencimiento, existencia, exento_iva
          FROM productos
          $whereSql
          $order_by
          LIMIT ? OFFSET ?";

$stmt = $conn->prepare($query);

// Combinar todos los parámetros para la consulta principal
$data_params = array_merge($where_params, $order_by_params);
$data_types = $where_types . $order_by_types;

// Añadir los parámetros de LIMIT y OFFSET
$data_params[] = $limit;
$data_params[] = $offset;
$data_types .= 'ii';

// Usar call_user_func_array para enlazar un número dinámico de parámetros
if (!empty($data_types)) {
    $bind_names = [];
    $bind_names[] = $data_types;
    for ($i = 0; $i < count($data_params); $i++) {
        $bind_names[] = &$data_params[$i];
    }
    call_user_func_array([$stmt, 'bind_param'], $bind_names);
}

$stmt->execute();
$result = $stmt->get_result();

$products = [];
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $products[] = $row;
    }
}
$stmt->close();
$conn->close();

// --- Respuesta JSON ---
echo json_encode([
    'products' => $products,
    'totalPages' => $totalPages,
    'currentPage' => $page,
    'totalRecords' => $totalRows
]);
?>