app.py

from flask import Flask, render_template, request, redirect, url_for, session, flash, send_file
from flask_sqlalchemy import SQLAlchemy
import pandas as pd
import uuid
import random
import io
import random

app = Flask(__name__)
app.secret_key = 'supersecretkey'  # 请更换为更安全的密钥
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///poker_tournament.db'  # 使用 SQLite 数据库引擎在当前项目根目录下生成一个持久化的 .db 文件
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# ----------------------
# 数据库模型定义
# ----------------------

class Player(db.Model):
    """
    选手信息模型，记录姓名、手机号、唯一编号，以及每轮的积分
    """
    id = db.Column(db.Integer, primary_key=True)
    unique_id = db.Column(db.String(5), unique=True, nullable=False)
    name = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20), unique=True, nullable=False)
    score_round1 = db.Column(db.Integer, default=0)
    score_round2 = db.Column(db.Integer, default=0)
    score_round3 = db.Column(db.Integer, default=0)
    
    def total_score(self):
        return self.score_round1 + self.score_round2

class TableAssignment(db.Model):
    """
    桌次分配记录，每条记录表示某一轮某桌中某座位分配给哪个选手。
    round_number: 轮次 (1,2,3)
    table_number: 桌子编号（例如第一桌、第二桌…）
    seat_number: 桌内编号（1-8）
    """
    id = db.Column(db.Integer, primary_key=True)
    round_number = db.Column(db.Integer, nullable=False)
    table_number = db.Column(db.Integer, nullable=False)
    seat_number = db.Column(db.Integer, nullable=False)
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'), nullable=False)
    player = db.relationship('Player')

class Judge(db.Model):
    """
    裁判账号模型，示例中只使用明文密码（实际使用时请对密码进行哈希加密）
    """
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(30), unique=True, nullable=False)
    password = db.Column(db.String(100), nullable=False)
    
class Elimination(db.Model):
    """
    第三轮淘汰记录，每记录一条淘汰的信息，包括淘汰顺序和选手ID
    """
    id = db.Column(db.Integer, primary_key=True)
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'), nullable=False)
    elimination_order = db.Column(db.Integer, nullable=False)
    player = db.relationship('Player')

class JudgeSubmission(db.Model):
    """
    裁判录入记录：
      - round_number, table_number, judge_username
      - 每个座位的筹码(chip_1 ~ chip_8)与计算后的积分(score_1 ~ score_8)
      - 记录录入的修改（例如选手编号修改）可通过提交时的表单数据体现
    """
    id = db.Column(db.Integer, primary_key=True)
    round_number = db.Column(db.Integer, nullable=False)
    table_number = db.Column(db.Integer, nullable=False)
    judge_username = db.Column(db.String(30), nullable=False)

    # 存储每座位的“最终确定”的选手ID
    seat1_player_id = db.Column(db.Integer, nullable=True)
    seat2_player_id = db.Column(db.Integer, nullable=True)
    seat3_player_id = db.Column(db.Integer, nullable=True)
    seat4_player_id = db.Column(db.Integer, nullable=True)
    seat5_player_id = db.Column(db.Integer, nullable=True)
    seat6_player_id = db.Column(db.Integer, nullable=True)
    seat7_player_id = db.Column(db.Integer, nullable=True)
    seat8_player_id = db.Column(db.Integer, nullable=True)

    # 保存每个座位的筹码值和积分
    chip_1 = db.Column(db.Integer, nullable=True)
    chip_2 = db.Column(db.Integer, nullable=True)
    chip_3 = db.Column(db.Integer, nullable=True)
    chip_4 = db.Column(db.Integer, nullable=True)
    chip_5 = db.Column(db.Integer, nullable=True)
    chip_6 = db.Column(db.Integer, nullable=True)
    chip_7 = db.Column(db.Integer, nullable=True)
    chip_8 = db.Column(db.Integer, nullable=True)
    score_1 = db.Column(db.Integer, nullable=True)
    score_2 = db.Column(db.Integer, nullable=True)
    score_3 = db.Column(db.Integer, nullable=True)
    score_4 = db.Column(db.Integer, nullable=True)
    score_5 = db.Column(db.Integer, nullable=True)
    score_6 = db.Column(db.Integer, nullable=True)
    score_7 = db.Column(db.Integer, nullable=True)
    score_8 = db.Column(db.Integer, nullable=True)
    # 你也可以记录提交时间等信息

