"""Classify all fields (on- and off-layout) into governance buckets.

Inputs: <out>/<Object>.json produced by probe_full_audit.py.
Adds a dependency scan over active VRs, active flows, reports, list views,
approvals, convert maps, field defaults and field formulas (read-only queries).

Buckets (comparable with the 2026-07-06 / 2026-08-05 audits):
  A-empty-both        never filled in either cohort
  B2-bool-never-true  boolean never true (cannot distinguish unused from all-false)
  B-legacy-only       legacy data exists, native fill is zero
  D-low-fill          both cohorts < 5%
  C-auto              formula / rollup / autonumber / not writable
  E-no-data           no physical column (virtual formula / rollup)
  U-indeterminate-no-native  object has no native rows yet, fill evidence is vacuous
  F-active            everything else

Caveats: flow dependency matches are by field NAME across all objects, and
sources owned by another object are matched on the dotted form (".<field>")
and tagged "kind~:name" — such a hit means "may read a same-named field via
some relationship or $-global path", not proof it targets this object. All
matches are leads; verify manually before deleting anything.

Usage: classify_full.py [OBJECT ...] [--out DIR]
"""

from __future__ import annotations

import csv
import io
import json
import re
import sys

from common import atomic_write, concat_present, connect, existing_columns, parse_args


