<?php

namespace App\Console\Commands\Migrations;

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

class OrderRequirementFilesMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_requirement_files';

    protected string $modelName = 'Order Requirement Files';

    protected array $tempRequirementData = [];

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

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

    protected function processData($oldDb, bool $dryRun, int $startFrom, int $batchSize): void
    {
        // Get requirement files with their associated file details and requirement info
        $query = $oldDb->table($this->oldTableName.' as rf')
            ->join('sek_files as f', 'rf.file_id', '=', 'f.id')
            ->join('sek_requirements as r', 'rf.requirement_id', '=', 'r.id')
            ->whereNotNull('rf.requirement_id')
            ->whereNotNull('rf.file_id')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->select(
                'rf.id as requirement_file_id',
                'rf.requirement_id',
                'rf.user_id',
                'f.file_name',
                'f.original_file_name',
                'f.mimetype',
                'r.requirement_name',
                'r.request_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_requirements as r', 'rf.requirement_id', '=', 'r.id')
            ->whereNotNull('rf.requirement_id')
            ->whereNotNull('rf.file_id')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->count();

        $this->stats['total'] = $totalRecords;

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

        if ($totalRecords === 0) {
            $this->info('✅ No requirement 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();

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

                    if ($this->validateData($data)) {
                        if (! $dryRun) {
                            $this->createModel($data);
                        }
                        $this->stats['migrated']++;
                    } else {
                        $this->stats['skipped']++;
                        // Validation already logs the reason for skipping
                    }
                } catch (\Exception $e) {
                    $this->stats['errors']++;
                    $this->error("❌ Error migrating requirement file {$oldRecord->requirement_file_id}: {$e->getMessage()}");
                }
            }
        });

        $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, '/');
        }

        return [
            'requirement_file_id' => $oldRecord->requirement_file_id,
            'old_requirement_id' => $oldRecord->requirement_id,
            'old_user_id' => $oldRecord->user_id,
            'old_request_id' => $oldRecord->request_id,
            'file_path' => $filePath,
            'original_file_name' => $oldRecord->original_file_name ?: basename($oldRecord->file_name),
            'file_name' => $oldRecord->file_name,
            'mime_type' => $oldRecord->mimetype,
            'requirement_name' => $oldRecord->requirement_name,
        ];
    }

    protected function validateData(array $data): bool
    {
        // Find the corresponding OrderRequirement by using the old requirement ID
        // Since order requirements are migrated with their original IDs, we can look them up directly
        $requirement = $this->findOrderRequirementByOldId(
            $data['old_requirement_id'],
            $data['old_request_id'],
            $data['requirement_name']
        );

        if (! $requirement) {
            $this->warn("⚠️  Could not find OrderRequirement for old requirement_id: {$data['old_requirement_id']}, request_id: {$data['old_request_id']}, name: '{$data['requirement_name']}'");

            return false;
        }

        // Check if requirement already has a file
        if ($requirement->hasRequirementFile()) {
            $this->warn("⚠️  Requirement {$requirement->id} already has a file, skipping");

            return false;
        }

        // 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 requirement back to data for use in createModel
        $this->tempRequirementData[$data['requirement_file_id']] = $requirement;

        return true;
    }

    /**
     * Find OrderRequirement by matching the old data
     * This uses a more reliable approach by looking for the requirement that matches
     * the old requirement and order relationship
     */
    protected function findOrderRequirementByOldId(int $oldRequirementId, int $oldRequestId, string $requirementName): ?OrderRequirement
    {
        // Method 1: Try direct ID matching first (if requirements preserved their IDs during migration)
        $requirement = OrderRequirement::where('id', $oldRequirementId)
            ->whereHas('order', function ($query) use ($oldRequestId) {
                $query->where('id', $oldRequestId);
            })->first();

        if ($requirement) {
            return $requirement;
        }

        // Method 2: Find by order ID and requirement name (most reliable method)
        $requirement = OrderRequirement::where('order_id', $oldRequestId)
            ->where('name', 'LIKE', '%'.trim($requirementName).'%')
            ->first();

        if ($requirement) {
            return $requirement;
        }

        // Method 3: Find by exact requirement name within any order with the old request ID
        $requirement = OrderRequirement::where('order_id', $oldRequestId)
            ->where('name', $requirementName)
            ->first();

        if ($requirement) {
            return $requirement;
        }

        // Method 4: If there's only one requirement for this order, use it (last resort)
        $requirements = OrderRequirement::where('order_id', $oldRequestId)->get();
        if ($requirements->count() === 1) {
            return $requirements->first();
        }

        return null;
    }

    protected function createModel(array $data): void
    {
        /** @var OrderRequirement $requirement */
        $requirement = $this->tempRequirementData[$data['requirement_file_id']] ?? null;

        if (! $requirement) {
            throw new \Exception("OrderRequirement not found for requirement_file_id: {$data['requirement_file_id']}");
        }

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

            // Determine filename
            $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',
                    'txt' => 'text/plain',
                    'csv' => 'text/csv',
                    'mp4' => 'video/mp4',
                    'mov' => 'video/quicktime',
                    'mp3' => 'audio/mpeg',
                    'wav' => 'audio/wav',
                    'ogg' => 'audio/ogg',
                    default => 'application/octet-stream'
                };
            }

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

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

            file_put_contents($tempLocalPath, $fileContent);

            // Add media to requirement_file collection
            $media = $requirement->addMedia($tempLocalPath)
                ->usingName($fileName)
                ->usingFileName($fileName)
                ->withCustomProperties([
                    'original_file_name' => $data['original_file_name'],
                    'original_file_path' => $data['file_name'],
                    'requirement_name' => $data['requirement_name'],
                    'old_requirement_id' => $data['old_requirement_id'],
                    'old_user_id' => $data['old_user_id'],
                    'old_request_id' => $data['old_request_id'],
                    'requirement_file_id' => $data['requirement_file_id'],
                    'migrated_from_old_system' => true,
                    'migration_date' => now()->toISOString(),
                ])
                ->toMediaCollection('requirement_file');

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

            $this->info("📄 Migrated requirement file for requirement {$requirement->id}: {$fileName}");

            // Log the successful migration
            Log::info('Order requirement file migrated', [
                'requirement_id' => $requirement->id,
                'order_id' => $requirement->order_id,
                'original_file_name' => $data['original_file_name'],
                'new_media_id' => $media->id,
                'file_name' => $fileName,
                'mime_type' => $mimeType,
                'old_requirement_id' => $data['old_requirement_id'],
                'old_user_id' => $data['old_user_id'],
                'requirement_file_id' => $data['requirement_file_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 requirement file: {$e->getMessage()}");
        } finally {
            // Clean up temporary requirement data
            unset($this->tempRequirementData[$data['requirement_file_id']]);
        }
    }
}
