Event Lead Enrichment

"""Shared utilities for event-lead-enrichment scripts.

Contains: Markdown rubric/alias loading + validation, shared xlsx styling
constants, Summary sheet rendering helpers.
"""

from pathlib import Path
from typing import Any


REQUIRED_RUBRIC_SECTIONS = {
    "Physical World Verticals": "physical_world_verticals",
    "Tech Strong": "tech_strong",
    "Tech Medium": "tech_med",
    "Hot Keywords": "hot_keywords",
    "Warm Keywords": "warm_keywords",
    "ICP Score Weights": "icp_score_weights",
    "Tier Thresholds": "tier_thresholds",
    "Tier A Gate": "tier_a_gate",
    "Heat Rules": "heat_rules",
    "Size Buckets": "size_buckets",
    "Summary Rubric Text": "summary_rubric_text",
}

REQUIRED_LIST_KEYS = {
    "physical_world_verticals",
    "tech_strong",
    "tech_med",
    "hot_keywords",
    "warm_keywords",
}

REQUIRED_SCORE_WEIGHT_KEYS = {
    "strong_tech",
    "medium_tech",
    "physical_world",
    "employees_500_plus",
    "v1_account_80_plus",
}

ICP_WEIGHT_LABEL_TO_KEY = {
    "strong tech": "strong_tech",
    "medium tech": "medium_tech",
    "physical world": "physical_world",
    "employees 500+": "employees_500_plus",
    "v1 account 80+": "v1_account_80_plus",
}

RUBRIC_SLUG_HINT = "event-lead-scoring-rubric"
ALIASES_SLUG_HINT = "event-lead-domain-aliases"


# ── Line-based Markdown parser ────────────────────────────────────────────────

def _split_sections(text: str) -> dict[str, str]:
    """Split Markdown into sections keyed by H2 header text.

    Handles H3 sub-headers by grouping them into a nested dict under the
    parent H2's key (used for Size Buckets). Returns a flat dict for all
    other sections where the value is the raw body text.

    This is intentionally simple: it only understands '## ' and '### '
    prefixes, which is all the rubric/aliases docs use.
    """
    sections: dict[str, str] = {}
    current_h2: str | None = None
    current_h3: str | None = None
    body_lines: list[str] = []

    def flush():
        nonlocal current_h2, current_h3, body_lines
        if current_h2 is None:
            body_lines = []
            return
        body = "\n".join(body_lines).strip()
        if current_h3 is not None:
            # Nest H3 under H2 using a compound key "H2 > H3"
            sections[f"{current_h2} > {current_h3}"] = body
        else:
            sections[current_h2] = body
        body_lines = []

    for line in text.splitlines():
        if line.startswith("## "):
            flush()
            current_h2 = line[3:].strip()
            current_h3 = None
        elif line.startswith("### "):
            flush()
            current_h3 = line[4:].strip()
        else:
            body_lines.append(line)

    flush()
    return sections


def _parse_bullet_list(body: str, lowercase: bool = True) -> list[str]:
    """Extract '- item' lines from a section body.

    Args:
        body: raw section body text
        lowercase: if True, lowercase each item (default for keyword/vertical lists)
    """
    items = []
    for line in body.splitlines():
        stripped = line.strip()
        if stripped.startswith("- "):
            value = stripped[2:].strip()
            if lowercase:
                value = value.lower()
            items.append(value)
    return items


def _parse_kv_bullets(
    body: str, comma_split: bool = False, snake_case_keys: bool = True
) -> dict[str, Any]:
    """Extract '- Key: value' bullets into a dict.

    Value coercions:
    - 'true' / 'false' → bool
    - integer strings → int
    - comma-separated strings → list[str]  (only when comma_split=True)
    - else → str (preserved as-is, including Unicode, em-dashes, smart quotes)

    Args:
        body: raw section body text
        comma_split: if True, split comma-containing values into list[str].
            Use for machine-readable fields (e.g. heat rules status lists).
            Leave False (default) for prose fields (e.g. summary rubric text).
        snake_case_keys: if True (default), convert key to snake_case
            (lowercase + spaces → underscores). Set False to preserve the raw
            key text (just stripped + lowercased) — needed for keys that
            contain hyphens or dots, e.g. domain names or size-range labels
            like '1 - 9'.
    """
    result: dict[str, Any] = {}
    for line in body.splitlines():
        stripped = line.strip()
        if not stripped.startswith("- "):
            continue
        content = stripped[2:]
        if ":" not in content:
            continue
        key_raw, _, val_raw = content.partition(":")
        raw_key = key_raw.strip().lower()
        key = raw_key.replace(" ", "_") if snake_case_keys else raw_key
        val = val_raw.strip()
        # coerce
        if val.lower() == "true":
            result[key] = True
        elif val.lower() == "false":
            result[key] = False
        else:
            try:
                result[key] = int(val)
            except ValueError:
                # comma-separated list or plain string
                if comma_split and "," in val:
                    result[key] = [v.strip() for v in val.split(",")]
                else:
                    result[key] = val
    return result



# ── Rubric loader ─────────────────────────────────────────────────────────────

def load_rubric(path: Path) -> dict[str, Any]:
    """Load and validate a scoring rubric Markdown file.

    Raises ValueError with a clear error message (including the Tiger Den
    slug) on any validation failure — bad configs fail fast so operators
    can fix the Google Doc rather than debug silent miscounts.
    """
    with open(path) as f:
        raw = f.read()

    sections = _split_sections(raw)

    # ── Validate required sections present ───────────────────────────────────
    missing_display = []
    for display_name in REQUIRED_RUBRIC_SECTIONS:
        if display_name not in sections:
            missing_display.append(display_name)
    if missing_display:
        missing_keys = [REQUIRED_RUBRIC_SECTIONS[n] for n in missing_display]
        raise ValueError(
            f"[{RUBRIC_SLUG_HINT}] missing required sections: {sorted(missing_keys)}"
        )

    rubric: dict[str, Any] = {}

    # ── Bullet list sections ──────────────────────────────────────────────────
    rubric["physical_world_verticals"] = _parse_bullet_list(
        sections["Physical World Verticals"], lowercase=True
    )
    rubric["tech_strong"] = _parse_bullet_list(sections["Tech Strong"], lowercase=True)
    rubric["tech_med"] = _parse_bullet_list(sections["Tech Medium"], lowercase=True)
    rubric["hot_keywords"] = _parse_bullet_list(sections["Hot Keywords"], lowercase=True)
    rubric["warm_keywords"] = _parse_bullet_list(sections["Warm Keywords"], lowercase=True)

    # ── Non-empty list validation ─────────────────────────────────────────────
    section_to_key = {
        "Physical World Verticals": "physical_world_verticals",
        "Tech Strong": "tech_strong",
        "Tech Medium": "tech_med",
        "Hot Keywords": "hot_keywords",
        "Warm Keywords": "warm_keywords",
    }
    for section_name, key in section_to_key.items():
        value = rubric[key]
        if not isinstance(value, list) or not value:
            raise ValueError(
                f"[{RUBRIC_SLUG_HINT}] '{key}' must be a non-empty list (got empty or non-list)"
            )

    # ── ICP Score Weights ─────────────────────────────────────────────────────
    # Keys in the doc use human labels (e.g. "Strong tech"); ICP_WEIGHT_LABEL_TO_KEY
    # maps them to snake_case identifiers. _parse_kv_bullets returns snake_case
    # keys by default, so we re-map via the label→key table.
    raw_weight_kv = _parse_kv_bullets(sections["ICP Score Weights"])
    weights: dict[str, int] = {}
    for raw_key, val in raw_weight_kv.items():
        # raw_key is already snake_case; reconstruct the space-separated label
        # to look up in ICP_WEIGHT_LABEL_TO_KEY.
        label = raw_key.replace("_", " ")
        mapped_key = ICP_WEIGHT_LABEL_TO_KEY.get(label)
        if mapped_key is None:
            continue
        if not isinstance(val, int):
            raise ValueError(
                f"[{RUBRIC_SLUG_HINT}] icp_score_weights.{mapped_key} must be an integer, got {val!r}"
            )
        weights[mapped_key] = val

    missing_weights = REQUIRED_SCORE_WEIGHT_KEYS - weights.keys()
    if missing_weights:
        raise ValueError(
            f"[{RUBRIC_SLUG_HINT}] icp_score_weights missing keys: {sorted(missing_weights)}"
        )
    rubric["icp_score_weights"] = weights

    # ── Tier Thresholds ───────────────────────────────────────────────────────
    # Keys are single letters (A/B/C). _parse_kv_bullets lowercases keys by
    # default, so we uppercase them after parsing.
    raw_threshold_kv = _parse_kv_bullets(sections["Tier Thresholds"])
    thresholds: dict[str, int] = {}
    for tier_key, val in raw_threshold_kv.items():
        tier_upper = tier_key.strip().upper()
        if not isinstance(val, int):
            raise ValueError(
                f"[{RUBRIC_SLUG_HINT}] tier_thresholds.{tier_upper} must be an integer, got {val!r}"
            )
        thresholds[tier_upper] = val
    rubric["tier_thresholds"] = thresholds

    if not (thresholds.get("A", 0) > thresholds.get("B", 0) > thresholds.get("C", 0)):
        raise ValueError(
            f"[{RUBRIC_SLUG_HINT}] tier_thresholds must satisfy A > B > C, got {thresholds}"
        )

    # ── Tier A Gate ───────────────────────────────────────────────────────────
    gate_kv = _parse_kv_bullets(sections["Tier A Gate"])
    rubric["tier_a_gate"] = gate_kv

    # ── Heat Rules ────────────────────────────────────────────────────────────
    heat_kv = _parse_kv_bullets(sections["Heat Rules"], comma_split=True)
    # Normalize list fields: hot_lead_statuses / warm_lead_statuses → lists
    # The KV parser may produce a string or list depending on comma presence.
    def _ensure_list(val: Any) -> list[str]:
        if isinstance(val, list):
            return [v.strip().upper() for v in val]
        return [val.strip().upper()]

    heat_rules: dict[str, Any] = {}
    for k, v in heat_kv.items():
        if k == "hot_lead_statuses":
            heat_rules["hot_lead_status"] = _ensure_list(v)
        elif k == "warm_lead_statuses":
            heat_rules["warm_lead_status"] = _ensure_list(v)
        else:
            heat_rules[k] = v
    rubric["heat_rules"] = heat_rules

    # ── Size Buckets ──────────────────────────────────────────────────────────
    canonical_key = "Size Buckets > Canonical"
    cr_mapping_key = "Size Buckets > CR Mapping"
    canonical_list = _parse_bullet_list(
        sections.get(canonical_key, ""), lowercase=False
    )
    # CR Mapping keys contain hyphens and spaces (e.g. "1 - 9") — must not be
    # snake-cased. Values are strings like "Under 10" or "1000+".
    cr_kv = _parse_kv_bullets(sections.get(cr_mapping_key, ""), snake_case_keys=False)
    # Values may have been coerced to int if they look numeric — convert back to str
    # to match the canonical bucket labels.
    cr_mapping = {k: str(v) for k, v in cr_kv.items()}
    rubric["size_buckets"] = {
        "canonical": canonical_list,
        "cr_mapping": cr_mapping,
    }

    # ── Summary Rubric Text ───────────────────────────────────────────────────
    summary_kv = _parse_kv_bullets(sections["Summary Rubric Text"])
    rubric["summary_rubric_text"] = summary_kv

    return rubric


