-- Migration 003: Enforce Student-Coordinator-ASM hierarchy consistency and add Student class foreign key

-- 1. Add composite unique index on coordinator_profiles (user_id, asm_id)
-- user_id is the primary key - this composite unique index allows student_profiles to reference both columns together
ALTER TABLE coordinator_profiles
  ADD UNIQUE KEY uq_coordinator_asm (user_id, asm_id);

-- 2. Add composite foreign key from student_profiles (coordinator_id, asm_id) to coordinator_profiles (user_id, asm_id)
-- Ensures a Student cannot be assigned to an ASM different from their Coordinator's ASM.
-- ON DELETE RESTRICT prevents deleting a coordinator if students are actively assigned.
ALTER TABLE student_profiles
  ADD CONSTRAINT fk_student_coordinator_asm
  FOREIGN KEY (coordinator_id, asm_id)
  REFERENCES coordinator_profiles (user_id, asm_id)
  ON DELETE RESTRICT;

-- 3. Add foreign key from student_profiles (class_id) to classes (id)
-- class_id is nullable - ON DELETE SET NULL allows students to remain active without an assigned class.
ALTER TABLE student_profiles
  ADD CONSTRAINT fk_student_class
  FOREIGN KEY (class_id)
  REFERENCES classes (id)
  ON DELETE SET NULL;