#!/usr/bin/env python3
"""
Standalone Organization provisioning script -- DarkWebID V2 External API.

Reads a single .xlsx or .csv file where every row represents one Organization with its
per-organization configuration.  Provisions each row in sequence across 7 API steps
with fail-fast semantics: any step failure marks all remaining steps for that
Organization as SKIPPED and moves on to the next row.  Writes a timestamped results
file after every row so partial results survive an unexpected exit.

Usage
-----
python provision_organizations.py \\
    --url      https://DARKWEBID_URL/ \\
    --username {darkweb_api_user_email_address} \\
    --password {darkweb_api_user_password} \\
    --file     provisioning_data.xlsx \\
    [--output  results.xlsx] \\
    [--dry-run]
"""

from __future__ import annotations

import argparse
import base64
import csv
import json
import re
import sys
import time
from datetime import datetime
from pathlib import Path
from typing import Any

import openpyxl
import requests
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter


# ---------------------------------------------------------------------------
# Enum mapping tables  (Excel display value -> API integer)
# ---------------------------------------------------------------------------

INDUSTRY_MAP: dict[str, int] = {
    "aerospace & defense": 0,
    "business & professional services": 1,
    "construction & engineering": 2,
    "education & research": 3,
    "energy & transportation": 4,
    "federal government": 5,
    "finance & insurance": 6,
    "high-tech & it": 7,
    "hospitality": 8,
    "legal": 9,
    "manufacturing": 10,
    "media & entertainment": 11,
    "medical & healthcare": 12,
    "non-profit organization": 13,
    "pharmaceutical": 14,
    "retail & ecommerce": 15,
    "service provider": 16,
    "state/local government": 17,
    "systems integrator": 18,
    "wireless industry": 19,
    "other": 20,
}

EMPLOYEE_COUNT_MAP: dict[str, int] = {
    "1-10": 0,
    "11-50": 1,
    "51-100": 2,
    "101-250": 3,
    "251+": 4,
}

NOTIFICATION_PREF_MAP: dict[str, int] = {
    "never": 0,
    "monthly": 1,
    "daily and monthly": 2,
}

DATE_FORMAT_MAP: dict[str, int] = {
    "m/d/y": 0,
    "d-m-y": 1,
}

USER_ROLE_MAP: dict[str, int] = {
    "standard": 0,
    "privileged": 1,
}


# ---------------------------------------------------------------------------
# Column definitions for the results spreadsheet
# ---------------------------------------------------------------------------

STEP_LABELS: list[str] = [
    "Step 1 Create Organization",
    "Step 2 Notifications",
    "Step 3 Reporting Prefs",
    "Step 4 Domain",
    "Step 5 Create User",
    "Step 6 User Prefs",
    "Step 7 Automated Report",
]

RESULT_COLS: list[str] = (
    ["#", "Organization Title", "Organization ID"] + STEP_LABELS[:5] + ["User ID"] + STEP_LABELS[5:] + ["Overall Status", "Errors"]
)

# Step cell values
_OK = "OK"
_FAIL = "FAIL"
_SKIPPED = "SKIPPED"
_NA = "N/A"
_NOT_RUN = "NOT RUN"

# Retry settings for steps that depend on async propagation (Steps 4 and 6).
_MAX_ATTEMPTS = 5  # up to 4 retries per step
_RETRY_DELAY = 5  # seconds between retries — total wait budget: up to 20 s


# ---------------------------------------------------------------------------
# Display helpers
# ---------------------------------------------------------------------------


def _fmt_dur(seconds: float) -> str:
    """Format a duration in seconds as M:SS or H:MM:SS."""
    s = int(seconds)
    h, rem = divmod(s, 3600)
    m, s = divmod(rem, 60)
    return f"{h}:{m:02d}:{s:02d}" if h else f"{m}:{s:02d}"


def _now() -> str:
    """Return the current wall-clock time as HH:MM:SS for log timestamps."""
    return datetime.now().strftime("%H:%M:%S")


def _fmt_payload(payload: dict[str, Any]) -> str:
    """Return payload as compact single-line JSON, truncated to 150 chars."""
    s = json.dumps(payload, separators=(",", ":"))
    return s[:147] + "..." if len(s) > 150 else s


class _Tee:
    """
    Mirror all writes to both the original stdout and a log file.

    Assign to sys.stdout before any provisioning output so that every
    print() call lands in both places.  The file is opened in line-buffered
    mode so a mid-run crash leaves a fully-readable log.
    """

    def __init__(self, log_path: Path) -> None:
        self._stdout = sys.stdout
        self._file = open(log_path, "w", encoding="utf-8", buffering=1)  # line-buffered

    def write(self, data: str) -> int:
        self._stdout.write(data)
        self._file.write(data)
        return len(data)

    def flush(self) -> None:
        self._stdout.flush()
        self._file.flush()

    def close(self) -> None:
        """Flush, close the log file, and restore sys.stdout."""
        self._file.flush()
        self._file.close()
        sys.stdout = self._stdout

    # Delegate attribute lookups the rest of the code might use
    def fileno(self) -> int:
        return self._stdout.fileno()

    def isatty(self) -> bool:
        return self._stdout.isatty()


