<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\RecordAttachment;
use Illuminate\Support\Facades\Storage;

class FixAttachmentPaths extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'attachments:fix-paths {--dry-run : Show what would be fixed without actually fixing} {--sql-file= : Path to SQL backup file to use as reference}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Fix attachment file paths by matching database records with actual files in uploads directory';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $isDryRun = $this->option('dry-run');
        $sqlFile = $this->option('sql-file');

        $this->info('Scanning for mismatched attachment file paths...');

        // Get all files in uploads directory
        $uploadsPath = storage_path('app/public/uploads');
        $actualFiles = [];

        if (is_dir($uploadsPath)) {
            $files = scandir($uploadsPath);
            foreach ($files as $file) {
                if ($file !== '.' && $file !== '..' && is_file($uploadsPath . '/' . $file)) {
                    // Extract the original filename (after the timestamp prefix)
                    if (preg_match('/^\d+_(.+)$/', $file, $matches)) {
                        $originalName = $matches[1];
                        $actualFiles[$originalName] = $file;
                    } else {
                        // Also store files without timestamp prefix
                        $actualFiles[$file] = $file;
                    }
                }
            }
        }

        $this->info('Found ' . count($actualFiles) . ' files in uploads directory.');

        // If SQL file is provided, use it as reference
        $sqlFileData = [];
        if ($sqlFile && file_exists($sqlFile)) {
            $this->info('Reading SQL file: ' . $sqlFile);
            $sqlFileData = $this->parseSqlFile($sqlFile);
            $this->info('Found ' . count($sqlFileData) . ' attachment records in SQL file.');
        }

        // Get all attachment records
        $attachments = RecordAttachment::all();
        $fixedCount = 0;
        $notFoundCount = 0;

        foreach ($attachments as $attachment) {
            $currentPath = $attachment->file_path;
            $currentFullPath = storage_path('app/public/uploads/' . $currentPath);

            // If file exists at current path, skip
            if (file_exists($currentFullPath)) {
                continue;
            }

            $foundMatch = false;
            $correctPath = '';

            // First, try to find exact match by original filename
            $originalName = '';
            if (preg_match('/^\d+_(.+)$/', $currentPath, $matches)) {
                $originalName = $matches[1];
            } else {
                $originalName = $currentPath;
            }

            if (isset($actualFiles[$originalName])) {
                $correctPath = $actualFiles[$originalName];
                $foundMatch = true;
            }

            // If SQL file data is available and no match found, try to find similar files
            if (!$foundMatch && !empty($sqlFileData)) {
                $sqlRecord = null;
                foreach ($sqlFileData as $sqlItem) {
                    if ($sqlItem['id'] == $attachment->id) {
                        $sqlRecord = $sqlItem;
                        break;
                    }
                }

                if ($sqlRecord) {
                    // Extract original filename from SQL file path
                    $sqlOriginalName = '';
                    if (preg_match('/^\d+_(.+)$/', $sqlRecord['file_path'], $matches)) {
                        $sqlOriginalName = $matches[1];
                    } else {
                        $sqlOriginalName = $sqlRecord['file_path'];
                    }

                    // Try to find file with same base name but different timestamp
                    if (isset($actualFiles[$sqlOriginalName])) {
                        $correctPath = $actualFiles[$sqlOriginalName];
                        $foundMatch = true;
                    }
                }
            }

            if ($foundMatch) {
                $this->line("Found match for: {$attachment->file_name}");
                $this->line("  Database path: {$currentPath}");
                $this->line("  Actual file:   {$correctPath}");

                if (!$isDryRun) {
                    $attachment->file_path = $correctPath;
                    $attachment->save();
                    $this->info("  ✓ Fixed");
                } else {
                    $this->info("  → Would fix");
                }

                $fixedCount++;
            } else {
                $this->warn("No matching file found for: {$attachment->file_name} (Original: {$originalName})");
                $notFoundCount++;
            }
        }

        $this->info("\nSummary:");
        $this->info("Fixed: {$fixedCount}");
        $this->info("Not found: {$notFoundCount}");

        if ($isDryRun && $fixedCount > 0) {
            $this->info("\nRun without --dry-run to apply these fixes.");
        }
    }

    private function parseSqlFile($sqlFile)
    {
        $sqlData = [];
        $content = file_get_contents($sqlFile);

        // Find the INSERT statement for record_attachments
        if (preg_match('/INSERT INTO `record_attachments`.*?VALUES\s*(.*?);/s', $content, $matches)) {
            $valuesString = $matches[1];

            // Parse each record
            if (preg_match_all('/\((\d+),\s*(\d+),\s*\'([^\']+)\',\s*\'([^\']+)\',\s*\'[^\']+\'\)/', $valuesString, $recordMatches, PREG_SET_ORDER)) {
                foreach ($recordMatches as $match) {
                    $id = $match[1];
                    $recordId = $match[2];
                    $fileName = $match[3];
                    $filePath = $match[4];

                    $sqlData[$id] = [
                        'id' => $id,
                        'record_id' => $recordId,
                        'file_name' => $fileName,
                        'file_path' => $filePath
                    ];
                }
            }
        }

        return $sqlData;
    }
}
