"""Align routes_manifests with ORM (manifest, source, created_at).

Revision ID: 015
Revises: 014

001 created ``routes`` + ``generated_at``; models use ``manifest``, ``source``, ``created_at``.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy import text

revision: str = "015"
down_revision: Union[str, None] = "014"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _routes_manifest_columns(bind) -> set[str]:
    r = bind.execute(
        text(
            """
            SELECT column_name FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = 'routes_manifests'
            """
        )
    )
    return {row[0] for row in r}


def upgrade() -> None:
    bind = op.get_bind()
    cols = _routes_manifest_columns(bind)

    if "manifest" in cols and "source" in cols:
        return

    if "routes" in cols:
        op.execute(text("ALTER TABLE routes_manifests RENAME COLUMN routes TO manifest"))

    cols = _routes_manifest_columns(bind)
    if "generated_at" in cols and "created_at" not in cols:
        op.execute(text("ALTER TABLE routes_manifests RENAME COLUMN generated_at TO created_at"))

    cols = _routes_manifest_columns(bind)
    if "source" not in cols:
        op.add_column(
            "routes_manifests",
            sa.Column(
                "source",
                sa.String(length=50),
                nullable=False,
                server_default=sa.text("'legacy'"),
            ),
        )
        op.alter_column("routes_manifests", "source", server_default=None)


def downgrade() -> None:
    bind = op.get_bind()
    cols = _routes_manifest_columns(bind)

    if "source" in cols:
        op.drop_column("routes_manifests", "source")

    cols = _routes_manifest_columns(bind)
    if "created_at" in cols and "generated_at" not in cols:
        op.execute(text("ALTER TABLE routes_manifests RENAME COLUMN created_at TO generated_at"))

    cols = _routes_manifest_columns(bind)
    if "manifest" in cols and "routes" not in cols:
        op.execute(text("ALTER TABLE routes_manifests RENAME COLUMN manifest TO routes"))