<?php
namespace Modules\Wms\Controllers;

use Gestoreasy\Core\Http\Request;
use Gestoreasy\Core\Http\Response;
use Gestoreasy\Core\Tenant\ConnectionManager;

class AnalyticsController {
    private static function pdo(): \PDO {
        return ConnectionManager::forTenant();
    }

    private static function fmtDateTime(string $ts): string {
        $dt = strtotime($ts);
        return date('d-m-Y H:i', $dt ?: time());
    }

    private static function fmtDay(string $ts): string {
        $dt = strtotime($ts);
        return date('d-m-Y', $dt ?: time());
    }

    private static function toInt(mixed $q): int { return (int)round((float)$q); }

    private static function parseDateBR(?string $s): ?string {
        if (!$s) return null;
        $s = trim($s);
        $dt = \DateTime::createFromFormat('d-m-Y', $s)
            ?: \DateTime::createFromFormat('d/m/Y', $s)
            ?: \DateTime::createFromFormat('Y-m-d', $s);
        if (!$dt) return null;
        return $dt->format('Y-m-d');
    }

    /**
     * BI: visão geral em tempo real e agregados por dia (últimos 7)
     */
    public function overview(Request $req): Response {
        $pdo = self::pdo();
        $deBr = $req->get('de');
        $ateBr = $req->get('ate');
        $categoria = $req->get('categoria'); // usa product_code
        $tipo = $req->get('tipo'); // 'recebimento' | 'expedicao' | null
        $de = self::parseDateBR(is_string($deBr) ? $deBr : null);
        $ate = self::parseDateBR(is_string($ateBr) ? $ateBr : null);

        // Construir filtros
        $filtersRec = [];
        $paramsRec = [];
        $filtersShip = [];
        $paramsShip = [];
        if ($de) { $filtersRec[] = 'DATE(created_at) >= :de'; $paramsRec[':de'] = $de; $filtersShip[] = 'DATE(created_at) >= :de'; $paramsShip[':de'] = $de; }
        if ($ate) { $filtersRec[] = 'DATE(created_at) <= :ate'; $paramsRec[':ate'] = $ate; $filtersShip[] = 'DATE(created_at) <= :ate'; $paramsShip[':ate'] = $ate; }
        if ($categoria) { $filtersRec[] = 'product_code = :pc'; $paramsRec[':pc'] = (string)$categoria; $filtersShip[] = 'product_code = :pc'; $paramsShip[':pc'] = (string)$categoria; }
        $whereRec = $filtersRec ? ('WHERE ' . implode(' AND ', $filtersRec)) : 'WHERE DATE(created_at)=CURDATE()';
        $whereShip = $filtersShip ? ('WHERE ' . implode(' AND ', $filtersShip)) : 'WHERE DATE(created_at)=CURDATE()';

        // Totais no período (default: hoje) a partir de tabelas RAW
        $sqlRecTotal = "SELECT COALESCE(SUM(quantity),0) FROM wms_receiving_raw $whereRec";
        $sqlShipTotal = "SELECT COALESCE(SUM(quantity),0) FROM wms_shipping_raw $whereShip";
        $recStmt = $pdo->prepare($sqlRecTotal); $recStmt->execute($paramsRec); $recTotal = (int)($recStmt->fetchColumn() ?? 0);
        $shipStmt = $pdo->prepare($sqlShipTotal); $shipStmt->execute($paramsShip); $shipTotal = (int)($shipStmt->fetchColumn() ?? 0);

        // Série por dia (default: últimos 7 dias) a partir de tabelas RAW
        $rangeRec = $filtersRec ? $whereRec : 'WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)';
        $rangeShip = $filtersShip ? $whereShip : 'WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)';
        $sqlRecDays = "SELECT DATE(created_at) d, SUM(quantity) q FROM wms_receiving_raw $rangeRec GROUP BY d ORDER BY d DESC";
        $sqlShipDays = "SELECT DATE(created_at) d, SUM(quantity) q FROM wms_shipping_raw $rangeShip GROUP BY d ORDER BY d DESC";
        $recDaysStmt = $pdo->prepare($sqlRecDays); $recDaysStmt->execute($paramsRec); $recDays = $recDaysStmt->fetchAll() ?: [];
        $shipDaysStmt = $pdo->prepare($sqlShipDays); $shipDaysStmt->execute($paramsShip); $shipDays = $shipDaysStmt->fetchAll() ?: [];
        $recSeries = array_map(fn($r)=> ['dia'=> self::fmtDay($r['d']), 'quantidade'=> self::toInt($r['q'])], $recDays);
        $shipSeries = array_map(fn($r)=> ['dia'=> self::fmtDay($r['d']), 'quantidade'=> self::toInt($r['q'])], $shipDays);

        // Fallback: se não houver dados nas RAW, estimar a partir de movimentações (bin_movements)
        if (($recTotal === 0 && $shipTotal === 0) && empty($recSeries) && empty($shipSeries)) {
            $whereRecMv = [];
            $paramsRecMv = [];
            $whereShipMv = [];
            $paramsShipMv = [];
            // Período
            if ($de) { $whereRecMv[] = 'DATE(bm.created_at) >= :de'; $paramsRecMv[':de'] = $de; $whereShipMv[] = 'DATE(bm.created_at) >= :de'; $paramsShipMv[':de'] = $de; }
            if ($ate) { $whereRecMv[] = 'DATE(bm.created_at) <= :ate'; $paramsRecMv[':ate'] = $ate; $whereShipMv[] = 'DATE(bm.created_at) <= :ate'; $paramsShipMv[':ate'] = $ate; }
            // Categoria via join em products
            if ($categoria) { $whereRecMv[] = 'p.codigo = :pc'; $paramsRecMv[':pc'] = (string)$categoria; $whereShipMv[] = 'p.codigo = :pc'; $paramsShipMv[':pc'] = (string)$categoria; }
            // Direções dos movimentos
            $whereRecMv[] = 'b_to.code = "RECV_DOCK"';
            $whereShipMv[] = 'b_from.code = "SHIP_DOCK"';
            $wRecSql = 'WHERE ' . implode(' AND ', $whereRecMv ?: ['DATE(bm.created_at)=CURDATE()']);
            $wShipSql = 'WHERE ' . implode(' AND ', $whereShipMv ?: ['DATE(bm.created_at)=CURDATE()']);

            // Totais via movimentos
            $sqlRecMvTotal = "SELECT COALESCE(SUM(bm.quantity),0) FROM bin_movements bm LEFT JOIN bins b_to ON b_to.id = bm.to_bin_id LEFT JOIN products p ON p.id = bm.product_id $wRecSql";
            $sqlShipMvTotal = "SELECT COALESCE(SUM(bm.quantity),0) FROM bin_movements bm LEFT JOIN bins b_from ON b_from.id = bm.from_bin_id LEFT JOIN products p ON p.id = bm.product_id $wShipSql";
            $stRecMv = $pdo->prepare($sqlRecMvTotal); $stRecMv->execute($paramsRecMv); $recMvTotal = (int)($stRecMv->fetchColumn() ?? 0);
            $stShipMv = $pdo->prepare($sqlShipMvTotal); $stShipMv->execute($paramsShipMv); $shipMvTotal = (int)($stShipMv->fetchColumn() ?? 0);

            // Série últimos 7 dias via movimentos
            $rangeRecMv = $de || $ate || $categoria ? $wRecSql : 'WHERE bm.created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND b_to.code = "RECV_DOCK"';
            $rangeShipMv = $de || $ate || $categoria ? $wShipSql : 'WHERE bm.created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND b_from.code = "SHIP_DOCK"';
            $sqlRecMvDays = "SELECT DATE(bm.created_at) d, SUM(bm.quantity) q FROM bin_movements bm LEFT JOIN bins b_to ON b_to.id = bm.to_bin_id LEFT JOIN products p ON p.id = bm.product_id $rangeRecMv GROUP BY d ORDER BY d DESC";
            $sqlShipMvDays = "SELECT DATE(bm.created_at) d, SUM(bm.quantity) q FROM bin_movements bm LEFT JOIN bins b_from ON b_from.id = bm.from_bin_id LEFT JOIN products p ON p.id = bm.product_id $rangeShipMv GROUP BY d ORDER BY d DESC";
            $stRecDaysMv = $pdo->prepare($sqlRecMvDays); $stRecDaysMv->execute($paramsRecMv); $recDaysMv = $stRecDaysMv->fetchAll() ?: [];
            $stShipDaysMv = $pdo->prepare($sqlShipMvDays); $stShipDaysMv->execute($paramsShipMv); $shipDaysMv = $stShipDaysMv->fetchAll() ?: [];

            // Aplicar fallback se houver dados
            if ($recMvTotal > 0 || $shipMvTotal > 0 || !empty($recDaysMv) || !empty($shipDaysMv)) {
                $recTotal = $recMvTotal; $shipTotal = $shipMvTotal;
                $recSeries = array_map(fn($r)=> ['dia'=> self::fmtDay($r['d']), 'quantidade'=> self::toInt($r['q'])], $recDaysMv);
                $shipSeries = array_map(fn($r)=> ['dia'=> self::fmtDay($r['d']), 'quantidade'=> self::toInt($r['q'])], $shipDaysMv);
            }
        }

        // Últimos eventos (filtráveis por tipo)
        $evFilters = [];
        $evParams = [];
        if ($tipo && in_array($tipo, ['recebimento','expedicao','inventory','shipping','receiving'], true)) {
            // aceita aliases
            $map = [ 'recebimento' => 'receiving', 'expedicao' => 'shipping' ];
            $t = $map[$tipo] ?? $tipo;
            $evFilters[] = 'type = :t'; $evParams[':t'] = $t;
        }
        $evWhere = $evFilters ? ('WHERE ' . implode(' AND ', $evFilters)) : '';
        $evStmt = $pdo->prepare("SELECT type, payload_json, created_at FROM wms_mobile_events $evWhere ORDER BY id DESC LIMIT 10");
        $evStmt->execute($evParams);
        $events = [];
        foreach ($evStmt->fetchAll() ?: [] as $ev) {
            $payload = json_decode((string)($ev['payload_json'] ?? '{}'), true) ?: [];
            $events[] = [
                'tipo' => (string)($ev['type'] ?? ''),
                'payload' => $payload,
                'quando' => self::fmtDateTime((string)($ev['created_at'] ?? date('c')))
            ];
        }

        // Resumo do coletor (últimas 24h)
        $colStmt = $pdo->query("SELECT type, COUNT(*) c FROM wms_mobile_events WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY type");
        $colRows = $colStmt?->fetchAll() ?: [];
        $colTotal = array_sum(array_map(fn($r)=> (int)$r['c'], $colRows));
        $colPorTipo = array_map(function($r) use ($colTotal){
            $c = (int)$r['c'];
            $pct = $colTotal > 0 ? round(($c / $colTotal) * 100) : 0;
            return [ 'tipo' => (string)$r['type'], 'count' => $c, 'percentual' => (int)$pct ];
        }, $colRows);

        // Metas simples para progresso (pode vir de configuração futura)
        $metas = [ 'recebimento_meta' => 100, 'expedicao_meta_ratio' => 1.0 ];

        return Response::json([
            'gerado_em' => self::fmtDateTime(date('c')),
            'totais_hoje' => [ 'recebimento' => self::toInt($recTotal), 'expedicao' => self::toInt($shipTotal) ],
            'serie_7dias' => [ 'recebimento' => $recSeries, 'expedicao' => $shipSeries ],
            'ultimos_eventos' => $events,
            'coletor_resumo' => [ 'total' => (int)$colTotal, 'por_tipo' => $colPorTipo ],
            'metas' => $metas,
            'filtros_aplicados' => [ 'de' => $deBr, 'ate' => $ateBr, 'categoria' => $categoria, 'tipo' => $tipo ]
        ]);
    }

