<?php
/**
 * almacen_ventashielo_api.php
 * ============================================================
 * Puente VentasHielo -> BReport para módulo Almacén.
 * 
 * Objetivo de esta versión:
 * - Leer pedidos AUTORIZADA desde VentasHielo.
 * - Al surtir, guardar primero el pedido en BReport:
 *      sales_customers / sales_orders / sales_order_items
 * - Generar salida de almacén usando tablas BReport:
 *      outbounds / outbound_items / inventory_moves
 * - Regresar JSON limpio, evitando el error NO_JSON.
 * - El PDF se genera desde BReport con orden_entrega_pdf.php.
 * 
 * Requiere:
 * - includes/db_ventashielo.php con una conexión PDO disponible.
 * - En VentasHielo products.breport_product_id configurado para
 *   relacionar productos con BReport.
 * ============================================================
 */

declare(strict_types=1);

if (!function_exists('vh_json_exit')) {
    function vh_json_exit(array $data, int $code = 200): void {
        http_response_code($code);
        header('Content-Type: application/json; charset=utf-8');
        echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        exit;
    }
}

if (!function_exists('vh_read_json')) {
    function vh_read_json(): array {
        $raw = file_get_contents('php://input');
        $arr = json_decode($raw ?: '[]', true);
        return is_array($arr) ? $arr : [];
    }
}

if (!function_exists('vh_arr')) {
    function vh_arr(array $a, string $k, $default = null) {
        return array_key_exists($k, $a) ? $a[$k] : $default;
    }
}

if (!function_exists('vh_table_exists')) {
    function vh_table_exists(PDO $pdo, string $table): bool {
        $st = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = ?
        ");
        $st->execute([$table]);
        return (int)$st->fetchColumn() > 0;
    }
}

if (!function_exists('vh_column_exists')) {
    function vh_column_exists(PDO $pdo, string $table, string $column): bool {
        $st = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = ?
              AND column_name = ?
        ");
        $st->execute([$table, $column]);
        return (int)$st->fetchColumn() > 0;
    }
}

if (!function_exists('vh_get_pdo')) {
    function vh_get_pdo(): PDO {
        $candidateFunctions = [
            'pdo_ventashielo',
            'ventashielo_pdo',
            'get_ventashielo_pdo',
            'pdoVentasHielo',
            'ventasHieloPdo',
        ];

        foreach ($candidateFunctions as $fn) {
            if (function_exists($fn)) {
                $conn = $fn();
                if ($conn instanceof PDO) {
                    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    return $conn;
                }
            }
        }

        $candidateGlobals = [
            'pdoVentasHielo',
            'pdoVentashielo',
            'pdo_ventashielo',
            'ventasHieloPdo',
            'ventashieloPdo',
            'vhPdo',
            'pdoVH',
            'connVentasHielo',
            'connVentashielo',
        ];

        foreach ($candidateGlobals as $name) {
            if (isset($GLOBALS[$name]) && $GLOBALS[$name] instanceof PDO) {
                $GLOBALS[$name]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                return $GLOBALS[$name];
            }
        }

        throw new RuntimeException('No se encontró conexión PDO a VentasHielo. Valida includes/db_ventashielo.php.');
    }
}

if (!function_exists('vh_folio')) {
    function vh_folio(array $order): string {
        $sales = trim((string)($order['sales_order'] ?? $order['sales_order_ref'] ?? ''));
        if ($sales !== '') return $sales;

        $ov = trim((string)($order['ov_system'] ?? ''));
        if ($ov !== '') return $ov;

        $id = (int)($order['id'] ?? 0);
        return $id > 0 ? 'PW-' . $id : 'PW';
    }
}

