-- WebSensor 0.3 — Source Factory & coverage (2026-09-13). Additive only.

-- ---- Sources: origin (seed = YAML registry · import = admin import · factory = Source Factory) ----
alter table sources add column if not exists origin text not null default 'seed';
alter table sources add column if not exists sector text;                              -- coverage sector that produced the source
create index if not exists sources_origin_idx on sources (origin);
create index if not exists sources_domain_idx on sources (domain);

-- ---- Sensors: SHADOW status (observed, evidence stored, never published) ----------------------
create index if not exists sensors_status_idx on sensors (status);
create index if not exists sensors_url_host_idx on sensors ((lower(split_part(split_part(url, '/', 3), ':', 1))));

-- ---- Factory seeds: organizations to expand -------------------------------------------------
create table if not exists factory_seeds (
  id text primary key,                                   -- kebab id; becomes the source id when a new source is created
  name text not null,
  domain text not null,
  homepage text,
  categories text[] not null default '{}',
  country text,
  language text,
  tier text not null default 'B',
  weight real not null default 1,
  importance smallint not null default 2,               -- 1 long tail · 2 notable · 3 systemic
  aliases text[] not null default '{}',
  first_party boolean not null default true,
  sector text,
  universe text,
  hints jsonb not null default '{}',
  status text not null default 'queued',                 -- queued | discovering | discovered | blocked | error | done
  attempts integer not null default 0,
  source_id text,                                        -- existing or created source
  candidates integer not null default 0,
  shadow integer not null default 0,
  accepted integer not null default 0,
  rejected integer not null default 0,
  last_error text,
  discovered_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists factory_seeds_status_idx on factory_seeds (status, importance desc, created_at);
create index if not exists factory_seeds_domain_idx on factory_seeds (domain);
create index if not exists factory_seeds_sector_idx on factory_seeds (sector);

-- ---- Discovery candidates: full lifecycle (candidate → shadow → accepted | rejected | duplicate | blocked) ----
alter table discovery_candidates add column if not exists seed_id text;
alter table discovery_candidates add column if not exists connector text;
alter table discovery_candidates add column if not exists type text;
alter table discovery_candidates add column if not exists name text;
alter table discovery_candidates add column if not exists kind_class text;             -- news press blog ir filings changelog releases security pricing legal careers docs api status sitemap models data posture other
alter table discovery_candidates add column if not exists config jsonb not null default '{}';
alter table discovery_candidates add column if not exists tier text;
alter table discovery_candidates add column if not exists score_value real;
alter table discovery_candidates add column if not exists reason text;
alter table discovery_candidates add column if not exists shadow_sensor_id text;
alter table discovery_candidates add column if not exists decided_at timestamptz;
alter table discovery_candidates add column if not exists updated_at timestamptz not null default now();
create index if not exists discovery_candidates_status_idx on discovery_candidates (status, score_value desc);
create index if not exists discovery_candidates_seed_idx on discovery_candidates (seed_id);
create index if not exists discovery_candidates_shadow_idx on discovery_candidates (shadow_sensor_id) where shadow_sensor_id is not null;

-- ---- Factory daily counters (funnel) ----------------------------------------------------------
create table if not exists factory_daily (
  day date primary key,
  seeds_processed integer not null default 0,
  requests integer not null default 0,
  candidates integer not null default 0,
  shadow_created integer not null default 0,
  accepted integer not null default 0,
  rejected integer not null default 0,
  blocked integer not null default 0
);