[{"_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","4e736cb42e83c17c10d9f33412034a74b06f3941","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","wahobd","name","waho-invest-team","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":73,"_111":73,"_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","11b339c2-a20a-11f0-a7b0-0224a6c84d84","createdAt",["D",1759682457876],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","8e0cb670-0173-11f0-8fc2-29ca0ea0c0ed","pinnedFileId","11b5ce12-a20a-11f0-a7b0-0224a6c84d84","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","cf832929-9b29-451f-a2c4-eccee50aba5c","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ1TFF3NjBXTTBuSThiWUhvMGJibVVxZmNSMCJ9","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,"11b39886-a20a-11f0-a7b0-0224a6c84d84","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},"63b0770e-a218-11f0-b811-0224a6c84d84","queries.ts","fileId","63b077c2-a218-11f0-b811-0224a6c84d84","parentId","74e56483-7a21-4ce4-98c1-c32efce3811a","branchId","branchVersion",12,"latest",["D",1759688608378],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./schema.ts\";\n\n// User related queries\nexport interface User {\n id?: number;\n telegram_user_id: string;\n phone_number?: string;\n username?: string;\n balance: number;\n vip_level: number;\n total_tasks_completed: number;\n referral_code: string;\n referred_by?: string;\n created_at?: string;\n updated_at?: string;\n}\n\nexport interface Referral {\n id?: number;\n referrer_telegram_id: string;\n referred_telegram_id: string;\n referral_level: number;\n status: string;\n task_commission_paid: number;\n vip_commission_paid: number;\n created_at?: string;\n}\n\nexport interface Commission {\n id?: number;\n user_telegram_id: string;\n from_user_telegram_id: string;\n commission_type: string;\n amount: number;\n level: number;\n description?: string;\n created_at?: string;\n}\n\n// Generate unique referral code\nfunction generateReferralCode(telegramId: string): string {\n return `REF${telegramId}${Date.now().toString().slice(-4)}`;\n}\n\n// User operations\nexport async function createUser(telegramId: string, phoneNumber?: string, referredBy?: string): Promise {\n const referralCode = generateReferralCode(telegramId);\n \n const { rows } = await sqlite.execute(`\n INSERT INTO ${TABLES.USERS} \n (telegram_user_id, phone_number, referral_code, referred_by, username)\n VALUES (?, ?, ?, ?, ?)\n `, [telegramId, phoneNumber || null, referralCode, referredBy || null, `UID ${telegramId}`]);\n\n // If user was referred, create referral record\n if (referredBy) {\n await createReferralRecord(referredBy, telegramId);\n }\n\n return await getUserByTelegramId(telegramId);\n}\n\nexport async function getUserByTelegramId(telegramId: string): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM ${TABLES.USERS} WHERE telegram_user_id = ?\n `, [telegramId]);\n\n return rows.length > 0 ? rows[0] as User : null;\n}\n\nexport async function updateUserBalance(telegramId: string, newBalance: number): Promise {\n await sqlite.execute(`\n UPDATE ${TABLES.USERS} \n SET balance = ?, updated_at = CURRENT_TIMESTAMP \n WHERE telegram_user_id = ?\n `, [newBalance, telegramId]);\n}\n\nexport async function updateUserVipLevel(telegramId: string, vipLevel: number): Promise {\n await sqlite.execute(`\n UPDATE ${TABLES.USERS} \n SET vip_level = ?, updated_at = CURRENT_TIMESTAMP \n WHERE telegram_user_id = ?\n `, [vipLevel, telegramId]);\n}\n\nexport async function updateUserTasksCompleted(telegramId: string, tasksCompleted: number): Promise {\n await sqlite.execute(`\n UPDATE ${TABLES.USERS} \n SET total_tasks_completed = ?, updated_at = CURRENT_TIMESTAMP \n WHERE telegram_user_id = ?\n `, [tasksCompleted, telegramId]);\n}\n\n// Referral operations\nexport async function createReferralRecord(referrerTelegramId: string, referredTelegramId: string): Promise {\n try {\n // Create direct referral (level 1)\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.REFERRALS} \n (referrer_telegram_id, referred_telegram_id, referral_level, status)\n VALUES (?, ?, 1, 'active')\n `, [referrerTelegramId, referredTelegramId]);\n\n // Find referrer's referrer for level 2\n const { rows: level2Rows } = await sqlite.execute(`\n SELECT referred_by FROM ${TABLES.USERS} WHERE telegram_user_id = ?\n `, [referrerTelegramId]);\n\n if (level2Rows.length > 0 && level2Rows[0].referred_by) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.REFERRALS} \n (referrer_telegram_id, referred_telegram_id, referral_level, status)\n VALUES (?, ?, 2, 'active')\n `, [level2Rows[0].referred_by, referredTelegramId]);\n\n // Find level 3 referrer\n const { rows: level3Rows } = await sqlite.execute(`\n SELECT referred_by FROM ${TABLES.USERS} WHERE telegram_user_id = ?\n `, [level2Rows[0].referred_by]);\n\n if (level3Rows.length > 0 && level3Rows[0].referred_by) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.REFERRALS} \n (referrer_telegram_id, referred_telegram_id, referral_level, status)\n VALUES (?, ?, 3, 'active')\n `, [level3Rows[0].referred_by, referredTelegramId]);\n }\n }\n } catch (error) {\n console.error('Error creating referral record:', error);\n }\n}\n\nexport async function getReferralsByReferrer(referrerTelegramId: string): Promise {\n const { rows } = await sqlite.execute(`\n SELECT r.*, u.username, u.vip_level, u.total_tasks_completed\n FROM ${TABLES.REFERRALS} r\n LEFT JOIN ${TABLES.USERS} u ON r.referred_telegram_id = u.telegram_user_id\n WHERE r.referrer_telegram_id = ?\n ORDER BY r.created_at DESC\n `, [referrerTelegramId]);\n\n return rows as Referral[];\n}\n\nexport async function getReferralStats(telegramId: string): Promise<{\n totalReferrals: number;\n activeReferrals: number;\n totalTaskCommission: number;\n totalVipCommission: number;\n level1Count: number;\n level2Count: number;\n level3Count: number;\n}> {\n const { rows } = await sqlite.execute(`\n SELECT \n COUNT(*) as totalReferrals,\n COUNT(CASE WHEN status = 'active' THEN 1 END) as activeReferrals,\n SUM(task_commission_paid) as totalTaskCommission,\n SUM(vip_commission_paid) as totalVipCommission,\n COUNT(CASE WHEN referral_level = 1 THEN 1 END) as level1Count,\n COUNT(CASE WHEN referral_level = 2 THEN 1 END) as level2Count,\n COUNT(CASE WHEN referral_level = 3 THEN 1 END) as level3Count\n FROM ${TABLES.REFERRALS}\n WHERE referrer_telegram_id = ?\n `, [telegramId]);\n\n const stats = rows[0] || {};\n return {\n totalReferrals: stats.totalReferrals || 0,\n activeReferrals: stats.activeReferrals || 0,\n totalTaskCommission: stats.totalTaskCommission || 0,\n totalVipCommission: stats.totalVipCommission || 0,\n level1Count: stats.level1Count || 0,\n level2Count: stats.level2Count || 0,\n level3Count: stats.level3Count || 0\n };\n}\n\n// Commission operations\nexport async function addCommission(\n userTelegramId: string,\n fromUserTelegramId: string,\n commissionType: string,\n amount: number,\n level: number,\n description?: string\n): Promise {\n await sqlite.execute(`\n INSERT INTO ${TABLES.COMMISSIONS} \n (user_telegram_id, from_user_telegram_id, commission_type, amount, level, description)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [userTelegramId, fromUserTelegramId, commissionType, amount, level, description || null]);\n\n // Update user balance\n const user = await getUserByTelegramId(userTelegramId);\n if (user) {\n await updateUserBalance(userTelegramId, user.balance + amount);\n }\n}\n\nexport async function getCommissionHistory(telegramId: string, limit: number = 50): Promise {\n const { rows } = await sqlite.execute(`\n SELECT c.*, u.username as from_username\n FROM ${TABLES.COMMISSIONS} c\n LEFT JOIN ${TABLES.USERS} u ON c.from_user_telegram_id = u.telegram_user_id\n WHERE c.user_telegram_id = ?\n ORDER BY c.created_at DESC\n LIMIT ?\n `, [telegramId, limit]);\n\n return rows as Commission[];\n}\n\n// Task commission system\nexport async function processTaskCommission(userTelegramId: string): Promise {\n const commissionRates = {\n 1: 10.0, // Level 1: ৳10\n 2: 5.0, // Level 2: ৳5\n 3: 2.0 // Level 3: ৳2\n };\n\n // Get all referrers for this user\n const { rows: referrers } = await sqlite.execute(`\n SELECT referrer_telegram_id, referral_level\n FROM ${TABLES.REFERRALS}\n WHERE referred_telegram_id = ? AND status = 'active'\n `, [userTelegramId]);\n\n for (const referrer of referrers) {\n const commissionAmount = commissionRates[referrer.referral_level] || 0;\n if (commissionAmount > 0) {\n await addCommission(\n referrer.referrer_telegram_id,\n userTelegramId,\n 'TASK_COMMISSION',\n commissionAmount,\n referrer.referral_level,\n `Task commission from Level ${referrer.referral_level} referral`\n );\n\n // Update referral record\n await sqlite.execute(`\n UPDATE ${TABLES.REFERRALS}\n SET task_commission_paid = task_commission_paid + ?\n WHERE referrer_telegram_id = ? AND referred_telegram_id = ?\n `, [commissionAmount, referrer.referrer_telegram_id, userTelegramId]);\n }\n }\n}\n\n// VIP commission system\nexport async function processVipCommission(userTelegramId: string, vipLevel: number): Promise {\n const commissionRates = {\n 1: 15.0, // Level 1: ৳15\n 2: 10.0, // Level 2: ৳10\n 3: 5.0 // Level 3: ৳5\n };\n\n // Get all referrers for this user\n const { rows: referrers } = await sqlite.execute(`\n SELECT referrer_telegram_id, referral_level\n FROM ${TABLES.REFERRALS}\n WHERE referred_telegram_id = ? AND status = 'active'\n `, [userTelegramId]);\n\n for (const referrer of referrers) {\n const commissionAmount = commissionRates[referrer.referral_level] || 0;\n if (commissionAmount > 0) {\n await addCommission(\n referrer.referrer_telegram_id,\n userTelegramId,\n 'VIP_COMMISSION',\n commissionAmount,\n referrer.referral_level,\n `VIP Level ${vipLevel} commission from Level ${referrer.referral_level} referral`\n );\n\n // Update referral record\n await sqlite.execute(`\n UPDATE ${TABLES.REFERRALS}\n SET vip_commission_paid = vip_commission_paid + ?\n WHERE referrer_telegram_id = ? AND referred_telegram_id = ?\n `, [commissionAmount, referrer.referrer_telegram_id, userTelegramId]);\n }\n }\n}\n\n// Revenue logging\nexport async function logRevenue(\n userTelegramId: string,\n type: string,\n amount: number,\n description?: string\n): Promise {\n await sqlite.execute(`\n INSERT INTO ${TABLES.REVENUE_LOGS} \n (user_telegram_id, type, amount, description)\n VALUES (?, ?, ?, ?)\n `, [userTelegramId, type, amount, description || null]);\n}\n\nexport async function getRevenueHistory(telegramId: string, limit: number = 50): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM ${TABLES.REVENUE_LOGS}\n WHERE user_telegram_id = ?\n ORDER BY created_at DESC\n LIMIT ?\n `, [telegramId, limit]);\n\n return rows;\n}\n\n// Admin queries\nexport async function getAllUsers(limit: number = 100): Promise {\n const { rows } = await sqlite.execute(`\n SELECT * FROM ${TABLES.USERS}\n ORDER BY created_at DESC\n LIMIT ?\n `, [limit]);\n\n return rows as User[];\n}\n\nexport async function getTotalStats(): Promise<{\n totalUsers: number;\n totalBalance: number;\n totalCommissions: number;\n totalReferrals: number;\n}> {\n const { rows } = await sqlite.execute(`\n SELECT \n (SELECT COUNT(*) FROM ${TABLES.USERS}) as totalUsers,\n (SELECT SUM(balance) FROM ${TABLES.USERS}) as totalBalance,\n (SELECT SUM(amount) FROM ${TABLES.COMMISSIONS}) as totalCommissions,\n (SELECT COUNT(*) FROM ${TABLES.REFERRALS}) as totalReferrals\n `);\n\n const stats = rows[0] || {};\n return {\n totalUsers: stats.totalUsers || 0,\n totalBalance: stats.totalBalance || 0,\n totalCommissions: stats.totalCommissions || 0,\n totalReferrals: stats.totalReferrals || 0\n };\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/wahobd/waho-invest-team/code/backend/database/queries.ts","extension","actionData","errors"]