"""Add account-email columns to cmdb_cloud_scopes.

Surfaces the cloud provider's per-account contact email (AWS
Organizations ``list_accounts`` → ``Email``; already captured into
``raw_data`` but never promoted to a first-class field) and lets the
scope owner override it while keeping the cloud value visible.

* ``cloud_account_email``  — cloud-authoritative, refreshed every sync.
* ``account_email_override`` — owner/admin-set replacement (NULL = use cloud).
* ``account_email_modified_by_person_id`` / ``account_email_modified_at``
  — who set/cleared the override and when. Plain sized column, NO FK to
  ``identity_persons`` (TiDB-FK-safe; resolved to a label in Python by
  the serializer, exactly like ``decommissioned_by`` on this table).

Revision ID: 450
Revises: 449
"""

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for

revision = "450"
down_revision = "449"
branch_labels = None
depends_on = None

_TABLE = "cmdb_cloud_scopes"
_COLUMNS = (
    "cloud_account_email",
    "account_email_override",
    "account_email_modified_by_person_id",
    "account_email_modified_at",
)


def _column_names() -> set[str]:
    return {
        str(column["name"])
        for column in sa.inspect(op.get_bind()).get_columns(_TABLE)
    }


def upgrade() -> None:
    # MySQL/TiDB DDL is non-transactional — inspect each column
    # independently so a process killed mid-way resumes cleanly.
    existing = _column_names()
    person_id_type = id_type_for("identity_persons")
    if "cloud_account_email" not in existing:
        op.add_column(
            _TABLE,
            sa.Column("cloud_account_email", sa.String(320), nullable=True),
        )
    if "account_email_override" not in existing:
        op.add_column(
            _TABLE,
            sa.Column("account_email_override", sa.String(320), nullable=True),
        )
    if "account_email_modified_by_person_id" not in existing:
        op.add_column(
            _TABLE,
            sa.Column(
                "account_email_modified_by_person_id",
                person_id_type,
                nullable=True,
            ),
        )
    if "account_email_modified_at" not in existing:
        op.add_column(
            _TABLE,
            sa.Column(
                "account_email_modified_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )


def downgrade() -> None:
    existing = _column_names()
    for column in reversed(_COLUMNS):
        if column in existing:
            op.drop_column(_TABLE, column)