import sqlite3
conn=sqlite3.connect('data/signals.db')
c=conn.cursor()

# Trade size vs IAS relationship
print("=== Trade Size vs IAS (by size bucket) ===")
for lo,hi in [(0,2000),(2000,5000),(5000,10000),(10000,20000),(20000,50000),(50000,999999)]:
    row=c.execute('SELECT COUNT(*), AVG(information_asymmetry_score), AVG(trade_size_usd) FROM signals WHERE trade_size_usd>? AND trade_size_usd<=?',(lo,hi)).fetchone()
    n=row[0]
    if n>0:
        avg_i = round(row[1],3)
        avg_s = round(row[2],0)
        print(f"  ${lo:,.0f}-${hi:,.0f}: {n:3d} sigs, avg IAS={avg_i:.3f}, avg size=${avg_s:,.0f}")

print()

# Market question frequency
print("=== Top 10 Most Frequently Signaled Markets ===")
for r in c.execute('SELECT market_question, COUNT(*) as cnt, AVG(information_asymmetry_score) as avg_i FROM signals GROUP BY market_question ORDER BY cnt DESC LIMIT 10').fetchall():
    print(f"  {r[0][:65]:65s} | {r[1]:3d} sigs | avg IAS={r[2]:.3f}")

print()

# Resolution status
print("=== Unresolved Signals (PENDING) ===")
pending=c.execute('SELECT COUNT(*) FROM signals WHERE market_resolved IS NULL OR market_resolved=?',(0,)).fetchone()[0]
print(f"  Pending: {pending}")

print()

# Trader wallet frequency
print("=== Top 10 Most Frequent Trader Wallets ===")
for r in c.execute('SELECT trader_wallet, COUNT(*) as cnt, AVG(information_asymmetry_score) as avg_i FROM signals GROUP BY trader_wallet ORDER BY cnt DESC LIMIT 10').fetchall():
    print(f"  {str(r[0])[:25]:25s} | {r[1]:3d} sigs | avg IAS={r[2]:.3f}")