<?php

namespace App\Console\Commands\Migrations;

use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class DonorFinancialPortfolioMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_project_wallets';

    protected string $modelName = 'Donor Financial Portfolios';

    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 'Donor Financial Portfolio';
    }

    protected function getModelClass(): string
    {
        return 'App\\Models\\DonorFinancialPortfolio';
    }

    protected function processData($oldDb, bool $dryRun = false, int $startFrom = 0, int $batchSize = 1000): void
    {
        $query = $oldDb->table($this->oldTableName)
            ->orderBy('id');

        $totalCount = $query->count();
        $this->stats['total'] = $totalCount;

        $this->info("📊 Found {$totalCount} donor financial portfolios to migrate");

        if ($totalCount === 0) {
            $this->warn('⚠️  No donor financial portfolios found to migrate');

            return;
        }

        $processed = 0;
        $bar = $this->command->getOutput()->createProgressBar($totalCount);

        $query->skip($startFrom)
            ->chunk($batchSize, function ($oldRecords) use ($dryRun, &$processed, $bar) {
                foreach ($oldRecords as $oldRecord) {
                    try {
                        $data = $this->mapData($oldRecord);

                        if ($this->validateData($data)) {
                            if (! $dryRun) {
                                $this->createModel($data);
                            }
                            $this->stats['migrated']++;
                        } else {
                            $this->stats['skipped']++;
                        }
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error processing portfolio ID {$oldRecord->id}: ".$e->getMessage());
                    }

                    $processed++;
                    $bar->advance();
                }
            });

        $bar->finish();
        $this->command->info('');
    }

    protected function mapData(object $oldRecord): array
    {
        return [
            'id' => $oldRecord->id, // Preserve original ID
            'name' => $oldRecord->wallet_name ?? 'Unnamed Portfolio',
            'balance' => $this->parseDecimal($oldRecord->total_amount),
            'created_by' => $this->mapUserId($oldRecord->created_by),
            'created_at' => $this->parseDateTime($oldRecord->created_at),
            'updated_at' => $this->parseDateTime($oldRecord->modified_at),
            'old_record' => $oldRecord, // Keep for reference
        ];
    }

    protected function validateData(array $data): bool
    {
        // Must have a name
        if (empty($data['name']) || trim($data['name']) === '') {
            $this->warn("⚠️  Skipping portfolio {$data['id']}: Missing name");

            return false;
        }

        // Balance must be non-negative
        if ($data['balance'] < 0) {
            $this->warn("⚠️  Skipping portfolio {$data['id']}: Negative balance ({$data['balance']})");

            return false;
        }

        // Check if portfolio already exists
        $existingPortfolio = DB::table('donor_financial_portfolios')->where('id', $data['id'])->exists();
        if ($existingPortfolio) {
            $this->warn("⚠️  Skipping portfolio {$data['id']}: Already exists");

            return false;
        }

        return true;
    }

    protected function createModel(array $data): void
    {
        $oldRecord = $data['old_record'];
        unset($data['old_record']);

        // Use raw DB insertion to preserve the original ID
        DB::table('donor_financial_portfolios')->insert($data);

        $portfolioId = $data['id'];
        $portfolioName = $data['name'];

        $this->info("✅ Created portfolio: {$portfolioName} (ID: {$portfolioId})");
    }

    /**
     * Parse decimal value, handling null and invalid values
     */
    private function parseDecimal($value): float
    {
        if (is_null($value) || $value === '' || $value === 'nan') {
            return 0.00;
        }

        $parsed = floatval($value);

        return max(0, $parsed); // Ensure non-negative
    }

    /**
     * Map user ID from old system to new system
     */
    private function mapUserId(?int $oldUserId): ?int
    {
        if (empty($oldUserId)) {
            return null;
        }

        // Check if user exists in new system
        $user = DB::table('users')->where('id', $oldUserId)->first();

        return $user ? $user->id : null;
    }

    /**
     * Parse datetime string to Carbon instance
     */
    protected function parseDateTime($dateTime): ?Carbon
    {
        if (empty($dateTime) || $dateTime === '0000-00-00 00:00:00') {
            return null;
        }

        try {
            return Carbon::parse($dateTime);
        } catch (\Exception $e) {
            return null;
        }
    }
}
