document-management/backend/migrations/versions/0001_initial_schema.py

"""创建DMS七张核心表。

Revision ID: 0001_initial_schema
Revises:
Create Date: 2026-07-23
"""

from __future__ import annotations

from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

revision: str = "0001_initial_schema"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _business_columns() -> list[sa.Column]:
    return [
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("created_by", sa.BigInteger(), nullable=True),
        sa.Column("updated_by", sa.BigInteger(), nullable=True),
        sa.Column(
            "created_at",
            mysql.DATETIME(fsp=3),
            server_default=sa.text("CURRENT_TIMESTAMP(3)"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            mysql.DATETIME(fsp=3),
            server_default=sa.text("CURRENT_TIMESTAMP(3)"),
            nullable=False,
        ),
        sa.Column("deleted_at", mysql.DATETIME(fsp=3), nullable=True),
        sa.Column(
            "is_deleted",
            sa.Boolean(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "row_version",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.PrimaryKeyConstraint("id"),
    ]


def upgrade() -> None:
    op.create_table(
        "sys_organization",
        *_business_columns(),
        sa.Column("parent_id", sa.BigInteger(), nullable=True),
        sa.Column("org_code", sa.String(length=64), nullable=False),
        sa.Column("org_name", sa.String(length=128), nullable=False),
        sa.Column("org_path", sa.String(length=1000), nullable=False),
        sa.Column(
            "sort_no",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "status",
            sa.String(length=20),
            server_default=sa.text("'ENABLED'"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "status IN ('ENABLED', 'DISABLED')",
            name="organization_status",
        ),
        sa.ForeignKeyConstraint(
            ["parent_id"],
            ["sys_organization.id"],
            name="fk_sys_organization_parent_id_sys_organization",
            ondelete="RESTRICT",
        ),
        sa.UniqueConstraint("org_code", name="uq_sys_organization_org_code"),
    )
    op.create_index(
        "ix_sys_organization_parent_active_sort",
        "sys_organization",
        ["parent_id", "is_deleted", "sort_no"],
    )
    op.create_index(
        "ix_sys_organization_status_active",
        "sys_organization",
        ["status", "is_deleted"],
    )

    op.create_table(
        "sys_user",
        *_business_columns(),
        sa.Column("username", sa.String(length=64), nullable=False),
        sa.Column("password_hash", sa.String(length=255), nullable=False),
        sa.Column("real_name", sa.String(length=64), nullable=False),
        sa.Column("organization_id", sa.BigInteger(), nullable=True),
        sa.Column("organization_name", sa.String(length=128), nullable=True),
        sa.Column(
            "role_code",
            sa.String(length=32),
            server_default=sa.text("'USER'"),
            nullable=False,
        ),
        sa.Column(
            "security_level",
            sa.String(length=32),
            server_default=sa.text("'INTERNAL'"),
            nullable=False,
        ),
        sa.Column(
            "status",
            sa.String(length=20),
            server_default=sa.text("'ENABLED'"),
            nullable=False,
        ),
        sa.Column(
            "auth_version",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column("last_login_at", mysql.DATETIME(fsp=3), nullable=True),
        sa.Column("last_login_ip", sa.String(length=64), nullable=True),
        sa.CheckConstraint(
            "role_code IN ('USER', 'ADMIN', 'AUDITOR')",
            name="user_role_code",
        ),
        sa.CheckConstraint(
            "security_level IN "
            "('PUBLIC', 'INTERNAL', 'SECRET', 'CONFIDENTIAL', 'TOP_SECRET')",
            name="user_security_level",
        ),
        sa.CheckConstraint(
            "status IN ('ENABLED', 'DISABLED')",
            name="user_status",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["sys_organization.id"],
            name="fk_sys_user_organization_id_sys_organization",
            ondelete="RESTRICT",
        ),
        sa.UniqueConstraint("username", name="uq_sys_user_username"),
    )
    op.create_index(
        "ix_sys_user_organization_active_status",
        "sys_user",
        ["organization_id", "is_deleted", "status"],
    )
    op.create_index(
        "ix_sys_user_role_status_active",
        "sys_user",
        ["role_code", "status", "is_deleted"],
    )

    op.create_table(
        "doc_category",
        *_business_columns(),
        sa.Column("parent_id", sa.BigInteger(), nullable=True),
        sa.Column("category_code", sa.String(length=64), nullable=False),
        sa.Column("category_name", sa.String(length=128), nullable=False),
        sa.Column(
            "category_type",
            sa.String(length=32),
            server_default=sa.text("'OTHER'"),
            nullable=False,
        ),
        sa.Column("category_path", sa.String(length=1000), nullable=False),
        sa.Column(
            "sort_no",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "document_count",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "status",
            sa.String(length=20),
            server_default=sa.text("'ENABLED'"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "category_type IN ('SCENE', 'STYLE', 'SITUATION', 'VERSION', 'OTHER')",
            name="category_type",
        ),
        sa.CheckConstraint(
            "status IN ('ENABLED', 'DISABLED')",
            name="category_status",
        ),
        sa.ForeignKeyConstraint(
            ["parent_id"],
            ["doc_category.id"],
            name="fk_doc_category_parent_id_doc_category",
            ondelete="RESTRICT",
        ),
        sa.UniqueConstraint("category_code", name="uq_doc_category_category_code"),
    )
    op.create_index(
        "ix_doc_category_parent_active_sort",
        "doc_category",
        ["parent_id", "is_deleted", "sort_no"],
    )
    op.create_index(
        "ix_doc_category_status_active",
        "doc_category",
        ["status", "is_deleted"],
    )
    op.create_index(
        "ix_doc_category_type_active",
        "doc_category",
        ["category_type", "is_deleted"],
    )

    op.create_table(
        "doc_document",
        *_business_columns(),
        sa.Column("document_name", sa.String(length=255), nullable=False),
        sa.Column("summary", sa.Text(), nullable=True),
        sa.Column("document_type", sa.String(length=32), nullable=False),
        sa.Column(
            "document_status",
            sa.String(length=32),
            server_default=sa.text("'DRAFT'"),
            nullable=False,
        ),
        sa.Column(
            "security_level",
            sa.String(length=32),
            server_default=sa.text("'INTERNAL'"),
            nullable=False,
        ),
        sa.Column(
            "visibility_type",
            sa.String(length=32),
            server_default=sa.text("'CUSTOM'"),
            nullable=False,
        ),
        sa.Column("attachment_type", sa.String(length=32), nullable=True),
        sa.Column("category_id", sa.BigInteger(), nullable=True),
        sa.Column("category_name", sa.String(length=128), nullable=True),
        sa.Column("category_path", sa.String(length=1000), nullable=True),
        sa.Column("parent_document_id", sa.BigInteger(), nullable=True),
        sa.Column("root_document_id", sa.BigInteger(), nullable=True),
        sa.Column("tags", mysql.JSON(), nullable=True),
        sa.Column("original_file_name", sa.String(length=255), nullable=False),
        sa.Column("file_relative_path", sa.String(length=1000), nullable=False),
        sa.Column("file_extension", sa.String(length=20), nullable=False),
        sa.Column("mime_type", sa.String(length=128), nullable=True),
        sa.Column(
            "file_size",
            sa.BigInteger(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column("file_hash", sa.String(length=64), nullable=False),
        sa.Column(
            "child_count",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "attachment_count",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "view_count",
            sa.BigInteger(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "download_count",
            sa.BigInteger(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column("search_text", sa.Text(), nullable=True),
        sa.Column("created_by_name", sa.String(length=64), nullable=True),
        sa.Column("updated_by_name", sa.String(length=64), nullable=True),
        sa.CheckConstraint(
            "document_type IN ('MAIN', 'SUB_PLAN', 'ATTACHMENT')",
            name="document_type",
        ),
        sa.CheckConstraint(
            "document_status IN ('DRAFT', 'PUBLISHED', 'ARCHIVED')",
            name="document_status",
        ),
        sa.CheckConstraint(
            "security_level IN "
            "('PUBLIC', 'INTERNAL', 'SECRET', 'CONFIDENTIAL', 'TOP_SECRET')",
            name="document_security_level",
        ),
        sa.CheckConstraint(
            "visibility_type IN ('ALL_AUTHENTICATED', 'ORGANIZATION', 'CUSTOM')",
            name="document_visibility_type",
        ),
        sa.CheckConstraint(
            "attachment_type IS NULL OR attachment_type IN "
            "('POLICY', 'WORK_STANDARD', 'TABLE', 'DIAGRAM', 'OTHER')",
            name="document_attachment_type",
        ),
        sa.CheckConstraint(
            "("
            "document_type = 'MAIN' "
            "AND parent_document_id IS NULL "
            "AND category_id IS NOT NULL "
            "AND attachment_type IS NULL"
            ") OR ("
            "document_type = 'SUB_PLAN' "
            "AND parent_document_id IS NOT NULL "
            "AND root_document_id IS NOT NULL "
            "AND category_id IS NOT NULL "
            "AND attachment_type IS NULL"
            ") OR ("
            "document_type = 'ATTACHMENT' "
            "AND parent_document_id IS NULL "
            "AND root_document_id IS NULL "
            "AND category_id IS NULL "
            "AND attachment_type IS NOT NULL "
            "AND security_level = 'PUBLIC' "
            "AND visibility_type = 'ALL_AUTHENTICATED'"
            ")",
            name="document_type_shape",
        ),
        sa.ForeignKeyConstraint(
            ["category_id"],
            ["doc_category.id"],
            name="fk_doc_document_category_id_doc_category",
            ondelete="RESTRICT",
        ),
        sa.ForeignKeyConstraint(
            ["parent_document_id"],
            ["doc_document.id"],
            name="fk_doc_document_parent_document_id_doc_document",
            ondelete="RESTRICT",
        ),
        sa.ForeignKeyConstraint(
            ["root_document_id"],
            ["doc_document.id"],
            name="fk_doc_document_root_document_id_doc_document",
            ondelete="RESTRICT",
        ),
    )
    op.create_index(
        "ix_doc_document_type_active_updated",
        "doc_document",
        ["document_type", "is_deleted", "updated_at"],
    )
    op.create_index(
        "ix_doc_document_parent_type_active",
        "doc_document",
        ["parent_document_id", "document_type", "is_deleted"],
    )
    op.create_index(
        "ix_doc_document_root_active",
        "doc_document",
        ["root_document_id", "is_deleted"],
    )
    op.create_index(
        "ix_doc_document_category_active",
        "doc_document",
        ["category_id", "is_deleted"],
    )
    op.create_index(
        "ix_doc_document_status_active",
        "doc_document",
        ["document_status", "is_deleted"],
    )
    op.create_index(
        "ix_doc_document_security_active",
        "doc_document",
        ["security_level", "is_deleted"],
    )
    op.create_index("ix_doc_document_file_hash", "doc_document", ["file_hash"])
    op.create_index(
        "ix_doc_document_creator_active",
        "doc_document",
        ["created_by", "is_deleted"],
    )
    op.create_index(
        "ix_doc_document_attachment_type_active_updated",
        "doc_document",
        ["attachment_type", "is_deleted", "updated_at"],
    )
    op.create_index(
        "ix_doc_document_file_extension_active_updated",
        "doc_document",
        ["file_extension", "is_deleted", "updated_at"],
    )

    op.create_table(
        "doc_attachment_binding",
        *_business_columns(),
        sa.Column("main_document_id", sa.BigInteger(), nullable=False),
        sa.Column("attachment_document_id", sa.BigInteger(), nullable=False),
        sa.Column(
            "sort_no",
            sa.Integer(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "active_marker",
            sa.Integer(),
            sa.Computed("IF(is_deleted = 0, 1, NULL)", persisted=False),
            nullable=True,
        ),
        sa.ForeignKeyConstraint(
            ["attachment_document_id"],
            ["doc_document.id"],
            name="fk_doc_attachment_binding_attachment_document_id_doc_document",
            ondelete="RESTRICT",
        ),
        sa.ForeignKeyConstraint(
            ["main_document_id"],
            ["doc_document.id"],
            name="fk_doc_attachment_binding_main_document_id_doc_document",
            ondelete="RESTRICT",
        ),
        sa.UniqueConstraint(
            "main_document_id",
            "attachment_document_id",
            "active_marker",
            name="uq_doc_attachment_binding_active",
        ),
    )
    op.create_index(
        "ix_doc_attachment_binding_main_active_sort",
        "doc_attachment_binding",
        ["main_document_id", "is_deleted", "sort_no"],
    )
    op.create_index(
        "ix_doc_attachment_binding_attachment_active",
        "doc_attachment_binding",
        ["attachment_document_id", "is_deleted"],
    )

    op.create_table(
        "doc_permission",
        *_business_columns(),
        sa.Column("document_id", sa.BigInteger(), nullable=False),
        sa.Column("subject_type", sa.String(length=20), nullable=False),
        sa.Column("subject_id", sa.BigInteger(), nullable=False),
        sa.Column("subject_name", sa.String(length=255), nullable=False),
        sa.Column(
            "can_view",
            sa.Boolean(),
            server_default=sa.text("1"),
            nullable=False,
        ),
        sa.Column(
            "can_download",
            sa.Boolean(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "can_edit",
            sa.Boolean(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "can_manage_permission",
            sa.Boolean(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "can_delete",
            sa.Boolean(),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "active_marker",
            sa.Integer(),
            sa.Computed("IF(is_deleted = 0, 1, NULL)", persisted=False),
            nullable=True,
        ),
        sa.CheckConstraint(
            "subject_type IN ('ORG', 'USER')",
            name="permission_subject_type",
        ),
        sa.ForeignKeyConstraint(
            ["document_id"],
            ["doc_document.id"],
            name="fk_doc_permission_document_id_doc_document",
            ondelete="RESTRICT",
        ),
        sa.UniqueConstraint(
            "document_id",
            "subject_type",
            "subject_id",
            "active_marker",
            name="uq_doc_permission_active",
        ),
    )
    op.create_index(
        "ix_doc_permission_document_active",
        "doc_permission",
        ["document_id", "is_deleted"],
    )
    op.create_index(
        "ix_doc_permission_subject_active",
        "doc_permission",
        ["subject_type", "subject_id", "is_deleted"],
    )

    op.create_table(
        "sys_audit_log",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=True),
        sa.Column("username", sa.String(length=64), nullable=True),
        sa.Column("real_name", sa.String(length=64), nullable=True),
        sa.Column("organization_id", sa.BigInteger(), nullable=True),
        sa.Column("organization_name", sa.String(length=128), nullable=True),
        sa.Column("action_type", sa.String(length=32), nullable=False),
        sa.Column("target_type", sa.String(length=32), nullable=True),
        sa.Column("target_id", sa.BigInteger(), nullable=True),
        sa.Column("target_name", sa.String(length=255), nullable=True),
        sa.Column("operation_result", sa.String(length=20), nullable=False),
        sa.Column("failure_reason", sa.String(length=1000), nullable=True),
        sa.Column("client_ip", sa.String(length=64), nullable=True),
        sa.Column("user_agent", sa.String(length=500), nullable=True),
        sa.Column("request_id", sa.String(length=64), nullable=True),
        sa.Column("operation_detail", mysql.JSON(), nullable=True),
        sa.Column(
            "created_at",
            mysql.DATETIME(fsp=3),
            server_default=sa.text("CURRENT_TIMESTAMP(3)"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "action_type IN ("
            "'LOGIN', 'LOGOUT', 'VIEW_DOCUMENT', 'DOWNLOAD_DOCUMENT', "
            "'UPLOAD_DOCUMENT', 'BATCH_IMPORT', 'EDIT_DOCUMENT', "
            "'DELETE_DOCUMENT', 'CREATE_CATEGORY', 'EDIT_CATEGORY', "
            "'DELETE_CATEGORY', 'CHANGE_PERMISSION', 'BIND_ATTACHMENT', "
            "'UNBIND_ATTACHMENT'"
            ")",
            name="audit_action_type",
        ),
        sa.CheckConstraint(
            "target_type IS NULL OR target_type IN ("
            "'AUTH', 'CATEGORY', 'DOCUMENT', 'ATTACHMENT', "
            "'PERMISSION', 'ATTACHMENT_BINDING'"
            ")",
            name="audit_target_type",
        ),
        sa.CheckConstraint(
            "operation_result IN ('SUCCESS', 'FAILURE')",
            name="audit_operation_result",
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_sys_audit_log_created_at",
        "sys_audit_log",
        ["created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_user_created",
        "sys_audit_log",
        ["user_id", "created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_organization_created",
        "sys_audit_log",
        ["organization_id", "created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_action_created",
        "sys_audit_log",
        ["action_type", "created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_target_created",
        "sys_audit_log",
        ["target_type", "target_id", "created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_result_created",
        "sys_audit_log",
        ["operation_result", "created_at"],
    )
    op.create_index(
        "ix_sys_audit_log_request_id",
        "sys_audit_log",
        ["request_id"],
    )


def downgrade() -> None:
    op.drop_table("sys_audit_log")
    op.drop_table("doc_permission")
    op.drop_table("doc_attachment_binding")
    op.drop_table("doc_document")
    op.drop_table("doc_category")
    op.drop_table("sys_user")
    op.drop_table("sys_organization")