[{"_1":2,"_175":-5,"_176":-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","e3ca0a77ab07ff2ce3097208ffd6aa0e378d3c47","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,"_150":151,"_172":173,"_174":-5},"handle","Saraxxx","name","FindMyCar","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,"_149":20},"id","19c1d1e4-3f8d-11f0-9f9b-76b3cceeab13","createdAt",["D",1748853570330],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","559fb7a0-3f8b-11f0-ad5b-814c56c8abb0","pinnedFileId","pinPreview","screenshotVersion","likeCount","1b94e921-7618-4293-b78c-b587fca04678","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4d2N1bUtTaDFjOFBpNE1VQ1h3SUdvNWtYTCJ9","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":139,"_140":141,"_142":108,"_143":144,"_145":146,"_147":96,"_148":94},"version",1,"19c40478-3f8d-11f0-9f9b-76b3cceeab13","projectId","forkedBranchId","96238d58-3f84-11f0-b0be-76b3cceeab13","forkedBranchVersion",26,"forkedProjectPrivacy","forkedProjectId","962348fc-3f84-11f0-b0be-76b3cceeab13","forkedProjectOrgId","d0808b00-181f-4bcb-ac9d-1d1a3c21ac29","projectName","userHandle","isOwned","file",{"_101":152,"_95":153,"_154":155,"_156":157,"_158":136,"_159":135,"_160":161,"_103":162,"_134":32,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"19c6b772-3f8d-11f0-9f9b-76b3cceeab13","queries.ts","fileId","3da39e4d-e973-4563-93ad-8a454b97b2c1","parentId","0115e15a-b324-4718-967f-6a6a90727425","branchId","branchVersion","latest",true,["D",1748853570330],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { STOLEN_CARS_TABLE, FOUND_CARS_TABLE, MATCHES_TABLE } from \"./migrations.ts\";\nimport type { StolenCar, FoundCar, CarMatch, SearchFilters } from \"../../shared/types.ts\";\n\n// Stolen Cars Queries\nexport async function createStolenCarReport(car: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${STOLEN_CARS_TABLE} (\n make, model, year, color, license_plate, vin, description,\n stolen_date, stolen_location, stolen_lat, stolen_lng,\n owner_name, owner_phone, owner_email, police_report_number, status\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n car.make, car.model, car.year, car.color, car.license_plate, car.vin, car.description,\n car.stolen_date, car.stolen_location, car.stolen_lat, car.stolen_lng,\n car.owner_name, car.owner_phone, car.owner_email, car.police_report_number, car.status\n ]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getStolenCars(filters?: SearchFilters): Promise {\n let query = `SELECT * FROM ${STOLEN_CARS_TABLE} WHERE 1=1`;\n const params: any[] = [];\n\n if (filters?.make) {\n query += ` AND LOWER(make) LIKE LOWER(?)`;\n params.push(`%${filters.make}%`);\n }\n if (filters?.model) {\n query += ` AND LOWER(model) LIKE LOWER(?)`;\n params.push(`%${filters.model}%`);\n }\n if (filters?.color) {\n query += ` AND LOWER(color) LIKE LOWER(?)`;\n params.push(`%${filters.color}%`);\n }\n if (filters?.license_plate) {\n query += ` AND LOWER(license_plate) LIKE LOWER(?)`;\n params.push(`%${filters.license_plate}%`);\n }\n if (filters?.date_from) {\n query += ` AND stolen_date >= ?`;\n params.push(filters.date_from);\n }\n if (filters?.date_to) {\n query += ` AND stolen_date <= ?`;\n params.push(filters.date_to);\n }\n\n query += ` ORDER BY created_at DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows as StolenCar[];\n}\n\nexport async function getStolenCarById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${STOLEN_CARS_TABLE} WHERE id = ?`, [id]);\n return result.rows[0] as StolenCar || null;\n}\n\nexport async function updateStolenCarStatus(id: number, status: 'active' | 'found' | 'resolved'): Promise {\n await sqlite.execute(`UPDATE ${STOLEN_CARS_TABLE} SET status = ? WHERE id = ?`, [status, id]);\n}\n\n// Found Cars Queries\nexport async function createFoundCarReport(car: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${FOUND_CARS_TABLE} (\n make, model, year, color, license_plate, description,\n found_date, found_location, found_lat, found_lng, photo_url,\n reporter_name, reporter_phone, reporter_email, status\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n car.make, car.model, car.year, car.color, car.license_plate, car.description,\n car.found_date, car.found_location, car.found_lat, car.found_lng, car.photo_url,\n car.reporter_name, car.reporter_phone, car.reporter_email, car.status\n ]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getFoundCars(filters?: SearchFilters): Promise {\n let query = `SELECT * FROM ${FOUND_CARS_TABLE} WHERE 1=1`;\n const params: any[] = [];\n\n if (filters?.make) {\n query += ` AND LOWER(make) LIKE LOWER(?)`;\n params.push(`%${filters.make}%`);\n }\n if (filters?.model) {\n query += ` AND LOWER(model) LIKE LOWER(?)`;\n params.push(`%${filters.model}%`);\n }\n if (filters?.color) {\n query += ` AND LOWER(color) LIKE LOWER(?)`;\n params.push(`%${filters.color}%`);\n }\n if (filters?.license_plate) {\n query += ` AND LOWER(license_plate) LIKE LOWER(?)`;\n params.push(`%${filters.license_plate}%`);\n }\n if (filters?.date_from) {\n query += ` AND found_date >= ?`;\n params.push(filters.date_from);\n }\n if (filters?.date_to) {\n query += ` AND found_date <= ?`;\n params.push(filters.date_to);\n }\n\n query += ` ORDER BY created_at DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows as FoundCar[];\n}\n\nexport async function getFoundCarById(id: number): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${FOUND_CARS_TABLE} WHERE id = ?`, [id]);\n return result.rows[0] as FoundCar || null;\n}\n\nexport async function updateFoundCarStatus(id: number, status: 'active' | 'matched' | 'resolved'): Promise {\n await sqlite.execute(`UPDATE ${FOUND_CARS_TABLE} SET status = ? WHERE id = ?`, [status, id]);\n}\n\n// Matching System\nexport async function findPotentialMatches(foundCarId: number): Promise {\n const foundCar = await getFoundCarById(foundCarId);\n if (!foundCar) return [];\n\n let query = `SELECT * FROM ${STOLEN_CARS_TABLE} WHERE status = 'active'`;\n const params: any[] = [];\n\n // Exact license plate match (highest priority)\n if (foundCar.license_plate) {\n query += ` AND license_plate = ?`;\n params.push(foundCar.license_plate);\n } else {\n // Match by make, model, color if no license plate\n const conditions: string[] = [];\n \n if (foundCar.make) {\n conditions.push(`LOWER(make) = LOWER(?)`);\n params.push(foundCar.make);\n }\n if (foundCar.model) {\n conditions.push(`LOWER(model) = LOWER(?)`);\n params.push(foundCar.model);\n }\n if (foundCar.color) {\n conditions.push(`LOWER(color) = LOWER(?)`);\n params.push(foundCar.color);\n }\n if (foundCar.year) {\n conditions.push(`year = ?`);\n params.push(foundCar.year);\n }\n\n if (conditions.length > 0) {\n query += ` AND (${conditions.join(' OR ')})`;\n }\n }\n\n const result = await sqlite.execute(query, params);\n return result.rows as StolenCar[];\n}\n\nexport async function createMatch(match: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${MATCHES_TABLE} (stolen_car_id, found_car_id, match_score, match_reasons, status)\n VALUES (?, ?, ?, ?, ?)\n `, [\n match.stolen_car_id, \n match.found_car_id, \n match.match_score, \n JSON.stringify(match.match_reasons), \n match.status\n ]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getMatches(stolenCarId?: number, foundCarId?: number): Promise {\n let query = `SELECT * FROM ${MATCHES_TABLE} WHERE 1=1`;\n const params: any[] = [];\n\n if (stolenCarId) {\n query += ` AND stolen_car_id = ?`;\n params.push(stolenCarId);\n }\n if (foundCarId) {\n query += ` AND found_car_id = ?`;\n params.push(foundCarId);\n }\n\n query += ` ORDER BY match_score DESC, created_at DESC`;\n\n const result = await sqlite.execute(query, params);\n return result.rows.map(row => ({\n ...row,\n match_reasons: JSON.parse(row.match_reasons as string)\n })) as CarMatch[];\n}\n\nexport async function updateMatchStatus(id: number, status: 'pending' | 'confirmed' | 'rejected'): Promise {\n await sqlite.execute(`UPDATE ${MATCHES_TABLE} SET status = ? WHERE id = ?`, [status, id]);\n}\n\n// Search across both tables\nexport async function searchAllCars(filters: SearchFilters) {\n const results = {\n stolen: [] as StolenCar[],\n found: [] as FoundCar[]\n };\n\n if (filters.type === 'stolen' || filters.type === 'all' || !filters.type) {\n results.stolen = await getStolenCars(filters);\n }\n\n if (filters.type === 'found' || filters.type === 'all' || !filters.type) {\n results.found = await getFoundCars(filters);\n }\n\n return results;\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/Saraxxx/FindMyCar/code/backend/database/queries.ts","extension","actionData","errors"]