[change] Enforce case-insensitive uniqueness for user email addresses

[nemesifier]

Is your change request related to a problem? Please describe.

openwisp_users.User.email has exact database uniqueness, while User.clean() rejects duplicates with email__iexact.

Normal registration and import paths call full_clean(), but direct writes or integrations that bypass model validation can create casing-only duplicates, such as user@example.com and USER@example.com. This makes email identity ambiguous and prevents safely adding a database-level case-insensitive uniqueness constraint.

Describe the change you would like

Replace unique=True on User.email with a conditional functional unique constraint:

UniqueConstraint(
    Lower("email"),
    condition=Q(email__isnull=False),
    name="unique_user_email_lower",
)

The condition means the constraint applies only to non-null emails. Accounts without an email address remain supported, and any number of users may have email=None.

Add a data migration before the schema migration. It must first normalize legacy empty-string emails to NULL, following the precedent in migration 0007_unique_email. An empty string is not NULL, and records created without full_clean() may still contain one.

After that normalization, run a non-mutating preflight check before adding the constraint. The preflight migration must:

1. Detect groups of non-null user emails whose Lower("email") value occurs more than once. For example:

from django.db.models import Count
from django.db.models.functions import Lower

conflicting_emails = (
    User.objects.exclude(email__isnull=True)
    .annotate(normalized_email=Lower("email"))
    .values("normalized_email")
    .annotate(count=Count("id"))
    .filter(count__gt=1)
)

2. Abort before adding the constraint if conflicts exist.
3. Create or overwrite openwisp-users-email-conflicts.csv in the current working directory from which manage.py migrate was executed.
4. Write every conflicting account to the CSV with this exact header:

id,email

5. Use restrictive file permissions where supported.
6. Raise an actionable exception that does not include email addresses, but includes the absolute report path:

Cannot enforce case-insensitive uniqueness for user email addresses.

Conflicting accounts were written to:
<absolute path>/openwisp-users-email-conflicts.csv

Review the CSV and resolve every conflicting account group before rerunning migrations. For each group, choose the account that should retain the email address and transfer any required memberships, permissions, tokens, RADIUS registrations, and related data. Change or remove the email address from the other account only after confirming it is safe.

Do not delete accounts blindly. The CSV contains personal data. Restrict access to it and delete it securely after resolving the conflicts.

The migration must not merge, delete, or modify conflicting accounts automatically.

Add migration coverage for:

• Casing-only duplicates inserted without full_clean() write the expected CSV and raise the actionable error.
• The exception contains the absolute CSV path but no email address.
• A subsequent migration attempt overwrites the previous report.
• Empty strings are normalized to NULL before duplicate detection.
• Unique lowercase emails and null emails migrate successfully.
• Multiple users without email addresses remain valid after the constraint is added.
• Default and swapped user configurations.

Describe alternatives you have considered

• Keep the existing model-level email__iexact validation only. This does not protect direct writes or concurrent writes that bypass validation.
• Normalize email addresses on every write. This does not prevent legacy duplicates and may silently alter an address without a safe account-resolution policy.
• Automatically merge or delete conflicting accounts during migration. Rejected because memberships, permissions, tokens, RADIUS registrations, and other related records require administrator review.
• Include conflicting email addresses directly in the migration exception. Rejected to avoid exposing personal data in deployment logs.

Additional context

This change covers User.email only. django-allauth EmailAddress constraints should be investigated separately.