Hi

\"\n});\n```\n\n## Val Town Utility Functions\n\nVal Town provides several utility functions to help with common project tasks.\n\n### Importing Utilities\n\nAlways import utilities with version pins to avoid breaking changes:\n\n```ts\nimport { parseProject, readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n```\n\n### Available Utilities\n\n\n#### **serveFile** - Serve project files with proper content types\n\nFor example, in Hono:\n\n```ts\n// serve all files in frontend/ and shared/\napp.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\napp.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n```\n\n#### **readFile** - Read files from within the project:\n\n```ts\n// Read a file from the project\nconst fileContent = await readFile(\"/frontend/index.html\", import.meta.url);\n```\n\n#### **listFiles** - List all files in the project\n\n```ts\nconst files = await listFiles(import.meta.url);\n```\n\n#### **parseProject** - Extract information about the current project from import.meta.url\n\nThis is useful for including info for linking back to a val, ie in \"view source\" urls:\n\n```ts\nconst projectVal = parseProject(import.meta.url);\nconsole.log(projectVal.username); // Owner of the project\nconsole.log(projectVal.name); // Project name\nconsole.log(projectVal.version); // Version number\nconsole.log(projectVal.branch); // Branch name\nconsole.log(projectVal.links.self.project); // URL to the project page\n```\n\nHowever, it's *extremely importing* to note that `parseProject` and other Standard Library utilities ONLY RUN ON THE SERVER.\nIf you need access to this data on the client, run it in the server and pass it to the client by splicing it into the HTML page\nor by making an API request for it.\n\n## Val Town Platform Specifics\n\n- **Redirects:** Use `return new Response(null, { status: 302, headers: { Location: \"/place/to/redirect\" }})` instead of `Response.redirect` which is broken\n- **Images:** Avoid external images or base64 images. Use emojis, unicode symbols, or icon fonts/libraries instead\n- **AI Image:** To inline generate an AI image use: `

