<?php

namespace App\Console\Commands;

use App\Models\Association;
use App\Models\Center;
use App\Models\City;
use App\Models\DonorFinancialPortfolio;
use App\Models\Order;
use App\Models\OrderAgreement;
use App\Models\OrderFinance;
use App\Models\OrderRequirement;
use App\Models\State;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class CreateOrdersFromCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'create:orders-from-csv {file : Path to the CSV file}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Create orders from CSV file data with association and donor portfolio linking';

    /**
     * Expected CSV headers
     */
    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)
        'X', // lat
        'Y', // lng
    ];

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

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

        $this->info('Starting order creation 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
            $created = 0;
            $errors = 0;

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

                try {
                    if ($this->createOrderFromRow($row, $rowNumber)) {
                        $created++;
                    } else {
                        $errors++;
                    }
                } catch (\Exception $e) {
                    $this->error("Row {$rowNumber}: {$e->getMessage()}");
                    $errors++;
                }
            }

            $this->info("\nOrder creation completed!");
            $this->info("Created: {$created} orders");
            if ($errors > 0) {
                $this->warn("Errors: {$errors} rows");
            }

            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)) {
                // If we have more columns than headers, try to merge the extra columns
                // This commonly happens when values contain unescaped commas
                $this->warn("Row {$rowCount} has ".count($row).' columns vs '.count($headers).' headers - attempting to fix');

                // Take only the first n columns where n = number of headers
                $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));
        }

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

    /**
     * Create order from CSV row data
     */
    protected function createOrderFromRow(array $row, int $rowNumber): bool
    {
        // Extract data from row
        $associationName = trim($row['اسم الجهة'] ?? '');
        $portfolioName = trim($row['المانح'] ?? '');
        $orderType = trim($row['المشروع المعتمد'] ?? '');
        $numberOfFamilies = trim($row[' عدد الأسر المستفيدة '] ?? '');
        $approvalDate = trim($row['تاريخ الاعتماد'] ?? '');
        $state = trim($row['المنطقة'] ?? '');
        $center = trim($row['المركز'] ?? '');
        $lng = trim($row['Y'] ?? '');
        $lat = trim($row['X'] ?? '');
        $status = Order::STATUS_ACCEPTED;

        // order finance data
        $count = trim($row['العدد'] ?? '');
        $capacity = trim($row['السعة'] ?? '');
        $offerPrice = trim($row['قيمة المشروع'] ?? '');

        // order agreement data
        $totalContractValue = trim($row['قيمة المشروع'] ?? '');
        $contractStartDate = trim($row['تاريخ بداية المشروع'] ?? '');

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

            return false;
        }

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

            return false;
        }

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

            return false;
        }

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

            return false;
        }

        // Find association (try exact match first, then partial match)
        $association = Association::where('name', $associationName)->first();
        if (! $association) {
            // Try partial match for testing purposes
            $association = Association::where('name', 'like', "%{$associationName}%")->first();
        }

        // if (!$association) {
        //     // Try searching for key parts of the association name
        //     $searchTerms = ['جمعية', 'البر', 'الخيرية'];
        //     foreach ($searchTerms as $term) {
        //         if (strpos($associationName, $term) !== false) {
        //             $association = Association::where('name', 'like', "%{$term}%")->first();
        //             if ($association) {
        //                 $this->info("Row {$rowNumber}: Found association by partial match: {$association->name}");
        //                 break;
        //             }
        //         }
        //     }
        // }

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

            return false;
        }

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

        // abort if no donor portfolio found
        if (! $donorPortfolio) {
            $this->error("Row {$rowNumber}: Donor portfolio not found: {$portfolioName}");

            return false;
        }

        // Find state and city if specified
        $stateId = null;
        $cityId = null;
        $centerIds = [];

        if (! empty($state)) {
            $stateModel = State::where('name', 'like', "%{$state}%")->first();
            if ($stateModel) {
                $stateId = $stateModel->id;
            } else {
                $this->warn("Row {$rowNumber}: State not found: {$state}");
            }
        }

        if (! empty($center) && $stateId) {
            // Find city that contains this center in the specified state
            $centerModel = Center::where('name', 'like', "%{$center}%")
                ->whereHas('city', function ($query) use ($stateId) {
                    $query->where('state_id', $stateId);
                })
                ->first();

            if ($centerModel) {
                $cityId = $centerModel->city_id;
                $centerIds[] = $centerModel->id;
            } else {
                $this->warn("Row {$rowNumber}: Center not found: {$center} in state: {$state}");
            }
        }

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

            return false;
        }

        // Find admin user for created_by field
        $adminUser = User::where('email', 'admin@sekaya.com')->first();
        if (! $adminUser) {
            $this->error("Admin user with email 'admin@sekaya.com' not found. Please ensure this user exists.");

            return false;
        }

        // Prepare order data
        $orderData = [
            'type' => $mappedOrderType,
            'association_id' => $association->id,
            'number_of_families' => is_numeric($numberOfFamilies) ? (int) $numberOfFamilies : null,
            'state_id' => $stateId,
            'city_id' => $cityId,
            'status' => $status,
            'lat' => is_numeric($lat) ? (float) $lat : null,
            'lng' => is_numeric($lng) ? (float) $lng : null,
            'created_by' => $adminUser->id,
        ];

        // Validate order data
        $validator = Validator::make($orderData, [
            'type' => 'required|string',
            'association_id' => 'required|exists:associations,id',
            'number_of_families' => 'nullable|integer|min:0',
            'state_id' => 'nullable|exists:states,id',
            'city_id' => 'nullable|exists:cities,id',
            'status' => 'required|string',
            'lat' => 'nullable|numeric|between:-90,90',
            'lng' => 'nullable|numeric|between:-180,180',
        ]);

        if ($validator->fails()) {
            $this->error("Row {$rowNumber}: Validation failed: ".implode(', ', $validator->errors()->all()));

            return false;
        }

        try {
            // // Check if order already exists for this association and type to avoid duplicates
            // $existingOrder = Order::where('association_id', $association->id)
            //     ->where('type', $mappedOrderType)
            //     ->where('number_of_families', $orderData['number_of_families'])
            //     ->first();

            // if ($existingOrder) {
            //     $this->warn("Row {$rowNumber}: Order already exists (ID: {$existingOrder->id}) - skipping");
            //     return false;
            // }

            // Create order in database transaction
            DB::beginTransaction();

            $order = Order::create($orderData);

            $order->timestamps = false;
            $order->created_at = $parsedDate;
            $order->save();
            $order->timestamps = true;

            // Link to centers if found
            if (! empty($centerIds)) {
                $order->centers()->attach($centerIds);
            }

            // Create OrderFinance if we have finance data
            if (! empty($count) || ! empty($capacity) || ! empty($offerPrice)) {
                // First create a basic OrderRequirement
                $requirement = OrderRequirement::create([
                    'order_id' => $order->id,
                    'name' => 'متطلبات مستوردة من CSV',
                    'status' => OrderRequirement::STATUS_ACCEPTED,
                    'created_by' => $adminUser->id,
                ]);

                $financeData = [
                    'order_id' => $order->id,
                    'order_requirement_id' => $requirement->id,
                    'count' => is_numeric($count) ? (int) $count : null,
                    'capacity' => is_numeric($capacity) ? (float) $capacity : null,
                    'offer_price' => is_numeric($offerPrice) ? (float) $offerPrice : null,
                    'created_by' => $adminUser->id,

                    'execution_duration' => 0,
                    'company_name' => 'غير معروف',
                    'additional_fees' => 0,
                    'operation_cost_percentage' => 0,
                    'operation_cost' => 0,
                ];

                // Only create if we have at least one finance value
                $hasFinanceData = ! empty($financeData['count']) ||
                    ! empty($financeData['capacity']) ||
                    ! empty($financeData['offer_price']);

                if ($hasFinanceData) {
                    OrderFinance::create($financeData);
                    $this->info("  - Created OrderRequirement and OrderFinance with count: {$count}, capacity: {$capacity}, offer_price: {$offerPrice}");
                }
            }

            // Create OrderAgreement if we have agreement data
            if (! empty($totalContractValue) || ! empty($contractStartDate)) {
                // Use offer_price as contract value if totalContractValue is empty
                $contractValue = is_numeric($totalContractValue) ? (float) $totalContractValue : (is_numeric($offerPrice) ? (float) $offerPrice : 0);

                $agreementData = [
                    'order_id' => $order->id,
                    'contract_number' => 'CSV-'.$order->id.'-'.date('Y'), // Generate a contract number
                    'total_contract_value' => $contractValue,
                    'created_by' => $adminUser->id,
                ];

                // Parse contract start date with multiple formats
                if (! empty($contractStartDate)) {
                    try {
                        // Try different date formats
                        $dateFormats = ['d/m/y', 'd/m/Y', 'Y-m-d', 'd-m-Y'];
                        $parsedDate = null;

                        foreach ($dateFormats as $format) {
                            try {
                                $parsedDate = Carbon::createFromFormat($format, $contractStartDate);
                                break;
                            } catch (\Exception $e) {
                                continue;
                            }
                        }

                        if ($parsedDate) {
                            $agreementData['start_date'] = $parsedDate;
                        } else {
                            $this->warn("  - Could not parse contract start date: {$contractStartDate}");
                        }
                    } catch (\Exception $e) {
                        $this->warn("  - Invalid contract start date format: {$contractStartDate}");
                    }
                }

                OrderAgreement::create($agreementData);
                $this->info("  - Created OrderAgreement with contract number: {$agreementData['contract_number']}, value: {$totalContractValue}, start_date: {$contractStartDate}");
            }

            // Link to donor portfolio if found
            if ($donorPortfolio) {
                $donorPortfolio->orders()->attach($order->id, [
                    'allocated_amount' => is_numeric($offerPrice) ? (float) $offerPrice : 0,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            DB::commit();

            $this->info("Row {$rowNumber}: Created order ID {$order->id} for association '{$associationName}'");

            if ($stateId) {
                $this->info("  - Linked to state: {$state}");
            }
            if ($cityId) {
                $this->info("  - Linked to city: {$center}");
            }
            if (! empty($centerIds)) {
                $this->info("  - Linked to center: {$center}");
            }

            if ($donorPortfolio) {
                $this->info("  - Linked to portfolio: {$portfolioName}");
            }

            return true;
        } catch (\Exception $e) {
            DB::rollBack();
            $this->error("Row {$rowNumber}: Failed to create order: ".$e->getMessage());

            return false;
        }
    }

    /**
     * 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
     */
    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;
    }
}