# ----------------------
# 初始化数据库及创建默认数据
# ----------------------
@app.before_request
def initialize_once():
    if not hasattr(app, 'initialized'):
        create_tables()  # 仅在第一次请求时调用一次初始化函数
        app.initialized = True

# @app.before_first_request
def create_tables():
    db.create_all()
    # 如果不存在默认裁判账号，则创建一个默认账号（用户名 judge1, 密码 password）
    if not Judge.query.filter_by(username='judge001').first():
        judge = Judge(username='judge001', password='password')
        db.session.add(judge)
        db.session.commit()

    # 如果没有默认超级裁判账号judge044，则创建（以及其他初始账号可在单独路由生成）
    if not Judge.query.filter_by(username='judge044').first():
        super_judge = Judge(username='judge044', password='password123')
        db.session.add(super_judge)
        db.session.commit()

# ----------------------
# 辅助函数：分桌、积分计算
# ----------------------
def assign_tables(players, round_number):
    """
    分桌操作：
      - 第一轮：按选手导入后分配的随机编号排序分桌（1~8为第一桌，9~16为第二桌……）。
      - 第二轮：基于第一轮累计积分（score_round1）排序重新分桌（不淘汰），并保留第一轮数据。
      - 第三轮：从前两轮累计积分中取前8名（积分清零后），分为一桌。
    """
    # 清除此轮以前的分桌记录
    # TableAssignment.query.filter_by(round_number=round_number).delete()
    # db.session.commit()
    
    # 筛选出该轮尚未分桌的选手（避免重复分配）
    assigned_ids = {a.player_id for a in TableAssignment.query.filter_by(round_number=round_number).all()}
    unassigned = [p for p in players if p.id not in assigned_ids]
    
    if round_number == 1:
        # 第一轮：已随机生成 unique_id 后，按 unique_id 升序
        unassigned = sorted(unassigned, key=lambda p: int(p.unique_id))
    elif round_number == 2:
        # 按第一轮成绩排序（降序）；保留所有选手
        unassigned = sorted(unassigned, key=lambda p: p.score_round1, reverse=True)
    elif round_number == 3:
        # 第三轮只选前8人；重置 round3 分数
        sorted_players = sorted(players, key=lambda p: p.total_score(), reverse=True)[:8]
        # 考虑是否已有分桌数据，本轮理论上只有一桌
        unassigned = [p for p in sorted_players if p.id not in assigned_ids]
        for p in unassigned:
            p.score_round3 = 0
        db.session.commit()
    else:
        return

    assignments = []
    table_size = 8
    # 按每 8 人一桌
    for index, player in enumerate(unassigned):
        table_number = index // table_size + 1
        seat_number = index % table_size + 1
        assignment = TableAssignment(
            round_number=round_number,
            table_number=table_number,
            seat_number=seat_number,
            player_id=player.id
        )
        assignments.append(assignment)
    db.session.bulk_save_objects(assignments)
    db.session.commit()

@app.route('/start_round/<int:round_number>')
def start_round(round_number):
    """
    仅超级裁判可点击“进入第X轮”后触发的分桌逻辑
    """
    if 'judge' not in session or session['judge'] != 'judge044':
        flash("没有权限操作此功能")
        return redirect(url_for('judge_dashboard'))
    
    if round_number not in [2, 3]:
        flash("无效的轮次")
        return redirect(url_for('judge_dashboard'))
    
    # 取所有选手
    players = Player.query.all()
    # 调用你的分桌函数
    assign_tables(players, round_number)  # 保留第一轮记录，不覆盖
    flash(f"已成功开始第 {round_number} 轮，并完成分桌！")
    return redirect(url_for('view_tables', round_number=round_number))

