-- name: Join :exec
INSERT INTO participants(did) VALUES (?) ON CONFLICT(did) DO NOTHING;

-- name: ReplaySequence :one
SELECT i.id, CAST(COALESCE(r.sequence, 0) AS INTEGER) AS sequence FROM identities i
LEFT JOIN replay_accounts r ON r.author = i.id AND r.source = @source
WHERE i.did = CAST(@author AS TEXT);

-- name: AdvanceReplayAccount :exec
INSERT INTO replay_accounts(source, author, sequence) VALUES (@source, @author, @sequence)
ON CONFLICT(source, author) DO UPDATE SET sequence = excluded.sequence;

-- name: Participant :one
SELECT * FROM participants WHERE did = ?;

-- name: Participants :many
SELECT * FROM participants ORDER BY synced_at, did LIMIT ?;

-- name: SetThreshold :execrows
UPDATE participants SET threshold = ? WHERE did = ?;

-- name: SetSynced :exec
UPDATE participants SET synced_at = ? WHERE did = ?;

-- name: SetBootstrap :exec
UPDATE participants SET bootstrap_us = ?, account_us = ?, active = 1, synced_at = ? WHERE did = ?;

-- name: SetAccount :exec
UPDATE participants SET active = ?, account_us = ?, synced_at = ? WHERE did = ?;

-- name: SetDeleted :exec
UPDATE participants SET active = 0, account_us = ?, bootstrap_us = ?, synced_at = ? WHERE did = ?;

-- name: ClearVouches :exec
DELETE FROM vouches WHERE author = ?;

-- name: InsertVouch :exec
INSERT INTO vouches(author, subject, tag, score, created_at, record_key, comment) VALUES (?, ?, ?, ?, ?, ?, ?);

-- name: AuthorRecords :many
SELECT * FROM vouches WHERE author = ?;

-- name: ReplaceVouch :exec
INSERT INTO vouches(author, subject, tag, score, created_at, record_key, comment) VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(author, subject, tag) DO UPDATE SET score = excluded.score, created_at = excluded.created_at, record_key = excluded.record_key, comment = excluded.comment
WHERE score != excluded.score OR created_at != excluded.created_at OR record_key != excluded.record_key OR comment != excluded.comment;

-- name: Incoming :many
SELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.author
WHERE subject = ?1 AND participants.active = 1
AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = vouches.subject AND target.active = 0)
AND (tag = ?2 OR (CAST(?3 AS INTEGER) = 1 AND tag = ''))
ORDER BY author, tag LIMIT ?4;

-- name: GraphRevision :one
SELECT revision FROM graph_revision WHERE singleton = 1;

-- name: BumpGraphRevision :exec
UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;

-- name: DeferTrustInvalidation :exec
UPDATE cache_policy SET deferred = 1 WHERE singleton = 1;

-- name: ResumeTrustInvalidation :exec
UPDATE cache_policy SET deferred = 0 WHERE singleton = 1;

-- name: ClearTrust :exec
DELETE FROM trust_cache;

-- name: CachedTrust :one
SELECT status, score FROM trust_cache WHERE cache_key = ? AND expires_us > ?;

-- name: TrustCachePath :many
SELECT did FROM trust_cache_paths WHERE cache_key = ? ORDER BY position;

-- name: TrustCacheScores :many
SELECT did, assertion, source, list_uri, score, comment FROM trust_cache_scores WHERE cache_key = ? ORDER BY position;

-- name: DeleteTrust :exec
DELETE FROM trust_cache WHERE cache_key = ?;

-- name: SaveTrust :exec
INSERT INTO trust_cache(cache_key, status, score, expires_us) VALUES (?, ?, ?, ?);

-- name: SaveTrustPath :exec
INSERT INTO trust_cache_paths(cache_key, position, did) VALUES (?, ?, ?);

-- name: SaveTrustScore :exec
INSERT INTO trust_cache_scores(cache_key, position, did, assertion, source, list_uri, score, comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?);

