"""Alter Base Tables

Revision ID: 27578d637b15
Revises: 5100906b53c3
Create Date: 2025-08-12 15:24:39.209951

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '27578d637b15'
down_revision: Union[str, None] = '5100906b53c3'
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.add_column('appellations', sa.Column('sub_region_id', sa.Integer(), nullable=False))
    op.drop_index('ix_appellations_location_id', table_name='appellations')
    op.drop_index('ix_appellations_scope', table_name='appellations')
    op.create_index('ix_appellations_scope', 'appellations', ['country_id', 'region_id', 'sub_region_id'], unique=False)
    op.drop_constraint('uq_appellation_scope_name_deleted', 'appellations', type_='unique')
    op.create_unique_constraint('uq_appellation_scope_name_deleted', 'appellations', ['country_id', 'region_id', 'sub_region_id', 'name', 'deleted_at'])
    op.create_index(op.f('ix_appellations_sub_region_id'), 'appellations', ['sub_region_id'], unique=False)
    op.drop_constraint('appellations_location_id_fkey', 'appellations', type_='foreignkey')
    op.create_foreign_key(None, 'appellations', 'sub_regions', ['sub_region_id'], ['id'])
    op.drop_column('appellations', 'location_id')
    op.alter_column('regions', 'region_code',
               existing_type=sa.VARCHAR(length=255),
               nullable=False)
    op.add_column('sub_appellations', sa.Column('sub_region_id', sa.Integer(), nullable=False))
    op.drop_index('ix_sub_appellations_location_id', table_name='sub_appellations')
    op.drop_index('ix_sub_appellations_lineage', table_name='sub_appellations')
    op.create_index('ix_sub_appellations_lineage', 'sub_appellations', ['country_id', 'region_id', 'sub_region_id', 'appellation_id'], unique=False)
    op.create_index(op.f('ix_sub_appellations_sub_region_id'), 'sub_appellations', ['sub_region_id'], unique=False)
    op.drop_constraint('sub_appellations_location_id_fkey', 'sub_appellations', type_='foreignkey')
    op.create_foreign_key(None, 'sub_appellations', 'sub_regions', ['sub_region_id'], ['id'])
    op.drop_column('sub_appellations', 'location_id')
    op.drop_index('ix_wine_appellations_location_id', table_name='wine_appellations')
    op.drop_constraint('uq_wa_title_location', 'wine_appellations', type_='unique')
    op.drop_index('ix_wine_appellations_level_picker', table_name='wine_appellations')
    op.create_index('ix_wine_appellations_level_picker', 'wine_appellations', ['country_id', 'region_id', 'sub_region_id', 'appellation_id', 'sub_appellation_id'], unique=False)
    op.drop_constraint('wine_appellations_location_id_fkey', 'wine_appellations', type_='foreignkey')
    op.drop_column('wine_appellations', 'location_id')
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('wine_appellations', sa.Column('location_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.create_foreign_key('wine_appellations_location_id_fkey', 'wine_appellations', 'locations', ['location_id'], ['id'], ondelete='RESTRICT')
    op.drop_index('ix_wine_appellations_level_picker', table_name='wine_appellations')
    op.create_index('ix_wine_appellations_level_picker', 'wine_appellations', ['country_id', 'region_id', 'sub_region_id', 'location_id', 'appellation_id', 'sub_appellation_id'], unique=False)
    op.create_unique_constraint('uq_wa_title_location', 'wine_appellations', ['title', 'location_id'])
    op.create_index('ix_wine_appellations_location_id', 'wine_appellations', ['location_id'], unique=False)
    op.add_column('sub_appellations', sa.Column('location_id', sa.INTEGER(), autoincrement=False, nullable=False))
    op.drop_constraint(None, 'sub_appellations', type_='foreignkey')
    op.create_foreign_key('sub_appellations_location_id_fkey', 'sub_appellations', 'locations', ['location_id'], ['id'])
    op.drop_index(op.f('ix_sub_appellations_sub_region_id'), table_name='sub_appellations')
    op.drop_index('ix_sub_appellations_lineage', table_name='sub_appellations')
    op.create_index('ix_sub_appellations_lineage', 'sub_appellations', ['country_id', 'region_id', 'location_id', 'appellation_id'], unique=False)
    op.create_index('ix_sub_appellations_location_id', 'sub_appellations', ['location_id'], unique=False)
    op.drop_column('sub_appellations', 'sub_region_id')
    op.alter_column('regions', 'region_code',
               existing_type=sa.VARCHAR(length=255),
               nullable=True)
    op.add_column('appellations', sa.Column('location_id', sa.INTEGER(), autoincrement=False, nullable=False))
    op.drop_constraint(None, 'appellations', type_='foreignkey')
    op.create_foreign_key('appellations_location_id_fkey', 'appellations', 'locations', ['location_id'], ['id'])
    op.drop_index(op.f('ix_appellations_sub_region_id'), table_name='appellations')
    op.drop_constraint('uq_appellation_scope_name_deleted', 'appellations', type_='unique')
    op.create_unique_constraint('uq_appellation_scope_name_deleted', 'appellations', ['country_id', 'region_id', 'location_id', 'name', 'deleted_at'])
    op.drop_index('ix_appellations_scope', table_name='appellations')
    op.create_index('ix_appellations_scope', 'appellations', ['country_id', 'region_id', 'location_id'], unique=False)
    op.create_index('ix_appellations_location_id', 'appellations', ['location_id'], unique=False)
    op.drop_column('appellations', 'sub_region_id')
    # ### end Alembic commands ###