[{"_1":2,"_180":-5,"_181":-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","a45694a1faeed7a48e6b6123449288e884a42bca","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,"_177":178,"_179":-5},"handle","shapedlines","name","carmy","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":73,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":116,"_118":-5,"_119":32,"_14":120,"_121":-5,"_122":-5,"_123":124,"_125":124,"_126":127,"_128":127,"_129":127,"_130":124,"_131":124,"_23":132,"_135":136,"_140":141,"_153":20},"id","75b56760-d3a6-11f0-86d1-42dde27851f2","createdAt",["D",1765137234131],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","7ed186b0-c78a-4757-a5b0-12c00ac89afa","pinnedFileId","b9153140-d3a9-11f0-8d63-42dde27851f2","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","e9195859-3c6b-4b40-bb3e-c11844477fdf","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_133":134},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yYlc2Q1A2eUFyNEdDeXBud0E0bFJyWlJVUzQifQ","org",{"_94":95,"_137":134,"_12":112,"_138":139},"avatar","tier","pro","branch",{"_142":143,"_96":44,"_102":144,"_145":103,"_146":-5,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":97,"_152":95},"version",241,"75b5bdc8-d3a6-11f0-86d1-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":156,"_96":157,"_158":159,"_160":161,"_162":144,"_163":164,"_165":116,"_104":166,"_142":167,"_168":169,"_170":171,"_172":20,"_173":-5,"_98":174},"efbee46c-eeb5-11f0-827f-42dde27851f2","queries.ts","fileId","b90da5f6-d3a9-11f0-a23f-42dde27851f2","parentId","b896776a-d3a9-11f0-8e4b-42dde27851f2","branchId","branchVersion",238,"latest",["D",1768112562714],12,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport {\n USERS_TABLE,\n SESSIONS_TABLE,\n RECIPES_TABLE,\n RECIPE_INGREDIENTS_TABLE,\n RECIPE_NUTRITION_TABLE,\n FDC_FOODS_CACHE_TABLE,\n GROCERY_LISTS_TABLE,\n GROCERY_LIST_ITEMS_TABLE,\n NUTRITION_GOALS_TABLE,\n MEAL_PLANS_TABLE,\n MEAL_PLAN_ENTRIES_TABLE,\n SCRAPER_EVAL_URLS_TABLE,\n STARRED_INGREDIENTS_TABLE,\n CUSTOM_FOODS_TABLE,\n} from \"./migrations.ts\";\nimport type {\n User,\n Recipe,\n RecipeIngredient,\n RecipeNutrition,\n GroceryList,\n GroceryListItem,\n NutritionGoals,\n MealPlan,\n MealPlanEntry,\n DayOfWeek,\n MealType,\n FDCDataType,\n CreateRecipeIngredient,\n StarredIngredient,\n CustomFood,\n CreateCustomFoodRequest,\n FDCFood,\n FDCNutrition,\n} from \"../../shared/types.ts\";\nimport { ALL_NUTRIENT_KEYS, DEFAULT_GROCERY_LIST_NAME } from \"../../shared/types.ts\";\nimport { calculateRecipeNutrition, convertToGrams } from \"../services/nutrition-calculator.ts\";\n\n// Generate unique IDs\nfunction generateId(): string {\n return crypto.randomUUID();\n}\n\n// ============ USER QUERIES ============\n\nexport async function getUserById(id: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, email, name, picture FROM ${USERS_TABLE} WHERE id = ?`,\n [id]\n );\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n return {\n id: row.id as string,\n email: row.email as string,\n name: row.name as string | null,\n picture: row.picture as string | null,\n };\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, email, name, picture FROM ${USERS_TABLE} WHERE email = ?`,\n [email]\n );\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n return {\n id: row.id as string,\n email: row.email as string,\n name: row.name as string | null,\n picture: row.picture as string | null,\n };\n}\n\nexport async function createOrUpdateUser(\n email: string,\n name: string | null,\n picture: string | null\n): Promise {\n const existing = await getUserByEmail(email);\n\n if (existing) {\n await sqlite.execute(\n `UPDATE ${USERS_TABLE} SET name = ?, picture = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [name, picture, existing.id]\n );\n return { ...existing, name, picture };\n }\n\n const id = generateId();\n await sqlite.execute(\n `INSERT INTO ${USERS_TABLE} (id, email, name, picture) VALUES (?, ?, ?, ?)`,\n [id, email, name, picture]\n );\n return { id, email, name, picture };\n}\n\n// ============ SESSION QUERIES ============\n\nexport async function createSession(userId: string): Promise {\n const sessionId = generateId();\n const expiresAt = new Date(Date.now() + 30 * 24 * 60 * 60 * 1000).toISOString(); // 30 days\n\n await sqlite.execute(\n `INSERT INTO ${SESSIONS_TABLE} (id, user_id, expires_at) VALUES (?, ?, ?)`,\n [sessionId, userId, expiresAt]\n );\n\n return sessionId;\n}\n\nexport async function getSessionUser(sessionId: string): Promise {\n const result = await sqlite.execute(\n `SELECT u.id, u.email, u.name, u.picture\n FROM ${SESSIONS_TABLE} s\n JOIN ${USERS_TABLE} u ON s.user_id = u.id\n WHERE s.id = ? AND s.expires_at > datetime('now')`,\n [sessionId]\n );\n\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n return {\n id: row.id as string,\n email: row.email as string,\n name: row.name as string | null,\n picture: row.picture as string | null,\n };\n}\n\nexport async function deleteSession(sessionId: string): Promise {\n await sqlite.execute(`DELETE FROM ${SESSIONS_TABLE} WHERE id = ?`, [sessionId]);\n}\n\n// ============ RECIPE QUERIES ============\n\n// Helper to map nutrition row to RecipeNutrition with all nutrients\nfunction mapNutritionRow(n: any, recipeId: string): RecipeNutrition {\n return {\n id: n.id as string,\n recipeId: recipeId,\n // Energy\n calories: n.calories as number | null,\n // Macronutrients\n protein: n.protein as number | null,\n fat: n.fat as number | null,\n carbs: n.carbs as number | null,\n fiber: n.fiber as number | null,\n sugars: n.sugars as number | null,\n addedSugars: n.added_sugars as number | null,\n starch: n.starch as number | null,\n // Fats breakdown\n saturatedFat: n.saturated_fat as number | null,\n transFat: n.trans_fat as number | null,\n monounsaturatedFat: n.monounsaturated_fat as number | null,\n polyunsaturatedFat: n.polyunsaturated_fat as number | null,\n cholesterol: n.cholesterol as number | null,\n // Other macros\n water: n.water as number | null,\n alcohol: n.alcohol as number | null,\n caffeine: n.caffeine as number | null,\n // Minerals\n sodium: n.sodium as number | null,\n potassium: n.potassium as number | null,\n calcium: n.calcium as number | null,\n iron: n.iron as number | null,\n magnesium: n.magnesium as number | null,\n zinc: n.zinc as number | null,\n phosphorus: n.phosphorus as number | null,\n selenium: n.selenium as number | null,\n copper: n.copper as number | null,\n manganese: n.manganese as number | null,\n fluoride: n.fluoride as number | null,\n chromium: n.chromium as number | null,\n molybdenum: n.molybdenum as number | null,\n iodine: n.iodine as number | null,\n chloride: n.chloride as number | null,\n // Vitamins - Fat soluble\n vitaminA: n.vitamin_a as number | null,\n retinol: n.retinol as number | null,\n betaCarotene: n.beta_carotene as number | null,\n alphaCarotene: n.alpha_carotene as number | null,\n betaCryptoxanthin: n.beta_cryptoxanthin as number | null,\n lycopene: n.lycopene as number | null,\n luteinZeaxanthin: n.lutein_zeaxanthin as number | null,\n vitaminD: n.vitamin_d as number | null,\n vitaminD2: n.vitamin_d2 as number | null,\n vitaminD3: n.vitamin_d3 as number | null,\n vitaminE: n.vitamin_e as number | null,\n vitaminK: n.vitamin_k as number | null,\n vitaminK2: n.vitamin_k2 as number | null,\n // Vitamins - Water soluble\n vitaminC: n.vitamin_c as number | null,\n vitaminB6: n.vitamin_b6 as number | null,\n vitaminB12: n.vitamin_b12 as number | null,\n thiamin: n.thiamin as number | null,\n riboflavin: n.riboflavin as number | null,\n niacin: n.niacin as number | null,\n pantothenicAcid: n.pantothenic_acid as number | null,\n biotin: n.biotin as number | null,\n folate: n.folate as number | null,\n folicAcid: n.folic_acid as number | null,\n choline: n.choline as number | null,\n betaine: n.betaine as number | null,\n // Metadata\n isComplete: Boolean(n.is_complete),\n };\n}\n\n// SQL column list for nutrition table SELECT queries\nconst NUTRITION_SELECT_COLUMNS = `\n id, recipe_id, calories,\n protein, fat, carbs, fiber, sugars, added_sugars, starch,\n saturated_fat, trans_fat, monounsaturated_fat, polyunsaturated_fat, cholesterol,\n water, alcohol, caffeine,\n sodium, potassium, calcium, iron, magnesium, zinc,\n phosphorus, selenium, copper, manganese, fluoride, chromium, molybdenum, iodine, chloride,\n vitamin_a, retinol, beta_carotene, alpha_carotene, beta_cryptoxanthin, lycopene, lutein_zeaxanthin,\n vitamin_d, vitamin_d2, vitamin_d3, vitamin_e, vitamin_k, vitamin_k2,\n vitamin_c, vitamin_b6, vitamin_b12, thiamin, riboflavin, niacin,\n pantothenic_acid, biotin, folate, folic_acid, choline, betaine,\n is_complete\n`;\n\nexport async function getRecipesByUser(userId: string): Promise {\n const recipesResult = await sqlite.execute(\n `SELECT id, user_id, title, url, instructions, serving_size, created_at, updated_at\n FROM ${RECIPES_TABLE} WHERE user_id = ? ORDER BY created_at DESC`,\n [userId]\n );\n\n const recipes: Recipe[] = [];\n\n for (const row of recipesResult.rows) {\n const recipeId = row.id as string;\n\n // Get ingredients with FDC fields\n const ingredientsResult = await sqlite.execute(\n `SELECT id, recipe_id, name, amount, unit, amount_grams, notes, order_index,\n fdc_id, fdc_description, fdc_data_type\n FROM ${RECIPE_INGREDIENTS_TABLE} WHERE recipe_id = ? ORDER BY order_index`,\n [recipeId]\n );\n\n const ingredients: RecipeIngredient[] = ingredientsResult.rows.map((i) => ({\n id: i.id as string,\n recipeId: i.recipe_id as string,\n name: i.name as string,\n amount: i.amount as number | null,\n unit: i.unit as string | null,\n amountGrams: i.amount_grams as number | null,\n notes: i.notes as string | null,\n orderIndex: i.order_index as number,\n fdcId: i.fdc_id as number | null,\n fdcDescription: i.fdc_description as string | null,\n fdcDataType: i.fdc_data_type as FDCDataType | null,\n }));\n\n // Get nutrition with all nutrients\n const nutritionResult = await sqlite.execute(\n `SELECT ${NUTRITION_SELECT_COLUMNS} FROM ${RECIPE_NUTRITION_TABLE} WHERE recipe_id = ?`,\n [recipeId]\n );\n\n let nutrition: RecipeNutrition | null = null;\n if (nutritionResult.rows.length > 0) {\n const n = nutritionResult.rows[0];\n nutrition = mapNutritionRow(n, recipeId);\n }\n\n recipes.push({\n id: recipeId,\n userId: row.user_id as string,\n title: row.title as string,\n url: row.url as string | null,\n instructions: row.instructions as string | null,\n servingSize: row.serving_size as number,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n ingredients,\n nutrition,\n });\n }\n\n return recipes;\n}\n\nexport async function getRecipeById(recipeId: string, userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, title, url, instructions, serving_size, created_at, updated_at\n FROM ${RECIPES_TABLE} WHERE id = ? AND user_id = ?`,\n [recipeId, userId]\n );\n\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n\n // Get ingredients with FDC fields\n const ingredientsResult = await sqlite.execute(\n `SELECT id, recipe_id, name, amount, unit, amount_grams, notes, order_index,\n fdc_id, fdc_description, fdc_data_type\n FROM ${RECIPE_INGREDIENTS_TABLE} WHERE recipe_id = ? ORDER BY order_index`,\n [recipeId]\n );\n\n const ingredients: RecipeIngredient[] = ingredientsResult.rows.map((i) => ({\n id: i.id as string,\n recipeId: i.recipe_id as string,\n name: i.name as string,\n amount: i.amount as number | null,\n unit: i.unit as string | null,\n amountGrams: i.amount_grams as number | null,\n notes: i.notes as string | null,\n orderIndex: i.order_index as number,\n fdcId: i.fdc_id as number | null,\n fdcDescription: i.fdc_description as string | null,\n fdcDataType: i.fdc_data_type as FDCDataType | null,\n }));\n\n // Get nutrition with all nutrients\n const nutritionResult = await sqlite.execute(\n `SELECT ${NUTRITION_SELECT_COLUMNS} FROM ${RECIPE_NUTRITION_TABLE} WHERE recipe_id = ?`,\n [recipeId]\n );\n\n let nutrition: RecipeNutrition | null = null;\n if (nutritionResult.rows.length > 0) {\n const n = nutritionResult.rows[0];\n nutrition = mapNutritionRow(n, recipeId);\n }\n\n return {\n id: recipeId,\n userId: row.user_id as string,\n title: row.title as string,\n url: row.url as string | null,\n instructions: row.instructions as string | null,\n servingSize: row.serving_size as number,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n ingredients,\n nutrition,\n };\n}\n\n// Helper to build INSERT statement for nutrition\nfunction buildNutritionInsert(nutId: string, recipeId: string, n: any): { sql: string; values: any[] } {\n return {\n sql: `INSERT INTO ${RECIPE_NUTRITION_TABLE} (\n id, recipe_id, calories,\n protein, fat, carbs, fiber, sugars, added_sugars, starch,\n saturated_fat, trans_fat, monounsaturated_fat, polyunsaturated_fat, cholesterol,\n water, alcohol, caffeine,\n sodium, potassium, calcium, iron, magnesium, zinc,\n phosphorus, selenium, copper, manganese, fluoride, chromium, molybdenum, iodine, chloride,\n vitamin_a, retinol, beta_carotene, alpha_carotene, beta_cryptoxanthin, lycopene, lutein_zeaxanthin,\n vitamin_d, vitamin_d2, vitamin_d3, vitamin_e, vitamin_k, vitamin_k2,\n vitamin_c, vitamin_b6, vitamin_b12, thiamin, riboflavin, niacin,\n pantothenic_acid, biotin, folate, folic_acid, choline, betaine,\n is_complete\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n values: [\n nutId, recipeId, n.calories,\n n.protein, n.fat, n.carbs, n.fiber, n.sugars, n.addedSugars, n.starch,\n n.saturatedFat, n.transFat, n.monounsaturatedFat, n.polyunsaturatedFat, n.cholesterol,\n n.water, n.alcohol, n.caffeine,\n n.sodium, n.potassium, n.calcium, n.iron, n.magnesium, n.zinc,\n n.phosphorus, n.selenium, n.copper, n.manganese, n.fluoride, n.chromium, n.molybdenum, n.iodine, n.chloride,\n n.vitaminA, n.retinol, n.betaCarotene, n.alphaCarotene, n.betaCryptoxanthin, n.lycopene, n.luteinZeaxanthin,\n n.vitaminD, n.vitaminD2, n.vitaminD3, n.vitaminE, n.vitaminK, n.vitaminK2,\n n.vitaminC, n.vitaminB6, n.vitaminB12, n.thiamin, n.riboflavin, n.niacin,\n n.pantothenicAcid, n.biotin, n.folate, n.folicAcid, n.choline, n.betaine,\n n.isComplete ? 1 : 0\n ]\n };\n}\n\nexport async function createRecipe(\n userId: string,\n title: string,\n url: string | null,\n instructions: string | null,\n servingSize: number,\n ingredients: CreateRecipeIngredient[]\n): Promise {\n const recipeId = generateId();\n\n await sqlite.execute(\n `INSERT INTO ${RECIPES_TABLE} (id, user_id, title, url, instructions, serving_size)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [recipeId, userId, title, url, instructions, servingSize]\n );\n\n // Insert ingredients with FDC fields\n const recipeIngredients: RecipeIngredient[] = [];\n for (let i = 0; i < ingredients.length; i++) {\n const ing = ingredients[i];\n const ingId = generateId();\n\n // Calculate amount in grams if possible\n const amountGrams = convertToGrams(ing.amount ?? null, ing.unit ?? null);\n\n await sqlite.execute(\n `INSERT INTO ${RECIPE_INGREDIENTS_TABLE} \n (id, recipe_id, name, amount, unit, amount_grams, notes, order_index, fdc_id, fdc_description, fdc_data_type)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n ingId, recipeId, ing.name,\n ing.amount ?? null, ing.unit ?? null, amountGrams,\n ing.notes ?? null, i,\n ing.fdcId ?? null, ing.fdcDescription ?? null, ing.fdcDataType ?? null\n ]\n );\n\n recipeIngredients.push({\n id: ingId,\n recipeId,\n name: ing.name,\n amount: ing.amount ?? null,\n unit: ing.unit ?? null,\n amountGrams,\n notes: ing.notes ?? null,\n orderIndex: i,\n fdcId: ing.fdcId ?? null,\n fdcDescription: ing.fdcDescription ?? null,\n fdcDataType: ing.fdcDataType ?? null,\n });\n }\n\n // Calculate nutrition from FDC-linked ingredients\n const calculatedNutrition = await calculateRecipeNutrition(recipeIngredients, servingSize);\n\n // Insert calculated nutrition\n const nutId = generateId();\n const n = calculatedNutrition.nutrition;\n const { sql, values } = buildNutritionInsert(nutId, recipeId, n);\n await sqlite.execute(sql, values);\n\n const recipeNutrition: RecipeNutrition = {\n id: nutId,\n recipeId,\n ...n,\n };\n\n const now = new Date().toISOString();\n return {\n id: recipeId,\n userId,\n title,\n url,\n instructions,\n servingSize,\n createdAt: now,\n updatedAt: now,\n ingredients: recipeIngredients,\n nutrition: recipeNutrition,\n };\n}\n\nexport async function updateRecipe(\n recipeId: string,\n userId: string,\n updates: {\n title?: string;\n url?: string | null;\n instructions?: string | null;\n servingSize?: number;\n ingredients?: CreateRecipeIngredient[];\n }\n): Promise {\n // Check ownership\n const existing = await getRecipeById(recipeId, userId);\n if (!existing) return null;\n\n // Update recipe\n const fields: string[] = [];\n const values: any[] = [];\n\n if (updates.title !== undefined) {\n fields.push('title = ?');\n values.push(updates.title);\n }\n if (updates.url !== undefined) {\n fields.push('url = ?');\n values.push(updates.url);\n }\n if (updates.instructions !== undefined) {\n fields.push('instructions = ?');\n values.push(updates.instructions);\n }\n if (updates.servingSize !== undefined) {\n fields.push('serving_size = ?');\n values.push(updates.servingSize);\n }\n\n if (fields.length > 0) {\n fields.push('updated_at = CURRENT_TIMESTAMP');\n values.push(recipeId, userId);\n await sqlite.execute(\n `UPDATE ${RECIPES_TABLE} SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`,\n values\n );\n }\n\n // Determine the serving size to use for nutrition calculation\n const servingSize = updates.servingSize ?? existing.servingSize;\n\n // Update ingredients if provided\n let recipeIngredients: RecipeIngredient[] = existing.ingredients;\n if (updates.ingredients !== undefined) {\n await sqlite.execute(`DELETE FROM ${RECIPE_INGREDIENTS_TABLE} WHERE recipe_id = ?`, [recipeId]);\n recipeIngredients = [];\n\n for (let i = 0; i < updates.ingredients.length; i++) {\n const ing = updates.ingredients[i];\n const ingId = generateId();\n\n // Calculate amount in grams if possible\n const amountGrams = convertToGrams(ing.amount ?? null, ing.unit ?? null);\n\n await sqlite.execute(\n `INSERT INTO ${RECIPE_INGREDIENTS_TABLE} \n (id, recipe_id, name, amount, unit, amount_grams, notes, order_index, fdc_id, fdc_description, fdc_data_type)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n ingId, recipeId, ing.name,\n ing.amount ?? null, ing.unit ?? null, amountGrams,\n ing.notes ?? null, i,\n ing.fdcId ?? null, ing.fdcDescription ?? null, ing.fdcDataType ?? null\n ]\n );\n\n recipeIngredients.push({\n id: ingId,\n recipeId,\n name: ing.name,\n amount: ing.amount ?? null,\n unit: ing.unit ?? null,\n amountGrams,\n notes: ing.notes ?? null,\n orderIndex: i,\n fdcId: ing.fdcId ?? null,\n fdcDescription: ing.fdcDescription ?? null,\n fdcDataType: ing.fdcDataType ?? null,\n });\n }\n }\n\n // Recalculate nutrition if ingredients or serving size changed\n if (updates.ingredients !== undefined || updates.servingSize !== undefined) {\n // Calculate nutrition from FDC-linked ingredients\n const calculatedNutrition = await calculateRecipeNutrition(recipeIngredients, servingSize);\n\n // Update nutrition\n await sqlite.execute(`DELETE FROM ${RECIPE_NUTRITION_TABLE} WHERE recipe_id = ?`, [recipeId]);\n\n const nutId = generateId();\n const n = calculatedNutrition.nutrition;\n const { sql, values } = buildNutritionInsert(nutId, recipeId, n);\n await sqlite.execute(sql, values);\n }\n\n return getRecipeById(recipeId, userId);\n}\n\nexport async function deleteRecipe(recipeId: string, userId: string): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${RECIPES_TABLE} WHERE id = ? AND user_id = ?`,\n [recipeId, userId]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function duplicateRecipe(recipeId: string, userId: string): Promise {\n const original = await getRecipeById(recipeId, userId);\n if (!original) return null;\n\n return createRecipe(\n userId,\n `${original.title} (Copy)`,\n original.url,\n original.instructions,\n original.servingSize,\n original.ingredients.map(i => ({\n name: i.name,\n amount: i.amount,\n unit: i.unit,\n notes: i.notes,\n fdcId: i.fdcId,\n fdcDescription: i.fdcDescription,\n fdcDataType: i.fdcDataType,\n }))\n );\n}\n\n// ============ GROCERY LIST QUERIES ============\n\nasync function ensureDefaultGroceryListExists(userId: string): Promise {\n const existingResult = await sqlite.execute(\n `SELECT id FROM ${GROCERY_LISTS_TABLE} WHERE user_id = ? AND name = ?`,\n [userId, DEFAULT_GROCERY_LIST_NAME]\n );\n\n if (existingResult.rows.length === 0) {\n // Create the default list\n const listId = generateId();\n const now = new Date().toISOString();\n await sqlite.execute(\n `INSERT INTO ${GROCERY_LISTS_TABLE} (id, user_id, name) VALUES (?, ?, ?)`,\n [listId, userId, DEFAULT_GROCERY_LIST_NAME]\n );\n }\n}\n\nexport async function getGroceryListsByUser(userId: string): Promise {\n // Ensure default list exists\n await ensureDefaultGroceryListExists(userId);\n const listsResult = await sqlite.execute(\n `SELECT id, user_id, name, created_at, updated_at\n FROM ${GROCERY_LISTS_TABLE} WHERE user_id = ? \n ORDER BY CASE WHEN name = ? THEN 0 ELSE 1 END, created_at DESC`,\n [userId, DEFAULT_GROCERY_LIST_NAME]\n );\n\n const lists: GroceryList[] = [];\n\n for (const row of listsResult.rows) {\n const listId = row.id as string;\n\n const itemsResult = await sqlite.execute(\n `SELECT id, list_id, name, quantity, unit, notes, recipe_id, recipe_name, checked, created_at\n FROM ${GROCERY_LIST_ITEMS_TABLE} WHERE list_id = ? ORDER BY created_at`,\n [listId]\n );\n\n const items: GroceryListItem[] = itemsResult.rows.map((i) => ({\n id: i.id as string,\n listId: i.list_id as string,\n name: i.name as string,\n quantity: i.quantity as number | null,\n unit: i.unit as string | null,\n notes: i.notes as string | null,\n recipeId: i.recipe_id as string | null,\n recipeName: i.recipe_name as string | null,\n checked: Boolean(i.checked),\n createdAt: i.created_at as string,\n }));\n\n lists.push({\n id: listId,\n userId: row.user_id as string,\n name: row.name as string,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n items,\n });\n }\n\n return lists;\n}\n\nexport async function getGroceryListById(listId: string, userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, name, created_at, updated_at\n FROM ${GROCERY_LISTS_TABLE} WHERE id = ? AND user_id = ?`,\n [listId, userId]\n );\n\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n\n const itemsResult = await sqlite.execute(\n `SELECT id, list_id, name, quantity, unit, notes, recipe_id, recipe_name, checked, created_at\n FROM ${GROCERY_LIST_ITEMS_TABLE} WHERE list_id = ? ORDER BY created_at`,\n [listId]\n );\n\n const items: GroceryListItem[] = itemsResult.rows.map((i) => ({\n id: i.id as string,\n listId: i.list_id as string,\n name: i.name as string,\n quantity: i.quantity as number | null,\n unit: i.unit as string | null,\n notes: i.notes as string | null,\n recipeId: i.recipe_id as string | null,\n recipeName: i.recipe_name as string | null,\n checked: Boolean(i.checked),\n createdAt: i.created_at as string,\n }));\n\n return {\n id: listId,\n userId: row.user_id as string,\n name: row.name as string,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n items,\n };\n}\n\nexport async function createGroceryList(userId: string, name: string): Promise {\n const listId = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO ${GROCERY_LISTS_TABLE} (id, user_id, name) VALUES (?, ?, ?)`,\n [listId, userId, name]\n );\n\n return {\n id: listId,\n userId,\n name,\n createdAt: now,\n updatedAt: now,\n items: [],\n };\n}\n\nexport async function updateGroceryList(listId: string, userId: string, name: string): Promise {\n const existing = await getGroceryListById(listId, userId);\n if (!existing) return null;\n\n await sqlite.execute(\n `UPDATE ${GROCERY_LISTS_TABLE} SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?`,\n [name, listId, userId]\n );\n\n return getGroceryListById(listId, userId);\n}\n\nexport async function deleteGroceryList(listId: string, userId: string): Promise {\n // Check if this is the default list\n const list = await getGroceryListById(listId, userId);\n if (list && list.name === DEFAULT_GROCERY_LIST_NAME) {\n // Prevent deletion of default list\n return false;\n }\n\n const result = await sqlite.execute(\n `DELETE FROM ${GROCERY_LISTS_TABLE} WHERE id = ? AND user_id = ?`,\n [listId, userId]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function addGroceryItem(\n listId: string,\n userId: string,\n name: string,\n quantity: number | null,\n unit: string | null,\n notes: string | null = null,\n recipeId: string | null = null,\n recipeName: string | null = null\n): Promise {\n // Verify list ownership\n const list = await getGroceryListById(listId, userId);\n if (!list) return null;\n\n const itemId = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO ${GROCERY_LIST_ITEMS_TABLE} (id, list_id, name, quantity, unit, notes, recipe_id, recipe_name)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n [itemId, listId, name, quantity, unit, notes, recipeId, recipeName]\n );\n\n await sqlite.execute(\n `UPDATE ${GROCERY_LISTS_TABLE} SET updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [listId]\n );\n\n return {\n id: itemId,\n listId,\n name,\n quantity,\n unit,\n notes,\n recipeId,\n recipeName,\n checked: false,\n createdAt: now,\n };\n}\n\nexport async function addRecipeIngredientsToList(\n listId: string,\n userId: string,\n recipeId: string,\n servings?: number\n): Promise {\n const recipe = await getRecipeById(recipeId, userId);\n if (!recipe) return [];\n\n // Calculate multiplier based on requested servings vs recipe's serving size\n const targetServings = servings ?? recipe.servingSize;\n const multiplier = targetServings / recipe.servingSize;\n\n const items: GroceryListItem[] = [];\n\n for (const ing of recipe.ingredients) {\n // Scale the amount by the multiplier\n const scaledAmount = ing.amount !== null ? ing.amount * multiplier : null;\n \n const item = await addGroceryItem(\n listId,\n userId,\n ing.name,\n scaledAmount,\n ing.unit,\n ing.notes, // Pass ingredient notes\n recipeId,\n recipe.title\n );\n if (item) items.push(item);\n }\n\n return items;\n}\n\nexport async function updateGroceryItemChecked(\n itemId: string,\n listId: string,\n userId: string,\n checked: boolean\n): Promise {\n // Verify list ownership\n const list = await getGroceryListById(listId, userId);\n if (!list) return false;\n\n const result = await sqlite.execute(\n `UPDATE ${GROCERY_LIST_ITEMS_TABLE} SET checked = ? WHERE id = ? AND list_id = ?`,\n [checked ? 1 : 0, itemId, listId]\n );\n\n return result.rowsAffected > 0;\n}\n\nexport async function updateGroceryItem(\n itemId: string,\n listId: string,\n userId: string,\n updates: {\n name?: string;\n quantity?: number | null;\n unit?: string | null;\n notes?: string | null;\n checked?: boolean;\n }\n): Promise {\n // Verify list ownership\n const list = await getGroceryListById(listId, userId);\n if (!list) return null;\n\n // Find the item\n const existingItem = list.items.find(i => i.id === itemId);\n if (!existingItem) return null;\n\n const fields: string[] = [];\n const values: any[] = [];\n\n if (updates.name !== undefined) {\n fields.push('name = ?');\n values.push(updates.name);\n }\n if (updates.quantity !== undefined) {\n fields.push('quantity = ?');\n values.push(updates.quantity);\n }\n if (updates.unit !== undefined) {\n fields.push('unit = ?');\n values.push(updates.unit);\n }\n if (updates.notes !== undefined) {\n fields.push('notes = ?');\n values.push(updates.notes);\n }\n if (updates.checked !== undefined) {\n fields.push('checked = ?');\n values.push(updates.checked ? 1 : 0);\n }\n\n if (fields.length === 0) {\n return existingItem;\n }\n\n values.push(itemId, listId);\n await sqlite.execute(\n `UPDATE ${GROCERY_LIST_ITEMS_TABLE} SET ${fields.join(', ')} WHERE id = ? AND list_id = ?`,\n values\n );\n\n await sqlite.execute(\n `UPDATE ${GROCERY_LISTS_TABLE} SET updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [listId]\n );\n\n // Return updated item\n return {\n ...existingItem,\n name: updates.name ?? existingItem.name,\n quantity: updates.quantity !== undefined ? updates.quantity : existingItem.quantity,\n unit: updates.unit !== undefined ? updates.unit : existingItem.unit,\n notes: updates.notes !== undefined ? updates.notes : existingItem.notes,\n checked: updates.checked !== undefined ? updates.checked : existingItem.checked,\n };\n}\n\nexport async function deleteGroceryItem(itemId: string, listId: string, userId: string): Promise {\n // Verify list ownership\n const list = await getGroceryListById(listId, userId);\n if (!list) return false;\n\n const result = await sqlite.execute(\n `DELETE FROM ${GROCERY_LIST_ITEMS_TABLE} WHERE id = ? AND list_id = ?`,\n [itemId, listId]\n );\n\n return result.rowsAffected > 0;\n}\n\nexport async function deleteAllGroceryItems(listId: string, userId: string): Promise {\n // Verify list ownership\n const list = await getGroceryListById(listId, userId);\n if (!list) return false;\n\n const result = await sqlite.execute(\n `DELETE FROM ${GROCERY_LIST_ITEMS_TABLE} WHERE list_id = ?`,\n [listId]\n );\n\n // Update the list's updated_at timestamp\n await sqlite.execute(\n `UPDATE ${GROCERY_LISTS_TABLE} SET updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [listId]\n );\n\n return result.rowsAffected > 0;\n}\n\n// ============ NUTRITION GOALS QUERIES ============\n\nexport async function getNutritionGoals(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, primary_goal_type, primary_goal_value, \n sub_goal_1_type, sub_goal_1_value, sub_goal_2_type, sub_goal_2_value, updated_at\n FROM ${NUTRITION_GOALS_TABLE} WHERE user_id = ?`,\n [userId]\n );\n\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n\n return {\n id: row.id as string,\n userId: row.user_id as string,\n primaryGoalType: row.primary_goal_type as any,\n primaryGoalValue: row.primary_goal_value as number,\n subGoal1Type: row.sub_goal_1_type as any | null,\n subGoal1Value: row.sub_goal_1_value as number | null,\n subGoal2Type: row.sub_goal_2_type as any | null,\n subGoal2Value: row.sub_goal_2_value as number | null,\n updatedAt: row.updated_at as string,\n };\n}\n\nexport async function setNutritionGoals(\n userId: string,\n primaryGoalType: string,\n primaryGoalValue: number,\n subGoal1Type: string | null,\n subGoal1Value: number | null,\n subGoal2Type: string | null,\n subGoal2Value: number | null\n): Promise {\n const existing = await getNutritionGoals(userId);\n const now = new Date().toISOString();\n\n if (existing) {\n await sqlite.execute(\n `UPDATE ${NUTRITION_GOALS_TABLE} \n SET primary_goal_type = ?, primary_goal_value = ?,\n sub_goal_1_type = ?, sub_goal_1_value = ?,\n sub_goal_2_type = ?, sub_goal_2_value = ?,\n updated_at = CURRENT_TIMESTAMP\n WHERE user_id = ?`,\n [primaryGoalType, primaryGoalValue, subGoal1Type, subGoal1Value, subGoal2Type, subGoal2Value, userId]\n );\n\n return {\n ...existing,\n primaryGoalType: primaryGoalType as any,\n primaryGoalValue,\n subGoal1Type: subGoal1Type as any,\n subGoal1Value,\n subGoal2Type: subGoal2Type as any,\n subGoal2Value,\n updatedAt: now,\n };\n }\n\n const id = generateId();\n await sqlite.execute(\n `INSERT INTO ${NUTRITION_GOALS_TABLE} \n (id, user_id, primary_goal_type, primary_goal_value, sub_goal_1_type, sub_goal_1_value, sub_goal_2_type, sub_goal_2_value)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n [id, userId, primaryGoalType, primaryGoalValue, subGoal1Type, subGoal1Value, subGoal2Type, subGoal2Value]\n );\n\n return {\n id,\n userId,\n primaryGoalType: primaryGoalType as any,\n primaryGoalValue,\n subGoal1Type: subGoal1Type as any,\n subGoal1Value,\n subGoal2Type: subGoal2Type as any,\n subGoal2Value,\n updatedAt: now,\n };\n}\n\n// ============ MEAL PLAN QUERIES ============\n\n// SQL for FDC food cache SELECT\nconst FDC_FOOD_SELECT_COLUMNS = `\n fdc_id, description, data_type, brand_owner, brand_name,\n serving_size, serving_size_unit, household_serving_text,\n calories, protein, fat, carbs, fiber, sugars, added_sugars, starch,\n saturated_fat, trans_fat, monounsaturated_fat, polyunsaturated_fat, cholesterol,\n water, alcohol, caffeine,\n sodium, potassium, calcium, iron, magnesium, zinc,\n phosphorus, selenium, copper, manganese, fluoride, chromium, molybdenum, iodine, chloride,\n vitamin_a, retinol, beta_carotene, alpha_carotene, beta_cryptoxanthin, lycopene, lutein_zeaxanthin,\n vitamin_d, vitamin_d2, vitamin_d3, vitamin_e, vitamin_k, vitamin_k2,\n vitamin_c, vitamin_b6, vitamin_b12, thiamin, riboflavin, niacin,\n pantothenic_acid, biotin, folate, folic_acid, choline, betaine,\n cached_at\n`;\n\nfunction mapFDCFoodRow(row: any): FDCFood {\n return {\n fdcId: row.fdc_id as number,\n description: row.description as string,\n dataType: row.data_type as FDCDataType,\n brandOwner: row.brand_owner as string | null,\n brandName: row.brand_name as string | null,\n servingSize: row.serving_size as number | null,\n servingSizeUnit: row.serving_size_unit as string | null,\n householdServingText: row.household_serving_text as string | null,\n nutrition: {\n calories: row.calories as number | null,\n protein: row.protein as number | null,\n fat: row.fat as number | null,\n carbs: row.carbs as number | null,\n fiber: row.fiber as number | null,\n sugars: row.sugars as number | null,\n addedSugars: row.added_sugars as number | null,\n starch: row.starch as number | null,\n saturatedFat: row.saturated_fat as number | null,\n transFat: row.trans_fat as number | null,\n monounsaturatedFat: row.monounsaturated_fat as number | null,\n polyunsaturatedFat: row.polyunsaturated_fat as number | null,\n cholesterol: row.cholesterol as number | null,\n water: row.water as number | null,\n alcohol: row.alcohol as number | null,\n caffeine: row.caffeine as number | null,\n sodium: row.sodium as number | null,\n potassium: row.potassium as number | null,\n calcium: row.calcium as number | null,\n iron: row.iron as number | null,\n magnesium: row.magnesium as number | null,\n zinc: row.zinc as number | null,\n phosphorus: row.phosphorus as number | null,\n selenium: row.selenium as number | null,\n copper: row.copper as number | null,\n manganese: row.manganese as number | null,\n fluoride: row.fluoride as number | null,\n chromium: row.chromium as number | null,\n molybdenum: row.molybdenum as number | null,\n iodine: row.iodine as number | null,\n chloride: row.chloride as number | null,\n vitaminA: row.vitamin_a as number | null,\n retinol: row.retinol as number | null,\n betaCarotene: row.beta_carotene as number | null,\n alphaCarotene: row.alpha_carotene as number | null,\n betaCryptoxanthin: row.beta_cryptoxanthin as number | null,\n lycopene: row.lycopene as number | null,\n luteinZeaxanthin: row.lutein_zeaxanthin as number | null,\n vitaminD: row.vitamin_d as number | null,\n vitaminD2: row.vitamin_d2 as number | null,\n vitaminD3: row.vitamin_d3 as number | null,\n vitaminE: row.vitamin_e as number | null,\n vitaminK: row.vitamin_k as number | null,\n vitaminK2: row.vitamin_k2 as number | null,\n vitaminC: row.vitamin_c as number | null,\n vitaminB6: row.vitamin_b6 as number | null,\n vitaminB12: row.vitamin_b12 as number | null,\n thiamin: row.thiamin as number | null,\n riboflavin: row.riboflavin as number | null,\n niacin: row.niacin as number | null,\n pantothenicAcid: row.pantothenic_acid as number | null,\n biotin: row.biotin as number | null,\n folate: row.folate as number | null,\n folicAcid: row.folic_acid as number | null,\n choline: row.choline as number | null,\n betaine: row.betaine as number | null,\n },\n cachedAt: row.cached_at as string,\n };\n}\n\nexport async function getFDCFoodFromCache(fdcId: number): Promise {\n const result = await sqlite.execute(\n `SELECT ${FDC_FOOD_SELECT_COLUMNS} FROM ${FDC_FOODS_CACHE_TABLE} WHERE fdc_id = ?`,\n [fdcId]\n );\n\n if (result.rows.length === 0) return null;\n return mapFDCFoodRow(result.rows[0]);\n}\n\n// Custom foods SQL\nconst CUSTOM_FOOD_SELECT_COLUMNS = `\n id, user_id, name, serving_size_grams,\n calories, protein, fat, carbs, fiber, sugars, added_sugars, starch,\n saturated_fat, trans_fat, monounsaturated_fat, polyunsaturated_fat, cholesterol,\n water, alcohol, caffeine,\n sodium, potassium, calcium, iron, magnesium, zinc,\n phosphorus, selenium, copper, manganese, fluoride, chromium, molybdenum, iodine, chloride,\n vitamin_a, retinol, beta_carotene, alpha_carotene, beta_cryptoxanthin, lycopene, lutein_zeaxanthin,\n vitamin_d, vitamin_d2, vitamin_d3, vitamin_e, vitamin_k, vitamin_k2,\n vitamin_c, vitamin_b6, vitamin_b12, thiamin, riboflavin, niacin,\n pantothenic_acid, biotin, folate, folic_acid, choline, betaine,\n created_at, updated_at\n`;\n\nfunction mapCustomFoodRow(row: any): CustomFood {\n return {\n id: row.id as string,\n userId: row.user_id as string,\n name: row.name as string,\n servingSizeGrams: row.serving_size_grams as number,\n calories: row.calories as number | null,\n protein: row.protein as number | null,\n fat: row.fat as number | null,\n carbs: row.carbs as number | null,\n fiber: row.fiber as number | null,\n sugars: row.sugars as number | null,\n addedSugars: row.added_sugars as number | null,\n starch: row.starch as number | null,\n saturatedFat: row.saturated_fat as number | null,\n transFat: row.trans_fat as number | null,\n monounsaturatedFat: row.monounsaturated_fat as number | null,\n polyunsaturatedFat: row.polyunsaturated_fat as number | null,\n cholesterol: row.cholesterol as number | null,\n water: row.water as number | null,\n alcohol: row.alcohol as number | null,\n caffeine: row.caffeine as number | null,\n sodium: row.sodium as number | null,\n potassium: row.potassium as number | null,\n calcium: row.calcium as number | null,\n iron: row.iron as number | null,\n magnesium: row.magnesium as number | null,\n zinc: row.zinc as number | null,\n phosphorus: row.phosphorus as number | null,\n selenium: row.selenium as number | null,\n copper: row.copper as number | null,\n manganese: row.manganese as number | null,\n fluoride: row.fluoride as number | null,\n chromium: row.chromium as number | null,\n molybdenum: row.molybdenum as number | null,\n iodine: row.iodine as number | null,\n chloride: row.chloride as number | null,\n vitaminA: row.vitamin_a as number | null,\n retinol: row.retinol as number | null,\n betaCarotene: row.beta_carotene as number | null,\n alphaCarotene: row.alpha_carotene as number | null,\n betaCryptoxanthin: row.beta_cryptoxanthin as number | null,\n lycopene: row.lycopene as number | null,\n luteinZeaxanthin: row.lutein_zeaxanthin as number | null,\n vitaminD: row.vitamin_d as number | null,\n vitaminD2: row.vitamin_d2 as number | null,\n vitaminD3: row.vitamin_d3 as number | null,\n vitaminE: row.vitamin_e as number | null,\n vitaminK: row.vitamin_k as number | null,\n vitaminK2: row.vitamin_k2 as number | null,\n vitaminC: row.vitamin_c as number | null,\n vitaminB6: row.vitamin_b6 as number | null,\n vitaminB12: row.vitamin_b12 as number | null,\n thiamin: row.thiamin as number | null,\n riboflavin: row.riboflavin as number | null,\n niacin: row.niacin as number | null,\n pantothenicAcid: row.pantothenic_acid as number | null,\n biotin: row.biotin as number | null,\n folate: row.folate as number | null,\n folicAcid: row.folic_acid as number | null,\n choline: row.choline as number | null,\n betaine: row.betaine as number | null,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n };\n}\n\nexport async function getMealPlan(userId: string, weekStartDate: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, week_start_date, created_at, updated_at\n FROM ${MEAL_PLANS_TABLE} WHERE user_id = ? AND week_start_date = ?`,\n [userId, weekStartDate]\n );\n\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n const mealPlanId = row.id as string;\n\n // Get all entries (recipes, custom foods, and FDC ingredients)\n const entriesResult = await sqlite.execute(\n `SELECT id, meal_plan_id, day_of_week, meal_type, recipe_id, custom_food_id, fdc_id, servings, amount_grams\n FROM ${MEAL_PLAN_ENTRIES_TABLE}\n WHERE meal_plan_id = ?\n ORDER BY day_of_week, meal_type`,\n [mealPlanId]\n );\n\n const entries: MealPlanEntry[] = [];\n for (const e of entriesResult.rows) {\n const entry: MealPlanEntry = {\n id: e.id as string,\n mealPlanId: e.meal_plan_id as string,\n dayOfWeek: e.day_of_week as DayOfWeek,\n mealType: e.meal_type as MealType,\n recipeId: e.recipe_id as string | null,\n customFoodId: e.custom_food_id as string | null,\n fdcId: e.fdc_id as number | null,\n servings: e.servings as number,\n amountGrams: e.amount_grams as number | null,\n };\n\n // Populate associated data based on entry type\n if (entry.recipeId) {\n const recipe = await getRecipeById(entry.recipeId, userId);\n if (recipe) entry.recipe = recipe;\n } else if (entry.customFoodId) {\n const customFood = await getCustomFoodById(entry.customFoodId, userId);\n if (customFood) entry.customFood = customFood;\n } else if (entry.fdcId) {\n const fdcFood = await getFDCFoodFromCache(entry.fdcId);\n if (fdcFood) entry.fdcFood = fdcFood;\n }\n\n entries.push(entry);\n }\n\n return {\n id: mealPlanId,\n userId: row.user_id as string,\n weekStartDate: row.week_start_date as string,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n entries,\n };\n}\n\nexport async function getOrCreateMealPlan(userId: string, weekStartDate: string): Promise {\n const existing = await getMealPlan(userId, weekStartDate);\n if (existing) return existing;\n\n const id = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO ${MEAL_PLANS_TABLE} (id, user_id, week_start_date) VALUES (?, ?, ?)`,\n [id, userId, weekStartDate]\n );\n\n return {\n id,\n userId,\n weekStartDate,\n createdAt: now,\n updatedAt: now,\n entries: [],\n };\n}\n\n// Add entry to meal plan - supports recipes, custom foods, or FDC ingredients\nexport async function addMealPlanEntry(\n userId: string,\n weekStartDate: string,\n dayOfWeek: DayOfWeek,\n mealType: MealType,\n options: {\n recipeId?: string;\n customFoodId?: string;\n fdcId?: number;\n servings?: number;\n amountGrams?: number;\n }\n): Promise {\n const { recipeId, customFoodId, fdcId, servings = 1, amountGrams } = options;\n\n // Validate that exactly one of the IDs is provided\n const idCount = [recipeId, customFoodId, fdcId].filter(Boolean).length;\n if (idCount !== 1) {\n return null;\n }\n\n // Verify ownership/existence based on entry type\n let recipe: Recipe | undefined;\n let customFood: CustomFood | undefined;\n let fdcFood: FDCFood | undefined;\n\n if (recipeId) {\n const r = await getRecipeById(recipeId, userId);\n if (!r) return null;\n recipe = r;\n } else if (customFoodId) {\n const cf = await getCustomFoodById(customFoodId, userId);\n if (!cf) return null;\n customFood = cf;\n } else if (fdcId) {\n const fdc = await getFDCFoodFromCache(fdcId);\n if (!fdc) return null;\n fdcFood = fdc;\n }\n\n const mealPlan = await getOrCreateMealPlan(userId, weekStartDate);\n const entryId = generateId();\n\n await sqlite.execute(\n `INSERT INTO ${MEAL_PLAN_ENTRIES_TABLE} \n (id, meal_plan_id, day_of_week, meal_type, recipe_id, custom_food_id, fdc_id, servings, amount_grams)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [entryId, mealPlan.id, dayOfWeek, mealType, recipeId ?? null, customFoodId ?? null, fdcId ?? null, servings, amountGrams ?? null]\n );\n\n await sqlite.execute(\n `UPDATE ${MEAL_PLANS_TABLE} SET updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [mealPlan.id]\n );\n\n return {\n id: entryId,\n mealPlanId: mealPlan.id,\n dayOfWeek,\n mealType,\n recipeId: recipeId ?? null,\n customFoodId: customFoodId ?? null,\n fdcId: fdcId ?? null,\n recipe,\n customFood,\n fdcFood,\n servings,\n amountGrams: amountGrams ?? null,\n };\n}\n\nexport async function deleteMealPlanEntry(entryId: string, userId: string): Promise {\n // Get entry and verify ownership through meal plan\n const entryResult = await sqlite.execute(\n `SELECT e.id, e.meal_plan_id, m.user_id\n FROM ${MEAL_PLAN_ENTRIES_TABLE} e\n JOIN ${MEAL_PLANS_TABLE} m ON e.meal_plan_id = m.id\n WHERE e.id = ?`,\n [entryId]\n );\n\n if (entryResult.rows.length === 0) return false;\n if (entryResult.rows[0].user_id !== userId) return false;\n\n const result = await sqlite.execute(\n `DELETE FROM ${MEAL_PLAN_ENTRIES_TABLE} WHERE id = ?`,\n [entryId]\n );\n\n return result.rowsAffected > 0;\n}\n\nexport async function scheduleMeals(\n userId: string,\n weekStartDate: string,\n options: {\n recipeId?: string;\n customFoodId?: string;\n fdcId?: number;\n servings?: number;\n amountGrams?: number;\n },\n mealType: MealType,\n days: DayOfWeek[]\n): Promise {\n const entries: MealPlanEntry[] = [];\n\n for (const day of days) {\n const entry = await addMealPlanEntry(userId, weekStartDate, day, mealType, options);\n if (entry) entries.push(entry);\n }\n\n return entries;\n}\n\n// ============ SCRAPER EVAL URL QUERIES ============\n\nexport interface ScraperEvalUrl {\n id: string;\n userId: string;\n url: string;\n createdAt: string;\n}\n\nexport async function getScraperEvalUrls(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, url, created_at FROM ${SCRAPER_EVAL_URLS_TABLE} WHERE user_id = ? ORDER BY created_at DESC`,\n [userId]\n );\n\n return result.rows.map((row) => ({\n id: row.id as string,\n userId: row.user_id as string,\n url: row.url as string,\n createdAt: row.created_at as string,\n }));\n}\n\nexport async function addScraperEvalUrl(userId: string, url: string): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n try {\n await sqlite.execute(\n `INSERT INTO ${SCRAPER_EVAL_URLS_TABLE} (id, user_id, url) VALUES (?, ?, ?)`,\n [id, userId, url]\n );\n\n return { id, userId, url, createdAt: now };\n } catch (error) {\n // URL already exists for this user (unique constraint)\n return null;\n }\n}\n\nexport async function addScraperEvalUrls(userId: string, urls: string[]): Promise {\n const added: ScraperEvalUrl[] = [];\n\n for (const url of urls) {\n const result = await addScraperEvalUrl(userId, url);\n if (result) added.push(result);\n }\n\n return added;\n}\n\nexport async function deleteScraperEvalUrl(userId: string, urlId: string): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${SCRAPER_EVAL_URLS_TABLE} WHERE id = ? AND user_id = ?`,\n [urlId, userId]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function deleteAllScraperEvalUrls(userId: string): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${SCRAPER_EVAL_URLS_TABLE} WHERE user_id = ?`,\n [userId]\n );\n return result.rowsAffected;\n}\n\n// ============ STARRED INGREDIENTS QUERIES ============\n\nexport async function getStarredIngredients(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT id, user_id, fdc_id, fdc_description, fdc_data_type, brand_owner, created_at\n FROM ${STARRED_INGREDIENTS_TABLE} WHERE user_id = ? ORDER BY created_at DESC`,\n [userId]\n );\n\n return result.rows.map((row) => ({\n id: row.id as string,\n userId: row.user_id as string,\n fdcId: row.fdc_id as number,\n fdcDescription: row.fdc_description as string,\n fdcDataType: row.fdc_data_type as FDCDataType,\n brandOwner: row.brand_owner as string | null,\n createdAt: row.created_at as string,\n }));\n}\n\nexport async function addStarredIngredient(\n userId: string,\n fdcId: number,\n fdcDescription: string,\n fdcDataType: FDCDataType,\n brandOwner: string | null\n): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n try {\n await sqlite.execute(\n `INSERT INTO ${STARRED_INGREDIENTS_TABLE} (id, user_id, fdc_id, fdc_description, fdc_data_type, brand_owner)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [id, userId, fdcId, fdcDescription, fdcDataType, brandOwner]\n );\n\n return { id, userId, fdcId, fdcDescription, fdcDataType, brandOwner, createdAt: now };\n } catch (error) {\n // Already starred (unique constraint on user_id + fdc_id)\n return null;\n }\n}\n\nexport async function removeStarredIngredient(userId: string, fdcId: number): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${STARRED_INGREDIENTS_TABLE} WHERE user_id = ? AND fdc_id = ?`,\n [userId, fdcId]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function isIngredientStarred(userId: string, fdcId: number): Promise {\n const result = await sqlite.execute(\n `SELECT 1 FROM ${STARRED_INGREDIENTS_TABLE} WHERE user_id = ? AND fdc_id = ? LIMIT 1`,\n [userId, fdcId]\n );\n return result.rows.length > 0;\n}\n\n// ============ CUSTOM FOODS QUERIES ============\n\nexport async function getCustomFoodsByUser(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT ${CUSTOM_FOOD_SELECT_COLUMNS} FROM ${CUSTOM_FOODS_TABLE} WHERE user_id = ? ORDER BY name`,\n [userId]\n );\n\n return result.rows.map(mapCustomFoodRow);\n}\n\nexport async function getCustomFoodById(customFoodId: string, userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT ${CUSTOM_FOOD_SELECT_COLUMNS} FROM ${CUSTOM_FOODS_TABLE} WHERE id = ? AND user_id = ?`,\n [customFoodId, userId]\n );\n\n if (result.rows.length === 0) return null;\n return mapCustomFoodRow(result.rows[0]);\n}\n\nexport async function createCustomFood(\n userId: string,\n data: CreateCustomFoodRequest\n): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO ${CUSTOM_FOODS_TABLE} (\n id, user_id, name, serving_size_grams,\n calories, protein, fat, carbs, fiber, sugars, added_sugars, starch,\n saturated_fat, trans_fat, monounsaturated_fat, polyunsaturated_fat, cholesterol,\n water, alcohol, caffeine,\n sodium, potassium, calcium, iron, magnesium, zinc,\n phosphorus, selenium, copper, manganese, fluoride, chromium, molybdenum, iodine, chloride,\n vitamin_a, retinol, beta_carotene, alpha_carotene, beta_cryptoxanthin, lycopene, lutein_zeaxanthin,\n vitamin_d, vitamin_d2, vitamin_d3, vitamin_e, vitamin_k, vitamin_k2,\n vitamin_c, vitamin_b6, vitamin_b12, thiamin, riboflavin, niacin,\n pantothenic_acid, biotin, folate, folic_acid, choline, betaine\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n id, userId, data.name, data.servingSizeGrams ?? 100,\n data.calories ?? null, data.protein ?? null, data.fat ?? null, data.carbs ?? null, data.fiber ?? null,\n data.sugars ?? null, data.addedSugars ?? null, data.starch ?? null,\n data.saturatedFat ?? null, data.transFat ?? null, data.monounsaturatedFat ?? null, data.polyunsaturatedFat ?? null, data.cholesterol ?? null,\n data.water ?? null, data.alcohol ?? null, data.caffeine ?? null,\n data.sodium ?? null, data.potassium ?? null, data.calcium ?? null, data.iron ?? null, data.magnesium ?? null, data.zinc ?? null,\n data.phosphorus ?? null, data.selenium ?? null, data.copper ?? null, data.manganese ?? null, data.fluoride ?? null, data.chromium ?? null, data.molybdenum ?? null, data.iodine ?? null, data.chloride ?? null,\n data.vitaminA ?? null, data.retinol ?? null, data.betaCarotene ?? null, data.alphaCarotene ?? null, data.betaCryptoxanthin ?? null, data.lycopene ?? null, data.luteinZeaxanthin ?? null,\n data.vitaminD ?? null, data.vitaminD2 ?? null, data.vitaminD3 ?? null, data.vitaminE ?? null, data.vitaminK ?? null, data.vitaminK2 ?? null,\n data.vitaminC ?? null, data.vitaminB6 ?? null, data.vitaminB12 ?? null, data.thiamin ?? null, data.riboflavin ?? null, data.niacin ?? null,\n data.pantothenicAcid ?? null, data.biotin ?? null, data.folate ?? null, data.folicAcid ?? null, data.choline ?? null, data.betaine ?? null,\n ]\n );\n\n // Return created food by fetching it\n return (await getCustomFoodById(id, userId))!;\n}\n\nexport async function updateCustomFood(\n customFoodId: string,\n userId: string,\n data: Partial\n): Promise {\n // Check ownership\n const existing = await getCustomFoodById(customFoodId, userId);\n if (!existing) return null;\n\n const fields: string[] = [];\n const values: any[] = [];\n\n // Build dynamic update based on provided fields\n const fieldMappings: Array<[keyof CreateCustomFoodRequest, string]> = [\n ['name', 'name'],\n ['servingSizeGrams', 'serving_size_grams'],\n ['calories', 'calories'],\n ['protein', 'protein'],\n ['fat', 'fat'],\n ['carbs', 'carbs'],\n ['fiber', 'fiber'],\n ['sugars', 'sugars'],\n ['addedSugars', 'added_sugars'],\n ['starch', 'starch'],\n ['saturatedFat', 'saturated_fat'],\n ['transFat', 'trans_fat'],\n ['monounsaturatedFat', 'monounsaturated_fat'],\n ['polyunsaturatedFat', 'polyunsaturated_fat'],\n ['cholesterol', 'cholesterol'],\n ['water', 'water'],\n ['alcohol', 'alcohol'],\n ['caffeine', 'caffeine'],\n ['sodium', 'sodium'],\n ['potassium', 'potassium'],\n ['calcium', 'calcium'],\n ['iron', 'iron'],\n ['magnesium', 'magnesium'],\n ['zinc', 'zinc'],\n ['phosphorus', 'phosphorus'],\n ['selenium', 'selenium'],\n ['copper', 'copper'],\n ['manganese', 'manganese'],\n ['fluoride', 'fluoride'],\n ['chromium', 'chromium'],\n ['molybdenum', 'molybdenum'],\n ['iodine', 'iodine'],\n ['chloride', 'chloride'],\n ['vitaminA', 'vitamin_a'],\n ['retinol', 'retinol'],\n ['betaCarotene', 'beta_carotene'],\n ['alphaCarotene', 'alpha_carotene'],\n ['betaCryptoxanthin', 'beta_cryptoxanthin'],\n ['lycopene', 'lycopene'],\n ['luteinZeaxanthin', 'lutein_zeaxanthin'],\n ['vitaminD', 'vitamin_d'],\n ['vitaminD2', 'vitamin_d2'],\n ['vitaminD3', 'vitamin_d3'],\n ['vitaminE', 'vitamin_e'],\n ['vitaminK', 'vitamin_k'],\n ['vitaminK2', 'vitamin_k2'],\n ['vitaminC', 'vitamin_c'],\n ['vitaminB6', 'vitamin_b6'],\n ['vitaminB12', 'vitamin_b12'],\n ['thiamin', 'thiamin'],\n ['riboflavin', 'riboflavin'],\n ['niacin', 'niacin'],\n ['pantothenicAcid', 'pantothenic_acid'],\n ['biotin', 'biotin'],\n ['folate', 'folate'],\n ['folicAcid', 'folic_acid'],\n ['choline', 'choline'],\n ['betaine', 'betaine'],\n ];\n\n for (const [jsKey, dbColumn] of fieldMappings) {\n if (data[jsKey] !== undefined) {\n fields.push(`${dbColumn} = ?`);\n values.push(data[jsKey]);\n }\n }\n\n if (fields.length > 0) {\n fields.push('updated_at = CURRENT_TIMESTAMP');\n values.push(customFoodId, userId);\n await sqlite.execute(\n `UPDATE ${CUSTOM_FOODS_TABLE} SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`,\n values\n );\n }\n\n return getCustomFoodById(customFoodId, userId);\n}\n\nexport async function deleteCustomFood(customFoodId: string, userId: string): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${CUSTOM_FOODS_TABLE} WHERE id = ? AND user_id = ?`,\n [customFoodId, userId]\n );\n return result.rowsAffected > 0;\n}\n","deleted","label",[175,176,157],"backend","database","permalink","https://www.val.town/x/shapedlines/carmy/code/backend/database/queries.ts","extension","actionData","errors"]