-- NB: This is a manually squashed view of all the CREATE and ALTER statements,
-- with comments attached to the fields for centralized documentation.

-- noinspection SqlDialectInspectionForFile
-- noinspection SqlNoDataSourceInspectionForFile
-- noinspection SqlResolveForFile

CREATE TABLE users (
  -- Not used for anything except replication.
  id BIGINT(20) NOT NULL AUTO_INCREMENT,

  -- Visible "primary key"
  token VARCHAR(32) NOT NULL,

  -- This is TRUE for accounts that were provisioned automatically (eg. eager stripe
  -- checkout flow), and who haven't completed onboarding (eg. finishing with billing).
  -- Once the user finishes setup, this is set to FALSE.
  -- Existing users and normal signup flow always sets this to FALSE.
  -- If enough time has elapsed for an incomplete `is_temporary` user, such user records
  -- might be possible to delete as we assume the user abandoned sign up long ago.
  is_temporary BOOLEAN NOT NULL DEFAULT false,

  -- Username is a lookup key; display_name allows the user to add custom case.
  username VARCHAR(20) NOT NULL,
  display_name VARCHAR(20) NOT NULL,

  -- If the username the user currently has was automatically generated by our
  -- system rather than manually entered. If the user selects the username we
  -- made for them, we still leave this as true.
  username_is_generated BOOLEAN NOT NULL DEFAULT false,

  -- Users with new randomly generated usernames have this flag set to true.
  -- If a user then changes their username from the default, we set this to false.
  -- Or if a user elects that the random username we generated for them is fine,
  -- we also set this to false.
  username_is_not_customized BOOLEAN NOT NULL DEFAULT false,

  -- Set by the user at sign up:
  --  - When email + password signup, this is the email provided.
  --  - When Google SSO, this is the email provided by Google.
  --  - When "eager" checkout flow, this is synthetically generated and
  --    updated by the user later.
  email_address VARCHAR(255) NOT NULL,

  -- Email address is confirmed by us.
  -- We do not set this on 3rd party (ie. Google) "email confirmation", only our own.
  email_confirmed BOOLEAN NOT NULL DEFAULT false,

  -- Email address is confirmed by Google.
  email_confirmed_by_google BOOLEAN NOT NULL DEFAULT false,

  -- We created a user account provisionally, and the email address is not yet real.
  -- This happens during "eager" checkout flow before users create accounts - we create
  -- a user account for them with a fake email address (since this is a unique key).
  email_is_synthetic BOOLEAN NOT NULL DEFAULT false,

  -- The role assigned to the user confers permissions.
  user_role_slug VARCHAR(16) NOT NULL,

  -- An optional comma separated list of flags for user accounts
  -- If a user has a flag, then they're enabled for that given feature.
  -- There are better ways to do this, but this hack will do for now.
  -- This should replace `can_access_studio`.
  --
  -- Possible flag values (multiple is okay) :
  --   - "explore_media"
  --   - "studio"
  --   - "video_style_transfer"
  --   - "sd_wl" (seedance whitelist)
  maybe_feature_flags VARCHAR(255) DEFAULT NULL,

  -- DEPRECATED
  -- TODO(bt, 2024-03-05): Remove after new flags are in place.
  -- Rollout flag for Storyteller Studio
  -- Default to false until rollout is 100%, then remove the column.
  can_access_studio BOOLEAN NOT NULL DEFAULT false,

  -- The user is predominantly an API user, eg. "AI streamer" on Twitch
  -- This flag serves both an analytics purpose (to disentangle from metrics)
  -- as well as a permissions feature flag for API users.
  is_api_user BOOLEAN NOT NULL DEFAULT false,

  -- ========== PREMIUM FEATURES ==========

  -- If the user has a Stripe subscription (or has had one), we link it to the user here.
  -- Will never exceed 255 characters: https://groups.google.com/a/lists.stripe.com/g/api-discuss/c/1F5Wb4HRnNQ
  maybe_stripe_customer_id VARCHAR(255) DEFAULT NULL,

  -- If a user is a known-good contributor, we can assign them premium service for free.
  -- Internally, our system statically maps keys to zero or more premium products.
  -- The business logic can live in the plans code.
  maybe_loyalty_program_key VARCHAR(32) DEFAULT NULL,

  -- ========== CREDENTIALS ==========

  -- Bcrypt password hash. Granted, there are newer methods:
  -- https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html
  -- Passwords may be a max of 64 characters per BCrypt.
  password_hash BINARY(60) NOT NULL,

  -- Incremented with every update to the password.
  password_version INT UNSIGNED NOT NULL DEFAULT 0,

  -- Users signing up from SSO do not have a password at time of account creation.
  -- Users signing up with eager stripe checkout flow do not have a password at time of creation
  is_without_password BOOLEAN NOT NULL DEFAULT false,

  -- ========== ABUSE TRACKING ==========

  -- For abuse tracking.
  -- Wide enough for IPv4/6
  ip_address_creation VARCHAR(40) NOT NULL,
  ip_address_last_login VARCHAR(40) NOT NULL,
  ip_address_last_update VARCHAR(40) NOT NULL,

  -- ========== DISPLAY / PROFILE ==========

  -- The "avatar" image is a media file of type image that serves as a
  -- small avatar or profile picture icon.
  maybe_avatar_media_file_token VARCHAR(32) DEFAULT NULL,

  -- The "cover" image is a media file of type image that covers the
  -- top of the page.
  maybe_cover_media_file_token VARCHAR(32) DEFAULT NULL,

  -- Gravatar image hashes are precomputed.
  email_gravatar_hash CHAR(32) NOT NULL,

  -- The profile of the user in markdown (user editable).
  profile_markdown TEXT NOT NULL,

  -- Generated HTML (not user-editable).
  profile_rendered_html TEXT NOT NULL,

  -- An uploaded avatar. Public hash in our bucket.
  -- avatar_public_bucket_hash CHAR(32) DEFAULT NULL,

  -- ========== SOCIAL MEDIA ==========

  -- Social media usernames
  -- These are not confirmed. We'll need to build an OAuth system to handle that.
  discord_username VARCHAR(36) DEFAULT NULL,
  twitter_username VARCHAR(36) DEFAULT NULL,
  twitch_username VARCHAR(36) DEFAULT NULL,
  patreon_username VARCHAR(36) DEFAULT NULL,
  github_username VARCHAR(36) DEFAULT NULL,
  cashapp_username VARCHAR(36) DEFAULT NULL,
  website_url VARCHAR(255) DEFAULT NULL,

  -- ========== USER PREFERENCES ==========

  -- If the user doesn't want to use gravatar and doesn't have an uploaded avatar.
  disable_gravatar BOOLEAN NOT NULL DEFAULT false,

  -- (THIS MIGHT NOT BE USED)
  -- NB: DO NOT SORT!
  -- THIS MUST MATCH THE RESPECTIVE JOBS TABLE.
  -- Visibility preference used at TTS inference time (which can be changed on the result itself).
  -- Moderators will still see them.
  preferred_tts_result_visibility ENUM(
    'public',
    'hidden',
    'private'
  ) NOT NULL DEFAULT 'public',

  -- (THIS MIGHT NOT BE USED)
  -- NB: DO NOT SORT!
  -- THIS MUST MATCH THE RESPECTIVE JOBS TABLE.
  -- Visibility preference used at TTS inference time (which can be changed on the result itself).
  -- Moderators will still see them.
  preferred_w2l_result_visibility ENUM(
    'public',
    'hidden',
    'private'
  ) NOT NULL DEFAULT 'public',

  -- Auto play preferences
  -- Tri-state because we want to potentially change the default behavior.
  auto_play_audio_preference BOOLEAN DEFAULT NULL,
  auto_play_video_preference BOOLEAN DEFAULT NULL,

  -- Favorite TTS voice to use by default
  -- maybe_preferred_tts_model_token VARCHAR(32) DEFAULT NULL,

  -- Favorite W2L model to use by default
  -- maybe_preferred_w2l_template_token VARCHAR(32) DEFAULT NULL,

  -- Settings
  -- DO NOT REORDER.
  -- dark_mode_preference ENUM(
  --   'light-mode',
  --   'dark-mode',
  --   'use-clock'
  --  ) NOT NULL DEFAULT 'light-mode',

  -- ========== STATS ==========

  -- For tracking stats.
  -- The "cached" values are updated by a background job.
  -- cached_tts_rendered_counter INT(10) NOT NULL DEFAULT 0,
  -- cached_w2l_rendered_counter INT(10) NOT NULL DEFAULT 0,

  -- ========== TRACKING ==========

  -- Where the user signed up from
  --
  -- ArtCraft Values:
  --  - "artcraft"         - (deprecated)
  --  - "artcraft_app"     - Signup with the ArtCraft app
  --  - "artcraft_get_web" - Signup with ArtCraft website signup flow
  --  - "artcraft_get_s"   - Signup with ArtCraft website Stripe Checkout Flow
  --  - "artcraft_ai_s"    - Signup with Artcraft.ai website Stripe checkout flow
  --  - "artcraft_get_web" - Signup with ArtCraft.ai website signup flow
  --
  -- Other Sources:
  --  - "fakeyou" - FakeYou signup
  --  - "storyteller" - Storyteller.ai signup
  --
  maybe_source VARCHAR(255) DEFAULT NULL,

  -- How users created their account
  -- Older users do not have a value and are assumed to be "email_password"
  -- Values:
  --   - "email_password"
  --   - "google_sign_in"
  --   - "stripe_checkout" (eager stripe checkout account provisioning)
  maybe_signup_method VARCHAR(16) DEFAULT NULL,

  -- Account was provisioned automatically (eg. stripe eager checkout flow)
  -- This will remain true even if the user finishes account setup, email change,
  -- password setup, etc. (It's a permanent label on the account for later
  -- statistical evaluation.)
  was_eagerly_provisioned BOOLEAN NOT NULL DEFAULT false,

  -- The referral URL the user arrived from when signing up.
  -- This helps us know what marketing is effective.
  maybe_referral_url VARCHAR(255) DEFAULT NULL,

  -- The actual URL the user first arrived at when signing up, including query params.
  -- This helps us know what marketing is effective.
  maybe_landing_url VARCHAR(255) DEFAULT NULL,

  -- If a user referred this user, this is either the raw username or raw "referral code"
  -- that this user used to sign up. This is not verified and can be used for debugging.
  maybe_referral_partner VARCHAR(32) DEFAULT NULL,

  -- If a user referred this user, we record the user token of the referrer.
  maybe_referral_user_token VARCHAR(32) DEFAULT NULL,

  -- ========== MODERATION DETAILS ==========

  -- Different than deleted.
  -- Users still show up, but can't do anything.
  is_banned BOOLEAN NOT NULL DEFAULT false,

  -- If a moderator has comments.
  maybe_mod_comments VARCHAR(255) DEFAULT NULL,

  -- The last moderator that made changes.
  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,

  -- ========== VECTOR CLOCK ==========

  -- Incremented with every update.
  version INT UNSIGNED NOT NULL DEFAULT 0,

  -- ========== TIMESTAMPS ==========

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  -- If the user is deleted, we set this.
  -- This is different than banned. These users won't show up at all.
  user_deleted_at TIMESTAMP NULL,
  mod_deleted_at TIMESTAMP NULL,

  -- INDICES --
  PRIMARY KEY (id),
  UNIQUE KEY (token),
  UNIQUE KEY (username),
  UNIQUE KEY (email_address),
  KEY fk_user_role_slug (user_role_slug),
  KEY index_can_access_studio (can_access_studio),
  KEY index_is_api_user (is_api_user),
  KEY fk_maybe_avatar_media_file_token (maybe_avatar_media_file_token),
  KEY fk_maybe_cover_media_file_token (maybe_cover_media_file_token)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;