def fetch_dependency_sources(conn):
    """Return list of (kind, scope_object, name, text_blob).

    Every source adapts to the columns actually present (schema drifts across
    environments and time); an entirely absent table skips that source kind
    with a note instead of failing the run.
    """
    src = []
    with conn.cursor() as cur:
        def cols_or_skip(kind: str, table: str):
            c = existing_columns(cur, table)
            if c is None:
                print(f"dependency source {kind}: table {table} absent, skipped")
            return c

        c = cols_or_skip("VR", "_System_ValidationRule")
        if c:
            blob = concat_present(c, "Condition", "ErrorDisplayField")
            cur.execute(f"SELECT ObjectApiName, Name, {blob} FROM `_System_ValidationRule` WHERE Active=1")
            for o, n, t in cur.fetchall():
                src.append(("VR", o, n, t or ""))

        if cols_or_skip("Flow", "Flow") and cols_or_skip("Flow", "FlowDefinition"):
            cur.execute(
                "SELECT fd.TriggerObjectOrEvent, fd.DeveloperName, f.Metadata "
                "FROM `Flow` f JOIN `FlowDefinition` fd ON f.DefinitionId=fd.Id "
                "WHERE f.Status='Active' AND (f.IsDeleted=0 OR f.IsDeleted IS NULL) AND (fd.IsDeleted=0 OR fd.IsDeleted IS NULL)"
            )
            for o, n, t in cur.fetchall():
                src.append(("Flow", o, n, str(t or "")))

        c = cols_or_skip("Report", "Report")
        if c:
            blob = concat_present(c, "Columns", "Filters", "Grouping", "Aggregates", "BooleanFilter")
            cur.execute(f"SELECT SourceObject, Name, {blob} FROM `Report` WHERE (IsDeleted=0 OR IsDeleted IS NULL)")
            for o, n, t in cur.fetchall():
                src.append(("Report", o, n, str(t or "")))

        c = cols_or_skip("ListView", "_System_ListView")
        if c:
            blob = concat_present(c, "FilterExpr", "Fields", "SortField")
            cur.execute(f"SELECT ObjectApiName, Label, {blob} FROM `_System_ListView`")
            for o, n, t in cur.fetchall():
                src.append(("ListView", o, n, str(t or "")))

        c = cols_or_skip("Approval", "ProcessDefinition")
        if c:
            blob = concat_present(c, "EntryFilter", "Metadata")
            cur.execute(f"SELECT TableEnumOrId, DeveloperName, {blob} FROM `ProcessDefinition` WHERE IsActive=1")
            for o, n, t in cur.fetchall():
                src.append(("Approval", o, n, str(t or "")))

        if cols_or_skip("ApprovalAction", "ProcessAction"):
            # Actions of INACTIVE approval definitions are not live dependencies.
            cur.execute(
                "SELECT pd.TableEnumOrId, pa.Name, pa.FieldApiName FROM `ProcessAction` pa "
                "JOIN `ProcessDefinition` pd ON pa.ProcessDefinitionId=pd.Id "
                "WHERE (pa.IsDeleted=0 OR pa.IsDeleted IS NULL) AND pd.IsActive=1"
            )
            for o, n, t in cur.fetchall():
                src.append(("ApprovalAction", o, n, str(t or "")))

        c = cols_or_skip("ApprovalNode", "ProcessNode")
        if c and "ApproverRelatedField" in c:
            # Approval nodes can route via a field on the record
            # (ApproverRelatedField); a field referenced only here is a live
            # runtime dependency of the approval chain.
            cur.execute(
                "SELECT pd.TableEnumOrId, pd.DeveloperName, pn.ApproverRelatedField "
                "FROM `ProcessNode` pn "
                "JOIN `ProcessDefinition` pd ON pn.ProcessDefinitionId=pd.Id "
                "WHERE (pn.IsDeleted=0 OR pn.IsDeleted IS NULL) AND pd.IsActive=1 "
                "AND pn.ApproverRelatedField IS NOT NULL AND pn.ApproverRelatedField<>''"
            )
            for o, n, t in cur.fetchall():
                src.append(("ApprovalNode", o, n, str(t or "")))

        c = cols_or_skip("ConvertMap", "_System_ObjectFieldMapping")
        if c:
            # A mapping references fields on BOTH sides: emit one entry scoped
            # to the source object (source-side fields) and one scoped to the
            # target object (target-side fields), so auditing either object
            # surfaces the dependency.
            blob_src = concat_present(c, "SourceField", "TransformExpression")
            blob_tgt = concat_present(c, "TargetField")
            cur.execute(
                f"SELECT SourceObject, TargetObject, CONCAT(SourceObject,'->',TargetObject), {blob_src}, {blob_tgt} "
                "FROM `_System_ObjectFieldMapping` WHERE IsActive=1 AND (IsDeleted=0 OR IsDeleted IS NULL)"
            )
            for so, to, n, t_src, t_tgt in cur.fetchall():
                src.append(("ConvertMap", so, n, str(t_src or "")))
                src.append(("ConvertMap", to, n, str(t_tgt or "")))

        c = cols_or_skip("FieldDefault", "_System_ActionFieldDefault")
        if c:
            blob = concat_present(c, "FieldAPIName", "ValueFormula", "Condition")
            cur.execute(
                f"SELECT TargetObject, FieldAPIName, {blob} "
                "FROM `_System_ActionFieldDefault` WHERE IsActive=1 AND (IsDeleted=0 OR IsDeleted IS NULL)"
            )
            for o, n, t in cur.fetchall():
                src.append(("FieldDefault", o, n, str(t or "")))

        c = cols_or_skip("FieldDef", "_System_Field")
        if c:
            blob = concat_present(c, "Formula", "RollupConfig", "ControllingField", "PicklistDependency", "DefaultValue")
            conds = [
                cond
                for col, cond in (
                    ("Formula", "f.Formula IS NOT NULL AND f.Formula<>''"),
                    ("RollupConfig", "f.RollupConfig IS NOT NULL"),
                    ("ControllingField", "f.ControllingField IS NOT NULL AND f.ControllingField<>''"),
                    ("DefaultValue", "f.DefaultValue IS NOT NULL AND f.DefaultValue<>''"),
                )
                if col in c
            ]
            if conds:
                cur.execute(
                    f"SELECT o.ApiName, f.ApiName, {blob} "
                    "FROM `_System_Field` f JOIN `_System_Object` o ON f.ObjectId=o.Id "
                    f"WHERE (f.IsDeleted=0 OR f.IsDeleted IS NULL) AND ({' OR '.join(conds)})"
                )
                for o, n, t in cur.fetchall():
                    src.append(("FieldDef", o, f"{o}.{n}", str(t or "")))
    return src