# ── Aliases loader ────────────────────────────────────────────────────────────

def load_aliases(path: Path) -> dict[str, str]:
    """Load and validate a domain alias map Markdown file."""
    with open(path) as f:
        raw = f.read()

    sections = _split_sections(raw)

    if "Aliases" not in sections:
        raise ValueError(
            f"[{ALIASES_SLUG_HINT}] Markdown must have an '## Aliases' section"
        )

    # Keys are domain names (e.g. "kpmg.fr") — must not be snake-cased.
    raw_kv = _parse_kv_bullets(sections["Aliases"], snake_case_keys=False)
    aliases = {k: str(v).lower().strip() for k, v in raw_kv.items()}

    return aliases


def apply_alias(domain: str, alias_map: dict[str, str]) -> str:
    """Return the canonical domain after applying the alias map. Case-insensitive."""
    key = (domain or "").lower().strip()
    return alias_map.get(key, key)

Privacy review passed on 2026-04-24.

Scope: all .py files in scripts/ directory + SKILL.md + references/.
Grep patterns checked:
  - Proprietary tech names (postgres, timescale, clickhouse, influx, cassandra, kafka, victoriametrics, prometheus, hadoop)
  - ICP industry markers (manufactur, industrial, automotive, aerospace, semiconductor, robotics, automation)

Zero industry-marker hits in production code.
Two tech-name hits resolved:
  - build_enriched.py:39 — docstring example of enrichment.json shape uses "Prometheus"/"Kubernetes"
    as illustrative tech_highlights values. These are public open-source projects named in public
    CR documentation; leaving them in the docstring is defensible. Not proprietary signal content.
  - build_enriched.py:~394 — previously hardcoded "Timescale/Postgres mention" string in warmth
    reason. Replaced with dynamic "strong tech mention ({matched terms from rubric})" so the
    label reflects whatever rubric.tech_strong contains, not a baked-in product list.

Production rubric content (scoring-policy tech lists, physical-world vertical markers, keyword
lists, tier thresholds, Summary explanation text) lives in Tiger Den under these slugs:
  - event-lead-scoring-rubric
  - event-lead-domain-aliases

Test fixtures (scripts/tests/fixtures/smoke-rubric.yaml, smoke-aliases.yaml, smoke-customers.json,
smoke-leads.csv) intentionally contain minimal sample values for smoke testing — they are NOT the
production rubric and should not be treated as such.

"""Combined-workbook rollup for multi-day events (Phase C).

Reads per-day enriched xlsx files, harmonizes columns, and produces a combined
workbook with Day-labeled leads and per-day + Total Summary stats.
"""

import argparse
from collections import Counter
from pathlib import Path

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

from _common import load_rubric

TIER_ORDER = {"Conversation": 0, "A": 1, "B": 2, "C": 3, "D": 4}
HEAT_ORDER = {"Hot": 0, "Warm": 1, "Mild": 2, None: 3}
FLAG_ORDER = {"HOT (no note)": 0, "WARM (no note)": 1, "": 2, None: 2}


def infer_day_label(path: Path) -> str:
    """Derive a 'Day N' label from the filename, defaulting to file stem."""
    stem = path.stem.lower()
    for token in stem.replace("_", "-").split("-"):
        if token.startswith("day"):
            digits = "".join(c for c in token if c.isdigit())
            if digits:
                return f"Day {digits}"
    return path.stem


def read_enriched(path: Path, day_label: str):
    """Read an enriched xlsx's All Leads + Removed sheets.

    Returns (all_leads_header, all_leads_rows, removed_header, removed_rows).
    Each row is a list; the Day column is appended at the end.
    """
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)

    ws_leads = wb["All Leads"]
    rows_iter = ws_leads.iter_rows(values_only=True)
    leads_header = list(next(rows_iter, ()))
    leads_rows = [list(r) + [day_label] for r in rows_iter if any(c is not None for c in r)]

    removed_header = []
    removed_rows = []
    if "Removed" in wb.sheetnames:
        ws_rem = wb["Removed"]
        rem_iter = ws_rem.iter_rows(values_only=True)
        removed_header = list(next(rem_iter, ()))
        removed_rows = [list(r) + [day_label] for r in rem_iter if any(c is not None for c in r)]

    return leads_header, leads_rows, removed_header, removed_rows


def harmonize(headers_list):
    """Return the ordered union of headers across all input files."""
    combined = list(headers_list[0])
    for h in headers_list[1:]:
        for col in h:
            if col not in combined:
                combined.append(col)
    return combined


def warn_column_mismatch(paths, headers_list):
    """Print a warning if any file has columns not in the first file."""
    first = set(headers_list[0])
    for path, h in zip(paths[1:], headers_list[1:]):
        extra = set(h) - first
        if extra:
            print(f"[WARN] column mismatch — {path.name} has extra columns: {sorted(extra)}")


def reorder_to_header(row, source_header, target_header):
    """Re-order a row so its cells match target_header. Missing cells → empty string."""
    src_idx = {col: i for i, col in enumerate(source_header)}
    return [row[src_idx[col]] if col in src_idx else "" for col in target_header]


