database_scripts/02_functions.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,
                (v_movement->>'ref_no')::TEXT,
                (v_movement->>'remarks')::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;
-- FUNCTION: trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text, text, text)

DROP FUNCTION IF EXISTS 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, text, 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,
    p_ref_no text DEFAULT NULL,
    p_remarks text DEFAULT NULL)
    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,
        catalogue_id,
        sku,
        batch_no,
        txn_type,
        qty,
        uom,
        ref_type,
        ref_id,
        ref_no,
        remarks,
        created_by,
        created_at
    ) VALUES (
        v_ledger_id,
        p_merchant_id,
        p_warehouse_id,
        p_catalogue_id,
        p_sku,
        p_batch_no,
        p_txn_type,
        v_qty_change,  -- Store with correct sign
        p_uom,
        p_ref_type,
        p_ref_id,
        p_ref_no,
        p_remarks,
        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, text, text)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_item_details(uuid)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_item_details(uuid);

CREATE OR REPLACE FUNCTION trans.fn_get_po_item_details(
	p_po_id uuid)
    RETURNS TABLE(po_id uuid, catalogue_code text, catalogue_name text, hsn_code text, gst_rate numeric, ord_qty numeric, dispatched_qty numeric, rcvd_qty numeric, rejected_qty numeric, returned_qty numeric, net_accepted_qty numeric, unit_price numeric, taxable_amount numeric, gst_amount numeric, final_amount numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        poi.po_id,

        cr.catalogue_code,
        cr.catalogue_name,
        cr.hsn_code,
        cr.gst_rate,

        poi.ord_qty,
        poi.dispatched_qty,
        poi.rcvd_qty,
        poi.rejected_qty,
        poi.returned_qty,
        (poi.rcvd_qty - poi.rejected_qty - poi.returned_qty) AS net_accepted_qty,

        poi.unit_price,
        poi.line_amt AS taxable_amount,
        poi.tax_amt AS gst_amount,

        (poi.line_amt + poi.tax_amt) AS final_amount
    FROM trans.scm_po_item poi
    JOIN trans.catalogue_ref cr
        ON cr.catalogue_id = poi.catalogue_id
    WHERE poi.po_id = p_po_id;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_item_details(uuid)
    OWNER TO trans_owner;

-- FUNCTION: trans.fn_get_po_item_details(uuid)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_item_details(uuid);

CREATE OR REPLACE FUNCTION trans.fn_get_po_item_details(
	p_po_id uuid)
    RETURNS TABLE(po_id uuid, catalogue_code text, catalogue_name text, hsn_code text, gst_rate numeric, ord_qty numeric, dispatched_qty numeric, rcvd_qty numeric, rejected_qty numeric, returned_qty numeric, net_accepted_qty numeric, unit_price numeric, taxable_amount numeric, gst_amount numeric, final_amount numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        poi.po_id,

        cr.catalogue_code,
        cr.catalogue_name,
        cr.hsn_code,
        cr.gst_rate,

        poi.ord_qty,
        poi.dispatched_qty,
        poi.rcvd_qty,
        poi.rejected_qty,
        poi.returned_qty,
        (poi.rcvd_qty - poi.rejected_qty - poi.returned_qty) AS net_accepted_qty,

        poi.unit_price,
        poi.line_amt AS taxable_amount,
        poi.tax_amt AS gst_amount,

        (poi.line_amt + poi.tax_amt) AS final_amount
    FROM trans.scm_po_item poi
    JOIN trans.catalogue_ref cr
        ON cr.catalogue_id = poi.catalogue_id
    WHERE poi.po_id = p_po_id;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_item_details(uuid)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_items_for_grn(uuid)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_items_for_grn(uuid);

CREATE OR REPLACE FUNCTION trans.fn_get_po_items_for_grn(
	p_po_id uuid)
    RETURNS TABLE(po_item_id uuid, catalogue_id uuid, catalogue_name text, ordered_qty numeric, received_qty numeric, rejected_qty numeric, pending_grn numeric, cost_price numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        po.po_item_id AS po_item_id,
        po.catalogue_id,
        cr.catalogue_name,
        po.ord_qty        AS ordered_qty,
        po.rcvd_qty       AS received_qty,
        po.rejected_qty,
        (po.ord_qty - po.rcvd_qty - po.rejected_qty) AS pending_grn,
        po.unit_price     AS cost_price
    FROM trans.scm_po_item po
    JOIN trans.catalogue_ref cr
        ON po.catalogue_id = cr.catalogue_id::uuid
    WHERE po.po_id = p_po_id
      AND (po.ord_qty - po.rcvd_qty - po.rejected_qty) > 0
    ORDER BY cr.catalogue_name;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_items_for_grn(uuid)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_items_for_order_process(uuid, text, text)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_items_for_order_process(uuid, text, text);

CREATE OR REPLACE FUNCTION trans.fn_get_po_items_for_order_process(
	p_po_id uuid,
	p_warehouse_id text,
	p_mode text)
    RETURNS TABLE(po_item_id uuid, catalogue_id uuid, catalogue_name text, ordered_qty numeric, received_qty numeric, dispatched_qty numeric, pending_qty numeric, qty_available numeric, batch_no character varying, expiry_date date, unit_price numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    IF p_mode = 'DISPATCH' THEN

        RETURN QUERY
                SELECT
                        po.po_item_id,
                        po.catalogue_id,
                        cr.catalogue_name,
                        po.ord_uom_qty,
                        po.rcvd_qty,
                        0::numeric AS dispatched_qty,
                        (po.ord_uom_qty - 0::numeric) AS pending_qty,
                        st.qty_available,
                        st.batch_no,
                        st.expiry_date,
                        po.unit_price
                FROM trans.scm_po_item po
        JOIN trans.catalogue_ref cr
            ON po.catalogue_id = cr.catalogue_id
        JOIN trans.scm_stock st
            ON st.catalogue_id = po.catalogue_id
           AND st.warehouse_id = p_warehouse_id
        WHERE po.po_id = p_po_id
                      AND (po.ord_uom_qty - 0::numeric) > 0
          AND st.qty_available > 0
        ORDER BY cr.catalogue_name, st.expiry_date;

    ELSIF p_mode = 'INVOICE' THEN

        RETURN QUERY
                SELECT
                        po.po_item_id,
                        po.catalogue_id,
                        cr.catalogue_name,
                        po.ord_uom_qty,
                        po.rcvd_qty,
                        0::numeric AS dispatched_qty,
                        (po.ord_uom_qty - 0::numeric) AS pending_qty,
                        NULL::numeric AS qty_available,
                        NULL::character varying(50)    AS batch_no,
                        NULL::date    AS expiry_date,
                        po.unit_price
                FROM trans.scm_po_item po
        JOIN trans.catalogue_ref cr
            ON po.catalogue_id = cr.catalogue_id
        WHERE po.po_id = p_po_id
                    AND (po.ord_uom_qty - po.returned_qty) > 0
        ORDER BY cr.catalogue_name;

    ELSE
        RAISE EXCEPTION 'Invalid p_mode: %', p_mode;
    END IF;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_items_for_order_process(uuid, text, text)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_items_for_purchase_return(uuid, character varying)

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

CREATE OR REPLACE FUNCTION trans.fn_get_po_items_for_purchase_return(
	p_po_id uuid,
	p_warehouse_id character varying)
    RETURNS TABLE(
        po_item_id uuid, 
        catalogue_id uuid, 
        catalogue_name text, 
        sku text,
        ordered_qty numeric,
        received_qty numeric, 
        returned_qty numeric, 
        dispatched_qty numeric, 
        returnable_qty numeric, 
        batch_no text, 
        expiry_date date, 
        qty_available numeric, 
        unit_price numeric
    ) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        pi.po_item_id,
        pi.catalogue_id,
        cr.catalogue_name,
        pi.sku::text,
        pi.ord_qty,
        pi.rcvd_qty,
        pi.returned_qty,
        pi.dispatched_qty,
        (
		COALESCE(pi.rcvd_qty, 0)
		  - COALESCE(pi.rejected_qty, 0)
		  - COALESCE(pi.returned_qty, 0)
		  - COALESCE(pi.dispatched_qty, 0)
		) AS returnable_qty,
        st.batch_no::text,
        st.expiry_date,
        st.qty_available,
        pi.unit_price

    FROM trans.scm_po_item pi
    JOIN trans.catalogue_ref cr
        ON cr.catalogue_id = pi.catalogue_id
    JOIN trans.scm_stock st
        ON st.catalogue_id::uuid = pi.catalogue_id
       AND st.warehouse_id = p_warehouse_id

    WHERE pi.po_id = p_po_id
      AND st.qty_available > 0

    ORDER BY cr.catalogue_name, st.expiry_date;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_items_for_purchase_return(uuid, character varying)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_ready_for_action(text, character varying)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_ready_for_action(text, character varying);

CREATE OR REPLACE FUNCTION trans.fn_get_po_ready_for_action(
	p_mode text,
	p_client_id character varying DEFAULT NULL::character varying)
    RETURNS TABLE(po_id uuid, po_no character varying, supplier_id character varying, client_id character varying, shipment_count integer, latest_shipment_date date, shipment_no text, lr_no text, transporter text) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    IF p_mode = 'INVOICE' THEN
        RETURN QUERY
        SELECT
            po.po_id,
            po.po_no,
            po.supplier_id::text,
            po.buyer_id::text,
            COUNT(DISTINCT sh.shipment_id)::integer AS shipment_count,
            MAX(sh.shipment_date)::date AS latest_shipment_date,
            MAX(sh.shipment_no) AS shipment_no,
            MAX(sh.lr_no) AS lr_no,
            MAX(sh.transporter) AS transporter
        FROM trans.scm_po po
        JOIN trans.scm_trade_shipment sh
            ON sh.order_id = po.po_id
           AND sh.status IN ('shipped', 'closed')
        WHERE po.status = 'APPROVED'
          AND (p_client_id IS NULL OR po.buyer_id::text = p_client_id)
        GROUP BY
            po.po_id,
            po.po_no,
            po.supplier_id,
            po.buyer_id
        ORDER BY latest_shipment_date DESC;

    ELSIF p_mode = 'DISPATCH' THEN
        RETURN QUERY
        SELECT
            po.po_id,
            po.po_no,
            po.supplier_id::text,
            po.buyer_id::text,
            COUNT(DISTINCT sh.shipment_id)::integer AS shipment_count,
            MAX(sh.shipment_date)::date AS latest_shipment_date,
            MAX(sh.shipment_no) AS shipment_no,
            MAX(sh.lr_no) AS lr_no,
            MAX(sh.transporter) AS transporter
        FROM trans.scm_po po
        LEFT JOIN trans.scm_trade_shipment sh
            ON sh.order_id = po.po_id
        WHERE po.status = 'APPROVED'
          AND (p_client_id IS NULL OR po.buyer_id::text = p_client_id)
        GROUP BY
            po.po_id,
            po.po_no,
            po.supplier_id,
            po.buyer_id
        ORDER BY latest_shipment_date DESC NULLS FIRST, po.po_no DESC;

    ELSE
        RAISE EXCEPTION 'Invalid p_mode: % (expected DISPATCH or INVOICE)', p_mode;
    END IF;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_ready_for_action(text, character varying)
    OWNER TO trans_owner;
-- FUNCTION: trans.fn_get_po_ready_for_purchase_return(character varying)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_ready_for_purchase_return(character varying);

CREATE OR REPLACE FUNCTION trans.fn_get_po_ready_for_purchase_return(
	p_client_id character varying DEFAULT NULL::character varying)
    RETURNS TABLE(po_id uuid, po_no character varying, supplier_id uuid, client_id uuid, returnable_qty numeric, last_grn_date date) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        po.po_id,
        po.po_no,
        po.supplier_id,
        po.buyer_id AS client_id,

        SUM(
            pi.rcvd_qty
          - pi.rejected_qty
          - pi.returned_qty
          - pi.dispatched_qty
        ) AS returnable_qty,

        MAX(grn.recv_dt) AS last_grn_date

    FROM trans.scm_po po
    JOIN trans.scm_po_item pi
        ON pi.po_id = po.po_id
    LEFT JOIN trans.scm_grn grn
        ON grn.po_id = po.po_id

    WHERE po.status = 'APPROVED'
      AND (p_client_id IS NULL OR po.buyer_id::text = p_client_id)

    GROUP BY
        po.po_id,
        po.po_no,
        po.supplier_id,
        po.buyer_id

    HAVING
        SUM(
            pi.rcvd_qty
          - pi.rejected_qty
          - pi.returned_qty
          - pi.dispatched_qty
        ) > 0

    ORDER BY last_grn_date DESC;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_ready_for_purchase_return(character varying)
    OWNER TO trans_owner;
-- FUNCTION: trans.get_stock_ledger(character varying, character varying, character varying, integer)

-- DROP FUNCTION IF EXISTS trans.get_stock_ledger(character varying, character varying, character varying, integer);

CREATE OR REPLACE FUNCTION trans.get_stock_ledger(
	p_merchant_id character varying,
	p_sku character varying DEFAULT NULL::character varying,
	p_batch_no character varying DEFAULT NULL::character varying,
	p_limit integer DEFAULT 100)
    RETURNS TABLE(ledger_id uuid, warehouse_id character varying, catalogue_id uuid, sku character varying, batch_no character varying, txn_type character varying, qty numeric, uom character varying, ref_type character varying, ref_id uuid, ref_no character varying, remarks text, created_by character varying, created_at timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
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;
$BODY$;

ALTER FUNCTION trans.get_stock_ledger(character varying, character varying, character varying, integer)
    OWNER TO trans_owner;
-- FUNCTION: trans.get_stock_summary(character varying, character varying, character varying)

-- DROP FUNCTION IF EXISTS trans.get_stock_summary(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION trans.get_stock_summary(
	p_merchant_id character varying,
	p_warehouse_id character varying DEFAULT NULL::character varying,
	p_sku character varying DEFAULT NULL::character varying)
    RETURNS TABLE(stock_id uuid, warehouse_id character varying, catalogue_id uuid, sku character varying, batch_no character varying, qty_on_hand numeric, qty_reserved numeric, qty_available numeric, uom character varying, updated_at timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
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.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;
$BODY$;

ALTER FUNCTION trans.get_stock_summary(character varying, character varying, character varying)
    OWNER TO trans_owner;
-- FUNCTION: trans.get_trade_sales_analytics(character varying, character varying, date, date)

-- DROP FUNCTION IF EXISTS trans.get_trade_sales_analytics(character varying, character varying, date, date);

CREATE OR REPLACE FUNCTION trans.get_trade_sales_analytics(
	p_supplier_id character varying DEFAULT NULL::character varying,
	p_client_id character varying DEFAULT NULL::character varying,
	p_date_from date DEFAULT NULL::date,
	p_date_to date DEFAULT NULL::date)
    RETURNS TABLE(metric_name character varying, metric_value numeric, metric_unit character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    -- Total shipments
    RETURN QUERY
    SELECT 
        'total_shipments'::VARCHAR(50) as metric_name,
        COUNT(*)::NUMERIC as metric_value,
        'count'::VARCHAR(20) as metric_unit
    FROM trans.scm_trade_shipment ts
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Total quantity shipped
    RETURN QUERY
    SELECT 
        'total_qty_shipped'::VARCHAR(50) as metric_name,
        COALESCE(SUM(tsi.shipped_qty), 0)::NUMERIC as metric_value,
        'units'::VARCHAR(20) as metric_unit
    FROM trans.scm_trade_shipment ts
    JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Unique SKUs shipped
    RETURN QUERY
    SELECT 
        'unique_skus'::VARCHAR(50) as metric_name,
        COUNT(DISTINCT tsi.sku)::NUMERIC as metric_value,
        'count'::VARCHAR(20) as metric_unit
    FROM trans.scm_trade_shipment ts
    JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
    WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
      AND (p_client_id IS NULL OR ts.client_id = p_client_id)
      AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
      AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to);
    
    -- Average shipment size
    RETURN QUERY
    SELECT 
        'avg_shipment_size'::VARCHAR(50) as metric_name,
        COALESCE(AVG(shipment_totals.total_qty), 0)::NUMERIC as metric_value,
        'units'::VARCHAR(20) as metric_unit
    FROM (
        SELECT ts.shipment_id, SUM(tsi.shipped_qty) as total_qty
        FROM trans.scm_trade_shipment ts
        JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
        WHERE (p_supplier_id IS NULL OR ts.supplier_id = p_supplier_id)
          AND (p_client_id IS NULL OR ts.client_id = p_client_id)
          AND (p_date_from IS NULL OR ts.shipment_date >= p_date_from)
          AND (p_date_to IS NULL OR ts.shipment_date <= p_date_to)
        GROUP BY ts.shipment_id
    ) shipment_totals;
    
END;
$BODY$;

ALTER FUNCTION trans.get_trade_sales_analytics(character varying, character varying, date, date)
    OWNER TO trans_owner;
-- FUNCTION: trans.release_stock_reservation(character varying, character varying, character varying, character varying, numeric, uuid, character varying)

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

CREATE OR REPLACE FUNCTION trans.release_stock_reservation(
	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$
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()
    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.release_stock_reservation(character varying, character varying, character varying, character varying, numeric, uuid, character varying)
    OWNER TO trans_owner;
-- 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,
        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;

-- FUNCTION: trans.update_adjustment_note_timestamp()
CREATE OR REPLACE FUNCTION trans.update_adjustment_note_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION trans.update_adjustment_note_timestamp()
    OWNER TO trans_owner;

-- FUNCTION: trans.update_trade_shipment_updated_at()
CREATE OR REPLACE FUNCTION trans.update_trade_shipment_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION trans.update_trade_shipment_updated_at()
    OWNER TO trans_owner;

-- FUNCTION: trans.get_next_scheme_sequence(integer)
CREATE OR REPLACE FUNCTION trans.get_next_scheme_sequence(scheme_year INTEGER)
RETURNS INTEGER AS $$
DECLARE
    next_seq INTEGER;
BEGIN
    SELECT COALESCE(MAX(
        CAST(SUBSTRING(scheme_code FROM 'SCH-[0-9]{4}-([0-9]{6})') AS INTEGER)
    ), 0) + 1
    INTO next_seq
    FROM trans.trade_schemes
    WHERE scheme_code LIKE 'SCH-' || scheme_year || '-%';
    
    RETURN next_seq;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION trans.get_next_scheme_sequence(integer)
    OWNER TO trans_owner;