<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

return new class extends Migration
{
    /**
     * Fix missing DivisionDelivery records for items that were adjusted
     * (stock increased) but had no existing DivisionDelivery entry.
     */
    public function up(): void
    {
        // Find all inventory records with stock that have NO matching DivisionDelivery record
        $missingRecords = DB::table('inventory')
            ->leftJoin('division_deliveries', function ($join) {
                $join->on('inventory.item_id', '=', 'division_deliveries.item_id')
                     ->on('inventory.division_id', '=', 'division_deliveries.division_id');
            })
            ->whereNotNull('inventory.division_id')
            ->where('inventory.quantity_available', '>', 0)
            ->whereNull('division_deliveries.id')
            ->select(
                'inventory.item_id',
                'inventory.division_id',
                'inventory.quantity_available',
                'inventory.unit_price',
                'inventory.created_at'
            )
            ->get();

        $created = 0;
        foreach ($missingRecords as $record) {
            // Sum up total quantity across all inventory entries for this item+division
            $totalQty = DB::table('inventory')
                ->where('item_id', $record->item_id)
                ->where('division_id', $record->division_id)
                ->sum('quantity_available');

            $avgPrice = DB::table('inventory')
                ->where('item_id', $record->item_id)
                ->where('division_id', $record->division_id)
                ->avg('unit_price') ?? 0;

            DB::table('division_deliveries')->insert([
                'division_id' => $record->division_id,
                'item_id' => $record->item_id,
                'total_quantity_delivered' => $totalQty,
                'current_available_quantity' => $totalQty,
                'total_quantity_issued' => 0,
                'average_unit_price' => $avgPrice,
                'total_value' => $totalQty * $avgPrice,
                'first_delivery_date' => $record->created_at,
                'last_delivery_date' => now(),
                'last_updated' => now(),
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            $created++;
        }

        Log::info("Fix migration: Created {$created} missing DivisionDelivery records from inventory adjustments.");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // No reverse needed - these records should have existed
    }
};
