#!/usr/bin/env python3
"""Match every current data-access grant to a request, a named approver, a reason and an end date, in DuckDB.

    python3 who_approved_each_grant.py --demo                  # clean generated records
    python3 who_approved_each_grant.py --demo --plant-error    # generated records with 9 grants that have a gap
    python3 who_approved_each_grant.py --demo --plant-input    # generated records with planted input faults
    python3 who_approved_each_grant.py                         # your exports: set DATA_DIR and the maps below first
    python3 who_approved_each_grant.py --strict                # exit 1 if any grant has a finding
    add  --out <folder>  to write every file under that folder (default: ./who-approved-out)

It reads five CSV files (see COLUMN_MAP) and, for each grant in grants.csv, writes the evidence an auditor asks for
(who requested it, who approved it, the reason each gave, when the access ends) to grant_evidence.csv, prints each
finding and prints three matched grants as examples. The findings:
  NO_REQUEST            no delivered request that assigned this person the access package
  NO_APPROVAL           the request has no approval by a named reviewer
  SELF_APPROVAL         the approver is the person who asked
  NO_REASON             the request or the approval carries no justification
  GRANT_BEFORE_APPROVAL the grant was created before the last approval was recorded
  NO_END_DATE           the person's most recent assignment for this package holds no end date, or there is none
                        (an assignment that carries only a duration and no end date is reported here too)
  REMOVED_IN_ENTRA      the person's assignment is not active in Entra (its state is not delivered, or a delivered
                        removal request names it) and the grant is still there
  PAST_END_DATE         the end date has passed and the grant is still there
  UNMAPPED_ROLE         the role is not in role_packages.csv, so no request can be looked for
Input checks run first. If one fails, no findings are computed and the run says INCOMPLETE.
The script reads files; it never connects to a platform and never writes to one.

The demo records are GENERATED (seed 124): not a company's data. With --demo the script downloads Microsoft's
published Graph metadata at a pinned commit and fails if a column it takes from Graph is not in it:
  https://github.com/microsoftgraph/msgraph-metadata/tree/c4b98b7b7808ee5f4e7d752378339d70ef472154
Column names come from:
  Graph   https://learn.microsoft.com/en-us/graph/api/resources/accesspackageassignmentrequest
          https://learn.microsoft.com/en-us/graph/api/resources/accesspackageassignment
          https://learn.microsoft.com/en-us/graph/api/resources/approvalstage
  Snowflake (grants.csv)  https://docs.snowflake.com/en/sql-reference/account-usage/grants_to_users
"""
import csv
import datetime as dt
import hashlib
import pathlib
import random
import re
import sys
import urllib.request
import xml.etree.ElementTree as ET

import duckdb

# ---- CHANGE THESE FOR YOUR OWN DATA ---------------------------------------------------------
AS_OF = "2026-11-04"          # the day you took the exports (UTC, YYYY-MM-DD); an end date before it has passed
DATA_DIR = None               # folder holding the five files named in FILE_NAMES
FILE_NAMES = {"grants": "grants.csv", "role_packages": "role_packages.csv", "requests": "requests.csv",
              "approvals": "approvals.csv", "assignments": "assignments.csv"}
# For each file: the script's column name (left, do not change) = the column name in your file (right).
# Names are matched without regard to case or surrounding spaces.
COLUMN_MAP = {
    "grants": {"grantee_name": "grantee_name", "role": "role", "created_on": "created_on"},
    "role_packages": {"role": "role", "access_package_id": "access_package_id"},
    "requests": {"id": "id", "requestType": "requestType", "state": "state", "createdDateTime": "createdDateTime",
                 "completedDateTime": "completedDateTime", "justification": "justification",
                 "requestor_objectId": "requestor_objectId", "assignment_id": "assignment_id",
                 "assignment_target_principalName": "assignment_target_principalName",
                 "accessPackage_id": "accessPackage_id"},
    "approvals": {"request_id": "request_id", "reviewedBy_id": "reviewedBy_id",
                  "reviewedBy_displayName": "reviewedBy_displayName", "reviewResult": "reviewResult",
                  "reviewedDateTime": "reviewedDateTime", "justification": "justification"},
    "assignments": {"id": "id", "accessPackage_id": "accessPackage_id", "target_principalName": "target_principalName",
                    "state": "state", "schedule_startDateTime": "schedule_startDateTime",
                    "schedule_expiration_type": "schedule_expiration_type",
                    "schedule_expiration_endDateTime": "schedule_expiration_endDateTime"},
}
# ----------------------------------------------------------------------------------------------