def sort_key(row, header):
    def _col(name, default=None):
        return row[header.index(name)] if name in header else default

    return (
        TIER_ORDER.get(_col("Tier"), 5),
        HEAT_ORDER.get(_col("Conversation_Heat"), 3),
        FLAG_ORDER.get(_col("SDR_Status_Flag") or "", 2),
        -(int(_col("ICP_Score") or 0)),
        -(int(_col("Warmth") or 0)),
    )


def build_combined_summary(ws, event_name, per_day_stats, total_stats, rubric):
    ws["A1"] = f"{event_name} — Combined Enrichment Summary"
    ws["A1"].font = Font(bold=True, size=14)
    ws.merge_cells("A1:Z1")

    day_labels = list(per_day_stats.keys())
    header_row = ["Metric"] + day_labels + ["Total"]
    ws.append([])
    ws.append(header_row)
    for c in ws[ws.max_row]:
        c.font = Font(bold=True)

    metric_keys = [
        ("Total rows kept", "total_kept"),
        ("Current customers", "customers_current"),
        ("Previous customers (churned)", "customers_previous"),
        ("Tier Conversation", "tier_conversation"),
        ("Tier A", "tier_a"),
        ("Tier B", "tier_b"),
        ("Tier C", "tier_c"),
        ("Tier D", "tier_d"),
        ("Heat: Hot", "heat_hot"),
        ("Heat: Warm", "heat_warm"),
        ("Heat: Mild", "heat_mild"),
        ("SDR HOT (no note)", "sdr_hot_no_note"),
        ("SDR WARM (no note)", "sdr_warm_no_note"),
    ]
    for label, key in metric_keys:
        row = [label] + [per_day_stats[d].get(key, 0) for d in day_labels] + [total_stats.get(key, 0)]
        ws.append(row)

    rubric_text = rubric["summary_rubric_text"]
    ws.append([])
    ws.append(["Scoring rubric", ""])
    for key in ["tier_conversation", "tier_a", "tier_a_gate", "tier_b", "tier_c", "tier_d",
                "heat_hot", "heat_warm", "heat_mild",
                "is_customer_current", "is_customer_previous", "is_customer_no"]:
        ws.append([key.replace("_", " ").title(), rubric_text.get(key, "")])

    ws.column_dimensions["A"].width = 36
    for i, _ in enumerate(day_labels, start=2):
        ws.column_dimensions[get_column_letter(i)].width = 14
    for row in ws.iter_rows(min_row=3):
        for c in row:
            c.alignment = Alignment(vertical="top", wrap_text=True)


def compute_day_stats(rows, header):
    tier_col = header.index("Tier") if "Tier" in header else None
    heat_col = header.index("Conversation_Heat") if "Conversation_Heat" in header else None
    cust_col = header.index("Is_Customer") if "Is_Customer" in header else None
    sdr_col = header.index("SDR_Status_Flag") if "SDR_Status_Flag" in header else None

    tiers = Counter()
    heats = Counter()
    custs = Counter()
    sdr_flags = Counter()
    for r in rows:
        if tier_col is not None:
            tiers[r[tier_col]] += 1
        if heat_col is not None:
            heats[r[heat_col]] += 1
        if cust_col is not None:
            custs[r[cust_col]] += 1
        if sdr_col is not None:
            sdr_flags[r[sdr_col] or ""] += 1

    return {
        "total_kept": len(rows),
        "tier_conversation": tiers.get("Conversation", 0),
        "tier_a": tiers.get("A", 0),
        "tier_b": tiers.get("B", 0),
        "tier_c": tiers.get("C", 0),
        "tier_d": tiers.get("D", 0),
        "heat_hot": heats.get("Hot", 0),
        "heat_warm": heats.get("Warm", 0),
        "heat_mild": heats.get("Mild", 0),
        "customers_current": custs.get("Current", 0),
        "customers_previous": custs.get("Previous", 0),
        "sdr_hot_no_note": sdr_flags.get("HOT (no note)", 0),
        "sdr_warm_no_note": sdr_flags.get("WARM (no note)", 0),
    }


def run(args):
    rubric = load_rubric(args.rubric)
    input_paths = [Path(p) for p in args.inputs]

    if args.day_labels:
        labels = [l.strip() for l in args.day_labels.split(",")]
        if len(labels) != len(input_paths):
            raise SystemExit(
                f"--day-labels count ({len(labels)}) != inputs count ({len(input_paths)})"
            )
    else:
        labels = [infer_day_label(p) for p in input_paths]

    leads_headers = []
    leads_rows_per_day = []
    removed_headers = []
    removed_rows_per_day = []
    for path, label in zip(input_paths, labels):
        lh, lr, rh, rr = read_enriched(path, label)
        leads_headers.append(lh)
        leads_rows_per_day.append(lr)
        if rh:
            removed_headers.append(rh)
            removed_rows_per_day.append(rr)

    warn_column_mismatch(input_paths, leads_headers)
    combined_leads_header = harmonize(leads_headers)
    if "Day" not in combined_leads_header:
        combined_leads_header.append("Day")

    all_rows = []
    per_day_stats = {}
    for label, header, rows in zip(labels, leads_headers, leads_rows_per_day):
        header_with_day = header + ["Day"]
        normalized = [reorder_to_header(r, header_with_day, combined_leads_header) for r in rows]
        all_rows.extend(normalized)
        per_day_stats[label] = compute_day_stats(normalized, combined_leads_header)

    all_rows.sort(key=lambda r: sort_key(r, combined_leads_header))

    total_stats = compute_day_stats(all_rows, combined_leads_header)

    wb = openpyxl.Workbook()
    summary = wb.active
    summary.title = "Summary"
    build_combined_summary(summary, args.event, per_day_stats, total_stats, rubric)

    ws_leads = wb.create_sheet("All Leads")
    ws_leads.append(combined_leads_header)
    for c in ws_leads[1]:
        c.font = Font(bold=True)
        c.fill = PatternFill("solid", fgColor="D9D2E9")
    for r in all_rows:
        ws_leads.append(r)
    ws_leads.freeze_panes = "A2"
    ws_leads.auto_filter.ref = ws_leads.dimensions

    ws_removed = wb.create_sheet("Removed")
    if removed_headers:
        combined_rem_header = harmonize(removed_headers)
        if "Day" not in combined_rem_header:
            combined_rem_header.append("Day")
        ws_removed.append(combined_rem_header)
        for c in ws_removed[1]:
            c.font = Font(bold=True)
            c.fill = PatternFill("solid", fgColor="EAD1DC")
        for header, rows in zip(removed_headers, removed_rows_per_day):
            header_with_day = header + ["Day"]
            for r in rows:
                ws_removed.append(reorder_to_header(r, header_with_day, combined_rem_header))
        ws_removed.freeze_panes = "A2"

    Path(args.output).parent.mkdir(parents=True, exist_ok=True)
    wb.save(args.output)

    print(f"[OK] Combined rollup complete: {args.output}")
    print(f"     Days:    {labels}")
    print(f"     Total:   {total_stats['total_kept']} leads across {len(input_paths)} days")
    print(f"     Tiers:   Conv={total_stats['tier_conversation']}, "
          f"A={total_stats['tier_a']}, B={total_stats['tier_b']}, "
          f"C={total_stats['tier_c']}, D={total_stats['tier_d']}")


def build_parser():
    p = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    p.add_argument("--event", required=True, help="Event name (for Summary title)")
    p.add_argument("--inputs", nargs="+", required=True, help="Per-day enriched xlsx files (ordered)")
    p.add_argument("--rubric", required=True, help="Path to scoring rubric Markdown (for Summary consistency)")
    p.add_argument("--output", required=True, help="Path to combined xlsx output")
    p.add_argument("--day-labels", default=None, help='Comma-separated day labels, e.g. "Day 1,Day 2,Day 3"')
    return p


if __name__ == "__main__":
    args = build_parser().parse_args()
    run(args)

