zhifu-platform/migrations/versions/0002_auth_after_sales.py

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.mysql import BIGINT, BINARY, DATETIME

revision = "0002_auth_after_sales"
down_revision = "0001_identity_foundation"
branch_labels = None
depends_on = None


def identity(name, primary=False):
    return sa.Column(name, BINARY(16), nullable=False, primary_key=primary)


def number(name, primary=False):
    return sa.Column(name, BIGINT(unsigned=True), nullable=False, primary_key=primary)


def varchar(name, length, nullable=False, primary=False, collation=None):
    return sa.Column(
        name, sa.String(length, collation=collation), nullable=nullable, primary_key=primary
    )


def fk(local, remote):
    return sa.ForeignKeyConstraint(local, remote)


def upgrade():
    op.create_table(
        "principals",
        identity("id", True),
        identity("tenant_id"),
        varchar("username", 128, collation="utf8mb4_bin"),
        varchar("password_hash", 255),
        varchar("kind", 16),
        varchar("status", 16),
        sa.UniqueConstraint("tenant_id", "id", name="uq_principals_tenant_id_id"),
        sa.UniqueConstraint("tenant_id", "username", name="uq_principals_tenant_username"),
        fk(["tenant_id"], ["tenants.id"]),
        sa.CheckConstraint(
            "kind IN ('CUSTOMER', 'STAFF', 'SERVICE')", name=op.f("ck_principals_kind")
        ),
        sa.CheckConstraint("status IN ('ACTIVE', 'SUSPENDED')", name=op.f("ck_principals_status")),
    )
    op.create_table(
        "access_sessions",
        sa.Column("token_hash", BINARY(32), primary_key=True),
        identity("tenant_id"),
        identity("principal_id"),
        sa.Column("expires_at", DATETIME(fsp=6), nullable=False),
        sa.Column("revoked", sa.Boolean(), nullable=False),
        fk(["tenant_id", "principal_id"], ["principals.tenant_id", "principals.id"]),
    )
    op.create_table(
        "store_grants",
        identity("tenant_id", True),
        identity("principal_id", True),
        identity("store_id", True),
        varchar("permission", 64, primary=True),
        fk(["tenant_id", "principal_id"], ["principals.tenant_id", "principals.id"]),
        fk(["tenant_id", "store_id"], ["stores.tenant_id", "stores.id"]),
    )
    op.create_table(
        "order_projections",
        identity("id", True),
        identity("tenant_id"),
        identity("store_id"),
        identity("subject_id"),
        varchar("source_system", 64),
        number("source_version"),
        varchar("item_id", 128),
        varchar("product_type", 64),
        sa.Column("available_quantity", sa.Integer(), nullable=False),
        sa.Column("signed_at", DATETIME(fsp=6), nullable=True),
        sa.Column("observed_at", DATETIME(fsp=6), nullable=False),
        sa.UniqueConstraint("tenant_id", "id", name="uq_order_projections_tenant_id_id"),
        fk(["tenant_id", "store_id"], ["stores.tenant_id", "stores.id"]),
        fk(["tenant_id", "subject_id"], ["principals.tenant_id", "principals.id"]),
        sa.CheckConstraint("available_quantity >= 0", name=op.f("ck_order_projections_quantity")),
    )
    op.create_table(
        "exchange_rules",
        identity("tenant_id", True),
        identity("store_id", True),
        number("version", True),
        sa.Column("window_days", sa.Integer(), nullable=False),
        sa.Column("product_types", sa.JSON(), nullable=False),
        varchar("citation", 2000),
        sa.Column("revoked", sa.Boolean(), nullable=False),
        fk(["tenant_id", "store_id"], ["stores.tenant_id", "stores.id"]),
        sa.CheckConstraint("window_days > 0", name=op.f("ck_exchange_rules_window")),
    )
    op.create_table(
        "tickets",
        identity("id", True),
        identity("tenant_id"),
        identity("store_id"),
        identity("subject_id"),
        identity("order_id"),
        identity("created_by"),
        varchar("status", 32),
        number("version"),
        number("proposal_version"),
        sa.Column("created_at", DATETIME(fsp=6), nullable=False),
        sa.Column("updated_at", DATETIME(fsp=6), nullable=False),
        sa.UniqueConstraint("tenant_id", "id", name="uq_tickets_tenant_id_id"),
        fk(["tenant_id", "store_id"], ["stores.tenant_id", "stores.id"]),
        fk(["tenant_id", "subject_id"], ["principals.tenant_id", "principals.id"]),
        fk(["tenant_id", "order_id"], ["order_projections.tenant_id", "order_projections.id"]),
        sa.CheckConstraint(
            "status IN ('DRAFT','WAITING_REVIEW','APPROVED','REJECTED','CANCELLED')",
            name=op.f("ck_tickets_status"),
        ),
    )
    op.create_index(
        "ix_tickets_tenant_subject_created",
        "tickets",
        ["tenant_id", "subject_id", "created_at", "id"],
    )
    op.create_table(
        "proposal_versions",
        identity("tenant_id", True),
        identity("proposal_id", True),
        number("version", True),
        sa.Column("quantity", sa.Integer(), nullable=False),
        varchar("reason", 2000),
        number("rule_version"),
        number("order_version"),
        varchar("citation", 2000),
        fk(["tenant_id", "proposal_id"], ["tickets.tenant_id", "tickets.id"]),
        sa.CheckConstraint("quantity > 0", name=op.f("ck_proposal_versions_quantity")),
    )
    proposal_fk = [
        "proposal_versions.tenant_id",
        "proposal_versions.proposal_id",
        "proposal_versions.version",
    ]
    op.create_table(
        "confirmations",
        identity("tenant_id", True),
        identity("proposal_id", True),
        number("proposal_version", True),
        identity("subject_id"),
        sa.Column("confirmed_at", DATETIME(fsp=6), nullable=False),
        fk(["tenant_id", "proposal_id", "proposal_version"], proposal_fk),
    )
    op.create_table(
        "approvals",
        identity("id", True),
        identity("tenant_id"),
        identity("proposal_id"),
        number("proposal_version"),
        identity("submitted_by"),
        varchar("status", 32),
        sa.Column("decided_by", BINARY(16), nullable=True),
        varchar("reason", 2000, nullable=True),
        sa.UniqueConstraint(
            "tenant_id", "proposal_id", "proposal_version", name="uq_approvals_proposal"
        ),
        fk(["tenant_id", "proposal_id", "proposal_version"], proposal_fk),
    )
    op.create_table(
        "command_receipts",
        identity("tenant_id", True),
        identity("actor_id", True),
        varchar("route", 128, primary=True),
        varchar("key", 64, primary=True, collation="utf8mb4_bin"),
        sa.Column("request_hash", BINARY(32), nullable=False),
        sa.Column("response", sa.JSON(), nullable=False),
    )
    op.create_table(
        "business_events",
        identity("id", True),
        identity("tenant_id"),
        identity("actor_id"),
        identity("aggregate_id"),
        number("aggregate_version"),
        varchar("event_type", 64),
        varchar("request_id", 36),
        sa.Column("payload", sa.JSON(), nullable=False),
        sa.Column("occurred_at", DATETIME(fsp=6), nullable=False),
        varchar("delivery_status", 16),
    )
    op.create_index(
        "ix_business_events_delivery", "business_events", ["delivery_status", "occurred_at", "id"]
    )


def downgrade():
    for name in (
        "business_events",
        "command_receipts",
        "approvals",
        "confirmations",
        "proposal_versions",
        "tickets",
        "exchange_rules",
        "order_projections",
        "store_grants",
        "access_sessions",
        "principals",
    ):
        op.drop_table(name)