import sqlite3
import os
from typing import List, Dict, Any, Optional


class TableManager:
    def __init__(self, db_path: str = 'user_data.db'):
        base_dir = os.path.dirname(os.path.abspath(__file__))
        self.db_path = os.path.join(base_dir, 'user_data.db')

    def _execute(self, sql: str, params: tuple = ()) -> sqlite3.Cursor:
        """执行SQL语句的通用方法"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute(sql, params)
            conn.commit()
            return cursor


class UserManager(TableManager):
    def add_user(self, qq_id: str, ea_player_name: str, ea_player_id: str, ea_user_id: str, designation: str,
                 medals: str) -> int:
        """添加用户"""
        cursor = self._execute(
            "INSERT INTO users (qq_id, ea_player_name,ea_player_id,ea_user_id, designation,medals) VALUES (?, ?, ?, ?, ?,?)",
            (qq_id, ea_player_name, ea_player_id, ea_user_id, designation, medals)
        )
        return cursor.lastrowid

    def update_user(self, qq_id: str, ea_player_name: str, ea_player_id: str, ea_user_id: str, designation: str,
                    medals: str) -> int:
        """修改用户"""
        cursor = self._execute(
            "UPDATE users SET ea_player_name = ?, ea_player_id = ?,ea_user_id = ? WHERE qq_id = ?",
            (ea_player_name, ea_player_id, ea_user_id, qq_id)
        )
        return cursor.rowcount

    def get_user_by_qq(self, qq_id: str) -> Optional[Dict[str, Any]]:
        cursor = self._execute(
            "SELECT * FROM users WHERE qq_id = ?",
            (qq_id,)
        )
        row = cursor.fetchone()
        return dict(row) if row else None

    def get_user_by_ea_id(self, ea_user_id: str) -> Optional[Dict[str, Any]]:
        cursor = self._execute(
            "SELECT * FROM users WHERE ea_user_id = ?",
            (ea_user_id,)
        )
        row = cursor.fetchone()
        return dict(row) if row else None

    def delete_user_by_qq(self, qq_id: str) -> Optional[Dict[str, Any]]:
        cursor = self._execute(
            "DELETE FROM users WHERE qq_id = ?",
            (qq_id,)
        )
        return cursor.rowcount

    def update_dog_tags(self, qq_id: int, medals: str) -> bool:
        """更新用户的狗牌列表"""
        self._execute(
            "UPDATE users SET medals = ? WHERE id = ?",
            (medals, qq_id)
        )
        return True


class DesignationManager(TableManager):
    def create_designation(self, name: str, icon_path: str) -> int:
        """创建新标签"""
        cursor = self._execute(
            "INSERT INTO designation (name,icon_path) VALUES (?,?)",
            (name, icon_path)
        )
        return cursor.lastrowid

    def get_all_designation(self) -> List[Dict[str, Any]]:
        """获取所有标签"""
        cursor = self._execute("SELECT * FROM designation")
        return [dict(row) for row in cursor.fetchall()]

    def get_designation_by_id_list(self, ids: List[int]) -> List[Dict[str, Any]]:
        """根据 ID 列表获取标签"""

        if not ids:
            return []

        placeholders = ",".join(["?"] * len(ids))
        sql = f"""
            SELECT *
            FROM designation
            WHERE id IN ({placeholders})
        """

        cursor = self._execute(sql, ids)
        return [dict(row) for row in cursor.fetchall()]


# class QueryRecordManager(TableManager):
#     def log_query(self, user_id: str, target_id: str, status: str) -> int:
#         """记录查询操作"""
#         cursor = self._execute(
#             """INSERT INTO query_record
#             (user_id, target_id, status)
#             VALUES (?, ?, ?)""",
#             (user_id, target_id, status)
#         )
#         return cursor.lastrowid
#
#     def get_user_history(self, user_id: str) -> List[Dict[str, Any]]:
#         """获取用户查询历史"""
#         cursor = self._execute(
#             "SELECT * FROM query_record WHERE user_id = ?",
#             (user_id,)
#         )
#         return [dict(row) for row in cursor.fetchall()]


if __name__ == "__main__":
    tag_db = DesignationManager()
    # tag_id = tag_db.create_designation("DEV","DEV.png")
    # tag_id = tag_db.create_designation("DICE", "DICE.png")
    # tag_id = tag_db.create_designation("IC", "IC.png")
    # tag_id = tag_db.create_designation("OWNER", "OWNER.png")
    # print(f"Created tag with ID: {tag_id}")

    record_id = tag_db.get_designation_by_id_list([1, 2, 3])

    print(f"Logged query with ID: {record_id}")