#!/usr/bin/env python3
"""
Event lead enrichment pipeline — parameterized replacement for the Hannover
Messe Day 2/3 forked scripts (closes Task #31).

Two-phase design because CR lookups happen via MCP in the Claude session,
not from a Python process:

  PHASE 1 (no --cr-enrichment):
    - normalize CSV schema (auto-detects Hannover vs GrafanCON shapes)
    - dedupe against prior days (if --prior)
    - apply student/trainee filter
    - apply --strip-companies filter (internal/host orgs)
    - match against TigerData customer list
    - emit `domains.json` listing unique corporate domains needing CR lookup
    - write a stub xlsx (no CR firmographics yet) so you can sanity-check
      counts before burning CR calls

  PHASE 2 (with --cr-enrichment enrichment.json):
    - same as phase 1 but additionally merges CR firmographics
    - computes ICP_Fit, ICP_Score, Warmth, Tier, Conversation_Heat, Next_Step
    - produces final xlsx with Summary + All Leads + Removed sheets

enrichment.json shape:
  {
    "by_domain": {
       "grafana.com": {
          "source": "CR",   # or "Prospector"
          "org_id": "o_...",
          "name": "Grafana Labs",
          "primary_domain": "grafana.com",
          "employees": 1200,
          "size_bucket": "1000 - 4999",
          "size_bucket_source": "enriched",
          "revenue_range": "$100M-$500M",
          "sub_industry": "Observability",
          "hq": "New York, USA",
          "v1_account_pct": 92,
          "tech_highlights": ["Prometheus", "Kubernetes"]
       },
       "ndhe.de": { "source": "Prospector", ... },
       "unknown.example": { "source": "NONE" }
    }
  }

Any missing domain is treated as "NONE" (no firmographic data).
"""

import argparse
import csv
import json
import re
from collections import Counter
from pathlib import Path

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

# --------------------------------------------------------------------------
# Canonical columns used everywhere downstream
# --------------------------------------------------------------------------
CANONICAL_COLS = [
    "Company", "Firstname", "Lastname", "E-Mail", "Phone",
    "Function", "Position", "Industry", "Job Title", "Companysize",
    "Notes", "Lead_Status",
]

# --------------------------------------------------------------------------
# Schema detection & mapping
# --------------------------------------------------------------------------
HANNOVER_SIG = {"Firstname", "Lastname", "E-Mail", "Company"}
GRAFANCON_SIG = {"firstName", "lastName", "email", "company", "leadStatus"}

# Hannover (and similar) exports write a literal placeholder when SDRs leave a
# field blank. Treat these as empty so they do not falsely qualify the lead
# for the Conversation tier.
NOTE_PLACEHOLDERS = {"-", "--", "–", "—", "n/a", "na", "none"}


def detect_schema(headers):
    hs = set(headers)
    if GRAFANCON_SIG.issubset(hs):
        return "grafancon"
    if HANNOVER_SIG.issubset(hs) or {"Firstname", "Lastname"}.issubset(hs):
        return "hannover"
    raise SystemExit(f"Unknown CSV schema. Headers: {headers}")


def normalize_note(value):
    s = (value or "").strip()
    if s.lower() in NOTE_PLACEHOLDERS:
        return ""
    return s


def row_to_canonical(row, schema):
    if schema == "grafancon":
        return {
            "Company": (row.get("company") or "").strip(),
            "Firstname": (row.get("firstName") or "").strip(),
            "Lastname": (row.get("lastName") or "").strip(),
            "E-Mail": (row.get("email") or "").strip().lower(),
            "Phone": (row.get("phoneNumber") or "").strip(),
            "Function": "",
            "Position": (row.get("title") or "").strip(),
            "Industry": "",
            "Job Title": (row.get("title") or "").strip(),
            "Companysize": "",
            "Notes": normalize_note(row.get("notes")),
            "Lead_Status": (row.get("leadStatus") or "").strip().upper(),
        }
    # hannover-style
    return {
        "Company": (row.get("Company") or "").strip(),
        "Firstname": (row.get("Firstname") or "").strip(),
        "Lastname": (row.get("Lastname") or "").strip(),
        "E-Mail": (row.get("E-Mail") or row.get("Email") or "").strip().lower(),
        "Phone": (row.get("Phone") or "").strip(),
        "Function": (row.get("Function") or "").strip(),
        "Position": (row.get("Position") or "").strip(),
        "Industry": (row.get("Industry") or "").strip(),
        "Job Title": (row.get("Job Title") or "").strip(),
        "Companysize": (row.get("Companysize") or "").strip(),
        "Notes": normalize_note(row.get("Notes")),
        "Lead_Status": "",
    }


# --------------------------------------------------------------------------
# Filters
# --------------------------------------------------------------------------
STUDENT_DOMAIN_MARKERS = (
    ".edu", ".edu.", "schule.de", "hochschule", "uni-", ".uni.",
    "stud.", "student.", "students.", "university", "universit",
    ".ac.", ".ac.uk", "hs-", "tu-", "th-", "fh-",
)
STUDENT_ROLE_MARKERS = (
    "azubi", "apprentice", "praktikant", "trainee", "ausbildung",
    "student", "studierende", "intern ",  # "intern " not "internal"
    "phd candidate", "phd student", "master student",
)
PERSONAL_EMAIL_DOMAINS = {
    "gmail.com", "yahoo.com", "yahoo.co.uk", "yahoo.de", "yahoo.es", "yahoo.fr",
    "hotmail.com", "hotmail.de", "hotmail.co.uk", "hotmail.es", "hotmail.fr",
    "outlook.com", "outlook.de", "outlook.es", "outlook.fr",
    "gmx.de", "gmx.net", "gmx.com", "gmx.ch", "gmx.at",
    "web.de", "t-online.de", "freenet.de",
    "icloud.com", "me.com", "mac.com",
    "mail.com", "live.com", "live.de", "msn.com", "aol.com",
    "protonmail.com", "pm.me", "proton.me",
    "yandex.com", "yandex.ru", "qq.com", "163.com", "126.com",
}


def is_student(lead):
    email = (lead.get("E-Mail") or "").lower()
    role = " ".join([
        lead.get("Position") or "",
        lead.get("Function") or "",
        lead.get("Job Title") or "",
    ]).lower()
    if any(marker in email for marker in STUDENT_DOMAIN_MARKERS):
        return "student-domain"
    if any(marker in role for marker in STUDENT_ROLE_MARKERS):
        return "student-role"
    return None


def email_domain(email):
    if not email or "@" not in email:
        return ""
    return email.split("@", 1)[1].strip().lower()


def normalize_size_bucket(employees, cr_bucket):
    """Map CR/Prospector size bucket → Tiger canonical bucket.

    Canonical: "Under 10" / "10 - 100" / "100 - 1000" / "1000+".
    Prefers precise employee count when available. Falls back to parsing the
    CR bucket string. Returns "" when no size data is available.
    """
    try:
        n = int(employees) if employees not in (None, "") else None
    except (ValueError, TypeError):
        n = None
    if n is not None and n > 0:
        if n < 10:
            return "Under 10"
        if n < 100:
            return "10 - 100"
        if n < 1000:
            return "100 - 1000"
        return "1000+"

    if not cr_bucket:
        return ""
    b = str(cr_bucket).strip()
    direct = {
        "1 - 9": "Under 10",
        "10 - 49": "10 - 100",
        "50 - 249": "100 - 1000",   # straddles 10-100 & 100-1000; median ~150 → upper
        "250 - 999": "100 - 1000",
        "1000 - 4999": "1000+",
        "5000 - 9999": "1000+",
        "10000 - 49999": "1000+",
        "50000+": "1000+",
    }
    return direct.get(b, "")


def email_kind(email):
    dom = email_domain(email)
    if not dom:
        return "missing"
    if dom in PERSONAL_EMAIL_DOMAINS:
        return "personal"
    return "corporate"


def write_phase_a_summary(path, event, day, stats, unique_domains_count):
    """Human-readable Phase A summary — replaces stub xlsx."""
    title = f"{event}" + (f" Day {day}" if day else "") + " — Phase A Scaffold"
    lines = [
        title,
        "",
        f"Total rows kept:               {stats['total_kept']}",
        f"Corporate email:                {stats['corporate']}",
        f"Personal email:                 {stats['personal']}",
        f"Missing email:                  {stats['missing_email']}",
        f"Students / apprentices:         {stats['students_filtered']}",
        f"Internal/host filtered:         {stats['internal_filtered']}",
        f"Duplicates (vs prior days):     {stats['duplicates']}",
        "",
        f"Current customers:              {stats['customers_current']}",
        f"Previous customers (churned):   {stats['customers_previous']}",
        "",
        f"Unique corporate domains for CR lookup: {unique_domains_count}",
    ]
    path.write_text("\n".join(lines) + "\n")