def _print_run_header(
    input_path: Path,
    output_path: Path,
    log_path: Path,
    base_url: str,
    total: int,
) -> None:
    """Print a bordered run-start banner."""
    width = 78
    sep = "=" * width
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(sep)
    print("  DarkWebID Organization Provisioning Tool")
    print(f"  File    : {input_path}")
    print(f"  Target  : {base_url}")
    print(f"  Organizations    : {total} to provision")
    print(f"  Output  : {output_path}")
    print(f"  Log     : {log_path}")
    print(f"  Started : {now}")
    print(sep)


def _print_run_footer(
    output_path: Path,
    log_path: Path,
    succeeded: int,
    failed: int,
    total: int,
    run_start: float,
) -> None:
    """Print a bordered final summary."""
    width = 78
    sep = "=" * width
    elapsed = time.perf_counter() - run_start
    print(sep)
    if failed == 0:
        print("  PROVISIONING COMPLETE -- all succeeded")
    else:
        print(f"  PROVISIONING COMPLETE -- {failed} row(s) failed")
    print(f"  Succeeded : {succeeded} / {total}")
    print(f"  Failed    : {failed} / {total}")
    print(f"  Elapsed   : {_fmt_dur(elapsed)}")
    print(f"  Results   : {output_path}")
    print(f"  Log       : {log_path}")
    print(sep)


def _print_progress(done: int, total: int, run_start: float) -> None:
    """Print an ASCII progress bar with elapsed time and ETA."""
    elapsed = time.perf_counter() - run_start
    if done > 0:
        eta_str = f"~{_fmt_dur(elapsed / done * (total - done))}"
    else:
        eta_str = "calculating"
    pct = done / total
    bar_w = 22
    filled = int(bar_w * pct)
    has_arrow = filled < bar_w
    bar = "=" * filled + (">" if has_arrow else "") + " " * max(0, bar_w - filled - (1 if has_arrow else 0))
    n_w = len(str(total))
    print(f"  [{bar}]  {done:>{n_w}}/{total}  ({pct:>4.0%})  elapsed {_fmt_dur(elapsed)}  ETA {eta_str}")


# ---------------------------------------------------------------------------
# Result record factory
# ---------------------------------------------------------------------------


def _make_result(row_num: int, org_title: str) -> dict[str, Any]:
    """Return a blank result dict for one provisioning row."""
    result: dict[str, Any] = {
        "#": row_num,
        "Organization Title": org_title,
        "Organization ID": "",
        "User ID": "",
        "Overall Status": "",
        "Errors": [],  # list[str]; joined to str when writing the spreadsheet
    }
    for label in STEP_LABELS:
        result[label] = ""
    return result


# ---------------------------------------------------------------------------
# File readers
# ---------------------------------------------------------------------------


def _normalize_key(key: str) -> str:
    """Lowercase + strip whitespace for case-insensitive header matching."""
    return str(key).lower().strip()


def _cell_str(value: Any) -> str:
    """Convert an openpyxl cell value to a stripped string."""
    if value is None:
        return ""
    # openpyxl returns int/float for numeric cells; avoid "1.0" for integers.
    if isinstance(value, float) and value == int(value):
        return str(int(value))
    return str(value).strip()


def _read_xlsx(path: Path):
    """Stream an .xlsx file row by row; yields one dict per data row."""
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    headers: list[str] = []
    for i, row in enumerate(ws.iter_rows(values_only=True)):
        if i == 0:
            headers = [_normalize_key(h) if h is not None else "" for h in row]
            continue
        cells = [_cell_str(c) for c in row]
        if all(c == "" for c in cells):
            continue  # skip blank rows
        yield dict(zip(headers, cells))
    wb.close()


def _read_csv(path: Path):
    """Stream a .csv file row by row (utf-8-sig handles BOM); yields one dict per data row."""
    with open(path, encoding="utf-8-sig", newline="") as fh:
        reader = csv.DictReader(fh)
        for raw_row in reader:
            normalized = {_normalize_key(k): v.strip() for k, v in raw_row.items()}
            if all(v == "" for v in normalized.values()):
                continue
            yield normalized


def _read_file(path: Path):
    """Auto-detect extension (.xlsx / .csv) and stream rows one at a time."""
    suffix = path.suffix.lower()
    if suffix == ".xlsx":
        yield from _read_xlsx(path)
    elif suffix == ".csv":
        yield from _read_csv(path)
    else:
        print(f"[ERROR] Unsupported file extension '{suffix}'. Use .xlsx or .csv.")
        sys.exit(1)


# ---------------------------------------------------------------------------
# Row value helpers
# ---------------------------------------------------------------------------


def _get(row: dict[str, str], col: str, default: str = "") -> str:
    """Fetch a cell using a case-insensitive column name."""
    return row.get(_normalize_key(col), default).strip()


def _yesno(value: str) -> bool:
    """Return True only when value is 'yes' (case-insensitive)."""
    return value.strip().lower() == "yes"


def _split_emails(value: str) -> list[str]:
    """Split a comma-separated email string into a cleaned list."""
    return [e.strip() for e in value.split(",") if e.strip()] if value else []


def _b64encode_body(text: str) -> str:
    """Encode plain text as strict Base64 (no line-wrapping)."""
    return base64.b64encode(text.encode("utf-8")).decode("ascii")


