-- Safe multi-school bootstrap for existing databases

CREATE TABLE IF NOT EXISTS schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  city VARCHAR(120) NULL,
  description TEXT 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, description, is_active)
SELECT 'Naim Frasheri', 'Bujanoc', 'Shkollë bazë për aktivizimin e platformës', 1
WHERE NOT EXISTS (SELECT 1 FROM schools WHERE name = 'Naim Frasheri');

-- Lidh administratorin ekzistues me shkollën e parë nëse ende nuk ka lidhje
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 = 'admin'
  AND NOT EXISTS (SELECT 1 FROM user_schools us WHERE us.user_id = u.id);

-- Nëse tabela users ka kolonën school_id dhe ajo ka vlera, mund t'i kopjosh manualisht më vonë.
