CREATE TABLE IF NOT EXISTS user_schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  school_id INT NOT NULL,
  role ENUM('admin','professor') NOT NULL DEFAULT 'professor',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_school (user_id, school_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

INSERT INTO user_schools(user_id, school_id, role, is_primary, is_active)
SELECT u.id, u.school_id, CASE WHEN u.role='admin' THEN 'admin' ELSE 'professor' END, 1, 1
FROM users u
WHERE u.school_id IS NOT NULL AND u.role IN ('admin','professor')
  AND NOT EXISTS (SELECT 1 FROM user_schools us WHERE us.user_id = u.id AND us.school_id = u.school_id);
