models/media.py

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, JSON, Enum, Float, Boolean
from sqlalchemy.sql import text
from database import Base
import enum


class MediaStatus(enum.Enum):
    PENDING = "pending"
    UPLOADING = "uploading"
    SUCCESS = "success"
    FAILED = "failed"


class MediaTag(Base):
    """素材标签表"""
    __tablename__ = "media_tags"
    
    id = Column(Integer, primary_key=True, index=True, comment='主键 ID')
    tag_name = Column(String(50), nullable=False, unique=True, index=True, comment='标签名称')
    tag_color = Column(String(20), nullable=False, server_default="#3b82f6", comment='标签颜色（hex）')
    sort_order = Column(Integer, nullable=False, server_default="0", comment='排序顺序')
    company_code = Column(String(50), nullable=False, server_default="", index=True, comment='公司编码')
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
    updated_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')


class MediaTagMapping(Base):
    """素材-标签关联表（多对多）"""
    __tablename__ = "media_tag_mappings"
    
    id = Column(Integer, primary_key=True, index=True, comment='主键 ID')
    media_id = Column(String(32), ForeignKey('media.media_id'), nullable=False, index=True, comment='素材ID')
    tag_id = Column(Integer, ForeignKey('media_tags.id'), nullable=False, index=True, comment='标签ID')
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')


class Media(Base):
    __tablename__ = "media"
    
    id = Column(Integer, primary_key=True, index=True, comment='主键 ID')
    media_id = Column(String(32), nullable=False, unique=True, index=True, comment='素材唯一ID（32位UUID）')
    file_name = Column(String(500), nullable=False, comment='原始文件名')
    r2_key = Column(String(500), nullable=False, server_default="", comment='R2原图对象键（MD5命名）')
    r2_url = Column(String(500), nullable=False, server_default="", comment='R2原图URL')
    thumbnail_r2_key = Column(String(500), nullable=False, server_default="", comment='R2缩略图对象键')
    thumbnail_r2_url = Column(String(500), nullable=False, server_default="", comment='R2缩略图URL')
    media_type = Column(String(20), nullable=False, server_default="image", comment='媒体类型：image, video')
    mime_type = Column(String(100), nullable=False, server_default="", comment='MIME类型')
    file_size = Column(Integer, nullable=False, server_default="0", comment='文件大小（字节）')
    width = Column(Integer, nullable=True, comment='图片宽度')
    height = Column(Integer, nullable=True, comment='图片高度')
    duration = Column(Float, nullable=True, comment='视频时长（秒）')
    directory_id = Column(Integer, ForeignKey('media_directories.id'), nullable=True, comment='目录ID')
    status = Column(Enum(MediaStatus), nullable=False, server_default="pending", comment='上传状态')
    error_message = Column(Text, nullable=True, comment='错误信息')
    batch_id = Column(String(32), nullable=True, index=True, comment='批次ID（批量上传时使用）')
    company_code = Column(String(50), nullable=False, server_default="", index=True, comment='公司编码')
    is_deleted = Column(Boolean, nullable=False, server_default="0", index=True, comment='是否已删除：0-未删除，1-已删除')
    deleted_at = Column(DateTime(timezone=True), nullable=True, comment='删除时间')
    
    # 关联关系
    tags = Column(JSON, nullable=True, comment='标签ID列表（JSON数组）')

    created_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
    updated_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')



class MediaDirectory(Base):
    __tablename__ = "media_directories"
    
    id = Column(Integer, primary_key=True, index=True, comment='主键 ID')
    name = Column(String(100), nullable=False, comment='目录名称')
    parent_id = Column(Integer, ForeignKey('media_directories.id'), nullable=True, comment='父目录ID')
    company_code = Column(String(50), nullable=False, server_default="", index=True, comment='公司编码')
    path = Column(String(500), nullable=False, server_default="", comment='完整路径')
    level = Column(Integer, nullable=False, server_default="0", comment='目录层级')
    sort_order = Column(Integer, nullable=False, server_default="0", comment='排序顺序')
    cover_r2_key = Column(String(500), nullable=True, comment='目录封面图R2键')
    cover_r2_url = Column(String(500), nullable=True, comment='目录封面图R2URL')
    description = Column(Text, nullable=True, comment='目录描述')
    is_public = Column(Boolean, nullable=False, server_default="0", comment='是否公开')
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
    updated_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
    
    # 关联关系
    media_count = Column(Integer, nullable=False, server_default="0", comment='素材数量（缓存字段）')