"""add provisioning framework tables

Revision ID: 021
Revises: 020
Create Date: 2026-04-16
"""
import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for


revision = '021'
down_revision = '020'


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

    op.create_table(
        'provisioning_tasks',
        sa.Column('id', ID, primary_key=True),
        sa.Column('user_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=False),
        sa.Column('software_id', ID,
                  sa.ForeignKey('asset_software_catalog.id'), nullable=True),
        sa.Column('action', sa.String(16), nullable=False),
        sa.Column('target_type', sa.String(32), nullable=False),
        sa.Column('target_id', ID, nullable=True),
        sa.Column('source', sa.String(32), nullable=False),
        sa.Column('execution_mode', sa.String(16), nullable=False),
        sa.Column('status', sa.String(16), nullable=False,
                  server_default=sa.text("'pending'")),
        sa.Column('assignee_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=True),
        sa.Column('external_ref', sa.String(255), nullable=True),
        sa.Column('error_message', sa.Text, nullable=True),
        sa.Column('approval_request_id', ID,
                  sa.ForeignKey('approval_requests.id'), nullable=True),
        sa.Column('context_data', sa.JSON, nullable=True),
        sa.Column('attempt_count', sa.Integer, nullable=False,
                  server_default=sa.text('0')),
        sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('completed_at', sa.DateTime(timezone=True), 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_provisioning_tasks_user_status',
        'provisioning_tasks', ['user_id', 'status'],
    )
    op.create_index(
        'ix_provisioning_tasks_software_status',
        'provisioning_tasks', ['software_id', 'status'],
    )
    op.create_index(
        'ix_provisioning_tasks_assignee_status',
        'provisioning_tasks', ['assignee_id', 'status'],
    )
    op.create_index(
        'ix_provisioning_tasks_source_status',
        'provisioning_tasks', ['source', 'status'],
    )

    op.create_table(
        'software_connectors',
        sa.Column('id', ID, primary_key=True),
        sa.Column('software_id', ID,
                  sa.ForeignKey('asset_software_catalog.id'),
                  nullable=False, unique=True),
        sa.Column('connector_type', sa.String(32), nullable=False),
        sa.Column('config', sa.JSON, nullable=False),
        sa.Column('is_active', sa.Boolean, nullable=False,
                  server_default=sa.text('1')),
        sa.Column('last_success_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('last_error_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('last_error_message', 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.add_column(
        'asset_software_catalog',
        sa.Column('sso_provider', sa.String(32), nullable=True),
    )
    op.add_column(
        'asset_software_catalog',
        sa.Column('offboarding_grace_days', sa.Integer, nullable=False,
                  server_default=sa.text('0')),
    )

    op.add_column(
        'software_license_plans',
        sa.Column('auto_provision_on_onboarding', sa.Boolean, nullable=False,
                  server_default=sa.text('0')),
    )


def downgrade() -> None:
    op.drop_column('software_license_plans', 'auto_provision_on_onboarding')
    op.drop_column('asset_software_catalog', 'offboarding_grace_days')
    op.drop_column('asset_software_catalog', 'sso_provider')
    op.drop_table('software_connectors')
    op.drop_index('ix_provisioning_tasks_source_status', table_name='provisioning_tasks')
    op.drop_index('ix_provisioning_tasks_assignee_status', table_name='provisioning_tasks')
    op.drop_index('ix_provisioning_tasks_software_status', table_name='provisioning_tasks')
    op.drop_index('ix_provisioning_tasks_user_status', table_name='provisioning_tasks')
    op.drop_table('provisioning_tasks')