import pandas as pd
import numpy as np

seasons = ['1920','2021','2122','2223','2324','2425','2526']
# Exclude Bundesliga (away CLV negative there)
leagues = {'E0':'EPL','E1':'Championship','F1':'Ligue 1','I1':'Serie A'}

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

dfs = []
for code, name in leagues.items():
    for s in seasons:
        try:
            d = pd.read_csv(f'E:/soccer/league_data/{code}_{s}.csv', encoding='utf-8-sig')
            d['Season'] = s
            d['League'] = name
            dfs.append(d)
        except: pass

df = pd.concat(dfs, ignore_index=True)
df = df.dropna(subset=['FTR','Date','B365A','PSCH','PSCD','PSCA'])
df = df.copy()

# Parse dates
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)
df = df.sort_values('Date').reset_index(drop=True)

# Pinnacle closing fair odds
tH,tD,tA = zip(*df.apply(lambda r: remove_margin(r['PSCH'],r['PSCD'],r['PSCA']), axis=1))
df['PSC_fair_A'] = 1/pd.Series(tA, index=df.index)

# Flag qualifying bets: B365 Away beats Pinnacle closing by >5%
df['edge'] = (df['B365A'] - df['PSC_fair_A']) / df['PSC_fair_A']
df['qualifies'] = df['edge'] > 0.05
df['won'] = df['FTR'] == 'A'

print(f"Total matches: {len(df)}")
print(f"Qualifying bets: {df['qualifies'].sum()}")
print(f"Date range: {df['Date'].min().date()} to {df['Date'].max().date()}")

# Assign week number (Mon-Sun)
df['Week'] = df['Date'].dt.to_period('W')

qualifying = df[df['qualifies']].copy()

# ==============================================================
# COMPOUNDING SIMULATION
# Parameters: stake 80% of bankroll per week, split equally across bets
# ==============================================================
def run_compound(data, stake_pct=0.80, starting_bankroll=1000.0, edge_threshold=0.05):
    bankroll = starting_bankroll
    history = []
    weeks = sorted(data['Week'].unique())

    for week in weeks:
        week_bets = data[data['Week'] == week]
        n = len(week_bets)
        if n == 0:
            continue

        week_stake = bankroll * stake_pct
        per_bet = week_stake / n

        week_pl = 0
        wins = 0
        for _, bet in week_bets.iterrows():
            if bet['won']:
                week_pl += per_bet * (bet['B365A'] - 1)
                wins += 1
            else:
                week_pl -= per_bet

        bankroll += week_pl
        history.append({
            'Week': str(week),
            'N_bets': n,
            'Per_bet': round(per_bet, 2),
            'Week_stake': round(week_stake, 2),
            'Week_PL': round(week_pl, 2),
            'Bankroll': round(bankroll, 2),
            'Wins': wins,
            'WR': round(wins/n*100, 1),
            'Bankroll_x': round(bankroll/starting_bankroll, 3),
        })

        if bankroll <= 0:
            print(f"  BUST at week {week}!")
            break

    return pd.DataFrame(history)

print("\n" + "="*90)
print("COMPOUNDING SIMULATION — Away CLV >5%, stake 80% per week, start £1000")
print("="*90)

hist = run_compound(qualifying, stake_pct=0.80, starting_bankroll=1000.0)

# Print each week
print(f"\n{'Week':<14} {'Bets':>4} {'Stake':>8} {'Per Bet':>8} {'W/L':>5} {'Week P&L':>10} {'Bankroll':>10} {'x':>6}")
print("-"*75)
for _, row in hist.iterrows():
    wl = f"{int(row['Wins'])}/{int(row['N_bets']-row['Wins'])}"
    pl_str = f"{row['Week_PL']:>+10.2f}"
    print(f"{row['Week']:<14} {int(row['N_bets']):>4} {row['Week_stake']:>8.2f} {row['Per_bet']:>8.2f} {wl:>5} {pl_str} {row['Bankroll']:>10.2f} {row['Bankroll_x']:>6.2f}x")

final = hist['Bankroll'].iloc[-1]
peak = hist['Bankroll'].max()
trough = hist['Bankroll'].min()
peak_week = hist.loc[hist['Bankroll'].idxmax(), 'Week']
max_dd_row = hist['Bankroll'].cummax() - hist['Bankroll']
max_dd = max_dd_row.max()
max_dd_pct = (max_dd / hist['Bankroll'].cummax()[max_dd_row.idxmax()]) * 100
total_weeks = len(hist)

print("\n" + "="*60)
print("SUMMARY")
print("="*60)
print(f"  Starting bankroll:   £1,000.00")
print(f"  Final bankroll:      £{final:,.2f}  ({final/10:.1f}x)")
print(f"  Peak bankroll:       £{peak:,.2f}  (week {peak_week})")
print(f"  Max drawdown:        £{max_dd:,.2f}  ({max_dd_pct:.1f}%)")
print(f"  Weeks with bets:     {total_weeks}")
print(f"  Overall win rate:    {qualifying['won'].mean()*100:.1f}%")

# ==============================================================
# SENSITIVITY: different stake percentages
# ==============================================================
print("\n" + "="*60)
print("SENSITIVITY — stake % vs final bankroll (start £1000)")
print("="*60)
print(f"{'Stake %':>10}  {'Final £':>12}  {'x':>6}  {'Max DD%':>8}  {'Went Bust':>10}")
for pct in [0.10, 0.20, 0.30, 0.40, 0.50, 0.60, 0.70, 0.80, 0.90, 1.00]:
    h = run_compound(qualifying, stake_pct=pct, starting_bankroll=1000.0)
    if h.empty:
        print(f"{pct*100:>9.0f}%  {'BUST':>12}")
        continue
    f = h['Bankroll'].iloc[-1]
    pk = h['Bankroll'].cummax()
    dd = ((pk - h['Bankroll']) / pk * 100).max()
    bust = f <= 0
    print(f"{pct*100:>9.0f}%  £{f:>11,.2f}  {f/1000:>6.1f}x  {dd:>7.1f}%  {'YES' if bust else 'no':>10}")

# ==============================================================
# PER-SEASON summary
# ==============================================================
print("\n" + "="*70)
print("PER-SEASON: start each season at £1000, stake 80%")
print("="*70)
print(f"{'Season':<10}  {'Bets':>5}  {'Final £':>10}  {'x':>6}  {'Max DD%':>8}")
for season in seasons:
    sub = qualifying[qualifying['Season'] == season]
    if sub.empty: continue
    h = run_compound(sub, stake_pct=0.80, starting_bankroll=1000.0)
    if h.empty: continue
    f = h['Bankroll'].iloc[-1]
    pk = h['Bankroll'].cummax()
    dd = ((pk - h['Bankroll']) / pk * 100).max()
    print(f"{season:<10}  {len(sub):>5}  £{f:>9,.2f}  {f/1000:>6.2f}x  {dd:>7.1f}%")