"""global accounts and operational tenant administration

Revision ID: 8c9d0e1f2a3b
Revises: 7b8c9d0e1f2a
Create Date: 2026-06-14 18:00:00.000000
"""
from __future__ import annotations

import uuid
from datetime import datetime, timezone

from alembic import op
import sqlalchemy as sa


revision = "8c9d0e1f2a3b"
down_revision = "7b8c9d0e1f2a"
branch_labels = None
depends_on = None


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _normalize_email(value: str) -> str:
    return value.strip().casefold()


def _coerce_datetime(value: object) -> datetime | None:
    if value is None or isinstance(value, datetime):
        return value
    if isinstance(value, str):
        normalized = value.strip().replace("Z", "+00:00")
        parsed = datetime.fromisoformat(normalized)
        return parsed.replace(tzinfo=timezone.utc) if parsed.tzinfo is None else parsed
    raise TypeError(f"Unsupported datetime value during account migration: {value!r}")


def _role_definitions() -> dict[str, dict[str, object]]:
    # Kept in the migration so historic upgrades remain deterministic even if
    # application role presets evolve later.
    tenant_admin = [
        "admin:api_keys:read", "admin:api_keys:write", "admin:groups:read", "admin:groups:write",
        "admin:roles:read", "admin:roles:write", "admin:settings:read", "admin:settings:write",
        "admin:users:read", "admin:users:write", "attachments:read", "attachments:write",
        "audit:read", "campaign:build", "campaign:queue", "campaign:read", "campaign:send",
        "campaign:send_test", "campaign:validate", "campaign:write", "reports:read", "reports:send",
    ]
    return {
        "owner": {"name": "Owner", "permissions": ["tenant:*"], "description": "Full tenant access, including administration and delivery."},
        "admin": {"name": "Administrator", "permissions": tenant_admin, "description": "Tenant administration and full campaign operation without system access."},
        "campaign_manager": {"name": "Campaign manager", "permissions": ["campaign:read", "campaign:write", "campaign:validate", "campaign:build", "attachments:read", "attachments:write", "reports:read"], "description": "Prepare, validate and build campaigns, but do not start real delivery."},
        "sender": {"name": "Sender", "permissions": ["campaign:read", "campaign:queue", "campaign:send_test", "campaign:send", "attachments:read", "reports:read", "reports:send"], "description": "Review, queue and send prepared campaigns."},
        "reviewer": {"name": "Reviewer", "permissions": ["campaign:read", "campaign:validate", "attachments:read", "reports:read"], "description": "Inspect campaigns, validate them and view delivery reports."},
        "viewer": {"name": "Viewer", "permissions": ["campaign:read", "attachments:read", "reports:read"], "description": "Read campaigns, files and reports without changing them."},
        "auditor": {"name": "Auditor", "permissions": ["campaign:read", "reports:read", "audit:read"], "description": "Read campaigns, reports and audit records."},
    }


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    tables = set(inspector.get_table_names())
    user_columns = {column["name"] for column in inspector.get_columns("access_users")}
    # Base.metadata.create_all() from a newer application can create brand-new
    # tables while leaving existing tables unaltered. Repair that known drift by
    # removing only empty, unreferenced administration tables before applying the
    # real migration. A non-empty table is never guessed at or discarded.
    if "account_id" not in user_columns and "access_system_role_assignments" in tables:
        count = bind.execute(sa.text("SELECT COUNT(*) FROM access_system_role_assignments")).scalar_one()
        if count:
            raise RuntimeError("Cannot reconcile non-empty create_all system_role_assignments table")
        op.drop_table("access_system_role_assignments")
        tables.remove("access_system_role_assignments")
    if "account_id" not in user_columns and "access_accounts" in tables:
        count = bind.execute(sa.text("SELECT COUNT(*) FROM access_accounts")).scalar_one()
        if count:
            raise RuntimeError("Cannot reconcile non-empty create_all accounts table")
        op.drop_table("access_accounts")

    op.create_table(
        "access_accounts",
        sa.Column("id", sa.String(length=36), nullable=False),
        sa.Column("email", sa.String(length=320), nullable=False),
        sa.Column("normalized_email", sa.String(length=320), nullable=False),
        sa.Column("display_name", sa.String(length=255), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("auth_provider", sa.String(length=50), nullable=False, server_default="local"),
        sa.Column("password_hash", sa.String(length=500), nullable=True),
        sa.Column("password_reset_required", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("last_login_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("normalized_email", name="uq_accounts_normalized_email"),
    )
    op.create_index(op.f("ix_access_accounts_normalized_email"), "access_accounts", ["normalized_email"])

    with op.batch_alter_table("tenancy_tenants") as batch_op:
        batch_op.add_column(sa.Column("description", sa.Text(), nullable=True))
        batch_op.add_column(sa.Column("default_locale", sa.String(length=20), nullable=False, server_default="en"))
        batch_op.add_column(sa.Column("settings", sa.JSON(), nullable=False, server_default="{}"))

    with op.batch_alter_table("access_groups") as batch_op:
        batch_op.add_column(sa.Column("description", sa.Text(), nullable=True))
        batch_op.add_column(sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()))

    with op.batch_alter_table("access_roles") as batch_op:
        batch_op.add_column(sa.Column("description", sa.Text(), nullable=True))
        batch_op.add_column(sa.Column("is_builtin", sa.Boolean(), nullable=False, server_default=sa.false()))
        batch_op.add_column(sa.Column("is_assignable", sa.Boolean(), nullable=False, server_default=sa.true()))

    with op.batch_alter_table("access_users") as batch_op:
        batch_op.add_column(sa.Column("account_id", sa.String(length=36), nullable=True))
    with op.batch_alter_table("access_auth_sessions") as batch_op:
        batch_op.add_column(sa.Column("account_id", sa.String(length=36), nullable=True))

    users = bind.execute(sa.text(
        "SELECT id, tenant_id, email, display_name, is_active, is_tenant_admin, auth_provider, password_hash, last_login_at, created_at, updated_at FROM access_users ORDER BY created_at, id"
    )).mappings().all()

    accounts_by_email: dict[str, str] = {}
    account_rows: dict[str, dict[str, object]] = {}
    first_system_owner_account_id: str | None = None
    for row in users:
        normalized = _normalize_email(row["email"])
        account_id = accounts_by_email.get(normalized)
        if account_id is None:
            account_id = str(uuid.uuid4())
            accounts_by_email[normalized] = account_id
            account_rows[account_id] = {
                "id": account_id,
                "email": row["email"],
                "normalized_email": normalized,
                "display_name": row["display_name"],
                "is_active": bool(row["is_active"]),
                "auth_provider": row["auth_provider"] or "local",
                "password_hash": row["password_hash"],
                "password_reset_required": False,
                "last_login_at": _coerce_datetime(row["last_login_at"]),
                "created_at": _coerce_datetime(row["created_at"]) or _now(),
                "updated_at": _coerce_datetime(row["updated_at"]) or _now(),
            }
        else:
            account = account_rows[account_id]
            if not account["password_hash"] and row["password_hash"]:
                account["password_hash"] = row["password_hash"]
            elif account["password_hash"] and row["password_hash"] and account["password_hash"] != row["password_hash"]:
                account["password_reset_required"] = True
            if not account["display_name"] and row["display_name"]:
                account["display_name"] = row["display_name"]
            account["is_active"] = bool(account["is_active"] or row["is_active"])
        if first_system_owner_account_id is None and row["is_active"] and row["is_tenant_admin"]:
            first_system_owner_account_id = account_id

    accounts_table = sa.table(
        "access_accounts",
        sa.column("id", sa.String), sa.column("email", sa.String), sa.column("normalized_email", sa.String),
        sa.column("display_name", sa.String), sa.column("is_active", sa.Boolean), sa.column("auth_provider", sa.String),
        sa.column("password_hash", sa.String), sa.column("password_reset_required", sa.Boolean),
        sa.column("last_login_at", sa.DateTime(timezone=True)), sa.column("created_at", sa.DateTime(timezone=True)),
        sa.column("updated_at", sa.DateTime(timezone=True)),
    )
    if account_rows:
        bind.execute(accounts_table.insert(), list(account_rows.values()))
    for row in users:
        bind.execute(
            sa.text("UPDATE access_users SET account_id = :account_id WHERE id = :user_id"),
            {"account_id": accounts_by_email[_normalize_email(row["email"])], "user_id": row["id"]},
        )
    bind.execute(sa.text(
        "UPDATE access_auth_sessions SET account_id = (SELECT access_users.account_id FROM access_users WHERE access_users.id = access_auth_sessions.user_id)"
    ))

    with op.batch_alter_table("access_users") as batch_op:
        batch_op.alter_column("account_id", existing_type=sa.String(length=36), nullable=False)
        batch_op.create_foreign_key("fk_users_account_id_accounts", "access_accounts", ["account_id"], ["id"], ondelete="CASCADE")
        batch_op.create_unique_constraint("uq_users_tenant_account", ["tenant_id", "account_id"])
    op.create_index(op.f("ix_access_users_account_id"), "access_users", ["account_id"])

    with op.batch_alter_table("access_auth_sessions") as batch_op:
        batch_op.alter_column("account_id", existing_type=sa.String(length=36), nullable=False)
        batch_op.create_foreign_key("fk_auth_sessions_account_id_accounts", "access_accounts", ["account_id"], ["id"], ondelete="CASCADE")
    op.create_index(op.f("ix_access_auth_sessions_account_id"), "access_auth_sessions", ["account_id"])

    op.create_table(
        "access_system_role_assignments",
        sa.Column("id", sa.String(length=36), nullable=False),
        sa.Column("account_id", sa.String(length=36), nullable=False),
        sa.Column("role_id", sa.String(length=36), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["access_accounts.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["role_id"], ["access_roles.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("account_id", "role_id", name="uq_system_role_assignments"),
    )
    op.create_index(op.f("ix_access_system_role_assignments_account_id"), "access_system_role_assignments", ["account_id"])
    op.create_index(op.f("ix_access_system_role_assignments_role_id"), "access_system_role_assignments", ["role_id"])

    roles_table = sa.table(
        "access_roles",
        sa.column("id", sa.String), sa.column("tenant_id", sa.String), sa.column("slug", sa.String),
        sa.column("name", sa.String), sa.column("description", sa.Text), sa.column("permissions", sa.JSON),
        sa.column("is_builtin", sa.Boolean), sa.column("is_assignable", sa.Boolean),
        sa.column("created_at", sa.DateTime(timezone=True)), sa.column("updated_at", sa.DateTime(timezone=True)),
    )
    now = _now()
    tenant_ids = [row[0] for row in bind.execute(sa.text("SELECT id FROM tenancy_tenants")).all()]
    definitions = _role_definitions()
    tenant_role_ids: dict[tuple[str, str], str] = {}
    for tenant_id in tenant_ids:
        for slug, definition in definitions.items():
            existing = bind.execute(
                sa.text("SELECT id FROM access_roles WHERE tenant_id = :tenant_id AND slug = :slug"),
                {"tenant_id": tenant_id, "slug": slug},
            ).scalar_one_or_none()
            if existing:
                role_id = existing
                bind.execute(
                    roles_table.update().where(roles_table.c.id == role_id).values(
                        name=definition["name"], description=definition["description"],
                        permissions=definition["permissions"], is_builtin=True, is_assignable=True,
                        updated_at=now,
                    )
                )
            else:
                role_id = str(uuid.uuid4())
                bind.execute(roles_table.insert().values(
                    id=role_id, tenant_id=tenant_id, slug=slug, name=definition["name"],
                    description=definition["description"], permissions=definition["permissions"],
                    is_builtin=True, is_assignable=True, created_at=now, updated_at=now,
                ))
            tenant_role_ids[(tenant_id, slug)] = role_id

    system_owner_role_id = str(uuid.uuid4())
    system_auditor_role_id = str(uuid.uuid4())
    bind.execute(roles_table.insert(), [
        {
            "id": system_owner_role_id, "tenant_id": None, "slug": "system_owner", "name": "System owner",
            "description": "Full instance-wide administration. At least one active account must retain this role.",
            "permissions": ["system:*"], "is_builtin": True, "is_assignable": True,
            "created_at": now, "updated_at": now,
        },
        {
            "id": system_auditor_role_id, "tenant_id": None, "slug": "system_auditor", "name": "System auditor",
            "description": "Read tenant registry, system access and cross-tenant audit records.",
            "permissions": ["system:tenants:read", "system:access:read", "system:audit:read"],
            "is_builtin": True, "is_assignable": True, "created_at": now, "updated_at": now,
        },
    ])

    op.create_index(
        "uq_roles_system_slug",
        "access_roles",
        ["slug"],
        unique=True,
        sqlite_where=sa.text("tenant_id IS NULL"),
        postgresql_where=sa.text("tenant_id IS NULL"),
    )

    # Preserve the old tenant-admin shortcut by assigning the canonical owner
    # role. Authorization no longer reads users.is_tenant_admin after upgrade.
    for row in users:
        if not row["is_tenant_admin"]:
            continue
        owner_role_id = tenant_role_ids[(row["tenant_id"], "owner")]
        exists = bind.execute(sa.text(
            "SELECT 1 FROM access_user_role_assignments WHERE tenant_id = :tenant_id AND user_id = :user_id AND role_id = :role_id"
        ), {"tenant_id": row["tenant_id"], "user_id": row["id"], "role_id": owner_role_id}).first()
        if not exists:
            bind.execute(sa.text(
                "INSERT INTO access_user_role_assignments (id, tenant_id, user_id, role_id, created_at, updated_at) VALUES (:id, :tenant_id, :user_id, :role_id, :created_at, :updated_at)"
            ), {"id": str(uuid.uuid4()), "tenant_id": row["tenant_id"], "user_id": row["id"], "role_id": owner_role_id, "created_at": now, "updated_at": now})

    # Bootstrap rule for existing installations: the earliest active legacy
    # tenant administrator becomes the initial system owner. This prevents an
    # upgrade from producing an instance with no actor able to create tenants or
    # delegate system access. It can be changed immediately through Admin.
    if first_system_owner_account_id is None and account_rows:
        first_system_owner_account_id = next((
            account_id for account_id, account in account_rows.items() if account["is_active"]
        ), None)
    if first_system_owner_account_id:
        bind.execute(sa.text(
            "INSERT INTO access_system_role_assignments (id, account_id, role_id, created_at, updated_at) VALUES (:id, :account_id, :role_id, :created_at, :updated_at)"
        ), {"id": str(uuid.uuid4()), "account_id": first_system_owner_account_id, "role_id": system_owner_role_id, "created_at": now, "updated_at": now})


def downgrade() -> None:
    op.drop_index("uq_roles_system_slug", table_name="access_roles")
    op.drop_index(op.f("ix_access_system_role_assignments_role_id"), table_name="access_system_role_assignments")
    op.drop_index(op.f("ix_access_system_role_assignments_account_id"), table_name="access_system_role_assignments")
    op.drop_table("access_system_role_assignments")

    op.drop_index(op.f("ix_access_auth_sessions_account_id"), table_name="access_auth_sessions")
    with op.batch_alter_table("access_auth_sessions") as batch_op:
        batch_op.drop_constraint("fk_auth_sessions_account_id_accounts", type_="foreignkey")
        batch_op.drop_column("account_id")

    op.drop_index(op.f("ix_access_users_account_id"), table_name="access_users")
    with op.batch_alter_table("access_users") as batch_op:
        batch_op.drop_constraint("uq_users_tenant_account", type_="unique")
        batch_op.drop_constraint("fk_users_account_id_accounts", type_="foreignkey")
        batch_op.drop_column("account_id")

    with op.batch_alter_table("access_roles") as batch_op:
        batch_op.drop_column("is_assignable")
        batch_op.drop_column("is_builtin")
        batch_op.drop_column("description")

    with op.batch_alter_table("access_groups") as batch_op:
        batch_op.drop_column("is_active")
        batch_op.drop_column("description")

    with op.batch_alter_table("tenancy_tenants") as batch_op:
        batch_op.drop_column("settings")
        batch_op.drop_column("default_locale")
        batch_op.drop_column("description")

    op.drop_index(op.f("ix_access_accounts_normalized_email"), table_name="access_accounts")
    op.drop_table("access_accounts")
