# -*- coding: utf-8 -*-
"""数据模型：12 张核心表。SQLAlchemy ORM。
角色: super(超管) / admin(中台) / staff(店员)
"""
import datetime
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import UserMixin

from app.extensions import db


# -------------------- 组织与人员 --------------------

class Chain(db.Model):
    """连锁（益丰/老百姓/单体...）"""
    __tablename__ = "chain"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False, index=True)
    logo = db.Column(db.String(255))
    pinned = db.Column(db.Boolean, default=False)            # H5/后台下拉置顶
    pinned_at = db.Column(db.DateTime)                        # 置顶时间（最后置顶的排最前）
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    stores = db.relationship("Store", backref="chain", lazy="dynamic")


class Store(db.Model):
    """门店"""
    __tablename__ = "store"
    id = db.Column(db.Integer, primary_key=True)
    chain_id = db.Column(db.Integer, db.ForeignKey("chain.id"))
    name = db.Column(db.String(120), nullable=False, index=True)
    region = db.Column(db.String(80))           # 区域
    province = db.Column(db.String(60))         # 省份
    city = db.Column(db.String(60))             # 城市
    address = db.Column(db.String(255))
    code = db.Column(db.String(60), index=True)  # 门店编码
    status = db.Column(db.String(20), default="active")  # active/disabled
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    staff = db.relationship("Staff", backref="store", lazy="dynamic")


class Staff(UserMixin, db.Model):
    """用户（店员 + 管理员同表，role 区分）"""
    __tablename__ = "staff"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), nullable=False)
    phone = db.Column(db.String(20), nullable=False, index=True)  # 不唯一：同一手机号可同时是后台管理员+前台店员（role 区分）
    password_hash = db.Column(db.String(255), nullable=False, default="")  # 管理员必有；店员可空(待激活)
    store_id = db.Column(db.Integer, db.ForeignKey("store.id"))  # 管理员可为空
    role = db.Column(db.String(20), nullable=False, default="staff")  # super/admin/staff
    points_balance = db.Column(db.Integer, default=0)
    status = db.Column(db.String(20), default="active")  # active/disabled
    security_question = db.Column(db.String(255))   # 密码保护问题
    security_answer = db.Column(db.String(255))     # 答案 hash
    avatar = db.Column(db.String(255))              # 头像 URL（/uploads/avatar/xxx）
    province = db.Column(db.String(60))             # 省份（注册/后台编辑维护）
    city = db.Column(db.String(60))                 # 城市
    position = db.Column(db.String(60))             # 职务（店员/店长/运营/采销...）
    last_login = db.Column(db.DateTime)             # 最后登录时间（H5 登录时记录；历史为空）
    deleted = db.Column(db.Boolean, default=False)  # 软删除标记（超管删除置 True，列表隐藏，数据保留）
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    def set_password(self, raw):
        self.password_hash = generate_password_hash(raw)

    def check_password(self, raw):
        if not self.password_hash:
            return False
        return check_password_hash(self.password_hash, raw)

    def set_security_answer(self, raw):
        """密码保护问题答案：normalize(去空格小写) 后 hash 存储。"""
        self.security_answer = generate_password_hash((raw or "").strip().lower())

    def check_security_answer(self, raw):
        if not self.security_answer:
            return False
        return check_password_hash(self.security_answer, (raw or "").strip().lower())

    @property
    def is_admin(self):
        return self.role in ("super", "admin")


# -------------------- 题库与考试 --------------------

class Question(db.Model):
    """题库（字段模型沿用 zhishi-game/quiz_questions.xls）。
    qtype: 单选/判断/多选/配对/分类。游戏题型映射（V2 复用本表，零独立表）：
      配对：opt_a=左、opt_b=右、stem=说明、answer="PAIR"（翻牌/连连看/拼图/消消乐/合成复用；消消乐/合成的 opt_b 兼作同组键或级标签）；
      判断：stem=陈述、answer=T/F；
      分类：opt_a=正确分类、opt_b/c=错误分类、answer="A"；
      单选/图片：opt_a~d=选项、answer=A/B/C/D；图片题额外用 image_url 配图。
    """
    __tablename__ = "question"
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(60), default="通用")   # 产品/通用
    indication = db.Column(db.String(120))                # 适应症（如：社区获得性肺炎）
    qtype = db.Column(db.String(20), default="单选")       # 单选/判断/多选/配对/分类
    stem = db.Column(db.Text, nullable=False)
    opt_a = db.Column(db.String(500))
    opt_b = db.Column(db.String(500))
    opt_c = db.Column(db.String(500))
    opt_d = db.Column(db.String(500))
    answer = db.Column(db.String(10), nullable=False)      # A/B/C/D 或 T/F（分类题正确分类固定 A）
    image_url = db.Column(db.String(255))                  # 图片题配图（/uploads/...），单选/分类等可选用
    explanation = db.Column(db.Text)
    points = db.Column(db.Integer, default=10)
    difficulty = db.Column(db.Integer, default=1)
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)


