docs/database/sql/stored_procedures/stock_management_updated.sql

-- Stock Management Stored Procedures - UPDATED VERSION
-- Centralized inventory mutation logic in PostgreSQL with proper schema references

-- Simplified stock movement function with idempotency and proper schema references
CREATE OR REPLACE FUNCTION apply_stock_movement(
    p_merchant_id TEXT,
    p_warehouse_id TEXT,
    p_sku TEXT,
    p_batch_no TEXT,
    p_qty NUMERIC,
    p_txn_type TEXT,
    p_ref_type TEXT,
    p_ref_id UUID,
    p_ref_no TEXT,
    p_user TEXT
) RETURNS VOID AS $$
BEGIN
    -- Idempotency check - prevent duplicate processing
    IF EXISTS (
        SELECT 1 FROM trans.scm_stock_ledger 
        WHERE ref_type = p_ref_type AND ref_id = p_ref_id AND sku = p_sku AND batch_no = p_batch_no
    ) THEN
        RETURN;
    END IF;
    
    -- Insert ledger entry (immutable audit trail)
    INSERT INTO trans.scm_stock_ledger (
        ledger_id, merchant_id, warehouse_id, sku, batch_no,
        qty, txn_type, ref_type, ref_id, ref_no, created_by, created_at
    ) VALUES (
        gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
        p_qty, p_txn_type, p_ref_type, p_ref_id, p_ref_no, p_user, NOW()
    );
    
    -- Stock snapshot upsert (create or update)
    INSERT INTO trans.scm_stock (
        stock_id, merchant_id, warehouse_id, sku, batch_no,
        qty_on_hand, qty_reserved, qty_available, created_at, updated_at, last_updated_at
    ) VALUES (
        gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
        GREATEST(p_qty, 0), 0, GREATEST(p_qty, 0), NOW(), NOW(), NOW()
    )
    ON CONFLICT (merchant_id, warehouse_id, sku, batch_no)
    DO UPDATE SET
        qty_on_hand = trans.scm_stock.qty_on_hand + p_qty,
        qty_available = GREATEST(trans.scm_stock.qty_on_hand + p_qty - trans.scm_stock.qty_reserved, 0),
        updated_at = NOW(),
        last_updated_at = NOW();
END;
$$ LANGUAGE plpgsql;

-- Enhanced bulk stock movement function with proper error handling
CREATE OR REPLACE FUNCTION apply_bulk_stock_movements(
    p_movements JSONB
) RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_movement JSONB;
    v_results JSONB := '[]'::JSONB;
    v_result JSONB;
    v_success BOOLEAN := TRUE;
    v_error_msg TEXT;
BEGIN
    -- Process each movement in the array
    FOR v_movement IN SELECT * FROM jsonb_array_elements(p_movements)
    LOOP
        BEGIN
            -- Apply individual stock movement
            PERFORM apply_stock_movement(
                (v_movement->>'merchant_id')::TEXT,
                (v_movement->>'warehouse_id')::TEXT,
                (v_movement->>'sku')::TEXT,
                (v_movement->>'batch_no')::TEXT,
                (v_movement->>'qty')::NUMERIC,
                (v_movement->>'txn_type')::TEXT,
                (v_movement->>'ref_type')::TEXT,
                (v_movement->>'ref_id')::UUID,
                (v_movement->>'ref_no')::TEXT,
                (v_movement->>'created_by')::TEXT
            );
            
            -- Build success result object
            v_result := jsonb_build_object(
                'ledger_id', gen_random_uuid(),
                'sku', v_movement->>'sku',
                'qty', v_movement->>'qty',
                'success', true,
                'error', null
            );
            
        EXCEPTION WHEN OTHERS THEN
            -- Handle individual movement errors
            v_success := FALSE;
            v_error_msg := SQLERRM;
            
            v_result := jsonb_build_object(
                'ledger_id', null,
                'sku', v_movement->>'sku',
                'qty', v_movement->>'qty',
                'success', false,
                'error', v_error_msg
            );
        END;
        
        -- Add to results array
        v_results := v_results || v_result;
    END LOOP;
    
    -- If any movement failed, rollback the entire transaction
    IF NOT v_success THEN
        RAISE EXCEPTION 'One or more stock movements failed. Transaction rolled back.';
    END IF;
    
    RETURN v_results;
