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

$response = [
    'success' => false,
    'message' => 'Error desconocido.',
    'products' => [],
    'branches' => [],
    'totalPages' => 0,
    'currentPage' => 0,
    'totalRecords' => 0
];

try {
    // Get branch names
    $branch_ids = [3, 5, 2, 4];
    $branch_placeholders = implode(',', array_fill(0, count($branch_ids), '?'));
    $stmt_branches = $conn->prepare("SELECT id, nombre FROM sucursales WHERE id IN ($branch_placeholders) ORDER BY FIELD(id, 3, 5, 2, 4)");
    $stmt_branches->bind_param(str_repeat('i', count($branch_ids)), ...$branch_ids);
    $stmt_branches->execute();
    $result_branches = $stmt_branches->get_result();
    $branches = [];
    while ($row = $result_branches->fetch_assoc()) {
        $branches[] = $row;
    }
    $stmt_branches->close();

    // Pagination and search parameters
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $limit = 15; // Same limit as products.js
    $offset = ($page - 1) * $limit;
    $searchTerm = isset($_GET['term']) ? trim($_GET['term']) : '';

    $whereClauses = [];
    $params = [];
    $types = '';
    $order_by = 'ORDER BY p.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[] = "p.CodigoInterno LIKE ?";
            $params[] = $likeWord;
            $wordWhereClauses[] = "p.NombreComercial LIKE ?";
            $params[] = $likeWord;
            $wordWhereClauses[] = "p.NombreGenerico LIKE ?";
            $params[] = $likeWord;
            $wordWhereClauses[] = "p.laboratorio LIKE ?";
            $params[] = $likeWord;
            $types .= 'ssss';
            $whereClauses[] = "(" . implode(' OR ', $wordWhereClauses) . ")";

            // Add relevance scoring for ORDER BY
            $relevance_score_clauses[] = "CASE 
                WHEN p.NombreComercial = ? THEN 1000 
                WHEN p.CodigoInterno = ? THEN 950 
                WHEN p.NombreComercial LIKE ? THEN 900 
                WHEN p.CodigoInterno LIKE ? THEN 850 
                WHEN p.NombreGenerico LIKE ? THEN 700 
                WHEN p.laboratorio LIKE ? THEN 600 
                ELSE 0 
            END";
            $order_by_params[] = $exactWord; // NombreComercial exact
            $order_by_params[] = $exactWord; // CodigoInterno exact
            $order_by_params[] = $startsWithWord; // NombreComercial starts with
            $order_by_params[] = $startsWithWord; // CodigoInterno starts with
            $order_by_params[] = $likeWord; // NombreGenerico like
            $order_by_params[] = $likeWord; // laboratorio like
            $order_by_types .= 'ssssss';
        }
        
        // Add a score for exact match of the entire search term
        $relevance_score_clauses[] = "CASE 
            WHEN p.NombreComercial = ? THEN 2000 
            WHEN p.CodigoInterno = ? THEN 1900 
            WHEN p.CodigoBarra = ? THEN 1800 
            ELSE 0 
        END";
        $order_by_params[] = $searchTerm; // Exact match of full search term
        $order_by_params[] = $searchTerm; // Exact match of full search term
        $order_by_params[] = $searchTerm; // Exact match of full search term
        $order_by_types .= 'sss';

        if (!empty($relevance_score_clauses)) {
            $order_by = 'ORDER BY (' . implode(' + ', $relevance_score_clauses) . ') DESC, p.NombreComercial ASC';
        }
    }

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

    // Count total records
    $countQuery = "SELECT COUNT(p.id) as total FROM productos p $whereSql";
    $stmtCount = $conn->prepare($countQuery);
    if (!empty($params)) {
        $stmtCount->bind_param($types, ...$params);
    }
    $stmtCount->execute();
    $totalRows = $stmtCount->get_result()->fetch_assoc()['total'];
    $totalPages = ceil($totalRows / $limit);
    $stmtCount->close();

    // Main query to get products and their inventory (assuming an 'inventario' table)
    // This is a placeholder. You might need to adjust this query based on your actual inventory table structure.
    $query = "SELECT 
                p.id, p.CodigoInterno, p.NombreComercial, p.laboratorio, p.FechaVencimiento,
                GROUP_CONCAT(CONCAT(i.sucursal_id, ':', i.stock_unidad_base, ':', i.presentacion, ':', i.last_updated) SEPARATOR '|') as inventory_data
              FROM productos p
              LEFT JOIN inventario i ON p.id = i.lote_id
              $whereSql
              GROUP BY p.id
              $order_by
              LIMIT ? OFFSET ?";

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

    // Combine all parameters for the main query
    $all_params = array_merge($params, $order_by_params, [$limit, $offset]);
    $all_types = $types . $order_by_types . 'ii';

    if (!empty($all_types)) {
        $bind_names = [];
        $bind_names[] = $all_types;
        for ($i = 0; $i < count($all_params); $i++) {
            $bind_names[] = &$all_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()) {
            $product = [
                'id' => $row['id'],
                'CodigoInterno' => $row['CodigoInterno'],
                'NombreComercial' => $row['NombreComercial'],
                'laboratorio' => $row['laboratorio'],
                'FechaVencimiento' => $row['FechaVencimiento'],
                'inventory' => []
            ];

            // Parse inventory data
            if (!empty($row['inventory_data'])) {
                $inventory_items = explode('|', $row['inventory_data']);
                foreach ($inventory_items as $item) {
                    list($branch_id, $quantity, $presentation, $last_updated) = explode(':', $item);
                    $product['inventory'][(int)$branch_id] = [
                        'quantity' => (int)$quantity,
                        'presentation' => $presentation, // Add presentation
                        'last_updated' => $last_updated
                    ];
                }
            }
            $products[] = $product;
        }
    }
    $stmt->close();

    $response = [
        'success' => true,
        'message' => 'Inventario cargado exitosamente.',
        'products' => $products,
        'branches' => $branches,
        'totalPages' => $totalPages,
        'currentPage' => $page,
        'totalRecords' => $totalRows
    ];

} catch (Exception $e) {
    $response['message'] = 'Error en el servidor: ' . $e->getMessage();
    error_log('Error en get_inventory.php: ' . $e->getMessage());
} finally {
    if (isset($conn) && $conn->ping()) {
        $conn->close();
    }
}

echo json_encode($response);
?>