#!/usr/bin/env python3
"""Keep Purview governance evidence after the switch-off: a read-only Parquet archive with file hashes, and
ten audit questions answered in SQL from the archive alone.

Archive (reads a saved Unified Catalog export, the layout the inventory and export-check guides use):
    python3 archive_evidence.py archive <export folder> <archive folder>
Verify (recomputes every hash and compares it with the manifest):
    python3 archive_evidence.py verify <archive folder>
Ask (runs the ten questions in audit_questions.sql against the archive):
    python3 archive_evidence.py ask <archive folder>
Demo:
    python3 archive_evidence.py --make-demo <folder> [--tamper <archive folder>]

The archive holds one Parquet file per object type (terms, term_links, businessdomains, dataProducts,
criticalDataElements, objectives, dataAssets, policies), manifest.csv (each file's rows, bytes and SHA-256)
and manifest.sha256 (the hash of manifest.csv). Every file is made read-only.
Guide: https://www.datameaning.com/guides/keep-purview-governance-evidence-after-switch-off/
Needs Python 3.9+ and duckdb (pip install duckdb).
"""
import argparse
import csv
import hashlib
import json
import os
import pathlib
import random
import re
import stat
import sys
import tempfile
import uuid

import duckdb

TYPES = {"businessdomains": "value", "terms": "value", "dataProducts": "value", "criticalDataElements": "value",
         "objectives": "value", "dataAssets": "value", "policies": "values"}
COLUMNS = ["id", "name", "domain", "status", "owners", "last_modified_at", "last_modified_by", "detail_json"]
QUESTIONS = pathlib.Path(__file__).resolve().parent / "audit_questions.sql"


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


def sha256(path):
    h = hashlib.sha256()
    with open(path, "rb") as fh:
        for block in iter(lambda: fh.read(1 << 20), b""):
            h.update(block)
    return h.hexdigest()


def read_json(path):
    try:
        with open(path, encoding="utf-8-sig") as fh:
            return json.load(fh)
    except (OSError, ValueError) as e:
        stop(f"{path} is not readable JSON ({e.__class__.__name__}: {e})")


def page_no(p):
    m = re.fullmatch(r"page-(\d+)\.json", p.name)
    return int(m.group(1)) if m else -1


def rows_of(export, otype):
    pages = sorted((p for p in (export / otype).glob("page-*.json") if page_no(p) >= 0), key=page_no)
    if not pages:
        return None, []
    nums = [page_no(p) for p in pages]
    if nums != list(range(1, nums[-1] + 1)):
        stop(f"{otype}: a page between 1 and {nums[-1]} was not saved")
    out = []
    for p in pages:
        doc = read_json(p)
        key = TYPES[otype]
        if not isinstance(doc, dict) or not isinstance(doc.get(key), list):
            stop(f"{p} has no \"{key}\" list")
        for o in doc[key]:
            if not isinstance(o, dict) or not o.get("id"):
                stop(f"{p} holds an object with no id")
            sd = o.get("systemData") if isinstance(o.get("systemData"), dict) else {}
            owners = ";".join(sorted(str(c.get("id")) for c in ((o.get("contacts") or {}).get("owner") or [])
                                     if isinstance(c, dict) and c.get("id"))) if isinstance(o.get("contacts"), dict) else ""
            out.append((str(o["id"]).lower(), str(o.get("name") or o.get("definition") or ""),
                        str(o.get("domain") or "").lower(), str(o.get("status") or "").upper(), owners,
                        str(sd.get("lastModifiedAt") or ""), str(sd.get("lastModifiedBy") or ""),
                        json.dumps(o, sort_keys=True, ensure_ascii=False)))
    last = read_json(pages[-1])
    if last.get("nextLink") or last.get("skipToken"):
        stop(f"{pages[-1]} still points to a next page: save every page first")
    return pages, out


def sql_path(path):
    """A file path as a SQL string literal: a quote in a folder name is doubled, not read as the end of the string."""
    return "'" + pathlib.Path(path).as_posix().replace("'", "''") + "'"


