<?php

namespace App\Console\Commands\Migrations;

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

class AssociationFilesMigration extends BaseMigration
{
    protected string $oldTableName = 'sek_user_files';

    protected string $modelName = 'Association Files';

    protected array $tempAssociationData = [];

    // Map old file types to new media collection names
    protected array $fileTypeMapping = [
        'AUTHORIZATION' => 'authorization_letter',
        'LICENSE' => 'license_certificate',
        'IBAN_CERTIFICATE' => 'iban_certificate',
        'NATIONAL_ADDRESS' => 'national_address_certificate',
        'GOVERNMENT_CERTIFICATE' => 'governance_certificate',
        'FINANCIAL_DOCUMENT' => 'financial_reports',
    ];

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

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

    protected function processData($oldDb, bool $dryRun, int $startFrom, int $batchSize): void
    {
        // Get user files with their associated file details AND user's association
        $query = $oldDb->table($this->oldTableName.' as uf')
            ->join('sek_files as f', 'uf.file_id', '=', 'f.id')
            ->join('sek_users as u', 'uf.user_id', '=', 'u.id')
            ->whereNotNull('uf.user_id')
            ->whereNotNull('u.association_id') // User must have an association
            ->whereNotNull('uf.type')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->whereIn('uf.type', array_keys($this->fileTypeMapping))
            ->select(
                'uf.user_id',
                'u.association_id', // Get the actual association ID from user
                'uf.type',
                'f.file_name',
                'f.original_file_name',
                'f.mimetype',
                'uf.id as user_file_id',
                'u.full_name',
                'u.first_name_ar',
                'u.first_name_en',
                'u.last_name_ar',
                'u.last_name_en'
            )
            ->orderBy('uf.id')
            ->offset($startFrom);

        $totalRecords = $oldDb->table($this->oldTableName.' as uf')
            ->join('sek_files as f', 'uf.file_id', '=', 'f.id')
            ->join('sek_users as u', 'uf.user_id', '=', 'u.id')
            ->whereNotNull('uf.user_id')
            ->whereNotNull('u.association_id')
            ->whereNotNull('uf.type')
            ->whereNotNull('f.file_name')
            ->where('f.file_name', '!=', '')
            ->whereIn('uf.type', array_keys($this->fileTypeMapping))
            ->count();

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

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

        if ($totalRecords === 0) {
            $this->info('✅ No association 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 association file {$oldRecord->user_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, '/');
        }

        // Map the file type to collection name
        $collection = $this->fileTypeMapping[$oldRecord->type] ?? 'documents';

        return [
            'user_file_id' => $oldRecord->user_file_id,
            'old_user_id' => $oldRecord->user_id,
            'association_id' => $oldRecord->association_id, // Now using the correct association ID
            'file_path' => $filePath,
            'original_file_name' => $oldRecord->original_file_name,
            'file_name' => $oldRecord->file_name,
            'mime_type' => $oldRecord->mimetype,
            'file_type' => $oldRecord->type,
            'collection' => $collection,
            'user_name' => $this->buildUserName($oldRecord),
        ];
    }

    /**
     * Build user name from available name fields
     */
    protected function buildUserName(object $oldRecord): string
    {
        // First try the full_name field if it exists and is not empty
        if (! empty($oldRecord->full_name)) {
            return trim($oldRecord->full_name);
        }

        // Fallback to building name from individual fields
        // Prefer Arabic names if available, otherwise use English
        $firstName = $oldRecord->first_name_ar ?: $oldRecord->first_name_en ?: '';
        $lastName = $oldRecord->last_name_ar ?: $oldRecord->last_name_en ?: '';

        $fullName = trim($firstName.' '.$lastName);

        // If we still don't have a name, return a default
        return $fullName ?: 'Unknown User';
    }

    protected function validateData(array $data): bool
    {
        // Find the association using proper lookup
        $association = $this->findAssociationById($data['association_id']);

        if (! $association) {
            $this->warn("⚠️  Could not find Association for association_id: {$data['association_id']}, user_id: {$data['old_user_id']}");

            return false;
        }

        // Check if association already has this type of file
        if ($this->associationHasRecentFile($association, $data['collection'])) {
            $this->warn("⚠️  Association {$association->id} has a recent {$data['collection']} file (after Aug 1st), skipping");

            return false;
        }

        // Check for corrupted paths with special characters that cause issues
        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 - they're common
            return false;
        }

        // Store association back to data for use in createModel
        $this->tempAssociationData[$data['user_file_id']] = $association;

        return true;
    }

