# services/conversation_service.py

import logging
import json
from typing import List, Dict, Any, Optional
from datetime import datetime

from sqlalchemy import create_engine, text
from config.settings import Config

logger = logging.getLogger(__name__)


class ConversationService:
    """
    Handles all database operations related to conversations and messages.
    """

    def __init__(self, database_uri: str):
        try:
            self.engine = create_engine(database_uri)
            with self.engine.connect() as connection:
                logger.info("ConversationService initialized and database connection successful.")
        except Exception as e:
            logger.error(f"Failed to connect to the database in ConversationService: {e}")
            self.engine = None

    def create_conversation(self, title: str, company_id: int) -> int:
        """
        Creates a new conversation thread for a company.
        Returns the ID of the new conversation.
        """
        if not self.engine:
            raise Exception("Database engine not available.")

        query = text(
            """
            INSERT INTO conversation (title, company_id, created_at, updated_at)
            VALUES (:title, :company_id, :now, :now) RETURNING id;
            """
        )
        with self.engine.connect() as connection:
            result = connection.execute(
                query,
                {"title": title, "company_id": company_id, "now": datetime.utcnow()}
            )
            connection.commit()
            conversation_id = result.scalar_one()
            logger.info(f"Created new conversation with ID: {conversation_id} for company {company_id}")
            return conversation_id

    def add_message_to_conversation(self, conversation_id: int, question: str, answer: str, metadata: Dict[str, Any]):
        """
        Adds a new question/answer pair to an existing conversation.
        Also updates the 'updated_at' timestamp of the parent conversation.
        """
        if not self.engine:
            raise Exception("Database engine not available.")

        # --- THIS IS THE SOLID FIX ---
        # The SQL query now uses a consistent parameter style (':key') that is guaranteed
        # to work with the database driver and the JSONB data type.
        message_query = text(
            """
            INSERT INTO conversation_message (conversation_id, question, answer, metadata, created_at)
            VALUES (:conversation_id, :question, :answer, :metadata, :now);
            """
        )
        # --- End of FIX ---

        update_query = text(
            """
            UPDATE conversation
            SET updated_at = :now
            WHERE id = :conversation_id;
            """
        )
        with self.engine.connect() as connection:
            # The metadata dictionary is converted to a JSON string before being sent.
            connection.execute(
                message_query,
                {
                    "conversation_id": conversation_id,
                    "question": question,
                    "answer": answer,
                    "metadata": json.dumps(metadata),
                    "now": datetime.utcnow()
                }
            )
            connection.execute(
                update_query,
                {"conversation_id": conversation_id, "now": datetime.utcnow()}
            )
            connection.commit()
            logger.info(f"Added new message to conversation ID: {conversation_id}")

    def get_conversations_for_company(self, company_id: int, page: int = 1, page_size: int = 20) -> List[
        Dict[str, Any]]:
        """
        Retrieves a paginated list of all conversations for a specific company.
        """
        if not self.engine:
            raise Exception("Database engine not available.")

        offset = (page - 1) * page_size
        query = text(
            """
            SELECT id, title, company_id, created_at, updated_at
            FROM conversation
            WHERE company_id = :company_id
            ORDER BY updated_at DESC LIMIT :page_size
            OFFSET :offset;
            """
        )
        with self.engine.connect() as connection:
            result = connection.execute(
                query,
                {"company_id": company_id, "page_size": page_size, "offset": offset}
            )
            conversations = [row._asdict() for row in result.fetchall()]
            return conversations

    def get_conversation_history(self, conversation_id: int, limit: int = 20) -> List[Dict[str, Any]]:
        """
        Retrieves the most recent messages for a given conversation.
        """
        if not self.engine:
            raise Exception("Database engine not available.")

        query = text(
            """
            SELECT id, question, answer, metadata, created_at
            FROM conversation_message
            WHERE conversation_id = :conversation_id
            ORDER BY created_at DESC LIMIT :limit;
            """
        )
        with self.engine.connect() as connection:
            result = connection.execute(
                query,
                {"conversation_id": conversation_id, "limit": limit}
            )
            history = [row._asdict() for row in result.fetchall()]
            return list(reversed(history))