#!/usr/bin/env python3
"""
Dashboard PX3 Lab — esqueleto inicial.
Login simples, sidebar fixa, secao de Pipelines com dados reais do Linkia (GHL).
"""
import os
import sys
import time
import html
import json
import re
import threading
import traceback
from concurrent.futures import ThreadPoolExecutor, as_completed
from datetime import datetime, timedelta
from pathlib import Path
from zoneinfo import ZoneInfo

import requests
from flask import (
    Flask,
    render_template,
    request,
    redirect,
    url_for,
    session,
    flash,
    jsonify,
)
from google.ads.googleads.client import GoogleAdsClient

# Reusar logica do relatorio semanal existente
SCRIPT_DIR = Path(__file__).resolve().parent
PX3_DIR = SCRIPT_DIR.parent
sys.path.insert(0, str(PX3_DIR))
sys.path.insert(0, str(PX3_DIR / "relatorio_semanal"))

from config_ghl import PX3_TOKEN, PX3_LOCATION_ID  # noqa: E402

import gerar_relatorio as rel  # noqa: E402  reusa coletar/agregar/etc.

from auth import (  # noqa: E402
    check_login,
    login_required,
    USERS,
    get_user_role,
    get_user_ghl_id,
)

import sqlite3 as _sqlite3  # noqa: E402  usado pelo endpoint /api/followup

import tracking  # noqa: E402  blueprint + helpers de rastreamento

# ============================================================
# Flask
# ============================================================
app = Flask(__name__)
app.secret_key = os.environ.get("DASHBOARD_SECRET", "px3-dash-secret-change-me-2026")
app.config["SESSION_COOKIE_NAME"] = "px3dash"
app.config["PERMANENT_SESSION_LIFETIME"] = timedelta(days=30)
app.config["SESSION_COOKIE_SAMESITE"] = "Lax"

# Registra endpoints publicos de tracking (sem login)
app.register_blueprint(tracking.bp)

GHL_BASE = "https://services.leadconnectorhq.com"
HEADERS = {
    "Authorization": f"Bearer {PX3_TOKEN}",
    "Version": "2021-07-28",
    "Accept": "application/json",
    "Content-Type": "application/json",
}

# Headers para a API de conversas (Version diferente)
CONV_HEADERS = {
    "Authorization": f"Bearer {PX3_TOKEN}",
    "Version": "2021-04-15",
    "Accept": "application/json",
}

# Meta Ads
META_ACCOUNT_ID = "act_519765955435243"
META_GRAPH_URL = f"https://graph.facebook.com/v21.0/{META_ACCOUNT_ID}/insights"
try:
    _META_TOKEN = Path("/opt/mia/config/meta_token.txt").read_text().strip()
except Exception:
    _META_TOKEN = ""

TZ_BR = ZoneInfo("America/Sao_Paulo")
TZ_UTC = ZoneInfo("UTC")

# Cache simples em memoria para pipelines (15 min)
_pipelines_cache = {"data": None, "ts": 0}
_PIPE_TTL = 900

# Cache de dados de oportunidades por (pipeline_id, preset, inicio, fim)
_opps_cache: dict = {}
_OPPS_TTL = 900  # 15 min

# Cache Meta Ads por (preset, inicio, fim)
_meta_cache: dict = {}
_META_TTL = 900  # 15 min

# Cache conversas
_conv_cache: dict = {}
_CONV_TTL = 900  # 15 min

# Google Ads
_GADS_TOKENS_PATH = Path("/opt/mia/config/google_ads_tokens.json")
_gads_client = None  # lazy init
_gads_cache: dict = {}
_GADS_TTL = 900  # 15 min

# Cache overview consolidado
_overview_cache: dict = {}
_OVERVIEW_TTL = 900  # 15 min


# ============================================================
# Helpers
# ============================================================
def listar_pipelines() -> list[dict]:
    """Lista todas as pipelines do location, com cache de 5 min."""
    now = time.time()
    if _pipelines_cache["data"] and (now - _pipelines_cache["ts"]) < _PIPE_TTL:
        return _pipelines_cache["data"]

    try:
        r = requests.get(
            f"{GHL_BASE}/opportunities/pipelines",
            headers=HEADERS,
            params={"locationId": PX3_LOCATION_ID},
            timeout=20,
        )
        r.raise_for_status()
        pipes = r.json().get("pipelines", []) or []
    except Exception as e:
        app.logger.warning(f"Falha ao listar pipelines: {e}")
        pipes = []

    # Normalizar
    pipelines = []
    for p in pipes:
        pid = p.get("id")
        if not pid:
            continue
        stages = []
        for s in p.get("stages", []) or []:
            stages.append({"id": s.get("id"), "name": s.get("name", "")})
        pipelines.append(
            {
                "id": pid,
                "name": p.get("name", "Sem nome"),
                "stages": stages,
                "stages_map": {s["id"]: s["name"] for s in stages},
            }
        )

    _pipelines_cache["data"] = pipelines
    _pipelines_cache["ts"] = now
    return pipelines


def get_pipeline(pipeline_id: str) -> dict | None:
    for p in listar_pipelines():
        if p["id"] == pipeline_id:
            return p
    return None


def calcular_periodo(preset: str, custom_inicio: str | None, custom_fim: str | None):
    """
    Retorna (inicio_utc, fim_utc, inicio_br, fim_br, label).
    presets: semana_atual | ultimos_7 | ultimos_30 | este_mes | mes_passado | custom
    """
    agora_br = datetime.now(TZ_BR)
    hoje_br = agora_br.replace(hour=23, minute=59, second=59, microsecond=0)

    if preset == "ultimos_7":
        inicio_br = (agora_br - timedelta(days=7)).replace(hour=0, minute=0, second=0, microsecond=0)
        fim_br = agora_br
        label = "Ultimos 7 dias"
    elif preset == "ultimos_30":
        inicio_br = (agora_br - timedelta(days=30)).replace(hour=0, minute=0, second=0, microsecond=0)
        fim_br = agora_br
        label = "Ultimos 30 dias"
    elif preset == "este_mes":
        inicio_br = agora_br.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        fim_br = agora_br
        label = "Este mes"
    elif preset == "mes_passado":
        primeiro_dia_mes = agora_br.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        ultimo_dia_passado = primeiro_dia_mes - timedelta(seconds=1)
        inicio_br = ultimo_dia_passado.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        fim_br = ultimo_dia_passado
        label = "Mes passado"
    elif preset == "custom" and custom_inicio and custom_fim:
        try:
            inicio_br = datetime.fromisoformat(custom_inicio).replace(tzinfo=TZ_BR, hour=0, minute=0, second=0, microsecond=0)
            fim_br = datetime.fromisoformat(custom_fim).replace(tzinfo=TZ_BR, hour=23, minute=59, second=59, microsecond=0)
            label = f"{inicio_br.strftime('%d/%m/%Y')} a {fim_br.strftime('%d/%m/%Y')}"
        except Exception:
            # fallback semana
            return calcular_periodo("semana_atual", None, None)
    else:
        # semana_atual = sex 09:01 -> agora (mesma do relatorio semanal)
        inicio_utc, fim_utc, lbl, inicio_br, fim_br = rel.calcular_janela()
        return inicio_utc, fim_utc, inicio_br, fim_br, "Esta semana (sex 09h - sex 09h)"

    inicio_utc = inicio_br.astimezone(TZ_UTC)
    fim_utc = fim_br.astimezone(TZ_UTC)
    return inicio_utc, fim_utc, inicio_br, fim_br, label


def buscar_opps_pipeline(pipeline_id: str) -> list[dict]:
    """Variacao do buscar_oportunidades do relatorio, mas para qualquer pipeline."""
    todas = []
    start_after = None
    start_after_id = None
    paginas = 0
    while True:
        paginas += 1
        params = {
            "location_id": PX3_LOCATION_ID,
            "pipeline_id": pipeline_id,
            "limit": 100,
        }
        if start_after and start_after_id:
            params["startAfter"] = start_after
            params["startAfterId"] = start_after_id
        try:
            r = requests.get(
                f"{GHL_BASE}/opportunities/search",
                headers=HEADERS,
                params=params,
                timeout=30,
            )
            if r.status_code == 429:
                time.sleep(2)
                continue
            r.raise_for_status()
            data = r.json()
        except Exception as e:
            app.logger.warning(f"Erro buscar opps {pipeline_id}: {e}")
            break

        opps = data.get("opportunities", []) or []
        todas.extend(opps)
        meta = data.get("meta", {}) or {}
        start_after = meta.get("startAfter")
        start_after_id = meta.get("startAfterId")
        if not meta.get("nextPageUrl") or not opps or paginas > 50:
            break
        time.sleep(0.15)
    return todas


