SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE email_verification_tokens (
  id BINARY(16) NOT NULL, user_id BINARY(16) NOT NULL, token_hash BINARY(32) NOT NULL,
  expires_at DATETIME(6) NOT NULL, consumed_at DATETIME(6) NULL,
  active_user_id BINARY(16) GENERATED ALWAYS AS (CASE WHEN consumed_at IS NULL THEN user_id ELSE NULL END) STORED,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY email_verify_token_unique (token_hash),
  UNIQUE KEY one_active_email_verification (active_user_id),
  CONSTRAINT email_verify_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_reset_tokens (
  id BINARY(16) NOT NULL, user_id BINARY(16) NOT NULL, token_hash BINARY(32) NOT NULL,
  expires_at DATETIME(6) NOT NULL, consumed_at DATETIME(6) NULL, requested_ip_hash BINARY(32) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY password_reset_token_unique (token_hash),
  KEY password_reset_expiry_idx (consumed_at, expires_at),
  CONSTRAINT password_reset_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE authentication_events (
  id BINARY(16) NOT NULL, email_hash BINARY(32) NOT NULL, user_id BINARY(16) NULL,
  outcome VARCHAR(24) NOT NULL, ip_hash BINARY(32) NULL, user_agent_hash BINARY(32) NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), KEY authentication_throttle_idx (email_hash, occurred_at),
  CONSTRAINT auth_event_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT auth_event_outcome_check CHECK (outcome IN ('success','invalid','locked','mfa_required','mfa_failed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_mfa_methods (
  id BINARY(16) NOT NULL, user_id BINARY(16) NOT NULL, method VARCHAR(16) NOT NULL,
  label VARCHAR(80) NOT NULL, encrypted_secret VARBINARY(1024) NULL, credential_data JSON NULL,
  enabled_at DATETIME(6) NULL, last_used_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), KEY user_mfa_methods_user_idx (user_id, enabled_at),
  CONSTRAINT user_mfa_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT user_mfa_method_check CHECK (method IN ('totp','webauthn')),
  CONSTRAINT user_mfa_material_check CHECK (
    (method = 'totp' AND encrypted_secret IS NOT NULL AND credential_data IS NULL)
    OR (method = 'webauthn' AND credential_data IS NOT NULL AND encrypted_secret IS NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;