spore_burn_utils.py

"""
spore_burn_utils.py
Database query functions for burn statistics.
Used by the Flask API to serve pre-computed burn data.
"""

import os
import json
import time
import datetime
import psycopg2
from dotenv import load_dotenv
from web3 import Web3

load_dotenv()

# ============================================================================
# On-Chain Constants
# ============================================================================
# Token contracts
AVAX_TOKEN_ADDRESS = '0x6e7f5C0b9f4432716bDd0a77a3601291b9D9e985'
BSC_TOKEN_ADDRESS  = '0x33A3d962955A3862C8093D1273344719f03cA17C'

# Burn / bridge addresses
ZERO_ADDRESS  = '0x0000000000000000000000000000000000000000'
DEAD_ADDRESS  = '0x000000000000000000000000000000000000dEaD'
AVAX_BRIDGE   = '0x1aFCEF48379ECad5a6D790cE85ad1c87458C0f07'

# Liquidity pools
PANGOLIN_AVAX_LP  = '0x0a63179a8838b5729E79D239940d7e29e40A0116'  # SPORE/AVAX
USDC_POOL         = '0x18D2641f562AEC732a94f208417D9B84f376C70d'  # SPORE/USDC
PANCAKESWAP_LP    = '0x4aA8F0ef7dd950e260d5EeaF50A1D796D0cefd2f'  # SPORE/BNB

# RPC endpoints
AVAX_RPC = 'https://api.avax.network/ext/bc/C/rpc'
BSC_RPC  = 'https://bsc-dataseed1.defibit.io/'

SPORE_DECIMALS = 9

# Load ABI (same one used by cmc_api.py)
try:
    with open(os.path.join(os.path.dirname(__file__), 'abi', 'spore_abi.json')) as _f:
        SPORE_ABI = json.load(_f)
except FileNotFoundError:
    SPORE_ABI = None

# Cache for on-chain queries (2 min TTL, matches spore_price_utils.py)
_onchain_cache = {'data': None, 'timestamp': 0}
_CACHE_TTL = 120  # seconds


def get_burn_db_connection():
    """Connect to the burn statistics database."""
    return psycopg2.connect(
        database=os.getenv("database", "spore_db"),
        host=os.getenv("host", "localhost"),
        user=os.getenv("user", "postgres"),
        password=os.getenv("password", ""),
        port=os.getenv("port", "5432")
    )


def _format_token_amount(raw_str, decimals=9):
    """Format a raw token amount string to a human-readable form."""
    try:
        raw = int(raw_str)
        whole = raw // (10 ** decimals)
        frac = raw % (10 ** decimals)
        return f"{whole:,}.{frac:0{decimals}d}"
    except (ValueError, TypeError):
        return raw_str


def _ts_to_date(ts):
    """Convert unix timestamp to ISO date string."""
    if ts:
        return datetime.datetime.utcfromtimestamp(ts).strftime('%Y-%m-%d')
    return None


def _ts_to_datetime(ts):
    """Convert unix timestamp to ISO datetime string."""
    if ts:
        return datetime.datetime.utcfromtimestamp(ts).strftime('%Y-%m-%dT%H:%M:%SZ')
    return None


# ============================================================================
# Summary
# ============================================================================
def get_burn_summary(chain=None):
    """
    Get overall burn summary for one or both chains.
    Returns the latest snapshot data + total burn event count.
    """
    conn = get_burn_db_connection()
    try:
        if chain:
            return _get_chain_summary(conn, chain)
        else:
            avax = _get_chain_summary(conn, 'avax')
            bsc = _get_chain_summary(conn, 'bsc')
            # Compute combined cross-chain totals (like cmc_api.py bscBurned + avaBurned)
            try:
                avax_burned = int(avax.get('total_burned', '0'))
            except (ValueError, TypeError):
                avax_burned = 0
            try:
                bsc_burned = int(bsc.get('total_burned', '0'))
            except (ValueError, TypeError):
                bsc_burned = 0

            combined_burned = avax_burned + bsc_burned

            return {
                'avax': avax,
                'bsc': bsc,
                'combined': {
                    'total_burned': str(combined_burned),
                    'total_burned_formatted': _format_token_amount(str(combined_burned)),
                    'avax_burned': avax.get('total_burned', '0'),
                    'avax_burned_formatted': avax.get('total_burned_formatted', '0'),
                    'bsc_burned': bsc.get('total_burned', '0'),
                    'bsc_burned_formatted': bsc.get('total_burned_formatted', '0'),
                    'total_burn_tx_count': (avax.get('burn_tx_count', 0) +
                                            bsc.get('burn_tx_count', 0)),
                }
            }
    finally:
        conn.close()