def coletar_dados_pipeline(pipeline_id: str, inicio_utc, fim_utc) -> dict:
    todas = buscar_opps_pipeline(pipeline_id)

    dentro = []
    novos = []
    for op in todas:
        created = rel.parse_iso_utc(op.get("createdAt", ""))
        last_change = rel.parse_iso_utc(op.get("lastStageChangeAt", "")) or rel.parse_iso_utc(
            op.get("lastStatusChangeAt", "")
        )
        if created and inicio_utc <= created <= fim_utc:
            novos.append(op)
            dentro.append(op)
        elif last_change and inicio_utc <= last_change <= fim_utc:
            dentro.append(op)

    contact_ids = list({op["contactId"] for op in dentro if op.get("contactId")})
    notas_por_contato = {}
    contatos_full = {}

    def _fnotes(cid):
        return cid, rel.buscar_notas(cid)

    def _fcontact(cid):
        return cid, rel.buscar_contato(cid)

    with ThreadPoolExecutor(max_workers=10) as ex:
        futs = [ex.submit(_fnotes, cid) for cid in contact_ids]
        for fut in as_completed(futs):
            cid, notas = fut.result()
            notas_por_contato[cid] = notas

    with ThreadPoolExecutor(max_workers=10) as ex:
        futs = [ex.submit(_fcontact, cid) for cid in contact_ids]
        for fut in as_completed(futs):
            cid, ct = fut.result()
            contatos_full[cid] = ct

    return {
        "todas": todas,
        "dentro": dentro,
        "novos": novos,
        "notas_por_contato": notas_por_contato,
        "contatos_full": contatos_full,
    }


def agregar_pipeline(dados: dict, stages_map: dict, inicio_utc, fim_utc) -> dict:
    dentro = dados["dentro"]
    novos = dados["novos"]
    contatos_full = dados["contatos_full"]
    notas_por_contato = dados["notas_por_contato"]

    # nomes de stages que provavelmente indicam qualificado/fechado/perdido
    def _classifica(stage_nome: str, status: str) -> str:
        s = (stage_nome or "").lower()
        st = (status or "").lower()
        if st == "won" or "ganhamos" in s or "fechad" in s or "vencido" in s:
            return "ganhamos"
        if st == "lost" or "perdid" in s or "perda" in s:
            return "perdido"
        if "qualific" in s or "proposta" in s or "agendam" in s or "lead em teste" in s or "negocia" in s:
            return "qualificado"
        return "outros"

    qualificados = 0
    fechamentos = 0
    perdidos = 0
    contagem_stage = {}

    for op in dentro:
        stage_id = op.get("pipelineStageId", "")
        stage_nome = stages_map.get(stage_id, "SEM STAGE")
        status = op.get("status", "")
        if status == "lost":
            stage_nome_display = stage_nome + " (perdido)"
        elif status == "won":
            stage_nome_display = stage_nome + " (ganho)"
        else:
            stage_nome_display = stage_nome

        contagem_stage[stage_nome_display] = contagem_stage.get(stage_nome_display, 0) + 1

        cls = _classifica(stage_nome, status)
        if cls == "ganhamos":
            fechamentos += 1
            qualificados += 1
        elif cls == "perdido":
            perdidos += 1
        elif cls == "qualificado":
            qualificados += 1

    total = len(dentro)
    novos_total = len(novos)
    taxa_qual = (qualificados / novos_total * 100) if novos_total else 0

    linhas = []
    for op in dentro:
        cid = op.get("contactId")
        ct = contatos_full.get(cid, {}) or {}
        nome = (
            (ct.get("contactName") or "").strip()
            or f"{ct.get('firstName','') or ''} {ct.get('lastName','') or ''}".strip()
            or op.get("name")
            or "Sem nome"
        )
        telefone = ct.get("phone") or ""
        notas = notas_por_contato.get(cid, []) or []
        notas_na_janela = []
        for n in notas:
            d = rel.parse_iso_utc(n.get("dateAdded", ""))
            if d and inicio_utc <= d <= fim_utc:
                notas_na_janela.append((d, n))
        notas_na_janela.sort(key=lambda x: x[0], reverse=True)
        if notas_na_janela:
            ultimo = notas_na_janela[0][1]
            obs_txt = rel.strip_html(ultimo.get("bodyText") or ultimo.get("body") or "")
            obs = rel.truncar(obs_txt, 150) if obs_txt else "Sem observacao no periodo"
        else:
            obs = "Sem observacao no periodo"

        stage_id = op.get("pipelineStageId", "")
        stage_nome = stages_map.get(stage_id, "SEM STAGE")
        if op.get("status") == "lost":
            stage_nome = stage_nome + " (perdido)"
        elif op.get("status") == "won":
            stage_nome = stage_nome + " (ganho)"

        created = rel.parse_iso_utc(op.get("createdAt", ""))
        data_entrada_br = (
            created.astimezone(TZ_BR).strftime("%d/%m/%Y %H:%M") if created else "-"
        )

        linhas.append(
            {
                "nome": nome,
                "telefone": telefone,
                "telefone_clean": re.sub(r"\D", "", telefone or ""),
                "stage": stage_nome,
                "data_entrada": data_entrada_br,
                "data_sort": created.timestamp() if created else 0,
                "obs": obs,
            }
        )

    linhas.sort(key=lambda x: -x["data_sort"])

    return {
        "total": total,
        "novos": novos_total,
        "qualificados": qualificados,
        "fechamentos": fechamentos,
        "perdidos": perdidos,
        "taxa_qual": taxa_qual,
        "contagem_stage": contagem_stage,
        "linhas": linhas,
    }


def coletar_cached(pipeline_id: str, preset: str, custom_inicio: str, custom_fim: str):
    """Retorna (dados, agg, inicio_utc, fim_utc, label) com cache de 5 min."""
    cache_key = f"{pipeline_id}|{preset}|{custom_inicio}|{custom_fim}"
    now = time.time()
    if cache_key in _opps_cache:
        entry = _opps_cache[cache_key]
        if now - entry["ts"] < _OPPS_TTL:
            return entry["dados"], entry["agg"], entry["inicio_utc"], entry["fim_utc"], entry["label"]

    pipe = get_pipeline(pipeline_id)
    if not pipe:
        return None, None, None, None, ""

    inicio_utc, fim_utc, _inicio_br, _fim_br, label = calcular_periodo(
        preset, custom_inicio or None, custom_fim or None
    )
    dados = coletar_dados_pipeline(pipe["id"], inicio_utc, fim_utc)
    agg = agregar_pipeline(dados, pipe["stages_map"], inicio_utc, fim_utc)

    _opps_cache[cache_key] = {
        "ts": now,
        "dados": dados,
        "agg": agg,
        "inicio_utc": inicio_utc,
        "fim_utc": fim_utc,
        "label": label,
    }
    return dados, agg, inicio_utc, fim_utc, label


# ============================================================
# Helpers Meta Ads
# ============================================================
META_PRESET_MAP = {
    "ultimos_7": "last_7d",
    "ultimos_30": "last_30d",
    "este_mes": "this_month",
    "mes_passado": "last_month",
    "semana_atual": "last_7d",
}


def _fmt_brl(value) -> str:
    try:
        v = float(value or 0)
    except (TypeError, ValueError):
        v = 0.0
    # padrao R$ X.XXX,XX
    s = f"{v:,.2f}"  # ex 1,234.56
    s = s.replace(",", "@").replace(".", ",").replace("@", ".")
    return f"R$ {s}"


def _fmt_int(value) -> str:
    try:
        v = int(float(value or 0))
    except (TypeError, ValueError):
        v = 0
    return f"{v:,}".replace(",", ".")


def _fmt_pct(value) -> str:
    try:
        v = float(value or 0)
    except (TypeError, ValueError):
        v = 0.0
    return f"{v:.2f}%"


