"""
积分和排名相关API路由
"""
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List, Optional
from backend.database import get_db
from backend.models import Student
from backend.schemas import RankingResponse, RankingItem
from backend.utils.excel import export_scores_to_excel
import os
import tempfile

router = APIRouter(prefix="/api/scores", tags=["scores"])


@router.get("/ranking", response_model=RankingResponse)
def get_ranking(top_n: int = 10, db: Session = Depends(get_db)):
    """获取积分排名"""
    students = (db.query(Student).order_by(Student.total_score.desc()).limit(top_n).all())

    rankings = []
    for rank, student in enumerate(students, 1):
        rankings.append(
            RankingItem(rank=rank,
                        student_id=student.student_id,
                        name=student.name,
                        major=student.major,
                        total_score=student.total_score,
                        random_rollcall_count=student.random_rollcall_count))

    total = db.query(Student).count()

    return RankingResponse(rankings=rankings, total=total)


@router.get("/export")
def export_scores(db: Session = Depends(get_db)):
    """导出积分详单"""
    students = db.query(Student).all()

    # 准备数据
    students_data = []
    for student in students:
        students_data.append({
            '学号': student.student_id,
            '姓名': student.name,
            '专业': student.major or '',
            '随机点名次数': student.random_rollcall_count,
            '总积分': student.total_score
        })

    # 创建临时文件
    with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp_file:
        tmp_path = tmp_file.name

    try:
        # 导出到Excel
        export_scores_to_excel(students_data, tmp_path)

        # 读取文件内容
        with open(tmp_path, 'rb') as f:
            file_content = f.read()

        # 删除临时文件
        os.remove(tmp_path)

        from fastapi.responses import Response
        return Response(content=file_content,
                        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                        headers={"Content-Disposition": "attachment; filename=scores.xlsx"})

    except Exception as e:
        if os.path.exists(tmp_path):
            os.remove(tmp_path)
        raise HTTPException(status_code=500, detail=f"导出失败: {str(e)}")


from sqlalchemy import func


@router.get("/statistics")
def get_statistics(db: Session = Depends(get_db)):
    """获取统计信息"""
    total_students = db.query(Student).count()

    total_rollcall_count = db.query(
        func.sum(Student.random_rollcall_count + Student.order_rollcall_count)).scalar() or 0

    avg_score = db.query(func.avg(Student.total_score)).scalar() or 0.0
    max_score = db.query(func.max(Student.total_score)).scalar() or 0.0
    min_score = db.query(func.min(Student.total_score)).scalar() or 0.0
    return {
        "total_students": total_students,
        "total_rollcall_count": total_rollcall_count,
        "avg_score": round(float(avg_score), 2),
        "max_score": float(max_score),
        "min_score": float(min_score),
    }