-- 2026_02_subject_advisors.sql
-- Asesor por materia (default) para asignación automática EE/IA

CREATE TABLE IF NOT EXISTS subject_advisors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  academic_year_id INT NOT NULL,
  subject_id INT NOT NULL,
  advisor_user_id INT NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_year_subject (academic_year_id, subject_id),
  INDEX idx_advisor (advisor_user_id),
  FOREIGN KEY (academic_year_id) REFERENCES academic_years(id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id),
  FOREIGN KEY (advisor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;