LMG / NCAM Census Translation Engine v3.5.0

Turns any carrier census spreadsheet into a ready-to-upload LaunchMyGroup file — plus an NCAM quoting sheet — right inside Excel. Your census data never leaves your workbook.

Latest version: v3.5.0 (build 2026-10-09). To see which version you have, run the script and look at the SUMMARY tab — if it shows an older number, jump to Keep it up to date below (takes about 20 seconds).

① Put the script into Excel (one time, about a minute)

Click the blue Copy script button below. The whole script is now on your clipboard.
Open Excel — desktop app or excel on the web — signed in with your Humana account. Any workbook is fine.
Click the Automate tab in the ribbon at the top (to the right of View). If you don't see it, open the workbook in Excel on the web instead.
Click New Script. A panel opens on the right with a small sample script in it.
Click anywhere inside that code, press Ctrl+A (select all), then Ctrl+V (paste). The sample is replaced with this engine.
At the top of the panel, click the name Script 1, rename it to LMG NCAM Census Engine, then click Save script.

You only do this once. The script is saved to your Office account, so it shows up in the Automate tab of every workbook you open from now on.

View the script code (you don't need to read it — the Copy button grabs all of it)

/**
 * ============================================================================
 * LMG / NCAM CENSUS TRANSLATION ENGINE
 * Version is set in ENGINE_VERSION below and stamped onto the SUMMARY and
 * PROCESSING LOG tabs when it runs — compare it to the version on the hub page
 * (lmg-ncam-engine.netlify.app) to confirm you're on the latest during testing.
 * Implements CENSUS-RULES.md (the canonical, language-agnostic spec).
 * If you change behavior here, update CENSUS-RULES.md first and bump its version.
 * ============================================================================
 * Paste this whole file into Excel  →  Automate tab  →  New Script, then Run.
 * It rebuilds the open workbook into the LaunchMyGroup (LMG) upload format and
 * a derived NCAM quoting sheet, with full validation + audit logging.
 *
 * It reads your census from the sheet named "RAW DATA" if one exists,
 * otherwise from whatever sheet is active when you press Run. The original
 * data is always snapshotted to "RAW DATA" and never modified, so the script
 * is safe to re-run as many times as you like.
 *
 * OUTPUT SHEETS (created/overwritten on every run):
 *   1. LMG               <- enrollment upload file (ALWAYS worksheet #1)
 *   2. NCAM              <- employees-only quoting sheet
 *   3. SUMMARY           <- counts by relationship and by plan/tier
 *   4. VALIDATION ISSUES <- every ERROR / WARNING / INFO found
 *   5. PROCESSING LOG    <- audit trail of every transformation
 *   6. RAW DATA          <- untouched copy of your original census
 *   7. Other Columns     <- every column NOT in the LMG upload, with the reason
 *   8. HEADER MAP        <- column mappings you confirm; remembered across runs
 *
 * DESIGN NOTE ON DUPLICATES: the spec called for silently merging duplicate
 * rows. This engine FLAGS duplicates in VALIDATION ISSUES instead of deleting
 * them, so an enrollee is never silently dropped from a file you upload. You
 * review the flags and decide. (Stacked product rows for the same person are
 * collapsed by the pre-pass, rules section 11; true duplicates are only flagged.)
 * ============================================================================
 */

function main(workbook: ExcelScript.Workbook) {
  // QUOTE MODE: build-site.py publishes a second script, NCAM-Quote-Engine.ts,
  // with this set to true. It outputs one QUOTE line per employee (tiers only,
  // dependents purged) instead of the LMG/NCAM tabs. Never edit that copy.
  const QUOTE_MODE = false;
  const OUTPUT_SHEETS = ["QUOTE", "LMG", "NCAM", "SUMMARY", "VALIDATION ISSUES", "PROCESSING LOG", "RAW DATA", "Other Columns", "HEADER MAP"];

  // ---- Audit collectors -------------------------------------------------
  const log: string[] = [];
  const issues: string[][] = []; // SEVERITY, SOURCE ROW, LMG ROW, MEMBER, COLUMN, VALUE, ISSUE, ACTION
  // srcRowLabels[i] = the RAW DATA row number(s) behind enrollee row i (set after
  // the pre-pass; stacked rows collapsed into one person list every source row).
  let srcRowLabels: string[] = [];
  function addLog(msg: string) { log.push(msg); }
  // `row` is the LMG sheet row (header = row 1). The issue records both the
  // source row on RAW DATA (what the user looks up) and the LMG row (highlighting).
  function addIssue(sev: string, row: number, member: string, col: string, val: string, issue: string, action: string) {
    const src = row ? (srcRowLabels[row - 2] || String(row)) : "";
    issues.push([sev, src, row ? String(row) : "", member, col, val, issue, action]);
  }

  // ---- Version stamp (compare this to the version shown on the hub page) ----
  const ENGINE_VERSION = "3.5.0";
  const ENGINE_BUILD = "2026-10-09";
  addLog(`LMG / NCAM Census Engine v${ENGINE_VERSION} (build ${ENGINE_BUILD}).`);

  // ======================================================================
  // 1. LOCATE SOURCE DATA
  // ======================================================================
  let sourceSheet: ExcelScript.Worksheet;
  const existingRaw = workbook.getWorksheet("RAW DATA");
  if (existingRaw) sourceSheet = existingRaw;
  else sourceSheet = workbook.getActiveWorksheet();
  if (OUTPUT_SHEETS.indexOf(sourceSheet.getName()) !== -1 && sourceSheet.getName() !== "RAW DATA") {
    throw new Error("Please select the worksheet that contains your census data, then run again. " +
      "(The active sheet is a generated output sheet and there is no RAW DATA sheet to read from.)");
  }
  const usedRange = sourceSheet.getUsedRange();
  if (!usedRange) throw new Error("The source worksheet is empty. Nothing to process.");
  const raw = usedRange.getValues();
  if (raw.length < 2) throw new Error("The source worksheet has no data rows beneath the header row.");

  const rawHeaders = raw[0].map(h => String(h == null ? "" : h));
  // Convert all cells to strings up front so the whole script is string[][]
  // (avoids union-type generics that the Office Scripts compiler rejects).
  // Excel date serials survive as numeric strings; formatDate() still parses them.
  const rawData: string[][] = raw.slice(1).map(r => r.map(v => v == null ? "" : String(v)));
  // (PRE-PASS runs below, after the alias map is built, so it knows which columns
  // we keep — rate/premium columns we don't keep must not drive the reshape logic.)

  // ======================================================================
  // 2. LMG FIELD DEFINITIONS + HEADER ALIAS MAP
  // ======================================================================
  // Canonical LMG output columns — EXACT names and order from the LMG platform.
  const LMG_FIELDS: string[] = [
    // MEMBER INFORMATION
    "Relationship", "Member Last Name", "First Name", "Middle Initial", "Gender",
    "Date of Birth", "Member Street Address", "City", "State", "Zip Code",
    "Phone Number", "Social Security Number", "Status Field", "Date of Hire", "Email Address",
    // ADDITIONAL DETAILS
    "Hours Worked Per Week", "Salary", "Occupation", "Disabled?", "Billing Division",
    // BENEFITS INFORMATION
    "Dental Cov Level", "Dental Plan Elected", "DHMO Provider Name",
    "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date",
    "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included",
    "Vision Cov Level", "Vision Plan Election",
    "Basic Life Cov Level", "Life/AD&D Class", "Primary Life Beneficiary",
    "Dependent Basic Life",
    "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
    "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class",
    "Working Location"
  ];

  // Alias variants -> LMG field. Keys are normalized (see normHeader()).
  // Field names used before v3.0.0 (2026-09-10 shared dictionary) and by the
  // old Python tool — accepted anywhere a user types an LMG field name.
  const LEGACY_FIELD_NAMES: { [old: string]: string } = {
    "Employee Status": "Status Field", "Status": "Status Field", "SSN": "Social Security Number",
    "Member First Name": "First Name", "Member City": "City", "Member State": "State", "Member Zip Code": "Zip Code",
    "Hours Worked per week": "Hours Worked Per Week", "Annual Salary": "Salary",
    "Dental Plan Selection": "Dental Plan Elected", "Dental Coverage Type": "Dental Cov Level", "Dental Coverage Tier": "Dental Cov Level",
    "Dental Prior Carrier Name": "Dental Prior Current Carrier Name", "Dental Prior/Current Carrier Name": "Dental Prior Current Carrier Name",
    "Dental Prior Carrier Termination Date": "Dental Prior Carrier Term Date",
    "Dental Prior Carrier Ortho": "Dental Prior Carrier Ortho included", "Orthodontia Included?": "Dental Prior Carrier Ortho included",
    "Vision Plan Selection": "Vision Plan Election", "Vision Plan Elected": "Vision Plan Election",
    "Vision Coverage Type": "Vision Cov Level", "Vision Coverage Tier": "Vision Cov Level",
    "Basic Life Election": "Basic Life Cov Level", "Basic Life Class": "Life/AD&D Class", "Primary Beneficiary": "Primary Life Beneficiary",
    "Employee Voluntary Life": "Employee Voluntary Life Flat Amount", "Spousal Voluntary Life": "Spouse Voluntary Life Flat Amount",
    "Dependent Voluntary Life": "Dependent Voluntary Life Flat Amount",
    "STD": "Short-Term Disability Cov Level", "STD Election": "Short-Term Disability Cov Level",
    "LTD": "Long-Term Disability Cov Level", "LTD Election": "Long-Term Disability Cov Level",
    "Location Number": "Working Location"
  };
  const ALIASES: { [field: string]: string[] } = {
    "Relationship": ["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"],
    "Status Field": ["STATUS", "EMPLOYEE STATUS", "EMPLOYMENT STATUS", "COBRA STATUS", "EMP STATUS", "STATUS FIELD"],
    "Social Security Number": ["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SOC SEC NO", "SS", "MEMBER SSN", "TAX ID", "EMPLOYEE SOCIAL SECURITY NUMBER", "EMPLOYEE SSN", "SOCIAL SECURITY NUMBER NOT USED"],
    "Member Last Name": ["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"],
    "First Name": ["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"],
    "Middle Initial": ["MIDDLE INITIAL", "MI", "MIDDLE", "MIDDLE NAME", "MIDDLE INTIAL"],
    "Gender": ["GENDER", "SEX", "BIRTH SEX"],
    "Date of Birth": ["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"],
    "Date of Hire": ["DATE OF HIRE", "DOH", "HIRE DATE", "ORIGINAL HIRE DATE", "EMPLOYMENT DATE"],
    "Member Street Address": ["MEMBER STREET ADDRESS", "STREET ADDRESS", "ADDRESS", "ADDRESS 1", "ADDR1", "ADDR", "HOME ADDRESS", "MAILING ADDRESS"],
    "City": ["CITY", "TOWN", "MEMBER CITY"],
    "State": ["STATE", "ST", "PROVINCE", "MEMBER STATE", "STATE ABBREVIATION", "STATE ABBR", "ST ABBREVIATION", "ST ABBR", "STATE CODE", "USPS", "USPS STATE", "MEMBER STATE ABBREVIATION"],
    "Zip Code": ["ZIP", "ZIP CODE", "POSTAL CODE", "ZIP 4", "MEMBER ZIP", "MEMBER ZIP CODE", "ZIP CODE 5 DIGITS"],
    "Phone Number": ["PHONE", "PHONE NUMBER", "HOME PHONE", "PERSONAL PHONE", "MOBILE PHONE", "CELL PHONE", "CELL", "MOBILE", "WORK PHONE", "OFFICE PHONE", "BUSINESS PHONE", "TELEPHONE", "CONTACT NUMBER", "PHONE NUMBER NUMERIC ONLY"],
    "Email Address": ["EMAIL", "EMAIL ADDRESS", "E MAIL", "PERSONAL EMAIL", "HOME EMAIL", "WORK EMAIL", "OFFICE EMAIL", "BUSINESS EMAIL"],
    "Salary": ["ANNUAL SALARY", "SALARY", "SALARY AMOUNT", "ANNUAL INCOME", "INCOME", "COMPENSATION", "ANNUAL COMPENSATION", "BENEFIT SALARY", "COMP", "W2 WAGES", "BASE SALARY"],
    "Age": ["AGE", "CURRENT AGE", "EMPLOYEE AGE", "MEMBER AGE", "AGE AT EFFECTIVE DATE"],
    "Compensation Type": ["COMPENSATION TYPE", "SALARY TYPE", "PAY TYPE", "ANNUAL OR HOURLY", "ANNUAL HOURLY", "HOURLY OR ANNUAL", "PAY FREQUENCY"],
    "Hours Worked Per Week": ["HOURS WORKED PER WEEK", "HOURS WORKED", "HOURS", "SCHEDULED HOURS", "WEEKLY HOURS", "HRS"],
    "Occupation": ["OCCUPATION", "JOB TITLE", "POSITION", "JOB", "JOB CLASS"],
    "Disabled?": ["DISABLED", "DISABILITY", "IS DISABLED", "DISABLED DEPENDENT"],
    "Dental Plan Elected": ["DENTAL PLAN ELECTED", "DENTAL PLAN ELECTION", "DENTAL PLAN SELECTION", "DENTAL PLAN SELECT", "DENTAL PLAN NAME", "DENTAL PLAN", "DENTAL SELECT", "DENTAL ELECT", "DENTAL ELECTION", "DENTAL IF EMPLOYEE IS ELECTING DENTAL PLEASE SELECT PLAN"],
    "Dental Cov Level": ["DENTAL COVERAGE TIER", "DENTAL COVERAGE TYPE", "DENTAL COVERAGE LEVEL", "DENTAL COVERAGE", "DENTAL COV", "DENTAL COV TIER", "DENTAL COV TYPE", "DENTAL COV LEVEL", "DENTAL TIER", "DENTAL LEVEL"],
    "DHMO Provider Name": ["DHMO PROVIDER NAME", "DHMO PROVIDER", "DENTAL PCP", "DENTAL OFFICE", "DENTAL FACILITY", "DHMO"],
    "Dental Prior Current Carrier Name": ["PRIOR CARRIER", "PRIOR CARRIER NAME", "CURRENT CARRIER", "PREVIOUS CARRIER", "DENTAL PRIOR CARRIER NAME", "PRIOR CURRENT CARRIER NAME", "IF A MEMBER HAD DENTAL AND OR ORTHO COVERAGE WITHIN THE PAST 12 MONTHS PLEASE PROVIDE PRIOR CURRENT CARRIER NAME"],
    "Dental Prior Carrier Effective Date": ["PRIOR CARRIER EFFECTIVE DATE", "PRIOR CARRIER EFF DATE", "PRIOR EFFECTIVE DATE", "PRIOR COVERAGE START"],
    "Dental Prior Carrier Term Date": ["PRIOR CARRIER TERM DATE", "PRIOR CARRIER TERMINATION DATE", "PRIOR TERMINATION DATE", "PRIOR COVERAGE END"],
    "Dental Prior Carrier Ortho included": ["ORTHODONTIA INCLUDED", "WAS ORTHODONTIA INCLUDED", "ORTHODONTIA", "ORTHO", "PRIOR ORTHO", "ORTHO COVERAGE"],
    "Vision Plan Election": ["VISION PLAN ELECTED", "VISION PLAN ELECTION", "VISION PLAN SELECTION", "VISION PLAN SELECT", "VISION PLAN NAME", "VISION PLAN", "VISION SELECT", "VISION ELECT", "VISION ELECTION", "VISION IF EMPLOYEE IS ELECTING VISION PLEASE SELECT PLAN"],
    "Vision Cov Level": ["VISION COVERAGE TIER", "VISION COVERAGE TYPE", "VISION COVERAGE LEVEL", "VISION COVERAGE", "VISION COV", "VISION COV TIER", "VISION COV TYPE", "VISION COV LEVEL", "VISION TIER", "VISION LEVEL"],
    "Basic Life Cov Level": ["BASIC LIFE ELECTION", "BASIC LIFE", "BASIC LIFE COVERAGE", "BASIC LIFE COVERAGE TYPE", "BASIC LIFE COV LEVEL", "LIFE ELECTION", "GROUP LIFE", "EMPLOYER PAID LIFE", "BASIC LIFE ENROLL", "BASIC LIFE COVERAGGE TYPE", "LIFE BASIC LIFE ELECTION FLAT AMOUNT OR LIFE BY SALARY ELECT ENROLL OR WAIVE LIFE BY CLASS SELECT CLASS NUMBER OR WAIVE"],
    "Life/AD&D Class": ["LIFE/AD&D CLASS", "LIFE AD&D CLASS", "BASIC LIFE CLASS", "LIFE CLASS"],
    "Primary Life Beneficiary": ["PRIMARY BENEFICIARY", "BENEFICIARY", "PRIMARY LIFE BENEFICIARY", "BENE"],
    "Dependent Basic Life": ["DEPENDENT BASIC LIFE", "DEPENDENT BASIC LIFE COVERAGE LEVEL", "DEP BASIC LIFE COVERAGE LEVEL", "DEPENDENT LIFE", "DEP BASIC LIFE", "SPOUSE CHILD LIFE"],
    "Employee Voluntary Life Flat Amount": ["EMPLOYEE VOLUNTARY LIFE", "EMPLOYEE VOLUNTARY LIFE AMOUNT", "EMPLOYEE VOLUNTARY LIFE FLAT AMOUNT", "EE VOL LIFE", "EMPLOYEE VOL LIFE", "VOLUNTARY LIFE", "VOLUNTARY LIFE FLAT AMOUNT", "VOL LIFE", "SUPPLEMENTAL LIFE", "EMPLOYEE VOLUNTARY LIFE ADD", "VOLUNTARY LIFE ADD", "EE VOL LIFE AMT", "EMPLOYEE VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN IF NOT ELECTING ENTER 0"],
    "Spouse Voluntary Life Flat Amount": ["SPOUSAL VOLUNTARY LIFE", "SPOUSAL VOLUNTARY LIFE AMOUNT", "SPOUSAL VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOLUNTARY LIFE", "SPOUSE VOLUNTARY LIFE AMOUNT", "SPOUSE VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOL LIFE", "VOLUNTARY SPOUSE LIFE", "SP VOL LIFE", "SPOUSE VOLUNTARY LIFE ADD", "SPOUSAL VOLUNTARY LIFE ADD", "SPOUSAL VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN NOT TO EXCEED 50 OF THE EMPLOYEE ELECTION IF NOT ELECTING ENTER 0"],
    "Dependent Voluntary Life Flat Amount": ["DEPENDENT VOLUNTARY LIFE", "DEPENDENT VOLUNTARY LIFE AMOUNT", "DEPENDENT VOLUNTARY LIFE FLAT AMOUNT", "DEPENDENT VOL LIFE", "CHILD VOL LIFE", "VOLUNTARY CHILD LIFE", "DEP VOL LIFE", "DEPENDENT CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE ADD", "DEPENDENT VOLUNTARY LIFE ADD"],
    "Short-Term Disability Cov Level": ["STD ELECTION", "STD", "SHORT TERM DISABILITY", "STD ENROLL", "STD COVERAGE TYPE", "STD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "STD Class": ["STD CLASS", "SHORT TERM DISABILITY CLASS", "STD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "Long-Term Disability Cov Level": ["LTD ELECTION", "LTD", "LONG TERM DISABILITY", "LTD ENROLL", "LTD COVERAGE TYPE", "LTD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "LTD Class": ["LTD CLASS", "LONG TERM DISABILITY CLASS", "LTD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "Working Location": ["WORKING LOCATION", "WORK LOCATION", "LOCATION", "LOCATION NUMBER", "LOCATION CODE", "BRANCH", "SOLD QUOTE LOCATION"],
    "Billing Division": ["BILLING DIVISION", "DIVISION", "BILL DIVISION", "BILLING DIV"]
  };

  // Build normalized alias -> field lookup.
  const aliasToField = new Map<string, string>();
  for (const field of Object.keys(ALIASES)) {
    for (const a of ALIASES[field]) aliasToField.set(a, field);
  }
  for (const f of LMG_FIELDS) if (!aliasToField.has(normHeader(f))) aliasToField.set(normHeader(f), f);

  // State lookups — declared BEFORE the pre-pass call because the §11c
  // combined-column split (Stage S in preprocess) validates state tokens.
  const STATES: { [k: string]: string } = {
    "ALABAMA": "AL", "ALASKA": "AK", "ARIZONA": "AZ", "ARKANSAS": "AR", "CALIFORNIA": "CA",
    "COLORADO": "CO", "CONNECTICUT": "CT", "DELAWARE": "DE", "DISTRICT OF COLUMBIA": "DC",
    "FLORIDA": "FL", "GEORGIA": "GA", "HAWAII": "HI", "IDAHO": "ID", "ILLINOIS": "IL",
    "INDIANA": "IN", "IOWA": "IA", "KANSAS": "KS", "KENTUCKY": "KY", "LOUISIANA": "LA",
    "MAINE": "ME", "MARYLAND": "MD", "MASSACHUSETTS": "MA", "MICHIGAN": "MI", "MINNESOTA": "MN",
    "MISSISSIPPI": "MS", "MISSOURI": "MO", "MONTANA": "MT", "NEBRASKA": "NE", "NEVADA": "NV",
    "NEW HAMPSHIRE": "NH", "NEW JERSEY": "NJ", "NEW MEXICO": "NM", "NEW YORK": "NY",
    "NORTH CAROLINA": "NC", "NORTH DAKOTA": "ND", "OHIO": "OH", "OKLAHOMA": "OK", "OREGON": "OR",
    "PENNSYLVANIA": "PA", "RHODE ISLAND": "RI", "SOUTH CAROLINA": "SC", "SOUTH DAKOTA": "SD",
    "TENNESSEE": "TN", "TEXAS": "TX", "UTAH": "UT", "VERMONT": "VT", "VIRGINIA": "VA",
    "WASHINGTON": "WA", "WEST VIRGINIA": "WV", "WISCONSIN": "WI", "WYOMING": "WY",
    "PUERTO RICO": "PR", "GUAM": "GU", "VIRGIN ISLANDS": "VI", "AMERICAN SAMOA": "AS"
  };
  const VALID_USPS = new Set<string>(Object.keys(STATES).map(k => STATES[k]));

  // Declared before the pre-pass: its tier detection (normalizeTier) reads it.
  const WAIVE_WORDS = new Set<string>(["W", "WAIVE", "WAIVED", "WAIVES", "DECLINE", "DECLINED", "NONE", "N A", "NA", "WAIVE COVERAGE", "OPT OUT", "NO COVERAGE", "WAIVE WITH OTHER COVERAGE"]);

  // PRE-PASS: reshape stacked product rows / dependent columns into one clean
  // enrollee row per person. aliasToField lets the collapse ignore columns we
  // don't keep (Total Rate, premiums, etc.) when checking for conflicts.
  const shaped = preprocess(rawHeaders, rawData, aliasToField);
  const sourceHeaders = shaped.headers;
  const dataRows = shaped.rows;
  srcRowLabels = shaped.srcRows;
  addLog(`Read ${rawData.length} source row(s), ${rawHeaders.length} column(s) from "${sourceSheet.getName()}"; ${dataRows.length} enrollee row(s) after reshape.`);

  // ---- Fuzzy matching + HEADER MAP setup ----
  const fieldByNorm = new Map<string, string>(); // normalized field/alias name -> canonical field
  for (const f of LMG_FIELDS) fieldByNorm.set(normHeader(f), f);
  for (const f of Object.keys(ALIASES)) fieldByNorm.set(normHeader(f), f);
  for (const o of Object.keys(LEGACY_FIELD_NAMES)) fieldByNorm.set(normHeader(o), LEGACY_FIELD_NAMES[o]); // pre-3.0 names typed in Map To
  const fuzzyCandidates: { field: string, text: string }[] = [];
  for (const f of Object.keys(ALIASES)) {
    fuzzyCandidates.push({ field: f, text: normHeader(f) });
    for (const a of ALIASES[f]) fuzzyCandidates.push({ field: f, text: a });
  }
  const AUTO_THRESHOLD = 0.85;    // >= this similarity -> auto-apply (with a verify flag)
  const SUGGEST_THRESHOLD = 0.50; // >= this -> offer as a suggestion on HEADER MAP

  // Read user-confirmed mappings from a prior run's HEADER MAP sheet (they win).
  const userMap = new Map<string, string>(); // normalized source header -> LMG field
  const userSrc = new Map<string, string>(); // normalized source header -> original text
  const hmSheet = workbook.getWorksheet("HEADER MAP");
  const hmRange = hmSheet ? hmSheet.getUsedRange() : undefined;
  if (hmSheet && hmRange) {
    const hv = hmRange.getValues();
    if (hv.length >= 2) {
      const hh = hv[0].map(x => normHeader(String(x == null ? "" : x)));
      const srcIdx = hh.indexOf("SOURCE HEADER");
      let mapIdx = hh.indexOf("MAP TO LMG FIELD");
      if (mapIdx < 0) mapIdx = hh.indexOf("MAP TO");
      if (srcIdx >= 0 && mapIdx >= 0) {
        for (let r = 1; r < hv.length; r++) {
          const srcH = String(hv[r][srcIdx] == null ? "" : hv[r][srcIdx]).trim();
          const toH = String(hv[r][mapIdx] == null ? "" : hv[r][mapIdx]).trim();
          if (!srcH || !toH) continue;
          const fld = fieldByNorm.get(normHeader(toH));
          if (fld) { userMap.set(normHeader(srcH), fld); userSrc.set(normHeader(srcH), srcH); }
        }
      }
    }
  }
  if (userMap.size) addLog(`HEADER MAP: applied ${userMap.size} user-confirmed column mapping(s).`);

  // Map each LMG field to a source column index (first exact match wins).
  const fieldToCol = new Map<string, number>();
  const addressCols: number[] = [];
  const unmapped: string[] = [];
  const dupCols: string[][] = []; // [sourceHeader, lmgFieldItDuplicates]
  // Phone / Email can have several source columns. Instead of one winning, we keep
  // them all with a priority rank and pick the best non-blank value PER ROW.
  // Phone:  personal/home/generic (1) > cell/mobile (2) > work/office (3).
  // Email:  personal/home/generic (1) > work/office (2).
  const phoneCols: { col: number, rank: number }[] = [];
  const emailCols: { col: number, rank: number }[] = [];
  const phoneRank = (n: string): number => /CELL|MOBILE/.test(n) ? 2 : (/WORK|OFFICE|BUSINESS/.test(n) ? 3 : 1);
  const emailRank = (n: string): number => /WORK|OFFICE|BUSINESS/.test(n) ? 2 : 1;

  // colReview drives the HEADER MAP sheet (every column that wasn't a clean exact match).
  const colReview: { header: string, status: string, mapTo: string, suggested: string, conf: string }[] = [];
  // Known Humana products that are NOT part of the LMG ancillary upload. These are
  // recognized and set aside (not treated as "unrecognized"), so a full enrollment
  // census doesn't produce a wall of warnings for Medical/Medicare/CI/etc.
  const nonLmgCols: string[] = [];
  const NON_LMG_RE = /MEDICARE|HICN|HEALTH SAVINGS|\bHSA\b|\bHRA\b|\bFSA\b|\bFSD\b|\bMEDICAL\b|CRITICAL ILLNESS|\bACCIDENT\b|SUPPLEMENTAL HEALTH|TERM LIFE|WORKPLACE|WRITING AGENT|\bSAN\b|APPLICATION SIGNATURE|SEQUENCE NUMBER|WAIVE ALL|ELIMINATION PERIOD|BENEFIT DURATION/;
  for (let c = 0; c < sourceHeaders.length; c++) {
    const norm = normHeader(sourceHeaders[c]);
    if (norm === "") continue;
    // Address columns (Address 1/2/3, Addr2, Street Address 2, ...) merge together.
    if (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(norm) && !/EMAIL|IP/.test(norm)) {
      addressCols.push(c);
      continue;
    }
    // Priority: user-confirmed mapping > known non-LMG product > exact alias > fuzzy.
    let field = userMap.get(norm) || "";
    let how = field ? "user" : "";
    let suggested = "", conf = "";
    if (!field && NON_LMG_RE.test(norm)) { nonLmgCols.push(sourceHeaders[c]); continue; }
    if (!field) {
      const exact = aliasToField.get(norm);
      if (exact) { field = exact; how = "exact"; }
    }
    if (!field) {
      const fz = bestFuzzy(norm, fuzzyCandidates);
      suggested = fz.field; conf = Math.round(fz.score * 100) + "%";
      if (fz.score >= AUTO_THRESHOLD) { field = fz.field; how = "auto"; }
      else if (fz.score >= SUGGEST_THRESHOLD) { how = "suggest"; }
      else { how = "none"; }
    }
    // Route the resolved field.
    if (field === "Phone Number") phoneCols.push({ col: c, rank: phoneRank(norm) });
    else if (field === "Email Address") emailCols.push({ col: c, rank: emailRank(norm) });
    else if (field && !fieldToCol.has(field)) fieldToCol.set(field, c);
    else if (field) { dupCols.push([sourceHeaders[c], field]); how = "dup"; }
    else unmapped.push(sourceHeaders[c]);

    if (how === "auto") {
      addIssue("INFO", 0, "", sourceHeaders[c], "", `Auto-matched to '${field}' by similarity (${conf})`, "Verify on the HEADER MAP tab; clear its 'Map To' cell to undo");
    }
    if (how !== "exact") {
      colReview.push({ header: sourceHeaders[c], status: how, mapTo: (how === "user" || how === "auto") ? field : "", suggested: how === "dup" ? field : suggested, conf: how === "user" ? "confirmed" : conf });
    }
  }
  // Columns split by the pre-pass (§11c) appear on HEADER MAP with their targets.
  for (const sp of shaped.splits) {
    colReview.push({ header: sp.header, status: "split", mapTo: "", suggested: sp.into, conf: "auto" });
  }
  // Vertical-census columns (Plan Type, Coverage Tier, Benefit Amount...) cover many
  // coverages at once, so there is no one field to map them to.
  for (const pv of shaped.pivots) {
    colReview.push({ header: pv.header, status: "pivot", mapTo: "", suggested: pv.role, conf: "auto" });
  }
  // Order each group by priority, then by source column order for ties.
  phoneCols.sort((a, b) => a.rank - b.rank || a.col - b.col);
  emailCols.sort((a, b) => a.rank - b.rank || a.col - b.col);
  const coalesce = (cols: { col: number, rank: number }[], src: string[]): string => {
    for (const cc of cols) { const v = src[cc.col]; if (v != null && String(v).trim() !== "") return String(v).trim(); }
    return "";
  };
  if (phoneCols.length > 1) addLog(`Phone: coalescing ${phoneCols.length} columns per row by priority (personal > cell > work).`);
  if (emailCols.length > 1) addLog(`Email: coalescing ${emailCols.length} columns per row by priority (personal > work).`);
  if (addressCols.length) addLog(`Address merge: combining ${addressCols.length} source address column(s).`);
  if (unmapped.length) addLog(`Unmapped source columns kept only in RAW DATA: ${unmapped.join(", ")}.`);
  if (dupCols.length) addLog(`Duplicate/unused source columns (first match used): ${dupCols.map(d => d[0]).join(", ")}.`);
  if (nonLmgCols.length) addLog(`Non-LMG product columns set aside (${nonLmgCols.length}): ${nonLmgCols.join(", ")}.`);
  // Non-LMG product columns: recognized as a real Humana field but not part of the
  // LMG ancillary upload — INFO (not a warning), expected on full enrollment files.
  for (const x of nonLmgCols) {
    addIssue("INFO", 0, "", x, "", "Non-LMG product column (Medical / Medicare / Critical Illness / Accident / etc.)",
      "Not part of the LMG ancillary upload — kept in RAW DATA only. Expected on full enrollment censuses.");
  }
  // Flag unrecognized / duplicate source columns so nothing leaves the LMG upload
  // silently — the user sees them on VALIDATION ISSUES and Other Columns; the raw
  // values stay on RAW DATA.
  for (const u of unmapped) {
    addIssue("WARNING", 0, "", u, "", "Unrecognized column — not mapped to any LMG field",
      "Open the HEADER MAP tab, put the right LMG field in its 'Map To' cell, and re-run to include it. Data stays on RAW DATA meanwhile.");
  }
  for (const d of dupCols) {
    addIssue("WARNING", 0, "", d[0], "", `Duplicate source for '${d[1]}' — an earlier column already filled it`,
      "Only the first column was used. Data preserved on RAW DATA only; merge manually if needed.");
  }
  for (const f of LMG_FIELDS) {
    if (!fieldToCol.has(f) && !(f === "Member Street Address" && addressCols.length)) {
      // not present in source; will be blank in output (logged once, quietly)
    }
  }
  addLog("Header normalization complete (parenthetical text removed, aliases standardized).");

  // No Relationship column at all (common on quoting censuses): every row is an
  // employee. Without this, every row would be treated as a dependent and its
  // SSN / salary / tiers wiped.
  const noRelationshipCol = !fieldToCol.has("Relationship");
  if (noRelationshipCol) {
    addIssue("INFO", 0, "", "Relationship", "", "No Relationship column in the census; every row treated as Employee",
      "If the file also lists spouses/children, add a Relationship column and re-run");
    addLog("No Relationship column found: every row treated as Employee.");
  }

  // Does this group even carry each product? Controls blank-default behavior.
  const hasDental = fieldToCol.has("Dental Plan Elected") || fieldToCol.has("Dental Cov Level");
  const hasVision = fieldToCol.has("Vision Plan Election") || fieldToCol.has("Vision Cov Level");
  const hasSTD = fieldToCol.has("Short-Term Disability Cov Level") || fieldToCol.has("STD Class");
  const hasLTD = fieldToCol.has("Long-Term Disability Cov Level") || fieldToCol.has("LTD Class");

  // ======================================================================
  // 3. STATIC LOOKUP TABLES
  // ======================================================================
  // (STATES / VALID_USPS are declared above the pre-pass call — the §11c
  //  combined-column split needs them before this point.)

  const OCC_ABBR: [RegExp, string][] = [
    [/\bSENIOR\b/g, "SR"], [/\bJUNIOR\b/g, "JR"], [/\bINFORMATION TECHNOLOGY\b/g, "IT"],
    [/\bSYSTEMS?\b/g, "SYS"], [/\bADMINISTRATORS?\b/g, "ADMIN"], [/\bADMINISTRATION\b/g, "ADMIN"],
    [/\bSUPPORT\b/g, "SUP"], [/\bTECHNICIANS?\b/g, "TECH"], [/\bREPRESENTATIVES?\b/g, "REP"],
    [/\bBUSINESS\b/g, "BIZ"], [/\bOPERATIONS?\b/g, "OPS"], [/\bMANAGERS?\b/g, "MGR"],
    [/\bMANAGEMENT\b/g, "MGMT"], [/\bASSISTANTS?\b/g, "ASST"], [/\bDIRECTORS?\b/g, "DIR"],
    [/\bENGINEERS?\b/g, "ENGR"], [/\bSUPERVISORS?\b/g, "SUPV"], [/\bDEVELOPERS?\b/g, "DEV"],
    [/\bCOORDINATORS?\b/g, "COORD"], [/\bSPECIALISTS?\b/g, "SPEC"], [/\bGENERAL\b/g, "GEN"]
  ];

  // Extra waiver words for the Enroll/Waive fields only (Basic Life, Dependent
  // Basic Life, STD, LTD). Not added to WAIVE_WORDS: a "0" there means an amount.
  const ENROLL_WAIVE_NO = ["N", "NO", "0", "FALSE"];
  // Dental/Vision Cov Level waive text on LMG (owner decision 2026-09-27, reverting
  // 2026-09-10 for these two only). Basic Life / STD / LTD stay EE/Waive.
  const LMG_WAIVE_TEXT = "Waive with other coverage";

  // ======================================================================
  // 4. PER-ROW TRANSFORMATION  ->  LMG TABLE
  // ======================================================================
  const lmgRows: string[][] = [];
  const ageByRow: string[] = []; // QUOTE tab only; 1:1 with lmgRows
  const dupKeyCount = new Map<string, number>();

  for (let r = 0; r < dataRows.length; r++) {
    const src = dataRows[r];
    const excelRow = r + 2; // human-facing source row number (header is row 1)
    const get = (field: string): string => {
      const idx = fieldToCol.get(field);
      if (idx == null) return "";
      const v = src[idx];
      return v == null ? "" : String(v).trim();
    };

    // --- Names (needed for issue messages) ---
    const lastName = get("Member Last Name").toUpperCase();
    const firstName = get("First Name").toUpperCase();
    let middle = get("Middle Initial").toUpperCase();
    if (middle.length > 1) middle = middle.charAt(0);
    const memberName = (firstName + " " + lastName).trim() || `(row ${excelRow})`;

    if (!lastName) addIssue("ERROR", excelRow, memberName, "Member Last Name", "", "Missing last name", "Add the member's last name");
    if (!firstName) addIssue("ERROR", excelRow, memberName, "First Name", "", "Missing first name", "Add the member's first name");

    // --- Relationship ---
    const relRaw = get("Relationship");
    const relationship = noRelationshipCol ? "Employee" : normalizeRelationship(relRaw);
    if (relRaw && relationship === "") {
      addIssue("WARNING", excelRow, memberName, "Relationship", relRaw, "Unrecognized relationship value", "Confirm: Employee / Spouse / Domestic Partner / Child");
    }
    if (!relRaw && !noRelationshipCol) addIssue("WARNING", excelRow, memberName, "Relationship", "", "Missing relationship", "Set Employee / Spouse / Domestic Partner / Child");
    const relOut = relationship || relRaw;
    const isEmployee = relationship === "Employee";

    // --- Gender ---
    const genderRaw = get("Gender");
    const gender = normalizeGender(genderRaw);
    if (gender === "") {
      addIssue(genderRaw ? "WARNING" : "ERROR", excelRow, memberName, "Gender", genderRaw, genderRaw ? "Unrecognized gender" : "Missing gender", "Set M or F");
    }

    // --- Dates ---
    const dob = formatDate(get("Date of Birth"));
    if (get("Date of Birth") && dob === "") addIssue("ERROR", excelRow, memberName, "Date of Birth", get("Date of Birth"), "Unparseable date of birth", "Enter as MM/DD/YYYY");
    if (!get("Date of Birth") && !(QUOTE_MODE && get("Age"))) addIssue("ERROR", excelRow, memberName, "Date of Birth", "", "Missing date of birth", "Enter as MM/DD/YYYY");
    const age = ageFromDob(dob);
    if (dob && isFutureDate(dob)) addIssue("ERROR", excelRow, memberName, "Date of Birth", dob, "Date of birth is in the future", "Correct the date of birth (check the year)");
    else if (age > 100) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (over 100)`, "Check the year of birth");
    else if (age >= 65) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (65 or older)`, "Review — age may affect eligibility/rating");
    if (isEmployee && age >= 0 && age < 14) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Employee is ${age} years old (under 14)`, "Check the date of birth, or whether this row is a dependent");
    const doh = isEmployee ? formatDate(get("Date of Hire")) : ""; // hire date blanked on dependents
    if (isEmployee && get("Date of Hire") && doh === "") addIssue("WARNING", excelRow, memberName, "Date of Hire", get("Date of Hire"), "Unparseable hire date", "Enter as MM/DD/YYYY");
    if (doh) {
      const yearsSinceHire = ageFromDob(doh);
      if (isFutureDate(doh)) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is in the future", "Confirm the hire date (new hire starting later?)");
      else if (yearsSinceHire > 100) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, `Hire date is ${yearsSinceHire} years ago`, "Check the year of hire");
      if (dob && age >= 0 && yearsSinceHire >= 0 && age - yearsSinceHire < 14) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is before the employee turned 14", "Check the hire date and date of birth");
    }
    const priorEff = formatDate(get("Dental Prior Carrier Effective Date"));
    const priorTerm = formatDate(get("Dental Prior Carrier Term Date"));

    // --- SSN ---
    // SSN is kept on the EMPLOYEE only — removed from spouse / domestic partner / child.
    let ssn = "";
    if (isEmployee) {
      ssn = get("Social Security Number").replace(/\D/g, "");
      if (ssn && ssn.length < 9) {
        // Often an 8-digit SSN simply lost a leading zero — pad to 9 and flag for a spot-check.
        const padded = ("000000000" + ssn).slice(-9);
        addIssue("INFO", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN had ${ssn.length} digits; padded leading zero(s) to ${padded}`, "Spot-check that the padded SSN is correct");
        ssn = padded;
      } else if (ssn.length > 9) {
        addIssue("ERROR", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN has ${ssn.length} digits (more than 9)`, "Correct to a 9-digit SSN");
      }
      if (!ssn) addIssue("ERROR", excelRow, memberName, "Social Security Number", "", "Employee missing SSN", "Add 9-digit SSN");
    }

    // --- Address (merge) ---
    let street = "";
    if (addressCols.length) {
      const parts: string[] = [];
      for (const c of addressCols) { const v = src[c]; if (v != null && String(v).trim() !== "") parts.push(String(v).trim()); }
      street = parts.join(" ").replace(/\s+/g, " ").toUpperCase();
    } else {
      street = get("Member Street Address").toUpperCase();
    }

    // --- City / State / Zip ---
    const city = get("City").toUpperCase();
    let state = get("State").toUpperCase().trim();
    if (state.length > 2) {
      const abbr = STATES[state];
      if (abbr) state = abbr;
      else addIssue("WARNING", excelRow, memberName, "State", get("State"), "Unknown state name", "Use 2-letter USPS code");
    } else if (state.length === 2 && !VALID_USPS.has(state)) {
      addIssue("WARNING", excelRow, memberName, "State", get("State"), "Invalid 2-letter state", "Use a valid USPS code");
    } else if (isEmployee && state === "") {
      addIssue("ERROR", excelRow, memberName, "State", "", "Employee missing state", "Add the 2-letter USPS state");
    }
    let zip = get("Zip Code").replace(/\D/g, "");
    if (zip.length > 5) zip = zip.substring(0, 5);
    if (zip && zip.length !== 5 && zip.length !== 0) {
      // pad short zips that lost a leading zero
      if (zip.length < 5) { zip = ("00000" + zip).slice(-5); }
    }

    // --- Phone (coalesce multiple columns per row by priority) ---
    const phoneRaw = coalesce(phoneCols, src);
    let phone = phoneRaw.replace(/\D/g, "");
    if (phone.length === 11 && phone.charAt(0) === "1") phone = phone.substring(1);
    if (phone && phone.length !== 10) {
      addIssue("WARNING", excelRow, memberName, "Phone Number", phoneRaw, `Phone has ${phone.length} digits (need 10)`, "Correct to a 10-digit phone");
      phone = ""; // do not export an invalid phone
    }

    // --- Email (coalesce multiple columns per row by priority) ---
    const email = coalesce(emailCols, src);

    // --- Hours / Salary ---
    // Use the member's actual Hours Worked Per Week. Default to 40 ONLY when
    // that field is blank/invalid (e.g. 20 hrs -> rate x 20 x 52, not x 40).
    const hoursRaw = get("Hours Worked Per Week").replace(/[^\d.]/g, "");
    const hoursParsed = parseFloat(hoursRaw);
    const hoursProvided = !isNaN(hoursParsed) && hoursParsed > 0;
    const hoursNum = hoursProvided ? hoursParsed : 40; // 40 only as a fallback
    let hours = hoursProvided ? hoursRaw : (isEmployee ? "40" : "");
    const salRaw = get("Salary");
    let salary = "";
    if (salRaw) {
      // Prefer an explicit Compensation Type (Hourly/Annual); else detect "/hr" text.
      const compType = normHeader(get("Compensation Type"));
      const compHourly = compType === "HOURLY" || compType === "HOUR" || compType === "H" || compType === "HRLY";
      const compAnnual = compType === "ANNUAL" || compType === "SALARY" || compType === "SALARIED" || compType === "A" || compType === "YEARLY";
      const isHourly = compHourly || (!compAnnual && compType === "" && /hr|hour/i.test(salRaw));
      const num = parseFloat(salRaw.replace(/[^\d.]/g, ""));
      if (!isNaN(num)) {
        if (isHourly) {
          salary = String(Math.round(num * hoursNum * 52 * 100) / 100);
          addLog(`Hourly->annual: ${salRaw} x ${hoursNum}h x 52 = ${salary} (${memberName}).`);
          if (!hoursProvided) {
            addIssue("INFO", excelRow, memberName, "Hours Worked Per Week", "",
              "Hourly wage annualized using the default 40 hrs/week (no hours given)",
              "Enter the actual weekly hours if it is not 40");
          }
        } else {
          salary = String(num);
          if (num > 0 && num < 1000) {
            addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary looks like an hourly rate", "Confirm annual salary, or label the cell '/hr'");
          }
        }
      } else {
        addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary not numeric", "Enter a numeric salary");
      }
    }

    // --- Occupation ---
    let occ = get("Occupation").toUpperCase().replace(/\s+/g, " ").trim();
    if (occ) {
      for (const [re, rep] of OCC_ABBR) occ = occ.replace(re, rep);
      occ = occ.replace(/\s+/g, " ").trim();
      if (occ.length > 25) {
        // Cut at the last whole word that fits (hard cut only when one word is over 25).
        const cut = occ.lastIndexOf(" ", 25);
        const short = (cut > 0 ? occ.substring(0, cut) : occ.substring(0, 25)).trim();
        addIssue("WARNING", excelRow, memberName, "Occupation", get("Occupation"), `Occupation exceeds 25 chars after abbreviation; truncated to '${short}'`, "Check the shortened title, or shorten it yourself");
        occ = short;
      }
    }

    // --- Disabled ---
    let disabled = normalizeYesNo(get("Disabled?"));
    if (disabled === "") disabled = "No";

    // --- Coverage tiers ---
    const dentalTierRaw = get("Dental Cov Level");
    let dentalTier = normalizeTier(dentalTierRaw);
    if (dentalTierRaw && dentalTier === "") addIssue("WARNING", excelRow, memberName, "Dental Cov Level", dentalTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading");
    const visionTierRaw = get("Vision Cov Level");
    let visionTier = normalizeTier(visionTierRaw);
    if (visionTierRaw && visionTier === "") addIssue("WARNING", excelRow, memberName, "Vision Cov Level", visionTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading");

    // LMG output: W -> "Waive"; blank employee -> waive (only if product exists)
    let dentalTierOut = tierToLmg(dentalTier, dentalTierRaw, isEmployee, hasDental, LMG_WAIVE_TEXT);
    let visionTierOut = tierToLmg(visionTier, visionTierRaw, isEmployee, hasVision, LMG_WAIVE_TEXT);
    // Waived Dental/Vision -> Plan column blank on LMG (source value stays on RAW DATA).
    const planOut = (field: string, tierOut: string): string => {
      const v = get(field);
      if (tierOut !== LMG_WAIVE_TEXT) return v;
      if (v.trim()) addIssue("INFO", excelRow, memberName, field, v, "Coverage waived; plan left blank on LMG", "Original value kept on RAW DATA");
      return "";
    };

    // --- Voluntary life (employee rows). Employee/Spousal waived -> 0;
    //     Dependent waived -> "Waive". ---
    const eeVol = volLife(get("Employee Voluntary Life Flat Amount"), isEmployee, "0", false);
    const spVol = volLife(get("Spouse Voluntary Life Flat Amount"), isEmployee, "0", false);
    const chVol = volLife(get("Dependent Voluntary Life Flat Amount"), isEmployee, "Waive", true); // 0 or blank -> Waive
    // Flag voluntary-life amounts that are not in even thousands (e.g. 62,500).
    const oddThousand = (v: string, label: string) => {
      const n = parseFloat(v);
      if (!isNaN(n) && n > 0 && n % 1000 !== 0) addIssue("WARNING", excelRow, memberName, label, v, "Voluntary life amount is not in even thousands", "Verify the volume amount");
    };
    oddThousand(eeVol, "Employee Voluntary Life Flat Amount");
    oddThousand(spVol, "Spouse Voluntary Life Flat Amount");
    oddThousand(chVol, "Dependent Voluntary Life Flat Amount");
    if (!isEmployee && (WAIVE_WORDS.has(normHeader(get("Spouse Voluntary Life Flat Amount"))) === false) && get("Spouse Voluntary Life Flat Amount")) {
      addIssue("INFO", excelRow, memberName, "Spouse Voluntary Life Flat Amount", get("Spouse Voluntary Life Flat Amount"), "Spousal vol life on a dependent row", "Will roll up to employee in a future version; verify manually");
    }

    // --- Basic life + beneficiary ---
    const basicLife = normalizeEnrollWaive(get("Basic Life Cov Level"));
    let beneficiary = get("Primary Life Beneficiary");
    const present = (x: string): boolean => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x));
    const realVol = (x: string): boolean => x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x));
    // Primary Beneficiary defaults to ON FILE when blank and the employee is actually
    // enrolled in life: Basic Life elected (not waived) and/or real Employee Voluntary
    // Life. Spouse/child vol life alone does not insure the employee.
    const hasLife = present(basicLife) || realVol(eeVol);
    if (isEmployee && hasLife && !beneficiary) beneficiary = "ON FILE";

    // --- Assemble LMG record (must match LMG_FIELDS order) ---
    const rec: { [k: string]: string } = {
      "Relationship": relOut,
      "Status Field": get("Status Field"),
      "Social Security Number": ssn,
      "Member Last Name": lastName,
      "First Name": firstName,
      "Middle Initial": middle,
      "Gender": gender || genderRaw,
      "Date of Birth": dob,
      "Date of Hire": doh,
      "Member Street Address": street,
      "City": city,
      "State": state,
      "Zip Code": zip,
      "Phone Number": phone,
      "Email Address": email,
      "Working Location": get("Working Location"),
      "Billing Division": get("Billing Division"),
      "Salary": salary,
      "Hours Worked Per Week": hours,
      "Occupation": occ,
      "Disabled?": disabled,
      "Dental Plan Elected": planOut("Dental Plan Elected", dentalTierOut),
      "Dental Cov Level": dentalTierOut,
      "DHMO Provider Name": get("DHMO Provider Name"),
      "Dental Prior Current Carrier Name": get("Dental Prior Current Carrier Name"),
      "Dental Prior Carrier Effective Date": priorEff,
      "Dental Prior Carrier Term Date": priorTerm,
      "Dental Prior Carrier Ortho included": normalizeYesNo(get("Dental Prior Carrier Ortho included")),
      "Vision Plan Election": planOut("Vision Plan Election", visionTierOut),
      "Vision Cov Level": visionTierOut,
      "Basic Life Cov Level": basicLife,
      "Life/AD&D Class": get("Life/AD&D Class"),
      "Primary Life Beneficiary": beneficiary,
      "Dependent Basic Life": normalizeEnrollWaive(get("Dependent Basic Life")),
      "Employee Voluntary Life Flat Amount": eeVol,
      "Spouse Voluntary Life Flat Amount": spVol,
      "Dependent Voluntary Life Flat Amount": chVol,
      "Short-Term Disability Cov Level": normalizeEnrollWaive(get("Short-Term Disability Cov Level")),
      "STD Class": get("STD Class"),
      "Long-Term Disability Cov Level": normalizeEnrollWaive(get("Long-Term Disability Cov Level")),
      "LTD Class": get("LTD Class")
    };
    lmgRows.push(LMG_FIELDS.map(f => rec[f] == null ? "" : rec[f]));
    ageByRow.push(get("Age").replace(/[^\d]/g, ""));

    // --- Duplicate detection (flag, never silently merge) ---
    const key = `${lastName}|${firstName}|${dob}|${gender}`;
    dupKeyCount.set(key, (dupKeyCount.get(key) || 0) + 1);
    if (dupKeyCount.get(key)! > 1) {
      addIssue("WARNING", excelRow, memberName, "(row)", key, "Possible duplicate (same name + DOB + gender)", "Review; remove the duplicate enrollee if confirmed");
    }
  }

  addLog("State conversions complete.");
  addLog("Phone cleanup complete.");
  addLog("Date normalization complete.");
  addLog("Coverage normalization complete.");
  addLog(`LMG build complete: ${lmgRows.length} enrollee row(s).`);

  // ======================================================================
  // 4b. FAMILY ROLL-UP (by adjacency)
  // Dependents belong to the nearest Employee row above them. Roll their
  // elections up to the employee row and derive the employee's coverage tier
  // from who is enrolled. Demographics stay on the dependent rows.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const relI = ci("Relationship");
    const realAmt = (x: string): boolean => x != null && x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x));
    const isWaiveTier = (x: string): boolean => x === LMG_WAIVE_TEXT || WAIVE_WORDS.has(normHeader(x)) || normHeader(x).indexOf("WAIVE") >= 0;
    const nameOf = (r: string[]): string => (r[ci("First Name")] + " " + r[ci("Member Last Name")]).trim();
    const tierRank: { [k: string]: number } = { EE: 1, ES: 2, EC: 2, FAM: 3 };

    // Build families by adjacency: an Employee starts one; following Spouse/DP/Child rows join it.
    const fams: { emp: number, deps: number[] }[] = [];
    for (let i = 0; i < lmgRows.length; i++) {
      const rel = lmgRows[i][relI];
      if (rel === "Employee") fams.push({ emp: i, deps: [] });
      else if (fams.length && (rel === "Spouse" || rel === "Domestic Partner" || rel === "Child")) fams[fams.length - 1].deps.push(i);
    }
    const censusListsDependents = fams.some(f => f.deps.length > 0);
    let rolled = 0;
    for (const fam of fams) {
      const emp = lmgRows[fam.emp];
      const empName = nameOf(emp) || "(employee)";
      const empRow = fam.emp + 2; // LMG sheet row of the employee
      for (const di of fam.deps) {
        const dep = lmgRows[di];
        const rel = dep[relI];
        const isSp = rel === "Spouse" || rel === "Domestic Partner";
        const isCh = rel === "Child";
        // The dependent's own voluntary-life amount (first real among the vol-life columns).
        const depVol = [dep[ci("Employee Voluntary Life Flat Amount")], dep[ci("Spouse Voluntary Life Flat Amount")], dep[ci("Dependent Voluntary Life Flat Amount")]].filter(x => realAmt(x))[0] || "";
        if (isSp && depVol && !realAmt(emp[ci("Spouse Voluntary Life Flat Amount")])) {
          emp[ci("Spouse Voluntary Life Flat Amount")] = depVol; rolled++;
          addIssue("INFO", empRow, empName, "Spouse Voluntary Life Flat Amount", depVol, `Rolled up spouse ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount");
        }
        if (isCh && depVol && !realAmt(emp[ci("Dependent Voluntary Life Flat Amount")])) {
          emp[ci("Dependent Voluntary Life Flat Amount")] = depVol; rolled++;
          addIssue("INFO", empRow, empName, "Dependent Voluntary Life Flat Amount", depVol, `Rolled up child ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount");
        }
        // Dependent Basic Life election.
        const depBasic = [dep[ci("Dependent Basic Life")], dep[ci("Basic Life Cov Level")]].filter(x => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x)))[0] || "";
        if (depBasic && (emp[ci("Dependent Basic Life")].trim() === "" || WAIVE_WORDS.has(normHeader(emp[ci("Dependent Basic Life")])))) {
          emp[ci("Dependent Basic Life")] = normalizeEnrollWaive(depBasic); rolled++;
        }
        // Vol-life elections live on the EE line in LMG, not the dependent rows — blank them.
        dep[ci("Employee Voluntary Life Flat Amount")] = "";
        dep[ci("Spouse Voluntary Life Flat Amount")] = "";
        dep[ci("Dependent Voluntary Life Flat Amount")] = "";
      }
      // Derive each product's tier from who is actually ENROLLED in THAT product.
      // A dependent who WAIVED a product does not count toward its tier. Upgrade-only:
      // never lower a stated tier, and never raise it for a waived/absent dependent.
      for (const tf of ["Dental Cov Level", "Vision Cov Level"]) {
        const idx = ci(tf);
        const cur = emp[idx];
        if (cur === "" || isWaiveTier(cur)) continue; // employee not enrolled in this product
        if (tierRank[cur] == null) continue; // unrecognized tier kept as-is (already flagged); never overwrite it
        let spEnr = false, chEnr = false;
        for (const di of fam.deps) {
          const dv = lmgRows[di][idx];
          if (dv.trim() === "" || isWaiveTier(dv)) continue; // dependent waived / not on this product
          const rel = lmgRows[di][relI];
          if (rel === "Spouse" || rel === "Domestic Partner") spEnr = true;
          else if (rel === "Child") chEnr = true;
        }
        const famTier = spEnr && chEnr ? "FAM" : spEnr ? "ES" : chEnr ? "EC" : "EE";
        if ((tierRank[famTier] || 0) > (tierRank[cur] || 0)) {
          emp[idx] = famTier; rolled++;
          addIssue("INFO", empRow, empName, tf, `${cur} -> ${famTier}`, "Coverage tier raised — dependent(s) enrolled in this product", "Verify the tier");
        }
      }

      // ---- Family consistency flags (do not auto-fix — flag for review) ----
      let spouseRow = false, childRow = false;
      for (const di of fam.deps) { const r = lmgRows[di][relI]; if (r === "Spouse" || r === "Domestic Partner") spouseRow = true; else if (r === "Child") childRow = true; }
      const tiers = [emp[ci("Dental Cov Level")], emp[ci("Vision Cov Level")]];
      const wantsSpouse = tiers.indexOf("ES") >= 0 || tiers.indexOf("FAM") >= 0;
      const wantsChild = tiers.indexOf("EC") >= 0 || tiers.indexOf("FAM") >= 0;
      // A dependent is listed but no coverage tier includes them.
      if (spouseRow && !wantsSpouse) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Spouse is listed but not included in any coverage tier", "Review — confirm whether the spouse should be covered");
      if (childRow && !wantsChild) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Child(ren) listed but not included in any coverage tier", "Review — confirm whether the child(ren) should be covered");
      // The tier implies a dependent that isn't listed (only checked when the census lists dependents as rows).
      if (censusListsDependents) {
        if (wantsSpouse && !spouseRow) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Coverage tier includes a spouse, but no spouse is listed", "Review — add the spouse or correct the tier");
        if (wantsChild && !childRow) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Coverage tier includes children, but none are listed", "Review — add the child(ren) or correct the tier");
      }
    }
    if (rolled > 0) addLog(`Family roll-up: rolled ${rolled} dependent election(s)/tier(s) up to employee rows (by adjacency).`);
  }

  // ======================================================================
  // 4c. EMPLOYEE-ONLY FIELDS
  // Elections, employment/comp, AND address/contact belong on the EMPLOYEE line
  // only. Blank them on spouse / domestic partner / child rows (which keep just
  // Relationship, name, gender, DOB, Disabled?). Runs AFTER the family roll-up so
  // dependent values are read first.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const relI = ci("Relationship");
    const EMPLOYEE_ONLY = [
      "Member Street Address", "City", "State", "Zip Code", "Phone Number", "Email Address",
      "Status Field", "Date of Hire", "Working Location", "Billing Division",
      "Salary", "Hours Worked Per Week", "Occupation",
      "Dental Plan Elected", "Dental Cov Level", "DHMO Provider Name",
      "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date",
      "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included",
      "Vision Plan Election", "Vision Cov Level",
      "Basic Life Cov Level", "Primary Life Beneficiary", "Dependent Basic Life",
      "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
      "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class"
    ];
    const idxs = EMPLOYEE_ONLY.map(f => ci(f)).filter(i => i >= 0);
    let blanked = 0;
    for (const r of lmgRows) {
      if (r[relI] === "Employee") continue;
      for (const i of idxs) if (r[i] !== "") { r[i] = ""; blanked++; }
    }
    if (blanked > 0) addLog(`Cleared ${blanked} employee-only field value(s) from dependent rows.`);
  }

  // ======================================================================
  // 4d. SPECIAL-CHARACTER CLEANING
  // Replace special characters with a space (# -> " UNIT ") across the census,
  // EXCEPT date fields, the email column, numeric fields (which keep decimals) and
  // the Dental/Vision Cov Level. Names and plan/class values keep ' and -.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const EXEMPT = new Set<number>([
      "Date of Birth", "Date of Hire", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date",
      "Email Address", "Social Security Number", "Phone Number", "Zip Code",
      "Salary", "Hours Worked Per Week", "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
      "Dental Cov Level", "Vision Cov Level" // normalized tiers have no specials; an unrecognized tier (EE+1) stays verbatim for review
    ].map(f => ci(f)).filter(i => i >= 0));
    // Names and plan/class values keep apostrophes and hyphens (O'BRIEN, SMITH-JONES,
    // PPO-1500) - rules section 8b.
    const KEEP_APOS_HYPHEN = new Set<number>([
      "Member Last Name", "First Name", "Middle Initial",
      "Dental Plan Elected", "Vision Plan Election", "Life/AD&D Class", "STD Class", "LTD Class"
    ].map(f => ci(f)).filter(i => i >= 0));
    let cleaned = 0;
    for (const r of lmgRows) {
      for (let c = 0; c < r.length; c++) {
        if (EXEMPT.has(c)) continue;
        const v = r[c];
        if (v === "") continue;
        const nv = KEEP_APOS_HYPHEN.has(c) ? cleanSpecialKeep(v) : cleanSpecial(v);
        if (nv !== v) { r[c] = nv; cleaned++; }
      }
    }
    if (cleaned > 0) addLog(`Special-character cleanup: normalized ${cleaned} cell(s).`);
  }

  // ======================================================================
  // 5. NCAM  (employees only, quoting layout)
  // ======================================================================
  const ncamBaseHeaders = ["Last Name", "Gender", "Date of Birth", "Dental Coverage Type", "Vision Coverage Type"];
  const ncamStdLtdHeaders = ["Salary", "Occupation", "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class"];
  const includeStdLtd = hasSTD || hasLTD;
  const ncamHeaders = includeStdLtd ? ncamBaseHeaders.concat(ncamStdLtdHeaders) : ncamBaseHeaders;

  const colIdx = (f: string) => LMG_FIELDS.indexOf(f);
  const ncamRows: string[][] = [];
  for (let i = 0; i < lmgRows.length; i++) {
    if (lmgRows[i][colIdx("Relationship")] !== "Employee") continue;
    const row = lmgRows[i];
    // Convert "Waive" back to W; blank employee -> W
    const dt = lmgToNcamTier(row[colIdx("Dental Cov Level")], hasDental);
    const vt = lmgToNcamTier(row[colIdx("Vision Cov Level")], hasVision);
    const base = [row[colIdx("Member Last Name")], row[colIdx("Gender")], row[colIdx("Date of Birth")], dt, vt];
    const extra = includeStdLtd
      ? [row[colIdx("Salary")], row[colIdx("Occupation")], row[colIdx("Short-Term Disability Cov Level")], row[colIdx("STD Class")], row[colIdx("Long-Term Disability Cov Level")], row[colIdx("LTD Class")]]
      : [];
    ncamRows.push(base.concat(extra));
  }
  addLog(`NCAM build complete: ${ncamRows.length} employee row(s)${includeStdLtd ? " (STD/LTD columns included)" : ""}.`);

  // ======================================================================
  // 5b. QUOTE  (quote mode: one line per employee, dependents purged)
  // Dependents only show up as the derived Dental/Vision tier. No plan names,
  // basic life or voluntary life. Columns blank for every employee are left off.
  // ======================================================================
  const QUOTE_COLS: [string, string][] = [
    ["Last Name", "Member Last Name"], ["First Name", "First Name"], ["Gender", "Gender"],
    ["Date of Birth", "Date of Birth"], ["Age", ""],
    ["Dental Coverage Type", "Dental Cov Level"], ["Vision Coverage Type", "Vision Cov Level"],
    ["Salary", "Salary"], ["Occupation", "Occupation"], ["Date of Hire", "Date of Hire"],
    ["Short-Term Disability Cov Level", "Short-Term Disability Cov Level"], ["STD Class", "STD Class"],
    ["Long-Term Disability Cov Level", "Long-Term Disability Cov Level"], ["LTD Class", "LTD Class"],
    ["City", "City"], ["State", "State"], ["Zip Code", "Zip Code"]
  ];
  const quoteAll: string[][] = [];
  for (let i = 0; i < lmgRows.length; i++) {
    const row = lmgRows[i];
    if (row[colIdx("Relationship")] !== "Employee") continue;
    quoteAll.push(QUOTE_COLS.map(q => { const out = q[0], f = q[1]; return (
      out === "Age" ? ageByRow[i]
        : out === "Dental Coverage Type" ? (hasDental ? lmgToNcamTier(row[colIdx(f)], true) : "")
          : out === "Vision Coverage Type" ? (hasVision ? lmgToNcamTier(row[colIdx(f)], true) : "")
            : row[colIdx(f)]); }));
  }
  const QUOTE_KEEP = new Set<string>(["Last Name", "Gender"]);
  const quoteKept: number[] = [];
  for (let c = 0; c < QUOTE_COLS.length; c++) {
    if (QUOTE_KEEP.has(QUOTE_COLS[c][0]) || quoteAll.some(r => r[c] !== "")) quoteKept.push(c);
  }
  // DOB or Age is required; keep DOB visible when neither column has data.
  if (quoteKept.every(c => QUOTE_COLS[c][0] !== "Date of Birth" && QUOTE_COLS[c][0] !== "Age")) { quoteKept.push(3); quoteKept.sort((a, b) => a - b); }
  const quoteHeaders = quoteKept.map(c => QUOTE_COLS[c][0]);
  const quoteRows = quoteAll.map(r => quoteKept.map(c => r[c]));
  if (QUOTE_MODE) addLog(`QUOTE build complete: ${quoteRows.length} employee line(s); ${lmgRows.length - quoteRows.length} dependent/other row(s) purged (their tiers are on the employee line).`);

  // ======================================================================
  // 6. SUMMARY
  // ======================================================================
  const relCount: { [k: string]: number } = { "Employee": 0, "Spouse": 0, "Domestic Partner": 0, "Child": 0, "Other/Unknown": 0 };
  for (const row of lmgRows) {
    const rel = row[colIdx("Relationship")];
    if (relCount[rel] != null) relCount[rel]++; else relCount["Other/Unknown"]++;
  }
  const summary: string[][] = [];
  summary.push(["LMG / NCAM CENSUS SUMMARY", ""]);
  summary.push(["Engine version", `${ENGINE_VERSION} (build ${ENGINE_BUILD})`]);
  summary.push(["Check for updates", "lmg-ncam-engine.netlify.app - if the site shows a newer version, re-copy the script"]);
  summary.push(["", ""]);
  summary.push(["Enrollment counts", ""]);
  summary.push(["Employees", String(relCount["Employee"])]);
  summary.push(["Spouses", String(relCount["Spouse"])]);
  summary.push(["Domestic Partners", String(relCount["Domestic Partner"])]);
  summary.push(["Children", String(relCount["Child"])]);
  if (relCount["Other/Unknown"]) summary.push(["Other / Unknown relationship", String(relCount["Other/Unknown"])]);
  summary.push(["Total LMG rows", String(lmgRows.length)]);
  summary.push(QUOTE_MODE ? ["QUOTE employee lines", String(quoteRows.length)] : ["NCAM employee rows", String(ncamRows.length)]);
  summary.push(["", ""]);

  // Tier breakdown per product (counts enrollees by EE/ES/EC/FAM/W on the employee tier value)
  function tierBlock(label: string, present: boolean, ncamTierResolver: (raw: string) => string, lmgFieldName: string) {
    if (!present) return;
    const counts: { [k: string]: number } = { EE: 0, ES: 0, EC: 0, FAM: 0, W: 0, OTHER: 0 };
    for (const row of lmgRows) {
      if (row[colIdx("Relationship")] !== "Employee") continue;
      const t = ncamTierResolver(row[colIdx(lmgFieldName)]);
      if (counts[t] != null) counts[t]++; else counts["OTHER"]++;
    }
    const enrolled = counts.EE + counts.ES + counts.EC + counts.FAM;
    summary.push([label + " (by employee tier)", ""]);
    summary.push(["  EE", String(counts.EE)]);
    summary.push(["  ES", String(counts.ES)]);
    summary.push(["  EC", String(counts.EC)]);
    summary.push(["  FAM", String(counts.FAM)]);
    summary.push(["  Enrolled (EE+ES+EC+FAM)", String(enrolled)]);
    summary.push(["  Waived (W)", String(counts.W)]);
    summary.push(["  Eligible (Enrolled + Waived)", String(enrolled + counts.W)]);
    if (counts.OTHER) summary.push(["  Unclassified", String(counts.OTHER)]);
    summary.push(["", ""]);
  }
  tierBlock("Dental", hasDental, (raw) => lmgToNcamTier(raw, true), "Dental Cov Level");
  tierBlock("Vision", hasVision, (raw) => lmgToNcamTier(raw, true), "Vision Cov Level");

  // (The issue tally is added to SUMMARY in section 7, after the last issue is
  // recorded, so the "Required column is entirely blank" errors are counted too.)

  // ======================================================================
  // 7. WRITE ALL SHEETS
  // ======================================================================
  // Tab colors: LMG & NCAM green; every other generated tab navy.
  const TAB_PRIMARY = "#5C9A1B";   // LMG, NCAM
  const TAB_SECONDARY = "#003057"; // SUMMARY, VALIDATION ISSUES, PROCESSING LOG, RAW DATA, Other Columns

  // Drop entirely-blank LMG columns — blank columns break the LMG upload. Their
  // names are listed on the "Other Columns" tab. NCAM and SUMMARY read from the
  // full unfiltered records (lmgRows), so they are unaffected.
  // REQUIRED columns are ALWAYS kept (never dropped) — if a required column is
  // entirely blank it is flagged as an ERROR so the data gets filled in.
  const REQUIRED = new Set<string>(QUOTE_MODE ? ["Member Last Name", "Gender"]
    : ["Relationship", "Member Last Name", "First Name", "Gender", "Date of Birth", "State"]);
  // Voluntary Life columns only appear if there are real elections — a column
  // that is blank or "0" for everyone is dropped (an all-waived line of coverage).
  const ZERO_IS_BLANK = new Set<string>(["Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount"]);
  // A Plan column with only one distinct value is left off — the system auto-selects
  // the single available plan.
  const PLAN_DROP_IF_SINGLE = new Set<string>(["Dental Plan Elected", "Vision Plan Election"]);
  const keptCols: number[] = [];
  const blankCols: string[] = [];
  const singlePlanCols: string[] = [];
  for (let c = 0; c < LMG_FIELDS.length; c++) {
    const zeroBlank = ZERO_IS_BLANK.has(LMG_FIELDS[c]);
    let hasData = false;
    for (let r = 0; r < lmgRows.length; r++) {
      const v = lmgRows[r][c];
      if (v !== "" && v != null && !(zeroBlank && (v === "0" || WAIVE_WORDS.has(normHeader(v))))) { hasData = true; break; }
    }
    // Drop a Plan column that only ever holds one distinct value.
    if (hasData && PLAN_DROP_IF_SINGLE.has(LMG_FIELDS[c])) {
      const distinct = new Set<string>();
      for (let r = 0; r < lmgRows.length; r++) { const v = lmgRows[r][c]; if (v !== "" && v != null) distinct.add(String(v).trim().toUpperCase()); }
      if (distinct.size <= 1) { singlePlanCols.push(LMG_FIELDS[c]); continue; }
    }
    if (hasData || REQUIRED.has(LMG_FIELDS[c])) {
      keptCols.push(c);
      if (!hasData) addIssue("ERROR", 0, "", LMG_FIELDS[c], "", "Required column is entirely blank", "Provide this required field for every enrollee");
    } else {
      blankCols.push(LMG_FIELDS[c]);
    }
  }
  if (singlePlanCols.length) addLog(`Single-plan columns left off (system auto-selects): ${singlePlanCols.join(", ")}.`);
  const keptCount = keptCols.length;
  const lmgHeaderOut = keptCols.map(i => LMG_FIELDS[i]);
  const lmgRowsOut = lmgRows.map(row => keptCols.map(i => row[i]));
  if (blankCols.length) addLog(`Removed ${blankCols.length} blank column(s) from LMG (see Other Columns): ${blankCols.join(", ")}.`);

  // Quote mode: issues about LMG-only fields (SSN, plans, life, address...) don't
  // apply to a quote. They are counted in the log, not shown.
  if (QUOTE_MODE) {
    const quoteFields = new Set<string>(QUOTE_COLS.map(q => q[1]).concat(["Relationship", "(row)", "", "Age"]));
    const before = issues.length;
    const keep = issues.filter(it => quoteFields.has(it[4]));
    issues.length = 0;
    for (const it of keep) { it[2] = ""; issues.push(it); }
    if (before > keep.length) addLog(`Quote mode: ${before - keep.length} issue(s) about LMG-only fields not listed (run the LMG/NCAM script to see them).`);
  }

  // Issue tally - every issue has been recorded by this point.
  let nErr = 0, nWarn = 0, nInfo = 0;
  for (const it of issues) { if (it[0] === "ERROR") nErr++; else if (it[0] === "WARNING") nWarn++; else nInfo++; }
  summary.push(["Validation results", ""]);
  summary.push(["Errors", String(nErr)]);
  summary.push(["Warnings", String(nWarn)]);
  summary.push(["Info", String(nInfo)]);
  addLog(`Validation complete: ${nErr} error(s), ${nWarn} warning(s), ${nInfo} info.`);

  // Snapshot RAW DATA first (only if it does not already exist).
  if (!workbook.getWorksheet("RAW DATA")) {
    const rawSheet = workbook.addWorksheet("RAW DATA");
    if (raw.length && raw[0].length) rawSheet.getRangeByIndexes(0, 0, raw.length, raw[0].length).setValues(raw);
    addLog("RAW DATA created (untouched copy of original census).");
  }
  const rawSheetRef = workbook.getWorksheet("RAW DATA");
  if (rawSheetRef) rawSheetRef.setTabColor(TAB_SECONDARY);

  // Remove the other script's output tabs so a workbook never mixes LMG and QUOTE results.
  for (const n of (QUOTE_MODE ? ["LMG", "NCAM", "Other Columns"] : ["QUOTE"])) {
    const old = workbook.getWorksheet(n);
    if (old) old.delete();
  }

  // Recreate output sheets.
  const firstSheet = QUOTE_MODE
    ? writeSheet(workbook, "QUOTE", [quoteHeaders].concat(quoteRows), "#1F4E79", TAB_PRIMARY)
    : writeSheet(workbook, "LMG", [lmgHeaderOut].concat(lmgRowsOut), "#1F4E79", TAB_PRIMARY);
  const lmgSheet = firstSheet;
  const ncamSheet = QUOTE_MODE ? null : writeSheet(workbook, "NCAM", [ncamHeaders].concat(ncamRows), "#2E75B6", TAB_PRIMARY);
  writeSheet(workbook, "SUMMARY", summary, "#548235", TAB_SECONDARY);
  const issuesHeader = ["SEVERITY", "SOURCE ROW (RAW DATA)", "LMG ROW", "MEMBER NAME", "COLUMN", "VALUE", "ISSUE", "RECOMMENDED ACTION"];
  const issuesGrid = issues.length ? issues : [["INFO", "", "", "", "", "", "No issues found", ""]];
  const issuesSheet = writeSheet(workbook, "VALIDATION ISSUES", [issuesHeader].concat(issuesGrid), "#C00000", TAB_SECONDARY);
  writeSheet(workbook, "PROCESSING LOG", [["STEP"]].concat(log.map(l => [l])), "#7F7F7F", TAB_SECONDARY);
  // "Other Columns" = everything NOT in the LMG upload, each with a reason.
  // (Unrecognized/duplicate source columns are flagged earlier, in section 2,
  // so they reach the VALIDATION ISSUES sheet and the SUMMARY tally. The data
  // itself is always preserved on RAW DATA.)
  const otherRows: string[][] = [];
  for (const u of unmapped) otherRows.push([u, "Unrecognized source header — data kept in RAW DATA only; NOT in the LMG upload"]);
  for (const d of dupCols) otherRows.push([d[0], `Duplicate of '${d[1]}' (already mapped) — data kept in RAW DATA only; NOT in the LMG upload`]);
  for (const b of blankCols) otherRows.push([b, "LMG field blank — excluded from upload"]);
  for (const s of singlePlanCols) otherRows.push([s, "Single plan option — system auto-selects; column left off the upload"]);
  for (const pv of shaped.pivots) otherRows.push([pv.header, pv.role]);
  for (const x of nonLmgCols) otherRows.push([x, "Recognized non-LMG product (Medical/Medicare/Critical Illness/etc.) — not part of the LMG ancillary upload"]);
  const otherGrid = otherRows.length
    ? [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"]].concat(otherRows)
    : [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"], ["None", "Every source column mapped and every LMG column had data."]];
  if (!QUOTE_MODE) writeSheet(workbook, "Other Columns", otherGrid, "#AF0061", "#AF0061"); // Plum tab

  // HEADER MAP — the user confirms unrecognized columns here; the tool applies
  // them on the next run and remembers them. Fill the 'Map To' cell with an LMG
  // field name and re-run to include that column.
  const hmHeaderRow = ["SOURCE HEADER", "MAP TO LMG FIELD", "SUGGESTED", "CONFIDENCE", "STATUS", "SHARE (copy → submit on hub)"];
  const hmRows: string[][] = [];
  const hmSeen = new Set<string>();
  const shareLine = (src: string, to: string): string => to ? `${src} | ${to}` : "";
  for (const cr of colReview) {
    hmSeen.add(normHeader(cr.header));
    let status = "No close match — type the LMG field in Map To and re-run";
    if (cr.status === "user") status = "Applied — your mapping";
    else if (cr.status === "auto") status = "Applied automatically — verify (clear Map To to undo)";
    else if (cr.status === "suggest") status = "Suggested — put a field in Map To and re-run to include";
    else if (cr.status === "dup") status = "Duplicate of another column — not used";
    else if (cr.status === "pivot") status = "Used by the vertical-census pivot - no mapping needed";
    else if (cr.status === "split") status = "Split automatically into the fields under SUGGESTED — original kept in RAW DATA";
    const suggested = cr.suggested || (cr.status === "none" ? "(no close match)" : "");
    hmRows.push([cr.header, cr.mapTo, suggested, cr.conf, status, shareLine(cr.header, cr.mapTo)]);
  }
  // Preserve any prior user mappings for columns not present in this file.
  userMap.forEach((fld, normSrc) => {
    if (!hmSeen.has(normSrc)) { const s = userSrc.get(normSrc) || normSrc; hmRows.push([s, fld, "", "confirmed", "Applied — your mapping (column not in this file)", shareLine(s, fld)]); }
  });
  const hmGrid = hmRows.length
    ? [hmHeaderRow].concat(hmRows)
    : [hmHeaderRow, ["(none)", "", "", "", "Every column mapped cleanly — nothing to confirm"]];
  writeSheet(workbook, "HEADER MAP", hmGrid, "#C55A11", "#C55A11"); // orange tab
  // Every LMG field in column H, and 'Map To' becomes a dropdown of that list
  // (warning style, so the legacy pre-3.0 names can still be typed by hand).
  const hmWs = workbook.getWorksheet("HEADER MAP");
  const optCol = hmHeaderRow.length + 1; // one blank column gap
  hmWs.getRangeByIndexes(0, optCol, LMG_FIELDS.length + 1, 1).setValues([["LMG FIELDS (Map To options)"]].concat(LMG_FIELDS.map(f => [f])));
  const optHdr = hmWs.getRangeByIndexes(0, optCol, 1, 1).getFormat();
  optHdr.getFill().setColor("#C55A11"); optHdr.getFont().setColor("#FFFFFF"); optHdr.getFont().setBold(true);
  hmWs.getRangeByIndexes(0, optCol, 1, 1).getEntireColumn().getFormat().autofitColumns();
  const optLetter = String.fromCharCode(65 + optCol);
  const dv = hmWs.getRangeByIndexes(1, 1, Math.max(hmRows.length, 1) + 50, 1).getDataValidation(); // +50 rows for hand-added headers
  dv.setRule({ list: { inCellDropDown: true, source: `=$${optLetter}$2:$${optLetter}$${LMG_FIELDS.length + 1}` } });
  dv.setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.warning, title: "Not an LMG field", message: "Pick a field from the list in column " + optLetter + " (legacy names still work — choose Yes to keep)." });

  // ---- Highlight validation issues by severity ----
  const HL: { [k: string]: string } = { INFO: "#BDD7EE", WARNING: "#FFEB9C", ERROR: "#FFC7CE" };
  const sevRank: { [k: string]: number } = { INFO: 0, WARNING: 1, ERROR: 2 };
  // LMG tab: color each offending cell. Apply INFO -> WARNING -> ERROR so the
  // most severe color wins when a cell has more than one issue.
  const ordered = QUOTE_MODE ? [] : issues.slice().sort((a, b) => (sevRank[a[0]] || 0) - (sevRank[b[0]] || 0));
  for (const it of ordered) {
    const rowNum = parseInt(it[2], 10); // LMG ROW
    if (isNaN(rowNum)) continue;
    const rIdx = rowNum - 1; // header at sheet row 0; data rows occupy 1..N
    if (rIdx < 1 || rIdx > lmgRowsOut.length) continue;
    const color = HL[it[0]] || HL.INFO;
    if (it[4] === "(row)") {
      lmgSheet.getRangeByIndexes(rIdx, 0, 1, keptCount).getFormat().getFill().setColor(color);
    } else {
      const full = LMG_FIELDS.indexOf(it[4]);
      const fc = full >= 0 ? keptCols.indexOf(full) : -1;
      if (fc >= 0) lmgSheet.getRangeByIndexes(rIdx, fc, 1, 1).getFormat().getFill().setColor(color);
    }
  }
  // VALIDATION ISSUES tab: color each SEVERITY cell to match.
  if (issues.length) {
    for (let i = 0; i < issues.length; i++) {
      const color = HL[issues[i][0]];
      if (color) issuesSheet.getRangeByIndexes(i + 1, 0, 1, 1).getFormat().getFill().setColor(color);
    }
  }

  // Tab order: LMG is #1 (LaunchMyGroup uploads the first sheet only), NCAM #2.
  firstSheet.setPosition(0);
  if (ncamSheet) ncamSheet.setPosition(1);
  firstSheet.activate();

  // ----------------------------------------------------------------------
  // Helper: replace-or-create a sheet, write grid, style header row.
  // ----------------------------------------------------------------------
  function writeSheet(wb: ExcelScript.Workbook, name: string, grid: string[][], headerHex: string, tabColor: string) {
    const existing = wb.getWorksheet(name);
    if (existing) existing.delete();
    const ws = wb.addWorksheet(name);
    ws.setTabColor(tabColor);
    writeGrid(ws, grid);
    if (grid.length && grid[0].length) {
      const hdr = ws.getRangeByIndexes(0, 0, 1, grid[0].length);
      hdr.getFormat().getFill().setColor(headerHex);
      hdr.getFormat().getFont().setColor("#FFFFFF");
      hdr.getFormat().getFont().setBold(true);
      const used = ws.getUsedRange();
      if (used) used.getFormat().autofitColumns();
      ws.getFreezePanes().freezeRows(1);
    }
    return ws;
  }

  function writeGrid(ws: ExcelScript.Worksheet, grid: string[][]) {
    if (!grid.length) return;
    const rows = grid.length;
    const cols = grid.reduce((m, r) => Math.max(m, r.length), 0);
    // pad ragged rows
    const padded = grid.map(r => { const a = r.slice(); while (a.length < cols) a.push(""); return a; });
    ws.getRangeByIndexes(0, 0, rows, cols).setValues(padded);
  }

  // ======================================================================
  // PURE HELPER FUNCTIONS
  // ======================================================================
  function cleanSpecial(v: string): string {
    // "#" -> " UNIT "; every other non-alphanumeric (except space) -> space; collapse.
    return v.replace(/#/g, " UNIT ").replace(/[^A-Za-z0-9 ]+/g, " ").replace(/\s+/g, " ").trim();
  }

  // Names / plan / class values: like cleanSpecial but apostrophes and hyphens stay
  // (curly apostrophes become straight ones).
  function cleanSpecialKeep(v: string): string {
    return v.replace(/[\u2018\u2019]/g, "'").replace(/[^A-Za-z0-9 '\-]+/g, " ").replace(/\s+/g, " ").trim();
  }

  // True when an MM/DD/YYYY date is after today.
  function isFutureDate(mmddyyyy: string): boolean {
    const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
    if (!m) return false;
    const now = new Date();
    const d = parseInt(m[3], 10) * 10000 + parseInt(m[1], 10) * 100 + parseInt(m[2], 10);
    const t = now.getFullYear() * 10000 + (now.getMonth() + 1) * 100 + now.getDate();
    return d > t;
  }

  function ageFromDob(mmddyyyy: string): number {
    const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
    if (!m) return -1;
    const mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10);
    const now = new Date();
    let age = now.getFullYear() - yr;
    const moNow = now.getMonth() + 1, daNow = now.getDate();
    if (moNow < mo || (moNow === mo && daNow < da)) age--;
    return age;
  }

  function normHeader(h: string): string {
    let s = String(h == null ? "" : h);
    // Drop parenthetical text, innermost-out, so nested parens like "Child(ren)"
    // inside an instruction "(... Employee +Child(ren), Family, Waive)" clear fully.
    let prev = "";
    while (s !== prev) { prev = s; s = s.replace(/\([^()]*\)/g, " "); }
    s = s.replace(/\([\s\S]*$/, " "); // drop any unmatched "(" to end of string
    // A leftover ")" means the parens were unbalanced — e.g. real-world header
    // "Status (Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date
    // and Event Type)". Everything from the first "(" was instruction text, so
    // fall back to the part of the original header before that "(".
    if (s.indexOf(")") !== -1) {
      const orig = String(h == null ? "" : h);
      const p = orig.indexOf("(");
      s = p >= 0 ? orig.slice(0, p) : s.replace(/\)/g, " ");
    }
    return s.toUpperCase()
      .replace(/[^A-Z0-9]+/g, " ")  // punctuation -> space
      .replace(/\s+/g, " ")
      .trim();
  }

  // ---- Combined-column split parsers (rules §11c) ----
  // splitCombinedName is also used by the Stage 2 dependent-name split so
  // both share one behavior (suffix + middle-initial rules).
  function splitCombinedName(v: string): { first: string, mi: string, last: string, guessed: boolean } | null {
    let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim();
    if (s === "") return null;
    let suffix = "";
    const sm = s.match(/[,\s]+(JR|SR|II|III|IV|V)\.?$/i);
    if (sm) { suffix = sm[1].toUpperCase(); s = s.slice(0, s.length - sm[0].length).trim(); }
    const withSfx = (last: string): string => suffix !== "" ? (last + " " + suffix).trim() : last;
    const stripDot = (t: string): string => t.replace(/\.$/, "");
    if (s.indexOf(",") >= 0) {
      // "Last, First [Middle]" — the comma makes the split reliable.
      const cp = s.indexOf(",");
      const last = s.slice(0, cp).trim();
      const rest = s.slice(cp + 1).replace(/,/g, " ").replace(/\s+/g, " ").trim();
      if (last === "" && rest === "") return null;
      const t = rest === "" ? [] : rest.split(" ");
      return { first: t.length > 0 ? t[0] : "", mi: t.length > 1 ? stripDot(t[1]) : "", last: withSfx(last), guessed: false };
    }
    const t = s.split(" ");
    if (t.length === 1) return { first: "", mi: "", last: withSfx(t[0]), guessed: true };
    if (t.length === 2) return { first: t[0], mi: "", last: withSfx(t[1]), guessed: true };
    if (t.length === 3 && /^[A-Za-z]\.?$/.test(t[1])) return { first: t[0], mi: stripDot(t[1]), last: withSfx(t[2]), guessed: true };
    return { first: t[0], mi: "", last: withSfx(t.slice(1).join(" ")), guessed: true }; // "Mary Van Buren" style
  }

  // "Austin, TX 78701" / "Round Rock TX 78664-1234" / "San Juan, Puerto Rico 00901".
  // Right-to-left: trailing zip, then a valid USPS code or full state name
  // (longest match first), remainder = city. zipOptional covers headers like
  // "CITY STATE" that legitimately carry no zip. Returns null when not confident.
  function parseCityStateZip(v: string, zipOptional: boolean): { city: string, state: string, zip: string } | null {
    let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim();
    if (s === "") return null;
    let zip = "";
    const zm = s.match(/[,\s]+(\d{5}(?:-\d{4})?)$/);
    if (zm) { zip = zm[1]; s = s.slice(0, s.length - zm[0].length); }
    else if (!zipOptional) return null;
    s = s.replace(/[\s,]+$/, "");
    let state = "";
    const ab = s.match(/[,\s]+([A-Za-z]{2})\.?$/);
    if (ab && VALID_USPS.has(ab[1].toUpperCase())) {
      state = ab[1].toUpperCase();
      s = s.slice(0, s.length - ab[0].length);
    } else {
      const words = s.split(/[\s,]+/).filter(w => w !== "");
      for (let n = Math.min(3, words.length - 1); n >= 1; n--) {
        const cand = words.slice(words.length - n).join(" ").toUpperCase();
        if (STATES[cand]) {
          state = STATES[cand];
          for (let k = 0; k < n; k++) s = s.replace(/[\s,]+[^\s,]+$/, "");
          break;
        }
      }
    }
    if (state === "") return null;
    const city = s.replace(/[\s,]+$/, "").trim();
    if (city === "" || /^\d+$/.test(city)) return null;
    return { city: city, state: state, zip: zip };
  }

  // Full "123 Main St, Apt 4, Austin, TX 78701". Peels zip + state via
  // parseCityStateZip, then the last comma segment is the city and the rest is
  // the street. No comma boundary -> null (the caller falls back to peeling
  // just State+ZIP so nothing is lost).
  function parseFullAddress(v: string): { street: string, city: string, state: string, zip: string } | null {
    const p = parseCityStateZip(v, false);
    if (p === null || p.zip === "") return null;
    const cp = p.city.lastIndexOf(",");
    if (cp < 0) return null;
    const street = p.city.slice(0, cp).replace(/[\s,]+$/, "").trim();
    const city = p.city.slice(cp + 1).trim();
    if (street === "" || city === "") return null;
    return { street: street, city: city, state: p.state, zip: p.zip };
  }

  function normalizeRelationship(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (["EMPLOYEE", "EE", "EMP", "SELF", "PRIMARY", "SUBSCRIBER", "E", "EMPL"].indexOf(n) !== -1) return "Employee";
    if (["SPOUSE", "SP", "SPS", "WIFE", "HUSBAND", "SPOUSE PARTNER"].indexOf(n) !== -1) return "Spouse";
    if (["DOMESTIC PARTNER", "DP", "PARTNER", "CIVIL UNION"].indexOf(n) !== -1) return "Domestic Partner";
    if (["CHILD", "CH", "DEPENDENT", "DEP", "SON", "DAUGHTER", "CHILDREN", "STEPCHILD", "STEPSON", "STEPDAUGHTER"].indexOf(n) !== -1) return "Child";
    return ""; // unknown -> caller flags
  }

  function normalizeGender(v: string): string {
    const n = normHeader(v);
    if (n === "M" || n === "MALE") return "M";
    if (n === "F" || n === "FEMALE") return "F";
    return "";
  }

  function normalizeEnrollWaive(v: string): string {
    // Basic Life Cov Level, Dependent Basic Life, STD, LTD all read "EE" / "Waive" (owner decision 2026-09-10; was Enroll/Waive).
    const n = normHeader(v);
    if (n === "") return "";
    if (WAIVE_WORDS.has(n) || ENROLL_WAIVE_NO.indexOf(n) !== -1) return "Waive";
    if (["ENROLL", "ENROLLED", "ENROLLEE", "ELECT", "ELECTED", "YES", "Y", "ACTIVE", "E", "ENROLLING", "ADD", "EE"].indexOf(n) !== -1) return "EE";
    if (["EE", "ES", "EC", "FAM"].indexOf(normalizeTier(v)) !== -1) return "EE"; // a tier (Employee Only, Family...) on a vertical census = enrolled
    return v.trim();
  }

  function normalizeYesNo(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (["Y", "YES", "TRUE", "1", "X"].indexOf(n) !== -1) return "Yes";
    if (["N", "NO", "FALSE", "0"].indexOf(n) !== -1) return "No";
    return v.trim();
  }

  function normalizeTier(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (WAIVE_WORDS.has(n)) return "W";
    if (["EE", "EO", "E", "EMPLOYEE", "EMPLOYEE ONLY", "EMP ONLY", "SELF", "SUBSCRIBER ONLY"].indexOf(n) !== -1) return "EE";
    if (n === "ES" || n === "EC" || n === "FAM" || n === "W") return n;
    const hasSpouse = /\bSPOUSE\b|\bSP\b|DOMESTIC|PARTNER/.test(n);
    const hasChild = /\bCHILD\b|\bCHILDREN\b|\bCH\b|DEPENDENT|\bDEP\b|\bKIDS?\b/.test(n);
    const hasFamily = /FAMILY|\bFAM\b/.test(n);
    if (hasFamily || (hasSpouse && hasChild)) return "FAM";
    if (hasSpouse) return "ES";
    if (hasChild) return "EC";
    if (/EMPLOYEE|\bEMP\b/.test(n)) return "EE";
    return "";
  }

  function tierToLmg(tier: string, raw: string, isEmployee: boolean, productExists: boolean, waiveText: string): string {
    if (tier === "W") return waiveText;
    if (tier !== "") return tier;
    if (raw.trim() !== "") return raw.trim(); // unrecognized (e.g. EE+1): keep the original; the caller raises a WARNING (rules section 5)
    // blank tier
    if (isEmployee && productExists) return waiveText; // NCAM would default blank to EE, so force waive
    return raw; // keep whatever was there (blank) for dependents
  }

  function lmgToNcamTier(v: string, productExists: boolean): string {
    if (v === LMG_WAIVE_TEXT) return "W";
    if (v === "" && productExists) return "W"; // NCAM forces blank employee coverage to W
    return v;
  }

  function volLife(v: string, isEmployee: boolean, waivedValue: string, zeroWaives: boolean): string {
    if (!isEmployee) return v.trim();
    const n = normHeader(v);
    if (n === "" || WAIVE_WORDS.has(n)) return waivedValue;
    const num = v.replace(/[^\d.]/g, "");
    if (num !== "") {
      if (zeroWaives && parseFloat(num) === 0) return waivedValue; // Dependent: 0 -> Waive
      return num;
    }
    return v.trim();
  }

  function formatDate(v: string | number): string {
    if (v === "" || v == null) return "";
    // Excel serial date number
    if (typeof v === "number" || /^\d+(\.\d+)?$/.test(String(v))) {
      const serial = typeof v === "number" ? v : parseFloat(String(v));
      if (serial > 59 && serial < 80000) { // plausible date serial (1900..~2119)
        const ms = Math.round((serial - 25569) * 86400 * 1000); // 25569 = 1970-01-01 in Excel serial
        const d = new Date(ms);
        return pad2(d.getUTCMonth() + 1) + "/" + pad2(d.getUTCDate()) + "/" + d.getUTCFullYear();
      }
    }
    const s = String(v).trim();
    // MM/DD/YYYY or M/D/YY or with - .
    let m = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{2,4})$/);
    if (m) {
      let mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10);
      // Two-digit year: 20YY unless that is after the current year, else 19YY.
      if (yr < 100) yr += (2000 + yr) <= new Date().getFullYear() ? 2000 : 1900;
      if (isRealDate(yr, mo, da)) return pad2(mo) + "/" + pad2(da) + "/" + yr;
    }
    // YYYY-MM-DD
    m = s.match(/^(\d{4})[\/\-.](\d{1,2})[\/\-.](\d{1,2})/);
    if (m) {
      const yr = parseInt(m[1], 10), mo = parseInt(m[2], 10), da = parseInt(m[3], 10);
      if (isRealDate(yr, mo, da)) return pad2(mo) + "/" + pad2(da) + "/" + yr;
    }
    return ""; // unparseable -> caller flags
  }

  function pad2(n: number): string { return n < 10 ? "0" + n : String(n); }

  // Day checked against the month (02/30 and 04/31 are rejected; leap years honored).
  function isRealDate(yr: number, mo: number, da: number): boolean {
    if (mo < 1 || mo > 12 || da < 1) return false;
    const d = new Date(Date.UTC(yr, mo - 1, da));
    return d.getUTCMonth() === mo - 1 && d.getUTCDate() === da;
  }

  // ---- Fuzzy header similarity (word overlap + character bigram similarity) ----
  function fuzzyBigrams(s: string): string[] {
    const g: string[] = [];
    for (let i = 0; i < s.length - 1; i++) g.push(s.substr(i, 2));
    return g;
  }
  function diceCoef(a: string, b: string): number {
    if (a === b) return 1;
    const A = fuzzyBigrams(a), B = fuzzyBigrams(b);
    if (A.length === 0 || B.length === 0) return 0;
    const counts = new Map<string, number>();
    for (const g of A) counts.set(g, (counts.get(g) || 0) + 1);
    let match = 0;
    for (const g of B) { const n = counts.get(g) || 0; if (n > 0) { counts.set(g, n - 1); match++; } }
    return (2 * match) / (A.length + B.length);
  }
  function tokenJaccard(a: string, b: string): number {
    const A = a.split(" ").filter(x => x !== ""), B = b.split(" ").filter(x => x !== "");
    if (A.length === 0 || B.length === 0) return 0;
    const setA = new Set<string>(A), setB = new Set<string>(B);
    let inter = 0;
    setA.forEach(x => { if (setB.has(x)) inter++; });
    const uni = new Set<string>(A.concat(B)).size;
    return inter / uni;
  }
  function simScore(a: string, b: string): number {
    return 0.6 * tokenJaccard(a, b) + 0.4 * diceCoef(a, b);
  }
  function bestFuzzy(norm: string, candidates: { field: string, text: string }[]): { field: string, score: number } {
    let bestField = "", bestScore = 0;
    for (const cand of candidates) {
      const s = simScore(norm, cand.text);
      if (s > bestScore) { bestScore = s; bestField = cand.field; }
    }
    return { field: bestField, score: bestScore };
  }

  function normalizeDepRel(v: string): string {
    const r = normalizeRelationship(v);
    return (r === "" || r === "Employee") ? "" : r; // dependents are Spouse / Domestic Partner / Child
  }

  // ======================================================================
  // PRE-PASS: reshape "nightmare" layouts into one clean row per person.
  //   Stage S — split combined columns (Name / City-State-Zip / full address).
  //   Stage 0 — product-coded routing (generic value + Product column).
  //   Stage 1 — collapse stacked product rows (gated on a Product column).
  //   Stage 2 — explode "Dependent N <field>" columns into dependent rows.
  // Transforms only when confident; flags anything out of the norm.
  // ======================================================================
  function preprocess(headers0: string[], rows0: string[][], aliasToFieldMap: Map<string, string>): { headers: string[], rows: string[][], splits: { header: string, into: string }[], pivots: { header: string, role: string }[], srcRows: string[] } {
    const splits: { header: string, into: string }[] = [];
    const pivots: { header: string, role: string }[] = [];
    if (rows0.length === 0) return { headers: headers0, rows: rows0, splits: splits, pivots: pivots, srcRows: [] };

    let headers = headers0.slice();
    let rows = rows0.map(r => r.slice());
    // RAW DATA row number(s) behind each row (header = row 1), kept in step with
    // rows through the collapse and explode stages.
    let srcRows: number[][] = rows0.map((_r, i) => [i + 2]);

    // ---- STAGE S: combined-column split ("data extrapolation", rules §11c) ----
    // A single column holding a full name ("Smith, John" / "Jane Doe"), a
    // "City, State ZIP" value, or a whole "Address, City, State ZIP" is split
    // into the separate LMG fields. Runs FIRST so the collapse/explode stages
    // below can key on the split First/Last columns. Transforms only when
    // confident; below-threshold columns are flagged and passed through (§10).
    {
      const snorm = headers.map(h => normHeader(h));
      const nCols = snorm.length; // freeze: columns appended below are never candidates
      const SPLIT_CONFIRM = 0.60; // named combined header: >=60% of non-blank values parse
      const SPLIT_STRONG = 0.90;  // value-shape-only (header is just CITY): >=90%
      const SPLIT_ADDR = 0.70;    // full address (always value-gated): >=70%
      const SPLIT_HINT = 0.30;    // 30%..threshold -> WARNING, no transform
      const depSkipRe = /^DEP(?:ENDENT)?\s*\d/; // Stage 2's territory
      const val = (row: string[], i: number): string => (row[i] == null) ? "" : String(row[i]).trim();
      const scan = (col: number, tryParse: (v: string) => boolean): { frac: number, nonBlank: number, hits: number } => {
        let nonBlank = 0, hits = 0;
        for (const row of rows) { const v = val(row, col); if (v === "") continue; nonBlank++; if (tryParse(v)) hits++; }
        return { frac: nonBlank ? hits / nonBlank : 0, nonBlank: nonBlank, hits: hits };
      };
      // Value-shape-only triggers on tiny columns (<5 non-blank) demand a 100% parse.
      const confident = (r: { frac: number, nonBlank: number }, threshold: number, valueOnly: boolean): boolean =>
        r.nonBlank > 0 && (valueOnly && r.nonBlank < 5 ? r.frac === 1 : r.frac >= threshold);
      const hasFieldNow = (field: string): boolean => {
        for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return true;
        return false;
      };
      const ensureField = (field: string): number => {
        for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return i;
        const idx = headers.length;
        headers.push(field);
        for (const row of rows) row.push("");
        return idx;
      };
      const setIfBlank = (row: string[], i: number, v: string) => { if (v !== "" && val(row, i) === "") row[i] = v; };
      // Consume the combined column BEFORE ensureField so a column whose header
      // itself maps to a target field (e.g. "City", "Address") gets a fresh
      // target column instead of pointing back at itself.
      const consume = (col: number, into: string, hits: number, nonBlank: number) => {
        splits.push({ header: headers[col], into: into });
        addLog(`Combined-column split: '${headers[col]}' -> ${into} (${hits}/${nonBlank} non-blank rows parsed).`);
        headers[col] = ""; // consumed; raw values stay on RAW DATA
      };

      // -- Shape 1: combined full-name column --
      const NAME_HEADERS = ["NAME", "FULL NAME", "EMPLOYEE NAME", "EMP NAME", "MEMBER NAME", "ENROLLEE NAME", "SUBSCRIBER NAME", "PARTICIPANT NAME", "EMPLOYEE FULL NAME", "MEMBER FULL NAME", "NAME LAST FIRST", "NAME FIRST LAST"];
      const NAME_BLOCK_RE = /FIRST|LAST|MIDDLE|CARRIER|PLAN|PROVIDER|BENEFICIARY|OFFICE|FACILITY|PCP|COMPANY|GROUP|EMPLOYER|CONTACT|AGENT|BROKER|SPONSOR|LOCATION|DOCTOR|DENTIST|PHYSICIAN|DEPENDENT|\bDEP\b|SPOUSE|CHILD|BANK|POLICY/;
      const isNameHeader = (n: string): boolean =>
        NAME_HEADERS.indexOf(n) !== -1 || (n !== "" && /( |^)NAME$/.test(n) && !NAME_BLOCK_RE.test(n));
      const nameParses = (v: string): boolean => {
        const p = splitCombinedName(v);
        return p !== null && p.first !== "" && /[A-Za-z]/.test(p.first) && /[A-Za-z]/.test(p.last);
      };
      for (let c = 0; c < nCols; c++) {
        if (headers[c] === "" || snorm[c] === "" || depSkipRe.test(snorm[c]) || !isNameHeader(snorm[c])) continue;
        if (hasFieldNow("Member Last Name") && hasFieldNow("First Name")) {
          addIssue("INFO", 0, "", headers[c], "", "Combined name column ignored — separate First/Last Name columns are present and were used", "Nothing to do unless First/Last are wrong; data kept in RAW DATA");
          continue;
        }
        const r = scan(c, nameParses);
        if (confident(r, SPLIT_CONFIRM, false)) {
          const hdr = headers[c];
          consume(c, "Member Last Name + First Name + Middle Initial", r.hits, r.nonBlank);
          const li = ensureField("Member Last Name"), fi = ensureField("First Name"), mi = ensureField("Middle Initial");
          let guessed = 0;
          for (const row of rows) {
            const v = val(row, c);
            if (v === "") continue;
            const p = splitCombinedName(v);
            if (p === null) continue;
            if (p.first === "" && p.guessed) { // single word — cannot split
              setIfBlank(row, li, p.last);
              addIssue("WARNING", 0, v, hdr, v, "Only one name word — could not split into First + Last", "Add the missing first name on the LMG tab");
              continue;
            }
            setIfBlank(row, li, p.last); setIfBlank(row, fi, p.first); setIfBlank(row, mi, p.mi);
            if (p.guessed) guessed++;
          }
          if (guessed > 0) addIssue("INFO", 0, "", hdr, "", `No comma in ${guessed} of ${r.nonBlank} name(s); split as 'First [MI] Last'`, "Verify first vs last name on the LMG tab");
          addIssue("INFO", 0, "", hdr, "", "Combined name column split into Last / First / Middle Initial", "Verify on the LMG tab; original kept in RAW DATA");
        } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
          addIssue("WARNING", 0, "", headers[c], "", "Column looks like a combined full-name column but too many values did not parse — not split automatically", "Split First/Last manually (or fix the values) and re-run");
        }
      }

      // -- Shape 2: combined "City, State ZIP" column --
      const CSZ_HEADERS = ["CITY STATE ZIP", "CITY ST ZIP", "CITY STATE ZIP CODE", "CITY ST ZIP CODE", "CITY STATE POSTAL CODE", "CSZ", "CITY STATE", "CITY AND STATE"];
      const CITY_ONLY = ["CITY", "TOWN", "MEMBER CITY"];
      for (let c = 0; c < nCols; c++) {
        if (headers[c] === "" || depSkipRe.test(snorm[c])) continue;
        const isCsz = CSZ_HEADERS.indexOf(snorm[c]) !== -1;
        const isCityShaped = !isCsz && CITY_ONLY.indexOf(snorm[c]) !== -1;
        if (!isCsz && !isCityShaped) continue;
        // Zip is optional only for named combined headers that don't promise one.
        const zipOpt = isCsz && snorm[c].indexOf("ZIP") === -1 && snorm[c] !== "CSZ";
        const r = scan(c, (v: string) => parseCityStateZip(v, zipOpt) !== null);
        if (confident(r, isCsz ? SPLIT_CONFIRM : SPLIT_STRONG, !isCsz)) {
          const hdr = headers[c];
          consume(c, "City + State + Zip Code", r.hits, r.nonBlank);
          const ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code");
          for (const row of rows) {
            const v = val(row, c);
            if (v === "") continue;
            const p = parseCityStateZip(v, zipOpt);
            if (p) { setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); }
            else {
              setIfBlank(row, ci, v); // keep the whole value — no silent loss
              addIssue("WARNING", 0, "", hdr, v, "Could not parse 'City, State ZIP' — whole value placed in City", "Split this row manually on the LMG tab");
            }
          }
          addIssue("INFO", 0, "", hdr, "", isCsz ? "Combined City/State ZIP column split into City, State, Zip Code" : `'${hdr}' contained full 'City, State ZIP' values — split into City, State, Zip Code`, "Verify on the LMG tab; original kept in RAW DATA");
        } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
          addIssue("WARNING", 0, "", headers[c], "", "Some values look like combined 'City, State ZIP' but too many did not parse — not split automatically", "Fix the combined values (City, ST 12345) or split the column manually, then re-run");
        }
      }

      // -- Shape 3: full "Address, City, State ZIP" column (always value-gated) --
      const ADDR_EXTRA = ["FULL ADDRESS", "COMPLETE ADDRESS", "HOME ADDRESS", "MAILING ADDRESS", "FULL HOME ADDRESS"];
      const isAddrHeader = (n: string): boolean =>
        (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(n) && !/EMAIL|IP/.test(n)) || ADDR_EXTRA.indexOf(n) !== -1;
      // Skipped entirely when separate City AND State AND Zip columns exist —
      // a street column can legitimately end in digits.
      if (!(hasFieldNow("City") && hasFieldNow("State") && hasFieldNow("Zip Code"))) {
        for (let c = 0; c < nCols; c++) {
          if (headers[c] === "" || depSkipRe.test(snorm[c]) || !isAddrHeader(snorm[c])) continue;
          const r = scan(c, (v: string) => parseFullAddress(v) !== null);
          if (confident(r, SPLIT_ADDR, true)) {
            const hdr = headers[c];
            consume(c, "Member Street Address + City + State + Zip Code", r.hits, r.nonBlank);
            const sti = ensureField("Member Street Address"), ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code");
            for (const row of rows) {
              const v = val(row, c);
              if (v === "") continue;
              const p = parseFullAddress(v);
              if (p) { setIfBlank(row, sti, p.street); setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); continue; }
              const csz = parseCityStateZip(v, false); // no comma boundary — peel State+ZIP, rest stays street
              if (csz && csz.zip !== "") {
                setIfBlank(row, sti, csz.city); setIfBlank(row, si, csz.state); setIfBlank(row, zi, csz.zip);
                addIssue("WARNING", 0, "", hdr, v, "Could not separate street from city (no comma) — both kept in Member Street Address", "Move the city out of the street address for this row");
              } else {
                setIfBlank(row, sti, v); // keep the whole value — no silent loss
                addIssue("WARNING", 0, "", hdr, v, "Could not parse full address — whole value placed in Member Street Address", "Split City/State/Zip for this row manually");
              }
            }
            addIssue("INFO", 0, "", hdr, "", "Combined full-address column split into Street / City / State / Zip", "Verify a few rows on the LMG tab; original kept in RAW DATA");
            break; // only the first qualifying column splits; others merge into the street as usual
          } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
            addIssue("WARNING", 0, "", headers[c], "", "Some values appear to contain City/State/ZIP inside the address — not split automatically", "Review — city/state/zip may be embedded in the street address");
          }
        }
      }
    }

    // Column lookups are computed AFTER Stage S so split-out First/Last/City
    // columns are visible to the collapse/explode stages below.
    const norm = headers.map(h => normHeader(h));
    const findCol = (names: string[]): number => {
      for (let i = 0; i < norm.length; i++) if (names.indexOf(norm[i]) !== -1) return i;
      return -1;
    };
    const ssnCol = findCol(["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SS", "MEMBER SSN", "TAX ID"]);
    const lastCol = findCol(["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"]);
    const firstCol = findCol(["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"]);
    const dobCol = findCol(["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"]);
    const relCol = findCol(["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"]);
    const genderCol = findCol(["GENDER", "SEX", "BIRTH SEX"]);
    const cell = (row: string[], i: number): string => (i < 0 || row[i] == null) ? "" : String(row[i]).trim();
    const productKey = (v: string): string => {
      const n = normHeader(v);
      if (n === "") return "";
      if (/DENTAL/.test(n)) return "DENTAL";
      if (/VISION/.test(n)) return "VISION";
      if (/MEDICAL|HEALTH/.test(n)) return "MEDICAL";
      if (/VOLUNTARY|VOL LIFE|SUPPLEMENTAL/.test(n)) return "VOL_LIFE";
      if (/BASIC LIFE|LIFE|AD D/.test(n)) return "BASIC_LIFE";
      if (/\bSTD\b|SHORT TERM/.test(n)) return "STD";
      if (/\bLTD\b|LONG TERM/.test(n)) return "LTD";
      return "";
    };
    let productCol = findCol(["PRODUCT", "COVERAGE", "COVERAGE TYPE", "PLAN TYPE", "BENEFIT", "BENEFIT TYPE", "LINE OF COVERAGE", "PRODUCT LINE", "COVERAGE LINE", "PRODUCT TYPE", "COVERAGE NAME", "BENEFIT PLAN TYPE", "LINE OF BUSINESS", "LOB"]);
    // Header not recognized: find the column whose values name the products
    // (Dental, Vision, Basic Life, STD...) on 80%+ of rows, with 2+ different products.
    if (productCol < 0) {
      for (let i = 0; i < headers.length && productCol < 0; i++) {
        if (aliasToFieldMap.has(norm[i])) continue;
        let filled = 0, hits = 0;
        const kinds = new Set<string>();
        for (const row of rows) {
          const v = cell(row, i);
          if (v === "") continue;
          filled++;
          const k = productKey(v);
          if (k !== "") { hits++; kinds.add(k); }
        }
        if (filled > 0 && hits / filled >= 0.8 && kinds.size >= 2) {
          productCol = i;
          addLog(`Product column found by its values: '${headers[i]}' (${Array.from(kinds).join(", ")}).`);
        }
      }
    }

    const inheritRe = /ADDRESS|ADDR|STREET|CITY|TOWN|STATE|ZIP|POSTAL|LOCATION|DIVISION|BRANCH/;

    // ---- STAGE 0: product-coded routing ----
    // When a row has a generic value column (Coverage Tier/Level, Plan, Election,
    // Class) plus a product column (Plan Type = Dental/Vision/Life/STD/LTD), move
    // that generic value into the product-specific LMG field for that row's product.
    const genericCols: { col: number, concept: string }[] = [];
    for (let i = 0; i < norm.length; i++) {
      const h = norm[i];
      if (i === productCol) continue;
      if (/DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(h)) continue; // already product-specific
      // COV / COVERAGE / TIER / LEVEL -> the election tier (EE/ES/EC/FAM/W).
      if (h === "COVERAGE DETAILS") continue; // free-text detail, not the tier
      if (/\bCOV/.test(h) || /\bTIER\b/.test(h) || h === "LEVEL") genericCols.push({ col: i, concept: "TIER" });
      // PLAN / SELECT / ELECT -> which plan to enroll on.
      else if (/\bPLAN\b/.test(h) || /SELECT/.test(h) || /ELECT/.test(h)) genericCols.push({ col: i, concept: "PLAN" });
      else if (h === "CLASS" || h === "CLASS WAIVE") genericCols.push({ col: i, concept: "CLASS" });
      else if (productCol >= 0 && /AMOUNT|\bAMT\b|VOLUME|BENEFIT/.test(h)) genericCols.push({ col: i, concept: "AMOUNT" });
      else if (productCol >= 0 && /PREMIUM|\bRATE\b|COST|CONTRIBUTION|DEDUCTION|PER PAY/.test(h)) genericCols.push({ col: i, concept: "COST" });
    }
    // Vertical census: a column whose values are tiers (Employee Only, Family, Waived...)
    // is the tier column whatever its header says (Election, Option, Enrollment Level...).
    if (productCol >= 0) {
      const looksLikeTiers = (i: number): boolean => {
        let filled = 0, hits = 0;
        for (const row of rows) {
          const v = cell(row, i);
          if (v === "") continue;
          filled++;
          if (normalizeTier(v) !== "") hits++;
        }
        return filled > 0 && hits / filled >= 0.8;
      };
      for (let i = 0; i < norm.length; i++) {
        if (i === productCol || norm[i] === "" || /DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(norm[i])) continue;
        const g = genericCols.filter(x => x.col === i)[0];
        if (g && g.concept === "TIER") continue;
        if (g && g.concept !== "PLAN") continue;
        if (!g && aliasToFieldMap.has(norm[i])) continue; // a real field (Relationship, Gender...)
        if (!looksLikeTiers(i)) continue;
        if (g) g.concept = "TIER"; else genericCols.push({ col: i, concept: "TIER" });
        addLog(`Tier column found by its values: '${headers[i]}'.`);
      }
    }
    const ROUTING: { [c: string]: { [p: string]: string } } = {
      // A tier on a Basic Life / STD / LTD row means enrolled (normalizeEnrollWaive reads it as EE / Waive).
      TIER: { DENTAL: "Dental Cov Level", VISION: "Vision Cov Level", BASIC_LIFE: "Basic Life Cov Level", STD: "Short-Term Disability Cov Level", LTD: "Long-Term Disability Cov Level" },
      PLAN: { DENTAL: "Dental Plan Elected", VISION: "Vision Plan Election" },
      CLASS: { BASIC_LIFE: "Life/AD&D Class", STD: "STD Class", LTD: "LTD Class" },
      AMOUNT: { VOL_LIFE: "Employee Voluntary Life Flat Amount" } // on a spouse/child row the roll-up moves it to Spouse/Dependent
    };
    if (productCol >= 0 && genericCols.length > 0) {
      const targetCol = new Map<string, number>();
      const ensureCol = (field: string): number => {
        if (targetCol.has(field)) return targetCol.get(field) as number;
        for (let i = 0; i < headers.length; i++) { // reuse an existing column that maps to this field
          if (aliasToFieldMap.get(normHeader(headers[i])) === field) { targetCol.set(field, i); return i; }
        }
        const idx = headers.length;
        headers.push(field);
        for (const row of rows) row.push("");
        targetCol.set(field, idx);
        return idx;
      };
      let routed = 0;
      const unrouted = new Map<string, number>(); // product value -> generic values with no output field
      for (const row of rows) {
        const p = productKey(cell(row, productCol));
        for (const gc of genericCols) {
          const map = ROUTING[gc.concept];
          const field = map && p ? map[p] : "";
          const val = cell(row, gc.col);
          if (val === "" || gc.concept === "COST") continue; // per-product cost/premium: not used
          if (field) {
            const ti = ensureCol(field);
            if (cell(row, ti) === "") { row[ti] = val; routed++; }
          } else {
            const pv = cell(row, productCol) || "(blank product)";
            unrouted.set(pv, (unrouted.get(pv) || 0) + 1);
          }
        }
      }
      // Voluntary life, medical and unknown products have no column here: say so, never drop quietly.
      unrouted.forEach((n, pv) => {
        addIssue("INFO", 0, "", "", pv, `${n} value(s) in the shared tier/amount column(s) for '${pv}' rows have no output column`, "Not used on the output; the values stay on RAW DATA");
      });
      // Blank the consumed generic headers so they aren't re-flagged as unrecognized
      // (the values now live in product-specific columns; RAW DATA keeps the originals).
      for (const gc of genericCols) {
        pivots.push({ header: headers[gc.col], role: gc.concept === "COST" ? "Per-product cost/premium on a vertical census; not used"
          : `Shared ${gc.concept.toLowerCase()} column on a vertical census; values moved into each coverage's own column` });
        headers[gc.col] = "";
      }
      if (routed > 0) {
        addLog(`Product routing: moved ${routed} generic value(s) into product-specific columns via '${headers0[productCol]}'.`);
      }
    }

    if (productCol >= 0 && headers[productCol] !== "") {
      let filled = 0, hits = 0;
      for (const row of rows) { const v = cell(row, productCol); if (v !== "") { filled++; if (productKey(v) !== "") hits++; } }
      if (filled > 0 && hits * 2 >= filled) {
        pivots.push({ header: headers[productCol], role: "Coverage column on a vertical census; rows were combined into one line per person" });
        headers[productCol] = "";
      }
    }

    // ---- STAGE 1: collapse stacked product rows ----
    if (productCol >= 0 && (ssnCol >= 0 || (lastCol >= 0 && dobCol >= 0))) {
      const personKey = (row: string[]): string =>
        [cell(row, ssnCol), cell(row, lastCol).toUpperCase(), cell(row, firstCol).toUpperCase(), cell(row, dobCol)].join("|");
      const groups = new Map<string, string[][]>();
      const groupSrc = new Map<string, number[]>();
      const order: string[] = [];
      for (let ri = 0; ri < rows.length; ri++) {
        const r = rows[ri];
        const k = personKey(r);
        if (!groups.has(k)) { groups.set(k, []); groupSrc.set(k, []); order.push(k); }
        (groups.get(k) as string[][]).push(r);
        (groupSrc.get(k) as number[]).push(...srcRows[ri]);
      }
      let collapsed = 0;
      const merged: string[][] = [];
      const mergedSrc: number[][] = [];
      // Only columns we actually keep (map to an LMG field) drive conflict flags.
      // Columns we don't keep — Total Rate, premiums, etc. — legitimately differ
      // between a person's Dental and Vision lines, so they are ignored here.
      const keptForConflict = headers.map(h => aliasToFieldMap.has(normHeader(h)));
      for (const k of order) {
        const grp = groups.get(k) as string[][];
        mergedSrc.push(groupSrc.get(k) as number[]);
        if (grp.length === 1) { merged.push(grp[0]); continue; }
        const memberName = (cell(grp[0], firstCol) + " " + cell(grp[0], lastCol)).trim() || "(stacked rows)";
        const out: string[] = headers.map(() => "");
        for (let c = 0; c < headers.length; c++) {
          let chosen = "";
          for (const r of grp) {
            const v = cell(r, c);
            if (v === "") continue;
            if (chosen === "") chosen = v;
            else if (chosen !== v && c !== productCol && keptForConflict[c]) {
              addIssue("WARNING", 0, memberName, headers[c], `${chosen} / ${v}`, "Conflicting values while collapsing stacked rows for the same person", "Confirm which value is correct");
            }
          }
          out[c] = chosen;
        }
        merged.push(out);
        collapsed += grp.length - 1;
      }
      if (collapsed > 0) {
        rows = merged;
        srcRows = mergedSrc;
        addLog(`Stacked-product collapse: merged ${collapsed} extra row(s) into ${merged.length} person row(s) via the '${headers0[productCol]}' column.`);
      }
    }

    // ---- STAGE 2: explode "Dependent N <field>" columns into rows ----
    const depRe = /^DEP(?:ENDENT)?\s*(\d+)\s+(.+)$/;
    const depGroups = new Map<number, { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number }>();
    const depColSet = new Set<number>();
    for (let i = 0; i < norm.length; i++) {
      const m = norm[i].match(depRe);
      if (!m) continue;
      const idx = parseInt(m[1], 10);
      const sub = m[2];
      if (!depGroups.has(idx)) depGroups.set(idx, { ssn: -1, dob: -1, gender: -1, rel: -1, last: -1, first: -1, name: -1 });
      const g = depGroups.get(idx) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number };
      let matched = true;
      if (sub === "SSN" || sub.indexOf("SOCIAL") === 0 || sub === "SS") g.ssn = i;
      else if (sub === "DOB" || sub.indexOf("BIRTH") >= 0 || sub === "DATE OF BIRTH" || sub === "BORN") g.dob = i;
      else if (sub === "GENDER" || sub === "SEX") g.gender = i;
      else if (sub.indexOf("RELATION") === 0 || sub === "REL" || sub === "TYPE") g.rel = i;
      else if (sub === "LAST NAME" || sub === "LAST") g.last = i;
      else if (sub === "FIRST NAME" || sub === "FIRST") g.first = i;
      else if (sub === "NAME" || sub === "FULL NAME") g.name = i;
      else matched = false;
      if (matched) depColSet.add(i);
    }

    if (depGroups.size > 0) {
      const keepIdx: number[] = [];
      for (let i = 0; i < headers.length; i++) if (!depColSet.has(i)) keepIdx.push(i);
      const reducedPos = (origCol: number): number => origCol < 0 ? -1 : keepIdx.indexOf(origCol);
      const inheritCols = keepIdx.filter(i => inheritRe.test(norm[i]));
      const newHeaders = keepIdx.map(i => headers[i]);
      const appendedRel = relCol < 0;
      if (appendedRel) newHeaders.push("Relationship");
      const relPos = appendedRel ? newHeaders.length - 1 : reducedPos(relCol);

      const newRows: string[][] = [];
      const newSrc: number[][] = [];
      let depCount = 0;
      const depIdxs = Array.from(depGroups.keys()).sort((a, b) => a - b);
      for (let ri = 0; ri < rows.length; ri++) {
        const r = rows[ri];
        const emp: string[] = keepIdx.map(i => r[i]);
        if (appendedRel) emp.push("Employee");
        else if (relPos >= 0 && cell(r, relCol) === "") emp[relPos] = "Employee";
        newRows.push(emp);
        newSrc.push(srcRows[ri]);

        const empName = (cell(r, firstCol) + " " + cell(r, lastCol)).trim();
        for (const n of depIdxs) {
          const g = depGroups.get(n) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number };
          const dssn = cell(r, g.ssn), ddob = cell(r, g.dob), dname = cell(r, g.name);
          const dlast = cell(r, g.last), dfirst = cell(r, g.first), dgender = cell(r, g.gender), drel = cell(r, g.rel);
          if (dssn === "" && ddob === "" && dname === "" && dlast === "" && dfirst === "") continue; // empty dependent slot
          depCount++;
          const drow: string[] = keepIdx.map(() => "");
          for (const ic of inheritCols) drow[keepIdx.indexOf(ic)] = r[ic]; // inherit address/location to tie family
          const setR = (origCol: number, val: string) => { const p = reducedPos(origCol); if (p >= 0 && val !== "") drow[p] = val; };
          if (dlast !== "" || dfirst !== "") { setR(lastCol, dlast); setR(firstCol, dfirst); }
          else if (dname !== "") {
            const np = splitCombinedName(dname); // shared §11c parser — comma "Last, First" is reliable
            if (np !== null) {
              if (np.guessed && np.first !== "") addIssue("INFO", 0, dname, "First Name", dname, "Dependent name had no comma; guessed 'First Last' split", "Verify first vs last name");
              setR(lastCol, np.last); setR(firstCol, np.first);
            }
          }
          setR(ssnCol, dssn); setR(dobCol, ddob); setR(genderCol, dgender);
          let relVal = normalizeDepRel(drel);
          if (relVal === "") {
            relVal = "Child";
            addIssue("INFO", 0, (dfirst + " " + dlast).trim() || empName, "Relationship", drel, "Dependent relationship not specified; defaulted to Child", "Set Spouse / Child / Domestic Partner if not a child");
          }
          if (relPos >= 0) { if (appendedRel) drow.push(relVal); else drow[relPos] = relVal; }
          else if (appendedRel) drow.push(relVal);
          newRows.push(drow);
          newSrc.push(srcRows[ri]); // a dependent comes from its employee's source row
        }
      }
      headers = newHeaders;
      rows = newRows;
      srcRows = newSrc;
      if (depCount > 0) addLog(`Dependent-column explode: created ${depCount} dependent row(s) from ${depGroups.size} dependent slot(s).`);
    }

    return { headers, rows, splits, pivots, srcRows: srcRows.map(a => a.join(", ")) };
  }
}