# --------------------------------------------------------------------------
# Customer match
# --------------------------------------------------------------------------
CORP_SUFFIX_RE = re.compile(
    r"\b(inc|llc|ltd|gmbh|sa|srl|bv|nv|plc|spa|ab|oy|group|holdings?|corp|corporation|co|kg|ag|pty|s\.?a\.?|s\.?l\.?|s\.?r\.?l\.?|s\.?p\.?a\.?|s\.?a\.?s\.?|u\.?a\.?b\.?)\b",
    re.IGNORECASE,
)


def normalize_name(name):
    if not name:
        return ""
    n = name.lower()
    n = re.sub(r"[.,&/()\-]", " ", n)
    n = CORP_SUFFIX_RE.sub(" ", n)
    n = re.sub(r"\s+", " ", n).strip()
    return n


def load_customer_index(path):
    with open(path) as f:
        data = json.load(f)
    by_domain = {}
    by_name = {}
    for c in data:
        dom = (c.get("primaryDomain") or "").lower().strip()
        entry = {
            "id": c.get("id"),
            "name": c.get("name"),
            "primaryDomain": dom,
            "status": c.get("status", "Current"),  # default Current for backward-compat
        }
        if dom:
            by_domain[dom] = entry
        n = normalize_name(c.get("name") or "")
        if n:
            by_name[n] = entry
    return {"by_domain": by_domain, "by_name": by_name, "count": len(data)}


def match_customer(lead, cust_idx, cr_primary_domain=None):
    """Returns (status: 'Current'|'Previous'|'No', match_method: str)."""
    if cr_primary_domain:
        d = cr_primary_domain.lower().strip()
        if d in cust_idx["by_domain"]:
            return cust_idx["by_domain"][d]["status"], "cr_primary_domain"
    edom = email_domain(lead.get("E-Mail") or "")
    if edom and edom in cust_idx["by_domain"]:
        return cust_idx["by_domain"][edom]["status"], "email_domain"
    cname = normalize_name(lead.get("Company") or "")
    if cname and cname in cust_idx["by_name"]:
        return cust_idx["by_name"][cname]["status"], "company_name"
    return "No", ""


# --------------------------------------------------------------------------
# ICP / Warmth / Tier scoring
# --------------------------------------------------------------------------

def detect_signals(text: str, tech_strong: set, tech_med: set) -> set:
    if not text:
        return set()
    t = text.lower()
    return {kw for kw in (tech_strong | tech_med) if kw in t}


def score_icp(lead: dict, cr_fields: dict, rubric: dict) -> tuple:
    """Returns (ICP_Fit, ICP_Score, reasons_list).

    Broadens industry scan to Industry + sub_industry + about for physical-world detection.
    """
    reasons = []
    score = 0

    tech_strong = set(rubric["tech_strong"])
    tech_med = set(rubric["tech_med"])
    physical_verticals = rubric["physical_world_verticals"]
    weights = rubric["icp_score_weights"]
    thresholds = rubric["tier_thresholds"]

    industry_text = " ".join([
        lead.get("Industry") or "",
        cr_fields.get("sub_industry") or "",
        cr_fields.get("about") or "",   # new — broadens the scan
    ]).lower()
    note = (lead.get("Notes") or "").lower()
    tech_hl = cr_fields.get("tech_highlights") or []
    tech_text = " ".join(tech_hl).lower()

    signals = detect_signals(note + " " + tech_text, tech_strong, tech_med)
    strong_signals = signals & tech_strong
    med_signals = signals & tech_med

    if strong_signals:
        score += weights["strong_tech"]
        reasons.append(f"strong tech ({', '.join(sorted(strong_signals))})")
    if med_signals:
        score += weights["medium_tech"]
        reasons.append(f"adjacent tech ({', '.join(sorted(med_signals))})")

    physical = any(m in industry_text for m in physical_verticals)
    if physical:
        score += weights["physical_world"]
        reasons.append("physical-world vertical")

    emp = cr_fields.get("employees") or 0
    try:
        emp = int(emp) if emp else 0
    except (ValueError, TypeError):
        emp = 0
    if emp >= 500:
        score += weights["employees_500_plus"]
        reasons.append("500+ employees")

    v1 = cr_fields.get("v1_account_pct")
    try:
        v1 = float(v1) if v1 is not None and v1 != "" else None
    except (ValueError, TypeError):
        v1 = None
    if v1 and v1 >= 80:
        score += weights["v1_account_80_plus"]
        reasons.append(f"V1 account {int(v1)}%")

    if score >= thresholds["A"]:
        fit = "A"
    elif score >= thresholds["B"]:
        fit = "B"
    elif score >= thresholds["C"]:
        fit = "C"
    else:
        fit = "D"

    return fit, score, reasons


def score_warmth(lead: dict, cr_fields: dict, rubric: dict, icp_score: int) -> tuple:
    """Returns (warmth_int_1_to_5, reasons_list)."""
    reasons = []
    warmth = 1
    note = (lead.get("Notes") or "").lower()
    lead_status = (lead.get("Lead_Status") or "").upper()
    tech_text = " ".join(cr_fields.get("tech_highlights") or []).lower()

    tech_strong = set(rubric["tech_strong"])
    tech_med = set(rubric["tech_med"])
    hot_kw = rubric["hot_keywords"]
    warm_kw = rubric["warm_keywords"]
    heat_rules = rubric["heat_rules"]

    has_strong = bool(detect_signals(note + " " + tech_text, tech_strong, tech_med) & tech_strong)
    any_hot_kw = any(kw in note for kw in hot_kw)
    any_warm_kw = any(kw in note for kw in warm_kw)
    substantive_note = len(note) > 20

    v1 = cr_fields.get("v1_account_pct")
    try:
        v1 = float(v1) if v1 is not None and v1 != "" else 0
    except (ValueError, TypeError):
        v1 = 0

    if has_strong or lead_status in heat_rules["hot_lead_status"]:
        warmth = 5
        if has_strong:
            matched = sorted(detect_signals(note + " " + tech_text, tech_strong, tech_med) & tech_strong)
            reasons.append(f"strong tech mention ({', '.join(matched)})" if matched else "strong tech mention")
        else:
            reasons.append("SDR marked HOT")
    elif v1 >= 80 or (substantive_note and any_hot_kw):
        warmth = 4
        reasons.append("high V1 account" if v1 >= 80 else "strong note signal")
    elif lead_status in heat_rules["warm_lead_status"] or any_warm_kw:
        warmth = 3
        reasons.append("SDR marked WARM" if lead_status in heat_rules["warm_lead_status"] else "warm keyword")
    elif substantive_note:
        warmth = 2
        reasons.append("SDR captured a note")
    else:
        warmth = 1
        reasons.append("booth scan only")

    return warmth, reasons


def score_tier_and_heat(lead: dict, cr_fields: dict, rubric: dict, icp_fit: str, warmth: int) -> tuple:
    """Returns (Tier, Heat, Next_Step).

    Conversation tier REQUIRES a non-empty SDR note. A HOT/WARM lead_status
    without a note is NOT enough — the lead falls through to firmographic tier
    and is surfaced via the separate SDR_Status_Flag column.
    Tier A gate: requires physical-world vertical match.
    """
    note = (lead.get("Notes") or "").strip()
    lead_status = (lead.get("Lead_Status") or "").upper()
    industry_text = " ".join([
        lead.get("Industry") or "",
        cr_fields.get("sub_industry") or "",
        cr_fields.get("about") or "",
    ]).lower()

    physical_verticals = rubric["physical_world_verticals"]
    tech_strong = set(rubric["tech_strong"])
    tech_med = set(rubric["tech_med"])
    hot_kw = rubric["hot_keywords"]
    warm_kw = rubric["warm_keywords"]
    heat_rules = rubric["heat_rules"]
    tier_a_gate = rubric["tier_a_gate"]

    physical_match = any(m in industry_text for m in physical_verticals)

    # Conversation tier — note required
    if note:
        nl = note.lower()
        has_strong = bool(detect_signals(nl, tech_strong, tech_med) & tech_strong)
        any_hot_kw = any(kw in nl for kw in hot_kw)
        any_warm_kw = any(kw in nl for kw in warm_kw)
        if has_strong or any_hot_kw or lead_status in heat_rules["hot_lead_status"]:
            heat = "Hot"
            step = "SDR follow-up this week — explicit intent"
        elif any_warm_kw or lead_status in heat_rules["warm_lead_status"]:
            heat = "Warm"
            step = "SDR follow-up within 2 weeks — soft intent"
        else:
            heat = "Mild"
            step = "SDR follow-up — review conversation"
        return "Conversation", heat, step

    # No note — firmographic tier.
    sdr_suffix = ""
    if lead_status in heat_rules["hot_lead_status"]:
        sdr_suffix = " (SDR flagged HOT — note missing, verify before outbound)"
    elif lead_status in heat_rules["warm_lead_status"]:
        sdr_suffix = " (SDR flagged WARM — note missing)"

    tier_a_qualifies = icp_fit == "A" and (
        physical_match if tier_a_gate.get("require_physical_world", True) else True
    )
    if tier_a_qualifies:
        return "A", None, "Outbound SDR target — physical-world + strong fit" + sdr_suffix
    if icp_fit in ("A", "B"):
        return "B", None, "Nurture + personalized email sequence" + sdr_suffix
    if icp_fit == "C":
        return "C", None, "Newsletter / lightweight follow-up" + sdr_suffix
    return "D", None, "Exclude from active outreach" + sdr_suffix


