"""add name and size to tool_files

Revision ID: bbadea11becb
Revises: 33f5fac87f29
Create Date: 2024-10-10 05:16:14.764268

"""

import sqlalchemy as sa
from alembic import op, context

# revision identifiers, used by Alembic.
revision = "bbadea11becb"
down_revision = "d8e744d88ed6"
branch_labels = None
depends_on = None


def _is_pg(conn):
    return conn.dialect.name == "postgresql"


def upgrade():
    def _has_name_or_size_column() -> bool:
        # We cannot access the database in offline mode, so assume
        # the "name" and "size" columns do not exist.
        if context.is_offline_mode():
            # Log a warning message to inform the user that the database schema cannot be inspected
            # in offline mode, and the generated SQL may not accurately reflect the actual execution.
            op.execute(
                "-- Executing in offline mode, assuming the name and size columns do not exist.\n"
                "-- The generated SQL may differ from what will actually be executed.\n"
                "-- Please review the migration script carefully!"
            )

            return False
        # Use SQLAlchemy inspector to get the columns of the 'tool_files' table
        inspector = sa.inspect(conn)
        columns = [col["name"] for col in inspector.get_columns("tool_files")]

        # If 'name' or 'size' columns already exist, exit the upgrade function
        if "name" in columns or "size" in columns:
            return True
        return False

    # ### commands auto generated by Alembic - please adjust! ###
    # Get the database connection
    conn = op.get_bind()

    if _has_name_or_size_column():
        return

    if _is_pg(conn):
        # PostgreSQL: Keep original syntax
        with op.batch_alter_table("tool_files", schema=None) as batch_op:
            batch_op.add_column(sa.Column("name", sa.String(), nullable=True))
            batch_op.add_column(sa.Column("size", sa.Integer(), nullable=True))
        op.execute("UPDATE tool_files SET name = '' WHERE name IS NULL")
        op.execute("UPDATE tool_files SET size = -1 WHERE size IS NULL")
        with op.batch_alter_table("tool_files", schema=None) as batch_op:
            batch_op.alter_column("name", existing_type=sa.String(), nullable=False)
            batch_op.alter_column("size", existing_type=sa.Integer(), nullable=False)
    else:
        # MySQL: Use compatible syntax
        with op.batch_alter_table("tool_files", schema=None) as batch_op:
            batch_op.add_column(sa.Column("name", sa.String(length=255), nullable=True))
            batch_op.add_column(sa.Column("size", sa.Integer(), nullable=True))
        op.execute("UPDATE tool_files SET name = '' WHERE name IS NULL")
        op.execute("UPDATE tool_files SET size = -1 WHERE size IS NULL")
        with op.batch_alter_table("tool_files", schema=None) as batch_op:
            batch_op.alter_column("name", existing_type=sa.String(length=255), nullable=False)
            batch_op.alter_column("size", existing_type=sa.Integer(), nullable=False)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("tool_files", schema=None) as batch_op:
        batch_op.drop_column("size")
        batch_op.drop_column("name")
    # ### end Alembic commands ###
