"""Initial schema for MySQL/TiDB.

Revision ID: 001
Revises:
Create Date: 2026-03-20
"""

from alembic import op
import sqlalchemy as sa

revision = "001"
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── Identity ──
    op.create_table(
        "identity_persons",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("source_system", sa.String(32), nullable=False, server_default="successfactors"),
        sa.Column("hris_person_id", sa.String(128), nullable=False),
        sa.Column("employee_no", sa.String(64), nullable=True),
        sa.Column("work_email", sa.String(255), nullable=False),
        sa.Column("display_name", sa.String(128), nullable=True),
        sa.Column("avatar_url", sa.String(512), nullable=True),
        sa.Column("employment_status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("is_china_employee", sa.Boolean(), nullable=False, server_default=sa.text("0")),
        sa.Column("manager_person_id", sa.String(36), nullable=True),
        sa.Column("work_location_code", sa.String(64), nullable=True),
        sa.Column("work_location_name", sa.String(128), nullable=True),
        sa.Column("company_code", sa.String(64), nullable=True),
        sa.Column("company_name", sa.String(128), nullable=True),
        sa.Column("hire_date", sa.Date(), nullable=True),
        sa.Column("termination_date", sa.Date(), nullable=True),
        sa.Column("password_hash", sa.String(255), nullable=True),
        sa.Column("last_login_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("source_updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["manager_person_id"], ["identity_persons.id"]),
        sa.UniqueConstraint("source_system", "hris_person_id", name="uq_identity_persons_source"),
        sa.UniqueConstraint("employee_no", name="uq_identity_persons_employee_no"),
    )
    op.create_index("idx_identity_persons_manager", "identity_persons", ["manager_person_id"])
    op.create_index("idx_identity_persons_status", "identity_persons", ["employment_status"])

    op.create_table(
        "identity_org_memberships",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("person_id", sa.String(36), nullable=False),
        sa.Column("source_system", sa.String(32), nullable=False, server_default="successfactors"),
        sa.Column("org_node_source_id", sa.String(128), nullable=False),
        sa.Column("org_node_type", sa.String(32), nullable=False, server_default="department"),
        sa.Column("org_node_code", sa.String(64), nullable=True),
        sa.Column("org_node_name", sa.String(128), nullable=False),
        sa.Column("parent_org_node_source_id", sa.String(128), nullable=True),
        sa.Column("org_path", sa.Text(), nullable=True),
        sa.Column("is_primary", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("membership_status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("source_updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"]),
        sa.UniqueConstraint("person_id", "org_node_source_id", "org_node_type", name="uq_identity_org_membership"),
    )
    op.create_index("idx_identity_org_memberships_person", "identity_org_memberships", ["person_id"])
    op.create_index("idx_identity_org_memberships_org_node", "identity_org_memberships", ["org_node_source_id"])
    op.create_index("idx_identity_org_memberships_primary", "identity_org_memberships", ["person_id", "is_primary"])
    op.create_index("idx_identity_org_memberships_parent", "identity_org_memberships", ["parent_org_node_source_id"])

    op.create_table(
        "identity_org_nodes",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("source_system", sa.String(32), nullable=False, server_default="successfactors"),
        sa.Column("org_node_source_id", sa.String(128), nullable=False),
        sa.Column("org_node_type", sa.String(32), nullable=False, server_default="department"),
        sa.Column("org_node_code", sa.String(64), nullable=True),
        sa.Column("org_node_name", sa.String(128), nullable=False),
        sa.Column("parent_org_node_source_id", sa.String(128), nullable=True),
        sa.Column("org_path", sa.Text(), nullable=True),
        sa.Column("org_level", sa.Integer(), nullable=True),
        sa.Column("node_status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("source_updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("source_system", "org_node_source_id", "org_node_type", name="uq_identity_org_nodes_source"),
    )
    op.create_index("idx_identity_org_nodes_parent", "identity_org_nodes", ["parent_org_node_source_id"])
    op.create_index("idx_identity_org_nodes_type", "identity_org_nodes", ["org_node_type"])

    op.create_table(
        "identity_external_identities",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("person_id", sa.String(36), nullable=False),
        sa.Column("provider", sa.String(32), nullable=False),
        sa.Column("provider_subject", sa.String(255), nullable=False),
        sa.Column("provider_username", sa.String(255), nullable=True),
        sa.Column("provider_email", sa.String(255), nullable=True),
        sa.Column("is_login_identity", sa.Boolean(), nullable=False, server_default=sa.text("0")),
        sa.Column("identity_status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("source_updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"]),
        sa.UniqueConstraint("provider", "provider_subject", name="uq_identity_external_provider_subject"),
        sa.UniqueConstraint("person_id", "provider", name="uq_identity_external_person_provider"),
    )
    op.create_index("idx_identity_external_person", "identity_external_identities", ["person_id"])

    # ── Auth / RBAC ──
    op.create_table(
        "auth_roles",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("name", sa.String(32), nullable=False, unique=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("is_system", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "auth_user_roles",
        sa.Column("user_id", sa.String(36), nullable=False),
        sa.Column("role_id", sa.String(36), nullable=False),
        sa.Column("granted_by", sa.String(36), nullable=True),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("user_id", "role_id"),
        sa.ForeignKeyConstraint(["user_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["role_id"], ["auth_roles.id"]),
        sa.ForeignKeyConstraint(["granted_by"], ["identity_persons.id"]),
    )

    op.create_table(
        "auth_permissions",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("resource", sa.String(64), nullable=False),
        sa.Column("action", sa.String(32), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("resource", "action"),
    )

    op.create_table(
        "auth_role_permissions",
        sa.Column("role_id", sa.String(36), nullable=False),
        sa.Column("permission_id", sa.String(36), nullable=False),
        sa.PrimaryKeyConstraint("role_id", "permission_id"),
        sa.ForeignKeyConstraint(["role_id"], ["auth_roles.id"]),
        sa.ForeignKeyConstraint(["permission_id"], ["auth_permissions.id"]),
    )

    op.create_table(
        "auth_entitlements",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("user_id", sa.String(36), nullable=False),
        sa.Column("resource_type", sa.String(64), nullable=False),
        sa.Column("resource_id", sa.String(128), nullable=False),
        sa.Column("privilege", sa.String(128), nullable=False),
        sa.Column("source_type", sa.String(32), nullable=False),
        sa.Column("source_ref", sa.String(128), nullable=True),
        sa.Column("status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["user_id"], ["identity_persons.id"]),
    )
    op.create_index("ix_auth_entitlements_user_id", "auth_entitlements", ["user_id"])

    # ── Audit ──
    op.create_table(
        "audit_logs",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("trace_id", sa.String(36), nullable=False),
        sa.Column("actor_user_id", sa.String(36), nullable=True),
        sa.Column("actor_type", sa.String(16), nullable=False),
        sa.Column("action", sa.String(64), nullable=False),
        sa.Column("resource_type", sa.String(64), nullable=True),
        sa.Column("resource_id", sa.String(128), nullable=True),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("detail", sa.JSON(), nullable=True),
        sa.Column("ip_address", sa.String(45), nullable=True),
        sa.Column("user_agent", sa.String(512), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_audit_logs_trace_id", "audit_logs", ["trace_id"])
    op.create_index("ix_audit_logs_action", "audit_logs", ["action"])
    op.create_index("ix_audit_logs_created_at", "audit_logs", ["created_at"])

    # ── Approval ──
    op.create_table(
        "approval_requests",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("external_id", sa.String(128), nullable=False, unique=True),
        sa.Column("approval_type", sa.String(64), nullable=False),
        sa.Column("applicant_id", sa.String(36), nullable=True),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("source_payload", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["applicant_id"], ["identity_persons.id"]),
    )

    op.create_table(
        "approval_steps",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("approval_id", sa.String(36), nullable=False),
        sa.Column("step_name", sa.String(128), nullable=False),
        sa.Column("approver_id", sa.String(36), nullable=True),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("acted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("detail", sa.JSON(), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["approval_id"], ["approval_requests.id"]),
        sa.ForeignKeyConstraint(["approver_id"], ["identity_persons.id"]),
    )

    # ── HR ──
    op.create_table(
        "hr_employees",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("employee_no", sa.String(64), nullable=False, unique=True),
        sa.Column("user_id", sa.String(36), nullable=False),
        sa.Column("hire_date", sa.Date(), nullable=True),
        sa.Column("leave_date", sa.Date(), nullable=True),
        sa.Column("status", sa.String(16), nullable=False, server_default="active"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["user_id"], ["identity_persons.id"]),
    )

    op.create_table(
        "hr_lifecycle_events",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("person_id", sa.String(36), nullable=False),
        sa.Column("event_type", sa.String(32), nullable=False),
        sa.Column("detail", sa.JSON(), nullable=True),
        sa.Column("effective_date", sa.Date(), nullable=False),
        sa.Column("workflow_id", sa.String(36), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"]),
    )

    # ── Asset: suppliers (must come before items/procurement for FK) ──
    op.create_table(
        "asset_suppliers",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("name", sa.String(128), nullable=False, unique=True),
        sa.Column("contact_name", sa.String(64), nullable=True),
        sa.Column("contact_phone", sa.String(32), nullable=True),
        sa.Column("remark", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "asset_legal_entities",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("name", sa.String(128), nullable=False, unique=True),
        sa.Column("country_or_region", sa.String(64), nullable=True),
        sa.Column("tax_identifier", sa.String(128), nullable=True),
        sa.Column("registered_address", sa.String(512), nullable=True),
        sa.Column("phone", sa.String(64), nullable=True),
        sa.Column("bank_name", sa.String(128), nullable=True),
        sa.Column("bank_account", sa.String(128), nullable=True),
        sa.Column("bank_account_name", sa.String(128), nullable=True),
        sa.Column("bank_address", sa.String(512), nullable=True),
        sa.Column("swift_code", sa.String(32), nullable=True),
        sa.Column("currency", sa.String(8), nullable=True),
        sa.Column("account_type", sa.String(64), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )

    # ── Asset: core tables ──
    op.create_table(
        "asset_items",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("asset_tag", sa.String(64), nullable=False, unique=True),
        sa.Column("asset_name", sa.String(128), nullable=True),
        sa.Column("category", sa.String(32), nullable=False),
        sa.Column("brand", sa.String(64), nullable=True),
        sa.Column("model", sa.String(128), nullable=True),
        sa.Column("serial_number", sa.String(128), nullable=True, unique=True),
        sa.Column("status", sa.String(32), nullable=False, server_default="idle"),
        sa.Column("device_status", sa.String(16), nullable=False, server_default="normal"),
        sa.Column("source_type", sa.String(16), nullable=True),
        sa.Column("order_number", sa.String(128), nullable=True),
        sa.Column("purchase_date", sa.Date(), nullable=True),
        sa.Column("purchase_cost", sa.Numeric(10, 2), nullable=True),
        sa.Column("currency", sa.String(8), nullable=True),
        sa.Column("invoice_number", sa.String(128), nullable=True),
        sa.Column("warranty_until", sa.Date(), nullable=True),
        sa.Column("usage_limit_months", sa.Integer(), nullable=True),
        sa.Column("supplier_name", sa.String(128), nullable=True),
        sa.Column("supplier_contact", sa.String(128), nullable=True),
        sa.Column("supplier_phone", sa.String(64), nullable=True),
        sa.Column("maintenance_note", sa.Text(), nullable=True),
        sa.Column("location", sa.String(256), nullable=True),
        sa.Column("admin_id", sa.String(36), nullable=True),
        sa.Column("assigned_to", sa.String(36), nullable=True),
        sa.Column("assigned_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("disposal_type", sa.String(32), nullable=True),
        sa.Column("disposed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("image_urls", sa.JSON(), nullable=True),
        sa.Column("custom_fields", sa.JSON(), nullable=True),
        sa.Column("epandian_id", sa.BigInteger(), nullable=True),
        sa.Column("owner_company", sa.String(128), nullable=True),
        sa.Column("usage_company", sa.String(128), nullable=True),
        sa.Column("expected_return_date", sa.Date(), nullable=True),
        sa.Column("repair_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("repair_total_cost", sa.Numeric(10, 2), nullable=False, server_default=sa.text("0")),
        sa.Column("print_status", sa.String(16), nullable=True),
        sa.Column("rfid", sa.String(128), nullable=True),
        sa.Column("purchase_entity", sa.String(128), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["admin_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["assigned_to"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_locations",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("code", sa.String(16), nullable=False, unique=True),
        sa.Column("name", sa.String(128), nullable=False),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "asset_category_configs",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("code", sa.String(16), nullable=False, unique=True),
        sa.Column("name", sa.String(128), nullable=False),
        sa.Column("parent_code", sa.String(16), nullable=True),
        sa.Column("usage_limit_months", sa.Integer(), nullable=True),
        sa.Column("unit", sa.String(32), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "asset_tag_sequences",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("location_code", sa.String(16), nullable=False),
        sa.Column("category_code", sa.String(16), nullable=False),
        sa.Column("year_month", sa.String(6), nullable=False),
        sa.Column("last_seq", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "asset_history",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("asset_id", sa.String(36), nullable=False),
        sa.Column("action", sa.String(32), nullable=False),
        sa.Column("from_user", sa.String(36), nullable=True),
        sa.Column("to_user", sa.String(36), nullable=True),
        sa.Column("operator_id", sa.String(36), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["asset_id"], ["asset_items.id"]),
        sa.ForeignKeyConstraint(["from_user"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["to_user"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["operator_id"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_operations",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("operation_number", sa.String(64), nullable=False, unique=True),
        sa.Column("asset_id", sa.String(36), nullable=False),
        sa.Column("operation_type", sa.String(32), nullable=False),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
        sa.Column("from_user_id", sa.String(36), nullable=True),
        sa.Column("to_user_id", sa.String(36), nullable=True),
        sa.Column("operator_id", sa.String(36), nullable=False),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("detail", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("confirmed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("confirmed_by", sa.String(36), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["asset_id"], ["asset_items.id"]),
        sa.ForeignKeyConstraint(["from_user_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["to_user_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["operator_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["confirmed_by"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_procurement_orders",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("order_number", sa.String(64), nullable=False, unique=True),
        sa.Column("order_type", sa.String(16), nullable=False),
        sa.Column("status", sa.String(16), nullable=False, server_default="draft"),
        sa.Column("applicant_id", sa.String(36), nullable=False),
        sa.Column("supplier_id", sa.String(36), nullable=True),
        sa.Column("department", sa.String(128), nullable=True),
        sa.Column("supplier_name", sa.String(128), nullable=True),
        sa.Column("total_amount", sa.Numeric(12, 2), nullable=True),
        sa.Column("items", sa.JSON(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("requested_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("approved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("approved_by", sa.String(36), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["applicant_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], ["asset_suppliers.id"]),
        sa.ForeignKeyConstraint(["approved_by"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_software_catalog",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("name", sa.String(128), nullable=False),
        sa.Column("asset_type", sa.String(64), nullable=True),
        sa.Column("owner", sa.String(64), nullable=True),
        sa.Column("owner_person_id", sa.String(36), nullable=True),
        sa.Column("it_contact", sa.String(64), nullable=True),
        sa.Column("it_contact_person_id", sa.String(36), nullable=True),
        sa.Column("status", sa.String(32), nullable=False, server_default="active"),
        sa.Column("remark", sa.Text(), nullable=True),
        sa.Column("department", sa.String(128), nullable=True),
        sa.Column("login_url", sa.String(512), nullable=True),
        sa.Column("expiry_date", sa.Date(), nullable=True),
        sa.Column("license_info", sa.Text(), nullable=True),
        sa.Column("attachment", sa.String(256), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["owner_person_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["it_contact_person_id"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_business_payments",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("software_catalog_id", sa.String(36), nullable=True),
        sa.Column("procurement_order_id", sa.String(36), nullable=True),
        sa.Column("supplier_id", sa.String(36), nullable=True),
        sa.Column("software_name", sa.String(128), nullable=False),
        sa.Column("software_type", sa.String(64), nullable=True),
        sa.Column("payment_frequency", sa.String(64), nullable=True),
        sa.Column("payment_method", sa.String(64), nullable=True),
        sa.Column("signing_entity", sa.String(128), nullable=True),
        sa.Column("po_number", sa.String(64), nullable=True),
        sa.Column("contract_expiry", sa.String(128), nullable=True),
        sa.Column("requester", sa.String(64), nullable=True),
        sa.Column("requester_person_id", sa.String(36), nullable=True),
        sa.Column("amount", sa.Numeric(12, 2), nullable=True),
        sa.Column("remark", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["software_catalog_id"], ["asset_software_catalog.id"]),
        sa.ForeignKeyConstraint(["procurement_order_id"], ["asset_procurement_orders.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], ["asset_suppliers.id"]),
        sa.ForeignKeyConstraint(["requester_person_id"], ["identity_persons.id"]),
    )

    op.create_table(
        "asset_office_addresses",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("city", sa.String(64), nullable=False, unique=True),
        sa.Column("office_name", sa.String(128), nullable=True),
        sa.Column("country_or_region", sa.String(64), nullable=True),
        sa.Column("contact_name", sa.String(64), nullable=True),
        sa.Column("contact_phone", sa.String(32), nullable=True),
        sa.Column("address", sa.String(512), nullable=True),
        sa.Column("address_en", sa.String(512), nullable=True),
        sa.Column("admin_person_id", sa.String(36), nullable=True),
        sa.Column("legal_entity_id", sa.String(36), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["admin_person_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["legal_entity_id"], ["asset_legal_entities.id"]),
    )

    op.create_table(
        "asset_employee_requests",
        sa.Column("id", sa.String(36), nullable=False),
        sa.Column("request_number", sa.String(64), nullable=False, unique=True),
        sa.Column("request_type", sa.String(32), nullable=False),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
        sa.Column("requester_id", sa.String(36), nullable=False),
        sa.Column("asset_id", sa.String(36), nullable=True),
        sa.Column("category", sa.String(32), nullable=True),
        sa.Column("target_user_id", sa.String(36), nullable=True),
        sa.Column("reason", sa.Text(), nullable=True),
        sa.Column("detail", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("processed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("processed_by", sa.String(36), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["requester_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["asset_id"], ["asset_items.id"]),
        sa.ForeignKeyConstraint(["target_user_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["processed_by"], ["identity_persons.id"]),
    )

    # ── Seed default roles ──
    op.execute("""
        INSERT INTO auth_roles (id, name, description, is_system) VALUES
        (REPLACE(UUID(), '-', ''), 'global_admin', 'Global administrator', 1),
        (REPLACE(UUID(), '-', ''), 'employee', 'Regular user', 1),
        (REPLACE(UUID(), '-', ''), 'auditor', 'Audit access', 1),
        (REPLACE(UUID(), '-', ''), 'cloud_admin', 'Cloud administrator', 1),
        (REPLACE(UUID(), '-', ''), 'asset_admin', 'Asset administrator', 1)
    """)


def downgrade() -> None:
    op.drop_table("asset_employee_requests")
    op.drop_table("asset_office_addresses")
    op.drop_table("asset_business_payments")
    op.drop_table("asset_software_catalog")
    op.drop_table("asset_procurement_orders")
    op.drop_table("asset_operations")
    op.drop_table("asset_history")
    op.drop_table("asset_tag_sequences")
    op.drop_table("asset_category_configs")
    op.drop_table("asset_locations")
    op.drop_table("asset_items")
    op.drop_table("asset_legal_entities")
    op.drop_table("asset_suppliers")
    op.drop_table("hr_lifecycle_events")
    op.drop_table("hr_employees")
    op.drop_table("approval_steps")
    op.drop_table("approval_requests")
    op.drop_table("audit_logs")
    op.drop_table("auth_entitlements")
    op.drop_table("auth_role_permissions")
    op.drop_table("auth_permissions")
    op.drop_table("auth_user_roles")
    op.drop_table("auth_roles")
    op.drop_table("identity_external_identities")
    op.drop_table("identity_org_nodes")
    op.drop_table("identity_org_memberships")
    op.drop_table("identity_persons")