if (!function_exists('vh_table_columns')) {
    function vh_table_columns(PDO $pdo, string $table): array {
        $st = $pdo->prepare("
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = ?
        ");
        $st->execute([$table]);
        $cols = [];
        foreach ($st->fetchAll(PDO::FETCH_COLUMN) as $c) {
            $cols[(string)$c] = true;
        }
        return $cols;
    }
}

if (!function_exists('vh_insert_dynamic')) {
    function vh_insert_dynamic(PDO $pdo, string $table, array $values): int {
        $colsAvailable = vh_table_columns($pdo, $table);
        $cols = [];
        $params = [];
        foreach ($values as $col => $val) {
            if (isset($colsAvailable[$col])) {
                $cols[] = $col;
                $params[] = $val;
            }
        }
        if (!$cols) {
            throw new RuntimeException('No hay columnas válidas para insertar en ' . $table);
        }

        $sql = "INSERT INTO {$table} (" . implode(', ', $cols) . ")
                VALUES (" . implode(', ', array_fill(0, count($cols), '?')) . ")";
        $st = $pdo->prepare($sql);
        $st->execute($params);
        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('vh_update_dynamic')) {
    function vh_update_dynamic(PDO $pdo, string $table, array $values, string $where, array $whereParams): void {
        $colsAvailable = vh_table_columns($pdo, $table);
        $sets = [];
        $params = [];
        foreach ($values as $col => $val) {
            if (isset($colsAvailable[$col])) {
                $sets[] = "{$col} = ?";
                $params[] = $val;
            }
        }
        if (!$sets) return;

        $sql = "UPDATE {$table} SET " . implode(', ', $sets) . " WHERE {$where}";
        $st = $pdo->prepare($sql);
        $st->execute(array_merge($params, $whereParams));
    }
}

if (!function_exists('vh_fetch_order')) {
    function vh_fetch_order(PDO $vh, int $orderId = 0, string $folio = ''): ?array {
        $folio = trim($folio);
        $lookup = $folio !== '' ? $folio : ($orderId > 0 ? 'PW-' . $orderId : '');

        $st = $vh->prepare("
            SELECT
                o.id,
                o.customer_id,
                o.delivery_address,
                o.delivery_date,
                o.delivery_time,
                o.delivery_instructions,
                o.comments,
                o.status,
                o.total,
                o.sales_order,
                o.ov_system,
                o.sales_at,
                o.Qty_pza,
                o.breport_sales_order_id,
                u.name AS cliente,
                u.phone AS cliente_phone,
                u.address AS cliente_address,
                ca.label AS address_label
            FROM orders o
            JOIN users u ON u.id = o.customer_id
            LEFT JOIN customer_addresses ca
                   ON ca.user_id = o.customer_id
                  AND ca.address = o.delivery_address
            WHERE o.id = ?
               OR o.sales_order = ?
               OR o.ov_system = ?
               OR CONCAT('PW-', o.id) = ?
               OR CONCAT('VH-', o.id) = ?
            ORDER BY
              CASE WHEN o.id = ? THEN 0 ELSE 1 END,
              o.id DESC
            LIMIT 1
        ");
        $st->execute([$orderId, $lookup, $lookup, $lookup, $lookup, $orderId]);
        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vh_fetch_items')) {
    function vh_fetch_items(PDO $vh, int $vhOrderId): array {
        $st = $vh->prepare("
            SELECT
                oi.id,
                oi.order_id,
                oi.product_id,
                oi.quantity,
                oi.unit_price,
                p.name AS product_name,
                p.description AS product_description,
                p.breport_product_id,
                p.breport_company_id,
                p.breport_location_id
            FROM order_items oi
            JOIN products p ON p.id = oi.product_id
            WHERE oi.order_id = ?
            ORDER BY p.name
        ");
        $st->execute([$vhOrderId]);
        return $st->fetchAll(PDO::FETCH_ASSOC);
    }
}

if (!function_exists('vh_breport_product_id')) {
    function vh_breport_product_id(PDO $pdo, array $item, int $companyId): int {
        $mapped = (int)($item['breport_product_id'] ?? 0);
        if ($mapped > 0) {
            $st = $pdo->prepare("
                SELECT id
                FROM products
                WHERE id = ?
                  AND company_id = ?
                  AND active = 1
                LIMIT 1
            ");
            $st->execute([$mapped, $companyId]);
            $id = (int)($st->fetchColumn() ?: 0);
            if ($id > 0) return $id;
        }

        $name = trim((string)($item['product_name'] ?? ''));
        if ($name !== '') {
            $st = $pdo->prepare("
                SELECT id
                FROM products
                WHERE company_id = ?
                  AND active = 1
                  AND (
                      UPPER(TRIM(name)) = UPPER(TRIM(?))
                      OR UPPER(TRIM(sku)) = UPPER(TRIM(?))
                  )
                LIMIT 1
            ");
            $st->execute([$companyId, $name, $name]);
            $id = (int)($st->fetchColumn() ?: 0);
            if ($id > 0) return $id;
        }

        // Producto no relacionado en BReport.
        // Antes esto detenía todo el proceso con error 500.
        // Ahora se regresa 0 para omitir este artículo del descuento de almacén.
        return 0;
    }
}

if (!function_exists('vh_find_or_create_customer')) {
    function vh_find_or_create_customer(PDO $pdo, array $order, int $companyId): int {
        $name = trim((string)($order['cliente'] ?? 'Cliente VentasHielo'));
        if ($name === '') $name = 'Cliente VentasHielo';
        $phone = trim((string)($order['cliente_phone'] ?? ''));

        $hasCompany = vh_column_exists($pdo, 'sales_customers', 'company_id');

        if ($hasCompany) {
            $st = $pdo->prepare("
                SELECT id
                FROM sales_customers
                WHERE company_id = ?
                  AND business_name = ?
                ORDER BY id DESC
                LIMIT 1
            ");
            $st->execute([$companyId, $name]);
        } else {
            $st = $pdo->prepare("
                SELECT id
                FROM sales_customers
                WHERE business_name = ?
                ORDER BY id DESC
                LIMIT 1
            ");
            $st->execute([$name]);
        }

        $id = (int)($st->fetchColumn() ?: 0);
        if ($id > 0) return $id;

        return vh_insert_dynamic($pdo, 'sales_customers', [
            'company_id'     => $companyId,
            'business_name'  => $name,
            'contact_name'   => $name,
            'phone'          => $phone !== '' ? $phone : null,
            'active'         => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }
}

if (!function_exists('vh_sync_sales_order_to_breport')) {
    function vh_sync_sales_order_to_breport(PDO $pdo, PDO $vh, array $vhOrder, array $vhItems, int $companyId, int $locationId): array {
        $folio = vh_folio($vhOrder);
        $customerId = vh_find_or_create_customer($pdo, $vhOrder, $companyId);

        $st = $pdo->prepare("
            SELECT id
            FROM sales_orders
            WHERE company_id = ?
              AND location_id = ?
              AND sales_order_ref = ?
            ORDER BY id DESC
            LIMIT 1
        ");
        $st->execute([$companyId, $locationId, $folio]);
        $salesOrderId = (int)($st->fetchColumn() ?: 0);

        $commentsParts = [];
        if (!empty($vhOrder['delivery_instructions'])) {
            $commentsParts[] = 'Instrucciones: ' . (string)$vhOrder['delivery_instructions'];
        }
        if (!empty($vhOrder['comments'])) {
            $commentsParts[] = 'Comentarios: ' . (string)$vhOrder['comments'];
        }
        $comments = implode("\n", $commentsParts);

        $orderValues = [
            'company_id'                => $companyId,
            'location_id'               => $locationId,
            'customer_id'               => $customerId,
            'sales_order_ref'           => $folio,
            'requested_delivery_date'   => $vhOrder['delivery_date'] ?: null,
            'requested_delivery_time'   => $vhOrder['delivery_time'] ?: null,
            'delivery_address'          => $vhOrder['delivery_address'] ?: ($vhOrder['cliente_address'] ?? null),
            'comments'                  => $comments !== '' ? $comments : null,
            'total'                     => (float)($vhOrder['total'] ?? 0),
            'status'                    => 'AUTORIZADA',
            'approved_at'               => date('Y-m-d H:i:s'),
            'created_at'                => date('Y-m-d H:i:s'),
            'updated_at'                => date('Y-m-d H:i:s'),
        ];

        if ($salesOrderId > 0) {
            $st = $pdo->prepare("SELECT outbound_id FROM sales_orders WHERE id = ? LIMIT 1");
            $st->execute([$salesOrderId]);
            $existingOutbound = (int)($st->fetchColumn() ?: 0);
            if ($existingOutbound > 0) {
                throw new RuntimeException('Este pedido ya fue ligado a una salida de almacén.');
            }

            vh_update_dynamic($pdo, 'sales_orders', $orderValues, 'id = ?', [$salesOrderId]);
        } else {
            $salesOrderId = vh_insert_dynamic($pdo, 'sales_orders', $orderValues);
        }

        $st = $pdo->prepare("DELETE FROM sales_order_items WHERE order_id = ?");
        $st->execute([$salesOrderId]);

        $skippedItems = [];
        $syncedItems = 0;

        foreach ($vhItems as $it) {
            $breportProductId = vh_breport_product_id($pdo, $it, $companyId);
            $qty = (float)($it['quantity'] ?? 0);
            if ($qty <= 0) continue;

            if ($breportProductId <= 0) {
                $skippedItems[] = [
                    'product_id' => (int)($it['product_id'] ?? 0),
                    'product_name' => (string)($it['product_name'] ?? ''),
                    'requested_qty' => $qty,
                    'reason' => 'Producto sin relación en BReport; no se descuenta de almacén.',
                ];
                continue;
            }

            $unitPrice = (float)($it['unit_price'] ?? 0);

            vh_insert_dynamic($pdo, 'sales_order_items', [
                'order_id'    => $salesOrderId,
                'product_id'  => $breportProductId,
                'legacy_product_id' => (int)($it['product_id'] ?? 0),
                'qty'         => $qty,
                'unit_price'  => $unitPrice,
                'line_total'  => $qty * $unitPrice,
                'notes'       => 'VentasHielo #' . (int)$vhOrder['id'] . ' / Producto VH #' . (int)$it['product_id'],
                'created_at'  => date('Y-m-d H:i:s'),
                'updated_at'  => date('Y-m-d H:i:s'),
            ]);
            $syncedItems++;
        }

        try {
            if (vh_column_exists($vh, 'orders', 'breport_sales_order_id')) {
                $st = $vh->prepare("
                    UPDATE orders
                    SET breport_sales_order_id = ?,
                        breport_sync_status = 'synced',
                        breport_sync_error = NULL,
                        breport_synced_at = NOW()
                    WHERE id = ?
                ");
                $st->execute([$salesOrderId, (int)$vhOrder['id']]);
            }
        } catch (Throwable $e) {
            // No se detiene la salida si no se puede actualizar VentasHielo.
        }

        return [
            'sales_order_id' => $salesOrderId,
            'skipped_items' => $skippedItems,
            'synced_items' => $syncedItems,
        ];
    }
}

if (!function_exists('vh_get_product_row')) {
    function vh_get_product_row(PDO $pdo, int $companyId, int $productId): ?array {
        $st = $pdo->prepare("\n            SELECT id, sku, name, uom\n            FROM products\n            WHERE id = ? AND company_id = ? AND active = 1\n            LIMIT 1\n        ");
        $st->execute([$productId, $companyId]);
        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

if (!function_exists('vh_get_warehouse_id_by_code')) {
    function vh_get_warehouse_id_by_code(PDO $pdo, int $companyId, int $locationId, string $code): int {
        $st = $pdo->prepare("\n            SELECT id\n            FROM warehouses\n            WHERE company_id = ?\n              AND location_id = ?\n              AND active = 1\n              AND UPPER(code) = UPPER(?)\n            LIMIT 1\n        ");
        $st->execute([$companyId, $locationId, $code]);
        return (int)($st->fetchColumn() ?: 0);
    }
}

if (!function_exists('vh_build_product_warehouse_stock')) {
    function vh_build_product_warehouse_stock(PDO $pdo, int $companyId, int $locationId): array {
        $st = $pdo->prepare("\n            SELECT\n                product_id,\n                warehouse_id,\n                SUM(CASE\n                    WHEN move_type IN ('IN','ADJ') THEN qty\n                    WHEN move_type = 'OUT' THEN -qty\n                    ELSE 0\n                END) AS qty\n            FROM inventory_moves\n            WHERE company_id = ?\n              AND location_id = ?\n              AND warehouse_id IS NOT NULL\n            GROUP BY product_id, warehouse_id\n        ");
        $st->execute([$companyId, $locationId]);

        $rows = [];
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $pid = (int)$r['product_id'];
            $wid = (int)$r['warehouse_id'];
            if ($pid <= 0 || $wid <= 0) continue;
            $rows[$pid][$wid] = (float)$r['qty'];
        }
        return $rows;
    }
}

if (!function_exists('vh_allocate_inventory_out')) {
    function vh_allocate_inventory_out(PDO $pdo, int $companyId, int $locationId, int $productId, float $qty): array {
        /**
         * Asignación real para VentasHielo.
         * - BARRA 150 KILOS: CF1 y después CF2.
         * - Bolsas/producto terminado: CF3.
         * No toma producto de CUB, MERMA, APROV-MERMA, SOB-CUB ni SIN-WH.
         */
        $qty = round($qty, 3);
        if ($qty <= 0) return [];

        $product = vh_get_product_row($pdo, $companyId, $productId);
        if (!$product) {
            throw new RuntimeException('Producto BReport inválido para salida VentasHielo. ID: ' . $productId);
        }

        $sku = strtoupper(trim((string)$product['sku']));
        $preferredCodes = ($sku === 'BARRA 150 KILOS') ? ['CF1', 'CF2'] : ['CF3'];
        $stockByWh = vh_build_product_warehouse_stock($pdo, $companyId, $locationId);

        $alloc = [];
        $remaining = $qty;
        foreach ($preferredCodes as $code) {
            $wid = vh_get_warehouse_id_by_code($pdo, $companyId, $locationId, $code);
            if ($wid <= 0) continue;
            $available = (float)($stockByWh[$productId][$wid] ?? 0.0);
            if ($available <= 0) continue;
            $take = min($remaining, $available);
            if ($take > 0) {
                $alloc[] = ['warehouse_id' => $wid, 'warehouse_code' => $code, 'qty' => $take];
                $remaining = round($remaining - $take, 3);
            }
            if ($remaining <= 0.0001) break;
        }

        if ($remaining > 0.0001) {
            $label = ($sku === 'BARRA 150 KILOS') ? 'CF1/CF2' : 'CF3';
            throw new RuntimeException('Inventario insuficiente en almacén disponible (' . $label . ') para ' . $sku . '. Faltante: ' . number_format($remaining, 3, '.', ''));
        }

        return $alloc;
    }
}

if (!function_exists('vh_available_for_product')) {
    function vh_available_for_product(PDO $pdo, int $companyId, int $locationId, int $productId): float {
        $product = vh_get_product_row($pdo, $companyId, $productId);
        if (!$product) return 0.0;
        $sku = strtoupper(trim((string)$product['sku']));
        $codes = ($sku === 'BARRA 150 KILOS') ? ['CF1', 'CF2'] : ['CF3'];
        $stockByWh = vh_build_product_warehouse_stock($pdo, $companyId, $locationId);

        $total = 0.0;
        foreach ($codes as $code) {
            $wid = vh_get_warehouse_id_by_code($pdo, $companyId, $locationId, $code);
            if ($wid > 0) $total += (float)($stockByWh[$productId][$wid] ?? 0.0);
        }
        return round($total, 3);
    }
}

if (!function_exists('vh_build_dispatch_preview')) {
    function vh_build_dispatch_preview(PDO $pdo, array $vhItems, int $companyId, int $locationId): array {
        /**
         * Prevalida un pedido VentasHielo sin escribir nada.
         * Regresa qué sí puede surtirse y qué queda pendiente por falta de mapeo/inventario.
         */
        $requestedByProduct = [];
        $pendingItems = [];
        $mappedLines = [];

        foreach ($vhItems as $it) {
            $requestedQty = (float)($it['quantity'] ?? 0);
            if ($requestedQty <= 0) continue;

            $breportProductId = vh_breport_product_id($pdo, $it, $companyId);
            if ($breportProductId <= 0) {
                $pendingItems[] = [
                    'product_id' => 0,
                    'vh_product_id' => (int)($it['product_id'] ?? 0),
                    'product_name' => (string)($it['product_name'] ?? ''),
                    'sku' => '',
                    'requested_qty' => $requestedQty,
                    'available_qty' => 0.0,
                    'dispatched_qty' => 0.0,
                    'missing_qty' => $requestedQty,
                    'reason' => 'Producto VentasHielo sin relación con BReport; no se puede descontar de almacén.',
                ];
                continue;
            }

            $prod = vh_get_product_row($pdo, $companyId, $breportProductId);
            if (!$prod) {
                $pendingItems[] = [
                    'product_id' => $breportProductId,
                    'vh_product_id' => (int)($it['product_id'] ?? 0),
                    'product_name' => (string)($it['product_name'] ?? ''),
                    'sku' => '',
                    'requested_qty' => $requestedQty,
                    'available_qty' => 0.0,
                    'dispatched_qty' => 0.0,
                    'missing_qty' => $requestedQty,
                    'reason' => 'Producto BReport inactivo o inválido.',
                ];
                continue;
            }

            if (!isset($requestedByProduct[$breportProductId])) {
                $requestedByProduct[$breportProductId] = [
                    'product_id' => $breportProductId,
                    'sku' => (string)$prod['sku'],
                    'product_name' => (string)$prod['name'],
                    'uom' => (string)($prod['uom'] ?? ''),
                    'requested_qty' => 0.0,
                ];
            }
            $requestedByProduct[$breportProductId]['requested_qty'] += $requestedQty;
            $mappedLines[] = $it;
        }

        $dispatchByProduct = [];
        $dispatchableItems = [];

        foreach ($requestedByProduct as $pid => $row) {
            $requested = round((float)$row['requested_qty'], 3);
            $available = vh_available_for_product($pdo, $companyId, $locationId, (int)$pid);
            $dispatchQty = min($requested, max(0.0, $available));
            $dispatchQty = round($dispatchQty, 3);
            $missing = round($requested - $dispatchQty, 3);

            if ($dispatchQty > 0) {
                $dispatchByProduct[(int)$pid] = $dispatchQty;
                $dispatchableItems[] = [
                    'product_id' => (int)$pid,
                    'sku' => (string)$row['sku'],
                    'product_name' => (string)$row['product_name'],
                    'uom' => (string)$row['uom'],
                    'requested_qty' => $requested,
                    'available_qty' => $available,
                    'dispatched_qty' => $dispatchQty,
                    'missing_qty' => $missing,
                    'reason' => $missing > 0 ? 'Inventario parcial disponible.' : 'Disponible completo.',
                ];
            }

            if ($missing > 0.0001) {
                $pendingItems[] = [
                    'product_id' => (int)$pid,
                    'vh_product_id' => 0,
                    'sku' => (string)$row['sku'],
                    'product_name' => (string)$row['product_name'],
                    'uom' => (string)$row['uom'],
                    'requested_qty' => $requested,
                    'available_qty' => $available,
                    'dispatched_qty' => $dispatchQty,
                    'missing_qty' => $missing,
                    'reason' => $available <= 0 ? 'Sin inventario disponible en almacén comercial.' : 'Inventario parcial disponible.',
                ];
            }
        }

        return [
            'dispatch_by_product' => $dispatchByProduct,
            'dispatchable_items' => $dispatchableItems,
            'pending_items' => $pendingItems,
            'has_pending' => !empty($pendingItems),
            'has_dispatchable' => !empty($dispatchByProduct),
            'mapped_lines' => $mappedLines,
        ];
    }
}

if (!function_exists('vh_build_inventory_snapshot_fallback')) {
    function vh_build_inventory_snapshot_fallback(PDO $pdo, int $companyId, int $locationId): array {
        $st = $pdo->prepare("\n            SELECT id AS product_id\n            FROM products\n            WHERE company_id = ? AND active = 1\n        ");
        $st->execute([$companyId]);
        $rows = [];
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $pid = (int)$r['product_id'];
            $rows[] = [
                'product_id' => $pid,
                'on_hand' => vh_available_for_product($pdo, $companyId, $locationId, $pid),
            ];
        }
        return ['rows' => $rows];
    }
}

if (!function_exists('vh_create_dispatch_from_breport_order')) {
    function vh_create_dispatch_from_breport_order(
        PDO $pdo,
        array $u,
        int $companyId,
        int $locationId,
        int $salesOrderId,
        string $workDate,
        int $channelId,
        string $reference,
        string $notes,
        string $driverName,
        string $vehicle,
        string $departAt,
        array $dispatchQtyByProduct = [],
        array $pendingItems = []
    ): array {
        $st = $pdo->prepare("\n            SELECT\n                o.*,\n                c.business_name,\n                c.contact_name,\n                c.phone\n            FROM sales_orders o\n            JOIN sales_customers c ON c.id = o.customer_id\n            WHERE o.id = ?\n              AND o.company_id = ?\n              AND o.location_id = ?\n            FOR UPDATE\n        ");
        $st->execute([$salesOrderId, $companyId, $locationId]);
        $order = $st->fetch(PDO::FETCH_ASSOC);

        if (!$order) throw new RuntimeException('Pedido BReport no encontrado después de sincronizar.');
        if ((string)$order['status'] !== 'AUTORIZADA') throw new RuntimeException('Solo se pueden surtir pedidos AUTORIZADA.');
        if (!empty($order['outbound_id'])) throw new RuntimeException('Este pedido ya fue ligado a una salida de almacén.');

        $st = $pdo->prepare("\n            SELECT\n                i.product_id,\n                i.qty,\n                p.sku,\n                p.name,\n                p.uom\n            FROM sales_order_items i\n            JOIN products p ON p.id = i.product_id\n            WHERE i.order_id = ?\n            ORDER BY p.sku DESC\n        ");
        $st->execute([$salesOrderId]);
        $items = $st->fetchAll(PDO::FETCH_ASSOC);

        if (!$items && !$dispatchQtyByProduct) {
            throw new RuntimeException('No hay artículos relacionados a BReport para generar salida.');
        }

        if (!$dispatchQtyByProduct) {
            foreach ($items as $it) {
                $pid = (int)$it['product_id'];
                $requestedQty = (float)$it['qty'];
                $available = vh_available_for_product($pdo, $companyId, $locationId, $pid);
                $dispatchQty = min($requestedQty, max(0.0, $available));
                if ($dispatchQty > 0) $dispatchQtyByProduct[$pid] = round($dispatchQty, 3);
                if ($dispatchQty < $requestedQty) {
                    $pendingItems[] = [
                        'product_id' => $pid,
                        'product_name' => (string)($it['name'] ?? $it['sku'] ?? ''),
                        'sku' => (string)($it['sku'] ?? ''),
                        'requested_qty' => $requestedQty,
                        'available_qty' => $available,
                        'dispatched_qty' => $dispatchQty,
                        'missing_qty' => $requestedQty - $dispatchQty,
                        'reason' => $available <= 0 ? 'Sin inventario disponible.' : 'Inventario parcial disponible.',
                    ];
                }
            }
        }

        $dispatchQtyByProduct = array_filter($dispatchQtyByProduct, fn($q) => (float)$q > 0);
        if (!$dispatchQtyByProduct) {
            throw new RuntimeException('No existe inventario disponible para ningún artículo relacionado. No se generó salida.');
        }

        $folio = trim((string)($order['sales_order_ref'] ?? ''));
        if ($reference === '') $reference = $folio !== '' ? $folio : ('PED-' . $salesOrderId);

        $notesExtra = [];
        $notesExtra[] = 'Pedido VentasHielo/BReport #' . $salesOrderId;
        if ($folio !== '') $notesExtra[] = 'Folio ventas: ' . $folio;
        if (!empty($order['business_name'])) $notesExtra[] = 'Cliente/Destino: ' . $order['business_name'];
        if ($driverName !== '') $notesExtra[] = 'Chofer: ' . $driverName;
        if ($vehicle !== '') $notesExtra[] = 'Unidad: ' . $vehicle;
        if ($departAt !== '') $notesExtra[] = 'Salida: ' . $departAt;
        if ($notes !== '') $notesExtra[] = 'Notas: ' . $notes;
        if (!empty($pendingItems)) $notesExtra[] = 'Surtido parcial autorizado por usuario. Pendientes/omitidos: ' . count($pendingItems);
        $fullNotes = mb_substr(implode(' | ', $notesExtra), 0, 255);

        $st = $pdo->prepare("\n            INSERT INTO outbounds (\n                company_id, location_id, outbound_date, channel_id, status,\n                created_by, reference, notes\n            ) VALUES (\n                ?, ?, ?, ?, 'POSTED',\n                ?, ?, ?\n            )\n        ");
        $st->execute([$companyId, $locationId, $workDate, $channelId, (int)($u['id'] ?? 0), $reference, $fullNotes]);
        $outboundId = (int)$pdo->lastInsertId();

        $stItem = $pdo->prepare("\n            INSERT INTO outbound_items (outbound_id, product_id, qty)\n            VALUES (?, ?, ?)\n        ");

        $stMove = $pdo->prepare("\n            INSERT INTO inventory_moves (\n                company_id, location_id, warehouse_id, work_date, product_id, qty, move_type,\n                channel_id, ref_type, ref_id, notes, created_by, created_at\n            ) VALUES (\n                ?, ?, ?, ?, ?, ?, 'OUT',\n                ?, 'VENTASHIELO', ?, ?, ?, NOW()\n            )\n        ");

        $movementRows = [];
        foreach ($dispatchQtyByProduct as $pid => $qtyToDispatch) {
            $pid = (int)$pid;
            $qtyToDispatch = round((float)$qtyToDispatch, 3);
            if ($pid <= 0 || $qtyToDispatch <= 0) continue;

            $prod = vh_get_product_row($pdo, $companyId, $pid);
            $stItem->execute([$outboundId, $pid, $qtyToDispatch]);

            foreach (vh_allocate_inventory_out($pdo, $companyId, $locationId, $pid, $qtyToDispatch) as $alloc) {
                $stMove->execute([
                    $companyId,
                    $locationId,
                    $alloc['warehouse_id'],
                    $workDate,
                    $pid,
                    (float)$alloc['qty'],
                    $channelId,
                    $outboundId,
                    $fullNotes,
                    (int)($u['id'] ?? 0)
                ]);
                $movementRows[] = [
                    'product_id' => $pid,
                    'sku' => (string)($prod['sku'] ?? ''),
                    'product_name' => (string)($prod['name'] ?? ''),
                    'warehouse_id' => (int)$alloc['warehouse_id'],
                    'warehouse_code' => (string)($alloc['warehouse_code'] ?? ''),
                    'move_type' => 'OUT',
                    'qty' => (float)$alloc['qty'],
                    'ref_type' => 'VENTASHIELO',
                    'ref_id' => $outboundId,
                ];
            }
        }

        vh_update_dynamic($pdo, 'sales_orders', [
            'outbound_id'  => $outboundId,
            'status'       => 'SURTIENDO',
            'fulfilled_at' => date('Y-m-d H:i:s'),
            'updated_at'   => date('Y-m-d H:i:s'),
        ], 'id = ?', [$salesOrderId]);

        if (vh_table_exists($pdo, 'sales_order_status_log')) {
            try {
                vh_insert_dynamic($pdo, 'sales_order_status_log', [
                    'order_id'            => $salesOrderId,
                    'old_status'          => 'AUTORIZADA',
                    'new_status'          => 'SURTIENDO',
                    'changed_by_user_id'  => (int)($u['id'] ?? 0),
                    'changed_by_role'     => 'ALMACEN',
                    'comment'             => 'Salida VentasHielo generada. Outbound #' . $outboundId,
                    'created_at'          => date('Y-m-d H:i:s'),
                ]);
            } catch (Throwable $e) {}
        }

        return [
            'outbound_id' => $outboundId,
            'sales_order_id' => $salesOrderId,
            'reference' => $reference,
            'pending_items' => array_values($pendingItems),
            'movement_rows' => $movementRows,
            'dispatched_count' => count($dispatchQtyByProduct),
        ];
    }
}

if (!function_exists('handle_ventashielo_bridge_actions')) {
    function handle_ventashielo_bridge_actions(PDO $pdo, array $u, int $companyId, int $locationId, bool $canWrite, string $action): void {
        if (!in_array($action, ['vh_authorized', 'vh_order_detail', 'vh_dispatch'], true)) {
            return;
        }

        try {
            $vh = vh_get_pdo();

            if ($action === 'vh_authorized') {
                $page = max(1, (int)($_GET['page'] ?? 1));
                $pageSize = max(1, min(100, (int)($_GET['pageSize'] ?? 10)));
                $offset = ($page - 1) * $pageSize;

                $count = $vh->query("
                    SELECT COUNT(*)
                    FROM orders
                    WHERE LOWER(status) = 'autorizada'
                ")->fetchColumn();
                $totalRaw = (int)$count;

                $st = $vh->prepare("
                    SELECT
                        o.id,
                        o.customer_id,
                        o.delivery_address,
                        o.delivery_date,
                        o.delivery_time,
                        o.delivery_instructions,
                        o.comments,
                        o.status,
                        o.total,
                        o.sales_order,
                        o.ov_system,
                        o.Qty_pza,
                        o.breport_sales_order_id,
                        u.name AS cliente,
                        u.phone AS cliente_phone,
                        u.address AS cliente_address,
                        ca.label AS address_label,
                        (
                            SELECT COUNT(*)
                            FROM order_items oi
                            WHERE oi.order_id = o.id
                        ) AS items_count
                    FROM orders o
                    JOIN users u ON u.id = o.customer_id
                    LEFT JOIN customer_addresses ca
                           ON ca.user_id = o.customer_id
                          AND ca.address = o.delivery_address
                    WHERE LOWER(o.status) = 'autorizada'
                    ORDER BY o.id DESC
                    LIMIT {$pageSize} OFFSET {$offset}
                ");
                $st->execute();
                $rows = $st->fetchAll(PDO::FETCH_ASSOC);

                // Oculta pedidos que ya tengan salida generada en BReport.
                foreach ($rows as $k => $r) {
                    $folio = vh_folio($r);
                    $chk = $pdo->prepare("
                        SELECT outbound_id, status
                        FROM sales_orders
                        WHERE company_id = ?
                          AND location_id = ?
                          AND sales_order_ref = ?
                        ORDER BY id DESC
                        LIMIT 1
                    ");
                    $chk->execute([$companyId, $locationId, $folio]);
                    $br = $chk->fetch(PDO::FETCH_ASSOC);
                    if ($br && !empty($br['outbound_id'])) {
                        unset($rows[$k]);
                    }
                }
                $rows = array_values($rows);

                vh_json_exit([
                    'ok' => true,
                    'rows' => $rows,
                    'page' => $page,
                    'pageSize' => $pageSize,
                    'pages' => max(1, (int)ceil($totalRaw / $pageSize)),
                    'total' => $totalRaw,
                ]);
            }

            if ($action === 'vh_order_detail') {
                $orderId = (int)($_GET['order_id'] ?? 0);
                $folio = trim((string)($_GET['folio'] ?? ''));
                if ($orderId <= 0 && $folio === '') {
                    vh_json_exit(['ok' => false, 'error' => 'order_id o folio requerido.'], 400);
                }

                $order = vh_fetch_order($vh, $orderId, $folio);
                if (!$order) {
                    vh_json_exit(['ok' => false, 'error' => 'Pedido VentasHielo no encontrado.'], 404);
                }

                $items = vh_fetch_items($vh, (int)$order['id']);
                foreach ($items as &$it) {
                    $it['breport_product_id'] = (int)($it['breport_product_id'] ?? 0);
                }
                unset($it);

                vh_json_exit([
                    'ok' => true,
                    'order' => $order,
                    'items' => $items,
                ]);
            }

            if ($action === 'vh_dispatch') {
                if (!$canWrite) {
                    vh_json_exit(['ok' => false, 'error' => 'Sin permiso para generar salida. Valida rol admin/warehouse/almacen.'], 403);
                }

                $d = vh_read_json();
                $vhOrderId  = (int)vh_arr($d, 'order_id', 0);
                $workDate   = trim((string)vh_arr($d, 'work_date', date('Y-m-d')));
                $channelId  = (int)vh_arr($d, 'channel_id', 0);
                $reference  = trim((string)vh_arr($d, 'reference', ''));
                $notes      = trim((string)vh_arr($d, 'notes', ''));
                $driverName = trim((string)vh_arr($d, 'driver_name', ''));
                $vehicle    = trim((string)vh_arr($d, 'vehicle_unit', ''));
                $departAt   = trim((string)vh_arr($d, 'depart_at', ''));
                $confirmPartial = (bool)vh_arr($d, 'confirm_partial', false);

                if ($vhOrderId <= 0) {
                    vh_json_exit(['ok' => false, 'error' => 'order_id requerido.'], 400);
                }
                if ($channelId <= 0) {
                    vh_json_exit(['ok' => false, 'error' => 'Canal requerido.'], 400);
                }

                $vhOrder = vh_fetch_order($vh, $vhOrderId, $reference);
                if (!$vhOrder) {
                    vh_json_exit(['ok' => false, 'error' => 'Pedido VentasHielo no encontrado.'], 404);
                }
                if (mb_strtolower((string)$vhOrder['status']) !== 'autorizada') {
                    vh_json_exit(['ok' => false, 'error' => 'Solo se pueden surtir pedidos AUTORIZADA.'], 409);
                }

                $vhItems = vh_fetch_items($vh, (int)$vhOrder['id']);
                if (!$vhItems) {
                    vh_json_exit(['ok' => false, 'error' => 'El pedido VentasHielo no tiene artículos.'], 409);
                }

                $preview = vh_build_dispatch_preview($pdo, $vhItems, $companyId, $locationId);
                if (empty($preview['has_dispatchable'])) {
                    vh_json_exit([
                        'ok' => false,
                        'error' => 'No existe inventario disponible para ningún artículo relacionado. No se generó salida.',
                        'pending_items' => (array)($preview['pending_items'] ?? []),
                        'dispatchable_items' => [],
                    ], 409);
                }

                if (!empty($preview['has_pending']) && !$confirmPartial) {
                    vh_json_exit([
                        'ok' => true,
                        'requires_confirmation' => true,
                        'message' => 'El pedido tiene artículos sin inventario suficiente o sin relación. Puedes generar salida solo por los artículos disponibles.',
                        'vh_order_id' => (int)$vhOrder['id'],
                        'reference' => $reference !== '' ? $reference : vh_folio($vhOrder),
                        'dispatchable_items' => (array)($preview['dispatchable_items'] ?? []),
                        'pending_items' => (array)($preview['pending_items'] ?? []),
                    ]);
                }

                $pdo->beginTransaction();
                try {
                    $sync = vh_sync_sales_order_to_breport($pdo, $vh, $vhOrder, $vhItems, $companyId, $locationId);
                    $salesOrderId = (int)($sync['sales_order_id'] ?? 0);
                    $pendingForDispatch = array_merge(
                        (array)($sync['skipped_items'] ?? []),
                        (array)($preview['pending_items'] ?? [])
                    );
                    $dispatch = vh_create_dispatch_from_breport_order(
                        $pdo,
                        $u,
                        $companyId,
                        $locationId,
                        $salesOrderId,
                        $workDate !== '' ? $workDate : date('Y-m-d'),
                        $channelId,
                        $reference !== '' ? $reference : vh_folio($vhOrder),
                        $notes,
                        $driverName,
                        $vehicle,
                        $departAt,
                        (array)($preview['dispatch_by_product'] ?? []),
                        $pendingForDispatch
                    );

                    $pdo->commit();

                    $pendingItems = array_merge(
                        (array)($sync['skipped_items'] ?? []),
                        (array)($dispatch['pending_items'] ?? [])
                    );

                    vh_json_exit([
                        'ok' => true,
                        'vh_order_id' => (int)$vhOrder['id'],
                        'sales_order_id' => (int)$dispatch['sales_order_id'],
                        'outbound_id' => (int)$dispatch['outbound_id'],
                        'reference' => (string)$dispatch['reference'],
                        'pending_items' => $pendingItems,
                        'skipped_items' => (array)($sync['skipped_items'] ?? []),
                        'dispatched_count' => (int)($dispatch['dispatched_count'] ?? 0),
                        'dispatchable_items' => (array)($preview['dispatchable_items'] ?? []),
                        'movement_rows' => (array)($dispatch['movement_rows'] ?? []),
                        'pdf_url' => './orden_entrega_pdf.php?order_id=' . (int)$dispatch['sales_order_id'] . '&outbound_id=' . (int)$dispatch['outbound_id'] . '&folio=' . rawurlencode((string)$dispatch['reference']),
                    ]);
                } catch (Throwable $e) {
                    if ($pdo->inTransaction()) $pdo->rollBack();

                    try {
                        if (vh_column_exists($vh, 'orders', 'breport_sync_status')) {
                            $st = $vh->prepare("
                                UPDATE orders
                                SET breport_sync_status = 'error',
                                    breport_sync_error = ?,
                                    breport_synced_at = NOW()
                                WHERE id = ?
                            ");
                            $st->execute([$e->getMessage(), (int)$vhOrder['id']]);
                        }
                    } catch (Throwable $ignore) {}

                    vh_json_exit(['ok' => false, 'error' => $e->getMessage()], 500);
                }
            }
        } catch (Throwable $e) {
            vh_json_exit(['ok' => false, 'error' => $e->getMessage()], 500);
        }
    }
}
