"""
insights_module.py — Leitura de métricas Meta + Vista Social do Postgres VPS.

Schema: six_hype.* no command_center_postgres (127.0.0.1:55433).
Workflows n8n alimentam o banco diariamente (meta_insights_pull, vs_snapshots, vs_inbox_sync).

Env:
  INSIGHTS_DB_DSN  → ex: postgresql://command_center_app:senha@127.0.0.1:55433/command_center
                     (em prod: leia do .env de produção; em dev: leia do túnel SSH)
"""

from __future__ import annotations

import json
import os
import sys
from typing import Optional

DSN = os.environ.get("INSIGHTS_DB_DSN", "")

_pool = None

def _get_conn():
    """Conexão lazy. Usa psycopg2 se disponível; senão, log + None."""
    global _pool
    if not DSN:
        return None
    try:
        import psycopg2  # type: ignore
        import psycopg2.extras  # type: ignore
    except ModuleNotFoundError:
        print("⚠ psycopg2 não instalado · insights desabilitados. Rode: pip install psycopg2-binary", file=sys.stderr)
        return None

    if _pool is None:
        from psycopg2 import pool  # type: ignore
        _pool = pool.SimpleConnectionPool(1, 5, DSN)
    return _pool.getconn()


def _release(conn):
    if _pool and conn:
        _pool.putconn(conn)


def is_enabled() -> bool:
    return bool(DSN)


def top_posts(persona_id: str, limit: int = 10) -> list[dict]:
    """Top posts de uma persona nos últimos 30 dias, ordenado por score."""
    conn = _get_conn()
    if not conn:
        return []
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT id, format, posted_at, caption, permalink, thumbnail_url,
                       impressions, reach, engagement, saves, shares, score
                FROM six_hype.top_posts_30d
                WHERE persona_id = %s
                ORDER BY score DESC
                LIMIT %s
                """,
                (persona_id, limit),
            )
            rows = cur.fetchall()
            cols = [d[0] for d in cur.description]
            return [_row_to_dict(cols, r) for r in rows]
    finally:
        _release(conn)


def by_format(persona_id: str) -> list[dict]:
    """Performance comparativa por formato (reel vs carrossel vs static) últimos 30d."""
    conn = _get_conn()
    if not conn:
        return []
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT format, n_posts, avg_impressions, avg_reach,
                       avg_engagement, avg_density, last_snapshot
                FROM six_hype.performance_by_format
                WHERE persona_id = %s
                ORDER BY avg_engagement DESC NULLS LAST
                """,
                (persona_id,),
            )
            rows = cur.fetchall()
            cols = [d[0] for d in cur.description]
            return [_row_to_dict(cols, r) for r in rows]
    finally:
        _release(conn)


def topics_demanded(persona_id: str, limit: int = 10) -> list[dict]:
    """Palavras-chave/tópicos mais perguntados pelos pacientes nos últimos 30 dias."""
    conn = _get_conn()
    if not conn:
        return []
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT keyword, topic_category, mentions, n_sources, last_mention, channels
                FROM six_hype.topics_demanded_30d
                WHERE persona_id = %s
                ORDER BY mentions DESC
                LIMIT %s
                """,
                (persona_id, limit),
            )
            rows = cur.fetchall()
            cols = [d[0] for d in cur.description]
            return [_row_to_dict(cols, r) for r in rows]
    finally:
        _release(conn)


def heatmap(persona_id: str) -> dict:
    """Heatmap dia-da-semana × hora-do-dia de melhor performance (últimos 30d)."""
    conn = _get_conn()
    if not conn:
        return {}
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT EXTRACT(DOW  FROM mp.posted_at)::INT AS dow,
                       EXTRACT(HOUR FROM mp.posted_at)::INT AS hour,
                       COUNT(*)                              AS n,
                       AVG(mi.engagement)::INT               AS avg_engagement
                FROM six_hype.media_posts mp
                LEFT JOIN LATERAL (
                  SELECT engagement FROM six_hype.media_insights
                  WHERE post_id = mp.id ORDER BY snapshot_date DESC LIMIT 1
                ) mi ON TRUE
                WHERE mp.persona_id = %s
                  AND mp.posted_at >= NOW() - INTERVAL '30 days'
                GROUP BY dow, hour
                """,
                (persona_id,),
            )
            cells = [
                {"dow": r[0], "hour": r[1], "n": r[2], "avg_engagement": r[3] or 0}
                for r in cur.fetchall()
            ]
        return {"cells": cells}
    finally:
        _release(conn)


def _row_to_dict(cols: list[str], row: tuple) -> dict:
    out = {}
    for k, v in zip(cols, row):
        if hasattr(v, "isoformat"):
            out[k] = v.isoformat()
        elif isinstance(v, list):
            out[k] = list(v)
        else:
            out[k] = v
    return out