price_api/new_targets/procs/get_options_merged.pg.sql

DROP FUNCTION rlarp.get_options_merged;

CREATE OR REPLACE FUNCTION rlarp.get_options_merged(
    _priceg TEXT,
    _majg TEXT,
    existing_json JSONB
)
RETURNS TABLE (
    entity TEXT,
    attr TEXT,
    val NUMERIC,
    func TEXT
)
LANGUAGE plpgsql AS
$$
BEGIN

RETURN QUERY
WITH

-- 1️⃣ Parse existing pricing from JSONB
existing AS (
    SELECT 
        e.entity, 
        COALESCE(e.attr, '') AS attr, 
        e.val, 
        e.func
    FROM jsonb_to_recordset(existing_json)
         AS e(entity TEXT, attr TEXT, val NUMERIC, func TEXT)
)
,items AS (
    SELECT 
        item 
    FROM 
        "CMS.CUSLG".itemm 
    WHERE 
        stlc IN (SELECT DISTINCT stlc FROM "CMS.CUSLG".itemm WHERE pricegroup ~ _priceg AND majg ~ _majg) 
        AND aplnt <> 'I'
)
-- 2️⃣ Build raw stack without hard-coded func
,stack AS (
    -- Anchor
    SELECT 'Anchor' AS entity, stlc AS attr, 0::numeric AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY stlc

    UNION ALL

    -- Color Tier
    SELECT 'Color Tier' AS entity, coltier AS attr, 1 AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY coltier

    UNION ALL

    -- Branding
    SELECT 'Branding' AS entity, COALESCE(substring(branding,1,1), '') AS attr, 0 AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY COALESCE(substring(branding,1,1), '')

    UNION ALL

    -- Packaging
    SELECT 'Packaging' AS entity, COALESCE(uomp, '') AS attr, 0 AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY COALESCE(uomp, '')

    UNION ALL

    -- Accessories
    SELECT 'Accessories' AS entity, COALESCE(accs_ps, '') AS attr, 0 AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY COALESCE(accs_ps, '')

    UNION ALL

    -- Suffix
    SELECT 'Suffix' AS entity, COALESCE(suffix, '') AS attr, 1 AS val
    FROM "CMS.CUSLG".itemm
    WHERE item IN (
        SELECT item
        FROM "CMS.CUSLG".itemm
        WHERE item IN (SELECT item FROM items)
    )
    GROUP BY COALESCE(suffix, '')

    UNION ALL

    -- Channel
    SELECT 'Channel' AS entity, x.chan AS attr, 0 AS val
    FROM (VALUES ('DIR'), ('DRP'), ('WHS')) AS x(chan)

    UNION ALL

    -- Customer Tier
    SELECT 'Tier' AS entity, x.tier AS attr, 0 AS val
    FROM (VALUES ('1'), ('2'), ('3')) AS x(tier)

    UNION ALL

    -- Volume
    SELECT 'Volume' AS entity, x.chan AS attr, 0 AS val
    FROM (VALUES ('0-1'),('1-8'),('8-24'),('24')) AS x(chan)
),

-- 3️⃣ Attach pricing func and sequence from option_sequence
stack_with_meta AS (
    SELECT
        s.entity,
        COALESCE(s.attr, '') AS attr,
        s.val,
        os.func,
        os.seq
    FROM stack s
    LEFT JOIN pricequote.option_sequence os
      ON s.entity = os.entity
),

-- 4️⃣ Merge: master rows with overrides
overridden AS (
    SELECT
        s.entity,
        s.attr,
        COALESCE(e.val, s.val) AS val,
        s.func,
        s.seq
    FROM stack_with_meta s
    LEFT JOIN existing e
      ON s.entity = e.entity
     AND COALESCE(s.attr, '') = COALESCE(e.attr, '')
     AND s.func = e.func
),

-- 5️⃣ Extras in saved JSON but not in master
extras AS (
    SELECT
        e.entity,
        COALESCE(e.attr, '') AS attr,
        e.val,
        e.func,
        os.seq
    FROM existing e
    LEFT JOIN stack_with_meta s
      ON e.entity = s.entity
     AND COALESCE(e.attr, '') = COALESCE(s.attr, '')
     AND e.func = s.func
    LEFT JOIN pricequote.option_sequence os
      ON e.entity = os.entity
    WHERE s.entity IS NULL
),

-- 6️⃣ Combine both sources
combined AS (
    SELECT o.entity, o.attr, o.val, o.func, o.seq FROM overridden o
    UNION ALL
    SELECT e.entity, e.attr, e.val, e.func, e.seq FROM extras e
)

-- 7️⃣ Return ordered by sequence
SELECT
    combined.entity,
    combined.attr,
    combined.val,
    combined.func
FROM combined
ORDER BY combined.seq NULLS LAST, combined.entity, combined.attr;

END;
$$;