# ASCII-only local part, domain with at least one dot, TLD between 2-62 chars.
_EMAIL_RE = re.compile(r"^[a-zA-Z0-9.!#$%&'*+/=?^_~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z][a-zA-Z0-9-]{1,61}$")


def _valid_email(address: str) -> bool:
    """Return True when *address* matches the backend email validation pattern."""
    return bool(_EMAIL_RE.match(address.strip()))


# Diagnostic headers returned on non-2xx responses; include them when reporting errors.
_TRACE_HEADERS = ("x-request-id", "x-amz-cf-id", "x-correlation-id")


def _fmt_diagnostic_headers(headers: dict[str, str]) -> str:
    """Return a compact string of trace headers useful for server-side investigation.

    Looks up each header case-insensitively (keys are already lowercased by _api_call).
    Returns an empty string when none of the trace headers are present.
    """
    parts = [f"{k}={headers[k]}" for k in _TRACE_HEADERS if k in headers]
    return "  ".join(parts)


# ---------------------------------------------------------------------------
# Enum mapping helpers  (raise ValueError with a descriptive message)
# ---------------------------------------------------------------------------


def _map_industry(value: str) -> int:
    key = value.lower().strip()
    if key not in INDUSTRY_MAP:
        valid = ", ".join(f'"{k}"' for k in INDUSTRY_MAP)
        raise ValueError(f"Unknown Industry '{value}'. Valid values: {valid}")
    return INDUSTRY_MAP[key]


def _map_employee_count(value: str) -> int:
    key = value.strip()
    if key not in EMPLOYEE_COUNT_MAP:
        valid = ", ".join(f'"{k}"' for k in EMPLOYEE_COUNT_MAP)
        raise ValueError(f"Unknown Employee Count '{value}'. Valid values: {valid}")
    return EMPLOYEE_COUNT_MAP[key]


def _map_organization_notification_pref(value: str) -> int:
    key = value.lower().strip()
    if key == "":
        return 0  # default: Never
    if key not in NOTIFICATION_PREF_MAP:
        valid = ", ".join(f'"{k}"' for k in NOTIFICATION_PREF_MAP)
        raise ValueError(f"Unknown Notification Preference '{value}'. Valid values: {valid}")
    return NOTIFICATION_PREF_MAP[key]

def _map_user_notification_pref(value: str) -> int:
    key = value.lower().strip()
    if key == "":
        return 2  # default: Daily and Monthly
    if key not in NOTIFICATION_PREF_MAP:
        valid = ", ".join(f'"{k}"' for k in NOTIFICATION_PREF_MAP)
        raise ValueError(f"Unknown Notification Preference '{value}'. Valid values: {valid}")
    return NOTIFICATION_PREF_MAP[key]


def _map_date_format(value: str) -> int:
    key = value.lower().strip()
    if key == "":
        return 0  # default: M/D/Y
    if key not in DATE_FORMAT_MAP:
        raise ValueError(f'Unknown Date Format \'{value}\'. Valid values: "M/D/Y", "D-M-Y"')
    return DATE_FORMAT_MAP[key]


def _map_user_role(value: str) -> int:
    key = value.lower().strip()
    if key == "":
        return 0  # default: standard
    if key not in USER_ROLE_MAP:
        valid = ", ".join(f'"{k}"' for k in USER_ROLE_MAP)
        raise ValueError(f"Unknown User Account Type '{value}'. Valid values: {valid}")
    return USER_ROLE_MAP[key]


# ---------------------------------------------------------------------------
# Row validation  (collects ALL errors; never raises)
# ---------------------------------------------------------------------------


