abot/db/encyclopedia.py

# -*- coding: utf-8 -*-
from typing import Dict, List, Optional, Tuple

from db.base import BaseDBOperator
from db.connection import DBConnectionManager


class EncyclopediaDB(BaseDBOperator):
    """百科答题游戏相关数据库操作"""
    
    def __init__(self, db_manager: DBConnectionManager):
        super().__init__(db_manager)
    
    def add_group(self, group_id: str) -> bool:
        """添加群组"""
        sql = "INSERT INTO t_encyclopedia_groups (group_id) VALUES (%s)"
        return self.execute_update(sql, (group_id,))
    
    def check_group_exists(self, group_id: str) -> bool:
        """检查群组是否存在"""
        sql = "SELECT 1 FROM t_encyclopedia_groups WHERE group_id = %s"
        result = self.execute_query(sql, (group_id,), fetch_one=True)
        return result is not None
    
    def add_player(self, player_id: str, group_id: str, player_name: str) -> bool:
        """添加玩家"""
        sql = """
        INSERT INTO t_encyclopedia_players (player_id, group_id, player_name)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE player_name = VALUES(player_name)
        """
        return self.execute_update(sql, (player_id, group_id, player_name))
    
    def get_active_task(self, group_id: str) -> Optional[Dict]:
        """获取群组的活跃任务"""
        sql = """
        SELECT active_task_id, group_id, question, answer, score, description, holder_id, assigned_at, status
        FROM t_encyclopedia_active_tasks
        WHERE group_id = %s AND status = 'pending'
        ORDER BY assigned_at DESC
        LIMIT 1
        """
        return self.execute_query(sql, (group_id,), fetch_one=True)
    
    def add_task_history(self, group_id: str, task_id: int, player_id: str, 
                         answer: str, is_correct: bool, points_earned: int) -> bool:
        """添加任务历史记录"""
        sql = """
        INSERT INTO t_encyclopedia_task_history
        (group_id, active_task_id, player_id, answer, is_correct, points_earned)
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        params = (group_id, task_id, player_id, answer, 1 if is_correct else 0, points_earned)
        return self.execute_update(sql, params)
    
    def get_player_ranking(self, group_id: str, limit: int = 10) -> List[Dict]:
        """获取玩家排名"""
        sql = """
        SELECT player_name, points 
        FROM t_encyclopedia_players 
        WHERE group_id = %s 
        ORDER BY points DESC 
        LIMIT %s
        """
        return self.execute_query(sql, (group_id, limit)) or []
    
    def create_active_task(self, group_id: str, question: str, answer: str, 
                          score: int, description: str, holder_id: str) -> Optional[int]:
        """创建活跃任务"""
        sql = """
        INSERT INTO t_encyclopedia_active_tasks 
        (group_id, question, answer, score, description, holder_id) 
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        if self.execute_update(sql, (group_id, question, answer, score, description, holder_id)):
            # 获取最新创建的任务ID
            get_id_sql = """
            SELECT active_task_id 
            FROM t_encyclopedia_active_tasks 
            WHERE group_id = %s AND question = %s AND holder_id = %s 
            ORDER BY assigned_at DESC LIMIT 1
            """
            result = self.execute_query(get_id_sql, (group_id, question, holder_id), fetch_one=True)
            return result['active_task_id'] if result else None
        return None
    
    def update_player_points(self, player_id: str, group_id: str, points: int) -> bool:
        """更新玩家积分"""
        if points > 0:
            sql = """
            UPDATE t_encyclopedia_players 
            SET points = points + %s 
            WHERE group_id = %s AND player_id = %s
            """
        else:
            sql = """
            UPDATE t_encyclopedia_players 
            SET points = GREATEST(points + %s, 0) 
            WHERE group_id = %s AND player_id = %s
            """
        return self.execute_update(sql, (points, group_id, player_id))
    
    def complete_task(self, active_task_id: int) -> bool:
        """完成任务"""
        sql = """
        UPDATE t_encyclopedia_active_tasks 
        SET status = 'completed' 
        WHERE active_task_id = %s
        """
        return self.execute_update(sql, (active_task_id,))
    
    def get_player(self, player_id: str, group_id: str) -> Optional[Dict]:
        """获取玩家信息"""
        sql = """
        SELECT player_id, player_name, points 
        FROM t_encyclopedia_players 
        WHERE group_id = %s AND player_id = %s
        """
        return self.execute_query(sql, (group_id, player_id), fetch_one=True)
    
    def get_all_groups(self) -> List[str]:
        """获取所有群组ID"""
        sql = "SELECT group_id FROM t_encyclopedia_groups"
        results = self.execute_query(sql)
        return [row['group_id'] for row in results] if results else []

    def get_all_players_in_group(self, group_id: str) -> List[Dict]:
        """获取群组中的所有玩家"""
        sql = "SELECT player_id, player_name FROM t_encyclopedia_players WHERE group_id = %s"
        return self.execute_query(sql, (group_id,)) or []

    def get_active_tasks_in_group(self, group_id: str) -> List[Dict]:
        """获取群组中的所有活跃任务"""
        sql = """
        SELECT a.active_task_id, a.question, p.player_name, p.player_id
        FROM t_encyclopedia_active_tasks a
        JOIN t_encyclopedia_players p ON a.holder_id = p.player_id AND a.group_id = p.group_id
        WHERE a.group_id = %s AND a.status = 'pending'
        """
        return self.execute_query(sql, (group_id,)) or []

    def get_task_by_id(self, group_id: str, task_id: int) -> Optional[Dict]:
        """根据ID获取任务"""
        sql = """
        SELECT question, answer, score, holder_id, status 
        FROM t_encyclopedia_active_tasks 
        WHERE group_id = %s AND active_task_id = %s
        """
        return self.execute_query(sql, (group_id, task_id), fetch_one=True)

    def get_task_holder(self, group_id: str, holder_id: str) -> Optional[Dict]:
        """获取任务持有者信息"""
        sql = "SELECT player_name FROM t_encyclopedia_players WHERE group_id = %s AND player_id = %s"
        return self.execute_query(sql, (group_id, holder_id), fetch_one=True)