def _get_chain_summary(conn, chain):
    """Summary for a single chain."""
    with conn.cursor() as cur:
        # Get the latest daily snapshot (most recent total_burned)
        cur.execute("""
            SELECT total_burned, total_supply, bucket_timestamp
            FROM burn_snapshots
            WHERE chain = %s AND bucket_type = 'daily'
            ORDER BY bucket_timestamp DESC
            LIMIT 1
        """, (chain,))
        row = cur.fetchone()

        if not row:
            return {'chain': chain, 'status': 'no_data'}

        total_burned = row[0]
        total_supply = row[1]
        last_snapshot_ts = row[2]

        # Count burn events
        cur.execute(
            "SELECT COUNT(*) FROM burn_events WHERE chain = %s",
            (chain,)
        )
        burn_count = cur.fetchone()[0]

        # Get indexing state
        cur.execute(
            "SELECT last_block_number, last_block_timestamp, last_stats_update, status "
            "FROM indexing_state WHERE chain = %s",
            (chain,)
        )
        state_row = cur.fetchone()

        # Compute percent burned
        try:
            pct = (int(total_burned) / int(total_supply)) * 100 if int(total_supply) > 0 else 0
        except (ValueError, ZeroDivisionError):
            pct = 0

        result = {
            'chain': chain,
            'total_burned': total_burned,
            'total_burned_formatted': _format_token_amount(total_burned),
            'total_supply': total_supply,
            'pct_burned': f"{pct:.6f}",
            'burn_tx_count': burn_count,
            'last_snapshot_date': _ts_to_date(last_snapshot_ts),
        }

        if state_row:
            result['last_indexed_block'] = state_row[0]
            result['last_indexed_timestamp'] = _ts_to_datetime(state_row[1])
            result['last_stats_update'] = _ts_to_datetime(state_row[2])
            result['indexing_status'] = state_row[3]

        return result


# ============================================================================
# Time Series
# ============================================================================
def get_burn_time_series(chain, bucket_type='monthly', limit=None, since=None):
    """
    Get burn time series data for charts.
    Returns list of {timestamp, date, total_burned, period_burned}.
    """
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            query = """
                SELECT bucket_timestamp, total_burned, period_burned, total_supply, burn_tx_count
                FROM burn_snapshots
                WHERE chain = %s AND bucket_type = %s
            """
            params = [chain, bucket_type]

            if since:
                query += " AND bucket_timestamp >= %s"
                params.append(since)

            query += " ORDER BY bucket_timestamp ASC"

            if limit:
                query += " LIMIT %s"
                params.append(limit)

            cur.execute(query, params)
            rows = cur.fetchall()

        results = []
        for row in rows:
            results.append({
                'timestamp': row[0],
                'date': _ts_to_date(row[0]),
                'total_burned': row[1],
                'total_burned_formatted': _format_token_amount(row[1]),
                'period_burned': row[2],
                'period_burned_formatted': _format_token_amount(row[2]),
                'total_supply': row[3],
                'burn_tx_count': row[4] or 0,
            })

        return results
    finally:
        conn.close()


