BEGIN IMMEDIATE;
CREATE TABLE orientation_candidates (
    session_id TEXT NOT NULL REFERENCES sessions(id),
    candidate_id TEXT NOT NULL,
    scope TEXT NOT NULL,
    semantic TEXT NOT NULL CHECK(json_valid(semantic)),
    candidate TEXT NOT NULL CHECK(json_valid(candidate)),
    PRIMARY KEY(session_id, candidate_id)
) STRICT;
CREATE TABLE orientation_revisions (
    session_id TEXT NOT NULL REFERENCES sessions(id),
    revision_id TEXT NOT NULL,
    scope TEXT NOT NULL,
    candidate_id TEXT NOT NULL,
    operation_id TEXT NOT NULL,
    request TEXT NOT NULL CHECK(json_valid(request)),
    revision TEXT NOT NULL CHECK(json_valid(revision)),
    event_id INTEGER NOT NULL UNIQUE REFERENCES events(seq),
    PRIMARY KEY(session_id, revision_id),
    UNIQUE(session_id, operation_id),
    UNIQUE(session_id, scope, revision_id),
    FOREIGN KEY(session_id, candidate_id) REFERENCES orientation_candidates(session_id, candidate_id)
) STRICT;
CREATE TABLE orientation_selections (
    session_id TEXT NOT NULL,
    scope TEXT NOT NULL,
    revision_id TEXT NOT NULL,
    PRIMARY KEY(session_id, scope),
    FOREIGN KEY(session_id, scope, revision_id) REFERENCES orientation_revisions(session_id, scope, revision_id)
) STRICT;
CREATE TRIGGER orientation_candidates_no_update BEFORE UPDATE ON orientation_candidates
BEGIN SELECT RAISE(ABORT, 'orientation candidates are immutable'); END;
CREATE TRIGGER orientation_candidates_no_delete BEFORE DELETE ON orientation_candidates
BEGIN SELECT RAISE(ABORT, 'orientation candidates are immutable'); END;
CREATE TRIGGER orientation_revisions_no_update BEFORE UPDATE ON orientation_revisions
BEGIN SELECT RAISE(ABORT, 'orientation revisions are immutable'); END;
CREATE TRIGGER orientation_revisions_no_delete BEFORE DELETE ON orientation_revisions
BEGIN SELECT RAISE(ABORT, 'orientation revisions are immutable'); END;
PRAGMA user_version = 11;
COMMIT;