-- Social sign-in: the one-time "state" for each trip to a provider, and pending "is this you?" links.
CREATE TABLE oauth_states (
  state_hash CHAR(64) NOT NULL PRIMARY KEY,
  session_id CHAR(64) NOT NULL,
  provider   VARCHAR(20) NOT NULL,
  intent     ENUM('login','link') NOT NULL,
  user_id    INT UNSIGNED NULL,
  expires_at DATETIME NOT NULL,
  KEY idx_expires (expires_at)
) ENGINE=InnoDB;

CREATE TABLE social_link_requests (
  token_hash       CHAR(64) NOT NULL PRIMARY KEY,
  user_id          INT UNSIGNED NOT NULL,
  provider         VARCHAR(20)  NOT NULL,
  provider_user_id VARCHAR(100) NOT NULL,
  provider_email   VARCHAR(190) NULL,
  provider_name    VARCHAR(150) NULL,
  expires_at       DATETIME NOT NULL,
  KEY idx_expires (expires_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO migrations (name) VALUES ('010_social_signin');
