[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","5aae8c594f4ceb0bfd0f0c6fa62a207a75f96ff1","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_150":151,"_172":173,"_174":-5},"handle","helton_munee","name","helton","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":116,"_118":-5,"_119":32,"_14":120,"_121":122,"_123":122,"_124":125,"_126":125,"_127":125,"_128":122,"_129":122,"_23":130,"_133":134,"_136":137,"_149":20},"id","acbd330c-3fac-11f0-8cb6-76b3cceeab13","createdAt",["D",1748867131346],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","f5249150-3faa-11f0-ad5b-814c56c8abb0","pinnedFileId","1f481b94-3fad-11f0-8ae5-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","b1a28a9d-f16e-4231-a770-736a79866509","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4eDRNbmFaOFN2QkVGdEVkd2tsWFZjZFgxZyJ9","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":97,"_148":95},"version",18,"acbd9b26-3fac-11f0-8cb6-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":152,"_96":153,"_154":155,"_156":157,"_158":140,"_159":160,"_161":116,"_104":162,"_138":32,"_163":164,"_165":166,"_167":20,"_168":-5,"_98":169},"01e68da6-3fad-11f0-9a64-76b3cceeab13","queries.ts","fileId","01e68e96-3fad-11f0-9a64-76b3cceeab13","parentId","ece71dce-0f00-4cdc-8075-8d920d771814","branchId","branchVersion",4,"latest",["D",1748867274219],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n Product, \n Supplier, \n Category, \n StockMovement, \n StockMovementWithProduct,\n ProductWithDetails,\n DashboardData,\n CreateProductRequest,\n CreateMovementRequest,\n CreateSupplierRequest,\n CreateCategoryRequest\n} from \"../../shared/types.ts\";\n\n// Product queries\nexport async function getAllProducts(): Promise {\n const result = await sqlite.execute(`\n SELECT \n p.*,\n c.name as category_name,\n s.name as supplier_name,\n (p.current_stock * p.unit_price) as total_value,\n CASE WHEN p.current_stock <= p.min_stock_level THEN 1 ELSE 0 END as is_low_stock\n FROM products p\n LEFT JOIN categories c ON p.category_id = c.id\n LEFT JOIN suppliers s ON p.supplier_id = s.id\n ORDER BY p.name\n `);\n return result.rows as ProductWithDetails[];\n}\n\nexport async function getProductById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM products WHERE id = ?`, [id]);\n return result.rows[0] as Product || null;\n}\n\nexport async function createProduct(product: CreateProductRequest): Promise {\n const result = await sqlite.execute(`\n INSERT INTO products (name, sku, description, category_id, supplier_id, unit_price, current_stock, min_stock_level, max_stock_level, unit_of_measure)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n product.name,\n product.sku,\n product.description || null,\n product.category_id || null,\n product.supplier_id || null,\n product.unit_price,\n product.current_stock,\n product.min_stock_level,\n product.max_stock_level,\n product.unit_of_measure\n ]);\n return result.lastInsertRowId as number;\n}\n\nexport async function updateProduct(id: number, product: Partial): Promise {\n const fields = [];\n const values = [];\n \n for (const [key, value] of Object.entries(product)) {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n }\n \n if (fields.length === 0) return;\n \n fields.push('updated_at = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE products SET ${fields.join(', ')} WHERE id = ?\n `, values);\n}\n\nexport async function deleteProduct(id: number): Promise {\n await sqlite.execute(`DELETE FROM products WHERE id = ?`, [id]);\n}\n\nexport async function updateProductStock(productId: number, newStock: number): Promise {\n await sqlite.execute(`\n UPDATE products SET current_stock = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?\n `, [newStock, productId]);\n}\n\n// Stock movement queries\nexport async function getAllMovements(): Promise {\n const result = await sqlite.execute(`\n SELECT \n sm.*,\n p.name as product_name,\n p.sku as product_sku,\n s.name as supplier_name\n FROM stock_movements sm\n JOIN products p ON sm.product_id = p.id\n LEFT JOIN suppliers s ON sm.supplier_id = s.id\n ORDER BY sm.created_at DESC\n `);\n return result.rows as StockMovementWithProduct[];\n}\n\nexport async function getMovementsByProduct(productId: number): Promise {\n const result = await sqlite.execute(`\n SELECT \n sm.*,\n p.name as product_name,\n p.sku as product_sku,\n s.name as supplier_name\n FROM stock_movements sm\n JOIN products p ON sm.product_id = p.id\n LEFT JOIN suppliers s ON sm.supplier_id = s.id\n WHERE sm.product_id = ?\n ORDER BY sm.created_at DESC\n `, [productId]);\n return result.rows as StockMovementWithProduct[];\n}\n\nexport async function createMovement(movement: CreateMovementRequest): Promise {\n // Start transaction-like operation\n const product = await getProductById(movement.product_id);\n if (!product) {\n throw new Error('Product not found');\n }\n\n // Calculate new stock level\n let newStock = product.current_stock;\n if (movement.movement_type === 'IN') {\n newStock += movement.quantity;\n } else if (movement.movement_type === 'OUT') {\n newStock -= movement.quantity;\n if (newStock < 0) {\n throw new Error('Insufficient stock');\n }\n } else if (movement.movement_type === 'ADJUSTMENT') {\n newStock = movement.quantity; // For adjustments, quantity is the new total\n }\n\n // Insert movement record\n const result = await sqlite.execute(`\n INSERT INTO stock_movements (product_id, movement_type, quantity, unit_price, reference_number, notes, supplier_id)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n movement.product_id,\n movement.movement_type,\n movement.quantity,\n movement.unit_price || null,\n movement.reference_number || null,\n movement.notes || null,\n movement.supplier_id || null\n ]);\n\n // Update product stock\n await updateProductStock(movement.product_id, newStock);\n\n return result.lastInsertRowId as number;\n}\n\n// Supplier queries\nexport async function getAllSuppliers(): Promise {\n const result = await sqlite.execute(`SELECT * FROM suppliers ORDER BY name`);\n return result.rows as Supplier[];\n}\n\nexport async function createSupplier(supplier: CreateSupplierRequest): Promise {\n const result = await sqlite.execute(`\n INSERT INTO suppliers (name, contact_person, email, phone, address)\n VALUES (?, ?, ?, ?, ?)\n `, [\n supplier.name,\n supplier.contact_person || null,\n supplier.email || null,\n supplier.phone || null,\n supplier.address || null\n ]);\n return result.lastInsertRowId as number;\n}\n\n// Category queries\nexport async function getAllCategories(): Promise {\n const result = await sqlite.execute(`SELECT * FROM categories ORDER BY name`);\n return result.rows as Category[];\n}\n\nexport async function createCategory(category: CreateCategoryRequest): Promise {\n const result = await sqlite.execute(`\n INSERT INTO categories (name, description) VALUES (?, ?)\n `, [category.name, category.description || null]);\n return result.lastInsertRowId as number;\n}\n\n// Dashboard queries\nexport async function getDashboardData(): Promise {\n // Get total products\n const totalProductsResult = await sqlite.execute(`SELECT COUNT(*) as count FROM products`);\n const total_products = totalProductsResult.rows[0].count as number;\n\n // Get total inventory value\n const totalValueResult = await sqlite.execute(`\n SELECT SUM(current_stock * unit_price) as total_value FROM products\n `);\n const total_value = totalValueResult.rows[0].total_value as number || 0;\n\n // Get low stock count\n const lowStockResult = await sqlite.execute(`\n SELECT COUNT(*) as count FROM products WHERE current_stock <= min_stock_level\n `);\n const low_stock_count = lowStockResult.rows[0].count as number;\n\n // Get recent movements\n const recentMovementsResult = await sqlite.execute(`\n SELECT \n sm.*,\n p.name as product_name,\n p.sku as product_sku,\n s.name as supplier_name\n FROM stock_movements sm\n JOIN products p ON sm.product_id = p.id\n LEFT JOIN suppliers s ON sm.supplier_id = s.id\n ORDER BY sm.created_at DESC\n LIMIT 10\n `);\n const recent_movements = recentMovementsResult.rows as StockMovementWithProduct[];\n\n // Get top products by value\n const topProductsResult = await sqlite.execute(`\n SELECT \n p.*,\n c.name as category_name,\n s.name as supplier_name,\n (p.current_stock * p.unit_price) as total_value,\n CASE WHEN p.current_stock <= p.min_stock_level THEN 1 ELSE 0 END as is_low_stock\n FROM products p\n LEFT JOIN categories c ON p.category_id = c.id\n LEFT JOIN suppliers s ON p.supplier_id = s.id\n ORDER BY (p.current_stock * p.unit_price) DESC\n LIMIT 5\n `);\n const top_products = topProductsResult.rows as ProductWithDetails[];\n\n return {\n total_products,\n total_value,\n low_stock_count,\n recent_movements,\n top_products\n };\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/helton_munee/helton/code/backend/database/queries.ts","extension","actionData","errors"]