import base64
import getpass
import hashlib
import json
import os
import re
import smtplib
import ssl
import time
from datetime import datetime
from email.message import EmailMessage
from html import escape
from pathlib import Path
from urllib.parse import quote

import mysql.connector
import requests
from playwright.sync_api import Playwright, TimeoutError as PlaywrightTimeoutError, expect, sync_playwright


# ============================================================
# AVALON CONFIGURATION
# ============================================================

LOGIN_URL = "https://avalonbus.avatarfleet.com/ssr/login"
APP_URL = "https://avalonbus.avatarfleet.com/af30"

AVALON_USERNAME = os.environ["AVALON_USERNAME"]
AVALON_PASSWORD = os.environ["AVALON_PASSWORD"]


# ============================================================
# PAYCHEX / TESS EMPLOYEE API CONFIGURATION
# ============================================================

# API mode replaces the old Paychex Playwright/browser lookup.
PAYCHEX_ENABLED = os.environ.get("PAYCHEX_ENABLED", "true").lower() == "true"
PAYCHEX_API_URL_CO1 = os.environ.get(
    "PAYCHEX_API_URL_CO1",
    "https://tessapp.tess360.us/getEmployeesco1",
).strip()
PAYCHEX_API_URL_CO2 = os.environ.get(
    "PAYCHEX_API_URL_CO2",
    "https://tessapp.tess360.us/getEmployeesco2",
).strip()

# Tess is invoked exactly twice using dedicated company endpoints.
# No COMPANYID parameter is sent in the URL, query string, JSON body, or form body.
# Company 1 -> PAYCHEX_API_URL_CO1
# Company 2 -> PAYCHEX_API_URL_CO2
PAYCHEX_API_METHOD = os.environ.get("PAYCHEX_API_METHOD", "GET").strip().upper()
PAYCHEX_API_TIMEOUT_SECONDS = int(os.environ.get("PAYCHEX_API_TIMEOUT_SECONDS", "30"))
PAYCHEX_API_MAX_RETRIES = int(os.environ.get("PAYCHEX_API_MAX_RETRIES", "3"))
PAYCHEX_API_DEBUG = os.environ.get("PAYCHEX_API_DEBUG", "true").lower() == "true"
PAYCHEX_VALIDATE_COMPANY_SPLIT = os.environ.get(
    "PAYCHEX_VALIDATE_COMPANY_SPLIT", "true"
).lower() == "true"
# Tess endpoint authentication is handled by the endpoint itself. No explicit
# Bearer token, API key, username, or password is sent by this script.
PAYCHEX_API_VERIFY_TLS = os.environ.get("PAYCHEX_API_VERIFY_TLS", "true").lower() == "true"
PAYCHEX_PRINT_ARRAYS = os.environ.get("PAYCHEX_PRINT_ARRAYS", "false").lower() == "true"

# Paychex/Tess matching uses a multi-attribute identity score. Normal and reversed
# First/Last Name combinations are supported, while DOB, email, phone and DOJ add
# independent confidence. When names are imperfect, a candidate can still qualify
# only when multiple strong secondary identifiers agree. Exact-score ties remain
# ambiguous; the script never guesses between different Employee IDs.
PAYCHEX_MATCH_MIN_SCORE = int(os.environ.get("PAYCHEX_MATCH_MIN_SCORE", "70"))
PAYCHEX_MATCH_MIN_MARGIN = int(os.environ.get("PAYCHEX_MATCH_MIN_MARGIN", "1"))
PAYCHEX_MATCH_ALLOW_REVERSED_NAMES = os.environ.get(
    "PAYCHEX_MATCH_ALLOW_REVERSED_NAMES", "true"
).lower() == "true"
PAYCHEX_MATCH_ALLOW_STRONG_SECONDARY = os.environ.get(
    "PAYCHEX_MATCH_ALLOW_STRONG_SECONDARY", "true"
).lower() == "true"
PAYCHEX_ID_COLUMN = os.environ.get("PAYCHEX_ID_COLUMN", "PAYCHEX_EMPLOYEE_ID").strip()

# Reverse reconciliation source for this Avatar tenant:
# the New Hire Saved View is the authoritative active-driver roster. The forward
# process scans only the current-month portion of New Hire, but the reverse check
# reopens New Hire and scans EVERY row across EVERY page with no date boundary.
# TARGET_MONTH never limits the reverse-check population.
# This is alert-only: the script never clears or replaces an existing Paychex ID.
PAYCHEX_REVERSE_CHECK_SCOPE = "ALL_NEW_HIRE_ROWS_ALL_PAGES_NO_MONTH_FILTER"
PAYCHEX_REVERSE_SOURCE_VIEW_NAME = "New Hire"
PAYCHEX_REVERSE_CHECK_ENABLED = os.environ.get(
    "PAYCHEX_REVERSE_CHECK_ENABLED", "true"
).lower() == "true"
PAYCHEX_REVERSE_CHECK_FAIL_ON_AVALON_ERROR = os.environ.get(
    "PAYCHEX_REVERSE_CHECK_FAIL_ON_AVALON_ERROR", "false"
).lower() == "true"

# Current-month Paychex termination control. This is independent of TARGET_MONTH.
# By default the server's current local month is used. Set PAYCHEX_TERMINATION_MONTH
# to YYYY-MM only for deliberate testing/backfill of the termination review.
PAYCHEX_TERMINATION_CHECK_ENABLED = os.environ.get(
    "PAYCHEX_TERMINATION_CHECK_ENABLED", "true"
).lower() == "true"
PAYCHEX_TERMINATION_MONTH = os.environ.get(
    "PAYCHEX_TERMINATION_MONTH", ""
).strip()

# Legacy browser values remain only so the old Playwright functions can stay in
# the file as commented/disabled reference code. They are NOT used by run().
PAYCHEX_HOME_URL = os.environ.get("PAYCHEX_HOME_URL", "https://www.paychex.com/")
PAYCHEX_USERNAME = os.environ.get("PAYCHEX_USERNAME", "").strip()
PAYCHEX_PASSWORD = os.environ.get("PAYCHEX_PASSWORD", "")
PAYCHEX_MFA_CODE = os.environ.get("PAYCHEX_MFA_CODE", "").strip()
PAYCHEX_TRUST_DEVICE = os.environ.get("PAYCHEX_TRUST_DEVICE", "false").lower() == "true"
PAYCHEX_SEARCH_WAIT_MS = int(os.environ.get("PAYCHEX_SEARCH_WAIT_MS", "1500"))
PAYCHEX_HEADLESS = os.environ.get("PAYCHEX_HEADLESS", os.environ.get("HEADLESS", "false")).lower() == "true"
PAYCHEX_SLOW_MO = int(os.environ.get("PAYCHEX_SLOW_MO", os.environ.get("SLOW_MO", "500")))

if not re.fullmatch(r"[A-Za-z0-9_]+", PAYCHEX_ID_COLUMN):
    raise RuntimeError("Invalid PAYCHEX_ID_COLUMN name.")

if PAYCHEX_API_METHOD not in {"GET", "POST"}:
    raise RuntimeError("PAYCHEX_API_METHOD must be GET or POST.")


# ============================================================
# SAMSARA API CONFIGURATION
# ============================================================

# Samsara integration temporarily disabled. Keep the original env-driven line below for later re-enable.
# SAMSARA_ENABLED = os.environ.get("SAMSARA_ENABLED", "true").lower() == "true"
SAMSARA_ENABLED = False
SAMSARA_API_TOKEN = os.environ.get("SAMSARA_API_TOKEN", "").strip()
SAMSARA_DRIVERS_URL = os.environ.get(
    "SAMSARA_DRIVERS_URL",
    "https://api.samsara.com/fleet/drivers",
).rstrip("/")
SAMSARA_REQUEST_TIMEOUT_SECONDS = int(
    os.environ.get("SAMSARA_REQUEST_TIMEOUT_SECONDS", "30")
)
SAMSARA_MAX_RETRIES = int(os.environ.get("SAMSARA_MAX_RETRIES", "4"))
SAMSARA_MATCH_MIN_SCORE = int(os.environ.get("SAMSARA_MATCH_MIN_SCORE", "70"))
SAMSARA_MATCH_MIN_MARGIN = int(os.environ.get("SAMSARA_MATCH_MIN_MARGIN", "10"))
SAMSARA_INCLUDE_DEACTIVATED = (
    os.environ.get("SAMSARA_INCLUDE_DEACTIVATED", "false").lower() == "true"
)
# Live Samsara sync is the default for this build because the workflow is expected
# to write Paychex/company external IDs after a safe driver match. Set
# SAMSARA_DRY_RUN=true explicitly if you want a no-write test run.
SAMSARA_DRY_RUN = os.environ.get("SAMSARA_DRY_RUN", "false").lower() == "true"
SAMSARA_VERIFY_PATCH = os.environ.get("SAMSARA_VERIFY_PATCH", "true").lower() == "true"
SAMSARA_PATCH_DEBUG = os.environ.get("SAMSARA_PATCH_DEBUG", "true").lower() == "true"
SAMSARA_VERIFY_RETRIES = int(os.environ.get("SAMSARA_VERIFY_RETRIES", "3"))
SAMSARA_VERIFY_WAIT_MS = int(os.environ.get("SAMSARA_VERIFY_WAIT_MS", "1500"))

SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY = os.environ.get(
    "SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY",
    "avatarEmployeeId",
).strip()
SAMSARA_COMPANY_EXTERNAL_ID_KEY = os.environ.get(
    "SAMSARA_COMPANY_EXTERNAL_ID_KEY",
    "avatarCompanyId",
).strip()

# Samsara external-ID values cannot contain spaces and a value for a given key
# cannot be reused on multiple objects. Therefore the human company label
# "Avalon Motor Coaches" is represented by a valid prefix and made unique per
# driver: AvalonMotorCoaches-<employeeId>.
SAMSARA_COMPANY_ID_PREFIX = os.environ.get(
    "SAMSARA_COMPANY_ID_PREFIX",
    "AvalonMotorCoaches",
).strip()

for _samsara_key_name, _samsara_key_value in (
    ("SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY", SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY),
    ("SAMSARA_COMPANY_EXTERNAL_ID_KEY", SAMSARA_COMPANY_EXTERNAL_ID_KEY),
):
    if not re.fullmatch(r"[A-Za-z0-9]{1,32}", _samsara_key_value):
        raise RuntimeError(
            f"{_samsara_key_name} must be alphanumeric and at most 32 characters."
        )


# ============================================================
# COMPLETION EMAIL CONFIGURATION - SMTP OR MICROSOFT GRAPH
# ============================================================

# End-of-run email notification is enabled by default.
# Server deployment should use EMAIL_TRANSPORT=smtp. Microsoft Graph remains
# available for the Mac/local deployment and can be selected with "graph".
EMAIL_NOTIFICATIONS_ENABLED = os.environ.get(
    "EMAIL_NOTIFICATIONS_ENABLED", "true"
).lower() == "true"
EMAIL_TRANSPORT = os.environ.get("EMAIL_TRANSPORT", "smtp").strip().lower()
if EMAIL_TRANSPORT not in {"smtp", "graph"}:
    raise RuntimeError("EMAIL_TRANSPORT must be either smtp or graph.")

#EMAIL_TO = os.environ.get(
#    "EMAIL_TO",
#    "mmantravadi@avalontrans.com",
#).strip()
EMAIL_TO = "mmantravadi@avalontrans.com; vicki@avalontrans.com; ssisson@avalonbus.com; hshaikh@avalontrans.com; shuckleby@avalontrans.com"
EMAIL_FROM = os.environ.get("EMAIL_FROM", "").strip()
EMAIL_SUMMARY_SUBJECT = os.environ.get(
    "EMAIL_SUMMARY_SUBJECT",
    "Avatar Driver Hub / Paychex Processing Summary",
).strip()

# SMTP settings. Defaults are intended for a local AlmaLinux Postfix/Exim relay.
# In Podman, use --network host so 127.0.0.1 reaches the host SMTP daemon.
EMAIL_SMTP_HOST = os.environ.get("EMAIL_SMTP_HOST", "127.0.0.1").strip()
EMAIL_SMTP_PORT = int(os.environ.get("EMAIL_SMTP_PORT", "25"))
EMAIL_SMTP_SECURITY = os.environ.get("EMAIL_SMTP_SECURITY", "none").strip().lower()
EMAIL_SMTP_USERNAME = os.environ.get("EMAIL_SMTP_USERNAME", "").strip()
EMAIL_SMTP_PASSWORD = os.environ.get("EMAIL_SMTP_PASSWORD", "")
EMAIL_SMTP_TIMEOUT_SECONDS = int(os.environ.get("EMAIL_SMTP_TIMEOUT_SECONDS", "30"))
EMAIL_SMTP_PREFLIGHT = os.environ.get("EMAIL_SMTP_PREFLIGHT", "true").lower() == "true"

# Microsoft Entra / Graph application settings retained for optional Mac use.
M365_TENANT_ID = os.environ.get("M365_TENANT_ID", "").strip()
M365_CLIENT_ID = os.environ.get("M365_CLIENT_ID", "").strip()
M365_CLIENT_SECRET = os.environ.get("M365_CLIENT_SECRET", "")
M365_GRAPH_SCOPE = os.environ.get(
    "M365_GRAPH_SCOPE",
    "https://graph.microsoft.com/.default",
).strip()
M365_GRAPH_BASE_URL = os.environ.get(
    "M365_GRAPH_BASE_URL",
    "https://graph.microsoft.com/v1.0",
).rstrip("/")
M365_GRAPH_TIMEOUT_SECONDS = int(
    os.environ.get("M365_GRAPH_TIMEOUT_SECONDS", "30")
)
EMAIL_SAVE_TO_SENT_ITEMS = os.environ.get(
    "EMAIL_SAVE_TO_SENT_ITEMS", "true"
).lower() == "true"

_GRAPH_ACCESS_TOKEN_CACHE = {
    "access_token": "",
    "expires_at": 0.0,
}

# ============================================================
# DATABASE CONFIGURATION
# ============================================================

DB_HOST = os.environ["DB_HOST"]
DB_PORT = int(os.environ.get("DB_PORT", "62142"))
DB_USER = os.environ["DB_USER"]
DB_PASSWORD = os.environ["DB_PASSWORD"]
DB_NAME = os.environ["DB_NAME"]
DB_TABLE = os.environ.get("DB_TABLE", "ADMIN_DRIVER_INFO")

if not re.fullmatch(r"[A-Za-z0-9_]+", DB_TABLE):
    raise RuntimeError("Invalid DB_TABLE name.")

ORG_ID = int(os.environ.get("ORG_ID", "316"))
LOCATION_ID = int(os.environ.get("LOCATION_ID", "245"))


# ============================================================
# AVALON PAGE / WAIT CONFIGURATION
# ============================================================

AVALON_PAGE_SIZE = int(os.environ.get("AVALON_PAGE_SIZE", "100"))
PASS1_SCROLL_ROWS = int(os.environ.get("PASS1_SCROLL_ROWS", "3"))
PASS1_RECOVERY_SCROLL_ROWS = int(os.environ.get("PASS1_RECOVERY_SCROLL_ROWS", "1"))
ROW_OPEN_WAIT_MS = int(os.environ.get("ROW_OPEN_WAIT_MS", "3000"))
# Increased from 1000ms because Avalon occasionally renders dialog values late.
FIELD_WAIT_MS = int(os.environ.get("FIELD_WAIT_MS", "1500"))
# Lower license fields are retried from fresh DOM snapshots after scrolling.
LICENSE_TAIL_WAIT_MS = int(os.environ.get("LICENSE_TAIL_WAIT_MS", "2000"))
LICENSE_TAIL_RETRIES = int(os.environ.get("LICENSE_TAIL_RETRIES", "4"))
# TEST MODE: attachment downloads intentionally disabled while Samsara matching is validated.
# To restore later, replace the next line with:
# DOWNLOAD_ATTACHMENTS = os.environ.get("DOWNLOAD_ATTACHMENTS", "true").lower() == "true"
DOWNLOAD_ATTACHMENTS = False

# Optional YYYY-MM override for testing/backfills. If blank, the machine's
# current local month is used. Example: TARGET_MONTH=2026-08
TARGET_MONTH = os.environ.get("TARGET_MONTH", "").strip()
PRINT_DRIVER_NAMES = (
    os.environ.get("PRINT_DRIVER_NAMES", "false").lower() == "true"
)
PRINT_DRIVER_LIST = (
    os.environ.get(
        "PRINT_DRIVER_LIST",
        os.environ.get("PRINT_DRIVER_NAMES", "false"),
    ).lower() == "true"
)

HEADLESS = os.environ.get("HEADLESS", "false").lower() == "true"
SLOW_MO = int(os.environ.get("SLOW_MO", "0" if HEADLESS else "500"))
# Playwright's chromium channel uses the newer headless implementation, which is
# much closer to headed Chromium than the legacy headless shell.
HEADLESS_BROWSER_CHANNEL = os.environ.get(
    "HEADLESS_BROWSER_CHANNEL",
    "chromium" if HEADLESS else "",
).strip()
AVALON_NAVIGATION_TIMEOUT_MS = int(
    os.environ.get("AVALON_NAVIGATION_TIMEOUT_MS", "60000")
)
AVALON_LOGIN_SETTLE_TIMEOUT_MS = int(
    os.environ.get("AVALON_LOGIN_SETTLE_TIMEOUT_MS", "45000")
)
AVALON_NAVIGATION_RETRIES = int(
    os.environ.get("AVALON_NAVIGATION_RETRIES", "3")
)


# ============================================================
# OUTPUT DIRECTORIES
# ============================================================

EXPORT_ROOT = Path(os.environ.get("EXPORT_DIR", "./exports")).resolve()
RUN_ID = datetime.now().strftime("%Y%m%d_%H%M%S")
RUN_ROOT = EXPORT_ROOT / RUN_ID
ATTACHMENT_ROOT = RUN_ROOT / "attachments"

RUN_ROOT.mkdir(parents=True, exist_ok=True)
ATTACHMENT_ROOT.mkdir(parents=True, exist_ok=True)


# ============================================================
# GENERAL HELPERS
# ============================================================


def clean_text(value):
    if value is None:
        return ""
    return " ".join(str(value).replace("\xa0", " ").split())


def safe_filename(value):
    value = clean_text(value)
    value = re.sub(r"[^A-Za-z0-9._-]+", "_", value)
    value = value.strip("_")
    return value or "unknown"


def truncate(value, length):
    value = clean_text(value)
    if not value:
        return None
    return value[:length]


def normalize_email(value):
    value = clean_text(value).lower()
    if not value:
        return None
    return value[:50]


def normalize_phone(value):
    value = clean_text(value)
    if not value:
        return None
    digits = re.sub(r"\D", "", value)
    return digits[:15] if digits else None


def first_visible(locator):
    for index in range(locator.count()):
        item = locator.nth(index)
        try:
            if item.is_visible():
                return item
        except Exception:
            pass
    return None


def get_dialog_lines(dialog):
    return [
        clean_text(line)
        for line in dialog.inner_text().splitlines()
        if clean_text(line)
    ]


def wait_after_field(page):
    page.wait_for_timeout(FIELD_WAIT_MS)


def print_capture(page, record, field_name, value):
    value = clean_text(value)
    record[field_name] = value
    print(f"        {field_name}: {value}")
    wait_after_field(page)
    return value


def scroll_driver_dialog_to_bottom(page, dialog):
    """
    Scroll every scrollable container inside the visible driver dialog to its
    bottom. Avalon/Webix can render the lower license fields lazily, so an
    inner_text snapshot taken immediately after opening/revealing DOB may not
    yet contain State / Issue Date / Expiry Date.
    """
    try:
        moved = dialog.evaluate(
            r"""
            (root) => {
                const nodes = [root, ...root.querySelectorAll('*')];
                let moved = false;
                for (const node of nodes) {
                    if (!(node instanceof HTMLElement)) continue;
                    const style = window.getComputedStyle(node);
                    const overflowY = style.overflowY;
                    const scrollable =
                        node.scrollHeight > node.clientHeight + 4 &&
                        ['auto', 'scroll', 'overlay'].includes(overflowY);
                    if (scrollable) {
                        const before = node.scrollTop;
                        node.scrollTop = node.scrollHeight;
                        node.dispatchEvent(new Event('scroll', {bubbles: true}));
                        if (node.scrollTop !== before) moved = true;
                    }
                }
                return moved;
            }
            """
        )
        if moved:
            print("        Scrolled driver dialog to license-detail section")
    except Exception as error:
        print(f"        Dialog bottom-scroll warning: {error}")

    page.wait_for_timeout(LICENSE_TAIL_WAIT_MS)


def parse_license_tail_from_cursor(lines, start_index):
    """
    Original sequential Avalon parser.

    This is intentionally the FIRST source used for Country / State / Issue
    Date / Expiry Date because this exact sequence worked reliably before the
    later lazy-render retry logic was added. The caller passes the cursor that
    already points immediately after the Motorcycle Yes/No field.
    """
    cleaned = [clean_text(value) for value in lines if clean_text(value)]
    if not cleaned:
        return "", "", "", ""

    start = max(int(start_index or 0), 0)
    if start >= len(cleaned):
        start = 0

    country = ""
    country_index = start

    # Prefer the exact country value used by Avalon for US drivers.
    for index in range(start, len(cleaned)):
        if cleaned[index].upper() == "US":
            country = cleaned[index]
            country_index = index
            break

    # Generic two-letter country fallback.
    if not country:
        for index in range(start, len(cleaned)):
            candidate = cleaned[index]
            if re.fullmatch(r"[A-Z]{2}", candidate):
                country = candidate
                country_index = index
                break

    state = ""
    state_index = country_index
    if country:
        for index in range(country_index + 1, len(cleaned)):
            candidate = cleaned[index]
            if re.fullmatch(r"[A-Z]{2}", candidate) and candidate.upper() != country.upper():
                state = candidate
                state_index = index
                break

    date_start = state_index + 1 if state else country_index + 1
    dates = []
    for index in range(max(date_start, 0), len(cleaned)):
        token = extract_date_token(cleaned[index])
        if token:
            dates.append(token)
            if len(dates) >= 2:
                break

    issue_date = dates[0] if len(dates) >= 1 else ""
    expiry_date = dates[1] if len(dates) >= 2 else ""
    return country, state, issue_date, expiry_date


def parse_license_tail_values(lines):
    """
    Parse a fresh whole-dialog snapshot as a fallback.

    This is less precise than parse_license_tail_from_cursor because a fresh
    lazy-rendered snapshot may not preserve the same sequential layout. It is
    retained for recovery after the direct sequential read fails.
    """
    cleaned = [clean_text(value) for value in lines if clean_text(value)]
    if not cleaned:
        return "", "", "", ""

    yes_no_indexes = [
        index
        for index, value in enumerate(cleaned)
        if value.lower() in {"yes", "no"}
    ]
    start = yes_no_indexes[-1] + 1 if yes_no_indexes else 0
    return parse_license_tail_from_cursor(cleaned, start)


def extract_license_tail_from_controls(dialog):
    """
    Read Country / State / Issue Date / Expiry Date from labeled controls.

    Avalon sometimes renders lower license fields as input/combobox values.
    Those values may be missing from dialog.inner_text(), so this routine
    searches labels, aria attributes and nearby controls directly in the DOM.
    """
    try:
        raw = dialog.evaluate(
            r"""
            (root) => {
                const specs = {
                    country: ['country'],
                    state: ['state'],
                    issue_date: ['issue date', 'issued date', 'date issued'],
                    expiry_date: ['expiry date', 'expiration date', 'expires', 'date expires']
                };

                const norm = (value) => (value || '')
                    .replace(/\u00a0/g, ' ')
                    .replace(/\s+/g, ' ')
                    .trim()
                    .toLowerCase();

                const clean = (value) => (value || '')
                    .replace(/\u00a0/g, ' ')
                    .replace(/\s+/g, ' ')
                    .trim();

                const all = [root, ...root.querySelectorAll('*')];

                function controlValue(el) {
                    if (!el) return '';
                    const tag = (el.tagName || '').toLowerCase();
                    if (tag === 'input' || tag === 'textarea') {
                        return clean(el.value || el.getAttribute('value') || '');
                    }
                    if (tag === 'select') {
                        const selected = el.options && el.selectedIndex >= 0
                            ? el.options[el.selectedIndex]
                            : null;
                        return clean((selected && (selected.text || selected.value)) || el.value || '');
                    }
                    for (const attr of ['data-value', 'aria-valuetext', 'value']) {
                        const value = clean(el.getAttribute && el.getAttribute(attr));
                        if (value) return value;
                    }
                    return clean(el.innerText || el.textContent || '');
                }

                function isControl(el) {
                    if (!el || !el.matches) return false;
                    return el.matches(
                        'input, textarea, select, [role="combobox"], [role="textbox"], ' +
                        '.webix_inp_static, .webix_el_box input, .webix_el_box select'
                    );
                }

                function valueNearLabel(labelEl, aliases) {
                    // <label for="..."> association.
                    const htmlFor = labelEl.getAttribute && labelEl.getAttribute('for');
                    if (htmlFor) {
                        const escaped = (window.CSS && CSS.escape) ? CSS.escape(htmlFor) : htmlFor;
                        const target = root.querySelector('#' + escaped);
                        const value = controlValue(target);
                        if (value && !aliases.includes(norm(value))) return value;
                    }

                    // Immediate siblings are common in simple forms.
                    for (const sibling of [labelEl.nextElementSibling, labelEl.previousElementSibling]) {
                        if (!sibling) continue;
                        if (isControl(sibling)) {
                            const value = controlValue(sibling);
                            if (value && !aliases.includes(norm(value))) return value;
                        }
                        const control = sibling.querySelector && sibling.querySelector(
                            'input, textarea, select, [role="combobox"], [role="textbox"], .webix_inp_static'
                        );
                        const value = controlValue(control);
                        if (value && !aliases.includes(norm(value))) return value;
                    }

                    // Walk up a few levels and inspect controls / value-like descendants.
                    let container = labelEl;
                    for (let depth = 0; depth < 5 && container; depth++, container = container.parentElement) {
                        const controls = container.querySelectorAll
                            ? container.querySelectorAll(
                                'input, textarea, select, [role="combobox"], [role="textbox"], .webix_inp_static'
                              )
                            : [];
                        for (const control of controls) {
                            const value = controlValue(control);
                            if (value && !aliases.includes(norm(value))) return value;
                        }

                        // Webix often keeps the selected value as plain text beside the label.
                        const texts = container.querySelectorAll ? container.querySelectorAll('div, span') : [];
                        for (const node of texts) {
                            if (node === labelEl) continue;
                            const value = clean(node.innerText || node.textContent || '');
                            const normalized = norm(value);
                            if (!value || aliases.includes(normalized)) continue;
                            if (value.length <= 80) return value;
                        }
                    }
                    return '';
                }

                function findField(aliases) {
                    // First: controls whose own metadata names the field.
                    for (const el of all) {
                        if (!isControl(el)) continue;
                        const metadata = [
                            el.getAttribute && el.getAttribute('aria-label'),
                            el.getAttribute && el.getAttribute('placeholder'),
                            el.getAttribute && el.getAttribute('name'),
                            el.getAttribute && el.getAttribute('title'),
                            el.id
                        ].map(norm).filter(Boolean);
                        if (metadata.some(meta => aliases.some(alias => meta === alias || meta.includes(alias)))) {
                            const value = controlValue(el);
                            if (value) return value;
                        }
                    }

                    // Second: exact/near-exact visible label text.
                    for (const el of all) {
                        const text = norm(el.innerText || el.textContent || '');
                        if (!text) continue;
                        if (aliases.some(alias => text === alias || text === alias + ':')) {
                            const value = valueNearLabel(el, aliases);
                            if (value) return value;
                        }
                    }
                    return '';
                }

                const result = {};
                for (const [key, aliases] of Object.entries(specs)) {
                    result[key] = findField(aliases);
                }
                return result;
            }
            """
        ) or {}
    except Exception as error:
        print(f"        License control-read warning: {error}")
        return "", "", "", ""

    country = clean_text(raw.get("country"))
    state = clean_text(raw.get("state"))
    issue_date = extract_date_token(raw.get("issue_date")) or clean_text(raw.get("issue_date"))
    expiry_date = extract_date_token(raw.get("expiry_date")) or clean_text(raw.get("expiry_date"))

    # Reject obvious label echoes from broad DOM association.
    if country.lower() in {"country", "country:"}:
        country = ""
    if state.lower() in {"state", "state:"}:
        state = ""
    if issue_date.lower() in {"issue date", "issued date", "date issued"}:
        issue_date = ""
    if expiry_date.lower() in {"expiry date", "expiration date", "expires", "date expires"}:
        expiry_date = ""

    return country, state, issue_date, expiry_date


def _merge_license_tail_values(*value_sets):
    """Prefer the first nonblank value for each license-tail field."""
    merged = []
    for position in range(4):
        value = ""
        for values in value_sets:
            if values and position < len(values) and clean_text(values[position]):
                value = clean_text(values[position])
                break
        merged.append(value)
    return tuple(merged)


def capture_license_tail_with_retries(page, dialog, initial_values=None):
    """
    Recovery path for the lower license fields.

    Each retry combines:
      1) direct labeled-control values (input/select/combobox), and
      2) a fresh whole-dialog text snapshot.

    initial_values normally comes from the original proven sequential parser.
    """
    best = tuple(initial_values or ("", "", "", ""))
    best_score = sum(bool(clean_text(value)) for value in best)

    for attempt in range(1, max(LICENSE_TAIL_RETRIES, 1) + 1):
        scroll_driver_dialog_to_bottom(page, dialog)

        control_values = extract_license_tail_from_controls(dialog)
        fresh_lines = get_dialog_lines(dialog)
        text_values = parse_license_tail_values(fresh_lines)
        values = _merge_license_tail_values(best, control_values, text_values)
        country, state, issue_date, expiry_date = values
        score = sum(bool(value) for value in values)

        if score > best_score:
            best = values
            best_score = score

        print(
            f"        License-tail read {attempt}/{max(LICENSE_TAIL_RETRIES, 1)}: "
            f"Country={country or '<blank>'}, "
            f"State={state or '<blank>'}, "
            f"Issue Date={issue_date or '<blank>'}, "
            f"Expiry Date={expiry_date or '<blank>'}"
        )
        if any(control_values):
            print(
                "        Direct control values: "
                f"Country={control_values[0] or '<blank>'}, "
                f"State={control_values[1] or '<blank>'}, "
                f"Issue Date={control_values[2] or '<blank>'}, "
                f"Expiry Date={control_values[3] or '<blank>'}"
            )

        if country and state and issue_date and expiry_date:
            return values

        if attempt < max(LICENSE_TAIL_RETRIES, 1):
            print("        Lower license fields incomplete; waiting and retrying...")
            page.wait_for_timeout(LICENSE_TAIL_WAIT_MS)

    return best


# ============================================================
# SEQUENTIAL TEXT PARSING HELPERS
# ============================================================


def find_next_matching_line(lines, start_index, pattern, flags=re.IGNORECASE):
    regex = re.compile(pattern, flags)
    for index in range(max(start_index, 0), len(lines)):
        line = clean_text(lines[index])
        if regex.fullmatch(line):
            return line, index
    return "", start_index


def find_next_matching_line_search(lines, start_index, pattern, flags=re.IGNORECASE):
    regex = re.compile(pattern, flags)
    for index in range(max(start_index, 0), len(lines)):
        line = clean_text(lines[index])
        if regex.search(line):
            return line, index
    return "", start_index


def find_exact_line(lines, start_index, value):
    wanted = clean_text(value).lower()
    for index in range(max(start_index, 0), len(lines)):
        if clean_text(lines[index]).lower() == wanted:
            return clean_text(lines[index]), index
    return "", start_index


FULL_DATE_RE = re.compile(r"(?<!\d)\d{1,2}/\d{1,2}/\d{4}(?!\d)")
DATE_FRAGMENT_RE = re.compile(
    r"(?:\d{1,2}/\d{1,2}/\d{4}|\d{1,2}/\d{1,2}/|/\d{1,2}/\d{4})"
)


def extract_date_token(value):
    value = clean_text(value)
    full = FULL_DATE_RE.search(value)
    if full:
        return full.group(0)
    fragment = DATE_FRAGMENT_RE.search(value)
    if fragment:
        return fragment.group(0)
    return ""


def find_next_date_line(lines, start_index):
    for index in range(max(start_index, 0), len(lines)):
        token = extract_date_token(lines[index])
        if token:
            return token, index
    return "", start_index


def find_last_date_before(lines, start_index, end_index):
    result = ""
    result_index = start_index
    for index in range(max(start_index, 0), min(end_index, len(lines))):
        token = extract_date_token(lines[index])
        if token:
            result = token
            result_index = index
    return result, result_index


def extract_date_near_locator(locator):
    try:
        return clean_text(
            locator.evaluate(
                r"""
                el => {
                    let current = el;
                    for (let level = 0; level < 7 && current; level++) {
                        const text = (current.innerText || '').trim();
                        if (text && text.length <= 300) {
                            const full = text.match(/\b\d{1,2}\/\d{1,2}\/\d{4}\b/);
                            if (full) return full[0];
                            const fragment = text.match(/(?:\d{1,2}\/\d{1,2}\/|\/\d{1,2}\/\d{4})/);
                            if (fragment) return fragment[0];
                        }
                        current = current.parentElement;
                    }
                    return '';
                }
                """
            )
        )
    except Exception:
        return ""


# ============================================================
# LOGIN / NAVIGATION
# ============================================================


def _save_headless_navigation_diagnostic(page, label):
    """Best-effort screenshot/current-URL diagnostic for unattended runs."""
    safe_label = re.sub(r"[^A-Za-z0-9_.-]+", "_", clean_text(label)) or "avalon"
    screenshot_path = RUN_ROOT / f"{safe_label}_{datetime.now().strftime('%H%M%S')}.png"
    try:
        RUN_ROOT.mkdir(parents=True, exist_ok=True)
        page.screenshot(path=str(screenshot_path), full_page=True)
        print(f"    Diagnostic screenshot: {screenshot_path}")
    except Exception as error:
        print(f"    Could not save diagnostic screenshot: {error}")
    try:
        print(f"    Current URL: {page.url}")
    except Exception:
        pass


def _goto_avalon_with_retry(page, url, label):
    """Navigate without racing a still-finishing login redirect."""
    last_error = None
    retries = max(AVALON_NAVIGATION_RETRIES, 1)

    for attempt in range(1, retries + 1):
        try:
            print(f"    {label} navigation {attempt}/{retries}: {url}")
            response = page.goto(
                url,
                wait_until="domcontentloaded",
                timeout=AVALON_NAVIGATION_TIMEOUT_MS,
            )
            status = response.status if response is not None else "n/a"
            print(f"    {label} navigation complete: HTTP {status} | URL={page.url}")
            return response
        except Exception as error:
            last_error = error
            print(f"    {label} navigation attempt {attempt} failed: {error}")
            if attempt >= retries:
                break
            # A fast headless login can still be finishing a redirect when the
            # next explicit goto starts. Give that navigation time to settle.
            try:
                page.wait_for_load_state("domcontentloaded", timeout=5000)
            except Exception:
                pass
            page.wait_for_timeout(1500 * attempt)

    _save_headless_navigation_diagnostic(page, f"{label}_navigation_failure")
    raise RuntimeError(
        f"Could not navigate to {label} after {retries} attempt(s): {last_error}"
    )


def _wait_for_avalon_login_completion(page):
    """Wait until the login transition/session setup has genuinely settled."""
    deadline = time.monotonic() + max(AVALON_LOGIN_SETTLE_TIMEOUT_MS, 1000) / 1000.0
    form_hidden_since = None
    last_url = ""

    while time.monotonic() < deadline:
        if page.is_closed():
            raise RuntimeError("Avalon page closed while waiting for login completion.")

        try:
            current_url = page.url
        except Exception:
            current_url = ""
        if current_url != last_url and current_url:
            print(f"    Login transition URL: {current_url}")
            last_url = current_url

        # The normal successful path leaves /ssr/login. Once that happens,
        # allow the target document/session scripts a moment to initialize.
        if current_url and "/ssr/login" not in current_url.lower():
            try:
                page.wait_for_load_state("domcontentloaded", timeout=10000)
            except Exception:
                pass
            page.wait_for_timeout(1000)
            print("Authenticated Avalon session detected.")
            return

        # Some deployments finish session setup before changing URL. If the
        # credential form disappears continuously, treat that as a settled login.
        try:
            login_form_visible = page.get_by_role(
                "textbox", name="Username / Email"
            ).is_visible()
        except Exception:
            login_form_visible = False

        if not login_form_visible:
            if form_hidden_since is None:
                form_hidden_since = time.monotonic()
            elif time.monotonic() - form_hidden_since >= 2.0:
                page.wait_for_timeout(750)
                print("Avalon login form disappeared; session transition is ready.")
                return
        else:
            form_hidden_since = None

        page.wait_for_timeout(250)

    _save_headless_navigation_diagnostic(page, "login_settle_timeout")
    raise RuntimeError(
        "Avalon login did not settle before the timeout. "
        "The login page remained active or never completed its authenticated transition."
    )


