<?php
declare(strict_types=1);
/**
 * BReport · Carga Ruta Botella
 * ------------------------------------------------------------
 * Carga operativa por ruta de reparto.
 * - No usa simulador de Caja Seca/Torton.
 * - La unidad de reparto no tiene capacidad fija por ahora.
 * - Permite cargar cantidades por presentación/artículo.
 * - Puede abrir una orden logística generada desde Almacén Botella.
 * - No afecta inventario. El movimiento real se realizará después en el flujo operativo.
 */
require_once __DIR__ . '/includes/auth.php';
require_once __DIR__ . '/includes/helpers.php';
require_once __DIR__ . '/includes/csrf.php';

require_scope();
ensure_session();

const BREPORT_TIMEZONE = 'America/Matamoros';
date_default_timezone_set(BREPORT_TIMEZONE);

$pdo = pdo();
$u = current_user();
$sc = scope();
$companyId = (int)($sc['company_id'] ?? 0);
$locationId = (int)($sc['location_id'] ?? 0);
$action = (string)($_GET['action'] ?? '');

function cr_h(?string $s): string { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function cr_jexit(array $d, int $code=200): void { http_response_code($code); header('Content-Type: application/json; charset=utf-8'); echo json_encode($d, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES); exit; }
function cr_read_json(): array { $raw=file_get_contents('php://input'); $d=json_decode($raw?:'[]', true); return is_array($d)?$d:[]; }
function cr_need_csrf(): void { if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'GET') return; $hdr=(string)($_SERVER['HTTP_X_CSRF'] ?? ''); if($hdr==='') cr_jexit(['ok'=>false,'error'=>'Falta encabezado X-CSRF'],419); csrf_verify($hdr); }
function cr_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; }
function cr_col_exists(PDO $pdo, string $table, string $col): bool { $st=$pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=? AND column_name=?"); $st->execute([$table,$col]); return (int)$st->fetchColumn()>0; }
function cr_add_col(PDO $pdo, string $table, string $col, string $def): void { if(!cr_col_exists($pdo,$table,$col)) { try{$pdo->exec("ALTER TABLE `$table` ADD COLUMN $def");}catch(Throwable $e){} } }
function cr_add_index(PDO $pdo, string $table, string $idx, string $def): void { $st=$pdo->prepare("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name=? AND index_name=?"); $st->execute([$table,$idx]); if((int)$st->fetchColumn()===0){ try{$pdo->exec("ALTER TABLE `$table` ADD $def");}catch(Throwable $e){} } }
function cr_now(): string { return (new DateTimeImmutable('now', new DateTimeZone(BREPORT_TIMEZONE)))->format('Y-m-d H:i:s'); }
function cr_work_date(): string { $now=new DateTimeImmutable('now', new DateTimeZone(BREPORT_TIMEZONE)); if((int)$now->format('H')<7) $now=$now->modify('-1 day'); return $now->format('Y-m-d'); }
function cr_scope_is_botella(PDO $pdo, int $companyId, int $locationId): bool { $st=$pdo->prepare("SELECT CONCAT(COALESCE(c.name,''),' ',COALESCE(l.name,'')) FROM companies c JOIN locations l ON l.company_id=c.id AND l.id=? WHERE c.id=? LIMIT 1"); $st->execute([$locationId,$companyId]); return str_contains(mb_strtoupper((string)$st->fetchColumn(),'UTF-8'), 'BOTELLA'); }
function cr_route_warehouse_code(int $routeNo): string { return 'RUTA-' . str_pad((string)$routeNo, 2, '0', STR_PAD_LEFT); }

function cr_ensure_schema(PDO $pdo): void {
    if(!cr_table_exists($pdo,'route_truck_load_plans')) {
        $pdo->exec("CREATE TABLE route_truck_load_plans (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            company_id BIGINT UNSIGNED NOT NULL,
            location_id BIGINT UNSIGNED NOT NULL,
            order_id BIGINT UNSIGNED DEFAULT NULL,
            route_no INT NOT NULL,
            route_code VARCHAR(80) DEFAULT NULL,
            route_name VARCHAR(160) DEFAULT NULL,
            work_date DATE NOT NULL,
            vehicle_type VARCHAR(30) NOT NULL DEFAULT 'REPARTO',
            vehicle_label VARCHAR(80) NOT NULL DEFAULT 'Unidad reparto',
            capacity_pallets INT NOT NULL DEFAULT 0,
            vehicle_unit VARCHAR(80) DEFAULT NULL,
            driver_name VARCHAR(120) DEFAULT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'BORRADOR',
            notes VARCHAR(255) DEFAULT NULL,
            created_by BIGINT UNSIGNED DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_by BIGINT UNSIGNED DEFAULT NULL,
            updated_at DATETIME DEFAULT NULL,
            PRIMARY KEY(id),
            KEY idx_rtlp_scope_status(company_id, location_id, status),
            KEY idx_rtlp_route_date(company_id, location_id, route_no, work_date),
            KEY idx_rtlp_order(company_id, location_id, order_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    } else {
        cr_add_col($pdo,'route_truck_load_plans','order_id',"order_id BIGINT UNSIGNED DEFAULT NULL AFTER location_id");
        cr_add_col($pdo,'route_truck_load_plans','vehicle_unit',"vehicle_unit VARCHAR(80) DEFAULT NULL AFTER capacity_pallets");
        cr_add_col($pdo,'route_truck_load_plans','driver_name',"driver_name VARCHAR(120) DEFAULT NULL AFTER vehicle_unit");
        cr_add_index($pdo,'route_truck_load_plans','idx_rtlp_scope_status',"INDEX idx_rtlp_scope_status(company_id, location_id, status)");
        cr_add_index($pdo,'route_truck_load_plans','idx_rtlp_route_date',"INDEX idx_rtlp_route_date(company_id, location_id, route_no, work_date)");
        cr_add_index($pdo,'route_truck_load_plans','idx_rtlp_order',"INDEX idx_rtlp_order(company_id, location_id, order_id)");
    }
    if(!cr_table_exists($pdo,'route_truck_load_plan_slots')) {
        $pdo->exec("CREATE TABLE route_truck_load_plan_slots (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            plan_id BIGINT UNSIGNED NOT NULL,
            slot_no INT NOT NULL DEFAULT 1,
            order_item_id BIGINT UNSIGNED DEFAULT NULL,
            product_id BIGINT UNSIGNED NOT NULL,
            packaging_option_id BIGINT UNSIGNED DEFAULT NULL,
            product_label VARCHAR(180) DEFAULT NULL,
            pallets_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000,
            packages_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000,
            pieces_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000,
            pallet_type VARCHAR(40) DEFAULT NULL,
            pallet_specification VARCHAR(60) DEFAULT NULL,
            notes VARCHAR(255) DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY(id),
            KEY idx_rtlps_plan(plan_id),
            KEY idx_rtlps_product(product_id),
            KEY idx_rtlps_order_item(order_item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    } else {
        cr_add_col($pdo,'route_truck_load_plan_slots','order_item_id',"order_item_id BIGINT UNSIGNED DEFAULT NULL AFTER slot_no");
        cr_add_col($pdo,'route_truck_load_plan_slots','packaging_option_id',"packaging_option_id BIGINT UNSIGNED DEFAULT NULL AFTER product_id");
        cr_add_col($pdo,'route_truck_load_plan_slots','product_label',"product_label VARCHAR(180) DEFAULT NULL AFTER packaging_option_id");
        cr_add_col($pdo,'route_truck_load_plan_slots','pallets_qty',"pallets_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000 AFTER product_label");
        cr_add_col($pdo,'route_truck_load_plan_slots','packages_qty',"packages_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000 AFTER pallets_qty");
        cr_add_col($pdo,'route_truck_load_plan_slots','pieces_qty',"pieces_qty DECIMAL(14,3) NOT NULL DEFAULT 0.000 AFTER packages_qty");
        cr_add_col($pdo,'route_truck_load_plan_slots','pallet_type',"pallet_type VARCHAR(40) DEFAULT NULL AFTER pieces_qty");
        cr_add_col($pdo,'route_truck_load_plan_slots','pallet_specification',"pallet_specification VARCHAR(60) DEFAULT NULL AFTER pallet_type");
        cr_add_index($pdo,'route_truck_load_plan_slots','idx_rtlps_plan',"INDEX idx_rtlps_plan(plan_id)");
        cr_add_index($pdo,'route_truck_load_plan_slots','idx_rtlps_product',"INDEX idx_rtlps_product(product_id)");
        cr_add_index($pdo,'route_truck_load_plan_slots','idx_rtlps_order_item',"INDEX idx_rtlps_order_item(order_item_id)");
    }
}

function cr_routes(PDO $pdo, int $companyId, int $locationId): array {
    if(!cr_table_exists($pdo,'route_access_codes')) return [];
    $st=$pdo->prepare("SELECT id, route_no, route_code, route_name, driver_name, vehicle_unit, description FROM route_access_codes WHERE company_id=? AND location_id=? AND active=1 ORDER BY sort_order, route_no, route_code");
    $st->execute([$companyId,$locationId]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}
function cr_products(PDO $pdo, int $companyId): array { $st=$pdo->prepare("SELECT id, sku, name, uom, pieces_per_package, packages_per_pallet FROM products WHERE company_id=? AND active=1 ORDER BY sku, name"); $st->execute([$companyId]); return $st->fetchAll(PDO::FETCH_ASSOC); }
function cr_packaging(PDO $pdo, int $companyId, int $productId, int $packagingId): array {
    if($packagingId>0 && cr_table_exists($pdo,'product_packaging_options')) {
        $st=$pdo->prepare("SELECT id, product_id, packaging_type, packaging_name, pieces_per_package, packages_per_pallet, active, 0 AS legacy_fallback FROM product_packaging_options WHERE id=? AND company_id=? AND product_id=? AND active=1 LIMIT 1");
        $st->execute([$packagingId,$companyId,$productId]); $r=$st->fetch(PDO::FETCH_ASSOC); if($r) return $r;
    }
    $st=$pdo->prepare("SELECT 0 AS id, id AS product_id, 'TARIMA' AS packaging_type, CONCAT('Embalaje principal · ', packages_per_pallet, ' paq/tar') AS packaging_name, pieces_per_package, packages_per_pallet, active, 1 AS legacy_fallback FROM products WHERE id=? AND company_id=? AND active=1 AND COALESCE(pieces_per_package,0)>0 AND COALESCE(packages_per_pallet,0)>0 LIMIT 1");
    $st->execute([$productId,$companyId]); $r=$st->fetch(PDO::FETCH_ASSOC); if(!$r) throw new RuntimeException('El producto no tiene embalaje configurado.'); return $r;
}
function cr_packaging_options(PDO $pdo, int $companyId, int $productId): array {
    $rows=[];
    if(cr_table_exists($pdo,'product_packaging_options')) {
        $st=$pdo->prepare("SELECT id, product_id, packaging_type, packaging_name, pieces_per_package, packages_per_pallet, is_default, 0 AS legacy_fallback FROM product_packaging_options WHERE company_id=? AND product_id=? AND active=1 ORDER BY is_default DESC, packages_per_pallet DESC, id");
        $st->execute([$companyId,$productId]); $rows=$st->fetchAll(PDO::FETCH_ASSOC);
    }
    if(!$rows) { $r=cr_packaging($pdo,$companyId,$productId,0); $r['is_default']=1; $rows[]=$r; }
    foreach($rows as &$r) $r['pieces_per_pallet']=(int)$r['pieces_per_package']*(int)$r['packages_per_pallet']; unset($r);
    return $rows;
}
function cr_route_stock(PDO $pdo, int $companyId, int $locationId, int $routeNo): array {
    $code=cr_route_warehouse_code($routeNo);
    $st=$pdo->prepare("SELECT id FROM warehouses WHERE company_id=? AND location_id=? AND UPPER(code)=UPPER(?) AND active=1 LIMIT 1");
    $st->execute([$companyId,$locationId,$code]);
    $wid=(int)($st->fetchColumn()?:0);
    if($wid<=0) return [];
    $st=$pdo->prepare("SELECT p.id AS product_id, p.sku, p.name, p.uom, COALESCE(SUM(CASE WHEN im.move_type IN ('IN','ADJ') THEN im.qty WHEN im.move_type='OUT' THEN -im.qty ELSE 0 END),0) AS qty FROM products p LEFT JOIN inventory_moves im ON im.product_id=p.id AND im.company_id=p.company_id AND im.location_id=? AND im.warehouse_id=? WHERE p.company_id=? AND p.active=1 GROUP BY p.id,p.sku,p.name,p.uom HAVING ABS(qty)>0.0001 ORDER BY p.sku,p.name");
    $st->execute([$locationId,$wid,$companyId]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}
function cr_route_by_no(PDO $pdo, int $companyId, int $locationId, int $routeNo): array {
    $st=$pdo->prepare("SELECT id, route_no, route_code, route_name, driver_name, vehicle_unit FROM route_access_codes WHERE company_id=? AND location_id=? AND route_no=? AND active=1 LIMIT 1");
    $st->execute([$companyId,$locationId,$routeNo]); $r=$st->fetch(PDO::FETCH_ASSOC); if(!$r) throw new RuntimeException('Ruta no encontrada o inactiva para esta planta.'); return $r;
}
function cr_find_order_id(PDO $pdo, int $companyId, int $locationId): int {
    $id=(int)($_GET['order_id'] ?? $_GET['id'] ?? 0);
    if($id>0) return $id;
    $routeNo=(int)($_GET['route_no'] ?? 0);
    $workDate=(string)($_GET['work_date'] ?? cr_work_date());
    if($routeNo<=0) return 0;
    $st=$pdo->prepare("SELECT id FROM sales_orders WHERE company_id=? AND location_id=? AND route_no=? AND requested_delivery_date=? AND status IN ('AUTORIZADA','SURTIENDO','POR_SURTIR','PENDIENTE') ORDER BY id DESC LIMIT 1");
    $st->execute([$companyId,$locationId,$routeNo,$workDate]);
    return (int)($st->fetchColumn() ?: 0);
}
function cr_order(PDO $pdo, int $companyId, int $locationId, int $orderId): ?array {
    if($orderId<=0 || !cr_table_exists($pdo,'sales_orders')) return null;
    $st=$pdo->prepare("SELECT so.*, c.business_name, c.contact_name, c.customer_code FROM sales_orders so LEFT JOIN sales_customers c ON c.id=so.customer_id WHERE so.id=? AND so.company_id=? AND so.location_id=? LIMIT 1");
    $st->execute([$orderId,$companyId,$locationId]);
    $r=$st->fetch(PDO::FETCH_ASSOC);
    return $r ?: null;
}
function cr_order_items(PDO $pdo, int $companyId, int $orderId): array {
    if($orderId<=0 || !cr_table_exists($pdo,'sales_order_items')) return [];
    $hasPackaging=cr_col_exists($pdo,'sales_order_items','packaging_option_id');
    $hasPackages=cr_col_exists($pdo,'sales_order_items','packages_qty');
    $hasPallets=cr_col_exists($pdo,'sales_order_items','pallets_qty');
    $hasPalletType=cr_col_exists($pdo,'sales_order_items','pallet_type');
    $hasPalletSpec=cr_col_exists($pdo,'sales_order_items','pallet_specification');
    $selectPackaging=$hasPackaging ? 'soi.packaging_option_id' : 'NULL AS packaging_option_id';
    $selectPackages=$hasPackages ? 'soi.packages_qty' : '0 AS packages_qty';
    $selectPallets=$hasPallets ? 'soi.pallets_qty' : '0 AS pallets_qty';
    $selectPalletType=$hasPalletType ? 'soi.pallet_type' : 'NULL AS pallet_type';
    $selectPalletSpec=$hasPalletSpec ? 'soi.pallet_specification' : 'NULL AS pallet_specification';
    $joinPackaging = ($hasPackaging && cr_table_exists($pdo,'product_packaging_options')) ? "LEFT JOIN product_packaging_options po ON po.id=soi.packaging_option_id AND po.company_id=p.company_id" : "";
    $packLabel = ($hasPackaging && cr_table_exists($pdo,'product_packaging_options')) ? "COALESCE(po.packaging_name, po.packaging_type, CONCAT('Tarima ', p.packages_per_pallet, ' paquetes'))" : "CONCAT('Tarima ', p.packages_per_pallet, ' paquetes')";
    $st=$pdo->prepare("SELECT soi.id AS order_item_id, soi.product_id, p.sku, p.name, p.uom, soi.qty AS pieces_qty_order, $selectPackaging, $selectPackages, $selectPallets, $selectPalletType, $selectPalletSpec, $packLabel AS packaging_label, p.pieces_per_package, p.packages_per_pallet FROM sales_order_items soi JOIN products p ON p.id=soi.product_id $joinPackaging WHERE soi.order_id=? AND p.company_id=? ORDER BY soi.id");
    $st->execute([$orderId,$companyId]);
    $rows=$st->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as &$r){
        $pallets=(float)($r['pallets_qty'] ?? 0);
        $packages=(float)($r['packages_qty'] ?? 0);
        $pieces=(float)($r['pieces_qty_order'] ?? 0);
        $ppp=(float)($r['packages_per_pallet'] ?? 0);
        $piecePack=(float)($r['pieces_per_package'] ?? 0);
        if($packages <= 0 && $pallets > 0 && $ppp > 0) $packages = $pallets * $ppp;
        if($pieces <= 0 && $packages > 0 && $piecePack > 0) $pieces = $packages * $piecePack;
        if($pallets <= 0 && $packages > 0 && $ppp > 0) $pallets = $packages / $ppp;
        $r['pallets_qty'] = round($pallets,3);
        $r['packages_qty'] = round($packages,3);
        $r['pieces_qty'] = round($pieces,3);
        $r['product_label'] = trim((string)$r['sku'].' · '.(string)$r['name']);
    }
    unset($r);
    return $rows;
}
function cr_open_plans(PDO $pdo, int $companyId, int $locationId): array {
    $st=$pdo->prepare("SELECT p.*, COUNT(s.id) AS rows_count, COALESCE(SUM(s.pallets_qty),0) AS pallets_qty, COALESCE(SUM(s.pieces_qty),0) AS pieces_qty FROM route_truck_load_plans p LEFT JOIN route_truck_load_plan_slots s ON s.plan_id=p.id WHERE p.company_id=? AND p.location_id=? AND p.status NOT IN ('CANCELADO','DESPACHADO','CERRADO') GROUP BY p.id ORDER BY p.updated_at DESC, p.created_at DESC LIMIT 100");
    $st->execute([$companyId,$locationId]); return $st->fetchAll(PDO::FETCH_ASSOC);
}
function cr_plan(PDO $pdo, int $companyId, int $locationId, int $planId): ?array { $st=$pdo->prepare("SELECT * FROM route_truck_load_plans WHERE id=? AND company_id=? AND location_id=? LIMIT 1"); $st->execute([$planId,$companyId,$locationId]); $r=$st->fetch(PDO::FETCH_ASSOC); return $r?:null; }
function cr_plan_for_order(PDO $pdo, int $companyId, int $locationId, int $orderId): ?array { if($orderId<=0) return null; $st=$pdo->prepare("SELECT * FROM route_truck_load_plans WHERE company_id=? AND location_id=? AND order_id=? AND status NOT IN ('CANCELADO','DESPACHADO','CERRADO') ORDER BY id DESC LIMIT 1"); $st->execute([$companyId,$locationId,$orderId]); $r=$st->fetch(PDO::FETCH_ASSOC); return $r?:null; }
function cr_slots(PDO $pdo, int $planId): array { $st=$pdo->prepare("SELECT * FROM route_truck_load_plan_slots WHERE plan_id=? ORDER BY slot_no ASC, id ASC"); $st->execute([$planId]); return $st->fetchAll(PDO::FETCH_ASSOC); }

function cr_is_route_warehouse_code(string $code): bool {
    return (bool)preg_match('/^RUTA[-_ ]?\d+$/i', trim($code));
}

function cr_is_disposal_warehouse_code(string $code): bool {
    return in_array(strtoupper(trim($code)), ['MERMA','APROV-MERMA','SIN-WH'], true);
}

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

function cr_ensure_route_warehouse(PDO $pdo, int $companyId, int $locationId, int $routeNo): int {
    $route = cr_route_by_no($pdo, $companyId, $locationId, $routeNo);
    $code = cr_route_warehouse_code($routeNo);
    $name = trim('Ruta ' . $routeNo . ' · ' . (string)($route['route_code'] ?? '') . ' ' . (string)($route['route_name'] ?? ''));
    $name = mb_substr($name !== '' ? $name : $code, 0, 120, 'UTF-8');

    $id = cr_warehouse_id_by_code($pdo, $companyId, $locationId, $code);
    if ($id > 0) {
        $st = $pdo->prepare("UPDATE warehouses SET name=?, active=1 WHERE id=? AND company_id=? AND location_id=?");
        $st->execute([$name, $id, $companyId, $locationId]);
        return $id;
    }

    $st = $pdo->prepare("INSERT INTO warehouses(company_id, location_id, code, name, active, created_at) VALUES(?,?,?,?,1,NOW())");
    $st->execute([$companyId, $locationId, $code, $name]);
    return (int)$pdo->lastInsertId();
}

function cr_product_row(PDO $pdo, int $companyId, int $productId): array {
    $st = $pdo->prepare("SELECT id, sku, name, uom FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$productId, $companyId]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if (!$row) throw new RuntimeException('Producto inválido o inactivo.');
    return $row;
}

function cr_product_stock_by_warehouse(PDO $pdo, int $companyId, int $locationId, int $productId): array {
    $st = $pdo->prepare("SELECT warehouse_id, COALESCE(SUM(CASE WHEN move_type IN ('IN','ADJ') THEN qty WHEN move_type='OUT' THEN -qty ELSE 0 END),0) AS qty FROM inventory_moves WHERE company_id=? AND location_id=? AND product_id=? AND warehouse_id IS NOT NULL GROUP BY warehouse_id");
    $st->execute([$companyId, $locationId, $productId]);
    $out = [];
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $out[(int)$r['warehouse_id']] = (float)$r['qty'];
    }
    return $out;
}

function cr_source_warehouses_for_product(PDO $pdo, int $companyId, int $locationId, int $productId): array {
    $ids = [];
    if (cr_table_exists($pdo, 'product_warehouse_rules')) {
        $cols = [];
        foreach (['default_sales_warehouse_id','default_finished_warehouse_id','cubicado_warehouse_id'] as $c) {
            if (cr_col_exists($pdo, 'product_warehouse_rules', $c)) $cols[] = $c;
        }
        if ($cols) {
            $st = $pdo->prepare("SELECT " . implode(',', $cols) . " FROM product_warehouse_rules WHERE company_id=? AND location_id=? AND product_id=? AND active=1 LIMIT 1");
            $st->execute([$companyId, $locationId, $productId]);
            $rule = $st->fetch(PDO::FETCH_ASSOC);
            if ($rule) {
                foreach ($cols as $c) {
                    $v = (int)($rule[$c] ?? 0);
                    if ($v > 0) $ids[$v] = true;
                }
            }
        }
    }

    if ($ids) {
        $ph = implode(',', array_fill(0, count($ids), '?'));
        $params = array_merge([$companyId, $locationId], array_keys($ids));
        $st = $pdo->prepare("SELECT id, code, name FROM warehouses WHERE company_id=? AND location_id=? AND active=1 AND id IN ($ph) ORDER BY code, name");
        $st->execute($params);
    } else {
        $st = $pdo->prepare("SELECT id, code, name FROM warehouses WHERE company_id=? AND location_id=? AND active=1 ORDER BY code, name");
        $st->execute([$companyId, $locationId]);
    }

    $rows = [];
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $w) {
        $code = strtoupper(trim((string)$w['code']));
        if ($code === '' || cr_is_route_warehouse_code($code) || cr_is_disposal_warehouse_code($code)) continue;
        $rows[] = [
            'warehouse_id' => (int)$w['id'],
            'code' => (string)$w['code'],
            'name' => (string)$w['name'],
        ];
    }
    return $rows;
}

function cr_allocate_source_inventory(PDO $pdo, int $companyId, int $locationId, int $productId, float $qty): array {
    $qty = round($qty, 3);
    if ($qty <= 0) return [];

    $product = cr_product_row($pdo, $companyId, $productId);
    $sku = (string)($product['sku'] ?? ('#' . $productId));
    $stock = cr_product_stock_by_warehouse($pdo, $companyId, $locationId, $productId);
    $sources = cr_source_warehouses_for_product($pdo, $companyId, $locationId, $productId);
    if (!$sources) {
        throw new RuntimeException('No existe almacén físico de salida configurado para ' . $sku . '. Revisa Admin > Productos/Almacenes.');
    }

    $remaining = $qty;
    $alloc = [];
    foreach ($sources as $w) {
        $wid = (int)$w['warehouse_id'];
        $available = (float)($stock[$wid] ?? 0.0);
        if ($available <= 0.0001) continue;
        $take = min($remaining, $available);
        if ($take > 0.0001) {
            $alloc[] = ['warehouse_id'=>$wid, 'code'=>(string)$w['code'], 'qty'=>round($take, 3)];
            $remaining = round($remaining - $take, 3);
        }
        if ($remaining <= 0.0001) break;
    }

    if ($remaining > 0.0001) {
        $labels = implode('/', array_map(fn($w) => (string)$w['code'], $sources));
        throw new RuntimeException('Inventario insuficiente para ' . $sku . ' en almacenes físicos (' . $labels . '). Faltante: ' . number_format($remaining, 0, '.', ',') . ' pz.');
    }
    return $alloc;
}


function cr_source_inventory_available(PDO $pdo, int $companyId, int $locationId, int $productId): array {
    $stock = cr_product_stock_by_warehouse($pdo, $companyId, $locationId, $productId);
    $sources = cr_source_warehouses_for_product($pdo, $companyId, $locationId, $productId);
    $total = 0.0;
    $labels = [];
    foreach ($sources as $w) {
        $wid = (int)$w['warehouse_id'];
        $qty = (float)($stock[$wid] ?? 0.0);
        $total += $qty;
        $labels[] = [
            'warehouse_id' => $wid,
            'code' => (string)($w['code'] ?? ''),
            'name' => (string)($w['name'] ?? ''),
            'qty' => round($qty, 3),
        ];
    }
    return ['available' => round($total, 3), 'warehouses' => $labels];
}

function cr_inventory_prediction(PDO $pdo, int $companyId, int $locationId, array $items): array {
    $running = [];
    $cache = [];
    $rows = [];
    $hasRed = false;
    $hasYellow = false;
    $totalPieces = 0.0;

    foreach ($items as $idx => $row) {
        if (!is_array($row)) continue;
        $productId = (int)($row['product_id'] ?? 0);
        $pieces = round((float)($row['pieces_qty'] ?? 0), 3);
        if ($productId <= 0 || $pieces <= 0) continue;
        if (!isset($cache[$productId])) {
            $product = cr_product_row($pdo, $companyId, $productId);
            $inv = cr_source_inventory_available($pdo, $companyId, $locationId, $productId);
            $cache[$productId] = [
                'product_id' => $productId,
                'sku' => (string)($product['sku'] ?? ''),
                'name' => (string)($product['name'] ?? ''),
                'available' => (float)$inv['available'],
                'warehouses' => $inv['warehouses'],
            ];
            $running[$productId] = 0.0;
        }
        $before = round($cache[$productId]['available'] - $running[$productId], 3);
        $after = round($before - $pieces, 3);
        $running[$productId] = round($running[$productId] + $pieces, 3);
        $status = 'green';
        $title = 'Inventario estable';
        $message = 'Se puede surtir con el saldo actual.';
        if ($after < -0.0001) {
            $status = 'red';
            $title = 'Requiere revisión de existencias';
            $message = 'No se alcanza a surtir considerando esta carga.';
            $hasRed = true;
        } elseif ($before > 0 && ($after <= max($pieces * 0.25, $before * 0.15))) {
            $status = 'yellow';
            $title = 'Inventario justo';
            $message = 'Sí se alcanza, pero deja saldo bajo en almacén.';
            $hasYellow = true;
        }
        $rows[] = [
            'row_index' => (int)$idx,
            'product_id' => $productId,
            'sku' => $cache[$productId]['sku'],
            'name' => $cache[$productId]['name'],
            'requested' => $pieces,
            'available_before' => $before,
            'available_after' => $after,
            'status' => $status,
            'title' => $title,
            'message' => $message,
            'warehouses' => $cache[$productId]['warehouses'],
        ];
        $totalPieces += $pieces;
    }

    $summaryStatus = $hasRed ? 'red' : ($hasYellow ? 'yellow' : 'green');
    $summaryTitle = $summaryStatus === 'red' ? 'Hay artículos que no se alcanzan a surtir' : ($summaryStatus === 'yellow' ? 'Inventario justo en uno o más artículos' : 'Inventario estable para esta carga');
    return [
        'status' => $summaryStatus,
        'title' => $summaryTitle,
        'rows' => $rows,
        'total_pieces' => round($totalPieces, 3),
    ];
}

function cr_route_load_already_posted(PDO $pdo, int $planId): bool {
    $st = $pdo->prepare("SELECT COUNT(*) FROM inventory_moves WHERE ref_type='ROUTE_LOAD' AND ref_id=?");
    $st->execute([$planId]);
    return (int)$st->fetchColumn() > 0;
}

function cr_post_route_load_inventory(PDO $pdo, int $companyId, int $locationId, int $userId, int $planId, int $routeNo, string $workDate, array $rows, string $vehicleUnit, string $driverName): void {
    if (cr_route_load_already_posted($pdo, $planId)) {
        throw new RuntimeException('Esta carga de ruta ya tiene movimientos de inventario registrados. No se puede duplicar.');
    }

    $routeWarehouseId = cr_ensure_route_warehouse($pdo, $companyId, $locationId, $routeNo);
    $routeCode = cr_route_warehouse_code($routeNo);
    $noteBase = 'Carga a ruta ' . $routeCode . ' | Plan #' . $planId;
    if ($vehicleUnit !== '') $noteBase .= ' | Unidad: ' . $vehicleUnit;
    if ($driverName !== '') $noteBase .= ' | Chofer: ' . $driverName;

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

    foreach ($rows as $row) {
        $productId = (int)$row['product_id'];
        $qty = round((float)$row['pieces_qty'], 3);
        if ($productId <= 0 || $qty <= 0) continue;

        $alloc = cr_allocate_source_inventory($pdo, $companyId, $locationId, $productId, $qty);
        foreach ($alloc as $a) {
            $notes = $noteBase . ' | OUT almacén físico ' . (string)$a['code'];
            $ins->execute([$companyId, $locationId, (int)$a['warehouse_id'], $workDate, $productId, (float)$a['qty'], 'OUT', null, 'ROUTE_LOAD', $planId, $notes, $userId]);
        }

        $notesIn = $noteBase . ' | IN almacén móvil ' . $routeCode;
        $ins->execute([$companyId, $locationId, $routeWarehouseId, $workDate, $productId, $qty, 'IN', null, 'ROUTE_LOAD', $planId, $notesIn, $userId]);
    }
}


function cr_save_plan(PDO $pdo, int $companyId, int $locationId, int $userId, array $payload): array {
    $planId=(int)($payload['plan_id']??0);
    $orderId=(int)($payload['order_id']??0);
    $routeNo=(int)($payload['route_no']??0); if($routeNo<=0) throw new RuntimeException('Selecciona la ruta a cargar.');
    $route=cr_route_by_no($pdo,$companyId,$locationId,$routeNo);
    $workDate=(string)($payload['work_date']??cr_work_date()); if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$workDate)) $workDate=cr_work_date();
    $items=$payload['items']??[]; if(!is_array($items)||!$items) throw new RuntimeException('Agrega al menos un artículo a la carga de ruta.');
    $rows=[]; $slotNo=1; $totalPallets=0.0; $totalPackages=0.0; $totalPieces=0.0;
    foreach($items as $row){
        if(!is_array($row)) continue;
        $productId=(int)($row['product_id']??0); $packagingId=(int)($row['packaging_option_id']??0);
        $pallets=(float)($row['pallets_qty']??$row['pallets_to_load']??0);
        $packages=(float)($row['packages_qty']??0);
        $pieces=(float)($row['pieces_qty']??0);
        if($productId<=0) continue;
        if($pallets < 0 || $packages < 0 || $pieces < 0) throw new RuntimeException('No se permiten cantidades negativas.');
        $p=cr_product_row($pdo,$companyId,$productId);
        $pkg=null;
        try { $pkg=cr_packaging($pdo,$companyId,$productId,$packagingId); } catch(Throwable $e) { $pkg=null; }
        if($pkg){
            $ppp=(float)($pkg['packages_per_pallet'] ?? 0); $piecesPack=(float)($pkg['pieces_per_package'] ?? 0);
            if($packages<=0 && $pallets>0 && $ppp>0) $packages=$pallets*$ppp;
            if($pieces<=0 && $packages>0 && $piecesPack>0) $pieces=$packages*$piecesPack;
            if($pallets<=0 && $packages>0 && $ppp>0) $pallets=$packages/$ppp;
        }
        if($pallets<=0 && $packages<=0 && $pieces<=0) continue;
        if(abs($pieces-round($pieces))>0.000001) throw new RuntimeException('Las piezas/botellas deben ser enteras. Revisa cantidades.');
        $label=trim((string)$p['sku'].' · '.(string)$p['name']);
        $rows[]=[
            'slot_no'=>$slotNo++,
            'order_item_id'=>(int)($row['order_item_id']??0) ?: null,
            'product_id'=>$productId,
            'packaging_option_id'=>$packagingId>0?$packagingId:null,
            'product_label'=>$label,
            'pallets_qty'=>round($pallets,3),
            'packages_qty'=>round($packages,3),
            'pieces_qty'=>round($pieces,3),
            'pallet_type'=>trim((string)($row['pallet_type']??'')) ?: null,
            'pallet_specification'=>trim((string)($row['pallet_specification']??'')) ?: null,
            'notes'=>trim((string)($row['notes']??'')) ?: null,
        ];
        $totalPallets += $pallets; $totalPackages += $packages; $totalPieces += $pieces;
    }
    if(!$rows) throw new RuntimeException('Captura al menos un artículo válido para cargar la ruta.');

    $vehicleUnit = trim((string)($payload['vehicle_unit']??''));
    $driverName = trim((string)($payload['driver_name']??''));
    $notesPlan = trim((string)($payload['notes']??''));

    $pdo->beginTransaction();
    try{
        if($planId>0){
            $existing=cr_plan($pdo,$companyId,$locationId,$planId); if(!$existing) throw new RuntimeException('Plan no encontrado.');
            if(in_array(strtoupper((string)$existing['status']),['DESPACHADO','CERRADO','CANCELADO'],true)) throw new RuntimeException('No se puede editar una carga ya despachada/cerrada. Genera una nueva carga si la ruta vuelve a surtirse.');
            if(cr_route_load_already_posted($pdo,$planId)) throw new RuntimeException('Esta carga ya afectó inventario. No se puede modificar para evitar duplicidad.');
            $pdo->prepare("UPDATE route_truck_load_plans SET order_id=?, route_no=?, route_code=?, route_name=?, work_date=?, vehicle_type='REPARTO', vehicle_label='Unidad reparto', capacity_pallets=0, vehicle_unit=?, driver_name=?, notes=?, updated_by=?, updated_at=NOW(), status='BORRADOR' WHERE id=? AND company_id=? AND location_id=?")->execute([$orderId>0?$orderId:null,$routeNo,(string)($route['route_code']??''),(string)($route['route_name']??''),$workDate,$vehicleUnit!==''?$vehicleUnit:null,$driverName!==''?$driverName:null,$notesPlan!==''?$notesPlan:null,$userId,$planId,$companyId,$locationId]);
            $pdo->prepare("DELETE FROM route_truck_load_plan_slots WHERE plan_id=?")->execute([$planId]);
        } else {
            $pdo->prepare("INSERT INTO route_truck_load_plans(company_id,location_id,order_id,route_no,route_code,route_name,work_date,vehicle_type,vehicle_label,capacity_pallets,vehicle_unit,driver_name,status,notes,created_by,created_at,updated_by,updated_at) VALUES(?,?,?,?,?,?,?,'REPARTO','Unidad reparto',0,?,?,'BORRADOR',?,?,NOW(),?,NOW())")->execute([$companyId,$locationId,$orderId>0?$orderId:null,$routeNo,(string)($route['route_code']??''),(string)($route['route_name']??''),$workDate,$vehicleUnit!==''?$vehicleUnit:null,$driverName!==''?$driverName:null,$notesPlan!==''?$notesPlan:null,$userId,$userId]);
            $planId=(int)$pdo->lastInsertId();
        }
        $st=$pdo->prepare("INSERT INTO route_truck_load_plan_slots(plan_id,slot_no,order_item_id,product_id,packaging_option_id,product_label,pallets_qty,packages_qty,pieces_qty,pallet_type,pallet_specification,notes,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,NOW())");
        foreach($rows as $s){ $st->execute([$planId,$s['slot_no'],$s['order_item_id'],$s['product_id'],$s['packaging_option_id'],$s['product_label'],$s['pallets_qty'],$s['packages_qty'],$s['pieces_qty'],$s['pallet_type'],$s['pallet_specification'],$s['notes']]); }

        cr_post_route_load_inventory($pdo, $companyId, $locationId, $userId, $planId, $routeNo, $workDate, $rows, $vehicleUnit, $driverName);

        $pdo->prepare("UPDATE route_truck_load_plans SET status='DESPACHADO', updated_by=?, updated_at=NOW() WHERE id=? AND company_id=? AND location_id=?")->execute([$userId,$planId,$companyId,$locationId]);
        if($orderId>0 && cr_table_exists($pdo,'sales_orders')){
            $pdo->prepare("UPDATE sales_orders SET status='EN_RUTA', updated_at=NOW() WHERE id=? AND company_id=? AND location_id=?")->execute([$orderId,$companyId,$locationId]);
        }

        $pdo->commit();
        return ['plan_id'=>$planId,'rows_count'=>count($rows),'pallets_qty'=>round($totalPallets,3),'packages_qty'=>round($totalPackages,3),'pieces_qty'=>round($totalPieces,3),'vehicle_label'=>'Unidad reparto','posted_inventory'=>true,'route_warehouse'=>cr_route_warehouse_code($routeNo)];
    }catch(Throwable $e){ if($pdo->inTransaction()) $pdo->rollBack(); throw $e; }
}

cr_ensure_schema($pdo);
if(!cr_scope_is_botella($pdo,$companyId,$locationId)){ http_response_code(403); exit('Esta sección corresponde únicamente a Botella.'); }
$canManage = has_role('admin') || has_role('warehouse') || has_role('almacen') || has_role('supervisor') || has_role('plant_admin');
if(!$canManage){ http_response_code(403); exit('Acceso denegado.'); }

if($action==='print'){
    $planId=(int)($_GET['plan_id']??0);
    $plan=cr_plan($pdo,$companyId,$locationId,$planId); if(!$plan) exit('Plan no encontrado.');
    $slots=cr_slots($pdo,$planId);
    ?><!doctype html><html lang="es"><head><meta charset="utf-8"><title>Carga ruta <?=cr_h((string)$plan['route_no'])?></title><style>body{font-family:Arial,sans-serif;margin:14px;color:#111;background:#fff}.logo{text-align:center;font-size:34px;color:#0d4e9b;font-weight:800;font-style:italic;line-height:1}.sub{text-align:center;letter-spacing:8px;color:#0d4e9b;font-weight:700;margin-bottom:14px}.tbl{width:100%;border-collapse:collapse;margin:8px 0 16px}.tbl th{background:#0877bd;color:white}.tbl th,.tbl td{border:1px solid #222;padding:6px;font-size:12px;vertical-align:middle}.num{text-align:right}.printbar{margin-bottom:10px}@media print{.printbar{display:none}body{margin:7mm}}@page{size:landscape;margin:7mm}</style></head><body><div class="printbar"><button onclick="window.print()">Imprimir</button> <button onclick="window.close()">Cerrar</button></div><div class="logo">Blanquita</div><div class="sub">AGUA PURIFICADA</div><table class="tbl"><tbody><tr><th>FECHA</th><td><?=cr_h((string)$plan['work_date'])?></td><th>RUTA</th><td><?=cr_h('Ruta '.$plan['route_no'].' · '.(string)$plan['route_code'].' '.(string)$plan['route_name'])?></td><th>UNIDAD</th><td><?=cr_h((string)$plan['vehicle_unit'])?></td><th>CHOFER</th><td><?=cr_h((string)$plan['driver_name'])?></td></tr><tr><th>NOTAS</th><td colspan="7"><?=nl2br(cr_h((string)$plan['notes']))?></td></tr></tbody></table><table class="tbl"><thead><tr><th>PRODUCTO</th><th>TARIMAS</th><th>PAQUETES</th><th>BOTELLAS</th><th>TIPO</th><th>OBSERVACIÓN</th></tr></thead><tbody><?php foreach($slots as $s): ?><tr><td><?=cr_h((string)$s['product_label'])?></td><td class="num"><?=number_format((float)$s['pallets_qty'],0,'.',',')?></td><td class="num"><?=number_format((float)$s['packages_qty'],0,'.',',')?></td><td class="num"><?=number_format((float)$s['pieces_qty'],0,'.',',')?></td><td><?=cr_h(trim((string)$s['pallet_type'].' '.(string)$s['pallet_specification']))?></td><td><?=cr_h((string)$s['notes'])?></td></tr><?php endforeach; ?></tbody></table></body></html><?php exit;
}

if($action!==''){
    cr_need_csrf();
    try{
        if($action==='bootstrap') {
            $orderId=cr_find_order_id($pdo,$companyId,$locationId);
            $order=$orderId>0?cr_order($pdo,$companyId,$locationId,$orderId):null;
            $orderItems=$orderId>0?cr_order_items($pdo,$companyId,$orderId):[];
            $plan=$orderId>0?cr_plan_for_order($pdo,$companyId,$locationId,$orderId):null;
            $slots=[];
            if($plan) $slots=cr_slots($pdo,(int)$plan['id']);
            cr_jexit(['ok'=>true,'routes'=>cr_routes($pdo,$companyId,$locationId),'products'=>cr_products($pdo,$companyId),'plans'=>cr_open_plans($pdo,$companyId,$locationId),'work_date'=>cr_work_date(),'order'=>$order,'order_items'=>$orderItems,'plan'=>$plan,'slots'=>$slots]);
        }
        if($action==='packaging_options') { $pid=(int)($_GET['product_id']??0); cr_jexit(['ok'=>true,'rows'=>$pid>0?cr_packaging_options($pdo,$companyId,$pid):[]]); }
        if($action==='route_stock') { $routeNo=(int)($_GET['route_no']??0); cr_jexit(['ok'=>true,'rows'=>$routeNo>0?cr_route_stock($pdo,$companyId,$locationId,$routeNo):[]]); }
        if($action==='inventory_prediction') { $payload=cr_read_json(); $items=$payload['items']??[]; cr_jexit(['ok'=>true,'prediction'=>cr_inventory_prediction($pdo,$companyId,$locationId,is_array($items)?$items:[])]); }
        if($action==='plan_detail') { $pid=(int)($_GET['plan_id']??0); $plan=cr_plan($pdo,$companyId,$locationId,$pid); if(!$plan) cr_jexit(['ok'=>false,'error'=>'Plan no encontrado'],404); cr_jexit(['ok'=>true,'plan'=>$plan,'slots'=>cr_slots($pdo,$pid),'order'=>!empty($plan['order_id'])?cr_order($pdo,$companyId,$locationId,(int)$plan['order_id']):null]); }
        if($action==='save_plan') cr_jexit(['ok'=>true]+cr_save_plan($pdo,$companyId,$locationId,(int)($u['id']??0),cr_read_json()));
        cr_jexit(['ok'=>false,'error'=>'Acción no válida'],400);
    }catch(Throwable $e){ cr_jexit(['ok'=>false,'error'=>$e->getMessage()],400); }
}
$csrf=csrf_token();
?><!doctype html><html lang="es"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1"><title>BReport · Carga Ruta Botella</title><link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"><link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css" rel="stylesheet"><style>
body{background:#eef3f8;color:#10243e}.topbar{background:linear-gradient(135deg,#102b63,#1d6ceb);color:white;padding:18px 22px}.shell{max-width:1480px;margin:0 auto;padding:18px}.glass{background:white;border:1px solid #d8e3f0;border-radius:20px;box-shadow:0 14px 34px rgba(34,55,85,.10)}.panel-pad{padding:20px}.btn-primary-soft{background:#eef5ff;color:#0d6efd;border:1px solid #bfd7ff;font-weight:700}.btn-green-soft{background:#13a65a;color:white;border:0;font-weight:800}.muted{color:#64748b}.mono{font-family:ui-monospace,SFMono-Regular,Menlo,Consolas,monospace}.table-wrap{border:1px solid #dbe5f1;border-radius:16px;overflow:hidden}.table thead th{background:#eaf2ff}.plan-pill{cursor:pointer;border:1px solid #dbe5f1;border-radius:14px;padding:10px;background:#f8fafc}.plan-pill:hover{background:#eef5ff}.route-stock{max-height:220px;overflow:auto}.kpi{background:#f8fbff;border:1px solid #dbeafe;border-radius:18px;padding:14px}.item-row{border:1px solid #dbe5f1;border-radius:16px;background:#fff;padding:12px}.app-note{border:1px solid #bae6fd;background:#ecfeff;border-radius:16px;padding:12px;color:#155e75}.inv-advice{border:1px solid #dbeafe;background:#f8fbff;border-radius:16px;padding:12px;margin-bottom:12px}.inv-advice.green{border-color:#86efac;background:#f0fdf4;color:#14532d}.inv-advice.yellow{border-color:#fde68a;background:#fffbeb;color:#92400e}.inv-advice.red{border-color:#fecaca;background:#fef2f2;color:#991b1b}.inv-pill{display:inline-flex;align-items:center;gap:4px;border-radius:999px;padding:4px 9px;font-size:12px;font-weight:800}.inv-pill.green{background:#dcfce7;color:#166534}.inv-pill.yellow{background:#fef3c7;color:#92400e}.inv-pill.red{background:#fee2e2;color:#991b1b}.row-inv-green{background:#fbfffb}.row-inv-yellow{background:#fffaf0}.row-inv-red{background:#fff5f5}.modal-lite{display:none;position:fixed;inset:0;background:rgba(15,23,42,.55);z-index:1090;align-items:center;justify-content:center;padding:18px}.modal-card{max-width:850px;width:100%;background:#fff;border-radius:20px;box-shadow:0 18px 50px rgba(15,23,42,.25);border:1px solid #dbe5f1}@media(max-width:768px){.topbar{padding:14px}.shell{padding:10px}.panel-pad{padding:14px}.table-wrap{overflow-x:auto}.table{min-width:760px}.sticky-actions{position:sticky;bottom:0;background:rgba(255,255,255,.96);border-top:1px solid #dbe5f1;padding:10px;z-index:5}}
</style></head><body><div class="topbar"><div class="d-flex justify-content-between flex-wrap gap-2 align-items-center"><div><div class="small opacity-75">BReport · Almacén Botella</div><h1 class="m-0 fw-bold">Carga de ruta</h1><div class="opacity-75">Carga por presentación para camionetas de reparto. Sin simulador de tarimas ni capacidad fija.</div></div><div class="d-flex gap-2"><a class="btn btn-light" href="almacen_botella.php"><i class="bi bi-arrow-left me-1"></i>Almacén</a><a class="btn btn-light" href="dashboard.php"><i class="bi bi-grid me-1"></i>Dashboard</a></div></div></div><div class="shell"><div class="row g-3"><div class="col-12 col-xl-4"><div class="glass panel-pad mb-3"><div class="fw-bold mb-2"><i class="bi bi-file-earmark-text text-primary"></i> Orden / ruta</div><div id="orderBox" class="app-note mb-3">Cargando información...</div><div class="row g-2"><div class="col-12"><label class="form-label">Ruta</label><select id="routeNo" class="form-select"></select></div><div class="col-6"><label class="form-label">Fecha de carga</label><input id="workDate" type="date" class="form-control"></div><div class="col-6"><label class="form-label">No. unidad</label><input id="vehicleUnit" class="form-control" placeholder="Camioneta / placa"></div><div class="col-12"><label class="form-label">Chofer</label><input id="driverName" class="form-control" placeholder="Chofer"></div><div class="col-12"><label class="form-label">Notas para reparto</label><textarea id="planNotes" rows="3" class="form-control" placeholder="Notas visibles para el reparto"></textarea></div></div></div><div class="glass panel-pad mb-3"><div class="fw-bold mb-2"><i class="bi bi-plus-circle text-primary"></i> Agregar presentación</div><div class="mb-2"><label class="form-label">Artículo</label><select id="productId" class="form-select"></select></div><div class="mb-2"><label class="form-label">Embalaje</label><select id="packagingId" class="form-select"></select></div><div class="row g-2"><div class="col-4"><label class="form-label">Tarimas</label><input id="pallets" type="number" min="0" step="1" class="form-control mono" value="1"></div><div class="col-4"><label class="form-label">Paquetes</label><input id="packages" type="number" min="0" step="1" class="form-control mono" value="0"></div><div class="col-4"><label class="form-label">Botellas</label><input id="pieces" type="number" min="0" step="1" class="form-control mono" value="0"></div><div class="col-6"><label class="form-label">Tipo tarima</label><select id="palletType" class="form-select"><option>Chep</option><option>Gris</option><option>Mixto</option></select></div><div class="col-6"><label class="form-label">Especificación</label><select id="palletSpec" class="form-select"><option>retorno</option><option>surtido</option><option>mixto</option></select></div></div><div id="calcInfo" class="small muted mt-2"></div><button id="btnAdd" class="btn btn-primary-soft w-100 mt-3" type="button"><i class="bi bi-plus-circle me-1"></i>Agregar a ruta</button></div><div class="glass panel-pad"><div class="fw-bold mb-2"><i class="bi bi-clock-history text-primary"></i> Cargas abiertas</div><div id="plansBox" class="d-grid gap-2"></div></div></div><div class="col-12 col-xl-8"><div class="glass panel-pad mb-3"><div class="row g-2"><div class="col-6 col-md-3"><div class="kpi"><div class="small muted">Renglones</div><div class="fw-bold" id="kRows">0</div></div></div><div class="col-6 col-md-3"><div class="kpi"><div class="small muted">Tarimas</div><div class="fw-bold" id="kPallets">0</div></div></div><div class="col-6 col-md-3"><div class="kpi"><div class="small muted">Paquetes</div><div class="fw-bold" id="kPackages">0</div></div></div><div class="col-6 col-md-3"><div class="kpi"><div class="small muted">Botellas</div><div class="fw-bold" id="kPieces">0</div></div></div></div></div><div class="glass panel-pad mb-3"><div class="d-flex justify-content-between align-items-center mb-2 flex-wrap gap-2"><div><div class="fw-bold">Artículos cargados a la ruta</div><div class="small muted">Al guardar se descuenta del almacén físico y se carga al almacén móvil de la ruta.</div></div><button id="btnClear" class="btn btn-outline-secondary btn-sm" type="button"><i class="bi bi-eraser me-1"></i>Limpiar</button></div><div id="resultBox"></div><div id="inventoryAdviceBox" class="inv-advice">Agrega o modifica cantidades para revisar disponibilidad en ALM01.</div><div class="table-wrap"><table class="table table-hover align-middle mb-0"><thead><tr><th>Artículo</th><th>Embalaje</th><th class="text-end">Tarimas</th><th class="text-end">Paquetes</th><th class="text-end">Botellas</th><th>Tipo</th><th>Inventario ALM01</th><th></th></tr></thead><tbody id="loadBody"></tbody></table></div><div class="sticky-actions mt-3 text-end"><button id="btnSave" class="btn btn-green-soft" type="button"><i class="bi bi-save me-1"></i>Guardar carga ruta</button> <button id="btnPrint" class="btn btn-primary-soft d-none" type="button"><i class="bi bi-printer me-1"></i>Imprimir</button></div></div><div class="glass panel-pad"><div class="fw-bold mb-2"><i class="bi bi-truck text-primary"></i> Inventario actual en ruta seleccionada</div><div id="routeStock" class="route-stock muted">Selecciona ruta para consultar saldo en almacén móvil.</div></div></div></div></div><div id="confirmOverlay" class="modal-lite"><div class="modal-card"><div class="p-3 border-bottom d-flex justify-content-between align-items-center"><div><div class="fw-bold fs-5">Confirmar carga de ruta</div><div class="small muted">Se descontará del almacén físico y se cargará al almacén móvil de la ruta.</div></div><button id="confirmClose" class="btn btn-outline-secondary btn-sm"><i class="bi bi-x-lg"></i></button></div><div class="p-3"><div id="confirmInfo"></div><div class="table-wrap"><table class="table table-sm mb-0"><thead><tr><th>Producto</th><th class="text-end">Tarimas</th><th class="text-end">Paquetes</th><th class="text-end">Botellas</th></tr></thead><tbody id="confirmBody"></tbody></table></div></div><div class="p-3 border-top text-end"><button id="confirmCancel" class="btn btn-outline-secondary">Cancelar</button> <button id="confirmOk" class="btn btn-success fw-bold"><i class="bi bi-save me-1"></i>Confirmar y guardar</button></div></div></div><script>
const CSRF=<?=json_encode($csrf)?>; const $=id=>document.getElementById(id); let routes=[],products=[],plans=[],packagingRows=[],loadRows=[],currentPlanId=0,currentOrderId=0,inventoryPrediction={rows:[],status:"green",title:""},predictionTimer=null; const h=s=>String(s??'').replace(/[&<>"]/g,c=>({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;'}[c]||c)); const n=v=>Number(v||0); const i=v=>parseInt(String(v||0),10)||0; const fmt=v=>Number(v||0).toLocaleString('es-MX',{maximumFractionDigits:0}); async function apiGet(a,p={}){const u=new URL(location.pathname,location.origin);u.searchParams.set('action',a);Object.entries(p).forEach(([k,v])=>u.searchParams.set(k,v));const r=await fetch(u,{headers:{'X-CSRF':CSRF}});return r.json();} async function apiPost(a,d={}){const u=new URL(location.pathname,location.origin);u.searchParams.set('action',a);const r=await fetch(u,{method:'POST',headers:{'Content-Type':'application/json','X-CSRF':CSRF},body:JSON.stringify(d)});return r.json();}
async function bootstrap(){const j=await apiGet('bootstrap',Object.fromEntries(new URLSearchParams(location.search))); if(!j.ok){alert(j.error||'Error');return;} routes=j.routes||[];products=j.products||[];plans=j.plans||[];$('workDate').value=j.work_date||'';$('routeNo').innerHTML='<option value="">Selecciona ruta</option>'+routes.map(r=>`<option value="${r.route_no}" data-driver="${h(r.driver_name||'')}" data-unit="${h(r.vehicle_unit||'')}" data-code="${h(r.route_code||'')}" data-name="${h(r.route_name||'')}">Ruta ${h(r.route_no)} · ${h(r.route_code||'')} ${h(r.route_name||'')}</option>`).join('');$('productId').innerHTML='<option value="">Selecciona producto</option>'+products.map(p=>`<option value="${p.id}">${h(p.sku)} · ${h(p.name)}</option>`).join(''); if(j.order){applyOrder(j.order,j.order_items||[]);} if(j.plan){applyPlan(j.plan,j.slots||[]);} renderPlans();await loadPackaging();renderAll();}
function applyOrder(order,items){currentOrderId=i(order.id);$('orderBox').innerHTML=`<div class="fw-bold mono">${h(order.sales_order_ref||('Orden #'+order.id))}</div><div>${h(order.delivery_address||'')}</div><div class="small">Fecha: <b>${h(order.requested_delivery_date||'')}</b> · Ruta: <b>${h(order.route_no||'')}</b></div>`;$('routeNo').value=String(order.route_no||'');$('workDate').value=order.requested_delivery_date||$('workDate').value;$('vehicleUnit').value=order.vehicle_unit||'';$('driverName').value=order.driver_name||'';$('planNotes').value=cleanNotes(order.comments||'');routeChanged(); if(items.length){loadRows=items.map(r=>({order_item_id:i(r.order_item_id),product_id:i(r.product_id),packaging_option_id:i(r.packaging_option_id),sku:r.sku||'',product_name:r.name||'',packaging_label:r.packaging_label||'',pallets_qty:n(r.pallets_qty),packages_qty:n(r.packages_qty),pieces_qty:n(r.pieces_qty),pallet_type:r.pallet_type||'Chep',pallet_specification:r.pallet_specification||'retorno'}));}}
function cleanNotes(txt){txt=String(txt||'');const m=txt.match(/Notas almac[eé]n:\s*([^\n]+)/i);return m?m[1].trim():txt.replace(/Orden de salida.*?\n/gi,'').replace(/Ruta DLX:.*?\n/gi,'').replace(/Unidad a cargar:.*?\n/gi,'').replace(/Chofer:.*?\n/gi,'').replace(/Fecha\/hora salida:.*?\n/gi,'').trim();}
function applyPlan(plan,slots){currentPlanId=i(plan.id);currentOrderId=i(plan.order_id)||currentOrderId;$('routeNo').value=String(plan.route_no||'');$('workDate').value=plan.work_date||$('workDate').value;$('vehicleUnit').value=plan.vehicle_unit||'';$('driverName').value=plan.driver_name||'';$('planNotes').value=plan.notes||'';routeChanged(); if(slots.length){loadRows=slots.map(s=>({order_item_id:i(s.order_item_id),product_id:i(s.product_id),packaging_option_id:i(s.packaging_option_id),sku:'',product_name:s.product_label||'',packaging_label:'',pallets_qty:n(s.pallets_qty),packages_qty:n(s.packages_qty),pieces_qty:n(s.pieces_qty),pallet_type:s.pallet_type||'Chep',pallet_specification:s.pallet_specification||'retorno'}));}$('btnPrint').classList.remove('d-none');}
function renderPlans(){ $('plansBox').innerHTML=plans.length?plans.map(p=>`<div class="plan-pill" onclick="loadPlan(${p.id})"><div class="fw-bold">Ruta ${h(p.route_no)} · ${h(p.route_code||'')}</div><div class="small muted">${h(p.work_date)} · ${fmt(p.pallets_qty)} tarimas · ${h(p.status)}</div></div>`).join(''):'<div class="muted small">Sin cargas abiertas.</div>'; }
async function loadPackaging(){const pid=i($('productId').value); if(!pid){$('packagingId').innerHTML='<option value="">Selecciona artículo</option>';packagingRows=[];return;} const j=await apiGet('packaging_options',{product_id:pid}); packagingRows=j.rows||[]; $('packagingId').innerHTML=packagingRows.map(p=>`<option value="${p.id}">${h(p.packaging_name||p.packaging_type)} · ${i(p.packages_per_pallet)} paq/tar · ${i(p.pieces_per_package)} pz/paq</option>`).join(''); updateCalcInfo();}
function updateCalcInfo(){const pk=packagingRows.find(x=>String(x.id)===$('packagingId').value)||packagingRows[0];const pal=n($('pallets').value)||0;if(!pk){$('calcInfo').textContent='';return;}const packs=pal*i(pk.packages_per_pallet),pieces=packs*i(pk.pieces_per_package);$('packages').value=packs?String(Math.round(packs)):$('packages').value;$('pieces').value=pieces?String(Math.round(pieces)):$('pieces').value;$('calcInfo').textContent=`Cálculo sugerido: ${fmt(packs)} paquetes · ${fmt(pieces)} botellas`;}
async function loadRouteStock(){const routeNo=i($('routeNo').value); if(!routeNo){$('routeStock').innerHTML='Selecciona ruta para consultar saldo en almacén móvil.';return;} const j=await apiGet('route_stock',{route_no:routeNo}); const rows=j.rows||[]; $('routeStock').innerHTML=rows.length?`<table class="table table-sm mb-0"><thead><tr><th>Producto</th><th class="text-end">Saldo ruta</th></tr></thead><tbody>${rows.map(r=>`<tr><td><div class="fw-bold mono">${h(r.sku)}</div><div class="small muted">${h(r.name)}</div></td><td class="text-end mono fw-bold">${fmt(r.qty)}</td></tr>`).join('')}</tbody></table>`:'La ruta no tiene saldo actual registrado.';}
function routeChanged(){const opt=$('routeNo').selectedOptions[0]; if(opt&&opt.value){$('driverName').value=opt.dataset.driver||$('driverName').value;$('vehicleUnit').value=opt.dataset.unit||$('vehicleUnit').value;} loadRouteStock();}
function addItem(){const pid=i($('productId').value);const pk=packagingRows.find(x=>String(x.id)===$('packagingId').value)||packagingRows[0];if(!pid||!pk){$('resultBox').innerHTML='<div class="alert alert-warning">Selecciona artículo y embalaje.</div>';return;}const pal=n($('pallets').value),packs=n($('packages').value),pieces=n($('pieces').value);if(pal<=0&&packs<=0&&pieces<=0){$('resultBox').innerHTML='<div class="alert alert-warning">Captura cantidad a cargar.</div>';return;}const pr=products.find(p=>n(p.id)===pid)||{};loadRows.push({order_item_id:0,product_id:pid,packaging_option_id:i(pk.id),sku:pr.sku||'',product_name:pr.name||'',packaging_label:pk.packaging_name||pk.packaging_type||'',pallets_qty:pal,packages_qty:packs,pieces_qty:pieces,pallet_type:$('palletType').value,pallet_specification:$('palletSpec').value});renderAll();}
function removeRow(idx){loadRows.splice(idx,1);renderAll();} function totals(){return loadRows.reduce((a,r)=>{a.pallets+=n(r.pallets_qty);a.packages+=n(r.packages_qty);a.pieces+=n(r.pieces_qty);return a;},{pallets:0,packages:0,pieces:0});}
function adviceForRow(idx){return (inventoryPrediction.rows||[]).find(x=>i(x.row_index)===idx)||null;}
function renderInventoryAdvice(){const box=$('inventoryAdviceBox'); if(!box)return; if(!loadRows.length){box.className='inv-advice';box.innerHTML='Agrega o modifica cantidades para revisar disponibilidad en ALM01.';return;} const st=inventoryPrediction.status||'green'; box.className='inv-advice '+st; const rows=inventoryPrediction.rows||[]; const bad=rows.filter(r=>r.status==='red').length; const warn=rows.filter(r=>r.status==='yellow').length; const icon=st==='red'?'⚠️':(st==='yellow'?'🟡':'✅'); box.innerHTML=`<div class="fw-bold">${icon} ${h(inventoryPrediction.title||'Revisión de inventario')}</div><div class="small">${bad?bad+' artículo(s) no se alcanzan a surtir. ':''}${warn?warn+' artículo(s) quedan justos. ':''}La validación compara contra inventario disponible en ALM01/almacenes físicos permitidos, sin considerar rutas.</div>`;}
async function refreshPrediction(){if(!loadRows.length){inventoryPrediction={rows:[],status:'green',title:''};renderInventoryAdvice();renderAllNoPrediction();return;} const items=loadRows.map(r=>({product_id:i(r.product_id),pieces_qty:n(r.pieces_qty)})); const j=await apiPost('inventory_prediction',{items}); if(j.ok&&j.prediction){inventoryPrediction=j.prediction;renderInventoryAdvice();renderAllNoPrediction();}}
function schedulePrediction(){clearTimeout(predictionTimer); predictionTimer=setTimeout(refreshPrediction,280);}
function rowAdviceHtml(idx){const a=adviceForRow(idx); if(!a)return '<span class="inv-pill green">Validando...</span>'; const st=a.status||'green'; const after=n(a.available_after); const before=n(a.available_before); const title=a.title||''; return `<div><span class="inv-pill ${st}">${h(title)}</span></div><div class="small muted mt-1">Disp. antes: <b>${fmt(before)}</b> · después: <b>${fmt(after)}</b></div>`;}
function renderAllNoPrediction(){const t=totals();$('kRows').textContent=loadRows.length;$('kPallets').textContent=fmt(t.pallets);$('kPackages').textContent=fmt(t.packages);$('kPieces').textContent=fmt(t.pieces);$('loadBody').innerHTML=loadRows.length?loadRows.map((r,idx)=>{const a=adviceForRow(idx);const cls=a?'row-inv-'+(a.status||'green'):'';return `<tr class="${cls}"><td><div class="fw-bold mono">${h(r.sku)}</div><div class="small muted">${h(r.product_name)}</div></td><td>${h(r.packaging_label)}</td><td class="text-end mono fw-bold"><input class="form-control form-control-sm text-end mono" value="${h(r.pallets_qty)}" onchange="loadRows[${idx}].pallets_qty=n(this.value);renderAll()"></td><td class="text-end mono"><input class="form-control form-control-sm text-end mono" value="${h(r.packages_qty)}" onchange="loadRows[${idx}].packages_qty=n(this.value);renderAll()"></td><td class="text-end mono"><input class="form-control form-control-sm text-end mono" value="${h(r.pieces_qty)}" onchange="loadRows[${idx}].pieces_qty=n(this.value);renderAll()"></td><td>${h([r.pallet_type,r.pallet_specification].filter(Boolean).join(' · '))}</td><td>${rowAdviceHtml(idx)}</td><td class="text-end"><button class="btn btn-outline-danger btn-sm" onclick="removeRow(${idx})"><i class="bi bi-x"></i></button></td></tr>`}).join(''):'<tr><td colspan="8" class="text-center muted py-4">Agrega presentaciones para cargar la ruta.</td></tr>';$('btnSave').disabled=!loadRows.length||!i($('routeNo').value);}
function renderAll(){renderAllNoPrediction();renderInventoryAdvice();schedulePrediction();}
async function loadPlan(id){const j=await apiGet('plan_detail',{plan_id:id}); if(!j.ok){alert(j.error||'No se pudo cargar');return;} if(j.order) applyOrder(j.order,[]); applyPlan(j.plan,j.slots||[]); renderAll();}
function newPlan(){currentPlanId=0;currentOrderId=0;loadRows=[];$('planNotes').value='';$('orderBox').innerHTML='Nueva carga de ruta sin orden vinculada.';$('btnPrint').classList.add('d-none');renderAll();}
function showConfirm(){if(!loadRows.length||!i($('routeNo').value)){renderAll();return;} const t=totals(); $('confirmInfo').innerHTML=`<div class="alert alert-info"><b>Ruta ${h($('routeNo').value)}</b> · ${fmt(t.pallets)} tarimas · ${fmt(t.packages)} paquetes · ${fmt(t.pieces)} botellas</div>`; $('confirmBody').innerHTML=loadRows.map(r=>`<tr><td><div class="fw-bold">${h(r.sku)} ${h(r.product_name)}</div></td><td class="text-end mono fw-bold">${fmt(r.pallets_qty)}</td><td class="text-end mono">${fmt(r.packages_qty)}</td><td class="text-end mono">${fmt(r.pieces_qty)}</td></tr>`).join(''); $('confirmOverlay').style.display='flex';}
function closeConfirm(){ $('confirmOverlay').style.display='none';}
async function savePlan(){closeConfirm();$('btnSave').disabled=true;$('btnSave').innerHTML='<span class="spinner-border spinner-border-sm me-1"></span>Guardando...';const j=await apiPost('save_plan',{plan_id:currentPlanId,order_id:currentOrderId,route_no:i($('routeNo').value),work_date:$('workDate').value,vehicle_unit:$('vehicleUnit').value,driver_name:$('driverName').value,notes:$('planNotes').value,items:loadRows});$('btnSave').disabled=false;$('btnSave').innerHTML='<i class="bi bi-save me-1"></i>Guardar carga ruta';if(!j.ok){$('resultBox').innerHTML=`<div class="alert alert-danger">${h(j.error||'No se pudo guardar')}</div>`;return;}currentPlanId=n(j.plan_id);$('btnPrint').classList.remove('d-none');$('resultBox').innerHTML=`<div class="alert alert-success">Carga de ruta guardada y aplicada a inventario: ${h(j.rows_count)} presentación(es). Almacén físico descontado y ruta cargada.</div>`;const b=await apiGet('bootstrap');plans=b.plans||[];renderPlans();}
function printPlan(){if(currentPlanId>0)window.open(`carga_ruta_botella.php?action=print&plan_id=${encodeURIComponent(currentPlanId)}`,'_blank','noopener');}
$('routeNo').addEventListener('change',routeChanged);$('productId').addEventListener('change',loadPackaging);$('packagingId').addEventListener('change',updateCalcInfo);$('pallets').addEventListener('input',updateCalcInfo);$('btnAdd').addEventListener('click',addItem);$('btnClear').addEventListener('click',()=>{loadRows=[];renderAll();});$('btnSave').addEventListener('click',showConfirm);$('btnPrint').addEventListener('click',printPlan);$('confirmClose').addEventListener('click',closeConfirm);$('confirmCancel').addEventListener('click',closeConfirm);$('confirmOk').addEventListener('click',savePlan);bootstrap();</script></body></html>
