#!/usr/bin/env python3
"""
Compare account-level parameters across the Orchard and Delphi Snowflake accounts.

Produces a report of:
  - Parameters that differ in value between accounts
  - Parameters explicitly set at the ACCOUNT level in one or both accounts

Parameters are fetched via SHOW PARAMETERS IN ACCOUNT. The level column
('ACCOUNT' vs 'SYSTEM') indicates whether a parameter was explicitly set or is
still at its Snowflake default. Parameters where both accounts share the same
value are omitted from the report.

Usage:
  pip install snowflake-connector-python
  python3 compare-snowflake-account-params.py [--csv output.csv]

Authentication:
  Uses key-pair auth if SNOWFLAKE_PRIVATE_KEY_PATH is set, otherwise falls back
  to externalbrowser SSO. Per-account overrides via ORCHARD_* / DELPHI_* prefixes.

Required env vars:
  SNOWFLAKE_USER   - your Snowflake username

Optional env vars:
  SNOWFLAKE_PRIVATE_KEY_PATH         - path to PEM private key file
  SNOWFLAKE_PRIVATE_KEY_PASSPHRASE   - passphrase if key is encrypted
  SNOWFLAKE_ROLE                     - role to use (default: SYSADMIN)
  ORCHARD_SNOWFLAKE_USER / DELPHI_SNOWFLAKE_USER   - per-account user override
  ORCHARD_SNOWFLAKE_ROLE / DELPHI_SNOWFLAKE_ROLE   - per-account role override
  ORCHARD_ACCOUNT / DELPHI_ACCOUNT                 - account identifier overrides
  ORCHARD_SNOWFLAKE_WAREHOUSE / DELPHI_SNOWFLAKE_WAREHOUSE - warehouse overrides
"""

import argparse
import csv
import os
import sys
from typing import Any

try:
    import snowflake.connector
except ImportError:
    sys.exit(
        "snowflake-connector-python is required.\n"
        "Install it with: pip install snowflake-connector-python"
    )


def _private_key_bytes() -> bytes | None:
    """Load a private key from disk if configured."""
    path = os.environ.get("SNOWFLAKE_PRIVATE_KEY_PATH")
    if not path:
        return None
    from cryptography.hazmat.backends import default_backend
    from cryptography.hazmat.primitives import serialization

    passphrase_str = os.environ.get("SNOWFLAKE_PRIVATE_KEY_PASSPHRASE")
    passphrase = passphrase_str.encode() if passphrase_str else None
    with open(path, "rb") as fh:
        private_key = serialization.load_pem_private_key(
            fh.read(), password=passphrase, backend=default_backend()
        )
    return private_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption(),
    )


def _conn_params(account_name: str, env_prefix: str, default_warehouse: str) -> dict[str, Any]:
    """Build snowflake.connector connection params for one account."""

    def env(key: str) -> str | None:
        return os.environ.get(f"{env_prefix}{key}") or os.environ.get(key)

    params: dict[str, Any] = {
        "account": account_name,
        "user": env("SNOWFLAKE_USER"),
        "role": env("SNOWFLAKE_ROLE") or "SYSADMIN",
        "warehouse": env("SNOWFLAKE_WAREHOUSE") or default_warehouse,
    }

    pk_bytes = _private_key_bytes()
    if pk_bytes:
        params["private_key"] = pk_bytes
        params["authenticator"] = "snowflake_jwt"
    else:
        params["authenticator"] = "externalbrowser"

    return params


def fetch_account_params(account_name: str, env_prefix: str, label: str, default_warehouse: str) -> dict[str, dict]:
    """
    Connect to `account_name` and return {param_key: {value, level, description}} dict.
    Fetched via SHOW PARAMETERS IN ACCOUNT.
    """
    print(f"Connecting to {label} ({account_name})…", flush=True)
    params = _conn_params(account_name, env_prefix, default_warehouse)
    conn = snowflake.connector.connect(**params)
    try:
        cur = conn.cursor(snowflake.connector.DictCursor)
        cur.execute("SHOW PARAMETERS IN ACCOUNT")
        rows = cur.fetchall()
    finally:
        conn.close()

    result: dict[str, dict] = {}
    for row in rows:
        key = row["key"]
        result[key] = {
            "value": row.get("value"),
            "level": row.get("level"),
            "description": row.get("description"),
            "type": row.get("type"),
        }

    print(f"  → {len(result):,} parameters found in {label}", flush=True)
    return result


