[{"_1":2,"_174":-5,"_175":-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","a31a1550ad486cf340b818a53bc6ddc8c7db0295","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,"_149":150,"_171":172,"_173":-5},"handle","Shsragab","name","Shippingrevolution","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":115,"_116":115,"_117":-5,"_118":32,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","0f920658-3f7e-11f0-93ae-76b3cceeab13","createdAt",["D",1748847110812],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","c66a6a10-3f7d-11f0-ad5b-814c56c8abb0","pinnedFileId","979257ec-3f7e-11f0-8c34-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","12230700-6a44-48a8-ac0c-c49257130cf6","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4d1I0bzBOOEs3T2xJVTZ3ZUJNZE9kU2kzYyJ9","org",{"_93":94,"_134":131,"_12":111},"avatar","branch",{"_137":138,"_95":43,"_101":139,"_140":102,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":96,"_147":94},"version",18,"0f925e00-3f7e-11f0-93ae-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":115,"_103":161,"_137":32,"_162":163,"_164":165,"_166":20,"_167":-5,"_97":168},"66cb979a-3f7e-11f0-b6f5-76b3cceeab13","queries.ts","fileId","66cb9862-3f7e-11f0-b6f5-76b3cceeab13","parentId","cc56f0c5-e260-4415-ae29-9b1f2263e12b","branchId","branchVersion",5,"latest",["D",1748847257146],"fileType","script","content","// Database query functions for shipping operations\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n Shipment, \n Package, \n TrackingEvent, \n Route, \n DashboardStats,\n CreateShipmentRequest,\n UpdateShipmentRequest,\n PaginatedResponse\n} from \"../../shared/types.ts\";\nimport { generateId, generateTrackingNumber } from \"../../shared/utils.ts\";\n\n// Shipment queries\nexport async function createShipment(data: CreateShipmentRequest): Promise {\n const shipmentId = generateId();\n const trackingNumber = generateTrackingNumber(data.carrier);\n const now = new Date().toISOString();\n \n // Calculate estimated delivery (simplified)\n const estimatedDelivery = new Date();\n estimatedDelivery.setDate(estimatedDelivery.getDate() + 5);\n \n // Insert shipment\n await sqlite.execute(`\n INSERT INTO shipments (\n id, tracking_number, carrier, service_type, status,\n origin_street, origin_city, origin_state, origin_zip_code, origin_country,\n origin_latitude, origin_longitude,\n dest_street, dest_city, dest_state, dest_zip_code, dest_country,\n dest_latitude, dest_longitude,\n sender_name, sender_phone, sender_email,\n recipient_name, recipient_phone, recipient_email,\n estimated_delivery, cost, notes, created_at, updated_at\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n shipmentId, trackingNumber, data.carrier, data.serviceType, 'pending',\n data.origin.street, data.origin.city, data.origin.state, data.origin.zipCode, data.origin.country,\n data.origin.latitude, data.origin.longitude,\n data.destination.street, data.destination.city, data.destination.state, data.destination.zipCode, data.destination.country,\n data.destination.latitude, data.destination.longitude,\n data.sender.name, data.sender.phone, data.sender.email,\n data.recipient.name, data.recipient.phone, data.recipient.email,\n estimatedDelivery.toISOString(), 25.99, data.notes || null, now, now\n ]);\n\n // Insert packages\n for (const pkg of data.packages) {\n const packageId = generateId();\n await sqlite.execute(`\n INSERT INTO packages (\n id, shipment_id, weight, length, width, height, value, description, fragile, hazardous\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n packageId, shipmentId, pkg.weight, pkg.dimensions.length, pkg.dimensions.width, \n pkg.dimensions.height, pkg.value, pkg.description, pkg.fragile ? 1 : 0, pkg.hazardous ? 1 : 0\n ]);\n }\n\n // Create initial tracking event\n await createTrackingEvent({\n shipmentId,\n status: 'pending',\n location: `${data.origin.city}, ${data.origin.state}`,\n description: 'Shipment created and pending pickup',\n scanType: 'created'\n });\n\n return getShipmentById(shipmentId);\n}\n\nexport async function getShipments(page = 1, limit = 20, status?: string): Promise> {\n const offset = (page - 1) * limit;\n let whereClause = '';\n let params: any[] = [];\n \n if (status) {\n whereClause = 'WHERE status = ?';\n params.push(status);\n }\n \n const countResult = await sqlite.execute(`SELECT COUNT(*) as count FROM shipments ${whereClause}`, params);\n const total = countResult[0]?.count || 0;\n \n const shipmentsResult = await sqlite.execute(`\n SELECT * FROM shipments ${whereClause} \n ORDER BY created_at DESC \n LIMIT ? OFFSET ?\n `, [...params, limit, offset]);\n \n const shipments = await Promise.all(\n shipmentsResult.map(row => buildShipmentFromRow(row))\n );\n \n return {\n data: shipments,\n total,\n page,\n limit,\n hasMore: offset + limit < total\n };\n}\n\nexport async function getShipmentById(id: string): Promise {\n const result = await sqlite.execute('SELECT * FROM shipments WHERE id = ?', [id]);\n if (!result.length) {\n throw new Error('Shipment not found');\n }\n \n return buildShipmentFromRow(result[0]);\n}\n\nexport async function getShipmentByTracking(trackingNumber: string): Promise {\n const result = await sqlite.execute('SELECT * FROM shipments WHERE tracking_number = ?', [trackingNumber]);\n if (!result.length) {\n throw new Error('Shipment not found');\n }\n \n return buildShipmentFromRow(result[0]);\n}\n\nexport async function updateShipment(id: string, data: UpdateShipmentRequest): Promise {\n const updates: string[] = [];\n const params: any[] = [];\n \n if (data.status) {\n updates.push('status = ?');\n params.push(data.status);\n }\n \n if (data.estimatedDelivery) {\n updates.push('estimated_delivery = ?');\n params.push(data.estimatedDelivery);\n }\n \n if (data.actualDelivery) {\n updates.push('actual_delivery = ?');\n params.push(data.actualDelivery);\n }\n \n if (data.notes) {\n updates.push('notes = ?');\n params.push(data.notes);\n }\n \n if (data.signature) {\n updates.push('signature = ?');\n params.push(data.signature);\n }\n \n if (data.proofOfDelivery) {\n updates.push('proof_of_delivery = ?');\n params.push(data.proofOfDelivery);\n }\n \n if (updates.length === 0) {\n return getShipmentById(id);\n }\n \n params.push(id);\n \n await sqlite.execute(`\n UPDATE shipments \n SET ${updates.join(', ')}, updated_at = datetime('now')\n WHERE id = ?\n `, params);\n \n // If status was updated, create tracking event\n if (data.status) {\n const shipment = await getShipmentById(id);\n await createTrackingEvent({\n shipmentId: id,\n status: data.status,\n location: `${shipment.destination.city}, ${shipment.destination.state}`,\n description: `Status updated to ${data.status}`,\n scanType: 'status_update'\n });\n }\n \n return getShipmentById(id);\n}\n\nexport async function deleteShipment(id: string): Promise {\n await sqlite.execute('DELETE FROM shipments WHERE id = ?', [id]);\n}\n\n// Package queries\nexport async function getPackagesByShipment(shipmentId: string): Promise {\n const result = await sqlite.execute('SELECT * FROM packages WHERE shipment_id = ?', [shipmentId]);\n \n return result.map(row => ({\n id: row.id,\n weight: row.weight,\n dimensions: {\n length: row.length,\n width: row.width,\n height: row.height\n },\n value: row.value,\n description: row.description,\n fragile: Boolean(row.fragile),\n hazardous: Boolean(row.hazardous)\n }));\n}\n\n// Tracking event queries\nexport async function createTrackingEvent(data: {\n shipmentId: string;\n status: string;\n location: string;\n description: string;\n scanType?: string;\n}): Promise {\n const id = generateId();\n const timestamp = new Date().toISOString();\n \n await sqlite.execute(`\n INSERT INTO tracking_events (id, shipment_id, status, location, timestamp, description, scan_type)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [id, data.shipmentId, data.status, data.location, timestamp, data.description, data.scanType]);\n \n return {\n id,\n shipmentId: data.shipmentId,\n status: data.status as any,\n location: data.location,\n timestamp,\n description: data.description,\n scanType: data.scanType\n };\n}\n\nexport async function getTrackingEvents(shipmentId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM tracking_events \n WHERE shipment_id = ? \n ORDER BY timestamp DESC\n `, [shipmentId]);\n \n return result.map(row => ({\n id: row.id,\n shipmentId: row.shipment_id,\n status: row.status,\n location: row.location,\n timestamp: row.timestamp,\n description: row.description,\n scanType: row.scan_type\n }));\n}\n\n// Dashboard queries\nexport async function getDashboardStats(): Promise {\n const today = new Date().toISOString().split('T')[0];\n \n const [totalResult, pendingResult, inTransitResult, deliveredTodayResult, revenueResult] = await Promise.all([\n sqlite.execute('SELECT COUNT(*) as count FROM shipments'),\n sqlite.execute('SELECT COUNT(*) as count FROM shipments WHERE status = ?', ['pending']),\n sqlite.execute('SELECT COUNT(*) as count FROM shipments WHERE status = ?', ['in_transit']),\n sqlite.execute('SELECT COUNT(*) as count FROM shipments WHERE DATE(actual_delivery) = ?', [today]),\n sqlite.execute('SELECT SUM(cost) as total FROM shipments WHERE status = ?', ['delivered'])\n ]);\n \n return {\n totalShipments: totalResult[0]?.count || 0,\n pendingShipments: pendingResult[0]?.count || 0,\n inTransitShipments: inTransitResult[0]?.count || 0,\n deliveredToday: deliveredTodayResult[0]?.count || 0,\n totalRevenue: revenueResult[0]?.total || 0,\n averageDeliveryTime: 48 // Placeholder - would need more complex query\n };\n}\n\n// Helper function to build shipment object from database row\nasync function buildShipmentFromRow(row: any): Promise {\n const packages = await getPackagesByShipment(row.id);\n \n return {\n id: row.id,\n trackingNumber: row.tracking_number,\n carrier: row.carrier,\n serviceType: row.service_type,\n status: row.status,\n origin: {\n street: row.origin_street,\n city: row.origin_city,\n state: row.origin_state,\n zipCode: row.origin_zip_code,\n country: row.origin_country,\n latitude: row.origin_latitude,\n longitude: row.origin_longitude\n },\n destination: {\n street: row.dest_street,\n city: row.dest_city,\n state: row.dest_state,\n zipCode: row.dest_zip_code,\n country: row.dest_country,\n latitude: row.dest_latitude,\n longitude: row.dest_longitude\n },\n sender: {\n name: row.sender_name,\n phone: row.sender_phone,\n email: row.sender_email\n },\n recipient: {\n name: row.recipient_name,\n phone: row.recipient_phone,\n email: row.recipient_email\n },\n packages,\n estimatedDelivery: row.estimated_delivery,\n actualDelivery: row.actual_delivery,\n cost: row.cost,\n createdAt: row.created_at,\n updatedAt: row.updated_at,\n notes: row.notes,\n signature: row.signature,\n proofOfDelivery: row.proof_of_delivery\n };\n}","deleted","label",[169,170,152],"backend","database","permalink","https://www.val.town/x/Shsragab/Shippingrevolution/code/backend/database/queries.ts","extension","actionData","errors"]