-- ================================================================
-- JNM Home Education Program — Phase 1 Functionality Migration
-- ================================================================
-- Run:  psql -U homeschool_user -d jnm_homeschool_db -f migration-phase1.sql
--
-- This migration is SAFE and NON-DESTRUCTIVE:
--   - Does NOT drop any tables
--   - Does NOT delete any existing students, grades, submissions, courses
--   - Uses IF NOT EXISTS / ON CONFLICT guards so it can run more than once
--
-- It does three things:
--   1. Creates a real local parent account (parent@jnm.com / parent123)
--   2. Adds unique constraints needed for reliable submit/grade upserts
--   3. Adds a word_list column to lessons for the spelling curriculum
-- ================================================================

-- ----------------------------------------------------------------
-- 1. REAL PARENT ACCOUNT
-- ----------------------------------------------------------------
-- We insert the account row here WITHOUT a usable password hash.
-- The backend (server.js) re-hashes "parent123" with bcrypt on startup
-- via ensureParentAccount(), which guarantees the stored hash always
-- matches the running bcrypt version. This avoids embedding a brittle
-- pre-computed hash in SQL.
--
-- If the account already exists, we leave it untouched here; the backend
-- will set/refresh the password hash on startup.
INSERT INTO users (email, password_hash, first_name, last_name, role)
VALUES (
  'parent@jnm.com',
  'PENDING_BACKEND_HASH',
  'JNM',
  'Parent',
  'parent'
)
ON CONFLICT (email) DO NOTHING;

-- ----------------------------------------------------------------
-- 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;

-- 2b. Add unique constraint on submissions (assignment_id, student_id).
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'submissions_assignment_student_key'
  ) THEN
    ALTER TABLE submissions
      ADD CONSTRAINT submissions_assignment_student_key
      UNIQUE (assignment_id, student_id);
  END IF;
END $$;

-- 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;

-- 2d. Add unique constraint on grades (assignment_id, student_id).
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'grades_assignment_student_key'
  ) THEN
    ALTER TABLE grades
      ADD CONSTRAINT grades_assignment_student_key
      UNIQUE (assignment_id, student_id);
  END IF;
END $$;

-- ----------------------------------------------------------------
-- 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';