#!/usr/bin/env python3
"""
sync_chatwoot_response_times.py
================================
Sincroniza TEMPO DE RESPOSTA real do Chatwoot PostgreSQL → relatórios HTML.

O que faz:
  1. Consulta o tempo médio de primeira resposta por agente no Chatwoot
  2. Atualiza o card "Tempo de Resposta do Corretor" em cada relatorio_*.html

Uso:
    python3 sync_chatwoot_response_times.py          # todos os relatórios
    python3 sync_chatwoot_response_times.py dry=1    # só mostra o que mudaria
    python3 sync_chatwoot_response_times.py force=1 # força update mesmo se igual

Cron suggested (a cada 30 min):
    */30 * * * * docker exec root-postgres-1 psql -U postgres -d chatwoot_production -t --csv -c "SELECT u.name, COUNT(*), AVG(EXTRACT(EPOCH FROM (c.first_reply_created_at - c.created_at))) FROM conversations c JOIN users u ON u.id = c.assignee_id WHERE c.first_reply_created_at IS NOT NULL AND c.assignee_id IS NOT NULL GROUP BY u.id, u.name ORDER BY u.name;" > /tmp/cw_response_times.csv 2>&1

Depends: psycopg2-binary (ou usa subprocess + docker exec)
VPS: root@31.97.243.106
"""

import os, sys, re, subprocess
from datetime import datetime

# ─── CONFIG ──────────────────────────────────────────────────────────────────
VPS_HOST = "31.97.243.106"
VPS_PASS = "Marcia19671951@"
REPORTS_DIR = "/var/www/hermes-insights"

DRY_MODE = "dry=1" in sys.argv or os.getenv("DRY") == "1"
FORCE = "force=1" in sys.argv or os.getenv("FORCE") == "1"

# ─── HELPERS ─────────────────────────────────────────────────────────────────

def ssh(cmd, timeout=30):
    """Executa comando no VPS via sshpass."""
    full = ["sshpass", "-p", VPS_PASS, "ssh", "-o", "StrictHostKeyChecking=no",
            "-o", f"ConnectTimeout={timeout}", f"root@{VPS_HOST}", cmd]
    r = subprocess.run(full, capture_output=True, text=True, timeout=timeout + 5)
    return r.stdout, r.stderr, r.returncode


def fmt_time(sec):
    """Formata segundos em string legível: 58s, 3min, 2.5h, 1.2d."""
    if sec is None or sec <= 0 or (sec != sec):  # NaN check
        return "—"
    if sec < 60:
        return f"{sec:.0f}s"
    if sec < 3600:
        return f"{sec/60:.0f}min"
    if sec < 86400:
        return f"{sec/3600:.1f}h"
    return f"{sec/86400:.1f}d"


def get_chatwoot_response_times():
    """Busca tempo médio de resposta por agente direto do PostgreSQL do Chatwoot."""
    sql = (
        "SELECT u.name, COUNT star,"
        " AVG(EXTRACT(EPOCH FROM c.first_reply_created_at - c.created_at)) "
        "FROM conversations c "
        "JOIN users u ON u.id = c.assignee_id "
        "WHERE c.first_reply_created_at IS NOT NULL "
        "  AND c.assignee_id IS NOT NULL "
        "  AND c.created_at > '2026-01-01' "
        "GROUP BY u.id, u.name ORDER BY u.name;"
    ).replace("COUNT star", "COUNT(*)")
    
    # Use single-string SSH to avoid subprocess list issues with parentheses
    cmd = (
        f"sshpass -p '{VPS_PASS}' ssh -o StrictHostKeyChecking=no "
        f"root@{VPS_HOST} \"docker exec root-postgres-1 psql -U postgres "
        f"-d chatwoot_production -t --csv -c \\\"{sql}\\\"\""
    )
    r = subprocess.run(cmd, shell=True, capture_output=True, text=True, timeout=40)
    stdout = r.stdout
    stderr = r.stderr
    rc = r.returncode
    if rc != 0:
        print(f"[ERRO] DB: {r.stderr[:200]}")
        return {}

    times = {}
    for line in stdout.strip().split("\n"):
        line = line.strip()
        if not line:
            continue
        parts = line.split(",")
        if len(parts) >= 3:
            name = parts[0].strip('"')
            try:
                avg_sec = float(parts[2].strip())
                times[name] = avg_sec
            except (ValueError, IndexError):
                pass
    return times


