Definitions

WITH spine AS (
  SELECT b.id AS branch_id, CAST(CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL (r.k) DAY AS DATE) AS day
  FROM branches b
  CROSS JOIN range(1, 22) r(k)
),
daily AS (
  SELECT s.branch_id, s.day, coalesce(sum(d.orders - coalesce(d.rejected, 0)), 0) AS orders
  FROM spine s
  LEFT JOIN orders_daily d ON d.branch_id = s.branch_id AND d.day = s.day
  GROUP BY s.branch_id, s.day
),
paired AS (
  SELECT c.branch_id, c.day, c.orders, p.orders AS week_before,
         c.orders < 0.75 * p.orders AS low,
         row_number() OVER (PARTITION BY c.branch_id ORDER BY c.day DESC) AS age
  FROM daily c
  JOIN daily p ON p.branch_id = c.branch_id AND p.day = CAST(c.day - INTERVAL 7 DAY AS DATE)
),
healthy AS (
  SELECT *, NOT low AND NOT lag(low, 1, TRUE) OVER d AND NOT lag(low, 2, TRUE) OVER d AS ends_healthy_stretch
  FROM paired
  WINDOW d AS (PARTITION BY branch_id ORDER BY day)
),
episode AS (
  SELECT branch_id, max(day) FILTER (WHERE ends_healthy_stretch) AS last_healthy_day
  FROM healthy
  GROUP BY branch_id
),
runs AS (
  SELECT p.branch_id,
         count(*) FILTER (WHERE p.low AND p.age <= 6) AS low_days,
         min(p.day) FILTER (WHERE p.low AND p.day > coalesce(e.last_healthy_day, DATE '-infinity')) AS episode_start,
         list(json_object('day', strftime(p.day, '%Y-%m-%d'), 'orders', p.orders,
                          'same_day_last_week', p.week_before) ORDER BY p.day) FILTER (WHERE p.age <= 7) AS last_7_days
  FROM paired p
  JOIN episode e ON e.branch_id = p.branch_id
  GROUP BY p.branch_id
),
today AS (
  SELECT o.branch_id, count(*) AS orders
  FROM orders o
  WHERE o.placed_at >= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) AND o.placed_at <= TIMESTAMP 'Fri 23:00:00' AND o.status <> 'rejected'
  GROUP BY o.branch_id
),
weekly AS (
  SELECT d.branch_id,
         sum(d.orders) FILTER (WHERE d.day >= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL 6 DAY) + coalesce(any_value(t.orders), 0) AS this_week,
         sum(d.orders) FILTER (WHERE d.day < CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL 6 DAY
                                 AND d.day >= CAST(TIMESTAMP 'Fri 23:00:00' AS DATE) - INTERVAL 13 DAY) AS last_week
  FROM daily d
  LEFT JOIN today t ON t.branch_id = d.branch_id
  GROUP BY d.branch_id
),
open_tickets AS (
  SELECT b.id AS branch_id, count(*) AS tickets,
         list(json_object('ticket_id', t.id, 'category', t.category, 'severity', t.severity,
                          'opened_at', t.opened_at) ORDER BY t.opened_at) AS ticket_list
  FROM tickets t
  JOIN branches b ON b.id = t.branch_id OR (t.branch_id IS NULL AND b.merchant_id = t.merchant_id)
  WHERE t.opened_at <= TIMESTAMP 'Fri 23:00:00' - INTERVAL (12) HOUR
    AND (t.closed_at IS NULL OR t.closed_at > TIMESTAMP 'Fri 23:00:00')
  GROUP BY b.id
),
auth_expired AS (
  SELECT DISTINCT b.id AS branch_id
  FROM integration_events e
  JOIN branches b ON b.pos_integration_id = e.integration_id
  WHERE e.type ILIKE '%auth_expired%' AND e.at > TIMESTAMP 'Fri 23:00:00' - INTERVAL 7 DAY AND e.at <= TIMESTAMP 'Fri 23:00:00'
),
overloaded AS (
  SELECT DISTINCT branch_id
  FROM (
    SELECT o.branch_id, date_trunc('hour', o.ready_at) AS hour, count(*) AS slow_orders
    FROM orders o
    WHERE o.ready_at > TIMESTAMP 'Fri 23:00:00' - INTERVAL 7 DAY AND o.ready_at <= TIMESTAMP 'Fri 23:00:00'
      AND date_diff('second', o.accepted_at, o.ready_at) >= 2 * 60 * o.promised_prep_min
    GROUP BY ALL
  )
  WHERE slow_orders >= 5
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id,
       CAST(r.episode_start AS TIMESTAMP) AS started_at,
       CASE WHEN w.this_week <= w.last_week * 0.4 THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'branch', b.name,
         'merchant', m.name,
         'csm_owner', m.csm_owner,
         'orders_this_week', w.this_week,
         'orders_last_week', w.last_week,
         'change_pct', round(100.0 * (w.this_week - w.last_week) / w.last_week, 1),
         'low_days', r.low_days,
         'low_day_window', 6,
         'last_7_days', r.last_7_days,
         'open_tickets', t.tickets,
         'tickets', t.ticket_list,
         'ruled_out', ['pos token expiry', 'kitchen overload']
       ) AS evidence
FROM weekly w
JOIN runs r ON r.branch_id = w.branch_id
JOIN branches b ON b.id = w.branch_id
JOIN merchants m ON m.id = b.merchant_id
JOIN open_tickets t ON t.branch_id = b.id
LEFT JOIN auth_expired ax ON ax.branch_id = b.id
LEFT JOIN overloaded ov ON ov.branch_id = b.id
WHERE w.last_week >= 14
  AND w.this_week <= w.last_week * (1 - 40 / 100.0)
  AND r.low_days >= 3
  AND t.tickets >= 1
  AND ax.branch_id IS NULL AND ov.branch_id IS NULL
  AND (m.churned_at IS NULL OR m.churned_at > TIMESTAMP 'Fri 23:00:00')
ORDER BY started_at, b.id