class Exam(db.Model):
    """考试/活动"""
    __tablename__ = "exam"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    description = db.Column(db.Text)
    pass_score = db.Column(db.Integer, default=60)
    max_attempts = db.Column(db.Integer, default=1)
    time_limit = db.Column(db.Integer, default=0)          # 秒，0=不限
    points_reward = db.Column(db.Integer, default=0)       # 通过奖励积分
    start_at = db.Column(db.DateTime)
    end_at = db.Column(db.DateTime)
    status = db.Column(db.String(20), default="draft")     # draft/open/closed
    mode = db.Column(db.String(20), default="quiz")        # quiz 普通答题 / game 游戏闯关
    scope_mode = db.Column(db.String(20), default="all")   # all 全员 / chain 按连锁 / staff 指定人员
    allowed_chains = db.Column(db.Text)                    # scope_mode=chain 时的连锁 id JSON 列表
    deleted = db.Column(db.Boolean, default=False)         # 软删除/隐藏（有考试记录时改隐藏，列表不显示）
    game_config = db.Column(db.Text)                       # 游戏模式配置 JSON，如 {"round_count":4,"round_codes":["match","judge","runner","mole"]}
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    questions = db.relationship("ExamQuestion", backref="exam",
                                cascade="all, delete-orphan", lazy="dynamic")


class ExamQuestion(db.Model):
    """考试-题目关联（带顺序）"""
    __tablename__ = "exam_question"
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey("exam.id"), nullable=False, index=True)
    question_id = db.Column(db.Integer, db.ForeignKey("question.id"), nullable=False, index=True)
    order = db.Column(db.Integer, default=0)
    round = db.Column(db.Integer, nullable=True)   # 游戏模式：题目归属关卡序号（1~N）；普通考试为 None
    game_code = db.Column(db.String(20))           # 该关游戏形式（match/judge/runner/mole/link/balloon/rapid/classify/puzzle/shoot/match3/merge），与 round 并存，解绑题型↔关卡

    question = db.relationship("Question")


class ExamParticipant(db.Model):
    """考试/游戏的指定参与人员（scope_mode='staff' 时生效）。"""
    __tablename__ = "exam_participant"
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey("exam.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    staff = db.relationship("Staff")
    __table_args__ = (db.UniqueConstraint("exam_id", "staff_id", name="uq_exam_staff"),)


class ExamRecord(db.Model):
    """考试记录"""
    __tablename__ = "exam_record"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    exam_id = db.Column(db.Integer, db.ForeignKey("exam.id"), nullable=False, index=True)
    score = db.Column(db.Integer, default=0)
    passed = db.Column(db.Boolean, default=False)
    points_earned = db.Column(db.Integer, default=0)
    rounds = db.Column(db.Text)        # 各题作答 JSON
    duration = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    staff = db.relationship("Staff")
    exam = db.relationship("Exam")


# -------------------- 积分 --------------------

class PointsLog(db.Model):
    """积分流水（所有积分变动全程留痕）"""
    __tablename__ = "points_log"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    delta = db.Column(db.Integer, nullable=False)          # +/- 正负
    type = db.Column(db.String(20), nullable=False)        # exam/like/exchange/adjust
    ref_id = db.Column(db.Integer)                         # 关联业务 id
    remark = db.Column(db.String(255))
    balance_after = db.Column(db.Integer, nullable=False)  # 变动后余额（审计）
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    staff = db.relationship("Staff")


# -------------------- 朋友圈集赞 --------------------

class LikeTask(db.Model):
    """朋友圈集赞任务"""
    __tablename__ = "like_task"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    content = db.Column(db.Text)                           # 宣传文案/要求
    image_url = db.Column(db.String(255))                  # 宣传配图
    reward_rules = db.Column(db.Text)                      # 阶梯 JSON，如 {"20":50,"50":100}
    deadline = db.Column(db.DateTime)
    status = db.Column(db.String(20), default="open")      # open/closed
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    submissions = db.relationship("LikeSubmission", backref="task", lazy="dynamic")


class LikeSubmission(db.Model):
    """集赞提交（店员上传截图，中台审核）"""
    __tablename__ = "like_submission"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    task_id = db.Column(db.Integer, db.ForeignKey("like_task.id"), nullable=False, index=True)
    screenshot_url = db.Column(db.String(255), nullable=False)
    claimed_likes = db.Column(db.Integer, default=0)       # 店员自报赞数
    status = db.Column(db.String(20), default="pending")   # pending/approved/rejected
    reviewer_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    reviewed_at = db.Column(db.DateTime)
    points_earned = db.Column(db.Integer, default=0)
    reject_reason = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    staff = db.relationship("Staff", foreign_keys=[staff_id])
    reviewer = db.relationship("Staff", foreign_keys=[reviewer_id])


# -------------------- 积分商城 --------------------

class Product(db.Model):
    """商城商品"""
    __tablename__ = "product"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), nullable=False)
    image_url = db.Column(db.String(255))
    points_cost = db.Column(db.Integer, nullable=False, default=0)
    stock = db.Column(db.Integer, default=0)
    category = db.Column(db.String(60))
    description = db.Column(db.Text)
    status = db.Column(db.String(20), default="on")        # on/off
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)


