DROP FUNCTION pricequote.build_pricing_path;

CREATE OR REPLACE FUNCTION pricequote.build_pricing_path(
  _json JSONB
)
RETURNS TABLE (
     stlc       TEXT
    ,seq        BIGINT
    ,srtcode    TEXT
    ,ds         TEXT
    ,chan       TEXT
    ,tier       TEXT
    ,vol        INT4RANGE
    ,func       TEXT
    ,val        NUMERIC
    ,price      NUMERIC
    ,math       TEXT[]
    ,lastflag   BOOLEAN
)
LANGUAGE plpgsql AS
$$
BEGIN
RETURN QUERY

WITH RECURSIVE
-- 1️⃣ Parse JSONB into rows of (entity, attr, val)
parsed AS (
  SELECT
    e.entity,
    COALESCE(e.attr, '') AS attr,
    e.val,
    e.func
  FROM jsonb_to_recordset(_json)
       AS e(entity TEXT, attr TEXT, val NUMERIC, func TEXT)
),
-- 2️⃣ Attach sequence & func from master option_sequence table
sequenced AS (
  SELECT
    p.entity,
    p.attr,
    p.val,
    p.func,
    s.DOMAIN,
    DENSE_RANK() OVER (ORDER BY s.seq) AS seq,
    ROW_NUMBER() OVER (PARTITION BY p.entity ORDER BY COALESCE(p.val,0) ASC) srt
  FROM parsed p
  JOIN pricequote.option_sequence s
    ON p.entity = s.entity
)
--select * from sequenced ORDER BY seq, srt
-- 3️⃣ Recursively accumulate pricing path
,combos AS (
  -- 🚀 Base case: first in sequence
  SELECT
    s.entity,
    s.attr,
    s.seq,
    to_char(s.srt,'FM000') srtcode,
    '' ds,
    '' chan,
    '' tier,
    null::TEXT vol,
    s.func,
    s.val,
    s.val agg,
    -- jsonb_build_array(jsonb_build_object('entity',s.entity,'attr',s.attr,'function',s.func, 'val',s.val,'seq',s.seq)) math
   ARRAY[
     CASE
       WHEN s.func = 'Price' THEN
         RPAD(s.entity || ':' || s.attr, 17, ' ') || ' + ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
       WHEN s.func = 'Factor' THEN
         RPAD(s.entity || ':' || s.attr, 17, ' ') || ' x ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
       ELSE
         RPAD(s.entity || ':' || s.attr, 17, ' ') || '   ' || LPAD(to_char(s.val, 'FM9999999990.00000'), 10, ' ')
     END
   ] math
  FROM 	
  	sequenced s
  WHERE 
  	s.seq = (SELECT MIN(x.seq) FROM sequenced x)
  UNION ALL
  -- 🔁 Recursive step: process next in sequence
  SELECT
    c.entity,
    c.attr,
    o.seq,
    c.srtcode || '.' || to_char(o.srt,'FM000'),
    c.ds || CASE WHEN o.DOMAIN = 'Product' THEN '.' || o.attr ELSE '' END ds,
    CASE WHEN o.DOMAIN = 'Channel' THEN o.attr ELSE c.chan END chan,
    CASE WHEN o.DOMAIN = 'Tier' THEN o.attr ELSE c.tier END tier,
    CASE WHEN o.DOMAIN = 'Volume' THEN o.attr ELSE c.vol END vol,
    o.func,
    o.val,
    CASE o.func WHEN 'Price' THEN c.agg + o.val WHEN 'Factor' THEN c.agg * o.val END agg,
    -- c.math || jsonb_build_array(jsonb_build_object('entity',o.entity,'attr',o.attr,'function',o.func, 'val',o.val,'seq',o.seq)) math,
    CASE WHEN (o.func = 'Price' AND o.val <> 0) OR (o.func = 'Factor' AND o.val <> 1) THEN
        c.math ||
        ARRAY[
         CASE
           WHEN o.func = 'Price' THEN
             RPAD(o.entity || ':' || o.attr, 17, ' ') || ' + ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
           WHEN o.func = 'Factor' THEN
             RPAD(o.entity || ':' || o.attr, 17, ' ') || ' x ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
           ELSE
             RPAD(o.entity || ':' || o.attr, 17, ' ') || '   ' || LPAD(to_char(o.val, 'FM9999999990.00000'), 10, ' ')
         END
       ]
    ELSE 
        c.math 
    END math
  FROM 
  	combos c
  	JOIN sequenced o ON 
  		o.seq = c.seq + 1
)
SELECT 
     -- c.entity
     c.attr
    ,c.seq
    ,c.srtcode
    ,'v1:'||SUBSTRING(c.ds,2,100) ds
    ,c.chan
    ,c.tier
    -- ,c.vol
    ,CASE
        WHEN c.vol ~ '^[0-9]+-[0-9]+$' THEN
          int4range(
            split_part(c.vol, '-', 1)::int,
            split_part(c.vol, '-', 2)::int,
            '[)'
          )
        WHEN c.vol ~ '^[0-9]+$' THEN
          int4range(
            c.vol::int,
            NULL,
            '[)'
          )
        ELSE NULL
    END AS vol
    ,c.func
    ,c.val
    ,c.agg
    ,c.math
    ,c.seq = (SELECT max(x.seq) FROM sequenced x) lastflag
FROM 
    combos c /*WHERE seq = (SELECT max(seq) FROM sequenced)*/ 
ORDER BY 
    c.srtcode ASC;

END;
$$;

/*
Anchor:EU170S50 + 0.08
Color Tier:P    x 1.30
Branding:       + 0.00
Packaging:SLV   + 0.00
Suffix:PCR      x 1.00
Accessories:    + 0.00
Channel:WHS     + 0.00
Volume:8        x 1.00
-----------------------
                  0.104
*/