def _extrair_leads(actions) -> int:
    """Soma actions de tipo 'lead' + 'offsite_conversion.fb_pixel_lead'."""
    if not actions:
        return 0
    total = 0
    for a in actions:
        t = (a.get("action_type") or "").strip()
        if t in ("lead", "offsite_conversion.fb_pixel_lead"):
            try:
                total += int(float(a.get("value") or 0))
            except (TypeError, ValueError):
                pass
    return total


def buscar_meta_insights(preset: str, custom_inicio: str, custom_fim: str) -> dict:
    """
    Busca insights de campanhas no Meta Ads.
    Retorna dict com 'campanhas' (lista) e 'periodo_label'.
    """
    if not _META_TOKEN:
        raise RuntimeError("Token Meta nao configurado em /opt/mia/config/meta_token.txt")

    params = {
        "access_token": _META_TOKEN,
        "fields": "campaign_name,campaign_id,spend,impressions,clicks,ctr,cpm,actions,reach,objective",
        "level": "campaign",
        "limit": 50,
    }

    label = ""
    if preset == "custom" and custom_inicio and custom_fim:
        params["time_range"] = '{"since":"%s","until":"%s"}' % (custom_inicio, custom_fim)
        try:
            di = datetime.fromisoformat(custom_inicio).strftime("%d/%m/%Y")
            df = datetime.fromisoformat(custom_fim).strftime("%d/%m/%Y")
            label = f"{di} a {df}"
        except Exception:
            label = f"{custom_inicio} a {custom_fim}"
    else:
        date_preset = META_PRESET_MAP.get(preset, "last_7d")
        params["date_preset"] = date_preset
        label_map = {
            "last_7d": "Ultimos 7 dias",
            "last_30d": "Ultimos 30 dias",
            "this_month": "Este mes",
            "last_month": "Mes passado",
        }
        label = label_map.get(date_preset, "Ultimos 7 dias")

    r = requests.get(META_GRAPH_URL, params=params, timeout=30)
    if r.status_code != 200:
        raise RuntimeError(f"Meta API {r.status_code}: {r.text[:300]}")
    data = r.json()
    return {"campanhas_raw": data.get("data", []) or [], "periodo_label": label}


def agregar_meta(campanhas_raw: list) -> dict:
    """Agrega resumo e tabela de campanhas a partir do raw da API Meta."""
    gasto_total = 0.0
    impressoes_total = 0
    cliques_total = 0
    leads_total = 0
    reach_total = 0

    campanhas = []
    for c in campanhas_raw:
        try:
            spend = float(c.get("spend") or 0)
        except (TypeError, ValueError):
            spend = 0.0
        try:
            impressoes = int(float(c.get("impressions") or 0))
        except (TypeError, ValueError):
            impressoes = 0
        try:
            cliques = int(float(c.get("clicks") or 0))
        except (TypeError, ValueError):
            cliques = 0
        try:
            ctr = float(c.get("ctr") or 0)
        except (TypeError, ValueError):
            ctr = 0.0
        try:
            cpm = float(c.get("cpm") or 0)
        except (TypeError, ValueError):
            cpm = 0.0
        try:
            reach = int(float(c.get("reach") or 0))
        except (TypeError, ValueError):
            reach = 0

        leads = _extrair_leads(c.get("actions") or [])
        cpl = (spend / leads) if leads > 0 else 0.0

        gasto_total += spend
        impressoes_total += impressoes
        cliques_total += cliques
        leads_total += leads
        reach_total += reach

        campanhas.append({
            "nome": c.get("campaign_name") or "Sem nome",
            "objetivo": c.get("objective") or "-",
            "gasto": _fmt_brl(spend),
            "gasto_raw": spend,
            "impressoes": _fmt_int(impressoes),
            "cliques": _fmt_int(cliques),
            "leads": leads,
            "cpl": _fmt_brl(cpl) if leads > 0 else "-",
            "ctr": _fmt_pct(ctr),
            "cpm": _fmt_brl(cpm),
        })

    # Ordenar por gasto desc
    campanhas.sort(key=lambda x: -x["gasto_raw"])

    cpl_total = (gasto_total / leads_total) if leads_total > 0 else 0.0
    ctr_total = (cliques_total / impressoes_total * 100) if impressoes_total > 0 else 0.0
    cpm_total = (gasto_total / impressoes_total * 1000) if impressoes_total > 0 else 0.0

    resumo = {
        "gasto_total": _fmt_brl(gasto_total),
        "impressoes": _fmt_int(impressoes_total),
        "cliques": _fmt_int(cliques_total),
        "leads": leads_total,
        "cpl": _fmt_brl(cpl_total) if leads_total > 0 else "-",
        "ctr": _fmt_pct(ctr_total),
        "cpm": _fmt_brl(cpm_total),
    }

    return {"resumo": resumo, "campanhas": campanhas}


def coletar_meta_cached(preset: str, custom_inicio: str, custom_fim: str):
    cache_key = f"meta|{preset}|{custom_inicio}|{custom_fim}"
    now = time.time()
    if cache_key in _meta_cache:
        entry = _meta_cache[cache_key]
        if now - entry["ts"] < _META_TTL:
            return entry["resumo"], entry["campanhas"], entry["periodo_label"]

    insights = buscar_meta_insights(preset, custom_inicio, custom_fim)
    agg = agregar_meta(insights["campanhas_raw"])
    _meta_cache[cache_key] = {
        "ts": now,
        "resumo": agg["resumo"],
        "campanhas": agg["campanhas"],
        "periodo_label": insights["periodo_label"],
    }
    return agg["resumo"], agg["campanhas"], insights["periodo_label"]


def buscar_meta_diario(preset: str, custom_inicio: str, custom_fim: str) -> list[dict]:
    """
    Busca insights diarios do Meta Ads (breakdown=date), retorna lista
    [{"data": "dd/mm", "leads": int, "gasto": float}] ordenada por data.
    """
    if not _META_TOKEN:
        return []

    params = {
        "access_token": _META_TOKEN,
        "fields": "spend,actions",
        "level": "account",
        "time_increment": 1,
        "limit": 200,
    }

    if preset == "custom" and custom_inicio and custom_fim:
        params["time_range"] = '{"since":"%s","until":"%s"}' % (custom_inicio, custom_fim)
    else:
        date_preset = META_PRESET_MAP.get(preset, "last_7d")
        params["date_preset"] = date_preset

    try:
        r = requests.get(META_GRAPH_URL, params=params, timeout=30)
        if r.status_code != 200:
            return []
        data = r.json().get("data", []) or []
    except Exception:
        return []

    serie = []
    for row in data:
        date_start = row.get("date_start") or ""
        try:
            dt = datetime.fromisoformat(date_start)
            label = dt.strftime("%d/%m")
        except Exception:
            label = date_start
        try:
            spend = float(row.get("spend") or 0)
        except (TypeError, ValueError):
            spend = 0.0
        leads = _extrair_leads(row.get("actions") or [])
        serie.append({"data": label, "data_iso": date_start, "leads": leads, "gasto": round(spend, 2)})

    serie.sort(key=lambda x: x.get("data_iso", ""))
    return serie


# ============================================================
# Helpers Conversas (GHL)
# ============================================================
def buscar_conversas_ghl(limit_total: int = 200) -> dict:
    """
    Busca conversas via /conversations/search.
    Pagina ate atingir limit_total (max ~200 = 2 paginas) usando startAfterDate.
    Retorna dict com 'conversations' (lista) e 'total' (numero da API).
    """
    conversations: list[dict] = []
    vistos: set[str] = set()
    total_api = 0
    cursor = None
    paginas = 0
    max_paginas = 2

    while paginas < max_paginas and len(conversations) < limit_total:
        paginas += 1
        params = {"locationId": PX3_LOCATION_ID, "limit": 100}
        if cursor:
            params["startAfterDate"] = cursor

        try:
            r = requests.get(
                f"{GHL_BASE}/conversations/search",
                headers=CONV_HEADERS,
                params=params,
                timeout=30,
            )
            if r.status_code == 429:
                time.sleep(2)
                continue
            r.raise_for_status()
            data = r.json()
        except Exception as e:
            app.logger.warning(f"Erro buscar conversas: {e}")
            break

        page_convs = data.get("conversations", []) or []
        if not page_convs:
            break

        if paginas == 1:
            total_api = int(data.get("total") or 0)

        for c in page_convs:
            cid = c.get("id")
            if not cid or cid in vistos:
                continue
            vistos.add(cid)
            conversations.append(c)

        # cursor = sort[0] do ultimo item
        last_item = page_convs[-1]
        sort_arr = last_item.get("sort") or []
        new_cursor = sort_arr[0] if sort_arr else None
        if not new_cursor or new_cursor == cursor:
            break
        cursor = new_cursor
        time.sleep(0.2)

    return {"conversations": conversations[:limit_total], "total": total_api}


