from typing import Dict, Any, List
import sqlite3
import asyncio
from datetime import datetime

class DatabaseManager:
    def __init__(self, db_path: str = "mining_data.db"):
        self.db_path = db_path
        self.init_database()

    def init_database(self):
        """Initialize database tables"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        # Create wallet table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS wallets (
            address TEXT PRIMARY KEY,
            balance REAL,
            last_updated TIMESTAMP,
            total_mined REAL,
            total_transactions INTEGER
        )
        ''')

        # Create transactions table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS transactions (
            tx_hash TEXT PRIMARY KEY,
            wallet_address TEXT,
            amount REAL,
            fee REAL,
            timestamp TIMESTAMP,
            type TEXT,
            status TEXT,
            block_height INTEGER,
            confirmations INTEGER,
            FOREIGN KEY (wallet_address) REFERENCES wallets(address)
        )
        ''')

        # Create mining_stats table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS mining_stats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TIMESTAMP,
            hash_rate REAL,
            blocks_mined INTEGER,
            power_consumption REAL,
            temperature REAL,
            earnings REAL
        )
        ''')

        conn.commit()
        conn.close()

    async def add_wallet(self, wallet_data: Dict[str, Any]) -> bool:
        """Add new wallet to database"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT INTO wallets (address, balance, last_updated, total_mined, total_transactions)
            VALUES (?, ?, ?, ?, ?)
            ''', (
                wallet_data['address'],
                wallet_data.get('balance', 0.0),
                datetime.now(),
                wallet_data.get('total_mined', 0.0),
                wallet_data.get('total_transactions', 0)
            ))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"Error adding wallet: {str(e)}")
            return False

    async def get_wallet_details(self, address: str) -> Dict[str, Any]:
        """Get wallet details"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            SELECT * FROM wallets WHERE address = ?
            ''', (address,))
            
            result = cursor.fetchone()
            if result:
                return {
                    'address': result[0],
                    'balance': result[1],
                    'last_updated': result[2],
                    'total_mined': result[3],
                    'total_transactions': result[4]
                }
            return None
        finally:
            conn.close()

    async def update_wallet_balance(self, address: str, new_balance: float) -> bool:
        """Update wallet balance"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            UPDATE wallets 
            SET balance = ?, last_updated = ?
            WHERE address = ?
            ''', (new_balance, datetime.now(), address))
            
            conn.commit()
            conn.close()
            return True
        except:
            return False

    async def add_transaction(self, tx_data: Dict[str, Any]) -> bool:
        """Add new transaction"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT INTO transactions 
            (tx_hash, wallet_address, amount, fee, timestamp, type, status, block_height, confirmations)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                tx_data['tx_hash'],
                tx_data['wallet_address'],
                tx_data['amount'],
                tx_data['fee'],
                datetime.now(),
                tx_data['type'],
                tx_data['status'],
                tx_data.get('block_height', 0),
                tx_data.get('confirmations', 0)
            ))
            
            conn.commit()
            conn.close()
            return True
        except:
            return False

    async def get_transactions(self, wallet_address: str, limit: int = 50) -> List[Dict[str, Any]]:
        """Get wallet transactions"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            SELECT * FROM transactions 
            WHERE wallet_address = ?
            ORDER BY timestamp DESC
            LIMIT ?
            ''', (wallet_address, limit))
            
            transactions = []
            for row in cursor.fetchall():
                transactions.append({
                    'tx_hash': row[0],
                    'wallet_address': row[1],
                    'amount': row[2],
                    'fee': row[3],
                    'timestamp': row[4],
                    'type': row[5],
                    'status': row[6],
                    'block_height': row[7],
                    'confirmations': row[8]
                })
            
            return transactions
        finally:
            conn.close()

    async def add_mining_stats(self, stats: Dict[str, Any]) -> bool:
        """Add mining statistics"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT INTO mining_stats 
            (timestamp, hash_rate, blocks_mined, power_consumption, temperature, earnings)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', (
                datetime.now(),
                stats.get('hash_rate', 0.0),
                stats.get('blocks_mined', 0),
                stats.get('power_consumption', 0.0),
                stats.get('temperature', 0.0),
                stats.get('earnings', 0.0)
            ))
            
            conn.commit()
            conn.close()
            return True
        except:
            return False

    async def get_mining_stats(self, time_range: str = '24h') -> List[Dict[str, Any]]:
        """Get mining statistics for a time range"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            if time_range == '24h':
                timeframe = "timestamp >= datetime('now', '-1 day')"
            elif time_range == '7d':
                timeframe = "timestamp >= datetime('now', '-7 days')"
            elif time_range == '30d':
                timeframe = "timestamp >= datetime('now', '-30 days')"
            else:
                timeframe = "1=1"
            
            cursor.execute(f'''
            SELECT * FROM mining_stats 
            WHERE {timeframe}
            ORDER BY timestamp DESC
            ''')
            
            stats = []
            for row in cursor.fetchall():
                stats.append({
                    'timestamp': row[1],
                    'hash_rate': row[2],
                    'blocks_mined': row[3],
                    'power_consumption': row[4],
                    'temperature': row[5],
                    'earnings': row[6]
                })
            
            return stats
        finally:
            conn.close()