class ExchangeOrder(db.Model):
    """兑换订单"""
    __tablename__ = "exchange_order"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    product_id = db.Column(db.Integer, db.ForeignKey("product.id"), nullable=False)
    points_cost = db.Column(db.Integer, nullable=False)
    status = db.Column(db.String(20), default="pending")   # pending/shipped/done/canceled
    address = db.Column(db.String(255))
    receiver = db.Column(db.String(60))
    receiver_phone = db.Column(db.String(20))
    tracking_no = db.Column(db.String(120))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    shipped_at = db.Column(db.DateTime)

    staff = db.relationship("Staff")
    product = db.relationship("Product")


# -------------------- 文章发布 --------------------

class Article(db.Model):
    """文章资讯（后台发布，H5 首页展示最新若干条，点开看详情）。"""
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    cover = db.Column(db.String(255))              # 封面图 URL（/uploads/article/xxx 或外链）
    summary = db.Column(db.String(500))            # 摘要（可选）
    content = db.Column(db.Text)                    # 正文（纯文本，按换行渲染）
    status = db.Column(db.String(20), default="published")   # draft/published
    sort = db.Column(db.Integer, default=0)        # 排序权重，大在前；同值按时间倒序
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)


class ArticleRead(db.Model):
    """店员已读资讯记录（资讯 New 标记按阅读状态：未读=NEW，点进详情即标记已读）。"""
    __tablename__ = "article_read"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    article_id = db.Column(db.Integer, db.ForeignKey("article.id"), nullable=False, index=True)
    read_at = db.Column(db.DateTime, default=datetime.datetime.now)
    __table_args__ = (db.UniqueConstraint("staff_id", "article_id", name="uq_staff_article"),)


# -------------------- 发票上传 --------------------

class Invoice(db.Model):
    """店员上传的高铁电子发票（游学活动报销用）。一人可多条。"""
    __tablename__ = "invoice"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    filename = db.Column(db.String(255))
    file_url = db.Column(db.String(255), nullable=False)   # /uploads/invoice/xxx
    status = db.Column(db.String(20), default="uploaded")  # uploaded/verified/rejected
    remark = db.Column(db.String(255))
    downloaded = db.Column(db.Boolean, default=False)        # 后台是否已下载过
    downloaded_at = db.Column(db.DateTime)                   # 最近一次后台下载时间
    downloaded_by = db.Column(db.Integer)                    # 下载操作的中台管理员 id（staff.id）
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    staff = db.relationship("Staff")


# -------------------- 意见反馈 --------------------

class Feedback(db.Model):
    """店员意见反馈（客服可在后台回复）。"""
    __tablename__ = "feedback"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    title = db.Column(db.String(20), nullable=False)        # 标题 ≤20 字
    content = db.Column(db.Text, nullable=False)            # 具体意见
    screenshot_url = db.Column(db.String(255))              # 附件截图 /uploads/feedback/xxx
    reply = db.Column(db.Text)                              # 客服回复
    replied_at = db.Column(db.DateTime)                     # 回复时间（自动）
    status = db.Column(db.String(20), default="pending")    # pending/replied
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff")