def _fmt_data_msg(ts_ms) -> str:
    if not ts_ms:
        return "-"
    try:
        dt = datetime.fromtimestamp(int(ts_ms) / 1000, tz=TZ_BR)
        return dt.strftime("%d/%m %H:%M")
    except Exception:
        return "-"


def agregar_conversas(payload: dict) -> dict:
    convs = payload.get("conversations") or []
    total_api = payload.get("total") or 0

    nao_lidas = 0
    por_tipo: dict[str, int] = {}

    ultimas_raw: list[tuple[int, dict]] = []

    CANAIS_ACEITOS = {"TYPE_WHATSAPP", "TYPE_PHONE", "TYPE_SMS", "TYPE_INSTAGRAM", "TYPE_FB_MESSENGER"}

    for c in convs:
        tipo = (c.get("lastMessageType") or c.get("type") or "DESCONHECIDO").strip().upper()

        # Ignorar e-mail, chamadas, no_show e outros
        if tipo not in CANAIS_ACEITOS:
            continue

        unread = int(c.get("unreadCount") or 0)
        if unread > 0:
            nao_lidas += unread

        por_tipo[tipo] = por_tipo.get(tipo, 0) + 1

        last_msg_ts = c.get("lastMessageDate") or c.get("dateUpdated") or 0
        ultimas_raw.append((int(last_msg_ts or 0), c))

    ultimas_raw.sort(key=lambda x: -x[0])

    ultimas = []
    for ts_ms, c in ultimas_raw[:50]:
        nome = (
            (c.get("contactName") or "").strip()
            or (c.get("fullName") or "").strip()
            or "Sem nome"
        )
        body = (c.get("lastMessageBody") or "").strip()
        body = re.sub(r"\s+", " ", body)
        if len(body) > 80:
            body = body[:77] + "..."

        tipo = (c.get("lastMessageType") or c.get("type") or "DESCONHECIDO").strip().upper()

        atribuido_id = c.get("assignedTo") or ""
        atribuido = atribuido_id if atribuido_id else "Sem atribuicao"

        ultimas.append({
            "conv_id": c.get("id") or "",
            "nome": nome,
            "telefone": c.get("phone") or "",
            "ultimo_msg": body or "(sem mensagem)",
            "data": _fmt_data_msg(ts_ms),
            "data_sort": ts_ms,
            "tipo": tipo,
            "nao_lidas": int(c.get("unreadCount") or 0),
            "atribuido": atribuido,
            "atribuido_id": atribuido_id,
        })

    return {
        "total": total_api,
        "nao_lidas": nao_lidas,
        "por_tipo": por_tipo,
        "ultimas": ultimas,
    }


def coletar_conversas_cached():
    cache_key = "conversas"
    now = time.time()
    if cache_key in _conv_cache:
        entry = _conv_cache[cache_key]
        if now - entry["ts"] < _CONV_TTL:
            return entry["data"]

    payload = buscar_conversas_ghl(limit_total=200)
    agg = agregar_conversas(payload)
    _conv_cache[cache_key] = {"ts": now, "data": agg}
    return agg


# ============================================================
# Helpers Google Ads
# ============================================================
GADS_PRESET_MAP = {
    "semana_atual": "LAST_7_DAYS",
    "ultimos_7": "LAST_7_DAYS",
    "ultimos_30": "LAST_30_DAYS",
    "este_mes": "THIS_MONTH",
    "mes_passado": "LAST_MONTH",
}

GADS_LABEL_MAP = {
    "LAST_7_DAYS": "Ultimos 7 dias",
    "LAST_30_DAYS": "Ultimos 30 dias",
    "THIS_MONTH": "Este mes",
    "LAST_MONTH": "Mes passado",
}


def _get_gads_client():
    global _gads_client
    if _gads_client is None:
        with open(_GADS_TOKENS_PATH) as f:
            t = json.load(f)
        _gads_client = GoogleAdsClient.load_from_dict({
            "developer_token": t["developer_token"],
            "client_id": t["client_id"],
            "client_secret": t["client_secret"],
            "refresh_token": t["refresh_token"],
            "login_customer_id": t["customer_id"],
            "use_proto_plus": True,
        })
    return _gads_client


def fetch_google_ads(preset: str = "ultimos_30", custom_inicio: str = "", custom_fim: str = "") -> dict:
    """Busca metricas de campanhas do Google Ads."""
    if preset == "custom" and custom_inicio and custom_fim:
        date_clause = f"segments.date BETWEEN '{custom_inicio}' AND '{custom_fim}'"
        try:
            di = datetime.fromisoformat(custom_inicio).strftime("%d/%m/%Y")
            df = datetime.fromisoformat(custom_fim).strftime("%d/%m/%Y")
            label = f"{di} a {df}"
        except Exception:
            label = f"{custom_inicio} a {custom_fim}"
    else:
        period = GADS_PRESET_MAP.get(preset, "LAST_30_DAYS")
        date_clause = f"segments.date DURING {period}"
        label = GADS_LABEL_MAP.get(period, "Ultimos 30 dias")

    query = f"""
        SELECT campaign.name, campaign.status,
               metrics.cost_micros, metrics.impressions, metrics.clicks,
               metrics.conversions, metrics.ctr, metrics.average_cpm,
               metrics.cost_per_conversion
        FROM campaign
        WHERE {date_clause}
          AND campaign.status != 'REMOVED'
          AND metrics.cost_micros > 0
        ORDER BY metrics.cost_micros DESC
        LIMIT 20
    """

    with open(_GADS_TOKENS_PATH) as f:
        t = json.load(f)

    client = _get_gads_client()
    ga_service = client.get_service("GoogleAdsService")
    response = ga_service.search(customer_id=t["customer_id"], query=query)

    campanhas = []
    total_gasto = 0.0
    total_impressoes = 0
    total_cliques = 0
    total_conv = 0.0

    for row in response:
        gasto = row.metrics.cost_micros / 1_000_000
        impressoes = int(row.metrics.impressions)
        cliques = int(row.metrics.clicks)
        conv = float(row.metrics.conversions)

        total_gasto += gasto
        total_impressoes += impressoes
        total_cliques += cliques
        total_conv += conv

        cpl = (gasto / conv) if conv > 0 else 0.0

        campanhas.append({
            "nome": row.campaign.name,
            "status": row.campaign.status.name,
            "gasto": _fmt_brl(gasto),
            "gasto_raw": gasto,
            "impressoes": _fmt_int(impressoes),
            "cliques": cliques,
            "conversoes": int(conv),
            "cpl": _fmt_brl(cpl) if cpl > 0 else "-",
            "ctr": f"{row.metrics.ctr * 100:.2f}%",
            "cpm": _fmt_brl(row.metrics.average_cpm / 1_000_000),
        })

    cpl_total = (total_gasto / total_conv) if total_conv > 0 else 0.0
    ctr_total = (total_cliques / total_impressoes * 100) if total_impressoes > 0 else 0.0
    cpm_total = (total_gasto / total_impressoes * 1000) if total_impressoes > 0 else 0.0

    return {
        "resumo": {
            "gasto_total": _fmt_brl(total_gasto),
            "impressoes": _fmt_int(total_impressoes),
            "cliques": _fmt_int(total_cliques),
            "conversoes": int(total_conv),
            "cpl": _fmt_brl(cpl_total) if cpl_total > 0 else "-",
            "ctr": f"{ctr_total:.2f}%",
            "cpm": _fmt_brl(cpm_total),
        },
        "campanhas": campanhas,
        "periodo_label": label,
    }


