<?php
// ===============================
// UPDATE STOCK ON SALE
// ===============================
function update_stock_on_sale($conn, $sale_id)
{
    // Fetch all items of this sale
    $items = mysqli_query($conn, "
        SELECT product_id, qty
        FROM hps_sale_items
        WHERE sale_id = $sale_id
    ");

    while ($row = mysqli_fetch_assoc($items)) {

        $product_id = (int)$row['product_id'];
        $qty_sold   = (float)$row['qty'];

        // Check if stock row exists
        $stock = mysqli_fetch_assoc(mysqli_query($conn, "
            SELECT qty_on_hand
            FROM hps_stock
            WHERE product_id = $product_id
        "));

        if ($stock) {
            // Deduct stock (prevent negative)
            mysqli_query($conn, "
                UPDATE hps_stock
                SET qty_on_hand = GREATEST(qty_on_hand - $qty_sold, 0),
                    updated_at = CURRENT_TIMESTAMP
                WHERE product_id = $product_id
            ");
        } else {
            // If stock row missing, create it with negative or zero
            mysqli_query($conn, "
                INSERT INTO hps_stock (product_id, qty_on_hand, avg_cost)
                VALUES ($product_id, 0, 0)
            ");
        }
    }
}
