from __future__ import annotations

import argparse
import csv
import json
import re
from collections import Counter, defaultdict
from dataclasses import asdict, dataclass
from pathlib import Path
from typing import Any, Iterable

from sqlalchemy import text

from app.db import engine
from app.opportunity_next_action_service import get_opportunity_next_action


SEVERITY_RANK = {"CRITICAL": 0, "HIGH": 1, "MEDIUM": 2, "LOW": 3, "INFO": 4}
TERMINAL_STAGES = {"WON", "LOST", "NO_INTEREST", "DELIVERED", "CLOSED"}
TERMINAL_STATUSES = {"closed", "won", "lost", "done", "delivered"}
PAYMENT_TASK_CODES = {"CONFIRM_PAYMENT", "FOLLOW_UP_PAYMENT"}
NO_WORK_ACTION_CODES = {"NO_ACTION", "IGNORE_SPAM", "IGNORE_BOUNCE"}
# These central next actions are human work that must be materialized into a pending task.
# v1.5.96 intentionally starts with SEND_INVOICE; other UI/ERP actions may stay as page buttons.
MATERIALIZED_NEXT_ACTIONS = {"SEND_INVOICE"}
# Actions that can exist as ordinary pending operator work and do not necessarily mean the
# opportunity next-action engine is wrong when a different operational next action is shown.
FOLLOW_UP_CODES = {"FOLLOW_UP", "FOLLOW_UP_QUOTE", "FOLLOW_UP_PROFORMA", "FOLLOW_UP_PAYMENT", "FOLLOW_UP_CUSTOMER_REVIEW", "FOLLOW_UP_GENERIC"}


@dataclass
class Finding:
    severity: str
    rule: str
    opportunity_id: str
    title: str = ""
    customer: str = ""
    stage: str = ""
    status: str = ""
    next_action: str = ""
    pending_task_id: str = ""
    pending_task_action: str = ""
    evidence: str = ""
    suggested_fix: str = ""


def _s(value: Any) -> str:
    return str(value or "").strip()


def _upper(value: Any) -> str:
    return _s(value).upper()


def _payload_dict(value: Any) -> dict[str, Any]:
    if isinstance(value, dict):
        return value
    if isinstance(value, str) and value.strip():
        try:
            parsed = json.loads(value)
            return parsed if isinstance(parsed, dict) else {}
        except Exception:
            return {}
    return {}


def _action_code_from_decision(decision: dict[str, Any]) -> str:
    return _upper(
        decision.get("action_code")
        or (decision.get("next_action") or {}).get("code")
        or decision.get("code")
    )


def _label_from_decision(decision: dict[str, Any]) -> str:
    return _s(
        decision.get("label")
        or (decision.get("next_action") or {}).get("label")
        or _action_code_from_decision(decision)
    )


def _document_from_decision(decision: dict[str, Any]) -> tuple[str, str]:
    doc_id = _s(decision.get("document_id") or (decision.get("next_action") or {}).get("document_id"))
    doc_number = _s(decision.get("document_number") or (decision.get("next_action") or {}).get("document_number"))
    return doc_id, doc_number


def _target_task_id(decision: dict[str, Any]) -> str:
    target_url = _s(decision.get("target_url") or (decision.get("next_action") or {}).get("target_url"))
    m = re.search(r"/tasks/([0-9a-fA-F-]{32,36})", target_url)
    return m.group(1) if m else ""


def _invoice_sent(doc: dict[str, Any] | None) -> bool:
    if not doc:
        return False
    payload = _payload_dict(doc.get("payload"))
    return bool(
        doc.get("sent_at")
        or doc.get("sent")
        or _s(doc.get("status")).lower() in {"sent", "issued_sent"}
        or payload.get("clientflow_invoice_sent_evidence")
        or payload.get("invoice_sent_at")
    )


def _is_invoice(doc: dict[str, Any]) -> bool:
    return _s(doc.get("document_kind")).lower() in {"invoice", "fatura", "fa", "ft", "jasmin_invoice"}


def _is_quote(doc: dict[str, Any]) -> bool:
    return _s(doc.get("document_kind")).lower() in {"quotation", "quote", "orc", "orcamento", "jasmin_quotation", "proforma", "jasmin_proforma"}