def login(page):
    print("\nOpening Avalon login...")
    _goto_avalon_with_retry(page, LOGIN_URL, "Avalon login")

    username = page.get_by_role("textbox", name="Username / Email")
    password = page.get_by_role("textbox", name="Password")

    expect(username).to_be_visible(timeout=20000)
    username.fill(AVALON_USERNAME)
    password.fill(AVALON_PASSWORD)
    page.get_by_role("button", name="Login").click()
    print("Login submitted; waiting for authenticated session...")
    _wait_for_avalon_login_completion(page)


def open_saved_views(page):
    print("Opening People view...")
    _goto_avalon_with_retry(page, APP_URL, "Avalon application")

    # If the app URL sends us back to the login page, the authentication did
    # not survive. Do not continue and accidentally interpret the login page.
    if "/ssr/login" in page.url.lower():
        _save_headless_navigation_diagnostic(page, "redirected_back_to_login")
        raise RuntimeError(
            "Avalon redirected back to the login page after authentication. "
            "The authenticated session was not established in headless mode."
        )

    people_icon = page.locator(".far.fa-lg.fa-poll-people")
    expect(people_icon).to_be_visible(timeout=30000)
    people_icon.click()

    clear_all = first_visible(page.get_by_text("Clear All", exact=True))
    if clear_all:
        clear_all.click()

    saved_views_button = page.get_by_role(
        "button",
        name=re.compile("Saved Views", re.IGNORECASE),
    )
    expect(saved_views_button).to_be_visible(timeout=20000)
    saved_views_button.click()

    print("Saved Views opened.")


def open_new_hire_and_get_count(page):
    print("\nOpening New Hire view...")

    new_hire = (
        page.locator("span.saved-view-name")
        .filter(has_text=re.compile(r"^\s*New Hire\s*$", re.IGNORECASE))
        .first
    )

    expect(new_hire).to_be_visible(timeout=10000)
    new_hire.click()

    count_pattern = re.compile(r"\bNew\s+Hire\s+(\d+)\b", re.IGNORECASE)
    deadline = time.monotonic() + 20

    while time.monotonic() < deadline:
        body_text = page.locator("body").inner_text()
        normalized = re.sub(r"\s+", " ", body_text)
        matches = list(count_pattern.finditer(normalized))

        if matches:
            total = int(matches[-1].group(1))
            print("\n======================================")
            print(f"NEW HIRE RECORD COUNT: {total}")
            print("======================================")

            if total > 0:
                expect(
                    page.locator('[role="gridcell"]:visible').first
                ).to_be_visible(timeout=20000)

            return total

        page.wait_for_timeout(250)

    raise RuntimeError("Could not determine New Hire record count.")


def _available_saved_view_names(page):
    """Return visible/known Avalon saved-view names without changing the selection."""
    names = []
    locator = page.locator("span.saved-view-name")
    try:
        count = locator.count()
    except Exception:
        count = 0
    for index in range(count):
        try:
            value = clean_text(locator.nth(index).inner_text())
        except Exception:
            value = ""
        if value and value.casefold() not in {item.casefold() for item in names}:
            names.append(value)
    return names


def open_saved_view_and_get_count(page, requested_name, aliases=None):
    """Open a named Avalon Saved View and return (actual_name, reported_count).

    Generic saved-view helper retained for compatibility with other Avatar tenants.
    The current reverse reconciliation does not use this helper; it explicitly reopens
    the New Hire view and scans the complete New Hire roster across all pages.
    """
    aliases = aliases or []
    available = _available_saved_view_names(page)
    candidates = []
    for value in [requested_name, *aliases]:
        value = clean_text(value)
        if value and value.casefold() not in {item.casefold() for item in candidates}:
            candidates.append(value)

    actual_name = ""
    available_by_fold = {value.casefold(): value for value in available}
    for candidate in candidates:
        if candidate.casefold() in available_by_fold:
            actual_name = available_by_fold[candidate.casefold()]
            break

    if not actual_name:
        raise RuntimeError(
            "Could not find the requested Avatar Saved View. "
            f"Requested/aliases={candidates!r}. Available saved views={available!r}."
        )

    print(f"\nOpening Avatar active-driver view: {actual_name!r}...")
    target = (
        page.locator("span.saved-view-name")
        .filter(has_text=re.compile(rf"^\s*{re.escape(actual_name)}\s*$", re.IGNORECASE))
        .first
    )
    expect(target).to_be_visible(timeout=10000)
    target.click()

    patterns = [
        re.compile(rf"\b{re.escape(actual_name)}\s+(\d+)\b", re.IGNORECASE),
        re.compile(rf"\b{re.escape(actual_name)}\s*\((\d+)\)", re.IGNORECASE),
    ]
    deadline = time.monotonic() + 20

    while time.monotonic() < deadline:
        body_text = page.locator("body").inner_text()
        normalized = re.sub(r"\s+", " ", body_text)
        matches = []
        for pattern in patterns:
            matches.extend(pattern.finditer(normalized))
        if matches:
            # If the name happens to appear more than once, the latest rendered
            # occurrence is normally the selected-view header/count.
            total = int(matches[-1].group(1))
            print("\n======================================")
            print(f"ACTIVE AVATAR VIEW: {actual_name}")
            print(f"ACTIVE AVATAR RECORD COUNT: {total}")
            print("======================================")
            if total > 0:
                expect(page.locator('[role="gridcell"]:visible').first).to_be_visible(
                    timeout=20000
                )
            return actual_name, total
        page.wait_for_timeout(250)

    raise RuntimeError(
        f"Opened Avatar saved view {actual_name!r}, but could not determine its record count. "
        "Confirm that the selected view displays its count next to the saved-view name."
    )


# ============================================================
# DISCOVER VIRTUAL GRID ROWS
# ============================================================


def discover_visual_rows(page, verbose=False):
    cells = page.locator('[role="gridcell"]:visible')
    expect(cells.first).to_be_visible(timeout=20000)

    rows = page.evaluate(
        """
        () => {
            function visible(el) {
                if (!el) return false;
                const style = window.getComputedStyle(el);
                const rect = el.getBoundingClientRect();
                return (
                    style.display !== 'none' &&
                    style.visibility !== 'hidden' &&
                    rect.width > 0 &&
                    rect.height > 0
                );
            }

            function getStableId(el) {
                let current = el;
                for (let i = 0; i < 10 && current; i++) {
                    const attrs = [
                        'webix_l_id',
                        'data-row-id',
                        'data-id',
                        'data-uid',
                        'aria-rowindex'
                    ];
                    for (const attr of attrs) {
                        const value = current.getAttribute(attr);
                        if (value) return attr + ':' + value;
                    }
                    current = current.parentElement;
                }
                return '';
            }

            document
                .querySelectorAll('[data-automation-cell-id]')
                .forEach(el => el.removeAttribute('data-automation-cell-id'));

            const elements = Array.from(
                document.querySelectorAll('[role="gridcell"]')
            ).filter(visible);

            const cells = elements.map((el, index) => {
                el.setAttribute('data-automation-cell-id', String(index));
                const rect = el.getBoundingClientRect();
                return {
                    cellId: String(index),
                    text: (el.innerText || '').trim(),
                    top: rect.top,
                    left: rect.left,
                    stableId: getStableId(el),
                    ariaRowIndex: el.getAttribute('aria-rowindex') || '',
                    ariaColIndex: el.getAttribute('aria-colindex') || ''
                };
            });

            cells.sort((a, b) => {
                if (Math.abs(a.top - b.top) > 5) return a.top - b.top;
                return a.left - b.left;
            });

            const rows = [];

            for (const cell of cells) {
                let row = null;
                for (const candidate of rows) {
                    if (Math.abs(candidate.top - cell.top) <= 5) {
                        row = candidate;
                        break;
                    }
                }

                if (!row) {
                    row = {top: cell.top, cells: []};
                    rows.push(row);
                }

                row.cells.push(cell);
            }

            return rows
                .filter(row => row.cells.some(cell => cell.text.trim()))
                .map((row, index) => {
                    row.cells.sort((a, b) => a.left - b.left);

                    const texts = row.cells
                        .map(cell => cell.text.trim())
                        .filter(Boolean);

                    const stableIds = row.cells
                        .map(cell => cell.stableId)
                        .filter(Boolean);

                    return {
                        index,
                        key: stableIds.length ? stableIds[0] : 'TEXT:' + texts.join('||'),
                        texts,
                        cells: row.cells
                    };
                });
        }
        """
    )

    print(f"    Visual rows currently rendered: {len(rows)}")

    if verbose:
        for row in rows:
            print(f"        ROW {row['index']}: {row['texts']}")

    return rows


# ============================================================
# GRID SCROLL HELPERS
# ============================================================


def get_scroll_container_state(page):
    cells = page.locator('[role="gridcell"]:visible')
    if cells.count() == 0:
        return {"found": False, "top": 0, "max": 0}

    return cells.first.evaluate(
        """
        el => {
            const candidates = [];
            let current = el.parentElement;

            while (current) {
                const style = window.getComputedStyle(current);
                const range = current.scrollHeight - current.clientHeight;
                if (
                    range > 5 &&
                    (style.overflowY === 'auto' || style.overflowY === 'scroll')
                ) {
                    candidates.push({element: current, range});
                }
                current = current.parentElement;
            }

            if (!candidates.length) {
                return {found: false, top: 0, max: 0};
            }

            candidates.sort((a, b) => b.range - a.range);
            const target = candidates[0].element;

            return {
                found: true,
                top: target.scrollTop,
                max: target.scrollHeight - target.clientHeight
            };
        }
        """
    )


def set_grid_scroll_position(page, top):
    cells = page.locator('[role="gridcell"]:visible')
    if cells.count() == 0:
        return False

    result = cells.first.evaluate(
        """
        (el, requestedTop) => {
            const candidates = [];
            let current = el.parentElement;

            while (current) {
                const style = window.getComputedStyle(current);
                const range = current.scrollHeight - current.clientHeight;
                if (
                    range > 5 &&
                    (style.overflowY === 'auto' || style.overflowY === 'scroll')
                ) {
                    candidates.push({element: current, range});
                }
                current = current.parentElement;
            }

            if (!candidates.length) return false;

            candidates.sort((a, b) => b.range - a.range);
            const target = candidates[0].element;
            const max = target.scrollHeight - target.clientHeight;

            target.scrollTop = Math.min(Math.max(requestedTop, 0), max);
            target.dispatchEvent(new Event('scroll', {bubbles: true}));
            return true;
        }
        """,
        top,
    )

    if result:
        page.wait_for_timeout(800)

    return result


def _webix_datatable_state(page):
    """Return safe scroll diagnostics for the actual visible Webix DataTable.

    Do not call getPage()/setPage() here. Avatar exposes paging mixin methods on
    objects whose pager state is not always initialized, which can raise inside
    Webix while dereferencing _settings. For diagnostics we only need the DataTable
    identity and scroll position.
    """
    try:
        return page.evaluate(
            """
            () => {
                function visible(el) {
                    if (!el) return false;
                    const style = window.getComputedStyle(el);
                    const rect = el.getBoundingClientRect();
                    return (
                        style.display !== 'none' &&
                        style.visibility !== 'hidden' &&
                        rect.width > 0 && rect.height > 0
                    );
                }

                if (!window.webix || typeof window.webix.$$ !== 'function') {
                    return {found:false, reason:'webix-not-available'};
                }

                const cell = Array.from(
                    document.querySelectorAll('[role="gridcell"]')
                ).find(visible);
                if (!cell) return {found:false, reason:'no-visible-gridcell'};

                const candidates = [];
                let current = cell;
                while (current) {
                    if (current.getAttribute) {
                        const viewId = current.getAttribute('view_id');
                        if (viewId && !candidates.includes(viewId)) candidates.push(viewId);
                    }
                    current = current.parentElement;
                }

                // Prefer true DataTable views. Fall back only to a view that both
                // owns/contains the visible cell and implements DataTable-style
                // scrolling. Never use pager APIs here.
                const ranked = [];
                for (const id of candidates) {
                    let view = null;
                    try { view = window.webix.$$(id); } catch (e) {}
                    if (!view || typeof view.scrollTo !== 'function') continue;

                    const type = view.config && view.config.view ? String(view.config.view) : '';
                    const root = view.$view || null;
                    const containsCell = !!(root && (root === cell || root.contains(cell)));
                    const hasData = !!view.data;
                    const hasScrollState = typeof view.getScrollState === 'function';
                    const score = (type === 'datatable' ? 100 : 0) +
                                  (containsCell ? 30 : 0) +
                                  (hasData ? 20 : 0) +
                                  (hasScrollState ? 10 : 0);
                    ranked.push({id, view, type, containsCell, score});
                }

                ranked.sort((a, b) => b.score - a.score);
                if (!ranked.length || ranked[0].score < 30) {
                    return {
                        found:false,
                        reason:'datatable-view-not-found',
                        candidateIds:candidates
                    };
                }

                const selected = ranked[0];
                let scroll = null;
                try {
                    scroll = typeof selected.view.getScrollState === 'function'
                        ? selected.view.getScrollState()
                        : null;
                } catch (e) {}

                return {
                    found:true,
                    id:selected.id,
                    viewType:selected.type,
                    score:selected.score,
                    containsCell:selected.containsCell,
                    scrollX:scroll && Number.isFinite(Number(scroll.x)) ? Number(scroll.x) : null,
                    scrollY:scroll && Number.isFinite(Number(scroll.y)) ? Number(scroll.y) : null
                };
            }
            """
        )
    except Exception as error:
        return {"found": False, "reason": f"state-error: {error}"}


def _webix_scroll_to_top(page):
    """Scroll the actual visible Webix DataTable to y=0 without touching pager APIs."""
    try:
        result = page.evaluate(
            """
            () => {
                function visible(el) {
                    if (!el) return false;
                    const style = window.getComputedStyle(el);
                    const rect = el.getBoundingClientRect();
                    return (
                        style.display !== 'none' &&
                        style.visibility !== 'hidden' &&
                        rect.width > 0 && rect.height > 0
                    );
                }

                if (!window.webix || typeof window.webix.$$ !== 'function') {
                    return {used:false, reason:'webix-not-available'};
                }

                const cell = Array.from(
                    document.querySelectorAll('[role="gridcell"]')
                ).find(visible);
                if (!cell) return {used:false, reason:'no-visible-gridcell'};

                const candidates = [];
                let current = cell;
                while (current) {
                    if (current.getAttribute) {
                        const viewId = current.getAttribute('view_id');
                        if (viewId && !candidates.includes(viewId)) candidates.push(viewId);
                    }
                    current = current.parentElement;
                }

                const ranked = [];
                for (const id of candidates) {
                    let view = null;
                    try { view = window.webix.$$(id); } catch (e) {}
                    if (!view || typeof view.scrollTo !== 'function') continue;
                    const type = view.config && view.config.view ? String(view.config.view) : '';
                    const root = view.$view || null;
                    const containsCell = !!(root && (root === cell || root.contains(cell)));
                    const hasData = !!view.data;
                    const hasScrollState = typeof view.getScrollState === 'function';
                    const score = (type === 'datatable' ? 100 : 0) +
                                  (containsCell ? 30 : 0) +
                                  (hasData ? 20 : 0) +
                                  (hasScrollState ? 10 : 0);
                    ranked.push({id, view, type, containsCell, score});
                }

                ranked.sort((a, b) => b.score - a.score);
                if (!ranked.length || ranked[0].score < 30) {
                    return {used:false, reason:'datatable-view-not-found', candidateIds:candidates};
                }

                const selected = ranked[0];
                try {
                    selected.view.scrollTo(0, 0);
                } catch (e) {
                    return {
                        used:false,
                        reason:'scrollTo-error: ' + String(e),
                        id:selected.id,
                        viewType:selected.type
                    };
                }

                let scroll = null;
                try {
                    scroll = typeof selected.view.getScrollState === 'function'
                        ? selected.view.getScrollState()
                        : null;
                } catch (e) {}

                return {
                    used:true,
                    id:selected.id,
                    viewType:selected.type,
                    score:selected.score,
                    scrollY:scroll && Number.isFinite(Number(scroll.y)) ? Number(scroll.y) : null
                };
            }
            """
        )
    except Exception as error:
        return {"used": False, "reason": f"scroll-top-error: {error}"}

    if result.get("used"):
        page.wait_for_timeout(1200)
    return result


def _visible_grid_aria_rowindexes(page):
    """Return currently rendered unique aria-rowindex values for diagnostics."""
    try:
        values = page.evaluate(
            """
            () => Array.from(document.querySelectorAll('[role="gridcell"]'))
                .filter(el => {
                    const s = window.getComputedStyle(el);
                    const r = el.getBoundingClientRect();
                    return s.display !== 'none' && s.visibility !== 'hidden' && r.width > 0 && r.height > 0;
                })
                .map(el => el.getAttribute('aria-rowindex'))
                .filter(Boolean)
            """
        )
    except Exception:
        values = []

    parsed = []
    for value in values or []:
        try:
            numeric = int(str(value).strip())
        except Exception:
            continue
        if numeric not in parsed:
            parsed.append(numeric)
    return sorted(parsed)


def reset_grid_to_top(page):
    """Reset Avatar's virtualized Webix grid to the first row of the current page.

    The old implementation only changed a native ancestor's scrollTop. Avatar's
    Webix DataTable can keep a separate virtual scroll position, especially after
    pager navigation. Prefer Webix scrollTo(0, 0), then retain native/keyboard
    fallbacks for environments where the Webix view cannot be resolved.
    """
    print("    Resetting grid to top...")

    webix_result = _webix_scroll_to_top(page)
    if webix_result.get("used"):
        print(
            "    Webix DataTable reset: "
            f"id={webix_result.get('id')} "
            f"type={webix_result.get('viewType') or '?'} "
            f"scrollY={webix_result.get('scrollY')}"
        )
    else:
        print(
            "    Webix API reset unavailable; using native scroll fallback: "
            f"{webix_result.get('reason', 'unknown')}"
        )
        set_grid_scroll_position(page, 0)

        # Last-resort keyboard reset. Focus without clicking so we do not open a
        # driver dialog, then send Ctrl+Home/Home to the grid.
        try:
            cell = page.locator('[role="gridcell"]:visible').first
            cell.focus(timeout=2000)
            page.keyboard.press("Control+Home")
            page.wait_for_timeout(400)
            page.keyboard.press("Home")
        except Exception:
            pass

    page.wait_for_timeout(800)
    indexes = _visible_grid_aria_rowindexes(page)
    if indexes:
        print(
            "    Grid top verification aria-rowindex sample: "
            f"{indexes[:12]}"
        )
    return webix_result


def scroll_grid_down(page, visual_rows):
    """Use mouse-wheel first (works well with Webix), then native scroll fallback."""
    before = get_scroll_container_state(page)

    target = None
    if visual_rows:
        for cell_info in reversed(visual_rows[-1]["cells"]):
            if clean_text(cell_info.get("text")):
                # Use the same ARIA-aware re-location strategy used for
                # driver clicks. Webix can recycle temporary DOM nodes while
                # scrolling, so data-automation-cell-id is only a fallback.
                target = _locate_current_grid_cell(page, cell_info)
                if target is not None:
                    break

    if target is not None:
        try:
            target.hover(timeout=3000)
            page.mouse.wheel(0, 900)
            page.wait_for_timeout(1200)
        except Exception:
            pass

    after_wheel = get_scroll_container_state(page)
    if after_wheel.get("top", 0) > before.get("top", 0):
        return {
            "moved": True,
            "before": before.get("top", 0),
            "after": after_wheel.get("top", 0),
            "max": after_wheel.get("max", 0),
        }

    # Native-scroll fallback for ordinary scroll containers.
    cells = page.locator('[role="gridcell"]:visible')
    if cells.count() == 0:
        return {"moved": False, "before": 0, "after": 0, "max": 0}

    result = cells.first.evaluate(
        """
        el => {
            const candidates = [];
            let current = el.parentElement;

            while (current) {
                const style = window.getComputedStyle(current);
                const range = current.scrollHeight - current.clientHeight;
                if (
                    range > 5 &&
                    (style.overflowY === 'auto' || style.overflowY === 'scroll')
                ) {
                    candidates.push({element: current, range});
                }
                current = current.parentElement;
            }

            if (!candidates.length) {
                return {moved: false, before: 0, after: 0, max: 0};
            }

            candidates.sort((a, b) => b.range - a.range);
            const target = candidates[0].element;
            const before = target.scrollTop;
            const max = target.scrollHeight - target.clientHeight;
            const amount = Math.max(target.clientHeight * 0.80, 500);

            target.scrollTop = Math.min(before + amount, max);
            target.dispatchEvent(new Event('scroll', {bubbles: true}));

            return {
                moved: target.scrollTop > before,
                before,
                after: target.scrollTop,
                max
            };
        }
        """
    )

    page.wait_for_timeout(1200)
    return result


# ============================================================
# OPEN DRIVER ROW
# ============================================================


def wait_for_grid_click_ready(page):
    """Wait briefly for the previous driver/modal UI to finish closing."""
    try:
        expect(
            page.locator('[role="dialog"]:visible')
        ).to_have_count(0, timeout=5000)
    except Exception:
        # Some Avalon/Webix popups do not use role=dialog; give the UI a
        # short settling period before trying the next virtualized row.
        page.wait_for_timeout(500)

    # If a Webix modal cover is present from the previous action, wait for it.
    try:
        expect(
            page.locator('.webix_modal_cover:visible')
        ).to_have_count(0, timeout=3000)
    except Exception:
        pass

    page.wait_for_timeout(250)


def _locate_current_grid_cell(page, cell_info):
    """Re-locate a virtualized grid cell using stable ARIA coordinates."""
    row_index = clean_text(cell_info.get('ariaRowIndex'))
    col_index = clean_text(cell_info.get('ariaColIndex'))
    expected_text = clean_text(cell_info.get('text'))

    # Avalon exposes useful ARIA row/column coordinates. Prefer them because
    # data-automation-cell-id is only a temporary marker created by our scan.
    if row_index and col_index:
        locator = page.locator(
            f'[role="gridcell"][aria-rowindex="{row_index}"]'
            f'[aria-colindex="{col_index}"]'
        )
        if locator.count() > 0:
            # If virtualization temporarily leaves more than one match, keep
            # the visible one whose text still matches the row we discovered.
            for i in range(locator.count()):
                candidate = locator.nth(i)
                try:
                    if (
                        candidate.is_visible()
                        and clean_text(candidate.inner_text()) == expected_text
                    ):
                        return candidate
                except Exception:
                    pass

            visible = first_visible(locator)
            if visible is not None:
                return visible

    # Fallback for cells where ARIA coordinates are unavailable.
    cell_id = clean_text(cell_info.get('cellId'))
    if cell_id:
        locator = page.locator(
            f'[data-automation-cell-id="{cell_id}"]'
        )
        visible = first_visible(locator)
        if visible is not None:
            return visible

    return None


def _driver_dialog_open(page):
    try:
        return page.locator('[role="dialog"]:visible').count() > 0
    except Exception:
        return False


def _try_open_grid_cell(page, cell_info):
    """Click one Webix grid cell robustly and return True if dialog opens."""
    expected_text = clean_text(cell_info.get('text'))
    row_index = clean_text(cell_info.get('ariaRowIndex'))
    col_index = clean_text(cell_info.get('ariaColIndex'))

    # We retry by RE-LOCATING the cell each time because Webix can recycle the
    # DOM node during scrolling/actionability checks.
    attempts = (
        ('normal', False),
        ('force', True),
        ('dispatch', None),
    )

    for mode, force in attempts:
        cell = _locate_current_grid_cell(page, cell_info)
        if cell is None:
            print(
                f"    Cell disappeared before {mode} click: "
                f"row={row_index or '?'} col={col_index or '?'} "
                f"text={expected_text!r}"
            )
            page.wait_for_timeout(300)
            continue

        try:
            # Verify that a recycled Webix cell still contains the expected
            # employee name before clicking it.
            current_text = clean_text(cell.inner_text())
            if expected_text and current_text != expected_text:
                print(
                    f"    Cell text changed during virtualization: "
                    f"expected={expected_text!r}, current={current_text!r}"
                )
                return False

            # If already fully visible, this is effectively a no-op. If it is
            # clipped at an edge of the virtual grid, Playwright brings it into
            # view before the click.
            try:
                cell.scroll_into_view_if_needed(timeout=3000)
            except Exception:
                pass

            page.wait_for_timeout(250)

            # Re-locate once more after any scroll because Webix may recycle
            # the underlying div while scrolling.
            cell = _locate_current_grid_cell(page, cell_info)
            if cell is None:
                continue

            if mode == 'normal':
                print(
                    f"    Clicking driver normally: {expected_text} "
                    f"(row {row_index or '?'}, col {col_index or '?'})"
                )
                cell.click(timeout=5000)

            elif mode == 'force':
                print(
                    f"    Retrying forced click: {expected_text} "
                    f"(row {row_index or '?'}, col {col_index or '?'})"
                )
                cell.click(force=True, timeout=3000)

            else:
                print(
                    f"    Retrying dispatched click: {expected_text} "
                    f"(row {row_index or '?'}, col {col_index or '?'})"
                )
                # Official Playwright fallback that dispatches a DOM click
                # without normal actionability checks.
                cell.dispatch_event('click')

            print('    Waiting 3 seconds for driver screen...')
            page.wait_for_timeout(ROW_OPEN_WAIT_MS)

            if _driver_dialog_open(page):
                print(f"    Driver opened: {expected_text}")
                return True

            print(
                f"    {mode} click completed but driver dialog did not open."
            )

        except PlaywrightTimeoutError as error:
            print(
                f"    {mode} click timed out for {expected_text!r}: {error}"
            )

        except Exception as error:
            print(
                f"    {mode} click failed for {expected_text!r}: {error}"
            )

        page.wait_for_timeout(500)

    return False


def open_visual_row(page, row):
    """Open a driver reliably in Avalon's virtualized Webix grid."""
    wait_for_grid_click_ready(page)

    candidates = []

    # The Codegen sample and the timeout log both show that the employee first
    # name is in aria-colindex=2. Prefer that exact column.
    for cell_info in row.get('cells', []):
        if (
            clean_text(cell_info.get('ariaColIndex')) == '2'
            and clean_text(cell_info.get('text'))
        ):
            candidates.append(cell_info)

    # Fallback: first text cell in the visual row.
    if not candidates and row.get('texts'):
        first_text = clean_text(row['texts'][0])
        for cell_info in row.get('cells', []):
            if clean_text(cell_info.get('text')) == first_text:
                candidates.append(cell_info)
                break

    # Final fallback: other non-empty cells.
    for cell_info in row.get('cells', []):
        if (
            cell_info not in candidates
            and clean_text(cell_info.get('text'))
        ):
            candidates.append(cell_info)

    for cell_info in candidates:
        text = clean_text(cell_info.get('text'))
        if _try_open_grid_cell(page, cell_info):
            return text

    raise RuntimeError(
        'Could not open driver row after normal, forced, and dispatched click attempts.'
    )


def get_driver_dialog(page):
    dialog = page.locator('[role="dialog"]:visible').last
    expect(dialog).to_be_visible(timeout=10000)
    return dialog


def infer_last_name(row_texts, first_name):
    try:
        first_index = next(
            index
            for index, value in enumerate(row_texts)
            if clean_text(value).lower() == clean_text(first_name).lower()
        )
        if first_index + 1 < len(row_texts):
            return clean_text(row_texts[first_index + 1])
    except Exception:
        pass
    return ""


# ============================================================
# LATEST SEQUENTIAL DATA CAPTURE
# ============================================================


def capture_driver_information(page, dialog, first_name, row_texts):
    """
    Mirrors the user's latest Codegen order:

      email
      phone
      address
      manager
      location
      designation
      company
      first date (mapped to DOJ)
      DOB/date fragment
      reveal DOB
      revealed DOB
      first name
      last name
      license number
      license class
      CDL
      motorcycle
      country
      state
      issue date
      expiry date

    Only the DOB reveal is clicked. Text fields are read, not clicked.
    """

    record = {}
    print("\n    CAPTURING DRIVER INFORMATION")
    print("    ----------------------------")

    lines_before = get_dialog_lines(dialog)
    cursor = 0

    # 1. Email
    email, cursor = find_next_matching_line(
        lines_before,
        cursor,
        r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}",
    )
    print_capture(page, record, "Email", email)
    cursor += 1

    # 2. Phone
    phone, cursor = find_next_matching_line_search(
        lines_before,
        cursor,
        r"\(?\d{3}\)?[\s.-]*\d{3}[\s.-]*\d{4}",
    )
    phone_match = re.search(r"\(?\d{3}\)?[\s.-]*\d{3}[\s.-]*\d{4}", phone)
    phone = phone_match.group(0) if phone_match else phone
    print_capture(page, record, "Phone", phone)
    cursor += 1

    # 3. Address
    address, cursor = find_next_matching_line_search(
        lines_before,
        cursor,
        r"^\d+\s+.*\b(?:Street|St|Road|Rd|Avenue|Ave|Court|Ct|Drive|Dr|Lane|Ln|Way|Boulevard|Blvd|Highway|Hwy)\b.*$",
    )
    print_capture(page, record, "Address", address)
    cursor += 1

    # 4. Manager - e.g. Easton, Donald
    manager, cursor = find_next_matching_line(
        lines_before,
        cursor,
        r"[A-Za-z][A-Za-z' .-]+,\s*[A-Za-z][A-Za-z' .-]+",
    )
    print_capture(page, record, "Manager", manager)
    cursor += 1

    # 5. Location - e.g. Houston TX
    location, cursor = find_next_matching_line(
        lines_before,
        cursor,
        r"[A-Za-z][A-Za-z .'/-]*(?:\s+[A-Za-z][A-Za-z .'/-]*)*\s+[A-Z]{2}",
    )
    if location == "US":
        location = ""
    print_capture(page, record, "Location", location)
    cursor += 1

    # 6. Designation - e.g. CDL Driver
    designation, cursor = find_next_matching_line_search(
        lines_before,
        cursor,
        r"\bDriver\b",
    )
    print_capture(page, record, "Designation", designation)
    cursor += 1

    # 7. Company
    company, cursor = find_next_matching_line_search(
        lines_before,
        cursor,
        r"\bAvalon\b.*\bLLC\b",
    )
    print_capture(page, record, "Company", company)
    cursor += 1

    # 8. First date after company. This is mapped to DOJ.
    doj, cursor = find_next_date_line(lines_before, cursor)
    print_capture(page, record, "DOJ", doj)
    cursor += 1

    # 9. Date fragment immediately before DOB reveal. Keep for diagnostics.
    dob_before_reveal, dob_before_index = find_next_date_line(lines_before, cursor)
    print_capture(page, record, "DOB Before Reveal", dob_before_reveal)

    # 10. Reveal DOB.
    dob_button = dialog.locator(".view-dob-btn > .fas")
    if dob_button.count() == 0:
        dob_button = page.locator(".view-dob-btn > .fas")

    dob_near_before = ""
    try:
        dob_near_before = extract_date_near_locator(dob_button.first)
        expect(dob_button.first).to_be_visible(timeout=5000)
        dob_button.first.click()
        print("        DOB reveal clicked")
    except Exception as error:
        print(f"        DOB reveal failed: {error}")

    wait_after_field(page)

    # 11. Capture DOB after reveal.
    lines_after = get_dialog_lines(dialog)

    first_name_line, first_name_index = find_exact_line(lines_after, 0, first_name)
    if not first_name_line:
        first_name_index = len(lines_after)

    company_after, company_after_index = find_exact_line(lines_after, 0, company)
    dob_search_start = company_after_index + 1 if company_after else 0

    dob_after, _ = find_last_date_before(
        lines_after,
        dob_search_start,
        first_name_index,
    )

    dob_near_after = extract_date_near_locator(dob_button.first)

    # Prefer a complete value close to the DOB control, then sequence-based value.
    dob = ""
    for candidate in (dob_near_after, dob_after, dob_before_reveal, dob_near_before):
        candidate = extract_date_token(candidate) or clean_text(candidate)
        if candidate and FULL_DATE_RE.fullmatch(candidate):
            dob = candidate
            break

    if not dob:
        for candidate in (dob_near_after, dob_after, dob_before_reveal, dob_near_before):
            candidate = extract_date_token(candidate) or clean_text(candidate)
            if candidate:
                dob = candidate
                break

    print_capture(page, record, "DOB", dob)

    # Re-read because reveal may have changed DOM text.
    lines_after = get_dialog_lines(dialog)
    cursor = 0

    # 12. First Name
    first_name_value, cursor = find_exact_line(lines_after, cursor, first_name)
    if not first_name_value:
        first_name_value = first_name
    print_capture(page, record, "First Name", first_name_value)
    cursor += 1

    # 13. Last Name
    expected_last_name = infer_last_name(row_texts, first_name)
    last_name, last_name_index = find_exact_line(lines_after, cursor, expected_last_name)
    if not last_name:
        last_name = expected_last_name
        last_name_index = cursor
    print_capture(page, record, "Last Name", last_name)
    cursor = last_name_index + 1

    # 14. License Number
    license_number, cursor = find_next_matching_line(
        lines_after,
        cursor,
        r"(?=.*\d)[A-Z0-9-]{5,20}",
    )
    print_capture(page, record, "License Number", license_number)
    cursor += 1

    # 15. License Class
    license_class, cursor = find_next_matching_line(
        lines_after,
        cursor,
        r"[A-Z]",
    )
    print_capture(page, record, "License Class", license_class)
    cursor += 1

    # 16. CDL
    cdl, cursor = find_next_matching_line(
        lines_after,
        cursor,
        r"(?:Yes|No)",
    )
    print_capture(page, record, "CDL", cdl)
    cursor += 1

    # 17. Motorcycle
    motorcycle, cursor = find_next_matching_line(
        lines_after,
        cursor,
        r"(?:Yes|No)",
    )
    print_capture(page, record, "Motorcycle", motorcycle)
    cursor += 1

    # 18-21. Country / State / Issue Date / Expiry Date
    #
    # Restore the original sequential parser FIRST. This was the proven path
    # before the lazy-render recovery code was introduced. Only if one or more
    # values are missing do we scroll/re-read and inspect labeled controls.
    initial_tail = parse_license_tail_from_cursor(lines_after, cursor)
    print(
        "        Initial sequential license-tail: "
        f"Country={initial_tail[0] or '<blank>'}, "
        f"State={initial_tail[1] or '<blank>'}, "
        f"Issue Date={initial_tail[2] or '<blank>'}, "
        f"Expiry Date={initial_tail[3] or '<blank>'}"
    )

    if all(clean_text(value) for value in initial_tail):
        country, state, issue_date, expiry_date = initial_tail
    else:
        country, state, issue_date, expiry_date = capture_license_tail_with_retries(
            page,
            dialog,
            initial_values=initial_tail,
        )
    print_capture(page, record, "Country", country)
    print_capture(page, record, "State", state)
    print_capture(page, record, "Issue Date", issue_date)
    print_capture(page, record, "Expiry Date", expiry_date)

    if not state or not issue_date or not expiry_date:
        missing_tail = [
            name
            for name, value in (
                ("State", state),
                ("Issue Date", issue_date),
                ("Expiry Date", expiry_date),
            )
            if not clean_text(value)
        ]
        print(
            "        WARNING: Avalon license-tail field(s) still blank after "
            f"{max(LICENSE_TAIL_RETRIES, 1)} retries: {', '.join(missing_tail)}"
        )

    print("\n    DRIVER INFORMATION CAPTURED")
    return record



# ============================================================
# CURRENT-MONTH GRID PREFILTER HELPERS
# ============================================================


def get_target_year_month():
    """Return (year, month) for the current run's DOJ screening month."""
    if TARGET_MONTH:
        try:
            parsed = datetime.strptime(TARGET_MONTH, "%Y-%m")
        except ValueError as error:
            raise RuntimeError(
                "TARGET_MONTH must use YYYY-MM, for example 2026-08."
            ) from error
        return parsed.year, parsed.month

    now = datetime.now()
    return now.year, now.month


def get_target_month_label():
    year, month = get_target_year_month()
    return datetime(year, month, 1).strftime("%B %Y")


def get_target_month_bounds():
    """Return the inclusive month start and exclusive next-month boundary."""
    year, month = get_target_year_month()
    start = datetime(year, month, 1)
    if month == 12:
        end = datetime(year + 1, 1, 1)
    else:
        end = datetime(year, month + 1, 1)
    return start, end


def get_paychex_termination_year_month():
    """Return the month used for the Paychex termination control.

    This intentionally does not inherit TARGET_MONTH. The default is the server's
    current local month. PAYCHEX_TERMINATION_MONTH=YYYY-MM is an explicit override
    for testing/backfill only.
    """
    if PAYCHEX_TERMINATION_MONTH:
        try:
            parsed = datetime.strptime(PAYCHEX_TERMINATION_MONTH, "%Y-%m")
        except ValueError as error:
            raise RuntimeError(
                "PAYCHEX_TERMINATION_MONTH must use YYYY-MM, for example 2026-09."
            ) from error
        return parsed.year, parsed.month

    now = datetime.now()
    return now.year, now.month


