USE db_abos_v0.1;

-- =============================================================================================
-- web_store_stock_reservation.sql — cart-reservation support for the storefront checkout flow
-- (companion to web_store_module.sql / inventory_module.sql, applied after both).
--
-- fact_inventory_stock.quantity_reserved already exists (inventory_module.sql) but nothing ever
-- set it — there was no cart-reservation flow at all, so two shoppers could both "successfully"
-- add the last unit to their carts and only the first to actually pay would get it. This adds a
-- cached mirror of the reserved total on dim_web_product_variants — reserved_qty — the exact same
-- "maintained rollup, cheap to read from the storefront" role stock_qty already plays for
-- quantity_on_hand (see InventoryService.syncVariantStockCache). Available-to-sell for storefront
-- display is stock_qty - reserved_qty, computed at the read site (PublicWebService), not stored.
--
-- Safe to re-run: information_schema-guarded ALTER.
-- =============================================================================================

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_product_variants' AND COLUMN_NAME = 'reserved_qty'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_product_variants ADD COLUMN reserved_qty INT NOT NULL DEFAULT 0 AFTER stock_qty',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;