-- ===========================================================================
-- Migration: OTP (one-time code) login
-- Date: 2026-07-30
--
-- Lets a user sign in with a one-time code sent to their email (SMTP) or phone
-- (EgoSMS) instead of a password. Codes are 6 digits, stored hashed with a
-- 10-minute TTL; one active code per user. users.phone is added so an SMS code
-- has somewhere to go. Idempotent.
-- ===========================================================================

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS phone VARCHAR(40) NULL AFTER email;

CREATE TABLE IF NOT EXISTS login_otps (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  code_hash  CHAR(64)     NOT NULL,           -- sha256 of the 6-digit code
  channel    ENUM('email','sms') NOT NULL DEFAULT 'email',
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at TIMESTAMP    NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_otp_user (user_id),
  KEY idx_otp_expires (expires_at),
  CONSTRAINT fk_otp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;