def calculate_score_from_chips(chips, num_players):
    """
    简单积分算法：每个座位获得积分 = 筹码数 - 平均筹码数（仅对实际人数计算），如果超过实际人数的名次，则计 0 分
    :param chips: 长度为8的列表（输入的筹码数，缺席者可视为空或 0）
    :param num_players: 当前实际参赛人数（<=8）
    """
    if num_players <= 0:
        return [0] * 8
    # 仅对前 num_players 名计算平均（忽略后面空白位）
    valid_chips = chips[:num_players]
    avg = sum(valid_chips) / len(valid_chips)
    scores = []
    for i in range(8):
        if i < num_players:
            scores.append(int(chips[i] - avg))
        else:
            scores.append(0)
    return scores


# ----------------------
# 各种路由（页面）定义
# ----------------------

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/upload_roster', methods=['GET', 'POST'])
def upload_roster():
    if request.method == 'POST':
        # 🧹清空旧选手（调试用）
        # Player.query.delete()
        # TableAssignment.query.filter_by(round_number=1).delete()
        # db.session.commit()

        file = request.files['file']
        if file:
            try:
                df = pd.read_excel(file)
                if '姓名' not in df.columns or '手机号' not in df.columns:
                    flash("Excel 必须包含 ‘姓名’ 和 ‘手机号’ 两列")
                    return redirect(request.url)

                # 将所有选手打包到一个列表
                raw_players = []
                for _, row in df.iterrows():
                    name = row['姓名']
                    phone = str(row['手机号'])
                    if Player.query.filter_by(phone=phone).first():
                        continue  # 避免重复手机号
                    raw_players.append({'name': name, 'phone': phone})

                if len(raw_players) > 999:
                    flash("选手数量不能超过 999")
                    return redirect(request.url)

                # 打乱顺序，生成随机编号（001 ~ N）
                random.shuffle(raw_players)
                inserted_players = []
                for idx, player_data in enumerate(raw_players):
                    unique_id = str(idx + 1).zfill(3)  # 生成编号 001~104
                    player = Player(unique_id=unique_id, name=player_data['name'], phone=player_data['phone'])
                    db.session.add(player)
                    inserted_players.append(player)
                db.session.commit()

                # ✅ 按编号排序，顺序分桌（桌号、座位号）
                # players = Player.query.order_by(Player.unique_id).all()
                # TableAssignment.query.filter_by(round_number=1).delete()
                # db.session.commit()
                # assignments = []
                # for index, player in enumerate(players):
                #     table_number = index // 8 + 1
                #     seat_number = index % 8 + 1
                #     assignment = TableAssignment(
                #         round_number=1,
                #         table_number=table_number,
                #         seat_number=seat_number,
                #         player_id=player.id
                #     )
                #     assignments.append(assignment)
                # db.session.bulk_save_objects(assignments)
                # db.session.commit()

                # flash(f"导入成功，共 {len(inserted_players)} 名选手，已随机编号并完成第一轮分桌")
                # print(f"导入成功，共 {len(inserted_players)} 名选手，已随机编号并完成第一轮分桌")
                # print(inserted_players)
                # print(raw_players)
                # return redirect(url_for('view_tables', round_number=1))

                # 进行第一轮分桌，按编号升序分桌（保证 1~8 分配到第一桌等）
                players = Player.query.order_by(Player.unique_id).all()
                # 注意：不删除其他轮次记录，第一轮新分桌只为未分桌选手分配
                assign_tables(players, round_number=1)
                flash(f"成功导入 {len(inserted_players)} 名选手，并已第一轮分桌完成")
                return redirect(url_for('view_tables', round_number=1))
            
            except Exception as e:
                flash("导入失败：" + str(e))
                return redirect(request.url)
    return render_template('upload_roster.html')

@app.route('/round/<int:round_number>/assign')
def assign_round(round_number):
    """
    手动触发指定轮次的桌次分配。
    根据轮次规则重新分配桌位，并生成 TableAssignment 记录。
    """
    players = Player.query.all()
    if round_number not in [1, 2, 3]:
        flash("无效的轮次")
        return redirect(url_for('index'))
    assign_tables(players, round_number)
    flash(f"成功为第 {round_number} 轮进行桌次分配！")
    return redirect(url_for('view_tables', round_number=round_number))

