from typing import Any, Dict, Optional, Tuple
import json

from sqlalchemy import text

from app.config import settings
from app.db import engine
from app.schemas import (
    ActionDecision,
    ActionResult,
    AnalyzeRequest,
    CurrentState,
    UsageInfo,
)


def _json(value) -> str:
    if hasattr(value, "model_dump"):
        value = value.model_dump()
    return json.dumps(value or {}, ensure_ascii=False)


def _uuid_or_none(value):
    value = str(value or "").strip()
    return value or None


def save_action_run(
    *,
    request: AnalyzeRequest,
    action_decision: ActionDecision,
    action_result: ActionResult,
    usage: UsageInfo,
    needs_review: bool,
    model: str,
    decision_source: str,
    raw_body: Optional[str] = None,
    clean_body: Optional[str] = None,
    raw_event_id: Optional[str] = None,
) -> Tuple[str, str]:
    if not settings.clientflow_persist:
        return "", ""

    conversation_id = request.conversation_id or "manual"

    with engine.begin() as conn:
        message_row = conn.execute(text("""
            INSERT INTO messages (
                raw_event_id,
                source_system,
                source_event_id,
                conversation_id,
                contact_id,
                direction,
                raw_body,
                clean_body,
                previous_context,
                metadata
            )
            VALUES (
                CAST(:raw_event_id AS UUID),
                :source_system,
                NULL,
                :conversation_id,
                :contact_id,
                'inbound',
                :raw_body,
                :clean_body,
                :previous_context,
                CAST(:metadata AS JSONB)
            )
            RETURNING id::text
        """), {
            "raw_event_id": raw_event_id,
            "source_system": request.source or "manual",
            "conversation_id": conversation_id,
            "contact_id": request.contact_id,
            "raw_body": raw_body or request.last_customer_message,
            "clean_body": clean_body or request.last_customer_message,
            "previous_context": request.previous_context,
            "metadata": _json({
                "current_state": request.current_state.model_dump(),
            }),
        }).fetchone()

        message_id = message_row[0]

        run_row = conn.execute(text("""
            INSERT INTO action_runs (
                message_id,
                raw_event_id,
                conversation_id,
                contact_id,
                source_system,

                model,
                provider,
                openrouter_generation_id,
                decision_source,

                action_decision,
                action_result,

                prompt_tokens,
                completion_tokens,
                total_tokens,
                cost,
                usage,

                needs_review
            )
            VALUES (
                CAST(:message_id AS UUID),
                CAST(:raw_event_id AS UUID),
                :conversation_id,
                :contact_id,
                :source_system,

                :model,
                :provider,
                :openrouter_generation_id,
                :decision_source,

                CAST(:action_decision AS JSONB),
                CAST(:action_result AS JSONB),

                :prompt_tokens,
                :completion_tokens,
                :total_tokens,
                :cost,
                CAST(:usage AS JSONB),

                :needs_review
            )
            RETURNING id::text
        """), {
            "message_id": message_id,
            "raw_event_id": raw_event_id,
            "conversation_id": conversation_id,
            "contact_id": request.contact_id,
            "source_system": request.source or "manual",

            "model": model,
            "provider": usage.provider,
            "openrouter_generation_id": usage.id,
            "decision_source": decision_source,

            "action_decision": _json(action_decision),
            "action_result": _json(action_result),

            "prompt_tokens": usage.prompt_tokens,
            "completion_tokens": usage.completion_tokens,
            "total_tokens": usage.total_tokens,
            "cost": usage.cost,
            "usage": _json(usage),

            "needs_review": needs_review,
        }).fetchone()

        action_run_id = run_row[0]

        if raw_event_id:
            conn.execute(text("""
                UPDATE raw_events
                SET
                    message_id = CAST(:message_id AS UUID),
                    action_run_id = CAST(:action_run_id AS UUID)
                WHERE id = CAST(:raw_event_id AS UUID)
            """), {
                "message_id": message_id,
                "action_run_id": action_run_id,
                "raw_event_id": raw_event_id,
            })

    return action_run_id, message_id


def save_raw_event(
    source_system: str,
    event_type: str | None,
    source_event_id: str | None,
    conversation_id: str | None,
    contact_id: str | None,
    payload: dict,
) -> Dict[str, Any]:
    with engine.begin() as conn:
        row = conn.execute(text("""
            INSERT INTO raw_events (
                source_system,
                event_type,
                source_event_id,
                conversation_id,
                contact_id,
                payload
            )
            VALUES (
                :source_system,
                :event_type,
                :source_event_id,
                :conversation_id,
                :contact_id,
                CAST(:payload AS JSONB)
            )
            ON CONFLICT (source_system, source_event_id)
            WHERE source_event_id IS NOT NULL
            DO UPDATE SET
                payload = EXCLUDED.payload,
                event_type = EXCLUDED.event_type,
                conversation_id = COALESCE(EXCLUDED.conversation_id, raw_events.conversation_id),
                contact_id = COALESCE(EXCLUDED.contact_id, raw_events.contact_id)
            RETURNING
                id::text,
                processed,
                ignored,
                message_id::text,
                action_run_id::text,
                (xmax = 0) AS inserted
        """), {
            "source_system": source_system,
            "event_type": event_type,
            "source_event_id": source_event_id,
            "conversation_id": conversation_id,
            "contact_id": contact_id,
            "payload": _json(payload),
        }).mappings().first()

    return dict(row or {})


