app/sql/apply_stock_movement.sql

-- FUNCTION: trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text)

-- DROP FUNCTION IF EXISTS trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text);

CREATE OR REPLACE FUNCTION trans.apply_stock_movement(
	p_merchant_id text,
	p_warehouse_id text,
	p_sku text,
	p_batch_no text,
	p_catalogue_id text,
	p_expiry_date date,
	p_uom text,
	p_qty numeric,
	p_txn_type text,
	p_ref_type text,
	p_ref_id uuid,
	p_user text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_ledger_id uuid := gen_random_uuid();
    v_qty_change numeric;
BEGIN
    ----------------------------------------------------------------
    -- Idempotency check
    ----------------------------------------------------------------
    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;

    ----------------------------------------------------------------
    -- Determine quantity change based on transaction type
    ----------------------------------------------------------------
    CASE p_txn_type
        WHEN 'GRN_IN' THEN
            v_qty_change := p_qty;  -- Add stock
        WHEN 'ADJUST_IN' THEN
            v_qty_change := p_qty;  -- Add stock
        WHEN 'RETURN_IN' THEN
            v_qty_change := p_qty;  -- Add stock
        WHEN 'TRANSFER_IN' THEN
            v_qty_change := p_qty;  -- Add stock
        WHEN 'ADJUST_OUT' THEN
            v_qty_change := -p_qty; -- Subtract stock (negate the positive qty)
        WHEN 'SALE_OUT' THEN
            v_qty_change := -p_qty; -- Subtract stock
        WHEN 'TRANSFER_OUT' THEN
            v_qty_change := -p_qty; -- Subtract stock
        ELSE
            RAISE EXCEPTION 'Unknown transaction type: %', p_txn_type;
    END CASE;

	Raise Notice 'in';
    RAISE NOTICE 'Transaction type: %, Input qty: %, Calculated change: %', 
        p_txn_type, p_qty, v_qty_change;
    
    RAISE NOTICE 'Inserting ledger: %, %, %, %, %, %, %, %, %, %, %',
        v_ledger_id,
        p_merchant_id,
        p_warehouse_id,
        p_sku,
        p_batch_no,
        p_txn_type,
        v_qty_change,  -- Store the calculated change in ledger
        p_ref_type,
        p_ref_id,
        p_user,
        NOW();
        
    ----------------------------------------------------------------
    -- Ledger insert (store the ACTUAL qty change with proper sign)
    ----------------------------------------------------------------
    INSERT INTO trans.scm_stock_ledger (
        ledger_id,
        merchant_id,
        warehouse_id,
        sku,
        batch_no,
        txn_type,
        qty,
        ref_type,
        ref_id,
        created_by,
        created_at
    ) VALUES (
        v_ledger_id,
        p_merchant_id,
        p_warehouse_id,
        p_sku,
        p_batch_no,
        p_txn_type,
        v_qty_change,  -- Store with correct sign
        p_ref_type,
        p_ref_id,
        p_user,
        NOW()
    );
    
    RAISE NOTICE 'Stock ledger done';
    
    ----------------------------------------------------------------
    -- Stock snapshot upsert (ALL TABLE COLUMNS)
    ----------------------------------------------------------------
    INSERT INTO trans.scm_stock (
        merchant_id,
        warehouse_id,
        sku,
        batch_no,
        catalogue_id,
        uom,
        qty_on_hand,
        qty_reserved,
        qty_available,
        cost_price,
        expiry_date,
        ledger_id,
        created_at,
        updated_at
    ) VALUES (
        p_merchant_id,
        p_warehouse_id,
        p_sku,
        p_batch_no,
        p_catalogue_id,
        p_uom,
        GREATEST(v_qty_change, 0),  -- Use calculated change (don't allow negative on insert)
        0,                           -- qty_reserved
        GREATEST(v_qty_change, 0),  -- qty_available
        NULL,                        -- cost_price
        p_expiry_date,              -- expiry_date
        v_ledger_id,
        NOW(),
        NOW()
    )
    ON CONFLICT (merchant_id, warehouse_id, catalogue_id, batch_no)
    DO UPDATE SET
        qty_on_hand   = GREATEST(trans.scm_stock.qty_on_hand + v_qty_change, 0),
        qty_available = GREATEST((trans.scm_stock.qty_on_hand + v_qty_change) - trans.scm_stock.qty_reserved, 0),
        ledger_id     = v_ledger_id,
        updated_at    = NOW();
        
    RAISE NOTICE 'Stock updated. Final qty change applied: %', v_qty_change;
END;
$BODY$;

ALTER FUNCTION trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text)
    OWNER TO trans_owner;