"""add unique constraint to admin_settings.user

Revision ID: 83647ca77970
Revises: 73647ca7796f
Create Date: 2026-04-23 12:00:00.000000
"""

import os
import sys
from typing import Sequence, Union

import sqlalchemy as sa

from alembic import op

# Add alembic folder to path for utils import
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from utils import (
    batch_alter_table,
    get_db_schema,
    get_qualified_table_name,
    get_tables_prefix,
    migration_logger as logger,
    timed_step,
    table_exists as _table_exists,
)

# revision identifiers, used by Alembic.
revision: str = "83647ca77970"
down_revision: Union[str, Sequence[str], None] = "73647ca7796f"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


BASE_CONSTRAINT_NAME = "uq_admin_settings_user"


def get_constraint_name() -> str:
    prefix = get_tables_prefix()
    return f"{prefix}{BASE_CONSTRAINT_NAME}" if prefix else BASE_CONSTRAINT_NAME


def quote_identifier(identifier: str) -> str:
    bind = op.get_bind()
    # `user` is a reserved/special identifier on some databases (notably SQL Server),
    # so hardcoding quoting syntax here would break portability across dialects.
    return bind.dialect.identifier_preparer.quote(identifier)


def delete_duplicate_admin_settings_rows() -> None:
    bind = op.get_bind()
    table_name = get_qualified_table_name("admin_settings")
    quoted_user = quote_identifier("user")
    result = bind.execute(
        sa.text(f"""
        DELETE FROM {table_name}
        WHERE id NOT IN (
            SELECT min_id FROM (
                SELECT MIN(id) as min_id
                FROM {table_name}
                GROUP BY {quoted_user}
            ) as preserved
        )
    """)
    )

    if result.rowcount:
        logger.warning("Deleted %s duplicate admin_settings rows before adding unique constraint", result.rowcount)


def upgrade() -> None:
    """Add a unique constraint on admin_settings.user."""
    constraint_name = get_constraint_name()
    with timed_step("Add admin_settings.user unique constraint"):
        if not _table_exists("admin_settings"):
            logger.warning("admin_settings table not found; skipping unique constraint migration")
            return

        delete_duplicate_admin_settings_rows()

        with batch_alter_table("admin_settings") as batch_op:
            batch_op.create_unique_constraint(constraint_name, ["user"])


def downgrade() -> None:
    """Remove the unique constraint on admin_settings.user."""
    constraint_name = get_constraint_name()
    with timed_step("Drop admin_settings.user unique constraint"):
        if not _table_exists("admin_settings"):
            logger.warning("admin_settings table not found; skipping unique constraint downgrade")
            return

        with batch_alter_table("admin_settings") as batch_op:
            batch_op.drop_constraint(constraint_name, type_="unique")
