zlqy/models.py

# models.py
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

# 用户表
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=True)
    phone = db.Column(db.String(20), unique=True, nullable=True)
    password = db.Column(db.String(128), nullable=False)
    role = db.Column(db.String(20), default='student')  # admin, teacher, student
    avatar = db.Column(db.String(200), default='')  # 头像URL
    is_banned = db.Column(db.Boolean, default=False)
    completed_exams = db.Column(db.Integer, default=0)  # 已完成并批改的考试次数
    name_changed_at = db.Column(db.DateTime, nullable=True)  # 上次修改用户名时间
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # 关系
    exams_created = db.relationship('Exam', back_populates='creator', lazy=True)
    submissions = db.relationship('Submission', back_populates='user', lazy=True)
    drafts = db.relationship('Draft', back_populates='user', lazy=True)
    posts = db.relationship('Post', back_populates='author', lazy=True)
    replies = db.relationship('Reply', back_populates='author', lazy=True)
    reactions = db.relationship('Reaction', back_populates='user', lazy=True)
    bookmarks = db.relationship('Bookmark', back_populates='user', lazy=True)
    reports = db.relationship('Report', backref='reporter', lazy=True)
    notifications = db.relationship('Notification', backref='user', lazy=True)
    contest_memberships = db.relationship('ContestMembership', back_populates='user', lazy=True)
    contest_registrations = db.relationship('ContestRegistration', backref='user', lazy=True)
    # teacher_applications 将通过 TeacherApplication 的 back_populates 自动创建

# 杯赛成员表（记录用户在特定杯赛中的角色）
class ContestMembership(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=False)
    role = db.Column(db.String(20), nullable=False)  # 'owner' 或 'teacher'
    joined_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', back_populates='contest_memberships')
    contest = db.relationship('Contest', back_populates='members')

    __table_args__ = (db.UniqueConstraint('user_id', 'contest_id', name='uq_user_contest'),)

