[{"_1":2,"_173":-5,"_174":-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","73d011c55f6ff6e543547a2f801bf2081197a5a4","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_146":147,"_170":171,"_172":-5},"handle","prashamtrivedi","name","stockApp","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":72,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","04ed788e-326f-11f0-81a2-569c3dd06744","createdAt",["D",1747411285389],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","1591349e-125d-4fb7-930d-cf293627961f","pinnedFileId","pinPreview","screenshotVersion","likeCount","4d7791fe-1f5b-4867-8b6e-34823c16ee48","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yaUF5OXE3VlcwNVRwRU9wSTRmcE1DZ0FSM1UifQ","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",114,"04edc32a-326f-11f0-81a2-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":160,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"3fcdccb4-3324-11f0-9df8-9e149126039e","queries.ts","fileId","99d7af90-327f-11f0-94c3-569c3dd06744","parentId","99258298-327f-11f0-8cfe-569c3dd06744","branchId","branchVersion",103,"latest",true,["D",1747489123071],1,"fileType","script","content","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\nexport async function getCategoryByName(name: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, name, description, created_by, created_at FROM ${CATEGORIES_TABLE} WHERE name = ?;`,\n args: [name], // Assuming name is stored with consistent casing or UNIQUE constraint handles it\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\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}","deleted","label",[168,169,149],"backend","database","permalink","https://www.val.town/x/prashamtrivedi/stockApp/code/backend/database/queries.ts","extension","actionData","errors"]