

-- ----------------------------------------------------------------
-- 2. UNIQUE CONSTRAINTS FOR RELIABLE UPSERTS
-- ----------------------------------------------------------------
-- The submit endpoint upserts one current submission and one current
-- grade per (assignment, student). Without these constraints, resubmits
-- created duplicate rows. We de-duplicate first, then add constraints.

-- 2a. Remove duplicate submissions, keeping the most recent per pair.
DELETE FROM submissions s
USING submissions s2
WHERE s.assignment_id = s2.assignment_id
  AND s.student_id = s2.student_id
  AND s.id < s2.id;

-- 2c. Remove duplicate grades, keeping the most recent per pair.
DELETE FROM grades g
USING grades g2
WHERE g.assignment_id = g2.assignment_id
  AND g.student_id = g2.student_id
  AND g.id < g2.id;

-- ----------------------------------------------------------------
-- 3. SPELLING SUPPORT COLUMN
-- ----------------------------------------------------------------
-- Stores the weekly 20-word list (and optional meanings) on the lesson.
-- Other subjects leave this NULL.
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS word_list JSONB;

-- ----------------------------------------------------------------
-- DONE
-- ----------------------------------------------------------------
-- Verify with:
--   SELECT email, role FROM users WHERE email = 'parent@jnm.com';
--   SELECT conname FROM pg_constraint WHERE conname LIKE '%assignment_student_key';