# 杯赛申请表（用于用户申请举办新杯赛）
class ContestApplication(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    organizer = db.Column(db.String(100))
    description = db.Column(db.Text)
    contact = db.Column(db.String(100))
    status = db.Column(db.String(20), default='pending')  # pending, approved, rejected
    applied_at = db.Column(db.DateTime, default=datetime.utcnow)
    reviewed_at = db.Column(db.DateTime)
    total_score = db.Column(db.Integer, default=150)  # 杯赛默认满分
    start_date = db.Column(db.String(20))  # 申请时填写的开始日期
    end_date = db.Column(db.String(20))    # 申请时填写的结束日期
    # 报备信息
    responsible_person = db.Column(db.String(80))   # 责任人姓名
    responsible_phone = db.Column(db.String(20))    # 责任人电话
    responsible_email = db.Column(db.String(120))   # 责任人邮箱
    organization = db.Column(db.String(100))        # 所属机构/学校

    user = db.relationship('User', backref='contest_applications')

# 杯赛报名表（用户报名参加杯赛）
class ContestRegistration(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=False)
    registered_at = db.Column(db.DateTime, default=datetime.utcnow)

    __table_args__ = (db.UniqueConstraint('user_id', 'contest_id', name='uq_user_contest_reg'),)

# 杯赛表
class Contest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    organizer = db.Column(db.String(100))
    description = db.Column(db.Text)
    start_date = db.Column(db.String(20))
    end_date = db.Column(db.String(20))
    status = db.Column(db.String(20), default='upcoming')
    participants = db.Column(db.Integer, default=0)
    created_by = db.Column(db.Integer, db.ForeignKey('user.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    past_papers = db.Column(db.Text)  # JSON 存储历年真题 [{year, title, file}]
    total_score = db.Column(db.Integer, default=150)  # 杯赛默认考试满分
    visible = db.Column(db.Boolean, default=True)  # 是否对普通用户可见
    # 报备信息
    responsible_person = db.Column(db.String(80))   # 责任人姓名
    responsible_phone = db.Column(db.String(20))    # 责任人电话
    responsible_email = db.Column(db.String(120))   # 责任人邮箱
    organization = db.Column(db.String(100))        # 所属机构/学校

    creator = db.relationship('User', backref='contests_created')
    members = db.relationship('ContestMembership', back_populates='contest', lazy=True, cascade='all, delete-orphan')
    exams = db.relationship('Exam', backref='contest', lazy=True)
    posts = db.relationship('Post', back_populates='contest', lazy=True, cascade='all, delete-orphan')
    teacher_applications = db.relationship('TeacherApplication', back_populates='contest', lazy=True, cascade='all, delete-orphan')

    def get_past_papers(self):
        return json.loads(self.past_papers) if self.past_papers else []

    def set_past_papers(self, papers):
        self.past_papers = json.dumps(papers)

# 题库表（杯赛老师和负责人往题库添加题目，负责人选题组卷）
class QuestionBankItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=False)
    contributor_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    type = db.Column(db.String(20), nullable=False)  # 'choice', 'fill', 'essay'
    content = db.Column(db.Text, nullable=False)      # 题目内容
    options = db.Column(db.Text)                       # JSON，选择题选项
    answer = db.Column(db.Text)                        # 答案
    score = db.Column(db.Integer, default=10)          # 建议分值
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    contest = db.relationship('Contest', backref='question_bank_items')
    contributor = db.relationship('User', backref='contributed_questions')

# 考试表
class Exam(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=True)
    title = db.Column(db.String(200), nullable=False)
    subject = db.Column(db.String(50))
    duration = db.Column(db.Integer)
    total_score = db.Column(db.Integer)
    creator_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    questions = db.Column(db.Text)  # JSON
    encrypted_questions = db.Column(db.Text)  # 加密后的试卷内容
    is_encrypted = db.Column(db.Boolean, default=False)  # 是否加密
    access_password = db.Column(db.String(128))  # 考试访问密码
    scheduled_start = db.Column(db.DateTime, nullable=True)  # 预定开始时间
    scheduled_end = db.Column(db.DateTime, nullable=True)    # 预定结束时间
    score_release_time = db.Column(db.DateTime, nullable=True)  # 成绩公布时间
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    status = db.Column(db.String(20), default='available')

    creator = db.relationship('User', back_populates='exams_created')
    submissions = db.relationship('Submission', back_populates='exam', lazy=True, cascade='all, delete-orphan')
    drafts = db.relationship('Draft', back_populates='exam', lazy=True, cascade='all, delete-orphan')

    def get_questions(self):
        return json.loads(self.questions) if self.questions else []

    def set_questions(self, questions):
        self.questions = json.dumps(questions)

# 提交记录表
class Submission(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey('exam.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    answers = db.Column(db.Text)  # JSON
    score = db.Column(db.Integer, default=0)
    question_scores = db.Column(db.Text)  # JSON
    graded = db.Column(db.Boolean, default=False)
    graded_by = db.Column(db.String(80))
    submitted_at = db.Column(db.DateTime, default=datetime.utcnow)

    exam = db.relationship('Exam', back_populates='submissions')
    user = db.relationship('User', back_populates='submissions')

    def get_answers(self):
        return json.loads(self.answers) if self.answers else {}

    def set_answers(self, answers):
        self.answers = json.dumps(answers)

    def get_question_scores(self):
        return json.loads(self.question_scores) if self.question_scores else {}

    def set_question_scores(self, scores):
        self.question_scores = json.dumps(scores)

# 草稿表
class Draft(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey('exam.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    answers = db.Column(db.Text)  # JSON
    saved_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    exam = db.relationship('Exam', back_populates='drafts')
    user = db.relationship('User', back_populates='drafts')

    def get_answers(self):
        return json.loads(self.answers) if self.answers else {}

    def set_answers(self, answers):
        self.answers = json.dumps(answers)

# 论坛帖子表
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    tag = db.Column(db.String(50), default='全部')
    is_official = db.Column(db.Boolean, default=False)
    pinned = db.Column(db.Boolean, default=False)
    likes = db.Column(db.Integer, default=0)
    replies_count = db.Column(db.Integer, default=0)
    views = db.Column(db.Integer, default=0)
    has_poll = db.Column(db.Boolean, default=False)
    images = db.Column(db.Text)  # JSON
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    author = db.relationship('User', back_populates='posts')
    replies = db.relationship('Reply', back_populates='post', lazy=True, cascade='all, delete-orphan')
    poll = db.relationship('Poll', back_populates='post', uselist=False, cascade='all, delete-orphan')
    reactions = db.relationship('Reaction', back_populates='post', lazy=True, cascade='all, delete-orphan')
    bookmarks = db.relationship('Bookmark', back_populates='post', lazy=True, cascade='all, delete-orphan')
    contest = db.relationship('Contest', back_populates='posts')

    def get_images(self):
        return json.loads(self.images) if self.images else []

    def set_images(self, images):
        self.images = json.dumps(images)

# 回复表
class Reply(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    content = db.Column(db.Text, nullable=False)
    likes = db.Column(db.Integer, default=0)
    reply_to = db.Column(db.String(80))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    post = db.relationship('Post', back_populates='replies')
    author = db.relationship('User', back_populates='replies')
    reactions = db.relationship('Reaction', back_populates='reply', lazy=True, cascade='all, delete-orphan')

# 投票表
class Poll(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
    question = db.Column(db.String(200), nullable=False)
    options = db.Column(db.Text)  # JSON
    voters = db.Column(db.Text)   # JSON
    multi = db.Column(db.Boolean, default=False)
    total_votes = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    post = db.relationship('Post', back_populates='poll')

    def get_options(self):
        return json.loads(self.options) if self.options else []

    def set_options(self, options):
        self.options = json.dumps(options)

    def get_voters(self):
        return json.loads(self.voters) if self.voters else {}

    def set_voters(self, voters):
        self.voters = json.dumps(voters)

# 表情反应表
class Reaction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=True)
    reply_id = db.Column(db.Integer, db.ForeignKey('reply.id'), nullable=True)
    reaction = db.Column(db.String(20))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', back_populates='reactions')
    post = db.relationship('Post', back_populates='reactions')
    reply = db.relationship('Reply', back_populates='reactions')

    __table_args__ = (
        db.UniqueConstraint('user_id', 'post_id', name='uq_user_post_reaction'),
        db.UniqueConstraint('user_id', 'reply_id', name='uq_user_reply_reaction'),
    )

# 收藏表
class Bookmark(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', back_populates='bookmarks')
    post = db.relationship('Post', back_populates='bookmarks')

    __table_args__ = (db.UniqueConstraint('user_id', 'post_id', name='uq_user_post_bookmark'),)

# 举报表
class Report(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(20))
    target_id = db.Column(db.Integer)
    reporter_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    reason = db.Column(db.String(100))
    detail = db.Column(db.Text)
    status = db.Column(db.String(20), default='pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # reporter 已在 User 中通过 backref 定义

# 通知表
class Notification(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    type = db.Column(db.String(50))
    content = db.Column(db.Text)
    from_user = db.Column(db.String(80))
    post_id = db.Column(db.Integer, nullable=True)
    read = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # user 已在 User 中通过 backref 定义

# 系统公告表（管理员发布的全站通知）
class SystemNotification(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    pinned = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    author = db.relationship('User', backref='system_notifications')

# 编辑历史表（可选）
class EditHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
    title = db.Column(db.String(200))
    content = db.Column(db.Text)
    edited_by = db.Column(db.String(80))
    edited_at = db.Column(db.DateTime, default=datetime.utcnow)

    post = db.relationship('Post')

# 教师申请表（针对特定杯赛）
class TeacherApplication(db.Model):
    """教师申请记录（针对特定杯赛）"""
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), nullable=False)
    name = db.Column(db.String(80), nullable=False)          # 申请人姓名
    email = db.Column(db.String(120), nullable=False)       # 申请人邮箱
    reason = db.Column(db.Text, nullable=False)             # 申请理由
    status = db.Column(db.String(20), default='pending')    # pending, approved, rejected
    applied_at = db.Column(db.DateTime, default=datetime.utcnow)
    reviewed_at = db.Column(db.DateTime)
    reviewed_by = db.Column(db.Integer, db.ForeignKey('user.id'))

    # 关系
    user = db.relationship('User', foreign_keys=[user_id], backref='teacher_applications')
    contest = db.relationship('Contest', back_populates='teacher_applications')
    reviewer = db.relationship('User', foreign_keys=[reviewed_by])

    __table_args__ = (db.UniqueConstraint('user_id', 'contest_id', name='uq_user_contest_application'),)

# 好友表
class Friend(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    friend_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    status = db.Column(db.String(20), default='pending')  # pending, accepted
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', foreign_keys=[user_id], backref=db.backref('friends_initiated', lazy='dynamic'))
    friend = db.relationship('User', foreign_keys=[friend_id], backref=db.backref('friends_received', lazy='dynamic'))

# 试卷收藏表
class ExamBookmark(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    exam_id = db.Column(db.Integer, db.ForeignKey('exam.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', backref=db.backref('exam_bookmarks', lazy='dynamic', cascade='all, delete-orphan'))
    exam = db.relationship('Exam', backref=db.backref('bookmarked_by', lazy='dynamic', cascade='all, delete-orphan'))

    __table_args__ = (db.UniqueConstraint('user_id', 'exam_id', name='uq_user_exam_bookmark'),)

# 聊天室表
class ChatRoom(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(20), nullable=False)  # 'private' | 'group' | 'contest'
    name = db.Column(db.String(100), nullable=True)
    avatar = db.Column(db.String(200), default='')
    creator_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
    contest_id = db.Column(db.Integer, db.ForeignKey('contest.id'), unique=True, nullable=True)
    announcement = db.Column(db.Text, default='')
    announcement_by = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
    announcement_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    creator = db.relationship('User', foreign_keys=[creator_id], backref='chatrooms_created')
    announcement_user = db.relationship('User', foreign_keys=[announcement_by])
    contest = db.relationship('Contest', backref=db.backref('chatroom', uselist=False))
    members = db.relationship('ChatRoomMember', backref='room', lazy=True, cascade='all, delete-orphan')
    messages = db.relationship('Message', backref='room', lazy=True, cascade='all, delete-orphan')

# 聊天室成员表
class ChatRoomMember(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    room_id = db.Column(db.Integer, db.ForeignKey('chat_room.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    role = db.Column(db.String(20), default='member')  # 'admin' | 'member'
    nickname = db.Column(db.String(50), default='')
    muted = db.Column(db.Boolean, default=False)
    last_read_at = db.Column(db.DateTime, default=datetime.utcnow)
    joined_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', backref='chat_memberships')

    __table_args__ = (db.UniqueConstraint('room_id', 'user_id', name='uq_room_user'),)

# 消息表
class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    room_id = db.Column(db.Integer, db.ForeignKey('chat_room.id'), nullable=False)
    sender_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    type = db.Column(db.String(20), default='text')  # 'text' | 'image' | 'file' | 'system' | 'voice'
    content = db.Column(db.Text, default='')
    file_url = db.Column(db.String(300), nullable=True)
    file_name = db.Column(db.String(200), nullable=True)
    recalled = db.Column(db.Boolean, default=False)
    reply_to_id = db.Column(db.Integer, db.ForeignKey('message.id'), nullable=True)
    mentions = db.Column(db.Text, default='')  # JSON array of user IDs, e.g. "[1,2,3]" or "all"
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    sender = db.relationship('User', backref='messages_sent')
    reply_to = db.relationship('Message', remote_side='Message.id', backref='replies')
    reactions = db.relationship('MessageReaction', backref='message', lazy=True, cascade='all, delete-orphan')

# 消息表情回应表
class MessageReaction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    message_id = db.Column(db.Integer, db.ForeignKey('message.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    emoji = db.Column(db.String(10), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', backref='message_reactions')

    __table_args__ = (db.UniqueConstraint('message_id', 'user_id', 'emoji', name='uq_msg_user_emoji'),)

# 邀请码表（教师申请审批通过后生成，老师输入邀请码激活身份）
class InviteCode(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(32), unique=True, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    application_id = db.Column(db.Integer, db.ForeignKey('teacher_application.id'), nullable=False)
    used = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    used_at = db.Column(db.DateTime, nullable=True)

    user = db.relationship('User', backref='invite_codes')
    application = db.relationship('TeacherApplication', backref='invite_code')