"""Parse lead CSV / XLSX uploads into normalized row dicts.

Matches the Esferasoft “Leads Pipeline” sheet format:
Lead ID | Date Added | Contact Name | Email | (Our Email) | Pipeline Stage |
FU 1–7 | Notes | optional Website / Store | Deal Value ($)
"""

from __future__ import annotations

import csv
import io
import re
from datetime import date, datetime

from app.config import PIPELINE_STAGES

HEADER_ALIASES: dict[str, tuple[str, ...]] = {
    "contact_name": (
        "contact_name",
        "contact name",
        "contact",
        "name",
        "full name",
        "lead name",
    ),
    "client_email": (
        "client_email",
        "client email",
        "client's email",
        "clients email",
        "lead email",
        "customer email",
        "email",
    ),
    "our_email": (
        "our_email",
        "our email",
        "our mail",
        "sender email",
        "from email",
    ),
    "website": (
        "website",
        "website / store",
        "website/store",
        "store",
        "store url",
        "site",
        "url",
        "web",
        "company website",
    ),
    "date_added": ("date_added", "date added", "added", "created", "date"),
    "stage": ("stage", "pipeline stage", "pipeline"),
    "notes": ("notes", "note", "comments", "comment"),
    "deal_value": (
        "deal_value",
        "deal value",
        "deal value ($)",
        "value",
        "amount",
        "price",
        "deal",
    ),
    "call_scheduled": ("call_scheduled", "call scheduled", "scheduled call"),
    "lead_code": ("lead_code", "lead code", "lead id", "id"),
}

STAGE_LOOKUP = {s.casefold(): s for s in PIPELINE_STAGES}
STAGE_ALIASES = {
    "new": "New Lead",
    "new lead": "New Lead",
    "cold lead": "Potential lead",
    "warm lead": "Warm lead",
    "hot lead": "Positive lead",
    "converted": "Won",
    "replied – warm": "Warm lead",
    "replied - warm": "Warm lead",
    "closed – won": "Won",
    "closed - won": "Won",
    "call booked": "Call Scheduled",
}

WEBSITE_IN_NOTES = re.compile(
    r"\[?\s*Website\s*:\s*(https?://[^\s\]\)]+|www\.[^\s\]\)]+|[^\s\]\)]+\.[a-z]{2,}[^\s\]\)]*)\s*\]?",
    re.IGNORECASE,
)
EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")
FU_HEADER_RE = re.compile(r"^fu\s*(\d+)", re.IGNORECASE)


def _norm_header(value: str) -> str:
    text = (value or "").replace("\n", " ").replace("\r", " ")
    return re.sub(r"[\s_\-]+", " ", text.strip()).casefold()


def _is_header_row(cells: list[str]) -> bool:
    norms = {_norm_header(c) for c in cells if c}
    has_lead = "lead id" in norms or "lead code" in norms
    has_contact = "contact name" in norms or "name" in norms
    has_email = any("email" in n for n in norms)
    return has_lead and (has_contact or has_email)


def _find_header_index(matrix: list[list[str]]) -> int:
    for i, row in enumerate(matrix[:30]):
        if _is_header_row(row):
            return i
    return 0


def _map_headers(fieldnames: list[str] | None) -> dict[str, str]:
    mapping: dict[str, str] = {}
    if not fieldnames:
        return mapping

    # Prefer "Client's Email" over bare "Email" for client_email
    ordered_fields = list(HEADER_ALIASES.keys())
    used_headers: set[str] = set()

    for field in ordered_fields:
        aliases = HEADER_ALIASES[field]
        for alias in aliases:
            key = _norm_header(alias)
            for h in fieldnames:
                if h in used_headers:
                    continue
                if _norm_header(h) == key:
                    mapping[field] = h
                    used_headers.add(h)
                    break
            if field in mapping:
                break

    # Sheet format: Email (client) then blank header column = our email
    if "our_email" not in mapping and "client_email" in mapping:
        try:
            idx = fieldnames.index(mapping["client_email"])
        except ValueError:
            idx = -1
        if 0 <= idx < len(fieldnames) - 1:
            nxt = fieldnames[idx + 1]
            if not (nxt or "").strip() or _norm_header(nxt).startswith("column "):
                mapping["our_email"] = nxt
                used_headers.add(nxt)
            elif _norm_header(nxt) == "email" and nxt not in used_headers:
                # Second "Email" column = our mail (future followups layout)
                mapping["our_email"] = nxt
                used_headers.add(nxt)

    # FU columns: "FU 1\nDay 1" …
    for h in fieldnames:
        m = FU_HEADER_RE.match(_norm_header(h).replace(" ", ""))
        if not m:
            m = FU_HEADER_RE.match(_norm_header(h))
        if m:
            mapping[f"fu_{int(m.group(1))}"] = h

    return mapping


