PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE events (
    id TEXT PRIMARY KEY,
    schema_version INTEGER NOT NULL,
    observed_at TEXT NOT NULL,
    recorded_at TEXT NOT NULL,
    source_json TEXT NOT NULL CHECK (json_valid(source_json)),
    event_type TEXT NOT NULL,
    trace_id TEXT NULL,
    session_id TEXT NULL,
    domain_tags_json TEXT NOT NULL CHECK (json_valid(domain_tags_json)),
    payload_json TEXT NOT NULL CHECK (json_valid(payload_json)),
    payload_hash TEXT NOT NULL,
    prev_event_hash TEXT NULL,
    event_hash TEXT NOT NULL UNIQUE
);

CREATE TABLE traces (
    id TEXT PRIMARY KEY,
    schema_version INTEGER NOT NULL,
    opened_at TEXT NOT NULL,
    closed_at TEXT NULL,
    trace_type TEXT NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE trace_events (
    trace_id TEXT NOT NULL REFERENCES traces(id),
    event_id TEXT NOT NULL REFERENCES events(id),
    ordinal INTEGER NOT NULL,
    PRIMARY KEY (trace_id, event_id),
    UNIQUE (trace_id, ordinal)
);

CREATE TABLE episodes (
    id TEXT PRIMARY KEY,
    trace_id TEXT NOT NULL REFERENCES traces(id),
    source_events_json TEXT NOT NULL CHECK (json_valid(source_events_json)),
    summary TEXT NOT NULL,
    domains_json TEXT NOT NULL CHECK (json_valid(domains_json)),
    entities_json TEXT NOT NULL CHECK (json_valid(entities_json)),
    candidate_meaning TEXT NULL,
    extracted_by_json TEXT NOT NULL CHECK (json_valid(extracted_by_json)),
    confidence REAL NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    status TEXT NOT NULL
);

CREATE TABLE memories (
    id TEXT PRIMARY KEY,
    memory_type TEXT NOT NULL,
    status TEXT NOT NULL,
    claim TEXT NOT NULL,
    source_episodes_json TEXT NOT NULL CHECK (json_valid(source_episodes_json)),
    source_events_json TEXT NOT NULL CHECK (json_valid(source_events_json)),
    domains_json TEXT NOT NULL CHECK (json_valid(domains_json)),
    salience_json TEXT NOT NULL CHECK (json_valid(salience_json)),
    confidence REAL NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    authority TEXT NOT NULL,
    applies_when_json TEXT NOT NULL CHECK (json_valid(applies_when_json)),
    does_not_apply_when_json TEXT NOT NULL CHECK (json_valid(does_not_apply_when_json)),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE principles (
    id TEXT PRIMARY KEY,
    statement TEXT NOT NULL,
    status TEXT NOT NULL,
    supporting_memories_json TEXT NOT NULL CHECK (json_valid(supporting_memories_json)),
    contradicting_memories_json TEXT NOT NULL CHECK (json_valid(contradicting_memories_json)),
    domains_observed_json TEXT NOT NULL CHECK (json_valid(domains_observed_json)),
    applies_when_json TEXT NOT NULL CHECK (json_valid(applies_when_json)),
    does_not_apply_when_json TEXT NOT NULL CHECK (json_valid(does_not_apply_when_json)),
    confidence REAL NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    validation REAL NOT NULL CHECK (validation >= 0 AND validation <= 1),
    brightness REAL NOT NULL CHECK (brightness >= 0 AND brightness <= 1),
    created_by_json TEXT NOT NULL CHECK (json_valid(created_by_json)),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE doctrine (
    id TEXT PRIMARY KEY,
    source_principle TEXT NOT NULL REFERENCES principles(id),
    rule TEXT NOT NULL,
    force TEXT NOT NULL,
    promotion_reason TEXT NOT NULL,
    promoted_by_json TEXT NOT NULL CHECK (json_valid(promoted_by_json)),
    created_at TEXT NOT NULL
);

CREATE TABLE contradictions (
    id TEXT PRIMARY KEY,
    left_ref TEXT NOT NULL,
    right_ref TEXT NOT NULL,
    contradiction_type TEXT NOT NULL,
    status TEXT NOT NULL,
    interpretation TEXT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE context_packs (
    id TEXT PRIMARY KEY,
    task TEXT NOT NULL,
    pack_json TEXT NOT NULL CHECK (json_valid(pack_json)),
    selection_audit TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE audit_records (
    id TEXT PRIMARY KEY,
    operation TEXT NOT NULL,
    target_ref TEXT NOT NULL,
    before_hash TEXT NULL,
    after_hash TEXT NOT NULL,
    reason TEXT NOT NULL,
    actor_json TEXT NOT NULL CHECK (json_valid(actor_json)),
    source_refs_json TEXT NOT NULL CHECK (json_valid(source_refs_json)),
    created_at TEXT NOT NULL
);
