[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_182":183,"_184":185},"routes/_app.x.$handle.$name",{"_95":94,"_31":43,"_104":85,"_26":105,"_13":14,"_106":-5,"_11":107,"_108":109,"_110":111,"_112":113,"_114":115,"_20":-5,"_116":-5,"_117":-5,"_118":24,"_119":120,"_121":57,"_122":-5,"_123":-5,"_124":125},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_41":42,"_31":43,"_36":44,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":51,"_52":-5,"_18":53,"_54":55,"_56":57,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":78,"_79":80,"_81":82,"_83":24},"version",430,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":27,"_32":33,"_34":35,"_36":37,"_38":14,"_39":40,"_23":24},"previous","deleted",false,"deletedHere","path","worker.ts","latestPath","fileId","addbdfce-9693-11f1-9f88-1607ee4eb77e","name","fileType","interval","content","// Drains the route queue.\n//\n// Why this exists: routing a full week for a large region takes ~68s (302 stops\n// is a ~91,000-pair distance matrix) and Val Town kills an HTTP request well\n// before that, so the map cannot build these itself. It queues a job instead\n// and this picks it up on the interval, where there is budget to finish.\n//\n// One job per tick, deliberately. Two 68-second builds in a single interval\n// run would risk running past the tick and leave both half-written; the next\n// tick is only a minute away.\nimport build, {\n loadQueue,\n type QueueJob,\n saveQueue,\n STALE_JOB_MS,\n} from \"./main.ts\";\n\nexport default async function () {\n const queue = await loadQueue();\n\n // Reclaim anything that claims to be running but was killed mid-build, or the\n // queue wedges on a job nothing is working on.\n let dirty = false;\n for (const [key, job] of Object.entries(queue)) {\n if (job.status !== \"running\") continue;\n const age = Date.now() - new Date(job.startedAt ?? job.requestedAt).getTime();\n if (age > STALE_JOB_MS) {\n queue[key] = {\n ...job,\n status: \"error\",\n error: `Build was interrupted after ${Math.round(age / 1000)}s and did not finish.`,\n finishedAt: new Date().toISOString(),\n };\n dirty = true;\n console.log(`Reclaimed stale job ${key}`);\n }\n }\n\n // Never start a second build while one is genuinely in flight - they compete\n // for the same geocode cache and the same snapshot blob.\n const inFlight = Object.values(queue).find((j) => j.status === \"running\");\n if (inFlight) {\n if (dirty) await saveQueue(queue);\n console.log(`Busy: ${inFlight.region} week ${inFlight.weekOffset} is still building`);\n return;\n }\n\n const entry = Object.entries(queue)\n .filter(([, j]) => j.status === \"queued\")\n .sort((a, b) => a[1].requestedAt.localeCompare(b[1].requestedAt))[0];\n if (!entry) {\n if (dirty) await saveQueue(queue);\n return;\n }\n\n const [key, job] = entry;\n const started = Date.now();\n queue[key] = { ...job, status: \"running\", startedAt: new Date().toISOString() };\n await saveQueue(queue);\n console.log(`Routing ${job.region} week ${job.weekOffset}...`);\n\n let finished: QueueJob;\n try {\n const out = await build({\n region: job.region,\n weekOffset: job.weekOffset,\n // Next week is a plan, not a dispatch: it must not overwrite the tab the\n // drivers are working from this week. Only week 0 writes the sheet.\n writeSheet: job.weekOffset === 0,\n });\n finished = {\n ...queue[key],\n status: \"done\",\n finishedAt: new Date().toISOString(),\n stops: out.totalStops ?? 0,\n routes: (out.routes ?? []).length,\n unassigned: out.unassignedCount ?? 0,\n };\n console.log(\n `Routed ${job.region} week ${job.weekOffset}: ${finished.stops} stops, ` +\n `${finished.routes} routes, ${finished.unassigned} unassigned in ` +\n `${((Date.now() - started) / 1000).toFixed(1)}s`,\n );\n } catch (err) {\n finished = {\n ...queue[key],\n status: \"error\",\n finishedAt: new Date().toISOString(),\n error: String(err).slice(0, 300),\n };\n console.log(`Routing ${job.region} week ${job.weekOffset} failed: ${String(err)}`);\n }\n\n // Re-read: the queue may have gained a job while this one was building, and\n // writing the stale copy back would silently drop it.\n const latest = await loadQueue();\n latest[key] = finished;\n await saveQueue(latest);\n}\n","createdAt",["D",1786569644611],"branchVersion","branchId","fcce0313-0df2-4398-9e5c-8a90515f9c8a","id","7ff6addc-d421-4037-b4cd-7b7685c3dab2","tribeca-route-builder",["D",1785443670415],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","DC-area route builder: Salesforce -> Google Sheet","imageUrl","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","10f0e8ae-8c56-11f1-94f0-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019f8fe2-cdfc-775d-a845-a69af13d39cb","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_95":94,"_103":99},"org",{"_95":94,"_96":97,"_98":99,"_18":53,"_100":101,"_102":24},"branch",{"_13":84,"_31":85,"_41":40,"_86":42,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":43,"_93":94},"isOwned",1437,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","Tribeca_Rep","handle","displayName","M A","avatar","https://lh3.googleusercontent.com/a/ACg8ocI9znhXn2hWp1wjjyWzeuOyoO_JrDgJo8aD0umFraR7bAG3UAmc=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_41":42,"_31":43,"_36":178,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":51,"_52":-5,"_18":53,"_54":55,"_56":57,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":179,"_79":180,"_81":181,"_83":24},"tree",[165,166,167],"files",[165,166,167],"counts",{"_153":61,"_154":155,"_156":61,"_157":158,"_159":160},"contents",[126,127,128],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_41":145,"_31":146,"_29":55,"_130":-5,"_39":40,"_38":147,"_131":24,"_36":148,"_13":149,"_32":150,"_34":151,"_23":24,"_133":-5,"_26":152},{"_41":135,"_31":136,"_29":137,"_130":-5,"_39":40,"_38":138,"_131":24,"_36":139,"_13":140,"_32":141,"_34":142,"_23":24,"_133":143,"_26":144},{"_41":129,"_31":27,"_29":30,"_130":-5,"_39":40,"_38":14,"_131":57,"_36":132,"_13":61,"_32":33,"_34":35,"_23":24,"_133":-5,"_26":134},"8cd08fe4-e5b1-4488-8a86-9f854368740e","parentId","latest",["D",1786569644611],"label",[27],"60fdca4b-9d2f-47da-ad2c-1e7b12ef1c20","map.ts","84a05328-95bd-11f1-9462-1607ee4eb77e",426,["D",1786568909148],133,"http","// Visual map of the last route build: every geocoded stop, shown either as a\n// raw cloud of dots (\"Before\") or coloured by assigned day with the actual\n// visit order drawn as lines (\"After\").\n//\n// Reads the snapshot main.ts writes on each run - it does not recompute\n// anything, so the page loads instantly and always shows exactly what was\n// written to the sheet.\n\nimport { blob } from \"https://esm.town/v/std/blob/main.ts\";\nimport buildDcRoutes, {\n aggregateProducts,\n blobKeyFor,\n getAccessToken,\n getRegion,\n todaysStops,\n type Region,\n REGIONS,\n type MapSnapshot,\n type Overrides,\n OVERRIDES_KEY,\n SNAPSHOT_KEY,\n snapshotKeyFor,\n writeRoutesToSheet,\n} from \"./main.ts\";\n\nconst html = (body: string, headers: Record = {}) =>\n new Response(body, { headers: { \"Content-Type\": \"text/html; charset=utf-8\", ...headers } });\n\n// ----------------------------------------------------------------------------\n// Storage resilience\n// ----------------------------------------------------------------------------\n//\n// Val Town's blob service occasionally drops a connection mid-request\n// (\"connection closed before message completed\"). Unretried, that surfaced as\n// a raw Deno stack trace - ugly, and it leaked internal service URLs. These\n// reads are idempotent, so a couple of quick retries clear it.\n\nconst napMs = (ms: number) => new Promise((r) => setTimeout(r, ms));\n\nexport async function withRetry(what: string, fn: () => Promise, attempts = 3): Promise {\n let lastError: unknown;\n for (let attempt = 1; attempt <= attempts; attempt++) {\n try {\n return await fn();\n } catch (err) {\n lastError = err;\n console.log(`${what} failed (attempt ${attempt}/${attempts}): ${String(err).slice(0, 140)}`);\n if (attempt < attempts) await napMs(250 * attempt);\n }\n }\n throw lastError;\n}\n\nconst readSnapshot = (region: Region, week = 0) =>\n withRetry(\"snapshot read\", async () =>\n await blob.getJSON(snapshotKeyFor(region.key, week)) as MapSnapshot | undefined);\n\nconst readOverrides = (region: Region) =>\n blob.getJSON(blobKeyFor(OVERRIDES_KEY, region.key)) as Promise;\n\nconst writeOverrides = (region: Region, value: Overrides) =>\n blob.setJSON(blobKeyFor(OVERRIDES_KEY, region.key), value);\n\nconst writeSnapshot = (region: Region, snap: MapSnapshot) =>\n blob.setJSON(snapshotKeyFor(region.key, snap.weekOffset ?? 0), snap);\n\n// Shown instead of a stack trace when storage is genuinely unavailable.\nfunction storageErrorPage(): Response {\n return new Response(\n `\n \n Tribeca DMV routes\n

Temporarily unavailable

Val Town's storage did not respond after three attempts. Nothing is lost -\n the routes are still saved. This usually clears within a few seconds.

\n \n

`,\n {\n status: 503,\n headers: {\n \"Content-Type\": \"text/html; charset=utf-8\",\n \"Cache-Control\": \"no-store\",\n \"Retry-After\": \"5\",\n },\n },\n );\n}\n\n// ----------------------------------------------------------------------------\n// Access gate\n// ----------------------------------------------------------------------------\n//\n// Val Town's own restricted-HTTP setting is not enabled for this org, so the\n// map guards itself with a shared secret. Visit once with ?key=... and the key\n// is stored in an HttpOnly cookie; every later request (including the POST\n// endpoints that rewrite routes and the sheet) is checked against it.\n//\n// Fails closed: if MAP_ACCESS_KEY is missing from the environment nothing is\n// served, so a misconfiguration can never silently expose customer addresses.\n\nconst COOKIE = \"rb_access\";\n\n// Constant-time-ish compare, to avoid leaking the key length/prefix by timing.\nfunction secretsMatch(a: string, b: string): boolean {\n if (a.length !== b.length) return false;\n let diff = 0;\n for (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);\n return diff === 0;\n}\n\nfunction readCookie(req: Request, name: string): string | null {\n const raw = req.headers.get(\"cookie\") ?? \"\";\n for (const part of raw.split(\";\")) {\n const [k, ...rest] = part.trim().split(\"=\");\n if (k === name) return decodeURIComponent(rest.join(\"=\"));\n }\n return null;\n}\n\ntype Gate =\n | { ok: true; setCookie?: string }\n | { ok: false; reason: \"unconfigured\" | \"denied\" };\n\nfunction checkAccess(req: Request): Gate {\n const expected = Deno.env.get(\"MAP_ACCESS_KEY\");\n if (!expected) return { ok: false, reason: \"unconfigured\" };\n\n const supplied = new URL(req.url).searchParams.get(\"key\");\n if (supplied && secretsMatch(supplied, expected)) {\n return {\n ok: true,\n // 30 days. HttpOnly so page scripts can't read it back out.\n setCookie: `${COOKIE}=${encodeURIComponent(expected)}; Path=/; Max-Age=2592000; ` +\n `HttpOnly; Secure; SameSite=Lax`,\n };\n }\n\n const cookie = readCookie(req, COOKIE);\n if (cookie && secretsMatch(cookie, expected)) return { ok: true };\n\n return { ok: false, reason: \"denied\" };\n}\n\n// A password prompt rather than a \"put ?key= in the URL\" instruction: the key\n// then never appears in the address bar, browser history or referrer headers.\nfunction deniedPage(reason: \"unconfigured\" | \"denied\"): Response {\n const body = reason === \"unconfigured\"\n ? `

Not configured

MAP_ACCESS_KEY is not set on this val, so the map is closed. Set it in the\n val's environment variables and reload.

`\n : `

Tribeca DMV routes

This map shows customer names and delivery addresses. Enter the password to continue.

