app/sql/reserve_stock.sql

-- FUNCTION: trans.reserve_stock(character varying, character varying, character varying, character varying, numeric, uuid, character varying)

-- DROP FUNCTION IF EXISTS trans.reserve_stock(character varying, character varying, character varying, character varying, numeric, uuid, character varying);

CREATE OR REPLACE FUNCTION trans.reserve_stock(
	p_merchant_id character varying,
	p_warehouse_id character varying,
	p_sku character varying,
	p_batch_no character varying,
	p_qty numeric,
	p_ref_id uuid,
	p_created_by character varying)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_available_qty NUMERIC(14,3);
BEGIN
    -- Get current available quantity
    SELECT qty_available
    INTO v_available_qty
    FROM trans.scm_stock
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    -- Check if sufficient stock available
    IF v_available_qty IS NULL OR v_available_qty < p_qty THEN
        RAISE EXCEPTION 'Insufficient available stock for SKU: %. Available: %, Required: %', 
            p_sku, COALESCE(v_available_qty, 0), p_qty;
    END IF;
    
    -- Update stock reservation
    UPDATE trans.scm_stock
    SET qty_reserved = qty_reserved + p_qty,
        qty_available = qty_available - p_qty,
        last_updated_at = NOW()
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    RETURN TRUE;
END;
$BODY$;

ALTER FUNCTION trans.reserve_stock(character varying, character varying, character varying, character varying, numeric, uuid, character varying)
    OWNER TO trans_owner;