"""Alter Base Tables

Revision ID: 5100906b53c3
Revises: 6eb0826e7959
Create Date: 2025-08-12 14:39:07.994532

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = '5100906b53c3'
down_revision: Union[str, None] = '6eb0826e7959'
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('sub_appellations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('appellation_id', sa.Integer(), nullable=False),
    sa.Column('country_id', sa.Integer(), nullable=False),
    sa.Column('region_id', sa.Integer(), nullable=False),
    sa.Column('location_id', sa.Integer(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=False),
    sa.Column('last_updated', sa.DateTime(), nullable=False),
    sa.Column('deleted_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['appellation_id'], ['appellations.id'], ),
    sa.ForeignKeyConstraint(['country_id'], ['countries.id'], ),
    sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ),
    sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('appellation_id', 'name', 'deleted_at', name='uq_sub_appellation_appellation_name_deleted')
    )
    op.create_index(op.f('ix_sub_appellations_appellation_id'), 'sub_appellations', ['appellation_id'], unique=False)
    op.create_index(op.f('ix_sub_appellations_country_id'), 'sub_appellations', ['country_id'], unique=False)
    op.create_index(op.f('ix_sub_appellations_id'), 'sub_appellations', ['id'], unique=False)
    op.create_index('ix_sub_appellations_lineage', 'sub_appellations', ['country_id', 'region_id', 'location_id', 'appellation_id'], unique=False)
    op.create_index(op.f('ix_sub_appellations_location_id'), 'sub_appellations', ['location_id'], unique=False)
    op.create_index('ix_sub_appellations_name', 'sub_appellations', ['name'], unique=False)
    op.create_index(op.f('ix_sub_appellations_region_id'), 'sub_appellations', ['region_id'], unique=False)

    op.add_column('appellations', sa.Column('name', sa.String(length=255), nullable=False))
    op.alter_column('appellations', 'country_id',
               existing_type=sa.INTEGER(),
               nullable=False)
    op.alter_column('appellations', 'region_id',
               existing_type=sa.INTEGER(),
               nullable=False)
    op.alter_column('appellations', 'location_id',
               existing_type=sa.INTEGER(),
               nullable=False)
    op.alter_column('appellations', 'date_created',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('appellations', 'last_updated',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.drop_constraint('uq_appellation_mongo_id', 'appellations', type_='unique')
    op.create_index(op.f('ix_appellations_country_id'), 'appellations', ['country_id'], unique=False)
    op.create_index(op.f('ix_appellations_location_id'), 'appellations', ['location_id'], unique=False)
    op.create_index('ix_appellations_name', 'appellations', ['name'], unique=False)
    op.create_index(op.f('ix_appellations_region_id'), 'appellations', ['region_id'], unique=False)
    op.create_index('ix_appellations_scope', 'appellations', ['country_id', 'region_id', 'location_id'], unique=False)
    op.create_unique_constraint('uq_appellation_scope_name_deleted', 'appellations', ['country_id', 'region_id', 'location_id', 'name', 'deleted_at'])
    op.drop_column('appellations', 'mongo_id')
    op.drop_column('appellations', 'literal')

    op.alter_column('countries', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('countries', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.drop_constraint('regions_country_code_fkey', 'regions', type_='foreignkey')
    op.drop_constraint('countries_country_code_key', 'countries', type_='unique')
    op.drop_index('ix_countries_uuid', table_name='countries')
    op.create_index('ix_countries_name', 'countries', ['name'], unique=False)
    op.create_unique_constraint('uq_countries_name_deleted', 'countries', ['name', 'deleted_at'])
    op.drop_column('countries', 'country_code')
    op.drop_column('countries', 'is_deleted')
    op.drop_column('countries', 'uuid')

    op.drop_column('keyword_spreads', 'version')

    op.drop_index('ix_locales_uuid', table_name='locales')
    op.drop_constraint('locales_location_uuid_fkey', 'locales', type_='foreignkey')
    op.drop_column('locales', 'uuid')
    op.drop_column('locales', 'location_uuid')
    op.drop_column('locales', 'is_deleted')

    op.drop_index('ix_locations_uuid', table_name='locations')
    op.drop_column('locations', 'uuid')
    op.drop_column('locations', 'is_deleted')

    op.add_column('regions', sa.Column('country_id', sa.Integer(), nullable=False))
    op.alter_column('regions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('regions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.drop_index('ix_regions_uuid', table_name='regions')
    op.create_index(op.f('ix_regions_country_id'), 'regions', ['country_id'], unique=False)
    op.create_index('ix_regions_name', 'regions', ['name'], unique=False)
    op.create_index('ix_regions_region_code', 'regions', ['region_code'], unique=False)
    op.create_unique_constraint('uq_region_country_code_deleted', 'regions', ['country_id', 'region_code', 'deleted_at'])
    op.create_unique_constraint('uq_region_country_name_deleted', 'regions', ['country_id', 'name', 'deleted_at'])
    op.create_foreign_key(None, 'regions', 'countries', ['country_id'], ['id'], ondelete='RESTRICT')
    op.drop_column('regions', 'country_code')
    op.drop_column('regions', 'is_deleted')
    op.drop_column('regions', 'uuid')

    op.alter_column('sub_regions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('sub_regions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.drop_index('ix_sub_regions_uuid', table_name='sub_regions')
    op.drop_constraint('sub_regions_sub_region_code_key', 'sub_regions', type_='unique')
    op.create_index('ix_sub_regions_name', 'sub_regions', ['name'], unique=False)
    op.create_index(op.f('ix_sub_regions_region_id'), 'sub_regions', ['region_id'], unique=False)
    op.create_unique_constraint('uq_sub_region_region_name_deleted', 'sub_regions', ['region_id', 'name', 'deleted_at'])
    op.drop_column('sub_regions', 'uuid')
    op.drop_column('sub_regions', 'is_deleted')
    op.drop_column('sub_regions', 'sub_region_code')

    op.add_column('wine_appellations', sa.Column('location_id', sa.Integer(), nullable=True))
    op.add_column('wine_appellations', sa.Column('appellation_id', sa.Integer(), nullable=True))
    op.add_column('wine_appellations', sa.Column('sub_appellation_id', sa.Integer(), nullable=True))
    op.alter_column('wine_appellations', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('wine_appellations', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.create_index(op.f('ix_wine_appellations_appellation_id'), 'wine_appellations', ['appellation_id'], unique=False)
    op.create_index(op.f('ix_wine_appellations_country_id'), 'wine_appellations', ['country_id'], unique=False)
    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_index(op.f('ix_wine_appellations_location_id'), 'wine_appellations', ['location_id'], unique=False)
    op.create_index(op.f('ix_wine_appellations_region_id'), 'wine_appellations', ['region_id'], unique=False)
    op.create_index(op.f('ix_wine_appellations_sub_appellation_id'), 'wine_appellations', ['sub_appellation_id'], unique=False)
    op.create_index(op.f('ix_wine_appellations_sub_region_id'), 'wine_appellations', ['sub_region_id'], unique=False)
    op.create_index('ix_wine_appellations_title', 'wine_appellations', ['title'], unique=False)
    op.create_unique_constraint('uq_wa_title_appellation', 'wine_appellations', ['title', 'appellation_id'])
    op.create_unique_constraint('uq_wa_title_country', 'wine_appellations', ['title', 'country_id'])
    op.create_unique_constraint('uq_wa_title_location', 'wine_appellations', ['title', 'location_id'])
    op.create_unique_constraint('uq_wa_title_region', 'wine_appellations', ['title', 'region_id'])
    op.create_unique_constraint('uq_wa_title_sub_appellation', 'wine_appellations', ['title', 'sub_appellation_id'])
    op.create_unique_constraint('uq_wa_title_sub_region', 'wine_appellations', ['title', 'sub_region_id'])
    op.drop_constraint('wine_appellations_country_id_fkey', 'wine_appellations', type_='foreignkey')
    op.drop_constraint('wine_appellations_region_id_fkey', 'wine_appellations', type_='foreignkey')
    op.drop_constraint('wine_appellations_sub_region_id_fkey', 'wine_appellations', type_='foreignkey')
    op.create_foreign_key(None, 'wine_appellations', 'locations', ['location_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'wine_appellations', 'sub_appellations', ['sub_appellation_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'wine_appellations', 'regions', ['region_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'wine_appellations', 'sub_regions', ['sub_region_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'wine_appellations', 'countries', ['country_id'], ['id'], ondelete='RESTRICT')
    op.create_foreign_key(None, 'wine_appellations', 'appellations', ['appellation_id'], ['id'], ondelete='RESTRICT')
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.drop_constraint(None, 'wine_appellations', type_='foreignkey')
    op.create_foreign_key('wine_appellations_sub_region_id_fkey', 'wine_appellations', 'sub_regions', ['sub_region_id'], ['id'])
    op.create_foreign_key('wine_appellations_region_id_fkey', 'wine_appellations', 'regions', ['region_id'], ['id'])
    op.create_foreign_key('wine_appellations_country_id_fkey', 'wine_appellations', 'countries', ['country_id'], ['id'])
    op.drop_constraint('uq_wa_title_sub_region', 'wine_appellations', type_='unique')
    op.drop_constraint('uq_wa_title_sub_appellation', 'wine_appellations', type_='unique')
    op.drop_constraint('uq_wa_title_region', 'wine_appellations', type_='unique')
    op.drop_constraint('uq_wa_title_location', 'wine_appellations', type_='unique')
    op.drop_constraint('uq_wa_title_country', 'wine_appellations', type_='unique')
    op.drop_constraint('uq_wa_title_appellation', 'wine_appellations', type_='unique')
    op.drop_index('ix_wine_appellations_title', table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_sub_region_id'), table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_sub_appellation_id'), table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_region_id'), table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_location_id'), table_name='wine_appellations')
    op.drop_index('ix_wine_appellations_level_picker', table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_country_id'), table_name='wine_appellations')
    op.drop_index(op.f('ix_wine_appellations_appellation_id'), table_name='wine_appellations')
    op.alter_column('wine_appellations', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('wine_appellations', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.drop_column('wine_appellations', 'sub_appellation_id')
    op.drop_column('wine_appellations', 'appellation_id')
    op.drop_column('wine_appellations', 'location_id')

    op.add_column('sub_regions', sa.Column('sub_region_code', sa.VARCHAR(length=100), autoincrement=False, nullable=False))
    op.add_column('sub_regions', sa.Column('is_deleted', sa.BOOLEAN(), autoincrement=False, nullable=False))
    op.add_column('sub_regions', sa.Column('uuid', sa.UUID(), autoincrement=False, nullable=False))
    op.drop_constraint('uq_sub_region_region_name_deleted', 'sub_regions', type_='unique')
    op.drop_index(op.f('ix_sub_regions_region_id'), table_name='sub_regions')
    op.drop_index('ix_sub_regions_name', table_name='sub_regions')
    op.create_unique_constraint('sub_regions_sub_region_code_key', 'sub_regions', ['sub_region_code'])
    op.create_index('ix_sub_regions_uuid', 'sub_regions', ['uuid'], unique=True)
    op.alter_column('sub_regions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('sub_regions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)

    op.add_column('regions', sa.Column('uuid', sa.UUID(), autoincrement=False, nullable=False))
    op.add_column('regions', sa.Column('is_deleted', sa.BOOLEAN(), autoincrement=False, nullable=False))
    op.add_column('regions', sa.Column('country_code', sa.VARCHAR(length=10), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'regions', type_='foreignkey')
    op.create_foreign_key('regions_country_code_fkey', 'regions', 'countries', ['country_code'], ['country_code'])
    op.drop_constraint('uq_region_country_name_deleted', 'regions', type_='unique')
    op.drop_constraint('uq_region_country_code_deleted', 'regions', type_='unique')
    op.drop_index('ix_regions_region_code', table_name='regions')
    op.drop_index('ix_regions_name', table_name='regions')
    op.drop_index(op.f('ix_regions_country_id'), table_name='regions')
    op.create_index('ix_regions_uuid', 'regions', ['uuid'], unique=True)
    op.alter_column('regions', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('regions', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.drop_column('regions', 'country_id')

    op.add_column('locations', sa.Column('is_deleted', sa.BOOLEAN(), autoincrement=False, nullable=False))
    op.add_column('locations', sa.Column('uuid', sa.UUID(), autoincrement=False, nullable=False))
    op.create_index('ix_locations_uuid', 'locations', ['uuid'], unique=True)

    op.add_column('locales', sa.Column('is_deleted', sa.BOOLEAN(), autoincrement=False, nullable=False))
    op.add_column('locales', sa.Column('location_uuid', sa.UUID(), autoincrement=False, nullable=False))
    op.add_column('locales', sa.Column('uuid', sa.UUID(), autoincrement=False, nullable=False))
    op.create_foreign_key('locales_location_uuid_fkey', 'locales', 'locations', ['location_uuid'], ['uuid'], ondelete='CASCADE')
    op.create_index('ix_locales_uuid', 'locales', ['uuid'], unique=True)

    op.add_column('keyword_spreads', sa.Column('version', sa.VARCHAR(), autoincrement=False, nullable=True))

    op.add_column('countries', sa.Column('uuid', sa.UUID(), autoincrement=False, nullable=True))
    op.add_column('countries', sa.Column('is_deleted', sa.BOOLEAN(), autoincrement=False, nullable=True))
    op.add_column('countries', sa.Column('country_code', sa.VARCHAR(length=10), autoincrement=False, nullable=True))
    op.drop_constraint('uq_countries_name_deleted', 'countries', type_='unique')
    op.drop_index('ix_countries_name', table_name='countries')
    op.create_index('ix_countries_uuid', 'countries', ['uuid'], unique=True)
    op.create_unique_constraint('countries_country_code_key', 'countries', ['country_code'])
    op.alter_column('countries', 'updated_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('countries', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)

    op.add_column('appellations', sa.Column('literal', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.add_column('appellations', sa.Column('mongo_id', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.drop_constraint('uq_appellation_scope_name_deleted', 'appellations', type_='unique')
    op.drop_index('ix_appellations_scope', table_name='appellations')
    op.drop_index(op.f('ix_appellations_region_id'), table_name='appellations')
    op.drop_index('ix_appellations_name', table_name='appellations')
    op.drop_index(op.f('ix_appellations_location_id'), table_name='appellations')
    op.drop_index(op.f('ix_appellations_country_id'), table_name='appellations')
    op.create_unique_constraint('uq_appellation_mongo_id', 'appellations', ['mongo_id'])
    op.alter_column('appellations', 'last_updated',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('appellations', 'date_created',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.alter_column('appellations', 'location_id',
               existing_type=sa.INTEGER(),
               nullable=True)
    op.alter_column('appellations', 'region_id',
               existing_type=sa.INTEGER(),
               nullable=True)
    op.alter_column('appellations', 'country_id',
               existing_type=sa.INTEGER(),
               nullable=True)
    op.drop_column('appellations', 'name')

    op.drop_index(op.f('ix_sub_appellations_region_id'), table_name='sub_appellations')
    op.drop_index('ix_sub_appellations_name', table_name='sub_appellations')
    op.drop_index(op.f('ix_sub_appellations_location_id'), table_name='sub_appellations')
    op.drop_index('ix_sub_appellations_lineage', table_name='sub_appellations')
    op.drop_index(op.f('ix_sub_appellations_id'), table_name='sub_appellations')
    op.drop_index(op.f('ix_sub_appellations_country_id'), table_name='sub_appellations')
    op.drop_index(op.f('ix_sub_appellations_appellation_id'), table_name='sub_appellations')
    op.drop_table('sub_appellations')
    # ### end Alembic commands ###