def main() -> None:
    objects, out_dir, _ = parse_args(sys.argv[1:])
    manifest_file = out_dir / "manifest.json"
    if not manifest_file.exists():
        print(f"ERROR: {manifest_file} not found — run probe_full_audit.py first (or the probe crashed mid-run)")
        raise SystemExit(2)
    probed = set(json.loads(manifest_file.read_text()).get("probed", []))

    # Tombstone the CSVs this run is about to (re)write BEFORE any step that
    # can fail (connection, dependency fetch): every failure mode must leave
    # missing files (hard downstream failure), never a mix of fresh manifest
    # and stale classifications.
    for obj in objects:
        (out_dir / f"{obj}_all_fields.csv").unlink(missing_ok=True)

    conn = connect()
    try:
        sources = fetch_dependency_sources(conn)
    finally:
        conn.close()
    print(f"dependency sources: {len(sources)}")

    for obj in objects:
        probe_file = out_dir / f"{obj}.json"
        if obj not in probed:
            print(f"{obj}: not in this run's probe manifest, skipped")
            continue
        if not probe_file.exists():
            print(f"{obj}: no probe output at {probe_file}, skipped (run probe_full_audit.py first)")
            continue
        data = json.loads(probe_file.read_text())
        fields = data["fields"]
        n_mig = data["rows"]["migrated"]
        n_nat = data["rows"]["native"]

        rows = []
        for api, f in fields.items():
            fm = f.get("filled_migrated")
            fn = f.get("filled_native")
            is_bool = f.get("bool", False)
            auto = (
                f["is_formula"]
                or f["is_rollup"]
                or f["type"] in ("AutoNumber", "auto_number", "Rollup", "Formula")
                or (not f["createable"] and not f["updateable"])
            )
            pat = re.compile(r"(?<![A-Za-z0-9_])" + re.escape(api) + r"(?![A-Za-z0-9_])")
            # Cross-object references travel through relationship paths, so for
            # sources owned by ANOTHER object we match the dotted form
            # (".<field>") and tag the hit with "~" — a cross-object lead that
            # needs manual confirmation, but one that must not silently vanish
            # (e.g. a Quote formula reading Opportunity.Account.<field> is a
            # real dependency of the Account field).
            dotted = re.compile(r"\." + re.escape(api) + r"(?![A-Za-z0-9_])")
            deps = []
            for kind, scope, name, text in sources:
                if kind == "FieldDef" and name == f"{obj}.{api}":
                    continue
                own_scope = not scope or scope in (obj, "")
                if own_scope or kind == "Flow":
                    if pat.search(text):
                        deps.append(f"{kind}:{name}")
                elif dotted.search(text):
                    deps.append(f"{kind}~:{name}")
            # Own-scope entries (proof-grade) rank before cross-object "~"
            # leads, so a flood of leads can never evict hard references from
            # the capped list.
            deps_s = "; ".join(sorted(set(deps), key=lambda d: ("~:" in d, d))[:6])

            if fm is None:
                bucket = "E-no-data"
            elif auto:
                bucket = "C-auto"
            elif n_nat == 0:
                # Without a native cohort every fill-based verdict (dead, legacy,
                # low, active) lacks its key evidence axis — classify all
                # measurable fields as indeterminate rather than dead.
                bucket = "U-indeterminate-no-native"
            elif is_bool and fm == 0 and fn == 0:
                bucket = "B2-bool-never-true"
            elif fm == 0 and fn == 0:
                bucket = "A-empty-both"
            elif fn == 0 and fm > 0:
                bucket = "B-legacy-only"
            elif fn / n_nat < 0.05 and fm / max(n_mig, 1) < 0.05:
                bucket = "D-low-fill"
            else:
                bucket = "F-active"

            rows.append(
                {
                    "field": api,
                    "label": f["label"],
                    "type": f["type"],
                    "on_layout": f.get("on_layout", False),
                    "bucket": bucket,
                    "mig_pct": round(100 * fm / max(n_mig, 1), 1) if fm is not None else "",
                    "nat_pct": round(100 * fn / max(n_nat, 1), 1) if fn is not None else "",
                    "required": f["required"],
                    "createable": f["createable"],
                    "updateable": f["updateable"],
                    "formula": f["is_formula"],
                    "rollup": f["is_rollup"],
                    "default": f.get("default", ""),
                    "owner_pack": f.get("owner_pack") or "",
                    "deps": deps_s,
                }
            )

        if not rows:
            print(f"{obj}: probe output has zero fields, skipped")
            continue
        rows.sort(key=lambda r: (not r["on_layout"], r["bucket"], -(float(r["mig_pct"] or 0))))
        out_csv = out_dir / f"{obj}_all_fields.csv"
        buf = io.StringIO()
        w = csv.DictWriter(buf, fieldnames=list(rows[0].keys()))
        w.writeheader()
        w.writerows(rows)
        atomic_write(out_csv, buf.getvalue())

        counts_on: dict[str, int] = {}
        counts_off: dict[str, int] = {}
        for r in rows:
            d = counts_on if r["on_layout"] else counts_off
            d[r["bucket"]] = d.get(r["bucket"], 0) + 1
        print(f"{obj}: on_layout={json.dumps(counts_on)} off_layout={json.dumps(counts_off)}")


if __name__ == "__main__":
    main()