price_api/new_targets/procs/single_price_call.pg.sql

DROP FUNCTION IF EXISTS pricequote.single_price_call;

CREATE OR REPLACE FUNCTION pricequote.single_price_call(
    _bill TEXT,
    _ship TEXT,
    _part TEXT,
    _stlc TEXT,
    _v1ds TEXT,
    _vol NUMERIC
)
RETURNS TABLE (
    bill TEXT,
    ship TEXT,
    part TEXT,
    stlc TEXT,
    v1ds TEXT,
    vol NUMERIC,
    chan TEXT,
    cust TEXT,
    tier TEXT,
    pltq NUMERIC,
    plevel TEXT,
--    hist JSONB,
    last_price NUMERIC,
    last_date DATE,
    last_order TEXT,
    last_quote TEXT,
    tprice NUMERIC,
    guidance_price NUMERIC,
    guidance_reason TEXT,
    expl JSONB
) AS $$
DECLARE
    _pltq NUMERIC;
    _chan TEXT;
    _tier TEXT;
    _cust TEXT;
    _plevel TEXT;
    _tprice NUMERIC;
    _last_price NUMERIC;
    _last_date DATE;
    _last_order TEXT;
    _last_quote TEXT;
    _list_price NUMERIC;
    _list_code TEXT;
    _hist JSONB := '{}'::jsonb;
    _expl JSONB := '{}'::jsonb;
	_this JSONB := '{}'::jsonb;
    _guidance_price NUMERIC;
    _guidance_reason TEXT;
	_partgroup TEXT;
BEGIN
    ------------------------------------------------------------------
    -- Step 1: Resolve customer metadata
    ------------------------------------------------------------------
	SELECT
	    i.mpck,
	    CASE SUBSTRING(bc.cclass, 2, 3)
	        WHEN 'DIS' THEN 
	            CASE SUBSTRING(sc.cclass, 2, 3)
	                WHEN 'DIS' THEN 'WHS'
	                ELSE 'DRP'
	            END
	        ELSE 'DIR'
	    END,
	    CASE SUBSTRING(bc.cclass, 2, 3)
	        WHEN 'DIR' THEN bc.tier
	        ELSE COALESCE(sc.tier, bc.tier)
	    END,
	    CASE SUBSTRING(bc.cclass, 2, 3)
	        WHEN 'DIS' THEN 
	            CASE SUBSTRING(sc.cclass, 2, 3)
	                WHEN 'DIS' THEN bc.dba
	                ELSE sc.dba
	            END
	        ELSE bc.dba
	    END,
	    CASE SUBSTRING(bc.cclass, 2, 3)
	        WHEN 'DIS' THEN 
	            CASE SUBSTRING(sc.cclass, 2, 3)
	                WHEN 'DIS' THEN sc.plevel
	                ELSE bc.plevel
	            END
	        ELSE bc.plevel
	    END,
		i.partgroup
	INTO _pltq, _chan, _tier, _cust, _plevel, _partgroup
	FROM rlarp.cust bc
	LEFT JOIN rlarp.cust sc ON sc.code = _ship
	LEFT JOIN "CMS.CUSLG".itemm i ON i.item = _part
	WHERE bc.code = _bill;

	-- RAISE NOTICE 'Step 1: %', _expl;

    ------------------------------------------------------------------
    -- Step 2: Target price logic
    ------------------------------------------------------------------
    SELECT tp.price,
           jsonb_build_object(
               'source', 'target price',
               'target_price', tp.price,
               'calculated_pallets', FLOOR(_vol / NULLIF(_pltq, 0)),
               'exact_pallets', ROUND(_vol / NULLIF(_pltq, 0), 5),
               'volume range', tp.vol::TEXT,
               'customer', _cust,
               'channel', _chan,
               'tier', TRIM(_tier),
               'target math', tp.math
           )
    INTO _tprice, _this
    FROM pricequote.target_prices tp
    WHERE tp.stlc = _stlc
      AND tp.ds = _v1ds
      AND tp.chan = _chan
      AND tp.tier = _tier
      AND FLOOR(_vol / NULLIF(_pltq, 0))::int <@ tp.vol;

	IF _this IS NOT NULL THEN
	    _expl := _expl || _this;
	END IF;

	-- RAISE NOTICE 'Step 2: %', _expl;

    ------------------------------------------------------------------
    -- Step 3: Last sale data
    ------------------------------------------------------------------
    SELECT 
        (lp.dataseg_stats -> _v1ds) ->> 'price',
        (lp.dataseg_stats -> _v1ds) ->> 'odate',
        (lp.dataseg_stats -> _v1ds) ->> 'ordnum',
        (lp.dataseg_stats -> _v1ds) ->> 'quoten',
        lp.dataseg_stats
    INTO 
        _last_price, _last_date, _last_order, _last_quote, _hist
    FROM pricequote.lastprice lp
    WHERE lp.customer = _cust
      AND lp.partgroup = _partgroup;

	_expl := _expl || jsonb_build_object(
	    'last_price', _last_price,
	    'last_date', _last_date,
	    'last_order', _last_order,
	    'last_quote', _last_quote
--	     'full_history_________', _hist
	);

	-- RAISE NOTICE 'Step 3: %', _expl;

    ------------------------------------------------------------------
    -- Step 4: List price
    ------------------------------------------------------------------
    SELECT 
		pr.price::numeric(20,5), pr.jcplcd
    INTO 
		_list_price, _list_code
    FROM 
		"CMS.CUSLG".IPRCBHC i
	    JOIN pricequote.pricelist_ranged pr
	        ON pr.jcplcd = TRIM(i.jbplcd)
	       AND pr.jcpart = _part
	       AND _vol >= pr.vb_from
	       AND _vol < pr.vb_to
    WHERE TRIM(i.jbplvl) = TRIM(_plevel)
      AND CURRENT_DATE BETWEEN i.jbfdat AND i.jbtdat
    ORDER BY pr.price ASC
    LIMIT 1;

	_expl := _expl || jsonb_build_object(
	    'list_price', _list_price,
	    'list_code', _list_code
	);

	-- RAISE NOTICE 'Step 4: %', _expl;

    ------------------------------------------------------------------
	-- Step 5: Compute guidance price and embed it
	------------------------------------------------------------------
	

    SELECT 
		gl.guidance_price, gl.guidance_reason
    INTO 
		_guidance_price, _guidance_reason
    FROM 
		pricequote.guidance_logic(_tprice, _last_price, _list_price, _last_date) gl;
	
    _expl := _expl || jsonb_build_object(
        'guidance_price', _guidance_price,
        'guidance_reason', _guidance_reason
    );


	-- RAISE NOTICE 'Step 5: %', _expl;

    ------------------------------------------------------------------
    -- Final: Return row
    ------------------------------------------------------------------
    RETURN QUERY
    SELECT 
        _bill, _ship, _part, _stlc, _v1ds, _vol,
        _chan, _cust, _tier, _pltq, _plevel,
        -- _hist,
        _last_price, _last_date, _last_order, _last_quote,
        _tprice,
        _guidance_price,
        _guidance_reason,
        _expl;
END;
$$ LANGUAGE plpgsql;