[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_339":340,"_341":342},"routes/_app.x.$handle.$name",{"_94":93,"_32":45,"_102":84,"_27":103,"_13":14,"_104":-5,"_11":105,"_106":107,"_108":109,"_110":111,"_112":113,"_20":-5,"_114":-5,"_115":-5,"_116":25,"_117":118,"_119":120,"_121":-5,"_122":-5,"_123":124},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":-5,"_53":-5,"_18":54,"_55":-5,"_56":25,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":-5,"_64":-5,"_65":-5,"_66":-5,"_67":68,"_69":68,"_70":71,"_72":71,"_73":71,"_74":68,"_75":68,"_76":77,"_78":79,"_80":81,"_82":25},"version",131,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","utils.tsx","latestPath","fileId","da859f36-ef65-11f0-8c45-42dde27851f2","name","fileType","script","content","/**\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 * Student self-report form spreadsheet (Google Form writes here).\n * For production, set STUDENT_FORMS_SHEET_ID env var in Val.town.\n */\nexport const STUDENT_FORMS_SHEET_ID = Deno.env.get(\"STUDENT_FORMS_SHEET_ID\") || \"1Iuoz4e8jYnd787qNMC5x6NMZaWJSuxnWz9zyTyVRRYA\";\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 * SOURCE OF TRUTH (Sarah can edit to fix data):\n * T&E-Roster, L&C-Roster, Cold-Call-Log, L&C-Schedule\n *\n * DERIVED (auto-generated, don't edit):\n * T&E-On-Call-Today, L&C-On-Call-Today, Watch-List, Pending-Review, Cancelled-Classes\n */\nexport const SHEET_TABS = {\n // === SOURCE OF TRUTH ===\n TE_ROSTER: \"T&E-Roster\",\n LC_ROSTER: \"L&C-Roster\",\n COLD_CALL_LOG: \"Cold-Call-Log\",\n LC_SCHEDULE: \"L&C-Schedule\", // Pre-assigned on-call dates from syllabus\n\n // === DERIVED (auto-generated) ===\n TE_ON_CALL: \"T&E-On-Call-Today\",\n LC_ON_CALL: \"L&C-On-Call-Today\",\n WATCH_LIST: \"Watch-List\",\n PENDING_REVIEW: \"Pending-Review\",\n CANCELLED_CLASSES: \"Cancelled-Classes\",\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// ============================================================================\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// 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}\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 .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 >= 4) {\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 >= 4) {\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 * 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 * Student self-report form spreadsheet (Google Form writes here).\n * For production, set STUDENT_FORMS_SHEET_ID env var in Val.town.\n */\nexport const STUDENT_FORMS_SHEET_ID = Deno.env.get(\"STUDENT_FORMS_SHEET_ID\") || \"1Iuoz4e8jYnd787qNMC5x6NMZaWJSuxnWz9zyTyVRRYA\";\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 * SOURCE OF TRUTH (Sarah can edit to fix data):\n * T&E-Roster, L&C-Roster, Cold-Call-Log, L&C-Schedule\n *\n * DERIVED (auto-generated, don't edit):\n * T&E-On-Call-Today, L&C-On-Call-Today, Watch-List, Pending-Review, Cancelled-Classes\n */\nexport const SHEET_TABS = {\n // === SOURCE OF TRUTH ===\n TE_ROSTER: \"T&E-Roster\",\n LC_ROSTER: \"L&C-Roster\",\n COLD_CALL_LOG: \"Cold-Call-Log\",\n LC_SCHEDULE: \"L&C-Schedule\", // Pre-assigned on-call dates from syllabus\n\n // === DERIVED (auto-generated) ===\n TE_ON_CALL: \"T&E-On-Call-Today\",\n LC_ON_CALL: \"L&C-On-Call-Today\",\n WATCH_LIST: \"Watch-List\",\n PENDING_REVIEW: \"Pending-Review\",\n CANCELLED_CLASSES: \"Cancelled-Classes\",\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// ============================================================================\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// 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}\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 .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","createdAt",["D",1768418428208],"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",{"_94":93,"_101":97},"org",{"_94":93,"_95":-5,"_96":97,"_18":54,"_98":99,"_100":25},"branch",{"_13":83,"_32":84,"_43":42,"_85":44,"_86":-5,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":45,"_92":93},"isOwned",158,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","sahar","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ydjJ5emk0MWJLd3hWMW1iWHZSOVFGdnBiRjYifQ","tier","pro","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":335,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":-5,"_53":-5,"_18":54,"_55":-5,"_56":25,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":-5,"_64":-5,"_65":-5,"_66":-5,"_67":68,"_69":68,"_70":71,"_72":71,"_73":71,"_74":68,"_75":68,"_76":336,"_78":337,"_80":338,"_82":25},"tree",[270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285],"files",[270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285],"counts",{"_259":60,"_260":148,"_261":60,"_262":263,"_264":265},"contents",[125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_43":252,"_32":253,"_30":254,"_144":-5,"_41":42,"_40":255,"_146":120,"_38":256,"_13":162,"_33":20,"_35":257,"_24":25,"_151":-5,"_27":258},{"_43":245,"_32":246,"_30":247,"_144":-5,"_41":42,"_40":248,"_146":120,"_38":249,"_13":148,"_33":170,"_35":250,"_24":25,"_151":-5,"_27":251},{"_43":238,"_32":239,"_30":240,"_144":-5,"_41":42,"_40":241,"_146":25,"_38":242,"_13":60,"_33":170,"_35":243,"_24":25,"_151":-5,"_27":244},{"_43":231,"_32":232,"_30":233,"_144":-5,"_41":42,"_40":234,"_146":25,"_38":235,"_13":60,"_33":170,"_35":236,"_24":25,"_151":-5,"_27":237},{"_43":224,"_32":225,"_30":226,"_144":-5,"_41":42,"_40":227,"_146":25,"_38":228,"_13":162,"_33":149,"_35":229,"_24":25,"_151":-5,"_27":230},{"_43":216,"_32":217,"_30":218,"_144":-5,"_41":42,"_40":219,"_146":25,"_38":220,"_13":221,"_33":170,"_35":222,"_24":25,"_151":-5,"_27":223},{"_43":209,"_32":210,"_30":211,"_144":-5,"_41":42,"_40":212,"_146":25,"_38":213,"_13":162,"_33":149,"_35":214,"_24":25,"_151":-5,"_27":215},{"_43":202,"_32":203,"_30":204,"_144":-5,"_41":42,"_40":205,"_146":120,"_38":206,"_13":162,"_33":170,"_35":207,"_24":25,"_151":-5,"_27":208},{"_43":195,"_32":196,"_30":197,"_144":-5,"_41":42,"_40":198,"_146":25,"_38":199,"_13":162,"_33":149,"_35":200,"_24":25,"_151":-5,"_27":201},{"_43":188,"_32":189,"_30":190,"_144":-5,"_41":42,"_40":191,"_146":25,"_38":192,"_13":155,"_33":170,"_35":193,"_24":25,"_151":-5,"_27":194},{"_43":180,"_32":181,"_30":182,"_144":-5,"_41":42,"_40":183,"_146":25,"_38":184,"_13":185,"_33":170,"_35":186,"_24":25,"_151":-5,"_27":187},{"_43":173,"_32":174,"_30":175,"_144":-5,"_41":42,"_40":176,"_146":120,"_38":177,"_13":60,"_33":170,"_35":178,"_24":25,"_151":-5,"_27":179},{"_43":165,"_32":166,"_30":167,"_144":-5,"_41":42,"_40":168,"_146":25,"_38":169,"_13":148,"_33":170,"_35":171,"_24":25,"_151":-5,"_27":172},{"_43":157,"_32":158,"_30":159,"_144":-5,"_41":42,"_40":160,"_146":25,"_38":161,"_13":162,"_33":149,"_35":163,"_24":25,"_151":-5,"_27":164},{"_43":153,"_32":28,"_30":31,"_144":-5,"_41":42,"_40":14,"_146":25,"_38":154,"_13":155,"_33":34,"_35":37,"_24":25,"_151":-5,"_27":156},{"_43":141,"_32":142,"_30":143,"_144":-5,"_41":42,"_40":145,"_146":25,"_38":147,"_13":148,"_33":149,"_35":150,"_24":25,"_151":-5,"_27":152},"e429108c-f0a4-11f0-b610-42dde27851f2","weekly-summary.cron.tsx","b1f82aae-f08f-11f0-952c-42dde27851f2","parentId",110,"latest",["D",1768325144155],1,"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 EMAIL_FOOTER,\n formatDate,\n getAuthClient,\n SHEET_ID,\n SHEET_TABS,\n} from \"./utils.tsx\";\n\n// Alert thresholds (same as post-class-report)\nconst ABSENCE_THRESHOLD = 4; // Updated: was 3, now 4 for dean notification\nconst CONCERNING_THRESHOLD = 5;\n\ninterface StudentAtRisk {\n name: string;\n classType: \"T&E\" | \"L&C\";\n absences: number;\n concerningEvents: number;\n recentActivity: string[];\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\n const isConcerning =\n result === \"absent\" ||\n result === \"absent-announced\" ||\n result === \"refused\" ||\n result === \"unprepared\" ||\n performance === \"bad\";\n\n if (isConcerning) {\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\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 if (absences >= ABSENCE_THRESHOLD || concerning >= CONCERNING_THRESHOLD) {\n studentsAtRisk.push({\n name,\n classType: \"T&E\",\n absences,\n concerningEvents: concerning,\n recentActivity: getRecentActivity(logRows, name, cutoffDate),\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 if (absences >= ABSENCE_THRESHOLD || concerning >= CONCERNING_THRESHOLD) {\n studentsAtRisk.push({\n name,\n classType: \"L&C\",\n absences,\n concerningEvents: concerning,\n recentActivity: getRecentActivity(logRows, name, cutoffDate),\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 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 alert thresholds.\n\nThresholds:\n- Absences: ${ABSENCE_THRESHOLD}\n- Concerning events: ${CONCERNING_THRESHOLD}`;\n } else {\n subject = `[LawTracker] Weekly Summary - ${studentsAtRisk.length} student(s) need attention`;\n\n // Sort by most concerning first\n studentsAtRisk.sort((a, b) => {\n const aMax = Math.max(a.absences / ABSENCE_THRESHOLD, a.concerningEvents / CONCERNING_THRESHOLD);\n const bMax = Math.max(b.absences / ABSENCE_THRESHOLD, b.concerningEvents / CONCERNING_THRESHOLD);\n return bMax - aMax;\n });\n\n const studentSummaries = studentsAtRisk.map((s) => {\n let summary = `${s.name} (${s.classType})\n Absences: ${s.absences}${s.absences >= ABSENCE_THRESHOLD ? \" [OVER]\" : \"\"}\n Concerning: ${s.concerningEvents}${s.concerningEvents >= CONCERNING_THRESHOLD ? \" [OVER]\" : \"\"}`;\n\n if (s.recentActivity.length > 0) {\n summary += `\\n Recent activity:\\n${s.recentActivity.map((a) => ` - ${a}`).join(\"\\n\")}`;\n } else {\n summary += \"\\n No recent activity (last 2 weeks)\";\n }\n\n return summary;\n });\n\n bodyText = `Weekly Summary for ${formatDate(new Date())}\n\n${studentsAtRisk.length} student(s) above alert thresholds:\n\n${studentSummaries.join(\"\\n\\n\")}\n\n---\nThresholds: ${ABSENCE_THRESHOLD} absences, ${CONCERNING_THRESHOLD} concerning events`;\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",[142],"15cb7574-f17e-11f0-a8d8-42dde27851f2",["D",1768418428208],10,[28],"64cae4bc-f024-11f0-9b78-42dde27851f2","te-report-prompt.cron.tsx","e4b192e0-ef4b-11f0-b248-42dde27851f2",100,["D",1768269954806],5,"/**\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 const notificationEmail = Deno.env.get(\"NOTIFICATION_EMAIL\");\n if (!notificationEmail) {\n throw new Error(\"NOTIFICATION_EMAIL env var not set. Configure in Val.town settings.\");\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\nReply to this email or use the reporting form.\n\nThanks!` + EMAIL_FOOTER,\n });\n\n console.log(`Sent T&E report prompt for ${today}`);\n}\n",[158],"15c80006-f17e-11f0-ab40-42dde27851f2","sarah-report-form.http.tsx","ab2d2176-f165-11f0-a75a-42dde27851f2",130,["D",1768418428198],"http","/**\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\nconst POST_CLASS_REPORT_URL = \"https://sahar--53e764dcef4c11f09c6042dde27851f2.web.val.run\";\nconst API_KEY = Deno.env.get(\"REPORT_API_KEY\") || \"\";\n\nfunction formatDate(date: Date): string {\n return date.toISOString().split(\"T\")[0];\n}\n\nfunction parseNameList(text: string): string[] {\n if (!text || !text.trim()) return [];\n return text\n .split(/[,\\n]/)\n .map(n => n.trim())\n .filter(n => n.length > 0);\n}\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 hasUnmatched = unmatchedNames.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 ${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 and use the format: \"Last, First\" (e.g., \"Adams, Alice\")

\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 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 \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",[166],"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",[174],"15bec2f2-f17e-11f0-82b9-42dde27851f2","post-class-report.http.tsx","53e764dc-ef4c-11f0-9c60-42dde27851f2",129,["D",1768418428141],22,"/**\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 type ColdCallEntry,\n EMAIL_FOOTER,\n getAuthClient,\n isConcerningEvent,\n MAIN_SHEET_ID,\n parseSheetDate,\n type ReportPayload,\n SHEET_ID,\n SHEET_TABS,\n STUDENT_FORMS_SHEET_ID,\n STUDENT_FORMS_TAB,\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// Alert thresholds (dean notification)\nconst ABSENCE_THRESHOLD = 4; // Was 3 - now 4 for dean notification\nconst CONCERNING_THRESHOLD = 5;\n\n// Warning thresholds (Sarah warns student)\nconst ABSENCE_WARNING = 3; // NEW - Sarah warns student at 3\nconst CONCERNING_WARNING = 4; // NEW - Sarah warns student at 4\n\n// Watch thresholds (early warning)\nconst ABSENCE_WATCH = 2;\nconst CONCERNING_WATCH = 3; // Was 4 - now 3 for early warning\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_WARNING : CONCERNING_WARNING;\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_THRESHOLD : CONCERNING_THRESHOLD;\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 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\n const rosterByName = new Map();\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 }\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 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 student forms spreadsheet\n let selfReportRows: Array<{ get: (key: string) => string }> = [];\n try {\n const studentFormsDoc = new GoogleSpreadsheet(STUDENT_FORMS_SHEET_ID, 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(\"Date of class you'll be absent, late-arriving/early-departing, or unprepared\");\n const reportType = row.get(\"Will you be absent, unprepared, or late/early?\") || \"\";\n const studentName = row.get(\"Your 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 (case-insensitive match)\n const normalizedName = studentName.toLowerCase().trim();\n let matchedRosterName: string | null = null;\n let matchedRosterRow: typeof rosterByName extends Map ? V : never | null = null;\n\n for (const [rosterName, rosterRow] of rosterByName) {\n if (rosterName.toLowerCase().trim() === normalizedName) {\n matchedRosterName = rosterName;\n matchedRosterRow = rosterRow;\n break;\n }\n }\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_WATCH,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARNING,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_THRESHOLD,\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_WATCH,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARNING,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_THRESHOLD,\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_WATCH,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARNING,\n notificationEmail,\n rosterRow: matchedRosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: matchedRosterName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_THRESHOLD,\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 // Update roster\n const rosterRow = rosterByName.get(call.student_name);\n if (!rosterRow) {\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 // Idempotency: skip if already logged\n if (isDuplicateEntry(payload.date, call.student_name, call.result)) {\n skippedDuplicateLogEntries++;\n continue;\n }\n\n // Add to Cold Call Log\n await logSheet.addRow({\n date: payload.date,\n student_name: call.student_name,\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(call.student_name);\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: call.student_name,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_WATCH,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: call.student_name,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARNING,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: call.student_name,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_THRESHOLD,\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(call.student_name); // 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: call.student_name,\n alertType: \"absences\",\n newCount: newAbsences,\n watchThreshold: ABSENCE_WATCH,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: call.student_name,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARNING,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: call.student_name,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_THRESHOLD,\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 // Skip if already counted as absent in cold_calls\n if (processedAbsent.has(absentName)) {\n skippedDuplicateAbsences++;\n continue;\n }\n\n const rosterRow = rosterByName.get(absentName);\n if (rosterRow) {\n // Idempotency: skip if already logged\n if (isDuplicateEntry(payload.date, absentName, \"absent\")) {\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: absentName,\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: absentName,\n alertType: \"absences\",\n newCount: newAbsences,\n watchThreshold: ABSENCE_WATCH,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: absentName,\n alertType: \"absences\",\n newCount: newAbsences,\n warningThreshold: ABSENCE_WARNING,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: absentName,\n alertType: \"absences\",\n newCount: newAbsences,\n threshold: ABSENCE_THRESHOLD,\n notificationEmail,\n alerts,\n logSheet,\n rosterRow,\n });\n\n await checkAndSendWatchAlert({\n studentName: absentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n watchThreshold: CONCERNING_WATCH,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendWarningAlert({\n studentName: absentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n warningThreshold: CONCERNING_WARNING,\n notificationEmail,\n rosterRow,\n });\n\n await checkAndSendThresholdAlert({\n studentName: absentName,\n alertType: \"concerning\",\n newCount: newConcerning,\n threshold: CONCERNING_THRESHOLD,\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 });\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 },\n );\n }\n}\n",[181],"ab14c7d4-f165-11f0-ae6b-42dde27851f2","oncall-selection.http.tsx","78f39bfe-ef49-11f0-a9a7-42dde27851f2",119,["D",1768407941262],"/**\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 formatDate,\n getAuthClient,\n MAIN_SHEET_ID,\n parseSheetDate,\n SHEET_ID,\n SHEET_TABS,\n 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: SHEET_TABS.TE_ON_CALL,\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 * Validates pool assignments look reasonable.\n * Warns if a student's last name suggests wrong pool (A-M = mon, N-Z = wed).\n */\nfunction validatePoolAssignment(\n roster: RosterStudent[],\n pool: \"mon\" | \"wed\",\n): { warnings: string[] } {\n const warnings: string[] = [];\n\n const lastNames = roster.map((s) => s.last_name.toLowerCase()).sort();\n if (lastNames.length === 0) {\n warnings.push(`No students found in ${pool} pool`);\n return { warnings };\n }\n\n // Simple heuristic: Monday should be A-M, Wednesday should be N-Z\n const midpoint = \"m\";\n for (const student of roster) {\n const firstLetter = student.last_name.toLowerCase()[0];\n if (pool === \"mon\" && firstLetter > midpoint) {\n warnings.push(\n `Student \"${student.student_name}\" (last name: ${student.last_name}) may be in wrong pool`,\n );\n } else if (pool === \"wed\" && firstLetter <= midpoint) {\n warnings.push(\n `Student \"${student.student_name}\" (last name: ${student.last_name}) may be in wrong pool`,\n );\n }\n }\n\n return { warnings };\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: string[];\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 tabs\n let onCallSheet = doc.sheetsByTitle[TABS.ONCALL];\n if (!onCallSheet) {\n onCallSheet = await doc.addSheet({\n title: TABS.ONCALL,\n headerValues: [\"date\", \"student_name\", \"notified_at\"],\n });\n }\n\n // Check if today already has selections (idempotency)\n await onCallSheet.loadHeaderRow();\n const existingRows = await onCallSheet.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) => row.get(\"student_name\")),\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 // Validate pool assignment\n const poolValidation = validatePoolAssignment(roster, pool);\n if (poolValidation.warnings.length > 0) {\n console.warn(\"Pool assignment warnings:\", poolValidation.warnings);\n }\n\n // Get today's self-reports from student forms spreadsheet\n const excludedNames: string[] = [];\n\n try {\n const studentFormsDoc = new GoogleSpreadsheet(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(\n \"Date of class you'll be absent, late-arriving/early-departing, or unprepared\",\n );\n if (reportDate) {\n const parsedDate = parseSheetDate(reportDate);\n if (parsedDate === today) {\n const name = row.get(\"Your name\");\n if (name) excludedNames.push(name.toLowerCase().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\n const eligible = roster.filter(\n (s) => !excludedNames.includes(s.student_name.toLowerCase().trim()),\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 selectedNames = selected.map((s) => s.student_name);\n\n // Store selections if we computed new ones\n if (forceRecompute && todaySelections.length > 0) {\n // Delete existing selections for today\n for (const row of todaySelections) {\n await row.delete();\n }\n }\n\n // Write new selections\n const now = new Date().toISOString();\n for (const name of selectedNames) {\n await onCallSheet.addRow({\n date: today,\n student_name: name,\n notified_at: now,\n });\n }\n\n return {\n success: true,\n date: today,\n pool,\n selected: selectedNames,\n fromCache: false, // Just computed and stored\n excluded: excludedNames,\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",[189],"64c1ffb4-f024-11f0-9863-42dde27851f2","oncall-notify.cron.tsx","e4ba617c-ef4b-11f0-be8e-42dde27851f2",98,["D",1768269954816],"/**\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 const notificationEmail = Deno.env.get(\"NOTIFICATION_EMAIL\");\n if (!notificationEmail) {\n throw new Error(\"NOTIFICATION_EMAIL env var not set. Configure in Val.town settings.\");\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\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 T&E:\n\n${studentList}\n\nPool: ${result.pool === \"mon\" ? \"Monday (A-M)\" : \"Wednesday (N-Z)\"}\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",[196],"8d266b3e-f006-11f0-82df-42dde27851f2","main.http.tsx","c49a6e2c-ef47-11f0-9984-42dde27851f2",80,["D",1768257137698],"/**\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",[203],"64bdea14-f024-11f0-9f83-42dde27851f2","lc-report-prompt.cron.tsx","e4ab0ea2-ef4b-11f0-b248-42dde27851f2",97,["D",1768269954806],"/**\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 const notificationEmail = Deno.env.get(\"NOTIFICATION_EMAIL\");\n if (!notificationEmail) {\n throw new Error(\"NOTIFICATION_EMAIL env var not set. Configure in Val.town settings.\");\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\nReply to this email or use the reporting form.\n\nThanks!` + EMAIL_FOOTER,\n });\n\n console.log(`Sent L&C report prompt for ${today}`);\n}\n",[210],"ab19a182-f165-11f0-b6d7-42dde27851f2","lc-oncall-selection.http.tsx","2b3b7afa-ef56-11f0-bec6-42dde27851f2",120,["D",1768407941286],7,"/**\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 { formatDate, getAuthClient, parseSheetDate, SHEET_ID, SHEET_TABS } from \"./utils.tsx\";\n\n// Local aliases for readability\nconst TABS = {\n ASSIGNMENTS: SHEET_TABS.LC_SCHEDULE,\n ONCALL: SHEET_TABS.LC_ON_CALL,\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}> {\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 On-Call cache tab\n let onCallSheet = doc.sheetsByTitle[TABS.ONCALL];\n if (!onCallSheet) {\n onCallSheet = await doc.addSheet({\n title: TABS.ONCALL,\n headerValues: [\"date\", \"student_name\", \"notified_at\"],\n });\n }\n\n // Check cache first (idempotency)\n await onCallSheet.loadHeaderRow();\n const cachedRows = await onCallSheet.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 // Store in cache\n const now = new Date().toISOString();\n for (const name of todayStudents) {\n await onCallSheet.addRow({\n date: today,\n student_name: name,\n notified_at: now,\n });\n }\n\n return {\n success: true,\n date: today,\n selected: todayStudents,\n fromCache: false,\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",[217],"64c5db8e-f024-11f0-ac9f-42dde27851f2","lc-oncall-notify.cron.tsx","2b409878-ef56-11f0-bf11-42dde27851f2",99,["D",1768269954845],"/**\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 const notificationEmail = Deno.env.get(\"NOTIFICATION_EMAIL\");\n if (!notificationEmail) {\n throw new Error(\"NOTIFICATION_EMAIL env var not set. Configure in Val.town settings.\");\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",[225],"b1fc0b06-f08f-11f0-b802-42dde27851f2","data-integrity-check.http.tsx","b1fc0ba6-f08f-11f0-b802-42dde27851f2",105,["D",1768316040517],"/**\n * Data Integrity Check Endpoint\n *\n * Verifies that Cold Call Log totals match roster counter values.\n * Useful for detecting/fixing drift from manual edits or bugs.\n *\n * GET / - Returns mismatches (empty array = data is consistent)\n * GET /?fix=true - Fixes mismatches by updating roster to match log totals\n *\n * Response format:\n * {\n * \"checked\": 50,\n * \"mismatches\": [\n * { \"student_name\": \"...\", \"roster\": \"te\", \"log_absences\": 3, \"roster_absences\": 2, ... }\n * ],\n * \"fixed\": 0 // Only present if ?fix=true\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, not self-reports or general absences\n if (result !== \"unprepared\" && performance !== \"not-on-call\") {\n entry.coldCalls++;\n }\n\n // Count absences (result = 'absent')\n if (result === \"absent\") {\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 const url = new URL(req.url);\n const shouldFix = url.searchParams.get(\"fix\") === \"true\";\n\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 let totalFixed = 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 // Fix if requested\n if (shouldFix) {\n row.set(\"absences\", String(logEntry.absences));\n row.set(\"concerning_events\", String(logEntry.concerning));\n row.set(\"cold_call_count\", String(logEntry.coldCalls));\n await row.save();\n totalFixed++;\n }\n }\n }\n }\n\n const response: Record = {\n checked: totalChecked,\n mismatches,\n };\n\n if (shouldFix) {\n response.fixed = totalFixed;\n }\n\n return Response.json(response);\n } catch (error) {\n return Response.json(\n { error: error instanceof Error ? error.message : String(error) },\n { status: 500 },\n );\n }\n}\n",[232],"e42ec7ac-f0a4-11f0-944b-42dde27851f2","assign-pools.http.tsx","e42ec86a-f0a4-11f0-944b-42dde27851f2",111,["D",1768325144199],"/**\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 // 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",[239],"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",[246],"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",[253],"pulls","branches","forks","envVars",{"_266":268,"_269":60},"connections",{"_266":60,"_267":60},"count","unfilledCount",6,"emptyCount",{"_43":252,"_32":253,"_30":254,"_144":-5,"_41":42,"_40":255,"_146":120,"_38":332,"_13":162,"_33":20,"_35":257,"_24":25,"_151":-5,"_287":333,"_27":334},{"_43":245,"_32":246,"_30":247,"_144":-5,"_41":42,"_40":248,"_146":120,"_38":329,"_13":148,"_33":170,"_35":250,"_24":25,"_151":-5,"_287":330,"_27":331},{"_43":238,"_32":239,"_30":240,"_144":-5,"_41":42,"_40":241,"_146":25,"_38":326,"_13":60,"_33":170,"_35":243,"_24":25,"_151":-5,"_287":327,"_27":328},{"_43":231,"_32":232,"_30":233,"_144":-5,"_41":42,"_40":234,"_146":25,"_38":323,"_13":60,"_33":170,"_35":236,"_24":25,"_151":-5,"_287":324,"_27":325},{"_43":224,"_32":225,"_30":226,"_144":-5,"_41":42,"_40":227,"_146":25,"_38":320,"_13":162,"_33":149,"_35":229,"_24":25,"_151":-5,"_287":321,"_27":322},{"_43":216,"_32":217,"_30":218,"_144":-5,"_41":42,"_40":219,"_146":25,"_38":317,"_13":221,"_33":170,"_35":222,"_24":25,"_151":-5,"_287":318,"_27":319},{"_43":209,"_32":210,"_30":211,"_144":-5,"_41":42,"_40":212,"_146":25,"_38":314,"_13":162,"_33":149,"_35":214,"_24":25,"_151":-5,"_287":315,"_27":316},{"_43":202,"_32":203,"_30":204,"_144":-5,"_41":42,"_40":205,"_146":120,"_38":311,"_13":162,"_33":170,"_35":207,"_24":25,"_151":-5,"_287":312,"_27":313},{"_43":195,"_32":196,"_30":197,"_144":-5,"_41":42,"_40":198,"_146":25,"_38":308,"_13":162,"_33":149,"_35":200,"_24":25,"_151":-5,"_287":309,"_27":310},{"_43":188,"_32":189,"_30":190,"_144":-5,"_41":42,"_40":191,"_146":25,"_38":305,"_13":155,"_33":170,"_35":193,"_24":25,"_151":-5,"_287":306,"_27":307},{"_43":180,"_32":181,"_30":182,"_144":-5,"_41":42,"_40":183,"_146":25,"_38":302,"_13":185,"_33":170,"_35":186,"_24":25,"_151":-5,"_287":303,"_27":304},{"_43":173,"_32":174,"_30":175,"_144":-5,"_41":42,"_40":176,"_146":120,"_38":299,"_13":60,"_33":170,"_35":178,"_24":25,"_151":-5,"_287":300,"_27":301},{"_43":165,"_32":166,"_30":167,"_144":-5,"_41":42,"_40":168,"_146":25,"_38":296,"_13":148,"_33":170,"_35":171,"_24":25,"_151":-5,"_287":297,"_27":298},{"_43":157,"_32":158,"_30":159,"_144":-5,"_41":42,"_40":160,"_146":25,"_38":293,"_13":162,"_33":149,"_35":163,"_24":25,"_151":-5,"_287":294,"_27":295},{"_43":153,"_32":28,"_30":31,"_144":-5,"_41":42,"_40":14,"_146":25,"_38":290,"_13":155,"_33":34,"_35":37,"_24":25,"_151":-5,"_287":291,"_27":292},{"_43":141,"_32":142,"_30":143,"_144":-5,"_41":42,"_40":145,"_146":25,"_38":286,"_13":148,"_33":149,"_35":150,"_24":25,"_151":-5,"_287":288,"_27":289},["D",1768325144155],"children",[],[142],["D",1768418428208],[],[28],["D",1768269954806],[],[158],["D",1768418428198],[],[166],["D",1768316040524],[],[174],["D",1768418428141],[],[181],["D",1768407941262],[],[189],["D",1768269954816],[],[196],["D",1768257137698],[],[203],["D",1768269954806],[],[210],["D",1768407941286],[],[217],["D",1768269954845],[],[225],["D",1768316040517],[],[232],["D",1768325144199],[],[239],["D",1768327845603],[],[246],["D",1768408310405],[],[253],["D",1754054718011],{"_94":93,"_101":97},{"_94":93,"_95":-5,"_96":97,"_18":54,"_98":99,"_100":25},{"_13":83,"_32":84,"_43":42,"_85":44,"_86":-5,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":45,"_92":93},"ctx",{"_76":-5,"_122":372,"_373":374,"_375":376,"_377":25,"_378":25,"_379":25,"_380":120,"_381":-5,"_382":-7,"_383":-7,"_384":60,"_385":25,"_386":25,"_387":-5,"_388":25,"_389":25,"_390":25,"_391":-5},"ENV",{"_343":344,"_345":346,"_347":84,"_348":349,"_350":351,"_352":353,"_354":355,"_356":357,"_358":359,"_360":361,"_362":363,"_364":365,"_366":367,"_368":369,"_370":371},"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 · v131