def get_paychex_termination_month_label():
    year, month = get_paychex_termination_year_month()
    return datetime(year, month, 1).strftime("%B %Y")


def classify_start_date(value):
    """
    Classify a Start Date/DOJ relative to the target month.

    Because Avalon is sorted newest Start Date first:
      * future  -> ignore but keep scanning (current month may follow)
      * current -> shortlist candidate for DB precheck
      * older   -> hard stop; every row below should also be older
      * invalid -> do not stop; keep scanning so one bad date cannot truncate the run
    """
    parsed = parse_business_date(value)
    if parsed is None:
        return "invalid", None

    month_start, next_month = get_target_month_bounds()
    if month_start <= parsed < next_month:
        return "current", parsed
    if parsed >= next_month:
        return "future", parsed
    return "older", parsed




def get_row_aria_index(row):
    """Return Avalon's logical aria-rowindex for a rendered grid row, if available."""
    values = []
    for cell_info in row.get("cells", []):
        value = clean_text(cell_info.get("ariaRowIndex"))
        if value.isdigit():
            values.append(int(value))
    if not values:
        return None
    return max(set(values), key=values.count)


def get_row_identity(page_number, row):
    """Prefer page + aria-rowindex so Webix recycling cannot collapse rows."""
    aria_rowindex = get_row_aria_index(row)
    if aria_rowindex is not None:
        return f"PAGE:{page_number}||ARIA_ROW:{aria_rowindex}"
    row_text = "||".join(row.get("texts", []))
    return f"PAGE:{page_number}||{row.get('key', '')}||TEXT:{row_text}"


def get_visible_aria_bounds(visual_rows):
    indexes = [v for v in (get_row_aria_index(r) for r in visual_rows) if v is not None]
    return (min(indexes), max(indexes)) if indexes else (None, None)


def estimate_visible_row_height(visual_rows):
    tops = []
    for row in visual_rows:
        vals = [c.get("top") for c in row.get("cells", []) if isinstance(c.get("top"), (int, float))]
        if vals:
            tops.append(min(vals))
    tops = sorted(set(round(v, 2) for v in tops))
    diffs = [b-a for a,b in zip(tops, tops[1:]) if 8 <= b-a <= 120]
    if diffs:
        diffs.sort()
        return diffs[len(diffs)//2]
    return 40


def scroll_grid_down_dense(page, visual_rows, rows_per_step=None):
    """Small overlapping virtual-grid scroll used when every logical row must be observed."""
    if rows_per_step is None:
        rows_per_step = PASS1_SCROLL_ROWS
    rows_per_step = max(1, int(rows_per_step))
    before_min, before_max = get_visible_aria_bounds(visual_rows)
    row_height = estimate_visible_row_height(visual_rows)
    pixel_step = max(40, min(int(row_height * rows_per_step), 240))

    target = None
    if visual_rows:
        for cell_info in reversed(visual_rows[-1].get("cells", [])):
            if clean_text(cell_info.get("text")):
                target = _locate_current_grid_cell(page, cell_info)
                if target is not None:
                    break
    if target is not None:
        try:
            target.hover(timeout=3000)
        except Exception:
            pass

    page.mouse.wheel(0, pixel_step)
    page.wait_for_timeout(550)
    after_rows = discover_visual_rows(page, verbose=False)
    after_min, after_max = get_visible_aria_bounds(after_rows)
    moved = (before_min, before_max) != (after_min, after_max)

    if not moved:
        cells = page.locator('[role="gridcell"]:visible')
        if cells.count() > 0:
            try:
                cells.first.evaluate(
                    """
                    (el, amount) => {
                        const candidates = [];
                        let current = el.parentElement;
                        while (current) {
                            const style = window.getComputedStyle(current);
                            const range = current.scrollHeight - current.clientHeight;
                            if (range > 5 && (style.overflowY === 'auto' || style.overflowY === 'scroll')) {
                                candidates.push({element: current, range});
                            }
                            current = current.parentElement;
                        }
                        if (!candidates.length) return false;
                        candidates.sort((a,b) => b.range-a.range);
                        const target = candidates[0].element;
                        const before = target.scrollTop;
                        target.scrollTop = Math.min(before + amount, target.scrollHeight-target.clientHeight);
                        target.dispatchEvent(new Event('scroll', {bubbles:true}));
                        return target.scrollTop > before;
                    }
                    """,
                    pixel_step,
                )
                page.wait_for_timeout(550)
                after_rows = discover_visual_rows(page, verbose=False)
                after_min, after_max = get_visible_aria_bounds(after_rows)
                moved = (before_min, before_max) != (after_min, after_max)
            except Exception:
                pass

    return {
        "moved": moved,
        "pixel_step": pixel_step,
        "before_min": before_min,
        "before_max": before_max,
        "after_min": after_min,
        "after_max": after_max,
        "rows": after_rows,
    }


def get_row_driver_name(row):
    """Read first/last/display name from the current grid row without opening it."""
    first_name = ""
    name_cell = None

    for cell_info in row.get("cells", []):
        if (
            clean_text(cell_info.get("ariaColIndex")) == "2"
            and clean_text(cell_info.get("text"))
        ):
            name_cell = cell_info
            first_name = clean_text(cell_info.get("text"))
            break

    if not first_name and row.get("texts"):
        first_name = clean_text(row["texts"][0])

    last_name = infer_last_name(row.get("texts", []), first_name)
    display_name = clean_text(
        " ".join(value for value in (first_name, last_name) if value)
    )

    return {
        "first_name": first_name,
        "last_name": last_name,
        "display_name": display_name,
        "aria_rowindex": clean_text(
            (name_cell or {}).get("ariaRowIndex")
        ),
        "aria_colindex": clean_text(
            (name_cell or {}).get("ariaColIndex")
        ),
    }






# ============================================================
# DATABASE
# ============================================================


def get_db_connection():
    return mysql.connector.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
        connection_timeout=15,
    )


def test_database_connection():
    print("\nTesting database connection...")
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute(f"SELECT DRIVER_ID FROM `{DB_TABLE}` LIMIT 1")
        cursor.fetchone()
        print("Database connection successful.")
    finally:
        cursor.close()
        connection.close()


def validate_extended_driver_columns():
    """Validate BigA columns used by Avalon location and Samsara tracking."""
    required_columns = {
        "LOCATION",
        "FOUND_IN_SAMSARA",
        "UPDATED_IN_SAMSARA",
        "SAMSARAID",
        "PAYCHEX_MATCH_SCORE",
        "PAYCHEX_REASON",
        "SAMSARA_MATCH_SCORE",
        "SAMSARA_REASON",
    }

    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        placeholders = ", ".join(["%s"] * len(required_columns))
        cursor.execute(
            f"""
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND COLUMN_NAME IN ({placeholders})
            """,
            (DB_NAME, DB_TABLE, *sorted(required_columns)),
        )
        found = {clean_text(row[0]).upper() for row in cursor.fetchall()}
    finally:
        cursor.close()
        connection.close()

    missing = sorted(required_columns - found)
    if missing:
        raise RuntimeError(
            "Required ADMIN_DRIVER_INFO column(s) are missing: "
            + ", ".join(missing)
            + ". Expected columns: LOCATION, FOUND_IN_SAMSARA, "
              "UPDATED_IN_SAMSARA, SAMSARAID, PAYCHEX_MATCH_SCORE, "
              "PAYCHEX_REASON, SAMSARA_MATCH_SCORE, SAMSARA_REASON."
        )

    print(
        "Extended BigA columns ready: LOCATION, FOUND_IN_SAMSARA, "
        "UPDATED_IN_SAMSARA, SAMSARAID, PAYCHEX_MATCH_SCORE, PAYCHEX_REASON, "
        "SAMSARA_MATCH_SCORE, SAMSARA_REASON."
    )


def validate_paychex_requirements():
    """Validate Tess Paychex API and the BigA employee-ID column."""
    if not PAYCHEX_ENABLED:
        print("Paychex employee-ID API lookup is disabled.")
        return

    missing_endpoints = []
    if not PAYCHEX_API_URL_CO1:
        missing_endpoints.append("PAYCHEX_API_URL_CO1")
    if not PAYCHEX_API_URL_CO2:
        missing_endpoints.append("PAYCHEX_API_URL_CO2")
    if missing_endpoints:
        raise RuntimeError(
            "Paychex/Tess API integration is enabled but endpoint configuration is missing: "
            + ", ".join(missing_endpoints)
        )

    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        cursor.execute(
            """
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND COLUMN_NAME = %s
            LIMIT 1
            """,
            (DB_NAME, DB_TABLE, PAYCHEX_ID_COLUMN),
        )
        row = cursor.fetchone()
    finally:
        cursor.close()
        connection.close()

    if not row:
        raise RuntimeError(
            f"Required Paychex ID column `{PAYCHEX_ID_COLUMN}` does not exist in "
            f"`{DB_TABLE}`. Suggested SQL: ALTER TABLE `{DB_TABLE}` ADD COLUMN "
            f"`{PAYCHEX_ID_COLUMN}` VARCHAR(50) DEFAULT NULL;"
        )

    print(
        "Paychex/Tess API integration ready (dedicated company endpoints; no auth header). "
        f"CO1={PAYCHEX_API_URL_CO1}; CO2={PAYCHEX_API_URL_CO2}; "
        f"method={PAYCHEX_API_METHOD}; employee IDs -> {DB_TABLE}.{PAYCHEX_ID_COLUMN}."
    )


def validate_samsara_requirements():
    """Validate Samsara configuration before browser/database mutation begins."""
    if not SAMSARA_ENABLED:
        print("Samsara driver sync is disabled.")
        return

    if not SAMSARA_API_TOKEN:
        raise RuntimeError(
            "SAMSARA_ENABLED=true but SAMSARA_API_TOKEN is missing. "
            "Create a Samsara API token with Read Drivers and Write Drivers permissions."
        )

    prefix = re.sub(r"[^A-Za-z0-9@._%+\-]", "", SAMSARA_COMPANY_ID_PREFIX)
    if not prefix:
        raise RuntimeError(
            "SAMSARA_COMPANY_ID_PREFIX becomes empty after Samsara external-ID "
            "character validation. Use letters/numbers or @ . _ % + -."
        )

    print(
        "Samsara integration ready. "
        f"PATCH mode={'DRY RUN' if SAMSARA_DRY_RUN else 'LIVE'}; "
        f"verify-after-patch={SAMSARA_VERIFY_PATCH}; "
        f"minimum match score={SAMSARA_MATCH_MIN_SCORE}."
    )
    print(
        "Samsara write configuration: "
        f"SAMSARA_DRY_RUN env={os.environ.get('SAMSARA_DRY_RUN', '<unset>')!r}; "
        f"employee key={SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY!r}; "
        f"company key={SAMSARA_COMPANY_EXTERNAL_ID_KEY!r}; "
        f"company prefix={SAMSARA_COMPANY_ID_PREFIX!r}."
    )


def load_drivers_missing_paychex_employee_id():
    """
    Build the Paychex backlog directly from the database.

    This intentionally includes BOTH:
      - drivers inserted during the current Avalon run, and
      - drivers that already existed before this run.

    Any row in the configured ORG_ID / LOCATION_ID whose Paychex employee ID
    is NULL or blank is eligible for a Paychex lookup.
    """
    if not PAYCHEX_ENABLED:
        return []

    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)
    try:
        cursor.execute(
            f"""
            SELECT
                DRIVER_ID,
                FNAME,
                LNAME,
                EMAIL,
                PHONE,
                DOB,
                DOJ
            FROM `{DB_TABLE}`
            WHERE ORG_ID = %s
              AND LOCATION_ID = %s
              AND (
                    `{PAYCHEX_ID_COLUMN}` IS NULL
                    OR TRIM(`{PAYCHEX_ID_COLUMN}`) = ''
                  )
            ORDER BY DRIVER_ID ASC
            """,
            (ORG_ID, LOCATION_ID),
        )
        rows = cursor.fetchall()
    finally:
        cursor.close()
        connection.close()

    backlog = []
    for row in rows:
        first_name = clean_text(row.get("FNAME"))
        last_name = clean_text(row.get("LNAME"))
        backlog.append(
            {
                "DB Driver ID": row.get("DRIVER_ID"),
                "First Name": first_name,
                "Last Name": last_name,
                "Email": clean_text(row.get("EMAIL")),
                "Phone": clean_text(row.get("PHONE")),
                "DOB": clean_text(row.get("DOB")),
                "DOJ": clean_text(row.get("DOJ")),
                "Display Name": clean_text(f"{first_name} {last_name}"),
                "Paychex Employee ID": None,
                "Paychex Lookup Status": "PENDING",
            }
        )

    print("\n======================================================================")
    print("PAYCHEX DATABASE BACKLOG")
    print(
        f"Rows in {DB_TABLE} with empty {PAYCHEX_ID_COLUMN}: "
        f"{len(backlog)}"
    )
    print(f"Scope: ORG_ID={ORG_ID}, LOCATION_ID={LOCATION_ID}")
    print("======================================================================")

    return backlog


def _compact_match_reason(status, reasons=None, *, context=None, max_length=255):
    """Build a compact audit reason safe for a typical VARCHAR column."""
    parts = [clean_text(status)]
    context = clean_text(context)
    if context:
        parts.append(context)
    reason_items = [clean_text(item) for item in (reasons or []) if clean_text(item)]
    if reason_items:
        parts.append("; ".join(reason_items))
    return " | ".join(part for part in parts if part)[:max_length]


def update_biga_paychex_match_audit(driver_id, score, reason):
    """Persist the latest Paychex matching score/reason for one BigA row."""
    try:
        numeric_score = int(score or 0)
    except (TypeError, ValueError):
        numeric_score = 0
    reason = clean_text(reason)[:255]

    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        cursor.execute(
            f"""
            UPDATE `{DB_TABLE}`
            SET PAYCHEX_MATCH_SCORE = %s,
                PAYCHEX_REASON = %s
            WHERE DRIVER_ID = %s
              AND ORG_ID = %s
              AND LOCATION_ID = %s
            """,
            (numeric_score, reason, driver_id, ORG_ID, LOCATION_ID),
        )
        connection.commit()
        if cursor.rowcount == 0:
            cursor.execute(
                f"""
                SELECT DRIVER_ID
                FROM `{DB_TABLE}`
                WHERE DRIVER_ID = %s
                  AND ORG_ID = %s
                  AND LOCATION_ID = %s
                LIMIT 1
                """,
                (driver_id, ORG_ID, LOCATION_ID),
            )
            if cursor.fetchone() is None:
                raise RuntimeError(
                    f"Could not find BigA DRIVER_ID={driver_id} while saving Paychex match audit."
                )
    except Exception:
        connection.rollback()
        raise
    finally:
        cursor.close()
        connection.close()


def update_paychex_employee_id(driver_id, employee_id):
    """Store a Paychex employee ID on the exact database row."""
    employee_id = clean_text(employee_id)[:50]
    if not employee_id:
        raise ValueError("Empty Paychex employee ID cannot be saved.")

    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        cursor.execute(
            f"""
            UPDATE `{DB_TABLE}`
            SET `{PAYCHEX_ID_COLUMN}` = %s
            WHERE DRIVER_ID = %s
              AND ORG_ID = %s
              AND LOCATION_ID = %s
              AND (
                    `{PAYCHEX_ID_COLUMN}` IS NULL
                    OR TRIM(`{PAYCHEX_ID_COLUMN}`) = ''
                  )
            """,
            (employee_id, driver_id, ORG_ID, LOCATION_ID),
        )
        connection.commit()

        if cursor.rowcount == 0:
            cursor.execute(
                f"""
                SELECT `{PAYCHEX_ID_COLUMN}`
                FROM `{DB_TABLE}`
                WHERE DRIVER_ID = %s
                  AND ORG_ID = %s
                  AND LOCATION_ID = %s
                LIMIT 1
                """,
                (driver_id, ORG_ID, LOCATION_ID),
            )
            row = cursor.fetchone()
            existing_value = clean_text(row[0]) if row else ""
            if existing_value == employee_id:
                return
            raise RuntimeError(
                f"Could not update {PAYCHEX_ID_COLUMN} for DRIVER_ID={driver_id}. "
                f"Current value is {existing_value!r}."
            )
    except Exception:
        connection.rollback()
        raise
    finally:
        cursor.close()
        connection.close()


def build_db_record(record):
    first_name = truncate(record.get("First Name"), 50)
    last_name = truncate(record.get("Last Name"), 50)

    display_name = clean_text(
        " ".join(value for value in (first_name, last_name) if value)
    )

    return {
        "ORG_ID": ORG_ID,
        "LOCATION_ID": LOCATION_ID,
        "FNAME": first_name,
        "MNAME": None,
        "LNAME": last_name,
        "EMAIL": normalize_email(record.get("Email")),
        "DOB": truncate(record.get("DOB"), 50),
        "DOJ": truncate(record.get("DOJ"), 50),
        "PHONE": normalize_phone(record.get("Phone")),
        "MANAGER": truncate(record.get("Manager"), 100),
        "DESIGNATION": truncate(record.get("Designation"), 100),
        "LOCATION": truncate(record.get("Location"), 100),
        "LICENSE_NUMBER": truncate(record.get("License Number"), 100),
        "LICENSE_CLASS": truncate(record.get("License Class"), 10),
        "CDL": truncate(record.get("CDL"), 10),
        "MOTORCYCLE": truncate(record.get("Motorcycle"), 100),
        "COUNTRY": truncate(record.get("Country"), 100),
        "STATE": truncate(record.get("State"), 100),
        "ISSUE_DATE": truncate(record.get("Issue Date"), 100),
        "EXPIRY_DATE": truncate(record.get("Expiry Date"), 100),
        "DISPLAY_NAME": truncate(display_name, 255),
        "FCM_TOKEN": None,
    }


def find_existing_driver(connection, driver):
    sql = f"""
        SELECT DRIVER_ID
        FROM `{DB_TABLE}`
        WHERE ORG_ID = %s
          AND LOCATION_ID = %s
          AND LOWER(TRIM(COALESCE(FNAME, ''))) = LOWER(TRIM(COALESCE(%s, '')))
          AND LOWER(TRIM(COALESCE(LNAME, ''))) = LOWER(TRIM(COALESCE(%s, '')))
          AND TRIM(COALESCE(DOB, '')) = TRIM(COALESCE(%s, ''))
          AND LOWER(TRIM(COALESCE(EMAIL, ''))) = LOWER(TRIM(COALESCE(%s, '')))
        LIMIT 1
    """

    values = (
        driver["ORG_ID"],
        driver["LOCATION_ID"],
        driver["FNAME"],
        driver["LNAME"],
        driver["DOB"],
        driver["EMAIL"],
    )

    cursor = connection.cursor()
    try:
        cursor.execute(sql, values)
        row = cursor.fetchone()
        return row[0] if row else None
    finally:
        cursor.close()


def insert_driver(connection, driver):
    sql = f"""
        INSERT INTO `{DB_TABLE}`
        (
            ORG_ID,
            LOCATION_ID,
            FNAME,
            MNAME,
            LNAME,
            EMAIL,
            DOB,
            DOJ,
            PHONE,
            MANAGER,
            DESIGNATION,
            `LOCATION`,
            LICENSE_NUMBER,
            LICENSE_CLASS,
            CDL,
            MOTORCYCLE,
            COUNTRY,
            STATE,
            ISSUE_DATE,
            EXPIRY_DATE,
            DISPLAY_NAME,
            FCM_TOKEN
        )
        VALUES
        (
            %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s,
            %s, %s
        )
    """

    values = (
        driver["ORG_ID"],
        driver["LOCATION_ID"],
        driver["FNAME"],
        driver["MNAME"],
        driver["LNAME"],
        driver["EMAIL"],
        driver["DOB"],
        driver["DOJ"],
        driver["PHONE"],
        driver["MANAGER"],
        driver["DESIGNATION"],
        driver["LOCATION"],
        driver["LICENSE_NUMBER"],
        driver["LICENSE_CLASS"],
        driver["CDL"],
        driver["MOTORCYCLE"],
        driver["COUNTRY"],
        driver["STATE"],
        driver["ISSUE_DATE"],
        driver["EXPIRY_DATE"],
        driver["DISPLAY_NAME"],
        driver["FCM_TOKEN"],
    )

    cursor = connection.cursor()
    try:
        cursor.execute(sql, values)
        driver_id = cursor.lastrowid
        connection.commit()
        return driver_id
    except Exception:
        connection.rollback()
        raise
    finally:
        cursor.close()



def check_driver_exists(record):
    """Lookup only. Never inserts. Used before expensive full capture."""
    driver = build_db_record(record)

    required = {
        "FNAME": driver["FNAME"],
        "LNAME": driver["LNAME"],
        "DOB": driver["DOB"],
        "EMAIL": driver["EMAIL"],
    }
    missing = [name for name, value in required.items() if not value]

    if missing:
        return {
            "status": "SKIPPED_MISSING_IDENTITY",
            "driver_id": None,
            "missing": missing,
        }

    connection = get_db_connection()
    try:
        existing_id = find_existing_driver(connection, driver)
    finally:
        connection.close()

    if existing_id is not None:
        return {
            "status": "EXISTING_DB",
            "driver_id": existing_id,
            "missing": [],
        }

    return {
        "status": "NEW_CANDIDATE",
        "driver_id": None,
        "missing": [],
    }


def save_driver_if_new(record):
    driver = build_db_record(record)

    required = {
        "FNAME": driver["FNAME"],
        "LNAME": driver["LNAME"],
        "DOB": driver["DOB"],
        "EMAIL": driver["EMAIL"],
    }

    missing = [name for name, value in required.items() if not value]

    if missing:
        print("\n    DATABASE INSERT SKIPPED")
        print("    Missing identity fields: " + ", ".join(missing))
        return {
            "status": "SKIPPED_MISSING_IDENTITY",
            "driver_id": None,
        }

    print("\n    Checking database...")
    print(f"    Driver: {driver['DISPLAY_NAME']}")
    print(f"    DOB: {driver['DOB']}")
    print(f"    Email: {driver['EMAIL']}")

    connection = get_db_connection()

    try:
        existing_id = find_existing_driver(connection, driver)

        if existing_id is not None:
            print("    DUPLICATE FOUND.")
            print(f"    Existing DRIVER_ID: {existing_id}")
            print("    Insert skipped.")
            return {
                "status": "DUPLICATE",
                "driver_id": existing_id,
            }

        driver_id = insert_driver(connection, driver)
        print("    NEW DRIVER INSERTED.")
        print(f"    DRIVER_ID: {driver_id}")

        return {
            "status": "INSERTED",
            "driver_id": driver_id,
        }

    finally:
        connection.close()


# ============================================================
# ATTACHMENT DOWNLOADS
# ============================================================


def download_all_attachments(page, dialog, folder_name):
    downloaded_files = []
    download_errors = []

    if not DOWNLOAD_ATTACHMENTS:
        print("    Attachment download disabled.")
        return {"files": downloaded_files, "errors": download_errors}

    driver_folder = ATTACHMENT_ROOT / folder_name
    driver_folder.mkdir(parents=True, exist_ok=True)

    print("\n    Opening Files section...")

    files_button = first_visible(dialog.get_by_text("Files", exact=True))
    if files_button is None:
        files_button = first_visible(page.get_by_text("Files", exact=True))

    if files_button is None:
        print("    No Files section found.")
        return {"files": downloaded_files, "errors": download_errors}

    files_button.click()
    page.wait_for_timeout(1000)

    action_buttons = dialog.locator('[title="Take Action"]:visible')
    if action_buttons.count() == 0:
        action_buttons = page.locator('[title="Take Action"]:visible')

    attachment_count = action_buttons.count()
    print(f"    Attachments found: {attachment_count}")

    for attachment_index in range(attachment_count):
        print(
            f"        Downloading {attachment_index + 1}/{attachment_count}"
        )

        try:
            action_buttons = dialog.locator('[title="Take Action"]:visible')
            if action_buttons.count() == 0:
                action_buttons = page.locator('[title="Take Action"]:visible')

            if attachment_index >= action_buttons.count():
                raise RuntimeError("Attachment action button disappeared.")

            action_buttons.nth(attachment_index).click()

            download_item = first_visible(
                page.get_by_role(
                    "menuitem",
                    name=re.compile("Download Attachment", re.IGNORECASE),
                )
            )

            if download_item is None:
                raise RuntimeError("Download Attachment menu item not found.")

            with page.expect_download(timeout=60000) as download_info:
                download_item.click()

            download = download_info.value
            original_filename = download.suggested_filename or (
                f"attachment_{attachment_index + 1}"
            )

            filename = (
                f"{attachment_index + 1:02d}_"
                f"{safe_filename(original_filename)}"
            )

            destination = driver_folder / filename
            download.save_as(str(destination))
            downloaded_files.append(str(destination))

            print(f"        Saved: {destination}")

            ok_button = first_visible(page.get_by_role("button", name="OK"))
            if ok_button:
                ok_button.click()

            page.wait_for_timeout(500)

        except Exception as error:
            message = f"Attachment {attachment_index + 1}: {error}"
            download_errors.append(message)
            print(f"        ERROR: {message}")

            try:
                ok_button = first_visible(page.get_by_role("button", name="OK"))
                if ok_button:
                    ok_button.click()
            except Exception:
                pass

            try:
                page.keyboard.press("Escape")
            except Exception:
                pass

            page.wait_for_timeout(500)

    print(f"    Successful downloads: {len(downloaded_files)}")
    if download_errors:
        print(f"    Attachment errors: {len(download_errors)}")

    return {
        "files": downloaded_files,
        "errors": download_errors,
    }


# ============================================================
# CLOSE DRIVER DIALOG
# ============================================================


def close_driver_dialog(page, dialog):
    print("    Closing driver...")

    close_candidates = dialog.locator(
        """
        button[aria-label*="close" i],
        button[title*="close" i],
        button.btn-close,
        button.close,
        button[data-bs-dismiss="modal"],
        .modal-header button
        """
    )

    close_button = first_visible(close_candidates)
    if close_button:
        try:
            close_button.click()
            expect(dialog).to_be_hidden(timeout=5000)
            page.wait_for_timeout(500)
            return
        except Exception:
            pass

    # Latest Codegen showed a blank button at nth(3).
    blank_buttons = page.get_by_role("button").filter(
        has_text=re.compile(r"^\s*$")
    )

    # Prefer latest recording: nth(3).
    if blank_buttons.count() > 3:
        try:
            blank_buttons.nth(3).click()
            expect(dialog).to_be_hidden(timeout=5000)
            page.wait_for_timeout(500)
            return
        except Exception:
            pass

    # Older recording used nth(4); retain as fallback.
    if blank_buttons.count() > 4:
        try:
            blank_buttons.nth(4).click()
            expect(dialog).to_be_hidden(timeout=5000)
            page.wait_for_timeout(500)
            return
        except Exception:
            pass

    page.keyboard.press("Escape")
    expect(dialog).to_be_hidden(timeout=3000)
    page.wait_for_timeout(500)


# ============================================================
# PAGINATION
# ============================================================


def get_grid_signature(page):
    rows = discover_visual_rows(page, verbose=False)
    return tuple(
        "||".join(row.get("texts", []))
        for row in rows[:3]
    )


def click_next_page(page, current_page):
    """Move to the next visible Avalon pager page, then reset the DataTable scroll.

    Do not call Webix getPage()/setPage(). In this Avatar build those paging methods
    can exist on a view even when their internal pager _settings are not initialized.
    The visible pager controls are authoritative and already proved that they move the
    grid correctly. Webix is used only for safe DataTable scrollTo(0,0).
    """
    target_page = int(current_page) + 1
    print(f"\nMoving from page {current_page} to page {target_page}...")
    before_signature = get_grid_signature(page)

    next_button = None

    # 1. Prefer the explicit numeric destination page.
    next_page_number = str(target_page)
    numeric_candidates = page.locator(
        ".webix_pager .webix_pager_item:visible"
    ).filter(has_text=re.compile(rf"^\s*{re.escape(next_page_number)}\s*$"))
    next_button = first_visible(numeric_candidates)

    # 2. Webix pager index attribute fallback (zero-based).
    if next_button is None:
        next_button = first_visible(
            page.locator(
                f'.webix_pager [webix_p_id="{target_page - 1}"]:visible'
            )
        )

    # 3. Pager-scoped next arrow.
    if next_button is None:
        webix_icons = page.locator(".webix_pager .wxi-angle-right:visible")
        if webix_icons.count() > 0:
            icon = webix_icons.last
            parent = icon.locator(
                "xpath=ancestor-or-self::*[self::button or self::a or "
                "contains(@class,'webix_pager_item')][1]"
            )
            next_button = parent if parent.count() > 0 else icon

    # 4. Accessible fallbacks.
    if next_button is None:
        next_button = first_visible(
            page.get_by_role("button", name=re.compile(r"next", re.IGNORECASE))
        )
    if next_button is None:
        next_button = first_visible(page.locator('[aria-label*="next" i]:visible'))
    if next_button is None:
        next_button = first_visible(page.locator('[title*="next" i]:visible'))

    if next_button is None:
        raise RuntimeError(f"Could not find Avalon pager control for page {target_page}.")

    class_name = next_button.get_attribute("class") or ""
    aria_disabled = next_button.get_attribute("aria-disabled") or ""
    if (
        "disabled" in class_name.lower()
        or "webix_disabled" in class_name.lower()
        or aria_disabled.lower() == "true"
    ):
        return False

    print(f"    Clicking pager control for page {target_page}...")
    next_button.click()

    # First prove the pager changed the logical grid contents.
    deadline = time.monotonic() + 25
    changed = False
    while time.monotonic() < deadline:
        page.wait_for_timeout(500)
        try:
            after_signature = get_grid_signature(page)
            if after_signature and after_signature != before_signature:
                changed = True
                break
        except Exception:
            pass

    if not changed:
        raise RuntimeError(
            f"Clicked pager control for page {target_page}, but Avalon grid content did not change."
        )

    # Now reset only the DataTable scroll. Never touch Webix pager APIs.
    reset_grid_to_top(page)
    page.wait_for_timeout(700)
    indexes = _visible_grid_aria_rowindexes(page)
    print(
        f"    Page {target_page} loaded; top aria-rowindex sample after reset: "
        f"{indexes[:12] if indexes else []}"
    )
    return True


# ============================================================
# PASS 1: READ GRID DATA, FILTER CURRENT-MONTH START DATES
# ============================================================


def normalize_header_name(value):
    return re.sub(r"[^a-z0-9]+", " ", clean_text(value).lower()).strip()


def discover_grid_column_map(page):
    """
    Discover the grid columns needed for Pass 1.

    Required business fields:
      First Name
      Last Name
      Start Date -> DOJ
      Email

    Avalon/Webix usually exposes aria-colindex on both header and data cells.
    If a header does not expose aria-colindex, visible header order is used as
    a fallback. First Name also retains the known Avalon fallback of column 2.
    """
    headers = page.evaluate(
        """
        () => {
            function visible(el) {
                if (!el) return false;
                const style = window.getComputedStyle(el);
                const rect = el.getBoundingClientRect();
                return (
                    style.display !== 'none' &&
                    style.visibility !== 'hidden' &&
                    rect.width > 0 &&
                    rect.height > 0
                );
            }

            let elements = Array.from(
                document.querySelectorAll('[role="columnheader"]')
            ).filter(visible);

            if (!elements.length) {
                elements = Array.from(
                    document.querySelectorAll('.webix_hcell')
                ).filter(visible);
            }

            return elements.map((el, index) => ({
                text: (el.innerText || '').trim(),
                ariaColIndex: el.getAttribute('aria-colindex') || String(index + 1)
            }));
        }
        """
    )

    aliases = {
        "first_name": ("first name", "firstname", "first"),
        "last_name": ("last name", "lastname", "last"),
        "doj": ("start date", "startdate", "date started", "hire date"),
        "email": ("email address", "emailaddress", "email", "e mail"),
    }

    column_map = {
        "first_name": "",
        "last_name": "",
        "doj": "",
        "email": "",
    }

    normalized_headers = []
    for header in headers:
        normalized = normalize_header_name(header.get("text"))
        col_index = clean_text(header.get("ariaColIndex"))
        normalized_headers.append((normalized, col_index, clean_text(header.get("text"))))

    for field, names in aliases.items():
        for normalized, col_index, _original in normalized_headers:
            if not normalized:
                continue
            if any(
                normalized == alias
                or normalized.startswith(alias + " ")
                or normalized.endswith(" " + alias)
                or (alias in {"email", "start date"} and alias in normalized)
                for alias in names
            ):
                column_map[field] = col_index
                break

    # Known Avalon result-grid fallback from Codegen/runtime traces.
    if not column_map["first_name"]:
        column_map["first_name"] = "2"

    # Last Name has consistently followed First Name in the current grid.
    if not column_map["last_name"]:
        try:
            column_map["last_name"] = str(int(column_map["first_name"]) + 1)
        except Exception:
            pass

    print("\n    PASS 1 GRID COLUMN MAP")
    for field in ("first_name", "last_name", "doj", "email"):
        print(f"        {field}: aria-colindex={column_map[field] or 'AUTO'}")

    if normalized_headers:
        print("        Visible headers:")
        for normalized, col_index, original in normalized_headers:
            print(f"            col {col_index}: {original or normalized}")

    if not column_map["doj"]:
        print(
            "        WARNING: Start Date header not identified. "
            "Pass 1 will fall back to the first complete date in each row."
        )

    if not column_map["email"]:
        print(
            "        WARNING: Email header not identified. "
            "Pass 1 will fall back to an email-pattern search in each row."
        )

    return column_map


def get_row_cell_text(row, aria_colindex):
    wanted = clean_text(aria_colindex)
    if not wanted:
        return ""

    for cell_info in row.get("cells", []):
        if clean_text(cell_info.get("ariaColIndex")) == wanted:
            return clean_text(cell_info.get("text"))

    return ""


def get_row_cell_info(row, aria_colindex):
    wanted = clean_text(aria_colindex)
    if not wanted:
        return None

    for cell_info in row.get("cells", []):
        if clean_text(cell_info.get("ariaColIndex")) == wanted:
            return cell_info

    return None


def extract_email_from_row(row):
    email_re = re.compile(
        r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"
    )

    for cell_info in row.get("cells", []):
        text = clean_text(cell_info.get("text"))
        match = email_re.search(text)
        if match:
            return match.group(0)

    return ""


def extract_full_date_from_text(value):
    value = clean_text(value)

    patterns = (
        r"(?<!\d)(\d{1,2}/\d{1,2}/\d{4})(?!\d)",
        r"(?<!\d)(\d{4}-\d{1,2}-\d{1,2})(?!\d)",
        r"(?<!\d)(\d{1,2}-\d{1,2}-\d{4})(?!\d)",
    )

    for pattern in patterns:
        match = re.search(pattern, value)
        if match:
            return match.group(1)

    return ""


def extract_start_date_from_row(row):
    for cell_info in row.get("cells", []):
        value = extract_full_date_from_text(cell_info.get("text"))
        if value:
            return value
    return ""


def parse_business_date(value):
    token = extract_full_date_from_text(value) or clean_text(value)
    if not token:
        return None

    for date_format in (
        "%m/%d/%Y",
        "%Y-%m-%d",
        "%m-%d-%Y",
        "%m/%d/%y",
    ):
        try:
            return datetime.strptime(token, date_format)
        except ValueError:
            pass

    return None


def normalize_date_key(value):
    parsed = parse_business_date(value)
    if parsed is not None:
        return parsed.strftime("%Y-%m-%d")
    return clean_text(value).lower()


def normalize_name_key(value):
    return clean_text(value).casefold()


def build_pass1_precheck_key(record):
    return (
        normalize_name_key(record.get("first_name") or record.get("First Name")),
        normalize_name_key(record.get("last_name") or record.get("Last Name")),
        normalize_email(record.get("email") or record.get("Email")) or "",
        normalize_date_key(record.get("doj") or record.get("DOJ")),
    )


def extract_pass1_row_fields(row, column_map):
    """Read First Name, Last Name, Start Date/DOJ and Email from one grid row."""
    first_name = get_row_cell_text(row, column_map.get("first_name"))
    if not first_name:
        first_name = get_row_driver_name(row)["first_name"]

    last_name = get_row_cell_text(row, column_map.get("last_name"))
    if not last_name:
        last_name = infer_last_name(row.get("texts", []), first_name)

    email = get_row_cell_text(row, column_map.get("email"))
    email_match = re.search(
        r"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}",
        email,
    )
    if email_match:
        email = email_match.group(0)
    else:
        email = extract_email_from_row(row)

    doj = get_row_cell_text(row, column_map.get("doj"))
    complete_doj = extract_full_date_from_text(doj)
    if complete_doj:
        doj = complete_doj
    else:
        doj = extract_start_date_from_row(row)

    display_name = clean_text(
        " ".join(value for value in (first_name, last_name) if value)
    )

    name_cell = get_row_cell_info(row, column_map.get("first_name"))
    if name_cell is None:
        for cell_info in row.get("cells", []):
            if clean_text(cell_info.get("text")) == first_name:
                name_cell = cell_info
                break

    result = {
        "first_name": clean_text(first_name),
        "last_name": clean_text(last_name),
        "display_name": display_name,
        "email": clean_text(email),
        "doj": clean_text(doj),
        "aria_rowindex": clean_text((name_cell or {}).get("ariaRowIndex")),
        "aria_colindex": clean_text((name_cell or {}).get("ariaColIndex")),
        "row_texts": list(row.get("texts", [])),
    }
    result["precheck_key"] = build_pass1_precheck_key(result)
    return result


