"""identity_and_hybrid_org_update

Revision ID: 25afe6f4f063
Revises: 398e76c2fa36
Create Date: 2026-02-12 22:38:04.309546

"""
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 = '25afe6f4f063'
down_revision: Union[str, Sequence[str], None] = '398e76c2fa36'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    
    # --- MANUÁLIS JAVÍTÁS: Enum típus létrehozása a sémában ---
    org_user_role = postgresql.ENUM('OWNER', 'ADMIN', 'FLEET_MANAGER', 'DRIVER', 'MECHANIC', 'RECEPTIONIST', name='orguserrole', schema='data')
    org_user_role.create(op.get_bind(), checkfirst=True)

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('organization_financials',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('organization_id', sa.Integer(), nullable=False),
    sa.Column('year', sa.Integer(), nullable=False),
    sa.Column('turnover', sa.Numeric(precision=18, scale=2), nullable=True),
    sa.Column('profit', sa.Numeric(precision=18, scale=2), nullable=True),
    sa.Column('employee_count', sa.Integer(), nullable=True),
    sa.Column('source', sa.String(length=50), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
    sa.ForeignKeyConstraint(['organization_id'], ['data.organizations.id'], ),
    sa.PrimaryKeyConstraint('id'),
    schema='data'
    )
    op.create_index(op.f('ix_data_organization_financials_id'), 'organization_financials', ['id'], unique=False, schema='data')
    op.add_column('addresses', sa.Column('latitude', sa.Float(), nullable=True))
    op.add_column('addresses', sa.Column('longitude', sa.Float(), nullable=True))
    op.drop_constraint('addresses_postal_code_id_fkey', 'addresses', type_='foreignkey')
    op.create_foreign_key(None, 'addresses', 'geo_postal_codes', ['postal_code_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Asset Assignments fix
    op.drop_constraint('asset_assignments_asset_id_fkey', 'asset_assignments', type_='foreignkey')
    op.drop_constraint('asset_assignments_organization_id_fkey', 'asset_assignments', type_='foreignkey')
    op.create_foreign_key(None, 'asset_assignments', 'organizations', ['organization_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'asset_assignments', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Asset Costs fix
    op.drop_constraint('asset_costs_driver_id_fkey', 'asset_costs', type_='foreignkey')
    op.drop_constraint('asset_costs_organization_id_fkey', 'asset_costs', type_='foreignkey')
    op.drop_constraint('asset_costs_asset_id_fkey', 'asset_costs', type_='foreignkey')
    op.create_foreign_key(None, 'asset_costs', 'organizations', ['organization_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'asset_costs', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'asset_costs', 'users', ['driver_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Egyéb Asset és Audit kapcsolatok
    op.drop_constraint('asset_events_asset_id_fkey', 'asset_events', type_='foreignkey')
    op.create_foreign_key(None, 'asset_events', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('asset_financials_asset_id_fkey', 'asset_financials', type_='foreignkey')
    op.create_foreign_key(None, 'asset_financials', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('asset_reviews_user_id_fkey', 'asset_reviews', type_='foreignkey')
    op.drop_constraint('asset_reviews_asset_id_fkey', 'asset_reviews', type_='foreignkey')
    op.create_foreign_key(None, 'asset_reviews', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'asset_reviews', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('asset_telemetry_asset_id_fkey', 'asset_telemetry', type_='foreignkey')
    op.create_foreign_key(None, 'asset_telemetry', 'assets', ['asset_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('assets_catalog_id_fkey', 'assets', type_='foreignkey')
    op.drop_constraint('assets_current_organization_id_fkey', 'assets', type_='foreignkey')
    op.create_foreign_key(None, 'assets', 'vehicle_catalog', ['catalog_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'assets', 'organizations', ['current_organization_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('audit_logs_user_id_fkey', 'audit_logs', type_='foreignkey')
    op.create_foreign_key(None, 'audit_logs', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('credit_logs_org_id_fkey', 'credit_logs', type_='foreignkey')
    op.create_foreign_key(None, 'credit_logs', 'organizations', ['org_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('documents_uploaded_by_fkey', 'documents', type_='foreignkey')
    op.create_foreign_key(None, 'documents', 'users', ['uploaded_by'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('geo_streets_postal_code_id_fkey', 'geo_streets', type_='foreignkey')
    op.create_foreign_key(None, 'geo_streets', 'geo_postal_codes', ['postal_code_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('org_subscriptions_tier_id_fkey', 'org_subscriptions', type_='foreignkey')
    op.drop_constraint('org_subscriptions_org_id_fkey', 'org_subscriptions', type_='foreignkey')
    op.create_foreign_key(None, 'org_subscriptions', 'organizations', ['org_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'org_subscriptions', 'subscription_tiers', ['tier_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Organization Members kiegészítése
    op.add_column('organization_members', sa.Column('person_id', sa.BigInteger(), nullable=True))
    op.add_column('organization_members', sa.Column('is_permanent', sa.Boolean(), nullable=True))
    op.alter_column('organization_members', 'user_id', existing_type=sa.INTEGER(), nullable=True)
    
    # ENUM casting fix (kisbetű nagybetűvé alakítás)
    op.alter_column('organization_members', 'role',
               existing_type=sa.VARCHAR(),
               type_=org_user_role,
               existing_nullable=True,
               postgresql_using='UPPER(role)::data.orguserrole')

    op.drop_constraint('organization_members_organization_id_fkey', 'organization_members', type_='foreignkey')
    op.drop_constraint('organization_members_user_id_fkey', 'organization_members', type_='foreignkey')
    op.create_foreign_key(None, 'organization_members', 'organizations', ['organization_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'organization_members', 'persons', ['person_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'organization_members', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Organization Schema fix
    op.alter_column('organizations', 'org_type',
               existing_type=postgresql.ENUM('individual', 'service', 'service_provider', 'fleet_owner', 'club', 'business', name='orgtype'),
               type_=postgresql.ENUM('individual', 'service', 'service_provider', 'fleet_owner', 'club', 'business', name='orgtype', schema='data', inherit_schema=True),
               existing_nullable=True)
    op.drop_constraint('organizations_owner_id_fkey', 'organizations', type_='foreignkey')
    op.drop_constraint('organizations_address_id_fkey', 'organizations', type_='foreignkey')
    op.create_foreign_key(None, 'organizations', 'addresses', ['address_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'organizations', 'users', ['owner_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Felesleges oszlopok törlése
    op.drop_column('organizations', 'address_floor')
    op.drop_column('organizations', 'verification_expires_at')
    op.drop_column('organizations', 'is_transferable')
    op.drop_column('organizations', 'address_door')
    op.drop_column('organizations', 'address_stairwell')
    
    # Pending actions
    op.drop_constraint('pending_actions_requester_id_fkey', 'pending_actions', type_='foreignkey')
    op.drop_constraint('pending_actions_approver_id_fkey', 'pending_actions', type_='foreignkey')
    op.create_foreign_key(None, 'pending_actions', 'users', ['approver_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'pending_actions', 'users', ['requester_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Person és egyéb kapcsolatok
    op.add_column('persons', sa.Column('is_ghost', sa.Boolean(), nullable=False, server_default='true'))
    op.drop_constraint('persons_address_id_fkey', 'persons', type_='foreignkey')
    op.create_foreign_key(None, 'persons', 'addresses', ['address_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('points_ledger_user_id_fkey', 'points_ledger', type_='foreignkey')
    op.create_foreign_key(None, 'points_ledger', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('ratings_author_id_fkey', 'ratings', type_='foreignkey')
    op.create_foreign_key(None, 'ratings', 'users', ['author_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('service_expertises_expertise_id_fkey', 'service_expertises', type_='foreignkey')
    op.drop_constraint('service_expertises_service_id_fkey', 'service_expertises', type_='foreignkey')
    op.create_foreign_key(None, 'service_expertises', 'expertise_tags', ['expertise_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'service_expertises', 'service_profiles', ['service_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Service Profile Enrichment
    op.add_column('service_profiles', sa.Column('google_place_id', sa.String(length=100), nullable=True))
    op.add_column('service_profiles', sa.Column('rating', sa.Float(), nullable=True))
    op.add_column('service_profiles', sa.Column('user_ratings_total', sa.Integer(), nullable=True))
    op.add_column('service_profiles', sa.Column('specialization_tags', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), nullable=True))
    op.create_unique_constraint(None, 'service_profiles', ['google_place_id'], schema='data')
    op.drop_constraint('service_profiles_organization_id_fkey', 'service_profiles', type_='foreignkey')
    op.create_foreign_key(None, 'service_profiles', 'organizations', ['organization_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('service_specialties_parent_id_fkey', 'service_specialties', type_='foreignkey')
    op.create_foreign_key(None, 'service_specialties', 'service_specialties', ['parent_id'], ['id'], source_schema='data', referent_schema='data')
    
    # Social Accounts
    op.drop_constraint('social_accounts_user_id_fkey', 'social_accounts', type_='foreignkey')
    op.create_foreign_key(None, 'social_accounts', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data', ondelete='CASCADE')
    
    # User / Wallet / Tokens
    op.drop_constraint('user_badges_badge_id_fkey', 'user_badges', type_='foreignkey')
    op.drop_constraint('user_badges_user_id_fkey', 'user_badges', type_='foreignkey')
    op.create_foreign_key(None, 'user_badges', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'user_badges', 'badges', ['badge_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('user_stats_user_id_fkey', 'user_stats', type_='foreignkey')
    op.create_foreign_key(None, 'user_stats', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('users_person_id_fkey', 'users', type_='foreignkey')
    op.create_foreign_key(None, 'users', 'persons', ['person_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('vehicle_ownerships_user_id_fkey', 'vehicle_ownerships', type_='foreignkey')
    op.drop_constraint('vehicle_ownerships_vehicle_id_fkey', 'vehicle_ownerships', type_='foreignkey')
    op.create_foreign_key(None, 'vehicle_ownerships', 'assets', ['vehicle_id'], ['id'], source_schema='data', referent_schema='data')
    op.create_foreign_key(None, 'vehicle_ownerships', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')
    op.drop_constraint('verification_tokens_user_id_fkey', 'verification_tokens', type_='foreignkey')
    op.create_foreign_key(None, 'verification_tokens', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data', ondelete='CASCADE')
    op.drop_constraint('wallets_user_id_fkey', 'wallets', type_='foreignkey')
    op.create_foreign_key(None, 'wallets', 'users', ['user_id'], ['id'], source_schema='data', referent_schema='data')


def downgrade() -> None:
    """Downgrade schema."""
    org_user_role = postgresql.ENUM('OWNER', 'ADMIN', 'FLEET_MANAGER', 'DRIVER', 'MECHANIC', 'RECEPTIONIST', name='orguserrole', schema='data')

    # Alap adatok visszagörgetése
    op.drop_table('organization_financials', schema='data')
    op.drop_index(op.f('ix_data_organization_financials_id'), table_name='organization_financials', schema='data')
    op.drop_column('addresses', 'longitude')
    op.drop_column('addresses', 'latitude')
    
    # Role visszaállítása (Stringre)
    op.alter_column('organization_members', 'role',
               existing_type=org_user_role,
               type_=sa.VARCHAR(),
               existing_nullable=True)
    
    op.drop_column('organization_members', 'is_permanent')
    op.drop_column('organization_members', 'person_id')
    op.drop_column('persons', 'is_ghost')
    
    # Service Profile takarítás
    op.drop_constraint(None, 'service_profiles', schema='data', type_='unique')
    op.drop_column('service_profiles', 'specialization_tags')
    op.drop_column('service_profiles', 'user_ratings_total')
    op.drop_column('service_profiles', 'rating')
    op.drop_column('service_profiles', 'google_place_id')

    # Enum törlése legutoljára
    org_user_role.drop(op.get_bind(), checkfirst=True)