Quote-only script (NCAM Quote Engine)

Same engine, run separately for quotes: one line per employee, dependents removed (they only show up as the Dental/Vision tier), no plan names, basic life or voluntary life. Output tab: QUOTE. Install it the same way and name it NCAM Quote Engine.

View the quote script code

/**
 * ============================================================================
 * LMG / NCAM CENSUS TRANSLATION ENGINE
 * Version is set in ENGINE_VERSION below and stamped onto the SUMMARY and
 * PROCESSING LOG tabs when it runs — compare it to the version on the hub page
 * (lmg-ncam-engine.netlify.app) to confirm you're on the latest during testing.
 * Implements CENSUS-RULES.md (the canonical, language-agnostic spec).
 * If you change behavior here, update CENSUS-RULES.md first and bump its version.
 * ============================================================================
 * Paste this whole file into Excel  →  Automate tab  →  New Script, then Run.
 * It rebuilds the open workbook into the LaunchMyGroup (LMG) upload format and
 * a derived NCAM quoting sheet, with full validation + audit logging.
 *
 * It reads your census from the sheet named "RAW DATA" if one exists,
 * otherwise from whatever sheet is active when you press Run. The original
 * data is always snapshotted to "RAW DATA" and never modified, so the script
 * is safe to re-run as many times as you like.
 *
 * OUTPUT SHEETS (created/overwritten on every run):
 *   1. LMG               <- enrollment upload file (ALWAYS worksheet #1)
 *   2. NCAM              <- employees-only quoting sheet
 *   3. SUMMARY           <- counts by relationship and by plan/tier
 *   4. VALIDATION ISSUES <- every ERROR / WARNING / INFO found
 *   5. PROCESSING LOG    <- audit trail of every transformation
 *   6. RAW DATA          <- untouched copy of your original census
 *   7. Other Columns     <- every column NOT in the LMG upload, with the reason
 *   8. HEADER MAP        <- column mappings you confirm; remembered across runs
 *
 * DESIGN NOTE ON DUPLICATES: the spec called for silently merging duplicate
 * rows. This engine FLAGS duplicates in VALIDATION ISSUES instead of deleting
 * them, so an enrollee is never silently dropped from a file you upload. You
 * review the flags and decide. (Stacked product rows for the same person are
 * collapsed by the pre-pass, rules section 11; true duplicates are only flagged.)
 * ============================================================================
 */

