import json
import traceback
from datetime import datetime
from flask import Blueprint, request, jsonify
from app.database import db
from app.auth import login_required
from app.routes.api_bi import build_filter_clause

conversations_bp = Blueprint("conversations", __name__)

import re
from datetime import datetime, timedelta

def adjust_cuiaba_time(dt_val, hours_to_subtract=1):
    """Subtrai 1 hora de um datetime ou string de data/hora para adequar ao fuso horário de Cuiabá (GMT-4)."""
    if not dt_val:
        return dt_val
    if isinstance(dt_val, datetime):
        return (dt_val - timedelta(hours=hours_to_subtract)).strftime("%Y-%m-%d %H:%M:%S")
    if isinstance(dt_val, str):
        s = dt_val.strip()
        try:
            s_clean = s.replace("T", " ")[:19]
            dt = datetime.strptime(s_clean, "%Y-%m-%d %H:%M:%S")
            return (dt - timedelta(hours=hours_to_subtract)).strftime("%Y-%m-%d %H:%M:%S")
        except Exception:
            pass
        try:
            has_sec = len(s.split(":")) > 2
            fmt = "%d/%m/%Y, %H:%M:%S" if has_sec else "%d/%m/%Y, %H:%M"
            dt = datetime.strptime(s[:20], fmt)
            return (dt - timedelta(hours=hours_to_subtract)).strftime(fmt)
        except Exception:
            pass
    return dt_val

def parse_closing_info(row, payload):
    """Extrai data/hora de fechamento (Cuiabá -1h), nome do agente de fechamento e motivo do encerramento."""
    p = payload if isinstance(payload, dict) else {}
    
    dt_fech = row.get("data_fechamento") or p.get("closingDate") or p.get("data_fechamento")
    op_fech = row.get("operador_fechamento") or p.get("operadorFechamento") or p.get("operador_fechamento")
    mot_fech = row.get("motivo_fechamento") or p.get("closingReason") or p.get("motivo_fechamento")
    
    txt = (p.get("messageHistory") or "") + "\n" + (p.get("mensagem") or "")
    if txt.strip():
        pattern = re.compile(r'\(\s*(\d{2}/\d{2}/\d{4},\s*\d{2}:\d{2}(?::\d{2})?)\s*\)\s*Chatbot Duotalk:\s*(?:Este\s+)?atendimento\s+(?:foi\s+)?encerrado\s+por\s+([^.]+)\.\s*Motivo:\s*([^\n\r]+)', re.IGNORECASE)
        m = pattern.search(txt)
        if m:
            if not dt_fech or dt_fech == "None":
                dt_fech = m.group(1).strip()
            if not op_fech or op_fech in ["-", "None", ""]:
                op_fech = m.group(2).strip()
            if not mot_fech or mot_fech in ["-", "None", ""]:
                mot_fech = m.group(3).strip()

    dt_fech_cuiaba = adjust_cuiaba_time(str(dt_fech)) if dt_fech else None

    return {
        "data_fechamento": dt_fech_cuiaba,
        "operador_fechamento": op_fech or "-",
        "motivo_fechamento": mot_fech or ""
    }

def parse_duotalk_transcript(text_content, default_customer="", default_operator=""):
    """Parseia transcrições de chat no formato (DD/MM/YYYY, HH:MM:SS) Sender: Mensagem sem alterar horário do chat."""
    if not text_content or not isinstance(text_content, str):
        return []
    pattern = re.compile(r'\(\s*(\d{2}/\d{2}/\d{4},\s*\d{2}:\d{2}(?::\d{2})?)\s*\)\s*([^:\n]+):\s*([\s\S]*?)(?=\(\s*\d{2}/\d{2}/\d{4},\s*\d{2}:\d{2}|\Z)')
    matches = pattern.findall(text_content)
    messages = []
    for timestamp, sender_name, text in matches:
        s_name = sender_name.strip()
        s_text = text.strip()
        
        # Limpar prefixos automáticos como "Mensagens recebidas! ✅ "
        s_text = re.sub(r'^Mensagens?\s+recebidas?!\s*✅\s*', '', s_text, flags=re.IGNORECASE).strip()
        
        if not s_text:
            continue
        lower_s = s_name.lower()
        if "bot" in lower_s or "chatbot" in lower_s:
            sender_type = "bot"
        elif default_operator and (default_operator.lower() in lower_s or lower_s in default_operator.lower()):
            sender_type = "agent"
        elif default_customer and (default_customer.lower() in lower_s or lower_s in default_customer.lower()):
            sender_type = "customer"
        else:
            sender_type = "agent" if ("atendente" in lower_s or "operador" in lower_s or "maria" in lower_s or "rafael" in lower_s or "carina" in lower_s or "amanda" in lower_s or "keren" in lower_s) else "customer"

        messages.append({
            "sender": sender_type,
            "sender_name": s_name,
            "text": s_text,
            "time": timestamp.strip()
        })
    return messages

