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

$response = [
    'success' => false,
    'message' => 'Error desconocido.',
    'report_data' => [],
    'branches' => [], // To populate branch filter dropdown
    'totalPages' => 0,
    'currentPage' => 0,
    'totalRecords' => 0
];

try {
    // Get branch names for filter dropdown
    $stmt_branches = $conn->prepare("SELECT id, nombre FROM sucursales ORDER BY nombre ASC");
    $stmt_branches->execute();
    $result_branches = $stmt_branches->get_result();
    $branches = [];
    while ($row = $result_branches->fetch_assoc()) {
        $branches[] = $row;
    }
    $stmt_branches->close();
    $response['branches'] = $branches;

    // Pagination and search parameters
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $limit = 15; // Default limit
    $offset = ($page - 1) * $limit;

    $searchTerm = isset($_GET['term']) ? trim($_GET['term']) : '';
    $laboratoryFilter = isset($_GET['laboratory']) ? trim($_GET['laboratory']) : '';
    $branchFilter = isset($_GET['branch']) ? (int)$_GET['branch'] : 0;
    $presentationFilter = isset($_GET['presentation']) ? trim($_GET['presentation']) : '';

    $whereClauses = [];
    $params = [];
    $types = '';

    // Filter for records that have been updated (last_updated is not '0000-00-00 00:00:00')
    // This filter is now implicitly handled by the JOIN and GROUP BY, as we only select products with inventory entries.
    // If a product has no inventory entries, it won't appear in the report.

    // Search by product code, commercial name, generic name
    if (!empty($searchTerm)) {
        $whereClauses[] = "(p.CodigoInterno LIKE ? OR p.NombreComercial LIKE ? OR p.NombreGenerico LIKE ?)";
        $params[] = "%{$searchTerm}%";
        $params[] = "%{$searchTerm}%";
        $params[] = "%{$searchTerm}%";
        $types .= 'sss';
    }

    // Filter by laboratory
    if (!empty($laboratoryFilter)) {
        $whereClauses[] = "p.laboratorio LIKE ?";
        $params[] = "%{$laboratoryFilter}%";
        $types .= 's';
    }

    // Filter by branch (this will filter products that have inventory in the specified branch)
    if ($branchFilter > 0) {
        $whereClauses[] = "i.sucursal_id = ?";
        $params[] = $branchFilter;
        $types .= 'i';
    }

    // Filter by presentation (this will filter products that have inventory with the specified presentation)
    if (!empty($presentationFilter)) {
        $whereClauses[] = "i.presentacion LIKE ?";
        $params[] = "%{$presentationFilter}%";
        $types .= 's';
    }

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

    // Count total records (count distinct products that have inventory entries matching criteria)
    $countQuery = "SELECT COUNT(DISTINCT p.id) as total 
                   FROM productos p
                   JOIN inventario i ON p.id = i.lote_id
                   $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 inventory report data in "Conteo" structure
    $query = "SELECT 
                p.id, p.CodigoInterno, p.NombreComercial, p.laboratorio,
                GROUP_CONCAT(CONCAT(i.sucursal_id, ':', i.stock_unidad_base, ':', i.presentacion, ':', i.last_updated) SEPARATOR '|') as inventory_data
              FROM productos p
              JOIN inventario i ON p.id = i.lote_id
              $whereSql
              GROUP BY p.id
              ORDER BY p.NombreComercial ASC
              LIMIT ? OFFSET ?";

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

    // Combine all parameters for the main query
    $all_params = array_merge($params, [$limit, $offset]);
    $all_types = $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();

    $report_data = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $product = [
                'id' => $row['id'],
                'CodigoInterno' => $row['CodigoInterno'],
                'NombreComercial' => $row['NombreComercial'],
                'laboratorio' => $row['laboratorio'],
                '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,
                        'last_updated' => $last_updated
                    ];
                }
            }
            $report_data[] = $product;
        }
    }
    $stmt->close();

    $response['success'] = true;
    $response['message'] = 'Reporte de inventario cargado exitosamente.';
    $response['report_data'] = $report_data;
    $response['totalPages'] = $totalPages;
    $response['currentPage'] = $page;
    $response['totalRecords'] = $totalRows;

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

echo json_encode($response);
?>