<?php

namespace Modules\Wms\Controllers;

use Gestoreasy\Core\Http\Request;
use Gestoreasy\Core\Http\Response;
use Gestoreasy\Core\Audit\AuditLogger;
use Gestoreasy\Core\Tenant\ConnectionManager;
use PDO;

class MobileController
{
    private static function pdo(?Request $req = null): PDO {
        try {
            $tenant = $req ? ($req->header('x-tenant-id') ?? null) : null;
            return ConnectionManager::forTenant($tenant);
        } catch (\Throwable $e) {
            // Fallback para tenant padrão quando contexto não disponível
            return ConnectionManager::forTenant('default');
        }
    }

    private static function ensureTables(Request $req): void
    {
        $pdo = self::pdo($req);
        // Queue table for asynchronous processing of mobile batches
        $pdo->exec("CREATE TABLE IF NOT EXISTS wms_inventory_queue (
            id INT AUTO_INCREMENT PRIMARY KEY,
            tenant_id INT NULL,
            device_id INT NULL,
            batch_id VARCHAR(64) NULL,
            payload_json JSON NOT NULL,
            status ENUM('received','validated','confirmed','processing','done','error') NOT NULL DEFAULT 'received',
            attempt_count INT NOT NULL DEFAULT 0,
            locked_at DATETIME NULL,
            result_json JSON NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_status_locked (status, locked_at),
            INDEX idx_batch (batch_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Raw staging entries captured from mobile before validation
        $pdo->exec("CREATE TABLE IF NOT EXISTS wms_inventory_raw (
            id INT AUTO_INCREMENT PRIMARY KEY,
            tenant_id INT NULL,
            session_id INT NULL,
            device_id INT NULL,
            product_id INT NULL,
            product_code VARCHAR(64) NULL,
            bin_id INT NULL,
            bin_code VARCHAR(64) NULL,
            quantity DECIMAL(12,3) NOT NULL,
            batch_id VARCHAR(64) NULL,
            status ENUM('received','validated','invalid') NOT NULL DEFAULT 'received',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_batch_status (batch_id, status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Definitive entries after validation/confirmation
        $pdo->exec("CREATE TABLE IF NOT EXISTS wms_inventory_entries (
            id INT AUTO_INCREMENT PRIMARY KEY,
            tenant_id INT NULL,
            session_id INT NULL,
            product_id INT NOT NULL,
            bin_id INT NOT NULL,
            quantity DECIMAL(12,3) NOT NULL,
            source_device_id INT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_session_bin_product (session_id, bin_id, product_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Registered mobile devices with optional API keys
        $pdo->exec("CREATE TABLE IF NOT EXISTS wms_mobile_devices (
            id INT AUTO_INCREMENT PRIMARY KEY,
            tenant_id INT NULL,
            name VARCHAR(128) NOT NULL,
            api_key VARCHAR(128) NULL,
            last_seen DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_api_key (api_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    }

    private static function resolveDevice(Request $req): ?array
    {
        $pdo = self::pdo($req);
        $apiKey = $req->header('x-api-key');
        if ($apiKey) {
            $stmt = $pdo->prepare('SELECT * FROM wms_mobile_devices WHERE api_key = ?');
            $stmt->execute([$apiKey]);
            $device = $stmt->fetch();
            if ($device) {
                $upd = $pdo->prepare('UPDATE wms_mobile_devices SET last_seen = NOW() WHERE id = ?');
                $upd->execute([$device['id']]);
                return $device;
            }
        }
        // Fallback: allow device_id from body for authenticated users
        $deviceId = $req->body['device_id'] ?? null;
        if ($deviceId) {
            $stmt = $pdo->prepare('SELECT * FROM wms_mobile_devices WHERE id = ?');
            $stmt->execute([$deviceId]);
            $device = $stmt->fetch();
            if ($device) {
                $upd = $pdo->prepare('UPDATE wms_mobile_devices SET last_seen = NOW() WHERE id = ?');
                $upd->execute([$device['id']]);
                return $device;
            }
        }
        return null;
    }

    private static function validateItems(PDO $pdo, array $items): array
    {
        $errors = [];
        $valid = [];
        foreach ($items as $idx => $item) {
            $line = $idx + 1;
            $productId = $item['product_id'] ?? null;
            $productCode = $item['product_code'] ?? null;
            $binId = $item['bin_id'] ?? null;
            $binCode = $item['bin_code'] ?? null;
            $qty = $item['quantity'] ?? null;

            if ($qty === null || !is_numeric($qty) || $qty < 0) {
                $errors[] = "Linha {$line}: quantidade inválida";
                continue;
            }

            $product = null;
            if ($productId) {
                $stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
                $stmt->execute([$productId]);
                $product = $stmt->fetch();
            } elseif ($productCode) {
                // Produtos usam coluna 'codigo' (SKU)
                $stmt = $pdo->prepare('SELECT * FROM products WHERE codigo = ?');
                $stmt->execute([$productCode]);
                $product = $stmt->fetch();
            }
            if (!$product) {
                $errors[] = "Linha {$line}: produto não encontrado";
                continue;
            }

            $bin = null;
            if ($binId) {
                $stmt = $pdo->prepare('SELECT * FROM bins WHERE id = ?');
                $stmt->execute([$binId]);
                $bin = $stmt->fetch();
            } elseif ($binCode) {
                $stmt = $pdo->prepare('SELECT * FROM bins WHERE code = ?');
                $stmt->execute([$binCode]);
                $bin = $stmt->fetch();
            }
            if (!$bin) {
                $errors[] = "Linha {$line}: bin não encontrado";
                continue;
            }

            $valid[] = [
                'product_id' => $product['id'],
                'product_code' => $product['codigo'] ?? null,
                'bin_id' => $bin['id'],
                'bin_code' => $bin['code'] ?? null,
                'quantity' => (float)$qty,
            ];
        }
        return [$valid, $errors];
    }

    public static function ingest(Request $req): Response
    {
        self::ensureTables($req);
        $device = self::resolveDevice($req);
        $batchId = $req->body['batch_id'] ?? null;
        $items = $req->body['items'] ?? [];

        if (!is_array($items) || empty($items)) {
            return Response::json(['error' => 'Itens ausentes ou inválidos'], 400);
        }

        $pdo = self::pdo($req);
        [$valid, $errors] = self::validateItems($pdo, $items);

        // Stage raw rows regardless of validation outcome
        // Stage raw rows regardless of validation outcome
        foreach ($items as $item) {
            $ins = $pdo->prepare('INSERT INTO wms_inventory_raw (tenant_id, device_id, product_id, product_code, bin_id, bin_code, quantity, batch_id, status) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?)');
            $ins->execute([
                $device['id'] ?? null,
                $item['product_id'] ?? null,
                $item['product_code'] ?? null,
                $item['bin_id'] ?? null,
                $item['bin_code'] ?? null,
                $item['quantity'] ?? 0,
                $batchId,
                empty($errors) ? 'validated' : 'received',
            ]);
        }

        $queueStatus = empty($errors) ? 'validated' : 'received';
        $q = $pdo->prepare('INSERT INTO wms_inventory_queue (tenant_id, device_id, batch_id, payload_json, status) VALUES (NULL, ?, ?, ?, ?)');
        $q->execute([
            $device['id'] ?? null,
            $batchId,
            json_encode(['items' => $items]),
            $queueStatus,
        ]);

        AuditLogger::log(
            'create',
            'wms.mobile',
            $device['id'] ?? null,
            null,
            [
                'action' => 'ingest',
                'batch_id' => $batchId,
                'device_id' => $device['id'] ?? null,
                'count' => count($items),
                'errors' => $errors,
            ],
            ['batch_id' => $batchId]
        );

        return Response::json([
            'status' => $queueStatus,
            'batch_id' => $batchId,
            'device_id' => $device['id'] ?? null,
            'errors' => $errors,
            'valid_count' => count($valid),
            'received_count' => count($items),
        ], empty($errors) ? 202 : 207); // 202 Accepted, 207 Multi-Status
    }

    public static function validate(Request $req): Response
    {
        self::ensureTables($req);

        $items = $req->body['items'] ?? [];
        if (!is_array($items) || empty($items)) {
            return Response::json(['error' => 'Itens ausentes ou inválidos'], 400);
        }

        [$valid, $errors] = self::validateItems(self::pdo($req), $items);
        AuditLogger::log(
            'read',
            'wms.mobile',
            null,
            null,
            null,
            [
                'action' => 'validate',
                'valid' => count($valid),
                'errors' => $errors,
            ]
        );

        return Response::json([
            'valid_items' => $valid,
            'errors' => $errors,
            'valid_count' => count($valid),
            'error_count' => count($errors),
        ], empty($errors) ? 200 : 207);
    }

    public static function confirm(Request $req): Response
    {
        self::ensureTables($req);

        $batchId = $req->body['batch_id'] ?? null;
        $sessionId = $req->body['session_id'] ?? null;
        if (!$batchId || !$sessionId) {
            return Response::json(['error' => 'Parâmetros obrigatórios: batch_id, session_id'], 400);
        }

        $pdo = self::pdo($req);
        $stmt = $pdo->prepare('SELECT * FROM wms_inventory_queue WHERE batch_id = ? ORDER BY id DESC');
        $stmt->execute([$batchId]);
        $queue = $stmt->fetch();
        if (!$queue) {
            return Response::json(['error' => 'Lote não encontrado'], 404);
        }
        $upd = $pdo->prepare('UPDATE wms_inventory_queue SET status = ?, updated_at = NOW() WHERE id = ?');
        $upd->execute(['confirmed', $queue['id']]);

        AuditLogger::log(
            'update',
            'wms.inventory.queue',
            (int)$queue['id'],
            null,
            [
                'action' => 'confirm',
                'batch_id' => $batchId,
                'session_id' => $sessionId,
            ]
        );

        return Response::json(['status' => 'confirmed', 'batch_id' => $batchId]);
    }

    public static function listPending(Request $req): Response
    {
        self::ensureTables($req);
        $pdo = self::pdo($req);
        $rows = $pdo->query("SELECT id, batch_id, status, attempt_count, locked_at, created_at FROM wms_inventory_queue WHERE status IN ('received','validated','confirmed') ORDER BY created_at ASC LIMIT 100")->fetchAll();
        return Response::json(['pending' => $rows]);
    }

    public static function processPending(Request $req): Response
    {
        self::ensureTables($req);
        $pdo = self::pdo($req);
        $limit = (int)($req->body['limit'] ?? 10);
        $processed = 0;
        $errors = [];

        $stmt = $pdo->query("SELECT * FROM wms_inventory_queue WHERE status IN ('validated','confirmed') AND (locked_at IS NULL OR locked_at < DATE_SUB(NOW(), INTERVAL 5 MINUTE)) ORDER BY id ASC LIMIT " . (int)$limit);
        $jobs = $stmt->fetchAll();
        foreach ($jobs as $job) {
            $lock = $pdo->prepare('UPDATE wms_inventory_queue SET locked_at = NOW(), status = ? WHERE id = ? AND (locked_at IS NULL OR locked_at < DATE_SUB(NOW(), INTERVAL 5 MINUTE))');
            $lock->execute(['processing', $job['id']]);
            $payload = json_decode($job['payload_json'] ?? '{}', true);
            $items = $payload['items'] ?? [];
            [$valid, $valErrors] = self::validateItems($pdo, $items);
            if (!empty($valErrors) && empty($valid)) {
                // Só erros, nenhum item válido
                $updErr = $pdo->prepare('UPDATE wms_inventory_queue SET status = ?, result_json = ?, attempt_count = attempt_count + 1 WHERE id = ?');
                $updErr->execute(['error', json_encode(['errors' => $valErrors]), $job['id']]);
                $errors[] = ['batch_id' => $job['batch_id'], 'errors' => $valErrors];
                continue;
            }
            // Persistir itens válidos mesmo havendo erros parciais
            foreach ($valid as $vi) {
                $ins = $pdo->prepare('INSERT INTO wms_inventory_entries (tenant_id, session_id, product_id, bin_id, quantity, source_device_id) VALUES (NULL, ?, ?, ?, ?, ?)');
                $ins->execute([
                    $req->body['session_id'] ?? null,
                    $vi['product_id'],
                    $vi['bin_id'],
                    $vi['quantity'],
                    $job['device_id'] ?? null,
                ]);
            }
            $result = ['persisted' => count($valid)];
            if (!empty($valErrors)) { $result['errors'] = $valErrors; }
            $done = $pdo->prepare('UPDATE wms_inventory_queue SET status = ?, result_json = ?, updated_at = NOW() WHERE id = ?');
            $done->execute(['done', json_encode($result), $job['id']]);
            $processed++;
        }

        AuditLogger::log(
            'update',
            'wms.inventory.queue',
            null,
            null,
            [
                'action' => 'process',
                'processed' => $processed,
                'errors' => $errors,
            ]
        );

        return Response::json(['processed' => $processed, 'errors' => $errors]);
    }

    /**
     * Lista pedidos pendentes de picking para o coletor móvel
     */
    public static function listPickingPedidos(Request $req): Response {
        $pdo = self::pdo($req);
        
        $limit = (int)($req->query['limit'] ?? 50);
        $offset = (int)($req->query['offset'] ?? 0);

        // Buscar pedidos com lista de picking ativa
        $sql = '
            SELECT 
                so.id,
                so.numero,
                c.nome as cliente_nome,
                so.valor_total,
                wpl.id as picking_list_id,
                wpl.status as picking_status,
                COUNT(DISTINCT wpli.id) as total_itens,
                SUM(CASE WHEN wpli.status = "separado" THEN 1 ELSE 0 END) as itens_separados
            FROM sales_orders so
            INNER JOIN clients c ON c.id = so.cliente_id
            INNER JOIN wms_picking_list wpl ON wpl.sales_order_id = so.id
            LEFT JOIN wms_picking_list_items wpli ON wpli.picking_list_id = wpl.id
            WHERE wpl.status IN ("aberto", "em_separacao")
            GROUP BY so.id, so.numero, c.nome, so.valor_total, wpl.id, wpl.status
            ORDER BY so.created_at ASC
            LIMIT ? OFFSET ?
        ';

        $stmt = $pdo->prepare($sql);
        $stmt->execute([$limit, $offset]);
        $pedidos = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        return Response::json([
            'data' => $pedidos,
            'limit' => $limit,
            'offset' => $offset
        ]);
    }

    /**
     * Obtém itens de picking para um pedido (para coletor móvel)
     */
    public static function getPickingItens(Request $req): Response {
        $pdo = self::pdo($req);
        $pickingListId = (int)($req->get('params')[0] ?? 0);

        if (!$pickingListId) {
            return Response::json(['error' => 'picking_list_id é obrigatório'], 422);
        }

        $stmt = $pdo->prepare('
            SELECT 
                wpli.id,
                wpli.product_id,
                p.codigo,
                p.nome,
                wpli.quantidade,
                wpli.quantidade_separada,
                wpli.bin_id,
                wpli.bin_code,
                b.description as bin_description,
                wpli.ordem,
                wpli.status,
                COALESCE(
                    SUM(CASE WHEN bm.to_bin_id = wpli.bin_id THEN bm.quantity ELSE 0 END) -
                    SUM(CASE WHEN bm.from_bin_id = wpli.bin_id THEN bm.quantity ELSE 0 END),
                    0
                ) as estoque_disponivel_bin
            FROM wms_picking_list_items wpli
            INNER JOIN products p ON p.id = wpli.product_id
            LEFT JOIN bins b ON b.id = wpli.bin_id
            LEFT JOIN bin_movements bm ON bm.product_id = wpli.product_id AND (bm.to_bin_id = wpli.bin_id OR bm.from_bin_id = wpli.bin_id)
            WHERE wpli.picking_list_id = ?
            GROUP BY wpli.id, wpli.product_id, p.codigo, p.nome, wpli.quantidade, wpli.quantidade_separada, wpli.bin_id, wpli.bin_code, b.description, wpli.ordem, wpli.status
            ORDER BY wpli.ordem ASC
        ');
        $stmt->execute([$pickingListId]);
        $itens = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        return Response::json(['data' => $itens]);
    }

    /**
     * Confirma separação de item via coletor móvel
     */
    public static function confirmarPickingMobile(Request $req): Response {
        $pdo = self::pdo($req);
        $itemId = (int)($req->get('params')[0] ?? 0);
        $body = is_array($req->body) ? $req->body : [];
        $quantidadeSeparada = (float)($body['quantidade'] ?? 0);
        $binId = isset($body['bin_id']) ? (int)$body['bin_id'] : null;
        $binCode = isset($body['bin_code']) ? (string)$body['bin_code'] : null;

        if (!$itemId) {
            return Response::json(['error' => 'item_id é obrigatório'], 422);
        }

        try {
            $pdo->beginTransaction();

            // Buscar item de picking
            $stmt = $pdo->prepare('
                SELECT 
                    wpli.*,
                    wpl.sales_order_id
                FROM wms_picking_list_items wpli
                INNER JOIN wms_picking_list wpl ON wpl.id = wpli.picking_list_id
                WHERE wpli.id = ?
            ');
            $stmt->execute([$itemId]);
            $item = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (!$item) {
                $pdo->rollBack();
                return Response::json(['error' => 'Item de picking não encontrado'], 404);
            }

            // Se bin foi informado, validar e atualizar
            if ($binId || $binCode) {
                $bin = null;
                if ($binId) {
                    $stmt = $pdo->prepare('SELECT * FROM bins WHERE id = ?');
                    $stmt->execute([$binId]);
                    $bin = $stmt->fetch(\PDO::FETCH_ASSOC);
                } elseif ($binCode) {
                    $stmt = $pdo->prepare('SELECT * FROM bins WHERE code = ?');
                    $stmt->execute([$binCode]);
                    $bin = $stmt->fetch(\PDO::FETCH_ASSOC);
                }

                if ($bin) {
                    // Validar se há estoque no bin
                    $stmt = $pdo->prepare('
                        SELECT 
                            COALESCE(
                                SUM(CASE WHEN bm.to_bin_id = ? THEN bm.quantity ELSE 0 END) -
                                SUM(CASE WHEN bm.from_bin_id = ? THEN bm.quantity ELSE 0 END),
                                0
                            ) as estoque
                        FROM bin_movements bm
                        WHERE bm.product_id = ?
                    ');
                    $stmt->execute([$bin['id'], $bin['id'], $item['product_id']]);
                    $estoque = $stmt->fetch(\PDO::FETCH_ASSOC);
                    $estoqueDisponivel = (float)($estoque['estoque'] ?? 0);

                    if ($estoqueDisponivel < $quantidadeSeparada) {
                        $pdo->rollBack();
                        return Response::json([
                            'error' => 'Estoque insuficiente no bin',
                            'estoque_disponivel' => $estoqueDisponivel,
                            'quantidade_solicitada' => $quantidadeSeparada
                        ], 422);
                    }

                    // Atualizar bin do item
                    $stmt = $pdo->prepare('
                        UPDATE wms_picking_list_items
                        SET bin_id = ?, bin_code = ?
                        WHERE id = ?
                    ');
                    $stmt->execute([$bin['id'], $bin['code'], $itemId]);
                }
            }

            if ($quantidadeSeparada <= 0) {
                $quantidadeSeparada = (float)$item['quantidade'];
            }

            // Atualizar status do item
            $status = $quantidadeSeparada >= (float)$item['quantidade'] ? 'separado' : 'parcial';
            $stmt = $pdo->prepare('
                UPDATE wms_picking_list_items
                SET quantidade_separada = ?,
                    status = ?,
                    separado_em = NOW()
                WHERE id = ?
            ');
            $stmt->execute([$quantidadeSeparada, $status, $itemId]);

            // Verificar se todos os itens foram separados
            $stmt = $pdo->prepare('
                SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status IN ("separado", "parcial") THEN 1 ELSE 0 END) as separados
                FROM wms_picking_list_items
                WHERE picking_list_id = ?
            ');
            $stmt->execute([$item['picking_list_id']]);
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);

            if ((int)$result['separados'] >= (int)$result['total']) {
                // Todos separados, atualizar status da lista
                $stmt = $pdo->prepare('
                    UPDATE wms_picking_list
                    SET status = "concluido",
                        concluido_em = NOW()
                    WHERE id = ?
                ');
                $stmt->execute([$item['picking_list_id']]);

                // Atualizar status do pedido
                $stmt = $pdo->prepare('
                    UPDATE sales_orders
                    SET status = "separado"
                    WHERE id = ?
                ');
                $stmt->execute([$item['sales_order_id']]);
            }

            $pdo->commit();

            AuditLogger::log('wms_picking', 'confirm_mobile', [
                'item_id' => $itemId,
                'picking_list_id' => $item['picking_list_id'],
                'quantidade_separada' => $quantidadeSeparada,
                'status' => $status
            ]);

            return Response::json([
                'data' => [
                    'item_id' => $itemId,
                    'quantidade_separada' => $quantidadeSeparada,
                    'status' => $status,
                    'picking_completo' => (int)$result['separados'] >= (int)$result['total']
                ]
            ]);

        } catch (\Exception $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            return Response::json(['error' => 'Erro ao confirmar separação: ' . $e->getMessage()], 500);
        }
    }
}