import pandas as pd

seasons = ['1920','2021','2122','2223','2324','2425','2526']
leagues = {
    'E0': 'EPL',
    'E1': 'Championship',
    'F1': 'Ligue 1',
    'S1': 'La Liga',
    'D1': 'Bundesliga',
    'I1': 'Serie A',
}

def simulate(bets):
    if not bets: return 0, 0.0, 0.0, 0.0
    profits = [(o-1) if w else -1 for o,w in bets]
    n = len(profits)
    total = sum(profits)
    return n, round(total,2), round(total/n*100,2), round(sum(w for _,w in bets)/n*100,1)

def remove_margin(h, d, a):
    over = 1/h + 1/d + 1/a
    return 1/h/over, 1/d/over, 1/a/over

all_league_data = {}

for code, name in leagues.items():
    dfs = []
    for s in seasons:
        try:
            d = pd.read_csv(f'E:/soccer/league_data/{code}_{s}.csv', encoding='utf-8-sig')
            d['Season'] = s
            dfs.append(d)
        except Exception as e:
            print(f"  {code} {s}: {e}")
    if not dfs:
        continue
    df = pd.concat(dfs, ignore_index=True)
    df = df.dropna(subset=['FTR','B365A','PSCH','PSCD','PSCA'])
    if len(df) == 0:
        print(f"{name}: no data with full odds")
        continue
    df = df.copy()
    tH,tD,tA = zip(*df.apply(lambda r: remove_margin(r['PSCH'],r['PSCD'],r['PSCA']), axis=1))
    df['PSC_fair_H'] = 1/pd.Series(tH, index=df.index)
    df['PSC_fair_D'] = 1/pd.Series(tD, index=df.index)
    df['PSC_fair_A'] = 1/pd.Series(tA, index=df.index)
    all_league_data[name] = df
    print(f"{name}: {len(df)} matches")

# ==============================================================
# MAIN TABLE: Away CLV across all leagues
# ==============================================================
print("\n" + "="*85)
print("AWAY CLV: B365 opening > Pinnacle closing fair — by league and edge threshold")
print("="*85)
print(f"{'League':<16} {'Edge':>6}  {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)

for name, df in all_league_data.items():
    for edge in [0.02, 0.05, 0.10]:
        sub = df[df['B365A'] > df['PSC_fair_A']*(1+edge)]
        bets = [(r['B365A'], r['FTR']=='A') for _,r in sub.iterrows()]
        n,p,roi,wr = simulate(bets)
        if n >= 10:
            marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
            print(f"{name:<16} >{edge*100:>4.0f}%  {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")
    print()

# ==============================================================
# HOME CLV
# ==============================================================
print("="*85)
print("HOME CLV: B365 opening > Pinnacle closing fair — by league")
print("="*85)
print(f"{'League':<16} {'Edge':>6}  {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)
for name, df in all_league_data.items():
    for edge in [0.02, 0.05, 0.10]:
        sub = df[df['B365H'] > df['PSC_fair_H']*(1+edge)]
        bets = [(r['B365H'], r['FTR']=='H') for _,r in sub.iterrows()]
        n,p,roi,wr = simulate(bets)
        if n >= 10:
            marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
            print(f"{name:<16} >{edge*100:>4.0f}%  {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")
    print()

# ==============================================================
# DRAW CLV
# ==============================================================
print("="*85)
print("DRAW CLV: B365 opening > Pinnacle closing fair — by league")
print("="*85)
print(f"{'League':<16} {'Edge':>6}  {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)
for name, df in all_league_data.items():
    for edge in [0.02, 0.05, 0.10]:
        sub = df[df['B365D'] > df['PSC_fair_D']*(1+edge)]
        bets = [(r['B365D'], r['FTR']=='D') for _,r in sub.iterrows()]
        n,p,roi,wr = simulate(bets)
        if n >= 10:
            marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
            print(f"{name:<16} >{edge*100:>4.0f}%  {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")
    print()

# ==============================================================
# PER-SEASON BREAKDOWN for Away CLV >2% across all leagues
# ==============================================================
print("="*85)
print("PER-SEASON Away CLV >2% — all leagues")
print("="*85)
print(f"{'League':<16}", end="")
for s in seasons:
    print(f"  {s:>9}", end="")
print()
print("-"*85)

for name, df in all_league_data.items():
    print(f"{name:<16}", end="")
    for s in seasons:
        sub = df[(df['Season']==s) & (df['B365A'] > df['PSC_fair_A']*1.02)]
        bets = [(r['B365A'], r['FTR']=='A') for _,r in sub.iterrows()]
        n,p,roi,wr = simulate(bets)
        if n >= 5:
            print(f"  {roi:>+8.1f}%", end="")
        else:
            print(f"  {'n/a':>9}", end="")
    print()

# ==============================================================
# COMBINED ALL LEAGUES
# ==============================================================
print("\n" + "="*85)
print("COMBINED ALL LEAGUES — Away CLV")
print("="*85)
combined = pd.concat(all_league_data.values(), ignore_index=True)
for edge in [0.0, 0.02, 0.05, 0.10, 0.15]:
    sub = combined[combined['B365A'] > combined['PSC_fair_A']*(1+edge)]
    bets = [(r['B365A'], r['FTR']=='A') for _,r in sub.iterrows()]
    n,p,roi,wr = simulate(bets)
    marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
    print(f"  >{edge*100:>4.0f}% edge  {n:>5} bets  P&L={p:>+9.2f}  ROI={roi:>+7.2f}%  WR={wr:.1f}%{marker}")