<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/branch.php';

try {
    $db = getDBConnection();
    
    // Get query parameters
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 20;
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $categoria_id = isset($_GET['categoria_id']) ? (int)$_GET['categoria_id'] : 0;
    $sucursal_id = getCurrentBranchId();
    
    $offset = ($page - 1) * $limit;
    
    // Build the WHERE clause
    $whereConditions = [];
    $params = [];
    
    if ($sucursal_id) {
        $whereConditions[] = 'p.sucursal_id = ?';
        $params[] = $sucursal_id;
    }

    if (!empty($search)) {
        // Búsqueda robusta en múltiples campos
        $searchConditions = [
            'prod.CodigoInterno LIKE ?',
            'prod.CodigoBarra LIKE ?',
            'dp.nombre_comercial LIKE ?',
            'prod.laboratorio LIKE ?'
        ];
        $whereConditions[] = '(' . implode(' OR ', $searchConditions) . ')';
        
        // Agregar el mismo parámetro de búsqueda 4 veces (una por cada campo)
        $searchParam = "%{$search}%";
        $params[] = $searchParam;
        $params[] = $searchParam;
        $params[] = $searchParam;
        $params[] = $searchParam;
    }

    if ($categoria_id > 0) {
        $whereConditions[] = 'prod.id_categoria = ?';
        $params[] = $categoria_id;
    }
    
    $whereClause = !empty($whereConditions) ? ' WHERE ' . implode(' AND ', $whereConditions) : '';
    
    // Get total count
    $countSql = "SELECT COUNT(*) as total 
                 FROM detalle_pedidos dp
                 INNER JOIN pedidos p ON dp.pedido_id = p.id
                 INNER JOIN productos prod ON dp.producto_id = prod.id" . $whereClause;
    
    $countStmt = $db->prepare($countSql);
    if (!empty($params)) {
        $countStmt->execute($params);
    } else {
        $countStmt->execute();
    }
    $totalProducts = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Get ordered products with pagination
    $sql = "SELECT 
                dp.*,
                p.id as pedido_id,
                p.fecha_pedido,
                p.estado as pedido_estado,
                s.nombre as sucursal_nombre,
                u.nombre as usuario_nombre,
                prod.laboratorio,
                prod.id_categoria
            FROM detalle_pedidos dp
            INNER JOIN pedidos p ON dp.pedido_id = p.id
            INNER JOIN sucursales s ON p.sucursal_id = s.id
            INNER JOIN usuarios u ON p.usuario_id = u.id
            INNER JOIN productos prod ON dp.producto_id = prod.id" . 
            $whereClause .
            " ORDER BY p.fecha_pedido DESC, dp.id DESC
            LIMIT ? OFFSET ?";
    
    // Add limit and offset to params
    $params[] = $limit;
    $params[] = $offset;
    
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate pagination meta
    $totalPages = ceil($totalProducts / $limit);
    $hasNext = $page < $totalPages;
    $hasPrev = $page > 1;
    
    echo json_encode([
        'success' => true,
        'data' => $products,
        'pagination' => [
            'current_page' => $page,
            'total_pages' => $totalPages,
            'total_products' => (int)$totalProducts,
            'per_page' => $limit,
            'showing_from' => $offset + 1,
            'showing_to' => min($offset + $limit, $totalProducts),
            'has_next' => $hasNext,
            'has_prev' => $hasPrev
        ]
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Error al obtener productos: ' . $e->getMessage()
    ]);
}
?>