def pass1_record_matches_target_month(record):
    """Strict current month AND year comparison using the grid Start Date."""
    parsed = parse_business_date(record.get("doj"))
    if parsed is None:
        return False, "unparseable-start-date"

    target_year, target_month = get_target_year_month()
    return (
        parsed.year == target_year and parsed.month == target_month,
        "full-start-date",
    )


def collect_full_saved_view_inventory(page, expected_count, view_label):
    """Enumerate every logical row in an Avalon saved view using the virtual grid.

    Used by the reverse Paychex check. For this tenant the New Hire Saved View is
    treated as the active-driver roster. Unlike the forward current-month pass, this
    function has no date boundary: every reported New Hire row on every page is collected.
    Only grid fields are read; no driver dialogs are opened.
    """
    if expected_count <= 0:
        print(f"Avatar saved view {view_label!r} contains no rows.")
        return [], {}

    records = []
    total_pages = (expected_count + AVALON_PAGE_SIZE - 1) // AVALON_PAGE_SIZE
    column_map = discover_grid_column_map(page)

    print("\n======================================================================")
    print("AVATAR NEW HIRE INVENTORY - FULL ACTIVE-ROSTER SCAN")
    print(f"Reverse source view: {view_label}")
    print(f"Rows reported: {expected_count}")
    print("Fields: First Name / Last Name / Start Date (DOJ) / Email")
    print("No driver dialogs will be opened during this scan.")
    print("======================================================================")

    for page_number in range(1, total_pages + 1):
        expected_this_page = min(
            AVALON_PAGE_SIZE,
            max(expected_count - ((page_number - 1) * AVALON_PAGE_SIZE), 0),
        )

        # IMPORTANT: do not carry a virtual-scroll position from the page that was
        # just scanned. Reopen New Hire fresh before every page after page 1, then
        # navigate from a clean page-1/top state to the requested page WITHOUT
        # scrolling any intermediate page. This avoids the Webix recycled-DOM issue
        # where page 2 initially exposed only aria-rowindex 92..100.
        if page_number > 1:
            print(
                f"\n    Reinitializing New Hire before full-scan page {page_number} "
                "to clear prior Webix virtual-scroll state..."
            )
            open_saved_views(page)
            refreshed_count = open_new_hire_and_get_count(page)
            if int(refreshed_count) != int(expected_count):
                raise RuntimeError(
                    "New Hire count changed during the full reverse scan. "
                    f"Expected {expected_count}, refreshed count={refreshed_count}. "
                    "Reverse reconciliation stopped to avoid mixing two roster snapshots."
                )
            column_map = discover_grid_column_map(page)
            reset_grid_to_top(page)

            # Navigate from fresh page 1 to the requested page. Because no data-page
            # scrolling occurs between these hops, each pager transition starts at top.
            for hop_page in range(1, page_number):
                moved = click_next_page(page, hop_page)
                if not moved:
                    raise RuntimeError(
                        f"Could not navigate clean New Hire grid from page {hop_page} "
                        f"to page {hop_page + 1} while preparing reverse-scan page {page_number}."
                    )
                page.wait_for_timeout(500)

        page_records = {}
        aria_indexes_seen = set()
        reset_grid_to_top(page)
        page.wait_for_timeout(700)

        print(
            f"\n    NEW HIRE FULL-SCAN PAGE {page_number}/{total_pages}: "
            f"expecting {expected_this_page} logical row(s)"
        )

        def harvest_current_view():
            visual_rows = discover_visual_rows(page, verbose=False)
            added = 0
            for row in visual_rows:
                identity = get_row_identity(page_number, row)
                if identity in page_records:
                    continue
                aria_index = get_row_aria_index(row)
                if aria_index is not None and aria_index in aria_indexes_seen:
                    continue

                fields = extract_pass1_row_fields(row, column_map)
                if not fields.get("first_name"):
                    continue
                if aria_index is not None:
                    aria_indexes_seen.add(aria_index)

                record = {
                    "record_number": len(records) + len(page_records) + 1,
                    "page_number": page_number,
                    "page_position": len(page_records) + 1,
                    "row_identity": identity,
                    **fields,
                }
                page_records[identity] = record
                added += 1
            return visual_rows, added

        no_progress = 0
        scan_steps = 0
        max_scan_steps = max(160, expected_this_page * 12)

        while len(page_records) < expected_this_page and scan_steps < max_scan_steps:
            scan_steps += 1
            visual_rows, added = harvest_current_view()
            if len(page_records) >= expected_this_page:
                break

            result = scroll_grid_down_dense(
                page, visual_rows, rows_per_step=max(1, PASS1_SCROLL_ROWS)
            )
            if added > 0 or result.get("moved"):
                no_progress = 0
            else:
                no_progress += 1
            if no_progress >= 20:
                break

        if len(page_records) < expected_this_page:
            print(
                f"    Recovery sweep: observed {len(page_records)}/{expected_this_page} rows"
            )
            reset_grid_to_top(page)
            page.wait_for_timeout(700)
            no_progress = 0
            recovery_steps = 0
            while (
                len(page_records) < expected_this_page
                and recovery_steps < max(240, expected_this_page * 18)
            ):
                recovery_steps += 1
                visual_rows, added = harvest_current_view()
                if len(page_records) >= expected_this_page:
                    break
                result = scroll_grid_down_dense(
                    page,
                    visual_rows,
                    rows_per_step=max(1, PASS1_RECOVERY_SCROLL_ROWS),
                )
                if added > 0 or result.get("moved"):
                    no_progress = 0
                else:
                    no_progress += 1
                if no_progress >= 35:
                    break

        if len(page_records) != expected_this_page:
            observed = sorted(aria_indexes_seen)
            webix_state = _webix_datatable_state(page)
            raise RuntimeError(
                f"Avatar New Hire reverse-source view {view_label!r} page {page_number} could not be fully "
                f"enumerated. Expected {expected_this_page}, observed {len(page_records)}. "
                f"Observed aria-rowindex sample={observed[:30]!r}. "
                f"Webix state={webix_state!r}."
            )

        page_rows = list(page_records.values())
        records.extend(page_rows)
        print(
            f"    New Hire page {page_number} complete: {len(page_rows)} row(s); "
            f"cumulative={len(records)}/{expected_count}"
        )

        # Do not advance from the bottom of this page. The next loop iteration
        # reopens New Hire fresh and navigates to the next page from a clean top state.

    # Full-list safety gate. The reverse check must never run against a partial
    # active-driver population because that could create misleading termination
    # conclusions. This scan is intentionally independent of TARGET_MONTH.
    if len(records) != expected_count:
        raise RuntimeError(
            f"Reverse-check full New Hire roster scan incomplete for {view_label!r}. "
            f"Avatar New Hire reported {expected_count} driver row(s), but only "
            f"{len(records)} unique row(s) were captured. Reverse reconciliation "
            "will not run on a partial list."
        )

    print("\n======================================================================")
    print("AVATAR NEW HIRE INVENTORY COMPLETE - FULL ACTIVE ROSTER")
    print(f"Reverse source view: {view_label}")
    print("Scope: ALL rows in New Hire across ALL pages; NO current-month/date filter")
    print(f"Rows reported: {expected_count}")
    print(f"Rows captured: {len(records)}")
    print("Full-list validation: PASS")
    print("======================================================================")
    return records, column_map


def collect_driver_inventory(page, expected_count):
    """
    PASS 1 - SORT-AWARE CURRENT-MONTH INVENTORY.

    Avalon is expected to be sorted by Start Date descending (latest first).
    Read only grid rows; never open a driver dialog here.

    For each rendered logical row capture:
      First Name / Last Name / Start Date -> DOJ / Email

    Rules:
      * DOJ in target month/year  -> add to current_month_drivers
      * DOJ newer than target month -> ignore for shortlist, continue downward
      * DOJ older than target month -> STOP PASS 1 immediately
      * blank/unparseable DOJ -> keep scanning; never use it as the stop boundary

    This means a 369-row New Hire view may require reading only the first handful
    of rows when current-month hires are at the top of a descending Start Date sort.
    """
    all_drivers = []
    current_month_drivers = []
    total_scanned = 0
    total_pages = (expected_count + AVALON_PAGE_SIZE - 1) // AVALON_PAGE_SIZE
    column_map = discover_grid_column_map(page)

    # Early stopping is safe only when we positively identified Avalon's
    # Start Date column. Falling back to an arbitrary date in the row could
    # stop the scan too early, so fail fast instead.
    if not column_map.get("doj"):
        raise RuntimeError(
            "Cannot use sorted Start Date early-stop logic because the Start Date "
            "column was not identified. Check the PASS 1 GRID COLUMN MAP output."
        )

    month_start, next_month = get_target_month_bounds()

    print("\n======================================================================")
    print("PASS 1 - SORTED CURRENT-MONTH GRID INVENTORY")
    print("Fields: First Name / Last Name / Start Date (DOJ) / Email")
    print(f"TOTAL NEW HIRE ROWS REPORTED: {expected_count}")
    print(f"TARGET START-DATE MONTH: {get_target_month_label()}")
    print(f"MONTH START: {month_start.strftime('%m/%d/%Y')}")
    print(f"STOP WHEN DOJ < {month_start.strftime('%m/%d/%Y')}")
    print("ASSUMPTION: grid Start Date is sorted newest -> oldest")
    print(f"DENSE SCROLL STEP: {PASS1_SCROLL_ROWS} row(s)")
    print("======================================================================")

    boundary_reached = False
    boundary_record = None
    pages_touched = 0

    for page_number in range(1, total_pages + 1):
        if boundary_reached:
            break

        pages_touched += 1
        expected_this_page = min(
            AVALON_PAGE_SIZE,
            max(expected_count - ((page_number - 1) * AVALON_PAGE_SIZE), 0),
        )
        page_records = {}
        aria_indexes_seen = set()

        reset_grid_to_top(page)
        page.wait_for_timeout(700)

        print(
            f"\n    PASS 1 PAGE {page_number}/{total_pages}: "
            "scan only until the first Start Date older than target month"
        )

        def harvest_current_view():
            nonlocal boundary_reached, boundary_record, total_scanned

            visual_rows = discover_visual_rows(page, verbose=False)
            added = 0

            # discover_visual_rows() is visual top-to-bottom order, which matches
            # the descending Start Date sort the user configured in Avalon.
            for row in visual_rows:
                if boundary_reached:
                    break

                identity = get_row_identity(page_number, row)
                if identity in page_records:
                    continue

                aria_index = get_row_aria_index(row)
                if aria_index is not None and aria_index in aria_indexes_seen:
                    continue

                fields = extract_pass1_row_fields(row, column_map)
                if not fields["first_name"]:
                    continue

                if aria_index is not None:
                    aria_indexes_seen.add(aria_index)

                total_scanned += 1
                page_position = len(page_records) + 1

                record = {
                    "record_number": total_scanned,
                    "page_number": page_number,
                    "page_position": page_position,
                    "row_identity": identity,
                    **fields,
                }

                date_class, parsed_doj = classify_start_date(record.get("doj"))
                record["start_date_class"] = date_class
                record["current_month"] = date_class == "current"
                record["month_match_basis"] = (
                    "full-start-date" if parsed_doj is not None else "unparseable-start-date"
                )

                page_records[identity] = record
                all_drivers.append(record)
                added += 1

                if date_class == "current":
                    current_month_drivers.append(record)
                    status = "CURRENT MONTH -> shortlist"
                elif date_class == "future":
                    status = "FUTURE -> ignore, keep scanning"
                elif date_class == "older":
                    status = "OLDER -> STOP HERE"
                else:
                    status = "INVALID DOJ -> keep scanning"

                print(
                    f"        P{page_number} #{page_position:03d} "
                    f"aria={aria_index if aria_index is not None else '-'} | "
                    f"{record['display_name']} | "
                    f"DOJ={record['doj'] or '<missing>'} | "
                    f"Email={record['email'] or '<missing>'} | {status}"
                )

                if date_class == "older":
                    boundary_reached = True
                    boundary_record = record
                    print("\n    **************************************************************")
                    print("    CURRENT-MONTH BOUNDARY REACHED")
                    print(
                        f"    First older row: {record['display_name']} | "
                        f"DOJ={record['doj']}"
                    )
                    print(
                        "    Grid is sorted newest first, so rows below this point "
                        "will not be read."
                    )
                    print("    **************************************************************")
                    break

            return visual_rows, added

        no_progress = 0
        scan_steps = 0
        max_scan_steps = max(120, expected_this_page * 10)

        while (
            not boundary_reached
            and len(page_records) < expected_this_page
            and scan_steps < max_scan_steps
        ):
            scan_steps += 1
            visual_rows, added = harvest_current_view()

            if boundary_reached or len(page_records) >= expected_this_page:
                break

            vmin, vmax = get_visible_aria_bounds(visual_rows)
            print(
                f"    Page {page_number}: scanned {len(page_records)} row(s) | "
                f"current-month {sum(1 for r in page_records.values() if r['current_month'])} | "
                f"visible aria={vmin}-{vmax}"
            )

            result = scroll_grid_down_dense(
                page,
                visual_rows,
                rows_per_step=max(1, PASS1_SCROLL_ROWS),
            )

            if added > 0 or result.get("moved"):
                no_progress = 0
            else:
                no_progress += 1

            if no_progress >= 16:
                break

        # If the boundary was found, leaving the page early is intentional.
        if boundary_reached:
            break

        # If the whole logical page is newer/current and we did not collect all
        # expected rows, do a one-row-overlap recovery sweep before moving Next.
        if len(page_records) < expected_this_page:
            print(
                f"\n    RECOVERY SWEEP PAGE {page_number}: "
                f"{len(page_records)}/{expected_this_page} rows observed and no old-month boundary yet"
            )
            reset_grid_to_top(page)
            page.wait_for_timeout(700)
            no_progress = 0
            recovery_steps = 0

            while (
                not boundary_reached
                and len(page_records) < expected_this_page
                and recovery_steps < max(200, expected_this_page * 15)
            ):
                recovery_steps += 1
                visual_rows, added = harvest_current_view()
                if boundary_reached or len(page_records) >= expected_this_page:
                    break

                result = scroll_grid_down_dense(
                    page,
                    visual_rows,
                    rows_per_step=max(1, PASS1_RECOVERY_SCROLL_ROWS),
                )

                if added > 0 or result.get("moved"):
                    no_progress = 0
                else:
                    no_progress += 1

                if no_progress >= 30:
                    break

        if boundary_reached:
            break

        if len(page_records) != expected_this_page:
            observed = sorted(aria_indexes_seen)
            hint = ""
            if observed:
                expected_range = range(observed[0], observed[0] + expected_this_page)
                missing = [i for i in expected_range if i not in aria_indexes_seen]
                if missing:
                    hint = f" Missing aria-rowindex values (first 30): {missing[:30]}."

            raise RuntimeError(
                f"Pass 1 did not reach an older Start Date boundary on page {page_number}, "
                f"so the full logical page must be enumerated before clicking Next. "
                f"Expected {expected_this_page} rows but observed {len(page_records)}.{hint}"
            )

        print("\n    --------------------------------------------------------------")
        print(f"    PAGE {page_number} SCAN COMPLETE WITHOUT OLD-MONTH BOUNDARY")
        print(f"    Rows observed: {len(page_records)}")
        print(
            f"    Current-month rows on page: "
            f"{sum(1 for r in page_records.values() if r['current_month'])}"
        )
        print("    --------------------------------------------------------------")

        if page_number < total_pages:
            moved = click_next_page(page, page_number)
            if not moved:
                raise RuntimeError(
                    "Pass 1 expected another Avalon page before reaching the "
                    "older Start Date boundary, but Next is disabled."
                )
            page.wait_for_timeout(1000)

    print("\n======================================================================")
    print("PASS 1 COMPLETE - SORT-AWARE EARLY STOP")
    print(f"New Hire rows reported by Avalon: {expected_count}")
    print(f"Rows actually scanned before stop: {len(all_drivers)}")
    print(f"Pagination pages touched: {pages_touched}")
    print(
        f"Current-month Start Date ({get_target_month_label()}): "
        f"{len(current_month_drivers)}"
    )

    if boundary_record is not None:
        print(
            f"Stopped at first older DOJ: {boundary_record['doj']} "
            f"({boundary_record['display_name']})"
        )
    else:
        print(
            "No older Start Date boundary was encountered before the scanned data ended."
        )

    print("======================================================================")

    if PRINT_DRIVER_LIST:
        print("\ncurrent_month_driver_list = [")
        for item in current_month_drivers:
            print(
                "    {"
                f"'First Name': {item['first_name']!r}, "
                f"'Last Name': {item['last_name']!r}, "
                f"'DOJ': {item['doj']!r}, "
                f"'Email': {item['email']!r}, "
                f"'Page': {item['page_number']!r}, "
                f"'Aria Row': {item.get('aria_rowindex')!r}"
                "},"
            )
        print("]")

    return all_drivers, current_month_drivers, column_map


# ============================================================
# PASS 2A: VALIDATE CURRENT-MONTH GRID LIST AGAINST DATABASE
# ============================================================


def load_existing_driver_precheck_map():
    """
    Read the existing table once and build a lookup using the four Pass 1
    fields, scoped by ORG_ID and LOCATION_ID.

    Precheck identity:
      FNAME + LNAME + EMAIL + DOJ

    The final insert path still performs the stronger historical duplicate
    check using FNAME + LNAME + DOB + EMAIL.
    """
    connection = get_db_connection()
    cursor = connection.cursor()

    try:
        cursor.execute(
            f"""
            SELECT DRIVER_ID, FNAME, LNAME, EMAIL, DOJ
            FROM `{DB_TABLE}`
            WHERE ORG_ID = %s
              AND LOCATION_ID = %s
            """,
            (ORG_ID, LOCATION_ID),
        )
        rows = cursor.fetchall()
    finally:
        cursor.close()
        connection.close()

    existing = {}

    for driver_id, fname, lname, email, doj in rows:
        record = {
            "first_name": fname,
            "last_name": lname,
            "email": email,
            "doj": doj,
        }
        key = build_pass1_precheck_key(record)

        # Only use a complete business key for early skipping. Incomplete DB
        # records are ignored here so they cannot cause a false skip.
        if all(key):
            existing.setdefault(key, driver_id)

    print(
        f"\n    Loaded {len(existing)} complete existing driver keys "
        f"from {DB_TABLE}."
    )
    return existing


def prevalidate_current_month_drivers(current_month_drivers):
    """
    PASS 2A - STRICT DATABASE GATE.

    No Avalon driver dialog may be opened before this function finishes.

    Validation identity comes entirely from the grid-only Pass 1 fields:
      First Name + Last Name + Email + DOJ

    Outcomes:
      * existing_drivers: exact key exists in ADMIN_DRIVER_INFO -> never open
      * new_candidates: complete key does NOT exist -> allowed to open later
      * incomplete_prechecks: one or more key fields missing -> never open

    This guarantees that process_new_candidates() receives only drivers that
    have first been positively checked against the database and found absent.
    """
    print("\n======================================================================")
    print("PASS 2A - STRICT DATABASE GATE BEFORE ANY DRIVER DIALOG")
    print(f"Current-month drivers to validate: {len(current_month_drivers)}")
    print("Precheck identity: First Name + Last Name + Email + DOJ")
    print("RULE: only DB-NOT-FOUND records may enter Pass 2B")
    print("======================================================================")

    existing_map = load_existing_driver_precheck_map()
    existing_drivers = []
    new_candidates = []
    incomplete_prechecks = []

    for record in current_month_drivers:
        key = build_pass1_precheck_key(record)
        missing = []
        if not key[0]:
            missing.append("First Name")
        if not key[1]:
            missing.append("Last Name")
        if not key[2]:
            missing.append("Email")
        if not key[3]:
            missing.append("DOJ")

        if missing:
            # Strict gate: an incomplete grid identity cannot prove that the
            # driver is absent from the database, so DO NOT open the dialog.
            record["db_precheck_status"] = "PRECHECK_INCOMPLETE_SKIPPED"
            record["db_precheck_driver_id"] = None
            record["db_precheck_missing"] = missing
            incomplete_prechecks.append(record)
            print(
                f"    SKIP - PRECHECK INCOMPLETE: {record['display_name']} | "
                f"missing={', '.join(missing)} | DIALOG WILL NOT OPEN"
            )
            continue

        existing_id = existing_map.get(key)

        if existing_id is not None:
            record["db_precheck_status"] = "EXISTING_DB_SKIPPED"
            record["db_precheck_driver_id"] = existing_id
            existing_drivers.append(record)
            print(
                f"    SKIP EXISTING: {record['display_name']} | "
                f"DOJ={record['doj']} | Email={record['email']} | "
                f"DRIVER_ID={existing_id} | DIALOG WILL NOT OPEN"
            )
            continue

        # Only this branch is permitted to reach Pass 2B / open_visual_row().
        record["db_precheck_status"] = "DB_NOT_FOUND_APPROVED_FOR_DIALOG"
        record["db_precheck_driver_id"] = None
        new_candidates.append(record)
        print(
            f"    DB NOT FOUND -> APPROVED FOR FULL PROCESS: "
            f"{record['display_name']} | DOJ={record['doj']} | "
            f"Email={record['email']}"
        )

    print("\n======================================================================")
    print("STRICT DATABASE GATE COMPLETE")
    print(f"Current-month list: {len(current_month_drivers)}")
    print(f"Already in DB - dialog blocked: {len(existing_drivers)}")
    print(f"Incomplete precheck - dialog blocked: {len(incomplete_prechecks)}")
    print(f"DB not found - allowed to open dialog: {len(new_candidates)}")
    print("======================================================================")

    return existing_drivers, new_candidates, incomplete_prechecks


def restart_new_hire_at_first_page(page, expected_count):
    """Return to a clean first page before processing new candidates."""
    print("\nRestarting New Hire view at page 1 for new-driver processing...")
    open_saved_views(page)
    refreshed_count = open_new_hire_and_get_count(page)

    if refreshed_count != expected_count:
        raise RuntimeError(
            "New Hire count changed after Pass 1: "
            f"was {expected_count}, now {refreshed_count}. "
            "Restart the run so the candidate list stays aligned."
        )

    reset_grid_to_top(page)


# ============================================================
# PASS 2B: OPEN/FULLY PROCESS ONLY DATABASE-NEW CANDIDATES
# ============================================================


def process_new_candidate_driver(page, row, candidate, process_number):
    """Open and fully process exactly one candidate that passed DB precheck."""
    row_texts = row.get("texts", [])

    record = {
        "Record Number": candidate.get("record_number"),
        "Process Number": process_number,
        "Page Number": candidate.get("page_number"),
        "Result Row": " | ".join(row_texts),
        "First Name": candidate.get("first_name", ""),
        "Last Name": candidate.get("last_name", ""),
        "Email": candidate.get("email", ""),
        "DOJ": candidate.get("doj", ""),
        "DB Precheck Status": candidate.get("db_precheck_status", ""),
        "DB Status": "",
        "DB Driver ID": None,
        "Attachment Count": 0,
        "Attachments": [],
        "Attachment Errors": [],
    }

    scroll_state = get_scroll_container_state(page)
    saved_scroll_top = scroll_state.get("top", 0)
    dialog = None

    try:
        clicked_first_name = open_visual_row(page, row)
        dialog = get_driver_dialog(page)

        # This is now the FIRST time any candidate dialog is opened. Because
        # the driver has already passed month and DB gates, capture the full
        # latest Codegen sequence in one pass.
        captured = capture_driver_information(
            page,
            dialog,
            clicked_first_name,
            row_texts,
        )
        record.update(captured)

        # Pass 1 grid fields are authoritative for the requested business
        # filter and should not be replaced by similarly positioned dialog
        # values.
        record["First Name"] = candidate.get("first_name", "") or record.get("First Name", "")
        record["Last Name"] = candidate.get("last_name", "") or record.get("Last Name", "")
        record["Email"] = candidate.get("email", "") or record.get("Email", "")
        record["DOJ"] = candidate.get("doj", "") or record.get("DOJ", "")

        # Final duplicate-safe check uses the established stronger identity:
        # First Name + Last Name + DOB + Email. This catches races and any
        # historical row whose DOJ formatting prevented the Pass 2A match.
        db_result = save_driver_if_new(record)
        record["DB Status"] = db_result["status"]
        record["DB Driver ID"] = db_result["driver_id"]

        if db_result["status"] == "INSERTED":
            display_name = clean_text(
                " ".join(
                    value
                    for value in (
                        record.get("First Name"),
                        record.get("Last Name"),
                    )
                    if value
                )
            )
            folder_identifier = record.get("DB Driver ID") or process_number
            folder_name = f"{folder_identifier}_{safe_filename(display_name)}"

            # TEST MODE: attachment download intentionally commented out while
            # Samsara matching/PATCH behavior is validated. Restore later by
            # uncommenting the download_all_attachments block.
            # attachment_result = download_all_attachments(
            #     page,
            #     dialog,
            #     folder_name,
            # )
            # record["Attachments"] = attachment_result["files"]
            # record["Attachment Count"] = len(attachment_result["files"])
            # record["Attachment Errors"] = attachment_result["errors"]
            print("    TEST MODE: attachment download skipped.")
        else:
            print(
                f"    Attachments skipped because final DB status is "
                f"{db_result['status']}."
            )

        close_driver_dialog(page, dialog)
        dialog = None
        print("\n    NEW-CANDIDATE PROCESS COMPLETE")

    except Exception as error:
        record["Process Error"] = str(error)
        print(f"\n    DRIVER ERROR: {error}")

        screenshot = RUN_ROOT / f"error_new_candidate_{process_number}.png"
        try:
            page.screenshot(path=str(screenshot), full_page=True)
            record["Error Screenshot"] = str(screenshot)
        except Exception:
            pass

        try:
            if dialog is not None:
                close_driver_dialog(page, dialog)
                dialog = None
            else:
                dialogs = page.locator('[role="dialog"]:visible')
                if dialogs.count() > 0:
                    close_driver_dialog(page, dialogs.last)
        except Exception:
            try:
                page.keyboard.press("Escape")
            except Exception:
                pass

    finally:
        try:
            set_grid_scroll_position(page, saved_scroll_top)
        except Exception:
            pass

    return record


def process_new_candidates(page, expected_count, new_candidates, column_map):
    """
    PASS 2B.

    Traverse only as far as needed through pagination. Rows that were filtered
    out by month, found in the database, or had an incomplete DB precheck are
    NEVER opened. new_candidates contains only records whose complete Pass 1
    identity was checked against the database and returned NOT FOUND.
    """
    if not new_candidates:
        print("\nNo new current-month drivers require full Avalon processing.")
        return []

    candidates_by_page = {}
    for candidate in new_candidates:
        candidates_by_page.setdefault(candidate["page_number"], []).append(candidate)

    last_candidate_page = max(candidates_by_page)
    results = []
    process_number = 0

    print("\n======================================================================")
    print("PASS 2B - OPEN DIALOG ONLY FOR DB-NOT-FOUND DRIVERS")
    print(f"New candidates: {len(new_candidates)}")
    print(f"Last pagination page needed: {last_candidate_page}")
    print("======================================================================")

    for page_number in range(1, last_candidate_page + 1):
        page_candidates = candidates_by_page.get(page_number, [])

        if not page_candidates:
            print(
                f"\n    Page {page_number}: no new candidates; "
                "skipping all driver dialogs."
            )
        else:
            reset_grid_to_top(page)
            pending_by_key = {}
            for candidate in page_candidates:
                pending_by_key.setdefault(candidate["precheck_key"], []).append(candidate)

            processed_candidate_ids = set()
            stagnant_scrolls = 0
            max_stagnant_scrolls = 8

            print("\n######################################################################")
            print(
                f"PROCESSING CANDIDATE PAGE {page_number}: "
                f"{len(page_candidates)} new driver(s)"
            )
            print("######################################################################")

            while len(processed_candidate_ids) < len(page_candidates):
                visual_rows = discover_visual_rows(page, verbose=False)
                processed_one = False

                for row in visual_rows:
                    preview = extract_pass1_row_fields(row, column_map)
                    row_key = preview["precheck_key"]
                    queue = pending_by_key.get(row_key, [])

                    while queue and queue[0]["record_number"] in processed_candidate_ids:
                        queue.pop(0)

                    if not queue:
                        continue

                    candidate = queue.pop(0)
                    process_number += 1

                    print("\n======================================================================")
                    print(
                        f"FULL PROCESS {process_number} OF {len(new_candidates)}: "
                        f"{candidate['display_name']}"
                    )
                    print(
                        f"DOJ={candidate['doj']} | Email={candidate['email']} | "
                        f"Page={page_number}"
                    )
                    print("======================================================================")

                    record = process_new_candidate_driver(
                        page,
                        row,
                        candidate,
                        process_number,
                    )
                    results.append(record)
                    processed_candidate_ids.add(candidate["record_number"])
                    stagnant_scrolls = 0
                    processed_one = True
                    break

                if processed_one:
                    # Full processing may redraw/recycle the virtual grid.
                    # Re-discover from the restored scroll position.
                    continue

                scroll_result = scroll_grid_down_dense(
                    page, visual_rows, rows_per_step=max(1, PASS1_SCROLL_ROWS)
                )
                print(
                    f"    Candidate search dense scroll: "
                    f"aria {scroll_result.get('before_min')}..{scroll_result.get('before_max')} "
                    f"-> {scroll_result.get('after_min')}..{scroll_result.get('after_max')}"
                )

                before_pending = len(processed_candidate_ids)
                after_rows = discover_visual_rows(page, verbose=False)
                has_pending_visible = any(
                    bool(pending_by_key.get(extract_pass1_row_fields(row, column_map)["precheck_key"]))
                    for row in after_rows
                )

                if has_pending_visible or scroll_result.get("moved"):
                    stagnant_scrolls = 0
                else:
                    stagnant_scrolls += 1
                    print(
                        f"    No pending candidate found after scroll "
                        f"{stagnant_scrolls}/{max_stagnant_scrolls}"
                    )

                _ = before_pending

                if stagnant_scrolls >= max_stagnant_scrolls:
                    missing = [
                        item["display_name"]
                        for item in page_candidates
                        if item["record_number"] not in processed_candidate_ids
                    ]
                    raise RuntimeError(
                        f"Could not locate all Pass 1 candidates on page {page_number}. "
                        f"Still missing: {missing}"
                    )

            print(
                f"\n    Candidate page {page_number} complete: "
                f"{len(processed_candidate_ids)}/{len(page_candidates)}"
            )

        if page_number < last_candidate_page:
            moved = click_next_page(page, page_number)
            if not moved:
                raise RuntimeError(
                    "Candidate processing expected another Avalon page, "
                    "but Next is disabled."
                )
            page.wait_for_timeout(1200)

    return results


# ============================================================
# LOGOUT
# ============================================================


def logout(page):
    print("\nLogging out...")

    try:
        page.get_by_title("Murali Mantravadi").get_by_role("button").click()
        page.get_by_role(
            "menuitem",
            name=re.compile("Logout", re.IGNORECASE),
        ).click()
        print("Logged out.")
    except Exception as error:
        print(f"Logout warning: {error}")


# ============================================================
# LEGACY PAYCHEX PLAYWRIGHT EMPLOYEE-ID LOOKUP - DISABLED
# ============================================================
# The browser implementation below is retained only as reference/fallback.
# run() DOES NOT invoke it. The active Paychex integration is the Tess API
# implementation in the next section.


def _paychex_visible(locator):
    try:
        return locator.count() > 0 and locator.first.is_visible()
    except Exception:
        return False


def _get_paychex_mfa_code():
    if PAYCHEX_MFA_CODE:
        return PAYCHEX_MFA_CODE

    code = getpass.getpass(
        "Enter the current Paychex Verification Code (input hidden): "
    ).strip()
    if not code:
        raise RuntimeError("Paychex verification code was required but not supplied.")
    return code


def login_to_paychex(home_page):
    """Follow the supplied Paychex login flow and return the authenticated popup page."""
    print("\nOpening Paychex...")
    home_page.goto(PAYCHEX_HOME_URL, wait_until="domcontentloaded")

    login_link = home_page.get_by_label("main navigation").get_by_role(
        "link", name="Login"
    )
    expect(login_link).to_be_visible(timeout=20000)
    login_link.click()

    learn_links = home_page.get_by_role(
        "link", name=re.compile(r"Learn more about this link", re.IGNORECASE)
    )
    expect(learn_links.first).to_be_visible(timeout=20000)

    link_to_click = learn_links.nth(1) if learn_links.count() > 1 else learn_links.first

    with home_page.expect_popup(timeout=30000) as popup_info:
        link_to_click.click()

    paychex_page = popup_info.value
    paychex_page.set_default_timeout(20000)
    paychex_page.wait_for_load_state("domcontentloaded")

    username = paychex_page.get_by_role("textbox", name="Username*")
    expect(username).to_be_visible(timeout=30000)
    username.fill(PAYCHEX_USERNAME)
    paychex_page.get_by_role("button", name="Continue").click()

    password = paychex_page.get_by_role("textbox", name="Password*")
    expect(password).to_be_visible(timeout=30000)
    password.fill(PAYCHEX_PASSWORD)
    paychex_page.get_by_role("button", name="Log in").click()

    verification = paychex_page.get_by_role(
        "textbox", name=re.compile(r"Verification Code", re.IGNORECASE)
    )

    mfa_visible = False
    try:
        expect(verification).to_be_visible(timeout=15000)
        mfa_visible = True
    except PlaywrightTimeoutError:
        # Some recognized sessions may not show MFA.
        pass

    if mfa_visible:
        verification.fill(_get_paychex_mfa_code())

        trust_checkbox = paychex_page.get_by_role(
            "checkbox", name="unrecognized-device-checkbox"
        )
        if PAYCHEX_TRUST_DEVICE and _paychex_visible(trust_checkbox):
            trust_checkbox.check()

        continue_button = paychex_page.get_by_role("button", name="Continue")
        expect(continue_button).to_be_visible(timeout=10000)
        continue_button.click()

    # Wait for an authenticated control instead of relying on a fixed delay.
    open_menu = paychex_page.get_by_role("button", name="Open Menu")
    expect(open_menu).to_be_visible(timeout=60000)
    print("Paychex login successful.")
    return paychex_page


def open_paychex_people(paychex_page):
    print("Opening Paychex People search...")
    open_menu = paychex_page.get_by_role("button", name="Open Menu")
    expect(open_menu).to_be_visible(timeout=20000)
    open_menu.click()

    people = paychex_page.get_by_role("listitem").filter(
        has_text=re.compile(r"\bPeople\b", re.IGNORECASE)
    ).first
    expect(people).to_be_visible(timeout=20000)
    people.click()

    search_box = paychex_page.get_by_role("textbox", name="Search")
    expect(search_box).to_be_visible(timeout=30000)
    return search_box


def _paychex_id_candidates(paychex_page):
    """Return visible ID values plus nearby result-card text for matching."""
    id_locator = paychex_page.get_by_text(
        re.compile(r"^\s*ID\s+[A-Za-z0-9_-]+\s*$", re.IGNORECASE)
    )

    candidates = {}

    for index in range(id_locator.count()):
        item = id_locator.nth(index)
        try:
            if not item.is_visible():
                continue
            label = clean_text(item.inner_text())
            match = re.search(
                r"\bID\s+([A-Za-z0-9_-]+)\b",
                label,
                re.IGNORECASE,
            )
            if not match:
                continue

            employee_id = match.group(1)
            contexts = item.evaluate(
                """
                el => {
                    const values = [];
                    let current = el;
                    for (let i = 0; i < 8 && current; i++) {
                        const value = (current.innerText || '')
                            .replace(/\\s+/g, ' ')
                            .trim();
                        if (value && value.length <= 1500 && !values.includes(value)) {
                            values.push(value);
                        }
                        current = current.parentElement;
                    }
                    return values;
                }
                """
            )
            candidates.setdefault(employee_id, []).extend(contexts or [])
        except Exception:
            continue

    # De-duplicate nearby context strings while preserving order.
    normalized = {}
    for employee_id, contexts in candidates.items():
        seen = set()
        unique_contexts = []
        for context in contexts:
            context = clean_text(context)
            if context and context not in seen:
                seen.add(context)
                unique_contexts.append(context)
        normalized[employee_id] = unique_contexts

    return normalized


