"""Import PEAC contracts from XLSX into ABS renewal backlog.

Workflow:
1. Read each row from the XLSX
2. Search Zoho Accounts by customer name (word_search)
3. Find related Contacts via word_search to get email + phone
4. Create renewal backlog leads with contract + contact data
5. Report stats: found w/ email, found no email, not found
"""

import argparse
import uuid
import sys
from datetime import datetime, date
from pathlib import Path

# Ensure the project root is on sys.path so 'state' and other packages are importable
_PROJECT_ROOT = Path(__file__).resolve().parent.parent
if str(_PROJECT_ROOT) not in sys.path:
    sys.path.insert(0, str(_PROJECT_ROOT))

# Load .env from project root
from dotenv import load_dotenv
load_dotenv(_PROJECT_ROOT / ".env")

# Pre-import Zoho client so token refresh happens at startup, not mid-search
import shared.zoho.client

import openpyxl

DATA_DIR = Path(__file__).resolve().parent.parent / "data"
DEFAULT_XLSX = DATA_DIR / "PEACcontractsGPT.xlsx"


def parse_args():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("xlsx", nargs="?", default=str(DEFAULT_XLSX),
                        help="Path to PEAC contracts XLSX")
    parser.add_argument("--dry-run", action="store_true",
                        help="Scan and report without importing")
    parser.add_argument("--dealer", default="Advantage Business Systems",
                        help="Only import rows for this dealer (default: Advantage Business Systems)")
    return parser.parse_args()


def fix_contract_term(raw) -> int:
    """Normalize term months to int, default 60."""
    if raw is None:
        return 60
    try:
        val = int(raw)
        return val if val > 0 else 60
    except (ValueError, TypeError):
        return 60


def make_term_date(raw) -> str:
    """Normalize term date to ISO date string, or '' if missing."""
    if raw is None:
        return ""
    if isinstance(raw, datetime):
        return raw.strftime("%Y-%m-%d")
    if isinstance(raw, date):
        return raw.isoformat()
    return str(raw)[:10] if raw else ""


def days_until_expiration(term_date_str: str) -> int:
    """Compute days until expiration. Negative = already expired."""
    if not term_date_str:
        return 365
    try:
        exp = datetime.strptime(term_date_str, "%Y-%m-%d")
        delta = (exp - datetime.utcnow()).days
        return delta
    except ValueError:
        return 365


def load_xlsx_rows(path: str):
    """Yield dicts for each data row in the XLSX."""
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    for row in ws.iter_rows(min_row=2, values_only=True):
        item, dealer, contract, customer, status, start, purchase, term, billing, past_due, term_date, action = (
            row[0], row[1], row[2], row[3], row[4], row[5],
            row[6], row[7], row[8], row[9], row[10], row[11]
        )
        if not customer or not contract:
            continue
        yield {
            "dealer": str(dealer or "").strip(),
            "contract_number": str(contract or "").strip(),
            "customer_name": str(customer or "").strip(),
            "purchase_option": str(purchase or "").strip(),
            "contract_term_months": fix_contract_term(term),
            "billing_cycle": str(billing or "").strip(),
            "days_past_due": int(past_due) if past_due is not None else 0,
            "term_date": make_term_date(term_date),
        }


def clean_search_name(name: str) -> str:
    """Strip entity suffixes and normalize for better Zoho matching."""
    import re
    cleaned = re.sub(
        r'\s*,?\s*(PLLC|P\.?L\.?L\.?C\.?|LLC|L\.?L\.?C\.?|INC\.?|Inc\.?|INCORPORATED|'
        r'CORP\.?|Corp\.?|CORPORATION|L\.?P\.?|LLP|L\.?L\.?P\.?|P\.?C\.?|P\.?A\.?|'
        r'LTD\.?|CO\.?)\s*$',
        '', name, flags=re.I
    )
    return cleaned.strip()


