import { packageStorage } from 'kody:runtime' import type { ItemKind, Priority, QueueItem, QuietOverride } from './types.ts' type SqlValue = string | number | null type SqlResult = { rows?: Array> } async function sql(query: string, bindings: Array = []): Promise { return (await packageStorage().sql(query, bindings)) as SqlResult } let schemaReady: Promise | null = null /** Create tables once per isolate. */ export function ensureSchema(): Promise { if (!schemaReady) { schemaReady = (async () => { await sql(`CREATE TABLE IF NOT EXISTS quiet_items ( id TEXT PRIMARY KEY, created_at TEXT NOT NULL, bot TEXT NOT NULL, kind TEXT NOT NULL, priority TEXT NOT NULL, title TEXT NOT NULL, link TEXT, detail TEXT, deadline TEXT, delivered_at TEXT, digest_id TEXT, waiting_on_me TEXT )`) await sql( 'CREATE INDEX IF NOT EXISTS quiet_items_undelivered ON quiet_items (delivered_at, created_at)', ) await sql(`CREATE TABLE IF NOT EXISTS quiet_state ( key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at TEXT NOT NULL )`) })().catch((error) => { schemaReady = null throw error }) } return schemaReady } /** Test seam: forget the per-isolate schema cache. */ export function resetSchemaCacheForTests() { schemaReady = null } function str(value: SqlValue | undefined): string | null { return typeof value === 'string' && value ? value : null } function rowToItem(row: Record): QueueItem { let waitingOnMe: QueueItem['waitingOnMe'] = null if (typeof row.waiting_on_me === 'string' && row.waiting_on_me) { try { waitingOnMe = JSON.parse(row.waiting_on_me) } catch { waitingOnMe = null } } return { id: String(row.id), createdAt: String(row.created_at), bot: String(row.bot), kind: String(row.kind) as ItemKind, priority: String(row.priority) as Priority, title: String(row.title), link: str(row.link), detail: str(row.detail), deadline: str(row.deadline), deliveredAt: str(row.delivered_at), digestId: str(row.digest_id), waitingOnMe, } } export async function insertItem(item: QueueItem): Promise { await ensureSchema() await sql( `INSERT INTO quiet_items (id, created_at, bot, kind, priority, title, link, detail, deadline, delivered_at, digest_id, waiting_on_me) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NULL, NULL)`, [ item.id, item.createdAt, item.bot, item.kind, item.priority, item.title, item.link, item.detail, item.deadline, ], ) } export async function setWaitingOnMe(id: string, value: QueueItem['waitingOnMe']) { await ensureSchema() await sql('UPDATE quiet_items SET waiting_on_me = ? WHERE id = ?', [ value ? JSON.stringify(value) : null, id, ]) } /** Items not yet delivered in a digest, oldest first. */ export async function listUndelivered(limit = 500): Promise { await ensureSchema() const result = await sql( 'SELECT * FROM quiet_items WHERE delivered_at IS NULL ORDER BY created_at ASC, id ASC LIMIT ?', [limit], ) return (result.rows ?? []).map(rowToItem) } export async function markDelivered(ids: string[], digestId: string, at: string) { await ensureSchema() for (const id of ids) { await sql( 'UPDATE quiet_items SET delivered_at = ?, digest_id = ? WHERE id = ? AND delivered_at IS NULL', [at, digestId, id], ) } } export async function getState(key: string): Promise { await ensureSchema() const result = await sql('SELECT value FROM quiet_state WHERE key = ?', [key]) const raw = result.rows?.[0]?.value if (typeof raw !== 'string') return null try { return JSON.parse(raw) as T } catch { return null } } export async function setState(key: string, value: unknown) { await ensureSchema() await sql( `INSERT INTO quiet_state (key, value, updated_at) VALUES (?, ?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`, [key, JSON.stringify(value), new Date().toISOString()], ) } export async function deleteState(key: string) { await ensureSchema() await sql('DELETE FROM quiet_state WHERE key = ?', [key]) } export const OVERRIDE_KEY = 'override' export const LAST_DIGEST_KEY = 'last-digest' export const SETTINGS_KEY = 'settings' export type LastDigest = { id: string; at: string; via: string; count: number } export type Settings = { fallbackChannelId?: string } export async function readOverride(): Promise { return getState(OVERRIDE_KEY) }