    /**
     * Alertas automáticos baseado em regras simples
     */
    public function alerts(Request $req): Response {
        $pdo = self::pdo();
        $recToday = (int)($pdo->query("SELECT COALESCE(SUM(quantity),0) FROM wms_receiving_raw WHERE DATE(created_at)=CURDATE()")?->fetchColumn() ?? 0);
        $shipToday = (int)($pdo->query("SELECT COALESCE(SUM(quantity),0) FROM wms_shipping_raw WHERE DATE(created_at)=CURDATE()")?->fetchColumn() ?? 0);
        $alerts = [];

        // Regra: pico de recebimento
        $limiteRec = 100; // pode ser movido para config
        if ($recToday > $limiteRec) {
            $alerts[] = [
                'nivel' => 'alto',
                'codigo' => 'recebimento_pico',
                'mensagem' => 'Volume de recebimento acima do esperado hoje',
                'valor' => self::toInt($recToday),
                'limite' => $limiteRec
            ];
        }

        // Regra: expedição baixa comparada ao recebimento
        if ($recToday > 0) {
            $ratio = ($shipToday / max(1, $recToday));
            if ($ratio < 0.5) {
                $alerts[] = [
                    'nivel' => 'medio',
                    'codigo' => 'expedicao_baixa',
                    'mensagem' => 'Expedição abaixo de 50% do recebimento hoje',
                    'recebimento' => self::toInt($recToday),
                    'expedicao' => self::toInt($shipToday)
                ];
            }
        }

        return Response::json([
            'gerado_em' => self::fmtDateTime(date('c')),
            'alertas' => $alerts
        ]);
    }