`\n- **Storage:** DO NOT use the Deno KV module for storage\n- **Browser APIs:** DO NOT use the `alert()`, `prompt()`, or `confirm()` methods\n- **Weather Data:** Use open-meteo for weather data (doesn't require API keys) unless otherwise specified\n- **View Source:** Add a view source link by importing & using `import.meta.url.replace(\"ems.sh\", \"val.town)\"` (or passing this data to the client) and include `target=\"_top\"` attribute\n- **Error Debugging:** Add `` to HTML to capture client-side errors\n- **Error Handling:** Only use try...catch when there's a clear local resolution; Avoid catches that merely log or return 500s. Let errors bubble up with full context\n- **Environment Variables:** Use `Deno.env.get('keyname')` when you need to, but generally prefer APIs that don't require keys\n- **Imports:** Use `https://esm.sh` for npm and Deno dependencies to ensure compatibility on server and browser\n- **Storage Strategy:** Only use backend storage if explicitly required; prefer simple static client-side sites\n- **React Configuration:** When using React libraries, pin versions with `?deps=react@18.2.0,react-dom@18.2.0` and start the file with `/** @jsxImportSource https://esm.sh/react@18.2.0 */`\n- Ensure all React dependencies and sub-dependencies are pinned to the same version\n- **Styling:** Default to using TailwindCSS via `` unless otherwise specified\n\n## Project Structure and Design Patterns\n\n### Recommended Directory Structure\n```\n├── backend/\n│ ├── database/\n│ │ ├── migrations.ts # Schema definitions\n│ │ ├── queries.ts # DB query functions\n│ │ └── README.md\n│ └── routes/ # Route modules\n│ ├── [route].ts\n│ └── static.ts # Static file serving\n│ ├── index.ts # Main entry point\n│ └── README.md\n├── frontend/\n│ ├── components/\n│ │ ├── App.tsx\n│ │ └── [Component].tsx\n│ ├── favicon.svg\n│ ├── index.html # Main HTML template\n│ ├── index.tsx # Frontend JS entry point\n│ ├── README.md\n│ └── style.css\n├── README.md\n└── shared/\n ├── README.md\n └── utils.ts # Shared types and functions\n```\n\n### Backend (Hono) Best Practices\n\n- Hono is the recommended API framework\n- Main entry point should be `backend/index.ts`\n- **Static asset serving:** Use the utility functions to read and serve project files:\n ```ts\n import { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n \n // serve all files in frontend/ and shared/\n app.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\n app.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n \n // For index.html, often you'll want to bootstrap with initial data\n app.get(\"/\", async c => {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Inject data to avoid extra round-trips\n const initialData = await fetchInitialData();\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n });\n ```\n- Create RESTful API routes for CRUD operations\n- Always include this snippet at the top-level Hono app to re-throwing errors to see full stack traces:\n ```ts\n // Unwrap Hono errors to see original error details\n app.onError((err, c) => {\n throw err;\n });\n ```\n\n### Database Patterns\n- Run migrations on startup or comment out for performance\n- Change table names when modifying schemas rather than altering\n- Export clear query functions with proper TypeScript typing\n\n## Common Gotchas and Solutions\n\n1. **Environment Limitations:** \n - Val Town runs on Deno in a serverless context, not Node.js\n - Code in `shared/` must work in both frontend and backend environments\n - Cannot use `Deno` keyword in shared code\n - Use `https://esm.sh` for imports that work in both environments\n\n2. **SQLite Peculiarities:**\n - Limited support for ALTER TABLE operations\n - Create new tables with updated schemas and copy data when needed\n - Always run table creation before querying\n\n3. **React Configuration:**\n - All React dependencies must be pinned to 18.2.0\n - Always include `@jsxImportSource https://esm.sh/react@18.2.0` at the top of React files\n - Rendering issues often come from mismatched React versions\n\n4. **File Handling:**\n - Val Town only supports text files, not binary\n - Use the provided utilities to read files across branches and forks\n - For files in the project, use `readFile` helpers\n\n5. **API Design:**\n - `fetch` handler is the entry point for HTTP vals\n - Run the Hono app with `export default app.fetch // This is the entry point for HTTP vals`\n\n",[],[186],{"_103":194,"_96":195,"_157":196,"_159":-5,"_160":142,"_161":197,"_163":164,"_105":198,"_100":199,"_166":200,"_168":201,"_170":22,"_171":-5,"_172":202,"_99":203},"8c48ab2e-f3ac-11f0-932b-42dde27851f2","backfill.ts","5e840ac6-f3ac-11f0-b966-42dde27851f2",20,["D",1768658286190],1,"script","// Backfill script to migrate hard-coded posts to SQLite\n// Call via HTTP: curl https://jdan--595af450f34411f0a73942dde27851f2.web.val.run/backfill\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst TABLE_NAME = \"jdan_posts_1\";\n\ntype Post = {\n id: number;\n ts: number;\n user: string;\n title: string;\n mood: string;\n imageUrls?: string[];\n};\n\nconst posts: Post[] = [\n {\n id: 1,\n ts: 1768613880393,\n user: \"jdan\",\n title: \"This is a post\",\n mood: \"happy\",\n },\n {\n id: 2,\n ts: 1768613893452,\n user: \"jdan\",\n title: \"This is another post\",\n mood: \"inquisitive\",\n },\n {\n id: 3,\n ts: 1768615068795,\n user: \"jdan\",\n title: \"Here's a random number: 42\",\n mood: \"inquisitive\",\n },\n {\n id: 4,\n ts: 1768616000000,\n user: \"jdan\",\n title: \"Fourth post checking in\",\n mood: \"happy\",\n },\n {\n id: 5,\n ts: 1768617000000,\n user: \"jdan\",\n title: \"Flat Eric says hello\",\n mood: \"happy\",\n imageUrls: [\"flateric.jpg\"],\n },\n {\n id: 6,\n ts: 1768618000000,\n user: \"jdan\",\n title: \"Some great music\",\n mood: \"happy\",\n imageUrls: [\"cds.jpeg\", \"takanaka.jpeg\"],\n },\n];\n\nasync function backfill(): Promise {\n const logs: string[] = [];\n\n // Create table if not exists\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n ts INTEGER NOT NULL,\n user TEXT NOT NULL,\n title TEXT NOT NULL,\n mood TEXT NOT NULL,\n imageUrls TEXT\n )\n `);\n\n // Check if table already has data\n const existing = await sqlite.execute(`SELECT COUNT(*) as count FROM ${TABLE_NAME}`);\n const count = existing.rows[0].count as number;\n\n if (count > 0) {\n logs.push(`Table ${TABLE_NAME} already has ${count} posts. Skipping backfill.`);\n return logs;\n }\n\n // Insert posts\n for (const post of posts) {\n const imageUrlsJson = post.imageUrls ? JSON.stringify(post.imageUrls) : null;\n\n await sqlite.execute({\n sql: `INSERT INTO ${TABLE_NAME} (ts, user, title, mood, imageUrls) VALUES (?, ?, ?, ?, ?)`,\n args: [post.ts, post.user, post.title, post.mood, imageUrlsJson],\n });\n\n logs.push(`Inserted post: \"${post.title}\"`);\n }\n\n logs.push(`Successfully backfilled ${posts.length} posts.`);\n return logs;\n}\n\nexport default async function (_req: Request): Promise {\n const logs = await backfill();\n return Response.json({ logs });\n}\n",[],[195],{"_103":205,"_96":206,"_157":207,"_159":-5,"_160":142,"_161":208,"_163":164,"_105":209,"_100":32,"_166":167,"_168":210,"_170":22,"_171":-5,"_172":211,"_99":212},"6f18b24a-f34a-11f0-8332-42dde27851f2","deno.json","6f18b2ea-f34a-11f0-8332-42dde27851f2",10,["D",1768616146472],"{\n \"$schema\": \"https://raw.githubusercontent.com/denoland/deno/98f62cee78e85bfc47c62ed703777c6bc8794f1c/cli/schemas/config-file.v1.json\",\n \"lock\": false,\n \"compilerOptions\": {\n \"noImplicitAny\": false,\n \"strict\": true,\n \"types\": [\"https://www.val.town/types/valtown.d.ts\"],\n \"lib\": [\n \"dom\",\n \"dom.iterable\",\n \"dom.asynciterable\",\n \"deno.ns\",\n \"deno.unstable\"\n ]\n },\n \"lint\": {\n \"include\": [\"deno:/https/esm.town/**/*\"],\n \"rules\": {\n \"exclude\": [\"no-explicit-any\"]\n }\n },\n \"node_modules_dir\": false,\n \"experimental\": {\n \"unstable-node-globals\": true,\n \"unstable-temporal\": true,\n \"unstable-worker-options\": true,\n \"unstable-sloppy-imports\": true\n }\n}",[],[206],{"_103":214,"_96":215,"_157":216,"_159":-5,"_160":142,"_161":141,"_163":164,"_105":217,"_100":218,"_166":219,"_168":220,"_170":22,"_171":-5,"_172":221,"_99":222},"cd366caa-f683-11f0-8794-42dde27851f2","main.ts","595af450-f344-11f0-a739-42dde27851f2",["D",1768970639239],55,"http","// Learn more: https://docs.val.town/vals/http/\nimport { blob } from \"https://esm.town/v/std/blob\";\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport * as jose from \"npm:jose\";\n\nconst POSTS_TABLE = \"jdan_posts_9\";\nconst REACTIONS_TABLE = \"jdan_reactions_1\";\nconst DEVICE_TOKENS_TABLE = \"jdan_device_tokens_1\";\nconst POST_NOTIFICATIONS_TABLE = \"jdan_post_notifications_1\";\nconst POLLS_TABLE = \"jdan_polls_1\";\nconst POLL_VOTES_TABLE = \"jdan_poll_votes_1\";\nconst NOTE_VOTES_TABLE = \"jdan_note_votes_1\";\nconst EMOJI_BUTTON_CLICKS_TABLE = \"jdan_emoji_button_clicks_2\";\nconst SETTINGS_TABLE = \"jdan_settings_1\";\n\nasync function ensureTables() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${POSTS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n ts INTEGER NOT NULL,\n user TEXT NOT NULL,\n title TEXT NOT NULL,\n location TEXT,\n imageUrls TEXT,\n audioUrl TEXT,\n audioDuration REAL,\n availableEmoji TEXT,\n communityNotes TEXT,\n archived INTEGER DEFAULT 0,\n emojiButtons TEXT,\n puzzleWord TEXT\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${EMOJI_BUTTON_CLICKS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n post_id INTEGER NOT NULL,\n button_index INTEGER NOT NULL,\n device_id TEXT NOT NULL,\n created_at INTEGER NOT NULL\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${REACTIONS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n post_id INTEGER NOT NULL,\n emoji TEXT NOT NULL,\n device_id TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n UNIQUE(post_id, emoji, device_id)\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${DEVICE_TOKENS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT NOT NULL UNIQUE,\n push_token TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n updated_at INTEGER NOT NULL\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${POST_NOTIFICATIONS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n post_id INTEGER NOT NULL UNIQUE,\n notified_at INTEGER NOT NULL\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${POLLS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n post_id INTEGER NOT NULL UNIQUE,\n options TEXT NOT NULL,\n created_at INTEGER NOT NULL\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${POLL_VOTES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n poll_id INTEGER NOT NULL,\n option_index INTEGER NOT NULL,\n device_id TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n UNIQUE(poll_id, device_id)\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${NOTE_VOTES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n post_id INTEGER NOT NULL,\n vote_type TEXT NOT NULL,\n device_id TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n UNIQUE(post_id, device_id)\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${SETTINGS_TABLE} (\n key TEXT PRIMARY KEY,\n value TEXT NOT NULL\n )\n `);\n}\n\nasync function getPuzzleMode(): Promise {\n await ensureTables();\n const result = await sqlite.execute(\n `SELECT value FROM ${SETTINGS_TABLE} WHERE key = 'puzzle_mode'`\n );\n if (result.rows.length > 0) {\n return result.rows[0].value === \"true\";\n }\n return false; // Default to off\n}\n\nasync function setPuzzleMode(enabled: boolean): Promise {\n await ensureTables();\n await sqlite.execute(\n `INSERT OR REPLACE INTO ${SETTINGS_TABLE} (key, value) VALUES ('puzzle_mode', ?)`,\n [enabled ? \"true\" : \"false\"]\n );\n}\n\ntype ReactionInfo = {\n count: number;\n hasVoted: boolean;\n};\n\ntype NoteVoteInfo = {\n upCount: number;\n downCount: number;\n hasVoted: boolean;\n userVote?: \"up\" | \"down\";\n};\n\ntype PollOption = {\n index: number;\n text: string;\n voteCount: number;\n percentage: number;\n};\n\ntype PollInfo = {\n id: number;\n options: PollOption[];\n totalVotes: number;\n hasVoted: boolean;\n votedOptionIndex?: number;\n};\n\ntype EmojiButtonColor = \"red\" | \"orange\" | \"yellow\" | \"green\" | \"blue\" | \"purple\" | \"pink\" | \"gray\";\n\ntype EmojiButtonConfig = {\n emoji: string;\n calloutMessage: string;\n color: EmojiButtonColor;\n};\n\ntype EmojiButtonInfo = EmojiButtonConfig & {\n index: number;\n clickCount: number;\n hasClicked: boolean;\n};\n\ntype Post = {\n id: number;\n ts: number;\n user: string;\n title: string;\n location?: string;\n imageUrls?: string[];\n audioUrl?: string;\n audioDuration?: number;\n availableEmoji?: string[];\n communityNotes?: string;\n reactions?: Record;\n poll?: PollInfo;\n noteVotes?: NoteVoteInfo;\n emojiButtons?: EmojiButtonInfo[];\n puzzleWord?: string;\n};\n\nasync function serveImage(key: string): Promise {\n const data = await blob.get(key);\n if (!data) {\n return new Response(\"Image not found\", { status: 404 });\n }\n\n const extension = key.split(\".\").pop()?.toLowerCase();\n const contentType = extension === \"png\" ? \"image/png\" : \"image/jpeg\";\n\n const arrayBuffer = await data.arrayBuffer();\n return new Response(arrayBuffer, {\n headers: {\n \"Content-Type\": contentType,\n \"Cache-Control\": \"public, max-age=3600\",\n },\n });\n}\n\nasync function serveAudio(key: string, req: Request): Promise {\n const data = await blob.get(key);\n if (!data) {\n return new Response(\"Audio not found\", { status: 404 });\n }\n\n const extension = key.split(\".\").pop()?.toLowerCase();\n let contentType = \"audio/mp4\"; // default for m4a\n if (extension === \"m4a\") {\n contentType = \"audio/mp4\";\n } else if (extension === \"webm\") {\n contentType = \"audio/webm\";\n } else if (extension === \"mp3\") {\n contentType = \"audio/mpeg\";\n } else if (extension === \"aac\") {\n contentType = \"audio/aac\";\n }\n\n const arrayBuffer = await data.arrayBuffer();\n const totalSize = arrayBuffer.byteLength;\n\n // Handle Range requests for iOS AVPlayer compatibility\n const rangeHeader = req.headers.get(\"Range\");\n if (rangeHeader) {\n const match = rangeHeader.match(/bytes=(\\d+)-(\\d*)/);\n if (match) {\n const start = parseInt(match[1], 10);\n const end = match[2] ? parseInt(match[2], 10) : totalSize - 1;\n const chunkSize = end - start + 1;\n\n return new Response(arrayBuffer.slice(start, end + 1), {\n status: 206,\n headers: {\n \"Content-Type\": contentType,\n \"Content-Range\": `bytes ${start}-${end}/${totalSize}`,\n \"Content-Length\": chunkSize.toString(),\n \"Accept-Ranges\": \"bytes\",\n \"Cache-Control\": \"public, max-age=3600\",\n },\n });\n }\n }\n\n // Full response\n return new Response(arrayBuffer, {\n headers: {\n \"Content-Type\": contentType,\n \"Content-Length\": totalSize.toString(),\n \"Accept-Ranges\": \"bytes\",\n \"Cache-Control\": \"public, max-age=3600\",\n },\n });\n}\n\nasync function uploadImage(req: Request): Promise {\n const { key, data } = await req.json();\n if (!key || !data) {\n return new Response(\"Missing key or data\", { status: 400 });\n }\n\n const binaryData = Uint8Array.from(atob(data.trim()), (c) => c.charCodeAt(0));\n await blob.set(key, binaryData);\n\n return Response.json({ success: true, key });\n}\n\nasync function createPost(req: Request): Promise {\n const body = await req.json();\n const { user, title, location, imageUrls, audioUrl, audioDuration, availableEmoji, communityNotes, pollOptions, emojiButtons, puzzleWord } = body;\n\n if (!user || !title) {\n return new Response(\"Missing required fields: user, title\", { status: 400 });\n }\n\n // Validate poll options if provided\n if (pollOptions) {\n if (!Array.isArray(pollOptions) || pollOptions.length < 2 || pollOptions.length > 4) {\n return new Response(\"Poll must have 2-4 options\", { status: 400 });\n }\n if (pollOptions.some((opt: unknown) => typeof opt !== \"string\" || !opt)) {\n return new Response(\"Poll options must be non-empty strings\", { status: 400 });\n }\n }\n\n // Validate puzzleWord if provided (must be exactly 5 alphabetic characters)\n let normalizedPuzzleWord: string | null = null;\n if (puzzleWord) {\n const word = puzzleWord.trim().toUpperCase();\n if (!/^[A-Z]{5}$/.test(word)) {\n return new Response(\"Puzzle word must be exactly 5 alphabetic characters\", { status: 400 });\n }\n normalizedPuzzleWord = word;\n }\n\n await ensureTables();\n\n const ts = Date.now();\n const imageUrlsJson = imageUrls ? JSON.stringify(imageUrls) : null;\n const availableEmojiJson = availableEmoji ? JSON.stringify(availableEmoji) : null;\n const emojiButtonsJson = emojiButtons ? JSON.stringify(emojiButtons) : null;\n\n const result = await sqlite.execute({\n sql: `INSERT INTO ${POSTS_TABLE} (ts, user, title, location, imageUrls, audioUrl, audioDuration, availableEmoji, communityNotes, emojiButtons, puzzleWord) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [ts, user, title, location || null, imageUrlsJson, audioUrl || null, audioDuration || null, availableEmojiJson, communityNotes || null, emojiButtonsJson, normalizedPuzzleWord],\n });\n\n const postId = Number(result.lastInsertRowid);\n\n // Create poll if options provided\n let poll: PollInfo | undefined;\n if (pollOptions && pollOptions.length >= 2) {\n await sqlite.execute({\n sql: `INSERT INTO ${POLLS_TABLE} (post_id, options, created_at) VALUES (?, ?, ?)`,\n args: [postId, JSON.stringify(pollOptions), ts],\n });\n\n // Fetch the created poll\n poll = await getPollForPost(postId) ?? undefined;\n }\n\n // Build emoji buttons info if provided\n let emojiButtonsInfo: EmojiButtonInfo[] | undefined;\n if (emojiButtons && Array.isArray(emojiButtons)) {\n emojiButtonsInfo = emojiButtons.map((config: EmojiButtonConfig, index: number) => ({\n ...config,\n index,\n clickCount: 0,\n hasClicked: false,\n }));\n }\n\n const post: Post = {\n id: postId,\n ts,\n user,\n title,\n ...(location && { location }),\n ...(imageUrls && { imageUrls }),\n ...(audioUrl && { audioUrl }),\n ...(audioDuration && { audioDuration }),\n ...(availableEmoji && { availableEmoji }),\n ...(communityNotes && { communityNotes }),\n ...(poll && { poll }),\n ...(emojiButtonsInfo && { emojiButtons: emojiButtonsInfo }),\n ...(normalizedPuzzleWord && { puzzleWord: normalizedPuzzleWord }),\n };\n\n return Response.json(post, { status: 201 });\n}\n\nasync function getReactionsForPost(postId: number, deviceId?: string): Promise> {\n const result = await sqlite.execute({\n sql: `SELECT emoji, COUNT(*) as count FROM ${REACTIONS_TABLE} WHERE post_id = ? GROUP BY emoji`,\n args: [postId],\n });\n\n const reactions: Record = {};\n\n for (const row of result.rows) {\n const emoji = row.emoji as string;\n const count = row.count as number;\n let hasVoted = false;\n\n if (deviceId) {\n const voteCheck = await sqlite.execute({\n sql: `SELECT 1 FROM ${REACTIONS_TABLE} WHERE post_id = ? AND emoji = ? AND device_id = ?`,\n args: [postId, emoji, deviceId],\n });\n hasVoted = voteCheck.rows.length > 0;\n }\n\n reactions[emoji] = { count, hasVoted };\n }\n\n return reactions;\n}\n\nasync function getPollForPost(postId: number, deviceId?: string): Promise {\n const pollResult = await sqlite.execute({\n sql: `SELECT id, options FROM ${POLLS_TABLE} WHERE post_id = ?`,\n args: [postId],\n });\n\n if (pollResult.rows.length === 0) {\n return null;\n }\n\n const pollRow = pollResult.rows[0];\n const pollId = pollRow.id as number;\n const optionTexts = JSON.parse(pollRow.options as string) as string[];\n\n // Get vote counts for each option\n const votesResult = await sqlite.execute({\n sql: `SELECT option_index, COUNT(*) as count FROM ${POLL_VOTES_TABLE} WHERE poll_id = ? GROUP BY option_index`,\n args: [pollId],\n });\n\n const voteCounts: Record = {};\n let totalVotes = 0;\n for (const row of votesResult.rows) {\n const optionIndex = row.option_index as number;\n const count = row.count as number;\n voteCounts[optionIndex] = count;\n totalVotes += count;\n }\n\n // Check if this device has voted\n let hasVoted = false;\n let votedOptionIndex: number | undefined;\n\n if (deviceId) {\n const voteCheck = await sqlite.execute({\n sql: `SELECT option_index FROM ${POLL_VOTES_TABLE} WHERE poll_id = ? AND device_id = ?`,\n args: [pollId, deviceId],\n });\n if (voteCheck.rows.length > 0) {\n hasVoted = true;\n votedOptionIndex = voteCheck.rows[0].option_index as number;\n }\n }\n\n // Build options array with percentages\n const options: PollOption[] = optionTexts.map((text, index) => {\n const voteCount = voteCounts[index] || 0;\n const percentage = totalVotes > 0 ? Math.round((voteCount / totalVotes) * 100) : 0;\n return { index, text, voteCount, percentage };\n });\n\n return {\n id: pollId,\n options,\n totalVotes,\n hasVoted,\n ...(votedOptionIndex !== undefined && { votedOptionIndex }),\n };\n}\n\nasync function getNoteVotesForPost(postId: number, deviceId?: string): Promise {\n // Get vote counts\n const upResult = await sqlite.execute({\n sql: `SELECT COUNT(*) as count FROM ${NOTE_VOTES_TABLE} WHERE post_id = ? AND vote_type = 'up'`,\n args: [postId],\n });\n const downResult = await sqlite.execute({\n sql: `SELECT COUNT(*) as count FROM ${NOTE_VOTES_TABLE} WHERE post_id = ? AND vote_type = 'down'`,\n args: [postId],\n });\n\n const upCount = upResult.rows[0].count as number;\n const downCount = downResult.rows[0].count as number;\n\n // Check if this device has voted\n let hasVoted = false;\n let userVote: \"up\" | \"down\" | undefined;\n\n if (deviceId) {\n const voteCheck = await sqlite.execute({\n sql: `SELECT vote_type FROM ${NOTE_VOTES_TABLE} WHERE post_id = ? AND device_id = ?`,\n args: [postId, deviceId],\n });\n if (voteCheck.rows.length > 0) {\n hasVoted = true;\n userVote = voteCheck.rows[0].vote_type as \"up\" | \"down\";\n }\n }\n\n return {\n upCount,\n downCount,\n hasVoted,\n ...(userVote && { userVote }),\n };\n}\n\nasync function getEmojiButtonsForPost(postId: number, emojiButtonsConfig: EmojiButtonConfig[], _deviceId?: string): Promise {\n // Get click counts for each button\n const clickCountsResult = await sqlite.execute({\n sql: `SELECT button_index, COUNT(*) as count FROM ${EMOJI_BUTTON_CLICKS_TABLE} WHERE post_id = ? GROUP BY button_index`,\n args: [postId],\n });\n\n const clickCounts: Record = {};\n for (const row of clickCountsResult.rows) {\n clickCounts[row.button_index as number] = row.count as number;\n }\n\n // hasClicked is always false since users can click multiple times (no toggle)\n return emojiButtonsConfig.map((config, index) => ({\n ...config,\n index,\n clickCount: clickCounts[index] || 0,\n hasClicked: false,\n }));\n}\n\nasync function getPosts(baseUrl: string, deviceId?: string): Promise {\n await ensureTables();\n\n // Filter by puzzle mode setting\n const puzzleModeEnabled = await getPuzzleMode();\n let whereClause = \"archived = 0\";\n if (puzzleModeEnabled) {\n whereClause += \" AND puzzleWord IS NOT NULL\";\n } else {\n whereClause += \" AND puzzleWord IS NULL\";\n }\n\n const result = await sqlite.execute(`SELECT * FROM ${POSTS_TABLE} WHERE ${whereClause} ORDER BY ts DESC`);\n\n const posts: Post[] = [];\n for (const row of result.rows) {\n const post: Post = {\n id: row.id as number,\n ts: row.ts as number,\n user: row.user as string,\n title: row.title as string,\n ...(row.location && { location: row.location as string }),\n };\n\n if (row.imageUrls) {\n const imageKeys = JSON.parse(row.imageUrls as string) as string[];\n post.imageUrls = imageKeys.map((key) => `${baseUrl}/image/${key}`);\n }\n\n if (row.audioUrl) {\n post.audioUrl = `${baseUrl}/audio/${row.audioUrl as string}`;\n if (row.audioDuration) {\n post.audioDuration = row.audioDuration as number;\n }\n }\n\n if (row.availableEmoji) {\n post.availableEmoji = JSON.parse(row.availableEmoji as string) as string[];\n }\n\n if (row.communityNotes) {\n post.communityNotes = row.communityNotes as string;\n }\n\n if (row.puzzleWord) {\n post.puzzleWord = row.puzzleWord as string;\n }\n\n // Get reactions for this post\n post.reactions = await getReactionsForPost(post.id, deviceId);\n\n // Get poll for this post\n const poll = await getPollForPost(post.id, deviceId);\n if (poll) {\n post.poll = poll;\n }\n\n // Get note votes for posts with community notes\n if (post.communityNotes) {\n post.noteVotes = await getNoteVotesForPost(post.id, deviceId);\n }\n\n // Get emoji buttons for this post\n if (row.emojiButtons) {\n const emojiButtonsConfig = JSON.parse(row.emojiButtons as string) as EmojiButtonConfig[];\n post.emojiButtons = await getEmojiButtonsForPost(post.id, emojiButtonsConfig, deviceId);\n }\n\n posts.push(post);\n }\n\n return posts;\n}\n\n// Returns posts with raw imageUrls/audioUrl (keys only, not full URLs) for admin\ntype AdminPost = Post & { notified?: boolean; archived?: boolean };\n\nasync function getPostsRaw(): Promise {\n await ensureTables();\n\n // Batch all queries upfront to avoid N+1\n const [postsResult, notifiedResult, pollsResult, pollVotesResult] = await Promise.all([\n sqlite.execute(`SELECT * FROM ${POSTS_TABLE} ORDER BY ts DESC`),\n sqlite.execute(`SELECT post_id FROM ${POST_NOTIFICATIONS_TABLE}`),\n sqlite.execute(`SELECT id, post_id, options FROM ${POLLS_TABLE}`),\n sqlite.execute(`SELECT poll_id, option_index, COUNT(*) as count FROM ${POLL_VOTES_TABLE} GROUP BY poll_id, option_index`),\n ]);\n\n const notifiedPostIds = new Set(notifiedResult.rows.map((r) => r.post_id as number));\n\n // Build poll vote counts map: pollId -> { optionIndex -> count }\n const pollVoteCounts: Record> = {};\n for (const row of pollVotesResult.rows) {\n const pollId = row.poll_id as number;\n const optionIndex = row.option_index as number;\n const count = row.count as number;\n if (!pollVoteCounts[pollId]) pollVoteCounts[pollId] = {};\n pollVoteCounts[pollId][optionIndex] = count;\n }\n\n // Build polls map: postId -> PollInfo\n const pollsByPostId: Record = {};\n for (const row of pollsResult.rows) {\n const pollId = row.id as number;\n const postId = row.post_id as number;\n const optionTexts = JSON.parse(row.options as string) as string[];\n const voteCounts = pollVoteCounts[pollId] || {};\n\n let totalVotes = 0;\n for (const count of Object.values(voteCounts)) {\n totalVotes += count;\n }\n\n const options: PollOption[] = optionTexts.map((text, index) => {\n const voteCount = voteCounts[index] || 0;\n const percentage = totalVotes > 0 ? Math.round((voteCount / totalVotes) * 100) : 0;\n return { index, text, voteCount, percentage };\n });\n\n pollsByPostId[postId] = {\n id: pollId,\n options,\n totalVotes,\n hasVoted: false,\n };\n }\n\n const posts: AdminPost[] = [];\n\n for (const row of postsResult.rows) {\n const postId = row.id as number;\n const post: AdminPost = {\n id: postId,\n ts: row.ts as number,\n user: row.user as string,\n title: row.title as string,\n notified: notifiedPostIds.has(postId),\n archived: (row.archived as number) === 1,\n ...(row.location && { location: row.location as string }),\n ...(row.audioUrl && { audioUrl: row.audioUrl as string }),\n ...(row.audioDuration && { audioDuration: row.audioDuration as number }),\n };\n\n if (row.imageUrls) {\n post.imageUrls = JSON.parse(row.imageUrls as string) as string[];\n }\n\n if (row.availableEmoji) {\n post.availableEmoji = JSON.parse(row.availableEmoji as string) as string[];\n }\n\n if (row.communityNotes) {\n post.communityNotes = row.communityNotes as string;\n }\n\n if (row.puzzleWord) {\n post.puzzleWord = row.puzzleWord as string;\n }\n\n if (pollsByPostId[postId]) {\n post.poll = pollsByPostId[postId];\n }\n\n // For admin, we return the raw config as emojiButtons\n if (row.emojiButtons) {\n const emojiButtonsConfig = JSON.parse(row.emojiButtons as string) as EmojiButtonConfig[];\n // Convert to EmojiButtonInfo format for admin display\n post.emojiButtons = emojiButtonsConfig.map((config, index) => ({\n ...config,\n index,\n clickCount: 0, // Admin doesn't need actual click counts\n hasClicked: false,\n }));\n }\n\n posts.push(post);\n }\n\n return posts;\n}\n\nasync function updatePost(id: number, req: Request): Promise {\n const body = await req.json();\n const { user, title, location, imageUrls, audioUrl, audioDuration, availableEmoji, communityNotes, pollOptions, emojiButtons, puzzleWord } = body;\n\n if (!user || !title) {\n return new Response(\"Missing required fields: user, title\", { status: 400 });\n }\n\n // Validate poll options if provided\n if (pollOptions) {\n if (!Array.isArray(pollOptions) || pollOptions.length < 2 || pollOptions.length > 4) {\n return new Response(\"Poll must have 2-4 options\", { status: 400 });\n }\n if (pollOptions.some((opt: unknown) => typeof opt !== \"string\" || !opt)) {\n return new Response(\"Poll options must be non-empty strings\", { status: 400 });\n }\n }\n\n // Validate puzzleWord if provided (must be exactly 5 alphabetic characters)\n let normalizedPuzzleWord: string | null = null;\n if (puzzleWord) {\n const word = puzzleWord.trim().toUpperCase();\n if (!/^[A-Z]{5}$/.test(word)) {\n return new Response(\"Puzzle word must be exactly 5 alphabetic characters\", { status: 400 });\n }\n normalizedPuzzleWord = word;\n }\n\n await ensureTables();\n\n const imageUrlsJson = imageUrls ? JSON.stringify(imageUrls) : null;\n const availableEmojiJson = availableEmoji ? JSON.stringify(availableEmoji) : null;\n const emojiButtonsJson = emojiButtons ? JSON.stringify(emojiButtons) : null;\n\n const result = await sqlite.execute({\n sql: `UPDATE ${POSTS_TABLE} SET user = ?, title = ?, location = ?, imageUrls = ?, audioUrl = ?, audioDuration = ?, availableEmoji = ?, communityNotes = ?, emojiButtons = ?, puzzleWord = ? WHERE id = ?`,\n args: [user, title, location || null, imageUrlsJson, audioUrl || null, audioDuration || null, availableEmojiJson, communityNotes || null, emojiButtonsJson, normalizedPuzzleWord, id],\n });\n\n if (result.rowsAffected === 0) {\n return new Response(\"Post not found\", { status: 404 });\n }\n\n // Handle poll updates\n const existingPoll = await sqlite.execute({\n sql: `SELECT id, options FROM ${POLLS_TABLE} WHERE post_id = ?`,\n args: [id],\n });\n\n if (pollOptions && pollOptions.length >= 2) {\n // Create or update poll\n if (existingPoll.rows.length > 0) {\n const pollId = existingPoll.rows[0].id as number;\n const existingOptions = existingPoll.rows[0].options as string;\n\n // Update options without clearing votes (allows fixing typos, etc.)\n if (existingOptions !== JSON.stringify(pollOptions)) {\n await sqlite.execute({\n sql: `UPDATE ${POLLS_TABLE} SET options = ? WHERE id = ?`,\n args: [JSON.stringify(pollOptions), pollId],\n });\n }\n } else {\n // Create new poll\n await sqlite.execute({\n sql: `INSERT INTO ${POLLS_TABLE} (post_id, options, created_at) VALUES (?, ?, ?)`,\n args: [id, JSON.stringify(pollOptions), Date.now()],\n });\n }\n } else if (existingPoll.rows.length > 0) {\n // Remove poll if pollOptions is empty/null\n const pollId = existingPoll.rows[0].id as number;\n await sqlite.execute({\n sql: `DELETE FROM ${POLL_VOTES_TABLE} WHERE poll_id = ?`,\n args: [pollId],\n });\n await sqlite.execute({\n sql: `DELETE FROM ${POLLS_TABLE} WHERE id = ?`,\n args: [pollId],\n });\n }\n\n return Response.json({ success: true, id });\n}\n\nasync function toggleArchivePost(id: number): Promise {\n await ensureTables();\n\n // Get current archive status\n const current = await sqlite.execute({\n sql: `SELECT archived FROM ${POSTS_TABLE} WHERE id = ?`,\n args: [id],\n });\n\n if (current.rows.length === 0) {\n return new Response(\"Post not found\", { status: 404 });\n }\n\n const currentArchived = (current.rows[0].archived as number) === 1;\n const newArchived = currentArchived ? 0 : 1;\n\n await sqlite.execute({\n sql: `UPDATE ${POSTS_TABLE} SET archived = ? WHERE id = ?`,\n args: [newArchived, id],\n });\n\n return Response.json({\n success: true,\n id,\n archived: newArchived === 1,\n action: newArchived === 1 ? \"archived\" : \"unarchived\",\n });\n}\n\nasync function votePoll(req: Request): Promise {\n const body = await req.json();\n const { poll_id, option_index, device_id } = body;\n\n if (poll_id === undefined || option_index === undefined || !device_id) {\n return new Response(\"Missing required fields: poll_id, option_index, device_id\", { status: 400 });\n }\n\n await ensureTables();\n\n // Verify poll exists and get options\n const pollResult = await sqlite.execute({\n sql: `SELECT options, post_id FROM ${POLLS_TABLE} WHERE id = ?`,\n args: [poll_id],\n });\n\n if (pollResult.rows.length === 0) {\n return new Response(\"Poll not found\", { status: 404 });\n }\n\n const options = JSON.parse(pollResult.rows[0].options as string) as string[];\n const postId = pollResult.rows[0].post_id as number;\n\n // Validate option_index\n if (option_index < 0 || option_index >= options.length) {\n return new Response(\"Invalid option_index\", { status: 400 });\n }\n\n // Check if already voted\n const existingVote = await sqlite.execute({\n sql: `SELECT id FROM ${POLL_VOTES_TABLE} WHERE poll_id = ? AND device_id = ?`,\n args: [poll_id, device_id],\n });\n\n if (existingVote.rows.length > 0) {\n return new Response(\"Already voted on this poll\", { status: 400 });\n }\n\n // Record the vote\n await sqlite.execute({\n sql: `INSERT INTO ${POLL_VOTES_TABLE} (poll_id, option_index, device_id, created_at) VALUES (?, ?, ?, ?)`,\n args: [poll_id, option_index, device_id, Date.now()],\n });\n\n // Get updated poll info\n const poll = await getPollForPost(postId, device_id);\n\n return Response.json({ success: true, poll });\n}\n\nasync function voteNote(req: Request): Promise {\n const body = await req.json();\n const { post_id, vote_type, device_id } = body;\n\n if (!post_id || !vote_type || !device_id) {\n return new Response(\"Missing required fields: post_id, vote_type, device_id\", { status: 400 });\n }\n\n if (vote_type !== \"up\" && vote_type !== \"down\") {\n return new Response(\"vote_type must be 'up' or 'down'\", { status: 400 });\n }\n\n await ensureTables();\n\n // Verify post exists and has community notes\n const postResult = await sqlite.execute({\n sql: `SELECT communityNotes FROM ${POSTS_TABLE} WHERE id = ?`,\n args: [post_id],\n });\n\n if (postResult.rows.length === 0) {\n return new Response(\"Post not found\", { status: 404 });\n }\n\n if (!postResult.rows[0].communityNotes) {\n return new Response(\"Post does not have community notes\", { status: 400 });\n }\n\n // Check if already voted\n const existingVote = await sqlite.execute({\n sql: `SELECT id FROM ${NOTE_VOTES_TABLE} WHERE post_id = ? AND device_id = ?`,\n args: [post_id, device_id],\n });\n\n if (existingVote.rows.length > 0) {\n return new Response(\"Already voted on this note\", { status: 400 });\n }\n\n // Record the vote\n await sqlite.execute({\n sql: `INSERT INTO ${NOTE_VOTES_TABLE} (post_id, vote_type, device_id, created_at) VALUES (?, ?, ?, ?)`,\n args: [post_id, vote_type, device_id, Date.now()],\n });\n\n // Get updated note votes\n const noteVotes = await getNoteVotesForPost(post_id, device_id);\n\n return Response.json({ success: true, noteVotes });\n}\n\nasync function toggleReaction(req: Request, baseUrl: string): Promise {\n const body = await req.json();\n const { post_id, emoji, device_id } = body;\n\n if (!post_id || !emoji || !device_id) {\n return new Response(\"Missing required fields: post_id, emoji, device_id\", { status: 400 });\n }\n\n await ensureTables();\n\n // Check if the reaction already exists\n const existing = await sqlite.execute({\n sql: `SELECT id FROM ${REACTIONS_TABLE} WHERE post_id = ? AND emoji = ? AND device_id = ?`,\n args: [post_id, emoji, device_id],\n });\n\n let action: \"added\" | \"removed\";\n\n if (existing.rows.length > 0) {\n // Remove the reaction\n await sqlite.execute({\n sql: `DELETE FROM ${REACTIONS_TABLE} WHERE post_id = ? AND emoji = ? AND device_id = ?`,\n args: [post_id, emoji, device_id],\n });\n action = \"removed\";\n } else {\n // Add the reaction\n await sqlite.execute({\n sql: `INSERT INTO ${REACTIONS_TABLE} (post_id, emoji, device_id, created_at) VALUES (?, ?, ?, ?)`,\n args: [post_id, emoji, device_id, Date.now()],\n });\n action = \"added\";\n }\n\n // Get updated reactions for this post\n const reactions = await getReactionsForPost(post_id, device_id);\n\n return Response.json({ success: true, action, reactions });\n}\n\nasync function clickEmojiButton(req: Request): Promise {\n const body = await req.json();\n const { post_id, button_index, device_id, count = 1 } = body;\n\n if (post_id === undefined || button_index === undefined || !device_id) {\n return new Response(\"Missing required fields: post_id, button_index, device_id\", { status: 400 });\n }\n\n const clickCount = Math.min(Math.max(1, Math.floor(count)), 100); // Clamp between 1-100\n\n await ensureTables();\n\n // Get the post to verify it exists and has emoji buttons\n const postResult = await sqlite.execute({\n sql: `SELECT emojiButtons FROM ${POSTS_TABLE} WHERE id = ?`,\n args: [post_id],\n });\n\n if (postResult.rows.length === 0) {\n return new Response(\"Post not found\", { status: 404 });\n }\n\n const emojiButtonsJson = postResult.rows[0].emojiButtons as string | null;\n if (!emojiButtonsJson) {\n return new Response(\"Post has no emoji buttons\", { status: 400 });\n }\n\n const emojiButtonsConfig = JSON.parse(emojiButtonsJson) as EmojiButtonConfig[];\n if (button_index < 0 || button_index >= emojiButtonsConfig.length) {\n return new Response(\"Invalid button_index\", { status: 400 });\n }\n\n // Insert multiple clicks in a batch\n const now = Date.now();\n const insertStatements = Array.from({ length: clickCount }, () => ({\n sql: `INSERT INTO ${EMOJI_BUTTON_CLICKS_TABLE} (post_id, button_index, device_id, created_at) VALUES (?, ?, ?, ?)`,\n args: [post_id, button_index, device_id, now],\n }));\n\n await sqlite.batch(insertStatements);\n\n // Get updated emoji buttons for this post\n const emojiButtons = await getEmojiButtonsForPost(post_id, emojiButtonsConfig, device_id);\n\n return Response.json({ success: true, action: \"clicked\", count: clickCount, emojiButtons });\n}\n\n// Push notification functions\nasync function registerPushToken(req: Request): Promise {\n const body = await req.json();\n const { device_id, push_token } = body;\n\n if (!device_id || !push_token) {\n return new Response(\"Missing required fields: device_id, push_token\", { status: 400 });\n }\n\n await ensureTables();\n\n const now = Date.now();\n\n // Insert or update the device token\n await sqlite.execute({\n sql: `INSERT INTO ${DEVICE_TOKENS_TABLE} (device_id, push_token, created_at, updated_at)\n VALUES (?, ?, ?, ?)\n ON CONFLICT(device_id) DO UPDATE SET push_token = ?, updated_at = ?`,\n args: [device_id, push_token, now, now, push_token, now],\n });\n\n return Response.json({ success: true });\n}\n\nasync function generateApnsJwt(): Promise {\n const teamId = Deno.env.get(\"APNS_TEAM_ID\");\n const keyId = Deno.env.get(\"APNS_KEY_ID\");\n const privateKeyPem = Deno.env.get(\"APNS_PRIVATE_KEY\");\n\n if (!teamId || !keyId || !privateKeyPem) {\n throw new Error(\"Missing APNs credentials in environment variables\");\n }\n\n // Import the private key\n const privateKey = await jose.importPKCS8(privateKeyPem, \"ES256\");\n\n // Create JWT\n const jwt = await new jose.SignJWT({})\n .setProtectedHeader({ alg: \"ES256\", kid: keyId })\n .setIssuer(teamId)\n .setIssuedAt()\n .sign(privateKey);\n\n return jwt;\n}\n\nasync function sendPushNotification(\n deviceToken: string,\n title: string,\n body: string\n): Promise<{ success: boolean; error?: string }> {\n const bundleId = Deno.env.get(\"APNS_BUNDLE_ID\");\n\n if (!bundleId) {\n return { success: false, error: \"Missing APNS_BUNDLE_ID\" };\n }\n\n try {\n const jwt = await generateApnsJwt();\n\n const payload = {\n aps: {\n alert: {\n title,\n body,\n },\n sound: \"default\",\n },\n };\n\n // Use sandbox for development builds, production for release\n const apnsHost = Deno.env.get(\"APNS_USE_SANDBOX\") === \"true\"\n ? \"api.sandbox.push.apple.com\"\n : \"api.push.apple.com\";\n\n const response = await fetch(\n `https://${apnsHost}/3/device/${deviceToken}`,\n {\n method: \"POST\",\n headers: {\n Authorization: `bearer ${jwt}`,\n \"apns-topic\": bundleId,\n \"apns-push-type\": \"alert\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify(payload),\n }\n );\n\n if (!response.ok) {\n const errorText = await response.text();\n console.error(`APNs error for token ${deviceToken.substring(0, 10)}...: ${response.status} - ${errorText}`);\n return { success: false, error: `APNs error: ${response.status} - ${errorText}` };\n }\n\n console.log(`Push sent successfully to ${deviceToken.substring(0, 10)}...`);\n return { success: true };\n } catch (error) {\n console.error(`Failed to send push to ${deviceToken.substring(0, 10)}...: ${error}`);\n return { success: false, error: `Failed to send push: ${error}` };\n }\n}\n\nfunction formatDuration(seconds: number): string {\n const mins = Math.floor(seconds / 60);\n const secs = Math.floor(seconds % 60);\n return `${mins}:${secs.toString().padStart(2, \"0\")}`;\n}\n\nfunction generateWaveform(): string {\n const bars = [\"▁\", \"▂\", \"▃\", \"▄\", \"▅\", \"▆\", \"▇\"];\n let waveform = \"\";\n for (let i = 0; i < 8; i++) {\n waveform += bars[Math.floor(Math.random() * bars.length)];\n }\n return waveform;\n}\n\nasync function sendNotificationsForPost(postId: number): Promise {\n await ensureTables();\n\n // Check if already notified\n const existing = await sqlite.execute({\n sql: `SELECT 1 FROM ${POST_NOTIFICATIONS_TABLE} WHERE post_id = ?`,\n args: [postId],\n });\n\n if (existing.rows.length > 0) {\n return Response.json({ success: false, error: \"Post already notified\" }, { status: 400 });\n }\n\n // Get the post\n const postResult = await sqlite.execute({\n sql: `SELECT user, title, audioUrl, audioDuration FROM ${POSTS_TABLE} WHERE id = ?`,\n args: [postId],\n });\n\n if (postResult.rows.length === 0) {\n return Response.json({ success: false, error: \"Post not found\" }, { status: 404 });\n }\n\n const post = postResult.rows[0];\n const user = post.user as string;\n const title = post.title as string;\n const audioUrl = post.audioUrl as string | null;\n const audioDuration = post.audioDuration as number | null;\n\n // Check if post has a poll\n const pollResult = await sqlite.execute({\n sql: `SELECT options FROM ${POLLS_TABLE} WHERE post_id = ?`,\n args: [postId],\n });\n const hasPoll = pollResult.rows.length > 0;\n let pollOptions: string[] = [];\n if (hasPoll) {\n pollOptions = JSON.parse(pollResult.rows[0].options as string) as string[];\n }\n\n // Build notification body - add audio waveform if present, add poll info if present\n let notificationBody = title;\n if (audioUrl && audioDuration) {\n const waveform = generateWaveform();\n const duration = formatDuration(audioDuration);\n notificationBody = `${waveform} ${duration}\\n${title}`;\n }\n if (hasPoll) {\n const pollText = `📊 ${pollOptions.join(\" · \")}`;\n notificationBody = `${notificationBody}\\n${pollText}`;\n }\n\n // Get all device tokens\n const tokensResult = await sqlite.execute(`SELECT push_token FROM ${DEVICE_TOKENS_TABLE}`);\n\n const results: { token: string; success: boolean; error?: string }[] = [];\n\n for (const row of tokensResult.rows) {\n const token = row.push_token as string;\n const result = await sendPushNotification(token, user, notificationBody);\n results.push({ token: token.substring(0, 10) + \"...\", ...result });\n }\n\n // Mark the post as notified\n await sqlite.execute({\n sql: `INSERT INTO ${POST_NOTIFICATIONS_TABLE} (post_id, notified_at) VALUES (?, ?)`,\n args: [postId, Date.now()],\n });\n\n const successCount = results.filter((r) => r.success).length;\n\n return Response.json({\n success: true,\n sent: successCount,\n total: results.length,\n results,\n });\n}\n\nfunction serveAdminPage(): Response {\n const html = `\n\n\n \n \n Posts Admin\n \n \n\n\n

Posts Admin

\n \n \n Puzzle mode\n \n ⚠️ No Auth\n \n

\n\n

Loading posts...

\n\n \n \n\n \n\n`;\n\n return new Response(html, {\n headers: { \"Content-Type\": \"text/html\" },\n });\n}\n\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const baseUrl = `${url.protocol}//${url.host}`;\n\n // Route: POST /upload\n if (req.method === \"POST\" && url.pathname === \"/upload\") {\n return uploadImage(req);\n }\n\n // Route: GET /image/:key\n if (req.method === \"GET\" && url.pathname.startsWith(\"/image/\")) {\n const key = url.pathname.replace(\"/image/\", \"\");\n return serveImage(key);\n }\n\n // Route: GET /audio/:key\n if (req.method === \"GET\" && url.pathname.startsWith(\"/audio/\")) {\n const key = url.pathname.replace(\"/audio/\", \"\");\n return serveAudio(key, req);\n }\n\n // Route: POST /post - Create a new post\n if (req.method === \"POST\" && url.pathname === \"/post\") {\n return createPost(req);\n }\n\n // Route: PUT /post/:id - Update a post\n const postMatch = url.pathname.match(/^\\/post\\/(\\d+)$/);\n if (postMatch) {\n const id = parseInt(postMatch[1], 10);\n if (req.method === \"PUT\") {\n return updatePost(id, req);\n }\n }\n\n // Route: POST /post/:id/archive - Toggle archive status\n const archiveMatch = url.pathname.match(/^\\/post\\/(\\d+)\\/archive$/);\n if (req.method === \"POST\" && archiveMatch) {\n const id = parseInt(archiveMatch[1], 10);\n return toggleArchivePost(id);\n }\n\n // Route: GET /admin - Serve admin page (TODO: add authentication)\n if (req.method === \"GET\" && url.pathname === \"/admin\") {\n return serveAdminPage();\n }\n\n // Route: GET /admin/posts - Return posts with raw image keys for admin\n if (req.method === \"GET\" && url.pathname === \"/admin/posts\") {\n const posts = await getPostsRaw();\n return Response.json({ posts });\n }\n\n // Route: POST /reaction - Toggle a reaction\n if (req.method === \"POST\" && url.pathname === \"/reaction\") {\n return toggleReaction(req, baseUrl);\n }\n\n // Route: POST /emoji-button/click - Toggle emoji button click\n if (req.method === \"POST\" && url.pathname === \"/emoji-button/click\") {\n return clickEmojiButton(req);\n }\n\n // Route: POST /poll/vote - Vote on a poll\n if (req.method === \"POST\" && url.pathname === \"/poll/vote\") {\n return votePoll(req);\n }\n\n // Route: POST /note/vote - Vote on a community note\n if (req.method === \"POST\" && url.pathname === \"/note/vote\") {\n return voteNote(req);\n }\n\n // Route: POST /register-push - Register a device push token\n if (req.method === \"POST\" && url.pathname === \"/register-push\") {\n return registerPushToken(req);\n }\n\n // Route: GET /puzzle-mode - Get current puzzle mode setting\n if (req.method === \"GET\" && url.pathname === \"/puzzle-mode\") {\n const enabled = await getPuzzleMode();\n return Response.json({ enabled });\n }\n\n // Route: POST /puzzle-mode - Set puzzle mode setting\n if (req.method === \"POST\" && url.pathname === \"/puzzle-mode\") {\n const body = await req.json();\n const enabled = body.enabled === true;\n await setPuzzleMode(enabled);\n return Response.json({ success: true, enabled });\n }\n\n // Route: POST /notify/:id - Send push notification for a post\n const notifyMatch = url.pathname.match(/^\\/notify\\/(\\d+)$/);\n if (req.method === \"POST\" && notifyMatch) {\n const id = parseInt(notifyMatch[1], 10);\n return sendNotificationsForPost(id);\n }\n\n // Route: GET / - Return posts from SQLite\n const deviceId = url.searchParams.get(\"device_id\") || undefined;\n const posts = await getPosts(baseUrl, deviceId);\n return Response.json({ posts });\n}",[],[215],"files",[154,175,184,193,204,213],"counts",{"_227":32,"_228":199,"_229":32,"_230":231,"_235":236},"pulls","branches","forks","envVars",{"_232":233,"_234":32},"count",5,"emptyCount","connections",{"_232":32,"_237":32},"unfilledCount","contents",[240,243,246,249,252,255],{"_103":155,"_96":156,"_157":158,"_159":-5,"_160":142,"_161":162,"_163":164,"_105":241,"_100":32,"_166":167,"_168":169,"_170":22,"_171":-5,"_99":242},["D",1768658586308],[156],{"_103":176,"_96":177,"_157":178,"_159":-5,"_160":142,"_161":179,"_163":164,"_105":244,"_100":32,"_166":167,"_168":181,"_170":22,"_171":-5,"_99":245},["D",1768616146463],[177],{"_103":185,"_96":186,"_157":187,"_159":-5,"_160":142,"_161":188,"_163":164,"_105":247,"_100":32,"_166":167,"_168":190,"_170":22,"_171":-5,"_99":248},["D",1768616146471],[186],{"_103":194,"_96":195,"_157":196,"_159":-5,"_160":142,"_161":197,"_163":164,"_105":250,"_100":199,"_166":200,"_168":201,"_170":22,"_171":-5,"_99":251},["D",1768658286190],[195],{"_103":205,"_96":206,"_157":207,"_159":-5,"_160":142,"_161":208,"_163":164,"_105":253,"_100":32,"_166":167,"_168":210,"_170":22,"_171":-5,"_99":254},["D",1768616146472],[206],{"_103":214,"_96":215,"_157":216,"_159":-5,"_160":142,"_161":141,"_163":164,"_105":256,"_100":218,"_166":219,"_168":220,"_170":22,"_171":-5,"_99":257},["D",1768970639239],[215],"upstreamProject","upstreamBranch","emptyEnvVars","isNew","flags",[],"routes/_app.x.$handle.$name.environment-variables",{"_94":95,"_96":97,"_101":266,"_230":271,"_298":22},{"_103":104,"_96":97,"_105":267,"_107":-5,"_108":22,"_109":110,"_111":-5,"_112":-5,"_14":113,"_114":-5,"_115":22,"_116":-5,"_117":-5,"_118":32,"_16":119,"_120":-5,"_121":-5,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":268,"_134":269,"_139":270,"_151":22},["D",1768613533076],{"_94":95,"_132":133},{"_94":95,"_136":133,"_14":113,"_137":138},{"_100":141,"_96":44,"_103":142,"_143":104,"_144":-5,"_145":-5,"_146":-5,"_147":-5,"_148":-5,"_149":97,"_150":95},[272,280,285,289,294],{"_273":274,"_105":275,"_276":277,"_111":278,"_279":22},"key","APNS_USE_SANDBOX",["D",1768671700165],"updatedAt",["D",1768706740401],"Use APNs sandbox for development builds","isEmpty",{"_273":281,"_105":282,"_276":283,"_111":284,"_279":22},"APNS_PRIVATE_KEY",["D",1768670919355],["D",1768707189825],"APNs authentication private key (.p8 file contents)",{"_273":286,"_105":287,"_276":-5,"_111":288,"_279":22},"APNS_BUNDLE_ID",["D",1768670882075],"iOS app bundle identifier for push notifications",{"_273":290,"_105":291,"_276":292,"_111":293,"_279":22},"APNS_KEY_ID",["D",1768670338040],["D",1768707154169],"PNS key ID",{"_273":295,"_105":296,"_276":-5,"_111":297,"_279":22},"APNS_TEAM_ID",["D",1768670311533],"Apple Developer Team ID","isTeamOrg","actionData","errors"]

Hi

Posts Admin

New Post

jdan

jdanapp-srv

Environment variables