def _opportunities(*, limit: int, include_closed: bool) -> list[dict[str, Any]]:
    where = ""
    if not include_closed:
        where = """
        WHERE COALESCE(o.status, 'open') NOT IN ('closed','won','lost','done','delivered')
          AND COALESCE(o.stage, '') NOT IN ('WON','LOST','NO_INTEREST','DELIVERED','CLOSED')
        """
    sql = text(f"""
        SELECT
            o.id::text,
            o.title,
            o.customer_name,
            o.customer_email,
            o.stage,
            o.status,
            o.updated_at,
            o.created_at
        FROM opportunities o
        {where}
        ORDER BY o.updated_at DESC NULLS LAST, o.created_at DESC NULLS LAST
        LIMIT :limit
    """)
    with engine.begin() as conn:
        return [dict(r) for r in conn.execute(sql, {"limit": limit}).mappings().all()]


def _tasks_for(opp_id: str) -> list[dict[str, Any]]:
    sql = text("""
        SELECT
            id::text,
            action_code,
            action,
            note,
            route,
            priority,
            status,
            due_at,
            created_at,
            updated_at,
            done_at,
            done_by,
            source_system,
            source_event_id,
            idempotency_key,
            metadata
        FROM tasks
        WHERE opportunity_id = CAST(:opportunity_id AS UUID)
        ORDER BY
            CASE status WHEN 'pending' THEN 1 WHEN 'failed' THEN 2 WHEN 'done' THEN 3 WHEN 'ignored' THEN 4 WHEN 'skipped' THEN 5 ELSE 6 END,
            CASE COALESCE(priority, 'normal') WHEN 'alta' THEN 1 WHEN 'normal' THEN 2 WHEN 'baixa' THEN 3 ELSE 4 END,
            due_at NULLS LAST,
            created_at DESC
    """)
    with engine.begin() as conn:
        return [dict(r) for r in conn.execute(sql, {"opportunity_id": opp_id}).mappings().all()]


def _docs_for(opp_id: str) -> list[dict[str, Any]]:
    sql = text("""
        SELECT
            id::text,
            document_kind,
            document_number,
            status,
            role,
            is_active,
            is_primary,
            total_amount,
            amount,
            document_date,
            created_at,
            updated_at,
            payload
        FROM commercial_documents
        WHERE opportunity_id = CAST(:opportunity_id AS UUID)
          AND system = 'jasmin'
          AND COALESCE(is_active, TRUE) = TRUE
          AND COALESCE(role, 'current') IN ('current', 'accepted', 'historical', 'history')
        ORDER BY
            CASE document_kind WHEN 'invoice' THEN 1 WHEN 'quotation' THEN 2 WHEN 'proforma' THEN 3 ELSE 4 END,
            CASE COALESCE(role, 'current') WHEN 'current' THEN 1 WHEN 'accepted' THEN 2 ELSE 3 END,
            COALESCE(document_date, created_at::date) DESC,
            created_at DESC
    """)
    with engine.begin() as conn:
        return [dict(r) for r in conn.execute(sql, {"opportunity_id": opp_id}).mappings().all()]


def _operation_links_for(opp_id: str) -> list[dict[str, Any]]:
    sql = text("""
        SELECT system, external_type, external_id, external_name, status, payload, updated_at
        FROM operation_links
        WHERE opportunity_id = CAST(:opportunity_id AS UUID)
    """)
    try:
        with engine.begin() as conn:
            return [dict(r) for r in conn.execute(sql, {"opportunity_id": opp_id}).mappings().all()]
    except Exception:
        return []


def _payment_confirmed(opp: dict[str, Any], links: list[dict[str, Any]], decision: dict[str, Any]) -> bool:
    stage = _upper(opp.get("stage"))
    financial_state = _s(decision.get("financial_state"))
    if stage == "PAYMENT_CONFIRMED" or financial_state == "payment_confirmed":
        return True
    for link in links:
        if _s(link.get("system")) == "clientflow" and _s(link.get("external_type")) == "payment" and _s(link.get("status")) == "confirmed":
            return True
    return False


def _current_invoice(docs: list[dict[str, Any]]) -> dict[str, Any] | None:
    for d in docs:
        if _is_invoice(d):
            return d
    return None


def _current_quote(docs: list[dict[str, Any]]) -> dict[str, Any] | None:
    for d in docs:
        if _is_quote(d):
            return d
    return None


def _f(
    findings: list[Finding],
    severity: str,
    rule: str,
    opp: dict[str, Any],
    *,
    next_action: str = "",
    task: dict[str, Any] | None = None,
    evidence: str = "",
    suggested_fix: str = "",
) -> None:
    findings.append(Finding(
        severity=severity,
        rule=rule,
        opportunity_id=_s(opp.get("id")),
        title=_s(opp.get("title")),
        customer=_s(opp.get("customer_name") or opp.get("customer_email")),
        stage=_s(opp.get("stage")),
        status=_s(opp.get("status")),
        next_action=next_action,
        pending_task_id=_s((task or {}).get("id")),
        pending_task_action=_upper((task or {}).get("action_code")),
        evidence=evidence,
        suggested_fix=suggested_fix,
    ))


