"""add user roles and approval

Revision ID: 20260603_0011
Revises: 20260603_0010
Create Date: 2026-06-03 19:30:00
"""
from __future__ import annotations

import os

from alembic import op
import sqlalchemy as sa


revision = "20260603_0011"
down_revision = "20260603_0010"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    columns = {column["name"] for column in inspector.get_columns("users")}

    if "role" not in columns:
        op.add_column(
            "users",
            sa.Column("role", sa.String(), nullable=False, server_default="user"),
        )
    if "approval_status" not in columns:
        op.add_column(
            "users",
            sa.Column("approval_status", sa.String(), nullable=False, server_default="pending"),
        )
    if "approved_at" not in columns:
        op.add_column("users", sa.Column("approved_at", sa.DateTime(timezone=True), nullable=True))
    if "approved_by" not in columns:
        op.add_column("users", sa.Column("approved_by", sa.Integer(), nullable=True))
    if "denied_at" not in columns:
        op.add_column("users", sa.Column("denied_at", sa.DateTime(timezone=True), nullable=True))

    admin_email = (os.getenv("ADMIN_EMAIL") or "").strip().lower()

    bind.execute(sa.text("UPDATE users SET role = 'user' WHERE role IS NULL OR role = ''"))
    bind.execute(
        sa.text(
            "UPDATE users SET approval_status = 'approved' "
            "WHERE approval_status IS NULL OR approval_status = '' OR approval_status = 'pending'"
        )
    )

    if admin_email:
        bind.execute(
            sa.text(
                "UPDATE users "
                "SET role = 'admin', approval_status = 'approved' "
                "WHERE lower(email) = :admin_email"
            ),
            {"admin_email": admin_email},
        )

    op.alter_column("users", "role", server_default=None)
    op.alter_column("users", "approval_status", server_default=None)


def downgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    columns = {column["name"] for column in inspector.get_columns("users")}

    if "denied_at" in columns:
        op.drop_column("users", "denied_at")
    if "approved_by" in columns:
        op.drop_column("users", "approved_by")
    if "approved_at" in columns:
        op.drop_column("users", "approved_at")
    if "approval_status" in columns:
        op.drop_column("users", "approval_status")
    if "role" in columns:
        op.drop_column("users", "role")