"""add_relationships_between_tables

Revision ID: a269dcc38e08
Revises: 1fad074c433a
Create Date: 2025-09-04 07:04:17.126822

"""
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 = 'a269dcc38e08'
down_revision: Union[str, None] = '1fad074c433a'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    # Drop existing foreign key constraints if they exist
    # For subscriptions table
    op.drop_constraint('subscriptions_company_id_fkey', 'subscriptions', type_='foreignkey')
    op.drop_constraint('subscriptions_plan_id_fkey', 'subscriptions', type_='foreignkey')
    
    # For role_department table
    op.drop_constraint('role_department_role_id_fkey', 'role_department', type_='foreignkey')
    op.drop_constraint('role_department_department_id_fkey', 'role_department', type_='foreignkey')
    
    # For plan_modules table
    op.drop_constraint('plan_modules_plan_id_fkey', 'plan_modules', type_='foreignkey')
    op.drop_constraint('plan_modules_module_id_fkey', 'plan_modules', type_='foreignkey')
    
    # For permissions table
    op.drop_constraint('permissions_module_id_fkey', 'permissions', type_='foreignkey')
    
    # For role_department_permissions table
    op.drop_constraint('role_department_permissions_role_department_id_fkey', 'role_department_permissions', type_='foreignkey')
    op.drop_constraint('role_department_permissions_permission_id_fkey', 'role_department_permissions', type_='foreignkey')
    
    # For user_roles table
    op.drop_constraint('user_roles_user_id_fkey', 'user_roles', type_='foreignkey')
    op.drop_constraint('user_roles_role_department_id_fkey', 'user_roles', type_='foreignkey')
    op.drop_constraint('user_roles_company_id_fkey', 'user_roles', type_='foreignkey')
    
    # Add new foreign key constraints with CASCADE
    # subscriptions table
    op.create_foreign_key(
        'subscriptions_company_id_fkey',
        'subscriptions', 'companies',
        ['company_id'], ['id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'subscriptions_plan_id_fkey',
        'subscriptions', 'subscription_plans',
        ['plan_id'], ['subscription_plan_id'],
        ondelete='CASCADE'
    )
    
    # role_department table
    op.create_foreign_key(
        'role_department_role_id_fkey',
        'role_department', 'roles',
        ['role_id'], ['role_id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'role_department_department_id_fkey',
        'role_department', 'departments',
        ['department_id'], ['department_id'],
        ondelete='CASCADE'
    )
    
    # plan_modules table
    op.create_foreign_key(
        'plan_modules_plan_id_fkey',
        'plan_modules', 'subscription_plans',
        ['plan_id'], ['subscription_plan_id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'plan_modules_module_id_fkey',
        'plan_modules', 'modules',
        ['module_id'], ['module_id'],
        ondelete='CASCADE'
    )
    
    # permissions table
    op.create_foreign_key(
        'permissions_module_id_fkey',
        'permissions', 'modules',
        ['module_id'], ['module_id'],
        ondelete='CASCADE'
    )
    
    # role_department_permissions table
    op.create_foreign_key(
        'role_department_permissions_role_department_id_fkey',
        'role_department_permissions', 'role_department',
        ['role_department_id'], ['role_department_id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'role_department_permissions_permission_id_fkey',
        'role_department_permissions', 'permissions',
        ['permission_id'], ['permission_id'],
        ondelete='CASCADE'
    )
    
    # user_roles table
    op.create_foreign_key(
        'user_roles_user_id_fkey',
        'user_roles', 'users',
        ['user_id'], ['id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'user_roles_role_department_id_fkey',
        'user_roles', 'role_department',
        ['role_department_id'], ['role_department_id'],
        ondelete='CASCADE'
    )
    op.create_foreign_key(
        'user_roles_company_id_fkey',
        'user_roles', 'companies',
        ['company_id'], ['id'],
        ondelete='CASCADE'
    )

def downgrade():
    # Drop the new constraints
    op.drop_constraint('subscriptions_company_id_fkey', 'subscriptions', type_='foreignkey')
    op.drop_constraint('subscriptions_plan_id_fkey', 'subscriptions', type_='foreignkey')
    
    op.drop_constraint('role_department_role_id_fkey', 'role_department', type_='foreignkey')
    op.drop_constraint('role_department_department_id_fkey', 'role_department', type_='foreignkey')
    
    op.drop_constraint('plan_modules_plan_id_fkey', 'plan_modules', type_='foreignkey')
    op.drop_constraint('plan_modules_module_id_fkey', 'plan_modules', type_='foreignkey')
    
    op.drop_constraint('permissions_module_id_fkey', 'permissions', type_='foreignkey')
    
    op.drop_constraint('role_department_permissions_role_department_id_fkey', 'role_department_permissions', type_='foreignkey')
    op.drop_constraint('role_department_permissions_permission_id_fkey', 'role_department_permissions', type_='foreignkey')
    
    op.drop_constraint('user_roles_user_id_fkey', 'user_roles', type_='foreignkey')
    op.drop_constraint('user_roles_role_department_id_fkey', 'user_roles', type_='foreignkey')
    op.drop_constraint('user_roles_company_id_fkey', 'user_roles', type_='foreignkey')
    
    # Re-add the old constraints (without CASCADE if that was the original)
    op.create_foreign_key(
        'subscriptions_company_id_fkey',
        'subscriptions', 'companies',
        ['company_id'], ['id']
    )
    op.create_foreign_key(
        'subscriptions_plan_id_fkey',
        'subscriptions', 'subscription_plans',
        ['plan_id'], ['subscription_plan_id']
    )
    
    # Add back other constraints similarly...
    # Note: You may need to adjust this based on your original constraint names