"""add manual high-risk flag and note to user

Revision ID: user_manual_risk_fields
Revises: risk_monitor_generic
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "user_manual_risk_fields"
down_revision: str | None = "risk_monitor_generic"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

_JSON = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")
_INVITE_WITHDRAW_PAGE = "invite-withdraws"


def _finance_role() -> sa.TableClause:
    return sa.table(
        "admin_role",
        sa.column("name", sa.String),
        sa.column("pages", _JSON),
    )


def upgrade() -> None:
    with op.batch_alter_table("user") as batch_op:
        batch_op.add_column(
            sa.Column(
                "is_high_risk",
                sa.Boolean(),
                server_default=sa.false(),
                nullable=False,
            )
        )
        batch_op.add_column(sa.Column("high_risk_note", sa.Text(), nullable=True))
        batch_op.create_index("ix_user_is_high_risk", ["is_high_risk"], unique=False)
    role = _finance_role()
    conn = op.get_bind()
    row = conn.execute(
        sa.select(role.c.pages).where(role.c.name == "finance")
    ).scalar_one_or_none()
    if row is not None and _INVITE_WITHDRAW_PAGE not in (row or []):
        conn.execute(
            role.update()
            .where(role.c.name == "finance")
            .values(pages=[*(row or []), _INVITE_WITHDRAW_PAGE])
        )


def downgrade() -> None:
    role = _finance_role()
    conn = op.get_bind()
    row = conn.execute(
        sa.select(role.c.pages).where(role.c.name == "finance")
    ).scalar_one_or_none()
    if row is not None:
        conn.execute(
            role.update()
            .where(role.c.name == "finance")
            .values(
                pages=[
                    page for page in (row or []) if page != _INVITE_WITHDRAW_PAGE
                ]
            )
        )
    with op.batch_alter_table("user") as batch_op:
        batch_op.drop_index("ix_user_is_high_risk")
        batch_op.drop_column("high_risk_note")
        batch_op.drop_column("is_high_risk")
