CREATE TABLE IF NOT EXISTS schema_versions(version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS operators(
 id TEXT PRIMARY KEY, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS auth_sessions(
 token_hash TEXT PRIMARY KEY, operator_id TEXT REFERENCES operators(id), csrf TEXT NOT NULL,
 expires_at BIGINT NOT NULL, created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS auth_attempts(key TEXT PRIMARY KEY, failures INTEGER NOT NULL, window_start BIGINT NOT NULL);
CREATE TABLE IF NOT EXISTS learning_tasks(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id), name TEXT NOT NULL,
 platform TEXT NOT NULL, entry_url TEXT NOT NULL, goal TEXT NOT NULL, account_key TEXT NOT NULL,
 parameters_json TEXT NOT NULL, status TEXT NOT NULL, identity_label TEXT, execution_profile TEXT,
 created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE TABLE IF NOT EXISTS observations(
 id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), data_json TEXT NOT NULL,
 created_at TEXT NOT NULL, ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS observations_task ON observations(task_id,ordinal);
CREATE TABLE IF NOT EXISTS learning_steps(
 id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), position INTEGER NOT NULL,
 data_json TEXT NOT NULL, created_at TEXT NOT NULL, UNIQUE(task_id,position)
);
CREATE TABLE IF NOT EXISTS candidates(
 id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), name TEXT NOT NULL,
 version INTEGER NOT NULL, digest TEXT NOT NULL, manifest_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE, UNIQUE(task_id,version)
);
CREATE TABLE IF NOT EXISTS validations(
 id TEXT PRIMARY KEY, candidate_id TEXT NOT NULL REFERENCES candidates(id), task_id TEXT NOT NULL REFERENCES learning_tasks(id),
 kind TEXT NOT NULL, status TEXT NOT NULL, data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS validations_candidate ON validations(candidate_id,ordinal);
CREATE TABLE IF NOT EXISTS quality_observations(
 id TEXT PRIMARY KEY, validation_id TEXT NOT NULL REFERENCES validations(id),
 candidate_id TEXT NOT NULL REFERENCES candidates(id), task_id TEXT NOT NULL REFERENCES learning_tasks(id),
 data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS quality_observations_candidate ON quality_observations(candidate_id,ordinal);
CREATE TABLE IF NOT EXISTS discovery_runs(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
 candidate_id TEXT NOT NULL REFERENCES candidates(id), validation_id TEXT REFERENCES validations(id),
 data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS discovery_runs_owner ON discovery_runs(owner_id,ordinal);
CREATE TABLE IF NOT EXISTS reading_runs(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
 discovery_run_id TEXT NOT NULL REFERENCES discovery_runs(id), candidate_id TEXT NOT NULL REFERENCES candidates(id),
 validation_id TEXT REFERENCES validations(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS reading_runs_owner ON reading_runs(owner_id,ordinal);
CREATE TABLE IF NOT EXISTS analysis_runs(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
 reading_run_id TEXT NOT NULL REFERENCES reading_runs(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS analysis_runs_reading ON analysis_runs(reading_run_id,ordinal);
CREATE TABLE IF NOT EXISTS business_briefs(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
 kind TEXT NOT NULL CHECK(kind IN ('parse','confirmed')), parent_id TEXT REFERENCES business_briefs(id), cache_key TEXT,
 data_json TEXT NOT NULL, created_at TEXT NOT NULL, ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE,
 CHECK((kind='parse' AND parent_id IS NULL) OR (kind='confirmed' AND parent_id IS NOT NULL))
);
CREATE INDEX IF NOT EXISTS business_briefs_owner ON business_briefs(owner_id,ordinal);
CREATE INDEX IF NOT EXISTS business_briefs_cache ON business_briefs(owner_id,cache_key);
CREATE TABLE IF NOT EXISTS analysis_reviews(
 id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
 analysis_run_id TEXT NOT NULL REFERENCES analysis_runs(id), source_ref TEXT NOT NULL,
 previous_id TEXT REFERENCES analysis_reviews(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
 ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
);
CREATE INDEX IF NOT EXISTS analysis_reviews_analysis ON analysis_reviews(analysis_run_id,ordinal);
CREATE UNIQUE INDEX IF NOT EXISTS analysis_reviews_first ON analysis_reviews(owner_id,analysis_run_id,source_ref) WHERE previous_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS analysis_reviews_successor ON analysis_reviews(previous_id) WHERE previous_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS artifacts(
 id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), relative_path TEXT NOT NULL,
 sha256 TEXT NOT NULL, bytes BIGINT NOT NULL, created_at TEXT NOT NULL
);
CREATE OR REPLACE FUNCTION reject_learning_fact_mutation() RETURNS TRIGGER
LANGUAGE plpgsql AS $guard$
BEGIN
 RAISE EXCEPTION 'learning facts are immutable and append only' USING ERRCODE='23514';
END;
$guard$;