"""add readable_id to connection table

Revision ID: 41e6efddaa18
Revises: ed8ef1d6266f
Create Date: 2025-07-14 12:49:48.093739

"""
import random
import re
import string
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '41e6efddaa18'
down_revision = 'ed8ef1d6266f'
branch_labels = None
depends_on = None


def generate_readable_id(name: str) -> str:
    """Generate a readable ID from a connection name."""
    # Convert to lowercase and replace spaces with hyphens
    readable_id = name.lower().strip()

    # Replace any character that's not a letter, number, or space with nothing
    readable_id = re.sub(r"[^a-z0-9\s]", "", readable_id)
    # Replace spaces with hyphens
    readable_id = re.sub(r"\s+", "-", readable_id)
    # Ensure no consecutive hyphens
    readable_id = re.sub(r"-+", "-", readable_id)
    # Trim hyphens from start and end
    readable_id = readable_id.strip("-")

    # Add random alphanumeric suffix
    suffix = "".join(random.choices(string.ascii_lowercase + string.digits, k=6))
    readable_id = f"{readable_id}-{suffix}"

    return readable_id


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # Add readable_id column as nullable first
    op.add_column('connection', sa.Column('readable_id', sa.String(), nullable=True))

    # Get database connection to populate existing rows
    connection = op.get_bind()

    # Fetch all existing connections
    result = connection.execute(sa.text("SELECT id, name FROM connection"))

    # Check if result exists and has rows
    if result is not None:
        rows = result.fetchall()
        if rows:
            # Generate readable_ids for existing connections
            for row in rows:
                readable_id = generate_readable_id(row.name)
                # Ensure uniqueness by checking for conflicts
                while True:
                    existing = connection.execute(
                        sa.text("SELECT COUNT(*) FROM connection WHERE readable_id = :readable_id"),
                        {"readable_id": readable_id}
                    ).scalar()
                    if existing == 0:
                        break
                    # Generate a new one if conflict exists
                    readable_id = generate_readable_id(row.name)

                # Update the row with the generated readable_id
                connection.execute(
                    sa.text("UPDATE connection SET readable_id = :readable_id WHERE id = :id"),
                    {"readable_id": readable_id, "id": row.id}
                )

    # Make the column non-nullable after population
    op.alter_column('connection', 'readable_id', nullable=False)

    # Add unique constraint
    op.create_unique_constraint('uq_connection_readable_id', 'connection', ['readable_id'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('uq_connection_readable_id', 'connection', type_='unique')
    op.drop_column('connection', 'readable_id')
    # ### end Alembic commands ###
