import aiosqlite
from contextlib import asynccontextmanager
from typing import List, Dict, Any, Tuple, Optional, Union

class DatabaseManager:
    def __init__(self, db_name: str):
        self.db_name = db_name

    @asynccontextmanager
    async def get_connection(self):
        """Async context manager for database connections."""
        async with aiosqlite.connect(self.db_name) as conn:
            conn.row_factory = aiosqlite.Row
            try:
                yield conn
            finally:
                await conn.close()

    async def initialize_database(self):
        """Create database and initial tables if they don't exist."""
        async with self.get_connection() as conn:
            await conn.execute('''
                CREATE TABLE IF NOT EXISTS visitors (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ip TEXT DEFAULT NULL,
                    phrase TEXT DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            await conn.execute('''
                CREATE TABLE IF NOT EXISTS api_keys (
                    key_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ip TEXT NOT NULL,
                    api_key VARCHAR(64) UNIQUE,
                    active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            await conn.execute('''
                CREATE TABLE IF NOT EXISTS config (
                    id INTEGER PRIMARY KEY CHECK (id = 1),
                    enabled INTEGER DEFAULT 1,
                    callback TEXT DEFAULT '45.11.59.247:8000',
                    auth_key TEXT DEFAULT 'AD9U1JVCwYsl43EtQyvQLz9u6KuEQjLM3LOHmxfqeTtbk0QrxkFhBgpXkOJQ42sVF8J3xc5VZusvloi12pz6dcY3Mct3Ehwi'
                )
            ''')
            await conn.execute('''
                INSERT OR IGNORE INTO config (id) VALUES (1)
            ''')
            await conn.commit()

    async def create_table(self, table_name: str, columns: Dict[str, str]) -> bool:
        """Create a new table with specified columns."""
        try:
            async with self.get_connection() as conn:
                columns_def = ', '.join([f"{col} {dtype}" for col, dtype in columns.items()])
                query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_def})"
                await conn.execute(query)
                await conn.commit()
                return True
        except aiosqlite.Error as e:
            print(f"Error creating table: {e}")
            return False
        
    async def search_table(self, table_name: str, search_value: Union[str, int]) -> List[Dict]:
        """
        Search all columns in a table for a given value (string or int).
        
        Args:
            table_name: The table to search
            search_value: The value to look for in any column

        Returns:
            List of rows (as dicts) where the value appears in any column
        """
        try:
            async with self.get_connection() as conn:
                cursor = await conn.execute(f"PRAGMA table_info({table_name})")
                columns = [row[1] async for row in cursor]
                await cursor.close()

                if not columns:
                    return []

                if isinstance(search_value, int):
                    conditions = [f"{col} = ?" for col in columns]
                    params = [search_value] * len(columns)
                else:
                    conditions = [f"{col} LIKE ?" for col in columns]
                    params = [f"%{search_value}%" for _ in columns]

                where_clause = " OR ".join(conditions)
                query = f"SELECT * FROM {table_name} WHERE {where_clause}"

                cursor = await conn.execute(query, params)
                rows = [dict(row) async for row in cursor]
                await cursor.close()
                return rows

        except aiosqlite.Error as e:
            print(f"Error searching table: {e}")
            return []

    async def upsert(self, table_name: str, data: Dict[str, Any], 
                    condition: Optional[Dict[str, Any]] = None) -> bool:
        """
        Universal insert/update function. If the record matching the condition exists,
        it will be updated; otherwise, a new record will be inserted.
        
        Args:
            table_name: The table to operate on
            data: The data to insert or update
            condition: Key-value pairs to identify existing records. 
                      If None, will always insert.
        
        Returns:
            bool: Success or failure
        """
        try:
            async with self.get_connection() as conn:
                if condition:
                    where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                    where_values = tuple(condition.values())
                    
                    query = f"SELECT COUNT(*) as count FROM {table_name} WHERE {where_clause}"
                    cursor = await conn.execute(query, where_values)
                    result = await cursor.fetchone()
                    record_exists = result['count'] > 0
                    
                    if record_exists:
                        set_clause = ', '.join([f"{col} = ?" for col in data.keys()])
                        update_values = tuple(data.values()) + where_values
                        query = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
                        await conn.execute(query, update_values)
                        await conn.commit()
                        return True
                
                columns = ', '.join(data.keys())
                placeholders = ', '.join(['?' for _ in data])
                values = tuple(data.values())
                query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                await conn.execute(query, values)
                await conn.commit()
                return True
                
        except aiosqlite.Error as e:
            print(f"Error in upsert operation: {e}")
            return False

    async def get_rows(self, table_name: str, 
                      condition: Optional[Dict[str, Any]] = None,
                      order_by: Optional[str] = None,
                      limit: Optional[int] = None) -> List[Dict]:
        """
        Retrieve rows from the specified table based on conditions.
        
        Args:
            table_name: The table to query
            condition: Key-value pairs for WHERE clause
            order_by: Optional ORDER BY clause (e.g., "id DESC")
            limit: Optional limit for number of returned rows
            
        Returns:
            List of dictionaries representing rows
        """
        try:
            async with self.get_connection() as conn:
                query = f"SELECT * FROM {table_name}"
                params = ()
                
                if condition:
                    where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                    params = tuple(condition.values())
                    query += f" WHERE {where_clause}"
                
                if order_by:
                    query += f" ORDER BY {order_by}"
                    
                if limit:
                    query += f" LIMIT {limit}"
                
                cursor = await conn.execute(query, params)
                rows = [dict(row) async for row in cursor]
                await cursor.close()
                return rows
                
        except aiosqlite.Error as e:
            print(f"Error retrieving rows: {e}")
            return []

    async def update_rows(self, table_name: str, data: Dict[str, Any], 
                         condition: Dict[str, Any]) -> bool:
        """
        Update rows in the specified table based on condition.
        
        Args:
            table_name: The table to update
            data: Key-value pairs of fields to update
            condition: Key-value pairs for WHERE clause
            
        Returns:
            Success or failure
        """
        try:
            async with self.get_connection() as conn:
                set_clause = ', '.join([f"{col} = ?" for col in data.keys()])
                where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                
                values = tuple(data.values()) + tuple(condition.values())
                query = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
                
                await conn.execute(query, values)
                await conn.commit()
                return True
                
        except aiosqlite.Error as e:
            print(f"Error updating rows: {e}")
            return False

    async def delete_rows(self, table_name: str, condition: Dict[str, Any]) -> bool:
        """
        Delete rows from the specified table based on condition.
        
        Args:
            table_name: The table to delete from
            condition: Key-value pairs for WHERE clause
            
        Returns:
            Success or failure
        """
        try:
            async with self.get_connection() as conn:
                where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                params = tuple(condition.values())
                
                query = f"DELETE FROM {table_name} WHERE {where_clause}"
                await conn.execute(query, params)
                await conn.commit()
                return True
                
        except aiosqlite.Error as e:
            print(f"Error deleting rows: {e}")
            return False

    async def exists(self, table_name: str, condition: Dict[str, Any]) -> bool:
        """
        Check if a record exists based on condition.
        
        Args:
            table_name: The table to check
            condition: Key-value pairs for WHERE clause
            
        Returns:
            True if record exists, False otherwise
        """
        try:
            async with self.get_connection() as conn:
                where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                params = tuple(condition.values())
                
                query = f"SELECT COUNT(*) as count FROM {table_name} WHERE {where_clause}"
                cursor = await conn.execute(query, params)
                result = await cursor.fetchone()
                await cursor.close()
                
                return result['count'] > 0
                
        except aiosqlite.Error as e:
            print(f"Error checking existence: {e}")
            return False

    async def count(self, table_name: str, condition: Optional[Dict[str, Any]] = None) -> int:
        """
        Count records based on condition.
        
        Args:
            table_name: The table to count from
            condition: Key-value pairs for WHERE clause
            
        Returns:
            Number of matching records
        """
        try:
            async with self.get_connection() as conn:
                query = f"SELECT COUNT(*) as count FROM {table_name}"
                params = ()
                
                if condition:
                    where_clause = ' AND '.join([f"{k} = ?" for k in condition.keys()])
                    params = tuple(condition.values())
                    query += f" WHERE {where_clause}"
                
                cursor = await conn.execute(query, params)
                result = await cursor.fetchone()
                await cursor.close()
                
                return result['count']
                
        except aiosqlite.Error as e:
            print(f"Error counting records: {e}")
            return 0

    async def execute_query(self, query: str, params: Tuple = ()) -> List[Dict]:
        """
        Execute a custom query with parameters.
        
        Args:
            query: SQL query to execute
            params: Parameters for the query
            
        Returns:
            Results of the query
        """
        try:
            async with self.get_connection() as conn:
                cursor = await conn.execute(query, params)
                
                if query.strip().upper().startswith("SELECT"):
                    rows = [dict(row) async for row in cursor]
                    await cursor.close()
                    return rows
                else:
                    await conn.commit()
                    return []
                    
        except aiosqlite.Error as e:
            print(f"Error executing query: {e}")
            return []