@app.route('/round/<int:round_number>/tables')
def view_tables(round_number):
    """
    查看某轮的桌次分配情况，显示桌号、座位号、选手姓名及其唯一编号。
    """
    assignments = TableAssignment.query.filter_by(round_number=round_number)\
                        .order_by(TableAssignment.table_number, TableAssignment.seat_number).all()
    return render_template('view_tables.html', assignments=assignments, round_number=round_number)

# ---------- 裁判登录及操作 --------------

@app.route('/judge/login', methods=['GET', 'POST'])
def judge_login():
    """
    裁判登录页面，登录成功后进入裁判工作台。
    （注意：示例中未对密码进行加密）
    """
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        judge = Judge.query.filter_by(username=username, password=password).first()
        if judge:
            session['judge'] = judge.username
            flash("登录成功")
            return redirect(url_for('judge_dashboard'))
        else:
            flash("用户名或密码错误")
    return render_template('judge_login.html')

@app.route('/judge/logout')
def judge_logout():
    session.pop('judge', None)
    flash("已退出登录")
    return redirect(url_for('index'))

@app.route('/judge/dashboard')
def judge_dashboard():
    """
    裁判工作台页面，裁判可以选择录入每桌筹码数，
    或者进入第三轮淘汰记录页面。
    """
    if 'judge' not in session:
        flash("请先登录")
        return redirect(url_for('judge_login'))
    return render_template('judge_dashboard.html')

