"""League Table Manager - Interactive Gradio Interface""" import os import re import logging from datetime import datetime, timedelta, timezone import pandas as pd import gradio as gr from supabase import create_client, Client # Configure logging logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s' ) logger = logging.getLogger(__name__) # Global matches storage (will be populated from database) # Teams will be derived from match data # IST timezone (UTC+5:30) IST = timezone(timedelta(hours=5, minutes=30)) # Supabase configuration SUPABASE_URL = os.environ.get("SUPABASE_URL", "https://ichhsthxaegexeogolzz.supabase.co") SUPABASE_KEY = os.environ.get("SUPABASE_KEY", "") # Initialize Supabase client supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY) # Global matches storage (in-memory cache) matches = [] def load_matches(): """Load matches from Supabase database.""" global matches matches = [] logger.info("=" * 60) logger.info("LOADING MATCHES FROM SUPABASE") logger.info("=" * 60) try: logger.info(f"→ Connecting to Supabase: {SUPABASE_URL}") response = supabase.table("matches").select("*").order("datetime", desc=True).execute() if response.data: for record in response.data: matches.append([ str(record["id"]), record["home"], record["away"], record["home_goals"], record["away_goals"], record["datetime"] ]) logger.info(f"✓ Successfully loaded {len(matches)} matches from Supabase") else: logger.info("✓ No matches found in database") logger.info("=" * 60) except Exception as e: logger.error(f"✗ Error accessing Supabase: {e}") return matches def get_teams_from_matches(): """Extract unique team names from matches data.""" teams = set() for match in matches: teams.add(match[1]) # home team teams.add(match[2]) # away team return sorted(list(teams)) if teams else [] def calculate_table(matches_list): """Calculate league table from matches list.""" # Get all unique teams from matches all_teams = set() for match in matches_list: all_teams.add(match[1]) # home team (skip ID at index 0) all_teams.add(match[2]) # away team # Initialize stats for all teams # Added #WW (White Washes) and #5GM (5 Goal Matches) table = {t: {"P": 0, "W": 0, "D": 0, "L": 0, "GF": 0, "GA": 0, "Pts": 0, "GPM": 0.0, "GAM": 0.0, "GDM": 0.0, "WP": 0.0, "#WW": 0, "#5GM": 0} for t in all_teams} # Process each match for match in matches_list: match_id, h, a, gh, ga = match[0], match[1], match[2], match[3], match[4] table[h]["P"] += 1 table[a]["P"] += 1 table[h]["GF"] += gh table[h]["GA"] += ga table[a]["GF"] += ga table[a]["GA"] += gh # Track #5GM (5 Goal Matches) - matches where player scores 5+ if gh >= 5: table[h]["#5GM"] += 1 if ga >= 5: table[a]["#5GM"] += 1 if gh > ga: table[h]["W"] += 1 table[a]["L"] += 1 table[h]["Pts"] += 3 # Track White Wash - wins where opponent scores 0 if ga == 0: table[h]["#WW"] += 1 elif gh < ga: table[a]["W"] += 1 table[h]["L"] += 1 table[a]["Pts"] += 3 # Track White Wash - wins where opponent scores 0 if gh == 0: table[a]["#WW"] += 1 else: table[h]["D"] += 1 table[a]["D"] += 1 table[h]["Pts"] += 1 table[a]["Pts"] += 1 # Calculate derived stats for t in all_teams: if table[t]["P"] > 0: table[t]["GPM"] = round(table[t]["GF"] / table[t]["P"], 2) table[t]["GAM"] = round(table[t]["GA"] / table[t]["P"], 2) table[t]["GDM"] = round((table[t]["GF"] - table[t]["GA"]) / table[t]["P"], 2) table[t]["WP"] = round((table[t]["W"] / table[t]["P"]) * 100, 2) # Create DataFrame df = pd.DataFrame.from_dict(table, orient="index") df["GD"] = df["GF"] - df["GA"] df.reset_index(inplace=True) df.rename(columns={"index": "Team"}, inplace=True) # Sort by WP descending (as per requirements) # Added #WW and #5GM columns df = df[["Team", "WP", "GPM", "GAM", "GDM", "P", "W", "D", "L", "GF", "GA", "GD", "Pts", "#WW", "#5GM"]] df = df.sort_values(by=["WP"], ascending=False) return df def calculate_league_stats(matches_list): """Calculate league-level statistics from matches list.""" if not matches_list: return pd.DataFrame({ "Stat": ["Highest Aggregate Goals", "Biggest Goal Margin", "Most Goals by Any Side"], "Value": ["-", "-", "-"], "Match": ["-", "-", "-"] }) highest_aggregate = 0 highest_aggregate_match = None biggest_margin = 0 biggest_margin_match = None most_goals_one_side = 0 most_goals_one_side_match = None most_goals_one_side_team = None for match in matches_list: match_id, h, a, gh, ga, dt = match[0], match[1], match[2], match[3], match[4], match[5] # Calculate aggregate goals aggregate = gh + ga if aggregate > highest_aggregate: highest_aggregate = aggregate highest_aggregate_match = match # Calculate goal margin margin = abs(gh - ga) if margin > biggest_margin: biggest_margin = margin biggest_margin_match = match # Track most goals by any side if gh > most_goals_one_side: most_goals_one_side = gh most_goals_one_side_match = match most_goals_one_side_team = h if ga > most_goals_one_side: most_goals_one_side = ga most_goals_one_side_match = match most_goals_one_side_team = a # Format match info def format_match(m): if m is None: return "-" return f"{m[1]} {m[3]} - {m[4]} {m[2]}" def format_match_with_team(m, team): if m is None: return "-" return f"{team} ({m[1]} {m[3]} - {m[4]} {m[2]})" stats_df = pd.DataFrame({ "Stat": ["Highest Aggregate Goals", "Biggest Goal Margin", "Most Goals by Any Side"], "Value": [highest_aggregate, biggest_margin, most_goals_one_side], "Match": [format_match(highest_aggregate_match), format_match(biggest_margin_match), format_match_with_team(most_goals_one_side_match, most_goals_one_side_team)] }) return stats_df def get_head_to_head_data(team1, team2, matches_list): """Get both head-to-head stats and matches between two teams in one pass.""" empty_stats = pd.DataFrame() empty_matches = pd.DataFrame(columns=["#", "Timestamp", "Home", "Away", "Home Goals", "Away Goals"]) if not team1 or not team2 or team1 == team2: return empty_stats, empty_matches # Filter matches between these two teams (single pass) h2h_matches = [] stats = { team1: {"P": 0, "W": 0, "D": 0, "L": 0, "GF": 0, "GA": 0, "WP": 0.0}, team2: {"P": 0, "W": 0, "D": 0, "L": 0, "GF": 0, "GA": 0, "WP": 0.0} } for match in matches_list: h, a, gh, ga = match[1], match[2], match[3], match[4] if (h == team1 and a == team2) or (h == team2 and a == team1): h2h_matches.append(match) # Update stats stats[h]["P"] += 1 stats[h]["GF"] += gh stats[h]["GA"] += ga stats[a]["P"] += 1 stats[a]["GF"] += ga stats[a]["GA"] += gh if gh > ga: stats[h]["W"] += 1 stats[a]["L"] += 1 elif gh < ga: stats[a]["W"] += 1 stats[h]["L"] += 1 else: stats[h]["D"] += 1 stats[a]["D"] += 1 # Calculate win percentages for team in [team1, team2]: if stats[team]["P"] > 0: stats[team]["WP"] = round((stats[team]["W"] / stats[team]["P"]) * 100, 2) # Create stats DataFrame stats_df = pd.DataFrame({ "Stat": ["P", "W", "D", "L", "Win %", "GF", "GA"], team1: [ stats[team1]["P"], stats[team1]["W"], stats[team1]["D"], stats[team1]["L"], f"{stats[team1]['WP']}%", stats[team1]["GF"], stats[team1]["GA"] ], team2: [ stats[team2]["P"], stats[team2]["W"], stats[team2]["D"], stats[team2]["L"], f"{stats[team2]['WP']}%", stats[team2]["GF"], stats[team2]["GA"] ] }) # Create matches DataFrame (reuse existing function) matches_df = get_matches_dataframe(h2h_matches) return stats_df, matches_df def calculate_head_to_head(team1, team2, matches_list): """Calculate head-to-head stats between two teams.""" stats_df, _ = get_head_to_head_data(team1, team2, matches_list) return stats_df def get_head_to_head_matches(team1, team2, matches_list): """Get all matches between two teams as a DataFrame.""" _, matches_df = get_head_to_head_data(team1, team2, matches_list) return matches_df def get_matches_dataframe(matches_list): """Convert matches list to DataFrame for display.""" if not matches_list: return pd.DataFrame(columns=["#", "Timestamp", "Home", "Away", "Home Goals", "Away Goals"]) # Sort matches by datetime (most recent first) sorted_matches = sorted(matches_list, key=lambda x: x[5], reverse=True) data = [] for i, match in enumerate(sorted_matches, 1): match_id, h, a, gh, ga, dt = match # Format datetime as DD-MM-YY HH:MM AM/PM IST # Handle ISO format strings with varying microsecond precision try: dt_obj = datetime.fromisoformat(dt) except ValueError: # Fallback: handle timestamps with non-standard microsecond precision # by normalizing microseconds to 6 digits dt_normalized = re.sub(r'\.(\d+)', lambda m: '.' + m.group(1).ljust(6, '0')[:6], dt) dt_obj = datetime.fromisoformat(dt_normalized) # Convert to IST timezone # If datetime is naive (no timezone), assume it's UTC and convert to IST # If datetime has timezone info, convert it to IST if dt_obj.tzinfo is None: # Naive datetime - assume UTC and convert to IST dt_obj = dt_obj.replace(tzinfo=timezone.utc).astimezone(IST) else: # Aware datetime - convert to IST dt_obj = dt_obj.astimezone(IST) formatted_dt = dt_obj.strftime("%d-%m-%y %I:%M %p IST") data.append({ "#": i, "Timestamp": formatted_dt, "Home": h, "Away": a, "Home Goals": gh, "Away Goals": ga }) return pd.DataFrame(data) def add_match(home, away, home_goals, away_goals): """Add a new match and update tables.""" # Validation if not home or not away or not home.strip() or not away.strip(): return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Team names cannot be empty!" ) # Clean up team names home = home.strip() away = away.strip() if home == away: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Home and away teams must be different!" ) if home_goals < 0 or away_goals < 0: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Goals must be non-negative!" ) logger.info(f"→ Adding match: {home} {int(home_goals)} - {int(away_goals)} {away}") # Insert into Supabase try: match_datetime = datetime.now(IST).isoformat() response = supabase.table("matches").insert({ "home": home, "away": away, "home_goals": int(home_goals), "away_goals": int(away_goals), "datetime": match_datetime }).execute() if response.data: record = response.data[0] match_id = str(record["id"]) match_data = [match_id, home, away, int(home_goals), int(away_goals), record["datetime"]] matches.append(match_data) logger.info(f" ✓ Match ID: {match_id}") logger.info(f" ✓ Successfully saved to Supabase") status = f"Match added: {home} {int(home_goals)} - {int(away_goals)} {away}" else: logger.error(" ✗ No data returned from insert") status = f"Match added locally but Supabase returned no data" except Exception as e: logger.error(f" ✗ Error saving to Supabase: {e}") status = f"Error: Failed to save match - {e}" # Return updated tables and status league_table = calculate_table(matches) matches_table = get_matches_dataframe(matches) return league_table, matches_table, status def delete_match(row_number): """Delete a match from history by row number.""" if row_number is None or row_number < 1: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Please enter a valid row number!" ) # Sort matches by datetime (most recent first) to match displayed order sorted_matches = sorted(matches, key=lambda x: x[5], reverse=True) row_idx = int(row_number) - 1 if row_idx >= len(sorted_matches) or row_idx < 0: return ( calculate_table(matches), get_matches_dataframe(matches), f"Error: Row {int(row_number)} does not exist! Valid rows: 1-{len(sorted_matches)}" ) # Get match details sorted_match = sorted_matches[row_idx] match_id, h, a, gh, ga = sorted_match[0], sorted_match[1], sorted_match[2], sorted_match[3], sorted_match[4] logger.info(f"→ Deleting match row #{int(row_number)}: {h} {gh} - {ga} {a}") logger.info(f" Match ID: {match_id}") # Delete from Supabase try: response = supabase.table("matches").delete().eq("id", match_id).execute() logger.info(f" ✓ Successfully deleted from Supabase") # Remove from in-memory list for i, match in enumerate(matches): if match[0] == match_id: matches.pop(i) break logger.info(f" ✓ Match removed from in-memory storage") status = f"Deleted row {int(row_number)}: {h} vs {a} ({gh}-{ga})" except Exception as e: logger.error(f" ✗ Error deleting from Supabase: {e}") status = f"Error: Failed to delete match - {e}" # Return updated tables and status league_table = calculate_table(matches) matches_table = get_matches_dataframe(matches) return league_table, matches_table, status def update_match(row_number, new_home, new_away, new_home_goals, new_away_goals): """Update an existing match by row number.""" if row_number is None or row_number < 1: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Please enter a valid row number!" ) # Sort matches by datetime (most recent first) to match displayed order sorted_matches = sorted(matches, key=lambda x: x[5], reverse=True) row_idx = int(row_number) - 1 if row_idx >= len(sorted_matches) or row_idx < 0: return ( calculate_table(matches), get_matches_dataframe(matches), f"Error: Row {int(row_number)} does not exist! Valid rows: 1-{len(sorted_matches)}" ) # Validation if not new_home or not new_away or not new_home.strip() or not new_away.strip(): return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Team names cannot be empty!" ) # Clean up team names new_home = new_home.strip() new_away = new_away.strip() if new_home == new_away: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Home and away teams must be different!" ) if new_home_goals < 0 or new_away_goals < 0: return ( calculate_table(matches), get_matches_dataframe(matches), "Error: Goals must be non-negative!" ) # Get the match to update from sorted list sorted_match = sorted_matches[row_idx] match_id = sorted_match[0] old_home, old_away, old_home_goals, old_away_goals = sorted_match[1], sorted_match[2], sorted_match[3], sorted_match[4] logger.info(f"→ Updating match row #{int(row_number)}") logger.info(f" Match ID: {match_id}") logger.info(f" Old: {old_home} {old_home_goals} - {old_away_goals} {old_away}") logger.info(f" New: {new_home} {int(new_home_goals)} - {int(new_away_goals)} {new_away}") # Update in Supabase try: update_datetime = datetime.now(IST).isoformat() response = supabase.table("matches").update({ "home": new_home, "away": new_away, "home_goals": int(new_home_goals), "away_goals": int(new_away_goals), "updated_at": update_datetime }).eq("id", match_id).execute() if response.data: # Update in-memory cache for i, match in enumerate(matches): if match[0] == match_id: matches[i][1] = new_home matches[i][2] = new_away matches[i][3] = int(new_home_goals) matches[i][4] = int(new_away_goals) break logger.info(f" ✓ Successfully updated in Supabase") status = f"Updated row {int(row_number)}: {new_home} {int(new_home_goals)} - {int(new_away_goals)} {new_away}" else: logger.error(" ✗ No data returned from update") status = f"Error: Update returned no data" except Exception as e: logger.error(f" ✗ Error updating in Supabase: {e}") status = f"Error: Failed to update match - {e}" # Return updated tables and status league_table = calculate_table(matches) matches_table = get_matches_dataframe(matches) return league_table, matches_table, status def build_interface(): """Build and return the Gradio interface.""" # Load initial data from Supabase load_matches() # Get initial teams from loaded data initial_teams = get_teams_from_matches() def refresh_data(): """Reload matches from Supabase and return updated tables.""" load_matches() teams = get_teams_from_matches() if len(teams) >= 2: h2h_table, h2h_matches_table = get_head_to_head_data(teams[0], teams[1], matches) else: h2h_table = pd.DataFrame() h2h_matches_table = pd.DataFrame(columns=["#", "Timestamp", "Home", "Away", "Home Goals", "Away Goals"]) return ( calculate_table(matches), get_matches_dataframe(matches), h2h_table, h2h_matches_table ) with gr.Blocks(title="League Table Manager") as demo: gr.Markdown("# League Table Manager") with gr.Tabs(): with gr.Tab("League Manager"): with gr.Row(): # Left Column - Input Form with gr.Column(scale=1): home_team = gr.Dropdown( choices=initial_teams, label="Home Team", value=initial_teams[0] if initial_teams else None, allow_custom_value=True ) away_team = gr.Dropdown( choices=initial_teams, label="Away Team", value=initial_teams[1] if len(initial_teams) > 1 else None, allow_custom_value=True ) with gr.Row(): home_goals = gr.Number( label="Home Goals", value=0, minimum=0, precision=0 ) away_goals = gr.Number( label="Away Goals", value=0, minimum=0, precision=0 ) submit_btn = gr.Button("Add Match", variant="primary") status_msg = gr.Textbox(label="Status", interactive=False) # Right Column - Tables with gr.Column(scale=2): league_table = gr.Dataframe( label="League Table", value=calculate_table(matches), interactive=False, wrap=True ) matches_table = gr.Dataframe( label="Match History", value=get_matches_dataframe(matches), interactive=False, wrap=True ) with gr.Row(): delete_row_input = gr.Number( label="Row # to Delete", value=None, minimum=1, precision=0, scale=2 ) delete_btn = gr.Button("Delete Row", variant="stop", scale=1) gr.Markdown("---") gr.Markdown("### Update Match") with gr.Row(): update_row_input = gr.Number( label="Row # to Update", value=None, minimum=1, precision=0 ) with gr.Row(): update_home_team = gr.Dropdown( choices=initial_teams, label="New Home Team", allow_custom_value=True ) update_away_team = gr.Dropdown( choices=initial_teams, label="New Away Team", allow_custom_value=True ) with gr.Row(): update_home_goals = gr.Number( label="New Home Goals", value=0, minimum=0, precision=0 ) update_away_goals = gr.Number( label="New Away Goals", value=0, minimum=0, precision=0 ) update_btn = gr.Button("Update Match", variant="secondary") # Event Handlers submit_btn.click( fn=add_match, inputs=[home_team, away_team, home_goals, away_goals], outputs=[league_table, matches_table, status_msg] ) delete_btn.click( fn=delete_match, inputs=[delete_row_input], outputs=[league_table, matches_table, status_msg] ) update_btn.click( fn=update_match, inputs=[update_row_input, update_home_team, update_away_team, update_home_goals, update_away_goals], outputs=[league_table, matches_table, status_msg] ) with gr.Tab("Team vs Team Stats"): gr.Markdown("### Head-to-Head Statistics") with gr.Row(): h2h_team1 = gr.Dropdown( choices=initial_teams, label="Team 1", value=initial_teams[0] if initial_teams else None, allow_custom_value=True ) h2h_team2 = gr.Dropdown( choices=initial_teams, label="Team 2", value=initial_teams[1] if len(initial_teams) > 1 else None, allow_custom_value=True ) # Pre-compute initial h2h data (single pass) if len(initial_teams) >= 2: initial_h2h_stats, initial_h2h_matches = get_head_to_head_data(initial_teams[0], initial_teams[1], matches) else: initial_h2h_stats = pd.DataFrame() initial_h2h_matches = pd.DataFrame(columns=["#", "Timestamp", "Home", "Away", "Home Goals", "Away Goals"]) h2h_stats = gr.Dataframe( label="Head-to-Head Stats", value=initial_h2h_stats, interactive=False, wrap=True ) gr.Markdown("### Match History") h2h_matches = gr.Dataframe( label="Matches Between Teams", value=initial_h2h_matches, interactive=False, wrap=True ) # Event handler for team selection (single pass for efficiency) def update_h2h_tables(t1, t2): return get_head_to_head_data(t1, t2, matches) h2h_team1.change( fn=update_h2h_tables, inputs=[h2h_team1, h2h_team2], outputs=[h2h_stats, h2h_matches] ) h2h_team2.change( fn=update_h2h_tables, inputs=[h2h_team1, h2h_team2], outputs=[h2h_stats, h2h_matches] ) with gr.Tab("League Stats"): gr.Markdown("### League Statistics") gr.Markdown("Overall league-level records and statistics.") league_stats_table = gr.Dataframe( label="League Records", value=calculate_league_stats(matches), interactive=False, wrap=True ) # Load fresh data when the page loads/refreshes demo.load( fn=refresh_data, inputs=[], outputs=[league_table, matches_table, h2h_stats, h2h_matches] ) return demo if __name__ == "__main__": demo = build_interface() demo.launch()