<?php

namespace App\Console\Commands;

use App\Models\Association;
use App\Models\Center;
use App\Models\DonorFinancialPortfolio;
use App\Models\Order;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class UpdateOrderDatesFromCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'update:order-dates-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 created_at dates from CSV file data with cautious matching';

    /**
     * Expected CSV headers (includes the new date header)
     */
    protected $expectedHeaders = [
        'اسم الجهة',  // Association name
        'محفظة المشاريع السابقة',  // Donor financial portfolio name
        'المشروع المعتمد',  // Order type
        ' عدد الأسر المستفيدة ',  // Number of families
        'المنطقة',  // Region (optional)
        'المركز',  // Center (optional)
        'العدد',  // Quantity
        'السعة',  // Capacity
        'قيمة المشروع',  // Project value
        'تاريخ بداية المشروع',  // Project start date
        'تاريخ الاعتماد',  // Approval date (new header)
    ];

    /**
     * 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 date updates 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->updateOrderDateFromRow($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 date 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 the critical new header
        if (! in_array('تاريخ الاعتماد', $headers)) {
            throw new \Exception("Required header 'تاريخ الاعتماد' not found in CSV");
        }

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

    /**
     * Update order date from CSV row data
     *
     * @return string Result status: 'updated', 'multiple_matches', 'no_matches', 'skipped', 'error'
     */
    protected function updateOrderDateFromRow(array $row, int $rowNumber, bool $isDryRun): string
    {
        // Extract data from row (same as CreateOrdersFromCsv)
        $associationName = trim($row['اسم الجهة'] ?? '');
        $portfolioName = trim($row['محفظة المشاريع السابقة'] ?? '');
        $orderType = trim($row['المشروع المعتمد'] ?? '');
        $numberOfFamilies = trim($row[' عدد الأسر المستفيدة '] ?? '');
        $approvalDate = 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';
        }

        if (empty($approvalDate)) {
            $this->error("Row {$rowNumber}: Approval date is required");

            return 'skipped';
        }

        // Parse and validate the approval date
        $parsedDate = $this->parseApprovalDate($approvalDate);
        if (! $parsedDate) {
            $this->error("Row {$rowNumber}: Invalid date format '{$approvalDate}'. Expected format: DD/MM/YY");

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

        // Find donor portfolio if specified (same logic as CreateOrdersFromCsv)
        $donorPortfolio = null;
        if (! empty($portfolioName)) {
            $donorPortfolio = DonorFinancialPortfolio::where('name', $portfolioName)->first();
            if (! $donorPortfolio) {
                $this->error("Row {$rowNumber}: Donor portfolio not found: {$portfolioName}");

                return 'no_matches';
            }
        }

        // Map order type to constants (same logic as CreateOrdersFromCsv)
        $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 number of families filter if provided
        if (is_numeric($numberOfFamilies)) {
            $query->where('number_of_families', (int) $numberOfFamilies);
        }

        // Add donor portfolio filter if found
        if ($donorPortfolio) {
            $query->whereHas('donorFinancialPortfolios', function ($q) use ($donorPortfolio) {
                $q->where('donor_financial_portfolio_id', $donorPortfolio->id);
            });
        }

        $matchingOrders = $query->get();

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

            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("  Manual review required for approval date: {$approvalDate}");

            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} created_at from '{$order->created_at}' to '{$parsedDate}'");

            return 'updated';
        }

        try {
            DB::beginTransaction();

            $oldDate = $order->created_at;

            // Temporarily disable timestamps to prevent Laravel from interfering
            $order->timestamps = false;
            $order->created_at = $parsedDate;
            $order->save();
            $order->timestamps = true;

            DB::commit();

            $this->info("Row {$rowNumber}: Updated Order ID {$order->id} created_at from '{$oldDate}' to '{$parsedDate}'");

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

            return 'error';
        }
    }

    /**
     * Parse approval date from DD/MM/YY format
     */
    protected function parseApprovalDate(string $dateString): ?Carbon
    {
        try {
            // Handle DD/MM/YY format (e.g., '27/10/22')
            if (preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{2})$/', $dateString, $matches)) {
                $day = (int) $matches[1];
                $month = (int) $matches[2];
                $year = (int) $matches[3];

                // Convert 2-digit year to 4-digit (assuming 20xx for years 00-50, 19xx for 51-99)
                if ($year <= 50) {
                    $year += 2000;
                } else {
                    $year += 1900;
                }

                // Validate date components
                if ($day < 1 || $day > 31 || $month < 1 || $month > 12) {
                    return null;
                }

                return Carbon::createFromDate($year, $month, $day)->startOfDay();
            }

            return null;
        } catch (\Exception $e) {
            return null;
        }
    }

    /**
     * Map Arabic order type to system constants (same as CreateOrdersFromCsv)
     */
    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_OTHER,
        ];

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