END;
$$;

-- Function to reserve stock (for sales orders) with proper schema
CREATE OR REPLACE FUNCTION reserve_stock(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64),
    p_sku VARCHAR(64),
    p_batch_no VARCHAR(50),
    p_qty NUMERIC(14,3),
    p_ref_id UUID,
    p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
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,
        updated_at = NOW(),
        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;
$$;

-- Function to release stock reservation with proper schema
CREATE OR REPLACE FUNCTION release_stock_reservation(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64),
    p_sku VARCHAR(64),
    p_batch_no VARCHAR(50),
    p_qty NUMERIC(14,3),
    p_ref_id UUID,
    p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Update stock reservation
    UPDATE trans.scm_stock
    SET qty_reserved = GREATEST(qty_reserved - p_qty, 0),
        qty_available = qty_on_hand - GREATEST(qty_reserved - p_qty, 0),
        updated_at = NOW(),
        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;
$$;

-- Function to get current stock summary with proper schema
CREATE OR REPLACE FUNCTION get_stock_summary(
    p_merchant_id VARCHAR(64),
    p_warehouse_id VARCHAR(64) DEFAULT NULL,
    p_sku VARCHAR(64) DEFAULT NULL
) RETURNS TABLE (
    stock_id UUID,
    warehouse_id VARCHAR(64),
    catalogue_id UUID,
    sku VARCHAR(64),
    batch_no VARCHAR(50),
    qty_on_hand NUMERIC(14,3),
    qty_reserved NUMERIC(14,3),
    qty_available NUMERIC(14,3),
    uom VARCHAR(10),
    last_updated_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT s.stock_id, s.warehouse_id, s.catalogue_id, s.sku, s.batch_no,
           s.qty_on_hand, s.qty_reserved, s.qty_available, s.uom, s.last_updated_at
    FROM trans.scm_stock s
    WHERE s.merchant_id = p_merchant_id
      AND (p_warehouse_id IS NULL OR s.warehouse_id = p_warehouse_id)
      AND (p_sku IS NULL OR s.sku = p_sku)
    ORDER BY s.sku, s.batch_no;
END;
$$;

-- Function to get stock ledger history with proper schema
CREATE OR REPLACE FUNCTION get_stock_ledger(
    p_merchant_id VARCHAR(64),
    p_sku VARCHAR(64) DEFAULT NULL,
    p_batch_no VARCHAR(50) DEFAULT NULL,
    p_limit INTEGER DEFAULT 100
) RETURNS TABLE (
    ledger_id UUID,
    warehouse_id VARCHAR(64),
    catalogue_id UUID,
    sku VARCHAR(64),
    batch_no VARCHAR(50),
    txn_type VARCHAR(30),
    qty NUMERIC(14,3),
    uom VARCHAR(10),
    ref_type VARCHAR(30),
    ref_id UUID,
    ref_no VARCHAR(50),
    remarks TEXT,
    created_by VARCHAR(64),
    created_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT l.ledger_id, l.warehouse_id, l.catalogue_id, l.sku, l.batch_no,
           l.txn_type, l.qty, l.uom, l.ref_type, l.ref_id, l.ref_no,
           l.remarks, l.created_by, l.created_at
    FROM trans.scm_stock_ledger l
    WHERE l.merchant_id = p_merchant_id
      AND (p_sku IS NULL OR l.sku = p_sku)
      AND (p_batch_no IS NULL OR l.batch_no = p_batch_no)
    ORDER BY l.created_at DESC
    LIMIT p_limit;
END;
$$;