<?php
function update_all_bom_costs($conn) {
    $boms = $conn->query("SELECT item_id FROM bom_master");

    while ($row = $boms->fetch_assoc()) {
        $item_id = (int)$row['item_id'];

        $bomResult = $conn->query("SELECT id FROM bom_master WHERE item_id = $item_id LIMIT 1");
        if (!$bomResult || $bomResult->num_rows === 0) continue;

        $bom_id = $bomResult->fetch_assoc()['id'];

        $total_bom_cost = 0;
        $query = "
            SELECT bc.quantity, i.cost_price
            FROM bom_components bc
            JOIN items i ON bc.component_item_id = i.id
            WHERE bc.bom_id = $bom_id
        ";
        $components = $conn->query($query);
        while ($comp = $components->fetch_assoc()) {
            $qty = floatval($comp['quantity']);
            $unit_cost = floatval($comp['cost_price']);
            $total_bom_cost += ($qty * $unit_cost);
        }

        $update = $conn->prepare("UPDATE items SET cost_price = ? WHERE id = ?");
        $update->bind_param("di", $total_bom_cost, $item_id);
        $update->execute();
    }
}
?>
