"""add web crawler match  tables

Revision ID: 43569c02276e
Revises: 07583bbd7bb9
Create Date: 2025-07-17 11:50:48.710400

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '43569c02276e'
down_revision: Union[str, None] = '07583bbd7bb9'
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('bottle_sizes',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('alias', sa.Text(), nullable=True),
    sa.Column('exclusion', sa.Text(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    op.create_index(op.f('ix_bottle_sizes_id'), 'bottle_sizes', ['id'], unique=False)
    op.create_table('producers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('name_show', sa.Text(), nullable=True),
    sa.Column('mongo_id', sa.String(), nullable=True),
    sa.Column('source', sa.Enum('NARROW', 'VALIDATION', 'WINE_ADDITION', 'KW_GENERATOR', name='source'), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('mongo_id')
    )
    op.create_index(op.f('ix_producers_id'), 'producers', ['id'], unique=False)
    op.create_table('producer_keywords',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('producer_id', sa.Integer(), nullable=False),
    sa.Column('keyword', sa.Text(), nullable=True),
    sa.Column('source', sa.Enum('NARROW', 'VALIDATION', 'WINE_ADDITION', 'KW_GENERATOR', name='source'), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['producer_id'], ['producers.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('producer_id'),
    sa.UniqueConstraint('producer_id', name='uq_producer_keyword_producer')
    )
    op.create_index(op.f('ix_producer_keywords_id'), 'producer_keywords', ['id'], unique=False)
    op.create_table('producer_noises',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('noise', sa.Text(), nullable=False),
    sa.Column('producer_id', sa.Integer(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['producer_id'], ['producers.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('producer_id', 'noise', name='uq_producer_noise_producer_noise')
    )
    op.create_index(op.f('ix_producer_noises_id'), 'producer_noises', ['id'], unique=False)
    op.create_table('retailers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('code', sa.String(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('web_url', sa.Text(), nullable=True),
    sa.Column('address', sa.Text(), nullable=True),
    sa.Column('address_extended', sa.Text(), nullable=True),
    sa.Column('postal_code', sa.String(), nullable=True),
    sa.Column('city', sa.String(), nullable=True),
    sa.Column('state', sa.String(), nullable=True),
    sa.Column('phone', sa.String(), nullable=True),
    sa.Column('ship_to', sa.String(), nullable=True),
    sa.Column('country_id', sa.Integer(), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['country_id'], ['countries.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code')
    )
    op.create_index(op.f('ix_retailers_id'), 'retailers', ['id'], unique=False)
    op.create_table('wine_db',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('color', sa.String(), nullable=True),
    sa.Column('type', sa.String(), nullable=True),
    sa.Column('sweetness', sa.String(), nullable=True),
    sa.Column('variety', sa.String(), nullable=True),
    sa.Column('note', sa.Text(), nullable=True),
    sa.Column('producer_id', sa.Integer(), nullable=True),
    sa.Column('wine_country', sa.String(), nullable=True),
    sa.Column('wine_region', sa.String(), nullable=True),
    sa.Column('wine_location', sa.String(), nullable=True),
    sa.Column('wine_locale', sa.String(), nullable=True),
    sa.Column('wine_site', sa.String(), nullable=True),
    sa.Column('wine_n_id', sa.String(length=100), nullable=True),
    sa.Column('source', sa.Enum('IMPORT', 'MANUAL', name='sourceenum'), nullable=True),
    sa.Column('status', sa.Enum('ACTIVE', 'DELETED', name='winedbstatus'), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['producer_id'], ['producers.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_wine_db_id'), 'wine_db', ['id'], unique=False)
    op.create_table('wine_keywords',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('wine_db_id', sa.Integer(), nullable=False),
    sa.Column('pattern', sa.Text(), nullable=False),
    sa.Column('producer_keyword', sa.Text(), nullable=True),
    sa.Column('base_keyword', sa.String(), nullable=True),
    sa.Column('appellation_keyword', sa.String(), nullable=True),
    sa.Column('color_keyword', sa.String(), nullable=True),
    sa.Column('common_modifiers_keyword', sa.String(), nullable=True),
    sa.Column('modifiers_keyword', sa.Text(), nullable=True),
    sa.Column('custom_not_keyword', sa.String(), nullable=True),
    sa.Column('global_not_keyword', sa.Text(), nullable=True),
    sa.Column('source', sa.String(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['wine_db_id'], ['wine_db.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('wine_db_id')
    )
    op.create_index(op.f('ix_wine_keywords_id'), 'wine_keywords', ['id'], unique=False)
    op.create_table('wine_noises',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('noise', sa.Text(), nullable=False),
    sa.Column('wine_db_id', sa.Integer(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['wine_db_id'], ['wine_db.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('wine_db_id', 'noise', name='uq_wine_noise_winedb_noise')
    )
    op.create_index(op.f('ix_wine_noises_id'), 'wine_noises', ['id'], unique=False)
    op.create_table('appellations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('country_id', sa.Integer(), nullable=True),
    sa.Column('region_id', sa.Integer(), nullable=True),
    sa.Column('location_id', sa.Integer(), nullable=True),
    sa.Column('mongo_id', sa.String(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['country_id'], ['countries.id'], ),
    sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ),
    sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('mongo_id'),
    sa.UniqueConstraint('mongo_id', name='uq_appellation_mongo_id')
    )
    op.create_index(op.f('ix_appellations_id'), 'appellations', ['id'], unique=False)
    op.create_table('wines',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('vintage', sa.String(), nullable=True),
    sa.Column('maturity', sa.String(), nullable=True),
    sa.Column('rating_low', sa.String(length=10), nullable=True),
    sa.Column('rating_high', sa.String(length=10), nullable=True),
    sa.Column('rating_q', sa.String(length=10), nullable=True),
    sa.Column('image_url', sa.Text(), nullable=True),
    sa.Column('wine_db_id', sa.Integer(), nullable=False),
    sa.Column('appellation_id', sa.Integer(), nullable=True),
    sa.Column('mongo_id', sa.String(), nullable=True),
    sa.Column('wine_n_id', sa.String(), nullable=True),
    sa.Column('wine_n_rating', sa.String(length=10), nullable=True),
    sa.Column('source', sa.Enum('NARROW', 'VALIDATION', 'WINE_ADDITION', 'KW_GENERATOR', name='source'), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['appellation_id'], ['appellations.id'], ),
    sa.ForeignKeyConstraint(['wine_db_id'], ['wine_db.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('mongo_id')
    )
    op.create_index(op.f('ix_wines_id'), 'wines', ['id'], unique=False)
    op.create_table('retailer_wines',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('quantity', sa.Integer(), nullable=True),
    sa.Column('tax_status', sa.String(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('sku', sa.String(), nullable=True),
    sa.Column('url', sa.Text(), nullable=True),
    sa.Column('currency', sa.String(), nullable=True),
    sa.Column('price', sa.DECIMAL(), nullable=True),
    sa.Column('usd_price', sa.DECIMAL(), nullable=True),
    sa.Column('bottle_size_id', sa.Integer(), nullable=True),
    sa.Column('retailer_id', sa.Integer(), nullable=True),
    sa.Column('wine_id', sa.Integer(), nullable=True),
    sa.Column('available', sa.Boolean(), nullable=True),
    sa.Column('last_available', sa.DateTime(), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['bottle_size_id'], ['bottle_sizes.id'], ),
    sa.ForeignKeyConstraint(['retailer_id'], ['retailers.id'], ),
    sa.ForeignKeyConstraint(['wine_id'], ['wines.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('retailer_id', 'wine_id', 'bottle_size_id', 'sku', name='uix_retailer_wine_size_sku')
    )
    op.create_index(op.f('ix_retailer_wines_id'), 'retailer_wines', ['id'], unique=False)
    op.create_index(op.f('ix_retailer_wines_name'), 'retailer_wines', ['name'], unique=False)
    op.create_table('wine_duplications',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('wine_id', sa.Integer(), nullable=False),
    sa.Column('mongo_id', sa.String(), nullable=False),
    sa.Column('source', sa.Enum('NARROW', 'VALIDATION', 'WINE_ADDITION', 'KW_GENERATOR', name='source'), nullable=True),
    sa.Column('date_created', sa.DateTime(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['wine_id'], ['wines.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('mongo_id'),
    sa.UniqueConstraint('mongo_id', name='uq_mongo_id')
    )
    op.create_index(op.f('ix_wine_duplications_id'), 'wine_duplications', ['id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_wine_duplications_id'), table_name='wine_duplications')
    op.drop_table('wine_duplications')
    op.drop_index(op.f('ix_retailer_wines_name'), table_name='retailer_wines')
    op.drop_index(op.f('ix_retailer_wines_id'), table_name='retailer_wines')
    op.drop_table('retailer_wines')
    op.drop_index(op.f('ix_wines_id'), table_name='wines')
    op.drop_table('wines')
    op.drop_index(op.f('ix_appellations_id'), table_name='appellations')
    op.drop_table('appellations')
    op.drop_index(op.f('ix_wine_noises_id'), table_name='wine_noises')
    op.drop_table('wine_noises')
    op.drop_index(op.f('ix_wine_keywords_id'), table_name='wine_keywords')
    op.drop_table('wine_keywords')
    op.drop_index(op.f('ix_wine_db_id'), table_name='wine_db')
    op.drop_table('wine_db')
    op.drop_index(op.f('ix_retailers_id'), table_name='retailers')
    op.drop_table('retailers')
    op.drop_index(op.f('ix_producer_noises_id'), table_name='producer_noises')
    op.drop_table('producer_noises')
    op.drop_index(op.f('ix_producer_keywords_id'), table_name='producer_keywords')
    op.drop_table('producer_keywords')
    op.drop_index(op.f('ix_producers_id'), table_name='producers')
    op.drop_table('producers')
    op.drop_index(op.f('ix_bottle_sizes_id'), table_name='bottle_sizes')
    op.drop_table('bottle_sizes')
    # ### end Alembic commands ###