"""Email identity extraction for fiscal enrichment.

v4.9.26 adds an evidence layer before fiscal lookup.  The goal is not to
confirm a fiscal customer by itself; it extracts identity signals from message
bodies/signatures so the enrichment worker can prefer explicit company evidence
(Dietimport S.A. in a signature/disclaimer, for example) over a weaker domain
match returned by an external endpoint.
"""
from __future__ import annotations

import json
import os
import re
import urllib.request
import urllib.error
import unicodedata
from datetime import datetime, timezone
from typing import Any, Dict, List, Optional

from sqlalchemy import text

from app.config import settings
from app.db import engine
from app.commercial_service import normalize_fiscal_name, normalize_tax_id

_SCHEMA_READY = False

COMPANY_SUFFIX_RE = re.compile(
    r"\b([A-ZÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇ0-9][A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç0-9&.,ºª\-' ]{2,}?\s+(?:S\.?A\.?|LDA\.?|UNIPESSOAL\s*,?\s*LDA\.?|LIMITADA|SOCIEDADE\s+UNIPESSOAL|S\.A\.|SA))\b",
    flags=re.I,
)
PHONE_RE = re.compile(r"(?:\+351\s*)?(?:\d[\s.-]?){9,}")
POSTCODE_RE = re.compile(r"\b\d{4}-\d{3}\b")
URL_RE = re.compile(r"(?:https?://)?(?:www\.)?([a-z0-9][a-z0-9.-]+\.[a-z]{2,})(?:/[^\s)]*)?", flags=re.I)
EMAIL_RE = re.compile(r"[A-Z0-9._%+\-]+@[A-Z0-9.\-]+\.[A-Z]{2,}", flags=re.I)
PUBLIC_EMAIL_DOMAINS = {
    "gmail.com", "googlemail.com", "hotmail.com", "hotmail.pt", "outlook.com",
    "outlook.pt", "live.com", "msn.com", "yahoo.com", "yahoo.pt",
    "icloud.com", "me.com", "mac.com", "sapo.pt", "mail.telepac.pt",
    "aol.com", "proton.me", "protonmail.com", "pm.me",
}

# Tokens that are often hallucinated/extracted from domains and TLDs.
# They must never be treated as a company mention.
INVALID_COMPANY_MENTION_TOKENS = {
    "pt", "com", "net", "org", "www", "http", "https", "email", "mail",
    "lda", "sa", "s.a", "unipessoal",
}



def _clean(value: Any) -> str:
    return re.sub(r"\s+", " ", str(value or "")).strip()


def _remove_unexpected_unicode(value: Any) -> str:
    """Remove characters often produced by noisy LLM responses.

    Keep Portuguese/European punctuation and letters, but remove CJK/Hangul/Kana
    artifacts such as the observed ``Luis Roch游戏副本a`` case.
    """
    out: List[str] = []
    for ch in str(value or ""):
        if not ch:
            continue
        name = unicodedata.name(ch, "")
        if any(token in name for token in ("CJK", "HIRAGANA", "KATAKANA", "HANGUL", "BOPOMOFO")):
            continue
        # Drop control/private characters; keep normal letters, numbers, spaces and punctuation.
        if unicodedata.category(ch).startswith(("C",)):
            continue
        out.append(ch)
    return _clean("".join(out))


def _json(value: Any) -> str:
    return json.dumps(value or {}, ensure_ascii=False, default=str)


def _now_iso() -> str:
    return datetime.now(timezone.utc).isoformat()


def _domain_from_email(email: Any) -> str:
    email_value = _clean(email).lower()
    if "@" not in email_value:
        return ""
    return normalize_domain(email_value.rsplit("@", 1)[1])


def normalize_domain(value: Any) -> str:
    raw = _clean(value).lower()
    if not raw:
        return ""
    raw = raw.replace("https://", "").replace("http://", "")
    raw = raw.split("/", 1)[0].split("?", 1)[0].split("#", 1)[0]
    raw = raw.strip(". ")
    if raw.startswith("www."):
        raw = raw[4:]
    return raw


def _looks_like_domain_token(value: Any) -> bool:
    v = _clean(value).lower().strip(" ,.;:-")
    if not v:
        return False
    if v in INVALID_COMPANY_MENTION_TOKENS:
        return True
    if re.fullmatch(r"[a-z]{2,3}", v):
        return True
    # Full domains/hostnames are website evidence, not company names.
    if "." in v and normalize_domain(v) == v and re.search(r"\.[a-z]{2,}$", v):
        return True
    return False


