#!/usr/bin/env python3
"""Plan Fabric tags from Purview glossary terms, inside Microsoft's tag limits, before any tag is created.

Reads four files from one folder:

    <folder>/terms.csv            one row per glossary term: term_guid, name, status (for example the terms.csv the
                                  guide "How to export a classic Purview glossary with the Atlas API" writes)
    <folder>/assignments.csv      which Fabric item each term should tag: term_guid, workspace_id, item_id, item_name
    <folder>/tag-names.csv        optional: term_guid, tag_name, for a tag name other than the term's name
    <folder>/existing-tags/page-001.json ...   the tags Fabric already has: GET https://api.fabric.microsoft.com/v1/tags,
                                  every page, following continuationToken
    <folder>/item-tags.csv        optional: item_id, tag_name, for tags already on an item

Microsoft's limits ("Tags in Microsoft Fabric" and "Create and manage a set of tags", read 2026-11-21): a tag name is at
most 40 characters, of letters, numbers, spaces (not at the beginning) and special characters, with at least one letter
or number; an item holds at most 10 tags; a tenant holds at most 10,000 tags; a tenant tag name cannot be repeated as a
domain tag, or the other way round. A tag is a name only: the term's definition does not travel with it.

    python3 plan_fabric_tags.py <folder> [--domain-id <id>] [--out <folder>]
    python3 plan_fabric_tags.py --make-demo <folder> [--plant-errors]

Writes tag-map.csv (term to tag name), create-tags.json (the body for POST /v1/admin/tags/bulkCreateTags),
apply-plan.csv (the tags each item gets) and findings.csv to --out (default: this script's folder).
Guide: https://www.datameaning.com/guides/turn-purview-glossary-terms-into-fabric-tags/
Needs Python 3.9+ and duckdb (pip install duckdb).
"""
import argparse
import csv
import json
import os
import pathlib
import random
import re
import sys
import uuid

import duckdb

# Microsoft's limits, read 2026-11-21. Settings, so you can change them if Microsoft does.
MAX_NAME = 40
MAX_PER_ITEM = 10
MAX_PER_TENANT = 10_000
# A term moves when it is approved (the classic glossary) or published (Unified Catalog).
MOVABLE = ("approved", "published")
CHECKS = [
    ("UNKNOWN_TERM", "Every assignment names a term in terms.csv"),
    ("NOT_APPROVED", "Every term that tags an item is approved or published"),
    ("NAME_TOO_LONG", f"Every tag name is at most {MAX_NAME} characters"),
    ("NAME_NOT_ALLOWED", "Every tag name starts with no space and holds a letter or number"),
    ("NAME_CLASH", "No two terms become the same tag name, ignoring case"),
    ("SCOPE_CLASH", "No new tag repeats a tag name Fabric holds in the other scope"),
    ("OVER_ITEM_LIMIT", f"No item ends up with more than {MAX_PER_ITEM} tags"),
    ("OVER_TENANT_LIMIT", f"The tenant ends up with at most {MAX_PER_TENANT:,} tags"),
]


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


def read_csv(path, need, required=True):
    if not path.is_file():
        if required:
            stop(f"{path.name} is missing")
        return []
    with open(path, newline="", encoding="utf-8-sig") as fh:
        rd = csv.DictReader(fh)
        if not set(need) <= set(rd.fieldnames or []):
            stop(f"{path.name} needs the columns {', '.join(need)}")
        return [{k: (v or "") for k, v in r.items() if k} for r in rd]


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


def unformula(name):
    """Undo the spreadsheet guard other guides' CSV files add: a leading ' before =, +, - or @."""
    return name[1:] if name[:1] == "'" and name[1:2] in ("=", "+", "-", "@") else name


def key(name):
    return name.casefold()


def suggest(name):
    """A shorter name that fits, cut at a word boundary: a suggestion for tag-names.csv, never applied."""
    if len(name) <= MAX_NAME:
        return name
    cut = name[:MAX_NAME].rsplit(" ", 1)[0].rstrip(" -,;:")
    return cut if cut else name[:MAX_NAME]


