-- =============================================
-- Adds qr_token, emergency_contact, verified, qr_generated_at, last_updated
-- to students, teachers, school_staff. Run in phpMyAdmin SQL tab.
-- Safe re-run: uses IF NOT EXISTS.
-- =============================================

ALTER TABLE `students`
  ADD COLUMN IF NOT EXISTS `qr_token` VARCHAR(255) NULL UNIQUE AFTER `disability`,
  ADD COLUMN IF NOT EXISTS `emergency_contact` VARCHAR(30) NULL AFTER `guardian_phone`,
  ADD COLUMN IF NOT EXISTS `verified` TINYINT(1) NOT NULL DEFAULT 1 AFTER `emergency_contact`,
  ADD COLUMN IF NOT EXISTS `qr_generated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS `last_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE `teachers`
  ADD COLUMN IF NOT EXISTS `qr_token` VARCHAR(255) NULL UNIQUE AFTER `hobbies`,
  ADD COLUMN IF NOT EXISTS `emergency_contact` VARCHAR(30) NULL AFTER `phone`,
  ADD COLUMN IF NOT EXISTS `verified` TINYINT(1) NOT NULL DEFAULT 1 AFTER `emergency_contact`,
  ADD COLUMN IF NOT EXISTS `qr_generated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS `last_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE `school_staff`
  ADD COLUMN IF NOT EXISTS `qr_token` VARCHAR(255) NULL UNIQUE AFTER `status`,
  ADD COLUMN IF NOT EXISTS `emergency_contact` VARCHAR(30) NULL AFTER `phone`,
  ADD COLUMN IF NOT EXISTS `verified` TINYINT(1) NOT NULL DEFAULT 1 AFTER `emergency_contact`,
  ADD COLUMN IF NOT EXISTS `qr_generated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS `last_updated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Indexes (ignore "duplicate key name" if re-running)
CREATE INDEX idx_students_qr_token ON `students`(`qr_token`);
CREATE INDEX idx_teachers_qr_token ON `teachers`(`qr_token`);
CREATE INDEX idx_staff_qr_token ON `school_staff`(`qr_token`);