<?php

require_once __DIR__ . '/vendor/autoload.php';

$app = require_once __DIR__ . '/bootstrap/app.php';
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();

use Illuminate\Support\Facades\DB;
use App\Models\Warehouse\ProjectInventory;

try {
    echo "=== Site Returns Project Inventory Sync Script ===" . PHP_EOL;
    echo "Starting sync of existing site returns to project inventory..." . PHP_EOL . PHP_EOL;

    // Get all completed site return operations
    $siteReturns = DB::table('incoming_operations')
        ->join('incoming_operation_items', 'incoming_operations.id', '=', 'incoming_operation_items.incoming_operation_id')
        ->select(
            'incoming_operations.id as operation_id',
            'incoming_operations.project_id',
            'incoming_operations.operation_number',
            'incoming_operations.operation_date',
            'incoming_operation_items.item_id',
            'incoming_operation_items.quantity_accepted',
            'incoming_operation_items.unit_price_aed'
        )
        ->where('incoming_operations.operation_type', 'site_return')
        ->where('incoming_operations.status', 'completed')
        ->where('incoming_operation_items.quantity_accepted', '>', 0)
        ->whereNotNull('incoming_operations.project_id')
        ->orderBy('incoming_operations.project_id')
        ->orderBy('incoming_operation_items.item_id')
        ->get();

    echo "Found " . $siteReturns->count() . " site return items to sync..." . PHP_EOL . PHP_EOL;

    if ($siteReturns->isEmpty()) {
        echo "No site return operations found to sync." . PHP_EOL;
        exit;
    }

    $processedCount = 0;
    $updatedRecords = 0;
    $deletedRecords = 0;
    $missingRecords = 0;
    $currentProject = null;
    $currentItem = null;

    foreach ($siteReturns as $return) {
        if ($currentProject !== $return->project_id) {
            echo PHP_EOL . "--- Processing Project ID: {$return->project_id} ---" . PHP_EOL;
            $currentProject = $return->project_id;
            $currentItem = null;
        }

        if ($currentItem !== $return->item_id) {
            echo "Processing Item ID: {$return->item_id}" . PHP_EOL;
            $currentItem = $return->item_id;
        }

        // Check if project inventory record exists
        $projectInventory = ProjectInventory::where('project_id', $return->project_id)
            ->where('item_id', $return->item_id)
            ->first();

        if ($projectInventory) {
            $oldQuantity = $projectInventory->quantity_available;
            $projectInventory->updateQuantity($return->quantity_accepted, 'subtract');
            $newQuantity = $projectInventory->quantity_available;

            if ($newQuantity <= 0) {
                $projectInventory->delete();
                echo "  - Deleted: Qty {$oldQuantity} - {$return->quantity_accepted} = {$newQuantity} (removed record)" . PHP_EOL;
                $deletedRecords++;
            } else {
                echo "  - Updated: Qty {$oldQuantity} - {$return->quantity_accepted} = {$newQuantity}" . PHP_EOL;
                $updatedRecords++;
            }
        } else {
            echo "  - WARNING: No project inventory found for Item {$return->item_id} in Project {$return->project_id}" . PHP_EOL;
            $missingRecords++;
        }

        $processedCount++;
    }

    echo PHP_EOL . "=== Site Returns Sync Complete ===" . PHP_EOL;
    echo "Total site return items processed: {$processedCount}" . PHP_EOL;
    echo "Project inventory records updated: {$updatedRecords}" . PHP_EOL;
    echo "Project inventory records deleted (zero quantity): {$deletedRecords}" . PHP_EOL;
    echo "Missing project inventory records (warnings): {$missingRecords}" . PHP_EOL;

    // Display updated summary of project inventory after site returns sync
    echo PHP_EOL . "=== Updated Project Inventory Summary ===" . PHP_EOL;
    $projectInventorySummary = ProjectInventory::select(
        'project_id',
        DB::raw('COUNT(*) as item_count'),
        DB::raw('SUM(quantity_available) as total_quantity'),
        DB::raw('SUM(total_value) as total_value')
    )
    ->groupBy('project_id')
    ->orderBy('project_id')
    ->get();

    foreach ($projectInventorySummary as $summary) {
        echo "Project {$summary->project_id}: {$summary->item_count} items, Total Qty: {$summary->total_quantity}, Total Value: " . number_format($summary->total_value, 2) . " AED" . PHP_EOL;
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . PHP_EOL;
    echo "Trace: " . $e->getTraceAsString() . PHP_EOL;
}