def _audit_one(opp: dict[str, Any]) -> tuple[list[Finding], dict[str, Any]]:
    findings: list[Finding] = []
    opp_id = _s(opp.get("id"))
    tasks = _tasks_for(opp_id)
    docs = _docs_for(opp_id)
    links = _operation_links_for(opp_id)

    pending = [t for t in tasks if _s(t.get("status")) == "pending"]
    pending_by_code: dict[str, list[dict[str, Any]]] = defaultdict(list)
    for task in pending:
        pending_by_code[_upper(task.get("action_code"))].append(task)

    invoice = _current_invoice(docs)
    quote = _current_quote(docs)
    invoice_sent = _invoice_sent(invoice)
    has_invoice = bool(invoice)
    has_quote = bool(quote)

    try:
        decision = get_opportunity_next_action(opp_id)
    except Exception as exc:
        _f(
            findings,
            "HIGH",
            "next_action_engine_error",
            opp,
            evidence=f"get_opportunity_next_action falhou: {type(exc).__name__}: {exc}",
            suggested_fix="Corrigir motor/evidence antes de confiar na página da oportunidade.",
        )
        decision = {}

    next_code = _action_code_from_decision(decision)
    next_label = _label_from_decision(decision)
    next_display = f"{next_code} · {next_label}" if next_code else ""
    payment_ok = _payment_confirmed(opp, links, decision)
    target_task_id = _target_task_id(decision)

    stage = _upper(opp.get("stage"))
    status = _s(opp.get("status")).lower()
    terminal = stage in TERMINAL_STAGES or status in TERMINAL_STATUSES

    if terminal and pending:
        for task in pending[:5]:
            _f(
                findings,
                "HIGH",
                "pending_task_on_terminal_opportunity",
                opp,
                next_action=next_display,
                task=task,
                evidence=f"Oportunidade terminal stage={stage or '—'} status={status or '—'} tem task pendente.",
                suggested_fix="Ignorar/concluir tasks pendentes quando a oportunidade está fechada/entregue/sem interesse.",
            )

    if next_code in MATERIALIZED_NEXT_ACTIONS and not pending_by_code.get(next_code):
        _, doc_number = _document_from_decision(decision)
        _f(
            findings,
            "HIGH",
            "next_action_missing_pending_task",
            opp,
            next_action=next_display,
            evidence=f"A próxima ação central é {next_code}, mas não existe task pendente correspondente. Documento={doc_number or '—'}.",
            suggested_fix="Executar scripts/repair_missing_send_invoice_tasks.py --apply ou abrir a oportunidade para materializar a task.",
        )

    for code, items in sorted(pending_by_code.items()):
        if len(items) > 1:
            ids = ", ".join(_s(t.get("id")) for t in items[:4])
            _f(
                findings,
                "MEDIUM" if code in MATERIALIZED_NEXT_ACTIONS or code in PAYMENT_TASK_CODES else "LOW",
                "duplicate_pending_task_same_action",
                opp,
                next_action=next_display,
                task=items[0],
                evidence=f"Existem {len(items)} tasks pendentes com action_code={code}. Exemplos: {ids}",
                suggested_fix="Manter só a task operacional mais recente/correta; ignorar duplicados.",
            )

    if next_code == "NO_ACTION" and pending:
        for task in pending[:5]:
            _f(
                findings,
                "MEDIUM",
                "pending_task_when_next_action_is_no_action",
                opp,
                next_action=next_display,
                task=task,
                evidence="Motor central diz que não há ação necessária, mas existem tasks pendentes.",
                suggested_fix="Rever se a task é obsoleta; se sim, ignorar/concluir.",
            )

    for task in pending:
        code = _upper(task.get("action_code"))
        if code in NO_WORK_ACTION_CODES:
            _f(
                findings,
                "LOW",
                "non_action_code_pending",
                opp,
                next_action=next_display,
                task=task,
                evidence=f"Task pendente usa action_code que normalmente não exige trabalho humano: {code}.",
                suggested_fix="Marcar como ignored/skipped se não houver ação real.",
            )

        if code in PAYMENT_TASK_CODES and not (has_quote or has_invoice):
            _f(
                findings,
                "HIGH",
                "payment_task_without_commercial_document",
                opp,
                next_action=next_display,
                task=task,
                evidence="Task de pagamento pendente sem orçamento/fatura Jasmin associado.",
                suggested_fix="Associar documento comercial correto antes de confirmar/pedir pagamento; se for falso positivo, ignorar task.",
            )

        if code in PAYMENT_TASK_CODES and payment_ok:
            _f(
                findings,
                "MEDIUM",
                "obsolete_payment_task_pending",
                opp,
                next_action=next_display,
                task=task,
                evidence="Pagamento já está confirmado, mas ainda existe task pendente de pagamento/follow-up.",
                suggested_fix="Ignorar/concluir task obsoleta; não pedir pagamento novamente.",
            )

        if code == "SEND_INVOICE" and not has_invoice:
            _f(
                findings,
                "HIGH",
                "send_invoice_task_without_invoice",
                opp,
                next_action=next_display,
                task=task,
                evidence="Task SEND_INVOICE pendente sem fatura Jasmin associada.",
                suggested_fix="Associar/criar fatura antes de enviar ao cliente; se a task veio de triagem antiga, ignorar.",
            )

        if code == "SEND_INVOICE" and has_invoice and invoice_sent:
            doc_number = _s((invoice or {}).get("document_number"))
            _f(
                findings,
                "MEDIUM",
                "obsolete_send_invoice_task_pending",
                opp,
                next_action=next_display,
                task=task,
                evidence=f"Fatura {doc_number or '—'} já tem evidência local de envio, mas a task SEND_INVOICE continua pendente.",
                suggested_fix="Concluir/ignorar task pendente para evitar reenvio da fatura.",
            )

    if target_task_id:
        target_pending = any(_s(t.get("id")) == target_task_id and _s(t.get("status")) == "pending" for t in tasks)
        if not target_pending:
            _f(
                findings,
                "MEDIUM",
                "next_action_points_to_non_pending_task",
                opp,
                next_action=next_display,
                evidence=f"target_url aponta para task {target_task_id}, mas essa task não está pendente nesta oportunidade.",
                suggested_fix="Recalcular próxima ação ou corrigir target_url/fallback da UI.",
            )

    # Only warn about mismatches when the next action is one that the task layer explicitly materializes.
    # For WAIT_PRODUCTION, SHIP_ORDER, PREPARE_ORDER, etc. the UI/ERP cards may be the correct surface.
    if next_code in MATERIALIZED_NEXT_ACTIONS:
        other_actionable = [
            t for t in pending
            if _upper(t.get("action_code")) != next_code and _upper(t.get("action_code")) not in FOLLOW_UP_CODES
        ]
        for task in other_actionable[:5]:
            _f(
                findings,
                "LOW",
                "pending_task_not_primary_next_action",
                opp,
                next_action=next_display,
                task=task,
                evidence=f"Próxima ação central é {next_code}, mas há outra task pendente {task.get('action_code')}.",
                suggested_fix="Confirmar se a outra task ainda faz sentido ou se deve ser ignorada após criar/executar a ação principal.",
            )

    summary = {
        "opportunity_id": opp_id,
        "title": _s(opp.get("title")),
        "customer": _s(opp.get("customer_name") or opp.get("customer_email")),
        "stage": _s(opp.get("stage")),
        "status": _s(opp.get("status")),
        "next_action": next_code,
        "next_label": next_label,
        "pending_tasks": len(pending),
        "has_quote": has_quote,
        "has_invoice": has_invoice,
        "invoice_sent": invoice_sent,
        "payment_confirmed": payment_ok,
        "findings": len(findings),
    }
    return findings, summary


