USE cashless_db;

CREATE TABLE IF NOT EXISTS contact_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  purpose ENUM('registration','contact_change') NOT NULL DEFAULT 'registration',
  target_phone VARCHAR(30) DEFAULT NULL,
  target_email VARCHAR(120) DEFAULT NULL,
  otp_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  attempt_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
  request_ip VARCHAR(45) NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_verification_user_created (user_id, created_at),
  INDEX idx_verification_ip_created (request_ip, created_at)
) ENGINE=InnoDB;