<?php

declare(strict_types=1);

namespace App\Controllers;

use App\Core\Auth;
use App\Core\Controller;
use App\Core\Database;
use App\Core\Permission;
use App\Core\Session;
use App\Services\AuditService;
use App\Services\DocumentNumberService;
use App\Services\SaleDocumentTypeService as SaleType;
use App\Services\StockMovementService;
use App\Services\StockReservationService;
use PDO;
use RuntimeException;
use Throwable;

final class CustomerDeliveryNoteController extends Controller
{
    public function index(): void
    {
        Permission::require('ventas.remitos.ver');
        $companyId = (int)Auth::companyId();
        $q = trim((string)($_GET['q'] ?? ''));
        $status = strtoupper(trim((string)($_GET['status'] ?? '')));
        $customerId = (int)($_GET['customer_id'] ?? 0);

        $sql =
            'SELECT cdn.*, c.business_name AS customer_name, c.cuit AS customer_cuit,
                    b.name AS branch_name, w.name AS warehouse_name, sd.document_number AS sale_document_number,
                    sd.document_type AS sale_document_type,
                    COALESCE((SELECT SUM(cdni.quantity) FROM customer_delivery_note_items cdni WHERE cdni.customer_delivery_note_id = cdn.id), 0) AS total_quantity
             FROM customer_delivery_notes cdn
             INNER JOIN customers c ON c.id = cdn.customer_id
             INNER JOIN branches b ON b.id = cdn.branch_id
             INNER JOIN warehouses w ON w.id = cdn.warehouse_id
             LEFT JOIN sale_documents sd ON sd.id = cdn.sale_document_id
             WHERE cdn.company_id = :company_id';
        $params = ['company_id' => $companyId];

        if ($customerId > 0) {
            $sql .= ' AND cdn.customer_id = :customer_id';
            $params['customer_id'] = $customerId;
        }
        if ($status !== '' && in_array($status, ['DRAFT', 'DELIVERED', 'PARTIAL', 'VOID'], true)) {
            $sql .= ' AND cdn.status = :status';
            $params['status'] = $status;
        }
        if ($q !== '') {
            $sql .= ' AND (cdn.document_number LIKE :q_doc OR c.business_name LIKE :q_customer OR c.cuit LIKE :q_cuit OR sd.document_number LIKE :q_sale)';
            $params['q_doc'] = '%' . $q . '%';
            $params['q_customer'] = '%' . $q . '%';
            $params['q_cuit'] = '%' . $q . '%';
            $params['q_sale'] = '%' . $q . '%';
        }

        $sql .= ' ORDER BY cdn.issue_date DESC, cdn.id DESC LIMIT 300';
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($params);

        $this->view('sales/delivery_notes/index', [
            'title' => 'Remitos / entregas a clientes',
            'notes' => $stmt->fetchAll(PDO::FETCH_ASSOC),
            'q' => $q,
            'status' => $status,
            'customerId' => $customerId,
        ]);
    }

    public function create(): void
    {
        Permission::require('ventas.remitos.crear');
        $companyId = (int)Auth::companyId();
        $branchId = (int)(Auth::branchId() ?? 0);
        $pdo = Database::pdo();

        $selectedSaleDocumentId = (int)($_GET['sale_document_id'] ?? 0);
        $selectedCustomerId = (int)($_GET['customer_id'] ?? 0);

        $this->view('sales/delivery_notes/create', [
            'title' => 'Nuevo remito / entrega a cliente',
            'today' => date('Y-m-d'),
            'customers' => $this->customers($pdo, $companyId),
            'warehouses' => $this->warehouses($pdo, $companyId, $branchId),
            'saleDocuments' => $this->openSaleDocuments($pdo, $companyId, $selectedCustomerId, $selectedSaleDocumentId),
            'saleItems' => $this->openSaleItems($pdo, $companyId, $selectedCustomerId, $selectedSaleDocumentId),
            'selectedCustomerId' => $selectedCustomerId,
            'selectedSaleDocumentId' => $selectedSaleDocumentId,
        ]);
    }

