[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_286":287,"_288":289},"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",14,"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/productService.ts","latestPath","fileId","99de4c9c-327f-11f0-b555-569c3dd06744","name","productService.ts","fileType","script","content","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}","createdAt",["D",1747418407216],"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",1789647704026],"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":281,"_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":282,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":283,"_83":284,"_85":285,"_87":24},"tree",[201,202,208,209,211,212,213,214],"files",[201,202,203,204,205,206,207,208,209,210,211,212,213,214],"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":278,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":279,"_26":280},{"_42":179,"_31":180,"_29":181,"_139":-5,"_40":41,"_39":182,"_141":58,"_37":275,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":276,"_26":277},{"_42":270,"_31":261,"_29":255,"_139":181,"_40":41,"_39":271,"_141":58,"_37":272,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":273,"_26":274},{"_42":262,"_31":263,"_29":264,"_139":255,"_40":41,"_39":265,"_141":24,"_37":266,"_13":62,"_33":34,"_35":267,"_23":24,"_144":-5,"_216":268,"_26":269},{"_42":252,"_31":253,"_29":254,"_139":255,"_40":41,"_39":256,"_141":24,"_37":257,"_13":62,"_33":34,"_35":258,"_23":24,"_144":-5,"_216":259,"_26":260},{"_42":247,"_31":246,"_29":242,"_139":181,"_40":41,"_39":248,"_141":58,"_37":249,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":250,"_26":251},{"_42":241,"_31":32,"_29":30,"_139":242,"_40":41,"_39":14,"_141":24,"_37":243,"_13":62,"_33":34,"_35":36,"_23":24,"_144":-5,"_216":244,"_26":245},{"_42":173,"_31":174,"_29":175,"_139":-5,"_40":41,"_39":176,"_141":58,"_37":238,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":239,"_26":240},{"_42":167,"_31":168,"_29":169,"_139":-5,"_40":41,"_39":170,"_141":24,"_37":235,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":236,"_26":237},{"_42":228,"_31":229,"_29":230,"_139":169,"_40":41,"_39":231,"_141":58,"_37":232,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":233,"_26":234},{"_42":160,"_31":161,"_29":162,"_139":-5,"_40":41,"_39":163,"_141":58,"_37":225,"_13":62,"_33":165,"_35":-5,"_23":24,"_144":-5,"_216":226,"_26":227},{"_42":153,"_31":154,"_29":155,"_139":-5,"_40":41,"_39":156,"_141":58,"_37":222,"_13":62,"_33":20,"_35":158,"_23":24,"_144":-5,"_216":223,"_26":224},{"_42":146,"_31":147,"_29":148,"_139":-5,"_40":41,"_39":149,"_141":24,"_37":219,"_13":62,"_33":20,"_35":151,"_23":24,"_144":-5,"_216":220,"_26":221},{"_42":136,"_31":137,"_29":138,"_139":-5,"_40":41,"_39":140,"_141":24,"_37":215,"_13":62,"_33":20,"_35":143,"_23":24,"_144":-5,"_216":217,"_26":218},["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,229],["D",1747418404936],[210],[168],["D",1747418405726],[],[174],"99de4c06-327f-11f0-b555-569c3dd06744","995101fc-327f-11f0-8bd6-569c3dd06744",["D",1747418407216],[],[180,246,32],"services","99510148-327f-11f0-8bd6-569c3dd06744",7,["D",1747418406289],[207],[180,246],"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,261,253],"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,261,263],"992581c6-327f-11f0-8cfe-569c3dd06744",6,["D",1747418406004],[204,205],[180,261],["D",1747418404637],[203,206],[180],["D",1747418405195],[],[186],["D",1747411285389],["D",1789647704026],{"_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":319,"_320":321,"_322":323,"_324":24,"_325":24,"_326":24,"_327":58,"_328":-5,"_329":-7,"_330":-7,"_331":62,"_332":24,"_333":24,"_334":-5,"_335":24,"_336":24,"_337":24,"_338":-5},"ENV",{"_290":291,"_292":293,"_294":89,"_295":296,"_297":298,"_299":300,"_301":302,"_303":304,"_305":306,"_307":308,"_309":310,"_311":312,"_313":314,"_315":316,"_317":318},"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 · v14