<?php
require_once '../../config/database.php';
require_once '../../includes/helpers.php';

$pdo = getConnection();

try {
    $query = isset($_GET['query']) ? trim($_GET['query']) : '';
    $laboratorio = isset($_GET['laboratorio']) ? trim($_GET['laboratorio']) : '';
    $page = max(1, (int)($_GET['page'] ?? 1));
    $limit = min(100, max(10, (int)($_GET['limit'] ?? 50)));
    $offset = ($page - 1) * $limit;

    $where = [];
    $params = [];

    if ($query !== '') {
        $q = "%$query%";
        $where[] = "(p.CodigoInterno LIKE :q1 OR p.CodigoBarra LIKE :q2 OR p.NombreComercial LIKE :q3 OR CONCAT(p.NombreComercial, ' ', p.laboratorio) LIKE :q4)";
        $params[':q1'] = $q;
        $params[':q2'] = $q;
        $params[':q3'] = $q;
        $params[':q4'] = $q;
    }

    if ($laboratorio !== '') {
        $where[] = "p.laboratorio = :lab";
        $params[':lab'] = $laboratorio;
    }

    $whereClause = count($where) > 0 ? 'WHERE ' . implode(' AND ', $where) : '';

    // Total count
    $countSql = "SELECT COUNT(*) FROM productos p $whereClause";
    $stmtCount = $pdo->prepare($countSql);
    $stmtCount->execute($params);
    $total = (int)$stmtCount->fetchColumn();

    // Products
    $sql = "SELECT p.id, p.CodigoInterno, p.CodigoBarra, p.NombreComercial, p.NombreGenerico,
                   p.laboratorio, p.exento_iva, COALESCE(p.receta_controlada, 0) as receta_controlada,
                   p.id_categoria, p.estado
            FROM productos p
            $whereClause
            ORDER BY p.NombreComercial ASC
            LIMIT :limit OFFSET :offset";

    $stmt = $pdo->prepare($sql);
    foreach ($params as $k => $v) {
        $stmt->bindValue($k, $v, PDO::PARAM_STR);
    }
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $productos = $stmt->fetchAll();

    // Get presentations and categories for each product
    $productIds = array_column($productos, 'id');
    $presentaciones = [];
    $categorias = [];

    if (!empty($productIds)) {
        $ids = implode(',', $productIds);
        $stmtPres = $pdo->query("
            SELECT pp.producto_id, pr.id as presentacion_id, pr.nombre as presentacion_nombre,
                   pp.factor_conversion, pp.precio_venta, pp.es_facturable
            FROM producto_presentaciones pp
            JOIN presentaciones pr ON pp.presentacion_id = pr.id
            WHERE pp.producto_id IN ($ids) AND pp.activo = 1
            ORDER BY pp.producto_id, pr.id
        ");
        while ($row = $stmtPres->fetch()) {
            $pid = $row['producto_id'];
            if (!isset($presentaciones[$pid])) $presentaciones[$pid] = [];
            $presentaciones[$pid][] = [
                'id' => (int)$row['presentacion_id'],
                'nombre' => $row['presentacion_nombre'],
                'factor_conversion' => (int)$row['factor_conversion'],
                'precio_venta' => (float)$row['precio_venta'],
                'es_facturable' => (int)$row['es_facturable'],
            ];
        }
    }

    // All categories
    $stmtCat = $pdo->query("SELECT id, nombre FROM categorias ORDER BY nombre");
    $categorias = $stmtCat->fetchAll();

    // All distinct labs (for filter dropdown)
    $stmtLabs = $pdo->query("SELECT DISTINCT laboratorio FROM productos WHERE laboratorio IS NOT NULL AND laboratorio != '' ORDER BY laboratorio");
    $laboratorios = $stmtLabs->fetchAll(PDO::FETCH_COLUMN);

    $data = [];
    foreach ($productos as $p) {
        $pid = $p['id'];
        $data[] = [
            'id' => (int)$pid,
            'CodigoInterno' => $p['CodigoInterno'],
            'CodigoBarra' => $p['CodigoBarra'],
            'NombreComercial' => $p['NombreComercial'],
            'NombreGenerico' => $p['NombreGenerico'],
            'laboratorio' => $p['laboratorio'],
            'exento_iva' => (int)$p['exento_iva'],
            'receta_controlada' => (int)$p['receta_controlada'],
            'id_categoria' => $p['id_categoria'] ? (int)$p['id_categoria'] : null,
            'estado' => (int)$p['estado'],
            'presentaciones' => $presentaciones[$pid] ?? [],
        ];
    }

    json_success([
        'productos' => $data,
        'categorias' => $categorias,
        'laboratorios' => $laboratorios,
        'total' => $total,
        'page' => $page,
        'pages' => max(1, ceil($total / $limit)),
    ]);

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