[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_313":314,"_315":316},"routes/_app.x.$handle.$name",{"_99":98,"_31":44,"_107":89,"_26":52,"_13":14,"_108":-5,"_11":109,"_110":111,"_112":113,"_114":115,"_116":117,"_20":-5,"_118":-5,"_119":-5,"_120":24,"_121":122,"_123":24,"_124":-5,"_125":-5,"_126":127},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_42":43,"_31":44,"_37":45,"_46":-5,"_47":24,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":56,"_57":58,"_59":58,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":24},"version",17,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":14,"_40":41,"_23":24},"previous","deleted",false,"deletedHere","path","backend/services/aiService.ts","latestPath","fileId","9a02d5a8-327f-11f0-b555-569c3dd06744","name","aiService.ts","fileType","script","content","import { generateObject, LanguageModel } from \"https://esm.sh/ai@3.2.2\";\nimport { openai } from \"https://esm.sh/@ai-sdk/openai@0.0.36\";\nimport { google } from \"https://esm.sh/@ai-sdk/google@0.0.23\";\nimport {\n AIProvider,\n BillScanResponse,\n ExpiryDateScanResponse,\n ProductDetectionResponse,\n} from \"../../shared/types.ts\";\nimport { z } from \"https://esm.sh/zod@3.23.8\";\n\nexport const BILL_SCANNING_PROMPT = `\nYou are an expert inventory assistant for a retail shop. \nAnalyze this image of a supplier bill and extract the following information in JSON format:\n1. Supplier name\n2. Bill date (YYYY-MM-DD format)\n3. Bill number/reference\n4. List of products with:\n - Product name (exactly as written)\n - Quantity (numeric)\n - Unit price (numeric)\n - Total price per item (numeric)\n5. Overall total amount for the bill (numeric)\nReturn ONLY valid JSON. If a date is unclear, try your best to infer it or use a placeholder like YYYY-MM-DD.\nEnsure all monetary values are numbers, not strings.\nExample for an item: { \"productName\": \"Organic Apples\", \"quantity\": 10, \"unitPrice\": 1.5, \"totalPrice\": 15.0 }\n`;\n\nexport const EXPIRY_DATE_PROMPT = `\nYou are an expert inventory assistant for a retail shop.\nAnalyze this image of a product label and extract the following information in JSON format:\n1. Product name (as visible on the label)\n2. Expiry/Best Before date (in YYYY-MM-DD format)\n3. Batch number (if visible)\nReturn ONLY valid JSON. If you cannot determine any field with certainty, use null for that field.\nIf multiple dates are present, prioritize the one explicitly labeled as \"Expiry\", \"EXP\", \"Best Before\", \"BB\".\n`;\n\nexport const PRODUCT_DETECTION_PROMPT = `\nYou are an expert inventory assistant for a retail shop.\nAnalyze this image of multiple products on a shelf or display and list all the distinct products you can identify.\nFor each distinct product, provide:\n1. Product name\n2. Estimated quantity visible (numeric count of that specific product)\n3. Any visible expiry information (YYYY-MM-DD or null if not clearly visible or legible)\nReturn ONLY valid JSON.\nExample: { \"products\": [{ \"productName\": \"Coke Can 330ml\", \"estimatedQuantity\": 5, \"visibleExpiryDate\": \"2025-12-31\" }] }\n`;\n\nconst BillScanSchema = z.object({\n supplierName: z.string().min(1, { message: \"Supplier name cannot be empty\" }),\n billDate: z.string().regex(/^\\d{4}-\\d{2}-\\d{2}$/, { message: \"Bill date must be YYYY-MM-DD\" }),\n billNumber: z.string().min(1, { message: \"Bill number cannot be empty\" }),\n items: z.array(\n z.object({\n productName: z.string().min(1, { message: \"Product name cannot be empty\" }),\n quantity: z.number().positive({ message: \"Quantity must be positive\" }),\n unitPrice: z.number({ description: \"Unit price must be a number\" }),\n totalPrice: z.number({ description: \"Total price must be a number\" }),\n }),\n ).min(1, { message: \"Bill must contain at least one item\" }),\n totalAmount: z.number({ description: \"Total amount must be a number\" }),\n});\n\nconst ExpiryDateScanSchema = z.object({\n productName: z.string().nullable(), \n expiryDate: z.string().regex(/^\\d{4}-\\d{2}-\\d{2}$/, { message: \"Expiry date must be YYYY-MM-DD\" }).nullable(),\n batchNumber: z.string().nullable(),\n});\n\nconst ProductDetectionSchema = z.object({\n products: z.array(\n z.object({\n productName: z.string().min(1, { message: \"Product name cannot be empty\" }),\n estimatedQuantity: z.number().int().positive({ message: \"Estimated quantity must be a positive integer\" }),\n visibleExpiryDate: z.string().regex(/^\\d{4}-\\d{2}-\\d{2}$/, { message: \"Expiry date must be YYYY-MM-DD\" }).nullable(),\n }),\n ),\n});\n\nclass AIService {\n private provider: AIProvider;\n private model: LanguageModel;\n\n constructor(provider?: AIProvider) {\n const envProvider = Deno.env.get(\"AI_PROVIDER\") as AIProvider | undefined;\n this.provider = provider || envProvider || \"openai\";\n\n if (this.provider === \"openai\") {\n if (!Deno.env.get(\"OPENAI_API_KEY\")) {\n console.warn(\"OPENAI_API_KEY environment variable not set. OpenAI calls may fail.\");\n }\n this.model = openai(\"gpt-4o\") as LanguageModel; \n } else if (this.provider === \"gemini\") {\n if (!Deno.env.get(\"GOOGLE_GENERATIVE_AI_API_KEY\") && !Deno.env.get(\"GOOGLE_API_KEY\")) {\n console.warn(\"GOOGLE_GENERATIVE_AI_API_KEY or GOOGLE_API_KEY env var not set. Gemini calls may fail.\");\n }\n this.model = google(\"gemini-1.5-pro-latest\") as LanguageModel; \n } else {\n throw new Error(`Unsupported AI provider: ${this.provider}`);\n }\n }\n\n public async processImageWithSchema(\n imageBase64: string,\n promptText: string,\n schema: S, \n ): Promise> {\n try {\n console.log(`Processing with ${this.provider}, prompt: ${promptText.substring(0,50)}...`);\n \n const messagesForApi: Array<{\n role: 'user' | 'assistant' | 'system'; \n content: Array<\n | { type: 'text'; text: string } \n | { type: 'image'; image: URL }\n >;\n }> = [\n {\n role: \"user\",\n content: [\n { type: \"text\", text: promptText },\n { // This object was the source of the syntax error\n type: \"image\",\n image: new URL(`data:image/jpeg;base64,${imageBase64}`),\n }, // Corrected: Comma was inside, should be outside if more elements followed in content array.\n // Or, if it's the last element, no comma needed after its closing brace.\n ],\n },\n ];\n\n const { object } = await generateObject({\n model: this.model,\n schema: schema,\n messages: messagesForApi,\n });\n console.log(\"AI processing successful.\");\n return schema.parse(object); \n } catch (error) {\n console.error(`${this.provider} processing error:`, error);\n if (error instanceof Error) {\n throw new Error(`${this.provider} processing failed: ${error.message}`);\n }\n throw new Error(`${this.provider} processing failed with an unknown error.`);\n }\n }\n\n async scanBill(imageBase64: string): Promise {\n return this.processImageWithSchema(imageBase64, BILL_SCANNING_PROMPT, BillScanSchema);\n }\n\n async extractExpiryDate(imageBase64: string): Promise {\n return this.processImageWithSchema(imageBase64, EXPIRY_DATE_PROMPT, ExpiryDateScanSchema);\n }\n\n async detectProducts(imageBase64: string): Promise {\n return this.processImageWithSchema(imageBase64, PRODUCT_DETECTION_PROMPT, ProductDetectionSchema);\n }\n}\n\nexport const aiServiceInstance = new AIService();\n\n/*\nasync function testAIService() {\n // const imageBase64 = \"your_base64_image_string_here\"; \n // if (!imageBase64) {\n // console.log(\"Please provide a base64 image string for testing.\");\n // return;\n // }\n try {\n // console.log(\"Testing Bill Scanning...\");\n // const billData = await aiServiceInstance.scanBill(imageBase64);\n // console.log(\"Bill Scan Data:\", billData);\n } catch (e) {\n console.error(\"AI Service test failed:\", e);\n }\n}\n// testAIService();\n*/","createdAt",["D",1747418407455],"branchVersion","branchId","04edc32a-326f-11f0-81a2-569c3dd06744","id","04ed788e-326f-11f0-81a2-569c3dd06744","stockApp",["D",1747411285389],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","1591349e-125d-4fb7-930d-cf293627961f","pinnedFileId","9a1cf244-327f-11f0-b877-569c3dd06744","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","4d7791fe-1f5b-4867-8b6e-34823c16ee48","tursoName","04ed788e326f11f081a2569c3dd06744","tursoHostname","04ed788e326f11f081a2569c3dd06744-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789734636070],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_99":98,"_106":102},"org",{"_99":98,"_100":-5,"_101":102,"_18":54,"_103":104,"_105":24},"branch",{"_13":88,"_31":89,"_42":41,"_90":43,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":44,"_97":98},"isOwned",114,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","prashamtrivedi","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yaUF5OXE3VlcwNVRwRU9wSTRmcE1DZ0FSM1UifQ","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_42":43,"_31":44,"_37":308,"_46":-5,"_47":24,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":56,"_57":58,"_59":58,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":309,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":310,"_83":311,"_85":312,"_87":24},"tree",[201,202,211,212,214,215,216,217],"files",[201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217],"counts",{"_191":62,"_192":182,"_193":62,"_194":195,"_196":197},"contents",[128,129,130,131,132,133,134,135],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_42":185,"_31":186,"_29":187,"_139":-5,"_40":41,"_39":188,"_141":58,"_37":189,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_26":190},{"_42":179,"_31":180,"_29":181,"_139":-5,"_40":41,"_39":182,"_141":58,"_37":183,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_26":184},{"_42":173,"_31":174,"_29":175,"_139":-5,"_40":41,"_39":176,"_141":58,"_37":177,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_26":178},{"_42":167,"_31":168,"_29":169,"_139":-5,"_40":41,"_39":170,"_141":24,"_37":171,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_26":172},{"_42":160,"_31":161,"_29":162,"_139":-5,"_40":41,"_39":163,"_141":58,"_37":164,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_26":166},{"_42":153,"_31":154,"_29":155,"_139":-5,"_40":41,"_39":156,"_141":58,"_37":157,"_13":62,"_33":20,"_35":158,"_23":24,"_144":-5,"_26":159},{"_42":146,"_31":147,"_29":148,"_139":-5,"_40":41,"_39":149,"_141":24,"_37":150,"_13":62,"_33":20,"_35":151,"_23":24,"_144":-5,"_26":152},{"_42":136,"_31":137,"_29":138,"_139":-5,"_40":41,"_39":140,"_141":24,"_37":142,"_13":62,"_33":20,"_35":143,"_23":24,"_144":-5,"_26":145},"99cac03c-327f-11f0-b58e-569c3dd06744","deno.lock","99cac0e6-327f-11f0-b58e-569c3dd06744","parentId",12,"latest",["D",1747418406917],"{\n \"version\": \"5\",\n \"redirects\": {\n \"https://esm.sh/@types/react@~18.2.79/jsx-runtime.d.ts\": \"https://esm.sh/@types/react@18.2.79/jsx-runtime.d.ts\"\n },\n \"remote\": {\n \"https://esm.sh/react@18.2.0/denonext/jsx-runtime.mjs\": \"82f5137c9609f7b42ca13787d97b8c2dc55cfb8ce378c788791c156087a13cb3\",\n \"https://esm.sh/react@18.2.0/denonext/react.mjs\": \"a986c2876dc2f73879f395e677dc34cb751f260e69db16364810655f884df173\",\n \"https://esm.sh/react@18.2.0/jsx-runtime\": \"0bb3cca902d25a6827ac8153c8038b637e21bd20180bd58e827515d67de80582\"\n }\n}\n","label",[137],"99bcfbbe-327f-11f0-888d-569c3dd06744","deno.json","99bcfc86-327f-11f0-888d-569c3dd06744",11,["D",1747418406914],"{\n \"tasks\": {\n \"dev\": \"deno run --allow-net --allow-read --allow-env --allow-write inventoryApp.http.tsx\",\n \"serve-api\": \"deno run --allow-net --allow-read --allow-env --allow-write backend/api.http.ts\"\n },\n \"importMap\": \"import_map.json\",\n \"compilerOptions\": {\n \"jsx\": \"react-jsx\",\n \"jsxImportSource\": \"https://esm.sh/react@18.2.0\"\n },\n \"fmt\": {\n \"options\": {\n \"useTabs\": false,\n \"lineWidth\": 100,\n \"indentWidth\": 2,\n \"singleQuote\": false\n }\n },\n \"lint\": {\n \"rules\": {\n \"tags\": [\"recommended\"],\n \"exclude\": [\"no-explicit-any\"]\n }\n }\n}",[147],"99b595c2-327f-11f0-a6a5-569c3dd06744",".vtignore","99b5968a-327f-11f0-a6a5-569c3dd06744",10,["D",1747418406915],".git\n.vscode\n.cursorrules\n.DS_Store\nnode_modules\nvendor\ndocs/**\n.roo/**/**\n.mcp.json",[154],"98d32cd2-327f-11f0-8627-569c3dd06744","shared","98d32d86-327f-11f0-8627-569c3dd06744",4,["D",1747418405464],"directory",[161],"9882d3f4-327f-11f0-8cfe-569c3dd06744","frontend","9882d4a8-327f-11f0-8cfe-569c3dd06744",2,["D",1747418404936],[168],"98fd645c-327f-11f0-8e23-569c3dd06744","docs","98fd652e-327f-11f0-8e23-569c3dd06744",5,["D",1747418405726],[174],"985735dc-327f-11f0-8c28-569c3dd06744","backend","9857369a-327f-11f0-8c28-569c3dd06744",1,["D",1747418404637],[180],"98aa15c2-327f-11f0-a7ab-569c3dd06744",".roo","98aa1662-327f-11f0-a7ab-569c3dd06744",3,["D",1747418405195],[186],"pulls","branches","forks","envVars",{"_198":188,"_200":62},"connections",{"_198":62,"_199":62},"count","unfilledCount","emptyCount",{"_42":185,"_31":186,"_29":187,"_139":-5,"_40":41,"_39":188,"_141":58,"_37":305,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":306,"_26":307},{"_42":179,"_31":180,"_29":181,"_139":-5,"_40":41,"_39":182,"_141":58,"_37":302,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":303,"_26":304},{"_42":297,"_31":288,"_29":282,"_139":181,"_40":41,"_39":298,"_141":58,"_37":299,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":300,"_26":301},{"_42":289,"_31":290,"_29":291,"_139":282,"_40":41,"_39":292,"_141":24,"_37":293,"_13":62,"_33":34,"_35":294,"_23":24,"_144":-5,"_219":295,"_26":296},{"_42":279,"_31":280,"_29":281,"_139":282,"_40":41,"_39":283,"_141":24,"_37":284,"_13":62,"_33":34,"_35":285,"_23":24,"_144":-5,"_219":286,"_26":287},{"_42":274,"_31":253,"_29":247,"_139":181,"_40":41,"_39":275,"_141":58,"_37":276,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":277,"_26":278},{"_42":270,"_31":32,"_29":30,"_139":247,"_40":41,"_39":14,"_141":24,"_37":271,"_13":62,"_33":34,"_35":36,"_23":24,"_144":-5,"_219":272,"_26":273},{"_42":262,"_31":263,"_29":264,"_139":247,"_40":41,"_39":265,"_141":58,"_37":266,"_13":62,"_33":34,"_35":267,"_23":24,"_144":-5,"_219":268,"_26":269},{"_42":254,"_31":255,"_29":256,"_139":247,"_40":41,"_39":257,"_141":24,"_37":258,"_13":62,"_33":34,"_35":259,"_23":24,"_144":-5,"_219":260,"_26":261},{"_42":244,"_31":245,"_29":246,"_139":247,"_40":41,"_39":248,"_141":24,"_37":249,"_13":62,"_33":34,"_35":250,"_23":24,"_144":-5,"_219":251,"_26":252},{"_42":173,"_31":174,"_29":175,"_139":-5,"_40":41,"_39":176,"_141":58,"_37":241,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":242,"_26":243},{"_42":167,"_31":168,"_29":169,"_139":-5,"_40":41,"_39":170,"_141":24,"_37":238,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":239,"_26":240},{"_42":231,"_31":232,"_29":233,"_139":169,"_40":41,"_39":234,"_141":58,"_37":235,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":236,"_26":237},{"_42":160,"_31":161,"_29":162,"_139":-5,"_40":41,"_39":163,"_141":58,"_37":228,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_219":229,"_26":230},{"_42":153,"_31":154,"_29":155,"_139":-5,"_40":41,"_39":156,"_141":58,"_37":225,"_13":62,"_33":20,"_35":158,"_23":24,"_144":-5,"_219":226,"_26":227},{"_42":146,"_31":147,"_29":148,"_139":-5,"_40":41,"_39":149,"_141":24,"_37":222,"_13":62,"_33":20,"_35":151,"_23":24,"_144":-5,"_219":223,"_26":224},{"_42":136,"_31":137,"_29":138,"_139":-5,"_40":41,"_39":140,"_141":24,"_37":218,"_13":62,"_33":20,"_35":143,"_23":24,"_144":-5,"_219":220,"_26":221},["D",1747418406917],"children",[],[137],["D",1747418406914],[],[147],["D",1747418406915],[],[154],["D",1747418405464],[],[161],"9982fd42-327f-11f0-bda9-569c3dd06744","components","9982fe32-327f-11f0-bda9-569c3dd06744",8,["D",1747418406588],[],[168,232],["D",1747418404936],[213],[168],["D",1747418405726],[],[174],"99e4da8a-327f-11f0-888d-569c3dd06744","userService.ts","99e4db52-327f-11f0-888d-569c3dd06744","995101fc-327f-11f0-8bd6-569c3dd06744",15,["D",1747418407255],"import {\n User,\n ActionLog,\n ActionType,\n CreateUserPayload,\n} from \"../../shared/types.ts\"\nimport * as db from \"../database/queries.ts\"\n// Use crypto.randomUUID() for generating IDs, standard in Deno\n// const uuid = () => crypto.randomUUID(); // No longer needed here as db.createUser handles ID generation\n\nexport async function createUser(payload: CreateUserPayload): Promise {\n const existingUser = await db.getAllUsers() // In a real app, check for existing name if it should be unique\n if (existingUser.find(u => u.name.toLowerCase() === payload.name.toLowerCase())) {\n // For this demo, we'll allow duplicate names but log it.\n // In a production app, you might throw an error or append a number.\n console.warn(`User with name \"${payload.name}\" already exists or similar name found.`)\n }\n\n const user = await db.createUser(payload)\n await db.logAction(user.id, \"user_login\", {name: user.name}, user.name) // Log initial creation as a login\n return user\n}\n\nexport async function getUserById(id: string): Promise {\n return db.getUserById(id)\n}\n\nexport async function getAllUsers(): Promise {\n return db.getAllUsers()\n}\n\nexport async function logUserAction(\n userId: string,\n action: ActionType,\n details: any,\n): Promise {\n const user = await db.getUserById(userId)\n if (!user) {\n throw new Error(`User not found for logging action: ${userId}`)\n }\n return db.logAction(userId, action, details, user.name)\n}\n\nexport async function getActionLogsForUser(\n userId: string,\n limit: number = 20,\n): Promise {\n return db.getActionLogs({userId, limit})\n}\n\nexport async function getAllActionLogs(limit: number = 50): Promise {\n return db.getActionLogs({limit})\n}",[],[180,253,245],"services","99de4c06-327f-11f0-b555-569c3dd06744","productService.ts","99de4c9c-327f-11f0-b555-569c3dd06744",14,["D",1747418407216],"import {\n Product,\n Category,\n CreateProductPayload,\n CreateCategoryPayload,\n} from \"../../shared/types.ts\";\nimport * as db from \"../database/queries.ts\";\nimport { logUserAction } from \"./userService.ts\"; // For logging actions\n\n// --- Category Service Functions ---\nexport async function createCategory(\n payload: CreateCategoryPayload,\n userId: string,\n): Promise {\n // Check if category with the same name already exists (case-insensitive)\n const existingCategories = await db.getAllCategories();\n const duplicate = existingCategories.find(\n (cat) => cat.name.toLowerCase() === payload.name.toLowerCase(),\n );\n if (duplicate) {\n // In a real app, you might throw an error or return the existing one.\n // For this demo, we'll allow it but log a warning.\n console.warn(`Category with name \"${payload.name}\" already exists or similar name found.`);\n // Potentially, you could return the existing category here if that's the desired behavior.\n // return duplicate; \n }\n\n const category = await db.createCategory(payload, userId);\n await logUserAction(userId, \"add_category\", { categoryId: category.id, name: category.name });\n return category;\n}\n\nexport async function getCategoryById(id: string): Promise {\n return db.getCategoryById(id);\n}\n\nexport async function getAllCategories(): Promise {\n return db.getAllCategories();\n}\n\n// --- Product Service Functions ---\nexport async function createProduct(\n payload: CreateProductPayload,\n userId: string,\n): Promise {\n // Validate if categoryId exists\n const category = await db.getCategoryById(payload.categoryId);\n if (!category) {\n throw new Error(`Category with ID \"${payload.categoryId}\" not found.`);\n }\n \n // Check for existing product by name (optional, depending on requirements)\n // For this demo, we'll allow products with the same name but different brands/categories.\n\n const product = await db.createProduct(payload, userId);\n await logUserAction(userId, \"add_product\", { productId: product.id, name: product.name });\n return product;\n}\n\nexport async function getProductById(id: string): Promise {\n return db.getProductById(id);\n}\n\nexport async function getAllProducts(): Promise> {\n return db.getAllProductsWithCategory(); // Fetches products with their category names\n}\n\nexport async function updateProduct(\n productId: string,\n payload: Partial, // Use CreateProductPayload for partial updates\n userId: string,\n): Promise {\n if (payload.categoryId) {\n const category = await db.getCategoryById(payload.categoryId);\n if (!category) {\n throw new Error(`Category with ID \"${payload.categoryId}\" not found for product update.`);\n }\n }\n const updatedProduct = await db.updateProduct(productId, payload, userId);\n if (updatedProduct) {\n await logUserAction(userId, \"update_product\", { productId: updatedProduct.id, changes: payload });\n }\n return updatedProduct;\n}\n\nexport async function getProductsByCategory(categoryId: string): Promise> {\n const allProducts = await db.getAllProductsWithCategory();\n return allProducts.filter(product => product.category === categoryId); // 'category' in Product is categoryId\n}\n\nexport async function getLowStockProducts(): Promise> {\n return db.getLowStockProducts();\n}",[],[180,253,255],"99f546a4-327f-11f0-af1d-569c3dd06744","inventoryService.ts","99f5476c-327f-11f0-af1d-569c3dd06744",16,["D",1747418407365],"import {\n InventoryBatch,\n CreateInventoryBatchPayload,\n Product,\n} from \"../../shared/types.ts\";\nimport * as db from \"../database/queries.ts\";\nimport { logUserAction } from \"./userService.ts\";\nimport { getProductById } from \"./productService.ts\"; // To validate product existence\n\nexport async function createInventoryBatch(\n payload: CreateInventoryBatchPayload,\n userId: string,\n): Promise {\n // Validate product ID\n const product: Product | null = await getProductById(payload.productId);\n if (!product) {\n throw new Error(`Product with ID \"${payload.productId}\" not found. Cannot create inventory batch.`);\n }\n\n // Ensure quantityCurrent is set, defaults to quantityReceived if not provided\n const batchToCreate: CreateInventoryBatchPayload = {\n ...payload,\n quantityCurrent: payload.quantityCurrent ?? payload.quantityReceived,\n };\n\n const batch = await db.createInventoryBatch(batchToCreate, userId);\n await logUserAction(userId, \"add_inventory\", {\n batchId: batch.id,\n productId: batch.productId,\n productName: product.name, // For better log readability\n quantityReceived: batch.quantityReceived,\n expiryDate: batch.expiryDate,\n });\n return batch;\n}\n\nexport async function getInventoryBatchById(id: string): Promise {\n return db.getInventoryBatchById(id);\n}\n\nexport async function getBatchesForProduct(productId: string): Promise {\n return db.getBatchesForProduct(productId);\n}\n\nexport async function updateInventoryBatch(\n batchId: string,\n updates: Partial>,\n userId: string,\n): Promise {\n const existingBatch = await db.getInventoryBatchById(batchId);\n if (!existingBatch) {\n throw new Error(`Inventory batch with ID \"${batchId}\" not found.`);\n }\n\n // Construct the update object carefully, only allowing certain fields to be updated.\n // For example, productId, createdBy, createdAt should generally not be changed.\n // quantityReceived might also be immutable after creation.\n \n const validUpdates: Partial = {};\n if (updates.quantityCurrent !== undefined) validUpdates.quantityCurrent = updates.quantityCurrent;\n if (updates.receiptDate) validUpdates.receiptDate = updates.receiptDate;\n if (updates.expiryDate) validUpdates.expiryDate = updates.expiryDate;\n if (updates.costPrice !== undefined) validUpdates.costPrice = updates.costPrice;\n if (updates.notes !== undefined) validUpdates.notes = updates.notes;\n // isInitialInventory is typically set at creation and not updated.\n // supplierBillId might be set once if not set at creation.\n\n if (Object.keys(validUpdates).length === 0) {\n return existingBatch; // No valid fields to update\n }\n \n // The db.updateInventoryBatch function needs to be implemented in queries.ts\n // For now, assuming it takes (batchId, updates, userId)\n // This is a simplified update; a real one might involve more complex logic or a dedicated query.\n // Let's assume we have a generic update in queries.ts or we update specific fields.\n // For this demo, let's focus on quantityCurrent as the primary updatable field.\n\n let updatedBatch: InventoryBatch | null = null;\n if (updates.quantityCurrent !== undefined) {\n updatedBatch = await db.updateInventoryBatchQuantity(batchId, updates.quantityCurrent, userId);\n } else {\n // If other fields were to be updated, we'd need a more generic update query\n // For now, if only quantityCurrent is supported for direct update via a specific query:\n console.warn(\"Only quantityCurrent update is directly supported by updateInventoryBatchQuantity. Other updates ignored for now.\");\n updatedBatch = existingBatch; // Return existing if no quantity update\n }\n\n\n if (updatedBatch) {\n await logUserAction(userId, \"update_inventory\", { batchId: updatedBatch.id, changes: validUpdates });\n }\n return updatedBatch;\n}\n\n\nexport async function getExpiringBatches(days?: number): Promise {\n return db.getExpiringBatches(days);\n}\n\nexport async function getTotalProductQuantity(productId: string): Promise {\n const result = await db.getTotalProductQuantity(productId);\n return result.totalQuantity;\n}\n\n// Placeholder for inventory reconciliation logic\nexport async function reconcileInventory(\n productId: string,\n actualPhysicalCount: number,\n userId: string,\n): Promise<{ discrepancies: number; updatedBatches: InventoryBatch[] }> {\n // This is a complex operation:\n // 1. Get all current batches for the product, ordered by expiry (FIFO/FEFO).\n // 2. Compare total system quantity with actualPhysicalCount.\n // 3. Adjust batch quantities. If actual < system, reduce from oldest/nearest expiry.\n // If actual > system, it might mean unrecorded stock (needs investigation or a new \"found\" batch).\n // 4. Log changes.\n console.warn(\n `Reconciliation for product ${productId} with count ${actualPhysicalCount} by user ${userId} - NOT IMPLEMENTED`,\n );\n // For demo, return no change\n const currentBatches = await db.getBatchesForProduct(productId);\n const systemTotal = currentBatches.reduce((sum, batch) => sum + batch.quantityCurrent, 0);\n return { discrepancies: actualPhysicalCount - systemTotal, updatedBatches: [] };\n}",[],[180,253,263],"9a02d512-327f-11f0-b555-569c3dd06744",["D",1747418407455],[],[180,253,32],"99510148-327f-11f0-8bd6-569c3dd06744",7,["D",1747418406289],[207,208,209,210],[180,253],"99d7aef0-327f-11f0-94c3-569c3dd06744","queries.ts","99d7af90-327f-11f0-94c3-569c3dd06744","99258298-327f-11f0-8cfe-569c3dd06744",13,["D",1747418407133],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport {\n User,\n Category,\n Product,\n SupplierBill,\n SupplierBillItem,\n InventoryBatch,\n ActionLog,\n ActionType,\n CreateUserPayload,\n CreateCategoryPayload,\n CreateProductPayload,\n CreateInventoryBatchPayload,\n} from \"../../shared/types.ts\"; // Adjusted path\nimport {\n USERS_TABLE,\n CATEGORIES_TABLE,\n PRODUCTS_TABLE,\n SUPPLIER_BILLS_TABLE,\n BILL_ITEMS_TABLE,\n INVENTORY_BATCHES_TABLE,\n ACTION_LOGS_TABLE,\n} from \"./migrations.ts\";\n\n// Helper to get current ISO date string\nconst now = () => new Date().toISOString();\n// Use crypto.randomUUID() for generating IDs, standard in Deno\nconst uuid = () => crypto.randomUUID();\n\n// --- User Queries ---\nexport async function createUser(payload: CreateUserPayload): Promise {\n const userId = uuid();\n const createdAt = now();\n await sqlite.execute({\n sql: `INSERT INTO ${USERS_TABLE} (id, name, role, created_at) VALUES (?, ?, ?, ?);`,\n args: [userId, payload.name, payload.role, createdAt],\n });\n return { id: userId, ...payload, createdAt };\n}\n\nexport async function getUserById(id: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, name, role, created_at FROM ${USERS_TABLE} WHERE id = ?;`,\n args: [id],\n });\n if (result.rows.length === 0) return null;\n const row = result.rows[0] as Record;\n return {\n id: row.id as string,\n name: row.name as string,\n role: row.role as \"owner\" | \"staff\",\n createdAt: row.created_at as string,\n };\n}\n\nexport async function getAllUsers(): Promise {\n const result = await sqlite.execute(`SELECT id, name, role, created_at FROM ${USERS_TABLE};`);\n return result.rows.map(row => {\n const r = row as Record;\n return {\n id: r.id as string,\n name: r.name as string,\n role: r.role as \"owner\" | \"staff\",\n createdAt: r.created_at as string\n };\n });\n}\n\n// --- Category Queries ---\nexport async function createCategory(payload: CreateCategoryPayload, userId: string): Promise {\n const categoryId = uuid();\n const createdAt = now();\n await sqlite.execute({\n sql: `INSERT INTO ${CATEGORIES_TABLE} (id, name, description, created_by, created_at) VALUES (?, ?, ?, ?, ?);`,\n args: [categoryId, payload.name, payload.description, userId, createdAt],\n });\n return { id: categoryId, ...payload, createdBy: userId, createdAt };\n}\n\nexport async function getCategoryById(id: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, name, description, created_by, created_at FROM ${CATEGORIES_TABLE} WHERE id = ?;`,\n args: [id],\n });\n if (result.rows.length === 0) return null;\n const row = result.rows[0] as Record;\n return {\n id: row.id as string,\n name: row.name as string,\n description: row.description as string | undefined,\n createdBy: row.created_by as string,\n createdAt: row.created_at as string,\n };\n}\n\nexport async function getAllCategories(): Promise {\n const result = await sqlite.execute(\n `SELECT id, name, description, created_by, created_at FROM ${CATEGORIES_TABLE} ORDER BY name ASC;`,\n );\n return result.rows.map(row => {\n const r = row as Record;\n return {\n id: r.id as string,\n name: r.name as string,\n description: r.description as string | undefined,\n createdBy: r.created_by as string,\n createdAt: r.created_at as string,\n };\n });\n}\n\n// --- Product Queries ---\nexport async function createProduct(payload: CreateProductPayload, userId: string): Promise {\n const productId = uuid();\n const currentTime = now();\n const { categoryId, ...restOfPayload } = payload; // categoryId is the correct field for DB\n await sqlite.execute({\n sql:\n `INSERT INTO ${PRODUCTS_TABLE} \n (id, name, brand, default_price, category_id, typical_shelf_life, min_stock_level, image_url, created_by, created_at, updated_by, updated_at) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`,\n args: [\n productId,\n restOfPayload.name,\n restOfPayload.brand,\n restOfPayload.defaultPrice,\n categoryId, // Use categoryId here\n restOfPayload.typicalShelfLife,\n restOfPayload.minStockLevel,\n restOfPayload.imageUrl,\n userId,\n currentTime,\n userId,\n currentTime,\n ],\n });\n // The 'category' field in Product type is a string, but we store category_id.\n // For returning the Product object, we might need to fetch category name or adjust the type.\n // For now, returning as is, assuming 'category' in Product type means categoryId.\n return { \n id: productId, \n ...restOfPayload, \n category: categoryId, // This matches the Product interface if 'category' is treated as ID\n createdBy: userId, \n createdAt: currentTime, \n updatedBy: userId, \n updatedAt: currentTime \n };\n}\n\nexport async function getProductById(id: string): Promise {\n const result = await sqlite.execute({\n sql: `\n SELECT p.id, p.name, p.brand, p.default_price, p.category_id as category, \n p.typical_shelf_life, p.min_stock_level, p.image_url, \n p.created_by, p.created_at, p.updated_by, p.updated_at\n FROM ${PRODUCTS_TABLE} p\n WHERE p.id = ?;\n `,\n args: [id],\n });\n if (result.rows.length === 0) return null;\n const row = result.rows[0] as Record; // Cast to Record for easier access\n return {\n id: row.id as string,\n name: row.name as string,\n brand: row.brand as string,\n defaultPrice: row.default_price as number,\n category: row.category as string, // This is category_id\n typicalShelfLife: row.typical_shelf_life as number | undefined,\n minStockLevel: row.min_stock_level as number | undefined,\n imageUrl: row.image_url as string | undefined,\n createdBy: row.created_by as string,\n createdAt: row.created_at as string,\n updatedBy: row.updated_by as string,\n updatedAt: row.updated_at as string,\n };\n}\n\nexport async function getAllProductsWithCategory(): Promise> {\n const result = await sqlite.execute(`\n SELECT p.id, p.name, p.brand, p.default_price, p.category_id, c.name as category_name,\n p.typical_shelf_life, p.min_stock_level, p.image_url,\n p.created_by, p.created_at, p.updated_by, p.updated_at\n FROM ${PRODUCTS_TABLE} p\n JOIN ${CATEGORIES_TABLE} c ON p.category_id = c.id\n ORDER BY p.name ASC;\n `);\n return result.rows.map(row => {\n const r = row as Record;\n return {\n id: r.id as string,\n name: r.name as string,\n brand: r.brand as string,\n defaultPrice: r.default_price as number,\n category: r.category_id as string, // This is category_id\n typicalShelfLife: r.typical_shelf_life as number | undefined,\n minStockLevel: r.min_stock_level as number | undefined,\n imageUrl: r.image_url as string | undefined,\n createdBy: r.created_by as string,\n createdAt: r.created_at as string,\n updatedBy: r.updated_by as string,\n updatedAt: r.updated_at as string,\n categoryName: r.category_name as string,\n };\n });\n}\n\nexport async function updateProduct(productId: string, payload: Partial, userId: string): Promise {\n const existingProduct = await getProductById(productId);\n if (!existingProduct) return null;\n\n const fieldsToUpdate = { ...existingProduct, ...payload, category: payload.categoryId || existingProduct.category, updatedAt: now(), updatedBy: userId };\n \n await sqlite.execute({\n sql: `\n UPDATE ${PRODUCTS_TABLE}\n SET name = ?, brand = ?, default_price = ?, category_id = ?, \n typical_shelf_life = ?, min_stock_level = ?, image_url = ?, \n updated_by = ?, updated_at = ?\n WHERE id = ?;\n `,\n args: [\n fieldsToUpdate.name,\n fieldsToUpdate.brand,\n fieldsToUpdate.defaultPrice,\n fieldsToUpdate.category, // category_id\n fieldsToUpdate.typicalShelfLife,\n fieldsToUpdate.minStockLevel,\n fieldsToUpdate.imageUrl,\n userId,\n fieldsToUpdate.updatedAt,\n productId,\n ],\n });\n return getProductById(productId); // Fetch updated product\n}\n\n\n// --- Supplier Bill Queries ---\nexport async function createSupplierBill(\n imageUrl: string,\n scanDate: string,\n userId: string,\n): Promise {\n const billId = uuid();\n const createdAt = now();\n await sqlite.execute({\n sql: `\n INSERT INTO ${SUPPLIER_BILLS_TABLE} (id, image_url, scan_date, created_by, created_at, processed) \n VALUES (?, ?, ?, ?, ?, 0);`,\n args: [billId, imageUrl, scanDate, userId, createdAt],\n });\n // Return a partial bill object, more details will be filled after AI processing\n return {\n id: billId,\n imageUrl,\n scanDate,\n supplierName: \"\", // To be filled by AI\n billDate: \"\", // To be filled by AI\n billNumber: \"\", // To be filled by AI\n items: [], // To be filled by AI\n totalAmount: 0, // To be filled by AI\n processed: false,\n createdBy: userId,\n createdAt,\n };\n}\n\nexport async function getSupplierBillById(id: string): Promise {\n const billResult = await sqlite.execute({\n sql: `SELECT * FROM ${SUPPLIER_BILLS_TABLE} WHERE id = ?;`,\n args: [id],\n });\n if (billResult.rows.length === 0) return null;\n const billData = billResult.rows[0] as Record;\n\n const itemsResult = await sqlite.execute({\n sql: `SELECT product_id, product_name_on_bill, quantity, unit_price, total FROM ${BILL_ITEMS_TABLE} WHERE bill_id = ?;`,\n args: [id],\n });\n\n return {\n id: billData.id as string,\n imageUrl: billData.image_url as string,\n scanDate: billData.scan_date as string,\n supplierName: billData.supplier_name as string,\n billDate: billData.bill_date as string,\n billNumber: billData.bill_number as string,\n items: itemsResult.rows.map(item => {\n const rItem = item as Record;\n return {\n productId: rItem.product_id as string | undefined,\n productName: rItem.product_name_on_bill as string,\n quantity: rItem.quantity as number,\n unitPrice: rItem.unit_price as number,\n total: rItem.total as number,\n };\n }) as SupplierBillItem[],\n totalAmount: billData.total_amount as number,\n processed: !!billData.processed,\n createdBy: billData.created_by as string,\n createdAt: billData.created_at as string,\n };\n}\n\nexport async function updateSupplierBillWithAIData(\n billId: string,\n aiData: {\n supplierName: string;\n billDate: string;\n billNumber: string;\n items: Array<{ productName: string; quantity: number; unitPrice: number; totalPrice: number }>;\n totalAmount: number;\n },\n userId: string,\n): Promise {\n await sqlite.execute({\n sql: `\n UPDATE ${SUPPLIER_BILLS_TABLE} \n SET supplier_name = ?, bill_date = ?, bill_number = ?, total_amount = ?, processed = 0 \n WHERE id = ?;`, // Keep processed = 0 until confirmed by user\n args: [aiData.supplierName, aiData.billDate, aiData.billNumber, aiData.totalAmount, billId],\n });\n\n // Clear existing items for this bill and insert new ones from AI\n await sqlite.execute({ sql: `DELETE FROM ${BILL_ITEMS_TABLE} WHERE bill_id = ?;`, args: [billId] });\n\n for (const item of aiData.items) {\n const itemId = uuid();\n await sqlite.execute({\n sql: `\n INSERT INTO ${BILL_ITEMS_TABLE} (id, bill_id, product_name_on_bill, quantity, unit_price, total) \n VALUES (?, ?, ?, ?, ?, ?);`,\n args: [itemId, billId, item.productName, item.quantity, item.unitPrice, item.totalPrice],\n });\n }\n return getSupplierBillById(billId);\n}\n\nexport async function confirmSupplierBillProcessing(billId: string, userId: string): Promise {\n // This function would involve:\n // 1. Verifying bill items, potentially matching product_name_on_bill to existing products or creating new ones.\n // 2. Creating InventoryBatch records for each confirmed item.\n // 3. Marking the bill as processed.\n // This is a complex step and will be fleshed out in the billService.ts.\n // For now, just mark as processed.\n await sqlite.execute({\n sql: `UPDATE ${SUPPLIER_BILLS_TABLE} SET processed = 1 WHERE id = ?;`,\n args: [billId],\n });\n // Log action\n await logAction(userId, \"confirm_bill\", { billId });\n return true;\n}\n\n\n// --- Inventory Batch Queries ---\nexport async function createInventoryBatch(payload: CreateInventoryBatchPayload, userId: string): Promise {\n const batchId = uuid();\n const currentTime = now();\n await sqlite.execute({\n sql: `\n INSERT INTO ${INVENTORY_BATCHES_TABLE} \n (id, product_id, quantity_received, quantity_current, receipt_date, expiry_date, \n supplier_bill_id, cost_price, is_initial_inventory, notes, \n created_by, created_at, updated_by, updated_at) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`,\n args: [\n batchId,\n payload.productId,\n payload.quantityReceived,\n payload.quantityCurrent, // Initially same as received\n payload.receiptDate,\n payload.expiryDate,\n payload.supplierBillId,\n payload.costPrice,\n payload.isInitialInventory,\n payload.notes,\n userId,\n currentTime,\n userId,\n currentTime,\n ],\n });\n return { \n id: batchId, \n ...payload, \n createdBy: userId, \n createdAt: currentTime, \n updatedBy: userId, \n updatedAt: currentTime \n };\n}\n\nexport async function getInventoryBatchById(id: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM ${INVENTORY_BATCHES_TABLE} WHERE id = ?;`,\n args: [id],\n });\n if (result.rows.length === 0) return null;\n const row = result.rows[0] as Record;\n return {\n id: row.id as string,\n productId: row.product_id as string,\n quantityReceived: row.quantity_received as number,\n quantityCurrent: row.quantity_current as number,\n receiptDate: row.receipt_date as string,\n expiryDate: row.expiry_date as string,\n supplierBillId: row.supplier_bill_id as string | undefined,\n costPrice: row.cost_price as number | undefined,\n isInitialInventory: !!row.is_initial_inventory,\n notes: row.notes as string | undefined,\n createdBy: row.created_by as string,\n createdAt: row.created_at as string,\n updatedBy: row.updated_by as string,\n updatedAt: row.updated_at as string,\n };\n}\n\nexport async function getBatchesForProduct(productId: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM ${INVENTORY_BATCHES_TABLE} WHERE product_id = ? ORDER BY expiry_date ASC;`,\n args: [productId],\n });\n return result.rows.map(row => {\n const r = row as Record;\n return {\n id: r.id as string,\n productId: r.product_id as string,\n quantityReceived: r.quantity_received as number,\n quantityCurrent: r.quantity_current as number,\n receiptDate: r.receipt_date as string,\n expiryDate: r.expiry_date as string,\n supplierBillId: r.supplier_bill_id as string | undefined,\n costPrice: r.cost_price as number | undefined,\n isInitialInventory: !!r.is_initial_inventory,\n notes: r.notes as string | undefined,\n createdBy: r.created_by as string,\n createdAt: r.created_at as string,\n updatedBy: r.updated_by as string,\n updatedAt: r.updated_at as string,\n };\n });\n}\n\nexport async function updateInventoryBatchQuantity(batchId: string, newQuantityCurrent: number, userId: string): Promise {\n const updatedAt = now();\n await sqlite.execute({\n sql: `UPDATE ${INVENTORY_BATCHES_TABLE} SET quantity_current = ?, updated_by = ?, updated_at = ? WHERE id = ?;`,\n args: [newQuantityCurrent, userId, updatedAt, batchId],\n });\n return getInventoryBatchById(batchId);\n}\n\nexport async function getExpiringBatches(days: number = 30): Promise {\n const dateLimit = new Date();\n dateLimit.setDate(dateLimit.getDate() + days);\n const isoDateLimit = dateLimit.toISOString().split('T')[0]; // YYYY-MM-DD format\n\n const result = await sqlite.execute({\n sql: `\n SELECT ib.*, p.name as productName \n FROM ${INVENTORY_BATCHES_TABLE} ib\n JOIN ${PRODUCTS_TABLE} p ON ib.product_id = p.id\n WHERE ib.expiry_date <= ? AND ib.quantity_current > 0\n ORDER BY ib.expiry_date ASC;\n `,\n args: [isoDateLimit],\n });\n return result.rows.map(row => {\n const r = row as Record;\n return {\n id: r.id as string,\n productId: r.product_id as string,\n quantityReceived: r.quantity_received as number,\n quantityCurrent: r.quantity_current as number,\n receiptDate: r.receipt_date as string,\n expiryDate: r.expiry_date as string,\n supplierBillId: r.supplier_bill_id as string | undefined,\n costPrice: r.cost_price as number | undefined,\n isInitialInventory: !!r.is_initial_inventory,\n notes: r.notes as string | undefined,\n createdBy: r.created_by as string,\n createdAt: r.created_at as string,\n updatedBy: r.updated_by as string,\n updatedAt: r.updated_at as string,\n // productName is joined from products table\n productName: r.productName as string,\n };\n });\n}\n\nexport async function getTotalProductQuantity(productId: string): Promise<{ productId: string; totalQuantity: number }> {\n const result = await sqlite.execute({\n sql: `\n SELECT product_id, SUM(quantity_current) as total_quantity\n FROM ${INVENTORY_BATCHES_TABLE}\n WHERE product_id = ?\n GROUP BY product_id;\n `,\n args: [productId],\n });\n if (result.rows.length === 0) {\n return { productId, totalQuantity: 0 };\n }\n return { productId, totalQuantity: (result.rows[0] as any).total_quantity || 0 };\n}\n\nexport async function getLowStockProducts(): Promise> {\n const result = await sqlite.execute(`\n SELECT p.id, p.name, p.brand, p.default_price, p.category_id as category, \n p.typical_shelf_life, p.min_stock_level, p.image_url,\n p.created_by, p.created_at, p.updated_by, p.updated_at,\n COALESCE(SUM(ib.quantity_current), 0) as current_quantity\n FROM ${PRODUCTS_TABLE} p\n LEFT JOIN ${INVENTORY_BATCHES_TABLE} ib ON p.id = ib.product_id\n GROUP BY p.id\n HAVING COALESCE(SUM(ib.quantity_current), 0) < p.min_stock_level AND p.min_stock_level IS NOT NULL;\n `);\n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n brand: row.brand,\n defaultPrice: row.default_price,\n category: row.category, // category_id\n typicalShelfLife: row.typical_shelf_life,\n minStockLevel: row.min_stock_level,\n imageUrl: row.image_url,\n createdBy: row.created_by,\n createdAt: row.created_at,\n updatedBy: row.updated_by,\n updatedAt: row.updated_at,\n currentQuantity: row.current_quantity,\n })) as Array;\n}\n\n\n// --- Action Log Queries ---\nexport async function logAction(\n userId: string,\n action: ActionType,\n details: any,\n userName?: string, // Optional: fetch if not provided\n): Promise {\n const logId = uuid();\n const timestamp = now();\n let finalUserName = userName;\n\n if (!finalUserName) {\n const user = await getUserById(userId);\n finalUserName = user ? user.name : \"Unknown User\";\n }\n\n await sqlite.execute({\n sql: `INSERT INTO ${ACTION_LOGS_TABLE} (id, user_id, user_name, action, details, timestamp) VALUES (?, ?, ?, ?, ?, ?);`,\n args: [logId, userId, finalUserName, action, JSON.stringify(details), timestamp],\n });\n return { id: logId, userId, userName: finalUserName, action, details, timestamp };\n}\n\nexport async function getActionLogs(filters: {\n startDate?: string;\n endDate?: string;\n userId?: string;\n action?: ActionType;\n limit?: number;\n}): Promise {\n let query = `SELECT * FROM ${ACTION_LOGS_TABLE} WHERE 1=1`;\n const args: any[] = [];\n\n if (filters.userId) {\n query += \" AND user_id = ?\";\n args.push(filters.userId);\n }\n if (filters.action) {\n query += \" AND action = ?\";\n args.push(filters.action);\n }\n if (filters.startDate) {\n query += \" AND timestamp >= ?\";\n args.push(filters.startDate);\n }\n if (filters.endDate) {\n query += \" AND timestamp <= ?\";\n args.push(filters.endDate);\n }\n query += \" ORDER BY timestamp DESC\";\n if (filters.limit) {\n query += \" LIMIT ?\";\n args.push(filters.limit);\n }\n\n const result = await sqlite.execute({ sql: query, args });\n return (result.rows as any[]).map(row => ({\n ...row,\n details: JSON.parse(row.details),\n })) as ActionLog[];\n}",[],[180,288,280],"database","99b06822-327f-11f0-b380-569c3dd06744","migrations.ts","99b068e0-327f-11f0-b380-569c3dd06744",9,["D",1747418406914],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\"; // Standard Val Town import\n\nexport const USERS_TABLE = \"users_v1\";\nexport const CATEGORIES_TABLE = \"categories_v1\";\nexport const PRODUCTS_TABLE = \"products_v1\";\nexport const SUPPLIER_BILLS_TABLE = \"supplier_bills_v1\";\nexport const BILL_ITEMS_TABLE = \"bill_items_v1\";\nexport const INVENTORY_BATCHES_TABLE = \"inventory_batches_v1\";\nexport const ACTION_LOGS_TABLE = \"action_logs_v1\";\n\nexport async function runMigrations() {\n // Users table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${USERS_TABLE} (\n id TEXT PRIMARY KEY,\n name TEXT NOT NULL,\n role TEXT NOT NULL CHECK (role IN ('owner', 'staff')),\n created_at TEXT NOT NULL\n );\n `);\n\n // Categories table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${CATEGORIES_TABLE} (\n id TEXT PRIMARY KEY,\n name TEXT NOT NULL UNIQUE,\n description TEXT,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL,\n FOREIGN KEY (created_by) REFERENCES ${USERS_TABLE}(id)\n );\n `);\n\n // Products table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${PRODUCTS_TABLE} (\n id TEXT PRIMARY KEY,\n name TEXT NOT NULL,\n brand TEXT NOT NULL,\n default_price REAL NOT NULL,\n category_id TEXT NOT NULL,\n typical_shelf_life INTEGER,\n min_stock_level INTEGER,\n image_url TEXT,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL,\n updated_by TEXT NOT NULL,\n updated_at TEXT NOT NULL,\n FOREIGN KEY (category_id) REFERENCES ${CATEGORIES_TABLE}(id),\n FOREIGN KEY (created_by) REFERENCES ${USERS_TABLE}(id),\n FOREIGN KEY (updated_by) REFERENCES ${USERS_TABLE}(id)\n );\n `);\n\n // Supplier bills table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${SUPPLIER_BILLS_TABLE} (\n id TEXT PRIMARY KEY,\n image_url TEXT NOT NULL, -- This will be a Val Town blob key or URL\n scan_date TEXT NOT NULL,\n supplier_name TEXT,\n bill_date TEXT,\n bill_number TEXT,\n total_amount REAL,\n processed BOOLEAN NOT NULL DEFAULT 0,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL,\n FOREIGN KEY (created_by) REFERENCES ${USERS_TABLE}(id)\n );\n `);\n\n // Bill items table\n // productName is stored here as it appears on the bill, before matching to an existing Product\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${BILL_ITEMS_TABLE} (\n id TEXT PRIMARY KEY,\n bill_id TEXT NOT NULL,\n product_id TEXT, -- Can be null if product not yet in system or matched\n product_name_on_bill TEXT NOT NULL, \n quantity INTEGER NOT NULL,\n unit_price REAL NOT NULL,\n total REAL NOT NULL,\n FOREIGN KEY (bill_id) REFERENCES ${SUPPLIER_BILLS_TABLE}(id),\n FOREIGN KEY (product_id) REFERENCES ${PRODUCTS_TABLE}(id)\n );\n `);\n\n // Inventory batches table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${INVENTORY_BATCHES_TABLE} (\n id TEXT PRIMARY KEY,\n product_id TEXT NOT NULL,\n quantity_received INTEGER NOT NULL,\n quantity_current INTEGER NOT NULL,\n receipt_date TEXT NOT NULL,\n expiry_date TEXT NOT NULL,\n supplier_bill_id TEXT,\n cost_price REAL,\n is_initial_inventory BOOLEAN NOT NULL DEFAULT 0,\n notes TEXT,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL,\n updated_by TEXT NOT NULL,\n updated_at TEXT NOT NULL,\n FOREIGN KEY (product_id) REFERENCES ${PRODUCTS_TABLE}(id),\n FOREIGN KEY (supplier_bill_id) REFERENCES ${SUPPLIER_BILLS_TABLE}(id),\n FOREIGN KEY (created_by) REFERENCES ${USERS_TABLE}(id),\n FOREIGN KEY (updated_by) REFERENCES ${USERS_TABLE}(id)\n );\n `);\n\n // Action logs table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${ACTION_LOGS_TABLE} (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n user_name TEXT NOT NULL, -- Denormalized for easier querying/display\n action TEXT NOT NULL,\n details TEXT NOT NULL, -- JSON string\n timestamp TEXT NOT NULL,\n FOREIGN KEY (user_id) REFERENCES ${USERS_TABLE}(id)\n );\n `);\n\n console.log(\"Database migrations completed.\");\n}\n\n// Example of how to call this, likely in your main backend/index.ts\n// (async () => {\n// try {\n// await runMigrations();\n// } catch (e) {\n// console.error(\"Migration failed:\", e);\n// }\n// })();",[],[180,288,290],"992581c6-327f-11f0-8cfe-569c3dd06744",6,["D",1747418406004],[204,205],[180,288],["D",1747418404637],[203,206],[180],["D",1747418405195],[],[186],["D",1747411285389],["D",1789734636070],{"_99":98,"_106":102},{"_99":98,"_100":-5,"_101":102,"_18":54,"_103":104,"_105":24},{"_13":88,"_31":89,"_42":41,"_90":43,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":44,"_97":98},"ctx",{"_81":-5,"_125":346,"_347":348,"_349":350,"_351":24,"_352":24,"_353":24,"_354":58,"_355":-5,"_356":-7,"_357":-7,"_358":62,"_359":24,"_360":24,"_361":-5,"_362":24,"_363":24,"_364":24,"_365":-5},"ENV",{"_317":318,"_319":320,"_321":89,"_322":323,"_324":325,"_326":327,"_328":329,"_330":331,"_332":333,"_334":335,"_336":337,"_338":339,"_340":341,"_342":343,"_344":345},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","71c1b20804b3232a7e513be260cfffc1932a9e97","RENDER_GIT_BRANCH","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","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v17