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
);

ALTER TABLE users MODIFY role ENUM('super_admin','admin','professor') NOT NULL DEFAULT 'professor';
ALTER TABLE users ADD COLUMN school_id INT NULL AFTER role;
ALTER TABLE users ADD CONSTRAINT fk_users_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL;

ALTER TABLE academic_years ADD COLUMN school_id INT NULL AFTER id;
ALTER TABLE academic_years ADD CONSTRAINT fk_years_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE;

ALTER TABLE classes ADD COLUMN school_id INT NULL AFTER id;
ALTER TABLE classes ADD CONSTRAINT fk_classes_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE;

INSERT INTO schools(name, city, description)
SELECT 'Shkolla Shembull', 'Bujanoc', 'Shkollë bazë për aktivizimin e platformës'
WHERE NOT EXISTS (SELECT 1 FROM schools WHERE name = 'Shkolla Shembull');

UPDATE users SET school_id = (SELECT id FROM schools WHERE name = 'Shkolla Shembull' LIMIT 1) WHERE role IN ('admin','professor') AND school_id IS NULL;

INSERT INTO users(full_name, username, password_hash, role, school_id)
SELECT 'Super Admin', 'superadmin', '$2y$12$7aCGf6sCDyL.1oLu//kvWuOpO7XjxNI1ZgQM/hFpoBXTYCNn.p9Ra', 'super_admin', NULL
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'superadmin');
