<?php

namespace WMS\Services;

use WMS\Core\Database;
use WMS\Repositories\StockRepository;
use WMS\Repositories\StockMovementRepository;
use WMS\Repositories\WarehouseTaskRepository;
use WMS\Repositories\PickListRepository;
use WMS\Repositories\BinRepository;
use WMS\Services\AuditService;

/**
 * Servicio de Picking.
 *
 * Flujo:
 * 1. Se crea pick list desde pedido SAP (o manual)
 * 2. Se asignan lotes por FEFO automáticamente
 * 3. Se generan tareas de PICK por cada línea
 * 4. El operador confirma picking escaneando bin + lote
 * 5. Se descuenta stock y se mueve a bin SHIPPING/STAGING
 */
class PickingService
{
    private StockRepository $stockRepo;
    private StockMovementRepository $movementRepo;
    private WarehouseTaskRepository $taskRepo;
    private PickListRepository $pickListRepo;
    private BinRepository $binRepo;

    public function __construct()
    {
        $this->stockRepo    = new StockRepository();
        $this->movementRepo = new StockMovementRepository();
        $this->taskRepo     = new WarehouseTaskRepository();
        $this->pickListRepo = new PickListRepository();
        $this->binRepo      = new BinRepository();
    }

    /**
     * Genera una pick list y asigna lotes por FEFO.
     */
    public function createPickList(array $data, int $userId): array
    {
        $warehouseId = (int) $data['warehouse_id'];
        $pickNumber = Database::nextDocNumber('PICK');

        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            // Normalizar strings vacios -> null (MariaDB rechaza '' en columnas DATE/etc)
            foreach (['ship_date','customer_code','customer_name','sap_doc_num','notes'] as $k) {
                if (isset($data[$k]) && $data[$k] === '') $data[$k] = null;
            }

            // Validar contra SAP que sap_doc_entry sea un DocEntry real (no un DocNum confundido).
            // Backfill sap_doc_num, customer_code y customer_name desde la Order autoritativa.
            // Si SAP no responde, tolerar y seguir (no romper la creacion).
            if (!empty($data['sap_doc_entry'])) {
                try {
                    $sl = new \WMS\Integrations\Sap\SapServiceLayerClient();
                    $sl->login();
                    $order = $sl->getSalesOrder((int)$data['sap_doc_entry']);
                    $sl->logout();
                    // Auto-populate desde SAP (fuente de verdad)
                    if (!empty($order['DocNum']))   $data['sap_doc_num']   = (int)$order['DocNum'];
                    if (!empty($order['CardCode'])) $data['customer_code'] = $data['customer_code'] ?: $order['CardCode'];
                    if (!empty($order['CardName'])) $data['customer_name'] = $data['customer_name'] ?: $order['CardName'];
                    // Si el cliente provisto no coincide con SAP, abortar (alerta de DocEntry mal)
                    if (!empty($data['customer_name']) && !empty($order['CardName'])
                        && mb_strtoupper(trim($data['customer_name'])) !== mb_strtoupper(trim($order['CardName']))) {
                        throw new \RuntimeException(
                            "Cliente no coincide con SAP. DocEntry {$data['sap_doc_entry']} pertenece a '{$order['CardName']}' " .
                            "(DocNum {$order['DocNum']}), no a '{$data['customer_name']}'. " .
                            "Posible confusion DocEntry vs DocNum."
                        );
                    }
                } catch (\RuntimeException $e) {
                    // Errores de validacion de negocio (cliente no coincide) — propagar
                    throw $e;
                } catch (\Throwable $e) {
                    $msg = $e->getMessage();
                    // SAP devuelve "No matching records found" si DocEntry no existe
                    if (stripos($msg, 'No matching') !== false || stripos($msg, '404') !== false || stripos($msg, 'not found') !== false) {
                        throw new \RuntimeException("DocEntry {$data['sap_doc_entry']} no existe en SAP. ¿Estas usando el DocNum por error?");
                    }
                    // Otros errores SAP (red, timeout) — tolerar y seguir
                    error_log('[PickingService] SAP validation soft-fail: ' . $msg);
                }
            }
            // Crear cabecera
            $pickListId = $this->pickListRepo->insert([
                'pick_number'   => $pickNumber,
                'warehouse_id'  => $warehouseId,
                'status'        => 'OPEN',
                'sap_doc_entry' => $data['sap_doc_entry'] ?? null,
                'sap_doc_num'   => $data['sap_doc_num'] ?? null,
                'customer_code' => $data['customer_code'] ?? null,
                'customer_name' => $data['customer_name'] ?? null,
                'ship_date'     => $data['ship_date'] ?? null,
                'notes'         => $data['notes'] ?? null,
                'created_by'    => $userId,
            ]);

            // Procesar líneas con asignación FEFO
            foreach ($data['lines'] as $i => $line) {
                $itemId = (int) $line['item_id'];
                $requestedQty = (float) ($line['quantity'] ?? $line['requested_qty'] ?? 0);

                // Obtener stock disponible ordenado por FEFO
                $available = $this->stockRepo->getAvailableForPicking($itemId, $warehouseId);

                $remainingQty = $requestedQty;
                $lineNum = $i + 1;

                foreach ($available as $stock) {
                    if ($remainingQty <= 0) break;

                    $availableQty = (float) $stock['available_qty'];
                    $allocateQty = min($remainingQty, $availableQty);

                    // Insertar línea de pick list
                    $stmt = $db->prepare(
                        "INSERT INTO pick_list_lines
                         (pick_list_id, line_num, item_id, batch_id, from_bin_id, requested_qty, uom, sap_line_num)
                         VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
                    );
                    $stmt->execute([
                        $pickListId,
                        $lineNum,
                        $itemId,
                        $stock['batch_id'],
                        $stock['bin_id'],
                        $allocateQty,
                        $stock['uom'] ?? 'UN',
                        $line['sap_line_num'] ?? null,
                    ]);

                    // Reservar stock
                    $this->stockRepo->reserve($stock['id'], $allocateQty);

                    // Crear tarea de picking, auto-asignada a op_picking online (round-robin)
                    $assignee = $this->pickOnlineOpPicking($warehouseId);
                    $this->taskRepo->insert([
                        'warehouse_id'   => $warehouseId,
                        'task_type'      => 'PICK',
                        'priority'       => 2,
                        'status'         => $assignee ? 'ASSIGNED' : 'PENDING',
                        'item_id'        => $itemId,
                        'batch_id'       => $stock['batch_id'],
                        'from_bin_id'    => $stock['bin_id'],
                        'quantity'       => $allocateQty,
                        'assigned_to'    => $assignee,
                        'reference_type' => 'PICK_LIST',
                        'reference_id'   => $pickListId,
                        'created_by'     => $userId,
                    ]);

                    $remainingQty -= $allocateQty;
                    $lineNum++;
                }

                if ($remainingQty > 0) {
                    // Stock insuficiente: registrar short
                    $stmt = $db->prepare(
                        "INSERT INTO pick_list_lines
                         (pick_list_id, line_num, item_id, requested_qty, uom, status, notes)
                         VALUES (?, ?, ?, ?, 'UN', 'SHORT', 'Stock insuficiente')"
                    );
                    $stmt->execute([$pickListId, $lineNum, $itemId, $remainingQty]);
                }
            }

            $db->commit();

            AuditService::log('pick_lists', $pickListId, 'PICK_LIST_CREATE', null, [
                'pick_number'   => $pickNumber,
                'warehouse_id'  => $warehouseId,
                'sap_doc_entry' => $data['sap_doc_entry'] ?? null,
                'sap_doc_num'   => $data['sap_doc_num'] ?? null,
                'customer_code' => $data['customer_code'] ?? null,
                'lines_count'   => count($data['lines'] ?? []),
            ], $userId);

            return $this->pickListRepo->getWithLines($pickListId);
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Confirma el picking de una tarea.
     * Descuenta stock del bin origen y registra movimiento.
     */
    public function confirmPick(int $taskId, array $data, int $userId): array
    {
        $task = $this->taskRepo->findById($taskId);
        if (!$task || $task['task_type'] !== 'PICK') {
            throw new \RuntimeException('Tarea de picking no válida');
        }
        if ($task['status'] === 'COMPLETED') {
            throw new \RuntimeException('Esta tarea ya fue confirmada');
        }

        $confirmedQty = (float) ($data['confirmed_qty'] ?? $task['quantity']);

        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            // Descontar stock
            $stockId = $this->stockRepo->findOrCreatePosition(
                $task['warehouse_id'],
                $task['from_bin_id'],
                $task['item_id'],
                $task['batch_id'],
                'AVAILABLE'
            );

            // Liberar reserva y descontar
            $this->stockRepo->unreserve($stockId, $task['quantity']);
            $this->stockRepo->subtractQuantity($stockId, $confirmedQty);

            // Registrar movimiento
            $this->movementRepo->logMovement([
                'warehouse_id'  => $task['warehouse_id'],
                'movement_type' => 'PICK',
                'item_id'       => $task['item_id'],
                'batch_id'      => $task['batch_id'],
                'from_bin_id'   => $task['from_bin_id'],
                'from_status'   => 'AVAILABLE',
                'quantity'      => $confirmedQty,
                'reference_type'=> 'PICK_LIST',
                'reference_id'  => $task['reference_id'],
                'created_by'    => $userId,
            ]);

            // Completar tarea
            $this->taskRepo->completeTask($taskId, $confirmedQty);

            // Actualizar línea del pick list
            if ($task['reference_id']) {
                $db->prepare(
                    "UPDATE pick_list_lines SET picked_qty = picked_qty + ?, status = 'PICKED'
                     WHERE pick_list_id = ? AND item_id = ? AND batch_id <=> ? AND status != 'PICKED'
                     LIMIT 1"
                )->execute([$confirmedQty, $task['reference_id'], $task['item_id'], $task['batch_id']]);

                // Si todas las líneas están PICKED/SHORT, cerrar la pick list
                $stmtCheck = $db->prepare(
                    "SELECT
                        SUM(CASE WHEN status NOT IN ('PICKED','SHORT','CANCELLED') THEN 1 ELSE 0 END) AS pendientes,
                        SUM(CASE WHEN status = 'PICKED' THEN 1 ELSE 0 END) AS picked
                     FROM pick_list_lines WHERE pick_list_id = ?"
                );
                $stmtCheck->execute([$task['reference_id']]);
                $cnt = $stmtCheck->fetch();
                if ((int)$cnt['pendientes'] === 0 && (int)$cnt['picked'] > 0) {
                    $db->prepare(
                        "UPDATE pick_lists SET status = 'COMPLETED', completed_at = NOW() WHERE id = ? AND status != 'COMPLETED'"
                    )->execute([$task['reference_id']]);
                } else {
                    $db->prepare(
                        "UPDATE pick_lists SET status = 'IN_PROGRESS' WHERE id = ? AND status = 'OPEN'"
                    )->execute([$task['reference_id']]);
                }
            }

            $db->commit();

            AuditService::log('warehouse_tasks', $taskId, 'PICK_CONFIRM', [
                'requested_qty' => (float)$task['quantity'],
                'task_status'   => $task['status'],
            ], [
                'pick_list_id' => $task['reference_id'],
                'item_id'      => $task['item_id'],
                'batch_id'     => $task['batch_id'],
                'from_bin_id'  => $task['from_bin_id'],
                'confirmed_qty'=> $confirmedQty,
                'short_qty'    => max(0, (float)$task['quantity'] - $confirmedQty),
            ], $userId);

            return $this->taskRepo->findById($taskId);
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Selecciona op_picking online (last_activity_at <= 5 min) con menor carga.
     * Fallback al primer op_picking activo.
     */
    private function pickOnlineOpPicking(int $warehouseId): ?int
    {
        $db = Database::getConnection();
        $sql = "SELECT u.id, COALESCE(SUM(CASE WHEN t.status IN ('PENDING','ASSIGNED','IN_PROGRESS') THEN 1 ELSE 0 END), 0) AS load_active
                FROM users u
                LEFT JOIN warehouse_tasks t ON t.assigned_to = u.id
                WHERE u.role = 'op_picking' AND u.is_active = 1
                  AND u.last_activity_at >= (NOW() - INTERVAL 5 MINUTE)
                  AND (u.warehouse_id = ? OR u.warehouse_id IS NULL)
                GROUP BY u.id
                ORDER BY load_active ASC, u.last_activity_at DESC, u.id ASC LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->execute([$warehouseId]);
        $row = $stmt->fetch();
        if ($row) return (int) $row['id'];

        $stmt = $db->prepare(
            "SELECT u.id FROM users u
             WHERE u.role = 'op_picking' AND u.is_active = 1
               AND (u.warehouse_id = ? OR u.warehouse_id IS NULL)
             ORDER BY u.id ASC LIMIT 1"
        );
        $stmt->execute([$warehouseId]);
        $row = $stmt->fetch();
        return $row ? (int) $row['id'] : null;
    }
}