# Where each script column comes from. A Graph path is checked against Microsoft's metadata with --demo.
# None = a key or list this script adds (a Snowflake column, or a join key), not a Graph property.
GRAPH_PATHS = {
    "requests": {"id": "accessPackageAssignmentRequest.id", "requestType": "accessPackageAssignmentRequest.requestType",
                 "state": "accessPackageAssignmentRequest.state",
                 "createdDateTime": "accessPackageAssignmentRequest.createdDateTime",
                 "completedDateTime": "accessPackageAssignmentRequest.completedDateTime",
                 "justification": "accessPackageAssignmentRequest.justification",
                 "requestor_objectId": "accessPackageAssignmentRequest.requestor.objectId",
                 "assignment_id": "accessPackageAssignmentRequest.assignment.id",
                 "assignment_target_principalName": "accessPackageAssignmentRequest.assignment.target.principalName",
                 "accessPackage_id": "accessPackageAssignmentRequest.accessPackage.id"},
    "approvals": {"request_id": None, "reviewedBy_id": "approvalStage.reviewedBy.id",
                  "reviewedBy_displayName": "approvalStage.reviewedBy.displayName",
                  "reviewResult": "approvalStage.reviewResult", "reviewedDateTime": "approvalStage.reviewedDateTime",
                  "justification": "approvalStage.justification"},
    "assignments": {"id": "accessPackageAssignment.id", "accessPackage_id": "accessPackageAssignment.accessPackage.id",
                    "target_principalName": "accessPackageAssignment.target.principalName",
                    "state": "accessPackageAssignment.state",
                    "schedule_startDateTime": "accessPackageAssignment.schedule.startDateTime",
                    "schedule_expiration_type": "accessPackageAssignment.schedule.expiration.type",
                    "schedule_expiration_endDateTime": "accessPackageAssignment.schedule.expiration.endDateTime"},
    "grants": {"grantee_name": None, "role": None, "created_on": None},
    "role_packages": {"role": None, "access_package_id": None},
}
GRAPH_URL = ("https://raw.githubusercontent.com/microsoftgraph/msgraph-metadata/"
             "c4b98b7b7808ee5f4e7d752378339d70ef472154/clean_v10_metadata/cleanMetadataWithDescriptionsv1.0.xml")
GRAPH_BYTES = 8200681
GRAPH_SHA256 = "ff4f707d562dd1aa1dfaa591f4e17f4e6f50d517dc65b0278d216fb4fc4e547b"

# Values Microsoft documents (read 2026-11-04); a value outside these lists fails an input check.
KNOWN_STATES = ["submitted", "pendingApproval", "delivering", "delivered", "deliveryFailed", "denied", "scheduled",
                "canceled", "partiallyDelivered", "unknownFutureValue"]
KNOWN_REQUEST_TYPES = ["notSpecified", "userAdd", "userUpdate", "userRemove", "adminAdd", "adminUpdate", "adminRemove",
                       "systemAdd", "systemUpdate", "systemRemove", "onBehalfAdd", "unknownFutureValue", "approverRemove"]
KNOWN_REVIEW_RESULTS = ["NotReviewed", "Approved", "Denied"]
KNOWN_EXPIRATION_TYPES = ["notSpecified", "noExpiration", "afterDateTime", "afterDuration"]
KNOWN_ASSIGNMENT_STATES = ["delivering", "partiallyDelivered", "delivered", "expired", "deliveryFailed",
                           "unknownFutureValue"]
GRANT_REQUEST_TYPES = ["userAdd", "adminAdd", "onBehalfAdd"]   # requests that create an assignment (v1.0 marks
                                                                # onBehalfAdd not supported; it is counted if present)
REMOVE_REQUEST_TYPES = ["userRemove", "adminRemove", "systemRemove", "approverRemove"]   # requests that end one
ZERO_GUID = "00000000-0000-0000-0000-000000000000"   # reviewedBy.id of a stage nobody has reviewed
CODES = ["NO_REQUEST", "NO_APPROVAL", "SELF_APPROVAL", "NO_REASON", "GRANT_BEFORE_APPROVAL", "NO_END_DATE",
         "REMOVED_IN_ENTRA", "PAST_END_DATE", "UNMAPPED_ROLE"]
