[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_352":353,"_354":355},"routes/_app.x.$handle.$name",{"_91":90,"_31":43,"_99":81,"_26":100,"_13":14,"_101":-5,"_11":102,"_103":104,"_105":106,"_107":108,"_109":110,"_20":-5,"_111":-5,"_112":-5,"_113":24,"_114":115,"_116":117,"_118":-5,"_119":-5,"_120":121},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_41":42,"_31":43,"_36":44,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":-5,"_51":-5,"_18":52,"_53":-5,"_54":24,"_55":-5,"_56":-5,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":-5,"_64":-5,"_65":66,"_67":66,"_68":69,"_70":69,"_71":69,"_72":66,"_73":66,"_74":75,"_76":77,"_78":79,"_80":24},"version",158,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":27,"_32":33,"_34":35,"_36":37,"_38":14,"_39":40,"_23":24},"previous","deleted",false,"deletedHere","path","setup-guide.http.tsx","latestPath","fileId","43de7534-0c30-11f1-9ca0-42dde27851f2","name","fileType","http","content","/**\n * Setup Guide\n * Serves the setup/launch checklist as a rendered HTML page.\n * For Sahar (developer) and Sarah (professor) reference.\n */\n\nexport default function (): Response {\n const html = `\n\n\n Law Student Tracker - Setup Guide\n \n \n\n\n\n

Getting Sarah's Law Student Tracker Running

Step-by-step guide for going from demo mode to live production.
\nSarah is starting mid-semester, so some steps account for importing existing data.

\n\n

What the system does (30-second version)

Every Monday and Wednesday:

Before class — Sarah gets an email listing which students are on-call
After class — Sarah fills out a quick web form: who she called on, how they did, who was absent
Automatically — The system tracks absences, bad performances, and concerning patterns. If a student crosses a threshold (e.g. 3 absences), Sarah gets an alert email

Everything lives in a Google Sheet that Sarah can view anytime. The system writes to it; Sarah reads from it (and can manually correct it).

\n\n

Before you start

You'll need:

Access to Val.town (Sahar's account)
The Google service account credentials file
Sarah's T&E roster (students with first + last names)
Sarah's L&C roster (students with first + last names)
Sarah's L&C syllabus with on-call assignments (which students on which dates)
The Google Form that students use to self-report absences (you need the spreadsheet ID it writes to)

\n\n

Phase 1: Set up the production Google Sheet

\n\n

1.1 Create the spreadsheet

Create a new Google Spreadsheet (or use existing production one)
Share it with the service account email (from credentials JSON, client_email field) — give it Editor access
Copy the spreadsheet ID from the URL (long string between /d/ and /edit)

\n\n

1.2 Create the T&E Roster tab

Name the tab exactly: T&E-Roster
Add these column headers in row 1:

Fill in student_name (e.g. \"Alice Adams\") and last_name (e.g. \"Adams\")
Leave pool empty (system calculates it)
Set all counter columns to 0

Mid-semester note: If Sarah already has data (e.g. \"Alice has 2 absences\"), enter those counts now. The system tracks from here forward.

\n\n

1.3 Create the L&C Roster tab

Name the tab exactly: L&C-Roster
Same columns as T&E (pool not used for L&C — leave blank)
Fill in student names and any existing counts

\n\n

1.4 Create the L&C Schedule tab

Name the tab exactly: L&C-Schedule
Two columns: date and student_name
Enter each remaining class date and assigned students

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

date	student_name
2026-02-18	Alice Adams
2026-02-18	Bob Brown
2026-02-20	Carol Clark

Use YYYY-MM-DD format. Multiple students per date = multiple rows.

\n\n

1.5 Other tabs (auto-created)

You don't need to create these — the system creates them on first run:

Cold-Call-Log (log) — audit trail
T&E-On-Call-Next (auto) — today's on-call list
Watch-List (auto) — students at risk
Cancelled-Classes (log) — cancelled class dates
Config (auto) — system configuration

\n\n

Phase 2: Configure Val.town environment variables

Go to Val.town → Settings → Environment Variables:

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

Variable	Value	Notes
`GOOGLE_APPLICATION_CREDENTIALS`	Entire JSON content of credentials file	Copy-paste the whole file
`REPORT_API_KEY`	Secret string	Already set; auths the report form
`MAIN_SHEET_ID`	Production spreadsheet ID	From Phase 1
`TE_STUDENT_FORMS_SHEET_ID`	T&E student form spreadsheet ID	Where Google Form writes
`LC_STUDENT_FORMS_SHEET_ID`	L&C student form spreadsheet ID	May be same spreadsheet
`NOTIFICATION_EMAIL`	Any value	Set LAST — enables cron emails

Important: NOTIFICATION_EMAIL doesn't control where emails go (Val.town free tier always emails the account owner). It's an on/off switch. Set = emails on. Unset = emails off.

\n\n

Phase 3: Run the setup endpoints

One-time setup steps. Hit each URL in your browser:

\n\n

3.1 Assign T&E pools

Visit the assign-pools endpoint (add ?dryRun=true first to preview)

This finds the letter that gives the most even 50/50 split and writes mon or wed to each student's pool column.

\n\n

3.2 Add tracking columns (if missing)

Visit the add-roster-columns endpoint (?dryRun=true first, then without)

\n\n

3.3 Verify the connection

Visit the connection test endpoint
Should return JSON showing spreadsheet title, headers, and sample rows
If error: check service account has Editor access to the sheet

\n\n

Phase 4: Configure cron schedules

Set in the Val.town web UI (code doesn't control schedules):

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

Cron file	What it does	Schedule (EST)	UTC Cron
`lc-oncall-notify`	Emails L&C on-call list	8 AM Mon/Wed	`0 13 * * 1,3`
`lc-report-prompt`	Reminds Sarah to report (L&C)	12 PM Mon/Wed	`0 17 * * 1,3`
`oncall-notify`	Emails T&E on-call list	3 PM Mon/Wed	`0 20 * * 1,3`
`te-report-prompt`	Reminds Sarah to report (T&E)	6 PM Mon/Wed	`0 23 * * 1,3`
`weekly-summary`	Friday risk summary	Fri 9 AM	`0 14 * * 5`

To set: Open cron file in Val.town → click schedule icon → enter cron expression. Make sure toggle is ON.

\n\n

Phase 5: Connect the student self-report form

Confirm you have the spreadsheet ID(s) where the form responses go
Make sure the service account has read access to those spreadsheets
Verify the form has these exact questions:

\"Date of class you'll be absent, late-arriving/early-departing, or unprepared\"
\"Will you be absent, unprepared, or late/early?\"
\"Your name\"

If form questions differ: Update constants FORM_COL_DATE, FORM_COL_TYPE, FORM_COL_NAME in utils.tsx, then redeploy with vt push.

\n\n

Phase 6: Test end-to-end

\n\n

6.1 Test on-call selection

Visit T&E selection endpoint — should return 6 students (Mon/Wed only; use ?pool=mon to force)
Visit L&C selection endpoint — should return today's assigned students

\n\n

6.2 Test the report form

Open Sarah's form (link below)
Select a class — on-call list should appear
Submit a test report with a student name
Check Google Sheet — Cold-Call-Log should have a new row, roster counts should update

\n\n

6.3 Test email

Set NOTIFICATION_EMAIL in Val.town env vars
Run a cron manually from Val.town dashboard (or wait for next scheduled run)
Confirm email arrives

\n\n

Phase 7: Hand off to Sarah

\n\n

Sarah's daily workflow

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

When	What happens	What you do
Before class	Email arrives listing on-call students	Note who's on the list
During class	Teach normally	Note performance + absences
After class	Reminder email arrives	Open the form, fill it out (< 2 min)
Friday	Weekly summary email	Review any at-risk students
Anytime	Alert email about a student	Follow the recommended action

\n\n

Filling out the report form

Select the class (T&E or L&C)
On-call students appear at the top for reference
Type student names into the right boxes:\n
- Great — did well when called on
- Normal — adequate
- Poor — struggled or unprepared
- Refused — declined to answer
- Absent — ALL absent students (on-call or not)
\n
Submit. Done.

\n\n

If something goes wrong

Form shows warnings for names it couldn't match — check spelling
\"Need to correct a mistake?\" section at the bottom links to the spreadsheet
Fix wrong counts: Edit the student's row directly in T&E-Roster or L&C-Roster
Remove a bad entry: Delete the row from Cold-Call-Log (log)
Duplicate submissions are safe — system skips them automatically

\n\n

If class is cancelled

Open the form, select the class, mark \"Yes - class was cancelled\", submit. This prevents false alerts.

\n\n

What Sarah should NOT touch

Tabs ending in (auto) — system overwrites these
Tabs ending in (log) — append-only, don't edit unless fixing a mistake
The pool column in T&E-Roster — auto-calculated

\n\n

What Sarah CAN edit freely

Student names in either Roster tab
Counter values (absences, concerning_events, etc.) — these are the source of truth
L&C-Schedule (add/change on-call assignments)
Add or remove students from rosters

\n\n

Troubleshooting

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

Problem	Fix
No emails arriving	Check `NOTIFICATION_EMAIL` is set. Check crons are toggled on with correct schedules.
\"Selection failed\" email	Check Google Sheet is accessible (service account has Editor access)
Wrong students on call	Check pool assignments in T&E-Roster. Run assign-pools to recalculate.
Form shows \"Unauthorized\"	Check `REPORT_API_KEY` in Val.town env vars
Student name not matching	System does fuzzy matching. If still failing, check exact spelling against roster.
Too many emails	Unset `NOTIFICATION_EMAIL` to disable all cron emails instantly.
Student added mid-semester	Add to roster with counters at 0. For T&E, run assign-pools.
Student dropped	Delete row from roster. Cold-Call-Log history stays.

\n\n

Quick reference: All URLs

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

What	URL
Sarah's report form	https://sahar--ab2d2176f16511f0a75a42dde27851f2.web.val.run
T&E on-call selection	https://sahar--78f39bfeef4911f0a9a742dde27851f2.web.val.run
L&C on-call selection	https://sahar--2b3b7afaef5611f0bec642dde27851f2.web.val.run
Connection test	https://sahar--c49a6e2cef4711f0998442dde27851f2.web.val.run
Watch list refresh	https://sahar--b1ff5bc6f08f11f0a81342dde27851f2.web.val.run
This setup guide	(you're here)

\n\n\n`;\n\n return new Response(html, { headers: { \"Content-Type\": \"text/html\" } });\n}\n","createdAt",["D",1771353686159],"branchVersion","branchId","f758b6b8-6eda-11f0-a225-0224a6c84d84","id","f758470a-6eda-11f0-a225-0224a6c84d84","law-student-tracker",["D",1754054718011],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","692ed020-0d91-11f0-a027-a973a7805c27","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","9c7a30de-d50c-484d-8048-00c4d2857bea","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_91":90,"_98":94},"org",{"_91":90,"_92":-5,"_93":94,"_18":52,"_95":96,"_97":24},"branch",{"_13":14,"_31":81,"_41":40,"_82":42,"_83":-5,"_84":-5,"_85":-5,"_86":-5,"_87":-5,"_88":43,"_89":90},"isOwned","main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","sahar","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ydjJ5emk0MWJLd3hWMW1iWHZSOVFGdnBiRjYifQ","tier","pro","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_41":42,"_31":43,"_36":348,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":-5,"_51":-5,"_18":52,"_53":-5,"_54":24,"_55":-5,"_56":-5,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":-5,"_64":-5,"_65":66,"_67":66,"_68":69,"_70":69,"_71":69,"_72":66,"_73":66,"_74":349,"_76":350,"_78":351,"_80":24},"tree",[279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295],"files",[279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295],"counts",{"_269":58,"_270":252,"_271":58,"_272":273,"_274":275},"contents",[122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_41":262,"_31":263,"_29":264,"_142":-5,"_39":40,"_38":265,"_144":117,"_36":266,"_13":214,"_32":20,"_34":267,"_23":24,"_149":-5,"_26":268},{"_41":255,"_31":256,"_29":257,"_142":-5,"_39":40,"_38":258,"_144":117,"_36":259,"_13":252,"_32":33,"_34":260,"_23":24,"_149":-5,"_26":261},{"_41":247,"_31":248,"_29":249,"_142":-5,"_39":40,"_38":250,"_144":117,"_36":251,"_13":252,"_32":33,"_34":253,"_23":24,"_149":-5,"_26":254},{"_41":240,"_31":241,"_29":242,"_142":-5,"_39":40,"_38":243,"_144":117,"_36":244,"_13":146,"_32":33,"_34":245,"_23":24,"_149":-5,"_26":246},{"_41":232,"_31":233,"_29":234,"_142":-5,"_39":40,"_38":235,"_144":117,"_36":236,"_13":237,"_32":147,"_34":238,"_23":24,"_149":-5,"_26":239},{"_41":224,"_31":225,"_29":226,"_142":-5,"_39":40,"_38":227,"_144":117,"_36":228,"_13":229,"_32":33,"_34":230,"_23":24,"_149":-5,"_26":231},{"_41":217,"_31":218,"_29":219,"_142":-5,"_39":40,"_38":220,"_144":117,"_36":221,"_13":165,"_32":147,"_34":222,"_23":24,"_149":-5,"_26":223},{"_41":209,"_31":210,"_29":211,"_142":-5,"_39":40,"_38":212,"_144":117,"_36":213,"_13":214,"_32":33,"_34":215,"_23":24,"_149":-5,"_26":216},{"_41":202,"_31":203,"_29":204,"_142":-5,"_39":40,"_38":205,"_144":117,"_36":206,"_13":165,"_32":147,"_34":207,"_23":24,"_149":-5,"_26":208},{"_41":194,"_31":195,"_29":196,"_142":-5,"_39":40,"_38":197,"_144":117,"_36":198,"_13":199,"_32":33,"_34":200,"_23":24,"_149":-5,"_26":201},{"_41":186,"_31":187,"_29":188,"_142":-5,"_39":40,"_38":189,"_144":117,"_36":190,"_13":191,"_32":33,"_34":192,"_23":24,"_149":-5,"_26":193},{"_41":179,"_31":180,"_29":181,"_142":-5,"_39":40,"_38":182,"_144":117,"_36":183,"_13":58,"_32":33,"_34":184,"_23":24,"_149":-5,"_26":185},{"_41":171,"_31":172,"_29":173,"_142":-5,"_39":40,"_38":174,"_144":117,"_36":175,"_13":176,"_32":33,"_34":177,"_23":24,"_149":-5,"_26":178},{"_41":168,"_31":27,"_29":30,"_142":-5,"_39":40,"_38":14,"_144":117,"_36":169,"_13":58,"_32":33,"_34":35,"_23":24,"_149":-5,"_26":170},{"_41":160,"_31":161,"_29":162,"_142":-5,"_39":40,"_38":163,"_144":117,"_36":164,"_13":165,"_32":147,"_34":166,"_23":24,"_149":-5,"_26":167},{"_41":151,"_31":152,"_29":153,"_142":-5,"_39":40,"_38":154,"_144":117,"_36":155,"_13":156,"_32":157,"_34":158,"_23":24,"_149":-5,"_26":159},{"_41":139,"_31":140,"_29":141,"_142":-5,"_39":40,"_38":143,"_144":117,"_36":145,"_13":146,"_32":147,"_34":148,"_23":24,"_149":-5,"_26":150},"4c90607a-f7a4-11f0-aa79-42dde27851f2","weekly-summary.cron.tsx","b1f82aae-f08f-11f0-952c-42dde27851f2","parentId",137,"latest",["D",1769094547928],2,"interval","/**\n * Weekly Summary Email\n * Runs Friday 9 AM EST (14:00 UTC)\n * Sends summary of students above alert thresholds and their recent activity.\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { email } from \"https://esm.town/v/std/email\";\nimport {\n ABSENCE_CONTACT_DEAN,\n ABSENCE_MONITOR,\n ABSENCE_WARN_STUDENT,\n CONCERNING_CONTACT_DEAN,\n CONCERNING_MONITOR,\n CONCERNING_WARN_STUDENT,\n EMAIL_FOOTER,\n formatDate,\n getAuthClient,\n isConcerningEvent,\n SHEET_ID,\n SHEET_TABS,\n} from \"./utils.tsx\";\n\ntype RiskLevel = \"CONTACT_DEAN\" | \"WARN_STUDENT\" | \"MONITOR\";\n\n/**\n * Determine risk level based on absences and concerning events.\n * Returns null if student is below all thresholds.\n */\nfunction getRiskLevel(absences: number, concerning: number): RiskLevel | null {\n if (absences >= ABSENCE_CONTACT_DEAN || concerning >= CONCERNING_CONTACT_DEAN) return \"CONTACT_DEAN\";\n if (absences >= ABSENCE_WARN_STUDENT || concerning >= CONCERNING_WARN_STUDENT) return \"WARN_STUDENT\";\n if (absences >= ABSENCE_MONITOR || concerning >= CONCERNING_MONITOR) return \"MONITOR\";\n return null;\n}\n\ninterface StudentAtRisk {\n name: string;\n classType: \"T&E\" | \"L&C\";\n absences: number;\n concerningEvents: number;\n recentActivity: string[];\n riskLevel: RiskLevel;\n}\n\ninterface IntegrityMismatch {\n student: string;\n classType: string;\n field: \"absences\" | \"concerning\" | \"cold_calls\";\n rosterCount: number;\n logCount: number;\n}\n\n/**\n * Check data integrity between roster counts and Cold Call Log entries.\n */\nfunction checkDataIntegrity(\n logRows: Array<{ get: (key: string) => string }>,\n rosterRows: Array<{ get: (key: string) => string }>,\n classType: string,\n): IntegrityMismatch[] {\n const mismatches: IntegrityMismatch[] = [];\n\n // Build counts from log\n const logCounts = new Map();\n\n for (const row of logRows) {\n const name = row.get(\"student_name\");\n const result = row.get(\"result\") || \"\";\n const performance = row.get(\"performance\") || \"\";\n\n if (!name) continue;\n\n if (!logCounts.has(name)) {\n logCounts.set(name, { absences: 0, concerning: 0, coldCalls: 0 });\n }\n const counts = logCounts.get(name)!;\n\n // Count cold calls (completed or refused)\n if (result === \"completed\" || result === \"refused\") {\n counts.coldCalls++;\n }\n\n // Count absences (both announced and unannounced)\n if (result === \"absent\" || result === \"absent-announced\") {\n counts.absences++;\n }\n\n // Count concerning events (uses shared logic from utils.tsx)\n if (isConcerningEvent(result as any, performance as any)) {\n counts.concerning++;\n }\n }\n\n // Compare with roster\n for (const row of rosterRows) {\n const name = row.get(\"student_name\");\n if (!name) continue;\n\n const rosterAbsences = parseInt(row.get(\"absences\") || \"0\", 10);\n const rosterConcerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n\n const logData = logCounts.get(name) || { absences: 0, concerning: 0, coldCalls: 0 };\n\n if (rosterAbsences !== logData.absences) {\n mismatches.push({\n student: name,\n classType,\n field: \"absences\",\n rosterCount: rosterAbsences,\n logCount: logData.absences,\n });\n }\n\n if (rosterConcerning !== logData.concerning) {\n mismatches.push({\n student: name,\n classType,\n field: \"concerning\",\n rosterCount: rosterConcerning,\n logCount: logData.concerning,\n });\n }\n\n const rosterColdCalls = parseInt(row.get(\"cold_call_count\") || \"0\", 10);\n if (rosterColdCalls !== logData.coldCalls) {\n mismatches.push({\n student: name,\n classType,\n field: \"cold_calls\",\n rosterCount: rosterColdCalls,\n logCount: logData.coldCalls,\n });\n }\n }\n\n return mismatches;\n}\n\n/**\n * Get recent activity from Cold Call Log for a student.\n * Returns entries from the last 2 weeks.\n */\nfunction getRecentActivity(\n logRows: Array<{ get: (key: string) => string }>,\n studentName: string,\n cutoffDate: Date,\n): string[] {\n const activity: string[] = [];\n\n for (const row of logRows) {\n if (row.get(\"student_name\") !== studentName) continue;\n\n const dateStr = row.get(\"date\");\n if (!dateStr) continue;\n\n const rowDate = new Date(dateStr);\n if (rowDate < cutoffDate) continue;\n\n const result = row.get(\"result\");\n const performance = row.get(\"performance\");\n\n let entry = `${dateStr}: ${result}`;\n if (performance && performance !== \"normal\" && performance !== \"not-on-call\") {\n entry += ` (${performance})`;\n }\n activity.push(entry);\n }\n\n return activity;\n}\n\nexport default async function () {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n const studentsAtRisk: StudentAtRisk[] = [];\n\n // Two weeks ago\n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - 14);\n\n // Load Cold Call Log once for activity lookup\n const logSheet = doc.sheetsByTitle[SHEET_TABS.COLD_CALL_LOG];\n let logRows: Array<{ get: (key: string) => string }> = [];\n if (logSheet) {\n await logSheet.loadHeaderRow();\n logRows = await logSheet.getRows();\n }\n\n // Check T&E Roster\n const teSheet = doc.sheetsByTitle[SHEET_TABS.TE_ROSTER];\n let teRows: Array<{ get: (key: string) => string }> = [];\n if (teSheet) {\n await teSheet.loadHeaderRow();\n teRows = await teSheet.getRows();\n\n for (const row of teRows) {\n const name = row.get(\"student_name\");\n if (!name) continue;\n\n const absences = parseInt(row.get(\"absences\") || \"0\", 10);\n const concerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n\n const riskLevel = getRiskLevel(absences, concerning);\n if (riskLevel) {\n studentsAtRisk.push({\n name,\n classType: \"T&E\",\n absences,\n concerningEvents: concerning,\n recentActivity: getRecentActivity(logRows, name, cutoffDate),\n riskLevel,\n });\n }\n }\n }\n\n // Check L&C Roster\n const lcSheet = doc.sheetsByTitle[SHEET_TABS.LC_ROSTER];\n let lcRows: Array<{ get: (key: string) => string }> = [];\n if (lcSheet) {\n await lcSheet.loadHeaderRow();\n lcRows = await lcSheet.getRows();\n\n for (const row of lcRows) {\n const name = row.get(\"student_name\");\n if (!name) continue;\n\n const absences = parseInt(row.get(\"absences\") || \"0\", 10);\n const concerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n\n const riskLevel = getRiskLevel(absences, concerning);\n if (riskLevel) {\n studentsAtRisk.push({\n name,\n classType: \"L&C\",\n absences,\n concerningEvents: concerning,\n recentActivity: getRecentActivity(logRows, name, cutoffDate),\n riskLevel,\n });\n }\n }\n }\n\n // Run data integrity check\n const integrityMismatches: IntegrityMismatch[] = [\n ...checkDataIntegrity(logRows, teRows, \"T&E\"),\n ...checkDataIntegrity(logRows, lcRows, \"L&C\"),\n ];\n\n // Build email\n let subject: string;\n let bodyText: string;\n\n // Helper to format a student summary\n const formatStudent = (s: StudentAtRisk): string => {\n const reasons: string[] = [];\n if (s.absences > 0) reasons.push(`${s.absences} absence${s.absences > 1 ? \"s\" : \"\"}`);\n if (s.concerningEvents > s.absences) reasons.push(`${s.concerningEvents} concerning`);\n return ` - ${s.name} (${s.classType}): ${reasons.join(\", \")}`;\n };\n\n if (studentsAtRisk.length === 0) {\n subject = \"[LawTracker] Weekly Summary - All Clear\";\n bodyText = `Weekly Summary for ${formatDate(new Date())}\n\nAll students are currently below monitoring thresholds.\n\nThresholds:\n- Monitor: ${ABSENCE_MONITOR}+ absences OR ${CONCERNING_MONITOR}+ concerning\n- Warn: ${ABSENCE_WARN_STUDENT}+ absences OR ${CONCERNING_WARN_STUDENT}+ concerning\n- Dean: ${ABSENCE_CONTACT_DEAN}+ absences OR ${CONCERNING_CONTACT_DEAN}+ concerning`;\n } else {\n // Group by risk level\n const contactDean = studentsAtRisk.filter(s => s.riskLevel === \"CONTACT_DEAN\");\n const warnStudent = studentsAtRisk.filter(s => s.riskLevel === \"WARN_STUDENT\");\n const monitor = studentsAtRisk.filter(s => s.riskLevel === \"MONITOR\");\n\n // Build subject based on highest severity\n if (contactDean.length > 0) {\n subject = `[LawTracker] URGENT - ${contactDean.length} student(s) need dean notification`;\n } else if (warnStudent.length > 0) {\n subject = `[LawTracker] Weekly Summary - ${warnStudent.length} student(s) need warning`;\n } else {\n subject = `[LawTracker] Weekly Summary - ${monitor.length} student(s) to monitor`;\n }\n\n // Build sections\n const sections: string[] = [];\n\n if (contactDean.length > 0) {\n sections.push(`CONTACT DEAN (${contactDean.length}):\\n${contactDean.map(formatStudent).join(\"\\n\")}`);\n }\n if (warnStudent.length > 0) {\n sections.push(`WARN STUDENT (${warnStudent.length}):\\n${warnStudent.map(formatStudent).join(\"\\n\")}`);\n }\n if (monitor.length > 0) {\n sections.push(`MONITOR (${monitor.length}):\\n${monitor.map(formatStudent).join(\"\\n\")}`);\n }\n\n bodyText = `Weekly Summary for ${formatDate(new Date())}\n\n${studentsAtRisk.length} student(s) need attention:\n\n${sections.join(\"\\n\\n\")}\n\n---\nThresholds: Monitor (${ABSENCE_MONITOR}/${CONCERNING_MONITOR}), Warn (${ABSENCE_WARN_STUDENT}/${CONCERNING_WARN_STUDENT}), Dean (${ABSENCE_CONTACT_DEAN}/${CONCERNING_CONTACT_DEAN})`;\n }\n\n // Append data integrity issues if any\n if (integrityMismatches.length > 0) {\n bodyText += `\\n\\n⚠️ DATA INTEGRITY ISSUES DETECTED:\\n\\nThe following counts don't match between roster and Cold Call Log:\\n`;\n for (const m of integrityMismatches) {\n bodyText += `\\n- ${m.student} (${m.classType}): ${m.field} - roster=${m.rosterCount}, log=${m.logCount}`;\n }\n bodyText += `\\n\\nPlease review and reconcile manually, or contact Sahar.`;\n }\n\n await email({\n subject,\n text: bodyText + EMAIL_FOOTER,\n });\n\n console.log(`Sent weekly summary: ${studentsAtRisk.length} students at risk, ${integrityMismatches.length} integrity issues`);\n}\n","label",[140],"289591c4-0c2e-11f1-9217-42dde27851f2","utils.tsx","da859f36-ef65-11f0-8c45-42dde27851f2",156,["D",1771352781351],14,"script","/**\n * Shared utilities for Law Student Tracker\n * Contains name matching, normalization, fuzzy matching, and auth functions.\n */\n\nimport { JWT } from \"npm:google-auth-library\";\n\n// ============================================================================\n// Sheet IDs (two spreadsheets)\n// ============================================================================\n\n/**\n * Main spreadsheet: rosters, cold call log, derived tabs.\n * For production, set MAIN_SHEET_ID env var in Val.town.\n */\nexport const MAIN_SHEET_ID = Deno.env.get(\"MAIN_SHEET_ID\") || \"1Iuoz4e8jYnd787qNMC5x6NMZaWJSuxnWz9zyTyVRRYA\";\n\n/**\n * T&E student self-report form spreadsheet (Google Form writes here).\n * For production, set TE_STUDENT_FORMS_SHEET_ID env var in Val.town.\n */\nexport const TE_STUDENT_FORMS_SHEET_ID = Deno.env.get(\"TE_STUDENT_FORMS_SHEET_ID\") || \"1Iuoz4e8jYnd787qNMC5x6NMZaWJSuxnWz9zyTyVRRYA\";\n\n/**\n * L&C student self-report form spreadsheet (Google Form writes here).\n * For production, set LC_STUDENT_FORMS_SHEET_ID env var in Val.town.\n */\nexport const LC_STUDENT_FORMS_SHEET_ID = Deno.env.get(\"LC_STUDENT_FORMS_SHEET_ID\") || \"1Iuoz4e8jYnd787qNMC5x6NMZaWJSuxnWz9zyTyVRRYA\";\n\n// Legacy alias - will be removed after migration\nexport const STUDENT_FORMS_SHEET_ID = TE_STUDENT_FORMS_SHEET_ID;\n\n// Legacy alias - some code still uses SHEET_ID\nexport const SHEET_ID = MAIN_SHEET_ID;\n\n// ============================================================================\n// Tab Names - Main Spreadsheet\n// ============================================================================\n\n/**\n * Tab names in the MAIN spreadsheet.\n *\n * Three types (suffixes tell Sarah what's safe to edit):\n *\n * SOURCE OF TRUTH (no suffix - Sarah can edit):\n * T&E-Roster, L&C-Roster, L&C-Schedule\n *\n * AUTO-GENERATED (auto) - cleared and rewritten each run:\n * Watch-List (auto), T&E-On-Call-Next (auto), Pending-Review (auto),\n * Config (auto), L&C-On-Call-Next (auto)\n *\n * APPEND-ONLY LOGS (log) - system adds rows, don't edit/delete:\n * Cold-Call-Log (log), T&E-On-Call-History (log), L&C-On-Call-History (log), Cancelled-Classes (log)\n */\nexport const SHEET_TABS = {\n // === SOURCE OF TRUTH (no suffix - Sarah can edit) ===\n TE_ROSTER: \"T&E-Roster\",\n LC_ROSTER: \"L&C-Roster\",\n LC_SCHEDULE: \"L&C-Schedule\", // Pre-assigned on-call dates from syllabus\n\n // === AUTO-GENERATED (auto) - cleared and rewritten each run ===\n TE_ON_CALL: \"T&E-On-Call-Today\", // DEPRECATED: Use TE_ON_CALL_NEXT\n LC_ON_CALL: \"L&C-On-Call-Today\", // DEPRECATED: Use LC_ON_CALL_NEXT\n WATCH_LIST: \"Watch-List (auto)\",\n TE_ON_CALL_NEXT: \"T&E-On-Call-Next (auto)\", // Clean view: only next class (6 students)\n PENDING_REVIEW: \"Pending-Review (auto)\",\n\n // === APPEND-ONLY LOGS (log) - system adds rows, don't edit/delete ===\n COLD_CALL_LOG: \"Cold-Call-Log (log)\",\n TE_ON_CALL_HISTORY: \"T&E-On-Call-History (log)\", // Archive of all past selections\n LC_ON_CALL_HISTORY: \"L&C-On-Call-History (log)\", // Archive of all past selections\n CANCELLED_CLASSES: \"Cancelled-Classes (log)\",\n\n // === LOW-VISIBILITY (auto, kept at end of tab bar) ===\n CONFIG: \"Config (auto)\", // System config (pool_pivot, etc.)\n LC_ON_CALL_NEXT: \"L&C-On-Call-Next (auto)\", // Filtered view of L&C-Schedule\n} as const;\n\n// Warning messages for auto-generated and log tabs\nexport const TAB_WARNINGS = {\n AUTO: \"⚠️ AUTO-GENERATED - Cleared and rewritten each run. Do not edit.\",\n LOG: \"⚠️ APPEND-ONLY LOG - System adds rows. Do not edit or delete.\",\n} as const;\n\n// ============================================================================\n// Tab Names - Student Forms Spreadsheet\n// ============================================================================\n\n/**\n * Tab name in the STUDENT_FORMS spreadsheet.\n * Google Forms creates this automatically.\n */\nexport const STUDENT_FORMS_TAB = \"Form Responses 1\";\n\n// Google Form column headers (exact question text from the student self-report form).\n// If the form questions change, update these strings — they're used in 3 files.\nexport const FORM_COL_DATE = \"Date of class you'll be absent, late-arriving/early-departing, or unprepared\";\nexport const FORM_COL_TYPE = \"Will you be absent, unprepared, or late/early?\";\nexport const FORM_COL_NAME = \"Your name\";\n\n// ============================================================================\n// Email Footer (for Gmail filtering)\n// ============================================================================\n\n/**\n * Unique footer appended to all emails for Gmail auto-forwarding filters.\n * Filter on: \"LawTracker-AutoNotify\" to forward to robot.squad.sm@gmail.com\n */\nexport const EMAIL_FOOTER = \"\\n\\n--\\nLawTracker-AutoNotify\";\n\n// ============================================================================\n// Alert Thresholds (3-tier system)\n// ============================================================================\n\n/**\n * Absence thresholds for escalating alerts.\n * - CONTACT_DEAN (red): Sarah contacts dean + student\n * - WARN_STUDENT (orange): Sarah warns student\n * - MONITOR (yellow): Early warning, watch list only\n */\nexport const ABSENCE_CONTACT_DEAN = 4;\nexport const ABSENCE_WARN_STUDENT = 3;\nexport const ABSENCE_MONITOR = 2;\n\n/**\n * Concerning event thresholds (absences + unprepared + refused + bad performance).\n */\nexport const CONCERNING_CONTACT_DEAN = 5;\nexport const CONCERNING_WARN_STUDENT = 4;\nexport const CONCERNING_MONITOR = 3;\n\n// ============================================================================\n// Shared Types\n// ============================================================================\n\n/**\n * A single cold call result from a post-class report.\n */\nexport interface ColdCallEntry {\n student_name: string;\n result: \"completed\" | \"absent\" | \"refused\";\n performance?: \"great\" | \"normal\" | \"bad\";\n}\n\n/**\n * Payload for POST /post-class-report endpoint.\n */\nexport interface ReportPayload {\n date: string;\n class_type: \"te\" | \"lc\";\n cold_calls: ColdCallEntry[];\n absences: string[]; // Names of students who were absent (not on on-call list)\n cancelled?: boolean; // True if class was cancelled\n}\n\n/**\n * Determines if a cold call result is a \"concerning event\" for threshold tracking.\n * Concerning events: absent (announced or not), refused, unprepared, or bad performance.\n */\nexport function isConcerningEvent(\n result: ColdCallEntry[\"result\"] | \"unprepared\" | \"absent-announced\",\n performance?: ColdCallEntry[\"performance\"] | \"self-reported\",\n): boolean {\n // Any type of absence is concerning\n if (result === \"absent\" || result === \"absent-announced\") return true;\n // Other concerning events\n if (result === \"refused\" || result === \"unprepared\") return true;\n // Bad performance on completed cold call\n if (performance === \"bad\") return true;\n return false;\n}\n\n/**\n * Formats a Date object as YYYY-MM-DD string for comparison and storage.\n */\nexport function formatDate(date: Date): string {\n return date.toISOString().split(\"T\")[0];\n}\n\n// ============================================================================\n// Authentication\n// ============================================================================\n\n/**\n * Creates authenticated JWT client for Google Sheets API.\n * Requires GOOGLE_APPLICATION_CREDENTIALS env var containing JSON credentials.\n */\nexport function getAuthClient(): JWT {\n const credsJson = Deno.env.get(\"GOOGLE_APPLICATION_CREDENTIALS\");\n if (!credsJson) {\n throw new Error(\n \"GOOGLE_APPLICATION_CREDENTIALS env var not set. Configure in Val.town settings.\",\n );\n }\n const creds = JSON.parse(credsJson);\n return new JWT({\n email: creds.client_email,\n key: creds.private_key,\n scopes: [\"https://www.googleapis.com/auth/spreadsheets\"],\n });\n}\n\n// ============================================================================\n// Name Matching\n// ============================================================================\n\n/**\n * Normalize a name for comparison:\n * - Lowercase\n * - Collapse multiple whitespace to single space\n * - Trim leading/trailing whitespace\n * - Handle \"Last, First\" -> \"First Last\" conversion\n */\nexport function normalizeName(name: string): string {\n if (!name || typeof name !== \"string\") return \"\";\n\n let normalized = name.toLowerCase().trim().replace(/\\s+/g, \" \");\n\n // Handle \"Last, First\" format -> \"First Last\"\n if (normalized.includes(\",\")) {\n const [last, first] = normalized.split(\",\").map((s) => s.trim());\n if (first && last) {\n normalized = `${first} ${last}`;\n }\n }\n\n return normalized;\n}\n\n/**\n * Calculate Levenshtein distance between two strings.\n * Used for fuzzy matching names with typos.\n */\nexport function levenshteinDistance(a: string, b: string): number {\n if (a.length === 0) return b.length;\n if (b.length === 0) return a.length;\n\n const matrix: number[][] = [];\n\n // Initialize first column\n for (let i = 0; i <= b.length; i++) {\n matrix[i] = [i];\n }\n\n // Initialize first row\n for (let j = 0; j <= a.length; j++) {\n matrix[0][j] = j;\n }\n\n // Fill in the rest\n for (let i = 1; i <= b.length; i++) {\n for (let j = 1; j <= a.length; j++) {\n if (b.charAt(i - 1) === a.charAt(j - 1)) {\n matrix[i][j] = matrix[i - 1][j - 1];\n } else {\n matrix[i][j] = Math.min(\n matrix[i - 1][j - 1] + 1, // substitution\n matrix[i][j - 1] + 1, // insertion\n matrix[i - 1][j] + 1, // deletion\n );\n }\n }\n }\n\n return matrix[b.length][a.length];\n}\n\n/**\n * Match result from findMatchingName\n */\nexport interface NameMatchResult {\n matched: true;\n rosterName: string;\n inputName: string;\n matchType: \"exact\" | \"normalized\" | \"fuzzy\";\n distance?: number; // Only for fuzzy matches\n}\n\nexport interface NameNoMatchResult {\n matched: false;\n inputName: string;\n closestCandidate?: string;\n closestDistance?: number;\n}\n\nexport type NameMatch = NameMatchResult | NameNoMatchResult;\n\n/**\n * Find matching name from roster given an input name.\n * Attempts matching in order:\n * 1. Exact match (case-sensitive)\n * 2. Normalized match (case-insensitive, whitespace normalized, Last/First flip)\n * 3. Fuzzy match (Levenshtein distance <= 2)\n *\n * @param inputName - The name from the form\n * @param rosterNames - Array of canonical names from the roster\n * @param maxDistance - Maximum Levenshtein distance for fuzzy match (default 2)\n * @returns NameMatch indicating success/failure and details\n */\nexport function findMatchingName(\n inputName: string,\n rosterNames: string[],\n maxDistance = 2,\n): NameMatch {\n if (!inputName || !rosterNames.length) {\n return { matched: false, inputName: inputName || \"\" };\n }\n\n // 1. Exact match\n if (rosterNames.includes(inputName)) {\n return {\n matched: true,\n rosterName: inputName,\n inputName,\n matchType: \"exact\",\n };\n }\n\n // 2. Normalized match\n const normalizedInput = normalizeName(inputName);\n const normalizedRoster = rosterNames.map((n) => ({\n original: n,\n normalized: normalizeName(n),\n }));\n\n for (const { original, normalized } of normalizedRoster) {\n if (normalized === normalizedInput) {\n return {\n matched: true,\n rosterName: original,\n inputName,\n matchType: \"normalized\",\n };\n }\n }\n\n // 3. Fuzzy match - find best match within threshold\n let bestMatch: { name: string; distance: number } | null = null;\n\n for (const { original, normalized } of normalizedRoster) {\n const distance = levenshteinDistance(normalizedInput, normalized);\n if (distance <= maxDistance) {\n if (!bestMatch || distance < bestMatch.distance) {\n bestMatch = { name: original, distance };\n }\n }\n }\n\n if (bestMatch) {\n return {\n matched: true,\n rosterName: bestMatch.name,\n inputName,\n matchType: \"fuzzy\",\n distance: bestMatch.distance,\n };\n }\n\n // No match found - return closest candidate for debugging\n let closest: { name: string; distance: number } | null = null;\n for (const { original, normalized } of normalizedRoster) {\n const distance = levenshteinDistance(normalizedInput, normalized);\n if (!closest || distance < closest.distance) {\n closest = { name: original, distance };\n }\n }\n\n return {\n matched: false,\n inputName,\n closestCandidate: closest?.name,\n closestDistance: closest?.distance,\n };\n}\n\n/**\n * Parse comma-separated list of names.\n * Handles leading/trailing whitespace around each name.\n * Filters out empty strings.\n */\nexport function parseNameList(input: string): string[] {\n if (!input || typeof input !== \"string\") return [];\n\n return input\n .split(/[,\\n]/) // Split on comma OR newline\n .map((s) => s.trim())\n .filter((s) => s.length > 0);\n}\n\n/**\n * Map class string from form to canonical class_type.\n * Form might say \"T&E\", \"Trusts & Estates\", \"te\", etc.\n */\nexport function parseClassType(classStr: string): \"te\" | \"lc\" | null {\n if (!classStr) return null;\n\n const lower = classStr.toLowerCase().trim();\n\n if (\n lower === \"te\" ||\n lower === \"t&e\" ||\n lower.includes(\"trusts\") ||\n lower.includes(\"estates\")\n ) {\n return \"te\";\n }\n\n if (\n lower === \"lc\" ||\n lower === \"l&c\" ||\n lower.includes(\"capitalism\") ||\n lower.includes(\"law &\")\n ) {\n return \"lc\";\n }\n\n return null;\n}\n\n/**\n * Parse date string from Google Sheets into YYYY-MM-DD format.\n * Handles common formats: \"MM/DD/YYYY\", \"M/D/YYYY\", \"YYYY-MM-DD\"\n * Returns null if unparseable.\n */\nexport function parseSheetDate(dateStr: string): string | null {\n if (!dateStr || typeof dateStr !== \"string\") return null;\n\n const trimmed = dateStr.trim();\n\n // Already ISO format: \"2026-01-12\"\n if (/^\\d{4}-\\d{2}-\\d{2}$/.test(trimmed)) {\n return trimmed;\n }\n\n // US format: \"1/12/2026\" or \"01/12/2026\"\n const usMatch = trimmed.match(/^(\\d{1,2})\\/(\\d{1,2})\\/(\\d{4})$/);\n if (usMatch) {\n const [, month, day, year] = usMatch;\n return `${year}-${month.padStart(2, \"0\")}-${day.padStart(2, \"0\")}`;\n }\n\n // Fallback: try native parsing\n const fallback = new Date(trimmed);\n if (!isNaN(fallback.getTime())) {\n return fallback.toISOString().split(\"T\")[0];\n }\n\n return null;\n}\n\n// ============================================================================\n// Watch List Tab Management\n// ============================================================================\n\n/**\n * Risk level for students approaching thresholds.\n */\nexport type RiskLevel = \"IMMINENT\" | \"WATCH\" | \"CAUTION\";\n\n/**\n * Student data for Watch List display.\n */\nexport interface WatchListStudent {\n name: string;\n class: \"T&E\" | \"L&C\";\n absences: number;\n concerning: number;\n riskLevel: RiskLevel;\n lastCalled: string;\n}\n\n/**\n * Calculate risk level based on counts.\n * Risk levels map to email tiers:\n * - IMMINENT = [ALERT] email (dean notification at 4+ absences, 5+ concerning)\n * - WATCH = [WARNING] email (warn student at 3 absences, 4 concerning)\n * - CAUTION = [WATCH] email (early warning at 2 absences, 3 concerning)\n */\nexport function calculateRiskLevel(absences: number, concerning: number): RiskLevel {\n // At or above dean threshold (4 absences, 5 concerning)\n if (absences >= 4 || concerning >= 5) return \"IMMINENT\";\n // At warning level - Sarah warns student (3 absences, 4 concerning)\n if (absences === 3 || concerning === 4) return \"WATCH\";\n // At early warning level (2 absences, 3 concerning)\n if (absences === 2 || concerning === 3) return \"CAUTION\";\n return \"CAUTION\";\n}\n\n/**\n * Sort priority for risk levels (lower = higher priority).\n */\nfunction riskPriority(level: RiskLevel): number {\n switch (level) {\n case \"IMMINENT\": return 0;\n case \"WATCH\": return 1;\n case \"CAUTION\": return 2;\n }\n}\n\n/**\n * Updates the Watch List tab with current at-risk students.\n * Clears and rewrites all rows each time (idempotent).\n *\n * @param doc - Loaded GoogleSpreadsheet instance\n * @returns Summary of what was written\n */\nexport async function updateWatchListTab(doc: {\n sheetsByTitle: Record;\n addSheet: (opts: { title: string; headerValues: string[] }) => Promise;\n}): Promise<{ studentsAtRisk: number; imminent: number; watch: number }> {\n // Get or create Watch List tab\n let watchSheet = doc.sheetsByTitle[SHEET_TABS.WATCH_LIST] as {\n clear: () => Promise;\n setHeaderRow: (headers: string[]) => Promise;\n addRows: (rows: Record[]) => Promise;\n loadHeaderRow: () => Promise;\n } | undefined;\n\n if (!watchSheet) {\n watchSheet = await doc.addSheet({\n title: SHEET_TABS.WATCH_LIST,\n headerValues: [\"Name\", \"Class\", \"Absences\", \"Concerning\", \"Risk Level\", \"Last Called\"],\n }) as typeof watchSheet;\n }\n\n // Clear existing data (keep headers)\n await watchSheet!.loadHeaderRow();\n await watchSheet!.clear();\n await watchSheet!.setHeaderRow([\"Name\", \"Class\", \"Absences\", \"Concerning\", \"Risk Level\", \"Last Called\"]);\n\n // Collect at-risk students from both rosters\n const atRisk: WatchListStudent[] = [];\n\n // Process T&E Roster\n const teSheet = doc.sheetsByTitle[SHEET_TABS.TE_ROSTER] as {\n loadHeaderRow: () => Promise;\n getRows: () => Promise string }>>;\n } | undefined;\n if (teSheet) {\n await teSheet.loadHeaderRow();\n const teRows = await teSheet.getRows();\n for (const row of teRows) {\n const absences = parseInt(row.get(\"absences\") || \"0\", 10);\n const concerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n if (absences >= 2 || concerning >= 3) {\n atRisk.push({\n name: row.get(\"student_name\") || \"\",\n class: \"T&E\",\n absences,\n concerning,\n riskLevel: calculateRiskLevel(absences, concerning),\n lastCalled: row.get(\"last_called\") || \"\",\n });\n }\n }\n }\n\n // Process L&C Roster\n const lcSheet = doc.sheetsByTitle[SHEET_TABS.LC_ROSTER] as typeof teSheet;\n if (lcSheet) {\n await lcSheet.loadHeaderRow();\n const lcRows = await lcSheet.getRows();\n for (const row of lcRows) {\n const absences = parseInt(row.get(\"absences\") || \"0\", 10);\n const concerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n if (absences >= 2 || concerning >= 3) {\n atRisk.push({\n name: row.get(\"student_name\") || \"\",\n class: \"L&C\",\n absences,\n concerning,\n riskLevel: calculateRiskLevel(absences, concerning),\n lastCalled: row.get(\"last_called\") || \"\",\n });\n }\n }\n }\n\n // Sort by risk level, then concerning (desc), then absences (desc)\n atRisk.sort((a, b) => {\n const riskDiff = riskPriority(a.riskLevel) - riskPriority(b.riskLevel);\n if (riskDiff !== 0) return riskDiff;\n const concerningDiff = b.concerning - a.concerning;\n if (concerningDiff !== 0) return concerningDiff;\n return b.absences - a.absences;\n });\n\n // Write rows\n if (atRisk.length > 0) {\n await watchSheet!.addRows(\n atRisk.map((s) => ({\n Name: s.name,\n Class: s.class,\n Absences: s.absences,\n Concerning: s.concerning,\n \"Risk Level\": s.riskLevel,\n \"Last Called\": s.lastCalled,\n }))\n );\n }\n\n return {\n studentsAtRisk: atRisk.length,\n imminent: atRisk.filter((s) => s.riskLevel === \"IMMINENT\").length,\n watch: atRisk.filter((s) => s.riskLevel === \"WATCH\").length,\n };\n}\n\n// ============================================================================\n// Config Tab Helpers\n// ============================================================================\n\n/**\n * Read a value from the Config tab.\n * Returns null if Config tab doesn't exist or key not found.\n */\nexport async function getConfigValue(\n doc: { sheetsByTitle: Record },\n key: string,\n): Promise {\n const configSheet = doc.sheetsByTitle[SHEET_TABS.CONFIG] as {\n loadHeaderRow: () => Promise;\n getRows: () => Promise string }>>;\n } | undefined;\n\n if (!configSheet) return null;\n\n await configSheet.loadHeaderRow();\n const rows = await configSheet.getRows();\n const row = rows.find((r) => r.get(\"key\") === key);\n return row?.get(\"value\") || null;\n}\n",[152],"287652aa-0c2e-11f1-93bf-42dde27851f2","te-report-prompt.cron.tsx","e4b192e0-ef4b-11f0-b248-42dde27851f2",151,["D",1771352781196],7,"/**\n * T&E Post-Class Report Prompt\n * Runs 6 PM EST Mon/Wed (11 PM UTC)\n * Reminds professor to submit post-class report.\n */\n\nimport { email } from \"https://esm.town/v/std/email\";\nimport { EMAIL_FOOTER, formatDate } from \"./utils.tsx\";\n\nexport default async function() {\n // Val.town free tier: email() always sends to account owner.\n // NOTIFICATION_EMAIL serves as an opt-in flag — if not set, skip silently.\n if (!Deno.env.get(\"NOTIFICATION_EMAIL\")) {\n console.log(\"NOTIFICATION_EMAIL not set — skipping notification.\");\n return;\n }\n\n const today = formatDate(new Date());\n\n await email({\n // Val.town free tier: emails go to account owner\n subject: `[T&E] Trusts & Estates Report - ${today}`,\n text: `Hi Sarah,\n\nTime to submit your post-class report for T&E.\n\nPlease note:\n- Who was absent (not on self-report list)\n- Who you called on and their performance (great/normal/bad)\n- Any students who refused when called\n\nUse the reporting form: https://sahar--ab2d2176f16511f0a75a42dde27851f2.web.val.run\n\nThanks!` + EMAIL_FOOTER,\n });\n\n console.log(`Sent T&E report prompt for ${today}`);\n}\n",[161],"43de744e-0c30-11f1-9ca0-42dde27851f2",["D",1771353686159],[27],"288d8510-0c2e-11f1-8b8e-42dde27851f2","sarah-report-form.http.tsx","ab2d2176-f165-11f0-a75a-42dde27851f2",155,["D",1771352781270],3,"/**\n * Sarah's Post-Class Report Form\n *\n * HTML form that POSTs directly to post-class-report endpoint.\n * No Google Form needed - data goes straight to the API.\n *\n * GET /sarah-report-form → Shows the form\n * POST /sarah-report-form → Submits the report\n */\n\nimport { formatDate, MAIN_SHEET_ID, parseNameList } from \"./utils.tsx\";\n\nconst POST_CLASS_REPORT_URL = \"https://sahar--53e764dcef4c11f09c6042dde27851f2.web.val.run\";\nconst API_KEY = Deno.env.get(\"REPORT_API_KEY\") || \"\";\n\nasync function handlePost(req: Request): Promise {\n const formData = await req.formData();\n\n const classType = formData.get(\"class_type\") as string;\n const date = formData.get(\"date\") as string;\n const cancelled = formData.get(\"cancelled\") === \"yes\";\n\n // If cancelled, just log it and return\n if (cancelled) {\n const payload = {\n date,\n class_type: classType,\n cold_calls: [],\n absences: [],\n cancelled: true,\n };\n\n const response = await fetch(POST_CLASS_REPORT_URL, {\n method: \"POST\",\n headers: {\n \"Content-Type\": \"application/json\",\n \"X-API-Key\": API_KEY,\n },\n body: JSON.stringify(payload),\n });\n\n const result = await response.json();\n return renderSuccess(result, \"Class marked as cancelled.\");\n }\n\n // Parse name fields\n const greatNames = parseNameList(formData.get(\"great\") as string || \"\");\n const normalNames = parseNameList(formData.get(\"normal\") as string || \"\");\n const badNames = parseNameList(formData.get(\"bad\") as string || \"\");\n const refusedNames = parseNameList(formData.get(\"refused\") as string || \"\");\n const absentNames = parseNameList(formData.get(\"absent\") as string || \"\");\n\n // Build cold_calls array\n const coldCalls: Array<{ student_name: string; result: string; performance?: string }> = [];\n\n for (const name of greatNames) {\n coldCalls.push({ student_name: name, result: \"completed\", performance: \"great\" });\n }\n for (const name of normalNames) {\n coldCalls.push({ student_name: name, result: \"completed\", performance: \"normal\" });\n }\n for (const name of badNames) {\n coldCalls.push({ student_name: name, result: \"completed\", performance: \"bad\" });\n }\n for (const name of refusedNames) {\n coldCalls.push({ student_name: name, result: \"refused\" });\n }\n\n const payload = {\n date,\n class_type: classType,\n cold_calls: coldCalls,\n absences: absentNames,\n };\n\n const response = await fetch(POST_CLASS_REPORT_URL, {\n method: \"POST\",\n headers: {\n \"Content-Type\": \"application/json\",\n \"X-API-Key\": API_KEY,\n },\n body: JSON.stringify(payload),\n });\n\n const result = await response.json();\n\n if (!response.ok) {\n return renderError(result.error || \"Something went wrong\");\n }\n\n return renderSuccess(result);\n}\n\nfunction renderSuccess(result: Record, message?: string): Response {\n const unmatchedNames = (result.unmatched_names as string[]) || [];\n const fuzzyMatches = (result.fuzzy_matches as Array<{ input: string; matched: string }>) || [];\n const hasUnmatched = unmatchedNames.length > 0;\n const hasFuzzy = fuzzyMatches.length > 0;\n\n const html = `\n\n\n Report Submitted\n \n \n\n\n

✓ Report Submitted

${message || \"Your post-class report has been recorded.\"}

\n ${hasFuzzy ? `\n

ℹ️ Fuzzy Matches (please verify)

These names were close but not exact matches:

\"${m.input}\" → ${m.matched}

If any match is wrong, edit the roster directly to fix the counts.

\n ` : \"\"}\n ${hasUnmatched ? `\n

⚠️ Names Not Found

The following names were NOT recorded because they don't match any student in the roster:

${n}

Please check spelling. Use format: First Last (e.g., \"Alice Adams\")

\n ` : \"\"}\n

\n Details:
\n Cold calls logged: ${result.cold_calls_logged || 0}
\n Absences logged: ${result.absences_logged || 0}
\n ${(result.alerts_sent as string[] || []).length > 0 ? `
Alerts sent: ${(result.alerts_sent as string[]).join(\", \")}` : \"\"}\n

← Submit another report

\n\n`;\n\n return new Response(html, { headers: { \"Content-Type\": \"text/html\" } });\n}\n\nfunction renderError(error: string): Response {\n const html = `\n\n\n Error\n \n \n\n\n

Error

${error}

← Try again

\n\n`;\n\n return new Response(html, { headers: { \"Content-Type\": \"text/html\" }, status: 400 });\n}\n\nfunction renderForm(): Response {\n const today = formatDate(new Date());\n\n const html = `\n\n\n Post-Class Report\n \n \n\n\n

📋 Post-Class Report

\n\n Class\n \n\n

Today's On-Call Students

\n\n Date\n \n\n Was class cancelled?\n \n\n

🎯 Students Called On

\n\n Great performance\n \n

Students who did well when called on

\n\n Normal performance\n \n

Students who performed adequately

\n\n Poor performance\n \n

Students who struggled or were unprepared

\n\n

⚠️ Issues

\n\n Refused when called\n \n

Students who declined to answer when called on

\n\n Students absent\n \n

All absent students (whether on call list or not)

\n\n

\n No student data will be recorded for a cancelled class.\n

\n\n \n

\n\n

Need to correct a mistake?

Open the tracking spreadsheet and:

Wrong student counts: Edit the student's row in T&E-Roster or L&C-Roster (these are the source of truth)
Wrong cold call entry: Delete the row from Cold-Call-Log (log)
Duplicate submission: The system auto-skips duplicates, so resubmitting is safe

\n\n \n\n`;\n\n return new Response(html, { headers: { \"Content-Type\": \"text/html\" } });\n}\n\nexport default async function handler(req: Request): Promise {\n if (req.method === \"POST\") {\n return handlePost(req);\n }\n return renderForm();\n}\n",[172],"b1ff5b1c-f08f-11f0-a813-42dde27851f2","refresh-watch-list.http.tsx","b1ff5bc6-f08f-11f0-a813-42dde27851f2",106,["D",1768316040524],"/**\n * Refresh Watch List (HTTP Endpoint)\n *\n * Manually triggers a refresh of the Watch List tab.\n * Use this after manually editing roster counts in Sheets.\n *\n * GET /refresh-watch-list → Refreshes Watch List tab\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { getAuthClient, SHEET_ID, updateWatchListTab } from \"./utils.tsx\";\n\nexport default async function (_req: Request): Promise {\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n const result = await updateWatchListTab(doc);\n\n return Response.json({\n success: true,\n message: \"Watch List refreshed\",\n ...result,\n });\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 }\n );\n }\n}\n",[180],"286b312c-0c2e-11f1-95b7-42dde27851f2","post-class-report.http.tsx","53e764dc-ef4c-11f0-9c60-42dde27851f2",149,["D",1771352781114],25,"/**\n * Post-Class Report Endpoint\n * Receives Sarah's post-class report, updates tracking, triggers alerts.\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { email } from \"https://esm.town/v/std/email\";\nimport {\n ABSENCE_CONTACT_DEAN,\n ABSENCE_MONITOR,\n ABSENCE_WARN_STUDENT,\n type ColdCallEntry,\n CONCERNING_CONTACT_DEAN,\n CONCERNING_MONITOR,\n CONCERNING_WARN_STUDENT,\n EMAIL_FOOTER,\n findMatchingName,\n FORM_COL_DATE,\n FORM_COL_NAME,\n FORM_COL_TYPE,\n getAuthClient,\n isConcerningEvent,\n LC_STUDENT_FORMS_SHEET_ID,\n MAIN_SHEET_ID,\n parseSheetDate,\n type ReportPayload,\n SHEET_ID,\n SHEET_TABS,\n STUDENT_FORMS_TAB,\n TE_STUDENT_FORMS_SHEET_ID,\n} from \"./utils.tsx\";\n\n// Local aliases for readability\nconst TABS = {\n TE_ROSTER: SHEET_TABS.TE_ROSTER,\n LC_ROSTER: SHEET_TABS.LC_ROSTER,\n COLD_CALL_LOG: SHEET_TABS.COLD_CALL_LOG,\n CANCELLED_CLASSES: SHEET_TABS.CANCELLED_CLASSES,\n} as const;\n\n/**\n * Fetches concerning event history for a student from Cold Call Log.\n * Returns formatted strings like \"2026-01-13: Absent (bad)\"\n */\nasync function getStudentHistory(\n logSheet: { getRows: () => Promise string }>> },\n studentName: string,\n includeCompleted = false,\n): Promise {\n const rows = await logSheet.getRows();\n const history: string[] = [];\n\n for (const row of rows) {\n if (row.get(\"student_name\") !== studentName) continue;\n\n const date = row.get(\"date\");\n const result = row.get(\"result\");\n const performance = row.get(\"performance\");\n\n // Only include concerning events unless includeCompleted\n const isConcerning = isConcerningEvent(\n result as ColdCallEntry[\"result\"],\n performance as ColdCallEntry[\"performance\"],\n );\n\n if (isConcerning || includeCompleted) {\n let entry = `${date}: ${result}`;\n if (performance && performance !== \"normal\") {\n entry += ` (${performance})`;\n }\n history.push(entry);\n }\n }\n\n return history;\n}\n\nasync function sendAlert(\n to: string,\n student: string,\n alertType: \"absences\" | \"concerning\",\n count: number,\n history: string[],\n) {\n const subject = alertType === \"absences\"\n ? `[ALERT] ${student} - ${count} absences`\n : `[ALERT] ${student} - ${count} concerning events`;\n\n const action = alertType === \"absences\"\n ? \"Contact dean + student per policy.\"\n : \"Contact student.\";\n\n const alertLabel = alertType === \"concerning\" ? \"concerning events\" : \"absences\";\n const body = `${student} has reached ${count} ${alertLabel}.\n\nHistory:\n${history.map((h) => `- ${h}`).join(\"\\n\")}\n\nAction needed: ${action}` + EMAIL_FOOTER;\n\n // Val.town free tier: emails go to account owner (filter in Gmail)\n await email({ subject, text: body });\n}\n\nasync function sendWatchAlert(\n student: string,\n alertType: \"absences\" | \"concerning\",\n count: number,\n) {\n const subject = alertType === \"absences\"\n ? `[WATCH] ${student} - ${count} absences (early warning)`\n : `[WATCH] ${student} - ${count} concerning events (early warning)`;\n\n const alertLabel = alertType === \"concerning\" ? \"concerning events\" : \"absences\";\n const warningAt = alertType === \"absences\" ? ABSENCE_WARN_STUDENT : CONCERNING_WARN_STUDENT;\n\n const body = `${student} now has ${count} ${alertLabel}.\n\nOne more ${alertType === \"absences\" ? \"absence\" : \"concerning event\"} will trigger the warning level (at ${warningAt}).\n\nThis is an early warning - no action required yet, but monitor this student.` + EMAIL_FOOTER;\n\n await email({ subject, text: body });\n}\n\n/**\n * Send WARNING alert - Sarah should warn the student directly.\n */\nasync function sendWarningAlert(\n student: string,\n alertType: \"absences\" | \"concerning\",\n count: number,\n) {\n const subject = alertType === \"absences\"\n ? `[WARNING] ${student} - ${count} absences - Please warn student`\n : `[WARNING] ${student} - ${count} concerning events - Please warn student`;\n\n const alertLabel = alertType === \"concerning\" ? \"concerning events\" : \"absences\";\n const threshold = alertType === \"absences\" ? ABSENCE_CONTACT_DEAN : CONCERNING_CONTACT_DEAN;\n\n const body = `${student} now has ${count} ${alertLabel}.\n\nACTION NEEDED: Contact this student to warn them about their attendance.\n\nOne more ${alertType === \"absences\" ? \"absence\" : \"concerning event\"} (total ${threshold}) will require dean notification.` + EMAIL_FOOTER;\n\n await email({ subject, text: body });\n}\n\n/**\n * Check if watch threshold is reached and send early warning if so.\n * Only sends if exactly at watch threshold (not past it).\n * Uses last_*_watch columns to prevent duplicate warnings.\n */\nasync function checkAndSendWatchAlert(params: {\n studentName: string;\n alertType: \"absences\" | \"concerning\";\n newCount: number;\n watchThreshold: number;\n notificationEmail: string | undefined;\n rosterRow: { get: (key: string) => string; set: (key: string, val: string) => void; save: () => Promise };\n}): Promise {\n const { studentName, alertType, newCount, watchThreshold, notificationEmail, rosterRow } = params;\n\n // Only send at exactly the watch threshold\n if (newCount !== watchThreshold || !notificationEmail) {\n return false;\n }\n\n // Check if we already sent a watch for this student\n const columnName = alertType === \"absences\" ? \"last_absence_watch\" : \"last_concerning_watch\";\n const lastWatch = rosterRow.get(columnName);\n if (lastWatch) {\n return false; // Already warned\n }\n\n await sendWatchAlert(studentName, alertType, newCount);\n\n // Record that we sent the watch\n rosterRow.set(columnName, String(newCount));\n // Note: caller should save the row\n\n return true;\n}\n\n/**\n * Check if warning threshold is reached and send WARNING if so.\n * Uses last_*_warning columns to prevent duplicate warnings.\n */\nasync function checkAndSendWarningAlert(params: {\n studentName: string;\n alertType: \"absences\" | \"concerning\";\n newCount: number;\n warningThreshold: number;\n notificationEmail: string | undefined;\n rosterRow: { get: (key: string) => string; set: (key: string, val: string) => void; save: () => Promise };\n}): Promise {\n const { studentName, alertType, newCount, warningThreshold, notificationEmail, rosterRow } = params;\n\n if (newCount < warningThreshold || !notificationEmail) {\n return false;\n }\n\n const columnName = alertType === \"absences\" ? \"last_absence_warning\" : \"last_concerning_warning\";\n const lastWarning = rosterRow.get(columnName);\n if (lastWarning && parseInt(lastWarning, 10) >= warningThreshold) {\n return false; // Already warned\n }\n\n await sendWarningAlert(studentName, alertType, newCount);\n\n rosterRow.set(columnName, String(newCount));\n // Note: caller should save the row\n\n return true;\n}\n\n/**\n * Check if threshold is reached and send alert if so.\n * Returns true if alert was sent.\n *\n * Uses last_*_alert columns to track whether we've already alerted at/above threshold.\n * This handles manual edits to counts in Sheets - we alert if:\n * - newCount >= threshold, AND\n * - we haven't previously alerted at or above threshold\n *\n * NOTE: We only alert once per threshold crossing. If Sarah corrects count\n * from 3→5→2→3, we won't re-alert at 3 because we already alerted at 5.\n * This prevents alert spam during manual corrections.\n */\nasync function checkAndSendThresholdAlert(params: {\n studentName: string;\n alertType: \"absences\" | \"concerning\";\n newCount: number;\n threshold: number;\n notificationEmail: string | undefined;\n alerts: string[];\n logSheet: { getRows: () => Promise string }>> };\n rosterRow: { get: (key: string) => string; set: (key: string, val: string) => void; save: () => Promise };\n extraHistoryItem?: string; // Optional extra item to add to history\n}): Promise {\n const { studentName, alertType, newCount, threshold, notificationEmail, alerts, logSheet, rosterRow, extraHistoryItem } =\n params;\n\n // Read last alert count from roster (handles manual edits)\n const columnName = alertType === \"absences\" ? \"last_absence_alert\" : \"last_concerning_alert\";\n const lastAlertStr = rosterRow.get(columnName);\n const lastAlert = lastAlertStr ? parseInt(lastAlertStr, 10) : null;\n\n // Alert if crossing threshold AND haven't already alerted at/above threshold\n const shouldAlert = newCount >= threshold && (lastAlert === null || lastAlert < threshold);\n\n if (!shouldAlert || !notificationEmail) {\n return false;\n }\n\n alerts.push(`${studentName}: ${threshold} ${alertType}`);\n\n let history = await getStudentHistory(logSheet, studentName);\n\n // Filter to absences only for absence alerts\n if (alertType === \"absences\") {\n history = history.filter((h) => h.includes(\"absent\"));\n }\n\n // Add extra item if provided (e.g., \"Absent (not on call list)\")\n if (extraHistoryItem) {\n history.push(extraHistoryItem);\n }\n\n await sendAlert(notificationEmail, studentName, alertType, newCount, history);\n\n // Record that we alerted at this count (prevents duplicate alerts)\n rosterRow.set(columnName, String(newCount));\n await rosterRow.save();\n\n return true;\n}\n\nexport default async function (req: Request): Promise {\n // Auth check\n const apiKey = Deno.env.get(\"REPORT_API_KEY\");\n const reqKey = req.headers.get(\"X-API-Key\");\n if (!apiKey || reqKey !== apiKey) {\n return new Response(\"Unauthorized\", { status: 401 });\n }\n\n if (req.method !== \"POST\") {\n return new Response(\"Method not allowed\", { status: 405 });\n }\n\n const notificationEmail = Deno.env.get(\"NOTIFICATION_EMAIL\");\n\n let payload: ReportPayload;\n try {\n payload = await req.json();\n } catch {\n return Response.json({ error: \"Invalid JSON\" }, { status: 400 });\n }\n\n // Validate payload\n if (!payload.date || !payload.class_type || !payload.cold_calls) {\n return Response.json({ error: \"Missing required fields\" }, { status: 400 });\n }\n\n // Handle class cancellation — log it and skip all student processing\n if (payload.cancelled) {\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n let cancelledSheet = doc.sheetsByTitle[TABS.CANCELLED_CLASSES];\n if (!cancelledSheet) {\n cancelledSheet = await doc.addSheet({\n title: TABS.CANCELLED_CLASSES,\n headerValues: [\"date\", \"class_type\", \"logged_at\"],\n });\n }\n await cancelledSheet.loadHeaderRow();\n\n // Idempotency: don't log the same cancellation twice\n const existingRows = await cancelledSheet.getRows();\n const alreadyLogged = existingRows.some(\n (r) => r.get(\"date\") === payload.date && r.get(\"class_type\") === payload.class_type,\n );\n\n if (!alreadyLogged) {\n await cancelledSheet.addRow({\n date: payload.date,\n class_type: payload.class_type,\n logged_at: new Date().toISOString(),\n });\n }\n\n return Response.json({\n success: true,\n date: payload.date,\n class_cancelled: true,\n message: alreadyLogged ? \"Already logged as cancelled\" : \"Class cancellation recorded\",\n });\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 },\n );\n }\n }\n\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n // Get or create Cold Call Log\n let logSheet = doc.sheetsByTitle[TABS.COLD_CALL_LOG];\n if (!logSheet) {\n logSheet = await doc.addSheet({\n title: TABS.COLD_CALL_LOG,\n headerValues: [\"date\", \"student_name\", \"result\", \"performance\"],\n });\n }\n await logSheet.loadHeaderRow();\n\n // Load existing log entries for idempotency checks\n const existingLogRows = await logSheet.getRows();\n const existingEntries = new Set(\n existingLogRows.map((r) => `${r.get(\"date\")}|${r.get(\"student_name\")}|${r.get(\"result\")}`),\n );\n const isDuplicateEntry = (date: string, studentName: string, result: string) =>\n existingEntries.has(`${date}|${studentName}|${result}`);\n\n // Get roster based on class type\n const rosterTabName = payload.class_type === \"lc\" ? TABS.LC_ROSTER : TABS.TE_ROSTER;\n const rosterSheet = doc.sheetsByTitle[rosterTabName];\n if (!rosterSheet) {\n return Response.json({ error: `Roster not found: ${rosterTabName}` }, { status: 500 });\n }\n\n await rosterSheet.loadHeaderRow();\n const rosterRows = await rosterSheet.getRows();\n\n // Create roster lookup - both Map (for row access) and array (for fuzzy matching)\n const rosterByName = new Map();\n const rosterNames: string[] = [];\n for (const row of rosterRows) {\n const name = row.get(\"student_name\");\n if (name && !rosterByName.has(name)) {\n rosterByName.set(name, row);\n rosterNames.push(name);\n }\n }\n\n // Helper to resolve a name (handles typos, case, whitespace)\n // Records fuzzy matches for Sarah to review\n const resolveStudentName = (inputName: string): { canonicalName: string | null; row: typeof rosterRows[0] | null } => {\n const match = findMatchingName(inputName, rosterNames);\n if (match.matched) {\n // Track fuzzy matches so Sarah can verify\n if (match.matchType === \"fuzzy\") {\n fuzzyMatches.push({ input: inputName, matched: match.rosterName });\n }\n return { canonicalName: match.rosterName, row: rosterByName.get(match.rosterName) || null };\n }\n return { canonicalName: null, row: null };\n };\n\n const alerts: string[] = [];\n const processedUnprepared = new Set();\n const processedAbsent = new Set(); // Track absences from cold_calls to avoid double-counting\n const unmatchedNames: string[] = []; // Track names not found in roster\n const fuzzyMatches: Array<{ input: string; matched: string }> = []; // Track fuzzy matches for review\n let skippedDuplicateAbsences = 0;\n let skippedDuplicateLogEntries = 0;\n\n // Load cancelled dates to skip self-reports for cancelled classes\n const cancelledDates = new Set();\n const cancelledSheet = doc.sheetsByTitle[TABS.CANCELLED_CLASSES];\n if (cancelledSheet) {\n await cancelledSheet.loadHeaderRow();\n const cancelledRows = await cancelledSheet.getRows();\n for (const row of cancelledRows) {\n const date = row.get(\"date\");\n if (date) cancelledDates.add(date);\n }\n }\n\n // Process self-reports for this date (skip if class was cancelled)\n const isClassCancelled = cancelledDates.has(payload.date);\n\n // Read self-reports from the correct student forms spreadsheet (T&E or L&C)\n const studentFormsSheetId = payload.class_type === \"te\"\n ? TE_STUDENT_FORMS_SHEET_ID\n : LC_STUDENT_FORMS_SHEET_ID;\n let selfReportRows: Array<{ get: (key: string) => string }> = [];\n try {\n const studentFormsDoc = new GoogleSpreadsheet(studentFormsSheetId, auth);\n await studentFormsDoc.loadInfo();\n const selfReportsSheet = studentFormsDoc.sheetsByTitle[STUDENT_FORMS_TAB];\n if (selfReportsSheet) {\n await selfReportsSheet.loadHeaderRow();\n selfReportRows = await selfReportsSheet.getRows();\n }\n } catch (err) {\n console.warn(\"Could not read student self-reports:\", err);\n }\n\n if (selfReportRows.length > 0 && !isClassCancelled) {\n\n for (const row of selfReportRows) {\n const reportDate = row.get(FORM_COL_DATE);\n const reportType = row.get(FORM_COL_TYPE) || \"\";\n const studentName = row.get(FORM_COL_NAME);\n\n if (!reportDate || !studentName) continue;\n\n const reportDateStr = parseSheetDate(reportDate);\n if (!reportDateStr || reportDateStr !== payload.date) continue;\n\n // Find student in roster (fuzzy match)\n const selfReportRosterNames = Array.from(rosterByName.keys());\n const selfReportMatch = findMatchingName(studentName, selfReportRosterNames);\n const matchedRosterName = selfReportMatch.matched ? selfReportMatch.rosterName : null;\n const matchedRosterRow = matchedRosterName ? rosterByName.get(matchedRosterName)! : null;\n\n if (!matchedRosterName || !matchedRosterRow) continue;\n\n const reportTypeLower = reportType.toLowerCase();\n\n // Handle UNPREPARED self-reports\n if (reportTypeLower.includes(\"unprepared\") && !processedUnprepared.has(matchedRosterName)) {\n processedUnprepared.add(matchedRosterName);\n\n // Idempotency: skip if already logged\n if (isDuplicateEntry(payload.date, matchedRosterName, \"unprepared\")) {\n skippedDuplicateLogEntries++;\n continue;\n }\n\n // Log to Cold Call Log for history tracking\n await logSheet.addRow({\n date: payload.date,\n student_name: matchedRosterName,\n result: \"unprepared\",\n performance: \"self-reported\",\n });\n\n const prevConcerning = parseInt(matchedRosterRow.get(\"concerning_events\") || \"0\", 10);\n const newConcerning = prevConcerning + 1;\n matchedRosterRow.set(\"concerning_events\", String(newConcerning));\n\n await checkAndSendWatchAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_MONITOR,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARN_STUDENT,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow: matchedRosterRow,\n });\n\n await matchedRosterRow.save();\n }\n\n // Handle ANNOUNCED ABSENCE self-reports (FIX-5: Previously missing!)\n if (reportTypeLower.includes(\"absent\") && !processedAbsent.has(matchedRosterName)) {\n processedAbsent.add(matchedRosterName);\n\n // Idempotency: skip if already logged\n if (isDuplicateEntry(payload.date, matchedRosterName, \"absent-announced\")) {\n skippedDuplicateLogEntries++;\n continue;\n }\n\n // Log to Cold Call Log (marked as announced to distinguish from unannounced)\n await logSheet.addRow({\n date: payload.date,\n student_name: matchedRosterName,\n result: \"absent-announced\",\n performance: \"self-reported\",\n });\n\n // Update absence counters\n const prevAbsences = parseInt(matchedRosterRow.get(\"absences\") || \"0\", 10);\n const newAbsences = prevAbsences + 1;\n matchedRosterRow.set(\"absences\", String(newAbsences));\n\n // Track announced absence specifically\n const prevAnnounced = parseInt(matchedRosterRow.get(\"absences_announced\") || \"0\", 10);\n matchedRosterRow.set(\"absences_announced\", String(prevAnnounced + 1));\n\n // Update concerning counter (all absences are concerning)\n const prevConcerning = parseInt(matchedRosterRow.get(\"concerning_events\") || \"0\", 10);\n const newConcerning = prevConcerning + 1;\n matchedRosterRow.set(\"concerning_events\", String(newConcerning));\n\n // Check absence thresholds\n await checkAndSendWatchAlert({\n studentName: matchedRosterName,\n alertType: \"absences\",\n newCount: newAbsences,\n watchThreshold: ABSENCE_MONITOR,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARN_STUDENT,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow: matchedRosterRow,\n });\n\n // Check concerning thresholds\n await checkAndSendWatchAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_MONITOR,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARN_STUDENT,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow: matchedRosterRow,\n });\n\n await matchedRosterRow.save();\n }\n }\n }\n\n // Process cold calls\n for (const call of payload.cold_calls) {\n // Resolve name (handles typos, case, whitespace)\n const { canonicalName, row: rosterRow } = resolveStudentName(call.student_name);\n if (!rosterRow || !canonicalName) {\n console.warn(`Student \"${call.student_name}\" not found in roster - skipping`);\n unmatchedNames.push(call.student_name);\n continue; // Skip unknown students\n }\n\n // Use canonical name for all operations\n const studentName = canonicalName;\n\n // Idempotency: skip if already logged (use canonical name)\n if (isDuplicateEntry(payload.date, studentName, call.result)) {\n skippedDuplicateLogEntries++;\n continue;\n }\n\n // Add to Cold Call Log (use canonical name)\n await logSheet.addRow({\n date: payload.date,\n student_name: studentName,\n result: call.result,\n performance: call.performance || \"\",\n });\n\n // Update cold call count\n const prevCount = parseInt(rosterRow.get(\"cold_call_count\") || \"0\", 10);\n rosterRow.set(\"cold_call_count\", String(prevCount + 1));\n rosterRow.set(\"last_called\", payload.date);\n\n // Check if concerning event - but skip if already counted as unprepared\n const alreadyCountedAsUnprepared = processedUnprepared.has(studentName);\n if (isConcerningEvent(call.result, call.performance) && !alreadyCountedAsUnprepared) {\n const prevConcerning = parseInt(rosterRow.get(\"concerning_events\") || \"0\", 10);\n const newConcerning = prevConcerning + 1;\n rosterRow.set(\"concerning_events\", String(newConcerning));\n\n // Track specific event type\n if (call.result === \"refused\") {\n const prevRefused = parseInt(rosterRow.get(\"refused_count\") || \"0\", 10);\n rosterRow.set(\"refused_count\", String(prevRefused + 1));\n } else if (call.performance === \"bad\") {\n const prevBad = parseInt(rosterRow.get(\"bad_performance_count\") || \"0\", 10);\n rosterRow.set(\"bad_performance_count\", String(prevBad + 1));\n }\n\n await checkAndSendWatchAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_MONITOR,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARN_STUDENT,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow,\n });\n }\n\n // Update absences if they were absent when called (unannounced - no self-report)\n if (call.result === \"absent\") {\n processedAbsent.add(studentName); // Track to avoid double-counting in absences array\n\n const prevAbsences = parseInt(rosterRow.get(\"absences\") || \"0\", 10);\n const newAbsences = prevAbsences + 1;\n rosterRow.set(\"absences\", String(newAbsences));\n\n // Track as unannounced (didn't fill out self-report form)\n const prevUnannounced = parseInt(rosterRow.get(\"absences_unannounced\") || \"0\", 10);\n rosterRow.set(\"absences_unannounced\", String(prevUnannounced + 1));\n\n await checkAndSendWatchAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n watchThreshold: ABSENCE_MONITOR,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARN_STUDENT,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow,\n });\n }\n\n await rosterRow.save(); // May double-save if alerts were sent, that's OK\n }\n\n // Process general absences (students not on on-call list who were absent)\n for (const absentName of payload.absences || []) {\n // Resolve name (handles typos, case, whitespace)\n const { canonicalName, row: rosterRow } = resolveStudentName(absentName);\n\n // Skip if already counted as absent in cold_calls (check both input and canonical)\n if (processedAbsent.has(absentName) || (canonicalName && processedAbsent.has(canonicalName))) {\n skippedDuplicateAbsences++;\n continue;\n }\n\n if (rosterRow && canonicalName) {\n const studentName = canonicalName;\n\n // Idempotency: skip if already logged (use canonical name)\n if (isDuplicateEntry(payload.date, studentName, \"absent\")) {\n skippedDuplicateLogEntries++;\n continue;\n }\n\n // Log to Cold Call Log for history tracking (use canonical name)\n await logSheet.addRow({\n date: payload.date,\n student_name: studentName,\n result: \"absent\",\n performance: \"not-on-call\",\n });\n\n const prevAbsences = parseInt(rosterRow.get(\"absences\") || \"0\", 10);\n const newAbsences = prevAbsences + 1;\n rosterRow.set(\"absences\", String(newAbsences));\n\n // Track as unannounced (general absences = not on call list, no self-report)\n const prevUnannounced = parseInt(rosterRow.get(\"absences_unannounced\") || \"0\", 10);\n rosterRow.set(\"absences_unannounced\", String(prevUnannounced + 1));\n\n const prevConcerning = parseInt(rosterRow.get(\"concerning_events\") || \"0\", 10);\n const newConcerning = prevConcerning + 1;\n rosterRow.set(\"concerning_events\", String(newConcerning));\n\n await checkAndSendWatchAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n watchThreshold: ABSENCE_MONITOR,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARN_STUDENT,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow,\n });\n\n await checkAndSendWatchAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_MONITOR,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARN_STUDENT,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_CONTACT_DEAN,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow,\n });\n\n await rosterRow.save(); // May double-save if alerts were sent, that's OK\n } else {\n // Name not found in roster\n unmatchedNames.push(absentName);\n }\n }\n\n return Response.json({\n success: true,\n date: payload.date,\n class_cancelled: isClassCancelled,\n cold_calls_logged: payload.cold_calls.length - unmatchedNames.filter(n => payload.cold_calls.some(c => c.student_name === n)).length,\n absences_logged: (payload.absences || []).length - unmatchedNames.filter(n => (payload.absences || []).includes(n)).length,\n unprepared_processed: processedUnprepared.size,\n skipped_duplicate_absences: skippedDuplicateAbsences,\n skipped_duplicate_log_entries: skippedDuplicateLogEntries,\n alerts_sent: alerts,\n unmatched_names: unmatchedNames,\n fuzzy_matches: fuzzyMatches,\n });\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 },\n );\n }\n}\n",[187],"289f827e-0c2e-11f1-a4e8-42dde27851f2","oncall-selection.http.tsx","78f39bfe-ef49-11f0-a9a7-42dde27851f2",157,["D",1771352781129],13,"/**\n * T&E On-Call Selection\n *\n * Selects 6 students for cold-calling based on:\n * - Day of week (Mon = first half alphabet, Wed = second half)\n * - Exclusion of same-day self-reports\n * - Prioritization by fewest actual cold calls\n *\n * Idempotent: Returns stored selection if already computed for today.\n */\n\nimport { GoogleSpreadsheet, GoogleSpreadsheetRow } from \"npm:google-spreadsheet\";\nimport {\n findMatchingName,\n FORM_COL_DATE,\n FORM_COL_NAME,\n formatDate,\n getAuthClient,\n MAIN_SHEET_ID,\n parseSheetDate,\n SHEET_ID,\n SHEET_TABS,\n TE_STUDENT_FORMS_SHEET_ID,\n STUDENT_FORMS_TAB,\n} from \"./utils.tsx\";\n\n// Local aliases for readability\nconst TABS = {\n ROSTER: SHEET_TABS.TE_ROSTER,\n ONCALL_NEXT: SHEET_TABS.TE_ON_CALL_NEXT, // Clean view for Sarah\n ONCALL_HISTORY: SHEET_TABS.TE_ON_CALL_HISTORY, // Archive\n ONCALL_OLD: SHEET_TABS.TE_ON_CALL, // Deprecated legacy tab\n COLD_CALL_LOG: SHEET_TABS.COLD_CALL_LOG,\n} as const;\n\ninterface RosterStudent {\n student_name: string;\n last_name: string;\n pool: \"mon\" | \"wed\";\n cold_call_count: number;\n last_called: string;\n absences: number;\n concerning_events: number;\n}\n\ninterface OnCallEntry {\n date: string;\n student_name: string;\n notified_at: string;\n}\n\ninterface SelfReport {\n timestamp: string;\n name: string;\n date: string;\n type: string;\n}\n\n/**\n * Gets day of week pool. Returns \"mon\" or \"wed\", or null if neither.\n * @param date - Optional date to check (defaults to today)\n */\nfunction getDayPool(date: Date = new Date()): \"mon\" | \"wed\" | null {\n const day = date.getDay();\n if (day === 1) return \"mon\";\n if (day === 3) return \"wed\";\n return null;\n}\n\n/**\n * Fisher-Yates shuffle for randomizing ties.\n */\nfunction shuffle(array: T[]): T[] {\n const result = [...array];\n for (let i = result.length - 1; i > 0; i--) {\n const j = Math.floor(Math.random() * (i + 1));\n [result[i], result[j]] = [result[j], result[i]];\n }\n return result;\n}\n\n/**\n * Main selection logic.\n * @param sheetId - Google Sheet ID\n * @param forceRecompute - If true, recompute even if cached\n * @param mockDate - Optional date to use instead of today (for testing)\n * @param forcePool - Optional pool override (for rescheduled classes on non-standard days)\n */\nasync function selectOnCall(\n sheetId: string,\n forceRecompute = false,\n mockDate?: Date,\n forcePool?: \"mon\" | \"wed\",\n): Promise<{\n success: boolean;\n date: string;\n pool: \"mon\" | \"wed\" | null;\n selected: Array<{ name: string; cold_call_count: number }>;\n fromCache: boolean;\n excluded: string[];\n error?: string;\n}> {\n const effectiveDate = mockDate || new Date();\n const today = formatDate(effectiveDate);\n\n // Use forced pool if provided, otherwise compute from day of week\n const pool = forcePool || getDayPool(effectiveDate);\n\n // Validate pool is available\n if (!pool) {\n return {\n success: false,\n date: today,\n pool: null,\n selected: [],\n fromCache: false,\n excluded: [],\n error: \"Not a standard class day (Mon/Wed). Use ?pool=mon or ?pool=wed to specify which student pool.\",\n };\n }\n\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(sheetId, auth);\n await doc.loadInfo();\n\n // Get or create \"Next\" tab (clean view for Sarah)\n let onCallNextSheet = doc.sheetsByTitle[TABS.ONCALL_NEXT];\n if (!onCallNextSheet) {\n onCallNextSheet = await doc.addSheet({\n title: TABS.ONCALL_NEXT,\n headerValues: [\"date\", \"student_name\", \"cold_calls\", \"notified_at\"],\n });\n }\n\n // Get or create \"History\" tab (archive)\n let onCallHistorySheet = doc.sheetsByTitle[TABS.ONCALL_HISTORY];\n if (!onCallHistorySheet) {\n onCallHistorySheet = await doc.addSheet({\n title: TABS.ONCALL_HISTORY,\n headerValues: [\"date\", \"student_name\", \"cold_calls\", \"notified_at\"],\n });\n }\n\n // Check if today already has selections (idempotency - check Next tab)\n await onCallNextSheet.loadHeaderRow();\n const existingRows = await onCallNextSheet.getRows();\n const todaySelections = existingRows.filter(\n (row) => row.get(\"date\") === today,\n );\n\n if (todaySelections.length > 0 && !forceRecompute) {\n return {\n success: true,\n date: today,\n pool,\n selected: todaySelections.map((row) => ({\n name: row.get(\"student_name\"),\n cold_call_count: parseInt(row.get(\"cold_calls\") || \"0\", 10),\n })),\n fromCache: true,\n excluded: [],\n };\n }\n\n // Get roster\n const rosterSheet = doc.sheetsByTitle[TABS.ROSTER];\n if (!rosterSheet) {\n return {\n success: false,\n date: today,\n pool,\n selected: [],\n fromCache: false,\n excluded: [],\n error: `Tab \"${TABS.ROSTER}\" not found. Please create roster first.`,\n };\n }\n\n await rosterSheet.loadHeaderRow();\n const rosterRows = await rosterSheet.getRows();\n\n // Parse roster and filter to today's pool (dedupe by name)\n const seen = new Set();\n const roster: RosterStudent[] = rosterRows\n .map((row) => ({\n student_name: row.get(\"student_name\") || \"\",\n last_name: row.get(\"last_name\") || \"\",\n pool: (row.get(\"pool\") || \"\").toLowerCase() as \"mon\" | \"wed\",\n cold_call_count: parseInt(row.get(\"cold_call_count\") || \"0\", 10),\n last_called: row.get(\"last_called\") || \"\",\n absences: parseInt(row.get(\"absences\") || \"0\", 10),\n concerning_events: parseInt(row.get(\"concerning_events\") || \"0\", 10),\n }))\n .filter((s) => {\n if (!s.student_name || s.pool !== pool) return false;\n if (seen.has(s.student_name)) return false;\n seen.add(s.student_name);\n return true;\n });\n\n // Get today's self-reports from student forms spreadsheet\n const selfReportedTodayNames: string[] = [];\n\n try {\n const studentFormsDoc = new GoogleSpreadsheet(TE_STUDENT_FORMS_SHEET_ID, auth);\n await studentFormsDoc.loadInfo();\n const selfReportsSheet = studentFormsDoc.sheetsByTitle[STUDENT_FORMS_TAB];\n\n if (selfReportsSheet) {\n await selfReportsSheet.loadHeaderRow();\n const selfReportRows = await selfReportsSheet.getRows();\n\n // Find students who submitted same-day reports\n for (const row of selfReportRows) {\n const reportDate = row.get(FORM_COL_DATE);\n if (reportDate) {\n const parsedDate = parseSheetDate(reportDate);\n if (parsedDate === today) {\n const name = row.get(FORM_COL_NAME);\n if (name) selfReportedTodayNames.push(name.trim());\n }\n }\n }\n }\n } catch (err) {\n console.warn(\"Could not read student self-reports:\", err);\n // Continue without exclusions - better than failing entirely\n }\n\n // Filter out excluded students (fuzzy match against self-reports)\n const eligible = roster.filter((s) => {\n // Check if any self-report fuzzy-matches this roster name\n const match = findMatchingName(s.student_name, selfReportedTodayNames);\n return !match.matched; // Keep only students with NO matching self-report\n });\n\n // Sort by cold_call_count (ascending) with random shuffle for ties\n const grouped = new Map();\n for (const student of eligible) {\n const count = student.cold_call_count;\n if (!grouped.has(count)) grouped.set(count, []);\n grouped.get(count)!.push(student);\n }\n\n // Sort groups by count, shuffle within each group\n const sortedCounts = [...grouped.keys()].sort((a, b) => a - b);\n const sorted: RosterStudent[] = [];\n for (const count of sortedCounts) {\n sorted.push(...shuffle(grouped.get(count)!));\n }\n\n // Take top 6\n const selected = sorted.slice(0, 6);\n const selectedWithCounts = selected.map((s) => ({\n name: s.student_name,\n cold_call_count: s.cold_call_count,\n }));\n\n // Clear the \"Next\" tab completely (Sarah wants a clean view)\n await onCallHistorySheet.loadHeaderRow();\n const allNextRows = await onCallNextSheet.getRows();\n for (const row of allNextRows) {\n await row.delete();\n }\n\n // Write new selections to BOTH tabs\n const now = new Date().toISOString();\n for (const item of selectedWithCounts) {\n // Write to \"Next\" tab (clean view)\n await onCallNextSheet.addRow({\n date: today,\n student_name: item.name,\n cold_calls: item.cold_call_count,\n notified_at: now,\n });\n\n // ALSO write to \"History\" tab (archive - append only)\n await onCallHistorySheet.addRow({\n date: today,\n student_name: item.name,\n cold_calls: item.cold_call_count,\n notified_at: now,\n });\n }\n\n return {\n success: true,\n date: today,\n pool,\n selected: selectedWithCounts,\n fromCache: false, // Just computed and stored\n excluded: selfReportedTodayNames,\n };\n } catch (error) {\n return {\n success: false,\n date: today,\n pool,\n selected: [],\n fromCache: false,\n excluded: [],\n error: error instanceof Error ? error.message : String(error),\n };\n }\n}\n\n// HTTP endpoint\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const sheetId = url.searchParams.get(\"sheetId\") || SHEET_ID;\n const forceRecompute = url.searchParams.get(\"force\") === \"true\";\n\n // Parse pool param for rescheduled classes (e.g., ?pool=mon for A-L students)\n const poolParam = url.searchParams.get(\"pool\");\n const forcePool = poolParam === \"mon\" || poolParam === \"wed\" ? poolParam : undefined;\n\n // Parse mockDate for testing (e.g., ?mockDate=2026-01-12)\n // Allows tests to run on any day by pretending it's a specific date\n let mockDate: Date | undefined;\n const mockDateParam = url.searchParams.get(\"mockDate\");\n if (mockDateParam) {\n const parsed = new Date(mockDateParam);\n if (!isNaN(parsed.getTime())) {\n mockDate = parsed;\n }\n }\n\n const result = await selectOnCall(sheetId, forceRecompute, mockDate, forcePool);\n\n return Response.json(result, {\n status: result.success ? 200 : 400,\n });\n}\n",[195],"2881aefc-0c2e-11f1-9217-42dde27851f2","oncall-notify.cron.tsx","e4ba617c-ef4b-11f0-be8e-42dde27851f2",153,["D",1771352781251],"/**\n * T&E On-Call Notification\n * Runs 3 PM EST Mon/Wed (8 PM UTC)\n * Selects on-call students and emails the list to professor.\n */\n\nimport { email } from \"https://esm.town/v/std/email\";\nimport { EMAIL_FOOTER } from \"./utils.tsx\";\n\n// Import the selection function from our HTTP val\n// Val.town allows importing from other vals in the same project\nconst ONCALL_ENDPOINT = \"https://sahar--78f39bfeef4911f0a9a742dde27851f2.web.val.run\";\n\nexport default async function() {\n // Val.town free tier: email() always sends to account owner.\n // NOTIFICATION_EMAIL serves as an opt-in flag — if not set, skip silently.\n if (!Deno.env.get(\"NOTIFICATION_EMAIL\")) {\n console.log(\"NOTIFICATION_EMAIL not set — skipping notification.\");\n return;\n }\n\n // Call the oncall-selection endpoint\n let result;\n try {\n const response = await fetch(ONCALL_ENDPOINT);\n result = await response.json();\n } catch (fetchError) {\n await email({\n // Val.town free tier: emails go to account owner\n subject: \"[Law Tracker] On-Call Selection Failed - Network Error\",\n text: `Failed to reach selection endpoint:\\n\\n${fetchError}\\n\\nEndpoint: ${ONCALL_ENDPOINT}` + EMAIL_FOOTER,\n });\n throw fetchError;\n }\n\n if (!result.success) {\n // Send error notification\n await email({\n // Val.town free tier: emails go to account owner\n subject: \"[Law Tracker] On-Call Selection Failed\",\n text: `Error selecting on-call students:\\n\\n${result.error}\\n\\nDate: ${result.date}` + EMAIL_FOOTER,\n });\n return;\n }\n\n // Format the student list with cold call counts\n const studentList = result.selected\n .map((s: { name: string; cold_call_count: number }, i: number) =>\n `${i + 1}. ${s.name} (${s.cold_call_count} cold calls)`)\n .join(\"\\n\");\n\n const emailBody = `Today's on-call students for T&E:\n\n${studentList}\n\nPool: ${result.pool === \"mon\" ? \"Monday pool\" : \"Wednesday pool\"}\n${result.excluded.length > 0 ? `\\nExcluded (same-day report): ${result.excluded.join(\", \")}` : \"\"}\n`;\n\n await email({\n // Val.town free tier: emails go to account owner\n subject: `[T&E] Trusts & Estates On-Call - ${result.date}`,\n text: emailBody + EMAIL_FOOTER,\n });\n\n console.log(`Sent on-call notification for ${result.date}`);\n}\n",[203],"8d266b3e-f006-11f0-82df-42dde27851f2","main.http.tsx","c49a6e2c-ef47-11f0-9984-42dde27851f2",80,["D",1768257137698],5,"/**\n * Google Sheets Connection Test\n *\n * Tests that we can read from a Google Sheet using the service account.\n * Useful for verifying credentials are configured correctly.\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { getAuthClient, SHEET_ID } from \"./utils.tsx\";\n\n/**\n * Reads the first sheet and returns first few rows as a test.\n */\nasync function testSheetRead(sheetId: string): Promise<{\n success: boolean;\n title?: string;\n sheetName?: string;\n rowCount?: number;\n headers?: string[];\n sample?: Record[];\n error?: string;\n}> {\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(sheetId, auth);\n\n await doc.loadInfo();\n\n const sheet = doc.sheetsByIndex[0];\n await sheet.loadHeaderRow();\n const rows = await sheet.getRows({ limit: 5 });\n\n return {\n success: true,\n title: doc.title,\n sheetName: sheet.title,\n rowCount: sheet.rowCount,\n headers: sheet.headerValues,\n sample: rows.map((row) => row.toObject()),\n };\n } catch (error) {\n return {\n success: false,\n error: error instanceof Error ? error.message : String(error),\n };\n }\n}\n\n// HTTP endpoint for testing\nexport default async function (req: Request): Promise {\n // Allow overriding sheet ID via query param for testing\n const url = new URL(req.url);\n const sheetId = url.searchParams.get(\"sheetId\") || SHEET_ID;\n\n const result = await testSheetRead(sheetId);\n\n return Response.json(result, {\n status: result.success ? 200 : 500,\n });\n}\n",[210],"2888478a-0c2e-11f1-93bf-42dde27851f2","lc-report-prompt.cron.tsx","e4ab0ea2-ef4b-11f0-b248-42dde27851f2",154,["D",1771352781303],"/**\n * Law & Capitalism Post-Class Report Prompt\n * Runs 12 PM EST Mon/Wed (5 PM UTC)\n * Reminds professor to submit post-class report.\n */\n\nimport { email } from \"https://esm.town/v/std/email\";\nimport { EMAIL_FOOTER, formatDate } from \"./utils.tsx\";\n\nexport default async function() {\n // Val.town free tier: email() always sends to account owner.\n // NOTIFICATION_EMAIL serves as an opt-in flag — if not set, skip silently.\n if (!Deno.env.get(\"NOTIFICATION_EMAIL\")) {\n console.log(\"NOTIFICATION_EMAIL not set — skipping notification.\");\n return;\n }\n\n const today = formatDate(new Date());\n\n await email({\n // Val.town free tier: emails go to account owner\n subject: `[L&C] Law & Capitalism Report - ${today}`,\n text: `Hi Sarah,\n\nTime to submit your post-class report for Law & Capitalism.\n\nPlease note:\n- Who was absent\n- How the on-call students performed (great/normal/bad)\n- Any students who refused when called\n\nUse the reporting form: https://sahar--ab2d2176f16511f0a75a42dde27851f2.web.val.run\n\nThanks!` + EMAIL_FOOTER,\n });\n\n console.log(`Sent L&C report prompt for ${today}`);\n}\n",[218],"2865cd0e-0c2e-11f1-93bf-42dde27851f2","lc-oncall-selection.http.tsx","2b3b7afa-ef56-11f0-bec6-42dde27851f2",148,["D",1771352781086],10,"/**\n * L&C On-Call Selection\n *\n * Unlike T&E (algorithmic), L&C uses PRESET assignments from syllabus.\n * Just looks up today's date in the assignments tab and returns those students.\n *\n * Idempotent: Returns stored selection if already fetched for today.\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport {\n findMatchingName,\n FORM_COL_DATE,\n FORM_COL_NAME,\n formatDate,\n getAuthClient,\n LC_STUDENT_FORMS_SHEET_ID,\n parseSheetDate,\n SHEET_ID,\n SHEET_TABS,\n STUDENT_FORMS_TAB,\n} from \"./utils.tsx\";\n\n// Local aliases for readability\nconst TABS = {\n ASSIGNMENTS: SHEET_TABS.LC_SCHEDULE,\n ONCALL_NEXT: SHEET_TABS.LC_ON_CALL_NEXT, // Clean view for Sarah\n ONCALL_HISTORY: SHEET_TABS.LC_ON_CALL_HISTORY, // Archive\n ONCALL_OLD: SHEET_TABS.LC_ON_CALL, // Deprecated legacy tab\n} as const;\n\n/**\n * Main selection logic - just lookup, no algorithm.\n * @param sheetId - Google Sheet ID\n * @param mockDate - Optional date to use instead of today (for testing)\n */\nasync function selectOnCall(\n sheetId: string,\n mockDate?: Date,\n): Promise<{\n success: boolean;\n date: string;\n selected: string[];\n fromCache: boolean;\n error?: string;\n warning?: string;\n warnings?: string[]; // Self-reported absence warnings\n}> {\n const effectiveDate = mockDate || new Date();\n const today = formatDate(effectiveDate);\n\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(sheetId, auth);\n await doc.loadInfo();\n\n // Get or create \"Next\" tab (clean view for Sarah)\n let onCallNextSheet = doc.sheetsByTitle[TABS.ONCALL_NEXT];\n if (!onCallNextSheet) {\n onCallNextSheet = await doc.addSheet({\n title: TABS.ONCALL_NEXT,\n headerValues: [\"date\", \"student_name\", \"notified_at\"],\n });\n }\n\n // Get or create \"History\" tab (archive)\n let onCallHistorySheet = doc.sheetsByTitle[TABS.ONCALL_HISTORY];\n if (!onCallHistorySheet) {\n onCallHistorySheet = await doc.addSheet({\n title: TABS.ONCALL_HISTORY,\n headerValues: [\"date\", \"student_name\", \"notified_at\"],\n });\n }\n\n // Check cache first (idempotency - check Next tab)\n await onCallNextSheet.loadHeaderRow();\n const cachedRows = await onCallNextSheet.getRows();\n const todayCache = cachedRows.filter((row) => row.get(\"date\") === today);\n\n if (todayCache.length > 0) {\n return {\n success: true,\n date: today,\n selected: todayCache.map((row) => row.get(\"student_name\")),\n fromCache: true,\n };\n }\n\n // Look up today's assignments\n const assignmentsSheet = doc.sheetsByTitle[TABS.ASSIGNMENTS];\n if (!assignmentsSheet) {\n return {\n success: false,\n date: today,\n selected: [],\n fromCache: false,\n error: `Tab \"${TABS.ASSIGNMENTS}\" not found. Create it with columns: date, student_name`,\n };\n }\n\n await assignmentsSheet.loadHeaderRow();\n const assignmentRows = await assignmentsSheet.getRows();\n\n // Find students assigned for today (deduplicate in case of duplicate rows)\n const seen = new Set();\n const todayStudents: string[] = [];\n for (const row of assignmentRows) {\n const assignDate = row.get(\"date\");\n const parsedDate = parseSheetDate(assignDate);\n if (parsedDate === today) {\n const name = row.get(\"student_name\");\n if (name && !seen.has(name)) {\n seen.add(name);\n todayStudents.push(name);\n }\n }\n }\n\n if (todayStudents.length === 0) {\n return {\n success: true,\n date: today,\n selected: [],\n fromCache: false,\n warning: `No students assigned for ${today} in L&C Assignments`,\n };\n }\n\n // Validate schedule names against roster\n const rosterNames: string[] = [];\n const rosterSheet = doc.sheetsByTitle[SHEET_TABS.LC_ROSTER];\n if (rosterSheet) {\n await rosterSheet.loadHeaderRow();\n const rosterRows = await rosterSheet.getRows();\n for (const row of rosterRows) {\n const name = row.get(\"student_name\");\n if (name) rosterNames.push(name);\n }\n }\n\n // Check each scheduled student against roster\n const scheduleWarnings: string[] = [];\n for (const scheduled of todayStudents) {\n const match = findMatchingName(scheduled, rosterNames);\n if (!match.matched) {\n scheduleWarnings.push(`\"${scheduled}\" not found in roster (closest: ${match.closestCandidate})`);\n } else if (match.matchType === \"fuzzy\") {\n scheduleWarnings.push(`\"${scheduled}\" fuzzy-matched to \"${match.rosterName}\" - consider fixing schedule`);\n }\n }\n\n // Check for self-reported absences\n const absenceWarnings: string[] = [];\n\n try {\n const studentFormsDoc = new GoogleSpreadsheet(LC_STUDENT_FORMS_SHEET_ID, auth);\n await studentFormsDoc.loadInfo();\n const selfReportsSheet = studentFormsDoc.sheetsByTitle[STUDENT_FORMS_TAB];\n\n if (selfReportsSheet) {\n await selfReportsSheet.loadHeaderRow();\n const selfReportRows = await selfReportsSheet.getRows();\n\n // Collect today's self-reports\n const todayReportNames: string[] = [];\n for (const row of selfReportRows) {\n const reportDate = row.get(FORM_COL_DATE);\n if (reportDate) {\n const parsedDate = parseSheetDate(reportDate);\n if (parsedDate === today) {\n const name = row.get(FORM_COL_NAME);\n if (name) todayReportNames.push(name.trim());\n }\n }\n }\n\n // Check which scheduled students have matching self-reports\n for (const scheduled of todayStudents) {\n const match = findMatchingName(scheduled, todayReportNames);\n if (match.matched) {\n absenceWarnings.push(`${scheduled} submitted absence report for today`);\n }\n }\n }\n } catch (err) {\n console.warn(\"Failed to check self-reports:\", err);\n }\n\n // Clear the \"Next\" tab completely (Sarah wants a clean view)\n await onCallHistorySheet.loadHeaderRow();\n const allNextRows = await onCallNextSheet.getRows();\n for (const row of allNextRows) {\n await row.delete();\n }\n\n // Store in BOTH tabs\n const now = new Date().toISOString();\n for (const name of todayStudents) {\n // Write to \"Next\" tab (clean view)\n await onCallNextSheet.addRow({\n date: today,\n student_name: name,\n notified_at: now,\n });\n\n // ALSO write to \"History\" tab (archive - append only)\n await onCallHistorySheet.addRow({\n date: today,\n student_name: name,\n notified_at: now,\n });\n }\n\n const allWarnings = [...scheduleWarnings, ...absenceWarnings];\n return {\n success: true,\n date: today,\n selected: todayStudents,\n fromCache: false,\n warnings: allWarnings.length > 0 ? allWarnings : undefined,\n };\n } catch (error) {\n return {\n success: false,\n date: today,\n selected: [],\n fromCache: false,\n error: error instanceof Error ? error.message : String(error),\n };\n }\n}\n\n// HTTP endpoint\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const sheetId = url.searchParams.get(\"sheetId\") || SHEET_ID;\n\n // Parse mockDate for testing (e.g., ?mockDate=2026-01-12)\n // Allows tests to run on any day by pretending it's a specific date\n let mockDate: Date | undefined;\n const mockDateParam = url.searchParams.get(\"mockDate\");\n if (mockDateParam) {\n const parsed = new Date(mockDateParam);\n if (!isNaN(parsed.getTime())) {\n mockDate = parsed;\n }\n }\n\n const result = await selectOnCall(sheetId, mockDate);\n\n return Response.json(result, {\n status: result.success ? 200 : 400,\n });\n}\n",[225],"287b3900-0c2e-11f1-bb33-42dde27851f2","lc-oncall-notify.cron.tsx","2b409878-ef56-11f0-bf11-42dde27851f2",152,["D",1771352781152],6,"/**\n * L&C On-Call Notification\n * Runs 8 AM EST Mon/Wed (1 PM UTC)\n * Fetches preset on-call students and emails the list to professor.\n */\n\nimport { email } from \"https://esm.town/v/std/email\";\nimport { EMAIL_FOOTER } from \"./utils.tsx\";\n\nconst LC_ONCALL_ENDPOINT = \"https://sahar--2b3b7afaef5611f0bec642dde27851f2.web.val.run\";\n\nexport default async function () {\n // Val.town free tier: email() always sends to account owner.\n // NOTIFICATION_EMAIL serves as an opt-in flag — if not set, skip silently.\n if (!Deno.env.get(\"NOTIFICATION_EMAIL\")) {\n console.log(\"NOTIFICATION_EMAIL not set — skipping notification.\");\n return;\n }\n\n // Call the selection endpoint\n let result;\n try {\n const response = await fetch(LC_ONCALL_ENDPOINT);\n result = await response.json();\n } catch (fetchError) {\n await email({\n // Val.town free tier: emails go to account owner\n subject: \"[Law Tracker] L&C On-Call Selection Failed - Network Error\",\n text: `Failed to reach selection endpoint:\\n\\n${fetchError}\\n\\nEndpoint: ${LC_ONCALL_ENDPOINT}` + EMAIL_FOOTER,\n });\n throw fetchError;\n }\n\n if (!result.success) {\n await email({\n // Val.town free tier: emails go to account owner\n subject: \"[Law Tracker] L&C On-Call Selection Failed\",\n text: `Error fetching on-call students:\\n\\n${result.error}\\n\\nDate: ${result.date}` + EMAIL_FOOTER,\n });\n return;\n }\n\n if (result.selected.length === 0) {\n await email({\n // Val.town free tier: emails go to account owner\n subject: `[L&C] Law & Capitalism - No On-Call Students - ${result.date}`,\n text: `No students are assigned for on-call today in the L&C Assignments sheet.\\n\\nIs this a class day? Check the assignments if needed.` + EMAIL_FOOTER,\n });\n return;\n }\n\n // Format the student list\n const studentList = result.selected\n .map((name: string, i: number) => `${i + 1}. ${name}`)\n .join(\"\\n\");\n\n const emailBody = `Today's on-call students for Law & Capitalism:\n\n${studentList}\n\n${result.fromCache ? \"(Retrieved from cache)\" : \"(Fresh from syllabus assignments)\"}\n`;\n\n await email({\n // Val.town free tier: emails go to account owner\n subject: `[L&C] Law & Capitalism On-Call - ${result.date}`,\n text: emailBody + EMAIL_FOOTER,\n });\n\n console.log(`Sent L&C on-call notification for ${result.date}`);\n}\n",[233],"287124c4-0c2e-11f1-8b8e-42dde27851f2","data-integrity-check.http.tsx","b1fc0ba6-f08f-11f0-b802-42dde27851f2",150,["D",1771352781121],"/**\n * Data Integrity Check Endpoint\n *\n * Verifies that Cold Call Log totals match roster counter values.\n * Useful for detecting drift from manual edits or bugs.\n * Reports mismatches; Sarah decides what to do (roster is source of truth).\n *\n * GET / - Returns mismatches (empty array = data is consistent)\n *\n * Response format:\n * {\n * \"checked\": 50,\n * \"mismatches\": [\n * { \"student_name\": \"...\", \"roster\": \"te\", \"log_absences\": 3, \"roster_absences\": 2, ... }\n * ]\n * }\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { getAuthClient, isConcerningEvent, SHEET_ID, SHEET_TABS } from \"./utils.tsx\";\n\nconst TABS = {\n TE_ROSTER: SHEET_TABS.TE_ROSTER,\n LC_ROSTER: SHEET_TABS.LC_ROSTER,\n COLD_CALL_LOG: SHEET_TABS.COLD_CALL_LOG,\n} as const;\n\ninterface StudentLogCounts {\n absences: number;\n concerning: number;\n coldCalls: number;\n}\n\ninterface Mismatch {\n student_name: string;\n roster: \"te\" | \"lc\";\n log_absences: number;\n roster_absences: number;\n log_concerning: number;\n roster_concerning: number;\n log_cold_calls: number;\n roster_cold_calls: number;\n}\n\n/**\n * Aggregate Cold Call Log entries per student.\n */\nfunction aggregateLogByStudent(\n logRows: Array<{ get: (key: string) => string }>,\n): Map {\n const counts = new Map();\n\n for (const row of logRows) {\n const studentName = row.get(\"student_name\");\n const result = row.get(\"result\");\n const performance = row.get(\"performance\");\n\n if (!studentName) continue;\n\n if (!counts.has(studentName)) {\n counts.set(studentName, { absences: 0, concerning: 0, coldCalls: 0 });\n }\n\n const entry = counts.get(studentName)!;\n\n // Only count actual cold calls: student was called on and responded (or refused)\n if (result === \"completed\" || result === \"refused\") {\n entry.coldCalls++;\n }\n\n // Count absences (both announced and unannounced)\n if (result === \"absent\" || result === \"absent-announced\") {\n entry.absences++;\n }\n\n // Count concerning events using same logic as post-class-report\n if (isConcerningEvent(result as \"completed\" | \"absent\" | \"refused\" | \"unprepared\", performance as \"great\" | \"normal\" | \"bad\" | \"self-reported\" | undefined)) {\n entry.concerning++;\n }\n }\n\n return counts;\n}\n\nexport default async function (req: Request): Promise {\n try {\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n // Load Cold Call Log\n const logSheet = doc.sheetsByTitle[TABS.COLD_CALL_LOG];\n if (!logSheet) {\n return Response.json({ error: \"Cold Call Log not found\" }, { status: 500 });\n }\n await logSheet.loadHeaderRow();\n const logRows = await logSheet.getRows();\n\n // Aggregate log entries by student\n const logCounts = aggregateLogByStudent(logRows);\n\n // Load both rosters\n const mismatches: Mismatch[] = [];\n let totalChecked = 0;\n\n for (const [rosterTab, rosterType] of [\n [TABS.TE_ROSTER, \"te\"],\n [TABS.LC_ROSTER, \"lc\"],\n ] as const) {\n const rosterSheet = doc.sheetsByTitle[rosterTab];\n if (!rosterSheet) continue;\n\n await rosterSheet.loadHeaderRow();\n const rosterRows = await rosterSheet.getRows();\n\n for (const row of rosterRows) {\n const studentName = row.get(\"student_name\");\n if (!studentName) continue;\n\n totalChecked++;\n\n const rosterAbsences = parseInt(row.get(\"absences\") || \"0\", 10);\n const rosterConcerning = parseInt(row.get(\"concerning_events\") || \"0\", 10);\n const rosterColdCalls = parseInt(row.get(\"cold_call_count\") || \"0\", 10);\n\n const logEntry = logCounts.get(studentName) || { absences: 0, concerning: 0, coldCalls: 0 };\n\n // Check for mismatches\n const hasMismatch =\n logEntry.absences !== rosterAbsences ||\n logEntry.concerning !== rosterConcerning ||\n logEntry.coldCalls !== rosterColdCalls;\n\n if (hasMismatch) {\n mismatches.push({\n student_name: studentName,\n roster: rosterType,\n log_absences: logEntry.absences,\n roster_absences: rosterAbsences,\n log_concerning: logEntry.concerning,\n roster_concerning: rosterConcerning,\n log_cold_calls: logEntry.coldCalls,\n roster_cold_calls: rosterColdCalls,\n });\n }\n }\n }\n\n return Response.json({\n checked: totalChecked,\n mismatches,\n });\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 },\n );\n }\n}\n",[241],"4cd81d84-f7a4-11f0-aa79-42dde27851f2","assign-pools.http.tsx","e42ec86a-f0a4-11f0-944b-42dde27851f2",145,["D",1769094548113],1,"/**\n * Pool Auto-Assignment Endpoint\n *\n * Calculates optimal pool distribution based on student last names.\n * Finds pivot letter that gives ~50/50 split between Monday and Wednesday.\n *\n * Usage:\n * GET /assign-pools - Calculate and assign pools\n * GET /assign-pools?force=true - Reset all pools (ignore manual overrides)\n * GET /assign-pools?dryRun=true - Show what would happen without making changes\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { getAuthClient, SHEET_ID, SHEET_TABS } from \"./utils.tsx\";\n\n/**\n * Calculate optimal split point for ~50/50 distribution.\n */\nfunction calculateOptimalSplit(lastNames: string[]): {\n pivot: string;\n monCount: number;\n wedCount: number;\n} {\n if (lastNames.length === 0) {\n return { pivot: \"m\", monCount: 0, wedCount: 0 };\n }\n\n // Normalize and sort\n const sorted = lastNames\n .map((n) => n.toLowerCase().trim())\n .filter((n) => n.length > 0)\n .sort();\n\n // Try each letter, find the one that minimizes imbalance\n const alphabet = \"abcdefghijklmnopqrstuvwxyz\";\n let bestPivot = \"m\";\n let bestDiff = Infinity;\n let bestMon = 0;\n let bestWed = 0;\n\n for (const letter of alphabet) {\n let mon = 0;\n let wed = 0;\n for (const name of sorted) {\n if (name[0] <= letter) {\n mon++;\n } else {\n wed++;\n }\n }\n const diff = Math.abs(mon - wed);\n if (diff < bestDiff) {\n bestDiff = diff;\n bestPivot = letter;\n bestMon = mon;\n bestWed = wed;\n }\n }\n\n return { pivot: bestPivot, monCount: bestMon, wedCount: bestWed };\n}\n\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const force = url.searchParams.get(\"force\") === \"true\";\n const dryRun = url.searchParams.get(\"dryRun\") === \"true\";\n\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n const rosterSheet = doc.sheetsByTitle[SHEET_TABS.TE_ROSTER];\n if (!rosterSheet) {\n return Response.json({ error: \"T&E Roster not found\" }, { status: 404 });\n }\n\n await rosterSheet.loadHeaderRow();\n const rosterRows = await rosterSheet.getRows();\n\n // Extract last names for pivot calculation\n const lastNames = rosterRows\n .map((row) => row.get(\"last_name\") || \"\")\n .filter((n) => n.length > 0);\n\n // Calculate optimal split\n const { pivot, monCount, wedCount } = calculateOptimalSplit(lastNames);\n\n // Store pivot in Config tab for other files to read\n let configSheet = doc.sheetsByTitle[SHEET_TABS.CONFIG];\n if (!configSheet) {\n configSheet = await doc.addSheet({\n title: SHEET_TABS.CONFIG,\n headerValues: [\"key\", \"value\", \"updated_at\"],\n });\n }\n\n await configSheet.loadHeaderRow();\n const configRows = await configSheet.getRows();\n\n // Find existing pool_pivot row or create new\n const pivotRow = configRows.find((r) => r.get(\"key\") === \"pool_pivot\");\n const pivotValue = pivot.toUpperCase(); // Store as uppercase for display\n\n if (!dryRun) {\n if (pivotRow) {\n pivotRow.set(\"value\", pivotValue);\n pivotRow.set(\"updated_at\", new Date().toISOString());\n await pivotRow.save();\n } else {\n await configSheet.addRow({\n key: \"pool_pivot\",\n value: pivotValue,\n updated_at: new Date().toISOString(),\n });\n }\n }\n\n // Track changes\n let assigned = 0;\n let preserved = 0;\n let unchanged = 0;\n const changes: Array<{ name: string; from: string; to: string }> = [];\n\n for (const row of rosterRows) {\n const name = row.get(\"student_name\") || \"\";\n const lastName = (row.get(\"last_name\") || \"\").toLowerCase().trim();\n const currentPool = (row.get(\"pool\") || \"\").toLowerCase();\n\n if (!lastName) continue;\n\n // Calculate what pool they should be in\n const computedPool = lastName[0] <= pivot ? \"mon\" : \"wed\";\n\n // Decide whether to update\n if (!currentPool) {\n // Empty - assign\n if (!dryRun) {\n row.set(\"pool\", computedPool);\n await row.save();\n }\n changes.push({ name, from: \"(empty)\", to: computedPool });\n assigned++;\n } else if (currentPool === computedPool) {\n // Already correct\n unchanged++;\n } else if (force) {\n // Different but force mode - override\n if (!dryRun) {\n row.set(\"pool\", computedPool);\n await row.save();\n }\n changes.push({ name, from: currentPool, to: computedPool });\n assigned++;\n } else {\n // Different and not force - preserve manual override\n preserved++;\n }\n }\n\n return Response.json({\n success: true,\n dryRun,\n pivot,\n mondayPool: monCount,\n wednesdayPool: wedCount,\n totalStudents: lastNames.length,\n assigned,\n preserved,\n unchanged,\n changes: dryRun ? changes : undefined, // Only show changes in dry run\n });\n}\n",[248],"2e59a562-f0ab-11f0-b739-42dde27851f2","add-roster-columns.http.tsx","f22babda-f0aa-11f0-a63a-42dde27851f2",116,["D",1768327845603],"/**\n * One-time migration: Add missing roster columns\n * Adds last_absence_warning and last_concerning_warning to both rosters.\n *\n * Safe to run multiple times - only adds columns if missing.\n *\n * Usage: GET /add-roster-columns?dryRun=true (preview)\n * GET /add-roster-columns (apply)\n */\n\nimport { GoogleSpreadsheet } from \"npm:google-spreadsheet\";\nimport { getAuthClient, SHEET_ID, SHEET_TABS } from \"./utils.tsx\";\n\nconst REQUIRED_COLUMNS = [\"last_absence_warning\", \"last_concerning_warning\"];\n\nexport default async function handler(req: Request): Promise {\n const url = new URL(req.url);\n const dryRun = url.searchParams.get(\"dryRun\") === \"true\";\n\n const auth = getAuthClient();\n const doc = new GoogleSpreadsheet(SHEET_ID, auth);\n await doc.loadInfo();\n\n const results: string[] = [];\n const rosters = [SHEET_TABS.TE_ROSTER, SHEET_TABS.LC_ROSTER];\n\n for (const rosterName of rosters) {\n const sheet = doc.sheetsByTitle[rosterName];\n if (!sheet) {\n results.push(`❌ ${rosterName}: Sheet not found`);\n continue;\n }\n\n await sheet.loadHeaderRow();\n const currentHeaders = sheet.headerValues;\n const missingColumns = REQUIRED_COLUMNS.filter(col => !currentHeaders.includes(col));\n\n if (missingColumns.length === 0) {\n results.push(`✅ ${rosterName}: All columns present`);\n continue;\n }\n\n if (dryRun) {\n results.push(`🔄 ${rosterName}: Would add columns: ${missingColumns.join(\", \")}`);\n } else {\n // Add missing columns by extending header row\n const newHeaders = [...currentHeaders, ...missingColumns];\n // Resize sheet if needed\n if (sheet.columnCount < newHeaders.length) {\n await sheet.resize({ columnCount: newHeaders.length });\n }\n await sheet.setHeaderRow(newHeaders);\n results.push(`✅ ${rosterName}: Added columns: ${missingColumns.join(\", \")}`);\n }\n }\n\n const summary = dryRun ? \"DRY RUN - no changes made\" : \"Migration complete\";\n\n return new Response(\n JSON.stringify({ summary, results }, null, 2),\n { headers: { \"Content-Type\": \"application/json\" } }\n );\n}\n",[256],"871b2084-f166-11f0-af3d-42dde27851f2","README.md","b6153ade-ef4d-11f0-9c60-42dde27851f2",126,["D",1768408310405],"# Law Student Tracker - Val.town Project\n\nAutomates attendance tracking and cold-call selection for both T&E and L&C classes.\n\n## Architecture\n\n**Two Google Spreadsheets:**\n\n| Spreadsheet | Purpose | Env Var |\n|-------------|---------|---------|\n| Main | Rosters, Cold Call Log, derived tabs | `MAIN_SHEET_ID` |\n| Student Forms | Student self-report Google Form responses | `STUDENT_FORMS_SHEET_ID` |\n\nSarah submits reports via HTML form (no Google Form needed for her).\n\n## Vals\n\n| File | Type | Purpose |\n|------|------|---------|\n| **Sarah's Interface** | | |\n| `sarah-report-form.http.tsx` | HTTP | HTML form for Sarah to submit post-class reports |\n| `post-class-report.http.tsx` | HTTP | API: receives reports, updates tracking, triggers alerts |\n| **T&E (Trusts & Estates)** | | |\n| `oncall-selection.http.tsx` | HTTP | Algorithmically selects 6 students for cold-calling |\n| `oncall-notify.cron.tsx` | Cron | Emails on-call list before class (3 PM) |\n| `te-report-prompt.cron.tsx` | Cron | Reminds Sarah to submit T&E report (6 PM) |\n| **L&C (Law & Capitalism)** | | |\n| `lc-oncall-selection.http.tsx` | HTTP | Looks up preset on-call from syllabus |\n| `lc-oncall-notify.cron.tsx` | Cron | Emails on-call list before class (8 AM) |\n| `lc-report-prompt.cron.tsx` | Cron | Reminds Sarah to submit L&C report (12 PM) |\n| **Utilities** | | |\n| `weekly-summary.cron.tsx` | Cron | Friday summary of students above thresholds |\n| `data-integrity-check.http.tsx` | HTTP | Compares roster counts vs Cold Call Log |\n| `refresh-watch-list.http.tsx` | HTTP | Manually refresh Watch-List tab |\n| `assign-pools.http.tsx` | HTTP | Calculate optimal A-Z pool split for T&E |\n| `utils.tsx` | Script | Shared utilities, constants, types |\n\n## Environment Variables\n\nSet in Val.town UI (Settings → Environment Variables):\n\n| Variable | Purpose |\n|----------|---------|\n| `GOOGLE_APPLICATION_CREDENTIALS` | Service account JSON (full string) |\n| `REPORT_API_KEY` | Secret key for report submissions |\n| `MAIN_SHEET_ID` | Main spreadsheet (rosters, logs) |\n| `STUDENT_FORMS_SHEET_ID` | Student self-report form responses |\n\n## Sheet Structure\n\n### Main Spreadsheet (MAIN_SHEET_ID)\n\n**Source of Truth (Sarah can edit to fix data):**\n\n| Tab | Purpose |\n|-----|---------|\n| `T&E-Roster` | Student list + cumulative counters |\n| `L&C-Roster` | Student list + cumulative counters |\n| `Cold-Call-Log` | Audit trail of all cold calls |\n| `L&C-Schedule` | Preset on-call assignments from syllabus |\n\n**Derived (auto-generated, don't edit):**\n\n| Tab | Purpose |\n|-----|---------|\n| `T&E-On-Call-Today` | Today's T&E selection (cached) |\n| `L&C-On-Call-Today` | Today's L&C selection (cached) |\n| `Watch-List` | Students approaching thresholds |\n| `Pending-Review` | Unmatched names for manual review |\n| `Cancelled-Classes` | Log of cancelled class dates |\n\n### Roster Columns\n\n```\nstudent_name, last_name, pool (T&E only)\ncold_call_count, last_called\nabsences, absences_announced, absences_unannounced\nconcerning_events, refused_count, bad_performance_count\nlast_absence_watch, last_absence_warning, last_absence_alert\nlast_concerning_watch, last_concerning_warning, last_concerning_alert\n```\n\n### Student Forms Spreadsheet (STUDENT_FORMS_SHEET_ID)\n\n| Tab | Purpose |\n|-----|---------|\n| `Form Responses 1` | Student self-reports (Google Form writes here) |\n\n## Alert Thresholds\n\n### Early Watch ([WATCH])\n- **2 absences** → Watch alert (early warning)\n- **3 concerning events** → Watch alert\n\n### Warning ([WARNING])\n- **3 absences** → Sarah warns student\n- **4 concerning events** → Sarah warns student\n\n### Alert ([ALERT])\n- **4 absences** → Contact dean + student\n- **5 concerning events** → Contact student\n\n**Concerning events** = absences + refused + bad performance + unprepared\n\n## Sarah's Workflow\n\n1. **Before class:** Receives on-call email automatically\n2. **After class:** Opens HTML form, fills out report, submits\n3. **On alerts:** Receives email with student details and required action\n\nForm URL: `https://sahar--{hash}.web.val.run` (get from Val.town UI)\n\n## API Reference\n\n### POST /post-class-report\n\n```bash\ncurl -X POST \"https://sahar--{hash}.web.val.run\" \\\n -H \"Content-Type: application/json\" \\\n -H \"X-API-Key: $REPORT_API_KEY\" \\\n -d '{\n \"date\": \"2026-01-12\",\n \"class_type\": \"te\",\n \"cold_calls\": [\n {\"student_name\": \"Alice Adams\", \"result\": \"completed\", \"performance\": \"great\"},\n {\"student_name\": \"Bob Baker\", \"result\": \"absent\"},\n {\"student_name\": \"Carol Chen\", \"result\": \"refused\"}\n ],\n \"absences\": [\"David Davis\"]\n }'\n```\n\n**cold_call results:**\n- `completed` + performance (`great`/`normal`/`bad`)\n- `absent` (unannounced - didn't show up)\n- `refused` (present but declined)\n\n**absences:** Students not on call list who were absent\n\n## Cron Schedules (UTC)\n\n| Val | EST Time | UTC Cron |\n|-----|----------|----------|\n| `oncall-notify` | 3 PM Mon/Wed | `0 20 * * 1,3` |\n| `te-report-prompt` | 6 PM Mon/Wed | `0 23 * * 1,3` |\n| `lc-oncall-notify` | 8 AM Mon/Wed | `0 13 * * 1,3` |\n| `lc-report-prompt` | 12 PM Mon/Wed | `0 17 * * 1,3` |\n| `weekly-summary` | 9 AM Fri | `0 14 * * 5` |\n\n**Note:** Adjust for DST. EST = UTC-5, EDT = UTC-4.\n\n## Development\n\n```bash\n# Deploy changes\n~/.deno/bin/vt push\n\n# Pull remote changes\n~/.deno/bin/vt pull\n```\n",[263],"pulls","branches","forks","envVars",{"_276":237,"_278":58},"connections",{"_276":58,"_277":58},"count","unfilledCount","emptyCount",{"_41":262,"_31":263,"_29":264,"_142":-5,"_39":40,"_38":265,"_144":117,"_36":345,"_13":214,"_32":20,"_34":267,"_23":24,"_149":-5,"_297":346,"_26":347},{"_41":255,"_31":256,"_29":257,"_142":-5,"_39":40,"_38":258,"_144":117,"_36":342,"_13":252,"_32":33,"_34":260,"_23":24,"_149":-5,"_297":343,"_26":344},{"_41":247,"_31":248,"_29":249,"_142":-5,"_39":40,"_38":250,"_144":117,"_36":339,"_13":252,"_32":33,"_34":253,"_23":24,"_149":-5,"_297":340,"_26":341},{"_41":240,"_31":241,"_29":242,"_142":-5,"_39":40,"_38":243,"_144":117,"_36":336,"_13":146,"_32":33,"_34":245,"_23":24,"_149":-5,"_297":337,"_26":338},{"_41":232,"_31":233,"_29":234,"_142":-5,"_39":40,"_38":235,"_144":117,"_36":333,"_13":237,"_32":147,"_34":238,"_23":24,"_149":-5,"_297":334,"_26":335},{"_41":224,"_31":225,"_29":226,"_142":-5,"_39":40,"_38":227,"_144":117,"_36":330,"_13":229,"_32":33,"_34":230,"_23":24,"_149":-5,"_297":331,"_26":332},{"_41":217,"_31":218,"_29":219,"_142":-5,"_39":40,"_38":220,"_144":117,"_36":327,"_13":165,"_32":147,"_34":222,"_23":24,"_149":-5,"_297":328,"_26":329},{"_41":209,"_31":210,"_29":211,"_142":-5,"_39":40,"_38":212,"_144":117,"_36":324,"_13":214,"_32":33,"_34":215,"_23":24,"_149":-5,"_297":325,"_26":326},{"_41":202,"_31":203,"_29":204,"_142":-5,"_39":40,"_38":205,"_144":117,"_36":321,"_13":165,"_32":147,"_34":207,"_23":24,"_149":-5,"_297":322,"_26":323},{"_41":194,"_31":195,"_29":196,"_142":-5,"_39":40,"_38":197,"_144":117,"_36":318,"_13":199,"_32":33,"_34":200,"_23":24,"_149":-5,"_297":319,"_26":320},{"_41":186,"_31":187,"_29":188,"_142":-5,"_39":40,"_38":189,"_144":117,"_36":315,"_13":191,"_32":33,"_34":192,"_23":24,"_149":-5,"_297":316,"_26":317},{"_41":179,"_31":180,"_29":181,"_142":-5,"_39":40,"_38":182,"_144":117,"_36":312,"_13":58,"_32":33,"_34":184,"_23":24,"_149":-5,"_297":313,"_26":314},{"_41":171,"_31":172,"_29":173,"_142":-5,"_39":40,"_38":174,"_144":117,"_36":309,"_13":176,"_32":33,"_34":177,"_23":24,"_149":-5,"_297":310,"_26":311},{"_41":168,"_31":27,"_29":30,"_142":-5,"_39":40,"_38":14,"_144":117,"_36":306,"_13":58,"_32":33,"_34":35,"_23":24,"_149":-5,"_297":307,"_26":308},{"_41":160,"_31":161,"_29":162,"_142":-5,"_39":40,"_38":163,"_144":117,"_36":303,"_13":165,"_32":147,"_34":166,"_23":24,"_149":-5,"_297":304,"_26":305},{"_41":151,"_31":152,"_29":153,"_142":-5,"_39":40,"_38":154,"_144":117,"_36":300,"_13":156,"_32":157,"_34":158,"_23":24,"_149":-5,"_297":301,"_26":302},{"_41":139,"_31":140,"_29":141,"_142":-5,"_39":40,"_38":143,"_144":117,"_36":296,"_13":146,"_32":147,"_34":148,"_23":24,"_149":-5,"_297":298,"_26":299},["D",1769094547928],"children",[],[140],["D",1771352781351],[],[152],["D",1771352781196],[],[161],["D",1771353686159],[],[27],["D",1771352781270],[],[172],["D",1768316040524],[],[180],["D",1771352781114],[],[187],["D",1771352781129],[],[195],["D",1771352781251],[],[203],["D",1768257137698],[],[210],["D",1771352781303],[],[218],["D",1771352781086],[],[225],["D",1771352781152],[],[233],["D",1771352781121],[],[241],["D",1769094548113],[],[248],["D",1768327845603],[],[256],["D",1768408310405],[],[263],["D",1754054718011],{"_91":90,"_98":94},{"_91":90,"_92":-5,"_93":94,"_18":52,"_95":96,"_97":24},{"_13":14,"_31":81,"_41":40,"_82":42,"_83":-5,"_84":-5,"_85":-5,"_86":-5,"_87":-5,"_88":43,"_89":90},"ctx",{"_74":-5,"_119":385,"_386":387,"_388":389,"_390":24,"_391":24,"_392":24,"_393":117,"_394":-5,"_395":-7,"_396":-7,"_397":58,"_398":24,"_399":24,"_400":-5,"_401":24,"_402":24,"_403":24,"_404":-5},"ENV",{"_356":357,"_358":359,"_360":81,"_361":362,"_363":364,"_365":366,"_367":368,"_369":370,"_371":372,"_373":374,"_375":376,"_377":378,"_379":380,"_381":382,"_383":384},"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 · v158