def is_plausible_company_mention(value: Any) -> bool:
    v = _clean(value).strip(" ,.;:-")
    if not v or _looks_like_domain_token(v):
        return False
    normalized = normalize_fiscal_name(v) or v.casefold()
    if not normalized or len(normalized) < 4:
        return False
    if normalized in INVALID_COMPANY_MENTION_TOKENS:
        return False
    if re.fullmatch(r"[a-z]{2,3}", normalized):
        return False
    # Avoid bare legal suffixes and tiny fragments extracted from emails/domains.
    meaningful_tokens = [t for t in normalized.split() if len(t) >= 4 and t not in {"unipessoal", "limitada"}]
    if not meaningful_tokens and not re.search(r"\b(?:lda|s\.?a|unipessoal|limitada)\b", v, flags=re.I):
        return False
    return True


def _dedupe(values: List[str]) -> List[str]:
    out: List[str] = []
    seen = set()
    for value in values:
        v = _clean(value).strip(" ,.;:-")
        if not v:
            continue
        key = normalize_fiscal_name(v) or v.casefold()
        if key not in seen:
            seen.add(key)
            out.append(v)
    return out


COMPANY_NOISE_WORDS = {
    "anexos", "aviso", "confidencial", "copiar", "destinatario", "destinatário",
    "distribuir", "emissor", "exclusivo", "mensagem", "opinioes", "opiniões",
    "recebeu", "uso", "utilizar", "informacao", "informação", "ficheiro",
    "responsavel", "responsável", "solicitada", "eliminacao", "eliminação",
    "confirmacao", "confirmação", "tratamento", "dados", "contacto", "breve",
}
COMPANY_BAD_START_RE = re.compile(
    r"^(?:dos|das|do|da|de|obter|informamos|informamos,|enquanto|tacto|contacto|entramos|entraremos|esta|este|esses|essas)\b",
    flags=re.I,
)
VALEDICTION_RE = re.compile(r"^(?:com os melhores cumprimentos|melhores cumprimentos|cumprimentos|obrigad[ao]s?|boa tarde|bom dia|exmos?\.?)\b", flags=re.I)
BAD_PERSON_RE = re.compile(
    r"^(?:enviado do meu|sent from my|assinado por|aviso|disclaimer|cid:|image|logo|telefone|telem|tel\.?|email|e-mail)\b",
    flags=re.I,
)
ROLE_OR_SECTION_RE = re.compile(r"\b(?:engenheiro|respons[aá]vel|departamento|manuten[cç][aã]o|telem|tel|telefone|aviso|rua|avenida|morada|nif|email)\b", flags=re.I)
LEGAL_SUFFIX = r"(?:[Ss]\.?\s*[Aa]\.?|[Ss][Aa]|[Ll][Dd][Aa]\.?|[Uu]nipessoal\s*,?\s*[Ll][Dd][Aa]\.?|[Ll]imitada|[Ss]ociedade\s+[Uu]nipessoal)"
LEGAL_COMPANY_RE = re.compile(
    rf"\b([A-ZÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇ0-9][A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç0-9&.,ºª\-' ]{{0,75}}?\s*,?\s*{LEGAL_SUFFIX})\b"
)
ADDRESS_RE = re.compile(
    r"\b((?:Rua|R\.|Avenida|Av\.|Estrada|Travessa|Largo|Pra[cç]a|Praceta|Alameda)\s+.{3,180}?\b\d{4}-\d{3}\s+[A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç .'-]+)",
    flags=re.I | re.S,
)


