-- OTP codes for phone number verification on payer accounts.

CREATE TABLE IF NOT EXISTS phone_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payer_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(30) NOT NULL,
  code_hash VARCHAR(128) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payer_id) REFERENCES payer_accounts(id) ON DELETE CASCADE,
  INDEX idx_pv_payer (payer_id),
  INDEX idx_pv_expires (expires_at)
)
