[{"_1":2,"_174":-5,"_175":-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","78328b63035f6043dcd490c05f2538f6acc4d6c8","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,"_147":148,"_171":172,"_173":-5},"handle","connnolly","name","cardamom","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":116,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","308099fa-494d-11f0-89b9-76b3cceeab13","createdAt",["D",1749925632351],"deletedAt","legacyEnvVars","privacy","unlisted","description","imageUrl","ffdca6cc-55ff-4ccf-a010-18f9e71d2594","pinnedFileId","pinPreview","screenshotVersion","likeCount",2,"6b303590-0fbc-46d8-a44e-e68f092d28e0","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJ1S21DQ001blMycndJZm9kem5RdjJlem9lVCJ9","org",{"_93":94,"_132":129},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",400,"3080e702-494d-11f0-89b9-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":161,"_162":163,"_164":165,"_166":20,"_167":-5,"_97":168},"ddab250a-94c0-11f0-90f1-0224a6c84d84","queries.ts","fileId","8a187ae6-494d-11f0-9177-76b3cceeab13","parentId","895bec6b-49f9-4ebc-8526-787ae05ba14f","branchId","branchVersion",376,"latest",true,["D",1758221652195],55,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { RECIPES_TABLE, INGREDIENTS_TABLE, SHOPPING_LISTS_TABLE, SHOPPING_LIST_ITEMS_TABLE, EXCLUDED_INGREDIENTS_TABLE, MEAL_PLANS_TABLE } from \"./migrations.ts\";\nimport type { Recipe, Ingredient, RecipeFilters, ShoppingList, ShoppingListItem, PaginationParams, PaginationInfo, ExcludedIngredient, MealPlan } from \"../../shared/types.ts\";\nimport { categorizeIngredient, getCategorySortOrder } from \"../utils/ingredientCategorizer.ts\";\n\n// Transaction helper function for atomic multi-query operations using Val Town's batch method\nasync function executeTransaction(queries: Array<{sql: string, params: any[]}>): Promise {\n console.log(`🔄 Starting batch transaction with ${queries.length} queries`);\n \n try {\n // Convert our query format to Val Town's batch format\n const batchQueries = queries.map((query, index) => {\n console.log(`🔍 Preparing query ${index + 1}/${queries.length}: ${query.sql.substring(0, 50)}...`);\n \n // Val Town batch expects 'args' instead of 'params'\n return {\n sql: query.sql,\n args: query.params\n };\n });\n \n // Execute all queries as a batch transaction\n const results = await sqlite.batch(batchQueries);\n console.log('✅ Batch transaction completed successfully');\n \n return results;\n } catch (error) {\n // Val Town's batch method automatically handles rollback on failure\n console.error('❌ Batch transaction failed (auto-rollback):', error);\n throw error;\n }\n}\n\n// Test function to verify pagination functionality\nexport async function testPagination(): Promise<{ success: boolean, message: string }> {\n try {\n console.log('🧪 Testing pagination functionality...');\n \n // Create some test recipes to ensure we have data\n const testRecipes = [];\n for (let i = 1; i <= 5; i++) {\n const recipe = await createRecipe({\n title: `Test Recipe ${i}`,\n ingredients: [\n { name: `Ingredient ${i}A`, amount: '1', unit: 'cup' },\n { name: `Ingredient ${i}B`, amount: '2', unit: 'tbsp' }\n ],\n steps: [`Step 1 for recipe ${i}`, `Step 2 for recipe ${i}`]\n });\n testRecipes.push(recipe);\n }\n \n console.log(`✅ Created ${testRecipes.length} test recipes`);\n \n // Test pagination with limit 2\n const page1 = await getAllRecipesPaginated({}, { page: 1, limit: 2 });\n const page2 = await getAllRecipesPaginated({}, { page: 2, limit: 2 });\n \n // Verify pagination info\n if (page1.paginationInfo.limit !== 2) {\n throw new Error(`Expected limit 2, got ${page1.paginationInfo.limit}`);\n }\n \n if (page1.paginationInfo.page !== 1) {\n throw new Error(`Expected page 1, got ${page1.paginationInfo.page}`);\n }\n \n if (page1.recipes.length > 2) {\n throw new Error(`Expected max 2 recipes on page 1, got ${page1.recipes.length}`);\n }\n \n if (page2.paginationInfo.page !== 2) {\n throw new Error(`Expected page 2, got ${page2.paginationInfo.page}`);\n }\n \n // Test that page 1 and page 2 have different recipes (no overlap)\n const page1Ids = page1.recipes.map(r => r.id);\n const page2Ids = page2.recipes.map(r => r.id);\n const overlap = page1Ids.filter(id => page2Ids.includes(id));\n \n if (overlap.length > 0) {\n throw new Error(`Found overlapping recipes between pages: ${overlap}`);\n }\n \n // Test shopping list pagination\n const shoppingList = await createShoppingList('Test Pagination List', testRecipes.slice(0, 2).map(r => r.id!));\n const listPage1 = await getAllShoppingListsPaginated({ page: 1, limit: 10 });\n \n if (!listPage1.paginationInfo) {\n throw new Error('Shopping list pagination info missing');\n }\n \n // Clean up test data\n for (const recipe of testRecipes) {\n await deleteRecipe(recipe.id!);\n }\n await deleteShoppingList(shoppingList.id!);\n \n console.log(`🧹 Cleaned up test data`);\n \n return { \n success: true, \n message: `Pagination working correctly - tested ${testRecipes.length} recipes with page limits` \n };\n \n } catch (error) {\n console.error('❌ Pagination test failed:', error);\n return { \n success: false, \n message: `Pagination test failed: ${error.message}` \n };\n }\n}\n\n// Test function to verify optimized ingredient updates\nexport async function testIngredientUpdates(): Promise<{ success: boolean, message: string }> {\n try {\n console.log('🧪 Testing optimized ingredient updates...');\n \n // Create a test recipe with initial ingredients\n const testRecipe = await createRecipe({\n title: 'Test Recipe for Updates',\n ingredients: [\n { name: 'Initial Ingredient 1', amount: '1', unit: 'cup' },\n { name: 'Initial Ingredient 2', amount: '2', unit: 'tbsp' }\n ],\n steps: ['Step 1', 'Step 2']\n });\n \n console.log(`✅ Created test recipe with ID: ${testRecipe.id}`);\n \n // Update with different ingredients (should use bulk INSERT)\n const updatedRecipe = await updateRecipe(testRecipe.id, {\n title: 'Updated Test Recipe',\n ingredients: [\n { name: 'New Ingredient 1', amount: '3', unit: 'oz' },\n { name: 'New Ingredient 2', amount: '1', unit: 'tsp' },\n { name: 'New Ingredient 3', amount: '1/2', unit: 'cup' }\n ]\n });\n \n console.log(`✅ Updated recipe successfully`);\n \n // Verify the update worked correctly\n if (!updatedRecipe) {\n throw new Error('Updated recipe is null');\n }\n \n if (updatedRecipe.ingredients.length !== 3) {\n throw new Error(`Expected 3 ingredients, got ${updatedRecipe.ingredients.length}`);\n }\n \n if (updatedRecipe.title !== 'Updated Test Recipe') {\n throw new Error(`Title not updated correctly`);\n }\n \n // Clean up - delete test recipe\n await deleteRecipe(testRecipe.id);\n console.log(`🧹 Cleaned up test recipe`);\n \n return { \n success: true, \n message: `Optimized ingredient updates working correctly - bulk INSERT with ${updatedRecipe.ingredients.length} ingredients` \n };\n \n } catch (error) {\n console.error('❌ Ingredient update test failed:', error);\n return { \n success: false, \n message: `Ingredient update test failed: ${error.message}` \n };\n }\n}\n\n// Test function to verify transaction rollback behavior\nexport async function testTransactionRollback(): Promise<{ success: boolean, message: string }> {\n try {\n console.log('🧪 Testing transaction rollback behavior...');\n \n // Try to execute a transaction with an intentional error\n const badQueries = [\n {\n sql: `INSERT INTO ${RECIPES_TABLE} (title, steps) VALUES (?, ?)`,\n params: ['Test Recipe', JSON.stringify(['Step 1'])]\n },\n {\n sql: `INSERT INTO non_existent_table (id, name) VALUES (?, ?)`, // This will fail\n params: [1, 'test']\n }\n ];\n \n await executeTransaction(badQueries);\n \n // If we get here, something went wrong\n return { success: false, message: 'Transaction should have failed but did not' };\n \n } catch (error) {\n console.log('✅ Transaction correctly rolled back on error');\n \n // Verify that no test recipe was created\n const testRecipe = await sqlite.execute(`\n SELECT * FROM ${RECIPES_TABLE} WHERE title = 'Test Recipe'\n `);\n \n if (testRecipe.rows.length === 0) {\n return { success: true, message: 'Transaction rollback working correctly - no orphaned data found' };\n } else {\n return { success: false, message: 'Transaction rollback failed - orphaned data found' };\n }\n }\n}\n\nexport async function createRecipe(recipe: Omit): Promise {\n const { ingredients, steps, tags, ...recipeData } = recipe;\n \n // Validate ingredients array\n if (!ingredients || ingredients.length === 0) {\n throw new Error('Recipe must have at least one ingredient');\n }\n \n // Validate steps array\n if (!steps || steps.length === 0) {\n throw new Error('Recipe must have at least one step');\n }\n \n // Prepare all queries for transaction\n const queries = [];\n \n // First query: Insert recipe\n queries.push({\n sql: `INSERT INTO ${RECIPES_TABLE} \n (title, description, servings, prep_time, cook_time, difficulty, tags, source, image_url, steps)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n params: [\n recipeData.title,\n recipeData.description || null,\n recipeData.servings || null,\n recipeData.prepTime || null,\n recipeData.cookTime || null,\n recipeData.difficulty || null,\n tags ? JSON.stringify(tags) : null,\n recipeData.source || null,\n recipeData.imageUrl || null,\n JSON.stringify(steps)\n ]\n });\n \n // Execute the recipe insertion first to get the ID\n const [recipeResult] = await executeTransaction(queries);\n \n // Get the recipe ID\n const recipeId = recipeResult.lastInsertRowid;\n \n if (!recipeId) {\n console.error('Failed to get recipe ID from insert result:', recipeResult);\n throw new Error('Failed to create recipe: no ID returned from database');\n }\n \n // Convert to number if it's a string\n const numericRecipeId = typeof recipeId === 'string' ? parseInt(recipeId, 10) : Number(recipeId);\n \n if (isNaN(numericRecipeId) || numericRecipeId <= 0) {\n console.error('Recipe ID is not a valid number:', recipeId);\n throw new Error('Failed to create recipe: invalid ID returned from database');\n }\n\n // Prepare ingredient insertion queries\n const ingredientQueries = [];\n for (let i = 0; i < ingredients.length; i++) {\n const ingredient = ingredients[i];\n \n ingredientQueries.push({\n sql: `INSERT INTO ${INGREDIENTS_TABLE} \n (recipe_id, name, amount, unit, notes, order_index)\n VALUES (?, ?, ?, ?, ?, ?)`,\n params: [\n numericRecipeId,\n ingredient.name,\n ingredient.amount || '',\n ingredient.unit || '',\n ingredient.notes || null,\n i\n ]\n });\n }\n \n // Execute all ingredient insertions in a single transaction\n if (ingredientQueries.length > 0) {\n await executeTransaction(ingredientQueries);\n }\n\n return await getRecipeById(numericRecipeId);\n}\n\nexport async function getRecipeById(id: number): Promise {\n const recipeResult = await sqlite.execute(`\n SELECT * FROM ${RECIPES_TABLE} WHERE id = ?\n `, [id]);\n\n if (recipeResult.rows.length === 0) {\n return null;\n }\n\n const recipeRow = recipeResult.rows[0];\n \n const ingredientsResult = await sqlite.execute(`\n SELECT * FROM ${INGREDIENTS_TABLE} \n WHERE recipe_id = ? \n ORDER BY order_index\n `, [id]);\n\n const ingredients: Ingredient[] = ingredientsResult.rows.map(row => ({\n id: row.id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined\n }));\n\n return {\n id: recipeRow.id as number,\n title: recipeRow.title as string,\n description: recipeRow.description as string || undefined,\n ingredients,\n steps: JSON.parse(recipeRow.steps as string),\n servings: recipeRow.servings as number || undefined,\n prepTime: recipeRow.prep_time as number || undefined,\n cookTime: recipeRow.cook_time as number || undefined,\n difficulty: recipeRow.difficulty as Recipe['difficulty'] || undefined,\n tags: recipeRow.tags ? JSON.parse(recipeRow.tags as string) : undefined,\n source: recipeRow.source as string || undefined,\n imageUrl: recipeRow.image_url as string || undefined,\n createdAt: recipeRow.created_at as string,\n updatedAt: recipeRow.updated_at as string\n };\n}\n\n// Helper function to create pagination info\nfunction createPaginationInfo(page: number, limit: number, total: number): PaginationInfo {\n const totalPages = Math.ceil(total / limit);\n return {\n page,\n limit,\n total,\n totalPages,\n hasNext: page < totalPages,\n hasPrev: page > 1\n };\n}\n\n// Helper function to validate and normalize pagination parameters\nfunction validatePaginationParams(params?: PaginationParams): { page: number, limit: number, offset: number } {\n const page = Math.max(1, params?.page || 1);\n const limit = Math.min(100, Math.max(1, params?.limit || 50)); // Default 50, max 100\n const offset = (page - 1) * limit;\n \n return { page, limit, offset };\n}\n\nexport async function getAllRecipesPaginated(filters?: RecipeFilters, pagination?: PaginationParams): Promise<{ recipes: Recipe[], paginationInfo: PaginationInfo }> {\n const { page, limit, offset } = validatePaginationParams(pagination);\n \n // Build base query for counting total results\n let countQuery = `SELECT COUNT(*) as total FROM ${RECIPES_TABLE}`;\n let dataQuery = `SELECT * FROM ${RECIPES_TABLE}`;\n \n const params: any[] = [];\n const conditions: string[] = [];\n\n // Apply filters\n if (filters?.search) {\n conditions.push(`title LIKE ?`);\n params.push(`%${filters.search}%`);\n }\n\n if (filters?.difficulty) {\n conditions.push(`difficulty = ?`);\n params.push(filters.difficulty);\n }\n\n if (filters?.maxPrepTime) {\n conditions.push(`prep_time <= ?`);\n params.push(filters.maxPrepTime);\n }\n\n if (filters?.maxCookTime) {\n conditions.push(`cook_time <= ?`);\n params.push(filters.maxCookTime);\n }\n\n // Add WHERE clause if there are conditions\n if (conditions.length > 0) {\n const whereClause = ` WHERE ${conditions.join(' AND ')}`;\n countQuery += whereClause;\n dataQuery += whereClause;\n }\n\n // Add ORDER BY, LIMIT, and OFFSET to data query\n dataQuery += ` ORDER BY created_at DESC LIMIT ? OFFSET ?`;\n const dataParams = [...params, limit, offset];\n\n // Execute both queries\n const [countResult, dataResult] = await Promise.all([\n sqlite.execute(countQuery, params),\n sqlite.execute(dataQuery, dataParams)\n ]);\n\n const total = countResult.rows[0].total as number;\n \n // If no recipes found, return empty results\n if (dataResult.rows.length === 0) {\n return {\n recipes: [],\n paginationInfo: createPaginationInfo(page, limit, total)\n };\n }\n\n // Batch fetch all ingredients for the paginated recipes\n const recipeIds = dataResult.rows.map(row => row.id as number);\n const placeholders = recipeIds.map(() => '?').join(',');\n \n const ingredientsResult = await sqlite.execute(`\n SELECT * FROM ${INGREDIENTS_TABLE} \n WHERE recipe_id IN (${placeholders})\n ORDER BY recipe_id, order_index\n `, recipeIds);\n\n // Create a map of recipe_id to ingredients for efficient lookup\n const ingredientsByRecipeId = new Map();\n \n for (const row of ingredientsResult.rows) {\n const recipeId = row.recipe_id as number;\n if (!ingredientsByRecipeId.has(recipeId)) {\n ingredientsByRecipeId.set(recipeId, []);\n }\n \n ingredientsByRecipeId.get(recipeId)!.push({\n id: row.id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined\n });\n }\n\n // Build the recipes array with their ingredients\n const recipes: Recipe[] = dataResult.rows.map(recipeRow => {\n const ingredients = ingredientsByRecipeId.get(recipeRow.id as number) || [];\n \n return {\n id: recipeRow.id as number,\n title: recipeRow.title as string,\n description: recipeRow.description as string || undefined,\n ingredients,\n steps: JSON.parse(recipeRow.steps as string),\n servings: recipeRow.servings as number || undefined,\n prepTime: recipeRow.prep_time as number || undefined,\n cookTime: recipeRow.cook_time as number || undefined,\n difficulty: recipeRow.difficulty as Recipe['difficulty'] || undefined,\n tags: recipeRow.tags ? JSON.parse(recipeRow.tags as string) : undefined,\n source: recipeRow.source as string || undefined,\n imageUrl: recipeRow.image_url as string || undefined,\n createdAt: recipeRow.created_at as string,\n updatedAt: recipeRow.updated_at as string\n };\n });\n\n return {\n recipes,\n paginationInfo: createPaginationInfo(page, limit, total)\n };\n}\n\nexport async function getAllRecipes(filters?: RecipeFilters): Promise {\n let query = `SELECT * FROM ${RECIPES_TABLE}`;\n const params: any[] = [];\n const conditions: string[] = [];\n\n if (filters?.search) {\n conditions.push(`title LIKE ?`);\n params.push(`%${filters.search}%`);\n }\n\n if (filters?.difficulty) {\n conditions.push(`difficulty = ?`);\n params.push(filters.difficulty);\n }\n\n if (filters?.maxPrepTime) {\n conditions.push(`prep_time <= ?`);\n params.push(filters.maxPrepTime);\n }\n\n if (filters?.maxCookTime) {\n conditions.push(`cook_time <= ?`);\n params.push(filters.maxCookTime);\n }\n\n if (conditions.length > 0) {\n query += ` WHERE ${conditions.join(' AND ')}`;\n }\n\n query += ` ORDER BY created_at DESC`;\n\n const result = await sqlite.execute(query, params);\n \n // If no recipes found, return empty array\n if (result.rows.length === 0) {\n return [];\n }\n\n // Batch fetch all ingredients for all recipes in one query\n const recipeIds = result.rows.map(row => row.id as number);\n const placeholders = recipeIds.map(() => '?').join(',');\n \n const ingredientsResult = await sqlite.execute(`\n SELECT * FROM ${INGREDIENTS_TABLE} \n WHERE recipe_id IN (${placeholders})\n ORDER BY recipe_id, order_index\n `, recipeIds);\n\n // Create a map of recipe_id to ingredients for efficient lookup\n const ingredientsByRecipeId = new Map();\n \n for (const row of ingredientsResult.rows) {\n const recipeId = row.recipe_id as number;\n if (!ingredientsByRecipeId.has(recipeId)) {\n ingredientsByRecipeId.set(recipeId, []);\n }\n \n ingredientsByRecipeId.get(recipeId)!.push({\n id: row.id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined\n });\n }\n\n // Build the recipes array with their ingredients\n const recipes: Recipe[] = result.rows.map(recipeRow => {\n const ingredients = ingredientsByRecipeId.get(recipeRow.id as number) || [];\n \n return {\n id: recipeRow.id as number,\n title: recipeRow.title as string,\n description: recipeRow.description as string || undefined,\n ingredients,\n steps: JSON.parse(recipeRow.steps as string),\n servings: recipeRow.servings as number || undefined,\n prepTime: recipeRow.prep_time as number || undefined,\n cookTime: recipeRow.cook_time as number || undefined,\n difficulty: recipeRow.difficulty as Recipe['difficulty'] || undefined,\n tags: recipeRow.tags ? JSON.parse(recipeRow.tags as string) : undefined,\n source: recipeRow.source as string || undefined,\n imageUrl: recipeRow.image_url as string || undefined,\n createdAt: recipeRow.created_at as string,\n updatedAt: recipeRow.updated_at as string\n };\n });\n\n return recipes;\n}\n\nexport async function updateRecipe(id: number, updates: Partial): Promise {\n const { ingredients, steps, tags, ...recipeData } = updates;\n \n // Prepare all queries for transaction\n const queries = [];\n \n // Build recipe update query if there are fields to update\n const setParts: string[] = [];\n const params: any[] = [];\n\n Object.entries(recipeData).forEach(([key, value]) => {\n if (value !== undefined && key !== 'id' && key !== 'createdAt' && key !== 'updatedAt') {\n const dbKey = key === 'prepTime' ? 'prep_time' : \n key === 'cookTime' ? 'cook_time' : \n key === 'imageUrl' ? 'image_url' : key;\n setParts.push(`${dbKey} = ?`);\n params.push(value);\n }\n });\n\n if (steps) {\n setParts.push(`steps = ?`);\n params.push(JSON.stringify(steps));\n }\n\n if (tags) {\n setParts.push(`tags = ?`);\n params.push(JSON.stringify(tags));\n }\n\n // Add recipe update query if there are changes\n if (setParts.length > 0) {\n setParts.push(`updated_at = CURRENT_TIMESTAMP`);\n params.push(id);\n \n queries.push({\n sql: `UPDATE ${RECIPES_TABLE} \n SET ${setParts.join(', ')} \n WHERE id = ?`,\n params: params\n });\n }\n\n // Add ingredient update queries if ingredients are provided\n if (ingredients && ingredients.length > 0) {\n // Validate ingredients before processing\n for (let i = 0; i < ingredients.length; i++) {\n const ingredient = ingredients[i];\n if (!ingredient.name || typeof ingredient.name !== 'string') {\n throw new Error(`Ingredient ${i + 1} must have a valid name`);\n }\n }\n \n // First delete existing ingredients\n queries.push({\n sql: `DELETE FROM ${INGREDIENTS_TABLE} WHERE recipe_id = ?`,\n params: [id]\n });\n \n // Then bulk insert new ingredients using multiple value rows in a single INSERT\n // Note: SQLite supports multiple VALUES in a single INSERT for better performance\n const ingredientValues = [];\n const ingredientParams = [];\n \n for (let i = 0; i < ingredients.length; i++) {\n const ingredient = ingredients[i];\n ingredientValues.push('(?, ?, ?, ?, ?, ?)');\n ingredientParams.push(\n id,\n ingredient.name.trim(),\n (ingredient.amount || '').toString().trim(),\n (ingredient.unit || '').toString().trim(),\n ingredient.notes && typeof ingredient.notes === 'string' ? ingredient.notes.trim() : null,\n i\n );\n }\n \n // Single bulk INSERT query instead of multiple individual INSERTs\n queries.push({\n sql: `INSERT INTO ${INGREDIENTS_TABLE} \n (recipe_id, name, amount, unit, notes, order_index)\n VALUES ${ingredientValues.join(', ')}`,\n params: ingredientParams\n });\n }\n\n // Execute all queries in a single transaction if there are any\n if (queries.length > 0) {\n await executeTransaction(queries);\n }\n\n return await getRecipeById(id);\n}\n\nexport async function deleteRecipe(id: number): Promise {\n const result = await sqlite.execute(`DELETE FROM ${RECIPES_TABLE} WHERE id = ?`, [id]);\n \n // The SQLite library returns rowsAffected, not changes\n return (result.rowsAffected || 0) > 0;\n}\n\n// Shopping List Functions\n\nexport async function createShoppingList(name: string, recipeIds: number[]): Promise {\n console.log('Creating shopping list with name:', name, 'and recipe IDs:', recipeIds);\n \n try {\n // First, prepare the shopping list creation query\n const listQuery = {\n sql: `INSERT INTO ${SHOPPING_LISTS_TABLE} (name) VALUES (?)`,\n params: [name]\n };\n \n // Execute shopping list creation to get the ID\n const [listResult] = await executeTransaction([listQuery]);\n console.log('List creation result:', listResult);\n\n const listId = listResult.lastInsertRowid;\n if (!listId) {\n throw new Error('Failed to create shopping list');\n }\n\n const numericListId = typeof listId === 'string' ? parseInt(listId, 10) : Number(listId);\n console.log('Created shopping list with ID:', numericListId);\n\n // Get all ingredients from the selected recipes\n if (recipeIds.length === 0) {\n throw new Error('No recipe IDs provided');\n }\n\n const placeholders = recipeIds.map(() => '?').join(',');\n const ingredients = await sqlite.execute(`\n SELECT i.name, i.amount, i.unit, i.notes, i.recipe_id\n FROM ${INGREDIENTS_TABLE} i\n WHERE i.recipe_id IN (${placeholders})\n ORDER BY i.name, i.recipe_id\n `, recipeIds);\n\n console.log('Ingredients query result:', ingredients);\n\n // Check if ingredients result is valid\n if (!ingredients || !ingredients.rows || !Array.isArray(ingredients.rows)) {\n console.error('Invalid ingredients result structure:', ingredients);\n // If no ingredients found, still create the shopping list but with no items\n return await getShoppingListById(numericListId);\n }\n\n // Get excluded ingredients to filter them out\n const excludedIngredients = await getAllExcludedIngredients();\n console.log(`Found ${excludedIngredients.length} excluded ingredient patterns`);\n\n // Filter out excluded ingredients\n const filteredIngredients = ingredients.rows.filter(ingredient => {\n // Handle both array and object formats\n let name;\n if (Array.isArray(ingredient)) {\n name = ingredient[0];\n } else {\n name = ingredient.name;\n }\n \n const isExcluded = isIngredientExcluded(name as string, excludedIngredients);\n if (isExcluded) {\n console.log(`Excluding ingredient: ${name}`);\n }\n return !isExcluded;\n });\n\n console.log(`Filtered ${ingredients.rows.length - filteredIngredients.length} excluded ingredients`);\n\n // If no ingredients remain after filtering, still create the shopping list but with no items\n if (filteredIngredients.length === 0) {\n console.log('No ingredients remaining after filtering excluded items');\n return await getShoppingListById(numericListId);\n }\n\n // Smart ingredient combination with fuzzy matching\n const combinedIngredients = new Map();\n\n // In-memory cache for ingredient name normalization (performance optimization)\n const normalizationCache = new Map();\n\n // Helper function to normalize ingredient names for smart matching with caching\n function normalizeIngredientName(name: string): string {\n // Check cache first for O(1) lookup\n if (normalizationCache.has(name)) {\n return normalizationCache.get(name)!;\n }\n\n const normalized = name.toLowerCase().trim();\n \n // Remove common descriptors and variations\n const cleanName = normalized\n // Remove size descriptors\n .replace(/\\b(large|medium|small|extra-large|jumbo)\\b/g, '')\n // Remove preparation descriptors \n .replace(/\\b(fresh|dried|frozen|canned|chopped|diced|sliced|minced|grated|shredded)\\b/g, '')\n // Remove packaging descriptors\n .replace(/\\b(whole|ground|crushed|flaked|powdered)\\b/g, '')\n // Normalize herbs and spices\n .replace(/\\bparsley sprigs?\\b/g, 'parsley')\n .replace(/\\bcilantro sprigs?\\b/g, 'cilantro')\n .replace(/\\bbasil leaves?\\b/g, 'basil')\n // Normalize oils\n .replace(/\\bextra-virgin olive oil\\b/g, 'olive oil')\n .replace(/\\bolive oil, extra-virgin\\b/g, 'olive oil')\n // Normalize salts\n .replace(/\\bkosher salt\\b/g, 'salt')\n .replace(/\\bsea salt\\b/g, 'salt')\n .replace(/\\btable salt\\b/g, 'salt')\n .replace(/\\bflaky salt\\b/g, 'salt')\n .replace(/\\bflaky sea salt\\b/g, 'salt')\n // Normalize peppers\n .replace(/\\bfreshly ground black pepper\\b/g, 'black pepper')\n .replace(/\\bground black pepper\\b/g, 'black pepper')\n .replace(/\\bblack peppercorns?\\b/g, 'black pepper')\n // Normalize common ingredients\n .replace(/\\bunsalted butter\\b/g, 'butter')\n .replace(/\\bsalted butter\\b/g, 'butter')\n .replace(/\\ball-purpose flour\\b/g, 'flour')\n .replace(/\\bplain flour\\b/g, 'flour')\n // Remove extra whitespace\n .replace(/\\s+/g, ' ')\n .trim();\n \n const result = cleanName || normalized; // Fallback to original if cleaning removed everything\n \n // Cache the result for future lookups (reduces O(n×m) to O(1) for repeat ingredients)\n normalizationCache.set(name, result);\n \n return result;\n }\n\n for (const ingredient of filteredIngredients) {\n // Handle both array and object formats\n let name, amount, unit, notes, recipeId;\n if (Array.isArray(ingredient)) {\n [name, amount, unit, notes, recipeId] = ingredient;\n } else {\n name = ingredient.name;\n amount = ingredient.amount;\n unit = ingredient.unit;\n notes = ingredient.notes;\n recipeId = ingredient.recipe_id;\n }\n \n const originalName = name as string;\n const normalizedKey = normalizeIngredientName(originalName);\n \n if (!combinedIngredients.has(normalizedKey)) {\n combinedIngredients.set(normalizedKey, {\n name: originalName, // Use the first occurrence as the display name\n quantities: [],\n notes: []\n });\n }\n\n const combined = combinedIngredients.get(normalizedKey)!;\n \n // Add this quantity (don't combine amounts, just collect them)\n combined.quantities.push({\n amount: (amount as string) || '',\n unit: (unit as string) || '',\n recipeIds: [recipeId as number],\n originalName: originalName\n });\n\n // Collect unique notes\n if (notes && !combined.notes.includes(notes as string)) {\n combined.notes.push(notes as string);\n }\n }\n\n // Prepare shopping list item insertion queries with categories\n const itemsWithCategories = [];\n \n for (const [, combined] of combinedIngredients) {\n // Create a comprehensive description of all quantities\n const quantityDescriptions = combined.quantities\n .map(q => {\n const amountUnit = `${q.amount} ${q.unit}`.trim();\n // Only include non-empty amounts\n if (!amountUnit || amountUnit === '') {\n return null;\n }\n const fromRecipe = q.recipeIds.length > 0 ? ` (recipe ${q.recipeIds[0]})` : '';\n return amountUnit + fromRecipe;\n })\n .filter(desc => desc !== null);\n\n // Combine all quantities into a single amount field\n const combinedAmount = quantityDescriptions.length > 0 \n ? quantityDescriptions.join(' + ') \n : 'as needed';\n \n // Use the most common unit, or empty if mixed\n const units = combined.quantities.map(q => q.unit).filter(u => u);\n const mostCommonUnit = units.length > 0 ? units[0] : '';\n \n // Collect all recipe IDs\n const allRecipeIds = combined.quantities.flatMap(q => q.recipeIds);\n \n // Categorize the ingredient\n const category = categorizeIngredient(combined.name);\n \n itemsWithCategories.push({\n name: combined.name,\n amount: combinedAmount,\n unit: mostCommonUnit,\n notes: combined.notes.join('; ') || null,\n recipeIds: allRecipeIds,\n category: category\n });\n }\n \n // Sort items by category order\n itemsWithCategories.sort((a, b) => {\n const orderA = getCategorySortOrder(a.category as any);\n const orderB = getCategorySortOrder(b.category as any);\n if (orderA !== orderB) {\n return orderA - orderB;\n }\n // Within same category, sort alphabetically by name\n return a.name.localeCompare(b.name);\n });\n \n // Create insertion queries with sorted order\n const itemQueries = [];\n let orderIndex = 0;\n \n for (const item of itemsWithCategories) {\n itemQueries.push({\n sql: `INSERT INTO ${SHOPPING_LIST_ITEMS_TABLE} \n (shopping_list_id, name, amount, unit, notes, recipe_ids, order_index, checked, category)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n params: [\n numericListId,\n item.name,\n item.amount,\n item.unit,\n item.notes,\n JSON.stringify(item.recipeIds),\n orderIndex++,\n 0,\n item.category\n ]\n });\n }\n \n // Execute all item insertions in a single transaction\n if (itemQueries.length > 0) {\n await executeTransaction(itemQueries);\n }\n\n return await getShoppingListById(numericListId);\n } catch (error) {\n console.error('Error in createShoppingList:', error);\n throw error;\n }\n}\n\nexport async function getAllShoppingListsPaginated(pagination?: PaginationParams): Promise<{ shoppingLists: ShoppingList[], paginationInfo: PaginationInfo }> {\n const { page, limit, offset } = validatePaginationParams(pagination);\n \n // Get total count of shopping lists\n const countResult = await sqlite.execute(`\n SELECT COUNT(*) as total FROM ${SHOPPING_LISTS_TABLE}\n `);\n \n const total = countResult.rows[0].total as number;\n \n // Get paginated shopping lists\n const lists = await sqlite.execute(`\n SELECT id, name, created_at, updated_at\n FROM ${SHOPPING_LISTS_TABLE}\n ORDER BY created_at DESC\n LIMIT ? OFFSET ?\n `, [limit, offset]);\n\n // If no lists found, return empty results\n if (lists.rows.length === 0) {\n return {\n shoppingLists: [],\n paginationInfo: createPaginationInfo(page, limit, total)\n };\n }\n\n // Batch fetch all items for the paginated shopping lists\n const listIds = lists.rows.map(row => row.id as number);\n const placeholders = listIds.map(() => '?').join(',');\n \n const itemsResult = await sqlite.execute(`\n SELECT id, shopping_list_id, name, amount, unit, notes, checked, recipe_ids, order_index\n FROM ${SHOPPING_LIST_ITEMS_TABLE}\n WHERE shopping_list_id IN (${placeholders})\n ORDER BY shopping_list_id, order_index, name\n `, listIds);\n\n // Create a map of shopping_list_id to items for efficient lookup\n const itemsByListId = new Map();\n \n for (const row of itemsResult.rows) {\n const listId = row.shopping_list_id as number;\n if (!itemsByListId.has(listId)) {\n itemsByListId.set(listId, []);\n }\n \n itemsByListId.get(listId)!.push({\n id: row.id as number,\n shoppingListId: row.shopping_list_id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined,\n checked: Boolean(row.checked),\n recipeIds: JSON.parse(row.recipe_ids as string)\n });\n }\n\n // Build the shopping lists array with their items\n const shoppingLists: ShoppingList[] = lists.rows.map(row => ({\n id: row.id as number,\n name: row.name as string,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n items: itemsByListId.get(row.id as number) || []\n }));\n\n return {\n shoppingLists,\n paginationInfo: createPaginationInfo(page, limit, total)\n };\n}\n\nexport async function getAllShoppingLists(): Promise {\n const lists = await sqlite.execute(`\n SELECT id, name, created_at, updated_at\n FROM ${SHOPPING_LISTS_TABLE}\n ORDER BY created_at DESC\n `);\n\n // If no lists found, return empty array\n if (lists.rows.length === 0) {\n return [];\n }\n\n // Batch fetch all items for all shopping lists in one query\n const listIds = lists.rows.map(row => row.id as number);\n const placeholders = listIds.map(() => '?').join(',');\n \n const itemsResult = await sqlite.execute(`\n SELECT id, shopping_list_id, name, amount, unit, notes, checked, recipe_ids, order_index\n FROM ${SHOPPING_LIST_ITEMS_TABLE}\n WHERE shopping_list_id IN (${placeholders})\n ORDER BY shopping_list_id, order_index, name\n `, listIds);\n\n // Create a map of shopping_list_id to items for efficient lookup\n const itemsByListId = new Map();\n \n for (const row of itemsResult.rows) {\n const listId = row.shopping_list_id as number;\n if (!itemsByListId.has(listId)) {\n itemsByListId.set(listId, []);\n }\n \n itemsByListId.get(listId)!.push({\n id: row.id as number,\n shoppingListId: row.shopping_list_id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined,\n checked: Boolean(row.checked),\n recipeIds: JSON.parse(row.recipe_ids as string)\n });\n }\n\n // Build the shopping lists array with their items\n const result: ShoppingList[] = lists.rows.map(row => ({\n id: row.id as number,\n name: row.name as string,\n createdAt: row.created_at as string,\n updatedAt: row.updated_at as string,\n items: itemsByListId.get(row.id as number) || []\n }));\n\n return result;\n}\n\nexport async function getShoppingListById(id: number): Promise {\n const lists = await sqlite.execute(`\n SELECT id, name, created_at, updated_at\n FROM ${SHOPPING_LISTS_TABLE}\n WHERE id = ?\n `, [id]);\n\n if (lists.rows.length === 0) {\n return null;\n }\n\n const row = lists.rows[0];\n const items = await getShoppingListItems(id);\n\n return {\n id: row.id as number,\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\nasync function getShoppingListItems(shoppingListId: number): Promise {\n const items = await sqlite.execute(`\n SELECT id, shopping_list_id, name, amount, unit, notes, checked, recipe_ids, order_index, category\n FROM ${SHOPPING_LIST_ITEMS_TABLE}\n WHERE shopping_list_id = ?\n ORDER BY order_index, name\n `, [shoppingListId]);\n\n return items.rows.map(row => {\n return {\n id: row.id as number,\n shoppingListId: row.shopping_list_id as number,\n name: row.name as string,\n amount: row.amount as string,\n unit: row.unit as string,\n notes: row.notes as string || undefined,\n checked: Boolean(row.checked), // Convert SQLite integer to boolean\n recipeIds: JSON.parse(row.recipe_ids as string),\n category: row.category as string || 'other'\n };\n });\n}\n\nexport async function updateShoppingListName(id: number, name: string): Promise {\n const result = await sqlite.execute(`\n UPDATE ${SHOPPING_LISTS_TABLE}\n SET name = ?, updated_at = CURRENT_TIMESTAMP\n WHERE id = ?\n `, [name, id]);\n\n return (result.rowsAffected || 0) > 0;\n}\n\nexport async function updateShoppingListItem(id: number, updates: Partial): Promise {\n const setParts: string[] = [];\n const params: any[] = [];\n\n if (updates.checked !== undefined) {\n setParts.push('checked = ?');\n params.push(updates.checked ? 1 : 0); // Convert boolean to integer for SQLite\n }\n\n if (updates.amount !== undefined) {\n setParts.push('amount = ?');\n params.push(updates.amount);\n }\n\n if (updates.unit !== undefined) {\n setParts.push('unit = ?');\n params.push(updates.unit);\n }\n\n if (updates.notes !== undefined) {\n setParts.push('notes = ?');\n params.push(updates.notes);\n }\n\n if (setParts.length === 0) {\n return false;\n }\n\n params.push(id);\n\n const result = await sqlite.execute(`\n UPDATE ${SHOPPING_LIST_ITEMS_TABLE}\n SET ${setParts.join(', ')}\n WHERE id = ?\n `, params);\n\n return (result.rowsAffected || 0) > 0;\n}\n\nexport async function deleteShoppingList(id: number): Promise {\n const result = await sqlite.execute(`DELETE FROM ${SHOPPING_LISTS_TABLE} WHERE id = ?`, [id]);\n return (result.rowsAffected || 0) > 0;\n}\n\nexport async function deleteShoppingListItem(id: number): Promise {\n const result = await sqlite.execute(`DELETE FROM ${SHOPPING_LIST_ITEMS_TABLE} WHERE id = ?`, [id]);\n return (result.rowsAffected || 0) > 0;\n}\n\nexport async function addShoppingListItem(\n shoppingListId: number,\n name: string,\n amount?: string,\n unit?: string,\n notes?: string,\n category?: string\n): Promise {\n try {\n // Get the current maximum order_index for this shopping list\n const maxOrderResult = await sqlite.execute(`\n SELECT MAX(order_index) as max_order FROM ${SHOPPING_LIST_ITEMS_TABLE}\n WHERE shopping_list_id = ?\n `, [shoppingListId]);\n\n const maxOrder = (maxOrderResult.rows[0]?.max_order as number) ?? -1;\n const newOrderIndex = maxOrder + 1;\n\n // Categorize the ingredient if no category provided\n const itemCategory = category || categorizeIngredient(name);\n\n // Insert the new item\n const result = await sqlite.execute(`\n INSERT INTO ${SHOPPING_LIST_ITEMS_TABLE}\n (shopping_list_id, name, amount, unit, notes, recipe_ids, order_index, checked, category)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n shoppingListId,\n name.trim(),\n amount?.trim() || '',\n unit?.trim() || '',\n notes?.trim() || null,\n JSON.stringify([]), // Manual items have no recipe associations\n newOrderIndex,\n 0, // Not checked by default\n itemCategory\n ]);\n\n const newItemId = result.lastInsertRowid as number;\n\n // Return the newly created item\n return {\n id: newItemId,\n shoppingListId,\n name: name.trim(),\n amount: amount?.trim() || '',\n unit: unit?.trim() || '',\n notes: notes?.trim(),\n checked: false,\n recipeIds: [],\n category: itemCategory\n };\n } catch (error) {\n console.error('Error adding shopping list item:', error);\n return null;\n }\n}\n\n// Test function to measure ingredient normalization cache performance\nexport async function testIngredientNormalizationCache(): Promise<{\n message: string;\n metrics: {\n withoutCache: { time: number; normalizations: number };\n withCache: { time: number; normalizations: number; cacheHitRate: number };\n performanceImprovement: string;\n };\n}> {\n // Create test ingredient names with realistic duplicates\n const testIngredients = [\n 'fresh basil leaves', 'dried basil', 'basil leaves',\n 'kosher salt', 'sea salt', 'table salt', 'flaky salt',\n 'extra-virgin olive oil', 'olive oil, extra-virgin', 'olive oil',\n 'large yellow onion', 'medium yellow onion', 'yellow onion',\n 'freshly ground black pepper', 'ground black pepper', 'black pepper',\n 'unsalted butter', 'salted butter', 'butter',\n 'all-purpose flour', 'plain flour', 'flour',\n // Add duplicates to test cache effectiveness\n 'fresh basil leaves', 'kosher salt', 'extra-virgin olive oil',\n 'large yellow onion', 'freshly ground black pepper', 'unsalted butter'\n ];\n\n // Test without cache (simulate original function)\n let normalizationCount = 0;\n function normalizeWithoutCache(name: string): string {\n normalizationCount++;\n const normalized = name.toLowerCase().trim();\n \n const cleanName = normalized\n .replace(/\\b(large|medium|small|extra-large|jumbo)\\b/g, '')\n .replace(/\\b(fresh|dried|frozen|canned|chopped|diced|sliced|minced|grated|shredded)\\b/g, '')\n .replace(/\\b(whole|ground|crushed|flaked|powdered)\\b/g, '')\n .replace(/\\bparsley sprigs?\\b/g, 'parsley')\n .replace(/\\bcilantro sprigs?\\b/g, 'cilantro')\n .replace(/\\bbasil leaves?\\b/g, 'basil')\n .replace(/\\bextra-virgin olive oil\\b/g, 'olive oil')\n .replace(/\\bolive oil, extra-virgin\\b/g, 'olive oil')\n .replace(/\\bkosher salt\\b/g, 'salt')\n .replace(/\\bsea salt\\b/g, 'salt')\n .replace(/\\btable salt\\b/g, 'salt')\n .replace(/\\bflaky salt\\b/g, 'salt')\n .replace(/\\bflaky sea salt\\b/g, 'salt')\n .replace(/\\bfreshly ground black pepper\\b/g, 'black pepper')\n .replace(/\\bground black pepper\\b/g, 'black pepper')\n .replace(/\\bblack peppercorns?\\b/g, 'black pepper')\n .replace(/\\bunsalted butter\\b/g, 'butter')\n .replace(/\\bsalted butter\\b/g, 'butter')\n .replace(/\\ball-purpose flour\\b/g, 'flour')\n .replace(/\\bplain flour\\b/g, 'flour')\n .replace(/\\s+/g, ' ')\n .trim();\n \n return cleanName || normalized;\n }\n\n const startTimeWithoutCache = Date.now();\n testIngredients.forEach(normalizeWithoutCache);\n const timeWithoutCache = Date.now() - startTimeWithoutCache;\n\n // Test with cache\n const cache = new Map();\n let cacheHits = 0;\n let cachedNormalizationCount = 0;\n\n function normalizeWithCache(name: string): string {\n if (cache.has(name)) {\n cacheHits++;\n return cache.get(name)!;\n }\n \n cachedNormalizationCount++;\n const normalized = name.toLowerCase().trim();\n \n const cleanName = normalized\n .replace(/\\b(large|medium|small|extra-large|jumbo)\\b/g, '')\n .replace(/\\b(fresh|dried|frozen|canned|chopped|diced|sliced|minced|grated|shredded)\\b/g, '')\n .replace(/\\b(whole|ground|crushed|flaked|powdered)\\b/g, '')\n .replace(/\\bparsley sprigs?\\b/g, 'parsley')\n .replace(/\\bcilantro sprigs?\\b/g, 'cilantro')\n .replace(/\\bbasil leaves?\\b/g, 'basil')\n .replace(/\\bextra-virgin olive oil\\b/g, 'olive oil')\n .replace(/\\bolive oil, extra-virgin\\b/g, 'olive oil')\n .replace(/\\bkosher salt\\b/g, 'salt')\n .replace(/\\bsea salt\\b/g, 'salt')\n .replace(/\\btable salt\\b/g, 'salt')\n .replace(/\\bflaky salt\\b/g, 'salt')\n .replace(/\\bflaky sea salt\\b/g, 'salt')\n .replace(/\\bfreshly ground black pepper\\b/g, 'black pepper')\n .replace(/\\bground black pepper\\b/g, 'black pepper')\n .replace(/\\bblack peppercorns?\\b/g, 'black pepper')\n .replace(/\\bunsalted butter\\b/g, 'butter')\n .replace(/\\bsalted butter\\b/g, 'butter')\n .replace(/\\ball-purpose flour\\b/g, 'flour')\n .replace(/\\bplain flour\\b/g, 'flour')\n .replace(/\\s+/g, ' ')\n .trim();\n \n const result = cleanName || normalized;\n cache.set(name, result);\n return result;\n }\n\n const startTimeWithCache = Date.now();\n testIngredients.forEach(normalizeWithCache);\n const timeWithCache = Date.now() - startTimeWithCache;\n\n const cacheHitRate = (cacheHits / testIngredients.length) * 100;\n const improvementPercentage = ((timeWithoutCache - timeWithCache) / timeWithoutCache * 100).toFixed(1);\n\n return {\n message: 'Ingredient normalization cache performance test completed',\n metrics: {\n withoutCache: {\n time: timeWithoutCache,\n normalizations: normalizationCount\n },\n withCache: {\n time: timeWithCache,\n normalizations: cachedNormalizationCount,\n cacheHitRate: Math.round(cacheHitRate * 100) / 100\n },\n performanceImprovement: `${improvementPercentage}% faster with cache`\n }\n };\n}\n\n// Excluded Ingredients Functions\n\nexport async function getAllExcludedIngredients(): Promise {\n const result = await sqlite.execute(`\n SELECT id, name, pattern, created_at\n FROM ${EXCLUDED_INGREDIENTS_TABLE}\n ORDER BY name\n `);\n\n return result.rows.map(row => ({\n id: row.id as number,\n name: row.name as string,\n pattern: row.pattern as string,\n createdAt: row.created_at as string\n }));\n}\n\nexport async function addExcludedIngredient(name: string, pattern: string): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${EXCLUDED_INGREDIENTS_TABLE} (name, pattern)\n VALUES (?, ?)\n `, [name.trim(), pattern.trim()]);\n\n const ingredientId = result.lastInsertRowid;\n if (!ingredientId) {\n throw new Error('Failed to create excluded ingredient');\n }\n\n const numericId = typeof ingredientId === 'string' ? parseInt(ingredientId, 10) : Number(ingredientId);\n \n // Return the created ingredient\n const created = await sqlite.execute(`\n SELECT id, name, pattern, created_at\n FROM ${EXCLUDED_INGREDIENTS_TABLE}\n WHERE id = ?\n `, [numericId]);\n\n const row = created.rows[0];\n return {\n id: row.id as number,\n name: row.name as string,\n pattern: row.pattern as string,\n createdAt: row.created_at as string\n };\n}\n\nexport async function deleteExcludedIngredient(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${EXCLUDED_INGREDIENTS_TABLE} WHERE id = ?\n `, [id]);\n \n return (result.rowsAffected || 0) > 0;\n}\n\nfunction isIngredientExcluded(ingredientName: string, excludedIngredients: ExcludedIngredient[]): boolean {\n const normalizedName = ingredientName.toLowerCase().trim();\n\n return excludedIngredients.some(excluded => {\n const pattern = excluded.pattern.toLowerCase().trim();\n return normalizedName.includes(pattern);\n });\n}\n\n// ============================================================================\n// MEAL PLAN QUERIES\n// ============================================================================\n\n// Create a new meal plan\nexport async function createMealPlan(mealPlan: Omit): Promise {\n console.log('🍽️ Creating meal plan:', mealPlan);\n\n // Validate recipe exists first using transaction\n const queries = [\n {\n sql: `SELECT id FROM ${RECIPES_TABLE} WHERE id = ?`,\n params: [mealPlan.recipeId]\n },\n {\n sql: `INSERT INTO ${MEAL_PLANS_TABLE} (recipe_id, date, meal_type, notes) VALUES (?, ?, ?, ?)`,\n params: [mealPlan.recipeId, mealPlan.date, mealPlan.mealType, mealPlan.notes || null]\n }\n ];\n\n try {\n const results = await executeTransaction(queries);\n\n // Check if recipe exists\n if (!results[0].rows.length) {\n throw new Error('Recipe not found');\n }\n\n const insertResult = results[1];\n const mealPlanId = insertResult.lastInsertRowid;\n\n // Fetch the created meal plan\n const createdMealPlan = await getMealPlanById(mealPlanId);\n if (!createdMealPlan) {\n throw new Error('Failed to fetch created meal plan');\n }\n\n console.log('✅ Meal plan created successfully:', createdMealPlan);\n return createdMealPlan;\n } catch (error) {\n console.error('❌ Failed to create meal plan:', error);\n throw error;\n }\n}\n\n// Get meal plan by ID\nexport async function getMealPlanById(id: number): Promise {\n console.log('🔍 Fetching meal plan by ID:', id);\n\n const result = await sqlite.execute(`\n SELECT\n mp.id,\n mp.recipe_id as recipeId,\n mp.date,\n mp.meal_type as mealType,\n mp.notes,\n mp.created_at as createdAt,\n mp.updated_at as updatedAt,\n r.id as recipe_id,\n r.title as recipe_title,\n r.description as recipe_description,\n r.servings as recipe_servings,\n r.prep_time as recipe_prep_time,\n r.cook_time as recipe_cook_time,\n r.difficulty as recipe_difficulty,\n r.tags as recipe_tags,\n r.source as recipe_source,\n r.image_url as recipe_image_url,\n r.steps as recipe_steps,\n r.created_at as recipe_created_at,\n r.updated_at as recipe_updated_at\n FROM ${MEAL_PLANS_TABLE} mp\n LEFT JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.id = ?\n `, [id]);\n\n if (!result.rows.length) {\n console.log('❌ No meal plan found with ID:', id);\n return null;\n }\n\n const row = result.rows[0];\n const mealPlan: MealPlan = {\n id: row.id,\n recipeId: row.recipeId,\n date: row.date,\n mealType: row.mealType,\n notes: row.notes,\n createdAt: row.createdAt,\n updatedAt: row.updatedAt,\n recipe: row.recipe_id ? {\n id: row.recipe_id,\n title: row.recipe_title,\n description: row.recipe_description,\n servings: row.recipe_servings,\n prepTime: row.recipe_prep_time,\n cookTime: row.recipe_cook_time,\n difficulty: row.recipe_difficulty,\n tags: row.recipe_tags ? JSON.parse(row.recipe_tags) : [],\n source: row.recipe_source,\n imageUrl: row.recipe_image_url,\n steps: JSON.parse(row.recipe_steps),\n ingredients: [], // Will be populated separately if needed\n createdAt: row.recipe_created_at,\n updatedAt: row.recipe_updated_at\n } : null\n };\n\n console.log('✅ Fetched meal plan:', mealPlan);\n return mealPlan;\n}\n\n// Get meal plans for a date range\nexport async function getMealPlansForDateRange(startDate: string, endDate: string): Promise {\n console.log('📅 Fetching meal plans for date range:', startDate, 'to', endDate);\n\n const result = await sqlite.execute(`\n SELECT\n mp.id,\n mp.recipe_id as recipeId,\n mp.date,\n mp.meal_type as mealType,\n mp.notes,\n mp.created_at as createdAt,\n mp.updated_at as updatedAt,\n r.id as recipe_id,\n r.title as recipe_title,\n r.description as recipe_description,\n r.servings as recipe_servings,\n r.prep_time as recipe_prep_time,\n r.cook_time as recipe_cook_time,\n r.difficulty as recipe_difficulty,\n r.tags as recipe_tags,\n r.source as recipe_source,\n r.image_url as recipe_image_url,\n r.steps as recipe_steps,\n r.created_at as recipe_created_at,\n r.updated_at as recipe_updated_at\n FROM ${MEAL_PLANS_TABLE} mp\n LEFT JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.date BETWEEN ? AND ?\n ORDER BY mp.date, mp.meal_type, mp.id\n `, [startDate, endDate]);\n\n const mealPlans: MealPlan[] = result.rows.map(row => ({\n id: row.id,\n recipeId: row.recipeId,\n date: row.date,\n mealType: row.mealType,\n notes: row.notes,\n createdAt: row.createdAt,\n updatedAt: row.updatedAt,\n recipe: row.recipe_id ? {\n id: row.recipe_id,\n title: row.recipe_title,\n description: row.recipe_description,\n servings: row.recipe_servings,\n prepTime: row.recipe_prep_time,\n cookTime: row.recipe_cook_time,\n difficulty: row.recipe_difficulty,\n tags: row.recipe_tags ? JSON.parse(row.recipe_tags) : [],\n source: row.recipe_source,\n imageUrl: row.recipe_image_url,\n steps: JSON.parse(row.recipe_steps),\n ingredients: [], // Will be populated separately if needed\n createdAt: row.recipe_created_at,\n updatedAt: row.recipe_updated_at\n } : null\n }));\n\n console.log(`✅ Fetched ${mealPlans.length} meal plans for date range`);\n return mealPlans;\n}\n\n// Update meal plan\nexport async function updateMealPlan(id: number, updates: Partial): Promise {\n console.log('📝 Updating meal plan ID:', id, 'with updates:', updates);\n\n const queries = [];\n\n // If updating recipe ID, validate it exists\n if (updates.recipeId) {\n queries.push({\n sql: `SELECT id FROM ${RECIPES_TABLE} WHERE id = ?`,\n params: [updates.recipeId]\n });\n }\n\n // Build update query\n const updateFields = [];\n const updateParams = [];\n\n if (updates.recipeId !== undefined) {\n updateFields.push('recipe_id = ?');\n updateParams.push(updates.recipeId);\n }\n if (updates.date !== undefined) {\n updateFields.push('date = ?');\n updateParams.push(updates.date);\n }\n if (updates.mealType !== undefined) {\n updateFields.push('meal_type = ?');\n updateParams.push(updates.mealType);\n }\n if (updates.notes !== undefined) {\n updateFields.push('notes = ?');\n updateParams.push(updates.notes);\n }\n\n if (updateFields.length === 0) {\n console.log('⚠️ No valid fields to update');\n return await getMealPlanById(id);\n }\n\n updateFields.push('updated_at = CURRENT_TIMESTAMP');\n updateParams.push(id);\n\n queries.push({\n sql: `UPDATE ${MEAL_PLANS_TABLE} SET ${updateFields.join(', ')} WHERE id = ?`,\n params: updateParams\n });\n\n try {\n const results = await executeTransaction(queries);\n\n // If we validated recipe ID, check it exists\n if (updates.recipeId && !results[0].rows.length) {\n throw new Error('Recipe not found');\n }\n\n const updateIndex = updates.recipeId ? 1 : 0;\n if ((results[updateIndex].rowsAffected || 0) === 0) {\n console.log('❌ No meal plan found with ID:', id);\n return null;\n }\n\n const updatedMealPlan = await getMealPlanById(id);\n console.log('✅ Meal plan updated successfully:', updatedMealPlan);\n return updatedMealPlan;\n } catch (error) {\n console.error('❌ Failed to update meal plan:', error);\n throw error;\n }\n}\n\n// Delete meal plan\nexport async function deleteMealPlan(id: number): Promise {\n console.log('🗑️ Deleting meal plan ID:', id);\n\n const result = await sqlite.execute(`\n DELETE FROM ${MEAL_PLANS_TABLE} WHERE id = ?\n `, [id]);\n\n const success = (result.rowsAffected || 0) > 0;\n console.log(success ? '✅ Meal plan deleted successfully' : '❌ No meal plan found with that ID');\n return success;\n}\n\n// Get recipe IDs from meal plans for shopping list generation\nexport async function getRecipeIdsFromMealPlans(startDate?: string, endDate?: string, dates?: string[]): Promise {\n console.log('🛒 Getting recipe IDs for shopping list from meal plans');\n\n let sql: string;\n let params: any[];\n\n if (dates && dates.length > 0) {\n // Use specific dates\n const placeholders = dates.map(() => '?').join(',');\n sql = `\n SELECT DISTINCT mp.recipe_id\n FROM ${MEAL_PLANS_TABLE} mp\n INNER JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.date IN (${placeholders})\n `;\n params = dates;\n } else if (startDate && endDate) {\n // Use date range\n sql = `\n SELECT DISTINCT mp.recipe_id\n FROM ${MEAL_PLANS_TABLE} mp\n INNER JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.date BETWEEN ? AND ?\n `;\n params = [startDate, endDate];\n } else {\n throw new Error('Either dates array or startDate/endDate must be provided');\n }\n\n const result = await sqlite.execute(sql, params);\n const recipeIds = result.rows.map(row => row.recipe_id);\n\n console.log(`✅ Found ${recipeIds.length} unique recipes for shopping list`);\n return recipeIds;\n}\n\n// Get recipe details with date information from meal plans for shopping list generation\nexport async function getRecipeDetailsFromMealPlans(startDate?: string, endDate?: string, dates?: string[]): Promise<{recipeIds: number[], recipeToDateMap: Record}> {\n console.log('🛒 Getting recipe details with dates for shopping list from meal plans');\n\n let sql: string;\n let params: any[];\n\n if (dates && dates.length > 0) {\n // Use specific dates\n const placeholders = dates.map(() => '?').join(',');\n sql = `\n SELECT mp.recipe_id, mp.date, r.title\n FROM ${MEAL_PLANS_TABLE} mp\n INNER JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.date IN (${placeholders})\n ORDER BY mp.date, r.title\n `;\n params = dates;\n } else if (startDate && endDate) {\n // Use date range\n sql = `\n SELECT mp.recipe_id, mp.date, r.title\n FROM ${MEAL_PLANS_TABLE} mp\n INNER JOIN ${RECIPES_TABLE} r ON mp.recipe_id = r.id\n WHERE mp.date BETWEEN ? AND ?\n ORDER BY mp.date, r.title\n `;\n params = [startDate, endDate];\n } else {\n throw new Error('Either dates array or startDate/endDate must be provided');\n }\n\n const result = await sqlite.execute(sql, params);\n\n // Build mapping of recipe ID to dates\n const recipeToDateMap: Record = {};\n const recipeIdsSet = new Set();\n\n for (const row of result.rows) {\n const recipeId = row.recipe_id;\n const date = row.date;\n\n recipeIdsSet.add(recipeId);\n\n if (!recipeToDateMap[recipeId]) {\n recipeToDateMap[recipeId] = [];\n }\n if (!recipeToDateMap[recipeId].includes(date)) {\n recipeToDateMap[recipeId].push(date);\n }\n }\n\n const recipeIds = Array.from(recipeIdsSet);\n\n console.log(`✅ Found ${recipeIds.length} unique recipes for shopping list with date mapping`);\n return { recipeIds, recipeToDateMap };\n}","deleted","label",[169,170,150],"backend","database","permalink","https://www.val.town/x/connnolly/cardamom/code/backend/database/queries.ts","extension","actionData","errors"]