-- Experiment 004 extraction record. Run read-only against the production DB.
-- Window frozen 2026-07-06T00:00Z .. 2026-07-13T00:00Z. Coverage = DISTINCT
-- domains per logical event; article volume never counts.

-- Q1: domain-by-event incidence for events with >= 3 distinct source domains.
COPY (
  WITH ev AS (
    SELECT ea.event_id, min(pa.publish_date) AS t0,
           count(DISTINCT pa.domain) AS deg
    FROM event_articles ea
    JOIN pipeline_articles pa ON pa.id = ea.article_id
    GROUP BY ea.event_id
  ), win AS (
    SELECT event_id FROM ev
    WHERE t0 >= '2026-07-06T00:00:00Z' AND t0 < '2026-07-13T00:00:00Z'
      AND deg >= 3
  )
  SELECT DISTINCT ea.event_id::text AS event_id, pa.domain
  FROM event_articles ea
  JOIN pipeline_articles pa ON pa.id = ea.article_id
  JOIN win w ON w.event_id = ea.event_id
  ORDER BY 1, 2
) TO STDOUT WITH CSV HEADER;

-- Q2: titles for the window's 15 highest-degree events that have a published
-- EN row (display constraint for Fig. 1 only; Q1 has no such restriction).
WITH ev AS (
  SELECT ea.event_id, min(pa.publish_date) AS t0,
         count(DISTINCT pa.domain) AS deg
  FROM event_articles ea
  JOIN pipeline_articles pa ON pa.id = ea.article_id
  GROUP BY ea.event_id
), win AS (
  SELECT event_id, t0, deg FROM ev
  WHERE t0 >= '2026-07-06T00:00:00Z' AND t0 < '2026-07-13T00:00:00Z'
    AND deg >= 3
)
SELECT w.event_id, w.deg, to_char(w.t0, 'MM-DD') AS d0, e.title
FROM win w
JOIN events_all e ON e.id = w.event_id::text AND e.lang = 'en'
ORDER BY w.deg DESC LIMIT 15;