"""add software_permissions + user_permissions tables

Revision ID: 024
Revises: 023
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 = '024'
down_revision = '023'


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

    op.create_table(
        'software_permissions',
        sa.Column('id', ID, primary_key=True),
        sa.Column('software_id', ID,
                  sa.ForeignKey('asset_software_catalog.id'), nullable=False),
        sa.Column('permission_key', sa.String(128), nullable=False),
        sa.Column('display_name', sa.String(255), nullable=False),
        sa.Column('description', sa.Text, nullable=True),
        sa.Column('requires_approval', sa.Boolean, nullable=False,
                  server_default=sa.text('1')),
        sa.Column('is_active', sa.Boolean, nullable=False,
                  server_default=sa.text('1')),
        sa.Column('sort_order', sa.Integer, nullable=False,
                  server_default=sa.text('0')),
        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_unique_constraint(
        'uq_software_permission_sw_key',
        'software_permissions', ['software_id', 'permission_key'],
    )
    op.create_index(
        'ix_software_permissions_software_active',
        'software_permissions', ['software_id', 'is_active'],
    )

    op.create_table(
        'user_permissions',
        sa.Column('id', ID, primary_key=True),
        sa.Column('user_id', ID,
                  sa.ForeignKey('identity_persons.id'), nullable=False),
        sa.Column('permission_id', ID,
                  sa.ForeignKey('software_permissions.id'), nullable=False),
        sa.Column('status', sa.String(32), nullable=False,
                  server_default=sa.text("'pending'")),
        sa.Column('reason', sa.Text, nullable=True),
        sa.Column('approval_request_id', ID,
                  sa.ForeignKey('approval_requests.id'), nullable=True),
        sa.Column('provisioning_task_id', ID,
                  sa.ForeignKey('provisioning_tasks.id'), nullable=True),
        sa.Column('external_ref', sa.String(255), nullable=True),
        sa.Column('granted_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('revoked_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_user_permissions_user_status',
        'user_permissions', ['user_id', 'status'],
    )
    op.create_index(
        'ix_user_permissions_permission',
        'user_permissions', ['permission_id'],
    )


def downgrade() -> None:
    op.drop_index('ix_user_permissions_permission', table_name='user_permissions')
    op.drop_index('ix_user_permissions_user_status', table_name='user_permissions')
    op.drop_table('user_permissions')
    op.drop_index('ix_software_permissions_software_active', table_name='software_permissions')
    op.drop_constraint('uq_software_permission_sw_key', 'software_permissions', type_='unique')
    op.drop_table('software_permissions')