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

print("="*60)
print("SIGNALS DATABASE ANALYSIS")
print("="*60)

total=c.execute('SELECT COUNT(*) FROM signals').fetchone()[0]
resolved=c.execute('SELECT COUNT(*) FROM signals WHERE market_resolved=?',(1,)).fetchone()[0]
correct=c.execute('SELECT COUNT(*) FROM signals WHERE signal_correct=?',(1,)).fetchone()[0]
incorrect=c.execute('SELECT COUNT(*) FROM signals WHERE signal_correct=?',(0,)).fetchone()[0]

print(f"\nTotal signals: {total}")
print(f"Resolved: {resolved} | Correct: {correct} | Incorrect: {incorrect}")
print(f"Win rate: {correct/resolved:.2%}")
print(f"Avg ROI: {c.execute('SELECT AVG(theoretical_roi) FROM signals WHERE market_resolved=?',(1,)).fetchone()[0]:.2%}")

print("\n=== IAS Distribution ===")
for lo,hi in [(0.0,0.3),(0.3,0.4),(0.4,0.5),(0.5,0.6),(0.6,0.7),(0.7,0.8),(0.8,1.0)]:
    row=c.execute('SELECT COUNT(*), AVG(information_asymmetry_score) FROM signals WHERE information_asymmetry_score>? AND information_asymmetry_score<=?',(lo,hi)).fetchone()
    n=row[0]; avg=round(row[1],3) if row[1] else 0
    bar='#'*int(n*0.4)
    print(f"  {lo}-{hi}: {n:3d} signals, avg={avg:.3f} {bar}")

print("\n=== Win Rate by IAS Tier ===")
for lo,hi in [(0.3,0.4),(0.4,0.5),(0.5,0.6),(0.6,0.7),(0.7,0.8),(0.8,1.0)]:
    n=c.execute('SELECT COUNT(*) FROM signals WHERE information_asymmetry_score>? AND information_asymmetry_score<=?',(lo,hi)).fetchone()[0]
    if n>0:
        cr=c.execute('SELECT COUNT(*) FROM signals WHERE information_asymmetry_score>? AND information_asymmetry_score<=? AND signal_correct=?',(lo,hi,1)).fetchone()[0]
        print(f"  IAS {lo}-{hi}: {cr}/{n} correct ({cr/n:.1%})")

print("\n=== Trade Size Distribution ===")
bins=[(0,1000),(1000,2000),(2000,3000),(3000,5000),(5000,10000),(10000,20000),(20000,50000),(50000,999999)]
for lo,hi in bins:
    row=c.execute('SELECT COUNT(*), AVG(trade_size_usd) FROM signals WHERE trade_size_usd>? AND trade_size_usd<=?',(lo,hi)).fetchone()
    n=row[0]; avg=round(row[1],0) if row[1] else 0
    if n>0:
        print(f"  ${lo:,.0f}-${hi:,.0f}: {n:3d} signals, avg ${avg:,.0f}")

print("\n=== Signal Time Range ===")
earliest=c.execute('SELECT MIN(detected_at) FROM signals').fetchone()[0]
latest=c.execute('SELECT MAX(detected_at) FROM signals').fetchone()[0]
print(f"  First: {earliest}")
print(f"  Last:  {latest}")

print("\n=== Top 10 Signals by IAS ===")
for r in c.execute('SELECT trade_side, trade_outcome, trade_size_usd, trade_price, market_question, information_asymmetry_score, signal_correct, theoretical_roi FROM signals ORDER BY information_asymmetry_score DESC LIMIT 10').fetchall():
    c_str='CORRECT' if r[6] else ('WRONG' if r[6] is not None else 'PENDING')
    roi=f'{r[7]:.2%}' if r[7] else 'N/A'
    print(f"  IAS={r[5]:.2f} | {r[0]} {r[1]} @ {r[3]:.4f} ${r[2]:,.0f} | {r[4][:55]}... [{c_str}] ROI={roi}")

print("\n=== Sample LLM Analysis Reasoning (highest IAS) ===")
top=c.execute('SELECT reasoning, insider_evidence FROM signals ORDER BY information_asymmetry_score DESC LIMIT 3').fetchall()
for i, (reasoning, evidence) in enumerate(top):
    print(f"\n--- Signal #{i+1} ---")
    print(f"Reasoning (first 500 chars): {str(reasoning)[:500]}")
    print(f"Insider evidence: {str(evidence)[:500] if evidence else 'None'}")