"""
Write the table descriptions from app/models/table_catalog.py into the database
(COMMENT ON TABLE). Run after adding or renaming tables:

    python scripts/apply_table_comments.py
"""
import asyncio
import os
import sys

sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import text
from app.core.database import engine
from app.models.table_catalog import table_descriptions


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("'", "''") + "'"


async def main() -> None:
    async with engine.begin() as conn:
        rows = await conn.execute(text("SELECT tablename FROM pg_tables WHERE schemaname = current_schema()"))
        existing = {r[0] for r in rows}
        applied = 0
        for table, description in table_descriptions().items():
            if table in existing:
                await conn.execute(text(_comment_sql(table, description)))
                applied += 1
        undocumented = sorted(existing - set(table_descriptions()) - {'alembic_version'})
    print(f'Described {applied} tables.')
    if undocumented:
        print('Tables without a description in table_catalog.py:', ', '.join(undocumented))


if __name__ == '__main__':
    asyncio.run(main())