app/sql/apply_bulk_stock_movements.sql

-- FUNCTION: trans.apply_bulk_stock_movements(jsonb)

-- DROP FUNCTION IF EXISTS trans.apply_bulk_stock_movements(jsonb);

CREATE OR REPLACE FUNCTION trans.apply_bulk_stock_movements(
	p_movements jsonb)
    RETURNS jsonb
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
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 using the 9-parameter function
            PERFORM trans.apply_stock_movement(
                (v_movement->>'merchant_id')::TEXT,
                (v_movement->>'warehouse_id')::TEXT,
                (v_movement->>'sku')::TEXT,
                (v_movement->>'batch_no')::TEXT,
				(v_movement->>'catalogue_id')::TEXT,
				(v_movement->>'expiry_date')::DATE,
				(v_movement->>'uom')::TEXT,
                (v_movement->>'qty')::NUMERIC,
                (v_movement->>'txn_type')::TEXT,
                (v_movement->>'ref_type')::TEXT,
                (v_movement->>'ref_id')::UUID,
                (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;
$BODY$;

ALTER FUNCTION trans.apply_bulk_stock_movements(jsonb)
    OWNER TO trans_owner;