CREATE OR REPLACE FUNCTION pricequote.process_queue()
RETURNS SETOF pricequote.price_queue
LANGUAGE plpgsql
AS $$
BEGIN

  --------------------------------------------------------------------------------
  -- Step 1: Set channel, tier, and pallet quantity
  --------------------------------------------------------------------------------
  UPDATE pricequote.price_queue s
  SET
      chan = cr.chan,
      tier = cr.tier,
      pltq = cr.mpck
  FROM (
      SELECT 
           q.bill,
           q.ship,
           q.part,
           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 AS chan,
           CASE SUBSTRING(bc.cclass, 2, 3)
               WHEN 'DIR' THEN bc.tier
               ELSE COALESCE(sc.tier, bc.tier)
           END AS tier
      FROM pricequote.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 "CMS.CUSLG".itemm i ON i.item = q.part
  ) cr
  WHERE
      cr.bill = s.bill AND
      COALESCE(cr.ship, '') = COALESCE(s.ship, '') AND
      cr.part = s.part;

  --------------------------------------------------------------------------------
  -- Step 2: Set price and explanation from target_prices, return touched rows
  --------------------------------------------------------------------------------
  RETURN QUERY
  WITH updated AS (
    UPDATE pricequote.price_queue q
    SET
        price = tp.price,
        expl = jsonb_build_object(
            'source', 'target price',
            'calculated_pallets', FLOOR(q.vol / NULLIF(q.pltq, 0))::INT,
            'exact_pallets', ROUND(q.vol / NULLIF(q.pltq, 0), 5),
            'volume range', tp.vol
        )
    FROM pricequote.target_prices tp
    WHERE
        q.stlc = tp.stlc
        AND q.v1ds = tp.ds
        AND q.chan = tp.chan
        AND q.tier = tp.tier
        AND tp.vol @> FLOOR(q.vol / NULLIF(q.pltq, 0))::INT
    RETURNING q.*
  )
  SELECT * FROM updated;

END;
$$;