TS_RE = "\\d{4}-\\d{2}-\\d{2}[ T]\\d{2}:\\d{2}:\\d{2}(\\.\\d+)?( ?(Z|\\+00:00|\\+0000))?"
RUN_AT = dt.datetime.now(dt.timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")


def ts(col):
    """A timestamp read strictly: YYYY-MM-DD[ T]HH:MM:SS taken as UTC (a Z, +00:00 or +0000 suffix is accepted, any
    other shape is NULL and counted by an input check)."""
    return (f"CASE WHEN regexp_full_match(trim({col}), '{TS_RE}') THEN "
            f"try_strptime(substr(replace(trim({col}), 'T', ' '), 1, 19), '%Y-%m-%d %H:%M:%S') END")


def blank(col):
    return f"nullif(trim({col}), '')"


def quote_list(items):
    return ", ".join("'" + str(i).replace("'", "''") + "'" for i in items)


def cell(v):
    """A CSV cell a spreadsheet will not run: text starting with = + - @, a tab or a carriage return gets a ' first."""
    return "'" + v if isinstance(v, str) and v[:1] in ("=", "+", "-", "@", "\t", "\r") else v


# ---------------------------------------------------------------------------------------------
# Demo records: GENERATED (seed 124), with every column name checked against Microsoft's Graph metadata.
# ---------------------------------------------------------------------------------------------
def check_graph_names(folder):
    """Download Graph's metadata at the pinned commit and fail if a column taken from it is not there."""
    path = folder / "graph-v1.0-metadata.xml"
    folder.mkdir(parents=True, exist_ok=True)
    cached = path.is_file()
    if cached:
        data = path.read_bytes()
    else:
        req = urllib.request.Request(GRAPH_URL, headers={
            "User-Agent": "who_approved_each_grant.py (https://www.datameaning.com/guides/prove-who-approved-each-data-access/; "
                          "info@datameaning.com)"})
        data = urllib.request.urlopen(req, timeout=60).read()
    sha = hashlib.sha256(data).hexdigest()
    print(f"  Graph metadata at the pinned commit: {len(data)} bytes, SHA-256 {sha[:16]}...")
    if len(data) != GRAPH_BYTES or sha != GRAPH_SHA256:
        if cached:
            path.unlink()
        sys.exit("The Graph metadata file is not the pinned one (size or SHA-256 differs): stopping."
                 + (f" Deleted the saved copy {path}; the next run downloads it again." if cached else ""))
    if not cached:
        path.write_bytes(data)
    types = {}
    for e in ET.fromstring(data).iter():
        if e.tag.endswith("}EntityType") or e.tag.endswith("}ComplexType"):
            types[e.get("Name")] = e

    def member(tname, prop):
        t = types.get(tname)
        while t is not None:
            for p in t:
                if p.get("Name") == prop and p.tag.split("}")[1] in ("Property", "NavigationProperty"):
                    return re.sub(r"^Collection\((.*)\)$", r"\1", p.get("Type")).removeprefix("graph.")
            t = types.get((t.get("BaseType") or "").removeprefix("graph."))
        return None

    missing, n = [], 0
    for fname, cols in GRAPH_PATHS.items():
        for col, gpath in cols.items():
            if gpath is None:
                continue
            n += 1
            parts = gpath.split(".")
            tname = parts[0]
            for prop in parts[1:]:
                tname = member(tname, prop)
                if tname is None:
                    missing.append(f"{fname}.{col} <- {gpath}")
                    break
    print(f"  Checked {n} columns against Graph's own property names; not found: {len(missing)}")
    if missing:
        sys.exit("Columns not in Microsoft Graph's metadata: " + "; ".join(missing))


def generate(folder, plant_error, plant_input):
    rnd = random.Random(124)
    folder.mkdir(parents=True, exist_ok=True)
    first = ["ada", "ben", "chloe", "dev", "elena", "farid", "gwen", "hugo", "iris", "jonas", "kira", "luca",
             "maya", "noor", "omar", "petra", "quinn", "rosa", "sven", "tara"]
    people = [(f"{n}.{'smith' if i % 2 else 'jones'}@example.com", f"00000000-0000-4000-8000-{i + 1:012d}")
              for i, n in enumerate(first)]
    owners = [(f"{n}.owner@example.com", f"00000000-0000-4000-9000-{i + 1:012d}", n.title() + " Owner")
              for i, n in enumerate(["sam", "tess", "uma", "vic", "wes"])]
    admin = "00000000-0000-4000-a000-000000000001"     # an Entra admin who assigns access (adminAdd)
    roles = ["FINANCE_READER", "SALES_READER", "HR_ANALYST", "OPS_READER", "MARKETING_READER", "SUPPLY_READER"]
    why = ["Quarter-end close reconciliation", "Pipeline review for the regional plan",
           "Headcount analysis for the annual plan", "Throughput study for the new site",
           "Campaign attribution rebuild", "Supplier lead-time analysis"]
    approved_why = ["Approved: the project named in the request needs this role",
                    "Approved: role matches the stated task, review in 180 days",
                    "Approved: owner confirmed the need with the team lead"]
    grants, requests, approvals, assignments, rolemap = [], [], [], [], []
    base = dt.datetime(2026, 8, 3, 9, 0, 0)
    z = lambda d: d.strftime("%Y-%m-%dT%H:%M:%SZ")
    for r in roles:
        rolemap.append({"role": r, "access_package_id": "pkg-" + r.lower().replace("_", "-")})
    for i in range(1, 31):
        email, oid = people[(i - 1) % 20]
        role = roles[(i - 1) % 6]
        pkg = "pkg-" + role.lower().replace("_", "-")
        own = owners[i % 5]
        asked = base + dt.timedelta(days=2 * i, minutes=rnd.randrange(240))
        approved = asked + dt.timedelta(days=1, hours=rnd.randrange(6), minutes=rnd.randrange(60))
        done = approved + dt.timedelta(minutes=10)
        rid, aid = f"req-{i:03d}", f"asg-{i:03d}"
        # 6 requests made by an admin: the requester is not the person who gets the access
        rtype, asker = ("adminAdd", admin) if i % 5 == 4 else ("userAdd", oid)
        requests.append({"id": rid, "requestType": rtype, "state": "delivered", "createdDateTime": z(asked),
                         "completedDateTime": z(done), "justification": why[(i - 1) % 6],
                         "requestor_objectId": asker, "assignment_id": aid, "assignment_target_principalName": email,
                         "accessPackage_id": pkg})
        approvals.append({"request_id": rid, "reviewedBy_id": own[1], "reviewedBy_displayName": own[2],
                          "reviewResult": "Approved", "reviewedDateTime": z(approved),
                          "justification": approved_why[i % 3]})
        assignments.append({"id": aid, "accessPackage_id": pkg, "target_principalName": email, "state": "delivered",
                            "schedule_startDateTime": z(done), "schedule_expiration_type": "afterDateTime",
                            "schedule_expiration_endDateTime": z(done + dt.timedelta(days=180))})
        grants.append({"grantee_name": email.upper() if i % 4 == 0 else email, "role": role,
                       "created_on": z(done + dt.timedelta(minutes=30))})
    clean_values = {"LEGACY_ADMIN", "noExpiration", "forever", "OLD_ROLE", "expired", "adminRemove"}
    seen = ({g["role"] for g in grants} | {a["schedule_expiration_type"] for a in assignments}
            | {a["state"] for a in assignments} | {r["requestType"] for r in requests})
    if clean_values & seen:
        sys.exit("A planted value already occurs in the clean records: stopping.")

    def row(rows, key, value):
        hit = [r for r in rows if r[key] == value]
        if len(hit) != 1:
            sys.exit(f"Could not plant a fault: {key}={value} matched {len(hit)} rows.")
        return hit[0]

    if plant_error:
        g = lambda n: grants[n - 1]
        rq = lambda n: row(requests, "id", f"req-{n:03d}")
        # 3: no request at all (and so no approval and no assignment)
        requests.remove(rq(3))
        approvals[:] = [a for a in approvals if a["request_id"] != "req-003"]
        assignments.remove(row(assignments, "id", "asg-003"))
        approvals[:] = [a for a in approvals if a["request_id"] != "req-007"]            # 7: request, no approval
        a11 = row(approvals, "request_id", "req-011")                                    # 11: the requester approved it
        a11["reviewedBy_id"], a11["reviewedBy_displayName"] = rq(11)["requestor_objectId"], "Self"
        row(approvals, "request_id", "req-015")["justification"] = ""                    # 15: approval with no reason
        rq(16)["justification"] = ""                                                     # 16: request with no reason
        ap19 = row(approvals, "request_id", "req-019")["reviewedDateTime"]               # 19: granted two days early
        grants[18]["created_on"] = z(dt.datetime.strptime(ap19, "%Y-%m-%dT%H:%M:%SZ") - dt.timedelta(days=2))
        a22 = row(assignments, "id", "asg-022")                                          # 22: no end date
        a22["schedule_expiration_type"], a22["schedule_expiration_endDateTime"] = "noExpiration", ""
        row(assignments, "id", "asg-025")["schedule_expiration_endDateTime"] = "2026-10-15T00:00:00Z"   # 25: ended
        r9, a9 = rq(9), row(assignments, "id", "asg-009")          # 9: removed in Entra, grant left on the platform
        a9["state"] = "expired"
        requests.append({**r9, "id": "req-031", "requestType": "adminRemove", "requestor_objectId": admin,
                         "justification": "Project closed", "createdDateTime": "2026-10-20T09:00:00Z",
                         "completedDateTime": "2026-10-20T09:05:00Z"})
        g(28)["role"] = "LEGACY_ADMIN"                                                   # 28: role not in the map
    header_fault = False
    if plant_input:
        grants[4]["created_on"] = "03/04/26"                                  # not a timestamp
        grants[5]["grantee_name"] = ""                                        # no grantee
        requests.append(dict(requests[9]))                                    # the same request id twice
        requests[11]["state"] = "approved"                                    # not a state Graph defines
        assignments[13]["schedule_expiration_type"] = "forever"               # not an expiration type Graph defines
        rolemap.append({"role": "OLD_ROLE", "access_package_id": "pkg-old"})  # matches no grant
        rolemap.append(dict(rolemap[0]))                                      # one role mapped twice
        header_fault = True                                                   # approvals.csv: justification -> reason
    tables = {"grants": grants, "role_packages": rolemap, "requests": requests, "approvals": approvals,
              "assignments": assignments}
    for name, rows in tables.items():
        cols = list(COLUMN_MAP[name])
        with open(folder / FILE_NAMES[name], "w", newline="") as f:
            w = csv.writer(f)
            w.writerow(cols)
            w.writerows([[r[c] for c in cols] for r in rows])
    if header_fault:
        p = folder / FILE_NAMES["approvals"]
        p.write_text(p.read_text().replace("reviewedDateTime,justification", "reviewedDateTime,reason", 1))


# ---------------------------------------------------------------------------------------------
# ENGINE
# ---------------------------------------------------------------------------------------------
def main():
    args = sys.argv[1:]
    global DATA_DIR
    out = pathlib.Path(args[args.index("--out") + 1]) if "--out" in args else pathlib.Path("who-approved-out")
    plant_error, plant_input = "--plant-error" in args, "--plant-input" in args
    suffix = "-planted-error" if plant_error else "-planted-input" if plant_input else ""
    if "--demo" in args:
        print("Demo: checking the generated records' column names against Microsoft Graph's metadata")
        check_graph_names(out)
        gen = out / f"generated{suffix}"
        generate(gen, plant_error, plant_input)
        DATA_DIR = str(gen)
        print(f"Generated records written to {gen}/ (seed 124; not a company's data).")
    if DATA_DIR is None:
        print("Set DATA_DIR at the top of the script (or run with --demo).")
        return 2
    folder = pathlib.Path(DATA_DIR)
    out.mkdir(parents=True, exist_ok=True)
    problems_found = []

    def check(name, bad, detail=""):
        print(f"  [{'FAIL' if bad else 'ok'}] {name}" + (f": {detail}" if bad and detail else ""))
        if bad:
            problems_found.append(name)

    con = duckdb.connect()
    print(f"\nRun at {RUN_AT}. Exports taken {AS_OF} (UTC).")
    print("\nInput checks")
    bad_date = not (isinstance(AS_OF, str) and re.fullmatch(r"\d{4}-\d{2}-\d{2}", AS_OF))
    if not bad_date:
        try:
            dt.date.fromisoformat(AS_OF)
        except ValueError:
            bad_date = True
    check("AS_OF is a real date written YYYY-MM-DD", bad_date, f"AS_OF is {AS_OF!r}")
    if bad_date:
        return 2

    # --- load every file; a missing file or column is a failure, the table is then empty
    bad_files = []
    for name, cmap in COLUMN_MAP.items():
        path = folder / FILE_NAMES[name]
        have = []
        if path.is_file():
            have = [d[0] for d in con.execute("SELECT * FROM read_csv(?, header=true, all_varchar=true) LIMIT 0",
                                              [str(path)]).description]
        lowered = {h.strip().lower(): h for h in have}
        missing = [theirs for theirs in cmap.values() if theirs.strip().lower() not in lowered]
        if not path.is_file():
            bad_files.append(f"{FILE_NAMES[name]} is missing")
        elif missing:
            bad_files.append(f"{FILE_NAMES[name]} has no column {', '.join(missing)}")
        if path.is_file() and not missing:
            sel = ", ".join(f'"{lowered[theirs.strip().lower()]}" AS "{mine}"' for mine, theirs in cmap.items())
            con.execute(f"CREATE TABLE t_{name} AS SELECT {sel} FROM read_csv(?, header=true, all_varchar=true)",
                        [str(path)])
        else:
            con.execute(f"CREATE TABLE t_{name} ({', '.join(chr(34) + c + chr(34) + ' VARCHAR' for c in cmap)})")
    check("every file is there with every column the script reads", bad_files, "; ".join(bad_files))
    counts = {n: con.execute(f"SELECT count(*) FROM t_{n}").fetchone()[0] for n in COLUMN_MAP}
    for n, c in counts.items():
        print(f"      read {c} rows from {FILE_NAMES[n]}")

    # --- unreadable values are counted, never skipped
    unreadable = []
    # need: TRUE = every row must have it; otherwise a condition on the row (an Approved stage needs its time)
    for tbl, col, need in [("grants", "created_on", "TRUE"), ("requests", "createdDateTime", "TRUE"),
                           ("requests", "completedDateTime", None),
                           ("approvals", "reviewedDateTime", "trim(reviewResult) = 'Approved'"),
                           ("assignments", "schedule_startDateTime", None),
                           ("assignments", "schedule_expiration_endDateTime", None)]:
        given = blank(chr(34) + col + chr(34)) + " IS NOT NULL"
        n = con.execute(f"SELECT count(*) FROM t_{tbl} WHERE ({ts(chr(34) + col + chr(34))}) IS NULL AND "
                        f"({given if need is None else given + ' OR ' + need})").fetchone()[0]
        if n:
            unreadable.append(f"{FILE_NAMES[tbl]}.{col}: {n}")
    check("every date and time is written as YYYY-MM-DD[T]HH:MM:SS (UTC)", unreadable,
          "not readable: " + ", ".join(unreadable))
    n = con.execute(f"SELECT count(*) FROM t_grants WHERE {blank('grantee_name')} IS NULL OR {blank('role')} IS NULL").fetchone()[0]
    check("every grant names a grantee and a role", n, f"{n} grant row(s) with no grantee or no role")
    n = con.execute(f"SELECT count(*) - count(DISTINCT lower(trim(id))) + count(*) FILTER (WHERE {blank('id')} IS NULL) "
                    f"FROM t_requests").fetchone()[0]
    check("every request id is there once", n, f"{n} request row(s) repeat an id or have none")
    odd = []
    for tbl, col, known in [("requests", "state", KNOWN_STATES), ("requests", "requestType", KNOWN_REQUEST_TYPES),
                            ("approvals", "reviewResult", KNOWN_REVIEW_RESULTS),
                            ("assignments", "schedule_expiration_type", KNOWN_EXPIRATION_TYPES),
                            ("assignments", "state", KNOWN_ASSIGNMENT_STATES)]:
        bad = con.execute(f"SELECT count(*) FROM t_{tbl} WHERE trim({col}) NOT IN ({quote_list(known)}) "
                          f"AND {blank(col)} IS NOT NULL").fetchone()[0]
        if bad:
            odd.append(f"{FILE_NAMES[tbl]}.{col}: {bad}")
    check("every state, type and result is one Microsoft documents", odd, "unknown values: " + ", ".join(odd))
    n = con.execute("SELECT count(*) FROM t_role_packages m WHERE NOT EXISTS (SELECT 1 FROM t_grants g "
                    "WHERE lower(trim(g.role)) = lower(trim(m.role)))").fetchone()[0]
    check("every role in role_packages holds at least one grant", n, f"{n} role(s) match no grant: a typo or a stale row")
    n = con.execute("SELECT count(*) - count(DISTINCT lower(trim(role))) FROM t_role_packages").fetchone()[0]
    check("every role maps to one access package", n, f"{n} role(s) listed more than once")
    if problems_found:
        print(f"\nINCOMPLETE: {len(problems_found)} input check(s) failed, so no findings are computed. Fix the input.")
        return 2

    # --- match every grant to its request, approvals and assignment
    asof = f"TIMESTAMP '{AS_OF} 00:00:00'"
    con.execute(f"""CREATE TEMP TABLE g AS
        SELECT row_number() OVER () AS grant_no, lower(trim(grantee_name)) AS grantee, lower(trim(role)) AS role,
               trim(role) AS role_shown, ({ts('created_on')}) AS granted_at FROM t_grants""")
    con.execute("""CREATE TEMP TABLE pkg AS SELECT lower(trim(role)) AS role, lower(trim(access_package_id)) AS package
                   FROM t_role_packages""")
    con.execute(f"""CREATE TEMP TABLE req AS
        SELECT lower(trim(id)) AS id, lower(trim(assignment_target_principalName)) AS principal,
               lower(trim(accessPackage_id)) AS package, lower(trim(requestor_objectId)) AS oid,
               ({ts('completedDateTime')}) AS completed, ({ts('createdDateTime')}) AS created,
               {blank('justification')} AS justification
        FROM t_requests WHERE trim(state) = 'delivered' AND trim(requestType) IN ({quote_list(GRANT_REQUEST_TYPES)})""")
    con.execute(f"""CREATE TEMP TABLE rem AS
        SELECT lower(trim(id)) AS id, trim(requestType) AS rtype, lower(trim(assignment_id)) AS assignment_id
        FROM t_requests WHERE trim(state) = 'delivered' AND trim(requestType) IN ({quote_list(REMOVE_REQUEST_TYPES)})""")
    con.execute(f"""CREATE TEMP TABLE apr AS
        SELECT lower(trim(request_id)) AS request_id,
               CASE WHEN lower({blank('reviewedBy_id')}) = '{ZERO_GUID}' THEN NULL ELSE lower({blank('reviewedBy_id')}) END AS reviewer,
               {blank('reviewedBy_displayName')} AS reviewer_name, ({ts('reviewedDateTime')}) AS at,
               {blank('justification')} AS justification
        FROM t_approvals WHERE trim(reviewResult) = 'Approved'""")
    con.execute(f"""CREATE TEMP TABLE asg AS
        SELECT lower(trim(id)) AS id, lower(trim(target_principalName)) AS principal,
               lower(trim(accessPackage_id)) AS package, trim(state) AS state, trim(schedule_expiration_type) AS etype, ({ts('schedule_expiration_endDateTime')}) AS ends_at,
               row_number() OVER (PARTITION BY lower(trim(target_principalName)), lower(trim(accessPackage_id))
                                  ORDER BY ({ts('schedule_startDateTime')}) DESC NULLS LAST, id) AS rk
        FROM t_assignments""")
    con.execute("""CREATE TEMP TABLE ev AS
        WITH cand AS (
          SELECT g.grant_no, r.id AS request_id, r.oid, r.created, r.justification AS req_reason,
                 row_number() OVER (PARTITION BY g.grant_no ORDER BY coalesce(r.completed > g.granted_at, TRUE),
                                    abs(epoch(r.completed) - epoch(g.granted_at)), r.id) AS rk
          FROM g JOIN pkg p ON p.role = g.role JOIN req r ON r.principal = g.grantee AND r.package = p.package)
        SELECT c.grant_no, c.request_id, c.oid, c.created, c.req_reason,
               (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS n_appr,
               (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer = c.oid) AS n_self,
               (SELECT count(*) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL
                  AND a.justification IS NULL) AS n_blank,
               (SELECT max(a.at) FROM apr a WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS last_appr,
               (SELECT string_agg(DISTINCT coalesce(a.reviewer_name, a.reviewer), '; ') FROM apr a
                  WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS approvers,
               (SELECT string_agg(coalesce(a.justification, '(none)'), ' | ') FROM apr a
                  WHERE a.request_id = c.request_id AND a.reviewer IS NOT NULL) AS approver_reasons
        FROM cand c WHERE c.rk = 1""")
    con.execute(f"""CREATE TEMP TABLE found AS
        WITH m AS (SELECT g.*, p.package, e.request_id, e.req_reason, e.n_appr, e.n_self, e.n_blank, e.last_appr,
                          s.id AS asg_id, s.state AS asg_state, s.etype, s.ends_at, s.principal IS NOT NULL AS has_asg,
                          (SELECT string_agg(x.id || ' (' || x.rtype || ')', ', ' ORDER BY x.id) FROM rem x
                             WHERE x.assignment_id = s.id) AS removed_by,
                          p.role IS NOT NULL AS mapped, e.request_id IS NOT NULL AS has_req
                   FROM g LEFT JOIN pkg p ON p.role = g.role LEFT JOIN ev e ON e.grant_no = g.grant_no
                   LEFT JOIN asg s ON s.principal = g.grantee AND s.package = p.package AND s.rk = 1)
        SELECT grant_no, 'UNMAPPED_ROLE' AS code, 'role ' || role_shown || ' is not in role_packages' AS detail
          FROM m WHERE NOT mapped
        UNION ALL SELECT grant_no, 'NO_REQUEST', 'no delivered request for this person and package' FROM m
          WHERE mapped AND NOT has_req
        UNION ALL SELECT grant_no, 'NO_APPROVAL', 'request ' || request_id || ' has no approval by a named reviewer'
          FROM m WHERE has_req AND n_appr = 0
        UNION ALL SELECT grant_no, 'SELF_APPROVAL', 'request ' || request_id || ' was approved by the person who asked'
          FROM m WHERE has_req AND n_self > 0
        UNION ALL SELECT grant_no, 'NO_REASON',
               'request ' || request_id || ': ' || CASE WHEN req_reason IS NULL AND n_blank > 0 THEN 'no reason from the requester or the approver'
                 WHEN req_reason IS NULL THEN 'no reason from the requester' ELSE 'no reason from the approver' END
          FROM m WHERE has_req AND (req_reason IS NULL OR n_blank > 0)
        UNION ALL SELECT grant_no, 'GRANT_BEFORE_APPROVAL', 'granted ' || strftime(granted_at, '%Y-%m-%d %H:%M')
               || ', last approval ' || strftime(last_appr, '%Y-%m-%d %H:%M')
          FROM m WHERE has_req AND n_appr > 0 AND last_appr > granted_at
        UNION ALL SELECT grant_no, 'NO_END_DATE', CASE WHEN NOT has_asg THEN 'no assignment for this person and package'
                 ELSE 'assignment type ' || etype || ' holds no end date' END
          FROM m WHERE mapped AND ends_at IS NULL
        UNION ALL SELECT grant_no, 'REMOVED_IN_ENTRA', 'assignment ' || asg_id || ' is '
               || coalesce(asg_state, '(blank)') || coalesce('; removed by request ' || removed_by, '')
          FROM m WHERE has_asg AND (coalesce(asg_state, '') <> 'delivered' OR removed_by IS NOT NULL)
        UNION ALL SELECT grant_no, 'PAST_END_DATE', 'ended ' || strftime(ends_at, '%Y-%m-%d') || ', grant still present'
          FROM m WHERE mapped AND ends_at < {asof}""")
    n_grants = con.execute("SELECT count(*) FROM g").fetchone()[0]
    if n_grants != counts["grants"]:   # a guard on the script itself, not an input check: it counts the grants it numbered
        sys.exit(f"Internal error: {counts['grants']} grants read but {n_grants} matched.")

    print("\nFindings (one line per gap; a grant can have more than one)")
    rows = con.execute("""SELECT f.grant_no, g.grantee, g.role_shown, f.code, f.detail FROM found f JOIN g USING (grant_no)
                          ORDER BY f.grant_no, f.code""").fetchall()
    for no, who, role, code, detail in rows:
        print(f"  grant {no:>2}  {who:<28} {role:<16} {code:<21} {detail}")
    if not rows:
        print("  none")
    flagged = con.execute("SELECT count(DISTINCT grant_no) FROM found").fetchone()[0]
    print("\nCounts by finding")
    by = dict(con.execute("SELECT code, count(*) FROM found GROUP BY 1").fetchall())
    for c in CODES:
        print(f"  {c:<21} {by.get(c, 0)}")
    print(f"\n{n_grants} grants read: {n_grants - flagged} matched to a request, an approver, a reason and an end date; "
          f"{flagged} with at least one finding.")

    # --- the evidence file: one row per grant
    evidence = out / f"grant_evidence{suffix}.csv"
    cur = con.execute("""SELECT g.grant_no, g.grantee, g.role_shown AS role, strftime(g.granted_at, '%Y-%m-%d %H:%M:%S') AS granted_at,
              p.package AS access_package, e.request_id, strftime(e.created, '%Y-%m-%d %H:%M:%S') AS requested_at,
              e.req_reason AS requester_reason, e.approvers, e.approver_reasons,
              strftime(e.last_appr, '%Y-%m-%d %H:%M:%S') AS approved_at, strftime(s.ends_at, '%Y-%m-%d') AS access_ends,
              coalesce((SELECT string_agg(f.code, ' ' ORDER BY f.code) FROM found f WHERE f.grant_no = g.grant_no),
                       'MATCHED') AS verdict
        FROM g LEFT JOIN pkg p ON p.role = g.role LEFT JOIN ev e ON e.grant_no = g.grant_no
        LEFT JOIN asg s ON s.principal = g.grantee AND s.package = p.package AND s.rk = 1 ORDER BY g.grant_no""")
    cols, data = [d[0] for d in cur.description], cur.fetchall()
    with open(evidence, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(cols)
        w.writerows([[cell(v) for v in r] for r in data])
    print(f"Evidence for every grant written to {evidence} ({len(data)} rows).")
    print("\nThree matched grants, as an auditor would read them")
    shown = 0
    for r in data:
        d = dict(zip(cols, r))
        if d["verdict"] == "MATCHED" and shown < 3:
            shown += 1
            print(f"  grant {d['grant_no']}: {d['grantee']} holds {d['role']}; asked {d['requested_at']} "
                  f"({d['requester_reason']}); approved {d['approved_at']} by {d['approvers']} ({d['approver_reasons']}); "
                  f"access ends {d['access_ends']}")
    return 1 if ("--strict" in args and rows) else 0


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