"""Creates the API's own database role and its database, if they don't
exist yet — the user, password and database name come from DATABASE_URL,
and the work is done as PROVISIONING_SUPERUSER.

    python scripts/create_app_role.py

The API must connect as an ordinary role that owns its tables, never as a
superuser: a superuser bypasses row-level security, which is what keeps
each tenant's rows to itself (db/schema.sql). Run before
`python -m app.db_migrate deploy` on a fresh server (CI does).
"""

import asyncio
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
from app.core.db_provisioning import _quote_ident, _quote_literal, validate_identifier  # noqa: E402


async def main() -> None:
    url = make_url(settings.database_url)
    validate_identifier(url.username, "role")
    validate_identifier(url.database, "database")
    admin = await asyncpg.connect(
        host=url.host, port=url.port or 5432, database="postgres",
        user=settings.provisioning_superuser, password=settings.provisioning_superuser_password,
    )
    try:
        role = await admin.fetchrow("SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = $1", url.username)
        if role is None:
            await admin.execute(f"CREATE ROLE {_quote_ident(url.username)} LOGIN PASSWORD {_quote_literal(url.password or '')}")
            print(f"Created role {url.username}.")
        elif role["rolsuper"] or role["rolbypassrls"]:
            sys.exit(f"Role {url.username} is a superuser or bypasses RLS — the API must connect as an ordinary role.")
        if not await admin.fetchval("SELECT 1 FROM pg_database WHERE datname = $1", url.database):
            await admin.execute(f"CREATE DATABASE {_quote_ident(url.database)} OWNER {_quote_ident(url.username)}")
            print(f"Created database {url.database}.")
        print("Ready.")
    finally:
        await admin.close()


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