# ============================================================================
# Top Burners
# ============================================================================
def get_top_burners(chain, limit=20):
    """
    Get leaderboard of top burning wallets.
    Ordered by total_burned descending.
    """
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT address, total_burned, burn_count,
                       first_burn_timestamp, last_burn_timestamp
                FROM top_burners
                WHERE chain = %s
                ORDER BY CAST(total_burned AS NUMERIC) DESC
                LIMIT %s
            """, (chain, limit))
            rows = cur.fetchall()

        results = []
        for rank, row in enumerate(rows, 1):
            results.append({
                'rank': rank,
                'address': row[0],
                'total_burned': row[1],
                'total_burned_formatted': _format_token_amount(row[1]),
                'burn_count': row[2],
                'first_burn': _ts_to_date(row[3]),
                'last_burn': _ts_to_date(row[4]),
            })

        return results
    finally:
        conn.close()


# ============================================================================
# Burn Events
# ============================================================================
def get_burn_events(chain, limit=50, since=None):
    """
    Get individual burn events, most recent first.
    """
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            query = """
                SELECT tx_hash, burner_address, burn_address, amount,
                       block_number, block_timestamp
                FROM burn_events
                WHERE chain = %s
            """
            params = [chain]

            if since:
                query += " AND block_timestamp >= %s"
                params.append(since)

            query += " ORDER BY block_timestamp DESC LIMIT %s"
            params.append(limit)

            cur.execute(query, params)
            rows = cur.fetchall()

        results = []
        for row in rows:
            results.append({
                'tx_hash': row[0],
                'burner_address': row[1],
                'burn_address': row[2],
                'amount': row[3],
                'amount_formatted': _format_token_amount(row[3]),
                'block_number': row[4],
                'timestamp': row[5],
                'date': _ts_to_datetime(row[5]),
            })

        return results
    finally:
        conn.close()


# ============================================================================
# Indexing Status
# ============================================================================
def get_indexing_status():
    """Get indexing status for all chains."""
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT chain, last_block_number, last_block_timestamp,
                       last_stats_update, status, error_message
                FROM indexing_state
                ORDER BY chain
            """)
            rows = cur.fetchall()

        results = {}
        for row in rows:
            results[row[0]] = {
                'last_block': row[1],
                'last_block_date': _ts_to_datetime(row[2]),
                'last_stats_update': _ts_to_datetime(row[3]),
                'status': row[4],
                'error': row[5],
            }

        return results
    finally:
        conn.close()


# ============================================================================
# AVAX Reflection Constants (must match the contract / stats builder)
# ============================================================================
T_TOTAL_AVAX = 100000000000 * (10**6) * (10**9)


# ============================================================================
# Holder Balance (with reflections for AVAX)
# ============================================================================
def get_holder_balance(chain, address):
    """
    Get a holder's current balance including reflections.
    For AVAX: converts r_owned → reflected token balance using latest r_total.
    For BSC:  returns simple balance directly.
    """
    conn = get_burn_db_connection()
    try:
        address_lower = address.lower()

        with conn.cursor() as cur:
            # Get holder's r_owned (AVAX) or balance (BSC)
            cur.execute(
                "SELECT r_owned, last_seen_block FROM holder_state "
                "WHERE chain = %s AND address = %s",
                (chain, address_lower)
            )
            holder_row = cur.fetchone()

            if not holder_row:
                return {
                    'chain': chain,
                    'address': address_lower,
                    'status': 'not_found',
                    'balance': '0',
                    'balance_formatted': '0',
                }

            r_owned = int(holder_row[0])
            last_seen_block = holder_row[1]

            if chain == 'avax':
                # Get latest r_total to compute reflected balance
                cur.execute(
                    "SELECT r_total, t_fee_total FROM reflection_rate "
                    "WHERE chain = %s ORDER BY bucket_timestamp DESC LIMIT 1",
                    (chain,)
                )
                rate_row = cur.fetchone()

                if rate_row:
                    r_total = int(rate_row[0])
                    t_fee_total = int(rate_row[1])
                    current_rate = r_total // T_TOTAL_AVAX if T_TOTAL_AVAX != 0 else 0
                    balance = r_owned // current_rate if current_rate > 0 else 0
                else:
                    balance = 0
                    t_fee_total = 0

                return {
                    'chain': chain,
                    'address': address_lower,
                    'balance': str(balance),
                    'balance_formatted': _format_token_amount(str(balance)),
                    'r_owned': str(r_owned),
                    'last_seen_block': last_seen_block,
                    'total_fees_redistributed': str(t_fee_total),
                    'total_fees_formatted': _format_token_amount(str(t_fee_total)),
                }
            else:
                # BSC: r_owned IS the simple balance
                return {
                    'chain': chain,
                    'address': address_lower,
                    'balance': str(r_owned),
                    'balance_formatted': _format_token_amount(str(r_owned)),
                    'last_seen_block': last_seen_block,
                }
    finally:
        conn.close()


