-- Authors, subjects, and owners use the same identity dictionary as soft records.
CREATE TABLE IF NOT EXISTS list_records (
    author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE,
    source TEXT NOT NULL CHECK(source IN ('app.bsky.graph.list', 'app.bsky.graph.listitem', 'app.bsky.graph.listblock')),
    record_key TEXT NOT NULL,
    list_uri TEXT NOT NULL,
    subject INTEGER REFERENCES identities(id),
    purpose TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    PRIMARY KEY(author, source, record_key)
) WITHOUT ROWID;
CREATE INDEX IF NOT EXISTS list_records_uri ON list_records(list_uri, source, subject, author);
CREATE INDEX IF NOT EXISTS list_records_subject ON list_records(subject, source, author);

CREATE TABLE IF NOT EXISTS list_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 TABLE IF NOT EXISTS list_snapshots (
    list_uri TEXT PRIMARY KEY,
    owner INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE,
    bootstrap_us INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS list_snapshots_owner ON list_snapshots(owner);
CREATE TABLE IF NOT EXISTS list_bootstraps (
    author INTEGER PRIMARY KEY REFERENCES soft_accounts(did) ON DELETE CASCADE,
    bootstrap_us INTEGER NOT NULL
);

CREATE VIEW IF NOT EXISTS list_edges AS
SELECT author.did AS author, subject.did AS subject, b.source, b.record_key, b.created_at,
       b.list_uri, owner_did.did AS list_author, l.record_key AS list_key, i.record_key AS item_key
FROM list_records b
JOIN list_records l ON l.list_uri = b.list_uri AND l.source = 'app.bsky.graph.list' AND l.purpose = 'app.bsky.graph.defs#modlist'
JOIN list_records i ON i.list_uri = l.list_uri AND i.author = l.author AND i.source = 'app.bsky.graph.listitem'
JOIN soft_accounts subscriber ON subscriber.did = b.author AND subscriber.active = 1
JOIN soft_accounts owner ON owner.did = l.author AND owner.active = 1
JOIN identities author ON author.id = b.author
JOIN identities subject ON subject.id = i.subject
JOIN identities owner_did ON owner_did.id = l.author
WHERE b.source = 'app.bsky.graph.listblock'
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 = i.subject AND p.active = 0)
AND NOT EXISTS (SELECT 1 FROM list_records x WHERE x.author = b.author AND x.source = b.source AND x.list_uri = b.list_uri AND x.record_key < b.record_key)
AND NOT EXISTS (SELECT 1 FROM list_records x WHERE x.author = i.author AND x.source = i.source AND x.list_uri = i.list_uri AND x.subject = i.subject AND x.record_key < i.record_key);

-- A selected list edge depends on its subscription, list definition, and membership.
CREATE TRIGGER IF NOT EXISTS trust_list_delete BEFORE DELETE ON list_records 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_list_update BEFORE UPDATE ON list_records 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_list_insert AFTER INSERT ON list_records BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_list_delete AFTER DELETE ON list_records BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;
CREATE TRIGGER IF NOT EXISTS graph_list_update AFTER UPDATE ON list_records BEGIN
    UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
END;