"""split mail profile usernames from server config

Revision ID: 0a1b2c3d4e6f
Revises: f5a6b7c8d9e0
Create Date: 2026-06-25 15:20:00.000000
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0a1b2c3d4e6f"
down_revision = "f5a6b7c8d9e0"
branch_labels = None
depends_on = None


def _profiles_table() -> sa.Table:
    return sa.table(
        "mail_server_profiles",
        sa.column("id", sa.String(length=36)),
        sa.column("smtp_config", sa.JSON()),
        sa.column("smtp_username", sa.String(length=320)),
        sa.column("imap_config", sa.JSON()),
        sa.column("imap_username", sa.String(length=320)),
    )


def _without_username(value):
    if not isinstance(value, dict):
        return value, None
    data = dict(value)
    username = data.pop("username", None)
    data.pop("enabled", None)
    return data, username


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    if "mail_server_profiles" not in inspector.get_table_names():
        return
    columns = {column["name"] for column in inspector.get_columns("mail_server_profiles")}
    if "smtp_username" not in columns:
        op.add_column("mail_server_profiles", sa.Column("smtp_username", sa.String(length=320), nullable=True))
    if "imap_username" not in columns:
        op.add_column("mail_server_profiles", sa.Column("imap_username", sa.String(length=320), nullable=True))

    profiles = _profiles_table()
    rows = bind.execute(sa.select(profiles.c.id, profiles.c.smtp_config, profiles.c.smtp_username, profiles.c.imap_config, profiles.c.imap_username)).mappings().all()
    for row in rows:
        smtp_config, smtp_username = _without_username(row["smtp_config"] or {})
        imap_config, imap_username = _without_username(row["imap_config"] or {}) if row["imap_config"] is not None else (None, None)
        values = {
            "smtp_config": smtp_config,
            "imap_config": imap_config,
        }
        if row["smtp_username"] in (None, "") and smtp_username not in (None, ""):
            values["smtp_username"] = str(smtp_username)
        if row["imap_username"] in (None, "") and imap_username not in (None, ""):
            values["imap_username"] = str(imap_username)
        bind.execute(sa.update(profiles).where(profiles.c.id == row["id"]).values(**values))


def downgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    if "mail_server_profiles" not in inspector.get_table_names():
        return
    columns = {column["name"] for column in inspector.get_columns("mail_server_profiles")}
    profiles = _profiles_table()
    if {"smtp_username", "imap_username"}.issubset(columns):
        rows = bind.execute(sa.select(profiles.c.id, profiles.c.smtp_config, profiles.c.smtp_username, profiles.c.imap_config, profiles.c.imap_username)).mappings().all()
        for row in rows:
            smtp_config = dict(row["smtp_config"] or {})
            if row["smtp_username"] not in (None, ""):
                smtp_config["username"] = row["smtp_username"]
            imap_config = dict(row["imap_config"] or {}) if row["imap_config"] is not None else None
            if imap_config is not None and row["imap_username"] not in (None, ""):
                imap_config["username"] = row["imap_username"]
            bind.execute(sa.update(profiles).where(profiles.c.id == row["id"]).values(smtp_config=smtp_config, imap_config=imap_config))
    if "imap_username" in columns:
        op.drop_column("mail_server_profiles", "imap_username")
    if "smtp_username" in columns:
        op.drop_column("mail_server_profiles", "smtp_username")
