-- Safe migration for school assignments and active school filtering

CREATE TABLE IF NOT EXISTS schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  city VARCHAR(120) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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),
  KEY idx_user_schools_user (user_id),
  KEY idx_user_schools_school (school_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO schools (name, city, is_active)
SELECT 'Naim Frasheri', 'Bujanoc', 1
WHERE NOT EXISTS (SELECT 1 FROM schools WHERE name = 'Naim Frasheri');

INSERT INTO user_schools (user_id, school_id, role, is_primary, is_active)
SELECT u.id, s.id, CASE WHEN u.role='admin' THEN 'admin' ELSE 'professor' END, 1, 1
FROM users u
CROSS JOIN (SELECT id FROM schools ORDER BY id ASC LIMIT 1) s
WHERE u.username IN ('admin','superadmin')
  AND NOT EXISTS (SELECT 1 FROM user_schools us WHERE us.user_id = u.id AND us.school_id = s.id);