@conversations_bp.route("", methods=["GET"], strict_slashes=False)
@conversations_bp.route("/", methods=["GET"], strict_slashes=False)
@login_required
def list_conversations():
    """Listagem paginada e filtrada de atendimentos com mapeamento de colunas e tolerância a falhas."""
    try:
        args = request.args
        page = max(1, int(args.get("page", 1)))
        page_size = min(100, max(5, int(args.get("page_size", 15))))
        offset = (page - 1) * page_size

        sort_col = args.get("sort_by", "criado_em")
        sort_dir = "ASC" if args.get("sort_dir", "desc").lower() == "asc" else "DESC"

        allowed_sort_cols = [
            "id", "id_duotalk", "id_conversa", "nome_chat", "telefone", 
            "canal", "origem", "operador_nome", "qualificacao_lead", 
            "motivo_fechamento", "criado_em"
        ]
        if sort_col not in allowed_sort_cols:
            sort_col = "criado_em"

        where_sql, params = build_filter_clause(args)

        total_records = 0
        try:
            count_sql = f"SELECT COUNT(*) as total FROM atendimentos {where_sql}"
            total_row = db.execute_query(count_sql, params, fetch="one")
            total_records = total_row.get("total", 0) if total_row else 0
        except Exception as err_count:
            print(f"[API CONVERSATIONS COUNT WARN] Erro ao contar total: {err_count}")

        select_sql = f"""
        SELECT * FROM atendimentos
        {where_sql}
        ORDER BY {sort_col} {sort_dir}
        LIMIT {page_size} OFFSET {offset}
        """
        rows = db.execute_query(select_sql, params) or []

        conversations = []
        for r in rows:
            if not isinstance(r, dict):
                continue

            p = r.get("payload_bruto")
            if isinstance(p, str):
                try:
                    p = json.loads(p)
                except Exception:
                    p = {}
            elif not isinstance(p, dict):
                p = {}

            cust_name = p.get("nome") or r.get("nome") or r.get("nome_cliente") or r.get("nome_chat") or p.get("nomeChatbot") or "Lead Visitante"
            canal_val = r.get("canal") or p.get("canal") or (p.get("interaction", {}).get("channel") if isinstance(p.get("interaction"), dict) else None) or "WhatsApp"
            origem_val = r.get("origem") or p.get("origem") or (p.get("interaction", {}).get("origin") if isinstance(p.get("interaction"), dict) else None) or "Inbound"
            intencao_val = r.get("intencao") or p.get("intencao") or (p.get("interaction", {}).get("intention") if isinstance(p.get("interaction"), dict) else None) or "Geral"
            op_nome = r.get("operador_nome") or p.get("operador") or (p.get("operator", {}).get("name") if isinstance(p.get("operator"), dict) else None) or "-"
            qual_val = r.get("qualificacao_lead") or p.get("qualificacaoLead") or (p.get("outcome", {}).get("qualification") if isinstance(p.get("outcome"), dict) else None) or "Lead"
            phone_val = r.get("telefone") or p.get("telefone") or (p.get("customer", {}).get("phone") if isinstance(p.get("customer"), dict) else None) or ""
            email_val = r.get("email") or p.get("email") or (p.get("customer", {}).get("email") if isinstance(p.get("customer"), dict) else None) or ""

            # Status de Atendimento (Aberto vs Encerrado) derivado de tipoIntegracao no payload
            tp_raw = str(r.get("tipo_integracao") or p.get("tipoIntegracao") or "abertura").strip()
            tp_lower = tp_raw.lower()
            if "fech" in tp_lower or "encerr" in tp_lower:
                status_atendimento = "Encerrado"
            else:
                status_atendimento = "Aberto"

            # Extração defensiva de informações de fechamento e ajuste de fuso Cuiabá (-1h) SOMENTE para criado_em
            closing_info = parse_closing_info(r, p)
            raw_criado = r.get("criado_em") or p.get("timestamp") or datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            criado_cuiaba = adjust_cuiaba_time(str(raw_criado))

            conv_item = {
                "id": r.get("id"),
                "id_duotalk": r.get("id_duotalk") or p.get("id") or f"DT-{r.get('id', '0')}",
                "id_conversa": r.get("id_conversa") or p.get("idConversa") or "",
                "nome_chat": cust_name,
                "nome": cust_name,
                "nome_cliente": cust_name,
                "telefone": phone_val,
                "email": email_val,
                "canal": canal_val,
                "origem": origem_val,
                "intencao": intencao_val,
                "operador_id": r.get("operador_id") or p.get("operadorId") or "",
                "operador_nome": op_nome,
                "operador_email": r.get("operador_email") or p.get("operadorEmail") or "",
                "key_entity": r.get("key_entity") or p.get("keyEntity") or p.get("Marca") or "",
                "tipo_integracao": tp_raw,
                "status_atendimento": status_atendimento,
                "qualificacao_lead": qual_val,
                "motivo_fechamento": closing_info["motivo_fechamento"],
                "operador_fechamento": closing_info["operador_fechamento"],
                "data_fechamento": closing_info["data_fechamento"],
                "criado_em": criado_cuiaba
            }
            conversations.append(conv_item)

        total_pages = (total_records + page_size - 1) // page_size if total_records > 0 else 1

        return jsonify({
            "success": True,
            "page": page,
            "page_size": page_size,
            "total_records": total_records,
            "total_pages": total_pages,
            "conversations": conversations
        }), 200

    except Exception as e:
        error_msg = str(e)
        print(f"[API CONVERSATIONS ERROR] Falha na consulta de conversas: {error_msg}")
        traceback.print_exc()

        fallback_page = int(request.args.get("page", 1)) if request.args.get("page") else 1
        fallback_page_size = int(request.args.get("page_size", 15)) if request.args.get("page_size") else 15
        
        return jsonify({
            "success": True,
            "page": fallback_page,
            "page_size": fallback_page_size,
            "total_records": 0,
            "total_pages": 1,
            "conversations": [],
            "error": error_msg
        }), 200