function main(workbook: ExcelScript.Workbook) {
  // QUOTE MODE: build-site.py publishes a second script, NCAM-Quote-Engine.ts,
  // with this set to true. It outputs one QUOTE line per employee (tiers only,
  // dependents purged) instead of the LMG/NCAM tabs. Never edit that copy.
  const QUOTE_MODE = true;
  const OUTPUT_SHEETS = ["QUOTE", "LMG", "NCAM", "SUMMARY", "VALIDATION ISSUES", "PROCESSING LOG", "RAW DATA", "Other Columns", "HEADER MAP"];

  // ---- Audit collectors -------------------------------------------------
  const log: string[] = [];
  const issues: string[][] = []; // SEVERITY, SOURCE ROW, LMG ROW, MEMBER, COLUMN, VALUE, ISSUE, ACTION
  // srcRowLabels[i] = the RAW DATA row number(s) behind enrollee row i (set after
  // the pre-pass; stacked rows collapsed into one person list every source row).
  let srcRowLabels: string[] = [];
  function addLog(msg: string) { log.push(msg); }
  // `row` is the LMG sheet row (header = row 1). The issue records both the
  // source row on RAW DATA (what the user looks up) and the LMG row (highlighting).
  function addIssue(sev: string, row: number, member: string, col: string, val: string, issue: string, action: string) {
    const src = row ? (srcRowLabels[row - 2] || String(row)) : "";
    issues.push([sev, src, row ? String(row) : "", member, col, val, issue, action]);
  }

  // ---- Version stamp (compare this to the version shown on the hub page) ----
  const ENGINE_VERSION = "3.5.0";
  const ENGINE_BUILD = "2026-10-09";
  addLog(`LMG / NCAM Census Engine v${ENGINE_VERSION} (build ${ENGINE_BUILD}).`);

  // ======================================================================
  // 1. LOCATE SOURCE DATA
  // ======================================================================
  let sourceSheet: ExcelScript.Worksheet;
  const existingRaw = workbook.getWorksheet("RAW DATA");
  if (existingRaw) sourceSheet = existingRaw;
  else sourceSheet = workbook.getActiveWorksheet();
  if (OUTPUT_SHEETS.indexOf(sourceSheet.getName()) !== -1 && sourceSheet.getName() !== "RAW DATA") {
    throw new Error("Please select the worksheet that contains your census data, then run again. " +
      "(The active sheet is a generated output sheet and there is no RAW DATA sheet to read from.)");
  }
  const usedRange = sourceSheet.getUsedRange();
  if (!usedRange) throw new Error("The source worksheet is empty. Nothing to process.");
  const raw = usedRange.getValues();
  if (raw.length < 2) throw new Error("The source worksheet has no data rows beneath the header row.");

  const rawHeaders = raw[0].map(h => String(h == null ? "" : h));
  // Convert all cells to strings up front so the whole script is string[][]
  // (avoids union-type generics that the Office Scripts compiler rejects).
  // Excel date serials survive as numeric strings; formatDate() still parses them.
  const rawData: string[][] = raw.slice(1).map(r => r.map(v => v == null ? "" : String(v)));
  // (PRE-PASS runs below, after the alias map is built, so it knows which columns
  // we keep — rate/premium columns we don't keep must not drive the reshape logic.)

  // ======================================================================
  // 2. LMG FIELD DEFINITIONS + HEADER ALIAS MAP
  // ======================================================================
  // Canonical LMG output columns — EXACT names and order from the LMG platform.
  const LMG_FIELDS: string[] = [
    // MEMBER INFORMATION
    "Relationship", "Member Last Name", "First Name", "Middle Initial", "Gender",
    "Date of Birth", "Member Street Address", "City", "State", "Zip Code",
    "Phone Number", "Social Security Number", "Status Field", "Date of Hire", "Email Address",
    // ADDITIONAL DETAILS
    "Hours Worked Per Week", "Salary", "Occupation", "Disabled?", "Billing Division",
    // BENEFITS INFORMATION
    "Dental Cov Level", "Dental Plan Elected", "DHMO Provider Name",
    "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date",
    "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included",
    "Vision Cov Level", "Vision Plan Election",
    "Basic Life Cov Level", "Life/AD&D Class", "Primary Life Beneficiary",
    "Dependent Basic Life",
    "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
    "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class",
    "Working Location"
  ];

  // Alias variants -> LMG field. Keys are normalized (see normHeader()).
  // Field names used before v3.0.0 (2026-09-10 shared dictionary) and by the
  // old Python tool — accepted anywhere a user types an LMG field name.
  const LEGACY_FIELD_NAMES: { [old: string]: string } = {
    "Employee Status": "Status Field", "Status": "Status Field", "SSN": "Social Security Number",
    "Member First Name": "First Name", "Member City": "City", "Member State": "State", "Member Zip Code": "Zip Code",
    "Hours Worked per week": "Hours Worked Per Week", "Annual Salary": "Salary",
    "Dental Plan Selection": "Dental Plan Elected", "Dental Coverage Type": "Dental Cov Level", "Dental Coverage Tier": "Dental Cov Level",
    "Dental Prior Carrier Name": "Dental Prior Current Carrier Name", "Dental Prior/Current Carrier Name": "Dental Prior Current Carrier Name",
    "Dental Prior Carrier Termination Date": "Dental Prior Carrier Term Date",
    "Dental Prior Carrier Ortho": "Dental Prior Carrier Ortho included", "Orthodontia Included?": "Dental Prior Carrier Ortho included",
    "Vision Plan Selection": "Vision Plan Election", "Vision Plan Elected": "Vision Plan Election",
    "Vision Coverage Type": "Vision Cov Level", "Vision Coverage Tier": "Vision Cov Level",
    "Basic Life Election": "Basic Life Cov Level", "Basic Life Class": "Life/AD&D Class", "Primary Beneficiary": "Primary Life Beneficiary",
    "Employee Voluntary Life": "Employee Voluntary Life Flat Amount", "Spousal Voluntary Life": "Spouse Voluntary Life Flat Amount",
    "Dependent Voluntary Life": "Dependent Voluntary Life Flat Amount",
    "STD": "Short-Term Disability Cov Level", "STD Election": "Short-Term Disability Cov Level",
    "LTD": "Long-Term Disability Cov Level", "LTD Election": "Long-Term Disability Cov Level",
    "Location Number": "Working Location"
  };
  const ALIASES: { [field: string]: string[] } = {
    "Relationship": ["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"],
    "Status Field": ["STATUS", "EMPLOYEE STATUS", "EMPLOYMENT STATUS", "COBRA STATUS", "EMP STATUS", "STATUS FIELD"],
    "Social Security Number": ["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SOC SEC NO", "SS", "MEMBER SSN", "TAX ID", "EMPLOYEE SOCIAL SECURITY NUMBER", "EMPLOYEE SSN", "SOCIAL SECURITY NUMBER NOT USED"],
    "Member Last Name": ["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"],
    "First Name": ["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"],
    "Middle Initial": ["MIDDLE INITIAL", "MI", "MIDDLE", "MIDDLE NAME", "MIDDLE INTIAL"],
    "Gender": ["GENDER", "SEX", "BIRTH SEX"],
    "Date of Birth": ["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"],
    "Date of Hire": ["DATE OF HIRE", "DOH", "HIRE DATE", "ORIGINAL HIRE DATE", "EMPLOYMENT DATE"],
    "Member Street Address": ["MEMBER STREET ADDRESS", "STREET ADDRESS", "ADDRESS", "ADDRESS 1", "ADDR1", "ADDR", "HOME ADDRESS", "MAILING ADDRESS"],
    "City": ["CITY", "TOWN", "MEMBER CITY"],
    "State": ["STATE", "ST", "PROVINCE", "MEMBER STATE", "STATE ABBREVIATION", "STATE ABBR", "ST ABBREVIATION", "ST ABBR", "STATE CODE", "USPS", "USPS STATE", "MEMBER STATE ABBREVIATION"],
    "Zip Code": ["ZIP", "ZIP CODE", "POSTAL CODE", "ZIP 4", "MEMBER ZIP", "MEMBER ZIP CODE", "ZIP CODE 5 DIGITS"],
    "Phone Number": ["PHONE", "PHONE NUMBER", "HOME PHONE", "PERSONAL PHONE", "MOBILE PHONE", "CELL PHONE", "CELL", "MOBILE", "WORK PHONE", "OFFICE PHONE", "BUSINESS PHONE", "TELEPHONE", "CONTACT NUMBER", "PHONE NUMBER NUMERIC ONLY"],
    "Email Address": ["EMAIL", "EMAIL ADDRESS", "E MAIL", "PERSONAL EMAIL", "HOME EMAIL", "WORK EMAIL", "OFFICE EMAIL", "BUSINESS EMAIL"],
    "Salary": ["ANNUAL SALARY", "SALARY", "SALARY AMOUNT", "ANNUAL INCOME", "INCOME", "COMPENSATION", "ANNUAL COMPENSATION", "BENEFIT SALARY", "COMP", "W2 WAGES", "BASE SALARY"],
    "Age": ["AGE", "CURRENT AGE", "EMPLOYEE AGE", "MEMBER AGE", "AGE AT EFFECTIVE DATE"],
    "Compensation Type": ["COMPENSATION TYPE", "SALARY TYPE", "PAY TYPE", "ANNUAL OR HOURLY", "ANNUAL HOURLY", "HOURLY OR ANNUAL", "PAY FREQUENCY"],
    "Hours Worked Per Week": ["HOURS WORKED PER WEEK", "HOURS WORKED", "HOURS", "SCHEDULED HOURS", "WEEKLY HOURS", "HRS"],
    "Occupation": ["OCCUPATION", "JOB TITLE", "POSITION", "JOB", "JOB CLASS"],
    "Disabled?": ["DISABLED", "DISABILITY", "IS DISABLED", "DISABLED DEPENDENT"],
    "Dental Plan Elected": ["DENTAL PLAN ELECTED", "DENTAL PLAN ELECTION", "DENTAL PLAN SELECTION", "DENTAL PLAN SELECT", "DENTAL PLAN NAME", "DENTAL PLAN", "DENTAL SELECT", "DENTAL ELECT", "DENTAL ELECTION", "DENTAL IF EMPLOYEE IS ELECTING DENTAL PLEASE SELECT PLAN"],
    "Dental Cov Level": ["DENTAL COVERAGE TIER", "DENTAL COVERAGE TYPE", "DENTAL COVERAGE LEVEL", "DENTAL COVERAGE", "DENTAL COV", "DENTAL COV TIER", "DENTAL COV TYPE", "DENTAL COV LEVEL", "DENTAL TIER", "DENTAL LEVEL"],
    "DHMO Provider Name": ["DHMO PROVIDER NAME", "DHMO PROVIDER", "DENTAL PCP", "DENTAL OFFICE", "DENTAL FACILITY", "DHMO"],
    "Dental Prior Current Carrier Name": ["PRIOR CARRIER", "PRIOR CARRIER NAME", "CURRENT CARRIER", "PREVIOUS CARRIER", "DENTAL PRIOR CARRIER NAME", "PRIOR CURRENT CARRIER NAME", "IF A MEMBER HAD DENTAL AND OR ORTHO COVERAGE WITHIN THE PAST 12 MONTHS PLEASE PROVIDE PRIOR CURRENT CARRIER NAME"],
    "Dental Prior Carrier Effective Date": ["PRIOR CARRIER EFFECTIVE DATE", "PRIOR CARRIER EFF DATE", "PRIOR EFFECTIVE DATE", "PRIOR COVERAGE START"],
    "Dental Prior Carrier Term Date": ["PRIOR CARRIER TERM DATE", "PRIOR CARRIER TERMINATION DATE", "PRIOR TERMINATION DATE", "PRIOR COVERAGE END"],
    "Dental Prior Carrier Ortho included": ["ORTHODONTIA INCLUDED", "WAS ORTHODONTIA INCLUDED", "ORTHODONTIA", "ORTHO", "PRIOR ORTHO", "ORTHO COVERAGE"],
    "Vision Plan Election": ["VISION PLAN ELECTED", "VISION PLAN ELECTION", "VISION PLAN SELECTION", "VISION PLAN SELECT", "VISION PLAN NAME", "VISION PLAN", "VISION SELECT", "VISION ELECT", "VISION ELECTION", "VISION IF EMPLOYEE IS ELECTING VISION PLEASE SELECT PLAN"],
    "Vision Cov Level": ["VISION COVERAGE TIER", "VISION COVERAGE TYPE", "VISION COVERAGE LEVEL", "VISION COVERAGE", "VISION COV", "VISION COV TIER", "VISION COV TYPE", "VISION COV LEVEL", "VISION TIER", "VISION LEVEL"],
    "Basic Life Cov Level": ["BASIC LIFE ELECTION", "BASIC LIFE", "BASIC LIFE COVERAGE", "BASIC LIFE COVERAGE TYPE", "BASIC LIFE COV LEVEL", "LIFE ELECTION", "GROUP LIFE", "EMPLOYER PAID LIFE", "BASIC LIFE ENROLL", "BASIC LIFE COVERAGGE TYPE", "LIFE BASIC LIFE ELECTION FLAT AMOUNT OR LIFE BY SALARY ELECT ENROLL OR WAIVE LIFE BY CLASS SELECT CLASS NUMBER OR WAIVE"],
    "Life/AD&D Class": ["LIFE/AD&D CLASS", "LIFE AD&D CLASS", "BASIC LIFE CLASS", "LIFE CLASS"],
    "Primary Life Beneficiary": ["PRIMARY BENEFICIARY", "BENEFICIARY", "PRIMARY LIFE BENEFICIARY", "BENE"],
    "Dependent Basic Life": ["DEPENDENT BASIC LIFE", "DEPENDENT BASIC LIFE COVERAGE LEVEL", "DEP BASIC LIFE COVERAGE LEVEL", "DEPENDENT LIFE", "DEP BASIC LIFE", "SPOUSE CHILD LIFE"],
    "Employee Voluntary Life Flat Amount": ["EMPLOYEE VOLUNTARY LIFE", "EMPLOYEE VOLUNTARY LIFE AMOUNT", "EMPLOYEE VOLUNTARY LIFE FLAT AMOUNT", "EE VOL LIFE", "EMPLOYEE VOL LIFE", "VOLUNTARY LIFE", "VOLUNTARY LIFE FLAT AMOUNT", "VOL LIFE", "SUPPLEMENTAL LIFE", "EMPLOYEE VOLUNTARY LIFE ADD", "VOLUNTARY LIFE ADD", "EE VOL LIFE AMT", "EMPLOYEE VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN IF NOT ELECTING ENTER 0"],
    "Spouse Voluntary Life Flat Amount": ["SPOUSAL VOLUNTARY LIFE", "SPOUSAL VOLUNTARY LIFE AMOUNT", "SPOUSAL VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOLUNTARY LIFE", "SPOUSE VOLUNTARY LIFE AMOUNT", "SPOUSE VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOL LIFE", "VOLUNTARY SPOUSE LIFE", "SP VOL LIFE", "SPOUSE VOLUNTARY LIFE ADD", "SPOUSAL VOLUNTARY LIFE ADD", "SPOUSAL VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN NOT TO EXCEED 50 OF THE EMPLOYEE ELECTION IF NOT ELECTING ENTER 0"],
    "Dependent Voluntary Life Flat Amount": ["DEPENDENT VOLUNTARY LIFE", "DEPENDENT VOLUNTARY LIFE AMOUNT", "DEPENDENT VOLUNTARY LIFE FLAT AMOUNT", "DEPENDENT VOL LIFE", "CHILD VOL LIFE", "VOLUNTARY CHILD LIFE", "DEP VOL LIFE", "DEPENDENT CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE ADD", "DEPENDENT VOLUNTARY LIFE ADD"],
    "Short-Term Disability Cov Level": ["STD ELECTION", "STD", "SHORT TERM DISABILITY", "STD ENROLL", "STD COVERAGE TYPE", "STD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "STD Class": ["STD CLASS", "SHORT TERM DISABILITY CLASS", "STD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "Long-Term Disability Cov Level": ["LTD ELECTION", "LTD", "LONG TERM DISABILITY", "LTD ENROLL", "LTD COVERAGE TYPE", "LTD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "LTD Class": ["LTD CLASS", "LONG TERM DISABILITY CLASS", "LTD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"],
    "Working Location": ["WORKING LOCATION", "WORK LOCATION", "LOCATION", "LOCATION NUMBER", "LOCATION CODE", "BRANCH", "SOLD QUOTE LOCATION"],
    "Billing Division": ["BILLING DIVISION", "DIVISION", "BILL DIVISION", "BILLING DIV"]
  };

  // Build normalized alias -> field lookup.
  const aliasToField = new Map<string, string>();
  for (const field of Object.keys(ALIASES)) {
    for (const a of ALIASES[field]) aliasToField.set(a, field);
  }
  for (const f of LMG_FIELDS) if (!aliasToField.has(normHeader(f))) aliasToField.set(normHeader(f), f);

  // State lookups — declared BEFORE the pre-pass call because the §11c
  // combined-column split (Stage S in preprocess) validates state tokens.
  const STATES: { [k: string]: string } = {
    "ALABAMA": "AL", "ALASKA": "AK", "ARIZONA": "AZ", "ARKANSAS": "AR", "CALIFORNIA": "CA",
    "COLORADO": "CO", "CONNECTICUT": "CT", "DELAWARE": "DE", "DISTRICT OF COLUMBIA": "DC",
    "FLORIDA": "FL", "GEORGIA": "GA", "HAWAII": "HI", "IDAHO": "ID", "ILLINOIS": "IL",
    "INDIANA": "IN", "IOWA": "IA", "KANSAS": "KS", "KENTUCKY": "KY", "LOUISIANA": "LA",
    "MAINE": "ME", "MARYLAND": "MD", "MASSACHUSETTS": "MA", "MICHIGAN": "MI", "MINNESOTA": "MN",
    "MISSISSIPPI": "MS", "MISSOURI": "MO", "MONTANA": "MT", "NEBRASKA": "NE", "NEVADA": "NV",
    "NEW HAMPSHIRE": "NH", "NEW JERSEY": "NJ", "NEW MEXICO": "NM", "NEW YORK": "NY",
    "NORTH CAROLINA": "NC", "NORTH DAKOTA": "ND", "OHIO": "OH", "OKLAHOMA": "OK", "OREGON": "OR",
    "PENNSYLVANIA": "PA", "RHODE ISLAND": "RI", "SOUTH CAROLINA": "SC", "SOUTH DAKOTA": "SD",
    "TENNESSEE": "TN", "TEXAS": "TX", "UTAH": "UT", "VERMONT": "VT", "VIRGINIA": "VA",
    "WASHINGTON": "WA", "WEST VIRGINIA": "WV", "WISCONSIN": "WI", "WYOMING": "WY",
    "PUERTO RICO": "PR", "GUAM": "GU", "VIRGIN ISLANDS": "VI", "AMERICAN SAMOA": "AS"
  };
  const VALID_USPS = new Set<string>(Object.keys(STATES).map(k => STATES[k]));

  // Declared before the pre-pass: its tier detection (normalizeTier) reads it.
  const WAIVE_WORDS = new Set<string>(["W", "WAIVE", "WAIVED", "WAIVES", "DECLINE", "DECLINED", "NONE", "N A", "NA", "WAIVE COVERAGE", "OPT OUT", "NO COVERAGE", "WAIVE WITH OTHER COVERAGE"]);

  // PRE-PASS: reshape stacked product rows / dependent columns into one clean
  // enrollee row per person. aliasToField lets the collapse ignore columns we
  // don't keep (Total Rate, premiums, etc.) when checking for conflicts.
  const shaped = preprocess(rawHeaders, rawData, aliasToField);
  const sourceHeaders = shaped.headers;
  const dataRows = shaped.rows;
  srcRowLabels = shaped.srcRows;
  addLog(`Read ${rawData.length} source row(s), ${rawHeaders.length} column(s) from "${sourceSheet.getName()}"; ${dataRows.length} enrollee row(s) after reshape.`);

  // ---- Fuzzy matching + HEADER MAP setup ----
  const fieldByNorm = new Map<string, string>(); // normalized field/alias name -> canonical field
  for (const f of LMG_FIELDS) fieldByNorm.set(normHeader(f), f);
  for (const f of Object.keys(ALIASES)) fieldByNorm.set(normHeader(f), f);
  for (const o of Object.keys(LEGACY_FIELD_NAMES)) fieldByNorm.set(normHeader(o), LEGACY_FIELD_NAMES[o]); // pre-3.0 names typed in Map To
  const fuzzyCandidates: { field: string, text: string }[] = [];
  for (const f of Object.keys(ALIASES)) {
    fuzzyCandidates.push({ field: f, text: normHeader(f) });
    for (const a of ALIASES[f]) fuzzyCandidates.push({ field: f, text: a });
  }
  const AUTO_THRESHOLD = 0.85;    // >= this similarity -> auto-apply (with a verify flag)
  const SUGGEST_THRESHOLD = 0.50; // >= this -> offer as a suggestion on HEADER MAP

  // Read user-confirmed mappings from a prior run's HEADER MAP sheet (they win).
  const userMap = new Map<string, string>(); // normalized source header -> LMG field
  const userSrc = new Map<string, string>(); // normalized source header -> original text
  const hmSheet = workbook.getWorksheet("HEADER MAP");
  const hmRange = hmSheet ? hmSheet.getUsedRange() : undefined;
  if (hmSheet && hmRange) {
    const hv = hmRange.getValues();
    if (hv.length >= 2) {
      const hh = hv[0].map(x => normHeader(String(x == null ? "" : x)));
      const srcIdx = hh.indexOf("SOURCE HEADER");
      let mapIdx = hh.indexOf("MAP TO LMG FIELD");
      if (mapIdx < 0) mapIdx = hh.indexOf("MAP TO");
      if (srcIdx >= 0 && mapIdx >= 0) {
        for (let r = 1; r < hv.length; r++) {
          const srcH = String(hv[r][srcIdx] == null ? "" : hv[r][srcIdx]).trim();
          const toH = String(hv[r][mapIdx] == null ? "" : hv[r][mapIdx]).trim();
          if (!srcH || !toH) continue;
          const fld = fieldByNorm.get(normHeader(toH));
          if (fld) { userMap.set(normHeader(srcH), fld); userSrc.set(normHeader(srcH), srcH); }
        }
      }
    }
  }
  if (userMap.size) addLog(`HEADER MAP: applied ${userMap.size} user-confirmed column mapping(s).`);

  // Map each LMG field to a source column index (first exact match wins).
  const fieldToCol = new Map<string, number>();
  const addressCols: number[] = [];
  const unmapped: string[] = [];
  const dupCols: string[][] = []; // [sourceHeader, lmgFieldItDuplicates]
  // Phone / Email can have several source columns. Instead of one winning, we keep
  // them all with a priority rank and pick the best non-blank value PER ROW.
  // Phone:  personal/home/generic (1) > cell/mobile (2) > work/office (3).
  // Email:  personal/home/generic (1) > work/office (2).
  const phoneCols: { col: number, rank: number }[] = [];
  const emailCols: { col: number, rank: number }[] = [];
  const phoneRank = (n: string): number => /CELL|MOBILE/.test(n) ? 2 : (/WORK|OFFICE|BUSINESS/.test(n) ? 3 : 1);
  const emailRank = (n: string): number => /WORK|OFFICE|BUSINESS/.test(n) ? 2 : 1;

  // colReview drives the HEADER MAP sheet (every column that wasn't a clean exact match).
  const colReview: { header: string, status: string, mapTo: string, suggested: string, conf: string }[] = [];
  // Known Humana products that are NOT part of the LMG ancillary upload. These are
  // recognized and set aside (not treated as "unrecognized"), so a full enrollment
  // census doesn't produce a wall of warnings for Medical/Medicare/CI/etc.
  const nonLmgCols: string[] = [];
  const NON_LMG_RE = /MEDICARE|HICN|HEALTH SAVINGS|\bHSA\b|\bHRA\b|\bFSA\b|\bFSD\b|\bMEDICAL\b|CRITICAL ILLNESS|\bACCIDENT\b|SUPPLEMENTAL HEALTH|TERM LIFE|WORKPLACE|WRITING AGENT|\bSAN\b|APPLICATION SIGNATURE|SEQUENCE NUMBER|WAIVE ALL|ELIMINATION PERIOD|BENEFIT DURATION/;
  for (let c = 0; c < sourceHeaders.length; c++) {
    const norm = normHeader(sourceHeaders[c]);
    if (norm === "") continue;
    // Address columns (Address 1/2/3, Addr2, Street Address 2, ...) merge together.
    if (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(norm) && !/EMAIL|IP/.test(norm)) {
      addressCols.push(c);
      continue;
    }
    // Priority: user-confirmed mapping > known non-LMG product > exact alias > fuzzy.
    let field = userMap.get(norm) || "";
    let how = field ? "user" : "";
    let suggested = "", conf = "";
    if (!field && NON_LMG_RE.test(norm)) { nonLmgCols.push(sourceHeaders[c]); continue; }
    if (!field) {
      const exact = aliasToField.get(norm);
      if (exact) { field = exact; how = "exact"; }
    }
    if (!field) {
      const fz = bestFuzzy(norm, fuzzyCandidates);
      suggested = fz.field; conf = Math.round(fz.score * 100) + "%";
      if (fz.score >= AUTO_THRESHOLD) { field = fz.field; how = "auto"; }
      else if (fz.score >= SUGGEST_THRESHOLD) { how = "suggest"; }
      else { how = "none"; }
    }
    // Route the resolved field.
    if (field === "Phone Number") phoneCols.push({ col: c, rank: phoneRank(norm) });
    else if (field === "Email Address") emailCols.push({ col: c, rank: emailRank(norm) });
    else if (field && !fieldToCol.has(field)) fieldToCol.set(field, c);
    else if (field) { dupCols.push([sourceHeaders[c], field]); how = "dup"; }
    else unmapped.push(sourceHeaders[c]);

    if (how === "auto") {
      addIssue("INFO", 0, "", sourceHeaders[c], "", `Auto-matched to '${field}' by similarity (${conf})`, "Verify on the HEADER MAP tab; clear its 'Map To' cell to undo");
    }
    if (how !== "exact") {
      colReview.push({ header: sourceHeaders[c], status: how, mapTo: (how === "user" || how === "auto") ? field : "", suggested: how === "dup" ? field : suggested, conf: how === "user" ? "confirmed" : conf });
    }
  }
  // Columns split by the pre-pass (§11c) appear on HEADER MAP with their targets.
  for (const sp of shaped.splits) {
    colReview.push({ header: sp.header, status: "split", mapTo: "", suggested: sp.into, conf: "auto" });
  }
  // Vertical-census columns (Plan Type, Coverage Tier, Benefit Amount...) cover many
  // coverages at once, so there is no one field to map them to.
  for (const pv of shaped.pivots) {
    colReview.push({ header: pv.header, status: "pivot", mapTo: "", suggested: pv.role, conf: "auto" });
  }
  // Order each group by priority, then by source column order for ties.
  phoneCols.sort((a, b) => a.rank - b.rank || a.col - b.col);
  emailCols.sort((a, b) => a.rank - b.rank || a.col - b.col);
  const coalesce = (cols: { col: number, rank: number }[], src: string[]): string => {
    for (const cc of cols) { const v = src[cc.col]; if (v != null && String(v).trim() !== "") return String(v).trim(); }
    return "";
  };
  if (phoneCols.length > 1) addLog(`Phone: coalescing ${phoneCols.length} columns per row by priority (personal > cell > work).`);
  if (emailCols.length > 1) addLog(`Email: coalescing ${emailCols.length} columns per row by priority (personal > work).`);
  if (addressCols.length) addLog(`Address merge: combining ${addressCols.length} source address column(s).`);
  if (unmapped.length) addLog(`Unmapped source columns kept only in RAW DATA: ${unmapped.join(", ")}.`);
  if (dupCols.length) addLog(`Duplicate/unused source columns (first match used): ${dupCols.map(d => d[0]).join(", ")}.`);
  if (nonLmgCols.length) addLog(`Non-LMG product columns set aside (${nonLmgCols.length}): ${nonLmgCols.join(", ")}.`);
  // Non-LMG product columns: recognized as a real Humana field but not part of the
  // LMG ancillary upload — INFO (not a warning), expected on full enrollment files.
  for (const x of nonLmgCols) {
    addIssue("INFO", 0, "", x, "", "Non-LMG product column (Medical / Medicare / Critical Illness / Accident / etc.)",
      "Not part of the LMG ancillary upload — kept in RAW DATA only. Expected on full enrollment censuses.");
  }
  // Flag unrecognized / duplicate source columns so nothing leaves the LMG upload
  // silently — the user sees them on VALIDATION ISSUES and Other Columns; the raw
  // values stay on RAW DATA.
  for (const u of unmapped) {
    addIssue("WARNING", 0, "", u, "", "Unrecognized column — not mapped to any LMG field",
      "Open the HEADER MAP tab, put the right LMG field in its 'Map To' cell, and re-run to include it. Data stays on RAW DATA meanwhile.");
  }
  for (const d of dupCols) {
    addIssue("WARNING", 0, "", d[0], "", `Duplicate source for '${d[1]}' — an earlier column already filled it`,
      "Only the first column was used. Data preserved on RAW DATA only; merge manually if needed.");
  }
  for (const f of LMG_FIELDS) {
    if (!fieldToCol.has(f) && !(f === "Member Street Address" && addressCols.length)) {
      // not present in source; will be blank in output (logged once, quietly)
    }
  }
  addLog("Header normalization complete (parenthetical text removed, aliases standardized).");

  // No Relationship column at all (common on quoting censuses): every row is an
  // employee. Without this, every row would be treated as a dependent and its
  // SSN / salary / tiers wiped.
  const noRelationshipCol = !fieldToCol.has("Relationship");
  if (noRelationshipCol) {
    addIssue("INFO", 0, "", "Relationship", "", "No Relationship column in the census; every row treated as Employee",
      "If the file also lists spouses/children, add a Relationship column and re-run");
    addLog("No Relationship column found: every row treated as Employee.");
  }

  // Does this group even carry each product? Controls blank-default behavior.
  const hasDental = fieldToCol.has("Dental Plan Elected") || fieldToCol.has("Dental Cov Level");
  const hasVision = fieldToCol.has("Vision Plan Election") || fieldToCol.has("Vision Cov Level");
  const hasSTD = fieldToCol.has("Short-Term Disability Cov Level") || fieldToCol.has("STD Class");
  const hasLTD = fieldToCol.has("Long-Term Disability Cov Level") || fieldToCol.has("LTD Class");

  // ======================================================================
  // 3. STATIC LOOKUP TABLES
  // ======================================================================
  // (STATES / VALID_USPS are declared above the pre-pass call — the §11c
  //  combined-column split needs them before this point.)

  const OCC_ABBR: [RegExp, string][] = [
    [/\bSENIOR\b/g, "SR"], [/\bJUNIOR\b/g, "JR"], [/\bINFORMATION TECHNOLOGY\b/g, "IT"],
    [/\bSYSTEMS?\b/g, "SYS"], [/\bADMINISTRATORS?\b/g, "ADMIN"], [/\bADMINISTRATION\b/g, "ADMIN"],
    [/\bSUPPORT\b/g, "SUP"], [/\bTECHNICIANS?\b/g, "TECH"], [/\bREPRESENTATIVES?\b/g, "REP"],
    [/\bBUSINESS\b/g, "BIZ"], [/\bOPERATIONS?\b/g, "OPS"], [/\bMANAGERS?\b/g, "MGR"],
    [/\bMANAGEMENT\b/g, "MGMT"], [/\bASSISTANTS?\b/g, "ASST"], [/\bDIRECTORS?\b/g, "DIR"],
    [/\bENGINEERS?\b/g, "ENGR"], [/\bSUPERVISORS?\b/g, "SUPV"], [/\bDEVELOPERS?\b/g, "DEV"],
    [/\bCOORDINATORS?\b/g, "COORD"], [/\bSPECIALISTS?\b/g, "SPEC"], [/\bGENERAL\b/g, "GEN"]
  ];

  // Extra waiver words for the Enroll/Waive fields only (Basic Life, Dependent
  // Basic Life, STD, LTD). Not added to WAIVE_WORDS: a "0" there means an amount.
  const ENROLL_WAIVE_NO = ["N", "NO", "0", "FALSE"];
  // Dental/Vision Cov Level waive text on LMG (owner decision 2026-09-27, reverting
  // 2026-09-10 for these two only). Basic Life / STD / LTD stay EE/Waive.
  const LMG_WAIVE_TEXT = "Waive with other coverage";

  // ======================================================================
  // 4. PER-ROW TRANSFORMATION  ->  LMG TABLE
  // ======================================================================
  const lmgRows: string[][] = [];
  const ageByRow: string[] = []; // QUOTE tab only; 1:1 with lmgRows
  const dupKeyCount = new Map<string, number>();

  for (let r = 0; r < dataRows.length; r++) {
    const src = dataRows[r];
    const excelRow = r + 2; // human-facing source row number (header is row 1)
    const get = (field: string): string => {
      const idx = fieldToCol.get(field);
      if (idx == null) return "";
      const v = src[idx];
      return v == null ? "" : String(v).trim();
    };

    // --- Names (needed for issue messages) ---
    const lastName = get("Member Last Name").toUpperCase();
    const firstName = get("First Name").toUpperCase();
    let middle = get("Middle Initial").toUpperCase();
    if (middle.length > 1) middle = middle.charAt(0);
    const memberName = (firstName + " " + lastName).trim() || `(row ${excelRow})`;

    if (!lastName) addIssue("ERROR", excelRow, memberName, "Member Last Name", "", "Missing last name", "Add the member's last name");
    if (!firstName) addIssue("ERROR", excelRow, memberName, "First Name", "", "Missing first name", "Add the member's first name");

    // --- Relationship ---
    const relRaw = get("Relationship");
    const relationship = noRelationshipCol ? "Employee" : normalizeRelationship(relRaw);
    if (relRaw && relationship === "") {
      addIssue("WARNING", excelRow, memberName, "Relationship", relRaw, "Unrecognized relationship value", "Confirm: Employee / Spouse / Domestic Partner / Child");
    }
    if (!relRaw && !noRelationshipCol) addIssue("WARNING", excelRow, memberName, "Relationship", "", "Missing relationship", "Set Employee / Spouse / Domestic Partner / Child");
    const relOut = relationship || relRaw;
    const isEmployee = relationship === "Employee";

    // --- Gender ---
    const genderRaw = get("Gender");
    const gender = normalizeGender(genderRaw);
    if (gender === "") {
      addIssue(genderRaw ? "WARNING" : "ERROR", excelRow, memberName, "Gender", genderRaw, genderRaw ? "Unrecognized gender" : "Missing gender", "Set M or F");
    }

    // --- Dates ---
    const dob = formatDate(get("Date of Birth"));
    if (get("Date of Birth") && dob === "") addIssue("ERROR", excelRow, memberName, "Date of Birth", get("Date of Birth"), "Unparseable date of birth", "Enter as MM/DD/YYYY");
    if (!get("Date of Birth") && !(QUOTE_MODE && get("Age"))) addIssue("ERROR", excelRow, memberName, "Date of Birth", "", "Missing date of birth", "Enter as MM/DD/YYYY");
    const age = ageFromDob(dob);
    if (dob && isFutureDate(dob)) addIssue("ERROR", excelRow, memberName, "Date of Birth", dob, "Date of birth is in the future", "Correct the date of birth (check the year)");
    else if (age > 100) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (over 100)`, "Check the year of birth");
    else if (age >= 65) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (65 or older)`, "Review — age may affect eligibility/rating");
    if (isEmployee && age >= 0 && age < 14) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Employee is ${age} years old (under 14)`, "Check the date of birth, or whether this row is a dependent");
    const doh = isEmployee ? formatDate(get("Date of Hire")) : ""; // hire date blanked on dependents
    if (isEmployee && get("Date of Hire") && doh === "") addIssue("WARNING", excelRow, memberName, "Date of Hire", get("Date of Hire"), "Unparseable hire date", "Enter as MM/DD/YYYY");
    if (doh) {
      const yearsSinceHire = ageFromDob(doh);
      if (isFutureDate(doh)) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is in the future", "Confirm the hire date (new hire starting later?)");
      else if (yearsSinceHire > 100) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, `Hire date is ${yearsSinceHire} years ago`, "Check the year of hire");
      if (dob && age >= 0 && yearsSinceHire >= 0 && age - yearsSinceHire < 14) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is before the employee turned 14", "Check the hire date and date of birth");
    }
    const priorEff = formatDate(get("Dental Prior Carrier Effective Date"));
    const priorTerm = formatDate(get("Dental Prior Carrier Term Date"));

    // --- SSN ---
    // SSN is kept on the EMPLOYEE only — removed from spouse / domestic partner / child.
    let ssn = "";
    if (isEmployee) {
      ssn = get("Social Security Number").replace(/\D/g, "");
      if (ssn && ssn.length < 9) {
        // Often an 8-digit SSN simply lost a leading zero — pad to 9 and flag for a spot-check.
        const padded = ("000000000" + ssn).slice(-9);
        addIssue("INFO", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN had ${ssn.length} digits; padded leading zero(s) to ${padded}`, "Spot-check that the padded SSN is correct");
        ssn = padded;
      } else if (ssn.length > 9) {
        addIssue("ERROR", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN has ${ssn.length} digits (more than 9)`, "Correct to a 9-digit SSN");
      }
      if (!ssn) addIssue("ERROR", excelRow, memberName, "Social Security Number", "", "Employee missing SSN", "Add 9-digit SSN");
    }

    // --- Address (merge) ---
    let street = "";
    if (addressCols.length) {
      const parts: string[] = [];
      for (const c of addressCols) { const v = src[c]; if (v != null && String(v).trim() !== "") parts.push(String(v).trim()); }
      street = parts.join(" ").replace(/\s+/g, " ").toUpperCase();
    } else {
      street = get("Member Street Address").toUpperCase();
    }

    // --- City / State / Zip ---
    const city = get("City").toUpperCase();
    let state = get("State").toUpperCase().trim();
    if (state.length > 2) {
      const abbr = STATES[state];
      if (abbr) state = abbr;
      else addIssue("WARNING", excelRow, memberName, "State", get("State"), "Unknown state name", "Use 2-letter USPS code");
    } else if (state.length === 2 && !VALID_USPS.has(state)) {
      addIssue("WARNING", excelRow, memberName, "State", get("State"), "Invalid 2-letter state", "Use a valid USPS code");
    } else if (isEmployee && state === "") {
      addIssue("ERROR", excelRow, memberName, "State", "", "Employee missing state", "Add the 2-letter USPS state");
    }
    let zip = get("Zip Code").replace(/\D/g, "");
    if (zip.length > 5) zip = zip.substring(0, 5);
    if (zip && zip.length !== 5 && zip.length !== 0) {
      // pad short zips that lost a leading zero
      if (zip.length < 5) { zip = ("00000" + zip).slice(-5); }
    }

    // --- Phone (coalesce multiple columns per row by priority) ---
    const phoneRaw = coalesce(phoneCols, src);
    let phone = phoneRaw.replace(/\D/g, "");
    if (phone.length === 11 && phone.charAt(0) === "1") phone = phone.substring(1);
    if (phone && phone.length !== 10) {
      addIssue("WARNING", excelRow, memberName, "Phone Number", phoneRaw, `Phone has ${phone.length} digits (need 10)`, "Correct to a 10-digit phone");
      phone = ""; // do not export an invalid phone
    }

    // --- Email (coalesce multiple columns per row by priority) ---
    const email = coalesce(emailCols, src);

    // --- Hours / Salary ---
    // Use the member's actual Hours Worked Per Week. Default to 40 ONLY when
    // that field is blank/invalid (e.g. 20 hrs -> rate x 20 x 52, not x 40).
    const hoursRaw = get("Hours Worked Per Week").replace(/[^\d.]/g, "");
    const hoursParsed = parseFloat(hoursRaw);
    const hoursProvided = !isNaN(hoursParsed) && hoursParsed > 0;
    const hoursNum = hoursProvided ? hoursParsed : 40; // 40 only as a fallback
    let hours = hoursProvided ? hoursRaw : (isEmployee ? "40" : "");
    const salRaw = get("Salary");
    let salary = "";
    if (salRaw) {
      // Prefer an explicit Compensation Type (Hourly/Annual); else detect "/hr" text.
      const compType = normHeader(get("Compensation Type"));
      const compHourly = compType === "HOURLY" || compType === "HOUR" || compType === "H" || compType === "HRLY";
      const compAnnual = compType === "ANNUAL" || compType === "SALARY" || compType === "SALARIED" || compType === "A" || compType === "YEARLY";
      const isHourly = compHourly || (!compAnnual && compType === "" && /hr|hour/i.test(salRaw));
      const num = parseFloat(salRaw.replace(/[^\d.]/g, ""));
      if (!isNaN(num)) {
        if (isHourly) {
          salary = String(Math.round(num * hoursNum * 52 * 100) / 100);
          addLog(`Hourly->annual: ${salRaw} x ${hoursNum}h x 52 = ${salary} (${memberName}).`);
          if (!hoursProvided) {
            addIssue("INFO", excelRow, memberName, "Hours Worked Per Week", "",
              "Hourly wage annualized using the default 40 hrs/week (no hours given)",
              "Enter the actual weekly hours if it is not 40");
          }
        } else {
          salary = String(num);
          if (num > 0 && num < 1000) {
            addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary looks like an hourly rate", "Confirm annual salary, or label the cell '/hr'");
          }
        }
      } else {
        addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary not numeric", "Enter a numeric salary");
      }
    }

    // --- Occupation ---
    let occ = get("Occupation").toUpperCase().replace(/\s+/g, " ").trim();
    if (occ) {
      for (const [re, rep] of OCC_ABBR) occ = occ.replace(re, rep);
      occ = occ.replace(/\s+/g, " ").trim();
      if (occ.length > 25) {
        // Cut at the last whole word that fits (hard cut only when one word is over 25).
        const cut = occ.lastIndexOf(" ", 25);
        const short = (cut > 0 ? occ.substring(0, cut) : occ.substring(0, 25)).trim();
        addIssue("WARNING", excelRow, memberName, "Occupation", get("Occupation"), `Occupation exceeds 25 chars after abbreviation; truncated to '${short}'`, "Check the shortened title, or shorten it yourself");
        occ = short;
      }
    }

    // --- Disabled ---
    let disabled = normalizeYesNo(get("Disabled?"));
    if (disabled === "") disabled = "No";

    // --- Coverage tiers ---
    const dentalTierRaw = get("Dental Cov Level");
    let dentalTier = normalizeTier(dentalTierRaw);
    if (dentalTierRaw && dentalTier === "") addIssue("WARNING", excelRow, memberName, "Dental Cov Level", dentalTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading");
    const visionTierRaw = get("Vision Cov Level");
    let visionTier = normalizeTier(visionTierRaw);
    if (visionTierRaw && visionTier === "") addIssue("WARNING", excelRow, memberName, "Vision Cov Level", visionTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading");

    // LMG output: W -> "Waive"; blank employee -> waive (only if product exists)
    let dentalTierOut = tierToLmg(dentalTier, dentalTierRaw, isEmployee, hasDental, LMG_WAIVE_TEXT);
    let visionTierOut = tierToLmg(visionTier, visionTierRaw, isEmployee, hasVision, LMG_WAIVE_TEXT);
    // Waived Dental/Vision -> Plan column blank on LMG (source value stays on RAW DATA).
    const planOut = (field: string, tierOut: string): string => {
      const v = get(field);
      if (tierOut !== LMG_WAIVE_TEXT) return v;
      if (v.trim()) addIssue("INFO", excelRow, memberName, field, v, "Coverage waived; plan left blank on LMG", "Original value kept on RAW DATA");
      return "";
    };

    // --- Voluntary life (employee rows). Employee/Spousal waived -> 0;
    //     Dependent waived -> "Waive". ---
    const eeVol = volLife(get("Employee Voluntary Life Flat Amount"), isEmployee, "0", false);
    const spVol = volLife(get("Spouse Voluntary Life Flat Amount"), isEmployee, "0", false);
    const chVol = volLife(get("Dependent Voluntary Life Flat Amount"), isEmployee, "Waive", true); // 0 or blank -> Waive
    // Flag voluntary-life amounts that are not in even thousands (e.g. 62,500).
    const oddThousand = (v: string, label: string) => {
      const n = parseFloat(v);
      if (!isNaN(n) && n > 0 && n % 1000 !== 0) addIssue("WARNING", excelRow, memberName, label, v, "Voluntary life amount is not in even thousands", "Verify the volume amount");
    };
    oddThousand(eeVol, "Employee Voluntary Life Flat Amount");
    oddThousand(spVol, "Spouse Voluntary Life Flat Amount");
    oddThousand(chVol, "Dependent Voluntary Life Flat Amount");
    if (!isEmployee && (WAIVE_WORDS.has(normHeader(get("Spouse Voluntary Life Flat Amount"))) === false) && get("Spouse Voluntary Life Flat Amount")) {
      addIssue("INFO", excelRow, memberName, "Spouse Voluntary Life Flat Amount", get("Spouse Voluntary Life Flat Amount"), "Spousal vol life on a dependent row", "Will roll up to employee in a future version; verify manually");
    }

    // --- Basic life + beneficiary ---
    const basicLife = normalizeEnrollWaive(get("Basic Life Cov Level"));
    let beneficiary = get("Primary Life Beneficiary");
    const present = (x: string): boolean => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x));
    const realVol = (x: string): boolean => x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x));
    // Primary Beneficiary defaults to ON FILE when blank and the employee is actually
    // enrolled in life: Basic Life elected (not waived) and/or real Employee Voluntary
    // Life. Spouse/child vol life alone does not insure the employee.
    const hasLife = present(basicLife) || realVol(eeVol);
    if (isEmployee && hasLife && !beneficiary) beneficiary = "ON FILE";

    // --- Assemble LMG record (must match LMG_FIELDS order) ---
    const rec: { [k: string]: string } = {
      "Relationship": relOut,
      "Status Field": get("Status Field"),
      "Social Security Number": ssn,
      "Member Last Name": lastName,
      "First Name": firstName,
      "Middle Initial": middle,
      "Gender": gender || genderRaw,
      "Date of Birth": dob,
      "Date of Hire": doh,
      "Member Street Address": street,
      "City": city,
      "State": state,
      "Zip Code": zip,
      "Phone Number": phone,
      "Email Address": email,
      "Working Location": get("Working Location"),
      "Billing Division": get("Billing Division"),
      "Salary": salary,
      "Hours Worked Per Week": hours,
      "Occupation": occ,
      "Disabled?": disabled,
      "Dental Plan Elected": planOut("Dental Plan Elected", dentalTierOut),
      "Dental Cov Level": dentalTierOut,
      "DHMO Provider Name": get("DHMO Provider Name"),
      "Dental Prior Current Carrier Name": get("Dental Prior Current Carrier Name"),
      "Dental Prior Carrier Effective Date": priorEff,
      "Dental Prior Carrier Term Date": priorTerm,
      "Dental Prior Carrier Ortho included": normalizeYesNo(get("Dental Prior Carrier Ortho included")),
      "Vision Plan Election": planOut("Vision Plan Election", visionTierOut),
      "Vision Cov Level": visionTierOut,
      "Basic Life Cov Level": basicLife,
      "Life/AD&D Class": get("Life/AD&D Class"),
      "Primary Life Beneficiary": beneficiary,
      "Dependent Basic Life": normalizeEnrollWaive(get("Dependent Basic Life")),
      "Employee Voluntary Life Flat Amount": eeVol,
      "Spouse Voluntary Life Flat Amount": spVol,
      "Dependent Voluntary Life Flat Amount": chVol,
      "Short-Term Disability Cov Level": normalizeEnrollWaive(get("Short-Term Disability Cov Level")),
      "STD Class": get("STD Class"),
      "Long-Term Disability Cov Level": normalizeEnrollWaive(get("Long-Term Disability Cov Level")),
      "LTD Class": get("LTD Class")
    };
    lmgRows.push(LMG_FIELDS.map(f => rec[f] == null ? "" : rec[f]));
    ageByRow.push(get("Age").replace(/[^\d]/g, ""));

    // --- Duplicate detection (flag, never silently merge) ---
    const key = `${lastName}|${firstName}|${dob}|${gender}`;
    dupKeyCount.set(key, (dupKeyCount.get(key) || 0) + 1);
    if (dupKeyCount.get(key)! > 1) {
      addIssue("WARNING", excelRow, memberName, "(row)", key, "Possible duplicate (same name + DOB + gender)", "Review; remove the duplicate enrollee if confirmed");
    }
  }

  addLog("State conversions complete.");
  addLog("Phone cleanup complete.");
  addLog("Date normalization complete.");
  addLog("Coverage normalization complete.");
  addLog(`LMG build complete: ${lmgRows.length} enrollee row(s).`);

  // ======================================================================
  // 4b. FAMILY ROLL-UP (by adjacency)
  // Dependents belong to the nearest Employee row above them. Roll their
  // elections up to the employee row and derive the employee's coverage tier
  // from who is enrolled. Demographics stay on the dependent rows.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const relI = ci("Relationship");
    const realAmt = (x: string): boolean => x != null && x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x));
    const isWaiveTier = (x: string): boolean => x === LMG_WAIVE_TEXT || WAIVE_WORDS.has(normHeader(x)) || normHeader(x).indexOf("WAIVE") >= 0;
    const nameOf = (r: string[]): string => (r[ci("First Name")] + " " + r[ci("Member Last Name")]).trim();
    const tierRank: { [k: string]: number } = { EE: 1, ES: 2, EC: 2, FAM: 3 };

    // Build families by adjacency: an Employee starts one; following Spouse/DP/Child rows join it.
    const fams: { emp: number, deps: number[] }[] = [];
    for (let i = 0; i < lmgRows.length; i++) {
      const rel = lmgRows[i][relI];
      if (rel === "Employee") fams.push({ emp: i, deps: [] });
      else if (fams.length && (rel === "Spouse" || rel === "Domestic Partner" || rel === "Child")) fams[fams.length - 1].deps.push(i);
    }
    const censusListsDependents = fams.some(f => f.deps.length > 0);
    let rolled = 0;
    for (const fam of fams) {
      const emp = lmgRows[fam.emp];
      const empName = nameOf(emp) || "(employee)";
      const empRow = fam.emp + 2; // LMG sheet row of the employee
      for (const di of fam.deps) {
        const dep = lmgRows[di];
        const rel = dep[relI];
        const isSp = rel === "Spouse" || rel === "Domestic Partner";
        const isCh = rel === "Child";
        // The dependent's own voluntary-life amount (first real among the vol-life columns).
        const depVol = [dep[ci("Employee Voluntary Life Flat Amount")], dep[ci("Spouse Voluntary Life Flat Amount")], dep[ci("Dependent Voluntary Life Flat Amount")]].filter(x => realAmt(x))[0] || "";
        if (isSp && depVol && !realAmt(emp[ci("Spouse Voluntary Life Flat Amount")])) {
          emp[ci("Spouse Voluntary Life Flat Amount")] = depVol; rolled++;
          addIssue("INFO", empRow, empName, "Spouse Voluntary Life Flat Amount", depVol, `Rolled up spouse ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount");
        }
        if (isCh && depVol && !realAmt(emp[ci("Dependent Voluntary Life Flat Amount")])) {
          emp[ci("Dependent Voluntary Life Flat Amount")] = depVol; rolled++;
          addIssue("INFO", empRow, empName, "Dependent Voluntary Life Flat Amount", depVol, `Rolled up child ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount");
        }
        // Dependent Basic Life election.
        const depBasic = [dep[ci("Dependent Basic Life")], dep[ci("Basic Life Cov Level")]].filter(x => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x)))[0] || "";
        if (depBasic && (emp[ci("Dependent Basic Life")].trim() === "" || WAIVE_WORDS.has(normHeader(emp[ci("Dependent Basic Life")])))) {
          emp[ci("Dependent Basic Life")] = normalizeEnrollWaive(depBasic); rolled++;
        }
        // Vol-life elections live on the EE line in LMG, not the dependent rows — blank them.
        dep[ci("Employee Voluntary Life Flat Amount")] = "";
        dep[ci("Spouse Voluntary Life Flat Amount")] = "";
        dep[ci("Dependent Voluntary Life Flat Amount")] = "";
      }
      // Derive each product's tier from who is actually ENROLLED in THAT product.
      // A dependent who WAIVED a product does not count toward its tier. Upgrade-only:
      // never lower a stated tier, and never raise it for a waived/absent dependent.
      for (const tf of ["Dental Cov Level", "Vision Cov Level"]) {
        const idx = ci(tf);
        const cur = emp[idx];
        if (cur === "" || isWaiveTier(cur)) continue; // employee not enrolled in this product
        if (tierRank[cur] == null) continue; // unrecognized tier kept as-is (already flagged); never overwrite it
        let spEnr = false, chEnr = false;
        for (const di of fam.deps) {
          const dv = lmgRows[di][idx];
          if (dv.trim() === "" || isWaiveTier(dv)) continue; // dependent waived / not on this product
          const rel = lmgRows[di][relI];
          if (rel === "Spouse" || rel === "Domestic Partner") spEnr = true;
          else if (rel === "Child") chEnr = true;
        }
        const famTier = spEnr && chEnr ? "FAM" : spEnr ? "ES" : chEnr ? "EC" : "EE";
        if ((tierRank[famTier] || 0) > (tierRank[cur] || 0)) {
          emp[idx] = famTier; rolled++;
          addIssue("INFO", empRow, empName, tf, `${cur} -> ${famTier}`, "Coverage tier raised — dependent(s) enrolled in this product", "Verify the tier");
        }
      }

      // ---- Family consistency flags (do not auto-fix — flag for review) ----
      let spouseRow = false, childRow = false;
      for (const di of fam.deps) { const r = lmgRows[di][relI]; if (r === "Spouse" || r === "Domestic Partner") spouseRow = true; else if (r === "Child") childRow = true; }
      const tiers = [emp[ci("Dental Cov Level")], emp[ci("Vision Cov Level")]];
      const wantsSpouse = tiers.indexOf("ES") >= 0 || tiers.indexOf("FAM") >= 0;
      const wantsChild = tiers.indexOf("EC") >= 0 || tiers.indexOf("FAM") >= 0;
      // A dependent is listed but no coverage tier includes them.
      if (spouseRow && !wantsSpouse) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Spouse is listed but not included in any coverage tier", "Review — confirm whether the spouse should be covered");
      if (childRow && !wantsChild) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Child(ren) listed but not included in any coverage tier", "Review — confirm whether the child(ren) should be covered");
      // The tier implies a dependent that isn't listed (only checked when the census lists dependents as rows).
      if (censusListsDependents) {
        if (wantsSpouse && !spouseRow) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Coverage tier includes a spouse, but no spouse is listed", "Review — add the spouse or correct the tier");
        if (wantsChild && !childRow) addIssue("WARNING", empRow, empName, "Coverage / Dependents", "", "Coverage tier includes children, but none are listed", "Review — add the child(ren) or correct the tier");
      }
    }
    if (rolled > 0) addLog(`Family roll-up: rolled ${rolled} dependent election(s)/tier(s) up to employee rows (by adjacency).`);
  }

  // ======================================================================
  // 4c. EMPLOYEE-ONLY FIELDS
  // Elections, employment/comp, AND address/contact belong on the EMPLOYEE line
  // only. Blank them on spouse / domestic partner / child rows (which keep just
  // Relationship, name, gender, DOB, Disabled?). Runs AFTER the family roll-up so
  // dependent values are read first.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const relI = ci("Relationship");
    const EMPLOYEE_ONLY = [
      "Member Street Address", "City", "State", "Zip Code", "Phone Number", "Email Address",
      "Status Field", "Date of Hire", "Working Location", "Billing Division",
      "Salary", "Hours Worked Per Week", "Occupation",
      "Dental Plan Elected", "Dental Cov Level", "DHMO Provider Name",
      "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date",
      "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included",
      "Vision Plan Election", "Vision Cov Level",
      "Basic Life Cov Level", "Primary Life Beneficiary", "Dependent Basic Life",
      "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
      "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class"
    ];
    const idxs = EMPLOYEE_ONLY.map(f => ci(f)).filter(i => i >= 0);
    let blanked = 0;
    for (const r of lmgRows) {
      if (r[relI] === "Employee") continue;
      for (const i of idxs) if (r[i] !== "") { r[i] = ""; blanked++; }
    }
    if (blanked > 0) addLog(`Cleared ${blanked} employee-only field value(s) from dependent rows.`);
  }

  // ======================================================================
  // 4d. SPECIAL-CHARACTER CLEANING
  // Replace special characters with a space (# -> " UNIT ") across the census,
  // EXCEPT date fields, the email column, numeric fields (which keep decimals) and
  // the Dental/Vision Cov Level. Names and plan/class values keep ' and -.
  // ======================================================================
  {
    const ci = (f: string): number => LMG_FIELDS.indexOf(f);
    const EXEMPT = new Set<number>([
      "Date of Birth", "Date of Hire", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date",
      "Email Address", "Social Security Number", "Phone Number", "Zip Code",
      "Salary", "Hours Worked Per Week", "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount",
      "Dental Cov Level", "Vision Cov Level" // normalized tiers have no specials; an unrecognized tier (EE+1) stays verbatim for review
    ].map(f => ci(f)).filter(i => i >= 0));
    // Names and plan/class values keep apostrophes and hyphens (O'BRIEN, SMITH-JONES,
    // PPO-1500) - rules section 8b.
    const KEEP_APOS_HYPHEN = new Set<number>([
      "Member Last Name", "First Name", "Middle Initial",
      "Dental Plan Elected", "Vision Plan Election", "Life/AD&D Class", "STD Class", "LTD Class"
    ].map(f => ci(f)).filter(i => i >= 0));
    let cleaned = 0;
    for (const r of lmgRows) {
      for (let c = 0; c < r.length; c++) {
        if (EXEMPT.has(c)) continue;
        const v = r[c];
        if (v === "") continue;
        const nv = KEEP_APOS_HYPHEN.has(c) ? cleanSpecialKeep(v) : cleanSpecial(v);
        if (nv !== v) { r[c] = nv; cleaned++; }
      }
    }
    if (cleaned > 0) addLog(`Special-character cleanup: normalized ${cleaned} cell(s).`);
  }

  // ======================================================================
  // 5. NCAM  (employees only, quoting layout)
  // ======================================================================
  const ncamBaseHeaders = ["Last Name", "Gender", "Date of Birth", "Dental Coverage Type", "Vision Coverage Type"];
  const ncamStdLtdHeaders = ["Salary", "Occupation", "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class"];
  const includeStdLtd = hasSTD || hasLTD;
  const ncamHeaders = includeStdLtd ? ncamBaseHeaders.concat(ncamStdLtdHeaders) : ncamBaseHeaders;

  const colIdx = (f: string) => LMG_FIELDS.indexOf(f);
  const ncamRows: string[][] = [];
  for (let i = 0; i < lmgRows.length; i++) {
    if (lmgRows[i][colIdx("Relationship")] !== "Employee") continue;
    const row = lmgRows[i];
    // Convert "Waive" back to W; blank employee -> W
    const dt = lmgToNcamTier(row[colIdx("Dental Cov Level")], hasDental);
    const vt = lmgToNcamTier(row[colIdx("Vision Cov Level")], hasVision);
    const base = [row[colIdx("Member Last Name")], row[colIdx("Gender")], row[colIdx("Date of Birth")], dt, vt];
    const extra = includeStdLtd
      ? [row[colIdx("Salary")], row[colIdx("Occupation")], row[colIdx("Short-Term Disability Cov Level")], row[colIdx("STD Class")], row[colIdx("Long-Term Disability Cov Level")], row[colIdx("LTD Class")]]
      : [];
    ncamRows.push(base.concat(extra));
  }
  addLog(`NCAM build complete: ${ncamRows.length} employee row(s)${includeStdLtd ? " (STD/LTD columns included)" : ""}.`);

  // ======================================================================
  // 5b. QUOTE  (quote mode: one line per employee, dependents purged)
  // Dependents only show up as the derived Dental/Vision tier. No plan names,
  // basic life or voluntary life. Columns blank for every employee are left off.
  // ======================================================================
  const QUOTE_COLS: [string, string][] = [
    ["Last Name", "Member Last Name"], ["First Name", "First Name"], ["Gender", "Gender"],
    ["Date of Birth", "Date of Birth"], ["Age", ""],
    ["Dental Coverage Type", "Dental Cov Level"], ["Vision Coverage Type", "Vision Cov Level"],
    ["Salary", "Salary"], ["Occupation", "Occupation"], ["Date of Hire", "Date of Hire"],
    ["Short-Term Disability Cov Level", "Short-Term Disability Cov Level"], ["STD Class", "STD Class"],
    ["Long-Term Disability Cov Level", "Long-Term Disability Cov Level"], ["LTD Class", "LTD Class"],
    ["City", "City"], ["State", "State"], ["Zip Code", "Zip Code"]
  ];
  const quoteAll: string[][] = [];
  for (let i = 0; i < lmgRows.length; i++) {
    const row = lmgRows[i];
    if (row[colIdx("Relationship")] !== "Employee") continue;
    quoteAll.push(QUOTE_COLS.map(q => { const out = q[0], f = q[1]; return (
      out === "Age" ? ageByRow[i]
        : out === "Dental Coverage Type" ? (hasDental ? lmgToNcamTier(row[colIdx(f)], true) : "")
          : out === "Vision Coverage Type" ? (hasVision ? lmgToNcamTier(row[colIdx(f)], true) : "")
            : row[colIdx(f)]); }));
  }
  const QUOTE_KEEP = new Set<string>(["Last Name", "Gender"]);
  const quoteKept: number[] = [];
  for (let c = 0; c < QUOTE_COLS.length; c++) {
    if (QUOTE_KEEP.has(QUOTE_COLS[c][0]) || quoteAll.some(r => r[c] !== "")) quoteKept.push(c);
  }
  // DOB or Age is required; keep DOB visible when neither column has data.
  if (quoteKept.every(c => QUOTE_COLS[c][0] !== "Date of Birth" && QUOTE_COLS[c][0] !== "Age")) { quoteKept.push(3); quoteKept.sort((a, b) => a - b); }
  const quoteHeaders = quoteKept.map(c => QUOTE_COLS[c][0]);
  const quoteRows = quoteAll.map(r => quoteKept.map(c => r[c]));
  if (QUOTE_MODE) addLog(`QUOTE build complete: ${quoteRows.length} employee line(s); ${lmgRows.length - quoteRows.length} dependent/other row(s) purged (their tiers are on the employee line).`);

  // ======================================================================
  // 6. SUMMARY
  // ======================================================================
  const relCount: { [k: string]: number } = { "Employee": 0, "Spouse": 0, "Domestic Partner": 0, "Child": 0, "Other/Unknown": 0 };
  for (const row of lmgRows) {
    const rel = row[colIdx("Relationship")];
    if (relCount[rel] != null) relCount[rel]++; else relCount["Other/Unknown"]++;
  }
  const summary: string[][] = [];
  summary.push(["LMG / NCAM CENSUS SUMMARY", ""]);
  summary.push(["Engine version", `${ENGINE_VERSION} (build ${ENGINE_BUILD})`]);
  summary.push(["Check for updates", "lmg-ncam-engine.netlify.app - if the site shows a newer version, re-copy the script"]);
  summary.push(["", ""]);
  summary.push(["Enrollment counts", ""]);
  summary.push(["Employees", String(relCount["Employee"])]);
  summary.push(["Spouses", String(relCount["Spouse"])]);
  summary.push(["Domestic Partners", String(relCount["Domestic Partner"])]);
  summary.push(["Children", String(relCount["Child"])]);
  if (relCount["Other/Unknown"]) summary.push(["Other / Unknown relationship", String(relCount["Other/Unknown"])]);
  summary.push(["Total LMG rows", String(lmgRows.length)]);
  summary.push(QUOTE_MODE ? ["QUOTE employee lines", String(quoteRows.length)] : ["NCAM employee rows", String(ncamRows.length)]);
  summary.push(["", ""]);

  // Tier breakdown per product (counts enrollees by EE/ES/EC/FAM/W on the employee tier value)
  function tierBlock(label: string, present: boolean, ncamTierResolver: (raw: string) => string, lmgFieldName: string) {
    if (!present) return;
    const counts: { [k: string]: number } = { EE: 0, ES: 0, EC: 0, FAM: 0, W: 0, OTHER: 0 };
    for (const row of lmgRows) {
      if (row[colIdx("Relationship")] !== "Employee") continue;
      const t = ncamTierResolver(row[colIdx(lmgFieldName)]);
      if (counts[t] != null) counts[t]++; else counts["OTHER"]++;
    }
    const enrolled = counts.EE + counts.ES + counts.EC + counts.FAM;
    summary.push([label + " (by employee tier)", ""]);
    summary.push(["  EE", String(counts.EE)]);
    summary.push(["  ES", String(counts.ES)]);
    summary.push(["  EC", String(counts.EC)]);
    summary.push(["  FAM", String(counts.FAM)]);
    summary.push(["  Enrolled (EE+ES+EC+FAM)", String(enrolled)]);
    summary.push(["  Waived (W)", String(counts.W)]);
    summary.push(["  Eligible (Enrolled + Waived)", String(enrolled + counts.W)]);
    if (counts.OTHER) summary.push(["  Unclassified", String(counts.OTHER)]);
    summary.push(["", ""]);
  }
  tierBlock("Dental", hasDental, (raw) => lmgToNcamTier(raw, true), "Dental Cov Level");
  tierBlock("Vision", hasVision, (raw) => lmgToNcamTier(raw, true), "Vision Cov Level");

  // (The issue tally is added to SUMMARY in section 7, after the last issue is
  // recorded, so the "Required column is entirely blank" errors are counted too.)

  // ======================================================================
  // 7. WRITE ALL SHEETS
  // ======================================================================
  // Tab colors: LMG & NCAM green; every other generated tab navy.
  const TAB_PRIMARY = "#5C9A1B";   // LMG, NCAM
  const TAB_SECONDARY = "#003057"; // SUMMARY, VALIDATION ISSUES, PROCESSING LOG, RAW DATA, Other Columns

  // Drop entirely-blank LMG columns — blank columns break the LMG upload. Their
  // names are listed on the "Other Columns" tab. NCAM and SUMMARY read from the
  // full unfiltered records (lmgRows), so they are unaffected.
  // REQUIRED columns are ALWAYS kept (never dropped) — if a required column is
  // entirely blank it is flagged as an ERROR so the data gets filled in.
  const REQUIRED = new Set<string>(QUOTE_MODE ? ["Member Last Name", "Gender"]
    : ["Relationship", "Member Last Name", "First Name", "Gender", "Date of Birth", "State"]);
  // Voluntary Life columns only appear if there are real elections — a column
  // that is blank or "0" for everyone is dropped (an all-waived line of coverage).
  const ZERO_IS_BLANK = new Set<string>(["Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount"]);
  // A Plan column with only one distinct value is left off — the system auto-selects
  // the single available plan.
  const PLAN_DROP_IF_SINGLE = new Set<string>(["Dental Plan Elected", "Vision Plan Election"]);
  const keptCols: number[] = [];
  const blankCols: string[] = [];
  const singlePlanCols: string[] = [];
  for (let c = 0; c < LMG_FIELDS.length; c++) {
    const zeroBlank = ZERO_IS_BLANK.has(LMG_FIELDS[c]);
    let hasData = false;
    for (let r = 0; r < lmgRows.length; r++) {
      const v = lmgRows[r][c];
      if (v !== "" && v != null && !(zeroBlank && (v === "0" || WAIVE_WORDS.has(normHeader(v))))) { hasData = true; break; }
    }
    // Drop a Plan column that only ever holds one distinct value.
    if (hasData && PLAN_DROP_IF_SINGLE.has(LMG_FIELDS[c])) {
      const distinct = new Set<string>();
      for (let r = 0; r < lmgRows.length; r++) { const v = lmgRows[r][c]; if (v !== "" && v != null) distinct.add(String(v).trim().toUpperCase()); }
      if (distinct.size <= 1) { singlePlanCols.push(LMG_FIELDS[c]); continue; }
    }
    if (hasData || REQUIRED.has(LMG_FIELDS[c])) {
      keptCols.push(c);
      if (!hasData) addIssue("ERROR", 0, "", LMG_FIELDS[c], "", "Required column is entirely blank", "Provide this required field for every enrollee");
    } else {
      blankCols.push(LMG_FIELDS[c]);
    }
  }
  if (singlePlanCols.length) addLog(`Single-plan columns left off (system auto-selects): ${singlePlanCols.join(", ")}.`);
  const keptCount = keptCols.length;
  const lmgHeaderOut = keptCols.map(i => LMG_FIELDS[i]);
  const lmgRowsOut = lmgRows.map(row => keptCols.map(i => row[i]));
  if (blankCols.length) addLog(`Removed ${blankCols.length} blank column(s) from LMG (see Other Columns): ${blankCols.join(", ")}.`);

  // Quote mode: issues about LMG-only fields (SSN, plans, life, address...) don't
  // apply to a quote. They are counted in the log, not shown.
  if (QUOTE_MODE) {
    const quoteFields = new Set<string>(QUOTE_COLS.map(q => q[1]).concat(["Relationship", "(row)", "", "Age"]));
    const before = issues.length;
    const keep = issues.filter(it => quoteFields.has(it[4]));
    issues.length = 0;
    for (const it of keep) { it[2] = ""; issues.push(it); }
    if (before > keep.length) addLog(`Quote mode: ${before - keep.length} issue(s) about LMG-only fields not listed (run the LMG/NCAM script to see them).`);
  }

  // Issue tally - every issue has been recorded by this point.
  let nErr = 0, nWarn = 0, nInfo = 0;
  for (const it of issues) { if (it[0] === "ERROR") nErr++; else if (it[0] === "WARNING") nWarn++; else nInfo++; }
  summary.push(["Validation results", ""]);
  summary.push(["Errors", String(nErr)]);
  summary.push(["Warnings", String(nWarn)]);
  summary.push(["Info", String(nInfo)]);
  addLog(`Validation complete: ${nErr} error(s), ${nWarn} warning(s), ${nInfo} info.`);

  // Snapshot RAW DATA first (only if it does not already exist).
  if (!workbook.getWorksheet("RAW DATA")) {
    const rawSheet = workbook.addWorksheet("RAW DATA");
    if (raw.length && raw[0].length) rawSheet.getRangeByIndexes(0, 0, raw.length, raw[0].length).setValues(raw);
    addLog("RAW DATA created (untouched copy of original census).");
  }
  const rawSheetRef = workbook.getWorksheet("RAW DATA");
  if (rawSheetRef) rawSheetRef.setTabColor(TAB_SECONDARY);

  // Remove the other script's output tabs so a workbook never mixes LMG and QUOTE results.
  for (const n of (QUOTE_MODE ? ["LMG", "NCAM", "Other Columns"] : ["QUOTE"])) {
    const old = workbook.getWorksheet(n);
    if (old) old.delete();
  }

  // Recreate output sheets.
  const firstSheet = QUOTE_MODE
    ? writeSheet(workbook, "QUOTE", [quoteHeaders].concat(quoteRows), "#1F4E79", TAB_PRIMARY)
    : writeSheet(workbook, "LMG", [lmgHeaderOut].concat(lmgRowsOut), "#1F4E79", TAB_PRIMARY);
  const lmgSheet = firstSheet;
  const ncamSheet = QUOTE_MODE ? null : writeSheet(workbook, "NCAM", [ncamHeaders].concat(ncamRows), "#2E75B6", TAB_PRIMARY);
  writeSheet(workbook, "SUMMARY", summary, "#548235", TAB_SECONDARY);
  const issuesHeader = ["SEVERITY", "SOURCE ROW (RAW DATA)", "LMG ROW", "MEMBER NAME", "COLUMN", "VALUE", "ISSUE", "RECOMMENDED ACTION"];
  const issuesGrid = issues.length ? issues : [["INFO", "", "", "", "", "", "No issues found", ""]];
  const issuesSheet = writeSheet(workbook, "VALIDATION ISSUES", [issuesHeader].concat(issuesGrid), "#C00000", TAB_SECONDARY);
  writeSheet(workbook, "PROCESSING LOG", [["STEP"]].concat(log.map(l => [l])), "#7F7F7F", TAB_SECONDARY);
  // "Other Columns" = everything NOT in the LMG upload, each with a reason.
  // (Unrecognized/duplicate source columns are flagged earlier, in section 2,
  // so they reach the VALIDATION ISSUES sheet and the SUMMARY tally. The data
  // itself is always preserved on RAW DATA.)
  const otherRows: string[][] = [];
  for (const u of unmapped) otherRows.push([u, "Unrecognized source header — data kept in RAW DATA only; NOT in the LMG upload"]);
  for (const d of dupCols) otherRows.push([d[0], `Duplicate of '${d[1]}' (already mapped) — data kept in RAW DATA only; NOT in the LMG upload`]);
  for (const b of blankCols) otherRows.push([b, "LMG field blank — excluded from upload"]);
  for (const s of singlePlanCols) otherRows.push([s, "Single plan option — system auto-selects; column left off the upload"]);
  for (const pv of shaped.pivots) otherRows.push([pv.header, pv.role]);
  for (const x of nonLmgCols) otherRows.push([x, "Recognized non-LMG product (Medical/Medicare/Critical Illness/etc.) — not part of the LMG ancillary upload"]);
  const otherGrid = otherRows.length
    ? [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"]].concat(otherRows)
    : [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"], ["None", "Every source column mapped and every LMG column had data."]];
  if (!QUOTE_MODE) writeSheet(workbook, "Other Columns", otherGrid, "#AF0061", "#AF0061"); // Plum tab

  // HEADER MAP — the user confirms unrecognized columns here; the tool applies
  // them on the next run and remembers them. Fill the 'Map To' cell with an LMG
  // field name and re-run to include that column.
  const hmHeaderRow = ["SOURCE HEADER", "MAP TO LMG FIELD", "SUGGESTED", "CONFIDENCE", "STATUS", "SHARE (copy → submit on hub)"];
  const hmRows: string[][] = [];
  const hmSeen = new Set<string>();
  const shareLine = (src: string, to: string): string => to ? `${src} | ${to}` : "";
  for (const cr of colReview) {
    hmSeen.add(normHeader(cr.header));
    let status = "No close match — type the LMG field in Map To and re-run";
    if (cr.status === "user") status = "Applied — your mapping";
    else if (cr.status === "auto") status = "Applied automatically — verify (clear Map To to undo)";
    else if (cr.status === "suggest") status = "Suggested — put a field in Map To and re-run to include";
    else if (cr.status === "dup") status = "Duplicate of another column — not used";
    else if (cr.status === "pivot") status = "Used by the vertical-census pivot - no mapping needed";
    else if (cr.status === "split") status = "Split automatically into the fields under SUGGESTED — original kept in RAW DATA";
    const suggested = cr.suggested || (cr.status === "none" ? "(no close match)" : "");
    hmRows.push([cr.header, cr.mapTo, suggested, cr.conf, status, shareLine(cr.header, cr.mapTo)]);
  }
  // Preserve any prior user mappings for columns not present in this file.
  userMap.forEach((fld, normSrc) => {
    if (!hmSeen.has(normSrc)) { const s = userSrc.get(normSrc) || normSrc; hmRows.push([s, fld, "", "confirmed", "Applied — your mapping (column not in this file)", shareLine(s, fld)]); }
  });
  const hmGrid = hmRows.length
    ? [hmHeaderRow].concat(hmRows)
    : [hmHeaderRow, ["(none)", "", "", "", "Every column mapped cleanly — nothing to confirm"]];
  writeSheet(workbook, "HEADER MAP", hmGrid, "#C55A11", "#C55A11"); // orange tab
  // Every LMG field in column H, and 'Map To' becomes a dropdown of that list
  // (warning style, so the legacy pre-3.0 names can still be typed by hand).
  const hmWs = workbook.getWorksheet("HEADER MAP");
  const optCol = hmHeaderRow.length + 1; // one blank column gap
  hmWs.getRangeByIndexes(0, optCol, LMG_FIELDS.length + 1, 1).setValues([["LMG FIELDS (Map To options)"]].concat(LMG_FIELDS.map(f => [f])));
  const optHdr = hmWs.getRangeByIndexes(0, optCol, 1, 1).getFormat();
  optHdr.getFill().setColor("#C55A11"); optHdr.getFont().setColor("#FFFFFF"); optHdr.getFont().setBold(true);
  hmWs.getRangeByIndexes(0, optCol, 1, 1).getEntireColumn().getFormat().autofitColumns();
  const optLetter = String.fromCharCode(65 + optCol);
  const dv = hmWs.getRangeByIndexes(1, 1, Math.max(hmRows.length, 1) + 50, 1).getDataValidation(); // +50 rows for hand-added headers
  dv.setRule({ list: { inCellDropDown: true, source: `=$${optLetter}$2:$${optLetter}$${LMG_FIELDS.length + 1}` } });
  dv.setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.warning, title: "Not an LMG field", message: "Pick a field from the list in column " + optLetter + " (legacy names still work — choose Yes to keep)." });

  // ---- Highlight validation issues by severity ----
  const HL: { [k: string]: string } = { INFO: "#BDD7EE", WARNING: "#FFEB9C", ERROR: "#FFC7CE" };
  const sevRank: { [k: string]: number } = { INFO: 0, WARNING: 1, ERROR: 2 };
  // LMG tab: color each offending cell. Apply INFO -> WARNING -> ERROR so the
  // most severe color wins when a cell has more than one issue.
  const ordered = QUOTE_MODE ? [] : issues.slice().sort((a, b) => (sevRank[a[0]] || 0) - (sevRank[b[0]] || 0));
  for (const it of ordered) {
    const rowNum = parseInt(it[2], 10); // LMG ROW
    if (isNaN(rowNum)) continue;
    const rIdx = rowNum - 1; // header at sheet row 0; data rows occupy 1..N
    if (rIdx < 1 || rIdx > lmgRowsOut.length) continue;
    const color = HL[it[0]] || HL.INFO;
    if (it[4] === "(row)") {
      lmgSheet.getRangeByIndexes(rIdx, 0, 1, keptCount).getFormat().getFill().setColor(color);
    } else {
      const full = LMG_FIELDS.indexOf(it[4]);
      const fc = full >= 0 ? keptCols.indexOf(full) : -1;
      if (fc >= 0) lmgSheet.getRangeByIndexes(rIdx, fc, 1, 1).getFormat().getFill().setColor(color);
    }
  }
  // VALIDATION ISSUES tab: color each SEVERITY cell to match.
  if (issues.length) {
    for (let i = 0; i < issues.length; i++) {
      const color = HL[issues[i][0]];
      if (color) issuesSheet.getRangeByIndexes(i + 1, 0, 1, 1).getFormat().getFill().setColor(color);
    }
  }

  // Tab order: LMG is #1 (LaunchMyGroup uploads the first sheet only), NCAM #2.
  firstSheet.setPosition(0);
  if (ncamSheet) ncamSheet.setPosition(1);
  firstSheet.activate();

  // ----------------------------------------------------------------------
  // Helper: replace-or-create a sheet, write grid, style header row.
  // ----------------------------------------------------------------------
  function writeSheet(wb: ExcelScript.Workbook, name: string, grid: string[][], headerHex: string, tabColor: string) {
    const existing = wb.getWorksheet(name);
    if (existing) existing.delete();
    const ws = wb.addWorksheet(name);
    ws.setTabColor(tabColor);
    writeGrid(ws, grid);
    if (grid.length && grid[0].length) {
      const hdr = ws.getRangeByIndexes(0, 0, 1, grid[0].length);
      hdr.getFormat().getFill().setColor(headerHex);
      hdr.getFormat().getFont().setColor("#FFFFFF");
      hdr.getFormat().getFont().setBold(true);
      const used = ws.getUsedRange();
      if (used) used.getFormat().autofitColumns();
      ws.getFreezePanes().freezeRows(1);
    }
    return ws;
  }

  function writeGrid(ws: ExcelScript.Worksheet, grid: string[][]) {
    if (!grid.length) return;
    const rows = grid.length;
    const cols = grid.reduce((m, r) => Math.max(m, r.length), 0);
    // pad ragged rows
    const padded = grid.map(r => { const a = r.slice(); while (a.length < cols) a.push(""); return a; });
    ws.getRangeByIndexes(0, 0, rows, cols).setValues(padded);
  }

  // ======================================================================
  // PURE HELPER FUNCTIONS
  // ======================================================================
  function cleanSpecial(v: string): string {
    // "#" -> " UNIT "; every other non-alphanumeric (except space) -> space; collapse.
    return v.replace(/#/g, " UNIT ").replace(/[^A-Za-z0-9 ]+/g, " ").replace(/\s+/g, " ").trim();
  }

  // Names / plan / class values: like cleanSpecial but apostrophes and hyphens stay
  // (curly apostrophes become straight ones).
  function cleanSpecialKeep(v: string): string {
    return v.replace(/[\u2018\u2019]/g, "'").replace(/[^A-Za-z0-9 '\-]+/g, " ").replace(/\s+/g, " ").trim();
  }

  // True when an MM/DD/YYYY date is after today.
  function isFutureDate(mmddyyyy: string): boolean {
    const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
    if (!m) return false;
    const now = new Date();
    const d = parseInt(m[3], 10) * 10000 + parseInt(m[1], 10) * 100 + parseInt(m[2], 10);
    const t = now.getFullYear() * 10000 + (now.getMonth() + 1) * 100 + now.getDate();
    return d > t;
  }

  function ageFromDob(mmddyyyy: string): number {
    const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
    if (!m) return -1;
    const mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10);
    const now = new Date();
    let age = now.getFullYear() - yr;
    const moNow = now.getMonth() + 1, daNow = now.getDate();
    if (moNow < mo || (moNow === mo && daNow < da)) age--;
    return age;
  }

  function normHeader(h: string): string {
    let s = String(h == null ? "" : h);
    // Drop parenthetical text, innermost-out, so nested parens like "Child(ren)"
    // inside an instruction "(... Employee +Child(ren), Family, Waive)" clear fully.
    let prev = "";
    while (s !== prev) { prev = s; s = s.replace(/\([^()]*\)/g, " "); }
    s = s.replace(/\([\s\S]*$/, " "); // drop any unmatched "(" to end of string
    // A leftover ")" means the parens were unbalanced — e.g. real-world header
    // "Status (Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date
    // and Event Type)". Everything from the first "(" was instruction text, so
    // fall back to the part of the original header before that "(".
    if (s.indexOf(")") !== -1) {
      const orig = String(h == null ? "" : h);
      const p = orig.indexOf("(");
      s = p >= 0 ? orig.slice(0, p) : s.replace(/\)/g, " ");
    }
    return s.toUpperCase()
      .replace(/[^A-Z0-9]+/g, " ")  // punctuation -> space
      .replace(/\s+/g, " ")
      .trim();
  }

  // ---- Combined-column split parsers (rules §11c) ----
  // splitCombinedName is also used by the Stage 2 dependent-name split so
  // both share one behavior (suffix + middle-initial rules).
  function splitCombinedName(v: string): { first: string, mi: string, last: string, guessed: boolean } | null {
    let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim();
    if (s === "") return null;
    let suffix = "";
    const sm = s.match(/[,\s]+(JR|SR|II|III|IV|V)\.?$/i);
    if (sm) { suffix = sm[1].toUpperCase(); s = s.slice(0, s.length - sm[0].length).trim(); }
    const withSfx = (last: string): string => suffix !== "" ? (last + " " + suffix).trim() : last;
    const stripDot = (t: string): string => t.replace(/\.$/, "");
    if (s.indexOf(",") >= 0) {
      // "Last, First [Middle]" — the comma makes the split reliable.
      const cp = s.indexOf(",");
      const last = s.slice(0, cp).trim();
      const rest = s.slice(cp + 1).replace(/,/g, " ").replace(/\s+/g, " ").trim();
      if (last === "" && rest === "") return null;
      const t = rest === "" ? [] : rest.split(" ");
      return { first: t.length > 0 ? t[0] : "", mi: t.length > 1 ? stripDot(t[1]) : "", last: withSfx(last), guessed: false };
    }
    const t = s.split(" ");
    if (t.length === 1) return { first: "", mi: "", last: withSfx(t[0]), guessed: true };
    if (t.length === 2) return { first: t[0], mi: "", last: withSfx(t[1]), guessed: true };
    if (t.length === 3 && /^[A-Za-z]\.?$/.test(t[1])) return { first: t[0], mi: stripDot(t[1]), last: withSfx(t[2]), guessed: true };
    return { first: t[0], mi: "", last: withSfx(t.slice(1).join(" ")), guessed: true }; // "Mary Van Buren" style
  }

  // "Austin, TX 78701" / "Round Rock TX 78664-1234" / "San Juan, Puerto Rico 00901".
  // Right-to-left: trailing zip, then a valid USPS code or full state name
  // (longest match first), remainder = city. zipOptional covers headers like
  // "CITY STATE" that legitimately carry no zip. Returns null when not confident.
  function parseCityStateZip(v: string, zipOptional: boolean): { city: string, state: string, zip: string } | null {
    let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim();
    if (s === "") return null;
    let zip = "";
    const zm = s.match(/[,\s]+(\d{5}(?:-\d{4})?)$/);
    if (zm) { zip = zm[1]; s = s.slice(0, s.length - zm[0].length); }
    else if (!zipOptional) return null;
    s = s.replace(/[\s,]+$/, "");
    let state = "";
    const ab = s.match(/[,\s]+([A-Za-z]{2})\.?$/);
    if (ab && VALID_USPS.has(ab[1].toUpperCase())) {
      state = ab[1].toUpperCase();
      s = s.slice(0, s.length - ab[0].length);
    } else {
      const words = s.split(/[\s,]+/).filter(w => w !== "");
      for (let n = Math.min(3, words.length - 1); n >= 1; n--) {
        const cand = words.slice(words.length - n).join(" ").toUpperCase();
        if (STATES[cand]) {
          state = STATES[cand];
          for (let k = 0; k < n; k++) s = s.replace(/[\s,]+[^\s,]+$/, "");
          break;
        }
      }
    }
    if (state === "") return null;
    const city = s.replace(/[\s,]+$/, "").trim();
    if (city === "" || /^\d+$/.test(city)) return null;
    return { city: city, state: state, zip: zip };
  }

  // Full "123 Main St, Apt 4, Austin, TX 78701". Peels zip + state via
  // parseCityStateZip, then the last comma segment is the city and the rest is
  // the street. No comma boundary -> null (the caller falls back to peeling
  // just State+ZIP so nothing is lost).
  function parseFullAddress(v: string): { street: string, city: string, state: string, zip: string } | null {
    const p = parseCityStateZip(v, false);
    if (p === null || p.zip === "") return null;
    const cp = p.city.lastIndexOf(",");
    if (cp < 0) return null;
    const street = p.city.slice(0, cp).replace(/[\s,]+$/, "").trim();
    const city = p.city.slice(cp + 1).trim();
    if (street === "" || city === "") return null;
    return { street: street, city: city, state: p.state, zip: p.zip };
  }

  function normalizeRelationship(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (["EMPLOYEE", "EE", "EMP", "SELF", "PRIMARY", "SUBSCRIBER", "E", "EMPL"].indexOf(n) !== -1) return "Employee";
    if (["SPOUSE", "SP", "SPS", "WIFE", "HUSBAND", "SPOUSE PARTNER"].indexOf(n) !== -1) return "Spouse";
    if (["DOMESTIC PARTNER", "DP", "PARTNER", "CIVIL UNION"].indexOf(n) !== -1) return "Domestic Partner";
    if (["CHILD", "CH", "DEPENDENT", "DEP", "SON", "DAUGHTER", "CHILDREN", "STEPCHILD", "STEPSON", "STEPDAUGHTER"].indexOf(n) !== -1) return "Child";
    return ""; // unknown -> caller flags
  }

  function normalizeGender(v: string): string {
    const n = normHeader(v);
    if (n === "M" || n === "MALE") return "M";
    if (n === "F" || n === "FEMALE") return "F";
    return "";
  }

  function normalizeEnrollWaive(v: string): string {
    // Basic Life Cov Level, Dependent Basic Life, STD, LTD all read "EE" / "Waive" (owner decision 2026-09-10; was Enroll/Waive).
    const n = normHeader(v);
    if (n === "") return "";
    if (WAIVE_WORDS.has(n) || ENROLL_WAIVE_NO.indexOf(n) !== -1) return "Waive";
    if (["ENROLL", "ENROLLED", "ENROLLEE", "ELECT", "ELECTED", "YES", "Y", "ACTIVE", "E", "ENROLLING", "ADD", "EE"].indexOf(n) !== -1) return "EE";
    if (["EE", "ES", "EC", "FAM"].indexOf(normalizeTier(v)) !== -1) return "EE"; // a tier (Employee Only, Family...) on a vertical census = enrolled
    return v.trim();
  }

  function normalizeYesNo(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (["Y", "YES", "TRUE", "1", "X"].indexOf(n) !== -1) return "Yes";
    if (["N", "NO", "FALSE", "0"].indexOf(n) !== -1) return "No";
    return v.trim();
  }

  function normalizeTier(v: string): string {
    const n = normHeader(v);
    if (n === "") return "";
    if (WAIVE_WORDS.has(n)) return "W";
    if (["EE", "EO", "E", "EMPLOYEE", "EMPLOYEE ONLY", "EMP ONLY", "SELF", "SUBSCRIBER ONLY"].indexOf(n) !== -1) return "EE";
    if (n === "ES" || n === "EC" || n === "FAM" || n === "W") return n;
    const hasSpouse = /\bSPOUSE\b|\bSP\b|DOMESTIC|PARTNER/.test(n);
    const hasChild = /\bCHILD\b|\bCHILDREN\b|\bCH\b|DEPENDENT|\bDEP\b|\bKIDS?\b/.test(n);
    const hasFamily = /FAMILY|\bFAM\b/.test(n);
    if (hasFamily || (hasSpouse && hasChild)) return "FAM";
    if (hasSpouse) return "ES";
    if (hasChild) return "EC";
    if (/EMPLOYEE|\bEMP\b/.test(n)) return "EE";
    return "";
  }

  function tierToLmg(tier: string, raw: string, isEmployee: boolean, productExists: boolean, waiveText: string): string {
    if (tier === "W") return waiveText;
    if (tier !== "") return tier;
    if (raw.trim() !== "") return raw.trim(); // unrecognized (e.g. EE+1): keep the original; the caller raises a WARNING (rules section 5)
    // blank tier
    if (isEmployee && productExists) return waiveText; // NCAM would default blank to EE, so force waive
    return raw; // keep whatever was there (blank) for dependents
  }

  function lmgToNcamTier(v: string, productExists: boolean): string {
    if (v === LMG_WAIVE_TEXT) return "W";
    if (v === "" && productExists) return "W"; // NCAM forces blank employee coverage to W
    return v;
  }

  function volLife(v: string, isEmployee: boolean, waivedValue: string, zeroWaives: boolean): string {
    if (!isEmployee) return v.trim();
    const n = normHeader(v);
    if (n === "" || WAIVE_WORDS.has(n)) return waivedValue;
    const num = v.replace(/[^\d.]/g, "");
    if (num !== "") {
      if (zeroWaives && parseFloat(num) === 0) return waivedValue; // Dependent: 0 -> Waive
      return num;
    }
    return v.trim();
  }

  function formatDate(v: string | number): string {
    if (v === "" || v == null) return "";
    // Excel serial date number
    if (typeof v === "number" || /^\d+(\.\d+)?$/.test(String(v))) {
      const serial = typeof v === "number" ? v : parseFloat(String(v));
      if (serial > 59 && serial < 80000) { // plausible date serial (1900..~2119)
        const ms = Math.round((serial - 25569) * 86400 * 1000); // 25569 = 1970-01-01 in Excel serial
        const d = new Date(ms);
        return pad2(d.getUTCMonth() + 1) + "/" + pad2(d.getUTCDate()) + "/" + d.getUTCFullYear();
      }
    }
    const s = String(v).trim();
    // MM/DD/YYYY or M/D/YY or with - .
    let m = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{2,4})$/);
    if (m) {
      let mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10);
      // Two-digit year: 20YY unless that is after the current year, else 19YY.
      if (yr < 100) yr += (2000 + yr) <= new Date().getFullYear() ? 2000 : 1900;
      if (isRealDate(yr, mo, da)) return pad2(mo) + "/" + pad2(da) + "/" + yr;
    }
    // YYYY-MM-DD
    m = s.match(/^(\d{4})[\/\-.](\d{1,2})[\/\-.](\d{1,2})/);
    if (m) {
      const yr = parseInt(m[1], 10), mo = parseInt(m[2], 10), da = parseInt(m[3], 10);
      if (isRealDate(yr, mo, da)) return pad2(mo) + "/" + pad2(da) + "/" + yr;
    }
    return ""; // unparseable -> caller flags
  }

  function pad2(n: number): string { return n < 10 ? "0" + n : String(n); }

  // Day checked against the month (02/30 and 04/31 are rejected; leap years honored).
  function isRealDate(yr: number, mo: number, da: number): boolean {
    if (mo < 1 || mo > 12 || da < 1) return false;
    const d = new Date(Date.UTC(yr, mo - 1, da));
    return d.getUTCMonth() === mo - 1 && d.getUTCDate() === da;
  }

  // ---- Fuzzy header similarity (word overlap + character bigram similarity) ----
  function fuzzyBigrams(s: string): string[] {
    const g: string[] = [];
    for (let i = 0; i < s.length - 1; i++) g.push(s.substr(i, 2));
    return g;
  }
  function diceCoef(a: string, b: string): number {
    if (a === b) return 1;
    const A = fuzzyBigrams(a), B = fuzzyBigrams(b);
    if (A.length === 0 || B.length === 0) return 0;
    const counts = new Map<string, number>();
    for (const g of A) counts.set(g, (counts.get(g) || 0) + 1);
    let match = 0;
    for (const g of B) { const n = counts.get(g) || 0; if (n > 0) { counts.set(g, n - 1); match++; } }
    return (2 * match) / (A.length + B.length);
  }
  function tokenJaccard(a: string, b: string): number {
    const A = a.split(" ").filter(x => x !== ""), B = b.split(" ").filter(x => x !== "");
    if (A.length === 0 || B.length === 0) return 0;
    const setA = new Set<string>(A), setB = new Set<string>(B);
    let inter = 0;
    setA.forEach(x => { if (setB.has(x)) inter++; });
    const uni = new Set<string>(A.concat(B)).size;
    return inter / uni;
  }
  function simScore(a: string, b: string): number {
    return 0.6 * tokenJaccard(a, b) + 0.4 * diceCoef(a, b);
  }
  function bestFuzzy(norm: string, candidates: { field: string, text: string }[]): { field: string, score: number } {
    let bestField = "", bestScore = 0;
    for (const cand of candidates) {
      const s = simScore(norm, cand.text);
      if (s > bestScore) { bestScore = s; bestField = cand.field; }
    }
    return { field: bestField, score: bestScore };
  }

  function normalizeDepRel(v: string): string {
    const r = normalizeRelationship(v);
    return (r === "" || r === "Employee") ? "" : r; // dependents are Spouse / Domestic Partner / Child
  }

  // ======================================================================
  // PRE-PASS: reshape "nightmare" layouts into one clean row per person.
  //   Stage S — split combined columns (Name / City-State-Zip / full address).
  //   Stage 0 — product-coded routing (generic value + Product column).
  //   Stage 1 — collapse stacked product rows (gated on a Product column).
  //   Stage 2 — explode "Dependent N <field>" columns into dependent rows.
  // Transforms only when confident; flags anything out of the norm.
  // ======================================================================
  function preprocess(headers0: string[], rows0: string[][], aliasToFieldMap: Map<string, string>): { headers: string[], rows: string[][], splits: { header: string, into: string }[], pivots: { header: string, role: string }[], srcRows: string[] } {
    const splits: { header: string, into: string }[] = [];
    const pivots: { header: string, role: string }[] = [];
    if (rows0.length === 0) return { headers: headers0, rows: rows0, splits: splits, pivots: pivots, srcRows: [] };

    let headers = headers0.slice();
    let rows = rows0.map(r => r.slice());
    // RAW DATA row number(s) behind each row (header = row 1), kept in step with
    // rows through the collapse and explode stages.
    let srcRows: number[][] = rows0.map((_r, i) => [i + 2]);

    // ---- STAGE S: combined-column split ("data extrapolation", rules §11c) ----
    // A single column holding a full name ("Smith, John" / "Jane Doe"), a
    // "City, State ZIP" value, or a whole "Address, City, State ZIP" is split
    // into the separate LMG fields. Runs FIRST so the collapse/explode stages
    // below can key on the split First/Last columns. Transforms only when
    // confident; below-threshold columns are flagged and passed through (§10).
    {
      const snorm = headers.map(h => normHeader(h));
      const nCols = snorm.length; // freeze: columns appended below are never candidates
      const SPLIT_CONFIRM = 0.60; // named combined header: >=60% of non-blank values parse
      const SPLIT_STRONG = 0.90;  // value-shape-only (header is just CITY): >=90%
      const SPLIT_ADDR = 0.70;    // full address (always value-gated): >=70%
      const SPLIT_HINT = 0.30;    // 30%..threshold -> WARNING, no transform
      const depSkipRe = /^DEP(?:ENDENT)?\s*\d/; // Stage 2's territory
      const val = (row: string[], i: number): string => (row[i] == null) ? "" : String(row[i]).trim();
      const scan = (col: number, tryParse: (v: string) => boolean): { frac: number, nonBlank: number, hits: number } => {
        let nonBlank = 0, hits = 0;
        for (const row of rows) { const v = val(row, col); if (v === "") continue; nonBlank++; if (tryParse(v)) hits++; }
        return { frac: nonBlank ? hits / nonBlank : 0, nonBlank: nonBlank, hits: hits };
      };
      // Value-shape-only triggers on tiny columns (<5 non-blank) demand a 100% parse.
      const confident = (r: { frac: number, nonBlank: number }, threshold: number, valueOnly: boolean): boolean =>
        r.nonBlank > 0 && (valueOnly && r.nonBlank < 5 ? r.frac === 1 : r.frac >= threshold);
      const hasFieldNow = (field: string): boolean => {
        for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return true;
        return false;
      };
      const ensureField = (field: string): number => {
        for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return i;
        const idx = headers.length;
        headers.push(field);
        for (const row of rows) row.push("");
        return idx;
      };
      const setIfBlank = (row: string[], i: number, v: string) => { if (v !== "" && val(row, i) === "") row[i] = v; };
      // Consume the combined column BEFORE ensureField so a column whose header
      // itself maps to a target field (e.g. "City", "Address") gets a fresh
      // target column instead of pointing back at itself.
      const consume = (col: number, into: string, hits: number, nonBlank: number) => {
        splits.push({ header: headers[col], into: into });
        addLog(`Combined-column split: '${headers[col]}' -> ${into} (${hits}/${nonBlank} non-blank rows parsed).`);
        headers[col] = ""; // consumed; raw values stay on RAW DATA
      };

      // -- Shape 1: combined full-name column --
      const NAME_HEADERS = ["NAME", "FULL NAME", "EMPLOYEE NAME", "EMP NAME", "MEMBER NAME", "ENROLLEE NAME", "SUBSCRIBER NAME", "PARTICIPANT NAME", "EMPLOYEE FULL NAME", "MEMBER FULL NAME", "NAME LAST FIRST", "NAME FIRST LAST"];
      const NAME_BLOCK_RE = /FIRST|LAST|MIDDLE|CARRIER|PLAN|PROVIDER|BENEFICIARY|OFFICE|FACILITY|PCP|COMPANY|GROUP|EMPLOYER|CONTACT|AGENT|BROKER|SPONSOR|LOCATION|DOCTOR|DENTIST|PHYSICIAN|DEPENDENT|\bDEP\b|SPOUSE|CHILD|BANK|POLICY/;
      const isNameHeader = (n: string): boolean =>
        NAME_HEADERS.indexOf(n) !== -1 || (n !== "" && /( |^)NAME$/.test(n) && !NAME_BLOCK_RE.test(n));
      const nameParses = (v: string): boolean => {
        const p = splitCombinedName(v);
        return p !== null && p.first !== "" && /[A-Za-z]/.test(p.first) && /[A-Za-z]/.test(p.last);
      };
      for (let c = 0; c < nCols; c++) {
        if (headers[c] === "" || snorm[c] === "" || depSkipRe.test(snorm[c]) || !isNameHeader(snorm[c])) continue;
        if (hasFieldNow("Member Last Name") && hasFieldNow("First Name")) {
          addIssue("INFO", 0, "", headers[c], "", "Combined name column ignored — separate First/Last Name columns are present and were used", "Nothing to do unless First/Last are wrong; data kept in RAW DATA");
          continue;
        }
        const r = scan(c, nameParses);
        if (confident(r, SPLIT_CONFIRM, false)) {
          const hdr = headers[c];
          consume(c, "Member Last Name + First Name + Middle Initial", r.hits, r.nonBlank);
          const li = ensureField("Member Last Name"), fi = ensureField("First Name"), mi = ensureField("Middle Initial");
          let guessed = 0;
          for (const row of rows) {
            const v = val(row, c);
            if (v === "") continue;
            const p = splitCombinedName(v);
            if (p === null) continue;
            if (p.first === "" && p.guessed) { // single word — cannot split
              setIfBlank(row, li, p.last);
              addIssue("WARNING", 0, v, hdr, v, "Only one name word — could not split into First + Last", "Add the missing first name on the LMG tab");
              continue;
            }
            setIfBlank(row, li, p.last); setIfBlank(row, fi, p.first); setIfBlank(row, mi, p.mi);
            if (p.guessed) guessed++;
          }
          if (guessed > 0) addIssue("INFO", 0, "", hdr, "", `No comma in ${guessed} of ${r.nonBlank} name(s); split as 'First [MI] Last'`, "Verify first vs last name on the LMG tab");
          addIssue("INFO", 0, "", hdr, "", "Combined name column split into Last / First / Middle Initial", "Verify on the LMG tab; original kept in RAW DATA");
        } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
          addIssue("WARNING", 0, "", headers[c], "", "Column looks like a combined full-name column but too many values did not parse — not split automatically", "Split First/Last manually (or fix the values) and re-run");
        }
      }

      // -- Shape 2: combined "City, State ZIP" column --
      const CSZ_HEADERS = ["CITY STATE ZIP", "CITY ST ZIP", "CITY STATE ZIP CODE", "CITY ST ZIP CODE", "CITY STATE POSTAL CODE", "CSZ", "CITY STATE", "CITY AND STATE"];
      const CITY_ONLY = ["CITY", "TOWN", "MEMBER CITY"];
      for (let c = 0; c < nCols; c++) {
        if (headers[c] === "" || depSkipRe.test(snorm[c])) continue;
        const isCsz = CSZ_HEADERS.indexOf(snorm[c]) !== -1;
        const isCityShaped = !isCsz && CITY_ONLY.indexOf(snorm[c]) !== -1;
        if (!isCsz && !isCityShaped) continue;
        // Zip is optional only for named combined headers that don't promise one.
        const zipOpt = isCsz && snorm[c].indexOf("ZIP") === -1 && snorm[c] !== "CSZ";
        const r = scan(c, (v: string) => parseCityStateZip(v, zipOpt) !== null);
        if (confident(r, isCsz ? SPLIT_CONFIRM : SPLIT_STRONG, !isCsz)) {
          const hdr = headers[c];
          consume(c, "City + State + Zip Code", r.hits, r.nonBlank);
          const ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code");
          for (const row of rows) {
            const v = val(row, c);
            if (v === "") continue;
            const p = parseCityStateZip(v, zipOpt);
            if (p) { setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); }
            else {
              setIfBlank(row, ci, v); // keep the whole value — no silent loss
              addIssue("WARNING", 0, "", hdr, v, "Could not parse 'City, State ZIP' — whole value placed in City", "Split this row manually on the LMG tab");
            }
          }
          addIssue("INFO", 0, "", hdr, "", isCsz ? "Combined City/State ZIP column split into City, State, Zip Code" : `'${hdr}' contained full 'City, State ZIP' values — split into City, State, Zip Code`, "Verify on the LMG tab; original kept in RAW DATA");
        } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
          addIssue("WARNING", 0, "", headers[c], "", "Some values look like combined 'City, State ZIP' but too many did not parse — not split automatically", "Fix the combined values (City, ST 12345) or split the column manually, then re-run");
        }
      }

      // -- Shape 3: full "Address, City, State ZIP" column (always value-gated) --
      const ADDR_EXTRA = ["FULL ADDRESS", "COMPLETE ADDRESS", "HOME ADDRESS", "MAILING ADDRESS", "FULL HOME ADDRESS"];
      const isAddrHeader = (n: string): boolean =>
        (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(n) && !/EMAIL|IP/.test(n)) || ADDR_EXTRA.indexOf(n) !== -1;
      // Skipped entirely when separate City AND State AND Zip columns exist —
      // a street column can legitimately end in digits.
      if (!(hasFieldNow("City") && hasFieldNow("State") && hasFieldNow("Zip Code"))) {
        for (let c = 0; c < nCols; c++) {
          if (headers[c] === "" || depSkipRe.test(snorm[c]) || !isAddrHeader(snorm[c])) continue;
          const r = scan(c, (v: string) => parseFullAddress(v) !== null);
          if (confident(r, SPLIT_ADDR, true)) {
            const hdr = headers[c];
            consume(c, "Member Street Address + City + State + Zip Code", r.hits, r.nonBlank);
            const sti = ensureField("Member Street Address"), ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code");
            for (const row of rows) {
              const v = val(row, c);
              if (v === "") continue;
              const p = parseFullAddress(v);
              if (p) { setIfBlank(row, sti, p.street); setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); continue; }
              const csz = parseCityStateZip(v, false); // no comma boundary — peel State+ZIP, rest stays street
              if (csz && csz.zip !== "") {
                setIfBlank(row, sti, csz.city); setIfBlank(row, si, csz.state); setIfBlank(row, zi, csz.zip);
                addIssue("WARNING", 0, "", hdr, v, "Could not separate street from city (no comma) — both kept in Member Street Address", "Move the city out of the street address for this row");
              } else {
                setIfBlank(row, sti, v); // keep the whole value — no silent loss
                addIssue("WARNING", 0, "", hdr, v, "Could not parse full address — whole value placed in Member Street Address", "Split City/State/Zip for this row manually");
              }
            }
            addIssue("INFO", 0, "", hdr, "", "Combined full-address column split into Street / City / State / Zip", "Verify a few rows on the LMG tab; original kept in RAW DATA");
            break; // only the first qualifying column splits; others merge into the street as usual
          } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) {
            addIssue("WARNING", 0, "", headers[c], "", "Some values appear to contain City/State/ZIP inside the address — not split automatically", "Review — city/state/zip may be embedded in the street address");
          }
        }
      }
    }

    // Column lookups are computed AFTER Stage S so split-out First/Last/City
    // columns are visible to the collapse/explode stages below.
    const norm = headers.map(h => normHeader(h));
    const findCol = (names: string[]): number => {
      for (let i = 0; i < norm.length; i++) if (names.indexOf(norm[i]) !== -1) return i;
      return -1;
    };
    const ssnCol = findCol(["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SS", "MEMBER SSN", "TAX ID"]);
    const lastCol = findCol(["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"]);
    const firstCol = findCol(["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"]);
    const dobCol = findCol(["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"]);
    const relCol = findCol(["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"]);
    const genderCol = findCol(["GENDER", "SEX", "BIRTH SEX"]);
    const cell = (row: string[], i: number): string => (i < 0 || row[i] == null) ? "" : String(row[i]).trim();
    const productKey = (v: string): string => {
      const n = normHeader(v);
      if (n === "") return "";
      if (/DENTAL/.test(n)) return "DENTAL";
      if (/VISION/.test(n)) return "VISION";
      if (/MEDICAL|HEALTH/.test(n)) return "MEDICAL";
      if (/VOLUNTARY|VOL LIFE|SUPPLEMENTAL/.test(n)) return "VOL_LIFE";
      if (/BASIC LIFE|LIFE|AD D/.test(n)) return "BASIC_LIFE";
      if (/\bSTD\b|SHORT TERM/.test(n)) return "STD";
      if (/\bLTD\b|LONG TERM/.test(n)) return "LTD";
      return "";
    };
    let productCol = findCol(["PRODUCT", "COVERAGE", "COVERAGE TYPE", "PLAN TYPE", "BENEFIT", "BENEFIT TYPE", "LINE OF COVERAGE", "PRODUCT LINE", "COVERAGE LINE", "PRODUCT TYPE", "COVERAGE NAME", "BENEFIT PLAN TYPE", "LINE OF BUSINESS", "LOB"]);
    // Header not recognized: find the column whose values name the products
    // (Dental, Vision, Basic Life, STD...) on 80%+ of rows, with 2+ different products.
    if (productCol < 0) {
      for (let i = 0; i < headers.length && productCol < 0; i++) {
        if (aliasToFieldMap.has(norm[i])) continue;
        let filled = 0, hits = 0;
        const kinds = new Set<string>();
        for (const row of rows) {
          const v = cell(row, i);
          if (v === "") continue;
          filled++;
          const k = productKey(v);
          if (k !== "") { hits++; kinds.add(k); }
        }
        if (filled > 0 && hits / filled >= 0.8 && kinds.size >= 2) {
          productCol = i;
          addLog(`Product column found by its values: '${headers[i]}' (${Array.from(kinds).join(", ")}).`);
        }
      }
    }

    const inheritRe = /ADDRESS|ADDR|STREET|CITY|TOWN|STATE|ZIP|POSTAL|LOCATION|DIVISION|BRANCH/;

    // ---- STAGE 0: product-coded routing ----
    // When a row has a generic value column (Coverage Tier/Level, Plan, Election,
    // Class) plus a product column (Plan Type = Dental/Vision/Life/STD/LTD), move
    // that generic value into the product-specific LMG field for that row's product.
    const genericCols: { col: number, concept: string }[] = [];
    for (let i = 0; i < norm.length; i++) {
      const h = norm[i];
      if (i === productCol) continue;
      if (/DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(h)) continue; // already product-specific
      // COV / COVERAGE / TIER / LEVEL -> the election tier (EE/ES/EC/FAM/W).
      if (h === "COVERAGE DETAILS") continue; // free-text detail, not the tier
      if (/\bCOV/.test(h) || /\bTIER\b/.test(h) || h === "LEVEL") genericCols.push({ col: i, concept: "TIER" });
      // PLAN / SELECT / ELECT -> which plan to enroll on.
      else if (/\bPLAN\b/.test(h) || /SELECT/.test(h) || /ELECT/.test(h)) genericCols.push({ col: i, concept: "PLAN" });
      else if (h === "CLASS" || h === "CLASS WAIVE") genericCols.push({ col: i, concept: "CLASS" });
      else if (productCol >= 0 && /AMOUNT|\bAMT\b|VOLUME|BENEFIT/.test(h)) genericCols.push({ col: i, concept: "AMOUNT" });
      else if (productCol >= 0 && /PREMIUM|\bRATE\b|COST|CONTRIBUTION|DEDUCTION|PER PAY/.test(h)) genericCols.push({ col: i, concept: "COST" });
    }
    // Vertical census: a column whose values are tiers (Employee Only, Family, Waived...)
    // is the tier column whatever its header says (Election, Option, Enrollment Level...).
    if (productCol >= 0) {
      const looksLikeTiers = (i: number): boolean => {
        let filled = 0, hits = 0;
        for (const row of rows) {
          const v = cell(row, i);
          if (v === "") continue;
          filled++;
          if (normalizeTier(v) !== "") hits++;
        }
        return filled > 0 && hits / filled >= 0.8;
      };
      for (let i = 0; i < norm.length; i++) {
        if (i === productCol || norm[i] === "" || /DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(norm[i])) continue;
        const g = genericCols.filter(x => x.col === i)[0];
        if (g && g.concept === "TIER") continue;
        if (g && g.concept !== "PLAN") continue;
        if (!g && aliasToFieldMap.has(norm[i])) continue; // a real field (Relationship, Gender...)
        if (!looksLikeTiers(i)) continue;
        if (g) g.concept = "TIER"; else genericCols.push({ col: i, concept: "TIER" });
        addLog(`Tier column found by its values: '${headers[i]}'.`);
      }
    }
    const ROUTING: { [c: string]: { [p: string]: string } } = {
      // A tier on a Basic Life / STD / LTD row means enrolled (normalizeEnrollWaive reads it as EE / Waive).
      TIER: { DENTAL: "Dental Cov Level", VISION: "Vision Cov Level", BASIC_LIFE: "Basic Life Cov Level", STD: "Short-Term Disability Cov Level", LTD: "Long-Term Disability Cov Level" },
      PLAN: { DENTAL: "Dental Plan Elected", VISION: "Vision Plan Election" },
      CLASS: { BASIC_LIFE: "Life/AD&D Class", STD: "STD Class", LTD: "LTD Class" },
      AMOUNT: { VOL_LIFE: "Employee Voluntary Life Flat Amount" } // on a spouse/child row the roll-up moves it to Spouse/Dependent
    };
    if (productCol >= 0 && genericCols.length > 0) {
      const targetCol = new Map<string, number>();
      const ensureCol = (field: string): number => {
        if (targetCol.has(field)) return targetCol.get(field) as number;
        for (let i = 0; i < headers.length; i++) { // reuse an existing column that maps to this field
          if (aliasToFieldMap.get(normHeader(headers[i])) === field) { targetCol.set(field, i); return i; }
        }
        const idx = headers.length;
        headers.push(field);
        for (const row of rows) row.push("");
        targetCol.set(field, idx);
        return idx;
      };
      let routed = 0;
      const unrouted = new Map<string, number>(); // product value -> generic values with no output field
      for (const row of rows) {
        const p = productKey(cell(row, productCol));
        for (const gc of genericCols) {
          const map = ROUTING[gc.concept];
          const field = map && p ? map[p] : "";
          const val = cell(row, gc.col);
          if (val === "" || gc.concept === "COST") continue; // per-product cost/premium: not used
          if (field) {
            const ti = ensureCol(field);
            if (cell(row, ti) === "") { row[ti] = val; routed++; }
          } else {
            const pv = cell(row, productCol) || "(blank product)";
            unrouted.set(pv, (unrouted.get(pv) || 0) + 1);
          }
        }
      }
      // Voluntary life, medical and unknown products have no column here: say so, never drop quietly.
      unrouted.forEach((n, pv) => {
        addIssue("INFO", 0, "", "", pv, `${n} value(s) in the shared tier/amount column(s) for '${pv}' rows have no output column`, "Not used on the output; the values stay on RAW DATA");
      });
      // Blank the consumed generic headers so they aren't re-flagged as unrecognized
      // (the values now live in product-specific columns; RAW DATA keeps the originals).
      for (const gc of genericCols) {
        pivots.push({ header: headers[gc.col], role: gc.concept === "COST" ? "Per-product cost/premium on a vertical census; not used"
          : `Shared ${gc.concept.toLowerCase()} column on a vertical census; values moved into each coverage's own column` });
        headers[gc.col] = "";
      }
      if (routed > 0) {
        addLog(`Product routing: moved ${routed} generic value(s) into product-specific columns via '${headers0[productCol]}'.`);
      }
    }

    if (productCol >= 0 && headers[productCol] !== "") {
      let filled = 0, hits = 0;
      for (const row of rows) { const v = cell(row, productCol); if (v !== "") { filled++; if (productKey(v) !== "") hits++; } }
      if (filled > 0 && hits * 2 >= filled) {
        pivots.push({ header: headers[productCol], role: "Coverage column on a vertical census; rows were combined into one line per person" });
        headers[productCol] = "";
      }
    }

    // ---- STAGE 1: collapse stacked product rows ----
    if (productCol >= 0 && (ssnCol >= 0 || (lastCol >= 0 && dobCol >= 0))) {
      const personKey = (row: string[]): string =>
        [cell(row, ssnCol), cell(row, lastCol).toUpperCase(), cell(row, firstCol).toUpperCase(), cell(row, dobCol)].join("|");
      const groups = new Map<string, string[][]>();
      const groupSrc = new Map<string, number[]>();
      const order: string[] = [];
      for (let ri = 0; ri < rows.length; ri++) {
        const r = rows[ri];
        const k = personKey(r);
        if (!groups.has(k)) { groups.set(k, []); groupSrc.set(k, []); order.push(k); }
        (groups.get(k) as string[][]).push(r);
        (groupSrc.get(k) as number[]).push(...srcRows[ri]);
      }
      let collapsed = 0;
      const merged: string[][] = [];
      const mergedSrc: number[][] = [];
      // Only columns we actually keep (map to an LMG field) drive conflict flags.
      // Columns we don't keep — Total Rate, premiums, etc. — legitimately differ
      // between a person's Dental and Vision lines, so they are ignored here.
      const keptForConflict = headers.map(h => aliasToFieldMap.has(normHeader(h)));
      for (const k of order) {
        const grp = groups.get(k) as string[][];
        mergedSrc.push(groupSrc.get(k) as number[]);
        if (grp.length === 1) { merged.push(grp[0]); continue; }
        const memberName = (cell(grp[0], firstCol) + " " + cell(grp[0], lastCol)).trim() || "(stacked rows)";
        const out: string[] = headers.map(() => "");
        for (let c = 0; c < headers.length; c++) {
          let chosen = "";
          for (const r of grp) {
            const v = cell(r, c);
            if (v === "") continue;
            if (chosen === "") chosen = v;
            else if (chosen !== v && c !== productCol && keptForConflict[c]) {
              addIssue("WARNING", 0, memberName, headers[c], `${chosen} / ${v}`, "Conflicting values while collapsing stacked rows for the same person", "Confirm which value is correct");
            }
          }
          out[c] = chosen;
        }
        merged.push(out);
        collapsed += grp.length - 1;
      }
      if (collapsed > 0) {
        rows = merged;
        srcRows = mergedSrc;
        addLog(`Stacked-product collapse: merged ${collapsed} extra row(s) into ${merged.length} person row(s) via the '${headers0[productCol]}' column.`);
      }
    }

    // ---- STAGE 2: explode "Dependent N <field>" columns into rows ----
    const depRe = /^DEP(?:ENDENT)?\s*(\d+)\s+(.+)$/;
    const depGroups = new Map<number, { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number }>();
    const depColSet = new Set<number>();
    for (let i = 0; i < norm.length; i++) {
      const m = norm[i].match(depRe);
      if (!m) continue;
      const idx = parseInt(m[1], 10);
      const sub = m[2];
      if (!depGroups.has(idx)) depGroups.set(idx, { ssn: -1, dob: -1, gender: -1, rel: -1, last: -1, first: -1, name: -1 });
      const g = depGroups.get(idx) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number };
      let matched = true;
      if (sub === "SSN" || sub.indexOf("SOCIAL") === 0 || sub === "SS") g.ssn = i;
      else if (sub === "DOB" || sub.indexOf("BIRTH") >= 0 || sub === "DATE OF BIRTH" || sub === "BORN") g.dob = i;
      else if (sub === "GENDER" || sub === "SEX") g.gender = i;
      else if (sub.indexOf("RELATION") === 0 || sub === "REL" || sub === "TYPE") g.rel = i;
      else if (sub === "LAST NAME" || sub === "LAST") g.last = i;
      else if (sub === "FIRST NAME" || sub === "FIRST") g.first = i;
      else if (sub === "NAME" || sub === "FULL NAME") g.name = i;
      else matched = false;
      if (matched) depColSet.add(i);
    }

    if (depGroups.size > 0) {
      const keepIdx: number[] = [];
      for (let i = 0; i < headers.length; i++) if (!depColSet.has(i)) keepIdx.push(i);
      const reducedPos = (origCol: number): number => origCol < 0 ? -1 : keepIdx.indexOf(origCol);
      const inheritCols = keepIdx.filter(i => inheritRe.test(norm[i]));
      const newHeaders = keepIdx.map(i => headers[i]);
      const appendedRel = relCol < 0;
      if (appendedRel) newHeaders.push("Relationship");
      const relPos = appendedRel ? newHeaders.length - 1 : reducedPos(relCol);

      const newRows: string[][] = [];
      const newSrc: number[][] = [];
      let depCount = 0;
      const depIdxs = Array.from(depGroups.keys()).sort((a, b) => a - b);
      for (let ri = 0; ri < rows.length; ri++) {
        const r = rows[ri];
        const emp: string[] = keepIdx.map(i => r[i]);
        if (appendedRel) emp.push("Employee");
        else if (relPos >= 0 && cell(r, relCol) === "") emp[relPos] = "Employee";
        newRows.push(emp);
        newSrc.push(srcRows[ri]);

        const empName = (cell(r, firstCol) + " " + cell(r, lastCol)).trim();
        for (const n of depIdxs) {
          const g = depGroups.get(n) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number };
          const dssn = cell(r, g.ssn), ddob = cell(r, g.dob), dname = cell(r, g.name);
          const dlast = cell(r, g.last), dfirst = cell(r, g.first), dgender = cell(r, g.gender), drel = cell(r, g.rel);
          if (dssn === "" && ddob === "" && dname === "" && dlast === "" && dfirst === "") continue; // empty dependent slot
          depCount++;
          const drow: string[] = keepIdx.map(() => "");
          for (const ic of inheritCols) drow[keepIdx.indexOf(ic)] = r[ic]; // inherit address/location to tie family
          const setR = (origCol: number, val: string) => { const p = reducedPos(origCol); if (p >= 0 && val !== "") drow[p] = val; };
          if (dlast !== "" || dfirst !== "") { setR(lastCol, dlast); setR(firstCol, dfirst); }
          else if (dname !== "") {
            const np = splitCombinedName(dname); // shared §11c parser — comma "Last, First" is reliable
            if (np !== null) {
              if (np.guessed && np.first !== "") addIssue("INFO", 0, dname, "First Name", dname, "Dependent name had no comma; guessed 'First Last' split", "Verify first vs last name");
              setR(lastCol, np.last); setR(firstCol, np.first);
            }
          }
          setR(ssnCol, dssn); setR(dobCol, ddob); setR(genderCol, dgender);
          let relVal = normalizeDepRel(drel);
          if (relVal === "") {
            relVal = "Child";
            addIssue("INFO", 0, (dfirst + " " + dlast).trim() || empName, "Relationship", drel, "Dependent relationship not specified; defaulted to Child", "Set Spouse / Child / Domestic Partner if not a child");
          }
          if (relPos >= 0) { if (appendedRel) drow.push(relVal); else drow[relPos] = relVal; }
          else if (appendedRel) drow.push(relVal);
          newRows.push(drow);
          newSrc.push(srcRows[ri]); // a dependent comes from its employee's source row
        }
      }
      headers = newHeaders;
      rows = newRows;
      srcRows = newSrc;
      if (depCount > 0) addLog(`Dependent-column explode: created ${depCount} dependent row(s) from ${depGroups.size} dependent slot(s).`);
    }

    return { headers, rows, splits, pivots, srcRows: srcRows.map(a => a.join(", ")) };
  }
}

