Definitions

WITH print_events AS (
  SELECT o.branch_id, e.order_id, e.at AS event_at, e.type, e.payload,
         e.type ILIKE '%fail%' AS is_failure,
         e.type ILIKE '%backup%' OR coalesce(e.payload->>'printer', '') ILIKE '%backup%' AS is_backup
  FROM order_events e
  JOIN orders o ON o.id = e.order_id
  WHERE e.type ILIKE '%print%'
    AND e.at >= TIMESTAMP 'Fri 20:00:00' AND e.at < TIMESTAMP 'Fri 23:00:00' AND e.at <= TIMESTAMP 'Fri 23:00:00'
),
per_branch AS (
  SELECT branch_id,
         max(event_at) FILTER (WHERE NOT is_failure AND NOT is_backup) AS last_main_print_at,
         max(event_at) FILTER (WHERE is_failure) AS last_failure_at,
         count(*) FILTER (WHERE is_backup AND NOT is_failure) AS backup_prints
  FROM print_events
  GROUP BY branch_id
),
streak AS (
  SELECT p.branch_id, min(e.event_at) AS streak_started_at, count(*) AS failures,
         arg_max(e.payload, e.event_at) AS last_error,
         list(e.order_id ORDER BY e.event_at) AS failed_order_ids
  FROM per_branch p
  JOIN print_events e ON e.branch_id = p.branch_id AND e.is_failure
   AND e.event_at > coalesce(p.last_main_print_at, TIMESTAMP '-infinity')
  GROUP BY p.branch_id
),
printers AS (
  SELECT i.branch_id, min(i.id) AS integration_id, any_value(v.name) AS vendor,
         any_value(i.status) AS status, max(i.last_sync_at) AS last_sync_at
  FROM integrations i
  JOIN integration_vendors v ON v.id = i.vendor_id
  WHERE v.kind = 'printer'
  GROUP BY i.branch_id
),
printer_state AS (
  SELECT i.branch_id,
         max(e.at) FILTER (WHERE e.type ILIKE '%offline%') AS reported_offline_at,
         max(e.at) FILTER (WHERE e.type ILIKE '%online%') AS reported_online_at
  FROM integration_events e
  JOIN integrations i ON i.id = e.integration_id
  JOIN integration_vendors v ON v.id = i.vendor_id AND v.kind = 'printer'
  WHERE e.at <= TIMESTAMP 'Fri 23:00:00'
  GROUP BY i.branch_id
)
SELECT 'branch' AS subject_type, b.id AS subject_id, b.id AS branch_id, b.merchant_id,
       pr.integration_id,
       CASE WHEN f.reported THEN least(ps.reported_offline_at, s.streak_started_at) ELSE s.streak_started_at END AS started_at,
       CASE WHEN coalesce(b.has_backup_printer, FALSE) THEN 'medium' ELSE 'high' END AS severity,
       json_object(
         'branch', b.name,
         'failures', coalesce(s.failures, 0),
         'streak_started_at', s.streak_started_at,
         'last_failure_at', p.last_failure_at,
         'last_main_print_at', p.last_main_print_at,
         'has_backup_printer', coalesce(b.has_backup_printer, FALSE),
         'backup_prints', coalesce(p.backup_prints, 0),
         'printer_vendor', pr.vendor,
         'printer_status', pr.status,
         'printer_last_sync_at', pr.last_sync_at,
         'printer_reported_offline_at', CASE WHEN f.reported THEN ps.reported_offline_at END,
         'last_error', s.last_error,
         'failed_order_ids', coalesce(s.failed_order_ids[1:20], [])
       ) AS evidence
FROM (
  SELECT b.id AS branch_id,
         ps.reported_offline_at > coalesce(ps.reported_online_at, TIMESTAMP '-infinity')
           AND ps.reported_offline_at > coalesce(p.last_main_print_at, TIMESTAMP '-infinity') AS reported,
         s.failures >= 2
           AND p.last_failure_at > coalesce(p.last_main_print_at, TIMESTAMP '-infinity') AS failing
  FROM branches b
  LEFT JOIN per_branch p ON p.branch_id = b.id
  LEFT JOIN streak s ON s.branch_id = b.id
  LEFT JOIN printer_state ps ON ps.branch_id = b.id
) f
JOIN branches b ON b.id = f.branch_id
LEFT JOIN per_branch p ON p.branch_id = b.id
LEFT JOIN streak s ON s.branch_id = b.id
LEFT JOIN printers pr ON pr.branch_id = b.id
LEFT JOIN printer_state ps ON ps.branch_id = b.id
WHERE coalesce(f.reported, FALSE) OR coalesce(f.failing, FALSE)
ORDER BY started_at, b.id