[{"_1":2,"_172":-5,"_173":-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","40d33e9c7bacd1f32863183408ee0e4ae3e5f5a3","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,"_146":147,"_169":170,"_171":-5},"handle","stevekrouse","name","complex-test-app","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","df5ec440-998b-11f0-ad22-0224a6c84d84","createdAt",["D",1758748647548],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","a0bf3b31-15a5-4d5c-880e-4b1e22c9bc18","pinnedFileId","pinPreview","screenshotVersion","likeCount","e81e7335-6d4c-4764-a902-d0962bbfa9f7","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://images.clerk.dev/uploaded/img_2PqHa2Gsy93xQrjh2w78Xu0cChW.jpeg","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",6,"df5f17e2-998b-11f0-ad22-0224a6c84d84","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"735e1236-998c-11f0-a5be-0224a6c84d84","queries.ts","fileId","735e1308-998c-11f0-a5be-0224a6c84d84","parentId","1c53d3ab-3ee6-45c5-a867-136290e2adeb","branchId","branchVersion",5,"latest",true,["D",1758748895842],"fileType","script","content","// Database query functions for the Todo App\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { \n Todo, Category, Tag, TodoTag, Attachment, Goal, TimeLog, \n Statistics, UserPreferences, Achievement, TodoFilters,\n CreateTodoRequest, UpdateTodoRequest, CreateCategoryRequest,\n CreateTagRequest, CreateGoalRequest, BulkOperation\n} from \"../../shared/types.ts\";\n\n// Todo queries\nexport async function getAllTodos(filters?: TodoFilters): Promise {\n let query = `\n SELECT t.*, c.name as category_name, c.color as category_color\n FROM todos_v1 t\n LEFT JOIN categories_v1 c ON t.category_id = c.id\n WHERE 1=1\n `;\n const params: any[] = [];\n \n if (filters) {\n if (filters.search) {\n query += ` AND (t.title LIKE ? OR t.description LIKE ?)`;\n params.push(`%${filters.search}%`, `%${filters.search}%`);\n }\n \n if (filters.category_id !== null && filters.category_id !== undefined) {\n query += ` AND t.category_id = ?`;\n params.push(filters.category_id);\n }\n \n if (filters.priority) {\n query += ` AND t.priority = ?`;\n params.push(filters.priority);\n }\n \n if (filters.completed !== null && filters.completed !== undefined) {\n query += ` AND t.completed = ?`;\n params.push(filters.completed);\n }\n \n if (filters.due_date_range.start) {\n query += ` AND t.due_date >= ?`;\n params.push(filters.due_date_range.start);\n }\n \n if (filters.due_date_range.end) {\n query += ` AND t.due_date <= ?`;\n params.push(filters.due_date_range.end);\n }\n \n if (filters.has_attachments !== null && filters.has_attachments !== undefined) {\n if (filters.has_attachments) {\n query += ` AND EXISTS (SELECT 1 FROM attachments_v1 WHERE todo_id = t.id)`;\n } else {\n query += ` AND NOT EXISTS (SELECT 1 FROM attachments_v1 WHERE todo_id = t.id)`;\n }\n }\n \n if (filters.overdue_only) {\n query += ` AND t.due_date < datetime('now') AND t.completed = FALSE`;\n }\n \n if (filters.tags && filters.tags.length > 0) {\n query += ` AND EXISTS (\n SELECT 1 FROM todo_tags_v1 tt \n WHERE tt.todo_id = t.id AND tt.tag_id IN (${filters.tags.map(() => '?').join(',')})\n )`;\n params.push(...filters.tags);\n }\n }\n \n query += ` ORDER BY t.position ASC, t.created_at DESC`;\n \n const { rows } = await sqlite.execute(query, params);\n return rows as Todo[];\n}\n\nexport async function getTodoById(id: number): Promise {\n const { rows } = await sqlite.execute(`\n SELECT t.*, c.name as category_name, c.color as category_color\n FROM todos_v1 t\n LEFT JOIN categories_v1 c ON t.category_id = c.id\n WHERE t.id = ?\n `, [id]);\n \n return rows.length > 0 ? rows[0] as Todo : null;\n}\n\nexport async function createTodo(todo: CreateTodoRequest): Promise {\n const { rows } = await sqlite.execute(`\n INSERT INTO todos_v1 (\n title, description, priority, category_id, due_date, \n estimated_minutes, color, reminder_at, recurring_pattern, \n parent_id, progress\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n RETURNING *\n `, [\n todo.title,\n todo.description || '',\n todo.priority,\n todo.category_id,\n todo.due_date,\n todo.estimated_minutes,\n todo.color,\n todo.reminder_at,\n todo.recurring_pattern,\n todo.parent_id,\n todo.progress || 0\n ]);\n \n return rows[0] as Todo;\n}\n\nexport async function updateTodo(id: number, updates: UpdateTodoRequest): Promise {\n const fields = [];\n const values = [];\n \n for (const [key, value] of Object.entries(updates)) {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n }\n \n if (fields.length === 0) {\n return getTodoById(id);\n }\n \n values.push(id);\n \n await sqlite.execute(`\n UPDATE todos_v1 SET ${fields.join(', ')} WHERE id = ?\n `, values);\n \n return getTodoById(id);\n}\n\nexport async function deleteTodo(id: number): Promise {\n const { changes } = await sqlite.execute(`DELETE FROM todos_v1 WHERE id = ?`, [id]);\n return changes > 0;\n}\n\nexport async function toggleTodoCompletion(id: number): Promise {\n await sqlite.execute(`\n UPDATE todos_v1 \n SET completed = NOT completed,\n completed_at = CASE WHEN NOT completed THEN CURRENT_TIMESTAMP ELSE NULL END\n WHERE id = ?\n `, [id]);\n \n return getTodoById(id);\n}\n\nexport async function updateTodoPositions(todoIds: number[]): Promise {\n for (let i = 0; i < todoIds.length; i++) {\n await sqlite.execute(`\n UPDATE todos_v1 SET position = ? WHERE id = ?\n `, [i + 1, todoIds[i]]);\n }\n}\n\n// Category queries\nexport async function getAllCategories(): Promise {\n const { rows } = await sqlite.execute(`\n SELECT c.*, COUNT(t.id) as todo_count\n FROM categories_v1 c\n LEFT JOIN todos_v1 t ON c.id = t.category_id\n GROUP BY c.id, c.name, c.color, c.icon, c.created_at\n ORDER BY c.name ASC\n `);\n \n return rows as Category[];\n}\n\nexport async function getCategoryById(id: number): Promise {\n const { rows } = await sqlite.execute(`\n SELECT c.*, COUNT(t.id) as todo_count\n FROM categories_v1 c\n LEFT JOIN todos_v1 t ON c.id = t.category_id\n WHERE c.id = ?\n GROUP BY c.id, c.name, c.color, c.icon, c.created_at\n `, [id]);\n \n return rows.length > 0 ? rows[0] as Category : null;\n}\n\nexport async function createCategory(category: CreateCategoryRequest): Promise {\n const { rows } = await sqlite.execute(`\n INSERT INTO categories_v1 (name, color, icon)\n VALUES (?, ?, ?)\n RETURNING *\n `, [category.name, category.color, category.icon]);\n \n const newCategory = rows[0] as Category;\n newCategory.todo_count = 0;\n return newCategory;\n}\n\nexport async function updateCategory(id: number, updates: Partial): Promise {\n const fields = [];\n const values = [];\n \n for (const [key, value] of Object.entries(updates)) {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n }\n \n if (fields.length === 0) {\n return getCategoryById(id);\n }\n \n values.push(id);\n \n await sqlite.execute(`\n UPDATE categories_v1 SET ${fields.join(', ')} WHERE id = ?\n `, values);\n \n return getCategoryById(id);\n}\n\nexport async function deleteCategory(id: number): Promise {\n const { changes } = await sqlite.execute(`DELETE FROM categories_v1 WHERE id = ?`, [id]);\n return changes > 0;\n}\n\n// Tag queries\nexport async function getAllTags(): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM tags_v1 ORDER BY name ASC\n `);\n \n return rows as Tag[];\n}\n\nexport async function getTagsForTodo(todoId: number): Promise {\n const { rows } = await sqlite.execute(`\n SELECT t.* FROM tags_v1 t\n INNER JOIN todo_tags_v1 tt ON t.id = tt.tag_id\n WHERE tt.todo_id = ?\n ORDER BY t.name ASC\n `, [todoId]);\n \n return rows as Tag[];\n}\n\nexport async function createTag(tag: CreateTagRequest): Promise {\n const { rows } = await sqlite.execute(`\n INSERT INTO tags_v1 (name, color)\n VALUES (?, ?)\n RETURNING *\n `, [tag.name, tag.color]);\n \n return rows[0] as Tag;\n}\n\nexport async function addTagToTodo(todoId: number, tagId: number): Promise {\n await sqlite.execute(`\n INSERT OR IGNORE INTO todo_tags_v1 (todo_id, tag_id)\n VALUES (?, ?)\n `, [todoId, tagId]);\n}\n\nexport async function removeTagFromTodo(todoId: number, tagId: number): Promise {\n await sqlite.execute(`\n DELETE FROM todo_tags_v1 WHERE todo_id = ? AND tag_id = ?\n `, [todoId, tagId]);\n}\n\nexport async function setTodoTags(todoId: number, tagIds: number[]): Promise {\n // Remove existing tags\n await sqlite.execute(`DELETE FROM todo_tags_v1 WHERE todo_id = ?`, [todoId]);\n \n // Add new tags\n for (const tagId of tagIds) {\n await addTagToTodo(todoId, tagId);\n }\n}\n\n// Goal queries\nexport async function getAllGoals(): Promise {\n const { rows } = await sqlite.execute(`\n SELECT g.*, c.name as category_name\n FROM goals_v1 g\n LEFT JOIN categories_v1 c ON g.category_id = c.id\n ORDER BY g.target_date ASC, g.created_at DESC\n `);\n \n return rows as Goal[];\n}\n\nexport async function createGoal(goal: CreateGoalRequest): Promise {\n const { rows } = await sqlite.execute(`\n INSERT INTO goals_v1 (title, description, target_count, target_date, category_id)\n VALUES (?, ?, ?, ?, ?)\n RETURNING *\n `, [goal.title, goal.description, goal.target_count, goal.target_date, goal.category_id]);\n \n return rows[0] as Goal;\n}\n\nexport async function updateGoalProgress(goalId: number): Promise {\n // This would be called when todos are completed to update goal progress\n // Implementation depends on specific goal criteria\n await sqlite.execute(`\n UPDATE goals_v1 \n SET current_count = (\n SELECT COUNT(*) FROM todos_v1 \n WHERE completed = TRUE \n AND created_at >= (SELECT created_at FROM goals_v1 WHERE id = ?)\n AND (category_id = (SELECT category_id FROM goals_v1 WHERE id = ?) OR (SELECT category_id FROM goals_v1 WHERE id = ?) IS NULL)\n ),\n completed = (current_count >= target_count)\n WHERE id = ?\n `, [goalId, goalId, goalId, goalId]);\n}\n\n// Statistics queries\nexport async function getStatistics(): Promise {\n // Basic counts\n const { rows: basicStats } = await sqlite.execute(`\n SELECT \n COUNT(*) as total_todos,\n SUM(CASE WHEN completed = TRUE THEN 1 ELSE 0 END) as completed_todos,\n SUM(CASE WHEN completed = FALSE THEN 1 ELSE 0 END) as pending_todos,\n SUM(CASE WHEN completed = FALSE AND due_date < datetime('now') THEN 1 ELSE 0 END) as overdue_todos\n FROM todos_v1\n `);\n \n const basic = basicStats[0];\n const completionRate = basic.total_todos > 0 ? Math.round((basic.completed_todos / basic.total_todos) * 100) : 0;\n \n // Average completion time\n const { rows: avgTime } = await sqlite.execute(`\n SELECT AVG(\n CASE WHEN completed_at IS NOT NULL AND created_at IS NOT NULL \n THEN (julianday(completed_at) - julianday(created_at)) \n ELSE NULL END\n ) as avg_days\n FROM todos_v1\n WHERE completed = TRUE\n `);\n \n const averageCompletionTime = avgTime[0].avg_days ? Math.round(avgTime[0].avg_days) : 0;\n \n // Most productive hour (mock data for now)\n const mostProductiveHour = 10; // 10 AM\n \n // Streak calculation (simplified)\n const { rows: recentCompleted } = await sqlite.execute(`\n SELECT DATE(completed_at) as completion_date, COUNT(*) as count\n FROM todos_v1\n WHERE completed = TRUE AND completed_at IS NOT NULL\n GROUP BY DATE(completed_at)\n ORDER BY completion_date DESC\n LIMIT 30\n `);\n \n let streakDays = 0;\n const today = new Date().toISOString().split('T')[0];\n let currentDate = new Date();\n \n for (const row of recentCompleted) {\n const completionDate = row.completion_date;\n const expectedDate = currentDate.toISOString().split('T')[0];\n \n if (completionDate === expectedDate) {\n streakDays++;\n currentDate.setDate(currentDate.getDate() - 1);\n } else {\n break;\n }\n }\n \n // Category stats\n const { rows: categoryStats } = await sqlite.execute(`\n SELECT \n c.id as category_id,\n c.name as category_name,\n COUNT(t.id) as total,\n SUM(CASE WHEN t.completed = TRUE THEN 1 ELSE 0 END) as completed\n FROM categories_v1 c\n LEFT JOIN todos_v1 t ON c.id = t.category_id\n GROUP BY c.id, c.name\n HAVING total > 0\n ORDER BY total DESC\n `);\n \n const categoriesStats = categoryStats.map(row => ({\n category_id: row.category_id,\n category_name: row.category_name,\n total: row.total,\n completed: row.completed,\n completion_rate: row.total > 0 ? Math.round((row.completed / row.total) * 100) : 0\n }));\n \n // Priority stats\n const { rows: priorityStats } = await sqlite.execute(`\n SELECT \n priority,\n COUNT(*) as total,\n SUM(CASE WHEN completed = TRUE THEN 1 ELSE 0 END) as completed,\n AVG(CASE WHEN completed = TRUE AND completed_at IS NOT NULL AND created_at IS NOT NULL \n THEN (julianday(completed_at) - julianday(created_at)) \n ELSE NULL END) as avg_completion_days\n FROM todos_v1\n GROUP BY priority\n ORDER BY \n CASE priority \n WHEN 'urgent' THEN 4 \n WHEN 'high' THEN 3 \n WHEN 'medium' THEN 2 \n WHEN 'low' THEN 1 \n END DESC\n `);\n \n const priorityStatsFormatted = priorityStats.map(row => ({\n priority: row.priority,\n total: row.total,\n completed: row.completed,\n average_completion_time: row.avg_completion_days ? Math.round(row.avg_completion_days) : 0\n }));\n \n // Weekly progress (last 8 weeks)\n const { rows: weeklyData } = await sqlite.execute(`\n SELECT \n DATE(created_at, 'weekday 0', '-6 days') as week_start,\n COUNT(*) as created,\n SUM(CASE WHEN completed = TRUE THEN 1 ELSE 0 END) as completed\n FROM todos_v1\n WHERE created_at >= datetime('now', '-8 weeks')\n GROUP BY DATE(created_at, 'weekday 0', '-6 days')\n ORDER BY week_start ASC\n `);\n \n const weeklyProgress = weeklyData.map(row => ({\n week_start: row.week_start,\n created: row.created,\n completed: row.completed,\n completion_rate: row.created > 0 ? Math.round((row.completed / row.created) * 100) : 0\n }));\n \n // Monthly summary\n const { rows: monthlyData } = await sqlite.execute(`\n SELECT \n COUNT(*) as total_completed,\n COUNT(DISTINCT DATE(completed_at)) as active_days\n FROM todos_v1\n WHERE completed = TRUE \n AND completed_at >= datetime('now', 'start of month')\n `);\n \n const { rows: monthlyCreated } = await sqlite.execute(`\n SELECT COUNT(*) as total_created\n FROM todos_v1\n WHERE created_at >= datetime('now', 'start of month')\n `);\n \n const monthlySummary = {\n month: new Date().toLocaleDateString('en-US', { year: 'numeric', month: 'long' }),\n total_completed: monthlyData[0].total_completed,\n total_created: monthlyCreated[0].total_created,\n most_productive_day: 'Monday', // Mock data\n average_daily_completion: monthlyData[0].active_days > 0 ? \n Math.round(monthlyData[0].total_completed / monthlyData[0].active_days) : 0\n };\n \n return {\n total_todos: basic.total_todos,\n completed_todos: basic.completed_todos,\n pending_todos: basic.pending_todos,\n overdue_todos: basic.overdue_todos,\n completion_rate: completionRate,\n average_completion_time: averageCompletionTime,\n most_productive_hour: mostProductiveHour,\n streak_days: streakDays,\n categories_stats: categoriesStats,\n priority_stats: priorityStatsFormatted,\n weekly_progress: weeklyProgress,\n monthly_summary: monthlySummary\n };\n}\n\n// Bulk operations\nexport async function executeBulkOperation(operation: BulkOperation): Promise {\n const todoIds = operation.todo_ids;\n const placeholders = todoIds.map(() => '?').join(',');\n \n switch (operation.action) {\n case 'complete':\n await sqlite.execute(`\n UPDATE todos_v1 \n SET completed = TRUE, completed_at = CURRENT_TIMESTAMP \n WHERE id IN (${placeholders})\n `, todoIds);\n break;\n \n case 'delete':\n await sqlite.execute(`\n DELETE FROM todos_v1 WHERE id IN (${placeholders})\n `, todoIds);\n break;\n \n case 'move_category':\n if (operation.params?.category_id !== undefined) {\n await sqlite.execute(`\n UPDATE todos_v1 \n SET category_id = ? \n WHERE id IN (${placeholders})\n `, [operation.params.category_id, ...todoIds]);\n }\n break;\n \n case 'add_tag':\n if (operation.params?.tag_id) {\n for (const todoId of todoIds) {\n await addTagToTodo(todoId, operation.params.tag_id);\n }\n }\n break;\n \n case 'set_priority':\n if (operation.params?.priority) {\n await sqlite.execute(`\n UPDATE todos_v1 \n SET priority = ? \n WHERE id IN (${placeholders})\n `, [operation.params.priority, ...todoIds]);\n }\n break;\n }\n}\n\n// User preferences\nexport async function getUserPreferences(): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM user_preferences_v1 WHERE id = 1\n `);\n \n if (rows.length === 0) {\n // Return default preferences\n return {\n theme: 'light',\n view_mode: 'list',\n sort_by: 'created_date',\n sort_direction: 'desc',\n items_per_page: 20,\n show_completed: true,\n auto_save: true,\n notifications: {\n enabled: true,\n due_date_reminders: true,\n daily_summary: false,\n achievement_alerts: true,\n overdue_alerts: true,\n reminder_minutes_before: 60\n },\n default_category_id: null,\n default_priority: 'medium',\n work_hours: {\n start: '09:00',\n end: '17:00'\n }\n } as UserPreferences;\n }\n \n const prefs = rows[0];\n return {\n theme: prefs.theme,\n view_mode: prefs.view_mode,\n sort_by: prefs.sort_by,\n sort_direction: prefs.sort_direction,\n items_per_page: prefs.items_per_page,\n show_completed: prefs.show_completed,\n auto_save: prefs.auto_save,\n notifications: {\n enabled: prefs.notifications_enabled,\n due_date_reminders: prefs.due_date_reminders,\n daily_summary: prefs.daily_summary,\n achievement_alerts: prefs.achievement_alerts,\n overdue_alerts: prefs.overdue_alerts,\n reminder_minutes_before: prefs.reminder_minutes_before\n },\n default_category_id: prefs.default_category_id,\n default_priority: prefs.default_priority,\n work_hours: {\n start: prefs.work_hours_start,\n end: prefs.work_hours_end\n }\n } as UserPreferences;\n}\n\nexport async function updateUserPreferences(preferences: Partial): Promise {\n const fields = [];\n const values = [];\n \n if (preferences.theme) {\n fields.push('theme = ?');\n values.push(preferences.theme);\n }\n \n if (preferences.view_mode) {\n fields.push('view_mode = ?');\n values.push(preferences.view_mode);\n }\n \n if (preferences.sort_by) {\n fields.push('sort_by = ?');\n values.push(preferences.sort_by);\n }\n \n if (preferences.sort_direction) {\n fields.push('sort_direction = ?');\n values.push(preferences.sort_direction);\n }\n \n if (preferences.items_per_page) {\n fields.push('items_per_page = ?');\n values.push(preferences.items_per_page);\n }\n \n if (preferences.show_completed !== undefined) {\n fields.push('show_completed = ?');\n values.push(preferences.show_completed);\n }\n \n if (preferences.auto_save !== undefined) {\n fields.push('auto_save = ?');\n values.push(preferences.auto_save);\n }\n \n if (preferences.notifications) {\n if (preferences.notifications.enabled !== undefined) {\n fields.push('notifications_enabled = ?');\n values.push(preferences.notifications.enabled);\n }\n if (preferences.notifications.due_date_reminders !== undefined) {\n fields.push('due_date_reminders = ?');\n values.push(preferences.notifications.due_date_reminders);\n }\n if (preferences.notifications.daily_summary !== undefined) {\n fields.push('daily_summary = ?');\n values.push(preferences.notifications.daily_summary);\n }\n if (preferences.notifications.achievement_alerts !== undefined) {\n fields.push('achievement_alerts = ?');\n values.push(preferences.notifications.achievement_alerts);\n }\n if (preferences.notifications.overdue_alerts !== undefined) {\n fields.push('overdue_alerts = ?');\n values.push(preferences.notifications.overdue_alerts);\n }\n if (preferences.notifications.reminder_minutes_before !== undefined) {\n fields.push('reminder_minutes_before = ?');\n values.push(preferences.notifications.reminder_minutes_before);\n }\n }\n \n if (preferences.default_category_id !== undefined) {\n fields.push('default_category_id = ?');\n values.push(preferences.default_category_id);\n }\n \n if (preferences.default_priority) {\n fields.push('default_priority = ?');\n values.push(preferences.default_priority);\n }\n \n if (preferences.work_hours) {\n if (preferences.work_hours.start) {\n fields.push('work_hours_start = ?');\n values.push(preferences.work_hours.start);\n }\n if (preferences.work_hours.end) {\n fields.push('work_hours_end = ?');\n values.push(preferences.work_hours.end);\n }\n }\n \n if (fields.length > 0) {\n await sqlite.execute(`\n UPDATE user_preferences_v1 SET ${fields.join(', ')} WHERE id = 1\n `, values);\n }\n \n return getUserPreferences();\n}\n\n// Achievements\nexport async function getAchievements(): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM achievements_v1 ORDER BY unlocked DESC, id ASC\n `);\n \n return rows as Achievement[];\n}\n\nexport async function updateAchievementProgress(achievementId: string, progress: number): Promise {\n await sqlite.execute(`\n UPDATE achievements_v1 \n SET progress = ?,\n unlocked = CASE WHEN progress >= target THEN TRUE ELSE unlocked END,\n unlocked_at = CASE WHEN progress >= target AND unlocked = FALSE THEN CURRENT_TIMESTAMP ELSE unlocked_at END\n WHERE id = ?\n `, [progress, achievementId]);\n}\n\n// Time tracking\nexport async function startTimeTracking(todoId: number, description?: string): Promise {\n const { rows } = await sqlite.execute(`\n INSERT INTO time_logs_v1 (todo_id, start_time, description)\n VALUES (?, CURRENT_TIMESTAMP, ?)\n RETURNING *\n `, [todoId, description || null]);\n \n return rows[0] as TimeLog;\n}\n\nexport async function stopTimeTracking(timeLogId: number): Promise {\n await sqlite.execute(`\n UPDATE time_logs_v1 \n SET end_time = CURRENT_TIMESTAMP,\n duration_minutes = CAST((julianday(CURRENT_TIMESTAMP) - julianday(start_time)) * 24 * 60 AS INTEGER)\n WHERE id = ?\n `, [timeLogId]);\n \n const { rows } = await sqlite.execute(`\n SELECT * FROM time_logs_v1 WHERE id = ?\n `, [timeLogId]);\n \n return rows.length > 0 ? rows[0] as TimeLog : null;\n}\n\nexport async function getTimeLogsForTodo(todoId: number): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM time_logs_v1 \n WHERE todo_id = ? \n ORDER BY start_time DESC\n `, [todoId]);\n \n return rows as TimeLog[];\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/stevekrouse/complex-test-app/code/backend/database/queries.ts","extension","actionData","errors"]