"""implement soft deletes and remove cascades

Revision ID: d5f8a2e4c7b9
Revises: c693ade3601c
Create Date: 2024-03-20 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = 'd5f8a2e4c7b9'
down_revision = 'c693ade3601c'
branch_labels = None
depends_on = None

def upgrade():
    # Add soft delete columns to relevant tables
    tables_for_soft_delete = [
        'users', 'groups', 'lists', 'items', 'expenses', 'expense_splits',
        'chores', 'chore_assignments', 'recurrence_patterns', 'categories',
        'time_entries'
    ]
    
    for table in tables_for_soft_delete:
        op.add_column(table, sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True))
        op.add_column(table, sa.Column('is_deleted', sa.Boolean(), server_default='false', nullable=False))
        op.create_index(f'ix_{table}_deleted_at', table, ['deleted_at'])
        op.create_index(f'ix_{table}_is_deleted', table, ['is_deleted'])

    # Remove cascade deletes from foreign keys
    # First drop existing foreign keys with cascade
    op.drop_constraint('user_groups_user_id_fkey', 'user_groups', type_='foreignkey')
    op.drop_constraint('user_groups_group_id_fkey', 'user_groups', type_='foreignkey')
    op.drop_constraint('invites_group_id_fkey', 'invites', type_='foreignkey')
    op.drop_constraint('items_list_id_fkey', 'items', type_='foreignkey')
    op.drop_constraint('expense_splits_expense_id_fkey', 'expense_splits', type_='foreignkey')
    op.drop_constraint('chores_group_id_fkey', 'chores', type_='foreignkey')
    op.drop_constraint('chore_assignments_chore_id_fkey', 'chore_assignments', type_='foreignkey')
    op.drop_constraint('chore_assignments_assigned_to_user_id_fkey', 'chore_assignments', type_='foreignkey')
    op.drop_constraint('chore_history_chore_id_fkey', 'chore_history', type_='foreignkey')
    op.drop_constraint('chore_history_group_id_fkey', 'chore_history', type_='foreignkey')
    op.drop_constraint('chore_assignment_history_assignment_id_fkey', 'chore_assignment_history', type_='foreignkey')
    op.drop_constraint('time_entries_chore_assignment_id_fkey', 'time_entries', type_='foreignkey')

    # Recreate foreign keys without cascade delete
    op.create_foreign_key('user_groups_user_id_fkey', 'user_groups', 'users', ['user_id'], ['id'])
    op.create_foreign_key('user_groups_group_id_fkey', 'user_groups', 'groups', ['group_id'], ['id'])
    op.create_foreign_key('invites_group_id_fkey', 'invites', 'groups', ['group_id'], ['id'])
    op.create_foreign_key('items_list_id_fkey', 'items', 'lists', ['list_id'], ['id'])
    op.create_foreign_key('expense_splits_expense_id_fkey', 'expense_splits', 'expenses', ['expense_id'], ['id'])
    op.create_foreign_key('chores_group_id_fkey', 'chores', 'groups', ['group_id'], ['id'])
    op.create_foreign_key('chore_assignments_chore_id_fkey', 'chore_assignments', 'chores', ['chore_id'], ['id'])
    op.create_foreign_key('chore_assignments_assigned_to_user_id_fkey', 'chore_assignments', 'users', ['assigned_to_user_id'], ['id'])
    op.create_foreign_key('chore_history_chore_id_fkey', 'chore_history', 'chores', ['chore_id'], ['id'])
    op.create_foreign_key('chore_history_group_id_fkey', 'chore_history', 'groups', ['group_id'], ['id'])
    op.create_foreign_key('chore_assignment_history_assignment_id_fkey', 'chore_assignment_history', 'chore_assignments', ['assignment_id'], ['id'])
    op.create_foreign_key('time_entries_chore_assignment_id_fkey', 'time_entries', 'chore_assignments', ['chore_assignment_id'], ['id'])

def downgrade():
    # Remove soft delete columns
    tables_for_soft_delete = [
        'users', 'groups', 'lists', 'items', 'expenses', 'expense_splits',
        'chores', 'chore_assignments', 'recurrence_patterns', 'categories',
        'time_entries'
    ]
    
    for table in tables_for_soft_delete:
        op.drop_index(f'ix_{table}_deleted_at', table)
        op.drop_index(f'ix_{table}_is_deleted', table)
        op.drop_column(table, 'deleted_at')
        op.drop_column(table, 'is_deleted')

    # Restore cascade deletes
    op.drop_constraint('user_groups_user_id_fkey', 'user_groups', type_='foreignkey')
    op.drop_constraint('user_groups_group_id_fkey', 'user_groups', type_='foreignkey')
    op.drop_constraint('invites_group_id_fkey', 'invites', type_='foreignkey')
    op.drop_constraint('items_list_id_fkey', 'items', type_='foreignkey')
    op.drop_constraint('expense_splits_expense_id_fkey', 'expense_splits', type_='foreignkey')
    op.drop_constraint('chores_group_id_fkey', 'chores', type_='foreignkey')
    op.drop_constraint('chore_assignments_chore_id_fkey', 'chore_assignments', type_='foreignkey')
    op.drop_constraint('chore_assignments_assigned_to_user_id_fkey', 'chore_assignments', type_='foreignkey')
    op.drop_constraint('chore_history_chore_id_fkey', 'chore_history', type_='foreignkey')
    op.drop_constraint('chore_history_group_id_fkey', 'chore_history', type_='foreignkey')
    op.drop_constraint('chore_assignment_history_assignment_id_fkey', 'chore_assignment_history', type_='foreignkey')
    op.drop_constraint('time_entries_chore_assignment_id_fkey', 'time_entries', type_='foreignkey')

    # Recreate foreign keys with cascade delete
    op.create_foreign_key('user_groups_user_id_fkey', 'user_groups', 'users', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('user_groups_group_id_fkey', 'user_groups', 'groups', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('invites_group_id_fkey', 'invites', 'groups', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('items_list_id_fkey', 'items', 'lists', ['list_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('expense_splits_expense_id_fkey', 'expense_splits', 'expenses', ['expense_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chores_group_id_fkey', 'chores', 'groups', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chore_assignments_chore_id_fkey', 'chore_assignments', 'chores', ['chore_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chore_assignments_assigned_to_user_id_fkey', 'chore_assignments', 'users', ['assigned_to_user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chore_history_chore_id_fkey', 'chore_history', 'chores', ['chore_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chore_history_group_id_fkey', 'chore_history', 'groups', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('chore_assignment_history_assignment_id_fkey', 'chore_assignment_history', 'chore_assignments', ['assignment_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key('time_entries_chore_assignment_id_fkey', 'time_entries', 'chore_assignments', ['chore_assignment_id'], ['id'], ondelete='CASCADE') 