[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_247":248,"_249":250},"routes/_app.x.$handle.$name",{"_95":94,"_32":44,"_104":85,"_27":105,"_13":14,"_106":-5,"_11":107,"_108":109,"_110":111,"_112":113,"_114":115,"_20":-5,"_116":-5,"_117":-5,"_118":48,"_119":120,"_121":25,"_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",{"_42":43,"_32":44,"_37":45,"_46":-5,"_47":48,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":25,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":-5,"_66":-5,"_67":-5,"_68":-5,"_69":70,"_71":70,"_72":73,"_74":73,"_75":73,"_76":70,"_77":70,"_78":79,"_80":81,"_82":83,"_84":48},"version",1437,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":36,"_37":38,"_39":14,"_40":41,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",true,"deletedHere","path","scratch_keycheck.ts","latestPath","fileId","e4ad6920-ad4d-11f1-b578-1607ee4eb77e","name","fileType","http","content","// Throwaway, delete after the 2026-09-10 key rotation is confirmed.\n// Proves THIS val's SALESFORCE_AGENT_KEY still opens the Salesforce proxy,\n// using the same header-based call sfQuery now makes. Read-only: one SELECT\n// against a single row. Reveals only the last 6 characters of the key so the\n// response can be compared against the expected value without printing it.\nexport default async function (): Promise {\n const url = Deno.env.get(\"SALESFORCE_AGENT_URL\") ?? \"\";\n const key = Deno.env.get(\"SALESFORCE_AGENT_KEY\") ?? \"\";\n if (!url || !key) {\n return Response.json({ error: \"SALESFORCE_AGENT_URL / SALESFORCE_AGENT_KEY not set\" }, { status: 500 });\n }\n const res = await fetch(`${url}?q=${encodeURIComponent(\"SELECT Id FROM Account LIMIT 1\")}`, {\n headers: { \"x-api-key\": key },\n });\n const body = await res.text();\n return Response.json({\n proxyStatus: res.status,\n ok: res.ok,\n keyLength: key.length,\n keyEndsWith: key.slice(-6),\n body: body.slice(0, 120),\n });\n}\n","createdAt",["D",1789068598755],"branchVersion","branchId","fcce0313-0df2-4398-9e5c-8a90515f9c8a","id","7ff6addc-d421-4037-b4cd-7b7685c3dab2","tribeca-route-builder",["D",1785443670415],"deletedAt","legacyEnvVars",false,"privacy","public","httpPrivacy","description","DC-area route builder: Salesforce -> Google Sheet","imageUrl","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","10f0e8ae-8c56-11f1-94f0-1607ee4eb77e","pinPreview","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":55,"_100":101,"_102":48},"branch",{"_13":14,"_32":85,"_42":41,"_86":43,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":44,"_93":94},"isOwned","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",{"_42":43,"_32":44,"_37":243,"_46":-5,"_47":48,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":25,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":-5,"_66":-5,"_67":-5,"_68":-5,"_69":70,"_71":70,"_72":73,"_74":73,"_75":73,"_76":70,"_77":70,"_78":244,"_80":245,"_82":246,"_84":48},"tree",[210,211,212,213,214,215,216,217],"files",[210,211,212,213,214,215,216,217],"counts",{"_199":62,"_200":159,"_201":62,"_202":203,"_204":205},"contents",[126,127,128,129,130,131,132,133],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_42":192,"_32":193,"_30":194,"_137":-5,"_40":41,"_39":195,"_139":25,"_37":196,"_13":62,"_33":151,"_35":197,"_24":48,"_143":-5,"_27":198},{"_42":185,"_32":186,"_30":57,"_137":-5,"_40":41,"_39":187,"_139":25,"_37":188,"_13":189,"_33":151,"_35":190,"_24":48,"_143":-5,"_27":191},{"_42":176,"_32":177,"_30":178,"_137":-5,"_40":41,"_39":179,"_139":25,"_37":180,"_13":181,"_33":34,"_35":182,"_24":48,"_143":183,"_27":184},{"_42":169,"_32":170,"_30":171,"_137":-5,"_40":41,"_39":172,"_139":25,"_37":173,"_13":159,"_33":141,"_35":174,"_24":48,"_143":-5,"_27":175},{"_42":162,"_32":163,"_30":164,"_137":-5,"_40":41,"_39":165,"_139":25,"_37":166,"_13":159,"_33":141,"_35":167,"_24":48,"_143":-5,"_27":168},{"_42":154,"_32":155,"_30":156,"_137":-5,"_40":41,"_39":157,"_139":25,"_37":158,"_13":159,"_33":141,"_35":160,"_24":48,"_143":-5,"_27":161},{"_42":145,"_32":146,"_30":147,"_137":-5,"_40":41,"_39":148,"_139":25,"_37":149,"_13":150,"_33":151,"_35":152,"_24":48,"_143":-5,"_27":153},{"_42":134,"_32":135,"_30":136,"_137":-5,"_40":41,"_39":138,"_139":25,"_37":140,"_13":62,"_33":141,"_35":142,"_24":48,"_143":-5,"_27":144},"8cd08fe4-e5b1-4488-8a86-9f854368740e","worker.ts","addbdfce-9693-11f1-9f88-1607ee4eb77e","parentId",430,"latest",["D",1786569644611],"interval","// 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","label",[135],"400e0890-331c-4469-a740-c498b1593074","rebuild.ts","351ab092-97ec-11f1-bc91-1607ee4eb77e",1344,["D",1787770080497],2,"script","// Shared body of the three morning rebuild intervals.\n//\n// One region per interval, not all three in one run: a full week of Florida is\n// ~40s on its own and Miami ~35s, so the combined run blew through Val Town's\n// 60s ceiling and left the last region unbuilt. The three crons are staggered\n// five minutes apart because the regions share the geocode cache, the distance\n// cache and OSRM's 1 req/sec limit - overlapping them would only make each one\n// slower.\nimport build, { mailFailure, REGIONS } from \"./main.ts\";\n\nexport async function rebuildRegion(key: string) {\n const region = REGIONS[key];\n const started = Date.now();\n try {\n const out = await build({ region: key, weekOffset: 0 });\n console.log(\n `${region.label}: ${out.totalStops} stops, ${(out.routes ?? []).length} routes, ` +\n `${out.unassignedCount ?? 0} unassigned in ${((Date.now() - started) / 1000).toFixed(1)}s`,\n );\n } catch (err) {\n // Still not rethrown: a Salesforce hiccup on one region is not a reason to\n // mark the interval failed. But it IS a reason to tell someone - a region\n // that quietly did not build leaves dispatch reading yesterday's sheet at\n // 6am and no one finds out until a truck is loaded wrong.\n const secs = ((Date.now() - started) / 1000).toFixed(1);\n console.log(`${region.label}: FAILED after ${secs}s - ${err}`);\n await mailFailure(\n `Route rebuild FAILED: ${region.label}`,\n [\n `The ${region.label} morning rebuild did not finish, ${secs}s in.`,\n \"\",\n \"The sheet and the map still hold whatever the last good run wrote, so\",\n \"they are STALE, not empty. The other regions build on their own crons and\",\n \"are unaffected. Re-running is safe: use Reoptimize on the map for this\",\n \"region, or wait for tomorrow's 5:30-5:40am ET run.\",\n ].join(\"\\n\"),\n err,\n );\n }\n}\n",[146],"c95501aa-1d27-4484-b558-d3eb6ae55abc","morning-miami.ts","3e1c3788-97ec-11f1-921b-1607ee4eb77e",648,["D",1786717657430],1,"// Miami morning rebuild - 5:35 AM ET, five minutes behind DC so the two are\n// not competing for OSRM's one-request-a-second budget.\n//\n// Week 0 only. Next week is a planning view and rebuilding it daily would\n// churn a plan nobody is driving yet - and would quietly discard any manual\n// moves made while looking ahead.\nimport { rebuildRegion } from \"./rebuild.ts\";\n\nexport default async function () {\n await rebuildRegion(\"miami\");\n}\n",[155],"e93e35aa-3a05-4d0d-9c51-2aa8730af9c2","morning-florida.ts","43132ec2-97ec-11f1-be77-1607ee4eb77e",649,["D",1786717661780],"// Florida morning rebuild - 5:40 AM ET. Last because it is the biggest: a full\n// week is ~230 stops and ~40s, which is most of Val Town's 60s ceiling on its\n// own. If this one ever starts timing out, that is the signal to move to a\n// paid plan or split the region.\n//\n// Week 0 only. Next week is a planning view and rebuilding it daily would\n// churn a plan nobody is driving yet - and would quietly discard any manual\n// moves made while looking ahead.\nimport { rebuildRegion } from \"./rebuild.ts\";\n\nexport default async function () {\n await rebuildRegion(\"florida\");\n}\n",[163],"cfb12e9f-5199-4f23-81bc-773fd8cffeca","morning-dc.ts","39e7c70e-97ec-11f1-ae3a-1607ee4eb77e",647,["D",1786717653194],"// DC / MD / VA morning rebuild - 5:30 AM ET. First of the three, and the\n// smallest, so dispatch has the DMV sheet earliest.\n//\n// Week 0 only. Next week is a planning view and rebuilding it daily would\n// churn a plan nobody is driving yet - and would quietly discard any manual\n// moves made while looking ahead.\nimport { rebuildRegion } from \"./rebuild.ts\";\n\nexport default async function () {\n await rebuildRegion(\"dc\");\n}\n",[170],"554e9913-a85f-4000-8565-1aaf10a33cb0","map.ts","84a05328-95bd-11f1-9462-1607ee4eb77e",1430,["D",1788201212666],425,"// 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 { email } from \"https://esm.town/v/std/email\";\nimport buildDcRoutes, {\n addDays,\n aggregateProducts,\n blobKeyFor,\n serviceVisits,\n todayEastern,\n enqueueRoute,\n getRegion,\n loadRemovedIds,\n committedStops,\n mailFailure,\n readJob,\n sfQuery,\n tomorrowEastern,\n saveRemovedIds,\n sfUpdateMany,\n todaysStops,\n type Aside,\n type Region,\n REGIONS,\n type MapSnapshot,\n type Overrides,\n OVERRIDES_KEY,\n SNAPSHOT_KEY,\n snapshotKeyFor,\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 // A header, for callers that are not a browser. Salesforce sends the key this\n // way so the secret never lands in a URL, a debug log or an org's callout\n // history. No cookie is issued - a server has nowhere to keep one.\n const header = req.headers.get(\"x-map-key\");\n if (header && secretsMatch(header, expected)) return { ok: true };\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\n// The company's own header logo, served from the company site. Kept as a URL\n// rather than inlined: it is 6.5KB that the browser caches once, and it stays\n// current if marketing ever changes it.\nconst LOGO_URL = \"https://tribecabeverage.com/wp-content/uploads/2023/04/TBC-logo-272x76-1.png\";\n\n// Top-right corner of every page: the logo alone. A title next to it only\n// repeated what the tab and the controls already say.\nconst brand = () =>\n `

$\"TriBeCa$

`;\n\nconst STYLES = `\n :root { color-scheme: light dark; --bg:#fff; --fg:#111827; --muted:#6b7280; --line:#e5e7eb; --panel:#f9fafb;\n --daybg:#dcfce7; --dayfg:#166534; --dayline:#86efac; --danger:#b91c1c; }\n @media (prefers-color-scheme: dark) {\n /* A lighter red on the dark ground - #b91c1c against #0f172a is unreadable,\n and a warning nobody can read is worse than no warning. */\n :root { --bg:#0f172a; --fg:#e2e8f0; --muted:#94a3b8; --line:#1e293b; --panel:#1e293b;\n --daybg:#14532d; --dayfg:#bbf7d0; --dayline:#166534; --danger:#fca5a5; }\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 /* padding-right reserves the corner so wrapping controls never run under the\n absolutely-positioned brand. */\n header { position:relative; padding:10px 16px; border-bottom:1px solid var(--line);\n display:flex; flex-wrap:wrap; gap:12px 20px; align-items:center;\n padding-right:140px; }\n h1 { font-size:15px; margin:0; font-weight:650; }\n .brand { position:absolute; top:9px; right:16px; display:flex; align-items:center; }\n .brand img { height:30px; width:auto; display:block; }\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 /* The map and the route panel share the row that used to be the map alone,\n so opening the panel narrows the map rather than covering it - a driver's\n stop list is no use if it hides the route it describes. */\n #stage { flex:1; min-height:0; display:flex; }\n #map { flex:1; min-height:0; background:var(--panel); position:relative; }\n #routepanel { width:330px; flex:none; overflow-y:auto; background:var(--bg);\n border-left:1px solid var(--line); padding:12px 14px 20px; }\n #routepanel h3 { margin:0 0 2px; font-size:14px; display:flex; align-items:center; gap:7px; }\n #routepanel .swatch { width:11px; height:11px; border-radius:50%; flex:none; }\n #routepanel .close { margin-left:auto; border:0; background:none; color:var(--muted);\n font:inherit; font-size:18px; line-height:1; cursor:pointer; padding:0 2px; }\n #routepanel ol { list-style:none; margin:10px 0 0; padding:0; }\n #routepanel li { display:flex; gap:9px; padding:6px 4px; border-radius:6px;\n border-top:1px solid var(--line); cursor:pointer; }\n #routepanel li:hover { background:var(--panel); }\n #routepanel .seq { flex:none; width:20px; text-align:right; font-weight:650;\n font-variant-numeric:tabular-nums; color:var(--muted); }\n #routepanel .who { font-weight:550; font-size:13px; }\n #routepanel .where { color:var(--muted); font-size:12px; }\n #routepanel .qty { margin-left:auto; flex:none; font-size:12px; font-weight:600;\n font-variant-numeric:tabular-nums; }\n #routepanel .swaprow { display:flex; gap:6px; margin-top:10px; }\n #routepanel .swaprow select { flex:1; min-width:0; font:inherit; font-size:12px; padding:4px 6px;\n background:var(--panel); color:var(--fg);\n border:1px solid var(--line); border-radius:7px; }\n #routepanel .swaprow button { font:inherit; font-size:12px; padding:4px 12px; border-radius:7px;\n cursor:pointer; border:1px solid var(--line);\n background:var(--panel); color:var(--fg); }\n #routepanel .swaprow button:hover { border-color:var(--fg); }\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 /* Remove is the only control in this bar that a second click cannot undo -\n Assign and Lock can both just be done again differently. It should not look\n like the two neutral buttons it sits between. */\n #selbar button.danger { color:var(--danger); border-color:var(--danger); }\n /* A mixed selection leaves some of these with nothing to act on. Faded and\n inert is better than a button that looks live and quietly does nothing to\n half of what is selected. */\n #selbar button:disabled { opacity:.38; cursor:default; }\n #selbar button:disabled:hover { 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 /* pre-line so the newlines in a confirmation survive. Every dialog here\n lists things - routes being sent, stops that break a rule - and set via\n textContent those breaks were being collapsed into one run-on paragraph. */\n #confirmbox p { margin:0 0 14px; font-size:13px; line-height:1.5; white-space:pre-line; }\n /* Red only when the dialog is telling you something is wrong. A send\n confirmation and a rule you are about to break should not look alike. */\n #confirmbox p.warn { color:var(--danger); font-weight:550; }\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 /* A column per day: the date heads the column and its trucks stack beneath,\n with the next day to the right. The date is named once instead of once per\n vehicle, and a day with no route contributes no column at all. */\n .legend { display:flex; flex-direction:row; flex-wrap:wrap; align-items:flex-start;\n gap:8px 20px; font-size:12px; }\n .legend-day { display:flex; flex-direction:column; align-items:flex-start; gap:3px; }\n /* Green banner across the top of each day's column. align-self:stretch makes\n it span the full column width whatever the trucks underneath are called,\n so the banners read as a header row. */\n .legend-daylabel { font:inherit; font-weight:650; font-variant-numeric:tabular-nums;\n color:var(--dayfg); background:var(--daybg);\n border:1px solid var(--dayline); border-radius:5px;\n padding:2px 8px; margin-bottom:2px; align-self:stretch;\n text-align:center; letter-spacing:.01em; cursor:pointer; }\n .legend-daylabel:hover { border-color:var(--dayfg); }\n /* The day being shown on its own. Inverted so it is obvious at a glance which\n one the map is filtered to, and that the others are hidden rather than\n absent. */\n .legend-daylabel[aria-pressed=\"true\"] { background:var(--dayfg); color:var(--daybg);\n border-color:var(--dayfg); }\n .legend-all { font:inherit; font-size:12px; font-weight:600; padding:3px 10px;\n border-radius:6px; cursor:pointer; border:1px solid #0f766e;\n background:#0d9488; color:#f0fdfa; align-self:center; }\n .legend-all:hover:not(:disabled) { background:#0f766e; }\n .legend-all:disabled { cursor:default; }\n .legend label { display:inline-flex; align-items:center; gap:5px; cursor:pointer; }\n /* Reads as a link rather than a button: it opens a list, it does not change\n the plan, and everything in this legend that does change something is a\n box or a chip. */\n .routelink { font:inherit; padding:0; border:0; background:none; color:inherit;\n cursor:pointer; text-decoration:underline; text-decoration-style:dotted;\n text-underline-offset:2px; text-decoration-color:var(--muted); }\n .routelink:hover { text-decoration-style:solid; text-decoration-color:currentColor; }\n .routelink[aria-pressed=\"true\"] { font-weight:650; text-decoration-style:solid; }\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 /* Day picker sits against its button so they read as one control. */\n .optimo { display:inline-flex; align-items:center; gap:0; }\n .optimo select { font:inherit; font-size:12px; padding:4px 6px; color:var(--fg);\n background:var(--panel); border:1px solid var(--line);\n border-right:0; border-radius:7px 0 0 7px; }\n .optimo button { border-radius:0 7px 7px 0; }\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 .move-row button.danger { background:transparent; color:#b91c1c; border-color:#fca5a5; width:100%; }\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 /* The brand shrinks but stays in the corner; the reserved gutter shrinks\n with it so the controls keep the rest of the width. */\n header { padding-right:96px; }\n .brand { top:7px; right:10px; }\n .brand img { height:22px; }\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 /* No room for a column beside the map on a phone, so the panel becomes a\n drawer under it and the map keeps the space it had. */\n #stage { flex-direction:column; }\n #routepanel { width:auto; max-height:38vh; border-left:0; border-top:1px solid var(--line); }\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// The overdue service map. A plot and nothing else: no trucks, no sequence,\n// nothing written back. Its own page rather than a fourth REGIONS entry,\n// because everything the region machinery does - packing, snapshots, commits -\n// is meaningless for a service visit.\nfunction renderServicePage(data: {\n visits: any[];\n date: string;\n from: string;\n to: string;\n daysBack: number;\n daysAhead: number;\n skipped: { account: string; why: string }[];\n}) {\n const { visits, date, from, to, daysBack, daysAhead, skipped } = data;\n const esc = (v: unknown) =>\n String(v ?? \"\").replace(/[&<>]/g, (c) => ({ \"&\": \"&\", \"<\": \"<\", \">\": \">\" }[c]!));\n\n // daysOverdue is signed - negative is not yet due. Banded rather than a\n // gradient because these are four different jobs, not one scale: badly late\n // is a phone call, just late is this week's round, due soon is next week's,\n // and the rest is only here for planning.\n const BANDS = [\n { min: 31, color: \"#dc2626\", label: \"over a month late\" },\n { min: 1, color: \"#f97316\", label: \"late\" },\n { min: -14, color: \"#eab308\", label: \"due within 2 weeks\" },\n { min: -Infinity, color: \"#3b82f6\", label: \"later\" },\n ];\n const bandOf = (d: number) => BANDS.findIndex((b) => d >= b.min);\n\n const nav = Object.values(REGIONS).map((r) =>\n ``\n ).join(\"\");\n const counts = BANDS.map((b, i) => visits.filter((v) => bandOf(v.daysOverdue) === i).length);\n const legend = BANDS.map((b, i) =>\n `${b.label} (${counts[i]})`\n ).join(\"\");\n\n // \"+12d\" is twelve days late, \"-5d\" is due in five. Late is coloured; not-yet\n // is not, so the list scans as a worklist rather than a table of numbers.\n const rows = [...visits]\n .sort((a, b) => b.daysOverdue - a.daysOverdue)\n .map((v) =>\n `` +\n ` 0 ? \"late\" : \"\"}\">${\n v.daysOverdue > 0 ? `+${v.daysOverdue}` : v.daysOverdue\n }d` +\n `${esc(v.accountName)}

${esc(v.serviceType ?? \"\")}

` +\n `${esc(v.dueDate)}`\n ).join(\"\");\n const late = visits.filter((v) => v.daysOverdue > 0).length;\n\n return html(`${PAGE_HEAD}\n

\n ${brand()}\n

Service visits

Controls

${nav}

\n ${visits.length} due${late ? `, ${late} late` : \"\"} · ${daysBack}d back to ${daysAhead}d ahead (${\n esc(from)\n } - ${esc(to)})${skipped.length ? ` · ${skipped.length} unmappable` : \"\"}\n

${legend}

\n \n

${\n visits.length\n ? `${rows}

`\n : `

No service visits due between ${esc(from)} and ${esc(to)}.

`\n }

\n\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 // What is on each truck, biggest line first - the same question the routing\n // map's load list answers, but for trucks already out.\n //\n // The quantity shown is what is STILL on board, not what was loaded at the\n // warehouse. This view is live and re-read all day as drivers mark drops\n // delivered, so \"20 x 5 GAL Spring\" on a truck that has already dropped 12 of\n // them is not a useful thing to tell anybody. The loaded figure is kept\n // beside it wherever the two differ, so the morning number is still there.\n const loads = routes.map((rn) => {\n const rows = stops.filter((s) => s.routeName === rn);\n const outstanding = rows.filter((s) => !s.delivered);\n const loaded = aggregateProducts(rows);\n const leftByName = new Map(aggregateProducts(outstanding).map((p) => [p.name, p.qty]));\n const units = rows.reduce((a, s) => a + s.units, 0);\n const leftUnits = outstanding.reduce((a, s) => a + s.units, 0);\n return `

${esc(rn)} ${\n leftUnits === units ? `${units} units` : `${leftUnits} of ${units} units left`\n }

\n ${\n loaded.map((p) => {\n const left = leftByName.get(p.name) ?? 0;\n return ``;\n }).join(\"\")\n }

${left}

${\n left === p.qty ? \"\" : `of ${p.qty}`\n }

${esc(p.name)}

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

\n ${brand()}\n

Controls

${nav}

\n \n \n \n

Today · ${esc(label)} · ${date} ·\n ${stops.length} stops · ${units} units ·\n ${delivered} delivered, ${stops.length - delivered} outstanding

\n\n\n`);\n}\n\n// Only for a genuinely empty snapshot - nothing built, or built and found\n// nothing. A plan whose stops were all REMOVED does not come here: it still has\n// grey dots worth drawing, so it renders the real map with a restore banner.\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\n // Which orders this plan is actually considering, by their own due date.\n //\n // The demand window is cumulative rather than a slice of the week on screen,\n // so a plan for the 24th routinely carries orders due weeks earlier. Saying\n // so on the page saves the question \"does next week include this week's\n // work\" - and shows at a glance how far back the overdue tail runs.\n const dueDates = snap.stops.map((s) => s.closeDate).filter((d): d is string => !!d).sort();\n const overdue = dueDates.filter((d) => d < snap.today).length;\n // Two different ranges, and confusing them is easy. The queried range is what\n // was asked of Salesforce; the found range is the due dates that came back.\n // \"Oldest order is three days old\" and \"we only looked back three days\" read\n // identically from the stops alone, so both are printed.\n const queried = snap.windowStart\n ? `Close dates queried ${snap.windowStart} to ${snap.windowEnd}`\n : `Window ends ${snap.windowEnd}`;\n const dueRange = dueDates.length === 0\n ? `${queried} · nothing found.`\n : `${queried} · found ${dueDates[0]} to ${dueDates[dueDates.length - 1]}` +\n (overdue > 0 ? ` · ${overdue} already past due` : \"\") +\n `.`;\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)} · ${\n esc((snap.vehicleNames ?? [])[r.vehicle - 1] ?? `Vehicle ${r.vehicle}`)\n }\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

\n ${brand()}\n

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

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

\n ${\n // Every stop taken off. The map still draws them in grey, so the plan is\n // readable, but the one thing wanted at that point is the way back - and a\n // rebuild will not do it, since removals outlive the build.\n snap.stops.length === 0 && snap.removed?.length\n ? `

\n All ${snap.removed.length} stop(s) on this plan have been removed from the route.\n They stay off until the delivery window rolls forward; rebuilding will not bring\n them back.\n \n

`\n : \"\"\n }\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 // The OpenAPI spec is the one thing served without the key.\n //\n // Not a choice - a requirement. Salesforce's External Services fetches the\n // schema during REGISTRATION, before any operation runs, and that fetch does\n // not carry the External Credential's custom headers. Gating it returned 401\n // and registration failed with \"an error was encountered while trying to\n // retrieve the service schema\", which names neither the status nor the cause.\n //\n // Safe to leave open: the document describes one endpoint's shape and holds\n // no data and no secret, and /push-optimo itself is still gated. Anyone who\n // finds this URL learns that a push endpoint exists and nothing else.\n const gate: Gate = pathname === \"/openapi.json\" ? { ok: true } : 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 // Act on the week the page is showing, not always this one.\n const postWeek = Number(url.searchParams.get(\"week\")) || 0;\n if (pathname === \"/move\") {\n const ids = Array.isArray(body.stopIds) ? body.stopIds : [body.stopId].filter(Boolean);\n return Response.json(\n await moveStops(region, ids, body.date, Number(body.vehicle), postWeek),\n );\n }\n if (pathname === \"/swap\") {\n const pick = (v: any) =>\n v && typeof v.date === \"string\" && Number.isFinite(Number(v.vehicle))\n ? { date: v.date as string, vehicle: Number(v.vehicle) }\n : null;\n const a = pick(body.a);\n const b = pick(body.b);\n if (!a || !b) {\n return Response.json({ ok: false, error: \"two routes are needed\" }, { status: 400 });\n }\n return Response.json(await swapRoutes(region, a, b, postWeek));\n }\n if (pathname === \"/remove-stop\") {\n // Takes either shape: `stopId` from a stop's own popup, `stopIds` from a\n // lasso selection. Kept on one path so both go through the same\n // all-or-nothing removal rather than the bulk one growing its own rules.\n const ids = Array.isArray(body.stopIds)\n ? body.stopIds.filter((id: unknown): id is string => typeof id === \"string\" && !!id)\n : typeof body.stopId === \"string\" && body.stopId\n ? [body.stopId]\n : [];\n if (ids.length === 0) {\n return Response.json({ ok: false, error: \"no stop given\" }, { status: 400 });\n }\n return Response.json(await removeStops(region, ids, postWeek));\n }\n if (pathname === \"/restore-removed\") {\n return Response.json(await restoreRemoved(region, postWeek));\n }\n if (pathname === \"/restore-stop\") {\n // Same two shapes as /remove-stop: `stopId` from a grey dot's popup,\n // `stopIds` from a lasso.\n const ids = Array.isArray(body.stopIds)\n ? body.stopIds.filter((id: unknown): id is string => typeof id === \"string\" && !!id)\n : typeof body.stopId === \"string\" && body.stopId\n ? [body.stopId]\n : [];\n if (ids.length === 0) {\n return Response.json({ ok: false, error: \"no stop given\" }, { status: 400 });\n }\n return Response.json(await restoreStops(region, ids, postWeek));\n }\n if (pathname === \"/optimo\") {\n if (!Array.isArray(body.routes) || body.routes.length === 0) {\n return Response.json({ ok: false, error: \"no routes given\" }, { status: 400 });\n }\n const picks = body.routes\n .filter((r: any) => typeof r?.date === \"string\" && Number.isFinite(Number(r?.vehicle)))\n .map((r: any) => ({ date: r.date as string, vehicle: Number(r.vehicle) }));\n if (picks.length === 0) {\n return Response.json({ ok: false, error: \"no valid routes given\" }, { status: 400 });\n }\n return Response.json(await optimoSend(region, postWeek, picks));\n }\n if (pathname === \"/salesforce\") {\n const picks = (Array.isArray(body.routes) ? body.routes : [])\n .filter((r: any) => typeof r?.date === \"string\" && Number.isFinite(Number(r?.vehicle)))\n .map((r: any) => ({ date: r.date as string, vehicle: Number(r.vehicle) }));\n if (picks.length === 0) {\n return Response.json({ ok: false, error: \"no valid routes given\" }, { status: 400 });\n }\n return Response.json(\n await salesforceCommit(region, postWeek, picks, body.overwrite === true),\n );\n }\n // All regions, one date. Called by the Salesforce home-page button, so\n // it takes no region and defaults to tomorrow - the only two things a\n // dispatcher pressing it at the end of the day could mean.\n if (pathname === \"/push-optimo\") {\n const when = typeof body.date === \"string\" && /^\\d{4}-\\d{2}-\\d{2}$/.test(body.date)\n ? body.date\n : tomorrowEastern();\n const only = typeof body.routeName === \"string\" ? body.routeName : undefined;\n return Response.json(await pushCommittedToOptimo(when, only));\n }\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 // Queue a build instead of running it inline. A full-week build for a\n // large region takes ~68s and the HTTP request is killed before it can\n // finish, so this hands the job to worker.ts and returns immediately.\n if (pathname === \"/route-week\") {\n const weekOffset = Number(body.weekOffset) || 0;\n await clearUnlockedMoves(region);\n const job = await enqueueRoute(region.key, weekOffset);\n return Response.json({ ok: true, job });\n }\n if (pathname === \"/route-status\") {\n const weekOffset = Number(body.weekOffset) || 0;\n return Response.json({ ok: true, job: await readJob(region.key, weekOffset) });\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, so it does not overwrite what the trucks are working from.\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 // The OpenAPI description Salesforce registers this endpoint from.\n //\n // Served from here rather than pasted into Setup by hand, so it cannot drift\n // from the response this file actually returns. Salesforce registers against\n // it by relative URL on the Named Credential.\n //\n // Reachable without the key - see the note at the gate for why that is forced\n // rather than chosen.\n //\n // Kept to what External Services accepts, which is less than OpenAPI allows:\n // an operationId on every operation, no oneOf/anyOf/allOf, every property\n // typed, and - the one that actually bit - NO NESTED ARRAY OF OBJECTS. The\n // first version described `summary` as an array of per-region objects and\n // registration failed with \"an error was encountered while trying to retrieve\n // the service schema\", which says nothing about schemas being the problem.\n // Hence the flat shape here and the `summaryText` string alongside it.\n //\n // `servers` is spelled out even though the Named Credential supplies the host,\n // because the validator wants somewhere to resolve relative paths against.\n //\n // It names the tribeca-route-map.val.run subdomain, NOT the default\n // tribeca_rep--.web.val.run one. Salesforce Remote Site Settings refuses\n // to save a URL whose hostname contains an underscore (\"Unable to parse\n // URI\"), and registering an external service needs that remote site because\n // the schema fetch does not go through the Named Credential. Both hostnames\n // serve this same file; only the hyphenated one is usable from Salesforce.\n if (pathname === \"/openapi.json\") {\n return Response.json({\n openapi: \"3.0.0\",\n info: { title: \"Tribeca Route Push\", version: \"1.0.0\" },\n servers: [{ url: \"https://tribeca-route-map.val.run\" }],\n paths: {\n \"/push-optimo\": {\n get: {\n operationId: \"pushOptimo\",\n summary: \"Send committed Route Dates to OptimoRoute for every region\",\n parameters: [\n {\n name: \"date\",\n in: \"query\",\n required: false,\n description: \"Route Date to send, as YYYY-MM-DD. Omit for tomorrow, Eastern.\",\n schema: { type: \"string\" },\n },\n {\n name: \"routeName\",\n in: \"query\",\n required: false,\n description:\n \"Optional. One truck only, e.g. DC Van, Miami 2, SP, TPA. Omit to send every truck committed to that date.\",\n schema: { type: \"string\" },\n },\n ],\n responses: {\n \"200\": {\n description: \"What was sent\",\n content: {\n \"application/json\": {\n schema: {\n type: \"object\",\n properties: {\n ok: { type: \"boolean\" },\n date: { type: \"string\" },\n sent: { type: \"integer\" },\n error: { type: \"string\" },\n summaryText: { type: \"string\" },\n },\n },\n },\n },\n },\n },\n },\n },\n },\n });\n }\n\n // Same push, over GET. Flow's HTTP Callout does not offer POST in every org,\n // and this saves that being the thing that blocks the button. Still gated by\n // the same key, so nothing can reach it by wandering past.\n if (pathname === \"/push-optimo\") {\n const asked = url.searchParams.get(\"date\");\n const when = asked && /^\\d{4}-\\d{2}-\\d{2}$/.test(asked) ? asked : tomorrowEastern();\n return Response.json(\n await pushCommittedToOptimo(when, url.searchParams.get(\"routeName\") ?? undefined),\n );\n }\n\n // Overdue equipment service. Region-less by design - see renderServicePage.\n if (url.searchParams.get(\"view\") === \"service\") {\n try {\n // Overridable so a longer look-back can be asked for without a deploy.\n const back = Number(url.searchParams.get(\"back\"));\n const ahead = Number(url.searchParams.get(\"ahead\"));\n const page = renderServicePage(\n await serviceVisits(\n Number.isFinite(back) && back > 0 ? back : 60,\n Number.isFinite(ahead) && ahead > 0 ? ahead : 30,\n ),\n );\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(`service view failed: ${String(err)}`);\n return storageErrorPage();\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 // Rebuild when there is nothing to DRAW, not merely when the snapshot is\n // missing. Restoring every removed stop leaves a snapshot that exists but\n // holds nothing - no stops, and no grey dots either, because restoring is\n // what clears those. The old `!snap` test walked straight past that state,\n // so the map sat blank until the next morning's cron, and the page told\n // people to run a build that would not have helped.\n //\n // An empty plan is precisely the case that wants building.\n if (!snap || ((snap.stops?.length ?? 0) === 0 && (snap.removed?.length ?? 0) === 0)) {\n console.log(`Nothing to draw 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 // A plan whose stops have all been removed still has something to show: the\n // grey dots. Rendering the map is far more use than a paragraph saying the\n // map is empty - you can see WHERE the work was, and put back the ones you\n // did not mean to drop. Only a genuinely empty snapshot falls through to the\n // text page.\n const hasSomethingToDraw = !!snap &&\n ((snap.stops?.length ?? 0) > 0 || (snap.removed?.length ?? 0) > 0);\n const page = hasSomethingToDraw ? renderPage(snap!) : emptyPage();\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",[177],"709f4354-8592-4a96-b5be-3c101360be31","main.ts",1432,["D",1789068165230],479,"// 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\";\nimport { email } from \"https://esm.town/v/std/email\";\n\n// ---------------------------------------------------------------------------\n// Failure reporting\n// ---------------------------------------------------------------------------\n//\n// The two things that matter here - the OptimoRoute push and the morning\n// rebuild crons - both used to fail into the log and nowhere else. The push on\n// 2026-08-27 returned a bare 500 and told nobody; it was found by reading\n// traces. The crons still deliberately swallow a bad region so that one\n// Salesforce hiccup cannot fail a whole interval, which is right, but\n// \"swallowed\" was being read by everyone as \"did not happen\".\n//\n// Nothing below changes what either one DOES. It only means a person hears.\n\n// Plain English for the failures this val has actually hit, so the first line\n// of the email says where to look. Anything unrecognised says exactly that\n// rather than guessing: a confident wrong explanation costs more time than none.\nexport function explainFailure(message: string): string {\n if (/SOQL query failed: 414/.test(message)) {\n return \"A Salesforce query outgrew the 16 KB limit on a request URL. That happens when one \" +\n \"route date carries far more orders than usual - 2026-08-27 had 601. Account lookups go 200 \" +\n \"ids at a time for exactly this reason, so if it is back, look for a query building an \" +\n \"Id IN (...) list without going through sfQueryByIds.\";\n }\n if (/SOQL query failed: 40[13]/.test(message)) {\n return \"The Salesforce proxy refused the request. Check SALESFORCE_AGENT_KEY on this val \" +\n \"against API_SHARED_SECRET on tribeca-salesforce-agent.\";\n }\n if (/SOQL query failed: 4\\d\\d/.test(message)) {\n return \"Salesforce rejected the query itself - usually a field this connection cannot read, \" +\n \"or one that has been renamed in the org. The error below carries Salesforce's own wording.\";\n }\n if (/SOQL query failed: 5\\d\\d/.test(message)) {\n return \"Salesforce or the proxy val was unreachable. This is usually transient - run it again \" +\n \"before digging.\";\n }\n if (/Salesforce update failed/.test(message)) {\n return \"Writing Route Date / Route Name back to Salesforce failed. A validation rule or a \" +\n \"field permission on Opportunity is the usual cause, and the per-record verdicts are below.\";\n }\n if (/OPTIMOROUTE_API_KEY/.test(message)) {\n return \"OPTIMOROUTE_API_KEY is not set on this val, so there was nothing to push to.\";\n }\n if (/OSRM|distance matrix/i.test(message)) {\n return \"The OSRM public routing server did not answer. It is a free demo host and does go \" +\n \"down; the run can simply be repeated.\";\n }\n if (/timed out|timeout|deadline/i.test(message)) {\n return \"The run hit Val Town's time limit. A full week for a large region already runs close \" +\n \"to it, so this usually means an unusually big day rather than a broken query.\";\n }\n return \"No known pattern matched this one, so there is no shortcut - the error and stack below \" +\n \"are the whole story, and the val's logs have the run around them.\";\n}\n\n// One email per failure, and never at the caller's expense.\n//\n// A mail problem must not change what happens next: a push that failed still has\n// to return its 500 so the Salesforce Flow takes its fault path, and a cron that\n// swallowed a bad region still has to swallow it.\nexport async function mailFailure(subject: string, context: string, err: unknown): Promise {\n const message = err instanceof Error ? err.message : String(err);\n const where = err instanceof Error && err.stack\n ? err.stack.split(\"\\n\").slice(1, 7).join(\"\\n\")\n : \"(no stack)\";\n try {\n await email({\n subject,\n text: [\n context,\n \"\",\n \"WHAT THIS USUALLY MEANS\",\n explainFailure(message),\n \"\",\n \"ERROR\",\n message,\n \"\",\n \"WHERE\",\n where,\n ].join(\"\\n\"),\n });\n } catch (mailErr) {\n console.log(` failure email failed: ${mailErr}`);\n }\n}\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 Orientation = \"radial\" | \"east-west\" | \"north-south\" | \"zoned\";\n\nexport type Region = {\n key: string;\n label: string;\n salesforceRegions: string[];\n sheetTab: string;\n vehicleCapacities: number[];\n // No windowStart here any more: how far back to look is derived from the week\n // being planned, not configured. See OVERDUE_LOOKBACK_MONTHS.\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 // How stops are grouped into trucks.\n // radial - sweep by compass angle from the depot (the default)\n // east-west - stack latitude bands, so each route runs coast to inland\n // north-south- stack longitude columns, so each route runs up the coast\n // zoned - grow each driver a territory, filled to near capacity\n // A radial sweep fans wedges out from the depot, which in a long thin metro\n // like Miami produces routes that run the length of the strip. Banding gives\n // compact routes with a consistent orientation instead.\n //\n // Both of those partition space EVENLY, which is wrong wherever the region\n // has a hole in it: Orlando sits 170km from the Tampa depot with nothing in\n // between, yet lands in the same wedge as north Tampa. Zoning grows each\n // driver a compact territory instead, adding whichever stop lengthens their\n // day's path least until the truck is near full.\n routeOrientation?: Orientation;\n // Hard ceiling on stops per truck per day, whatever the units add up to.\n // A 43-stop day is not a real day's work even when it fits the cube.\n maxStopsPerRoute?: number;\n // Front-loads the week. 0 packs every day equally; 0.4 aims the last day of\n // the week at 60% of the first day's capacity, leaving room later in the week\n // for call-ins and new customers that land after the plan is built.\n //\n // It is a target, not a limit: if honouring it would push stops off the\n // schedule entirely the build relaxes it step by step until everything fits.\n frontLoadFactor?: number;\n // Use as few truck-days as the work actually needs, earliest days first.\n //\n // The default spreads the week's stops across every available truck-day, which\n // keeps each day short but sends a van out five days running. This packs\n // instead: fill Monday's trucks, then Tuesday's, and stop as soon as the book\n // fits. Fewer routes, and the back of the week stays clear for call-ins.\n //\n // Supersedes frontLoadFactor where set - the front-loading comes from which\n // days get used at all, so capping the later days as well would only make the\n // trucks that do go out emptier.\n minimiseRoutes?: boolean;\n // How many of vehicleCapacities are trucks the company actually owns. The\n // rest are hired in and cost money per day, so they are used only when the\n // owned fleet genuinely cannot carry the week - after every owned truck-day\n // has been filled, and after Saturday has been opened. Defaults to all.\n ownedVehicles?: number;\n // What dispatch calls each truck, positionally matching vehicleCapacities.\n // \"v1\"/\"v2\" mean nothing to a driver or to OptimoRoute; these are the names\n // the routes actually go out under.\n vehicleNames?: string[];\n // A road that is cheap to drive along and expensive to cross, so the packer\n // should keep each truck to one side of it where it reasonably can. See\n // penaliseBarrierCrossings. Absent on regions with no such divide.\n barrier?: {\n name: string;\n line: [number, number][];\n crossPenaltyKm: number;\n };\n // Named territories, for a region divided by more than one barrier. Same\n // mechanism as `barrier` - legs between zones are made to look longer - just\n // with ten zones rather than two sides. A region uses one or the other.\n territories?: {\n spec: typeof MIAMI_TERRITORIES;\n crossPenaltyKm: number;\n };\n};\n\n// I-95 through South Florida, south to north. Declared here rather than with the\n// rest of the barrier code further down because REGIONS references it, and a\n// const is not hoisted - reaching it from there would throw at module load.\n//\n// I-95 runs very nearly north-south, so one longitude per latitude is enough to\n// say which side of it a point is on. These waypoints were checked against the\n// real Miami book before anything was built on them: 12 Miami Beach / Collins /\n// Ocean Drive stops all came out east, 17 Weston / Coral Springs / Parkland /\n// Sunrise / Plantation / Davie / Miramar stops all west, nothing misclassified.\n// Redo that check if these are ever edited - a centreline that is subtly wrong\n// produces worse routes silently, with nothing in the logs to say so.\nexport const I95_SOUTH_FLORIDA: [number, number][] = [\n [25.7743, -80.1937], // southern terminus at US-1, downtown Miami\n [25.8100, -80.2050],\n [25.8500, -80.2100],\n [25.9000, -80.2110],\n [25.9400, -80.2080],\n [25.9800, -80.1930],\n [26.0100, -80.1730],\n [26.0600, -80.1690],\n [26.1200, -80.1690], // Fort Lauderdale\n [26.1700, -80.1560],\n [26.2300, -80.1330], // Pompano Beach, alongside the depot\n [26.2900, -80.1180],\n [26.3600, -80.1030], // Boca Raton\n [26.4600, -80.0830],\n [26.5600, -80.0680],\n [26.7100, -80.0680], // West Palm Beach\n [26.8400, -80.0850],\n [26.9300, -80.1090], // Jupiter\n];\n\n// The Intracoastal Waterway, south to north. The second line a truck cannot\n// cross freely here, and the harder of the two: east of it is barrier island,\n// reachable only over a bridge. Wide through Biscayne Bay, narrow by Fort\n// Lauderdale, which is why it is a line of its own rather than an offset of I-95.\n//\n// Checked the same way as I-95, against the real Miami book: it puts 14 stops on\n// the islands, and every Miami Beach / Collins / Ocean Drive address east of it.\n// It will misplace a handful of private islands that sit west of the main\n// channel - Williams Island in Aventura is the known one - because they are\n// bridge-access but not east of the waterway. A line test cannot catch those.\nexport const ICW_SOUTH_FLORIDA: [number, number][] = [\n [25.70, -80.160],\n [25.79, -80.145],\n [25.85, -80.140],\n [25.93, -80.122], // Biscayne Bay is at its widest here\n [26.02, -80.115],\n [26.12, -80.105], // Fort Lauderdale\n [26.23, -80.085],\n [26.36, -80.070], // Boca Raton\n [26.50, -80.060],\n [26.71, -80.045], // Palm Beach\n [26.85, -80.040],\n [26.95, -80.080],\n];\n\n// Ten delivery territories for South Florida.\n//\n// Two cuts, in order. First the corridor: east of the Intracoastal is island,\n// between the Intracoastal and I-95 is coastal, west of I-95 is inland. Then\n// latitude, splitting each corridor into route-sized pieces.\n//\n// The corridor cut is the one that carries meaning - it is where the crossings\n// are expensive. The latitude cuts are just balance, and were taken from the\n// equal-count quantiles of a real week (196 stops: 14 island, 79 coastal, 103\n// inland) then rounded to boundaries a dispatcher would recognise.\nexport const MIAMI_TERRITORIES = {\n name: \"South Florida territories\",\n icw: ICW_SOUTH_FLORIDA,\n i95: I95_SOUTH_FLORIDA,\n coastalCuts: [25.95, 26.15, 26.40],\n inlandCuts: [25.95, 26.20, 26.31, 26.55],\n // Index order matches territoryOf: 0 island, 1-4 coastal south to north,\n // 5-9 inland south to north.\n labels: [\n \"Beaches\",\n \"Miami / Coral Gables coastal\",\n \"Hollywood / Aventura coastal\",\n \"Fort Lauderdale / Pompano coastal\",\n \"Boca / Palm Beach coastal\",\n \"Miami-Dade inland\",\n \"Broward south inland\",\n \"Broward central inland\",\n \"Coral Springs / Parkland\",\n \"Palm Beach inland\",\n ],\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 // These strings are the driver SERIAL in OptimoRoute, not display labels -\n // \"DC Van\" and \"DC Van-2\" are real drivers there. Renaming either one\n // silently unassigns every order sent for it, so they must match exactly.\n // DC Van-2 is hired in by the day; ownedVehicles below is what marks it as\n // a last resort rather than half the fleet.\n vehicleNames: [\"DC Van\", \"DC Van-2\"],\n ownedVehicles: 1,\n includeSaturday: true,\n // Same territory-growing as Florida. The DMV has the same shape of problem:\n // a dense core with outlying work (Baltimore, the Eastern Shore) that a\n // radial sweep folds into whichever wedge it happens to fall in.\n //\n routeOrientation: \"zoned\",\n // DC only half-fills its fleet - ~513 units against 1000 - so spreading the\n // week put a van on the road five days running, twice for a single stop.\n // Pack it instead: fewest truck-days the book needs, earliest days first,\n // and the rest of the week stays clear.\n minimiseRoutes: true,\n },\n miami: {\n key: \"miami\",\n label: \"Miami\",\n salesforceRegions: [\"Miami\", \"New Miami\"],\n sheetTab: \"Miami\",\n // Order matters: it lines up with vehicleNames below. \"Miami 2\" is the\n // 192-unit truck, \"Miami Van\" the 100 - not the other way round.\n vehicleCapacities: [192, 100],\n vehicleNames: [\"Miami 2\", \"Miami Van\"],\n depotAddress: \"1661 N Dixie Hwy #6, Pompano Beach, FL 33060\",\n includeSaturday: true,\n // South Florida is a ~100 mile north-south strip only ~20 miles deep, with\n // the depot in the middle of it. A radial sweep hands each truck a long run\n // up or down the coast; east-west banding gave each a horizontal slice\n // instead. Zoning supersedes both - it finds the clumps the strip actually\n // has rather than cutting it into equal slices, and the band orientation\n // stops being something to configure.\n routeOrientation: \"zoned\",\n // Ten territories rather than the two sides of I-95 this started as. The\n // Intracoastal turned out to matter more than the highway - a bridge is a\n // harder thing to cross than an interchange - and once both lines are in,\n // the corridors they carve want splitting by latitude anyway, because the\n // region is a 150km strip only 30km deep.\n //\n // See penaliseZoneCrossings for the mechanism; it is the same soft penalty,\n // just with ten zones instead of two.\n //\n // OFF (0) deliberately, and this is the interesting part. Measured on the\n // 2026-08-31 book, 196 stops and 1119 units against 1168 of fleet capacity:\n //\n // penalty unassigned crossings\n // 0km 0 21\n // 3km 18 24\n // 5km 18 26\n // 8km 10 22\n // 12km 9 16\n // 18km 11 30\n // 25km 13 22\n //\n // Every non-zero setting strands deliveries, and none of them reliably\n // reduces crossings - 16 to 30 is noise, not a trend. At 96% of fleet\n // capacity the packer has no slack to spend on geography: each stop has\n // essentially one home it can fit in, so leaning on the shape of the region\n // only pushes work off the end of the week. Same effect the taper comment\n // further down describes.\n //\n // So this is not a knob to turn up until it works. It becomes useful when\n // there is room to move - another truck-day, a Saturday, or clearing the ~29\n // stops held back on balance. Re-run the sweep then; the mechanism is built\n // and tested, it is the book that cannot afford it yet.\n //\n // That sweep was run on two zones. Ten zones is a stronger constraint, not a\n // weaker one - more pairs are cross-zone - so it should be swept again from\n // a low value before being trusted, not set to whatever two zones tolerated.\n territories: { spec: MIAMI_TERRITORIES, crossPenaltyKm: 0 },\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 vehicleNames: [\"SP\", \"TPA\"],\n depotAddress: \"6531 43rd St N #1608, Pinellas Park, FL 33781\",\n includeSaturday: true,\n // Tampa Bay is not one blob. Orlando sits 170km east with nothing between,\n // and a sweep put it in the same wedge as north Tampa - one truck-day with\n // a 171km leg in the middle of it. Zoning gives each driver a territory\n // instead, so the far book costs one run out and back rather than four.\n routeOrientation: \"zoned\",\n // Monday heavy, Saturday light - achieved by which zone goes on which day,\n // not by capping the later days.\n //\n // The taper is the wrong lever here. Tapering by t leaves 200 * (6 - 3t)\n // units of capacity and Florida carries ~1078, so even t = 0.15 runs the\n // fleet at 97% - and at that point every stop has exactly one possible home,\n // geography stops mattering, and 28% of stops end up nearer another truck\n // than their own. Zones need slack.\n //\n // Left flat, the fleet runs at 90% and the heaviest zone is simply assigned\n // to the earliest slot, which gives the same shape for free: measured on the\n // week of 2026-08-17, 193 units Monday declining to 139 on Saturday, with\n // interleave at 14% instead of 28%.\n frontLoadFactor: 0,\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 // The close-date range the Salesforce query actually asked for - not the\n // range of what came back. Worth showing separately: \"oldest order found is\n // three days old\" and \"we only looked back three days\" mean opposite things,\n // and the map cannot tell them apart from the stops alone.\n windowStart?: string;\n windowEnd: string;\n weekOffset: number; // 0 = rest of this week, 1 = next week\n depot: { lat: number; lon: number };\n // Positional, matching vehicle 1..n, so the map can show \"Miami 2\" instead\n // of \"v1\". Absent on snapshots built before names existed.\n vehicleNames?: string[];\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 // Optional: snapshots built before these were carried through will not\n // have them, and the map falls back to hiding the line.\n closeDate?: string | null;\n createdDate?: string | null;\n // Carried purely so the OptimoRoute payload matches what the Cloud Run\n // bridge sends. Optional: older snapshots predate them.\n bottles?: number;\n springBottles?: number;\n waterType?: string | null;\n apt?: string | null;\n phone?: string | null;\n email?: string | null;\n orderSummary?: string | null;\n allowedDaysRaw?: string | null;\n precision: \"exact\" | \"approx\";\n locked: boolean;\n // Why a padlock is showing, when it is one of these rather than a hand-set\n // lock: the Route Date the stop is committed to in Salesforce, and whether\n // the customer has been texted it. Optional - snapshots built before these\n // existed have neither, and the map treats their absence as \"unknown\"\n // rather than \"no\".\n pinnedDate?: string | null;\n smsSent?: boolean;\n // Stamped by the map when \"Set route date in Salesforce\" writes this stop,\n // so the padlock and the popup can say so immediately rather than waiting\n // for the next rebuild to read Route Date back. replacedRouteDate is set\n // only where the commit overwrote a different date that was already there.\n committedAt?: string;\n committedRoute?: string;\n replacedRouteDate?: string;\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 // Stops a dispatcher pulled off the plan by hand. Kept out of `stops` on\n // purpose - everything downstream (the sheet, the OptimoRoute payload, the\n // load list, the legend counts) reads `stops` and must not see these - but\n // carried so the map can still draw them as grey dots instead of letting\n // them vanish. Optional: snapshots built before this existed have none.\n //\n // Deliberately a thinner record than a routed stop: enough to draw a dot,\n // name it and put it back, and nothing that only matters to a truck.\n removed?: {\n id: string;\n name: string;\n account: string;\n address: string;\n units: number;\n closeDate?: string | null;\n lat: number;\n lon: number;\n }[];\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\nexport async function sfQuery(soql: string): Promise {\n const url = Deno.env.get(\"SALESFORCE_AGENT_URL\")!;\n const key = Deno.env.get(\"SALESFORCE_AGENT_KEY\")!;\n // Key in a HEADER, not the query string. Val Town records the full request\n // URL in its traces, so ?key= published this secret on every route build and\n // those traces cannot be purged. Found 2026-09-10.\n const res = await fetch(`${url}?q=${encodeURIComponent(soql)}`, {\n headers: { \"x-api-key\": key },\n });\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// The same query, split so an `Id IN (...)` list can never outgrow the URL.\n//\n// sfQuery sends SOQL in the query string, and Val Town's edge rejects a request\n// URL over 16 KiB with a bare 414 - no body at all, so it comes back through the\n// throw above as the uninformative \"SOQL query failed: 414 {}\", naming neither\n// the length nor the query. On 2026-08-27 a mass import left 601 orders\n// route-dated to a single day; their 599 account ids made a 16,583-character URL\n// and killed the whole OptimoRoute push about 200 characters over the line.\n//\n// 200 ids per call is the batch size sfUpdateMany already uses, and holds the\n// URL near 5 KB however big the day gets. `soqlFor` is handed the quoted,\n// comma-joined chunk and returns the query to run for it.\nexport async function sfQueryByIds(\n ids: string[],\n soqlFor: (idList: string) => string,\n): Promise {\n const out: any[] = [];\n for (let i = 0; i < ids.length; i += 200) {\n const chunk = ids.slice(i, i + 200).map((id) => `'${id}'`).join(\",\");\n out.push(...await sfQuery(soqlFor(chunk)));\n }\n return out;\n}\n\n// Write a field set back to Salesforce, 200 records per call.\n//\n// The proxy enforces its own allow-list, so a typo in a field name comes back\n// as a 400 rather than writing something unexpected. Per-record verdicts are\n// passed through untouched: with allOrNone off, a validation rule can reject\n// three opportunities while the other 197 commit, and the caller needs to be\n// able to say which three rather than \"some of it worked\".\nexport async function sfUpdateMany(\n sobject: string,\n records: { id: string; fields: Record }[],\n): Promise<{ id: string; success: boolean; errors: unknown[] }[]> {\n const url = Deno.env.get(\"SALESFORCE_AGENT_URL\")!;\n const key = Deno.env.get(\"SALESFORCE_AGENT_KEY\")!;\n const out: { id: string; success: boolean; errors: unknown[] }[] = [];\n\n for (let i = 0; i < records.length; i += 200) {\n const chunk = records.slice(i, i + 200);\n // Key in a header, same reason as sfQuery above: query strings are written\n // into Val Town's traces permanently.\n const res = await fetch(url, {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\", \"x-api-key\": key },\n body: JSON.stringify({ sobject, records: chunk }),\n });\n const data = await res.json().catch(() => ({}));\n if (!res.ok || data?.error) {\n throw new Error(`Salesforce update failed: ${res.status} ${JSON.stringify(data)}`);\n }\n out.push(...(data.results ?? []));\n }\n return out;\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\nexport function 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`);\nexport const 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// Every delivery day still open in the week closing on `saturday`, counting\n// from tomorrow. Today is deliberately excluded: anything dated today is\n// already out on a truck.\n//\n// Returns empty once the week's last weekday has gone - Friday evening\n// onwards. The caller decides what that means rather than this quietly rolling\n// forward on its own, because \"which week are we planning\" has to be one\n// answer shared by the day list, the demand window and the week tabs.\nfunction remainingDeliveryDays(today: string, saturday: string): { day: string; date: string }[] {\n const days: { day: string; date: string }[] = [];\n for (let d = addDays(today, 1); d <= saturday; d = addDays(d, 1)) {\n if (isWeekday(d)) days.push({ day: dayName(d), date: d });\n }\n return days;\n}\n\n// How far back the current week looks for orders that were never delivered.\n// Rolling, not a fixed date: a hardcoded start silently widens for ever, so by\n// winter a \"three month\" window would have been reaching back six.\nconst OVERDUE_LOOKBACK_MONTHS = 3;\n\n// The same day-of-month N months earlier, clamped to the end of a short month\n// so three months before 31 May is 28 February rather than a date that does not\n// exist.\nfunction monthsBefore(iso: string, months: number): string {\n const [y, m, d] = iso.split(\"-\").map(Number);\n let year = y;\n let month = m - months;\n while (month <= 0) {\n month += 12;\n year -= 1;\n }\n const lastOfMonth = new Date(Date.UTC(year, month, 0)).getUTCDate();\n const day = Math.min(d, lastOfMonth);\n return `${year}-${String(month).padStart(2, \"0\")}-${String(day).padStart(2, \"0\")}`;\n}\n\n// The Sunday that opens the week closing on the given Saturday.\nconst weekStartFor = (saturday: string) => addDays(saturday, -6);\n\n// Monday to Friday of the week that closes on the given Saturday. The delivery\n// week runs Sunday to Saturday, so those are the five days before it.\nfunction weekdaysEnding(saturday: string): { day: string; date: string }[] {\n const days: { day: string; date: string }[] = [];\n for (let back = 5; back >= 1; back--) {\n const d = addDays(saturday, -back);\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// Route Date carries sentinel values (2000-01-01, 2001-01-01) on records pulled\n// off a route - the route name literally reads \"Removed from route\". Those mean\n// \"no route\", not \"delivered in the year 2000\".\nconst ROUTE_DATE_FLOOR = \"2020-01-01\";\n\n// Route Date is what dispatch schedules on; CloseDate is only the fallback for\n// records not yet on a route. Windowing the query on CloseDate alone therefore\n// misses anything routed for a date its CloseDate does not agree with - and in\n// this org the two disagree on ~92% of scheduled records. Harmless when the\n// window is a whole week, fatal when it is a single day.\nconst dueBy = (endDate: string) =>\n `((Route_Date__c >= ${ROUTE_DATE_FLOOR} AND Route_Date__c <= ${endDate})` +\n ` OR ((Route_Date__c = null OR Route_Date__c < ${ROUTE_DATE_FLOOR}) AND CloseDate <= ${endDate}))`;\n\n// How much deliverable work is already sitting on one specific date. Used to\n// decide whether an imminent Saturday is worth scheduling.\nasync function countDueOn(region: Region, date: string): Promise {\n const rows = await sfQuery(\n `SELECT Id FROM Opportunity\n WHERE Delivery_Region__c IN (${region.salesforceRegions.map((r) => `'${r}'`).join(\",\")})\n AND StageName = 'Delivery Due'\n AND (Route_Date__c = ${date} OR (Route_Date__c = null AND CloseDate = ${date}))`\n .replace(/\\s+/g, \" \").trim(),\n );\n return rows.length;\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.\n// Road distance between two fixed points does not change, and the same\n// customers recur week after week - so the expensive part is worth keeping.\n//\n// Measured 2026-08-12 before this existed: a 302-stop build spent 65.9s in this\n// function and 0.0s actually packing routes. Requests scale as (n/45)^2 and\n// each one waits 1.1s for the public server's rate limit, so 302 stops meant 49\n// requests and 52.8s of pure sleeping. With the cache warm, a block whose pairs\n// are all known is skipped outright - no request, no sleep.\nconst DISTANCE_CACHE_PREFIX = \"distance-cache\";\n\n// Roughly a metre of precision. Coordinates come from the geocode cache or\n// Salesforce and are stable, so this only guards against float drift.\nconst coordKey = (p: { lat: number; lon: number }) => `${p.lat.toFixed(5)},${p.lon.toFixed(5)}`;\n\n// A full week for a large region is ~91k pairs. Past this the blob is pruned\n// back to just the pairs this build used, which keeps it bounded without\n// tracking per-entry timestamps.\nconst MAX_CACHE_ENTRIES = 400_000;\n\n// Defaults for every region unless it overrides them. A truck-day is capped at\n// 30 stops however small the orders are, and the week is packed front-heavy so\n// the later days keep room for work that has not been booked yet.\nconst DEFAULT_MAX_STOPS_PER_ROUTE = 30;\nconst DEFAULT_FRONT_LOAD = 0.4;\n\n// How far a stored account coordinate may sit from that account's own closest\n// warehouse before it is treated as bad data. Some accounts carry coordinates\n// from another region entirely; routing to those is worse than geocoding.\nconst MAX_KM_FROM_WAREHOUSE = 300;\n\n// Two groups of stops closer than this are treated as the same place whatever\n// their own spread says, so a handful of drops in one suburb still merge onto\n// one truck rather than being called separate territories.\nconst MIN_MERGE_GAP_KM = 15;\n\n// Straight-line kilometres between two stops. Rough by design - it is used for\n// \"are these the same part of town\", not for routing, where the OSRM matrix\n// gives real road distance.\nfunction apartKm(a: { lat: number; lon: number }, b: { lat: number; lon: number }) {\n return Math.hypot((a.lat - b.lat) * 111, (a.lon - b.lon) * 98 * Math.cos(a.lat * Math.PI / 180) / 0.98);\n}\n\n// Banding splits the region into one horizontal slice per truck-day, so the\n// slices are only compact when there are enough of them. Measured on Miami:\n// 6 truck-days gave 35km bands, but 2 truck-days gave each truck ~130km of a\n// 150km strip - two trucks running the whole coast past each other. Below this\n// many truck-days a radial sweep from the depot gives each truck a tighter\n// sector, so that is used instead whatever the region asks for.\nconst MIN_SLOTS_FOR_BANDING = 4;\n\n// ----------------------------------------------------------------------------\n// Barriers\n// ----------------------------------------------------------------------------\n//\n// Some roads are cheap to drive along and expensive to cross. I-95 through South\n// Florida is the case that prompted this: it runs the length of the region, and\n// crossing it means finding an interchange, so a route that hops back and forth\n// over it burns time that never shows up as extra distance on a map.\n//\n// Modelled as a centreline plus a penalty rather than a hard partition. A truck\n// still crosses when the alternative is worse - one isolated stop on the far\n// side is cheaper to collect than a whole extra truck-day - but it will not\n// cross casually.\n//\n// The I-95 centreline itself lives above REGIONS, which references it.\n//\n// Longitude of the barrier at a given latitude, interpolated between the two\n// waypoints that bracket it. Beyond either end the nearest end is held, which\n// keeps a stop north of Jupiter or south of downtown on a sensible side rather\n// than undefined.\nfunction barrierLonAt(line: [number, number][], lat: number): number {\n if (lat <= line[0][0]) return line[0][1];\n if (lat >= line[line.length - 1][0]) return line[line.length - 1][1];\n for (let i = 0; i < line.length - 1; i++) {\n const [latA, lonA] = line[i];\n const [latB, lonB] = line[i + 1];\n if (lat >= latA && lat <= latB) {\n return lonA + ((lat - latA) / (latB - latA)) * (lonB - lonA);\n }\n }\n return line[line.length - 1][1];\n}\n\n// +1 east of the line, -1 west of it.\nexport function barrierSide(line: [number, number][], lat: number, lon: number) {\n return lon >= barrierLonAt(line, lat) ? 1 : -1;\n}\n\n// Which of the ten territories a point falls in. Corridor first, then latitude.\n//\n// The corridor test runs east to west because the tests nest: everything east\n// of the Intracoastal is island whatever it does relative to I-95, and I-95 only\n// decides between coastal and inland for what is left.\nexport function territoryOf(\n spec: typeof MIAMI_TERRITORIES,\n lat: number,\n lon: number,\n): number {\n if (barrierSide(spec.icw, lat, lon) > 0) return 0;\n const band = (cuts: number[]) => cuts.filter((c) => lat >= c).length;\n return barrierSide(spec.i95, lat, lon) > 0\n ? 1 + band(spec.coastalCuts)\n : 5 + band(spec.inlandCuts);\n}\n\n// A copy of the road matrix with a flat penalty added to every stop-to-stop leg\n// that crosses the barrier.\n//\n// A copy, not an edit in place, because the true road distances are still needed\n// for the kilometre total the build reports. Inflating those would make every\n// plan read as worse than it actually drives, and the number is used to compare\n// one build against another.\n//\n// The depot row and column are deliberately untouched. The Pompano depot sits\n// essentially on I-95, so every route starts and ends beside it; taxing the run\n// out of the yard would penalise every route equally and change no decision.\n// The rule is about not crossing BETWEEN stops.\n// Zones are whatever the caller says they are: two sides of one road, or ten\n// territories. Either way the rule is the same - a leg between two zones costs\n// more than the road says - so this takes a function rather than a line, and one\n// implementation serves both.\nexport function penaliseZoneCrossings(\n matrix: number[][],\n stops: { lat: number; lon: number }[],\n zoneOf: (s: { lat: number; lon: number }) => number,\n penaltyKm: number,\n) {\n const side = stops.map(zoneOf);\n const out = matrix.map((row) => row.slice());\n // The matrix is in METRES - it comes straight from OSRM's distance annotation,\n // which is why every reader divides by 1000. The penalty is configured in km\n // because that is how the routes are talked about, so convert it here. Adding\n // the km figure raw is a 25-metre penalty on a 200km route: it changes nothing\n // and looks like it is working.\n const penaltyMetres = penaltyKm * 1000;\n let crossingPairs = 0;\n for (let i = 0; i < stops.length; i++) {\n for (let j = i + 1; j < stops.length; j++) {\n if (side[i] === side[j]) continue;\n crossingPairs++;\n // +1 for the depot occupying index 0.\n out[i + 1][j + 1] += penaltyMetres;\n out[j + 1][i + 1] += penaltyMetres;\n }\n }\n // Stops per zone, so a build can log the split it actually planned against\n // rather than just a pair count. A zone that has collapsed to one or two stops\n // is worth seeing - it means the cuts no longer match where the customers are.\n const perZone = new Map();\n for (const z of side) perZone.set(z, (perZone.get(z) ?? 0) + 1);\n return { matrix: out, crossingPairs, perZone };\n}\n\nexport async function getDistanceMatrix(\n depot: { lat: number; lon: number },\n stops: { lat: number; lon: number }[],\n regionKey?: string,\n): Promise {\n const points = [depot, ...stops];\n const keys = points.map(coordKey);\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 for (let i = 0; i < points.length; i++) matrix[i][i] = 0;\n\n const cacheKey = regionKey ? blobKeyFor(DISTANCE_CACHE_PREFIX, regionKey) : null;\n const cache: Record = cacheKey\n ? ((await blob.getJSON(cacheKey).catch(() => null)) as Record) ?? {}\n : {};\n const cacheSizeBefore = Object.keys(cache).length;\n\n // Directional on purpose: one-way streets make A->B and B->A different, and\n // the matrix is used directionally.\n let hits = 0;\n if (cacheKey) {\n for (let i = 0; i < points.length; i++) {\n for (let j = 0; j < points.length; j++) {\n if (i === j) continue;\n const v = cache[`${keys[i]}|${keys[j]}`];\n if (typeof v === \"number\") {\n matrix[i][j] = v;\n hits++;\n }\n }\n }\n }\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 let requests = 0;\n let firstRequest = true;\n for (const rowChunk of rowChunks) {\n for (const colChunk of colChunks) {\n // Skip any block the cache already covers completely - this is where the\n // time goes, so not asking is the whole optimisation.\n const needed = rowChunk.some((r) =>\n colChunk.some((c) => r.i !== c.i && matrix[r.i][c.i] === Infinity)\n );\n if (!needed) continue;\n\n if (!firstRequest) await sleep(OSRM_MIN_INTERVAL_MS);\n firstRequest = false;\n requests++;\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 const d = sub[ri][ci];\n matrix[r.i][c.i] = d;\n // Only finite values are worth keeping; an unreachable pair would\n // otherwise be cached as a permanent Infinity.\n if (cacheKey && r.i !== c.i && Number.isFinite(d)) {\n cache[`${keys[r.i]}|${keys[c.i]}`] = d;\n }\n });\n });\n }\n }\n\n if (cacheKey) {\n let entries = Object.keys(cache).length;\n if (entries > MAX_CACHE_ENTRIES) {\n const kept: Record = {};\n for (let i = 0; i < points.length; i++) {\n for (let j = 0; j < points.length; j++) {\n if (i === j) continue;\n const k = `${keys[i]}|${keys[j]}`;\n const v = cache[k];\n if (typeof v === \"number\") kept[k] = v;\n }\n }\n console.log(`Distance cache pruned ${entries} -> ${Object.keys(kept).length} pair(s)`);\n Object.keys(cache).forEach((k) => delete cache[k]);\n Object.assign(cache, kept);\n entries = Object.keys(cache).length;\n }\n if (entries !== cacheSizeBefore) await blob.setJSON(cacheKey, cache);\n\n const total = points.length * (points.length - 1);\n const blocks = rowChunks.length * colChunks.length;\n console.log(\n `Distance matrix: ${requests}/${blocks} OSRM block(s) fetched, ` +\n `${hits}/${total} pair(s) from cache (${Math.round(hits / total * 100)}%), ` +\n `cache now ${entries} pair(s)`,\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 // Weekday names this account will accept a delivery on, from the account's\n // \"Allowed days\" multi-select. null means no restriction, which is the case\n // for all but a handful of accounts.\n allowedDays: string[] | null;\n // The Route Date this stop has been committed to in Salesforce, so it cannot\n // be moved by a rebuild. null for anything not yet committed, and for a\n // committed date that has already passed. Pins the DAY only - the truck stays\n // free for the packer.\n pinnedDate: string | null;\n // Whether the customer has been texted their date. No longer what pins the\n // stop - the Route Date does that - but it still says whether anyone outside\n // the building is expecting us.\n smsSent?: boolean;\n // A texted date that has already passed. Not a pin - it constrains nothing -\n // but the customer was told a day nobody came on, so it is worth naming.\n lapsedPromise?: string | null;\n // Set when a pinned stop was forced onto a truck that was already full. The\n // route goes out over capacity and the sheet flags it in red.\n overPacked?: boolean;\n closeDate: string;\n // When the order was raised. An old created date on an undelivered stop is\n // the signal that something has been sitting in the queue too long.\n createdDate: string | null;\n units: number;\n // Carried for OptimoRoute only - none of this affects routing. The bridge\n // sends the same set, so an order created from here looks the same to a\n // driver as one created from Salesforce.\n bottles: number;\n springBottles: number;\n waterType: string | null;\n apt: string | null;\n phone: string | null;\n email: string | null;\n orderSummary: string | null;\n allowedDaysRaw: string | null;\n products: Product[];\n truckType: string | null;\n lat: number;\n lon: number;\n precision: \"exact\" | \"approx\";\n};\n\n// How many units an order actually puts on a truck.\n//\n// Total_Units_for_Order__c is the real load: it expands a pallet into the packs\n// stacked on it, where Total_Amount_of_Products__c only counts the line\n// quantity. A two-pallet Kirkland order therefore read as 2 units and was\n// packed as though it were nothing - one DC Helix order measured 4 against a\n// true 82. It also drops accessory lines (cups) from the count and gives a\n// cooler its own bulk.\n//\n// Measured across all three regions on 2026-08-19: the two agree on 763 of 796\n// open orders, and DC's three-month book grows 918 -> 1096 units, almost all of\n// it those Helix pallets.\n//\n// Not to be trusted blindly. A handful of records carry null, and one carried a\n// flat 0 against an order for three 5-gallon bottles. Zero is worse than the\n// old number - a weightless stop the packer will hang on any truck - so fall\n// back to the old field rather than believe it.\nexport function orderUnits(\n opp: { Total_Units_for_Order__c?: unknown; Total_Amount_of_Products__c?: unknown },\n): number {\n const exact = Number(opp.Total_Units_for_Order__c);\n if (Number.isFinite(exact) && exact > 0) return exact;\n return Number(opp.Total_Amount_of_Products__c) || 1;\n}\n\n// Did the line above have to fall back? Only for reporting - the build says how\n// often it happened so a field that quietly stops being populated is visible\n// rather than silently shrinking every truck's load.\nexport const usedUnitsFallback = (\n opp: { Total_Units_for_Order__c?: unknown },\n): boolean => !(Number(opp.Total_Units_for_Order__c) > 0);\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 maxStops = Infinity,\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 (\n current.length > 0 &&\n (used + stops[i].units > capacityOf(clusters.length) || current.length >= maxStops)\n ) {\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\n// Like sweepInto, but cuts on TWO limits: the truck's capacity, and how far the\n// band is allowed to stretch along the sort axis.\n//\n// Capacity alone is not enough. A run of small orders lets one truck swallow a\n// huge latitude range - the untuned version handed a single Miami truck 41\n// stops spanning 70km north-south, which is the sprawl banding was meant to\n// remove. Cutting on span as well keeps bands shallow even where orders are\n// small.\nfunction bandInto(\n order: number[],\n stops: Stop[],\n capacities: number[],\n keyOf: (s: Stop) => number,\n maxSpan: number,\n maxStops = Infinity,\n): number[][] {\n const clusters: number[][] = [];\n let current: number[] = [];\n let used = 0;\n let anchor = 0;\n const capacityOf = (clusterIndex: number) =>\n capacities[Math.min(clusterIndex, capacities.length - 1)];\n\n for (const i of order) {\n const k = keyOf(stops[i]);\n const overCapacity = current.length > 0 &&\n used + stops[i].units > capacityOf(clusters.length);\n const overSpan = current.length > 0 && Math.abs(anchor - k) > maxSpan;\n const overCount = current.length >= maxStops;\n if (overCapacity || overSpan || overCount) {\n clusters.push(current);\n current = [];\n used = 0;\n }\n if (current.length === 0) anchor = k;\n current.push(i);\n used += stops[i].units;\n }\n if (current.length > 0) clusters.push(current);\n return clusters;\n}\n\n// Zones: give each driver a territory, not a scatter of points.\n//\n// What is minimised here is the distance covered BETWEEN the day's stops - first\n// stop to last - with the run out from the depot treated as a fixed cost of the\n// day. A driver does not care that the work is 170km away; they care that once\n// they arrive, every drop is round the corner from the last.\n//\n// So a zone grows by repeatedly taking whichever remaining stop lengthens the\n// zone's own path least. That is cheapest insertion, the same criterion that\n// sequences the route afterwards - grouping and ordering optimise one number\n// instead of two different ones. A sweep, a band, or a distance-to-centre\n// cluster all optimise something else and hope the path follows.\n//\n// Growth aims at a unit target rather than the hard cap, so the fleet comes out\n// evenly near-full instead of the first trucks stuffed and the last carrying\n// scraps. The target tracks each slot's share of the capacity still unbuilt, so\n// the front-load taper continues to shape the week.\n// How hard a zone pulls a stop toward its own body, relative to the extra\n// driving that stop causes. 0 is pure cheapest-insertion, which snakes; 1 makes\n// compactness dominate and gives up good sequencing. Both terms are metres.\nconst ZONE_COMPACTNESS = 0.35;\n\n// How far outside itself a zone will reach, as a multiple of its own average\n// leg. Past this the territory is finished and the stop belongs to somebody\n// else. The floor keeps a very tight urban zone from refusing its own suburbs.\nconst ZONE_REACH = 4;\nconst MIN_ZONE_REACH_M = 8000;\n\n// What it costs this zone to take on a stop: the extra driving, plus a pull\n// toward the body of the territory.\n//\n// The second term is what stops a zone becoming a snake. Appending to either\n// end of a path costs a single edge, so raw insertion cost is happy to reach\n// clear across the map and back - which is precisely how two routes end up\n// interleaved through each other's patch. Averaging the distance to every\n// member instead asks \"does this stop belong here\", not \"is it near an end\".\nfunction fitCost(\n path: number[],\n i: number,\n dist: Dist,\n): { cost: number; at: number; pull: number } {\n if (path.length === 0) return { cost: 0, at: 0, pull: 0 };\n let ins = dist(i, path[0]);\n let at = 0;\n const tail = dist(path[path.length - 1], i);\n if (tail < ins) {\n ins = tail;\n at = path.length;\n }\n for (let p = 0; p < path.length - 1; p++) {\n const c = dist(path[p], i) + dist(i, path[p + 1]) - dist(path[p], path[p + 1]);\n if (c < ins) {\n ins = c;\n at = p + 1;\n }\n }\n let sum = 0;\n for (const m of path) sum += dist(m, i);\n const pull = sum / path.length;\n return { cost: ins + ZONE_COMPACTNESS * pull, at, pull };\n}\n\nfunction growZones(\n members: number[],\n stops: Stop[],\n dist: Dist,\n capacities: number[],\n maxStops: number,\n): { zones: number[][]; leftovers: number[] } {\n const capOf = (z: number) => capacities[Math.min(z, capacities.length - 1)];\n const n = capacities.length;\n const remaining = new Set(members);\n\n const totalUnits = members.reduce((a, i) => a + stops[i].units, 0);\n const totalCap = capacities.reduce((a, c) => a + c, 0) || 1;\n const targets = capacities.map((c) => Math.min(c, (totalUnits * c) / totalCap));\n\n // Seed every zone before growing any of them, as far apart as they will go.\n // Growing one territory to capacity before its neighbours exist is what\n // produced the snakes: with nobody competing, a zone extends across the whole\n // map one cheap edge at a time and the later zones inherit the gaps it left.\n const seeds: number[] = [];\n if (remaining.size > 0) {\n let first = -1;\n for (const i of remaining) if (first === -1 || dist(-1, i) > dist(-1, first)) first = i;\n seeds.push(first);\n while (seeds.length < Math.min(n, remaining.size)) {\n let pick = -1;\n let far = -1;\n for (const i of remaining) {\n if (seeds.includes(i)) continue;\n let d = Infinity;\n for (const s of seeds) d = Math.min(d, dist(s, i));\n if (d > far) {\n far = d;\n pick = i;\n }\n }\n if (pick === -1) break;\n seeds.push(pick);\n }\n }\n\n const zones: number[][] = seeds.map((s) => [s]);\n while (zones.length < n) zones.push([]);\n const used = zones.map((z) => z.reduce((a, i) => a + stops[i].units, 0));\n for (const s of seeds) remaining.delete(s);\n\n const pathLen = (p: number[]) => {\n let t = 0;\n for (let i = 0; i < p.length - 1; i++) t += dist(p[i], p[i + 1]);\n return t;\n };\n\n // Round-robin, emptiest zone first. Every zone grows at the same rate, so\n // they meet at a boundary and stop instead of one overrunning the others.\n //\n // A zone will not reach outside itself to hit its target. When the nearest\n // remaining work is several times further off than the zone's own average\n // leg, that territory is simply finished, and taking the stop anyway is what\n // stapled a Brooksville drop and a Largo drop onto the end of an Orlando\n // run - the Largo one 400 metres from a truck already working Largo.\n while (remaining.size > 0) {\n let turn = -1;\n let slack = -Infinity;\n for (let z = 0; z < n; z++) {\n if (zones[z].length === 0 || zones[z].length >= maxStops) continue;\n if (used[z] >= targets[z]) continue;\n const s = (targets[z] - used[z]) / Math.max(1, targets[z]);\n if (s > slack) {\n slack = s;\n turn = z;\n }\n }\n if (turn === -1) break;\n\n // Only once a zone has a shape worth defending. Below that any limit would\n // be measuring noise off two or three stops.\n const path = zones[turn];\n const reach = path.length >= 4\n ? Math.max((pathLen(path) / (path.length - 1)) * ZONE_REACH, MIN_ZONE_REACH_M)\n : Infinity;\n\n let bestStop = -1;\n let bestAt = 0;\n let bestScore = Infinity;\n for (const i of remaining) {\n if (used[turn] + stops[i].units > capOf(turn)) continue;\n const { cost, at, pull } = fitCost(path, i, dist);\n if (pull > reach) continue;\n if (cost < bestScore) {\n bestScore = cost;\n bestStop = i;\n bestAt = at;\n }\n }\n // Either nothing fits the truck or nothing is near enough to belong here.\n // Retire the zone and let another take a turn.\n if (bestStop === -1) {\n targets[turn] = used[turn];\n continue;\n }\n path.splice(bestAt, 0, bestStop);\n remaining.delete(bestStop);\n used[turn] += stops[bestStop].units;\n }\n\n // Whatever the zones would not reach for, placed by best fit and no limit.\n //\n // Stop-driven, not zone-driven: the question is \"which truck is nearest this\n // stop\", not \"what is nearest this half-empty truck\". Asking it the other way\n // round hands the leftovers straight back to the zone that just declined\n // them, because a zone that stopped early is the emptiest one left.\n const stragglers = [...remaining].sort((a, b) => stops[b].units - stops[a].units);\n for (const i of stragglers) {\n let bestZone = -1;\n let bestAt = 0;\n let bestScore = Infinity;\n for (let z = 0; z < n; z++) {\n if (zones[z].length === 0 || zones[z].length >= maxStops) continue;\n if (used[z] + stops[i].units > capOf(z)) continue;\n const { cost, at } = fitCost(zones[z], i, dist);\n if (cost < bestScore) {\n bestScore = cost;\n bestZone = z;\n bestAt = at;\n }\n }\n if (bestZone < 0) continue;\n zones[bestZone].splice(bestAt, 0, i);\n used[bestZone] += stops[i].units;\n remaining.delete(i);\n }\n\n return { zones, leftovers: [...remaining] };\n}\n\n// Boundary tidy-up. A stop claimed early can end up on the wrong side of a seam\n// that only settled later. Judged on the same measure that grew the zones, so a\n// stop moves whenever it belongs to its neighbour more than to its own zone.\nfunction tidyZones(\n zones: number[][],\n stops: Stop[],\n dist: Dist,\n capacities: number[],\n maxStops: number,\n): number {\n const capOf = (z: number) => capacities[Math.min(z, capacities.length - 1)];\n const unitsOf = (p: number[]) => p.reduce((n, i) => n + stops[i].units, 0);\n\n let total = 0;\n for (let pass = 0; pass < 4; pass++) {\n let moved = 0;\n for (let a = 0; a < zones.length; a++) {\n for (const i of [...zones[a]]) {\n if (zones[a].length <= 1) break;\n const without = zones[a].filter((x) => x !== i);\n // How well the stop fits where it already is. A move has to beat this.\n let bestScore = fitCost(without, i, dist).cost;\n let bestZone = -1;\n let bestAt = 0;\n for (let b = 0; b < zones.length; b++) {\n if (b === a || zones[b].length === 0 || zones[b].length >= maxStops) continue;\n if (unitsOf(zones[b]) + stops[i].units > capOf(b)) continue;\n const { cost, at } = fitCost(zones[b], i, dist);\n if (cost < bestScore) {\n bestScore = cost;\n bestZone = b;\n bestAt = at;\n }\n }\n if (bestZone >= 0) {\n zones[a] = without;\n zones[bestZone].splice(bestAt, 0, i);\n moved++;\n }\n }\n }\n\n // Swaps. Relocating a stop needs free capacity in the destination, and on a\n // full week there is none - every truck sits within a unit or two of its\n // cap, so the move that would fix things is never legal. A swap needs no\n // free capacity at all: two stops trade zones and both loads stay put.\n //\n // Without this a 10-unit drop in Brooksville rode a Monday truck it was\n // 68km from the middle of, while the Wednesday route it was 47km from had\n // one unit spare and a 10-unit stop of its own that belonged on the Monday.\n for (let a = 0; a < zones.length; a++) {\n for (let b = a + 1; b < zones.length; b++) {\n if (zones[a].length < 2 || zones[b].length < 2) continue;\n const ua = unitsOf(zones[a]);\n const ub = unitsOf(zones[b]);\n // Precomputed per stop of b, since it does not vary with a's stop.\n const bOptions = zones[b].map((j) => {\n const rest = zones[b].filter((x) => x !== j);\n return { j, rest, home: fitCost(rest, j, dist).cost };\n });\n\n for (const i of [...zones[a]]) {\n const aRest = zones[a].filter((x) => x !== i);\n const iHome = fitCost(aRest, i, dist).cost;\n let bestGain = 1; // a metre of slack, so float noise cannot cycle\n let pick: { j: number; rest: number[]; iAt: number; jAt: number } | null = null;\n\n for (const opt of bOptions) {\n if (!zones[b].includes(opt.j)) continue; // already traded away\n if (ua - stops[i].units + stops[opt.j].units > capOf(a)) continue;\n if (ub - stops[opt.j].units + stops[i].units > capOf(b)) continue;\n const iThere = fitCost(opt.rest, i, dist);\n const jThere = fitCost(aRest, opt.j, dist);\n const gain = (iHome + opt.home) - (iThere.cost + jThere.cost);\n if (gain > bestGain) {\n bestGain = gain;\n pick = { j: opt.j, rest: opt.rest, iAt: iThere.at, jAt: jThere.at };\n }\n }\n\n if (pick) {\n zones[a] = aRest;\n zones[b] = pick.rest;\n zones[a].splice(pick.jAt, 0, pick.j);\n zones[b].splice(pick.iAt, 0, i);\n moved++;\n break; // a and b have both changed shape; revisit them next pass\n }\n }\n }\n }\n\n total += moved;\n if (moved === 0) break;\n }\n return total;\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 orientation: Orientation = \"radial\",\n maxStops = Infinity,\n // Zoned builds only. Which slots to grow zones for, in the order they should\n // be filled. Lets the caller pack a week into the fewest truck-days by\n // passing the earliest days first and only as many slots as the work needs.\n // Omitted, every slot is used, roomiest first.\n slotPriority?: number[],\n) {\n const dist: Dist = (from, to) => distances[from + 1][to + 1];\n\n let bestClusters: number[][] = [];\n let bestScore = Infinity;\n\n if (orientation === \"radial\") {\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 for (let seam = 0; seam < angularOrder.length; seam++) {\n const clusters = sweepInto(angularOrder, seam, stops, slotCapacities, maxStops);\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) {\n bestClusters = sweepInto(angularOrder, 0, stops, slotCapacities, maxStops);\n }\n } else if (orientation === \"zoned\") {\n // Slots are not interchangeable: two truck sizes, plus the front-load taper\n // that leaves later days deliberately emptier. Grow the zones against the\n // capacities sorted large-to-small and hand zone j to the j-th roomiest\n // slot, so the biggest territory lands on the truck that can carry it.\n // Without a priority list, every slot is used, roomiest first.\n const order = slotPriority ??\n slotCapacities\n .map((cap, slot) => ({ cap, slot }))\n .sort((a, b) => b.cap - a.cap)\n .map((x) => x.slot);\n const caps = order.map((slot) => slotCapacities[slot]);\n const { zones, leftovers } = growZones(\n stops.map((_, i) => i),\n stops,\n dist,\n caps,\n maxStops,\n );\n tidyZones(zones, stops, dist, caps, maxStops);\n\n // Heaviest zone onto the first slot in the order. Growth order is\n // geographic - the first zone seeded is the furthest-out one - and that\n // territory is usually among the smallest, so pairing by growth order put\n // the lightest work on Monday and defeated the front-loading entirely.\n // Re-pairing by size is safe: every zone already fits the capacity it was\n // grown against, and sorting both sides keeps that true.\n bestClusters = Array.from({ length: maxSlots }, () => [] as number[]);\n zones\n .map((z, j) => ({ z, j, units: z.reduce((n, i) => n + stops[i].units, 0) }))\n .sort((a, b) => b.units - a.units)\n .forEach((x, rank) => {\n if (rank < order.length) bestClusters[order[rank]] = x.z;\n });\n // Anything that fitted nowhere is pushed past the last slot, where the\n // caller reports it unassigned - which is what triggers the front-load\n // relaxation upstream.\n if (leftovers.length > 0) bestClusters.push(leftovers);\n\n bestScore = bestClusters.reduce(\n (sum, c) => sum + (c.length > 0 ? pathLength(nearestNeighbour(c, dist), dist) : 0),\n 0,\n );\n } else {\n // Banded. Sort along one axis and cut the line into capacity-sized runs, so\n // each truck gets a contiguous slice perpendicular to that axis: sorting by\n // latitude stacks bands north-to-south, and each band runs east-west.\n //\n // No seam search here. The order is a line, not a circle, so rotating it\n // would wrap the far end of the region round to the near end and hand one\n // truck both the northernmost and southernmost stops - exactly the sprawl\n // this is meant to remove.\n const key = (s: Stop) => (orientation === \"east-west\" ? s.lat : s.lon);\n const banded = stops\n .map((s, i) => ({ i, k: key(s) }))\n .sort((a, b) => b.k - a.k)\n .map((x) => x.i);\n\n // Capacity-only banding is the loosest cut there is. If even that needs\n // more trucks than exist, no span limit can help - tightening bands only\n // ever makes more of them - so take it and let the overflow report as\n // unassigned rather than silently dropping more stops.\n const loosest = bandInto(banded, stops, slotCapacities, key, Infinity, maxStops);\n if (loosest.length > maxSlots) {\n bestClusters = loosest;\n } else {\n // Otherwise find the SHALLOWEST bands the fleet can still cover, by\n // bisecting on the span limit. Tighter bands mean more of them, so this\n // is monotonic and a plain binary search finds the tightest feasible\n // setting. No magic constant, and it retunes itself as stop counts and\n // truck capacities change.\n const keys = stops.map(key);\n let lo = 0;\n let hi = Math.max(...keys) - Math.min(...keys);\n let fit = loosest;\n for (let iter = 0; iter < 24; iter++) {\n const mid = (lo + hi) / 2;\n const attempt = bandInto(banded, stops, slotCapacities, key, mid, maxStops);\n if (attempt.length <= maxSlots) {\n fit = attempt;\n hi = mid;\n } else {\n lo = mid;\n }\n }\n bestClusters = fit;\n }\n\n bestScore = bestClusters.reduce(\n (sum, c) => sum + pathLength(nearestNeighbour(c, dist), dist),\n 0,\n );\n }\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 // Clustering can leave a slot empty where a sweep never would. Keep the\n // slot so the day still shows an available truck to the rules passes; the\n // caller drops empty routes before anything reaches the sheet.\n if (cluster.length === 0) {\n slots.push({ slot, stops: [] });\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\n// Stops pulled off the plan by hand from the map. Val Town's own state only -\n// nothing is written back to Salesforce, so the delivery stays open there and\n// simply is not routed this window.\n//\n// Keyed by windowEnd like the overrides, so a removal expires when the window\n// rolls forward rather than hiding a customer indefinitely.\nexport const REMOVED_KEY = \"removed-stops\";\n\nexport async function loadRemovedIds(windowEnd: string, region: Region): Promise> {\n const raw = await blob.getJSON(blobKeyFor(REMOVED_KEY, region.key)).catch(() => null) as\n | { windowEnd?: string; ids?: string[] }\n | null;\n if (!raw || raw.windowEnd !== windowEnd) return new Set();\n return new Set(raw.ids ?? []);\n}\n\nexport async function saveRemovedIds(windowEnd: string, region: Region, ids: Set) {\n await blob.setJSON(blobKeyFor(REMOVED_KEY, region.key), { windowEnd, ids: [...ids] });\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.\n// Enforces the two rules the packer cannot express geographically:\n// - an account's \"Allowed days\", where it has any\n// - a stop already texted to the customer, which is a promise about a date\n//\n// Both are rare, so this is a repair pass over the finished plan rather than\n// another constraint inside the sweep, where it would fight the geography for\n// every stop. A moved stop is appended to its new truck, so its position in\n// that truck's driving order is not re-optimised - fine for the handful this\n// touches, and the alternative is re-running the whole plan per exception.\nfunction honourDayRules(\n slots: { slot: number; stops: Stop[] }[],\n days: { day: string; date: string }[],\n slotCapacities: number[],\n maxStops: number,\n unassigned: Stop[] = [],\n // The trucks' real capacity, before the front-load taper. Merging two\n // half-empty trucks is judged against the vehicle, not against a planning\n // target - the taper exists to keep room for call-ins, and a merged truck\n // with spare units serves that better than a second driver does.\n hardCapacities: number[] = slotCapacities,\n): { moved: string[]; stuck: string[]; promoted: string[]; relieved: string[] } {\n const moved: string[] = [];\n const stuck: string[] = [];\n const promoted: string[] = [];\n const relieved: string[] = [];\n const bySlot = new Map(slots.map((s) => [s.slot, s]));\n const unitsIn = (s: { stops: Stop[] }) => s.stops.reduce((a, x) => a + x.units, 0);\n const dayOf = (slot: number) => days[slot % days.length];\n\n const allowedOn = (stop: Stop, slot: number) => {\n const d = dayOf(slot);\n if (stop.pinnedDate) return d.date === stop.pinnedDate;\n if (stop.allowedDays) return stop.allowedDays.includes(d.day);\n return true;\n };\n const fits = (slot: number, stop: Stop) => {\n const t = bySlot.get(slot);\n if (!t) return false;\n const cap = slotCapacities[Math.min(slot, slotCapacities.length - 1)];\n return t.stops.length < maxStops && unitsIn(t) + stop.units <= cap;\n };\n\n // An empty truck-day is a truck nobody is driving. Putting a single stop on\n // one costs a whole extra driver - in DC a rented van, since only the first\n // is owned - so every pass below treats it as the last resort and looks for\n // a truck that is already going out first.\n const alreadyOut = (slot: number) => (bySlot.get(slot)?.stops.length ?? 0) > 0;\n\n // How far a stop is from the work a truck is already carrying, measured to\n // the nearest stop on it rather than to its centre: a route is a line, not a\n // blob, and a drop beside one end of it is on the way even if the centre is\n // far off. Empty trucks have no work to be near, so they sort last.\n const reachKm = (stop: Stop, slot: number) => {\n const t = bySlot.get(slot);\n if (!t || t.stops.length === 0) return Infinity;\n return Math.min(...t.stops.map((s) => apartKm(stop, s)));\n };\n\n // Trucks already on the road that could take this stop, NEAREST first.\n //\n // Sorting purely by how empty a truck is was what put an Arlington stop on\n // the Loudoun run: Friday had the most room, so Friday won, 30km from\n // anything else on it. Room is already guaranteed by fits() - among the\n // trucks that can take the stop, the one whose route passes closest is the\n // one that should, and load only breaks ties between equally close days.\n const nearestFirst = (stop: Stop, except: number) =>\n slots\n .map((s) => s.slot)\n .filter((t) => t !== except && alreadyOut(t) && allowedOn(stop, t) && fits(t, stop))\n .sort((a, b) =>\n reachKm(stop, a) - reachKm(stop, b) ||\n unitsIn(bySlot.get(a)!) - unitsIn(bySlot.get(b)!)\n );\n\n // Try to open up `need` units on a truck that is already full, by shifting\n // un-promised work off it onto lighter days.\n //\n // This is what keeps DC on one van. When a stop has to move onto a day whose\n // truck is full, the choice is between hiring a second driver for that one\n // stop and moving an equal amount of un-promised work to a day with room.\n // The lighter day is always cheaper, so it is tried first - and only if it\n // cannot free enough does the day get a second truck.\n //\n // Largest first: one move that frees enough disturbs the week less than three\n // that each free a little. All-or-nothing - a half-finished shuffle would\n // scatter stops across the week and still need the extra truck.\n const makeRoom = (slot: number, need: number): boolean => {\n const host = bySlot.get(slot);\n if (!host) return false;\n const cap = slotCapacities[Math.min(slot, slotCapacities.length - 1)];\n const enough = () => cap - unitsIn(host) >= need && host.stops.length < maxStops;\n if (enough()) return true;\n\n const movable = host.stops.filter((s) => !s.pinnedDate).sort((a, b) => b.units - a.units);\n const shifted: { stop: Stop; to: number }[] = [];\n for (const stop of movable) {\n if (enough()) break;\n const to = nearestFirst(stop, slot)[0];\n if (to === undefined) continue;\n host.stops.splice(host.stops.indexOf(stop), 1);\n bySlot.get(to)!.stops.push(stop);\n shifted.push({ stop, to });\n }\n\n if (enough()) {\n for (const s of shifted) {\n relieved.push(\n `${s.stop.accountName} (${s.stop.units}u): ${dayOf(slot).day} -> ${dayOf(s.to).day}` +\n ` to keep ${dayOf(slot).day} on one truck`,\n );\n }\n return true;\n }\n // Not enough came off - put it all back. The day needs the extra truck.\n for (const s of shifted) {\n const t = bySlot.get(s.to)!;\n t.stops.splice(t.stops.indexOf(s.stop), 1);\n host.stops.push(s.stop);\n }\n return false;\n };\n\n for (const src of slots) {\n for (const stop of [...src.stops]) {\n if (allowedOn(stop, src.slot)) continue;\n // Nearest acceptable day wins, so a Wednesday-only stop lands on the\n // Wednesday closest to where the geography had already put it.\n const candidates = slots\n .map((s) => s.slot)\n .filter((t) => t !== src.slot && allowedOn(stop, t));\n const byNearestDay = (a: number, b: number) =>\n Math.abs((a % days.length) - (src.slot % days.length)) -\n Math.abs((b % days.length) - (src.slot % days.length));\n\n // A truck already going out beats an empty one every time, even one a few\n // days further off: the alternative is paying a second driver. Only when\n // no running truck has room, and none can be given room by shifting\n // un-promised work elsewhere, does a new truck get opened.\n //\n // Among the running trucks, nearest route wins and the calendar only\n // breaks ties. This used to go by calendar distance alone, which was a\n // stand-in for geography back when consecutive days were adjacent bands;\n // under zoning the days are territories, so the real distance is knowable\n // and Tuesday's truck may well pass closer than Wednesday's.\n const running = candidates.filter(alreadyOut).sort((a, b) =>\n reachKm(stop, a) - reachKm(stop, b) || byNearestDay(a, b)\n );\n let target = running.find((t) => fits(t, stop)) ??\n running.find((t) => makeRoom(t, stop.units)) ??\n candidates.filter((t) => fits(t, stop)).sort(byNearestDay)[0];\n let over = false;\n\n // Nowhere with room. A texted stop still goes on its promised day - the\n // truck runs over capacity and the sheet flags it - because the customer\n // has already been told. An allowed-days stop has no such promise behind\n // it, so that one stays put and is reported instead.\n if (target === undefined && stop.pinnedDate) {\n target = candidates\n .slice()\n .sort((a, b) => unitsIn(bySlot.get(a)!) - unitsIn(bySlot.get(b)!))[0];\n over = target !== undefined;\n }\n\n if (target === undefined) {\n stuck.push(\n `${stop.accountName} (${\n stop.pinnedDate ? `route-dated ${stop.pinnedDate}` : stop.allowedDays?.join(\"/\")\n }) stays on ${dayOf(src.slot).day}`,\n );\n continue;\n }\n src.stops.splice(src.stops.indexOf(stop), 1);\n bySlot.get(target)!.stops.push(stop);\n if (over) {\n stop.overPacked = true;\n const cap = slotCapacities[Math.min(target, slotCapacities.length - 1)];\n moved.push(\n `${stop.accountName}: ${dayOf(src.slot).day} -> ${dayOf(target).day} ` +\n `OVER CAPACITY (${unitsIn(bySlot.get(target)!)}/${cap} units)`,\n );\n } else {\n moved.push(`${stop.accountName}: ${dayOf(src.slot).day} -> ${dayOf(target).day}`);\n }\n }\n }\n\n // A texted stop that never got a truck is the worst outcome there is: the\n // customer has been told a date and nobody is coming. It outranks a stop no\n // one has been promised anything about, so it takes a place on its day - and\n // if the day is full, it displaces the largest un-promised stop there, which\n // becomes unassigned in its stead.\n for (const stop of [...unassigned]) {\n if (!stop.pinnedDate) continue;\n const onDay = slots.filter((s) => dayOf(s.slot).date === stop.pinnedDate);\n if (onDay.length === 0) continue;\n\n // Room if there is any, otherwise put it on anyway. A promise already made\n // beats the cube: the truck goes out over capacity and the sheet says so in\n // red, rather than a customer being told we are coming and nobody arriving.\n // Trucks already going out that day first, and if none has room, try to\n // make room on one by shifting un-promised work to a lighter day - the\n // stop's date is fixed, but which truck carries it is not, and a day should\n // not gain a second driver while another day is running half empty.\n const running = onDay\n .filter((s) => alreadyOut(s.slot))\n .sort((a, b) => reachKm(stop, a.slot) - reachKm(stop, b.slot));\n let target = running.find((s) => fits(s.slot, stop)) ??\n running.find((s) => makeRoom(s.slot, stop.units)) ??\n onDay.find((s) => fits(s.slot, stop));\n let over = false;\n if (!target) {\n // Least-loaded truck that day, so the overage is as small as possible.\n target = onDay.slice().sort((a, b) => unitsIn(a) - unitsIn(b))[0];\n over = true;\n }\n\n target.stops.push(stop);\n unassigned.splice(unassigned.indexOf(stop), 1);\n if (over) {\n stop.overPacked = true;\n const cap = slotCapacities[Math.min(target.slot, slotCapacities.length - 1)];\n promoted.push(\n `${stop.accountName} -> ${dayOf(target.slot).day} ${stop.pinnedDate} ` +\n `OVER CAPACITY (${unitsIn(target)}/${cap} units)`,\n );\n } else {\n promoted.push(`${stop.accountName} -> ${dayOf(target.slot).day} ${stop.pinnedDate}`);\n }\n }\n\n // Relief pass. A truck can end up over capacity because texted stops were\n // forced onto it; anything on that truck that has NOT been texted can still\n // be moved to a later day with room. Nobody has been promised a date for\n // those, so shifting them is free - and it is the only lever left, since the\n // texted ones are fixed by definition.\n //\n // Smallest first: it takes more moves, but it disturbs the fewest large\n // deliveries and keeps the day's shape closest to what the packer intended.\n for (const src of slots) {\n const cap = () => slotCapacities[Math.min(src.slot, slotCapacities.length - 1)];\n if (unitsIn(src) <= cap()) continue;\n const srcDay = src.slot % days.length;\n\n const movable = src.stops\n .filter((s) => !s.pinnedDate)\n .sort((a, b) => a.units - b.units);\n\n for (const stop of movable) {\n if (unitsIn(src) <= cap()) break;\n // Later days first - pushing work forward is what the spare Saturday is\n // for - then earlier days if that is all there is.\n const target = slots\n .map((s) => s.slot)\n .filter((t) => t !== src.slot && allowedOn(stop, t) && fits(t, stop))\n .sort((a, b) => {\n // A truck already on the road first, whichever day it runs. Relieving\n // one over-packed truck by dispatching a second one is no relief at\n // all - it just moves the cost from the cube to the payroll.\n const ra = alreadyOut(a) ? 0 : 1;\n const rb = alreadyOut(b) ? 0 : 1;\n if (ra !== rb) return ra - rb;\n // Then the route that passes nearest, rounded to 5km so that days\n // which are much of a muchness geographically still fall back to the\n // old preference for pushing work later in the week.\n const na = Math.round(reachKm(stop, a) / 5);\n const nb = Math.round(reachKm(stop, b) / 5);\n if (na !== nb) return na - nb;\n const la = (a % days.length) > srcDay ? 0 : 1;\n const lb = (b % days.length) > srcDay ? 0 : 1;\n if (la !== lb) return la - lb;\n return Math.abs((a % days.length) - srcDay) - Math.abs((b % days.length) - srcDay);\n })[0];\n if (target === undefined) continue;\n\n src.stops.splice(src.stops.indexOf(stop), 1);\n bySlot.get(target)!.stops.push(stop);\n relieved.push(\n `${stop.accountName} (${stop.units}u): ${dayOf(src.slot).day} -> ${dayOf(target).day}` +\n ` [${dayOf(src.slot).day} now ${unitsIn(src)}/${cap()}]`,\n );\n }\n }\n\n // Consolidate. The packer opens a second truck for a day only when the first\n // is full, but the repair passes above move stops one at a time onto whatever\n // slot has room - so a day can finish with two half-empty trucks. Two drivers\n // for 87 units is a waste when one carries 100.\n //\n // Same-day only, so this cannot break a texted date or an allowed day: both\n // are about which day, not which truck.\n //\n // Capacity is not the only test. Two trucks working two different places both\n // fit on one vehicle on paper, and merging them produced a Miami Thursday of\n // 26 stops in two tight clumps joined by a single 119km leg - Aventura and\n // Hobe Sound, 185 units, on one van. So the groups have to be neighbours as\n // well as fit: if the gap between them is wider than either group is itself,\n // they are separate territories and stay on separate trucks.\n const spanKm = (list: Stop[]) => {\n let w = 0;\n for (let i = 0; i < list.length; i++) {\n for (let j = i + 1; j < list.length; j++) w = Math.max(w, apartKm(list[i], list[j]));\n }\n return w;\n };\n const gapKm = (a: Stop[], b: Stop[]) => {\n let g = Infinity;\n for (const x of a) for (const y of b) g = Math.min(g, apartKm(x, y));\n return g;\n };\n for (let d = 0; d < days.length; d++) {\n const onDay = slots\n .filter((s) => s.slot % days.length === d && s.stops.length > 0)\n .sort((a, b) => a.slot - b.slot); // vehicle 1 first\n if (onDay.length < 2) continue;\n\n for (let i = onDay.length - 1; i > 0; i--) {\n const donor = onDay[i];\n if (donor.stops.length === 0) continue;\n for (let j = 0; j < i; j++) {\n const host = onDay[j];\n const hostCap = hardCapacities[Math.min(host.slot, hardCapacities.length - 1)];\n const reach = Math.max(spanKm(host.stops), spanKm(donor.stops), MIN_MERGE_GAP_KM);\n if (\n unitsIn(host) + unitsIn(donor) <= hostCap &&\n host.stops.length + donor.stops.length <= maxStops &&\n gapKm(host.stops, donor.stops) <= reach\n ) {\n const count = donor.stops.length;\n const units = unitsIn(donor);\n host.stops.push(...donor.stops);\n donor.stops.length = 0;\n relieved.push(\n `${dayOf(donor.slot).day}: merged ${count} stop(s)/${units}u onto one truck ` +\n `(now ${unitsIn(host)}/${hostCap})`,\n );\n break;\n }\n }\n }\n }\n\n return { moved, stuck, promoted, relieved };\n}\n\n// How lopsided two days must be before the tail is worth correcting, as a share\n// of what the day can carry. Miami and Florida run every truck at 90-100% of\n// cube, so a flat figure either fires on a 96-versus-99 difference there - noise,\n// not a back-loaded week, and each such move reshuffles a day for nothing - or\n// is set high enough to avoid that and then gives up on DC's single 100-unit van\n// while Saturday is still 16 units heavier than Friday. A tenth of the day's own\n// capacity means the same thing in both places.\nconst MIN_TAIL_GAP_SHARE = 0.1;\n\n// How far out of its way a stop may be dragged to correct that tail. Not zero:\n// the whole point is to move work off an overloaded day, and the nearest\n// candidate will rarely also be the very nearest stop on the map. But a day\n// being busy is no reason to send a van across the region.\nconst MAX_REDAY_DETOUR_KM = 10;\n\n// Settle the week after every rule has finished firing.\n//\n// The passes above move stops one at a time, each reacting to the state at the\n// moment it runs, and that state keeps changing underneath them. DC showed the\n// problem plainly: a 50-unit stop had to leave Friday, Monday had to be cleared\n// to receive it, and the work cleared off Monday was judged against a Friday\n// that still had the 50 units on it. Friday looked full, so three stops went to\n// Saturday instead - and the moment the 50 units left, Friday dropped to 49\n// against Saturday's 93. Every individual decision was right when it was made.\n//\n// So this runs last, when nothing else will move, and asks a simpler question\n// of each stop: is there an earlier day it would be better off on?\n//\n// A move needs a genuinely lighter earlier day to go to, and must not make the\n// geography worse getting there. Both, always - being merely nearer is not\n// enough on its own, and this is the part that took several attempts to get\n// right, so it is worth writing down why.\n//\n// The obvious design is \"move any stop to a day whose route passes nearer\".\n// Tried three ways - crow-flies to the truck, crow-flies to the day, real road\n// distance to the day - and every one of them made Florida worse, by 45km, then\n// 103km, then 108km, while each individual move looked like a 5-15km\n// improvement. The reason is that Florida runs every truck at 95-100% of its\n// cube. There is no slack anywhere, so a stop arriving on an \"easier\" day\n// forces that day's two vans to be re-cut, and the re-cut costs far more than\n// the proximity gain. Proximity to one stop simply does not predict what\n// happens to a full day's route.\n//\n// Where the tail really is back-loaded there is slack by definition, and the\n// arithmetic works. That is DC's case and the one this was built for: Saturday\n// carrying 93 units against Friday's 49. So the load gap is the trigger, and\n// distance is only a veto - it stops the fix from dragging a stop somewhere\n// silly, rather than being a reason to move anything by itself.\n//\n// Every move goes to an earlier day, never a later one. That is the\n// front-loading, and it is also what guarantees this terminates: each move\n// strictly lowers the total of unit-days, which cannot fall for ever.\n//\n// Only untexted stops on days they are allowed, and only onto trucks already\n// going out, so nothing here can break a promise or hire a van.\nfunction balanceDays(\n slots: { slot: number; stops: Stop[] }[],\n days: { day: string; date: string }[],\n slotCapacities: number[],\n maxStops: number,\n // Real road distances, not crow-flies.\n //\n // apartKm was the obvious thing to reach for and it is wrong here, because\n // Tampa Bay is water. Straight-line, Pinellas and Tampa are neighbours; by\n // road they are a bridge and half an hour apart. Judging moves on the crow's\n // route had Florida cheerfully hopping stops across the bay for a \"nearer\"\n // day and adding 100km to the week. The matrix knows about bridges.\n allStops: Stop[],\n distances: number[][],\n): string[] {\n const indexOf = new Map(allStops.map((s, i) => [s.opportunityId, i]));\n const roadKm = (a: Stop, b: Stop) => {\n const i = indexOf.get(a.opportunityId);\n const j = indexOf.get(b.opportunityId);\n if (i === undefined || j === undefined) return apartKm(a, b);\n return distances[i + 1][j + 1] / 1000;\n };\n const notes: string[] = [];\n const unitsIn = (list: Stop[]) => list.reduce((a, x) => a + x.units, 0);\n const capOf = (slot: number) => slotCapacities[Math.min(slot, slotCapacities.length - 1)];\n\n // Everything here is per DAY, not per truck.\n //\n // A slot is one truck on one day, but rebalanceDays re-splits each day across\n // its own trucks immediately after this runs, so which truck a stop sits on\n // now is not information - it is about to be overwritten. Measuring against a\n // single truck's stops was doing exactly that: in Florida it kept finding a\n // stop \"8km nearer\" to the other van on a day whose two vans were then\n // re-cut anyway, and the shuffle cost more mileage than the move saved.\n //\n // The only thing this pass genuinely decides is which DAY a stop belongs to.\n const trucksOn = (d: number) => slots.filter((s) => s.slot % days.length === d);\n const stopsOn = (d: number) => trucksOn(d).flatMap((s) => s.stops);\n // Only the trucks actually going out that day: an idle slot is not capacity\n // this pass may spend, since filling it would mean hiring a driver.\n const capacityOn = (d: number) =>\n trucksOn(d).filter((t) => t.stops.length > 0).reduce((a, t) => a + capOf(t.slot), 0);\n\n const allowedOn = (stop: Stop, d: number) => {\n if (stop.pinnedDate) return days[d].date === stop.pinnedDate;\n if (stop.allowedDays) return stop.allowedDays.includes(days[d].day);\n return true;\n };\n // Distance to the nearest stop already on a day, optionally ignoring one -\n // used to ask \"how far would this be from the day it is leaving, once it has\n // left\", which is the only fair comparison against the day it would join.\n const reachKm = (stop: Stop, d: number, ignore?: Stop) => {\n const others = stopsOn(d).filter((s) => s !== ignore);\n if (others.length === 0) return Infinity;\n return Math.min(...others.map((s) => roadKm(stop, s)));\n };\n // A day can take a stop only on a truck that is already going out and has\n // room for it; a day with no such truck cannot take it at any distance.\n const truckWithRoom = (d: number, stop: Stop) =>\n trucksOn(d)\n .filter((t) =>\n t.stops.length > 0 && t.stops.length < maxStops &&\n unitsIn(t.stops) + stop.units <= capOf(t.slot)\n )\n .sort((a, b) => unitsIn(a.stops) - unitsIn(b.stops))[0];\n\n // One move per round, the biggest improvement first, so the week converges on\n // the same answer whatever order the stops happen to be in.\n for (let round = 0; round < 40; round++) {\n let best: { stop: Stop; from: number; to: number; gain: number } | null = null;\n\n for (let srcDay = 1; srcDay < days.length; srcDay++) {\n const srcUnits = unitsIn(stopsOn(srcDay));\n\n for (const truck of trucksOn(srcDay)) {\n for (const stop of truck.stops) {\n if (stop.pinnedDate) continue;\n const here = reachKm(stop, srcDay, stop);\n\n for (let dstDay = 0; dstDay < srcDay; dstDay++) { // earlier days only\n if (!allowedOn(stop, dstDay)) continue;\n if (!truckWithRoom(dstDay, stop)) continue;\n const dstUnits = unitsIn(stopsOn(dstDay));\n\n // Trigger: this day is carrying materially more than that one, and\n // the move does not simply reverse the imbalance.\n const material = capacityOn(dstDay) * MIN_TAIL_GAP_SHARE;\n if (srcUnits - dstUnits < material) continue;\n if (dstUnits + stop.units > srcUnits) continue;\n // Veto: and it does not land the stop somewhere absurd.\n const gain = here - reachKm(stop, dstDay);\n if (gain < -MAX_REDAY_DETOUR_KM) continue;\n if (!best || gain > best.gain) {\n best = { stop, from: srcDay, to: dstDay, gain };\n }\n }\n }\n }\n }\n\n if (!best) break;\n const from = trucksOn(best.from).find((t) => t.stops.includes(best!.stop))!;\n const to = truckWithRoom(best.to, best.stop)!;\n from.stops.splice(from.stops.indexOf(best.stop), 1);\n to.stops.push(best.stop);\n notes.push(\n `${best.stop.accountName} (${best.stop.units}u): ${days[best.from].day} -> ` +\n `${days[best.to].day} (${best.gain > 0 ? \"+\" : \"\"}${best.gain.toFixed(0)}km) ` +\n `[${days[best.from].day} now ${unitsIn(stopsOn(best.from))}, ` +\n `${days[best.to].day} ${unitsIn(stopsOn(best.to))}]`,\n );\n }\n return notes;\n}\n\n// A day packed full of committed Route Dates has no earlier day to shed to, and\n// balanceDays only ever moves work earlier. So whatever unpinned stop happens to\n// fit in the last few units of that day stays there, however far out of the way\n// it is - DC put William Yopp in Manassas on a Wednesday that was 99/100 units\n// of pinned DC-core work, while Friday ran Woodbridge, Stafford and\n// Fredericksburg past his door with 52 units spare.\n//\n// This is the one case where a stop should be allowed to move LATER. It is kept\n// as its own pass rather than folded into balanceDays because it inverts that\n// function's two invariants deliberately, and only under conditions narrow\n// enough that they cannot recur elsewhere:\n//\n// - The source day must be nearly full, so this never fires on a slack week\n// and reshuffles days for nothing.\n// - The destination must still have real headroom AFTER taking the stop.\n// This is the guard that matters. balanceDays documents at length why\n// proximity-driven moves wrecked Florida - every truck there runs at\n// 95-100% of cube, so an arriving stop forces a re-cut that costs more than\n// the proximity gain. Requiring the destination to stay comfortably below\n// capacity means a region with no slack anywhere produces no moves at all,\n// which is precisely the Florida case.\n// - The stop must be materially nearer, by a positive threshold. Here\n// distance is the reason to move rather than merely a veto, so it has to\n// clear a real bar, not just avoid being absurd.\n// - Pinned stops are never touched: a committed Route Date outranks geography.\n//\n// Termination is by construction: each stop may be reseated at most once.\nconst RESEAT_SOURCE_FULL_SHARE = 0.9;\nconst RESEAT_HEADROOM_SHARE = 0.15;\nconst MIN_RESEAT_GAIN_KM = 5;\n\nfunction reseatStragglers(\n slots: { slot: number; stops: Stop[] }[],\n days: { day: string; date: string }[],\n slotCapacities: number[],\n maxStops: number,\n allStops: Stop[],\n distances: number[][],\n): string[] {\n const indexOf = new Map(allStops.map((s, i) => [s.opportunityId, i]));\n const roadKm = (a: Stop, b: Stop) => {\n const i = indexOf.get(a.opportunityId);\n const j = indexOf.get(b.opportunityId);\n if (i === undefined || j === undefined) return apartKm(a, b);\n return distances[i + 1][j + 1] / 1000;\n };\n const notes: string[] = [];\n const unitsIn = (list: Stop[]) => list.reduce((a, x) => a + x.units, 0);\n const capOf = (slot: number) => slotCapacities[Math.min(slot, slotCapacities.length - 1)];\n\n const trucksOn = (d: number) => slots.filter((s) => s.slot % days.length === d);\n const stopsOn = (d: number) => trucksOn(d).flatMap((s) => s.stops);\n // Only trucks already going out - filling an idle slot would mean hiring.\n const capacityOn = (d: number) =>\n trucksOn(d).filter((t) => t.stops.length > 0).reduce((a, t) => a + capOf(t.slot), 0);\n const allowedOn = (stop: Stop, d: number) =>\n stop.allowedDays ? stop.allowedDays.includes(days[d].day) : true;\n const reachKm = (stop: Stop, d: number, ignore?: Stop) => {\n const others = stopsOn(d).filter((s) => s !== ignore);\n if (others.length === 0) return Infinity;\n return Math.min(...others.map((s) => roadKm(stop, s)));\n };\n const truckWithRoom = (d: number, stop: Stop) =>\n trucksOn(d)\n .filter((t) =>\n t.stops.length > 0 && t.stops.length < maxStops &&\n unitsIn(t.stops) + stop.units <= capOf(t.slot)\n )\n .sort((a, b) => unitsIn(a.stops) - unitsIn(b.stops))[0];\n\n const reseated = new Set();\n\n for (let round = 0; round < 40; round++) {\n let best: { stop: Stop; from: number; to: number; gain: number } | null = null;\n\n for (let srcDay = 0; srcDay < days.length; srcDay++) {\n const srcCap = capacityOn(srcDay);\n if (srcCap === 0) continue;\n // Only shed from a day that is genuinely full. A day with room to spare\n // has no straggler problem - the stop is there because it belongs there.\n if (unitsIn(stopsOn(srcDay)) < srcCap * RESEAT_SOURCE_FULL_SHARE) continue;\n\n for (const truck of trucksOn(srcDay)) {\n for (const stop of truck.stops) {\n if (stop.pinnedDate) continue;\n if (reseated.has(stop.opportunityId)) continue;\n const here = reachKm(stop, srcDay, stop);\n\n for (let dstDay = 0; dstDay < days.length; dstDay++) {\n if (dstDay === srcDay) continue;\n if (!allowedOn(stop, dstDay)) continue;\n const dstCap = capacityOn(dstDay);\n if (dstCap === 0) continue;\n // The destination has to stay comfortably clear of its cube, not\n // merely fit the stop in. See the note above on Florida.\n if (\n unitsIn(stopsOn(dstDay)) + stop.units >\n dstCap * (1 - RESEAT_HEADROOM_SHARE)\n ) continue;\n if (!truckWithRoom(dstDay, stop)) continue;\n\n const gain = here - reachKm(stop, dstDay);\n if (gain < MIN_RESEAT_GAIN_KM) continue;\n if (!best || gain > best.gain) best = { stop, from: srcDay, to: dstDay, gain };\n }\n }\n }\n }\n\n if (!best) break;\n const from = trucksOn(best.from).find((t) => t.stops.includes(best!.stop))!;\n const to = truckWithRoom(best.to, best.stop)!;\n from.stops.splice(from.stops.indexOf(best.stop), 1);\n to.stops.push(best.stop);\n reseated.add(best.stop.opportunityId);\n notes.push(\n `${best.stop.accountName} (${best.stop.units}u): ${days[best.from].day} -> ` +\n `${days[best.to].day} (${best.gain.toFixed(0)}km nearer) ` +\n `[${days[best.from].day} now ${unitsIn(stopsOn(best.from))}, ` +\n `${days[best.to].day} ${unitsIn(stopsOn(best.to))}]`,\n );\n }\n return notes;\n}\n\n// Clean the boundary between two trucks sharing a day.\n//\n// The test is the one a dispatcher applies by eye: no stop should have its\n// nearest neighbour on the other truck. When that happens the two vans are\n// threading through each other and somebody drives past a door they could have\n// served. Growing zones gets the shape roughly right, but the seam is decided\n// by capacity rather than geography, and any stop that fitted no zone is simply\n// dropped on the lightest truck - so a handful always end up on the wrong side.\n//\n// Only violating stops are candidates, and each is offered two ways out: move\n// it to the truck holding its nearest neighbour, or swap it with a stop over\n// there. The swap matters because both vans are usually near full on the heavy\n// days, and a swap barely moves the loads while a move needs real headroom.\n//\n// Every candidate is scored on the whole day's violation count, and nothing is\n// applied unless that count strictly falls, so this cannot make the seam worse.\n// It also cannot always reach zero: with both trucks full and a dense corridor\n// between them, some seam is arithmetic rather than a mistake. The count that\n// remains is returned so the build can say so instead of implying a clean cut.\nfunction separateTrucks(\n daySlots: { slot: number; stops: Stop[] }[],\n stops: Stop[],\n indexOf: Map,\n dist: Dist,\n caps: number[],\n maxStops: number,\n): number {\n if (daySlots.length < 2) return 0;\n const at = (s: Stop) => indexOf.get(s.opportunityId)!;\n const unitsOf = (list: Stop[]) => list.reduce((a, s) => a + s.units, 0);\n\n // Which stops are on the wrong side, by nearest-neighbour road distance.\n const offside = () => {\n const bad: { stop: Stop; from: number; to: number }[] = [];\n for (let a = 0; a < daySlots.length; a++) {\n for (const s of daySlots[a].stops) {\n let own = Infinity;\n let best = Infinity;\n let bestSlot = -1;\n for (let b = 0; b < daySlots.length; b++) {\n for (const o of daySlots[b].stops) {\n if (o === s) continue;\n const d = dist(at(s), at(o));\n if (b === a) own = Math.min(own, d);\n else if (d < best) { best = d; bestSlot = b; }\n }\n }\n if (bestSlot >= 0 && best < own) bad.push({ stop: s, from: a, to: bestSlot });\n }\n }\n return bad;\n };\n\n const fits = (n: number, extra: Stop, without?: Stop) => {\n const list = daySlots[n].stops;\n const count = list.length + 1 - (without ? 1 : 0);\n const load = unitsOf(list) + extra.units - (without?.units ?? 0);\n return count <= maxStops && load <= caps[n];\n };\n const move = (s: Stop, from: number, to: number) => {\n daySlots[from].stops.splice(daySlots[from].stops.indexOf(s), 1);\n daySlots[to].stops.push(s);\n };\n\n let bad = offside();\n for (let round = 0; round < 40 && bad.length > 0; round++) {\n let bestCount = bad.length;\n let apply: (() => void) | null = null;\n\n for (const v of bad) {\n // Straight move, if the other truck has the room.\n if (fits(v.to, v.stop)) {\n move(v.stop, v.from, v.to);\n const after = offside().length;\n move(v.stop, v.to, v.from);\n if (after < bestCount) {\n bestCount = after;\n apply = () => move(v.stop, v.from, v.to);\n }\n }\n // Or trade places with something over there, which needs no headroom.\n for (const partner of [...daySlots[v.to].stops]) {\n if (!fits(v.to, v.stop, partner) || !fits(v.from, partner, v.stop)) continue;\n move(v.stop, v.from, v.to);\n move(partner, v.to, v.from);\n const after = offside().length;\n move(partner, v.from, v.to);\n move(v.stop, v.to, v.from);\n if (after < bestCount) {\n bestCount = after;\n apply = () => {\n move(v.stop, v.from, v.to);\n move(partner, v.to, v.from);\n };\n }\n }\n }\n\n if (!apply) break;\n apply();\n bad = offside();\n }\n return bad.length;\n}\n\n// Re-plan each day after the rules have finished shuffling stops between days.\n//\n// Everything above moves stops by rule - a texted date, an allowed day, a truck\n// over capacity - and none of it looks at a map. Stops get appended to whichever\n// truck had room, so a stop can sit on TPA while SP drives past its door, and a\n// moved stop lands at the end of the driving order instead of where it belongs.\n//\n// The constraints are all about WHICH DAY. Within a day the trucks and the\n// order are free, so this re-splits each day's work geographically across that\n// day's trucks and re-optimises the sequence. No stop changes day, so no\n// promise or allowed-day rule can be broken by it.\nfunction rebalanceDays(\n slots: { slot: number; stops: Stop[] }[],\n days: { day: string; date: string }[],\n stops: Stop[],\n distances: number[][],\n depot: { lat: number; lon: number },\n hardCapacities: number[],\n maxStops: number,\n // Split the day's stops the same way the week was split, so a banded region\n // keeps its shape instead of being re-cut into radial wedges here.\n orientation: Orientation = \"radial\",\n): string[] {\n const indexOf = new Map(stops.map((s, i) => [s.opportunityId, i]));\n const dist: Dist = (from, to) => distances[from + 1][to + 1];\n const notes: string[] = [];\n\n for (let d = 0; d < days.length; d++) {\n const everySlot = slots.filter((s) => s.slot % days.length === d);\n const all = everySlot.flatMap((s) => s.stops);\n if (all.length === 0) continue;\n\n // Re-split across the trucks this day is ALREADY using, not every truck the\n // fleet owns. A day given one van on purpose - because the packer was\n // minimising routes, or because the second van is a rental - must not have\n // a second one handed to it here just because the slot exists. Only if the\n // day genuinely will not fit on the trucks it has does another get opened.\n const dayUnits = all.reduce((a, s) => a + s.units, 0);\n const inUse = everySlot.filter((s) => s.stops.length > 0);\n const roomInUse = inUse.reduce(\n (a, s) => a + hardCapacities[Math.min(s.slot, hardCapacities.length - 1)],\n 0,\n );\n const daySlots = inUse.length > 0 && dayUnits <= roomInUse ? inUse : everySlot;\n\n if (daySlots.length > 1) {\n const caps = daySlots.map((s) =>\n hardCapacities[Math.min(s.slot, hardCapacities.length - 1)]\n );\n const before = daySlots.map((s) => s.stops.length).join(\"/\");\n\n if (orientation === \"zoned\") {\n // Same rule as the week split: re-grow the day's zones rather than\n // sweeping. Two trucks sharing a day may be working two places 100km\n // apart, and a sweep here would blend them straight back together -\n // undoing the zoning one day at a time.\n const idx = all.map((s) => indexOf.get(s.opportunityId)!)\n .filter((i) => i !== undefined);\n const order = caps.map((cap, n) => ({ cap, n })).sort((a, b) => b.cap - a.cap);\n const zoneCaps = order.map((x) => x.cap);\n const { zones, leftovers } = growZones(idx, stops, dist, zoneCaps, maxStops);\n tidyZones(zones, stops, dist, zoneCaps, maxStops);\n\n // Heaviest zone onto the roomiest truck - the same pairing the week\n // split uses, and for the same reason. Zones come back in growth order,\n // which is geographic, so pairing them positionally hands zone 0 to the\n // first slot whatever its size. When a day collapses into a single zone\n // that put the empty one on the owned van and all the work on the\n // rental: a DC Thursday of 15 stops dispatched to DC Van-2 with DC Van\n // sitting idle. Sorting by size fixes the labelling, and because `order`\n // is stable across equal capacities, an empty zone falls to the last\n // slot - which is exactly the truck you least want to send out.\n daySlots.forEach((s) => (s.stops = []));\n zones\n .map((z) => ({ z, units: z.reduce((a, i) => a + stops[i].units, 0) }))\n .sort((a, b) => b.units - a.units)\n .forEach((x, rank) => {\n if (rank < order.length) daySlots[order[rank].n].stops = x.z.map((i) => stops[i]);\n });\n // A stop that fitted nowhere still has to go out today. Give it to the\n // truck whose work it is nearest to, not the one carrying least: these\n // are precisely the stops that end up on the wrong side of the seam,\n // and \"lightest\" ignores the map entirely. Load only breaks ties among\n // trucks with room, and a full one is skipped rather than overloaded.\n const load = (s: { stops: Stop[] }) => s.stops.reduce((u, x) => u + x.units, 0);\n for (const i of leftovers) {\n const stop = stops[i];\n const near = (s: { slot: number; stops: Stop[] }) =>\n s.stops.length === 0 ? Infinity : Math.min(...s.stops.map((o) => dist(i, indexOf.get(o.opportunityId)!)));\n const room = daySlots.filter((s, n) =>\n load(s) + stop.units <= caps[n] && s.stops.length < maxStops\n );\n // Copied before sorting: daySlots is positional - its order maps to\n // the slots and to caps - so sorting it in place silently re-labels\n // which truck is which. That put a whole DC Thursday on the rental\n // and left the owned van empty.\n const pool = [...(room.length > 0 ? room : daySlots)]\n .sort((a, b) => near(a) - near(b) || load(a) - load(b));\n pool[0].stops.push(stop);\n }\n\n // Finally, tidy the seam. Zones are grown against capacity, so the line\n // between them lands where the cube says rather than where the map does.\n const left = separateTrucks(daySlots, stops, indexOf, dist, caps, maxStops);\n if (left > 0) {\n notes.push(\n `${days[d].day}: ${left} stop(s) still nearer the other truck - ` +\n `both trucks are too full to trade them`,\n );\n }\n } else {\n // Sweep the day's stops by compass angle from the depot and cut at\n // capacity, so each truck gets a contiguous wedge instead of whatever the\n // rules happened to leave it.\n const key = (s: Stop) =>\n orientation === \"east-west\"\n ? -s.lat // north to south, matching how the week was banded\n : orientation === \"north-south\"\n ? -s.lon\n : Math.atan2(s.lat - depot.lat, s.lon - depot.lon);\n const swept = all\n .map((s) => ({ s, a: key(s) }))\n .sort((x, y) => x.a - y.a)\n .map((x) => x.s);\n daySlots.forEach((s) => (s.stops = []));\n\n // Split by each truck's SHARE of the day's capacity, not by filling the\n // first truck up and dumping the rest. Filling first is what put 146\n // units on a 100-unit van: the 192 truck hit the 30-stop cap at 135\n // units and everything heavy that was left had nowhere else to go.\n //\n // Unit capacity is physical and the stop cap is a working preference, so\n // when they conflict the stop cap gives way - one extra drop beats a load\n // that will not fit on the vehicle.\n const totalUnits = all.reduce((a, s) => a + s.units, 0);\n const totalCap = caps.reduce((a, c) => a + c, 0) || 1;\n const targets = caps.map((c) => (totalUnits * c) / totalCap);\n\n let truck = 0;\n let used = 0;\n for (const stop of swept) {\n const pastShare = used > 0 && used + stop.units > targets[truck];\n if (pastShare && truck < daySlots.length - 1) {\n truck++;\n used = 0;\n }\n daySlots[truck].stops.push(stop);\n used += stop.units;\n }\n }\n for (const s of daySlots) {\n if (s.stops.length > maxStops) {\n notes.push(\n `${days[d].day} ${slotLabel(s.slot, days).vehicle}: ${s.stops.length} stops, ` +\n `over the ${maxStops} cap - kept together so the smaller truck is not overloaded`,\n );\n }\n }\n const after = daySlots.map((s) => s.stops.length).join(\"/\");\n if (before !== after) {\n notes.push(`${days[d].day}: re-split ${all.length} stops across trucks, ${before} -> ${after}`);\n }\n }\n\n // Order every truck properly, including the ones that were not re-split.\n for (const s of daySlots) {\n if (s.stops.length < 2) continue;\n const idx = s.stops.map((x) => indexOf.get(x.opportunityId)!).filter((i) => i !== undefined);\n if (idx.length !== s.stops.length) continue; // defensive: unknown stop\n s.stops = twoOpt(nearestNeighbour(idx, dist), dist).map((i) => stops[i]);\n }\n }\n return notes;\n}\n\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 // Rows carrying a stop that was forced on over capacity, so they can be\n // turned red once the values are in. Tracked per day block because each day\n // occupies its own column range.\n const overPackedCells: { date: string; blockRow: number }[] = [];\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 if (s.overPacked) overPackedCells.push({ date, blockRow: blockRows.length });\n blockRows.push([\n `Vehicle ${vehicle}`,\n i + 1,\n // The note rides on the stop name so it is impossible to miss on a\n // printed sheet, red or not.\n s.overPacked ? `*** OVER CAPACITY - TEXT SENT *** ${s.opportunityName}` : 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\n // Values carry no formatting, so the red has to be a second call. Best effort:\n // the note text is already in the cell, so a failure here loses the colour\n // but never the warning.\n if (overPackedCells.length > 0) {\n try {\n const meta = await (await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}?fields=sheets.properties`,\n { headers: { Authorization: `Bearer ${token}` } },\n )).json();\n const gid = (meta.sheets ?? [])\n .map((s: any) => s.properties)\n .find((p: any) => p.title === tab)?.sheetId;\n\n if (gid !== undefined) {\n // +2 for the day header and sub-header rows above the grid.\n const headerRows = 2;\n const requests = overPackedCells.map(({ date, blockRow }) => {\n const di = days.findIndex((d) => d.date === date);\n return {\n repeatCell: {\n range: {\n sheetId: gid,\n startRowIndex: headerRows + blockRow,\n endRowIndex: headerRows + blockRow + 1,\n startColumnIndex: di * STRIDE,\n endColumnIndex: di * STRIDE + BLOCK_HEADERS.length,\n },\n cell: {\n userEnteredFormat: {\n textFormat: { bold: true, foregroundColor: { red: 0.8, green: 0.1, blue: 0.1 } },\n },\n },\n fields: \"userEnteredFormat.textFormat\",\n },\n };\n });\n const fmt = await fetch(\n `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}:batchUpdate`,\n {\n method: \"POST\",\n headers: { Authorization: `Bearer ${token}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ requests }),\n },\n );\n console.log(\n fmt.ok\n ? `Marked ${overPackedCells.length} over-capacity row(s) red on \"${tab}\"`\n : `Red formatting failed (${fmt.status}); the OVER CAPACITY text is still on the row`,\n );\n }\n } catch (err) {\n console.log(`Red formatting skipped: ${String(err).slice(0, 120)}`);\n }\n }\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 // Which week does week 0 actually schedule?\n //\n // Normally the one we are in. But once its last weekday has gone - Friday\n // evening onwards, and all day Saturday - there is nothing left to plan and\n // week 0 becomes the following week. Every other offset counts from whichever\n // week that turns out to be, so \"next week\" is always the week after the one\n // on screen. Counting from the calendar week instead meant that on a Friday\n // or a Saturday both tabs resolved to the same days.\n const openThisWeek = remainingDeliveryDays(today, thisSaturday);\n const baseSaturday = openThisWeek.length > 0 ? thisSaturday : addDays(thisSaturday, 7);\n const targetSaturday = addDays(baseSaturday, 7 * weekOffset);\n\n // Weekdays only for now. Saturday is added below, and only if the stops that\n // come back cannot fit inside Mon-Fri.\n let days = weekOffset === 0 && openThisWeek.length > 0\n ? openThisWeek\n : weekdaysEnding(targetSaturday);\n\n // The week rolls forward on a Friday, straight past tomorrow to next Monday.\n // That silently abandons Saturday work: dispatch\n // routinely dates stops for the Saturday and texts the customer, and those\n // records were landing on next Monday with the promise already broken.\n //\n // Saturday stays overflow-only in the normal case - this is not a standing\n // sixth day. It is scheduled only when work is already committed to it.\n if (\n weekOffset === 0 && region.includeSaturday &&\n thisSaturday > today && days[0].date > thisSaturday\n ) {\n const waiting = await countDueOn(region, thisSaturday);\n if (waiting > 0) {\n days = [{ day: dayName(thisSaturday), date: thisSaturday }];\n console.log(\n `${waiting} stop(s) already dated ${thisSaturday} - scheduling tomorrow's ` +\n `Saturday rather than rolling forward to next week`,\n );\n }\n }\n\n // The demand window has to end with the days actually being scheduled, not\n // with the calendar week the run started in. On a Friday or Saturday nothing\n // is left this week, so deliveryDays rolls forward to next Monday - and if\n // endDate stayed on this Saturday the SOQL would plan Mon-Fri while only\n // pulling work due by the weekend. That is how a Friday rebuild produced 9\n // Miami stops when the same five days really hold 230.\n //\n // A run that ends on a Saturday is already at its own window end; walking\n // forward would hand a single Saturday a whole week of demand.\n const lastDay = days[days.length - 1].date;\n const endDate = dayOfWeek(lastDay) === 6 ? lastDay : saturdayAfter(days).date;\n\n // How far back to look for orders.\n //\n // The week being driven sweeps up everything still outstanding, three months\n // back - that is the only plan overdue work can land on, and without it an\n // order missed in June is invisible for ever.\n //\n // A forward week takes its own Sunday to Saturday and nothing else. Overdue\n // orders belong to the week actually going out, not to one being planned:\n // letting them through put an order due Friday the 21st on the following\n // Monday's truck while the current week had never picked it up at all.\n const windowStart = weekOffset === 0\n ? monthsBefore(endDate, OVERDUE_LOOKBACK_MONTHS)\n : weekStartFor(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 // Let Salesforce overrule the ledger.\n //\n // The ledger is the builder's own memory of what it planned, written before\n // there was any way to record that fact in Salesforce. It is a guess about\n // reality, and nothing has ever checked it against the CRM - so a stop could\n // be \"used up\" in the ledger while Salesforce still showed it undelivered and\n // due later in the week, invisible to the plan and to dispatch alike. Rick\n // Carlson sat in exactly that gap: ledgered for Tuesday, no Route Date, close\n // date Thursday, on nobody's truck.\n //\n // So each rebuild now re-reads the ledgered records. A stop that is still\n // Delivery Due and is not routed for today was never actually committed,\n // whatever the ledger remembers, and goes back on the map. Anything the CRM\n // does not return, or that has moved off Delivery Due, is left excluded - the\n // ledger keeps its authority over stops Salesforce knows nothing about.\n //\n // As Route Date gets written on commit this correction fires less and less,\n // and the ledger fades into a fallback rather than a second opinion.\n if (alreadyAssigned.size > 0) {\n const ids = [...alreadyAssigned];\n const stale = new Set();\n for (let i = 0; i < ids.length; i += 200) {\n const chunk = ids.slice(i, i + 200).map((id) => `'${id}'`).join(\",\");\n const rows = await sfQuery(\n `SELECT Id, StageName, Route_Date__c FROM Opportunity WHERE Id IN (${chunk})`,\n );\n for (const r of rows) {\n const routedForToday = r.Route_Date__c && r.Route_Date__c === today;\n if (r.StageName === \"Delivery Due\" && !routedForToday) stale.add(r.Id);\n }\n }\n if (stale.size > 0) {\n for (const id of stale) alreadyAssigned.delete(id);\n console.log(\n `${stale.size} ledger entr${stale.size === 1 ? \"y\" : \"ies\"} for ${today} contradicted by ` +\n `Salesforce (still Delivery Due, not routed today) - putting them back on the map`,\n );\n }\n }\n console.log(`${alreadyAssigned.size} stop(s) already assigned to today's route - excluding them`);\n\n // Planning ahead: drop anything this week's trucks are already carrying.\n //\n // The demand window is cumulative - everything still \"Delivery Due\" from\n // windowStart onwards - which is right for the week being driven but makes a\n // forward week a superset of it. Measured on 2026-08-15, 160 of Miami's 277\n // next-week stops were due THIS week and already on a truck, so the plan\n // double-counted work about to be delivered and reported a capacity crisis\n // that did not exist.\n //\n // Reads the committed week's own snapshot rather than the dispatch ledger:\n // the ledger records what went out on a given day, and what matters here is\n // everything the current plan has spoken for, across all of its days.\n if (weekOffset > 0) {\n const committed = await blob.getJSON(snapshotKeyFor(region.key, 0)) as MapSnapshot | undefined;\n const ids = new Set((committed?.stops ?? []).filter((s) => s.day).map((s) => s.id));\n if (ids.size > 0) {\n for (const id of ids) alreadyAssigned.add(id);\n console.log(\n `${ids.size} stop(s) already committed on the current week's plan ` +\n `(${committed!.days.map((d) => d.date.slice(5)).join(\", \")}) - excluding them`,\n );\n }\n }\n\n const allOpps = await sfQuery(\n `SELECT Id, Name, CloseDate, CreatedDate, Total_Amount_of_Products__c,\n Total_Units_for_Order__c, AccountId,\n Order_Summary__c, Truck_Type__c, Route_Date__c, Route_Name__c, SMS_Sent__c,\n Email_Address__c\n FROM Opportunity\n WHERE Delivery_Region__c IN (${region.salesforceRegions.map((r) => `'${r}'`).join(\",\")})\n AND StageName = 'Delivery Due'\n AND CloseDate >= ${windowStart}\n AND ${dueBy(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_FLOOR guards the sentinels - see its definition above.\n //\n // Only a Route Date of today takes a stop off this map, never a Close Date.\n //\n // The two mean different things. Route Date says dispatch put it on today's\n // truck; Close Date only says it is due today, which is exactly the state of\n // something that still needs routing. Treating both as \"today's work\" hid\n // four DC stops that nobody had scheduled - due today, on no truck, and\n // absent from the one screen that would have caught it.\n const routedToday = (o: any) =>\n o.Route_Date__c && o.Route_Date__c >= ROUTE_DATE_FLOOR && o.Route_Date__c === today;\n\n const dueToday = allOpps.filter(routedToday);\n const oppRecords = allOpps.filter((o) => !alreadyAssigned.has(o.Id) && !routedToday(o));\n if (dueToday.length > 0) {\n console.log(\n `${dueToday.length} stop(s) already routed for today (${today}) - excluded from the map`,\n );\n }\n const dueTodayUnrouted = allOpps.filter((o) =>\n !routedToday(o) && o.CloseDate === today && !alreadyAssigned.has(o.Id)\n ).length;\n if (dueTodayUnrouted > 0) {\n console.log(\n `${dueTodayUnrouted} stop(s) due today but on no route - kept on the map to be scheduled`,\n );\n }\n console.log(\n `Found ${allOpps.length} ${region.label} opportunities due ${windowStart} to ${endDate}` +\n (weekOffset === 0\n ? ` (${OVERDUE_LOOKBACK_MONTHS}-month overdue sweep)`\n : ` (this week only - overdue orders stay on week 0)`) +\n `; ` +\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 // 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 // The last four lines are not used for routing at all - they are carried so\n // the OptimoRoute payload can match what the Cloud Run bridge already sends:\n // the load split by water type, the customer's contact details for delivery\n // notifications, and the apartment/suite that Full_Address__c buries in free\n // text.\n const ACCOUNT_FIELDS = `Id, Name, Full_Address__c,\n Client_Location__Latitude__s, Client_Location__Longitude__s,\n Lattitude__c, Longitude__c, Allowed_days__c,\n Closest_Warehouse_Latitude__c, Closest_Warehouse_Longitude__c,\n Night_Delivery__c, Override_Outstandling_Balance__c,\n Amount_of_Bottles__c, Spring_Bottle_Amount__c,\n Water_Type__c, Apt_1__c, Phone, Email_Address__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 sfQueryByIds(\n accountIds,\n (ids) =>\n `SELECT ${ACCOUNT_FIELDS}, ${BALANCE_FIELD}\n FROM Account WHERE Id IN (${ids})`.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 sfQueryByIds(\n accountIds,\n (ids) =>\n `SELECT ${ACCOUNT_FIELDS} FROM Account WHERE Id IN (${ids})`\n .replace(/\\s+/g, \" \").trim(),\n );\n }\n const accountsById = new Map(accounts.map((a) => [a.Id, a]));\n\n // Stops someone pulled off the plan by hand on the map. They stay off until\n // the window rolls, so a rebuild does not quietly put them back.\n const removedIds = await loadRemovedIds(endDate, region);\n let removedByHand = 0;\n // Carried onto the snapshot so the map can keep drawing a removed stop as a\n // grey dot instead of having it disappear. Without this the dots would last\n // only until the next rebuild, and a dispatcher who took something off on\n // Tuesday would find no trace of it on Wednesday morning.\n //\n // Stored account coordinates only, deliberately: a stop we have taken off the\n // plan is a marker, not a routing input, and it does not justify a geocode\n // call. Anything without stored coordinates is simply not drawn.\n const removedStops: Array<{\n id: string;\n name: string;\n account: string;\n address: string;\n units: number;\n closeDate: string;\n lat: number;\n lon: number;\n }> = [];\n\n const nightHeld: string[] = [];\n const balanceHeld: string[] = [];\n const routableOpps = oppRecords.filter((o) => {\n if (removedIds.has(o.Id)) {\n removedByHand++;\n const acc = accountsById.get(o.AccountId);\n const lat = Number(acc?.Client_Location__Latitude__s);\n const lon = Number(acc?.Client_Location__Longitude__s);\n if (acc && Number.isFinite(lat) && Number.isFinite(lon)) {\n removedStops.push({\n id: o.Id,\n name: o.Name,\n account: acc.Name,\n address: acc.Full_Address__c,\n units: orderUnits(o),\n closeDate: o.CloseDate,\n lat,\n lon,\n });\n }\n return false;\n }\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 (removedByHand > 0) {\n console.log(`${removedByHand} stop(s) removed from the route by hand on the map`);\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 strayCoords: string[] = []; // stored coordinate nowhere near its warehouse\n let fromStored = 0; // placed straight from the account, no geocoding needed\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 // Coordinate sources, best first:\n // 1. Lattitude__c / Longitude__c - set on ~all accounts and exact.\n // 2. Client_Location__* - the original pair, empty across the whole org.\n // 3. Geocoding the address, which is slow and sometimes only resolves to\n // a ZIP centroid.\n // A stored coordinate is only trusted when it is near the account's own\n // closest warehouse; a few Miami accounts carry New Jersey coordinates, and\n // routing to those would be worse than geocoding the address.\n let lat = null;\n let lon = null;\n let precision: \"exact\" | \"approx\" = \"exact\";\n const storedLat = Number(account.Lattitude__c);\n const storedLon = Number(account.Longitude__c);\n if (\n Number.isFinite(storedLat) && Number.isFinite(storedLon) &&\n !(storedLat === 0 && storedLon === 0) &&\n Math.abs(storedLat) <= 90 && Math.abs(storedLon) <= 180\n ) {\n const wLat = Number(account.Closest_Warehouse_Latitude__c);\n const wLon = Number(account.Closest_Warehouse_Longitude__c);\n const plausible = !Number.isFinite(wLat) || !Number.isFinite(wLon) ||\n Math.hypot((storedLat - wLat) * 111, (storedLon - wLon) * 98) <= MAX_KM_FROM_WAREHOUSE;\n if (plausible) {\n lat = storedLat;\n lon = storedLon;\n fromStored++;\n } else {\n strayCoords.push(\n `${opp.Name}: stored ${storedLat.toFixed(4)},${storedLon.toFixed(4)} is far from its ` +\n `warehouse - geocoding the address instead`,\n );\n }\n }\n if (lat == null || lon == null) {\n lat = account.Client_Location__Latitude__s;\n lon = account.Client_Location__Longitude__s;\n }\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 // \"Monday;Wednesday;Friday\" -> [\"Monday\",\"Wednesday\",\"Friday\"]. Blank means\n // no restriction; an empty list would mean \"no day works\", which is not\n // what a blank field is saying.\n const allowed = String(account.Allowed_days__c ?? \"\")\n .split(\";\")\n .map((d: string) => d.trim())\n .filter(Boolean);\n\n // A Route Date is dispatch committing this stop to a day: either someone\n // set it in Salesforce by hand, or the map's \"Set route date in Salesforce\"\n // button wrote it when a plan was sent out. Either way the decision has\n // been made and left the building, so a rebuild must not move it.\n //\n // This used to require SMS_Sent__c as well, on the reasoning that only a\n // text is a real promise. That was the wrong test: it meant committing a\n // week to Salesforce did nothing, and the next Reoptimize happily re-dated\n // six DC stops that were already committed for the next morning. Committing\n // is the commitment. A text is how the customer finds out, not what makes\n // it true - see smsSent below, which is still tracked, just not for this.\n //\n // Sentinel dates are \"removed from route\" and promise nothing.\n const routed = opp.Route_Date__c && opp.Route_Date__c >= ROUTE_DATE_FLOOR\n ? opp.Route_Date__c\n : null;\n\n // A promised date in the past is a promise already broken - the stop is\n // still Delivery Due, so nobody came. Holding it to that date helps no one:\n // there is no such day left to schedule, so honourDayRules could not place\n // it, gave up, and parked it on whatever day the packer happened to pick,\n // where every repair pass then refused to touch it because it was pinned.\n // Two Florida stops sat like that for a month, texted for 14 August and 15\n // July, reported as unplaceable on every single rebuild.\n //\n // So the pin expires with its date. What the customer is owed at that point\n // is a fresh text, not a frozen slot.\n const promiseExpired = routed != null && routed < today;\n\n const stop: Stop = {\n opportunityId: opp.Id,\n opportunityName: opp.Name,\n accountName: account.Name,\n address: account.Full_Address__c,\n allowedDays: allowed.length > 0 ? allowed : null,\n pinnedDate: promiseExpired ? null : routed,\n // Not a pin any more, but still worth carrying: it is the difference\n // between \"the customer is expecting us\" and \"we have decided but not\n // told them\", which changes what a driver's notes should say and what a\n // dispatcher should do before moving the stop.\n smsSent: opp.SMS_Sent__c === true,\n // Kept so the build can say which promises have lapsed and need re-texting.\n lapsedPromise: opp.SMS_Sent__c === true && promiseExpired ? routed : null,\n closeDate: opp.CloseDate,\n // CreatedDate comes back as a full ISO timestamp; the date is the useful\n // part and the time of day tells nobody anything.\n createdDate: opp.CreatedDate ? String(opp.CreatedDate).slice(0, 10) : null,\n units: orderUnits(opp),\n bottles: Number(account.Amount_of_Bottles__c) || 0,\n springBottles: Number(account.Spring_Bottle_Amount__c) || 0,\n waterType: account.Water_Type__c ?? null,\n apt: account.Apt_1__c ?? null,\n phone: account.Phone ?? null,\n email: account.Email_Address__c ?? opp.Email_Address__c ?? null,\n orderSummary: opp.Order_Summary__c ?? null,\n allowedDaysRaw: account.Allowed_days__c ?? null,\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(\n `Placed ${stops.length}/${routableOpps.length} stops ` +\n `(${fromStored} from stored account coordinates, ` +\n `${stops.length - fromStored} geocoded, ${geocodeFailures.length} failures)`,\n );\n\n // Say so when the unit count had to come from the old field. A quiet drift\n // back to line quantities would shrink every pallet order to a couple of\n // units and nothing else about the build would look wrong.\n const fellBack = routableOpps.filter(usedUnitsFallback);\n if (fellBack.length > 0) {\n console.log(\n `${fellBack.length} order(s) with no usable Total_Units_for_Order__c - ` +\n `counted from Total_Amount_of_Products__c instead`,\n );\n }\n if (strayCoords.length > 0) {\n console.log(`${strayCoords.length} stored coordinate(s) ignored as out of region:`);\n for (const s of strayCoords.slice(0, 8)) console.log(` ${s}`);\n if (strayCoords.length > 8) console.log(` ... and ${strayCoords.length - 8} more`);\n }\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 //\n // Hired weekday capacity counts towards the test. This used to be owned\n // trucks only, on the reasoning that a sixth day on a van you already own\n // beats paying for a rental - but that is not the trade dispatch actually\n // wants. A Saturday costs a driver a weekend whether the van is owned or not,\n // so the order of preference is: fill the weekdays, then hire a van on a\n // weekday, and only then work Saturday. DC next week is the case in point -\n // 589 units, which overflows the owned van's Mon-Fri 500 but sits well inside\n // 600 once one hired weekday is counted, so nobody needs to work the weekend.\n const owned = region.ownedVehicles ?? region.vehicleCapacities.length;\n const fleetPerDay = region.vehicleCapacities.slice(0, owned).reduce((a, c) => a + c, 0);\n const hiredPerDay = region.vehicleCapacities.slice(owned).reduce((a, c) => a + c, 0);\n const totalUnits = stops.reduce((a, s) => a + s.units, 0);\n const weekdayCapacity = days.length * (fleetPerDay + hiredPerDay);\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 + hiredPerDay)})`,\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 same slots, but with later days deliberately under-filled so the back\n // half of the week has room for call-ins and new customers that arrive after\n // the plan is built. Monday runs at full capacity, the last day at\n // (1 - frontLoadFactor) of it, straight-line in between.\n const taperedCapacities = (taper: number) =>\n slotCapacities.map((base, slot) => {\n const dayIdx = slot % days.length;\n if (days.length <= 1 || taper <= 0) return base;\n return Math.max(1, Math.round(base * (1 - taper * (dayIdx / (days.length - 1)))));\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 vehicleNames: region.vehicleNames,\n optimized: false,\n today,\n windowStart,\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 closeDate: s.closeDate,\n createdDate: s.createdDate,\n bottles: s.bottles,\n springBottles: s.springBottles,\n waterType: s.waterType,\n apt: s.apt,\n phone: s.phone,\n email: s.email,\n orderSummary: s.orderSummary,\n allowedDaysRaw: s.allowedDaysRaw,\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 removed: removedStops,\n geocodeFailures,\n } satisfies MapSnapshot);\n return {\n today,\n windowStart,\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 // Per region: the pair cache is only ever hit by stops in the same region,\n // and keeping them separate stops one big region's blob slowing the others.\n const distances = await getDistanceMatrix(depot, stops, region.key);\n\n // What the packer plans against. Identical to `distances` unless the region\n // declares a barrier, in which case legs that cross it are made to look longer\n // than they are, so zones and visit orders both prefer to stay on one side.\n //\n // `distances` itself stays true, and is what the kilometre total is measured\n // with - the plan should be reported by how far it actually drives, not by the\n // penalties used to shape it.\n //\n // A barrier with a zero penalty is off, not free: skip it rather than copy a\n // 197x197 matrix and add nothing to it.\n let planDistances = distances;\n const zoning = region.territories\n ? {\n name: region.territories.spec.name,\n penaltyKm: region.territories.crossPenaltyKm,\n zoneOf: (s: { lat: number; lon: number }) =>\n territoryOf(region.territories!.spec, s.lat, s.lon),\n labelOf: (z: number) => region.territories!.spec.labels[z] ?? `zone ${z}`,\n }\n : region.barrier\n ? {\n name: region.barrier.name,\n penaltyKm: region.barrier.crossPenaltyKm,\n zoneOf: (s: { lat: number; lon: number }) =>\n barrierSide(region.barrier!.line, s.lat, s.lon),\n labelOf: (z: number) => (z > 0 ? \"east\" : \"west\"),\n }\n : null;\n\n if (zoning && zoning.penaltyKm > 0) {\n const z = penaliseZoneCrossings(distances, stops, zoning.zoneOf, zoning.penaltyKm);\n planDistances = z.matrix;\n const split = [...z.perZone.entries()]\n .sort((a, b) => a[0] - b[0])\n .map(([id, n]) => `${zoning.labelOf(id)} ${n}`)\n .join(\", \");\n console.log(\n `${zoning.name}: ${z.crossingPairs} cross-zone pair(s) penalised ${zoning.penaltyKm}km`,\n );\n console.log(` ${split}`);\n }\n\n // Front-loading is a preference, never a reason to strand a delivery. Start\n // at the configured taper and back off in steps until nothing is left\n // unassigned that a flatter week would have carried.\n const maxStopsPerRoute = region.maxStopsPerRoute ?? DEFAULT_MAX_STOPS_PER_ROUTE;\n\n // Banding needs enough trucks to cut thin slices; on a short week it hands\n // each truck most of the region instead. Fall back to the radial sweep there.\n // Clustering is exempt: it finds however many clumps the map actually has and\n // does not need a wide fleet to cut thin slices.\n const wantedOrientation = region.routeOrientation ?? \"radial\";\n const isBanded = wantedOrientation === \"east-west\" || wantedOrientation === \"north-south\";\n const orientation: Orientation = isBanded && maxSlots < MIN_SLOTS_FOR_BANDING\n ? \"radial\"\n : wantedOrientation;\n if (orientation !== wantedOrientation) {\n console.log(\n `Only ${maxSlots} truck-day(s) this week - using a radial sweep instead of ` +\n `${wantedOrientation}, which needs at least ${MIN_SLOTS_FOR_BANDING} to give compact bands`,\n );\n }\n\n // Packing the week into as few truck-days as it needs, earliest first.\n //\n // The order truck-days are taken up in, when packing into as few as possible.\n //\n // Owned trucks come first, each working right through the week, before a\n // hired one is touched at all - a rental costs money per day, so a sixth day\n // on the van you own beats a second van on Monday. Within a tier the earliest\n // day wins, which is what makes the week front-loaded.\n let slotPriority: number[] | undefined;\n // The whole priority order, not just the slice taken first time. Kept in\n // scope so the escalation further down can add truck-days one at a time and\n // still take the cheapest one left.\n let preferredSlots: number[] = [];\n if (region.minimiseRoutes && orientation === \"zoned\") {\n // Cheapest truck-day first, and a Saturday is not cheap. The order is:\n // weekday on a van we own, then weekday on a hired one, then Saturday\n // owned, then Saturday hired. Hiring on a Tuesday beats working the\n // weekend even though the van costs money and the sixth day does not -\n // the driver's Saturday is the scarcer thing.\n const satIndex = days.findIndex((d) => dayOfWeek(d.date) === 6);\n const rankOf = (slot: number) => {\n const hired = Math.floor(slot / days.length) >= owned ? 1 : 0;\n const weekend = satIndex >= 0 && (slot % days.length) === satIndex ? 2 : 0;\n return weekend + hired;\n };\n preferredSlots = Array.from({ length: maxSlots }, (_, s) => s).sort((a, b) =>\n rankOf(a) - rankOf(b) ||\n Math.floor(a / days.length) - Math.floor(b / days.length) ||\n (a % days.length) - (b % days.length)\n );\n let need = 0;\n let room = 0;\n while (room < totalUnits && need < preferredSlots.length) {\n room += slotCapacities[preferredSlots[need++]];\n }\n // A truck can run out of stops before it runs out of cube, so the stop cap\n // sets its own floor on how many are needed.\n need = Math.min(\n preferredSlots.length,\n Math.max(need, Math.ceil(stops.length / maxStopsPerRoute)),\n );\n slotPriority = preferredSlots.slice(0, need);\n\n const hired = slotPriority.filter((s) => Math.floor(s / days.length) >= owned).length;\n console.log(\n `Packing ${totalUnits} units into ${need} of ${maxSlots} truck-day(s): ` +\n slotPriority.map((s) => {\n const { day, vehicle } = slotLabel(s, days);\n return `${day.slice(0, 3)} ${region.vehicleNames?.[vehicle - 1] ?? \"v\" + vehicle}`;\n }).join(\", \"),\n );\n if (hired > 0) {\n console.log(\n ` ${hired} of those is a hired truck-day - the ${owned} owned truck(s) cannot carry ` +\n `${totalUnits} units across ${days.length} day(s)`,\n );\n }\n }\n\n // With minimiseRoutes the front-loading comes from which days are used at\n // all, so the taper is off - capping the later days as well would only make\n // the trucks that do go out emptier.\n let taper = region.minimiseRoutes ? 0 : (region.frontLoadFactor ?? DEFAULT_FRONT_LOAD);\n let built = buildRoutes(\n stops,\n planDistances,\n maxSlots,\n depot,\n taperedCapacities(taper),\n orientation,\n maxStopsPerRoute,\n slotPriority,\n );\n // Packed too tight to fit - add truck-days one at a time until it does.\n //\n // The count above comes from capacity arithmetic alone: 589 units over\n // 100-unit vans is six van-days. Arithmetic does not know about allowed days,\n // or that a zone packed to 99% stops being a sensible shape, so the first\n // attempt often leaves a stop or two homeless.\n //\n // This used to answer that by dropping the limit entirely and re-solving with\n // the whole fleet open. That is not minimising anything - it just hands the\n // packer twelve slots and takes whatever it does with them. On a DC week that\n // needed eight truck-days it produced nine, and seven and eight were never\n // tried at all.\n //\n // So step up instead: six, then seven, then eight, stopping at the first that\n // places every stop. The order truck-days are added in is the priority list,\n // so the extra one is always the cheapest still available - another day on a\n // van we own before any day on a hired one.\n if (built.unassigned.length > 0 && slotPriority && slotPriority.length < maxSlots) {\n const startedWith = slotPriority.length;\n for (let n = startedWith + 1; n <= maxSlots && built.unassigned.length > 0; n++) {\n const attempt = buildRoutes(\n stops,\n planDistances,\n maxSlots,\n depot,\n taperedCapacities(taper),\n orientation,\n maxStopsPerRoute,\n n < maxSlots ? preferredSlots.slice(0, n) : undefined,\n );\n // Keep the better attempt even if it still falls short, so a run that\n // ends without a perfect fit is at least the least-bad one tried.\n if (attempt.unassigned.length <= built.unassigned.length) {\n built = attempt;\n slotPriority = n < maxSlots ? preferredSlots.slice(0, n) : undefined;\n }\n }\n console.log(\n `${startedWith} truck-day(s) left stops homeless - settled on ` +\n `${slotPriority ? slotPriority.length : maxSlots}`,\n );\n }\n while (built.unassigned.length > 0 && taper > 0) {\n const relaxed = Math.max(0, Number((taper - 0.1).toFixed(2)));\n const attempt = buildRoutes(\n stops,\n planDistances,\n maxSlots,\n depot,\n taperedCapacities(relaxed),\n orientation,\n maxStopsPerRoute,\n );\n // Keep relaxing even when a step places nothing. Zones need slack: at 99%\n // of fleet capacity every stop has exactly one home and geography loses, so\n // a flatter week is both fuller and tidier. Measured on this Florida week -\n // taper 0.20 gave 28% of stops a neighbour on another truck, taper 0.00\n // gave 15%.\n console.log(\n `Front-load ${taper.toFixed(2)} left ${built.unassigned.length} unassigned; ` +\n `relaxing to ${relaxed.toFixed(2)} -> ${attempt.unassigned.length} unassigned`,\n );\n taper = relaxed;\n built = attempt;\n }\n if (taper > 0) {\n console.log(\n `Front-loaded the week at ${taper.toFixed(2)}: day 1 at full capacity, ` +\n `last day at ${Math.round((1 - taper) * 100)}%`,\n );\n }\n const unassigned = built.unassigned;\n\n // Allowed days and texted commitments, applied to the finished plan.\n const rules = honourDayRules(\n built.slots,\n days,\n taperedCapacities(taper),\n maxStopsPerRoute,\n unassigned,\n slotCapacities,\n );\n if (rules.promoted.length > 0) {\n console.log(`${rules.promoted.length} committed stop(s) given a place on their Route Date:`);\n for (const p of rules.promoted) console.log(` ${p}`);\n }\n if (rules.relieved.length > 0) {\n console.log(\n `${rules.relieved.length} uncommitted stop(s) shifted to keep trucks off the road:`,\n );\n for (const r of rules.relieved.slice(0, 15)) console.log(` ${r}`);\n if (rules.relieved.length > 15) console.log(` ... and ${rules.relieved.length - 15} more`);\n }\n if (rules.moved.length > 0) {\n console.log(`${rules.moved.length} stop(s) moved to honour allowed days / committed Route Dates:`);\n for (const m of rules.moved.slice(0, 12)) console.log(` ${m}`);\n if (rules.moved.length > 12) console.log(` ... and ${rules.moved.length - 12} more`);\n }\n // Not a routing problem - these are re-planned normally now - but somebody\n // was told a date that came and went, so it needs a human and a new text.\n const lapsed = stops.filter((s) => s.lapsedPromise);\n if (lapsed.length > 0) {\n console.log(`${lapsed.length} stop(s) texted for a date that has passed - re-plannable, needs re-texting:`);\n for (const s of lapsed.slice(0, 12)) {\n console.log(` ${s.accountName} (texted for ${s.lapsedPromise})`);\n }\n if (lapsed.length > 12) console.log(` ... and ${lapsed.length - 12} more`);\n }\n\n if (rules.stuck.length > 0) {\n console.log(`WARNING: ${rules.stuck.length} stop(s) could not be placed on an allowed day:`);\n for (const s of rules.stuck.slice(0, 12)) console.log(` ${s}`);\n }\n\n // Every rule has now fired. Before the days are re-optimised internally, give\n // the week one settling pass: each rule above judged its move against a plan\n // that was still moving, so this is the first chance anything has to look at\n // the finished week and spot work stranded on a late day that a nearer,\n // earlier truck should have taken.\n const settled = balanceDays(\n built.slots,\n days,\n taperedCapacities(taper),\n maxStopsPerRoute,\n stops,\n // The repair passes move stops between trucks on distance too, so they get\n // the penalised view as well. Handing them the true matrix would let them\n // quietly undo the barrier the packer just respected.\n planDistances,\n );\n if (settled.length > 0) {\n console.log(`${settled.length} stop(s) pulled onto a nearer, earlier day:`);\n for (const s of settled.slice(0, 15)) console.log(` ${s}`);\n if (settled.length > 15) console.log(` ... and ${settled.length - 15} more`);\n }\n\n // The settling pass can only pull work earlier, so it cannot help a stop\n // stranded on the FIRST day - which is exactly where committed Route Dates\n // pile up. Give those stragglers one chance to move to a nearer day that has\n // room to spare, before the days are re-optimised internally.\n const reseated = reseatStragglers(\n built.slots,\n days,\n taperedCapacities(taper),\n maxStopsPerRoute,\n stops,\n planDistances,\n );\n if (reseated.length > 0) {\n console.log(`${reseated.length} straggler(s) reseated onto a nearer day with room:`);\n for (const s of reseated) console.log(` ${s}`);\n }\n\n // The rules are done moving stops between days. Now put each day's work back\n // in geographic order across its own trucks.\n const rebalanced = rebalanceDays(\n built.slots,\n days,\n stops,\n distances,\n depot,\n slotCapacities,\n maxStopsPerRoute,\n orientation,\n );\n for (const n of rebalanced) console.log(` ${n}`);\n\n const overrides = await loadOverrides(endDate, region);\n const { slots: allSlots, applied } = applyOverrides(built.slots, distances, stops, days, overrides);\n // Consolidating a day onto one truck empties the other; an empty truck is not\n // a route and must not reach the map, the sheet or the load list.\n const slots = allSlots.filter((s) => s.stops.length > 0);\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\n // Texted stops are NOT written into the overrides.\n //\n // They were, briefly, and it did real damage: an override records a date AND\n // a vehicle, so every rebuild dragged texted stops back onto whichever truck\n // they happened to land on last time - undoing the geographic split and, on\n // one Miami build, leaving a 100-unit van loaded to 186.\n //\n // A text promises a DAY, nothing more. The day is already enforced on every\n // build from Salesforce, so nothing needs storing; the truck stays free for\n // the packer to decide. Hand-set locks still record a vehicle, because a\n // dispatcher pinning a stop to a truck means exactly that.\n const datePinned = stops.filter((s) =>\n s.pinnedDate && assignment.get(s.opportunityId)?.date === s.pinnedDate\n );\n if (datePinned.length > 0) {\n const texted = datePinned.filter((s) => s.smsSent).length;\n console.log(\n `${datePinned.length} stop(s) held to their committed Route Date ` +\n `(${texted} of them texted; truck left free for the packer)`,\n );\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 vehicleNames: region.vehicleNames,\n today,\n windowStart,\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 // Padlock shown for a hand-set lock, and for a stop sitting on the\n // Route Date it is committed to - both mean \"this day is not up for\n // grabs\". pinnedDate and smsSent ride along so the map can say which\n // of the two it is looking at rather than guessing.\n locked: overrides.moves[s.opportunityId]?.locked === true ||\n (s.pinnedDate != null && assignment.get(s.opportunityId)?.date === s.pinnedDate),\n pinnedDate: s.pinnedDate,\n smsSent: s.smsSent === true,\n closeDate: s.closeDate,\n createdDate: s.createdDate,\n bottles: s.bottles,\n springBottles: s.springBottles,\n waterType: s.waterType,\n apt: s.apt,\n phone: s.phone,\n email: s.email,\n orderSummary: s.orderSummary,\n allowedDaysRaw: s.allowedDaysRaw,\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 removed: removedStops,\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 windowStart,\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\n// Everything committed to a given Route Date, ready to go to OptimoRoute.\n//\n// Deliberately reads Salesforce rather than the stored plan. The push button\n// fires with nobody looking at a map, possibly days after the last build, so\n// the snapshot could be stale or missing for that date entirely - and the thing\n// dispatch actually committed to is the Route Date on the record. This cannot\n// go stale: whatever Salesforce says is on the truck is what gets sent.\n//\n// Only routes this val owns. Route_Name__c has to match one of the region's\n// vehicleNames, which is both the driver serial in OptimoRoute and the value\n// the map writes when it commits. That filter drops the \"Removed from route:\n// ...\" records, anything with no route name, and every truck belonging to the\n// other markets the Cloud Run bridge looks after.\nexport type CommittedStop = {\n opportunityId: string;\n opportunityName: string;\n accountName: string;\n address: string;\n // null when the stop has no usable truck. It still goes to OptimoRoute, just\n // without a driver assignment - see the note in committedStops.\n routeName: string | null;\n // Salesforce Delivery_Region__c verbatim. Only used to group the push report;\n // an all-regions push spans far more of these than this val plans routes for.\n deliveryRegion: string | null;\n units: number;\n products: Product[];\n bottles: number;\n springBottles: number;\n waterType: string | null;\n apt: string | null;\n phone: string | null;\n email: string | null;\n orderSummary: string | null;\n allowedDaysRaw: string | null;\n smsSent: boolean;\n lat: number;\n lon: number;\n precision: \"exact\" | \"approx\";\n};\n\n// A stop that did not go up as planned, and why. `region` is carried so the\n// push report can group these the same way it groups the stops themselves.\nexport type Aside = {\n account: string;\n routeName: string | null;\n why: string;\n region: string | null;\n};\n\n// regionKey null means EVERY region - the Salesforce home-page button is a\n// company-wide \"send this day\" and is not scoped to the three regions this val\n// plans routes for. Without that, 11 of the 13 stops route-dated 2026-08-24 were\n// invisible to it purely because they were Brooklyn/Nassau/North/Midtown rather\n// than DC or Miami.\n//\n// `serials` overrides the truck list to match against. The per-region config\n// only names this val's own vehicles, which is no use for a NY stop on\n// \"NJ Isuzu\", so the caller passes the serials OptimoRoute actually knows.\nexport async function committedStops(\n regionKey: string | null,\n date: string,\n opts?: { serials?: Iterable; routeName?: string },\n): Promise<{\n region: string;\n label: string;\n date: string;\n stops: CommittedStop[];\n skipped: Aside[];\n unassigned: Aside[];\n // Route-dated that day but left out because `opts.routeName` asked for one\n // truck. Kept apart from `skipped`, which is for stops that were meant to go\n // and could not - these were never in scope to begin with.\n excluded: Aside[];\n // Every truck name committed to this date, exactly as Salesforce holds it,\n // including ones matching no OptimoRoute driver - which is what a typo looks\n // like from here. Read before any filtering, so a routeName that matches\n // nothing can still say what does exist.\n allRoutes: string[];\n // Everything route-dated that day and still Delivery Due, before any of the\n // checks below. The push report reconciles against this so a stop can never\n // go missing without being named.\n considered: number;\n}> {\n const region = regionKey ? getRegion(regionKey) : null;\n // Matched case-insensitively, but sent under the CONFIGURED spelling.\n //\n // Route Name is typed by hand often enough that the case drifts - a Miami\n // record was carrying \"miami 2\" against a configured \"Miami 2\". An exact\n // match silently dropped that stop from the push; sending it verbatim would\n // instead have been rejected by OptimoRoute, whose driver serials are exact.\n // Normalising fixes the typo on the way out rather than failing either way.\n //\n // Whitespace is collapsed as well as case, because the same drift produced\n // \"freight liner- 2\" against \"Freight Liner-2\".\n const norm = (s: string) => s.trim().toLowerCase().replace(/\\s+/g, \" \");\n const names = opts?.serials ??\n (region\n ? (region.vehicleNames ?? [])\n : Object.values(REGIONS).flatMap((r) => r.vehicleNames ?? []));\n const drivers = new Map([...names].map((n) => [norm(n), n]));\n\n const opps = await sfQuery(\n `SELECT Id, Name, Route_Date__c, Route_Name__c, StageName, SMS_Sent__c, Delivery_Region__c,\n Total_Amount_of_Products__c, Total_Units_for_Order__c, AccountId, Order_Summary__c\n FROM Opportunity\n WHERE ${\n region\n ? `Delivery_Region__c IN (${region.salesforceRegions.map((r) => `'${r}'`).join(\",\")}) AND `\n : \"\"\n }Route_Date__c = ${date}\n AND StageName = 'Delivery Due'`.replace(/\\s+/g, \" \").trim(),\n );\n\n const skipped: Aside[] = [];\n const unassigned: Aside[] = [];\n\n // Route Name is the OptimoRoute driver serial, and a stop without a usable\n // one used to be dropped here entirely. That was wrong: a route-dated order\n // with no truck is still a real delivery someone scheduled, and dropping it\n // meant it silently never reached a driver - on 2026-08-24 that was 11 of the\n // 12 route-dated stops, including a 45-unit and a 30-unit order. It now goes\n // to OptimoRoute unassigned instead, so a dispatcher can assign it there.\n //\n // Only called for stops that survive the address and geocode checks below, so\n // the count reflects what was actually sent rather than everything looked at.\n const serialFor = (\n o: { Name: string; Route_Name__c?: string | null; Delivery_Region__c?: string | null },\n ): string | null => {\n const region = o.Delivery_Region__c ?? null;\n if (!o.Route_Name__c) {\n unassigned.push({ account: o.Name, routeName: null, why: \"no Route Name\", region });\n return null;\n }\n const serial = drivers.get(norm(String(o.Route_Name__c)));\n if (!serial) {\n // Passing the name through verbatim would be rejected - OptimoRoute's\n // driver serials are exact - so an unrecognised truck also goes\n // unassigned rather than failing the whole batch.\n unassigned.push({\n account: o.Name,\n routeName: o.Route_Name__c,\n why: \"no OptimoRoute driver by that name\",\n region,\n });\n return null;\n }\n return serial;\n };\n\n // Every truck committed to this date, before the filter below touches it.\n const allRoutes = [\n ...new Set(opps.map((o) => o.Route_Name__c).filter((n): n is string => !!n)),\n ].sort();\n\n // Naming a truck narrows the work here rather than at the end.\n //\n // This used to hand every stop route-dated that day back to the caller and let\n // it filter the built results, so a 20-stop DC Van push still loaded all 601\n // orders and all 599 accounts committed to 2026-08-27 - and died on the account\n // query before the filter that would have discarded 581 of them ever ran.\n // Matching on the opportunity rows first makes the expensive half of this\n // function proportional to what is actually being sent.\n //\n // Compared with the same norm() the driver list uses, and deliberately NOT in\n // SOQL: a record carrying \"dc van\" or \"DC Van\" has to match, and SOQL equality\n // would not collapse that whitespace. The opportunity query above is a fixed\n // length whatever the day looks like, so there is nothing to win by pushing the\n // filter into it.\n const wanted = opts?.routeName ? norm(opts.routeName) : null;\n const excluded: Aside[] = [];\n const mine = wanted === null ? opps : opps.filter((o) => {\n if (o.Route_Name__c && norm(String(o.Route_Name__c)) === wanted) return true;\n excluded.push({\n account: o.Name,\n routeName: o.Route_Name__c ?? null,\n region: o.Delivery_Region__c ?? null,\n why: o.Route_Name__c\n ? `on ${o.Route_Name__c}, not ${opts!.routeName}`\n : `no Route Name, and this push was limited to ${opts!.routeName}`,\n });\n return false;\n });\n\n if (mine.length === 0) {\n return {\n region: region?.key ?? \"all\",\n label: region?.label ?? \"All regions\",\n date,\n stops: [],\n skipped,\n unassigned,\n excluded,\n allRoutes,\n considered: opps.length,\n };\n }\n\n const accounts = await sfQueryByIds(\n [...new Set(mine.map((o) => o.AccountId))],\n (ids) =>\n `SELECT Id, Name, Full_Address__c, Client_Location__Latitude__s,\n Client_Location__Longitude__s, Allowed_days__c, Amount_of_Bottles__c,\n Spring_Bottle_Amount__c, Water_Type__c, Apt_1__c, Phone, Email_Address__c\n FROM Account WHERE Id IN (${ids})`.replace(/\\s+/g, \" \").trim(),\n );\n const accountsById = new Map(accounts.map((a) => [a.Id, a]));\n const geoCache = await loadGeoCache();\n\n const built = await Promise.all(mine.map(async (opp) => {\n const account = accountsById.get(opp.AccountId);\n if (!account?.Full_Address__c) {\n skipped.push({\n account: opp.Name,\n routeName: opp.Route_Name__c,\n why: \"no address on account\",\n region: opp.Delivery_Region__c ?? null,\n });\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 skipped.push({\n account: opp.Name,\n routeName: opp.Route_Name__c,\n why: \"geocode failed\",\n region: opp.Delivery_Region__c ?? null,\n });\n return null;\n }\n lat = geo.lat;\n lon = geo.lon;\n precision = geo.precision;\n }\n return {\n opportunityId: opp.Id,\n opportunityName: opp.Name,\n accountName: account.Name,\n address: account.Full_Address__c,\n routeName: serialFor(opp),\n deliveryRegion: opp.Delivery_Region__c ?? null,\n units: orderUnits(opp),\n products: parseOrderSummary(opp.Order_Summary__c),\n bottles: Number(account.Amount_of_Bottles__c) || 0,\n springBottles: Number(account.Spring_Bottle_Amount__c) || 0,\n waterType: account.Water_Type__c ?? null,\n apt: account.Apt_1__c ?? null,\n phone: account.Phone ?? null,\n email: opp.Email_Address__c ?? account.Email_Address__c ?? null,\n orderSummary: opp.Order_Summary__c ?? null,\n allowedDaysRaw: account.Allowed_days__c ?? null,\n smsSent: opp.SMS_Sent__c === true,\n lat: Number(lat),\n lon: Number(lon),\n precision,\n } satisfies CommittedStop;\n }));\n\n return {\n region: region?.key ?? \"all\",\n label: region?.label ?? \"All regions\",\n date,\n stops: built.filter((s): s is CommittedStop => s !== null),\n skipped,\n unassigned,\n excluded,\n allRoutes,\n // Everything that day, not just the truck asked for: sent + unassigned +\n // skipped + excluded has to add back up to this, or a stop went missing\n // without anyone saying so.\n considered: opps.length,\n };\n}\n\nexport type ServiceVisit = {\n id: string;\n name: string;\n accountName: string;\n address: string;\n serviceType: string | null;\n technician: string | null;\n dueDate: string;\n // Signed: positive is days late, negative is days still to run.\n daysOverdue: number;\n phone: string | null;\n lat: number;\n lon: number;\n precision: \"exact\" | \"approx\";\n};\n\n// Equipment service visits that are past their due date and still sitting at\n// Due - filter changes and cooler cleanings nobody has been out to yet.\n//\n// Deliberately NOT a REGIONS entry. A region here means a route plan: trucks,\n// capacities, a weekly snapshot, a morning cron. This has none of those, it is\n// a plot of where the overdue work is, and putting it in REGIONS would feed it\n// to the packer and the crons where it means nothing.\n//\n// Defaults to 60 days behind and 30 ahead: far enough back to catch anything\n// that has slipped, far enough forward to plan the next round, and short enough\n// that everything on it is still actionable.\nexport async function serviceVisits(daysBack = 60, daysAhead = 30): Promise<{\n date: string;\n from: string;\n to: string;\n daysBack: number;\n daysAhead: number;\n visits: ServiceVisit[];\n skipped: { account: string; why: string }[];\n}> {\n const today = todayEastern();\n // A window straddling today rather than one behind it. A visit is created\n // with a future due date and normally closed before it lapses, so a\n // backwards-only window is empty whenever the service schedule is healthy -\n // a map that shows nothing on a good day is a map nobody opens. Looking\n // forward as well turns it into the round-planning view it wants to be,\n // while anything already late still shows up and reads as late.\n const from = addDays(today, -Math.abs(daysBack));\n const to = addDays(today, Math.abs(daysAhead));\n\n // Installed_Equipment__r and Equipment_Service_Schedule__r are deliberately\n // not traversed: the API user was granted this object only, and reaching\n // through a lookup it cannot read fails the whole query.\n const rows = await sfQuery(\n `SELECT Id, Name, Service_Date_Due__c, Service_Type__c, Technician_Name__c,\n Account__r.Name, Account__r.Full_Address__c, Account__r.Phone,\n Account__r.Client_Location__Latitude__s, Account__r.Client_Location__Longitude__s\n FROM Equipment_Service_Visit__c\n WHERE Status__c = 'Due'\n AND Service_Date_Due__c >= ${from}\n AND Service_Date_Due__c <= ${to}\n ORDER BY Service_Date_Due__c`.replace(/\\s+/g, \" \").trim(),\n );\n\n const skipped: { account: string; why: string }[] = [];\n const geoCache = await loadGeoCache();\n const days = (iso: string) =>\n Math.round((parseDate(today).getTime() - parseDate(iso).getTime()) / 86400000);\n\n const built = await Promise.all(rows.map(async (v: any) => {\n const account = v.Account__r;\n if (!account?.Full_Address__c) {\n skipped.push({ account: account?.Name ?? v.Name, why: \"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 skipped.push({ account: account.Name, why: \"geocode failed\" });\n return null;\n }\n lat = geo.lat;\n lon = geo.lon;\n precision = geo.precision;\n }\n return {\n id: v.Id,\n name: v.Name,\n accountName: account.Name,\n address: account.Full_Address__c,\n serviceType: v.Service_Type__c ?? null,\n technician: v.Technician_Name__c ?? null,\n dueDate: v.Service_Date_Due__c,\n daysOverdue: days(v.Service_Date_Due__c),\n phone: account.Phone ?? null,\n lat: Number(lat),\n lon: Number(lon),\n precision,\n } satisfies ServiceVisit;\n }));\n\n return {\n date: today,\n from,\n to,\n daysBack: Math.abs(daysBack),\n daysAhead: Math.abs(daysAhead),\n visits: built.filter((v): v is ServiceVisit => v !== null),\n skipped,\n };\n}\n\n// Tomorrow, Eastern. The push button's default: dispatch loads tomorrow's\n// trucks at the end of today.\nexport const tomorrowEastern = () => addDays(todayEastern(), 1);\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, Total_Units_for_Order__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}`\n .replace(/\\s+/g, \" \").trim(),\n );\n\n // Route Date only. This screen is \"what is on a truck today\", and a Close\n // Date of today does not put anything on a truck - it is an order due today\n // that nobody has scheduled yet, which belongs on the planning map where it\n // can still be routed. Including it here showed it as though it were out for\n // delivery and simultaneously hid it from the plan.\n //\n // The floor guards the 2000/2001 sentinels, which mean \"pulled off a route\".\n const ROUTE_DATE_FLOOR = \"2020-01-01\";\n const due = opps.filter((o) => o.Route_Date__c && o.Route_Date__c >= ROUTE_DATE_FLOOR);\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 accounts = await sfQueryByIds(\n [...new Set(due.map((o) => o.AccountId))],\n (ids) =>\n `SELECT Id, Name, Full_Address__c, Client_Location__Latitude__s, Client_Location__Longitude__s\n FROM Account WHERE Id IN (${ids})`.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: orderUnits(opp),\n products: parseOrderSummary(opp.Order_Summary__c),\n // Empty, not a stand-in label. \"No route name\" was being used as one and\n // it ended up as a heading on the Today screen, reading like the name of\n // a truck that does not exist.\n routeName: opp.Route_Name__c ?? \"\",\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 // A stop with no Route_Name__c was never put on a truck, so whatever its date\n // says it is not part of today's road work. Kept apart from the allow-list\n // count because the two mean different things to whoever is reading: one is\n // \"that is somebody else's route\", the other is \"that is on nobody's route\".\n const named = all.filter((s) => s.routeName.trim() !== \"\");\n const unrouted = all.length - named.length;\n const stops = allow\n ? named.filter((s) => allow.some((r) => r.toLowerCase() === s.routeName.trim().toLowerCase()))\n : named;\n const hidden = named.length - stops.length;\n const hiddenRoutes = [...new Set(named.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 (unrouted ? `; ${unrouted} dated today with no route name` : \"\"),\n );\n return {\n region: region.key,\n label: region.label,\n date: today,\n stops,\n hidden,\n hiddenRoutes,\n unrouted,\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",[186],"81389074-1c4b-4ab0-8282-d953917681ab","_proxy.ts","d5e568d4-971d-11f1-8e97-1607ee4eb77e",518,["D",1786628982355],"// TEMPORARY read-only probe. Safe to delete.\n// What does the Salesforce proxy accept besides ?q= ? Nothing here writes.\nconst url = Deno.env.get(\"SALESFORCE_AGENT_URL\")!;\nconst key = Deno.env.get(\"SALESFORCE_AGENT_KEY\")!;\nconsole.log(`proxy: ${url.replace(/https:\\/\\/([^-]+)/, \"https://***\")}`);\n\n// No parameters at all - most of these vals answer with a usage note.\nconst bare = await fetch(`${url}?key=${encodeURIComponent(key)}`);\nconsole.log(`\\nGET (no q) -> ${bare.status}`);\nconsole.log((await bare.text()).slice(0, 600));\n\n// Some proxies document themselves on OPTIONS or a /help path.\nfor (const probe of [\"&help=1\", \"&action=help\"]) {\n const r = await fetch(`${url}?key=${encodeURIComponent(key)}${probe}`);\n console.log(`\\nGET ${probe} -> ${r.status}`);\n console.log((await r.text()).slice(0, 400));\n}\n\n// Does it take a POST? Send a deliberately invalid body so nothing can be\n// written even if it does - we only want to see the shape of the complaint.\nconst post = await fetch(`${url}?key=${encodeURIComponent(key)}`, {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ probe: \"capability-check-only\" }),\n});\nconsole.log(`\\nPOST {} -> ${post.status}`);\nconsole.log((await post.text()).slice(0, 600));\n",[193],"pulls","branches","forks","envVars",{"_206":208,"_209":62},"connections",{"_206":62,"_207":62},"count","unfilledCount",10,"emptyCount",{"_42":192,"_32":193,"_30":194,"_137":-5,"_40":41,"_39":195,"_139":25,"_37":240,"_13":62,"_33":151,"_35":197,"_24":48,"_143":-5,"_219":241,"_27":242},{"_42":185,"_32":186,"_30":57,"_137":-5,"_40":41,"_39":187,"_139":25,"_37":237,"_13":189,"_33":151,"_35":190,"_24":48,"_143":-5,"_219":238,"_27":239},{"_42":176,"_32":177,"_30":178,"_137":-5,"_40":41,"_39":179,"_139":25,"_37":234,"_13":181,"_33":34,"_35":182,"_24":48,"_143":183,"_219":235,"_27":236},{"_42":169,"_32":170,"_30":171,"_137":-5,"_40":41,"_39":172,"_139":25,"_37":231,"_13":159,"_33":141,"_35":174,"_24":48,"_143":-5,"_219":232,"_27":233},{"_42":162,"_32":163,"_30":164,"_137":-5,"_40":41,"_39":165,"_139":25,"_37":228,"_13":159,"_33":141,"_35":167,"_24":48,"_143":-5,"_219":229,"_27":230},{"_42":154,"_32":155,"_30":156,"_137":-5,"_40":41,"_39":157,"_139":25,"_37":225,"_13":159,"_33":141,"_35":160,"_24":48,"_143":-5,"_219":226,"_27":227},{"_42":145,"_32":146,"_30":147,"_137":-5,"_40":41,"_39":148,"_139":25,"_37":222,"_13":150,"_33":151,"_35":152,"_24":48,"_143":-5,"_219":223,"_27":224},{"_42":134,"_32":135,"_30":136,"_137":-5,"_40":41,"_39":138,"_139":25,"_37":218,"_13":62,"_33":141,"_35":142,"_24":48,"_143":-5,"_219":220,"_27":221},["D",1786569644611],"children",[],[135],["D",1787770080497],[],[146],["D",1786717657430],[],[155],["D",1786717661780],[],[163],["D",1786717653194],[],[170],["D",1788201212666],[],[177],["D",1789068165230],[],[186],["D",1786628982355],[],[193],["D",1785443670415],{"_95":94,"_103":99},{"_95":94,"_96":97,"_98":99,"_18":55,"_100":101,"_102":48},{"_13":14,"_32":85,"_42":41,"_86":43,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":44,"_93":94},"ctx",{"_78":-5,"_123":280,"_281":282,"_283":284,"_285":48,"_286":48,"_287":48,"_288":25,"_289":-5,"_290":-7,"_291":-7,"_292":62,"_293":48,"_294":48,"_295":-5,"_296":48,"_297":48,"_298":48,"_299":-5},"ENV",{"_251":252,"_253":254,"_255":85,"_256":257,"_258":259,"_260":261,"_262":263,"_264":265,"_266":267,"_268":269,"_270":271,"_272":273,"_274":275,"_276":277,"_278":279},"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 · v1437