BEGIN;

-- V34 shared curriculum/activity tables remain the storage foundation.
\i v34-launch-sync.sql

-- Explicit family ownership. No parent route may fall back to unrestricted
-- access when the legacy students table has no owner column.
CREATE TABLE IF NOT EXISTS parent_student_links (
  parent_user_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  relationship TEXT NOT NULL DEFAULT 'parent_teacher',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (parent_user_id, student_id)
);

CREATE INDEX IF NOT EXISTS idx_parent_student_links_student
  ON parent_student_links(student_id);

-- Teacher dashboard preferences persist across browsers after backend setup.
CREATE TABLE IF NOT EXISTS teacher_profiles (
  parent_user_id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL DEFAULT 'Parent / Teacher',
  avatar_data TEXT NOT NULL DEFAULT '',
  timezone TEXT NOT NULL DEFAULT 'America/Los_Angeles',
  preferences JSONB NOT NULL DEFAULT '{}'::jsonb,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Writing is normally auto-graded. These columns preserve the rubric result
-- and reserve concern flags for repeated patterns rather than every response.
ALTER TABLE curriculum_submissions
  ADD COLUMN IF NOT EXISTS auto_graded BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS writing_score NUMERIC(6,2),
  ADD COLUMN IF NOT EXISTS concern_flag BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS concern_reason TEXT NOT NULL DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_curriculum_submissions_concerns
  ON curriculum_submissions(student_id, concern_flag, submitted_at DESC);

-- Existing owner columns are copied into the explicit link table when present.
DO $$
DECLARE owner_column TEXT;
BEGIN
  SELECT column_name INTO owner_column
  FROM information_schema.columns
  WHERE table_schema='public' AND table_name='students'
    AND column_name IN ('parent_user_id','parent_id','user_id','account_id')
  ORDER BY CASE column_name WHEN 'parent_user_id' THEN 1 WHEN 'parent_id' THEN 2 WHEN 'user_id' THEN 3 ELSE 4 END
  LIMIT 1;
  IF owner_column IS NOT NULL THEN
    EXECUTE format(
      'INSERT INTO parent_student_links (parent_user_id, student_id) SELECT %I, id FROM students WHERE %I IS NOT NULL ON CONFLICT DO NOTHING',
      owner_column, owner_column
    );
  END IF;
END $$;

-- Source-only duplicate report. Cleanup is performed by the audited Node tool
-- so no student is removed merely because two children share a name.
CREATE OR REPLACE VIEW student_duplicate_candidates AS
SELECT lower(trim(first_name)) AS first_name_key,
       lower(trim(last_name)) AS last_name_key,
       grade_level,
       array_agg(id ORDER BY id) AS student_ids,
       count(*) AS duplicate_count
FROM students
GROUP BY lower(trim(first_name)), lower(trim(last_name)), grade_level
HAVING count(*) > 1;

COMMIT;