-- INNENT SCHOOL360 | Migration 002: Onboarding (spec sec 94)
ALTER TABLE users ADD COLUMN email_verified_at DATETIME NULL AFTER email;

CREATE TABLE onboarding_steps (
  school_id INT UNSIGNED NOT NULL,
  step_code VARCHAR(30) NOT NULL,   -- profile, academic, classes, subjects, users, fees, import, communication, payments, test, launch
  status ENUM('pending','done','skipped') NOT NULL DEFAULT 'pending',
  completed_at DATETIME NULL,
  PRIMARY KEY (school_id, step_code),
  CONSTRAINT fk_ob_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE email_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,   -- sha256 of emailed token
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  CONSTRAINT fk_ev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;