-- WebSensor 0.2 — signal intelligence upgrade (2026-09-11).
-- Additive only: new columns, tables and indexes. No data is destroyed.

-- ---- Sources: provenance & geography ----------------------------------------------------
alter table sources add column if not exists first_party boolean not null default true;
alter table sources add column if not exists country text;
alter table sources add column if not exists language text;
alter table sources add column if not exists kind text not null default 'registry';   -- registry | custom
alter table sources add column if not exists owner_token text;                          -- custom monitors only
create index if not exists sources_country_idx on sources (country) where country is not null;
create index if not exists sources_owner_idx on sources (owner_token) where owner_token is not null;

-- ---- Sensors: lifecycle status (DISCOVERED → PENDING → VALIDATED → ACTIVE → DEGRADED → DISABLED)
alter table sensors add column if not exists status text not null default 'ACTIVE';
alter table sensors add column if not exists validated_at timestamptz;
alter table sensors add column if not exists priority smallint not null default 2;      -- 0 critical … 3 low
update sensors set status = case when not enabled then 'DISABLED' when health in ('ERROR','RATE_LIMITED') then 'DEGRADED' else 'ACTIVE' end where status = 'ACTIVE';

-- ---- Changes: semantic class -------------------------------------------------------------
alter table changes add column if not exists change_class text;                          -- meaningful | pricing | policy | product | personnel | cosmetic | navigation | timestamp | advertisement | boilerplate
alter table changes add column if not exists field_changes jsonb;                       -- [{label, before, after, delta_pct}]

-- ---- Events: richer scores & provenance ----------------------------------------------------
alter table events add column if not exists fingerprint text;                           -- deterministic (sensor + canonical hashes) → idempotent ingestion
alter table events add column if not exists signal_score real;                          -- WebSensor Signal Score 0–100
alter table events add column if not exists velocity_score real not null default 0;
alter table events add column if not exists impact_score real not null default 0;
alter table events add column if not exists anomaly_score real not null default 0;
alter table events add column if not exists change_class text;
alter table events add column if not exists first_party boolean not null default true;
alter table events add column if not exists country text;
alter table events add column if not exists language text;
alter table events add column if not exists canonical_url text;
alter table events add column if not exists field_changes jsonb;
alter table events add column if not exists score_reasons jsonb not null default '[]';  -- explainable score decomposition
create unique index if not exists events_fingerprint_uidx on events (fingerprint) where fingerprint is not null;
create index if not exists events_signal_idx on events (signal_score desc, detected_at desc) where signal_score is not null;
create index if not exists events_country_idx on events (country, detected_at desc) where country is not null;
create index if not exists events_first_party_idx on events (detected_at desc) where first_party;
create index if not exists events_class_idx on events (change_class) where change_class is not null;

-- ---- Clusters: propagation & confirmation -------------------------------------------------
alter table event_clusters add column if not exists slug text;
alter table event_clusters add column if not exists source_count integer not null default 1;
alter table event_clusters add column if not exists first_party_count integer not null default 0;
alter table event_clusters add column if not exists external_count integer not null default 0;
alter table event_clusters add column if not exists velocity real not null default 0;    -- signals per hour over the active window
alter table event_clusters add column if not exists state text not null default 'watching';  -- breaking | developing | confirmed | watching | closed
alter table event_clusters add column if not exists lead_time_ms bigint;                -- first-party detection → first external report
alter table event_clusters add column if not exists first_party_at timestamptz;
alter table event_clusters add column if not exists first_external_at timestamptz;
alter table event_clusters add column if not exists timeline jsonb not null default '[]'; -- [{at, event_id, source_id, first_party, kind}]
create unique index if not exists event_clusters_slug_uidx on event_clusters (slug) where slug is not null;
create index if not exists event_clusters_state_idx on event_clusters (state, last_at desc);
update event_clusters set slug = id where slug is null;

-- ---- Entity daily activity (heatmaps, baselines, anomaly) ---------------------------------
create table if not exists entity_daily (
  entity_id text not null references entities(id) on delete cascade,
  day date not null,
  events integer not null default 0,
  silent integer not null default 0,
  breaking integer not null default 0,
  max_importance real not null default 0,
  primary key (entity_id, day)
);
-- Backfill from the existing event history (idempotent).
insert into entity_daily (entity_id, day, events, silent, breaking, max_importance)
select ee.entity_id, (e.detected_at at time zone 'UTC')::date, count(*), sum(case when e.silent_change then 1 else 0 end), sum(case when e.importance >= 80 then 1 else 0 end), max(e.importance)
from events e join event_entities ee on ee.event_id = e.id
group by ee.entity_id, (e.detected_at at time zone 'UTC')::date
on conflict (entity_id, day) do update set events = excluded.events, silent = excluded.silent, breaking = excluded.breaking, max_importance = excluded.max_importance;

-- Source daily activity (source quality, heatmaps)
create table if not exists source_daily (
  source_id text not null references sources(id) on delete cascade,
  day date not null,
  checks integer not null default 0,
  not_modified integer not null default 0,
  errors integer not null default 0,
  raw_changes integer not null default 0,
  events integer not null default 0,
  primary key (source_id, day)
);

-- ---- Bookmarks (anonymous owner token, phase 1) -------------------------------------------
create table if not exists bookmarks (
  owner_token text not null,
  event_id text not null references events(id) on delete cascade,
  note text,
  created_at timestamptz not null default now(),
  primary key (owner_token, event_id)
);
create index if not exists bookmarks_owner_idx on bookmarks (owner_token, created_at desc);

-- ---- Alerts: channel configuration + delivery log ------------------------------------------
alter table alerts add column if not exists channel_config jsonb not null default '{}';   -- webhook: {url, secret}
alter table alerts add column if not exists fired_count integer not null default 0;
alter table notifications add column if not exists channel text not null default 'web';
alter table notifications add column if not exists status text not null default 'queued'; -- queued | delivered | failed
alter table notifications add column if not exists delivered_at timestamptz;
alter table notifications add column if not exists error text;
create index if not exists notifications_alert_idx on notifications (alert_id, created_at desc);
create index if not exists notifications_status_idx on notifications (status) where status = 'queued';

-- ---- Saved views (URL filter presets) ------------------------------------------------------
create table if not exists saved_views (
  id text primary key,
  owner_token text not null,
  name text not null,
  query text not null,
  created_at timestamptz not null default now()
);
create index if not exists saved_views_owner_idx on saved_views (owner_token);

-- ---- Watchlist item kinds are free text already; nothing to migrate.