{\n const result = await sqlite.execute(`\n SELECT * FROM ${TABLES.TABLES} WHERE qr_code = ? AND active = 1\n `, [qrCode]);\n \n if (result.length === 0) return null;\n \n const row = result[0];\n return {\n id: row.id,\n number: row.number,\n name: row.name,\n qr_code: row.qr_code,\n active: Boolean(row.active)\n };\n}\n\nexport async function createTable(table: CreateTableRequest): Promise

{\n const qrCode = `canteen-table-${table.number}-${Date.now()}`;\n \n const result = await sqlite.execute(`\n INSERT INTO ${TABLES.TABLES} (number, name, qr_code)\n VALUES (?, ?, ?)\n `, [table.number, table.name, qrCode]);\n \n const newTable = await getTableById(result.lastInsertRowid);\n if (!newTable) throw new Error('Failed to create table');\n return newTable;\n}\n\n// Order Queries\nexport async function getAllOrders(): Promise {\n const result = await sqlite.execute(`\n SELECT o.*, t.number as table_number, t.name as table_name\n FROM ${TABLES.ORDERS} o\n JOIN ${TABLES.TABLES} t ON o.table_id = t.id\n ORDER BY o.created_at DESC\n `);\n \n const orders: Order[] = [];\n \n for (const row of result) {\n const orderItems = await getOrderItems(row.id);\n orders.push({\n id: row.id,\n table_id: row.table_id,\n table: {\n id: row.table_id,\n number: row.table_number,\n name: row.table_name,\n qr_code: '',\n active: true\n },\n customer_name: row.customer_name,\n customer_phone: row.customer_phone,\n items: orderItems,\n total: row.total,\n status: row.status as OrderStatus,\n created_at: row.created_at\n });\n }\n \n return orders;\n}\n\nexport async function getOrderById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT o.*, t.number as table_number, t.name as table_name\n FROM ${TABLES.ORDERS} o\n JOIN ${TABLES.TABLES} t ON o.table_id = t.id\n WHERE o.id = ?\n `, [id]);\n \n if (result.length === 0) return null;\n \n const row = result[0];\n const orderItems = await getOrderItems(row.id);\n \n return {\n id: row.id,\n table_id: row.table_id,\n table: {\n id: row.table_id,\n number: row.table_number,\n name: row.table_name,\n qr_code: '',\n active: true\n },\n customer_name: row.customer_name,\n customer_phone: row.customer_phone,\n items: orderItems,\n total: row.total,\n status: row.status as OrderStatus,\n created_at: row.created_at\n };\n}\n\nasync function getOrderItems(orderId: number): Promise {\n const result = await sqlite.execute(`\n SELECT oi.*, mi.name, mi.description, mi.category\n FROM ${TABLES.ORDER_ITEMS} oi\n JOIN ${TABLES.MENU_ITEMS} mi ON oi.menu_item_id = mi.id\n WHERE oi.order_id = ?\n `, [orderId]);\n \n return result.map(row => ({\n id: row.id,\n menu_item_id: row.menu_item_id,\n menu_item: {\n id: row.menu_item_id,\n name: row.name,\n description: row.description || '',\n price: row.price,\n category: row.category,\n available: true\n },\n quantity: row.quantity,\n price: row.price\n }));\n}\n\nexport async function createOrder(orderData: CreateOrderRequest): Promise {\n // Calculate total\n let total = 0;\n const menuItems = [];\n \n for (const item of orderData.items) {\n const menuItem = await getMenuItemById(item.menu_item_id);\n if (!menuItem) throw new Error(`Menu item ${item.menu_item_id} not found`);\n if (!menuItem.available) throw new Error(`Menu item ${menuItem.name} is not available`);\n \n menuItems.push({ menuItem, quantity: item.quantity });\n total += menuItem.price * item.quantity;\n }\n \n // Create order\n const orderResult = await sqlite.execute(`\n INSERT INTO ${TABLES.ORDERS} (table_id, customer_name, customer_phone, total)\n VALUES (?, ?, ?, ?)\n `, [orderData.table_id, orderData.customer_name, orderData.customer_phone, total]);\n \n const orderId = orderResult.lastInsertRowid;\n \n // Create order items\n for (const { menuItem, quantity } of menuItems) {\n await sqlite.execute(`\n INSERT INTO ${TABLES.ORDER_ITEMS} (order_id, menu_item_id, quantity, price)\n VALUES (?, ?, ?, ?)\n `, [orderId, menuItem.id, quantity, menuItem.price]);\n }\n \n const newOrder = await getOrderById(orderId);\n if (!newOrder) throw new Error('Failed to create order');\n return newOrder;\n}\n\nexport async function updateOrderStatus(id: number, status: OrderStatus): Promise {\n await sqlite.execute(`\n UPDATE ${TABLES.ORDERS} \n SET status = ?, updated_at = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [status, id]);\n \n return getOrderById(id);\n}\n\nexport async function getOrdersByStatus(status: OrderStatus): Promise {\n const result = await sqlite.execute(`\n SELECT o.*, t.number as table_number, t.name as table_name\n FROM ${TABLES.ORDERS} o\n JOIN ${TABLES.TABLES} t ON o.table_id = t.id\n WHERE o.status = ?\n ORDER BY o.created_at ASC\n `, [status]);\n \n const orders: Order[] = [];\n \n for (const row of result) {\n const orderItems = await getOrderItems(row.id);\n orders.push({\n id: row.id,\n table_id: row.table_id,\n table: {\n id: row.table_id,\n number: row.table_number,\n name: row.table_name,\n qr_code: '',\n active: true\n },\n customer_name: row.customer_name,\n customer_phone: row.customer_phone,\n items: orderItems,\n total: row.total,\n status: row.status as OrderStatus,\n created_at: row.created_at\n });\n }\n \n return orders;\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/Pratham211/canteen/code/backend/database/queries.ts","extension","actionData","errors"]