def get_report_agents():
    """Lista todos os relatorio_*.html e extrai o nome do agente."""
    import glob
    agents = {}
    for path in glob.glob(f"{REPORTS_DIR}/relatorio_*.html"):
        fname = os.path.basename(path)
        # relatorio_NOME.html
        m = re.match(r"relatorio_(.+)\.html$", fname)
        if not m:
            continue
        agent_slug = m.group(1).lower().replace("_", " ").replace("%20", " ")
        # Read agent name from HTML <h1>
        try:
            with open(path) as f:
                c = f.read()
            h1 = re.search(r"<h1[^>]*>Relatório Comercial - ([^<]+)</h1>", c)
            if h1:
                agent_name = h1.group(1).strip()
            else:
                # Try alternate format
                h1 = re.search(r"<h1[^>]*>Relatorio Comercial - ([^<]+)</h1>", c)
                agent_name = h1.group(1).strip() if h1 else agent_slug
        except Exception:
            agent_name = agent_slug
        agents[agent_name] = {"slug": m.group(1), "path": path}
    return agents


def name_match(report_name, chatwoot_name):
    """Match flexível entre nome do relatório e nome do Chatwoot."""
    r = report_name.lower().strip()
    c = chatwoot_name.lower().strip()
    # Exact match
    if r == c:
        return True
    # Slug comparison (remove accents, spaces, etc.)
    def slug(n):
        return re.sub(r"[^a-z]", "", n.replace(" ", "").replace("-", ""))
    if slug(r) == slug(c):
        return True
    # Partial match (one contains the other, and length is similar)
    if len(r) > 3 and len(c) > 3:
        if r in c or c in r:
            return True
    return False


def update_report_tempo(report_path, new_value):
    """Atualiza o card de Tempo de Resposta em um relatório HTML."""
    with open(report_path) as f:
        c = f.read()

    original = c

    # Find the "Tempo de Resposta do Corretor" card block
    # It's a <div style="text-align:center;background:#0d0d0f;padding:16px;border-radius:10px;">
    # followed by font-size:32px value, then the label, then the description

    # Find the tempo card by looking for the label
    label_pos = c.find("Tempo de Resposta do Corretor")
    if label_pos < 0:
        label_pos = c.find("Tempo de Resposta Médio")
    if label_pos < 0:
        label_pos = c.find("Tempo Medio")

    if label_pos < 0:
        return False, "Label não encontrado"

    # Walk backward to find the start of the card div
    card_start = c.rfind('<div style="text-align:center;background:#0d0d0f;padding:16px;border-radius:10px;">', 0, label_pos)
    if card_start < 0:
        return False, "Card div não encontrado"

    # Walk forward to find the end of this card (next card or closing </div>)
    # Find the next occurrence of same pattern or end of grid
    search_from = label_pos
    # The card ends at the next <div style="text-align:center;... or at </div> of the grid
    next_card = c.find('<div style="text-align:center;background:#0d0d0f;padding:16px;border-radius:10px;">', search_from + 1)
    grid_end = c.find("</div>", label_pos)

    # Whichever comes first
    if next_card > 0 and (grid_end < 0 or next_card < grid_end):
        card_end = next_card
    elif grid_end > 0:
        card_end = grid_end + 6  # len("</div>")
    else:
        card_end = label_pos + 500

    old_card = c[card_start:card_end]

    # Replace the numeric value (font-size:32px) in the card
    # Pattern: <div style="font-size:32px;font-weight:800;color:#c9a96e">VALUE</div>
    new_card = re.sub(
        r'(<div style="font-size:32px;font-weight:800;color:#c9a96e">)[^<]+(</div>)',
        r'\g<1>' + new_value + r'\g<2>',
        old_card
    )

    if old_card == new_card:
        return False, "Valor já está correto"

    c = c[:card_start] + new_card + c[card_end:]
    with open(report_path, 'w') as f:
        f.write(c)
    return True, "OK"