# --------------------------------------------------------------------------
# Main pipeline
# --------------------------------------------------------------------------
def load_csv(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        rows = list(reader)
        return reader.fieldnames, rows


def dedupe(leads, prior_emails=None):
    prior = set(prior_emails or [])
    seen = set(prior)
    out = []
    dupes = []
    for ld in leads:
        key = ld.get("E-Mail") or (
            (ld.get("Firstname") or "") + "|" +
            (ld.get("Lastname") or "") + "|" +
            (ld.get("Company") or "")
        ).lower()
        if not key:
            out.append(ld)
            continue
        if key in seen:
            dupes.append(ld)
            continue
        seen.add(key)
        out.append(ld)
    return out, dupes


def apply_removals(leads, strip_companies, strip_email_domains=None):
    """Returns (kept_leads, removed_with_reason)."""
    strip_lower = {s.strip().lower() for s in strip_companies if s.strip()}
    strip_doms = {s.strip().lower() for s in (strip_email_domains or []) if s.strip()}
    kept = []
    removed = []
    for ld in leads:
        cname = (ld.get("Company") or "").strip().lower()
        edom = email_domain(ld.get("E-Mail") or "")
        # internal / host email-domain strip
        if edom and edom in strip_doms:
            removed.append({**ld, "_Removal_Reason": f"internal/host (@{edom})"})
            continue
        # internal / host company strip
        if cname and any(s in cname or cname in s for s in strip_lower):
            removed.append({**ld, "_Removal_Reason": f"internal/host ({ld.get('Company')})"})
            continue
        # student/trainee filter
        st = is_student(ld)
        if st:
            removed.append({**ld, "_Removal_Reason": st})
            continue
        kept.append(ld)
    return kept, removed


# --------------------------------------------------------------------------
# Output rendering
# --------------------------------------------------------------------------
OUTPUT_COLS = [
    "Company", "Firstname", "Lastname", "E-Mail", "Phone",
    "Function", "Position", "Industry", "Job Title", "Companysize",
    "Notes", "Lead_Status", "SDR_Status_Flag",
    "Email_Kind", "CR_Match_Type", "CR_Org_Domain",
    "Employees", "Size_Bucket", "Size_Bucket_Raw", "Size_Bucket_Source",
    "Revenue_Range", "Sub_Industry", "HQ", "HQ_Country",
    "V1_Account_Score_Pct", "Tech_Highlights",
    "ICP_Fit", "ICP_Score", "ICP_Reasons",
    "Warmth", "Warmth_Reasons", "Tier", "Conversation_Heat", "Next_Step",
    "Is_Customer", "Customer_Match_Method",
]

TIER_FILL = {
    "Conversation": PatternFill("solid", fgColor="FFD966"),
    "A": PatternFill("solid", fgColor="B6D7A8"),
    "B": PatternFill("solid", fgColor="D9EAD3"),
    "C": PatternFill("solid", fgColor="FFF2CC"),
    "D": PatternFill("solid", fgColor="EFEFEF"),
}
HEAT_FILL = {
    "Hot": PatternFill("solid", fgColor="E06666"),
    "Warm": PatternFill("solid", fgColor="F6B26B"),
    "Mild": PatternFill("solid", fgColor="FFE599"),
}
CURRENT_CUSTOMER_FILL = PatternFill("solid", fgColor="4A86E8")  # blue
PREVIOUS_CUSTOMER_FILL = PatternFill("solid", fgColor="F6B26B")  # orange
SDR_FLAG_FILL = {
    "HOT (no note)": PatternFill("solid", fgColor="F4B6B6"),
    "WARM (no note)": PatternFill("solid", fgColor="FDE6B8"),
}


def write_xlsx(
    event_name, day, all_leads, removed_leads, stats, out_path,
    rubric,
):
    wb = openpyxl.Workbook()

    # --- Summary ---
    ws = wb.active
    ws.title = "Summary"
    title = f"{event_name}" + (f" Day {day}" if day else "") + " — Enrichment Summary"
    ws["A1"] = title
    ws["A1"].font = Font(bold=True, size=14)
    ws.merge_cells("A1:B1")

    rows = [
        ("", ""),
        ("Total rows (kept)", stats["total_kept"]),
        ("Corporate email", stats["corporate"]),
        ("Personal email", stats["personal"]),
        ("Missing email", stats["missing_email"]),
        ("Students / apprentices filtered", stats["students_filtered"]),
        ("Internal/host companies filtered", stats["internal_filtered"]),
        ("Duplicates removed", stats["duplicates"]),
        ("Matched in Common Room (Org)", stats["cr_matched"]),
        ("Matched in Prospector only", stats["prospector_matched"]),
        ("No firmographic data", stats["no_match"]),
        ("Current customers", stats["customers_current"]),
        ("Previous customers (churned)", stats["customers_previous"]),
        ("", ""),
        ("Tier distribution:", ""),
        ("Tier Conversation", stats["tiers"].get("Conversation", 0)),
        ("Tier A", stats["tiers"].get("A", 0)),
        ("Tier B", stats["tiers"].get("B", 0)),
        ("Tier C", stats["tiers"].get("C", 0)),
        ("Tier D", stats["tiers"].get("D", 0)),
        ("", ""),
        ("Conversation heat breakdown:", ""),
        ("  Hot", stats["heat"].get("Hot", 0)),
        ("  Warm", stats["heat"].get("Warm", 0)),
        ("  Mild", stats["heat"].get("Mild", 0)),
        ("", ""),
        ("SDR status flags (note-less):", ""),
        ("  HOT (no note) — review before outbound", stats["sdr_hot_no_note"]),
        ("  WARM (no note)", stats["sdr_warm_no_note"]),
    ]
    rubric_text = rubric["summary_rubric_text"]
    rubric_rows = [
        ("Scoring rubric", ""),
        ("Tier Conversation", rubric_text["tier_conversation"]),
        ("Tier A", rubric_text["tier_a"]),
        ("Tier A gate", rubric_text["tier_a_gate"]),
        ("Tier B", rubric_text["tier_b"]),
        ("Tier C", rubric_text["tier_c"]),
        ("Tier D", rubric_text["tier_d"]),
        ("Heat: Hot", rubric_text["heat_hot"]),
        ("Heat: Warm", rubric_text["heat_warm"]),
        ("Heat: Mild", rubric_text["heat_mild"]),
        ("Is_Customer: Current", rubric_text["is_customer_current"]),
        ("Is_Customer: Previous", rubric_text["is_customer_previous"]),
        ("Is_Customer: No", rubric_text["is_customer_no"]),
    ]
    rows.extend(rubric_rows)
    for label, val in rows:
        ws.append([label, val])
    ws.column_dimensions["A"].width = 36
    ws.column_dimensions["B"].width = 80
    for row in ws.iter_rows(min_row=3):
        for c in row:
            c.alignment = Alignment(vertical="top", wrap_text=True)

    # --- All Leads ---
    ws2 = wb.create_sheet("All Leads")
    ws2.append(OUTPUT_COLS)
    for c in ws2[1]:
        c.font = Font(bold=True)
        c.fill = PatternFill("solid", fgColor="D9D2E9")
    # sort: Conversation first (by heat Hot>Warm>Mild>None), then A, B, C, D.
    # Within a tier, SDR-flagged (HOT no note > WARM no note > unflagged) floats up.
    heat_order = {"Hot": 0, "Warm": 1, "Mild": 2, None: 3}
    tier_order = {"Conversation": 0, "A": 1, "B": 2, "C": 3, "D": 4}
    flag_order = {"HOT (no note)": 0, "WARM (no note)": 1, "": 2}
    sorted_leads = sorted(
        all_leads,
        key=lambda l: (
            tier_order.get(l.get("Tier"), 5),
            heat_order.get(l.get("Conversation_Heat"), 3),
            flag_order.get(l.get("SDR_Status_Flag", ""), 2),
            -int(l.get("ICP_Score") or 0),
            -int(l.get("Warmth") or 0),
        ),
    )
    for ld in sorted_leads:
        ws2.append([ld.get(col, "") for col in OUTPUT_COLS])

    # column widths
    widths = {
        "Company": 28, "Firstname": 12, "Lastname": 14, "E-Mail": 30,
        "Notes": 40, "ICP_Reasons": 32, "Warmth_Reasons": 28,
        "Tier": 14, "Conversation_Heat": 10, "Next_Step": 40,
        "Tech_Highlights": 30, "Is_Customer": 10, "Customer_Match_Method": 18,
        "Sub_Industry": 20, "HQ": 24, "HQ_Country": 16, "CR_Match_Type": 14,
        "CR_Org_Domain": 22, "Size_Bucket": 14, "Size_Bucket_Raw": 14, "Revenue_Range": 14,
        "SDR_Status_Flag": 18, "Lead_Status": 10,
    }
    for i, col in enumerate(OUTPUT_COLS, 1):
        ws2.column_dimensions[get_column_letter(i)].width = widths.get(col, 14)
    # freeze & filter
    ws2.freeze_panes = "A2"
    ws2.auto_filter.ref = ws2.dimensions

    # tier / heat / customer / sdr-flag coloring
    tier_col = OUTPUT_COLS.index("Tier") + 1
    heat_col = OUTPUT_COLS.index("Conversation_Heat") + 1
    cust_col = OUTPUT_COLS.index("Is_Customer") + 1
    sdr_col = OUTPUT_COLS.index("SDR_Status_Flag") + 1
    for r in range(2, ws2.max_row + 1):
        tier_cell = ws2.cell(row=r, column=tier_col)
        if tier_cell.value in TIER_FILL:
            tier_cell.fill = TIER_FILL[tier_cell.value]
        heat_cell = ws2.cell(row=r, column=heat_col)
        if heat_cell.value in HEAT_FILL:
            heat_cell.fill = HEAT_FILL[heat_cell.value]
        cust_cell = ws2.cell(row=r, column=cust_col)
        if cust_cell.value == "Current":
            cust_cell.fill = CURRENT_CUSTOMER_FILL
            cust_cell.font = Font(color="FFFFFF", bold=True)
        elif cust_cell.value == "Previous":
            cust_cell.fill = PREVIOUS_CUSTOMER_FILL
            cust_cell.font = Font(bold=True)
        sdr_cell = ws2.cell(row=r, column=sdr_col)
        if sdr_cell.value in SDR_FLAG_FILL:
            sdr_cell.fill = SDR_FLAG_FILL[sdr_cell.value]
            sdr_cell.font = Font(bold=True)

    # --- Removed ---
    ws3 = wb.create_sheet("Removed")
    removed_cols = CANONICAL_COLS + ["_Removal_Reason"]
    ws3.append(removed_cols)
    for c in ws3[1]:
        c.font = Font(bold=True)
        c.fill = PatternFill("solid", fgColor="EAD1DC")
    for ld in removed_leads:
        ws3.append([ld.get(col, "") for col in removed_cols])
    ws3.column_dimensions["A"].width = 28
    ws3.column_dimensions["D"].width = 30
    ws3.column_dimensions[get_column_letter(len(removed_cols))].width = 30
    ws3.freeze_panes = "A2"

    # --- subIndustry Diagnostic ---
    ws4 = wb.create_sheet("subIndustry Diagnostic")
    ws4.append(["subIndustry", "Count", "Matched_Physical_World"])
    for c in ws4[1]:
        c.font = Font(bold=True)
        c.fill = PatternFill("solid", fgColor="FFF2CC")

    # Count subIndustry occurrences
    sub_counter = Counter()
    for ld in all_leads:
        sub = (ld.get("Sub_Industry") or "").strip()
        if sub:
            sub_counter[sub] += 1

    physical_verticals = rubric["physical_world_verticals"]
    for sub, count in sub_counter.most_common(20):
        matched = any(m in sub.lower() for m in physical_verticals)
        ws4.append([sub, count, "\u2713" if matched else "\u2717"])
    ws4.column_dimensions["A"].width = 50
    ws4.column_dimensions["B"].width = 10
    ws4.column_dimensions["C"].width = 26
    ws4.freeze_panes = "A2"

    Path(out_path).parent.mkdir(parents=True, exist_ok=True)
    wb.save(out_path)


# --------------------------------------------------------------------------
# Orchestration
# --------------------------------------------------------------------------
def run(args):
    from _common import load_rubric, load_aliases, apply_alias

    rubric = load_rubric(args.rubric)
    aliases = load_aliases(args.aliases)

    # 1. ingest
    headers, raw_rows = load_csv(args.input)
    schema = args.schema if args.schema != "auto" else detect_schema(headers)
    leads = [row_to_canonical(r, schema) for r in raw_rows]

    # 2. prior days dedupe
    prior_emails = []
    if args.prior:
        for p in args.prior:
            p_path = Path(p)
            if p_path.suffix.lower() == ".xlsx":
                wb_prior = openpyxl.load_workbook(p_path, read_only=True, data_only=True)
                # Prefer "All Leads" sheet; fall back to the first sheet
                sheet_name = "All Leads" if "All Leads" in wb_prior.sheetnames else wb_prior.sheetnames[0]
                ws_prior = wb_prior[sheet_name]
                rows_iter = ws_prior.iter_rows(values_only=True)
                header = next(rows_iter, None)
                if not header:
                    continue
                header_lower = [str(h or "").strip().lower() for h in header]
                email_col = None
                for key in ("e-mail", "email"):
                    if key in header_lower:
                        email_col = header_lower.index(key)
                        break
                if email_col is None:
                    continue
                for row in rows_iter:
                    e = (str(row[email_col] or "")).strip().lower()
                    if e:
                        prior_emails.append(e)
            else:
                with open(p_path, encoding="utf-8-sig", newline="") as f:
                    r = csv.DictReader(f)
                    for row in r:
                        e = (row.get("E-Mail") or row.get("Email") or row.get("email") or "").strip().lower()
                        if e:
                            prior_emails.append(e)

    leads, dupes = dedupe(leads, prior_emails)

    # 3. removal filters
    strip_companies = (args.strip_companies or "").split(",")
    strip_email_domains = (args.strip_email_domains or "").split(",")
    kept, removed = apply_removals(leads, strip_companies, strip_email_domains)

    # 4. customer match (before CR, so we flag even without CR data)
    cust_idx = load_customer_index(args.customers)
    for ld in kept:
        status, method = match_customer(ld, cust_idx)
        ld["Is_Customer"] = status   # Current / Previous / No
        ld["Customer_Match_Method"] = method

    # 5. extract unique domains for CR lookup (alias-canonicalized)
    unique_domains = set()
    for ld in kept:
        dom = email_domain(ld.get("E-Mail") or "")
        if dom and dom not in PERSONAL_EMAIL_DOMAINS:
            canonical = apply_alias(dom, aliases)
            unique_domains.add(canonical)

    # 6. emit domains list (always useful)
    domains_path = Path(args.output).with_suffix(".domains.json")
    with open(domains_path, "w") as f:
        json.dump({
            "event": args.event,
            "day": args.day,
            "unique_corporate_domains": sorted(unique_domains),
            "count": len(unique_domains),
        }, f, indent=2)

    # Phase A early return
    if args.stop_after == "scaffold":
        phase_a_stats = {
            "total_kept": len(kept),
            "corporate": sum(1 for ld in kept if email_kind(ld.get("E-Mail") or "") == "corporate"),
            "personal": sum(1 for ld in kept if email_kind(ld.get("E-Mail") or "") == "personal"),
            "missing_email": sum(1 for ld in kept if email_kind(ld.get("E-Mail") or "") == "missing"),
            "students_filtered": sum(1 for r in removed if "student" in r.get("_Removal_Reason", "")),
            "internal_filtered": sum(1 for r in removed if "internal" in r.get("_Removal_Reason", "")),
            "duplicates": len(dupes),
            "customers_current": sum(1 for ld in kept if ld.get("Is_Customer") == "Current"),
            "customers_previous": sum(1 for ld in kept if ld.get("Is_Customer") == "Previous"),
        }
        summary_path = Path(args.output).parent / "phase_a_summary.txt"
        write_phase_a_summary(summary_path, args.event, args.day, phase_a_stats, len(unique_domains))
        print(f"[OK] Phase A scaffold complete.")
        print(f"     Kept:    {phase_a_stats['total_kept']}")
        print(f"     Domains: {len(unique_domains)} → {domains_path}")
        print(f"     Summary: {summary_path}")
        return

    # 7. apply CR enrichment (if provided)
    enrichment = {}
    if args.cr_enrichment:
        with open(args.cr_enrichment) as f:
            enrichment = json.load(f).get("by_domain", {})

    for ld in kept:
        email = ld.get("E-Mail") or ""
        dom = email_domain(email)
        canonical = apply_alias(dom, aliases)
        ld["Email_Kind"] = email_kind(email)
        cr = enrichment.get(canonical) or {}
        source = cr.get("source", "NONE")
        if source == "CR":
            ld["CR_Match_Type"] = "CR"
        elif source == "Prospector":
            ld["CR_Match_Type"] = "Prospector"
        else:
            ld["CR_Match_Type"] = ""
        ld["CR_Org_Domain"] = cr.get("primary_domain") or (dom if cr else "")
        ld["Employees"] = cr.get("employees") or ""
        ld["Size_Bucket_Raw"] = cr.get("size_bucket") or ""
        ld["Size_Bucket"] = normalize_size_bucket(cr.get("employees"), cr.get("size_bucket"))
        ld["Size_Bucket_Source"] = cr.get("size_bucket_source") or ""
        ld["Revenue_Range"] = cr.get("revenue_range") or ""
        ld["Sub_Industry"] = cr.get("sub_industry") or ""
        hq = cr.get("hq") or ""
        ld["HQ"] = hq
        # Extract country — everything after the last comma, stripped.
        # If no comma, the whole string is the country (e.g. "Germany").
        ld["HQ_Country"] = hq.rsplit(",", 1)[-1].strip() if hq else ""
        ld["V1_Account_Score_Pct"] = cr.get("v1_account_pct") or ""
        tech_hl = cr.get("tech_highlights") or []
        ld["Tech_Highlights"] = ", ".join(tech_hl) if tech_hl else ""

        # upgrade customer match using CR primary domain
        if ld["Is_Customer"] == "No":
            cr_pd = cr.get("primary_domain") if cr else None
            status, method = match_customer(ld, cust_idx, cr_primary_domain=cr_pd)
            if status != "No":
                ld["Is_Customer"] = status
                ld["Customer_Match_Method"] = method

        # 8. scoring
        fit, sc, reasons = score_icp(ld, cr, rubric)
        ld["ICP_Fit"] = fit
        ld["ICP_Score"] = sc
        ld["ICP_Reasons"] = "; ".join(reasons) if reasons else "—"
        warmth, wreasons = score_warmth(ld, cr, rubric, sc)
        ld["Warmth"] = warmth
        ld["Warmth_Reasons"] = "; ".join(wreasons)
        tier, heat, step = score_tier_and_heat(ld, cr, rubric, fit, warmth)
        ld["Tier"] = tier
        ld["Conversation_Heat"] = heat
        ld["Next_Step"] = step
        # SDR status flag: surface note-less HOT/WARM for follow-up
        note_present = bool((ld.get("Notes") or "").strip())
        ls = (ld.get("Lead_Status") or "").upper()
        if not note_present and ls in ("HOT", "WARM"):
            ld["SDR_Status_Flag"] = f"{ls} (no note)"
        else:
            ld["SDR_Status_Flag"] = ""

    # 9. stats
    stats = {
        "total_kept": len(kept),
        "corporate": sum(1 for ld in kept if ld.get("Email_Kind") == "corporate"),
        "personal": sum(1 for ld in kept if ld.get("Email_Kind") == "personal"),
        "missing_email": sum(1 for ld in kept if ld.get("Email_Kind") == "missing"),
        "students_filtered": sum(1 for r in removed if "student" in r.get("_Removal_Reason", "")),
        "internal_filtered": sum(1 for r in removed if "internal" in r.get("_Removal_Reason", "")),
        "duplicates": len(dupes),
        "cr_matched": sum(1 for ld in kept if ld.get("CR_Match_Type") == "CR"),
        "prospector_matched": sum(1 for ld in kept if ld.get("CR_Match_Type") == "Prospector"),
        "no_match": sum(1 for ld in kept if not ld.get("CR_Match_Type")),
        "customers_current": sum(1 for ld in kept if ld.get("Is_Customer") == "Current"),
        "customers_previous": sum(1 for ld in kept if ld.get("Is_Customer") == "Previous"),
        "tiers": Counter(ld.get("Tier") for ld in kept),
        "heat": Counter(ld.get("Conversation_Heat") for ld in kept if ld.get("Conversation_Heat")),
        "sdr_hot_no_note": sum(1 for ld in kept if ld.get("SDR_Status_Flag") == "HOT (no note)"),
        "sdr_warm_no_note": sum(1 for ld in kept if ld.get("SDR_Status_Flag") == "WARM (no note)"),
    }

    # 10. write xlsx
    write_xlsx(args.event, args.day, kept, removed, stats, args.output, rubric)

    print(f"[OK] Wrote {args.output}")
    print(f"     Kept:      {stats['total_kept']}")
    print(f"     Removed:   {len(removed)}  (students {stats['students_filtered']}, internal {stats['internal_filtered']})")
    print(f"     Dupes:     {stats['duplicates']}")
    print(f"     Customers: Current={stats['customers_current']}  Previous={stats['customers_previous']}")
    print(f"     Tiers:     {dict(stats['tiers'])}")
    print(f"     Heat:      {dict(stats['heat'])}")
    print(f"     SDR flag:  HOT_no_note={stats['sdr_hot_no_note']}  WARM_no_note={stats['sdr_warm_no_note']}")
    print(f"     Domains for CR lookup: {len(unique_domains)} → {domains_path}")


def build_parser():
    p = argparse.ArgumentParser(
        description=__doc__,
        formatter_class=argparse.RawDescriptionHelpFormatter,
    )
    p.add_argument("--event", required=True, help="Event name (e.g. 'GrafanCON 2026')")
    p.add_argument("--input", required=True, help="Path to raw leads CSV")
    p.add_argument("--output", required=True, help="Path to output xlsx (or working-dir for --stop-after scaffold)")
    p.add_argument("--customers", required=True, help="Path to customers.json")
    p.add_argument("--rubric", required=True, help="Path to scoring rubric Markdown (from Tiger Den event-lead-scoring-rubric)")
    p.add_argument("--aliases", required=True, help="Path to domain alias Markdown (from Tiger Den event-lead-domain-aliases)")
    p.add_argument("--cr-enrichment", default=None, help="Path to cr_enrichment.json (Phase B)")
    p.add_argument("--day", default=None, help="Day number (optional)")
    p.add_argument("--schema", default="auto", choices=["auto", "hannover", "grafancon"])
    p.add_argument("--prior", nargs="*", default=None, help="Prior-day CSV or xlsx files for dedupe")
    p.add_argument("--strip-companies", default="", help="Comma-separated company substrings to strip")
    p.add_argument("--strip-email-domains", default="", help="Comma-separated email domains to strip")
    p.add_argument(
        "--stop-after",
        choices=["scaffold"],
        default=None,
        help="If 'scaffold', emit domains.json + phase_a_summary.txt and exit (Phase A).",
    )
    return p


if __name__ == "__main__":
    args = build_parser().parse_args()
    run(args)