[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","1f1a009cadcf7353a37b999b92a7f8af633192ea","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_155":156,"_177":178,"_179":-5},"handle","Mouhak","name","my-first-val","path","backend/database/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_141":142,"_154":20},"id","c0416410-41a6-11f0-b07e-76b3cceeab13","createdAt",["D",1749084489435],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","b4fcfa60-41a6-11f0-a933-b14e0af544f0","pinnedFileId","c049a616-41a6-11f0-b07e-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","e472d355-967c-4f88-94c6-a3576ff331c2","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeTRDU3ZUOHAwTzhHaVE3RkhOM2IzWWoxV3IifQ","org",{"_95":96,"_138":135,"_12":113,"_139":140},"avatar","tier","free","branch",{"_143":144,"_97":45,"_103":145,"_146":104,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":98,"_153":96},"version",27,"c041aa42-41a6-11f0-b07e-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":157,"_97":158,"_159":160,"_161":162,"_163":145,"_164":165,"_166":117,"_105":167,"_143":33,"_168":169,"_170":171,"_172":20,"_173":-5,"_99":174},"135130d2-4219-11f0-b533-76b3cceeab13","queries.ts","fileId","13513186-4219-11f0-b533-76b3cceeab13","parentId","c92cc891-b3b2-465b-959b-61aafcf936b7","branchId","branchVersion",15,"latest",["D",1749133591378],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Project, Message, ProjectUpdate, DashboardStats } from \"../../shared/types.ts\";\n\n// User queries\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE id = ?\", [id]);\n return result[0] as User || null;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE email = ?\", [email]);\n return result[0] as User || null;\n}\n\nexport async function createUser(email: string, name: string, role: 'customer' | 'agency' = 'customer'): Promise {\n await sqlite.execute(\n \"INSERT INTO users (email, name, role) VALUES (?, ?, ?)\",\n [email, name, role]\n );\n \n const result = await sqlite.execute(\"SELECT * FROM users WHERE email = ?\", [email]);\n return result[0] as User;\n}\n\n// Project queries\nexport async function getAllProjects(): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, u.name as customer_name, u.email as customer_email \n FROM projects p \n LEFT JOIN users u ON p.customer_id = u.id \n ORDER BY p.updated_at DESC\n `);\n \n return result.map(row => ({\n ...row,\n customer: {\n id: row.customer_id,\n name: row.customer_name,\n email: row.customer_email,\n role: 'customer' as const,\n created_at: ''\n }\n })) as Project[];\n}\n\nexport async function getProjectsByCustomerId(customerId: number): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, u.name as customer_name, u.email as customer_email \n FROM projects p \n LEFT JOIN users u ON p.customer_id = u.id \n WHERE p.customer_id = ? \n ORDER BY p.updated_at DESC\n `, [customerId]);\n \n return result.map(row => ({\n ...row,\n customer: {\n id: row.customer_id,\n name: row.customer_name,\n email: row.customer_email,\n role: 'customer' as const,\n created_at: ''\n }\n })) as Project[];\n}\n\nexport async function getProjectById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT p.*, u.name as customer_name, u.email as customer_email \n FROM projects p \n LEFT JOIN users u ON p.customer_id = u.id \n WHERE p.id = ?\n `, [id]);\n \n if (!result[0]) return null;\n \n const row = result[0];\n return {\n ...row,\n customer: {\n id: row.customer_id,\n name: row.customer_name,\n email: row.customer_email,\n role: 'customer' as const,\n created_at: ''\n }\n } as Project;\n}\n\nexport async function createProject(projectData: {\n customer_id: number;\n title: string;\n description: string;\n status?: string;\n priority?: string;\n start_date?: string;\n due_date?: string;\n budget?: number;\n progress?: number;\n}): Promise {\n const {\n customer_id,\n title,\n description,\n status = 'planning',\n priority = 'medium',\n start_date,\n due_date,\n budget = 0,\n progress = 0\n } = projectData;\n\n await sqlite.execute(`\n INSERT INTO projects (customer_id, title, description, status, priority, start_date, due_date, budget, progress)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [customer_id, title, description, status, priority, start_date, due_date, budget, progress]);\n\n const result = await sqlite.execute(\"SELECT last_insert_rowid() as id\");\n const projectId = result[0].id;\n \n return await getProjectById(projectId) as Project;\n}\n\nexport async function updateProject(id: number, updates: Partial): Promise {\n const setClause = Object.keys(updates)\n .filter(key => key !== 'id' && key !== 'customer' && updates[key as keyof typeof updates] !== undefined)\n .map(key => `${key} = ?`)\n .join(', ');\n \n if (!setClause) return await getProjectById(id);\n\n const values = Object.keys(updates)\n .filter(key => key !== 'id' && key !== 'customer' && updates[key as keyof typeof updates] !== undefined)\n .map(key => updates[key as keyof typeof updates]);\n\n await sqlite.execute(`\n UPDATE projects \n SET ${setClause}, updated_at = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [...values, id]);\n\n return await getProjectById(id);\n}\n\n// Message queries\nexport async function getMessagesByProjectId(projectId: number): Promise {\n const result = await sqlite.execute(`\n SELECT m.*, u.name as sender_name, u.email as sender_email, u.role as sender_role\n FROM messages m\n LEFT JOIN users u ON m.sender_id = u.id\n WHERE m.project_id = ?\n ORDER BY m.created_at ASC\n `, [projectId]);\n\n return result.map(row => ({\n ...row,\n sender: {\n id: row.sender_id,\n name: row.sender_name,\n email: row.sender_email,\n role: row.sender_role,\n created_at: ''\n }\n })) as Message[];\n}\n\nexport async function createMessage(messageData: {\n project_id: number;\n sender_id: number;\n content: string;\n message_type?: string;\n file_url?: string;\n file_name?: string;\n}): Promise {\n const {\n project_id,\n sender_id,\n content,\n message_type = 'text',\n file_url,\n file_name\n } = messageData;\n\n await sqlite.execute(`\n INSERT INTO messages (project_id, sender_id, content, message_type, file_url, file_name)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [project_id, sender_id, content, message_type, file_url, file_name]);\n\n const result = await sqlite.execute(\"SELECT last_insert_rowid() as id\");\n const messageId = result[0].id;\n \n const messages = await getMessagesByProjectId(project_id);\n return messages.find(m => m.id === messageId) as Message;\n}\n\n// Project update queries\nexport async function getProjectUpdatesByProjectId(projectId: number): Promise {\n const result = await sqlite.execute(`\n SELECT pu.*, u.name as user_name, u.email as user_email, u.role as user_role\n FROM project_updates pu\n LEFT JOIN users u ON pu.user_id = u.id\n WHERE pu.project_id = ?\n ORDER BY pu.created_at DESC\n `, [projectId]);\n\n return result.map(row => ({\n ...row,\n user: {\n id: row.user_id,\n name: row.user_name,\n email: row.user_email,\n role: row.user_role,\n created_at: ''\n }\n })) as ProjectUpdate[];\n}\n\nexport async function createProjectUpdate(updateData: {\n project_id: number;\n user_id: number;\n title: string;\n description: string;\n update_type?: string;\n}): Promise {\n const {\n project_id,\n user_id,\n title,\n description,\n update_type = 'general'\n } = updateData;\n\n await sqlite.execute(`\n INSERT INTO project_updates (project_id, user_id, title, description, update_type)\n VALUES (?, ?, ?, ?, ?)\n `, [project_id, user_id, title, description, update_type]);\n\n const result = await sqlite.execute(\"SELECT last_insert_rowid() as id\");\n const updateId = result[0].id;\n \n const updates = await getProjectUpdatesByProjectId(project_id);\n return updates.find(u => u.id === updateId) as ProjectUpdate;\n}\n\n// Dashboard stats\nexport async function getDashboardStats(customerId?: number): Promise {\n let whereClause = '';\n let params: any[] = [];\n \n if (customerId) {\n whereClause = 'WHERE customer_id = ?';\n params = [customerId];\n }\n\n const totalResult = await sqlite.execute(`SELECT COUNT(*) as count FROM projects ${whereClause}`, params);\n const activeResult = await sqlite.execute(`SELECT COUNT(*) as count FROM projects ${whereClause} ${whereClause ? 'AND' : 'WHERE'} status IN ('planning', 'in_progress')`, params);\n const completedResult = await sqlite.execute(`SELECT COUNT(*) as count FROM projects ${whereClause} ${whereClause ? 'AND' : 'WHERE'} status = 'completed'`, params);\n \n // For pending messages, we'll count unread messages (simplified - in real app you'd track read status)\n const messagesResult = await sqlite.execute(`\n SELECT COUNT(*) as count FROM messages m \n JOIN projects p ON m.project_id = p.id \n ${whereClause ? `WHERE p.customer_id = ?` : ''}\n `, params);\n\n return {\n total_projects: totalResult[0].count,\n active_projects: activeResult[0].count,\n completed_projects: completedResult[0].count,\n pending_messages: messagesResult[0].count\n };\n}","deleted","label",[175,176,158],"backend","database","permalink","https://www.val.town/x/Mouhak/my-first-val/code/backend/database/queries.ts","extension","actionData","errors"]