"""baseline

Revision ID: 0001
Revises: 
Create Date: 2026-10-14 12:49:48.691204

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '0001'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('app_user',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('username', sa.String(length=50), nullable=False),
    sa.Column('email', sa.String(length=256), nullable=False),
    sa.Column('first_name', sa.String(length=100), nullable=False),
    sa.Column('last_name', sa.String(length=100), nullable=False),
    sa.Column('mobile', sa.String(length=100), nullable=False),
    sa.Column('password_hash', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('is_locked_out', sa.Boolean(), nullable=False),
    sa.Column('failed_attempts', sa.Integer(), nullable=False),
    sa.Column('failed_window_start', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('audit_log',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('user_id', sa.Uuid(), nullable=True),
    sa.Column('username', sa.String(length=50), nullable=False),
    sa.Column('action', sa.String(length=50), nullable=False),
    sa.Column('entity', sa.String(length=50), nullable=False),
    sa.Column('entity_id', sa.String(length=64), nullable=False),
    sa.Column('detail', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_audit_log_entity_id'), 'audit_log', ['entity_id'], unique=False)
    op.create_table('fw_Client',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Name', sa.String(length=50), nullable=False),
    sa.Column('Email', sa.String(length=100), nullable=False),
    sa.Column('MobilePhone', sa.String(length=50), nullable=False),
    sa.Column('Phone', sa.String(length=50), nullable=True),
    sa.Column('Phone1', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_ClientAccounts',
    sa.Column('Id', sa.String(length=128), nullable=False),
    sa.Column('UserName', sa.Text(), nullable=True),
    sa.Column('PasswordHash', sa.Text(), nullable=True),
    sa.Column('SecurityStamp', sa.Text(), nullable=True),
    sa.Column('ClientId', sa.Uuid(), nullable=True),
    sa.Column('Discriminator', sa.String(length=128), nullable=False),
    sa.PrimaryKeyConstraint('Id')
    )
    op.create_table('fw_ClientRoles',
    sa.Column('Id', sa.String(length=128), nullable=False),
    sa.Column('Name', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('Id')
    )
    op.create_table('fw_PackingSlip',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Reference', sa.String(length=50), nullable=False),
    sa.Column('Date', sa.DateTime(timezone=True), nullable=False),
    sa.Column('FromLocation', sa.String(length=256), nullable=False),
    sa.Column('FromRMP', sa.String(length=50), nullable=False),
    sa.Column('ToLocation', sa.String(length=256), nullable=False),
    sa.Column('ToRMP', sa.String(length=50), nullable=False),
    sa.Column('Completed', sa.DateTime(timezone=True), nullable=True),
    sa.Column('Operator', sa.String(length=100), nullable=True),
    sa.Column('Logs', sa.Text(), nullable=True),
    sa.Column('AwbNumber', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_Product',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Title', sa.String(length=256), nullable=False),
    sa.Column('DefaultUnit', sa.Integer(), nullable=False),
    sa.Column('AvailableUnit', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_ShippingParcels',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Barcode', sa.String(length=128), nullable=False),
    sa.Column('Comment', sa.Text(), nullable=True),
    sa.Column('BgColor', sa.String(length=16), nullable=True),
    sa.Column('FontColor', sa.String(length=16), nullable=True),
    sa.Column('CreatedUtc', sa.DateTime(timezone=True), nullable=False),
    sa.Column('ShippedUtc', sa.DateTime(timezone=True), nullable=True),
    sa.Column('DeletedUtc', sa.DateTime(timezone=True), nullable=True),
    sa.Column('OperatorId', sa.Uuid(), nullable=True),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_Supplier',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Name', sa.String(length=150), nullable=False),
    sa.Column('RMPNumber', sa.String(length=150), nullable=False),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_Template',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('Type', sa.String(length=50), nullable=False),
    sa.Column('Content', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('migration_legacy_value',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('table_name', sa.String(length=64), nullable=False),
    sa.Column('row_id', sa.String(length=64), nullable=False),
    sa.Column('column_name', sa.String(length=64), nullable=False),
    sa.Column('raw_value', sa.Text(), nullable=True),
    sa.Column('resolution', sa.String(length=32), nullable=False),
    sa.Column('reason', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('app_session',
    sa.Column('token_hash', sa.String(length=64), nullable=False),
    sa.Column('user_id', sa.Uuid(), nullable=False),
    sa.Column('csrf_token', sa.String(length=64), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('token_hash')
    )
    op.create_index(op.f('ix_app_session_user_id'), 'app_session', ['user_id'], unique=False)
    op.create_table('app_user_role',
    sa.Column('user_id', sa.Uuid(), nullable=False),
    sa.Column('role', sa.String(length=50), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id', 'role')
    )
    op.create_table('fw_ClientAccountClaims',
    sa.Column('Id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('ClaimType', sa.Text(), nullable=True),
    sa.Column('ClaimValue', sa.Text(), nullable=True),
    sa.Column('User_Id', sa.String(length=128), nullable=False),
    sa.ForeignKeyConstraint(['User_Id'], ['fw_ClientAccounts.Id'], ),
    sa.PrimaryKeyConstraint('Id')
    )
    op.create_table('fw_ClientAccountLogins',
    sa.Column('UserId', sa.String(length=128), nullable=False),
    sa.Column('LoginProvider', sa.String(length=128), nullable=False),
    sa.Column('ProviderKey', sa.String(length=128), nullable=False),
    sa.ForeignKeyConstraint(['UserId'], ['fw_ClientAccounts.Id'], ),
    sa.PrimaryKeyConstraint('UserId', 'LoginProvider', 'ProviderKey')
    )
    op.create_table('fw_ClientAccountRoles',
    sa.Column('UserId', sa.String(length=128), nullable=False),
    sa.Column('RoleId', sa.String(length=128), nullable=False),
    sa.ForeignKeyConstraint(['RoleId'], ['fw_ClientRoles.Id'], ),
    sa.ForeignKeyConstraint(['UserId'], ['fw_ClientAccounts.Id'], ),
    sa.PrimaryKeyConstraint('UserId', 'RoleId')
    )
    op.create_table('fw_ClientProduct',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('ProductId', sa.Uuid(), nullable=False),
    sa.Column('ClientId', sa.Uuid(), nullable=False),
    sa.Column('OpeningStock', sa.Integer(), nullable=False),
    sa.Column('OpeningDate', sa.Date(), nullable=False),
    sa.Column('ClosingStock', sa.Integer(), nullable=False),
    sa.Column('ClosingDate', sa.Date(), nullable=False),
    sa.ForeignKeyConstraint(['ClientId'], ['fw_Client.ID'], ),
    sa.ForeignKeyConstraint(['ProductId'], ['fw_Product.ID'], ),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_table('fw_ClientTransfer',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('IsLocal', sa.Boolean(), nullable=False),
    sa.Column('ClientId', sa.Uuid(), nullable=False),
    sa.Column('Code', sa.String(length=50), nullable=False),
    sa.Column('PackingSlipId', sa.Uuid(), nullable=True),
    sa.Column('Date', sa.DateTime(timezone=True), nullable=False),
    sa.Column('DepartureDate', sa.DateTime(timezone=True), nullable=True),
    sa.Column('IsLoadOut', sa.Boolean(), nullable=False),
    sa.Column('FromLocation', sa.String(length=256), nullable=True),
    sa.Column('FromRMP', sa.String(length=50), nullable=True),
    sa.Column('ToLocation', sa.String(length=256), nullable=True),
    sa.Column('ToRMP', sa.String(length=50), nullable=True),
    sa.Column('TransferFromId', sa.Uuid(), nullable=True),
    sa.Column('TransportCompany', sa.String(length=256), nullable=True),
    sa.Column('VehicleRegistration', sa.String(length=50), nullable=True),
    sa.Column('ConditionOfVehicle', sa.String(length=50), nullable=True),
    sa.Column('NonConformingProduct', sa.Boolean(), nullable=False),
    sa.Column('ContainerNumber', sa.String(length=50), nullable=True),
    sa.Column('SealNumber', sa.String(length=50), nullable=True),
    sa.Column('CanNumber', sa.String(length=50), nullable=True),
    sa.Column('AwbNumber', sa.String(length=50), nullable=True),
    sa.Column('IsDraft', sa.Boolean(), nullable=False),
    sa.Column('Operator', sa.String(length=100), nullable=True),
    sa.Column('Logs', sa.Text(), nullable=True),
    sa.Column('Notes', sa.Text(), nullable=True),
    sa.Column('EdIdentification', sa.String(length=128), nullable=True),
    sa.Column('ExportTo', sa.String(length=128), nullable=True),
    sa.ForeignKeyConstraint(['ClientId'], ['fw_Client.ID'], ),
    sa.ForeignKeyConstraint(['PackingSlipId'], ['fw_PackingSlip.ID'], ),
    sa.ForeignKeyConstraint(['TransferFromId'], ['fw_ClientTransfer.ID'], ),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_index(op.f('ix_fw_ClientTransfer_ClientId'), 'fw_ClientTransfer', ['ClientId'], unique=False)
    op.create_index(op.f('ix_fw_ClientTransfer_PackingSlipId'), 'fw_ClientTransfer', ['PackingSlipId'], unique=False)
    op.create_index(op.f('ix_fw_ClientTransfer_TransferFromId'), 'fw_ClientTransfer', ['TransferFromId'], unique=False)
    op.create_table('fw_ProductBatch',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('ProductId', sa.Uuid(), nullable=False),
    sa.Column('Number', sa.String(length=50), nullable=False),
    sa.Column('InitialStock', sa.Integer(), nullable=False),
    sa.Column('Date', sa.Date(), nullable=False),
    sa.Column('UsedByDate', sa.Date(), nullable=True),
    sa.ForeignKeyConstraint(['ProductId'], ['fw_Product.ID'], ),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_index(op.f('ix_fw_ProductBatch_ProductId'), 'fw_ProductBatch', ['ProductId'], unique=False)
    op.create_table('fw_ClientTransferItem',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('TransferId', sa.Uuid(), nullable=False),
    sa.Column('BatchId', sa.Uuid(), nullable=False),
    sa.Column('Quantity', sa.Integer(), nullable=False),
    sa.Column('NetWeight', sa.Float(), nullable=True),
    sa.Column('RelId', sa.String(length=64), nullable=True),
    sa.Column('SKU', sa.String(length=64), nullable=True),
    sa.Column('Reference', sa.String(length=100), nullable=True),
    sa.Column('RelatedTransferItemId', sa.Uuid(), nullable=True),
    sa.Column('RemainingStock', sa.Integer(), nullable=False),
    sa.Column('Unit', sa.Integer(), nullable=False),
    sa.Column('EdLocation', sa.String(length=256), nullable=True),
    sa.ForeignKeyConstraint(['BatchId'], ['fw_ProductBatch.ID'], ),
    sa.ForeignKeyConstraint(['RelatedTransferItemId'], ['fw_ClientTransferItem.ID'], ),
    sa.ForeignKeyConstraint(['TransferId'], ['fw_ClientTransfer.ID'], ),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_index(op.f('ix_fw_ClientTransferItem_BatchId'), 'fw_ClientTransferItem', ['BatchId'], unique=False)
    op.create_index(op.f('ix_fw_ClientTransferItem_RelatedTransferItemId'), 'fw_ClientTransferItem', ['RelatedTransferItemId'], unique=False)
    op.create_index(op.f('ix_fw_ClientTransferItem_TransferId'), 'fw_ClientTransferItem', ['TransferId'], unique=False)
    op.create_table('fw_ClientTransferRelations',
    sa.Column('ID', sa.Uuid(), nullable=False),
    sa.Column('TransferId', sa.Uuid(), nullable=False),
    sa.Column('RelTransferId', sa.Uuid(), nullable=False),
    sa.ForeignKeyConstraint(['RelTransferId'], ['fw_ClientTransfer.ID'], ),
    sa.ForeignKeyConstraint(['TransferId'], ['fw_ClientTransfer.ID'], ),
    sa.PrimaryKeyConstraint('ID')
    )
    op.create_index(op.f('ix_fw_ClientTransferRelations_RelTransferId'), 'fw_ClientTransferRelations', ['RelTransferId'], unique=False)
    op.create_index(op.f('ix_fw_ClientTransferRelations_TransferId'), 'fw_ClientTransferRelations', ['TransferId'], unique=False)
    # ### end Alembic commands ###

    # 目标库专用 DDL（文档 12.6 第 6 条、10.6）：大小写与首尾空格不同视为重复；
    # 调拨转入单（TransferFromId 非空）与转出单共用单号，不参与唯一校验。
    if op.get_bind().dialect.name == "postgresql":
        op.execute(
            'CREATE UNIQUE INDEX ux_transfer_code ON "fw_ClientTransfer" (lower(trim("Code"))) '
            'WHERE "TransferFromId" IS NULL'
        )
        op.execute('CREATE UNIQUE INDEX ux_packing_slip_reference ON "fw_PackingSlip" (lower(trim("Reference")))')
        op.execute('CREATE UNIQUE INDEX ux_parcel_barcode ON "fw_ShippingParcels" (lower(trim("Barcode")))')
        op.execute('CREATE UNIQUE INDEX ux_batch_number ON "fw_ProductBatch" ("ProductId", lower(trim("Number")))')
        op.execute("CREATE UNIQUE INDEX ux_app_user_username ON app_user (lower(username))")


def downgrade() -> None:
    """Downgrade schema."""
    for name in ("ux_transfer_code", "ux_packing_slip_reference", "ux_parcel_barcode", "ux_batch_number",
                 "ux_app_user_username"):
        op.execute(f"DROP INDEX IF EXISTS {name}")
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_fw_ClientTransferRelations_TransferId'), table_name='fw_ClientTransferRelations')
    op.drop_index(op.f('ix_fw_ClientTransferRelations_RelTransferId'), table_name='fw_ClientTransferRelations')
    op.drop_table('fw_ClientTransferRelations')
    op.drop_index(op.f('ix_fw_ClientTransferItem_TransferId'), table_name='fw_ClientTransferItem')
    op.drop_index(op.f('ix_fw_ClientTransferItem_RelatedTransferItemId'), table_name='fw_ClientTransferItem')
    op.drop_index(op.f('ix_fw_ClientTransferItem_BatchId'), table_name='fw_ClientTransferItem')
    op.drop_table('fw_ClientTransferItem')
    op.drop_index(op.f('ix_fw_ProductBatch_ProductId'), table_name='fw_ProductBatch')
    op.drop_table('fw_ProductBatch')
    op.drop_index(op.f('ix_fw_ClientTransfer_TransferFromId'), table_name='fw_ClientTransfer')
    op.drop_index(op.f('ix_fw_ClientTransfer_PackingSlipId'), table_name='fw_ClientTransfer')
    op.drop_index(op.f('ix_fw_ClientTransfer_ClientId'), table_name='fw_ClientTransfer')
    op.drop_table('fw_ClientTransfer')
    op.drop_table('fw_ClientProduct')
    op.drop_table('fw_ClientAccountRoles')
    op.drop_table('fw_ClientAccountLogins')
    op.drop_table('fw_ClientAccountClaims')
    op.drop_table('app_user_role')
    op.drop_index(op.f('ix_app_session_user_id'), table_name='app_session')
    op.drop_table('app_session')
    op.drop_table('migration_legacy_value')
    op.drop_table('fw_Template')
    op.drop_table('fw_Supplier')
    op.drop_table('fw_ShippingParcels')
    op.drop_table('fw_Product')
    op.drop_table('fw_PackingSlip')
    op.drop_table('fw_ClientRoles')
    op.drop_table('fw_ClientAccounts')
    op.drop_table('fw_Client')
    op.drop_index(op.f('ix_audit_log_entity_id'), table_name='audit_log')
    op.drop_table('audit_log')
    op.drop_table('app_user')
    # ### end Alembic commands ###