<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Warehouse\IncomingOperation;
use App\Models\Warehouse\IncomingOperationItem;
use App\Models\Warehouse\ProjectInventory;
use App\Models\Warehouse\Inventory;
use Illuminate\Support\Facades\DB;

class FixSupplierDeliveryReturns extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'fix:supplier-delivery-returns {--dry-run : Show what would be fixed without making changes}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Comprehensive fix for supplier delivery return system - links unlinked returns, corrects inventory, validates data integrity';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info('🔧 Supplier Delivery Return System Fix');
        $this->info('=====================================');

        $dryRun = $this->option('dry-run');
        if ($dryRun) {
            $this->warn('DRY RUN MODE - No changes will be made');
        }

        $this->newLine();

        // Step 1: Find and fix unlinked returns
        $this->fixUnlinkedReturns($dryRun);

        // Step 2: Validate and correct inventory
        $this->validateInventory($dryRun);

        // Step 3: Verify data integrity
        $this->verifyDataIntegrity();

        // Step 4: Test API response
        $this->testApiResponse();

        $this->newLine();
        $this->info('✅ Supplier Delivery Return Fix Complete!');

        return Command::SUCCESS;
    }

    /**
     * Find and fix unlinked supplier delivery returns
     */
    private function fixUnlinkedReturns($dryRun = false)
    {
        $this->info('🔍 Step 1: Finding unlinked supplier delivery returns...');

        // Find all supplier delivery operations to project sites
        $supplierDeliveries = IncomingOperation::where('operation_type', 'supplier_delivery')
            ->where('delivery_type', 'project_site')
            ->where('status', 'completed')
            ->get();

        $fixedCount = 0;
        $totalIssues = 0;

        foreach ($supplierDeliveries as $sd) {
            $this->line("Checking SD: {$sd->operation_number} (Project: {$sd->project_id})");

            // Find unlinked returns for this project that could be from this SD
            $unlinkedReturns = IncomingOperation::where('operation_type', 'site_return')
                ->where('project_id', $sd->project_id)
                ->whereNull('supplier_delivery_id')
                ->where('status', 'completed')
                ->get();

            foreach ($unlinkedReturns as $return) {
                // Check if this return has items that match SD items
                $returnItems = $return->items;
                $sdItems = $sd->items;

                $shouldLink = $this->shouldLinkReturnToSD($return, $sd, $returnItems, $sdItems);

                if ($shouldLink) {
                    $totalIssues++;
                    $this->warn("  Found unlinked return: {$return->operation_number}");

                    if (!$dryRun) {
                        $this->linkReturnToSupplierDelivery($return, $sd);
                        $fixedCount++;
                        $this->info("    ✅ Linked to SD: {$sd->operation_number}");
                    } else {
                        $this->line("    Would link to SD: {$sd->operation_number}");
                    }
                }
            }
        }

        if ($totalIssues > 0) {
            $this->warn("Found {$totalIssues} unlinked returns");
            if (!$dryRun) {
                $this->info("Fixed {$fixedCount} returns");
            }
        } else {
            $this->info("✅ No unlinked returns found");
        }
    }

    /**
     * Determine if a return should be linked to a supplier delivery
     */
    private function shouldLinkReturnToSD($return, $sd, $returnItems, $sdItems)
    {
        // Check if return items match SD items by item_id
        foreach ($returnItems as $returnItem) {
            foreach ($sdItems as $sdItem) {
                if ($returnItem->item_id == $sdItem->item_id) {
                    return true; // Found matching item
                }
            }
        }
        return false;
    }

    /**
     * Link a return to a supplier delivery
     */
    private function linkReturnToSupplierDelivery($return, $sd)
    {
        // Link the operation
        $return->supplier_delivery_id = $sd->id;
        $return->save();

        // Link the items
        foreach ($return->items as $returnItem) {
            foreach ($sd->items as $sdItem) {
                if ($returnItem->item_id == $sdItem->item_id) {
                    $returnItem->supplier_delivery_item_id = $sdItem->id;
                    $returnItem->save();
                    break;
                }
            }
        }
    }

    /**
     * Validate and correct inventory discrepancies
     */
    private function validateInventory($dryRun = false)
    {
        $this->info('📊 Step 2: Validating inventory consistency...');

        $supplierDeliveries = IncomingOperation::where('operation_type', 'supplier_delivery')
            ->where('delivery_type', 'project_site')
            ->where('status', 'completed')
            ->get();

        $inventoryIssues = 0;

        foreach ($supplierDeliveries as $sd) {
            foreach ($sd->items as $sdItem) {
                // Calculate expected vs actual inventory
                $delivered = $sdItem->quantity_delivered;
                $returned = $this->calculateReturnedQuantity($sdItem->id);
                $expectedProjectQty = $delivered - $returned;

                $actualProjectQty = ProjectInventory::where('project_id', $sd->project_id)
                    ->where('item_id', $sdItem->item_id)
                    ->value('quantity_available') ?? 0;

                if (abs($expectedProjectQty - $actualProjectQty) > 0.01) {
                    $inventoryIssues++;
                    $this->warn("  Inventory mismatch for item {$sdItem->item_id} in project {$sd->project_id}:");
                    $this->line("    Expected: {$expectedProjectQty}, Actual: {$actualProjectQty}");

                    if (!$dryRun) {
                        $this->correctProjectInventory($sd->project_id, $sdItem->item_id, $expectedProjectQty);
                        $this->info("    ✅ Corrected to {$expectedProjectQty}");
                    }
                }
            }
        }

        if ($inventoryIssues == 0) {
            $this->info("✅ All inventory quantities are correct");
        } else if (!$dryRun) {
            $this->info("Fixed {$inventoryIssues} inventory discrepancies");
        }
    }

    /**
     * Calculate returned quantity for a supplier delivery item
     */
    private function calculateReturnedQuantity($supplierDeliveryItemId)
    {
        return IncomingOperationItem::where('supplier_delivery_item_id', $supplierDeliveryItemId)
            ->whereHas('operation', function($query) {
                $query->where('operation_type', 'site_return')
                      ->where('status', 'completed');
            })
            ->sum('quantity_delivered');
    }

    /**
     * Correct project inventory quantity
     */
    private function correctProjectInventory($projectId, $itemId, $correctQuantity)
    {
        $inventory = ProjectInventory::where('project_id', $projectId)
            ->where('item_id', $itemId)
            ->first();

        if ($inventory) {
            $inventory->quantity_available = $correctQuantity;
            $inventory->save();
        } elseif ($correctQuantity > 0) {
            // Create new inventory record if needed
            ProjectInventory::create([
                'project_id' => $projectId,
                'item_id' => $itemId,
                'quantity_available' => $correctQuantity,
                'quantity_allocated' => 0
            ]);
        }
    }

    /**
     * Verify overall data integrity
     */
    private function verifyDataIntegrity()
    {
        $this->info('🔍 Step 3: Verifying data integrity...');

        $issues = [];

        // Check fillable fields
        $incomingOp = new IncomingOperation();
        if (!in_array('supplier_delivery_id', $incomingOp->getFillable())) {
            $issues[] = 'supplier_delivery_id not in IncomingOperation fillable fields';
        }

        $incomingItem = new IncomingOperationItem();
        if (!in_array('supplier_delivery_item_id', $incomingItem->getFillable())) {
            $issues[] = 'supplier_delivery_item_id not in IncomingOperationItem fillable fields';
        }

        // Check database columns exist
        try {
            DB::select('SELECT supplier_delivery_id FROM incoming_operations LIMIT 1');
        } catch (\Exception $e) {
            $issues[] = 'supplier_delivery_id column missing from incoming_operations table';
        }

        try {
            DB::select('SELECT supplier_delivery_item_id FROM incoming_operation_items LIMIT 1');
        } catch (\Exception $e) {
            $issues[] = 'supplier_delivery_item_id column missing from incoming_operation_items table';
        }

        if (count($issues) > 0) {
            $this->error('❌ Data integrity issues found:');
            foreach ($issues as $issue) {
                $this->line("  - {$issue}");
            }
        } else {
            $this->info('✅ Data integrity verified');
        }
    }

    /**
     * Test API response to ensure everything works
     */
    private function testApiResponse()
    {
        $this->info('🧪 Step 4: Testing API response...');

        $supplierDeliveries = IncomingOperation::where('operation_type', 'supplier_delivery')
            ->where('delivery_type', 'project_site')
            ->where('status', 'completed')
            ->first();

        if (!$supplierDeliveries) {
            $this->warn('No supplier deliveries found for testing');
            return;
        }

        try {
            $controller = new \App\Http\Controllers\Warehouse\IncomingOperationController();
            $response = $controller->getSupplierDelivery($supplierDeliveries->id);
            $data = json_decode($response->content(), true);

            if ($data['success']) {
                $this->info('✅ API response working correctly');

                foreach ($data['data']['items'] as $item) {
                    $this->line("  Item {$item['item_id']}: {$item['previously_returned']} returned, {$item['remaining_returnable']} available");
                }
            } else {
                $this->error('❌ API response error: ' . $data['message']);
            }
        } catch (\Exception $e) {
            $this->error('❌ API test failed: ' . $e->getMessage());
        }
    }
}