import aiosqlite
import config
from datetime import datetime

class Database:
    def __init__(self):
        self.db_name = config.DB_NAME
    
    async def init_db(self):
        """Инициализация базы данных"""
        async with aiosqlite.connect(self.db_name) as db:
            # Таблица пользователей
            await db.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    user_id INTEGER PRIMARY KEY,
                    username TEXT,
                    first_name TEXT,
                    role_id INTEGER,
                    ingame_rank TEXT DEFAULT '',
                    daily_online INTEGER DEFAULT 0,
                    is_banned INTEGER DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (role_id) REFERENCES roles(id)
                )
            ''')
            
            # Таблица серверов
            await db.execute('''
                CREATE TABLE IF NOT EXISTS servers (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    ftp_host TEXT DEFAULT '',
                    ftp_port INTEGER DEFAULT 21,
                    ftp_user TEXT DEFAULT '',
                    ftp_password TEXT DEFAULT '',
                    db_host TEXT DEFAULT '',
                    db_port INTEGER DEFAULT 3306,
                    db_name TEXT DEFAULT '',
                    db_user TEXT DEFAULT '',
                    db_password TEXT DEFAULT '',
                    server_cfg TEXT DEFAULT '',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Таблица ролей
            await db.execute('''
                CREATE TABLE IF NOT EXISTS roles (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Таблица прав доступа
            await db.execute('''
                CREATE TABLE IF NOT EXISTS permissions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    role_id INTEGER,
                    permission TEXT NOT NULL,
                    FOREIGN KEY (role_id) REFERENCES roles(id),
                    UNIQUE(role_id, permission)
                )
            ''')
            
            # Таблица доступа пользователей к серверам
            await db.execute('''
                CREATE TABLE IF NOT EXISTS user_server_access (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER,
                    server_id INTEGER,
                    FOREIGN KEY (user_id) REFERENCES users(user_id),
                    FOREIGN KEY (server_id) REFERENCES servers(id),
                    UNIQUE(user_id, server_id)
                )
            ''')
            
            await db.commit()
    
    # ===== USERS =====
    async def add_user(self, user_id: int, username: str = None, first_name: str = None):
        """Добавить пользователя"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute(
                'INSERT OR IGNORE INTO users (user_id, username, first_name) VALUES (?, ?, ?)',
                (user_id, username, first_name)
            )
            await db.commit()
    
    async def get_user(self, user_id: int):
        """Получить информацию о пользователе"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM users WHERE user_id = ?', (user_id,)) as cursor:
                return await cursor.fetchone()
    
    async def update_user_role(self, user_id: int, role_id: int):
        """Обновить роль пользователя"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('UPDATE users SET role_id = ? WHERE user_id = ?', (role_id, user_id))
            await db.commit()
    
    async def update_user_ingame_rank(self, user_id: int, rank: str):
        """Обновить игровой ранг пользователя"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('UPDATE users SET ingame_rank = ? WHERE user_id = ?', (rank, user_id))
            await db.commit()
    
    async def ban_user(self, user_id: int):
        """Забанить пользователя"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('UPDATE users SET is_banned = 1 WHERE user_id = ?', (user_id,))
            await db.commit()
    
    async def unban_user(self, user_id: int):
        """Разбанить пользователя"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('UPDATE users SET is_banned = 0 WHERE user_id = ?', (user_id,))
            await db.commit()
    
    async def is_user_banned(self, user_id: int) -> bool:
        """Проверить, забанен ли пользователь"""
        async with aiosqlite.connect(self.db_name) as db:
            async with db.execute('SELECT is_banned FROM users WHERE user_id = ?', (user_id,)) as cursor:
                result = await cursor.fetchone()
                return result[0] == 1 if result else False
    
    async def get_all_users(self):
        """Получить всех пользователей"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM users') as cursor:
                return await cursor.fetchall()
    
    # ===== SERVERS =====
    async def create_server(self, name: str):
        """Создать сервер"""
        async with aiosqlite.connect(self.db_name) as db:
            cursor = await db.execute('INSERT INTO servers (name) VALUES (?)', (name,))
            await db.commit()
            return cursor.lastrowid
    
    async def delete_server(self, server_id: int):
        """Удалить сервер"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('DELETE FROM servers WHERE id = ?', (server_id,))
            await db.execute('DELETE FROM user_server_access WHERE server_id = ?', (server_id,))
            await db.commit()
    
    async def get_server(self, server_id: int):
        """Получить информацию о сервере"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM servers WHERE id = ?', (server_id,)) as cursor:
                return await cursor.fetchone()
    
    async def get_all_servers(self):
        """Получить все серверы"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM servers') as cursor:
                return await cursor.fetchall()
    
    async def update_server_ftp(self, server_id: int, host: str, port: int, user: str, password: str):
        """Обновить FTP данные сервера"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute(
                'UPDATE servers SET ftp_host = ?, ftp_port = ?, ftp_user = ?, ftp_password = ? WHERE id = ?',
                (host, port, user, password, server_id)
            )
            await db.commit()
    
    async def update_server_db(self, server_id: int, host: str, port: int, db_name: str, user: str, password: str):
        """Обновить данные MariaDB сервера"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute(
                'UPDATE servers SET db_host = ?, db_port = ?, db_name = ?, db_user = ?, db_password = ? WHERE id = ?',
                (host, port, db_name, user, password, server_id)
            )
            await db.commit()
    
    async def update_server_cfg(self, server_id: int, cfg_content: str):
        """Обновить server.cfg"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('UPDATE servers SET server_cfg = ? WHERE id = ?', (cfg_content, server_id))
            await db.commit()
    
    # ===== ROLES =====
    async def create_role(self, name: str):
        """Создать роль"""
        async with aiosqlite.connect(self.db_name) as db:
            try:
                cursor = await db.execute('INSERT INTO roles (name) VALUES (?)', (name,))
                await db.commit()
                return cursor.lastrowid
            except aiosqlite.IntegrityError:
                return None
    
    async def delete_role(self, role_id: int):
        """Удалить роль"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute('DELETE FROM roles WHERE id = ?', (role_id,))
            await db.execute('DELETE FROM permissions WHERE role_id = ?', (role_id,))
            await db.execute('UPDATE users SET role_id = NULL WHERE role_id = ?', (role_id,))
            await db.commit()
    
    async def get_role(self, role_id: int):
        """Получить роль"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM roles WHERE id = ?', (role_id,)) as cursor:
                return await cursor.fetchone()
    
    async def get_all_roles(self):
        """Получить все роли"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('SELECT * FROM roles') as cursor:
                return await cursor.fetchall()
    
    # ===== PERMISSIONS =====
    async def add_permission(self, role_id: int, permission: str):
        """Добавить право доступа роли"""
        async with aiosqlite.connect(self.db_name) as db:
            try:
                await db.execute(
                    'INSERT INTO permissions (role_id, permission) VALUES (?, ?)',
                    (role_id, permission)
                )
                await db.commit()
                return True
            except aiosqlite.IntegrityError:
                return False
    
    async def remove_permission(self, role_id: int, permission: str):
        """Удалить право доступа роли"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute(
                'DELETE FROM permissions WHERE role_id = ? AND permission = ?',
                (role_id, permission)
            )
            await db.commit()
    
    async def get_role_permissions(self, role_id: int):
        """Получить права доступа роли"""
        async with aiosqlite.connect(self.db_name) as db:
            async with db.execute(
                'SELECT permission FROM permissions WHERE role_id = ?',
                (role_id,)
            ) as cursor:
                rows = await cursor.fetchall()
                return [row[0] for row in rows]
    
    async def has_permission(self, user_id: int, permission: str) -> bool:
        """Проверить, есть ли у пользователя право доступа"""
        async with aiosqlite.connect(self.db_name) as db:
            async with db.execute('''
                SELECT COUNT(*) FROM users u
                JOIN permissions p ON u.role_id = p.role_id
                WHERE u.user_id = ? AND p.permission = ?
            ''', (user_id, permission)) as cursor:
                result = await cursor.fetchone()
                return result[0] > 0 if result else False
    
    # ===== USER SERVER ACCESS =====
    async def grant_server_access(self, user_id: int, server_id: int):
        """Дать доступ пользователю к серверу"""
        async with aiosqlite.connect(self.db_name) as db:
            try:
                await db.execute(
                    'INSERT INTO user_server_access (user_id, server_id) VALUES (?, ?)',
                    (user_id, server_id)
                )
                await db.commit()
                return True
            except aiosqlite.IntegrityError:
                return False
    
    async def revoke_server_access(self, user_id: int, server_id: int):
        """Забрать доступ пользователя к серверу"""
        async with aiosqlite.connect(self.db_name) as db:
            await db.execute(
                'DELETE FROM user_server_access WHERE user_id = ? AND server_id = ?',
                (user_id, server_id)
            )
            await db.commit()
    
    async def get_user_servers(self, user_id: int):
        """Получить серверы, к которым у пользователя есть доступ"""
        async with aiosqlite.connect(self.db_name) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute('''
                SELECT s.* FROM servers s
                JOIN user_server_access usa ON s.id = usa.server_id
                WHERE usa.user_id = ?
            ''', (user_id,)) as cursor:
                return await cursor.fetchall()
    
    async def has_server_access(self, user_id: int, server_id: int) -> bool:
        """Проверить, есть ли у пользователя доступ к серверу"""
        async with aiosqlite.connect(self.db_name) as db:
            async with db.execute(
                'SELECT COUNT(*) FROM user_server_access WHERE user_id = ? AND server_id = ?',
                (user_id, server_id)
            ) as cursor:
                result = await cursor.fetchone()
                return result[0] > 0 if result else False