[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","ff0419d68e57d8cc3e37e5d3da688157c5dd37c6","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","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","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_148":149,"_172":173,"_174":-5},"handle","paulkinlan","name","postherous","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":110,"_111":-5,"_12":112,"_113":-5,"_114":20,"_115":-5,"_116":117,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","346548ce-5dc8-11f0-ace7-0224a6c84d84","createdAt",["D",1752177490232],"deletedAt","legacyEnvVars","privacy","public","description","https://posthero.us - an email powered blogging system","imageUrl","25f41957-737e-448b-bae4-dec9937eed5b","pinnedFileId","pinPreview","screenshotVersion","likeCount",6,"f5d3fee2-e1c2-4f2b-b60d-716ec388aac8","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_129":130},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yWG9oT0R2UTMxam83NXp1d0RRaGJYVTY0aFYifQ","org",{"_93":94,"_133":130},"avatar","branch",{"_136":137,"_95":43,"_101":138,"_139":102,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},"version",522,"3465ba48-5dc8-11f0-ace7-0224a6c84d84","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":150,"_95":151,"_152":153,"_154":155,"_156":138,"_157":158,"_159":160,"_103":161,"_136":162,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"2285576c-6a51-11f0-80f8-0224a6c84d84","queries.ts","fileId","075bc6c2-5dc9-11f0-8026-0224a6c84d84","parentId","12e098bb-e3d3-4bf5-842d-04a9e25048b7","branchId","branchVersion",494,"latest",true,["D",1753555715247],16,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { BlogPost, CreatePostData, DraftPost, CreateDraftPostData, EmailVerification, BlogImage, CreateImageData } from \"../../shared/types.ts\";\nimport { POSTS_TABLE, DRAFT_POSTS_TABLE, EMAIL_VERIFICATIONS_TABLE, SUBSCRIPTIONS_TABLE, FOLLOWERS_TABLE, ACTIVITIES_TABLE, IMAGES_TABLE } from \"./schema.ts\";\n// Import CONFIG_TABLE separately to avoid import issues\nconst CONFIG_TABLE = 'blog_config_v1';\n\n/**\n * Create a new blog post\n */\nexport async function createPost(data: CreatePostData): Promise {\n const now = new Date().toISOString();\n \n const result = await sqlite.execute(`\n INSERT INTO ${POSTS_TABLE} (\n title, slug, content, content_type, author_email, author_name, published_at\n ) VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n data.title,\n data.slug,\n data.content,\n data.content_type,\n data.author_email,\n data.author_name || null,\n now\n ]);\n\n // Get the inserted post by slug since lastInsertRowId might not be available\n const post = await getPostBySlug(data.slug);\n if (!post) {\n throw new Error('Failed to create post');\n }\n \n return post;\n}\n\n/**\n * Get a post by ID\n */\nexport async function getPostById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${POSTS_TABLE} WHERE id = ?\n `, [id]);\n\n if (result.rows.length === 0) {\n throw new Error(`Post with ID ${id} not found`);\n }\n\n return result.rows[0] as BlogPost;\n}\n\n/**\n * Get a post by slug\n */\nexport async function getPostBySlug(slug: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${POSTS_TABLE} WHERE slug = ?\n `, [slug]);\n\n return result.rows.length > 0 ? result.rows[0] as BlogPost : null;\n}\n\n/**\n * Get all posts ordered by publication date (newest first)\n */\nexport async function getAllPosts(limit: number = 50, offset: number = 0): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${POSTS_TABLE} \n ORDER BY published_at DESC \n LIMIT ? OFFSET ?\n `, [limit, offset]);\n\n return result.rows as BlogPost[];\n}\n\n/**\n * Get recent posts for RSS feed\n */\nexport async function getRecentPosts(limit: number = 20): Promise {\n return await getAllPosts(limit, 0);\n}\n\n/**\n * Check if a slug already exists\n */\nexport async function slugExists(slug: string): Promise {\n const result = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${POSTS_TABLE} WHERE slug = ?\n `, [slug]);\n\n return (result.rows[0] as any).count > 0;\n}\n\n/**\n * Generate a unique slug\n */\nexport async function generateUniqueSlug(baseSlug: string): Promise {\n let slug = baseSlug;\n let counter = 1;\n\n while (await slugExists(slug)) {\n slug = `${baseSlug}-${counter}`;\n counter++;\n }\n\n return slug;\n}\n\n/**\n * Draft Posts Management\n */\n\n/**\n * Create a new draft post\n */\nexport async function createDraftPost(data: CreateDraftPostData): Promise {\n console.log('💾 Creating draft post in database:', {\n title: data.title,\n slug: data.slug,\n contentLength: data.content.length,\n contentType: data.content_type,\n authorEmail: data.author_email,\n authorName: data.author_name,\n tokenLength: data.verification_token.length,\n expiresAt: data.expires_at\n });\n\n try {\n const result = await sqlite.execute(`\n INSERT INTO ${DRAFT_POSTS_TABLE} (\n title, slug, content, content_type, author_email, author_name, verification_token, expires_at\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n data.title,\n data.slug,\n data.content,\n data.content_type,\n data.author_email,\n data.author_name || null,\n data.verification_token,\n data.expires_at\n ]);\n\n console.log('✅ Draft post inserted, fetching created record...');\n\n // Get the inserted draft post\n const draft = await getDraftPostByToken(data.verification_token);\n if (!draft) {\n console.error('❌ Failed to retrieve created draft post');\n throw new Error('Failed to create draft post');\n }\n \n console.log('✅ Draft post created successfully:', {\n id: draft.id,\n title: draft.title,\n slug: draft.slug\n });\n\n return draft;\n } catch (error) {\n console.error('❌ Database error creating draft post:', {\n error: error.message,\n stack: error.stack,\n data: data\n });\n throw error;\n }\n}\n\n/**\n * Get a draft post by verification token\n */\nexport async function getDraftPostByToken(token: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${DRAFT_POSTS_TABLE} WHERE verification_token = ?\n `, [token]);\n\n return result.rows.length > 0 ? result.rows[0] as DraftPost : null;\n}\n\n/**\n * Convert draft post to published post\n */\nexport async function publishDraftPost(token: string): Promise {\n console.log('📤 Publishing draft post:', {\n token: token.substring(0, 10) + '...',\n tokenLength: token.length\n });\n\n const draft = await getDraftPostByToken(token);\n if (!draft) {\n console.error('❌ Draft post not found for token:', {\n token: token.substring(0, 10) + '...'\n });\n throw new Error('Draft post not found');\n }\n\n console.log('✅ Draft post found:', {\n id: draft.id,\n title: draft.title,\n slug: draft.slug,\n expiresAt: draft.expires_at\n });\n\n // Check if draft has expired\n if (new Date(draft.expires_at) < new Date()) {\n console.error('❌ Draft post has expired:', {\n expiresAt: draft.expires_at,\n currentTime: new Date().toISOString()\n });\n throw new Error('Draft post has expired');\n }\n\n console.log('📝 Creating published post from draft...');\n\n // Create the published post\n const postData: CreatePostData = {\n title: draft.title,\n slug: draft.slug,\n content: draft.content,\n content_type: draft.content_type,\n author_email: draft.author_email,\n author_name: draft.author_name\n };\n\n try {\n const post = await createPost(postData);\n console.log('✅ Published post created:', {\n id: post.id,\n title: post.title,\n slug: post.slug\n });\n\n console.log('🧹 Cleaning up draft and verification records...');\n \n // Clean up draft and verification records\n await sqlite.execute(`DELETE FROM ${EMAIL_VERIFICATIONS_TABLE} WHERE draft_post_id = ?`, [draft.id]);\n await sqlite.execute(`DELETE FROM ${DRAFT_POSTS_TABLE} WHERE id = ?`, [draft.id]);\n\n console.log('✅ Cleanup completed, draft post published successfully');\n return post;\n } catch (error) {\n console.error('❌ Error creating published post:', {\n error: error.message,\n stack: error.stack,\n postData: postData\n });\n throw error;\n }\n}\n\n/**\n * Clean up expired draft posts\n */\nexport async function cleanupExpiredDrafts(): Promise {\n const now = new Date().toISOString();\n \n console.log('🧹 Starting cleanup of expired drafts:', {\n currentTime: now\n });\n\n try {\n // Get expired draft IDs first\n const expiredDrafts = await sqlite.execute(`\n SELECT id, title, expires_at FROM ${DRAFT_POSTS_TABLE} WHERE expires_at < ?\n `, [now]);\n\n console.log('📊 Found expired drafts:', {\n count: expiredDrafts.rows.length,\n drafts: expiredDrafts.rows.map(row => ({\n id: (row as any).id,\n title: (row as any).title,\n expires_at: (row as any).expires_at\n }))\n });\n\n if (expiredDrafts.rows.length === 0) {\n console.log('✅ No expired drafts to clean up');\n return;\n }\n\n // Delete related verification records\n for (const draft of expiredDrafts.rows) {\n await sqlite.execute(`DELETE FROM ${EMAIL_VERIFICATIONS_TABLE} WHERE draft_post_id = ?`, [(draft as any).id]);\n }\n\n // Delete expired drafts\n const deleteResult = await sqlite.execute(`DELETE FROM ${DRAFT_POSTS_TABLE} WHERE expires_at < ?`, [now]);\n\n console.log('✅ Cleanup completed:', {\n expiredDraftsDeleted: expiredDrafts.rows.length,\n verificationRecordsDeleted: expiredDrafts.rows.length\n });\n } catch (error) {\n console.error('❌ Error during cleanup:', {\n error: error.message,\n stack: error.stack\n });\n throw error;\n }\n}\n\n/**\n * Email Verification Management\n */\n\n/**\n * Create email verification record\n */\nexport async function createEmailVerification(\n email: string,\n token: string,\n draftPostId: number,\n expiresAt: string\n): Promise {\n console.log('📝 Creating email verification record:', {\n email: email,\n tokenLength: token.length,\n draftPostId: draftPostId,\n expiresAt: expiresAt\n });\n\n try {\n await sqlite.execute(`\n INSERT INTO ${EMAIL_VERIFICATIONS_TABLE} (email, token, draft_post_id, expires_at)\n VALUES (?, ?, ?, ?)\n `, [email, token, draftPostId, expiresAt]);\n\n console.log('✅ Email verification record created successfully');\n } catch (error) {\n console.error('❌ Database error creating email verification:', {\n error: error.message,\n stack: error.stack,\n email: email,\n draftPostId: draftPostId\n });\n throw error;\n }\n}\n\n/**\n * Get email verification by token\n */\nexport async function getEmailVerificationByToken(token: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${EMAIL_VERIFICATIONS_TABLE} WHERE token = ?\n `, [token]);\n\n return result.rows.length > 0 ? result.rows[0] as EmailVerification : null;\n}\n\n/**\n * Mark email verification as verified\n */\nexport async function markEmailVerified(token: string): Promise {\n await sqlite.execute(`\n UPDATE ${EMAIL_VERIFICATIONS_TABLE} \n SET verified_at = datetime('now')\n WHERE token = ?\n `, [token]);\n}\n\n/**\n * WebSub subscription management\n */\nexport async function addWebSubSubscription(\n callbackUrl: string,\n topicUrl: string,\n leaseSeconds?: number,\n secret?: string\n): Promise {\n const expiresAt = leaseSeconds \n ? new Date(Date.now() + leaseSeconds * 1000).toISOString()\n : null;\n\n await sqlite.execute(`\n INSERT OR REPLACE INTO ${SUBSCRIPTIONS_TABLE} \n (callback_url, topic_url, lease_seconds, secret, expires_at)\n VALUES (?, ?, ?, ?, ?)\n `, [callbackUrl, topicUrl, leaseSeconds || null, secret || null, expiresAt]);\n}\n\nexport async function getActiveWebSubSubscriptions(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${SUBSCRIPTIONS_TABLE}\n WHERE verified_at IS NOT NULL \n AND (expires_at IS NULL OR expires_at > datetime('now'))\n `);\n\n return result.rows;\n}\n\nexport async function verifyWebSubSubscription(callbackUrl: string): Promise {\n await sqlite.execute(`\n UPDATE ${SUBSCRIPTIONS_TABLE} \n SET verified_at = datetime('now')\n WHERE callback_url = ?\n `, [callbackUrl]);\n}\n\n/**\n * ActivityPub Followers Management\n */\n\nexport interface ActivityPubFollower {\n id: number;\n actor_id: string;\n actor_username?: string;\n actor_display_name?: string;\n actor_domain?: string;\n actor_inbox?: string;\n actor_shared_inbox?: string;\n actor_icon_url?: string;\n followed_at: string;\n last_activity_at?: string;\n is_active: number;\n}\n\nexport interface ActivityPubActivity {\n id: number;\n activity_id: string;\n activity_type: string;\n actor_id: string;\n object_id?: string;\n object_type?: string;\n post_slug?: string;\n activity_data: string;\n received_at: string;\n processed_at?: string;\n}\n\n/**\n * Add a new follower\n */\nexport async function addFollower(\n actorId: string,\n actorData: {\n username?: string;\n displayName?: string;\n domain?: string;\n inbox?: string;\n sharedInbox?: string;\n iconUrl?: string;\n }\n): Promise {\n await sqlite.execute(`\n INSERT OR REPLACE INTO ${FOLLOWERS_TABLE} \n (actor_id, actor_username, actor_display_name, actor_domain, actor_inbox, actor_shared_inbox, actor_icon_url, last_activity_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))\n `, [\n actorId,\n actorData.username || null,\n actorData.displayName || null,\n actorData.domain || null,\n actorData.inbox || null,\n actorData.sharedInbox || null,\n actorData.iconUrl || null\n ]);\n}\n\n/**\n * Remove a follower\n */\nexport async function removeFollower(actorId: string): Promise {\n await sqlite.execute(`\n UPDATE ${FOLLOWERS_TABLE} \n SET is_active = 0, last_activity_at = datetime('now')\n WHERE actor_id = ?\n `, [actorId]);\n}\n\n/**\n * Get all active followers\n */\nexport async function getActiveFollowers(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${FOLLOWERS_TABLE}\n WHERE is_active = 1\n ORDER BY followed_at DESC\n `);\n\n return result.rows as ActivityPubFollower[];\n}\n\n/**\n * Get follower count\n */\nexport async function getFollowerCount(): Promise {\n const result = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${FOLLOWERS_TABLE}\n WHERE is_active = 1\n `);\n\n return (result.rows[0] as any).count;\n}\n\n/**\n * Check if an actor is following\n */\nexport async function isFollowing(actorId: string): Promise {\n const result = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${FOLLOWERS_TABLE}\n WHERE actor_id = ? AND is_active = 1\n `, [actorId]);\n\n return (result.rows[0] as any).count > 0;\n}\n\n/**\n * ActivityPub Activities Management\n */\n\n/**\n * Store an ActivityPub activity\n */\nexport async function storeActivity(\n activityId: string,\n activityType: string,\n actorId: string,\n objectId?: string,\n objectType?: string,\n postSlug?: string,\n activityData?: any\n): Promise {\n await sqlite.execute(`\n INSERT OR REPLACE INTO ${ACTIVITIES_TABLE}\n (activity_id, activity_type, actor_id, object_id, object_type, post_slug, activity_data)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n activityId,\n activityType,\n actorId,\n objectId || null,\n objectType || null,\n postSlug || null,\n activityData ? JSON.stringify(activityData) : null\n ]);\n}\n\n/**\n * Get activities for a post (likes, shares, etc.)\n */\nexport async function getPostActivities(postSlug: string, activityType?: string): Promise {\n let query = `\n SELECT * FROM ${ACTIVITIES_TABLE}\n WHERE post_slug = ?\n `;\n const params: any[] = [postSlug];\n\n if (activityType) {\n query += ` AND activity_type = ?`;\n params.push(activityType);\n }\n\n query += ` ORDER BY received_at DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows as ActivityPubActivity[];\n}\n\n/**\n * Get activity counts for a post\n */\nexport async function getPostActivityCounts(postSlug: string): Promise<{ likes: number; shares: number; replies: number }> {\n const result = await sqlite.execute(`\n SELECT \n activity_type,\n COUNT(*) as count\n FROM ${ACTIVITIES_TABLE}\n WHERE post_slug = ?\n GROUP BY activity_type\n `, [postSlug]);\n\n const counts = { likes: 0, shares: 0, replies: 0 };\n \n for (const row of result.rows) {\n const { activity_type, count } = row as any;\n if (activity_type === 'Like') counts.likes = count;\n else if (activity_type === 'Announce') counts.shares = count;\n else if (activity_type === 'Create') counts.replies = count;\n }\n\n return counts;\n}\n\n/**\n * Check if an activity already exists\n */\nexport async function activityExists(activityId: string): Promise {\n const result = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${ACTIVITIES_TABLE}\n WHERE activity_id = ?\n `, [activityId]);\n\n return (result.rows[0] as any).count > 0;\n}\n\n/**\n * Delete posts by author name\n */\nexport async function deletePostsByAuthorName(authorName: string): Promise {\n console.log(`🗑️ Deleting posts by author: ${authorName}`);\n \n // First, get the posts that will be deleted to clean up related activities\n const postsToDelete = await sqlite.execute(`\n SELECT id, slug, title FROM ${POSTS_TABLE} WHERE author_name = ?\n `, [authorName]);\n \n console.log(`📊 Found ${postsToDelete.rows.length} posts to delete:`, \n postsToDelete.rows.map(row => ({ \n id: (row as any).id, \n title: (row as any).title, \n slug: (row as any).slug \n }))\n );\n \n if (postsToDelete.rows.length === 0) {\n console.log('✅ No posts found to delete');\n return 0;\n }\n \n // Delete related activities for these posts\n for (const post of postsToDelete.rows) {\n const slug = (post as any).slug;\n await sqlite.execute(`DELETE FROM ${ACTIVITIES_TABLE} WHERE post_slug = ?`, [slug]);\n console.log(`🧹 Cleaned up activities for post: ${slug}`);\n }\n \n // Delete the posts\n const deleteResult = await sqlite.execute(`\n DELETE FROM ${POSTS_TABLE} WHERE author_name = ?\n `, [authorName]);\n \n console.log(`✅ Successfully deleted ${postsToDelete.rows.length} posts by ${authorName}`);\n return postsToDelete.rows.length;\n}\n\n// ===== IMAGE FUNCTIONS =====\n\n/**\n * Create a new image record\n */\nexport async function createImage(data: CreateImageData): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${IMAGES_TABLE} (\n filename, original_filename, mime_type, file_size, width, height, \n alt_text, post_slug, uploaded_by_email, blob_key\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n data.filename,\n data.original_filename,\n data.mime_type,\n data.file_size,\n data.width || null,\n data.height || null,\n data.alt_text || null,\n data.post_slug || null,\n data.uploaded_by_email,\n data.blob_key\n ]);\n\n // Get the inserted image\n const image = await getImageByFilename(data.filename);\n if (!image) {\n throw new Error('Failed to create image record');\n }\n \n return image;\n}\n\n/**\n * Get an image by filename\n */\nexport async function getImageByFilename(filename: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${IMAGES_TABLE} WHERE filename = ?\n `, [filename]);\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return result.rows[0] as BlogImage;\n}\n\n/**\n * Get an image by blob key\n */\nexport async function getImageByBlobKey(blobKey: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${IMAGES_TABLE} WHERE blob_key = ?\n `, [blobKey]);\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return result.rows[0] as BlogImage;\n}\n\n/**\n * Get all images for a specific post\n */\nexport async function getImagesByPostSlug(postSlug: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${IMAGES_TABLE} \n WHERE post_slug = ? \n ORDER BY created_at ASC\n `, [postSlug]);\n\n return result.rows as BlogImage[];\n}\n\n/**\n * Get all images uploaded by a specific user\n */\nexport async function getImagesByUser(email: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${IMAGES_TABLE} \n WHERE uploaded_by_email = ? \n ORDER BY created_at DESC\n `, [email]);\n\n return result.rows as BlogImage[];\n}\n\n/**\n * Get all images (for admin/management purposes)\n */\nexport async function getAllImages(limit: number = 100, offset: number = 0): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${IMAGES_TABLE} \n ORDER BY created_at DESC \n LIMIT ? OFFSET ?\n `, [limit, offset]);\n\n return result.rows as BlogImage[];\n}\n\n/**\n * Update image metadata\n */\nexport async function updateImage(filename: string, updates: Partial>): Promise {\n const setParts: string[] = [];\n const values: any[] = [];\n\n if (updates.alt_text !== undefined) {\n setParts.push('alt_text = ?');\n values.push(updates.alt_text);\n }\n\n if (updates.post_slug !== undefined) {\n setParts.push('post_slug = ?');\n values.push(updates.post_slug);\n }\n\n if (setParts.length === 0) {\n return getImageByFilename(filename);\n }\n\n values.push(filename);\n\n await sqlite.execute(`\n UPDATE ${IMAGES_TABLE} \n SET ${setParts.join(', ')} \n WHERE filename = ?\n `, values);\n\n return getImageByFilename(filename);\n}\n\n/**\n * Delete an image record\n */\nexport async function deleteImage(filename: string): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${IMAGES_TABLE} WHERE filename = ?\n `, [filename]);\n\n return result.changes > 0;\n}\n\n/**\n * Generate a unique filename for an image\n */\nexport async function generateUniqueImageFilename(originalFilename: string): Promise {\n const timestamp = Date.now();\n const randomSuffix = Math.random().toString(36).substring(2, 8);\n \n // Extract file extension\n const lastDotIndex = originalFilename.lastIndexOf('.');\n const extension = lastDotIndex > 0 ? originalFilename.substring(lastDotIndex) : '';\n const baseName = lastDotIndex > 0 ? originalFilename.substring(0, lastDotIndex) : originalFilename;\n \n // Clean the base name (remove special characters, spaces, etc.)\n const cleanBaseName = baseName\n .toLowerCase()\n .replace(/[^a-z0-9]/g, '-')\n .replace(/-+/g, '-')\n .replace(/^-|-$/g, '');\n \n let filename = `${timestamp}-${randomSuffix}-${cleanBaseName}${extension}`;\n \n // Ensure uniqueness\n let counter = 1;\n while (await getImageByFilename(filename)) {\n filename = `${timestamp}-${randomSuffix}-${cleanBaseName}-${counter}${extension}`;\n counter++;\n }\n \n return filename;\n}\n\n/**\n * Update a blog post\n */\nexport async function updatePost(slug: string, updates: Partial>): Promise {\n const setParts: string[] = [];\n const values: any[] = [];\n\n if (updates.title !== undefined) {\n setParts.push('title = ?');\n values.push(updates.title);\n }\n\n if (updates.content !== undefined) {\n setParts.push('content = ?');\n values.push(updates.content);\n }\n\n if (updates.content_type !== undefined) {\n setParts.push('content_type = ?');\n values.push(updates.content_type);\n }\n\n if (setParts.length === 0) {\n return getPostBySlug(slug);\n }\n\n // Add updated_at timestamp\n setParts.push('updated_at = datetime(\\'now\\')');\n values.push(slug);\n\n await sqlite.execute(`\n UPDATE ${POSTS_TABLE} \n SET ${setParts.join(', ')} \n WHERE slug = ?\n `, values);\n\n return getPostBySlug(slug);\n}\n\n/**\n * Delete a blog post by slug\n */\nexport async function deletePost(slug: string): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${POSTS_TABLE} WHERE slug = ?\n `, [slug]);\n\n return result.changes > 0;\n}\n\n/**\n * Delete posts by author email (for cleanup/admin purposes)\n */\nexport async function deletePostsByAuthorEmail(authorEmail: string): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${POSTS_TABLE} WHERE author_email = ?\n `, [authorEmail]);\n\n return result.changes;\n}\n\n// Configuration management functions\n\nexport interface BlogConfig {\n id: number;\n config_key: string;\n config_value: string | null;\n config_type: 'string' | 'text' | 'number' | 'boolean';\n description: string | null;\n updated_at: string;\n updated_by: string | null;\n}\n\n/**\n * Get a configuration value by key\n */\nexport async function getConfigValue(key: string): Promise {\n const result = await sqlite.execute(`\n SELECT config_value FROM ${CONFIG_TABLE} WHERE config_key = ?\n `, [key]);\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return result.rows[0].config_value as string | null;\n}\n\n/**\n * Get all configuration values\n */\nexport async function getAllConfig(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${CONFIG_TABLE} ORDER BY config_key\n `);\n\n return result.rows as BlogConfig[];\n}\n\n/**\n * Update a configuration value\n */\nexport async function updateConfigValue(key: string, value: string | null, updatedBy?: string): Promise {\n await sqlite.execute(`\n UPDATE ${CONFIG_TABLE} \n SET config_value = ?, updated_at = datetime('now'), updated_by = ?\n WHERE config_key = ?\n `, [value, updatedBy || null, key]);\n}\n\n/**\n * Get configuration values as a typed object\n */\nexport async function getBlogConfig(): Promise<{\n blog_title: string;\n blog_description: string;\n blog_tagline: string;\n blog_footer_text: string;\n blog_icon: string;\n author_name: string;\n mastodon_handle: string;\n mastodon_url: string;\n twitter_handle: string;\n github_url: string;\n custom_css: string;\n show_fork_link: boolean;\n enable_comments: boolean;\n posts_per_page: number;\n}> {\n const configs = await getAllConfig();\n const configMap = new Map(configs.map(c => [c.config_key, c.config_value]));\n\n return {\n blog_title: configMap.get('blog_title') || 'Email Blog',\n blog_description: configMap.get('blog_description') || 'A blog powered by email publishing',\n blog_tagline: configMap.get('blog_tagline') || 'Powered by email publishing',\n blog_footer_text: configMap.get('blog_footer_text') || 'Send an email to publish instantly',\n blog_icon: configMap.get('blog_icon') || '📧',\n author_name: configMap.get('author_name') || 'Email Blog',\n mastodon_handle: configMap.get('mastodon_handle') || '',\n mastodon_url: configMap.get('mastodon_url') || '',\n twitter_handle: configMap.get('twitter_handle') || '',\n github_url: configMap.get('github_url') || '',\n custom_css: configMap.get('custom_css') || '',\n show_fork_link: configMap.get('show_fork_link') === 'true',\n enable_comments: configMap.get('enable_comments') === 'true',\n posts_per_page: parseInt(configMap.get('posts_per_page') || '10', 10)\n };\n}","deleted","label",[170,171,151],"backend","database","permalink","https://www.val.town/x/paulkinlan/postherous/code/backend/database/queries.ts","extension","actionData","errors"]