[{"_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","4049fd8e684531193de8d0777b9ab11a81dae27e","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","lee_roy","name","Test2","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","33e2f488-3e64-11f0-85c3-76b3cceeab13","createdAt",["D",1748726053662],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","5b132470-23fb-11f0-b5b3-1f7e5edb5a66","pinnedFileId","pinPreview","screenshotVersion","likeCount","07324b28-26f9-4230-b1e3-054974ec5b3a","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ3TFlNV3pYV1ZFZlY3Q3N0aVgwQ2p5SFFpeiJ9","org",{"_93":94,"_131":128,"_12":111},"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",15,"33e355ea-3e64-11f0-85c3-76b3cceeab13","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},"16efb84c-3e65-11f0-9a8f-76b3cceeab13","queries.ts","fileId","16efb90a-3e65-11f0-9a8f-76b3cceeab13","parentId","cbefdabb-ca33-4d2c-b36b-ecd349f55f4b","branchId","branchVersion",6,"latest",true,["D",1748726434586],"fileType","script","content","// Database query functions for IoT Solutions platform\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n User, Product, Category, Order, OrderItem, Solution, Lead, Review, BlogPost,\n ProductFilters, ProductSearchParams, PaginatedResponse, DashboardStats\n} from '../../shared/types.ts';\n\n// User queries\nexport class UserQueries {\n static async findByEmail(email: string): Promise {\n const result = await sqlite.execute(\n 'SELECT * FROM users WHERE email = ? AND is_active = 1',\n [email]\n );\n return result.length > 0 ? this.mapToUser(result[0]) : null;\n }\n\n static async findById(id: number): Promise {\n const result = await sqlite.execute(\n 'SELECT * FROM users WHERE id = ? AND is_active = 1',\n [id]\n );\n return result.length > 0 ? this.mapToUser(result[0]) : null;\n }\n\n static async create(userData: {\n email: string;\n passwordHash: string;\n firstName: string;\n lastName: string;\n role?: string;\n company?: string;\n phone?: string;\n }): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users (email, password_hash, first_name, last_name, role, company, phone) \n VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [userData.email, userData.passwordHash, userData.firstName, userData.lastName, \n userData.role || 'customer', userData.company, userData.phone]\n );\n return this.mapToUser(result[0]);\n }\n\n static async update(id: number, updates: Partial): Promise {\n const setClause = Object.keys(updates)\n .filter(key => key !== 'id' && updates[key as keyof User] !== undefined)\n .map(key => `${this.camelToSnake(key)} = ?`)\n .join(', ');\n \n if (!setClause) return this.findById(id);\n\n const values = Object.keys(updates)\n .filter(key => key !== 'id' && updates[key as keyof User] !== undefined)\n .map(key => updates[key as keyof User]);\n\n await sqlite.execute(\n `UPDATE users SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [...values, id]\n );\n \n return this.findById(id);\n }\n\n static async list(page = 1, limit = 20, role?: string): Promise> {\n const offset = (page - 1) * limit;\n let whereClause = 'WHERE is_active = 1';\n const params: any[] = [];\n\n if (role) {\n whereClause += ' AND role = ?';\n params.push(role);\n }\n\n const [items, total] = await Promise.all([\n sqlite.execute(\n `SELECT * FROM users ${whereClause} ORDER BY created_at DESC LIMIT ? OFFSET ?`,\n [...params, limit, offset]\n ),\n sqlite.execute(`SELECT COUNT(*) as count FROM users ${whereClause}`, params)\n ]);\n\n return {\n items: items.map(this.mapToUser),\n total: total[0].count,\n page,\n limit,\n totalPages: Math.ceil(total[0].count / limit)\n };\n }\n\n private static mapToUser(row: any): User {\n return {\n id: row.id,\n email: row.email,\n firstName: row.first_name,\n lastName: row.last_name,\n role: row.role,\n company: row.company,\n phone: row.phone,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n\n private static camelToSnake(str: string): string {\n return str.replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`);\n }\n}\n\n// Product queries\nexport class ProductQueries {\n static async findById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT p.*, c.name as category_name, c.slug as category_slug \n FROM products p \n LEFT JOIN categories c ON p.category_id = c.id \n WHERE p.id = ? AND p.is_active = 1`,\n [id]\n );\n return result.length > 0 ? this.mapToProduct(result[0]) : null;\n }\n\n static async findBySku(sku: string): Promise {\n const result = await sqlite.execute(\n `SELECT p.*, c.name as category_name, c.slug as category_slug \n FROM products p \n LEFT JOIN categories c ON p.category_id = c.id \n WHERE p.sku = ? AND p.is_active = 1`,\n [sku]\n );\n return result.length > 0 ? this.mapToProduct(result[0]) : null;\n }\n\n static async search(params: ProductSearchParams): Promise> {\n const { page = 1, limit = 20, sortBy = 'created', sortOrder = 'desc' } = params;\n const offset = (page - 1) * limit;\n \n let whereClause = 'WHERE p.is_active = 1';\n const queryParams: any[] = [];\n\n // Apply filters\n if (params.category) {\n whereClause += ' AND c.slug = ?';\n queryParams.push(params.category);\n }\n\n if (params.minPrice !== undefined) {\n whereClause += ' AND p.price >= ?';\n queryParams.push(params.minPrice);\n }\n\n if (params.maxPrice !== undefined) {\n whereClause += ' AND p.price <= ?';\n queryParams.push(params.maxPrice);\n }\n\n if (params.inStock) {\n whereClause += ' AND p.stock_quantity > 0';\n }\n\n if (params.featured) {\n whereClause += ' AND p.featured = 1';\n }\n\n if (params.search) {\n whereClause += ' AND (p.name LIKE ? OR p.description LIKE ? OR p.short_description LIKE ?)';\n const searchTerm = `%${params.search}%`;\n queryParams.push(searchTerm, searchTerm, searchTerm);\n }\n\n // Build ORDER BY clause\n const sortColumn = this.getSortColumn(sortBy);\n const orderClause = `ORDER BY ${sortColumn} ${sortOrder.toUpperCase()}`;\n\n const [items, total] = await Promise.all([\n sqlite.execute(\n `SELECT p.*, c.name as category_name, c.slug as category_slug \n FROM products p \n LEFT JOIN categories c ON p.category_id = c.id \n ${whereClause} ${orderClause} LIMIT ? OFFSET ?`,\n [...queryParams, limit, offset]\n ),\n sqlite.execute(\n `SELECT COUNT(*) as count FROM products p \n LEFT JOIN categories c ON p.category_id = c.id ${whereClause}`,\n queryParams\n )\n ]);\n\n return {\n items: items.map(this.mapToProduct),\n total: total[0].count,\n page,\n limit,\n totalPages: Math.ceil(total[0].count / limit)\n };\n }\n\n static async create(productData: {\n name: string;\n description?: string;\n shortDescription?: string;\n price: number;\n categoryId: number;\n sku: string;\n stockQuantity?: number;\n specifications?: any[];\n images?: string[];\n datasheetUrl?: string;\n featured?: boolean;\n }): Promise {\n const result = await sqlite.execute(\n `INSERT INTO products (name, description, short_description, price, category_id, sku, \n stock_quantity, specifications, images, datasheet_url, featured) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [\n productData.name,\n productData.description,\n productData.shortDescription,\n productData.price,\n productData.categoryId,\n productData.sku,\n productData.stockQuantity || 0,\n JSON.stringify(productData.specifications || []),\n JSON.stringify(productData.images || []),\n productData.datasheetUrl,\n productData.featured ? 1 : 0\n ]\n );\n \n return this.findById(result[0].id)!;\n }\n\n static async update(id: number, updates: Partial): Promise {\n const setClause = Object.keys(updates)\n .filter(key => key !== 'id' && updates[key as keyof Product] !== undefined)\n .map(key => `${this.camelToSnake(key)} = ?`)\n .join(', ');\n \n if (!setClause) return this.findById(id);\n\n const values = Object.keys(updates)\n .filter(key => key !== 'id' && updates[key as keyof Product] !== undefined)\n .map(key => {\n const value = updates[key as keyof Product];\n if (key === 'specifications' || key === 'images') {\n return JSON.stringify(value);\n }\n return value;\n });\n\n await sqlite.execute(\n `UPDATE products SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [...values, id]\n );\n \n return this.findById(id);\n }\n\n static async updateStock(id: number, quantity: number): Promise {\n await sqlite.execute(\n 'UPDATE products SET stock_quantity = stock_quantity + ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',\n [quantity, id]\n );\n }\n\n private static mapToProduct(row: any): Product {\n return {\n id: row.id,\n name: row.name,\n description: row.description,\n shortDescription: row.short_description,\n price: parseFloat(row.price),\n categoryId: row.category_id,\n category: row.category_name ? {\n id: row.category_id,\n name: row.category_name,\n slug: row.category_slug,\n description: '',\n isActive: true\n } : undefined,\n sku: row.sku,\n stockQuantity: row.stock_quantity,\n specifications: row.specifications ? JSON.parse(row.specifications) : [],\n images: row.images ? JSON.parse(row.images) : [],\n datasheetUrl: row.datasheet_url,\n isActive: Boolean(row.is_active),\n featured: Boolean(row.featured),\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n\n private static getSortColumn(sortBy: string): string {\n const sortMap: Record = {\n name: 'p.name',\n price: 'p.price',\n created: 'p.created_at',\n popularity: 'p.id' // Placeholder - would need sales data\n };\n return sortMap[sortBy] || 'p.created_at';\n }\n\n private static camelToSnake(str: string): string {\n return str.replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`);\n }\n}\n\n// Category queries\nexport class CategoryQueries {\n static async findAll(): Promise {\n const result = await sqlite.execute(\n 'SELECT * FROM categories WHERE is_active = 1 ORDER BY sort_order, name'\n );\n return result.map(this.mapToCategory);\n }\n\n static async findBySlug(slug: string): Promise {\n const result = await sqlite.execute(\n 'SELECT * FROM categories WHERE slug = ? AND is_active = 1',\n [slug]\n );\n return result.length > 0 ? this.mapToCategory(result[0]) : null;\n }\n\n private static mapToCategory(row: any): Category {\n return {\n id: row.id,\n name: row.name,\n description: row.description,\n slug: row.slug,\n parentId: row.parent_id,\n isActive: Boolean(row.is_active)\n };\n }\n}\n\n// Order queries\nexport class OrderQueries {\n static async findById(id: number, userId?: number): Promise {\n let whereClause = 'WHERE o.id = ?';\n const params = [id];\n \n if (userId) {\n whereClause += ' AND o.user_id = ?';\n params.push(userId);\n }\n\n const result = await sqlite.execute(\n `SELECT o.*, u.first_name, u.last_name, u.email \n FROM orders o \n LEFT JOIN users u ON o.user_id = u.id \n ${whereClause}`,\n params\n );\n \n if (result.length === 0) return null;\n\n const order = this.mapToOrder(result[0]);\n order.items = await this.getOrderItems(id);\n return order;\n }\n\n static async findByUser(userId: number, page = 1, limit = 20): Promise> {\n const offset = (page - 1) * limit;\n \n const [items, total] = await Promise.all([\n sqlite.execute(\n `SELECT o.*, u.first_name, u.last_name, u.email \n FROM orders o \n LEFT JOIN users u ON o.user_id = u.id \n WHERE o.user_id = ? ORDER BY o.created_at DESC LIMIT ? OFFSET ?`,\n [userId, limit, offset]\n ),\n sqlite.execute(\n 'SELECT COUNT(*) as count FROM orders WHERE user_id = ?',\n [userId]\n )\n ]);\n\n const orders = await Promise.all(\n items.map(async (row) => {\n const order = this.mapToOrder(row);\n order.items = await this.getOrderItems(order.id);\n return order;\n })\n );\n\n return {\n items: orders,\n total: total[0].count,\n page,\n limit,\n totalPages: Math.ceil(total[0].count / limit)\n };\n }\n\n static async create(orderData: {\n userId: number;\n orderNumber: string;\n items: Array<{ productId: number; quantity: number; unitPrice: number }>;\n subtotal: number;\n tax: number;\n shipping: number;\n totalAmount: number;\n shippingAddress: any;\n billingAddress: any;\n paymentIntentId?: string;\n }): Promise {\n // Create order\n const orderResult = await sqlite.execute(\n `INSERT INTO orders (user_id, order_number, subtotal, tax, shipping, total_amount, \n shipping_address, billing_address, payment_intent_id) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [\n orderData.userId,\n orderData.orderNumber,\n orderData.subtotal,\n orderData.tax,\n orderData.shipping,\n orderData.totalAmount,\n JSON.stringify(orderData.shippingAddress),\n JSON.stringify(orderData.billingAddress),\n orderData.paymentIntentId\n ]\n );\n\n const orderId = orderResult[0].id;\n\n // Create order items\n for (const item of orderData.items) {\n await sqlite.execute(\n `INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price) \n VALUES (?, ?, ?, ?, ?)`,\n [orderId, item.productId, item.quantity, item.unitPrice, item.quantity * item.unitPrice]\n );\n\n // Update product stock\n await ProductQueries.updateStock(item.productId, -item.quantity);\n }\n\n return this.findById(orderId)!;\n }\n\n static async updateStatus(id: number, status: string, paymentStatus?: string): Promise {\n let query = 'UPDATE orders SET status = ?, updated_at = CURRENT_TIMESTAMP';\n const params = [status];\n\n if (paymentStatus) {\n query += ', payment_status = ?';\n params.push(paymentStatus);\n }\n\n if (status === 'shipped') {\n query += ', shipped_at = CURRENT_TIMESTAMP';\n } else if (status === 'delivered') {\n query += ', delivered_at = CURRENT_TIMESTAMP';\n }\n\n query += ' WHERE id = ?';\n params.push(id);\n\n await sqlite.execute(query, params);\n }\n\n private static async getOrderItems(orderId: number): Promise {\n const result = await sqlite.execute(\n `SELECT oi.*, p.name, p.sku, p.images \n FROM order_items oi \n LEFT JOIN products p ON oi.product_id = p.id \n WHERE oi.order_id = ?`,\n [orderId]\n );\n\n return result.map(row => ({\n id: row.id,\n orderId: row.order_id,\n productId: row.product_id,\n product: {\n id: row.product_id,\n name: row.name,\n sku: row.sku,\n images: row.images ? JSON.parse(row.images) : []\n } as any,\n quantity: row.quantity,\n unitPrice: parseFloat(row.unit_price),\n totalPrice: parseFloat(row.total_price)\n }));\n }\n\n private static mapToOrder(row: any): Order {\n return {\n id: row.id,\n userId: row.user_id,\n user: row.first_name ? {\n id: row.user_id,\n firstName: row.first_name,\n lastName: row.last_name,\n email: row.email\n } as any : undefined,\n orderNumber: row.order_number,\n status: row.status,\n totalAmount: parseFloat(row.total_amount),\n subtotal: parseFloat(row.subtotal),\n tax: parseFloat(row.tax),\n shipping: parseFloat(row.shipping),\n paymentStatus: row.payment_status,\n paymentIntentId: row.payment_intent_id,\n shippingAddress: JSON.parse(row.shipping_address),\n billingAddress: JSON.parse(row.billing_address),\n items: [], // Will be populated separately\n notes: row.notes,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n}\n\n// Solution queries\nexport class SolutionQueries {\n static async findAll(featured?: boolean): Promise {\n let whereClause = 'WHERE is_active = 1';\n const params: any[] = [];\n\n if (featured !== undefined) {\n whereClause += ' AND featured = ?';\n params.push(featured ? 1 : 0);\n }\n\n const result = await sqlite.execute(\n `SELECT * FROM solutions ${whereClause} ORDER BY sort_order, title`,\n params\n );\n \n return result.map(this.mapToSolution);\n }\n\n static async findBySlug(slug: string): Promise {\n const result = await sqlite.execute(\n 'SELECT * FROM solutions WHERE slug = ? AND is_active = 1',\n [slug]\n );\n return result.length > 0 ? this.mapToSolution(result[0]) : null;\n }\n\n private static mapToSolution(row: any): Solution {\n return {\n id: row.id,\n title: row.title,\n slug: row.slug,\n description: row.description,\n shortDescription: row.short_description,\n industry: row.industry,\n benefits: row.benefits ? JSON.parse(row.benefits) : [],\n useCases: row.use_cases ? JSON.parse(row.use_cases) : [],\n features: row.features ? JSON.parse(row.features) : [],\n imageUrl: row.image_url,\n isActive: Boolean(row.is_active),\n featured: Boolean(row.featured),\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n}\n\n// Lead queries\nexport class LeadQueries {\n static async create(leadData: {\n solutionId?: number;\n firstName: string;\n lastName: string;\n email: string;\n company: string;\n phone?: string;\n industry?: string;\n projectDescription: string;\n budget?: string;\n timeline?: string;\n source?: string;\n }): Promise {\n const result = await sqlite.execute(\n `INSERT INTO leads (solution_id, first_name, last_name, email, company, phone, \n industry, project_description, budget, timeline, source) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [\n leadData.solutionId,\n leadData.firstName,\n leadData.lastName,\n leadData.email,\n leadData.company,\n leadData.phone,\n leadData.industry,\n leadData.projectDescription,\n leadData.budget,\n leadData.timeline,\n leadData.source || 'website'\n ]\n );\n \n return this.mapToLead(result[0]);\n }\n\n static async findAll(page = 1, limit = 20, status?: string): Promise> {\n const offset = (page - 1) * limit;\n let whereClause = '';\n const params: any[] = [];\n\n if (status) {\n whereClause = 'WHERE l.status = ?';\n params.push(status);\n }\n\n const [items, total] = await Promise.all([\n sqlite.execute(\n `SELECT l.*, s.title as solution_title \n FROM leads l \n LEFT JOIN solutions s ON l.solution_id = s.id \n ${whereClause} ORDER BY l.created_at DESC LIMIT ? OFFSET ?`,\n [...params, limit, offset]\n ),\n sqlite.execute(\n `SELECT COUNT(*) as count FROM leads l ${whereClause}`,\n params\n )\n ]);\n\n return {\n items: items.map(this.mapToLead),\n total: total[0].count,\n page,\n limit,\n totalPages: Math.ceil(total[0].count / limit)\n };\n }\n\n static async updateStatus(id: number, status: string, notes?: string): Promise {\n let query = 'UPDATE leads SET status = ?, updated_at = CURRENT_TIMESTAMP';\n const params = [status];\n\n if (notes) {\n query += ', notes = ?';\n params.push(notes);\n }\n\n query += ' WHERE id = ?';\n params.push(id);\n\n await sqlite.execute(query, params);\n }\n\n private static mapToLead(row: any): Lead {\n return {\n id: row.id,\n solutionId: row.solution_id,\n solution: row.solution_title ? {\n id: row.solution_id,\n title: row.solution_title\n } as any : undefined,\n firstName: row.first_name,\n lastName: row.last_name,\n email: row.email,\n company: row.company,\n phone: row.phone,\n industry: row.industry,\n projectDescription: row.project_description,\n budget: row.budget,\n timeline: row.timeline,\n status: row.status,\n source: row.source,\n notes: row.notes,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n}\n\n// Dashboard analytics queries\nexport class AnalyticsQueries {\n static async getDashboardStats(): Promise {\n const [\n revenueResult,\n ordersResult,\n customersResult,\n leadsResult,\n recentOrders,\n recentLeads,\n topProducts,\n salesChart\n ] = await Promise.all([\n sqlite.execute(\"SELECT COALESCE(SUM(total_amount), 0) as total FROM orders WHERE payment_status = 'succeeded'\"),\n sqlite.execute(\"SELECT COUNT(*) as total FROM orders\"),\n sqlite.execute(\"SELECT COUNT(*) as total FROM users WHERE role = 'customer'\"),\n sqlite.execute(\"SELECT COUNT(*) as total FROM leads\"),\n sqlite.execute(`\n SELECT o.*, u.first_name, u.last_name \n FROM orders o \n LEFT JOIN users u ON o.user_id = u.id \n ORDER BY o.created_at DESC LIMIT 5\n `),\n sqlite.execute(`\n SELECT l.*, s.title as solution_title \n FROM leads l \n LEFT JOIN solutions s ON l.solution_id = s.id \n ORDER BY l.created_at DESC LIMIT 5\n `),\n sqlite.execute(`\n SELECT p.*, SUM(oi.quantity) as total_sold, SUM(oi.total_price) as revenue\n FROM products p\n LEFT JOIN order_items oi ON p.id = oi.product_id\n LEFT JOIN orders o ON oi.order_id = o.id\n WHERE o.payment_status = 'succeeded'\n GROUP BY p.id\n ORDER BY total_sold DESC\n LIMIT 5\n `),\n sqlite.execute(`\n SELECT DATE(created_at) as date, \n SUM(total_amount) as revenue, \n COUNT(*) as orders\n FROM orders \n WHERE payment_status = 'succeeded' \n AND created_at >= date('now', '-30 days')\n GROUP BY DATE(created_at)\n ORDER BY date\n `)\n ]);\n\n return {\n totalRevenue: revenueResult[0].total,\n totalOrders: ordersResult[0].total,\n totalCustomers: customersResult[0].total,\n totalLeads: leadsResult[0].total,\n recentOrders: recentOrders.map(OrderQueries['mapToOrder']),\n recentLeads: recentLeads.map(LeadQueries['mapToLead']),\n topProducts: topProducts.map(row => ({\n product: ProductQueries['mapToProduct'](row),\n totalSold: row.total_sold || 0,\n revenue: parseFloat(row.revenue || 0)\n })),\n salesChart: salesChart.map(row => ({\n date: row.date,\n revenue: parseFloat(row.revenue),\n orders: row.orders\n }))\n };\n }\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/lee_roy/Test2/code/backend/database/queries.ts","extension","actionData","errors"]