@app.route('/judge/submit_score', methods=['GET', 'POST'])
def judge_submit():
    if 'judge' not in session:
        flash("请先登录")
        return redirect(url_for('judge_login'))
    judge_username = session['judge']
    
    if request.method == 'POST':
        # 前端会经过 JS 弹窗确认后提交数据
        round_number = int(request.form['round_number'])
        table_number = int(request.form['table_number'])
        # 收集每座输入的筹码及可修改的选手编号（和姓名可选）
        chips = []
        new_ids = []
        # seat_player_ids = []  # 用于存 seat1_player_id ~ seat8_player_id

        for seat in range(1, 9):
            chip = int(request.form.get(f'chip_{seat}', 0))
            chips.append(chip)
            # 若裁判对某座位修改了选手编号，则接收（允许为空）
            new_id = request.form.get(f'player_id_{seat}', '').strip()
            new_ids.append(new_id)
        
        # 取出当前该桌的分桌数据（实际参赛人员），注意可能不足 8 人
        assignments = TableAssignment.query.filter_by(round_number=round_number, table_number=table_number)\
                            .order_by(TableAssignment.seat_number).all()
        actual_count = len(assignments)
        # 若不足8人，后面座位看作空白，筹码记0
        while len(chips) < 8:
            chips.append(0)
        # 计算积分：对实际人数计算积分，多余座位记0分
        scores = calculate_score_from_chips(chips, actual_count)
        
        # 保存录入记录
        submission = JudgeSubmission(
            round_number=round_number,
            table_number=table_number,
            judge_username=judge_username,
            chip_1=chips[0],
            chip_2=chips[1],
            chip_3=chips[2],
            chip_4=chips[3],
            chip_5=chips[4],
            chip_6=chips[5],
            chip_7=chips[6],
            chip_8=chips[7],
            score_1=scores[0],
            score_2=scores[1],
            score_3=scores[2],
            score_4=scores[3],
            score_5=scores[4],
            score_6=scores[5],
            score_7=scores[6],
            score_8=scores[7],
            seat1_player_id = new_ids[0],
            seat2_player_id = new_ids[1],
            seat3_player_id = new_ids[2],
            seat4_player_id = new_ids[3],
            seat5_player_id = new_ids[4],
            seat6_player_id = new_ids[5],
            seat7_player_id = new_ids[6],
            seat8_player_id = new_ids[7]
        )
        db.session.add(submission)
        db.session.commit()
        
        # 更新每位选手的积分（前两轮累计，第三轮为独立积分）
        # 这里按分桌排序逐一更新，如果对应座位无选手，则跳过更新
        for seat, assign in enumerate(assignments, start=1):
            player = assign.player
            # 如果裁判修改了该选手编号，则更新（这里仅修改 unique_id，姓名等信息也可按需求更新）
            if new_ids[seat-1]:
                player.unique_id = new_ids[seat-1]
            if round_number == 1:
                player.score_round1 += scores[seat-1]
            elif round_number == 2:
                player.score_round2 += scores[seat-1]
            elif round_number == 3:
                player.score_round3 += scores[seat-1]
        db.session.commit()
        flash("上传成功")
        return redirect(url_for('judge_dashboard'))
    else:
        # --- 处理 GET 请求：根据 轮次 + 桌号 显示该轮分桌 ---
        round_number = request.args.get('round_number', 1, type=int)
        table_number = request.args.get('table_number', 1, type=int)

        # 检查权限，非超级裁判只能录入自己那桌
        if judge_username != 'judge044':
            allowed_table = int(judge_username.replace('judge',''))
            if table_number != allowed_table:
                flash("无权录入其他桌的数据！")
                return redirect(url_for('judge_dashboard'))
        
        # 查找分配
        assignments = TableAssignment.query.filter_by(
            round_number=round_number,
            table_number=table_number
        ).order_by(TableAssignment.seat_number).all()

        # seat_info 用于存储 1~8 的选手编号、姓名
        seat_info = {}
        for seat in range(1,9):
            seat_info[seat] = {'player_id': '', 'player_name': '若无则留空'}
        
        for a in assignments:
            seat = a.seat_number
            seat_info[seat]['player_id'] = a.player.unique_id
            seat_info[seat]['player_name'] = a.player.name
        
        return render_template('judge_submit.html',
                               round_number=round_number,
                               table_number=table_number,
                               seat_info=seat_info)
    
    # GET 请求：显示当前裁判可操作的桌次与分桌信息
    # 判断裁判权限：非超级裁判只能操作与裁判账号数字对应的桌次
    # if judge_username != "judge044":
    #     # 从用户名中提取数字，例如 judge003 -> 3
    #     allowed_table = int(judge_username[-3:])
    # else:
    #     allowed_table = None  # 超级裁判可选择任意桌
    # # 前端页面中，裁判可选择轮次（下拉框），如果非超级裁判，table_number固定显示为 allowed_table，
    # # 同时展示该桌的分桌情况（选手编号和姓名），并预留不足8人的空白行（填写预留编号，如105,106,...）
    # # 这里我们简单传递空数据，实际页面请根据需求编写 JS 或模板逻辑
    # # return render_template('judge_submit.html', allowed_table=allowed_table)
    # # 从前端 URL 参数获取本次要操作的轮次和桌号，没有就给默认值
    # round_number = request.args.get('round_number', 1, type=int)
    # if allowed_table is None:
    #     # 超级裁判：可以从前端参数获取桌号
    #     table_number = request.args.get('table_number', 1, type=int)
    # else:
    #     # 普通裁判：固定桌号
    #     table_number = allowed_table
    
    # # 查数据库，获取该轮次、该桌的分桌信息（座位1~8）
    # assignments = TableAssignment.query.filter_by(
    #     round_number=round_number,
    #     table_number=table_number
    # ).order_by(TableAssignment.seat_number).all()
    
    # # 做一个 seat -> { 'player_id':..., 'player_name':... } 的映射表
    # seat_info = {}
    # # 先默认 8 个空位
    # for seat in range(1, 9):
    #     seat_info[seat] = {
    #         'player_id': '',
    #         'player_name': '若无则留空'
    #     }
    # # 填充已有分桌
    # for a in assignments:
    #     seat_info[a.seat_number]['player_id'] = a.player.unique_id
    #     seat_info[a.seat_number]['player_name'] = a.player.name
    
    # # 把它传给模板
    # return render_template('judge_submit.html',
    #                        allowed_table=allowed_table,
    #                        round_number=round_number,
    #                        table_number=table_number,
    #                        seat_info=seat_info)

