"""add offboarding processes and asset transfer requests

Revision ID: 023
Revises: 022
Create Date: 2026-04-17
"""
import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for


revision = '023'
down_revision = '022'


def upgrade() -> None:
    ID = id_type_for("identity_persons")
    KW = table_kw_for("identity_persons")

    op.create_table(
        'offboarding_processes',
        sa.Column('id', ID, primary_key=True),
        sa.Column('person_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=False, unique=True),
        sa.Column('leaver_date', sa.Date, nullable=False),
        sa.Column('status', sa.String(32), nullable=False,
                  server_default=sa.text("'scanning'")),
        sa.Column('scanned_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('ready_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('sso_suspended_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('summary', sa.JSON, nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        **KW,
    )
    op.create_index(
        'ix_offboarding_processes_status',
        'offboarding_processes', ['status'],
    )

    op.create_table(
        'offboarding_blockers',
        sa.Column('id', ID, primary_key=True),
        sa.Column('process_id', ID,
                  sa.ForeignKey('offboarding_processes.id', ondelete='CASCADE'),
                  nullable=False),
        sa.Column('resource_type', sa.String(32), nullable=False),
        sa.Column('resource_id', ID, nullable=False),
        sa.Column('label', sa.String(255), nullable=False),
        sa.Column('software_id', ID, nullable=True),
        sa.Column('resolution', sa.String(32), nullable=False,
                  server_default=sa.text("'pending'")),
        sa.Column('resolution_ref', sa.String(255), nullable=True),
        sa.Column('provisioning_task_id', ID,
                  sa.ForeignKey('provisioning_tasks.id'), nullable=True),
        sa.Column('transfer_request_id', ID, nullable=True),
        sa.Column('notes', sa.Text, nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        **KW,
    )
    op.create_index(
        'ix_offboarding_blockers_process_resolution',
        'offboarding_blockers', ['process_id', 'resolution'],
    )

    op.create_table(
        'asset_transfer_requests',
        sa.Column('id', ID, primary_key=True),
        sa.Column('initiator_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=False),
        sa.Column('recipient_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=False),
        sa.Column('asset_type', sa.String(32), nullable=False),
        sa.Column('asset_ref_id', ID, nullable=False),
        sa.Column('asset_label', sa.String(255), nullable=False),
        sa.Column('status', sa.String(32), nullable=False,
                  server_default=sa.text("'pending'")),
        sa.Column('reason', sa.Text, nullable=True),
        sa.Column('response_note', sa.Text, nullable=True),
        sa.Column('responded_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('offboarding_process_id', ID,
                  sa.ForeignKey('offboarding_processes.id'), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        **KW,
    )
    op.create_index(
        'ix_asset_transfer_initiator_status',
        'asset_transfer_requests', ['initiator_id', 'status'],
    )
    op.create_index(
        'ix_asset_transfer_recipient_status',
        'asset_transfer_requests', ['recipient_id', 'status'],
    )


def downgrade() -> None:
    op.drop_index('ix_asset_transfer_recipient_status', table_name='asset_transfer_requests')
    op.drop_index('ix_asset_transfer_initiator_status', table_name='asset_transfer_requests')
    op.drop_table('asset_transfer_requests')
    op.drop_index('ix_offboarding_blockers_process_resolution', table_name='offboarding_blockers')
    op.drop_table('offboarding_blockers')
    op.drop_index('ix_offboarding_processes_status', table_name='offboarding_processes')
    op.drop_table('offboarding_processes')