"""drop 10 redundant single-column FK indexes (R12 S5)

Each of these columns already leads a composite UNIQUE constraint, whose btree covers leftmost-prefix
lookups on that column (and the ON DELETE CASCADE reverse lookups too) — so the extra single-column
`index=True` was pure write-amplification. Dropping them costs nothing on reads and saves an index
write per insert/update. Fast (index-only drop, no table rewrite). Reversible (downgrade recreates).

Redundant index -> the composite that already covers it:
  ix_subscriptions_user_id            <- uq_user_channel (user_id, channel_id)
  ix_video_states_user_id             <- uq_user_video (user_id, video_id)
  ix_search_finds_user_id             <- uq_user_search_video (user_id, video_id)
  ix_explored_channels_user_id        <- uq_user_explored_channel (user_id, channel_id)
  ix_blocked_channels_user_id         <- uq_user_blocked_channel (user_id, channel_id)
  ix_saved_views_user_id              <- uq_user_saved_view (user_id, name)
  ix_playlist_items_playlist_id       <- uq_playlist_video (playlist_id, video_id)
  ix_download_shares_job_id           <- uq_download_share (job_id, to_user_id)
  ix_plex_states_user_id              <- uq_plex_user_item (user_id, item_id)
  ix_plex_playlist_items_playlist_id  <- uq_plex_playlist_item (playlist_id, item_id)
"""

from typing import Sequence, Union

from alembic import op

revision: str = "0062_drop_redundant_idx"
down_revision: Union[str, None] = "0061_dl_job_error_code"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# (index_name, table_name, column)
_REDUNDANT: list[tuple[str, str, str]] = [
    ("ix_subscriptions_user_id", "subscriptions", "user_id"),
    ("ix_video_states_user_id", "video_states", "user_id"),
    ("ix_search_finds_user_id", "search_finds", "user_id"),
    ("ix_explored_channels_user_id", "explored_channels", "user_id"),
    ("ix_blocked_channels_user_id", "blocked_channels", "user_id"),
    ("ix_saved_views_user_id", "saved_views", "user_id"),
    ("ix_playlist_items_playlist_id", "playlist_items", "playlist_id"),
    ("ix_download_shares_job_id", "download_shares", "job_id"),
    ("ix_plex_states_user_id", "plex_states", "user_id"),
    ("ix_plex_playlist_items_playlist_id", "plex_playlist_items", "playlist_id"),
]


def upgrade() -> None:
    for idx, _table, _col in _REDUNDANT:
        # IF EXISTS: a DB whose schema was stamped from a create_all (tests) won't have these once
        # the models drop index=True, so a plain drop_index would error there.
        op.execute(f"DROP INDEX IF EXISTS {idx}")


def downgrade() -> None:
    for idx, table, col in _REDUNDANT:
        op.create_index(idx, table, [col])