<?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
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Check if we're using PostgreSQL (production) or SQLite (testing)
        $driver = Schema::getConnection()->getDriverName();

        if ($driver === 'pgsql') {
            // PostgreSQL: Use raw SQL with CASCADE to drop columns and dependent views
            DB::statement('ALTER TABLE transactions
                DROP COLUMN IF EXISTS registry_company_number CASCADE,
                DROP COLUMN IF EXISTS registry_source CASCADE,
                DROP COLUMN IF EXISTS registry_match_score CASCADE,
                DROP COLUMN IF EXISTS registry_data CASCADE,
                DROP COLUMN IF EXISTS registry_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS genesis_context CASCADE,
                DROP COLUMN IF EXISTS genesis_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS govdata_data CASCADE,
                DROP COLUMN IF EXISTS govdata_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS bundesanzeiger_data CASCADE,
                DROP COLUMN IF EXISTS bundesanzeiger_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS insolvency_data CASCADE,
                DROP COLUMN IF EXISTS insolvency_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS rss_alerts CASCADE,
                DROP COLUMN IF EXISTS rss_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS sanctions_data CASCADE,
                DROP COLUMN IF EXISTS sanctions_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS pep_data CASCADE,
                DROP COLUMN IF EXISTS pep_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS gleif_lei CASCADE,
                DROP COLUMN IF EXISTS gleif_data CASCADE,
                DROP COLUMN IF EXISTS gleif_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS eu_sanctions_data CASCADE,
                DROP COLUMN IF EXISTS eu_sanctions_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS handelsregister_data CASCADE,
                DROP COLUMN IF EXISTS handelsregister_last_refreshed_at CASCADE,
                DROP COLUMN IF EXISTS handelsregister_status CASCADE,
                DROP COLUMN IF EXISTS handelsregister_entity_id CASCADE
            ');
        } else {
            // SQLite: Use Schema Builder to drop columns (if they exist)
            Schema::table('transactions', function (Blueprint $table) {
                $columns = [
                    'registry_company_number', 'registry_source', 'registry_match_score',
                    'registry_data', 'registry_last_refreshed_at', 'genesis_context',
                    'genesis_last_refreshed_at', 'govdata_data', 'govdata_last_refreshed_at',
                    'bundesanzeiger_data', 'bundesanzeiger_last_refreshed_at', 'insolvency_data',
                    'insolvency_last_refreshed_at', 'rss_alerts', 'rss_last_refreshed_at',
                    'sanctions_data', 'sanctions_last_refreshed_at', 'pep_data',
                    'pep_last_refreshed_at', 'gleif_lei', 'gleif_data',
                    'gleif_last_refreshed_at', 'eu_sanctions_data', 'eu_sanctions_last_refreshed_at',
                    'handelsregister_data', 'handelsregister_last_refreshed_at',
                    'handelsregister_status', 'handelsregister_entity_id',
                ];

                foreach ($columns as $column) {
                    if (Schema::hasColumn('transactions', $column)) {
                        $table->dropColumn($column);
                    }
                }
            });
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('transactions', function (Blueprint $table) {
            // Registry Data
            $table->text('registry_company_number')->nullable();
            $table->text('registry_source')->nullable();
            $table->double('registry_match_score')->nullable();
            $table->jsonb('registry_data')->nullable();
            $table->timestamp('registry_last_refreshed_at')->nullable();

            // Genesis
            $table->jsonb('genesis_context')->nullable();
            $table->timestamp('genesis_last_refreshed_at')->nullable();

            // Govdata
            $table->jsonb('govdata_data')->nullable();
            $table->timestamp('govdata_last_refreshed_at')->nullable();

            // Bundesanzeiger
            $table->jsonb('bundesanzeiger_data')->nullable();
            $table->timestamp('bundesanzeiger_last_refreshed_at')->nullable();

            // Insolvency
            $table->jsonb('insolvency_data')->nullable();
            $table->timestamp('insolvency_last_refreshed_at')->nullable();

            // RSS Alerts
            $table->jsonb('rss_alerts')->nullable();
            $table->timestamp('rss_last_refreshed_at')->nullable();

            // Sanctions
            $table->jsonb('sanctions_data')->nullable();
            $table->timestamp('sanctions_last_refreshed_at')->nullable();

            // PEP - Politically Exposed Person
            $table->jsonb('pep_data')->nullable();
            $table->timestamp('pep_last_refreshed_at')->nullable();

            // GLEIF
            $table->text('gleif_lei')->nullable();
            $table->json('gleif_data')->nullable();
            $table->timestamp('gleif_last_refreshed_at')->nullable();

            // EU Sanctions
            $table->jsonb('eu_sanctions_data')->nullable();
            $table->timestamp('eu_sanctions_last_refreshed_at')->nullable();

            // Handelsregister
            $table->jsonb('handelsregister_data')->nullable();
            $table->timestamp('handelsregister_last_refreshed_at')->nullable();
            $table->text('handelsregister_status')->nullable();
            $table->bigInteger('handelsregister_entity_id')->nullable();
        });
    }
};