② Run it on a census

Open the workbook that contains the census you want to convert.
Click the sheet tab that holds the raw census data, so it's the active sheet.
Go to Automate → find LMG NCAM Census Engine in the script gallery → click it → click Run.
When it finishes you land on the LMG tab — that's your upload file (always sheet #1; NCAM quoting is #2). Review the VALIDATION ISSUES tab before uploading.

Safe to re-run as many times as you like: the first run saves your original data to a RAW DATA tab and never touches it again — later runs reprocess from that untouched copy.

③ Keep it up to date (about 20 seconds)

The script doesn't update itself — when a new version ships, you paste the new copy over the old one:

On this page, click Copy script (step ① above).
In Excel: Automate → click LMG NCAM Census Engine → Edit (opens the code panel).
Click inside the code, Ctrl+A, Ctrl+V, then Save script. Done.

Want to know when a new version is out? Sign up for update emails below — no need to keep checking this page.

Try it on a sample first

Three deliberately messy test censuses — run the script on one of these before pointing it at a real file, so you know what to expect.

Flat sample — triggers most cleanup rules (split address, spelled-out states, hourly pay, a retiree, a bad row, a flagged duplicate).
⬇ SAMPLE-census.xlsx

Stacked sample — one person spread across several product rows, with dependents stored in columns; the tool collapses each person to one clean row.
⬇ SAMPLE-stacked-census.xlsx

