#!/usr/bin/env python3
"""HIPAA audit-log evidence for Claude Enterprise from Compliance API Activity Feed records, in DuckDB.

    python3 hipaa_claude_review_evidence.py            # clean generated records
    python3 hipaa_claude_review_evidence.py --plant    # generated records with planted faults
    python3 hipaa_claude_review_evidence.py --strict   # exit 1 when anything needs follow-up

Builds four lists, each printed with its period, filters, run time, row count and a SHA-256 of its rows:
  164.312(b)             one row per day of the period: how many Activity Feed records exist, and whether the
                         day is recorded, has NO RECORDS, or falls BEFORE ENABLED (the Compliance API was not on yet)
  164.308(a)(1)(ii)(D)   every review in your review log that overlaps the period, with its reviewer, the date
                         signed, the days from the end of the period reviewed to the signature, and a status
  164.308(a)(1)(ii)(D)   every stretch of days in the period that no review row covers
  164.308(a)(3)(ii)(C)   every leaver in the period, the date of the first removal record credited to that
                         departure, the days between, and any use of the account after that removal
Before it builds them it runs nine input checks and marks the lists INCOMPLETE if one fails.

The generated records are NOT a real organization's data. Field names (id, created_at, actor.type,
actor.email_address, actor.user_id, type) come from Anthropic's Activity object:
https://platform.claude.com/docs/en/manage-claude/compliance-activity-feed
The activity-type strings and the removal fields come from the API reference:
https://platform.claude.com/docs/en/api/compliance/activities/list
There, org_user_deleted names the removed user in deleted_user_email and deleted_user_id, and
scim_user_deleted carries only user_id. The script reads the email from TARGET_FIELD, and maps the ids
to an email through actor.user_id seen elsewhere in the feed. The reference does not say that these
ids are the same as actor.user_id; we assume so. Check one of your own removal records.
"""
import csv
import datetime as dt
import hashlib
import json
import pathlib
import random
import sys

import duckdb

# ---- CHANGE THESE FOR YOUR OWN DATA ---------------------------------------------------------
PERIOD_START = "2026-07-06"          # first day of the period you are evidencing (UTC, inclusive)
PERIOD_END = "2026-09-27"            # last day of the period (UTC, inclusive)
COMPLIANCE_API_ENABLED_ON = None     # the day you first turned the Compliance API on, YYYY-MM-DD; None = not known
ACTIVITIES_FILE = None               # your Activity Feed pull, one JSON object per line; None = generate
REVIEWS_FILE = None                  # your activity review log (CSV with a header row); None = generate
REVIEWERS_FILE = None                # your roster of people allowed to sign a review (CSV); None = generate
LEAVERS_FILE = None                  # your HR leaver list (CSV with a header row); None = generate
REVIEW_START_COL = "period_start"    # first day the review covered, as YYYY-MM-DD
REVIEW_END_COL = "period_end"        # last day the review covered, as YYYY-MM-DD
REVIEWER_COL = "reviewer"            # the reviewer's work email
SIGNED_COL = "signed_on"             # the day the reviewer signed, as YYYY-MM-DD; empty = not signed
REVIEWER_EMAIL_COL = "email"         # on the roster
LEAVER_EMAIL_COL = "email"
LEAVER_DATE_COL = "last_day"         # the leaver's last day of work, as YYYY-MM-DD
TARGET_FIELD = "deleted_user_email"  # the field on a removal record that holds the removed user's email
SIGN_WITHIN_DAYS = 14                # a review must be signed within this many days after the period it covers ends
GRACE_DAYS = 1                       # access must end within this many days of the last day of work
REMOVAL_WINDOW_DAYS = 30             # a removal before every last day counts if it is at most this many days early
# ----------------------------------------------------------------------------------------------

REMOVAL_TYPES = ["org_user_deleted", "scim_user_deleted", "org_user_left"]
OUT_DIR = pathlib.Path("evidence-out")
GEN_DIR = pathlib.Path("generated")