# ============================================================================
# Reflection Gains (balance vs net transfers)
# ============================================================================
def get_holder_reflection_gains(chain, address):
    """
    Calculate reflection gains for an AVAX holder:
    reflection_gain = current_balance - (sum_received - sum_sent)

    For BSC this is not applicable (no reflections), returns 0.
    """
    conn = get_burn_db_connection()
    try:
        address_lower = address.lower()

        # First get current balance
        holder_data = get_holder_balance(chain, address_lower)
        if holder_data.get('status') == 'not_found':
            return holder_data

        current_balance = int(holder_data.get('balance', '0'))

        if chain != 'avax':
            return {
                'chain': chain,
                'address': address_lower,
                'current_balance': str(current_balance),
                'current_balance_formatted': _format_token_amount(str(current_balance)),
                'reflection_gain': '0',
                'reflection_gain_formatted': '0',
                'note': 'BSC does not have reflection mechanics',
            }

        with conn.cursor() as cur:
            # Sum of tokens received (inflows)
            cur.execute(
                "SELECT COALESCE(SUM(CAST(tx_value AS NUMERIC)), 0) "
                "FROM transfers_avax WHERE tx_to = %s",
                (address_lower,)
            )
            total_received = int(cur.fetchone()[0])

            # Sum of tokens sent (outflows)
            cur.execute(
                "SELECT COALESCE(SUM(CAST(tx_value AS NUMERIC)), 0) "
                "FROM transfers_avax WHERE tx_from = %s",
                (address_lower,)
            )
            total_sent = int(cur.fetchone()[0])

        # Net token flow from explicit transfers
        net_transfers = total_received - total_sent
        # Reflection gain = how much more you hold than what transfers explain
        reflection_gain = current_balance - net_transfers

        return {
            'chain': chain,
            'address': address_lower,
            'current_balance': str(current_balance),
            'current_balance_formatted': _format_token_amount(str(current_balance)),
            'total_received': str(total_received),
            'total_received_formatted': _format_token_amount(str(total_received)),
            'total_sent': str(total_sent),
            'total_sent_formatted': _format_token_amount(str(total_sent)),
            'net_transfers': str(net_transfers),
            'net_transfers_formatted': _format_token_amount(str(net_transfers)),
            'reflection_gain': str(reflection_gain),
            'reflection_gain_formatted': _format_token_amount(str(reflection_gain)),
        }
    finally:
        conn.close()


# ============================================================================
# Reflection Rate Time Series
# ============================================================================
def get_reflection_rate_history(chain, limit=None):
    """
    Get the reflection rate (r_total) over time.
    Only meaningful for AVAX (reflection chain).
    Shows how the reflection multiplier has evolved.
    """
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            query = """
                SELECT bucket_timestamp, r_total, t_fee_total
                FROM reflection_rate
                WHERE chain = %s
                ORDER BY bucket_timestamp ASC
            """
            params = [chain]

            if limit:
                query += " LIMIT %s"
                params.append(limit)

            cur.execute(query, params)
            rows = cur.fetchall()

        results = []
        for row in rows:
            r_total = int(row[1])
            t_fee_total = int(row[2])
            rate = r_total // T_TOTAL_AVAX if T_TOTAL_AVAX != 0 else 0

            results.append({
                'timestamp': row[0],
                'date': _ts_to_date(row[0]),
                'r_total': row[1],
                'rate': str(rate),
                't_fee_total': row[2],
                't_fee_total_formatted': _format_token_amount(row[2]),
            })

        return results
    finally:
        conn.close()


# ============================================================================
# On-Chain Helpers
# ============================================================================
def _get_onchain_balance(web3, token_address, holder_address):
    """Get raw token balance for a holder via balanceOf()."""
    try:
        contract = web3.eth.contract(
            address=Web3.to_checksum_address(token_address),
            abi=SPORE_ABI
        )
        balance = contract.functions.balanceOf(
            Web3.to_checksum_address(holder_address)
        ).call()
        return int(balance)
    except Exception as e:
        print(f"[onchain] balanceOf error for {holder_address}: {e}")
        return None


