<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

class DatabasePerformanceCheck extends Command
{
    protected $signature = 'database:performance-check {--json : Emit machine-readable JSON} {--explain : Include representative query plans}';

    protected $description = 'Verify reporting indexes and optionally inspect representative dashboard/report query plans';

    private const EXPECTED_INDEXES = [
        'financial_account_transactions' => [
            ['branch_id', 'type', 'transaction_date'],
        ],
        'accounting_entries' => [
            ['branch_id', 'category', 'entry_date'],
        ],
        'loans' => [
            ['branch_id', 'status'],
            ['branch_id', 'status', 'completed_at'],
            ['branch_id', 'status', 'written_off_at'],
            ['branch_id', 'status', 'total_overdue'],
        ],
        'customers' => [
            ['branch_id', 'status'],
            ['branch_id', 'created_at'],
        ],
        'customer_risk_profiles' => [
            ['score_band', 'calculated_at'],
            ['score_version', 'calculated_at'],
        ],
        'customer_credit_score_histories' => [
            ['customer_id', 'calculated_at'],
            ['branch_id', 'calculated_at'],
            ['score_band', 'calculated_at'],
            ['score_version', 'calculated_at'],
        ],
        'management_alerts' => [
            ['branch_id', 'status', 'occurred_at'],
        ],
        'audit_logs' => [
            ['branch_id', 'created_at'],
        ],
    ];

    public function handle(): int
    {
        $checks = [];
        $errors = [];

        foreach (self::EXPECTED_INDEXES as $table => $expectedIndexes) {
            try {
                if (! Schema::hasTable($table)) {
                    $checks[$table] = [
                        'status' => 'fail',
                        'message' => 'Table is missing.',
                    ];
                    $errors[] = $table.' table is missing.';
                    continue;
                }

                $actualIndexes = collect(Schema::getIndexes($table))
                    ->map(fn (array $index) => array_values($index['columns'] ?? []))
                    ->filter()
                    ->values();

                $missing = collect($expectedIndexes)
                    ->reject(fn (array $expected) => $actualIndexes->contains(fn (array $actual) => $actual === $expected))
                    ->values()
                    ->all();

                $checks[$table] = [
                    'status' => $missing === [] ? 'pass' : 'fail',
                    'expected' => $expectedIndexes,
                    'missing' => $missing,
                ];

                foreach ($missing as $columns) {
                    $errors[] = $table.' is missing composite index ['.implode(', ', $columns).']';
                }
            } catch (Throwable $exception) {
                $checks[$table] = [
                    'status' => 'fail',
                    'message' => $exception->getMessage(),
                ];
                $errors[] = 'Could not inspect indexes for '.$table.'.';
            }
        }

        $plans = null;
        if ($this->option('explain')) {
            $plans = $this->representativePlans();
        }

        $report = [
            'schema_version' => 1,
            'status' => $errors === [] ? 'healthy' : 'unhealthy',
            'connection' => DB::connection()->getDriverName(),
            'checked_at' => now()->toIso8601String(),
            'slow_query_threshold_ms' => (int) config('database_performance.slow_query_ms', 0),
            'checks' => $checks,
            'plans' => $plans,
            'errors' => $errors,
        ];

        if ($this->option('json')) {
            $this->line((string) json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
        } else {
            $this->info('Database performance preflight');
            foreach ($checks as $table => $check) {
                $method = $check['status'] === 'pass' ? 'info' : 'error';
                $this->{$method}(strtoupper($check['status']).': '.$table);
            }

            if ($plans !== null) {
                $this->newLine();
                $this->line('Representative query plans collected for '.$report['connection'].'.');
            }

            if ($errors === []) {
                $this->newLine();
                $this->info('Database performance preflight passed.');
            } else {
                $this->newLine();
                foreach ($errors as $error) {
                    $this->error('FAIL: '.$error);
                }
            }
        }

        return $errors === [] ? self::SUCCESS : self::FAILURE;
    }

    private function representativePlans(): array
    {
        if (DB::connection()->getDriverName() !== 'mysql') {
            return [
                'status' => 'skipped',
                'reason' => 'EXPLAIN plan capture is defined for the supported MySQL production deployment.',
            ];
        }

        $from = now()->subDays(30)->startOfDay()->toDateTimeString();
        $to = now()->endOfDay()->toDateTimeString();

        $queries = [
            'transaction_flow' => [
                'sql' => 'SELECT id FROM financial_account_transactions WHERE branch_id = ? AND type IN (?, ?) AND transaction_date BETWEEN ? AND ? ORDER BY transaction_date LIMIT 100',
                'bindings' => [1, 'loan_payment', 'loan_disbursement', $from, $to],
            ],
            'accounting_series' => [
                'sql' => 'SELECT id FROM accounting_entries WHERE branch_id = ? AND category IN (?, ?) AND entry_date BETWEEN ? AND ? ORDER BY entry_date LIMIT 100',
                'bindings' => [1, 'interest', 'expenses', $from, $to],
            ],
            'active_overdue_loans' => [
                'sql' => 'SELECT id FROM loans WHERE branch_id = ? AND status = ? AND total_overdue > ? ORDER BY id LIMIT 100',
                'bindings' => [1, 'active', 0],
            ],
            'completed_loans' => [
                'sql' => 'SELECT id FROM loans WHERE branch_id = ? AND status IN (?, ?) AND completed_at BETWEEN ? AND ? ORDER BY completed_at DESC LIMIT 100',
                'bindings' => [1, 'completed', 'fully_paid', $from, $to],
            ],
            'new_customers' => [
                'sql' => 'SELECT id FROM customers WHERE branch_id = ? AND created_at BETWEEN ? AND ? ORDER BY created_at DESC LIMIT 100',
                'bindings' => [1, $from, $to],
            ],
            'credit_score_band_history' => [
                'sql' => 'SELECT id FROM customer_credit_score_histories WHERE branch_id = ? AND score_band = ? AND calculated_at BETWEEN ? AND ? ORDER BY calculated_at DESC LIMIT 100',
                'bindings' => [1, 'good', $from, $to],
            ],
        ];

        $plans = [];
        foreach ($queries as $name => $query) {
            try {
                $rows = DB::select('EXPLAIN '.$query['sql'], $query['bindings']);
                $plans[$name] = array_map(fn (object $row) => [
                    'table' => $row->table ?? null,
                    'type' => $row->type ?? null,
                    'possible_keys' => $row->possible_keys ?? null,
                    'key' => $row->key ?? null,
                    'rows' => isset($row->rows) ? (int) $row->rows : null,
                    'extra' => $row->Extra ?? null,
                ], $rows);
            } catch (Throwable $exception) {
                $plans[$name] = [
                    'error' => $exception->getMessage(),
                ];
            }
        }

        return [
            'status' => 'collected',
            'queries' => $plans,
        ];
    }
}