\n \n \n \n`;\n\nconst STYLES = `\n :root { color-scheme: light dark; --bg:#fff; --fg:#111827; --muted:#6b7280; --line:#e5e7eb; --panel:#f9fafb; }\n @media (prefers-color-scheme: dark) {\n :root { --bg:#0f172a; --fg:#e2e8f0; --muted:#94a3b8; --line:#1e293b; --panel:#1e293b; }\n }\n * { box-sizing: border-box; }\n body { margin:0; font:14px/1.5 ui-sans-serif,system-ui,-apple-system,\"Segoe UI\",sans-serif;\n background:var(--bg); color:var(--fg); display:flex; flex-direction:column; height:100vh; }\n header { padding:10px 16px; border-bottom:1px solid var(--line); display:flex; flex-wrap:wrap;\n gap:12px 20px; align-items:center; }\n h1 { font-size:15px; margin:0; font-weight:650; }\n .meta { color:var(--muted); font-size:12px; }\n .toggle { display:inline-flex; border:1px solid var(--line); border-radius:8px; overflow:hidden; }\n .toggle button { border:0; padding:6px 14px; background:var(--panel); color:var(--fg);\n font:inherit; font-weight:550; cursor:pointer; }\n .toggle button[aria-pressed=\"true\"] { background:#2563eb; color:#fff; }\n #map { flex:1; min-height:0; background:var(--panel); position:relative; }\n #map.lassoing { cursor:crosshair; }\n #map.lassoing .leaflet-interactive { cursor:crosshair; }\n .toggle button[aria-pressed=\"true\"], .actions button[aria-pressed=\"true\"] { background:#2563eb; color:#fff; }\n #selbar { position:absolute; z-index:1000; left:50%; transform:translateX(-50%); bottom:14px;\n background:var(--bg); border:1px solid var(--line); border-radius:10px;\n box-shadow:0 4px 16px rgba(0,0,0,.18); padding:8px 10px;\n display:flex; gap:8px; align-items:center; font-size:13px; max-width:94%; }\n #selbar[hidden] { display:none; }\n #selbar select { font:inherit; font-size:12px; padding:4px; }\n #selbar button { font:inherit; font-size:12px; padding:5px 12px; border-radius:7px; cursor:pointer;\n border:1px solid #2563eb; background:#2563eb; color:#fff; }\n #selbar button.ghost { background:transparent; color:var(--fg); border-color:var(--line); }\n #selcount { font-weight:600; white-space:nowrap; }\n /* In-page confirmation. Native confirm() is silently suppressed in embedded\n and hardened browsers - it returns false, so the click just does nothing. */\n #confirmwrap { position:fixed; inset:0; z-index:2000; background:rgba(15,23,42,.45);\n display:flex; align-items:center; justify-content:center; padding:20px; }\n #confirmwrap[hidden] { display:none; }\n #confirmbox { background:var(--bg); color:var(--fg); border:1px solid var(--line);\n border-radius:12px; padding:18px 20px; max-width:430px;\n box-shadow:0 10px 40px rgba(0,0,0,.3); }\n #confirmbox p { margin:0 0 14px; font-size:13px; line-height:1.5; }\n #confirmbox .row { display:flex; gap:8px; justify-content:flex-end; }\n #confirmbox button { font:inherit; font-size:13px; padding:7px 15px; border-radius:8px;\n cursor:pointer; border:1px solid #2563eb; background:#2563eb; color:#fff; }\n #confirmbox button.ghost { background:transparent; color:var(--fg); border-color:var(--line); }\n .legend { display:flex; flex-wrap:wrap; gap:6px 14px; align-items:center; font-size:12px; }\n .legend label { display:inline-flex; align-items:center; gap:5px; cursor:pointer; }\n .swatch { width:11px; height:11px; border-radius:50%; display:inline-block; }\n .searchwrap { position:relative; }\n #search { font:inherit; font-size:12px; padding:5px 9px; border-radius:7px; width:230px;\n border:1px solid var(--line); background:var(--panel); color:var(--fg); }\n #results { position:absolute; z-index:1500; top:calc(100% + 4px); left:0; width:340px;\n max-height:260px; overflow-y:auto; background:var(--bg); color:var(--fg);\n border:1px solid var(--line); border-radius:9px; box-shadow:0 6px 24px rgba(0,0,0,.2); }\n #results[hidden] { display:none; }\n #results div { padding:6px 10px; font-size:12px; cursor:pointer; border-bottom:1px solid var(--line); }\n #results div:last-child { border-bottom:0; }\n #results div:hover, #results div.sel { background:#2563eb; color:#fff; }\n #results div small { display:block; opacity:.75; }\n #results .none { cursor:default; opacity:.7; }\n .lockbtn { font:inherit; font-size:11px; padding:2px 7px; border-radius:6px; cursor:pointer;\n border:1px solid var(--line); background:var(--panel); color:var(--muted); }\n .lockbtn.on { border-color:#111827; color:var(--fg); font-weight:600; }\n .leaflet-popup-content { font:13px/1.45 ui-sans-serif,system-ui,sans-serif; margin:10px 12px; }\n .leaflet-popup-content b { font-size:13px; }\n .leaflet-popup-content .sub { color:#6b7280; font-size:12px; }\n /* The load list grows with the number of trucks; without a cap a 7-route\n week pushes the map off the screen entirely. */\n footer { padding:8px 16px; border-top:1px solid var(--line); font-size:12px; color:var(--muted);\n max-height:34vh; overflow-y:auto; flex:none; }\n .actions button { border:1px solid var(--line); background:var(--panel); color:var(--fg);\n border-radius:7px; padding:5px 11px; font:inherit; font-size:12px; cursor:pointer; }\n .actions button:disabled { opacity:.5; cursor:default; }\n /* Placeholder: deliberately styled as not-yet-active so nobody assumes a\n click sent anything. There is no endpoint behind it. */\n .actions button.placeholder { border-style:dashed; color:var(--muted); }\n .approx { margin-top:6px; padding:5px 7px; border-radius:6px; font-size:11.5px; line-height:1.4;\n background:#fef3c7; color:#92400e; border:1px solid #fcd34d; }\n .prods { margin-top:7px; padding-top:6px; border-top:1px solid #e5e7eb; font-size:12px; }\n .prods div { padding:1px 0; }\n .loadlist { display:flex; flex-wrap:wrap; gap:10px 18px; margin-top:6px; }\n .loadlist section { min-width:210px; }\n .loadlist h4 { margin:0 0 3px; font-size:12px; display:flex; align-items:center; gap:6px; }\n .loadlist table { border-collapse:collapse; font-size:12px; }\n .loadlist td { padding:1px 8px 1px 0; vertical-align:top; }\n .loadlist td.q { text-align:right; font-variant-numeric:tabular-nums; font-weight:600; }\n .move-row { margin-top:9px; padding-top:8px; border-top:1px solid #e5e7eb;\n display:flex; gap:6px; align-items:center; }\n .move-row select { font:inherit; font-size:12px; padding:3px; flex:1; }\n .move-row button { font:inherit; font-size:12px; padding:4px 10px; cursor:pointer;\n border:1px solid #2563eb; background:#2563eb; color:#fff; border-radius:6px; }\n .move-row button.lockone { background:transparent; color:#111827; border-color:#9ca3af; }\n .lockednote { margin-top:6px; padding:5px 7px; border-radius:6px; font-size:11.5px;\n background:#e5e7eb; color:#111827; }\n details summary { cursor:pointer; }\n details ul { margin:6px 0 0; padding-left:18px; max-height:150px; overflow:auto; }\n\n /* ---- Phones -------------------------------------------------------------\n The header carries a lot: search, three toggle groups, the route legend,\n six action buttons and a status line. At desktop widths that sits on two\n rows; at 375px it would swallow the whole viewport and leave no map. So\n below 720px the controls compact, the action row becomes a single\n horizontally scrolling strip rather than six stacked rows, and the map is\n guaranteed a minimum share of the screen no matter how many routes the\n legend has to list. */\n /* On desktop the disclosure is inert: display:contents dissolves the

\n box so its children sit directly in the header's flex row, exactly as they\n did before it was introduced, and the summary is hidden. */\n #ctrls { display: contents; }\n #ctrls > summary { display: none; }\n\n @media (max-width: 720px) {\n /* dvh, not vh: mobile browsers count the collapsing URL bar in vh, which\n leaves the footer stranded below the fold until you scroll. */\n body { height:100dvh; }\n header { padding:8px 10px; gap:8px 10px; }\n h1 { font-size:14px; flex:1 1 auto; }\n\n /* Here it becomes a real disclosure. Collapsed, the header is just the\n title, the toggle and the stop count, which hands the map most of the\n screen; everything is one tap away. */\n #ctrls { display:block; flex:1 1 100%; }\n #ctrls > summary { display:inline-flex; align-items:center; gap:6px;\n list-style:none; cursor:pointer; font-weight:600; font-size:13px;\n border:1px solid var(--line); border-radius:8px;\n background:var(--panel); padding:9px 13px; }\n #ctrls > summary::-webkit-details-marker { display:none; }\n #ctrls > summary::after { content:\"\\\\25be\"; opacity:.7; }\n #ctrls[open] > summary::after { content:\"\\\\25b4\"; }\n #ctrls[open] > summary { margin-bottom:10px; }\n #ctrls > *:not(summary) { margin-bottom:8px; }\n #ctrls > *:last-child { margin-bottom:0; }\n .searchwrap { flex:1 1 100%; }\n /* 16px is deliberate - iOS Safari zooms the whole page in when a focused\n input has a font smaller than that, and never zooms back out. */\n #search { width:100%; font-size:16px; padding:8px 10px; }\n #results { width:100%; max-height:45vh; }\n #results div { padding:10px; font-size:13px; }\n /* min-height rather than padding alone: these are the primary controls and\n a 30px target is too small to hit reliably with a thumb. */\n .toggle { flex:1 1 auto; }\n .toggle button { flex:1; padding:10px; font-size:12px; min-height:40px; }\n .legend { flex:1 1 100%; gap:6px 12px; }\n .legend label { padding:2px 0; }\n .actions { flex:1 1 100%; display:flex; gap:6px; overflow-x:auto;\n padding-bottom:2px; -webkit-overflow-scrolling:touch; }\n .actions button { flex:0 0 auto; padding:10px 13px; font-size:12px; min-height:40px; }\n header > .meta { flex:1 1 100%; }\n #map { min-height:45vh; }\n footer { max-height:26vh; padding:8px 10px; }\n .loadlist { gap:14px; }\n .loadlist section { min-width:100%; }\n /* Full-width sheet along the bottom instead of a centred pill - at 375px\n the pill's contents wrap and it stops being tappable. */\n #selbar { left:8px; right:8px; bottom:8px; transform:none; max-width:none;\n flex-wrap:wrap; padding:10px; }\n #selcount { flex:1 1 100%; }\n #selbar select, #selbar button { flex:1 1 auto; padding:9px 10px; font-size:13px; }\n #confirmbox { max-width:none; width:100%; }\n /* The day lock toggles sit inside the legend, so they inherit its small\n type; give them a real target of their own. */\n .lockbtn { padding:7px 11px; font-size:12px; min-height:36px; }\n .move-row select, .move-row button { padding:8px 10px; font-size:13px; }\n }\n`;\n\n// Today's route gets its own page rather than reusing renderPage. The routing\n// view is built around trucks - the legend, lasso, move dropdowns, reoptimize\n// and save-to-sheet all assume a plan that can be changed. Today's route is\n// already on the road and none of that applies, so this is read-only: where\n// the stops are, whose route they are on, and what has been delivered.\nfunction renderTodayPage(data: Awaited>): Response {\n const { stops, label, date } = data;\n // Account names in this org carry & and * routinely (\"SEND INVOICES George\n // Vargas/East Coast A&P\"), so nothing user-supplied goes in raw.\n const esc = (v: string) =>\n String(v).replace(/[&<>]/g, (c) => ({ \"&\": \"&\", \"<\": \"<\", \">\": \">\" }[c]!));\n const routes = [...new Set(stops.map((s) => s.routeName))].sort();\n const delivered = stops.filter((s) => s.delivered).length;\n const units = stops.reduce((a, s) => a + s.units, 0);\n\n const nav = Object.values(REGIONS).map((r) =>\n ``\n ).join(\"\");\n\n const list = routes.map((rn) => {\n const rows = stops.filter((s) => s.routeName === rn);\n const done = rows.filter((s) => s.delivered).length;\n return `

${esc(rn)} ${rows.length} stops · ${\n rows.reduce((a, s) => a + s.units, 0)\n } units · ${done}/${rows.length} delivered

\n ${\n rows.map((s) =>\n ``\n ).join(\"\")\n }

${s.units}

${\n esc(s.accountName)\n }

${s.delivered ? \"delivered\" : esc(s.stage)}

`;\n }).join(\"\");\n\n return html(`${PAGE_HEAD}\n

Today · ${esc(label)}

Controls

${nav}

\n \n \n \n

${date} · ${stops.length} stops · ${units} units ·\n ${delivered} delivered, ${stops.length - delivered} outstanding

\n\n\n`);\n}\n\nfunction emptyPage(): Response {\n return html(`${PAGE_HEAD}\n

Tribeca DMV routes - map

No route snapshot stored yet.

Run main.ts once (the Run button on the val) and reload this page -\n the build writes the snapshot this map reads.

`);\n}\n\nfunction renderPage(snap: MapSnapshot): Response {\n // Inline the data rather than a second round trip; it is only a few KB.\n const json = JSON.stringify(snap).replace(/ s.day).length;\n const unrouted = snap.stops.length - routed;\n const built = new Date(snap.generatedAt).toLocaleString(\"en-US\", {\n timeZone: \"America/New_York\",\n dateStyle: \"medium\",\n timeStyle: \"short\",\n });\n\n // What the warehouse loads on each truck, biggest line first.\n const esc = (v: string) => String(v).replace(/[&<>]/g, (c) => ({ \"&\": \"&\", \"<\": \"<\", \">\": \">\" }[c]!));\n const byId = new Map(snap.stops.map((s) => [s.id, s]));\n // Collapsed by default once there are enough trucks that an open list would\n // crowd out the map.\n const loadList = snap.routes.length === 0 ? \"\" : `\n

Load list - what goes on each truck

` +\n snap.routes\n .slice()\n .sort((a, b) => a.date === b.date ? a.vehicle - b.vehicle : (a.date < b.date ? -1 : 1))\n .map((r) => {\n const truckStops = r.stopIds.map((id) => byId.get(id)!).filter(Boolean);\n const totals = aggregateProducts(truckStops);\n const units = truckStops.reduce((a, s) => a + s.units, 0);\n return `

${esc(r.day)} ${r.date.slice(5)} · Vehicle ${r.vehicle}\n ${truckStops.length} stops, ${units} units

\n ${\n totals.map((p) => ``).join(\"\")\n }

${p.qty}

${esc(p.name)}

`;\n }).join(\"\") +\n `

`;\n\n const failures = snap.geocodeFailures.length === 0 ? \"\" : `\n

${snap.geocodeFailures.length} stop(s) could not be geocoded - not on the map

${f.replace(/`).join(\"\")}

`;\n\n return html(`${PAGE_HEAD}\n

Tribeca routes

Controls

\n \n \n

${\n Object.values(REGIONS).map((r) =>\n ``\n ).join(\"\")\n }

\n \n \n

\n \n \n \n

\n \n \n \n \n \n \n \n

\n ${snap.optimized === false ? `${snap.stops.length} stops · not routed` : `${snap.stops.length} stops mapped${unrouted ? ` · ${unrouted} unassigned` : \"\"}`}\n · built ${built} ET\n

\n \n

\n\n\n\n`);\n}\n\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const { pathname } = url;\n // Region travels in the query string; the client appends location.search to\n // every request, so it propagates without threading it through each call.\n const region = getRegion(url.searchParams.get(\"region\") ?? undefined);\n\n // Sign out - matters on a shared dispatcher machine, and the cookie is\n // HttpOnly so it cannot be cleared any other way.\n if (pathname === \"/logout\") {\n return new Response(null, {\n status: 302,\n headers: {\n \"Location\": \"/\",\n \"Set-Cookie\": `${COOKIE}=; Path=/; Max-Age=0; HttpOnly; Secure; SameSite=Lax`,\n },\n });\n }\n\n // The one endpoint that must be reachable before authentication.\n if (req.method === \"POST\" && pathname === \"/login\") {\n const expected = Deno.env.get(\"MAP_ACCESS_KEY\");\n const body = await req.json().catch(() => ({}));\n if (expected && typeof body.key === \"string\" && secretsMatch(body.key, expected)) {\n return Response.json({ ok: true }, {\n headers: {\n \"Set-Cookie\": `${COOKIE}=${encodeURIComponent(expected)}; Path=/; Max-Age=2592000; ` +\n `HttpOnly; Secure; SameSite=Lax`,\n },\n });\n }\n return Response.json({ ok: false }, { status: 401 });\n }\n\n const gate = checkAccess(req);\n if (!gate.ok) {\n if (req.method === \"POST\") {\n return Response.json({ ok: false, error: \"unauthorized\" }, { status: 401 });\n }\n return deniedPage(gate.reason);\n }\n\n if (req.method === \"POST\") {\n const body = await req.json().catch(() => ({}));\n try {\n if (pathname === \"/move\") {\n const ids = Array.isArray(body.stopIds) ? body.stopIds : [body.stopId].filter(Boolean);\n return Response.json(await moveStops(region, ids, body.date, Number(body.vehicle)));\n }\n if (pathname === \"/save-sheet\") return Response.json(await saveToSheet(region));\n if (pathname === \"/reset\") return Response.json(await resetMoves(region));\n if (pathname === \"/lock\") {\n return Response.json(await setLocks(region, {\n stopIds: Array.isArray(body.stopIds) ? body.stopIds : undefined,\n date: typeof body.date === \"string\" ? body.date : undefined,\n locked: body.locked !== false,\n }));\n }\n if (pathname === \"/reoptimize\") {\n // Back to the clean machine plan: manual moves are discarded first, so\n // this always returns the original optimisation rather than re-packing\n // around whatever was dragged about since.\n await clearUnlockedMoves(region);\n const weekOffset = Number(body.weekOffset) || 0;\n // Only the current week owns the sheet. Looking at another week is a\n // preview - use \"Save to sheet\" to commit it.\n const out = await buildDcRoutes({\n weekOffset,\n writeSheet: weekOffset === 0,\n region: region.key,\n // Next week is plotted, not routed, unless explicitly asked for.\n optimize: body.optimize !== false,\n });\n return Response.json({\n ok: true,\n stops: out.totalStops ?? 0,\n routes: (out.routes ?? []).length,\n days: out.scheduledDays ?? [],\n });\n }\n } catch (err) {\n // The raw error carries internal service URLs, so it goes to the logs\n // rather than to the browser.\n console.log(`POST ${pathname} failed: ${String(err)}`);\n return Response.json(\n { ok: false, error: \"The server hit a problem. Check the val logs and try again.\" },\n { status: 500 },\n );\n }\n return Response.json({ ok: false, error: \"unknown action\" }, { status: 404 });\n }\n\n // Today's route is built live from Salesforce rather than read from a stored\n // snapshot: it changes all day as drivers mark drops delivered, and a stale\n // view of what is on the truck right now is worse than none.\n if (url.searchParams.get(\"view\") === \"today\") {\n try {\n const page = renderTodayPage(await todaysStops(region.key));\n if (gate.setCookie) {\n const withCookie = new Response(page.body, page);\n withCookie.headers.append(\"Set-Cookie\", gate.setCookie);\n return withCookie;\n }\n return page;\n } catch (err) {\n console.log(`today view failed for ${region.key}: ${String(err)}`);\n return storageErrorPage();\n }\n }\n\n const week = Number(url.searchParams.get(\"week\")) || 0;\n\n let snap: MapSnapshot | undefined;\n try {\n snap = await readSnapshot(region, week);\n // Switching weeks is normally instant because each week keeps its own\n // stored plan. Only the very first visit to a week has to build one, and a\n // future week is plotted rather than routed so even that stays quick.\n if (!snap) {\n console.log(`No stored plan for ${region.key} week ${week}; building one`);\n await buildDcRoutes({\n region: region.key,\n weekOffset: week,\n optimize: week === 0,\n writeSheet: week === 0,\n });\n snap = await readSnapshot(region, week);\n }\n } catch {\n return storageErrorPage();\n }\n const page = (!snap || !snap.stops?.length) ? emptyPage() : renderPage(snap);\n\n // First visit with ?key=... - remember it so the key need not stay in the URL.\n if (gate.setCookie) {\n const withCookie = new Response(page.body, page);\n withCookie.headers.append(\"Set-Cookie\", gate.setCookie);\n return withCookie;\n }\n return page;\n}\n","tribeca-route-map",[136],"796e302a-83db-466a-98ff-677d31e6a0d5","main.ts",429,["D",1786569624453],173,"script","// Tribeca DC/MD/VA-area route builder.\n//\n// Pulls DC-metro Opportunities (DC, New DC, Maryland, New Maryland, Virginia,\n// New Virginia delivery regions) due through the upcoming Saturday, geocodes\n// each stop's address (US Census Geocoder, no API key), builds a real road\n// distance matrix (OSRM's public demo server, no API key), and greedily packs\n// stops into as few vehicle-routes as possible (100-unit capacity each,\n// nearest-neighbor from the Springfield, VA depot). Writes routes, unassigned\n// overflow, and geocode failures to the \"Dc Test\" Google Sheet.\n//\n// Scheduling rules:\n// - The delivery week runs from TOMORROW through the upcoming Saturday,\n// weekdays only (plus Saturday for regions with includeSaturday set).\n// Today is never scheduled - that truck is already loaded.\n// - Stops the previous run assigned to TODAY are read back out of the sheet\n// and excluded, so a mid-week rebuild never re-plans a route that is\n// already out being driven.\n\nimport { blob } from \"https://esm.town/v/std/blob/main.ts\";\n\n// ============================================================================\n// Regions\n// ============================================================================\n//\n// The org has 32 delivery regions; this val serves the ones configured here.\n// Everything region-specific lives in this table so adding another market is a\n// config entry, not a fork.\n//\n// vehicleCapacities is per vehicle, in order: index 0 is Vehicle 1. Trucks are\n// not necessarily the same size - Miami runs a 192-unit truck alongside a\n// 100-unit one.\n\nexport type Region = {\n key: string;\n label: string;\n salesforceRegions: string[];\n sheetTab: string;\n vehicleCapacities: number[];\n windowStart: string;\n // Explicit depot address. When set it wins over the warehouse coordinates on\n // the Salesforce accounts, which are only accurate to the nearest depot.\n depotAddress?: string;\n // Allows Saturday as an OVERFLOW day. It is only added to the schedule when\n // the week's demand exceeds what Mon-Fri can carry, so a region under\n // capacity never puts a driver on the road for a sixth day. Miami trips this\n // most weeks (~722 units against 584 of Mon-Fri capacity); DC does not\n // (~180 against 400) and so stays on five days despite the flag being set.\n includeSaturday?: boolean;\n // Restricts the \"Today\" view to these Route Names. Exact match, so\n // \"Removed from route: TPA\" is excluded even though it contains \"TPA\".\n // Unset means show every route name the day happens to have.\n todayRoutes?: string[];\n};\n\nexport const REGIONS: Record = {\n dc: {\n key: \"dc\",\n label: \"DC / MD / VA\",\n salesforceRegions: [\"DC\", \"New DC\", \"Maryland\", \"New Maryland\", \"Virginia\", \"New Virginia\"],\n sheetTab: \"Sheet1\",\n vehicleCapacities: [100, 100],\n windowStart: \"2026-06-08\",\n includeSaturday: true,\n },\n miami: {\n key: \"miami\",\n label: \"Miami\",\n salesforceRegions: [\"Miami\", \"New Miami\"],\n sheetTab: \"Miami\",\n vehicleCapacities: [192, 100],\n windowStart: \"2026-06-08\",\n depotAddress: \"1661 N Dixie Hwy #6, Pompano Beach, FL 33060\",\n includeSaturday: true,\n },\n // Despite the name this is Tampa Bay, not the whole state - Tampa, St. Pete,\n // Largo, Seminole, with a tail up to Spring Hill and down to Sarasota. It is\n // a separate Delivery_Region__c value from Miami and does not overlap it.\n florida: {\n key: \"florida\",\n label: \"Florida\",\n salesforceRegions: [\"Florida\"],\n sheetTab: \"Florida\",\n vehicleCapacities: [100, 100],\n windowStart: \"2026-06-08\",\n depotAddress: \"6531 43rd St N #1608, Pinellas Park, FL 33781\",\n includeSaturday: true,\n // The two real trucks. Everything else the day picks up - unnamed routes\n // and \"Removed from route: ...\" records - is noise on this view.\n todayRoutes: [\"SP\", \"TPA\"],\n },\n};\n\nexport const getRegion = (key?: string): Region => REGIONS[key ?? \"dc\"] ?? REGIONS.dc;\n\n// Blob keys are namespaced per region. DC keeps the original unprefixed keys so\n// its existing snapshot, locks and dispatch ledger survive this change - losing\n// the ledger would silently re-break the \"already out on today's truck\" check.\nconst scoped = (base: string, region: Region) => region.key === \"dc\" ? base : `${base}:${region.key}`;\n\n// Same rule, for callers that only have the region key (map.ts).\nexport const blobKeyFor = (base: string, regionKey: string) =>\n regionKey === \"dc\" ? base : `${base}:${regionKey}`;\n\n// Snapshots are stored per region AND per week, so switching weeks is a page\n// load rather than a rebuild. Week 0 keeps the pre-existing key shape so no\n// stored plan is orphaned by this change.\nexport const snapshotKeyFor = (regionKey: string, weekOffset: number) =>\n weekOffset === 0\n ? blobKeyFor(SNAPSHOT_KEY_BASE, regionKey)\n : `${SNAPSHOT_KEY_BASE}:${regionKey}:w${weekOffset}`;\n\n// ============================================================================\n// Map snapshot\n// ============================================================================\n//\n// map.ts renders whatever the last build produced, so the expensive work\n// (Salesforce + geocoding + OSRM) happens here once rather than on every page\n// load. Stored val-scoped.\n\nconst SNAPSHOT_KEY_BASE = \"route-snapshot\";\nexport const SNAPSHOT_KEY = SNAPSHOT_KEY_BASE;\n\nexport type MapSnapshot = {\n generatedAt: string;\n region?: string;\n regionLabel?: string;\n vehicleCapacities?: number[];\n // false when the stops were plotted but deliberately not routed.\n optimized?: boolean;\n today: string;\n windowEnd: string;\n weekOffset: number; // 0 = rest of this week, 1 = next week\n depot: { lat: number; lon: number };\n days: { day: string; date: string }[];\n stops: {\n id: string;\n name: string;\n account: string;\n address: string;\n units: number;\n products: { qty: number; name: string }[];\n truckType: string | null;\n precision: \"exact\" | \"approx\";\n locked: boolean;\n lat: number;\n lon: number;\n // null on stops that were geocoded but never made it onto a truck.\n day: string | null;\n date: string | null;\n vehicle: number | null;\n seq: number | null;\n }[];\n routes: { day: string; date: string; vehicle: number; stopIds: string[] }[];\n geocodeFailures: string[];\n};\n\n// ============================================================================\n// Google Sheets auth (verified working against DC_ROUTE_SHEET_ID)\n// ============================================================================\n\nfunction base64url(bytes: ArrayBuffer | Uint8Array): string {\n const arr = bytes instanceof Uint8Array ? bytes : new Uint8Array(bytes);\n let str = \"\";\n for (const b of arr) str += String.fromCharCode(b);\n return btoa(str).replace(/\\+/g, \"-\").replace(/\\//g, \"_\").replace(/=+$/, \"\");\n}\n\nexport async function getAccessToken(): Promise {\n const clientEmail = Deno.env.get(\"GOOGLE_SERVICE_ACCOUNT_EMAIL\")!;\n const privateKeyPem = Deno.env.get(\"GOOGLE_SERVICE_ACCOUNT_PRIVATE_KEY\")!;\n\n const pemBody = privateKeyPem\n .replace(\"-----BEGIN PRIVATE KEY-----\", \"\")\n .replace(\"-----END PRIVATE KEY-----\", \"\")\n .replace(/\\s/g, \"\");\n const keyData = Uint8Array.from(atob(pemBody), (c) => c.charCodeAt(0));\n\n const cryptoKey = await crypto.subtle.importKey(\n \"pkcs8\",\n keyData,\n { name: \"RSASSA-PKCS1-v1_5\", hash: \"SHA-256\" },\n false,\n [\"sign\"],\n );\n\n const now = Math.floor(Date.now() / 1000);\n const header = { alg: \"RS256\", typ: \"JWT\" };\n const claims = {\n iss: clientEmail,\n scope: \"https://www.googleapis.com/auth/spreadsheets\",\n aud: \"https://oauth2.googleapis.com/token\",\n iat: now,\n exp: now + 3600,\n };\n\n const encoder = new TextEncoder();\n const signingInput = `${base64url(encoder.encode(JSON.stringify(header)))}.${\n base64url(encoder.encode(JSON.stringify(claims)))\n }`;\n const signature = await crypto.subtle.sign(\n \"RSASSA-PKCS1-v1_5\",\n cryptoKey,\n encoder.encode(signingInput),\n );\n const jwt = `${signingInput}.${base64url(signature)}`;\n\n const tokenRes = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n grant_type: \"urn:ietf:params:oauth:grant-type:jwt-bearer\",\n assertion: jwt,\n }),\n });\n const tokenBody = await tokenRes.json();\n if (!tokenRes.ok) {\n throw new Error(`Token exchange failed: ${tokenRes.status} ${JSON.stringify(tokenBody)}`);\n }\n return tokenBody.access_token;\n}\n\n// ============================================================================\n// Salesforce query (via the tribeca-salesforce-agent proxy val)\n// ============================================================================\n\nasync function sfQuery(soql: string): Promise {\n const url = Deno.env.get(\"SALESFORCE_AGENT_URL\")!;\n const key = Deno.env.get(\"SALESFORCE_AGENT_KEY\")!;\n const res = await fetch(`${url}?key=${encodeURIComponent(key)}&q=${encodeURIComponent(soql)}`);\n const data = await res.json().catch(() => ({}));\n if (!res.ok || data?.error) {\n throw new Error(`SOQL query failed: ${res.status} ${JSON.stringify(data)}`);\n }\n return data.records ?? [];\n}\n\n// ============================================================================\n// Dates\n// ============================================================================\n//\n// All date math is done in Eastern time on plain \"YYYY-MM-DD\" strings. Val\n// Town runs in UTC, so a naive Date() would roll the day forward after 8pm ET\n// and shift the whole schedule by one day.\n\nconst DAY_NAMES = [\"Sunday\", \"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\"];\n\nfunction todayEastern(): string {\n return new Intl.DateTimeFormat(\"en-CA\", {\n timeZone: \"America/New_York\",\n year: \"numeric\",\n month: \"2-digit\",\n day: \"2-digit\",\n }).format(new Date());\n}\n\n// Noon UTC keeps the arithmetic clear of any DST/offset edges.\nconst parseDate = (iso: string) => new Date(`${iso}T12:00:00Z`);\nconst addDays = (iso: string, n: number) => {\n const d = parseDate(iso);\n d.setUTCDate(d.getUTCDate() + n);\n return d.toISOString().slice(0, 10);\n};\nconst dayOfWeek = (iso: string) => parseDate(iso).getUTCDay();\nconst dayName = (iso: string) => DAY_NAMES[dayOfWeek(iso)];\nconst isWeekday = (iso: string) => dayOfWeek(iso) >= 1 && dayOfWeek(iso) <= 5;\n\n// End of the delivery window: the upcoming Saturday (today, if today is one).\nfunction upcomingSaturday(today: string): string {\n return addDays(today, (6 - dayOfWeek(today) + 7) % 7);\n}\n\n// The days this rebuild is allowed to schedule into: every delivery day from\n// tomorrow through the end of the window. Today is deliberately excluded.\n// On a Friday or Saturday run nothing is left this week, so it falls through\n// to the next full week.\nfunction deliveryDays(today: string, endDate: string): { day: string; date: string }[] {\n const days: { day: string; date: string }[] = [];\n for (let d = addDays(today, 1); d <= endDate; d = addDays(d, 1)) {\n if (isWeekday(d)) days.push({ day: dayName(d), date: d });\n }\n if (days.length > 0) return days;\n\n let monday = addDays(endDate, 1);\n while (dayOfWeek(monday) !== 1) monday = addDays(monday, 1);\n for (let i = 0; i < 5; i++) {\n const d = addDays(monday, i);\n days.push({ day: dayName(d), date: d });\n }\n return days;\n}\n\n// The Saturday that closes out a run of weekdays. Walks forward rather than\n// assuming the list ends on a Friday, since a mid-week rebuild can hand back\n// a single day.\nfunction saturdayAfter(days: { day: string; date: string }[]): { day: string; date: string } {\n let d = days[days.length - 1].date;\n do { d = addDays(d, 1); } while (dayOfWeek(d) !== 6);\n return { day: dayName(d), date: d };\n}\n\n// ============================================================================\n// Geocoding (US Census Geocoder - no API key)\n// ============================================================================\n\n// Full_Address__c mixes the mailing address with driver notes, all in one\n// string: \"4598 Yorktown Rd, Joint Base Andrews, MD 20762, CALL BRADEN FOR\n// ENTRY\". A real address ends at the ZIP, and the Census geocoder fails\n// outright when it has to parse the trailing note. So try progressively\n// cleaner forms and take the first that resolves.\n// The ZIP, anchored on \" \" so a 5-digit house number can never be\n// mistaken for one. Getting this wrong is not a small error: taking the first\n// 5-digit group in \"11150 Thomasson Barn Rd, Manassas, VA 20109\" yields 11150,\n// whose centroid is in Seattle.\nexport function zipOf(raw: string): string | null {\n const anchored = [...raw.matchAll(/\\b[A-Za-z]{2}\\.?\\s+(\\d{5})(?:-\\d{4})?\\b/g)];\n if (anchored.length) return anchored[anchored.length - 1][1];\n const bare = [...raw.matchAll(/\\b(\\d{5})(?:-\\d{4})?\\b/g)];\n return bare.length ? bare[bare.length - 1][1] : null;\n}\n\nexport function addressCandidates(raw: string): string[] {\n const out: string[] = [];\n const add = (s: string) => {\n const t = s.trim().replace(/[\\s,]+$/, \"\");\n if (t && !out.includes(t)) out.push(t);\n };\n\n // Cut at the ZIP, using the same state-anchored match as zipOf.\n const anchored = [...raw.matchAll(/\\b[A-Za-z]{2}\\.?\\s+\\d{5}(?:-\\d{4})?\\b/g)];\n const bare = [...raw.matchAll(/\\b\\d{5}(?:-\\d{4})?\\b/g)];\n const hit = anchored.length ? anchored[anchored.length - 1] : bare[bare.length - 1];\n const truncated = hit ? raw.slice(0, hit.index! + hit[0].length) : null;\n\n // Some records put \"DC\" in the city slot (\"4800 Foxhall Crescent NW, DC, DC\n // 20007\"), which the geocoder will not match.\n const fixCity = (s: string) => s.replace(/,\\s*(?:DC|D\\.C\\.)\\s*,\\s*DC\\b/i, \", Washington, DC\");\n\n // Raw first, so an address that already resolves is untouched and the\n // \"rescued by cleanup\" log genuinely means the raw form failed.\n add(raw);\n if (truncated) add(truncated);\n add(fixCity(raw));\n if (truncated) add(fixCity(truncated));\n return out;\n}\n\nasync function geocodeOnce(address: string): Promise<{ lat: number; lon: number } | null> {\n const url = `https://geocoding.geo.census.gov/geocoder/locations/onelineaddress?address=${\n encodeURIComponent(address)\n }&benchmark=Public_AR_Current&format=json`;\n const res = await fetch(url);\n if (!res.ok) return null;\n const body = await res.json().catch(() => null);\n const match = body?.result?.addressMatches?.[0];\n if (!match) return null;\n return { lat: Number(match.coordinates.y), lon: Number(match.coordinates.x) };\n}\n\n// ---------------------------------------------------------------------------\n// Second-tier geocoder: OpenStreetMap / Nominatim\n// ---------------------------------------------------------------------------\n//\n// The Census database has no record of some real addresses - military bases,\n// newer commercial parks, private lanes. OSM usually does (and it is the same\n// data OSRM routes on). Its usage policy caps us at 1 request/second, so calls\n// are funnelled through a queue even though the callers run in parallel. This\n// only ever runs for addresses the Census geocoder has already failed on, so\n// it costs a handful of seconds per build, not one per stop.\n\nconst NOMINATIM_UA =\n \"TribecaRouteBuilder/1.0 (internal delivery routing; contact info@tribecabeverage.com)\";\n\nlet nominatimQueue: Promise = Promise.resolve();\nfunction queued(fn: () => Promise): Promise {\n const run = nominatimQueue.then(fn, fn);\n nominatimQueue = run.then(() => sleep(1100), () => sleep(1100));\n return run;\n}\n\nasync function nominatimOnce(query: string): Promise<{ lat: number; lon: number } | null> {\n return await queued(async () => {\n const url = `https://nominatim.openstreetmap.org/search?q=${encodeURIComponent(query)}` +\n `&format=json&limit=1&countrycodes=us`;\n const res = await fetch(url, {\n headers: { \"User-Agent\": NOMINATIM_UA, \"Accept-Language\": \"en\" },\n });\n if (!res.ok) return null;\n const body = await res.json().catch(() => []);\n if (!Array.isArray(body) || body.length === 0) return null;\n return { lat: Number(body[0].lat), lon: Number(body[0].lon) };\n });\n}\n\nexport type GeocodeResult = {\n lat: number;\n lon: number;\n used: string;\n // \"exact\" = a real address match. \"approx\" = only the ZIP centroid resolved,\n // so the pin is in the right neighbourhood but not at the door.\n precision: \"exact\" | \"approx\";\n source: \"census\" | \"osm\" | \"zip\";\n};\n\n// ---------------------------------------------------------------------------\n// Geocode cache\n// ---------------------------------------------------------------------------\n//\n// Addresses barely change between runs, and the OSM tier is capped at one\n// request per second - without a cache, a week with a dozen hard addresses\n// spends over a minute geocoding and blows the HTTP request limit. Results are\n// keyed by the raw address string and reused forever, so each address is\n// looked up once ever, not once per build.\n\nexport const GEOCACHE_KEY = \"geocode-cache\";\nexport type GeoCache = Record & { used: string }>;\n\nexport async function loadGeoCache(): Promise {\n return (await blob.getJSON(GEOCACHE_KEY) as GeoCache | undefined) ?? {};\n}\n\nasync function geocodeAddress(raw: string, cache: GeoCache): Promise {\n const key = raw.trim();\n const cached = cache[key];\n if (cached) return cached;\n\n const candidates = addressCandidates(raw);\n let result: GeocodeResult | null = null;\n\n // 1. Census - authoritative for US street addresses, and free of rate limits.\n for (const candidate of candidates) {\n const hit = await geocodeOnce(candidate);\n if (hit) {\n result = { ...hit, used: candidate, precision: \"exact\", source: \"census\" };\n break;\n }\n }\n\n // 2. OSM - catches what the Census file is missing. Only the two most\n // promising forms are tried; each costs a second of wall clock.\n if (!result) {\n for (const candidate of candidates.slice(0, 2)) {\n const hit = await nominatimOnce(candidate);\n if (hit) {\n result = { ...hit, used: candidate, precision: \"exact\", source: \"osm\" };\n break;\n }\n }\n }\n\n // 3. ZIP centroid - never leaves a stop off the map entirely, but is flagged\n // everywhere it surfaces so nobody drives to it expecting a door.\n if (!result) {\n const zip = zipOf(raw);\n if (zip) {\n const hit = await nominatimOnce(`${zip}, USA`);\n if (hit) result = { ...hit, used: `ZIP ${zip}`, precision: \"approx\", source: \"zip\" };\n }\n }\n\n if (result) cache[key] = result;\n return result;\n}\n\n// ============================================================================\n// Distance matrix (OSRM public demo server - no API key)\n// ============================================================================\n//\n// The public demo server caps table requests at ~100 total coordinates, and\n// a full week of DC/MD/VA stops can exceed that. So the matrix is built in\n// row x column chunks (depot + stops as rows, stops as columns - we never\n// need a distance back to the depot) and stitched into one full matrix.\n\nconst OSRM_CHUNK_SIZE = 45; // keeps rowChunk + colChunk comfortably under 100\n\nfunction chunkWithIndex(arr: T[], size: number): { item: T; i: number }[][] {\n const indexed = arr.map((item, i) => ({ item, i }));\n const out: { item: T; i: number }[][] = [];\n for (let i = 0; i < indexed.length; i += size) out.push(indexed.slice(i, i + size));\n return out;\n}\n\n// The demo server intermittently refuses connections outright (the fetch\n// throws \"unsuccessful tunnel\" rather than returning a status), so every\n// chunk gets a few attempts with backoff. Without this a single blip\n// anywhere in the matrix aborts the whole build.\nconst OSRM_ATTEMPTS = 4;\nconst OSRM_MIN_INTERVAL_MS = 1100; // the public server's terms allow 1 req/sec\nconst sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));\n\nasync function osrmTableChunk(\n rowPoints: { lat: number; lon: number }[],\n colPoints: { lat: number; lon: number }[],\n): Promise {\n const all = [...rowPoints, ...colPoints];\n const coords = all.map((p) => `${p.lon},${p.lat}`).join(\";\");\n const sources = rowPoints.map((_, i) => i).join(\";\");\n const destinations = colPoints.map((_, i) => rowPoints.length + i).join(\";\");\n const url = `https://router.project-osrm.org/table/v1/driving/${coords}` +\n `?annotations=distance&sources=${sources}&destinations=${destinations}`;\n\n let lastError = \"\";\n for (let attempt = 1; attempt <= OSRM_ATTEMPTS; attempt++) {\n if (attempt > 1) await sleep(500 * 2 ** (attempt - 2)); // 0.5s, 1s, 2s\n\n try {\n const res = await fetch(url, {\n headers: { \"User-Agent\": \"TribecaRouteBuilder/1.0 (internal ops tool)\" },\n });\n if (!res.ok) {\n lastError = `${res.status} ${(await res.text().catch(() => \"\")).slice(0, 200)}`;\n continue;\n }\n const body = await res.json();\n if (body.code !== \"Ok\") {\n lastError = JSON.stringify(body).slice(0, 200);\n continue;\n }\n if (attempt > 1) console.log(`OSRM chunk succeeded on attempt ${attempt}`);\n return body.distances as number[][];\n } catch (err) {\n lastError = String(err).slice(0, 200); // connection refused / tunnel failure\n }\n }\n\n throw new Error(`OSRM table request failed after ${OSRM_ATTEMPTS} attempts: ${lastError}`);\n}\n\n// Square matrix over [depot, ...stops]: index 0 is the depot, index s+1 is\n// stops[s], in both dimensions. The depot column exists because routes return\n// to it - a driver finishing far from the yard still has to drive home, and\n// leaving that leg out let the optimiser end routes anywhere for free.\nexport async function getDistanceMatrix(\n depot: { lat: number; lon: number },\n stops: { lat: number; lon: number }[],\n): Promise {\n const points = [depot, ...stops];\n const rowChunks = chunkWithIndex(points, OSRM_CHUNK_SIZE);\n const colChunks = chunkWithIndex(points, OSRM_CHUNK_SIZE);\n\n const matrix: number[][] = points.map(() => new Array(points.length).fill(Infinity));\n\n // Sequential, one request per second. The public OSRM server's terms cap it\n // at 1 req/sec, and firing the chunks concurrently (which is what this used\n // to do) is the likely cause of the \"unsuccessful tunnel\" failures we saw.\n // Costs a second per chunk - four requests for a 53-stop week, nine for 131.\n let firstRequest = true;\n for (const rowChunk of rowChunks) {\n for (const colChunk of colChunks) {\n if (!firstRequest) await sleep(OSRM_MIN_INTERVAL_MS);\n firstRequest = false;\n\n const sub = await osrmTableChunk(\n rowChunk.map((r) => r.item),\n colChunk.map((c) => c.item),\n );\n rowChunk.forEach((r, ri) => {\n colChunk.forEach((c, ci) => {\n matrix[r.i][c.i] = sub[ri][ci];\n });\n });\n }\n }\n\n return matrix;\n}\n\n// ============================================================================\n// Route building\n// ============================================================================\n\ntype Product = { qty: number; name: string };\n\ntype Stop = {\n opportunityId: string;\n opportunityName: string;\n accountName: string;\n address: string;\n closeDate: string;\n units: number;\n products: Product[];\n truckType: string | null;\n lat: number;\n lon: number;\n precision: \"exact\" | \"approx\";\n};\n\n// OpportunityLineItem is not readable by the proxy val's integration user, but\n// Order_Summary__c carries the same breakdown as text, one line per product:\n// \"10 x 5 GAL PL Water Bottles - Spring\\n\\n4 x 5 GAL PL Water Bottles - ...\"\nexport function parseOrderSummary(summary: string | null | undefined): Product[] {\n if (!summary) return [];\n return String(summary)\n .split(\"\\n\")\n .map((line) => line.trim())\n .filter(Boolean)\n .map((line) => {\n const m = line.match(/^(\\d+(?:\\.\\d+)?)\\s*[xX]\\s*(.+)$/);\n return m ? { qty: Number(m[1]), name: m[2].trim() } : { qty: 1, name: line };\n });\n}\n\n// Roll a set of stops up into \"what goes on the truck\", biggest line first.\nexport function aggregateProducts(stops: { products: Product[] }[]): Product[] {\n const totals = new Map();\n for (const s of stops) {\n for (const p of s.products ?? []) totals.set(p.name, (totals.get(p.name) ?? 0) + p.qty);\n }\n return [...totals.entries()]\n .map(([name, qty]) => ({ name, qty }))\n // Orders sometimes carry a zero-quantity line; \"load 0 of these\" is noise\n // on a truck sheet. The per-stop breakdown still shows it verbatim.\n .filter((p) => p.qty > 0)\n .sort((a, b) => b.qty - a.qty || a.name.localeCompare(b.name));\n}\n\n// Vehicle count and capacities now come from the region config.\n\n// Geography-first packing (sweep + 2-opt).\n//\n// The previous approach filled each truck to capacity by repeatedly grabbing\n// the nearest stop that still fit. That minimised truck count but let routes\n// sprawl: two different days could each cross the whole metro, so a driver\n// covered the same neighbourhood twice in a week.\n//\n// Instead, cut the map into wedges around the depot:\n// 1. Sort stops by compass angle from the depot.\n// 2. Sweep around the circle, closing a wedge whenever the next stop would\n// exceed vehicle capacity. Each wedge is a contiguous slice of geography,\n// so wedges do not overlap and no area is visited twice unless its own\n// volume needs two trucks.\n// 3. The seam (where the sweep starts) changes the result, so try every stop\n// as the seam and keep whichever gives the least total driving.\n// 4. Order each wedge nearest-neighbour, then improve it with 2-opt.\n\n// -1 means \"the depot\"; 0..n-1 are stop indices. Both sides accept either.\ntype Dist = (from: number, to: number) => number;\n\n// Depot -> every stop in order -> back to the depot. The closing leg is what\n// makes the route a loop rather than a one-way trip.\nfunction pathLength(route: number[], dist: Dist): number {\n if (route.length === 0) return 0;\n let total = dist(-1, route[0]);\n for (let i = 0; i + 1 < route.length; i++) total += dist(route[i], route[i + 1]);\n total += dist(route[route.length - 1], -1);\n return total;\n}\n\nfunction nearestNeighbour(cluster: number[], dist: Dist): number[] {\n const remaining = new Set(cluster);\n const order: number[] = [];\n let current = -1; // depot\n while (remaining.size > 0) {\n let best = -1;\n let bestDist = Infinity;\n for (const i of remaining) {\n const d = dist(current, i);\n if (d < bestDist) {\n bestDist = d;\n best = i;\n }\n }\n if (best === -1) best = [...remaining][0]; // unreachable in the matrix\n order.push(best);\n remaining.delete(best);\n current = best;\n }\n return order;\n}\n\n// Standard 2-opt, but each candidate is scored by recomputing the whole path.\n// Road distances are asymmetric, so the usual O(1) delta (which assumes the\n// reversed segment costs the same in both directions) would be wrong here.\nfunction twoOpt(route: number[], dist: Dist): number[] {\n let best = route;\n let bestLen = pathLength(best, dist);\n for (let pass = 0; pass < 30; pass++) {\n let improved = false;\n for (let i = 0; i < best.length - 1; i++) {\n for (let j = i + 1; j < best.length; j++) {\n const candidate = [\n ...best.slice(0, i),\n ...best.slice(i, j + 1).reverse(),\n ...best.slice(j + 1),\n ];\n const len = pathLength(candidate, dist);\n if (len < bestLen - 1e-6) {\n best = candidate;\n bestLen = len;\n improved = true;\n }\n }\n }\n if (!improved) break;\n }\n return best;\n}\n\n// Cut the angular ordering into capacity-bounded wedges, starting at `seam`.\n//\n// `capacities` is the capacity of each wedge in the order they will be filled,\n// because trucks differ in size: wedge 0 goes to the first vehicle-slot, wedge\n// 1 to the second, and so on. Cutting every wedge at the same size would either\n// waste the big truck or overload the small one.\nfunction sweepInto(\n angularOrder: number[],\n seam: number,\n stops: Stop[],\n capacities: number[],\n): number[][] {\n const clusters: number[][] = [];\n let current: number[] = [];\n let used = 0;\n const capacityOf = (clusterIndex: number) =>\n capacities[Math.min(clusterIndex, capacities.length - 1)];\n\n for (let k = 0; k < angularOrder.length; k++) {\n const i = angularOrder[(seam + k) % angularOrder.length];\n if (current.length > 0 && used + stops[i].units > capacityOf(clusters.length)) {\n clusters.push(current);\n current = [];\n used = 0;\n }\n current.push(i);\n used += stops[i].units;\n }\n if (current.length > 0) clusters.push(current);\n return clusters;\n}\n\nexport function buildRoutes(\n stops: Stop[],\n distances: number[][],\n maxSlots: number,\n depot: { lat: number; lon: number },\n // Capacity of each vehicle-slot, in slot order.\n slotCapacities: number[],\n) {\n const dist: Dist = (from, to) => distances[from + 1][to + 1];\n\n // Compass angle from the depot, so the sweep goes round the clock.\n const angularOrder = stops\n .map((s, i) => ({ i, angle: Math.atan2(s.lat - depot.lat, s.lon - depot.lon) }))\n .sort((a, b) => a.angle - b.angle)\n .map((x) => x.i);\n\n // Pick the seam that minimises total driving, scoring cheaply with\n // nearest-neighbour only; 2-opt is applied once to the winner.\n let bestClusters: number[][] = [];\n let bestScore = Infinity;\n for (let seam = 0; seam < angularOrder.length; seam++) {\n const clusters = sweepInto(angularOrder, seam, stops, slotCapacities);\n if (clusters.length > maxSlots) continue; // needs more trucks than exist\n const score = clusters.reduce((sum, c) => sum + pathLength(nearestNeighbour(c, dist), dist), 0);\n if (score < bestScore) {\n bestScore = score;\n bestClusters = clusters;\n }\n }\n\n // Every seam overflowed the available slots: fall back to the tightest\n // packing we can and let the overflow report as unassigned.\n if (bestClusters.length === 0) bestClusters = sweepInto(angularOrder, 0, stops, slotCapacities);\n\n const slots: { slot: number; stops: Stop[] }[] = [];\n const unassigned: Stop[] = [];\n\n bestClusters.forEach((cluster, slot) => {\n if (slot >= maxSlots) {\n for (const i of cluster) unassigned.push(stops[i]);\n return;\n }\n const ordered = twoOpt(nearestNeighbour(cluster, dist), dist);\n slots.push({ slot, stops: ordered.map((i) => stops[i]) });\n });\n\n return { slots, unassigned, totalDistance: bestScore };\n}\n\n// Total metres driven across every route, for logging and comparison.\nfunction totalRouteDistance(\n slots: { stops: Stop[] }[],\n stops: Stop[],\n distances: number[][],\n): number {\n const indexOf = new Map(stops.map((s, i) => [s.opportunityId, i]));\n const dist: Dist = (from, to) => distances[from + 1][to + 1];\n return slots.reduce(\n (sum, r) => sum + pathLength(r.stops.map((s) => indexOf.get(s.opportunityId)!), dist),\n 0,\n );\n}\n\n// ----------------------------------------------------------------------------\n// Manual overrides\n// ----------------------------------------------------------------------------\n//\n// Dispatchers move stops between days on the map. Those moves are recorded\n// here and re-applied on every rebuild, otherwise the next run would silently\n// undo them. Overrides are scoped to a delivery window and discarded once that\n// window has passed, so they never leak into a later week.\n\n// ----------------------------------------------------------------------------\n// Dispatch ledger\n// ----------------------------------------------------------------------------\n//\n// Which stops were planned for which date. This is how a rebuild knows not to\n// re-plan the stops that are already out on today's truck.\n//\n// It used to be read back out of the Google Sheet, which was wrong in a way\n// that only showed up once a day rolled over: the first rebuild of the day\n// correctly excluded today's stops, but that same rebuild rewrote the sheet\n// without a column for today - so every later rebuild found nothing, and the\n// stops on today's truck were silently re-planned into the coming days.\n//\n// A build only ever plans tomorrow onward, so it never overwrites today's\n// entry. The ledger therefore still holds what yesterday's build committed,\n// which is exactly the question being asked.\n\nexport const DISPATCH_KEY = \"dispatch-log\";\nexport type DispatchLog = Record; // ISO date -> opportunity ids\n\nexport async function loadDispatchLog(region: Region = REGIONS.dc): Promise {\n return (await blob.getJSON(scoped(DISPATCH_KEY, region)) as DispatchLog | undefined) ?? {};\n}\n\nexport const OVERRIDES_KEY = \"route-overrides\";\n\nexport type Overrides = {\n windowEnd: string;\n // `locked` marks an entry the dispatcher explicitly froze. Both kinds pin a\n // stop, but Reoptimize discards plain moves and keeps locks - that is what\n // makes a lock a lock.\n moves: Record;\n};\n\nexport async function loadOverrides(\n windowEnd: string,\n region: Region = REGIONS.dc,\n): Promise {\n const stored = await blob.getJSON(scoped(OVERRIDES_KEY, region)) as Overrides | undefined;\n if (!stored || stored.windowEnd !== windowEnd) return { windowEnd, moves: {} };\n return stored;\n}\n\nfunction applyOverrides(\n slots: { slot: number; stops: Stop[] }[],\n distances: number[][],\n stops: Stop[],\n days: { day: string; date: string }[],\n overrides: Overrides,\n): { slots: { slot: number; stops: Stop[] }[]; applied: number } {\n const moves = Object.entries(overrides.moves);\n if (moves.length === 0) return { slots, applied: 0 };\n\n const indexOf = new Map(stops.map((s, i) => [s.opportunityId, i]));\n const dist: Dist = (from, to) => distances[from + 1][to + 1];\n\n // slot number for a (date, vehicle) pair - the inverse of slotLabel.\n const slotFor = (date: string, vehicle: number) => {\n const dayIndex = days.findIndex((d) => d.date === date);\n return dayIndex === -1 ? -1 : dayIndex + (vehicle - 1) * days.length;\n };\n\n const bySlot = new Map(slots.map((s) => [s.slot, [...s.stops]]));\n const touched = new Set();\n let applied = 0;\n\n for (const [oppId, target] of moves) {\n const targetSlot = slotFor(target.date, target.vehicle);\n if (targetSlot === -1) continue; // that day is no longer in the window\n\n let stop: Stop | undefined;\n for (const [slot, list] of bySlot) {\n const at = list.findIndex((s) => s.opportunityId === oppId);\n if (at !== -1) {\n stop = list[at];\n if (slot === targetSlot) break; // already where it belongs\n list.splice(at, 1);\n touched.add(slot);\n break;\n }\n }\n if (!stop) continue; // stop is not in this build (delivered, or filtered out)\n\n if (!bySlot.has(targetSlot)) bySlot.set(targetSlot, []);\n const target_ = bySlot.get(targetSlot)!;\n if (!target_.some((s) => s.opportunityId === oppId)) {\n target_.push(stop);\n touched.add(targetSlot);\n applied++;\n }\n }\n\n // Re-optimise only the routes a move actually changed.\n const rebuilt = [...bySlot.entries()]\n .filter(([, list]) => list.length > 0)\n .map(([slot, list]) => {\n if (!touched.has(slot)) return { slot, stops: list };\n const idx = list.map((s) => indexOf.get(s.opportunityId)!);\n const ordered = twoOpt(nearestNeighbour(idx, dist), dist);\n return { slot, stops: ordered.map((i) => stops[i]) };\n })\n .sort((a, b) => a.slot - b.slot);\n\n return { slots: rebuilt, applied };\n}\n\n// Slots are ordered by vehicle tier first, day second: every available day\n// gets a shot at a single truck before any day is handed a 2nd truck.\n// Combined with buildRoutes only opening a new slot when the previous one is\n// truly full, this keeps 2-truck days to the minimum the actual stop volume\n// forces, instead of concentrating both trucks on the first couple of days\n// while later days sit unused.\nfunction slotLabel(slot: number, days: { day: string; date: string }[]) {\n const { day, date } = days[slot % days.length];\n const vehicle = Math.floor(slot / days.length) + 1;\n return { day, date, vehicle };\n}\n\n// ============================================================================\n// Sheet I/O\n// ============================================================================\n\nconst BLOCK_HEADERS = [\n \"Vehicle\",\n \"Stop #\",\n \"Opportunity\",\n \"Account\",\n \"Address\",\n \"Close Date\",\n \"Units\",\n \"Running Total\",\n \"Products\",\n \"Opp ID\",\n];\nconst OPP_ID_OFFSET = BLOCK_HEADERS.indexOf(\"Opp ID\");\nconst BLOCK_WIDTH = BLOCK_HEADERS.length;\nconst GAP = 1; // blank spacer column between day blocks\nconst STRIDE = BLOCK_WIDTH + GAP;\nconst sheetRange = (tab: string) => `${tab}!A1:BZ2000`;\n\n// Each region writes to its own tab. A tab the spreadsheet does not have yet is\n// created on demand, otherwise the values API 400s on an unknown range.\nasync function ensureTab(sheetId: string, token: string, tab: string) {\n const res = await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}?fields=sheets.properties.title`,\n { headers: { Authorization: `Bearer ${token}` } },\n );\n if (!res.ok) return; // let the write surface the real error\n const body = await res.json().catch(() => ({}));\n const titles: string[] = (body.sheets ?? []).map((s: any) => s.properties?.title).filter(Boolean);\n if (titles.includes(tab)) return;\n\n console.log(`Creating sheet tab \"${tab}\"`);\n await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${sheetId}:batchUpdate`, {\n method: \"POST\",\n headers: { Authorization: `Bearer ${token}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ requests: [{ addSheet: { properties: { title: tab } } }] }),\n });\n}\n\n// Day-block headers carry the ISO date (\"Wednesday 2026-08-12\") so a later\n// run can find a specific day's block again unambiguously - a bare weekday\n// name would also match the same weekday from a previous week.\nconst dayHeaderText = (day: string, date: string) => `${day} ${date}`;\n\n// Stops already out on today's truck. The previous run wrote today's block\n// into the sheet; those routes are being driven right now, so they must not\n// be re-planned. Returns the Opportunity Ids under today's date header.\n//\n// Returns an empty set if the sheet is unreadable, has no block for today, or\n// predates the \"Opp ID\" column - in all of those cases there is nothing we can\n// confidently call already-assigned, and the rebuild proceeds from scratch.\nexport async function readTodaysAssignedIds(\n sheetId: string,\n token: string,\n today: string,\n tab = \"Sheet1\",\n): Promise> {\n const assigned = new Set();\n\n const res = await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${encodeURIComponent(sheetRange(tab))}`,\n { headers: { Authorization: `Bearer ${token}` } },\n );\n if (!res.ok) {\n console.log(`Could not read existing sheet (${res.status}); assuming nothing is assigned yet`);\n return assigned;\n }\n\n const values: string[][] = (await res.json().catch(() => ({})))?.values ?? [];\n if (values.length < 3) return assigned;\n\n const blockStart = (values[0] ?? []).findIndex((cell) => String(cell ?? \"\").includes(today));\n if (blockStart === -1) return assigned;\n\n for (const row of values.slice(2)) {\n // The LOAD LIST / UNASSIGNED / GEOCODE FAILURES sections live below the\n // day grid and reuse the same columns, so stop before reading them as\n // route rows.\n const marker = String(row?.[0] ?? \"\");\n if (\n marker.startsWith(\"LOAD LIST\") || marker.startsWith(\"UNASSIGNED\") ||\n marker.startsWith(\"GEOCODE\")\n ) break;\n\n const id = String(row?.[blockStart + OPP_ID_OFFSET] ?? \"\").trim();\n if (id) assigned.add(id);\n }\n return assigned;\n}\n\nexport async function writeRoutesToSheet(\n sheetId: string,\n token: string,\n days: { day: string; date: string }[],\n slots: { slot: number; stops: Stop[] }[],\n unassigned: Stop[],\n geocodeFailures: string[],\n tab = \"Sheet1\",\n) {\n await ensureTab(sheetId, token, tab);\n // Each delivery day gets its own side-by-side block of columns, so the\n // whole week is scannable left-to-right instead of scrolling through one\n // long day-labeled column.\n const totalCols = days.length * STRIDE - GAP;\n const blank = () => new Array(totalCols).fill(\"\");\n\n const byDate = new Map(days.map((d) => [d.date, []]));\n for (const { slot, stops } of slots) {\n const { date, vehicle } = slotLabel(slot, days);\n byDate.get(date)!.push({ vehicle, stops });\n }\n\n const dayBlockRows = new Map();\n let maxRows = 0;\n for (const { date } of days) {\n const blockRows: (string | number)[][] = [];\n for (const { vehicle, stops } of byDate.get(date)!) {\n let running = 0;\n stops.forEach((s, i) => {\n running += s.units;\n blockRows.push([\n `Vehicle ${vehicle}`,\n i + 1,\n s.opportunityName,\n s.accountName,\n // Loudly marked: this pin is the ZIP centroid, not the address.\n s.precision === \"approx\" ? `~APPROX~ ${s.address}` : s.address,\n s.closeDate,\n s.units,\n running,\n (s.products ?? []).map((p) => `${p.qty} x ${p.name}`).join(\"; \"),\n s.opportunityId,\n ]);\n });\n }\n dayBlockRows.set(date, blockRows);\n maxRows = Math.max(maxRows, blockRows.length);\n }\n\n const rows: (string | number)[][] = [];\n\n const dayHeaderRow = blank();\n days.forEach(({ day, date }, di) => {\n dayHeaderRow[di * STRIDE] = dayHeaderText(day, date);\n });\n rows.push(dayHeaderRow);\n\n const subHeaderRow = blank();\n days.forEach((_, di) => {\n BLOCK_HEADERS.forEach((h, ci) => {\n subHeaderRow[di * STRIDE + ci] = h;\n });\n });\n rows.push(subHeaderRow);\n\n for (let r = 0; r < maxRows; r++) {\n const row = blank();\n days.forEach(({ date }, di) => {\n const blockRows = dayBlockRows.get(date)!;\n if (r < blockRows.length) {\n blockRows[r].forEach((val, ci) => {\n row[di * STRIDE + ci] = val;\n });\n }\n });\n rows.push(row);\n }\n\n // Load list: what the warehouse puts on each truck. Sits directly under the\n // day grid so each block's totals line up with the route above them.\n rows.push(blank());\n const loadHeader = blank();\n days.forEach(({ date }, di) => {\n const trucks = byDate.get(date)!;\n loadHeader[di * STRIDE] = trucks.length > 0 ? \"LOAD LIST\" : \"\";\n });\n rows.push(loadHeader);\n\n const loadLines = new Map();\n let maxLoadRows = 0;\n for (const { date } of days) {\n const lines: (string | number)[][] = [];\n for (const { vehicle, stops: truckStops } of byDate.get(date)!) {\n lines.push([`Vehicle ${vehicle}`, \"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\", \"\"]);\n for (const p of aggregateProducts(truckStops)) lines.push([\"\", p.qty, p.name]);\n }\n loadLines.set(date, lines);\n maxLoadRows = Math.max(maxLoadRows, lines.length);\n }\n for (let r = 0; r < maxLoadRows; r++) {\n const row = blank();\n days.forEach(({ date }, di) => {\n const lines = loadLines.get(date)!;\n if (r < lines.length) lines[r].forEach((v, ci) => { if (v !== \"\") row[di * STRIDE + ci] = v; });\n });\n rows.push(row);\n }\n\n // Note: the sections below deliberately leave the \"Opp ID\" column empty, so\n // readTodaysAssignedIds can never mistake an unassigned or failed stop for\n // one that is out on a truck.\n if (unassigned.length > 0) {\n rows.push(blank());\n const header = blank();\n header[0] = \"UNASSIGNED - over capacity for the remaining days\";\n rows.push(header);\n for (const s of unassigned) {\n const row = blank();\n row[2] = s.opportunityName;\n row[3] = s.accountName;\n row[4] = s.address;\n row[5] = s.closeDate;\n row[6] = s.units;\n rows.push(row);\n }\n }\n\n rows.push(blank());\n const failHeader = blank();\n failHeader[0] = \"GEOCODE FAILURES\";\n rows.push(failHeader);\n if (geocodeFailures.length > 0) {\n for (const f of geocodeFailures) {\n const row = blank();\n row[2] = f;\n rows.push(row);\n }\n } else {\n const row = blank();\n row[2] = \"(none)\";\n rows.push(row);\n }\n\n // Clear the sheet first so re-runs don't just keep appending stale data.\n await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${encodeURIComponent(sheetRange(tab))}:clear`,\n { method: \"POST\", headers: { Authorization: `Bearer ${token}` } },\n );\n\n const res = await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${encodeURIComponent(tab + \"!A1\")}?valueInputOption=RAW`,\n {\n method: \"PUT\",\n headers: { Authorization: `Bearer ${token}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ values: rows }),\n },\n );\n const body = await res.json();\n if (!res.ok) throw new Error(`Sheets write failed: ${res.status} ${JSON.stringify(body)}`);\n return body;\n}\n\n// ============================================================================\n// Main\n// ============================================================================\n\n// weekOffset 0 = the rest of this delivery week (tomorrow through the upcoming\n// Saturday). weekOffset 1 = next week only: the full delivery week after that\n// Saturday, with the order window extended to the following Saturday. Useful\n// for planning ahead without disturbing the current week.\n// writeSheet: false builds the plan and refreshes the map only, leaving the\n// Google Sheet untouched. Used for looking at another week without clobbering\n// the week the drivers are currently working from.\n// optimize: false pulls and geocodes the stops but skips the distance matrix\n// and the packing entirely, producing a plain map of where the work is. That\n// matters for scale: routing 355 Miami stops needs 64 sequential OSRM requests\n// (~90s) and blows the HTTP timeout, while plotting them needs none at all.\nasync function buildDcRoutes(\n options: {\n weekOffset?: number;\n writeSheet?: boolean;\n region?: string;\n optimize?: boolean;\n } = {},\n) {\n const region = getRegion(options.region);\n const weekOffset = options.weekOffset ?? 0;\n const optimize = options.optimize ?? true;\n const writeSheet = (options.writeSheet ?? true) && optimize;\n const MAX_VEHICLES = region.vehicleCapacities.length;\n const today = todayEastern();\n const thisSaturday = upcomingSaturday(today);\n\n const endDate = weekOffset === 0 ? thisSaturday : addDays(thisSaturday, 7 * weekOffset);\n // Weekdays only for now. Saturday is added below, and only if the stops that\n // come back cannot fit inside Mon-Fri.\n const days = weekOffset === 0\n ? deliveryDays(today, endDate)\n // Everything after this week's Saturday is fair game, so the whole\n // delivery week of the target week is available.\n : deliveryDays(addDays(thisSaturday, 7 * (weekOffset - 1)), endDate);\n\n console.log(\n `[${region.label}] Today is ${dayName(today)} ${today}` +\n (weekOffset ? ` (planning +${weekOffset} week)` : \"\") +\n `; scheduling ${days.length} day(s): ` +\n days.map((d) => `${d.day} ${d.date}`).join(\", \"),\n );\n\n const sheetId = Deno.env.get(\"DC_ROUTE_SHEET_ID\")!;\n const token = await getAccessToken();\n\n // What a previous build already committed to today's truck.\n const dispatchLog = await loadDispatchLog(region);\n let alreadyAssigned = new Set(dispatchLog[today] ?? []);\n if (alreadyAssigned.size === 0) {\n // Ledger has nothing for today (first run after this change, or the blob\n // was lost). Fall back to the old sheet readback, which still works as long\n // as today's column has not been overwritten yet.\n alreadyAssigned = await readTodaysAssignedIds(sheetId, token, today, region.sheetTab);\n if (alreadyAssigned.size > 0) {\n console.log(`Ledger empty for ${today}; recovered ${alreadyAssigned.size} stop(s) from the sheet`);\n }\n }\n console.log(`${alreadyAssigned.size} stop(s) already assigned to today's route - excluding them`);\n\n const allOpps = await sfQuery(\n `SELECT Id, Name, CloseDate, Total_Amount_of_Products__c, AccountId,\n Order_Summary__c, Truck_Type__c, Route_Date__c, Route_Name__c\n FROM Opportunity\n WHERE Delivery_Region__c IN (${region.salesforceRegions.map((r) => `'${r}'`).join(\",\")})\n AND StageName = 'Delivery Due'\n AND CloseDate >= ${region.windowStart}\n AND CloseDate <= ${endDate}\n ORDER BY CloseDate ASC`.replace(/\\s+/g, \" \").trim(),\n );\n // Anything dated today is going out today - it does not belong on a map of\n // what still has to be planned. Two independent signals, because neither is\n // complete on its own:\n // - the dispatch ledger: what a previous build committed to today's truck\n // - the delivery date on the record itself\n //\n // Route Date is the field dispatch actually schedules on, so it wins when\n // set; CloseDate is the fallback for anything not yet on a route. In this\n // org the two disagree on ~92% of scheduled records, so using CloseDate\n // alone put stops on the map that were already out on a truck.\n //\n // Route Date carries sentinel values (2000-01-01, 2001-01-01) on records\n // pulled off a route - the route name literally reads \"Removed from route\".\n // Those mean \"no route\", not \"delivered in the year 2000\".\n const ROUTE_DATE_FLOOR = \"2020-01-01\";\n const effectiveDate = (o: any) => {\n const routed = o.Route_Date__c;\n return routed && routed >= ROUTE_DATE_FLOOR ? routed : o.CloseDate;\n };\n\n const dueToday = allOpps.filter((o) => effectiveDate(o) === today);\n const oppRecords = allOpps.filter((o) =>\n !alreadyAssigned.has(o.Id) && effectiveDate(o) !== today\n );\n if (dueToday.length > 0) {\n const byRoute = dueToday.filter((o) => o.Route_Date__c === today).length;\n console.log(\n `${dueToday.length} stop(s) dated today (${today}) - excluded from the map ` +\n `(${byRoute} by Route Date, ${dueToday.length - byRoute} by Close Date)`,\n );\n }\n console.log(\n `Found ${allOpps.length} ${region.label} opportunities due ${region.windowStart} to ${endDate}; ` +\n `${oppRecords.length} to route after dropping today's assignments`,\n );\n if (oppRecords.length === 0) {\n return { totalStops: 0, days, routes: [], unassignedCount: 0, geocodeFailures: [] };\n }\n\n const accountIds = [...new Set(oppRecords.map((o) => o.AccountId))];\n const accountIdList = accountIds.map((id) => `'${id}'`).join(\",\");\n // Delivery eligibility, mirroring the saved filter in the mapping tool:\n // Night Delivery != Yes AND (balance is null OR balance < 13 OR override)\n //\n // Two field names are deliberately odd and are NOT typos here:\n // - Override_Outstandling_Balance__c is misspelled in the org itself.\n // - Night_Delivery__c is the one that carries data; a second field with the\n // identical \"Night Delivery\" label (Night_Delivery1__c) holds only\n // free-text drop notes, and Night_Delivery_Acceptable__c is false on\n // every account.\n // Balance_for_Valtown__c is a formula that surfaces the account's Total\n // Balance Amount without the fw1 managed package, whose objects and fields\n // are invisible to this connection (it is not licensed for this user). The\n // values match Balance_for_Wordpress__c exactly across every active account.\n //\n // The saved filter has an \"equals NULL\" branch. A currency formula never\n // evaluates to null, so that branch is dead here - the \"< limit\" test covers\n // the zero-balance case on its own. Null is still handled below in case this\n // is ever pointed at a plain currency field.\n const BALANCE_FIELD = \"Balance_for_Valtown__c\";\n const BALANCE_LIMIT = 13;\n const ACCOUNT_FIELDS = `Id, Name, Full_Address__c,\n Client_Location__Latitude__s, Client_Location__Longitude__s,\n Closest_Warehouse_Latitude__c, Closest_Warehouse_Longitude__c,\n Night_Delivery__c, Override_Outstandling_Balance__c`;\n\n // The balance lives in a managed package (fw1__). Where that package is not\n // licensed for this connection the field is not queryable at all, so fall\n // back to building without the balance gate rather than failing the run -\n // but say so loudly, because it silently widens who gets delivered to.\n let haveBalance = true;\n let accounts: any[];\n try {\n accounts = await sfQuery(\n `SELECT ${ACCOUNT_FIELDS}, ${BALANCE_FIELD}\n FROM Account WHERE Id IN (${accountIdList})`.replace(/\\s+/g, \" \").trim(),\n );\n } catch (e) {\n if (!String(e).includes(\"No such column\")) throw e;\n haveBalance = false;\n console.log(\n `WARNING: ${BALANCE_FIELD} is not readable by this connection, so the ` +\n `outstanding-balance filter is NOT being applied. Accounts that owe ` +\n `money will still be routed.`,\n );\n accounts = await sfQuery(\n `SELECT ${ACCOUNT_FIELDS} FROM Account WHERE Id IN (${accountIdList})`\n .replace(/\\s+/g, \" \").trim(),\n );\n }\n const accountsById = new Map(accounts.map((a) => [a.Id, a]));\n\n const nightHeld: string[] = [];\n const balanceHeld: string[] = [];\n const routableOpps = oppRecords.filter((o) => {\n const a = accountsById.get(o.AccountId);\n if (!a) return true; // no account row - the geocode step reports it instead\n if (String(a.Night_Delivery__c ?? \"\").trim().toLowerCase() === \"yes\") {\n nightHeld.push(a.Name);\n return false;\n }\n if (haveBalance) {\n const bal = a[BALANCE_FIELD];\n const owes = bal !== null && bal !== undefined && Number(bal) >= BALANCE_LIMIT;\n if (owes && a.Override_Outstandling_Balance__c !== true) {\n balanceHeld.push(`${a.Name} (${bal})`);\n return false;\n }\n }\n return true;\n });\n if (nightHeld.length > 0) {\n console.log(`${nightHeld.length} stop(s) held back: Night Delivery = Yes`);\n }\n if (balanceHeld.length > 0) {\n console.log(\n `${balanceHeld.length} stop(s) held back: balance >= ${BALANCE_LIMIT} and no override`,\n );\n for (const n of balanceHeld.slice(0, 12)) console.log(` ${n}`);\n if (balanceHeld.length > 12) console.log(` ... and ${balanceHeld.length - 12} more`);\n }\n if (routableOpps.length === 0) {\n return { totalStops: 0, days, routes: [], unassignedCount: 0, geocodeFailures: [] };\n }\n\n const geocodeFailures: string[] = [];\n const rescued: string[] = []; // needed a fallback geocoder to place at all\n const geoCache = await loadGeoCache();\n const geoCacheSizeBefore = Object.keys(geoCache).length;\n\n const stopResults = await Promise.all(routableOpps.map(async (opp) => {\n const account = accountsById.get(opp.AccountId);\n if (!account?.Full_Address__c) {\n geocodeFailures.push(`${opp.Name} (${opp.Id}): no address on account`);\n return null;\n }\n\n let lat = account.Client_Location__Latitude__s;\n let lon = account.Client_Location__Longitude__s;\n let precision: \"exact\" | \"approx\" = \"exact\"; // coords already on the Account\n if (lat == null || lon == null) {\n const geo = await geocodeAddress(account.Full_Address__c, geoCache);\n if (!geo) {\n geocodeFailures.push(`${opp.Name} (${opp.Id}): geocode failed for \"${account.Full_Address__c}\"`);\n return null;\n }\n if (geo.source !== \"census\") {\n rescued.push(\n `[${geo.source}${geo.precision === \"approx\" ? \", APPROXIMATE\" : \"\"}] ` +\n `${opp.Name}: \"${account.Full_Address__c}\" -> ${geo.used}`,\n );\n }\n lat = geo.lat;\n lon = geo.lon;\n precision = geo.precision;\n }\n\n const stop: Stop = {\n opportunityId: opp.Id,\n opportunityName: opp.Name,\n accountName: account.Name,\n address: account.Full_Address__c,\n closeDate: opp.CloseDate,\n units: Number(opp.Total_Amount_of_Products__c) || 1,\n products: parseOrderSummary(opp.Order_Summary__c),\n truckType: opp.Truck_Type__c ?? null,\n lat,\n lon,\n precision,\n };\n return stop;\n }));\n const stops: Stop[] = stopResults.filter((s): s is Stop => s !== null);\n\n console.log(`Geocoded ${stops.length}/${routableOpps.length} stops (${geocodeFailures.length} failures)`);\n const newlyCached = Object.keys(geoCache).length - geoCacheSizeBefore;\n if (newlyCached > 0) {\n await blob.setJSON(GEOCACHE_KEY, geoCache);\n console.log(`Geocode cache: +${newlyCached} new, ${Object.keys(geoCache).length} total`);\n }\n\n const approxCount = stops.filter((s) => s.precision === \"approx\").length;\n if (rescued.length > 0) {\n console.log(`${rescued.length} address(es) needed a fallback geocoder:`);\n for (const c of rescued) console.log(\" \", c);\n }\n if (approxCount > 0) {\n console.log(`${approxCount} stop(s) placed at a ZIP centroid only - flagged as approximate`);\n }\n if (geocodeFailures.length > 0) {\n console.log(\"Geocode failures:\", JSON.stringify(geocodeFailures));\n }\n if (stops.length === 0) {\n return { totalStops: 0, days, routes: [], unassignedCount: 0, geocodeFailures };\n }\n\n // Saturday is an overflow day, not a standing one. Open it only when the\n // week's demand genuinely will not fit inside Mon-Fri, so a region running\n // under capacity never costs a driver a sixth day. Measured 2026-08-12:\n // Miami needs it (722 units vs 584), DC does not (180 vs 400).\n //\n // This is deliberately a total-units test, not a \"did the packer drop\n // anything\" test - it has to run before packing, since the number of days\n // determines how many truck-slots the packer is given in the first place.\n const fleetPerDay = region.vehicleCapacities.reduce((a, c) => a + c, 0);\n const totalUnits = stops.reduce((a, s) => a + s.units, 0);\n const weekdayCapacity = days.length * fleetPerDay;\n if (region.includeSaturday && totalUnits > weekdayCapacity) {\n days.push(saturdayAfter(days));\n console.log(\n `${totalUnits} units exceeds Mon-Fri capacity (${weekdayCapacity}) - ` +\n `adding ${days[days.length - 1].day} ${days[days.length - 1].date} as an overflow day ` +\n `(capacity now ${days.length * fleetPerDay})`,\n );\n } else if (region.includeSaturday) {\n console.log(\n `${totalUnits} units fits inside Mon-Fri capacity (${weekdayCapacity}) - no Saturday needed`,\n );\n }\n\n const maxSlots = days.length * MAX_VEHICLES;\n // Slot order is day-major then vehicle tier, so slots 0..d-1 are Vehicle 1,\n // d..2d-1 are Vehicle 2. Capacity follows the vehicle, not the day.\n const slotCapacities = Array.from(\n { length: maxSlots },\n (_, slot) => region.vehicleCapacities[Math.floor(slot / days.length)],\n );\n\n // The depot comes from Salesforce - every account carries the coordinates of\n // its closest warehouse, so each region finds its own without configuration.\n // The most common value wins; env vars remain the fallback.\n const warehouses = new Map();\n for (const a of accounts) {\n const lat = a.Closest_Warehouse_Latitude__c;\n const lon = a.Closest_Warehouse_Longitude__c;\n if (lat == null || lon == null) continue;\n const k = `${lat},${lon}`;\n const seen = warehouses.get(k) ?? { lat: Number(lat), lon: Number(lon), n: 0 };\n seen.n++;\n warehouses.set(k, seen);\n }\n const commonest = [...warehouses.values()].sort((a, b) => b.n - a.n)[0];\n\n // An explicit depot address for the region beats everything else.\n let depot: { lat: number; lon: number };\n let depotSource: string;\n const configured = region.depotAddress\n ? await geocodeAddress(region.depotAddress, geoCache)\n : null;\n if (configured) {\n depot = { lat: configured.lat, lon: configured.lon };\n depotSource = `configured address \"${region.depotAddress}\" via ${configured.source}`;\n } else if (commonest) {\n depot = { lat: commonest.lat, lon: commonest.lon };\n depotSource = `Salesforce warehouse, ${commonest.n}/${accounts.length} accounts`;\n } else {\n depot = { lat: Number(Deno.env.get(\"DEPOT_LAT\")), lon: Number(Deno.env.get(\"DEPOT_LON\")) };\n depotSource = \"env vars\";\n }\n console.log(`Depot ${depot.lat},${depot.lon} (${depotSource}); routes start and end here`);\n\n // Stops-only mode: no distance matrix, no packing, every stop unassigned.\n if (!optimize) {\n console.log(`Stops-only build: ${stops.length} stop(s) plotted, not routed`);\n await blob.setJSON(snapshotKeyFor(region.key, weekOffset), {\n generatedAt: new Date().toISOString(),\n region: region.key,\n regionLabel: region.label,\n vehicleCapacities: region.vehicleCapacities,\n optimized: false,\n today,\n windowEnd: endDate,\n weekOffset,\n depot,\n days,\n stops: stops.map((s) => ({\n id: s.opportunityId,\n name: s.opportunityName,\n account: s.accountName,\n address: s.address,\n units: s.units,\n products: s.products,\n truckType: s.truckType,\n locked: false,\n precision: s.precision,\n lat: s.lat,\n lon: s.lon,\n day: null,\n date: null,\n vehicle: null,\n seq: null,\n })),\n routes: [],\n geocodeFailures,\n } satisfies MapSnapshot);\n return {\n today,\n windowEnd: endDate,\n optimized: false,\n scheduledDays: days.map((d) => `${d.day} ${d.date}`),\n totalStops: stops.length,\n routes: [],\n unassignedCount: stops.length,\n geocodeFailures,\n };\n }\n\n const distances = await getDistanceMatrix(depot, stops);\n\n const built = buildRoutes(stops, distances, maxSlots, depot, slotCapacities);\n const unassigned = built.unassigned;\n\n const overrides = await loadOverrides(endDate, region);\n const { slots, applied } = applyOverrides(built.slots, distances, stops, days, overrides);\n\n const km = (m: number) => (m / 1000).toFixed(1);\n console.log(\n `Built ${slots.length} route(s) across ${days.length} day(s), ${unassigned.length} unassigned ` +\n `stop(s) (capacity ${slotCapacities.reduce((a, c) => a + c, 0)} units across ${maxSlots} truck-days); ` +\n `${km(totalRouteDistance(slots, stops, distances))} km total` +\n (applied > 0 ? `; ${applied} manual override(s) applied` : \"\"),\n );\n for (const { slot, stops: routeStops } of slots) {\n const { day, vehicle } = slotLabel(slot, days);\n const units = routeStops.reduce((a, s) => a + s.units, 0);\n const cap = region.vehicleCapacities[vehicle - 1] ?? slotCapacities[slot];\n console.log(\n ` ${day} v${vehicle}: ${routeStops.length} stops, ${units}/${cap} units` +\n (units > cap ? \" <-- OVER CAPACITY\" : \"\"),\n );\n }\n\n // Record what this build commits to each planned day, so tomorrow's rebuild\n // knows which stops are already out on a truck. Only the days this build\n // actually planned are touched - today's entry is left alone.\n const plannedByDate: Record = {};\n for (const { slot, stops: routeStops } of slots) {\n const { date } = slotLabel(slot, days);\n (plannedByDate[date] ??= []).push(...routeStops.map((s) => s.opportunityId));\n }\n for (const { date } of days) dispatchLog[date] = plannedByDate[date] ?? [];\n\n // Keep the ledger from growing forever; three weeks of history is ample.\n const cutoff = addDays(today, -21);\n for (const date of Object.keys(dispatchLog)) {\n if (date < cutoff) delete dispatchLog[date];\n }\n await blob.setJSON(scoped(DISPATCH_KEY, region), dispatchLog);\n\n // Snapshot for map.ts: every geocoded stop, plus how the packer assigned it.\n const assignment = new Map();\n const routes = slots.map(({ slot, stops: routeStops }) => {\n const { day, date, vehicle } = slotLabel(slot, days);\n routeStops.forEach((s, i) => assignment.set(s.opportunityId, { day, date, vehicle, seq: i + 1 }));\n return { day, date, vehicle, stopIds: routeStops.map((s) => s.opportunityId) };\n });\n await blob.setJSON(snapshotKeyFor(region.key, weekOffset), {\n generatedAt: new Date().toISOString(),\n region: region.key,\n regionLabel: region.label,\n vehicleCapacities: region.vehicleCapacities,\n today,\n windowEnd: endDate,\n weekOffset,\n depot,\n days,\n stops: stops.map((s) => {\n const a = assignment.get(s.opportunityId);\n return {\n id: s.opportunityId,\n name: s.opportunityName,\n account: s.accountName,\n address: s.address,\n units: s.units,\n products: s.products,\n truckType: s.truckType,\n locked: overrides.moves[s.opportunityId]?.locked === true,\n precision: s.precision,\n lat: s.lat,\n lon: s.lon,\n day: a?.day ?? null,\n date: a?.date ?? null,\n vehicle: a?.vehicle ?? null,\n seq: a?.seq ?? null,\n };\n }),\n routes,\n geocodeFailures,\n } satisfies MapSnapshot);\n console.log(`Saved map snapshot: ${stops.length} stop(s), ${routes.length} route(s)`);\n\n if (writeSheet) {\n const sheetResult = await writeRoutesToSheet(\n sheetId,\n token,\n days,\n slots,\n unassigned,\n geocodeFailures,\n region.sheetTab,\n );\n console.log(`Sheet write result (tab \"${region.sheetTab}\"):`, JSON.stringify(sheetResult));\n } else {\n console.log(\"Sheet left untouched (preview build)\");\n }\n\n return {\n today,\n windowEnd: endDate,\n scheduledDays: days.map((d) => `${d.day} ${d.date}`),\n excludedAsAlreadyAssigned: alreadyAssigned.size,\n totalStops: stops.length,\n routes: slots.map(({ slot, stops }) => ({\n ...slotLabel(slot, days),\n stopCount: stops.length,\n units: stops.reduce((a, s) => a + s.units, 0),\n })),\n unassignedCount: unassigned.length,\n geocodeFailures,\n };\n}\n\n// ============================================================================\n// Route queue\n// ============================================================================\n//\n// Routing a full week for a large region takes about 68 seconds - 302 stops\n// means a ~91,000-pair distance matrix - and Val Town kills an HTTP request\n// well before that. Measured 2026-08-12: florida 68.1s, miami 67.6s, dc 12.1s.\n// A button press died mid-build with nothing saved.\n//\n// So the map no longer runs those builds. It writes a job here and returns\n// immediately; worker.ts picks it up on its interval, where there is a\n// script-length budget to finish, and the page polls until the plan appears.\nexport const QUEUE_KEY = \"route-queue\";\n\n// A job that claims to be running for longer than this was killed mid-build\n// (a deploy, an isolate restart). Without this the queue would wedge forever\n// on a job nothing is actually working on.\nexport const STALE_JOB_MS = 10 * 60 * 1000;\n\nexport type QueueJob = {\n region: string;\n weekOffset: number;\n status: \"queued\" | \"running\" | \"done\" | \"error\";\n requestedAt: string;\n startedAt?: string;\n finishedAt?: string;\n stops?: number;\n routes?: number;\n unassigned?: number;\n error?: string;\n};\n\nexport const jobKey = (region: string, weekOffset: number) => `${region}:${weekOffset}`;\n\nexport async function loadQueue(): Promise> {\n return ((await blob.getJSON(QUEUE_KEY).catch(() => null)) as Record) ?? {};\n}\n\nexport async function saveQueue(q: Record): Promise {\n await blob.setJSON(QUEUE_KEY, q);\n}\n\n// Returns the job that is now in flight. Pressing the button twice does not\n// stack two builds of the same week on top of each other.\nexport async function enqueueRoute(region: string, weekOffset: number): Promise {\n const queue = await loadQueue();\n const key = jobKey(region, weekOffset);\n const existing = queue[key];\n if (existing && (existing.status === \"queued\" || existing.status === \"running\")) {\n const age = Date.now() - new Date(existing.startedAt ?? existing.requestedAt).getTime();\n if (existing.status === \"queued\" || age < STALE_JOB_MS) return existing;\n }\n const job: QueueJob = {\n region,\n weekOffset,\n status: \"queued\",\n requestedAt: new Date().toISOString(),\n };\n queue[key] = job;\n await saveQueue(queue);\n return job;\n}\n\nexport async function readJob(region: string, weekOffset: number): Promise {\n return (await loadQueue())[jobKey(region, weekOffset)] ?? null;\n}\n\n// ============================================================================\n// Today's route (read-only)\n// ============================================================================\n//\n// Everything whose effective delivery date is TODAY. The planner deliberately\n// leaves these out of the week's map - they are already loaded on a truck - so\n// there has been no way to see them. This is that view.\n//\n// Deliberately NOT routed: no distance matrix, no packing, no overrides. The\n// stops come back grouped by the Route Name dispatch already put them on, in\n// Salesforce's own order. That also keeps it fast enough to build on request\n// instead of from a stored snapshot, so it is always current.\n//\n// Every stage is included, not just Delivery Due. By mid-afternoon most of the\n// day's drops have flipped to Delivered and would otherwise vanish from the\n// view, which is the opposite of useful when you are asking what is out today.\nexport type TodayStop = {\n opportunityId: string;\n opportunityName: string;\n accountName: string;\n address: string;\n units: number;\n products: Product[];\n routeName: string;\n stage: string;\n delivered: boolean;\n lat: number;\n lon: number;\n precision: \"exact\" | \"approx\";\n};\n\nexport async function todaysStops(regionKey: string): Promise<{\n region: string;\n label: string;\n date: string;\n stops: TodayStop[];\n hidden: number;\n hiddenRoutes: string[];\n geocodeFailures: string[];\n}> {\n const region = getRegion(regionKey);\n const today = todayEastern();\n\n const opps = await sfQuery(\n `SELECT Id, Name, CloseDate, Route_Date__c, Route_Name__c, StageName,\n Total_Amount_of_Products__c, AccountId, Order_Summary__c\n FROM Opportunity\n WHERE Delivery_Region__c IN (${region.salesforceRegions.map((r) => `'${r}'`).join(\",\")})\n AND (Route_Date__c = ${today} OR CloseDate = ${today})`\n .replace(/\\s+/g, \" \").trim(),\n );\n\n // Same rule the planner uses: Route Date wins when it is a real date, and\n // the 2000/2001 sentinels mean \"pulled off a route\", not a delivery date.\n const ROUTE_DATE_FLOOR = \"2020-01-01\";\n const effectiveDate = (o: any) =>\n o.Route_Date__c && o.Route_Date__c >= ROUTE_DATE_FLOOR ? o.Route_Date__c : o.CloseDate;\n const due = opps.filter((o) => effectiveDate(o) === today);\n\n const geocodeFailures: string[] = [];\n if (due.length === 0) {\n return {\n region: region.key,\n label: region.label,\n date: today,\n stops: [],\n hidden: 0,\n hiddenRoutes: [],\n geocodeFailures,\n };\n }\n\n const accountIdList = [...new Set(due.map((o) => o.AccountId))].map((id) => `'${id}'`).join(\",\");\n const accounts = await sfQuery(\n `SELECT Id, Name, Full_Address__c, Client_Location__Latitude__s, Client_Location__Longitude__s\n FROM Account WHERE Id IN (${accountIdList})`.replace(/\\s+/g, \" \").trim(),\n );\n const accountsById = new Map(accounts.map((a) => [a.Id, a]));\n\n const geoCache = await loadGeoCache();\n const cacheSizeBefore = Object.keys(geoCache).length;\n\n const results = await Promise.all(due.map(async (opp) => {\n const account = accountsById.get(opp.AccountId);\n if (!account?.Full_Address__c) {\n geocodeFailures.push(`${opp.Name}: no address on account`);\n return null;\n }\n let lat = account.Client_Location__Latitude__s;\n let lon = account.Client_Location__Longitude__s;\n let precision: \"exact\" | \"approx\" = \"exact\";\n if (lat == null || lon == null) {\n const geo = await geocodeAddress(account.Full_Address__c, geoCache);\n if (!geo) {\n geocodeFailures.push(`${opp.Name}: geocode failed for \"${account.Full_Address__c}\"`);\n return null;\n }\n lat = geo.lat;\n lon = geo.lon;\n precision = geo.precision;\n }\n const stage = String(opp.StageName ?? \"\");\n return {\n opportunityId: opp.Id,\n opportunityName: opp.Name,\n accountName: account.Name,\n address: account.Full_Address__c,\n units: Number(opp.Total_Amount_of_Products__c) || 1,\n products: parseOrderSummary(opp.Order_Summary__c),\n routeName: opp.Route_Name__c ?? \"No route name\",\n stage,\n delivered: stage.toLowerCase() === \"delivered\",\n lat: Number(lat),\n lon: Number(lon),\n precision,\n } as TodayStop;\n }));\n\n if (Object.keys(geoCache).length > cacheSizeBefore) await blob.setJSON(GEOCACHE_KEY, geoCache);\n\n const all = results.filter((s): s is TodayStop => s !== null);\n\n // Keep only the region's real trucks, where it names them. Hidden stops are\n // counted and reported rather than dropped quietly - \"52 stops\" turning into\n // \"38 stops\" with no explanation is how people stop trusting the view.\n const allow = region.todayRoutes;\n const stops = allow\n ? all.filter((s) => allow.some((r) => r.toLowerCase() === s.routeName.trim().toLowerCase()))\n : all;\n const hidden = all.length - stops.length;\n const hiddenRoutes = [...new Set(all.filter((s) => !stops.includes(s)).map((s) => s.routeName))];\n\n console.log(\n `[${region.label}] today (${today}): ${stops.length} stop(s) on route, ` +\n `${stops.filter((s) => s.delivered).length} already delivered` +\n (hidden ? `; ${hidden} hidden on other route names (${hiddenRoutes.join(\", \")})` : \"\"),\n );\n return {\n region: region.key,\n label: region.label,\n date: today,\n stops,\n hidden,\n hiddenRoutes,\n geocodeFailures,\n };\n}\n\nexport default async function (\n options: { weekOffset?: number; writeSheet?: boolean; region?: string } = {},\n) {\n return await buildDcRoutes(options);\n}\n",[146],"pulls","branches",1,"forks","envVars",{"_161":163,"_164":61},"connections",{"_161":61,"_162":61},"count","unfilledCount",10,"emptyCount",{"_41":145,"_31":146,"_29":55,"_130":-5,"_39":40,"_38":147,"_131":24,"_36":175,"_13":149,"_32":150,"_34":151,"_23":24,"_133":-5,"_169":176,"_26":177},{"_41":135,"_31":136,"_29":137,"_130":-5,"_39":40,"_38":138,"_131":24,"_36":172,"_13":140,"_32":141,"_34":142,"_23":24,"_133":143,"_169":173,"_26":174},{"_41":129,"_31":27,"_29":30,"_130":-5,"_39":40,"_38":14,"_131":57,"_36":168,"_13":61,"_32":33,"_34":35,"_23":24,"_133":-5,"_169":170,"_26":171},["D",1786569644611],"children",[],[27],["D",1786568909148],[],[136],["D",1786569624453],[],[146],["D",1785443670415],{"_95":94,"_103":99},{"_95":94,"_96":97,"_98":99,"_18":53,"_100":101,"_102":24},{"_13":84,"_31":85,"_41":40,"_86":42,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":43,"_93":94},"ctx",{"_77":-5,"_123":215,"_216":217,"_218":219,"_220":24,"_221":24,"_222":24,"_223":57,"_224":-5,"_225":-7,"_226":-7,"_227":61,"_228":24,"_229":24,"_230":-5,"_231":24,"_232":24,"_233":24,"_234":-5},"ENV",{"_186":187,"_188":189,"_190":85,"_191":192,"_193":194,"_195":196,"_197":198,"_199":200,"_201":202,"_203":204,"_205":206,"_207":208,"_209":210,"_211":212,"_213":214},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","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 · v430