    public function store(): void
    {
        Permission::require('ventas.remitos.crear');
        $this->csrf();

        $companyId = (int)Auth::companyId();
        $branchId = (int)(Auth::branchId() ?? 0);
        $userId = (int)Auth::id();
        $customerId = (int)$this->input('customer_id', 0);
        $warehouseId = (int)$this->input('warehouse_id', 0);
        $saleDocumentId = (int)$this->input('sale_document_id', 0);
        $issueDate = $this->stringInput('issue_date', date('Y-m-d'));
        $notes = $this->stringInput('notes');

        try {
            $noteId = Database::transaction(function (PDO $pdo) use ($companyId, $branchId, $userId, $customerId, $warehouseId, $saleDocumentId, $issueDate, $notes): int {
                if ($branchId <= 0) {
                    throw new RuntimeException('No hay sucursal activa para el usuario.');
                }
                if ($customerId <= 0) {
                    throw new RuntimeException('Seleccioná un cliente.');
                }
                if ($warehouseId <= 0) {
                    throw new RuntimeException('Seleccioná un depósito.');
                }
                if ($saleDocumentId <= 0) {
                    throw new RuntimeException('Seleccioná el documento comercial a entregar.');
                }
                if (!$this->validDate($issueDate)) {
                    throw new RuntimeException('La fecha del remito no es válida.');
                }

                $this->assertCustomer($pdo, $companyId, $customerId);
                $this->assertWarehouse($pdo, $companyId, $branchId, $warehouseId);
                $saleDocument = $this->lockSaleDocument($pdo, $companyId, $customerId, $saleDocumentId);

                if ((int)$saleDocument['warehouse_id'] !== $warehouseId) {
                    throw new RuntimeException('El depósito del remito debe coincidir con el depósito de la venta seleccionada.');
                }

                $lines = $this->parseLines($pdo, $companyId, $saleDocumentId);
                if ($lines === []) {
                    throw new RuntimeException('Indicá al menos una cantidad a entregar.');
                }

                $number = (new DocumentNumberService())->nextUsingPdo($pdo, $companyId, $branchId, 'CUSTOMER_DELIVERY_NOTE', 'REM-');
                $stmt = $pdo->prepare(
                    'INSERT INTO customer_delivery_notes
                     (company_id, branch_id, warehouse_id, customer_id, sale_document_id, document_number, issue_date, status, notes, created_by)
                     VALUES
                     (:company_id, :branch_id, :warehouse_id, :customer_id, :sale_document_id, :document_number, :issue_date, "DRAFT", :notes, :created_by)'
                );
                $stmt->execute([
                    'company_id' => $companyId,
                    'branch_id' => $branchId,
                    'warehouse_id' => $warehouseId,
                    'customer_id' => $customerId,
                    'sale_document_id' => $saleDocumentId,
                    'document_number' => $number,
                    'issue_date' => $issueDate,
                    'notes' => $this->nullable($notes),
                    'created_by' => $userId,
                ]);
                $noteId = (int)$pdo->lastInsertId();

                $insertItem = $pdo->prepare(
                    'INSERT INTO customer_delivery_note_items
                     (customer_delivery_note_id, sale_document_item_id, product_id, description, quantity)
                     VALUES
                     (:customer_delivery_note_id, :sale_document_item_id, :product_id, :description, :quantity)'
                );
                foreach ($lines as $line) {
                    $insertItem->execute([
                        'customer_delivery_note_id' => $noteId,
                        'sale_document_item_id' => (int)$line['sale_document_item_id'],
                        'product_id' => (int)$line['product_id'],
                        'description' => (string)$line['description'],
                        'quantity' => (float)$line['quantity_to_deliver'],
                    ]);
                }

                (new AuditService())->log('ventas', 'crear_remito_cliente', 'customer_delivery_notes', $noteId, null, [
                    'document_number' => $number,
                    'sale_document_number' => $saleDocument['document_number'],
                    'customer_id' => $customerId,
                ]);

                return $noteId;
            });

            Session::flash('success', 'Remito creado en borrador. Aprobá la entrega para descontar stock y consumir FIFO.');
            $this->redirect('/customer-delivery-notes/show?id=' . $noteId);
        } catch (Throwable $e) {
            Session::flash('error', $this->errorMessage($e));
            $this->oldInput($_POST);
            $redirect = '/customer-delivery-notes/create';
            if ($customerId > 0) {
                $redirect .= '?customer_id=' . $customerId;
                if ($saleDocumentId > 0) {
                    $redirect .= '&sale_document_id=' . $saleDocumentId;
                }
            }
            $this->redirect($redirect);
        }
    }

