CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(249) NOT NULL,
  password VARCHAR(255) NOT NULL,
  username VARCHAR(100) DEFAULT NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 0,
  verified TINYINT UNSIGNED NOT NULL DEFAULT 0,
  resettable TINYINT UNSIGNED NOT NULL DEFAULT 1,
  roles_mask INT UNSIGNED NOT NULL DEFAULT 0,
  registered INT UNSIGNED NOT NULL,
  last_login INT UNSIGNED DEFAULT NULL,
  force_logout MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_uq (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_2fa (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  mechanism TINYINT UNSIGNED NOT NULL,
  seed VARCHAR(255) DEFAULT NULL,
  created_at INT UNSIGNED NOT NULL,
  expires_at INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_2fa_user_mechanism_uq (user_id, mechanism)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED DEFAULT NULL,
  event_at INT UNSIGNED NOT NULL,
  event_type VARCHAR(128) NOT NULL,
  admin_id INT UNSIGNED DEFAULT NULL,
  ip_address VARCHAR(49) DEFAULT NULL,
  user_agent TEXT DEFAULT NULL,
  details_json TEXT DEFAULT NULL,
  PRIMARY KEY (id),
  KEY users_audit_event_at_ix (event_at),
  KEY users_audit_user_event_at_ix (user_id, event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_confirmations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  email VARCHAR(249) NOT NULL,
  selector VARCHAR(16) NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_confirmations_selector_uq (selector),
  KEY users_confirmations_email_expires_ix (email, expires),
  KEY users_confirmations_user_ix (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_otps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  mechanism TINYINT UNSIGNED NOT NULL,
  single_factor TINYINT UNSIGNED NOT NULL DEFAULT 0,
  selector VARCHAR(24) NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires_at INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (id),
  KEY users_otps_user_mechanism_ix (user_id, mechanism),
  KEY users_otps_selector_user_ix (selector, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_remembered (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user INT UNSIGNED NOT NULL,
  selector VARCHAR(24) NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_remembered_selector_uq (selector),
  KEY users_remembered_user_ix (user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_resets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user INT UNSIGNED NOT NULL,
  selector VARCHAR(20) NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_resets_selector_uq (selector),
  KEY users_resets_user_expires_ix (user, expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users_throttling (
  bucket VARCHAR(44) NOT NULL,
  tokens FLOAT NOT NULL,
  replenished_at INT UNSIGNED NOT NULL,
  expires_at INT UNSIGNED NOT NULL,
  PRIMARY KEY (bucket),
  KEY users_throttling_expires_at_ix (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_user_roles (
  user_id INT UNSIGNED NOT NULL,
  role ENUM('admin','reviewer','viewer') NOT NULL DEFAULT 'reviewer',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id),
  CONSTRAINT fm_user_roles_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;