CREATE OR ALTER PROCEDURE pricing.process_queue
AS
BEGIN
    SET NOCOUNT ON;
    --------------------------------------------------------------------------------
    -- Step 1: Insert input row into real queue table
    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    -- Step 2: Enrich the row with chan, tier, cust, pltq
    --------------------------------------------------------------------------------
    UPDATE q
    SET
        q.chan = 
            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,
        q.tier =
            CASE SUBSTRING(bc.cclass, 2, 3)
                WHEN 'DIR' THEN bc.tier
                ELSE ISNULL(sc.tier, bc.tier)
            END,
        q.cust = 
            CASE SUBSTRING(bc.cclass, 2, 3)
                WHEN 'DIS' THEN 
                    CASE SUBSTRING(sc.cclass, 2, 3)
                        WHEN 'DIS' THEN q.ship
                        ELSE q.ship
                    END
                ELSE q.bill
            END,
        q.pltq = i.mpck
    FROM 
    	pricing.price_queue q
	    LEFT JOIN rlarp.cust bc ON bc.code = q.bill
	    LEFT JOIN rlarp.cust sc ON sc.code = q.ship
	    LEFT JOIN CMSInterfaceIn.[CMS.CUSLG].itemm i ON i.item = q.part;

    --------------------------------------------------------------------------------
    -- Step 3: Apply pricing from target_prices
    --------------------------------------------------------------------------------

    DECLARE @updated TABLE (
        id      BIGINT,
        bill    VARCHAR(100),
        ship    VARCHAR(100),
        part    VARCHAR(100),
        stlc    VARCHAR(100),
        v1ds    VARCHAR(100),
        vol     NUMERIC(18,6),
        chan    VARCHAR(50),
        cust    VARCHAR(100),
        tier    VARCHAR(50),
        pltq    NUMERIC(18,6),
        price   NUMERIC(18,6),
        expl    NVARCHAR(MAX)
    );

    UPDATE q
    SET 
        q.price = tp.price,
	    q.expl = (
	        SELECT 
	            'target price'                                                              AS [source],
	            FLOOR(q.vol / NULLIF(q.pltq, 0))                                            AS [calculated_pallets],
	            ROUND(q.vol / NULLIF(q.pltq, 0), 5)                                         AS [exact_pallets],
                CONCAT(tp.lower_bound, '-', ISNULL(CAST(tp.upper_bound AS VARCHAR), '∞'))   AS [volume range],
	            q.cust                                                                      AS [customer],
	            q.chan                                                                      AS [channel],
	            q.tier                                                                      AS [tier],
	            JSON_QUERY(tp.math)                                                         AS [target math]  -- important if math is JSON
	        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	    )
    OUTPUT 
        inserted.id,
        inserted.bill,
        inserted.ship,
        inserted.part,
        inserted.stlc,
        inserted.v1ds,
        inserted.vol,
        inserted.chan,
        inserted.cust,
        inserted.tier,
        inserted.pltq,
        inserted.price,
        inserted.expl
    INTO @updated
    FROM pricing.price_queue q
    INNER JOIN pricing.target_prices tp ON
        q.stlc = tp.stlc
        AND q.v1ds = tp.ds
        AND q.chan = tp.chan
        AND q.tier = tp.tier
        AND FLOOR(q.vol / NULLIF(q.pltq, 0)) >= tp.lower_bound
        AND (
            tp.upper_bound IS NULL OR FLOOR(q.vol / NULLIF(q.pltq, 0)) < tp.upper_bound
        );

    --------------------------------------------------------------------------------
    -- Step 4: Return just the enriched row
    --------------------------------------------------------------------------------
    SELECT * FROM @updated;
END;