"""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),
        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=120), nullable=False),
        sa.Column("business_segment", sa.String(length=100), nullable=False),
        sa.Column("contact_name", sa.String(length=100), nullable=False),
        sa.Column("contact_phone", sa.String(length=20), nullable=False),
        sa.Column("ord_id", sa.String(length=40), nullable=False),
        sa.Column("bundle_type", sa.String(length=50), nullable=False),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="pending"),
        sa.Column("primary_document_id", sa.BigInteger(), nullable=True),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False),
        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),
        sa.Column("bundle_id", sa.BigInteger(), nullable=False),
        sa.Column("contract_type", sa.String(length=50), nullable=False),
        sa.Column("contract_name", sa.String(length=100), nullable=False),
        sa.Column("is_primary", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("status", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("sign_flow_id", sa.String(length=64), nullable=False),
        sa.Column("file_id", sa.String(length=64), nullable=False),
        sa.Column("template_url", mysql.LONGTEXT(), nullable=False),
        sa.Column("sign_url", mysql.LONGTEXT(), nullable=False),
        sa.Column("download_url", mysql.LONGTEXT(), nullable=False),
        sa.Column("signing_time", sa.DateTime(), nullable=True),
        sa.Column("effective_time", sa.DateTime(), nullable=True),
        sa.Column("expiry_time", sa.DateTime(), nullable=True),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False),
        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),
        sa.Column("bundle_id", sa.BigInteger(), nullable=True),
        sa.Column("document_id", sa.BigInteger(), nullable=True),
        sa.Column("sign_flow_id", sa.String(length=64), nullable=False),
        sa.Column("event_type", sa.String(length=50), nullable=False),
        sa.Column("payload_json", mysql.LONGTEXT(), nullable=False),
        sa.Column("created_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_time", sa.DateTime(), server_default=sa.text("now()"), nullable=False),
        sa.Column("delete_flag", sa.Integer(), server_default="0", nullable=False),
        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")