<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        if (Schema::hasTable('merchants')) {
            Schema::table('merchants', function (Blueprint $table) {
                if (! Schema::hasColumn('merchants', 'two_factor_confirmed_at')) $table->timestamp('two_factor_confirmed_at')->nullable();
                if (! Schema::hasColumn('merchants', 'last_login_at')) $table->timestamp('last_login_at')->nullable();
                if (! Schema::hasColumn('merchants', 'session_version')) $table->unsignedBigInteger('session_version')->default(1);
                if (! Schema::hasColumn('merchants', 'risk_settings')) $table->json('risk_settings')->nullable();
            });
        }

        if (Schema::hasTable('api_keys')) {
            Schema::table('api_keys', function (Blueprint $table) {
                if (! Schema::hasColumn('api_keys', 'rate_limit_per_minute')) $table->unsignedInteger('rate_limit_per_minute')->nullable();
                if (! Schema::hasColumn('api_keys', 'revoked_at')) $table->timestamp('revoked_at')->nullable();
            });
        }

        if (Schema::hasTable('webhook_endpoints')) {
            Schema::table('webhook_endpoints', function (Blueprint $table) {
                if (! Schema::hasColumn('webhook_endpoints', 'consecutive_failures')) $table->unsignedInteger('consecutive_failures')->default(0);
                if (! Schema::hasColumn('webhook_endpoints', 'last_success_at')) $table->timestamp('last_success_at')->nullable();
                if (! Schema::hasColumn('webhook_endpoints', 'last_failure_at')) $table->timestamp('last_failure_at')->nullable();
                if (! Schema::hasColumn('webhook_endpoints', 'health_status')) $table->string('health_status', 30)->default('unknown');
            });
        }

        if (Schema::hasTable('payouts')) {
            Schema::table('payouts', function (Blueprint $table) {
                if (! Schema::hasColumn('payouts', 'risk_status')) $table->string('risk_status', 30)->default('pending');
                if (! Schema::hasColumn('payouts', 'risk_result')) $table->json('risk_result')->nullable();
                if (! Schema::hasColumn('payouts', 'step_up_verified_at')) $table->timestamp('step_up_verified_at')->nullable();
                if (! Schema::hasColumn('payouts', 'daily_limit_snapshot_atomic')) $table->unsignedBigInteger('daily_limit_snapshot_atomic')->nullable();
            });
        }

        if (! Schema::hasTable('reconciliation_items')) {
            Schema::create('reconciliation_items', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('reconciliation_run_id')->constrained('reconciliation_runs')->cascadeOnDelete();
                $table->foreignId('merchant_id')->nullable()->constrained('merchants');
                $table->foreignId('asset_id')->nullable()->constrained('assets');
                $table->string('type', 60);
                $table->string('severity', 20)->default('warning');
                $table->string('reference_type', 80)->nullable();
                $table->string('reference_id', 120)->nullable();
                $table->decimal('difference_atomic', 65, 0)->default(0);
                $table->json('details')->nullable();
                $table->string('status', 30)->default('open');
                $table->foreignId('resolved_by')->nullable()->constrained('merchants');
                $table->timestamp('resolved_at')->nullable();
                $table->timestamps();
                $table->index(['status', 'severity']);
            });
        }

        if (! Schema::hasTable('provider_health_checks')) {
            Schema::create('provider_health_checks', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->string('provider', 50);
                $table->string('environment', 20);
                $table->boolean('healthy');
                $table->unsignedInteger('latency_ms')->nullable();
                $table->string('error_code', 80)->nullable();
                $table->text('error_message')->nullable();
                $table->timestamp('checked_at')->nullable();
                $table->index(['provider', 'environment', 'checked_at'], 'provider_health_lookup_idx');
            });
        }

        if (! Schema::hasTable('audit_log_hashes')) {
            Schema::create('audit_log_hashes', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->foreignId('audit_log_id')->unique()->constrained('audit_logs')->cascadeOnDelete();
                $table->char('previous_hash', 64)->nullable();
                $table->char('record_hash', 64)->unique();
                $table->timestamp('created_at')->nullable();
            });
        }

        if (DB::getDriverName() === 'mysql') {
            $existing = collect(DB::select('SHOW TRIGGERS'))->pluck('Trigger')->all();
            if (! in_array('ledger_entries_no_update', $existing, true)) DB::unprepared("CREATE TRIGGER ledger_entries_no_update BEFORE UPDATE ON ledger_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records are immutable'");
            if (! in_array('ledger_entries_no_delete', $existing, true)) DB::unprepared("CREATE TRIGGER ledger_entries_no_delete BEFORE DELETE ON ledger_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records are immutable'");
            if (! in_array('ledger_transactions_no_delete', $existing, true)) DB::unprepared("CREATE TRIGGER ledger_transactions_no_delete BEFORE DELETE ON ledger_transactions FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records are immutable'");
        }
    }

    public function down(): void
    {
        if (DB::getDriverName() === 'mysql') {
            DB::unprepared('DROP TRIGGER IF EXISTS ledger_entries_no_update');
            DB::unprepared('DROP TRIGGER IF EXISTS ledger_entries_no_delete');
            DB::unprepared('DROP TRIGGER IF EXISTS ledger_transactions_no_delete');
        }
        Schema::dropIfExists('audit_log_hashes');
        Schema::dropIfExists('provider_health_checks');
        Schema::dropIfExists('reconciliation_items');
    }
};
