"""initial catalog schema

Revision ID: fb59f2e88621
Revises: 
Create Date: 2026-08-21 16:00:37.831671
"""
from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa


revision: str = 'fb59f2e88621'
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('products',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('gtin', sa.String(length=20), nullable=True),
    sa.Column('match_key', sa.String(length=255), nullable=False),
    sa.Column('brand', sa.String(length=160), nullable=True),
    sa.Column('name', sa.String(length=400), nullable=False),
    sa.Column('vertical', sa.String(length=32), nullable=False),
    sa.Column('category', sa.String(length=80), nullable=True),
    sa.Column('size_ml', sa.Integer(), nullable=True),
    sa.Column('abv', sa.Numeric(precision=5, scale=2), nullable=True),
    sa.Column('country_of_origin', sa.String(length=80), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_products_brand'), 'products', ['brand'], unique=False)
    op.create_index(op.f('ix_products_category'), 'products', ['category'], unique=False)
    op.create_index(op.f('ix_products_gtin'), 'products', ['gtin'], unique=True)
    op.create_index(op.f('ix_products_match_key'), 'products', ['match_key'], unique=False)
    op.create_index(op.f('ix_products_vertical'), 'products', ['vertical'], unique=False)
    op.create_table('retailers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('slug', sa.String(length=64), nullable=False),
    sa.Column('name', sa.String(length=160), nullable=False),
    sa.Column('operator', sa.String(length=160), nullable=True),
    sa.Column('homepage', sa.String(length=400), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('slug')
    )
    op.create_table('sources',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('slug', sa.String(length=64), nullable=False),
    sa.Column('name', sa.String(length=160), nullable=False),
    sa.Column('enabled', sa.Boolean(), nullable=False),
    sa.Column('delay_seconds', sa.Numeric(precision=5, scale=2), nullable=False),
    sa.Column('max_concurrency', sa.Integer(), nullable=False),
    sa.Column('notes', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('slug')
    )
    op.create_table('awards',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('product_id', sa.Integer(), nullable=False),
    sa.Column('competition', sa.String(length=160), nullable=False),
    sa.Column('competition_slug', sa.String(length=64), nullable=True),
    sa.Column('year', sa.Integer(), nullable=True),
    sa.Column('medal', sa.String(length=64), nullable=True),
    sa.Column('score', sa.Integer(), nullable=True),
    sa.Column('is_own_competition', sa.Boolean(), nullable=False),
    sa.Column('source', sa.String(length=64), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('product_id', 'competition', 'year', name='uq_award_product_comp_year')
    )
    op.create_index(op.f('ix_awards_competition_slug'), 'awards', ['competition_slug'], unique=False)
    op.create_index(op.f('ix_awards_product_id'), 'awards', ['product_id'], unique=False)
    op.create_table('collection_runs',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('source_id', sa.Integer(), nullable=False),
    sa.Column('started_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('status', sa.String(length=24), nullable=False),
    sa.Column('products_seen', sa.Integer(), nullable=False),
    sa.Column('prices_written', sa.Integer(), nullable=False),
    sa.Column('error', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['source_id'], ['sources.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('locations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('retailer_id', sa.Integer(), nullable=False),
    sa.Column('code', sa.String(length=32), nullable=False),
    sa.Column('iata', sa.String(length=4), nullable=True),
    sa.Column('name', sa.String(length=160), nullable=False),
    sa.Column('city', sa.String(length=120), nullable=True),
    sa.Column('country', sa.String(length=80), nullable=True),
    sa.Column('currency', sa.String(length=3), nullable=False),
    sa.Column('is_catalogue_only', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.ForeignKeyConstraint(['retailer_id'], ['retailers.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('retailer_id', 'code', name='uq_location_retailer_code')
    )
    op.create_index(op.f('ix_locations_iata'), 'locations', ['iata'], unique=False)
    op.create_table('listings',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('product_id', sa.Integer(), nullable=False),
    sa.Column('location_id', sa.Integer(), nullable=False),
    sa.Column('source_sku', sa.String(length=96), nullable=False),
    sa.Column('url', sa.String(length=600), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ),
    sa.ForeignKeyConstraint(['product_id'], ['products.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('location_id', 'source_sku', name='uq_listing_location_sku')
    )
    op.create_index(op.f('ix_listings_location_id'), 'listings', ['location_id'], unique=False)
    op.create_index(op.f('ix_listings_product_id'), 'listings', ['product_id'], unique=False)
    op.create_table('price_observations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('listing_id', sa.Integer(), nullable=False),
    sa.Column('price', sa.Numeric(precision=12, scale=2), nullable=False),
    sa.Column('currency', sa.String(length=3), nullable=False),
    sa.Column('price_usd', sa.Numeric(precision=12, scale=2), nullable=True),
    sa.Column('was_price', sa.Numeric(precision=12, scale=2), nullable=True),
    sa.Column('price_type', sa.String(length=24), nullable=False),
    sa.Column('in_stock', sa.Boolean(), nullable=True),
    sa.Column('observed_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('run_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['listing_id'], ['listings.id'], ),
    sa.ForeignKeyConstraint(['run_id'], ['collection_runs.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_obs_listing_time', 'price_observations', ['listing_id', 'observed_at'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_obs_listing_time', table_name='price_observations')
    op.drop_table('price_observations')
    op.drop_index(op.f('ix_listings_product_id'), table_name='listings')
    op.drop_index(op.f('ix_listings_location_id'), table_name='listings')
    op.drop_table('listings')
    op.drop_index(op.f('ix_locations_iata'), table_name='locations')
    op.drop_table('locations')
    op.drop_table('collection_runs')
    op.drop_index(op.f('ix_awards_product_id'), table_name='awards')
    op.drop_index(op.f('ix_awards_competition_slug'), table_name='awards')
    op.drop_table('awards')
    op.drop_table('sources')
    op.drop_table('retailers')
    op.drop_index(op.f('ix_products_vertical'), table_name='products')
    op.drop_index(op.f('ix_products_match_key'), table_name='products')
    op.drop_index(op.f('ix_products_gtin'), table_name='products')
    op.drop_index(op.f('ix_products_category'), table_name='products')
    op.drop_index(op.f('ix_products_brand'), table_name='products')
    op.drop_table('products')
    # ### end Alembic commands ###