"""Update skills table

Revision ID: c1acf65d4731
Revises: a8fff9df0a02
Create Date: 2024-06-13 16:09:19.067502

"""
from alembic import op
from app.core.security import get_password_hash
import sqlalchemy as sa
from sqlalchemy.sql import table, column, select
from sqlalchemy import String, Integer, insert

# revision identifiers, used by Alembic.
revision = 'c1acf65d4731'
down_revision = 'a8fff9df0a02'
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # Add tool_definition column
    op.add_column('skill', sa.Column('tool_definition', sa.JSON(), nullable=True))

    # Import the settings to get the FIRST_SUPERUSER email
    from app.core.config import settings

    # Use raw SQL to find the user ID for the superuser
    connection = op.get_bind()
    user_table = table('user',
                       column('id', Integer),
                       column('email', String),
                       column('hashed_password', String),
                       column('is_superuser', sa.Boolean),
                       column('is_active', sa.Boolean))
    superuser_email = settings.FIRST_SUPERUSER

    # Correct the query to properly select the user ID
    superuser_id = connection.execute(
        select(user_table.c.id).where(user_table.c.email == superuser_email)
    ).scalar()

    if superuser_id is None:
        # Insert the superuser if it does not exist
        connection.execute(
            insert(user_table).values(
                email=superuser_email,
                hashed_password=get_password_hash(settings.FIRST_SUPERUSER_PASSWORD),
                is_superuser=True,
                is_active=True
            )
        )
        # Fetch the superuser ID after insertion
        superuser_id = connection.execute(
            select(user_table.c.id).where(user_table.c.email == superuser_email)
        ).scalar()

    # Add owner_id column with the superuser ID as default for existing rows
    op.add_column('skill', sa.Column('owner_id', sa.Integer(), nullable=False, server_default=str(superuser_id)))
    op.alter_column('skill', 'description', existing_type=sa.VARCHAR(), nullable=False)
    op.create_foreign_key(None, 'skill', 'user', ['owner_id'], ['id'])

    # Remove the server default after setting the initial values
    op.alter_column('skill', 'owner_id', server_default=None)
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'skill', type_='foreignkey')
    op.alter_column('skill', 'description', existing_type=sa.VARCHAR(), nullable=True)
    op.drop_column('skill', 'owner_id')
    op.drop_column('skill', 'tool_definition')
    # ### end Alembic commands ###