[{"_1":2,"_179":-5,"_180":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","48807a73dfb4c250ae0af97d07e94d04aadb2cac","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_154":155,"_176":177,"_178":-5},"handle","jaballadares","name","sharesheet_thing","path","backend/database/queries_v2.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":-5,"_118":-5,"_119":32,"_14":120,"_121":122,"_123":122,"_124":125,"_126":125,"_127":125,"_128":122,"_129":122,"_23":130,"_133":134,"_136":137,"_153":20},"id","76b44c1c-dba3-11f0-a67e-42dde27851f2","createdAt",["D",1766015556590],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","ae9e6b63-0b4a-4c1b-ab8e-cdc794d642f7","pinnedFileId","019b2eba-dc23-70e4-a6e6-33077cdb8332","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","af62b60f-8b54-4c8c-b5e7-a6a134fd026f","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJObXhoVVk2Sk94M0p2alRVdUlrSHZSa0tjeCIsImluaXRpYWxzIjoiSkIifQ","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":143,"_144":145,"_146":109,"_147":148,"_149":150,"_151":97,"_152":95},"version",37,"76b6f6d8-dba3-11f0-a67e-42dde27851f2","projectId","forkedBranchId","aacf49ce-3b40-11f0-a79d-9e149126039e","forkedBranchVersion",57,"forkedProjectPrivacy","forkedProjectId","aacf14c2-3b40-11f0-a79d-9e149126039e","forkedProjectOrgId","0630d977-f678-453e-926c-52043293f59f","projectName","userHandle","isOwned","file",{"_102":156,"_96":157,"_158":159,"_160":161,"_162":140,"_163":164,"_165":116,"_104":166,"_138":32,"_167":168,"_169":170,"_171":20,"_172":-5,"_98":173},"2be497cc-e0fe-11f0-b28f-42dde27851f2","queries_v2.ts","fileId","2be4986c-e0fe-11f0-b28f-42dde27851f2","parentId","019b2eba-dc23-70e4-a6e6-1bab3472de67","branchId","branchVersion",2,"latest",["D",1766604271115],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { UserSettings, DailySummary } from \"../../shared/types.ts\";\n\n// ============================================================================\n// Date-Range Content Queries\n// ============================================================================\n\nexport async function getContentItemsByUserIdAndDateRange(\n userId: string,\n startDate: string, // ISO date string\n endDate: string, // ISO date string\n limit = 100,\n offset = 0\n): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM content_items\n WHERE userId = ?\n AND createdAt >= ?\n AND createdAt < ?\n ORDER BY createdAt ASC\n LIMIT ? OFFSET ?`,\n [userId, startDate, endDate, limit, offset]\n );\n\n return result.rows.map((row: any) => ({\n id: row.id,\n userId: row.userId,\n type: row.type,\n content: row.content,\n title: row.title,\n metadata: row.metadata ? JSON.parse(row.metadata) : undefined,\n createdAt: row.createdAt,\n }));\n } catch (error) {\n console.error(\"getContentItemsByUserIdAndDateRange error:\", error);\n throw error;\n }\n}\n\nexport async function getContentItemsCountByDateRange(\n userId: string,\n startDate: string,\n endDate: string\n): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT COUNT(*) as count FROM content_items\n WHERE userId = ?\n AND createdAt >= ?\n AND createdAt < ?`,\n [userId, startDate, endDate]\n );\n\n return result.rows[0].count as number;\n } catch (error) {\n console.error(\"getContentItemsCountByDateRange error:\", error);\n throw error;\n }\n}\n\nexport async function getContentItemsGroupedByType(\n userId: string,\n startDate: string,\n endDate: string\n): Promise<{ type: string; count: number }[]> {\n try {\n const result = await sqlite.execute(\n `SELECT type, COUNT(*) as count FROM content_items\n WHERE userId = ?\n AND createdAt >= ?\n AND createdAt < ?\n GROUP BY type`,\n [userId, startDate, endDate]\n );\n\n return result.rows.map((row: any) => ({\n type: row.type,\n count: row.count as number,\n }));\n } catch (error) {\n console.error(\"getContentItemsGroupedByType error:\", error);\n throw error;\n }\n}\n\n// ============================================================================\n// User Settings Queries\n// ============================================================================\n\nexport async function createUserSettings(settings: Omit): Promise {\n await sqlite.execute(\n `INSERT INTO user_settings (userId, emailEnabled, smsEnabled, smsPhone, summaryTime, timezone, summaryTone, summaryLength, includeContentTypes, createdAt, updatedAt)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n settings.userId,\n settings.emailEnabled ? 1 : 0,\n settings.smsEnabled ? 1 : 0,\n settings.smsPhone || null,\n settings.summaryTime,\n settings.timezone,\n settings.summaryTone,\n settings.summaryLength,\n settings.includeContentTypes ? JSON.stringify(settings.includeContentTypes) : null,\n settings.createdAt,\n settings.createdAt,\n ]\n );\n}\n\nexport async function getUserSettings(userId: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM user_settings WHERE userId = ?`,\n [userId]\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return {\n userId: row.userId,\n emailEnabled: Boolean(row.emailEnabled),\n smsEnabled: Boolean(row.smsEnabled),\n smsPhone: row.smsPhone,\n summaryTime: row.summaryTime,\n timezone: row.timezone,\n summaryTone: row.summaryTone,\n summaryLength: row.summaryLength,\n includeContentTypes: row.includeContentTypes ? JSON.parse(row.includeContentTypes) : undefined,\n createdAt: row.createdAt,\n updatedAt: row.updatedAt,\n };\n } catch (error) {\n console.error(\"getUserSettings error:\", error);\n throw error;\n }\n}\n\nexport async function updateUserSettings(\n userId: string,\n updates: Partial>\n): Promise {\n const fields: string[] = [];\n const values: any[] = [];\n\n if (updates.emailEnabled !== undefined) {\n fields.push(\"emailEnabled = ?\");\n values.push(updates.emailEnabled ? 1 : 0);\n }\n if (updates.smsEnabled !== undefined) {\n fields.push(\"smsEnabled = ?\");\n values.push(updates.smsEnabled ? 1 : 0);\n }\n if (updates.smsPhone !== undefined) {\n fields.push(\"smsPhone = ?\");\n values.push(updates.smsPhone);\n }\n if (updates.summaryTime !== undefined) {\n fields.push(\"summaryTime = ?\");\n values.push(updates.summaryTime);\n }\n if (updates.timezone !== undefined) {\n fields.push(\"timezone = ?\");\n values.push(updates.timezone);\n }\n if (updates.summaryTone !== undefined) {\n fields.push(\"summaryTone = ?\");\n values.push(updates.summaryTone);\n }\n if (updates.summaryLength !== undefined) {\n fields.push(\"summaryLength = ?\");\n values.push(updates.summaryLength);\n }\n if (updates.includeContentTypes !== undefined) {\n fields.push(\"includeContentTypes = ?\");\n values.push(updates.includeContentTypes ? JSON.stringify(updates.includeContentTypes) : null);\n }\n\n if (fields.length === 0) return;\n\n fields.push(\"updatedAt = ?\");\n values.push(new Date().toISOString());\n values.push(userId);\n\n await sqlite.execute(\n `UPDATE user_settings SET ${fields.join(\", \")} WHERE userId = ?`,\n values\n );\n}\n\nexport async function getUsersWithSummaryEnabled(): Promise> {\n try {\n const result = await sqlite.execute(\n `SELECT u.id as userId, u.email\n FROM users u\n INNER JOIN user_settings us ON u.id = us.userId\n WHERE us.emailEnabled = 1 OR us.smsEnabled = 1`\n );\n\n return result.rows.map((row: any) => ({\n userId: row.userId,\n email: row.email,\n }));\n } catch (error) {\n console.error(\"getUsersWithSummaryEnabled error:\", error);\n throw error;\n }\n}\n\n// ============================================================================\n// Daily Summary Queries\n// ============================================================================\n\nexport async function createSummary(summary: Omit): Promise {\n await sqlite.execute(\n `INSERT INTO daily_summaries (id, userId, summaryDate, content, contentItemsCount, contentTypes, generatedAt, sentViaEmail, sentViaSMS, emailSentAt, smsSentAt)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n summary.id,\n summary.userId,\n summary.summaryDate,\n summary.content,\n summary.contentItemsCount,\n summary.contentTypes ? JSON.stringify(summary.contentTypes) : null,\n summary.generatedAt,\n summary.sentViaEmail ? 1 : 0,\n summary.sentViaSMS ? 1 : 0,\n summary.emailSentAt || null,\n summary.smsSentAt || null,\n ]\n );\n}\n\nexport async function getSummaryById(summaryId: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM daily_summaries WHERE id = ?`,\n [summaryId]\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return {\n id: row.id,\n userId: row.userId,\n summaryDate: row.summaryDate,\n content: row.content,\n contentItemsCount: row.contentItemsCount,\n contentTypes: row.contentTypes ? JSON.parse(row.contentTypes) : undefined,\n generatedAt: row.generatedAt,\n sentViaEmail: Boolean(row.sentViaEmail),\n sentViaSMS: Boolean(row.sentViaSMS),\n emailSentAt: row.emailSentAt,\n smsSentAt: row.smsSentAt,\n };\n } catch (error) {\n console.error(\"getSummaryById error:\", error);\n throw error;\n }\n}\n\nexport async function getSummaryByUserIdAndDate(\n userId: string,\n summaryDate: string // YYYY-MM-DD format\n): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM daily_summaries WHERE userId = ? AND summaryDate = ?`,\n [userId, summaryDate]\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return {\n id: row.id,\n userId: row.userId,\n summaryDate: row.summaryDate,\n content: row.content,\n contentItemsCount: row.contentItemsCount,\n contentTypes: row.contentTypes ? JSON.parse(row.contentTypes) : undefined,\n generatedAt: row.generatedAt,\n sentViaEmail: Boolean(row.sentViaEmail),\n sentViaSMS: Boolean(row.sentViaSMS),\n emailSentAt: row.emailSentAt,\n smsSentAt: row.smsSentAt,\n };\n } catch (error) {\n console.error(\"getSummaryByUserIdAndDate error:\", error);\n throw error;\n }\n}\n\nexport async function getSummariesByUserId(\n userId: string,\n limit = 20,\n offset = 0\n): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM daily_summaries\n WHERE userId = ?\n ORDER BY summaryDate DESC\n LIMIT ? OFFSET ?`,\n [userId, limit, offset]\n );\n\n return result.rows.map((row: any) => ({\n id: row.id,\n userId: row.userId,\n summaryDate: row.summaryDate,\n content: row.content,\n contentItemsCount: row.contentItemsCount,\n contentTypes: row.contentTypes ? JSON.parse(row.contentTypes) : undefined,\n generatedAt: row.generatedAt,\n sentViaEmail: Boolean(row.sentViaEmail),\n sentViaSMS: Boolean(row.sentViaSMS),\n emailSentAt: row.emailSentAt,\n smsSentAt: row.smsSentAt,\n }));\n } catch (error) {\n console.error(\"getSummariesByUserId error:\", error);\n throw error;\n }\n}\n\nexport async function getPendingSummaries(): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM daily_summaries\n WHERE sentViaEmail = 0 OR sentViaSMS = 0\n ORDER BY generatedAt ASC`\n );\n\n return result.rows.map((row: any) => ({\n id: row.id,\n userId: row.userId,\n summaryDate: row.summaryDate,\n content: row.content,\n contentItemsCount: row.contentItemsCount,\n contentTypes: row.contentTypes ? JSON.parse(row.contentTypes) : undefined,\n generatedAt: row.generatedAt,\n sentViaEmail: Boolean(row.sentViaEmail),\n sentViaSMS: Boolean(row.sentViaSMS),\n emailSentAt: row.emailSentAt,\n smsSentAt: row.smsSentAt,\n }));\n } catch (error) {\n console.error(\"getPendingSummaries error:\", error);\n throw error;\n }\n}\n\nexport async function markSummaryAsSent(\n summaryId: string,\n method: \"email\" | \"sms\"\n): Promise {\n const field = method === \"email\" ? \"emailSentAt\" : \"smsSentAt\";\n const flagField = method === \"email\" ? \"sentViaEmail\" : \"sentViaSMS\";\n\n await sqlite.execute(\n `UPDATE daily_summaries SET ${flagField} = 1, ${field} = ? WHERE id = ?`,\n [new Date().toISOString(), summaryId]\n );\n}\n\nexport async function deleteSummary(summaryId: string): Promise {\n const result = await sqlite.execute(\n `DELETE FROM daily_summaries WHERE id = ?`,\n [summaryId]\n );\n\n return result.rowsAffected > 0;\n}\n","deleted","label",[174,175,157],"backend","database","permalink","https://www.val.town/x/jaballadares/sharesheet_thing/code/backend/database/queries_v2.ts","extension","actionData","errors"]