-- Labs experiment 003, specimen extraction record. Read-only queries run
-- against the production database on 2026-07-16, event
-- 5378cc28-1b6f-46a2-b24c-6c0f13f30020 (Negombo prison riot, 6-7 July 2026).
-- Quotes in extraction.json are verbatim substrings of
-- pipeline_articles.content as returned by these queries on that date.

-- 1. Candidate scan that surfaced the cluster: events from the last 14 days
--    whose article titles carry casualty words with diverging numbers.
WITH ea AS (
  SELECT ev.id, ev.title AS event_title, a.title AS art_title, a.domain
  FROM events_all ev
  JOIN event_articles j ON j.event_id::text = ev.id
  JOIN pipeline_articles a ON a.id = j.article_id
  WHERE ev.lang = 'en' AND ev.first_seen_at > now() - interval '14 days'
    AND ev.status = 'active' AND ev.summarized
),
hits AS (
  SELECT id, event_title, domain, m.num::int AS num
  FROM ea, LATERAL (
    SELECT (regexp_matches(art_title, '(\d{1,4})', 'g'))[1] AS num
  ) m
  WHERE art_title ~* '(kill|dead|death|die[sd]|injur|wound|toll|casualt|missing|zgin|ofiar|rann|tote|verletz|mort|bless)'
    AND m.num::int NOT BETWEEN 1900 AND 2100
)
SELECT id, left(event_title, 70) AS event_title,
       count(DISTINCT domain) AS domains,
       count(DISTINCT num)    AS distinct_nums,
       array_agg(DISTINCT num ORDER BY num) AS nums
FROM hits
GROUP BY id, event_title
HAVING count(DISTINCT num) >= 3 AND count(DISTINCT domain) >= 4
ORDER BY count(DISTINCT num) DESC, count(DISTINCT domain) DESC
LIMIT 25;

-- 2. The cluster pull for the chosen event: every article, with metadata
--    and content, ordered by publish time.
SELECT a.id, a.publish_date, a.domain, a.source, a.url, a.title, a.content
FROM event_articles j
JOIN pipeline_articles a ON a.id = j.article_id
WHERE j.event_id = '5378cc28-1b6f-46a2-b24c-6c0f13f30020'
ORDER BY a.publish_date;