# from db import database
from fastapi import HTTPException
from db import database_date, database_num


# 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))