-- Intel store: Phase 0 + Phase 1 of the predictive business-intelligence system
-- (docs/blueprint/intelligence-system.md, ADR-0017..0021).
--
-- Portable SQL: runs unchanged on SQLite (Phase 1, local — ADR-0020) and on
-- Postgres/Supabase when the store moves. The price of that portability:
-- timestamps are ISO-8601 UTC text, booleans are 0/1 integers, JSON is text, ids
-- are generated in Python (no serial/uuid defaults). Revisit all three when the
-- store moves to Postgres for good.
--
-- Applied by `py -m intel.cli init` (idempotent — every statement is IF NOT EXISTS).

-- ─── Gazetteer ──────────────────────────────────────────────────────────────
-- Everything in the store is keyed by location, so this comes first. Ids are
-- dotted paths: 'ng', 'ng.lagos', 'ng.lagos.ikeja'. State/LGA names are exactly
-- the ones users pick in onboarding (data/nigeria-locations.ts → nigeria_locations.json).
create table if not exists locations (
  id         text primary key,
  level      text not null check (level in ('country', 'state', 'lga', 'town')),
  name       text not null,
  parent_id  text references locations(id),
  lat        real,
  lon        real,
  source     text not null
);

-- Normalised alternative names → location. Seeded from the gazetteer itself,
-- boundary-file spellings, a short curated city list, and place-data localities
-- (intel/locations.py). The seed of the Layer 5 search/geocoding service.
create table if not exists location_aliases (
  alias        text not null,
  location_id  text not null references locations(id),
  origin       text not null,   -- 'gazetteer' | 'boundary' | 'curated' | 'places'
  primary key (alias, location_id)
);

-- ─── Layer 1: stale awareness ───────────────────────────────────────────────
-- One row per external source. "Stale" means late against the source's OWN
-- cadence, and two dates are tracked because a fetch can succeed today and still
-- return last quarter's numbers.
create table if not exists sources (
  id                      text primary key,
  family                  text not null,
  provider                text not null,
  licence                 text not null,
  expected_cadence_hours  integer not null,
  latency_hours           integer not null default 0,  -- how far behind "now" the newest data legitimately is
  last_attempt_at         text,
  last_success_at         text,
  latest_data_period      text,                         -- newest period the source has given us
  last_error              text
);

-- Every layer reports "done" and the receiving layer acknowledges it. Layer 1
-- flags reports nobody received.
create table if not exists handoffs (
  id           text primary key,
  run_id       text not null,
  from_layer   text not null,
  to_layer     text not null,
  report       text not null,   -- JSON
  produced_at  text not null,
  received_at  text,
  received_by  text
);
create index if not exists handoffs_pending on handoffs (to_layer, received_at);

-- ─── Layer 2: data stream ───────────────────────────────────────────────────
-- A stream is one metric from one source, stored at its NATIVE resolution
-- (never copied down to finer locations — that would fake precision the models
-- would then learn from).
create table if not exists streams (
  id            text primary key,   -- 'weather.precipitation_sum'
  family        text not null,
  metric        text not null,
  unit          text not null,
  kind          text not null check (kind in ('driver', 'indicator')),
  native_level  text not null check (native_level in ('country', 'state', 'lga', 'town', 'point')),
  cadence       text not null check (cadence in ('daily', 'weekly', 'monthly', 'quarterly', 'yearly', 'event')),
  source_id     text not null references sources(id)
);

-- Numeric time series. Observed history and forecasts share one table:
-- history has is_forecast=0 and issued_at=''; each forecast run keeps its own
-- rows (issued_at = run date) so forecast skill can be measured later.
create table if not exists observations (
  stream_id     text not null references streams(id),
  location_id   text not null references locations(id),
  period_start  text not null,
  is_forecast   integer not null default 0 check (is_forecast in (0, 1)),
  issued_at     text not null default '',
  value         real not null,
  ingested_at   text not null,
  primary key (stream_id, location_id, period_start, is_forecast, issued_at)
);
create index if not exists observations_loc on observations (location_id, stream_id, period_start);

-- Things that happen rather than values that move: a policy enacted, a holiday,
-- an incident, a project gazetted. Kept permanently — this is the history the
-- correlation step and models learn from (unlike news_items, which expire from
-- the user-facing feed).
create table if not exists events (
  id           text primary key,
  family       text not null,   -- policy | weather | infrastructure | funding | security | calendar | other
  type         text not null,   -- e.g. 'public_holiday', 'religious_holiday', 'season', 'news'
  location_id  text references locations(id),
  starts_on    text not null,
  ends_on      text,
  title        text not null,
  detail       text,
  is_estimate  integer not null default 0 check (is_estimate in (0, 1)),
  source_id    text not null references sources(id),
  source_ref   text not null,   -- natural key at the source, so re-ingest is idempotent
  ingested_at  text not null,
  unique (source_id, source_ref)
);
create index if not exists events_loc on events (location_id, starts_on);

-- Points of interest. subsector is one of backend/news/subsectors.json, or null
-- for a context place (worship, civic, landmark) — those still matter as
-- footfall generators even though they aren't competitors.
create table if not exists places (
  id                text primary key,   -- Overture GERS id
  name              text,
  category          text,
  taxonomy          text,
  subsector         text,
  confidence        real,
  operating_status  text,
  lat               real not null,
  lon               real not null,
  location_id       text references locations(id),   -- the LGA it falls in
  locality          text,
  source_id         text not null references sources(id),
  release           text not null
);
create index if not exists places_loc on places (location_id, subsector);