[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","1f1a009cadcf7353a37b999b92a7f8af633192ea","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_155":156,"_177":178,"_179":-5},"handle","nezha","name","To-do_Tracker","path","backend/database/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_141":142,"_154":20},"id","b07a44d6-3fab-11f0-ba59-76b3cceeab13","createdAt",["D",1748866708122],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","5afa7900-3faa-11f0-b6e9-ab0b2f88b858","pinnedFileId","bcf1b310-3fac-11f0-9459-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","865b9509-db67-4d28-b880-28866a98d4f9","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4eDNvdDEydVlzWmJ1TlNwTGtUQ2RwaW1mMyJ9","org",{"_95":96,"_138":135,"_12":113,"_139":140},"avatar","tier","free","branch",{"_143":144,"_97":45,"_103":145,"_146":104,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":98,"_153":96},"version",17,"b07a8cf2-3fab-11f0-ba59-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":157,"_97":158,"_159":160,"_161":162,"_163":145,"_164":165,"_166":117,"_105":167,"_143":33,"_168":169,"_170":171,"_172":20,"_173":-5,"_99":174},"98ef9590-3fac-11f0-941e-76b3cceeab13","queries.ts","fileId","98ef9662-3fac-11f0-941e-76b3cceeab13","parentId","42109c35-7191-458a-8d45-78a1490258fb","branchId","branchVersion",5,"latest",["D",1748867098119],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TODOS_TABLE, HABITS_TABLE, HABIT_COMPLETIONS_TABLE } from \"./migrations.ts\";\nimport type { \n Todo, \n CreateTodoRequest, \n UpdateTodoRequest,\n Habit,\n CreateHabitRequest,\n UpdateHabitRequest,\n HabitCompletion,\n HabitWithCompletions\n} from \"../../shared/types.ts\";\n\n// Todo Queries\nexport async function getAllTodos(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${TODOS_TABLE} \n ORDER BY \n CASE priority \n WHEN 'high' THEN 1 \n WHEN 'medium' THEN 2 \n WHEN 'low' THEN 3 \n END,\n created_at DESC\n `);\n return result.rows as Todo[];\n}\n\nexport async function getTodoById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${TODOS_TABLE} WHERE id = ?\n `, [id]);\n return result.rows[0] as Todo || null;\n}\n\nexport async function createTodo(todo: CreateTodoRequest): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(`\n INSERT INTO ${TODOS_TABLE} (title, description, priority, due_date, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [\n todo.title,\n todo.description || null,\n todo.priority || 'medium',\n todo.due_date || null,\n now,\n now\n ]);\n \n return await getTodoById(result.lastInsertRowId!) as Todo;\n}\n\nexport async function updateTodo(id: number, updates: UpdateTodoRequest): Promise {\n const existing = await getTodoById(id);\n if (!existing) return null;\n\n const now = new Date().toISOString();\n await sqlite.execute(`\n UPDATE ${TODOS_TABLE} \n SET title = ?, description = ?, completed = ?, priority = ?, due_date = ?, updated_at = ?\n WHERE id = ?\n `, [\n updates.title ?? existing.title,\n updates.description ?? existing.description,\n updates.completed ?? existing.completed,\n updates.priority ?? existing.priority,\n updates.due_date ?? existing.due_date,\n now,\n id\n ]);\n\n return await getTodoById(id);\n}\n\nexport async function deleteTodo(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${TODOS_TABLE} WHERE id = ?\n `, [id]);\n return result.changes > 0;\n}\n\n// Habit Queries\nexport async function getAllHabits(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${HABITS_TABLE} \n ORDER BY created_at DESC\n `);\n return result.rows as Habit[];\n}\n\nexport async function getHabitById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${HABITS_TABLE} WHERE id = ?\n `, [id]);\n return result.rows[0] as Habit || null;\n}\n\nexport async function createHabit(habit: CreateHabitRequest): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(`\n INSERT INTO ${HABITS_TABLE} (name, description, color, target_frequency, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [\n habit.name,\n habit.description || null,\n habit.color || '#3B82F6',\n habit.target_frequency || 7,\n now,\n now\n ]);\n \n return await getHabitById(result.lastInsertRowId!) as Habit;\n}\n\nexport async function updateHabit(id: number, updates: UpdateHabitRequest): Promise {\n const existing = await getHabitById(id);\n if (!existing) return null;\n\n const now = new Date().toISOString();\n await sqlite.execute(`\n UPDATE ${HABITS_TABLE} \n SET name = ?, description = ?, color = ?, target_frequency = ?, updated_at = ?\n WHERE id = ?\n `, [\n updates.name ?? existing.name,\n updates.description ?? existing.description,\n updates.color ?? existing.color,\n updates.target_frequency ?? existing.target_frequency,\n now,\n id\n ]);\n\n return await getHabitById(id);\n}\n\nexport async function deleteHabit(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${HABITS_TABLE} WHERE id = ?\n `, [id]);\n return result.changes > 0;\n}\n\n// Habit Completion Queries\nexport async function getHabitCompletions(habitId: number, startDate?: string, endDate?: string): Promise {\n let query = `\n SELECT * FROM ${HABIT_COMPLETIONS_TABLE} \n WHERE habit_id = ?\n `;\n const params: any[] = [habitId];\n\n if (startDate) {\n query += ` AND completion_date >= ?`;\n params.push(startDate);\n }\n \n if (endDate) {\n query += ` AND completion_date <= ?`;\n params.push(endDate);\n }\n\n query += ` ORDER BY completion_date DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows as HabitCompletion[];\n}\n\nexport async function markHabitComplete(habitId: number, date: string): Promise {\n const now = new Date().toISOString();\n \n // Use INSERT OR REPLACE to handle duplicates\n const result = await sqlite.execute(`\n INSERT OR REPLACE INTO ${HABIT_COMPLETIONS_TABLE} (habit_id, completion_date, created_at)\n VALUES (?, ?, ?)\n `, [habitId, date, now]);\n\n const completion = await sqlite.execute(`\n SELECT * FROM ${HABIT_COMPLETIONS_TABLE} \n WHERE habit_id = ? AND completion_date = ?\n `, [habitId, date]);\n\n return completion.rows[0] as HabitCompletion;\n}\n\nexport async function removeHabitCompletion(habitId: number, date: string): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${HABIT_COMPLETIONS_TABLE} \n WHERE habit_id = ? AND completion_date = ?\n `, [habitId, date]);\n return result.changes > 0;\n}\n\nexport async function getHabitsWithCompletions(): Promise {\n const habits = await getAllHabits();\n const habitsWithCompletions: HabitWithCompletions[] = [];\n\n for (const habit of habits) {\n // Get completions for the last 30 days\n const thirtyDaysAgo = new Date();\n thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);\n const startDate = thirtyDaysAgo.toISOString().split('T')[0];\n \n const completions = await getHabitCompletions(habit.id, startDate);\n \n // Calculate current streak\n const currentStreak = calculateCurrentStreak(completions);\n \n // Calculate completion rate for current week\n const weekStart = new Date();\n weekStart.setDate(weekStart.getDate() - weekStart.getDay());\n const weekStartStr = weekStart.toISOString().split('T')[0];\n \n const weekCompletions = completions.filter(c => c.completion_date >= weekStartStr);\n const completionRate = Math.round((weekCompletions.length / habit.target_frequency) * 100);\n\n habitsWithCompletions.push({\n ...habit,\n completions,\n current_streak: currentStreak,\n completion_rate: Math.min(completionRate, 100)\n });\n }\n\n return habitsWithCompletions;\n}\n\nfunction calculateCurrentStreak(completions: HabitCompletion[]): number {\n if (completions.length === 0) return 0;\n\n // Sort completions by date (most recent first)\n const sortedCompletions = completions\n .map(c => c.completion_date)\n .sort((a, b) => b.localeCompare(a));\n\n const today = new Date().toISOString().split('T')[0];\n const yesterday = new Date();\n yesterday.setDate(yesterday.getDate() - 1);\n const yesterdayStr = yesterday.toISOString().split('T')[0];\n\n // Check if today or yesterday is completed (streak can continue)\n if (!sortedCompletions.includes(today) && !sortedCompletions.includes(yesterdayStr)) {\n return 0;\n }\n\n let streak = 0;\n let currentDate = new Date();\n\n // Count consecutive days backwards from today\n for (let i = 0; i < 365; i++) { // Max 365 days to prevent infinite loop\n const dateStr = currentDate.toISOString().split('T')[0];\n \n if (sortedCompletions.includes(dateStr)) {\n streak++;\n } else {\n break;\n }\n \n currentDate.setDate(currentDate.getDate() - 1);\n }\n\n return streak;\n}","deleted","label",[175,176,158],"backend","database","permalink","https://www.val.town/x/nezha/To-do_Tracker/code/backend/database/queries.ts","extension","actionData","errors"]