def expand_search_candidates(name: str) -> list[str]:
    """Generate search candidate strings from a customer name.

    Uses a series of transformations to improve Zoho matching:
    - Strip entity suffixes
    - Try original and cleaned forms
    - Normalize SAINT/ST
    - Try splitting merged words
    - Try first few words
    - Try key unique word
    - Try typo fix for known patterns
    """
    candidates = []
    original = name.strip()

    # 1. Strip entity suffixes
    cleaned = clean_search_name(original)
    candidates.append(cleaned)
    candidates.append(original)

    # 2. Before comma
    before_comma = original.split(",")[0].strip()
    if before_comma and len(before_comma) > 3:
        candidates.append(before_comma)
        candidates.append(clean_search_name(before_comma))

    # 3. SAINT -> ST normalization
    if original.startswith("SAINT "):
        candidates.append("ST. " + original[6:])
        candidates.append("ST " + original[6:])
    if original.startswith("ST. "):
        candidates.append("SAINT " + original[4:])
    if original.startswith("ST "):
        candidates.append("SAINT " + original[3:])

    # 4. Try splitting merged words (e.g., "Mongilloand" -> "Mongillo and")
    words = original.replace(",", " ").split()
    for i, w in enumerate(words):
        # CamelCase split or known merged words
        if w.endswith("and") and len(w) > 6:
            split_name = " ".join(words[:i]) + " " + w[:-3] + " and " + " ".join(words[i+1:])
            candidates.append(split_name.strip())

    # 5. Truncated first N words
    for n in [3, 2]:
        if len(words) > n:
            candidates.append(" ".join(words[:n]))

    # 6. Known typo corrections
    corrections = {
        "WORWICK": "WARWICK",
        "ALL PETSA": "ALL PETS",
    }
    for wrong, right in corrections.items():
        if wrong in original:
            candidates.append(original.replace(wrong, right))
            corrected_clean = clean_search_name(candidates[-1])
            if corrected_clean != candidates[-1]:
                candidates.append(corrected_clean)

    # Deduplicate while preserving order
    seen = set()
    result = []
    for n in candidates:
        key = n.lower().strip()
        if key and key not in seen:
            seen.add(key)
            result.append(n)
    return result


def find_account(customer_name: str):
    """Search Zoho Accounts by customer name. Returns account dict or None.

    Iterates through expanded search candidates and returns the first match.
    """
    from shared.zoho.client import word_search

    candidates = expand_search_candidates(customer_name)

    for n in candidates:
        accounts = word_search("Accounts", n, fields="id,Account_Name")
        if accounts:
            return accounts[0]
    return None


def find_contacts(customer_name: str) -> list[dict]:
    """Find Zoho Contacts related to this customer name.

    Returns list of contact dicts with Full_Name, Email, Phone.
    """
    from shared.zoho.client import word_search
    try:
        contacts = word_search("Contacts", customer_name,
                               fields="id,Full_Name,Email,Phone,Account_Name")
        return contacts or []
    except Exception:
        return []


def pick_best_contact(contacts: list[dict]) -> dict:
    """Pick the best contact: prefer one with email, then phone, then first.

    Returns a dict with contact_name, email, phone (all optional).
    """
    if not contacts:
        return {"contact_name": "", "email": "", "phone": ""}

    def _email(c):
        e = c.get("Email") or ""
        return e.strip()

    def _phone(c):
        p = c.get("Phone") or ""
        return p.strip()

    # Prefer first contact with an email
    for c in contacts:
        if _email(c):
            return {
                "contact_name": (c.get("Full_Name") or "").strip(),
                "email": _email(c),
                "phone": _phone(c),
            }

    # Then the first with a phone
    for c in contacts:
        if _phone(c):
            return {
                "contact_name": (c.get("Full_Name") or "").strip(),
                "email": "",
                "phone": _phone(c),
            }

    # Fall back to first contact
    c = contacts[0]
    return {
        "contact_name": (c.get("Full_Name") or "").strip(),
        "email": _email(c),
        "phone": _phone(c),
    }


def contract_exists(contract_number: str) -> bool:
    """Check if a contract already exists in the backlog."""
    from state.backlog import load_backlog
    backlog = load_backlog()
    for lead in backlog:
        if lead.get("contract_number") == contract_number:
            return True
    return False


def derive_temporal_category(days_until):
    """Derive temporal category from days until expiration."""
    if days_until is None:
        return "renewal_120"
    if days_until < -90:
        return "renewal_expired"
    elif days_until < 0:
        return "renewal_expired_recent"
    elif days_until <= 30:
        return "renewal_30"
    elif days_until <= 60:
        return "renewal_60"
    elif days_until <= 90:
        return "renewal_90"
    else:
        return "renewal_120"


