app/sql/get_trade_sales_analytics.sql

-- 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;