<?php

namespace App\Console\Commands;

use App\Console\Commands\Migrations\AssociationFilesMigration;
use App\Console\Commands\Migrations\AssociationLocationMigrationFinal;
use App\Console\Commands\Migrations\AssociationLogoMigration;
use App\Console\Commands\Migrations\AssociationMigration;
use App\Console\Commands\Migrations\CenterMigration;
use App\Console\Commands\Migrations\CityMigration;
use App\Console\Commands\Migrations\DonorFinancialPortfolioMigration;
use App\Console\Commands\Migrations\DonorPortfolioOrderMigration;
use App\Console\Commands\Migrations\InitiativeMigration;
use App\Console\Commands\Migrations\InitiativeOperationGoalMigration;
use App\Console\Commands\Migrations\InitiativeProductMigration;
use App\Console\Commands\Migrations\InitiativeProjectMigration;
use App\Console\Commands\Migrations\InitiativeResultMigration;
use App\Console\Commands\Migrations\InitiativeResultPcrMigration;
use App\Console\Commands\Migrations\MigrationManager;
use App\Console\Commands\Migrations\OrderDevelopmentImpactMigration;
use App\Console\Commands\Migrations\OrderFilesMigration;
use App\Console\Commands\Migrations\OrderFinanceMigration;
use App\Console\Commands\Migrations\OrderMigration;
use App\Console\Commands\Migrations\OrderRequirementFilesMigration;
use App\Console\Commands\Migrations\OrderRequirementMigration;
use App\Console\Commands\Migrations\PerformanceCardAdminMigration;
use App\Console\Commands\Migrations\PerformanceCardMigration;
use App\Console\Commands\Migrations\PerformanceCardProductMigration;
use App\Console\Commands\Migrations\PerformanceCardResultMigration;
use App\Console\Commands\Migrations\PerformanceWalletMigration;
use App\Console\Commands\Migrations\ProjectCharterDeliverableMigration;
use App\Console\Commands\Migrations\ProjectCharterMigration;
use App\Console\Commands\Migrations\ProjectCharterPerformanceCardResultMigration;
use App\Console\Commands\Migrations\ProjectCharterTaskMigration;
use App\Console\Commands\Migrations\RolesMigration;
use App\Console\Commands\Migrations\SekayaValueMigration;
use App\Console\Commands\Migrations\StateMigration;
use App\Console\Commands\Migrations\UserMigration;
use App\Console\Commands\Migrations\WaterSourceMigration;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class MigrateOldDatabaseCommand extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'migrate:old-database 
                           {--connection=old_database : Old database connection name}
                           {--dry-run : Run migration without actually inserting data}
                           {--batch-size=100 : Number of records to process in each batch}
                           {--start-from=0 : Start migration from specific record number}
                           {--roles-only : Migrate only roles}
                           {--associations-only : Migrate only associations}
                           {--users-only : Migrate only users}
                           {--orders-only : Migrate order requirements and orders}
                           {--states-only : Migrate only states}
                           {--cities-only : Migrate only cities}
                           {--centers-only : Migrate only centers}
                           {--water-sources-only : Migrate only water sources}
                           {--sekaya-values-only : Migrate only sekaya values}
                           {--order-development-impacts-only : Migrate only order development impact relationships}
                           {--donor-portfolios-only : Migrate only donor financial portfolios}
                           {--performance-cards-only : Migrate only performance cards}
                           {--performance-card-products-only : Migrate only performance card products relationships}
                           {--performance-card-admins-only : Migrate only performance card admin relationships}
                           {--performance-card-results-only : Migrate only performance card results}
                           {--performance-wallets-only : Migrate only performance wallets}
                           {--initiatives-only : Migrate only initiatives}
                           {--initiative-operation-goals-only : Migrate only initiative operation goals}
                           {--initiative-results-only : Migrate only initiative results}
                           {--initiative-result-pcr-only : Migrate only initiative result PCR links}
                           {--initiative-projects-only : Migrate only initiative projects}
                           {--project-charters-only : Migrate only project charters}
                           {--project-charter-pcr-only : Migrate only project charter PCR links}
                           {--project-charter-deliverables-only : Migrate only project charter deliverables}
                           {--project-charter-tasks-only : Migrate only project charter tasks}
                           {--initiative-products-only : Migrate only initiative product relationships}
                           {--association-locations-only : Migrate only association location relationships}
                           {--association-logos-only : Migrate only association logos}
                           {--association-files-only : Migrate only association files (authorization letters, certificates, etc.)}
                           {--order-files-only : Migrate only order files (requirement documents)}
                           {--order-requirement-files-only : Migrate only order requirement files}
                           {--locations : Migrate states, cities, and centers}
                           {--basic : Migrate roles, states, cities, centers (basic data)}
                           {--full : Migrate associations and users (requires basic data)}
                           {--relationships : Migrate association location relationships (requires associations and locations)}';

    /**
     * The console command description.
     */
    protected $description = 'Migrate data from old database schema to new database schema';

    /**
     * Migration manager instance
     */
    protected MigrationManager $migrationManager;

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('🚀 Starting migration from old database...');

        $connection = $this->option('connection');

        // Validate old database connection
        if (! $this->validateOldDatabaseConnection($connection)) {
            return Command::FAILURE;
        }

        // Initialize migration manager and register migrations
        $this->migrationManager = new MigrationManager($this, $connection);
        $this->registerMigrations();

        $dryRun = $this->option('dry-run');

        if ($dryRun) {
            $this->warn('🧪 DRY RUN MODE - No data will be inserted');
        }

        $batchSize = (int) $this->option('batch-size');
        $startFrom = (int) $this->option('start-from');

        try {
            DB::transaction(function () use ($dryRun, $batchSize, $startFrom) {
                // Run specific migrations based on options
                if ($this->option('roles-only')) {
                    $this->migrationManager->runMigration('roles', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('associations-only')) {
                    $this->migrationManager->runMigration('associations', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('users-only')) {
                    $this->migrationManager->runMigration('users', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('orders-only')) {
                    // Run orders first, then order requirements, then order finance
                    $this->migrationManager->runMigration('orders', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('order-requirements', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('order-finance', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('states-only')) {
                    $this->migrationManager->runMigration('states', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('cities-only')) {
                    $this->migrationManager->runMigration('cities', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('centers-only')) {
                    $this->migrationManager->runMigration('centers', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('water-sources-only')) {
                    $this->migrationManager->runMigration('water-sources', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('sekaya-values-only')) {
                    $this->migrationManager->runMigration('sekaya-values', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('order-development-impacts-only')) {
                    $this->migrationManager->runMigration('order-development-impacts', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('donor-portfolios-only')) {
                    // Run donor portfolios first, then the relationships
                    $this->migrationManager->runMigration('donor-portfolios', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('donor-portfolio-orders', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('performance-cards-only')) {
                    $this->migrationManager->runMigration('performance-cards', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('performance-card-products-only')) {
                    $this->migrationManager->runMigration('performance-card-products', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('performance-card-admins-only')) {
                    $this->migrationManager->runMigration('performance-card-admins', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('performance-card-results-only')) {
                    $this->migrationManager->runMigration('performance-card-results', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('performance-wallets-only')) {
                    $this->migrationManager->runMigration('performance-wallets', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiatives-only')) {
                    $this->migrationManager->runMigration('initiatives', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiative-operation-goals-only')) {
                    $this->migrationManager->runMigration('initiative-operation-goals', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiative-results-only')) {
                    $this->migrationManager->runMigration('initiative-results', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiative-result-pcr-only')) {
                    $this->migrationManager->runMigration('initiative-result-pcr', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiative-projects-only')) {
                    $this->migrationManager->runMigration('initiative-projects', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('project-charters-only')) {
                    $this->migrationManager->runMigration('project-charters', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('project-charter-pcr-only')) {
                    $this->migrationManager->runMigration('project-charter-pcr', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('project-charter-deliverables-only')) {
                    $this->migrationManager->runMigration('project-charter-deliverables', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('project-charter-tasks-only')) {
                    $this->migrationManager->runMigration('project-charter-tasks', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('initiative-products-only')) {
                    $this->migrationManager->runMigration('initiative-products', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('association-locations-only')) {
                    $this->migrationManager->runMigration('association-locations', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('association-logos-only')) {
                    $this->migrationManager->runMigration('association-logos', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('association-files-only')) {
                    $this->migrationManager->runMigration('association-files', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('order-files-only')) {
                    $this->migrationManager->runMigration('order-files', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('order-requirement-files-only')) {
                    $this->migrationManager->runMigration('order-requirement-files', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('locations')) {
                    // Run location hierarchy migrations
                    $this->migrationManager->runMigration('states', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('cities', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('centers', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('basic')) {
                    // Run basic data migrations (no complex relationships)
                    $this->migrationManager->runMigration('roles', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('states', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('cities', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('centers', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('water-sources', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('full')) {
                    // Run complex entity migrations (requires basic data)
                    $this->migrationManager->runMigration('associations', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('users', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('donor-portfolios', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('orders', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('order-requirements', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('order-finance', $dryRun, $batchSize, $startFrom);
                    $this->migrationManager->runMigration('donor-portfolio-orders', $dryRun, $batchSize, $startFrom);
                } elseif ($this->option('relationships')) {
                    // Run relationship migrations (requires associations and locations)
                    $this->migrationManager->runMigration('association-locations', $dryRun, $batchSize, $startFrom);
                } else {
                    // Run all migrations in order
                    $this->migrationManager->runAll($dryRun, $batchSize, $startFrom);
                }
            });

            $this->migrationManager->displayResults($dryRun);

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('❌ Migration failed: '.$e->getMessage());
            $this->error('Stack trace: '.$e->getTraceAsString());

            return Command::FAILURE;
        }
    }

    /**
     * Register all available migrations
     */
    protected function registerMigrations(): void
    {
        $connection = $this->option('connection');

        // Register migrations in dependency order
        // Basic data (no dependencies)
        $this->migrationManager->register('roles', new RolesMigration($this, $connection));
        $this->migrationManager->register('states', new StateMigration($this, $connection));

        // Location hierarchy (depends on states)
        $this->migrationManager->register('cities', new CityMigration($this, $connection));
        $this->migrationManager->register('centers', new CenterMigration($this, $connection));
        $this->migrationManager->register('water-sources', new WaterSourceMigration($this, $connection));
        $this->migrationManager->register('sekaya-values', new SekayaValueMigration($this, $connection));

        // Complex entities (depend on basic data)
        $this->migrationManager->register('associations', new AssociationMigration($this, $connection));

        // Association file migrations (depend on associations existing)
        $this->migrationManager->register('association-logos', new AssociationLogoMigration($this, $connection));
        $this->migrationManager->register('association-files', new AssociationFilesMigration($this, $connection));

        $this->migrationManager->register('users', new UserMigration($this, $connection));
        $this->migrationManager->register('donor-portfolios', new DonorFinancialPortfolioMigration($this, $connection));
        $this->migrationManager->register('performance-cards', new PerformanceCardMigration($this, $connection));

        // Performance card relationships (depend on performance cards and sekaya values)
        $this->migrationManager->register('performance-card-products', new PerformanceCardProductMigration($this, $connection));

        // Performance card admin relationships (depend on performance cards and users)
        $this->migrationManager->register('performance-card-admins', new PerformanceCardAdminMigration($this, $connection));

        // Performance card results (depend on performance cards)
        $this->migrationManager->register('performance-card-results', new PerformanceCardResultMigration($this, $connection));

        // Performance wallets (depend on performance cards)
        $this->migrationManager->register('performance-wallets', new PerformanceWalletMigration($this, $connection));

        // Initiatives (depend on performance wallets)
        $this->migrationManager->register('initiatives', new InitiativeMigration($this, $connection));

        // Initiative operation goals (depend on initiatives)
        $this->migrationManager->register('initiative-operation-goals', new InitiativeOperationGoalMigration($this, $connection));

        // Initiative results (depend on initiatives)
        $this->migrationManager->register('initiative-results', new InitiativeResultMigration($this, $connection));

        // Initiative result PCR links (depend on initiative results and performance card results)
        $this->migrationManager->register('initiative-result-pcr', new InitiativeResultPcrMigration($this, $connection));

        // Initiative projects (depend on initiatives)
        $this->migrationManager->register('initiative-projects', new InitiativeProjectMigration($this, $connection));

        // Project charters (depend on initiative projects and orders)
        $this->migrationManager->register('project-charters', new ProjectCharterMigration($this, $connection));

        // Project charter PCR links (depend on project charters and performance card results)
        $this->migrationManager->register('project-charter-pcr', new ProjectCharterPerformanceCardResultMigration($this, $connection));

        // Project charter deliverables (depend on project charters - combines completion reports and payments)
        $this->migrationManager->register('project-charter-deliverables', new ProjectCharterDeliverableMigration($this, $connection));

        // Project charter tasks (depend on project charter deliverables)
        $this->migrationManager->register('project-charter-tasks', new ProjectCharterTaskMigration($this, $connection));

        // Initiative product relationships (depend on initiatives and sekaya values)
        $this->migrationManager->register('initiative-products', new InitiativeProductMigration($this, $connection));

        // Order-related migrations (requirements depend on orders, finance depends on both)
        $this->migrationManager->register('orders', new OrderMigration($this, $connection));
        $this->migrationManager->register('order-requirements', new OrderRequirementMigration($this, $connection));
        $this->migrationManager->register('order-finance', new OrderFinanceMigration($this, $connection));

        // Order file migrations (depend on orders existing)
        $this->migrationManager->register('order-files', new OrderFilesMigration($this, $connection));

        // Order requirement file migrations (depend on order requirements existing)
        $this->migrationManager->register('order-requirement-files', new OrderRequirementFilesMigration($this, $connection));

        // Donor portfolio relationships (depend on orders and portfolios)
        $this->migrationManager->register('donor-portfolio-orders', new DonorPortfolioOrderMigration($this, $connection));

        // Order development impacts relationships (depend on orders and sekaya values)
        $this->migrationManager->register('order-development-impacts', new OrderDevelopmentImpactMigration($this, $connection));

        // Relationships (depend on associations and locations)
        $this->migrationManager->register('association-locations', new AssociationLocationMigrationFinal($this, $connection));
    }

    /**
     * Validate old database connection
     */
    protected function validateOldDatabaseConnection(string $connection): bool
    {
        try {
            $this->info("🔍 Testing connection to: {$connection}");

            // Test connection and check if required tables exist
            $oldDb = DB::connection($connection);

            $requiredTables = [
                'sek_associations',
                'sek_users',
                'sek_addresses',
                'sek_user_bank_information',
                'sek_banks',
            ];

            foreach ($requiredTables as $table) {
                if (! $oldDb->getSchemaBuilder()->hasTable($table)) {
                    $this->error("❌ Required table '{$table}' not found in old database");

                    return false;
                }
            }

            $this->info('✅ Old database connection validated');

            return true;
        } catch (\Exception $e) {
            $this->error('❌ Failed to connect to old database: '.$e->getMessage());

            return false;
        }
    }
}
