<?php

namespace App\Services\Charts;

use Carbon\Carbon;
use DB;
use Illuminate\Support\Facades\Log;

final class DashboardChartService
{
    /**
     * Build all dashboard charts in a Chart.js-friendly shape.
     *
     * Every chart method is defensive because the legacy WMS/M3 data source is
     * an ODBC connection and some environments do not have IBM i available.
     * The dashboard still renders cleanly with zeroed data instead of failing.
     *
     * @return array<string, mixed>
     */
    public function dashboard(): array
    {
        return [
            'metrics' => $this->metrics(),
            'charts' => [
                'pickingLastSevenDays' => $this->pickingLastSevenDays(),
                'dispatchesLastSevenDays' => $this->dispatchesLastSevenDays(),
                'stockMovementsByType' => $this->stockMovementsByType(),
                'lowStockByWarehouse' => $this->lowStockByWarehouse(),
                'warehouseUtilisation' => $this->warehouseUtilisation(),
                'systemHealth' => $this->systemHealth(),
            ],
        ];
    }

    /**
     * @return array<int, array{label: string, value: int|string, hint: string, icon: string}>
     */
    private function metrics(): array
    {
        return [
            [
                'label' => 'Open pick waves',
                'value' => $this->safeCount('ICM688M1', "A4PICKSTS IN ('B', 'P')"),
                'hint' => 'Active or in-progress pick work',
                'icon' => 'fa-box-open',
            ],
            [
                'label' => 'Pending manifests',
                'value' => $this->safeCount('ICM705M1', "APSPARE2 IN ('', 'P')"),
                'hint' => 'Waiting for close or confirmation',
                'icon' => 'fa-truck-loading',
            ],
            [
                'label' => 'M3 errors',
                'value' => $this->safeCount('ICM699M', "SHERROR = 'X'"),
                'hint' => 'Messages needing attention',
                'icon' => 'fa-exclamation-triangle',
            ],
            [
                'label' => 'Warehouses',
                'value' => $this->safeCount('IC670M'),
                'hint' => 'Known warehouse records',
                'icon' => 'fa-warehouse',
            ],
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function pickingLastSevenDays(): array
    {
        $labels = $this->lastDaysLabels(7);
        $data = $this->emptySeries($labels);

        try {
            $rows = DB::connection('odbc')->select(
                'SELECT DATE(A5PICKCMTS) AS PICK_DATE, COUNT(*) AS TOTAL
                 FROM EWDAT.ICM688M2
                 WHERE A5PICKCMTS >= CURRENT DATE - 7 DAYS
                 GROUP BY DATE(A5PICKCMTS)
                 ORDER BY PICK_DATE',
            );

            foreach ($rows as $row) {
                $key = Carbon::parse($row->PICK_DATE)->format('d M');
                if (array_key_exists($key, $data)) {
                    $data[$key] = (int) $row->TOTAL;
                }
            }
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->lineChart($labels, array_values($data), 'Pick lines completed');
    }

    /**
     * @return array<string, mixed>
     */
    private function dispatchesLastSevenDays(): array
    {
        $labels = $this->lastDaysLabels(7);
        $data = $this->emptySeries($labels);

        try {
            $rows = DB::connection('odbc')->select(
                "SELECT SHDT AS SHIP_DATE, COUNT(DISTINCT SHMACPKR) AS TOTAL
                 FROM EWDAT.ICM699M
                 WHERE SHTYPE = 'M3-Res-out'
                   AND SHSOURCE = 'CloseShipmentCoChargeAPI'
                   AND SHDATA LIKE 'Success%'
                   AND SHDT >= VARCHAR_FORMAT(CURRENT DATE - 7 DAYS, 'YYYYMMDD')
                 GROUP BY SHDT
                 ORDER BY SHDT",
            );

            foreach ($rows as $row) {
                $key = Carbon::createFromFormat('Ymd', (string) $row->SHIP_DATE)->format('d M');
                if (array_key_exists($key, $data)) {
                    $data[$key] = (int) $row->TOTAL;
                }
            }
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->barChart($labels, array_values($data), 'Dispatches closed');
    }

    /**
     * @return array<string, mixed>
     */
    private function stockMovementsByType(): array
    {
        $labels = ['Transfer', 'Replenishment', 'Packing', 'Shipment', 'Adjustment'];
        $data = [0, 0, 0, 0, 0];

        try {
            $rows = DB::connection('odbc')->select(
                "SELECT TRIM(SHSOURCE) AS SOURCE_NAME, COUNT(*) AS TOTAL
                 FROM EWDAT.ICM699M
                 WHERE SHDT >= VARCHAR_FORMAT(CURRENT DATE - 30 DAYS, 'YYYYMMDD')
                 GROUP BY TRIM(SHSOURCE)
                 ORDER BY TOTAL DESC
                 FETCH FIRST 5 ROWS ONLY",
            );

            if (! empty($rows)) {
                $labels = [];
                $data = [];
                foreach ($rows as $row) {
                    $labels[] = ucwords(strtolower(str_replace(['API', '_', '-'], [' API', ' ', ' '], trim($row->SOURCE_NAME))));
                    $data[] = (int) $row->TOTAL;
                }
            }
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->doughnutChart($labels, $data, 'Movement messages');
    }

    /**
     * @return array<string, mixed>
     */
    private function lowStockByWarehouse(): array
    {
        $labels = ['WH 1', 'WH 2', 'WH 3', 'WH 4', 'WH 5'];
        $data = [0, 0, 0, 0, 0];

        try {
            $rows = DB::connection('odbc')->select(
                'SELECT TRIM(AT9WHS) AS WAREHOUSE, COUNT(*) AS TOTAL
                 FROM EWDAT.ICM709M
                 WHERE AT9QTY <= AT9WRN
                 GROUP BY TRIM(AT9WHS)
                 ORDER BY TOTAL DESC
                 FETCH FIRST 6 ROWS ONLY',
            );

            if (! empty($rows)) {
                $labels = [];
                $data = [];
                foreach ($rows as $row) {
                    $labels[] = 'WH ' . trim($row->WAREHOUSE);
                    $data[] = (int) $row->TOTAL;
                }
            }
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->barChart($labels, $data, 'Low-stock lines');
    }

    /**
     * @return array<string, mixed>
     */
    private function warehouseUtilisation(): array
    {
        $labels = ['Available', 'Allocated', 'Packed'];
        $data = [0, 0, 0];

        try {
            $rows = DB::connection('odbc')->select(
                'SELECT
                    SUM(A5RSVQTY) AS RESERVED_QTY,
                    SUM(A5PACKED) AS PACKED_QTY,
                    SUM(A5RSVQTY - A5PACKED) AS REMAINING_QTY
                 FROM EWDAT.ICM688M2
                 WHERE A5RSVQTY > 0',
            );

            if (! empty($rows)) {
                $data = [
                    max(0, (int) $rows[0]->REMAINING_QTY),
                    max(0, (int) $rows[0]->RESERVED_QTY),
                    max(0, (int) $rows[0]->PACKED_QTY),
                ];
            }
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->doughnutChart($labels, $data, 'Quantity status');
    }

    /**
     * @return array<string, mixed>
     */
    private function systemHealth(): array
    {
        $labels = ['Success', 'Errors', 'Pending'];
        $data = [0, 0, 0];

        try {
            $success = $this->safeCount('ICM699M', "SHTYPE = 'M3-Res-out' AND SHDATA LIKE 'Success%'");
            $errors = $this->safeCount('ICM699M', "SHERROR = 'X'");
            $pending = $this->safeCount('ICM705M1', "APSPARE2 IN ('', 'P')");
            $data = [$success, $errors, $pending];
        } catch (\Throwable $exception) {
            $this->logChartException($exception);
        }

        return $this->doughnutChart($labels, $data, 'Operational status');
    }

    /**
     * @return array<int, string>
     */
    private function lastDaysLabels(int $days): array
    {
        $labels = [];
        for ($offset = $days - 1; $offset >= 0; $offset--) {
            $labels[] = Carbon::today()->subDays($offset)->format('d M');
        }

        return $labels;
    }

    /**
     * @param array<int, string> $labels
     *
     * @return array<string, int>
     */
    private function emptySeries(array $labels): array
    {
        return array_fill_keys($labels, 0);
    }

    private function safeCount(string $table, ?string $where = null): int
    {
        try {
            $sql = 'SELECT COUNT(*) AS TOTAL FROM EWDAT.' . $table;
            if ($where !== null && trim($where) !== '') {
                $sql .= ' WHERE ' . $where;
            }

            $rows = DB::connection('odbc')->select($sql);

            return isset($rows[0]->TOTAL) ? (int) $rows[0]->TOTAL : 0;
        } catch (\Throwable $exception) {
            $this->logChartException($exception);

            return 0;
        }
    }

    /**
     * @param array<int, string> $labels
     * @param array<int, int>    $data
     *
     * @return array<string, mixed>
     */
    private function lineChart(array $labels, array $data, string $label): array
    {
        return [
            'labels' => $labels,
            'datasets' => [[
                'label' => $label,
                'data' => $data,
                'tension' => 0.35,
                'fill' => true,
            ]],
        ];
    }

    /**
     * @param array<int, string> $labels
     * @param array<int, int>    $data
     *
     * @return array<string, mixed>
     */
    private function barChart(array $labels, array $data, string $label): array
    {
        return [
            'labels' => $labels,
            'datasets' => [[
                'label' => $label,
                'data' => $data,
                'borderRadius' => 8,
            ]],
        ];
    }

    /**
     * @param array<int, string> $labels
     * @param array<int, int>    $data
     *
     * @return array<string, mixed>
     */
    private function doughnutChart(array $labels, array $data, string $label): array
    {
        return [
            'labels' => $labels,
            'datasets' => [[
                'label' => $label,
                'data' => $data,
                'borderWidth' => 2,
                'hoverOffset' => 8,
            ]],
        ];
    }

    private function logChartException(\Throwable $exception): void
    {
        Log::debug('Dashboard chart data unavailable.', [
            'message' => $exception->getMessage(),
        ]);
    }
}