-- Recipient access links + OTP challenges for scheduled unlock (MySQL)

CREATE TABLE IF NOT EXISTS capsule_recipient_links (
  id CHAR(36) PRIMARY KEY,
  capsule_id CHAR(36) NOT NULL,
  recipient_email VARCHAR(320) NOT NULL,

  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  delivered_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_capsule_recipient_links_token_hash (token_hash),
  KEY idx_capsule_recipient_links_capsule_email (capsule_id, recipient_email),
  KEY idx_capsule_recipient_links_expires_used (expires_at, used_at),
  KEY idx_capsule_recipient_links_delivered (delivered_at),
  CONSTRAINT fk_capsule_recipient_links_capsules FOREIGN KEY (capsule_id) REFERENCES capsules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS capsule_recipient_challenges (
  id CHAR(36) PRIMARY KEY,
  link_id CHAR(36) NOT NULL,

  code_hash TEXT NOT NULL,
  expires_at DATETIME NOT NULL,
  attempts INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  consumed_at DATETIME NULL,

  KEY idx_capsule_recipient_challenges_link_created (link_id, created_at),
  KEY idx_capsule_recipient_challenges_expires_consumed (expires_at, consumed_at),
  CONSTRAINT fk_capsule_recipient_challenges_link FOREIGN KEY (link_id) REFERENCES capsule_recipient_links(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