@conversations_bp.route("/<int:conversation_id>", methods=["GET"])
@login_required
def get_conversation_detail(conversation_id):
    """Retorna detalhes completos de um atendimento incluindo payload_bruto e mensagens."""
    try:
        row = db.execute_query("SELECT * FROM atendimentos WHERE id = %s", (conversation_id,), fetch="one")
        if not row:
            row = db.execute_query("SELECT * FROM atendimentos WHERE id = ?", (conversation_id,), fetch="one")

        if not row:
            return jsonify({"success": False, "error": "Atendimento não encontrado."}), 404

        payload = row.get("payload_bruto")
        if isinstance(payload, str):
            try:
                payload = json.loads(payload)
            except Exception:
                payload = {"raw_string": payload}

        # Extrai mensagens para timeline
        messages = []
        if isinstance(payload, dict):
            if "interaction" in payload and isinstance(payload.get("interaction"), dict) and "messages" in payload["interaction"]:
                messages = payload["interaction"]["messages"]
            elif "conversation" in payload and isinstance(payload.get("conversation"), dict) and "messages" in payload["conversation"]:
                messages = payload["conversation"]["messages"]

            if not messages:
                mh = payload.get("messageHistory") or ""
                mg = payload.get("mensagem") or ""
                # Seleciona o candidato que possui estampas de data/hora (06/07/2026, 15:32:14)
                transcript = mh if ("(" in mh and ")" in mh) else (mg if ("(" in mg and ")" in mg) else (mh or mg))
                
                if transcript:
                    cust_name = payload.get("nome") or (row.get("nome_chat") if row else "")
                    op_name = payload.get("operador") or (row.get("operador_nome") if row else "")
                    messages = parse_duotalk_transcript(transcript, cust_name, op_name)

        # Enriquece objeto row com fuso de Cuiabá (-1h) e dados de fechamento parseados
        cust_name = (payload.get("nome") if isinstance(payload, dict) else None) or row.get("nome") or row.get("nome_cliente") or row.get("nome_chat") or "Lead Visitante"
        row["nome_chat"] = cust_name
        row["nome"] = cust_name
        row["nome_cliente"] = cust_name
        closing_info = parse_closing_info(row, payload if isinstance(payload, dict) else {})
        row["criado_em"] = adjust_cuiaba_time(str(row.get("criado_em") or (payload.get("timestamp") if isinstance(payload, dict) else "")))
        row["data_fechamento"] = closing_info["data_fechamento"]
        row["operador_fechamento"] = closing_info["operador_fechamento"]
        row["motivo_fechamento"] = closing_info["motivo_fechamento"]
        if not row.get("intencao") and isinstance(payload, dict):
            row["intencao"] = payload.get("intencao") or "Geral"

        return jsonify({
            "success": True,
            "conversation": row,
            "payload_bruto": payload or {},
            "messages": messages or [],
            "closing_info": closing_info
        }), 200

    except Exception as e:
        print("\n" + "="*70)
        print(f"[API CONVERSATION DETAIL ERROR] id={conversation_id}: {e}")
        traceback.print_exc()
        print("="*70 + "\n")
        return jsonify({
            "success": False,
            "error": f"Erro ao recuperar atendimento: {str(e)}",
            "conversation": {},
            "payload_bruto": {},
            "messages": []
        }), 200

