-- Death-threshold unlock process: heartbeat + votes + outbox (MySQL)

CREATE TABLE IF NOT EXISTS heartbeat_checks (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,

  -- Token stored hashed (raw token only in email link).
  token_hash CHAR(64) NOT NULL,

  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reminder_sent_at DATETIME NULL,
  responded_at DATETIME NULL,
  escalated_at DATETIME NULL,

  status ENUM('sent','responded','cancelled') NOT NULL DEFAULT 'sent',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_heartbeat_checks_token_hash (token_hash),
  KEY idx_heartbeat_checks_user_sent (user_id, sent_at),
  KEY idx_heartbeat_checks_status_sent (status, sent_at),
  CONSTRAINT fk_heartbeat_checks_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unlock_requests (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  capsule_id CHAR(36) NOT NULL,
  heartbeat_check_id CHAR(36) NOT NULL,

  threshold INT NOT NULL,
  status ENUM('open','cancelled','unlocked') NOT NULL DEFAULT 'open',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cancelled_at DATETIME NULL,
  unlocked_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_unlock_requests_capsule_heartbeat (capsule_id, heartbeat_check_id),
  KEY idx_unlock_requests_user_status (user_id, status),
  KEY idx_unlock_requests_capsule_status (capsule_id, status),
  CONSTRAINT fk_unlock_requests_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_unlock_requests_capsule FOREIGN KEY (capsule_id) REFERENCES capsules(id),
  CONSTRAINT fk_unlock_requests_heartbeat FOREIGN KEY (heartbeat_check_id) REFERENCES heartbeat_checks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unlock_votes (
  id CHAR(36) PRIMARY KEY,
  unlock_request_id CHAR(36) NOT NULL,

  -- The same list as capsule_recipients; we store email here for audit + direct lookup.
  recipient_email VARCHAR(320) NOT NULL,
  recipient_name VARCHAR(255) NULL,

  -- One-shot token for voting (hashed in DB).
  token_hash CHAR(64) NOT NULL,

  vote ENUM('alive','deceased') NULL,
  voted_at DATETIME NULL,
  auto_accepted TINYINT(1) NOT NULL DEFAULT 0,

  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reminder_sent_at DATETIME NULL,
  auto_accept_at DATETIME NOT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_unlock_votes_token_hash (token_hash),
  UNIQUE KEY uq_unlock_votes_request_email (unlock_request_id, recipient_email),
  KEY idx_unlock_votes_request_voted (unlock_request_id, voted_at),
  KEY idx_unlock_votes_auto_accept (auto_accept_at, voted_at),
  CONSTRAINT fk_unlock_votes_request FOREIGN KEY (unlock_request_id) REFERENCES unlock_requests(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_outbox (
  id CHAR(36) PRIMARY KEY,
  template VARCHAR(64) NOT NULL,
  to_email VARCHAR(320) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  payload_json JSON NOT NULL,

  status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  last_error TEXT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_email_outbox_status_next (status, next_attempt_at),
  KEY idx_email_outbox_to_created (to_email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

