<?php

namespace App\Console\Commands\Migrations;

use Illuminate\Support\Facades\DB;

class ProjectCharterPerformanceCardResultMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_project_charter_result_links';

    protected string $modelName = 'Project Charter Performance Card Result Links';

    public function migrate(bool $dryRun = false, int $batchSize = 1000, int $startFrom = 0): void
    {
        $oldDb = DB::connection($this->connection);
        $this->processData($oldDb, $dryRun, $startFrom, $batchSize);
    }

    protected function getModelName(): string
    {
        return 'Project Charter Performance Card Result Link';
    }

    protected function getModelClass(): string
    {
        return 'project_charter_performance_card_result'; // This is a pivot table, not a model
    }

    protected function createModel(array $data): void
    {
        // Insert into project_charter_performance_card_result table using raw DB query to preserve ID
        DB::table('project_charter_performance_card_result')->insert($data);
    }

    protected function processData($oldDb, bool $dryRun, int $startFrom, int $batchSize): void
    {
        // Query with join to get the actual performance_card_result_id
        $query = $oldDb->table($this->oldTableName.' as pcrl')
            ->join('sek_initiative_result_result_links as irrl', 'pcrl.result_link_id', '=', 'irrl.id')
            ->select([
                'pcrl.id',
                'pcrl.charter_id',
                'irrl.result_id as performance_card_result_id', // Get the actual result_id
                'pcrl.created_at',
                'pcrl.modified_at',
            ])
            ->orderBy('pcrl.id')
            ->offset($startFrom);

        $totalRecords = $oldDb->table($this->oldTableName)->count();
        $this->info("📊 Found {$totalRecords} project charter performance card result links to migrate");

        if ($totalRecords === 0) {
            $this->info('✅ No records to migrate');

            return;
        }

        $bar = $this->command->getOutput()->createProgressBar($totalRecords);
        $bar->start();

        $query->chunk($batchSize, function ($oldRecords) use ($dryRun, $bar) {
            foreach ($oldRecords as $oldRecord) {
                $bar->advance();

                $data = $this->mapData($oldRecord);

                if ($this->validateData($data)) {
                    try {
                        if (! $dryRun) {
                            $this->createModel($data);
                        }
                        $this->stats['migrated']++;
                        $this->info("✅ Created project charter PCR link: {$data['id']} - Charter {$data['project_charter_id']} -> PCR {$data['performance_card_result_id']}");
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error creating project charter PCR link ID {$oldRecord->id}: {$e->getMessage()}");
                    }
                } else {
                    $this->stats['skipped']++;
                    $this->warn("⚠️  Skipped project charter PCR link ID {$oldRecord->id} (validation failed)");
                }
            }
        });

        $bar->finish();
        $this->info('');  // Add newline
    }

    protected function mapData($oldRecord): array
    {
        return [
            'id' => $oldRecord->id,
            'project_charter_id' => $oldRecord->charter_id,
            'performance_card_result_id' => $oldRecord->performance_card_result_id, // Now comes from the join
            'created_at' => $this->parseDateTime($oldRecord->created_at),
            'updated_at' => $this->parseDateTime($oldRecord->modified_at ?: $oldRecord->created_at),
        ];
    }

    protected function validateData(array $data): bool
    {
        // Check if project charter exists
        if (! DB::table('project_charters')->where('id', $data['project_charter_id'])->exists()) {
            $this->warn("⚠️  Project charter with ID {$data['project_charter_id']} does not exist");

            return false;
        }

        // Check if performance card result exists
        if (! DB::table('performance_card_results')->where('id', $data['performance_card_result_id'])->exists()) {
            $this->warn("⚠️  Performance card result with ID {$data['performance_card_result_id']} does not exist");

            return false;
        }

        // Check if link already exists
        $exists = DB::table('project_charter_performance_card_result')
            ->where('id', $data['id'])
            ->exists();

        if ($exists) {
            $this->warn("⚠️  Project charter PCR link with ID {$data['id']} already exists");

            return false;
        }

        // Check for duplicate relationship (unique constraint)
        $duplicateRelation = DB::table('project_charter_performance_card_result')
            ->where('project_charter_id', $data['project_charter_id'])
            ->where('performance_card_result_id', $data['performance_card_result_id'])
            ->exists();

        if ($duplicateRelation) {
            $this->warn("⚠️  Relationship between Project Charter {$data['project_charter_id']} and Performance Card Result {$data['performance_card_result_id']} already exists");

            return false;
        }

        return true;
    }

    protected function getStatsSummary(): array
    {
        return [
            'Project-charter-pcr-links' => [
                'Total' => $this->stats['total'] ?? 0,
                'Migrated' => $this->stats['migrated'] ?? 0,
                'Skipped' => $this->stats['skipped'] ?? 0,
                'Errors' => $this->stats['errors'] ?? 0,
            ],
        ];
    }
}