    public function show(): void
    {
        Permission::require('ventas.remitos.ver');
        $companyId = (int)Auth::companyId();
        $id = (int)($_GET['id'] ?? 0);
        $pdo = Database::pdo();

        $note = $this->deliveryNote($pdo, $companyId, $id);
        if (!$note) {
            http_response_code(404);
            $this->view('errors/404', ['title' => 'Remito no encontrado']);
            return;
        }

        $this->view('sales/delivery_notes/show', [
            'title' => 'Remito ' . (string)$note['document_number'],
            'note' => $note,
            'items' => $this->deliveryNoteItems($pdo, $id),
            'stockMovements' => $this->stockMovements($pdo, $id),
        ]);
    }

    public function approve(): void
    {
        Permission::require('ventas.remitos.aprobar');
        $this->csrf();
        $companyId = (int)Auth::companyId();
        $userId = (int)Auth::id();
        $id = (int)$this->input('id', 0);

        try {
            Database::transaction(function (PDO $pdo) use ($companyId, $userId, $id): void {
                $note = $this->lockDeliveryNote($pdo, $companyId, $id);
                if ((string)$note['status'] !== 'DRAFT') {
                    throw new RuntimeException('Solo se pueden aprobar remitos en borrador.');
                }

                $saleDocumentId = (int)($note['sale_document_id'] ?? 0);
                if ($saleDocumentId <= 0) {
                    throw new RuntimeException('El remito no tiene una venta vinculada.');
                }

                $saleDocument = $this->lockSaleDocument($pdo, $companyId, (int)$note['customer_id'], $saleDocumentId);
                if (in_array((string)$saleDocument['document_type'], [SaleType::VENTA_ANTICIPADA, SaleType::LEGACY_PREVENTA], true)) {
                    $balanceUsd = $this->saleDocumentOutstandingUsd($pdo, (int)$saleDocument['id']);
                    if ($balanceUsd > 0.0001) {
                        throw new RuntimeException('La venta anticipada debe estar totalmente pagada para retirar mercadería. Saldo pendiente: USD ' . number_format($balanceUsd, 2, ',', '.') . '.');
                    }
                }
                $stockService = new StockMovementService();
                $reservationService = new StockReservationService();

                $items = $this->deliveryNoteItemsForUpdate($pdo, $id);
                if ($items === []) {
                    throw new RuntimeException('El remito no tiene ítems para entregar.');
                }

                foreach ($items as $item) {
                    $quantity = round((float)$item['quantity'], 6);
                    if ($quantity <= 0) {
                        throw new RuntimeException('El remito tiene una línea con cantidad inválida.');
                    }

                    $saleItem = $this->lockSaleItem($pdo, $saleDocumentId, (int)$item['sale_document_item_id']);
                    $pending = round((float)$saleItem['quantity'] - (float)$saleItem['quantity_delivered'], 6);
                    if ($quantity - $pending > 0.000001) {
                        throw new RuntimeException('La cantidad a entregar de ' . (string)$saleItem['description'] . ' supera el pendiente.');
                    }

                    if ((int)$saleItem['manages_stock'] === 1) {
                        $reservedPending = $reservationService->reservedPendingForItem(
                            $pdo,
                            (int)$note['company_id'],
                            (int)$note['branch_id'],
                            (int)$note['warehouse_id'],
                            (int)$saleDocument['id'],
                            (int)$saleItem['id'],
                            (int)$saleItem['product_id']
                        );
                        $reservedToDeliver = min($quantity, $reservedPending);
                        if ($reservedToDeliver > 0.000001) {
                            $reservationService->deliverSaleDocumentItem(
                                $pdo,
                                (int)$note['company_id'],
                                (int)$note['branch_id'],
                                (int)$note['warehouse_id'],
                                (int)$saleDocument['id'],
                                (int)$saleItem['id'],
                                (int)$saleItem['product_id'],
                                $reservedToDeliver
                            );
                        }

                        $stockService->decreaseStock($pdo, [
                            'company_id' => (int)$note['company_id'],
                            'branch_id' => (int)$note['branch_id'],
                            'warehouse_id' => (int)$note['warehouse_id'],
                            'product_id' => (int)$saleItem['product_id'],
                            'source_type' => 'CUSTOMER_DELIVERY_NOTE',
                            'source_id' => (int)$note['id'],
                            'customer_delivery_note_item_id' => (int)$item['id'],
                            'notes' => 'Entrega cliente ' . (string)$note['document_number'] . ' / venta ' . (string)$saleDocument['document_number'],
                            'created_by' => $userId,
                        ], $quantity, 'OUT');
                    }

                    $updateItem = $pdo->prepare(
                        'UPDATE sale_document_items
                         SET quantity_delivered = quantity_delivered + :quantity_delivered
                         WHERE id = :sale_document_item_id'
                    );
                    $updateItem->execute([
                        'quantity_delivered' => $quantity,
                        'sale_document_item_id' => (int)$saleItem['id'],
                    ]);
                }

                $newSaleStatus = $this->saleDocumentDeliveryStatus($pdo, $saleDocumentId);
                $updateSale = $pdo->prepare('UPDATE sale_documents SET status = :status WHERE id = :id');
                $updateSale->execute(['status' => $newSaleStatus, 'id' => $saleDocumentId]);

                $updateNote = $pdo->prepare('UPDATE customer_delivery_notes SET status = "DELIVERED" WHERE id = :id');
                $updateNote->execute(['id' => $id]);

                (new AuditService())->log('ventas', 'aprobar_remito_cliente', 'customer_delivery_notes', $id, null, [
                    'document_number' => $note['document_number'],
                    'sale_document_number' => $saleDocument['document_number'],
                    'sale_status' => $newSaleStatus,
                ]);
            });

            Session::flash('success', 'Entrega aprobada. Se descontó stock, se consumió FIFO y se actualizó el estado de la venta.');
        } catch (Throwable $e) {
            Session::flash('error', $this->errorMessage($e));
        }

        $this->redirect('/customer-delivery-notes/show?id=' . $id);
    }

