<?php
namespace App\Http\Controllers\Api\V1;

use App\Http\Controllers\Controller;
use App\Models\AuditLog;
use App\Models\BankAccount;
use App\Models\Customer;
use App\Models\Supplier;
use App\Models\User;
use App\Models\MaintenanceWorkOrder;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/** Live data for the Command Centre — Reports & Analytics module (Group scope: meta view over all domains). */
class ReportsController extends Controller
{
    use Concerns\DashboardHelpers;

    /** [label, table, created-date-col|null] for the data-volume registry. */
    private const VOLUMES = [
        ['Customers', 'customers', 'created_at'],
        ['Suppliers', 'suppliers', 'created_at'],
        ['Sales orders', 'sales_orders', 'created_at'],
        ['Sales invoices', 'sales_invoices', 'created_at'],
        ['Purchase orders', 'purchase_orders', 'created_at'],
        ['Purchase invoices', 'purchase_invoices', 'created_at'],
        ['Quotations', 'quotations', 'created_at'],
        ['Weighbridge tickets', 'weighbridge_tickets', 'created_at'],
        ['Stock movements', 'stock_movements', 'created_at'],
        ['Journal entries', 'journals', 'created_at'],
        ['Employees', 'employees', 'created_at'],
        ['Work orders', 'work_orders', 'created_at'],
        ['Site diaries', 'site_diaries', 'created_at'],
        ['HSE incidents', 'incidents', 'created_at'],
    ];