# -------------------- 门店运营月报 --------------------

class StoreReport(db.Model):
    """门店运营月报：店员每月上传陈列照/库存截图/销量截图，中台审核通过发 100 积分。
    每人自然月 1 次（staff_id+period 唯一约束）。"""
    __tablename__ = "store_report"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    store_id = db.Column(db.Integer, db.ForeignKey("store.id"), index=True)
    display_img = db.Column(db.String(255), nullable=False)   # 可乐必妥陈列照 /uploads/store_report/
    stock_img = db.Column(db.String(255), nullable=False)     # 门店库存量截图
    sales_img = db.Column(db.String(255), nullable=False)     # 门店销量截图
    period = db.Column(db.String(7), nullable=False)          # YYYY-MM 自然月（配合唯一约束限每月1次）
    status = db.Column(db.String(20), default="pending")      # pending/approved/rejected
    points_earned = db.Column(db.Integer, default=0)
    reviewer_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    reviewed_at = db.Column(db.DateTime)
    reject_reason = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff", foreign_keys=[staff_id])
    __table_args__ = (db.UniqueConstraint("staff_id", "period", name="uq_staff_report_period"),)


# -------------------- 案例投稿 --------------------

class CaseSubmission(db.Model):
    """案例投稿：店员提交用药服务案例，中台审核通过发 50 积分。不限频。"""
    __tablename__ = "case_submission"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    store_id = db.Column(db.Integer, db.ForeignKey("store.id"), index=True)
    disease = db.Column(db.String(20), nullable=False)         # 泌尿感染/肠道感染/呼吸道感染/其他
    customer_age = db.Column(db.Integer)                       # 顾客年龄
    customer_gender = db.Column(db.String(4))                  # 男/女
    recommend_reason = db.Column(db.String(255), nullable=False)   # 推荐原因 ≤100字
    service_process = db.Column(db.String(500), nullable=False)    # 服务过程 ≤200字
    customer_feedback = db.Column(db.Text)                     # 顾客反馈（选填）
    status = db.Column(db.String(20), default="pending")       # pending/approved/rejected
    points_earned = db.Column(db.Integer, default=0)
    reviewer_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    reviewed_at = db.Column(db.DateTime)
    reject_reason = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff", foreign_keys=[staff_id])


# -------------------- 首页未读红点（各频道最后查看时间）--------------------

class StaffSeen(db.Model):
    """店员各频道最后查看时间，用于首页图标未读红点（exams/likes/feedback）。"""
    __tablename__ = "staff_seen"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    channel = db.Column(db.String(20), nullable=False)
    seen_at = db.Column(db.DateTime, default=datetime.datetime.now)
    __table_args__ = (db.UniqueConstraint("staff_id", "channel", name="uq_staff_channel"),)


# -------------------- 每日打卡 --------------------

class Checkin(db.Model):
    """每日打卡：一天一条；streak=打卡后连续天数；points_earned=本次奖励。"""
    __tablename__ = "checkin"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    checkin_date = db.Column(db.Date, nullable=False)
    streak = db.Column(db.Integer, default=1)
    points_earned = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    ip = db.Column(db.String(64))                       # 打卡客户端 IP（历史记录为空）
    staff = db.relationship("Staff")
    __table_args__ = (db.UniqueConstraint("staff_id", "checkin_date", name="uq_staff_checkin_date"),)


# -------------------- 通用配置 --------------------

class Setting(db.Model):
    """通用配置（key-value）。如 checkin_reward=每日连续打卡奖励积分。"""
    __tablename__ = "setting"
    key = db.Column(db.String(60), primary_key=True)
    value = db.Column(db.String(255))


# -------------------- 审计：操作日志 --------------------

class OperationLog(db.Model):
    """中台操作日志：积分调整/审核/发货/重置密码/启停/删除等敏感动作留痕。"""
    __tablename__ = "operation_log"
    id = db.Column(db.Integer, primary_key=True)
    admin_id = db.Column(db.Integer, db.ForeignKey("staff.id"), index=True)  # 操作人(中台)
    action = db.Column(db.String(40), nullable=False)   # points_adjust/like_approve/order_ship/...
    target_type = db.Column(db.String(40))              # staff/order/product/exam/...
    target_id = db.Column(db.Integer)
    summary = db.Column(db.String(500))                 # 人话描述
    ip = db.Column(db.String(64))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)

    admin = db.relationship("Staff", foreign_keys=[admin_id])