def fetch_google_ads_diario(preset: str, custom_inicio: str, custom_fim: str) -> list[dict]:
    """Retorna serie diaria do Google Ads [{data, leads, gasto}]."""
    if preset == "custom" and custom_inicio and custom_fim:
        date_clause = f"segments.date BETWEEN '{custom_inicio}' AND '{custom_fim}'"
    else:
        period = GADS_PRESET_MAP.get(preset, "LAST_30_DAYS")
        date_clause = f"segments.date DURING {period}"

    query = f"""
        SELECT segments.date,
               metrics.cost_micros,
               metrics.conversions
        FROM campaign
        WHERE {date_clause}
          AND campaign.status != 'REMOVED'
        ORDER BY segments.date ASC
        LIMIT 1000
    """

    try:
        with open(_GADS_TOKENS_PATH) as f:
            t = json.load(f)
        client = _get_gads_client()
        ga_service = client.get_service("GoogleAdsService")
        response = ga_service.search(customer_id=t["customer_id"], query=query)
    except Exception:
        return []

    por_dia: dict[str, dict] = {}
    for row in response:
        d = row.segments.date  # YYYY-MM-DD
        if d not in por_dia:
            por_dia[d] = {"gasto": 0.0, "leads": 0.0}
        por_dia[d]["gasto"] += row.metrics.cost_micros / 1_000_000
        por_dia[d]["leads"] += float(row.metrics.conversions)

    serie = []
    for d in sorted(por_dia.keys()):
        try:
            dt = datetime.fromisoformat(d)
            label = dt.strftime("%d/%m")
        except Exception:
            label = d
        serie.append({
            "data": label,
            "data_iso": d,
            "gasto": round(por_dia[d]["gasto"], 2),
            "leads": int(por_dia[d]["leads"]),
        })
    return serie


def coletar_gads_cached(preset: str, custom_inicio: str, custom_fim: str):
    """Retorna (resumo, campanhas, periodo_label) com cache de 5 min."""
    cache_key = f"gads|{preset}|{custom_inicio}|{custom_fim}"
    now = time.time()
    if cache_key in _gads_cache:
        entry = _gads_cache[cache_key]
        if now - entry["ts"] < _GADS_TTL:
            return entry["resumo"], entry["campanhas"], entry["periodo_label"]

    dados = fetch_google_ads(preset, custom_inicio, custom_fim)
    _gads_cache[cache_key] = {
        "ts": now,
        "resumo": dados["resumo"],
        "campanhas": dados["campanhas"],
        "periodo_label": dados["periodo_label"],
    }
    return dados["resumo"], dados["campanhas"], dados["periodo_label"]


# ============================================================
# Context processor
# ============================================================
@app.context_processor
def inject_globals():
    pipes = []
    try:
        pipes = listar_pipelines()
    except Exception:
        pipes = []
    return {
        "pipelines_menu": pipes,
        "current_user": session.get("user"),
        "current_role": session.get("role", ""),
        "current_year": datetime.now().year,
    }


# ============================================================
# Helpers de role
# ============================================================
def _salesperson_guard():
    """Redireciona salesperson para /meus-leads. Retorna redirect ou None."""
    if session.get("role") == "salesperson":
        return redirect(url_for("meus_leads"))
    return None


# ============================================================
# Routes
# ============================================================
@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        username = (request.form.get("username") or "").strip().lower()
        password = (request.form.get("password") or "").strip()
        if check_login(username, password):
            session.permanent = True
            session["user"] = username
            role = get_user_role(username)
            session["role"] = role
            session["ghl_id"] = get_user_ghl_id(username)
            if role == "salesperson":
                return redirect(url_for("followup"))
            nxt = request.args.get("next") or url_for("index")
            return redirect(nxt)
        flash("Usuario ou senha invalidos.", "error")
    return render_template("login.html")


@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("login"))


@app.route("/")
@login_required
def index():
    guard = _salesperson_guard()
    if guard:
        return guard
    preset = request.args.get("preset", "ultimos_30")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")
    return render_template(
        "index.html",
        active="home",
        preset=preset,
        custom_inicio=custom_inicio,
        custom_fim=custom_fim,
    )


@app.route("/api/overview")
@login_required
def api_overview():
    preset = request.args.get("preset", "ultimos_30")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    cache_key = f"overview|{preset}|{custom_inicio}|{custom_fim}"
    now = time.time()
    if cache_key in _overview_cache:
        entry = _overview_cache[cache_key]
        if now - entry["ts"] < _OVERVIEW_TTL:
            return jsonify(entry["data"])

    out = {
        "ok": True,
        "preset": preset,
        "periodo_label": "",
        "meta": {"ok": False, "erro": ""},
        "google": {"ok": False, "erro": ""},
        "conversas": {"ok": False, "erro": ""},
        "pipeline": {"ok": False, "erro": ""},
    }

    # ---------- Meta Ads ----------
    try:
        resumo_meta, campanhas_meta, label_meta = coletar_meta_cached(preset, custom_inicio, custom_fim)
        serie_meta = buscar_meta_diario(preset, custom_inicio, custom_fim)
        # Top 5 campanhas por gasto (formato compacto pro grafico)
        top_camp = []
        for c in (campanhas_meta or [])[:5]:
            top_camp.append({
                "nome": c.get("nome") or "-",
                "leads": int(c.get("leads") or 0),
                "gasto": float(c.get("gasto_raw") or 0),
            })
        out["periodo_label"] = label_meta
        out["meta"] = {
            "ok": True,
            "resumo": resumo_meta,
            "serie_diaria": serie_meta,
            "top_campanhas": top_camp,
        }
    except Exception as e:
        app.logger.warning(f"overview meta: {e}")
        out["meta"] = {"ok": False, "erro": str(e)}

    # ---------- Google Ads ----------
    try:
        resumo_g, _camp_g, label_g = coletar_gads_cached(preset, custom_inicio, custom_fim)
        serie_g = fetch_google_ads_diario(preset, custom_inicio, custom_fim)
        if not out["periodo_label"]:
            out["periodo_label"] = label_g
        out["google"] = {
            "ok": True,
            "resumo": resumo_g,
            "serie_diaria": serie_g,
        }
    except Exception as e:
        app.logger.warning(f"overview google: {e}")
        out["google"] = {"ok": False, "erro": str(e)}

    # ---------- Conversas ----------
    try:
        conv = coletar_conversas_cached()
        # Renomear chaves de canal pra labels amigaveis
        canais_label = {
            "TYPE_WHATSAPP": "WhatsApp",
            "TYPE_INSTAGRAM": "Instagram",
            "TYPE_FB_MESSENGER": "Messenger",
            "TYPE_SMS": "SMS",
            "TYPE_PHONE": "Telefone",
        }
        por_canal = []
        for k, v in (conv.get("por_tipo") or {}).items():
            por_canal.append({"canal": canais_label.get(k, k), "qtd": int(v)})
        por_canal.sort(key=lambda x: -x["qtd"])
        out["conversas"] = {
            "ok": True,
            "total": conv.get("total", 0),
            "nao_lidas": conv.get("nao_lidas", 0),
            "por_canal": por_canal,
        }
    except Exception as e:
        app.logger.warning(f"overview conversas: {e}")
        out["conversas"] = {"ok": False, "erro": str(e)}

    # ---------- Pipeline (PADRAO PX3 se existir, senao primeira) ----------
    try:
        import unicodedata as _ud

        def _norm(s: str) -> str:
            return "".join(
                c for c in _ud.normalize("NFKD", s or "") if not _ud.combining(c)
            ).lower()

        pipes = listar_pipelines()
        if pipes:
            primeiro = None
            for pp in pipes:
                n = _norm(pp["name"])
                if "padrao" in n and "px3" in n:
                    primeiro = pp
                    break
            if not primeiro:
                primeiro = pipes[0]
            _d, agg, _ini, _fim, label_p = coletar_cached(
                primeiro["id"], preset, custom_inicio, custom_fim
            )
            if agg is not None:
                stages_rows = []
                total_stage = sum(agg["contagem_stage"].values()) or 1
                for k, v in agg["contagem_stage"].items():
                    stages_rows.append({
                        "stage": k,
                        "qtd": v,
                        "pct": round(v / total_stage * 100, 1),
                    })
                stages_rows.sort(key=lambda x: -x["qtd"])
                if not out["periodo_label"]:
                    out["periodo_label"] = label_p
                out["pipeline"] = {
                    "ok": True,
                    "id": primeiro["id"],
                    "nome": primeiro["name"],
                    "kpis": {
                        "novos": agg["novos"],
                        "qualificados": agg["qualificados"],
                        "fechamentos": agg["fechamentos"],
                        "perdidos": agg["perdidos"],
                        "taxa_qual": round(agg["taxa_qual"], 1),
                        "total": agg["total"],
                    },
                    "stages": stages_rows[:8],
                }
            else:
                out["pipeline"] = {"ok": False, "erro": "Sem dados"}
        else:
            out["pipeline"] = {"ok": False, "erro": "Nenhuma pipeline"}
    except Exception as e:
        app.logger.warning(f"overview pipeline: {e}")
        out["pipeline"] = {"ok": False, "erro": str(e)}

    _overview_cache[cache_key] = {"ts": now, "data": out}
    return jsonify(out)