def generate(plant):
    """Seeded stand-in for an organization's feed, review log, reviewer roster and HR leaver list."""
    rnd = random.Random(222)
    GEN_DIR.mkdir(exist_ok=True)
    users = [f"u{n:02d}@example.test" for n in range(1, 41)]
    reviewers = ["privacy.officer@example.test", "security.lead@example.test"]
    leavers = users[29:35]
    last_day = {u: dt.date(2026, 7, 10) + dt.timedelta(days=11 * i) for i, u in enumerate(leavers)}
    start, end = dt.date.fromisoformat(PERIOD_START), dt.date.fromisoformat(PERIOD_END)
    recs = []

    def add(day, typ, actor, **extra):
        t = dt.datetime.combine(day, dt.time(rnd.randrange(7, 19), rnd.randrange(60), rnd.randrange(60)))
        recs.append({"id": "", "created_at": t.strftime("%Y-%m-%dT%H:%M:%SZ"),
                     "organization_id": "org_generated", "actor": actor, "type": typ, **extra})

    def user(email):
        return {"type": "user_actor", "email_address": email, "user_id": "user_" + email.split("@")[0],
                "ip_address": "192.0.2.1", "user_agent": "generated"}

    leaver_kept, leaver_late, came_back = leavers[3], leavers[4], leavers[0]
    day = start
    while day <= end:
        staff = [u for u in users if u not in last_day or day <= last_day[u]]
        if plant and day > last_day.get(leaver_kept, dt.date.max) and (day - last_day[leaver_kept]).days <= 10:
            staff.append(leaver_kept)
            add(day, "claude_chat_created", user(leaver_kept))
        add(day, "sso_login_succeeded", user(rnd.choice(staff)))
        for _ in range(rnd.randrange(3, 8)):
            add(day, rnd.choice(["claude_chat_created", "claude_chat_viewed"]), user(rnd.choice(staff)))
        day += dt.timedelta(days=1)
    for u in leavers:
        if plant and u == leaver_kept:
            continue
        lag = 9 if (plant and u == leaver_late) else rnd.choice([0, 1])
        add(last_day[u] + dt.timedelta(days=lag), "org_user_deleted",
            {"type": "scim_directory_sync_actor", "directory_id": "directory_generated",
             "idp_connection_type": "AzureSCIMV2"},
            **{TARGET_FIELD: u, "deleted_user_id": "user_" + u.split("@")[0]})
    enabled = PERIOD_START
    if plant:
        add(last_day[came_back] + dt.timedelta(days=10), "claude_chat_created", user(came_back))
        enabled = "2026-07-08"                                        # planted: Compliance API turned on two days late
        recs = [r for r in recs if r["created_at"][:10] >= enabled
                and not "2026-08-10" <= r["created_at"][:10] <= "2026-08-14"]   # planted: five days with no records
    recs.sort(key=lambda r: (r["created_at"], r["type"], json.dumps(r["actor"], sort_keys=True)))
    for n, r in enumerate(recs, 1):
        r["id"] = f"activity_{n:06d}"
    if plant:
        recs[60]["created_at"] = "not-a-date"                         # planted: an unreadable timestamp
        recs.append(dict(recs[40]))                                   # planted: a duplicated record
    feed = GEN_DIR / ("feed_planted.jsonl" if plant else "feed_clean.jsonl")
    feed.write_text("".join(json.dumps(r) + "\n" for r in recs))

    reviews = []
    for k in range(12):                                               # one review a week, Monday to Sunday
        s = start + dt.timedelta(days=7 * k)
        e = s + dt.timedelta(days=6)
        signed = e + dt.timedelta(days=rnd.randrange(1, 7))
        who = rnd.choice(reviewers)
        row = [s.isoformat(), e.isoformat(), who, signed.isoformat()]
        if plant and k == 1:
            row[3] = signed.strftime("%m/%d/%Y")                      # planted: a signature date not written YYYY-MM-DD
        if plant and k == 3:
            continue                                                  # planted: the week nobody reviewed
        if plant and k == 6:
            row[3] = (e + dt.timedelta(days=33)).isoformat()          # planted: signed 33 days after the week ended
        if plant and k == 8:
            row[2] = "u11@example.test"                               # planted: signed by someone not on the roster
        if plant and k == 9:
            row[1] = ""                                               # planted: a review row with no end date
        if plant and k == 10:
            row[3] = ""                                               # planted: a review nobody signed
        if plant and k == 11:
            row[3] = (e - dt.timedelta(days=3)).isoformat()           # planted: signed before the week ended
        reviews.append(row)
    with open(GEN_DIR / "reviews.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["period_start", "period_end", "reviewer", "signed_on"])
        w.writerows(reviews)
    with open(GEN_DIR / "reviewers.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["email", "role"])
        w.writerows([[r, "reviewer"] for r in reviewers] + ([["", "alternate"]] if plant else []))
    with open(GEN_DIR / "leavers.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["email", "last_day"])
        extra = [["", "2026-09-10"],                    # planted: a NULL email in the HR file
                 ["u43@example.test", "8/5/26"]]        # planted: a last day not written as YYYY-MM-DD
        w.writerows([[u, last_day[u].isoformat()] for u in leavers] + (extra if plant else []))
    return (str(feed), str(GEN_DIR / "reviews.csv"), str(GEN_DIR / "reviewers.csv"),
            str(GEN_DIR / "leavers.csv"), enabled)


def fingerprint(rows):
    body = "\n".join("|".join("" if v is None else str(v) for v in r) for r in rows)
    return hashlib.sha256(body.encode()).hexdigest()


def population(con, name, control, filters, sql, header, complete):
    rows = con.execute(sql).fetchall()
    OUT_DIR.mkdir(exist_ok=True)
    with open(OUT_DIR / f"{name}.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(header)
        w.writerows(rows)
    print(f"\n== {control}: {name}" + ("" if complete else "   ** INCOMPLETE: see input checks **"))
    print(f"period    : {PERIOD_START} to {PERIOD_END} (UTC, inclusive)")
    print(f"filters   : {filters}")
    print(f"run at    : {dt.datetime.now(dt.timezone.utc):%Y-%m-%d %H:%M:%S} UTC")
    print(f"rows      : {len(rows)}")
    print(f"sha256    : {fingerprint(rows)}")
    return rows


def ranges(days):
    """Collapse sorted dates into (first, last, count) stretches."""
    out = []
    for d in days:
        if out and (d - out[-1][1]).days == 1:
            out[-1][1] = d
            out[-1][2] += 1
        else:
            out.append([d, d, 1])
    return out


ISO_DATE = r"\d{4}-\d{2}-\d{2}"


def strict_date(col):
    """A CSV date column read only as YYYY-MM-DD. DuckDB's lenient cast reads 8/5/26 as the year 8, so
    any other form becomes NULL here and is counted by an input check instead of moving the date."""
    return (f"CASE WHEN regexp_full_match(trim(\"{col}\"), '{ISO_DATE}') "
            f"THEN try_strptime(trim(\"{col}\"), '%Y-%m-%d')::DATE END")


def main():
    plant, strict = "--plant" in sys.argv, "--strict" in sys.argv
    acts, rvw, rost, lvr, enabled = ACTIVITIES_FILE, REVIEWS_FILE, REVIEWERS_FILE, LEAVERS_FILE, COMPLIANCE_API_ENABLED_ON
    if acts is None:
        acts, rvw, rost, lvr, enabled = generate(plant)
        print(f"input     : GENERATED records (not a real organization), seed 222, plant={plant}")
    else:
        missing = [n for n, v in [("REVIEWS_FILE", rvw), ("REVIEWERS_FILE", rost), ("LEAVERS_FILE", lvr)] if v is None]
        if missing:
            sys.exit("set " + ", ".join(missing) + " as well as ACTIVITIES_FILE")
        print("input     : " + acts)
    print("enabled on: " + (enabled or "not known (COMPLIANCE_API_ENABLED_ON not set)"))
    con = duckdb.connect()
    con.execute(f"""
        CREATE TABLE raw AS SELECT * FROM read_json(?, format='newline_delimited', columns={{
          'id':'VARCHAR', 'created_at':'VARCHAR', 'type':'VARCHAR',
          'actor':'STRUCT(type VARCHAR, email_address VARCHAR, user_id VARCHAR)',
          '{TARGET_FIELD}':'VARCHAR', 'deleted_user_id':'VARCHAR', 'user_id':'VARCHAR'}})""", [acts])
    con.execute(f"""
        CREATE TABLE activities AS SELECT id, type,
          try_cast(replace(created_at, 'Z', '') AS TIMESTAMP) AS ts,
          actor.type AS actor_type, lower(trim(actor.email_address)) AS actor_email,
          actor.user_id AS actor_user_id,
          lower(trim("{TARGET_FIELD}")) AS target_email, coalesce(deleted_user_id, user_id) AS target_user_id
        FROM raw""")
    con.execute(f"""CREATE TABLE leavers AS SELECT lower(trim("{LEAVER_EMAIL_COL}")) AS email,
          trim(coalesce("{LEAVER_DATE_COL}", '')) AS last_day_text, {strict_date(LEAVER_DATE_COL)} AS last_day
        FROM read_csv(?, header=true, all_varchar=true)""", [lvr])
    con.execute(f"""CREATE TABLE reviewers AS SELECT lower(trim("{REVIEWER_EMAIL_COL}")) AS email
          FROM read_csv(?, header=true, all_varchar=true)""", [rost])
    con.execute(f"""CREATE TABLE reviews_raw AS SELECT
          {strict_date(REVIEW_START_COL)} AS period_start, {strict_date(REVIEW_END_COL)} AS period_end,
          lower(trim("{REVIEWER_COL}")) AS reviewer, {strict_date(SIGNED_COL)} AS signed_on,
          trim(coalesce("{SIGNED_COL}", '')) AS signed_text
          FROM read_csv(?, header=true, all_varchar=true)""", [rvw])
    p0, p1 = PERIOD_START, PERIOD_END
    en = f"DATE '{enabled}'" if enabled else "NULL::DATE"
    rt = ", ".join(f"'{t}'" for t in REMOVAL_TYPES)
    # who each removal record names: the email field, else the id mapped through actor.user_id, else
    # (org_user_left) the person who removed themselves
    con.execute(f"""
        CREATE TABLE removals AS
        WITH ids AS (SELECT actor_user_id, arg_max(actor_email, ts) AS email FROM activities
                     WHERE actor_type = 'user_actor' AND actor_user_id IS NOT NULL AND actor_email IS NOT NULL GROUP BY 1)
        SELECT a.id, a.ts, a.type, coalesce(a.target_email, i.email,
               CASE WHEN a.type = 'org_user_left' THEN a.actor_email END) AS email
        FROM activities a LEFT JOIN ids i ON i.actor_user_id = a.target_user_id WHERE a.type IN ({rt})""")
    # a review row is usable only if both ends of its period read as dates; unusable rows are counted below
    con.execute("CREATE TABLE reviews AS SELECT * FROM reviews_raw WHERE period_start IS NOT NULL AND period_end IS NOT NULL")

    # ---- input checks: each says which planted fault trips it
    total = con.execute("SELECT count(*) FROM activities").fetchone()[0]
    dup = con.execute("SELECT count(*) FROM (SELECT id FROM activities GROUP BY id HAVING count(*) > 1)").fetchone()[0]
    no_time = con.execute("SELECT count(*) FROM activities WHERE ts IS NULL OR id IS NULL").fetchone()[0]
    outside = con.execute(f"SELECT count(*) FROM activities WHERE ts::DATE NOT BETWEEN '{p0}' AND '{p1}'").fetchone()[0]
    days = con.execute(f"""
        SELECT d::DATE AS day, count(a.id) AS records,
               CASE WHEN count(a.id) > 0 THEN 'recorded'
                    WHEN d::DATE < {en} THEN 'BEFORE ENABLED' ELSE 'NO RECORDS' END AS status
        FROM generate_series(DATE '{p0}', DATE '{p1}', INTERVAL 1 DAY) t(d)
        LEFT JOIN activities a ON a.ts::DATE = d::DATE GROUP BY 1 ORDER BY 1""").fetchall()
    off = [r[0] for r in days if r[2] == "NO RECORDS"]
    pre = [r[0] for r in days if r[2] == "BEFORE ENABLED"]
    bad_review = con.execute("SELECT count(*) FROM reviews_raw WHERE period_start IS NULL OR period_end IS NULL").fetchone()[0]
    bad_signed = con.execute("SELECT count(*) FROM reviews_raw WHERE signed_text <> '' AND signed_on IS NULL").fetchone()[0]
    ros_bad = con.execute("SELECT count(*) FROM reviewers WHERE email IS NULL OR email = ''").fetchone()[0]
    hr_bad = con.execute("SELECT count(*) FROM leavers WHERE email IS NULL OR email = '' OR last_day_text = ''").fetchone()[0]
    hr_date = con.execute("SELECT count(*) FROM leavers WHERE last_day_text <> '' AND last_day IS NULL").fetchone()[0]
    checks = [
        ("duplicate activity ids", dup, "a duplicated record"),
        ("records with no id or no readable created_at", no_time, "an unreadable timestamp"),
        ("days on or after enablement with no records", len(off), "five days with no records"),
        ("days in the period before the Compliance API was enabled", len(pre), "the Compliance API turned on late"),
        ("review log rows with no readable period start or end", bad_review, "a review row with no end date"),
        ("review log rows with a signature date that is not YYYY-MM-DD", bad_signed, "a signature date written 09/24/2026"),
        ("reviewer roster rows with no email", ros_bad, "a NULL in the roster"),
        ("HR leaver rows with no email or no last day", hr_bad, "a NULL in the HR file"),
        ("HR leaver rows whose last day is not written as YYYY-MM-DD", hr_date, "a last day written 8/5/26"),
    ]
    print(f"\n== Input checks   (records read: {total}; outside the period, left out: {outside})")
    ok = True
    for label, n, fault in checks:
        flag = "ok  " if n == 0 else "FAIL"
        ok &= n == 0
        print(f"[{flag}] {label}: {n}" + ("" if n == 0 else f"   (trips on: {fault})"))
    for label, ds in [("days with no records", off), ("days before enablement", pre)]:
        if ds:
            print(f"        {label}: " + ", ".join(f"{a}..{b}" if a != b else str(a) for a, b, _ in ranges(ds)))
    seen = {r[0] for r in con.execute("SELECT DISTINCT type FROM activities").fetchall()}
    print("[info] removal types never seen in this feed: " + (", ".join(t for t in REMOVAL_TYPES if t not in seen) or "none"))
    unnamed = con.execute("SELECT count(*) FROM removals WHERE email IS NULL").fetchone()[0]
    print(f"[info] removal records that name no email the script can match: {unnamed}")

    # ---- 164.312(b): was the feed recording on every day of the period?
    r_log = population(con, "audit_controls_daily", "164.312(b)",
                       "one row per day; records counted by created_at date; enabled on " + (enabled or "not known"),
                       f"""SELECT d::DATE AS day, count(a.id) AS records,
                                  CASE WHEN count(a.id) > 0 THEN 'recorded'
                                       WHEN d::DATE < {en} THEN 'BEFORE ENABLED' ELSE 'NO RECORDS' END AS status
                           FROM generate_series(DATE '{p0}', DATE '{p1}', INTERVAL 1 DAY) t(d)
                           LEFT JOIN activities a ON a.ts::DATE = d::DATE GROUP BY 1 ORDER BY 1""",
                       ["day", "records", "status"], ok)
    n_rec = sum(1 for r in r_log if r[2] == "recorded")
    print(f"  days recorded: {n_rec} of {len(r_log)}")
    for label, st in [("NO RECORDS", "NO RECORDS"), ("BEFORE ENABLED", "BEFORE ENABLED")]:
        for a, b, n in ranges([r[0] for r in r_log if r[2] == st]):
            print(f"  {label}: {a} to {b} ({n} day(s))")

    # ---- 164.308(a)(1)(ii)(D): every review, and who signed it when
    sql = f"""
        SELECT v.period_start, v.period_end, coalesce(v.reviewer, '-') AS reviewer, v.signed_on,
               date_diff('day', v.period_end, v.signed_on) AS days_to_sign,
               CASE WHEN v.signed_on IS NULL AND v.signed_text <> '' THEN 'SIGNATURE DATE UNREADABLE'
                    WHEN v.signed_on IS NULL THEN 'NOT SIGNED'
                    WHEN v.signed_on < v.period_end THEN 'SIGNED BEFORE PERIOD ENDED'
                    WHEN v.reviewer IS NULL OR v.reviewer = '' THEN 'NO REVIEWER'
                    WHEN v.reviewer NOT IN (SELECT email FROM reviewers WHERE email IS NOT NULL) THEN 'NOT ON ROSTER'
                    WHEN date_diff('day', v.period_end, v.signed_on) > {SIGN_WITHIN_DAYS} THEN 'LATE'
                    ELSE 'on time' END AS status
        FROM reviews v WHERE v.period_start <= DATE '{p1}' AND v.period_end >= DATE '{p0}'
        ORDER BY v.period_start, v.period_end, v.reviewer"""
    r_rev = population(con, "activity_reviews", "164.308(a)(1)(ii)(D)",
                       f"review log rows overlapping the period; signed within {SIGN_WITHIN_DAYS} day(s) of the period's last day; "
                       "reviewer on the roster", sql,
                       ["period_start", "period_end", "reviewer", "signed_on", "days_to_sign", "status"], ok)
    for r in r_rev:
        print("  " + " | ".join("-" if v is None else str(v) for v in r))
    sql = f"""
        WITH d AS (SELECT d::DATE AS day FROM generate_series(DATE '{p0}', DATE '{p1}', INTERVAL 1 DAY) t(d)
                   WHERE NOT EXISTS (SELECT 1 FROM reviews v WHERE v.period_start <= d::DATE AND v.period_end >= d::DATE)),
             g AS (SELECT day, day - (row_number() OVER (ORDER BY day))::INTEGER AS grp FROM d)
        SELECT min(day) AS first_day, max(day) AS last_day, count(*) AS days FROM g GROUP BY grp ORDER BY 1"""
    r_gap = population(con, "days_without_review", "164.308(a)(1)(ii)(D)",
                       "days of the period that no readable review row covers", sql, ["first_day", "last_day", "days"], ok)
    for r in r_gap:
        print("  " + " | ".join(str(v) for v in r))

    # ---- 164.308(a)(3)(ii)(C): leavers. Each removal record is credited to ONE departure of that person: the
    # latest HR last day on or before it, or, if it came before every last day, the earliest last day it precedes
    # by at most REMOVAL_WINDOW_DAYS. So a removal from an earlier stint never counts for a later departure.
    con.execute(f"""
        CREATE TABLE had_account AS
        SELECT actor_email AS email FROM activities
        WHERE actor_type = 'user_actor' AND actor_email IS NOT NULL AND ts::DATE <= DATE '{p1}'
        UNION SELECT email FROM removals WHERE email IS NOT NULL""")
    in_period = f"l.last_day BETWEEN DATE '{p0}' AND DATE '{p1}' AND l.email IS NOT NULL"
    sql = f"""
        WITH credited AS (
               SELECT r.email, r.ts, coalesce(
                 (SELECT max(l.last_day) FROM leavers l WHERE l.email = r.email AND l.last_day <= r.ts::DATE),
                 (SELECT min(l.last_day) FROM leavers l WHERE l.email = r.email
                   AND l.last_day - INTERVAL {REMOVAL_WINDOW_DAYS} DAY <= r.ts::DATE)) AS last_day
               FROM removals r WHERE r.email IS NOT NULL),
             rem AS (SELECT email, last_day, min(ts) AS removed_at FROM credited
                     WHERE last_day IS NOT NULL GROUP BY 1, 2),
             nxt AS (SELECT email, last_day,
                            lead(last_day) OVER (PARTITION BY email ORDER BY last_day) AS next_last_day
                     FROM leavers WHERE email IS NOT NULL),
             -- after the grace period and before the removal; and after the removal. When the same person
             -- has a LATER departure in HR, use after the removal belongs to that later stint, not this row.
             after AS (SELECT l.email, l.last_day,
                         count(a.id) FILTER (WHERE a.ts::DATE > l.last_day + INTERVAL {GRACE_DAYS} DAY
                                               AND (r.removed_at IS NULL OR a.ts < r.removed_at)) AS n_grace,
                         count(a.id) FILTER (WHERE r.removed_at IS NOT NULL AND a.ts > r.removed_at
                                               AND n.next_last_day IS NULL) AS n_removed
                       FROM leavers l JOIN nxt n ON n.email = l.email AND n.last_day = l.last_day
                       LEFT JOIN rem r ON r.email = l.email AND r.last_day = l.last_day
                       LEFT JOIN activities a ON a.actor_email = l.email AND a.actor_type = 'user_actor'
                       GROUP BY 1, 2)
        SELECT l.email, l.last_day, r.removed_at::DATE AS first_removal_record,
               date_diff('day', l.last_day, r.removed_at::DATE) AS days_to_removal,
               af.n_grace AS actions_after_grace, af.n_removed AS actions_after_removal,
               CASE WHEN r.removed_at IS NULL AND l.email IN (SELECT email FROM had_account) THEN 'NO REMOVAL RECORD'
                    WHEN r.removed_at IS NULL THEN 'CHECK: NO CLAUDE ACTIVITY SEEN'
                    WHEN af.n_removed > 0 THEN 'ACTIVE AFTER REMOVAL'
                    WHEN date_diff('day', l.last_day, r.removed_at::DATE) > {GRACE_DAYS} THEN 'REMOVED LATE'
                    ELSE 'removed in time' END AS status
        FROM leavers l
        LEFT JOIN rem r ON r.email = l.email AND r.last_day = l.last_day
        LEFT JOIN after af ON af.email = l.email AND af.last_day = l.last_day
        WHERE {in_period}
        ORDER BY l.last_day, l.email"""
    r_lv = population(con, "leavers", "164.308(a)(3)(ii)(C)",
                      f"HR leavers with last day in period; removal types {REMOVAL_TYPES}, each credited to one departure; "
                      f"grace {GRACE_DAYS} day(s)", sql,
                      ["email", "last_day", "first_removal_record", "days_to_removal", "actions_after_grace",
                       "actions_after_removal", "status"], ok)
    for r in r_lv:
        print("  " + " | ".join("-" if v is None else str(v) for v in r))

    follow = (len(off) + len(pre) + sum(1 for r in r_rev if r[5] != "on time") + len(r_gap)
              + sum(1 for r in r_lv if r[6] != "removed in time" or r[4] or r[5]))
    print("\n== Result")
    print(f"input checks : {'all passed' if ok else 'FAILED, the lists above are not complete'}")
    print(f"follow-up    : {follow} item(s) need an explanation "
          f"(days with no records or before enablement, reviews not on time, days with no review, leaver rows)")
    verdict = "NOTHING TO FOLLOW UP" if ok and follow == 0 else "EXCEPTIONS FOUND"
    print(f"RESULT: {verdict}")
    return 1 if strict and verdict != "NOTHING TO FOLLOW UP" else 0


if __name__ == "__main__":
    sys.exit(main())
