<?php

namespace App\Console\Commands\Migrations;

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

class OrderFilesMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_request_files';

    protected string $modelName = 'Order Files';

    // Map old file types to new file requirement keys (lowercase)
    protected array $fileTypeMapping = [
        'OWNERSHIP_OR_LEASE_AGREEMENT' => 'ownership_or_lease_agreement',
        'WELL_DISCLOSURE' => 'well_disclosure',
        'WATER_SAMPLE_ANALYSIS' => 'water_sample_analysis',
        'PROJECT_LAND_BUILDINGS_PLAN' => 'project_land_buildings_plan',
        'GROUNDWATER_USAGE_LICENSE' => 'groundwater_usage_license',
        'COMMERCIAL_REGISTRATION' => 'commercial_registration',
        'HOME_TANK_CAPACITY' => 'home_tank_capacity',
        'CENTRAL_TANK_CAPACITY' => 'central_tank_capacity',
        'STATION_TECHNICAL_CONTRACT' => 'station_technical_contract',
        'PRODUCED_WATER_ANALYSIS' => 'produced_water_analysis',
        'TANKER_DRIVER_CONTRACT' => 'tanker_driver_contract',
        'VEHICLE_REGISTRATION' => 'vehicle_registration',
        'VEHICLE_INSURANCE' => 'vehicle_insurance',
        'COMPLETION_REPORTS' => 'completion_reports',
        'CONTRACT_ATTACHMENT' => 'contract_attachment',
        'ACCEPTANCE_CRITERIA_FOR_MICRO_TREATMENT_PLANT' => 'acceptance_criteria_for_micro_treatment_plant',
        'OPERATIONAL_REPORT' => 'operational_report',
        'WELL_AND_TANK_LAND_CONCESSION' => 'well_and_tank_land_concession',
        'WELL_WATER_ANALYSIS' => 'well_water_analysis',
        'NETWORK_LOCATION_MAP' => 'network_location_map',
        'PROJECT_FEASIBILITY_STUDY' => 'project_feasibility_study',
    ];

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

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

    protected function processData($oldDb, bool $dryRun, int $startFrom, int $batchSize): void
    {
        // Get request files with their associated file details and map to orders
        $query = $oldDb->table($this->oldTableName.' as rf')
            ->join('sek_files as f', 'rf.file_id', '=', 'f.id')
            ->join('sek_requests as r', 'rf.request_id', '=', 'r.id')
            ->whereNotNull('rf.request_id')
            ->whereNotNull('rf.file_type')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->whereIn('rf.file_type', array_keys($this->fileTypeMapping))
            ->select(
                'rf.id as request_file_id',
                'rf.request_id',
                'rf.file_type',
                'rf.filename as request_filename',
                'f.file_name',
                'f.original_file_name',
                'f.mimetype',
                'r.association_id'
            )
            ->orderBy('rf.id')
            ->offset($startFrom);

        $totalRecords = $oldDb->table($this->oldTableName.' as rf')
            ->join('sek_files as f', 'rf.file_id', '=', 'f.id')
            ->join('sek_requests as r', 'rf.request_id', '=', 'r.id')
            ->whereNotNull('rf.request_id')
            ->whereNotNull('rf.file_type')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->whereIn('rf.file_type', array_keys($this->fileTypeMapping))
            ->count();

        $this->info("📊 Found {$totalRecords} order files to migrate");

        if ($totalRecords === 0) {
            $this->info('✅ No order files 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']++;
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error migrating {$data['file_type']} for order {$data['order_id']}: {$e->getMessage()}");
                    }
                } else {
                    $this->stats['skipped']++;
                    // Silent skip for missing orders/files to reduce noise
                }
            }
        });

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

    protected function mapData(object $oldRecord): array
    {
        // Add the /sekaya/ prefix to the file path for MinIO storage
        $filePath = $oldRecord->file_name;
        if (! str_starts_with($filePath, '/sekaya/')) {
            $filePath = '/sekaya/'.ltrim($filePath, '/');
        }

        // Map the file type to requirement key
        $fileTypeKey = $this->fileTypeMapping[$oldRecord->file_type] ?? strtolower($oldRecord->file_type);

        return [
            'request_id' => $oldRecord->request_id,
            'order_id' => null, // Will be determined in validation
            'file_path' => $filePath,
            'original_file_name' => $oldRecord->original_file_name ?: $oldRecord->request_filename,
            'file_name' => $oldRecord->file_name,
            'mime_type' => $oldRecord->mimetype,
            'file_type' => $oldRecord->file_type,
            'file_type_key' => $fileTypeKey,
            'request_file_id' => $oldRecord->request_file_id,
            'association_id' => $oldRecord->association_id,
        ];
    }

    protected function validateData(array $data): bool
    {
        // Find the corresponding order by looking up the request_id mapping
        // Assuming there's a mapping table or we need to find orders by association and other criteria
        $order = $this->findOrderByRequestId($data['request_id'], $data['association_id']);

        if (! $order) {
            // Silent skip - many old requests might not have corresponding orders
            return false;
        }

        $data['order_id'] = $order->id;

        // Check for corrupted paths with special characters
        if (preg_match('/[\x{200E}\x{200F}\x{202A}-\x{202E}\x{2066}-\x{2069}]/u', $data['file_path'])) {
            $this->warn("⚠️  Skipping file with corrupted path characters: {$data['file_path']}");

            return false;
        }

        // Check if file exists in storage
        if (! Storage::disk('s3')->exists($data['file_path'])) {
            // Silent skip for missing files to reduce noise
            return false;
        }

        // Store order_id back to data for use in createModel
        $this->tempOrderData[$data['request_file_id']] = $order;

        return true;
    }

    protected array $tempOrderData = [];

    /**
     * Find order by request ID and association ID
     * This assumes that orders were migrated with a mapping to old request IDs
     */
    protected function findOrderByRequestId(int $requestId, int $associationId): ?Order
    {
        // First try to find by exact request mapping if it exists
        // This would require the order migration to have stored the old request_id mapping

        // For now, let's try to find orders by association_id
        // In a real migration, you'd have a more specific mapping
        $orders = Order::where('association_id', $associationId)->get();

        // If there's only one order for this association, use it
        if ($orders->count() === 1) {
            return $orders->first();
        }

        // If multiple orders, we might need additional logic to match
        // For now, return the first one or null if none found
        return $orders->first();
    }

    protected function createModel(array $data): void
    {
        $order = $this->tempOrderData[$data['request_file_id']] ?? null;

        if (! $order) {
            throw new \Exception("Order not found for request file {$data['request_file_id']}");
        }

        try {
            // Get file content from S3
            $fileContent = Storage::disk('s3')->get($data['file_path']);

            if (! $fileContent) {
                throw new \Exception('Could not read file content from S3');
            }

            // Use original file name if available, otherwise use file name
            $fileName = $data['original_file_name'] ?: basename($data['file_path']);

            // Determine mime type - use from database if available, otherwise detect from extension
            $mimeType = $data['mime_type'];
            if (! $mimeType) {
                $extension = pathinfo($fileName, PATHINFO_EXTENSION);
                $mimeType = match (strtolower($extension)) {
                    'pdf' => 'application/pdf',
                    'doc' => 'application/msword',
                    'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
                    'jpg', 'jpeg' => 'image/jpeg',
                    'png' => 'image/png',
                    'gif' => 'image/gif',
                    'xls' => 'application/vnd.ms-excel',
                    'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                    default => 'application/octet-stream'
                };
            }

            // Create a temporary local file
            $tempLocalPath = storage_path('app/tmp/order_file_migration_'.uniqid().'_'.$fileName);

            // Ensure the tmp directory exists
            if (! file_exists(dirname($tempLocalPath))) {
                mkdir(dirname($tempLocalPath), 0755, true);
            }

            file_put_contents($tempLocalPath, $fileContent);

            $translated = __('order.file_requirements.'.$data['file_type_key'], [], 'ar');

            // Add media to order_files collection with the specific file type key as placeholder
            $media = $order->addMedia($tempLocalPath)
                ->usingName($fileName)
                ->usingFileName($fileName)
                ->withCustomProperties([
                    'title' => $fileName,
                    'placeholder' => $data['file_type_key'], // This maps to the getTypeFileRequirements keys
                    'original_file_name' => $data['original_file_name'],
                    'original_file_path' => $data['file_name'],
                    'file_title' => $translated,
                    'file_type' => $data['file_type'],
                    'request_file_id' => $data['request_file_id'],
                    'request_id' => $data['request_id'],
                    'migrated_from_old_system' => true,
                    'migration_date' => now()->toISOString(),
                ])
                ->toMediaCollection('order_files');

            // Clean up temporary file
            if (file_exists($tempLocalPath)) {
                unlink($tempLocalPath);
            }

            $this->info("📄 Migrated {$data['file_type_key']} for order {$order->id}: {$fileName}");

            // Log the successful migration
            Log::info('Order file migrated', [
                'order_id' => $order->id,
                'file_type' => $data['file_type'],
                'file_type_key' => $data['file_type_key'],
                'original_file_name' => $data['original_file_name'],
                'new_media_id' => $media->id,
                'file_name' => $fileName,
                'mime_type' => $mimeType,
                'request_file_id' => $data['request_file_id'],
                'request_id' => $data['request_id'],
            ]);
        } catch (\Exception $e) {
            // Clean up temporary file if it exists
            if (isset($tempLocalPath) && file_exists($tempLocalPath)) {
                unlink($tempLocalPath);
            }

            throw new \Exception("Failed to migrate order file: {$e->getMessage()}");
        } finally {
            // Clean up temp data
            unset($this->tempOrderData[$data['request_file_id']]);
        }
    }
}