    public function void(): void
    {
        Permission::require('ventas.remitos.anular');
        $this->csrf();
        $companyId = (int)Auth::companyId();
        $id = (int)$this->input('id', 0);

        try {
            Database::transaction(function (PDO $pdo) use ($companyId, $id): void {
                $note = $this->lockDeliveryNote($pdo, $companyId, $id);
                if ((string)$note['status'] !== 'DRAFT') {
                    throw new RuntimeException('Solo se pueden anular remitos en borrador. Si ya fue entregado, debe hacerse una devolución/ajuste controlado.');
                }

                $stmt = $pdo->prepare('UPDATE customer_delivery_notes SET status = "VOID" WHERE id = :id');
                $stmt->execute(['id' => $id]);
            });

            Session::flash('success', 'Remito anulado correctamente. No se modificó stock.');
        } catch (Throwable $e) {
            Session::flash('error', $this->errorMessage($e));
        }

        $this->redirect('/customer-delivery-notes/show?id=' . $id);
    }

    private function customers(PDO $pdo, int $companyId): array
    {
        $stmt = $pdo->prepare('SELECT id, business_name, cuit FROM customers WHERE company_id = :company_id AND is_active = 1 ORDER BY business_name ASC LIMIT 1000');
        $stmt->execute(['company_id' => $companyId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function warehouses(PDO $pdo, int $companyId, int $branchId): array
    {
        $stmt = $pdo->prepare('SELECT id, name FROM warehouses WHERE company_id = :company_id AND branch_id = :branch_id AND is_active = 1 ORDER BY is_default DESC, name ASC');
        $stmt->execute(['company_id' => $companyId, 'branch_id' => $branchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function openSaleDocuments(PDO $pdo, int $companyId, int $selectedCustomerId = 0, int $selectedSaleDocumentId = 0): array
    {
        $sql =
            'SELECT sd.id, sd.customer_id, c.business_name AS customer_name, sd.document_number, sd.document_type, sd.status,
                    sd.issue_date, sd.warehouse_id, w.name AS warehouse_name,
                    SUM(GREATEST(sdi.quantity - sdi.quantity_delivered, 0)) AS pending_quantity
             FROM sale_documents sd
             INNER JOIN customers c ON c.id = sd.customer_id
             INNER JOIN warehouses w ON w.id = sd.warehouse_id
             INNER JOIN sale_document_items sdi ON sdi.sale_document_id = sd.id
             WHERE sd.company_id = :company_id
               AND sd.document_type IN ("PROFORMA_X", "VENTA_ANTICIPADA", "PREVENTA")
               AND sd.status IN ("RESERVED", "APPROVED", "PARTIAL_DELIVERED")';
        $params = ['company_id' => $companyId];

        if ($selectedCustomerId > 0) {
            $sql .= ' AND sd.customer_id = :customer_id';
            $params['customer_id'] = $selectedCustomerId;
        }
        if ($selectedSaleDocumentId > 0) {
            $sql .= ' AND sd.id = :sale_document_id';
            $params['sale_document_id'] = $selectedSaleDocumentId;
        }

        $sql .= ' GROUP BY sd.id, sd.customer_id, c.business_name, sd.document_number, sd.document_type, sd.status, sd.issue_date, sd.warehouse_id, w.name
                  HAVING pending_quantity > 0.000001
                  ORDER BY c.business_name ASC, sd.issue_date ASC, sd.id ASC
                  LIMIT 500';
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function openSaleItems(PDO $pdo, int $companyId, int $selectedCustomerId = 0, int $selectedSaleDocumentId = 0): array
    {
        $sql =
            'SELECT sdi.id AS sale_document_item_id,
                    sdi.sale_document_id,
                    sd.customer_id,
                    sd.document_number,
                    sd.document_type,
                    sd.status,
                    sd.warehouse_id,
                    p.id AS product_id,
                    p.code AS product_code,
                    p.name AS product_name,
                    p.manages_stock,
                    u.code AS unit_code,
                    sdi.description,
                    sdi.quantity,
                    sdi.quantity_reserved,
                    sdi.quantity_delivered,
                    GREATEST(sdi.quantity - sdi.quantity_delivered, 0) AS pending_quantity,
                    COALESCE(sb.quantity_on_hand, 0) AS quantity_on_hand,
                    COALESCE(sb.quantity_reserved, 0) AS quantity_reserved_total,
                    COALESCE(sb.quantity_available, 0) AS quantity_available
             FROM sale_document_items sdi
             INNER JOIN sale_documents sd ON sd.id = sdi.sale_document_id
             INNER JOIN products p ON p.id = sdi.product_id
             INNER JOIN units u ON u.id = p.unit_id
             LEFT JOIN stock_balances sb ON sb.product_id = p.id AND sb.warehouse_id = sd.warehouse_id
             WHERE sd.company_id = :company_id
               AND sd.document_type IN ("PROFORMA_X", "VENTA_ANTICIPADA", "PREVENTA")
               AND sd.status IN ("RESERVED", "APPROVED", "PARTIAL_DELIVERED")
               AND GREATEST(sdi.quantity - sdi.quantity_delivered, 0) > 0.000001';
        $params = ['company_id' => $companyId];

        if ($selectedCustomerId > 0) {
            $sql .= ' AND sd.customer_id = :customer_id';
            $params['customer_id'] = $selectedCustomerId;
        }
        if ($selectedSaleDocumentId > 0) {
            $sql .= ' AND sd.id = :sale_document_id';
            $params['sale_document_id'] = $selectedSaleDocumentId;
        }

        $sql .= ' ORDER BY sd.issue_date ASC, sd.id ASC, sdi.id ASC LIMIT 2000';
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function parseLines(PDO $pdo, int $companyId, int $saleDocumentId): array
    {
        $saleItemIds = $_POST['sale_document_item_id'] ?? [];
        $quantities = $_POST['quantity'] ?? [];
        if (!is_array($saleItemIds) || !is_array($quantities)) {
            return [];
        }

        $lines = [];
        $seen = min(count($saleItemIds), count($quantities));
        for ($i = 0; $i < $seen; $i++) {
            $saleItemId = (int)$saleItemIds[$i];
            $quantity = $this->dec((string)($quantities[$i] ?? '0'));
            if ($saleItemId <= 0 || $quantity <= 0) {
                continue;
            }

            $stmt = $pdo->prepare(
                'SELECT sdi.*, p.id AS product_id, p.manages_stock
                 FROM sale_document_items sdi
                 INNER JOIN sale_documents sd ON sd.id = sdi.sale_document_id
                 INNER JOIN products p ON p.id = sdi.product_id
                 WHERE sd.company_id = :company_id
                   AND sdi.sale_document_id = :sale_document_id
                   AND sdi.id = :sale_document_item_id
                 LIMIT 1'
            );
            $stmt->execute([
                'company_id' => $companyId,
                'sale_document_id' => $saleDocumentId,
                'sale_document_item_id' => $saleItemId,
            ]);
            $item = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$item) {
                throw new RuntimeException('Una línea seleccionada no pertenece a la venta elegida.');
            }

            $pending = round((float)$item['quantity'] - (float)$item['quantity_delivered'], 6);
            if ($quantity - $pending > 0.000001) {
                throw new RuntimeException('La cantidad a entregar de ' . (string)$item['description'] . ' supera el pendiente.');
            }

            $lines[] = [
                'sale_document_item_id' => $saleItemId,
                'product_id' => (int)$item['product_id'],
                'description' => (string)$item['description'],
                'quantity_to_deliver' => round($quantity, 6),
            ];
        }

        return $lines;
    }

    private function assertCustomer(PDO $pdo, int $companyId, int $customerId): void
    {
        $stmt = $pdo->prepare('SELECT id FROM customers WHERE company_id = :company_id AND id = :id AND is_active = 1 LIMIT 1');
        $stmt->execute(['company_id' => $companyId, 'id' => $customerId]);
        if (!$stmt->fetch()) {
            throw new RuntimeException('Cliente inválido o inactivo.');
        }
    }

    private function assertWarehouse(PDO $pdo, int $companyId, int $branchId, int $warehouseId): void
    {
        $stmt = $pdo->prepare('SELECT id FROM warehouses WHERE company_id = :company_id AND branch_id = :branch_id AND id = :id AND is_active = 1 LIMIT 1');
        $stmt->execute(['company_id' => $companyId, 'branch_id' => $branchId, 'id' => $warehouseId]);
        if (!$stmt->fetch()) {
            throw new RuntimeException('Depósito inválido para la sucursal actual.');
        }
    }

    private function lockSaleDocument(PDO $pdo, int $companyId, int $customerId, int $saleDocumentId): array
    {
        $stmt = $pdo->prepare(
            'SELECT * FROM sale_documents
             WHERE company_id = :company_id
               AND customer_id = :customer_id
               AND id = :id
               AND document_type IN ("PROFORMA_X", "VENTA_ANTICIPADA", "PREVENTA")
               AND status IN ("RESERVED", "APPROVED", "PARTIAL_DELIVERED")
             FOR UPDATE'
        );
        $stmt->execute(['company_id' => $companyId, 'customer_id' => $customerId, 'id' => $saleDocumentId]);
        $doc = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$doc) {
            throw new RuntimeException('El documento comercial no existe, no pertenece al cliente o no está disponible para entregar.');
        }
        return $doc;
    }

    private function lockSaleItem(PDO $pdo, int $saleDocumentId, int $saleDocumentItemId): array
    {
        $stmt = $pdo->prepare(
            'SELECT sdi.*, p.manages_stock, p.id AS product_id
             FROM sale_document_items sdi
             INNER JOIN products p ON p.id = sdi.product_id
             WHERE sdi.sale_document_id = :sale_document_id AND sdi.id = :sale_document_item_id
             FOR UPDATE'
        );
        $stmt->execute(['sale_document_id' => $saleDocumentId, 'sale_document_item_id' => $saleDocumentItemId]);
        $item = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$item) {
            throw new RuntimeException('Una línea del remito ya no pertenece a la venta.');
        }
        return $item;
    }

    private function lockDeliveryNote(PDO $pdo, int $companyId, int $id): array
    {
        $stmt = $pdo->prepare('SELECT * FROM customer_delivery_notes WHERE company_id = :company_id AND id = :id FOR UPDATE');
        $stmt->execute(['company_id' => $companyId, 'id' => $id]);
        $note = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$note) {
            throw new RuntimeException('Remito no encontrado.');
        }
        return $note;
    }

    private function deliveryNote(PDO $pdo, int $companyId, int $id): ?array
    {
        $stmt = $pdo->prepare(
            'SELECT cdn.*, c.business_name AS customer_name, c.cuit AS customer_cuit,
                    c.address AS customer_address, c.city AS customer_city, c.province AS customer_province,
                    b.name AS branch_name, w.name AS warehouse_name,
                    sd.document_number AS sale_document_number, sd.document_type AS sale_document_type, sd.status AS sale_document_status,
                    u.full_name AS created_by_name
             FROM customer_delivery_notes cdn
             INNER JOIN customers c ON c.id = cdn.customer_id
             INNER JOIN branches b ON b.id = cdn.branch_id
             INNER JOIN warehouses w ON w.id = cdn.warehouse_id
             LEFT JOIN sale_documents sd ON sd.id = cdn.sale_document_id
             LEFT JOIN users u ON u.id = cdn.created_by
             WHERE cdn.company_id = :company_id AND cdn.id = :id
             LIMIT 1'
        );
        $stmt->execute(['company_id' => $companyId, 'id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function deliveryNoteItems(PDO $pdo, int $noteId): array
    {
        $stmt = $pdo->prepare(
            'SELECT cdni.*, p.code AS product_code, p.manages_stock, u.code AS unit_code,
                    sdi.quantity AS sale_quantity,
                    sdi.quantity_delivered AS sale_quantity_delivered
             FROM customer_delivery_note_items cdni
             INNER JOIN products p ON p.id = cdni.product_id
             INNER JOIN units u ON u.id = p.unit_id
             LEFT JOIN sale_document_items sdi ON sdi.id = cdni.sale_document_item_id
             WHERE cdni.customer_delivery_note_id = :id
             ORDER BY cdni.id ASC'
        );
        $stmt->execute(['id' => $noteId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function deliveryNoteItemsForUpdate(PDO $pdo, int $noteId): array
    {
        $stmt = $pdo->prepare('SELECT * FROM customer_delivery_note_items WHERE customer_delivery_note_id = :id ORDER BY id ASC FOR UPDATE');
        $stmt->execute(['id' => $noteId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function stockMovements(PDO $pdo, int $noteId): array
    {
        $stmt = $pdo->prepare(
            'SELECT sm.*, p.code AS product_code, p.name AS product_name
             FROM stock_movements sm
             INNER JOIN products p ON p.id = sm.product_id
             WHERE sm.source_type = "CUSTOMER_DELIVERY_NOTE" AND sm.source_id = :id
             ORDER BY sm.id ASC'
        );
        $stmt->execute(['id' => $noteId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function saleDocumentOutstandingUsd(PDO $pdo, int $saleDocumentId): float
    {
        $stmt = $pdo->prepare(
            'SELECT sd.total_usd_reference - COALESCE(SUM(CASE WHEN r.status <> "VOID" THEN pa.amount_usd ELSE 0 END), 0) AS balance
             FROM sale_documents sd
             LEFT JOIN payment_allocations pa ON pa.sale_document_id = sd.id
             LEFT JOIN receipts r ON r.id = pa.receipt_id
             WHERE sd.id = :id
             GROUP BY sd.id, sd.total_usd_reference'
        );
        $stmt->execute(['id' => $saleDocumentId]);
        return max(0.0, round((float)$stmt->fetchColumn(), 4));
    }

    private function saleDocumentDeliveryStatus(PDO $pdo, int $saleDocumentId): string
    {
        $stmt = $pdo->prepare(
            'SELECT COALESCE(SUM(GREATEST(quantity - quantity_delivered, 0)), 0) AS pending
             FROM sale_document_items
             WHERE sale_document_id = :id'
        );
        $stmt->execute(['id' => $saleDocumentId]);
        $pending = (float)$stmt->fetchColumn();
        return $pending <= 0.000001 ? 'DELIVERED' : 'PARTIAL_DELIVERED';
    }

    private function dec(string $value): float
    {
        $text = trim($value);
        if ($text === '') {
            return 0.0;
        }
        if (str_contains($text, ',')) {
            $text = str_replace('.', '', $text);
            $text = str_replace(',', '.', $text);
        }
        return is_numeric($text) ? (float)$text : 0.0;
    }

    private function validDate(string $date): bool
    {
        $dt = \DateTimeImmutable::createFromFormat('Y-m-d', $date);
        return $dt instanceof \DateTimeImmutable && $dt->format('Y-m-d') === $date;
    }

    private function nullable(mixed $value): ?string
    {
        $text = trim((string)$value);
        return $text === '' ? null : $text;
    }

    private function errorMessage(Throwable $e): string
    {
        return $e instanceof RuntimeException ? $e->getMessage() : 'Error interno: ' . $e->getMessage();
    }
}