Definitions

WITH market_orders AS (
  SELECT b.market_id, o.channel_id, o.placed_at
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  JOIN channels c ON c.id = o.channel_id
  WHERE c.kind <> 'dine_in'
    AND o.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (7) DAY - INTERVAL (20) MINUTE
    AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
),
current_window AS (
  SELECT market_id, channel_id, count(*) AS orders
  FROM market_orders
  WHERE placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (20) MINUTE
  GROUP BY market_id, channel_id
),
history AS (
  SELECT m.market_id, m.channel_id, s.d, count(*) AS orders
  FROM market_orders m
  JOIN range(1, 7 + 1) s(d)
    ON m.placed_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (s.d) DAY - INTERVAL (20) MINUTE
   AND m.placed_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (s.d) DAY
  GROUP BY m.market_id, m.channel_id, s.d
),
market_days AS (
  SELECT DISTINCT market_id, d FROM history
),
expected AS (
  SELECT md.market_id, ch.channel_id, avg(coalesce(h.orders, 0)) AS expected, count(*) AS days_with_data,
         list(coalesce(h.orders, 0) ORDER BY md.d) AS same_slot_by_day
  FROM market_days md
  JOIN (SELECT DISTINCT market_id, channel_id FROM history) ch ON ch.market_id = md.market_id
  LEFT JOIN history h ON h.market_id = md.market_id AND h.channel_id = ch.channel_id AND h.d = md.d
  GROUP BY md.market_id, ch.channel_id
),
compared AS (
  SELECT e.*, coalesce(w.orders, 0) AS current_orders,
         sum(e.expected) OVER (PARTITION BY e.market_id) - e.expected AS others_expected,
         sum(coalesce(w.orders, 0)) OVER (PARTITION BY e.market_id) - coalesce(w.orders, 0) AS others_current
  FROM expected e
  LEFT JOIN current_window w ON w.market_id = e.market_id AND w.channel_id = e.channel_id
),
last_order AS (
  SELECT market_id, channel_id, max(placed_at) AS last_order_at
  FROM market_orders
  GROUP BY market_id, channel_id
)
SELECT 'market_channel' AS subject_type, mk.code || ':' || CAST(c.channel_id AS VARCHAR) AS subject_id,
       mk.code AS market, c.market_id, c.channel_id,
       CASE WHEN c.current_orders = 0
                 AND lo.last_order_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL (2 * 20) MINUTE THEN lo.last_order_at
            ELSE TIMESTAMP 'Fri 23:00:00' - INTERVAL (20) MINUTE END AS started_at,
       'critical' AS severity,
       json_object(
         'market', mk.code,
         'channel', ch.name,
         'window_minutes', 20,
         'orders_in_window', c.current_orders,
         'expected_orders', round(c.expected, 1),
         'ratio', round(c.current_orders / c.expected, 3),
         'drop_ratio', 0.15,
         'same_slot_previous_days', c.same_slot_by_day,
         'days_with_data', c.days_with_data,
         'other_channels_orders', c.others_current,
         'other_channels_expected', round(c.others_expected, 1),
         'other_channels_ratio', round(c.others_current / c.others_expected, 3),
         'last_order_at', lo.last_order_at,
         'stores_listed', (SELECT count(*) FROM branch_channels bc JOIN branches b2 ON b2.id = bc.branch_id
                           WHERE bc.channel_id = c.channel_id AND b2.market_id = c.market_id)
       ) AS evidence
FROM compared c
JOIN markets mk ON mk.id = c.market_id
JOIN channels ch ON ch.id = c.channel_id
LEFT JOIN last_order lo ON lo.market_id = c.market_id AND lo.channel_id = c.channel_id
WHERE c.expected >= 20
  AND c.current_orders < c.expected * 0.15
  AND c.others_expected > 0
  AND c.others_current >= c.others_expected * 0.6
ORDER BY started_at, mk.code, c.channel_id