    /**
     * Find Association by ID with caching to avoid repeated lookups
     */
    protected function findAssociationById(int $associationId): ?Association
    {
        static $associationCache = [];

        if (isset($associationCache[$associationId])) {
            return $associationCache[$associationId];
        }

        $association = Association::find($associationId);
        $associationCache[$associationId] = $association;

        return $association;
    }

    /**
     * Check if association has a recent file of this type (after August 1st, 2025)
     */
    protected function associationHasRecentFile(Association $association, string $collection): bool
    {
        $cutoffDate = \Carbon\Carbon::create(2025, 8, 9, 0, 0, 0);

        $recentFiles = $association->getMedia($collection)
            ->filter(function ($media) use ($cutoffDate) {
                return $media->created_at->gte($cutoffDate);
            });

        return $recentFiles->isNotEmpty();
    }

    protected function createModel(array $data): void
    {
        /** @var Association $association */
        $association = $this->tempAssociationData[$data['user_file_id']] ?? null;

        if (! $association) {
            throw new \Exception("Association not found for user_file_id: {$data['user_file_id']}");
        }

        try {
            // Clear only old files of this type (before August 1st, 2025) before adding new one
            $cutoffDate = \Carbon\Carbon::create(2025, 8, 1, 0, 0, 0);
            $existingMedia = $association->getMedia($data['collection']);
            $oldFilesToDelete = $existingMedia->filter(function ($media) use ($cutoffDate) {
                return $media->created_at->lt($cutoffDate);
            });

            if ($oldFilesToDelete->isNotEmpty()) {
                foreach ($oldFilesToDelete as $media) {
                    $media->delete();
                }
                $this->info("🗑️  Cleared {$oldFilesToDelete->count()} old {$data['collection']} file(s) for association {$data['association_id']} (before Aug 1st)");
            }

            // 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',
                    '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/association_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 appropriate collection
            $media = $association->addMedia($tempLocalPath)
                ->usingName($fileName)
                ->usingFileName($fileName)
                ->withCustomProperties([
                    'original_file_name' => $data['original_file_name'],
                    'original_file_path' => $data['file_name'],
                    'file_type' => $data['file_type'],
                    'old_user_id' => $data['old_user_id'],
                    'user_name' => $data['user_name'],
                    'user_file_id' => $data['user_file_id'],
                    'migrated_from_old_system' => true,
                    'migration_date' => now()->toISOString(),
                ])
                ->toMediaCollection($data['collection']);

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

            $this->info("📄 Migrated {$data['collection']} for association {$data['association_id']} (from user: {$data['user_name']}): {$fileName}");

            // Log the successful migration
            Log::info('Association file migrated', [
                'association_id' => $data['association_id'],
                'collection' => $data['collection'],
                'file_type' => $data['file_type'],
                'original_file_name' => $data['original_file_name'],
                'new_media_id' => $media->id,
                'file_name' => $fileName,
                'mime_type' => $mimeType,
                'old_user_id' => $data['old_user_id'],
                'user_name' => $data['user_name'],
                'user_file_id' => $data['user_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 association file: {$e->getMessage()}");
        } finally {
            // Clean up temporary association data
            unset($this->tempAssociationData[$data['user_file_id']]);
        }
    }
}