@app.route("/pipelines")
@login_required
def pipelines_list():
    guard = _salesperson_guard()
    if guard:
        return guard
    pipes = listar_pipelines()
    # Redireciona pra primeira pipeline se existir
    if pipes:
        return redirect(url_for("pipeline_detail", pipeline_id=pipes[0]["id"]))
    return render_template(
        "pipelines.html",
        active="pipelines",
        pipeline=None,
        agg=None,
        periodo_label="",
        preset="semana_atual",
        custom_inicio="",
        custom_fim="",
    )


@app.route("/pipelines/<pipeline_id>")
@login_required
def pipeline_detail(pipeline_id):
    guard = _salesperson_guard()
    if guard:
        return guard
    pipe = get_pipeline(pipeline_id)
    if not pipe:
        flash("Pipeline nao encontrada.", "error")
        return redirect(url_for("pipelines_list"))

    preset = request.args.get("preset", "semana_atual")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    return render_template(
        "pipelines.html",
        active="pipelines",
        active_pipeline_id=pipe["id"],
        pipeline=pipe,
        preset=preset,
        custom_inicio=custom_inicio,
        custom_fim=custom_fim,
    )


@app.route("/api/pipeline/<pipeline_id>")
@login_required
def api_pipeline(pipeline_id):
    preset = request.args.get("preset", "semana_atual")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    pipe = get_pipeline(pipeline_id)
    if not pipe:
        return jsonify({"ok": False, "erro": "Pipeline nao encontrada"}), 404

    try:
        _dados, agg, inicio_utc, fim_utc, label = coletar_cached(
            pipeline_id, preset, custom_inicio, custom_fim
        )
    except Exception as e:
        app.logger.exception("erro api_pipeline")
        return jsonify({"ok": False, "erro": str(e)}), 500

    inicio_br = inicio_utc.astimezone(TZ_BR)
    fim_br = fim_utc.astimezone(TZ_BR)

    stages_rows = [
        {"stage": k, "qtd": v, "pct": round(v / max(sum(agg["contagem_stage"].values()), 1) * 100, 1)}
        for k, v in agg["contagem_stage"].items()
    ]

    return jsonify({
        "ok": True,
        "periodo_label": label,
        "inicio_br": inicio_br.strftime("%d/%m/%Y %H:%M"),
        "fim_br": fim_br.strftime("%d/%m/%Y %H:%M"),
        "kpis": {
            "novos": agg["novos"],
            "qualificados": agg["qualificados"],
            "fechamentos": agg["fechamentos"],
            "perdidos": agg["perdidos"],
            "taxa_qual": round(agg["taxa_qual"], 1),
            "total": agg["total"],
        },
        "stages": stages_rows,
        "linhas": agg["linhas"],
    })


@app.route("/meta-ads")
@login_required
def meta_ads():
    guard = _salesperson_guard()
    if guard:
        return guard
    preset = request.args.get("preset", "ultimos_7")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")
    return render_template(
        "meta_ads.html",
        active="meta_ads",
        section_title="Meta Ads",
        preset=preset,
        custom_inicio=custom_inicio,
        custom_fim=custom_fim,
    )


@app.route("/api/meta-ads")
@login_required
def api_meta_ads():
    preset = request.args.get("preset", "ultimos_7")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    try:
        resumo, campanhas, periodo_label = coletar_meta_cached(preset, custom_inicio, custom_fim)
    except Exception as e:
        app.logger.exception("erro api_meta_ads")
        return jsonify({"ok": False, "erro": str(e)}), 500

    return jsonify({
        "ok": True,
        "periodo_label": periodo_label,
        "resumo": resumo,
        "campanhas": campanhas,
    })


@app.route("/google-ads")
@login_required
def google_ads():
    guard = _salesperson_guard()
    if guard:
        return guard
    preset = request.args.get("preset", "ultimos_30")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")
    return render_template(
        "google_ads.html",
        active="google_ads",
        section_title="Google Ads",
        preset=preset,
        custom_inicio=custom_inicio,
        custom_fim=custom_fim,
    )


@app.route("/api/google-ads")
@login_required
def api_google_ads():
    preset = request.args.get("preset", "ultimos_30")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    try:
        resumo, campanhas, periodo_label = coletar_gads_cached(preset, custom_inicio, custom_fim)
    except Exception as e:
        app.logger.exception("erro api_google_ads")
        return jsonify({"ok": False, "erro": str(e)}), 500

    return jsonify({
        "ok": True,
        "periodo_label": periodo_label,
        "resumo": resumo,
        "campanhas": campanhas,
    })


@app.route("/conversas")
@login_required
def conversas():
    guard = _salesperson_guard()
    if guard:
        return guard
    return render_template("conversas.html", active="conversas", section_title="Conversas")


@app.route("/api/conversas")
@login_required
def api_conversas():
    try:
        data = coletar_conversas_cached()
    except Exception as e:
        app.logger.exception("erro api_conversas")
        return jsonify({"ok": False, "erro": str(e)}), 500
    return jsonify({"ok": True, **data})


@app.route("/api/conversa/<conv_id>/mensagens")
@login_required
def api_conversa_mensagens(conv_id):
    url = f"{GHL_BASE}/conversations/{conv_id}/messages"
    try:
        r = requests.get(url, headers=CONV_HEADERS, params={"limit": 100}, timeout=20)
    except Exception as e:
        app.logger.exception("erro api_conversa_mensagens")
        return jsonify({"ok": False, "erro": str(e)}), 500

    if r.status_code != 200:
        return jsonify({"ok": False, "erro": r.text[:200]}), 500

    msgs = r.json().get("messages", {}).get("messages", []) or []
    # Ordenar por dateAdded crescente (mais antiga primeiro)
    def _ts(m):
        d = m.get("dateAdded") or ""
        dt = rel.parse_iso_utc(d)
        return dt.timestamp() if dt else 0

    msgs.sort(key=_ts)

    # Regex para extrair prefixo tipo "[MARY ← LEAD]: " ou "[LEAD → MARY]: "
    _PREFIX_RE = re.compile(r'^\[([^\]]+)\]:\s*', re.UNICODE)

    resultado = []
    for m in msgs:
        tipo = m.get("messageType", "") or m.get("type", "")
        direcao = m.get("direction", "")  # inbound = lead, outbound = atendente
        corpo = m.get("body", "") or m.get("text", "") or ""
        if not corpo.strip():
            continue
        # Extrair remetente do prefixo e limpar o corpo
        remetente = ""
        m_prefix = _PREFIX_RE.match(corpo)
        if m_prefix:
            remetente = m_prefix.group(1).strip()
            corpo = corpo[m_prefix.end():].strip()
        d_str = m.get("dateAdded") or ""
        dt_utc = rel.parse_iso_utc(d_str)
        if dt_utc:
            data_fmt = dt_utc.astimezone(TZ_BR).strftime("%d/%m %H:%M")
        else:
            data_fmt = ""
        resultado.append({
            "direcao": direcao,  # "inbound" ou "outbound"
            "corpo": corpo,
            "remetente": remetente,
            "data": data_fmt,
            "tipo": tipo,
        })

    return jsonify({"ok": True, "mensagens": resultado})