def _choose_paychex_candidate(candidates, first_name, last_name, query, full_query):
    """Choose only an unambiguous employee-ID result."""
    if not candidates:
        return None, "NO_RESULTS"

    first = clean_text(first_name).lower()
    last = clean_text(last_name).lower()
    full = clean_text(f"{first_name} {last_name}").lower()
    reverse_full = clean_text(f"{last_name} {first_name}").lower()

    strong_matches = []

    for employee_id, contexts in candidates.items():
        best_score = 0
        for context in contexts:
            lower = clean_text(context).lower()
            if full and full in lower:
                best_score = max(best_score, 4)
            elif reverse_full and reverse_full in lower:
                best_score = max(best_score, 4)
            elif first and last and first in lower and last in lower:
                best_score = max(best_score, 3)
            elif query.lower() in lower:
                best_score = max(best_score, 1)

        if best_score >= 3:
            strong_matches.append((employee_id, best_score))

    if len(strong_matches) == 1:
        return strong_matches[0][0], "NAME_CONTEXT_MATCH"

    if len(strong_matches) > 1:
        return None, "AMBIGUOUS_NAME_MATCHES"

    # When the exact First+Last query returns exactly one ID, accept it even
    # if the current result DOM does not place the name in the ID's ancestor.
    if query == full_query and len(candidates) == 1:
        return next(iter(candidates.keys())), "UNIQUE_FULL_NAME_RESULT"

    return None, "AMBIGUOUS_OR_UNCONFIRMED"


def search_paychex_employee_id(paychex_page, search_box, first_name, last_name):
    """Search Full Name -> Last Name -> First Name and return a safe ID match."""
    full_query = clean_text(f"{first_name} {last_name}")
    search_terms = []
    for term in (full_query, clean_text(last_name), clean_text(first_name)):
        if term and term.lower() not in {value.lower() for value in search_terms}:
            search_terms.append(term)

    last_status = "NO_RESULTS"

    for term in search_terms:
        print(f"        Paychex search: {term}")
        search_box.fill("")
        search_box.fill(term)
        paychex_page.wait_for_timeout(PAYCHEX_SEARCH_WAIT_MS)

        candidates = _paychex_id_candidates(paychex_page)
        employee_id, status = _choose_paychex_candidate(
            candidates,
            first_name,
            last_name,
            term,
            full_query,
        )
        last_status = status

        if employee_id:
            return employee_id, term, status

        print(
            f"        No safe employee-ID match for {term!r}: {status}; "
            f"visible IDs={list(candidates.keys())}"
        )

        # A full-name ambiguity is not made safer by broadening to first/last.
        if status == "AMBIGUOUS_NAME_MATCHES" and term == full_query:
            break

    return None, "", last_status


def run_paychex_employee_id_lookup(playwright, drivers_to_lookup):
    """
    Separate Paychex phase after Avalon completes.

    The input is the DATABASE backlog, not merely drivers inserted in this run.
    Every scoped database row whose Paychex employee ID is NULL/blank is
    eligible. Paychex login happens once, then each backlog driver is searched
    independently using Full Name -> Last Name -> First Name.
    """
    if not PAYCHEX_ENABLED:
        print("\nPaychex phase disabled.")
        return []

    if not drivers_to_lookup:
        print(
            f"\nNo database rows have an empty {PAYCHEX_ID_COLUMN}; "
            "Paychex phase not needed."
        )
        return []

    lookup_results = []
    searchable_drivers = []

    # Keep rows with missing names in the audit results, but do not search
    # Paychex with a blank query. They remain NULL in the database and will
    # automatically be included again on the next run.
    for record in drivers_to_lookup:
        driver_id = record.get("DB Driver ID")
        first_name = clean_text(record.get("First Name"))
        last_name = clean_text(record.get("Last Name"))
        display_name = clean_text(f"{first_name} {last_name}")

        if first_name or last_name:
            searchable_drivers.append(record)
            continue

        lookup = {
            "driver_id": driver_id,
            "first_name": first_name,
            "last_name": last_name,
            "display_name": display_name,
            "email": clean_text(record.get("Email")),
            "doj": clean_text(record.get("DOJ")),
            "employee_id": None,
            "status": "SKIPPED_MISSING_NAME",
            "search_term": "",
        }
        record["Paychex Lookup Status"] = "SKIPPED_MISSING_NAME"
        lookup_results.append(lookup)
        print(
            f"\nSKIP PAYCHEX: DRIVER_ID={driver_id} has no usable first/last name. "
            "The employee ID remains empty."
        )

    if not searchable_drivers:
        print("\nNo Paychex-searchable drivers remain after name validation.")
        return lookup_results

    print("\n======================================================================")
    print("PAYCHEX PHASE - ALL DATABASE ROWS WITH EMPTY EMPLOYEE ID")
    print(f"Database backlog rows: {len(drivers_to_lookup)}")
    print(f"Rows with searchable names: {len(searchable_drivers)}")
    print("Search order: First+Last -> Last -> First")
    print("======================================================================")

    browser = playwright.chromium.launch(
        headless=PAYCHEX_HEADLESS,
        slow_mo=PAYCHEX_SLOW_MO,
    )
    context = browser.new_context()
    home_page = context.new_page()
    home_page.set_default_timeout(20000)

    try:
        paychex_page = login_to_paychex(home_page)
        search_box = open_paychex_people(paychex_page)

        for index, record in enumerate(searchable_drivers, start=1):
            driver_id = record.get("DB Driver ID")
            first_name = clean_text(record.get("First Name"))
            last_name = clean_text(record.get("Last Name"))
            display_name = clean_text(f"{first_name} {last_name}")

            lookup = {
                "driver_id": driver_id,
                "first_name": first_name,
                "last_name": last_name,
                "display_name": display_name,
                "email": clean_text(record.get("Email")),
                "doj": clean_text(record.get("DOJ")),
                "employee_id": None,
                "status": "",
                "search_term": "",
            }

            print("\n----------------------------------------------------------------------")
            print(
                f"PAYCHEX LOOKUP {index}/{len(searchable_drivers)}: "
                f"{display_name} | DRIVER_ID={driver_id}"
            )
            print("----------------------------------------------------------------------")

            try:
                employee_id, search_term, match_status = search_paychex_employee_id(
                    paychex_page,
                    search_box,
                    first_name,
                    last_name,
                )

                lookup["search_term"] = search_term

                if employee_id:
                    update_paychex_employee_id(driver_id, employee_id)
                    lookup["employee_id"] = employee_id
                    lookup["status"] = "FOUND_AND_SAVED"
                    record["Paychex Employee ID"] = employee_id
                    record["Paychex Lookup Status"] = "FOUND_AND_SAVED"
                    print(
                        f"        FOUND: Paychex Employee ID={employee_id} "
                        f"({match_status})"
                    )
                    print(
                        f"        SAVED: DRIVER_ID={driver_id} -> "
                        f"{PAYCHEX_ID_COLUMN}={employee_id}"
                    )
                else:
                    lookup["status"] = match_status
                    record["Paychex Lookup Status"] = match_status
                    print(f"        NOT SAVED: {match_status}")

            except Exception as error:
                lookup["status"] = "LOOKUP_ERROR"
                lookup["error"] = str(error)
                record["Paychex Lookup Status"] = "LOOKUP_ERROR"
                record["Paychex Lookup Error"] = str(error)
                print(f"        PAYCHEX LOOKUP ERROR: {error}")

                screenshot = RUN_ROOT / f"paychex_error_driver_{driver_id}.png"
                try:
                    paychex_page.screenshot(path=str(screenshot), full_page=True)
                    lookup["error_screenshot"] = str(screenshot)
                except Exception:
                    pass

            lookup_results.append(lookup)

        try:
            search_box.fill("")
        except Exception:
            pass

    finally:
        context.close()
        browser.close()

    found = sum(1 for item in lookup_results if item.get("status") == "FOUND_AND_SAVED")
    failed = len(lookup_results) - found

    print("\n======================================================================")
    print("PAYCHEX PHASE COMPLETE")
    print(f"Database backlog rows considered: {len(drivers_to_lookup)}")
    print(f"Employee IDs found and saved: {found}")
    print(f"Not found / ambiguous / skipped / errors: {failed}")
    print(f"Database column: {DB_TABLE}.{PAYCHEX_ID_COLUMN}")
    print("======================================================================")

    return lookup_results



# ============================================================
# PAYCHEX / TESS API EMPLOYEE-ID LOOKUP - ACTIVE IMPLEMENTATION
# ============================================================


def _normalized_key(value):
    return re.sub(r"[^a-z0-9]", "", clean_text(value).lower())


def _recursive_field_values(value, aliases):
    """Find values for any alias anywhere inside a nested API employee object."""
    alias_keys = {_normalized_key(alias) for alias in aliases}
    found = []

    def walk(node):
        if isinstance(node, dict):
            for key, child in node.items():
                if _normalized_key(key) in alias_keys and child not in (None, ""):
                    if isinstance(child, (str, int, float)):
                        found.append(clean_text(child))
                walk(child)
        elif isinstance(node, list):
            for child in node:
                walk(child)

    walk(value)
    # De-duplicate while preserving order.
    result = []
    seen = set()
    for item in found:
        if item and item not in seen:
            seen.add(item)
            result.append(item)
    return result


def _first_recursive_field(value, aliases):
    values = _recursive_field_values(value, aliases)
    return values[0] if values else ""


PAYCHEX_EMPLOYEE_ID_ALIASES = (
    "employeeId", "employeeID", "employee_id", "EMPLOYEE_ID", "EMPLOYEEID",
    "paychexEmployeeId", "workerId", "employeeNumber", "employeeNo",
)
PAYCHEX_FULL_NAME_ALIASES = (
    "name", "NAME", "fullName", "full_name", "displayName",
)
PAYCHEX_FIRST_NAME_ALIASES = (
    "firstName", "first_name", "FIRSTNAME", "fname", "FNAME", "givenName",
)
PAYCHEX_LAST_NAME_ALIASES = (
    "lastName", "last_name", "LASTNAME", "lname", "LNAME", "familyName", "surname",
)
PAYCHEX_DOB_ALIASES = (
    "dob", "DOB", "dateOfBirth", "date_of_birth", "birthDate", "birth_date",
)
PAYCHEX_EMAIL_ALIASES = (
    "email", "EMAIL", "emailAddress", "email_address", "workEmail", "personalEmail",
)
PAYCHEX_PHONE_ALIASES = (
    "phone", "phoneNumber", "phone_number", "PHONE", "mobile", "mobilePhone",
    "mobile_no", "MOBILE_NO", "mobileNo", "cellPhone", "telephone",
    "workPhone", "homePhone",
)
PAYCHEX_DOJ_ALIASES = (
    "doj", "DOJ", "dateOfJoining", "date_of_joining", "startDate", "STARTDATE",
    "hireDate", "hiredDate", "employmentStartDate", "effectiveDate",
)


# Termination fields are optional because Tess/Paychex payloads can vary by company.
# The reverse control uses them when present. If terminated employees are removed from
# the endpoint entirely, the existing "stored ID missing" warning remains the fallback.
PAYCHEX_TERMINATION_DATE_ALIASES = (
    "terminationDate", "termination_date", "TERMINATION_DATE",
    "termDate", "TERM_DATE", "terminatedDate", "terminated_date",
    "dateOfTermination", "date_of_termination",
    "employmentTerminationDate", "employment_termination_date",
    "workerTerminationDate", "worker_termination_date",
    "separationDate", "separation_date", "SEPARATION_DATE",
)
PAYCHEX_EMPLOYMENT_STATUS_ALIASES = (
    "employmentStatus", "employment_status", "EMPLOYMENT_STATUS",
    "employeeStatus", "employee_status", "EMPLOYEE_STATUS",
    "workerStatus", "worker_status", "WORKER_STATUS",
)


def _extract_paychex_employee_array(payload):
    """
    Convert a Tess response payload into a list of raw employee dictionaries.

    The Tess endpoint is not publicly documented, so this supports common
    response envelopes such as data/employees/result/results as well as a raw
    JSON array. The original employee dictionaries are retained in the arrays.
    """
    if isinstance(payload, list):
        return [item for item in payload if isinstance(item, dict)]

    if not isinstance(payload, dict):
        return []

    preferred_keys = (
        "data", "employees", "Employees", "employeeList", "employeeData",
        "result", "results", "response", "items",
    )
    for key in preferred_keys:
        value = payload.get(key)
        if isinstance(value, list):
            rows = [item for item in value if isinstance(item, dict)]
            if rows:
                return rows
        if isinstance(value, dict):
            rows = _extract_paychex_employee_array(value)
            if rows:
                return rows

    # Generic recursive fallback: choose the largest list of dictionaries.
    candidate_lists = []

    def walk(node):
        if isinstance(node, dict):
            for child in node.values():
                walk(child)
        elif isinstance(node, list):
            rows = [item for item in node if isinstance(item, dict)]
            if rows:
                candidate_lists.append(rows)
            for child in node:
                walk(child)

    walk(payload)
    if candidate_lists:
        candidate_lists.sort(key=len, reverse=True)
        return candidate_lists[0]

    # A single employee object response is also accepted.
    if _first_recursive_field(payload, PAYCHEX_EMPLOYEE_ID_ALIASES):
        return [payload]
    return []


def _paychex_array_fingerprint(employees):
    """
    Return a stable, diagnostic-only fingerprint for one Tess employee array.

    The fingerprint intentionally uses a small set of identity fields rather
    than the raw JSON representation, so dictionary key ordering cannot change
    the result. This helper is used only to warn when company 1 and company 2
    appear to return the same dataset; it never participates in matching.
    """
    identity_rows = []
    for employee in employees or []:
        if not isinstance(employee, dict):
            continue

        employee_id = clean_text(
            _first_recursive_field(employee, PAYCHEX_EMPLOYEE_ID_ALIASES)
        )
        full_name = clean_text(
            _first_recursive_field(employee, PAYCHEX_FULL_NAME_ALIASES)
        ).casefold()
        first_name = clean_text(
            _first_recursive_field(employee, PAYCHEX_FIRST_NAME_ALIASES)
        ).casefold()
        last_name = clean_text(
            _first_recursive_field(employee, PAYCHEX_LAST_NAME_ALIASES)
        ).casefold()
        dob = clean_text(
            _first_recursive_field(employee, PAYCHEX_DOB_ALIASES)
        )

        identity_rows.append(
            "|".join((employee_id, full_name, first_name, last_name, dob))
        )

    identity_rows.sort()
    material = "\n".join(identity_rows).encode("utf-8", errors="replace")
    return hashlib.sha256(material).hexdigest()[:16]


def _paychex_api_headers():
    """Build Tess request headers. Authentication is handled by the endpoint."""
    return {"Accept": "application/json"}


def _call_tess_get_employees(company_id):
    """Invoke the dedicated Tess endpoint for company 1 or company 2.

    No COMPANYID parameter is sent. Endpoint selection itself determines the company:
      1 -> /getEmployeesco1
      2 -> /getEmployeesco2
    """
    company_id = int(company_id)
    if company_id == 1:
        endpoint = PAYCHEX_API_URL_CO1
    elif company_id == 2:
        endpoint = PAYCHEX_API_URL_CO2
    else:
        raise ValueError(f"Unsupported Paychex company_id: {company_id}")

    headers = _paychex_api_headers()
    last_error = None

    for attempt in range(1, PAYCHEX_API_MAX_RETRIES + 1):
        try:
            kwargs = {
                "headers": headers,
                "timeout": PAYCHEX_API_TIMEOUT_SECONDS,
                "verify": PAYCHEX_API_VERIFY_TLS,
            }

            if PAYCHEX_API_METHOD == "POST":
                response = requests.post(endpoint, **kwargs)
            else:
                response = requests.get(endpoint, **kwargs)

        except requests.RequestException as error:
            last_error = error
            if attempt >= PAYCHEX_API_MAX_RETRIES:
                break
            time.sleep(min(2 ** (attempt - 1), 8))
            continue

        if PAYCHEX_API_DEBUG:
            request = response.request
            print("\nTESS REQUEST DEBUG")
            print(f"Company             : {company_id}")
            print(f"Endpoint            : {endpoint}")
            print(f"Method              : {request.method}")
            print(f"Prepared URL        : {request.url}")
            body = request.body
            if isinstance(body, bytes):
                try:
                    body = body.decode("utf-8", errors="replace")
                except Exception:
                    body = repr(body)
            print(f"Request body        : {body if body else '<none>'}")
            print(f"HTTP status         : {response.status_code}")

        if response.status_code == 429 and attempt < PAYCHEX_API_MAX_RETRIES:
            retry_after = clean_text(response.headers.get("Retry-After"))
            try:
                delay = max(float(retry_after), 1.0)
            except Exception:
                delay = min(2 ** (attempt - 1), 8)
            time.sleep(delay)
            continue

        if 500 <= response.status_code <= 599 and attempt < PAYCHEX_API_MAX_RETRIES:
            time.sleep(min(2 ** (attempt - 1), 8))
            continue

        if not response.ok:
            raise RuntimeError(
                f"Tess Paychex API company {company_id} failed with HTTP "
                f"{response.status_code}: {response.text[:1000]}"
            )

        try:
            json_payload = response.json()
        except ValueError as error:
            raise RuntimeError(
                f"Tess Paychex API company {company_id} returned non-JSON: "
                f"{response.text[:1000]}"
            ) from error

        employees = _extract_paychex_employee_array(json_payload)
        print(
            f"Tess Paychex API company {company_id}: {len(employees)} employee row(s) "
            f"stored in {'paychex_array1' if company_id == 1 else 'paychex_array2'}."
        )
        return employees

    raise RuntimeError(
        f"Tess Paychex API company {company_id} request failed after "
        f"{PAYCHEX_API_MAX_RETRIES} attempt(s): {last_error}"
    )

def fetch_paychex_employee_arrays():
    """Call Tess twice and return Array1 (company 1) and Array2 (company 2)."""
    if not PAYCHEX_ENABLED:
        return [], []

    print("\n======================================================================")
    print("PAYCHEX / TESS API FETCH")
    print(f"Call 1: {PAYCHEX_API_URL_CO1} -> paychex_array1")
    print(f"Call 2: {PAYCHEX_API_URL_CO2} -> paychex_array2")
    print(f"Method: {PAYCHEX_API_METHOD}")
    print("No COMPANYID parameter is sent; the endpoint URL determines the company.")
    print("======================================================================")

    paychex_array1 = _call_tess_get_employees(1)
    paychex_array2 = _call_tess_get_employees(2)

    if PAYCHEX_VALIDATE_COMPANY_SPLIT:
        # Diagnostic only. Never allow this check to stop employee matching.
        try:
            fingerprint1 = _paychex_array_fingerprint(paychex_array1)
            fingerprint2 = _paychex_array_fingerprint(paychex_array2)
            print("\n        TESS COMPANY SPLIT CHECK")
            print(
                f"        Array1 count/fingerprint: "
                f"{len(paychex_array1)} / {fingerprint1}"
            )
            print(
                f"        Array2 count/fingerprint: "
                f"{len(paychex_array2)} / {fingerprint2}"
            )
            if (
                paychex_array1
                and paychex_array2
                and fingerprint1 == fingerprint2
            ):
                print(
                    "        WARNING: company 1 and company 2 endpoints returned "
                    "identical employee fingerprints. Confirm that getEmployeesco1 "
                    "and getEmployeesco2 are configured to return different company "
                    "datasets."
                )
        except Exception as error:
            print(
                "        WARNING: Tess company split diagnostic could not run; "
                f"continuing with Paychex matching. Error: {error}"
            )

    if PAYCHEX_PRINT_ARRAYS:
        print(f"\npaychex_array1 ({len(paychex_array1)} rows) = {paychex_array1!r}")
        print(f"\npaychex_array2 ({len(paychex_array2)} rows) = {paychex_array2!r}")

    return paychex_array1, paychex_array2

def _normalize_match_name(value):
    value = clean_text(value).casefold()
    return re.sub(r"[^a-z0-9]+", "", value)


def _normalize_match_phone(value):
    digits = re.sub(r"\D", "", clean_text(value))
    if len(digits) >= 10:
        return digits[-10:]
    return digits



def _normalize_match_email(value):
    return clean_text(value).casefold()


def _normalize_match_date(value):
    parsed = parse_business_date(value)
    if parsed is None:
        return ""
    return parsed.strftime("%Y-%m-%d")


def _paychex_api_employee_summary(employee, company_id):
    """Extract the Tess fields used for deterministic BigA matching."""
    full_name = _first_recursive_field(employee, PAYCHEX_FULL_NAME_ALIASES)
    first_name = _first_recursive_field(employee, PAYCHEX_FIRST_NAME_ALIASES)
    last_name = _first_recursive_field(employee, PAYCHEX_LAST_NAME_ALIASES)
    employee_id = _first_recursive_field(employee, PAYCHEX_EMPLOYEE_ID_ALIASES)
    dob_values = _recursive_field_values(employee, PAYCHEX_DOB_ALIASES)
    email_values = _recursive_field_values(employee, PAYCHEX_EMAIL_ALIASES)
    phone_values = _recursive_field_values(employee, PAYCHEX_PHONE_ALIASES)
    doj_values = _recursive_field_values(employee, PAYCHEX_DOJ_ALIASES)

    return {
        "company_id": company_id,
        "employee_id": clean_text(employee_id),
        "full_name": clean_text(full_name),
        "first_name": clean_text(first_name),
        "last_name": clean_text(last_name),
        "dob_values": [value for value in dob_values if _normalize_match_date(value)],
        "emails": [value for value in email_values if _normalize_match_email(value)],
        "phones": [value for value in phone_values if _normalize_match_phone(value)],
        "doj_values": [value for value in doj_values if _normalize_match_date(value)],
        "raw": employee,
    }


def _name_tokens(value):
    """Normalized name tokens used for middle-name/order tolerant comparisons."""
    value = clean_text(value).casefold()
    return [token for token in re.findall(r"[a-z0-9]+", value) if token]


def _name_tokens_contain_pair(full_name, first_name, last_name, *, reversed_order=False):
    """Return True when a full name contains first/last in the requested order.

    Middle names/initials between the two components are allowed.
    """
    tokens = _name_tokens(full_name)
    first = _normalize_match_name(first_name)
    last = _normalize_match_name(last_name)
    if not tokens or not first or not last:
        return False

    normalized_tokens = [_normalize_match_name(token) for token in tokens]
    try:
        first_pos = normalized_tokens.index(first)
        last_pos = normalized_tokens.index(last)
    except ValueError:
        return False

    return last_pos < first_pos if reversed_order else first_pos < last_pos


def _score_paychex_api_candidate(driver, employee, company_id):
    """Score one Tess employee against one BigA/Avatar driver.

    Name evidence:
      exact First+Last                                70
      exact full name                                70
      exact reversed Last+First                      68
      full NAME contains First ... Last              65
      full NAME contains Last ... First              63

    Secondary evidence:
      DOB exact                                     +35
      Email exact                                   +35
      Phone exact (digits; last 10)                 +30
      DOJ / Start Date exact                        +25

    If a full normal/reversed name identity is unavailable, a candidate is still
    considered only when at least two strong secondary identifiers agree and the
    total score reaches PAYCHEX_MATCH_MIN_SCORE. This allows safe recovery from
    name-order/data-entry differences without accepting a weak single-field match.
    """
    summary = _paychex_api_employee_summary(employee, company_id)
    if not summary["employee_id"]:
        return None

    driver_first_raw = clean_text(driver.get("First Name"))
    driver_last_raw = clean_text(driver.get("Last Name"))
    driver_full_raw = clean_text(
        driver.get("Display Name")
        or f"{driver_first_raw} {driver_last_raw}"
    )

    driver_first = _normalize_match_name(driver_first_raw)
    driver_last = _normalize_match_name(driver_last_raw)
    driver_full = _normalize_match_name(driver_full_raw)

    emp_first = _normalize_match_name(summary["first_name"])
    emp_last = _normalize_match_name(summary["last_name"])
    emp_full = _normalize_match_name(summary["full_name"])

    pair_match = bool(
        driver_first and driver_last and emp_first and emp_last
        and driver_first == emp_first and driver_last == emp_last
    )
    reversed_pair_match = bool(
        PAYCHEX_MATCH_ALLOW_REVERSED_NAMES
        and driver_first and driver_last and emp_first and emp_last
        and driver_first == emp_last and driver_last == emp_first
    )
    full_name_match = bool(driver_full and emp_full and driver_full == emp_full)

    emp_full_normal_order = _name_tokens_contain_pair(
        summary["full_name"], driver_first_raw, driver_last_raw, reversed_order=False
    )
    emp_full_reversed_order = bool(
        PAYCHEX_MATCH_ALLOW_REVERSED_NAMES
        and _name_tokens_contain_pair(
            summary["full_name"], driver_first_raw, driver_last_raw, reversed_order=True
        )
    )

    # Component-level checks are intentionally weak on their own; they only help
    # a fallback when multiple strong secondary identifiers also agree.
    first_component_match = bool(driver_first and emp_first and driver_first == emp_first)
    last_component_match = bool(driver_last and emp_last and driver_last == emp_last)
    first_to_last_match = bool(
        PAYCHEX_MATCH_ALLOW_REVERSED_NAMES
        and driver_first and emp_last and driver_first == emp_last
    )
    last_to_first_match = bool(
        PAYCHEX_MATCH_ALLOW_REVERSED_NAMES
        and driver_last and emp_first and driver_last == emp_first
    )

    name_score = 0
    reasons = []
    primary_name_identity = False

    if pair_match or full_name_match:
        name_score = 70
        primary_name_identity = True
        if pair_match:
            reasons.append("first+last-exact")
        if full_name_match:
            reasons.append("name-exact")
    elif reversed_pair_match:
        name_score = 68
        primary_name_identity = True
        reasons.append("first+last-reversed")
    elif emp_full_normal_order:
        name_score = 65
        primary_name_identity = True
        reasons.append("name-components-exact-middle-tolerant")
    elif emp_full_reversed_order:
        name_score = 63
        primary_name_identity = True
        reasons.append("name-components-reversed-middle-tolerant")
    else:
        # Weak component evidence may contribute to a strong-secondary fallback.
        if first_component_match:
            name_score += 12
            reasons.append("first-name-exact")
        if last_component_match:
            name_score += 15
            reasons.append("last-name-exact")
        if first_to_last_match:
            name_score += 12
            reasons.append("first-to-last-exact")
        if last_to_first_match:
            name_score += 15
            reasons.append("last-to-first-exact")

    score = name_score

    driver_dob = _normalize_match_date(driver.get("DOB"))
    employee_dobs = {
        _normalize_match_date(value)
        for value in summary["dob_values"]
        if _normalize_match_date(value)
    }
    dob_match = bool(driver_dob and driver_dob in employee_dobs)
    if dob_match:
        score += 35
        reasons.append("dob-exact")

    driver_email = _normalize_match_email(driver.get("Email"))
    employee_emails = {
        _normalize_match_email(value)
        for value in summary["emails"]
        if _normalize_match_email(value)
    }
    email_match = bool(driver_email and driver_email in employee_emails)
    if email_match:
        score += 35
        reasons.append("email-exact")

    driver_phone = _normalize_match_phone(driver.get("Phone"))
    employee_phones = {
        _normalize_match_phone(value)
        for value in summary["phones"]
        if _normalize_match_phone(value)
    }
    phone_match = bool(driver_phone and driver_phone in employee_phones)
    if phone_match:
        score += 30
        reasons.append("phone-exact")

    driver_doj = _normalize_match_date(driver.get("DOJ"))
    employee_dojs = {
        _normalize_match_date(value)
        for value in summary["doj_values"]
        if _normalize_match_date(value)
    }
    doj_match = bool(driver_doj and driver_doj in employee_dojs)
    if doj_match:
        score += 25
        reasons.append("doj-exact")

    strong_secondary_count = sum(
        1 for matched in (dob_match, email_match, phone_match, doj_match) if matched
    )
    any_name_component = bool(
        first_component_match
        or last_component_match
        or first_to_last_match
        or last_to_first_match
    )

    if not primary_name_identity:
        if not PAYCHEX_MATCH_ALLOW_STRONG_SECONDARY:
            return None

        # Strong fallback examples that are allowed:
        #   First OR Last + DOB + Phone
        #   First OR Last + DOB + DOJ
        #   Email + DOB
        #   Email + Phone + DOJ
        # This keeps a single shared phone/DOB/DOJ from becoming an auto-match.
        strong_fallback = bool(
            strong_secondary_count >= 2
            and (
                any_name_component
                or (email_match and dob_match)
                or (email_match and phone_match)
                or (dob_match and phone_match and doj_match)
            )
        )
        if not strong_fallback:
            return None
        reasons.append("strong-secondary-fallback")

    return {
        "score": score,
        "reasons": reasons,
        "name_identity": (
            "normal"
            if pair_match or full_name_match or emp_full_normal_order
            else "reversed"
            if reversed_pair_match or emp_full_reversed_order
            else "secondary-fallback"
        ),
        "secondary_matches": strong_secondary_count,
        **summary,
    }

def _find_best_paychex_match_in_array(driver, employee_array, company_id):
    """
    Resolve one Tess company array independently.

    A valid match in company 1 is final. company 2 must never compete with
    or override a valid company 1 match.
    """
    candidates = []
    for employee in employee_array:
        if not isinstance(employee, dict):
            continue
        scored = _score_paychex_api_candidate(driver, employee, company_id)
        if scored:
            candidates.append(scored)

    if not candidates:
        return None, "NO_SAFE_IDENTITY_MATCH", []

    candidates.sort(key=lambda item: item["score"], reverse=True)
    top = candidates[0]
    second_score = candidates[1]["score"] if len(candidates) > 1 else -1

    if top["score"] < PAYCHEX_MATCH_MIN_SCORE:
        return None, "BELOW_MIN_SCORE", candidates[:5]

    # Equal/close candidates inside the SAME company are ambiguous unless both
    # rows resolve to the same Employee ID. An ambiguous Array1 result is not a
    # safe match, so the caller is allowed to continue to Array2.
    if len(candidates) > 1:
        same_employee = (
            clean_text(candidates[1]["employee_id"])
            == clean_text(top["employee_id"])
        )
        if (
            not same_employee
            and (top["score"] - second_score) < PAYCHEX_MATCH_MIN_MARGIN
        ):
            return None, "AMBIGUOUS_TOP_MATCHES", candidates[:5]

    return top, "MATCHED", candidates[:5]


def find_best_paychex_api_match(driver, paychex_array1, paychex_array2):
    """
    Resolve Paychex in strict company priority order.

    1. Search company 1 / paychex_array1.
    2. If Array1 has a safe valid match, return it immediately.
    3. Only when Array1 has NO safe match, search company 2 / paychex_array2.
    4. Array2 can never override a valid Array1 result, even with a higher score.
    """
    array1_match, array1_status, array1_candidates = (
        _find_best_paychex_match_in_array(driver, paychex_array1, 1)
    )
    if array1_match:
        return array1_match, "MATCHED_ARRAY1", array1_candidates

    array2_match, array2_status, array2_candidates = (
        _find_best_paychex_match_in_array(driver, paychex_array2, 2)
    )
    if array2_match:
        return array2_match, "MATCHED_ARRAY2", array2_candidates

    # No safe match in either array. Keep candidates from both arrays for
    # diagnostics, but preserve the fact that Array1 was checked first.
    diagnostics = []
    for item in array1_candidates:
        copy = dict(item)
        copy["lookup_array"] = "Array1"
        diagnostics.append(copy)
    for item in array2_candidates:
        copy = dict(item)
        copy["lookup_array"] = "Array2"
        diagnostics.append(copy)

    status = f"ARRAY1_{array1_status}__ARRAY2_{array2_status}"
    return None, status, diagnostics[:10]


def run_paychex_api_employee_id_lookup(drivers_to_lookup):
    """
    Active Paychex phase.

    1) company 1 endpoint -> paychex_array1
    2) company 2 endpoint -> paychex_array2
    3) Search company 1 first. Only if no safe Array1 match exists, search company 2.
    4) Within each array, use exact FNAME+LNAME or exact NAME, with DOB/email/mobile
       used to strengthen and disambiguate.
    5) Save the selected Employee ID to ADMIN_DRIVER_INFO.
    """
    if not PAYCHEX_ENABLED:
        print("\nPaychex/Tess API phase disabled.")
        return [], [], []

    paychex_array1, paychex_array2 = fetch_paychex_employee_arrays()

    if not drivers_to_lookup:
        print(f"\nNo BigA rows have an empty {PAYCHEX_ID_COLUMN}; no matching required.")
        return paychex_array1, paychex_array2, []

    results = []
    print("\n======================================================================")
    print("PAYCHEX / TESS API MATCHING")
    print(f"BigA backlog rows: {len(drivers_to_lookup)}")
    print(f"Array1 company 1: {len(paychex_array1)}")
    print(f"Array2 company 2: {len(paychex_array2)}")
    print("Lookup order: Array1 (company 1) FIRST; Array2 only if Array1 has no safe match")
    print("Rule: normal/reversed names plus DOB/email/phone/DOJ; strong-secondary fallback allowed only with corroboration")
    print(f"Minimum score: {PAYCHEX_MATCH_MIN_SCORE}; margin: {PAYCHEX_MATCH_MIN_MARGIN}")
    print("======================================================================")

    for index, driver in enumerate(drivers_to_lookup, start=1):
        driver_id = driver.get("DB Driver ID")
        display_name = clean_text(driver.get("Display Name") or f"{driver.get('First Name', '')} {driver.get('Last Name', '')}")
        result = {
            "driver_id": driver_id,
            "first_name": clean_text(driver.get("First Name")),
            "last_name": clean_text(driver.get("Last Name")),
            "display_name": display_name,
            "email": clean_text(driver.get("Email")),
            "phone": clean_text(driver.get("Phone")),
            "dob": clean_text(driver.get("DOB")),
            "doj": clean_text(driver.get("DOJ")),
            "employee_id": None,
            "company_id": None,
            "score": 0,
            "status": "",
        }

        print("\n----------------------------------------------------------------------")
        print(f"PAYCHEX API MATCH {index}/{len(drivers_to_lookup)}: {display_name} | DRIVER_ID={driver_id}")
        print(
            f"DOB={result['dob'] or '<missing>'} | "
            f"Email={result['email'] or '<missing>'} | "
            f"Phone={result['phone'] or '<missing>'}"
        )
        print("----------------------------------------------------------------------")

        try:
            match, status, top_candidates = find_best_paychex_api_match(
                driver,
                paychex_array1,
                paychex_array2,
            )

            result["status"] = status
            result["top_candidates"] = [
                {
                    "company_id": item["company_id"],
                    "employee_id": item["employee_id"],
                    "name": clean_text(item.get("full_name") or f"{item['first_name']} {item['last_name']}"),
                    "score": item["score"],
                    "reasons": item["reasons"],
                    "lookup_array": clean_text(item.get("lookup_array")),
                }
                for item in top_candidates
            ]

            if match:
                audit_score = int(match.get("score") or 0)
                audit_reason = _compact_match_reason(
                    status,
                    match.get("reasons"),
                    context=f"company={match.get('company_id')}",
                )
            elif top_candidates:
                audit_candidate = max(
                    top_candidates,
                    key=lambda item: int(item.get("score") or 0),
                )
                audit_score = int(audit_candidate.get("score") or 0)
                audit_context = clean_text(audit_candidate.get("lookup_array"))
                if audit_candidate.get("company_id"):
                    audit_context = clean_text(
                        f"{audit_context} company={audit_candidate.get('company_id')}"
                    )
                audit_reason = _compact_match_reason(
                    status,
                    audit_candidate.get("reasons"),
                    context=audit_context,
                )
            else:
                audit_score = 0
                audit_reason = _compact_match_reason(status)

            update_biga_paychex_match_audit(driver_id, audit_score, audit_reason)
            result["score"] = audit_score
            result["match_reason"] = audit_reason
            print(
                f"        BIGA PAYCHEX AUDIT: score={audit_score} | reason={audit_reason}"
            )

            if not match:
                driver["Paychex Lookup Status"] = status
                print(f"        NOT SAVED: {status}")
                if result["top_candidates"]:
                    print(f"        Top candidates: {result['top_candidates']}")
                results.append(result)
                continue

            employee_id = clean_text(match["employee_id"])
            update_paychex_employee_id(driver_id, employee_id)

            result.update(
                {
                    "employee_id": employee_id,
                    "company_id": match["company_id"],
                    "score": match["score"],
                    "status": "FOUND_AND_SAVED",
                    "match_reasons": match["reasons"],
                    "match_reason": audit_reason,
                }
            )
            driver["Paychex Employee ID"] = employee_id
            driver["Paychex Lookup Status"] = "FOUND_AND_SAVED"
            print(
                f"        FOUND: Employee ID={employee_id} | "
                f"company={match['company_id']} | score={match['score']} | "
                f"reasons={match['reasons']}"
            )
            print(f"        SAVED: DRIVER_ID={driver_id} -> {PAYCHEX_ID_COLUMN}={employee_id}")

        except Exception as error:
            result["status"] = "LOOKUP_ERROR"
            result["error"] = str(error)
            driver["Paychex Lookup Status"] = "LOOKUP_ERROR"
            error_reason = _compact_match_reason(
                "LOOKUP_ERROR",
                context=str(error),
            )
            result["match_reason"] = error_reason
            try:
                update_biga_paychex_match_audit(
                    driver_id,
                    result.get("score", 0),
                    error_reason,
                )
            except Exception as audit_error:
                result["paychex_audit_error"] = str(audit_error)
                print(f"        BIGA PAYCHEX AUDIT ERROR: {audit_error}")
            print(f"        PAYCHEX API LOOKUP ERROR: {error}")

        results.append(result)

    found = sum(1 for item in results if item.get("status") == "FOUND_AND_SAVED")
    print("\n======================================================================")
    print("PAYCHEX / TESS API PHASE COMPLETE")
    print(f"Array1 rows (company 1): {len(paychex_array1)}")
    print(f"Array2 rows (company 2): {len(paychex_array2)}")
    print(f"BigA backlog rows checked: {len(drivers_to_lookup)}")
    print(f"Employee IDs found and saved: {found}")
    print(f"Unresolved: {len(results) - found}")
    print("======================================================================")
    return paychex_array1, paychex_array2, results