# -------------------- 首页入口可见范围（销售看板/文件下载 等图标）--------------------

class HomeFeature(db.Model):
    """首页图标功能的可见范围配置（销售看板 salesboard / 文件下载 downloads）。
    scope_mode/allowed_chains + HomeFeatureParticipant 同考试范围模式；无权店员看不到图标及其整行。"""
    __tablename__ = "home_feature"
    id = db.Column(db.Integer, primary_key=True)
    feature_key = db.Column(db.String(40), nullable=False, unique=True)  # salesboard / downloads
    name = db.Column(db.String(60), nullable=False)                       # 显示名
    scope_mode = db.Column(db.String(20), default="all")                  # all/chain/staff
    allowed_chains = db.Column(db.Text)                                   # scope_mode=chain 时的连锁 id JSON 列表
    updated_at = db.Column(db.DateTime, default=datetime.datetime.now)


class HomeFeatureParticipant(db.Model):
    """首页图标功能的指定可见人员（scope_mode='staff' 时生效）。"""
    __tablename__ = "home_feature_participant"
    id = db.Column(db.Integer, primary_key=True)
    feature_id = db.Column(db.Integer, db.ForeignKey("home_feature.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    staff = db.relationship("Staff")
    __table_args__ = (db.UniqueConstraint("feature_id", "staff_id", name="uq_feature_staff"),)


# -------------------- 文件下载（4 分类，各自配可见范围）--------------------

class DownloadCategory(db.Model):
    """文件下载分类（KA连锁/中小连锁/单体药店/C端平台），各自独立配可见范围。"""
    __tablename__ = "download_category"
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(20), nullable=False, unique=True)   # ka/medium/single/cend
    name = db.Column(db.String(60), nullable=False)
    sort = db.Column(db.Integer, default=0)                       # 大在前
    scope_mode = db.Column(db.String(20), default="all")          # all/chain/staff
    allowed_chains = db.Column(db.Text)                           # scope_mode=chain 时的连锁 id JSON 列表

    files = db.relationship("DownloadFile", backref="category",
                            cascade="all, delete-orphan", lazy="dynamic")


class DownloadCategoryParticipant(db.Model):
    """文件下载分类的指定可见人员（scope_mode='staff' 时生效）。"""
    __tablename__ = "download_category_participant"
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey("download_category.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    staff = db.relationship("Staff")
    __table_args__ = (db.UniqueConstraint("category_id", "staff_id", name="uq_cat_staff"),)


class DownloadFile(db.Model):
    """文件下载条目（可见范围继承所属分类 DownloadCategory；非指定人员下载返回 403）。"""
    __tablename__ = "download_file"
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey("download_category.id"), nullable=False, index=True)
    filename = db.Column(db.String(255), nullable=False)          # 展示文件名（保留原名）
    file_url = db.Column(db.String(255), nullable=False)          # /uploads/downloads/uuid.ext
    size = db.Column(db.Integer, default=0)                       # 字节
    uploader_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    chain_id = db.Column(db.Integer, db.ForeignKey("chain.id"), index=True)  # KA连锁文件的归属连锁
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    pinned = db.Column(db.Boolean, default=False)            # H5/后台列表置顶
    pinned_at = db.Column(db.DateTime)                        # 置顶时间（最后置顶的排最前）
    staff = db.relationship("Staff", foreign_keys=[uploader_id])
    chain = db.relationship("Chain")


# -------------------- 日常管理（合规照）--------------------

class DailyTab(db.Model):
    """日常管理 TAB（KA连锁访店 ka / 中小连锁拜访 yaoyou / 壹药网联合拜访 yiyao），
    各自独立配可见范围（同 DownloadCategory 模式）。form_type 决定 H5/POST 的表单字段集。
    「日常管理」图标对店员是否可见 = 至少有一个 TAB 可见（见 api _daily_icon_visible）。"""
    __tablename__ = "daily_tab"
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(20), nullable=False, unique=True)   # ka/yaoyou/yiyao
    name = db.Column(db.String(60), nullable=False)
    sort = db.Column(db.Integer, default=0)                       # 大在前（KA=300 排第一）
    door_label = db.Column(db.String(60))                         # 第一张照标签（H5 显示）
    office_label = db.Column(db.String(60))                       # 第二张照标签
    form_type = db.Column(db.String(20), default="normal")        # ka / normal
    scope_mode = db.Column(db.String(20), default="all")          # all/chain/staff
    allowed_chains = db.Column(db.Text)                           # scope_mode=chain 时的连锁 id JSON 列表


