BEGIN;

DROP INDEX IF EXISTS iam.idx_users_tenant_email;
DROP INDEX IF EXISTS iam.idx_users_tenant_username;

CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_users_email_active
    ON iam.users ((LOWER(email)))
    WHERE deleted_at IS NULL
      AND email IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_users_username_active
    ON iam.users ((LOWER(username)))
    WHERE deleted_at IS NULL
      AND username IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_users_phone_active
    ON iam.users ((LOWER(phone)))
    WHERE deleted_at IS NULL
      AND phone IS NOT NULL
      AND BTRIM(phone) <> '';

COMMIT;