CREATE TABLE IF NOT EXISTS classes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  sort_order INT DEFAULT 0,
  status VARCHAR(50) DEFAULT 'ACTIVE'
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS referral_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referral_code_id INT NULL,
  referrer_user_id INT NULL,
  referred_user_id INT NULL,
  attribution_type VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referral_code_id) REFERENCES referral_codes(id) ON DELETE SET NULL,
  FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE(referred_user_id) -- Ensures a user is only referred once as the primary referral
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS otp_challenges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  identifier VARCHAR(255) NOT NULL,
  purpose VARCHAR(50) NOT NULL,
  otp_hash VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  attempts INT DEFAULT 0,
  consumed_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  actor_user_id INT NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id INT NOT NULL,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;