    /** CSV: Recebimento RAW */
    public function exportReceivingCsv(Request $req): Response {
        $pdo = self::pdo();
        $stmt = $pdo->query("SELECT id, product_code, quantity, origin, status, created_at FROM wms_receiving_raw ORDER BY id DESC LIMIT 1000");
        $rows = $stmt?->fetchAll() ?: [];
        $csv = "id;produto;quantidade;origem;status;quando\n";
        foreach ($rows as $r) {
            $csv .= implode(';', [
                (string)$r['id'],
                (string)$r['product_code'],
                (string)self::toInt($r['quantity']),
                (string)($r['origin'] ?? ''),
                (string)$r['status'],
                self::fmtDateTime((string)$r['created_at'])
            ]) . "\n";
        }
        return new Response(200, $csv, ['Content-Type' => 'text/csv', 'Content-Disposition' => 'attachment; filename="recebimento_raw.csv"']);
    }

    /** CSV: Expedição RAW */
    public function exportShippingCsv(Request $req): Response {
        $pdo = self::pdo();
        $stmt = $pdo->query("SELECT id, product_code, quantity, dest, status, created_at FROM wms_shipping_raw ORDER BY id DESC LIMIT 1000");
        $rows = $stmt?->fetchAll() ?: [];
        $csv = "id;produto;quantidade;destino;status;quando\n";
        foreach ($rows as $r) {
            $csv .= implode(';', [
                (string)$r['id'],
                (string)$r['product_code'],
                (string)self::toInt($r['quantity']),
                (string)($r['dest'] ?? ''),
                (string)$r['status'],
                self::fmtDateTime((string)$r['created_at'])
            ]) . "\n";
        }
        return new Response(200, $csv, ['Content-Type' => 'text/csv', 'Content-Disposition' => 'attachment; filename="expedicao_raw.csv"']);
    }
}