"""Work tracker Phase 4B internal target dates

Revision ID: 20260506_work_tracker_phase_4b
Revises: 20260505_renewal_before_expiry_due_dates
Create Date: 2026-05-03
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "20260506_work_tracker_phase_4b"
down_revision = "20260505_renewal_before_expiry_due_dates"
branch_labels = None
depends_on = None


def _table_columns(table_name: str) -> set[str]:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    try:
        return {column["name"] for column in inspector.get_columns(table_name)}
    except Exception:
        return set()


def _index_names(table_name: str) -> set[str]:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    try:
        return {index["name"] for index in inspector.get_indexes(table_name) if index.get("name")}
    except Exception:
        return set()


def upgrade() -> None:
    columns = _table_columns("client_service_task_instances")
    if "internal_target_date" not in columns:
        op.add_column("client_service_task_instances", sa.Column("internal_target_date", sa.Date(), nullable=True))

    indexes = _index_names("client_service_task_instances")
    if "ix_client_service_task_instances_internal_target_date" not in indexes:
        op.create_index(
            "ix_client_service_task_instances_internal_target_date",
            "client_service_task_instances",
            ["internal_target_date"],
            unique=False,
        )


def downgrade() -> None:
    indexes = _index_names("client_service_task_instances")
    if "ix_client_service_task_instances_internal_target_date" in indexes:
        op.drop_index("ix_client_service_task_instances_internal_target_date", table_name="client_service_task_instances")

    columns = _table_columns("client_service_task_instances")
    if "internal_target_date" in columns:
        op.drop_column("client_service_task_instances", "internal_target_date")
