airlabs-manage/backend/models.py

"""数据库模型 —— 所有表定义"""
from sqlalchemy import (
    Column, Integer, String, Float, Date, DateTime, Text,
    ForeignKey, Enum as SAEnum, JSON
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database import Base
import enum


# ──────────────────────────── 权限标识符定义 ────────────────────────────

ALL_PERMISSIONS = [
    # 仪表盘
    ("dashboard:view", "查看仪表盘（基础）", "仪表盘"),
    ("dashboard:view_cost", "查看成本模块", "仪表盘"),
    ("dashboard:view_waste", "查看损耗模块", "仪表盘"),
    ("dashboard:view_profit", "查看盈亏模块", "仪表盘"),
    ("dashboard:view_risk", "查看风险预警", "仪表盘"),
    # 项目管理
    ("project:view", "查看项目", "项目管理"),
    ("project:create", "创建项目", "项目管理"),
    ("project:edit", "编辑项目", "项目管理"),
    ("project:delete", "删除项目", "项目管理"),
    ("project:complete", "确认完成项目", "项目管理"),
    ("project:view_contract", "查看合同金额", "项目管理"),
    # 内容提交
    ("submission:view", "查看提交记录", "内容提交"),
    ("submission:create", "新增提交", "内容提交"),
    ("submission:proxy", "代人提交", "内容提交"),
    ("submission:delete", "删除提交记录", "内容提交"),
    # 成本管理 —— 按类型细分
    ("cost_ai:view", "查看AI工具成本", "成本管理"),
    ("cost_ai:create", "录入AI工具成本", "成本管理"),
    ("cost_ai:delete", "删除AI工具成本", "成本管理"),
    ("cost_outsource:view", "查看外包成本", "成本管理"),
    ("cost_outsource:create", "录入外包成本", "成本管理"),
    ("cost_outsource:delete", "删除外包成本", "成本管理"),
    ("cost_overhead:view", "查看固定开支", "成本管理"),
    ("cost_overhead:create", "录入固定开支", "成本管理"),
    ("cost_overhead:delete", "删除固定开支", "成本管理"),
    ("cost_labor:view", "查看人力调整", "成本管理"),
    ("cost_labor:create", "录入人力调整", "成本管理"),
    # 用户与角色
    ("user:view", "查看用户列表", "用户与角色"),
    ("user:view_cost", "查看成员薪资成本", "用户与角色"),
    ("user:manage", "管理用户", "用户与角色"),
    ("role:manage", "管理角色", "用户与角色"),
    # 结算与效率
    ("settlement:view", "查看结算报告", "结算与效率"),
    ("efficiency:view", "查看团队效率", "结算与效率"),
    # 报告推送
    ("report:daily", "触发/查看日报", "报告推送"),
    ("report:weekly", "触发/查看周报（含成本）", "报告推送"),
    ("report:monthly", "触发/查看月报（含成本+盈亏）", "报告推送"),
]

PERMISSION_KEYS = [p[0] for p in ALL_PERMISSIONS]

# 成本查看权限集合（用于判断是否有任一成本查看权限）
COST_VIEW_PERMS = ["cost_ai:view", "cost_outsource:view", "cost_overhead:view", "cost_labor:view"]

# 旧权限 → 新权限映射（用于数据库迁移）
COST_PERM_MIGRATION = {
    "cost:view": ["cost_ai:view", "cost_outsource:view", "cost_overhead:view", "cost_labor:view"],
    "cost:create": ["cost_ai:create", "cost_outsource:create", "cost_overhead:create", "cost_labor:create"],
    "cost:delete": ["cost_ai:delete", "cost_outsource:delete", "cost_overhead:delete"],
}

# 内置角色定义
BUILTIN_ROLES = {
    "超级管理员": {
        "description": "系统最高权限，拥有全部功能",
        "permissions": PERMISSION_KEYS[:],  # 全部
    },
    "主管": {
        "description": "管理项目和提交，可查看AI工具与外包成本",
        "permissions": [
            "dashboard:view", "dashboard:view_waste", "dashboard:view_risk",
            "project:view", "project:create", "project:edit", "project:complete",
            "submission:view", "submission:create", "submission:proxy", "submission:delete",
            "cost_ai:view", "cost_ai:create", "cost_ai:delete",
            "cost_outsource:view", "cost_outsource:create", "cost_outsource:delete",
            "user:view",
            "efficiency:view",
            "report:daily", "report:weekly", "report:monthly",
        ],
    },
    "组长": {
        "description": "管理本组提交和查看成本",
        "permissions": [
            "project:view",
            "submission:view", "submission:create",
            "cost_ai:view", "cost_ai:create",
            "efficiency:view",
            "report:daily",
        ],
    },
    "成员": {
        "description": "提交内容和查看项目",
        "permissions": [
            "project:view",
            "submission:view", "submission:create",
        ],
    },
}


# ──────────────────────────── 枚举定义 ────────────────────────────

class ProjectType(str, enum.Enum):
    CLIENT_FORMAL = "客户正式项目"
    CLIENT_TEST = "客户测试项目"
    INTERNAL_ORIGINAL = "内部原创项目"
    INTERNAL_TEST = "内部测试项目"


class ProjectStatus(str, enum.Enum):
    IN_PROGRESS = "制作中"
    COMPLETED = "已完成"
    ABANDONED = "废弃"


class PhaseGroup(str, enum.Enum):
    PRE = "前期"
    PRODUCTION = "中期"
    POST = "后期"
    INTERNAL = "内部事务"


class WorkType(str, enum.Enum):
    PRODUCTION = "制作"
    TEST = "测试"
    PLAN = "方案"
    REVISION = "修改"
    QC = "QC"


class ContentType(str, enum.Enum):
    # 前期（项目级）
    PLANNING = "策划案"
    SYNOPSIS = "大纲/梗概"
    CONCEPT_DESIGN = "概念设计图"
    TEST_FOOTAGE = "测试片"
    # 前期（集数级）
    SCRIPT = "剧本"
    STORYBOARD = "分镜"
    CHARACTER_DESIGN = "人设图"
    SCENE_DESIGN = "场景图"
    PROP_DESIGN = "道具图"
    # 中期
    ANIMATION = "动画制作"
    # 后期
    DUBBING = "AI配音"
    SOUND_EFFECTS = "音效"
    SHOT_REPAIR = "修补镜头"
    EDITING = "剪辑"
    MUSIC = "音乐/BGM"
    SUBTITLE = "字幕"
    # 通用
    OTHER = "其他"
    # 内部事务
    TRAINING = "培训"
    RECRUITMENT = "招聘面试"
    INTERNAL_OTHER = "内部其他"


class SubmitTo(str, enum.Enum):
    LEADER = "组长"
    PRODUCER = "制片"
    INTERNAL = "内部"
    EXTERNAL = "外部"


class SubscriptionPeriod(str, enum.Enum):
    MONTHLY = "月"
    YEARLY = "年"


class CostAllocationType(str, enum.Enum):
    PROJECT = "指定项目"
    TEAM = "内容组整体"
    MANUAL = "手动分摊"


class OutsourceType(str, enum.Enum):
    ANIMATION = "动画"
    EDITING = "剪辑"
    FULL_EPISODE = "整集"


class OverheadCostType(str, enum.Enum):
    OFFICE_RENT = "办公室租金"
    UTILITIES = "水电费"


# ──────────────────────────── 角色 ────────────────────────────

class Role(Base):
    __tablename__ = "roles"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), unique=True, nullable=False)
    description = Column(String(200), nullable=True)
    permissions = Column(JSON, nullable=False, default=[])  # 权限标识符列表
    is_system = Column(Integer, nullable=False, default=0)  # 1=内置角色不可删
    exempt_submission = Column(Integer, nullable=False, default=0)  # 1=豁免提交
    created_at = Column(DateTime, server_default=func.now())

    users = relationship("User", back_populates="role_ref")

    def has_permission(self, perm: str) -> bool:
        return perm in (self.permissions or [])


