"""CRUD for conversations and messages."""

from __future__ import annotations

from typing import Any

from database.connection_manager import ConnectionManager


class ConversationManager:
    def __init__(self, cm: ConnectionManager) -> None:
        self._cm = cm

    async def create_conversation(self, title: str | None = None) -> dict:
        return await self._cm.fetch_one(
            "INSERT INTO v3q_results.conversations (title) VALUES (:title) RETURNING *",
            {"title": title or "New Conversation"},
        )

    async def list_conversations(self, limit: int = 50) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_results.conversations ORDER BY updated_at DESC LIMIT :limit",
            {"limit": limit},
        )

    async def get_conversation(self, conversation_id: str) -> dict | None:
        return await self._cm.fetch_one(
            "SELECT * FROM v3q_results.conversations WHERE id = :id",
            {"id": conversation_id},
        )

    async def update_conversation(self, conversation_id: str, title: str) -> dict | None:
        return await self._cm.fetch_one(
            "UPDATE v3q_results.conversations SET title = :title, updated_at = now() WHERE id = :id RETURNING *",
            {"id": conversation_id, "title": title},
        )

    async def delete_conversation(self, conversation_id: str) -> None:
        await self._cm.execute_write(
            "DELETE FROM v3q_results.conversations WHERE id = :id",
            {"id": conversation_id},
        )

    async def add_message(
        self,
        conversation_id: str,
        role: str,
        content: str,
        sql_query: str | None = None,
        has_results: bool = False,
        result_table: str | None = None,
        result_csv_path: str | None = None,
        row_count: int | None = None,
        chart_config: dict | None = None,
        execution_time_ms: int | None = None,
        model_used: str | None = None,
        tokens_used: int | None = None,
    ) -> dict:
        import json
        return await self._cm.fetch_one("""
            INSERT INTO v3q_results.messages
                (conversation_id, role, content, sql_query, has_results,
                 result_table, result_csv_path, row_count, chart_config,
                 execution_time_ms, model_used, tokens_used)
            VALUES (:conversation_id, :role, :content, :sql_query, :has_results,
                    :result_table, :result_csv_path, :row_count, :chart_config,
                    :execution_time_ms, :model_used, :tokens_used)
            RETURNING *
        """, {
            "conversation_id": conversation_id,
            "role": role,
            "content": content,
            "sql_query": sql_query,
            "has_results": has_results,
            "result_table": result_table,
            "result_csv_path": result_csv_path,
            "row_count": row_count,
            "chart_config": json.dumps(chart_config) if chart_config else None,
            "execution_time_ms": execution_time_ms,
            "model_used": model_used,
            "tokens_used": tokens_used,
        })

    async def get_messages(self, conversation_id: str) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_results.messages WHERE conversation_id = :cid ORDER BY created_at",
            {"cid": conversation_id},
        )

    async def get_message(self, message_id: str) -> dict | None:
        return await self._cm.fetch_one(
            "SELECT * FROM v3q_results.messages WHERE id = :id",
            {"id": message_id},
        )

    async def get_conversation_history_for_claude(
        self, conversation_id: str, limit: int = 10
    ) -> list[dict[str, str]]:
        """Get recent messages formatted for Claude API (no data values)."""
        messages = await self._cm.execute("""
            SELECT role, content FROM v3q_results.messages
            WHERE conversation_id = :cid
            ORDER BY created_at DESC
            LIMIT :limit
        """, {"cid": conversation_id, "limit": limit})

        # Reverse to chronological order and filter to user/assistant roles
        return [
            {"role": m["role"], "content": m["content"]}
            for m in reversed(messages)
            if m["role"] in ("user", "assistant")
        ]
