[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_105":106,"_107":108},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","jeffvincent","name","mibbs-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":58,"_59":32,"_60":-5,"_61":-5,"_62":36,"_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/jeffvincent/mibbs-bot/code/db.ts","extension","id","0f23e8de-5118-457c-bc99-dd3becaee793","fileId","0b7fd604-ac69-11f1-8efb-1607ee4eb77e","parentId","branchId","05facaa4-90f3-48b5-bf13-4bf8572bc454","branchVersion",99,"latest",true,"createdAt",["D",1788970258940],"version",0,"fileType","script","content","// Shared database layer for mibbs-bot.\n// Single source of truth for SQLite schema, migrations, and all DB operations.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst TZ = \"America/New_York\";\n\n// ── Utilities ──────────────────────────────────────────────────────────────────\n\nexport function rowsToObjects(result: { rows: any[][]; columns: string[] }): Record[] {\n return result.rows.map((row) => {\n const obj: Record = {};\n result.columns.forEach((col, i) => { obj[col] = row[i]; });\n return obj;\n });\n}\n\n// Convert a date-only string (YYYY-MM-DD) + a local hour to a UTC ISO 8601 instant.\n// Two-pass Intl adjustment handles DST transitions correctly.\nexport function localToUtcIso(dateStr: string, hour: number, tz = TZ): string {\n let utc = Date.UTC(+dateStr.slice(0, 4), +dateStr.slice(5, 7) - 1, +dateStr.slice(8, 10), hour, 0, 0);\n for (let i = 0; i < 2; i++) {\n const parts = new Intl.DateTimeFormat(\"en-US\", {\n timeZone: tz, hour12: false,\n year: \"numeric\", month: \"2-digit\", day: \"2-digit\",\n hour: \"2-digit\", minute: \"2-digit\", second: \"2-digit\",\n }).formatToParts(new Date(utc)).reduce((a, x) => { (a as any)[x.type] = x.value; return a; }, {} as Record);\n const asIfUtc = Date.UTC(+parts.year, +parts.month - 1, +parts.day, +parts.hour, +parts.minute, +parts.second);\n utc -= asIfUtc - utc;\n }\n return new Date(utc).toISOString();\n}\n\nexport async function ensureColumns(table: string, cols: Record): Promise {\n const info = await sqlite.execute(`PRAGMA table_info(${table})`);\n const existing = new Set(rowsToObjects(info).map((r) => r.name as string));\n for (const [name, def] of Object.entries(cols)) {\n if (!existing.has(name)) {\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN ${name} ${def}`);\n }\n }\n}\n\n// ── Schema ─────────────────────────────────────────────────────────────────────\n\n// Superset initDb — safe to call from any file; all statements are idempotent.\nexport async function initDb(): Promise {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fm_tasks (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n text TEXT NOT NULL,\n created_by TEXT,\n created_at TEXT NOT NULL,\n due_date TEXT,\n completed INTEGER NOT NULL DEFAULT 0,\n completed_at TEXT\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fm_info (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n key TEXT NOT NULL,\n value TEXT NOT NULL,\n created_by TEXT,\n created_at TEXT NOT NULL\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fm_messages (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n chat_id INTEGER NOT NULL,\n role TEXT NOT NULL,\n content TEXT NOT NULL,\n user_name TEXT,\n created_at TEXT NOT NULL\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fm_users (\n telegram_id INTEGER PRIMARY KEY,\n first_name TEXT,\n username TEXT,\n updated_at TEXT NOT NULL\n )`);\n // Additive migrations — safe to run against existing prod table\n await ensureColumns(\"fm_tasks\", {\n owner: \"TEXT\",\n remind_at: \"TEXT\",\n reminded: \"INTEGER NOT NULL DEFAULT 0\",\n });\n}\n\n// ── Tasks ─────────────────────────────────────────────────────────────────────\n\nexport async function addTask(text: string, createdBy: string, dueDate?: string, owner?: string): Promise {\n let remindAt: string | null = null;\n if (dueDate) {\n remindAt = dueDate.includes(\"T\") ? dueDate : localToUtcIso(dueDate, 8);\n }\n await sqlite.execute({\n sql: \"INSERT INTO fm_tasks (text, created_by, created_at, due_date, owner, remind_at, reminded) VALUES (?, ?, ?, ?, ?, ?, 0)\",\n args: [text, createdBy, new Date().toISOString(), dueDate ?? null, owner ?? null, remindAt],\n });\n}\n\nexport async function listTasks(): Promise<{ id: number; text: string; created_by: string; due_date: string | null; owner: string | null; remind_at: string | null }[]> {\n const result = await sqlite.execute(\"SELECT id, text, created_by, due_date, owner, remind_at FROM fm_tasks WHERE completed = 0 ORDER BY created_at DESC\");\n return rowsToObjects(result) as any;\n}\n\nexport async function completeTask(id: number): Promise {\n const result = await sqlite.execute({\n sql: \"UPDATE fm_tasks SET completed = 1, completed_at = ? WHERE id = ? AND completed = 0\",\n args: [new Date().toISOString(), id],\n });\n return (result.rowsAffected ?? 0) > 0;\n}\n\nexport async function updateTask(id: number, owner?: string, dueDate?: string): Promise {\n const fields: string[] = [];\n const args: any[] = [];\n if (owner !== undefined) {\n fields.push(\"owner = ?\");\n args.push(owner);\n }\n if (dueDate !== undefined) {\n const remindAt = dueDate ? (dueDate.includes(\"T\") ? dueDate : localToUtcIso(dueDate, 8)) : null;\n fields.push(\"due_date = ?\", \"remind_at = ?\", \"reminded = 0\");\n args.push(dueDate || null, remindAt);\n }\n if (!fields.length) return false;\n args.push(id);\n const result = await sqlite.execute({\n sql: `UPDATE fm_tasks SET ${fields.join(\", \")} WHERE id = ? AND completed = 0`,\n args,\n });\n return (result.rowsAffected ?? 0) > 0;\n}\n\n// ── Info ──────────────────────────────────────────────────────────────────────\n\nexport async function storeInfo(key: string, value: string, createdBy: string): Promise {\n await sqlite.execute({\n sql: \"INSERT INTO fm_info (key, value, created_by, created_at) VALUES (?, ?, ?, ?)\",\n args: [key, value, createdBy, new Date().toISOString()],\n });\n}\n\nexport async function getInfo(query?: string): Promise<{ key: string; value: string }[]> {\n let result;\n if (query) {\n result = await sqlite.execute({\n sql: \"SELECT key, value FROM fm_info WHERE key LIKE ? OR value LIKE ? ORDER BY created_at DESC\",\n args: [`%${query}%`, `%${query}%`],\n });\n } else {\n result = await sqlite.execute(\"SELECT key, value FROM fm_info ORDER BY created_at DESC\");\n }\n return rowsToObjects(result) as any;\n}\n\n// ── Messages ──────────────────────────────────────────────────────────────────\n\nexport async function saveMessage(chatId: number, role: \"user\" | \"assistant\", content: string, userName?: string): Promise {\n try {\n await sqlite.execute({\n sql: \"INSERT INTO fm_messages (chat_id, role, content, user_name, created_at) VALUES (?, ?, ?, ?, ?)\",\n args: [chatId, role, content, userName ?? null, new Date().toISOString()],\n });\n } catch (err) {\n console.error(\"saveMessage: INSERT failed:\", err);\n }\n}\n\nexport async function getRecentMessages(chatId: number, limit = 20): Promise<{ role: string; content: string }[]> {\n const result = await sqlite.execute({\n sql: \"SELECT role, content FROM fm_messages WHERE chat_id = ? ORDER BY id DESC LIMIT ?\",\n args: [chatId, limit],\n });\n return rowsToObjects(result).reverse() as any;\n}\n\n// ── Users ─────────────────────────────────────────────────────────────────────\n\nexport async function upsertUser(telegramId: number, firstName: string | null, username: string | null): Promise {\n await sqlite.execute({\n sql: `INSERT INTO fm_users (telegram_id, first_name, username, updated_at)\n VALUES (?, ?, ?, ?)\n ON CONFLICT(telegram_id) DO UPDATE SET\n first_name = excluded.first_name,\n username = excluded.username,\n updated_at = excluded.updated_at`,\n args: [telegramId, firstName, username, new Date().toISOString()],\n });\n}\n","deleted",false,"label",[14],"ea90a44b-e5c3-4abb-8fb5-1fdf1a363f60",["D",1788733325122],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","mibbs bot code","imageUrl","userId","e8e1fec0-46e7-11f0-a801-9b9c2b15cc6a","pinnedFileId","63f1ffa8-aa41-11f1-aa27-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount","orgId","7a1a376d-d8d2-47ee-b61d-1016a5b7e33d","tursoName","ea90a44be5c34abb8fb51fdf1a363f60","tursoHostname","ea90a44be5c34abb8fb51fdf1a363f60-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789522252250],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_104":100},"org",{"_9":10,"_98":-5,"_99":100,"_55":56,"_101":102,"_103":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",130,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeU41V0tQaE1XdnpOME5pR05pMVFRNkh0bUIifQ","tier","free","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_138":139,"_140":141,"_142":143,"_144":42,"_145":42,"_146":42,"_147":32,"_148":-5,"_149":-7,"_150":-7,"_151":36,"_152":42,"_153":42,"_154":-5,"_155":42,"_156":42,"_157":42,"_158":-5},"ENV",{"_109":110,"_111":112,"_113":89,"_114":115,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137},"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"]