-- Enforce case-insensitive genre uniqueness, matching `authors` and `tags`
-- (both got `UNIQUE (lower(name))` in 0009). `genres` only had a case-SENSITIVE
-- `name UNIQUE`, but the crawler's `sync_genres` treats genres as
-- case-insensitive (`WHERE lower(name) = lower($1)`) and can INSERT a new row
-- from a source string. Under a race (or across ticks) two different-cased
-- strings — "Isekai" vs "isekai" — could both miss the pre-check and both
-- insert, since the exact-name unique doesn't collide. That leaves duplicate
-- genre rows for one logical genre.

-- Heal any pre-existing case-variant duplicates before adding the index, so
-- `sqlx::migrate!` can't crash on a dirty table (mirrors 0031's pre-dedup).
-- Canonical = lowest id per lower(name).

-- 1. Ensure every manga linked to any case-variant also has the canonical link.
--    INSERT ... ON CONFLICT DO NOTHING is collision-proof: SELECT DISTINCT
--    collapses multiple variants of one manga to a single canonical row, and the
--    ON CONFLICT absorbs a canonical link that already exists. (A prior
--    UPDATE-repoint could set two non-canonical rows of the SAME manga to the
--    same canonical id in a single statement and violate the manga_genres PK,
--    rolling the migration back and wedging startup.)
INSERT INTO manga_genres (manga_id, genre_id)
SELECT DISTINCT mg.manga_id, k.keep_id
  FROM manga_genres mg
  JOIN genres g ON mg.genre_id = g.id
  JOIN (SELECT lower(name) AS lname, (array_agg(id ORDER BY id))[1] AS keep_id
          FROM genres GROUP BY lower(name)) k
    ON lower(g.name) = k.lname
 WHERE g.id <> k.keep_id
ON CONFLICT (manga_id, genre_id) DO NOTHING;

-- 2. Every non-canonical link now has a canonical sibling — drop the variants.
DELETE FROM manga_genres mg
 USING genres g,
       (SELECT lower(name) AS lname, (array_agg(id ORDER BY id))[1] AS keep_id
          FROM genres GROUP BY lower(name)) k
 WHERE mg.genre_id = g.id AND lower(g.name) = k.lname AND g.id <> k.keep_id;

-- Remove the now-orphaned duplicate genre rows.
DELETE FROM genres g
 USING (SELECT lower(name) AS lname, (array_agg(id ORDER BY id))[1] AS keep_id
          FROM genres GROUP BY lower(name)) k
 WHERE lower(g.name) = k.lname AND g.id <> k.keep_id;

CREATE UNIQUE INDEX genres_name_lower_uniq ON genres (lower(name));