"""add api_key_providers and api_key_assignments tables

Revision ID: 020
Revises: 019
Create Date: 2026-04-16

New domain: AI API Key management.
"""
import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for


revision = '020'
down_revision = '019'


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

    op.create_table(
        'api_key_providers',
        sa.Column('id', ID, primary_key=True),
        sa.Column('name', sa.String(128), nullable=False, unique=True),
        sa.Column('description', sa.Text, nullable=True),
        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_table(
        'api_key_assignments',
        sa.Column('id', ID, primary_key=True),
        sa.Column('provider_id', ID, sa.ForeignKey('api_key_providers.id'), nullable=False),
        sa.Column('assigned_to', ID, sa.ForeignKey('identity_persons.id'), nullable=False),
        sa.Column('status', sa.String(32), nullable=False, server_default=sa.text("'pending_approval'")),
        sa.Column('approval_request_id', ID, sa.ForeignKey('approval_requests.id'), nullable=True),
        sa.Column('request_number', sa.String(64), nullable=False, unique=True),
        sa.Column('project_name', sa.String(256), nullable=False),
        sa.Column('reason', sa.Text, nullable=False),
        sa.Column('monthly_budget_usd', sa.Numeric(10, 2), nullable=False),
        sa.Column('expected_usage', sa.Text, nullable=True),
        sa.Column('key_value', sa.Text, nullable=True),
        sa.Column('valid_from', sa.Date, nullable=True),
        sa.Column('valid_until', sa.Date, nullable=True),
        sa.Column('extended_count', sa.Integer, nullable=False, server_default=sa.text('0')),
        sa.Column('detail', 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_api_key_assignments_assigned_to', 'api_key_assignments', ['assigned_to'])
    op.create_index('ix_api_key_assignments_provider_id', 'api_key_assignments', ['provider_id'])
    op.create_index('ix_api_key_assignments_status', 'api_key_assignments', ['status'])


def downgrade() -> None:
    op.drop_table('api_key_assignments')
    op.drop_table('api_key_providers')