<?php

namespace App\Console\Commands\Migrations;

use App\Models\Association;
use App\Models\Center;
use App\Models\City;
use App\Models\Order;
use App\Models\OrderAgreement;
use App\Models\State;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class OrderMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_requests';

    protected string $modelName = 'Orders';

    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 'Orders';
    }

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

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

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

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

        if ($totalCount === 0) {
            $this->warn('⚠️  No orders 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 {
                        $mappedData = $this->mapData($oldRecord);

                        if ($this->validateData($mappedData)) {
                            if (! $dryRun) {
                                $this->createModel($mappedData);
                                $this->stats['migrated']++;
                            } else {
                                $this->info("🔍 [DRY RUN] Would migrate order ID {$oldRecord->id}: {$mappedData['type']} - {$mappedData['status']}");
                            }
                        } else {
                            $this->stats['skipped']++;
                        }
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error migrating order ID {$oldRecord->id}: ".$e->getMessage());
                    }

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

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

    protected function mapData(object $oldRecord): array
    {
        // Parse coordinates
        $coordinates = $this->parseCoordinates($oldRecord->project_coordinates ?? '');

        // Map water sources
        $waterSources = [];
        if (! empty($oldRecord->is_government)) {
            $waterSources[] = Order::WATER_SOURCE_GOVERNMENT;
        }
        if (! empty($oldRecord->is_private)) {
            $waterSources[] = Order::WATER_SOURCE_PRIVATE;
        }
        if (! empty($oldRecord->is_charity)) {
            $waterSources[] = Order::WATER_SOURCE_CHARITY;
        }

        return [
            'id' => (int) $oldRecord->id,
            'type' => $this->mapOrderType($oldRecord->order_type ?? ''),
            'status' => $this->mapOrderStatus($oldRecord->status ?? ''),
            'created_by' => $this->mapUserId($oldRecord->user_id),
            'association_id' => ! empty($oldRecord->association_id) ? (int) $oldRecord->association_id : null,
            'state_id' => ! empty($oldRecord->state_id) ? (int) $oldRecord->state_id : null,
            'city_id' => ! empty($oldRecord->city_id) ? (int) $oldRecord->city_id : null,
            'lat' => $coordinates['lat'],
            'lng' => $coordinates['lng'],
            'number_of_families' => ! empty($oldRecord->number_of_families) ? (int) $oldRecord->number_of_families : 0,
            'water_source' => $waterSources,
            'water_distance' => ! empty($oldRecord->water_distance_in_km) ? (float) $oldRecord->water_distance_in_km : null,
            'order_justifications' => $oldRecord->justification ?? '',
            'order_description' => $oldRecord->description ?? '',
            'created_at' => $this->parseDateTime($oldRecord->created_at),
            'updated_at' => $this->parseDateTime($oldRecord->modified_at ?? $oldRecord->created_at),

            // Additional data for related tables
            'old_record' => $oldRecord,
        ];
    }

    protected function validateData(array $data): bool
    {
        // Must have valid type and status
        if (empty($data['type']) || empty($data['status'])) {
            $this->warn("⚠️  Skipping order ID {$data['id']}: missing type or status");

            return false;
        }

        // Must have created_by user
        if (empty($data['created_by'])) {
            $this->warn("⚠️  Skipping order ID {$data['id']}: missing created_by user");

            return false;
        }

        // Validate foreign keys exist
        if (! empty($data['association_id']) && ! Association::find($data['association_id'])) {
            $this->warn("⚠️  Skipping order ID {$data['id']}: Association {$data['association_id']} not found");

            return false;
        }

        if (! empty($data['state_id']) && ! State::find($data['state_id'])) {
            $this->warn("⚠️  Skipping order ID {$data['id']}: State {$data['state_id']} not found");

            return false;
        }

        if (! empty($data['city_id']) && ! City::find($data['city_id'])) {
            $this->warn("⚠️  Skipping order ID {$data['id']}: City {$data['city_id']} not found");

            return false;
        }

        return true;
    }

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

        // Create the order with unguarded mass assignment to preserve IDs
        Order::unguarded(function () use ($data, $oldRecord) {
            // Disable automatic activity logging during creation to prevent
            // any unwanted events during migration
            activity()->disableLogging();

            $order = Order::create($data);

            // Create OrderAgreement if agreement data exists
            $this->createOrderAgreement($order, $oldRecord);

            // Create center relationships
            $this->createOrderCenters($order, $oldRecord);

            // Re-enable activity logging before creating manual activity logs
            activity()->enableLogging();

            // Create activity logs for status transitions (including proper "created" event)
            $this->createActivityLogs($order, $oldRecord);

            $this->info("📝 Migrated order ID {$order->getKey()}: {$order->type} - {$order->status}");
        });
    }

    private function createOrderAgreement(Order $order, object $oldRecord): void
    {
        // Check if we have agreement data
        $hasAgreementData = ! empty($oldRecord->contract_number) ||
            ! empty($oldRecord->contract_value) ||
            ! empty($oldRecord->work_start_date);

        if (! $hasAgreementData) {
            return;
        }

        $agreementData = [
            'order_id' => $order->getKey(),
            'contract_number' => $oldRecord->contract_number ?? null,
            'total_contract_value' => ! empty($oldRecord->contract_value) ? (float) $oldRecord->contract_value : null,
            'start_date' => $this->parseDateTime($oldRecord->work_start_date),
            'created_by' => $order->created_by,
            'created_at' => $order->created_at,
            'updated_at' => $order->updated_at,
        ];

        OrderAgreement::create($agreementData);
        $this->info("📄 Created agreement record for order {$order->getKey()}");
    }

    private function createOrderCenters(Order $order, object $oldRecord): void
    {
        // Get center relationships from old database
        $oldDb = DB::connection($this->connection);
        $centerRelations = $oldDb->table('sek_request_centers')
            ->where('request_id', $oldRecord->id)
            ->get();

        if ($centerRelations->isEmpty()) {
            return;
        }

        $createdRelationsCount = 0;
        foreach ($centerRelations as $relation) {
            try {
                // Check if center exists in new database
                if (! DB::table('centers')->where('id', $relation->center_id)->exists()) {
                    $this->warn("⚠️  Center {$relation->center_id} not found for order {$order->getKey()}");

                    continue;
                }

                // Create the relationship in center_order pivot table
                $createdAt = $this->parseDateTime($relation->created_at) ?? $order->created_at;

                DB::table('center_order')->insert([
                    'center_id' => $relation->center_id,
                    'order_id' => $order->getKey(),
                    'created_at' => $createdAt,
                    'updated_at' => $createdAt,
                ]);

                $createdRelationsCount++;
            } catch (\Exception $e) {
                $this->error("❌ Error creating center relationship for order {$order->getKey()}: ".$e->getMessage());
            }
        }

        if ($createdRelationsCount > 0) {
            $this->info("🏢 Created {$createdRelationsCount} center relationships for order {$order->getKey()}");
        }
    }

    private function createActivityLogs(Order $order, object $oldRecord): void
    {
        $activities = $this->generateActivityLogs($order, $oldRecord);
        $createdActivitiesCount = 0;

        foreach ($activities as $activityData) {
            if (empty($activityData['causer_id']) || empty($activityData['created_at'])) {
                continue;
            }

            $causer = User::find($activityData['causer_id']);
            if (! $causer) {
                continue;
            }

            // Store the original timestamp temporarily
            $originalTimestamp = $activityData['created_at'];

            // Use the same activity logging pattern as Order model
            // The log name will be inherited from the Order model's getActivitylogOptions() method
            $activity = activity('order')  // Explicitly set log name to 'order'
                ->performedOn($order)  // This will use Order's getActivitylogOptions() for log name
                ->causedBy($causer)
                ->event($activityData['event'])
                ->withProperties($activityData['properties'])
                ->log($activityData['description']);

            // print activity log for order id 84
            if ($oldRecord->id == 84) {
                $this->info('Activity log for order ID 84: '.json_encode($activityData));
            }

            // Update the created_at timestamp to match the original datetime
            // Find the most recent activity log for this order and update its timestamp
            DB::table('activity_log')
                ->where('subject_type', 'App\\Models\\Order')
                ->where('subject_id', $order->getKey())
                ->where('description', $activityData['description'])
                ->where('event', $activityData['event'])
                ->where('causer_id', $activityData['causer_id'])
                ->orderBy('created_at', 'desc')
                ->limit(1)
                ->update([
                    'created_at' => $originalTimestamp,
                    'updated_at' => $originalTimestamp,
                ]);

            $createdActivitiesCount++;
        }

        if ($createdActivitiesCount > 0) {
            $this->info("📋 Created {$createdActivitiesCount} activity logs for order {$order->getKey()}");
        }
    }

    private function generateActivityLogs(Order $order, object $oldRecord): array
    {
        $activities = [];

        // Add the "created" event as the first activity log with correct timestamp and user
        $activities[] = [
            'description' => 'created',
            'causer_id' => $order->created_by,
            'properties' => [
                'attributes' => $order->toArray(),
            ],
            'event' => 'created',
            'created_at' => $order->created_at,
        ];

        // Map old status transitions to activity logs based on datetime fields
        // Important: Use 'approved' event type to match Order::getActivityForStatus() expectations
        $statusTransitions = [
            [
                'field' => 'sent_for_recommendation_datetime',
                'user_field' => 'sent_for_recommendation_by',
                'status' => Order::STATUS_RECOMMENDED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'added_recommendation_datetime',
                'user_field' => 'added_recommendation_by',
                'status' => Order::STATUS_TECHNICAL_FINANCIAL_REQUESTED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'ask_operational_approval_datetime',
                'user_field' => 'ask_operational_approval_by',
                'status' => Order::STATUS_TECHNICAL_FINANCIAL_APPROVED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'ask_operational_manger_approval_datetime',
                'user_field' => 'ask_operational_manger_approval_by',
                'status' => Order::STATUS_PROJECT_MANAGER_APPROVED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'ask_classification_approval_datetime',
                'user_field' => 'ask_classification_approval_by',
                'status' => Order::STATUS_CLASSIFICATION,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'classified_datetime',
                'user_field' => 'classified_by',
                'status' => Order::STATUS_AGREEMENT,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'project_data_updated_at',
                'user_field' => 'project_data_updated_by',
                'status' => Order::STATUS_CEO_APPROVED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'ceo_approval_datetime',
                'user_field' => 'ceo_approval_by',
                'status' => Order::STATUS_LAUNCHED,
                'description' => 'Order approved to next stage',
            ],
            [
                'field' => 'project_launched_at',
                'user_field' => 'project_launched_by',
                'status' => Order::STATUS_ACCEPTED,
                'description' => 'Order approved to next stage',
            ],
            // [
            //     'field' => 'accepted_datetime',
            //     'user_field' => 'accepted_by',
            //     'status' => Order::STATUS_ACCEPTED,
            //     'description' => 'Order approved to next stage'
            // ]
        ];

        foreach ($statusTransitions as $transition) {
            if (! empty($oldRecord->{$transition['field']}) && ! empty($oldRecord->{$transition['user_field']})) {
                $activities[] = [
                    'description' => 'Order approved to next stage', // Use consistent description like working DB
                    'causer_id' => $this->mapUserId($oldRecord->{$transition['user_field']}),
                    'properties' => [
                        'old_status' => $this->getPreviousStatus($transition['status']), // Previous status (what we're moving FROM)
                        'new_status' => $transition['status'], // Target status (what we're moving TO)
                        'action' => 'approved', // Always use 'approved' action
                        'notes' => '', // Add empty notes field like working DB
                    ],
                    'event' => 'approved', // This must be 'approved' to match Order::getActivityForStatus()
                    'created_at' => $this->parseDateTime($oldRecord->{$transition['field']}),
                ];
            }
        }

        // Handle rejection scenarios
        if (! empty($oldRecord->operation_reject_datetime) && ! empty($oldRecord->operation_reject_by)) {
            $activities[] = [
                'description' => 'Order rejected by operations',
                'causer_id' => $this->mapUserId($oldRecord->operation_reject_by),
                'properties' => [
                    'old_status' => $this->getPreviousStatus(Order::STATUS_REJECTED),
                    'new_status' => Order::STATUS_REJECTED,
                    'action' => 'rejected',
                    'notes' => $oldRecord->operation_rejection_reason ?? '',
                    'migrated_from_old_system' => true,
                ],
                'event' => 'rejected',
                'created_at' => $this->parseDateTime($oldRecord->operation_reject_datetime),
            ];
        }

        if (! empty($oldRecord->manger_reject_datetime) && ! empty($oldRecord->manger_reject_by)) {
            $activities[] = [
                'description' => 'Order rejected by manager',
                'causer_id' => $this->mapUserId($oldRecord->manger_reject_by),
                'properties' => [
                    'old_status' => $this->getPreviousStatus(Order::STATUS_REJECTED),
                    'new_status' => Order::STATUS_REJECTED,
                    'action' => 'rejected',
                    'notes' => 'Manager rejection',
                    'migrated_from_old_system' => true,
                ],
                'event' => 'rejected',
                'created_at' => $this->parseDateTime($oldRecord->manger_reject_datetime),
            ];
        }

        return $activities;
    }

    /**
     * Get the previous status for a given status (reverse of Order::getNextStatus)
     */
    private function getPreviousStatus(string $currentStatus): ?string
    {
        // Reverse mapping of Order::getNextStatus()
        $previousStatusMap = [
            Order::STATUS_PENDING_RECOMMENDATION => Order::STATUS_CREATED,
            Order::STATUS_RECOMMENDED => Order::STATUS_PENDING_RECOMMENDATION,
            Order::STATUS_TECHNICAL_FINANCIAL_REQUESTED => Order::STATUS_RECOMMENDED,
            Order::STATUS_TECHNICAL_FINANCIAL_APPROVED => Order::STATUS_TECHNICAL_FINANCIAL_REQUESTED,
            Order::STATUS_PROJECT_MANAGER_APPROVED => Order::STATUS_TECHNICAL_FINANCIAL_APPROVED,
            Order::STATUS_CLASSIFICATION => Order::STATUS_PROJECT_MANAGER_APPROVED,
            Order::STATUS_AGREEMENT => Order::STATUS_CLASSIFICATION,
            Order::STATUS_CEO_APPROVED => Order::STATUS_AGREEMENT,
            Order::STATUS_LAUNCHED => Order::STATUS_CEO_APPROVED,
            Order::STATUS_ACCEPTED => Order::STATUS_LAUNCHED,
            // Rejected can come from any status, so we'll default to null for now
            // In practice, we could track this better by analyzing which rejection field is set
            Order::STATUS_REJECTED => null,
        ];

        return $previousStatusMap[$currentStatus] ?? null;
    }

    protected function parseCoordinates(?string $coordinates): array
    {
        if (empty($coordinates)) {
            return ['lat' => null, 'lng' => null];
        }

        $parts = explode(',', $coordinates);
        if (count($parts) >= 2) {
            return [
                'lat' => (float) trim($parts[0]),
                'lng' => (float) trim($parts[1]),
            ];
        }

        return ['lat' => null, 'lng' => null];
    }

    private function mapOrderType(string $oldType): string
    {
        $typeMap = [
            'STATION' => Order::TYPE_STATION,
            'TANKER' => Order::TYPE_TANKER,
            'STORAGE_TANK' => Order::TYPE_DOMESTIC_STORAGE_TANK,
            'CENTRAL_STORAGE_TANK' => Order::TYPE_CENTRAL_STORAGE_TANK,
            'MAINTENANCE_STATION' => Order::TYPE_MAINTENANCE_STATION,
            'MAINTENANCE_TANKER' => Order::TYPE_MAINTENANCE_TANKER,
            'MICRO_NETWORK' => Order::TYPE_MICRO_NETWORK,
            'OTHER' => Order::TYPE_OTHER,
            'WELL_DRILLING' => Order::TYPE_WELL_DRILLING,
            'RAINWATER_STORAGE' => Order::TYPE_RAINWATER_STORAGE,
            'HOME_FILTERS' => Order::TYPE_HOME_FILTERS,
            'WATER_BOTTLES' => Order::TYPE_WATER_BOTTLES,
            'COOLERS' => Order::TYPE_COOLERS,
            'PRODUCTS_UNDER_REVIEW' => Order::TYPE_PRODUCTS_UNDER_REVIEW,
        ];

        return $typeMap[$oldType] ?? Order::TYPE_OTHER;
    }

    private function mapOrderStatus(string $oldStatus): string
    {
        $statusMap = [
            // Primary status mappings as specified
            'PENDING' => Order::STATUS_PENDING_RECOMMENDATION,
            'AWAITING_RECOMMENDATION' => Order::STATUS_RECOMMENDED,
            'APPROVED' => Order::STATUS_TECHNICAL_FINANCIAL_REQUESTED,
            'AWAITING_OPERATIONAL_APPROVAL' => Order::STATUS_TECHNICAL_FINANCIAL_APPROVED,
            'AWAITING_OPERATIONAL_MANGER_APPROVAL' => Order::STATUS_PROJECT_MANAGER_APPROVED,
            'AWAITING_CLASSIFICATION_APPROVAL' => Order::STATUS_CLASSIFICATION,
            'ACCEPTED_AWAITING_PROJECT_DATA' => Order::STATUS_AGREEMENT,
            'ACCEPTED_AWAITING_PROJECT_LAUNCH' => Order::STATUS_CEO_APPROVED,
            'ACCEPTED_AWAITING_CEO_APPROVAL' => Order::STATUS_LAUNCHED,
            'ACCEPTED_PROJECTS_LAUNCHED' => Order::STATUS_ACCEPTED,
            'ACCEPTED' => Order::STATUS_ACCEPTED,
            'REJECTED' => Order::STATUS_REJECTED,

            // Additional old status mappings
            'DRAFT' => Order::STATUS_PENDING_RECOMMENDATION,
            'AWAITING_DECISION' => Order::STATUS_RECOMMENDED,
            'REJECTED_BY_MANGER' => Order::STATUS_REJECTED,
            'REJECTED_BY_OPERATION' => Order::STATUS_REJECTED,

            // Legacy mappings (in case they still exist)
            'CREATED' => Order::STATUS_PENDING_RECOMMENDATION,
            'PENDING_RECOMMENDATION' => Order::STATUS_PENDING_RECOMMENDATION,
            'RECOMMENDED' => Order::STATUS_RECOMMENDED,
            'TECHNICAL_FINANCIAL_REQUESTED' => Order::STATUS_TECHNICAL_FINANCIAL_REQUESTED,
            'TECHNICAL_FINANCIAL_APPROVED' => Order::STATUS_TECHNICAL_FINANCIAL_APPROVED,
            'PROJECT_MANAGER_APPROVED' => Order::STATUS_PROJECT_MANAGER_APPROVED,
            'CLASSIFICATION' => Order::STATUS_CLASSIFICATION,
            'AGREEMENT' => Order::STATUS_AGREEMENT,
            'CEO_APPROVED' => Order::STATUS_CEO_APPROVED,
            'LAUNCHED' => Order::STATUS_LAUNCHED,
        ];

        $mappedStatus = $statusMap[$oldStatus] ?? Order::STATUS_PENDING_RECOMMENDATION;

        // Log the mapping for debugging
        if (! isset($statusMap[$oldStatus]) && ! empty($oldStatus)) {
            $this->warn("⚠️  Unknown status '{$oldStatus}' mapped to CREATED");
        }

        return $mappedStatus;
    }

    private function mapUserId(?int $oldUserId): ?int
    {
        if (empty($oldUserId)) {
            return null;
        }

        $user = User::find($oldUserId);

        return $user ? $user->getKey() : null;
    }

    protected function parseDateTime($dateTime): ?Carbon
    {
        if (empty($dateTime)) {
            return null;
        }

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