-- Account security: password recovery and two-step sign-in for staff, lost-link recovery and
-- email codes on new devices for clients (booking links) and partners (portal links).
-- Every secret is stored as a SHA-256 hash, never in clear (the TOTP seed excepted: it must be read to check codes).

-- Staff two-step sign-in (authenticator app, RFC 6238 TOTP) and backup codes.
ALTER TABLE users
  ADD COLUMN totp_secret     text,                       -- base32 seed; set at setup, kept only while enabled
  ADD COLUMN totp_enabled    boolean NOT NULL DEFAULT false,
  ADD COLUMN totp_last_step  bigint NOT NULL DEFAULT 0,  -- last accepted 30-second step, so a code is never accepted twice
  ADD COLUMN backup_codes    jsonb NOT NULL DEFAULT '[]'; -- sha256 hashes of unused one-time backup codes

-- "Forgot password" links (single use, short-lived).
CREATE TABLE password_resets (
  token_hash  text PRIMARY KEY,
  user_id     int NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  expires_at  timestamptz NOT NULL,
  used_at     timestamptz
);
CREATE INDEX password_resets_user ON password_resets (user_id);

-- Half-finished sign-ins waiting for the two-step code.
CREATE TABLE login_challenges (
  token_hash  text PRIMARY KEY,
  user_id     int NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at  timestamptz NOT NULL,
  attempts    int NOT NULL DEFAULT 0
);

-- Extra private links for a booking, sent by "find my booking". The original link keeps working.
CREATE TABLE booking_links (
  token_hash  text PRIMARY KEY,
  booking_id  int NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX booking_links_booking ON booking_links (booking_id);

-- 6-digit codes emailed when a booking or partner link is opened on a new device.
CREATE TABLE link_codes (
  id          serial PRIMARY KEY,
  kind        text NOT NULL CHECK (kind IN ('booking', 'portal')),
  subject_id  int NOT NULL,
  code_hash   text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  expires_at  timestamptz NOT NULL,
  attempts    int NOT NULL DEFAULT 0,
  used_at     timestamptz
);
CREATE INDEX link_codes_subject ON link_codes (kind, subject_id, created_at DESC);

-- Browsers that passed the email code (30 days).
CREATE TABLE trusted_devices (
  device_hash text NOT NULL,
  kind        text NOT NULL CHECK (kind IN ('booking', 'portal')),
  subject_id  int NOT NULL,
  expires_at  timestamptz NOT NULL,
  PRIMARY KEY (device_hash, kind, subject_id)
);