def classify_renewal(days_until: int) -> dict:
    """Classify a renewal lead by contract status."""
    if days_until < -90:
        return {"classification": "expired_critical", "urgency": "critical", "framing": "lapsed"}
    elif days_until < 0:
        return {"classification": "expired_recent", "urgency": "urgent", "framing": "recently_expired"}
    elif days_until <= 30:
        return {"classification": "upcoming_urgent", "urgency": "urgent", "framing": "imminent"}
    elif days_until <= 90:
        return {"classification": "upcoming_standard", "urgency": "standard", "framing": "upcoming"}
    else:
        return {"classification": "upcoming_early", "urgency": "early", "framing": "early_notice"}


def recommend_upgrade():
    """Default upgrade recommendation when we don't have equipment data."""
    return {
        "recommended_model": "AltaLink C8245",
        "reason": "newer platform with improved features and lower cost per page",
        "meter_insight": "No meter data available — PEAC import",
        "upgrade_urgency": "optional",
    }


def build_lead(row: dict, contact: dict) -> dict:
    """Build a renewal backlog lead dict from XLSX row + Zoho contact data."""
    term_date = row["term_date"]
    days_until = days_until_expiration(term_date) if term_date else 365
    temporal = derive_temporal_category(days_until)
    classification = classify_renewal(days_until)
    upgrade = recommend_upgrade()
    now = datetime.utcnow().isoformat() + "Z"

    contact_name = contact.get("contact_name", "")
    greeting = contact_name.split()[0] if contact_name else ""

    lead_id = f"peac_{uuid.uuid4().hex[:12]}"

    lead = {
        "id": lead_id,
        "lead_type": "renewal",
        "lead_status": "active",
        "harvest_source": "csv_import",
        "harvested_at": now,
        "name": row["customer_name"],
        "contract_number": row["contract_number"],
        "dealer": row["dealer"],
        "contract_term_months": row["contract_term_months"],
        "contract_expiration": term_date,
        "days_until_expiration": days_until,
        "contact_name": contact_name,
        "greeting_name": greeting,
        "email": contact.get("email", ""),
        "phone": contact.get("phone", ""),
        "filing_date": term_date,
        "greeting_name_source": "zoho_contact" if contact_name else "",
        "temporal_category": temporal,
        "renewal_classification": classification["classification"],
        "renewal_urgency": classification["urgency"],
        "recommended_upgrade": upgrade["recommended_model"],
        "upgrade_reason": upgrade["reason"],
        "meter_insight": upgrade["meter_insight"],
        "upgrade_urgency": upgrade["upgrade_urgency"],
        "sequence": None,
        "draft_status": "none",
        "reviewed_at": "",
        "created_at": now,
        "last_updated": now,
    }
    return lead


def import_row(row: dict) -> dict:
    """Process a single XLSX row: search Zoho, build lead, save to backlog.

    Returns a dict with action, customer_name, contract_number, email, phone, reason.
    """
    customer_name = row["customer_name"]
    contract_number = row["contract_number"]

    if contract_exists(contract_number):
        return {
            "action": "skipped_duplicate",
            "customer_name": customer_name,
            "contract_number": contract_number,
            "email": "",
            "phone": "",
            "reason": "Contract already in backlog",
        }

    account = find_account(customer_name)
    if not account:
        return {
            "action": "skipped_no_account",
            "customer_name": customer_name,
            "contract_number": contract_number,
            "email": "",
            "phone": "",
            "reason": f"No Zoho Account found for '{customer_name}'",
        }

    contacts = find_contacts(customer_name)
    contact = pick_best_contact(contacts)

    lead = build_lead(row, contact)

    from state.backlog import load_backlog, save_backlog
    backlog = load_backlog()
    backlog.append(lead)
    save_backlog(backlog)

    if contact["email"]:
        return {
            "action": "imported",
            "customer_name": customer_name,
            "contract_number": contract_number,
            "email": contact["email"],
            "phone": contact["phone"],
            "reason": "Found with email",
        }
    elif contact["phone"]:
        return {
            "action": "imported_no_email",
            "customer_name": customer_name,
            "contract_number": contract_number,
            "email": "",
            "phone": contact["phone"],
            "reason": "No email, has phone — needs enrichment",
        }
    else:
        return {
            "action": "imported_no_contact",
            "customer_name": customer_name,
            "contract_number": contract_number,
            "email": "",
            "phone": "",
            "reason": "No contact info found — needs full enrichment",
        }