def _write_outputs(findings: list[Finding], summaries: list[dict[str, Any]], prefix: str) -> dict[str, str]:
    base = Path(prefix)
    base.parent.mkdir(parents=True, exist_ok=True)
    csv_path = str(base.with_suffix(".csv"))
    md_path = str(base.with_suffix(".md"))
    json_path = str(base.with_suffix(".json"))

    fieldnames = list(asdict(Finding("INFO", "", "")).keys())
    with open(csv_path, "w", newline="", encoding="utf-8") as fh:
        writer = csv.DictWriter(fh, fieldnames=fieldnames)
        writer.writeheader()
        for f in findings:
            writer.writerow(asdict(f))

    payload = {
        "findings": [asdict(f) for f in findings],
        "opportunities": summaries,
        "summary": {
            "opportunities_scanned": len(summaries),
            "findings": len(findings),
            "severity": dict(Counter(f.severity for f in findings)),
            "rules": dict(Counter(f.rule for f in findings)),
        },
    }
    with open(json_path, "w", encoding="utf-8") as fh:
        json.dump(payload, fh, ensure_ascii=False, indent=2, default=str)

    with open(md_path, "w", encoding="utf-8") as fh:
        fh.write("# Auditoria de fluxo de tarefas por oportunidade\n\n")
        fh.write(f"Oportunidades analisadas: **{len(summaries)}**\n\n")
        fh.write(f"Findings: **{len(findings)}**\n\n")
        fh.write("## Resumo por severidade\n\n")
        for sev, count in Counter(f.severity for f in findings).most_common():
            fh.write(f"- {sev}: {count}\n")
        fh.write("\n## Top regras\n\n")
        for rule, count in Counter(f.rule for f in findings).most_common(20):
            fh.write(f"- {rule}: {count}\n")
        fh.write("\n## Findings\n\n")
        if not findings:
            fh.write("Sem inconsistências encontradas.\n")
        else:
            fh.write("| Severidade | Regra | Oportunidade | Cliente | Próxima ação | Task | Evidência | Correção sugerida |\n")
            fh.write("|---|---|---|---|---|---|---|---|\n")
            for f in findings:
                fh.write(
                    "| "
                    + " | ".join(
                        _md_cell(x)
                        for x in [
                            f.severity,
                            f.rule,
                            f"{f.title} `{f.opportunity_id}`",
                            f.customer,
                            f.next_action,
                            f"{f.pending_task_action} `{f.pending_task_id}`" if f.pending_task_id else "—",
                            f.evidence,
                            f.suggested_fix,
                        ]
                    )
                    + " |\n"
                )
    return {"csv": csv_path, "markdown": md_path, "json": json_path}


