-- Identity columns contain dictionary keys. Views restore full DIDs at the storage boundary.
CREATE TABLE IF NOT EXISTS soft_accounts (
    did INTEGER PRIMARY KEY REFERENCES identities(id),
    active INTEGER NOT NULL DEFAULT 1,
    account_us INTEGER NOT NULL DEFAULT 0,
    bootstrap_us INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS soft_vouches (
    author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE,
    subject INTEGER NOT NULL REFERENCES identities(id),
    source TEXT NOT NULL CHECK(source IN ('app.bsky.graph.follow', 'app.bsky.graph.block')),
    record_key TEXT NOT NULL,
    score REAL NOT NULL CHECK(
        (source = 'app.bsky.graph.follow' AND score = 0.5) OR
        (source = 'app.bsky.graph.block' AND score = -0.5)
    ),
    created_at TEXT NOT NULL,
    PRIMARY KEY(author, source, record_key)
) WITHOUT ROWID;

-- One block overrides follows. Duplicate records never multiply a pair's score.
CREATE VIEW IF NOT EXISTS soft_edges AS
SELECT author.did AS author, subject.did AS subject, s.source, s.record_key, s.score, s.created_at
FROM soft_vouches s JOIN soft_accounts a ON a.did = s.author
JOIN identities author ON author.id = s.author
JOIN identities subject ON subject.id = s.subject
WHERE a.active = 1
AND NOT EXISTS (SELECT 1 FROM participants p WHERE p.did = subject.did AND p.active = 0)
AND NOT EXISTS (SELECT 1 FROM soft_accounts p WHERE p.did = s.subject AND p.active = 0)
AND NOT EXISTS (
    SELECT 1 FROM soft_vouches preferred
    WHERE preferred.author = s.author AND preferred.subject = s.subject
    AND ((preferred.source = 'app.bsky.graph.block' AND s.source = 'app.bsky.graph.follow')
        OR (preferred.source = s.source AND preferred.record_key < s.record_key))
);

CREATE TABLE IF NOT EXISTS soft_versions (
    author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE,
    source TEXT NOT NULL,
    record_key TEXT NOT NULL,
    time_us INTEGER NOT NULL,
    PRIMARY KEY(author, source, record_key)
) WITHOUT ROWID;

CREATE TRIGGER IF NOT EXISTS trust_soft_delete BEFORE DELETE ON soft_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 = OLD.author AND source = OLD.source AND record_key = OLD.record_key
    );
END;
CREATE TRIGGER IF NOT EXISTS trust_soft_update BEFORE UPDATE ON soft_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 = OLD.author AND source = OLD.source AND record_key = OLD.record_key
    );
END;
CREATE TRIGGER IF NOT EXISTS graph_soft_insert AFTER INSERT ON soft_vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_soft_delete AFTER DELETE ON soft_vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_soft_update AFTER UPDATE ON soft_vouches BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_soft_account AFTER UPDATE OF active ON soft_accounts WHEN OLD.active != NEW.active BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;