def write_table(con, name, columns, rows, path):
    con.execute(f"CREATE OR REPLACE TABLE t ({', '.join(c + ' VARCHAR' for c in columns)})")
    if rows:
        with tempfile.TemporaryDirectory() as tmp:
            src = pathlib.Path(tmp) / "rows.csv"
            with open(src, "w", newline="", encoding="utf-8") as fh:
                csv.writer(fh).writerows(rows)
            con.execute("INSERT INTO t SELECT * FROM read_csv(?, header = false, all_varchar = true, "
                        "quote = '\"', escape = '\"')", [str(src)])
    con.execute(f"COPY (SELECT * FROM t ORDER BY 1, 2) TO {sql_path(path)} (FORMAT parquet)")
    return con.execute("SELECT count(*) FROM t").fetchone()[0]


def archive(export, dest):
    export, dest = pathlib.Path(export), pathlib.Path(dest)
    if not export.is_dir():
        stop(f"{export} is not a folder")
    if dest.exists() and any(dest.iterdir()):
        stop(f"{dest} is not empty: an archive is written once, into a new folder")
    # Read and check every file first, so a stop leaves nothing half-written in the archive folder.
    read, sources = [], []
    for otype in TYPES:
        pages, rows = rows_of(export, otype)
        if pages is None:
            print(f"  {otype:<22} not in the export, skipped")
            continue
        sources += pages
        read.append((otype, rows))
    if "terms" not in (t for t, _ in read):
        stop(f"{export} has no terms/page-*.json: point the script at the export folder itself")
    links, rel_files = [], []
    for p in sorted((export / "relationships").glob("*.json")):
        m = re.match(r"^([0-9a-fA-F-]{36})\.([A-Z]+)(?:\.\d+)?\.json$", p.name)
        if not m:
            continue
        rel_files.append(p)
        doc = read_json(p)
        for r in (doc.get("value") or []) if isinstance(doc, dict) else []:
            if isinstance(r, dict) and r.get("entityId"):
                links.append((m.group(1).lower(), m.group(2), str(r["entityId"]).lower(), str(r.get("relationshipType") or "")))
    dest.mkdir(parents=True, exist_ok=True)
    con = duckdb.connect()
    manifest = []
    for otype, rows in read:
        n = write_table(con, otype, COLUMNS, rows, dest / f"{otype}.parquet")
        if n != len(rows):
            stop(f"{otype}: {len(rows)} objects read, {n} written")
        manifest.append((f"{otype}.parquet", n))
    if links:
        n = write_table(con, "term_links", ["term_id", "entity_type", "entity_id", "relationship_type"], links,
                        dest / "term_links.parquet")
        manifest.append(("term_links.parquet", n))
    con.close()
    sources += rel_files   # the relationships files behind term_links are part of the source too
    src_hash = hashlib.sha256("".join(sha256(p) for p in sorted(sources, key=str)).encode()).hexdigest()
    with open(dest / "manifest.csv", "w", newline="", encoding="utf-8") as fh:
        w = csv.writer(fh)
        w.writerow(["file", "rows", "bytes", "sha256"])
        for f, n in manifest:
            w.writerow([f, n, (dest / f).stat().st_size, sha256(dest / f)])
        w.writerow(["source export files", len(sources), "", src_hash])
    (dest / "manifest.sha256").write_text(sha256(dest / "manifest.csv") + "  manifest.csv\n")
    for f in dest.iterdir():
        f.chmod(stat.S_IRUSR | stat.S_IRGRP | stat.S_IROTH)
    print(f"Archived {len(sources) - len(rel_files)} export pages and {len(rel_files)} relationship files into "
          f"{len(manifest)} Parquet files in {rel(dest)}:")
    for f, n in manifest:
        print(f"  {f:<30} {n:>6} rows")
    print(f"Wrote manifest.csv and manifest.sha256; every file in {rel(dest)} is now read-only.")


