-- Run this BEFORE applying migration 20260817000000_case_insensitive_user_email.
--
-- Finds User rows whose emails are identical except for casing (e.g.
-- "Name@x.de" and "name@x.de"). The migration creates a unique index on
-- lower(email) and will fail to apply if any such duplicates exist.
--
-- Usage:
--   npx prisma db execute --file scripts/check-duplicate-user-emails.sql --schema prisma/schema.prisma
-- or paste directly into psql / your DB console.
--
-- If this returns any rows, resolve them manually first — e.g. decide which
-- account is canonical, merge or reassign its CourseBooking/UserTraining
-- rows to that account, then delete or rename the duplicate — before
-- applying the migration.

SELECT
  LOWER(email) AS normalized_email,
  array_agg(id) AS user_ids,
  array_agg(email) AS original_emails,
  count(*) AS duplicate_count
FROM "User"
GROUP BY LOWER(email)
HAVING count(*) > 1;