def main():
    args = parse_args()
    print("Starting PEAC contract import...", flush=True)

    xlsx_path = args.xlsx
    if not Path(xlsx_path).exists():
        print(f"✗ XLSX not found: {xlsx_path}")
        sys.exit(1)

    rows = list(load_xlsx_rows(xlsx_path))
    print(f"Loaded {len(rows)} rows from {xlsx_path}", flush=True)

    if args.dealer:
        rows = [r for r in rows if r["dealer"] == args.dealer or not args.dealer]
        print(f"Filtered to {len(rows)} rows for dealer: '{args.dealer}'")

    if args.dry_run:
        print("\n── Dry Run ────────────────────────────────────────────")
        for row in rows[:10]:
            print(f"  {row['customer_name'][:50]:50s} | contract={row['contract_number']:18s} | term={row['term_date']}")
        print(f"  ... ({len(rows)} total, showing first 10)")
        print()
        # Report what would be found
        import time
        stats = {"found_with_email": 0, "found_no_email": 0, "not_found": 0, "errors": 0}
        for i, row in enumerate(rows):
            if i > 0 and i % 10 == 0:
                print(f"  ... {i}/{len(rows)} checked")
                time.sleep(1)
            try:
                account = find_account(row["customer_name"])
                if not account:
                    stats["not_found"] += 1
                    continue
                contacts = find_contacts(row["customer_name"])
                contact = pick_best_contact(contacts)
                if contact["email"]:
                    stats["found_with_email"] += 1
                else:
                    stats["found_no_email"] += 1
            except Exception:
                stats["errors"] += 1

        print("\n── Dry Run Results ────────────────────────────────────")
        print(f"  Found with email: {stats['found_with_email']}")
        print(f"  Found no email:   {stats['found_no_email']}")
        print(f"  Not found:         {stats['not_found']}")
        print(f"  Errors:            {stats['errors']}")
        print(f"  Total:             {sum(stats.values())}")
        return

    results = {"imported": 0, "imported_no_email": 0, "imported_no_contact": 0,
               "skipped_duplicate": 0, "skipped_no_account": 0, "errors": 0}
    import time

    for i, row in enumerate(rows):
        if i > 0 and i % 5 == 0:
            time.sleep(1)
        try:
            result = import_row(row)
            action = result["action"]
            if action in results:
                results[action] += 1
            else:
                results[action] = 1
            if action == "imported":
                print(f"  ✓ {result['customer_name'][:45]:45s} | {result['email']}")
            elif action == "imported_no_email":
                print(f"  ~ {result['customer_name'][:45]:45s} | phone={result['phone']} (needs email)")
            elif action == "imported_no_contact":
                print(f"  ? {result['customer_name'][:45]:45s} | NO CONTACT INFO")
            elif action == "skipped_no_account":
                print(f"  ✗ {result['customer_name'][:45]:45s} | NO ZOHO ACCOUNT")
            else:
                print(f"  - {result['customer_name'][:45]:45s} | {result['reason']}")
        except Exception as e:
            results["errors"] += 1
            print(f"  ✗ ERROR {row['customer_name'][:45]:45s} | {e}")

    print("\n═══ Import Complete ════════════════════════════════════")
    print(f"  Imported (with email):     {results['imported']}")
    print(f"  Imported (no email):       {results['imported_no_email']}")
    print(f"  Imported (no contact):     {results['imported_no_contact']}")
    print(f"  Skipped (duplicate):       {results['skipped_duplicate']}")
    print(f"  Skipped (no Zoho account): {results['skipped_no_account']}")
    print(f"  Errors:                    {results['errors']}")
    print(f"  ───────────────────────────")
    print(f"  Total processed:           {sum(results.values())}")


if __name__ == "__main__":
    main()
