[{"_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","e07cad684b5dc23115887bcf640336d2523678fa","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","jasonr","name","Gliderops","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","88adccf6-3458-11f0-a04d-9e149126039e","createdAt",["D",1747621530332],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","edf04d00-e0ac-11ef-b589-bf0d59e1abdf","pinnedFileId","pinPreview","screenshotVersion","likeCount","c9388b01-c598-4d0f-bacb-e146aba80959","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJzUmNXSXVCcGRvd0FvMjlRcGo5aVRvRFppdiJ9","org",{"_93":94,"_131":128,"_12":111},"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",14,"88adfa46-3458-11f0-a04d-9e149126039e","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},"019276f8-3459-11f0-b615-9e149126039e","queries.ts","fileId","019278ec-3459-11f0-b615-9e149126039e","parentId","e12e4c7c-94a2-49cb-b33c-8ea959791772","branchId","branchVersion",4,"latest",true,["D",1747621733156],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./migrations.ts\";\nimport { \n User, UserRole, Aircraft, MaintenanceRecord, \n FlightBooking, BillingRecord, WeatherData \n} from \"../../shared/types.ts\";\n\n// User queries\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT u.*, GROUP_CONCAT(ur.role) as roles \n FROM ${TABLES.USERS} u\n LEFT JOIN ${TABLES.USER_ROLES} ur ON u.id = ur.user_id\n WHERE u.id = ?\n GROUP BY u.id`,\n [id]\n );\n \n if (result.rows.length === 0) return null;\n \n const user = result.rows[0];\n return {\n id: user.id,\n email: user.email,\n name: user.name,\n phone: user.phone,\n role: (user.roles ? user.roles.split(',') : []) as UserRole[],\n memberSince: user.member_since,\n pilotRatings: user.pilot_ratings ? user.pilot_ratings.split(',') : undefined,\n towPilotQualified: Boolean(user.tow_pilot_qualified),\n emergencyContact: user.emergency_contact_name ? {\n name: user.emergency_contact_name,\n phone: user.emergency_contact_phone,\n relationship: user.emergency_contact_relationship\n } : undefined\n };\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT u.*, GROUP_CONCAT(ur.role) as roles \n FROM ${TABLES.USERS} u\n LEFT JOIN ${TABLES.USER_ROLES} ur ON u.id = ur.user_id\n WHERE u.email = ?\n GROUP BY u.id`,\n [email]\n );\n \n if (result.rows.length === 0) return null;\n \n const user = result.rows[0];\n return {\n id: user.id,\n email: user.email,\n name: user.name,\n phone: user.phone,\n role: (user.roles ? user.roles.split(',') : []) as UserRole[],\n memberSince: user.member_since,\n pilotRatings: user.pilot_ratings ? user.pilot_ratings.split(',') : undefined,\n towPilotQualified: Boolean(user.tow_pilot_qualified),\n emergencyContact: user.emergency_contact_name ? {\n name: user.emergency_contact_name,\n phone: user.emergency_contact_phone,\n relationship: user.emergency_contact_relationship\n } : undefined\n };\n}\n\nexport async function getAllUsers(): Promise {\n const result = await sqlite.execute(\n `SELECT u.*, GROUP_CONCAT(ur.role) as roles \n FROM ${TABLES.USERS} u\n LEFT JOIN ${TABLES.USER_ROLES} ur ON u.id = ur.user_id\n GROUP BY u.id`\n );\n \n return result.rows.map(user => ({\n id: user.id,\n email: user.email,\n name: user.name,\n phone: user.phone,\n role: (user.roles ? user.roles.split(',') : []) as UserRole[],\n memberSince: user.member_since,\n pilotRatings: user.pilot_ratings ? user.pilot_ratings.split(',') : undefined,\n towPilotQualified: Boolean(user.tow_pilot_qualified),\n emergencyContact: user.emergency_contact_name ? {\n name: user.emergency_contact_name,\n phone: user.emergency_contact_phone,\n relationship: user.emergency_contact_relationship\n } : undefined\n }));\n}\n\nexport async function createUser(user: Omit, passwordHash: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.USERS} \n (email, password_hash, name, phone, member_since, pilot_ratings, tow_pilot_qualified, \n emergency_contact_name, emergency_contact_phone, emergency_contact_relationship)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [\n user.email,\n passwordHash,\n user.name,\n user.phone,\n user.memberSince,\n user.pilotRatings?.join(','),\n user.towPilotQualified ? 1 : 0,\n user.emergencyContact?.name,\n user.emergencyContact?.phone,\n user.emergencyContact?.relationship\n ]\n );\n \n const userId = result.rows[0].id;\n \n // Insert user roles\n for (const role of user.role) {\n await sqlite.execute(\n `INSERT INTO ${TABLES.USER_ROLES} (user_id, role) VALUES (?, ?)`,\n [userId, role]\n );\n }\n \n return userId;\n}\n\n// Aircraft queries\nexport async function getAllAircraft(): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${TABLES.AIRCRAFT}`);\n \n return result.rows.map(aircraft => ({\n id: aircraft.id,\n registration: aircraft.registration,\n type: aircraft.type,\n make: aircraft.make,\n model: aircraft.model,\n year: aircraft.year,\n lastAnnual: aircraft.last_annual,\n nextAnnual: aircraft.next_annual,\n totalHours: aircraft.total_hours,\n status: aircraft.status,\n notes: aircraft.notes\n }));\n}\n\nexport async function getAircraftById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.AIRCRAFT} WHERE id = ?`,\n [id]\n );\n \n if (result.rows.length === 0) return null;\n \n const aircraft = result.rows[0];\n return {\n id: aircraft.id,\n registration: aircraft.registration,\n type: aircraft.type,\n make: aircraft.make,\n model: aircraft.model,\n year: aircraft.year,\n lastAnnual: aircraft.last_annual,\n nextAnnual: aircraft.next_annual,\n totalHours: aircraft.total_hours,\n status: aircraft.status,\n notes: aircraft.notes\n };\n}\n\n// Maintenance queries\nexport async function getMaintenanceByAircraftId(aircraftId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.MAINTENANCE} WHERE aircraft_id = ? ORDER BY date DESC`,\n [aircraftId]\n );\n \n return result.rows.map(record => ({\n id: record.id,\n aircraftId: record.aircraft_id,\n date: record.date,\n description: record.description,\n performedBy: record.performed_by,\n hoursAtService: record.hours_at_service,\n type: record.type,\n cost: record.cost,\n completed: Boolean(record.completed),\n notes: record.notes\n }));\n}\n\n// Flight booking queries\nexport async function getFlightsByDate(date: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.FLIGHTS} WHERE date = ? ORDER BY start_time`,\n [date]\n );\n \n return result.rows.map(flight => ({\n id: flight.id,\n date: flight.date,\n startTime: flight.start_time,\n endTime: flight.end_time,\n gliderId: flight.glider_id,\n towPlaneId: flight.tow_plane_id,\n gliderPilotId: flight.glider_pilot_id,\n towPilotId: flight.tow_pilot_id,\n status: flight.status,\n weatherStatus: flight.weather_status,\n notes: flight.notes\n }));\n}\n\nexport async function createFlightBooking(flight: Omit): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.FLIGHTS}\n (date, start_time, end_time, glider_id, tow_plane_id, glider_pilot_id, tow_pilot_id, status, weather_status, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [\n flight.date,\n flight.startTime,\n flight.endTime,\n flight.gliderId,\n flight.towPlaneId,\n flight.gliderPilotId,\n flight.towPilotId,\n flight.status,\n flight.weatherStatus,\n flight.notes\n ]\n );\n \n return result.rows[0].id;\n}\n\n// Billing queries\nexport async function getBillingByUserId(userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.BILLING} WHERE user_id = ? ORDER BY date DESC`,\n [userId]\n );\n \n return result.rows.map(record => ({\n id: record.id,\n userId: record.user_id,\n flightId: record.flight_id,\n date: record.date,\n amount: record.amount,\n description: record.description,\n status: record.status,\n paymentMethod: record.payment_method,\n paymentDate: record.payment_date\n }));\n}\n\nexport async function createBillingRecord(billing: Omit): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.BILLING}\n (user_id, flight_id, date, amount, description, status, payment_method, payment_date)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [\n billing.userId,\n billing.flightId,\n billing.date,\n billing.amount,\n billing.description,\n billing.status,\n billing.paymentMethod,\n billing.paymentDate\n ]\n );\n \n return result.rows[0].id;\n}\n\n// Weather queries\nexport async function getLatestWeather(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.WEATHER} ORDER BY timestamp DESC LIMIT 1`\n );\n \n if (result.rows.length === 0) return null;\n \n const weather = result.rows[0];\n return {\n timestamp: weather.timestamp,\n temperature: weather.temperature,\n windSpeed: weather.wind_speed,\n windDirection: weather.wind_direction,\n visibility: weather.visibility,\n cloudCeiling: weather.cloud_ceiling,\n conditions: weather.conditions,\n suitable: Boolean(weather.suitable)\n };\n}\n\nexport async function saveWeatherData(weather: Omit): Promise {\n await sqlite.execute(\n `INSERT INTO ${TABLES.WEATHER}\n (timestamp, temperature, wind_speed, wind_direction, visibility, cloud_ceiling, conditions, suitable)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n weather.timestamp,\n weather.temperature,\n weather.windSpeed,\n weather.windDirection,\n weather.visibility,\n weather.cloudCeiling,\n weather.conditions,\n weather.suitable ? 1 : 0\n ]\n );\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/jasonr/Gliderops/code/backend/database/queries.ts","extension","actionData","errors"]