def _sanitize_company_mention(value: Any) -> str:
    """Return a concise plausible company mention.

    This is deliberately stricter than the LLM.  Provider responses and regex
    can include entire GDPR/legal paragraphs; fiscal enrichment should only see
    short organization labels, for example ``SOPAC, S.A`` and not the whole
    sentence containing it.
    """
    v = _remove_unexpected_unicode(value).strip(" ,;:-")
    if not v or _looks_like_domain_token(v):
        return ""

    # Normalize common punctuation spacing before trying to extract a legal name.
    v = re.sub(r"\s+([,.;:])", r"\1", v)
    v = re.sub(r"([,.;:])(?=\S)", r"\1 ", v)
    v = _clean(v).strip(" ,;:-")

    def _tidy_company_name(name: str) -> str:
        name = _clean(name).strip(" ,;:-.")
        protected = re.sub(r"\b([Ss])\.?\s*([Aa])\.?\b", r"\1__DOT__\2__DOT__", name)
        sentence_parts = [part.strip(" ,;:-.") for part in re.split(r"\.\s+", protected) if part.strip(" ,;:-.")]
        if len(sentence_parts) > 1:
            protected = sentence_parts[-1]
        name = protected.replace("__DOT__", ".")
        name = re.sub(r"\b[Ss]\.?\s*[Aa]\.?\b", "S.A", name)
        name = re.sub(r"\b[Ll][Dd][Aa]\.?\b", "Lda", name)
        return name

    candidates = []
    for match in LEGAL_COMPANY_RE.finditer(v):
        candidate = _tidy_company_name(match.group(1))
        if candidate:
            candidates.append(candidate)

    # Specific tail found in legal disclaimers: "... da Dietimport S.A.".
    tail = re.search(rf"\b(?:da|de|do|pela|pelo|responsável é a|responsavel e a|é a|e a)\s+(.+?{LEGAL_SUFFIX})\.?$", v, flags=re.I)
    if tail:
        candidate = _tidy_company_name(tail.group(1))
        extracted = [m.group(1).strip(" ,;:-.") for m in LEGAL_COMPANY_RE.finditer(candidate)]
        candidates.extend(extracted or [candidate])

    if candidates:
        # Prefer the shortest clean candidate, because long ones are usually
        # paragraphs ending in a company suffix.
        clean_candidates = []
        for candidate in candidates:
            c = _tidy_company_name(candidate)
            words = c.split()
            normalized_words = set((normalize_fiscal_name(c) or c.casefold()).replace(".", " ").split())
            if not c or len(c) > 90 or len(words) > 8:
                continue
            if normalized_words & COMPANY_NOISE_WORDS:
                continue
            if COMPANY_BAD_START_RE.search(c):
                continue
            clean_candidates.append(c)
        if clean_candidates:
            return sorted(clean_candidates, key=len)[0]

    words = v.split()
    normalized_words = set((normalize_fiscal_name(v) or v.casefold()).replace(".", " ").split())
    noisy = bool(normalized_words & COMPANY_NOISE_WORDS)
    if len(v) > 80 or len(words) > 8 or noisy or COMPANY_BAD_START_RE.search(v):
        return ""

    # Avoid returning full person names as companies.  Single/two-token brand
    # mentions such as "Inkey", "Coimpack", "Badoni" are still allowed.
    if BAD_PERSON_RE.search(v) or VALEDICTION_RE.search(v):
        return ""
    tidied = _tidy_company_name(v)
    if not is_plausible_company_mention(tidied):
        return ""
    return tidied


def _sanitize_person_name(value: Any) -> str:
    v = _remove_unexpected_unicode(value).strip(" ,.;:-")
    if not v:
        return ""
    v = re.sub(r"\b(?:Sr\.?|Sra\.?|Dr\.?|Dra\.?)\s+", "", v, flags=re.I).strip()
    if not v or "@" in v or any(ch.isdigit() for ch in v):
        return ""
    if BAD_PERSON_RE.search(v) or VALEDICTION_RE.search(v):
        return ""
    if len(v) > 80 or len(v.split()) > 7:
        return ""
    if re.search(r"\b(?:LDA|S\.?A\.?|UNIPESSOAL|LIMITADA|AVISO|CONFIDENCIAL)\b", v, flags=re.I):
        return ""
    return v


def _is_person_name_candidate(value: Any) -> bool:
    v = _sanitize_person_name(value)
    if not v or "@" in v or any(ch.isdigit() for ch in v):
        return False
    if VALEDICTION_RE.search(v) or ROLE_OR_SECTION_RE.search(v):
        return False
    words = v.split()
    if not (2 <= len(words) <= 5):
        return False
    # Require at least two human-looking capitalized tokens.
    capitalized = [w for w in words if re.match(r"^[A-ZÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇ][A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç'.-]+$", w)]
    return len(capitalized) >= 2


