"""describe every table with COMMENT ON TABLE

Revision ID: g3b4c5d6e7f8
Revises: f2a3b4c5d6e7
Create Date: 2026-10-06 09:00:00.000000

Descriptions come from app/models/table_catalog.py, so pgAdmin and psql show what each
table holds. Tables missing from the database are skipped. Safe to re-run.
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

from app.models.table_catalog import table_descriptions

revision: str = 'g3b4c5d6e7f8'
down_revision: Union[str, None] = 'f2a3b4c5d6e7'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _comment_sql(table: str, description: str) -> str:
    # DDL takes no bind parameters, so quote the literal (descriptions are our own text).
    return f'COMMENT ON TABLE "{table}" IS \'' + description.replace("'", "''") + "'"


def _existing_tables(conn) -> set[str]:
    rows = conn.execute(sa.text(
        "SELECT tablename FROM pg_tables WHERE schemaname = current_schema()"
    ))
    return {r[0] for r in rows}


def upgrade() -> None:
    conn = op.get_bind()
    existing = _existing_tables(conn)
    for table, description in table_descriptions().items():
        if table in existing:
            conn.execute(sa.text(_comment_sql(table, description)))


def downgrade() -> None:
    conn = op.get_bind()
    existing = _existing_tables(conn)
    for table in table_descriptions():
        if table in existing:
            op.execute(f'COMMENT ON TABLE "{table}" IS NULL')