<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use App\Models\PurchaseOrder;
use App\Models\ProductStock;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        echo "Starting Inventory Duplication Fix...\n";

        // Find all POs that have a specific location set and have been received (partial or full)
        $pos = PurchaseOrder::whereNotNull('location_id')
            ->whereIn('status', ['received', 'completed', 'partial'])
            ->with('items')
            ->cursor();

        $count = 0;
        $totalDeducted = 0;

        foreach ($pos as $po) {
            foreach ($po->items as $item) {
                // The quantity that was erroneously added to the NULL location inventory
                $qtyReceived = $item->received_qty;
                
                if ($qtyReceived > 0) {
                    // Find the Unassigned Inventory (Location NULL) for this product and branch
                    $inventory = ProductStock::where('product_id', $item->product_id)
                        ->where('branch_id', $po->branch_id)
                        ->whereNull('location_id')
                        ->first();
                    
                    if ($inventory) {
                        $oldQty = $inventory->quantity;
                        // Deduct, but ensure we don't go below 0
                        $newQty = max(0, $oldQty - $qtyReceived);
                        
                        if ($oldQty != $newQty) {
                            $inventory->update(['quantity' => $newQty]);
                            $deducted = $oldQty - $newQty;
                            $totalDeducted += $deducted;
                            echo "Corrected Product {$item->product_id}: Unassigned Qty {$oldQty} -> {$newQty} (Deducted {$deducted})\n";
                        }
                    }
                }
            }
            $count++;
        }

        echo "Processed {$count} POs. Total units deducted from unassigned stock: {$totalDeducted}\n";
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Reversal is complex because we don't trace exactly which units were deducted easily without a log.
        // For this hotfix, we assume down() is not strictly required strictly for data fix, 
        // or we would need to add the quantities back.
    }
};
