"""
Lead Flow History — Postgres Persistence Layer
Bảng: canifa_chat.lead_flow_history
Conn: CHECKPOINT_POSTGRES_URL (pgvector@172.16.2.190:15433)

Dùng psycopg v3 (đã có trong requirements) — native async, không cần asyncpg.
"""

import json
import logging
from typing import Any

import psycopg
from psycopg.rows import dict_row
from psycopg_pool import AsyncConnectionPool

from config import CHECKPOINT_POSTGRES_URL

logger = logging.getLogger(__name__)

_pool: AsyncConnectionPool | None = None


async def get_pg_pool() -> AsyncConnectionPool | None:
    """Lazy-init psycopg3 async connection pool."""
    global _pool
    if _pool is None:
        try:
            _pool = AsyncConnectionPool(
                conninfo=CHECKPOINT_POSTGRES_URL,
                min_size=1,
                max_size=5,
                open=False,
            )
            await _pool.open()
            logger.info("✅ [LeadFlowPG] psycopg3 pool ready")
        except Exception as e:
            logger.error(f"❌ [LeadFlowPG] Pool init failed: {e}")
            return None
    return _pool


# ─────────────────────────────────────────
# WRITE
# ─────────────────────────────────────────

async def save_lead_turn(
    device_id: str,
    conv_id: str,
    human_message: str,
    ai_response_text: str,
    products: list | None = None,
    lead_stage: dict | None = None,
) -> None:
    """Lưu một turn (human + AI) vào Postgres."""
    from config import USE_LOCAL_SQLITE
    
    if USE_LOCAL_SQLITE:
        from common.sqlite_mock import MockCursor
        mc = MockCursor()
        mc.execute(
            """
            INSERT INTO canifa_chat.lead_flow_history
                (device_id, conv_id, is_human, message)
            VALUES (%s, %s, TRUE, %s)
            """,
            (device_id, conv_id, human_message),
        )
        ai_payload = json.dumps(
            {
                "ai_response": ai_response_text,
                "products": products or [],
                "lead_stage": lead_stage or {},
            },
            ensure_ascii=False,
        )
        mc.execute(
            """
            INSERT INTO canifa_chat.lead_flow_history
                (device_id, conv_id, is_human, message, ai_response, products, lead_stage)
            VALUES (%s, %s, FALSE, %s, %s, %s::jsonb, %s::jsonb)
            """,
            (
                device_id,
                conv_id,
                ai_payload,
                ai_response_text,
                json.dumps(products or [], ensure_ascii=False),
                json.dumps(lead_stage or {}, ensure_ascii=False),
            ),
        )
        logger.debug(f"✅ [LeadFlowPG MOCK] Saved turn dev={device_id[:8]}… conv={conv_id[:8]}…")
        return

    pool = await get_pg_pool()
    if not pool:
        return

    try:
        async with pool.connection() as conn:
            # Human message
            await conn.execute(
                """
                INSERT INTO canifa_chat.lead_flow_history
                    (device_id, conv_id, is_human, message)
                VALUES (%s, %s, TRUE, %s)
                """,
                (device_id, conv_id, human_message),
            )
            # AI message — lưu full JSON blob để frontend parse products + lead_stage
            ai_payload = json.dumps(
                {
                    "ai_response": ai_response_text,
                    "products": products or [],
                    "lead_stage": lead_stage or {},
                },
                ensure_ascii=False,
            )
            await conn.execute(
                """
                INSERT INTO canifa_chat.lead_flow_history
                    (device_id, conv_id, is_human, message, ai_response, products, lead_stage)
                VALUES (%s, %s, FALSE, %s, %s, %s::jsonb, %s::jsonb)
                """,
                (
                    device_id,
                    conv_id,
                    ai_payload,
                    ai_response_text,
                    json.dumps(products or [], ensure_ascii=False),
                    json.dumps(lead_stage or {}, ensure_ascii=False),
                ),
            )
        logger.debug(f"✅ [LeadFlowPG] Saved turn dev={device_id[:8]}… conv={conv_id[:8]}…")
    except Exception as e:
        logger.error(f"❌ [LeadFlowPG] save_lead_turn failed: {e}")


# ─────────────────────────────────────────
# READ
# ─────────────────────────────────────────

async def get_lead_history(
    device_id: str,
    conv_id: str,
    limit: int = 50,
) -> list[dict[str, Any]]:
    """
    Lấy history theo (device_id, conv_id).
    Trả về list dict mới nhất trước (DESC).
    """
    from config import USE_LOCAL_SQLITE
    
    if USE_LOCAL_SQLITE:
        from common.sqlite_mock import MockCursor
        mc = MockCursor()
        mc.execute(
            """
            SELECT id, is_human, message, created_at
            FROM canifa_chat.lead_flow_history
            WHERE device_id = %s AND conv_id = %s
            ORDER BY id DESC
            LIMIT %s
            """,
            (device_id, conv_id, limit),
        )
        rows = mc.fetchall()
        return [
            {
                "id": r["id"],
                "is_human": r["is_human"],
                "message": r["message"],
                "timestamp": r["created_at"] if isinstance(r["created_at"], str) else r["created_at"].isoformat(),
                "conversation_id": conv_id,
            }
            for r in rows
        ]

    pool = await get_pg_pool()
    if not pool:
        return []

    try:
        async with pool.connection() as conn:
            async with conn.cursor(row_factory=dict_row) as cur:
                await cur.execute(
                    """
                    SELECT id, is_human, message, created_at
                    FROM canifa_chat.lead_flow_history
                    WHERE device_id = %s AND conv_id = %s
                    ORDER BY id DESC
                    LIMIT %s
                    """,
                    (device_id, conv_id, limit),
                )
                rows = await cur.fetchall()

        return [
            {
                "id": r["id"],
                "is_human": r["is_human"],
                "message": r["message"],
                "timestamp": r["created_at"].isoformat(),
                "conversation_id": conv_id,
            }
            for r in rows
        ]
    except Exception as e:
        logger.error(f"❌ [LeadFlowPG] get_lead_history failed: {e}")
        return []


