-- Password reset workflow support

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id            CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id       CHAR(36)     NOT NULL,
  tenant_id     CHAR(36)     NOT NULL,
  token_hash    VARCHAR(128) NOT NULL,
  expires_at    DATETIME     NOT NULL,
  consumed_at   DATETIME,
  created_at    DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_password_reset_token_hash (token_hash),
  INDEX idx_password_reset_user (user_id),
  INDEX idx_password_reset_expires (expires_at),
  CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_password_reset_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Remove stale rows from historical runs.
DELETE FROM password_reset_tokens WHERE expires_at < NOW() OR consumed_at IS NOT NULL;