"""email_canonical on users + invites, for matching one mailbox across Gmail spellings

Revision ID: 0079_email_canonical
Revises: 0078_password_revoked_at
Create Date: 2026-09-04

Gmail delivers `x.y@gmail.com`, `xy@gmail.com` and `xy+tag@gmail.com` to ONE mailbox; the invite gate
only lowercased. On 2026-09-03 that turned an approved user's Google sign-in into a silent second
access request (OAuth returns the account's stored primary, which need not be the spelling they typed)
and left prod with two user rows for one mailbox.

The backfill below reproduces `app.utils.canonical_email` in SQL. That duplication is deliberate — a
migration must not import application code that will keep changing — but it means the two have to
agree; `test_canonical_email_backfill_matches_the_helper` pins them together.

Deliberately NOT unique. Prod already carries canonical duplicates: two approved invites for one
mailbox dating back to 2026-06-16, and two active user rows from the 2026-09-03 walkthrough. Merging
accounts is a judgement call about people's data, not something a migration gets to do on its own, so
this migration only records the canonical form and leaves the duplicates in place for a human.
"""

import sqlalchemy as sa
from alembic import op

revision: str = "0079_email_canonical"
down_revision: str | None = "0078_password_revoked_at"
branch_labels: str | None = None
depends_on: str | None = None

# Mirrors app.utils.canonical_email: Gmail (and its googlemail alias) loses dots and any `+tag` from
# the local part and lands on gmail.com; every other domain is only lowercased, because the dot rule
# is not guaranteed elsewhere — and does not hold for Workspace custom domains unless their admin
# enabled it.
_CANON_SQL = """
    CASE
      WHEN split_part(lower({col}), '@', 2) IN ('gmail.com', 'googlemail.com')
       -- an empty local part after stripping falls through to plain lowercase, as the helper does
       AND replace(split_part(split_part(lower({col}), '@', 1), '+', 1), '.', '') <> ''
      THEN replace(split_part(split_part(lower({col}), '@', 1), '+', 1), '.', '') || '@gmail.com'
      ELSE lower({col})
    END
"""


def _backfill(table: str) -> None:
    op.execute(
        f"UPDATE {table} SET email_canonical = {_CANON_SQL.format(col='email')} "
        f"WHERE email IS NOT NULL"
    )


def upgrade() -> None:
    for table in ("users", "invites"):
        op.add_column(
            table,
            sa.Column("email_canonical", sa.String(length=320), nullable=False, server_default=""),
        )
        _backfill(table)
        op.create_index(f"ix_{table}_email_canonical", table, ["email_canonical"])


def downgrade() -> None:
    for table in ("users", "invites"):
        op.drop_index(f"ix_{table}_email_canonical", table_name=table)
        op.drop_column(table, "email_canonical")