[{"_1":2,"_176":-5,"_177":-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","8abab1f3bacc65052d6313ca736f41d897b49791","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,"_173":174,"_175":-5},"handle","eddie_walk","name","crm_OBUO_FARMS","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","755ce5fe-3fc7-11f0-a0be-76b3cceeab13","createdAt",["D",1748878634852],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","438ac540-3faf-11f0-b6e9-ab0b2f88b858","pinnedFileId","aa24c482-3fc7-11f0-b39e-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","8de6586a-f8f9-4d30-9bd2-782698026a59","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHg4QWVmMkZjWUdaNm1Jc0ZiVGs4UzBabnMifQ","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",185,"755d5430-3fc7-11f0-a0be-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":163,"_164":165,"_166":167,"_168":20,"_169":-5,"_98":170},"07f26374-4169-11f0-be97-76b3cceeab13","queries.ts","fileId","90f672d0-3fc7-11f0-9f4b-76b3cceeab13","parentId","539dd682-e6c3-4d92-a7e0-121392032568","branchId","branchVersion",162,"latest",["D",1749057980909],27,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLE_NAME, USERS_TABLE_NAME, PRODUCTS_TABLE_NAME, STOCK_MOVEMENTS_TABLE_NAME, EXPENSES_TABLE_NAME, REVENUE_TABLE_NAME, hashPassword } from \"./migrations.ts\";\nimport type { \n Order, CreateOrderRequest, User, CreateUserRequest, UpdateUserRequest,\n Product, CreateProductRequest, UpdateProductRequest, StockMovement, StockAdjustmentRequest,\n Expense, CreateExpenseRequest, Revenue, FinancialSummary, SalesReport\n} from \"../../shared/types.ts\";\n\n// Order queries\nexport async function createOrder(orderData: CreateOrderRequest): Promise {\n // First, find the matching product\n const product = await getProductByOrderData(orderData);\n if (!product) {\n throw new Error(`Product not found for: ${orderData.orderType}`);\n }\n \n if (product.currentStock < orderData.quantity) {\n throw new Error(`Insufficient stock. Available: ${product.currentStock} ${product.unit}, Requested: ${orderData.quantity} ${product.unit}`);\n }\n\n const result = await sqlite.execute(`\n INSERT INTO ${TABLE_NAME} \n (clientName, phone, email, productCategory, fishType, productForm, poultryType, eggType, feedType, orderType, quantity, deliveryDate, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n orderData.clientName,\n orderData.phone,\n orderData.email,\n orderData.productCategory,\n orderData.fishType || null,\n orderData.productForm || null,\n orderData.poultryType || null,\n orderData.eggType || null,\n orderData.feedType || null,\n orderData.orderType,\n orderData.quantity,\n orderData.deliveryDate,\n orderData.notes || null\n ]);\n\n // Try to get the inserted ID from different possible locations\n let insertedId = result.lastInsertRowId || result.meta?.lastInsertRowId || result.changes;\n \n if (!insertedId) {\n // Fallback: get the most recent order for this client\n const fallbackResult = await sqlite.execute(`\n SELECT * FROM ${TABLE_NAME} \n WHERE clientName = ? AND email = ? \n ORDER BY createdAt DESC \n LIMIT 1\n `, [orderData.clientName, orderData.email]);\n \n let order = null;\n if (fallbackResult.rows && fallbackResult.rows.length > 0) {\n order = fallbackResult.rows[0];\n } else if (Array.isArray(fallbackResult) && fallbackResult.length > 0) {\n order = fallbackResult[0];\n } else if (fallbackResult[0]) {\n order = fallbackResult[0];\n }\n \n if (!order) {\n console.error('Could not retrieve created order using fallback method');\n throw new Error('Failed to retrieve created order');\n }\n \n // Update stock for the order\n await updateProductStock(product.id!, -orderData.quantity, 'OUT', `Order #${order.id}`, order.id);\n \n return order as Order;\n }\n\n // Get the created order by ID\n const createdOrder = await sqlite.execute(`\n SELECT * FROM ${TABLE_NAME} WHERE id = ?\n `, [insertedId]);\n\n // Handle different result structures\n let order = null;\n if (createdOrder.rows && createdOrder.rows.length > 0) {\n order = createdOrder.rows[0];\n } else if (Array.isArray(createdOrder) && createdOrder.length > 0) {\n order = createdOrder[0];\n } else if (createdOrder[0]) {\n order = createdOrder[0];\n }\n \n if (!order) {\n console.error('Could not retrieve created order. Result structure:', createdOrder);\n throw new Error('Failed to retrieve created order');\n }\n\n // Update stock for the order\n await updateProductStock(product.id!, -orderData.quantity, 'OUT', `Order #${insertedId}`, insertedId as number);\n\n return order as Order;\n}\n\n// Helper function to find product by order data\nasync function getProductByOrderData(orderData: CreateOrderRequest): Promise {\n let whereClause = 'WHERE productCategory = ? AND isActive = 1';\n const params = [orderData.productCategory];\n \n if (orderData.productCategory === 'Fish') {\n whereClause += ' AND fishType = ? AND productForm = ?';\n params.push(orderData.fishType!, orderData.productForm!);\n } else if (orderData.productCategory === 'Poultry') {\n if (orderData.poultryType) {\n whereClause += ' AND poultryType = ?';\n params.push(orderData.poultryType);\n } else if (orderData.eggType) {\n whereClause += ' AND eggType = ?';\n params.push(orderData.eggType);\n }\n } else if (orderData.productCategory === 'Feed') {\n whereClause += ' AND feedType = ?';\n params.push(orderData.feedType!);\n }\n \n const result = await sqlite.execute(`\n SELECT * FROM ${PRODUCTS_TABLE_NAME} ${whereClause}\n `, params);\n \n let product = null;\n if (result.rows && result.rows.length > 0) {\n product = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n product = result[0];\n } else if (result[0]) {\n product = result[0];\n }\n \n return product as Product || null;\n}\n\nexport async function getAllOrders(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${TABLE_NAME} \n ORDER BY createdAt DESC\n `);\n \n // Handle different result structures\n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as Order[];\n } else if (Array.isArray(result)) {\n return result as Order[];\n } else {\n return [];\n }\n}\n\nexport async function getOrderById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${TABLE_NAME} WHERE id = ?\n `, [id]);\n \n // Handle different result structures\n let order = null;\n if (result.rows && result.rows.length > 0) {\n order = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n order = result[0];\n } else if (result[0]) {\n order = result[0];\n }\n \n return order as Order || null;\n}\n\nexport async function updateOrder(id: number, orderData: Partial): Promise {\n const fields = [];\n const values = [];\n \n Object.entries(orderData).forEach(([key, value]) => {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n });\n \n if (fields.length === 0) {\n return getOrderById(id);\n }\n \n fields.push('updatedAt = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE ${TABLE_NAME} \n SET ${fields.join(', ')}\n WHERE id = ?\n `, values);\n \n return getOrderById(id);\n}\n\nexport async function deleteOrder(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${TABLE_NAME} WHERE id = ?\n `, [id]);\n \n return result.changes > 0;\n}\n\n// User queries\nexport async function createUser(userData: CreateUserRequest): Promise {\n const passwordHash = await hashPassword(userData.password);\n \n const result = await sqlite.execute(`\n INSERT INTO ${USERS_TABLE_NAME} \n (username, email, passwordHash, role)\n VALUES (?, ?, ?, ?)\n `, [\n userData.username,\n userData.email,\n passwordHash,\n userData.role\n ]);\n\n // Get the created user by username (more reliable than using lastInsertRowId)\n const createdUser = await sqlite.execute(`\n SELECT id, username, email, role, isActive, createdAt, updatedAt, lastLoginAt \n FROM ${USERS_TABLE_NAME} WHERE username = ?\n `, [userData.username]);\n\n // Try different ways to access the result\n let user = null;\n if (createdUser.rows && createdUser.rows.length > 0) {\n user = createdUser.rows[0];\n } else if (Array.isArray(createdUser) && createdUser.length > 0) {\n user = createdUser[0];\n } else if (createdUser[0]) {\n user = createdUser[0];\n }\n \n if (!user) {\n console.error('Could not retrieve created user. Result structure:', createdUser);\n throw new Error('Failed to retrieve created user');\n }\n \n return user as User;\n}\n\nexport async function getAllUsers(): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, role, isActive, createdAt, updatedAt, lastLoginAt \n FROM ${USERS_TABLE_NAME} \n ORDER BY createdAt DESC\n `);\n \n // Handle different result structures\n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as User[];\n } else if (Array.isArray(result)) {\n return result as User[];\n } else {\n return [];\n }\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, role, isActive, createdAt, updatedAt, lastLoginAt \n FROM ${USERS_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n // Handle different result structures\n let user = null;\n if (result.rows && result.rows.length > 0) {\n user = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n user = result[0];\n } else if (result[0]) {\n user = result[0];\n }\n \n return user as User || null;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(`\n SELECT id, username, email, role, isActive, createdAt, updatedAt, lastLoginAt \n FROM ${USERS_TABLE_NAME} WHERE username = ?\n `, [username]);\n \n return (result.rows?.[0] || result[0]) as User || null;\n}\n\nexport async function validateUserCredentials(username: string, password: string): Promise {\n const passwordHash = await hashPassword(password);\n \n const result = await sqlite.execute(`\n SELECT id, username, email, role, isActive, createdAt, updatedAt, lastLoginAt \n FROM ${USERS_TABLE_NAME} \n WHERE username = ? AND passwordHash = ? AND isActive = 1\n `, [username, passwordHash]);\n \n const rows = result.rows || result;\n if (rows.length > 0) {\n // Update last login time\n await sqlite.execute(`\n UPDATE ${USERS_TABLE_NAME} \n SET lastLoginAt = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [rows[0].id]);\n \n return rows[0] as User;\n }\n \n return null;\n}\n\nexport async function updateUser(id: number, userData: UpdateUserRequest): Promise {\n const fields = [];\n const values = [];\n \n for (const [key, value] of Object.entries(userData)) {\n if (value !== undefined) {\n if (key === 'password') {\n // Hash the password\n fields.push('passwordHash = ?');\n values.push(await hashPassword(value as string));\n } else {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n }\n }\n \n if (fields.length === 0) {\n return getUserById(id);\n }\n \n fields.push('updatedAt = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE ${USERS_TABLE_NAME} \n SET ${fields.join(', ')}\n WHERE id = ?\n `, values);\n \n return getUserById(id);\n}\n\nexport async function deleteUser(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${USERS_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n return result.changes > 0;\n}\n\n// Product queries\nexport async function getAllProducts(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${PRODUCTS_TABLE_NAME} \n WHERE isActive = 1\n ORDER BY fishType, productForm\n `);\n \n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as Product[];\n } else if (Array.isArray(result)) {\n return result as Product[];\n } else {\n return [];\n }\n}\n\nexport async function getProductById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${PRODUCTS_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n let product = null;\n if (result.rows && result.rows.length > 0) {\n product = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n product = result[0];\n } else if (result[0]) {\n product = result[0];\n }\n \n return product as Product || null;\n}\n\nexport async function getProductByTypeAndForm(fishType: string, productForm: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${PRODUCTS_TABLE_NAME} \n WHERE fishType = ? AND productForm = ? AND isActive = 1\n `, [fishType, productForm]);\n \n let product = null;\n if (result.rows && result.rows.length > 0) {\n product = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n product = result[0];\n } else if (result[0]) {\n product = result[0];\n }\n \n return product as Product || null;\n}\n\nexport async function createProduct(productData: CreateProductRequest): Promise {\n let name = '';\n \n if (productData.productCategory === 'Fish') {\n name = `${productData.fishType} - ${productData.productForm}`;\n } else if (productData.productCategory === 'Poultry') {\n name = productData.poultryType || productData.eggType || 'Unknown Poultry Product';\n } else if (productData.productCategory === 'Feed') {\n name = productData.feedType || 'Unknown Feed Product';\n }\n \n const result = await sqlite.execute(`\n INSERT INTO ${PRODUCTS_TABLE_NAME} \n (productCategory, fishType, productForm, poultryType, eggType, feedType, name, currentStock, minStockLevel, maxStockLevel, unit, pricePerUnit)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n productData.productCategory,\n productData.fishType || null,\n productData.productForm || null,\n productData.poultryType || null,\n productData.eggType || null,\n productData.feedType || null,\n name,\n productData.currentStock,\n productData.minStockLevel,\n productData.maxStockLevel,\n productData.unit,\n productData.pricePerUnit || null\n ]);\n\n let insertedId = result.lastInsertRowId || result.meta?.lastInsertRowId;\n \n if (!insertedId) {\n throw new Error('Failed to create product');\n }\n\n const createdProduct = await getProductById(insertedId as number);\n if (!createdProduct) {\n throw new Error('Failed to retrieve created product');\n }\n\n // Record initial stock movement\n if (productData.currentStock > 0) {\n await recordStockMovement({\n productId: createdProduct.id!,\n movementType: 'IN',\n quantity: productData.currentStock,\n reason: 'Initial stock',\n previousStock: 0,\n newStock: productData.currentStock\n });\n }\n\n return createdProduct;\n}\n\nexport async function updateProduct(id: number, productData: UpdateProductRequest): Promise {\n const fields = [];\n const values = [];\n \n Object.entries(productData).forEach(([key, value]) => {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n });\n \n if (fields.length === 0) {\n return getProductById(id);\n }\n \n fields.push('updatedAt = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE ${PRODUCTS_TABLE_NAME} \n SET ${fields.join(', ')}\n WHERE id = ?\n `, values);\n \n return getProductById(id);\n}\n\nexport async function updateProductStock(\n productId: number, \n quantityChange: number, \n movementType: 'IN' | 'OUT' | 'ADJUSTMENT',\n reason: string,\n orderId?: number,\n userId?: number\n): Promise {\n const product = await getProductById(productId);\n if (!product) {\n throw new Error('Product not found');\n }\n\n const previousStock = product.currentStock;\n const newStock = previousStock + quantityChange;\n\n if (newStock < 0) {\n throw new Error('Insufficient stock for this operation');\n }\n\n // Update product stock\n await sqlite.execute(`\n UPDATE ${PRODUCTS_TABLE_NAME} \n SET currentStock = ?, updatedAt = CURRENT_TIMESTAMP\n WHERE id = ?\n `, [newStock, productId]);\n\n // Record stock movement\n await recordStockMovement({\n productId,\n movementType,\n quantity: Math.abs(quantityChange),\n reason,\n orderId,\n userId,\n previousStock,\n newStock\n });\n\n return getProductById(productId);\n}\n\nexport async function getLowStockProducts(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${PRODUCTS_TABLE_NAME} \n WHERE currentStock <= minStockLevel AND isActive = 1\n ORDER BY (currentStock - minStockLevel) ASC\n `);\n \n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as Product[];\n } else if (Array.isArray(result)) {\n return result as Product[];\n } else {\n return [];\n }\n}\n\n// Stock movement queries\nexport async function recordStockMovement(movementData: {\n productId: number;\n movementType: 'IN' | 'OUT' | 'ADJUSTMENT';\n quantity: number;\n reason: string;\n orderId?: number;\n userId?: number;\n previousStock: number;\n newStock: number;\n}): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${STOCK_MOVEMENTS_TABLE_NAME} \n (productId, movementType, quantity, reason, orderId, userId, previousStock, newStock)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n movementData.productId,\n movementData.movementType,\n movementData.quantity,\n movementData.reason,\n movementData.orderId || null,\n movementData.userId || null,\n movementData.previousStock,\n movementData.newStock\n ]);\n\n let insertedId = result.lastInsertRowId || result.meta?.lastInsertRowId;\n \n if (!insertedId) {\n // If we can't get the ID, just return a mock movement object\n return {\n id: Date.now(), // Use timestamp as fallback ID\n productId: movementData.productId,\n movementType: movementData.movementType,\n quantity: movementData.quantity,\n reason: movementData.reason,\n orderId: movementData.orderId,\n userId: movementData.userId,\n previousStock: movementData.previousStock,\n newStock: movementData.newStock,\n createdAt: new Date().toISOString()\n };\n }\n\n const movement = await getStockMovementById(insertedId as number);\n if (!movement) {\n // Fallback if we can't retrieve the movement\n return {\n id: insertedId as number,\n productId: movementData.productId,\n movementType: movementData.movementType,\n quantity: movementData.quantity,\n reason: movementData.reason,\n orderId: movementData.orderId,\n userId: movementData.userId,\n previousStock: movementData.previousStock,\n newStock: movementData.newStock,\n createdAt: new Date().toISOString()\n };\n }\n\n return movement;\n}\n\nexport async function getStockMovementById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${STOCK_MOVEMENTS_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n let movement = null;\n if (result.rows && result.rows.length > 0) {\n movement = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n movement = result[0];\n } else if (result[0]) {\n movement = result[0];\n }\n \n return movement as StockMovement || null;\n}\n\nexport async function getStockMovements(productId?: number, limit: number = 100): Promise {\n let query = `\n SELECT sm.*, p.name as productName \n FROM ${STOCK_MOVEMENTS_TABLE_NAME} sm\n JOIN ${PRODUCTS_TABLE_NAME} p ON sm.productId = p.id\n `;\n const params = [];\n \n if (productId) {\n query += ' WHERE sm.productId = ?';\n params.push(productId);\n }\n \n query += ' ORDER BY sm.createdAt DESC LIMIT ?';\n params.push(limit);\n\n const result = await sqlite.execute(query, params);\n \n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as StockMovement[];\n } else if (Array.isArray(result)) {\n return result as StockMovement[];\n } else {\n return [];\n }\n}\n\nexport async function adjustStock(adjustmentData: StockAdjustmentRequest, userId?: number): Promise {\n const product = await getProductById(adjustmentData.productId);\n if (!product) {\n throw new Error('Product not found');\n }\n\n let quantityChange = adjustmentData.quantity;\n if (adjustmentData.movementType === 'OUT') {\n quantityChange = -quantityChange;\n }\n\n const updatedProduct = await updateProductStock(\n adjustmentData.productId,\n quantityChange,\n adjustmentData.movementType,\n adjustmentData.reason,\n undefined,\n userId\n );\n\n if (!updatedProduct) {\n throw new Error('Failed to adjust stock');\n }\n\n return updatedProduct;\n}\n\n// Financial Management Functions\n\n// Expense functions\nexport async function createExpense(expenseData: CreateExpenseRequest): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${EXPENSES_TABLE_NAME} \n (category, description, amount, date, paymentMethod, receipt, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n expenseData.category,\n expenseData.description,\n expenseData.amount,\n expenseData.date,\n expenseData.paymentMethod || null,\n expenseData.receipt || null,\n expenseData.notes || null\n ]);\n\n let insertedId = result.lastInsertRowId || result.meta?.lastInsertRowId;\n \n if (!insertedId) {\n throw new Error('Failed to create expense');\n }\n\n const createdExpense = await getExpenseById(insertedId as number);\n if (!createdExpense) {\n throw new Error('Failed to retrieve created expense');\n }\n\n return createdExpense;\n}\n\nexport async function getAllExpenses(startDate?: string, endDate?: string, category?: string): Promise {\n let query = `SELECT * FROM ${EXPENSES_TABLE_NAME} WHERE 1=1`;\n const params = [];\n \n if (startDate) {\n query += ' AND date >= ?';\n params.push(startDate);\n }\n \n if (endDate) {\n query += ' AND date <= ?';\n params.push(endDate);\n }\n \n if (category) {\n query += ' AND category = ?';\n params.push(category);\n }\n \n query += ' ORDER BY date DESC, createdAt DESC';\n\n const result = await sqlite.execute(query, params);\n \n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as Expense[];\n } else if (Array.isArray(result)) {\n return result as Expense[];\n } else {\n return [];\n }\n}\n\nexport async function getExpenseById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${EXPENSES_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n let expense = null;\n if (result.rows && result.rows.length > 0) {\n expense = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n expense = result[0];\n } else if (result[0]) {\n expense = result[0];\n }\n \n return expense as Expense || null;\n}\n\nexport async function updateExpense(id: number, expenseData: Partial): Promise {\n const fields = [];\n const values = [];\n \n Object.entries(expenseData).forEach(([key, value]) => {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n });\n \n if (fields.length === 0) {\n return getExpenseById(id);\n }\n \n fields.push('updatedAt = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE ${EXPENSES_TABLE_NAME} \n SET ${fields.join(', ')}\n WHERE id = ?\n `, values);\n \n return getExpenseById(id);\n}\n\nexport async function deleteExpense(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${EXPENSES_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n return result.changes > 0;\n}\n\n// Revenue functions\nexport async function createRevenue(revenueData: any): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${REVENUE_TABLE_NAME} \n (orderId, source, description, amount, date, paymentMethod, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n revenueData.orderId || null,\n revenueData.source,\n revenueData.description,\n revenueData.amount,\n revenueData.date,\n revenueData.paymentMethod || null,\n revenueData.notes || null\n ]);\n\n let insertedId = result.lastInsertRowId || result.meta?.lastInsertRowId;\n \n if (!insertedId) {\n throw new Error('Failed to create revenue');\n }\n\n const createdRevenue = await getRevenueById(insertedId as number);\n if (!createdRevenue) {\n throw new Error('Failed to retrieve created revenue');\n }\n\n return createdRevenue;\n}\n\nexport async function getAllRevenue(startDate?: string, endDate?: string, source?: string): Promise {\n let query = `SELECT * FROM ${REVENUE_TABLE_NAME} WHERE 1=1`;\n const params = [];\n \n if (startDate) {\n query += ' AND date >= ?';\n params.push(startDate);\n }\n \n if (endDate) {\n query += ' AND date <= ?';\n params.push(endDate);\n }\n \n if (source) {\n query += ' AND source = ?';\n params.push(source);\n }\n \n query += ' ORDER BY date DESC, createdAt DESC';\n\n const result = await sqlite.execute(query, params);\n \n if (result.rows && Array.isArray(result.rows)) {\n return result.rows as Revenue[];\n } else if (Array.isArray(result)) {\n return result as Revenue[];\n } else {\n return [];\n }\n}\n\nexport async function getRevenueById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${REVENUE_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n let revenue = null;\n if (result.rows && result.rows.length > 0) {\n revenue = result.rows[0];\n } else if (Array.isArray(result) && result.length > 0) {\n revenue = result[0];\n } else if (result[0]) {\n revenue = result[0];\n }\n \n return revenue as Revenue || null;\n}\n\nexport async function updateRevenue(id: number, revenueData: any): Promise {\n const fields = [];\n const values = [];\n \n Object.entries(revenueData).forEach(([key, value]) => {\n if (value !== undefined) {\n fields.push(`${key} = ?`);\n values.push(value);\n }\n });\n \n if (fields.length === 0) {\n return getRevenueById(id);\n }\n \n fields.push('updatedAt = CURRENT_TIMESTAMP');\n values.push(id);\n \n await sqlite.execute(`\n UPDATE ${REVENUE_TABLE_NAME} \n SET ${fields.join(', ')}\n WHERE id = ?\n `, values);\n \n return getRevenueById(id);\n}\n\nexport async function deleteRevenue(id: number): Promise {\n const result = await sqlite.execute(`\n DELETE FROM ${REVENUE_TABLE_NAME} WHERE id = ?\n `, [id]);\n \n return result.changes > 0;\n}\n\n// Financial reporting functions\nexport async function getFinancialSummary(period: string, startDate?: string, endDate?: string): Promise {\n // Calculate date range if not provided\n if (!startDate || !endDate) {\n const now = new Date();\n if (period === 'month') {\n startDate = new Date(now.getFullYear(), now.getMonth(), 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0).toISOString().split('T')[0];\n } else if (period === 'quarter') {\n const quarter = Math.floor(now.getMonth() / 3);\n startDate = new Date(now.getFullYear(), quarter * 3, 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), (quarter + 1) * 3, 0).toISOString().split('T')[0];\n } else if (period === 'year') {\n startDate = new Date(now.getFullYear(), 0, 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), 11, 31).toISOString().split('T')[0];\n }\n }\n\n // Get total revenue\n const revenueResult = await sqlite.execute(`\n SELECT COALESCE(SUM(amount), 0) as totalRevenue FROM ${REVENUE_TABLE_NAME}\n WHERE date >= ? AND date <= ?\n `, [startDate, endDate]);\n \n const totalRevenue = (revenueResult.rows?.[0] || revenueResult[0])?.totalRevenue || 0;\n\n // Get total expenses\n const expenseResult = await sqlite.execute(`\n SELECT COALESCE(SUM(amount), 0) as totalExpenses FROM ${EXPENSES_TABLE_NAME}\n WHERE date >= ? AND date <= ?\n `, [startDate, endDate]);\n \n const totalExpenses = (expenseResult.rows?.[0] || expenseResult[0])?.totalExpenses || 0;\n\n // Get order statistics\n const orderResult = await sqlite.execute(`\n SELECT \n COUNT(*) as totalOrders,\n COALESCE(AVG(totalAmount), 0) as avgOrderValue\n FROM ${TABLE_NAME}\n WHERE createdAt >= ? AND createdAt <= ? AND status != 'cancelled'\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n \n const orderStats = orderResult.rows?.[0] || orderResult[0] || { totalOrders: 0, avgOrderValue: 0 };\n\n // Get top selling products\n const topProductsResult = await sqlite.execute(`\n SELECT \n o.orderType as productName,\n SUM(o.quantity) as quantitySold,\n COALESCE(SUM(o.totalAmount), 0) as revenue\n FROM ${TABLE_NAME} o\n WHERE o.createdAt >= ? AND o.createdAt <= ? AND o.status != 'cancelled'\n GROUP BY o.orderType\n ORDER BY revenue DESC\n LIMIT 5\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n\n const topSellingProducts = (topProductsResult.rows || topProductsResult || []).map((row: any) => ({\n productName: row.productName,\n quantitySold: row.quantitySold,\n revenue: row.revenue\n }));\n\n // Get expense breakdown\n const expenseBreakdownResult = await sqlite.execute(`\n SELECT \n category,\n SUM(amount) as amount\n FROM ${EXPENSES_TABLE_NAME}\n WHERE date >= ? AND date <= ?\n GROUP BY category\n ORDER BY amount DESC\n `, [startDate, endDate]);\n\n const expenseBreakdown = (expenseBreakdownResult.rows || expenseBreakdownResult || []).map((row: any) => ({\n category: row.category,\n amount: row.amount,\n percentage: totalExpenses > 0 ? (row.amount / totalExpenses) * 100 : 0\n }));\n\n const grossProfit = totalRevenue - totalExpenses;\n const netProfit = grossProfit; // Simplified - in real scenario, subtract taxes, etc.\n const profitMargin = totalRevenue > 0 ? (netProfit / totalRevenue) * 100 : 0;\n\n return {\n period: `${startDate} to ${endDate}`,\n totalRevenue,\n totalExpenses,\n grossProfit,\n netProfit,\n profitMargin,\n totalOrders: orderStats.totalOrders,\n averageOrderValue: orderStats.avgOrderValue,\n topSellingProducts,\n expenseBreakdown\n };\n}\n\n// Reset all stock to zero (for pre-operations setup)\nexport async function resetAllStockToZero(): Promise {\n console.log('Resetting all product stock to zero...');\n \n // Get all products first to record stock movements\n const products = await getAllProducts();\n \n // Reset all stock to zero\n await sqlite.execute(`\n UPDATE ${PRODUCTS_TABLE_NAME} \n SET currentStock = 0, updatedAt = CURRENT_TIMESTAMP\n WHERE isActive = 1\n `);\n \n // Record stock movements for each product that had stock\n for (const product of products) {\n if (product.currentStock > 0) {\n await recordStockMovement({\n productId: product.id!,\n movementType: 'ADJUSTMENT',\n quantity: product.currentStock,\n reason: 'Stock reset to zero - operations not yet started',\n previousStock: product.currentStock,\n newStock: 0\n });\n }\n }\n \n console.log(`Reset stock for ${products.length} products to zero`);\n}\n\nexport async function getSalesReport(period: string, startDate?: string, endDate?: string): Promise {\n // Calculate date range if not provided (same logic as financial summary)\n if (!startDate || !endDate) {\n const now = new Date();\n if (period === 'month') {\n startDate = new Date(now.getFullYear(), now.getMonth(), 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0).toISOString().split('T')[0];\n } else if (period === 'quarter') {\n const quarter = Math.floor(now.getMonth() / 3);\n startDate = new Date(now.getFullYear(), quarter * 3, 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), (quarter + 1) * 3, 0).toISOString().split('T')[0];\n } else if (period === 'year') {\n startDate = new Date(now.getFullYear(), 0, 1).toISOString().split('T')[0];\n endDate = new Date(now.getFullYear(), 11, 31).toISOString().split('T')[0];\n }\n }\n\n // Get total sales\n const salesResult = await sqlite.execute(`\n SELECT \n COALESCE(SUM(totalAmount), 0) as totalSales,\n COALESCE(SUM(quantity), 0) as totalQuantity,\n COALESCE(AVG(totalAmount), 0) as avgOrderValue\n FROM ${TABLE_NAME}\n WHERE createdAt >= ? AND createdAt <= ? AND status != 'cancelled'\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n \n const salesStats = salesResult.rows?.[0] || salesResult[0] || { totalSales: 0, totalQuantity: 0, avgOrderValue: 0 };\n\n // Get sales by category\n const categoryResult = await sqlite.execute(`\n SELECT \n productCategory as category,\n COALESCE(SUM(totalAmount), 0) as sales,\n COALESCE(SUM(quantity), 0) as quantity,\n COUNT(*) as orders\n FROM ${TABLE_NAME}\n WHERE createdAt >= ? AND createdAt <= ? AND status != 'cancelled'\n GROUP BY productCategory\n ORDER BY sales DESC\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n\n const salesByCategory = (categoryResult.rows || categoryResult || []).map((row: any) => ({\n category: row.category,\n sales: row.sales,\n quantity: row.quantity,\n orders: row.orders\n }));\n\n // Get sales by product with profit margins\n const productResult = await sqlite.execute(`\n SELECT \n o.orderType as productName,\n COALESCE(SUM(o.totalAmount), 0) as sales,\n COALESCE(SUM(o.quantity), 0) as quantity,\n COUNT(*) as orders,\n COALESCE(AVG(p.sellingPrice - p.costPrice), 0) as profitMargin\n FROM ${TABLE_NAME} o\n LEFT JOIN ${PRODUCTS_TABLE_NAME} p ON p.name = o.orderType\n WHERE o.createdAt >= ? AND o.createdAt <= ? AND o.status != 'cancelled'\n GROUP BY o.orderType\n ORDER BY sales DESC\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n\n const salesByProduct = (productResult.rows || productResult || []).map((row: any) => ({\n productName: row.productName,\n sales: row.sales,\n quantity: row.quantity,\n orders: row.orders,\n profitMargin: row.profitMargin\n }));\n\n // Get sales trend (daily sales for the period)\n const trendResult = await sqlite.execute(`\n SELECT \n DATE(createdAt) as date,\n COALESCE(SUM(totalAmount), 0) as sales,\n COUNT(*) as orders\n FROM ${TABLE_NAME}\n WHERE createdAt >= ? AND createdAt <= ? AND status != 'cancelled'\n GROUP BY DATE(createdAt)\n ORDER BY date\n `, [startDate + ' 00:00:00', endDate + ' 23:59:59']);\n\n const salesTrend = (trendResult.rows || trendResult || []).map((row: any) => ({\n date: row.date,\n sales: row.sales,\n orders: row.orders\n }));\n\n return {\n period: `${startDate} to ${endDate}`,\n totalSales: salesStats.totalSales,\n totalQuantity: salesStats.totalQuantity,\n averageOrderValue: salesStats.avgOrderValue,\n salesByCategory,\n salesByProduct,\n salesTrend\n };\n}","deleted","label",[171,172,153],"backend","database","permalink","https://www.val.town/x/eddie_walk/crm_OBUO_FARMS/code/backend/database/queries.ts","extension","actionData","errors"]