# ============================================================
# PAYCHEX REVERSE RECONCILIATION
# ============================================================


def load_biga_drivers_with_paychex_employee_id_for_reverse_check():
    """Load scoped BigA drivers that already have a stored Paychex employee ID."""
    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)
    try:
        cursor.execute(
            f"""
            SELECT
                DRIVER_ID, FNAME, LNAME, EMAIL, PHONE, DOB, DOJ, LOCATION,
                `{PAYCHEX_ID_COLUMN}` AS PAYCHEX_EMPLOYEE_ID
            FROM `{DB_TABLE}`
            WHERE ORG_ID = %s
              AND LOCATION_ID = %s
              AND `{PAYCHEX_ID_COLUMN}` IS NOT NULL
              AND TRIM(`{PAYCHEX_ID_COLUMN}`) <> ''
            ORDER BY DRIVER_ID ASC
            """,
            (ORG_ID, LOCATION_ID),
        )
        rows = cursor.fetchall()
    finally:
        cursor.close()
        connection.close()

    drivers = []
    for row in rows:
        first_name = clean_text(row.get("FNAME"))
        last_name = clean_text(row.get("LNAME"))
        drivers.append(
            {
                "DB Driver ID": row.get("DRIVER_ID"),
                "First Name": first_name,
                "Last Name": last_name,
                "Display Name": clean_text(f"{first_name} {last_name}"),
                "Email": clean_text(row.get("EMAIL")),
                "Phone": clean_text(row.get("PHONE")),
                "DOB": clean_text(row.get("DOB")),
                "DOJ": clean_text(row.get("DOJ")),
                "Location": clean_text(row.get("LOCATION")),
                "Paychex Employee ID": clean_text(row.get("PAYCHEX_EMPLOYEE_ID")),
            }
        )
    return drivers


def _normalize_paychex_employee_id(value):
    return clean_text(value).casefold()


def _paychex_termination_details(employee):
    """Return normalized termination dates and employment status from one Paychex row."""
    raw_dates = _recursive_field_values(employee, PAYCHEX_TERMINATION_DATE_ALIASES)
    termination_dates = []
    seen_dates = set()
    for raw_value in raw_dates:
        normalized = _normalize_match_date(raw_value)
        if normalized and normalized not in seen_dates:
            seen_dates.add(normalized)
            termination_dates.append(normalized)

    employment_status = clean_text(
        _first_recursive_field(employee, PAYCHEX_EMPLOYMENT_STATUS_ALIASES)
    )
    return termination_dates, employment_status


def _termination_dates_for_review_month(termination_dates):
    year, month = get_paychex_termination_year_month()
    matched = []
    for value in termination_dates or []:
        parsed = parse_business_date(value)
        if parsed is not None and parsed.year == year and parsed.month == month:
            matched.append(parsed.strftime("%Y-%m-%d"))
    return sorted(set(matched))


def _build_paychex_employee_id_index(paychex_array1, paychex_array2):
    index = {}
    for company_id, employees in ((1, paychex_array1), (2, paychex_array2)):
        for employee in employees or []:
            employee_id = clean_text(
                _first_recursive_field(employee, PAYCHEX_EMPLOYEE_ID_ALIASES)
            )
            normalized = _normalize_paychex_employee_id(employee_id)
            if not normalized:
                continue
            termination_dates, employment_status = _paychex_termination_details(employee)
            index.setdefault(normalized, []).append(
                {
                    "company_id": company_id,
                    "employee_id": employee_id,
                    "name": clean_text(
                        _first_recursive_field(employee, PAYCHEX_FULL_NAME_ALIASES)
                        or " ".join(
                            value
                            for value in (
                                _first_recursive_field(employee, PAYCHEX_FIRST_NAME_ALIASES),
                                _first_recursive_field(employee, PAYCHEX_LAST_NAME_ALIASES),
                            )
                            if clean_text(value)
                        )
                    ),
                    "termination_dates": termination_dates,
                    "employment_status": employment_status,
                    "raw": employee,
                }
            )
    return index


def _match_active_avatar_row_to_biga(active_row, biga_drivers):
    """Conservatively map one Avatar New Hire grid row to exactly one BigA row.

    The New Hire grid currently provides First Name, Last Name, Email and DOJ. The
    matcher accepts normal or reversed First/Last order and also supports DOB/Phone
    automatically if those fields become available in the grid later. At least two
    independent identity dimensions must agree; name-only is never accepted here.
    """
    active_first = normalize_name_key(active_row.get("first_name"))
    active_last = normalize_name_key(active_row.get("last_name"))
    active_email = normalize_email(active_row.get("email")) or ""
    active_doj = normalize_date_key(active_row.get("doj"))
    active_dob = normalize_date_key(active_row.get("dob") or active_row.get("DOB"))
    active_phone = normalize_phone(active_row.get("phone") or active_row.get("Phone")) or ""

    candidates = []
    for driver in biga_drivers:
        db_first = normalize_name_key(driver.get("First Name"))
        db_last = normalize_name_key(driver.get("Last Name"))
        db_email = normalize_email(driver.get("Email")) or ""
        db_doj = normalize_date_key(driver.get("DOJ"))
        db_dob = normalize_date_key(driver.get("DOB"))
        db_phone = normalize_phone(driver.get("Phone")) or ""

        name_match = bool(
            active_first and active_last and db_first and db_last
            and active_first == db_first and active_last == db_last
        )
        reversed_name_match = bool(
            PAYCHEX_MATCH_ALLOW_REVERSED_NAMES
            and active_first and active_last and db_first and db_last
            and active_first == db_last and active_last == db_first
        )
        email_match = bool(active_email and db_email and active_email == db_email)
        doj_match = bool(active_doj and db_doj and active_doj == db_doj)
        dob_match = bool(active_dob and db_dob and active_dob == db_dob)
        phone_match = bool(active_phone and db_phone and active_phone == db_phone)

        name_dimension = name_match or reversed_name_match
        dimensions = sum(
            1
            for matched in (name_dimension, email_match, doj_match, dob_match, phone_match)
            if matched
        )
        if dimensions < 2:
            continue

        score = 0
        reasons = []
        if name_match:
            score += 70
            reasons.append("name-exact")
        elif reversed_name_match:
            score += 68
            reasons.append("name-reversed")
        if email_match:
            score += 45
            reasons.append("email-exact")
        if dob_match:
            score += 35
            reasons.append("dob-exact")
        if phone_match:
            score += 35
            reasons.append("phone-exact")
        if doj_match:
            score += 30
            reasons.append("doj-exact")
        candidates.append((score, driver, reasons))

    if not candidates:
        return None, "NO_SAFE_BIGA_MATCH", []

    candidates.sort(key=lambda item: item[0], reverse=True)
    top_score = candidates[0][0]
    top = [item for item in candidates if item[0] == top_score]
    if len(top) != 1:
        return None, "AMBIGUOUS_BIGA_MATCH", [
            {
                "driver_id": item[1].get("DB Driver ID"),
                "name": item[1].get("Display Name"),
                "score": item[0],
                "reasons": item[2],
            }
            for item in top[:10]
        ]

    return top[0][1], "MATCHED_BIGA", top[0][2]

def reconcile_active_avatar_against_paychex(
    avatar_active_drivers,
    paychex_array1,
    paychex_array2,
    *,
    active_view_name="",
    active_view_expected_count=0,
    active_view_scan_error="",
):
    """Reverse-check every active Avatar driver against Paychex.

    The Avatar population is ALWAYS the complete New Hire Saved View across all pages.
    In this tenant New Hire is the authoritative active-driver roster. TARGET_MONTH never
    limits this reverse-check population.

    Alerts are generated when:
      1) the stored Paychex ID is present, but Paychex shows a termination date in the
         configured termination-review month (server current month by default); or
      2) the stored Paychex ID is absent from both current Paychex arrays; or
      3) the stored ID is absent but the same person safely matches another Paychex ID
         (possible rehire/ID change; current-month termination is also checked there).

    This function is alert-only. It never clears, replaces, or deactivates records.
    """
    result = {
        "enabled": PAYCHEX_REVERSE_CHECK_ENABLED,
        "scope": PAYCHEX_REVERSE_CHECK_SCOPE,
        "view_name": active_view_name,
        "active_view_expected_count": int(active_view_expected_count or 0),
        "active_rows_scanned": len(avatar_active_drivers or []),
        "full_list_validated": False,
        "scan_error": clean_text(active_view_scan_error),
        "biga_rows_with_paychex_id": 0,
        "mapped_active_rows": 0,
        "paychex_id_present": 0,
        "unmapped_active_rows": 0,
        "ambiguous_active_rows": 0,
        "termination_check_enabled": PAYCHEX_TERMINATION_CHECK_ENABLED,
        "termination_month": get_paychex_termination_month_label(),
        "current_month_termination_flags": 0,
        "missing_id_flags": 0,
        "replacement_id_flags": 0,
        "flags": [],
    }

    if not PAYCHEX_REVERSE_CHECK_ENABLED:
        return result
    if active_view_scan_error:
        return result

    expected_active_count = int(active_view_expected_count or 0)
    captured_active_count = len(avatar_active_drivers or [])
    if captured_active_count != expected_active_count:
        result["scan_error"] = (
            "Reverse reconciliation requires the complete Avatar active-driver list. "
            f"Saved view reported {expected_active_count} row(s), but "
            f"{captured_active_count} row(s) were captured. No reverse flags were generated."
        )
        return result
    result["full_list_validated"] = True

    biga_drivers = load_biga_drivers_with_paychex_employee_id_for_reverse_check()
    result["biga_rows_with_paychex_id"] = len(biga_drivers)
    paychex_id_index = _build_paychex_employee_id_index(paychex_array1, paychex_array2)
    termination_month_label = result["termination_month"]

    print("\n======================================================================")
    print("PAYCHEX REVERSE RECONCILIATION - FULL NEW HIRE ACTIVE ROSTER")
    print(f"Avatar reverse source view: {active_view_name or PAYCHEX_REVERSE_SOURCE_VIEW_NAME}")
    print("Scope: ALL New Hire rows across ALL pages; TARGET_MONTH is NOT applied")
    print(f"New Hire rows reported for reverse check: {expected_active_count}")
    print(f"New Hire rows captured/scanned for reverse check: {captured_active_count}")
    print("Full-list validation: PASS")
    print(f"BigA rows with stored Paychex ID: {len(biga_drivers)}")
    print(f"Current Paychex IDs across Array1+Array2: {len(paychex_id_index)}")
    print(
        "Current-month Paychex termination check: "
        + (f"ENABLED ({termination_month_label})" if PAYCHEX_TERMINATION_CHECK_ENABLED else "DISABLED")
    )
    print("Rule A: flag New Hire roster drivers terminated in Paychex during the review month")
    print("Rule B: flag New Hire roster drivers whose stored Paychex ID is absent from both arrays")
    print("Database action: NONE (alert-only)")
    print("======================================================================")

    for active_row in avatar_active_drivers:
        biga_driver, mapping_status, mapping_reasons = _match_active_avatar_row_to_biga(
            active_row, biga_drivers
        )
        if biga_driver is None:
            if mapping_status == "AMBIGUOUS_BIGA_MATCH":
                result["ambiguous_active_rows"] += 1
            else:
                result["unmapped_active_rows"] += 1
            continue

        result["mapped_active_rows"] += 1
        stored_id = clean_text(biga_driver.get("Paychex Employee ID"))
        normalized_stored_id = _normalize_paychex_employee_id(stored_id)
        stored_entries = paychex_id_index.get(normalized_stored_id, [])

        if stored_entries:
            result["paychex_id_present"] += 1

            if PAYCHEX_TERMINATION_CHECK_ENABLED:
                termination_hits = []
                for entry in stored_entries:
                    month_dates = _termination_dates_for_review_month(
                        entry.get("termination_dates") or []
                    )
                    if month_dates:
                        termination_hits.append((entry, month_dates))

                if termination_hits:
                    termination_dates = sorted(
                        {
                            date_value
                            for _entry, date_values in termination_hits
                            for date_value in date_values
                        }
                    )
                    statuses = sorted(
                        {
                            clean_text(entry.get("employment_status"))
                            for entry, _dates in termination_hits
                            if clean_text(entry.get("employment_status"))
                        }
                    )
                    company_ids = sorted(
                        {
                            str(entry.get("company_id"))
                            for entry, _dates in termination_hits
                            if entry.get("company_id") is not None
                        }
                    )
                    flag = {
                        "status": "ACTIVE_IN_AVATAR_PAYCHEX_TERMINATED_CURRENT_MONTH",
                        "driver_id": biga_driver.get("DB Driver ID"),
                        "name": clean_text(biga_driver.get("Display Name")),
                        "email": clean_text(biga_driver.get("Email")),
                        "phone": clean_text(biga_driver.get("Phone")),
                        "dob": clean_text(biga_driver.get("DOB")),
                        "doj": clean_text(biga_driver.get("DOJ")),
                        "location": clean_text(biga_driver.get("Location")),
                        "stored_paychex_employee_id": stored_id,
                        "possible_current_paychex_employee_id": "",
                        "paychex_company_id": ",".join(company_ids),
                        "paychex_status": ", ".join(statuses),
                        "termination_date": ", ".join(termination_dates),
                        "replacement_company_id": None,
                        "replacement_score": 0,
                        "replacement_status": "",
                        "mapping_reason": ", ".join(mapping_reasons),
                        "reason": (
                            f"Driver is active in Avatar, but Paychex shows a termination date "
                            f"in {termination_month_label}. Review Avatar active status and offboarding."
                        ),
                    }
                    result["flags"].append(flag)
                    result["current_month_termination_flags"] += 1
                    print(
                        "    CURRENT-MONTH TERMINATION FLAG: "
                        f"DRIVER_ID={flag['driver_id']} | {flag['name']} | "
                        f"Paychex ID={stored_id} | termination={flag['termination_date']}"
                    )
            continue

        # Stored Paychex ID is absent from both arrays. This remains a separate warning
        # because if the endpoint removes terminated employees, no termination date may
        # be available to prove the month of termination.
        replacement_match = None
        replacement_status = ""
        replacement_candidates = []
        try:
            replacement_match, replacement_status, replacement_candidates = (
                find_best_paychex_api_match(
                    biga_driver, paychex_array1, paychex_array2
                )
            )
        except Exception as error:
            replacement_status = f"REPLACEMENT_LOOKUP_ERROR: {error}"

        replacement_id = clean_text((replacement_match or {}).get("employee_id"))
        replacement_term_dates = []
        replacement_paychex_status = ""
        replacement_company_id = (replacement_match or {}).get("company_id")
        if replacement_match:
            all_replacement_dates, replacement_paychex_status = _paychex_termination_details(
                replacement_match.get("raw") or {}
            )
            if PAYCHEX_TERMINATION_CHECK_ENABLED:
                replacement_term_dates = _termination_dates_for_review_month(
                    all_replacement_dates
                )

        if (
            replacement_id
            and _normalize_paychex_employee_id(replacement_id) != normalized_stored_id
            and replacement_term_dates
        ):
            flag_status = "ACTIVE_IN_AVATAR_PAYCHEX_TERMINATED_CURRENT_MONTH_REPLACEMENT_ID"
            reason = (
                f"Driver is active in Avatar; stored Paychex ID is absent, but the driver "
                f"matches Paychex ID {replacement_id}, which has a termination date in "
                f"{termination_month_label}. Review rehire/ID history and Avatar offboarding."
            )
            result["current_month_termination_flags"] += 1
            result["replacement_id_flags"] += 1
        elif replacement_id and _normalize_paychex_employee_id(replacement_id) != normalized_stored_id:
            flag_status = "STORED_PAYCHEX_ID_MISSING_REPLACEMENT_FOUND"
            reason = (
                "Driver is active in Avatar; stored Paychex Employee ID is absent from both "
                "Paychex arrays, but the driver safely matches a different current Paychex ID. "
                "Review for rehire/employee-ID change before updating BigA."
            )
            result["replacement_id_flags"] += 1
        else:
            replacement_id = ""
            flag_status = "ACTIVE_IN_AVATAR_PAYCHEX_ID_NOT_FOUND"
            reason = (
                "Driver is active in Avatar, but the stored Paychex Employee ID is absent "
                "from both current Paychex arrays. Termination month cannot be confirmed from "
                "the current payload; review for Paychex termination/removal."
            )
            result["missing_id_flags"] += 1

        flag = {
            "status": flag_status,
            "driver_id": biga_driver.get("DB Driver ID"),
            "name": clean_text(biga_driver.get("Display Name")),
            "email": clean_text(biga_driver.get("Email")),
            "phone": clean_text(biga_driver.get("Phone")),
            "dob": clean_text(biga_driver.get("DOB")),
            "doj": clean_text(biga_driver.get("DOJ")),
            "location": clean_text(biga_driver.get("Location")),
            "stored_paychex_employee_id": stored_id,
            "possible_current_paychex_employee_id": replacement_id,
            "paychex_company_id": clean_text(replacement_company_id),
            "paychex_status": clean_text(replacement_paychex_status),
            "termination_date": ", ".join(replacement_term_dates),
            "replacement_company_id": replacement_company_id,
            "replacement_score": int((replacement_match or {}).get("score") or 0),
            "replacement_status": clean_text(replacement_status),
            "mapping_reason": ", ".join(mapping_reasons),
            "reason": reason,
        }
        result["flags"].append(flag)
        print(
            "    REVERSE FLAG: "
            f"DRIVER_ID={flag['driver_id']} | {flag['name']} | "
            f"stored Paychex ID={stored_id} | status={flag_status}"
            + (f" | termination={flag['termination_date']}" if flag["termination_date"] else "")
            + (f" | possible current ID={replacement_id}" if replacement_id else "")
        )

    print("\n======================================================================")
    print("PAYCHEX REVERSE RECONCILIATION COMPLETE")
    print(f"Termination review month: {termination_month_label}")
    print(f"New Hire roster rows mapped safely to BigA: {result['mapped_active_rows']}")
    print(f"Stored Paychex IDs still present: {result['paychex_id_present']}")
    print(f"Current-month Paychex termination flags: {result['current_month_termination_flags']}")
    print(f"Stored Paychex ID missing flags: {result['missing_id_flags']}")
    print(f"Possible replacement Paychex ID flags: {result['replacement_id_flags']}")
    print(f"New Hire rows not safely mapped to BigA: {result['unmapped_active_rows']}")
    print(f"New Hire rows with ambiguous BigA mapping: {result['ambiguous_active_rows']}")
    print(f"TOTAL REVERSE FLAGS: {len(result['flags'])}")
    print("======================================================================")
    return result

# ============================================================
# COMPLETION EMAIL NOTIFICATIONS
# ============================================================
# SAMSARA DRIVER SYNC
# ============================================================


def load_drivers_for_samsara_sync():
    """
    Load every scoped BigA driver that has a Paychex employee ID.

    These rows are matched against the Samsara driver array. Existing BigA rows
    are included; this is not limited to drivers inserted during the current run.
    """
    if not SAMSARA_ENABLED:
        return []

    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)
    try:
        cursor.execute(
            f"""
            SELECT
                DRIVER_ID,
                FNAME,
                LNAME,
                EMAIL,
                PHONE,
                DOJ,
                LICENSE_NUMBER,
                STATE,
                SAMSARAID,
                FOUND_IN_SAMSARA,
                UPDATED_IN_SAMSARA,
                `{PAYCHEX_ID_COLUMN}` AS PAYCHEX_EMPLOYEE_ID
            FROM `{DB_TABLE}`
            WHERE ORG_ID = %s
              AND LOCATION_ID = %s
              AND `{PAYCHEX_ID_COLUMN}` IS NOT NULL
              AND TRIM(`{PAYCHEX_ID_COLUMN}`) <> ''
            ORDER BY DRIVER_ID ASC
            """,
            (ORG_ID, LOCATION_ID),
        )
        rows = cursor.fetchall()
    finally:
        cursor.close()
        connection.close()

    drivers = []
    for row in rows:
        first_name = clean_text(row.get("FNAME"))
        last_name = clean_text(row.get("LNAME"))
        drivers.append(
            {
                "DB Driver ID": row.get("DRIVER_ID"),
                "First Name": first_name,
                "Last Name": last_name,
                "Display Name": clean_text(f"{first_name} {last_name}"),
                "Email": clean_text(row.get("EMAIL")),
                "Phone": clean_text(row.get("PHONE")),
                "DOJ": clean_text(row.get("DOJ")),
                "License Number": clean_text(row.get("LICENSE_NUMBER")),
                "License State": clean_text(row.get("STATE")),
                "Samsara ID": clean_text(row.get("SAMSARAID")),
                "Found in Samsara": clean_text(row.get("FOUND_IN_SAMSARA")),
                "Updated in Samsara": clean_text(row.get("UPDATED_IN_SAMSARA")),
                "Paychex Employee ID": clean_text(row.get("PAYCHEX_EMPLOYEE_ID")),
            }
        )

    print("\n======================================================================")
    print("SAMSARA BIGA SOURCE LIST")
    print(f"BigA drivers with a nonblank {PAYCHEX_ID_COLUMN}: {len(drivers)}")
    print(f"Scope: ORG_ID={ORG_ID}, LOCATION_ID={LOCATION_ID}")
    print("======================================================================")
    return drivers


def update_biga_samsara_tracking(
    driver_id,
    *,
    samsara_driver_id=None,
    found=False,
    updated=False,
    match_score=None,
    match_reason=None,
):
    """
    Persist Samsara tracking against one exact BigA driver row.

    FOUND_IN_SAMSARA is set only after a safe Samsara match.
    SAMSARAID stores the matched Samsara driver ID.
    UPDATED_IN_SAMSARA is set only after the desired external IDs are confirmed
    in Samsara (PATCHED or ALREADY_CURRENT). Existing Y values are never reset.
    """
    assignments = []
    values = []

    if found:
        assignments.append("`FOUND_IN_SAMSARA` = 'Y'")

    samsara_driver_id = clean_text(samsara_driver_id)
    if samsara_driver_id:
        assignments.append("`SAMSARAID` = %s")
        values.append(samsara_driver_id)

    if updated:
        assignments.append("`UPDATED_IN_SAMSARA` = 'Y'")

    if match_score is not None:
        try:
            numeric_score = int(match_score or 0)
        except (TypeError, ValueError):
            numeric_score = 0
        assignments.append("`SAMSARA_MATCH_SCORE` = %s")
        values.append(numeric_score)

    if match_reason is not None:
        assignments.append("`SAMSARA_REASON` = %s")
        values.append(clean_text(match_reason)[:255])

    if not assignments:
        return

    values.extend([driver_id, ORG_ID, LOCATION_ID])

    connection = get_db_connection()
    cursor = connection.cursor()
    try:
        cursor.execute(
            f"""
            UPDATE `{DB_TABLE}`
            SET {', '.join(assignments)}
            WHERE DRIVER_ID = %s
              AND ORG_ID = %s
              AND LOCATION_ID = %s
            """,
            tuple(values),
        )
        connection.commit()
        if cursor.rowcount == 0:
            cursor.execute(
                f"""
                SELECT DRIVER_ID
                FROM `{DB_TABLE}`
                WHERE DRIVER_ID = %s
                  AND ORG_ID = %s
                  AND LOCATION_ID = %s
                LIMIT 1
                """,
                (driver_id, ORG_ID, LOCATION_ID),
            )
            if cursor.fetchone() is None:
                raise RuntimeError(
                    f"Could not find BigA DRIVER_ID={driver_id} while saving Samsara tracking."
                )
    except Exception:
        connection.rollback()
        raise
    finally:
        cursor.close()
        connection.close()


def _samsara_request(method, url, *, params=None, json_body=None):
    """Call Samsara with bearer authentication and conservative retry handling."""
    headers = {
        "Authorization": f"Bearer {SAMSARA_API_TOKEN}",
        "Accept": "application/json",
    }
    if json_body is not None:
        headers["Content-Type"] = "application/json"

    last_error = None
    for attempt in range(1, SAMSARA_MAX_RETRIES + 1):
        try:
            response = requests.request(
                method,
                url,
                headers=headers,
                params=params,
                json=json_body,
                timeout=SAMSARA_REQUEST_TIMEOUT_SECONDS,
            )
        except requests.RequestException as error:
            last_error = error
            if attempt >= SAMSARA_MAX_RETRIES:
                break
            time.sleep(min(2 ** (attempt - 1), 8))
            continue

        if response.status_code == 429:
            if attempt >= SAMSARA_MAX_RETRIES:
                raise RuntimeError(
                    f"Samsara rate limit persisted after {attempt} attempts: "
                    f"{response.text[:500]}"
                )
            retry_after = clean_text(response.headers.get("Retry-After"))
            try:
                delay = max(float(retry_after), 1.0)
            except Exception:
                delay = min(2 ** (attempt - 1), 8)
            time.sleep(delay)
            continue

        if 500 <= response.status_code <= 599 and attempt < SAMSARA_MAX_RETRIES:
            time.sleep(min(2 ** (attempt - 1), 8))
            continue

        if not response.ok:
            hint = ""
            if response.status_code in {401, 403}:
                hint = " Check that the API token is valid and has Write Drivers permission for PATCH requests."
            elif response.status_code in {400, 409}:
                hint = (
                    " Check the externalIds values: each value must use supported characters "
                    "and must be unique for that external-ID key in the Samsara organization."
                )
            raise RuntimeError(
                f"Samsara {method} {url} failed with HTTP {response.status_code}: "
                f"{response.text[:1000]}.{hint}"
            )

        if not response.content:
            return {}
        try:
            return response.json()
        except ValueError as error:
            raise RuntimeError(
                f"Samsara returned non-JSON content for {method} {url}: "
                f"{response.text[:500]}"
            ) from error

    raise RuntimeError(f"Samsara request failed: {last_error}")


def fetch_all_samsara_drivers():
    """
    GET every page from /fleet/drivers and return one in-memory array.

    Samsara defaults to active drivers. If SAMSARA_INCLUDE_DEACTIVATED=true,
    a second paginated pass fetches deactivated drivers and combines them.
    """
    if not SAMSARA_ENABLED:
        return []

    statuses = ["active"]
    if SAMSARA_INCLUDE_DEACTIVATED:
        statuses.append("deactivated")

    samsara_drivers = []
    seen_ids = set()

    print("\n======================================================================")
    print("SAMSARA GET /fleet/drivers")
    print(f"Statuses: {', '.join(statuses)}")
    print("All pages will be combined into the samsara_drivers array.")
    print("======================================================================")

    for status in statuses:
        after = ""
        page_number = 0

        while True:
            page_number += 1
            params = {
                "limit": 512,
                "driverActivationStatus": status,
            }
            if after:
                params["after"] = after

            payload = _samsara_request("GET", SAMSARA_DRIVERS_URL, params=params)
            page_data = payload.get("data") or []
            if not isinstance(page_data, list):
                raise RuntimeError("Samsara /fleet/drivers response data was not an array.")

            added = 0
            for driver in page_data:
                if not isinstance(driver, dict):
                    continue
                driver_id = clean_text(driver.get("id"))
                unique_key = driver_id or repr(driver)
                if unique_key in seen_ids:
                    continue
                seen_ids.add(unique_key)
                samsara_drivers.append(driver)
                added += 1

            pagination = payload.get("pagination") or {}
            has_next = bool(pagination.get("hasNextPage"))
            after = clean_text(pagination.get("endCursor"))

            print(
                f"    {status} page {page_number}: received {len(page_data)}, "
                f"added {added}, array total={len(samsara_drivers)}, "
                f"hasNextPage={has_next}"
            )

            if not has_next:
                break
            if not after:
                raise RuntimeError(
                    "Samsara returned hasNextPage=true without an endCursor."
                )

    print(f"Samsara driver array complete: {len(samsara_drivers)} driver object(s).")
    return samsara_drivers


def _normalize_match_name(value):
    value = clean_text(value).lower()
    return re.sub(r"[^a-z0-9]+", "", value)


def _normalize_match_email(value):
    value = clean_text(value).lower()
    if re.fullmatch(r"[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}", value):
        return value
    return ""


def _normalize_match_phone(value):
    digits = re.sub(r"\D", "", clean_text(value))
    if len(digits) >= 10:
        return digits[-10:]
    return digits


def _normalize_match_license(value):
    """Normalize a driver's license number for exact cross-system comparison."""
    return re.sub(r"[^A-Za-z0-9]+", "", clean_text(value)).upper()


def _normalize_match_state(value):
    """Normalize US/Canadian license-state/province abbreviations."""
    return re.sub(r"[^A-Za-z0-9]+", "", clean_text(value)).upper()


def _collect_values_for_keys(value, wanted_tokens):
    """Collect string values from nested JSON where the key hints at email/phone."""
    found = []
    if isinstance(value, dict):
        for key, child in value.items():
            key_lower = str(key).lower()
            if any(token in key_lower for token in wanted_tokens):
                if isinstance(child, (str, int, float)):
                    found.append(str(child))
            found.extend(_collect_values_for_keys(child, wanted_tokens))
    elif isinstance(value, list):
        for child in value:
            found.extend(_collect_values_for_keys(child, wanted_tokens))
    return found


def extract_samsara_match_identity(driver):
    """Build normalized identity fields from one raw Samsara driver object."""
    display_name = clean_text(driver.get("name"))
    explicit_first = clean_text(driver.get("firstName"))
    explicit_last = clean_text(driver.get("lastName"))

    name_tokens = [token for token in re.split(r"\s+", display_name) if token]
    inferred_first = name_tokens[0] if name_tokens else ""
    inferred_last = name_tokens[-1] if len(name_tokens) >= 2 else ""

    first_name = explicit_first or inferred_first
    last_name = explicit_last or inferred_last

    email_values = _collect_values_for_keys(driver, ("email",))
    username = clean_text(driver.get("username"))
    if "@" in username:
        email_values.append(username)
    email_values.append(clean_text(driver.get("email")))

    phone_values = _collect_values_for_keys(driver, ("phone", "mobile"))
    phone_values.extend(
        [
            clean_text(driver.get("phone")),
            clean_text(driver.get("phoneNumber")),
        ]
    )

    emails = {
        normalized
        for normalized in (_normalize_match_email(value) for value in email_values)
        if normalized
    }
    phones = {
        normalized
        for normalized in (_normalize_match_phone(value) for value in phone_values)
        if normalized
    }

    license_number = clean_text(driver.get("licenseNumber"))
    license_state = clean_text(driver.get("licenseState"))

    external_ids = driver.get("externalIds") or {}
    if not isinstance(external_ids, dict):
        external_ids = {}

    return {
        "id": clean_text(driver.get("id")),
        "display_name": display_name,
        "first_name": first_name,
        "last_name": last_name,
        "first_norm": _normalize_match_name(first_name),
        "last_norm": _normalize_match_name(last_name),
        "full_norm": _normalize_match_name(display_name),
        "emails": emails,
        "phones": phones,
        "license_number": license_number,
        "license_number_norm": _normalize_match_license(license_number),
        "license_state": license_state,
        "license_state_norm": _normalize_match_state(license_state),
        "external_ids": {str(k): clean_text(v) for k, v in external_ids.items()},
    }


def score_samsara_driver_match(big_a_driver, samsara_driver):
    """
    Score one BigA/Samsara pair.

    Strong identifiers dominate. A first- or last-name-only match cannot reach
    the default patch threshold. Stored SAMSARAID is intentionally weighted above
    other identifiers so a previously confirmed BigA/Samsara link is deterministic.
    """
    identity = extract_samsara_match_identity(samsara_driver)

    employee_id = clean_text(big_a_driver.get("Paychex Employee ID"))
    stored_samsara_id = clean_text(big_a_driver.get("Samsara ID"))
    db_email = _normalize_match_email(big_a_driver.get("Email"))
    db_phone = _normalize_match_phone(big_a_driver.get("Phone"))
    db_first = _normalize_match_name(big_a_driver.get("First Name"))
    db_last = _normalize_match_name(big_a_driver.get("Last Name"))
    db_full = _normalize_match_name(
        clean_text(f"{big_a_driver.get('First Name', '')} {big_a_driver.get('Last Name', '')}")
    )

    score = 0
    reasons = []
    strong = []

    if stored_samsara_id and identity["id"] == stored_samsara_id:
        score += 150
        reasons.append("stored-samsara-id")
        strong.append("samsaraId")

    if employee_id and identity["external_ids"].get(SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY) == employee_id:
        score += 100
        reasons.append("existing-avatarEmployeeId")
        strong.append("externalEmployeeId")

    if db_email and db_email in identity["emails"]:
        score += 85
        reasons.append("email-exact")
        strong.append("email")

    if db_phone and db_phone in identity["phones"]:
        score += 80
        reasons.append("phone-exact")
        strong.append("phone")

    full_exact = bool(db_full and identity["full_norm"] and db_full == identity["full_norm"])
    if full_exact:
        score += 75
        reasons.append("full-name-exact")

    first_exact = bool(db_first and identity["first_norm"] and db_first == identity["first_norm"])
    last_exact = bool(db_last and identity["last_norm"] and db_last == identity["last_norm"])

    # Also allow first/last matching against tokens in the Samsara display name.
    samsara_name_token_norms = {
        _normalize_match_name(token)
        for token in re.split(r"\s+", identity["display_name"])
        if clean_text(token)
    }
    if db_first and (first_exact or db_first in samsara_name_token_norms):
        score += 25
        reasons.append("first-name")
    if db_last and (last_exact or db_last in samsara_name_token_norms):
        score += 30
        reasons.append("last-name")

    if db_first and db_last and db_first in samsara_name_token_norms and db_last in samsara_name_token_norms:
        score += 20
        reasons.append("first-last-combination")

    return {
        "score": score,
        "reasons": reasons,
        "strong": strong,
        "identity": identity,
        "driver": samsara_driver,
    }


def _choose_standard_samsara_match(big_a_driver, samsara_drivers):
    """Run the normal Samsara identity matcher without license-number fallback."""
    scored = [score_samsara_driver_match(big_a_driver, driver) for driver in samsara_drivers]
    scored = [item for item in scored if item["score"] > 0]
    scored.sort(key=lambda item: item["score"], reverse=True)

    if not scored:
        return None, "NO_MATCH", []

    top = scored[0]
    runner_up = scored[1] if len(scored) > 1 else None

    if top["score"] < SAMSARA_MATCH_MIN_SCORE:
        return None, f"LOW_SCORE_{top['score']}", scored[:5]

    if "samsaraId" in top["strong"]:
        return top, "MATCH_STORED_SAMSARA_ID", scored[:5]

    if "externalEmployeeId" in top["strong"]:
        return top, "MATCH_EXTERNAL_EMPLOYEE_ID", scored[:5]

    # An exact email/phone is accepted only when it is unique among the scored candidates.
    if "email" in top["strong"] or "phone" in top["strong"]:
        competing_strong = [
            item
            for item in scored[1:]
            if ("email" in top["strong"] and "email" in item["strong"])
            or ("phone" in top["strong"] and "phone" in item["strong"])
        ]
        if not competing_strong:
            return top, "MATCH_STRONG_IDENTIFIER", scored[:5]

    if runner_up is not None:
        margin = top["score"] - runner_up["score"]
        if margin < SAMSARA_MATCH_MIN_MARGIN:
            return None, f"AMBIGUOUS_MARGIN_{margin}", scored[:5]

    return top, "MATCH_HIGHEST_SCORE", scored[:5]


