"""add mail profile scope and policy hierarchy

Revision ID: e4f5a6b7c8d9
Revises: d3e4f5a6b7c8
Create Date: 2026-06-16 13:30:00.000000
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "e4f5a6b7c8d9"
down_revision = "d3e4f5a6b7c8"
branch_labels = None
depends_on = None


_POLICY_DEFAULT = sa.text("'{}'")


def _columns(inspector: sa.Inspector, table_name: str) -> set[str]:
    return {column["name"] for column in inspector.get_columns(table_name)}


def _indexes(inspector: sa.Inspector, table_name: str) -> set[str]:
    return {index["name"] for index in inspector.get_indexes(table_name)}


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    tables = set(inspector.get_table_names())

    for table_name in ("access_users", "access_groups", "campaigns"):
        if table_name not in tables:
            continue
        columns = _columns(inspector, table_name)
        if "mail_profile_policy" not in columns:
            op.add_column(
                table_name,
                sa.Column("mail_profile_policy", sa.JSON(), nullable=False, server_default=_POLICY_DEFAULT),
            )

    if "mail_server_profiles" not in tables:
        return

    columns = _columns(inspector, "mail_server_profiles")
    with op.batch_alter_table("mail_server_profiles") as batch:
        if "scope_type" not in columns:
            batch.add_column(sa.Column("scope_type", sa.String(length=20), nullable=False, server_default="tenant"))
        if "scope_id" not in columns:
            batch.add_column(sa.Column("scope_id", sa.String(length=36), nullable=True))
        batch.alter_column("tenant_id", existing_type=sa.String(length=36), nullable=True)

    op.execute("UPDATE mail_server_profiles SET scope_type = 'tenant' WHERE scope_type IS NULL OR scope_type = ''")
    op.execute("UPDATE mail_server_profiles SET scope_id = tenant_id WHERE scope_id IS NULL AND tenant_id IS NOT NULL")

    inspector = sa.inspect(bind)
    indexes = _indexes(inspector, "mail_server_profiles")
    if "ix_mail_server_profiles_scope_type" not in indexes:
        op.create_index(op.f("ix_mail_server_profiles_scope_type"), "mail_server_profiles", ["scope_type"])
    if "ix_mail_server_profiles_scope_id" not in indexes:
        op.create_index(op.f("ix_mail_server_profiles_scope_id"), "mail_server_profiles", ["scope_id"])
    if "ix_mail_server_profiles_scope" not in indexes:
        op.create_index("ix_mail_server_profiles_scope", "mail_server_profiles", ["scope_type", "scope_id"])


def downgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    tables = set(inspector.get_table_names())

    if "mail_server_profiles" in tables:
        indexes = _indexes(inspector, "mail_server_profiles")
        for name in ("ix_mail_server_profiles_scope", op.f("ix_mail_server_profiles_scope_id"), op.f("ix_mail_server_profiles_scope_type")):
            if name in indexes:
                op.drop_index(name, table_name="mail_server_profiles")
        columns = _columns(inspector, "mail_server_profiles")
        with op.batch_alter_table("mail_server_profiles") as batch:
            if "scope_id" in columns:
                batch.drop_column("scope_id")
            if "scope_type" in columns:
                batch.drop_column("scope_type")
            batch.alter_column("tenant_id", existing_type=sa.String(length=36), nullable=False)

    for table_name in ("campaigns", "access_groups", "access_users"):
        if table_name in tables and "mail_profile_policy" in _columns(inspector, table_name):
            op.drop_column(table_name, "mail_profile_policy")