-- name: SaveDependency :exec
INSERT OR IGNORE INTO trust_dependencies(cache_key, author, source, record_key)
VALUES (@cache_key, (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key);

-- name: PutIdentity :exec
INSERT INTO identities(did) VALUES (?) ON CONFLICT(did) DO NOTHING;

-- name: ReplayIndexState :one
SELECT source, deferred FROM replay_indexes WHERE singleton = 1;

-- name: DeferReplayIndexes :exec
UPDATE replay_indexes SET source = ?, deferred = 1 WHERE singleton = 1;

-- name: FinishReplayIndexes :exec
UPDATE replay_indexes SET deferred = 0 WHERE singleton = 1;

-- name: SoftGraph :many
-- The caller sorts complete results. Avoid a global DID sort before the edge limit.
SELECT s.* FROM soft_edges s
WHERE (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
LIMIT @max_rows;

-- name: SoftIncoming :many
SELECT s.* FROM soft_edges s
WHERE s.subject = @subject
AND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
ORDER BY s.author LIMIT @max_rows;

-- name: SoftAccount :one
SELECT i.did, a.active, a.account_us, a.bootstrap_us FROM soft_accounts a JOIN identities i ON i.id = a.did WHERE i.did = ?;

-- name: SoftReplayState :one
SELECT a.bootstrap_us, v.time_us FROM (SELECT CAST(@author AS INTEGER) AS id) i
LEFT JOIN soft_accounts a ON a.did = i.id
LEFT JOIN soft_versions v ON v.author = i.id AND v.source = @source AND v.record_key = @record_key;

-- name: JoinReplaySoft :exec
INSERT INTO soft_accounts(did) VALUES (?) ON CONFLICT(did) DO NOTHING;

-- name: SetReplaySoftVersion :exec
INSERT INTO soft_versions(author, source, record_key, time_us) VALUES (@author, @source, @record_key, @time_us)
ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;

-- name: ReplaceReplaySoft :exec
INSERT INTO soft_vouches(author, subject, source, record_key, score, created_at)
VALUES (@author, (SELECT id FROM identities WHERE did = CAST(@subject AS TEXT)), @source, @record_key, @score, @created_at)
ON CONFLICT(author, source, record_key) DO UPDATE SET subject = excluded.subject, score = excluded.score, created_at = excluded.created_at
WHERE subject != excluded.subject OR score != excluded.score OR created_at != excluded.created_at;

-- name: DeleteReplaySoft :exec
DELETE FROM soft_vouches WHERE author = @author AND source = @source AND record_key = @record_key;

-- name: JoinSoft :exec
INSERT INTO soft_accounts(did) SELECT id FROM identities WHERE did = CAST(@did AS TEXT) ON CONFLICT(did) DO NOTHING;

-- name: SoftAccountState :exec
UPDATE soft_accounts SET active = @active, account_us = @account_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));

-- name: SoftBootstrap :exec
UPDATE soft_accounts SET active = 1, account_us = @account_us, bootstrap_us = @bootstrap_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));

-- name: SoftDeleted :exec
UPDATE soft_accounts SET active = 0, account_us = @account_us, bootstrap_us = @bootstrap_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));

-- name: SoftRecords :many
SELECT * FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: SoftVersion :one
SELECT time_us FROM soft_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;

-- name: SetSoftVersion :exec
INSERT INTO soft_versions(author, source, record_key, time_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @time_us)
ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;

-- name: ClearSoftVersions :exec
DELETE FROM soft_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: DeleteSoft :exec
DELETE FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;

-- name: ClearSoft :exec
DELETE FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: ReplaceSoft :exec
INSERT INTO soft_vouches(author, subject, source, record_key, score, created_at)
VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), (SELECT id FROM identities WHERE did = CAST(@subject AS TEXT)), @source, @record_key, @score, @created_at)
ON CONFLICT(author, source, record_key) DO UPDATE SET subject = excluded.subject, score = excluded.score, created_at = excluded.created_at
WHERE subject != excluded.subject OR score != excluded.score OR created_at != excluded.created_at;

-- name: ListGraph :many
SELECT e.* FROM list_edges e
WHERE (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
LIMIT @max_rows;

-- name: ListIncoming :many
SELECT e.* FROM list_edges e
WHERE e.subject = @subject
AND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
ORDER BY e.author, e.list_uri LIMIT @max_rows;

-- name: ListRecord :one
SELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_at
FROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subject
WHERE a.did = CAST(@author AS TEXT) AND r.source = @source AND r.record_key = @record_key;

-- name: ListAuthorRecords :many
SELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_at
FROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subject
WHERE a.did = CAST(@author AS TEXT) AND r.source = @source;

-- name: ListURIRecords :many
SELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_at
FROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subject
WHERE r.list_uri = ? AND r.source != 'app.bsky.graph.listblock';

-- name: PutListRecord :exec
INSERT INTO list_records(author, source, record_key, list_uri, subject, purpose, created_at)
VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @list_uri, (SELECT id FROM identities WHERE did = NULLIF(CAST(@subject AS TEXT), '')), @purpose, @created_at)
ON CONFLICT(author, source, record_key) DO UPDATE SET list_uri = excluded.list_uri, subject = excluded.subject, purpose = excluded.purpose, created_at = excluded.created_at
WHERE list_uri != excluded.list_uri OR subject IS NOT excluded.subject OR purpose != excluded.purpose OR created_at != excluded.created_at;

-- name: DeleteListRecord :exec
DELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;

-- name: ClearListAuthor :exec
DELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: ReplayClearLists :exec
DELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT))
AND NOT EXISTS (SELECT 1 FROM list_snapshots s WHERE s.list_uri = list_records.list_uri AND s.bootstrap_us >= @time_us)
AND (source != 'app.bsky.graph.listblock' OR NOT EXISTS (SELECT 1 FROM list_bootstraps b WHERE b.author = list_records.author AND b.bootstrap_us >= @time_us));

