Definitions

WITH rejected AS (
  SELECT e.order_id, min(e.at) AS rejected_at,
         any_value(TRY_CAST(e.payload->>'item_id' AS INTEGER)) AS item_id,
         any_value(e.payload->>'pos_error') AS pos_error
  FROM order_events e
  WHERE e.type ILIKE '%reject%' AND (e.payload->>'item_id') IS NOT NULL
  GROUP BY e.order_id
),
rejections AS (
  SELECT o.branch_id, r.item_id, o.id AS order_id, o.channel_id, r.rejected_at, r.pos_error,
         o.rejection_reason = 'payload mapping error' AS mapping
  FROM orders o
  JOIN rejected r ON r.order_id = o.id
  WHERE o.status = 'rejected'
    AND (o.rejection_reason IN ('item unavailability', 'payload mapping error')
         OR o.rejection_reason ILIKE '%mismatch%')
    AND r.rejected_at >= TIMESTAMP 'Thu 23:00:00' AND r.rejected_at < TIMESTAMP 'Fri 23:00:00' AND r.rejected_at <= TIMESTAMP 'Fri 23:00:00'
),
per_item AS (
  SELECT branch_id, item_id, count(*) AS rejected_orders,
         min(rejected_at) AS first_rejected_at, max(rejected_at) AS last_rejected_at,
         mode(channel_id) AS channel_id, list(DISTINCT channel_id) AS channel_ids,
         count(*) FILTER (WHERE mapping) AS mapping_rejections,
         count(*) FILTER (WHERE NOT mapping) AS availability_rejections,
         any_value(pos_error) AS pos_error,
         list(order_id ORDER BY rejected_at) AS rejected_order_ids
  FROM rejections
  GROUP BY branch_id, item_id
),
syncs AS (
  SELECT e.integration_id, max(e.at) AS last_synced_at
  FROM integration_events e
  WHERE e.type = 'menu_synced' AND e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY e.integration_id
),
unsynced AS (
  SELECT b.id AS branch_id, TRY_CAST(e.payload->>'item_id' AS INTEGER) AS item_id,
         min(e.at) AS changed_at, arg_max(e.type, e.at) AS change
  FROM integration_events e
  JOIN branches b ON b.pos_integration_id = e.integration_id
  LEFT JOIN syncs s ON s.integration_id = e.integration_id
  WHERE (e.type ILIKE '%item_unavailable%' OR e.type ILIKE '%item_available%' OR e.type ILIKE '%price_changed%')
    AND e.at <= TIMESTAMP 'Fri 23:00:00' AND e.at > coalesce(s.last_synced_at, TIMESTAMP '-infinity')
  GROUP BY ALL
),
sync_failures AS (
  SELECT b.id AS branch_id, count(*) AS failures, arg_max(e.payload, e.at) AS last_error
  FROM integration_events e
  JOIN branches b ON b.pos_integration_id = e.integration_id
  LEFT JOIN syncs s ON s.integration_id = e.integration_id
  WHERE e.type ILIKE '%sync_fail%' AND e.at <= TIMESTAMP 'Fri 23:00:00'
    AND e.at > coalesce(s.last_synced_at, TIMESTAMP '-infinity')
  GROUP BY b.id
)
SELECT 'branch_item' AS subject_type, CAST(b.id AS VARCHAR) || ':' || CAST(p.item_id AS VARCHAR) AS subject_id,
       b.id AS branch_id, b.merchant_id, p.item_id, p.channel_id,
       p.first_rejected_at AS started_at,
       CASE WHEN p.rejected_orders >= 2 * 3 THEN 'high' ELSE 'medium' END AS severity,
       CASE WHEN p.availability_rejections >= p.mapping_rejections THEN 'switch item off on channel'
            ELSE 'menu push needed' END AS fix,
       json_object(
         'branch', b.name,
         'item', mi.name,
         'rejected_orders', p.rejected_orders,
         'availability_rejections', p.availability_rejections,
         'mapping_rejections', p.mapping_rejections,
         'pos_error', p.pos_error,
         'first_rejected_at', p.first_rejected_at,
         'last_rejected_at', p.last_rejected_at,
         'channel_ids', p.channel_ids,
         'rejected_item_ids', [p.item_id],
         'rejected_order_ids', p.rejected_order_ids[1:20],
         'unsynced_pos_change', u.change,
         'pos_change_at', u.changed_at,
         'pos_integration_id', b.pos_integration_id,
         'last_menu_sync_at', s.last_synced_at,
         'menu_sync_failures', coalesce(f.failures, 0),
         'last_sync_error', f.last_error
       ) AS evidence
FROM per_item p
JOIN branches b ON b.id = p.branch_id
LEFT JOIN menu_items mi ON mi.id = p.item_id
LEFT JOIN syncs s ON s.integration_id = b.pos_integration_id
LEFT JOIN unsynced u ON u.branch_id = p.branch_id AND u.item_id = p.item_id
LEFT JOIN sync_failures f ON f.branch_id = p.branch_id
WHERE (p.rejected_orders >= 3 OR u.item_id IS NOT NULL)
  AND p.last_rejected_at > coalesce(s.last_synced_at, TIMESTAMP '-infinity')
ORDER BY started_at, b.id, p.item_id