-- Run this in phpMyAdmin (SQL tab) on your MySQL database.
-- Adds a `qr_code` column to students, teachers, and school_staff
-- to store each person's QR token. Safe to run multiple times.

-- Students
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'students'
    AND COLUMN_NAME = 'qr_code'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE students ADD COLUMN qr_code VARCHAR(255) NULL DEFAULT NULL, ADD INDEX idx_students_qr_code (qr_code)',
  'SELECT "students.qr_code already exists" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Teachers
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'teachers'
    AND COLUMN_NAME = 'qr_code'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE teachers ADD COLUMN qr_code VARCHAR(255) NULL DEFAULT NULL, ADD INDEX idx_teachers_qr_code (qr_code)',
  'SELECT "teachers.qr_code already exists" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- School Staff
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'school_staff'
    AND COLUMN_NAME = 'qr_code'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE school_staff ADD COLUMN qr_code VARCHAR(255) NULL DEFAULT NULL, ADD INDEX idx_school_staff_qr_code (qr_code)',
  'SELECT "school_staff.qr_code already exists" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;