"""Finds per-field indexes whose datasource or form no longer exists, and drops
them with --apply. Each one still slows every write to its records table.

    python scripts/drop_orphaned_field_indexes.py          # lists them
    python scripts/drop_orphaned_field_indexes.py --apply  # drops them

Deleting a tenant used to leave these behind. It's done as
PROVISIONING_SUPERUSER, which sees every tenant's sources: the API's own role
is limited by row-level security, so other tenants' live sources would look
missing to it.
"""

import asyncio
import re
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

import asyncpg  # noqa: E402
from sqlalchemy.engine import make_url  # noqa: E402

from app.core.config import settings  # noqa: E402

_INDEX_NAME = re.compile(r"^ix_ff_[mf]_([0-9a-f]{32})_[0-9a-f]+$")


def orphaned(index_names: list[str], source_ids: set[str]) -> list[str]:
    """The field indexes named for a source that isn't in source_ids (hex ids)."""
    return [name for name in index_names if (match := _INDEX_NAME.match(name)) and match.group(1) not in source_ids]


async def main(apply: bool) -> None:
    url = make_url(settings.database_url)
    conn = await asyncpg.connect(
        host=url.host, port=url.port or 5432, database=url.database,
        user=settings.provisioning_superuser, password=settings.provisioning_superuser_password,
    )
    try:
        if not await conn.fetchval("SELECT rolbypassrls OR rolsuper FROM pg_roles WHERE rolname = current_user"):
            raise SystemExit("PROVISIONING_SUPERUSER must be able to see every tenant's sources.")
        rows = await conn.fetch("SELECT id FROM masters UNION SELECT form_id FROM forms")
        names = [r[0] for r in await conn.fetch("SELECT indexname FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE 'ix\\_ff\\_%'")]
        found = orphaned(names, {r[0].hex for r in rows})
        print(f"{len(found)} of {len(names)} field indexes have no source.")
        if not apply:
            for name in found:
                print(" ", name)
            print("Nothing dropped. Run with --apply to drop them.")
            return
        for name in found:
            await conn.execute(f'DROP INDEX CONCURRENTLY IF EXISTS "{name}"')
        print(f"Dropped {len(found)}.")
    finally:
        await conn.close()


if __name__ == "__main__":
    asyncio.run(main("--apply" in sys.argv[1:]))