-- WebSensor core schema (PostgreSQL 17). All timestamps are UTC (timestamptz).

create table if not exists sources (
  id text primary key,
  name text not null,
  domain text not null,
  homepage text,
  description text,
  categories text[] not null default '{}',
  tier text not null default 'B',
  importance_weight real not null default 1.0,
  discover jsonb not null default '{}',
  fallback jsonb not null default '{}',
  enabled boolean not null default true,
  robots_checked_at timestamptz,
  terms_reviewed_at timestamptz,
  allowed_methods text[] not null default '{}',
  rate_limit_per_min integer,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists sources_domain_idx on sources (domain);
create index if not exists sources_categories_idx on sources using gin (categories);

create table if not exists sensors (
  id text primary key,
  source_id text not null references sources(id) on delete cascade,
  name text not null,
  url text not null,
  type text not null,
  connector text not null,
  tier text not null default 'B',
  importance_weight real not null default 1.0,
  config jsonb not null default '{}',
  base_interval_seconds integer,
  enabled boolean not null default true,
  health text not null default 'UP',
  next_check_at timestamptz not null default now(),
  last_check_at timestamptz,
  last_change_at timestamptz,
  last_event_at timestamptz,
  last_status integer,
  last_error text,
  etag text,
  last_modified text,
  state jsonb,
  last_snapshot_id text,
  consecutive_errors integer not null default 0,
  total_runs integer not null default 0,
  total_not_modified integer not null default 0,
  raw_changes integer not null default 0,
  meaningful_changes integer not null default 0,
  avg_latency_ms integer,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists sensors_next_check_idx on sensors (next_check_at) where enabled;
create index if not exists sensors_source_idx on sensors (source_id);
create index if not exists sensors_url_idx on sensors (url);

create table if not exists sensor_runs (
  id text primary key,
  sensor_id text not null references sensors(id) on delete cascade,
  started_at timestamptz not null,
  finished_at timestamptz,
  http_status integer,
  outcome text not null,
  error text,
  duration_ms integer,
  bytes integer,
  fetch_method text,
  snapshot_id text
);
create index if not exists sensor_runs_sensor_idx on sensor_runs (sensor_id, started_at desc);
create index if not exists sensor_runs_started_idx on sensor_runs (started_at desc);

create table if not exists snapshots (
  id text primary key,
  sensor_id text not null references sensors(id) on delete cascade,
  url text not null,
  captured_at timestamptz not null,
  http_status integer,
  content_type text,
  content_length integer,
  content_hash text not null,
  canonical_hash text not null,
  semantic_hash text,
  etag text,
  last_modified text,
  storage_key text,
  canonical_storage_key text,
  parser_version text not null,
  fetch_duration_ms integer,
  fetch_method text,
  mode text not null,
  title text,
  published_at timestamptz,
  extraction_confidence real,
  extra jsonb
);
create index if not exists snapshots_sensor_idx on snapshots (sensor_id, captured_at desc);
create index if not exists snapshots_hash_idx on snapshots (canonical_hash);

create table if not exists changes (
  id text primary key,
  sensor_id text not null references sensors(id) on delete cascade,
  old_snapshot_id text references snapshots(id),
  new_snapshot_id text not null references snapshots(id),
  detected_at timestamptz not null,
  kind text not null,
  diff jsonb not null,
  diff_storage_key text,
  signal real not null,
  noise_ratio real not null,
  magnitude real not null,
  heuristic jsonb not null,
  meaningful boolean not null default false,
  event_id text
);
create index if not exists changes_sensor_idx on changes (sensor_id, detected_at desc);
create index if not exists changes_detected_idx on changes (detected_at desc);

create table if not exists event_clusters (
  id text primary key,
  title text not null,
  summary text,
  primary_event_id text,
  entity_ids text[] not null default '{}',
  categories text[] not null default '{}',
  event_count integer not null default 0,
  max_importance real not null default 0,
  first_at timestamptz not null,
  last_at timestamptz not null
);
create index if not exists event_clusters_last_idx on event_clusters (last_at desc);

create table if not exists events (
  id text primary key,
  slug text not null unique,
  sensor_id text not null references sensors(id) on delete cascade,
  source_id text not null references sources(id) on delete cascade,
  cluster_id text references event_clusters(id),
  change_id text references changes(id),
  old_snapshot_id text references snapshots(id),
  new_snapshot_id text references snapshots(id),
  url text not null,
  event_type text not null,
  title text not null,
  summary text not null,
  why_it_matters text,
  importance real not null,
  importance_components jsonb not null default '{}',
  confidence real not null,
  novelty real not null,
  categories text[] not null default '{}',
  keywords text[] not null default '{}',
  silent_change boolean not null default false,
  evidence_label text not null default 'OBSERVED',
  published_at timestamptz,
  observed_from timestamptz,
  detected_at timestamptz not null,
  processed_at timestamptz not null,
  published_to_feed_at timestamptz,
  detection_latency_ms integer,
  processing_latency_ms integer,
  processing_version text not null,
  interpretation jsonb not null default '{}',
  search tsvector generated always as (
    setweight(to_tsvector('english'::regconfig, coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english'::regconfig, coalesce(summary, '')), 'B') ||
    setweight(array_to_tsvector(coalesce(keywords, '{}'::text[])), 'C')
  ) stored
);
create index if not exists events_detected_idx on events (detected_at desc);
create index if not exists events_importance_idx on events (importance desc, detected_at desc);
create index if not exists events_source_idx on events (source_id, detected_at desc);
create index if not exists events_sensor_idx on events (sensor_id, detected_at desc);
create index if not exists events_type_idx on events (event_type);
create index if not exists events_cluster_idx on events (cluster_id);
create index if not exists events_categories_idx on events using gin (categories);
create index if not exists events_silent_idx on events (detected_at desc) where silent_change;
create index if not exists events_search_idx on events using gin (search);

create table if not exists interpretations (
  id bigserial primary key,
  event_id text not null references events(id) on delete cascade,
  version integer not null,
  model text not null,
  payload jsonb not null,
  created_at timestamptz not null default now(),
  unique (event_id, version)
);

create table if not exists entities (
  id text primary key,
  name text not null,
  type text not null,
  description text,
  domain text,
  homepage text,
  importance real not null default 50,
  categories text[] not null default '{}',
  parent_id text references entities(id),
  metadata jsonb not null default '{}',
  event_count integer not null default 0,
  last_event_at timestamptz,
  created_at timestamptz not null default now(),
  search tsvector generated always as (
    setweight(to_tsvector('simple'::regconfig, coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english'::regconfig, coalesce(description, '')), 'B')
  ) stored
);
create index if not exists entities_type_idx on entities (type);
create index if not exists entities_search_idx on entities using gin (search);
create index if not exists entities_domain_idx on entities (domain);

create table if not exists entity_aliases (
  alias text primary key,
  entity_id text not null references entities(id) on delete cascade
);
create index if not exists entity_aliases_entity_idx on entity_aliases (entity_id);

create table if not exists event_entities (
  event_id text not null references events(id) on delete cascade,
  entity_id text not null references entities(id) on delete cascade,
  role text not null default 'subject',
  primary key (event_id, entity_id)
);
create index if not exists event_entities_entity_idx on event_entities (entity_id);

create table if not exists source_entities (
  source_id text not null references sources(id) on delete cascade,
  entity_id text not null references entities(id) on delete cascade,
  primary key (source_id, entity_id)
);

create table if not exists entity_relations (
  from_id text not null references entities(id) on delete cascade,
  relation text not null,
  to_id text not null references entities(id) on delete cascade,
  metadata jsonb not null default '{}',
  primary key (from_id, relation, to_id)
);

create table if not exists urls (
  url text primary key,
  domain text not null,
  source_id text references sources(id) on delete cascade,
  sensor_id text references sensors(id) on delete set null,
  first_seen_at timestamptz not null default now(),
  last_seen_at timestamptz not null default now(),
  status text not null default 'active',
  missing_count integer not null default 0,
  snapshot_count integer not null default 0,
  change_count integer not null default 0
);
create index if not exists urls_domain_idx on urls (domain);
create index if not exists urls_sensor_idx on urls (sensor_id);

create table if not exists url_history (
  id bigserial primary key,
  url text not null,
  at timestamptz not null default now(),
  kind text not null,
  snapshot_id text,
  change_id text,
  event_id text,
  note text
);
create index if not exists url_history_url_idx on url_history (url, at desc);

create table if not exists watchlists (
  id text primary key,
  owner_token text not null,
  name text not null,
  created_at timestamptz not null default now()
);
create index if not exists watchlists_owner_idx on watchlists (owner_token);

create table if not exists watchlist_items (
  watchlist_id text not null references watchlists(id) on delete cascade,
  kind text not null,
  value text not null,
  added_at timestamptz not null default now(),
  primary key (watchlist_id, kind, value)
);

create table if not exists alerts (
  id text primary key,
  owner_token text not null,
  name text not null,
  rule jsonb not null,
  channel text not null default 'web',
  enabled boolean not null default true,
  created_at timestamptz not null default now(),
  last_fired_at timestamptz
);
create index if not exists alerts_owner_idx on alerts (owner_token);

create table if not exists notifications (
  id bigserial primary key,
  alert_id text not null references alerts(id) on delete cascade,
  event_id text not null references events(id) on delete cascade,
  created_at timestamptz not null default now(),
  read_at timestamptz
);

create table if not exists connector_health (
  connector text primary key,
  status text not null default 'UP',
  runs_24h integer not null default 0,
  errors_24h integer not null default 0,
  success_rate real,
  avg_latency_ms integer,
  changes_24h integer not null default 0,
  events_24h integer not null default 0,
  last_success_at timestamptz,
  last_error_at timestamptz,
  last_error text,
  http_codes jsonb not null default '{}',
  rate_limit_until timestamptz,
  updated_at timestamptz not null default now()
);

create table if not exists discovery_candidates (
  id text primary key,
  source_id text not null references sources(id) on delete cascade,
  url text not null,
  kind text not null,
  evidence text,
  score jsonb not null default '{}',
  status text not null default 'candidate',
  found_at timestamptz not null default now(),
  unique (source_id, url)
);

create table if not exists metrics_daily (
  day date primary key,
  checks bigint not null default 0,
  not_modified bigint not null default 0,
  bytes bigint not null default 0,
  raw_changes bigint not null default 0,
  events bigint not null default 0,
  silent_events bigint not null default 0,
  errors bigint not null default 0,
  llm_calls bigint not null default 0,
  llm_input_tokens bigint not null default 0,
  llm_output_tokens bigint not null default 0
);

create table if not exists llm_usage (
  id bigserial primary key,
  at timestamptz not null default now(),
  model text not null,
  purpose text not null,
  input_tokens integer not null,
  output_tokens integer not null,
  event_id text,
  ok boolean not null default true
);