<?php

namespace App\Console\Commands;

use App\Models\Association;
use App\Models\Center;
use App\Models\City;
use App\Models\Order;
use App\Models\State;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class UpdateOrderStatusFromCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'update:order-status-from-csv {file : Path to the CSV file} {--dry-run : Preview changes without applying them}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Update order status to STATUS_CLASSIFICATION from CSV file data with cautious matching';

    /**
     * Expected CSV headers
     */
    protected $expectedHeaders = [
        'المنطقة',  // Region/State
        'المدينة أو المحافظة',  // City or Province
        'المركز',  // Center
        'اسم الجهة',  // Association name
        'المشروع المعتمد',  // Order type
    ];

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

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

            return Command::FAILURE;
        }

        // Check if file is readable
        if (! is_readable($filePath)) {
            $this->error("File is not readable: {$filePath}");

            return Command::FAILURE;
        }

        if ($isDryRun) {
            $this->info('=== DRY RUN MODE - No changes will be applied ===');
        }

        $this->info('Starting order status update from CSV...');
        $this->info("File: {$filePath}");

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

            if (empty($csvData)) {
                $this->error('CSV file is empty or could not be read');

                return Command::FAILURE;
            }

            $this->info('Found '.count($csvData).' rows to process');

            // Process each row
            $updated = 0;
            $errors = 0;
            $skipped = 0;
            $multipleMatches = 0;
            $noMatches = 0;

            foreach ($csvData as $index => $row) {
                $rowNumber = $index + 2; // +2 because index starts at 0 and we have headers

                try {
                    $result = $this->updateOrderStatusFromRow($row, $rowNumber, $isDryRun);

                    switch ($result) {
                        case 'updated':
                            $updated++;
                            break;
                        case 'multiple_matches':
                            $multipleMatches++;
                            break;
                        case 'no_matches':
                            $noMatches++;
                            break;
                        case 'skipped':
                            $skipped++;
                            break;
                        default:
                            $errors++;
                            break;
                    }
                } catch (\Exception $e) {
                    $this->error("Row {$rowNumber}: {$e->getMessage()}");
                    $errors++;
                }
            }

            $this->info("\nOrder status update completed!");
            if ($isDryRun) {
                $this->info("Would update: {$updated} orders");
            } else {
                $this->info("Updated: {$updated} orders");
            }
            $this->info("Multiple matches (skipped): {$multipleMatches} rows");
            $this->info("No matches found: {$noMatches} rows");
            $this->info("Skipped (invalid data): {$skipped} rows");
            if ($errors > 0) {
                $this->warn("Errors: {$errors} rows");
            }

            if ($multipleMatches > 0) {
                $this->warn("\nWARNING: {$multipleMatches} rows had multiple matching orders and were skipped for safety.");
                $this->warn('Please review the log output above for details on which orders need manual review.');
            }

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

            return Command::FAILURE;
        }
    }

    /**
     * Read CSV file and return array of data
     */
    protected function readCsvFile(string $filePath): array
    {
        $csvData = [];

        // Open file with UTF-8 encoding support
        $file = fopen($filePath, 'r');
        if (! $file) {
            throw new \Exception("Could not open file: {$filePath}");
        }

        // Read header row
        $headers = fgetcsv($file, separator: ';');
        if (! $headers) {
            fclose($file);
            throw new \Exception('Could not read CSV headers');
        }

        // Remove BOM if present
        if (! empty($headers[0])) {
            $headers[0] = preg_replace('/\x{FEFF}/u', '', $headers[0]);
        }

        // Validate headers
        $this->validateHeaders($headers);

        // Read data rows
        $rowCount = 0;
        while (($row = fgetcsv($file, separator: ';')) !== false) {
            $rowCount++;

            // Try to handle rows with mismatched column counts
            if (count($row) > count($headers)) {
                $this->warn("Row {$rowCount} has ".count($row).' columns vs '.count($headers).' headers - attempting to fix');
                $row = array_slice($row, 0, count($headers));
            }

            if (count($row) === count($headers)) {
                $csvData[] = array_combine($headers, $row);
            } else {
                $this->warn("Row {$rowCount} still has mismatched columns after fix - skipping");
            }
        }

        $this->info("Total rows read: {$rowCount}, Data rows: ".count($csvData));

        fclose($file);

        return $csvData;
    }

    /**
     * Validate CSV headers
     */
    protected function validateHeaders(array $headers): void
    {
        $missingHeaders = array_diff($this->expectedHeaders, $headers);

        if (! empty($missingHeaders)) {
            $this->warn('Missing expected headers: '.implode(', ', $missingHeaders));
        }

        // Check for required headers
        $requiredHeaders = ['اسم الجهة', 'المشروع المعتمد'];
        foreach ($requiredHeaders as $required) {
            if (! in_array($required, $headers)) {
                throw new \Exception("Required header '{$required}' not found in CSV");
            }
        }

        $this->info('CSV Headers found: '.implode(', ', $headers));
    }

    /**
     * Update order status from CSV row data
     *
     * @return string Result status: 'updated', 'multiple_matches', 'no_matches', 'skipped', 'error'
     */
    protected function updateOrderStatusFromRow(array $row, int $rowNumber, bool $isDryRun): string
    {
        // Extract data from row
        $stateName = trim($row['المنطقة'] ?? '');
        $cityName = trim($row['المدينة أو المحافظة'] ?? '');
        $centerName = trim($row['المركز'] ?? '');
        $associationName = trim($row['اسم الجهة'] ?? '');
        $orderType = trim($row['المشروع المعتمد'] ?? '');

        // Validate required fields
        if (empty($associationName)) {
            $this->error("Row {$rowNumber}: Association name is required");

            return 'skipped';
        }

        if (empty($orderType)) {
            $this->error("Row {$rowNumber}: Order type is required");

            return 'skipped';
        }

        // Find association (same logic as CreateOrdersFromCsv)
        $association = Association::where('name', $associationName)->first();
        if (! $association) {
            $association = Association::where('name', 'like', "%{$associationName}%")->first();
        }

        if (! $association) {
            $this->error("Row {$rowNumber}: Association not found: {$associationName}");

            return 'no_matches';
        }

        // Map order type to constants
        $mappedOrderType = $this->mapOrderType($orderType);
        if (! $mappedOrderType) {
            $this->error("Row {$rowNumber}: Invalid order type: {$orderType}");

            return 'skipped';
        }

        // Build query to find matching orders
        $query = Order::where('association_id', $association->id)
            ->where('type', $mappedOrderType);

        // Add location filters if provided
        if (! empty($stateName)) {
            $state = State::where('name', $stateName)->first();
            if (! $state) {
                $state = State::where('name', 'ILIKE', "%{$stateName}%")->first();
            }

            if ($state) {
                $query->where('state_id', $state->id);
            } else {
                $this->warn("Row {$rowNumber}: State not found: {$stateName}");
            }
        }

        if (! empty($cityName)) {
            $city = City::where('name', $cityName)->first();
            if (! $city) {
                $city = City::where('name', 'ILIKE', "%{$cityName}%")->first();
            }

            if ($city) {
                $query->where('city_id', $city->id);
            } else {
                $this->warn("Row {$rowNumber}: City not found: {$cityName}");
            }
        }

        // Filter by center if provided (many-to-many relationship)
        if (! empty($centerName)) {
            $center = Center::whereRaw("name->>'ar' = ?", [$centerName])->first();
            if (! $center) {
                $center = Center::whereRaw("name->>'ar' ILIKE ?", ["%{$centerName}%"])->first();
            }

            if ($center) {
                $query->whereHas('centers', function ($centerQuery) use ($center) {
                    $centerQuery->where('centers.id', $center->id);
                });
            } else {
                $this->warn("Row {$rowNumber}: Center not found: {$centerName}");
            }
        }

        $matchingOrders = $query->get();

        // Handle different match scenarios
        if ($matchingOrders->count() === 0) {
            $this->warn("Row {$rowNumber}: No matching orders found for association '{$associationName}', type '{$orderType}'");
            $this->warn("  Filters: State='{$stateName}', City='{$cityName}', Center='{$centerName}'");

            return 'no_matches';
        }

        if ($matchingOrders->count() > 1) {
            $this->warn("Row {$rowNumber}: Multiple orders found ({$matchingOrders->count()}) for association '{$associationName}', type '{$orderType}' - SKIPPING for safety");
            $this->warn('  Order IDs: '.$matchingOrders->pluck('id')->implode(', '));
            $this->warn("  Filters: State='{$stateName}', City='{$cityName}', Center='{$centerName}'");
            $this->warn('  Manual review required to update status to STATUS_CLASSIFICATION');

            return 'multiple_matches';
        }

        // Exactly one match found - proceed with update
        $order = $matchingOrders->first();

        if ($isDryRun) {
            $this->info("Row {$rowNumber}: Would update Order ID {$order->id} status from '{$order->status}' to '".Order::STATUS_CLASSIFICATION."'");

            return 'updated';
        }

        try {
            DB::beginTransaction();

            $oldStatus = $order->status;
            $order->status = Order::STATUS_CLASSIFICATION;
            $order->save();

            DB::commit();

            $this->info("Row {$rowNumber}: Updated Order ID {$order->id} status from '{$oldStatus}' to '".Order::STATUS_CLASSIFICATION."'");

            return 'updated';
        } catch (\Exception $e) {
            DB::rollBack();
            $this->error("Row {$rowNumber}: Failed to update Order ID {$order->id}: ".$e->getMessage());

            return 'error';
        }
    }

    /**
     * Map Arabic order type to system constants
     */
    protected function mapOrderType(string $orderType): ?string
    {
        $typeMapping = [
            'محطة تحلية' => Order::TYPE_STATION,
            'صهاريج' => Order::TYPE_TANKER,
            'خزانات' => Order::TYPE_DOMESTIC_STORAGE_TANK,
            'خزان مركزي' => Order::TYPE_CENTRAL_STORAGE_TANK,
            'صيانة وتشغيل' => Order::TYPE_MAINTENANCE_STATION,
            'صيانة صهريج' => Order::TYPE_MAINTENANCE_TANKER,
            'شبكة مصغرة' => Order::TYPE_MICRO_NETWORK,
            'حفر بئر' => Order::TYPE_WELL_DRILLING,
            'تخزين مياه أمطار' => Order::TYPE_RAINWATER_STORAGE,
            'فلاتر منزلية' => Order::TYPE_HOME_FILTERS,
            'قوارير مياه' => Order::TYPE_WATER_BOTTLES,
            'مبردات' => Order::TYPE_COOLERS,
            'منتجات تحت المراجعة' => Order::TYPE_PRODUCTS_UNDER_REVIEW,
            'تراخيص الابار و محطات التنقية' => Order::TYPE_WELL_PURIFICATION_LICENSES,
            'أخرى' => Order::TYPE_OTHER,
        ];

        return $typeMapping[$orderType] ?? null;
    }
}