def verify(dest):
    dest = pathlib.Path(dest)
    problems = []
    want = (dest / "manifest.sha256").read_text().split()[0] if (dest / "manifest.sha256").is_file() else ""
    if not want:
        stop(f"{dest} has no manifest.sha256")
    if sha256(dest / "manifest.csv") != want:
        problems.append("manifest.csv does not match manifest.sha256")
    listed = set()
    with open(dest / "manifest.csv", newline="", encoding="utf-8") as fh:
        for row in csv.DictReader(fh):
            if not row["file"].endswith(".parquet"):
                continue
            listed.add(row["file"])
            f = dest / row["file"]
            if not f.is_file():
                problems.append(f"{row['file']} is missing")
            elif sha256(f) != row["sha256"]:
                problems.append(f"{row['file']} changed after it was archived: its SHA-256 no longer matches")
            else:
                n = duckdb.connect().execute(f"SELECT count(*) FROM {sql_path(f)}").fetchone()[0]
                if str(n) != row["rows"]:
                    problems.append(f"{row['file']} holds {n} rows, the manifest says {row['rows']}")
    for f in sorted(dest.iterdir()):
        if f.name not in listed and f.name not in ("manifest.csv", "manifest.sha256"):
            problems.append(f"{f.name} is in the archive but not in the manifest")
    print(f"Verified {len(listed)} files against manifest.csv in {rel(dest)}")
    for p in problems:
        print(f"  FAIL  {p}")
    print("RESULT: " + ("the archive is intact." if not problems else f"the archive is not intact (problems: {len(problems)})."))
    return len(problems)


def ask(dest):
    dest = pathlib.Path(dest)
    if verify(dest):
        stop("the archive is not intact, so its answers are not evidence")
    con = duckdb.connect()
    for f in sorted(dest.glob("*.parquet")):
        con.execute(f"CREATE VIEW {f.stem} AS SELECT * FROM {sql_path(f)}")
    if not QUESTIONS.is_file():
        stop(f"{QUESTIONS} is missing: download audit_questions.sql next to this script")
    blocks = re.split(r"(?m)^-- Q(\d+)\. ", QUESTIONS.read_text())[1:]
    print()
    for num, block in zip(blocks[0::2], blocks[1::2]):
        title, sql = block.split("\n", 1)
        try:
            cur = con.execute(sql)
        except duckdb.Error as e:
            print(f"Q{num}. {title}\n  cannot run: {e.__class__.__name__}: {str(e).splitlines()[0]}\n")
            continue
        cols = [d[0] for d in cur.description]
        rows = cur.fetchall()
        print(f"Q{num}. {title}  ({len(rows)} rows)")
        print("  " + " | ".join(cols))
        for r in rows[:10]:
            print("  " + " | ".join("" if v is None else str(v) for v in r))
        if len(rows) > 10:
            print(f"  ... {len(rows) - 10} more rows")
        print()


def rel(p):
    try:
        return os.path.relpath(p)
    except ValueError:
        return str(p)


