import pandas as pd
import numpy as np
import urllib.request
import os

seasons = ['1920','2021','2122','2223','2324','2425','2526']

# All leagues from fixtures.csv
league_names = {
    'E0': 'EPL', 'E1': 'Championship', 'E2': 'League One', 'E3': 'League Two', 'EC': 'Conference',
    'SP1': 'La Liga', 'SP2': 'Segunda',
    'I1': 'Serie A', 'I2': 'Serie B',
    'D1': 'Bundesliga', 'D2': '2.Bundesliga',
    'F1': 'Ligue 1', 'F2': 'Ligue 2',
    'N1': 'Eredivisie',
    'B1': 'Belgian Pro League',
    'P1': 'Primeira Liga',
    'T1': 'Super Lig',
    'G1': 'Greek SL',
    'SC0': 'Scottish Prem', 'SC1': 'Scottish Champ', 'SC2': 'Scottish L1', 'SC3': 'Scottish L2',
}

# Download all missing files
print("Downloading data...")
for code in league_names:
    for s in seasons:
        path = f'E:/soccer/league_data/{code}_{s}.csv'
        if not os.path.exists(path):
            url = f'https://www.football-data.co.uk/mmz4281/{s}/{code}.csv'
            try:
                urllib.request.urlretrieve(url, path)
                print(f"  Downloaded {code} {s}")
            except Exception as e:
                open(path, 'w').close()  # empty file as placeholder

# Load all data
print("\nLoading data...")
all_dfs = []
league_counts = {}

for code, name in league_names.items():
    for s in seasons:
        path = f'E:/soccer/league_data/{code}_{s}.csv'
        try:
            d = pd.read_csv(path, encoding='utf-8-sig')
            if len(d) < 10 or 'FTR' not in d.columns or 'B365A' not in d.columns:
                continue
            d = d.dropna(subset=['FTR','B365A','PSCH','PSCD','PSCA'])
            if len(d) < 10:
                continue
            d['Season'] = s
            d['LeagueCode'] = code
            d['LeagueName'] = name
            all_dfs.append(d)
            league_counts[name] = league_counts.get(name, 0) + len(d)
        except:
            pass

df = pd.concat(all_dfs, ignore_index=True)
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True, errors='coerce')
df = df.dropna(subset=['Date'])
df = df.sort_values('Date').reset_index(drop=True)

print(f"\nTotal matches loaded: {len(df)}")
print(f"Leagues with data:")
for name, count in sorted(league_counts.items(), key=lambda x: -x[1]):
    print(f"  {name:<20} {count:>5} matches")

# ---------------------------------------------------------------
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

# Pinnacle closing fair odds
tH,tD,tA = zip(*df.apply(lambda r: remove_margin(r['PSCH'],r['PSCD'],r['PSCA']), axis=1))
df = df.copy()
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)

df['edge_A'] = (df['B365A'] - df['PSC_fair_A']) / df['PSC_fair_A']
df['edge_H'] = (df['B365H'] - df['PSC_fair_H']) / df['PSC_fair_H']
df['edge_D'] = (df['B365D'] - df['PSC_fair_D']) / df['PSC_fair_D']

