[{"_1":2,"_173":-5,"_174":-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","d481f3d28ac6dd3f9b89bb10f2a0c9798df3020a","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,"_147":148,"_170":171,"_172":-5},"handle","amak","name","Bizapp","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":113,"_114":20,"_115":-5,"_116":32,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","5ea4849a-3f9d-11f0-93ae-76b3cceeab13","createdAt",["D",1748860557871],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","e76ce7c0-3f90-11f0-b6e9-ab0b2f88b858","pinnedFileId","b607e196-3f9d-11f0-a7e9-76b3cceeab13","pinPreview","screenshotVersion","likeCount","19fa01d3-261d-45bc-80e7-246084201e15","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4d2hpQjEzR212Rjd6QTBlVzJMdkU5R3pPaiJ9","org",{"_93":94,"_132":129},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",19,"5ea502da-3f9d-11f0-93ae-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":32,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"e5318936-3f9d-11f0-9543-76b3cceeab13","queries.ts","fileId","e53189e0-3f9d-11f0-9543-76b3cceeab13","parentId","42e54d58-9643-492a-ba7b-e0a15ff38c5e","branchId","branchVersion",4,"latest",true,["D",1748860783606],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { Staff, Task, Client, FollowUp, KPI, DashboardData, TaskWithStaff, ClientWithFollowups } from \"../../shared/types.ts\";\n\n// Staff queries\nexport async function getAllStaff(): Promise {\n return await sqlite.execute(\"SELECT * FROM staff WHERE active = 1 ORDER BY name\");\n}\n\nexport async function getStaffById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM staff WHERE id = ?\", [id]);\n return result[0] || null;\n}\n\nexport async function createStaff(staff: Omit): Promise {\n const result = await sqlite.execute(\n \"INSERT INTO staff (name, email, role, department, active) VALUES (?, ?, ?, ?, ?) RETURNING id\",\n [staff.name, staff.email, staff.role, staff.department, staff.active]\n );\n return result[0].id;\n}\n\nexport async function updateStaff(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at');\n if (fields.length === 0) return false;\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof Staff]);\n \n await sqlite.execute(`UPDATE staff SET ${setClause} WHERE id = ?`, [...values, id]);\n return true;\n}\n\n// Task queries\nexport async function getAllTasks(): Promise {\n return await sqlite.execute(`\n SELECT t.*, \n s1.name as assigned_to_name,\n s2.name as assigned_by_name\n FROM tasks t\n LEFT JOIN staff s1 ON t.assigned_to = s1.id\n LEFT JOIN staff s2 ON t.assigned_by = s2.id\n ORDER BY \n CASE t.priority \n WHEN 'urgent' THEN 1 \n WHEN 'high' THEN 2 \n WHEN 'medium' THEN 3 \n WHEN 'low' THEN 4 \n END,\n t.due_date ASC\n `);\n}\n\nexport async function getTasksByStaff(staffId: number): Promise {\n return await sqlite.execute(`\n SELECT t.*, \n s1.name as assigned_to_name,\n s2.name as assigned_by_name\n FROM tasks t\n LEFT JOIN staff s1 ON t.assigned_to = s1.id\n LEFT JOIN staff s2 ON t.assigned_by = s2.id\n WHERE t.assigned_to = ?\n ORDER BY \n CASE t.priority \n WHEN 'urgent' THEN 1 \n WHEN 'high' THEN 2 \n WHEN 'medium' THEN 3 \n WHEN 'low' THEN 4 \n END,\n t.due_date ASC\n `, [staffId]);\n}\n\nexport async function createTask(task: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO tasks (title, description, assigned_to, assigned_by, priority, status, due_date, estimated_hours, actual_hours)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id\n `, [\n task.title, task.description, task.assigned_to, task.assigned_by,\n task.priority, task.status, task.due_date, task.estimated_hours, task.actual_hours\n ]);\n return result[0].id;\n}\n\nexport async function updateTask(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at');\n if (fields.length === 0) return false;\n \n // If status is being updated to completed, set completed_at\n if (updates.status === 'completed' && !updates.completed_at) {\n updates.completed_at = new Date().toISOString();\n fields.push('completed_at');\n }\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof Task]);\n \n await sqlite.execute(`UPDATE tasks SET ${setClause} WHERE id = ?`, [...values, id]);\n return true;\n}\n\n// Client queries\nexport async function getAllClients(): Promise {\n return await sqlite.execute(`\n SELECT c.*,\n COUNT(f.id) as pending_followups,\n MAX(f.scheduled_date) as last_followup_date\n FROM clients c\n LEFT JOIN followups f ON c.id = f.client_id AND f.status = 'scheduled'\n GROUP BY c.id\n ORDER BY c.name\n `);\n}\n\nexport async function getClientById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM clients WHERE id = ?\", [id]);\n return result[0] || null;\n}\n\nexport async function createClient(client: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO clients (name, email, phone, company, status, last_contact, next_followup, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING id\n `, [\n client.name, client.email, client.phone, client.company,\n client.status, client.last_contact, client.next_followup, client.notes\n ]);\n return result[0].id;\n}\n\nexport async function updateClient(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at');\n if (fields.length === 0) return false;\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof Client]);\n \n await sqlite.execute(`UPDATE clients SET ${setClause} WHERE id = ?`, [...values, id]);\n return true;\n}\n\n// Follow-up queries\nexport async function getFollowupsByClient(clientId: number): Promise {\n return await sqlite.execute(\n \"SELECT * FROM followups WHERE client_id = ? ORDER BY scheduled_date ASC\",\n [clientId]\n );\n}\n\nexport async function getDueFollowups(): Promise {\n return await sqlite.execute(`\n SELECT f.*, c.name as client_name, c.email as client_email\n FROM followups f\n JOIN clients c ON f.client_id = c.id\n WHERE f.status = 'scheduled' AND f.scheduled_date <= datetime('now')\n ORDER BY f.scheduled_date ASC\n `);\n}\n\nexport async function createFollowup(followup: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO followups (client_id, type, subject, message, scheduled_date, status)\n VALUES (?, ?, ?, ?, ?, ?) RETURNING id\n `, [\n followup.client_id, followup.type, followup.subject,\n followup.message, followup.scheduled_date, followup.status\n ]);\n return result[0].id;\n}\n\nexport async function updateFollowup(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at');\n if (fields.length === 0) return false;\n \n // If status is being updated to completed, set completed_at\n if (updates.status === 'completed' && !updates.completed_at) {\n updates.completed_at = new Date().toISOString();\n fields.push('completed_at');\n }\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof FollowUp]);\n \n await sqlite.execute(`UPDATE followups SET ${setClause} WHERE id = ?`, [...values, id]);\n return true;\n}\n\n// KPI queries\nexport async function getLatestKPIs(): Promise {\n return await sqlite.execute(`\n SELECT k1.*\n FROM kpis k1\n INNER JOIN (\n SELECT name, MAX(date) as max_date\n FROM kpis\n GROUP BY name\n ) k2 ON k1.name = k2.name AND k1.date = k2.max_date\n ORDER BY k1.category, k1.name\n `);\n}\n\nexport async function getKPIHistory(name: string, days: number = 30): Promise {\n return await sqlite.execute(`\n SELECT * FROM kpis \n WHERE name = ? AND date >= date('now', '-${days} days')\n ORDER BY date ASC\n `, [name]);\n}\n\nexport async function createKPI(kpi: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO kpis (name, value, target, unit, category, date)\n VALUES (?, ?, ?, ?, ?, ?) RETURNING id\n `, [kpi.name, kpi.value, kpi.target, kpi.unit, kpi.category, kpi.date]);\n return result[0].id;\n}\n\n// Dashboard data\nexport async function getDashboardData(): Promise {\n // Task statistics\n const taskStats = await sqlite.execute(`\n SELECT \n COUNT(*) as total,\n SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,\n SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress,\n SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,\n SUM(CASE WHEN status != 'completed' AND due_date < datetime('now') THEN 1 ELSE 0 END) as overdue\n FROM tasks\n `);\n\n // Staff statistics\n const staffStats = await sqlite.execute(`\n SELECT \n COUNT(*) as total,\n SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) as active\n FROM staff\n `);\n\n // Staff workload\n const staffWorkload = await sqlite.execute(`\n SELECT \n s.id as staff_id,\n s.name,\n COUNT(t.id) as task_count\n FROM staff s\n LEFT JOIN tasks t ON s.id = t.assigned_to AND t.status IN ('pending', 'in_progress')\n WHERE s.active = 1\n GROUP BY s.id, s.name\n ORDER BY task_count DESC\n `);\n\n // Client statistics\n const clientStats = await sqlite.execute(`\n SELECT \n COUNT(*) as total,\n SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,\n SUM(CASE WHEN status = 'prospect' THEN 1 ELSE 0 END) as prospects\n FROM clients\n `);\n\n const followupsDue = await sqlite.execute(`\n SELECT COUNT(*) as count\n FROM followups\n WHERE status = 'scheduled' AND scheduled_date <= datetime('now')\n `);\n\n // Latest KPIs with trends\n const kpis = await sqlite.execute(`\n SELECT \n k1.name,\n k1.value,\n k1.target,\n ROUND((k1.value * 100.0 / k1.target), 1) as percentage,\n CASE \n WHEN k2.value IS NULL THEN 'stable'\n WHEN k1.value > k2.value THEN 'up'\n WHEN k1.value < k2.value THEN 'down'\n ELSE 'stable'\n END as trend\n FROM kpis k1\n LEFT JOIN kpis k2 ON k1.name = k2.name AND k2.date = (\n SELECT MAX(date) FROM kpis k3 \n WHERE k3.name = k1.name AND k3.date < k1.date\n )\n WHERE k1.date = (\n SELECT MAX(date) FROM kpis k4 WHERE k4.name = k1.name\n )\n ORDER BY k1.category, k1.name\n `);\n\n return {\n tasks: taskStats[0],\n staff: {\n ...staffStats[0],\n workload: staffWorkload\n },\n clients: {\n ...clientStats[0],\n followups_due: followupsDue[0].count\n },\n kpis\n };\n}","deleted","label",[168,169,150],"backend","database","permalink","https://www.val.town/x/amak/Bizapp/code/backend/database/queries.ts","extension","actionData","errors"]