CREATE OR REPLACE PROCEDURE pricequote.process_queue_proc()
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,
      cust = cr.cust
  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,
           CASE SUBSTRING(bc.cclass, 2, 3)
               WHEN 'DIR' THEN bc.dba
               ELSE COALESCE(sc.dba, bc.dba)
           END AS cust
      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
  --------------------------------------------------------------------------------
  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;

END;
$$;