@app.route("/rastreamento")
@login_required
def rastreamento():
    guard = _salesperson_guard()
    if guard:
        return guard

    # Preset do periodo pros gastos (defaults conservadores)
    preset = request.args.get("preset", "ultimos_30")
    custom_inicio = request.args.get("inicio", "")
    custom_fim = request.args.get("fim", "")

    # Cliques (JSONL local)
    clicks = tracking.carregar_clicks()
    agg_clicks = tracking.agregar_clicks(clicks)

    pageviews = tracking.carregar_pageviews()
    agg_pvs = tracking.agregar_pageviews(pageviews)

    # Funil GHL: pipeline "PADRAO PX3" (ou primeira disponivel)
    funil = {"pipeline_nome": "", "stages": [], "total": 0, "erro": ""}
    try:
        import unicodedata

        def _norm(s: str) -> str:
            return "".join(
                c for c in unicodedata.normalize("NFKD", s or "") if not unicodedata.combining(c)
            ).lower()

        pipes = listar_pipelines()
        alvo = None
        for p in pipes:
            n = _norm(p["name"])
            if "padrao" in n and "px3" in n:
                alvo = p
                break
        if not alvo and pipes:
            alvo = pipes[0]

        if alvo:
            opps = buscar_opps_pipeline(alvo["id"])
            stage_count: dict[str, int] = {}
            for op in opps:
                sid = op.get("pipelineStageId", "")
                sname = alvo["stages_map"].get(sid, "SEM STAGE")
                status = (op.get("status") or "").lower()
                if status == "lost":
                    sname += " (perdido)"
                elif status == "won":
                    sname += " (ganho)"
                stage_count[sname] = stage_count.get(sname, 0) + 1

            total_op = sum(stage_count.values())
            stages_rows = [
                {"stage": k, "qtd": v, "pct": (v / total_op * 100) if total_op else 0}
                for k, v in stage_count.items()
            ]
            stages_rows.sort(key=lambda x: -x["qtd"])
            funil = {
                "pipeline_nome": alvo["name"],
                "stages": stages_rows,
                "total": total_op,
                "erro": "",
            }
    except Exception as e:
        app.logger.exception("erro funil rastreamento")
        funil["erro"] = str(e)

    # ============================================================
    # Atribuicao real: contatos GHL x tags de compra x gasto ads
    # ============================================================
    atribuicao = {"por_canal": {}, "canais": [], "serie_diaria": [],
                  "total": {}, "tempos_globais": {}, "erro": ""}
    cpa_real = {"meta": {}, "google": {}, "total": {}, "erro": ""}
    periodo_label_ads = ""
    try:
        atribuicao = tracking.coletar_atribuicao_cached(HEADERS, PX3_LOCATION_ID)
    except Exception as e:
        app.logger.exception("erro atribuicao rastreamento")
        atribuicao = {"por_canal": {}, "canais": [], "serie_diaria": [],
                      "total": {}, "tempos_globais": {}, "erro": str(e)}

    gasto_meta = 0.0
    gasto_google = 0.0
    try:
        resumo_meta, _c, periodo_label_ads = coletar_meta_cached(preset, custom_inicio, custom_fim)
        # resumo_meta['gasto_total'] vem como "R$ 1.234,56" -> pegamos o raw somando campanhas
        # Melhor recalcular pelo raw:
        insights = buscar_meta_insights(preset, custom_inicio, custom_fim)
        for c in insights.get("campanhas_raw", []) or []:
            try:
                gasto_meta += float(c.get("spend") or 0)
            except Exception:
                pass
    except Exception as e:
        app.logger.warning(f"nao pegou gasto Meta: {e}")

    try:
        gads = fetch_google_ads(preset, custom_inicio, custom_fim)
        for c in gads.get("campanhas", []) or []:
            try:
                gasto_google += float(c.get("gasto_raw") or 0)
            except Exception:
                pass
        if not periodo_label_ads:
            periodo_label_ads = gads.get("periodo_label", "")
    except Exception as e:
        app.logger.warning(f"nao pegou gasto Google Ads: {e}")

    try:
        cpa_real = tracking.calcular_cpa_real(atribuicao, gasto_meta, gasto_google)
        cpa_real["erro"] = ""
    except Exception as e:
        cpa_real = {"meta": {}, "google": {}, "total": {}, "erro": str(e)}

    # Labels amigaveis para o front
    canal_label_map = {
        "meta": "Meta (Facebook/Instagram)",
        "google": "Google Ads",
        "whatsapp": "WhatsApp",
        "crm": "CRM / Manual",
        "direto": "Direto / Organico",
    }
    canal_cores = {
        "meta": "#4267B2",
        "google": "#EA4335",
        "whatsapp": "#25D366",
        "crm": "#8B5CF6",
        "direto": "#94A3B8",
    }

    return render_template(
        "rastreamento.html",
        active="rastreamento",
        section_title="Rastreamento",
        agg_clicks=agg_clicks,
        agg_pvs=agg_pvs,
        funil=funil,
        atribuicao=atribuicao,
        cpa_real=cpa_real,
        canal_label_map=canal_label_map,
        canal_cores=canal_cores,
        gasto_meta=gasto_meta,
        gasto_google=gasto_google,
        periodo_label_ads=periodo_label_ads or "-",
        preset=preset,
        custom_inicio=custom_inicio,
        custom_fim=custom_fim,
        base_url=request.host_url.rstrip("/"),
    )


@app.route("/api/rastreamento/atribuicao")
@login_required
def api_rastreamento_atribuicao():
    """Endpoint JSON pro front consumir/atualizar (cache de 30 min)."""
    guard = _salesperson_guard()
    if guard:
        return jsonify({"error": "forbidden"}), 403
    force = request.args.get("refresh") == "1"
    try:
        data = tracking.coletar_atribuicao_cached(HEADERS, PX3_LOCATION_ID, force=force)
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================================================
# Follow-up de leads (SQLite followup_notif)
# ============================================================
FOLLOWUP_DB = "/opt/mia/workspace/clientes/px3lab/followup_notif/followup.db"

FOLLOWUP_TAG_LABELS = {
    "nutricao_cloud_photorf": "Nutricao Cloud PhotoRF",
    "iniciou_fluxo_compra_cloud": "Iniciou Fluxo de Compra Cloud",
}

FOLLOWUP_DELAYS = {
    "nutricao_cloud_photorf": [1, 24, 36, 48, 120, 240, 360, 600, 960, 1440],
    "iniciou_fluxo_compra_cloud": [24, 36, 48, 120, 240, 360, 600, 960, 1440],
}

GHL_USER_NAMES = {
    "FKCKhrtdwZjHS6ISgAFF": "Monique",
    "n2TXqcFucc2mSfK5p0Do": "Mary",
    "3egsMwQ3G5rHFHVhp555": "Vinicius",
    "0ZFCVED2VANUkXjfB4tL": "Naiane",
}


def _parse_dt_utc(value: str):
    """Parse timestamps salvos pelo followup_notif (isoformat, com ou sem tz)."""
    if not value:
        return None
    s = str(value).strip()
    if not s:
        return None
    # SQLite CURRENT_TIMESTAMP volta como "YYYY-MM-DD HH:MM:SS"
    s = s.replace(" ", "T")
    if s.endswith("Z"):
        s = s[:-1] + "+00:00"
    try:
        dt = datetime.fromisoformat(s)
    except ValueError:
        return None
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=TZ_UTC)
    return dt.astimezone(TZ_UTC)


def _format_elapsed(hours: float) -> str:
    total_min = max(int(round(hours * 60)), 0)
    h, m = divmod(total_min, 60)
    if h >= 48:
        d = h // 24
        rem_h = h % 24
        if rem_h:
            return f"{d}d {rem_h}h"
        return f"{d} dias"
    return f"{h}h {m:02d}m"


def _format_delay(delay_h) -> str:
    if delay_h is None:
        return "-"
    try:
        h = int(delay_h)
    except (TypeError, ValueError):
        return "-"
    if h >= 48:
        d, rem = divmod(h, 24)
        if rem:
            return f"{d}d {rem}h"
        return f"{d} dias"
    return f"{h}h"


def _semaforo(elapsed_h: float) -> str:
    if elapsed_h >= 48:
        return "red"
    if elapsed_h >= 24:
        return "yellow"
    return "green"


@app.route("/followup")
@login_required
def followup():
    return render_template(
        "followup.html",
        active="followup",
        section_title="Follow-up",
        user_role=session.get("role", "salesperson"),
    )


