"""Shared helpers for the field-governance audit scripts.

Connection is read-only by convention: every script in this directory issues
SELECT/SHOW/DESCRIBE statements only. See README.md.
"""

from __future__ import annotations

import os
import re
from pathlib import Path

DEFAULT_OBJECTS = [
    "Opportunity",
    "Account",
    "Lead",
    "Contact",
    "Order",
    "Quote",
    "OpportunityLineItem",
    "Campaign",
    "CampaignMember",
]


REQUIRED_KEYS = ("TIDB_PROD_HOST", "TIDB_PROD_USER", "TIDB_PROD_PASSWORD", "TIDB_PROD_DATABASE")


def load_env(path: Path) -> dict[str, str]:
    env: dict[str, str] = {}
    for line in path.read_text().splitlines():
        m = re.match(r"^([A-Za-z_][A-Za-z0-9_]*)=(.*)$", line.strip())
        if m:
            env[m.group(1)] = m.group(2).strip().strip('"').strip("'")
    return env


CONNECTION_KEYS = REQUIRED_KEYS + ("TIDB_PROD_PORT", "TIDB_TLS")


def resolve_settings() -> dict[str, str]:
    """Resolve TIDB_PROD_* connection settings with per-variable precedence.

    A single total order, applied key by key (no all-or-nothing tiers):
      process environment  >  env file  >  built-in defaults
    The env file is $NEXUSCRM_ENV_FILE when set (must exist), else the first
    of: repo-root .env.prod / .env.local (git-ignored), then ../nexuscrm/.env
    or ../NexusCRM/.env (local sibling-checkout convenience fallbacks) that
    defines any connection key. Process-env values always win per key, so a
    partial override (e.g. just TIDB_PROD_HOST, or TIDB_TLS=0) is honored.
    """
    file_env: dict[str, str] = {}
    override = os.environ.get("NEXUSCRM_ENV_FILE")
    if override:
        # Explicit file: no fallthrough — the operator named the source.
        p = Path(override).expanduser()
        if not p.is_file():
            raise SystemExit(f"NEXUSCRM_ENV_FILE={override} does not exist")
        file_env = load_env(p)
    else:
        # Merge across candidates per key, earlier file wins, stop once every
        # connection key is covered. A partial file (e.g. a repo-root
        # .env.local carrying only TIDB_TLS=0) contributes its keys without
        # shadowing credentials found in a later candidate.
        repo_root = Path(__file__).resolve().parents[2]
        candidates = [
            repo_root / ".env.prod",
            repo_root / ".env.local",
            repo_root.parent / "nexuscrm" / ".env",
            repo_root.parent / "NexusCRM" / ".env",
        ]
        for c in candidates:
            if not c.is_file():
                continue
            env = load_env(c)
            for k in CONNECTION_KEYS:
                if k not in file_env and env.get(k):
                    file_env[k] = env[k]
            if all(file_env.get(k) for k in CONNECTION_KEYS):
                break

    merged = {k: file_env[k] for k in CONNECTION_KEYS if file_env.get(k)}
    for k in CONNECTION_KEYS:
        if os.environ.get(k):
            merged[k] = os.environ[k]

    missing = [k for k in REQUIRED_KEYS if not merged.get(k)]
    if missing:
        raise SystemExit(
            f"Missing connection settings: {', '.join(missing)}. "
            "Export them, set NEXUSCRM_ENV_FILE, or place them in the repo-root "
            ".env.prod/.env.local or a sibling checkout's .env"
        )
    return merged


FALSY = ("0", "false", "off", "no")


def atomic_write(path: Path, text: str) -> None:
    """Write via temp file + rename so a crash can never leave a partial,
    valid-looking artifact (a truncated CSV parses; a truncated rename never
    happens)."""
    tmp = path.with_name(path.name + ".tmp")
    tmp.write_text(text)
    os.replace(tmp, path)


def existing_columns(cur, table: str) -> set[str] | None:
    """Columns of `table`, or None when the table itself is absent.

    The audited schema drifts across environments (canonical stack, prod,
    local bootstrap) and across time as governance retires fields; every
    metadata SELECT must adapt to the columns actually present instead of
    hardcoding today's schema.
    """
    import pymysql

    try:
        cur.execute(f"DESCRIBE `{table}`")
    except pymysql.err.ProgrammingError:
        return None
    return {r[0] for r in cur.fetchall()}


def concat_present(cols: set[str], *candidates: str) -> str:
    """CONCAT_WS expression over the candidate columns that exist (or '' if none)."""
    present = [c for c in candidates if c in cols]
    if not present:
        return "''"
    return "CONCAT_WS(' ', " + ", ".join(f"`{c}`" for c in present) + ")"


def connect():
    import pymysql  # lazy: only the DB-touching scripts need it

    env = resolve_settings()
    # resolve_settings already applied process-env-over-file precedence per key.
    # Accept the falsy spellings used elsewhere in this repo (0/false/off/no).
    use_tls = env.get("TIDB_TLS", "1").strip().lower() not in FALSY
    return pymysql.connect(
        host=env["TIDB_PROD_HOST"],
        port=int(env.get("TIDB_PROD_PORT", "4000")),
        user=env["TIDB_PROD_USER"],
        password=env["TIDB_PROD_PASSWORD"],
        database=env["TIDB_PROD_DATABASE"],
        ssl={"ssl": True} if use_tls else None,
        connect_timeout=10,
        read_timeout=180,
    )


# Object names are interpolated into backtick-quoted identifiers and file names;
# restrict to the shapes a real SObject API name can take.
OBJECT_NAME_RE = re.compile(r"^[A-Za-z][A-Za-z0-9_]*$")


def parse_args(argv: list[str], default_out: str = "audit_out") -> tuple[list[str], Path, Path | None]:
    """Tiny arg parser shared by the scripts: [OBJECT ...] [--out DIR] [--baseline DIR].

    Returns (objects, out_dir, baseline_dir). Objects default to DEFAULT_OBJECTS.
    """
    objects: list[str] = []
    out = Path(default_out)
    baseline: Path | None = None
    def flag_value(flag: str, it) -> str:
        v = next(it, None)
        if v is None or v.startswith("--"):
            raise SystemExit(f"{flag} requires a value")
        return v

    it = iter(argv)
    for a in it:
        if a == "--out":
            out = Path(flag_value(a, it))
        elif a == "--baseline":
            baseline = Path(flag_value(a, it))
        elif a.startswith("--"):
            raise SystemExit(f"unknown flag {a}")
        elif not OBJECT_NAME_RE.fullmatch(a):
            raise SystemExit(f"invalid object name: {a!r}")
        else:
            objects.append(a)
    return objects or list(DEFAULT_OBJECTS), out, baseline