-- Add new columns to qp_setter_assignments: exam_subject_id (required), template_id (optional)
-- Safe to run multiple times due to conditional checks

-- exam_subject_id
ALTER TABLE qp_setter_assignments
  ADD COLUMN IF NOT EXISTS exam_subject_id INT NULL AFTER pattern_id;

-- template_id
ALTER TABLE qp_setter_assignments
  ADD COLUMN IF NOT EXISTS template_id INT NULL AFTER exam_subject_id;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_qps_exam_subject_id ON qp_setter_assignments (exam_subject_id);
CREATE INDEX IF NOT EXISTS idx_qps_template_id ON qp_setter_assignments (template_id);

-- Foreign keys (skip if already present); adjust constraint names if needed for your DB
ALTER TABLE qp_setter_assignments
  ADD CONSTRAINT fk_qps_exam_subject
    FOREIGN KEY (exam_subject_id) REFERENCES exam_subjects (exam_subject_id)
    ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE qp_setter_assignments
  ADD CONSTRAINT fk_qps_template
    FOREIGN KEY (template_id) REFERENCES question_pattern_templates (template_id)
    ON UPDATE CASCADE ON DELETE SET NULL;

-- Optional: If pattern_id is being deprecated, you may choose to drop later once migration is stable
-- ALTER TABLE qp_setter_assignments DROP COLUMN pattern_id;