def _parse_date(raw: str | None) -> date:
    text = (raw or "").strip()
    if not text:
        return date.today()
    # Excel datetime strings sometimes include time
    text = text.split(" ")[0]
    for fmt in ("%Y-%m-%d", "%m/%d/%Y", "%d/%m/%Y", "%m-%d-%Y", "%d-%m-%Y", "%Y/%m/%d"):
        try:
            return datetime.strptime(text, fmt).date()
        except ValueError:
            continue
    return date.today()


def _parse_bool(raw: str | None) -> bool:
    text = (raw or "").strip().casefold()
    return text in {"1", "true", "yes", "y", "on"}


def _parse_deal_value(raw: str | None) -> float | None:
    text = (raw or "").strip()
    if not text:
        return None
    cleaned = re.sub(r"[,$€£\s]", "", text)
    try:
        return float(cleaned)
    except ValueError:
        return None


def _normalize_stage(raw: str | None) -> str:
    text = (raw or "").strip()
    if not text:
        return "New Lead"
    folded = text.casefold()
    if folded in STAGE_LOOKUP:
        return STAGE_LOOKUP[folded]
    if folded in STAGE_ALIASES:
        return STAGE_ALIASES[folded]
    return text


def _parse_fu_status(raw: str | None) -> str | None:
    text = (raw or "").strip().casefold()
    if not text:
        return None
    if text in {"done", "complete", "completed", "✓", "✔", "yes"}:
        return "done"
    if text in {"skipped", "skip", "n/a", "na"}:
        return "skipped"
    if text in {"pending", "todo", "to do", "open"}:
        return "pending"
    return None


def _extract_website_from_notes(notes: str | None) -> tuple[str | None, str | None]:
    if not notes:
        return None, notes
    match = WEBSITE_IN_NOTES.search(notes)
    if not match:
        return None, notes
    website = match.group(1).rstrip(".,;")
    cleaned = (notes[: match.start()] + notes[match.end() :]).strip()
    cleaned = re.sub(r"[ \t]+\n", "\n", cleaned).strip()
    return website, cleaned or notes


def _cell_to_str(value) -> str:
    if value is None:
        return ""
    if isinstance(value, datetime):
        return value.date().isoformat()
    if isinstance(value, date):
        return value.isoformat()
    return str(value).strip()


def _looks_like_email(value: str) -> bool:
    return bool(EMAIL_RE.match((value or "").strip()))


