<?php

namespace App\Console\Commands;

use App\Models\Order;
use App\Models\OrderSequence;
use App\Models\User;
use App\Services\GoogleSheetsService;
use App\Services\OrderNumberService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ImportMissingOrdersFromGoogleSheets extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'orders:import-missing {user_id} {--dry-run} {--fix-auto-increment}';

    /**
     * The console command description.
     */
    protected $description = 'Import missing orders from Google Sheets after database restore';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $userId = $this->argument('user_id');
        $dryRun = $this->option('dry-run');
        $fixAutoIncrement = $this->option('fix-auto-increment');

        $this->info("=== Import Missing Orders from Google Sheets ===\n");

        // Get user
        $user = User::find($userId);
        if (!$user) {
            $this->error("User ID {$userId} not found!");
            return 1;
        }

        if (!$user->google_sheets_sync_enabled || !$user->google_sheet_id) {
            $this->error("User {$user->name} does not have Google Sheets sync enabled!");
            return 1;
        }

        $this->info("User: {$user->name} (ID: {$user->id})");
        $this->info("Google Sheet ID: {$user->google_sheet_id}\n");

        try {
            // Authenticate and get sheet data
            $service = new GoogleSheetsService();
            $service->authenticate($user);

            $sheetsService = new \Google\Service\Sheets($service->client ?? $service->authenticate($user));
            $spreadsheetId = $user->google_sheet_id;

            // Get all data from Google Sheets
            $range = 'Sheet1!A2:S1000'; // Skip header row, get up to 1000 orders
            $response = $sheetsService->spreadsheets_values->get($spreadsheetId, $range);
            $rows = $response->getValues();

            if (empty($rows)) {
                $this->info("No orders found in Google Sheets.");
                return 0;
            }

            $this->info("Found " . count($rows) . " orders in Google Sheets\n");

            // Parse orders from Google Sheets
            $sheetOrders = [];
            foreach ($rows as $index => $row) {
                if (empty($row[0])) {
                    continue; // Skip empty rows
                }

                $sheetOrders[] = [
                    'order_number' => $row[0] ?? '',
                    'global_order_id' => $row[1] ?? '',
                    'date_created' => $row[2] ?? '',
                    'customer_name' => $row[3] ?? '',
                    'phone' => $row[4] ?? '',
                    'email' => $row[5] ?? '',
                    'address' => $row[6] ?? '',
                    'city' => $row[7] ?? '',
                    'state' => $row[8] ?? '',
                    'postcode' => $row[9] ?? '',
                    'products' => $row[10] ?? '',
                    'sku' => $row[11] ?? '',
                    'quantity' => $row[12] ?? '',
                    'total' => $row[13] ?? 0,
                    'currency' => $row[14] ?? 'MYR',
                    'payment_method' => $row[15] ?? '',
                    'status' => $row[16] ?? 'processing',
                    'tracking_number' => $row[17] ?? '',
                    'store_name' => $row[18] ?? '',
                ];
            }

            // Find missing orders (in Google Sheets but not in database)
            $missingOrders = [];
            $existingOrders = [];
            $conflicts = [];

            foreach ($sheetOrders as $sheetOrder) {
                $orderNumber = $sheetOrder['order_number'];

                $existingOrder = Order::where('order_number', $orderNumber)->first();

                if ($existingOrder) {
                    // Check if it's a real duplicate or a conflict (different customer)
                    $existingCustomerName = $existingOrder->billing['first_name'] . ' ' . ($existingOrder->billing['last_name'] ?? '');
                    $sheetCustomerName = $sheetOrder['customer_name'];

                    // Normalize names for comparison
                    $existingNormalized = strtolower(trim($existingCustomerName));
                    $sheetNormalized = strtolower(trim($sheetCustomerName));

                    if ($existingNormalized !== $sheetNormalized) {
                        // CONFLICT! Same order number, different customer
                        $conflicts[] = [
                            'order_number' => $orderNumber,
                            'database_customer' => $existingCustomerName,
                            'sheet_customer' => $sheetCustomerName,
                            'database_total' => $existingOrder->total,
                            'sheet_total' => $sheetOrder['total'],
                            'database_date' => $existingOrder->date_created,
                            'sheet_date' => $sheetOrder['date_created'],
                        ];
                    } else {
                        $existingOrders[] = $orderNumber;
                    }
                } else {
                    $missingOrders[] = $sheetOrder;
                }
            }

            $this->info("Orders already in database: " . count($existingOrders));
            $this->info("Missing orders to import: " . count($missingOrders));

            if (count($conflicts) > 0) {
                $this->warn("\n⚠️  CONFLICTS DETECTED: " . count($conflicts) . " orders");
                $this->error("These order numbers exist in database BUT with different customer data!\n");

                $this->table(
                    ['Order Number', 'DB Customer', 'Sheet Customer', 'DB Total', 'Sheet Total'],
                    collect($conflicts)->map(fn($c) => [
                        $c['order_number'],
                        $c['database_customer'],
                        $c['sheet_customer'],
                        'MYR ' . number_format($c['database_total'], 2),
                        'MYR ' . number_format($c['sheet_total'], 2),
                    ])->toArray()
                );

                $this->error("\n💥 CRITICAL ISSUE:");
                $this->error("This means new orders were created AFTER restore but BEFORE import.");
                $this->error("The NEW orders in database have reused order numbers from LOST orders in Google Sheets.");
                $this->error("\nRECOMMENDED ACTION:");
                $this->error("1. Put site in maintenance mode: php artisan down");
                $this->error("2. Delete the conflicting NEW orders from database (if they're test orders)");
                $this->error("3. Re-run this import command");
                $this->error("4. Bring site back up: php artisan up\n");

                if (!$this->confirm("Do you want to continue importing non-conflicting orders?", false)) {
                    $this->info("Import cancelled. Please resolve conflicts first.");
                    return 1;
                }
            }

            $this->newLine();

            if (count($missingOrders) === 0) {
                if (count($conflicts) === 0) {
                    $this->info("✓ No missing orders. Database is up to date!");
                }
                return 0;
            }

            // Show missing orders
            $this->table(
                ['Order Number', 'Customer', 'Total', 'Date', 'Status'],
                collect($missingOrders)->map(fn($o) => [
                    $o['order_number'],
                    $o['customer_name'],
                    $o['currency'] . ' ' . $o['total'],
                    $o['date_created'],
                    $o['status']
                ])->toArray()
            );

            if ($dryRun) {
                $this->warn("\n--dry-run mode: No changes made.");
                $this->info("Run without --dry-run to import these orders.");
                return 0;
            }

            // Confirm import
            if (!$this->confirm("\nDo you want to import these " . count($missingOrders) . " orders?", true)) {
                $this->info("Import cancelled.");
                return 0;
            }

            // Fix auto-increment if requested
            if ($fixAutoIncrement) {
                $this->fixAutoIncrement($sheetOrders);
            }

            // Import missing orders
            $imported = 0;
            $failed = 0;

            DB::beginTransaction();

            try {
                foreach ($missingOrders as $sheetOrder) {
                    try {
                        // Parse customer name
                        $nameParts = explode(' ', $sheetOrder['customer_name'], 2);
                        $firstName = $nameParts[0] ?? '';
                        $lastName = $nameParts[1] ?? '';

                        // Parse products into line items
                        $products = array_filter(array_map('trim', explode(',', $sheetOrder['products'])));
                        $skus = array_filter(array_map('trim', explode(',', $sheetOrder['sku'])));
                        $quantities = array_filter(array_map('trim', explode(',', $sheetOrder['quantity'])));

                        $lineItems = [];
                        foreach ($products as $i => $productName) {
                            $lineItems[] = [
                                'name' => $productName,
                                'sku' => $skus[$i] ?? '',
                                'quantity' => $quantities[$i] ?? 1,
                                'price' => 0, // Not stored in sheet
                                'total' => 0,
                            ];
                        }

                        // Create order
                        $order = Order::create([
                            'order_number' => $sheetOrder['order_number'],
                            'global_order_id' => $sheetOrder['global_order_id'],
                            'date_created' => $sheetOrder['date_created'] ?: now(),
                            'status' => $sheetOrder['status'],
                            'currency' => $sheetOrder['currency'],
                            'total' => (float) $sheetOrder['total'],
                            'payment_method_title' => $sheetOrder['payment_method'],
                            'tracking_number' => $sheetOrder['tracking_number'] ?: null,
                            'created_by' => $user->id,
                            'billing' => [
                                'first_name' => $firstName,
                                'last_name' => $lastName,
                                'phone' => $sheetOrder['phone'],
                                'email' => $sheetOrder['email'],
                                'address_1' => $sheetOrder['address'],
                                'city' => $sheetOrder['city'],
                                'state' => $sheetOrder['state'],
                                'postcode' => $sheetOrder['postcode'],
                                'country' => 'MY',
                            ],
                            'shipping' => [
                                'first_name' => $firstName,
                                'last_name' => $lastName,
                                'phone' => $sheetOrder['phone'],
                                'email' => $sheetOrder['email'],
                                'address_1' => $sheetOrder['address'],
                                'city' => $sheetOrder['city'],
                                'state' => $sheetOrder['state'],
                                'postcode' => $sheetOrder['postcode'],
                                'country' => 'MY',
                            ],
                            'line_items' => $lineItems,
                        ]);

                        $this->info("✓ Imported: {$sheetOrder['order_number']}");
                        $imported++;

                    } catch (\Exception $e) {
                        $this->error("✗ Failed to import {$sheetOrder['order_number']}: " . $e->getMessage());
                        $failed++;
                    }
                }

                DB::commit();

                $this->info("\n=== Import Complete ===");
                $this->info("✓ Successfully imported: {$imported} orders");
                if ($failed > 0) {
                    $this->warn("✗ Failed to import: {$failed} orders");
                }

            } catch (\Exception $e) {
                DB::rollBack();
                $this->error("\n✗ Import failed: " . $e->getMessage());
                return 1;
            }

        } catch (\Exception $e) {
            $this->error("Error: " . $e->getMessage());
            $this->error($e->getTraceAsString());
            return 1;
        }

        return 0;
    }

    /**
     * Fix sequences to prevent order number conflicts
     * Updates order_sequences table based on imported orders
     */
    protected function fixAutoIncrement($sheetOrders)
    {
        $this->info("\n=== Fixing Order Sequences ===");

        $orderNumberService = app(OrderNumberService::class);

        // Group order numbers by prefix
        $prefixGroups = [];
        foreach ($sheetOrders as $order) {
            $orderNumber = $order['order_number'];

            // Extract prefix and sequence from order number
            // Examples: CK-SIT-000123, SP-AHM-000456, WH-000789
            if (preg_match('/^((?:CK|SP)-[A-Z]{3}|WH)-(\d+)$/', $orderNumber, $matches)) {
                $prefix = $matches[1];
                $sequence = (int) $matches[2];

                if (!isset($prefixGroups[$prefix]) || $sequence > $prefixGroups[$prefix]) {
                    $prefixGroups[$prefix] = $sequence;
                }
            }
        }

        // Update sequences for each prefix
        foreach ($prefixGroups as $prefix => $maxSequence) {
            $currentSequence = $orderNumberService->getCurrentSequence($prefix);
            $newSequence = max($currentSequence, $maxSequence) + 10; // Add buffer

            $orderNumberService->resetSequence($prefix, $newSequence);

            $this->info("✓ Updated sequence for {$prefix}: {$newSequence}");
        }

        // Also fix orders table auto-increment for good measure
        $maxDbId = Order::max('id') ?? 0;
        $newAutoIncrement = $maxDbId + 100; // Add buffer
        DB::statement("ALTER TABLE orders AUTO_INCREMENT = {$newAutoIncrement}");
        $this->info("✓ Orders table auto-increment set to: {$newAutoIncrement}");

        $this->info("\n✓ All sequences updated. New orders will not conflict with imported ones.\n");
    }
}
