"""Validate and de-duplicate parsed import rows against the DB and within the file."""

from __future__ import annotations

import re
from datetime import date

from sqlalchemy.orm import Session

from app.config import PIPELINE_STAGES
from app.models import Lead

EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")
URL_RE = re.compile(r"^(https?://)?([a-z0-9-]+\.)+[a-z]{2,}(/.*)?$", re.I)
STAGE_OK = {s.casefold() for s in PIPELINE_STAGES}


def _issue(severity: str, code: str, message: str, field: str | None = None) -> dict:
    return {"severity": severity, "code": code, "message": message, "field": field}


def _norm_email(value: str | None) -> str:
    return (value or "").strip().casefold()


def _norm_code(value: str | None) -> str:
    return (value or "").strip().casefold()


def validate_row(row: dict) -> list[dict]:
    issues: list[dict] = []
    contact = (row.get("contact_name") or "").strip()
    if not contact:
        issues.append(_issue("error", "missing_contact", "Contact name is required", "contact_name"))
    elif len(contact) < 2:
        issues.append(_issue("error", "short_contact", "Contact name is too short", "contact_name"))

    email = (row.get("client_email") or "").strip()
    if not email:
        issues.append(_issue("error", "missing_email", "Client email is required", "client_email"))
    elif not EMAIL_RE.match(email):
        issues.append(_issue("error", "invalid_email", f"Invalid client email: {email}", "client_email"))

    our = (row.get("our_email") or "").strip()
    if our and not EMAIL_RE.match(our):
        issues.append(_issue("error", "invalid_our_email", f"Invalid our email: {our}", "our_email"))

    date_added = row.get("date_added")
    if date_added is None:
        issues.append(_issue("error", "missing_date", "Date added is required", "date_added"))
    elif isinstance(date_added, date) and date_added.year < 2000:
        issues.append(_issue("warning", "old_date", f"Unusual date added: {date_added}", "date_added"))

    stage = (row.get("stage") or "").strip()
    if stage and stage.casefold() not in STAGE_OK:
        issues.append(
            _issue(
                "warning",
                "unknown_stage",
                f"Stage “{stage}” is not in the pipeline list",
                "stage",
            )
        )

    website = (row.get("website") or "").strip()
    if website:
        cleaned = website if "://" in website else f"https://{website}"
        # light check — allow almost any host-looking string
        host = cleaned.split("://", 1)[-1].split("/", 1)[0]
        if "." not in host and host.casefold() not in {"localhost"}:
            issues.append(_issue("warning", "odd_website", f"Unusual website: {website}", "website"))

    deal = row.get("deal_value")
    if deal is not None:
        try:
            if float(deal) < 0:
                issues.append(_issue("error", "negative_deal", "Deal value cannot be negative", "deal_value"))
        except (TypeError, ValueError):
            issues.append(_issue("error", "invalid_deal", "Deal value must be a number", "deal_value"))

    code = (row.get("lead_code") or "").strip()
    if code and not re.match(r"^[A-Za-z0-9][A-Za-z0-9\-_]*$", code):
        issues.append(_issue("error", "invalid_lead_code", f"Invalid lead ID: {code}", "lead_code"))

    return issues


def annotate_duplicates(db: Session, rows: list[dict]) -> list[dict]:
    """Attach validation + duplicate issues. Mutates/returns enriched row dicts."""
    existing_codes = {
        _norm_code(c): c for (c,) in db.query(Lead.lead_code).all() if c
    }
    existing_emails = {
        _norm_email(e): e for (e,) in db.query(Lead.client_email).all() if e
    }

    seen_codes: dict[str, int] = {}
    seen_emails: dict[str, int] = {}
    enriched: list[dict] = []

    for row in rows:
        item = dict(row)
        issues = validate_row(item)

        code = _norm_code(item.get("lead_code"))
        email = _norm_email(item.get("client_email"))

        if code:
            if code in existing_codes:
                issues.append(
                    _issue(
                        "error",
                        "duplicate_lead_code_db",
                        f"Lead ID “{item.get('lead_code')}” already exists in the database",
                        "lead_code",
                    )
                )
            if code in seen_codes:
                issues.append(
                    _issue(
                        "error",
                        "duplicate_lead_code_file",
                        f"Lead ID “{item.get('lead_code')}” is duplicated in this file (also row {seen_codes[code]})",
                        "lead_code",
                    )
                )
            else:
                seen_codes[code] = item.get("row_number") or 0

        if email:
            if email in existing_emails:
                issues.append(
                    _issue(
                        "error",
                        "duplicate_email_db",
                        f"Client email “{item.get('client_email')}” already exists in the database",
                        "client_email",
                    )
                )
            if email in seen_emails:
                issues.append(
                    _issue(
                        "error",
                        "duplicate_email_file",
                        f"Client email “{item.get('client_email')}” is duplicated in this file (also row {seen_emails[email]})",
                        "client_email",
                    )
                )
            else:
                seen_emails[email] = item.get("row_number") or 0

        item["issues"] = issues
        item["status"] = "invalid" if any(i["severity"] == "error" for i in issues) else "ready"
        if item["status"] == "ready" and any(i["severity"] == "warning" for i in issues):
            item["status"] = "warning"
        enriched.append(item)

    return enriched


DUP_CODES = {
    "duplicate_lead_code_db",
    "duplicate_email_db",
    "duplicate_lead_code_file",
    "duplicate_email_file",
}


def partition_rows(annotated: list[dict]) -> tuple[list[dict], list[dict], list[dict]]:
    ready: list[dict] = []
    duplicates: list[dict] = []
    invalid: list[dict] = []
    for row in annotated:
        error_codes = {
            i["code"] for i in (row.get("issues") or []) if i.get("severity") == "error"
        }
        if not error_codes:
            ready.append(row)
        elif error_codes.issubset(DUP_CODES):
            duplicates.append(row)
        else:
            invalid.append(row)
    return ready, duplicates, invalid


def summarize(annotated: list[dict]) -> dict:
    ready, duplicates, invalid = partition_rows(annotated)
    all_issues = []
    for row in annotated:
        for issue in row.get("issues") or []:
            all_issues.append(
                {
                    **issue,
                    "row_number": row.get("row_number"),
                    "lead_code": row.get("lead_code"),
                    "contact_name": row.get("contact_name"),
                    "client_email": row.get("client_email"),
                }
            )
    return {
        "total": len(annotated),
        "ready": len(ready),
        "duplicates": len(duplicates),
        "invalid": len(invalid),
        "warnings": sum(1 for r in annotated if r.get("status") == "warning"),
        "ready_rows": ready,
        "duplicate_rows": duplicates,
        "invalid_rows": invalid,
        "issues": all_issues,
    }
