"""
Marginal CLV-band ROI, FIRST-BREAK version (exclusive bins, one bet/fixture).
For each fixture+side, walk pre-kickoff snapshots in time order and take the
FIRST moment CLV breaks positive (CLV >= 0). Enter at that snapshot's ask,
bucket into band [X,X+1) by the value at that first break, settle via ftr.
Flat 1-unit stakes so ROI is comparable across bands.
"""
import csv, glob, os
from collections import defaultdict

os.chdir(r'E:\soccer')
files = sorted(glob.glob(r'live_data\*.csv'))

series = defaultdict(lambda: {'rows': [], 'ftr': None})

def fval(r, k):
    try: return float(r.get(k, ''))
    except (TypeError, ValueError): return None

for fp in files:
    with open(fp, newline='', encoding='utf-8', errors='replace') as f:
        for r in csv.DictReader(f):
            if r.get('status') != 'PRE':
                continue
            base = (r['game_date'], r['home'], r['away'])
            ftr = r.get('ftr') or None
            for side in ('home', 'away'):
                clv = fval(r, f'clv_kalshi_{side}')
                ask = fval(r, f'kalshi_{side}_ask')
                d = series[base + (side,)]
                if clv is not None and ask is not None and 0 < ask < 1:
                    d['rows'].append((r['timestamp'], clv * 100, ask))
                if ftr:
                    d['ftr'] = ftr

for d in series.values():
    d['rows'].sort()

bands = defaultdict(lambda: {'n': 0, 'w': 0, 'ret': 0.0, 'ask': 0.0})
for key, d in series.items():
    if not d['ftr'] or not d['rows']:
        continue
    side = key[3]
    win = (d['ftr'] == ('H' if side == 'home' else 'A'))
    # first snapshot where CLV breaks positive
    entry = next(((c, a) for _, c, a in d['rows'] if c >= 0), None)
    if entry is None:
        continue
    c, a = entry
    band = min(int(c), 25)
    b = (1 - a) / a
    bd = bands[band]
    bd['n'] += 1
    bd['ask'] += a
    if win:
        bd['w'] += 1
        bd['ret'] += b
    else:
        bd['ret'] -= 1

print(f"{'BAND':>7}{'N':>6}{'W':>5}{'WR%':>7}{'avgAsk':>8}{'ROI%':>9}{'units':>9}")
print('-' * 51)
tot_n = tot_w = 0; tot_ret = 0.0
for lo in range(0, 26):
    if lo not in bands:
        continue
    bd = bands[lo]; n = bd['n']; roi = 100 * bd['ret'] / n
    label = '25+' if lo == 25 else f'{lo}-{lo+1}'
    print(f"{label:>7}{n:>6}{bd['w']:>5}{100*bd['w']/n:>6.1f}{bd['ask']/n:>8.3f}{roi:>+9.1f}{bd['ret']:>+9.2f}")
    tot_n += n; tot_w += bd['w']; tot_ret += bd['ret']
print('-' * 51)
print(f"{'ALL':>7}{tot_n:>6}{tot_w:>5}{100*tot_w/tot_n:>6.1f}{'':>8}{100*tot_ret/tot_n:>+9.1f}{tot_ret:>+9.2f}")