-- class_registry had no uniqueness constraint at all on (school_id,
-- class_name), and the app's own Edit flow had a bug (the API response
-- was missing the "rowId" field every other module's frontend code
-- expects, so parseInt(undefined) produced NaN, which is falsy — meaning
-- every "Update Class" click silently fell through to INSERT instead of
-- UPDATE). The frontend bug is fixed separately; this migration cleans up
-- the duplicate rows it already produced, and adds a real constraint so
-- the same class of bug can never silently create duplicates again,
-- resurfacing some other way.
--
-- class_registry has row-level security scoped to app.current_school_id
-- (set only inside the app's own withTenant() helper), so a plain
-- cross-school DELETE run from a migration — with that session variable
-- never set — is silently filtered down to zero rows by RLS itself: no
-- error, it just deletes nothing, which is exactly why the very next
-- statement (the unique index) failed on leftover duplicates. Loop over
-- every school explicitly, setting that session variable each time so
-- each school's own rows are actually visible for its own dedup pass.
-- (schools itself carries no per-school RLS, so it's safe to read here
-- to drive the loop.)
DO $$
DECLARE
  sid INTEGER;
BEGIN
  FOR sid IN SELECT id FROM schools LOOP
    PERFORM set_config('app.current_school_id', sid::text, true);

    -- For each (normalized class name) group with more than one row for
    -- THIS school, keep exactly one — preferring, in order: a row with an
    -- assigned teacher, then a row with a level set, then the lowest id
    -- (oldest/first-created) — and delete the rest. No other table has a
    -- foreign key into class_registry.id, so deleting the losers is safe.
    DELETE FROM class_registry cr
    USING (
      SELECT id,
             ROW_NUMBER() OVER (
               PARTITION BY lower(trim(class_name))
               ORDER BY (assigned_teacher IS NOT NULL AND assigned_teacher != '') DESC,
                        (level IS NOT NULL AND level != '') DESC,
                        id ASC
             ) AS rn
      FROM class_registry
      WHERE school_id = sid
    ) ranked
    WHERE cr.id = ranked.id AND ranked.rn > 1;
  END LOOP;
END $$;

-- Now that duplicates are gone (this is DDL, not subject to RLS, so it
-- correctly sees/enforces across every school regardless of session
-- state). Case/whitespace-insensitive ("Nursery 1" and "nursery 1 " both
-- collide) so near-identical re-entries are also caught, while genuinely
-- different names (e.g. "KG1" vs "KG 1") are left alone — those are a
-- data-entry question for the school, not something safe to silently merge.
CREATE UNIQUE INDEX IF NOT EXISTS idx_class_registry_unique_name
  ON class_registry (school_id, lower(trim(class_name)));