    public function show(Request $r)
    {
        $this->need('reports.view');
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));

        $vols = $this->volumes();
        $records = array_sum(array_column($vols, 'total'));
        $addedP = array_sum(array_column($vols, 'added'));

        $ev = AuditLog::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])->count();
        $evP = AuditLog::whereBetween('created_at', [$pf . ' 00:00:00', $pt . ' 23:59:59'])->count();
        $crt = AuditLog::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])->where('action', 'created')->count();
        $crtP = AuditLog::whereBetween('created_at', [$pf . ' 00:00:00', $pt . ' 23:59:59'])->where('action', 'created')->count();
        $upd = $ev - $crt;
        $updP = $evP - $crtP;
        $users = AuditLog::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])->distinct('user_id')->count('user_id');
        $usersP = AuditLog::whereBetween('created_at', [$pf . ' 00:00:00', $pt . ' 23:59:59'])->distinct('user_id')->count('user_id');

        $debtors = round((float) Customer::where('current_balance', '>', 0)->sum('current_balance'));
        $creditors = round((float) Supplier::where('current_balance', '>', 0)->sum('current_balance'));
        $cash = round((float) BankAccount::sum('current_balance'));
        $openWo = MaintenanceWorkOrder::whereNotIn('status', ['completed', 'closed', 'cancelled'])->count();

        $cards = [
            $this->card('Live Modules', 27, '#', 27, [], ['st' => 'blue']),
            $this->card('Records Managed', $records, '#', $records, [], ['st' => 'blue']),
            $this->card('Activity Events', $ev, '#', $evP),
            $this->card('Records Created', $crt, '#', $crtP),
            $this->card('Records Updated', $upd, '#', $updP),
            $this->card('Active Users', $users, '#', $usersP),
            $this->card('Reports in Catalogue', count($this->catalogue()), '#', count($this->catalogue()), [], ['st' => 'blue']),
            $this->card('Added This Period', $addedP, '#', $addedP, [], ['st' => 'blue']),
            $this->card('Group Debtors', $debtors, 'GHS', $debtors, [], ['good' => 'down']),
            $this->card('Group Creditors', $creditors, 'GHS', $creditors, [], ['good' => 'down']),
            $this->card('Cash at Bank', $cash, 'GHS', $cash, [], ['good' => 'up']),
            $this->card('Open Work Orders', $openWo, '#', $openWo, [], ['good' => 'down']),
        ];

        return response()->json([
            'sub' => 'Catalogue · system activity · data volumes · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secAct(),
                $this->secMod($cf, $ct),
                $this->secUsers($cf, $ct),
                $this->secCov(),
                $this->secCat(),
                $this->secVol($vols),
                $this->secAudit(),
            ],
        ]);
    }

    private function volumes(): array
    {
        [$cf, $ct] = [$this->windows('This Month')[0], $this->windows('This Month')[1]];
        $out = [];
        foreach (self::VOLUMES as [$label, $table, $dc]) {
            if (!Schema::hasTable($table)) continue;
            $total = DB::table($table)->count();
            $added = ($dc && Schema::hasColumn($table, $dc))
                ? DB::table($table)->whereBetween($dc, [$cf . ' 00:00:00', $ct . ' 23:59:59'])->count() : 0;
            $last = ($dc && Schema::hasColumn($table, $dc)) ? DB::table($table)->max($dc) : null;
            $out[] = ['label' => $label, 'total' => $total, 'added' => $added, 'last' => $last ? substr($last, 0, 10) : '—'];
        }
        return $out;
    }

    private function catalogue(): array
    {
        return [
            ['Executive Flash — Group P&L, cash & debtors', 'Executive', 'Daily', 'Screen'],
            ['Quarry Production & Yield Report', 'Quarry', 'Daily', 'Screen'],
            ['Drill & Blast Performance', 'Drilling', 'Per blast', 'Screen'],
            ['Crusher Performance & Gradation', 'Crushing', 'Daily', 'Screen'],
            ['Weighbridge Reconciliation', 'Weighbridge', 'Daily', 'Screen + CSV'],
            ['Stockpile Survey vs Book', 'Stockpiles', 'Monthly', 'Screen'],
            ['Sales Daybook & Customer Statements', 'Sales', 'On demand', 'PDF'],
            ['Equipment Availability & Cost', 'Equipment', 'Weekly', 'Screen'],
            ['Project Cost & Progress (S-curves)', 'Projects', 'Weekly', 'Screen'],
            ['Contract & BOQ Status', 'Contracts', 'Monthly', 'PDF'],
            ['Procurement Pipeline & Open POs', 'Procurement', 'Weekly', 'Screen'],
            ['Inventory Valuation & Slow Stock', 'Inventory', 'Monthly', 'Screen + CSV'],
            ['Trial Balance, P&L & Balance Sheet', 'Finance', 'Monthly', 'PDF'],
            ['Payroll Journal & Deduction Schedule', 'HR & Payroll', 'Monthly', 'PDF'],
            ['HSE Incident Register & LTIFR', 'HSE', 'Monthly', 'PDF'],
            ['EPA Compliance & Monitoring', 'Environment', 'Quarterly', 'PDF'],
            ['Cash Position & 13-Week Forecast', 'Treasury', 'Daily', 'Screen'],
            ['VAT Return (GRA e-VAT schedule)', 'Tax', 'Monthly', 'CSV'],
            ['PAYE / SSNIT / WHT Schedules', 'Tax', 'Monthly', 'CSV'],
            ['Budget Variance Report', 'Budgets', 'Monthly', 'PDF'],
            ['Fleet Tonnage & Cost per Tonne', 'Fleet', 'Weekly', 'Screen'],
            ['Fuel Reconciliation & Burn Rates', 'Fuel', 'Weekly', 'Screen'],
            ['IPC & Retention Statement', 'IPC', 'Per cert', 'PDF'],
            ['Debtor Ageing & Collection', 'CRM', 'Weekly', 'PDF'],
            ['Creditor Ageing & Remittance', 'Suppliers', 'Weekly', 'PDF'],
        ];
    }

    private function secAct(): array
    {
        $t = Carbon::today();
        $labels = [];
        $crt = [];
        $upd = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = $t->copy()->subDays($i)->format('Y-m-d');
            $labels[] = substr($d, 5);
            $crt[] = AuditLog::whereDate('created_at', $d)->where('action', 'created')->count();
            $upd[] = AuditLog::whereDate('created_at', $d)->where('action', 'updated')->count();
        }
        return ['k' => 'chart', 'id' => 'rp_act', 'ti' => 'System Activity — audit events per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'num',
            'labels' => $labels,
            'series' => [
                ['name' => 'Created', 'data' => $crt, 'color' => '#22c55e'],
                ['name' => 'Updated', 'data' => $upd, 'color' => '#3b82f6'],
            ],
        ]];
    }

    private function pretty($type): string
    {
        $b = preg_replace('/^App\\\\Models\\\\/', '', (string) $type);
        return trim(preg_replace('/(?<!^)([A-Z])/', ' $1', $b));
    }

    private function secMod($cf, $ct): array
    {
        $rows = AuditLog::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])
            ->select('auditable_type', DB::raw('count(*) n'))->groupBy('auditable_type')->orderByDesc('n')->take(10)->get();
        return ['k' => 'chart', 'id' => 'rp_mod', 'ti' => 'Activity by Entity — events (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth($this->pretty($r->auditable_type), 0, 24, '…'))->all(),
            'series' => [['name' => 'Events', 'data' => $rows->map(fn($r) => (int) $r->n)->all(), 'color' => '#8b5cf6']],
        ]];
    }

    private function secUsers($cf, $ct): array
    {
        $rows = AuditLog::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])->whereNotNull('user_id')
            ->select('user_id', DB::raw('count(*) n'))->groupBy('user_id')->orderByDesc('n')->take(8)->get();
        $names = User::whereIn('id', $rows->pluck('user_id'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'rp_users', 'ti' => 'Activity by User — events (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->user_id] ?? ('#' . $r->user_id), 0, 24, '…'))->all(),
            'series' => [['name' => 'Events', 'data' => $rows->map(fn($r) => (int) $r->n)->all(), 'color' => '#06b6d4']],
        ]];
    }

    private function secCov(): array
    {
        $pal = $this->palette();
        return ['k' => 'chart', 'id' => 'rp_cov', 'ti' => 'ERP Coverage — live vs queued modules', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'num', 'center' => '31', 'items' => [
                ['label' => 'Live', 'value' => 27, 'color' => $pal[1]],
                ['label' => 'Queued', 'value' => 4, 'color' => $pal[4]],
            ],
        ]];
    }

    private function secCat(): array
    {
        $rows = array_map(fn($c, $i) => ['no' => $i + 1, 'name' => $c[0], 'area' => $c[1], 'cad' => $c[2], 'fmt' => $c[3],
            'st' => 'green', 'stL' => 'Live'], $this->catalogue(), array_keys($this->catalogue()));
        return ['k' => 'table', 'id' => 'rp_cat', 'ti' => 'Report Catalogue — standard reports & statutory returns', 'cls' => 'wide', 'cfg' => [
            'id' => 'rp_cat', 'page' => 13, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => '#'], ['k' => 'name', 'l' => 'Report', 'wide' => 1], ['k' => 'area', 'l' => 'Area'],
                ['k' => 'cad', 'l' => 'Cadence'], ['k' => 'fmt', 'l' => 'Format'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows,
        ]];
    }

    private function secVol($vols): array
    {
        $rows = array_map(fn($v) => ['ent' => $v['label'], 'total' => $v['total'], 'added' => $v['added'], 'last' => $v['last'],
            'st' => $v['total'] > 0 ? 'green' : 'amber', 'stL' => $v['total'] > 0 ? 'Live' : 'Empty'], $vols);
        return ['k' => 'table', 'id' => 'rp_vol', 'ti' => 'Data Volumes — rows under management per register', 'cls' => 'wide', 'cfg' => [
            'id' => 'rp_vol', 'page' => 14, 'search' => true,
            'cols' => [
                ['k' => 'ent', 'l' => 'Register', 'wide' => 1], ['k' => 'total', 'l' => 'Rows', 'y' => 'num'],
                ['k' => 'added', 'l' => 'Added (MTD)', 'y' => 'num'], ['k' => 'last', 'l' => 'Last entry'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['total', 'added'],
        ]];
    }

    private function secAudit(): array
    {
        $rows = AuditLog::orderByDesc('id')->take(30)->get()->map(function ($a) {
            $u = $a->user_id ? User::find($a->user_id) : null;
            return ['when' => substr((string) $a->created_at, 0, 16), 'user' => $u->name ?? ('#' . $a->user_id),
                'act' => ucfirst((string) $a->action), 'ent' => $this->pretty($a->auditable_type),
                'ref' => '#' . $a->auditable_id, 'ip' => $a->ip ?? '',
                'st' => $a->action === 'created' ? 'green' : 'blue', 'stL' => ucfirst((string) $a->action)];
        })->all();
        return ['k' => 'table', 'id' => 'rp_audit', 'ti' => 'Audit Trail — latest 30 system events', 'cls' => 'wide', 'cfg' => [
            'id' => 'rp_audit', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'when', 'l' => 'When'], ['k' => 'user', 'l' => 'User'], ['k' => 'act', 'l' => 'Action'],
                ['k' => 'ent', 'l' => 'Entity', 'wide' => 1], ['k' => 'ref', 'l' => 'Ref'], ['k' => 'ip', 'l' => 'IP'],
                ['k' => 'st', 'l' => 'Type', 'y' => 'badge'],
            ], 'rows' => $rows,
        ]];
    }
}
