[{"_1":2,"_172":-5,"_173":-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","0f1790570f24c75ae1c9fc99892327093b413003","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,"_146":147,"_169":170,"_171":-5},"handle","bart","name","Rowing","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":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","219112cc-8e93-11f0-9386-0224a6c84d84","createdAt",["D",1757542302292],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","6eeac605-6d2a-4b4d-a080-5e402a4b0861","pinnedFileId","pinPreview","screenshotVersion","likeCount","cd13fa7a-681c-458d-9abc-b9672a379733","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ycXJoYVpWakVRZEZIVmxrbmNWN255RUowMTcifQ","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",20,"21916ede-8e93-11f0-9386-0224a6c84d84","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"69b0b6f2-8e93-11f0-8201-0224a6c84d84","queries.ts","fileId","69b0b79c-8e93-11f0-8201-0224a6c84d84","parentId","8f24b1a1-7395-4d9b-81d8-8dabef50298f","branchId","branchVersion",4,"latest",true,["D",1757542423294],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n Boat, Repair, Rental, CleaningDuty, DashboardStats,\n CreateBoat, CreateRepair, CreateRental, CreateCleaningDuty \n} from \"../../shared/types.ts\";\n\n// Boat queries\nexport async function getAllBoats(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM boats ORDER BY name\");\n return result.rows as Boat[];\n}\n\nexport async function getBoatById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM boats WHERE id = ?\", [id]);\n return result.rows[0] as Boat || null;\n}\n\nexport async function createBoat(boat: CreateBoat): Promise {\n const result = await sqlite.execute(`\n INSERT INTO boats (name, type, status, manufacturer, year, notes)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [boat.name, boat.type, boat.status, boat.manufacturer, boat.year, boat.notes]);\n return result.lastInsertRowId as number;\n}\n\nexport async function updateBoat(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');\n const values = Object.values(updates);\n \n await sqlite.execute(`\n UPDATE boats \n SET ${fields}, updated_at = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [...values, id]);\n}\n\nexport async function deleteBoat(id: number): Promise {\n await sqlite.execute(\"DELETE FROM boats WHERE id = ?\", [id]);\n}\n\n// Repair queries\nexport async function getAllRepairs(): Promise {\n const result = await sqlite.execute(`\n SELECT r.*, b.name as boat_name \n FROM repairs r \n LEFT JOIN boats b ON r.boat_id = b.id \n ORDER BY r.created_at DESC\n `);\n return result.rows as Repair[];\n}\n\nexport async function getRepairById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT r.*, b.name as boat_name \n FROM repairs r \n LEFT JOIN boats b ON r.boat_id = b.id \n WHERE r.id = ?\n `, [id]);\n return result.rows[0] as Repair || null;\n}\n\nexport async function createRepair(repair: CreateRepair): Promise {\n const result = await sqlite.execute(`\n INSERT INTO repairs (boat_id, description, priority, status, reported_by, assigned_to, estimated_cost, parts_needed)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [repair.boat_id, repair.description, repair.priority, repair.status, repair.reported_by, repair.assigned_to, repair.estimated_cost, repair.parts_needed]);\n \n // Update boat status to maintenance if repair is created\n if (repair.status === 'in_progress' || repair.priority === 'urgent') {\n await updateBoat(repair.boat_id, { status: 'maintenance' });\n }\n \n return result.lastInsertRowId as number;\n}\n\nexport async function updateRepair(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');\n const values = Object.values(updates);\n \n let query = `UPDATE repairs SET ${fields}, updated_at = CURRENT_TIMESTAMP`;\n \n // If status is being updated to completed, set completed_at\n if (updates.status === 'completed') {\n query += ', completed_at = CURRENT_TIMESTAMP';\n }\n \n query += ' WHERE id = ?';\n \n await sqlite.execute(query, [...values, id]);\n \n // If repair is completed, update boat status back to available\n if (updates.status === 'completed') {\n const repair = await getRepairById(id);\n if (repair) {\n await updateBoat(repair.boat_id, { status: 'available' });\n }\n }\n}\n\nexport async function deleteRepair(id: number): Promise {\n await sqlite.execute(\"DELETE FROM repairs WHERE id = ?\", [id]);\n}\n\n// Rental queries\nexport async function getAllRentals(): Promise {\n const result = await sqlite.execute(`\n SELECT r.*, b.name as boat_name \n FROM rentals r \n LEFT JOIN boats b ON r.boat_id = b.id \n ORDER BY r.start_date DESC\n `);\n return result.rows as Rental[];\n}\n\nexport async function getRentalById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT r.*, b.name as boat_name \n FROM rentals r \n LEFT JOIN boats b ON r.boat_id = b.id \n WHERE r.id = ?\n `, [id]);\n return result.rows[0] as Rental || null;\n}\n\nexport async function createRental(rental: CreateRental): Promise {\n const result = await sqlite.execute(`\n INSERT INTO rentals (boat_id, renter_name, renter_email, renter_phone, start_date, end_date, status, deposit_amount, rental_fee, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [rental.boat_id, rental.renter_name, rental.renter_email, rental.renter_phone, rental.start_date, rental.end_date, rental.status, rental.deposit_amount, rental.rental_fee, rental.notes]);\n \n // Update boat status to rented\n if (rental.status === 'active') {\n await updateBoat(rental.boat_id, { status: 'rented' });\n }\n \n return result.lastInsertRowId as number;\n}\n\nexport async function updateRental(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');\n const values = Object.values(updates);\n \n await sqlite.execute(`\n UPDATE rentals \n SET ${fields}, updated_at = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [...values, id]);\n \n // If rental is completed or cancelled, update boat status back to available\n if (updates.status === 'completed' || updates.status === 'cancelled') {\n const rental = await getRentalById(id);\n if (rental) {\n await updateBoat(rental.boat_id, { status: 'available' });\n }\n }\n}\n\nexport async function deleteRental(id: number): Promise {\n await sqlite.execute(\"DELETE FROM rentals WHERE id = ?\", [id]);\n}\n\n// Cleaning duty queries\nexport async function getAllCleaningDuties(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM cleaning_duties ORDER BY due_date ASC\");\n return result.rows as CleaningDuty[];\n}\n\nexport async function getCleaningDutyById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM cleaning_duties WHERE id = ?\", [id]);\n return result.rows[0] as CleaningDuty || null;\n}\n\nexport async function createCleaningDuty(duty: CreateCleaningDuty): Promise {\n const result = await sqlite.execute(`\n INSERT INTO cleaning_duties (assigned_to, task_description, area, priority, status, due_date, assigned_date, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [duty.assigned_to, duty.task_description, duty.area, duty.priority, duty.status, duty.due_date, duty.assigned_date, duty.notes]);\n return result.lastInsertRowId as number;\n}\n\nexport async function updateCleaningDuty(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');\n const values = Object.values(updates);\n \n let query = `UPDATE cleaning_duties SET ${fields}, updated_at = CURRENT_TIMESTAMP`;\n \n // If status is being updated to completed, set completed_at\n if (updates.status === 'completed') {\n query += ', completed_at = CURRENT_TIMESTAMP';\n }\n \n query += ' WHERE id = ?';\n \n await sqlite.execute(query, [...values, id]);\n}\n\nexport async function deleteCleaningDuty(id: number): Promise {\n await sqlite.execute(\"DELETE FROM cleaning_duties WHERE id = ?\", [id]);\n}\n\n// Dashboard statistics\nexport async function getDashboardStats(): Promise {\n const [boatsResult, repairsResult, rentalsResult, cleaningResult] = await Promise.all([\n sqlite.execute(\"SELECT status, COUNT(*) as count FROM boats GROUP BY status\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM repairs WHERE status IN ('pending', 'in_progress')\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM rentals WHERE status = 'active'\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM cleaning_duties WHERE status IN ('assigned', 'in_progress') AND due_date < datetime('now')\")\n ]);\n\n const boatStats = boatsResult.rows.reduce((acc: any, row: any) => {\n acc[row.status] = row.count;\n return acc;\n }, {});\n\n return {\n total_boats: Object.values(boatStats).reduce((sum: number, count: any) => sum + count, 0),\n available_boats: boatStats.available || 0,\n boats_in_maintenance: boatStats.maintenance || 0,\n active_rentals: rentalsResult.rows[0]?.count || 0,\n pending_repairs: repairsResult.rows[0]?.count || 0,\n overdue_cleaning: cleaningResult.rows[0]?.count || 0\n };\n}\n\n// Update overdue rentals and cleaning duties\nexport async function updateOverdueItems(): Promise {\n // Update overdue rentals\n await sqlite.execute(`\n UPDATE rentals \n SET status = 'overdue', updated_at = CURRENT_TIMESTAMP \n WHERE status = 'active' AND end_date < datetime('now')\n `);\n\n // Update overdue cleaning duties\n await sqlite.execute(`\n UPDATE cleaning_duties \n SET status = 'overdue', updated_at = CURRENT_TIMESTAMP \n WHERE status IN ('assigned', 'in_progress') AND due_date < datetime('now')\n `);\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/bart/Rowing/code/backend/database/queries.ts","extension","actionData","errors"]