<?php
require __DIR__ . '/vendor/autoload.php';
$app = require_once __DIR__ . '/bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

$projectId = 681;
$itemId = 2114;

echo "=== CALCULATING CORRECT PROJECT INVENTORY ===\n\n";

// From inventory dispatches (OutgoingTransaction with status successful)
$inventoryDispatch = DB::table('outgoing_items')
    ->join('outgoing_transactions', 'outgoing_items.outgoing_transaction_id', '=', 'outgoing_transactions.id')
    ->where('outgoing_transactions.project_id', $projectId)
    ->where('outgoing_items.item_id', $itemId)
    ->whereIn('outgoing_transactions.status', ['successful', 'completed'])
    ->where('outgoing_items.quantity_released', '>', 0)
    ->sum('outgoing_items.quantity_released');

echo "1. Inventory Dispatch (MR-INV): +{$inventoryDispatch}\n";

// From Direct Deliveries
$directDelivery = DB::table('direct_delivery_items')
    ->join('direct_deliveries', 'direct_delivery_items.direct_delivery_id', '=', 'direct_deliveries.id')
    ->where('direct_deliveries.project_id', $projectId)
    ->where('direct_delivery_items.item_id', $itemId)
    ->whereIn('direct_deliveries.status', ['delivered', 'completed'])
    ->sum('direct_delivery_items.delivered_quantity');

echo "2. Direct Deliveries: +{$directDelivery}\n";

// From Supplier Deliveries direct to project
$supplierDelivery = DB::table('incoming_operation_items')
    ->join('incoming_operations', 'incoming_operation_items.incoming_operation_id', '=', 'incoming_operations.id')
    ->where('incoming_operations.project_id', $projectId)
    ->where('incoming_operations.operation_type', 'supplier_delivery')
    ->where('incoming_operation_items.item_id', $itemId)
    ->where('incoming_operations.status', 'completed')
    ->sum(DB::raw('GREATEST(incoming_operation_items.quantity_delivered, incoming_operation_items.quantity_accepted)'));

echo "3. Supplier Delivery to Project: +{$supplierDelivery}\n";

// Site Transfers IN
$transfersIn = DB::table('material_site_transfer_items')
    ->join('material_site_transfers', 'material_site_transfer_items.material_site_transfer_id', '=', 'material_site_transfers.id')
    ->where('material_site_transfers.to_project_id', $projectId)
    ->where('material_site_transfer_items.item_id', $itemId)
    ->where('material_site_transfers.status', 'completed')
    ->sum('material_site_transfer_items.quantity_transferred');

echo "4. Site Transfers IN: +{$transfersIn}\n";

// Site Returns (subtract)
$siteReturns = DB::table('incoming_operation_items')
    ->join('incoming_operations', 'incoming_operation_items.incoming_operation_id', '=', 'incoming_operations.id')
    ->where('incoming_operations.project_id', $projectId)
    ->where('incoming_operations.operation_type', 'site_return')
    ->where('incoming_operation_items.item_id', $itemId)
    ->where('incoming_operations.status', 'completed')
    ->sum(DB::raw('GREATEST(incoming_operation_items.quantity_delivered, incoming_operation_items.quantity_accepted)'));

echo "5. Site Returns: -{$siteReturns}\n";

// Site Transfers OUT (subtract)
$transfersOut = DB::table('material_site_transfer_items')
    ->join('material_site_transfers', 'material_site_transfer_items.material_site_transfer_id', '=', 'material_site_transfers.id')
    ->where('material_site_transfers.from_project_id', $projectId)
    ->where('material_site_transfer_items.item_id', $itemId)
    ->where('material_site_transfers.status', 'completed')
    ->sum('material_site_transfer_items.quantity_transferred');

echo "6. Site Transfers OUT: -{$transfersOut}\n";

$expected = $inventoryDispatch + $directDelivery + $supplierDelivery + $transfersIn - $siteReturns - $transfersOut;

echo "\n=== RESULT ===\n";
echo "Expected: {$expected}\n";

$actual = DB::table('project_inventories')
    ->where('project_id', $projectId)
    ->where('item_id', $itemId)
    ->value('quantity_available');

echo "Actual: {$actual}\n";
echo "Difference: " . ($actual - $expected) . "\n";

// Fix it
if ($actual != $expected) {
    echo "\n=== FIXING PROJECT INVENTORY ===\n";
    DB::table('project_inventories')
        ->where('project_id', $projectId)
        ->where('item_id', $itemId)
        ->update(['quantity_available' => $expected]);
    echo "Updated project inventory from {$actual} to {$expected}\n";
}