def _license_fallback_samsara_match(big_a_driver, samsara_drivers):
    """
    Fallback only: match an otherwise-unresolved BigA driver by exact license number.

    Samsara documents licenseNumber as a driver-matching identifier. Because the
    same number could theoretically exist in different issuing states, licenseState
    is used to disambiguate whenever BigA has STATE populated.
    """
    db_license = _normalize_match_license(big_a_driver.get("License Number"))
    db_state = _normalize_match_state(big_a_driver.get("License State"))

    if not db_license:
        return None, "NO_LICENSE_NUMBER", []

    matches = []
    for driver in samsara_drivers:
        identity = extract_samsara_match_identity(driver)
        if identity["license_number_norm"] != db_license:
            continue

        # Start with a strong exact-license score. Add state/name evidence to make
        # audit scores/reasons more informative, but exact license is the gate.
        score = 95
        reasons = ["license-number-exact"]
        strong = ["licenseNumber"]

        state_exact = bool(
            db_state
            and identity["license_state_norm"]
            and db_state == identity["license_state_norm"]
        )
        if state_exact:
            score += 20
            reasons.append("license-state-exact")
            strong.append("licenseState")

        db_first = _normalize_match_name(big_a_driver.get("First Name"))
        db_last = _normalize_match_name(big_a_driver.get("Last Name"))
        if db_first and db_first == identity["first_norm"]:
            score += 10
            reasons.append("first-name")
        if db_last and db_last == identity["last_norm"]:
            score += 10
            reasons.append("last-name")

        matches.append(
            {
                "score": score,
                "reasons": reasons,
                "strong": strong,
                "identity": identity,
                "driver": driver,
            }
        )

    matches.sort(key=lambda item: item["score"], reverse=True)
    if not matches:
        return None, "LICENSE_NO_MATCH", []

    # Exact license number is sufficient only if it identifies one Samsara driver.
    if len(matches) == 1:
        status = (
            "MATCH_LICENSE_NUMBER_STATE"
            if "licenseState" in matches[0]["strong"]
            else "MATCH_LICENSE_NUMBER"
        )
        return matches[0], status, matches[:5]

    # If the number appears more than once, state must uniquely resolve it.
    if db_state:
        state_matches = [
            item
            for item in matches
            if item["identity"]["license_state_norm"] == db_state
        ]
        if len(state_matches) == 1:
            return state_matches[0], "MATCH_LICENSE_NUMBER_STATE", matches[:5]
        if len(state_matches) > 1:
            return None, "AMBIGUOUS_LICENSE_NUMBER_STATE", matches[:5]

    # Do not guess if the license number alone maps to multiple Samsara drivers.
    return None, "AMBIGUOUS_LICENSE_NUMBER", matches[:5]


def choose_best_samsara_match(big_a_driver, samsara_drivers):
    """
    Match using normal identifiers first. Only when no safe normal match exists,
    fall back to exact Samsara licenseNumber (plus licenseState when available).
    """
    best, status, candidates = _choose_standard_samsara_match(
        big_a_driver,
        samsara_drivers,
    )
    if best is not None:
        return best, status, candidates

    license_best, license_status, license_candidates = _license_fallback_samsara_match(
        big_a_driver,
        samsara_drivers,
    )
    if license_best is not None:
        return license_best, license_status, license_candidates

    # Preserve license-fallback diagnostics when a license was available, otherwise
    # retain the original matcher result/status.
    if license_status not in {"NO_LICENSE_NUMBER", "LICENSE_NO_MATCH"}:
        return None, license_status, license_candidates or candidates

    return None, status, candidates


def build_samsara_company_external_id(employee_id):
    """Build a valid and unique company-origin marker for Samsara externalIds."""
    prefix = re.sub(r"[^A-Za-z0-9@._%+\-]", "", SAMSARA_COMPANY_ID_PREFIX)
    employee_id = re.sub(r"[^A-Za-z0-9@._%+\-]", "", clean_text(employee_id))
    if not prefix or not employee_id:
        raise ValueError("Cannot build avatarCompanyId without a valid prefix and employee ID.")
    return f"{prefix}-{employee_id}"


def _unwrap_samsara_driver_response(payload):
    """Return a driver dictionary from either {'data': {...}} or a direct object."""
    if isinstance(payload, dict):
        data = payload.get("data")
        if isinstance(data, dict):
            return data
        return payload
    return {}


def _verify_samsara_external_ids(samsara_driver_id, expected):
    """GET the driver after PATCH and confirm both external IDs are persisted."""
    verify_url = f"{SAMSARA_DRIVERS_URL}/{samsara_driver_id}"
    last_actual = {}
    last_driver = {}

    for attempt in range(1, max(SAMSARA_VERIFY_RETRIES, 1) + 1):
        payload = _samsara_request("GET", verify_url)
        driver = _unwrap_samsara_driver_response(payload)
        external_ids = driver.get("externalIds") or {}
        if not isinstance(external_ids, dict):
            external_ids = {}

        actual = {
            key: clean_text(external_ids.get(key))
            for key in expected
        }
        verified = all(actual.get(key) == value for key, value in expected.items())
        last_actual = actual
        last_driver = driver

        if SAMSARA_PATCH_DEBUG:
            print(
                f"        VERIFY GET {attempt}/{max(SAMSARA_VERIFY_RETRIES, 1)} "
                f"{verify_url}"
            )
            print(f"        VERIFY expected externalIds={expected}")
            print(f"        VERIFY actual   externalIds={actual}")

        if verified:
            return True, actual, driver

        if attempt < max(SAMSARA_VERIFY_RETRIES, 1):
            time.sleep(max(SAMSARA_VERIFY_WAIT_MS, 0) / 1000.0)

    return False, last_actual, last_driver


def assert_biga_samsara_patch_gate(driver_id, samsara_driver_id):
    """
    Hard safety gate before any Samsara external-ID update.

    A PATCH is allowed only when the exact BigA row has already been confirmed as
    FOUND_IN_SAMSARA='Y' and its stored SAMSARAID exactly matches the Samsara
    driver that is about to be updated.
    """
    if driver_id is None:
        raise RuntimeError("Samsara PATCH blocked: BigA DRIVER_ID is missing.")

    samsara_driver_id = clean_text(samsara_driver_id)
    if not samsara_driver_id:
        raise RuntimeError("Samsara PATCH blocked: matched Samsara driver ID is missing.")

    connection = get_db_connection()
    cursor = connection.cursor(dictionary=True)
    try:
        cursor.execute(
            f"""
            SELECT FOUND_IN_SAMSARA, SAMSARAID
            FROM `{DB_TABLE}`
            WHERE DRIVER_ID = %s
              AND ORG_ID = %s
              AND LOCATION_ID = %s
            LIMIT 1
            """,
            (driver_id, ORG_ID, LOCATION_ID),
        )
        row = cursor.fetchone()
    finally:
        cursor.close()
        connection.close()

    if not row:
        raise RuntimeError(
            f"Samsara PATCH blocked: BigA DRIVER_ID={driver_id} was not found in the configured scope."
        )

    found_flag = clean_text(row.get("FOUND_IN_SAMSARA")).upper()
    stored_samsara_id = clean_text(row.get("SAMSARAID"))

    if found_flag != "Y":
        raise RuntimeError(
            f"Samsara PATCH blocked for DRIVER_ID={driver_id}: "
            f"FOUND_IN_SAMSARA={found_flag or '<blank>'}; expected Y."
        )

    if not stored_samsara_id:
        raise RuntimeError(
            f"Samsara PATCH blocked for DRIVER_ID={driver_id}: SAMSARAID is blank."
        )

    if stored_samsara_id != samsara_driver_id:
        raise RuntimeError(
            f"Samsara PATCH blocked for DRIVER_ID={driver_id}: "
            f"BigA SAMSARAID={stored_samsara_id!r} does not match "
            f"the matched Samsara driver ID={samsara_driver_id!r}."
        )

    print(
        f"        SAMSARA PATCH GATE PASSED: DRIVER_ID={driver_id} | "
        f"FOUND_IN_SAMSARA=Y | SAMSARAID={stored_samsara_id}"
    )
    return True


def patch_samsara_driver_external_ids(samsara_driver, employee_id, *, biga_driver_id):
    samsara_driver_id = clean_text(samsara_driver.get("id"))
    if not samsara_driver_id:
        raise ValueError("Matched Samsara driver has no Samsara ID.")

    # Never update external IDs unless the exact BigA row is already marked as
    # found in Samsara and points to this same Samsara driver ID.
    assert_biga_samsara_patch_gate(biga_driver_id, samsara_driver_id)

    employee_value = clean_text(employee_id)
    if not employee_value:
        raise ValueError("Cannot update Samsara because Paychex Employee ID is blank.")

    company_value = build_samsara_company_external_id(employee_value)
    expected = {
        SAMSARA_EMPLOYEE_EXTERNAL_ID_KEY: employee_value,
        SAMSARA_COMPANY_EXTERNAL_ID_KEY: company_value,
    }

    existing = samsara_driver.get("externalIds") or {}
    if not isinstance(existing, dict):
        existing = {}

    if all(clean_text(existing.get(key)) == value for key, value in expected.items()):
        print(
            f"        Samsara external IDs already current for driver {samsara_driver_id}: "
            f"{expected}"
        )
        return {
            "status": "ALREADY_CURRENT",
            "samsara_driver_id": samsara_driver_id,
            "external_ids": expected,
            "verified_external_ids": expected,
            "response": None,
        }

    patch_url = f"{SAMSARA_DRIVERS_URL}/{samsara_driver_id}"
    payload = {"externalIds": expected}

    if SAMSARA_PATCH_DEBUG:
        print(f"        SAMSARA PATCH MODE: {'DRY RUN' if SAMSARA_DRY_RUN else 'LIVE'}")
        print(f"        SAMSARA PATCH URL: {patch_url}")
        print(f"        SAMSARA PATCH PAYLOAD: {payload}")
        print(
            "        NOTE: Authorization bearer token is configured but intentionally "
            "not printed."
        )

    if SAMSARA_DRY_RUN:
        print(
            f"        DRY RUN ONLY - no HTTP PATCH sent for Samsara driver "
            f"{samsara_driver_id}."
        )
        return {
            "status": "DRY_RUN_WOULD_PATCH",
            "samsara_driver_id": samsara_driver_id,
            "external_ids": expected,
            "verified_external_ids": {},
            "response": None,
        }

    try:
        response = _samsara_request(
            "PATCH",
            patch_url,
            json_body=payload,
        )
    except Exception as error:
        raise RuntimeError(
            f"Samsara PATCH failed for driver {samsara_driver_id}. "
            f"Payload externalIds={expected}. Error: {error}"
        ) from error

    if SAMSARA_PATCH_DEBUG:
        response_driver = _unwrap_samsara_driver_response(response)
        response_external_ids = response_driver.get("externalIds") or {}
        print(
            f"        SAMSARA PATCH HTTP SUCCESS. Response externalIds="
            f"{response_external_ids if isinstance(response_external_ids, dict) else '<not-a-map>'}"
        )

    # A 2xx response is not enough for this workflow. Verify the values by reading
    # the driver back from Samsara before BigA is allowed to mark UPDATED_IN_SAMSARA=Y.
    if SAMSARA_VERIFY_PATCH:
        verified, actual, verified_driver = _verify_samsara_external_ids(
            samsara_driver_id,
            expected,
        )
        if not verified:
            raise RuntimeError(
                "Samsara PATCH returned success but verification GET did not show "
                f"the requested external IDs. expected={expected}; actual={actual}. "
                "Confirm the API token has Write Drivers permission and that the "
                "external-ID values are unique in the Samsara organization."
            )
        # Keep the in-memory array current for the remainder of this run.
        samsara_driver["externalIds"] = dict(
            (verified_driver.get("externalIds") or {})
            if isinstance(verified_driver.get("externalIds") or {}, dict)
            else expected
        )
        return {
            "status": "PATCHED_AND_VERIFIED",
            "samsara_driver_id": samsara_driver_id,
            "external_ids": expected,
            "verified_external_ids": actual,
            "response": response,
        }

    # Verification may be disabled explicitly, but the normal/default path above
    # always verifies before reporting a confirmed update.
    samsara_driver.setdefault("externalIds", {}).update(expected)
    return {
        "status": "PATCHED_UNVERIFIED",
        "samsara_driver_id": samsara_driver_id,
        "external_ids": expected,
        "verified_external_ids": {},
        "response": response,
    }


def run_samsara_driver_sync():
    """
    Fetch the Samsara driver API into an array, match BigA drivers one-by-one,
    and PATCH external IDs only for high-confidence, unambiguous matches.
    """
    if not SAMSARA_ENABLED:
        print("\nSamsara sync disabled.")
        return [], [], []

    big_a_drivers = load_drivers_for_samsara_sync()
    samsara_drivers = fetch_all_samsara_drivers()
    results = []

    print("\n======================================================================")
    print("SAMSARA MATCH + EXTERNAL ID SYNC")
    print(f"BigA drivers to evaluate: {len(big_a_drivers)}")
    print(f"Samsara drivers in array: {len(samsara_drivers)}")
    print(f"Minimum score: {SAMSARA_MATCH_MIN_SCORE}")
    print(f"Minimum non-strong margin: {SAMSARA_MATCH_MIN_MARGIN}")
    print(f"PATCH mode: {'DRY RUN' if SAMSARA_DRY_RUN else 'LIVE'}")
    print("======================================================================")

    for index, db_driver in enumerate(big_a_drivers, start=1):
        display_name = clean_text(db_driver.get("Display Name"))
        employee_id = clean_text(db_driver.get("Paychex Employee ID"))
        driver_id = db_driver.get("DB Driver ID")

        print("\n----------------------------------------------------------------------")
        print(
            f"SAMSARA MATCH {index}/{len(big_a_drivers)}: "
            f"{display_name or '<no name>'} | DRIVER_ID={driver_id} | "
            f"EmployeeID={employee_id} | "
            f"License={clean_text(db_driver.get('License Number')) or '<missing>'}"
        )

        best, match_status, top_candidates = choose_best_samsara_match(
            db_driver,
            samsara_drivers,
        )

        audit_score = int(
            best["score"]
            if best
            else max((item.get("score", 0) for item in top_candidates), default=0)
        )
        audit_evidence = best if best else (
            max(top_candidates, key=lambda item: int(item.get("score") or 0))
            if top_candidates else None
        )
        audit_reason = _compact_match_reason(
            match_status,
            audit_evidence.get("reasons") if audit_evidence else None,
            context=(
                f"samsaraId={audit_evidence['identity']['id']}"
                if audit_evidence and audit_evidence.get("identity") else None
            ),
        )

        result = {
            "DB Driver ID": driver_id,
            "First Name": db_driver.get("First Name"),
            "Last Name": db_driver.get("Last Name"),
            "Email": db_driver.get("Email"),
            "Phone": db_driver.get("Phone"),
            "License Number": db_driver.get("License Number"),
            "License State": db_driver.get("License State"),
            "Paychex Employee ID": employee_id,
            "match_status": match_status,
            "match_score": audit_score,
            "match_reason": audit_reason,
            "samsara_driver_id": None,
            "samsara_driver_name": None,
            "found_in_samsara": "",
            "updated_in_samsara": "",
            "patch_status": "NOT_ATTEMPTED",
            "top_candidates": [
                {
                    "id": item["identity"]["id"],
                    "name": item["identity"]["display_name"],
                    "score": item["score"],
                    "reasons": item["reasons"],
                }
                for item in top_candidates
            ],
        }

        if best is None:
            try:
                update_biga_samsara_tracking(
                    driver_id,
                    match_score=audit_score,
                    match_reason=audit_reason,
                )
                print(
                    f"    BIGA SAMSARA AUDIT: score={audit_score} | reason={audit_reason}"
                )
            except Exception as error:
                result["biga_match_audit_error"] = str(error)
                print(f"    BIGA SAMSARA AUDIT ERROR: {error}")

            print(f"    NO PATCH: {match_status}")
            for candidate in result["top_candidates"][:3]:
                print(
                    f"        candidate id={candidate['id']} name={candidate['name']!r} "
                    f"score={candidate['score']} reasons={candidate['reasons']}"
                )
            results.append(result)
            continue

        matched_driver = best["driver"]
        result["samsara_driver_id"] = best["identity"]["id"]
        result["samsara_driver_name"] = best["identity"]["display_name"]

        print(
            f"    MATCH: Samsara ID={result['samsara_driver_id']} | "
            f"Name={result['samsara_driver_name']} | Score={best['score']} | "
            f"Reasons={best['reasons']}"
        )

        # A safe match is enough to persist FOUND_IN_SAMSARA=Y and SAMSARAID,
        # including during dry-run testing. This does not claim that Samsara was updated.
        try:
            update_biga_samsara_tracking(
                driver_id,
                samsara_driver_id=result["samsara_driver_id"],
                found=True,
                match_score=audit_score,
                match_reason=audit_reason,
            )
            result["found_in_samsara"] = "Y"
            print(
                f"    BIGA SAMSARA MATCH SAVED: DRIVER_ID={driver_id} | "
                f"FOUND_IN_SAMSARA=Y | SAMSARAID={result['samsara_driver_id']}"
            )
        except Exception as error:
            result["patch_status"] = "BIGA_MATCH_TRACKING_ERROR"
            result["error"] = str(error)
            print(f"    BIGA SAMSARA TRACKING ERROR: {error}")
            results.append(result)
            continue

        # External IDs are allowed only after this exact row was safely matched
        # and FOUND_IN_SAMSARA/SAMSARAID were successfully persisted in BigA.
        if result.get("found_in_samsara") != "Y" or not clean_text(result.get("samsara_driver_id")):
            result["patch_status"] = "PATCH_BLOCKED_NOT_FOUND_IN_SAMSARA"
            result["error"] = (
                "External-ID update blocked because this BigA row was not confirmed "
                "as FOUND_IN_SAMSARA=Y with a valid SAMSARAID."
            )
            print(f"    SAMSARA PATCH BLOCKED: {result['error']}")
            results.append(result)
            continue

        try:
            patch_result = patch_samsara_driver_external_ids(
                matched_driver,
                employee_id,
                biga_driver_id=driver_id,
            )
            result["patch_status"] = patch_result["status"]
            result["external_ids"] = patch_result["external_ids"]
            print(f"    SAMSARA UPDATE STATUS: {patch_result['status']}")
        except Exception as error:
            result["patch_status"] = "PATCH_ERROR"
            result["error"] = str(error)
            print(f"    SAMSARA PATCH ERROR: {error}")
            results.append(result)
            continue

        # BigA is marked UPDATED_IN_SAMSARA=Y only when the desired IDs were
        # already present or a live PATCH was followed by a successful verification GET.
        # Dry-run and unverified writes intentionally do not set the flag.
        if patch_result["status"] in {"PATCHED_AND_VERIFIED", "ALREADY_CURRENT"}:
            try:
                update_biga_samsara_tracking(
                    driver_id,
                    samsara_driver_id=result["samsara_driver_id"],
                    found=True,
                    updated=True,
                    match_score=audit_score,
                    match_reason=audit_reason,
                )
                result["updated_in_samsara"] = "Y"
                print(
                    f"    BIGA SAMSARA UPDATE SAVED: DRIVER_ID={driver_id} | "
                    "UPDATED_IN_SAMSARA=Y"
                )
            except Exception as error:
                result["biga_update_tracking_error"] = str(error)
                print(
                    "    BIGA UPDATED_IN_SAMSARA TRACKING ERROR: "
                    f"{error} (Samsara status remains {patch_result['status']})"
                )

        results.append(result)

    print("\n======================================================================")
    print("SAMSARA PHASE COMPLETE")
    print(f"Raw Samsara array size: {len(samsara_drivers)}")
    print(f"BigA rows evaluated: {len(big_a_drivers)}")
    print(
        "Matched: "
        f"{sum(1 for item in results if item.get('samsara_driver_id'))}"
    )
    print(
        "Found in Samsara and saved to BigA: "
        f"{sum(1 for item in results if item.get('found_in_samsara') == 'Y')}"
    )
    print(
        "Confirmed updated/current in Samsara and saved to BigA: "
        f"{sum(1 for item in results if item.get('updated_in_samsara') == 'Y')}"
    )
    print(
        "Patched+verified/already-current/dry-run: "
        f"{sum(1 for item in results if item.get('patch_status') in {'PATCHED_AND_VERIFIED', 'ALREADY_CURRENT', 'DRY_RUN_WOULD_PATCH'})}"
    )
    print(
        "Unmatched/ambiguous: "
        f"{sum(1 for item in results if not item.get('samsara_driver_id'))}"
    )
    print("======================================================================")

    return big_a_drivers, samsara_drivers, results


# ============================================================


def _decode_jwt_payload_without_verification(access_token):
    """Decode token claims only for diagnostics/permission validation; never log the token."""
    try:
        parts = clean_text(access_token).split(".")
        if len(parts) < 2:
            return {}
        payload = parts[1]
        payload += "=" * (-len(payload) % 4)
        decoded = base64.urlsafe_b64decode(payload.encode("ascii"))
        value = json.loads(decoded.decode("utf-8"))
        return value if isinstance(value, dict) else {}
    except Exception:
        return {}


def _m365_token_url():
    tenant = quote(M365_TENANT_ID, safe=".-")
    return f"https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token"


def _get_graph_access_token(force_refresh=False):
    """Acquire an app-only Microsoft Graph token using OAuth 2.0 client credentials."""
    now = time.time()
    cached = clean_text(_GRAPH_ACCESS_TOKEN_CACHE.get("access_token"))
    expires_at = float(_GRAPH_ACCESS_TOKEN_CACHE.get("expires_at") or 0)
    if not force_refresh and cached and expires_at > now + 120:
        return cached

    response = requests.post(
        _m365_token_url(),
        data={
            "client_id": M365_CLIENT_ID,
            "client_secret": M365_CLIENT_SECRET,
            "scope": M365_GRAPH_SCOPE,
            "grant_type": "client_credentials",
        },
        timeout=M365_GRAPH_TIMEOUT_SECONDS,
    )

    try:
        body = response.json()
    except Exception:
        body = {}

    if response.status_code != 200:
        error_code = clean_text(body.get("error")) or f"HTTP_{response.status_code}"
        error_description = clean_text(body.get("error_description")) or clean_text(response.text)[:500]
        raise RuntimeError(
            "Microsoft Graph OAuth token request failed: "
            f"{error_code}: {error_description}"
        )

    access_token = clean_text(body.get("access_token"))
    if not access_token:
        raise RuntimeError("Microsoft Graph token response did not contain access_token.")

    claims = _decode_jwt_payload_without_verification(access_token)
    roles = claims.get("roles") or []
    if isinstance(roles, str):
        roles = [roles]
    if "Mail.Send" not in roles:
        raise RuntimeError(
            "Microsoft Graph access token does not contain the Mail.Send application role. "
            "In Entra ID, add Microsoft Graph -> Application permissions -> Mail.Send, "
            "then Grant admin consent and rerun."
        )

    try:
        expires_in = int(body.get("expires_in") or 3599)
    except (TypeError, ValueError):
        expires_in = 3599
    _GRAPH_ACCESS_TOKEN_CACHE["access_token"] = access_token
    _GRAPH_ACCESS_TOKEN_CACHE["expires_at"] = now + max(expires_in, 300)
    return access_token


def _smtp_recipient_addresses(value):
    recipients = []
    seen = set()
    for raw in re.split(r"[,;]", clean_text(value)):
        address = raw.strip()
        key = address.lower()
        if not address or key in seen:
            continue
        seen.add(key)
        recipients.append(address)
    return recipients


def _open_smtp_connection():
    """Open the configured SMTP connection; authentication is optional."""
    if EMAIL_SMTP_SECURITY not in {"none", "starttls", "ssl"}:
        raise RuntimeError("EMAIL_SMTP_SECURITY must be one of: none, starttls, ssl.")

    context = ssl.create_default_context()
    if EMAIL_SMTP_SECURITY == "ssl":
        smtp = smtplib.SMTP_SSL(
            EMAIL_SMTP_HOST, EMAIL_SMTP_PORT,
            timeout=EMAIL_SMTP_TIMEOUT_SECONDS, context=context,
        )
    else:
        smtp = smtplib.SMTP(
            EMAIL_SMTP_HOST, EMAIL_SMTP_PORT,
            timeout=EMAIL_SMTP_TIMEOUT_SECONDS,
        )
        smtp.ehlo()
        if EMAIL_SMTP_SECURITY == "starttls":
            smtp.starttls(context=context)
            smtp.ehlo()

    if EMAIL_SMTP_USERNAME:
        smtp.login(EMAIL_SMTP_USERNAME, EMAIL_SMTP_PASSWORD)
    return smtp


def _validate_smtp_configuration():
    missing = []
    for label, value in (("EMAIL_TO", EMAIL_TO), ("EMAIL_FROM", EMAIL_FROM), ("EMAIL_SMTP_HOST", EMAIL_SMTP_HOST)):
        if not value:
            missing.append(label)
    if EMAIL_SMTP_USERNAME and not EMAIL_SMTP_PASSWORD:
        missing.append("EMAIL_SMTP_PASSWORD")
    if missing:
        raise RuntimeError(
            "SMTP email notifications are enabled but required settings are missing: "
            + ", ".join(missing)
        )
    if EMAIL_SMTP_SECURITY not in {"none", "starttls", "ssl"}:
        raise RuntimeError("EMAIL_SMTP_SECURITY must be one of: none, starttls, ssl.")

    print(
        "SMTP email notifications configured. "
        f"From: {EMAIL_FROM}; To: {EMAIL_TO}; SMTP={EMAIL_SMTP_HOST}:{EMAIL_SMTP_PORT}; "
        f"security={EMAIL_SMTP_SECURITY}; auth={'YES' if EMAIL_SMTP_USERNAME else 'NO'}."
    )
    if EMAIL_SMTP_PREFLIGHT:
        print("Checking SMTP connectivity before Avalon processing...")
        smtp = _open_smtp_connection()
        try:
            code, message = smtp.noop()
            if int(code) >= 400:
                raise RuntimeError(f"SMTP NOOP failed: {code} {message!r}")
        finally:
            try:
                smtp.quit()
            except Exception:
                try:
                    smtp.close()
                except Exception:
                    pass
        print("SMTP connectivity preflight successful.")


def _validate_graph_configuration():
    missing = []
    for label, value in (
        ("EMAIL_TO", EMAIL_TO), ("EMAIL_FROM", EMAIL_FROM),
        ("M365_TENANT_ID", M365_TENANT_ID), ("M365_CLIENT_ID", M365_CLIENT_ID),
        ("M365_CLIENT_SECRET", M365_CLIENT_SECRET),
    ):
        if not value:
            missing.append(label)
    if missing:
        raise RuntimeError(
            "Microsoft Graph email notifications are enabled but required settings are missing: "
            + ", ".join(missing)
        )
    if M365_GRAPH_SCOPE != "https://graph.microsoft.com/.default":
        print("WARNING: M365_GRAPH_SCOPE is not the normal Graph client-credentials scope: " + M365_GRAPH_SCOPE)
    print(
        "Microsoft Graph email notifications configured. "
        f"From: {EMAIL_FROM}; To: {EMAIL_TO}; tenant={M365_TENANT_ID}; Graph={M365_GRAPH_BASE_URL}."
    )
    print("Checking Microsoft Graph OAuth credentials and Mail.Send application permission...")
    _get_graph_access_token(force_refresh=True)
    print("Microsoft Graph OAuth preflight successful; Mail.Send application role is present.")


def validate_email_requirements():
    """Validate selected email transport before the automation starts."""
    if not EMAIL_NOTIFICATIONS_ENABLED:
        print("Completion email notifications are disabled.")
        return

    for label, value in (("EMAIL_TO", EMAIL_TO), ("EMAIL_FROM", EMAIL_FROM)):
        for address in re.split(r"[,;]", value):
            address = address.strip()
            if not address or not re.fullmatch(r"[^@\s]+@[^@\s]+\.[^@\s]+", address):
                raise RuntimeError(f"{label} does not look like a valid email address: {address!r}")

    if EMAIL_TRANSPORT == "smtp":
        _validate_smtp_configuration()
    else:
        _validate_graph_configuration()

def _email_text(value):
    value = clean_text(value)
    return value if value else "-"


def _build_paychex_email_rows(
    paychex_results,
    inserted_driver_ids,
    remaining_unresolved_drivers,
):
    """
    Build the two completion-email datasets.

    Mapped rows come from successful Paychex updates during THIS run.
    Unresolved rows come from a fresh database query AFTER Paychex, so the
    database is the authoritative source for which employee IDs are still
    missing at completion time.
    """
    mapped = []
    unresolved = []

    result_by_driver_id = {
        item.get("driver_id"): item
        for item in paychex_results
        if item.get("driver_id") is not None
    }

    for item in paychex_results:
        driver_id = item.get("driver_id")
        employee_id = clean_text(item.get("employee_id"))
        status = clean_text(item.get("status"))
        if status != "FOUND_AND_SAVED" or not employee_id:
            continue

        mapped.append(
            {
                "driver_id": driver_id,
                "name": clean_text(
                    item.get("display_name")
                    or f"{item.get('first_name', '')} {item.get('last_name', '')}"
                ),
                "email": clean_text(item.get("email")),
                "doj": clean_text(item.get("doj")),
                "employee_id": employee_id,
                "status": status,
                "search_term": clean_text(item.get("search_term")),
                "new_this_run": driver_id in inserted_driver_ids,
                "error": clean_text(item.get("error")),
            }
        )

    for record in remaining_unresolved_drivers:
        driver_id = record.get("DB Driver ID")
        result = result_by_driver_id.get(driver_id, {})
        first_name = clean_text(record.get("First Name"))
        last_name = clean_text(record.get("Last Name"))
        status = clean_text(result.get("status")) or "STILL_EMPTY_AFTER_RUN"

        unresolved.append(
            {
                "driver_id": driver_id,
                "name": clean_text(
                    record.get("Display Name") or f"{first_name} {last_name}"
                ),
                "email": clean_text(record.get("Email")),
                "doj": clean_text(record.get("DOJ")),
                "employee_id": "",
                "status": status,
                "search_term": clean_text(result.get("search_term")),
                "new_this_run": driver_id in inserted_driver_ids,
                "error": clean_text(result.get("error")),
            }
        )

    return mapped, unresolved


def _render_unresolved_email(unresolved_rows):
    count = len(unresolved_rows)
    plain = [
        "Avatar Driver Hub / Paychex completion notification",
        "",
        f"Drivers without a Paychex Employee ID after this run: {count}",
        "",
    ]

    html_rows = []
    for row in unresolved_rows:
        source = "New this run" if row["new_this_run"] else "Existing Driver Hub record"
        status = row["status"] or "UNRESOLVED"
        details = row["error"] or row["search_term"] or "-"
        plain.append(
            f"DRIVER_ID={_email_text(row['driver_id'])} | "
            f"Name={_email_text(row['name'])} | "
            f"Email={_email_text(row['email'])} | "
            f"DOJ={_email_text(row['doj'])} | "
            f"Status={status} | Source={source} | Details={details}"
        )
        html_rows.append(
            "<tr>"
            f"<td>{escape(_email_text(row['driver_id']))}</td>"
            f"<td>{escape(_email_text(row['name']))}</td>"
            f"<td>{escape(_email_text(row['email']))}</td>"
            f"<td>{escape(_email_text(row['doj']))}</td>"
            f"<td>{escape(status)}</td>"
            f"<td>{escape(source)}</td>"
            f"<td>{escape(details)}</td>"
            "</tr>"
        )

    if not unresolved_rows:
        plain.append("No unresolved drivers. All checked drivers have a Paychex Employee ID.")
        html_rows.append(
            '<tr><td colspan="7">No unresolved drivers. All checked drivers have a Paychex Employee ID.</td></tr>'
        )

    html_body = f"""
    <html>
      <body style="font-family:Arial,Helvetica,sans-serif;color:#1f2937;">
        <h2>New Driver Created in Avatar Driver Hub</h2>
        <p>Drivers without a Paychex Employee ID after this automation run: <strong>{count}</strong></p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Driver ID</th><th>Name</th><th>Email</th><th>DOJ</th>
              <th>Paychex Status</th><th>Source</th><th>Search/Error Details</th>
            </tr>
          </thead>
          <tbody>{''.join(html_rows)}</tbody>
        </table>
        <p style="font-size:12px;color:#6b7280;">Generated automatically after Avalon Driver Hub and Paychex processing.</p>
      </body>
    </html>
    """
    return "\n".join(plain), html_body


def _render_mapped_email(mapped_rows):
    count = len(mapped_rows)
    plain = [
        "Avatar Driver Hub / Paychex completion notification",
        "",
        f"Employees mapped to a Paychex Employee ID during this run: {count}",
        "",
    ]

    html_rows = []
    for row in mapped_rows:
        source = "New this run" if row["new_this_run"] else "Existing Driver Hub record"
        plain.append(
            f"DRIVER_ID={_email_text(row['driver_id'])} | "
            f"Name={_email_text(row['name'])} | "
            f"Email={_email_text(row['email'])} | "
            f"DOJ={_email_text(row['doj'])} | "
            f"PAYCHEX_EMPLOYEE_ID={_email_text(row['employee_id'])} | "
            f"Source={source}"
        )
        html_rows.append(
            "<tr>"
            f"<td>{escape(_email_text(row['driver_id']))}</td>"
            f"<td>{escape(_email_text(row['name']))}</td>"
            f"<td>{escape(_email_text(row['email']))}</td>"
            f"<td>{escape(_email_text(row['doj']))}</td>"
            f"<td>{escape(_email_text(row['employee_id']))}</td>"
            f"<td>{escape(source)}</td>"
            "</tr>"
        )

    if not mapped_rows:
        plain.append("No Paychex employee IDs were newly mapped during this run.")
        html_rows.append(
            '<tr><td colspan="6">No Paychex employee IDs were newly mapped during this run.</td></tr>'
        )

    html_body = f"""
    <html>
      <body style="font-family:Arial,Helvetica,sans-serif;color:#1f2937;">
        <h2>Driver Hub Employees Mapped with Paychex Employee ID</h2>
        <p>Employees successfully mapped to a Paychex Employee ID during this automation run: <strong>{count}</strong></p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Driver ID</th><th>Name</th><th>Email</th><th>DOJ</th>
              <th>Paychex Employee ID</th><th>Source</th>
            </tr>
          </thead>
          <tbody>{''.join(html_rows)}</tbody>
        </table>
        <p style="font-size:12px;color:#6b7280;">Generated automatically after Avalon Driver Hub and Paychex processing.</p>
      </body>
    </html>
    """
    return "\n".join(plain), html_body


def _graph_recipient_addresses(value):
    recipients = []
    seen = set()
    for raw in re.split(r"[,;]", clean_text(value)):
        address = raw.strip()
        key = address.lower()
        if not address or key in seen:
            continue
        seen.add(key)
        recipients.append(address)
    return recipients


def _send_email_via_smtp(subject, plain_body, html_body):
    """Send one message through the configured SMTP relay."""
    recipients = _smtp_recipient_addresses(EMAIL_TO)
    if not recipients:
        raise RuntimeError("EMAIL_TO contains no usable recipient addresses.")

    message = EmailMessage()
    message["Subject"] = subject
    message["From"] = EMAIL_FROM
    message["To"] = ", ".join(recipients)
    message.set_content(plain_body)
    message.add_alternative(html_body, subtype="html")

    smtp = _open_smtp_connection()
    try:
        refused = smtp.send_message(message, from_addr=EMAIL_FROM, to_addrs=recipients)
        if refused:
            raise RuntimeError(f"SMTP server refused one or more recipients: {refused}")
    finally:
        try:
            smtp.quit()
        except Exception:
            try:
                smtp.close()
            except Exception:
                pass

    return {"provider": "SMTP", "host": EMAIL_SMTP_HOST, "port": EMAIL_SMTP_PORT}


def _send_email_via_microsoft_graph(subject, plain_body, html_body):
    """Send one message as EMAIL_FROM via Microsoft Graph /users/{sender}/sendMail."""
    access_token = _get_graph_access_token()
    sender_path = quote(EMAIL_FROM, safe="")
    url = f"{M365_GRAPH_BASE_URL}/users/{sender_path}/sendMail"

    recipients = _graph_recipient_addresses(EMAIL_TO)
    if not recipients:
        raise RuntimeError("EMAIL_TO contains no usable recipient addresses.")

    # Graph's JSON sendMail API accepts one body representation. HTML is used
    # because this report contains tables; the HTML contains the same information
    # as the plain-text rendering built by the report function.
    payload = {
        "message": {
            "subject": subject,
            "body": {
                "contentType": "HTML",
                "content": html_body,
            },
            "toRecipients": [
                {"emailAddress": {"address": address}}
                for address in recipients
            ],
        }
    }
    # Microsoft Graph saves sent mail by default. Only specify this property when false.
    if not EMAIL_SAVE_TO_SENT_ITEMS:
        payload["saveToSentItems"] = False

    response = requests.post(
        url,
        headers={
            "Authorization": f"Bearer {access_token}",
            "Content-Type": "application/json",
        },
        json=payload,
        timeout=M365_GRAPH_TIMEOUT_SECONDS,
    )

    if response.status_code not in {200, 202}:
        try:
            error_body = response.json()
        except Exception:
            error_body = {}
        graph_error = error_body.get("error") if isinstance(error_body, dict) else {}
        if not isinstance(graph_error, dict):
            graph_error = {}
        code = clean_text(graph_error.get("code")) or f"HTTP_{response.status_code}"
        message = clean_text(graph_error.get("message")) or clean_text(response.text)[:700]

        hint = ""
        if response.status_code == 403:
            hint = (
                " Check that the Entra app has Microsoft Graph Application permission Mail.Send "
                "with admin consent, and that any Exchange application access restriction allows "
                f"the app to use mailbox {EMAIL_FROM}."
            )
        elif response.status_code == 404:
            hint = f" Check that EMAIL_FROM={EMAIL_FROM!r} is a valid Exchange Online mailbox."
        elif response.status_code == 401:
            hint = " The OAuth token was rejected; verify tenant/client credentials and token audience."

        raise RuntimeError(
            f"Microsoft Graph sendMail failed: HTTP {response.status_code} {code}: {message}.{hint}"
        )

    return {
        "status_code": response.status_code,
        "provider": "MICROSOFT_GRAPH",
        "sender": EMAIL_FROM,
        "recipients": recipients,
    }


