path: root/app.py

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=1946)