import uuid
import json
import logging
from sqlalchemy import text
from typing import Literal
from app.models.connection import get_async_db
from app.exceptions.http import HTTPException


# CREATE TABLE conversations (
#     id CHAR(36) PRIMARY KEY,
#     first_message TEXT,
#     hotel_id VARCHAR(255),
#     sub_hotel_id VARCHAR(255),
#     is_deleted TINYINT(1) NOT NULL DEFAULT 0,
#     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
#     updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
#     deleted_at TIMESTAMP NULL
# ) ENGINE=InnoDB;

# CREATE TABLE messages (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     conversation_id CHAR(36) NOT NULL,
#     message TEXT,
#     role VARCHAR(10) NOT NULL,
#     customer_id TEXT,
#     feedback TEXT,
#     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
#     updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

#     CONSTRAINT fk_conversation
#         FOREIGN KEY (conversation_id)
#         REFERENCES conversations(id)
#         ON DELETE CASCADE
# ) ENGINE=InnoDB;


log = logging.getLogger(__name__)

async def create_conversation(question: str, hotel_id: str, sub_hotel_id: str, metadata: dict = {}):
    try:
        async with get_async_db() as db:
            conversation_id = str(uuid.uuid4())

            await db.execute(
                text("INSERT INTO conversations (id, first_message, hotel_id, sub_hotel_id, metadata) VALUES (:id, :msg, :hotel_id, :sub_hotel_id, :metadata)"),
                {"id": conversation_id, "msg": question, "hotel_id": hotel_id, "sub_hotel_id": sub_hotel_id, "metadata": json.dumps(metadata) if metadata else None}
            )

            await db.commit()

        return conversation_id

    except Exception as e:
        log.error(f"Error creating conversation: {e}")
        return None

async def save_conversation(conversation_id: str, hotel_id: str, sub_hotel_id: str, question: str | None = None, answer: str | None = None, customer_id: str | None = None):
    try:
        if not conversation_id:
            conversation_id = await create_conversation(question, hotel_id, sub_hotel_id)

        messages = []

        if question:
            messages.append({
                "conversation_id": conversation_id,
                "message": question,
                "role": "human",
                "customer_id": customer_id
            })

        if answer:
            messages.append({
                "conversation_id": conversation_id,
                "message": answer,
                "role": "ai",
                "customer_id": customer_id
            })

        if len(messages) == 0:
            return conversation_id

        async with get_async_db() as db:
            query = text("""
                INSERT INTO messages (conversation_id, message, role, customer_id)
                VALUES (:conversation_id, :message, :role, :customer_id)
            """)

            query1 = text("""
                UPDATE conversations
                SET updated_at = NOW()
                WHERE id = :conversation_id
            """)
            await db.execute(query, messages)
            await db.execute(query1, {"conversation_id": conversation_id})
            await db.commit()

        return conversation_id
    except Exception as e:
        log.error(f"Error saving conversation: {e}")
        return None
async def save_conversation_feedback(
    conversation_id: str,
    feedback: Literal["up", "down"],
) -> bool:
    """
    Update feedback directly in `messages` table.

    - feedback: "up" -> 1, "down" -> 0
    - Targets latest AI message in the conversation
    - Raises HTTPException if conversation or message not found
    """
    async with get_async_db() as db:
        # 1. Check conversation exists and not deleted
        conv_result = await db.execute(
            text("""
                SELECT id
                FROM conversations
                WHERE id = :conversation_id
                AND is_deleted = 0
                LIMIT 1;
            """),
            {"conversation_id": conversation_id},
        )
        if not conv_result.fetchone():
            raise HTTPException(status_code=404, detail="Conversation not found")

        # 2. Get latest AI message
        msg_result = await db.execute(
            text("""
                SELECT id
                FROM messages
                WHERE conversation_id = :conversation_id
                AND role = 'ai'
                ORDER BY created_at DESC
                LIMIT 1;
            """),
            {"conversation_id": conversation_id},
        )
        msg_row = msg_result.fetchone()

        if not msg_row:
            raise HTTPException(status_code=404, detail="No AI message found")

        message_id = msg_row[0]

        await db.execute(
            text("""
                UPDATE messages
                SET feedback = :feedback
                WHERE id = :message_id;
            """),
            {
                "feedback": feedback,
                "message_id": message_id,
            },
        )

        await db.commit()
        return True