def _md_cell(value: Any) -> str:
    text_value = _s(value).replace("\n", " ")
    return text_value.replace("|", "\\|") or "—"


def main() -> None:
    parser = argparse.ArgumentParser(description="Audita coerência entre oportunidades, próxima ação central e tasks criadas.")
    parser.add_argument("--limit", type=int, default=1000, help="máximo de oportunidades a analisar")
    parser.add_argument("--include-closed", action="store_true", help="inclui oportunidades fechadas/terminais")
    parser.add_argument("--prefix", default="/tmp/clientflow_opportunity_task_flow_audit", help="prefixo dos ficheiros CSV/MD/JSON")
    parser.add_argument("--only-findings", action="store_true", help="não imprime linhas OK por oportunidade")
    args = parser.parse_args()

    opps = _opportunities(limit=args.limit, include_closed=args.include_closed)
    print(f"OPPORTUNITIES_TO_SCAN={len(opps)} include_closed={args.include_closed}")

    all_findings: list[Finding] = []
    summaries: list[dict[str, Any]] = []
    for i, opp in enumerate(opps, start=1):
        if i == 1 or i % 50 == 0:
            print(f"scanning={i}/{len(opps)} updated_at={opp.get('updated_at')} title={opp.get('title')}")
        findings, summary = _audit_one(opp)
        all_findings.extend(findings)
        summaries.append(summary)
        if findings:
            worst = min((f.severity for f in findings), key=lambda s: SEVERITY_RANK.get(s, 99))
            print(f"{worst} findings={len(findings)} opp={opp.get('id')} next={summary.get('next_action')} title={opp.get('title')}")
        elif not args.only_findings:
            print(f"OK opp={opp.get('id')} next={summary.get('next_action')} pending={summary.get('pending_tasks')} title={opp.get('title')}")

    all_findings.sort(key=lambda f: (SEVERITY_RANK.get(f.severity, 99), f.rule, f.title))
    paths = _write_outputs(all_findings, summaries, args.prefix)

    severity_counter = Counter(f.severity for f in all_findings)
    rule_counter = Counter(f.rule for f in all_findings)

    print("Auditoria de fluxo de tarefas concluída.")
    print(f"Oportunidades analisadas: {len(summaries)}")
    print(f"Findings: {len(all_findings)}")
    print("Resumo por severidade:")
    if severity_counter:
        for sev in sorted(severity_counter, key=lambda s: SEVERITY_RANK.get(s, 99)):
            print(f"{sev}: {severity_counter[sev]}")
    else:
        print("OK: 0 inconsistências")
    print("Top regras:")
    if rule_counter:
        for rule, count in rule_counter.most_common(20):
            print(f"{rule}: {count}")
    else:
        print("—")
    print(f"CSV: {paths['csv']}")
    print(f"Markdown: {paths['markdown']}")
    print(f"JSON: {paths['json']}")


if __name__ == "__main__":
    main()