# 超级裁判可以撤销某个裁判的录入记录
@app.route('/judge/revoke/<int:submission_id>')
def judge_revoke(submission_id):
    if 'judge' not in session or session['judge'] != "judge044":
        flash("没有权限")
        return redirect(url_for('judge_dashboard'))
    sub = JudgeSubmission.query.get(submission_id)
    if sub:
        round_number = sub.round_number
        # 回退seat1~8的积分
        seat_player_ids = [
            sub.seat1_player_id,
            sub.seat2_player_id,
            sub.seat3_player_id,
            sub.seat4_player_id,
            sub.seat5_player_id,
            sub.seat6_player_id,
            sub.seat7_player_id,
            sub.seat8_player_id
        ]
        seat_scores = [
            sub.score_1,
            sub.score_2,
            sub.score_3,
            sub.score_4,
            sub.score_5,
            sub.score_6,
            sub.score_7,
            sub.score_8
        ]
        # 对每个seat，找到对应player并把积分减掉
        for i in range(8):
            p_id = seat_player_ids[i]
            sc = seat_scores[i]
            if p_id and sc:  # 如果有玩家ID且积分不为0
                player = Player.query.get(p_id)
                if player:
                    if round_number == 1:
                        player.score_round1 -= sc
                    elif round_number == 2:
                        player.score_round2 -= sc
                    elif round_number == 3:
                        player.score_round3 -= sc
        db.session.delete(sub)
        db.session.commit()

        flash("撤销成功，积分已回退")
    else:
        flash("未找到对应记录")

    return redirect(url_for('judge_view_submissions'))

@app.route('/judge/view_submissions')
def judge_view_submissions():
    # 1) 检查是否是超级裁判
    if 'judge' not in session or session['judge'] != 'judge044':
        flash("只有超级裁判可查看所有录入记录")
        return redirect(url_for('judge_dashboard'))
    
    # 2) 查询所有录入记录
    submissions = JudgeSubmission.query.order_by(JudgeSubmission.round_number,
                                                 JudgeSubmission.table_number,
                                                 JudgeSubmission.id).all()
    # 3) 传给模板
    return render_template('judge_view_submissions.html', submissions=submissions)

# ---------- 选手查询 --------------

# 选手查询（根据手机号或编号查询分桌信息）
@app.route('/player/query', methods=['GET', 'POST'])
def player_query():
    data = None
    if request.method == 'POST':
        identifier = request.form['identifier'].strip()
        # 允许输入手机号或选手唯一编号
        player = Player.query.filter((Player.phone==identifier) | (Player.unique_id==identifier)).first()
        if player:
            # 查询三轮的分桌信息
            all_rounds_info = []
            for r in [1, 2, 3]:
                # 是否有该轮分配记录
                assignment = TableAssignment.query.filter_by(round_number=r, player_id=player.id).first()
                if assignment:
                    all_rounds_info.append({
                        'round': r,
                        'table_number': assignment.table_number,
                        'seat_number': assignment.seat_number
                    })
                else:
                    all_rounds_info.append({
                        'round': r,
                        'table_number': '未分桌',
                        'seat_number': '未分桌'
                    })

            data = {
                'unique_id': player.unique_id,
                'name': player.name,
                'rounds': all_rounds_info
            }
        else:
            flash("没有找到对应选手")
    return render_template('player_query.html', data=data)

# ---------- 排行榜 --------------

