price_api/builder/build_pricing_path_base.ms.sql

CREATE OR ALTER FUNCTION pricing.build_pricing_path_base
(
    @_json NVARCHAR(MAX)
)
RETURNS @Result TABLE
(
    stlc NVARCHAR(255),
    seq BIGINT,
    srtcode NVARCHAR(50),
    ds NVARCHAR(255),
    chan NVARCHAR(255),
    tier NVARCHAR(255),
    vol_lower INT,
    vol_upper INT,
    func NVARCHAR(50),
    val DECIMAL(18,5),
    price DECIMAL(18,5),
    math NVARCHAR(MAX),
    lastflag BIT
)
AS
BEGIN
    WITH
    -- 1️⃣ Parse JSON into rows of (entity, attr, val)
    parsed AS (
        SELECT
            entity = JSON_VALUE(j.value, '$.entity'),
            attr = ISNULL(JSON_VALUE(j.value, '$.attr'), ''),
            val = JSON_VALUE(j.value, '$.val'),
            func = JSON_VALUE(j.value, '$.func')
        FROM OPENJSON(@_json) j
    ),
    -- 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 ISNULL(CAST(p.val AS DECIMAL(18,5)), 0) ASC) srt
        FROM parsed p
        JOIN pricing.option_sequence s
            ON p.entity = s.entity
    ),
    -- 3️⃣ Recursively accumulate pricing path
    combos AS (
        -- 🚀 Base case: first in sequence
        SELECT
            s.entity,
            s.attr,
            s.seq,
            srtcode = FORMAT(s.srt, '000'),
            ds = CAST('' AS NVARCHAR(255)),
            chan = CAST('' AS NVARCHAR(255)),
            tier = CAST('' AS NVARCHAR(255)),
            vol = CAST(NULL AS NVARCHAR(50)),
            s.func,
            val = CAST(s.val AS DECIMAL(18,5)),
            agg = CAST(s.val AS DECIMAL(18,5)),
            base = CASE WHEN s.func = 'Price' THEN CAST(s.val AS DECIMAL(18,5)) ELSE NULL END,
            math = CAST(
                '[' + 
                CASE
                    WHEN s.func = 'Price' THEN
                        CONCAT('"', RIGHT(REPLICATE(' ', 17) + (s.entity + ':' + s.attr), 17), ' + ', FORMAT(CAST(s.val AS DECIMAL(18,5)), '0.00000'), '"')
                    WHEN s.func = 'Factor' THEN
                        CONCAT('"', RIGHT(REPLICATE(' ', 17) + (s.entity + ':' + s.attr), 17), ' + ', FORMAT(ISNULL(CASE WHEN s.func = 'Price' THEN CAST(s.val AS DECIMAL(18,5)) END, 0) * (CAST(s.val AS DECIMAL(18,5)) - 1), '0.00000'), '"')
                    ELSE
                        CONCAT('"', RIGHT(REPLICATE(' ', 17) + (s.entity + ':' + s.attr), 17), '   ', FORMAT(CAST(s.val AS DECIMAL(18,5)), '0.00000'), '"')
                END + ']'
                AS NVARCHAR(MAX))
        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,
            srtcode = c.srtcode + '.' + FORMAT(o.srt, '000'),
            ds = CAST(c.ds + CASE WHEN o.DOMAIN = 'Product' THEN '.' + ISNULL(o.attr, '') ELSE '' END AS NVARCHAR(255)),
            chan = CAST(CASE WHEN o.DOMAIN = 'Channel' THEN ISNULL(o.attr, '') ELSE c.chan END AS NVARCHAR(255)),
            tier = CAST(CASE WHEN o.DOMAIN = 'Tier' THEN ISNULL(o.attr, '') ELSE c.tier END AS NVARCHAR(255)),
            vol = CAST(CASE WHEN o.DOMAIN = 'Volume' THEN o.attr ELSE c.vol END AS NVARCHAR(50)),
            o.func,
            val = CAST(o.val AS DECIMAL(18,5)),
            agg = CAST(CASE 
                WHEN o.func = 'Price' THEN c.agg + CAST(o.val AS DECIMAL(18,5))
                WHEN o.func = 'Factor' THEN c.agg + ISNULL(c.base, 0) * (CAST(o.val AS DECIMAL(18,5)) - 1)
            END AS DECIMAL(18,5)),
            base = ISNULL(c.base, CASE WHEN o.func = 'Price' THEN CAST(o.val AS DECIMAL(18,5)) ELSE NULL END),
            math = CAST(
                CASE 
                    WHEN (o.func = 'Price' AND CAST(o.val AS DECIMAL(18,5)) <> 0) OR (o.func = 'Factor' AND CAST(o.val AS DECIMAL(18,5)) <> 1) THEN
                        LEFT(c.math, LEN(c.math) - 1) + ',' +
                        CASE
                            WHEN o.func = 'Price' THEN
                                CONCAT('"', RIGHT(REPLICATE(' ', 17) + (o.entity + ':' + o.attr), 17), ' + ', FORMAT(CAST(o.val AS DECIMAL(18,5)), '0.00000'), '"')
                            WHEN o.func = 'Factor' THEN
                                CONCAT('"', RIGHT(REPLICATE(' ', 17) + (o.entity + ':' + o.attr), 17), ' + ', FORMAT(ISNULL(c.base, 0) * (CAST(o.val AS DECIMAL(18,5)) - 1), '0.00000'), '"')
                            ELSE
                                CONCAT('"', RIGHT(REPLICATE(' ', 17) + (o.entity + ':' + o.attr), 17), '   ', FORMAT(CAST(o.val AS DECIMAL(18,5)), '0.00000'), '"')
                        END + ']'
                    ELSE 
                        c.math 
                END AS NVARCHAR(MAX))
        FROM combos c
        JOIN sequenced o 
            ON o.seq = c.seq + 1
    )
    INSERT INTO @Result
    SELECT
        stlc = c.attr,
        c.seq,
        c.srtcode,
        ds = 'v1:' + SUBSTRING(c.ds, 2, 100),
        c.chan,
        c.tier,
        vol_lower = CASE 
            WHEN c.vol LIKE '[0-9]%-[0-9]%' THEN CAST(LEFT(c.vol, CHARINDEX('-', c.vol) - 1) AS INT)
            WHEN c.vol LIKE '[0-9]%' THEN CAST(c.vol AS INT)
            ELSE NULL
        END,
        vol_upper = CASE 
            WHEN c.vol LIKE '[0-9]%-[0-9]%' THEN CAST(SUBSTRING(c.vol, CHARINDEX('-', c.vol) + 1, LEN(c.vol)) AS INT)
            ELSE NULL
        END,
        c.func,
        c.val,
        c.agg,
        math = c.math,
        lastflag = CASE WHEN c.seq = (SELECT MAX(x.seq) FROM sequenced x) THEN 1 ELSE 0 END
    FROM combos c
    ORDER BY c.srtcode ASC;

    RETURN;
END;