"""phase 3 model profiles

Revision ID: f956db868bc2
Revises: fb27a9b84b1b
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "f956db868bc2"
down_revision: str | Sequence[str] | None = "fb27a9b84b1b"
branch_labels = None
depends_on = None

def upgrade() -> None:
    permissions = sa.table(
        "permissions", sa.column("key", sa.String), sa.column("description", sa.String)
    )
    op.bulk_insert(permissions, [{
        "key": "settings.read", "description": "Read workspace settings"
    }])
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('ai_model_profiles',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('created_by_user_id', sa.Uuid(), nullable=False),
    sa.Column('profile_key', sa.String(length=80), nullable=False),
    sa.Column('display_name', sa.String(length=160), nullable=False),
    sa.Column('provider', sa.String(length=40), nullable=False),
    sa.Column('model_id', sa.String(length=160), nullable=False),
    sa.Column('enabled', sa.Boolean(), nullable=False),
    sa.Column('capabilities', sa.JSON().with_variant(postgresql.JSONB(astext_type=sa.Text()), 'postgresql'), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.CheckConstraint("provider IN ('openai')", name='ck_model_profile_provider'),
    sa.ForeignKeyConstraint(['workspace_id', 'created_by_user_id'], ['workspace_memberships.workspace_id', 'workspace_memberships.user_id'], name='fk_model_profile_creator'),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('workspace_id', 'id', name='uq_model_profile_workspace_id'),
    sa.UniqueConstraint('workspace_id', 'profile_key', name='uq_model_profile_workspace_key')
    )
    op.create_index(op.f('ix_ai_model_profiles_workspace_id'), 'ai_model_profiles', ['workspace_id'], unique=False)
    roles = sa.table(
        "roles", sa.column("id", sa.Uuid), sa.column("workspace_id", sa.Uuid),
        sa.column("name", sa.String), sa.column("is_system", sa.Boolean),
    )
    role_permissions = sa.table(
        "role_permissions", sa.column("workspace_id", sa.Uuid),
        sa.column("role_id", sa.Uuid), sa.column("permission_key", sa.String),
    )
    op.get_bind().execute(role_permissions.insert().from_select(
        ["workspace_id", "role_id", "permission_key"],
        sa.select(roles.c.workspace_id, roles.c.id, sa.literal("settings.read")).where(
            roles.c.is_system.is_(True),
            roles.c.name.in_([
                "Owner", "Administrator", "Assistant Manager", "Human Assistant",
                "Viewer", "Developer",
            ]),
        ),
    ))
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_ai_model_profiles_workspace_id'), table_name='ai_model_profiles')
    op.drop_table('ai_model_profiles')
    role_permissions = sa.table(
        "role_permissions", sa.column("permission_key", sa.String)
    )
    permissions = sa.table("permissions", sa.column("key", sa.String))
    connection = op.get_bind()
    connection.execute(role_permissions.delete().where(
        role_permissions.c.permission_key == "settings.read"
    ))
    connection.execute(permissions.delete().where(permissions.c.key == "settings.read"))
    # ### end Alembic commands ###