def _validate_row(row: dict[str, str], idx: int) -> list[str]:
    """
    Validate a single input row.

    Returns a list of human-readable error strings.  Every problem is
    reported -- the user sees the complete picture in one run.
    Never raises.
    """
    errors: list[str] = []

    def require(col: str, label: str) -> str:
        val = _get(row, col)
        if not val:
            errors.append(f"Row {idx}: '{label}' is required but missing or empty.")
        return val

    # Required fields
    require("organization title", "Organization Title")
    industry_raw = require("industry", "Industry")
    emp_count_raw = require("employee count", "Employee Count")
    require("domain", "Domain")
    user_email_raw = require("user email", "User Email")
    if user_email_raw and not _valid_email(user_email_raw):
        errors.append(f"Row {idx}: 'User Email' is not a valid email address: '{user_email_raw}'.")
    require("user first name", "User First Name")
    require("user last name", "User Last Name")

    # Industry must map to a known enum value
    if industry_raw:
        try:
            _map_industry(industry_raw)
        except ValueError as exc:
            errors.append(f"Row {idx}: {exc}")

    # Employee Count must map to a known enum value
    if emp_count_raw:
        try:
            _map_employee_count(emp_count_raw)
        except ValueError as exc:
            errors.append(f"Row {idx}: {exc}")

    # Notification preference: conditional requirements
    notif_pref_raw = _get(row, "notification preference")
    notif_pref_int = 0
    if notif_pref_raw:
        try:
            notif_pref_int = _map_organization_notification_pref(notif_pref_raw)
        except ValueError as exc:
            errors.append(f"Row {idx}: {exc}")

    if notif_pref_int > 0:
        if not _get(row, "notification date format"):
            errors.append(
                f"Row {idx}: 'Notification Date Format' is required when "
                f"Notification Preference is '{notif_pref_raw}'."
            )
        notif_emails_raw = _get(row, "notification emails")
        if not notif_emails_raw:
            errors.append(
                f"Row {idx}: 'Notification Emails' is required when " f"Notification Preference is '{notif_pref_raw}'."
            )
        else:
            for addr in _split_emails(notif_emails_raw):
                if not _valid_email(addr):
                    errors.append(f"Row {idx}: 'Notification Emails' contains an invalid address: '{addr}'.")

    # Clean Bill of Health: conditional requirements
    cboh = _yesno(_get(row, "clean bill of health"))
    if cboh:
        cboh_emails_raw = _get(row, "cboh emails")
        if not cboh_emails_raw:
            errors.append(f"Row {idx}: 'CBoH Emails' is required when 'Clean Bill of Health' is 'Yes'.")
        else:
            for addr in _split_emails(cboh_emails_raw):
                if not _valid_email(addr):
                    errors.append(f"Row {idx}: 'CBoH Emails' contains an invalid address: '{addr}'.")
        cboh_reply_to = _get(row, "cboh reply-to")
        if cboh_reply_to and not _valid_email(cboh_reply_to):
            errors.append(f"Row {idx}: 'CBoH Reply-To' is not a valid email address: '{cboh_reply_to}'.")

    # Reporting prefs: executive summary required if any sub-section is enabled
    sub_prefs = [
        "report benchmark averages",
        "report monitoring",
        "report organizational compromises",
        "report breaches",
    ]
    if any(_yesno(_get(row, p)) for p in sub_prefs):
        if not _yesno(_get(row, "report executive summary")):
            errors.append(
                f"Row {idx}: 'Report Executive Summary' must be 'Yes' when any "
                f"other Report preference (Benchmark Averages, Monitoring, "
                f"Organizational Compromises, or Breaches) is enabled."
            )

    # Automated report: conditional requirements
    if _yesno(_get(row, "automated report")):
        ar_recipients_raw = _get(row, "automated report recipients")
        if not ar_recipients_raw:
            errors.append(f"Row {idx}: 'Automated Report Recipients' is required when 'Automated Report' is 'Yes'.")
        else:
            for addr in _split_emails(ar_recipients_raw):
                if not _valid_email(addr):
                    errors.append(f"Row {idx}: 'Automated Report Recipients' contains an invalid address: '{addr}'.")
        if not _get(row, "automated report subject"):
            errors.append(f"Row {idx}: 'Automated Report Subject' is required when 'Automated Report' is 'Yes'.")
        monthly_ok = _yesno(_get(row, "monthly business report"))
        quarterly_ok = _yesno(_get(row, "quarterly business report"))
        if not monthly_ok and not quarterly_ok:
            errors.append(
                f"Row {idx}: At least one of 'Monthly Business Report' or "
                f"'Quarterly Business Report' must be 'Yes' when 'Automated Report' is 'Yes'."
            )

    return errors


# ---------------------------------------------------------------------------
# API payload builders
# ---------------------------------------------------------------------------


def _build_org_payload(row: dict[str, str]) -> dict[str, Any]:
    return {
        "title": _get(row, "organization title"),
        "industry": _map_industry(_get(row, "industry")),
        "employee_count": _map_employee_count(_get(row, "employee count")),
    }


def _build_notification_payload(row: dict[str, str]) -> dict[str, Any]:
    notif_pref = _map_organization_notification_pref(_get(row, "notification preference"))
    cboh = _yesno(_get(row, "clean bill of health"))
    needs_date_fmt = notif_pref > 0 or cboh

    payload: dict[str, Any] = {
        "notification_preference": notif_pref,
        "clean_bill_email": cboh,
    }
    if needs_date_fmt:
        payload["notification_date_format"] = _map_date_format(_get(row, "notification date format"))
    if notif_pref > 0:
        payload["notification_email_addresses"] = _split_emails(_get(row, "notification emails"))
    if cboh:
        payload["clean_bill_email_addresses"] = _split_emails(_get(row, "cboh emails"))
        reply_to = _get(row, "cboh reply-to")
        if reply_to:
            payload["clean_bill_email_reply_to"] = reply_to
    return payload


def _build_reporting_prefs_payload(row: dict[str, str]) -> dict[str, Any]:
    return {
        "date_format": _map_date_format(_get(row, "report date format")),
        "dw_executive_summary": _yesno(_get(row, "report executive summary")),
        "dw_benchmark_averages": _yesno(_get(row, "report benchmark averages")),
        "dw_monitoring": _yesno(_get(row, "report monitoring")),
        "dw_organizational_compromises": _yesno(_get(row, "report organizational compromises")),
        "dw_breaches": _yesno(_get(row, "report breaches")),
        "darkweb_benefits": _yesno(_get(row, "report dark web benefits")),
    }


def _build_user_payload(row: dict[str, str], org_id: str) -> dict[str, Any]:
    return {
        "organization_id": int(org_id),
        "first_name": _get(row, "user first name"),
        "last_name": _get(row, "user last name"),
        "email": _get(row, "user email"),
        "roles": [_map_user_role(_get(row, "user account type"))],
    }


