show_balance.py

import sqlite3
from datetime import datetime
from tabulate import tabulate
from config import DB_NAME

# 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 show_latest_balances():
    """Display latest balance status"""
    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:
        print("No balance data found")
        return
    
    print(f"\n=== Latest Balance Status ({format_datetime(latest_date)}) ===")
    
    # Get balances by address
    cursor.execute('''
    SELECT address, chain_id, balance
    FROM balance_history
    WHERE check_date = ?
    ORDER BY address, chain_id
    ''', (latest_date,))
    balances = cursor.fetchall()
    
    if balances:
        # Display by address
        current_address = None
        address_balances = []
        
        for address, chain_id, balance in balances:
            if balance > 0:  # Only show non-zero balances
                address_balances.append([
                    address,
                    CHAINS[chain_id]['name'],
                    f"{balance:.8f}",
                    CHAINS[chain_id]['symbol']
                ])
        
        if address_balances:
            print("\n=== Address Balances ===")
            headers = ["Address", "Chain", "Balance", "Currency"]
            print(tabulate(address_balances, headers=headers, tablefmt="grid"))
        
        # Total balance by chain
        cursor.execute('''
        SELECT chain_id, SUM(balance) as total
        FROM balance_history
        WHERE check_date = ?
        GROUP BY chain_id
        HAVING total > 0
        ORDER BY total DESC
        ''', (latest_date,))
        totals = cursor.fetchall()
        
        if totals:
            print("\n=== Total Balance by Chain ===")
            headers = ["Chain", "Total Balance", "Currency"]
            table_data = [
                [CHAINS[chain_id]['name'], f"{total:.8f}", CHAINS[chain_id]['symbol']]
                for chain_id, total in totals
            ]
            print(tabulate(table_data, headers=headers, tablefmt="grid"))
    
    conn.close()

def show_balance_history():
    """Display balance history"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Get all balance history
    cursor.execute('''
    SELECT 
        address,
        chain_id,
        balance,
        check_date
    FROM balance_history
    WHERE balance > 0
    ORDER BY check_date DESC, address, chain_id
    ''')
    history = cursor.fetchall()
    
    if history:
        print("\n=== Balance History ===")
        headers = ["Check Date", "Address", "Chain", "Balance", "Currency"]
        table_data = [
            [
                format_datetime(date),
                addr,
                CHAINS[chain_id]['name'],
                f"{balance:.8f}",
                CHAINS[chain_id]['symbol']
            ]
            for addr, chain_id, balance, date in history
        ]
        print(tabulate(table_data, headers=headers, tablefmt="grid"))
    else:
        print("\nNo balance history found")
    
    conn.close()

def main():
    """Main execution function"""
    try:
        setup_database()
        show_latest_balances()
        print("\n" + "="*50)
        show_balance_history()
    except Exception as e:
        print(f"An error occurred: {str(e)}")

if __name__ == "__main__":
    main()