"""Initial Seva Setu schema Revision ID: 98f8d7817d98 Revises: Create Date: 2026-09-30 09:57:45.021703 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = '98f8d7817d98' down_revision = None branch_labels = None depends_on = None def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.create_table('activity_types', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('badges', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('description', sa.String(length=500), nullable=True), sa.Column('icon_path', sa.String(length=500), nullable=True), sa.Column('hearts_required', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('business_units', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('cause_categories', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('description', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('cities', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=20), nullable=False), sa.Column('name', sa.String(length=100), nullable=False), sa.Column('state_name', sa.String(length=100), nullable=True), sa.Column('launch_eligible', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('departments', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('designations', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name') ) op.create_table('heart_rules', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('action_type', sa.String(length=50), nullable=False), sa.Column('calculation_type', sa.String(length=30), nullable=False), sa.Column('unit_value', sa.Numeric(precision=14, scale=2), nullable=False), sa.Column('hearts', sa.Integer(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('impact_metrics', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('unit', sa.String(length=50), nullable=False), sa.Column('methodology', sa.Text(), nullable=True), sa.Column('default_verification_status', sa.String(length=30), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('integration_logs', sa.Column('id', sa.Integer(), nullable=False), sa.Column('integration', sa.String(length=50), nullable=False), sa.Column('direction', sa.String(length=20), nullable=False), sa.Column('reference_type', sa.String(length=100), nullable=True), sa.Column('reference_id', sa.String(length=100), nullable=True), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('request_payload', sa.JSON(), nullable=True), sa.Column('response_payload', sa.JSON(), nullable=True), sa.Column('error_message', sa.Text(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('integration_logs', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_integration_logs_integration'), ['integration'], unique=False) op.create_table('notification_templates', sa.Column('id', sa.Integer(), nullable=False), sa.Column('event_code', sa.String(length=100), nullable=False), sa.Column('channel', sa.String(length=30), nullable=False), sa.Column('subject', sa.String(length=255), nullable=True), sa.Column('body', sa.Text(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('event_code', 'channel', name='uq_notification_template') ) with op.batch_alter_table('notification_templates', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_notification_templates_event_code'), ['event_code'], unique=False) op.create_table('partners', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=255), nullable=False), sa.Column('partner_type', sa.String(length=50), nullable=False), sa.Column('registration_number', sa.String(length=100), nullable=True), sa.Column('contact_name', sa.String(length=150), nullable=True), sa.Column('contact_email', sa.String(length=255), nullable=True), sa.Column('contact_phone', sa.String(length=30), nullable=True), sa.Column('website', sa.String(length=255), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.PrimaryKeyConstraint('id') ) op.create_table('permissions', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=100), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('roles', sa.Column('id', sa.Integer(), nullable=False), sa.Column('code', sa.String(length=50), nullable=False), sa.Column('name', sa.String(length=100), nullable=False), sa.Column('description', sa.String(length=255), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code') ) op.create_table('system_settings', sa.Column('id', sa.Integer(), nullable=False), sa.Column('key', sa.String(length=100), nullable=False), sa.Column('value', sa.Text(), nullable=True), sa.Column('value_type', sa.String(length=30), nullable=False), sa.Column('description', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('key') ) op.create_table('users', sa.Column('id', sa.Integer(), nullable=False), sa.Column('official_email', sa.String(length=255), nullable=False), sa.Column('password_hash', sa.String(length=255), nullable=True), sa.Column('sso_subject', sa.String(length=255), nullable=True), sa.Column('last_login_at', sa.DateTime(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('users', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_users_official_email'), ['official_email'], unique=True) batch_op.create_index(batch_op.f('ix_users_sso_subject'), ['sso_subject'], unique=True) op.create_table('audit_logs', sa.Column('id', sa.Integer(), nullable=False), sa.Column('user_id', sa.Integer(), nullable=True), sa.Column('entity_type', sa.String(length=100), nullable=False), sa.Column('entity_id', sa.String(length=100), nullable=False), sa.Column('action', sa.String(length=100), nullable=False), sa.Column('old_values', sa.JSON(), nullable=True), sa.Column('new_values', sa.JSON(), nullable=True), sa.Column('ip_address', sa.String(length=64), nullable=True), sa.Column('user_agent', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['user_id'], ['users.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('audit_logs', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_audit_logs_action'), ['action'], unique=False) batch_op.create_index(batch_op.f('ix_audit_logs_entity_id'), ['entity_id'], unique=False) batch_op.create_index(batch_op.f('ix_audit_logs_entity_type'), ['entity_type'], unique=False) op.create_table('donation_causes', sa.Column('id', sa.Integer(), nullable=False), sa.Column('cause_code', sa.String(length=30), nullable=False), sa.Column('name', sa.String(length=255), nullable=False), sa.Column('cause_category_id', sa.Integer(), nullable=False), sa.Column('partner_id', sa.Integer(), nullable=True), sa.Column('problem_statement', sa.Text(), nullable=True), sa.Column('contribution_enables', sa.Text(), nullable=True), sa.Column('fundraising_target', sa.Numeric(precision=14, scale=2), nullable=True), sa.Column('start_date', sa.Date(), nullable=True), sa.Column('end_date', sa.Date(), nullable=True), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('receipt_configuration', sa.Text(), nullable=True), sa.Column('created_by_id', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['cause_category_id'], ['cause_categories.id'], ), sa.ForeignKeyConstraint(['created_by_id'], ['users.id'], ), sa.ForeignKeyConstraint(['partner_id'], ['partners.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('donation_causes', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_donation_causes_cause_code'), ['cause_code'], unique=True) batch_op.create_index(batch_op.f('ix_donation_causes_status'), ['status'], unique=False) op.create_table('locations', sa.Column('id', sa.Integer(), nullable=False), sa.Column('city_id', sa.Integer(), nullable=False), sa.Column('name', sa.String(length=150), nullable=False), sa.Column('address', sa.String(length=500), nullable=True), sa.Column('latitude', sa.Numeric(precision=10, scale=7), nullable=True), sa.Column('longitude', sa.Numeric(precision=10, scale=7), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.ForeignKeyConstraint(['city_id'], ['cities.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('role_permissions', sa.Column('role_id', sa.Integer(), nullable=False), sa.Column('permission_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['permission_id'], ['permissions.id'], ), sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ), sa.PrimaryKeyConstraint('role_id', 'permission_id') ) op.create_table('user_roles', sa.Column('user_id', sa.Integer(), nullable=False), sa.Column('role_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ), sa.ForeignKeyConstraint(['user_id'], ['users.id'], ), sa.PrimaryKeyConstraint('user_id', 'role_id') ) op.create_table('activities', sa.Column('id', sa.Integer(), nullable=False), sa.Column('activity_code', sa.String(length=30), nullable=False), sa.Column('title', sa.String(length=255), nullable=False), sa.Column('short_impact_statement', sa.String(length=500), nullable=True), sa.Column('description', sa.Text(), nullable=True), sa.Column('what_you_will_do', sa.Text(), nullable=True), sa.Column('what_to_carry', sa.Text(), nullable=True), sa.Column('eligibility_requirements', sa.Text(), nullable=True), sa.Column('safety_instructions', sa.Text(), nullable=True), sa.Column('cause_category_id', sa.Integer(), nullable=False), sa.Column('activity_type_id', sa.Integer(), nullable=True), sa.Column('partner_id', sa.Integer(), nullable=True), sa.Column('city_id', sa.Integer(), nullable=False), sa.Column('location_id', sa.Integer(), nullable=True), sa.Column('meeting_point', sa.String(length=500), nullable=True), sa.Column('start_datetime', sa.DateTime(), nullable=False), sa.Column('end_datetime', sa.DateTime(), nullable=False), sa.Column('registration_open_at', sa.DateTime(), nullable=True), sa.Column('registration_close_at', sa.DateTime(), nullable=True), sa.Column('capacity', sa.Integer(), nullable=False), sa.Column('manager_approval_required', sa.Boolean(), nullable=False), sa.Column('working_hours_activity', sa.Boolean(), nullable=False), sa.Column('cancellation_cutoff_hours', sa.Integer(), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('published_at', sa.DateTime(), nullable=True), sa.Column('completed_at', sa.DateTime(), nullable=True), sa.Column('created_by_id', sa.Integer(), nullable=False), sa.Column('approved_by_id', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_type_id'], ['activity_types.id'], ), sa.ForeignKeyConstraint(['approved_by_id'], ['users.id'], ), sa.ForeignKeyConstraint(['cause_category_id'], ['cause_categories.id'], ), sa.ForeignKeyConstraint(['city_id'], ['cities.id'], ), sa.ForeignKeyConstraint(['created_by_id'], ['users.id'], ), sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ), sa.ForeignKeyConstraint(['partner_id'], ['partners.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('activities', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_activities_activity_code'), ['activity_code'], unique=True) batch_op.create_index(batch_op.f('ix_activities_status'), ['status'], unique=False) op.create_table('donation_cause_images', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_cause_id', sa.Integer(), nullable=False), sa.Column('file_path', sa.String(length=500), nullable=False), sa.Column('alt_text', sa.String(length=255), nullable=True), sa.Column('sort_order', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_cause_id'], ['donation_causes.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('donation_suggested_amounts', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_cause_id', sa.Integer(), nullable=False), sa.Column('amount', sa.Numeric(precision=14, scale=2), nullable=False), sa.Column('impact_text', sa.String(length=500), nullable=True), sa.Column('sort_order', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_cause_id'], ['donation_causes.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('employees', sa.Column('id', sa.Integer(), nullable=False), sa.Column('user_id', sa.Integer(), nullable=False), sa.Column('employee_code', sa.String(length=50), nullable=False), sa.Column('full_name', sa.String(length=255), nullable=False), sa.Column('official_email', sa.String(length=255), nullable=False), sa.Column('mobile', sa.String(length=30), nullable=True), sa.Column('department_id', sa.Integer(), nullable=True), sa.Column('designation_id', sa.Integer(), nullable=True), sa.Column('business_unit_id', sa.Integer(), nullable=True), sa.Column('city_id', sa.Integer(), nullable=True), sa.Column('location_id', sa.Integer(), nullable=True), sa.Column('manager_id', sa.Integer(), nullable=True), sa.Column('hrms_source_id', sa.String(length=100), nullable=True), sa.Column('eligible_for_portal', sa.Boolean(), nullable=False), sa.Column('first_login_completed', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('is_active', sa.Boolean(), nullable=False), sa.ForeignKeyConstraint(['business_unit_id'], ['business_units.id'], ), sa.ForeignKeyConstraint(['city_id'], ['cities.id'], ), sa.ForeignKeyConstraint(['department_id'], ['departments.id'], ), sa.ForeignKeyConstraint(['designation_id'], ['designations.id'], ), sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ), sa.ForeignKeyConstraint(['manager_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['user_id'], ['users.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('user_id') ) with op.batch_alter_table('employees', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_employees_employee_code'), ['employee_code'], unique=True) batch_op.create_index(batch_op.f('ix_employees_hrms_source_id'), ['hrms_source_id'], unique=False) batch_op.create_index(batch_op.f('ix_employees_official_email'), ['official_email'], unique=True) op.create_table('activity_images', sa.Column('id', sa.Integer(), nullable=False), sa.Column('activity_id', sa.Integer(), nullable=False), sa.Column('file_path', sa.String(length=500), nullable=False), sa.Column('alt_text', sa.String(length=255), nullable=True), sa.Column('sort_order', sa.Integer(), nullable=False), sa.Column('is_primary', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_id'], ['activities.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('activity_registrations', sa.Column('id', sa.Integer(), nullable=False), sa.Column('participation_id', sa.String(length=30), nullable=False), sa.Column('activity_id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('approval_status', sa.String(length=30), nullable=False), sa.Column('mobile', sa.String(length=30), nullable=True), sa.Column('emergency_contact_name', sa.String(length=150), nullable=True), sa.Column('emergency_contact_phone', sa.String(length=30), nullable=True), sa.Column('tshirt_size', sa.String(length=20), nullable=True), sa.Column('dietary_requirements', sa.String(length=500), nullable=True), sa.Column('accessibility_requirements', sa.String(length=500), nullable=True), sa.Column('participation_guidelines_accepted', sa.Boolean(), nullable=False), sa.Column('consent_accepted', sa.Boolean(), nullable=False), sa.Column('cancelled_at', sa.DateTime(), nullable=True), sa.Column('cancellation_reason', sa.String(length=500), nullable=True), sa.Column('volunteer_hours', sa.Numeric(precision=6, scale=2), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_id'], ['activities.id'], ), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('activity_id', 'employee_id', name='uq_activity_employee_registration') ) with op.batch_alter_table('activity_registrations', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_activity_registrations_activity_id'), ['activity_id'], unique=False) batch_op.create_index(batch_op.f('ix_activity_registrations_approval_status'), ['approval_status'], unique=False) batch_op.create_index(batch_op.f('ix_activity_registrations_employee_id'), ['employee_id'], unique=False) batch_op.create_index(batch_op.f('ix_activity_registrations_participation_id'), ['participation_id'], unique=True) batch_op.create_index(batch_op.f('ix_activity_registrations_status'), ['status'], unique=False) op.create_table('donations', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_no', sa.String(length=30), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('donation_cause_id', sa.Integer(), nullable=False), sa.Column('amount', sa.Numeric(precision=14, scale=2), nullable=False), sa.Column('currency', sa.String(length=3), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('initiated_at', sa.DateTime(), nullable=False), sa.Column('completed_at', sa.DateTime(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_cause_id'], ['donation_causes.id'], ), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('donations', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_donations_donation_cause_id'), ['donation_cause_id'], unique=False) batch_op.create_index(batch_op.f('ix_donations_donation_no'), ['donation_no'], unique=True) batch_op.create_index(batch_op.f('ix_donations_employee_id'), ['employee_id'], unique=False) batch_op.create_index(batch_op.f('ix_donations_status'), ['status'], unique=False) op.create_table('employee_badges', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('badge_id', sa.Integer(), nullable=False), sa.Column('awarded_at', sa.DateTime(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['badge_id'], ['badges.id'], ), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('employee_id', 'badge_id', name='uq_employee_badge') ) op.create_table('employee_cause_preferences', sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('cause_category_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['cause_category_id'], ['cause_categories.id'], ), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('employee_id', 'cause_category_id') ) op.create_table('employee_notification_preferences', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('channel', sa.String(length=30), nullable=False), sa.Column('notification_type', sa.String(length=50), nullable=False), sa.Column('enabled', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('employee_id', 'channel', 'notification_type', name='uq_emp_notification_pref') ) op.create_table('employee_preferences', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('accessibility_support', sa.Text(), nullable=True), sa.Column('preferred_city_id', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['preferred_city_id'], ['cities.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('employee_id') ) op.create_table('heart_transactions', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('rule_id', sa.Integer(), nullable=True), sa.Column('source_type', sa.String(length=50), nullable=False), sa.Column('source_id', sa.Integer(), nullable=False), sa.Column('transaction_type', sa.String(length=20), nullable=False), sa.Column('hearts', sa.Integer(), nullable=False), sa.Column('description', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['rule_id'], ['heart_rules.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('employee_id', 'source_type', 'source_id', 'rule_id', name='uq_heart_source_rule') ) with op.batch_alter_table('heart_transactions', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_heart_transactions_employee_id'), ['employee_id'], unique=False) op.create_table('impact_metric_values', sa.Column('id', sa.Integer(), nullable=False), sa.Column('impact_metric_id', sa.Integer(), nullable=False), sa.Column('activity_id', sa.Integer(), nullable=True), sa.Column('donation_cause_id', sa.Integer(), nullable=True), sa.Column('numeric_value', sa.Numeric(precision=18, scale=4), nullable=False), sa.Column('source', sa.String(length=500), nullable=False), sa.Column('data_owner', sa.String(length=255), nullable=True), sa.Column('verification_status', sa.String(length=30), nullable=False), sa.Column('last_updated_at', sa.DateTime(), nullable=True), sa.Column('approved_by_user_id', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_id'], ['activities.id'], ), sa.ForeignKeyConstraint(['approved_by_user_id'], ['users.id'], ), sa.ForeignKeyConstraint(['donation_cause_id'], ['donation_causes.id'], ), sa.ForeignKeyConstraint(['impact_metric_id'], ['impact_metrics.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('impact_stories', sa.Column('id', sa.Integer(), nullable=False), sa.Column('title', sa.String(length=255), nullable=False), sa.Column('story', sa.Text(), nullable=False), sa.Column('image_path', sa.String(length=500), nullable=True), sa.Column('activity_id', sa.Integer(), nullable=True), sa.Column('donation_cause_id', sa.Integer(), nullable=True), sa.Column('is_published', sa.Boolean(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_id'], ['activities.id'], ), sa.ForeignKeyConstraint(['donation_cause_id'], ['donation_causes.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('notifications', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('event_code', sa.String(length=100), nullable=False), sa.Column('title', sa.String(length=255), nullable=False), sa.Column('message', sa.Text(), nullable=False), sa.Column('link_url', sa.String(length=500), nullable=True), sa.Column('read_at', sa.DateTime(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('notifications', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_notifications_employee_id'), ['employee_id'], unique=False) op.create_table('saved_activities', sa.Column('id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('activity_id', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['activity_id'], ['activities.id'], ), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('employee_id', 'activity_id', name='uq_saved_activity') ) op.create_table('activity_attendance', sa.Column('id', sa.Integer(), nullable=False), sa.Column('registration_id', sa.Integer(), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('check_in_at', sa.DateTime(), nullable=True), sa.Column('check_out_at', sa.DateTime(), nullable=True), sa.Column('volunteer_hours', sa.Numeric(precision=6, scale=2), nullable=False), sa.Column('confirmed_by_user_id', sa.Integer(), nullable=True), sa.Column('confirmed_at', sa.DateTime(), nullable=True), sa.Column('remarks', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['confirmed_by_user_id'], ['users.id'], ), sa.ForeignKeyConstraint(['registration_id'], ['activity_registrations.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('registration_id') ) op.create_table('activity_feedback', sa.Column('id', sa.Integer(), nullable=False), sa.Column('registration_id', sa.Integer(), nullable=False), sa.Column('rating', sa.Integer(), nullable=True), sa.Column('feedback', sa.Text(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['registration_id'], ['activity_registrations.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('registration_id') ) op.create_table('donation_receipts', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_id', sa.Integer(), nullable=False), sa.Column('receipt_number', sa.String(length=100), nullable=False), sa.Column('receipt_date', sa.Date(), nullable=False), sa.Column('receipt_file', sa.String(length=500), nullable=True), sa.Column('tax_certificate_file', sa.String(length=500), nullable=True), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_id'], ['donations.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('donation_id'), sa.UniqueConstraint('receipt_number') ) op.create_table('donation_refunds', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_id', sa.Integer(), nullable=False), sa.Column('amount', sa.Numeric(precision=14, scale=2), nullable=False), sa.Column('gateway_refund_id', sa.String(length=150), nullable=True), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('reason', sa.String(length=500), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_id'], ['donations.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('donation_transactions', sa.Column('id', sa.Integer(), nullable=False), sa.Column('donation_id', sa.Integer(), nullable=False), sa.Column('gateway', sa.String(length=50), nullable=False), sa.Column('gateway_order_id', sa.String(length=150), nullable=True), sa.Column('gateway_payment_id', sa.String(length=150), nullable=True), sa.Column('gateway_reference', sa.String(length=150), nullable=True), sa.Column('amount', sa.Numeric(precision=14, scale=2), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('gateway_response', sa.JSON(), nullable=True), sa.Column('initiated_at', sa.DateTime(), nullable=False), sa.Column('completed_at', sa.DateTime(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['donation_id'], ['donations.id'], ), sa.PrimaryKeyConstraint('id') ) with op.batch_alter_table('donation_transactions', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_donation_transactions_donation_id'), ['donation_id'], unique=False) batch_op.create_index(batch_op.f('ix_donation_transactions_gateway_order_id'), ['gateway_order_id'], unique=False) batch_op.create_index(batch_op.f('ix_donation_transactions_gateway_payment_id'), ['gateway_payment_id'], unique=False) batch_op.create_index(batch_op.f('ix_donation_transactions_gateway_reference'), ['gateway_reference'], unique=False) op.create_table('manager_approval_requests', sa.Column('id', sa.Integer(), nullable=False), sa.Column('registration_id', sa.Integer(), nullable=False), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('manager_id', sa.Integer(), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('note_to_manager', sa.Text(), nullable=True), sa.Column('manager_response', sa.Text(), nullable=True), sa.Column('requested_at', sa.DateTime(), nullable=False), sa.Column('decided_at', sa.DateTime(), nullable=True), sa.Column('expires_at', sa.DateTime(), nullable=True), sa.Column('last_nudged_at', sa.DateTime(), nullable=True), sa.Column('nudge_count', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['manager_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['registration_id'], ['activity_registrations.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('registration_id') ) with op.batch_alter_table('manager_approval_requests', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_manager_approval_requests_status'), ['status'], unique=False) op.create_table('notification_delivery_logs', sa.Column('id', sa.Integer(), nullable=False), sa.Column('notification_id', sa.Integer(), nullable=True), sa.Column('employee_id', sa.Integer(), nullable=False), sa.Column('channel', sa.String(length=30), nullable=False), sa.Column('recipient', sa.String(length=255), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('provider_message_id', sa.String(length=255), nullable=True), sa.Column('error_message', sa.Text(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['employee_id'], ['employees.id'], ), sa.ForeignKeyConstraint(['notification_id'], ['notifications.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('manager_approval_history', sa.Column('id', sa.Integer(), nullable=False), sa.Column('approval_request_id', sa.Integer(), nullable=False), sa.Column('action', sa.String(length=50), nullable=False), sa.Column('from_status', sa.String(length=30), nullable=True), sa.Column('to_status', sa.String(length=30), nullable=False), sa.Column('acted_by_user_id', sa.Integer(), nullable=False), sa.Column('remarks', sa.Text(), nullable=True), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.ForeignKeyConstraint(['acted_by_user_id'], ['users.id'], ), sa.ForeignKeyConstraint(['approval_request_id'], ['manager_approval_requests.id'], ), sa.PrimaryKeyConstraint('id') ) # ### end Alembic commands ### def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_table('manager_approval_history') op.drop_table('notification_delivery_logs') with op.batch_alter_table('manager_approval_requests', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_manager_approval_requests_status')) op.drop_table('manager_approval_requests') with op.batch_alter_table('donation_transactions', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_donation_transactions_gateway_reference')) batch_op.drop_index(batch_op.f('ix_donation_transactions_gateway_payment_id')) batch_op.drop_index(batch_op.f('ix_donation_transactions_gateway_order_id')) batch_op.drop_index(batch_op.f('ix_donation_transactions_donation_id')) op.drop_table('donation_transactions') op.drop_table('donation_refunds') op.drop_table('donation_receipts') op.drop_table('activity_feedback') op.drop_table('activity_attendance') op.drop_table('saved_activities') with op.batch_alter_table('notifications', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_notifications_employee_id')) op.drop_table('notifications') op.drop_table('impact_stories') op.drop_table('impact_metric_values') with op.batch_alter_table('heart_transactions', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_heart_transactions_employee_id')) op.drop_table('heart_transactions') op.drop_table('employee_preferences') op.drop_table('employee_notification_preferences') op.drop_table('employee_cause_preferences') op.drop_table('employee_badges') with op.batch_alter_table('donations', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_donations_status')) batch_op.drop_index(batch_op.f('ix_donations_employee_id')) batch_op.drop_index(batch_op.f('ix_donations_donation_no')) batch_op.drop_index(batch_op.f('ix_donations_donation_cause_id')) op.drop_table('donations') with op.batch_alter_table('activity_registrations', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_activity_registrations_status')) batch_op.drop_index(batch_op.f('ix_activity_registrations_participation_id')) batch_op.drop_index(batch_op.f('ix_activity_registrations_employee_id')) batch_op.drop_index(batch_op.f('ix_activity_registrations_approval_status')) batch_op.drop_index(batch_op.f('ix_activity_registrations_activity_id')) op.drop_table('activity_registrations') op.drop_table('activity_images') with op.batch_alter_table('employees', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_employees_official_email')) batch_op.drop_index(batch_op.f('ix_employees_hrms_source_id')) batch_op.drop_index(batch_op.f('ix_employees_employee_code')) op.drop_table('employees') op.drop_table('donation_suggested_amounts') op.drop_table('donation_cause_images') with op.batch_alter_table('activities', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_activities_status')) batch_op.drop_index(batch_op.f('ix_activities_activity_code')) op.drop_table('activities') op.drop_table('user_roles') op.drop_table('role_permissions') op.drop_table('locations') with op.batch_alter_table('donation_causes', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_donation_causes_status')) batch_op.drop_index(batch_op.f('ix_donation_causes_cause_code')) op.drop_table('donation_causes') with op.batch_alter_table('audit_logs', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_audit_logs_entity_type')) batch_op.drop_index(batch_op.f('ix_audit_logs_entity_id')) batch_op.drop_index(batch_op.f('ix_audit_logs_action')) op.drop_table('audit_logs') with op.batch_alter_table('users', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_users_sso_subject')) batch_op.drop_index(batch_op.f('ix_users_official_email')) op.drop_table('users') op.drop_table('system_settings') op.drop_table('roles') op.drop_table('permissions') op.drop_table('partners') with op.batch_alter_table('notification_templates', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_notification_templates_event_code')) op.drop_table('notification_templates') with op.batch_alter_table('integration_logs', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_integration_logs_integration')) op.drop_table('integration_logs') op.drop_table('impact_metrics') op.drop_table('heart_rules') op.drop_table('designations') op.drop_table('departments') op.drop_table('cities') op.drop_table('cause_categories') op.drop_table('business_units') op.drop_table('badges') op.drop_table('activity_types') # ### end Alembic commands ###