models.py

"""資料庫模型定義"""
import os
import re
from threading import RLock
from sqlalchemy import (
    Column,
    Integer,
    String,
    Float,
    DateTime,
    ForeignKey,
    Text,
    UniqueConstraint,
    CheckConstraint,
    create_engine,
    text,
)
from sqlalchemy.orm import relationship, declarative_base, sessionmaker
from datetime import datetime
from fastapi import Query, Request

Base = declarative_base()

class Student(Base):
    """學生模型"""
    __tablename__ = 'students'
    
    id = Column(Integer, primary_key=True)
    student_id = Column(String(50), unique=True, nullable=False)
    name = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    submissions = relationship('Submission', back_populates='student', cascade='all, delete-orphan')

class Submission(Base):
    """學生提交的檔案"""
    __tablename__ = 'submissions'
    
    id = Column(Integer, primary_key=True)
    student_id = Column(Integer, ForeignKey('students.id'), nullable=False, index=True)
    filename = Column(String(255), nullable=False)
    file_hash = Column(String(64), unique=True, nullable=False)  # SHA256雜湊
    file_path = Column(String(500), nullable=False)
    file_size = Column(Integer)
    file_type = Column(String(10))  # pdf, jpg, png, docx, pptx等
    uploaded_at = Column(DateTime, default=datetime.utcnow, index=True)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    student = relationship('Student', back_populates='submissions')
    grades = relationship('Grade', back_populates='submission', cascade='all, delete-orphan', order_by='Grade.graded_at')
    override = relationship('GradeOverride', back_populates='submission', uselist=False, cascade='all, delete-orphan')

class TA(Base):
    """助教模型"""
    __tablename__ = 'tas'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True, nullable=False)
    email = Column(String(100))
    created_at = Column(DateTime, default=datetime.utcnow)
    
    grades = relationship('Grade', back_populates='ta', cascade='all, delete-orphan')

class Professor(Base):
    """教授模型"""
    __tablename__ = 'professors'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True, nullable=False)
    email = Column(String(100))
    created_at = Column(DateTime, default=datetime.utcnow)
    
    overrides = relationship('GradeOverride', back_populates='professor', cascade='all, delete-orphan')

class Grade(Base):
    """評分紀錄"""
    __tablename__ = 'grades'
    
    id = Column(Integer, primary_key=True)
    submission_id = Column(Integer, ForeignKey('submissions.id'), nullable=False, index=True)
    ta_id = Column(Integer, ForeignKey('tas.id'), nullable=False, index=True)
    score = Column(Float)  # 百分制 0-100
    cr1 = Column(Float)    # 指標 1 分數
    cr2 = Column(Float)    # 指標 2 分數
    cr3 = Column(Float)    # 指標 3 分數
    cr4 = Column(Float)    # 指標 4 分數
    comments = Column(Text)
    graded_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    submission = relationship('Submission', back_populates='grades')
    ta = relationship('TA', back_populates='grades')

    # 保證一個檔案只能被一個助教評分一次
    __table_args__ = (
        UniqueConstraint('submission_id', 'ta_id', name='unique_submission_ta'),
        CheckConstraint('score IS NULL OR (score >= 0 AND score <= 100)', name='check_grade_score_range'),
        CheckConstraint('cr1 IS NULL OR (cr1 >= 0 AND cr1 <= 100)', name='check_grade_cr1_range'),
        CheckConstraint('cr2 IS NULL OR (cr2 >= 0 AND cr2 <= 100)', name='check_grade_cr2_range'),
        CheckConstraint('cr3 IS NULL OR (cr3 >= 0 AND cr3 <= 100)', name='check_grade_cr3_range'),
        CheckConstraint('cr4 IS NULL OR (cr4 >= 0 AND cr4 <= 100)', name='check_grade_cr4_range'),
    )

class GradeOverride(Base):
    """教授的成績覆蓋"""
    __tablename__ = 'grade_overrides'
    
    id = Column(Integer, primary_key=True)
    submission_id = Column(Integer, ForeignKey('submissions.id'), nullable=False, index=True)
    professor_id = Column(Integer, ForeignKey('professors.id'), nullable=False)
    final_score = Column(Float, nullable=False)
    reason = Column(Text)
    overridden_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    submission = relationship('Submission', back_populates='override')
    professor = relationship('Professor', back_populates='overrides')

    __table_args__ = (
        UniqueConstraint('submission_id', name='unique_override_submission'),
        CheckConstraint('final_score >= 0 AND final_score <= 100', name='check_override_score_range'),
    )