def mark_raw_event_processed(
    raw_event_id: str,
    action_run_id: str | None = None,
    message_id: str | None = None,
    ignored: bool = False,
    error: str | None = None,
) -> None:
    with engine.begin() as conn:
        conn.execute(text("""
            UPDATE raw_events
            SET
                processed = TRUE,
                ignored = :ignored,
                processing_error = :error,
                action_run_id = COALESCE(CAST(:action_run_id AS UUID), action_run_id),
                message_id = COALESCE(CAST(:message_id AS UUID), message_id),
                processed_at = now()
            WHERE id = CAST(:raw_event_id AS UUID)
        """), {
            "raw_event_id": raw_event_id,
            "ignored": ignored,
            "error": error,
            "action_run_id": _uuid_or_none(action_run_id),
            "message_id": _uuid_or_none(message_id),
        })


def mark_raw_event_error(raw_event_id: str, error: str) -> None:
    """Regista erro de processamento sem marcar o evento como processado.

    Isto evita filas silenciosas: o evento deixa de ficar em
    processed=false/ignored=false/processing_error=null, mas continua elegível
    para recovery explícito com scripts administrativos.
    """
    with engine.begin() as conn:
        conn.execute(text("""
            UPDATE raw_events
            SET
                processed = FALSE,
                ignored = FALSE,
                processing_error = :error,
                processed_at = now()
            WHERE id = CAST(:raw_event_id AS UUID)
        """), {
            "raw_event_id": raw_event_id,
            "error": str(error or "processing_exception")[:1000],
        })


def get_state_for_conversation(conversation_id: str | None) -> CurrentState:
    if not conversation_id:
        return CurrentState()

    with engine.begin() as conn:
        row = conn.execute(text("""
            SELECT
                t.action_code,
                t.route,
                t.status,
                t.action,
                t.note,
                t.created_at
            FROM tasks t
            WHERE t.conversation_id = :conversation_id
            ORDER BY t.created_at DESC
            LIMIT 1
        """), {
            "conversation_id": conversation_id,
        }).mappings().first()

    if not row:
        return CurrentState()

    return CurrentState(
        last_action_code=row.get("action_code") or "desconhecido",
        last_route=row.get("route") or "desconhecido",
        last_task_status=row.get("status") or "desconhecido",
        metadata={
            "last_action": row.get("action"),
            "last_note": row.get("note"),
            "last_created_at": str(row.get("created_at")),
        },
    )


def get_recent_chatwoot_public_context(
    conversation_id: str | None,
    *,
    current_source_event_id: str | None = None,
    max_messages: int = 2,
) -> list[str]:
    """Devolve as últimas mensagens públicas Chatwoot para contexto LLM.

    Usado quando o webhook não traz `conversation.messages`. Lê raw_events já
    guardados e exclui a mensagem atual para evitar que o LLM confunda histórico
    com o email recebido agora.
    """
    if not conversation_id:
        return []

    with engine.begin() as conn:
        rows = conn.execute(text("""
            SELECT
                source_event_id,
                payload #>> '{message_type}' AS message_type,
                payload #>> '{content}' AS content,
                created_at
            FROM raw_events
            WHERE source_system = 'chatwoot'
              AND event_type = 'message_created'
              AND conversation_id = :conversation_id
              AND COALESCE(payload #>> '{content}', '') <> ''
              AND (CAST(:current_source_event_id AS TEXT) IS NULL OR source_event_id <> CAST(:current_source_event_id AS TEXT))
              AND COALESCE(payload #>> '{private}', 'false') <> 'true'
            ORDER BY created_at DESC
            LIMIT :limit
        """), {
            "conversation_id": str(conversation_id),
            "current_source_event_id": str(current_source_event_id or "") or None,
            "limit": max(1, int(max_messages or 2)),
        }).mappings().all()

    lines: list[str] = []
    for row in reversed(rows):
        role = "BLIF" if str(row.get("message_type") or "").lower() == "outgoing" else "Cliente"
        content = str(row.get("content") or "")
        content = " ".join(content.split())
        if len(content) > 360:
            content = content[:359].rstrip() + "…"
        if content:
            lines.append(f"- {role}: {content}")
    return lines