# ============================================================
# SINGLE END-OF-RUN SUMMARY EMAIL
# ============================================================


def _build_unresolved_paychex_summary_rows(paychex_results, remaining_unresolved_drivers):
    """Return one row per BigA/Avatar driver that still has no Paychex employee ID."""
    result_by_driver_id = {
        item.get("driver_id"): item
        for item in paychex_results
        if item.get("driver_id") is not None
    }

    rows = []
    for record in remaining_unresolved_drivers:
        driver_id = record.get("DB Driver ID")
        result = result_by_driver_id.get(driver_id, {})
        first_name = clean_text(record.get("First Name"))
        last_name = clean_text(record.get("Last Name"))
        rows.append(
            {
                "driver_id": driver_id,
                "name": clean_text(
                    record.get("Display Name") or f"{first_name} {last_name}"
                ),
                "email": clean_text(record.get("Email")),
                "phone": clean_text(record.get("Phone")),
                "dob": clean_text(record.get("DOB")),
                "doj": clean_text(record.get("DOJ")),
                "status": clean_text(result.get("status")) or "NOT_FOUND_IN_PAYCHEX",
                "score": int(result.get("score") or 0),
                "reason": clean_text(result.get("match_reason"))
                or clean_text(result.get("error"))
                or "No safe Paychex match found",
            }
        )
    return rows




def _matched_paychex_candidate_name(result):
    """Return the actual Paychex candidate name selected for a successful match."""
    employee_id = clean_text(result.get("employee_id"))
    company_id = result.get("company_id")
    for candidate in result.get("top_candidates") or []:
        if clean_text(candidate.get("employee_id")) != employee_id:
            continue
        if company_id is not None and candidate.get("company_id") != company_id:
            continue
        return clean_text(candidate.get("name"))
    return ""


def _build_new_avatar_driver_email_rows(inserted_drivers, paychex_results):
    """Build one reconciliation row for every driver inserted from Avatar this run."""
    result_by_driver_id = {
        item.get("driver_id"): item
        for item in paychex_results
        if item.get("driver_id") is not None
    }

    rows = []
    for record in inserted_drivers:
        driver_id = record.get("DB Driver ID")
        result = result_by_driver_id.get(driver_id, {})
        first_name = clean_text(record.get("First Name"))
        last_name = clean_text(record.get("Last Name"))
        paychex_status = clean_text(result.get("status")) or "NOT_CHECKED_OR_NO_RESULT"
        paychex_employee_id = clean_text(result.get("employee_id"))
        paychex_name = _matched_paychex_candidate_name(result)

        rows.append(
            {
                "driver_id": driver_id,
                "name": clean_text(record.get("Display Name") or f"{first_name} {last_name}"),
                "email": clean_text(record.get("Email")),
                "phone": clean_text(record.get("Phone")),
                "dob": clean_text(record.get("DOB")),
                "doj": clean_text(record.get("DOJ")),
                "location": clean_text(record.get("Location")),
                "paychex_status": paychex_status,
                "paychex_name": paychex_name,
                "paychex_employee_id": paychex_employee_id,
                "paychex_company_id": result.get("company_id"),
                "match_score": int(result.get("score") or 0),
                "match_reason": clean_text(result.get("match_reason"))
                or clean_text(result.get("error"))
                or "-",
            }
        )
    return rows


def _build_successful_paychex_match_email_rows(paychex_results, inserted_driver_ids):
    """Return every Paychex match saved during this run, with the actual Paychex record name."""
    rows = []
    for result in paychex_results:
        if clean_text(result.get("status")) != "FOUND_AND_SAVED":
            continue
        employee_id = clean_text(result.get("employee_id"))
        if not employee_id:
            continue
        driver_id = result.get("driver_id")
        rows.append(
            {
                "driver_id": driver_id,
                "avatar_name": clean_text(
                    result.get("display_name")
                    or f"{result.get('first_name', '')} {result.get('last_name', '')}"
                ),
                "paychex_name": _matched_paychex_candidate_name(result),
                "email": clean_text(result.get("email")),
                "phone": clean_text(result.get("phone")),
                "dob": clean_text(result.get("dob")),
                "doj": clean_text(result.get("doj")),
                "employee_id": employee_id,
                "company_id": result.get("company_id"),
                "score": int(result.get("score") or 0),
                "reason": clean_text(result.get("match_reason")) or "-",
                "source": "New Avatar driver" if driver_id in inserted_driver_ids else "Existing BigA backlog",
            }
        )
    return rows

def _render_end_of_run_summary_email(
    *,
    expected_count,
    all_drivers,
    current_month_drivers,
    existing_drivers,
    new_candidates,
    inserted_drivers,
    incomplete_prechecks,
    paychex_backlog,
    paychex_results,
    paychex_remaining_backlog,
    paychex_reverse_result,
):
    """Build plain-text and HTML versions of the requested single completion email."""
    paychex_found = sum(
        1 for item in paychex_results if item.get("status") == "FOUND_AND_SAVED"
    )
    unresolved_rows = _build_unresolved_paychex_summary_rows(
        paychex_results,
        paychex_remaining_backlog,
    )

    inserted_driver_ids = {
        record.get("DB Driver ID")
        for record in inserted_drivers
        if record.get("DB Driver ID") is not None
    }
    new_avatar_rows = _build_new_avatar_driver_email_rows(
        inserted_drivers,
        paychex_results,
    )
    successful_paychex_rows = _build_successful_paychex_match_email_rows(
        paychex_results,
        inserted_driver_ids,
    )
    new_avatar_paychex_matches = sum(
        1 for row in new_avatar_rows if row.get("paychex_employee_id")
    )

    paychex_reverse_result = paychex_reverse_result or {}
    reverse_flags = list(paychex_reverse_result.get("flags") or [])
    reverse_scan_error = clean_text(paychex_reverse_result.get("scan_error"))
    reverse_status = (
        "DISABLED"
        if not paychex_reverse_result.get("enabled", PAYCHEX_REVERSE_CHECK_ENABLED)
        else (f"ERROR - {reverse_scan_error}" if reverse_scan_error else "COMPLETED")
    )

    total_records_processed = len(current_month_drivers)
    new_records_found = len(new_candidates)
    new_records_inserted = len(inserted_drivers)
    already_in_biga = len(existing_drivers)

    run_time = datetime.now().astimezone().strftime("%Y-%m-%d %I:%M:%S %p %Z")
    target_month = get_target_month_label()

    metrics = [
        ("Run completed", run_time),
        ("Avatar rows scanned as of run date", len(all_drivers)),
        ("Total target-month records processed", total_records_processed),
        ("New Avatar drivers inserted into BigA", len(new_avatar_rows)),
        ("New Avatar drivers matched in Paychex", new_avatar_paychex_matches),
        ("Drivers checked against Paychex", len(paychex_backlog)),
        ("Records found in Paychex / Employee IDs saved", paychex_found),
        ("Avatar drivers not found in Paychex", len(unresolved_rows)),
        ("Paychex termination review month", paychex_reverse_result.get("termination_month") or get_paychex_termination_month_label()),
        ("Paychex terminated in review month", paychex_reverse_result.get("current_month_termination_flags", 0)),
        ("Paychex ID missing", paychex_reverse_result.get("missing_id_flags", 0)),
        ("Possible replacement Paychex ID", paychex_reverse_result.get("replacement_id_flags", 0)),
    ]

    plain = [
        "Avatar Driver Hub / Paychex Processing Summary",
        "",
    ]
    for label, value in metrics:
        plain.append(f"{label}: {value}")

    # --------------------------------------------------------
    # NEW AVATAR DRIVERS + THEIR PAYCHEX RESULT
    # --------------------------------------------------------
    plain.extend(
        [
            "",
            "New drivers found in Avatar and inserted into BigA this run:",
        ]
    )
    new_avatar_html_rows = []
    for row in new_avatar_rows:
        plain.append(
            f"DRIVER_ID={_email_text(row['driver_id'])} | "
            f"Name={_email_text(row['name'])} | "
            f"Email={_email_text(row['email'])} | "
            f"Phone={_email_text(row['phone'])} | "
            f"DOB={_email_text(row['dob'])} | "
            f"DOJ={_email_text(row['doj'])} | "
            f"Location={_email_text(row['location'])} | "
            f"Paychex Status={_email_text(row['paychex_status'])} | "
            f"Paychex Name={_email_text(row['paychex_name'])} | "
            f"Paychex Employee ID={_email_text(row['paychex_employee_id'])} | "
            f"Company={_email_text(row['paychex_company_id'])} | "
            f"Score={row['match_score']} | Reason={_email_text(row['match_reason'])}"
        )
        new_avatar_html_rows.append(
            "<tr>"
            f"<td>{escape(_email_text(row['driver_id']))}</td>"
            f"<td>{escape(_email_text(row['name']))}</td>"
            f"<td>{escape(_email_text(row['email']))}</td>"
            f"<td>{escape(_email_text(row['phone']))}</td>"
            f"<td>{escape(_email_text(row['dob']))}</td>"
            f"<td>{escape(_email_text(row['doj']))}</td>"
            f"<td>{escape(_email_text(row['location']))}</td>"
            f"<td>{escape(_email_text(row['paychex_status']))}</td>"
            f"<td>{escape(_email_text(row['paychex_name']))}</td>"
            f"<td>{escape(_email_text(row['paychex_employee_id']))}</td>"
            f"<td>{escape(_email_text(row['paychex_company_id']))}</td>"
            f"<td>{escape(str(row['match_score']))}</td>"
            f"<td>{escape(_email_text(row['match_reason']))}</td>"
            "</tr>"
        )
    if not new_avatar_rows:
        plain.append("None. No new Avatar drivers were inserted into BigA during this run.")
        new_avatar_html_rows.append(
            '<tr><td colspan="13">None. No new Avatar drivers were inserted into BigA during this run.</td></tr>'
        )

    # --------------------------------------------------------
    # ALL PAYCHEX MATCHES FOUND/SAVED THIS RUN
    # --------------------------------------------------------
    plain.extend(
        [
            "",
            "Paychex matches found and saved during this run:",
        ]
    )
    successful_paychex_html_rows = []
    for row in successful_paychex_rows:
        plain.append(
            f"DRIVER_ID={_email_text(row['driver_id'])} | "
            f"Avatar/BigA Name={_email_text(row['avatar_name'])} | "
            f"Paychex Name={_email_text(row['paychex_name'])} | "
            f"Employee ID={_email_text(row['employee_id'])} | "
            f"Company={_email_text(row['company_id'])} | "
            f"Score={row['score']} | Source={_email_text(row['source'])} | "
            f"Reason={_email_text(row['reason'])}"
        )
        successful_paychex_html_rows.append(
            "<tr>"
            f"<td>{escape(_email_text(row['driver_id']))}</td>"
            f"<td>{escape(_email_text(row['avatar_name']))}</td>"
            f"<td>{escape(_email_text(row['paychex_name']))}</td>"
            f"<td>{escape(_email_text(row['employee_id']))}</td>"
            f"<td>{escape(_email_text(row['company_id']))}</td>"
            f"<td>{escape(str(row['score']))}</td>"
            f"<td>{escape(_email_text(row['source']))}</td>"
            f"<td>{escape(_email_text(row['reason']))}</td>"
            "</tr>"
        )
    if not successful_paychex_rows:
        plain.append("None. No new Paychex employee IDs were found and saved during this run.")
        successful_paychex_html_rows.append(
            '<tr><td colspan="8">None. No new Paychex employee IDs were found and saved during this run.</td></tr>'
        )

    plain.extend(
        [
            "",
            "Drivers in Avatar/BigA that still do not have a Paychex Employee ID:",
        ]
    )

    html_rows = []
    for row in unresolved_rows:
        plain.append(
            f"Name={_email_text(row['name'])} | "
            f"Email={_email_text(row['email'])} | "
            f"Phone={_email_text(row['phone'])} | "
            f"DOB={_email_text(row['dob'])} | "
            f"DOJ={_email_text(row['doj'])} | "
        )
        html_rows.append(
            "<tr>"
            f"<td>{escape(_email_text(row['name']))}</td>"
            f"<td>{escape(_email_text(row['email']))}</td>"
            f"<td>{escape(_email_text(row['phone']))}</td>"
            f"<td>{escape(_email_text(row['dob']))}</td>"
            f"<td>{escape(_email_text(row['doj']))}</td>"
            "</tr>"
        )

    if not unresolved_rows:
        plain.append("None. All eligible drivers checked in this run have a Paychex Employee ID.")
        html_rows.append(
            '<tr><td colspan="9">None. All eligible drivers checked in this run have a Paychex Employee ID.</td></tr>'
        )

    plain.extend(
        [
            "",
            "New Hire active-roster / Paychex termination and removal review:",
        ]
    )

    reverse_html_rows = []
    if reverse_scan_error:
        plain.append(f"Reverse reconciliation could not be completed: {reverse_scan_error}")
        reverse_html_rows.append(
            '<tr><td colspan="12"><strong>Reverse reconciliation error:</strong> '
            + escape(reverse_scan_error)
            + '</td></tr>'
        )
    else:
        for row in reverse_flags:
            plain.append(
                f"Name={_email_text(row.get('name'))} | "
                f"Email={_email_text(row.get('email'))} | "
                f"DOJ={_email_text(row.get('doj'))} | "
                f"Termination Date={_email_text(row.get('termination_date'))} | "
                f"Paychex Status={_email_text(row.get('paychex_status'))} | "
                f"Paychex Company={_email_text(row.get('paychex_company_id'))} | "
                f"Status={_email_text(row.get('status'))} | "
            )

            reverse_html_rows.append(
                "<tr>"
                f"<td>{escape(_email_text(row.get('name')))}</td>"
                f"<td>{escape(_email_text(row.get('email')))}</td>"
                f"<td>{escape(_email_text(row.get('doj')))}</td>"
                f"<td>{escape(_email_text(row.get('termination_date')))}</td>"
                f"<td>{escape(_email_text(row.get('paychex_status')))}</td>"
                f"<td>{escape(_email_text(row.get('paychex_company_id')))}</td>"
                f"<td>{escape(_email_text(row.get('status')))}</td>"
                "</tr>"
            )

        if not reverse_flags:
            plain.append(
                "None. No driver in Avatar Driver hub was flagged for a current-month Paychex termination."
            )
            reverse_html_rows.append(
                '<tr><td colspan="12">None. No driver in Avatar Driver hub was flagged for a current-month Paychex termination.</td></tr>'
            )

    metric_rows = "".join(
        "<tr>"
        f"<td style=\"font-weight:600;\">{escape(str(label))}</td>"
        f"<td>{escape(str(value))}</td>"
        "</tr>"
        for label, value in metrics
    )

    html_body = f"""
    <html>
      <body style="font-family:Arial,Helvetica,sans-serif;color:#1f2937;line-height:1.4;">
        <h2>Avatar Driver Hub / Paychex Processing Summary</h2>
        <table style="border-collapse:collapse;min-width:650px;" border="1" cellpadding="7" cellspacing="0">
          <tbody>{metric_rows}</tbody>
        </table>

        <h3 style="margin-top:24px;">New Drivers Found in Avatar This Run</h3>
        <p>These drivers were identified as new in Avatar and successfully mapped with Paychex.</p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Driver ID</th><th>Name</th><th>Email</th><th>Phone</th><th>DOB</th><th>DOJ</th><th>Location</th>
              <th>Paychex Status</th><th>Paychex Name</th><th>Paychex Employee ID</th><th>Company</th><th>Score</th><th>Match Reason</th>
            </tr>
          </thead>
          <tbody>{''.join(new_avatar_html_rows)}</tbody>
        </table>

        <h3 style="margin-top:24px;">Paychex Matches Found This Run</h3>
        <p>This includes successful Paychex matches for both newly inserted Avatar drivers and any existing BigA backlog drivers that previously had a blank Paychex Employee ID.</p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Driver ID</th><th>Avatar/BigA Name</th><th>Paychex Name</th><th>Paychex Employee ID</th>
              <th>Company</th><th>Score</th><th>Source</th><th>Match Reason</th>
            </tr>
          </thead>
          <tbody>{''.join(successful_paychex_html_rows)}</tbody>
        </table>

        <h3 style="margin-top:24px;">Drivers in Avatar/BigA Not Found in Paychex</h3>
        <p>These records still have a blank Paychex Employee ID after the completed lookup.</p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Name</th><th>Email</th><th>Phone</th><th>DOB</th><th>DOJ</th>
            </tr>
          </thead>
          <tbody>{''.join(html_rows)}</tbody>
        </table>
        <p style="font-size:12px;color:#6b7280;margin-top:18px;">

        </p>

        <h3 style="margin-top:24px;">List of Drivers Active in Avatar, Terminated in Paychex</h3>
        <p>

        </p>
        <table style="border-collapse:collapse;width:100%;" border="1" cellpadding="6" cellspacing="0">
          <thead style="background:#f3f4f6;">
            <tr>
              <th>Name</th><th>Email</th><th>DOJ</th><th>Termination Date</th><th>Paychex Status</th><th>Paychex Company</th><th>Status</th>
            </tr>
          </thead>
          <tbody>{''.join(reverse_html_rows)}</tbody>
        </table>
        <p style="font-size:12px;color:#6b7280;margin-top:18px;">
          Generated automatically after Avalon Driver Hub and Paychex processing.
        </p>
      </body>
    </html>
    """

    return "\n".join(plain), html_body, {
        "total_records_processed": total_records_processed,
        "new_records_found": new_records_found,
        "new_records_inserted": new_records_inserted,
        "new_avatar_driver_count": len(new_avatar_rows),
        "new_avatar_paychex_match_count": new_avatar_paychex_matches,
        "paychex_found": paychex_found,
        "unresolved_count": len(unresolved_rows),
        "reverse_flagged_count": len(reverse_flags),
        "reverse_current_month_termination_count": paychex_reverse_result.get("current_month_termination_flags", 0),
        "reverse_missing_id_count": paychex_reverse_result.get("missing_id_flags", 0),
        "reverse_replacement_id_count": paychex_reverse_result.get("replacement_id_flags", 0),
        "reverse_status": reverse_status,
    }


def send_end_of_run_summary_email(
    *,
    expected_count,
    all_drivers,
    current_month_drivers,
    existing_drivers,
    new_candidates,
    inserted_drivers,
    incomplete_prechecks,
    paychex_backlog,
    paychex_results,
    paychex_remaining_backlog,
    paychex_reverse_result,
):
    """Send one requested end-of-run summary email to EMAIL_TO."""
    if not EMAIL_NOTIFICATIONS_ENABLED:
        print("\nCompletion summary email disabled; no message sent.")
        return {
            "sent": False,
            "recipient": EMAIL_TO,
            "reason": "EMAIL_NOTIFICATIONS_DISABLED",
        }

    plain_body, html_body, summary = _render_end_of_run_summary_email(
        expected_count=expected_count,
        all_drivers=all_drivers,
        current_month_drivers=current_month_drivers,
        existing_drivers=existing_drivers,
        new_candidates=new_candidates,
        inserted_drivers=inserted_drivers,
        incomplete_prechecks=incomplete_prechecks,
        paychex_backlog=paychex_backlog,
        paychex_results=paychex_results,
        paychex_remaining_backlog=paychex_remaining_backlog,
        paychex_reverse_result=paychex_reverse_result,
    )

    print("\n======================================================================")
    print(f"END-OF-RUN SUMMARY EMAIL - {EMAIL_TRANSPORT.upper()}")
    print(f"From: {EMAIL_FROM}")
    print(f"To: {EMAIL_TO}")
    print(f"Subject: {EMAIL_SUMMARY_SUBJECT}")
    print(f"Total target-month records processed: {summary['total_records_processed']}")
    print(f"New records found: {summary['new_records_found']}")
    print(f"New Avatar drivers inserted: {summary['new_avatar_driver_count']}")
    print(f"New Avatar drivers matched in Paychex: {summary['new_avatar_paychex_match_count']}")
    print(f"Paychex matches found: {summary['paychex_found']}")
    print(f"Still not found in Paychex: {summary['unresolved_count']}")
    print(f"New Hire roster / reverse reconciliation flags: {summary['reverse_flagged_count']}")
    if EMAIL_TRANSPORT == "smtp":
        print(
            f"Transport: SMTP {EMAIL_SMTP_HOST}:{EMAIL_SMTP_PORT} | "
            f"security={EMAIL_SMTP_SECURITY} | auth={'YES' if EMAIL_SMTP_USERNAME else 'NO'}"
        )
    else:
        print("Transport: Microsoft Graph OAuth 2.0 client credentials")
    print("======================================================================")

    try:
        if EMAIL_TRANSPORT == "smtp":
            send_result = _send_email_via_smtp(EMAIL_SUMMARY_SUBJECT, plain_body, html_body)
        else:
            send_result = _send_email_via_microsoft_graph(EMAIL_SUMMARY_SUBJECT, plain_body, html_body)
    except Exception as error:
        print(f"EMAIL SEND FAILED: {error}")
        return {
            "sent": False, "recipient": EMAIL_TO, "subject": EMAIL_SUMMARY_SUBJECT,
            "provider": EMAIL_TRANSPORT.upper(), "error": str(error), **summary,
        }

    if EMAIL_TRANSPORT == "smtp":
        print(f"Summary email accepted by SMTP relay {EMAIL_SMTP_HOST}:{EMAIL_SMTP_PORT} -> {EMAIL_TO}")
    else:
        print(f"Summary email accepted by Microsoft Graph (HTTP {send_result['status_code']}) -> {EMAIL_TO}")

    result = {
        "sent": True, "recipient": EMAIL_TO, "subject": EMAIL_SUMMARY_SUBJECT,
        "provider": EMAIL_TRANSPORT.upper(), **summary,
    }
    if EMAIL_TRANSPORT == "graph":
        result["http_status"] = send_result.get("status_code")
    return result


# ============================================================
# MAIN
# ============================================================


def run(playwright: Playwright) -> None:
    test_database_connection()
    validate_extended_driver_columns()
    validate_paychex_requirements()

    # ========================================================
    # SAMSARA INTEGRATION - TEMPORARILY DISABLED / COMMENTED
    # ========================================================
    # Keep this validation call for later use. Re-enable it together with
    # run_samsara_driver_sync() below when Samsara integration is needed again.
    # validate_samsara_requirements()
    #
    # While this remains commented, this run will NOT require a Samsara API
    # token and will NOT fetch, match, or update any Samsara driver records.
    print("Samsara integration is temporarily disabled/commented out.")

    validate_email_requirements()

    launch_kwargs = {
        "headless": HEADLESS,
        "slow_mo": SLOW_MO,
    }
    if HEADLESS and HEADLESS_BROWSER_CHANNEL:
        launch_kwargs["channel"] = HEADLESS_BROWSER_CHANNEL

    print(
        "Avalon browser mode: "
        f"{'HEADLESS' if HEADLESS else 'HEADED'} | "
        f"channel={launch_kwargs.get('channel', 'default')} | "
        f"slow_mo={SLOW_MO}ms"
    )

    try:
        browser = playwright.chromium.launch(**launch_kwargs)
    except Exception as error:
        # Keep a fallback for older Playwright installations that do not support
        # the requested channel, while making the behavior obvious in the log.
        if HEADLESS and launch_kwargs.get("channel"):
            print(
                f"Headless channel {launch_kwargs['channel']!r} could not launch: {error}"
            )
            print("Falling back to Playwright default headless browser.")
            launch_kwargs.pop("channel", None)
            browser = playwright.chromium.launch(**launch_kwargs)
        else:
            raise

    context = browser.new_context(
        accept_downloads=True,
    )

    page = context.new_page()
    page.set_default_timeout(20000)
    page.set_default_navigation_timeout(AVALON_NAVIGATION_TIMEOUT_MS)

    expected_count = 0
    all_drivers = []
    current_month_drivers = []
    existing_drivers = []
    new_candidates = []
    incomplete_prechecks = []
    results = []
    column_map = {}
    inserted_drivers = []
    paychex_backlog = []
    paychex_array1 = []
    paychex_array2 = []
    paychex_results = []
    paychex_remaining_backlog = []
    avatar_active_drivers = []
    active_view_name_used = ""
    active_view_expected_count = 0
    active_view_scan_error = ""
    paychex_reverse_result = {
        "enabled": PAYCHEX_REVERSE_CHECK_ENABLED,
        "flags": [],
    }
    samsara_big_a_drivers = []
    samsara_drivers = []
    samsara_results = []
    email_results = {}

    try:
        login(page)
        open_saved_views(page)
        expected_count = open_new_hire_and_get_count(page)

        # ----------------------------------------------------
        # PASS 1
        # Grid only. No dialogs.
        # First Name + Last Name + Start Date/DOJ + Email.
        # Because the grid is sorted newest Start Date first, stop at the first older month.
        # ----------------------------------------------------
        all_drivers, current_month_drivers, column_map = collect_driver_inventory(
            page,
            expected_count,
        )

        # ----------------------------------------------------
        # PASS 2A
        # STRICT DB GATE BEFORE ANY AVALON DRIVER DIALOG.
        # ----------------------------------------------------
        existing_drivers, new_candidates, incomplete_prechecks = prevalidate_current_month_drivers(
            current_month_drivers
        )

        # ----------------------------------------------------
        # PASS 2B
        # Only database-new candidates go through the full
        # dialog capture + final DB insert + attachments.
        # ----------------------------------------------------
        if new_candidates:
            restart_new_hire_at_first_page(page, expected_count)
            column_map = discover_grid_column_map(page)
            results = process_new_candidates(
                page,
                expected_count,
                new_candidates,
                column_map,
            )

        inserted_drivers = [
            record for record in results
            if record.get("DB Status") == "INSERTED"
        ]

        inserted = len(inserted_drivers)
        final_duplicates = sum(
            1 for record in results
            if record.get("DB Status") == "DUPLICATE"
        )
        missing_identity = sum(
            1 for record in results
            if record.get("DB Status") == "SKIPPED_MISSING_IDENTITY"
        )
        db_errors = sum(
            1 for record in results
            if record.get("DB Status") == "DATABASE_ERROR"
        )
        process_errors = sum(
            1 for record in results
            if record.get("Process Error")
        )
        attachment_count = sum(
            int(record.get("Attachment Count", 0) or 0)
            for record in results
        )
        attachment_errors = sum(
            len(record.get("Attachment Errors", []) or [])
            for record in results
        )

        # ----------------------------------------------------
        # FULL NEW HIRE INVENTORY FOR PAYCHEX REVERSE CHECK
        # ----------------------------------------------------
        # IMPORTANT: New Hire is the active-driver roster in this Avatar tenant.
        # Reopen it and scan every row on every page. This is completely independent
        # of the current-month early-stop logic used by the forward new-hire process.
        if PAYCHEX_REVERSE_CHECK_ENABLED:
            try:
                open_saved_views(page)
                active_view_name_used = PAYCHEX_REVERSE_SOURCE_VIEW_NAME
                active_view_expected_count = open_new_hire_and_get_count(page)
                if expected_count and active_view_expected_count != expected_count:
                    print(
                        "WARNING: New Hire count changed during this run: "
                        f"forward-start count={expected_count}, reverse-scan count={active_view_expected_count}. "
                        "The reverse scan will use the newly reported full-view count."
                    )
                avatar_active_drivers, _active_column_map = collect_full_saved_view_inventory(
                    page,
                    active_view_expected_count,
                    active_view_name_used,
                )
            except Exception as error:
                active_view_scan_error = str(error)
                print("\n!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
                print("PAYCHEX REVERSE CHECK - FULL NEW HIRE SCAN FAILED")
                print(active_view_scan_error)
                print("The forward Avalon/Paychex process will continue.")
                print("The completion email will show the reverse check as ERROR.")
                print("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
                if PAYCHEX_REVERSE_CHECK_FAIL_ON_AVALON_ERROR:
                    raise

        print("\n======================================================================")
        print("AVALON PHASE COMPLETE")
        print("======================================================================")
        print(f"New Hire rows reported: {expected_count}")
        print(f"Pass 1 rows scanned before date boundary: {len(all_drivers)}")
        print(f"Target Start Date month: {get_target_month_label()}")
        print(f"Current-month rows: {len(current_month_drivers)}")
        print(
            f"Scanned rows not shortlisted for DB: "
            f"{len(all_drivers) - len(current_month_drivers)}"
        )
        print(f"Current-month rows already in DB: {len(existing_drivers)}")
        print(f"Current-month rows blocked by incomplete DB precheck: {len(incomplete_prechecks)}")
        print(f"Rows validated as DB-not-found and selected for full Avalon processing: {len(new_candidates)}")
        print(f"New drivers inserted: {inserted}")
        print(f"Final duplicate checks skipped: {final_duplicates}")
        print(f"Missing identity after full capture: {missing_identity}")
        print(f"Database errors: {db_errors}")
        print(f"Process errors: {process_errors}")
        print(f"Attachments downloaded: {attachment_count}")
        print(f"Attachment errors: {attachment_errors}")
        print(f"Attachment folder: {ATTACHMENT_ROOT}")
        print(f"Error screenshot folder: {RUN_ROOT}")
        if PAYCHEX_REVERSE_CHECK_ENABLED:
            print(f"Avatar reverse source view: {active_view_name_used or PAYCHEX_REVERSE_SOURCE_VIEW_NAME}")
            print("Reverse-check scope: ALL rows in New Hire across ALL pages; no TARGET_MONTH/current-month filter")
            print(f"New Hire rows reported for reverse check: {active_view_expected_count}")
            print(f"New Hire rows captured for reverse check: {len(avatar_active_drivers)}")
            if active_view_scan_error:
                print(f"New Hire full reverse-check scan ERROR: {active_view_scan_error}")
        print("======================================================================")

        logout(page)

    finally:
        context.close()
        browser.close()

    # --------------------------------------------------------
    # PAYCHEX / TESS API PHASE - ACTIVE
    # Database-driven backlog after Avalon is finished.
    # Call Tess exactly twice:
    #   company 1 endpoint -> paychex_array1
    #   company 2 endpoint -> paychex_array2
    # Then match exact FNAME+LNAME or NAME, strengthened by DOB/email/mobile, and update BigA.
    # --------------------------------------------------------
    paychex_backlog = load_drivers_missing_paychex_employee_id()
    paychex_array1, paychex_array2, paychex_results = run_paychex_api_employee_id_lookup(
        paychex_backlog
    )

    # LEGACY PAYCHEX PLAYWRIGHT CALL - DISABLED / COMMENTED OUT
    # paychex_results = run_paychex_employee_id_lookup(
    #     playwright,
    #     paychex_backlog,
    # )

    # Re-query after Paychex. This is the authoritative unresolved list for
    # the first completion email. Any ID successfully saved above disappears
    # from this backlog automatically.
    paychex_remaining_backlog = load_drivers_missing_paychex_employee_id()

    # --------------------------------------------------------
    # PAYCHEX REVERSE RECONCILIATION - ACTIVE
    # --------------------------------------------------------
    paychex_reverse_result = reconcile_active_avatar_against_paychex(
        avatar_active_drivers,
        paychex_array1,
        paychex_array2,
        active_view_name=active_view_name_used or PAYCHEX_REVERSE_SOURCE_VIEW_NAME,
        active_view_expected_count=active_view_expected_count,
        active_view_scan_error=active_view_scan_error,
    )

    # --------------------------------------------------------
    # SAMSARA PHASE - TEMPORARILY DISABLED / COMMENTED OUT
    # --------------------------------------------------------
    # The complete Samsara implementation is intentionally retained above.
    # Re-enable this single call (and validate_samsara_requirements() near the
    # start of run()) when you want to restore:
    #   - fetching the Samsara driver array
    #   - matching BigA drivers to Samsara
    #   - updating FOUND_IN_SAMSARA / SAMSARAID / match audit fields
    #   - PATCHing Paychex employee ID and company external IDs into Samsara
    #
    # samsara_big_a_drivers, samsara_drivers, samsara_results = run_samsara_driver_sync()
    print(
        "Samsara phase skipped: integration and external-ID/employee-ID updates "
        "are commented out for later use."
    )

    # --------------------------------------------------------
    # COMPLETION EMAIL PHASE - ACTIVE
    # Send one consolidated summary after Avalon + Paychex processing.
    # Samsara remains disabled/commented out.
    # --------------------------------------------------------
    email_results = send_end_of_run_summary_email(
        expected_count=expected_count,
        all_drivers=all_drivers,
        current_month_drivers=current_month_drivers,
        existing_drivers=existing_drivers,
        new_candidates=new_candidates,
        inserted_drivers=inserted_drivers,
        incomplete_prechecks=incomplete_prechecks,
        paychex_backlog=paychex_backlog,
        paychex_results=paychex_results,
        paychex_remaining_backlog=paychex_remaining_backlog,
        paychex_reverse_result=paychex_reverse_result,
    )

    print("\n======================================================================")
    print("END-TO-END AUTOMATION COMPLETE")
    print(f"Avalon new drivers inserted: {len(inserted_drivers)}")
    print(f"Paychex empty-ID backlog checked: {len(paychex_backlog)}")
    print(f"Paychex Array1 company 1 rows: {len(paychex_array1)}")
    print(f"Paychex Array2 company 2 rows: {len(paychex_array2)}")
    print(
        "Paychex IDs saved: "
        f"{sum(1 for item in paychex_results if item.get('status') == 'FOUND_AND_SAVED')}"
    )
    print(
        "Paychex rows still unresolved after this run: "
        f"{len(paychex_remaining_backlog)}"
    )
    print(
        "New Hire roster / Paychex terminated in review month: "
        f"{paychex_reverse_result.get('current_month_termination_flags', 0)}"
    )
    print(
        "New Hire roster / stored Paychex ID missing: "
        f"{paychex_reverse_result.get('missing_id_flags', 0)}"
    )
    print(
        "New Hire roster / possible replacement Paychex ID: "
        f"{paychex_reverse_result.get('replacement_id_flags', 0)}"
    )
    print(
        "Total Paychex reverse reconciliation flags: "
        f"{len(paychex_reverse_result.get('flags') or [])}"
    )
    if PAYCHEX_REVERSE_CHECK_ENABLED:
        print(
            "Reverse-check full-list coverage: "
            f"{paychex_reverse_result.get('active_rows_scanned', 0)}/"
            f"{paychex_reverse_result.get('active_view_expected_count', 0)} active rows"
        )
    if paychex_reverse_result.get("scan_error"):
        print(f"Paychex reverse check status: ERROR - {paychex_reverse_result['scan_error']}")
    elif PAYCHEX_REVERSE_CHECK_ENABLED:
        print("Paychex reverse check status: COMPLETED")
    else:
        print("Paychex reverse check status: DISABLED")
    # SAMSARA SUMMARY - retained as comments for later re-enable:
    # print(f"Samsara drivers fetched into array: {len(samsara_drivers)}")
    # print(f"BigA drivers evaluated for Samsara: {len(samsara_big_a_drivers)}")
    # print(
    #     "Samsara drivers matched: "
    #     f"{sum(1 for item in samsara_results if item.get('samsara_driver_id'))}"
    # )
    # print(
    #     "Samsara updates successful/already current/dry-run: "
    #     f"{sum(1 for item in samsara_results if item.get('patch_status') in {'PATCHED', 'ALREADY_CURRENT', 'DRY_RUN_WOULD_PATCH'})}"
    # )
    print("Samsara integration: DISABLED / COMMENTED FOR LATER USE")
    print(f"Completion summary email sent: {'YES' if email_results.get('sent') else 'NO'}")
    print(f"Completion email recipient: {email_results.get('recipient', EMAIL_TO)}")
    print("======================================================================")

    # Keep arrays available in local scope for debugging/adaptation.
    _ = (
        all_drivers,
        current_month_drivers,
        existing_drivers,
        new_candidates,
        incomplete_prechecks,
        results,
        inserted_drivers,
        paychex_backlog,
        paychex_array1,
        paychex_array2,
        paychex_results,
        paychex_remaining_backlog,
        avatar_active_drivers,
        active_view_name_used,
        active_view_expected_count,
        active_view_scan_error,
        paychex_reverse_result,
        samsara_big_a_drivers,
        samsara_drivers,
        samsara_results,
        email_results,
    )


if __name__ == "__main__":
    with sync_playwright() as playwright:
        run(playwright)