CREATE TABLE IF NOT EXISTS rekognition_otp_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NOT NULL,
  otp_code VARCHAR(12) NOT NULL,
  otp_ref VARCHAR(16) NOT NULL,
  otp_hash VARCHAR(255) NOT NULL,
  status ENUM('sent', 'verified', 'expired', 'failed') NOT NULL DEFAULT 'sent',
  request_ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  verified_at TIMESTAMP NULL,
  expires_at TIMESTAMP NOT NULL,
  KEY idx_rekognition_otp_phone_status (phone, status),
  KEY idx_rekognition_otp_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_access_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NULL,
  event_type VARCHAR(64) NOT NULL,
  otp_verification_id BIGINT UNSIGNED NULL,
  request_ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  details JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rekognition_access_logs_phone (phone),
  KEY idx_rekognition_access_logs_event_type (event_type),
  KEY idx_rekognition_access_logs_created_at (created_at),
  CONSTRAINT fk_rekognition_access_logs_otp
    FOREIGN KEY (otp_verification_id) REFERENCES rekognition_otp_verifications(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;