report_balance.py

import sqlite3
from datetime import datetime
import yagmail
import os
from dotenv import load_dotenv
import json
from tabulate import tabulate
from config import DB_NAME

# Load environment variables
load_dotenv()

# Email settings
EMAIL_USER = os.getenv('EMAIL_USER')
EMAIL_PASSWORD = os.getenv('EMAIL_PASSWORD')
EMAIL_TO = os.getenv('EMAIL_TO')
BALANCE_THRESHOLD = float(os.getenv('BALANCE_THRESHOLD', '0.1'))

# Chain settings
CHAINS = {
    'ethereum': {
        'name': 'Ethereum Mainnet',
        'symbol': 'ETH'
    },
    'avalanche': {
        'name': 'Avalanche C-Chain',
        'symbol': 'AVAX'
    },
    'base': {
        'name': 'Base Chain',
        'symbol': 'ETH'
    },
    'bsc': {
        'name': 'BNB Smart Chain',
        'symbol': 'BNB'
    },
    'polygon': {
        'name': 'Polygon',
        'symbol': 'MATIC'
    },
    'arbitrum': {
        'name': 'Arbitrum One',
        'symbol': 'ETH'
    }
}

def setup_database():
    """Set up database tables"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Balance history table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS balance_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        address TEXT,
        chain_id TEXT,
        balance REAL,
        check_date TIMESTAMP,
        UNIQUE(address, chain_id, check_date)
    )
    ''')
    
    conn.commit()
    conn.close()

def format_datetime(dt):
    """Convert datetime to readable format"""
    if isinstance(dt, str):
        try:
            dt = datetime.fromisoformat(dt)
        except:
            return dt
    return dt.strftime('%Y-%m-%d %H:%M:%S')

def get_high_balance_accounts():
    """Get accounts with balance above threshold"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Get latest check date
    cursor.execute('SELECT MAX(check_date) FROM balance_history')
    latest_date = cursor.fetchone()[0]
    
    if not latest_date:
        return []
    
    # Get accounts with balance above threshold
    cursor.execute('''
    SELECT 
        b.address,
        b.chain_id,
        b.balance,
        a.private_key,
        a.chains_with_transactions
    FROM balance_history b
    JOIN active_addresses a ON b.address = a.address
    WHERE b.check_date = ? AND b.balance >= ?
    ORDER BY b.balance DESC
    ''', (latest_date, BALANCE_THRESHOLD))
    
    high_balance_accounts = cursor.fetchall()
    conn.close()
    
    return high_balance_accounts, latest_date

def create_report_content(accounts, check_date):
    """Create email report content"""
    if not accounts:
        return f"No accounts found with balance above threshold ({BALANCE_THRESHOLD})"
    
    content = [f"Balance Report ({format_datetime(check_date)})\n"]
    content.append(f"Balance Threshold: {BALANCE_THRESHOLD}\n")
    
    # Account information in table format
    headers = ["Address", "Private Key", "Chain", "Balance", "Active Chains"]
    table_data = []
    
    for addr, chain_id, balance, private_key, chains in accounts:
        chains_list = json.loads(chains)
        table_data.append([
            addr,
            private_key,
            CHAINS[chain_id]['name'],
            f"{balance:.8f} {CHAINS[chain_id]['symbol']}",
            ", ".join(chains_list)
        ])
    
    content.append(tabulate(table_data, headers=headers, tablefmt="grid"))
    
    # Calculate total balance by chain
    chain_totals = {}
    for _, chain_id, balance, _, _ in accounts:
        if chain_id not in chain_totals:
            chain_totals[chain_id] = 0
        chain_totals[chain_id] += balance
    
    content.append("\n=== Total Balance by Chain ===")
    for chain_id, total in chain_totals.items():
        content.append(f"{CHAINS[chain_id]['name']}: {total:.8f} {CHAINS[chain_id]['symbol']}")
    
    return "\n".join(content)

def send_email_report(content):
    """Send email report"""
    if not all([EMAIL_USER, EMAIL_PASSWORD, EMAIL_TO]):
        raise ValueError("Incomplete email settings. Please check your .env file.")
    
    subject = f"Ethereum Key Search Report - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
    
    try:
        # Initialize email client
        yag = yagmail.SMTP(EMAIL_USER, EMAIL_PASSWORD)
        
        # Send email
        yag.send(
            to=EMAIL_TO,
            subject=subject,
            contents=content
        )
        
        print("Email report sent successfully")
        
    except Exception as e:
        print(f"Email sending error: {str(e)}")
    finally:
        try:
            yag.close()
        except:
            pass

def main():
    """Main execution function"""
    try:
        setup_database()
        
        # Get high balance accounts
        accounts, check_date = get_high_balance_accounts()
        
        # Create report content
        report_content = create_report_content(accounts, check_date)
        
        if accounts:  # Only send email if accounts found
            send_email_report(report_content)
        else:
            print(report_content)
        
    except Exception as e:
        print(f"An error occurred: {str(e)}")

if __name__ == "__main__":
    main()