-- ============================================================
-- PIEDTB — OTP Migration
-- Run this if you already have the database set up:
--   mysql -u root -p piedtb < migrate_otp.sql
-- ============================================================

USE piedtb;

-- Add OTP columns (safe — won't fail if they already exist)
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS otp_code       VARCHAR(6)  NULL     AFTER is_active,
  ADD COLUMN IF NOT EXISTS otp_expires_at DATETIME    NULL     AFTER otp_code,
  ADD COLUMN IF NOT EXISTS otp_attempts   TINYINT(1)  NOT NULL DEFAULT 0 AFTER otp_expires_at;

-- Optional: add an index on otp_expires_at for cleanup queries
CREATE INDEX IF NOT EXISTS idx_otp_expires ON users (otp_expires_at);

SELECT 'OTP migration complete.' AS status;
