"""create contract center tables

Revision ID: a7d4b3e21c10
Revises: 984d88f91c0d
Create Date: 2026-03-28 00:00:00.000000

"""
from typing import Sequence, Union

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

revision: str = "a7d4b3e21c10"
down_revision: Union[str, Sequence[str], None] = "984d88f91c0d"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute("DROP TABLE IF EXISTS contract_event")
    op.execute("DROP TABLE IF EXISTS contract_document")
    op.execute("DROP TABLE IF EXISTS contract_bundle")
    op.create_table(
        "contract_bundle",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False, comment="合同包唯一标识"),
        sa.Column("subject_type", sa.String(length=20), nullable=False, comment="签约主体类型(store/lawyer)"),
        sa.Column("subject_id", sa.BigInteger(), nullable=False, comment="签约主体ID，关联门店/律师业务主键"),
        sa.Column("subject_name", sa.String(length=120), nullable=False, comment="签约主体名称"),
        sa.Column("business_segment", sa.String(length=100), nullable=False, comment="业务板块"),
        sa.Column("contact_name", sa.String(length=100), nullable=False, comment="联系人姓名"),
        sa.Column("contact_phone", sa.String(length=20), nullable=False, comment="联系人手机号"),
        sa.Column("ord_id", sa.String(length=40), nullable=False, comment="组织标识(统一社会信用代码)"),
        sa.Column("bundle_type", sa.String(length=50), nullable=False, comment="合同包类型(STORE_STANDARD/LAWYER_STANDARD)"),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="未签署", comment="整体状态: 未签署/审核中/已签署"),
        sa.Column("primary_document_id", sa.BigInteger(), nullable=True, comment="主合同文档ID"),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="创建时间"),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="更新时间"),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False, comment="逻辑删除(0未删/1已删)"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("idx_contract_bundle_subject", "contract_bundle", ["subject_type", "subject_id"], unique=False)
    op.create_index("idx_contract_bundle_status", "contract_bundle", ["status"], unique=False)

    op.create_table(
        "contract_document",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False, comment="合同文档唯一标识"),
        sa.Column("bundle_id", sa.BigInteger(), nullable=False, comment="所属合同包ID"),
        sa.Column("contract_type", sa.String(length=50), nullable=False, comment="合同类型编码(store_agreement/alipay_auth等)"),
        sa.Column("contract_name", sa.String(length=100), nullable=False, comment="合同展示名称"),
        sa.Column("is_primary", sa.Integer(), nullable=False, server_default="0", comment="是否主合同(1是/0否)"),
        sa.Column("status", sa.Integer(), nullable=False, server_default="0", comment="签署状态(0未签署/1已签署)"),
        sa.Column("sign_flow_id", sa.String(length=64), nullable=False, comment="e签宝签署流程ID"),
        sa.Column("file_id", sa.String(length=64), nullable=False, comment="e签宝文件ID"),
        sa.Column("template_url", mysql.LONGTEXT(), nullable=False, comment="合同模板预览URL"),
        sa.Column("sign_url", mysql.LONGTEXT(), nullable=False, comment="签署链接URL"),
        sa.Column("download_url", mysql.LONGTEXT(), nullable=False, comment="签署完成后的下载URL"),
        sa.Column("signing_time", sa.DateTime(), nullable=True, comment="签署时间"),
        sa.Column("effective_time", sa.DateTime(), nullable=True, comment="合同生效时间"),
        sa.Column("expiry_time", sa.DateTime(), nullable=True, comment="合同到期时间"),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="创建时间"),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="更新时间"),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False, comment="逻辑删除(0未删/1已删)"),
        sa.ForeignKeyConstraint(["bundle_id"], ["contract_bundle.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("idx_contract_document_bundle_id", "contract_document", ["bundle_id"], unique=False)
    op.create_index("idx_contract_document_sign_flow_id", "contract_document", ["sign_flow_id"], unique=True)
    op.create_index("idx_contract_document_bundle_type_unique", "contract_document", ["bundle_id", "contract_type"], unique=True)

    op.create_table(
        "contract_event",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False, comment="事件唯一标识"),
        sa.Column("bundle_id", sa.BigInteger(), nullable=True, comment="关联合同包ID"),
        sa.Column("document_id", sa.BigInteger(), nullable=True, comment="关联合同文档ID"),
        sa.Column("sign_flow_id", sa.String(length=64), nullable=False, comment="e签宝签署流程ID"),
        sa.Column("event_type", sa.String(length=50), nullable=False, comment="事件类型(esign_callback:{action})"),
        sa.Column("payload_json", mysql.LONGTEXT(), nullable=False, comment="e签宝回调原始JSON报文"),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="创建时间"),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False, comment="更新时间"),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False, comment="逻辑删除(0未删/1已删)"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("idx_contract_event_sign_flow_id", "contract_event", ["sign_flow_id"], unique=False)


def downgrade() -> None:
    op.drop_index("idx_contract_event_sign_flow_id", table_name="contract_event")
    op.drop_table("contract_event")
    op.drop_index("idx_contract_document_bundle_type_unique", table_name="contract_document")
    op.drop_index("idx_contract_document_sign_flow_id", table_name="contract_document")
    op.drop_index("idx_contract_document_bundle_id", table_name="contract_document")
    op.drop_table("contract_document")
    op.drop_index("idx_contract_bundle_status", table_name="contract_bundle")
    op.drop_index("idx_contract_bundle_subject", table_name="contract_bundle")
    op.drop_table("contract_bundle")