"""Canonical ontology: model hierarchy columns (family / canonical / artifact kind), run ids on every fact for batch rollback,
event semantics (recorded_at, occurred_at, is_backfill, group_key), benchmark comparability (config_key, trust_level, variant,
run_group, is_current), persisted entity-resolution decisions, anomaly flags, admin audit log, quarantined runs, taxonomy mappings.

Additive only — no column is dropped or retyped; existing rows keep their values.

Revision ID: 0003
"""
from __future__ import annotations

from alembic import op

revision = "0003"
down_revision = "0002"
branch_labels = None
depends_on = None

SQL = r"""
-- ---------------------------------------------------------------------------------------------- entity hierarchy
alter table entities add column if not exists family_id text references entities(id);
-- artifact → model; folded variant → model
alter table entities add column if not exists canonical_id text references entities(id);
-- checkpoint|quantization|conversion|packaging (artifacts only)
alter table entities add column if not exists artifact_kind text;
-- high|medium|low (how sure we are this row is one real thing)
alter table entities add column if not exists identity_confidence text not null default 'high';
create index if not exists entities_family_idx on entities (family_id) where family_id is not null;
create index if not exists entities_canonical_idx on entities (canonical_id) where canonical_id is not null;
create index if not exists entities_type_slug_idx on entities (entity_type, slug);
create index if not exists entities_type_status_idx on entities (entity_type, status) where merged_into is null;

-- ---------------------------------------------------------------------------------------------- run ids on facts (batch rollback)
alter table claims add column if not exists run_id text;
alter table relations add column if not exists run_id text;
alter table prices add column if not exists run_id text;
alter table benchmark_results add column if not exists run_id text;
alter table change_events add column if not exists run_id text;
create index if not exists claims_run_idx on claims (run_id) where run_id is not null;
create index if not exists relations_run_idx on relations (run_id) where run_id is not null;
create index if not exists prices_run_idx on prices (run_id) where run_id is not null;
create index if not exists benchmark_results_run_idx on benchmark_results (run_id) where run_id is not null;
create index if not exists change_events_run_idx on change_events (run_id) where run_id is not null;
-- source label when the canonical value differs
alter table claims add column if not exists value_raw text;

-- ---------------------------------------------------------------------------------------------- event semantics
alter table change_events add column if not exists recorded_at timestamptz not null default now();
alter table change_events add column if not exists is_backfill boolean not null default false;
-- semantic group (one release across documents)
alter table change_events add column if not exists group_key text;
alter table change_events add column if not exists occurred_at timestamptz generated always as (coalesce(effective_at, observed_at)) stored;
create index if not exists change_events_live_idx on change_events (occurred_at desc) where is_backfill = false and event_type <> 'DOCUMENT_CHANGED';
create index if not exists change_events_occurred_idx on change_events (occurred_at desc);
create index if not exists change_events_group_idx on change_events (group_key) where group_key is not null;
create index if not exists change_events_live_importance_idx on change_events (importance desc, occurred_at desc) where is_backfill = false;

-- ---------------------------------------------------------------------------------------------- benchmark comparability & trust
alter table benchmark_results add column if not exists config_key text;
alter table benchmark_results add column if not exists trust_level text;
alter table benchmark_results add column if not exists variant text;
alter table benchmark_results add column if not exists run_group text;
alter table benchmark_results add column if not exists is_current boolean not null default true;
alter table benchmark_results add column if not exists extractor text not null default 'deterministic';
create index if not exists benchmark_results_current2_idx on benchmark_results (benchmark_id, config_key, is_current) where valid_to is null;
create index if not exists benchmark_results_model_metric_idx on benchmark_results (model_id, benchmark_id, metric) where valid_to is null;

-- ---------------------------------------------------------------------------------------------- persisted resolution decisions
create table if not exists resolution_decisions (
  id          text primary key,
  a_id        text not null references entities(id) on delete cascade,
  b_id        text not null references entities(id) on delete cascade,
  decision    text not null,                 -- merge|alias|variant_of|family_member|keep_separate|defer
  actor       text not null default 'admin',
  note        text,
  payload     jsonb not null default '{}'::jsonb,
  applied     boolean not null default false,
  created_at  timestamptz not null default now(),
  unique (a_id, b_id, decision)
);
create index if not exists resolution_decisions_pair_idx on resolution_decisions (a_id, b_id);
create index if not exists resolution_decisions_b_idx on resolution_decisions (b_id);

-- ---------------------------------------------------------------------------------------------- anomaly flags (never deletes)
create table if not exists anomalies (
  id            text primary key,
  entity_id     text references entities(id) on delete cascade,
  check_name    text not null,
  severity      text not null,               -- critical|warning|info
  message       text not null,
  value         jsonb,
  detail        jsonb not null default '{}'::jsonb,
  status        text not null default 'open', -- open|resolved|ignored|fixed
  first_seen_at timestamptz not null default now(),
  last_seen_at  timestamptz not null default now(),
  resolved_at   timestamptz,
  resolution    text,
  dedupe_key    text unique
);
create index if not exists anomalies_status_idx on anomalies (status, severity, last_seen_at desc);
create index if not exists anomalies_entity_idx on anomalies (entity_id);

-- ---------------------------------------------------------------------------------------------- admin audit log
create table if not exists admin_audit_log (
  id         bigserial primary key,
  actor      text not null default 'admin',
  action     text not null,
  target     text,
  payload    jsonb not null default '{}'::jsonb,
  ip         text,
  created_at timestamptz not null default now()
);
create index if not exists admin_audit_log_created_idx on admin_audit_log (created_at desc);

-- ---------------------------------------------------------------------------------------------- quarantined runs (held facts awaiting review)
create table if not exists quarantined_runs (
  id             text primary key,
  run_id         text,
  connector_name text not null,
  reason         text not null,
  stats          jsonb not null default '{}'::jsonb,   -- baseline vs observed counts
  facts          jsonb not null default '[]'::jsonb,   -- serialised Facts objects, written on release
  status         text not null default 'pending',      -- pending|released|discarded
  created_at     timestamptz not null default now(),
  resolved_at    timestamptz,
  resolved_by    text
);
create index if not exists quarantined_runs_status_idx on quarantined_runs (status, created_at desc);

-- ---------------------------------------------------------------------------------------------- taxonomy mappings observed (raw → canonical)
create table if not exists taxonomy_mappings (
  domain     text not null,                  -- license|openness|modality|status|org_kind|hardware_kind|framework_kind|metric
  raw        text not null,
  canonical  text,
  count      integer not null default 1,
  first_seen_at timestamptz not null default now(),
  last_seen_at  timestamptz not null default now(),
  primary key (domain, raw)
);

-- ---------------------------------------------------------------------------------------------- connector run baselines (anomaly detector)
alter table connector_runs add column if not exists quarantined boolean not null default false;
alter table connector_runs add column if not exists baseline jsonb;
-- rolling medians of records/prices/results
alter table connectors add column if not exists baseline jsonb;

-- ---------------------------------------------------------------------------------------------- search vector: include aliases of family
create or replace function entities_search_update() returns trigger language plpgsql as $$
begin
  new.search :=
    setweight(to_tsvector('simple', coalesce(new.canonical_name, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(new.attributes->>'family', '')), 'B') ||
    setweight(to_tsvector('simple', coalesce(new.attributes->>'api_model_id', '')), 'B') ||
    setweight(to_tsvector('simple', coalesce(new.entity_type, '')), 'C') ||
    setweight(to_tsvector('english', left(coalesce(new.description, ''), 4000)), 'C');
  new.updated_at := now();
  return new;
end $$;
"""


def upgrade() -> None:
    for statement in _split(SQL):
        op.execute(statement)


def downgrade() -> None:
    raise RuntimeError("AI Atlas migrations are forward-only: historical data is never disposable")


def _split(sql: str) -> list[str]:
    out: list[str] = []
    buf: list[str] = []
    in_dollar = False
    for line in sql.splitlines():
        stripped = line.strip()
        if stripped.count("$$") % 2 == 1:
            in_dollar = not in_dollar
        buf.append(line)
        if not in_dollar and stripped.endswith(";"):
            body = [ln for ln in buf if not ln.strip().startswith("--") or in_dollar]
            stmt = "\n".join(body).strip()
            if stmt:
                out.append(stmt)
            buf = []
    tail = "\n".join(ln for ln in buf if not ln.strip().startswith("--")).strip()
    if tail:
        out.append(tail)
    return out