-- Keep full DIDs at the boundary. High-volume graph tables store dictionary keys.
CREATE TABLE IF NOT EXISTS identities (
    id INTEGER PRIMARY KEY,
    did TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS participants (
    did TEXT PRIMARY KEY,
    threshold REAL NOT NULL DEFAULT 1 CHECK(threshold BETWEEN -2 AND 2),
    synced_at TEXT NOT NULL DEFAULT '',
    bootstrap_us INTEGER NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    account_us INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS vouches (
    author TEXT NOT NULL REFERENCES participants(did) ON DELETE CASCADE,
    subject TEXT NOT NULL,
    tag TEXT NOT NULL CHECK(length(tag) <= 128),
    score INTEGER NOT NULL CHECK(score BETWEEN -3 AND 2),
    created_at TEXT NOT NULL,
    record_key TEXT NOT NULL,
    comment TEXT NOT NULL DEFAULT '',
    source TEXT NOT NULL DEFAULT 'computer.ngp.provenance.vouch',
    PRIMARY KEY(author, subject, tag)
);

CREATE INDEX IF NOT EXISTS vouches_tag ON vouches(tag);

CREATE UNIQUE INDEX IF NOT EXISTS vouches_key ON vouches(author, record_key);
CREATE INDEX IF NOT EXISTS vouches_subject_tag ON vouches(subject, tag, author);

CREATE TABLE IF NOT EXISTS graph_revision (
    singleton INTEGER PRIMARY KEY CHECK(singleton = 1),
    revision INTEGER NOT NULL DEFAULT 0
);
INSERT OR IGNORE INTO graph_revision(singleton) VALUES (1);

-- Replay defers path invalidation until the transaction contains the complete batch.
CREATE TABLE IF NOT EXISTS cache_policy (
    singleton INTEGER PRIMARY KEY CHECK(singleton = 1),
    deferred INTEGER NOT NULL DEFAULT 0 CHECK(deferred IN (0, 1))
);
INSERT OR IGNORE INTO cache_policy(singleton) VALUES (1);

CREATE TABLE IF NOT EXISTS trust_cache (
    cache_key TEXT PRIMARY KEY,
    status TEXT NOT NULL DEFAULT '',
    score REAL,
    expires_us INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS trust_cache_expiry ON trust_cache(expires_us);

CREATE TABLE IF NOT EXISTS trust_cache_paths (
    cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE,
    position INTEGER NOT NULL CHECK(position >= 0),
    did TEXT NOT NULL,
    PRIMARY KEY(cache_key, position)
) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS trust_cache_scores (
    cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE,
    position INTEGER NOT NULL CHECK(position >= 0),
    did TEXT NOT NULL,
    assertion REAL,
    source TEXT NOT NULL,
    list_uri TEXT NOT NULL,
    score REAL NOT NULL,
    comment TEXT NOT NULL,
    PRIMARY KEY(cache_key, position)
) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS trust_dependencies (
    cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE,
    author INTEGER NOT NULL REFERENCES identities(id),
    record_key TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT 'computer.ngp.provenance.vouch',
    PRIMARY KEY(cache_key, author, source, record_key)
);
CREATE INDEX IF NOT EXISTS trust_dependencies_vouch ON trust_dependencies(author, source, record_key, cache_key);

-- Record mutations invalidate only the paths that use that exact record.
CREATE TRIGGER IF NOT EXISTS trust_vouch_delete BEFORE DELETE ON vouches WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN
    DELETE FROM trust_cache WHERE cache_key IN (
        SELECT cache_key FROM trust_dependencies WHERE author = (SELECT id FROM identities WHERE did = OLD.author) AND source = OLD.source AND record_key = OLD.record_key
    );
END;
CREATE TRIGGER IF NOT EXISTS trust_vouch_update BEFORE UPDATE ON vouches WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN
    DELETE FROM trust_cache WHERE cache_key IN (
        SELECT cache_key FROM trust_dependencies WHERE author = (SELECT id FROM identities WHERE did = OLD.author) AND source = OLD.source AND record_key = OLD.record_key
    );
END;

-- Revisions prevent calculations from publishing cache entries after concurrent graph mutations.
CREATE TRIGGER IF NOT EXISTS graph_vouch_insert AFTER INSERT ON vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_vouch_delete AFTER DELETE ON vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_vouch_update AFTER UPDATE ON vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_account_update AFTER UPDATE OF active ON participants WHEN OLD.active != NEW.active BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;

CREATE TABLE IF NOT EXISTS record_versions (
    author TEXT NOT NULL REFERENCES participants(did) ON DELETE CASCADE,
    record_key TEXT NOT NULL,
    time_us INTEGER NOT NULL,
    PRIMARY KEY(author, record_key)
);

CREATE TABLE IF NOT EXISTS stream_cursors (
    source TEXT PRIMARY KEY,
    time_us INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS secrets (
    kind TEXT NOT NULL,
    id TEXT NOT NULL,
    value BLOB NOT NULL,
    expires INTEGER NOT NULL,
    PRIMARY KEY(kind, id)
);