Notes

public function updateWarehouseStatus(UpdateCreateItemAlongWarehouseRequest $req)
{
try {

Log::info('===== updateWarehouseStatus START =====');

// --------------------------------------------------
// STEP 0: Reset tables (as per your existing logic)
// --------------------------------------------------
Schema::disableForeignKeyConstraints();
Inventory::truncate();
ItemAlongWarehouse::truncate();
Schema::enableForeignKeyConstraints();

Log::info('Tables truncated: new_inventory, item_along_warehouses');

// --------------------------------------------------
// STEP 1: Insert data into new_inventory (UNCHANGED)
// --------------------------------------------------
$resultArray = [];

foreach ($req->data as $val) {

$itemCodeValue = $val['Item_Code'] ?? null;
if (empty($itemCodeValue)) {
continue;
}

$resultArray[$itemCodeValue] = [
'Company_Code' => $val['Company_Code'] ?? null,
'Warehouse_ID' => $val['warehouse_ids_data'] ?? null,
'Item_Code' => $itemCodeValue,
'Item_Description' => isset($val['Item_Description'])
? html_entity_decode($val['Item_Description'], ENT_QUOTES | ENT_HTML5)
: null,
'Item_Category' => $val['Item_Category'] ?? null,
'Standard_Cost' => (float) ($val['Standard_Cost'] ?? 0),
'created_by' => Auth::user()->id,
'updated_by' => Auth::user()->id,
'created_at' => now(),
'updated_at' => now(),
];
}

foreach (array_chunk($resultArray, 1000) as $chunk) {
DB::table('new_inventory')->insert($chunk);
}

Log::info('Inserted into new_inventory', [
'rows' => count($resultArray)
]);

// --------------------------------------------------
// STEP 2: Fetch ALL item codes from new_inventory
// --------------------------------------------------
$inventoryItems = DB::table('new_inventory')
->select('id', 'Item_Code')
->get();

Log::info('New Inventory rows', [
'count' => $inventoryItems->count()
]);

// --------------------------------------------------
// STEP 3: Fetch ALL item codes from master table
// --------------------------------------------------
$masterItemCodes = DB::table('master_items_phase_code')
->pluck('item_code');

Log::info('Master Items Phase Code rows', [
'count' => $masterItemCodes->count()
]);

// --------------------------------------------------
// STEP 4: Find unmatched item codes
// --------------------------------------------------
$unmatchedItemCodes = $inventoryItems
->pluck('Item_Code')
->diff($masterItemCodes);

Log::info('Unmatched item codes', [
'count' => $unmatchedItemCodes->count()
]);

// --------------------------------------------------
// STEP 5: Fetch unmatched inventory rows
// --------------------------------------------------
$unmatchedInventoryRows = DB::table('new_inventory')
->whereIn('Item_Code', $unmatchedItemCodes)
->select('Item_Code', 'Item_Description', 'Item_Category', 'Warehouse_ID')
->get();

Log::info('Unmatched inventory rows fetched', [
'count' => $unmatchedInventoryRows->count()
]);

// --------------------------------------------------
// STEP 6: Prepare insert data (UNIQUE BY Item_Code)
// --------------------------------------------------
$insertPhaseCodeRows = [];

foreach ($unmatchedInventoryRows as $row) {

// Force uniqueness by item_code
if (isset($insertPhaseCodeRows[$row->Item_Code])) {
continue;
}

$insertPhaseCodeRows[$row->Item_Code] = [
'item_code' => $row->Item_Code,
'item_description' => $row->Item_Description,
'phase_code' => 0,
'stone' => 0,
'stucco' => 0,
'use' => 'Y',
'category' => $row->Item_Category ?? null,
'warehouse' => $row->Warehouse_ID ?? null,
'created_at' => now(),
'updated_at' => now(),
];
}

Log::info('Prepared rows for master_items_phase_code insert', [
'count' => count($insertPhaseCodeRows)
]);

// --------------------------------------------------
// STEP 7: Insert into master_items_phase_code
// --------------------------------------------------
$totalInserted = 0;

foreach (array_chunk($insertPhaseCodeRows, 1000) as $chunk) {
DB::table('master_items_phase_code')->insert($chunk);
$totalInserted += count($chunk);
}

Log::info('Inserted into master_items_phase_code', [
'inserted' => $totalInserted
]);

Log::info('===== updateWarehouseStatus END =====');

return response()->json([
'success' => true,
'message' => 'Inventory and phase code data processed successfully',
'summary' => [
'inventory_rows' => $inventoryItems->count(),
'master_existing_rows' => $masterItemCodes->count(),
'unmatched_items' => $unmatchedItemCodes->count(),
'inserted_into_master' => $totalInserted,
]
], 200);

} catch (Exception $e) {

Log::error('updateWarehouseStatus ERROR', [
'message' => $e->getMessage(),
'trace' => $e->getTraceAsString()
]);

return response()->json([
'success' => false,
'message' => $e->getMessage()
], 500);
}
}