# ──────────────────────────── 用户 ────────────────────────────

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    name = Column(String(50), nullable=False)
    phase_group = Column(SAEnum(PhaseGroup), nullable=False)
    role_id = Column(Integer, ForeignKey("roles.id"), nullable=False)
    monthly_salary = Column(Float, nullable=False, default=0)
    bonus = Column(Float, nullable=False, default=0)
    social_insurance = Column(Float, nullable=False, default=0)
    is_active = Column(Integer, nullable=False, default=1)
    created_at = Column(DateTime, server_default=func.now())

    # 关系
    role_ref = relationship("Role", back_populates="users")
    submissions = relationship("Submission", back_populates="user")
    led_projects = relationship("Project", back_populates="leader")

    @property
    def role_name(self):
        return self.role_ref.name if self.role_ref else ""

    @property
    def permissions(self):
        return self.role_ref.permissions if self.role_ref else []

    def has_permission(self, perm: str) -> bool:
        return self.role_ref.has_permission(perm) if self.role_ref else False

    @property
    def monthly_total_cost(self):
        return (self.monthly_salary or 0) + (self.bonus or 0) + (self.social_insurance or 0)

    @property
    def daily_cost(self):
        from config import WORKING_DAYS_PER_MONTH
        return round(self.monthly_total_cost / WORKING_DAYS_PER_MONTH, 2) if self.monthly_total_cost else 0