def compare(orchard: dict[str, dict], delphi: dict[str, dict]) -> list[tuple]:
    """
    Returns a list of (param_key, orchard_value, delphi_value, orchard_level, delphi_level)
    for every parameter where the value differs between accounts.
    Sorted so explicitly-set parameters (level='ACCOUNT') appear first.
    """
    all_keys = sorted(set(orchard) | set(delphi))
    diffs = []
    for key in all_keys:
        o = orchard.get(key, {})
        d = delphi.get(key, {})
        o_val = o.get("value")
        d_val = d.get("value")
        if str(o_val) != str(d_val):
            o_level = o.get("level", "")
            d_level = d.get("level", "")
            diffs.append((key, o_val, d_val, o_level, d_level))

    # Sort: explicitly set in either account first, then alphabetical within each group
    def _sort_key(row):
        _, _, _, o_level, d_level = row
        explicitly_set = o_level == "ACCOUNT" or d_level == "ACCOUNT"
        return (0 if explicitly_set else 1, row[0])

    return sorted(diffs, key=_sort_key)


def print_report(diffs: list[tuple]):
    sep = "=" * 90

    print(f"\n{sep}")
    print("SNOWFLAKE ACCOUNT PARAMETER COMPARISON: ORCHARD vs DELPHI")
    print(sep)

    explicitly_set = [(k, ov, dv, ol, dl) for k, ov, dv, ol, dl in diffs
                      if ol == "ACCOUNT" or dl == "ACCOUNT"]
    default_differ = [(k, ov, dv, ol, dl) for k, ov, dv, ol, dl in diffs
                      if ol != "ACCOUNT" and dl != "ACCOUNT"]

    print(f"\nPARAMETERS EXPLICITLY SET IN AT LEAST ONE ACCOUNT ({len(explicitly_set)} parameter(s)):\n")
    if explicitly_set:
        hdr_key = "PARAMETER"
        hdr_o = "ORCHARD"
        hdr_d = "DELPHI"
        print(f"  {'*':1}  {hdr_key:<50} {hdr_o:<35} {hdr_d}")
        print(f"  {'-':1}  {'-'*50} {'-'*35} {'-'*35}")
        for key, o_val, d_val, o_level, d_level in explicitly_set:
            marker_o = "*" if o_level == "ACCOUNT" else " "
            marker_d = "*" if d_level == "ACCOUNT" else " "
            o_str = _truncate(f"{marker_o} {o_val}", 34)
            d_str = _truncate(f"{marker_d} {d_val}", 34)
            print(f"     {key:<50} {o_str:<35} {d_str}")
        print("\n  (* = explicitly set at ACCOUNT level; blank = Snowflake default)")
    else:
        print("  (none)")

    print(f"\nPARAMETERS AT DEFAULT LEVEL THAT DIFFER ({len(default_differ)} parameter(s)):\n")
    if default_differ:
        print(f"  {'PARAMETER':<50} {'ORCHARD':<35} {'DELPHI'}")
        print(f"  {'-'*50} {'-'*35} {'-'*35}")
        for key, o_val, d_val, _, _ in default_differ:
            o_str = _truncate(str(o_val), 34)
            d_str = _truncate(str(d_val), 34)
            print(f"  {key:<50} {o_str:<35} {d_str}")
    else:
        print("  (none)")

    print(f"\n{sep}")
    print(f"Total differing parameters: {len(diffs)}")
    print(f"{sep}\n")


def write_csv(path: str, diffs: list[tuple]):
    with open(path, "w", newline="") as fh:
        writer = csv.writer(fh)
        writer.writerow([
            "parameter", "orchard_value", "delphi_value",
            "orchard_level", "delphi_level",
        ])
        for key, o_val, d_val, o_level, d_level in diffs:
            writer.writerow([key, o_val, d_val, o_level, d_level])
    print(f"CSV written to: {path}")


def _truncate(s: str, maxlen: int) -> str:
    return s if len(s) <= maxlen else s[: maxlen - 1] + "…"


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--csv", metavar="FILE", help="also write results to CSV")
    parser.add_argument(
        "--orchard-account",
        default=os.environ.get("ORCHARD_ACCOUNT", "sme-orchard"),
        help="Orchard account identifier (default: sme-orchard)",
    )
    parser.add_argument(
        "--delphi-account",
        default=os.environ.get("DELPHI_ACCOUNT", "sme-delphi"),
        help="Delphi account identifier (default: sme-delphi)",
    )
    args = parser.parse_args()

    orchard_params = fetch_account_params(args.orchard_account, "ORCHARD_", "Orchard", "DEV_OWS_WAREHOUSE")
    delphi_params = fetch_account_params(args.delphi_account, "DELPHI_", "Delphi", "DEV_OWS_WH")

    diffs = compare(orchard_params, delphi_params)
    print_report(diffs)

    if args.csv:
        write_csv(args.csv, diffs)


if __name__ == "__main__":
    main()