@conversations_bp.route("/webhook", methods=["POST"])
def receive_webhook():
    """Simulador / Receptor de Webhook da Duotalk para novos atendimentos."""
    try:
        data = request.get_json() or {}
        
        # Extração de campos flexível
        id_duotalk = data.get("protocol") or data.get("id_duotalk") or f"DT-2026-{datetime.now().strftime('%H%M%S')}"
        id_conversa = data.get("conversa_id") or data.get("id_conversa") or f"CONV-API-{datetime.now().strftime('%M%S')}"
        
        customer = data.get("customer", {}) if isinstance(data.get("customer"), dict) else {}
        nome_chat = customer.get("name") or data.get("nome_chat", "Lead Visitante")
        telefone = customer.get("phone") or data.get("telefone", "+55 (11) 99999-0000")
        email = customer.get("email") or data.get("email", "lead@cliente.com.br")

        interaction = data.get("interaction", {}) if isinstance(data.get("interaction"), dict) else {}
        canal = interaction.get("channel") or data.get("canal", "WhatsApp")
        origem = interaction.get("origin") or data.get("origem", "Campanha Duotalk Webhook")
        intencao = interaction.get("intention") or data.get("intencao", "Compra de Veículo 0km")
        key_entity = interaction.get("target_product") or data.get("key_entity", "Jeep Compass 2024")

        operator = data.get("operator", {}) if isinstance(data.get("operator"), dict) else {}
        operador_id = operator.get("id") or data.get("operador_id", "OP-01")
        operador_nome = operator.get("name") or data.get("operador_nome", "Lucas Silva")
        operador_email = operator.get("email") or data.get("operador_email", "lucas.silva@duotalk.com.br")

        outcome = data.get("outcome", {}) if isinstance(data.get("outcome"), dict) else {}
        qualificacao_lead = outcome.get("qualification") or data.get("qualificacao_lead", "Quente (Em Negociação)")
        motivo_fechamento = outcome.get("closing_reason") or data.get("motivo_fechamento", "Em andamento / Proposta enviada")
        operador_fechamento = outcome.get("closed_by") or operador_nome
        
        tipo_integracao = data.get("tipo_integracao", "Duotalk Webhook API")
        data_fechamento = outcome.get("closed_at") or datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        # Inserção
        sql_mysql = """
        INSERT INTO atendimentos (
            id_duotalk, id_conversa, nome_chat, telefone, email,
            canal, origem, intencao, operador_id, operador_nome,
            operador_email, key_entity, tipo_integracao, qualificacao_lead,
            motivo_fechamento, operador_fechamento, data_fechamento,
            payload_bruto, criado_em
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        sql_sqlite = """
        INSERT INTO atendimentos (
            id_duotalk, id_conversa, nome_chat, telefone, email,
            canal, origem, intencao, operador_id, operador_nome,
            operador_email, key_entity, tipo_integracao, qualificacao_lead,
            motivo_fechamento, operador_fechamento, data_fechamento,
            payload_bruto, criado_em
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """

        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        payload_str = json.dumps(data, ensure_ascii=False) if not isinstance(data, str) else data

        vals = (
            id_duotalk, id_conversa, nome_chat, telefone, email,
            canal, origem, intencao, operador_id, operador_nome,
            operador_email, key_entity, tipo_integracao, qualificacao_lead,
            motivo_fechamento, operador_fechamento, data_fechamento,
            payload_str, now_str
        )

        if db.active_driver == "mysql":
            new_id = db.execute_insert(sql_mysql, vals)
        else:
            new_id = db.execute_insert(sql_sqlite, vals)

        db.log_event("INFO", "WEBHOOK", f"Novo atendimento recebido via Webhook: {id_duotalk} ({nome_chat})", {"id": new_id})

        return jsonify({
            "success": True,
            "message": "Atendimento registrado com sucesso!",
            "inserted_id": new_id,
            "protocol": id_duotalk
        }), 201

    except Exception as e:
        print("\n" + "="*70)
        print(f"[API WEBHOOK ERROR] Erro ao processar webhook: {e}")
        traceback.print_exc()
        print("="*70 + "\n")
        return jsonify({
            "success": False,
            "error": f"Erro ao processar webhook: {str(e)}"
        }), 500