from typing import List, Dict
from fastapi import HTTPException
from config import logger
from db import database_yokohama

async def clear_ranking_data():
    """
    // book_rankingsとkeywordsテーブルの全データを削除（初期化）します。
    // TRUNCATE TABLEを使い、データを高速に削除しIDもリセットします。
    """
    try:
        query_rankings = "TRUNCATE TABLE book_rankings;"
        query_keywords = "TRUNCATE TABLE keywords;"

        await database_yokohama.execute(query=query_rankings)
        await database_yokohama.execute(query=query_keywords)
        logger.info("Tables 'book_rankings' and 'keywords' have been cleared.")
    except Exception as e:
        logger.error(f"Error clearing tables: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")

async def save_book_rankings(rankings: List[Dict]):
    """
    // スクレイピングで取得した複数の書籍の基本情報をMySQL DBに保存します。
    """
    if not rankings:
        return

    values_to_insert = [
        {"title": r["title"], "source": r.get("source", "rakuten")} for r in rankings
    ]

    try:
        query = """
            INSERT IGNORE INTO book_rankings (title, source)
            VALUES (:title, :source)
        """
        await database_yokohama.execute_many(query=query, values=values_to_insert)
        logger.info(f"Saved or ignored {len(values_to_insert)} book rankings in MySQL.")
    except Exception as e:
        logger.error(f"Error during bulk insert of book rankings to MySQL: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")

async def save_keywords_list(keywords: List[str]):
    """
    // [修正] キーワードリストを`keywords`テーブルに保存します。
    // `link_keywords_to_book`からこの機能だけを分離しました。
    """
    if not keywords:
        return

    try:
        values = [{"keyword": kw} for kw in keywords]
        # // INSERT IGNOREを使い、既に存在するキーワードはエラーを出さずに無視します。
        query = "INSERT IGNORE INTO keywords (keyword) VALUES (:keyword)"
        await database_yokohama.execute_many(query=query, values=values)
    except Exception as e:
        logger.error(f"Error saving keywords list to MySQL: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")

async def link_keywords_to_book(title: str, keywords: List[str]):
    """
    // [新規] 書籍のタイトルを基準に、`book_rankings`テーブルの`keyword`カラムを更新します。
    // これが本来この関数名が担うべき役割です。
    """
    if not keywords:
        return

    keywords_str = ",".join(keywords)
    try:
        query = """
            UPDATE book_rankings SET keyword = :keyword WHERE title = :title
        """
        await database_yokohama.execute(query, values={"keyword": keywords_str, "title": title})
    except Exception as e:
        logger.error(f"Error linking keywords to '{title}' in MySQL: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")

async def get_random_keywords(limit: int = 4) -> List[str]:
    """
    // `keywords`テーブルから重複なくランダムにキーワードを取得します。
    """
    try:
        query = """
            SELECT keyword FROM keywords
            WHERE keyword IS NOT NULL AND keyword != ''
            GROUP BY keyword
            ORDER BY RAND()
            LIMIT :limit
        """
        results = await database_yokohama.fetch_all(query, values={"limit": limit})
        return [row["keyword"] for row in results]
    except Exception as e:
        logger.error(f"Error fetching random keywords: {e}")
        return []


# ===================================================
# チャット関連の関数 (Djangoテンプレートから移植・修正)
# ===================================================

async def get_chat_history(session_id: str) -> List[Dict]:
    """
    // 指定されたsession_idの会話履歴をDBから取得します。
    """
    try:
        query = """
            SELECT user_message, assistant_response, status
            FROM chat_history
            WHERE session_id = :session_id AND status != '99'
            ORDER BY sort_order ASC
        """
        return await database_yokohama.fetch_all(query, values={"session_id": session_id})
    except Exception as e:
        logger.error(f"Error fetching chat history for session_id {session_id}: {e}")
        return []

async def save_chat_message(chat_token: str, status: str, message: str, response: str):
    """
    // 1回の対話ターンをDBに保存します。
    """
    try:
        # // まず、現在のセッションの最後のsort_orderを取得します
        sort_order_query = "SELECT MAX(sort_order) as max_order FROM chat_history WHERE session_id = :session_id"
        last_order_result = await database_yokohama.fetch_one(sort_order_query, values={"session_id": chat_token})

        # // 新しいsort_orderを計算します（履歴がなければ1から開始）
        next_order = (last_order_result["max_order"] or 0) + 1

        insert_query = """
            INSERT INTO chat_history (session_id, sort_order, status, user_message, assistant_response)
            VALUES (:session_id, :sort_order, :status, :user_message, :assistant_response)
        """
        values = {
            "session_id": chat_token,
            "sort_order": next_order,
            "status": str(status),
            "user_message": message,
            "assistant_response": response
        }
        await database_yokohama.execute(insert_query, values=values)
    except Exception as e:
        logger.error(f"Error saving chat turn for session_id {chat_token}: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")

async def delete_chat_history(session_id: str):
    """
    // 指定されたsession_idの会話履歴をすべて削除します。
    """
    try:
        query = "DELETE FROM chat_history WHERE session_id = :session_id"
        await database_yokohama.execute(query, values={"session_id": session_id})
        logger.info(f"Chat history for session {session_id} has been cleared.")
    except Exception as e:
        logger.error(f"Error deleting chat history for session {session_id}: {e}")
        raise HTTPException(status_code=500, detail=f"DB Error: {e}")