def _build_user_prefs_payload(row: dict[str, str]) -> dict[str, Any]:
    return {
        "notification_preference": _map_user_notification_pref(_get(row, "user notification preference")),
        "date_format": _map_date_format(_get(row, "user date format")),
    }


def _build_report_config_payload(row: dict[str, str]) -> dict[str, Any]:
    monthly = _yesno(_get(row, "monthly business report"))
    quarterly = _yesno(_get(row, "quarterly business report"))

    body_text = _get(row, "automated report body")
    body_b64 = _b64encode_body(body_text) if body_text else _b64encode_body(" ")

    return {
        "recipients": _split_emails(_get(row, "automated report recipients")),
        "subject": _get(row, "automated report subject"),
        "body": body_b64,
        "monthly": monthly,
        "quarterly": quarterly,
    }


# ---------------------------------------------------------------------------
# API helper
# ---------------------------------------------------------------------------


def _api_call(
    session: requests.Session,
    method: str,
    url: str,
    **kwargs: Any,
) -> tuple[bool, int, dict[str, Any], float, dict[str, str]]:
    """
    Execute one API request.

    Returns (success, http_status_code, response_body, elapsed_seconds, response_headers).
    Header keys are lowercased for case-insensitive lookup via _fmt_diagnostic_headers.
    Never raises -- network exceptions are caught and returned as
    (False, 0, {"_error": "..."}, elapsed, {}).
    """
    t0 = time.perf_counter()
    try:
        resp = session.request(method, url, timeout=30, **kwargs)
        elapsed = time.perf_counter() - t0
        headers: dict[str, str] = {k.lower(): v for k, v in resp.headers.items()}
        try:
            body: dict[str, Any] = resp.json()
        except Exception:
            body = {"_raw": resp.text[:500]}
        return resp.ok, resp.status_code, body, elapsed, headers
    except requests.RequestException as exc:
        elapsed = time.perf_counter() - t0
        return False, 0, {"_error": str(exc)}, elapsed, {}


# ---------------------------------------------------------------------------
# Core provisioning logic
# ---------------------------------------------------------------------------


