"""
Fix users.email unique constraint for multi-tenant setup.

- Blank emails become NULL (multiple users may omit email).
- Replace global users_email_key with per-company partial unique indexes.

Run: python migrations/fix_user_email_nullable_unique.py
"""
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

from sqlalchemy import text
from app.core.database import engine


def run():
    dialect = engine.dialect.name
    with engine.begin() as conn:
        if dialect == "sqlite":
            conn.execute(
                text("UPDATE users SET email = NULL WHERE email IS NOT NULL AND trim(email) = ''")
            )
            print("SQLite: cleared empty user emails (NULL allowed multiple times on UNIQUE)")
            return

        conn.execute(
            text("UPDATE users SET email = NULL WHERE email IS NOT NULL AND trim(email) = ''")
        )
        conn.execute(text("ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key"))
        conn.execute(
            text(
                """
                CREATE UNIQUE INDEX IF NOT EXISTS ix_users_company_email_unique
                ON users (company_id, lower(email))
                WHERE email IS NOT NULL AND company_id IS NOT NULL
                """
            )
        )
        conn.execute(
            text(
                """
                CREATE UNIQUE INDEX IF NOT EXISTS ix_users_email_no_company_unique
                ON users (lower(email))
                WHERE email IS NOT NULL AND company_id IS NULL
                """
            )
        )
    print("users.email: empty cleared to NULL; per-company unique index applied")


if __name__ == "__main__":
    run()
