<?php

namespace App\Console\Commands\Migrations;

use App\Models\Order;
use Illuminate\Support\Facades\DB;

class DonorPortfolioOrderMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_wallet_request_payments';

    protected string $modelName = 'Donor Portfolio Order Relations';

    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 Portfolio Order Relation';
    }

    protected function getModelClass(): string
    {
        return 'App\\Models\\DonorFinancialPortfolio'; // Using portfolio as main model
    }

    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 portfolio-order relations to migrate");

        if ($totalCount === 0) {
            $this->warn('⚠️  No donor portfolio-order relations 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) {
                    // Use a transaction for each record to prevent transaction aborted errors
                    DB::beginTransaction();
                    try {
                        $data = $this->mapData($oldRecord);

                        if ($this->validateData($data, $dryRun)) {
                            if (! $dryRun) {
                                $this->createModel($data);
                            }
                            $this->stats['migrated']++;
                        } else {
                            $this->stats['skipped']++;
                        }

                        DB::commit();
                    } catch (\Exception $e) {
                        DB::rollback();
                        $this->stats['errors']++;
                        $this->error("❌ Error processing relation ID {$oldRecord->id}: ".$e->getMessage());
                    }

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

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

    protected function mapData(object $oldRecord): array
    {
        // Get order's approved price for percentage calculation
        $orderFinance = DB::table('order_finances')->where('order_id', $oldRecord->request_id)->first();

        // Calculate approved price (offer_price + additional_fees) since it's not stored in DB
        $approvedPrice = null;
        if ($orderFinance && $orderFinance->offer_price !== null && $orderFinance->additional_fees !== null) {
            $approvedPrice = (float) $orderFinance->offer_price + (float) $orderFinance->additional_fees;
        }

        $paymentPercentage = $this->parseDecimal($oldRecord->payment_percentage);
        $paymentAmount = $this->parseDecimal($oldRecord->payment_amount);

        // Calculate allocated amount
        $allocatedAmount = null;
        if ($paymentPercentage > 0 && $approvedPrice > 0) {
            $allocatedAmount = ($approvedPrice * $paymentPercentage) / 100;
        } elseif ($paymentAmount > 0) {
            // Use payment_amount if percentage calculation fails
            $allocatedAmount = $paymentAmount;
        }

        return [
            'order_id' => $oldRecord->request_id,
            'donor_financial_portfolio_id' => $oldRecord->wallet_id,
            'payment_percentage' => $paymentPercentage,
            'payment_amount' => $paymentAmount,
            'allocated_amount' => $allocatedAmount,
            'approved_price' => $approvedPrice, // Store for debugging
            'created_at' => now(),
            'updated_at' => now(),
            'old_record' => $oldRecord, // Keep for reference
        ];
    }

    protected function validateData(array $data, bool $dryRun = false): bool
    {
        // Check if order exists
        $order = DB::table('orders')->where('id', $data['order_id'])->first();
        if (! $order) {
            $this->warn("⚠️  Skipping relation: Order {$data['order_id']} not found");

            return false;
        }

        // Check if portfolio exists (skip this check in dry-run mode since portfolios haven't been migrated yet)
        if (! $dryRun) {
            $portfolio = DB::table('donor_financial_portfolios')->where('id', $data['donor_financial_portfolio_id'])->first();
            if (! $portfolio) {
                $this->warn("⚠️  Skipping relation: Portfolio {$data['donor_financial_portfolio_id']} not found");

                return false;
            }
        } else {
            // In dry-run mode, check if portfolio exists in the old database
            $oldDb = DB::connection($this->connection);
            $oldPortfolio = $oldDb->table('sek_project_wallets')->where('id', $data['donor_financial_portfolio_id'])->first();
            if (! $oldPortfolio) {
                $this->warn("⚠️  Skipping relation: Portfolio {$data['donor_financial_portfolio_id']} not found in old database");

                return false;
            }
        }

        // Validate allocated amount is positive and not null
        if ($data['allocated_amount'] === null || $data['allocated_amount'] <= 0) {
            $financeInfo = isset($data['approved_price']) ?
                "approved_price: {$data['approved_price']}" :
                'no approved price calculated';
            $this->warn("⚠️  Skipping relation: No valid amount or percentage for Order {$data['order_id']} (payment_percentage: {$data['payment_percentage']}, payment_amount: {$data['payment_amount']}, {$financeInfo})");

            return false;
        }

        // Check if relation already exists (skip in dry-run mode)
        if (! $dryRun) {
            $existingRelation = DB::table('donor_portfolio_order')
                ->where('order_id', $data['order_id'])
                ->where('donor_financial_portfolio_id', $data['donor_financial_portfolio_id'])
                ->exists();

            if ($existingRelation) {
                $this->warn("⚠️  Skipping relation: Already exists for Order {$data['order_id']} and Portfolio {$data['donor_financial_portfolio_id']}");

                return false;
            }
        }

        return true;
    }

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

        // Remove fields not needed for insertion
        unset($data['old_record'], $data['payment_percentage'], $data['payment_amount'], $data['approved_price']);

        // Insert into pivot table
        DB::table('donor_portfolio_order')->insert($data);

        $this->info("✅ Created relation: Order {$data['order_id']} ↔ Portfolio {$data['donor_financial_portfolio_id']} (Amount: {$data['allocated_amount']})");
    }

    /**
     * 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
    }
}
