[{"_1":2,"_174":-5,"_175":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","37715a33de29158708c83edf9ee5a306fa943e73","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,"_149":150,"_171":172,"_173":-5},"handle","hails","name","cosmoye","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":113,"_114":115,"_116":115,"_117":-5,"_118":32,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","d4c535aa-3351-11f0-88ed-9e149126039e","createdAt",["D",1747508700353],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","d399e3e0-2598-11f0-b9a4-cd6ea2802e41","pinnedFileId","a474c996-3352-11f0-9dff-9e149126039e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","a80757db-06a0-49be-a6c5-5169fe1988d5","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yd1JNSkY3alBmZW1pUkU2WWljNno4VnpBblcifQ","org",{"_93":94,"_134":131,"_12":111},"avatar","branch",{"_137":138,"_95":43,"_101":139,"_140":102,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":96,"_147":94},"version",14,"d4c57ad8-3351-11f0-88ed-9e149126039e","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":115,"_103":161,"_137":32,"_162":163,"_164":165,"_166":20,"_167":-5,"_97":168},"61271db0-3352-11f0-a474-9e149126039e","queries.ts","fileId","61271e32-3352-11f0-a474-9e149126039e","parentId","8b73ed67-445e-4abd-ba0e-801d7b7ab968","branchId","branchVersion",4,"latest",["D",1747508935873],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./migrations.ts\";\nimport { \n User, UserResponse, Product, ProductCategory, \n CartItem, Order, OrderItem, OrderStatus \n} from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(username: string, email: string, password: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.USERS} (username, email, password) VALUES (?, ?, ?)`,\n [username, email, password]\n );\n return result.lastInsertId;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.USERS} WHERE email = ?`,\n [email]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n return {\n id: result.rows[0].id,\n username: result.rows[0].username,\n email: result.rows[0].email,\n password: result.rows[0].password,\n createdAt: result.rows[0].created_at\n };\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT id, username, email, created_at FROM ${TABLES.USERS} WHERE id = ?`,\n [id]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n return {\n id: result.rows[0].id,\n username: result.rows[0].username,\n email: result.rows[0].email,\n createdAt: result.rows[0].created_at\n };\n}\n\n// Product queries\nexport async function getAllProducts(): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, c.name as category \n FROM ${TABLES.PRODUCTS} p\n LEFT JOIN ${TABLES.CATEGORIES} c ON p.category_id = c.id\n `);\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n price: row.price,\n imageUrl: row.image_url,\n category: row.category,\n stock: row.stock,\n createdAt: row.created_at\n }));\n}\n\nexport async function getProductById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, c.name as category \n FROM ${TABLES.PRODUCTS} p\n LEFT JOIN ${TABLES.CATEGORIES} c ON p.category_id = c.id\n WHERE p.id = ?\n `, [id]);\n \n if (result.rows.length === 0) {\n return null;\n }\n \n const row = result.rows[0];\n return {\n id: row.id,\n name: row.name,\n description: row.description,\n price: row.price,\n imageUrl: row.image_url,\n category: row.category,\n stock: row.stock,\n createdAt: row.created_at\n };\n}\n\nexport async function getProductCategories(): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${TABLES.CATEGORIES}`);\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name\n }));\n}\n\nexport async function getProductsByCategory(categoryId: number): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, c.name as category \n FROM ${TABLES.PRODUCTS} p\n LEFT JOIN ${TABLES.CATEGORIES} c ON p.category_id = c.id\n WHERE p.category_id = ?\n `, [categoryId]);\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n price: row.price,\n imageUrl: row.image_url,\n category: row.category,\n stock: row.stock,\n createdAt: row.created_at\n }));\n}\n\n// Cart queries\nexport async function getCartItems(userId: number): Promise {\n const result = await sqlite.execute(`\n SELECT ci.*, p.name, p.price, p.image_url, c.name as category\n FROM ${TABLES.CART_ITEMS} ci\n JOIN ${TABLES.PRODUCTS} p ON ci.product_id = p.id\n LEFT JOIN ${TABLES.CATEGORIES} c ON p.category_id = c.id\n WHERE ci.user_id = ?\n `, [userId]);\n \n return result.rows.map(row => ({\n id: row.id,\n userId: row.user_id,\n productId: row.product_id,\n quantity: row.quantity,\n product: {\n id: row.product_id,\n name: row.name,\n description: '', // Not needed in cart view\n price: row.price,\n imageUrl: row.image_url,\n category: row.category,\n stock: 0, // Not needed in cart view\n createdAt: ''\n }\n }));\n}\n\nexport async function addToCart(userId: number, productId: number, quantity: number): Promise {\n // Check if item already exists in cart\n const existingItem = await sqlite.execute(\n `SELECT * FROM ${TABLES.CART_ITEMS} WHERE user_id = ? AND product_id = ?`,\n [userId, productId]\n );\n \n if (existingItem.rows.length > 0) {\n // Update quantity if item exists\n await sqlite.execute(\n `UPDATE ${TABLES.CART_ITEMS} SET quantity = quantity + ? WHERE user_id = ? AND product_id = ?`,\n [quantity, userId, productId]\n );\n } else {\n // Add new item if it doesn't exist\n await sqlite.execute(\n `INSERT INTO ${TABLES.CART_ITEMS} (user_id, product_id, quantity) VALUES (?, ?, ?)`,\n [userId, productId, quantity]\n );\n }\n}\n\nexport async function updateCartItem(id: number, userId: number, quantity: number): Promise {\n const result = await sqlite.execute(\n `UPDATE ${TABLES.CART_ITEMS} SET quantity = ? WHERE id = ? AND user_id = ?`,\n [quantity, id, userId]\n );\n \n return result.rowsAffected > 0;\n}\n\nexport async function removeCartItem(id: number, userId: number): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${TABLES.CART_ITEMS} WHERE id = ? AND user_id = ?`,\n [id, userId]\n );\n \n return result.rowsAffected > 0;\n}\n\nexport async function clearCart(userId: number): Promise {\n await sqlite.execute(\n `DELETE FROM ${TABLES.CART_ITEMS} WHERE user_id = ?`,\n [userId]\n );\n}\n\n// Order queries\nexport async function createOrder(\n userId: number, \n totalAmount: number, \n shippingAddress: string,\n items: { productId: number; quantity: number; price: number }[]\n): Promise {\n // Start transaction\n await sqlite.execute('BEGIN TRANSACTION');\n \n try {\n // Create order\n const orderResult = await sqlite.execute(\n `INSERT INTO ${TABLES.ORDERS} (user_id, status, total_amount, shipping_address) \n VALUES (?, ?, ?, ?)`,\n [userId, OrderStatus.PENDING, totalAmount, shippingAddress]\n );\n \n const orderId = orderResult.lastInsertId;\n \n // Add order items\n for (const item of items) {\n await sqlite.execute(\n `INSERT INTO ${TABLES.ORDER_ITEMS} (order_id, product_id, quantity, price) \n VALUES (?, ?, ?, ?)`,\n [orderId, item.productId, item.quantity, item.price]\n );\n \n // Update product stock\n await sqlite.execute(\n `UPDATE ${TABLES.PRODUCTS} SET stock = stock - ? WHERE id = ?`,\n [item.quantity, item.productId]\n );\n }\n \n // Commit transaction\n await sqlite.execute('COMMIT');\n \n return orderId;\n } catch (error) {\n // Rollback transaction on error\n await sqlite.execute('ROLLBACK');\n throw error;\n }\n}\n\nexport async function getOrderById(id: number, userId: number): Promise {\n const orderResult = await sqlite.execute(\n `SELECT * FROM ${TABLES.ORDERS} WHERE id = ? AND user_id = ?`,\n [id, userId]\n );\n \n if (orderResult.rows.length === 0) {\n return null;\n }\n \n const order = orderResult.rows[0];\n \n // Get order items\n const itemsResult = await sqlite.execute(`\n SELECT oi.*, p.name, p.image_url, c.name as category\n FROM ${TABLES.ORDER_ITEMS} oi\n JOIN ${TABLES.PRODUCTS} p ON oi.product_id = p.id\n LEFT JOIN ${TABLES.CATEGORIES} c ON p.category_id = c.id\n WHERE oi.order_id = ?\n `, [id]);\n \n const items: OrderItem[] = itemsResult.rows.map(row => ({\n id: row.id,\n orderId: row.order_id,\n productId: row.product_id,\n quantity: row.quantity,\n price: row.price,\n product: {\n id: row.product_id,\n name: row.name,\n description: '', // Not needed in order view\n price: row.price,\n imageUrl: row.image_url,\n category: row.category,\n stock: 0, // Not needed in order view\n createdAt: ''\n }\n }));\n \n return {\n id: order.id,\n userId: order.user_id,\n status: order.status as OrderStatus,\n totalAmount: order.total_amount,\n createdAt: order.created_at,\n items\n };\n}\n\nexport async function getUserOrders(userId: number): Promise {\n const ordersResult = await sqlite.execute(\n `SELECT * FROM ${TABLES.ORDERS} WHERE user_id = ? ORDER BY created_at DESC`,\n [userId]\n );\n \n const orders: Order[] = [];\n \n for (const order of ordersResult.rows) {\n // Get order items\n const itemsResult = await sqlite.execute(\n `SELECT * FROM ${TABLES.ORDER_ITEMS} WHERE order_id = ?`,\n [order.id]\n );\n \n const items: OrderItem[] = itemsResult.rows.map(row => ({\n id: row.id,\n orderId: row.order_id,\n productId: row.product_id,\n quantity: row.quantity,\n price: row.price\n }));\n \n orders.push({\n id: order.id,\n userId: order.user_id,\n status: order.status as OrderStatus,\n totalAmount: order.total_amount,\n createdAt: order.created_at,\n items\n });\n }\n \n return orders;\n}\n\nexport async function updateOrderStatus(id: number, status: OrderStatus): Promise {\n const result = await sqlite.execute(\n `UPDATE ${TABLES.ORDERS} SET status = ? WHERE id = ?`,\n [status, id]\n );\n \n return result.rowsAffected > 0;\n}\n\n// Payment queries\nexport async function createPayment(\n orderId: number, \n transactionId: string, \n amount: number, \n status: string,\n paymentMethod: string\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.PAYMENTS} (order_id, transaction_id, amount, status, payment_method) \n VALUES (?, ?, ?, ?, ?)`,\n [orderId, transactionId, amount, status, paymentMethod]\n );\n \n return result.lastInsertId;\n}\n\nexport async function getPaymentByOrderId(orderId: number) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.PAYMENTS} WHERE order_id = ?`,\n [orderId]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n return result.rows[0];\n}","deleted","label",[169,170,152],"backend","database","permalink","https://www.val.town/x/hails/cosmoye/code/backend/database/queries.ts","extension","actionData","errors"]