[{"_1":2,"_174":-5,"_175":-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","4207e8ad4ddbe7f6e8b804339ca1b2ec4ab76c50","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,"_149":150,"_171":172,"_173":-5},"handle","MrsJD","name","public","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":97,"_109":-5,"_110":-5,"_12":111,"_112":113,"_114":115,"_116":115,"_117":-5,"_118":32,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","01fbebf2-3f93-11f0-8152-76b3cceeab13","createdAt",["D",1748856107449],"deletedAt","legacyEnvVars","privacy","description","imageUrl","e0cf86b0-3f91-11f0-b6e9-ab0b2f88b858","pinnedFileId","62241356-3f93-11f0-9cea-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","d6e87c56-bb6b-483c-b32f-76f2cddfffc0","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4d2lVRndKSHcyNENLV0hzZ3lDSzBhY3p4WSJ9","org",{"_94":95,"_134":131,"_12":111},"avatar","branch",{"_137":138,"_96":44,"_102":139,"_140":103,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":97,"_147":95},"version",22,"01fc47be-3f93-11f0-8152-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":151,"_96":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":115,"_104":161,"_137":32,"_162":163,"_164":165,"_166":20,"_167":-5,"_98":168},"3f85f594-3f93-11f0-b922-76b3cceeab13","queries.ts","fileId","3f85f65c-3f93-11f0-b922-76b3cceeab13","parentId","194ba6ba-e660-42d6-821d-a4eec3ab11cf","branchId","branchVersion",4,"latest",["D",1748856210691],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { CYCLES_TABLE, SYMPTOMS_TABLE } from \"./migrations.ts\";\nimport type { Cycle, Symptom, CyclePrediction, CycleInsights } from \"../../shared/types.ts\";\n\n// Cycle queries\nexport async function createCycle(cycle: Omit): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${CYCLES_TABLE} (start_date, end_date, flow_intensity, notes) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [cycle.start_date, cycle.end_date || null, cycle.flow_intensity, cycle.notes || null]\n );\n return result[0] as Cycle;\n}\n\nexport async function updateCycle(id: number, updates: Partial): Promise {\n const setClauses = [];\n const values = [];\n \n if (updates.end_date !== undefined) {\n setClauses.push('end_date = ?');\n values.push(updates.end_date);\n }\n if (updates.flow_intensity) {\n setClauses.push('flow_intensity = ?');\n values.push(updates.flow_intensity);\n }\n if (updates.notes !== undefined) {\n setClauses.push('notes = ?');\n values.push(updates.notes);\n }\n \n if (setClauses.length === 0) return null;\n \n values.push(id);\n const result = await sqlite.execute(\n `UPDATE ${CYCLES_TABLE} SET ${setClauses.join(', ')} WHERE id = ? RETURNING *`,\n values\n );\n \n return result[0] as Cycle || null;\n}\n\nexport async function getCycles(limit = 50): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${CYCLES_TABLE} ORDER BY start_date DESC LIMIT ?`,\n [limit]\n );\n return result as Cycle[];\n}\n\nexport async function getCycleById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${CYCLES_TABLE} WHERE id = ?`,\n [id]\n );\n return result[0] as Cycle || null;\n}\n\nexport async function deleteCycle(id: number): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${CYCLES_TABLE} WHERE id = ?`,\n [id]\n );\n return result.length > 0;\n}\n\n// Symptom queries\nexport async function createOrUpdateSymptom(symptom: Omit): Promise {\n const symptomsJson = JSON.stringify(symptom.symptoms || []);\n \n const result = await sqlite.execute(\n `INSERT OR REPLACE INTO ${SYMPTOMS_TABLE} \n (date, mood, pain_level, energy_level, sleep_quality, symptoms, notes) \n VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [\n symptom.date,\n symptom.mood,\n symptom.pain_level,\n symptom.energy_level,\n symptom.sleep_quality,\n symptomsJson,\n symptom.notes || null\n ]\n );\n \n const returned = result[0] as any;\n return {\n ...returned,\n symptoms: JSON.parse(returned.symptoms || '[]')\n };\n}\n\nexport async function getSymptoms(startDate?: string, endDate?: string): Promise {\n let query = `SELECT * FROM ${SYMPTOMS_TABLE}`;\n const params = [];\n \n if (startDate && endDate) {\n query += ` WHERE date BETWEEN ? AND ?`;\n params.push(startDate, endDate);\n } else if (startDate) {\n query += ` WHERE date >= ?`;\n params.push(startDate);\n }\n \n query += ` ORDER BY date DESC`;\n \n const result = await sqlite.execute(query, params);\n return result.map((row: any) => ({\n ...row,\n symptoms: JSON.parse(row.symptoms || '[]')\n })) as Symptom[];\n}\n\nexport async function getSymptomByDate(date: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${SYMPTOMS_TABLE} WHERE date = ?`,\n [date]\n );\n \n if (!result[0]) return null;\n \n const row = result[0] as any;\n return {\n ...row,\n symptoms: JSON.parse(row.symptoms || '[]')\n };\n}\n\n// Analytics and predictions\nexport async function calculateCyclePrediction(): Promise {\n const cycles = await getCycles(6); // Get last 6 cycles for prediction\n \n if (cycles.length < 2) return null;\n \n // Calculate average cycle length\n const completedCycles = cycles.filter(c => c.end_date);\n if (completedCycles.length < 2) return null;\n \n const cycleLengths = [];\n const periodLengths = [];\n \n for (let i = 0; i < completedCycles.length - 1; i++) {\n const current = new Date(completedCycles[i].start_date);\n const next = new Date(completedCycles[i + 1].start_date);\n const cycleLength = Math.abs((current.getTime() - next.getTime()) / (1000 * 60 * 60 * 24));\n cycleLengths.push(cycleLength);\n }\n \n completedCycles.forEach(cycle => {\n if (cycle.end_date) {\n const start = new Date(cycle.start_date);\n const end = new Date(cycle.end_date);\n const periodLength = Math.abs((end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24)) + 1;\n periodLengths.push(periodLength);\n }\n });\n \n const avgCycleLength = Math.round(cycleLengths.reduce((a, b) => a + b, 0) / cycleLengths.length);\n const avgPeriodLength = Math.round(periodLengths.reduce((a, b) => a + b, 0) / periodLengths.length);\n \n // Predict next period based on last cycle\n const lastCycle = cycles[0];\n const lastPeriodStart = new Date(lastCycle.start_date);\n \n const nextPeriodStart = new Date(lastPeriodStart);\n nextPeriodStart.setDate(nextPeriodStart.getDate() + avgCycleLength);\n \n const nextPeriodEnd = new Date(nextPeriodStart);\n nextPeriodEnd.setDate(nextPeriodEnd.getDate() + avgPeriodLength - 1);\n \n // Calculate fertile window (typically 5 days before ovulation + ovulation day)\n const ovulationDate = new Date(nextPeriodStart);\n ovulationDate.setDate(ovulationDate.getDate() - 14); // Ovulation typically 14 days before period\n \n const fertileStart = new Date(ovulationDate);\n fertileStart.setDate(fertileStart.getDate() - 5);\n \n const fertileEnd = new Date(ovulationDate);\n fertileEnd.setDate(fertileEnd.getDate() + 1);\n \n return {\n next_period_start: nextPeriodStart.toISOString().split('T')[0],\n next_period_end: nextPeriodEnd.toISOString().split('T')[0],\n fertile_window_start: fertileStart.toISOString().split('T')[0],\n fertile_window_end: fertileEnd.toISOString().split('T')[0],\n ovulation_date: ovulationDate.toISOString().split('T')[0],\n cycle_length: avgCycleLength,\n period_length: avgPeriodLength\n };\n}\n\nexport async function getCycleInsights(): Promise {\n const cycles = await getCycles();\n const symptoms = await getSymptoms();\n \n // Calculate cycle statistics\n const completedCycles = cycles.filter(c => c.end_date);\n const cycleLengths = [];\n const periodLengths = [];\n \n for (let i = 0; i < completedCycles.length - 1; i++) {\n const current = new Date(completedCycles[i].start_date);\n const next = new Date(completedCycles[i + 1].start_date);\n const cycleLength = Math.abs((current.getTime() - next.getTime()) / (1000 * 60 * 60 * 24));\n cycleLengths.push(cycleLength);\n }\n \n completedCycles.forEach(cycle => {\n if (cycle.end_date) {\n const start = new Date(cycle.start_date);\n const end = new Date(cycle.end_date);\n const periodLength = Math.abs((end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24)) + 1;\n periodLengths.push(periodLength);\n }\n });\n \n const avgCycleLength = cycleLengths.length > 0 \n ? cycleLengths.reduce((a, b) => a + b, 0) / cycleLengths.length \n : 0;\n \n const avgPeriodLength = periodLengths.length > 0\n ? periodLengths.reduce((a, b) => a + b, 0) / periodLengths.length\n : 0;\n \n // Determine regularity\n const cycleVariation = cycleLengths.length > 1\n ? Math.max(...cycleLengths) - Math.min(...cycleLengths)\n : 0;\n \n let regularity: 'regular' | 'irregular' | 'unknown' = 'unknown';\n if (cycleLengths.length >= 3) {\n regularity = cycleVariation <= 7 ? 'regular' : 'irregular';\n }\n \n // Most common symptoms\n const allSymptoms = symptoms.flatMap(s => s.symptoms || []);\n const symptomCounts = allSymptoms.reduce((acc, symptom) => {\n acc[symptom] = (acc[symptom] || 0) + 1;\n return acc;\n }, {} as Record);\n \n const mostCommonSymptoms = Object.entries(symptomCounts)\n .sort(([,a], [,b]) => b - a)\n .slice(0, 5)\n .map(([symptom]) => symptom);\n \n // Mood and pain trends (last 30 days)\n const thirtyDaysAgo = new Date();\n thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);\n const recentSymptoms = symptoms.filter(s => new Date(s.date) >= thirtyDaysAgo);\n \n const moodTrends = recentSymptoms\n .filter(s => s.mood)\n .map(s => ({ date: s.date, mood: s.mood }))\n .sort((a, b) => a.date.localeCompare(b.date));\n \n const painTrends = recentSymptoms\n .filter(s => s.pain_level)\n .map(s => ({ date: s.date, pain: s.pain_level }))\n .sort((a, b) => a.date.localeCompare(b.date));\n \n return {\n average_cycle_length: Math.round(avgCycleLength * 10) / 10,\n average_period_length: Math.round(avgPeriodLength * 10) / 10,\n cycle_regularity: regularity,\n most_common_symptoms: mostCommonSymptoms,\n mood_trends: moodTrends,\n pain_trends: painTrends\n };\n}","deleted","label",[169,170,152],"backend","database","permalink","https://www.val.town/x/MrsJD/public/code/backend/database/queries.ts","extension","actionData","errors"]