def _provision_row(
    session: requests.Session,
    base_url: str,
    row: dict[str, str],
    row_num: int,
) -> dict[str, Any]:
    """
    Provision one Organization through all 7 API steps.

    Fail-fast: the moment any step fails all subsequent steps are marked
    SKIPPED (this applies to every step, including Step 1).
    Returns a filled result dict.
    """
    org_title = _get(row, "organization title")
    result = _make_result(row_num, org_title)

    failed = False
    org_id = ""
    user_id = ""

    # ------------------------------------------------------------------
    # Step 1: Create Organization
    # ------------------------------------------------------------------
    s = "Step 1 Create Organization"
    path = "services/v2/organization"
    payload = _build_org_payload(row)
    print(f"\n  {s}  [{_now()}]")
    print(f"    POST  /{path}")
    print(f"    body: {_fmt_payload(payload)}")
    ok, status, body, elapsed, headers = _api_call(session, "POST", f"{base_url}{path}", json=payload)
    if ok:
        org_id = str(body.get("data", {}).get("id", ""))
        if org_id:
            result["Organization ID"] = org_id
            result[s] = _OK
            print(f"    --> {status}  id={org_id}  ({elapsed:.2f}s)  [{_now()}]")
        else:
            failed = True
            result[s] = _FAIL
            msg = f"Created OK but no 'id' in response: {json.dumps(body)[:200]}"
            result["Errors"].append(f"{s}: {msg}")
            print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
            print(f"    error: {msg}")
            _diag = _fmt_diagnostic_headers(headers)
            if _diag:
                print(f"    trace: {_diag}")
    else:
        failed = True
        result[s] = _FAIL
        err_body = json.dumps(body)[:300]
        result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
        print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
        print(f"    error: {err_body}")
        _diag = _fmt_diagnostic_headers(headers)
        if _diag:
            print(f"    trace: {_diag}")

    # ------------------------------------------------------------------
    # Step 2: Organization notification preferences
    # ------------------------------------------------------------------
    s = "Step 2 Notifications"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    else:
        path = f"services/v2/organization/notification/{org_id}"
        payload = _build_notification_payload(row)
        print(f"\n  {s}  [{_now()}]")
        print(f"    PUT   /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        ok, status, body, elapsed, headers = _api_call(session, "PUT", f"{base_url}{path}", json=payload)
        if ok:
            result[s] = _OK
            print(f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]")
        else:
            failed = True
            result[s] = _FAIL
            err_body = json.dumps(body)[:300]
            result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
            print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
            print(f"    error: {err_body}")
            _diag = _fmt_diagnostic_headers(headers)
            if _diag:
                print(f"    trace: {_diag}")

    # ------------------------------------------------------------------
    # Step 3: Organization reporting preferences
    # ------------------------------------------------------------------
    s = "Step 3 Reporting Prefs"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    else:
        path = f"services/v2/organization/reporting-preferences/{org_id}"
        payload = _build_reporting_prefs_payload(row)
        print(f"\n  {s}  [{_now()}]")
        print(f"    PUT   /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        ok, status, body, elapsed, headers = _api_call(session, "PUT", f"{base_url}{path}", json=payload)
        if ok:
            result[s] = _OK
            print(f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]")
        else:
            failed = True
            result[s] = _FAIL
            err_body = json.dumps(body)[:300]
            result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
            print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
            print(f"    error: {err_body}")
            _diag = _fmt_diagnostic_headers(headers)
            if _diag:
                print(f"    trace: {_diag}")

    # ------------------------------------------------------------------
    # Step 4: Add monitored domain
    # ------------------------------------------------------------------
    s = "Step 4 Domain"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    else:
        domain = _get(row, "domain")
        path = f"services/v2/monitor/{org_id}/domain"
        payload = {"domain": domain}
        print(f"\n  {s}  [{_now()}]")
        print(f"    POST  /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        for attempt in range(_MAX_ATTEMPTS):
            ok, status, body, elapsed, headers = _api_call(session, "POST", f"{base_url}{path}", json=payload)
            if ok:
                result[s] = _OK
                print(f"    --> {status}  domain={domain}  ({elapsed:.2f}s)  [{_now()}]")
                break
            if status == 403 and attempt < _MAX_ATTEMPTS - 1:
                _diag = _fmt_diagnostic_headers(headers)
                _trace = f"  trace: {_diag}" if _diag else ""
                print(
                    f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]  "
                    f"attempt {attempt + 1}/{_MAX_ATTEMPTS} -- org not yet available, "
                    f"retrying in {_RETRY_DELAY}s...{_trace}"
                )
                time.sleep(_RETRY_DELAY)
            else:
                failed = True
                result[s] = _FAIL
                err_body = json.dumps(body)[:300]
                result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
                print(
                    f"    --> {status}  FAILED  (attempt {attempt + 1}/{_MAX_ATTEMPTS})  ({elapsed:.2f}s)  [{_now()}]"
                )
                print(f"    error: {err_body}")
                _diag = _fmt_diagnostic_headers(headers)
                if _diag:
                    print(f"    trace: {_diag}")
                break

    # ------------------------------------------------------------------
    # Step 5: Create user
    # ------------------------------------------------------------------
    s = "Step 5 Create User"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    else:
        path = "services/v2/user"
        payload = _build_user_payload(row, org_id)
        print(f"\n  {s}  [{_now()}]")
        print(f"    POST  /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        ok, status, body, elapsed, headers = _api_call(session, "POST", f"{base_url}{path}", json=payload)
        if ok:
            user_id = str(body.get("data", {}).get("id", ""))
            if user_id:
                result["User ID"] = user_id
                result[s] = _OK
                print(f"    --> {status}  id={user_id}  ({elapsed:.2f}s)  [{_now()}]")
            else:
                failed = True
                result[s] = _FAIL
                msg = f"Created OK but no 'id' in response: {json.dumps(body)[:200]}"
                result["Errors"].append(f"{s}: {msg}")
                print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
                print(f"    error: {msg}")
                _diag = _fmt_diagnostic_headers(headers)
                if _diag:
                    print(f"    trace: {_diag}")
        else:
            failed = True
            result[s] = _FAIL
            err_body = json.dumps(body)[:300]
            result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
            print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
            print(f"    error: {err_body}")
            _diag = _fmt_diagnostic_headers(headers)
            if _diag:
                print(f"    trace: {_diag}")

    # ------------------------------------------------------------------
    # Step 6: User notification/date-format preferences
    # The user record may take a moment to become available after creation.
    # Retry up to 3 times with a 3-second gap to absorb the propagation delay.
    # ------------------------------------------------------------------
    s = "Step 6 User Prefs"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    else:
        path = f"services/v2/user/{user_id}/preferences"
        payload = _build_user_prefs_payload(row)
        print(f"\n  {s}  [{_now()}]")
        print(f"    PUT   /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        for attempt in range(_MAX_ATTEMPTS):
            ok, status, body, elapsed, headers = _api_call(session, "PUT", f"{base_url}{path}", json=payload)
            if ok:
                result[s] = _OK
                print(f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]")
                break
            if status == 404 and attempt < _MAX_ATTEMPTS - 1:
                print(
                    f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]  "
                    f"attempt {attempt + 1}/{_MAX_ATTEMPTS} -- user record not yet available, "
                    f"retrying in {_RETRY_DELAY}s..."
                )
                time.sleep(_RETRY_DELAY)
            else:
                failed = True
                result[s] = _FAIL
                err_body = json.dumps(body)[:300]
                result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
                print(
                    f"    --> {status}  FAILED  (attempt {attempt + 1}/{_MAX_ATTEMPTS})  ({elapsed:.2f}s)  [{_now()}]"
                )
                print(f"    error: {err_body}")
                _diag = _fmt_diagnostic_headers(headers)
                if _diag:
                    print(f"    trace: {_diag}")
                break

    # ------------------------------------------------------------------
    # Step 7: Automated report configuration (optional)
    # ------------------------------------------------------------------
    s = "Step 7 Automated Report"
    if failed:
        result[s] = _SKIPPED
        print(f"  {s}  [SKIPPED]  [{_now()}]")
    elif not _yesno(_get(row, "automated report")):
        result[s] = _NA
        print(f"\n  {s}  [{_now()}]")
        print(f"    --> N/A  (Automated Report = No)  [{_now()}]")
    else:
        path = f"services/v2/reporting/{org_id}"
        payload = _build_report_config_payload(row)
        print(f"\n  {s}  [{_now()}]")
        print(f"    PUT   /{path}")
        print(f"    body: {_fmt_payload(payload)}")
        ok, status, body, elapsed, headers = _api_call(session, "PUT", f"{base_url}{path}", json=payload)
        if ok:
            result[s] = _OK
            print(f"    --> {status}  ({elapsed:.2f}s)  [{_now()}]")
        else:
            failed = True
            result[s] = _FAIL
            err_body = json.dumps(body)[:300]
            result["Errors"].append(f"{s}: HTTP {status} -- {json.dumps(body)[:200]}")
            print(f"    --> {status}  FAILED  ({elapsed:.2f}s)  [{_now()}]")
            print(f"    error: {err_body}")
            _diag = _fmt_diagnostic_headers(headers)
            if _diag:
                print(f"    trace: {_diag}")

    result["Overall Status"] = "FAILED" if failed else "SUCCESS"
    return result


# ---------------------------------------------------------------------------
# Results spreadsheet writer
# ---------------------------------------------------------------------------

_FILL_HEADER = PatternFill(fill_type="solid", fgColor="1F4E79")
_FILL_OK = PatternFill(fill_type="solid", fgColor="C6EFCE")
_FILL_FAIL = PatternFill(fill_type="solid", fgColor="FFC7CE")
_FILL_SKIPPED = PatternFill(fill_type="solid", fgColor="FFEB9C")
_FILL_NA = PatternFill(fill_type="solid", fgColor="EDEDED")
_FILL_NOT_RUN = PatternFill(fill_type="solid", fgColor="F2F2F2")

_FONT_HEADER = Font(name="Calibri", color="FFFFFF", bold=True)
_FONT_SUCCESS = Font(color="006100", bold=True)
_FONT_FAILED = Font(color="9C0006", bold=True)

def _append_result(result: dict[str, Any], output_path: Path) -> None:
    """Append one result row to the output spreadsheet, creating it if needed."""
    if output_path.exists():
        wb = openpyxl.load_workbook(output_path)
        ws = wb.active
    else:
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Provisioning Results"
        for col_idx, col_name in enumerate(RESULT_COLS, start=1):
            cell = ws.cell(row=1, column=col_idx, value=col_name)
            cell.font = _FONT_HEADER
            cell.fill = _FILL_HEADER
            cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        ws.row_dimensions[1].height = 30
        ws.freeze_panes = "A2"

    row_idx = ws.max_row + 1
    errors_str = "\n".join(result.get("Errors", []))
    for col_idx, col_name in enumerate(RESULT_COLS, start=1):
        value = errors_str if col_name == "Errors" else result.get(col_name, "")
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.alignment = Alignment(wrap_text=True, vertical="top")
        if col_name in STEP_LABELS:
            cell.fill = {
                _OK: _FILL_OK,
                _FAIL: _FILL_FAIL,
                _SKIPPED: _FILL_SKIPPED,
                _NA: _FILL_NA,
                _NOT_RUN: _FILL_NOT_RUN,
            }.get(str(value), PatternFill())
        if col_name == "Overall Status":
            if value == "SUCCESS":
                cell.font = _FONT_SUCCESS
            elif str(value).startswith("FAIL"):
                cell.font = _FONT_FAILED

    wb.save(output_path)


# ---------------------------------------------------------------------------
# CLI
# ---------------------------------------------------------------------------


def _parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        prog="provision_organizations.py",
        description="Provision Organizations via the DarkWebID V2 External API.",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
  Provision from an Excel file:
    python provision_organizations.py \\
        --url https://DARKWEBID_URL/ \\
        --username {darkweb_api_user_email_address} \\
        --password {darkweb_api_user_password} \\
        --file provisioning_data.xlsx

  Dry-run (validate + print plan, no API calls):
    python provision_organizations.py \\
        --url https://DARKWEBID_URL/ \\
        --username {darkweb_api_user_email_address} \\
        --password {darkweb_api_user_password} \\
        --file provisioning_data.xlsx \\
        --dry-run

  Use a CSV file with a custom output path:
    python provision_organizations.py \\
        --url https://DARKWEBID_URL/ \\
        --username {darkweb_api_user_email_address} \\
        --password {darkweb_api_user_password} \\
        --file provisioning_data.csv \\
        --output my_results.xlsx
""",
    )
    parser.add_argument(
        "--url",
        required=True,
        help="Base URL of the DarkWebID API (e.g. https://DARKWEBID_URL/).",
    )
    parser.add_argument(
        "--username",
        required=True,
        help="API username (email address).",
    )
    parser.add_argument(
        "--password",
        required=True,
        help="API password.",
    )
    parser.add_argument(
        "--file",
        required=True,
        help="Path to the provisioning data file (.xlsx or .csv).",
    )
    parser.add_argument(
        "--output",
        default=None,
        help=(
            "Path for the results spreadsheet. "
            "Default: provisioning_results_YYYYMMDD_HHMMSS.xlsx "
            "in the same directory as --file."
        ),
    )
    parser.add_argument(
        "--dry-run",
        action="store_true",
        help="Validate the input file and print the provisioning plan. No API calls are made.",
    )
    parser.add_argument(
        "--delay",
        type=float,
        default=2.0,
        metavar="SECONDS",
        help=(
            "Seconds to pause between Organizations (default: 2.0). "
            "Prevents triggering the API rate-limiter when provisioning many rows. "
            "Set to 0 to disable."
        ),
    )
    return parser.parse_args()


def _default_output_path(input_path: Path, stamp: str) -> Path:
    return input_path.parent / f"provisioning_results_{stamp}.xlsx"


def main() -> None:  # noqa: C901
    args = _parse_args()

    # Ensure base URL ends with a slash
    base_url: str = args.url if args.url.endswith("/") else args.url + "/"
    if not base_url.startswith("http://") and not base_url.startswith("https://"):
        base_url = "https://" + base_url

    # Locate and read input file
    input_path = Path(args.file).resolve()
    if not input_path.exists():
        print(f"[ERROR] Input file not found: {input_path}")
        sys.exit(1)

    print(f"Reading {input_path} ...")

    # Output and log paths share one timestamp so auto-named files are trivially paired.
    stamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_path = Path(args.output).resolve() if args.output else _default_output_path(input_path, stamp)
    log_path = output_path.parent / f"provisioning_log_{stamp}.txt"

    # Single pass: count rows, validate, and write each row's result immediately.
    print("\nValidating all rows ...")
    errors_count = 0
    row_count = 0
    for idx, row in enumerate(_read_file(input_path), start=1):
        row_count += 1
        row_errs = _validate_row(row, idx)
        errors_count += len(row_errs)
        for err in row_errs:
            print(f"  - {err}")
        r = _make_result(idx, _get(row, "organization title"))
        r["Overall Status"] = "VALIDATION FAILED" if row_errs else "NOT RUN"
        r["Errors"] = row_errs
        for step in STEP_LABELS:
            r[step] = _NOT_RUN
        _append_result(r, output_path)

    if row_count == 0:
        output_path.unlink(missing_ok=True)
        print("[ERROR] The input file is empty or contains no data rows.")
        sys.exit(1)
    print(f"Found {row_count} data row(s).")

    if errors_count:
        print(f"\n[VALIDATION FAILED] Total: {errors_count} error(s):\n")
        print(f"\n  Results saved: {output_path}")
        sys.exit(1)

    # Validation passed — discard the NOT RUN file written during the pass.
    output_path.unlink(missing_ok=True)
    print("All rows valid.")

    # Dry-run: print plan and exit without making any API calls.
    # Re-stream the file for the plan output.
    if args.dry_run:
        print("\n--- DRY RUN PLAN ---")
        for idx, row in enumerate(_read_file(input_path), start=1):
            title = _get(row, "organization title")
            domain = _get(row, "domain")
            email = _get(row, "user email")
            auto_report = "Yes" if _yesno(_get(row, "automated report")) else "No"
            steps = "1-7" if auto_report == "Yes" else "1-6 (Step 7: N/A)"
            print(
                f"\n  Organization {idx}: {title}\n"
                f"    Domain:           {domain}\n"
                f"    User:       {email}\n"
                f"    Automated report: {auto_report}\n"
                f"    Steps:            {steps}"
            )
        print(f"\n{row_count} Organization(s) would be provisioned (no API calls made).")
        sys.exit(0)

    # Build session with Basic Auth
    session = requests.Session()
    session.auth = (args.username, args.password)
    session.headers.update(
        {
            "Content-Type": "application/json",
            "Accept": "application/json",
        }
    )

    inter_org_delay: float = max(0.0, args.delay)

    # Tee stdout → both console and log file from this point forward.
    # Line-buffered writes ensure the log is readable even after a crash.
    tee = _Tee(log_path)
    sys.stdout = tee

    total = row_count
    n_w = len(str(total))
    exit_code = 0
    try:
        _print_run_header(input_path, output_path, log_path, base_url, total)
        if inter_org_delay > 0:
            print(f"  Inter-Organization delay: {inter_org_delay:.1f}s  (use --delay 0 to disable)")
        run_start = time.perf_counter()

        # Provision rows — re-stream the file for the provisioning pass.
        succeeded = 0
        failed = 0

        for idx, row in enumerate(_read_file(input_path), start=1):
            org_title = _get(row, "organization title")
            pct_before = (idx - 1) / total * 100

            # Per-Organization banner
            print(f"\n[{idx:>{n_w}}/{total} | {pct_before:>3.0f}%]  {org_title}  [{_now()}]")

            org_t0 = time.perf_counter()
            result = _provision_row(session, base_url, row, idx)
            org_elapsed = time.perf_counter() - org_t0

            if result["Overall Status"] == "SUCCESS":
                succeeded += 1
                print(f"\n  --> SUCCESS  ({org_elapsed:.1f}s)")
            else:
                failed += 1
                print(f"\n  --> FAILED  ({org_elapsed:.1f}s)")

            print()
            _print_progress(idx, total, run_start)

            # Write after every row; partial results survive an unexpected exit
            _append_result(result, output_path)

            # Pace requests to avoid triggering the API rate-limiter
            if inter_org_delay > 0 and idx < total:
                print(f"  [pausing {inter_org_delay:.1f}s]")
                time.sleep(inter_org_delay)

        # Final summary
        _print_run_footer(output_path, log_path, succeeded, failed, total, run_start)

        if failed > 0:
            exit_code = 1

    finally:
        tee.close()

    sys.exit(exit_code)


if __name__ == "__main__":
    main()