def plan(folder, domain_id, out, tag=""):
    folder = pathlib.Path(folder)
    if not folder.is_dir():
        stop(f"{folder} is not a folder")
    terms = read_csv(folder / "terms.csv", ["term_guid", "name", "status"])
    assigns = read_csv(folder / "assignments.csv", ["term_guid", "workspace_id", "item_id"])
    overrides = {r["term_guid"].strip().lower(): r["tag_name"] for r in
                 read_csv(folder / "tag-names.csv", ["term_guid", "tag_name"], required=False)}
    item_tags = read_csv(folder / "item-tags.csv", ["item_id", "tag_name"], required=False)
    existing, findings = [], []
    pages = sorted((p for p in (folder / "existing-tags").glob("page-*.json") if page_no(p) >= 0), key=page_no)
    for i, p in enumerate(pages):
        try:
            doc = json.loads(p.read_text(encoding="utf-8-sig"))
        except (OSError, ValueError) as e:
            stop(f"{p} is not readable JSON ({e})")
        existing += [(str(t.get("id") or ""), str(t.get("displayName") or ""),
                      str((t.get("scope") or {}).get("type") or "Tenant"), str((t.get("scope") or {}).get("domainId") or "").strip().lower())
                     for t in doc.get("value") or [] if isinstance(t, dict)]
        if i == len(pages) - 1 and (doc.get("continuationToken") or doc.get("continuationUri")):
            stop(f"{p.name} still holds a continuationToken: save every page of the existing tags first")
    if not pages:
        stop("existing-tags/page-001.json is missing: save the tags Fabric already has, even when there are none")

    scope = "Domain" if domain_id else "Tenant"
    con = duckdb.connect()
    con.execute("CREATE TABLE term (guid VARCHAR, name VARCHAR, status VARCHAR, tag VARCHAR)")
    con.executemany("INSERT INTO term VALUES (?,?,?,?)", [
        (t["term_guid"].strip().lower(), unformula(t["name"]), t["status"].strip(),
         unformula(overrides.get(t["term_guid"].strip().lower(), t["name"]))) for t in terms])
    con.execute("CREATE TABLE asg (guid VARCHAR, ws VARCHAR, item VARCHAR, item_name VARCHAR)")
    con.executemany("INSERT INTO asg VALUES (?,?,?,?)", [
        (a["term_guid"].strip().lower(), a["workspace_id"].strip().lower(), a["item_id"].strip().lower(),
         a.get("item_name", "") or a["item_id"]) for a in assigns]) if assigns else None
    con.execute("CREATE TABLE ex (id VARCHAR, name VARCHAR, scope VARCHAR, domain VARCHAR)")
    con.executemany("INSERT INTO ex VALUES (?,?,?,?)", existing) if existing else None
    con.execute("CREATE TABLE itag (item VARCHAR, name VARCHAR)")
    con.executemany("INSERT INTO itag VALUES (?,?)", [(r["item_id"].strip().lower(), r["tag_name"]) for r in item_tags]) if item_tags else None
    print(f"Input: {folder}")
    print(f"Read {len(terms)} terms, {len(assigns)} assignments to {con.execute('SELECT count(DISTINCT item) FROM asg').fetchone()[0]} "
          f"Fabric items, {len(existing):,} existing Fabric tags, {len(item_tags)} tags already on items; new tags at "
          f"{scope.lower()} scope")

    def add(code, q, args=()):
        findings.extend((code, *r) for r in con.execute(q, list(args)).fetchall())
    add("UNKNOWN_TERM", """SELECT DISTINCT 'item ' || item_name, 'term ' || guid || ' is not in terms.csv' FROM asg
        WHERE guid NOT IN (SELECT guid FROM term) ORDER BY 1, 2""")
    add("NOT_APPROVED", """SELECT DISTINCT 'term ' || t.name, 'status ' || coalesce(nullif(t.status, ''), '(none)')
            || ', yet assigned to ' || count(*) OVER (PARTITION BY t.guid)
            || CASE WHEN count(*) OVER (PARTITION BY t.guid) = 1 THEN ' item' ELSE ' items' END
        FROM term t JOIN asg a ON a.guid = t.guid WHERE lower(t.status) NOT IN ('approved', 'published') ORDER BY 1""")
    used = con.execute("""SELECT DISTINCT t.guid, t.name, t.tag FROM term t JOIN asg a ON a.guid = t.guid
        WHERE lower(t.status) IN ('approved', 'published') ORDER BY t.tag""").fetchall()
    for g, n, tg in used:
        if len(tg) > MAX_NAME:
            findings.append(("NAME_TOO_LONG", f"term {n}", f"{len(tg)} characters; add a row to tag-names.csv, "
                             f"for example \"{suggest(tg)}\""))
        elif tg[:1].isspace() or not any(ch.isalnum() for ch in tg):
            findings.append(("NAME_NOT_ALLOWED", f"term {n}", f"tag name \"{tg}\" starts with a space or holds no letter or number"))
    groups = {}
    for g, n, tg in used:
        groups.setdefault(key(tg.strip()), []).append(n)
    for k, names in sorted(groups.items()):
        if len(names) > 1:
            findings.append(("NAME_CLASH", "tag " + names[0], "terms " + ", ".join(f'"{x}"' for x in sorted(names)) +
                             " become the same tag name"))
    other = "Tenant" if scope == "Domain" else "Domain"
    ex_by = {}
    for i, n, s, d in existing:
        ex_by.setdefault(key(n.strip()), []).append((i, s, d))
    for g, n, tg in used:
        hits = ex_by.get(key(tg.strip()), [])
        if any(s == other for _, s, _ in hits):
            findings.append(("SCOPE_CLASH", f"term {n}", f"Fabric already holds a {other.lower()} tag named \"{tg}\""))

    # the plan: reuse a tag Fabric already holds in the same scope (and domain), create the rest
    reuse = {key(n.strip()) for i, n, s, d in existing if s == scope and (scope == "Tenant" or d == domain_id)}
    first = {}
    for g, n, tg in used:                 # one new tag per name, ignoring case: the first spelling wins
        first.setdefault(key(tg.strip()), tg.strip())
    new_names = sorted((v for k, v in first.items() if k not in reuse), key=str.casefold)
    con.execute("CREATE TABLE ok (guid VARCHAR, tag VARCHAR)")
    con.executemany("INSERT INTO ok VALUES (?,?)", [(g, tg.strip()) for g, n, tg in used]) if used else None
    rows = con.execute("""WITH want AS (SELECT DISTINCT a.ws, a.item, o.tag FROM asg a JOIN ok o ON o.guid = a.guid),
            names AS (SELECT item, max(item_name) AS item_name FROM asg GROUP BY item),
            have AS (SELECT item, lower(name) AS k FROM itag)
        SELECT w.ws, w.item, max(nm.item_name), string_agg(w.tag, '; ' ORDER BY lower(w.tag)),
               count(*) + (SELECT count(*) FROM have h WHERE h.item = w.item
                           AND h.k NOT IN (SELECT lower(x.tag) FROM want x WHERE x.item = w.item))
        FROM want w JOIN names nm ON nm.item = w.item GROUP BY w.ws, w.item ORDER BY 3""").fetchall()
    for ws, item, name, tags, total in rows:
        if total > MAX_PER_ITEM:
            findings.append(("OVER_ITEM_LIMIT", f"item {name}", f"{total} tags, counting the ones already on the item"))
    total_tenant = len(existing) + len(new_names)
    if total_tenant > MAX_PER_TENANT:
        findings.append(("OVER_TENANT_LIMIT", "tenant", f"{len(existing):,} existing + {len(new_names)} new = {total_tenant:,} tags"))

    print()
    print(f"Plan: {len(used)} approved or published terms become {len(groups)} tag names; {len(groups) - len(new_names)} already in Fabric, "
          f"{len(new_names)} to create; {len(rows)} items to tag")
    per = [r[4] for r in rows]
    if per:
        print(f"Tags per item after the move: fewest {min(per)}, most {max(per)} (limit {MAX_PER_ITEM})")
    print(f"Tags in the tenant after the move: {total_tenant:,} (limit {MAX_PER_TENANT:,})")
    print()
    print("Checks")
    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, where, detail in findings:
        print(f"  {code:<17} {where}: {detail}")

    out = pathlib.Path(out)
    out.mkdir(parents=True, exist_ok=True)
    suffix = f"-{tag}" if tag else ""
    safe = lambda v: "'" + str(v) if str(v)[:1] and str(v)[0] in "=+-@" else v
    with open(out / f"tag-map{suffix}.csv", "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.writer(fh)
        w.writerow(["term_guid", "term", "tag_name", "in_fabric_already", "items"])
        cnt = dict(con.execute("SELECT guid, count(DISTINCT item) FROM asg GROUP BY guid").fetchall())
        w.writerows([[safe(x) for x in (g, n, tg.strip(), "yes" if key(tg.strip()) in reuse else "no", cnt.get(g, 0))]
                     for g, n, tg in used])
    body = {"createTagsRequest": [{"displayName": n} for n in new_names],
            "scope": {"type": "Domain", "domainId": domain_id} if domain_id else {"type": "Tenant"}}
    (out / f"create-tags{suffix}.json").write_text(json.dumps(body, indent=1) + "\n")
    with open(out / f"apply-plan{suffix}.csv", "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.writer(fh)
        w.writerow(["workspace_id", "item_id", "item_name", "tags", "tags_after_move"])
        w.writerows([[safe(x) for x in r] for r in rows])
    with open(out / f"findings{suffix}.csv", "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.writer(fh)
        w.writerow(["check", "where", "detail"])
        w.writerows([[safe(x) for x in f] for f in findings])
    try:
        where = os.path.relpath(out)
    except ValueError:
        where = str(out)
    print()
    print(f"Wrote tag-map{suffix}.csv, create-tags{suffix}.json ({len(new_names)} tags), apply-plan{suffix}.csv "
          f"({len(rows)} items) and findings{suffix}.csv ({len(findings)} rows) to {where}")
    print(f"RESULT: {len(findings)} findings." + (" Fix them, and re-run, before you send create-tags.json." if findings else
                                                 " The plan fits Microsoft's tag limits."))
    return len(findings)


# ---------------------------------------------------------------------------------------------
def make_demo(folder, plant):
    rng = random.Random(265)
    gid = 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.iterdir()) and not marker.exists():
        sys.exit(f"{folder} already holds files that --make-demo did not write; choose a new folder.")
    folder.mkdir(parents=True, exist_ok=True)
    for old in list(folder.rglob("*.json")) + list(folder.rglob("*.csv")):
        old.unlink()
    marker.write_text("Written by plan_fabric_tags.py --make-demo: generated records, not an export.\n")
    names = ["Net Revenue", "Gross Margin", "Operating Expense", "Accounts Receivable", "Cost Center", "Fiscal Period",
             "Budget Variance", "Deferred Revenue", "Invoice", "Customer", "Active Customer", "Opportunity", "Win Rate",
             "Sales Territory", "Booking", "Renewal", "Supplier", "Purchase Order", "Lead Time", "Safety Stock",
             "Fill Rate", "Backorder", "Shipment", "Demand Forecast", "Reorder Point"]
    terms = [{"term_guid": gid(), "name": n, "status": "Approved"} for n in names]
    terms += [{"term_guid": gid(), "name": n, "status": "Draft"} for n in ("Churn Risk", "Price Elasticity")]
    items = [(gid(), gid(), n) for n in ("Finance lakehouse", "Sales semantic model", "Supply chain warehouse",
                                          "Revenue report", "Pipeline report", "Inventory notebook", "Customer 360 model",
                                          "Procurement dataflow", "Margin dashboard", "Forecast notebook")]
    asg = []
    for t in terms[:25]:
        for ws, it, nm in rng.sample(items, rng.randint(1, 3)):
            asg.append([t["term_guid"], ws, it, nm])
    existing = [{"id": gid(), "displayName": n, "scope": {"type": "Tenant"}} for n in ("Certified", "Finance", "Customer", "Invoice")]
    item_tags = [[items[0][1], "Certified"], [items[1][1], "Certified"], [items[3][1], "Finance"]]
    tag_names = []
    planted = []
    if plant:
        long = {"term_guid": gid(), "name": "Recognized revenue under contracts with customers (ASC 606)", "status": "Approved"}
        terms.append(long); asg.append([long["term_guid"], *items[3]]); planted.append("NAME_TOO_LONG")
        bad = {"term_guid": gid(), "name": " Net margin", "status": "Approved"}
        terms.append(bad); asg.append([bad["term_guid"], *items[8]]); planted.append("NAME_NOT_ALLOWED")
        clash = {"term_guid": gid(), "name": "customer", "status": "Approved"}
        terms.append(clash); asg.append([clash["term_guid"], *items[6]]); planted.append("NAME_CLASH")
        existing.append({"id": gid(), "displayName": "Supplier", "scope": {"type": "Domain", "domainId": gid()}}); planted.append("SCOPE_CLASH")
        asg.append([terms[25]["term_guid"], *items[1]]); planted.append("NOT_APPROVED")
        asg.append([gid(), *items[2]]); planted.append("UNKNOWN_TERM")
        busy = items[4]
        for n in names[:10]:
            g = next(t["term_guid"] for t in terms if t["name"] == n)
            if not any(a[0] == g and a[2] == busy[1] for a in asg):
                asg.append([g, *busy])
        planted.append("OVER_ITEM_LIMIT")
        existing += [{"id": gid(), "displayName": f"Legacy tag {i:04d}", "scope": {"type": "Tenant"}} for i in range(9_975)]
        planted.append("OVER_TENANT_LIMIT")
    with open(folder / "terms.csv", "w", newline="") as fh:
        w = csv.DictWriter(fh, ["term_guid", "name", "status"]); w.writeheader(); w.writerows(terms)
    with open(folder / "assignments.csv", "w", newline="") as fh:
        w = csv.writer(fh); w.writerow(["term_guid", "workspace_id", "item_id", "item_name"]); w.writerows(asg)
    with open(folder / "item-tags.csv", "w", newline="") as fh:
        w = csv.writer(fh); w.writerow(["item_id", "tag_name"]); w.writerows(item_tags)
    (folder / "existing-tags").mkdir(exist_ok=True)
    pages = [existing[i:i + 1000] for i in range(0, len(existing), 1000)] or [[]]
    for i, pg in enumerate(pages, 1):
        doc = {"value": pg}
        if i < len(pages):
            doc["continuationToken"] = f"token-{i + 1}"
        (folder / "existing-tags" / f"page-{i:03d}.json").write_text(json.dumps(doc))
    print(f"Wrote generated inputs to {folder}: {len(terms)} terms, {len(asg)} assignments to {len(items)} Fabric items, "
          f"{len(existing):,} existing tags in {len(pages)} page{'' if len(pages) == 1 else 's'} (seed 265)")
    if plant:
        assert set(planted) == {c for c, _ in CHECKS}, planted
        print("Planted one problem for each check: " + ", ".join(sorted(planted)))


def main():
    ap = argparse.ArgumentParser(description="Plan Fabric tags from Purview glossary terms inside Microsoft's limits.")
    ap.add_argument("folder", nargs="?")
    ap.add_argument("--domain-id", default="", help="create the new tags in this Fabric domain instead of the tenant")
    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="")
    ap.add_argument("--fail-on-findings", action="store_true", help="exit with status 1 when any check fails")
    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 input folder, or --make-demo FOLDER")
    print(f"Python {sys.version.split()[0]} duckdb {duckdb.__version__}")
    findings = plan(a.folder, a.domain_id.strip().lower(), a.out, a.tag)
    return 1 if a.fail_on_findings and findings else 0


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