<?php

namespace App\Console\Commands;

use App\Models\Association;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;

class AnalyzeAssociationsFromCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:analyze-associations-csv {csv_file : Path to the CSV file} {--output= : Output CSV file path (optional)}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Analyze association names from CSV file and output CSV with associations found/not found in system';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $csvFile = $this->argument('csv_file');
        $outputFile = $this->option('output') ?? 'associations_analysis_'.now()->format('Y-m-d_H-i-s').'.csv';

        // Check if file exists
        if (! File::exists($csvFile)) {
            $this->error("CSV file not found: {$csvFile}");

            return Command::FAILURE;
        }

        $this->info("Starting analysis from: {$csvFile}");

        try {
            // Read and process CSV
            $data = $this->readCsvFile($csvFile);

            if (empty($data)) {
                $this->error('No data found in CSV file or invalid format');

                return Command::FAILURE;
            }

            $this->info('Found '.count($data).' records in CSV');

            // Process associations
            $result = $this->analyzeAssociations($data);

            // Write output CSV
            $this->writeCsvOutput($result, $outputFile);

            $this->info('Analysis completed successfully!');
            $this->info("Output saved to: {$outputFile}");
            $this->displaySummary($result);

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('Error processing CSV: '.$e->getMessage());

            return Command::FAILURE;
        }
    }

    /**
     * Read CSV file and return data array
     */
    private function readCsvFile($filePath)
    {
        $data = [];
        $file = fopen($filePath, 'r');

        if (! $file) {
            throw new \Exception('Cannot open CSV file');
        }

        // Read header line
        $header = fgetcsv($file, 0, ',');

        if (! $header) {
            fclose($file);
            throw new \Exception('Invalid CSV header format');
        }

        // Find the association name column index
        $associationNameIndex = null;
        foreach ($header as $index => $columnName) {
            if (trim($columnName) === 'اسم الجهة') {
                $associationNameIndex = $index;
                break;
            }
        }

        if ($associationNameIndex === null) {
            fclose($file);
            throw new \Exception('Column "اسم الجهة" not found in CSV header');
        }

        $this->info("Found association name column at index: {$associationNameIndex}");

        // Read data lines
        while (($row = fgetcsv($file, 0, ',')) !== false) {
            if (isset($row[$associationNameIndex]) && ! empty(trim($row[$associationNameIndex]))) {
                $data[] = [
                    'id' => $row[0] ?? '',
                    'region' => $row[1] ?? '',
                    'center' => $row[2] ?? '',
                    'association_name' => trim($row[$associationNameIndex]),
                    'approval_date' => $row[4] ?? '',
                    'project_start_date' => $row[5] ?? '',
                    'closure_date' => $row[6] ?? '',
                    'beneficiary_families' => $row[7] ?? '',
                    'beneficiaries' => $row[8] ?? '',
                    'approved_project' => $row[9] ?? '',
                    'donor' => $row[10] ?? '',
                    'status' => $row[11] ?? '',
                    'count' => $row[12] ?? '',
                    'capacity' => $row[13] ?? '',
                    'project_value' => $row[14] ?? '',
                    'project_value_million' => $row[15] ?? '',
                ];
            }
        }

        fclose($file);

        return $data;
    }

    /**
     * Analyze associations and categorize them
     */
    private function analyzeAssociations($data)
    {
        $this->info('Analyzing associations...');

        // Get unique association names from the data
        $uniqueAssociations = collect($data)
            ->pluck('association_name')
            ->filter()
            ->unique()
            ->values()
            ->sort();

        $this->info('Found '.count($uniqueAssociations).' unique association names');

        // Get all associations from the database
        $systemAssociations = Association::all()->pluck('name')->map(function ($name) {
            return trim($name);
        });

        $foundInSystem = [];
        $notFoundInSystem = [];

        foreach ($uniqueAssociations as $associationName) {
            $trimmedName = trim($associationName);

            // Check if association exists in system (case-insensitive)
            $found = $systemAssociations->contains(function ($systemName) use ($trimmedName) {
                return mb_strtolower(trim($systemName)) === mb_strtolower($trimmedName);
            });

            if ($found) {
                $foundInSystem[] = $trimmedName;
            } else {
                $notFoundInSystem[] = $trimmedName;
            }
        }

        return [
            'found_in_system' => $foundInSystem,
            'not_found_in_system' => $notFoundInSystem,
            'total_unique' => count($uniqueAssociations),
            'found_count' => count($foundInSystem),
            'not_found_count' => count($notFoundInSystem),
        ];
    }

    /**
     * Write CSV output file
     */
    private function writeCsvOutput($result, $outputFile)
    {
        $file = fopen($outputFile, 'w');

        if (! $file) {
            throw new \Exception('Cannot create output CSV file');
        }

        // Write UTF-8 BOM for proper Arabic display in Excel
        fwrite($file, "\xEF\xBB\xBF");

        // Write headers
        fputcsv($file, ['Association Name', 'Status', 'Category']);

        // Write associations found in system
        foreach ($result['found_in_system'] as $association) {
            fputcsv($file, [$association, 'Found', 'In System']);
        }

        // Write associations not found in system
        foreach ($result['not_found_in_system'] as $association) {
            fputcsv($file, [$association, 'Not Found', 'Not in System']);
        }

        fclose($file);
    }

    /**
     * Display summary of analysis
     */
    private function displaySummary($result)
    {
        $this->info('=== ANALYSIS SUMMARY ===');
        $this->info("Total unique associations: {$result['total_unique']}");
        $this->info("Found in system: {$result['found_count']}");
        $this->info("Not found in system: {$result['not_found_count']}");

        if ($result['found_count'] > 0) {
            $this->info("\n=== ASSOCIATIONS FOUND IN SYSTEM ===");
            foreach ($result['found_in_system'] as $association) {
                $this->line("✓ {$association}");
            }
        }

        if ($result['not_found_count'] > 0) {
            $this->info("\n=== ASSOCIATIONS NOT FOUND IN SYSTEM ===");
            foreach ($result['not_found_in_system'] as $association) {
                $this->line("✗ {$association}");
            }
        }
    }
}