Product-coded sample — the hard case: dental and vision share one generic "Coverage Tier" column, told apart by a "Plan Type" column; the tool routes each tier to the right product.
⬇ SAMPLE-product-coded.xlsx

Found a problem? Want update emails?

🐛 Report a problem

Something mapped wrong, flagged wrong, or errored? Describe what happened — the census layout, what you expected, what you got. Don't paste real employee data (names/SSNs) — column headers are fine.

For power users

Ask Jev about unmapped headers

Paste the SOURCE HEADER values the HEADER MAP tab left unmapped or only suggested, one per line. Jev (a typed classifier, not a chatbot) answers with the LMG field it thinks each one is and how sure it is. Nothing is applied for you: tick the rows you agree with and send them to the mappings form below. ≥ 90% — auto 70–89% — suggest < 70% — ask

Contribute learned mappings

As you confirm columns on the HEADER MAP tab, copy that tab's SHARE column (each line is Source Header | LMG Field) and paste it below. Good mappings get folded into the built-in dictionary so future versions recognize them automatically — for everyone.

Docs & downloads

⬇ User Guide (.md) ⬇ Rulebook (.md) ⬇ AI Reference Blurb (.md) ⬇ Script (.ts) ⬇ Quote script (.ts)

Read the User Guide inline

# LMG / NCAM Census Translation Engine: User Guide (v3.1.0)

