[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_106":107,"_108":109},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","StonedSheamus","name","dnd-twitch-bot","path","db.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":-5,"_58":42,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/StonedSheamus/dnd-twitch-bot/code/db.ts","extension","id","d42f4d0a-eba6-4f14-983c-583b7f2ed11e","fileId","82592d08-a4d9-11f1-854e-1607ee4eb77e","parentId","branchId","05246ac8-a272-4b74-b48a-34d91555f75d","branchVersion",508,"latest",true,"createdAt",["D",1789335872315],"version",39,"fileType","script","content","// SQLite persistence layer\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport type { Character } from \"./types.ts\";\nimport { COMMAND_GROUPS } from \"./utils.ts\";\n\n// Channel-wide default cooldowns for custom commands/triggers (mods can\n// override per-command/per-trigger with !dndbot cooldown / !trigger cooldown).\nexport const DEFAULT_CUSTOM_COMMAND_COOLDOWN_MS = 5_000;\nexport const DEFAULT_CUSTOM_TRIGGER_COOLDOWN_MS = 15_000;\n\n// Default posting interval for a new timed message when none is given\n// (mods can set their own with !timedmsg add/interval — see timedmessages.ts).\nexport const DEFAULT_TIMED_MESSAGE_INTERVAL_MINUTES = 30;\n\nasync function tableColumns(table: string): Promise {\n const res = await sqlite.execute(`PRAGMA table_info(${table})`);\n return res.rows.map((r: any) => String(r.name));\n}\n\nasync function migrateCharacterTables() {\n const chars = await tableColumns(\"characters\");\n if (chars.length && !chars.includes(\"broadcaster_id\")) {\n await sqlite.execute(\"ALTER TABLE characters RENAME TO characters_legacy\");\n }\n const backups = await tableColumns(\"character_backups\");\n if (backups.length && !backups.includes(\"broadcaster_id\")) {\n await sqlite.execute(\"ALTER TABLE character_backups RENAME TO character_backups_legacy\");\n }\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS characters (\n broadcaster_id TEXT, username TEXT, race TEXT, subrace TEXT, class TEXT, level INTEGER,\n xp INTEGER DEFAULT 0, str INTEGER, dex INTEGER, con INTEGER, int INTEGER, wis INTEGER, cha INTEGER,\n speed INTEGER, hp_max INTEGER, hp_current INTEGER, proficiency INTEGER, traits TEXT,\n spells TEXT DEFAULT '[]', items TEXT DEFAULT '[]', feats TEXT DEFAULT '[]', abilities TEXT DEFAULT '[]',\n PRIMARY KEY (broadcaster_id, username)\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS character_backups (\n broadcaster_id TEXT, username TEXT, race TEXT, subrace TEXT, class TEXT, level INTEGER,\n xp INTEGER DEFAULT 0, str INTEGER, dex INTEGER, con INTEGER, int INTEGER, wis INTEGER, cha INTEGER,\n speed INTEGER, hp_max INTEGER, hp_current INTEGER, proficiency INTEGER, traits TEXT,\n spells TEXT DEFAULT '[]', items TEXT DEFAULT '[]', feats TEXT DEFAULT '[]', abilities TEXT DEFAULT '[]',\n PRIMARY KEY (broadcaster_id, username)\n )`);\n const legacyChars = await tableColumns(\"characters_legacy\");\n if (legacyChars.length) {\n for (const column of [\"spells\", \"items\", \"feats\", \"abilities\", \"xp\"]) {\n if (!legacyChars.includes(column)) {\n await sqlite.execute(`ALTER TABLE characters_legacy ADD COLUMN ${column} ${column === \"xp\" ? \"INTEGER DEFAULT 0\" : \"TEXT DEFAULT '[]'\"}`);\n }\n }\n await sqlite.execute(`INSERT OR IGNORE INTO characters\n (broadcaster_id,username,race,subrace,class,level,xp,str,dex,con,int,wis,cha,speed,hp_max,hp_current,proficiency,traits,spells,items,feats,abilities)\n SELECT cc.broadcaster_id,c.username,c.race,c.subrace,c.class,c.level,COALESCE(c.xp,0),c.str,c.dex,c.con,c.int,c.wis,c.cha,c.speed,c.hp_max,c.hp_current,c.proficiency,c.traits,COALESCE(c.spells,'[]'),COALESCE(c.items,'[]'),COALESCE(c.feats,'[]'),COALESCE(c.abilities,'[]')\n FROM characters_legacy c JOIN channel_characters cc ON cc.username=c.username`);\n await sqlite.execute(\"DROP TABLE characters_legacy\");\n }\n const legacyBackups = await tableColumns(\"character_backups_legacy\");\n if (legacyBackups.length) {\n for (const column of [\"spells\", \"items\", \"feats\", \"abilities\", \"xp\"]) {\n if (!legacyBackups.includes(column)) {\n await sqlite.execute(`ALTER TABLE character_backups_legacy ADD COLUMN ${column} ${column === \"xp\" ? \"INTEGER DEFAULT 0\" : \"TEXT DEFAULT '[]'\"}`);\n }\n }\n await sqlite.execute(`INSERT OR IGNORE INTO character_backups\n (broadcaster_id,username,race,subrace,class,level,xp,str,dex,con,int,wis,cha,speed,hp_max,hp_current,proficiency,traits,spells,items,feats,abilities)\n SELECT cc.broadcaster_id,c.username,c.race,c.subrace,c.class,c.level,COALESCE(c.xp,0),c.str,c.dex,c.con,c.int,c.wis,c.cha,c.speed,c.hp_max,c.hp_current,c.proficiency,c.traits,COALESCE(c.spells,'[]'),COALESCE(c.items,'[]'),COALESCE(c.feats,'[]'),COALESCE(c.abilities,'[]')\n FROM character_backups_legacy c JOIN channel_characters cc ON cc.username=c.username`);\n await sqlite.execute(\"DROP TABLE character_backups_legacy\");\n }\n await sqlite.execute(\"CREATE INDEX IF NOT EXISTS idx_characters_channel ON characters(broadcaster_id)\");\n await sqlite.execute(\"CREATE INDEX IF NOT EXISTS idx_backups_channel ON character_backups(broadcaster_id)\");\n}\n\nasync function migrateCreationSessions() {\n const cols = await tableColumns(\"creation_sessions\");\n if (cols.length && !cols.includes(\"broadcaster_id\")) {\n await sqlite.execute(\"ALTER TABLE creation_sessions RENAME TO creation_sessions_legacy\");\n await sqlite.execute(`CREATE TABLE creation_sessions (\n broadcaster_id TEXT, username TEXT, step TEXT, race TEXT, subrace TEXT, class TEXT, scores TEXT, updated_at INTEGER,\n PRIMARY KEY (broadcaster_id, username)\n )`);\n await sqlite.execute(`INSERT OR IGNORE INTO creation_sessions (broadcaster_id,username,step,race,subrace,class,scores,updated_at)\n SELECT cc.broadcaster_id,s.username,s.step,s.race,s.subrace,s.class,s.scores,s.updated_at\n FROM creation_sessions_legacy s JOIN channel_characters cc ON cc.username=s.username`);\n await sqlite.execute(\"DROP TABLE creation_sessions_legacy\");\n }\n}\n\nasync function migrateMapTables() {\n // Sparse cell storage: a map only stores *overridden* cells; anything not\n // present falls back to the map's own default_terrain. Keeps `!map fill`\n // and large grids cheap, in line with SQLite's limited ALTER TABLE support.\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS maps (\n broadcaster_id TEXT NOT NULL,\n map_name TEXT NOT NULL,\n width INTEGER NOT NULL,\n height INTEGER NOT NULL,\n default_terrain TEXT NOT NULL DEFAULT 'grass',\n created_by TEXT,\n created_at INTEGER,\n updated_at INTEGER,\n PRIMARY KEY (broadcaster_id, map_name)\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS map_cells (\n broadcaster_id TEXT NOT NULL,\n map_name TEXT NOT NULL,\n x INTEGER NOT NULL,\n y INTEGER NOT NULL,\n terrain TEXT NOT NULL,\n PRIMARY KEY (broadcaster_id, map_name, x, y)\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS map_tokens (\n broadcaster_id TEXT NOT NULL,\n map_name TEXT NOT NULL,\n username TEXT NOT NULL,\n display_name TEXT,\n x INTEGER NOT NULL,\n y INTEGER NOT NULL,\n placed_at INTEGER,\n updated_at INTEGER,\n PRIMARY KEY (broadcaster_id, map_name, username)\n )`);\n}\n\nexport async function ensureTables() {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS channel_characters (broadcaster_id TEXT, username TEXT, created_at INTEGER, updated_at INTEGER, PRIMARY KEY (broadcaster_id, username))`);\n await migrateCharacterTables();\n await migrateMapTables();\n for (const table of [\"characters\", \"character_backups\"]) {\n for (const column of [\"spells\", \"items\", \"feats\", \"abilities\"]) {\n try {\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN ${column} TEXT DEFAULT '[]'`);\n } catch (_) {\n /* column already exists */\n }\n }\n try {\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN xp INTEGER DEFAULT 0`);\n } catch (_) {\n /* column already exists */\n }\n }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS eventsub_extra_subscriptions (\n broadcaster_id TEXT, kind TEXT, subscription_id TEXT, created_at INTEGER,\n PRIMARY KEY (broadcaster_id, kind)\n )`,\n );\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS oauth_states (state TEXT PRIMARY KEY, expires_at INTEGER)`);\n // Separate OAuth state table for the dashboard's viewer-side \"log in with\n // Twitch\" moderator check (see dashboard.ts) — kept apart from the\n // broadcaster-connect oauth_states above since the payload differs\n // (channel_id + dashboard_key, not just an expiry) and the two flows use\n // different redirect URIs/scopes.\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS dashboard_oauth_states (\n state TEXT PRIMARY KEY, channel_id TEXT NOT NULL, dashboard_key TEXT NOT NULL, expires_at INTEGER NOT NULL\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS broadcasters (\n broadcaster_id TEXT PRIMARY KEY, login TEXT, display_name TEXT, subscription_id TEXT, connected_at INTEGER, connected INTEGER NOT NULL DEFAULT 1, disconnected_at INTEGER, disconnect_reason TEXT\n )`,\n );\n try { await sqlite.execute(`ALTER TABLE broadcasters ADD COLUMN connected INTEGER NOT NULL DEFAULT 1`); } catch (_) {}\n try { await sqlite.execute(`ALTER TABLE broadcasters ADD COLUMN disconnected_at INTEGER`); } catch (_) {}\n try { await sqlite.execute(`ALTER TABLE broadcasters ADD COLUMN disconnect_reason TEXT`); } catch (_) {}\n // Per-channel capability token for the web dashboard (see dashboard.ts) —\n // a mod/broadcaster fetches it with !dashboard, then anyone holding the\n // link can manage that one channel's custom commands/triggers/timed\n // messages at GET/POST /dashboard. Hex-only (crypto.randomUUID minus\n // dashes) so it never collides with the \"+\"-decodes-to-space query string\n // trap documented on /admin/logs below.\n try { await sqlite.execute(`ALTER TABLE broadcasters ADD COLUMN dashboard_key TEXT`); } catch (_) {}\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS channel_characters (broadcaster_id TEXT, username TEXT, created_at INTEGER, updated_at INTEGER, PRIMARY KEY (broadcaster_id, username))`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS channel_blocks (broadcaster_id TEXT PRIMARY KEY, reason TEXT, created_at INTEGER, updated_at INTEGER)`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS monitor_events (id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT, detail TEXT, created_at INTEGER)`);\n // Single-row status for the merchant cron tick — read by GET\n // /admin/merchant/status and /admin/logs (see getMerchantCronStatus /\n // recordMerchantCronRun below, written once per tick by merchant_cron.ts).\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS merchant_cron_status (\n id INTEGER PRIMARY KEY CHECK (id = 1),\n last_run_at INTEGER, channels_due INTEGER NOT NULL DEFAULT 0,\n last_posts_ok INTEGER NOT NULL DEFAULT 0, last_posts_failed INTEGER NOT NULL DEFAULT 0\n )`,\n );\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS eventsub_messages (message_id TEXT PRIMARY KEY, received_at INTEGER NOT NULL)`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS command_rate_limits (broadcaster_id TEXT, username TEXT, last_at INTEGER NOT NULL, PRIMARY KEY (broadcaster_id, username))`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS pending_eventsub_cancellations (subscription_id TEXT PRIMARY KEY, broadcaster_id TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, last_error TEXT, updated_at INTEGER NOT NULL)`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS welcomed_users (username TEXT PRIMARY KEY, welcomed_at INTEGER)`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS goodnight_cooldowns (broadcaster_id TEXT PRIMARY KEY, last_at INTEGER NOT NULL)`);\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS creation_sessions (\n broadcaster_id TEXT, username TEXT, step TEXT, race TEXT, subrace TEXT, class TEXT, scores TEXT, updated_at INTEGER,\n PRIMARY KEY (broadcaster_id, username)\n )`,\n );\n await migrateCreationSessions();\n // BG3-flavored creation (!bg3) stores extra flavor text alongside the\n // standard wizard's race/subrace/class/scores columns.\n for (const column of [\"subclass\", \"background\", \"alignment\", \"hook\"]) {\n try {\n await sqlite.execute(`ALTER TABLE creation_sessions ADD COLUMN ${column} TEXT`);\n } catch (_) {\n /* column already exists */\n }\n }\n // target_user: who a pending confirm_createchar overwrite-warning applies\n // to (may differ from the session's own username when a mod is rolling a\n // random character for someone else via !createchar @user).\n try {\n await sqlite.execute(`ALTER TABLE creation_sessions ADD COLUMN target_user TEXT`);\n } catch (_) {\n /* column already exists */\n }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS encounters (\n broadcaster_id TEXT PRIMARY KEY, round INTEGER, current_index INTEGER, active INTEGER, entries TEXT, updated_at INTEGER\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS activity_logs (\n id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, broadcaster_id TEXT, action TEXT, detail TEXT, created_at INTEGER\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS channel_settings (\n broadcaster_id TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 1, updated_at INTEGER\n )`,\n );\n // Open-stall merchant: off by default per channel. next_post_at is the\n // randomized due time (ms epoch) for the next ad; the merchant cron\n // trigger polls this rather than posting on every tick.\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS merchant_settings (\n broadcaster_id TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 0, next_post_at INTEGER, updated_at INTEGER\n )`,\n );\n // Granular per-channel command-group toggles for the dashboard (see\n // COMMAND_GROUPS in utils.ts). Missing row = enabled, same \"absence means\n // default\" pattern as channel_settings above.\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS command_toggles (\n broadcaster_id TEXT, group_name TEXT, enabled INTEGER NOT NULL DEFAULT 1, updated_at INTEGER,\n PRIMARY KEY (broadcaster_id, group_name)\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS duel_challenges (\n broadcaster_id TEXT, challenger TEXT, defender TEXT, created_at INTEGER,\n PRIMARY KEY (broadcaster_id, challenger, defender)\n )`,\n );\n try {\n await sqlite.execute(`ALTER TABLE duel_challenges ADD COLUMN mode TEXT DEFAULT 'auto'`);\n } catch (_) {\n /* column already exists */\n }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS duels (\n broadcaster_id TEXT PRIMARY KEY, challenger TEXT, defender TEXT, current_turn TEXT,\n challenger_hp INTEGER, defender_hp INTEGER, active INTEGER, updated_at INTEGER\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS parties (\n broadcaster_id TEXT, party_name TEXT, owner TEXT, created_at INTEGER,\n PRIMARY KEY (broadcaster_id, party_name)\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS party_members (\n broadcaster_id TEXT, party_name TEXT, username TEXT, joined_at INTEGER,\n PRIMARY KEY (broadcaster_id, party_name, username)\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS party_invites (\n broadcaster_id TEXT, party_name TEXT, target TEXT, inviter TEXT, created_at INTEGER,\n PRIMARY KEY (broadcaster_id, party_name, target)\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS party_duel_challenges (\n broadcaster_id TEXT, challenger_party TEXT, defender_party TEXT,\n challenger_owner TEXT, defender_owner TEXT, created_at INTEGER,\n PRIMARY KEY (broadcaster_id, challenger_party, defender_party)\n )`,\n );\n try {\n await sqlite.execute(`ALTER TABLE party_duel_challenges ADD COLUMN mode TEXT DEFAULT 'auto'`);\n } catch (_) {\n /* column already exists */\n }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS party_duels (\n broadcaster_id TEXT PRIMARY KEY, challenger_party TEXT, defender_party TEXT,\n current_side TEXT, current_index INTEGER, challenger_members TEXT, defender_members TEXT,\n challenger_hp TEXT, defender_hp TEXT, active INTEGER, updated_at INTEGER\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS monster_duels (\n broadcaster_id TEXT PRIMARY KEY, player TEXT, monster_name TEXT, monster_cr TEXT,\n monster_ac INTEGER, monster_hp INTEGER, monster_hp_max INTEGER, monster_attack INTEGER,\n monster_damage_die INTEGER, monster_damage_bonus INTEGER, current_turn TEXT, active INTEGER, updated_at INTEGER,\n player_hp INTEGER\n )`,\n );\n // player_hp was added after the original table shipped — back-fill it for\n // channels whose monster_duels table predates this column, or every\n // !dndduel attack past the first exchange throws on the UPDATE below and\n // the bot goes silent.\n try { await sqlite.execute(`ALTER TABLE monster_duels ADD COLUMN player_hp INTEGER`); } catch (_) {}\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS party_monster_duels (\n broadcaster_id TEXT PRIMARY KEY,\n party_name TEXT,\n members TEXT,\n member_hp TEXT,\n current_index INTEGER,\n monster_name TEXT,\n monster_cr TEXT,\n monster_ac INTEGER,\n monster_hp INTEGER,\n monster_hp_max INTEGER,\n monster_attack INTEGER,\n monster_damage_die INTEGER,\n monster_damage_bonus INTEGER,\n active INTEGER,\n updated_at INTEGER\n )`,\n );\n // Custom commands (!dndbot add/edit/remove/cooldown/list) and passive\n // keyword triggers (!trigger), both authored from Twitch chat by the\n // broadcaster/mods — see customcommands.ts.\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS custom_commands (\n broadcaster_id TEXT, name TEXT, response TEXT, created_by TEXT,\n created_at INTEGER, updated_at INTEGER, uses INTEGER NOT NULL DEFAULT 0,\n cooldown_ms INTEGER NOT NULL DEFAULT ${DEFAULT_CUSTOM_COMMAND_COOLDOWN_MS}, last_used_at INTEGER NOT NULL DEFAULT 0,\n PRIMARY KEY (broadcaster_id, name)\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS custom_triggers (\n broadcaster_id TEXT, keyword TEXT, response TEXT, created_by TEXT,\n created_at INTEGER, updated_at INTEGER, uses INTEGER NOT NULL DEFAULT 0,\n cooldown_ms INTEGER NOT NULL DEFAULT ${DEFAULT_CUSTOM_TRIGGER_COOLDOWN_MS}, last_used_at INTEGER NOT NULL DEFAULT 0,\n PRIMARY KEY (broadcaster_id, keyword)\n )`,\n );\n // Timed messages: broadcaster/mod-authored announcements posted on a\n // recurring interval by timedmessages_cron.ts (a separate Val Town cron\n // trigger, same shape as merchant_cron.ts). Each row schedules itself via\n // next_post_at rather than the cron computing a shared schedule, so\n // messages with different intervals in the same channel rotate\n // independently instead of all firing together.\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS timed_messages (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n broadcaster_id TEXT NOT NULL,\n message TEXT NOT NULL,\n interval_minutes INTEGER NOT NULL,\n enabled INTEGER NOT NULL DEFAULT 1,\n created_by TEXT,\n created_at INTEGER,\n updated_at INTEGER,\n next_post_at INTEGER,\n last_sent_at INTEGER NOT NULL DEFAULT 0,\n uses INTEGER NOT NULL DEFAULT 0\n )`,\n );\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_custom_commands_channel ON custom_commands(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_custom_triggers_channel ON custom_triggers(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_timed_messages_channel ON timed_messages(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_timed_messages_due ON timed_messages(enabled, next_post_at)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_activity_logs_channel_id ON activity_logs(broadcaster_id,id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_activity_logs_created ON activity_logs(created_at)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_channel_characters_channel ON channel_characters(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_party_members_channel ON party_members(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_parties_channel ON parties(broadcaster_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_activity_logs_username_channel ON activity_logs(broadcaster_id,username)`);\n}\n\nexport async function getPartyMonsterDuel(broadcasterId: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM party_monster_duels WHERE broadcaster_id = ? AND active = 1\",\n [broadcasterId],\n );\n if (!res.rows.length) return null;\n const r = res.rows[0];\n return {\n ...r,\n current_index: Number(r.current_index),\n members: JSON.parse(r.members || \"[]\") as string[],\n member_hp: JSON.parse(r.member_hp || \"{}\") as Record,\n };\n}\n\nexport function rowToCharacter(r: any): Character {\n return {\n username: r.username,\n race: r.race,\n subrace: r.subrace,\n cls: r.class,\n level: r.level,\n xp: Number(r.xp ?? 0),\n scores: { STR: r.str, DEX: r.dex, CON: r.con, INT: r.int, WIS: r.wis, CHA: r.cha },\n speed: r.speed,\n hpMax: r.hp_max,\n hpCurrent: r.hp_current,\n proficiency: r.proficiency,\n traits: JSON.parse(r.traits || \"[]\"),\n spells: JSON.parse(r.spells || \"[]\"),\n items: JSON.parse(r.items || \"[]\"),\n feats: JSON.parse(r.feats || \"[]\"),\n abilities: JSON.parse(r.abilities || \"[]\"),\n };\n}\n\nexport async function getCharacter(username: string, broadcasterId: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM characters WHERE broadcaster_id = ? AND username = ?\",\n [broadcasterId, username],\n );\n if (!res.rows.length) return null;\n await sqlite.execute(\n \"UPDATE channel_characters SET updated_at = ? WHERE broadcaster_id = ? AND username = ?\",\n [Date.now(), broadcasterId, username],\n );\n return rowToCharacter(res.rows[0]);\n}\n\nexport async function saveCharacter(c: Character, broadcasterId: string) {\n const existing = await sqlite.execute(\n \"SELECT 1 FROM channel_characters WHERE broadcaster_id = ? AND username = ?\",\n [broadcasterId, c.username],\n );\n if (!existing.rows.length) {\n const max = Math.max(1, Number(Deno.env.get(\"MAX_CHARACTERS_PER_CHANNEL\") ?? \"500\"));\n const count = await sqlite.execute(\"SELECT COUNT(*) AS count FROM channel_characters WHERE broadcaster_id = ?\", [broadcasterId]);\n if (Number(count.rows[0]?.count ?? 0) >= max) throw new Error(`Channel character cap reached (${max}).`);\n }\n const now = Date.now();\n await sqlite.execute(\n \"INSERT OR IGNORE INTO channel_characters (broadcaster_id,username,created_at,updated_at) VALUES (?,?,?,?)\",\n [broadcasterId, c.username, now, now],\n );\n await sqlite.execute(\"UPDATE channel_characters SET updated_at = ? WHERE broadcaster_id = ? AND username = ?\", [now, broadcasterId, c.username]);\n await sqlite.execute(\n `INSERT OR REPLACE INTO characters\n (broadcaster_id,username,race,subrace,class,level,xp,str,dex,con,int,wis,cha,speed,hp_max,hp_current,proficiency,traits,spells,items,feats,abilities)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n [\n broadcasterId,\n c.username,\n c.race,\n c.subrace,\n c.cls,\n c.level,\n c.xp ?? 0,\n c.scores.STR,\n c.scores.DEX,\n c.scores.CON,\n c.scores.INT,\n c.scores.WIS,\n c.scores.CHA,\n c.speed,\n c.hpMax,\n c.hpCurrent,\n c.proficiency,\n JSON.stringify(c.traits),\n JSON.stringify(c.spells),\n JSON.stringify(c.items),\n JSON.stringify(c.feats),\n JSON.stringify(c.abilities),\n ],\n );\n}\n\nexport async function adjustHp(username: string, delta: number, broadcasterId: string) {\n const c = await getCharacter(username, broadcasterId);\n if (!c) return null;\n c.hpCurrent = Math.max(0, Math.min(c.hpMax, c.hpCurrent + delta));\n await sqlite.execute(\"UPDATE characters SET hp_current = ? WHERE broadcaster_id = ? AND username = ?\", [c.hpCurrent, broadcasterId, username]);\n return c;\n}\n\nexport async function backupCharacter(username: string, broadcasterId: string) {\n const c = await getCharacter(username, broadcasterId);\n if (!c) return false;\n await sqlite.execute(\n `INSERT OR REPLACE INTO character_backups\n (broadcaster_id,username,race,subrace,class,level,xp,str,dex,con,int,wis,cha,speed,hp_max,hp_current,proficiency,traits,spells,items,feats,abilities)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n [\n broadcasterId,\n c.username,\n c.race,\n c.subrace,\n c.cls,\n c.level,\n c.xp ?? 0,\n c.scores.STR,\n c.scores.DEX,\n c.scores.CON,\n c.scores.INT,\n c.scores.WIS,\n c.scores.CHA,\n c.speed,\n c.hpMax,\n c.hpCurrent,\n c.proficiency,\n JSON.stringify(c.traits),\n JSON.stringify(c.spells),\n JSON.stringify(c.items),\n JSON.stringify(c.feats),\n JSON.stringify(c.abilities),\n ],\n );\n return true;\n}\n\nexport async function loadBackup(username: string, broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM character_backups WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n if (!res.rows.length) return null;\n const c = rowToCharacter(res.rows[0]);\n await saveCharacter(c, broadcasterId);\n return c;\n}\n\nexport async function resetCharacter(username: string, broadcasterId: string) {\n await sqlite.execute(\"DELETE FROM channel_characters WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n await sqlite.execute(\"DELETE FROM characters WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n await sqlite.execute(\"DELETE FROM character_backups WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n}\n\nexport async function getCreationSession(username: string, broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM creation_sessions WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n if (!res.rows.length) return null;\n const r = res.rows[0];\n return {\n username,\n broadcasterId,\n step: r.step,\n race: r.race,\n subrace: r.subrace,\n cls: r.class,\n scores: r.scores ? JSON.parse(r.scores) : null,\n subclass: r.subclass ?? null,\n background: r.background ?? null,\n alignment: r.alignment ?? null,\n hook: r.hook ?? null,\n targetUser: r.target_user ?? null,\n };\n}\n\nexport async function saveCreationSession(s: any, broadcasterId: string) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO creation_sessions (broadcaster_id,username,step,race,subrace,class,scores,subclass,background,alignment,hook,target_user,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)\",\n [\n broadcasterId,\n s.username,\n s.step,\n s.race ?? null,\n s.subrace ?? null,\n s.cls ?? null,\n s.scores ? JSON.stringify(s.scores) : null,\n s.subclass ?? null,\n s.background ?? null,\n s.alignment ?? null,\n s.hook ?? null,\n s.targetUser ?? null,\n Date.now(),\n ],\n );\n}\n\nexport async function clearCreationSession(username: string, broadcasterId: string) {\n await sqlite.execute(\"DELETE FROM creation_sessions WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n}\n\nexport async function getConnections() {\n const res = await sqlite.execute(\"SELECT display_name, login FROM broadcasters WHERE connected = 1 ORDER BY connected_at ASC\");\n return res.rows.map((r: any) => r.display_name || r.login);\n}\n\nexport async function isChannelBlocked(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT 1 FROM channel_blocks WHERE broadcaster_id = ?\", [broadcasterId]);\n return res.rows.length > 0;\n}\n\nexport async function blockChannel(broadcasterId: string, reason = \"operator block\") {\n await sqlite.execute(\"INSERT OR REPLACE INTO channel_blocks (broadcaster_id,reason,created_at,updated_at) VALUES (?,?,COALESCE((SELECT created_at FROM channel_blocks WHERE broadcaster_id = ?),?),?)\", [broadcasterId, reason.slice(0,240), broadcasterId, Date.now(), Date.now()]);\n await setChannelEnabled(broadcasterId, false);\n}\n\nexport async function unblockChannel(broadcasterId: string) {\n await sqlite.execute(\"DELETE FROM channel_blocks WHERE broadcaster_id = ?\", [broadcasterId]);\n await setChannelEnabled(broadcasterId, true);\n}\n\nexport async function markBroadcasterDisconnected(broadcasterId: string, reason: string, subscriptionId?: string) {\n await sqlite.execute(\n \"UPDATE broadcasters SET connected = 0, disconnected_at = ?, disconnect_reason = ?, subscription_id = COALESCE(?, subscription_id) WHERE broadcaster_id = ?\",\n [Date.now(), reason.slice(0, 240), subscriptionId ?? null, broadcasterId],\n );\n}\n\nexport async function getBroadcaster(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM broadcasters WHERE broadcaster_id = ?\", [broadcasterId]);\n return res.rows.length ? res.rows[0] : null;\n}\n\n// Lets operator-only routes (e.g. /admin/dashboard-link) accept a Twitch\n// login instead of the numeric broadcaster_id, since that's what a human\n// actually has memorized.\nexport async function getBroadcasterByLogin(login: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM broadcasters WHERE login = ? COLLATE NOCASE\",\n [login],\n );\n return res.rows.length ? res.rows[0] : null;\n}\n\n// \"kind\" is a short label (\"sub\" for channel.subscribe, \"resub\" for\n// channel.subscription.message) distinguishing the two extra subscriptions\n// created alongside the main chat-message one in broadcasters.subscription_id.\nexport async function saveExtraEventSubSubscription(broadcasterId: string, kind: string, subscriptionId: string) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO eventsub_extra_subscriptions (broadcaster_id,kind,subscription_id,created_at) VALUES (?,?,?,?)\",\n [broadcasterId, kind, subscriptionId, Date.now()],\n );\n}\n\nexport async function getExtraEventSubSubscriptions(broadcasterId: string) {\n const res = await sqlite.execute(\n \"SELECT kind, subscription_id FROM eventsub_extra_subscriptions WHERE broadcaster_id = ?\",\n [broadcasterId],\n );\n return res.rows as Array<{ kind: string; subscription_id: string }>;\n}\n\nexport async function deleteExtraEventSubSubscriptions(broadcasterId: string) {\n await sqlite.execute(\"DELETE FROM eventsub_extra_subscriptions WHERE broadcaster_id = ?\", [broadcasterId]);\n}\n\nexport async function purgeChannelData(broadcasterId: string) {\n for (const table of [\n \"activity_logs\",\n \"creation_sessions\",\n \"encounters\",\n \"duel_challenges\",\n \"duels\",\n \"parties\",\n \"party_members\",\n \"party_invites\",\n \"party_duel_challenges\",\n \"party_duels\",\n \"monster_duels\",\n \"party_monster_duels\",\n \"channel_settings\",\n \"merchant_settings\",\n \"command_toggles\",\n \"chronicle_settings\",\n \"chronicle_activity\",\n \"npc_settings\",\n \"npc_chatter_settings\",\n \"npc_chatter_activity\",\n \"channel_characters\",\n \"characters\",\n \"character_backups\",\n \"eventsub_extra_subscriptions\",\n \"maps\",\n \"map_cells\",\n \"map_tokens\",\n \"dice_roll_events\",\n ]) {\n await sqlite.execute(`DELETE FROM ${table} WHERE broadcaster_id = ?`, [broadcasterId]);\n }\n // npc_characters/npc_conversations are keyed by owner_key (\"twitch:\"),\n // not broadcaster_id directly, so they don't fit the generic loop above.\n await sqlite.execute(\"DELETE FROM npc_characters WHERE owner_key = ?\", [`twitch:${broadcasterId}`]);\n await sqlite.execute(\"DELETE FROM npc_conversations WHERE owner_key = ?\", [`twitch:${broadcasterId}`]);\n await sqlite.execute(\"DELETE FROM broadcasters WHERE broadcaster_id = ?\", [broadcasterId]);\n}\n\nexport async function disconnectBroadcasterData(broadcasterId: string, purge = false) {\n if (purge) await purgeChannelData(broadcasterId);\n else {\n await sqlite.execute(\"DELETE FROM channel_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM merchant_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM command_toggles WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM chronicle_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM npc_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM npc_chatter_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM eventsub_extra_subscriptions WHERE broadcaster_id = ?\", [broadcasterId]);\n await sqlite.execute(\"DELETE FROM broadcasters WHERE broadcaster_id = ?\", [broadcasterId]);\n }\n}\n\nexport async function isChannelEnabled(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT enabled FROM channel_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n return !res.rows.length || Number(res.rows[0].enabled) === 1;\n}\n\nexport async function setChannelEnabled(broadcasterId: string, enabled: boolean) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO channel_settings (broadcaster_id, enabled, updated_at) VALUES (?,?,?)\",\n [broadcasterId, enabled ? 1 : 0, Date.now()],\n );\n}\n\n/** All command-group states for the dashboard's toggle list, defaulting any\n * group with no row (never touched) to enabled. */\nexport async function getCommandGroupToggles(broadcasterId: string): Promise> {\n const res = await sqlite.execute(\"SELECT group_name, enabled FROM command_toggles WHERE broadcaster_id = ?\", [broadcasterId]);\n const overrides = new Map(res.rows.map((r: any) => [String(r.group_name), Number(r.enabled) === 1]));\n const out: Record = {};\n for (const group of Object.keys(COMMAND_GROUPS)) out[group] = overrides.get(group) ?? true;\n return out;\n}\n\nexport async function isCommandGroupEnabled(broadcasterId: string, group: string): Promise {\n const res = await sqlite.execute(\"SELECT enabled FROM command_toggles WHERE broadcaster_id = ? AND group_name = ?\", [\n broadcasterId,\n group,\n ]);\n return !res.rows.length || Number(res.rows[0].enabled) === 1;\n}\n\nexport async function setCommandGroupEnabled(broadcasterId: string, group: string, enabled: boolean) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO command_toggles (broadcaster_id, group_name, enabled, updated_at) VALUES (?,?,?,?)\",\n [broadcasterId, group, enabled ? 1 : 0, Date.now()],\n );\n}\n\nexport async function isMerchantEnabled(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT enabled FROM merchant_settings WHERE broadcaster_id = ?\", [broadcasterId]);\n return res.rows.length > 0 && Number(res.rows[0].enabled) === 1;\n}\n\n/** Toggle the open-stall merchant. `nextPostAt` should be a freshly randomized\n * due time (ms epoch) when enabling; pass null when disabling. */\nexport async function setMerchantEnabled(broadcasterId: string, enabled: boolean, nextPostAt: number | null) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO merchant_settings (broadcaster_id, enabled, next_post_at, updated_at) VALUES (?,?,?,?)\",\n [broadcasterId, enabled ? 1 : 0, enabled ? nextPostAt : null, Date.now()],\n );\n}\n\n/** Recently-active chatters in a channel (from activity_logs), most-recent\n * first — the pool !oracle picks a name from. */\nexport async function getRecentChatters(broadcasterId: string, limit = 50) {\n const safeLimit = Math.max(1, Math.min(200, Math.floor(limit)));\n const res = await sqlite.execute(\n `SELECT username, MAX(created_at) AS last_seen\n FROM (SELECT username, created_at FROM activity_logs WHERE broadcaster_id = ? ORDER BY id DESC LIMIT 500) AS recent\n GROUP BY username\n ORDER BY last_seen DESC\n LIMIT ${safeLimit}`,\n [broadcasterId],\n );\n return res.rows.map((r: any) => String(r.username));\n}\n\n/** Channels whose merchant is enabled, due for a post, connected, not\n * operator-blocked, and not otherwise disabled via !dndbot off. */\nexport async function getDueMerchantChannels(now: number): Promise {\n const res = await sqlite.execute(\n `SELECT m.broadcaster_id AS broadcaster_id\n\n FROM merchant_settings m\n JOIN broadcasters b ON b.broadcaster_id = m.broadcaster_id AND b.connected = 1\n LEFT JOIN channel_settings cs ON cs.broadcaster_id = m.broadcaster_id\n LEFT JOIN channel_blocks cb ON cb.broadcaster_id = m.broadcaster_id\n WHERE m.enabled = 1\n AND m.next_post_at IS NOT NULL AND m.next_post_at <= ?\n AND cb.broadcaster_id IS NULL\n AND (cs.enabled IS NULL OR cs.enabled = 1)`,\n [now],\n );\n return res.rows.map((r: any) => String(r.broadcaster_id));\n}\n\nexport async function rescheduleMerchant(broadcasterId: string, nextPostAt: number) {\n await sqlite.execute(\n \"UPDATE merchant_settings SET next_post_at = ?, updated_at = ? WHERE broadcaster_id = ?\",\n [nextPostAt, Date.now(), broadcasterId],\n );\n}\n\n/** Called once per merchant_cron.ts tick — overwrites the single status row\n * so GET /admin/merchant/status and /admin/logs can tell whether the cron is\n * still ticking and whether its last run posted cleanly. */\nexport async function recordMerchantCronRun(channelsDue: number, postsOk: number, postsFailed: number) {\n await sqlite.execute(\n `INSERT INTO merchant_cron_status (id, last_run_at, channels_due, last_posts_ok, last_posts_failed)\n VALUES (1, ?, ?, ?, ?)\n ON CONFLICT(id) DO UPDATE SET last_run_at = excluded.last_run_at, channels_due = excluded.channels_due,\n last_posts_ok = excluded.last_posts_ok, last_posts_failed = excluded.last_posts_failed`,\n [Date.now(), channelsDue, postsOk, postsFailed],\n );\n}\n\nexport async function getMerchantCronStatus() {\n const res = await sqlite.execute(\"SELECT * FROM merchant_cron_status WHERE id = 1\");\n return res.rows.length ? res.rows[0] : null;\n}\n\n/** Per-channel merchant on/off + next-due-time, for every connected\n * channel — the operator-only overview table on GET /admin/logs. */\nexport async function getMerchantOverview() {\n const res = await sqlite.execute(\n `SELECT b.broadcaster_id AS broadcaster_id, b.display_name AS display_name, b.login AS login,\n COALESCE(m.enabled, 0) AS enabled, m.next_post_at AS next_post_at\n FROM broadcasters b\n LEFT JOIN merchant_settings m ON m.broadcaster_id = b.broadcaster_id\n WHERE b.connected = 1\n ORDER BY b.display_name ASC`,\n );\n return res.rows;\n}\n\n/** Most recent monitor_events, optionally filtered to one `kind` (e.g.\n * \"merchant\") — backs both the JSON status endpoint and the /admin/logs page. */\nexport async function getMonitorEvents(kind?: string, limit = 100) {\n const res = kind\n ? await sqlite.execute(\"SELECT * FROM monitor_events WHERE kind = ? ORDER BY id DESC LIMIT ?\", [kind, limit])\n : await sqlite.execute(\"SELECT * FROM monitor_events ORDER BY id DESC LIMIT ?\", [limit]);\n return res.rows;\n}\n\nexport async function getDuel(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM duels WHERE broadcaster_id = ? AND active = 1\", [broadcasterId]);\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function getMonsterDuel(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM monster_duels WHERE broadcaster_id = ? AND active = 1\", [\n broadcasterId,\n ]);\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function getParty(broadcasterId: string, partyName: string) {\n const res = await sqlite.execute(\"SELECT * FROM parties WHERE broadcaster_id = ? AND party_name = ?\", [\n broadcasterId,\n partyName.toLowerCase(),\n ]);\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function getPartyMembers(broadcasterId: string, partyName: string) {\n const res = await sqlite.execute(\n \"SELECT username FROM party_members WHERE broadcaster_id = ? AND party_name = ? ORDER BY joined_at ASC\",\n [broadcasterId, partyName.toLowerCase()],\n );\n return res.rows.map((r: any) => String(r.username));\n}\n\n// A party invite must be explicitly accepted by the target before they're\n// added to party_members — see !party invite / accept / decline.\nexport async function createPartyInvite(\n broadcasterId: string,\n partyName: string,\n target: string,\n inviter: string,\n) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO party_invites (broadcaster_id,party_name,target,inviter,created_at) VALUES (?,?,?,?,?)\",\n [broadcasterId, partyName, target, inviter, Date.now()],\n );\n}\n\nexport async function getPartyInvites(broadcasterId: string, target: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM party_invites WHERE broadcaster_id = ? AND target = ? ORDER BY created_at DESC\",\n [broadcasterId, target],\n );\n return res.rows;\n}\n\nexport async function getPartyInvite(broadcasterId: string, target: string, partyName: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM party_invites WHERE broadcaster_id = ? AND target = ? AND party_name = ?\",\n [broadcasterId, target, partyName],\n );\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function deletePartyInvite(broadcasterId: string, partyName: string, target: string) {\n await sqlite.execute(\"DELETE FROM party_invites WHERE broadcaster_id = ? AND party_name = ? AND target = ?\", [\n broadcasterId,\n partyName,\n target,\n ]);\n}\n\nexport async function getPartyDuel(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM party_duels WHERE broadcaster_id = ? AND active = 1\", [\n broadcasterId,\n ]);\n if (!res.rows.length) return null;\n const r = res.rows[0];\n return {\n ...r,\n current_index: Number(r.current_index),\n challenger_members: JSON.parse(r.challenger_members || \"[]\"),\n defender_members: JSON.parse(r.defender_members || \"[]\"),\n challenger_hp: JSON.parse(r.challenger_hp || \"{}\"),\n defender_hp: JSON.parse(r.defender_hp || \"{}\"),\n };\n}\n\nexport async function getEncounter(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM encounters WHERE broadcaster_id = ?\", [broadcasterId]);\n if (!res.rows.length) return null;\n const r = res.rows[0];\n return {\n broadcasterId,\n round: Number(r.round),\n currentIndex: Number(r.current_index),\n active: Boolean(r.active),\n entries: JSON.parse(r.entries || \"[]\") as Array<{ name: string; initiative: number; dex?: number; rolled?: boolean }>,\n };\n}\n\nexport async function saveEncounter(e: any) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO encounters (broadcaster_id,round,current_index,active,entries,updated_at) VALUES (?,?,?,?,?,?)\",\n [e.broadcasterId, e.round, e.currentIndex, e.active ? 1 : 0, JSON.stringify(e.entries), Date.now()],\n );\n}\n\nexport async function recordActivity(username: string, broadcasterId: string, action: string, detail: string) {\n await sqlite.execute(\n \"INSERT INTO activity_logs (username,broadcaster_id,action,detail,created_at) VALUES (?,?,?,?,?)\",\n [username, broadcasterId, action.slice(0, 40), detail.replace(/\\s+/g, \" \").trim().slice(0, 220), Date.now()],\n );\n // Retain at most 5,000 rows per channel, plus a hard 90-day privacy window.\n await sqlite.execute(\n `DELETE FROM activity_logs\n WHERE created_at < ?\n OR (broadcaster_id = ? AND id NOT IN (SELECT id FROM activity_logs WHERE broadcaster_id = ? ORDER BY id DESC LIMIT 5000))`,\n [Date.now() - 90 * 24 * 60 * 60 * 1000, broadcasterId, broadcasterId],\n );\n}\n\nexport async function getRecentLogs(broadcasterId?: string, limit = 200) {\n const safeLimit = Math.max(1, Math.min(500, Math.floor(limit)));\n const query = broadcasterId\n ? `SELECT username,broadcaster_id,action,detail,created_at FROM activity_logs WHERE broadcaster_id = ? ORDER BY id DESC LIMIT ${safeLimit}`\n : `SELECT username,broadcaster_id,action,detail,created_at FROM activity_logs ORDER BY id DESC LIMIT ${safeLimit}`;\n const res = await sqlite.execute(query, broadcasterId ? [broadcasterId] : []);\n return res.rows;\n}\n\nexport { sqlite };\n\nexport async function queueEventSubCancellation(subscriptionId: string, broadcasterId: string, error: string) {\n if (!subscriptionId) return;\n await sqlite.execute(\n `INSERT OR REPLACE INTO pending_eventsub_cancellations (subscription_id,broadcaster_id,attempts,last_error,updated_at)\n VALUES (?, ?, COALESCE((SELECT attempts FROM pending_eventsub_cancellations WHERE subscription_id = ?), 0) + 1, ?, ?)`,\n [subscriptionId, broadcasterId, subscriptionId, error.slice(0, 500), Date.now()],\n );\n}\n\nexport async function getPendingEventSubCancellations(limit = 5) {\n const safeLimit = Math.max(1, Math.min(20, Math.floor(limit)));\n const res = await sqlite.execute(`SELECT * FROM pending_eventsub_cancellations ORDER BY updated_at ASC LIMIT ${safeLimit}`);\n return res.rows;\n}\n\nexport async function clearPendingEventSubCancellation(subscriptionId: string) {\n await sqlite.execute(\"DELETE FROM pending_eventsub_cancellations WHERE subscription_id = ?\", [subscriptionId]);\n}\n\nexport async function claimEventSubMessage(messageId: string, now = Date.now()) {\n if (!messageId) return false;\n try {\n await sqlite.execute(\"INSERT INTO eventsub_messages (message_id,received_at) VALUES (?,?)\", [messageId, now]);\n await sqlite.execute(\"DELETE FROM eventsub_messages WHERE received_at < ?\", [now - 24 * 60 * 60 * 1000]);\n return true;\n } catch (_) {\n return false;\n }\n}\n\nexport async function checkCommandRateLimit(broadcasterId: string, username: string, cooldownMs = 1200) {\n const now = Date.now();\n const res = await sqlite.execute(\"SELECT last_at FROM command_rate_limits WHERE broadcaster_id = ? AND username = ?\", [broadcasterId, username]);\n const last = Number(res.rows[0]?.last_at ?? 0);\n if (now - last < cooldownMs) return false;\n await sqlite.execute(\"INSERT OR REPLACE INTO command_rate_limits (broadcaster_id,username,last_at) VALUES (?,?,?)\", [broadcasterId, username, now]);\n await sqlite.execute(\"DELETE FROM command_rate_limits WHERE last_at < ?\", [now - 10 * 60 * 1000]);\n return true;\n}\n\n// Per-channel cooldown so a wave of \"goodnight\"s from many chatters only\n// gets one bot reply instead of one per person.\nexport async function checkGoodnightCooldown(broadcasterId: string, cooldownMs = 300_000) {\n const now = Date.now();\n const res = await sqlite.execute(\"SELECT last_at FROM goodnight_cooldowns WHERE broadcaster_id = ?\", [broadcasterId]);\n const last = Number(res.rows[0]?.last_at ?? 0);\n if (now - last < cooldownMs) return false;\n await sqlite.execute(\"INSERT OR REPLACE INTO goodnight_cooldowns (broadcaster_id,last_at) VALUES (?,?)\", [broadcasterId, now]);\n return true;\n}\n\nexport async function recordMonitorEvent(kind: string, detail: string) {\n await sqlite.execute(\"INSERT INTO monitor_events (kind,detail,created_at) VALUES (?,?,?)\", [kind.slice(0,80), detail.slice(0,500), Date.now()]);\n await sqlite.execute(\"DELETE FROM monitor_events WHERE id NOT IN (SELECT id FROM monitor_events ORDER BY id DESC LIMIT 1000)\");\n}\n\nexport async function getChannelCounts(broadcasterId: string) {\n const [p, c, l] = await Promise.all([\n sqlite.execute(\"SELECT COUNT(*) AS count FROM parties WHERE broadcaster_id = ?\", [broadcasterId]),\n sqlite.execute(\"SELECT COUNT(*) AS count FROM channel_characters WHERE broadcaster_id = ?\", [broadcasterId]),\n sqlite.execute(\"SELECT COUNT(*) AS count FROM activity_logs WHERE broadcaster_id = ?\", [broadcasterId]),\n ]);\n return { parties: Number(p.rows[0]?.count ?? 0), characters: Number(c.rows[0]?.count ?? 0), logs: Number(l.rows[0]?.count ?? 0) };\n}\n\n// ── Maps ──\n\nexport type MapRow = {\n broadcaster_id: string;\n map_name: string;\n width: number;\n height: number;\n default_terrain: string;\n created_by: string | null;\n created_at: number;\n updated_at: number;\n};\n\nexport type MapToken = {\n username: string;\n display_name: string | null;\n x: number;\n y: number;\n};\n\nexport async function getMap(broadcasterId: string, mapName: string): Promise {\n const res = await sqlite.execute(\"SELECT * FROM maps WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n if (!res.rows.length) return null;\n const r: any = res.rows[0];\n return {\n broadcaster_id: r.broadcaster_id,\n map_name: r.map_name,\n width: Number(r.width),\n height: Number(r.height),\n default_terrain: r.default_terrain,\n created_by: r.created_by,\n created_at: Number(r.created_at),\n updated_at: Number(r.updated_at),\n };\n}\n\nexport async function listMaps(broadcasterId: string): Promise {\n const res = await sqlite.execute(\"SELECT * FROM maps WHERE broadcaster_id = ? ORDER BY map_name ASC\", [broadcasterId]);\n return res.rows.map((r: any) => ({\n broadcaster_id: r.broadcaster_id,\n map_name: r.map_name,\n width: Number(r.width),\n height: Number(r.height),\n default_terrain: r.default_terrain,\n created_by: r.created_by,\n created_at: Number(r.created_at),\n updated_at: Number(r.updated_at),\n }));\n}\n\nexport async function createMap(\n broadcasterId: string,\n mapName: string,\n width: number,\n height: number,\n createdBy: string,\n) {\n const maxMaps = Math.max(1, Number(Deno.env.get(\"MAX_MAPS_PER_CHANNEL\") ?? \"25\"));\n const count = await sqlite.execute(\"SELECT COUNT(*) AS count FROM maps WHERE broadcaster_id = ?\", [broadcasterId]);\n if (Number(count.rows[0]?.count ?? 0) >= maxMaps) throw new Error(`Channel map cap reached (${maxMaps}).`);\n const now = Date.now();\n await sqlite.execute(\n \"INSERT INTO maps (broadcaster_id,map_name,width,height,default_terrain,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?)\",\n [broadcasterId, mapName, width, height, \"grass\", createdBy, now, now],\n );\n}\n\nexport async function deleteMap(broadcasterId: string, mapName: string) {\n await sqlite.execute(\"DELETE FROM maps WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n await sqlite.execute(\"DELETE FROM map_cells WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n await sqlite.execute(\"DELETE FROM map_tokens WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n}\n\nasync function touchMap(broadcasterId: string, mapName: string) {\n await sqlite.execute(\"UPDATE maps SET updated_at = ? WHERE broadcaster_id = ? AND map_name = ?\", [Date.now(), broadcasterId, mapName]);\n}\n\nexport async function getMapCells(broadcasterId: string, mapName: string): Promise> {\n const res = await sqlite.execute(\"SELECT x,y,terrain FROM map_cells WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n const out: Record = {};\n for (const r of res.rows as any[]) out[`${r.x},${r.y}`] = r.terrain;\n return out;\n}\n\nexport async function setMapCell(broadcasterId: string, mapName: string, x: number, y: number, terrain: string) {\n await sqlite.execute(\n \"INSERT OR REPLACE INTO map_cells (broadcaster_id,map_name,x,y,terrain) VALUES (?,?,?,?,?)\",\n [broadcasterId, mapName, x, y, terrain],\n );\n await touchMap(broadcasterId, mapName);\n}\n\nexport async function fillMap(broadcasterId: string, mapName: string, terrain: string) {\n await sqlite.execute(\"DELETE FROM map_cells WHERE broadcaster_id = ? AND map_name = ?\", [broadcasterId, mapName]);\n await sqlite.execute(\"UPDATE maps SET default_terrain = ?, updated_at = ? WHERE broadcaster_id = ? AND map_name = ?\", [\n terrain,\n Date.now(),\n broadcasterId,\n mapName,\n ]);\n}\n\nexport async function getMapTokens(broadcasterId: string, mapName: string): Promise {\n const res = await sqlite.execute(\n \"SELECT username,display_name,x,y FROM map_tokens WHERE broadcaster_id = ? AND map_name = ?\",\n [broadcasterId, mapName],\n );\n return (res.rows as any[]).map((r) => ({ username: r.username, display_name: r.display_name, x: Number(r.x), y: Number(r.y) }));\n}\n\nexport async function getMapToken(broadcasterId: string, mapName: string, username: string): Promise {\n const res = await sqlite.execute(\n \"SELECT username,display_name,x,y FROM map_tokens WHERE broadcaster_id = ? AND map_name = ? AND username = ?\",\n [broadcasterId, mapName, username],\n );\n if (!res.rows.length) return null;\n const r: any = res.rows[0];\n return { username: r.username, display_name: r.display_name, x: Number(r.x), y: Number(r.y) };\n}\n\nexport async function findTokenAt(broadcasterId: string, mapName: string, x: number, y: number): Promise {\n const res = await sqlite.execute(\n \"SELECT username,display_name,x,y FROM map_tokens WHERE broadcaster_id = ? AND map_name = ? AND x = ? AND y = ?\",\n [broadcasterId, mapName, x, y],\n );\n if (!res.rows.length) return null;\n const r: any = res.rows[0];\n return { username: r.username, display_name: r.display_name, x: Number(r.x), y: Number(r.y) };\n}\n\nexport async function upsertMapToken(\n broadcasterId: string,\n mapName: string,\n username: string,\n displayName: string,\n x: number,\n y: number,\n) {\n const now = Date.now();\n await sqlite.execute(\n \"INSERT OR REPLACE INTO map_tokens (broadcaster_id,map_name,username,display_name,x,y,placed_at,updated_at) VALUES (?,?,?,?,?,?,?,?)\",\n [broadcasterId, mapName, username, displayName, x, y, now, now],\n );\n await touchMap(broadcasterId, mapName);\n}\n\nexport async function removeMapToken(broadcasterId: string, mapName: string, username: string) {\n await sqlite.execute(\"DELETE FROM map_tokens WHERE broadcaster_id = ? AND map_name = ? AND username = ?\", [broadcasterId, mapName, username]);\n await touchMap(broadcasterId, mapName);\n}\n\n// ── Custom commands & triggers ──\n\nexport async function countCustomCommands(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT COUNT(*) AS count FROM custom_commands WHERE broadcaster_id = ?\", [broadcasterId]);\n return Number(res.rows[0]?.count ?? 0);\n}\n\nexport async function getCustomCommand(broadcasterId: string, name: string) {\n const res = await sqlite.execute(\"SELECT * FROM custom_commands WHERE broadcaster_id = ? AND name = ?\", [broadcasterId, name]);\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function listCustomCommands(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT name, uses FROM custom_commands WHERE broadcaster_id = ? ORDER BY name ASC\", [broadcasterId]);\n return res.rows;\n}\n\nexport async function addCustomCommand(broadcasterId: string, name: string, response: string, createdBy: string) {\n if (await getCustomCommand(broadcasterId, name)) return { ok: false as const, error: \"exists\" as const };\n const max = Math.max(1, Number(Deno.env.get(\"MAX_CUSTOM_COMMANDS_PER_CHANNEL\") ?? \"100\"));\n if ((await countCustomCommands(broadcasterId)) >= max) return { ok: false as const, error: \"cap\" as const, max };\n const now = Date.now();\n await sqlite.execute(\n \"INSERT INTO custom_commands (broadcaster_id,name,response,created_by,created_at,updated_at,uses,cooldown_ms,last_used_at) VALUES (?,?,?,?,?,?,0,?,0)\",\n [broadcasterId, name, response, createdBy, now, now, DEFAULT_CUSTOM_COMMAND_COOLDOWN_MS],\n );\n return { ok: true as const };\n}\n\nexport async function editCustomCommand(broadcasterId: string, name: string, response: string) {\n if (!(await getCustomCommand(broadcasterId, name))) return false;\n await sqlite.execute(\n \"UPDATE custom_commands SET response = ?, updated_at = ? WHERE broadcaster_id = ? AND name = ?\",\n [response, Date.now(), broadcasterId, name],\n );\n return true;\n}\n\nexport async function deleteCustomCommand(broadcasterId: string, name: string) {\n if (!(await getCustomCommand(broadcasterId, name))) return false;\n await sqlite.execute(\"DELETE FROM custom_commands WHERE broadcaster_id = ? AND name = ?\", [broadcasterId, name]);\n return true;\n}\n\nexport async function setCustomCommandCooldown(broadcasterId: string, name: string, cooldownMs: number) {\n if (!(await getCustomCommand(broadcasterId, name))) return false;\n await sqlite.execute(\n \"UPDATE custom_commands SET cooldown_ms = ?, updated_at = ? WHERE broadcaster_id = ? AND name = ?\",\n [cooldownMs, Date.now(), broadcasterId, name],\n );\n return true;\n}\n\n/** Atomically checks cooldown and (if clear) records a use. Returns null if the command doesn't exist. */\nexport async function useCustomCommand(broadcasterId: string, name: string) {\n const row = await getCustomCommand(broadcasterId, name);\n if (!row) return null;\n const now = Date.now();\n const cooldownMs = Number(row.cooldown_ms ?? 0);\n const lastUsedAt = Number(row.last_used_at ?? 0);\n if (cooldownMs > 0 && now - lastUsedAt < cooldownMs) return { row, onCooldown: true as const };\n await sqlite.execute(\n \"UPDATE custom_commands SET uses = uses + 1, last_used_at = ? WHERE broadcaster_id = ? AND name = ?\",\n [now, broadcasterId, name],\n );\n return { row: { ...row, uses: Number(row.uses ?? 0) + 1 }, onCooldown: false as const };\n}\n\nexport async function countCustomTriggers(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT COUNT(*) AS count FROM custom_triggers WHERE broadcaster_id = ?\", [broadcasterId]);\n return Number(res.rows[0]?.count ?? 0);\n}\n\nexport async function getCustomTrigger(broadcasterId: string, keyword: string) {\n const res = await sqlite.execute(\"SELECT * FROM custom_triggers WHERE broadcaster_id = ? AND keyword = ?\", [broadcasterId, keyword]);\n return res.rows.length ? res.rows[0] : null;\n}\n\n// Every plain chat message is matched against this list (see\n// handleTriggerMatch in customcommands.ts), so it's kept small per channel\n// and cached only for the duration of a single request.\nexport async function listCustomTriggers(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM custom_triggers WHERE broadcaster_id = ? ORDER BY created_at ASC\", [broadcasterId]);\n return res.rows;\n}\n\nexport async function addCustomTrigger(broadcasterId: string, keyword: string, response: string, createdBy: string) {\n if (await getCustomTrigger(broadcasterId, keyword)) return { ok: false as const, error: \"exists\" as const };\n const max = Math.max(1, Number(Deno.env.get(\"MAX_CUSTOM_TRIGGERS_PER_CHANNEL\") ?? \"50\"));\n if ((await countCustomTriggers(broadcasterId)) >= max) return { ok: false as const, error: \"cap\" as const, max };\n const now = Date.now();\n await sqlite.execute(\n \"INSERT INTO custom_triggers (broadcaster_id,keyword,response,created_by,created_at,updated_at,uses,cooldown_ms,last_used_at) VALUES (?,?,?,?,?,?,0,?,0)\",\n [broadcasterId, keyword, response, createdBy, now, now, DEFAULT_CUSTOM_TRIGGER_COOLDOWN_MS],\n );\n return { ok: true as const };\n}\n\n// No !trigger edit chat command exists (mods currently remove+re-add to\n// change a trigger's response — see customcommands.ts), but the web\n// dashboard edits triggers in place like it does custom commands, so this\n// mirrors editCustomCommand for that one caller.\nexport async function editCustomTrigger(broadcasterId: string, keyword: string, response: string) {\n if (!(await getCustomTrigger(broadcasterId, keyword))) return false;\n await sqlite.execute(\n \"UPDATE custom_triggers SET response = ?, updated_at = ? WHERE broadcaster_id = ? AND keyword = ?\",\n [response, Date.now(), broadcasterId, keyword],\n );\n return true;\n}\n\nexport async function deleteCustomTrigger(broadcasterId: string, keyword: string) {\n if (!(await getCustomTrigger(broadcasterId, keyword))) return false;\n await sqlite.execute(\"DELETE FROM custom_triggers WHERE broadcaster_id = ? AND keyword = ?\", [broadcasterId, keyword]);\n return true;\n}\n\nexport async function setCustomTriggerCooldown(broadcasterId: string, keyword: string, cooldownMs: number) {\n if (!(await getCustomTrigger(broadcasterId, keyword))) return false;\n await sqlite.execute(\n \"UPDATE custom_triggers SET cooldown_ms = ?, updated_at = ? WHERE broadcaster_id = ? AND keyword = ?\",\n [cooldownMs, Date.now(), broadcasterId, keyword],\n );\n return true;\n}\n\nexport async function markCustomTriggerUsed(broadcasterId: string, keyword: string) {\n await sqlite.execute(\n \"UPDATE custom_triggers SET uses = uses + 1, last_used_at = ? WHERE broadcaster_id = ? AND keyword = ?\",\n [Date.now(), broadcasterId, keyword],\n );\n}\n\n// Full rows (unlike listCustomCommands' name+uses-only projection used by\n// the in-chat !dndbot list) — for the web dashboard, which needs to show\n// and pre-fill the actual response/cooldown for editing.\nexport async function listCustomCommandsFull(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM custom_commands WHERE broadcaster_id = ? ORDER BY name ASC\", [broadcasterId]);\n return res.rows;\n}\n\n// ── Timed messages (!timedmsg — see timedmessages.ts, posted by timedmessages_cron.ts) ──\n\nexport async function countTimedMessages(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT COUNT(*) AS count FROM timed_messages WHERE broadcaster_id = ?\", [broadcasterId]);\n return Number(res.rows[0]?.count ?? 0);\n}\n\nexport async function listTimedMessages(broadcasterId: string) {\n const res = await sqlite.execute(\"SELECT * FROM timed_messages WHERE broadcaster_id = ? ORDER BY id ASC\", [broadcasterId]);\n return res.rows;\n}\n\nexport async function getTimedMessage(broadcasterId: string, id: number) {\n const res = await sqlite.execute(\"SELECT * FROM timed_messages WHERE broadcaster_id = ? AND id = ?\", [broadcasterId, id]);\n return res.rows.length ? res.rows[0] : null;\n}\n\nexport async function addTimedMessage(broadcasterId: string, message: string, intervalMinutes: number, createdBy: string) {\n const max = Math.max(1, Number(Deno.env.get(\"MAX_TIMED_MESSAGES_PER_CHANNEL\") ?? \"20\"));\n if ((await countTimedMessages(broadcasterId)) >= max) return { ok: false as const, error: \"cap\" as const, max };\n const now = Date.now();\n await sqlite.execute(\n \"INSERT INTO timed_messages (broadcaster_id,message,interval_minutes,enabled,created_by,created_at,updated_at,next_post_at,last_sent_at,uses) VALUES (?,?,?,1,?,?,?,?,0,0)\",\n [broadcasterId, message, intervalMinutes, createdBy, now, now, now + intervalMinutes * 60_000],\n );\n // No lastInsertRowId plumbing exists elsewhere in this codebase to reuse,\n // so the new row is looked up by its own created_at instead — safe enough\n // since it's scoped to one channel and a collision needs two adds in the\n // same broadcaster within the same millisecond.\n const res = await sqlite.execute(\n \"SELECT id FROM timed_messages WHERE broadcaster_id = ? AND created_at = ? ORDER BY id DESC LIMIT 1\",\n [broadcasterId, now],\n );\n return { ok: true as const, id: Number(res.rows[0]?.id ?? 0) };\n}\n\nexport async function editTimedMessage(broadcasterId: string, id: number, message: string) {\n if (!(await getTimedMessage(broadcasterId, id))) return false;\n await sqlite.execute(\n \"UPDATE timed_messages SET message = ?, updated_at = ? WHERE broadcaster_id = ? AND id = ?\",\n [message, Date.now(), broadcasterId, id],\n );\n return true;\n}\n\n// Changing the interval reschedules from now, so a mod shortening a 6-hour\n// interval to 10 minutes doesn't cause an immediate flood of overdue posts.\nexport async function setTimedMessageInterval(broadcasterId: string, id: number, intervalMinutes: number) {\n if (!(await getTimedMessage(broadcasterId, id))) return false;\n const now = Date.now();\n await sqlite.execute(\n \"UPDATE timed_messages SET interval_minutes = ?, next_post_at = ?, updated_at = ? WHERE broadcaster_id = ? AND id = ?\",\n [intervalMinutes, now + intervalMinutes * 60_000, now, broadcasterId, id],\n );\n return true;\n}\n\n// Re-enabling also reschedules from now, for the same reason as above — a\n// message paused for a week shouldn't fire the instant it's turned back on.\nexport async function setTimedMessageEnabled(broadcasterId: string, id: number, enabled: boolean) {\n const row = await getTimedMessage(broadcasterId, id);\n if (!row) return false;\n const now = Date.now();\n const nextPostAt = enabled ? now + Number(row.interval_minutes ?? DEFAULT_TIMED_MESSAGE_INTERVAL_MINUTES) * 60_000 : row.next_post_at;\n await sqlite.execute(\n \"UPDATE timed_messages SET enabled = ?, next_post_at = ?, updated_at = ? WHERE broadcaster_id = ? AND id = ?\",\n [enabled ? 1 : 0, nextPostAt, now, broadcasterId, id],\n );\n return true;\n}\n\nexport async function deleteTimedMessage(broadcasterId: string, id: number) {\n if (!(await getTimedMessage(broadcasterId, id))) return false;\n await sqlite.execute(\"DELETE FROM timed_messages WHERE broadcaster_id = ? AND id = ?\", [broadcasterId, id]);\n return true;\n}\n\n// Polled by timedmessages_cron.ts. Only messages in currently-connected,\n// non-blocked channels are considered due — mirrors getDueMerchantChannels'\n// join against broadcasters(connected = 1).\nexport async function getDueTimedMessages(now: number) {\n const res = await sqlite.execute(\n `SELECT t.* FROM timed_messages t\n JOIN broadcasters b ON b.broadcaster_id = t.broadcaster_id AND b.connected = 1\n WHERE t.enabled = 1 AND t.next_post_at IS NOT NULL AND t.next_post_at <= ?`,\n [now],\n );\n return res.rows;\n}\n\nexport async function recordTimedMessageSent(id: number, nextPostAt: number) {\n await sqlite.execute(\n \"UPDATE timed_messages SET next_post_at = ?, last_sent_at = ?, uses = uses + 1 WHERE id = ?\",\n [nextPostAt, Date.now(), id],\n );\n}\n\n// ── Web dashboard capability token (see dashboard.ts) ──\n\nexport async function getDashboardKey(broadcasterId: string): Promise {\n const broadcaster = await getBroadcaster(broadcasterId);\n return broadcaster?.dashboard_key ? String(broadcaster.dashboard_key) : null;\n}\n\n/** Returns the channel's existing dashboard key, minting one on first use. */\nexport async function getOrCreateDashboardKey(broadcasterId: string): Promise {\n const existing = await getDashboardKey(broadcasterId);\n if (existing) return existing;\n return await regenerateDashboardKey(broadcasterId);\n}\n\n/** Invalidates any previously shared dashboard link (e.g. leaked in chat/VOD) and issues a fresh key. */\nexport async function regenerateDashboardKey(broadcasterId: string): Promise {\n const key = crypto.randomUUID().replace(/-/g, \"\");\n await sqlite.execute(\"UPDATE broadcasters SET dashboard_key = ? WHERE broadcaster_id = ?\", [key, broadcasterId]);\n return key;\n}\n\n/** True only for a connected channel whose current dashboard key matches. */\nexport async function verifyDashboardKey(broadcasterId: string, key: string): Promise {\n if (!key || key.length < 20) return false;\n const broadcaster = await getBroadcaster(broadcasterId);\n if (!broadcaster || Number(broadcaster.connected) !== 1) return false;\n return !!broadcaster.dashboard_key && String(broadcaster.dashboard_key) === key;\n}\n\n// ── Dashboard \"log in with Twitch\" OAuth state (see dashboard.ts) ──\n\nexport async function saveDashboardOAuthState(state: string, channelId: string, dashboardKey: string, expiresAt: number) {\n await sqlite.execute(\n \"INSERT INTO dashboard_oauth_states (state, channel_id, dashboard_key, expires_at) VALUES (?,?,?,?)\",\n [state, channelId, dashboardKey, expiresAt],\n );\n}\n\n/** One-time read: returns the state row (if valid and unexpired) and always\n * deletes it, so a state value can never be replayed. */\nexport async function consumeDashboardOAuthState(state: string) {\n const res = await sqlite.execute(\n \"SELECT * FROM dashboard_oauth_states WHERE state = ? AND expires_at > ?\",\n [state, Date.now()],\n );\n await sqlite.execute(\"DELETE FROM dashboard_oauth_states WHERE state = ?\", [state]);\n return res.rows.length ? res.rows[0] : null;\n}\n","deleted",false,"label",[14],"20e82baa-ad9c-41ac-a8e5-4d2d285f6a79",["D",1787995257440],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","userId","cc37ba10-6e6d-4af6-a801-9955c92d23c4","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a04ba1-c79f-7319-b4f2-7c4f3772837b","tursoName","20e82baaad9c41aca8e54d2d285f6a79","tursoHostname","20e82baaad9c41aca8e54d2d285f6a79-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789500119686],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_105":101},"org",{"_9":10,"_98":99,"_100":101,"_55":56,"_102":103,"_104":42},"branch",{"_35":88,"_11":89,"_22":28,"_90":45,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":12,"_97":10},"isOwned",512,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Shea Stolp","avatar","https://lh3.googleusercontent.com/a/ACg8ocIWmQgR3gyoMMicg2zCAoN1c7i36qX5mf9gRIdIXm8F2mo-ig=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_139":140,"_141":142,"_143":144,"_145":42,"_146":42,"_147":42,"_148":32,"_149":-5,"_150":-7,"_151":-7,"_152":62,"_153":42,"_154":42,"_155":-5,"_156":42,"_157":42,"_158":42,"_159":-5},"ENV",{"_110":111,"_112":113,"_114":89,"_115":116,"_117":118,"_119":120,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]