"""create profiles table

Revision ID: d3ff04145fbc
Revises: 92cbdc762a0f
Create Date: 2025-02-13 10:41:54.218555

"""

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

# revision identifiers, used by Alembic.
revision = "d3ff04145fbc"
down_revision = "92cbdc762a0f"
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "profile_item",
        sa.Column(
            "id",
            mysql.BIGINT(),
            autoincrement=True,
            nullable=False,
            comment="Unique ID",
        ),
        sa.Column(
            "profile_id", sa.String(length=36), nullable=False, comment="Profile ID"
        ),
        sa.Column(
            "parent_id",
            sa.String(length=36),
            nullable=False,
            comment="Parent ID: now is shifu_bid",
        ),
        sa.Column(
            "profile_index", sa.Integer(), nullable=False, comment="Profile index"
        ),
        sa.Column(
            "profile_key", sa.String(length=255), nullable=False, comment="Profile key"
        ),
        sa.Column("profile_type", sa.Integer(), nullable=False),
        sa.Column("profile_value_type", sa.Integer(), nullable=False),
        sa.Column("profile_show_type", sa.Integer(), nullable=False),
        sa.Column(
            "profile_remark", sa.Text(), nullable=False, comment="Profile remark"
        ),
        sa.Column("created", sa.TIMESTAMP(), nullable=False, comment="Creation time"),
        sa.Column("updated", sa.TIMESTAMP(), nullable=False, comment="Update time"),
        sa.Column(
            "status",
            sa.Integer(),
            nullable=False,
            comment="0 for deleted, 1 for active",
        ),
        sa.Column(
            "created_by", sa.String(length=36), nullable=False, comment="Created by"
        ),
        sa.Column(
            "updated_by", sa.String(length=36), nullable=False, comment="Updated by"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("profile_id"),
    )
    with op.batch_alter_table("profile_item", schema=None) as batch_op:
        batch_op.create_index(
            batch_op.f("ix_profile_item_parent_id"), ["parent_id"], unique=False
        )
        batch_op.create_index(
            batch_op.f("ix_profile_item_profile_key"), ["profile_key"], unique=False
        )

    op.create_table(
        "profile_item_i18n",
        sa.Column(
            "id",
            mysql.BIGINT(),
            autoincrement=True,
            nullable=False,
            comment="Unique ID",
        ),
        sa.Column(
            "parent_id", sa.String(length=36), nullable=False, comment="parent_id"
        ),
        sa.Column("conf_type", sa.Integer(), nullable=False),
        sa.Column(
            "language", sa.String(length=255), nullable=False, comment="Language"
        ),
        sa.Column(
            "profile_item_remark",
            sa.Text(),
            nullable=False,
            comment="Profile item remark",
        ),
        sa.Column("created", sa.TIMESTAMP(), nullable=False, comment="Creation time"),
        sa.Column("updated", sa.TIMESTAMP(), nullable=False, comment="Update time"),
        sa.Column(
            "status",
            sa.Integer(),
            nullable=False,
            comment="0 for deleted, 1 for active",
        ),
        sa.Column(
            "created_by", sa.String(length=36), nullable=False, comment="Created by"
        ),
        sa.Column(
            "updated_by", sa.String(length=36), nullable=False, comment="Updated by"
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    with op.batch_alter_table("profile_item_i18n", schema=None) as batch_op:
        batch_op.create_index(
            batch_op.f("ix_profile_item_i18n_language"), ["language"], unique=False
        )
        batch_op.create_index(
            batch_op.f("ix_profile_item_i18n_parent_id"), ["parent_id"], unique=False
        )

    op.create_table(
        "profile_item_value",
        sa.Column(
            "id",
            mysql.BIGINT(),
            autoincrement=True,
            nullable=False,
            comment="Unique ID",
        ),
        sa.Column(
            "profile_id", sa.String(length=36), nullable=False, comment="Profile ID"
        ),
        sa.Column(
            "profile_item_id",
            sa.String(length=36),
            nullable=False,
            comment="Profile item ID",
        ),
        sa.Column("profile_value", sa.Text(), nullable=False, comment="Profile value"),
        sa.Column("created", sa.TIMESTAMP(), nullable=False, comment="Creation time"),
        sa.Column("updated", sa.TIMESTAMP(), nullable=False, comment="Update time"),
        sa.Column(
            "status",
            sa.Integer(),
            nullable=False,
            comment="0 for deleted, 1 for active",
        ),
        sa.Column(
            "created_by", sa.String(length=36), nullable=False, comment="Created by"
        ),
        sa.Column(
            "updated_by", sa.String(length=36), nullable=False, comment="Updated by"
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    with op.batch_alter_table("profile_item_value", schema=None) as batch_op:
        batch_op.create_index(
            batch_op.f("ix_profile_item_value_profile_id"), ["profile_id"], unique=False
        )
        batch_op.create_index(
            batch_op.f("ix_profile_item_value_profile_item_id"),
            ["profile_item_id"],
            unique=False,
        )

    with op.batch_alter_table("user_profile", schema=None) as batch_op:
        batch_op.add_column(
            sa.Column(
                "profile_id", sa.String(length=36), nullable=False, comment="Profile ID"
            )
        )
        batch_op.alter_column(
            "profile_type",
            existing_type=mysql.INTEGER(display_width=11),
            comment="",
            existing_comment="profile type: 2900=input_unconf, 2901=input_text, 2902=input_number, 2903=input_select, 2904=input_sex, 2905=input_date",
            existing_nullable=False,
            existing_server_default=sa.text("'0'"),
        )
        batch_op.create_index(
            batch_op.f("ix_user_profile_profile_id"), ["profile_id"], unique=False
        )
        batch_op.create_index(
            batch_op.f("ix_user_profile_profile_key"), ["profile_key"], unique=False
        )
        batch_op.create_index(
            batch_op.f("ix_user_profile_user_id"), ["user_id"], unique=False
        )

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("user_profile", schema=None) as batch_op:
        batch_op.drop_index(batch_op.f("ix_user_profile_user_id"))
        batch_op.drop_index(batch_op.f("ix_user_profile_profile_key"))
        batch_op.drop_index(batch_op.f("ix_user_profile_profile_id"))
        batch_op.alter_column(
            "profile_type",
            existing_type=mysql.INTEGER(display_width=11),
            comment="profile type: 2900=input_unconf, 2901=input_text, 2902=input_number, 2903=input_select, 2904=input_sex, 2905=input_date",
            existing_comment="",
            existing_nullable=False,
            existing_server_default=sa.text("'0'"),
        )
        batch_op.drop_column("profile_id")

    with op.batch_alter_table("profile_item_value", schema=None) as batch_op:
        batch_op.drop_index(batch_op.f("ix_profile_item_value_profile_item_id"))
        batch_op.drop_index(batch_op.f("ix_profile_item_value_profile_id"))

    op.drop_table("profile_item_value")
    with op.batch_alter_table("profile_item_i18n", schema=None) as batch_op:
        batch_op.drop_index(batch_op.f("ix_profile_item_i18n_parent_id"))
        batch_op.drop_index(batch_op.f("ix_profile_item_i18n_language"))

    op.drop_table("profile_item_i18n")
    with op.batch_alter_table("profile_item", schema=None) as batch_op:
        batch_op.drop_index(batch_op.f("ix_profile_item_profile_key"))
        batch_op.drop_index(batch_op.f("ix_profile_item_parent_id"))

    op.drop_table("profile_item")
    # ### end Alembic commands ###
