-- WebSensor 0.2 — one-time backfills after 0005 (idempotent, additive).

-- Provenance columns on existing events from their source.
update events e set first_party = s.first_party, country = coalesce(e.country, s.country), language = coalesce(e.language, s.language, 'en')
from sources s where s.id = e.source_id and (e.country is null or e.language is null);

-- Cluster statistics from the events they contain.
update event_clusters c set
  source_count = greatest(1, (select count(distinct e.source_id) from events e where e.cluster_id = c.id)),
  first_party_count = (select count(*) from events e where e.cluster_id = c.id and e.first_party),
  external_count = (select count(*) from events e where e.cluster_id = c.id and not e.first_party),
  first_party_at = (select min(e.detected_at) from events e where e.cluster_id = c.id and e.first_party),
  first_external_at = (select min(e.detected_at) from events e where e.cluster_id = c.id and not e.first_party)
where c.last_at >= now() - interval '10 days';
update event_clusters set lead_time_ms = extract(epoch from (first_external_at - first_party_at)) * 1000 where first_party_at is not null and first_external_at is not null and lead_time_ms is null;

-- Initial breaking states for recent clusters (the engine maintains them from now on).
update event_clusters c set state = case
  when c.last_at < now() - interval '72 hours' then 'closed'
  when c.max_importance >= 85 and c.first_at >= now() - interval '6 hours' and (c.source_count >= 2 or c.event_count >= 2) and c.title not like '%new items in%' then 'breaking'
  when c.source_count >= 3 and c.last_at >= now() - interval '24 hours' then 'confirmed'
  when c.max_importance >= 65 and c.event_count >= 3 and c.first_at >= now() - interval '12 hours' then 'developing'
  else 'watching' end
where c.state = 'watching';

-- Signal score for existing events (approximation: importance blended with confidence/novelty; the engine computes the full score for new events).
update events set signal_score = round((0.6 * importance + 0.2 * confidence + 0.2 * novelty + case when first_party then 4 else -6 end + case when evidence_label = 'CONFIRMED' then 6 else 0 end + case when silent_change then 3 else 0 end)::numeric, 1)
where signal_score is null;
update events set signal_score = least(100, greatest(0, signal_score)) where signal_score is not null and (signal_score > 100 or signal_score < 0);