<?php

namespace App\Console\Commands;

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

class CreditScoreProductionCheck extends Command
{
    protected $signature = 'credit-score:production-check {--json : Emit machine-readable JSON}';

    protected $description = 'Verify credit-score configuration, schema, permissions, indexes and immutable-snapshot deployment prerequisites';

    private const REQUIRED_PERMISSIONS = [
        'credit-score.view',
        'credit-score.history',
        'credit-score.recalculate',
    ];

    private const REQUIRED_COLUMNS = [
        'customer_risk_profiles' => [
            'customer_id', 'risk_level', 'score', 'score_band', 'score_version', 'confidence',
            'thin_file', 'manual_review_required', 'last_trigger', 'factors', 'explanation', 'calculated_at',
        ],
        'customer_credit_score_histories' => [
            'id', 'customer_id', 'branch_id', 'score', 'score_band', 'risk_level', 'confidence', 'thin_file',
            'manual_review_required', 'score_version', 'trigger', 'component_scores', 'factors', 'explanation',
            'calculated_at', 'created_by', 'created_at', 'updated_at',
        ],
        'loans' => [
            'customer_id', 'branch_id', 'application_credit_score_history_id', 'status', 'total_outstanding',
            'total_overdue', 'days_overdue', 'approved_at', 'activated_at', 'completed_at', 'written_off_at',
        ],
    ];

    private const REQUIRED_INDEXES = [
        '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'],
        ],
    ];

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

        $this->checkConfiguration($checks, $errors);
        $this->checkSchema($checks, $errors);
        $this->checkIndexes($checks, $errors);
        $this->checkPermissions($checks, $errors);
        $this->checkSnapshotForeignKey($checks, $errors, $warnings);

        $warnings[] = 'Verify the host runs `php artisan schedule:run` every minute so the stale-score safety net can execute.';

        $report = [
            'schema_version' => 1,
            'status' => $errors === [] ? 'ready' : 'not_ready',
            'score_version' => (string) config('credit_score.version', ''),
            'connection' => DB::connection()->getDriverName(),
            'checked_at' => now()->toIso8601String(),
            'checks' => $checks,
            'warnings' => $warnings,
            'errors' => $errors,
        ];

        if ($this->option('json')) {
            $this->line((string) json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
        } else {
            $this->info('Credit-score production readiness');
            foreach ($checks as $name => $check) {
                $method = ($check['status'] ?? 'fail') === 'pass' ? 'info' : 'error';
                $this->{$method}(strtoupper((string) ($check['status'] ?? 'fail')).': '.$name);
            }

            foreach ($warnings as $warning) {
                $this->warn('WARN: '.$warning);
            }

            if ($errors === []) {
                $this->newLine();
                $this->info('Credit-score production gate passed.');
                $this->line('No score weights, thresholds or historical snapshots were changed by this check.');
            } else {
                $this->newLine();
                foreach ($errors as $error) {
                    $this->error('FAIL: '.$error);
                }
                $this->error('Credit-score deployment is NOT ready.');
            }
        }

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

    private function checkConfiguration(array &$checks, array &$errors): void
    {
        $weights = config('credit_score.weights', []);
        $minimum = (int) config('credit_score.score.minimum', 0);
        $maximum = (int) config('credit_score.score.maximum', 0);
        $valid = filled(config('credit_score.version'))
            && $minimum === 300
            && $maximum === 850
            && is_array($weights)
            && array_sum(array_map('intval', $weights)) === 100
            && (int) config('credit_score.operations.stale_after_hours', 0) > 0
            && (int) config('credit_score.operations.refresh_chunk_size', 0) > 0
            && (int) config('credit_score.monitoring.recent_window_days', 0) > 0
            && (int) config('credit_score.monitoring.minimum_sample_size', 0) > 0;

        $checks['configuration'] = ['status' => $valid ? 'pass' : 'fail'];
        if (! $valid) {
            $errors[] = 'Credit-score V1 configuration is incomplete or violates production invariants.';
        }
    }

    private function checkSchema(array &$checks, array &$errors): void
    {
        foreach (self::REQUIRED_COLUMNS as $table => $columns) {
            if (! Schema::hasTable($table)) {
                $checks['schema:'.$table] = ['status' => 'fail', 'missing_table' => true];
                $errors[] = $table.' table is missing.';
                continue;
            }

            $missing = array_values(array_filter($columns, fn (string $column) => ! Schema::hasColumn($table, $column)));
            $checks['schema:'.$table] = [
                'status' => $missing === [] ? 'pass' : 'fail',
                'missing_columns' => $missing,
            ];

            foreach ($missing as $column) {
                $errors[] = $table.'.'.$column.' is missing.';
            }
        }
    }

    private function checkIndexes(array &$checks, array &$errors): void
    {
        foreach (self::REQUIRED_INDEXES as $table => $expectedIndexes) {
            if (! Schema::hasTable($table)) {
                continue;
            }

            try {
                $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['indexes:'.$table] = [
                    'status' => $missing === [] ? 'pass' : 'fail',
                    'missing' => $missing,
                ];

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

    private function checkPermissions(array &$checks, array &$errors): void
    {
        if (! Schema::hasTable('permissions')) {
            $checks['permissions'] = ['status' => 'fail', 'missing_table' => true];
            $errors[] = 'permissions table is missing.';
            return;
        }

        $present = DB::table('permissions')
            ->where('guard_name', 'web')
            ->whereIn('name', self::REQUIRED_PERMISSIONS)
            ->pluck('name')
            ->all();
        $missing = array_values(array_diff(self::REQUIRED_PERMISSIONS, $present));

        $checks['permissions'] = [
            'status' => $missing === [] ? 'pass' : 'fail',
            'missing' => $missing,
        ];
        foreach ($missing as $permission) {
            $errors[] = 'Required permission '.$permission.' is missing.';
        }
    }

    private function checkSnapshotForeignKey(array &$checks, array &$errors, array &$warnings): void
    {
        if (! Schema::hasTable('loans') || ! Schema::hasColumn('loans', 'application_credit_score_history_id')) {
            return;
        }

        if (DB::connection()->getDriverName() !== 'mysql') {
            $checks['snapshot_foreign_key'] = ['status' => 'pass', 'verification' => 'schema-column-only'];
            $warnings[] = 'Snapshot foreign-key metadata verification is only enforced on the supported MySQL production connection.';
            return;
        }

        try {
            $database = DB::connection()->getDatabaseName();
            $constraint = DB::table('information_schema.KEY_COLUMN_USAGE')
                ->where('TABLE_SCHEMA', $database)
                ->where('TABLE_NAME', 'loans')
                ->where('COLUMN_NAME', 'application_credit_score_history_id')
                ->where('REFERENCED_TABLE_NAME', 'customer_credit_score_histories')
                ->exists();

            $checks['snapshot_foreign_key'] = ['status' => $constraint ? 'pass' : 'fail'];
            if (! $constraint) {
                $errors[] = 'Loan application score snapshot foreign key is missing.';
            }
        } catch (Throwable $exception) {
            $checks['snapshot_foreign_key'] = ['status' => 'fail', 'message' => $exception->getMessage()];
            $errors[] = 'Could not verify the loan application score snapshot foreign key.';
        }
    }
}
