check_transactions.py

import sqlite3
from datetime import datetime
from web3 import Web3
from dotenv import load_dotenv
import os
import json
import time
from config import DB_NAME

# Load environment variables
load_dotenv()

# Chain settings
CHAINS = {
    'ethereum': {
        'name': 'Ethereum Mainnet',
        'rpc': os.getenv('ETH_RPC_URL', 'https://eth-mainnet.g.alchemy.com/v2/your-api-key'),
        'chain_id': 1
    },
    'avalanche': {
        'name': 'Avalanche C-Chain',
        'rpc': os.getenv('AVAX_RPC_URL', 'https://api.avax.network/ext/bc/C/rpc'),
        'chain_id': 43114
    },
    'base': {
        'name': 'Base Chain',
        'rpc': os.getenv('BASE_RPC_URL', 'https://mainnet.base.org'),
        'chain_id': 8453
    },
    'bsc': {
        'name': 'BNB Smart Chain',
        'rpc': os.getenv('BSC_RPC_URL', 'https://bsc-dataseed.binance.org'),
        'chain_id': 56
    },
    'polygon': {
        'name': 'Polygon',
        'rpc': os.getenv('POLYGON_RPC_URL', 'https://polygon-rpc.com'),
        'chain_id': 137
    },
    'arbitrum': {
        'name': 'Arbitrum One',
        'rpc': os.getenv('ARBITRUM_RPC_URL', 'https://arb1.arbitrum.io/rpc'),
        'chain_id': 42161
    }
}

def log_message(message):
    """Display log message with timestamp"""
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    print(f"[{timestamp}] {message}")

def setup_database():
    """Set up database tables"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # Transaction check status table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS transaction_checks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        address TEXT,
        chain_name TEXT,
        has_transactions BOOLEAN,
        tx_count INTEGER,
        last_checked TIMESTAMP,
        UNIQUE(address, chain_name)
    )
    ''')

    # Active addresses table (addresses with transactions)
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS active_addresses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        address TEXT UNIQUE,
        private_key TEXT,
        first_found_date TIMESTAMP,
        chains_with_transactions TEXT,
        total_tx_count INTEGER
    )
    ''')

    conn.commit()
    conn.close()
    log_message("Database setup completed")

def is_address_checked(address, chain_name):
    """Check if address has been checked on specified chain"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    cursor.execute('''
    SELECT id FROM transaction_checks 
    WHERE address = ? AND chain_name = ?
    ''', (address, chain_name))
    
    result = cursor.fetchone() is not None
    conn.close()
    return result

def log_transaction_check(address, chain_name, has_transactions, tx_count):
    """Log transaction check results"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    try:
        cursor.execute('''
        INSERT INTO transaction_checks 
        (address, chain_name, has_transactions, tx_count, last_checked)
        VALUES (?, ?, ?, ?, ?)
        ''', (address, chain_name, has_transactions, tx_count, datetime.now()))
        conn.commit()
        log_message(f"Recorded transaction check: {address} on {chain_name} (TX count: {tx_count})")
    except sqlite3.IntegrityError:
        # Update if already exists
        cursor.execute('''
        UPDATE transaction_checks 
        SET has_transactions = ?, tx_count = ?, last_checked = ?
        WHERE address = ? AND chain_name = ?
        ''', (has_transactions, tx_count, datetime.now(), address, chain_name))
        conn.commit()
        log_message(f"Updated transaction check: {address} on {chain_name} (TX count: {tx_count})")
    finally:
        conn.close()

def update_active_address(address, private_key, chain_name, tx_count):
    """Update active address information"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    try:
        # Check existing record
        cursor.execute('SELECT chains_with_transactions, total_tx_count FROM active_addresses WHERE address = ?', (address,))
        result = cursor.fetchone()
        
        if result:
            # Update existing chain information
            chains = json.loads(result[0])
            if chain_name not in chains:
                chains.append(chain_name)
            total_tx_count = result[1] + tx_count
            
            cursor.execute('''
            UPDATE active_addresses 
            SET chains_with_transactions = ?, total_tx_count = ?
            WHERE address = ?
            ''', (json.dumps(chains), total_tx_count, address))
            log_message(f"Updated active address: {address} (Total TX count: {total_tx_count})")
        else:
            # Create new record
            cursor.execute('''
            INSERT INTO active_addresses 
            (address, private_key, first_found_date, chains_with_transactions, total_tx_count)
            VALUES (?, ?, ?, ?, ?)
            ''', (address, private_key, datetime.now(), json.dumps([chain_name]), tx_count))
            log_message(f"Added new active address: {address} (TX count: {tx_count})")
        
        conn.commit()
    finally:
        conn.close()

def check_transactions(address, chain_config):
    """Check transactions for address on specified chain"""
    w3 = Web3(Web3.HTTPProvider(chain_config['rpc']))
    
    try:
        # Get transaction count
        tx_count = w3.eth.get_transaction_count(address)
        log_message(f"Transaction check: {address} on {chain_config['name']} (TX count: {tx_count})")
        return tx_count > 0, tx_count
    except Exception as e:
        log_message(f"Error ({chain_config['name']}): {str(e)}")
        return False, 0

def process_addresses():
    """Process all unchecked addresses on all chains"""
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Get discovered keys
    cursor.execute('SELECT private_key, eth_address FROM found_keys')
    keys = cursor.fetchall()
    conn.close()
    
    total_addresses = len(keys)
    processed = 0
    
    log_message(f"Total addresses to check: {total_addresses}")
    
    for private_key, address in keys:
        processed += 1
        if not address:
            continue
            
        log_message(f"[{processed}/{total_addresses}] Checking address: {address}")
        
        for chain_id, chain_config in CHAINS.items():
            if is_address_checked(address, chain_id):
                log_message(f"Skip: {chain_config['name']} already checked")
                continue
                
            log_message(f"Checking: {chain_config['name']}")
            has_tx, tx_count = check_transactions(address, chain_config)
            
            log_transaction_check(address, chain_id, has_tx, tx_count)
            
            if has_tx:
                log_message(f"Found transactions: {chain_config['name']} ({tx_count} transactions)")
                update_active_address(address, private_key, chain_id, tx_count)
            
            # Delay between chains
            time.sleep(1)

def main():
    """Main execution function"""
    log_message("Starting transaction check")
    setup_database()
    process_addresses()
    log_message("Transaction check completed")

if __name__ == "__main__":
    main()