-- name: ClearListVersions :exec
DELETE FROM list_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: ListVersion :one
SELECT time_us FROM list_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;

-- name: PutListVersion :exec
INSERT INTO list_versions(author, source, record_key, time_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @time_us)
ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;

-- name: ListWatermark :one
SELECT bootstrap_us FROM list_snapshots WHERE list_uri = ?;

-- name: PutListWatermark :exec
INSERT INTO list_snapshots(list_uri, owner, bootstrap_us) VALUES (@list_uri, (SELECT id FROM identities WHERE did = CAST(@owner AS TEXT)), @bootstrap_us)
ON CONFLICT(list_uri) DO UPDATE SET bootstrap_us = excluded.bootstrap_us;

-- name: ListBootstrap :one
SELECT bootstrap_us FROM list_bootstraps WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));

-- name: PutListBootstrap :exec
INSERT INTO list_bootstraps(author, bootstrap_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @bootstrap_us)
ON CONFLICT(author) DO UPDATE SET bootstrap_us = excluded.bootstrap_us;

-- name: WantsListOwner :one
SELECT EXISTS(SELECT 1 FROM list_snapshots WHERE owner = (SELECT id FROM identities WHERE did = CAST(@owner AS TEXT))) AS wanted;

-- name: WantsListURI :one
SELECT EXISTS(SELECT 1 FROM list_snapshots WHERE list_uri = ?) AS wanted;

-- name: Graph :many
SELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.author
WHERE participants.active = 1
AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = vouches.subject AND target.active = 0)
AND (tag = sqlc.arg(tag) OR (CAST(sqlc.arg(fallback) AS INTEGER) = 1 AND tag = ''))
ORDER BY author, subject, tag LIMIT sqlc.arg(max_rows);

-- Outgoing reads preserve per-pair selection and use author indexes rather than a global graph scan.
-- name: Outgoing :many
SELECT v.* FROM vouches v JOIN participants p ON p.did = v.author
WHERE v.author = @author AND p.active = 1
AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = v.subject AND target.active = 0)
AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = ''))
ORDER BY v.subject, v.tag LIMIT @max_rows;

-- name: SoftOutgoing :many
SELECT s.* FROM soft_edges s
WHERE s.author = @author
AND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
LIMIT @max_rows;

-- name: ListOutgoing :many
SELECT e.* FROM list_edges e
WHERE e.author = @author
AND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))
AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))
LIMIT @max_rows;

-- name: OwnVouches :many
SELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.author
WHERE participants.active = 1 AND author = ? ORDER BY subject, tag LIMIT ? OFFSET ?;

-- name: Tags :many
SELECT DISTINCT tag FROM vouches JOIN participants ON participants.did = vouches.author
WHERE participants.active = 1 AND tag != '' AND substr(tag, 1, length(CAST(sqlc.arg(prefix) AS TEXT))) = CAST(sqlc.arg(prefix) AS TEXT)
ORDER BY tag LIMIT sqlc.arg(max_rows);

-- name: DeleteRecord :exec
DELETE FROM vouches WHERE author = ? AND record_key = ?;

-- name: RecordVersion :one
SELECT time_us FROM record_versions WHERE author = ? AND record_key = ?;

-- name: SetRecordVersion :exec
INSERT INTO record_versions(author, record_key, time_us) VALUES (?, ?, ?)
ON CONFLICT(author, record_key) DO UPDATE SET time_us = excluded.time_us;

-- name: ClearVersions :exec
DELETE FROM record_versions WHERE author = ?;

-- name: AdvanceCursor :exec
INSERT INTO stream_cursors(source, time_us) VALUES (?, ?)
ON CONFLICT(source) DO UPDATE SET time_us = max(stream_cursors.time_us, excluded.time_us);

-- name: InitCursor :exec
INSERT INTO stream_cursors(source, time_us) VALUES (?, ?) ON CONFLICT(source) DO NOTHING;

-- name: Cursor :one
SELECT time_us FROM stream_cursors WHERE source = ?;

-- name: LegacyKeys :many
SELECT author, subject, tag FROM vouches WHERE record_key = '';

-- name: SetLegacyKey :exec
UPDATE vouches SET record_key = ? WHERE author = ? AND subject = ? AND tag = ?;

-- name: PutSecret :exec
INSERT INTO secrets(kind, id, value, expires) VALUES (?, ?, ?, ?)
ON CONFLICT(kind, id) DO UPDATE SET value = excluded.value, expires = excluded.expires;

-- name: AddSecret :exec
INSERT INTO secrets(kind, id, value, expires) VALUES (?, ?, ?, ?);

-- name: GetSecret :one
SELECT value FROM secrets WHERE kind = ? AND id = ? AND expires > ?;

-- name: RemoveSecret :exec
DELETE FROM secrets WHERE kind = ? AND id = ?;

-- name: ConsumeSecret :one
DELETE FROM secrets WHERE kind = ? AND id = ? AND expires > ? RETURNING value;

-- name: PruneSecrets :exec
DELETE FROM secrets WHERE expires <= ?;