group_fqcd_jr/alembic/versions/20260910_promotion_material.py

"""新增基金产品推介材料独立业务表。"""

from alembic import op

revision = "20260910_promotion_material"
down_revision = "20260910_offsite_worker"
branch_labels = None
depends_on = None


TABLES = [
    """
    CREATE TABLE promotion_material_task (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      product_code VARCHAR(32) NULL,
      product_name VARCHAR(128) NOT NULL,
      material_title VARCHAR(200) NOT NULL,
      style_code VARCHAR(32) NOT NULL,
      output_formats JSON NOT NULL,
      template_version VARCHAR(64) NULL,
      status VARCHAR(32) NOT NULL,
      created_by BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(6) NOT NULL,
      updated_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uk_promotion_task_no (task_no),
      KEY idx_promotion_task_status (status, updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_input_snapshot (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      version_no INT UNSIGNED NOT NULL,
      product_info JSON NOT NULL,
      manager_info JSON NOT NULL,
      team_info JSON NOT NULL,
      strategy_info JSON NOT NULL,
      fee_structure JSON NOT NULL,
      performance_info JSON NOT NULL,
      risk_disclosure JSON NOT NULL,
      source_notes JSON NOT NULL,
      created_by BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uk_promotion_input_version (task_no, version_no),
      KEY idx_promotion_input_task (task_no, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_attachment (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      attachment_type VARCHAR(32) NOT NULL,
      filename VARCHAR(255) NOT NULL,
      media_type VARCHAR(128) NOT NULL,
      file_hash VARCHAR(128) NOT NULL,
      size_bytes BIGINT UNSIGNED NOT NULL,
      file_path VARCHAR(500) NOT NULL,
      parsed_metadata JSON NOT NULL,
      uploaded_by BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uk_promotion_attachment_hash (task_no, file_hash),
      KEY idx_promotion_attachment_task_type (task_no, attachment_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_material_version (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      input_snapshot_id BIGINT UNSIGNED NOT NULL,
      version_no INT UNSIGNED NOT NULL,
      style_code VARCHAR(32) NOT NULL,
      template_version VARCHAR(64) NULL,
      prompt_version VARCHAR(64) NOT NULL,
      draft_json JSON NOT NULL,
      pptx_path VARCHAR(500) NULL,
      pdf_path VARCHAR(500) NULL,
      chart_paths JSON NOT NULL,
      status VARCHAR(32) NOT NULL,
      error_message VARCHAR(500) NULL,
      generated_by BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uk_promotion_material_version (task_no, version_no),
      KEY idx_promotion_material_status (task_no, status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_compliance_check (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      material_version_id BIGINT UNSIGNED NULL,
      rule_code VARCHAR(64) NOT NULL,
      rule_name VARCHAR(128) NOT NULL,
      scope VARCHAR(32) NOT NULL,
      severity VARCHAR(16) NOT NULL,
      hit_text TEXT NULL,
      suggestion VARCHAR(500) NOT NULL,
      created_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      KEY idx_promotion_compliance_task (task_no, created_at),
      KEY idx_promotion_compliance_version (material_version_id, severity)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_review_record (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      material_version_id BIGINT UNSIGNED NOT NULL,
      reviewer_id BIGINT UNSIGNED NOT NULL,
      decision VARCHAR(32) NOT NULL,
      comment VARCHAR(1000) NULL,
      created_at DATETIME(6) NOT NULL,
      PRIMARY KEY (id),
      KEY idx_promotion_review_version (material_version_id, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
    """
    CREATE TABLE promotion_delivery_record (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      task_no VARCHAR(32) NOT NULL,
      material_version_id BIGINT UNSIGNED NOT NULL,
      advisor_id BIGINT UNSIGNED NOT NULL,
      delivery_channel VARCHAR(32) NOT NULL,
      status VARCHAR(16) NOT NULL,
      failure_reason VARCHAR(500) NULL,
      created_by BIGINT UNSIGNED NOT NULL,
      created_at DATETIME(6) NOT NULL,
      sent_at DATETIME(6) NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uk_promotion_delivery_version_advisor (material_version_id, advisor_id),
      KEY idx_promotion_delivery_advisor (advisor_id, status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    """,
]


def upgrade() -> None:
    for statement in TABLES:
        op.execute(statement)


def downgrade() -> None:
    raise RuntimeError("产品推介材料业务数据禁止自动删除，请使用前向兼容迁移")