A one-click Excel tool that converts a carrier census into the LaunchMyGroup
(LMG) upload format and a derived NCAM quoting sheet, with full validation and
an audit trail. It runs **inside Excel** — no install, no Python, no website.

---

## 1. What it does

Point it at a messy census file and press **Run**. In a few seconds your
workbook is rebuilt into eight tabs:

| Tab | What it is |
|-----|------------|
| **LMG** | Your clean enrollment file, ready to upload. Always tab #1. |
| **NCAM** | Employees-only sheet in quoting layout. |
| **SUMMARY** | Counts by relationship and by plan tier (EE/ES/EC/FAM/W). |
| **VALIDATION ISSUES** | Every problem found, rated ERROR / WARNING / INFO, with the source row on RAW DATA and the row on the LMG tab. |
| **PROCESSING LOG** | A record of every transformation it performed. |
| **RAW DATA** | An untouched copy of your original census. Never modified. |
| **Other Columns** | Every column kept out of the LMG upload, with the reason — blank LMG fields, unrecognized source headers, and duplicate columns. The data still lives on RAW DATA. |
| **HEADER MAP** | Columns the tool could not match exactly, with a suggested field. Type the right LMG field and re-run; your choices are remembered (section 5b). |

The **LMG** and **NCAM** tabs are colored green; the rest are navy. On the
**LMG** tab, any cell with a validation issue is highlighted by severity
(red = error, amber = warning, blue = info), so problems jump out at a glance.