DB_DIR = "week_dbs"
DEFAULT_WEEK = "week01"

_ENGINE_BY_WEEK = {}
_SESSION_FACTORY_BY_WEEK = {}
_INITIALIZED_WEEKS = set()
_DB_LOCK = RLock()


def normalize_week(raw_week: str | None) -> str:
    value = (raw_week or "").strip().lower()
    if not value:
        return DEFAULT_WEEK

    match = re.fullmatch(r"(?:week)?\s*(\d{1,2})", value)
    if match:
        week_num = int(match.group(1))
        if 1 <= week_num <= 53:
            return f"week{week_num:02d}"

    return DEFAULT_WEEK


def _db_file_path(week: str) -> str:
    os.makedirs(DB_DIR, exist_ok=True)
    return os.path.join(DB_DIR, f"{week}.db")


def list_existing_weeks(include_default: bool = True) -> list[str]:
    os.makedirs(DB_DIR, exist_ok=True)
    weeks = []
    for filename in os.listdir(DB_DIR):
        match = re.fullmatch(r"(week\d{2})\.db", filename)
        if match:
            weeks.append(match.group(1))

    weeks = sorted(set(weeks), key=lambda item: int(item[-2:]))
    if include_default and DEFAULT_WEEK not in weeks:
        weeks.insert(0, DEFAULT_WEEK)
    return weeks


def get_engine_for_week(week: str):
    with _DB_LOCK:
        engine = _ENGINE_BY_WEEK.get(week)
        if engine:
            return engine

        db_url = f"sqlite:///./{_db_file_path(week)}"
        engine = create_engine(db_url, connect_args={"check_same_thread": False})
        _ENGINE_BY_WEEK[week] = engine
        return engine


def _get_session_factory_for_week(week: str):
    with _DB_LOCK:
        factory = _SESSION_FACTORY_BY_WEEK.get(week)
        if factory:
            return factory

        factory = sessionmaker(autocommit=False, autoflush=False, bind=get_engine_for_week(week))
        _SESSION_FACTORY_BY_WEEK[week] = factory
        return factory


def _ensure_schema(engine):
    Base.metadata.create_all(bind=engine)

    # 補上可重複執行的索引建立。
    with engine.begin() as conn:
        conn.execute(text('CREATE INDEX IF NOT EXISTS ix_submissions_student_id ON submissions(student_id)'))
        conn.execute(text('CREATE INDEX IF NOT EXISTS ix_submissions_uploaded_at ON submissions(uploaded_at)'))
        conn.execute(text('CREATE INDEX IF NOT EXISTS ix_grades_submission_id ON grades(submission_id)'))
        conn.execute(text('CREATE INDEX IF NOT EXISTS ix_grades_ta_id ON grades(ta_id)'))
        conn.execute(text('CREATE INDEX IF NOT EXISTS ix_grade_overrides_submission_id ON grade_overrides(submission_id)'))
        conn.execute(text('CREATE UNIQUE INDEX IF NOT EXISTS ux_grade_overrides_submission_id ON grade_overrides(submission_id)'))


def init_db(week: str | None = None):
    normalized_week = normalize_week(week)
    engine = get_engine_for_week(normalized_week)
    _ensure_schema(engine)


def ensure_week_initialized(week: str):
    normalized_week = normalize_week(week)
    if normalized_week in _INITIALIZED_WEEKS:
        return

    with _DB_LOCK:
        if normalized_week in _INITIALIZED_WEEKS:
            return

        init_db(normalized_week)
        session = _get_session_factory_for_week(normalized_week)()
        try:
            # 延遲匯入避免循環依賴
            from services.staff_service import init_staff_for_db

            init_staff_for_db(session, week=normalized_week)
            session.commit()
        finally:
            session.close()

        _INITIALIZED_WEEKS.add(normalized_week)


def get_db(request: Request, week: str | None = Query(default=None)):
    current_week = normalize_week(week or request.query_params.get("week"))
    request.state.current_week = current_week
    ensure_week_initialized(current_week)

    db = _get_session_factory_for_week(current_week)()
    try:
        yield db
    finally:
        db.close()


def open_week_session(week: str):
    current_week = normalize_week(week)
    ensure_week_initialized(current_week)
    return _get_session_factory_for_week(current_week)()