start er up

1 files changed, 956 insertions, 0 deletions

diff --git a/app.py b/app.py
new file mode 100644
index 0000000..826410b
--- /dev/null
+++ b/app.py
@@ -0,0 +1,956 @@
+import os
+import json
+import itertools
+from flask import Flask, render_template, request, redirect, url_for, flash
+from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
+from werkzeug.security import generate_password_hash, check_password_hash
+from werkzeug.utils import secure_filename
+import sqlite3
+from datetime import datetime, timedelta
+
+app = Flask(__name__)
+app.secret_key = 'super_secret_orchard_key'
+app.config['UPLOAD_FOLDER'] = 'static/uploads'
+
+os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
+
+login_manager = LoginManager()
+login_manager.init_app(app)
+login_manager.login_view = 'login'
+
+DB_FILE = 'orchard_league.db'
+
+def get_db():
+    conn = sqlite3.connect(DB_FILE)
+    conn.row_factory = sqlite3.Row
+    return conn
+
+class User(UserMixin):
+    def __init__(self, id, username, is_admin, team_name, team_icon):
+        self.id = str(id)
+        self.username = username
+        self.is_admin = bool(is_admin)
+        self.team_name = team_name
+        self.team_icon = team_icon
+
+@login_manager.user_loader
+def load_user(user_id):
+    conn = get_db()
+    c = conn.cursor()
+    c.execute('SELECT * FROM Users WHERE id = ?', (user_id,))
+    user_row = c.fetchone()
+    conn.close()
+    if user_row:
+        return User(user_row['id'], user_row['username'], user_row['is_admin'], user_row['team_name'], user_row['team_icon'])
+    return None
+
+@app.route('/')
+def index():
+    season_id = request.args.get('season_id')
+    team_id = request.args.get('team_id')
+    tab = request.args.get('tab', 'overview')
+    
+    conn = get_db()
+    c = conn.cursor()
+    
+    # Get all seasons for the dropdown
+    c.execute('SELECT id, name FROM Seasons ORDER BY id DESC')
+    seasons = c.fetchall()
+    
+    if not seasons:
+        conn.close()
+        return render_template('season.html', seasons=[], selected_season_id=None)
+        
+    if not season_id:
+        season_id = seasons[0]['id']
+        
+    # Season Overview Data
+    c.execute('SELECT * FROM Seasons WHERE id = ?', (season_id,))
+    season_info = dict(c.fetchone())
+    
+    # Season Runtime (Start/End dates)
+    c.execute('SELECT MIN(scheduled_date), MAX(scheduled_date) FROM Games WHERE season_id = ?', (season_id,))
+    runtime = c.fetchone()
+    season_info['start_date'] = runtime[0] if runtime[0] else "N/A"
+    season_info['end_date'] = runtime[1] if runtime[1] else "N/A"
+    
+    # Final Placement (if all games are Final and games exist)
+    c.execute("SELECT COUNT(*) as c FROM Games WHERE season_id = ?", (season_id,))
+    has_games = c.fetchone()['c'] > 0
+    c.execute("SELECT COUNT(*) as c FROM Games WHERE season_id = ? AND status != 'Final'", (season_id,))
+    all_final = c.fetchone()['c'] == 0
+    is_finished = has_games and all_final
+    season_info['is_finished'] = is_finished
+    
+    # Join Status for current user
+    user_season_status = None
+    if current_user.is_authenticated:
+        c.execute('SELECT status FROM SeasonTeams WHERE season_id = ? AND user_id = ?', (season_id, current_user.id))
+        status_row = c.fetchone()
+        if status_row:
+            user_season_status = status_row['status']
+            
+    # Standings Data (Regular Season only)
+    c.execute('''
+        SELECT 
+            u.id as user_id, u.team_name, u.team_icon,
+            SUM(CASE WHEN (g.home_team_id = u.id AND g.home_score > g.away_score) OR (g.away_team_id = u.id AND g.away_score > g.home_score) THEN 1 ELSE 0 END) as wins,
+            SUM(CASE WHEN (g.home_team_id = u.id AND g.home_score < g.away_score) OR (g.away_team_id = u.id AND g.away_score < g.home_score) THEN 1 ELSE 0 END) as losses,
+            SUM(CASE WHEN g.home_team_id = u.id THEN g.home_score WHEN g.away_team_id = u.id THEN g.away_score ELSE 0 END) as runs_for,
+            SUM(CASE WHEN g.home_team_id = u.id THEN g.away_score WHEN g.away_team_id = u.id THEN g.home_score ELSE 0 END) as runs_against,
+            (SELECT MIN(scheduled_date) FROM Games WHERE (home_team_id = u.id OR away_team_id = u.id) AND season_id = st.season_id) as first_game
+        FROM SeasonTeams st
+        JOIN Users u ON st.user_id = u.id
+        LEFT JOIN Games g ON (g.home_team_id = u.id OR g.away_team_id = u.id) AND g.season_id = st.season_id AND g.status = 'Final' AND g.is_playoff = 0
+        WHERE st.season_id = ? AND st.status = 'Approved'
+        GROUP BY u.id, u.team_name, u.team_icon
+        ORDER BY wins DESC, runs_for DESC, runs_against ASC, first_game ASC
+    ''', (season_id,))
+    standings_raw = c.fetchall()
+    
+    # Clinch Calculation
+    standings = []
+    if standings_raw:
+        top_wins = standings_raw[0]['wins']
+        top_losses = standings_raw[0]['losses']
+        p_count = season_info['playoff_teams'] or 0
+        total_sched = season_info['total_games'] or 0
+        
+        # Get remaining games for each team
+        c.execute('''
+            SELECT u.id, COUNT(g.id) as remaining
+            FROM SeasonTeams st
+            JOIN Users u ON st.user_id = u.id
+            LEFT JOIN Games g ON (g.home_team_id = u.id OR g.away_team_id = u.id) 
+                AND g.season_id = st.season_id AND g.status = 'Scheduled' AND g.is_playoff = 0
+            WHERE st.season_id = ?
+            GROUP BY u.id
+        ''', (season_id,))
+        remaining_map = {row['id']: row['remaining'] for row in c.fetchall()}
+        
+        team_stats = []
+        for row in standings_raw:
+            d = dict(row)
+            d['max_wins'] = d['wins'] + remaining_map.get(d['user_id'], 0)
+            team_stats.append(d)
+            
+        # Clinch Playoff Marker
+        # A team clinches if their wins > max possible wins of the team currently in the first 'out' spot
+        clinch_out_threshold = float('inf')
+        if p_count > 0 and len(team_stats) > p_count:
+            clinch_out_threshold = team_stats[p_count]['max_wins']
+            
+        # Clinch 1st Seed Marker
+        clinch_1st_threshold = float('inf')
+        if p_count > 0 and len(team_stats) > 1:
+            clinch_1st_threshold = team_stats[1]['max_wins']
+
+        for i, d in enumerate(team_stats):
+            games_back = ((top_wins - d['wins']) + (d['losses'] - top_losses)) / 2.0
+            d['games_back'] = "-" if games_back == 0 else str(games_back)
+            
+            d['clinch'] = ""
+            if d['wins'] > clinch_out_threshold:
+                d['clinch'] = "*"
+            if i == 0 and d['wins'] > clinch_1st_threshold:
+                d['clinch'] = "**"
+            standings.append(d)
+
+    # Schedule Data
+    c.execute('SELECT id, team_name FROM Users WHERE is_admin = 0')
+    all_teams = c.fetchall()
+    
+    if not team_id and current_user.is_authenticated and not current_user.is_admin:
+        team_id = str(current_user.id)
+    elif not team_id:
+        team_id = 'all'
+        
+    query = '''
+        SELECT g.*, 
+               a.team_name as away_team, a.team_icon as away_icon,
+               h.team_name as home_team, h.team_icon as home_icon,
+               (SELECT COUNT(*) FROM GameSubmissions gs 
+                WHERE g.status != 'Final' AND gs.game_id = g.id 
+                AND (
+                    (gs.proposed_date IS NOT NULL AND gs.proposed_date != g.scheduled_date) OR 
+                    (gs.away_score IS NOT NULL AND (g.away_score IS NULL OR gs.away_score != g.away_score)) OR
+                    (gs.home_score IS NOT NULL AND (g.home_score IS NULL OR gs.home_score != g.home_score))
+                )
+               ) as pending_proposals
+        FROM Games g
+        LEFT JOIN Users a ON g.away_team_id = a.id
+        LEFT JOIN Users h ON g.home_team_id = h.id
+        WHERE g.season_id = ?
+    '''
+    params = [season_id]
+    
+    # We fetch ALL games first, then filter regular season games in Python if needed,
+    # OR we modify the query to include ALL playoff games but only filtered regular games.
+    if team_id and team_id != 'all':
+        query += ' AND (g.is_playoff = 1 OR g.away_team_id = ? OR g.home_team_id = ?)'
+        params.extend([team_id, team_id])
+        
+    query += ' ORDER BY g.scheduled_date ASC'
+    c.execute(query, params)
+    games_list = c.fetchall()
+    
+    # Get team records for bracket display
+    team_records = {}
+    for row in standings:
+        team_records[row['user_id']] = f"({row['wins']}-{row['losses']})"
+
+    # Group games by date for calendar
+    games_by_date = {}
+    playoff_games = []
+    for g_row in games_list:
+        g = dict(g_row)
+        if g['is_playoff']:
+            g['away_record'] = team_records.get(g['away_team_id'], "")
+            g['home_record'] = team_records.get(g['home_team_id'], "")
+            playoff_games.append(g)
+            
+        dt = g['scheduled_date']
+        if dt not in games_by_date:
+            games_by_date[dt] = []
+        games_by_date[dt].append(g)
+
+    # Bracket Logic (using series_id)
+    bracket_series = {}
+    for pg in playoff_games:
+        sid = pg['series_id']
+        if sid not in bracket_series:
+            bracket_series[sid] = pg.copy()
+            c.execute('''
+                SELECT 
+                    SUM(CASE WHEN away_score > home_score THEN 1 ELSE 0 END) as aw,
+                    SUM(CASE WHEN home_score > away_score THEN 1 ELSE 0 END) as hw
+                FROM Games WHERE season_id = ? AND series_id = ? AND status = 'Final'
+            ''', (season_id, sid))
+            s_res = c.fetchone()
+            bracket_series[sid]['away_series_wins'] = s_res['aw'] or 0
+            bracket_series[sid]['home_series_wins'] = s_res['hw'] or 0
+    
+    # Group series into rounds
+    bracket_rounds = {}
+    for sid, s_data in bracket_series.items():
+        rnd = s_data['playoff_round']
+        if rnd not in bracket_rounds:
+            bracket_rounds[rnd] = []
+        bracket_rounds[rnd].append(s_data)
+    
+    sorted_rounds = sorted(bracket_rounds.items())
+    bracket = [r[1] for r in sorted_rounds]
+
+    # Calendar generation logic
+    import calendar
+    today = datetime.now().strftime('%Y-%m-%d')
+    
+    # Determine range of months to show
+    if games_list:
+        dates = [datetime.strptime(g['scheduled_date'], '%Y-%m-%d') for g in games_list if g['scheduled_date'] and g['scheduled_date'] != 'TBD']
+        if dates:
+            min_date = min(dates)
+            max_date = max(dates)
+        else:
+            min_date = datetime.now()
+            max_date = datetime.now()
+    else:
+        min_date = datetime.now()
+        max_date = datetime.now()
+        
+    months = []
+    curr = datetime(min_date.year, min_date.month, 1)
+    while curr <= datetime(max_date.year, max_date.month, 1):
+        cal = calendar.Calendar(firstweekday=6) # Sunday start
+        weeks = cal.monthdayscalendar(curr.year, curr.month)
+        months.append({
+            'name': curr.strftime('%B %Y'),
+            'year': curr.year,
+            'month': curr.month,
+            'weeks': weeks
+        })
+        if curr.month == 12:
+            curr = datetime(curr.year + 1, 1, 1)
+        else:
+            curr = datetime(curr.year, curr.month + 1, 1)
+
+    conn.close()
+    return render_template('season.html', 
+                           seasons=seasons, 
+                           selected_season_id=season_id,
+                           season_info=season_info,
+                           standings=standings,
+                           games_by_date=games_by_date,
+                           playoff_games=playoff_games,
+                           bracket=bracket,
+                           months=months,
+                           today=today,
+                           all_teams=all_teams,
+                           selected_team_id=team_id,
+                           active_tab=tab,
+                           user_season_status=user_season_status)
+
+def run_consensus_check(game_id, c):
+    # Consensus Check: Find most recent from each team
+    c.execute('SELECT away_team_id, home_team_id FROM Games WHERE id = ?', (game_id,))
+    g_teams = c.fetchone()
+    if not g_teams: return
+    
+    c.execute('''
+        SELECT * FROM GameSubmissions 
+        WHERE game_id = ? AND submitted_by_id = ? 
+        ORDER BY id DESC LIMIT 1
+    ''', (game_id, g_teams['away_team_id']))
+    sub_away = c.fetchone()
+    
+    c.execute('''
+        SELECT * FROM GameSubmissions 
+        WHERE game_id = ? AND submitted_by_id = ? 
+        ORDER BY id DESC LIMIT 1
+    ''', (game_id, g_teams['home_team_id']))
+    sub_home = c.fetchone()
+    
+    if sub_away and sub_home:
+        # Check score consensus
+        if sub_away['away_score'] is not None and sub_home['away_score'] is not None and \
+           sub_away['home_score'] is not None and sub_home['home_score'] is not None and \
+           sub_away['away_score'] == sub_home['away_score'] and \
+           sub_away['home_score'] == sub_home['home_score']:
+           
+            # Consensus reached on score
+            box_score_final = None
+            if sub_away['box_score_json'] and sub_home['box_score_json'] and sub_away['box_score_json'] == sub_home['box_score_json']:
+                bs = json.loads(sub_away['box_score_json'])
+                # bs["away"][10] is the 'runs' column (index 10)
+                if bs["away"][10] == sub_away['away_score'] and bs["home"][10] == sub_away['home_score']:
+                    box_score_final = sub_away['box_score_json']
+            
+            c.execute('''
+                UPDATE Games 
+                SET away_score = ?, home_score = ?, box_score_json = ?, status = 'Final'
+                WHERE id = ?
+            ''', (sub_away['away_score'], sub_away['home_score'], box_score_final, game_id))
+            
+            c.execute('DELETE FROM GameSubmissions WHERE game_id = ?', (game_id,))
+            
+            check_and_trigger_playoff_updates(game_id, c)
+            flash('Scores agreed! Game finalized.')
+            
+        # Check date consensus
+        if sub_away['proposed_date'] and sub_home['proposed_date'] and sub_away['proposed_date'] == sub_home['proposed_date']:
+            c.execute('UPDATE Games SET scheduled_date = ? WHERE id = ?', (sub_away['proposed_date'], game_id))
+            c.execute('DELETE FROM GameSubmissions WHERE game_id = ?', (game_id,))
+            flash('Date change agreed! Game rescheduled.')
+
+@app.route('/game/<int:game_id>/retract', methods=['POST'])
+@login_required
+def retract_submission(game_id):
+    conn = get_db()
+    c = conn.cursor()
+    c.execute('DELETE FROM GameSubmissions WHERE game_id = ? AND submitted_by_id = ?', (game_id, current_user.id))
+    conn.commit()
+    conn.close()
+    flash('Submission retracted.')
+    return redirect(url_for('game', game_id=game_id))
+
+@app.route('/game/<int:game_id>/agree/<int:submission_id>', methods=['POST'])
+@login_required
+def agree_submission(game_id, submission_id):
+    conn = get_db()
+    c = conn.cursor()
+    
+    # Get the submission to agree with
+    c.execute('SELECT * FROM GameSubmissions WHERE id = ?', (submission_id,))
+    sub = c.fetchone()
+    
+    if not sub or sub['game_id'] != game_id:
+        conn.close()
+        flash('Submission not found.')
+        return redirect(url_for('game', game_id=game_id))
+        
+    if str(sub['submitted_by_id']) == str(current_user.id):
+        conn.close()
+        flash('You cannot agree with your own submission.')
+        return redirect(url_for('game', game_id=game_id))
+
+    # Create an identical submission for the current user to trigger consensus
+    c.execute('''
+        INSERT INTO GameSubmissions (game_id, submitted_by_id, away_score, home_score, box_score_json, proposed_date)
+        VALUES (?, ?, ?, ?, ?, ?)
+    ''', (game_id, current_user.id, sub['away_score'], sub['home_score'], sub['box_score_json'], sub['proposed_date']))
+
+    run_consensus_check(game_id, c)
+
+    conn.commit()
+    conn.close()
+    return redirect(url_for('game', game_id=game_id))
+@app.route('/login', methods=['GET', 'POST'])
+def login():
+    if request.method == 'POST':
+        username = request.form['username']
+        password = request.form['password']
+        
+        conn = get_db()
+        c = conn.cursor()
+        c.execute('SELECT * FROM Users WHERE username = ?', (username,))
+        user_row = c.fetchone()
+        conn.close()
+        
+        if user_row and check_password_hash(user_row['password_hash'], password):
+            user = User(user_row['id'], user_row['username'], user_row['is_admin'], user_row['team_name'], user_row['team_icon'])
+            login_user(user)
+            return redirect(url_for('index'))
+        else:
+            flash('Invalid username or password')
+            
+    return render_template('login.html')
+
+@app.route('/logout')
+@login_required
+def logout():
+    logout_user()
+    return redirect(url_for('index'))
+
+@app.route('/game/<int:game_id>')
+def game(game_id):
+    conn = get_db()
+    c = conn.cursor()
+    c.execute('''
+        SELECT g.*, 
+               a.team_name as away_team, a.team_icon as away_icon,
+               h.team_name as home_team, h.team_icon as home_icon
+        FROM Games g
+        LEFT JOIN Users a ON g.away_team_id = a.id
+        LEFT JOIN Users h ON g.home_team_id = h.id
+        WHERE g.id = ?
+    ''', (game_id,))
+    game_row = c.fetchone()
+    
+    if not game_row:
+        conn.close()
+        return "Game not found", 404
+        
+    game_dict = dict(game_row)
+    if game_dict['box_score_json']:
+        game_dict['box_score'] = json.loads(game_dict['box_score_json'])
+    else:
+        game_dict['box_score'] = None
+    
+    # Fetch pending submissions
+    c.execute('''
+        SELECT s.*, u.username, u.team_name
+        FROM GameSubmissions s
+        JOIN Users u ON s.submitted_by_id = u.id
+        WHERE s.game_id = ?
+        ORDER BY s.id DESC
+    ''', (game_id,))
+    submissions_raw = c.fetchall()
+    submissions = []
+    for s in submissions_raw:
+        s_dict = dict(s)
+        
+        # Hide components that match current game state
+        if s_dict['proposed_date'] == game_dict['scheduled_date']:
+            s_dict['proposed_date'] = None
+            
+        if game_dict['status'] == 'Final':
+            if s_dict['away_score'] == game_dict['away_score'] and s_dict['home_score'] == game_dict['home_score']:
+                s_dict['away_score'] = None
+                s_dict['home_score'] = None
+        
+        # Only show if there's still something to show
+        if s_dict['proposed_date'] or s_dict['away_score'] is not None:
+            if s_dict['box_score_json']:
+                s_dict['box_score'] = json.loads(s_dict['box_score_json'])
+            else:
+                s_dict['box_score'] = None
+            submissions.append(s_dict)
+        
+    conn.close()
+    return render_template('game.html', game=game_dict, submissions=submissions)
+
+def generate_tbd_playoffs(season_id, playoff_teams, series_length, c):
+    # Generates TBD playoff series
+    # For N=4: Series 1 (Semi A), Series 2 (Semi B), Series 3 (Finals)
+    # For N=2: Series 1 (Finals)
+    games_to_create = []
+    wins_needed = (series_length // 2) + 1
+    start_date = datetime.now() + timedelta(days=30)
+    
+    series_map = {
+        2: [1], # Finals
+        4: [1, 2, 3] # Semis then Finals
+    }
+    
+    rounds_map = {
+        2: {1: 1},
+        4: {1: 1, 2: 1, 3: 2}
+    }
+    
+    active_series = series_map.get(playoff_teams, [])
+    for sid in active_series:
+        rnd = rounds_map[playoff_teams][sid]
+        for g_num in range(1, series_length + 1):
+            is_conditional = 1 if g_num > wins_needed else 0
+            games_to_create.append((
+                season_id, None, None, 'TBD', 
+                (start_date + timedelta(days=sid*7 + g_num)).strftime('%Y-%m-%d'),
+                1, sid, rnd, g_num, is_conditional
+            ))
+            
+    c.executemany('''
+        INSERT INTO Games (season_id, away_team_id, home_team_id, status, scheduled_date, is_playoff, series_id, playoff_round, playoff_game_num, is_conditional)
+        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+    ''', games_to_create)
+
+def check_and_trigger_playoff_updates(game_id, c):
+    c.execute('SELECT season_id, is_playoff, series_id, playoff_round FROM Games WHERE id = ?', (game_id,))
+    game = c.fetchone()
+    if not game: return
+    season_id = game['season_id']
+    
+    if not game['is_playoff']:
+        # Regular season check
+        c.execute("SELECT COUNT(*) as c FROM Games WHERE season_id = ? AND is_playoff = 0 AND status != 'Final'", (season_id,))
+        if c.fetchone()['c'] == 0:
+            c.execute('SELECT playoff_teams FROM Seasons WHERE id = ?', (season_id,))
+            p_teams = c.fetchone()['playoff_teams'] or 0
+            
+            c.execute('''
+                SELECT u.id
+                FROM SeasonTeams st
+                JOIN Users u ON st.user_id = u.id
+                LEFT JOIN Games g ON (g.home_team_id = u.id OR g.away_team_id = u.id) 
+                    AND g.season_id = st.season_id AND g.status = 'Final' AND g.is_playoff = 0
+                WHERE st.season_id = ? AND st.status = 'Approved'
+                GROUP BY u.id ORDER BY SUM(CASE WHEN (g.home_team_id = u.id AND g.home_score > g.away_score) OR (g.away_team_id = u.id AND g.away_score > g.home_score) THEN 1 ELSE 0 END) DESC,
+                SUM(CASE WHEN g.home_team_id = u.id THEN g.home_score ELSE g.away_score END) DESC
+                LIMIT ?
+            ''', (season_id, p_teams))
+            top_teams = [row['id'] for row in c.fetchall()]
+            
+            if len(top_teams) == 2:
+                c.execute("UPDATE Games SET away_team_id = ?, home_team_id = ?, status = 'Scheduled' WHERE season_id = ? AND series_id = 1", (top_teams[1], top_teams[0], season_id))
+            elif len(top_teams) == 4:
+                # 1v4 (S1), 2v3 (S2)
+                c.execute("UPDATE Games SET away_team_id = ?, home_team_id = ?, status = 'Scheduled' WHERE season_id = ? AND series_id = 1", (top_teams[3], top_teams[0], season_id))
+                c.execute("UPDATE Games SET away_team_id = ?, home_team_id = ?, status = 'Scheduled' WHERE season_id = ? AND series_id = 2", (top_teams[2], top_teams[1], season_id))
+    else:
+        # Playoff advancement
+        c.execute('SELECT playoff_teams, playoff_series_length FROM Seasons WHERE id = ?', (season_id,))
+        s_info = c.fetchone()
+        wins_needed = (s_info['playoff_series_length'] // 2) + 1
+        sid = game['series_id']
+        
+        c.execute('''
+            SELECT 
+                SUM(CASE WHEN away_score > home_score THEN 1 ELSE 0 END) as aw,
+                SUM(CASE WHEN home_score > away_score THEN 1 ELSE 0 END) as hw,
+                MAX(away_team_id) as aid, MAX(home_team_id) as hid
+            FROM Games WHERE season_id = ? AND series_id = ? AND status = 'Final'
+        ''', (season_id, sid))
+        res = c.fetchone()
+        
+        winner = None
+        if res['aw'] >= wins_needed: winner = res['aid']
+        elif res['hw'] >= wins_needed: winner = res['hid']
+        
+        if winner:
+            c.execute("UPDATE Games SET status = 'Canceled' WHERE season_id = ? AND series_id = ? AND status IN ('Scheduled', 'TBD')", (season_id, sid))
+            if s_info['playoff_teams'] == 4:
+                if sid == 1: c.execute("UPDATE Games SET away_team_id = ? WHERE season_id = ? AND series_id = 3", (winner, season_id))
+                elif sid == 2: c.execute("UPDATE Games SET home_team_id = ? WHERE season_id = ? AND series_id = 3", (winner, season_id))
+                c.execute("UPDATE Games SET status = 'Scheduled' WHERE season_id = ? AND series_id = 3 AND away_team_id IS NOT NULL AND home_team_id IS NOT NULL AND status = 'TBD'", (season_id,))
+
+@app.route('/submit_game/<int:game_id>', methods=['POST'])
+@login_required
+def submit_game(game_id):
+    away_score = request.form.get('away_score')
+    home_score = request.form.get('home_score')
+    proposed_date = request.form.get('proposed_date')
+    
+    # Collect Box Score from grid (Optional)
+    box_score = {"away": [], "home": []}
+    cols = ["1", "2", "3", "4", "5", "6", "7", "8", "9", "extra", "runs", "hits", "errors"]
+    has_box_data = False
+    for team in ["away", "home"]:
+        for col in cols:
+            val = request.form.get(f"{team}_inn_{col}")
+            if val and val != "0" and val != "": has_box_data = True
+            box_score[team].append(int(val) if val and val.isdigit() else 0)
+    
+    box_score_json = json.dumps(box_score) if has_box_data else None
+    
+    conn = get_db()
+    c = conn.cursor()
+    
+    # Check if user is part of the game
+    c.execute('SELECT away_team_id, home_team_id, status FROM Games WHERE id = ?', (game_id,))
+    game = c.fetchone()
+    
+    if not game or game['status'] == 'Final':
+        conn.close()
+        flash('Game not found or already finalized.')
+        return redirect(url_for('game', game_id=game_id))
+        
+    if str(current_user.id) not in (str(game['away_team_id']), str(game['home_team_id'])):
+        conn.close()
+        flash('You are not authorized to submit for this game.')
+        return redirect(url_for('game', game_id=game_id))
+        
+    # Save submission (Update existing or Insert new)
+    away_score_val = int(away_score) if away_score else None
+    home_score_val = int(home_score) if home_score else None
+    
+    c.execute('SELECT * FROM GameSubmissions WHERE game_id = ? AND submitted_by_id = ?', (game_id, current_user.id))
+    existing = c.fetchone()
+    
+    if existing:
+        if away_score_val is not None:
+            c.execute('''
+                UPDATE GameSubmissions 
+                SET away_score = ?, home_score = ?, box_score_json = ? 
+                WHERE id = ?
+            ''', (away_score_val, home_score_val, box_score_json, existing['id']))
+        if proposed_date:
+            c.execute('''
+                UPDATE GameSubmissions 
+                SET proposed_date = ? 
+                WHERE id = ?
+            ''', (proposed_date, existing['id']))
+    else:
+        c.execute('''
+            INSERT INTO GameSubmissions (game_id, submitted_by_id, away_score, home_score, box_score_json, proposed_date)
+            VALUES (?, ?, ?, ?, ?, ?)
+        ''', (game_id, current_user.id, away_score_val, home_score_val, box_score_json, proposed_date))
+    
+    run_consensus_check(game_id, c)
+                
+    conn.commit()
+    conn.close()
+    flash('Submission recorded.')
+    return redirect(url_for('game', game_id=game_id))
+
+@app.route('/team', methods=['GET', 'POST'])
+@login_required
+def team():
+    if request.method == 'POST':
+        conn = get_db()
+        c = conn.cursor()
+        
+        new_team_name = request.form.get('team_name')
+        if new_team_name:
+            c.execute('UPDATE Users SET team_name = ? WHERE id = ?', (new_team_name, current_user.id))
+            current_user.team_name = new_team_name
+            
+        new_password = request.form.get('new_password')
+        if new_password:
+            c.execute('UPDATE Users SET password_hash = ? WHERE id = ?', (generate_password_hash(new_password), current_user.id))
+            
+        icon_file = request.files.get('team_icon')
+        if icon_file and icon_file.filename != '':
+            filename = secure_filename(icon_file.filename)
+            filepath = os.path.join(app.config['UPLOAD_FOLDER'], f"{current_user.id}_{filename}")
+            icon_file.save(filepath)
+            c.execute('UPDATE Users SET team_icon = ? WHERE id = ?', (f"{current_user.id}_{filename}", current_user.id))
+            current_user.team_icon = f"{current_user.id}_{filename}"
+            
+        conn.commit()
+        conn.close()
+        flash('Team profile updated successfully.')
+        return redirect(url_for('team'))
+        
+    conn = get_db()
+    c = conn.cursor()
+    c.execute('SELECT * FROM Seasons')
+    seasons = c.fetchall()
+    
+    c.execute('SELECT season_id, status FROM SeasonTeams WHERE user_id = ?', (current_user.id,))
+    my_seasons = {row['season_id']: row['status'] for row in c.fetchall()}
+    conn.close()
+        
+    return render_template('team.html', seasons=seasons, my_seasons=my_seasons)
+
+@app.route('/team/join_season/<int:season_id>', methods=['POST'])
+@login_required
+def join_season(season_id):
+    if current_user.is_admin:
+        flash('Admins cannot join seasons as teams.')
+        return redirect(url_for('team'))
+        
+    conn = get_db()
+    c = conn.cursor()
+    try:
+        c.execute('INSERT INTO SeasonTeams (season_id, user_id, status) VALUES (?, ?, ?)', (season_id, current_user.id, 'Pending'))
+        conn.commit()
+        flash('Requested to join season.')
+    except sqlite3.IntegrityError:
+        flash('Already requested or joined this season.')
+    finally:
+        conn.close()
+        
+    return redirect(url_for('index', season_id=season_id))
+
+@app.route('/admin', methods=['GET', 'POST'])
+@login_required
+def admin():
+    if not current_user.is_admin:
+        return "Access Denied", 403
+        
+    conn = get_db()
+    c = conn.cursor()
+    
+    if request.method == 'POST':
+        action = request.form.get('action')
+        if action == 'create_season':
+            name = request.form['name']
+            c.execute('INSERT INTO Seasons (name) VALUES (?)', (name,))
+            conn.commit()
+            flash('Season created.')
+        
+        elif action == 'create_team':
+            username = request.form['username']
+            password = request.form['password']
+            team_name = request.form['team_name']
+            
+            try:
+                c.execute('''
+                    INSERT INTO Users (username, password_hash, team_name)
+                    VALUES (?, ?, ?)
+                ''', (username, generate_password_hash(password), team_name))
+                conn.commit()
+                flash('Team created.')
+            except sqlite3.IntegrityError:
+                flash('Username already exists.')
+    
+    c.execute('SELECT * FROM Seasons')
+    seasons = c.fetchall()
+    
+    c.execute('SELECT * FROM Users WHERE is_admin = 0')
+    teams = c.fetchall()
+    
+    conn.close()
+    return render_template('admin.html', seasons=seasons, teams=teams)
+
+@app.route('/admin/season/<int:season_id>/manage_teams', methods=['GET', 'POST'])
+@login_required
+def admin_manage_teams(season_id):
+    if not current_user.is_admin:
+        return "Access Denied", 403
+        
+    conn = get_db()
+    c = conn.cursor()
+    
+    if request.method == 'POST':
+        action = request.form.get('action')
+        user_id = request.form.get('user_id')
+        
+        if action == 'approve':
+            c.execute("UPDATE SeasonTeams SET status = 'Approved' WHERE season_id = ? AND user_id = ?", (season_id, user_id))
+            flash('Team approved.')
+        elif action == 'deny' or action == 'remove':
+            c.execute("DELETE FROM SeasonTeams WHERE season_id = ? AND user_id = ?", (season_id, user_id))
+            flash(f"Team {action}d.")
+        elif action == 'add':
+            team_ids = request.form.getlist('team_ids')
+            for tid in team_ids:
+                try:
+                    c.execute("INSERT INTO SeasonTeams (season_id, user_id, status) VALUES (?, ?, 'Approved')", (season_id, tid))
+                except sqlite3.IntegrityError:
+                    c.execute("UPDATE SeasonTeams SET status = 'Approved' WHERE season_id = ? AND user_id = ?", (season_id, tid))
+            flash('Selected teams added directly.')
+            
+        conn.commit()
+        return redirect(url_for('admin_manage_teams', season_id=season_id))
+        
+    c.execute('SELECT * FROM Seasons WHERE id = ?', (season_id,))
+    season = c.fetchone()
+    
+    c.execute('SELECT * FROM Users WHERE is_admin = 0')
+    all_teams = c.fetchall()
+    
+    c.execute('''
+        SELECT u.id, u.team_name, u.username, st.status 
+        FROM SeasonTeams st
+        JOIN Users u ON st.user_id = u.id
+        WHERE st.season_id = ?
+    ''', (season_id,))
+    enrolled_data = c.fetchall()
+    
+    pending_teams = [t for t in enrolled_data if t['status'] == 'Pending']
+    approved_teams = [t for t in enrolled_data if t['status'] == 'Approved']
+    enrolled_ids = [t['id'] for t in enrolled_data]
+    
+    conn.close()
+    return render_template('admin_manage_teams.html', season=season, all_teams=all_teams, pending_teams=pending_teams, approved_teams=approved_teams, enrolled_ids=enrolled_ids)
+
+@app.route('/admin/season/<int:season_id>/delete', methods=['POST'])
+@login_required
+def admin_delete_season(season_id):
+    if not current_user.is_admin:
+        return "Access Denied", 403
+        
+    conn = get_db()
+    c = conn.cursor()
+    c.execute('DELETE FROM GameSubmissions WHERE game_id IN (SELECT id FROM Games WHERE season_id = ?)', (season_id,))
+    c.execute('DELETE FROM Games WHERE season_id = ?', (season_id,))
+    c.execute('DELETE FROM SeasonTeams WHERE season_id = ?', (season_id,))
+    c.execute('DELETE FROM Seasons WHERE id = ?', (season_id,))
+    conn.commit()
+    conn.close()
+    flash('Season deleted.')
+    return redirect(url_for('admin'))
+
+@app.route('/admin/season/<int:season_id>/generate_schedule', methods=['POST'])
+@login_required
+def admin_generate_schedule(season_id):
+    if not current_user.is_admin:
+        return "Access Denied", 403
+        
+    confirm = request.form.get('confirm') == 'true'
+    total_games_per_team = int(request.form['total_games'])
+    gpw = int(request.form['games_per_week'])
+    p_teams = int(request.form['playoff_teams'])
+    p_series_len = int(request.form['playoff_series_length'])
+
+    conn = get_db()
+    c = conn.cursor()
+    
+    # Check if any games are finalized
+    c.execute("SELECT COUNT(*) as c FROM Games WHERE season_id = ? AND status = 'Final'", (season_id,))
+    final_count = c.fetchone()['c']
+    
+    if final_count > 0 and not confirm:
+        conn.close()
+        return {"status": "confirm_required", "message": f"There are {final_count} finalized games. Regenerating will delete ALL games. Proceed?"}, 200
+
+    # Update Season Parameters
+    c.execute('''
+        UPDATE Seasons 
+        SET total_games = ?, games_per_week = ?, playoff_teams = ?, playoff_series_length = ?
+        WHERE id = ?
+    ''', (total_games_per_team, gpw, p_teams, p_series_len, season_id))
+
+    c.execute('SELECT user_id FROM SeasonTeams WHERE season_id = ? AND status = "Approved"', (season_id,))
+    team_ids = [row['user_id'] for row in c.fetchall()]
+    
+    if len(team_ids) < 2:
+        conn.close()
+        flash('Not enough approved teams to generate schedule.')
+        return redirect(url_for('admin'))
+        
+    # Delete ALL existing games for this season
+    c.execute("DELETE FROM Games WHERE season_id = ?", (season_id,))
+    c.execute("DELETE FROM GameSubmissions WHERE game_id NOT IN (SELECT id FROM Games)")
+    
+    # Per-team schedule generation using the circle method
+    num_teams = len(team_ids)
+    
+    if num_teams % 2 != 0:
+        team_ids.append(None) # Add a bye team
+        num_teams += 1
+        
+    games_count = {tid: 0 for tid in team_ids if tid is not None}
+    current_teams = list(team_ids)
+    games_to_create = []
+    start_date = datetime.now()
+    
+    round_num = 0
+    while any(count < total_games_per_team for count in games_count.values()):
+        for i in range(num_teams // 2):
+            team1 = current_teams[i]
+            team2 = current_teams[num_teams - 1 - i]
+            
+            if team1 is not None and team2 is not None:
+                if games_count[team1] < total_games_per_team and games_count[team2] < total_games_per_team:
+                    if round_num % 2 == 0: away_id, home_id = team1, team2
+                    else: away_id, home_id = team2, team1
+                    
+                    day_offset = int((round_num * 7) / gpw)
+                    game_date = (start_date + timedelta(days=day_offset)).strftime('%Y-%m-%d')
+                    games_to_create.append((season_id, away_id, home_id, game_date))
+                    games_count[team1] += 1
+                    games_count[team2] += 1
+        
+        current_teams = [current_teams[0]] + [current_teams[-1]] + current_teams[1:-1]
+        round_num += 1
+
+    c.executemany('''
+        INSERT INTO Games (season_id, away_team_id, home_team_id, scheduled_date)
+        VALUES (?, ?, ?, ?)
+    ''', games_to_create)
+    
+    # Also generate Playoff TBDs
+    generate_tbd_playoffs(season_id, p_teams, p_series_len, c)
+    
+    conn.commit()
+    conn.close()
+    flash(f'Schedule and Playoffs generated.')
+    if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
+        return {"status": "success", "redirect": url_for('admin')}
+    return redirect(url_for('admin'))
+
+@app.route('/admin/game/<int:game_id>/edit', methods=['GET', 'POST'])
+@login_required
+def admin_edit_game(game_id):
+    if not current_user.is_admin:
+        return "Access Denied", 403
+        
+    conn = get_db()
+    c = conn.cursor()
+    
+    if request.method == 'POST':
+        scheduled_date = request.form.get('scheduled_date')
+        status = request.form.get('status')
+        away_score = request.form.get('away_score')
+        home_score = request.form.get('home_score')
+        
+        # Collect Box Score from grid
+        box_score = {"away": [], "home": []}
+        cols = ["1", "2", "3", "4", "5", "6", "7", "8", "9", "extra", "runs", "hits", "errors"]
+        has_box_data = False
+        for team in ["away", "home"]:
+            for col in cols:
+                val = request.form.get(f"{team}_inn_{col}")
+                if val and val != "0" and val != "": has_box_data = True
+                box_score[team].append(int(val) if val and val.isdigit() else 0)
+        
+        box_score_json = json.dumps(box_score) if has_box_data else None
+        
+        c.execute('''
+            UPDATE Games 
+            SET scheduled_date = ?, status = ?, away_score = ?, home_score = ?, box_score_json = ?
+            WHERE id = ?
+        ''', (scheduled_date, status, away_score if away_score else None, home_score if home_score else None, box_score_json, game_id))
+        
+        if status == 'Final':
+            check_and_trigger_playoff_updates(game_id, c)
+            c.execute('DELETE FROM GameSubmissions WHERE game_id = ?', (game_id,))
+            
+        conn.commit()
+        flash('Game updated.')
+        return redirect(url_for('game', game_id=game_id))
+        
+    c.execute('''
+        SELECT g.*, a.team_name as away_team, h.team_name as home_team
+        FROM Games g
+        LEFT JOIN Users a ON g.away_team_id = a.id
+        LEFT JOIN Users h ON g.home_team_id = h.id
+        WHERE g.id = ?
+    ''', (game_id,))
+    game_row = c.fetchone()
+    if not game_row:
+        conn.close()
+        return "Game not found", 404
+        
+    game_dict = dict(game_row)
+    if game_dict['box_score_json']:
+        game_dict['box_score'] = json.loads(game_dict['box_score_json'])
+    else:
+        game_dict['box_score'] = None
+        
+    conn.close()
+    return render_template('admin_edit_game.html', game=game_dict)
+
+if __name__ == '__main__':
+    app.run(debug=True, port=5000)
\ No newline at end of file