-- QR + phrase secrète access for scheduled capsules (MySQL)

ALTER TABLE capsules
  ADD COLUMN delivery_method ENUM('email','qr_secret') NOT NULL DEFAULT 'email' AFTER unlock_threshold,
  ADD KEY idx_capsules_delivery_method (delivery_method);

CREATE TABLE IF NOT EXISTS capsule_qr_access (
  id CHAR(36) PRIMARY KEY,
  capsule_id CHAR(36) NOT NULL,

  token_hash CHAR(64) NOT NULL,
  verifier_hash CHAR(64) NOT NULL,
  kdf_salt_b64 VARCHAR(128) NOT NULL,
  kdf_ops_limit INT NOT NULL,
  kdf_mem_limit INT NOT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  revealed_at DATETIME NULL,
  confirmed_at DATETIME NULL,
  disabled_at DATETIME NULL,
  failed_attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,

  UNIQUE KEY uq_capsule_qr_access_capsule (capsule_id),
  UNIQUE KEY uq_capsule_qr_access_token_hash (token_hash),
  KEY idx_capsule_qr_access_locked (locked_until),
  CONSTRAINT fk_capsule_qr_access_capsules FOREIGN KEY (capsule_id) REFERENCES capsules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