def _parse_mapped_rows(
    fieldnames: list[str] | None,
    raw_rows: list[dict],
    *,
    header_row_number: int = 1,
) -> tuple[list[dict], list[str]]:
    header_map = _map_headers(fieldnames)
    if "contact_name" not in header_map and "client_email" not in header_map:
        raise ValueError(
            "File must include Contact Name and Email columns "
            "(Leads Pipeline sheet format)."
        )

    rows: list[dict] = []
    errors: list[str] = []

    for offset, raw in enumerate(raw_rows):
        excel_row = header_row_number + 1 + offset

        def get(field: str, _raw=raw) -> str:
            col = header_map.get(field)
            if col is None:
                return ""
            return _cell_to_str(_raw.get(col))

        contact_name = get("contact_name")
        client_email = get("client_email")
        our_email = get("our_email")

        # If "Email" mapped to our mail by mistake and client empty, swap when needed
        if client_email and not our_email and not _looks_like_email(client_email):
            pass
        if not client_email and our_email and _looks_like_email(our_email):
            client_email, our_email = our_email, ""

        # Blank header our-email column may hold sender address
        if our_email and not _looks_like_email(our_email):
            our_email = ""

        if not contact_name and not client_email:
            continue
        # Skip leftover title / dashboard rows
        if contact_name and "pipeline" in contact_name.casefold() and "email marketing" in contact_name.casefold():
            continue

        notes = get("notes") or None
        website = get("website") or None
        if not website and notes:
            extracted, notes = _extract_website_from_notes(notes)
            website = extracted

        stage = _normalize_stage(get("stage"))
        call_scheduled = _parse_bool(get("call_scheduled")) or stage == "Call Scheduled"

        follow_ups: dict[int, str] = {}
        for n in range(1, 8):
            status = _parse_fu_status(get(f"fu_{n}"))
            if status:
                follow_ups[n] = status

        # Soft parse notices (hard validation happens in import_validate)
        if not contact_name:
            errors.append(f"Row {excel_row}: missing contact name")
        if not client_email:
            errors.append(f"Row {excel_row}: missing client email")
        elif not _looks_like_email(client_email):
            errors.append(f"Row {excel_row}: invalid client email “{client_email}”")

        rows.append(
            {
                "row_number": excel_row,
                "lead_code": get("lead_code") or None,
                "contact_name": contact_name,
                "client_email": client_email,
                "our_email": our_email or None,
                "website": website,
                "date_added": _parse_date(get("date_added")),
                "stage": stage,
                "deal_value": _parse_deal_value(get("deal_value")),
                "notes": notes,
                "call_scheduled": call_scheduled,
                "follow_ups": follow_ups,
            }
        )

    return rows, errors


def _matrix_from_csv(text: str) -> list[list[str]]:
    reader = csv.reader(io.StringIO(text))
    return [[_cell_to_str(c) for c in row] for row in reader]


def _rows_from_matrix(matrix: list[list[str]]) -> tuple[list[str], list[dict], int]:
    while matrix and all(not c for c in matrix[-1]):
        matrix.pop()
    if not matrix:
        raise ValueError("Spreadsheet is empty")

    header_idx = _find_header_index(matrix)
    raw_headers = matrix[header_idx]
    # Pad width from data rows (blank our-email header)
    width = max(len(raw_headers), *(len(r) for r in matrix[header_idx:]), 0)
    headers: list[str] = []
    for i in range(width):
        h = raw_headers[i] if i < len(raw_headers) else ""
        headers.append(h if h else f"Column {i + 1}")

    raw_rows: list[dict] = []
    for cells in matrix[header_idx + 1 :]:
        padded = list(cells) + [""] * (width - len(cells))
        if all(not c for c in padded):
            continue
        raw_rows.append({headers[i]: padded[i] for i in range(width)})

    return headers, raw_rows, header_idx + 1  # 1-based sheet row number


def parse_leads_csv(content: bytes | str) -> tuple[list[dict], list[str]]:
    if isinstance(content, bytes):
        text = content.decode("utf-8-sig", errors="replace")
    else:
        text = content
    headers, raw_rows, header_row = _rows_from_matrix(_matrix_from_csv(text))
    return _parse_mapped_rows(headers, raw_rows, header_row_number=header_row)


def parse_leads_xlsx(content: bytes) -> tuple[list[dict], list[str]]:
    from openpyxl import load_workbook

    wb = load_workbook(filename=io.BytesIO(content), read_only=True, data_only=True)
    try:
        preferred = None
        for name in wb.sheetnames:
            if name.strip().casefold() == "leads pipeline":
                preferred = name
                break
        ws = wb[preferred] if preferred else wb.active
        matrix = [[_cell_to_str(c) for c in row] for row in ws.iter_rows(values_only=True)]
    finally:
        wb.close()

    headers, raw_rows, header_row = _rows_from_matrix(matrix)
    return _parse_mapped_rows(headers, raw_rows, header_row_number=header_row)


def parse_leads_upload(filename: str, content: bytes) -> tuple[list[dict], list[str]]:
    name = (filename or "").lower()
    if name.endswith(".xlsx"):
        return parse_leads_xlsx(content)
    if name.endswith(".xls"):
        raise ValueError("Legacy .xls is not supported. Please save as .xlsx or .csv.")
    return parse_leads_csv(content)
