<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Store;
use App\Models\Order;
use Carbon\Carbon;

class ImportWooCommerceCSV extends Command
{
    protected $signature = 'orders:import-csv {store_id} {csv_file}';
    protected $description = 'Import WooCommerce orders from CSV file';

    public function handle()
    {
        $storeId = $this->argument('store_id');
        $csvFile = $this->argument('csv_file');

        // Validate store
        $store = Store::find($storeId);
        if (!$store) {
            $this->error("❌ Store ID {$storeId} not found!");
            return 1;
        }

        // Validate file
        if (!file_exists($csvFile)) {
            $this->error("❌ File not found: {$csvFile}");
            return 1;
        }

        $this->info("🚀 Importing orders for: {$store->name}");
        $this->info("📁 File: {$csvFile}\n");

        $imported = 0;
        $skipped = 0;
        $errors = 0;

        // Open CSV
        if (($handle = fopen($csvFile, 'r')) !== false) {
            // Read header row
            $header = fgetcsv($handle);

            if (!$header) {
                $this->error("❌ Invalid CSV file - no header row");
                return 1;
            }

            // Clean headers: remove BOM, trim whitespace, lowercase for consistency
            $header = array_map(function($col) {
                // Remove UTF-8 BOM if present
                $col = str_replace("\xEF\xBB\xBF", '', $col);
                // Trim whitespace
                return trim($col);
            }, $header);

            $this->line("📋 CSV Headers: " . implode(', ', $header));
            $this->newLine();

            $rowNumber = 1;

            while (($row = fgetcsv($handle)) !== false) {
                $rowNumber++;

                // Convert to associative array
                $data = array_combine($header, $row);

                try {
                    // Map CSV columns (flexible - works with different column names)
                    $orderId = $data['order_id'] ?? $data['ID'] ?? $data['id'] ?? null;
                    $orderNumber = $data['order_number'] ?? $data['number'] ?? $orderId;

                    if (!$orderId) {
                        $this->warn("  ⚠️  Row {$rowNumber}: No order ID, skipping");
                        $skipped++;
                        continue;
                    }

                    // Build billing data
                    $billing = [
                        'first_name' => $data['billing_first_name'] ?? $data['customer_first_name'] ?? '',
                        'last_name' => $data['billing_last_name'] ?? $data['customer_last_name'] ?? '',
                        'email' => $data['billing_email'] ?? $data['customer_email'] ?? '',
                        'phone' => $data['billing_phone'] ?? $data['customer_phone'] ?? '',
                        'address_1' => $data['billing_address_1'] ?? '',
                        'address_2' => $data['billing_address_2'] ?? '',
                        'city' => $data['billing_city'] ?? '',
                        'state' => $data['billing_state'] ?? $data['billing_province'] ?? '',
                        'postcode' => $data['billing_postcode'] ?? $data['billing_zip'] ?? '',
                        'country' => $data['billing_country'] ?? 'MY',
                    ];

                    // Build shipping data
                    $shipping = [
                        'first_name' => $data['shipping_first_name'] ?? $billing['first_name'],
                        'last_name' => $data['shipping_last_name'] ?? $billing['last_name'],
                        'address_1' => $data['shipping_address_1'] ?? $billing['address_1'],
                        'address_2' => $data['shipping_address_2'] ?? $billing['address_2'],
                        'city' => $data['shipping_city'] ?? $billing['city'],
                        'state' => $data['shipping_state'] ?? $data['shipping_province'] ?? $billing['state'],
                        'postcode' => $data['shipping_postcode'] ?? $data['shipping_zip'] ?? $billing['postcode'],
                        'country' => $data['shipping_country'] ?? 'MY',
                    ];

                    // Parse line items from separate columns (Product Item 1 Name, Product Item 2 Name, etc.)
                    $lineItems = [];

                    // Check for "line_items" column first (comma-separated format)
                    if (!empty($data['line_items'])) {
                        $items = explode(',', $data['line_items']);
                        foreach ($items as $item) {
                            // Parse: "Product Name x2 @ RM50"
                            if (preg_match('/(.+?)\s*x(\d+)\s*@\s*RM?([\d.]+)/', trim($item), $matches)) {
                                $lineItems[] = [
                                    'name' => trim($matches[1]),
                                    'quantity' => (int)$matches[2],
                                    'price' => (float)$matches[3],
                                    'total' => (float)$matches[3] * (int)$matches[2],
                                ];
                            }
                        }
                    }

                    // Or check for separate Product Item columns (Product Item 1 Name, etc.)
                    if (empty($lineItems)) {
                        for ($i = 1; $i <= 20; $i++) {
                            $nameKey = "Product Item {$i} Name";
                            $qtyKey = "Product Item {$i} Quantity";
                            $totalKey = "Product Item {$i} Total";
                            $subtotalKey = "Product Item {$i} Subtotal";

                            if (!empty($data[$nameKey])) {
                                $quantity = (int)($data[$qtyKey] ?? 1);
                                $total = (float)($data[$totalKey] ?? $data[$subtotalKey] ?? 0);
                                $price = $quantity > 0 ? $total / $quantity : 0;

                                $lineItems[] = [
                                    'name' => trim($data[$nameKey]),
                                    'quantity' => $quantity,
                                    'price' => $price,
                                    'total' => $total,
                                    'sku' => $data["Product Item {$i} SKU"] ?? '',
                                    'product_id' => $data["Product Item {$i} id"] ?? null,
                                ];
                            }
                        }
                    }

                    // Parse date
                    $dateCreated = $data['date_created'] ?? $data['order_date'] ?? now();
                    if (is_string($dateCreated)) {
                        $dateCreated = Carbon::parse($dateCreated);
                    }

                    // Create/Update order
                    Order::updateOrCreate(
                        [
                            'store_id' => $store->id,
                            'woo_order_id' => $orderId
                        ],
                        [
                            'order_number' => $orderNumber,
                            'status' => $data['status'] ?? 'completed',
                            'total' => (float)($data['total'] ?? $data['order_total'] ?? 0),
                            'subtotal' => (float)($data['subtotal'] ?? 0),
                            'shipping_total' => (float)($data['shipping_total'] ?? $data['shipping_amount'] ?? 0),
                            'discount_total' => (float)($data['discount_total'] ?? $data['discount_amount'] ?? 0),
                            'currency' => $data['currency'] ?? 'MYR',
                            'payment_method' => $data['payment_method'] ?? 'cod',
                            'payment_method_title' => $data['payment_method_title'] ?? 'Cash on Delivery',
                            'billing' => $billing,
                            'shipping' => $shipping,
                            'line_items' => $lineItems,
                            'notes' => $data['customer_note'] ?? null,
                            'shipping_method_title' => $data['shipping_method'] ?? $data['shipping_method_title'] ?? '',
                            'date_created' => $dateCreated,
                            'date_modified' => isset($data['date_modified']) ? Carbon::parse($data['date_modified']) : now(),
                        ]
                    );

                    $imported++;

                    // Show progress every 100 orders
                    if ($imported % 100 == 0) {
                        $this->info("✓ Progress: {$imported} orders imported...");
                    }

                } catch (\Exception $e) {
                    $this->error("  ✗ Row {$rowNumber} (Order {$orderId}): " . $e->getMessage());
                    $errors++;
                }
            }

            fclose($handle);
        }

        // Update store last sync
        $store->update(['last_sync' => now()]);

        // Summary
        $this->newLine();
        $this->info("✅ Import completed!");
        $this->info("📊 Imported: {$imported} orders");
        if ($skipped > 0) {
            $this->warn("⚠️  Skipped: {$skipped} rows");
        }
        if ($errors > 0) {
            $this->error("❌ Errors: {$errors} rows");
        }

        return 0;
    }
}
