import os
import sqlite3
from nonebot import logger
from typing import Optional, List, Tuple


class DatabaseManager:
    def __init__(self, db_path: str = 'user_data.db'):
        self.db_path = db_path
        self.expected_tables = {
            'users': [
                ('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
                ('qq_id', 'TEXT NOT NULL'),
                ('ea_player_name', 'TEXT UNIQUE'),
                ('ea_player_id', 'TEXT UNIQUE'),
                ('ea_user_id', 'TEXT UNIQUE'),
                ('designation', 'TEXT NOT NULL'),
                ('medals', 'TEXT NOT NULL'),
                ('create_time', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP')
            ],
            'designation': [
                ('id', 'INTEGER PRIMARY KEY'),
                ('name', 'TEXT NOT NULL'),
                ('icon_path', 'TEXT NOT NULL'),
                ('create_time', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP')
            ],
            'medals': [
                ('id', 'INTEGER PRIMARY KEY'),
                ('name', 'TEXT NOT NULL'),
                ('icon_path', 'TEXT NOT NULL'),
                ('create_time', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP')
            ]
            ,
            'query_record': [
                ('id', 'INTEGER PRIMARY KEY'),
                ('user_id', 'TEXT NOT NULL'),
                ('ea_player_name', 'TEXT NOT NULL'),
                ('ea_player_id', 'TEXT NOT NULL'),
                ('ea_user_id', 'TEXT NOT NULL'),
                ('status', 'TEXT NOT NULL'),
                ('create_time', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP')
            ]
        }

    def initialize_database(self) -> bool:
        """初始化数据库并创建表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 创建所有表
                for table_name, columns in self.expected_tables.items():
                    columns_sql = ', '.join(f'{col} {typ}' for col, typ in columns)
                    cursor.execute(f'''
                        CREATE TABLE IF NOT EXISTS {table_name} (
                            {columns_sql}
                        )
                    ''')

                conn.commit()
                return True

        except Exception as e:
            logger.error(f"Database initialization failed: {str(e)}")
            if os.path.exists(self.db_path):
                conn.close()  # 确保连接关闭
                for _ in range(3):  # 重试机制
                    try:
                        os.remove(self.db_path)
                        break
                    except PermissionError:
                        time.sleep(0.5)
            return False

    def _verify_database_integrity(self, conn: sqlite3.Connection) -> bool:
        """验证数据库完整性"""
        cursor = conn.cursor()

        # 检查表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        existing_tables = {row[0] for row in cursor.fetchall()}

        if existing_tables != set(self.expected_tables.keys()):
            logger.warning(f"Missing tables: {set(self.expected_tables.keys()) - existing_tables}")
            return False

        # 检查每个表的结构
        for table_name, expected_columns in self.expected_tables.items():
            cursor.execute(f"PRAGMA table_info({table_name})")
            actual_columns = [(row[1], row[2]) for row in cursor.fetchall()]

            if not self._compare_columns(actual_columns, expected_columns):
                logger.warning(f"Table {table_name} structure mismatch")
                return False

        # 执行完整性检查
        cursor.execute("PRAGMA integrity_check")
        integrity_result = cursor.fetchone()
        if integrity_result[0] != 'ok':
            logger.warning(f"Integrity check failed: {integrity_result}")
            return False

        return True

    def _compare_columns(self, actual: List[Tuple], expected: List[Tuple]) -> bool:
        """比较实际列与预期列是否匹配"""
        if len(actual) != len(expected):
            return False

        for (act_col, act_type), (exp_col, exp_type) in zip(actual, expected):
            if act_col.lower() != exp_col.lower():
                return False
            if not act_type.upper().startswith(exp_type.upper()):
                return False

        return True


# 初始化
if __name__ == '__main__':
    db_manager = DatabaseManager()
    if db_manager.initialize_database():
        print("Database initialized successfully")
        print(f"Database file created at: {os.path.abspath(db_manager.db_path)}")
    else:
        print("Database initialization failed")
