"""Add verification tokens and legal tables

Revision ID: 13bd03551ebf
Revises: 8d450e9dc77f
Create Date: 2026-01-26 09:34:26.147993

"""
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 = '13bd03551ebf'
down_revision: Union[str, Sequence[str], None] = '8d450e9dc77f'
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('email_logs',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('type', sa.String(), nullable=True),
    sa.Column('sent_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_email_logs_email'), 'email_logs', ['email'], unique=False, schema='data')
    op.create_index(op.f('ix_data_email_logs_id'), 'email_logs', ['id'], unique=False, schema='data')
    op.create_table('email_provider_configs',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.Column('provider_type', sa.String(length=20), nullable=True),
    sa.Column('priority', sa.Integer(), nullable=True),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('fail_count', sa.Integer(), nullable=True),
    sa.Column('max_fail_threshold', sa.Integer(), nullable=True),
    sa.Column('success_rate', sa.Float(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name'),
    schema='data'
    )
    op.create_index(op.f('ix_data_email_provider_configs_id'), 'email_provider_configs', ['id'], unique=False, schema='data')
    op.create_table('email_templates',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('type', sa.Enum('REGISTRATION', 'PASSWORD_RESET', 'GDPR_NOTICE', name='emailtype'), nullable=True),
    sa.Column('subject', sa.String(length=255), nullable=False),
    sa.Column('body_html', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_email_templates_id'), 'email_templates', ['id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_email_templates_type'), 'email_templates', ['type'], unique=True, schema='data')
    op.create_table('legal_documents',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(length=255), nullable=True),
    sa.Column('content', sa.Text(), nullable=False),
    sa.Column('version', sa.String(length=20), nullable=False),
    sa.Column('region_code', sa.String(length=5), nullable=True),
    sa.Column('language', sa.String(length=5), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_legal_documents_id'), 'legal_documents', ['id'], unique=False, schema='data')
    op.create_table('legal_acceptances',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('document_id', sa.Integer(), nullable=True),
    sa.Column('accepted_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.Column('ip_address', sa.String(length=45), nullable=True),
    sa.Column('user_agent', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['document_id'], ['data.legal_documents.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], ),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_legal_acceptances_id'), 'legal_acceptances', ['id'], unique=False, schema='data')
    op.create_table('organizations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('org_type', sa.Enum('PRIVATE', 'COMPANY', name='orgtype'), nullable=True),
    sa.Column('tax_number', sa.String(), nullable=True),
    sa.Column('founded_at', sa.Date(), nullable=True),
    sa.Column('validation_status', sa.Enum('NOT_VALIDATED', 'PENDING', 'VALIDATED', 'REJECTED', name='validationstatus'), nullable=True),
    sa.Column('owner_id', sa.Integer(), nullable=True),
    sa.Column('ui_theme', sa.String(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.ForeignKeyConstraint(['owner_id'], ['data.users.id'], ),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_organizations_id'), 'organizations', ['id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_organizations_tax_number'), 'organizations', ['tax_number'], unique=False, schema='data')
    op.create_table('verification_tokens',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('token', sa.String(length=255), nullable=True),
    sa.Column('token_type', sa.Enum('EMAIL_VERIFY', 'PASSWORD_RESET', name='tokentype'), nullable=True),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_verification_tokens_id'), 'verification_tokens', ['id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_verification_tokens_token'), 'verification_tokens', ['token'], unique=True, schema='data')
    op.create_table('organization_members',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('org_id', sa.Integer(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('role', sa.Enum('OWNER', 'ADMIN', 'FLEET_MANAGER', 'DRIVER', name='userrole'), nullable=True),
    sa.Column('is_permanent', sa.Boolean(), nullable=True),
    sa.ForeignKeyConstraint(['org_id'], ['data.organizations.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_organization_members_id'), 'organization_members', ['id'], unique=False, schema='data')
    op.create_table('vehicles',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('vin', sa.String(), nullable=False),
    sa.Column('license_plate', sa.String(), nullable=False),
    sa.Column('make', sa.String(), nullable=True),
    sa.Column('model', sa.String(), nullable=True),
    sa.Column('year', sa.Integer(), nullable=True),
    sa.Column('is_deleted', sa.Boolean(), nullable=True),
    sa.Column('current_org_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.ForeignKeyConstraint(['current_org_id'], ['data.organizations.id'], ),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_vehicles_id'), 'vehicles', ['id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_vehicles_license_plate'), 'vehicles', ['license_plate'], unique=False, schema='data')
    op.create_index(op.f('ix_data_vehicles_vin'), 'vehicles', ['vin'], unique=True, schema='data')
    op.drop_index(op.f('ix_data_companies_id'), table_name='companies', schema='data')
    op.drop_table('companies', schema='data')
    op.drop_index(op.f('ix_data_company_members_id'), table_name='company_members', schema='data')
    op.drop_table('company_members', schema='data')
    op.drop_table('vehicle_options_catalog', schema='data')
    op.drop_index(op.f('ix_data_vehicle_ownerships_id'), table_name='vehicle_ownerships', schema='data')
    op.drop_table('vehicle_ownerships', schema='data')
    op.drop_table('user_stats', schema='data')
    op.drop_table('alembic_version')
    op.drop_index(op.f('ix_data_point_rules_action_key'), table_name='point_rules', schema='data')
    op.drop_index(op.f('ix_data_point_rules_id'), table_name='point_rules', schema='data')
    op.drop_table('point_rules', schema='data')
    op.drop_index(op.f('ix_data_level_configs_id'), table_name='level_configs', schema='data')
    op.drop_table('level_configs', schema='data')
    op.drop_table('vehicle_engines', schema='data')
    op.drop_index(op.f('ix_data_system_settings_key'), table_name='system_settings', schema='data')
    op.drop_table('system_settings', schema='data')
    op.drop_table('vehicle_models', schema='data')
    op.drop_index(op.f('ix_data_points_ledger_id'), table_name='points_ledger', schema='data')
    op.drop_index(op.f('ix_data_points_ledger_user_id'), table_name='points_ledger', schema='data')
    op.drop_table('points_ledger', schema='data')
    op.drop_index(op.f('ix_data_badges_id'), table_name='badges', schema='data')
    op.drop_table('badges', schema='data')
    op.drop_table('vehicle_categories', schema='data')
    op.drop_table('competitions', schema='data')
    op.drop_index(op.f('ix_data_service_providers_id'), table_name='service_providers', schema='data')
    op.drop_table('service_providers', schema='data')
    op.drop_table('vehicle_makes', schema='data')
    op.drop_table('votes', schema='data')
    op.drop_index(op.f('ix_data_vehicle_events_id'), table_name='vehicle_events', schema='data')
    op.drop_table('vehicle_events', schema='data')
    op.drop_index(op.f('ix_data_translations_id'), table_name='translations', schema='data')
    op.drop_index(op.f('ix_data_translations_key'), table_name='translations', schema='data')
    op.drop_index(op.f('ix_data_translations_lang_code'), table_name='translations', schema='data')
    op.drop_table('translations', schema='data')
    op.drop_table('staged_vehicle_data', schema='data')
    op.drop_index(op.f('ix_data_locations_id'), table_name='locations', schema='data')
    op.drop_table('locations', schema='data')
    op.drop_index(op.f('ix_data_regional_settings_id'), table_name='regional_settings', schema='data')
    op.drop_table('regional_settings', schema='data')
    op.drop_table('user_badges', schema='data')
    op.drop_index(op.f('ix_data_user_vehicles_id'), table_name='user_vehicles', schema='data')
    op.drop_index(op.f('ix_data_user_vehicles_license_plate'), table_name='user_vehicles', schema='data')
    op.drop_index(op.f('ix_data_user_vehicles_vin'), table_name='user_vehicles', schema='data')
    op.drop_table('user_vehicles', schema='data')
    op.drop_index(op.f('ix_data_audit_logs_id'), table_name='audit_logs', schema='data')
    op.drop_index(op.f('ix_data_audit_logs_target_id'), table_name='audit_logs', schema='data')
    op.drop_index(op.f('ix_data_audit_logs_target_type'), table_name='audit_logs', schema='data')
    op.drop_table('audit_logs', schema='data')
    op.drop_table('user_scores', schema='data')
    op.drop_table('user_vehicle_modifications', schema='data')
    op.drop_index(op.f('ix_data_vehicle_assignments_id'), table_name='vehicle_assignments', schema='data')
    op.drop_table('vehicle_assignments', schema='data')
    op.add_column('users', sa.Column('first_name', sa.String(), nullable=True), schema='data')
    op.add_column('users', sa.Column('last_name', sa.String(), nullable=True), schema='data')
    op.add_column('users', sa.Column('birthday', sa.Date(), nullable=True), schema='data')
    op.add_column('users', sa.Column('is_email_verified', sa.Boolean(), nullable=True), schema='data')
    op.add_column('users', sa.Column('is_banned', sa.Boolean(), nullable=True), schema='data')
    op.add_column('users', sa.Column('is_gdpr_deleted', sa.Boolean(), nullable=True), schema='data')
    op.add_column('users', sa.Column('previous_login_count', sa.Integer(), nullable=True), schema='data')
    op.add_column('users', sa.Column('verified_at', sa.DateTime(timezone=True), nullable=True), schema='data')
    op.drop_index(op.f('ix_data_users_region_code'), table_name='users', schema='data')
    op.drop_column('users', 'is_superuser', schema='data')
    op.drop_column('users', 'full_name', schema='data')
    op.drop_column('users', 'reputation_score', schema='data')
    op.drop_column('users', 'updated_at', schema='data')
    op.drop_column('users', 'license_expiry_date', schema='data')
    op.drop_column('users', 'role', schema='data')
    op.drop_column('users', 'region_code', schema='data')
    op.drop_column('users', 'id_card_expiry_date', schema='data')
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('id_card_expiry_date', sa.DATE(), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('region_code', sa.VARCHAR(length=5), server_default=sa.text("'HU'::character varying"), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('role', postgresql.ENUM('SUPERUSER', 'REGIONAL_ADMIN', 'MODERATOR', 'BUSINESS_PARTNER', 'USER', name='userrole'), server_default=sa.text("'USER'::userrole"), autoincrement=False, nullable=False), schema='data')
    op.add_column('users', sa.Column('license_expiry_date', sa.DATE(), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('reputation_score', sa.INTEGER(), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('full_name', sa.VARCHAR(), autoincrement=False, nullable=True), schema='data')
    op.add_column('users', sa.Column('is_superuser', sa.BOOLEAN(), autoincrement=False, nullable=True), schema='data')
    op.create_index(op.f('ix_data_users_region_code'), 'users', ['region_code'], unique=False, schema='data')
    op.drop_column('users', 'verified_at', schema='data')
    op.drop_column('users', 'previous_login_count', schema='data')
    op.drop_column('users', 'is_gdpr_deleted', schema='data')
    op.drop_column('users', 'is_banned', schema='data')
    op.drop_column('users', 'is_email_verified', schema='data')
    op.drop_column('users', 'birthday', schema='data')
    op.drop_column('users', 'last_name', schema='data')
    op.drop_column('users', 'first_name', schema='data')
    op.create_table('vehicle_assignments',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('company_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('vehicle_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('driver_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('start_date', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('end_date', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('start_odometer', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('end_odometer', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('notes', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['company_id'], ['data.companies.id'], name=op.f('vehicle_assignments_company_id_fkey')),
    sa.ForeignKeyConstraint(['driver_id'], ['data.users.id'], name=op.f('vehicle_assignments_driver_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_assignments_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_vehicle_assignments_id'), 'vehicle_assignments', ['id'], unique=False, schema='data')
    op.create_table('user_vehicle_modifications',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('vehicle_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('mod_type', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('category', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('is_validated', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('installed_at_km', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('cost', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('user_vehicle_modifications_pkey')),
    schema='data'
    )
    op.create_table('user_scores',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('competition_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('points', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('last_updated', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['competition_id'], ['data.competitions.id'], name=op.f('user_scores_competition_id_fkey')),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('user_scores_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('user_scores_pkey')),
    sa.UniqueConstraint('user_id', 'competition_id', name=op.f('uq_user_competition_score'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_table('audit_logs',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('target_type', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('target_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('action', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('changes', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.Column('ip_address', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('user_agent', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('timestamp', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('audit_logs_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('audit_logs_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_audit_logs_target_type'), 'audit_logs', ['target_type'], unique=False, schema='data')
    op.create_index(op.f('ix_data_audit_logs_target_id'), 'audit_logs', ['target_id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_audit_logs_id'), 'audit_logs', ['id'], unique=False, schema='data')
    op.create_table('user_vehicles',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('nickname', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('make', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('model', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('year', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('fuel_type', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('model_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('engine_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('license_plate', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('vin', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('color_code', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('manufacture_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('registration_region', sa.VARCHAR(length=5), autoincrement=False, nullable=True),
    sa.Column('initial_odometer', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('current_odometer', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('mot_expiry_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('insurance_expiry_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('factory_options', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['engine_id'], ['data.vehicle_engines.id'], name=op.f('user_vehicles_engine_id_fkey')),
    sa.ForeignKeyConstraint(['model_id'], ['data.vehicle_models.id'], name=op.f('user_vehicles_model_id_fkey')),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('user_vehicles_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('user_vehicles_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_user_vehicles_vin'), 'user_vehicles', ['vin'], unique=True, schema='data')
    op.create_index(op.f('ix_data_user_vehicles_license_plate'), 'user_vehicles', ['license_plate'], unique=False, schema='data')
    op.create_index(op.f('ix_data_user_vehicles_id'), 'user_vehicles', ['id'], unique=False, schema='data')
    op.create_table('user_badges',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('badge_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('earned_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['badge_id'], ['data.badges.id'], name=op.f('user_badges_badge_id_fkey')),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('user_badges_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('user_badges_pkey')),
    schema='data'
    )
    op.create_table('regional_settings',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('country_code', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('currency', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('regional_settings_pkey')),
    sa.UniqueConstraint('country_code', name=op.f('regional_settings_country_code_key'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_index(op.f('ix_data_regional_settings_id'), 'regional_settings', ['id'], unique=False, schema='data')
    op.create_table('locations',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('type', postgresql.ENUM('stop', 'warehouse', 'client', name='location_type_enum', schema='data'), autoincrement=False, nullable=False),
    sa.Column('coordinates', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('address_full', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('capacity', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('locations_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_locations_id'), 'locations', ['id'], unique=False, schema='data')
    op.create_table('staged_vehicle_data',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('source_url', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('raw_data', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('error_log', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('staged_vehicle_data_pkey')),
    schema='data'
    )
    op.create_table('translations',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('key', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('lang_code', sa.VARCHAR(length=5), autoincrement=False, nullable=False),
    sa.Column('value', sa.TEXT(), autoincrement=False, nullable=False),
    sa.Column('is_published', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('translations_pkey')),
    sa.UniqueConstraint('key', 'lang_code', name=op.f('uq_translation_key_lang'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_index(op.f('ix_data_translations_lang_code'), 'translations', ['lang_code'], unique=False, schema='data')
    op.create_index(op.f('ix_data_translations_key'), 'translations', ['key'], unique=False, schema='data')
    op.create_index(op.f('ix_data_translations_id'), 'translations', ['id'], unique=False, schema='data')
    op.create_table('vehicle_events',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('vehicle_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('event_type', postgresql.ENUM('PURCHASE_PRICE', 'TRANSFER_TAX', 'ADMIN_FEE', 'VEHICLE_TAX', 'INSURANCE', 'REFUELING', 'SERVICE', 'PARKING', 'TOLL', 'FINE', 'TUNING_ACCESSORIES', 'OTHER', name='expense_category_enum', schema='data'), autoincrement=False, nullable=False),
    sa.Column('date', sa.DATE(), autoincrement=False, nullable=False),
    sa.Column('odometer_value', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('odometer_anomaly', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('cost_amount', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('image_paths', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.Column('is_diy', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('service_provider_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['service_provider_id'], ['data.service_providers.id'], name=op.f('vehicle_events_service_provider_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_events_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_vehicle_events_id'), 'vehicle_events', ['id'], unique=False, schema='data')
    op.create_table('votes',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('provider_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('vote_value', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['provider_id'], ['data.service_providers.id'], name=op.f('votes_provider_id_fkey')),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('votes_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('votes_pkey')),
    sa.UniqueConstraint('user_id', 'provider_id', name=op.f('uq_user_provider_vote'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_table('vehicle_makes',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('logo_url', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_makes_pkey')),
    sa.UniqueConstraint('name', name=op.f('vehicle_makes_name_key'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_table('service_providers',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('address', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('category', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('status', postgresql.ENUM('pending', 'approved', 'rejected', name='moderation_status_enum', schema='data'), autoincrement=False, nullable=False),
    sa.Column('source', postgresql.ENUM('manual', 'ocr', 'api_import', name='source_type_enum', schema='data'), autoincrement=False, nullable=False),
    sa.Column('validation_score', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('evidence_image_path', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('added_by_user_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['added_by_user_id'], ['data.users.id'], name=op.f('service_providers_added_by_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('service_providers_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_service_providers_id'), 'service_providers', ['id'], unique=False, schema='data')
    op.create_table('competitions',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('description', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('start_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('end_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('competitions_pkey')),
    schema='data'
    )
    op.create_table('vehicle_categories',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name_key', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_categories_pkey')),
    schema='data'
    )
    op.create_table('badges',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(length=255), autoincrement=False, nullable=False),
    sa.Column('icon_url', sa.VARCHAR(length=500), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('badges_pkey')),
    sa.UniqueConstraint('name', name=op.f('badges_name_key'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_index(op.f('ix_data_badges_id'), 'badges', ['id'], unique=False, schema='data')
    op.create_table('points_ledger',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('points_change', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('reason', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.Column('timestamp', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('points_ledger_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('points_ledger_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_points_ledger_user_id'), 'points_ledger', ['user_id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_points_ledger_id'), 'points_ledger', ['id'], unique=False, schema='data')
    op.create_table('vehicle_models',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('make_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('category_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('generation_name', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('production_start_year', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('production_end_year', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['category_id'], ['data.vehicle_categories.id'], name=op.f('vehicle_models_category_id_fkey')),
    sa.ForeignKeyConstraint(['make_id'], ['data.vehicle_makes.id'], name=op.f('vehicle_models_make_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_models_pkey')),
    schema='data'
    )
    op.create_table('system_settings',
    sa.Column('key', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('value', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('key', name=op.f('system_settings_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_system_settings_key'), 'system_settings', ['key'], unique=False, schema='data')
    op.create_table('vehicle_engines',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('model_id', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('engine_code', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('fuel_type', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('displacement_ccm', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('power_kw', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('torque_nm', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('transmission_type', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('gears_count', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('drive_type', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['model_id'], ['data.vehicle_models.id'], name=op.f('vehicle_engines_model_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_engines_pkey')),
    schema='data'
    )
    op.create_table('level_configs',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('level_number', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('min_points', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('rank_name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('level_configs_pkey')),
    sa.UniqueConstraint('level_number', name=op.f('level_configs_level_number_key'), postgresql_include=[], postgresql_nulls_not_distinct=False),
    schema='data'
    )
    op.create_index(op.f('ix_data_level_configs_id'), 'level_configs', ['id'], unique=False, schema='data')
    op.create_table('point_rules',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('action_key', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('points', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('point_rules_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_point_rules_id'), 'point_rules', ['id'], unique=False, schema='data')
    op.create_index(op.f('ix_data_point_rules_action_key'), 'point_rules', ['action_key'], unique=True, schema='data')
    op.create_table('alembic_version',
    sa.Column('version_num', sa.VARCHAR(length=32), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('version_num', name=op.f('alembic_version_pkc'))
    )
    op.create_table('user_stats',
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('total_points', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=False),
    sa.Column('current_level', sa.INTEGER(), server_default=sa.text('1'), autoincrement=False, nullable=False),
    sa.Column('last_updated', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('user_stats_user_id_fkey')),
    sa.PrimaryKeyConstraint('user_id', name=op.f('user_stats_pkey')),
    schema='data'
    )
    op.create_table('vehicle_ownerships',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('vehicle_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('start_date', sa.DATE(), autoincrement=False, nullable=False),
    sa.Column('end_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('vehicle_ownerships_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_ownerships_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_vehicle_ownerships_id'), 'vehicle_ownerships', ['id'], unique=False, schema='data')
    op.create_table('vehicle_options_catalog',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('category', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('name_key', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('vehicle_options_catalog_pkey')),
    schema='data'
    )
    op.create_table('company_members',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('company_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('role', postgresql.ENUM('OWNER', 'MANAGER', 'DRIVER', name='companyrole'), autoincrement=False, nullable=False),
    sa.Column('can_edit_service', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('can_see_costs', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['company_id'], ['data.companies.id'], name=op.f('company_members_company_id_fkey')),
    sa.ForeignKeyConstraint(['user_id'], ['data.users.id'], name=op.f('company_members_user_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('company_members_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_company_members_id'), 'company_members', ['id'], unique=False, schema='data')
    op.create_table('companies',
    sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('tax_number', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('subscription_tier', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('owner_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['owner_id'], ['data.users.id'], name=op.f('companies_owner_id_fkey')),
    sa.PrimaryKeyConstraint('id', name=op.f('companies_pkey')),
    schema='data'
    )
    op.create_index(op.f('ix_data_companies_id'), 'companies', ['id'], unique=False, schema='data')
    op.drop_index(op.f('ix_data_vehicles_vin'), table_name='vehicles', schema='data')
    op.drop_index(op.f('ix_data_vehicles_license_plate'), table_name='vehicles', schema='data')
    op.drop_index(op.f('ix_data_vehicles_id'), table_name='vehicles', schema='data')
    op.drop_table('vehicles', schema='data')
    op.drop_index(op.f('ix_data_organization_members_id'), table_name='organization_members', schema='data')
    op.drop_table('organization_members', schema='data')
    op.drop_index(op.f('ix_data_verification_tokens_token'), table_name='verification_tokens', schema='data')
    op.drop_index(op.f('ix_data_verification_tokens_id'), table_name='verification_tokens', schema='data')
    op.drop_table('verification_tokens', schema='data')
    op.drop_index(op.f('ix_data_organizations_tax_number'), table_name='organizations', schema='data')
    op.drop_index(op.f('ix_data_organizations_id'), table_name='organizations', schema='data')
    op.drop_table('organizations', schema='data')
    op.drop_index(op.f('ix_data_legal_acceptances_id'), table_name='legal_acceptances', schema='data')
    op.drop_table('legal_acceptances', schema='data')
    op.drop_index(op.f('ix_data_legal_documents_id'), table_name='legal_documents', schema='data')
    op.drop_table('legal_documents', schema='data')
    op.drop_index(op.f('ix_data_email_templates_type'), table_name='email_templates', schema='data')
    op.drop_index(op.f('ix_data_email_templates_id'), table_name='email_templates', schema='data')
    op.drop_table('email_templates', schema='data')
    op.drop_index(op.f('ix_data_email_provider_configs_id'), table_name='email_provider_configs', schema='data')
    op.drop_table('email_provider_configs', schema='data')
    op.drop_index(op.f('ix_data_email_logs_id'), table_name='email_logs', schema='data')
    op.drop_index(op.f('ix_data_email_logs_email'), table_name='email_logs', schema='data')
    op.drop_table('email_logs', schema='data')
    # ### end Alembic commands ###