# 排行榜展示（前两轮累计，第三轮独立）
@app.route('/scoreboard/<int:round_number>')
def scoreboard(round_number):
    if round_number == 1:
        # 第一轮只看 score_round1
        players = Player.query.all()
        scoreboard_data = []
        for p in players:
            scoreboard_data.append({
                'unique_id': p.unique_id,
                'name': p.name,
                'score': p.score_round1
            })
        # 按第一轮积分降序
        scoreboard_data.sort(key=lambda x: x['score'], reverse=True)
        title = "第一轮积分榜 (仅第一轮)"
        
    elif round_number == 2:
        # 第二轮看前两轮累计 (score_round1 + score_round2)
        players = Player.query.all()
        scoreboard_data = []
        for p in players:
            total_score = p.score_round1 + p.score_round2
            scoreboard_data.append({
                'unique_id': p.unique_id,
                'name': p.name,
                'score': total_score
            })
        scoreboard_data.sort(key=lambda x: x['score'], reverse=True)
        title = "第二轮积分榜 (前两轮累计)"
    elif round_number == 3:
        # 只选出本轮有分桌记录的选手
        players_r3 = (Player.query
                    .join(TableAssignment, Player.id == TableAssignment.player_id)
                    .filter(TableAssignment.round_number == 3)
                    .all())
        # 生成排行榜数据
        scoreboard_data = [{
            'unique_id': p.unique_id,
            'name': p.name,
            'score': p.score_round3
        } for p in players_r3]
        scoreboard_data.sort(key=lambda x: x['score'], reverse=True)
        title = "第三轮积分榜 (仅进入第三轮的选手)"

    else:
        flash("无效轮次")
        return redirect(url_for('index'))
    return render_template('scoreboard.html', 
                           scoreboard=scoreboard_data, 
                           round_number=round_number,
                           title=title)

# ---------- 第三轮淘汰记录 --------------

@app.route('/round3/eliminate', methods=['GET', 'POST'])
def round3_elimination():
    """
    第三轮比赛时，当选手被淘汰后，由裁判录入选手的唯一编号。
    系统记录淘汰顺序，并在页面中显示每次淘汰的选手（编号和姓名）。
    """
    # 1) 必须先判断是否是裁判
    if 'judge' not in session:
        flash("只有裁判可以录入第三轮淘汰，请先登录")
        return redirect(url_for('judge_login'))
    if request.method == 'POST':
        player_unique_id = request.form['player_unique_id']
        player = Player.query.filter_by(unique_id=player_unique_id).first()
        if player:
            # 根据当前淘汰记录计数确定淘汰顺序
            current_count = Elimination.query.count()
            elimination = Elimination(player_id=player.id, elimination_order=current_count + 1)
            db.session.add(elimination)
            db.session.commit()
            flash(f"记录淘汰: {player.unique_id} - {player.name}")
        else:
            flash("找不到该选手")
    eliminations = Elimination.query.order_by(Elimination.elimination_order).all()
    elimination_list = [{
        'order': e.elimination_order, 
        'unique_id': e.player.unique_id, 
        'name': e.player.name
    } for e in eliminations]
    return render_template('round3_elimination.html', eliminations=elimination_list)



# 批量创建裁判账号（示例： judge001~judge020）
@app.route('/create_judges')
def create_judges():
    created = 0
    for i in range(1, 21):
        username = f"judge{str(i).zfill(3)}"
        if not Judge.query.filter_by(username=username).first():
            new_judge = Judge(username=username, password="password123")
            db.session.add(new_judge)
            created += 1
    db.session.commit()
    flash(f"成功创建 {created} 个裁判账号")
    return redirect(url_for('index'))

# 清空部分数据（调试用，可清空选手、分桌、淘汰、录入记录等）
@app.route('/clear_data')
def clear_data():
    TableAssignment.query.delete()
    Player.query.delete()
    Elimination.query.delete()
    JudgeSubmission.query.delete()
    db.session.commit()
    flash("已清空所有选手及相关数据")
    return redirect(url_for('index'))

@app.route('/generate_players_excel', methods=['POST'])
def generate_players_excel():
    try:
        count = int(request.form['count'])
        data = []
        for i in range(count):
            name = f"选手{i+1}"
            phone = f"1{random.randint(3000000000, 9999999999)}"
            data.append({"姓名": name, "手机号": phone})
        df = pd.DataFrame(data)
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)
        output.seek(0)
        return send_file(output, download_name="选手信息.xlsx", as_attachment=True)
    except Exception as e:
        flash("生成失败：" + str(e))
        return redirect(url_for('index'))


if __name__ == "__main__":
    app.run(debug=True)