class DailyTabParticipant(db.Model):
    """日常管理某 TAB 的指定可见人员（scope_mode='staff' 时生效）。"""
    __tablename__ = "daily_tab_participant"
    id = db.Column(db.Integer, primary_key=True)
    tab_id = db.Column(db.Integer, db.ForeignKey("daily_tab.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    staff = db.relationship("Staff")
    __table_args__ = (db.UniqueConstraint("tab_id", "staff_id", name="uq_daily_tab_staff"),)


class DailyCompliance(db.Model):
    """日常管理合规照：店员按 TAB(KA连锁访店 ka / 中小连锁拜访 yaoyou / 壹药网联合拜访 yiyao)
    上传 2 张照 + 客户信息，中台审核通过发分（积分后台可配 daily_reward）。不限频，可重复提交。
    form_type=ka 的提交用 ka_chain/displayed_products 两列；normal 用 cust_name/visit_*；
    door_img/office_img/cust_province/cust_city 各 TAB 通用。"""
    __tablename__ = "daily_compliance"
    id = db.Column(db.Integer, primary_key=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    store_id = db.Column(db.Integer, db.ForeignKey("store.id"), index=True)
    tab_key = db.Column(db.String(20), nullable=False)        # ka / yaoyou / yiyao
    door_img = db.Column(db.String(255), nullable=False)      # 第一张照  /uploads/daily/
    office_img = db.Column(db.String(255), nullable=False)    # 第二张照
    cust_name = db.Column(db.String(20))                      # 客户名称（≤6字，normal 必填）
    cust_province = db.Column(db.String(20))                  # 客户/门店省份（必填）
    cust_city = db.Column(db.String(20))                      # 客户/门店城市（必填）
    visit_purpose = db.Column(db.String(20))                  # 拜访目的：库存量/月销量/当月活动（normal 必填）
    product_name = db.Column(db.String(60))                   # 产品名称（normal 必填）
    visit_content = db.Column(db.String(255))                 # 沟量化/活动描述（normal 必填，随拜访目的变）
    ka_chain = db.Column(db.String(60))                       # KA连锁名（form_type=ka 必填；其他 NULL）
    displayed_products = db.Column(db.String(120))            # 门店已陈列产品，逗号分隔（form_type=ka 必填）
    period = db.Column(db.String(7))                          # YYYY-MM（仅记录提交月份，不限频）
    status = db.Column(db.String(20), default="pending")      # pending/approved/rejected
    points_earned = db.Column(db.Integer, default=0)
    reviewer_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    reviewed_at = db.Column(db.DateTime)
    reject_reason = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff", foreign_keys=[staff_id])


# -------------------- 游戏闯关：英雄榜屏蔽 + 手动发分 --------------------

class GameExclusion(db.Model):
    """游戏英雄榜屏蔽：该店员在该游戏的成绩不上榜（软隐藏，可恢复；区别于物理删除成绩）。
    英雄榜查询排除被屏蔽的 staff。"""
    __tablename__ = "game_exclusion"
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey("exam.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    admin_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff", foreign_keys=[staff_id])
    __table_args__ = (db.UniqueConstraint("exam_id", "staff_id", name="uq_game_excl"),)


class GameReward(db.Model):
    """游戏英雄榜手动发分记录：管理员对某店员在某游戏发放的积分档位（50/100/200/300/500）。
    unique(exam_id,staff_id) 防重复发放；后台展示「已发 X 分」。积分流水走 add_points(type='game')。"""
    __tablename__ = "game_reward"
    id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey("exam.id"), nullable=False, index=True)
    staff_id = db.Column(db.Integer, db.ForeignKey("staff.id"), nullable=False, index=True)
    points = db.Column(db.Integer, nullable=False)            # 发放档位 50/100/200/300/500
    admin_id = db.Column(db.Integer, db.ForeignKey("staff.id"))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    staff = db.relationship("Staff", foreign_keys=[staff_id])
    __table_args__ = (db.UniqueConstraint("exam_id", "staff_id", name="uq_game_reward"),)