<?php

namespace App\Console\Commands;

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

class SyncInventoryPrices extends Command
{
    protected $signature = 'sync:inventory-prices {--dry-run : Show what would be changed without making changes} {--item= : Specific item ID to sync}';

    protected $description = 'Recalculate inventory unit_price and total_value based on weighted average from supplier deliveries';

    public function handle()
    {
        $dryRun = $this->option('dry-run');
        $specificItemId = $this->option('item');

        $this->info($dryRun ? 'DRY RUN - No changes will be made' : 'Syncing inventory prices...');

        $query = Inventory::with('item')
            ->where('quantity_available', '>', 0);

        if ($specificItemId) {
            $query->where('item_id', $specificItemId);
        }

        $inventories = $query->get();
        $updated = 0;
        $errors = 0;

        foreach ($inventories as $inventory) {
            try {
                // Get all supplier delivery items for this item and division
                $deliveryQuery = IncomingOperationItem::query()
                    ->join('incoming_operations', 'incoming_operation_items.incoming_operation_id', '=', 'incoming_operations.id')
                    ->where('incoming_operation_items.item_id', $inventory->item_id)
                    ->where('incoming_operations.operation_type', 'supplier_delivery')
                    ->where('incoming_operations.status', 'completed')
                    ->whereNotNull('incoming_operation_items.unit_price_aed')
                    ->where('incoming_operation_items.unit_price_aed', '>', 0);

                // Filter by division if set
                if ($inventory->division_id) {
                    $deliveryQuery->where('incoming_operations.division_id', $inventory->division_id);
                }

                // Filter by batch if set
                if ($inventory->batch_number) {
                    $deliveryQuery->where('incoming_operation_items.batch_number', $inventory->batch_number);
                }

                // Calculate weighted average from all deliveries
                $deliveryData = $deliveryQuery->selectRaw('
                    SUM(incoming_operation_items.quantity_delivered * incoming_operation_items.unit_price_aed) as total_value,
                    SUM(incoming_operation_items.quantity_delivered) as total_qty
                ')->first();

                if ($deliveryData && $deliveryData->total_qty > 0) {
                    $weightedAvgPrice = $deliveryData->total_value / $deliveryData->total_qty;
                    $newTotalValue = $inventory->quantity_available * $weightedAvgPrice;

                    $oldPrice = $inventory->unit_price;
                    $oldTotalValue = $inventory->total_value;

                    // Check if there's a significant difference
                    $priceDiff = abs($oldPrice - $weightedAvgPrice);
                    $valueDiff = abs($oldTotalValue - $newTotalValue);

                    if ($priceDiff > 0.01 || $valueDiff > 0.01) {
                        $itemCode = $inventory->item ? $inventory->item->item_code : 'Unknown';

                        $this->info("Item: {$itemCode} (ID: {$inventory->item_id})");
                        $this->info("  Division: {$inventory->division_id}, Batch: {$inventory->batch_number}");
                        $this->info("  Qty Available: {$inventory->quantity_available}");
                        $this->info("  Old Unit Price: {$oldPrice} -> New: " . number_format($weightedAvgPrice, 2));
                        $this->info("  Old Total Value: {$oldTotalValue} -> New: " . number_format($newTotalValue, 2));
                        $this->info("  Based on {$deliveryData->total_qty} units delivered");

                        if (!$dryRun) {
                            // Disable the model event to prevent auto-recalculation
                            $inventory->timestamps = false;
                            DB::table('inventory')
                                ->where('id', $inventory->id)
                                ->update([
                                    'unit_price' => $weightedAvgPrice,
                                    'total_value' => $newTotalValue,
                                    'updated_at' => now()
                                ]);
                            $this->info("  -> UPDATED");
                        } else {
                            $this->info("  -> Would be updated");
                        }

                        $updated++;
                    }
                } else {
                    // No delivery data found - log but don't error
                    $itemCode = $inventory->item ? $inventory->item->item_code : 'Unknown';
                    $this->warn("No delivery data found for item {$itemCode} (ID: {$inventory->item_id})");
                }
            } catch (\Exception $e) {
                $this->error("Error processing inventory ID {$inventory->id}: " . $e->getMessage());
                $errors++;
            }
        }

        $this->info('');
        $this->info("Summary:");
        $this->info("  Total inventories checked: " . $inventories->count());
        $this->info("  Records " . ($dryRun ? "that would be updated" : "updated") . ": {$updated}");
        $this->info("  Errors: {$errors}");

        return Command::SUCCESS;
    }
}
