import os
from collections.abc import Sequence
from pathlib import Path

import sqlalchemy as sa
from alembic import op


def get_revision() -> str:
    return Path(__file__).name.split("-")[0]


def get_down_revision() -> str | None:
    versions_path = Path(__file__).parent
    migration_files = sorted([f.name for f in versions_path.iterdir() if f.is_file()])
    for i, file in enumerate(migration_files):
        if file == Path(__file__).name:
            return None if i == 0 else migration_files[i - 1].split("-")[0]
    return None


# revision identifiers, used by Alembic.
revision: str = get_revision()
down_revision: str | None = get_down_revision()
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

username = os.getenv("POSTGRES_WATCHER_USER", "watcher")
password = os.getenv("POSTGRES_WATCHER_PASSWORD", "watcher")
watcher_tables = (
    "public.tenants",
    "public.clients",
    "public.client_tenants",
    "public.pending_client_tenants",
    "public.quota_allocations",
    "public.quota_usages",
    "public.usage_event_requests",
    "public.usage_events",
    "public.prices",
)


def quote_string_literal(value: str) -> str:
    # CREATE ROLE ... PASSWORD ではbindparamが使えないため、
    # シングルクォートを含むパスワードでもSQLが壊れないようDDLに埋め込めるSQL文字列リテラルへ変換する。
    bind = op.get_bind()
    literal_processor = sa.String().literal_processor(bind.dialect)
    if literal_processor is None:
        return "'" + value.replace("'", "''") + "'"
    return literal_processor(value)


def upgrade() -> None:
    op.execute(f"CREATE ROLE {username} WITH LOGIN PASSWORD {quote_string_literal(password)}")
    op.execute(f"ALTER ROLE {username} BYPASSRLS")  # クエリエディタではセッション変数が使えないため
    op.execute(f"GRANT CONNECT ON DATABASE postgres TO {username}")
    op.execute(f"GRANT USAGE ON SCHEMA public TO {username}")
    op.execute(f"GRANT SELECT ON TABLE {', '.join(watcher_tables)} TO {username}")


def downgrade() -> None:
    op.execute(f"REVOKE SELECT ON TABLE {', '.join(watcher_tables)} FROM {username}")  # noqa: S608
    op.execute(f"REVOKE USAGE ON SCHEMA public FROM {username}")
    op.execute(f"REVOKE CONNECT ON DATABASE postgres FROM {username}")
    op.execute(f"DROP ROLE IF EXISTS {username}")