# ──────────────────────────── 项目 ────────────────────────────

class Project(Base):
    __tablename__ = "projects"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)
    project_type = Column(SAEnum(ProjectType), nullable=False)
    status = Column(SAEnum(ProjectStatus), nullable=False, default=ProjectStatus.IN_PROGRESS)
    leader_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    current_phase = Column(SAEnum(PhaseGroup), nullable=False, default=PhaseGroup.PRE)
    episode_duration_minutes = Column(Float, nullable=False)
    episode_count = Column(Integer, nullable=False)
    estimated_completion_date = Column(Date, nullable=True)
    actual_completion_date = Column(Date, nullable=True)
    contract_amount = Column(Float, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    leader = relationship("User", back_populates="led_projects")
    submissions = relationship("Submission", back_populates="project")
    outsource_costs = relationship("OutsourceCost", back_populates="project")
    ai_tool_allocations = relationship("AIToolCostAllocation", back_populates="project")
    milestones = relationship("ProjectMilestone", back_populates="project", cascade="all, delete-orphan")

    @property
    def target_total_seconds(self):
        return int(self.episode_duration_minutes * 60 * self.episode_count)


# ──────────────────────────── 内容提交 ────────────────────────────

class Submission(Base):
    __tablename__ = "submissions"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    project_phase = Column(SAEnum(PhaseGroup), nullable=False)
    work_type = Column(SAEnum(WorkType), nullable=False)
    content_type = Column(SAEnum(ContentType), nullable=False)
    duration_minutes = Column(Float, nullable=True, default=0)
    duration_seconds = Column(Float, nullable=True, default=0)
    total_seconds = Column(Float, nullable=False, default=0)
    hours_spent = Column(Float, nullable=True)
    submit_to = Column(SAEnum(SubmitTo), nullable=False)
    description = Column(Text, nullable=True)
    submit_date = Column(Date, nullable=False)
    milestone_id = Column(Integer, ForeignKey("project_milestones.id"), nullable=True)
    delay_reason = Column(Text, nullable=True)
    episode_number = Column(Integer, nullable=True)  # EP集数: 1=EP01, 2=EP02...
    created_at = Column(DateTime, server_default=func.now())

    user = relationship("User", back_populates="submissions")
    project = relationship("Project", back_populates="submissions")
    milestone = relationship("ProjectMilestone")
    history = relationship("SubmissionHistory", back_populates="submission")


# ──────────────────────────── AI 工具成本 ────────────────────────────

class AIToolCost(Base):
    __tablename__ = "ai_tool_costs"

    id = Column(Integer, primary_key=True, index=True)
    tool_name = Column(String(100), nullable=False)
    subscription_period = Column(SAEnum(SubscriptionPeriod), nullable=False)
    amount = Column(Float, nullable=False)
    allocation_type = Column(SAEnum(CostAllocationType), nullable=False)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=True)
    recorded_by = Column(Integer, ForeignKey("users.id"), nullable=False)
    record_date = Column(Date, nullable=False)
    created_at = Column(DateTime, server_default=func.now())

    allocations = relationship("AIToolCostAllocation", back_populates="ai_tool_cost")


class AIToolCostAllocation(Base):
    __tablename__ = "ai_tool_cost_allocations"

    id = Column(Integer, primary_key=True, index=True)
    ai_tool_cost_id = Column(Integer, ForeignKey("ai_tool_costs.id"), nullable=False)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    percentage = Column(Float, nullable=False)

    ai_tool_cost = relationship("AIToolCost", back_populates="allocations")
    project = relationship("Project", back_populates="ai_tool_allocations")


# ──────────────────────────── 外包成本 ────────────────────────────