# ---------------------------------------------------------------
# PER-LEAGUE CLV results
# ---------------------------------------------------------------
print("\n" + "="*85)
print("PER-LEAGUE — Away CLV >5% (B365 vs Pinnacle closing)")
print("="*85)
print(f"{'League':<22} {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)

league_results = []
for name in sorted(league_names.values()):
    sub = df[(df['LeagueName']==name) & (df['edge_A'] > 0.05)]
    bets = [(r['B365A'], r['FTR']=='A') for _,r in sub.iterrows()]
    n,p,roi,wr = simulate(bets)
    if n >= 20:
        marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
        print(f"{name:<22} {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")
        league_results.append((name, n, p, roi, wr))

# ---------------------------------------------------------------
# COMBINED BEST LEAGUES (away CLV positive)
# ---------------------------------------------------------------
print("\n" + "="*85)
print("COMBINED ALL LEAGUES — Away CLV by edge threshold")
print("="*85)
for edge in [0.0, 0.02, 0.05, 0.10, 0.15]:
    sub = df[df['edge_A'] > 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}")

# ---------------------------------------------------------------
# HOME CLV per league
# ---------------------------------------------------------------
print("\n" + "="*85)
print("PER-LEAGUE — Home CLV >5%")
print("="*85)
print(f"{'League':<22} {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)
for name in sorted(league_names.values()):
    sub = df[(df['LeagueName']==name) & (df['edge_H'] > 0.05)]
    bets = [(r['B365H'], r['FTR']=='H') for _,r in sub.iterrows()]
    n,p,roi,wr = simulate(bets)
    if n >= 20:
        marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
        print(f"{name:<22} {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")

# ---------------------------------------------------------------
# DRAW CLV per league
# ---------------------------------------------------------------
print("\n" + "="*85)
print("PER-LEAGUE — Draw CLV >5%")
print("="*85)
print(f"{'League':<22} {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)
for name in sorted(league_names.values()):
    sub = df[(df['LeagueName']==name) & (df['edge_D'] > 0.05)]
    bets = [(r['B365D'], r['FTR']=='D') for _,r in sub.iterrows()]
    n,p,roi,wr = simulate(bets)
    if n >= 20:
        marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
        print(f"{name:<22} {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")

# ---------------------------------------------------------------
# BEST COMBINED STRATEGY: per-league best outcome
# ---------------------------------------------------------------
print("\n" + "="*85)
print("BEST OUTCOME PER LEAGUE (highest ROI at >5% edge, min 20 bets)")
print("="*85)
print(f"{'League':<22} {'Outcome':<8} {'N':>5}  {'P&L':>8}  {'ROI%':>7}  {'WR%':>6}")
print("-"*85)
best_bets = []
for name in sorted(league_names.values()):
    best_roi = -999
    best_row = None
    for outcome, edge_col, odds_col, result in [
        ('Away', 'edge_A', 'B365A', 'A'),
        ('Home', 'edge_H', 'B365H', 'H'),
        ('Draw', 'edge_D', 'B365D', 'D'),
    ]:
        sub = df[(df['LeagueName']==name) & (df[edge_col] > 0.05)]
        bets = [(r[odds_col], r['FTR']==result) for _,r in sub.iterrows()]
        n,p,roi,wr = simulate(bets)
        if n >= 20 and roi > best_roi:
            best_roi = roi
            best_row = (name, outcome, n, p, roi, wr, sub, odds_col, result)
    if best_row:
        name,outcome,n,p,roi,wr,sub,odds_col,result = best_row
        marker = " <<" if roi > 5 else (" +" if roi > 0 else "")
        print(f"{name:<22} {outcome:<8} {n:>5}  {p:>8.2f}  {roi:>7.2f}  {wr:>6.1f}{marker}")
        best_bets.append((name, outcome, sub, odds_col, result))

# ---------------------------------------------------------------
# KELLY COMPOUNDING on combined best-outcome strategy
# ---------------------------------------------------------------
print("\n" + "="*85)
print("KELLY COMPOUNDING — best outcome per league, >5% edge, 0.25x Kelly")
print("="*85)

# Build combined bet list with Kelly fractions
all_bet_rows = []
for name, outcome, sub, odds_col, result in best_bets:
    for _, r in sub.iterrows():
        tH2,tD2,tA2 = remove_margin(r['PSCH'],r['PSCD'],r['PSCA'])
        fair = {'H':1/tH2,'D':1/tD2,'A':1/tA2}[result]
        b = r[odds_col] - 1
        p = {'H':tH2,'D':tD2,'A':tA2}[result]
        edge = r[odds_col]*p - 1
        kelly_f = edge / b if b > 0 else 0
        if kelly_f > 0:
            all_bet_rows.append({
                'Date': r['Date'],
                'League': name,
                'HomeTeam': r['HomeTeam'],
                'AwayTeam': r['AwayTeam'],
                'Outcome': outcome,
                'Odds': r[odds_col],
                'Kelly': kelly_f,
                'Won': r['FTR'] == result,
            })

bets_df = pd.DataFrame(all_bet_rows).sort_values('Date').reset_index(drop=True)
bets_df['Week'] = bets_df['Date'].dt.to_period('W')

print(f"Total qualifying bets across all leagues: {len(bets_df)}")
print(f"Date range: {bets_df['Date'].min().date()} to {bets_df['Date'].max().date()}")

# Simulate
KELLY_MULT = 0.25
bankroll = 1000.0
history = []
for week, grp in bets_df.groupby('Week'):
    week_pl = 0
    wins = 0
    total_staked = 0
    for _, bet in grp.iterrows():
        f = min(bet['Kelly'] * KELLY_MULT, 0.25)
        stake = bankroll * f
        total_staked += stake
        if bet['Won']:
            week_pl += stake * (bet['Odds']-1)
            wins += 1
        else:
            week_pl -= stake
    bankroll += week_pl
    history.append({'Week':str(week),'N':len(grp),'Staked':round(total_staked,2),
                    'PL':round(week_pl,2),'Bankroll':round(bankroll,2),
                    'Wins':wins,'x':round(bankroll/1000,3)})
    if bankroll <= 0:
        break

hist = pd.DataFrame(history)
print(f"\n{'Week':<14} {'N':>4} {'Staked':>9} {'W/L':>5} {'P&L':>9} {'Bankroll':>10} {'x':>6}")
print("-"*70)
for _, r in hist.iterrows():
    wl = f"{int(r['Wins'])}/{int(r['N']-r['Wins'])}"
    print(f"{r['Week']:<14} {int(r['N']):>4} {r['Staked']:>9.2f} {wl:>5} {r['PL']:>+9.2f} {r['Bankroll']:>10.2f} {r['x']:>6.2f}x")

final = hist['Bankroll'].iloc[-1]
pk = hist['Bankroll'].cummax()
dd = ((pk - hist['Bankroll']) / pk * 100).max()
print(f"\nFinal: £{final:,.2f} ({final/1000:.1f}x) | Peak: £{pk.max():,.2f} | Max DD: {dd:.1f}%")

# Sensitivity
print("\n" + "="*60)
print("SENSITIVITY — Kelly fraction")
print("="*60)
for km in [0.10, 0.25, 0.50, 1.0]:
    br = 1000.0
    for week, grp in bets_df.groupby('Week'):
        wpl = 0
        for _, bet in grp.iterrows():
            f = min(bet['Kelly']*km, 0.25)
            stake = br * f
            wpl += stake*(bet['Odds']-1) if bet['Won'] else -stake
        br += wpl
        if br <= 0: break
    pk2 = 0
    br2 = 1000.0
    dd2 = 0
    for week, grp in bets_df.groupby('Week'):
        wpl = 0
        for _, bet in grp.iterrows():
            f = min(bet['Kelly']*km, 0.25)
            stake = br2*f
            wpl += stake*(bet['Odds']-1) if bet['Won'] else -stake
        br2 += wpl
        pk2 = max(pk2, br2)
        dd2 = max(dd2, (pk2-br2)/pk2*100 if pk2 > 0 else 0)
        if br2 <= 0: break
    print(f"  {km:.2f}x Kelly  Final=£{br2:>10,.2f}  {br2/1000:>6.1f}x  MaxDD={dd2:.1f}%")