[{"_1":2,"_179":-5,"_180":-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","2f1b7933526c99d174f6c4b9fb9d1854a177ff5c","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,"_153":154,"_176":177,"_178":-5},"handle","trevormunoz","name","lakeland-timesheet","path","db/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":74,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":33,"_14":119,"_120":-5,"_121":-5,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_139":140,"_152":20},"id","5c0c02e4-130a-11f1-9411-42dde27851f2","createdAt",["D",1772107064004],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","7ec577df-7957-4760-a4d3-81e05cef3345","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","48ae43c1-9cce-4f57-9abb-f1491d51acfb","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yWm5xQUFoY280MXptMVB2Tk9ad0ptV3gyNWUifQ","org",{"_95":96,"_136":133,"_12":113,"_137":138},"avatar","tier","free","branch",{"_141":142,"_97":45,"_103":143,"_144":104,"_145":-5,"_146":-5,"_147":-5,"_148":-5,"_149":-5,"_150":98,"_151":96},"version",168,"5c0c68ec-130a-11f1-9411-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":155,"_97":156,"_157":158,"_159":160,"_161":143,"_162":163,"_164":165,"_105":166,"_141":167,"_168":169,"_170":171,"_172":20,"_173":-5,"_99":174},"477f0552-130e-11f1-abac-42dde27851f2","queries.ts","fileId","9eebf1f4-130b-11f1-bab2-42dde27851f2","parentId","9e9b1c5c-130b-11f1-a4f6-42dde27851f2","branchId","branchVersion",40,"latest",true,["D",1772108747167],1,"fileType","script","content","import type { Database } from \"./types.ts\";\n\n// --- Lookup table types ---\n\nexport interface Project {\n id: number;\n name: string;\n}\n\nexport interface Grant {\n id: number;\n code: string;\n name: string;\n}\n\nexport interface TaskType {\n id: number;\n name: string;\n}\n\n// --- Project CRUD ---\n\nexport async function addProject(db: Database, name: string): Promise {\n await db.execute({ sql: \"INSERT INTO projects (name) VALUES (?)\", args: [name] });\n}\n\nexport async function getActiveProjects(db: Database): Promise {\n const result = await db.execute({\n sql: \"SELECT id, name FROM projects WHERE active = 1 ORDER BY name\",\n });\n return result.rows.map((row) => ({\n id: row[0] as number,\n name: row[1] as string,\n }));\n}\n\nexport async function deactivateProject(db: Database, name: string): Promise {\n await db.execute({\n sql: \"UPDATE projects SET active = 0 WHERE name = ?\",\n args: [name],\n });\n}\n\n// --- Grant CRUD ---\n\nexport async function addGrant(db: Database, code: string, name: string): Promise {\n await db.execute({\n sql: \"INSERT INTO grants (code, name) VALUES (?, ?)\",\n args: [code, name],\n });\n}\n\nexport async function getActiveGrants(db: Database): Promise {\n const result = await db.execute({\n sql: \"SELECT id, code, name FROM grants WHERE active = 1 ORDER BY code\",\n });\n return result.rows.map((row) => ({\n id: row[0] as number,\n code: row[1] as string,\n name: row[2] as string,\n }));\n}\n\nexport async function deactivateGrant(db: Database, code: string): Promise {\n await db.execute({\n sql: \"UPDATE grants SET active = 0 WHERE code = ?\",\n args: [code],\n });\n}\n\n// --- TaskType CRUD ---\n\nexport async function addTaskType(db: Database, name: string): Promise {\n await db.execute({\n sql: \"INSERT INTO task_types (name) VALUES (?)\",\n args: [name],\n });\n}\n\nexport async function getActiveTaskTypes(db: Database): Promise {\n const result = await db.execute({\n sql: \"SELECT id, name FROM task_types WHERE active = 1 ORDER BY name\",\n });\n return result.rows.map((row) => ({\n id: row[0] as number,\n name: row[1] as string,\n }));\n}\n\nexport async function deactivateTaskType(db: Database, name: string): Promise {\n await db.execute({\n sql: \"UPDATE task_types SET active = 0 WHERE name = ?\",\n args: [name],\n });\n}\n\n// --- Time entry types ---\n\nexport interface TimeEntryInput {\n slackUserId: string;\n slackUsername: string;\n date: string;\n minutes: number;\n taskTypeId: number;\n projectId: number | null;\n grantId: number | null;\n comment: string | null;\n}\n\nexport interface TimeEntry {\n id: number;\n slackUserId: string;\n slackUsername: string;\n date: string;\n minutes: number;\n taskTypeId: number;\n projectId: number | null;\n grantId: number | null;\n comment: string | null;\n createdAt: string;\n}\n\n// --- Time entry CRUD ---\n\nexport async function createTimeEntry(db: Database, input: TimeEntryInput): Promise {\n const result = await db.execute({\n sql: `INSERT INTO time_entries (slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n input.slackUserId,\n input.slackUsername,\n input.date,\n input.minutes,\n input.taskTypeId,\n input.projectId,\n input.grantId,\n input.comment,\n ],\n });\n return Number(result.lastInsertRowid);\n}\n\nexport async function getTimeEntryById(db: Database, id: number): Promise {\n const result = await db.execute({\n sql: `SELECT id, slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment, created_at\n FROM time_entries WHERE id = ? AND deleted_at IS NULL`,\n args: [id],\n });\n if (result.rows.length === 0) return null;\n const row = result.rows[0];\n return {\n id: row[0] as number,\n slackUserId: row[1] as string,\n slackUsername: row[2] as string,\n date: row[3] as string,\n minutes: row[4] as number,\n taskTypeId: row[5] as number,\n projectId: row[6] as number | null,\n grantId: row[7] as number | null,\n comment: row[8] as string | null,\n createdAt: row[9] as string,\n };\n}\n\nexport async function updateTimeEntry(\n db: Database,\n id: number,\n updates: Partial>\n): Promise {\n const setClauses: string[] = [];\n const args: unknown[] = [];\n\n const columnMap: Record = {\n minutes: \"minutes\",\n taskTypeId: \"task_type_id\",\n projectId: \"project_id\",\n grantId: \"grant_id\",\n comment: \"comment\",\n date: \"date\",\n };\n\n for (const [key, value] of Object.entries(updates)) {\n const column = columnMap[key];\n if (column) {\n setClauses.push(`${column} = ?`);\n args.push(value);\n }\n }\n\n if (setClauses.length === 0) return;\n\n setClauses.push(\"updated_at = datetime('now')\");\n args.push(id);\n\n await db.execute({\n sql: `UPDATE time_entries SET ${setClauses.join(\", \")} WHERE id = ?`,\n args,\n });\n}\n\nexport async function softDeleteTimeEntry(db: Database, id: number): Promise {\n await db.execute({\n sql: \"UPDATE time_entries SET deleted_at = datetime('now') WHERE id = ?\",\n args: [id],\n });\n}\n\nexport async function getRecentEntries(\n db: Database,\n slackUserId: string,\n limit: number\n): Promise {\n const result = await db.execute({\n sql: `SELECT id, slack_user_id, slack_username, date, minutes, task_type_id, project_id, grant_id, comment, created_at\n FROM time_entries\n WHERE slack_user_id = ? AND deleted_at IS NULL\n ORDER BY date DESC, created_at DESC\n LIMIT ?`,\n args: [slackUserId, limit],\n });\n return result.rows.map((row) => ({\n id: row[0] as number,\n slackUserId: row[1] as string,\n slackUsername: row[2] as string,\n date: row[3] as string,\n minutes: row[4] as number,\n taskTypeId: row[5] as number,\n projectId: row[6] as number | null,\n grantId: row[7] as number | null,\n comment: row[8] as string | null,\n createdAt: row[9] as string,\n }));\n}\n\n// --- Intern types ---\n\nexport interface Intern {\n slackUserId: string;\n slackUsername: string;\n}\n\n// --- Intern management ---\n\nexport async function addIntern(\n db: Database,\n slackUserId: string,\n slackUsername: string\n): Promise {\n await db.execute({\n sql: \"INSERT OR REPLACE INTO interns (slack_user_id, slack_username) VALUES (?, ?)\",\n args: [slackUserId, slackUsername],\n });\n}\n\nexport async function removeIntern(db: Database, slackUserId: string): Promise {\n await db.execute({\n sql: \"DELETE FROM interns WHERE slack_user_id = ?\",\n args: [slackUserId],\n });\n}\n\nexport async function getInterns(db: Database): Promise {\n const result = await db.execute({\n sql: \"SELECT slack_user_id, slack_username FROM interns ORDER BY slack_username\",\n });\n return result.rows.map((row) => ({\n slackUserId: row[0] as string,\n slackUsername: row[1] as string,\n }));\n}\n\n// --- Report queries ---\n\nexport interface ReportRow {\n slackUserId: string;\n slackUsername: string;\n taskTypeName: string;\n grantCode: string | null;\n totalMinutes: number;\n}\n\nexport async function getReportData(\n db: Database,\n startDate: string,\n endDate: string,\n slackUserId: string | null\n): Promise {\n const args: unknown[] = [startDate, endDate];\n let userFilter = \"\";\n if (slackUserId) {\n userFilter = \"AND te.slack_user_id = ?\";\n args.push(slackUserId);\n }\n\n const result = await db.execute({\n sql: `SELECT te.slack_user_id, te.slack_username, tt.name as task_type_name,\n g.code as grant_code, SUM(te.minutes) as total_minutes\n FROM time_entries te\n JOIN task_types tt ON te.task_type_id = tt.id\n LEFT JOIN grants g ON te.grant_id = g.id\n WHERE te.date >= ? AND te.date <= ? AND te.deleted_at IS NULL ${userFilter}\n GROUP BY te.slack_user_id, te.slack_username, tt.name, g.code\n ORDER BY te.slack_username, tt.name`,\n args,\n });\n\n return result.rows.map((r) => ({\n slackUserId: r[0] as string,\n slackUsername: r[1] as string,\n taskTypeName: r[2] as string,\n grantCode: r[3] as string | null,\n totalMinutes: r[4] as number,\n }));\n}\n\n// --- Config key-value store ---\n\nexport async function setConfig(db: Database, key: string, value: string): Promise {\n await db.execute({\n sql: \"INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)\",\n args: [key, value],\n });\n}\n\nexport async function getConfig(db: Database, key: string): Promise {\n const result = await db.execute({\n sql: \"SELECT value FROM config WHERE key = ?\",\n args: [key],\n });\n if (result.rows.length === 0) return null;\n return result.rows[0][0] as string;\n}\n","deleted","label",[175,156],"db","permalink","https://www.val.town/x/trevormunoz/lakeland-timesheet/code/db/queries.ts","extension","actionData","errors"]