Shipping Operations Dashboard

Loading Shipping Operations Dashboard...

\n \n \n\n",[],[146,208],"5cc7d3f2-3f84-11f0-861e-76b3cceeab13",["D",1748849817324],[],[146,32],["D",1748849809664],[178,179],[146],"9aeb3ecc-3f7e-11f0-a4ca-76b3cceeab13","index.ts",11,["D",1748847344593],"http","// Main Hono server for shipping operations\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { runMigrations, createTriggers } from \"./database/migrations.ts\";\nimport shipmentsRoutes from \"./routes/shipments.ts\";\nimport trackingRoutes from \"./routes/tracking.ts\";\nimport routesRoutes from \"./routes/routes.ts\";\nimport staticRoutes from \"./routes/static.ts\";\n\nconst app = new Hono();\n\n// Unwrap Hono errors to see original error details\napp.onError((err, c) => {\n throw err;\n});\n\n// Initialize database on startup\nlet dbInitialized = false;\nasync function initializeDatabase() {\n if (!dbInitialized) {\n console.log(\"Initializing database...\");\n await runMigrations();\n await createTriggers();\n dbInitialized = true;\n console.log(\"Database initialized successfully!\");\n }\n}\n\n// Middleware to ensure database is initialized\napp.use(\"*\", async (c, next) => {\n await initializeDatabase();\n await next();\n});\n\n// API routes\napp.route(\"/api/shipments\", shipmentsRoutes);\napp.route(\"/api/tracking\", trackingRoutes);\napp.route(\"/api/routes\", routesRoutes);\n\n// Static file serving and main app\napp.route(\"/\", staticRoutes);\n\n// Health check endpoint\napp.get(\"/health\", (c) => {\n return c.json({\n status: \"healthy\",\n timestamp: new Date().toISOString(),\n service: \"shipping-operations\"\n });\n});\n\n// 404 handler for API routes\napp.notFound((c) => {\n if (c.req.path.startsWith(\"/api/\")) {\n return c.json({\n success: false,\n error: \"Endpoint not found\"\n }, 404);\n }\n \n // For non-API routes, serve the main app (SPA routing)\n return c.redirect(\"/\");\n});\n\nexport default app;",[],[152,222],"77b642e4-3f7e-11f0-ba5b-76b3cceeab13","tracking.ts","77b64384-3f7e-11f0-ba5b-76b3cceeab13","02a7e29c-128e-49a2-9f5e-8a84b1e45a99",7,["D",1748847285475],"// Tracking and monitoring routes\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport {\n getShipmentByTracking,\n getTrackingEvents,\n createTrackingEvent,\n getDashboardStats\n} from \"../database/queries.ts\";\n\nconst app = new Hono();\n\n// Track shipment by tracking number\napp.get(\"/:trackingNumber\", async (c) => {\n try {\n const trackingNumber = c.req.param(\"trackingNumber\");\n const shipment = await getShipmentByTracking(trackingNumber);\n const events = await getTrackingEvents(shipment.id);\n \n return c.json({\n success: true,\n data: {\n shipment,\n events\n }\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, error.message === \"Shipment not found\" ? 404 : 500);\n }\n});\n\n// Get tracking events for a shipment\napp.get(\"/events/:shipmentId\", async (c) => {\n try {\n const shipmentId = c.req.param(\"shipmentId\");\n const events = await getTrackingEvents(shipmentId);\n \n return c.json({\n success: true,\n data: events\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Add tracking event (for internal use or carrier webhooks)\napp.post(\"/events\", async (c) => {\n try {\n const data = await c.req.json();\n \n if (!data.shipmentId || !data.status || !data.location || !data.description) {\n return c.json({\n success: false,\n error: \"Missing required fields\"\n }, 400);\n }\n \n const event = await createTrackingEvent(data);\n \n return c.json({\n success: true,\n data: event,\n message: \"Tracking event created successfully\"\n }, 201);\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Get dashboard statistics\napp.get(\"/dashboard/stats\", async (c) => {\n try {\n const stats = await getDashboardStats();\n \n return c.json({\n success: true,\n data: stats\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\nexport default app;",[],[152,238,230],"routes","90ad89ce-3f7e-11f0-8697-76b3cceeab13","static.ts","90ad8a8c-3f7e-11f0-8697-76b3cceeab13",9,["D",1748847327414],"// Static file serving routes\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\nimport { getDashboardStats } from \"../database/queries.ts\";\n\nconst app = new Hono();\n\n// Serve frontend files\napp.get(\"/frontend/*\", (c) => serveFile(c.req.path, import.meta.url));\napp.get(\"/shared/*\", (c) => serveFile(c.req.path, import.meta.url));\n\n// Serve main application with initial data\napp.get(\"/\", async (c) => {\n try {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Inject initial dashboard data to avoid extra round-trips\n const initialData = await getDashboardStats();\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n } catch (error) {\n return c.text(\"Error loading application\", 500);\n }\n});\n\nexport default app;",[],[152,238,240],"6fdba172-3f7e-11f0-9c46-76b3cceeab13","shipments.ts","6fdba21c-3f7e-11f0-9c46-76b3cceeab13",6,["D",1748847272347],"// Shipment management routes\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport type { CreateShipmentRequest, UpdateShipmentRequest } from \"../../shared/types.ts\";\nimport {\n createShipment,\n getShipments,\n getShipmentById,\n updateShipment,\n deleteShipment\n} from \"../database/queries.ts\";\n\nconst app = new Hono();\n\n// Get all shipments with pagination and filtering\napp.get(\"/\", async (c) => {\n try {\n const page = parseInt(c.req.query(\"page\") || \"1\");\n const limit = parseInt(c.req.query(\"limit\") || \"20\");\n const status = c.req.query(\"status\");\n \n const result = await getShipments(page, limit, status);\n \n return c.json({\n success: true,\n data: result\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Get shipment by ID\napp.get(\"/:id\", async (c) => {\n try {\n const id = c.req.param(\"id\");\n const shipment = await getShipmentById(id);\n \n return c.json({\n success: true,\n data: shipment\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, error.message === \"Shipment not found\" ? 404 : 500);\n }\n});\n\n// Create new shipment\napp.post(\"/\", async (c) => {\n try {\n const data: CreateShipmentRequest = await c.req.json();\n \n // Basic validation\n if (!data.origin || !data.destination || !data.sender || !data.recipient || !data.packages?.length) {\n return c.json({\n success: false,\n error: \"Missing required fields\"\n }, 400);\n }\n \n const shipment = await createShipment(data);\n \n return c.json({\n success: true,\n data: shipment,\n message: \"Shipment created successfully\"\n }, 201);\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Update shipment\napp.put(\"/:id\", async (c) => {\n try {\n const id = c.req.param(\"id\");\n const data: UpdateShipmentRequest = await c.req.json();\n \n const shipment = await updateShipment(id, data);\n \n return c.json({\n success: true,\n data: shipment,\n message: \"Shipment updated successfully\"\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, error.message === \"Shipment not found\" ? 404 : 500);\n }\n});\n\n// Delete shipment\napp.delete(\"/:id\", async (c) => {\n try {\n const id = c.req.param(\"id\");\n await deleteShipment(id);\n \n return c.json({\n success: true,\n message: \"Shipment deleted successfully\"\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\nexport default app;",[],[152,238,248],"8a9ea3f6-3f7e-11f0-8b33-76b3cceeab13","routes.ts","8a9ea4aa-3f7e-11f0-8b33-76b3cceeab13",8,["D",1748847317251],"// Route optimization and management\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport type { RouteOptimizationRequest, Address } from \"../../shared/types.ts\";\nimport { getShipmentById } from \"../database/queries.ts\";\nimport { calculateDistance } from \"../../shared/utils.ts\";\n\nconst app = new Hono();\n\n// Optimize delivery route\napp.post(\"/optimize\", async (c) => {\n try {\n const data: RouteOptimizationRequest = await c.req.json();\n \n if (!data.shipmentIds || data.shipmentIds.length === 0) {\n return c.json({\n success: false,\n error: \"No shipments provided\"\n }, 400);\n }\n \n // Get shipment details\n const shipments = await Promise.all(\n data.shipmentIds.map(id => getShipmentById(id))\n );\n \n // Extract destinations\n const destinations = shipments.map(s => s.destination);\n \n // Simple route optimization using nearest neighbor algorithm\n const optimizedRoute = optimizeRoute(destinations, data.startLocation);\n \n // Calculate total distance and estimated time\n const totalDistance = calculateTotalDistance(optimizedRoute);\n const estimatedDuration = Math.round(totalDistance * 2); // Rough estimate: 2 minutes per mile\n \n return c.json({\n success: true,\n data: {\n route: optimizedRoute,\n shipmentIds: data.shipmentIds,\n totalDistance,\n estimatedDuration,\n optimized: true\n }\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Get available carriers and their services\napp.get(\"/carriers\", async (c) => {\n try {\n const carriers = [\n {\n id: \"ups\",\n name: \"UPS\",\n services: [\"standard\", \"expedited\", \"overnight\", \"two_day\", \"ground\"],\n trackingUrlTemplate: \"https://www.ups.com/track?tracknum={trackingNumber}\"\n },\n {\n id: \"fedex\",\n name: \"FedEx\",\n services: [\"standard\", \"expedited\", \"overnight\", \"two_day\", \"ground\"],\n trackingUrlTemplate: \"https://www.fedex.com/apps/fedextrack/?tracknumbers={trackingNumber}\"\n },\n {\n id: \"usps\",\n name: \"USPS\",\n services: [\"standard\", \"expedited\", \"ground\"],\n trackingUrlTemplate: \"https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1={trackingNumber}\"\n },\n {\n id: \"dhl\",\n name: \"DHL\",\n services: [\"standard\", \"expedited\", \"overnight\"],\n trackingUrlTemplate: \"https://www.dhl.com/en/express/tracking.html?AWB={trackingNumber}\"\n },\n {\n id: \"local\",\n name: \"Local Delivery\",\n services: [\"standard\", \"expedited\"],\n trackingUrlTemplate: \"\"\n }\n ];\n \n return c.json({\n success: true,\n data: carriers\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Calculate shipping rates (mock implementation)\napp.post(\"/rates\", async (c) => {\n try {\n const { origin, destination, packages, serviceTypes } = await c.req.json();\n \n if (!origin || !destination || !packages) {\n return c.json({\n success: false,\n error: \"Missing required fields\"\n }, 400);\n }\n \n // Mock rate calculation\n const distance = calculateDistance(origin, destination);\n const totalWeight = packages.reduce((sum: number, pkg: any) => sum + pkg.weight, 0);\n \n const baseRates = {\n ups: { standard: 15.99, expedited: 25.99, overnight: 45.99, two_day: 35.99, ground: 12.99 },\n fedex: { standard: 16.99, expedited: 26.99, overnight: 47.99, two_day: 36.99, ground: 13.99 },\n usps: { standard: 12.99, expedited: 22.99, ground: 9.99 },\n dhl: { standard: 18.99, expedited: 28.99, overnight: 49.99 },\n local: { standard: 8.99, expedited: 15.99 }\n };\n \n const rates = [];\n \n for (const [carrier, services] of Object.entries(baseRates)) {\n for (const [service, baseRate] of Object.entries(services)) {\n if (!serviceTypes || serviceTypes.includes(service)) {\n const weightMultiplier = 1 + (totalWeight * 0.1);\n const distanceMultiplier = 1 + (distance * 0.001);\n const finalRate = Math.round(baseRate * weightMultiplier * distanceMultiplier * 100) / 100;\n \n rates.push({\n carrier,\n serviceType: service,\n cost: finalRate,\n estimatedDays: getEstimatedDays(service, distance),\n guaranteed: service === \"overnight\" || service === \"two_day\"\n });\n }\n }\n }\n \n return c.json({\n success: true,\n data: rates.sort((a, b) => a.cost - b.cost)\n });\n } catch (error) {\n return c.json({\n success: false,\n error: error.message\n }, 500);\n }\n});\n\n// Helper functions\nfunction optimizeRoute(destinations: Address[], startLocation?: Address): Address[] {\n if (destinations.length <= 1) return destinations;\n \n const unvisited = [...destinations];\n const route: Address[] = [];\n let current = startLocation || destinations[0];\n \n if (startLocation) {\n // Remove the closest destination to start location from unvisited\n let closestIndex = 0;\n let closestDistance = Infinity;\n \n unvisited.forEach((dest, index) => {\n const distance = calculateDistance(current, dest);\n if (distance < closestDistance) {\n closestDistance = distance;\n closestIndex = index;\n }\n });\n \n route.push(unvisited.splice(closestIndex, 1)[0]);\n current = route[0];\n } else {\n route.push(unvisited.shift()!);\n }\n \n // Nearest neighbor algorithm\n while (unvisited.length > 0) {\n let nearestIndex = 0;\n let nearestDistance = Infinity;\n \n unvisited.forEach((dest, index) => {\n const distance = calculateDistance(current, dest);\n if (distance < nearestDistance) {\n nearestDistance = distance;\n nearestIndex = index;\n }\n });\n \n const nearest = unvisited.splice(nearestIndex, 1)[0];\n route.push(nearest);\n current = nearest;\n }\n \n return route;\n}\n\nfunction calculateTotalDistance(route: Address[]): number {\n if (route.length < 2) return 0;\n \n let total = 0;\n for (let i = 0; i < route.length - 1; i++) {\n total += calculateDistance(route[i], route[i + 1]);\n }\n \n return Math.round(total * 100) / 100;\n}\n\nfunction getEstimatedDays(serviceType: string, distance: number): number {\n const baseDays = {\n overnight: 1,\n two_day: 2,\n expedited: 3,\n standard: 5,\n ground: 7\n }[serviceType] || 5;\n \n // Add extra days for long distances\n const extraDays = distance > 1000 ? 2 : distance > 500 ? 1 : 0;\n \n return baseDays + extraDays;\n}\n\nexport default app;",[],[152,238,256],"6fdbaabe-3f7e-11f0-9c46-76b3cceeab13",["D",1748847272347],[172,173,174,175],[152,238],"66cb979a-3f7e-11f0-b6f5-76b3cceeab13","queries.ts","66cb9862-3f7e-11f0-b6f5-76b3cceeab13","cc56f0c5-e260-4415-ae29-9b1f2263e12b",5,["D",1748847257146],"// 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}",[],[152,276,268],"database","4cee0312-3f7e-11f0-95cc-76b3cceeab13","migrations.ts","4cee03d0-3f7e-11f0-95cc-76b3cceeab13",["D",1748847213749],"// Database schema setup for shipping operations\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nexport async function runMigrations() {\n console.log(\"Running database migrations...\");\n\n // Shipments table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS shipments (\n id TEXT PRIMARY KEY,\n tracking_number TEXT UNIQUE NOT NULL,\n carrier TEXT NOT NULL,\n service_type TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT 'pending',\n \n -- Origin address\n origin_street TEXT NOT NULL,\n origin_city TEXT NOT NULL,\n origin_state TEXT NOT NULL,\n origin_zip_code TEXT NOT NULL,\n origin_country TEXT NOT NULL DEFAULT 'US',\n origin_latitude REAL,\n origin_longitude REAL,\n \n -- Destination address\n dest_street TEXT NOT NULL,\n dest_city TEXT NOT NULL,\n dest_state TEXT NOT NULL,\n dest_zip_code TEXT NOT NULL,\n dest_country TEXT NOT NULL DEFAULT 'US',\n dest_latitude REAL,\n dest_longitude REAL,\n \n -- Sender contact\n sender_name TEXT NOT NULL,\n sender_phone TEXT NOT NULL,\n sender_email TEXT NOT NULL,\n \n -- Recipient contact\n recipient_name TEXT NOT NULL,\n recipient_phone TEXT NOT NULL,\n recipient_email TEXT NOT NULL,\n \n -- Shipping details\n estimated_delivery TEXT NOT NULL,\n actual_delivery TEXT,\n cost REAL NOT NULL,\n \n -- Additional fields\n notes TEXT,\n signature TEXT,\n proof_of_delivery TEXT,\n \n -- Timestamps\n created_at TEXT NOT NULL DEFAULT (datetime('now')),\n updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n )\n `);\n\n // Packages table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS packages (\n id TEXT PRIMARY KEY,\n shipment_id TEXT NOT NULL,\n weight REAL NOT NULL,\n length REAL NOT NULL,\n width REAL NOT NULL,\n height REAL NOT NULL,\n value REAL NOT NULL,\n description TEXT NOT NULL,\n fragile INTEGER NOT NULL DEFAULT 0,\n hazardous INTEGER NOT NULL DEFAULT 0,\n created_at TEXT NOT NULL DEFAULT (datetime('now')),\n FOREIGN KEY (shipment_id) REFERENCES shipments (id) ON DELETE CASCADE\n )\n `);\n\n // Tracking events table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS tracking_events (\n id TEXT PRIMARY KEY,\n shipment_id TEXT NOT NULL,\n status TEXT NOT NULL,\n location TEXT NOT NULL,\n timestamp TEXT NOT NULL,\n description TEXT NOT NULL,\n scan_type TEXT,\n created_at TEXT NOT NULL DEFAULT (datetime('now')),\n FOREIGN KEY (shipment_id) REFERENCES shipments (id) ON DELETE CASCADE\n )\n `);\n\n // Routes table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS routes (\n id TEXT PRIMARY KEY,\n driver_id TEXT,\n estimated_distance REAL NOT NULL,\n estimated_duration INTEGER NOT NULL,\n optimized INTEGER NOT NULL DEFAULT 0,\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n )\n `);\n\n // Route shipments junction table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS route_shipments (\n route_id TEXT NOT NULL,\n shipment_id TEXT NOT NULL,\n stop_order INTEGER NOT NULL,\n PRIMARY KEY (route_id, shipment_id),\n FOREIGN KEY (route_id) REFERENCES routes (id) ON DELETE CASCADE,\n FOREIGN KEY (shipment_id) REFERENCES shipments (id) ON DELETE CASCADE\n )\n `);\n\n // Create indexes for better performance\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_shipments_tracking ON shipments (tracking_number)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_shipments_status ON shipments (status)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_shipments_carrier ON shipments (carrier)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_shipments_created ON shipments (created_at)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_packages_shipment ON packages (shipment_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_tracking_shipment ON tracking_events (shipment_id)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_tracking_timestamp ON tracking_events (timestamp)`);\n\n console.log(\"Database migrations completed successfully!\");\n}\n\n// Trigger to update updated_at timestamp\nexport async function createTriggers() {\n await sqlite.execute(`\n CREATE TRIGGER IF NOT EXISTS update_shipments_timestamp \n AFTER UPDATE ON shipments\n BEGIN\n UPDATE shipments SET updated_at = datetime('now') WHERE id = NEW.id;\n END\n `);\n}",[],[152,276,278],"4cee0ea2-3f7e-11f0-95cc-76b3cceeab13",["D",1748847213749],[169,170],[152,276],["D",1748847213749],[168,171,176],[152],["D",1748847110812],{"_94":93,"_101":97},{"_94":93,"_95":-5,"_96":97,"_18":52,"_98":99,"_100":24},{"_13":83,"_31":84,"_41":40,"_85":42,"_86":-5,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":43,"_92":93},"ctx",{"_76":-5,"_121":328,"_329":330,"_331":332,"_333":24,"_334":24,"_335":24,"_336":56,"_337":-5,"_338":-7,"_339":-7,"_340":60,"_341":24,"_342":24,"_343":-5,"_344":24,"_345":24,"_346":24,"_347":-5},"ENV",{"_299":300,"_301":302,"_303":84,"_304":305,"_306":307,"_308":309,"_310":311,"_312":313,"_314":315,"_316":317,"_318":319,"_320":321,"_322":323,"_324":325,"_326":327},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","71c1b20804b3232a7e513be260cfffc1932a9e97","RENDER_GIT_BRANCH","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","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v13