async def list_lead_conversations(device_id: str, limit: int = 20) -> list[dict]:
    """
    Lấy danh sách conversation của 1 device để populate dropdown.
    """
    from config import USE_LOCAL_SQLITE
    
    if USE_LOCAL_SQLITE:
        from common.sqlite_mock import MockCursor
        mc = MockCursor()
        mc.execute(
            """
            SELECT conv_id,
                   MIN(created_at) AS started_at,
                   MAX(created_at) AS last_at,
                   COUNT(*) AS turn_count
            FROM canifa_chat.lead_flow_history
            WHERE device_id = %s
            GROUP BY conv_id
            ORDER BY last_at DESC
            LIMIT %s
            """,
            (device_id, limit),
        )
        # Note: COUNT(*) FILTER (WHERE is_human) was translated to standard COUNT(*) for sqlite safety in this query temporarily.
        # It's better to just use COUNT(*) because SQLite's FILTER support might be tricky with our translate regex.
        rows = mc.fetchall()
        return [
            {
                "conv_id": r["conv_id"],
                "started_at": r["started_at"] if isinstance(r["started_at"], str) else r["started_at"].isoformat(),
                "last_at": r["last_at"] if isinstance(r["last_at"], str) else r["last_at"].isoformat(),
                "turn_count": r["turn_count"],
            }
            for r in rows
        ]

    pool = await get_pg_pool()
    if not pool:
        return []

    try:
        async with pool.connection() as conn:
            async with conn.cursor(row_factory=dict_row) as cur:
                await cur.execute(
                    """
                    SELECT conv_id,
                           MIN(created_at) AS started_at,
                           MAX(created_at) AS last_at,
                           COUNT(*) FILTER (WHERE is_human) AS turn_count
                    FROM canifa_chat.lead_flow_history
                    WHERE device_id = %s
                    GROUP BY conv_id
                    ORDER BY last_at DESC
                    LIMIT %s
                    """,
                    (device_id, limit),
                )
                rows = await cur.fetchall()

        return [
            {
                "conv_id": r["conv_id"],
                "started_at": r["started_at"].isoformat(),
                "last_at": r["last_at"].isoformat(),
                "turn_count": r["turn_count"],
            }
            for r in rows
        ]
    except Exception as e:
        logger.error(f"❌ [LeadFlowPG] list_lead_conversations failed: {e}")
        return []


async def get_all_history_for_dashboard(limit: int = 200) -> list[dict]:
    """
    Admin: lấy toàn bộ history gần nhất (mọi device, mọi conv) cho dashboard.
    """
    from config import USE_LOCAL_SQLITE
    
    if USE_LOCAL_SQLITE:
        from common.sqlite_mock import MockCursor
        mc = MockCursor()
        mc.execute(
            """
            SELECT id, device_id, conv_id, is_human, message,
                   ai_response, products, lead_stage, created_at
            FROM canifa_chat.lead_flow_history
            ORDER BY id DESC
            LIMIT %s
            """,
            (limit,),
        )
        rows = mc.fetchall()
        return [
            {
                "id": r["id"],
                "device_id": str(r["device_id"])[:12] + "…",
                "conv_id": r["conv_id"],
                "is_human": r["is_human"],
                "message": r["message"],
                "ai_response": r["ai_response"],
                "products": r["products"],
                "lead_stage": r["lead_stage"],
                "created_at": r["created_at"] if isinstance(r["created_at"], str) else r["created_at"].isoformat(),
            }
            for r in rows
        ]

    pool = await get_pg_pool()
    if not pool:
        return []

    try:
        async with pool.connection() as conn:
            async with conn.cursor(row_factory=dict_row) as cur:
                await cur.execute(
                    """
                    SELECT id, device_id, conv_id, is_human, message,
                           ai_response, products, lead_stage, created_at
                    FROM canifa_chat.lead_flow_history
                    ORDER BY id DESC
                    LIMIT %s
                    """,
                    (limit,),
                )
                rows = await cur.fetchall()

        return [
            {
                "id": r["id"],
                "device_id": str(r["device_id"])[:12] + "…",
                "conv_id": r["conv_id"],
                "is_human": r["is_human"],
                "message": r["message"],
                "ai_response": r["ai_response"],
                "products": r["products"],
                "lead_stage": r["lead_stage"],
                "created_at": r["created_at"].isoformat(),
            }
            for r in rows
        ]
    except Exception as e:
        logger.error(f"❌ [LeadFlowPG] get_all_history_for_dashboard failed: {e}")
        return []