@app.route("/api/followup")
@login_required
def api_followup():
    role = session.get("role", "salesperson")
    ghl_id = session.get("ghl_id")

    rows_out = []
    try:
        conn = _sqlite3.connect(FOLLOWUP_DB, timeout=15.0)
        conn.row_factory = _sqlite3.Row
        try:
            events = conn.execute(
                """
                SELECT id, contact_id, contact_name, contact_phone,
                       assigned_user_id, tag, tag_added_at
                  FROM events
                """
            ).fetchall()

            notif_rows = conn.execute(
                "SELECT event_id, delay_hours FROM notifications_sent"
            ).fetchall()
        finally:
            conn.close()
    except _sqlite3.OperationalError as e:
        # DB pode nao existir ainda -> devolve lista vazia sem quebrar
        app.logger.warning(f"api_followup DB indisponivel: {e}")
        events, notif_rows = [], []
    except Exception as e:
        app.logger.exception("erro api_followup")
        return jsonify({"ok": False, "erro": str(e)}), 500

    # Indexa notificacoes por event_id -> set(delay_hours)
    sent_by_event: dict = {}
    for nr in notif_rows:
        eid = nr["event_id"]
        sent_by_event.setdefault(eid, set()).add(int(nr["delay_hours"]))

    now_utc = datetime.now(TZ_UTC)

    for ev in events:
        assigned = ev["assigned_user_id"] or ""
        # Filtro por role
        if role == "salesperson":
            if not ghl_id or assigned != ghl_id:
                continue

        tag = ev["tag"] or ""
        tag_added = _parse_dt_utc(ev["tag_added_at"])
        if not tag_added:
            continue

        elapsed_hours = (now_utc - tag_added).total_seconds() / 3600.0
        if elapsed_hours < 0:
            elapsed_hours = 0.0

        delays = FOLLOWUP_DELAYS.get(tag, [])
        enviados = sent_by_event.get(ev["id"], set())
        pendentes = [d for d in delays if d not in enviados]
        proxima = min(pendentes) if pendentes else None
        ultima = max(enviados) if enviados else None

        rows_out.append({
            "id": ev["id"],
            "contact_name": ev["contact_name"] or "Sem nome",
            "contact_phone": ev["contact_phone"] or "",
            "tag": tag,
            "tag_label": FOLLOWUP_TAG_LABELS.get(tag, tag),
            "tag_added_at": tag_added.isoformat(),
            "elapsed_hours": round(elapsed_hours, 2),
            "elapsed_label": _format_elapsed(elapsed_hours),
            "semaforo": _semaforo(elapsed_hours),
            "proxima_notif_h": proxima,
            "proxima_notif_label": _format_delay(proxima) if proxima is not None else "Concluido",
            "ultima_notif_h": ultima,
            "ultima_notif_label": _format_delay(ultima) if ultima is not None else "-",
            "assigned_user_id": assigned,
            "assigned_name": (GHL_USER_NAMES.get(assigned) if role == "manager" else None),
        })

    rows_out.sort(key=lambda r: -r["elapsed_hours"])

    return jsonify({
        "ok": True,
        "rows": rows_out,
        "ts": int(time.time()),
        "role": role,
    })


@app.route("/meus-leads")
@login_required
def meus_leads():
    # Manager ja tem visao completa no /followup
    if session.get("role") == "manager":
        return redirect(url_for("followup"))
    return render_template(
        "meus_leads.html",
        active="meus_leads",
        section_title="Meus Leads",
        user_role=session.get("role", "salesperson"),
        username=session.get("user", ""),
    )


@app.route("/api/meus-leads")
@login_required
def api_meus_leads():
    """Sempre filtra pelo ghl_id do usuario logado, independente do role."""
    username = session.get("user", "")
    ghl_id = session.get("ghl_id")

    rows_out = []
    try:
        conn = _sqlite3.connect(FOLLOWUP_DB, timeout=15.0)
        conn.row_factory = _sqlite3.Row
        try:
            events = conn.execute(
                """
                SELECT id, contact_id, contact_name, contact_phone,
                       assigned_user_id, tag, tag_added_at
                  FROM events
                """
            ).fetchall()

            notif_rows = conn.execute(
                "SELECT event_id, delay_hours FROM notifications_sent"
            ).fetchall()
        finally:
            conn.close()
    except _sqlite3.OperationalError as e:
        app.logger.warning(f"api_meus_leads DB indisponivel: {e}")
        events, notif_rows = [], []
    except Exception as e:
        app.logger.exception("erro api_meus_leads")
        return jsonify({"ok": False, "erro": str(e)}), 500

    sent_by_event: dict = {}
    for nr in notif_rows:
        eid = nr["event_id"]
        sent_by_event.setdefault(eid, set()).add(int(nr["delay_hours"]))

    now_utc = datetime.now(TZ_UTC)

    for ev in events:
        assigned = ev["assigned_user_id"] or ""
        # SEMPRE filtra pelo ghl_id do usuario logado
        if not ghl_id or assigned != ghl_id:
            continue

        tag = ev["tag"] or ""
        tag_added = _parse_dt_utc(ev["tag_added_at"])
        if not tag_added:
            continue

        elapsed_hours = (now_utc - tag_added).total_seconds() / 3600.0
        if elapsed_hours < 0:
            elapsed_hours = 0.0

        delays = FOLLOWUP_DELAYS.get(tag, [])
        enviados = sent_by_event.get(ev["id"], set())
        pendentes = [d for d in delays if d not in enviados]
        proxima = min(pendentes) if pendentes else None
        ultima = max(enviados) if enviados else None

        rows_out.append({
            "id": ev["id"],
            "contact_name": ev["contact_name"] or "Sem nome",
            "contact_phone": ev["contact_phone"] or "",
            "tag": tag,
            "tag_label": FOLLOWUP_TAG_LABELS.get(tag, tag),
            "tag_added_at": tag_added.isoformat(),
            "elapsed_hours": round(elapsed_hours, 2),
            "elapsed_label": _format_elapsed(elapsed_hours),
            "semaforo": _semaforo(elapsed_hours),
            "proxima_notif_h": proxima,
            "proxima_notif_label": _format_delay(proxima) if proxima is not None else "Concluido",
            "ultima_notif_h": ultima,
            "ultima_notif_label": _format_delay(ultima) if ultima is not None else "-",
        })

    rows_out.sort(key=lambda r: -r["elapsed_hours"])

    resumo = {
        "total": len(rows_out),
        "red":    sum(1 for r in rows_out if r["semaforo"] == "red"),
        "yellow": sum(1 for r in rows_out if r["semaforo"] == "yellow"),
        "green":  sum(1 for r in rows_out if r["semaforo"] == "green"),
    }

    return jsonify({
        "ok": True,
        "vendedor": username,
        "rows": rows_out,
        "resumo": resumo,
        "ts": int(time.time()),
    })


@app.route("/healthz")
def healthz():
    return jsonify({"ok": True, "ts": int(time.time())})


# ============================================================
# Cache warmer (background pre-aquece dados antes do TTL expirar)
# ============================================================
def _warm_all_caches():
    """Roda em background a cada 4 min para manter caches sempre quentes."""
    try:
        coletar_conversas_cached()
    except Exception:
        traceback.print_exc()

    presets = ["semana_atual", "ultimos_7", "ultimos_30", "este_mes", "mes_passado"]

    for preset in presets:
        try:
            coletar_meta_cached(preset, "", "")
        except Exception:
            traceback.print_exc()
        try:
            coletar_gads_cached(preset, "", "")
        except Exception:
            traceback.print_exc()

    try:
        pipes = listar_pipelines()
        for p in pipes:
            for preset in ["semana_atual", "ultimos_30", "este_mes"]:
                try:
                    coletar_cached(p["id"], preset, "", "")
                except Exception:
                    traceback.print_exc()
    except Exception:
        traceback.print_exc()


def _start_cache_warmer():
    def _loop():
        while True:
            time.sleep(240)  # 4 min
            try:
                _warm_all_caches()
            except Exception:
                traceback.print_exc()

    t = threading.Thread(target=_loop, daemon=True, name="cache-warmer-loop")
    t.start()
    # Warm imediatamente no startup (em thread separada pra nao bloquear o boot)
    threading.Thread(target=_warm_all_caches, daemon=True, name="cache-warmer-initial").start()


_start_cache_warmer()


# ============================================================
# Entrypoint
# ============================================================
if __name__ == "__main__":
    port = int(os.environ.get("PORT", 8915))
    app.run(host="0.0.0.0", port=port, debug=False)