It standardizes headers, merges split address fields, converts state names to
2-letter codes, cleans phones to 10 digits, formats dates to MM/DD/YYYY,
normalizes gender to M/F, converts coverage to EE/ES/EC/FAM/W, abbreviates long
job titles, annualizes hourly wages, and more.

**It also reshapes the two "nightmare" layouts before cleaning:** if a person is
split across several rows (one per line of coverage — Dental, Vision, Life…), it
**collapses them into one row**; if dependents are stored sideways as columns
(`Dependent 1 Name`, `Dependent 2 …`), it **explodes them into their own
Spouse/Child rows**. It only reshapes when it's confident, and flags anything it's
unsure about. Try `SAMPLE-stacked-census.xlsx` to see it. A census where dental
and vision share one generic `Coverage Tier` column, told apart by a `Plan Type`
column, is routed product by product (`SAMPLE-product-coded.xlsx`).

**Family roll-up:** dependents belong to the employee row above them. Their
voluntary life and dependent basic life move up to the employee row, and the
employee's dental/vision tier is raised (never lowered) when a spouse or child
is enrolled in that product. Mismatches are flagged, not fixed.

---

## 1b. Rolling this out to your team

**The Automate tab (Office Scripts) is enabled on the Humana network** —
confirmed — so this is the right primary tool for the team. Everyone uses their
Humana M365 account in Excel Online or desktop Excel.

**Share one copy so everyone runs the same logic.** Two mechanisms, with an
important difference:

- **Share into a workbook** (script's **⋯** menu → **Share**): attaches the
script to *that workbook only* — anyone opening that workbook can run it, but
it doesn't follow them to other files. Fine for a shared template; not a
team-wide rollout.
- **Save the script to a shared SharePoint/Teams library** (script's **⋯**
menu → **Save As** → pick a SharePoint site your team can access): everyone
adds that location once in **Automate → All Scripts** and runs the *same
file* in any workbook. When the engine updates, you paste the new version
into that one script and **the whole team is updated — no re-pasting for
anyone else.** This is the recommended way to manage versions for a team.

If neither is available on your tenant, everyone maintains their own copy and
updates it from the hub page (see section 2c — it takes ~20 seconds).

**Fallback (off-network / non-Humana users):** the offline HTML cleaner
(`CensaScrub/lmg_census_cleaner_offline.html`) and the web Cleaner run in any
browser and keep data on the machine. **Their output does not match this script
yet:** they have not been brought up to `CENSUS-RULES.md` v2.1 (tier handling,
waivers, salary, family roll-up and more differ). Use this script when you can,
and review a Cleaner file carefully before uploading it.

## 2. One-time setup (≈2 minutes)

You need **Excel for the web** or **Excel on desktop with a Microsoft 365
work account** (the **Automate** tab must be visible on the ribbon).

1. Open the census workbook in Excel.
2. Click the **Automate** tab on the ribbon → **New Script**.
3. Delete the sample code in the editor.
4. Open `LMG-NCAM-Census-Engine.ts`, copy **all** of it, and paste it in.
5. Rename the script to `LMG NCAM Census Engine` and click **Save script**.

That script now lives in your account. For every future census you only do
section 3.

---

## 2b. Confirming you're on the latest version (for testing)

The script carries a version number. When it runs, that version is printed on the
**SUMMARY** tab (`Engine version`) and as the first line of the **PROCESSING LOG**
tab. The hub page (lmg-ncam-engine.netlify.app) shows the latest version at the
top. **If the version in your output doesn't match the hub, re-copy the script.**
This is how we stay lined up while testing.

## 2c. Updating to a new version (≈20 seconds)

The script doesn't update itself. When the hub shows a newer version:

1. On the hub page, click **Copy script**.
2. In Excel: **Automate** tab → click **LMG NCAM Census Engine** → **Edit**.
3. Click inside the code, **Ctrl+A** (select all), **Ctrl+V** (paste),
then **Save script**.

You can also sign up on the hub page to get an email when a new version ships.

## 3. Running it on a census (every time)

1. Open the census workbook.
2. Make sure the tab with the raw census data is the **active** tab
(just click it). The engine reads from the active tab, or from a tab named
`RAW DATA` if one already exists.
3. **Automate** tab → find `LMG NCAM Census Engine` → **Run**.
4. When it finishes, you'll land on the **LMG** tab.

**Always review the VALIDATION ISSUES tab before you upload.** See section 5.

It is safe to run more than once. The first run snapshots your original to
`RAW DATA`; every later run reprocesses from that untouched snapshot, so you
can fix the source and re-run without losing anything.

---

## 4. What gets cleaned (the rules)

- **Headers** — parenthetical text removed (`RELATIONSHIP (EE, SP, CH)` →
`RELATIONSHIP`); common variants mapped to LMG field names (`SSN`,
`Soc Sec No`, `Tax ID` → the SSN column).
- **Relationship** → `Employee`, `Spouse`, `Domestic Partner`, `Child`.
Unknown values (e.g. `Retiree`) are flagged, not guessed. If the census has
**no Relationship column at all**, every row is treated as an Employee (one
INFO note says so).
- **Names** — Last/First UPPERCASE, Middle Initial trimmed to one letter,
Relationship in Proper Case. Apostrophes and hyphens are kept (`O'BRIEN`,
`SMITH-JONES`); other punctuation becomes a space.
- **Gender** — `Male`→`M`, `Female`→`F`. Anything else is flagged.
- **Dates** (DOB, hire, prior-carrier) → `MM/DD/YYYY`. Handles slashes,
dashes, ISO, 2-digit years, and Excel date serials. A 2-digit year is read as
20YY unless that is after this year, then 19YY (`05/05/28` → 1928). Impossible
dates (`02/30/1980`) and unparseable dates are flagged. A date of birth in the
future is an ERROR; anyone over 100 and an employee under 14 are WARNINGs; a
hire date in the future, over 100 years ago, or before age 14 is a WARNING.
- **SSN** — digits only, must be 9. Employees missing an SSN are flagged.
- **Address** — `Address 1` + `Address 2` + `Address 3` merged into one
`Member Street Address`, UPPERCASE.
- **State** — full names → USPS (`Texas`→`TX`). Invalid states flagged.
- **Zip** — first 5 digits; restores a dropped leading zero.
- **Phone** — digits only, 10 digits; strips a leading country `1`. Bad
phones are flagged and left blank rather than uploaded wrong.
- **Salary** — `$50,000` → `50000`. `$25.50/hr` with 40 hours →
`25.50 × 40 × 52 = 53040`. A salary under $1,000 is flagged as a possible
hourly rate.
- **Hours** — blank employee defaults to `40`.
- **Occupation** — UPPERCASE, common abbreviations (`SENIOR`→`SR`,
`ADMINISTRATOR`→`ADMIN`, …), max 25 characters. A title that is still too
long is cut at the last whole word that fits and flagged as a WARNING that
shows the shortened title.
- **Disabled?** — blank defaults to `No`.
- **Coverage tier** — normalized to `EE / ES / EC / FAM / W`. A tier the tool
does not recognize (e.g. `EE+1`) is kept exactly as written and flagged as a
WARNING; fix it before uploading.
- On **LMG**, a waived dental/vision tier becomes
`Waive with other coverage`; a blank employee tier (when the group
carries that product) is also forced to `Waive with other coverage`.
That product's Plan column is left blank (the original stays on RAW DATA).
- On **NCAM**, those convert back to `W` (because NCAM defaults blanks to EE).
- **Basic Life, Dependent Basic Life, STD, LTD**: read `EE` or `Waive`.
`Waive`, `Declined`, `None`, `N`, `No`, `0` and `False` all mean `Waive`;
`Enroll`, `Yes`, `Y`, `EE` mean `EE`.
- **Primary Beneficiary** — if an employee is enrolled in Basic Life or has an
Employee Voluntary Life amount but no beneficiary, it's set to `ON FILE`.
Employees who waived life get no `ON FILE`.
- **Voluntary Life** — employee blank/waive amounts become `0`.

---

## 5. Reading the VALIDATION ISSUES tab

| Severity | Meaning | Do before upload? |
|----------|---------|-------------------|
| **ERROR** | Missing/invalid required data (no SSN, bad DOB, no gender). | **Yes — fix.** |
| **WARNING** | Likely wrong but not blocking (odd salary, unknown relationship, **possible duplicate**). | Review. |
| **INFO** | FYI only (e.g. spousal vol-life on a dependent row). | Optional. |

Each issue lists the **source row** (the row on RAW DATA; when several stacked
rows were collapsed into one person you see all of them, e.g. `2, 3, 4`), the
**LMG row**, member name, column, the offending value, the problem, and a
recommended action. The SUMMARY tab's Errors / Warnings / Info counts match this
tab exactly. Fix the issues on your source tab and re-run,
or fix them directly on the LMG tab before uploading.

## 5b. Mapping columns the tool didn't recognize (HEADER MAP tab)

When the tool isn't sure where a column belongs, it doesn't guess silently — it
puts it on the **HEADER MAP** tab. To include such a column:

1. Open the **HEADER MAP** tab. Each row is a column the tool couldn't match
exactly, with a **SUGGESTED** field, a **CONFIDENCE** %, and a blank
**MAP TO LMG FIELD** cell.
2. Click the **MAP TO LMG FIELD** cell and pick the LMG field it should become
from the dropdown (e.g. `Dental Cov Level`). Every available field is also
listed in column **H**, **LMG FIELDS (Map To options)**.
3. **Re-run** the script. The column is now mapped and included.

The tool reads your **MAP TO** entries first (they override everything), and they
**persist** — the next file from the same carrier maps cleanly without redoing it.
A very close match (≥85%) is applied automatically and marked "verify" so you can
check it; clear its **MAP TO** cell to undo.

### Duplicates are flagged, not deleted
If two rows share the same last name + first name + DOB + gender, the engine
flags them as a WARNING. It does **not** delete a row — you decide which to
remove. This is deliberate: an upload file should never silently drop an
enrollee.

---

## 6. Uploading to LaunchMyGroup

The **LMG** tab is your upload file and is always worksheet #1 (LMG uploads the
first sheet only). Either upload the workbook as-is, or right-click the **LMG**
tab → **Move or Copy** → *(new book)* to export just that sheet.

