from datetime import datetime

from sqlalchemy import (
    BigInteger,
    Boolean,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


class AppSettings(Base):
    __tablename__ = "app_settings"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    system_name: Mapped[str] = mapped_column(String(128), default="考试阅卷系统")
    logo_ext: Mapped[str] = mapped_column(String(16), default="")


class LoginLock(Base):
    __tablename__ = "login_lock"

    username: Mapped[str] = mapped_column(String(64), primary_key=True)
    fail_count: Mapped[int] = mapped_column(Integer, default=0)
    locked_until: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)


class GraderSubject(Base):
    __tablename__ = "grader_subject"

    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True)
    subject_name: Mapped[str] = mapped_column(String(256), primary_key=True)


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    password_hash: Mapped[str] = mapped_column(String(255))
    display_name: Mapped[str] = mapped_column(String(128), default="")
    role: Mapped[str] = mapped_column(String(16), default="grader", index=True)  # admin | grader
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)


class ImportBatch(Base):
    __tablename__ = "import_batch"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    subject_name: Mapped[str] = mapped_column(String(256), index=True)
    zip_filename: Mapped[str] = mapped_column(String(512), default="")
    sha256: Mapped[str] = mapped_column(String(64), default="")
    status: Mapped[str] = mapped_column(String(32), default="success")
    report_json: Mapped[str] = mapped_column(Text, default="{}")
    imported_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    imported_by: Mapped[int | None] = mapped_column(Integer, ForeignKey("users.id"), nullable=True)
    paper_source_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    exam_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True, index=True)
    exam_ids_json: Mapped[str] = mapped_column(Text, default="[]")
    external_paper_id: Mapped[str] = mapped_column(String(64), default="")
    revision: Mapped[str] = mapped_column(String(32), default="")
    occupation: Mapped[str] = mapped_column(String(128), default="")
    level: Mapped[str] = mapped_column(String(64), default="")
    subject_code: Mapped[int | None] = mapped_column(Integer, nullable=True)


class ExamPaperMap(Base):
    __tablename__ = "exam_paper_map"
    __table_args__ = (UniqueConstraint("exam_id", name="uq_exam_paper_map_exam_id"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    exam_id: Mapped[int] = mapped_column(BigInteger, index=True)
    paper_source_id: Mapped[int] = mapped_column(BigInteger)
    import_batch_id: Mapped[int] = mapped_column(Integer, ForeignKey("import_batch.id"))


class ExaminationExaminee(Base):
    __tablename__ = "examination_examinee"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    username: Mapped[str | None] = mapped_column(String(64), nullable=True)
    nickname: Mapped[str | None] = mapped_column(String(128), nullable=True)
    id_card: Mapped[str | None] = mapped_column(String(32), nullable=True, index=True)
    mobile: Mapped[str | None] = mapped_column(String(32), nullable=True)
    sex: Mapped[int | None] = mapped_column(Integer, nullable=True)
    deleted: Mapped[int] = mapped_column(Integer, default=0)


class ExaminationPaperSource(Base):
    __tablename__ = "examination_paper_source"
    __table_args__ = (
        UniqueConstraint("external_paper_id", "revision", name="uq_paper_ext_rev"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    external_paper_id: Mapped[str] = mapped_column(String(64))
    revision: Mapped[str] = mapped_column(String(32))
    sha256: Mapped[str | None] = mapped_column(String(128), nullable=True)
    paper_name: Mapped[str | None] = mapped_column(String(512), nullable=True)
    occupation: Mapped[str | None] = mapped_column(String(128), nullable=True)
    level: Mapped[str | None] = mapped_column(String(64), nullable=True)
    subject: Mapped[int | None] = mapped_column(Integer, nullable=True)
    struct_json: Mapped[str | None] = mapped_column(Text, nullable=True)
    item_count: Mapped[int | None] = mapped_column(Integer, nullable=True)
    import_batch_id: Mapped[int | None] = mapped_column(Integer, ForeignKey("import_batch.id"))
    total_score: Mapped[float | None] = mapped_column(Float, nullable=True)


class ExaminationPaperItem(Base):
    __tablename__ = "examination_paper_item"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    paper_source_id: Mapped[int] = mapped_column(BigInteger, index=True)
    external_item_id: Mapped[str] = mapped_column(String(64), index=True)
    section_ref: Mapped[str | None] = mapped_column(String(16), nullable=True)
    part_ref: Mapped[str | None] = mapped_column(String(16), nullable=True)
    part_name: Mapped[str | None] = mapped_column(String(64), nullable=True)
    item_type: Mapped[int | None] = mapped_column(Integer, nullable=True)
    item_order: Mapped[int | None] = mapped_column(Integer, nullable=True)
    title_html: Mapped[str | None] = mapped_column(Text, nullable=True)
    detail_json: Mapped[str | None] = mapped_column(Text, nullable=True)
    max_score: Mapped[float | None] = mapped_column(Float, nullable=True)
    reference_answer: Mapped[str | None] = mapped_column(String(64), nullable=True)
    answer_rules: Mapped[str | None] = mapped_column(Text, nullable=True)


class ExaminationExamExaminee(Base):
    __tablename__ = "examination_exam_examinee"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    exam_id: Mapped[int] = mapped_column(BigInteger, index=True)
    examinee_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("examination_examinee.id"))
    room_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    seat_number: Mapped[str | None] = mapped_column(String(32), nullable=True)
    status: Mapped[int | None] = mapped_column(Integer, nullable=True)
    score: Mapped[float] = mapped_column(Float, default=0)
    imported_score: Mapped[float | None] = mapped_column(Float, nullable=True)
    level_str: Mapped[str | None] = mapped_column(String(16), nullable=True)
    start_time: Mapped[str | None] = mapped_column(String(32), nullable=True)
    end_time: Mapped[str | None] = mapped_column(String(32), nullable=True)
    subject: Mapped[int | None] = mapped_column(Integer, nullable=True)
    import_batch_id: Mapped[int] = mapped_column(Integer, ForeignKey("import_batch.id"), index=True)


class ExaminationExamineeExamAnswer(Base):
    __tablename__ = "examination_examinee_exam_answer"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    exam_examinee_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("examination_exam_examinee.id"), unique=True
    )
    answers: Mapped[str] = mapped_column(Text)
    deleted: Mapped[int] = mapped_column(Integer, default=0)


class ExaminationExamineeExamAnswerItem(Base):
    __tablename__ = "examination_examinee_exam_answer_item"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    exam_examinee_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("examination_exam_examinee.id"), index=True
    )
    external_item_id: Mapped[str] = mapped_column(String(64))
    answer_raw: Mapped[str] = mapped_column(String(256), default="")
    is_correct: Mapped[bool] = mapped_column(Boolean, default=False)
    score: Mapped[float] = mapped_column(Float, default=0)