#!/usr/bin/env python3
"""
Snapshot diario do funil WhatsApp PX3.

Rodado via cron 1x por dia (07h BR). Grava o estado do funil dos ultimos 30d
numa tabela SQLite pra montar a serie historica.

Pode ser rodado manualmente a qualquer momento:
    python3 /opt/mia/workspace/clientes/px3lab/dashboard/snapshot_funil.py
"""
from __future__ import annotations

import json
import sqlite3
import sys
import time
from datetime import datetime, timedelta
from pathlib import Path
from zoneinfo import ZoneInfo

import requests

SCRIPT_DIR = Path(__file__).resolve().parent
PX3_DIR = SCRIPT_DIR.parent
sys.path.insert(0, str(SCRIPT_DIR))
sys.path.insert(0, str(PX3_DIR))

from config_ghl import PX3_TOKEN, PX3_LOCATION_ID  # noqa: E402
import funil_whatsapp  # noqa: E402

TZ_BR = ZoneInfo("America/Sao_Paulo")

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

DB_PATH = SCRIPT_DIR / "db" / "funil_history.db"

JANELA_DIAS = 30  # cada snapshot = funil dos ultimos 30 dias


def _ensure_schema(con: sqlite3.Connection) -> None:
    con.executescript("""
    CREATE TABLE IF NOT EXISTS funil_snapshot (
        snapshot_date   TEXT PRIMARY KEY,
        periodo_inicio  TEXT NOT NULL,
        periodo_fim     TEXT NOT NULL,
        chegou          INTEGER NOT NULL,
        conversou       INTEGER NOT NULL,
        reuniao         INTEGER NOT NULL,
        venda           INTEGER NOT NULL,
        perdido         INTEGER NOT NULL,
        taxas_json      TEXT NOT NULL,
        criado_em       TEXT NOT NULL
    );
    """)
    con.commit()


def _listar_pipelines_meta() -> dict:
    try:
        r = requests.get(
            f"{GHL_BASE}/opportunities/pipelines",
            headers=HEADERS,
            params={"locationId": PX3_LOCATION_ID},
            timeout=20,
        )
        try:
            data = r.json()
        except Exception:
            data = {}
        if r.status_code != 200:
            print(f"[snapshot] erro pipelines: {r.status_code}")
            return {}
    except Exception as e:
        print(f"[snapshot] erro request pipelines: {e}")
        return {}

    out = {}
    for p in data.get("pipelines", []) or []:
        pid = p.get("id")
        if not pid:
            continue
        stages_map = {s.get("id"): s.get("name", "") for s in (p.get("stages") or [])}
        out[pid] = {
            "name": p.get("name", ""),
            "stages_map": stages_map,
        }
    return out


def rodar_snapshot(snapshot_date=None) -> dict:
    """
    snapshot_date: date BR. Default = hoje. Grava snapshot do dia.
    """
    agora_br = datetime.now(TZ_BR)
    if snapshot_date is None:
        snap_date_str = agora_br.date().isoformat()
    else:
        snap_date_str = snapshot_date.isoformat() if hasattr(snapshot_date, "isoformat") else str(snapshot_date)

    fim_br = agora_br.replace(hour=23, minute=59, second=59, microsecond=0)
    inicio_br = (agora_br - timedelta(days=JANELA_DIAS)).replace(
        hour=0, minute=0, second=0, microsecond=0
    )

    inicio_ms = int(inicio_br.timestamp() * 1000)
    fim_ms = int(fim_br.timestamp() * 1000)

    print(f"[snapshot] {snap_date_str} - janela {inicio_br.date()} ate {fim_br.date()}")

    pipelines_meta = _listar_pipelines_meta()
    print(f"[snapshot] {len(pipelines_meta)} pipelines carregadas")

    conversas = funil_whatsapp.buscar_conversas_whatsapp(
        CONV_HEADERS, PX3_LOCATION_ID, inicio_ms, fim_ms,
    )
    print(f"[snapshot] {len(conversas)} conversas puxadas")

    data = funil_whatsapp.montar_funil(
        conversas, pipelines_meta, inicio_ms, fim_ms,
        enriquecer_msgs=False, headers_conv=CONV_HEADERS,
        location_id=PX3_LOCATION_ID,
    )

    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(str(DB_PATH))
    _ensure_schema(con)
    con.execute(
        """
        INSERT OR REPLACE INTO funil_snapshot
        (snapshot_date, periodo_inicio, periodo_fim,
         chegou, conversou, reuniao, venda, perdido,
         taxas_json, criado_em)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            snap_date_str,
            inicio_br.date().isoformat(),
            fim_br.date().isoformat(),
            data.get("chegou", 0),
            data.get("conversou", 0),
            data.get("reuniao", 0),
            data.get("venda", 0),
            data.get("perdido", 0),
            json.dumps(data.get("taxas", {})),
            agora_br.isoformat(),
        ),
    )
    con.commit()
    con.close()

    print(
        f"[snapshot] OK - chegou={data.get('chegou')} "
        f"conversou={data.get('conversou')} "
        f"reuniao={data.get('reuniao')} "
        f"venda={data.get('venda')} "
        f"perdido={data.get('perdido')}"
    )
    return data


if __name__ == "__main__":
    try:
        rodar_snapshot()
    except Exception as e:
        print(f"[snapshot] ERRO: {e}")
        raise