# ─── MAIN ─────────────────────────────────────────────────────────────────────

def main():
    print(f"[{datetime.now().strftime('%H:%M:%S')}] === Chatwoot Response Time Sync ===")
    print(f"Modo: {'DRY (só mostra)' if DRY_MODE else 'LIVE'}")
    print()

    # Step 1: Get real times from Chatwoot
    print("1. Consultando Chatwoot PostgreSQL...")
    times = get_chatwoot_response_times()
    if not times:
        print("   AVISO: Nenhum dado retornado do Chatwoot")
    else:
        print(f"   {len(times)} agentes encontrados:")
        for name, sec in sorted(times.items(), key=lambda x: x[1]):
            print(f"     {name:30s} → {fmt_time(sec)}")

    # Step 2: Get all reports
    print()
    print("2. Verificando relatórios...")
    agents = get_report_agents()
    print(f"   {len(agents)} relatórios encontrados")

    # Step 3: Match and update
    print()
    print("3. Atualizando tempos de resposta...")

    changes = []
    for agent_name, info in agents.items():
        path = info["path"]

        # Find matching Chatwoot agent
        matched_cw_name = None
        for cw_name, sec in times.items():
            if name_match(agent_name, cw_name):
                matched_cw_name = cw_name
                matched_sec = sec
                break

        if matched_cw_name:
            new_value = fmt_time(matched_sec)
        else:
            new_value = None

        # Read current value
        with open(path) as f:
            c = f.read()

        label_pos = c.find("Tempo de Resposta")
        if label_pos < 0:
            print(f"   — {agent_name}: sem card de tempo de resposta")
            continue

        # Extract current value
        card_search = c[max(0, label_pos-500):label_pos+100]
        current_m = re.search(r'font-size:32px;font-weight:800;color:#c9a96e">([^<]+)<', card_search)
        current_value = current_m.group(1) if current_m else "?"

        if new_value is None:
            action = f"   — {agent_name}: sem dado no Chatwoot (mantém: {current_value})"
            print(action)
            continue

        if current_value == new_value and not FORCE:
            action = f"   ✓ {agent_name}: já correto ({new_value})"
            print(action)
        else:
            if DRY_MODE:
                action = f"   [DRY] {agent_name}: {current_value} → {new_value}"
            else:
                ok, msg = update_report_tempo(path, new_value)
                if ok:
                    action = f"   ✓ {agent_name}: {current_value} → {new_value}"
                    changes.append((agent_name, current_value, new_value))
                else:
                    action = f"   ✗ {agent_name}: {msg}"
            print(action)

    # Step 4: Upload changed files to VPS
    if not DRY_MODE and changes:
        print()
        print("4. Fazendo upload para VPS...")
        import glob
        for agent_name, _, _ in changes:
            # Find path for this agent
            for an, info in agents.items():
                if an == agent_name:
                    slug = info["slug"]
                    local = info["path"]
                    remote = f"root@{VPS_HOST}:/var/www/hermes-insights/relatorio_{slug}.html"
                    r = subprocess.run(
                        ["sshpass", "-p", VPS_PASS, "scp", "-o", "StrictHostKeyChecking=no",
                         local, remote],
                        capture_output=True, text=True, timeout=20
                    )
                    if r.returncode == 0:
                        print(f"   ✓ relatorio_{slug}.html uploaded")
                    else:
                        print(f"   ✗ relatorio_{slug}.html: {r.stderr[:100]}")
                    break
        print()
        print(f"[OK] {len(changes)} relatórios atualizados")
    elif DRY_MODE and changes:
        print()
        print(f"[DRY] {len(changes)} relatórios precisam de atualização")
        print("   Rode sem dry=1 para aplicar.")
    else:
        print()
        print("[OK] Nenhuma mudança necessária")


if __name__ == "__main__":
    main()