def _extract_address(text_value: str, postcodes: List[str]) -> str:
    compact = _clean(text_value)
    match = ADDRESS_RE.search(compact)
    if match:
        address = _clean(match.group(1)).strip(" ,.;")
        address = re.split(r"\b(?:Telem|Tel|Telefone|Phone|Email|E-mail|AVISO)\b", address, maxsplit=1, flags=re.I)[0]
        return _clean(address).strip(" ,.;")
    if not postcodes:
        return ""
    lines = [_clean(x) for x in text_value.splitlines() if _clean(x)]
    for idx, line in enumerate(lines):
        if any(pc in line for pc in postcodes):
            before = lines[idx - 1] if idx > 0 and re.search(r"\b(?:Rua|R\.|Avenida|Av\.|Estrada|Travessa|Largo|Pra[cç]a|Praceta|Alameda)\b", lines[idx - 1], flags=re.I) else ""
            candidate = _clean((before + " " + line).strip())
            street = re.search(r"\b(?:Rua|R\.|Avenida|Av\.|Estrada|Travessa|Largo|Pra[cç]a|Praceta|Alameda)\b.*", candidate, flags=re.I)
            return _clean(street.group(0) if street else candidate).strip(" ,.;")
    return ""


def ensure_email_identity_schema() -> None:
    global _SCHEMA_READY
    if _SCHEMA_READY:
        return
    with engine.begin() as conn:
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS email_identity_extractions (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                opportunity_id UUID REFERENCES opportunities(id) ON DELETE CASCADE,
                task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
                message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
                conversation_id TEXT,
                contact_id TEXT,
                email TEXT,
                domain TEXT,
                person_name TEXT,
                company_mentions JSONB NOT NULL DEFAULT '[]'::jsonb,
                address TEXT,
                phones JSONB NOT NULL DEFAULT '[]'::jsonb,
                websites JSONB NOT NULL DEFAULT '[]'::jsonb,
                extraction_method TEXT NOT NULL DEFAULT 'regex',
                confidence NUMERIC(5,2) NOT NULL DEFAULT 0,
                evidence JSONB NOT NULL DEFAULT '[]'::jsonb,
                raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """))
        for stmt in [
            "ALTER TABLE email_identity_extractions ADD COLUMN IF NOT EXISTS confidence NUMERIC(5,2) NOT NULL DEFAULT 0",
            "ALTER TABLE email_identity_extractions ADD COLUMN IF NOT EXISTS extraction_method TEXT NOT NULL DEFAULT 'regex'",
            "CREATE INDEX IF NOT EXISTS idx_email_identity_extractions_opp ON email_identity_extractions(opportunity_id, updated_at DESC)",
            "CREATE INDEX IF NOT EXISTS idx_email_identity_extractions_domain ON email_identity_extractions(domain)",
        ]:
            conn.execute(text(stmt))
    _SCHEMA_READY = True


def extract_first_json_object(raw: str) -> str:
    s = str(raw or "").strip()
    if s.startswith("```"):
        lines = s.splitlines()
        if lines and lines[0].strip().startswith("```"):
            lines = lines[1:]
        if lines and lines[-1].strip().startswith("```"):
            lines = lines[:-1]
        s = "\n".join(lines).strip()
    start = s.find("{")
    if start == -1:
        raise ValueError("no JSON object found")
    in_string = False
    escaped = False
    depth = 0
    for i in range(start, len(s)):
        ch = s[i]
        if escaped:
            escaped = False
            continue
        if ch == "\\":
            escaped = True
            continue
        if ch == '"':
            in_string = not in_string
            continue
        if in_string:
            continue
        if ch == "{":
            depth += 1
        elif ch == "}":
            depth -= 1
            if depth == 0:
                return s[start:i + 1]
    raise ValueError("incomplete JSON object")


def _regex_identity(text_value: str, *, email: str = "", subject: str = "") -> Dict[str, Any]:
    text_value = str(text_value or "")
    emails = EMAIL_RE.findall(text_value)
    if email:
        emails.insert(0, email)
    domains = [normalize_domain(m.group(1)) for m in URL_RE.finditer(text_value)]
    for e in emails:
        d = _domain_from_email(e)
        if d:
            domains.insert(0, d)

    company_mentions: List[str] = []
    for match in COMPANY_SUFFIX_RE.finditer(text_value):
        cleaned_company = _sanitize_company_mention(match.group(1))
        if cleaned_company:
            company_mentions.append(cleaned_company)

    # Portuguese disclaimers often contain "da <Empresa>" near the organization.
    disclaimer_match = re.search(
        r"opini[oõ]es emitidas.+?\b(?:da|de|do)\s+([A-ZÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇ][A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç0-9&.,ºª\-' ]{2,}?\s+(?:S\.?A\.?|LDA\.?|LIMITADA))",
        text_value,
        flags=re.I | re.S,
    )
    if disclaimer_match:
        cleaned_company = _sanitize_company_mention(disclaimer_match.group(1))
        if cleaned_company:
            company_mentions.append(cleaned_company)

    # Marketing/reply subjects often contain "para a <Empresa>". Treat this as
    # a weak mention useful for review, not as fiscal confirmation by itself.
    subject_company = re.search(
        r"\bpara\s+(?:a|o|à|ao)\s+([A-ZÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇ0-9][A-Za-zÁÀÂÃÉÈÊÍÌÎÓÒÔÕÚÙÛÇáàâãéèêíìîóòôõúùûç0-9&.,ºª\-' ]{2,60})$",
        _clean(subject),
        flags=re.I,
    )
    if subject_company:
        subject_name = _sanitize_company_mention(subject_company.group(1))
        if subject_name:
            company_mentions.append(subject_name)

    phones = [m.group(0).strip() for m in PHONE_RE.finditer(text_value)]
    postcodes = POSTCODE_RE.findall(text_value)
    address = _extract_address(text_value, postcodes)

    person_name = ""
    lines = [_clean(x) for x in text_value.splitlines() if _clean(x)]
    for idx, line in enumerate(lines[:30]):
        if re.search(r"cumprimentos|obrigad", line, flags=re.I):
            for candidate in lines[idx + 1: idx + 7]:
                if _is_person_name_candidate(candidate):
                    person_name = _sanitize_person_name(candidate)
                    break
            if person_name:
                break

    evidence: List[str] = []
    if company_mentions:
        evidence.append("empresa mencionada no corpo/assinatura: " + company_mentions[0])
    if address:
        evidence.append("morada extraída da assinatura")
    if phones:
        evidence.append("telefone extraído da assinatura")

    confidence = 0.0
    if company_mentions:
        confidence += 0.55
    if domains:
        confidence += 0.15
    if address:
        confidence += 0.15
    if phones:
        confidence += 0.10
    if person_name:
        confidence += 0.05

    return {
        "person_name": person_name,
        "company_mentions": _dedupe(company_mentions),
        "address": address,
        "phones": _dedupe(phones),
        "websites": _dedupe(domains),
        "email": _clean(email or (emails[0] if emails else "")).lower(),
        "domain": normalize_domain(domains[0]) if domains else _domain_from_email(email),
        "subject": subject,
        "confidence": min(round(confidence, 2), 0.95),
        "evidence": evidence,
        "method": "regex",
    }


def _llm_enabled() -> bool:
    return bool(getattr(settings, "openrouter_api_key", ""))


def _llm_timeout_seconds() -> int:
    try:
        value = os.getenv("EMAIL_IDENTITY_LLM_TIMEOUT_SECONDS") or getattr(settings, "email_identity_llm_timeout_seconds", 20)
        return max(5, min(60, int(value)))
    except Exception:
        return 20


def _llm_max_body_chars() -> int:
    try:
        value = os.getenv("EMAIL_IDENTITY_LLM_MAX_BODY_CHARS") or getattr(settings, "email_identity_llm_max_body_chars", 3500)
        return max(1000, min(12000, int(value)))
    except Exception:
        return 3500


def _llm_model(model_override: str = "") -> str:
    return (
        _clean(model_override)
        or os.getenv("EMAIL_IDENTITY_LLM_MODEL")
        or getattr(settings, "email_identity_llm_model", "")
        or getattr(settings, "openrouter_model", "")
    )


def _llm_fallback_model() -> str:
    return (
        os.getenv("EMAIL_IDENTITY_LLM_FALLBACK_MODEL")
        or getattr(settings, "email_identity_llm_fallback_model", "")
        or ""
    )


def _is_public_domain(domain: Any) -> bool:
    return normalize_domain(domain) in PUBLIC_EMAIL_DOMAINS


def _identity_needs_fallback(identity: Dict[str, Any]) -> bool:
    companies = identity.get("company_mentions") or []
    domain = normalize_domain(identity.get("domain"))
    confidence = float(identity.get("confidence") or 0)
    if identity.get("_timeout") or identity.get("_error"):
        return True
    if not companies and domain and not _is_public_domain(domain):
        return True
    if not companies and confidence < 0.65:
        return True
    return False


def _call_llm_identity(text_value: str, *, email: str = "", subject: str = "", model_override: str = "") -> Optional[Dict[str, Any]]:
    if not _llm_enabled() or not text_value.strip():
        return None
    text_for_llm = str(text_value or "")[:_llm_max_body_chars()]
    prompt = f"""Extrai identidade de um email B2B para reconciliação fiscal. Não confirmes cliente fiscal; apenas extrai evidências explícitas.

Devolve apenas JSON válido com este formato:
{{
  "person_name": "",
  "person_role": "",
  "department": "",
  "company_mentions": [],
  "address": "",
  "phones": [],
  "websites": [],
  "nif_candidates": [],
  "intent_hint": "",
  "confidence": 0.0,
  "evidence": []
}}

Regras:
- company_mentions deve conter apenas nomes curtos de empresas/organizações, não frases completas.
- Não copies parágrafos legais/GDPR para company_mentions. Extrai só o nome, por exemplo "SOPAC, S.A".
- Não uses o domínio do email para inventar empresa.
- Se a empresa aparecer só no assunto tipo "para a Empresa X", podes incluir Empresa X, mas não inventes NIF.
- Se aparecer aviso legal tipo “opiniões ... da Empresa X”, inclui apenas Empresa X como company_mentions.
- person_name nunca deve ser "Enviado do meu Galaxy", valediction, cargo ou disclaimer.
- evidence deve citar fragmentos curtos do email.

Assunto: {subject or ''}
Email origem: {email or ''}

Mensagem:
{text_for_llm}
"""
    payload = {
        "model": _llm_model(model_override),
        "messages": [
            {"role": "system", "content": "És um extrator JSON rigoroso de identidade empresarial em emails B2B."},
            {"role": "user", "content": prompt},
        ],
        "temperature": 0,
        "max_tokens": 500,
    }
    request = urllib.request.Request(
        getattr(settings, "openrouter_url", "https://openrouter.ai/api/v1/chat/completions"),
        data=json.dumps(payload, ensure_ascii=False).encode("utf-8"),
        method="POST",
        headers={
            "Authorization": f"Bearer {getattr(settings, 'openrouter_api_key', '')}",
            "Content-Type": "application/json",
        },
    )
    try:
        with urllib.request.urlopen(request, timeout=_llm_timeout_seconds()) as response:  # noqa: S310 configured API
            raw = json.loads(response.read().decode("utf-8"))
        content = raw["choices"][0]["message"].get("content") or ""
        data = json.loads(extract_first_json_object(content))
        if isinstance(data, dict):
            data["method"] = "llm"
            data["llm_model"] = _llm_model(model_override)
            data["raw_llm_content"] = content[:1000]
            return data
    except Exception:
        return None
    return None


def _finalize_identity(data: Dict[str, Any]) -> Dict[str, Any]:
    finalized = dict(data)
    finalized["person_name"] = _sanitize_person_name(finalized.get("person_name"))

    companies: List[str] = []
    for item in finalized.get("company_mentions") or []:
        cleaned = _sanitize_company_mention(item)
        if cleaned:
            companies.append(cleaned)
    finalized["company_mentions"] = _dedupe(companies)

    phones = [_clean(x) for x in finalized.get("phones") or [] if _clean(x)]
    finalized["phones"] = _dedupe(phones)
    websites = [normalize_domain(x) for x in finalized.get("websites") or [] if normalize_domain(x)]
    finalized["websites"] = _dedupe(websites)

    email_domain = _domain_from_email(finalized.get("email"))
    if email_domain:
        finalized["domain"] = email_domain
    else:
        finalized["domain"] = normalize_domain(finalized.get("domain"))

    try:
        confidence = float(finalized.get("confidence") or 0)
    except Exception:
        confidence = 0.0
    if not finalized["company_mentions"]:
        # Identity may be good for a person, but fiscal identity remains weak.
        confidence = min(confidence, 0.45)
    finalized["confidence"] = round(max(0.0, min(confidence, 0.98)), 2)
    return finalized


def merge_identity(regex_data: Dict[str, Any], llm_data: Optional[Dict[str, Any]]) -> Dict[str, Any]:
    if not llm_data:
        return _finalize_identity(regex_data)
    merged = dict(regex_data)
    for key in ("person_name", "person_role", "department", "address", "intent_hint"):
        if _clean(llm_data.get(key)):
            merged[key] = _clean(llm_data.get(key))
    for key in ("company_mentions", "phones", "websites", "nif_candidates", "evidence"):
        values = []
        # For company names, prefer the LLM explicit extraction and then add
        # sanitized regex fallback values. This avoids showing a long disclaimer
        # paragraph before the actual company name.
        sources = (llm_data, regex_data) if key == "company_mentions" else (regex_data, llm_data)
        for source in sources:
            if isinstance(source.get(key), list):
                for item in source.get(key):
                    value = _sanitize_company_mention(item) if key == "company_mentions" else str(item)
                    if value:
                        values.append(value)
        merged[key] = _dedupe(values)
    merged["confidence"] = max(float(regex_data.get("confidence") or 0), float(llm_data.get("confidence") or 0))
    merged["method"] = "regex+llm"
    merged["llm_model"] = llm_data.get("llm_model") or _llm_model()
    merged["raw_llm_content"] = llm_data.get("raw_llm_content")
    return _finalize_identity(merged)


def extract_email_identity(text_value: str, *, email: str = "", subject: str = "", use_llm: bool = True) -> Dict[str, Any]:
    regex_data = _regex_identity(text_value, email=email, subject=subject)
    llm_data = _call_llm_identity(text_value, email=email, subject=subject) if use_llm else None
    identity = merge_identity(regex_data, llm_data)

    fallback_model = _llm_fallback_model()
    first_model = _clean(identity.get("llm_model") or _llm_model())
    if use_llm and fallback_model and fallback_model != first_model and _identity_needs_fallback(identity):
        fallback_data = _call_llm_identity(text_value, email=email, subject=subject, model_override=fallback_model)
        fallback_identity = merge_identity(regex_data, fallback_data) if fallback_data else identity
        if (fallback_identity.get("company_mentions") and not identity.get("company_mentions")) or float(fallback_identity.get("confidence") or 0) > float(identity.get("confidence") or 0):
            fallback_identity["fallback_used"] = True
            fallback_identity["fallback_from_model"] = first_model
            identity = fallback_identity
    return identity


def _latest_message_for_opportunity(opportunity_id: str) -> Optional[Dict[str, Any]]:
    with engine.begin() as conn:
        row = conn.execute(text("""
            SELECT
                t.id::text AS task_id,
                t.message_id::text AS message_id,
                t.conversation_id,
                t.contact_id,
                COALESCE(NULLIF(m.clean_body, ''), NULLIF(m.raw_body, '')) AS body,
                COALESCE(
                    NULLIF(m.metadata->>'subject', ''),
                    NULLIF(re.payload->'conversation'->'additional_attributes'->>'mail_subject', ''),
                    NULLIF(re.payload->'content_attributes'->'email'->>'subject', ''),
                    NULLIF(re.payload->'conversation'->'messages'->0->'content_attributes'->'email'->>'subject', '')
                ) AS subject,
                COALESCE(
                    NULLIF(re.payload->'sender'->>'email', ''),
                    NULLIF(re.payload->'conversation'->'meta'->'sender'->>'email', ''),
                    NULLIF(re.payload->'conversation'->'contact_inbox'->>'source_id', '')
                ) AS event_email
            FROM tasks t
            LEFT JOIN messages m ON m.id = t.message_id
            LEFT JOIN raw_events re ON re.id = t.raw_event_id
            WHERE t.opportunity_id = CAST(:opportunity_id AS UUID)
            ORDER BY t.created_at DESC
            LIMIT 1
        """), {"opportunity_id": opportunity_id}).mappings().first()
    return dict(row) if row and row.get("body") else None


def upsert_identity_extraction_for_opportunity(opportunity_id: str, *, use_llm: bool = True) -> Optional[Dict[str, Any]]:
    ensure_email_identity_schema()
    msg = _latest_message_for_opportunity(opportunity_id)
    if not msg:
        return None
    identity = extract_email_identity(
        msg.get("body") or "",
        email=msg.get("event_email") or "",
        subject=msg.get("subject") or "",
        use_llm=use_llm,
    )
    params = {
        "opportunity_id": opportunity_id,
        "task_id": msg.get("task_id"),
        "message_id": msg.get("message_id"),
        "conversation_id": msg.get("conversation_id"),
        "contact_id": msg.get("contact_id"),
        "email": identity.get("email") or msg.get("event_email"),
        "domain": normalize_domain(identity.get("domain")),
        "person_name": identity.get("person_name"),
        "company_mentions": _json(identity.get("company_mentions") or []),
        "address": identity.get("address"),
        "phones": _json(identity.get("phones") or []),
        "websites": _json(identity.get("websites") or []),
        "method": identity.get("method") or "regex",
        "confidence": float(identity.get("confidence") or 0),
        "evidence": _json(identity.get("evidence") or []),
        "raw_payload": _json(identity),
    }
    with engine.begin() as conn:
        row = conn.execute(text("""
            INSERT INTO email_identity_extractions (
                opportunity_id, task_id, message_id, conversation_id, contact_id, email, domain,
                person_name, company_mentions, address, phones, websites, extraction_method,
                confidence, evidence, raw_payload, updated_at
            ) VALUES (
                CAST(:opportunity_id AS UUID), CAST(:task_id AS UUID), CAST(:message_id AS UUID),
                :conversation_id, :contact_id, :email, :domain, :person_name,
                CAST(:company_mentions AS JSONB), :address, CAST(:phones AS JSONB),
                CAST(:websites AS JSONB), :method, :confidence, CAST(:evidence AS JSONB),
                CAST(:raw_payload AS JSONB), now()
            )
            RETURNING id::text, opportunity_id::text, task_id::text, message_id::text,
                      conversation_id, contact_id, email, domain, person_name,
                      company_mentions, address, phones, websites, extraction_method,
                      confidence, evidence, raw_payload, created_at, updated_at
        """), params).mappings().first()
    return dict(row) if row else None


def _normalize_stored_identity(identity: Optional[Dict[str, Any]]) -> Optional[Dict[str, Any]]:
    """Apply current quality filters to identities already stored in DB.

    Older rows may contain noisy company_mentions such as ``pt`` extracted from
    domains/TLDs.  The UI and fiscal review must not keep showing or matching
    those stale tokens just because they were saved before the stricter filters.
    """
    if not identity:
        return identity
    normalized = dict(identity)
    companies: List[str] = []
    invalid: List[str] = []
    for item in normalized.get("company_mentions") or []:
        cleaned = _sanitize_company_mention(item)
        if cleaned and is_plausible_company_mention(cleaned):
            companies.append(cleaned)
        elif _clean(item):
            invalid.append(_clean(item))
    normalized["company_mentions"] = _dedupe(companies)
    if invalid:
        raw_payload = dict(normalized.get("raw_payload") or {}) if isinstance(normalized.get("raw_payload"), dict) else {}
        raw_payload["filtered_invalid_company_mentions"] = _dedupe(invalid)
        normalized["raw_payload"] = raw_payload
    try:
        confidence = float(normalized.get("confidence") or 0)
    except Exception:
        confidence = 0.0
    if not normalized["company_mentions"]:
        confidence = min(confidence, 0.45)
    normalized["confidence"] = round(max(0.0, min(confidence, 0.98)), 2)
    return normalized


def latest_identity_for_opportunity(opportunity_id: str) -> Optional[Dict[str, Any]]:
    ensure_email_identity_schema()
    with engine.begin() as conn:
        row = conn.execute(text("""
            SELECT id::text, opportunity_id::text, task_id::text, message_id::text,
                   conversation_id, contact_id, email, domain, person_name, company_mentions,
                   address, phones, websites, extraction_method, confidence, evidence,
                   raw_payload, created_at, updated_at
            FROM email_identity_extractions
            WHERE opportunity_id = CAST(:opportunity_id AS UUID)
            ORDER BY updated_at DESC
            LIMIT 1
        """), {"opportunity_id": opportunity_id}).mappings().first()
    return _normalize_stored_identity(dict(row)) if row else None


def extract_identity_for_opportunity(opportunity_id: str, *, refresh: bool = False, use_llm: bool = True) -> Optional[Dict[str, Any]]:
    existing = None if refresh else latest_identity_for_opportunity(opportunity_id)
    if existing:
        return existing
    return upsert_identity_extraction_for_opportunity(opportunity_id, use_llm=use_llm)