---

## 7. Known limits in v3.1.0 (by design)

- **Families are linked by row order.** A dependent belongs to the nearest
Employee row above it; there is no linking by subscriber SSN yet.
- **Duplicates are flagged, not merged.**
- **Relationship codes `S`, `C`, `D`** are not recognized; they are flagged and
the row is treated as a non-employee. Spell them out (`SP`, `CH`, `Spouse`).
- **Short or long zip codes** are padded or cut to 5 digits without a flag.
- **NCAM** uses the spec's minimum quoting columns plus Salary/Occupation/STD/
LTD when disability coverage is present.
- **The web Cleaner's output differs** from this script until it is rebuilt on
the same rules (see section 1b).

The fastest way to improve the tool is to run real census files through it and
report the edge cases on the hub page.

---

## 8. If something looks wrong

1. Check **PROCESSING LOG** — it lists what the engine did.
2. Check **RAW DATA** — confirm the original value.
3. Note the case, the column, the source value, and the wrong output, and bring
that example back to your AI assistant with the reference blurb
(`AI-REFERENCE-BLURB.md`). Real examples improve the tool far faster than
guessing at carrier formats up front.

Read the Rulebook (canonical spec) inline

# Census Translation Rules — Canonical Spec v2.4

**This document is the single source of truth for every LMG/NCAM census tool.**
The Office Script, the CensaScrub Python tools, and the offline HTML cleaner
should each implement *these* rules so they produce identical output and don't
drift apart. When a rule changes, change it **here first**, bump the version,
then update each implementation.

| Implementation | Repo / file | Runtime | Audience |
|----------------|-------------|---------|----------|
| LMG/NCAM Office Script | `lmg-ncam-engine/LMG-NCAM-Census-Engine.ts` | Excel Automate tab (M365) | Excel + LMG uploaders, zero-install |
| Python CLI/GUI | `CensaScrub/censascrub_v001.py`, `censascrub_gui.py` | Windows desktop | Local power users |
| Web / offline HTML cleaner | `CensaScrub/site/index.html` (lmgcensus.netlify.app, SSPWest login) + `CensaScrub/lmg_census_cleaner_offline.html` (same engine, no account) | Any browser | **Team upload site; offline file is the universal fallback** |
| ~~Web app~~ (archived 2026-06-29) | `_archive/census-data-refiner/` — dead, never deployed; not a sync target | — | — |

Column source of truth: `CensaScrub/LMG_Column_Dictionary.xlsx` (LMG platform
field names + alias variants).

---

## 1. Workbook / output structure

Seven outputs, in this order. **LMG must be worksheet #1** (LaunchMyGroup uploads
the first sheet only).

1. `LMG` — full enrollment upload (employees, spouses, domestic partners, children). Tab #1, colored `#5C9A1B`.
2. `NCAM` — employees-only quoting sheet derived from LMG. Tab #2, colored `#5C9A1B`.
3. `SUMMARY` — counts by relationship and by plan tier.
4. `VALIDATION ISSUES` — ERROR / WARNING / INFO with source row, LMG row, member, column, value, issue, action. The source row is the row on `RAW DATA` (when the pre-pass collapsed stacked rows, every source row is listed, e.g. `2, 3, 4`; an exploded dependent shows its employee's source row); the LMG row is the row on the `LMG` tab.
5. `PROCESSING LOG` — audit trail of every transformation.
6. `RAW DATA` — untouched copy of the original census. Never modified.
7. `Other Columns` — every column **not** in the LMG upload, each with a reason: entirely-blank LMG fields (removed because blank columns break the upload), unrecognized source headers, and duplicate/unused source columns. Two columns: COLUMN, WHY IT IS NOT IN THE LMG UPLOAD. Tab colored Plum `#AF0061`. All other secondary tabs are colored `#003057`.

**Blank-column removal:** after processing, any LMG output column with no data in
any row is dropped from the `LMG` sheet and its name listed on `Other Columns`.
**Exception — REQUIRED columns are never dropped** (Relationship, Member Last
Name, Member First Name, Gender, Date of Birth, State); if a required column is
entirely blank it is kept and flagged ERROR. NCAM and SUMMARY read from the full
(unfiltered) records, so they are unaffected.

**Validation highlighting:** each issue's offending cell is highlighted on the
`LMG` tab by severity — ERROR `#FFC7CE`, WARNING `#FFEB9C`, INFO `#BDD7EE`
(most-severe color wins per cell; duplicate flags highlight the whole row). The
SEVERITY column on `VALIDATION ISSUES` is shaded the same colors.

A tool may read its source from `RAW DATA` if present (idempotent re-runs),
else from the active/first data sheet.

## 2. LMG output columns (canonical order)

**One dictionary for every tool** (owner decision 2026-09-10 — the web cleaner's
names, from the LMG column dictionary). 41 columns, in this order:

MEMBER INFORMATION — Relationship, Member Last Name, First Name, Middle Initial,
Gender, Date of Birth, Member Street Address, City, State, Zip Code, Phone Number,
Social Security Number, Status Field, Date of Hire, Email Address.
(`Status Field` was `Employee Status` from v2.0 to v2.3; owner decision 2026-10-09.)

ADDITIONAL DETAILS — Hours Worked Per Week, Salary, Occupation, Disabled?,
Billing Division.

BENEFITS INFORMATION — Dental Cov Level, Dental Plan Elected, DHMO Provider Name,
Dental Prior Current Carrier Name, Dental Prior Carrier Effective Date,
Dental Prior Carrier Term Date, Dental Prior Carrier Ortho included,
Vision Cov Level, Vision Plan Election,
Basic Life Cov Level, Life/AD&D Class, Primary Life Beneficiary,
Dependent Basic Life,
Employee Voluntary Life Flat Amount, Spouse Voluntary Life Flat Amount,
Dependent Voluntary Life Flat Amount,
Short-Term Disability Cov Level, STD Class, Long-Term Disability Cov Level, LTD Class,
Working Location.

Notes: COV/COVERAGE → **Cov Level** (tier `EE` / `ES` / `EC` / `FAM` / waived; Dental/Vision waived prints `Waive with other coverage`, §5);
Plan/Select/Elect → Plan Elected / Plan Election. **Basic Life Cov Level,
Dependent Basic Life, Short-Term / Long-Term Disability Cov Level read `EE` /
`Waive`** (owner decision 2026-09-10 — was Enroll/Waive). Waiver words for these
fields: Waive / Waived / Decline / None / N/A / Opt Out **and `N`, `No`, `0`,
`False`** (v2.1); Enroll / Elect / Yes / Y / E / EE / Active → `EE`. SSN is kept on the
employee only. `Member Last Name` becomes `Last Name` on the NCAM tab. Voluntary
Life columns are omitted when blank/0/Waive for everyone (§6). Superseded names
(`Dental Coverage Type`, `STD`, `Employee Status`, `Dental Coverage Tier`, `STD
Election`, …) stay in every tool's alias table so old files still map.

## 3. Header normalization

- Remove parenthetical text: `RELATIONSHIP (EE, SP, CH)` → `RELATIONSHIP`.
- Unbalanced parens: if a stray `)` remains after stripping (e.g. `Status
  (Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date and Event
  Type)`), everything from the first `(` onward is instruction text — keep only
  the text before the first `(` (→ `STATUS`).
- Uppercase, strip punctuation to spaces, collapse whitespace.
- A bare `Coverage Tier` / `Effective Date` / `Term Date` header is **not** an
  alias (v2.1): a generic tier belongs to §11 Stage 0 product routing, and a plain
  effective/term date is usually the coverage date, not the prior carrier's.
  Without a product column they are only suggested on HEADER MAP.
- Map normalized header to the LMG field. Priority: **user-confirmed HEADER MAP
  entry > exact alias > high-confidence fuzzy match (≥85% word+character
  similarity, auto-applied with a verify flag) > suggestion (50–85%, listed but
  not applied) > unrecognized**. Exact matches never get flagged.
- **HEADER MAP tab:** every non-exact column is listed with a suggested field +
  confidence and an editable `Map To` cell. Fill `Map To` with an LMG field and
  re-run to include the column. Entries persist across runs (the tool learns).
- **No column leaves the LMG upload silently.** Any source column the tool does
  not recognize — or recognizes but can't use because an earlier column already
  filled that LMG field (e.g. both `Home Phone` and `Cell`) — is **flagged
  WARNING on VALIDATION ISSUES** and **listed on Other Columns**, while its raw
  values are always preserved on `RAW DATA`. The user decides; the tool never
  drops a column without surfacing it.

## 4. Field rules

| Field | Rule |
|-------|------|
| Relationship | → `Employee` / `Spouse` / `Domestic Partner` / `Child`. Unknown (e.g. `Retiree`) = flag, keep original. **No Relationship column at all** (common on quoting censuses) → every row is `Employee`, one INFO flag (v2.1). |
| Last / First name | UPPERCASE. Missing = ERROR. |
| Middle Initial | First character only, UPPERCASE. |
| Gender | `Male`→`M`, `Female`→`F`. Else flag (blank/unknown = ERROR for enrollees). |
| Date of Birth | → `MM/DD/YYYY`. Accept slashes/dashes/ISO/2-digit-year/Excel serial. Two-digit year → `20YY` unless that is after the current year, else `19YY`. The day is checked against the month (`02/30` is invalid). Unparseable or missing = ERROR. **Plausibility (v2.1):** future date = ERROR; over 100 = WARNING; an employee under 14 = WARNING. |
| Date of Hire | Employees only (blanked on dependents). → `MM/DD/YYYY`. Future hire date, hire over 100 years ago, or hired before age 14 = WARNING. |
| Prior carrier eff/term dates | → `MM/DD/YYYY`. |
| SSN | **Kept on the EMPLOYEE only** — removed from spouse / domestic partner / child. Digits only. If fewer than 9 digits, **pad leading zeros to 9** (INFO spot-check). More than 9 digits = ERROR. Employee missing SSN = ERROR. |
| Member Street Address | Merge Address 1/2/3 (and Addr/Street variants) with single spaces, UPPERCASE. |
| City | UPPERCASE. |
| State | Output header is **`State`** (not "Member State"). Full name → USPS 2-letter (`Texas`→`TX`). Invalid = flag; employee missing state = ERROR. **Required — never dropped.** |
| Zip Code | First 5 digits; restore dropped leading zero. |
| Phone Number | Digits only, 10 digits; strip leading country `1`. Invalid = flag + leave blank. **Multiple phone columns coalesce per row by priority: personal/home/generic → cell/mobile → work/office.** |
| Email Address | **Multiple email columns coalesce per row by priority: personal/home/generic → work/office.** |
| Salary | Strip `$` and commas. Hourly → `rate × Hours Worked Per Week × 52`, using the member's **actual** weekly hours (e.g. 20 → `rate × 20 × 52`), default 40 only when hours blank/invalid (INFO flag). **Hourly vs annual is decided by a Compensation Type / Salary Type column when present** (Hourly/Annual), else by detecting "/hr" in the salary cell. Salary < $1,000 = flag as possible hourly. Non-numeric = flag. |
| Hours Worked Per Week | Use the value as given. Blank/invalid employee → `40` (fallback only). |
| Occupation | UPPERCASE; apply abbreviation table; max 25 chars: if still longer, truncate at the last whole word that fits (hard cut only for a single word over 25) + WARNING showing the shortened title. (v2.1: the vowel-drop was removed; it made titles unreadable.) |
| Disabled? | Blank → `No`. Y/N variants → `Yes`/`No`. |
| Combined columns | A single `Name` / `City, State ZIP` / full-address column is split into the separate fields per §11c **before** the field rules above apply. |

### Abbreviation table (occupation)
SENIOR→SR, JUNIOR→JR, INFORMATION TECHNOLOGY→IT, SYSTEM(S)→SYS,
ADMINISTRATOR/ADMINISTRATION→ADMIN, SUPPORT→SUP, TECHNICIAN→TECH,
REPRESENTATIVE→REP, BUSINESS→BIZ, OPERATIONS→OPS, MANAGER→MGR, MANAGEMENT→MGMT,
ASSISTANT→ASST, DIRECTOR→DIR, ENGINEER→ENGR, SUPERVISOR→SUPV, DEVELOPER→DEV,
COORDINATOR→COORD, SPECIALIST→SPEC, GENERAL→GEN.

### Coverage vs Plan columns
- A column containing **COV / COVERAGE** (e.g. `Dental Coverage`, `Vision Cov
  Level`) is the **Cov Level** (coverage tier) — the election EE/ES/EC/FAM/Waive.
- A column containing **Plan / Select / Elect** (e.g. `Dental Plan`, `Vision
  Select`, `Dental Election`) is the **Plan Elected** — which plan to enroll on.
- A Dental/Vision **Plan** column that holds only **one distinct value** is left
  off the upload (the system auto-selects the single plan); it's noted on Other
  Columns.

## 5. Coverage normalization

Normalize any tier value to one of: `EE`, `ES`, `EC`, `FAM`, `Waive`.

- Employee only / Self → `EE`.
- Employee + Spouse **or** Domestic Partner → `ES`.
- Employee + Child(ren) → `EC`.
- Employee + Spouse/DP + Child(ren) → `FAM`.
- Waive / Waived / Decline / None / Opt Out → `Waive`.
- Unrecognized → flag, keep original. On LMG the original value is output as-is
  (e.g. `EE+1`), never turned into `Waive`, and the family roll-up does not
  overwrite it.

### LMG-specific
- Dental/Vision tier waived → output text `Waive with other coverage` (owner
  decision 2026-09-27, reverting 2026-09-10 for Dental/Vision only). Every other
  coverage column (Basic Life, Dependent Basic Life, STD, LTD) stays `Waive` (§2).
- Blank **employee** dental/vision tier, **when the group carries that product**,
  → `Waive with other coverage` (LMG would otherwise treat blank as EE).
- Dental/Vision waived → that product's **Plan** column (`Dental Plan Elected` /
  `Vision Plan Election`) is left **blank** on LMG; a non-blank source plan is
  flagged INFO and stays on RAW DATA.

### NCAM-specific (reverse)
- `Waive` (any value starting with WAIV) → `W`.
- Blank employee dental/vision → `W` (NCAM defaults blanks to EE).

## 6. Life rules

- Primary Beneficiary: if an employee is **actually enrolled in life** (Basic Life
  elected and not waived, and/or a real Employee Voluntary Life amount) and the
  beneficiary is blank → `ON FILE`. Employee rows only. A waiver (`Waive`, `No`,
  `N`, …) never gets `ON FILE`; spouse/child vol life alone does not trigger it (v2.1).
- Employee & Spousal Voluntary Life: blank or waive → `0`. Employee rows only.
- **Dependent Voluntary Life: blank, `0`, or waive → `Waive`** (not `0`).
- A Voluntary Life column (Employee / Spousal / Dependent) is **dropped from the
  LMG output entirely if it is blank or `0` for every row** — it only appears when
  there are real elections. (Dropped columns are listed on Other Columns.)
- Spousal Voluntary Life found on a dependent row → flag INFO (roll-up to
  employee is a future version; handle manually for now).

## 7. NCAM derivation

From LMG, employees only. `Member Last Name` → `Last Name`.
Columns: Last Name, Gender, Date of Birth, Dental Coverage Type,
Vision Coverage Type. **If STD or LTD coverage exists**, also include:
Salary, Occupation, STD, STD Class, LTD, LTD Class (values as on LMG: EE/Waive,
class number). Counts must reconcile to LMG. Implemented by the Office Script,
the web/offline cleaner (`CensaEngine.buildNcam`) and the Python tool.

## 7b. Quote mode (NCAM Quote Engine)

A second script, generated from the same source with `QUOTE_MODE = true`, for
quotes rather than LMG uploads. It runs the same pre-pass (§11, §11b, §11c),
normalization and validation, then writes a **QUOTE** tab (#1) instead of LMG,
NCAM and Other Columns:

- **One line per employee.** Spouse / domestic partner / child rows are purged;
  they appear only as the derived Dental / Vision tier (EE / ES / EC / FAM / W).
- Columns, in order, each left off when blank for every employee (Last Name and
  Gender always kept; Date of Birth kept when there is no Age either): Last Name,
  First Name, Gender, Date of Birth, Age, Dental Coverage Type, Vision Coverage
  Type, Salary, Occupation, Date of Hire, Short-Term Disability Cov Level, STD
  Class, Long-Term Disability Cov Level, LTD Class, City, State, Zip Code.
- Not on the quote: plan names, Basic Life, Dependent Basic Life, voluntary life,
  SSN, address, phone, email (NCAM assumes every listed employee for
  non-contributory lines).
- An **Age** column is read (alias `Age`, `Current Age`, …); with Age present a
  missing DOB is not an ERROR in quote mode.
- VALIDATION ISSUES lists only issues on the quote's columns, Relationship and
  whole-row (duplicate) flags; the number left out is written to PROCESSING LOG.
- Running either script removes the other's output tabs, so a workbook never
  mixes LMG and QUOTE results.

## 8. Duplicates

Key = Last Name + First Name + Date of Birth + Gender. Duplicates are
**flagged** as WARNING in VALIDATION ISSUES — **never silently merged or
deleted**. An upload file must never silently drop an enrollee. The user
reviews and removes. (Family-unit-aware merge is a planned future version.)

## 8b. Special characters & extra validation flags

- **Special characters** anywhere in the census are replaced with a space; `#` →
  ` UNIT `. Exempt: date fields, Email Address, numeric fields (SSN, Phone,
  Zip, Salary, Hours, Voluntary Life) and Dental/Vision Cov Level (so an
  unrecognized tier stays verbatim for review).
- **Names and plan/class values keep apostrophes and hyphens** (v2.1): Member Last
  Name, First Name, Middle Initial, Dental Plan Elected, Vision Plan Election,
  Life/AD&D Class, STD Class and LTD Class. `O'Brien` → `O'BRIEN` (not `O BRIEN`),
  `Smith-Jones` → `SMITH-JONES`, `PPO-1500` stays `PPO-1500`. Curly apostrophes
  become straight ones; other punctuation in these fields still becomes a space.
- **Age 65+**: any member (employee or dependent) 65 or older is flagged.
- **Implausible dates** (v2.1): see §4 Date of Birth / Date of Hire.
- **Voluntary life not in even thousands** (e.g. 62,500) is flagged.
- **Family consistency** (flag only, never auto-fixed): a dependent listed but not
  covered by any tier → flag; a tier that implies a dependent not listed (ES w/o
  spouse, EC w/o child, FAM missing either) → flag (row-listing censuses only).

## 9. Validation severities

- **ERROR** — missing/invalid required data (no SSN, bad/missing DOB, missing gender). Fix before upload.
- **WARNING** — likely wrong but not blocking (odd salary, unknown relationship/tier, possible duplicate, over-long occupation, bad phone).
- **INFO** — FYI (e.g. spousal vol-life on a dependent row).

## 10. Guiding principle for variable carrier formats

Carriers, payroll vendors, and TPAs each format census files differently; for the
most part their columns match the alias logic above, but it varies. The tool
**auto-handles the normal patterns and FLAGS anything out of the norm for the end
user to decide — it never guesses silently.** When a structural detection isn't
confident, it does not transform; it flags the rows and passes them through. This
extends the no-silent-loss rule from values to structure.

## 11. Pivot pre-pass (IMPLEMENTED)

Runs BEFORE normalization, reshaping stacked/sideways layouts into one clean
enrollee row per person, then hands off to the existing pipeline. Transforms only
when confident; flags anything out of the norm.

- **Stage 0 — product-coded routing.** When a row has a generic value column
  (Coverage Tier / Coverage Level / Coverage Type, Plan, Election, Class) plus a
  product column (Plan Type / Product = Dental / Vision / Basic Life / STD / LTD),
  the generic value is moved into the product-specific LMG field for that row's
  product *before* the collapse: TIER→Dental/Vision Coverage Tier, PLAN→Dental/
  Vision Plan Elected, ELECTION→Basic Life/STD/LTD Election, CLASS→Basic Life/STD/
  LTD Class. Unknown products are left alone (flagged). Output stays
  ancillary-focused — Medical/Medicare generic values are not routed/output.
- **Stage 1 — collapse stacked product rows.** Gated on a Product column
  (Product / Coverage / Coverage Type / Plan Type / Benefit / Line of Coverage).
  Groups rows by person (SSN + last + first + DOB), merges each person's non-blank
  cells into one row (so the Dental row's tier, the Vision row's tier, etc. land on
  one line). Conflicting non-blank values for the same person/column are flagged
  WARNING, not silently overwritten. The existing duplicate check is the post-pivot
  fail-safe.
- **Stage 2 — explode dependent columns.** Detects `Dependent N <field>` groups
  (Name/SSN/DOB/Gender/Relationship, split or combined name; index N to any depth).
  Each populated dependent becomes its own row under the employee, inheriting the
  employee's address/city/state/zip/location to tie the family. Missing dependent
  relationship → defaulted to Child + INFO flag. A comma-less combined name is split
  "First Last" with an INFO flag to verify. (The dependent combined-name split
  shares the same name parser as §11c — identical suffix and middle-initial rules.)
- **Vertical census (v2.4).** The product column is found by header, or, when the
  header is unknown, by its values (80%+ of filled cells name a product, 2+
  different products). A column whose values are tiers (Employee Only, Family,
  Waived...) is the shared tier column whatever its header (Election, Option...).
  Routing: tier → Dental / Vision Cov Level, and on Basic Life / STD / LTD rows →
  that coverage's Cov Level (a tier there means enrolled = `EE`; a waiver = `Waive`);
  class → Life/AD&D / STD / LTD Class; a shared amount column on Voluntary Life
  rows → Employee Voluntary Life (the §11b roll-up moves a spouse's/child's).
  Per-product cost/premium columns are not used. Values with no output column
  (voluntary life tier, basic life amount, medical, unknown products) are counted
  in one INFO per product and stay on RAW DATA.
- The product column and the shared columns are listed on HEADER MAP as **used by
  the vertical-census pivot** (and on Other Columns with the reason). They are never
  shown as unrecognized or asked for a mapping: they cover several coverages, so
  there is no one field to map them to.

## 11b. Family roll-up (IMPLEMENTED)

After the rows are built, families are grouped by **adjacency** — an Employee row
plus the Spouse/Domestic Partner/Child rows beneath it, until the next Employee.
Onto the employee row the engine rolls: spouse's voluntary life → Spousal
Voluntary Life; child's → Dependent Voluntary Life; dependent basic life →
Dependent Basic Life (those are blanked on the dependent rows). The employee's
Dental/Vision Coverage Type is **derived per product from who is enrolled in that
product** (a dependent who waived that product does NOT count) — spouse+child →
FAM, spouse → ES, child → EC. Upgrade-only: never lowers a stated tier.
Demographics stay on the dependent rows. Every roll-up is flagged INFO to verify.

**Only employee-line data is uploaded.** After the roll-up, spouse / domestic
partner / child rows have all election, employment, AND address/contact fields
blanked (coverage types, plans, life, disability, beneficiary, salary, occupation,
hire, location, street/city/state/zip, phone, email). Dependents keep only
Relationship, name, Middle Initial, gender, DOB, and Disabled?.

## 11c. Combined-column split — "data extrapolation" (IMPLEMENTED)

Some carriers consolidate several fields into ONE column. This split runs as the
**first stage of the pre-pass** (before §11 Stage 0 product routing, collapse,
and dependent explode, so person-keying sees the split First/Last), detects
combined columns by header + value shape, and extrapolates them into the
separate canonical fields. Per §10: transform only when confident; otherwise
flag and pass through untouched.

### Shapes

| # | Shape | Example values | Split into |
|---|-------|----------------|------------|
| 1 | Combined name | `Smith, John A` / `Jane Doe` | Member Last Name, First Name, Middle Initial |
| 2 | City/State/Zip | `Austin, TX 78701` / `Round Rock TX 78664-1234` | City, State, Zip Code |
| 3 | Full address | `123 Main St, Apt 4, Austin, TX 78701` | Member Street Address, City, State, Zip Code |

### Detection (header candidates + value-shape confidence)

Confidence = fraction of **non-blank** cells in the column that parse (full
scan). Thresholds: **60%** for a named combined header (`SPLIT_CONFIRM`),
**90%** for a value-shape-only trigger on a plainly-named column
(`SPLIT_STRONG`), **70%** for full address, which is always value-gated
(`SPLIT_ADDR`). Columns with fewer than 5 non-blank values require **100%**
parse for value-shape-only triggers. Between **30%** (`SPLIT_HINT`) and the
threshold → WARNING flag, **no transform** (the column passes through as
today). Below 30% → nothing.

- **Shape 1 — combined name.** Header candidates: `NAME`, `FULL NAME`,
  `EMPLOYEE NAME`, `EMP NAME`, `MEMBER NAME`, `ENROLLEE NAME`,
  `SUBSCRIBER NAME`, `PARTICIPANT NAME`, and other `* NAME` headers except
  FIRST/LAST/MIDDLE/CARRIER/PLAN/PROVIDER/BENEFICIARY/PCP/OFFICE/FACILITY
  names. **Guard:** if separate First AND Last Name columns exist, the combined
  column is left alone (INFO note) — the explicit columns win.
- **Shape 2 — City/State/Zip.** Header candidates: `CITY STATE ZIP`,
  `CITY ST ZIP`, `CSZ`, `CITY STATE`, `CITY AND STATE` (zip optional when the
  header lacks ZIP). Also triggers on a plain `CITY`/`TOWN` header when ≥90% of
  values are full `City, ST 12345` shapes (zip + valid state required).
- **Shape 3 — full address.** Candidate columns: `ADDRESS`, `FULL ADDRESS`,
  `HOME ADDRESS`, `MAILING ADDRESS` and Address-column variants. Header alone
  is never sufficient (an `Address` column is normally street-only): requires
  ≥70% of values to parse as street + city + valid state + zip with a
  comma-delimited city. **Guard:** skipped entirely when separate City AND
  State AND Zip columns exist; only the first qualifying column splits (other
  address columns keep merging into the street per §4).

### Parsing rules

- **Name:** a trailing suffix (JR/SR/II/III/IV/V, with or without periods) is
  detached and re-appended to the last name. Comma present → `Last, First
  [Middle]` (reliable). No comma → guessed: 2 tokens = `First Last`; 3 tokens
  with a 1-character middle = `First MI Last`; 3+ tokens otherwise = first word
  is First Name, the rest is Last Name (protects `Mary Van Buren`); 1 token =
  Last Name only + WARNING.
- **City/State/Zip and full address parse right-to-left:** trailing zip
  (`12345` or `12345-6789`), then a trailing valid USPS 2-letter code or full
  state name (longest match first — `DISTRICT OF COLUMBIA`), remainder = city
  (full address: last comma segment = city, the rest = street). Values are
  stored raw; the normal §4 field rules (UPPERCASE, USPS conversion, zip-5)
  apply downstream.

### No silent loss

- The consumed combined column is removed from mapping but its raw values stay
  on `RAW DATA` (Office Script) / the source file (siblings), and every
  executed split is logged and flagged INFO with a parsed-row count.
- In a confidently-split column, a row that fails to parse keeps its **whole
  original value** in the primary target field (City for shape 2, Member
  Street Address for shape 3, Member Last Name for a one-word name) + WARNING.
- Comma-less name rows get ONE column-level INFO with a count ("split as
  'First [MI] Last' — verify"), not a flag per row.
- Low-confidence columns (30%–threshold) are flagged WARNING and passed
  through unchanged for the user to decide.

## 12. Roadmap (not yet implemented)

- **v2+** — duplicate merge (currently flagged), per-product dependent enrollment
  for tier derivation, family linking by subscriber-SSN / sequence-number (today:
  adjacency), beneficiary refinements.
- **v3** — richer NCAM column selection + reconciliation guarantees.
- **v4** — expanded SUMMARY (per-plan, per-tier, enrolled/waived/eligible).

---

### Changelog
- **v2.4** (2026-10-09, Office Script v3.5.0): vertical census routing for
  Basic Life / STD / LTD tiers, value-based product and tier column detection,
  pivot columns labeled on HEADER MAP (§11); LMG field `Employee Status` renamed
  `Status Field` in every tool (§2).
- **v2.3** (2026-10-09, Office Script v3.4.0): §7b quote mode — NCAM Quote
  Engine script, QUOTE tab, one line per employee, `Age` alias.
- **v2.2** (2026-09-27, Office Script v3.3.0): Dental/Vision waived tier on LMG
  is `Waive with other coverage` again (other coverages stay `Waive`), and the
  waived product's Plan column is blank on LMG (§5). NCAM still reads `W`.
- **v2.1** (2026-09-19, Office Script v3.1.0): fixes from the 2026-09-19 audit:
  no Relationship column → every row Employee + INFO (§4); bare `Coverage Tier`,
  `Effective Date`, `Term Date` removed from the alias table and the duplicate
  `Basic Life Class` alias removed (it maps to Life/AD&D Class) (§3); unrecognized
  tier kept verbatim on LMG, never `Waive` (§5); `N` / `No` / `0` / `False` are
  waivers for the Enroll/Waive fields and `ON FILE` only for employees enrolled in
  life (§2, §6); names and plan/class values keep `'` and `-` (§8b); DOB/DOH
  plausibility, current-year two-digit pivot and calendar check (§4); occupation
  word-boundary truncation (§4); VALIDATION ISSUES shows the RAW DATA source row
  plus the LMG row, and the SUMMARY issue tally counts every issue (§1). The web
  Cleaner and Python tools have **not** been updated to v2.1 yet; their output can
  differ from the Office Script until they are.
- **v2.0** (2026-09-10) — **one column dictionary + one vocabulary for all tools**
  (owner decision): §2 is now the web cleaner's 41-column LMG list (was the 40-name
  Office-Script list; Python had a third set); elections read `EE`/`Waive` (was
  Enroll/Waive); waived tier text is `Waive` (was `Waive with other coverage`);
  Python tiers are codes (was `Employee + Spouse` …); Python/cleaner tab #1 is `LMG`
  (was CENSUS); `Life/AD&D Class` added to the Office Script. Office Script v3.0.0,
  cleaner Build 3. Old names remain as aliases everywhere. If LMG rejects an upload
  on a header or value, that is the first thing to revisit.
- **2026-09-10** (implementation note, spec unchanged) — §7 NCAM derivation is now
  implemented by the web cleaner (lmgcensus.netlify.app, `CensaScrub/site/index.html`
  + the offline HTML) and the Python CLI/GUI, not only the Office Script. Known
  drift still open: the cleaner and Python tools keep their own LMG column
  dictionaries and tier vocabularies (e.g. `Dental Cov Level` / `Dental Coverage Tier`
  vs the §2 canonical `Dental Coverage Type`; CENSUS tab vs LMG tab; `Waive` vs
  `Waive with other coverage`) — reconciling to §2 is an owner decision, not done silently.
- **v1.1** (2026-07-06) — added §11c combined-column split ("data
  extrapolation"): combined Name, City/State/Zip, and full-address columns are
  split into separate canonical fields; §4 combined-columns row; dependent
  name split now shares the §11c name parser.
- **v1.0** (2026-06-29) — initial canonical spec, extracted from the LMG/NCAM
  Office Script and the Copilot requirements transcript.

Read the AI Reference Blurb inline

# Future-AI Reference Blurb

Paste this into any AI chat (Claude, Copilot, ChatGPT) when you need help
adjusting the tool. It gives the assistant the full context in one shot.

---

We are maintaining the **LMG / NCAM Census Translation Engine** (v3.1.0), an Excel
**Office Script** (TypeScript) for Humana ancillary census processing. The rules
it implements are in `CENSUS-RULES.md` (v2.1), the canonical spec. It
converts a carrier census into two outputs:

1. **LMG** — LaunchMyGroup enrollment upload format.
2. **NCAM** — employees-only quoting format derived from LMG.

**Delivery:** a single Office Script pasted into Excel → Automate tab → Run. It
operates on the open workbook. No backend, no install.

**Output workbook tabs:** `LMG` (always worksheet #1 — LMG uploads the first
sheet only), `NCAM`, `SUMMARY`, `VALIDATION ISSUES`, `PROCESSING LOG`,
`RAW DATA` (untouched original), `Other Columns` (every column not in the upload,
with the reason), `HEADER MAP` (user-confirmed column mappings, remembered across
runs).

**Source of truth for columns:** the LMG platform field list and alias variants
live in `LMG_Column_Dictionary.xlsx` (CensaScrub project). Output columns follow
that dictionary's order and names.

**Key processing rules:**
- Headers: strip parenthetical text; map common variants to LMG field names.
- Relationship → Employee / Spouse / Domestic Partner / Child (unknown = flag).
  No Relationship column at all → every row is Employee + one INFO flag.
- Names: Last/First UPPERCASE; Middle Initial = 1 char; Relationship Proper Case.
  Names and plan/class values keep apostrophes and hyphens (O'BRIEN); other
  special characters become a space.
- Gender → M / F (else flag).
- Dates (DOB, hire, prior-carrier) → MM/DD/YYYY; handles slashes/dashes/ISO/
  2-digit years/Excel serials. 2-digit year → 20YY unless after the current year,
  else 19YY; day checked against month. DOB in the future = ERROR; over 100 or an
  employee under 14 = WARNING; hire date in the future / before age 14 = WARNING.
- SSN: 9 digits, digits only; employee missing SSN = error.
- Address 1/2/3 merged → MEMBER STREET ADDRESS (UPPERCASE).
- State names → USPS 2-letter; invalid = flag.
- Zip → first 5 digits; restore dropped leading zero.
- Phone → 10 digits (strip leading country 1); invalid = flag + blanked.
- Salary: strip $ and commas; `$X/hr` annualized as rate × hours × 52
  (hours default 40); salary < $1,000 flagged as possible hourly.
- Occupation: UPPERCASE, abbreviation table, max 25 chars (truncate at a word
  boundary + WARNING).
- Disabled? blank → No.
- Coverage tier → EE / ES / EC / FAM / W. Unrecognized (e.g. EE+1) → kept
  verbatim + WARNING, never turned into Waive.
  - LMG: waived dental/vision → `Waive with other coverage` and that product's Plan column blank; blank employee
    tier (if the group carries that product) → `Waive with other coverage`. Elections (Basic Life / Dep Basic Life / STD / LTD) read `EE` / `Waive`; N / No / 0 / False are waivers. Column names = CENSUS-RULES §2 shared dictionary.
  - NCAM: convert those back to `W` (NCAM defaults blanks to EE).
- Primary Beneficiary: employee enrolled in Basic Life or with a real Employee
  Voluntary Life amount, blank bene → `ON FILE` (never for a waiver).
- Voluntary Life: employee/spouse blank/waive → 0; dependent blank/0/waive → Waive.
  All-0 columns are dropped.

**Pre-pass (reshaping, runs before normalization):**
- Stage S splits combined Name, City/State/Zip and full-address columns.
- Stage 0 routes a generic Coverage Tier / Plan / Class column into the
  product-specific fields using a Plan Type / Product column.
- Stage 1 collapses stacked product rows (same person across many rows, one per
  coverage line) into one row — gated on a Product column; groups by SSN+name+DOB;
  merges non-blank cells; flags value conflicts.
- Stage 2 explodes `Dependent N <field>` columns into Spouse/Child rows under the
  employee, inheriting address/location; flags defaulted relationships and guessed
  name splits.
- Reshapes only when confident; flags anything out of the norm. Governing rule:
  auto-handle the normal patterns, flag the rest, never guess silently.

**Family roll-up (after the per-row pass):** dependents join the Employee row
above them; dependent vol life and dependent basic life move up to the employee;
dental/vision tiers are raised (never lowered) for enrolled dependents; family
mismatches are flagged, not fixed.

**Validation:** ERROR (missing/invalid required), WARNING (likely wrong),
INFO (FYI). Columns: SEVERITY, SOURCE ROW (RAW DATA), LMG ROW, MEMBER NAME,
COLUMN, VALUE, ISSUE, RECOMMENDED ACTION. The SUMMARY tally counts every issue.

**Deliberate deviation from earlier spec:** duplicates (same last+first+DOB+
gender) are **flagged, not silently merged** — an upload file must never drop an
enrollee silently. The user reviews and removes.

**NCAM columns:** Last Name, Gender, Date of Birth, Dental Coverage Type,
Vision Coverage Type; plus Salary, Occupation, Short-Term Disability Cov Level,
STD Class, Long-Term Disability Cov Level, LTD Class **only if** STD or LTD
coverage is present.

**Other tools:** the web Cleaner (lmgcensus) and the Python CensaScrub tool
claim the same spec but do **not** produce identical output yet; treat this
script as the reference until they are rebuilt on the same rules.

**Planned next:** family linking by subscriber SSN (today: row order), duplicate
merge (today: flagged), relationship codes S/C/D, a shared rules core with the
web Cleaner, golden-file tests.

**When I bring you a problem,** I'll paste a real example: the case, the column,
the source value, and the wrong output. Help me adjust the matching script
(`LMG-NCAM-Census-Engine.ts`) — give me the exact edited code block, not
pseudocode.

Duplicates are flagged, not deleted — an upload file should never silently drop an enrollee. Family roll-up (deps → employee line) and product-coded coverage routing are built in.