# ---------------------------------------------------------------------------------------------
def make_demo(folder):
    rng = random.Random(277)
    new_id = lambda: str(uuid.UUID(int=rng.getrandbits(128), version=4))
    folder = pathlib.Path(folder)
    marker = folder / ".made-by-make-demo"
    if folder.exists() and any(folder.rglob("*.json")) and not marker.exists():
        sys.exit(f"{folder} already holds JSON files that --make-demo did not write; choose a new folder.")
    folder.mkdir(parents=True, exist_ok=True)
    for old in folder.rglob("*.json"):
        old.unlink()
    marker.write_text("Written by archive_evidence.py --make-demo: generated records, not an export.\n")
    people = [new_id() for _ in range(5)]
    doms = [(new_id(), n) for n in ("Finance", "Sales", "Supply Chain")]
    def w(path, doc):
        f = folder / path; f.parent.mkdir(parents=True, exist_ok=True); f.write_text(json.dumps(doc, indent=1))
    w("businessdomains/page-001.json", {"value": [{"id": d, "name": n, "status": "PUBLISHED"} for d, n in doms]})
    terms, links = [], []
    assets = [{"id": new_id(), "name": f"asset_{i + 1:02d}", "type": "AzureSqlTable"} for i in range(12)]
    for d, n in doms:
        for i in range(8):
            t = {"id": new_id(), "name": f"{n} term {i + 1}", "domain": d,
                 "status": "PUBLISHED" if i < 6 else ("DRAFT" if i == 6 else "EXPIRED"),
                 "contacts": {"owner": [{"id": rng.choice(people), "description": "Data steward"}]} if i != 5 else {"owner": []},
                 "systemData": {"lastModifiedAt": f"2026-{1 + i:02d}-15T10:00:00Z", "lastModifiedBy": rng.choice(people)}}
            terms.append(t)
            for a in rng.sample(assets, 2):
                links.append((t["id"], a["id"]))
    w("terms/page-001.json", {"value": terms[:12], "nextLink": "https://api.purview-service.microsoft.com/datagovernance/catalog/terms?api-version=2026-03-20-preview&skip=12"})
    w("terms/page-002.json", {"value": terms[12:]})
    for t in terms:
        w(f"relationships/{t['id']}.DATAASSET.json",
          {"value": [{"entityId": a, "relationshipType": "Related"} for tid, a in links if tid == t["id"]]})
    w("dataProducts/page-001.json", {"value": [{"id": new_id(), "name": f"{n} reporting", "domain": d, "status": "PUBLISHED",
                                                "contacts": {"owner": [{"id": rng.choice(people)}]}} for d, n in doms]})
    w("criticalDataElements/page-001.json", {"value": [{"id": new_id(), "name": f"{n} key figure", "domain": d,
                                                        "status": "PUBLISHED"} for d, n in doms]})
    w("objectives/page-001.json", {"value": [{"id": new_id(), "definition": f"Invented {n} objective", "domain": d,
                                              "status": "Active"} for d, n in doms]})
    w("dataAssets/page-001.json", {"value": assets})
    w("policies/page-001.json", {"values": [{"id": new_id(), "name": f"Invented access policy {i + 1}"} for i in range(2)]})
    print(f"Wrote a generated export to {rel(folder)}: {len(terms)} terms in {len(doms)} domains, {len(links)} term links, "
          f"{len(assets)} data assets (seed 277)")


def tamper(dest):
    """Change one value in an archived file the way a careless fix would, keeping the file's size and row count."""
    dest = pathlib.Path(dest)
    f = dest / "terms.parquet"
    f.chmod(stat.S_IRUSR | stat.S_IWUSR)
    con = duckdb.connect()
    rows = con.execute(f"SELECT * FROM {sql_path(f)}").fetchall()
    cols = [d[0] for d in con.description]
    con.execute(f"CREATE TABLE t AS SELECT * FROM {sql_path(f)}")
    con.execute("UPDATE t SET status = 'PUBLISHED' WHERE id = (SELECT min(id) FROM t WHERE status = 'EXPIRED')")
    con.execute(f"COPY (SELECT {', '.join(cols)} FROM t ORDER BY 1, 2) TO {sql_path(f)} (FORMAT parquet)")
    f.chmod(stat.S_IRUSR | stat.S_IRGRP | stat.S_IROTH)
    print(f"Tampered with {rel(f)}: one expired term marked PUBLISHED ({len(rows)} rows, as before)")


def main():
    ap = argparse.ArgumentParser(description="Archive Purview governance evidence and answer audit questions from it.")
    ap.add_argument("command", nargs="?", choices=["archive", "verify", "ask"])
    ap.add_argument("paths", nargs="*")
    ap.add_argument("--make-demo", metavar="FOLDER", help="write a generated export to FOLDER and stop")
    ap.add_argument("--tamper", metavar="ARCHIVE", help="change one archived value, to show that verify catches it")
    a = ap.parse_args()
    if a.make_demo:
        make_demo(a.make_demo)
        return 0
    if a.tamper:
        tamper(a.tamper)
        return 0
    print(f"Python {sys.version.split()[0]} duckdb {duckdb.__version__}")
    if a.command == "archive" and len(a.paths) == 2:
        archive(*a.paths)
    elif a.command == "verify" and len(a.paths) == 1:
        return 1 if verify(a.paths[0]) else 0
    elif a.command == "ask" and len(a.paths) == 1:
        ask(a.paths[0])
    else:
        ap.error("use: archive <export> <archive> | verify <archive> | ask <archive> | --make-demo <folder>")
    return 0


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