class OutsourceCost(Base):
    __tablename__ = "outsource_costs"

    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    outsource_type = Column(SAEnum(OutsourceType), nullable=False)
    episode_start = Column(Integer, nullable=True)
    episode_end = Column(Integer, nullable=True)
    amount = Column(Float, nullable=False)
    recorded_by = Column(Integer, ForeignKey("users.id"), nullable=False)
    record_date = Column(Date, nullable=False)
    created_at = Column(DateTime, server_default=func.now())

    project = relationship("Project", back_populates="outsource_costs")


# ──────────────────────────── 人力成本手动调整 ────────────────────────────

class CostOverride(Base):
    __tablename__ = "cost_overrides"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    date = Column(Date, nullable=False)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    override_amount = Column(Float, nullable=False)
    adjusted_by = Column(Integer, ForeignKey("users.id"), nullable=False)
    reason = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())


# ──────────────────────────── 提交历史版本 ────────────────────────────

class SubmissionHistory(Base):
    __tablename__ = "submission_history"

    id = Column(Integer, primary_key=True, index=True)
    submission_id = Column(Integer, ForeignKey("submissions.id"), nullable=False)
    changed_by = Column(Integer, ForeignKey("users.id"), nullable=False)
    change_reason = Column(Text, nullable=False)
    old_data = Column(JSON, nullable=False)
    new_data = Column(JSON, nullable=False)
    created_at = Column(DateTime, server_default=func.now())

    submission = relationship("Submission", back_populates="history")


# ──────────────────────────── 固定开支 ────────────────────────────

class OverheadCost(Base):
    __tablename__ = "overhead_costs"

    id = Column(Integer, primary_key=True, index=True)
    cost_type = Column(SAEnum(OverheadCostType), nullable=False)
    amount = Column(Float, nullable=False)
    record_month = Column(String(7), nullable=False)
    note = Column(Text, nullable=True)
    recorded_by = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime, server_default=func.now())


# ──────────────────────────── 项目里程碑 ────────────────────────────

class ProjectMilestone(Base):
    __tablename__ = "project_milestones"

    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    name = Column(String(100), nullable=False)
    phase = Column(SAEnum(PhaseGroup), nullable=False)
    is_completed = Column(Integer, nullable=False, default=0)  # 0/1
    completed_at = Column(DateTime, nullable=True)
    sort_order = Column(Integer, nullable=False, default=0)
    estimated_days = Column(Integer, nullable=True)   # 预估工作日
    start_date = Column(Date, nullable=True)          # 开始日期

    project = relationship("Project", back_populates="milestones")


# 默认里程碑模板
DEFAULT_MILESTONES = [
    # 前期
    {"name": "策划案", "phase": "前期", "sort_order": 1},
    {"name": "剧本", "phase": "前期", "sort_order": 2},
    {"name": "分镜", "phase": "前期", "sort_order": 3},
    {"name": "人设图", "phase": "前期", "sort_order": 4},
    {"name": "场景图", "phase": "前期", "sort_order": 5},
    {"name": "道具图", "phase": "前期", "sort_order": 6},
    # 后期
    {"name": "配音", "phase": "后期", "sort_order": 1},
    {"name": "AI配音", "phase": "后期", "sort_order": 2},
    {"name": "音效", "phase": "后期", "sort_order": 3},
    {"name": "修补镜头", "phase": "后期", "sort_order": 4},
    {"name": "剪辑", "phase": "后期", "sort_order": 5},
    {"name": "音乐/BGM", "phase": "后期", "sort_order": 6},
    {"name": "字幕", "phase": "后期", "sort_order": 7},
    {"name": "杂项", "phase": "后期", "sort_order": 8},
]

# 内容类型 → 阶段映射（用于自动设置阶段和关联里程碑）
CONTENT_PHASE_MAP = {
    "策划案": "前期", "大纲/梗概": "前期", "概念设计图": "前期", "测试片": "前期",
    "剧本": "前期", "分镜": "前期",
    "人设图": "前期", "场景图": "前期", "道具图": "前期",
    "动画制作": "中期",
    "AI配音": "后期", "音效": "后期",
    "修补镜头": "后期", "剪辑": "后期", "音乐/BGM": "后期", "字幕": "后期",
    "培训": "内部事务", "招聘面试": "内部事务", "内部其他": "内部事务",
}

# 项目级内容类型（不需要选集数）
PROJECT_LEVEL_TYPES = {"策划案", "大纲/梗概", "概念设计图", "测试片", "培训", "招聘面试", "内部其他"}