from db import database
from fastapi import HTTPException
from databases import Database


async def delete_chat_history(lineuser_id: int):
    try:
        query = "DELETE FROM chat WHERE lineuser_id = :lineuser_id"
        await database.execute(query, values={"lineuser_id": lineuser_id})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_chat_history(lineuser_id: int):
    try:
        query = """
            SELECT message, response FROM chat WHERE lineuser_id = :lineuser_id
            ORDER BY created_at ASC
            """
        return await database.fetch_all(query, {"lineuser_id": lineuser_id})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_chat_history_exclude_result(lineuser_id: int):
    try:
        query = """
            SELECT message, response 
            FROM chat 
            WHERE lineuser_id = :lineuser_id AND status != 99
            ORDER BY created_at ASC
        """
        return await database.fetch_all(query, {"lineuser_id": lineuser_id})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_chat_history_status(lineuser_id: int, status: int):
    try:
        query = """
            SELECT message, response FROM chat WHERE lineuser_id = :lineuser_id and status = :status
            ORDER BY created_at ASC
            """
        results = await database.fetch_all(query, {"lineuser_id": lineuser_id, "status": status})
        if not results:
            return []
        return results
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_quiz_chat_history(lineuser_id: int):
    try:
        query = """
            SELECT message, response FROM chat
            WHERE lineuser_id = :lineuser_id AND status = 'QUIZ'
            ORDER BY created_at ASC
            """
        results = await database.fetch_all(query, {"lineuser_id": lineuser_id})
        if not results:
            return []
        return results
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_non_status_one_chat_history(lineuser_id: int):
    try:
        query = """
            SELECT message, response FROM chat
            WHERE lineuser_id = :lineuser_id AND status != 1
            ORDER BY created_at ASC
            """
        results = await database.fetch_all(query, {"lineuser_id": lineuser_id})
        if not results:
            return []
        return results
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def save_chat_message(lineuser_id: int, status: int, message: str, response: str):
    try:
        query = """
            INSERT INTO chat (lineuser_id, status, message, response, created_at)
            VALUES (:lineuser_id, :status, :message, :response, NOW())
            """
        await database.execute(query, values={
            "lineuser_id": lineuser_id,
            "status": status,
            "message": message,
            "response": response
        })
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def get_user_info(user_id: str):
    try:
        user_query = """
        SELECT lineuser_key
        FROM lineusers
        WHERE lineuser_key = :userId
        """
        result = await database.fetch_one(user_query, {"userId": user_id})
        if result is None:
            return ''
        return result['lineuser_key']
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def fetch_lineuser_id(lineuser_key: str) -> int:
    try:
        query = "SELECT lineuser_id FROM lineusers WHERE lineuser_key = :lineuser_key"
        result = await database.fetch_one(query, {"lineuser_key": lineuser_key})
        if result is None:
            raise HTTPException(status_code=404, detail="Lineuser not found")
        return result['lineuser_id']
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


async def insert_user_info(user_id: str, user_name: str):
    try:
        insert_query = """
        INSERT INTO lineusers (lineuser_key, lineuser_name) VALUES (:userId, :displayName)
        """
        await database.execute(insert_query, {"userId": user_id, "displayName": user_name})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))