def _get_onchain_total_supply(web3, token_address):
    """Get totalSupply() from on-chain."""
    try:
        contract = web3.eth.contract(
            address=Web3.to_checksum_address(token_address),
            abi=SPORE_ABI
        )
        return int(contract.functions.totalSupply().call())
    except Exception as e:
        print(f"[onchain] totalSupply error: {e}")
        return None


def _get_onchain_total_fees(web3, token_address):
    """Get totalFees() from the AVAX reflection contract."""
    try:
        contract = web3.eth.contract(
            address=Web3.to_checksum_address(token_address),
            abi=SPORE_ABI
        )
        return int(contract.functions.totalFees().call())
    except Exception as e:
        print(f"[onchain] totalFees error: {e}")
        return None


# ============================================================================
# Global Burn Stats (cross-chain dashboard)
# ============================================================================
def get_global_burn_stats():
    """
    Comprehensive cross-chain burn dashboard.
    Combines indexed data from Postgres with live on-chain verification.
    Uses the same supply math as cmc_api.py:
        supplyavax = maxSupply - avaBurned - bridgeLocked
        supplybsc  = bscTotalSupply - bscBurned
        circulatingSupply = supplyavax + supplybsc
    Results are cached for 2 minutes.
    """
    global _onchain_cache
    now = time.time()

    # Return cached result if fresh
    if _onchain_cache['data'] and (now - _onchain_cache['timestamp']) < _CACHE_TTL:
        return _onchain_cache['data']

    result = {
        'avax': {},
        'bsc': {},
        'global': {},
        'pools': {},
        'verification': {},
        'last_updated': datetime.datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ'),
    }

    # ------------------------------------------------------------------
    # 1. Indexed data from Postgres
    # ------------------------------------------------------------------
    conn = get_burn_db_connection()
    try:
        avax_summary = _get_chain_summary(conn, 'avax')
        bsc_summary  = _get_chain_summary(conn, 'bsc')

        # Get latest reflection rate for AVAX
        with conn.cursor() as cur:
            cur.execute(
                "SELECT r_total, t_fee_total FROM reflection_rate "
                "WHERE chain = 'avax' ORDER BY bucket_timestamp DESC LIMIT 1"
            )
            rate_row = cur.fetchone()
    finally:
        conn.close()

    indexed_avax_burned = int(avax_summary.get('total_burned', '0') or '0')
    indexed_bsc_burned  = int(bsc_summary.get('total_burned', '0') or '0')

    # ------------------------------------------------------------------
    # 2. Live on-chain data
    # ------------------------------------------------------------------
    onchain = {
        'avax_burned': None,
        'avax_bridge': None,
        'avax_total_supply': None,
        'avax_total_fees': None,
        'bsc_contract_balance': None,
        'bsc_total_supply': None,
        'pangolin_balance': None,
        'usdc_pool_balance': None,
        'pancakeswap_balance': None,
    }

    if SPORE_ABI:
        try:
            avax_w3 = Web3(Web3.HTTPProvider(AVAX_RPC))
            bsc_w3  = Web3(Web3.HTTPProvider(BSC_RPC))

            # AVAX on-chain
            if avax_w3.is_connected():
                onchain['avax_burned'] = _get_onchain_balance(
                    avax_w3, AVAX_TOKEN_ADDRESS, DEAD_ADDRESS)
                onchain['avax_bridge'] = _get_onchain_balance(
                    avax_w3, AVAX_TOKEN_ADDRESS, AVAX_BRIDGE)
                onchain['avax_total_supply'] = _get_onchain_total_supply(
                    avax_w3, AVAX_TOKEN_ADDRESS)
                onchain['avax_total_fees'] = _get_onchain_total_fees(
                    avax_w3, AVAX_TOKEN_ADDRESS)
                # Pool balances
                onchain['pangolin_balance'] = _get_onchain_balance(
                    avax_w3, AVAX_TOKEN_ADDRESS, PANGOLIN_AVAX_LP)
                onchain['usdc_pool_balance'] = _get_onchain_balance(
                    avax_w3, AVAX_TOKEN_ADDRESS, USDC_POOL)

            # BSC on-chain
            if bsc_w3.is_connected():
                # On BSC, tokens sent to ZERO_ADDRESS are physically removed from totalSupply.
                # So the contract balance represents ONLY the reflection fees locked.
                onchain['bsc_contract_balance'] = _get_onchain_balance(
                    bsc_w3, BSC_TOKEN_ADDRESS, BSC_TOKEN_ADDRESS)
                onchain['bsc_total_supply'] = _get_onchain_total_supply(
                    bsc_w3, BSC_TOKEN_ADDRESS)
                onchain['pancakeswap_balance'] = _get_onchain_balance(
                    bsc_w3, BSC_TOKEN_ADDRESS, PANCAKESWAP_LP)
        except Exception as e:
            print(f"[global] Web3 error: {e}")

    # ------------------------------------------------------------------
    # 3. Compute supply math (matching cmc_api.py)
    # ------------------------------------------------------------------
    # To get true on-chain BSC total burned, we must add the tokens that were
    # physically removed from supply (zero burns) to the active contract fees.
    # Since we can't query "total ever minted" on-chain, we rely on the indexer
    # for the exact zero burn count, plus the live on-chain contract balance.
    indexed_bsc_zero_burns = 0
    conn = get_burn_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT COALESCE(SUM(amount::numeric), 0) FROM burn_events WHERE chain = 'bsc' AND burn_address = %s", (ZERO_ADDRESS,))
            indexed_bsc_zero_burns = int(cur.fetchone()[0])
    finally:
        conn.close()

    avax_total_supply = onchain['avax_total_supply'] or T_TOTAL_AVAX
    avax_burned   = onchain['avax_burned'] if onchain['avax_burned'] is not None else indexed_avax_burned
    avax_bridge   = onchain['avax_bridge'] or 0

    bsc_total_supply = onchain['bsc_total_supply'] or 0
    bsc_contract_fees = onchain['bsc_contract_balance'] if onchain['bsc_contract_balance'] is not None else (indexed_bsc_burned - indexed_bsc_zero_burns)
    
    # Accurate total burned for BSC (Zero address burns + Contract locked fees)
    bsc_burned = indexed_bsc_zero_burns + bsc_contract_fees

    supply_avax = avax_total_supply - avax_burned - avax_bridge
    # BSC circulating is total circulating minus what's locked in the contract
    supply_bsc  = bsc_total_supply - bsc_contract_fees
    
    circulating = supply_avax + supply_bsc
    total_burned_global = avax_burned + bsc_burned

    # Pool totals
    pangolin_bal   = onchain['pangolin_balance'] or 0
    usdc_pool_bal  = onchain['usdc_pool_balance'] or 0
    pancake_bal    = onchain['pancakeswap_balance'] or 0
    total_pool_locked = pangolin_bal + usdc_pool_bal + pancake_bal

    # ------------------------------------------------------------------
    # 4. Build response
    # ------------------------------------------------------------------
    # AVAX section
    result['avax'] = {
        'total_supply': str(avax_total_supply),
        'total_supply_formatted': _format_token_amount(str(avax_total_supply)),
        'total_burned': str(avax_burned),
        'total_burned_formatted': _format_token_amount(str(avax_burned)),
        'bridge_locked': str(avax_bridge),
        'bridge_locked_formatted': _format_token_amount(str(avax_bridge)),
        'circulating_supply': str(supply_avax),
        'circulating_supply_formatted': _format_token_amount(str(supply_avax)),
        'pct_burned': f"{(avax_burned / avax_total_supply * 100):.6f}" if avax_total_supply else '0',
        'burn_tx_count': avax_summary.get('burn_tx_count', 0),
        'total_fees_redistributed': str(onchain['avax_total_fees'] or 0),
        'total_fees_formatted': _format_token_amount(
            str(onchain['avax_total_fees'] or 0)),
    }

    # Add reflection rate info if available
    if rate_row:
        r_total = int(rate_row[0])
        result['avax']['reflection_rate'] = str(r_total // T_TOTAL_AVAX if T_TOTAL_AVAX else 0)
        result['avax']['indexed_t_fee_total'] = str(rate_row[1])

    # BSC section
    result['bsc'] = {
        'total_supply': str(bsc_total_supply),
        'total_supply_formatted': _format_token_amount(str(bsc_total_supply)),
        'total_burned': str(bsc_burned),
        'total_burned_formatted': _format_token_amount(str(bsc_burned)),
        'contract_fees_locked': str(bsc_contract_fees),
        'contract_fees_formatted': _format_token_amount(str(bsc_contract_fees)),
        'zero_address_burns': str(indexed_bsc_zero_burns),
        'zero_address_burns_formatted': _format_token_amount(str(indexed_bsc_zero_burns)),
        'circulating_supply': str(supply_bsc),
        'circulating_supply_formatted': _format_token_amount(str(supply_bsc)),
        'pct_burned': f"{(bsc_burned / (bsc_total_supply + indexed_bsc_zero_burns) * 100):.6f}" if (bsc_total_supply + indexed_bsc_zero_burns) else '0',
        'burn_tx_count': bsc_summary.get('burn_tx_count', 0),
    }

    # Global section
    combined_supply = avax_total_supply + (bsc_total_supply + indexed_bsc_zero_burns)
    result['global'] = {
        'circulating_supply': str(circulating),
        'circulating_supply_formatted': _format_token_amount(str(circulating)),
        'total_burned_all_chains': str(total_burned_global),
        'total_burned_formatted': _format_token_amount(str(total_burned_global)),
        'pct_burned_global': f"{(total_burned_global / combined_supply * 100):.6f}" if combined_supply else '0',
        'bridge_locked': str(avax_bridge),
        'bridge_locked_formatted': _format_token_amount(str(avax_bridge)),
        'total_pool_locked': str(total_pool_locked),
        'total_pool_locked_formatted': _format_token_amount(str(total_pool_locked)),
    }

    # Pools section
    result['pools'] = {
        'pangolin_avax': {
            'address': PANGOLIN_AVAX_LP,
            'chain': 'avax',
            'pair': 'SPORE/AVAX',
            'balance': str(pangolin_bal),
            'balance_formatted': _format_token_amount(str(pangolin_bal)),
        },
        'usdc_pool': {
            'address': USDC_POOL,
            'chain': 'avax',
            'pair': 'SPORE/USDC',
            'balance': str(usdc_pool_bal),
            'balance_formatted': _format_token_amount(str(usdc_pool_bal)),
        },
        'pancakeswap_bnb': {
            'address': PANCAKESWAP_LP,
            'chain': 'bsc',
            'pair': 'SPORE/BNB',
            'balance': str(pancake_bal),
            'balance_formatted': _format_token_amount(str(pancake_bal)),
        },
    }

    # Verification section (indexed vs on-chain)
    avax_match = None
    bsc_match = None
    if onchain['avax_burned'] is not None:
        # Reflection tolerance: indexer math can diverge by up to 2% over millions of txs
        diff = abs(onchain['avax_burned'] - indexed_avax_burned)
        threshold = max(1, avax_burned * 2 // 100)  # 2%
        avax_match = diff <= threshold
    if onchain['bsc_contract_balance'] is not None:
        # BSC verification should compare apples to apples
        onchain_bsc_burned = indexed_bsc_zero_burns + onchain['bsc_contract_balance']
        diff = abs(onchain_bsc_burned - indexed_bsc_burned)
        threshold = max(1, onchain_bsc_burned * 2 // 100) # 2%
        bsc_match = diff <= threshold

    result['verification'] = {
        'avax_indexed_burned': str(indexed_avax_burned),
        'avax_indexed_formatted': _format_token_amount(str(indexed_avax_burned)),
        'avax_onchain_burned': str(onchain['avax_burned']) if onchain['avax_burned'] is not None else None,
        'avax_onchain_formatted': _format_token_amount(str(onchain['avax_burned'])) if onchain['avax_burned'] is not None else None,
        'avax_match': avax_match,
        
        'bsc_indexed_burned': str(indexed_bsc_burned),
        'bsc_indexed_formatted': _format_token_amount(str(indexed_bsc_burned)),
        'bsc_onchain_burned': str(indexed_bsc_zero_burns + onchain['bsc_contract_balance']) if onchain['bsc_contract_balance'] is not None else None,
        'bsc_onchain_formatted': _format_token_amount(str(indexed_bsc_zero_burns + onchain['bsc_contract_balance'])) if onchain['bsc_contract_balance'] is not None else None,
        'bsc_match': bsc_match,
    }

    # Cache the result
    _onchain_cache['data'] = result
    _onchain_cache['timestamp'] = now

    return result