#!/usr/bin/env python3
"""Find what reads your Purview catalog: every app, managed identity and person that signed in to Purview's API,
from your own Microsoft Entra sign-in logs, joined to the owners you record.

Reads CSV exports of four Log Analytics tables, each filtered to the Purview resource (queries on the guide page):
    service-principals.csv   AADServicePrincipalSignInLogs
    managed-identities.csv   AADManagedIdentitySignInLogs
    users.csv                SigninLogs and AADNonInteractiveUserSignInLogs
plus owners.csv (caller_id, owner, planned_action): your record of who owns each caller.

    python3 find_catalog_readers.py <folder with the CSVs> [--as-of 2026-11-21] [--active-days 30] [--out <folder>]
    python3 find_catalog_readers.py --make-demo <folder> [--plant-errors]

Writes readers.csv (one row per caller) to --out (default: this script's folder).
Guide: https://www.datameaning.com/guides/find-what-reads-your-purview-catalog/
Needs Python 3.9+ and duckdb (pip install duckdb).
"""
import argparse
import csv
import datetime as dt
import os
import pathlib
import re
import random
import sys
import uuid

import duckdb

# The columns each export must hold: Azure Monitor Logs reference pages for the four tables (read 2026-11-21).
SOURCES = {
    "service-principals.csv": ("app", ["TimeGenerated", "ServicePrincipalId", "ServicePrincipalName", "AppId",
                                       "ResourceDisplayName", "ResultType", "IPAddress"]),
    "managed-identities.csv": ("managed identity", ["TimeGenerated", "ServicePrincipalId", "ServicePrincipalName", "AppId",
                                                    "ResourceDisplayName", "ResultType", "IPAddress"]),
    "users.csv": ("person", ["TimeGenerated", "UserId", "UserPrincipalName", "AppDisplayName", "ResourceDisplayName",
                             "ResultType", "IPAddress"]),
}
# PERSON_DIRECT flags a person whose sign-in app name contains one of these words: scripts and command-line tools.
# A person who signs in only through the Purview portal is not flagged. Add the names your tenant shows in users.csv.
SCRIPT_CLIENTS = ["CLI", "PowerShell", "Python", "SDK", "Postman", "Graph Explorer", "Visual Studio"]
# The guide's queries write TimeGenerated as ISO 8601 text. A column exported without that step can carry a suffix such
# as "TimeGenerated [UTC]" and US times such as "11/21/2026, 6:48:12.123 PM"; both are read too. Other regional
# formats are not: add the query's extend line, or add the format here.
TIME_FORMATS = ["%m/%d/%Y, %I:%M:%S.%g %p", "%m/%d/%Y, %I:%M:%S %p"]
CHECKS = [
    ("NO_OWNER", "Every caller that signed in has an owner in owners.csv"),
    ("NO_PLAN", "Every active caller has a planned action"),
    ("ONLY_FAILURES", "No caller has only failed sign-ins (a broken integration nobody noticed)"),
    ("PERSON_DIRECT", "No person calls the API directly from a script or tool"),
]


def stop(msg):
    print(f"CANNOT READ THE INPUT: {msg}")
    sys.exit(2)


