[{"_1":2,"_172":-5,"_173":-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","ff0419d68e57d8cc3e37e5d3da688157c5dd37c6","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,"_169":170,"_171":-5},"handle","Lenny254","name","PointofSale","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":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","b55c75a0-358d-11f0-a131-9e149126039e","createdAt",["D",1747754319784],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","9e4a73e0-ed2f-11ef-af12-67b5d5b0b458","pinnedFileId","pinPreview","screenshotVersion","likeCount","3adbf8df-2c00-42d3-9fb1-3e9b778b76f3","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJ3YWdMcTZRcHViZGJId3RRQnBVbHNRVzByVyJ9","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",34,"b55ce0d0-358d-11f0-a131-9e149126039e","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":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"2356e0e0-358e-11f0-9ccd-9e149126039e","queries.ts","fileId","2356e1a8-358e-11f0-9ccd-9e149126039e","parentId","761b15d0-57fb-4886-a5b5-e7ca29f8b636","branchId","branchVersion",4,"latest",true,["D",1747754504296],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./migrations.ts\";\nimport { Product, Sale, SaleItem, User } from \"../../shared/types.ts\";\nimport * as bcrypt from \"https://deno.land/x/bcrypt@v0.4.1/mod.ts\";\n\n// Product queries\nexport async function getAllProducts(): Promise {\n return await sqlite.execute(`SELECT * FROM ${TABLES.PRODUCTS} ORDER BY name ASC`);\n}\n\nexport async function getProductById(id: number): Promise {\n const results = await sqlite.execute(\n `SELECT * FROM ${TABLES.PRODUCTS} WHERE id = ?`,\n [id]\n );\n return results.length > 0 ? results[0] : null;\n}\n\nexport async function createProduct(product: Omit): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.PRODUCTS} (name, price, stock, category, barcode, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [product.name, product.price, product.stock, product.category, product.barcode || null, now, now]\n );\n return result[0].id;\n}\n\nexport async function updateProduct(id: number, product: Partial>): Promise {\n const current = await getProductById(id);\n if (!current) return false;\n\n const updates = [];\n const values = [];\n\n for (const [key, value] of Object.entries(product)) {\n if (key !== 'id' && key !== 'created_at' && key !== 'updated_at') {\n updates.push(`${key} = ?`);\n values.push(value);\n }\n }\n\n if (updates.length === 0) return true;\n\n updates.push('updated_at = ?');\n values.push(new Date().toISOString());\n values.push(id);\n\n await sqlite.execute(\n `UPDATE ${TABLES.PRODUCTS} SET ${updates.join(', ')} WHERE id = ?`,\n values\n );\n return true;\n}\n\nexport async function deleteProduct(id: number): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${TABLES.PRODUCTS} WHERE id = ?`,\n [id]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function updateProductStock(id: number, quantityChange: number): Promise {\n const result = await sqlite.execute(\n `UPDATE ${TABLES.PRODUCTS} \n SET stock = stock + ?, updated_at = ?\n WHERE id = ? AND stock + ? >= 0`,\n [quantityChange, new Date().toISOString(), id, quantityChange]\n );\n return result.rowsAffected > 0;\n}\n\n// User queries\nexport async function getUserByUsername(username: string): Promise {\n const results = await sqlite.execute(\n `SELECT id, username, role, created_at FROM ${TABLES.USERS} WHERE username = ?`,\n [username]\n );\n return results.length > 0 ? results[0] : null;\n}\n\nexport async function verifyUser(username: string, password: string): Promise {\n const results = await sqlite.execute(\n `SELECT id, username, password_hash, role, created_at FROM ${TABLES.USERS} WHERE username = ?`,\n [username]\n );\n \n if (results.length === 0) return null;\n \n const user = results[0];\n const passwordMatch = await bcrypt.compare(password, user.password_hash);\n \n if (!passwordMatch) return null;\n \n // Don't return the password hash\n delete user.password_hash;\n return user;\n}\n\nexport async function createUser(username: string, password: string, role: 'admin' | 'cashier'): Promise {\n const passwordHash = await bcrypt.hash(password);\n const now = new Date().toISOString();\n \n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.USERS} (username, password_hash, role, created_at)\n VALUES (?, ?, ?, ?)\n RETURNING id`,\n [username, passwordHash, role, now]\n );\n \n return result[0].id;\n}\n\n// Sales queries\nexport async function createSale(\n userId: number, \n items: Array<{ productId: number; quantity: number; }>,\n paymentMethod: 'cash' | 'card' | 'other'\n): Promise {\n // Start a transaction\n await sqlite.execute('BEGIN TRANSACTION');\n \n try {\n let totalAmount = 0;\n const now = new Date().toISOString();\n \n // Calculate total and verify stock\n for (const item of items) {\n const product = await getProductById(item.productId);\n if (!product) throw new Error(`Product with ID ${item.productId} not found`);\n if (product.stock < item.quantity) throw new Error(`Insufficient stock for product: ${product.name}`);\n \n totalAmount += product.price * item.quantity;\n }\n \n // Create the sale record\n const saleResult = await sqlite.execute(\n `INSERT INTO ${TABLES.SALES} (user_id, total_amount, payment_method, created_at)\n VALUES (?, ?, ?, ?)\n RETURNING id`,\n [userId, totalAmount, paymentMethod, now]\n );\n \n const saleId = saleResult[0].id;\n \n // Add sale items and update stock\n for (const item of items) {\n const product = await getProductById(item.productId);\n if (!product) throw new Error(`Product with ID ${item.productId} not found`);\n \n // Add sale item\n await sqlite.execute(\n `INSERT INTO ${TABLES.SALE_ITEMS} (sale_id, product_id, quantity, price_per_unit, total_price)\n VALUES (?, ?, ?, ?, ?)`,\n [saleId, item.productId, item.quantity, product.price, product.price * item.quantity]\n );\n \n // Update product stock\n await updateProductStock(item.productId, -item.quantity);\n }\n \n // Commit the transaction\n await sqlite.execute('COMMIT');\n return saleId;\n } catch (error) {\n // Rollback on error\n await sqlite.execute('ROLLBACK');\n throw error;\n }\n}\n\nexport async function getSaleById(id: number): Promise {\n const saleResults = await sqlite.execute(\n `SELECT s.id, s.user_id, u.username, s.total_amount, s.payment_method, s.created_at\n FROM ${TABLES.SALES} s\n JOIN ${TABLES.USERS} u ON s.user_id = u.id\n WHERE s.id = ?`,\n [id]\n );\n \n if (saleResults.length === 0) return null;\n \n const sale = saleResults[0] as Sale;\n \n // Get sale items\n const itemResults = await sqlite.execute(\n `SELECT si.id, si.sale_id, si.product_id, p.name as product_name, \n si.quantity, si.price_per_unit, si.total_price\n FROM ${TABLES.SALE_ITEMS} si\n JOIN ${TABLES.PRODUCTS} p ON si.product_id = p.id\n WHERE si.sale_id = ?`,\n [id]\n );\n \n sale.items = itemResults as SaleItem[];\n return sale;\n}\n\nexport async function getRecentSales(limit: number = 20): Promise {\n const saleResults = await sqlite.execute(\n `SELECT s.id, s.user_id, u.username, s.total_amount, s.payment_method, s.created_at\n FROM ${TABLES.SALES} s\n JOIN ${TABLES.USERS} u ON s.user_id = u.id\n ORDER BY s.created_at DESC\n LIMIT ?`,\n [limit]\n );\n \n const sales = saleResults as Sale[];\n \n // Get items for each sale\n for (const sale of sales) {\n const itemResults = await sqlite.execute(\n `SELECT si.id, si.sale_id, si.product_id, p.name as product_name, \n si.quantity, si.price_per_unit, si.total_price\n FROM ${TABLES.SALE_ITEMS} si\n JOIN ${TABLES.PRODUCTS} p ON si.product_id = p.id\n WHERE si.sale_id = ?`,\n [sale.id]\n );\n \n sale.items = itemResults as SaleItem[];\n }\n \n return sales;\n}\n\n// Dashboard queries\nexport async function getDashboardStats() {\n // Get total sales count\n const totalSalesResult = await sqlite.execute(\n `SELECT COUNT(*) as count FROM ${TABLES.SALES}`\n );\n const totalSales = totalSalesResult[0].count;\n \n // Get total revenue\n const totalRevenueResult = await sqlite.execute(\n `SELECT SUM(total_amount) as total FROM ${TABLES.SALES}`\n );\n const totalRevenue = totalRevenueResult[0].total || 0;\n \n // Get low stock items count\n const lowStockResult = await sqlite.execute(\n `SELECT COUNT(*) as count FROM ${TABLES.PRODUCTS} WHERE stock < 5`\n );\n const lowStockItems = lowStockResult[0].count;\n \n // Get top selling products\n const topProductsResult = await sqlite.execute(\n `SELECT p.name, SUM(si.quantity) as quantity\n FROM ${TABLES.SALE_ITEMS} si\n JOIN ${TABLES.PRODUCTS} p ON si.product_id = p.id\n GROUP BY si.product_id\n ORDER BY quantity DESC\n LIMIT 5`\n );\n \n return {\n totalSales,\n totalRevenue,\n lowStockItems,\n topProducts: topProductsResult\n };\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/Lenny254/PointofSale/code/backend/database/queries.ts","extension","actionData","errors"]