def run(folder, as_of, active_days, out, tag=""):
    folder = pathlib.Path(folder)
    if not folder.is_dir():
        stop(f"{folder} is not a folder")
    con = duckdb.connect()
    con.execute("CREATE TABLE s (kind VARCHAR, caller_id VARCHAR, caller VARCHAR, via VARCHAR, resource VARCHAR, "
                "ok BOOLEAN, signed_at TIMESTAMP, ip VARCHAR)")
    found = 0
    for name, (kind, cols) in SOURCES.items():
        f = folder / name
        if not f.is_file():
            print(f"  {name:<24} not found, skipped")
            continue
        found += 1
        have = [r[0] for r in con.execute("DESCRIBE SELECT * FROM read_csv(?, header = true, all_varchar = true)",
                                          [str(f)]).fetchall()]
        col = {re.sub(r"\s*\[[^\]]*\]$", "", h).strip(): '"' + h.replace('"', '""') + '"' for h in have}
        missing = [c for c in cols if c not in col]
        if missing:
            stop(f"{name} has no column {', '.join(missing)}: export the columns the guide's query projects")
        c = col.get
        blank = lambda x: f"nullif(lower(trim({x})), '')"
        if kind == "person":   # a failed sign-in can carry no object id: fall back to the sign-in name
            sel = f"coalesce({blank(c('UserId'))}, {blank(c('UserPrincipalName'))}, '(no id)'), " \
                  f"coalesce(nullif(trim({c('UserPrincipalName')}), ''), '(no name)'), {c('AppDisplayName')}"
        else:                  # fall back to the app id, then to the name
            sel = f"coalesce({blank(c('ServicePrincipalId'))}, {blank(c('AppId'))}, '(no id)'), " \
                  f"coalesce(nullif(trim({c('ServicePrincipalName')}), ''), {c('AppId')}, '(no name)'), {c('AppId')}"
        t = c("TimeGenerated")
        when = ", ".join([f"TRY_CAST({t} AS TIMESTAMP)"] + [f"try_strptime({t}, '{fmt}')" for fmt in TIME_FORMATS])
        con.execute(f"""INSERT INTO s SELECT ?, {sel}, {c('ResourceDisplayName')}, trim({c('ResultType')}) = '0',
                        coalesce({when}), {c('IPAddress')}
                        FROM read_csv(?, header = true, all_varchar = true)""", [kind, str(f)])
    if not found:
        stop(f"none of {', '.join(SOURCES)} is in {folder}")
    if not con.execute("SELECT count(*) FROM s").fetchone()[0]:
        stop("the exports hold no sign-ins: check the resource name in the queries and the time range")
    bad = con.execute("SELECT count(*) FROM s WHERE signed_at IS NULL").fetchone()[0]
    if bad:
        stop(f"{bad} sign-ins have no readable TimeGenerated")
    con.execute("CREATE TABLE o (caller_id VARCHAR, owner VARCHAR, planned_action VARCHAR)")
    of = folder / "owners.csv"
    if of.is_file():
        have = [r[0] for r in con.execute("DESCRIBE SELECT * FROM read_csv(?, header = true, all_varchar = true)",
                                          [str(of)]).fetchall()]
        if not {"caller_id", "owner", "planned_action"} <= set(have):
            stop("owners.csv needs the columns caller_id, owner and planned_action")
        con.execute("""INSERT INTO o SELECT lower(trim(caller_id)),   -- two rows for one caller are joined, not counted twice
                       string_agg(DISTINCT nullif(trim(owner), ''), '; ' ORDER BY nullif(trim(owner), '')),
                       string_agg(DISTINCT nullif(trim(planned_action), ''), '; ' ORDER BY nullif(trim(planned_action), ''))
                       FROM read_csv(?, header = true, all_varchar = true) GROUP BY 1""", [str(of)])
    n = con.execute("SELECT count(*), count(DISTINCT caller_id), min(signed_at), max(signed_at) FROM s").fetchone()
    print(f"Sign-ins: {n[0]} from {n[1]} callers, {n[2]:%Y-%m-%d} to {n[3]:%Y-%m-%d}; resources: "
          + ", ".join(r[0] for r in con.execute("SELECT DISTINCT resource FROM s ORDER BY 1").fetchall()))
    con.execute(f"""CREATE TABLE r AS SELECT s.kind, s.caller_id, max(s.caller) AS caller, string_agg(DISTINCT s.via, '; ' ORDER BY s.via) AS via,
            count(*) AS sign_ins, count(*) FILTER (WHERE ok) AS succeeded, min(signed_at) AS first_seen, max(signed_at) AS last_seen,
            count(DISTINCT ip) AS addresses, max(o.owner) AS owner, max(o.planned_action) AS planned_action,
            max(signed_at) >= TIMESTAMP '{as_of}' - INTERVAL {int(active_days)} DAY AS active
        FROM s LEFT JOIN o ON o.caller_id = s.caller_id GROUP BY s.kind, s.caller_id""")
    findings = []
    findings += [("NO_OWNER", k, c, "no row in owners.csv") for k, c in con.execute(
        "SELECT kind, caller FROM r WHERE owner IS NULL ORDER BY kind, caller").fetchall()]
    findings += [("NO_PLAN", k, c, f"last signed in {ls:%Y-%m-%d}, no planned action") for k, c, ls in con.execute(
        "SELECT kind, caller, last_seen FROM r WHERE active AND planned_action IS NULL ORDER BY kind, caller").fetchall()]
    findings += [("ONLY_FAILURES", k, c, f"{n} sign-in{'' if n == 1 else 's'}, none succeeded") for k, c, n in con.execute(
        "SELECT kind, caller, sign_ins FROM r WHERE succeeded = 0 ORDER BY kind, caller").fetchall()]
    script = " OR ".join(f"contains(lower(coalesce(via, '')), {chr(39) + w.lower().replace(chr(39), chr(39) * 2) + chr(39)})"
                         for w in SCRIPT_CLIENTS)
    findings += [("PERSON_DIRECT", k, c, f"signs in through {v}") for k, c, v in con.execute(
        f"SELECT kind, caller, via FROM r WHERE kind = 'person' AND ({script}) ORDER BY caller").fetchall()]

    print()
    print(f"  {'Kind':<17} {'Caller':<34} {'Sign-ins':>8} {'OK':>5} {'Last seen':<11} {'Owner':<26} Active")
    for row in con.execute("""SELECT kind, caller, sign_ins, succeeded, last_seen, coalesce(owner, '-'), active
                              FROM r ORDER BY active DESC, sign_ins DESC, caller""").fetchall():
        k, c, s_, ok, ls, ow, act = row
        print(f"  {k:<17} {c[:34]:<34} {s_:>8} {ok:>5} {ls:%Y-%m-%d}  {ow[:26]:<26} {'yes' if act else 'no'}")
    print()
    print(f"Checks (active means signed in within {active_days} days of {as_of})")
    for code, text in CHECKS:
        k = sum(1 for f in findings if f[0] == code)
        print(f"  {'PASS' if k == 0 else 'FAIL'}  {text}" + (f"  ({k})" if k else ""))
    for code, kind, caller, detail in findings:
        print(f"  {code:<14} {kind}: {caller}: {detail}")

    out = pathlib.Path(out)
    out.mkdir(parents=True, exist_ok=True)
    suffix = f"-{tag}" if tag else ""
    rows = con.execute("""SELECT kind, caller_id, caller, via, sign_ins, succeeded, strftime(first_seen, '%Y-%m-%d %H:%M'),
            strftime(last_seen, '%Y-%m-%d %H:%M'), addresses, coalesce(owner, ''), coalesce(planned_action, ''), active
        FROM r ORDER BY kind, caller""").fetchall()
    with open(out / f"readers{suffix}.csv", "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.writer(fh)
        w.writerow(["kind", "caller_id", "caller", "client_or_app_id", "sign_ins", "succeeded", "first_seen", "last_seen",
                    "ip_addresses", "owner", "planned_action", "active"])
        w.writerows([["'" + str(x) if str(x)[:1] and str(x)[0] in "=+-@" else x for x in r] for r in rows])
    try:
        where = os.path.relpath(out)
    except ValueError:
        where = str(out)
    print()
    print(f"Wrote readers{suffix}.csv ({len(rows)} callers) to {where}")
    print(f"RESULT: {len(rows)} caller{'' if len(rows) == 1 else 's'} read the catalog; {len(findings)} "
          f"finding{'' if len(findings) == 1 else 's'} to settle before the switch-off.")
    return len(findings)


# ---------------------------------------------------------------------------------------------
def make_demo(folder, plant):
    rng = random.Random(276)
    gid = lambda: str(uuid.UUID(int=rng.getrandbits(128), version=4))
    folder = pathlib.Path(folder)
    folder.mkdir(parents=True, exist_ok=True)
    res = "Microsoft Purview"
    apps = [("sp", gid(), "powerbi-catalog-refresh", gid()), ("sp", gid(), "adf-lineage-push", gid()),
            ("sp", gid(), "glossary-sync-to-wiki", gid()), ("mi", gid(), "func-steward-alerts", gid()),
            ("mi", gid(), "logicapp-access-requests", gid())]
    users = [(gid(), "data.steward@example.com"), (gid(), "governance.lead@example.com")]
    start = dt.datetime(2026, 9, 1)
    def rows_for(n, last_day, ok=True):
        days = sorted(rng.randint(0, last_day) for _ in range(n))
        return [(start + dt.timedelta(days=d, hours=rng.randint(6, 20), minutes=rng.randint(0, 59)), ok) for d in days]
    sp, mi, us = [], [], []
    for kind, spid, name, appid in apps:
        last = 81 if name != "glossary-sync-to-wiki" else 40      # one app stopped calling in October
        for at, ok in rows_for(25, last):
            (sp if kind == "sp" else mi).append([at.strftime("%Y-%m-%dT%H:%M:%SZ"), spid, name, appid, res, "0" if ok else "50126", f"10.0.0.{rng.randint(2, 40)}"])
    owners = [(spid, f"team-{name.split('-')[0]}@example.com", "repoint to the new catalog" if name != "glossary-sync-to-wiki" else "retire")
              for _, spid, name, _ in apps]
    if plant:
        broken = ("sp", gid(), "nightly-term-export", gid())     # ONLY_FAILURES
        for at, _ in rows_for(6, 80):
            sp.append([at.strftime("%Y-%m-%dT%H:%M:%SZ"), broken[1], broken[2], broken[3], res, "7000215", "10.0.0.9"])
        owners.append((broken[1], "team-nightly@example.com", "fix or retire"))
        owners = [o for o in owners if o[0] != apps[1][1]]        # NO_OWNER: adf-lineage-push
        owners = [(a, b, "" if a == apps[3][1] else c) for a, b, c in owners]   # NO_PLAN: func-steward-alerts
        uid, upn = users[0]                                       # PERSON_DIRECT
        for at, _ in rows_for(4, 78):
            us.append([at.strftime("%Y-%m-%dT%H:%M:%SZ"), uid, upn, "Azure CLI", res, "0", "10.1.0.5"])
    def w(name, header, rows):
        with open(folder / name, "w", newline="") as fh:
            cw = csv.writer(fh); cw.writerow(header); cw.writerows(rows)
    hdr = ["TimeGenerated", "ServicePrincipalId", "ServicePrincipalName", "AppId", "ResourceDisplayName", "ResultType", "IPAddress"]
    w("service-principals.csv", hdr, sp)
    w("managed-identities.csv", hdr, mi)
    w("users.csv", ["TimeGenerated", "UserId", "UserPrincipalName", "AppDisplayName", "ResourceDisplayName", "ResultType", "IPAddress"], us)
    w("owners.csv", ["caller_id", "owner", "planned_action"], owners)
    print(f"Wrote generated sign-in exports to {folder}: {len(sp)} service principal, {len(mi)} managed identity and "
          f"{len(us)} user sign-ins, {len(owners)} owner rows (seed 276)")
    if plant:
        print("Planted one error for each check: NO_OWNER, NO_PLAN, ONLY_FAILURES, PERSON_DIRECT")


def main():
    ap = argparse.ArgumentParser(description="Find every caller of the Purview catalog API in your Entra sign-in logs.")
    ap.add_argument("folder", nargs="?")
    ap.add_argument("--as-of", default=dt.date.today().isoformat())
    ap.add_argument("--active-days", type=int, default=30)
    ap.add_argument("--out", default=str(pathlib.Path(__file__).resolve().parent))
    ap.add_argument("--make-demo", metavar="FOLDER")
    ap.add_argument("--plant-errors", action="store_true")
    ap.add_argument("--tag", default="")
    a = ap.parse_args()
    if a.make_demo:
        make_demo(a.make_demo, a.plant_errors)
        return 0
    if not a.folder:
        ap.error("give the folder with the exports, or --make-demo FOLDER")
    try:
        dt.date.fromisoformat(a.as_of)
    except ValueError:
        ap.error("--as-of must be a date like 2026-11-21")
    print(f"Python {sys.version.split()[0]} duckdb {duckdb.__version__}")
    run(a.folder, a.as_of, a.active_days, a.out, a.tag)
    return 0


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