Skip to content

A platform for community discussion. Free, open, simple.

184 KB
Raw Blame
# frozen_string_literal: true

begin
  require_relative "base"
  require "sqlite3"
  require "json"
rescue LoadError
  STDERR.puts "",
              "ERROR: Failed to load required gems.",
              "",
              "You need to enable the `generic_import` group in your Gemfile.",
              "Execute the following command to do so:",
              "",
              "\tbundle config set --local with generic_import && bundle install",
              ""
  exit 1
end

class BulkImport::Generic < BulkImport::Base
  include HasSanitizableFields

  AVATAR_DIRECTORY = ENV["AVATAR_DIRECTORY"]
  UPLOAD_DIRECTORY = ENV["UPLOAD_DIRECTORY"]
  MERGE_IMPORT = ENV["MERGE_IMPORT"].present?
  DELTA_IMPORT = ENV["DELTA_IMPORT"].present?
  CONTENT_UPLOAD_REFERENCE_TYPES = %w[posts chat_messages]
  LAST_VIEWED_AT_PLACEHOLDER = "1970-01-01 00:00:00"
  # Same quote pairs as the BBCode parser accepts (QUOTATION_MARKS in discourse-markdown-it).
  BBCODE_QUOTATION_MARKS = ["\"\"", "''", "«»", "“”", "””", "‘’", "„“", "‚’", "‹›"]

  def initialize(db_path, uploads_db_path = nil)
    self.class.validate_modes!(merge_import: MERGE_IMPORT, delta_import: DELTA_IMPORT)
    # SQLite would silently create an empty database for a mistyped path
    {
      "Intermediate database" => db_path,
      "Uploads database" => uploads_db_path,
    }.each { |label, path| raise "#{label} not found: #{path}" if path && !File.file?(path) }
    super()
    @source_db = create_connection(db_path)
    @uploads_db = create_connection(uploads_db_path) if uploads_db_path

    if MERGE_IMPORT
      row = @source_db.execute("SELECT value FROM config WHERE name = 'converting_from'").first
      @import_prefix = row&.[]("value")
      unless @import_prefix
        raise "MERGE_IMPORT requires 'converting_from' in intermediate DB config table"
      end
      puts "MERGE_IMPORT mode enabled with source prefix: #{@import_prefix}"
    elsif DELTA_IMPORT
      puts "DELTA_IMPORT mode enabled"
    end
  end

  def self.validate_modes!(merge_import:, delta_import:)
    return unless merge_import && delta_import

    raise "MERGE_IMPORT and DELTA_IMPORT cannot be enabled together"
  end

  def delta_import?
    DELTA_IMPORT
  end

  def preflight
    configure_unicode_usernames!

    return unless delta_import?

    puts "Running delta import preflight..."

    unless DB.query_single("SELECT to_regclass('public.migration_mappings')::TEXT").first
      raise "DELTA_IMPORT requires migration_mappings from a completed base import"
    end

    mappings = {
      groups: plain_import_mappings("group"),
      users: canonical_user_import_mappings,
      categories: plain_import_mappings("category"),
      topics: plain_import_mappings("topic"),
      posts: plain_import_mappings("post"),
    }
    if mappings.values.sum(&:size).zero?
      raise "DELTA_IMPORT requires existing plain numeric import_id mappings from a base import"
    end
    unless plain_mapped_core_record_exists?
      raise "DELTA_IMPORT requires at least one mapped core record from a base import"
    end

    errors = []
    preflight_users(mappings[:users], errors)
    preflight_groups(mappings[:groups], errors)
    preflight_categories(mappings, errors)
    preflight_topics(mappings, errors)
    preflight_posts(mappings, errors)

    report_delta_user_audit(
      @delta_preexisting_user_source_ids,
      "mapped users merge into pre-existing destination accounts " \
        "(identity verified by email) and will be updated",
    )
    report_delta_user_audit(
      @delta_username_conflict_source_ids,
      "mapped users keep their current usernames because the source username is taken",
    )
    report_delta_user_audit(
      @delta_unverified_user_source_ids,
      "mapped users have creation-date mismatches the delta cannot verify " \
        "(no email supplied) and will not be updated",
    )

    return if errors.empty?

    shown_errors = errors.first(25)
    suffix =
      (
        if errors.length > shown_errors.length
          "\n  ...and #{errors.length - shown_errors.length} more"
        else
          ""
        end
      )
    raise "Delta import preflight failed:\n  #{shown_errors.join("\n  ")}#{suffix}"
  end

  # non-ASCII source names require unicode_usernames before any name is
  # sanitized; the final value must be live before both the fresh import
  # and the delta preflight run
  def configure_unicode_usernames!
    if (allowlist = source_site_setting_value("allowed_unicode_username_characters"))
      SiteSetting.set_and_log(:allowed_unicode_username_characters, allowlist)
    end

    if (explicit = source_site_setting_value("unicode_usernames"))
      desired = %w[t true 1].include?(explicit.to_s.downcase)
      enable_unicode_usernames! if desired && !SiteSetting.unicode_usernames
      return
    end

    return if SiteSetting.unicode_usernames
    return unless source_has_non_ascii_names?

    enable_unicode_usernames!
    log_import_issue(
      "unicode_usernames auto-enabled",
      "source contains non-ASCII usernames or group names",
    )
  end

  def enable_unicode_usernames!
    raise <<~MSG if SiteSetting.external_system_avatars_url.blank?
        The source contains non-ASCII usernames, which requires enabling the
        'unicode_usernames' site setting, but its validator requires
        'external_system_avatars_url' to be set. Configure
        external_system_avatars_url on the destination site and re-run the import.
      MSG

    SiteSetting.set_and_log(:unicode_usernames, true)
    puts "Enabled unicode_usernames (non-ASCII names detected in source)"
  end

  def source_site_setting_value(name)
    query(
      "SELECT value FROM site_settings WHERE name = ? AND action = 'update' ORDER BY ROWID DESC LIMIT 1",
      name,
    ) { |rows| rows.first&.fetch("value", nil) }
  end

  def source_has_non_ascii_names?
    non_ascii = ->(value) { value.present? && !value.ascii_only? }
    user_columns = %w[username original_username] & table_column_names("users").to_a

    query("SELECT #{user_columns.join(", ")} FROM users") do |rows|
      rows.any? { |row| user_columns.any? { |column| non_ascii.call(row[column]) } }
    end ||
      query("SELECT name FROM groups") { |rows| rows.any? { |row| non_ascii.call(row["name"]) } }
  end

  def load_imported_ids
    super
    return unless delta_import?

    @delta_base_mappings = {
      groups: @groups.dup,
      users: @users.dup,
      categories: @categories.dup,
      topics: @topics.dup,
      posts: @posts.dup,
    }
    canonical_users = canonical_user_import_mappings
    if @delta_unverified_user_source_ids.present?
      canonical_users =
        canonical_users.reject do |source_id, _|
          @delta_unverified_user_source_ids.include?(source_id)
        end
    end
    @delta_update_mappings = @delta_base_mappings.merge(users: canonical_users)
  end

  def plain_import_mappings(name)
    DB.query(<<~SQL).to_h { |row| [Integer(row.value, 10), row.discourse_id.to_i] }
      SELECT value, #{name}_id AS discourse_id
        FROM #{name}_custom_fields
       WHERE name = 'import_id'
         AND value ~ '^[0-9]+$'
    SQL
  end

  def plain_mapped_core_record_exists?
    %w[group user category topic post].any? { |name| DB.query_single(<<~SQL).present? }
        SELECT 1
          FROM #{name}_custom_fields mapping
               JOIN #{name.pluralize} record ON record.id = mapping.#{name}_id
         WHERE mapping.name = 'import_id'
           AND mapping.value ~ '^[0-9]+$'
         LIMIT 1
      SQL
  end

  # A destination user can carry several source ids (base-import email merges,
  # alias merges); prefer the one this delta still exports so its updates apply.
  def canonical_user_import_mappings
    delta_source_ids =
      query("SELECT id FROM users") { |rows| rows.map { |row| row["id"].to_i }.to_set }
    rows = DB.query(<<~SQL)
      SELECT value, user_id AS discourse_id, created_at, id
        FROM user_custom_fields
       WHERE name = 'import_id'
         AND value ~ '^[0-9]+$'
       ORDER BY user_id, created_at, id
    SQL
    rows
      .group_by(&:discourse_id)
      .values
      .to_h do |mapping_rows|
        row =
          mapping_rows.find { |mapping| delta_source_ids.include?(Integer(mapping.value, 10)) } ||
            mapping_rows.first
        [Integer(row.value, 10), row.discourse_id.to_i]
      end
  end

  # The converter may consolidate several source accounts into one winner. The
  # optional user_aliases table lists those alias -> winner pairs so a delta can
  # merge destination accounts the base import created separately.
  def delta_user_aliases
    @delta_user_aliases ||=
      if table_column_names("user_aliases").any?
        query(
          "SELECT alias_user_id, canonical_user_id FROM user_aliases ORDER BY alias_user_id",
        ) { |rows| rows.map { |row| [row["alias_user_id"].to_i, row["canonical_user_id"].to_i] } }
      else
        []
      end
  end

  def delta_alias_accounts_by_canonical
    base_mappings = plain_import_mappings("user")
    delta_user_aliases.each_with_object(
      Hash.new { |hash, key| hash[key] = Set.new },
    ) do |(alias_id, canonical_id), result|
      account_id = base_mappings[alias_id]
      result[canonical_id] << account_id if account_id
    end
  end

  # Runs once before existing users are updated (both accounts came from the
  # base import) and once after the delta's new users, profiles, and stats
  # exist (the winner is new to this delta). Each pass is a no-op for pairs that
  # already share one account, so reruns are safe.
  def merge_delta_user_aliases
    pending_merges =
      delta_user_aliases.filter_map do |alias_source_id, canonical_source_id|
        source_id = user_id_from_imported_id(alias_source_id)
        target_id = user_id_from_imported_id(canonical_source_id)
        next if source_id.nil? || target_id.nil? || source_id == target_id

        source_user = User.unscoped.find_by(id: source_id)
        target_user = User.unscoped.find_by(id: target_id)
        next unless source_user && target_user

        [alias_source_id, canonical_source_id, source_user, target_user]
      end
    return if pending_merges.empty?

    # UserMerger inserts through ActiveRecord, but this run has COPYed rows with
    # importer-assigned ids that the sequences only learn about at the end.
    fix_primary_keys
    removed_users = pending_merges.map { |_, _, source_user, _| source_user }

    pending_merges.each do |alias_source_id, canonical_source_id, source_user, target_user|
      begin
        UserMerger.new(source_user, target_user).merge!
      rescue StandardError => e
        raise "Merging alias user #{alias_source_id} into user #{canonical_source_id} failed: #{e.message}"
      end

      # UserMerger keeps the target's own import_id and drops the alias's; keep
      # it on the survivor so reruns and later deltas still resolve the alias.
      UserCustomField.find_or_create_by!(
        user_id: target_user.id,
        name: "import_id",
        value: alias_source_id.to_s,
      )
      @users[alias_source_id] = target_user.id
      @delta_stats[:users][:updated_ids] << target_user.id if @delta_stats
    end

    # the merger's replacement addresses took ids above the importer's counter
    @last_user_email_id = last_id(UserEmail)
    refresh_user_lookup_caches(
      pending_merges.map { |_, _, _, target_user| target_user.id }.uniq,
      removed_users: removed_users,
    )

    merged_source_ids = pending_merges.map(&:first)
    shown_ids = merged_source_ids.first(20)
    suffix =
      (
        if merged_source_ids.length > shown_ids.length
          " +#{merged_source_ids.length - shown_ids.length} more"
        else
          ""
        end
      )
    puts "  Merged #{merged_source_ids.length} alias accounts into their winners (source IDs: #{shown_ids.join(", ")}#{suffix})"
  end

  def refresh_user_lookup_caches(user_ids, removed_users: [])
    removed_users.each do |user|
      @usernames_lower.delete(user.username_lower)
      @user_ids_by_username_lower.delete(user.username_lower)
      @usernames_by_id.delete(user.id)
      @user_full_names_by_id.delete(user.id)
    end

    user_ids.each_slice(10_000) do |ids|
      User
        .unscoped
        .where(id: ids)
        .pluck(:id, :username, :username_lower, :name)
        .each do |id, username, username_lower, name|
          @usernames_lower << username_lower
          @user_ids_by_username_lower[username_lower] = id
          @usernames_by_id[id] = username
          @user_full_names_by_id[id] = name if name.present?
        end
    end
    @emails = UserEmail.pluck(:email, :user_id).to_h
  end

  def delta_update_mapping(name)
    @delta_update_mappings.fetch(name)
  end

  def preflight_users(mappings, errors)
    @delta_preexisting_user_source_ids ||= Set.new
    @delta_unverified_user_source_ids ||= Set.new
    @delta_username_conflict_source_ids ||= Set.new
    return if mappings.empty?

    created_at_by_user_id = User.unscoped.pluck(:id, :created_at).to_h
    email_owners = UserEmail.pluck(Arel.sql("LOWER(email)"), :user_id).to_h
    username_owners = User.unscoped.pluck(:username_lower, :id).to_h
    username_lower_by_id = User.unscoped.pluck(:id, :username_lower).to_h
    alias_accounts = delta_alias_accounts_by_canonical
    seen_emails = {}
    seen_usernames = {}

    rows = query("SELECT * FROM users ORDER BY id")
    rows.each do |row|
      discourse_id = mappings[row["id"].to_i]
      next unless discourse_id

      created_at = created_at_by_user_id[discourse_id]
      unless created_at
        errors << "user #{row["id"]} maps to missing Discourse user #{discourse_id}"
        next
      end

      if immutable_time_changed?(row["created_at"], created_at)
        if row["email"].blank?
          # without an email the delta cannot re-verify the mapping, so the
          # account is left untouched instead of failing the run
          @delta_unverified_user_source_ids << row["id"].to_i
          next
        elsif email_owners[row["email"].downcase] == discourse_id
          # the base import merged this source user by email into an account
          # that predates it; the email match proves the mapping, so only the
          # creation-date check is waived and the account stays updatable
          @delta_preexisting_user_source_ids << row["id"].to_i
        else
          errors << "user #{row["id"]} changes immutable created_at"
        end
      end

      if row["email"].present?
        email = row["email"].downcase
        owner_id = email_owners[email]
        if owner_id && owner_id != discourse_id &&
             !alias_accounts.fetch(row["id"].to_i, Set.new).include?(owner_id)
          errors << "user #{row["id"]} email belongs to Discourse user #{owner_id}"
        end

        if (previous_source_id = seen_emails[email])
          errors << "user #{row["id"]} duplicates email of source user #{previous_source_id}"
        else
          seen_emails[email] = row["id"]
        end
      end

      if row["username"].present? &&
           !delta_username_unchanged?(row, discourse_id, username_lower_by_id[discourse_id]) &&
           (username = fix_name(row["username"])).present?
        normalized_username = User.normalize_username(username)
        owner_id = username_owners[normalized_username]
        if owner_id && owner_id != discourse_id
          # a taken username skips only the rename; failing the run would
          # block deltas on conflicts the source cannot resolve
          @delta_username_conflict_source_ids << row["id"].to_i
        elsif (previous_source_id = seen_usernames[normalized_username])
          errors << "user #{row["id"]} duplicates username of source user #{previous_source_id}"
        else
          seen_usernames[normalized_username] = row["id"]
        end
      end
    end
  ensure
    rows&.close
  end

  def report_delta_user_audit(set, description)
    return if set.blank?

    shown_ids = set.sort.first(20)
    hidden_count = set.size - shown_ids.size
    suffix = hidden_count > 0 ? " +#{hidden_count} more" : ""
    puts "Preflight: #{set.size} #{description} (source IDs: #{shown_ids.join(", ")}#{suffix})"
  end

  def imported_usernames_by_user_id
    @imported_usernames_by_user_id ||=
      UserCustomField.where(name: "import_username").pluck(:user_id, :value).to_h
  end

  # true when the source still carries the username the base import saw; the
  # destination may hold a deduplicated variant of it that must be kept
  def delta_username_unchanged?(row, discourse_id, destination_username_lower)
    source_username = row["original_username"].presence || row["username"]
    if User.normalize_username(imported_usernames_by_user_id[discourse_id]) ==
         User.normalize_username(source_username)
      return true
    end
    return false if destination_username_lower.blank?

    # the destination still carries the source username verbatim: the base
    # import kept it, so a stricter sanitizer must not rename it now
    return true if User.normalize_username(source_username) == destination_username_lower

    # suffix-deduplicated names are recorded nowhere when fix_name left the
    # name itself unchanged, so recognize the suffix pattern directly
    fixed = fix_name(row["username"])
    return false if fixed.blank?

    normalized = User.normalize_username(fixed)
    destination_username_lower.match?(/\A#{Regexp.escape(normalized)}_\d+\z/)
  end

  def preflight_groups(mappings, errors)
    return if mappings.empty?

    seen_names = {}
    rows = query("SELECT id, name, existing_id FROM groups ORDER BY id")
    rows.each do |row|
      next unless mappings.key?(row["id"].to_i)
      next if row["existing_id"].present?
      next if (name = fix_name(row["name"])).blank?

      name_lower = User.normalize_username(name)
      if (previous_source_id = seen_names[name_lower])
        errors << "group #{row["id"]} duplicates name of source group #{previous_source_id}"
      else
        seen_names[name_lower] = row["id"]
      end
    end
  ensure
    rows&.close
  end

  def preflight_categories(mappings, errors)
    return if mappings[:categories].empty?

    seen_names = {}
    rows = query("SELECT id, parent_category_id, name, existing_id FROM categories ORDER BY id")
    rows.each do |row|
      next unless mappings[:categories].key?(row["id"].to_i)
      next if row["existing_id"].present?
      next if row["name"].blank?

      if row["parent_category_id"].present?
        parent_id = mappings[:categories][row["parent_category_id"].to_i]
        # parents new in this delta cannot collide with updated names yet
        next unless parent_id
      end

      key = [parent_id, row["name"][0...50].scrub.strip]
      if (previous_source_id = seen_names[key])
        errors << "category #{row["id"]} duplicates name of source category #{previous_source_id}"
      else
        seen_names[key] = row["id"]
      end
    end
  ensure
    rows&.close
  end

  def preflight_topics(mappings, errors)
    return if mappings[:topics].empty?

    topics_by_id =
      Topic
        .unscoped
        .pluck(:id, :created_at, :archetype)
        .to_h { |id, created_at, archetype| [id, { created_at: created_at, archetype: archetype }] }
    topic_columns = table_column_names("topics")
    rows = query("SELECT * FROM topics ORDER BY id")
    rows.each do |row|
      discourse_id = mappings[:topics][row["id"].to_i]
      next unless discourse_id

      topic = topics_by_id[discourse_id]
      unless topic
        errors << "topic #{row["id"]} maps to missing Discourse topic #{discourse_id}"
        next
      end

      if immutable_time_changed?(row["created_at"], topic[:created_at])
        errors << "topic #{row["id"]} changes immutable created_at"
      end

      source_archetype =
        if topic_columns.include?("archetype")
          row["archetype"]
        elsif topic_columns.include?("private_message")
          row["private_message"].present? ? Archetype.private_message : Archetype.default
        end
      if source_archetype.present? && source_archetype != topic[:archetype]
        errors << "topic #{row["id"]} changes immutable archetype"
      end
    end
  ensure
    rows&.close
  end

  def preflight_posts(mappings, errors)
    return if mappings[:posts].empty?

    posts_by_id =
      Post
        .unscoped
        .pluck(:id, :created_at, :topic_id, :post_number, :reply_to_post_number)
        .to_h do |id, created_at, topic_id, post_number, reply_to_post_number|
          [
            id,
            {
              created_at: created_at,
              topic_id: topic_id,
              post_number: post_number,
              reply_to_post_number: reply_to_post_number,
            },
          ]
        end
    rows = query("SELECT * FROM posts ORDER BY id")
    rows.each do |row|
      discourse_id = mappings[:posts][row["id"].to_i]
      next unless discourse_id

      post = posts_by_id[discourse_id]
      unless post
        errors << "post #{row["id"]} maps to missing Discourse post #{discourse_id}"
        next
      end

      if immutable_time_changed?(row["created_at"], post[:created_at])
        errors << "post #{row["id"]} changes immutable created_at"
      end

      mapped_topic_id = mappings[:topics][row["topic_id"].to_i]
      errors << "post #{row["id"]} changes immutable topic" if mapped_topic_id != post[:topic_id]

      next if row["reply_to_post_id"].blank?

      parent_id = mappings[:posts][row["reply_to_post_id"].to_i]
      parent_number = posts_by_id.dig(parent_id, :post_number)
      parent_number = nil if parent_number == 1
      if parent_number != post[:reply_to_post_number]
        errors << "post #{row["id"]} changes immutable reply parent"
      end
    end
  ensure
    rows&.close
  end

  def immutable_time_changed?(source_value, destination_value)
    source_value.present? &&
      to_datetime(source_value).to_time.to_i != destination_value.to_time.to_i
  end

  def start
    run # will call execute, and then "complete" the migration

    # Now that the migration is complete, do some more work:

    ENV["SKIP_USER_STATS"] = "1"
    Discourse::Application.load_tasks

    puts "running 'import:ensure_consistency' rake task."
    Rake::Task["import:ensure_consistency"].invoke

    # Force refresh directory items to sync user stats
    # This is needed even when enable_user_directory is false
    puts "", "Refreshing directory items and syncing user stats..."
    refresh_directory_items
  end

  def execute
    enable_required_plugins
    import_site_settings

    import_uploads

    # needs to happen before users, because keeping group names is more important than usernames
    import_groups

    import_users
    import_user_emails
    import_user_profiles
    import_user_options
    import_user_fields
    import_user_field_values
    import_single_sign_on_records
    import_user_associated_accounts
    import_muted_users
    import_user_histories
    import_user_notes
    import_user_note_counts
    import_user_custom_fields
    import_user_followers

    import_user_avatars
    update_uploaded_avatar_id

    import_group_members

    import_tag_groups
    import_tags
    import_tag_users

    import_categories
    import_category_custom_fields
    import_category_tag_groups
    import_category_permissions
    import_category_users
    import_category_moderation_groups
    update_category_read_restricted

    import_topics
    import_topic_custom_fields
    import_posts
    import_post_custom_fields

    import_polls
    import_poll_options
    import_poll_votes

    import_topic_tags
    import_topic_allowed_users
    import_topic_allowed_groups

    import_engagement
    import_post_voting_votes
    import_topic_voting_votes
    import_category_topic_voting
    import_answers
    import_gamification_scores
    import_post_events
    import_event_invitees

    import_badge_groupings
    import_badges
    import_user_badges
    import_anniversary_user_badges
    update_badge_grant_counts

    import_optimized_images

    import_topic_users
    import_event_invitee_topic_tracking
    update_topic_users
    import_bookmarks

    import_user_stats
    merge_delta_user_aliases if delta_import?
    import_user_associated_groups

    import_permalink_normalizations
    import_permalinks

    import_chat_direct_messages
    import_chat_channels

    import_chat_threads
    import_chat_messages

    import_user_chat_channel_memberships
    import_chat_thread_users

    import_chat_reactions
    import_chat_mentions

    update_chat_threads
    update_chat_membership_metadata

    import_upload_references
  end

  def execute_after
    import_category_about_topics
    report_delta_stats if delta_import?

    @source_db.close
    @uploads_db.close if @uploads_db
  end

  def refresh_directory_items
    start_time = Time.now

    # Force refresh all directory periods - this works even when
    # enable_user_directory site setting is disabled
    DirectoryItem.period_types.each_key do |period|
      puts "  Refreshing directory items for period: #{period}"
      DirectoryItem.refresh_period!(period, force: true)
    end

    UserStat.ensure_consistency!
    Discourse.cache.clear
    Site.clear_cache

    puts "  Refreshed directory items in #{(Time.now - start_time).to_i} seconds."
  end

  def start_delta_entity(entity, table, mapping, last_destination_id)
    return unless delta_import?

    source_ids = query("SELECT id FROM #{table}") { |rows| rows.map { |row| row["id"].to_i } }
    base_mapping = @delta_base_mappings.fetch(mapping)
    @delta_stats ||= {}
    @delta_stats[entity] = {
      mapped: source_ids.count { |source_id| base_mapping.key?(source_id) },
      unmapped_ids: source_ids.reject { |source_id| base_mapping.key?(source_id) },
      last_destination_id: last_destination_id,
      updated_ids: Set.new,
    }
  end

  def finish_delta_entity(entity, mapping)
    return unless delta_import?

    stats = @delta_stats.fetch(entity)
    destination_mapping = instance_variable_get("@#{mapping}")
    stats[:deduplicated_ids] = stats[:unmapped_ids]
      .select do |source_id|
        destination_id = destination_mapping[source_id]
        destination_id && destination_id <= stats[:last_destination_id]
      end
      .to_set
    stats[:deduplicated] = stats[:deduplicated_ids].size
    stats[:new] = stats[:unmapped_ids].count do |source_id|
      destination_id = destination_mapping[source_id]
      destination_id && destination_id > stats[:last_destination_id]
    end
    stats[:updated] = stats[:updated_ids].size
    stats[:unchanged] = stats[:mapped] - stats[:updated]
  end

  def delta_deduplicated_source_id?(entity, source_id)
    return false unless delta_import?

    source_id = source_id.to_i
    return true if @delta_stats.dig(entity, :deduplicated_ids)&.include?(source_id)

    entity == :users && @delta_base_mappings[:users].key?(source_id) &&
      !delta_update_mapping(:users).key?(source_id)
  end

  def record_delta_updates(entity, result)
    return unless delta_import?

    @delta_stats.fetch(entity)[:updated_ids].merge(result[:updated_keys].map(&:to_i))
  end

  def update_records(rows, name, columns, keys: [:id])
    result = super
    track_delta_column_changes(name, result) if delta_import?
    result
  end

  def track_delta_column_changes(name, result)
    @delta_column_changes ||=
      Hash.new { |hash, key| hash[key] = { total: 0, columns: Hash.new(0) } }
    entry = @delta_column_changes[name.to_s.pluralize]
    entry[:total] += result[:total]
    result[:changed_counts].each { |column, count| entry[:columns][column] += count }
  end

  def report_delta_stats
    puts "", "Delta import summary:"
    @delta_stats.each do |entity, stats|
      stats[:updated] = stats[:updated_ids].size
      stats[:unchanged] = stats[:mapped] - stats[:updated]
      puts "  #{entity}: mapped=#{stats[:mapped]}, new=#{stats[:new]}, " \
             "unchanged=#{stats[:unchanged]}, updated=#{stats[:updated]}, " \
             "deduplicated=#{stats[:deduplicated]}"
    end

    report_delta_column_changes
  end

  def report_delta_column_changes
    return if @delta_column_changes.blank?

    printed_header = false
    @delta_column_changes.sort.each do |table, entry|
      columns = entry[:columns].select { |_, count| count > 0 }.sort_by { |_, count| -count }
      next if columns.empty?

      unless printed_header
        puts "", "Delta column updates:"
        printed_header = true
      end
      puts "  #{table} (#{entry[:total]} candidate rows):"
      columns.each do |column, count|
        share = entry[:total] > 0 ? (count * 100.0 / entry[:total]).round : 0
        flag =
          if count >= 100 && share >= 50
            "  <-- #{share}% of rows; verify the source really changed " \
              "(an export-side default may have changed)"
          else
            ""
          end
        puts "    #{column}: #{count}#{flag}"
      end
    end
  end

  def upsert_delta_custom_fields(entity, mapping_name, anonymized_filter: false)
    foreign_key = "#{entity}_id"
    source_table = "#{entity}_custom_fields"
    sql = "SELECT fields.* FROM #{source_table} fields"
    if anonymized_filter
      sql += " JOIN users source_users ON source_users.id = fields.user_id"
      sql += " WHERE source_users.anonymized IS NOT TRUE"
    end

    rows = query(sql)
    updates_by_key = {}
    rows.each do |row|
      next if row["value"].nil?

      discourse_id = delta_update_mapping(mapping_name)[row[foreign_key].to_i]
      next unless discourse_id

      update = { foreign_key.to_sym => discourse_id, :name => row["name"], :value => row["value"] }
      updates_by_key[[discourse_id, row["name"]]] = update
    end
    result =
      update_records(
        updates_by_key.values,
        "#{entity}_custom_field",
        [:value],
        keys: [foreign_key.to_sym, :name],
      )
    @delta_stats[mapping_name][:updated_ids].merge(
      result[:updated_keys].map { |key| key.first.to_i },
    )
  ensure
    rows&.close
  end

  def enable_required_plugins
    puts "", "Enabling required plugins..."

    required_plugin_names = @source_db.get_first_value(<<~SQL)&.then(&JSON.method(:parse))
      SELECT value
        FROM config
       WHERE name = 'enable_required_plugins'
    SQL

    return if required_plugin_names.blank?

    plugins_by_name = Discourse.plugins_by_name

    required_plugin_names.each do |plugin_name|
      if (plugin = plugins_by_name[plugin_name])
        if !plugin.enabled? && plugin.configurable?
          SiteSetting.set(plugin.enabled_site_setting, true)
        end
        puts "  #{plugin_name} plugin enabled"
      else
        puts "  ERROR: The #{plugin_name} plugin is required, but not installed."
        exit 1
      end
    end
  end

  def import_site_settings
    puts "", "Importing site settings..."

    rows = query(<<~SQL)
      SELECT name, value, action
      FROM site_settings
      ORDER BY ROWID
    SQL

    all_settings = SiteSetting.all_settings

    rows.each do |row|
      name = row["name"].to_sym
      setting = all_settings.find { |s| s[:setting] == name }
      next unless setting

      case row["action"]
      when "update"
        SiteSetting.set_and_log(name, row["value"])
      when "append"
        raise "Cannot append to #{name} setting" if setting[:type] != "list"
        items = (SiteSetting.get(name) || "").split("|")
        items << row["value"] if items.exclude?(row["value"])
        SiteSetting.set_and_log(name, items.join("|"))
      end
    end

    rows.close

    return if ENV["SKIP_MIGRATED_SITE_FLAG_UPDATE"]

    # Bypassing SiteSetting.set_and_log if migrated_site is present and not enabled, enable it
    # We don't need to have the plugin enabled
    migrated_site_flag_enabled = DB.exec(<<~SQL) > 0
      UPDATE site_settings
         SET value = 't'
       WHERE name = 'migrated_site'
         AND value <> 't'
    SQL

    SiteSetting.refresh! if migrated_site_flag_enabled
  end

  def import_categories
    puts "", "Importing categories..."

    start_delta_entity(:categories, "categories", :categories, @last_category_id)
    update_delta_categories if delta_import?

    categories = query(<<~SQL)
        WITH
          RECURSIVE
          tree AS (
                    SELECT c.id, c.parent_category_id, c.name, c.description, c.color, c.text_color,
                           c.slug, c.existing_id, c.position, c.logo_upload_id, 0 AS level, c.show_subcategory_list, c.subcategory_list_style, c.minimum_required_tags
                      FROM categories c
                     WHERE c.parent_category_id IS NULL
                     UNION ALL
                    SELECT c.id, c.parent_category_id, c.name, c.description, c.color, c.text_color,
                           c.slug, c.existing_id, c.position, c.logo_upload_id, tree.level + 1 AS level,
                           c.show_subcategory_list, c.subcategory_list_style, c.minimum_required_tags
                      FROM categories c,
                           tree
                     WHERE c.parent_category_id = tree.id
                  )
      SELECT id, parent_category_id, name, description, color, text_color, slug, existing_id, logo_upload_id, show_subcategory_list, subcategory_list_style, minimum_required_tags,
             COALESCE(position,
                      ROW_NUMBER() OVER (PARTITION BY parent_category_id ORDER BY parent_category_id NULLS FIRST, name)) AS position
        FROM tree
       ORDER BY level, position, id
    SQL

    create_categories(categories) do |row|
      next if category_id_from_imported_id(row["id"]).present?

      {
        imported_id: row["id"],
        existing_id: row["existing_id"],
        name: row["name"],
        description: row["description"],
        parent_category_id:
          (
            if row["parent_category_id"]
              category_id_from_imported_id(row["parent_category_id"])
            else
              nil
            end
          ),
        slug: row["slug"],
        uploaded_logo_id:
          (
            if row["logo_upload_id"]
              upload_id_from_original_id(row["logo_upload_id"])
            else
              nil
            end
          ),
        show_subcategory_list: row["show_subcategory_list"],
        subcategory_list_style: row["subcategory_list_style"],
        minimum_required_tags: row["minimum_required_tags"],
        color: row["color"],
        text_color: row["text_color"],
      }
    end

    categories.close
    update_delta_category_parents if delta_import?
    finish_delta_entity(:categories, :categories)
  end

  # second pass: parents that were only created by this delta are resolvable
  # after create_categories has run
  def update_delta_category_parents
    rows = query(<<~SQL)
      SELECT id, parent_category_id, existing_id
        FROM categories
       WHERE parent_category_id IS NOT NULL
       ORDER BY id
    SQL
    updates =
      rows.filter_map do |row|
        discourse_id = delta_update_mapping(:categories)[row["id"].to_i]
        next unless discourse_id
        next if row["existing_id"].present?

        parent_id = category_id_from_imported_id(row["parent_category_id"])
        next unless parent_id

        { id: discourse_id, parent_category_id: parent_id }
      end
    result = update_records(updates, "category", [:parent_category_id])
    record_delta_updates(:categories, result)
  ensure
    rows&.close
  end

  def update_delta_categories
    columns = %i[
      name
      name_lower
      slug
      user_id
      description
      position
      parent_category_id
      uploaded_logo_id
      show_subcategory_list
      subcategory_list_style
      minimum_required_tags
      color
      text_color
    ]
    source_columns = table_column_names("categories")
    rows = query("SELECT * FROM categories ORDER BY id")
    updates =
      rows.filter_map do |row|
        discourse_id = delta_update_mapping(:categories)[row["id"].to_i]
        next unless discourse_id
        next if row["existing_id"].present?

        update = { id: discourse_id }
        if row["name"].present?
          update[:name] = row["name"][0...50].scrub.strip
          update[:name_lower] = update[:name].downcase
        end
        update[:slug] = row["slug"] if source_columns.include?("slug")
        if source_columns.include?("user_id") && row["user_id"].present?
          update[:user_id] = user_id_from_imported_id(row["user_id"])
        end
        update[:description] = row["description"] if source_columns.include?("description")
        update[:position] = row["position"] if source_columns.include?("position")
        if row["parent_category_id"].present?
          update[:parent_category_id] = category_id_from_imported_id(row["parent_category_id"])
        end
        if row["logo_upload_id"].present?
          update[:uploaded_logo_id] = upload_id_from_original_id(row["logo_upload_id"])
        end
        %i[
          show_subcategory_list
          subcategory_list_style
          minimum_required_tags
          color
          text_color
        ].each do |column|
          update[column] = row[column.to_s] if source_columns.include?(column.to_s)
        end
        update
      end
    result = update_records(updates, "category", columns)
    record_delta_updates(:categories, result)
  ensure
    rows&.close
  end

  def import_category_about_topics
    puts "", %|Creating "About..." topics for categories...|
    start_time = Time.now
    Category.ensure_consistency!
    Site.clear_cache

    categories = query(<<~SQL)
      SELECT id, about_topic_title, existing_id
        FROM categories
       WHERE about_topic_title IS NOT NULL
       ORDER BY id
    SQL

    categories.each do |row|
      next if delta_import? && row["existing_id"].present?

      if (about_topic_title = row["about_topic_title"]).present?
        if (category_id = category_id_from_imported_id(row["id"]))
          topic = Category.find(category_id).topic
          if delta_import? && topic.title != about_topic_title
            @delta_stats[:categories][:updated_ids] << category_id
          end
          topic.title = about_topic_title
          topic.save!(validate: false)
        end
      end
    end

    categories.close

    puts "  Creating took #{(Time.now - start_time).to_i} seconds."
  end

  def import_category_custom_fields
    puts "", "Importing category custom fields..."

    upsert_delta_custom_fields(:category, :categories) if delta_import?

    category_custom_fields = query(<<~SQL)
      SELECT *
      FROM category_custom_fields
      ORDER BY category_id, name
    SQL

    field_names =
      query("SELECT DISTINCT name FROM category_custom_fields") { it.map { |row| row["name"] } }
    existing_category_custom_fields =
      CategoryCustomField.where(name: field_names).pluck(:category_id, :name).to_set

    create_category_custom_fields(category_custom_fields) do |row|
      category_id = category_id_from_imported_id(row["category_id"])
      next if category_id.nil?

      next if existing_category_custom_fields.include?([category_id, row["name"]])

      { category_id: category_id, name: row["name"], value: row["value"] }
    end

    category_custom_fields.close
  end

  def import_category_tag_groups
    puts "", "Importing category tag groups..."

    category_tag_groups = query(<<~SQL)
      SELECT c.id AS category_id, t.value AS tag_group_id
        FROM categories c,
             JSON_EACH(c.tag_group_ids) t
       ORDER BY category_id, tag_group_id
    SQL

    existing_category_tag_groups = CategoryTagGroup.pluck(:category_id, :tag_group_id).to_set

    create_category_tag_groups(category_tag_groups) do |row|
      category_id = category_id_from_imported_id(row["category_id"])
      tag_group_id = @tag_group_mapping[row["tag_group_id"]]

      next unless category_id && tag_group_id
      next unless existing_category_tag_groups.add?([category_id, tag_group_id])

      { category_id: category_id, tag_group_id: tag_group_id }
    end

    category_tag_groups.close
  end

  def import_category_permissions
    puts "", "Importing category permissions..."

    permissions = query(<<~SQL)
      SELECT c.id AS category_id,
            p.value -> 'group_id' AS group_id,
            p.value -> 'existing_group_id' AS existing_group_id,
            p.value -> 'permission_type' AS permission_type
      FROM categories c,
          JSON_EACH(c.permissions) p
    SQL

    existing_category_group_ids = CategoryGroup.pluck(:category_id, :group_id).to_set

    if delta_import?
      permission_updates =
        permissions.filter_map do |row|
          category_id = category_id_from_imported_id(row["category_id"])
          # JSON `->` yields TEXT in SQLite; keep IDs comparable with pluck results
          group_id = row["existing_group_id"]&.to_i || group_id_from_imported_id(row["group_id"])
          next unless category_id && group_id && !row["permission_type"].nil?
          next if existing_category_group_ids.exclude?([category_id, group_id])

          { category_id: category_id, group_id: group_id, permission_type: row["permission_type"] }
        end
      result =
        update_records(
          permission_updates,
          "category_group",
          [:permission_type],
          keys: %i[category_id group_id],
        )
      @delta_stats[:categories][:updated_ids].merge(
        result[:updated_keys].map { |key| key.first.to_i },
      )
      permissions.close
      permissions = query(<<~SQL)
        SELECT c.id AS category_id,
              p.value -> 'group_id' AS group_id,
              p.value -> 'existing_group_id' AS existing_group_id,
              p.value -> 'permission_type' AS permission_type
        FROM categories c,
            JSON_EACH(c.permissions) p
      SQL
    end

    create_category_groups(permissions) do |row|
      category_id = category_id_from_imported_id(row["category_id"])
      group_id = row["existing_group_id"]&.to_i || group_id_from_imported_id(row["group_id"])
      next if existing_category_group_ids.include?([category_id, group_id])

      { category_id: category_id, group_id: group_id, permission_type: row["permission_type"] }
    end

    permissions.close
  end

  def import_category_moderation_groups
    puts "", "Importing category moderation groups..."

    moderation_groups = query(<<~SQL)
      SELECT c.id AS category_id,
            m.value AS group_id
      FROM categories c,
           JSON_EACH(c.moderation_group_ids) m
      ORDER BY c.id, m.value
    SQL

    existing_moderation_groups = CategoryModerationGroup.pluck(:category_id, :group_id).to_set

    create_category_moderation_groups(moderation_groups) do |row|
      category_id = category_id_from_imported_id(row["category_id"])
      group_id = group_id_from_imported_id(row["group_id"])

      next unless category_id && group_id
      next unless existing_moderation_groups.add?([category_id, group_id])

      { category_id: category_id, group_id: group_id }
    end

    moderation_groups.close
  end

  def import_category_users
    puts "", "Importing category users..."

    category_users = query(<<~SQL)
      SELECT *
        FROM category_users
       ORDER BY CASE notification_level WHEN 3 THEN 0 WHEN 4 THEN 1
                     WHEN 2 THEN 2 WHEN 1 THEN 3 WHEN 0 THEN 4 ELSE 5 END,
                category_id, user_id
    SQL

    existing_category_user_ids = CategoryUser.pluck(:category_id, :user_id).to_set

    create_category_users(category_users) do |row|
      category_id = category_id_from_imported_id(row["category_id"])
      user_id = user_id_from_imported_id(row["user_id"])
      next unless category_id && user_id
      next unless existing_category_user_ids.add?([category_id, user_id])

      {
        category_id: category_id,
        user_id: user_id,
        notification_level: row["notification_level"],
        last_seen_at: to_datetime(row["last_seen_at"]),
      }
    end

    category_users.close
  end

  def update_category_read_restricted
    puts "", "Updating category read_restricted flags..."
    start_time = Time.now
    processed_count = 0
    updated_count = 0
    skipped_count = 0

    Category
      .includes(category_groups: :group)
      .find_each do |category|
        processed_count += 1

        permissions = {}
        category.category_groups.each do |category_group|
          group = category_group.group
          permissions[category_group.group_name] = category_group.permission_type if group.present?
        end

        expected_read_restricted =
          if permissions.empty?
            false
          else
            Category.resolve_permissions(permissions).first
          end

        current_read_restricted = category.read_restricted

        if current_read_restricted != expected_read_restricted
          category.update_column(:read_restricted, expected_read_restricted)
          Jobs.enqueue(:update_category_upload_security, category_id: category.id)
          updated_count += 1
        else
          skipped_count += 1
        end
      end

    puts "  Update took #{(Time.now - start_time).to_i} seconds. Processed: #{processed_count}, Updated: #{updated_count}, Skipped: #{skipped_count}."
  end

  def import_groups
    puts "", "Importing groups..."

    start_delta_entity(:groups, "groups", :groups, @last_group_id)
    update_delta_groups if delta_import?

    groups = query(<<~SQL)
      SELECT *
      FROM groups
      ORDER BY id
    SQL

    create_groups(groups) do |row|
      next if group_id_from_imported_id(row["id"]).present?

      {
        imported_id: row["id"],
        existing_id: row["existing_id"],
        name: row["name"],
        full_name: row["full_name"],
        public_admission: row["public_admission"] || false,
        public_exit: row["public_exit"] || false,
        allow_membership_requests: row["allow_membership_requests"] || false,
        visibility_level: row["visibility_level"],
        members_visibility_level: row["members_visibility_level"],
        mentionable_level: row["mentionable_level"],
        messageable_level: row["messageable_level"],
        assignable_level: row["assignable_level"],
      }
    end

    groups.close
    finish_delta_entity(:groups, :groups)
  end

  def update_delta_groups
    columns = %i[
      name
      full_name
      title
      bio_raw
      bio_cooked
      public_admission
      public_exit
      allow_membership_requests
      visibility_level
      members_visibility_level
      mentionable_level
      messageable_level
    ]
    columns << :assignable_level if GROUP_COLUMNS.include?(:assignable_level)
    rows = query("SELECT * FROM groups ORDER BY id")
    updates =
      rows.filter_map do |row|
        discourse_id = delta_update_mapping(:groups)[row["id"].to_i]
        next unless discourse_id
        next if row["existing_id"].present?

        update = { id: discourse_id }
        columns.each { |column| update[column] = row[column.to_s] }
        if update[:name].present?
          fixed_name = fix_name(update[:name])
          if fixed_name.present?
            update[:name] = fixed_name
          else
            update.delete(:name)
            log_import_issue(
              "group name sanitized to blank, name kept",
              "group #{row["id"]} #{row["name"].inspect}",
            )
          end
        end
        update[:bio_raw] = row["description"] unless row["description"].nil?
        update[:bio_cooked] = pre_cook(update[:bio_raw]) unless update[:bio_raw].nil?
        update
      end
    result = update_records(updates, "group", columns)
    record_delta_updates(:groups, result)
    Group
      .where(id: updates.map { |update| update[:id] })
      .pluck(:name)
      .each { |name| @group_names_lower << User.normalize_username(name) }
  ensure
    rows&.close
  end

  def import_group_members
    puts "", "Importing group members..."

    group_members = query(<<~SQL)
      SELECT *
      FROM group_members
      ORDER BY ROWID
    SQL

    existing_group_user_ids = GroupUser.pluck(:group_id, :user_id).to_set

    if delta_import?
      ownership_updates =
        group_members.filter_map do |row|
          group_id = group_id_from_imported_id(row["group_id"])
          user_id = user_id_from_imported_id(row["user_id"])
          next unless group_id && user_id && !row["owner"].nil?
          next if existing_group_user_ids.exclude?([group_id, user_id])

          { group_id: group_id, user_id: user_id, owner: row["owner"] }
        end
      update_records(ownership_updates, "group_user", [:owner], keys: %i[group_id user_id])
      group_members.close
      group_members = query("SELECT * FROM group_members ORDER BY ROWID")
    end

    create_group_users(group_members) do |row|
      group_id = group_id_from_imported_id(row["group_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next if user_id.nil?
      next if group_id.nil?
      next unless existing_group_user_ids.add?([group_id, user_id])

      { group_id: group_id, user_id: user_id, owner: row["owner"] }
    end

    group_members.close
  end

  def import_users
    puts "", "Importing users..."

    start_delta_entity(:users, "users", :users, @last_user_id)
    if delta_import?
      merge_delta_user_aliases
      update_delta_users
    end

    query("SELECT id, username, email, sso_record, anonymized FROM users ORDER BY id") do |rows|
      reserve_valid_usernames(rows)
    end

    users = query(<<~SQL)
      SELECT *
      FROM users
      ORDER BY id
    SQL

    create_users(users) do |row|
      next if user_id_from_imported_id(row["id"]).present?

      if row["suspension"].present?
        suspension = JSON.parse(row["suspension"])
        suspended_at = suspension["suspended_at"]
        suspended_till = suspension["suspended_till"]
      end

      if row["anonymized"] == 1
        row["username"] = "anon_#{anon_username_suffix}"
        row["email"] = "#{row["username"]}#{UserAnonymizer::EMAIL_SUFFIX}"
        row["name"] = nil
        row["ip_address"] = nil
        row["registration_ip_address"] = nil
        row["date_of_birth"] = nil
        row["title"] = nil
        row["sso_record"] = nil
      end

      sso_record = JSON.parse(row["sso_record"]) if row["sso_record"].present?

      {
        imported_id: row["id"],
        username: row["username"],
        original_username: row["original_username"],
        name: row["name"],
        email: row["email"],
        locale: row["locale"],
        external_id: sso_record&.fetch("external_id", nil),
        persist_imported_username: row["anonymized"] != 1,
        created_at: to_datetime(row["created_at"]),
        staged: row["staged"],
        last_seen_at: to_datetime(row["last_seen_at"]),
        admin: row["admin"],
        moderator: row["moderator"],
        approved: row["approved"].nil? ? nil : row["approved"] == 1,
        approved_at: to_datetime(row["approved_at"]),
        approved_by_id: user_id_from_imported_id(row["approved_by_id"]),
        suspended_at: suspended_at,
        suspended_till: suspended_till,
        trust_level: row["trust_level"],
        manual_locked_trust_level: row["manual_locked_trust_level"],
        ip_address: row["ip_address"],
        registration_ip_address: row["registration_ip_address"],
        date_of_birth: to_date(row["date_of_birth"]),
        primary_group_id: group_id_from_imported_id(row["primary_group_id"]),
        title: row["title"],
      }
    end

    users.close
    finish_delta_entity(:users, :users)
  end

  # Lets each source username that is already valid claim its name before an
  # earlier row's sanitized username can take it, so only the sanitized one
  # receives a dedup suffix. Rows that process_user will map onto an existing
  # user by email or external ID reserve nothing.
  def reserve_valid_usernames(rows)
    @reserved_usernames = {}
    emails = Set.new
    external_ids = Set.new

    rows.each do |row|
      next if user_id_from_imported_id(row["id"]).present? || row["anonymized"] == 1

      if (email = row["email"].presence&.downcase)
        next if @emails.key?(email) || emails.include?(email)
      end

      external_id = JSON.parse(row["sso_record"])["external_id"] if row["sso_record"].present?
      if external_id.present?
        next if @external_ids.key?(external_id) || external_ids.include?(external_id)
      end

      emails.add(email) if email
      external_ids.add(external_id) if external_id.present?

      username = row["username"]
      next if username.blank? || fix_name(username) != username

      @reserved_usernames[User.normalize_username(username)] ||= row["id"].to_i
    end
  end

  def update_delta_users
    destination_users = User.unscoped.where(id: delta_update_mapping(:users).values).index_by(&:id)
    columns = %i[
      username
      username_lower
      name
      locale
      title
      staged
      admin
      moderator
      trust_level
      manual_locked_trust_level
      primary_group_id
      suspended_at
      suspended_till
      last_seen_at
    ]
    rows = query("SELECT * FROM users ORDER BY id")
    updates = []
    email_updates = []
    primary_emails =
      UserEmail.where(user_id: delta_update_mapping(:users).values, primary: true).index_by(
        &:user_id
      )

    rows.each do |row|
      discourse_id = delta_update_mapping(:users)[row["id"].to_i]
      next unless discourse_id
      next if row["anonymized"] == 1

      destination_user = destination_users[discourse_id]
      update = { id: discourse_id }
      if row["username"].present? &&
           !@delta_username_conflict_source_ids.include?(row["id"].to_i) &&
           !delta_username_unchanged?(row, discourse_id, destination_user&.username_lower) &&
           (username = fix_name(row["username"])).present?
        update[:username] = username
        update[:username_lower] = User.normalize_username(username)
      end
      %i[
        name
        locale
        title
        staged
        admin
        moderator
        trust_level
        manual_locked_trust_level
      ].each { |column| update[column] = row[column.to_s] }
      if row["primary_group_id"].present?
        update[:primary_group_id] = group_id_from_imported_id(row["primary_group_id"])
      end
      if row["last_seen_at"].present?
        source_last_seen_at = to_datetime(row["last_seen_at"])
        update[:last_seen_at] = [destination_user&.last_seen_at, source_last_seen_at].compact.max
      end

      if row["suspension"].present?
        suspension = JSON.parse(row["suspension"])
        source_start = to_datetime(suspension["suspended_at"])
        source_end = to_datetime(suspension["suspended_till"])
        update[:suspended_at] = destination_user&.suspended_at || source_start || Time.zone.now
        update[:suspended_till] = [destination_user&.suspended_till, source_end].compact.max ||
          200.years.from_now
      end
      updates << update

      if row["email"].present? && (primary_email = primary_emails[discourse_id]) &&
           !primary_email.email.casecmp?(row["email"])
        email = row["email"].downcase
        if EmailAddressValidator.valid_value?(email)
          # UserMerger may have moved the address over as a secondary email
          UserEmail.where(user_id: discourse_id, email:).where.not(id: primary_email.id).delete_all
          email_updates << { id: primary_email.id, user_id: discourse_id, email: email }
        else
          log_import_issue("invalid email update skipped", "user #{row["id"]}")
        end
      end
    end

    result = update_records(updates, "user", columns)
    record_delta_updates(:users, result)
    updated_user_ids_by_email_id = email_updates.to_h { |update| [update[:id], update[:user_id]] }
    email_result = update_records(email_updates, "user_email", [:email])
    @delta_stats[:users][:updated_ids].merge(
      email_result[:updated_keys].filter_map { |id| updated_user_ids_by_email_id[id.to_i] },
    )

    refresh_user_lookup_caches(updates.map { |update| update[:id] })
  ensure
    rows&.close
  end

  def import_user_emails
    puts "", "Importing user emails..."

    existing_user_ids = UserEmail.pluck(:user_id).to_set

    users = query(<<~SQL)
      SELECT id, email, created_at, anonymized
      FROM users
      ORDER BY id
    SQL

    create_user_emails(users) do |row|
      user_id = user_id_from_imported_id(row["id"])
      next if delta_deduplicated_source_id?(:users, row["id"])
      next unless user_id && existing_user_ids.add?(user_id)

      if row["anonymized"] == 1
        username = username_from_id(user_id)
        row["email"] = "#{username}#{UserAnonymizer::EMAIL_SUFFIX}"
      end

      { user_id: user_id, email: row["email"], created_at: to_datetime(row["created_at"]) }
    end

    users.close
  end

  def import_user_profiles
    puts "", "Importing user profiles..."

    update_delta_user_profiles if delta_import?

    users = query(<<~SQL)
      SELECT id, bio, location, website, anonymized
      FROM users
      ORDER BY id
    SQL

    existing_user_ids = UserProfile.pluck(:user_id).to_set

    create_user_profiles(users) do |row|
      user_id = user_id_from_imported_id(row["id"])
      next if delta_deduplicated_source_id?(:users, row["id"])
      next unless user_id && existing_user_ids.add?(user_id)

      if row["anonymized"] == 1
        row["bio"] = nil
        row["location"] = nil
        row["website"] = nil
      end

      { user_id: user_id, bio_raw: row["bio"], location: row["location"], website: row["website"] }
    end

    users.close
  end

  def update_delta_user_profiles
    rows = query("SELECT id, bio, location, website, anonymized FROM users ORDER BY id")
    updates =
      rows.filter_map do |row|
        user_id = delta_update_mapping(:users)[row["id"].to_i]
        next unless user_id
        next if row["anonymized"] == 1

        update = {
          user_id: user_id,
          location: row["location"],
          website: row["website"],
          bio_raw: row["bio"],
        }
        update[:bio_cooked] = pre_cook(row["bio"].scrub.strip) unless row["bio"].nil?
        update
      end
    result =
      update_records(
        updates,
        "user_profile",
        %i[location website bio_raw bio_cooked],
        keys: [:user_id],
      )
    @delta_stats[:users][:updated_ids].merge(result[:updated_keys].map(&:to_i))
  ensure
    rows&.close
  end

  def import_user_options
    puts "", "Importing user options..."

    update_delta_user_options if delta_import?

    users_columns = table_column_names("users")
    hide_profile_columns = %w[hide_profile_and_presence hide_profile hide_presence]
    available_hide_profile_columns =
      hide_profile_columns.select { |column| users_columns.include?(column) }
    select_hide_profile_columns =
      hide_profile_columns.map do |column|
        available_hide_profile_columns.include?(column) ? column : "NULL AS #{column}"
      end

    users = query(<<~SQL)
      SELECT id, timezone, email_level, email_messages_level, email_digests, anonymized,
             #{select_hide_profile_columns.join(", ")}
        FROM users
       WHERE anonymized IS TRUE
          OR timezone IS NOT NULL
          OR email_level IS NOT NULL
          OR email_messages_level IS NOT NULL
          OR email_digests IS NOT NULL
          #{available_hide_profile_columns.map { |column| "OR #{column} IS NOT NULL" }.join("\n          ")}
       ORDER BY id
    SQL

    existing_user_ids = UserOption.pluck(:user_id).to_set

    create_user_options(users) do |row|
      user_id = user_id_from_imported_id(row["id"])
      next if delta_deduplicated_source_id?(:users, row["id"])
      next unless user_id && existing_user_ids.add?(user_id)

      if row["anonymized"] == 1
        row["mailing_list_mode"] = false
        row["email_digests"] = false
        row["email_level"] = UserOption.email_level_types[:never]
        row["email_messages_level"] = UserOption.email_level_types[:never]
      end

      options = {
        user_id: user_id,
        timezone: row["timezone"],
        email_level: row["email_level"],
        email_messages_level: row["email_messages_level"],
        email_digests: row["email_digests"],
        hide_profile: row["hide_profile"],
        hide_presence: row["hide_presence"],
      }
      options[:mailing_list_mode] = row["mailing_list_mode"] if !row["mailing_list_mode"].nil?
      options[:hide_profile_and_presence] = row["hide_profile_and_presence"] if !row[
        "hide_profile_and_presence"
      ].nil?

      options
    end

    users.close
  end

  def update_delta_user_options
    source_columns = table_column_names("users")
    columns = USER_OPTION_COLUMNS.reject { |column| column == :user_id }
    columns.select! { |column| source_columns.include?(column.to_s) }
    return if columns.empty?

    # the combined flag is mirrored into the split columns below, even when the
    # source schema predates the split
    columns |= %i[hide_profile hide_presence] if columns.include?(:hide_profile_and_presence)

    rows = query("SELECT * FROM users ORDER BY id")
    updates =
      rows.filter_map do |row|
        user_id = delta_update_mapping(:users)[row["id"].to_i]
        next unless user_id
        next if row["anonymized"] == 1

        update = { user_id: user_id }
        columns.each { |column| update[column] = row[column.to_s] }
        if !update[:hide_profile_and_presence].nil?
          update[:hide_profile] = update[:hide_presence] = update[:hide_profile_and_presence]
        end
        update
      end
    result = update_records(updates, "user_option", columns, keys: [:user_id])
    @delta_stats[:users][:updated_ids].merge(result[:updated_keys].map(&:to_i))
  ensure
    rows&.close
  end

  def import_user_fields
    puts "", "Importing user fields..."

    user_fields = query(<<~SQL)
      SELECT *
      FROM user_fields
      ORDER BY ROWID
    SQL

    existing_user_field_names = UserField.pluck(:name).to_set

    user_fields.each do |row|
      next if existing_user_field_names.include?(row["name"])

      # TODO: Use `id` and store it in mapping table, but for now just ignore it.
      row.delete("id")
      options = row.delete("options")
      field = UserField.create!(row)

      if options.present?
        JSON.parse(options).each { |option| field.user_field_options.create!(value: option) }
      end
    end

    user_fields.close
  end

  def import_user_field_values
    puts "", "Importing user field values..."

    discourse_field_mapping = UserField.pluck(:name, :id).to_h

    user_fields = query("SELECT id, name FROM user_fields")

    field_id_mapping =
      user_fields
        .map do |row|
          discourse_field_id = discourse_field_mapping[row["name"]]
          field_name = "#{User::USER_FIELD_PREFIX}#{discourse_field_id}"
          [row["id"], field_name]
        end
        .to_h

    user_fields.close

    update_delta_user_field_values(field_id_mapping) if delta_import?

    values = query(<<~SQL)
      SELECT v.*
        FROM user_field_values v
             JOIN users u ON v.user_id = u.id
       WHERE u.anonymized IS NOT TRUE
    SQL

    existing_user_fields =
      UserCustomField.where("name LIKE '#{User::USER_FIELD_PREFIX}%'").pluck(:user_id, :name).to_set

    create_user_custom_fields(values) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      field_name = field_id_mapping[row["field_id"]]
      next if user_id && field_name && existing_user_fields.include?([user_id, field_name])

      { user_id: user_id, name: field_name, value: row["value"] }
    end

    values.close
  end

  def update_delta_user_field_values(field_id_mapping)
    rows = query(<<~SQL)
      SELECT v.*
        FROM user_field_values v
             JOIN users u ON v.user_id = u.id
       WHERE u.anonymized IS NOT TRUE
    SQL
    updates_by_key = {}
    rows.each do |row|
      next if row["value"].nil?

      user_id = delta_update_mapping(:users)[row["user_id"].to_i]
      field_name = field_id_mapping[row["field_id"]]
      next unless user_id && field_name

      update = { user_id: user_id, name: field_name, value: row["value"] }
      updates_by_key[[user_id, field_name]] = update
    end
    result =
      update_records(updates_by_key.values, "user_custom_field", [:value], keys: %i[user_id name])
    @delta_stats[:users][:updated_ids].merge(result[:updated_keys].map { |key| key.first.to_i })
  ensure
    rows&.close
  end

  def import_single_sign_on_records
    puts "", "Importing SSO records..."

    users = query(<<~SQL)
      SELECT id, sso_record
      FROM users
      WHERE sso_record IS NOT NULL
        AND anonymized IS NOT TRUE
      ORDER BY id
    SQL

    existing_user_ids = SingleSignOnRecord.pluck(:user_id).to_set

    create_single_sign_on_records(users) do |row|
      user_id = user_id_from_imported_id(row["id"])
      next unless user_id && existing_user_ids.add?(user_id)

      sso_record = JSON.parse(row["sso_record"], symbolize_names: true)
      sso_record[:user_id] = user_id
      sso_record
    end

    users.close
  end

  def import_user_associated_accounts
    puts "", "Importing user associated accounts..."

    update_delta_user_associated_accounts if delta_import?

    accounts = query(<<~SQL)
      SELECT a.*, COALESCE(u.last_seen_at, u.created_at) AS last_used_at, u.email, u.username
        FROM user_associated_accounts a
             JOIN users u ON u.id = a.user_id
       WHERE u.anonymized IS NOT TRUE
       ORDER BY a.user_id, a.provider_name
    SQL

    existing_user_ids = UserAssociatedAccount.pluck(:user_id).to_set
    existing_provider_uids = UserAssociatedAccount.pluck(:provider_uid, :provider_name).to_set

    create_user_associated_accounts(accounts) do |row|
      user_id = user_id_from_imported_id(row["user_id"])

      next if user_id && existing_user_ids.include?(user_id)
      next if existing_provider_uids.include?([row["provider_uid"], row["provider_name"]])

      {
        user_id: user_id,
        provider_name: row["provider_name"],
        provider_uid: row["provider_uid"],
        last_used: to_datetime(row["last_used_at"]),
        info: row["info"].presence || { nickname: row["username"], email: row["email"] }.to_json,
      }
    end

    accounts.close
  end

  # Source and destination share the identity provider, so the source's current
  # provider UID wins over whichever destination account still holds it,
  # including the userless rows UserMerger leaves behind.
  def update_delta_user_associated_accounts
    wanted_by_identity = {}
    users_by_account_id = {}
    rows = query(<<~SQL)
      SELECT a.user_id, a.provider_name, a.provider_uid
        FROM user_associated_accounts a
             JOIN users u ON u.id = a.user_id
       WHERE u.anonymized IS NOT TRUE
       ORDER BY a.user_id, a.provider_name
    SQL
    rows.each do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      wanted_by_identity[[row["provider_name"], row["provider_uid"]]] = user_id if user_id
    end

    # rows whose identity the delta gives to someone else go first, so both
    # unique indexes stay satisfied; a user left without a row is created by
    # the regular loop below, which also covers two users swapping identities
    displaced_ids = []
    current_by_user = {}
    UserAssociatedAccount
      .pluck(:id, :user_id, :provider_name, :provider_uid)
      .each do |id, user_id, provider_name, provider_uid|
        wanted_by = wanted_by_identity[[provider_name, provider_uid]]
        if wanted_by && wanted_by != user_id
          log_import_issue(
            "associated account reassigned",
            "#{provider_name}/#{provider_uid} moved from user #{user_id.inspect} to user #{wanted_by}",
          )
          displaced_ids << id
        elsif user_id
          current_by_user[[user_id, provider_name]] = [id, provider_uid]
        end
      end
    displaced_ids.each_slice(1_000) { |ids| UserAssociatedAccount.where(id: ids).delete_all }

    updates =
      wanted_by_identity.filter_map do |(provider_name, provider_uid), user_id|
        current_id, current_uid = current_by_user[[user_id, provider_name]]
        next if current_id.nil? || current_uid == provider_uid

        users_by_account_id[current_id] = user_id
        { id: current_id, provider_uid: provider_uid }
      end
    result = update_records(updates, "user_associated_account", [:provider_uid])
    @delta_stats[:users][:updated_ids].merge(
      result[:updated_keys].filter_map { |id| users_by_account_id[id.to_i] },
    )
  ensure
    rows&.close
  end

  # An authenticator that provides group claims rewrites each user's
  # associated groups on every login, and only a destroyed claim row removes
  # the user from the Discourse groups that claim granted. Imported members
  # hold group_users rows alone, so seed the claim rows a login would have
  # created for the linked groups they already belong to; their next login
  # then reconciles against the provider like any other user's. A group
  # granted by several claims seeds all of them, and the login keeps only the
  # ones the provider still returns.
  def import_user_associated_groups
    puts "", "Importing user associated groups..."

    provider_names = Discourse.enabled_authenticators.select(&:provides_groups?).map(&:name)
    if provider_names.empty?
      puts "  Skipped: no enabled authenticator provides group claims"
      return
    end

    links =
      GroupAssociatedGroup
        .joins(:associated_group)
        .where(associated_groups: { provider_name: provider_names })
        .pluck(:group_id, :associated_group_id)
    if links.empty?
      puts "  Skipped: no Discourse group is linked to a #{provider_names.join(", ")} group"
      return
    end

    associated_group_ids_by_group_id =
      links.group_by(&:first).transform_values { |pairs| pairs.map(&:last).uniq }
    imported_user_ids = @users.values.to_set
    existing_claims =
      UserAssociatedGroup
        .where(associated_group_id: links.map(&:last).uniq)
        .pluck(:user_id, :associated_group_id)
        .to_set

    memberships =
      GroupUser
        .where(group_id: associated_group_ids_by_group_id.keys)
        .order(:user_id, :group_id)
        .pluck(:user_id, :group_id)

    seeded_associated_group_ids = Set.new
    seeded_user_ids = Set.new
    claims =
      memberships.flat_map do |user_id, group_id|
        next [] if imported_user_ids.exclude?(user_id)

        associated_group_ids_by_group_id[group_id].filter_map do |associated_group_id|
          next unless existing_claims.add?([user_id, associated_group_id])

          seeded_associated_group_ids << associated_group_id
          seeded_user_ids << user_id
          { user_id: user_id, associated_group_id: associated_group_id }
        end
      end

    create_user_associated_groups(claims) { |claim| claim }

    # The daily cleanup drops associated groups unused for a week; the seeded
    # claims count as use.
    AssociatedGroup.where(id: seeded_associated_group_ids.to_a).update_all(last_used: Time.zone.now)
    puts "  Seeded #{claims.size} group claim(s) for #{seeded_user_ids.size} imported user(s)"
  end

  def import_topics
    puts "", "Importing topics..."

    start_delta_entity(:topics, "topics", :topics, @last_topic_id)
    if delta_import?
      update_delta_topics
    else
      close_existing_topics
    end

    topics = query(<<~SQL)
      SELECT *
      FROM topics
      ORDER BY id
    SQL

    create_topics(topics) do |row|
      category_id = category_id_from_imported_id(row["category_id"]) if row["category_id"].present?

      next if topic_id_from_imported_id(row["id"]).present?
      next if row["private_message"].blank? && category_id.nil?

      {
        archetype:
          (
            if row["private_message"]
              Archetype.private_message
            else
              Archetype.default
            end
          ),
        imported_id: row["id"],
        title: row["title"],
        user_id: user_id_from_imported_id(row["user_id"]),
        created_at: to_datetime(row["created_at"]),
        category_id: category_id,
        closed: to_boolean(row["closed"]),
        archived: to_boolean(row["archived"]),
        views: row["views"],
        subtype: row["subtype"],
        pinned_at: to_datetime(row["pinned_at"]),
        pinned_until: to_datetime(row["pinned_until"]),
        pinned_globally: to_boolean(row["pinned_globally"]),
      }
    end

    topics.close
    finish_delta_entity(:topics, :topics)
  end

  # Under MERGE_IMPORT the imported-id maps are cleared to avoid cross-source
  # collisions, so this pass maps nothing and is a deliberate no-op there.
  def close_existing_topics
    closed_topics = query(<<~SQL)
      SELECT id
      FROM topics
      WHERE closed = 1
      ORDER BY id
    SQL
    mapped_topic_ids = closed_topics.filter_map { |row| topic_id_from_imported_id(row["id"]) }.uniq
    closed_topics.close

    mapped_topic_ids.each_slice(1_000) do |topic_ids|
      Topic.where(id: topic_ids, closed: false).update_all(closed: true)
    end
  end

  def update_delta_topics
    source_columns = table_column_names("topics")
    columns = %i[
      title
      fancy_title
      slug
      user_id
      category_id
      views
      closed
      archived
      pinned_at
      pinned_until
      pinned_globally
      subtype
    ]
    indexed_columns =
      Topic
        .unscoped
        .where(id: delta_update_mapping(:topics).values)
        .pluck(:id, :title, :category_id)
        .to_h { |id, title, category_id| [id, [title, category_id]] }
    reindex_ids = []
    rows = query("SELECT * FROM topics ORDER BY id")
    updates =
      rows.filter_map do |row|
        discourse_id = delta_update_mapping(:topics)[row["id"].to_i]
        next unless discourse_id

        update = { id: discourse_id }
        if row["title"].present?
          update[:title] = row["title"][0...255].scrub.strip
          update[:fancy_title] = (
            if source_columns.include?("fancy_title") && row["fancy_title"].present?
              row["fancy_title"]
            else
              pre_fancy(update[:title])
            end
          )
          update[:slug] = (
            if source_columns.include?("slug") && row["slug"].present?
              row["slug"]
            else
              Slug.for(update[:title])
            end
          )
        end
        update[:user_id] = user_id_from_imported_id(row["user_id"]) if row["user_id"].present?
        if row["category_id"].present?
          update[:category_id] = category_id_from_imported_id(row["category_id"])
        end
        update[:views] = row["views"] if source_columns.include?("views") && !row["views"].nil?
        %i[archived pinned_globally].each do |column|
          if source_columns.include?(column.to_s)
            update[column] = to_nullable_boolean(row[column.to_s])
          end
        end
        update[:closed] = true if source_columns.include?("closed") && to_boolean(row["closed"])
        update[:subtype] = row["subtype"] if source_columns.include?("subtype")
        %i[pinned_at pinned_until].each do |column|
          if source_columns.include?(column.to_s) && row[column.to_s].present?
            update[column] = to_datetime(row[column.to_s])
          end
        end
        current_title, current_category_id = indexed_columns[discourse_id]
        if (update[:title] && update[:title] != current_title) ||
             (update[:category_id] && update[:category_id] != current_category_id)
          reindex_ids << discourse_id
        end
        update
      end
    result = update_records(updates, "topic", columns)
    record_delta_updates(:topics, result)

    # direct SQL updates bypass the model callbacks that keep search in sync;
    # only the title and category feed the index, so reindex just those
    reindex_ids &= result[:updated_keys].map(&:to_i)
    reindex_ids.each_slice(1_000) do |ids|
      Topic.unscoped.where(id: ids).find_each { |topic| SearchIndexer.index(topic, force: true) }
    end
  ensure
    rows&.close
  end

  def import_topic_custom_fields
    puts "", "Importing topic custom fields..."

    upsert_delta_custom_fields(:topic, :topics) if delta_import?

    topic_custom_fields = query(<<~SQL)
      SELECT *
      FROM topic_custom_fields
      ORDER BY topic_id, name
    SQL

    field_names =
      query("SELECT DISTINCT name FROM topic_custom_fields") { it.map { |row| row["name"] } }
    existing_topic_custom_fields =
      TopicCustomField.where(name: field_names).pluck(:topic_id, :name).to_set

    create_topic_custom_fields(topic_custom_fields) do |row|
      topic_id = topic_id_from_imported_id(row["topic_id"])
      next if topic_id.nil?

      next unless existing_topic_custom_fields.add?([topic_id, row["name"]])

      { topic_id: topic_id, name: row["name"], value: row["value"] }
    end

    topic_custom_fields.close
  end

  def import_topic_allowed_users
    puts "", "Importing topic_allowed_users..."

    topics = query(<<~SQL)
      SELECT
        t.id,
        user_ids.value AS user_id
      FROM topics t, JSON_EACH(t.private_message, '$.user_ids') AS user_ids
      WHERE t.private_message IS NOT NULL
      ORDER BY t.id
    SQL

    added = 0
    existing_topic_allowed_users = TopicAllowedUser.pluck(:topic_id, :user_id).to_set

    create_topic_allowed_users(topics) do |row|
      topic_id = topic_id_from_imported_id(row["id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next unless topic_id && user_id
      next unless existing_topic_allowed_users.add?([topic_id, user_id])

      added += 1

      { topic_id: topic_id, user_id: user_id }
    end

    topics.close

    puts "  Added #{added} topic_allowed_users records."
  end

  def import_topic_allowed_groups
    puts "", "Importing topic_allowed_groups..."

    topics = query(<<~SQL)
      SELECT
        t.id,
        group_ids.value AS group_id
      FROM topics t, JSON_EACH(t.private_message, '$.group_ids') AS group_ids
      WHERE t.private_message IS NOT NULL
      ORDER BY t.id
    SQL

    added = 0
    existing_topic_allowed_groups = TopicAllowedGroup.pluck(:topic_id, :group_id).to_set

    create_topic_allowed_groups(topics) do |row|
      topic_id = topic_id_from_imported_id(row["id"])
      group_id = group_id_from_imported_id(row["group_id"])

      next unless topic_id && group_id
      next unless existing_topic_allowed_groups.add?([topic_id, group_id])

      added += 1

      { topic_id: topic_id, group_id: group_id }
    end

    # TODO: Add support for special group names

    topics.close

    puts "  Added #{added} topic_allowed_groups records."
  end

  def import_posts
    puts "", "Importing posts..."

    start_delta_entity(:posts, "posts", :posts, @last_post_id)
    load_event_lookups
    update_delta_posts if delta_import?

    posts = query(<<~SQL)
      SELECT *
      FROM posts
      ORDER BY topic_id, post_number, id
    SQL

    create_posts(posts) do |row|
      next if row["raw"].blank?
      next unless (topic_id = topic_id_from_imported_id(row["topic_id"]))
      next if post_id_from_imported_id(row["id"]).present?

      {
        imported_id: row["id"],
        topic_id: topic_id,
        user_id: user_id_from_imported_id(row["user_id"]),
        created_at: to_datetime(row["created_at"]),
        raw: raw_with_placeholders_interpolated(row["raw"], row),
        like_count: row["like_count"],
        reply_to_post_number:
          (
            if row["reply_to_post_id"]
              post_number_from_imported_id(row["reply_to_post_id"])
            else
              nil
            end
          ),
      }
    end

    posts.close
    finish_delta_entity(:posts, :posts)
  end

  def update_delta_posts
    rows = query("SELECT * FROM posts ORDER BY id")
    updates =
      rows.lazy.filter_map do |row|
        discourse_id = delta_update_mapping(:posts)[row["id"].to_i]
        next unless discourse_id

        update = { id: discourse_id }
        if row["user_id"].present?
          update[:user_id] = user_id_from_imported_id(row["user_id"])
          update[:last_editor_id] = update[:user_id]
        end
        unless row["raw"].nil?
          raw = raw_with_placeholders_interpolated(row["raw"], row)
          if (raw = prepare_delta_post_raw(raw, original_id: row["id"]))
            update[:raw] = raw
            update[:word_count] = raw.scan(/[[:word:]]+/).size
          end
        end
        update
      end
    result = update_records(updates, "post", %i[user_id last_editor_id raw word_count])
    record_delta_updates(:posts, result)

    # cooked content is regenerated by the post-delta rebake; clearing the
    # marker scopes that rebake to the posts this delta touched
    result[:updated_keys].each_slice(10_000) do |ids|
      DB.exec("UPDATE posts SET baked_version = NULL WHERE id IN (#{ids.join(",")})")
    end
  ensure
    rows&.close
  end

  def prepare_delta_post_raw(raw, original_id:)
    raw = raw.scrub.strip.presence || "<Empty imported post>"
    raw = process_raw(raw)
    if @bbcode_to_md
      raw =
        begin
          raw.bbcode_to_md(false, {}, :disable, :quote)
        rescue StandardError
          raw
        end
    end
    raw = normalize_text(raw)

    if raw.bytes.include?(0)
      log_import_issue("raw update skipped (raw contains null bytes)", "post #{original_id}")
      return nil
    end

    raw
  end

  def group_id_name_map
    @group_id_name_map ||= Group.pluck(:id, :name).to_h
  end

  def raw_with_placeholders_interpolated(raw, row)
    raw = raw.dup
    placeholders = row["placeholders"]&.then { |json| JSON.parse(json) }

    if (polls = placeholders&.fetch("polls", nil))
      poll_mapping = polls.map { |poll| [poll["poll_id"], poll["placeholder"]] }.to_h

      poll_details = query(<<~SQL, { post_id: row["id"] })
        SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.post_id, p.name ORDER BY p.id) AS seq,
               JSON_GROUP_ARRAY(DISTINCT TRIM(po.text)) AS options
          FROM polls p
               JOIN poll_options po ON p.id = po.poll_id
         WHERE p.post_id = :post_id
         ORDER BY p.id, po.position, po.id
      SQL

      poll_details.each do |poll|
        if (placeholder = poll_mapping.delete(poll["id"]))
          raw.gsub!(placeholder, poll_bbcode(poll))
        end
      end

      poll_details.close

      # Remove placeholders for polls without options
      poll_mapping.each_value { |placeholder| raw.gsub!(placeholder, "") }
    end

    if (mentions = placeholders&.fetch("mentions", nil))
      mentions.each do |mention|
        name = resolve_mentioned_name(mention)

        unless name
          log_import_issue(
            "unresolved #{mention["type"]} mention",
            "#{mention["placeholder"]} (content #{row["id"]})",
          )
        end
        raw.gsub!(mention["placeholder"], " @#{name} ")
      end
    end

    if (event = placeholders&.fetch("event", nil))
      event_details = @source_db.get_first_row(<<~SQL, { event_id: event["event_id"] })
        SELECT *
          FROM events
         WHERE id = :event_id
      SQL

      raw.gsub!(event["placeholder"], event_bbcode(event_details).to_s) if event_details
    end

    if (quotes = placeholders&.fetch("quotes", nil))
      quotes.each do |quote|
        user_id =
          if quote["user_id"]
            user_id_from_imported_id(quote["user_id"])
          elsif quote["username"]
            user_id_from_original_username(quote["username"])
          end

        username = quote["username"]
        name = nil

        if user_id
          username = username_from_id(user_id)
          name = user_full_name_from_id(user_id)
        end

        if quote["post_id"]
          topic_id = topic_id_from_imported_post_id(quote["post_id"])
          post_number = post_number_from_imported_id(quote["post_id"])
        end

        bbcode =
          if username.blank? && name.blank?
            "[quote]"
          else
            bbcode_parts = []
            bbcode_parts << (name.presence || username)
            bbcode_parts << "post:#{post_number}" if post_number.present?
            bbcode_parts << "topic:#{topic_id}" if topic_id.present?
            bbcode_parts << "username:#{username}" if username.present? && name.present?

            %Q|[quote="#{bbcode_parts.join(", ")}"]|
          end

        raw.gsub!(quote["placeholder"], bbcode)
      end
    end

    if (links = placeholders&.fetch("links", nil))
      links.each do |link|
        text = link["text"]
        original_url = link["url"]

        markdown =
          if link["topic_id"]
            topic_id = topic_id_from_imported_id(link["topic_id"])
            url = topic_id ? "#{Discourse.base_url}/t/#{topic_id}" : original_url
            text ? "[#{text}](#{url})" : url
          elsif link["post_id"]
            topic_id = topic_id_from_imported_post_id(link["post_id"])
            post_number = post_number_from_imported_id(link["post_id"])
            url =
              (
                if topic_id && post_number
                  "#{Discourse.base_url}/t/#{topic_id}/#{post_number}"
                else
                  original_url
                end
              )
            text ? "[#{text}](#{url})" : url
          else
            text ? "[#{text}](#{original_url})" : original_url
          end

        # ensure that the placeholder is surrounded by whitespace unless it's at the beginning or end of the string
        placeholder = link["placeholder"]
        escaped_placeholder = Regexp.escape(placeholder)
        raw.gsub!(/(?<!\s)#{escaped_placeholder}/, " #{placeholder}")
        raw.gsub!(/#{escaped_placeholder}(?!\s)/, "#{placeholder} ")

        raw.gsub!(placeholder, markdown)
      end
    end

    if row["upload_ids"].present? && @uploads_db
      upload_ids = JSON.parse(row["upload_ids"])
      upload_ids_placeholders = (["?"] * upload_ids.size).join(",")

      query(
        "SELECT id, markdown FROM uploads WHERE id IN (#{upload_ids_placeholders})",
        upload_ids,
        db: @uploads_db,
      ).tap do |result_set|
        result_set.each { |upload| raw.gsub!("[upload|#{upload["id"]}]", upload["markdown"] || "") }
        result_set.close
      end
    end

    raw
  end

  def resolve_mentioned_name(mention)
    # NOTE: original_id lookup order is important until post and chat mentions are unified
    original_id = mention["target_id"] || mention["id"]
    name = mention["name"]

    case mention["type"]
    when "user", "Chat::UserMention"
      resolved_user_name(original_id, name)
    when "group", "Chat::GroupMention"
      resolved_group_name(original_id, name)
    when "Chat::HereMention"
      "here"
    when "Chat::AllMention"
      "all"
    end
  end

  def resolved_user_name(original_id, name)
    user_id =
      if original_id
        user_id_from_imported_id(original_id)
      elsif name
        user_id_from_original_username(name)
      end

    user_id ? username_from_id(user_id) : name
  end

  def resolved_group_name(original_id, name)
    group_id = group_id_from_imported_id(original_id) if original_id

    group_id ? group_id_name_map[group_id] : name
  end

  def process_raw(original_raw)
    original_raw
  end

  def poll_name(row)
    name = +(row["name"] || "poll")
    name << "-#{row["seq"]}" if row["seq"] > 1
    name
  end

  def poll_bbcode(row)
    return unless defined?(::Poll)

    name = poll_name(row)
    type = ::Poll.types.key(row["type"])
    regular_type = type == ::Poll.types[:regular]
    number_type = type == ::Poll.types[:number]
    result_visibility = ::Poll.results.key(row["results"])
    min = row["min"]
    max = row["max"]
    step = row["step"]
    visibility = row["visibility"]
    chart_type = ::Poll.chart_types.key(row["chart_type"])
    groups = row["groups"]
    auto_close = to_datetime(row["close_at"])
    title = row["title"]
    options = JSON.parse(row["options"])

    text = +"[poll"
    text << " name=#{name}" if name != "poll"
    text << " type=#{type}"
    text << " results=#{result_visibility}"
    text << " min=#{min}" if min && !regular_type
    text << " max=#{max}" if max && !regular_type
    text << " step=#{step}" if step && !number_type
    text << " public=true" if visibility == Poll.visibilities[:everyone]
    text << " chartType=#{chart_type}" if chart_type.present? && !regular_type
    text << " groups=#{groups.join(",")}" if groups.present?
    text << " close=#{auto_close.utc.iso8601}" if auto_close
    text << "]\n"
    text << "# #{title}\n" if title.present?
    text << options.map { |o| "* #{o}" }.join("\n") if options.present? && !number_type
    text << "\n[/poll]\n"
    text
  end

  def event_bbcode(event)
    return unless defined?(DiscourseEvents::Events)

    starts_at = to_datetime(event["starts_at"])
    ends_at = to_datetime(event["ends_at"])
    timezone = event_timezone(event)
    status = DiscourseEvents::Events::Event.statuses[event["status"]].to_s
    name =
      if (name = event["name"].presence)
        name.ljust(DiscourseEvents::Events::Event::MIN_NAME_LENGTH, ".").truncate(
          DiscourseEvents::Events::Event::MAX_NAME_LENGTH,
        )
      end
    url = event["url"]
    location = event_location(event)
    allowed_groups = event_allowed_group_names(event)
    image = @event_image_urls_by_upload_id[event_image_upload_id(event)]
    custom_fields = event["custom_fields"] ? JSON.parse(event["custom_fields"]) : nil

    text = +"[event"
    # The plugin reads start and end as local times in the event's timezone.
    text << %{ start="#{format_event_time(starts_at, timezone)}"} if starts_at
    text << %{ end="#{format_event_time(ends_at, timezone)}"} if ends_at
    text << %{ timezone="#{timezone}"}
    text << %{ status="#{status}"} if status
    text << bbcode_attribute("name", name) if name
    text << bbcode_attribute("url", url) if url
    text << bbcode_attribute("location", location) if location
    text << %{ allowed-groups="#{allowed_groups.join(",")}"} if allowed_groups.present?
    text << %{ image="#{image}"} if image
    text << %{ closed="true"} if to_boolean(event["closed"])
    custom_fields&.each { |key, value| text << bbcode_attribute(key, value) }
    text << "]\n"
    text << "[/event]\n"
    text
  end

  def event_timezone(event)
    timezone = event["timezone"].presence
    timezone && ActiveSupport::TimeZone[timezone] ? timezone : "UTC"
  end

  def format_event_time(time, timezone)
    time.in_time_zone(timezone).strftime("%Y-%m-%d %H:%M")
  end

  # The plugin only keeps invited groups on private events. Public events invite
  # everyone and standalone events nobody.
  def event_allowed_group_names(event)
    return [] if event["status"] != DiscourseEvents::Events::Event.statuses[:private]

    event_allowed_group_ids(event).filter_map { |id| @group_names_by_id[id] }
  end

  def event_allowed_group_ids(event)
    return [] if event["allowed_group_ids"].blank?

    JSON.parse(event["allowed_group_ids"]).filter_map { |id| group_id_from_imported_id(id) }
  end

  # Events are converted while rows are copied, when the connection can't run queries.
  def load_event_lookups
    return unless defined?(DiscourseEvents::Events)

    @group_names_by_id = Group.pluck(:id, :name).to_h

    upload_ids =
      if table_column_names("events").include?("image_upload_id")
        query(
          "SELECT DISTINCT image_upload_id FROM events WHERE image_upload_id IS NOT NULL",
        ) { |rows| rows.filter_map { |row| upload_id_from_original_id(row["image_upload_id"]) } }
      else
        []
      end
    @event_image_urls_by_upload_id =
      Upload.where(id: upload_ids).to_h { |upload| [upload.id, upload.short_url] }
  end

  def log_event_allowed_group_issues(event)
    return if event["allowed_group_ids"].blank?

    if event["status"] != DiscourseEvents::Events::Event.statuses[:private]
      log_import_issue("event allowed groups ignored (event isn't private)", "event #{event["id"]}")
      return
    end

    missing = JSON.parse(event["allowed_group_ids"]).reject { |id| group_id_from_imported_id(id) }
    return if missing.empty?

    log_import_issue(
      "event allowed groups not found",
      "#{missing.join(", ")} (event #{event["id"]})",
    )
  end

  def event_image_upload_id(event)
    upload_id_from_original_id(event["image_upload_id"]) if event["image_upload_id"].present?
  end

  # Line breaks would end the BBCode tag, and the plugin stores at most 1000 characters.
  def event_location(event)
    event["location"]&.squish.presence&.[](0..999)
  end

  # Picks a quote pair that doesn't occur in the value, so it needs no escaping.
  def bbcode_attribute(name, value)
    value = value.to_s
    marks = BBCODE_QUOTATION_MARKS.find { |pair| pair.chars.none? { |mark| value.include?(mark) } }
    return %{ #{name}="#{value.delete('"')}"} unless marks

    %{ #{name}=#{marks[0]}#{value}#{marks[1]}}
  end

  def import_post_custom_fields
    puts "", "Importing post custom fields..."

    upsert_delta_custom_fields(:post, :posts) if delta_import?

    post_custom_fields = query(<<~SQL)
      SELECT *
      FROM post_custom_fields
      ORDER BY post_id, name
    SQL

    field_names =
      query("SELECT DISTINCT name FROM post_custom_fields") { it.map { |row| row["name"] } }
    existing_post_custom_fields =
      PostCustomField.where(name: field_names).pluck(:post_id, :name).to_set

    create_post_custom_fields(post_custom_fields) do |row|
      post_id = post_id_from_imported_id(row["post_id"])
      next if post_id.nil?

      next if existing_post_custom_fields.include?([post_id, row["name"]])

      { post_id: post_id, name: row["name"], value: row["value"] }
    end

    post_custom_fields.close
  end

  def import_polls
    unless defined?(::Poll)
      puts "", "Skipping polls, because the poll plugin is not installed."
      return
    end

    puts "", "Importing polls..."

    polls = query(<<~SQL)
      SELECT *, ROW_NUMBER() OVER (PARTITION BY post_id, name ORDER BY id) AS seq
        FROM polls
       ORDER BY id
    SQL

    create_polls(polls) do |row|
      next if poll_id_from_original_id(row["id"]).present?

      post_id = post_id_from_imported_id(row["post_id"])
      next unless post_id

      {
        original_id: row["id"],
        post_id: post_id,
        name: poll_name(row),
        closed_at: to_datetime(row["close_at"]),
        type: row["type"],
        status: row["status"],
        results: row["results"],
        visibility: row["visibility"],
        min: row["min"],
        max: row["max"],
        step: row["step"],
        anonymous_voters: row["anonymous_voters"],
        created_at: to_datetime(row["created_at"]),
        chart_type: row["chart_type"],
        groups: row["groups"],
        title: row["title"],
      }
    end

    polls.close

    puts "", "Importing polls into post custom fields..."

    polls = query(<<~SQL)
      SELECT post_id, MIN(created_at) AS created_at
        FROM polls
       GROUP BY post_id
       ORDER BY post_id
    SQL

    field_name = DiscoursePoll::HAS_POLLS
    value = "true"
    existing_fields = PostCustomField.where(name: field_name).pluck(:post_id).to_set

    create_post_custom_fields(polls) do |row|
      next unless (post_id = post_id_from_imported_id(row["post_id"]))
      next if existing_fields.include?(post_id)

      {
        post_id: post_id,
        name: field_name,
        value: value,
        created_at: to_datetime(row["created_at"]),
      }
    end

    polls.close
  end

  def import_poll_options
    unless defined?(::Poll)
      puts "", "Skipping polls, because the poll plugin is not installed."
      return
    end

    puts "", "Importing poll options..."

    poll_options = query(<<~SQL)
      SELECT poll_id, TRIM(text) AS text, MIN(created_at) AS created_at, GROUP_CONCAT(id) AS option_ids
        FROM poll_options
       GROUP BY 1, 2
       ORDER BY poll_id, position, id
    SQL

    create_poll_options(poll_options) do |row|
      poll_id = poll_id_from_original_id(row["poll_id"])
      next unless poll_id

      option_ids = row["option_ids"].split(",")
      next if option_ids.all? { |oid| poll_option_id_from_original_id(oid).present? }

      {
        original_ids: option_ids,
        poll_id: poll_id,
        html: row["text"],
        created_at: to_datetime(row["created_at"]),
      }
    end

    poll_options.close
  end

  def import_poll_votes
    unless defined?(::Poll)
      puts "", "Skipping polls, because the poll plugin is not installed."
      return
    end

    puts "", "Importing poll votes..."

    poll_votes = query(<<~SQL)
      SELECT po.poll_id, pv.poll_option_id, pv.user_id, pv.created_at
        FROM poll_votes pv
             JOIN poll_options po ON pv.poll_option_id = po.id
       ORDER BY pv.poll_option_id, pv.user_id
    SQL

    existing_poll_votes = PollVote.pluck(:poll_option_id, :user_id).to_set

    create_poll_votes(poll_votes) do |row|
      poll_id = poll_id_from_original_id(row["poll_id"])
      poll_option_id = poll_option_id_from_original_id(row["poll_option_id"])
      user_id = user_id_from_imported_id(row["user_id"])
      next unless poll_id && poll_option_id && user_id

      next unless existing_poll_votes.add?([poll_option_id, user_id])

      {
        poll_id: poll_id,
        poll_option_id: poll_option_id,
        user_id: user_id,
        created_at: row["created_at"],
      }
    end

    poll_votes.close
  end

  def import_likes
    puts "", "Importing likes..."

    likes = query(<<~SQL)
      SELECT post_id, user_id, created_at
        FROM likes
       ORDER BY post_id, user_id
    SQL

    post_action_type_id = PostActionType.types[:like]
    existing_likes =
      PostAction.where(post_action_type_id: post_action_type_id).pluck(:post_id, :user_id).to_set

    create_post_actions(likes) do |row|
      post_id = post_id_from_imported_id(row["post_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next unless post_id && user_id
      next unless existing_likes.add?([post_id, user_id])

      {
        post_id: post_id,
        user_id: user_id,
        post_action_type_id: post_action_type_id,
        created_at: to_datetime(row["created_at"]),
      }
    end

    likes.close
  end

  def import_engagement
    import_likes
    import_reactions
    import_reaction_users
    import_reaction_shadow_likes

    puts "", "Updating engagement statistics..."
    start_time = Time.now
    update_engagement_stats
    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def update_engagement_stats
    DB.exec(
      <<~SQL,
        WITH post_likes AS (
          SELECT post_actions.post_id,
                 COUNT(*) AS like_count,
                 SUM(
                   CASE
                     WHEN users.moderator OR users.admin THEN :staff_like_weight
                     ELSE 1
                   END
                 ) AS like_score
            FROM post_actions
                 LEFT JOIN users ON users.id = post_actions.user_id
           WHERE post_actions.post_action_type_id = :like_type_id
             AND post_actions.deleted_at IS NULL
           GROUP BY post_actions.post_id
        )
        UPDATE posts
           SET like_count = post_likes.like_count,
               like_score = post_likes.like_score
          FROM post_likes
         WHERE posts.id = post_likes.post_id
      SQL
      like_type_id: PostActionType::LIKE_POST_ACTION_ID,
      staff_like_weight: SiteSetting.staff_like_weight,
    )

    DB.exec(<<~SQL, whisper_post_type: Post.types[:whisper])
      WITH topic_likes AS (
        SELECT topic_id, SUM(like_count) AS like_count
          FROM posts
         WHERE deleted_at IS NULL
           AND post_type <> :whisper_post_type
         GROUP BY topic_id
      )
      UPDATE topics
         SET like_count = topic_likes.like_count
        FROM topic_likes
       WHERE topics.id = topic_likes.topic_id
         AND topics.like_count <> topic_likes.like_count
    SQL

    return unless defined?(DiscourseReactions)

    DB.exec(<<~SQL)
      UPDATE discourse_reactions_reactions
         SET reaction_users_count = (
           SELECT COUNT(*)
             FROM discourse_reactions_reaction_users
            WHERE discourse_reactions_reaction_users.reaction_id = discourse_reactions_reactions.id
         )
    SQL
  end

  def import_topic_users
    puts "", "Importing topic users..."

    topic_users = query(<<~SQL)
      SELECT *
        FROM topic_users
       ORDER BY CASE notification_level WHEN 3 THEN 0 WHEN 2 THEN 1 WHEN 1 THEN 2 WHEN 0 THEN 3 ELSE 4 END,
                user_id, topic_id
    SQL

    existing_topic_users = TopicUser.pluck(:topic_id, :user_id).to_set

    create_topic_users(topic_users) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      topic_id = topic_id_from_imported_id(row["topic_id"])
      next unless user_id && topic_id
      next unless existing_topic_users.add?([topic_id, user_id])

      {
        user_id: user_id,
        topic_id: topic_id,
        last_read_post_number: row["last_read_post_number"],
        last_visited_at: to_datetime(row["last_visited_at"]),
        first_visited_at: to_datetime(row["first_visited_at"]),
        notification_level: row["notification_level"],
        notifications_changed_at: to_datetime(row["notifications_changed_at"]),
        notifications_reason_id:
          row["notifications_reason_id"] || TopicUser.notification_reasons[:user_changed],
        total_msecs_viewed: row["total_msecs_viewed"] || 0,
      }
    end

    topic_users.close
  end

  # Mirrors Invitee#update_topic_tracking!. Runs after the imported topic users, so an
  # explicit subscription from the source wins, and before update_topic_users, which
  # keeps the level because the row has a notification reason.
  def import_event_invitee_topic_tracking
    return unless defined?(DiscourseEvents::Events)

    puts "", "Importing topic tracking of event invitees..."

    statuses = DiscourseEvents::Events::Invitee.statuses
    levels = TopicUser.notification_levels

    DB.exec(
      <<~SQL,
      INSERT INTO topic_users (user_id, topic_id, notification_level, notifications_changed_at,
                               notifications_reason_id)
      SELECT i.user_id, p.topic_id,
             CASE i.status WHEN :going THEN :watching WHEN :interested THEN :tracking ELSE :regular END,
             i.created_at, :user_changed
        FROM discourse_post_event_invitees i
             JOIN posts p ON p.id = i.post_id
       WHERE i.status IS NOT NULL
          ON CONFLICT (user_id, topic_id) DO NOTHING
    SQL
      going: statuses[:going],
      interested: statuses[:interested],
      watching: levels[:watching],
      tracking: levels[:tracking],
      regular: levels[:regular],
      user_changed: TopicUser.notification_reasons[:user_changed],
    )
  end

  def update_topic_users
    puts "", "Updating topic users..."

    start_time = Time.now

    params = {
      post_action_type_id: PostActionType.types[:like],
      msecs_viewed_per_post: 10_000,
      notification_level_topic_created: NotificationLevels.topic_levels[:watching],
      notification_level_posted: NotificationLevels.topic_levels[:tracking],
      reason_topic_created: TopicUser.notification_reasons[:created_topic],
      reason_posted: TopicUser.notification_reasons[:created_post],
    }

    # Like TopicUser.auto_notification, posting history only fills rows without a
    # notification reason. Imported subscriptions, earlier posting history and user
    # changes all carry a reason and survive this pass and later imports.
    DB.exec(<<~SQL, params)
      INSERT INTO topic_users (user_id, topic_id, posted, last_read_post_number, first_visited_at, last_visited_at,
                               notification_level, notifications_changed_at, notifications_reason_id, total_msecs_viewed,
                               last_posted_at)
      SELECT p.user_id, p.topic_id, TRUE AS posted, MAX(p.post_number) AS last_read_post_number,
             MIN(p.created_at) AS first_visited_at, MAX(p.created_at) AS last_visited_at,
             CASE WHEN MIN(p.post_number) = 1 THEN :notification_level_topic_created
                  ELSE :notification_level_posted END AS notification_level, MIN(p.created_at) AS notifications_changed_at,
             CASE WHEN MIN(p.post_number) = 1 THEN :reason_topic_created ELSE :reason_posted END AS notifications_reason_id,
             MAX(p.post_number) * :msecs_viewed_per_post AS total_msecs_viewed, MAX(p.created_at) AS last_posted_at
        FROM posts p
             JOIN topics t ON p.topic_id = t.id
       WHERE p.user_id > 0
         AND p.deleted_at IS NULL
         AND NOT p.hidden
         AND t.deleted_at IS NULL
         AND t.visible
       GROUP BY p.user_id, p.topic_id
          ON CONFLICT (user_id, topic_id) DO UPDATE SET posted = excluded.posted,
                                                        last_read_post_number = GREATEST(topic_users.last_read_post_number, excluded.last_read_post_number),
                                                        first_visited_at = LEAST(topic_users.first_visited_at, excluded.first_visited_at),
                                                        last_visited_at = GREATEST(topic_users.last_visited_at, excluded.last_visited_at),
                                                        notification_level = CASE WHEN topic_users.notifications_reason_id IS NULL
                                                                                    THEN excluded.notification_level
                                                                                  ELSE topic_users.notification_level END,
                                                        notifications_changed_at = CASE WHEN topic_users.notifications_reason_id IS NULL
                                                                                          THEN excluded.notifications_changed_at
                                                                                        ELSE topic_users.notifications_changed_at END,
                                                        notifications_reason_id = COALESCE(topic_users.notifications_reason_id, excluded.notifications_reason_id),
                                                        total_msecs_viewed = CASE WHEN topic_users.total_msecs_viewed = 0
                                                                                    THEN excluded.total_msecs_viewed
                                                                                  ELSE topic_users.total_msecs_viewed END,
                                                        last_posted_at = GREATEST(topic_users.last_posted_at, excluded.last_posted_at)
    SQL

    DB.exec(<<~SQL, params)
      INSERT INTO topic_users (user_id, topic_id, last_read_post_number, first_visited_at, last_visited_at, total_msecs_viewed, liked)
      SELECT pa.user_id, p.topic_id, MAX(p.post_number) AS last_read_post_number, MIN(pa.created_at) AS first_visited_at,
             MAX(pa.created_at) AS last_visited_at, MAX(p.post_number) * :msecs_viewed_per_post AS total_msecs_viewed,
             TRUE AS liked
        FROM post_actions pa
             JOIN posts p ON pa.post_id = p.id
             JOIN topics t ON p.topic_id = t.id
       WHERE pa.post_action_type_id = :post_action_type_id
         AND pa.user_id > 0
         AND pa.deleted_at IS NULL
         AND p.deleted_at IS NULL
         AND NOT p.hidden
         AND t.deleted_at IS NULL
         AND t.visible
       GROUP BY pa.user_id, p.topic_id
          ON CONFLICT (user_id, topic_id) DO UPDATE SET last_read_post_number = GREATEST(topic_users.last_read_post_number, excluded.last_read_post_number),
                                                        first_visited_at = LEAST(topic_users.first_visited_at, excluded.first_visited_at),
                                                        last_visited_at = GREATEST(topic_users.last_visited_at, excluded.last_visited_at),
                                                        total_msecs_viewed = CASE WHEN topic_users.total_msecs_viewed = 0
                                                                                    THEN excluded.total_msecs_viewed
                                                                                  ELSE topic_users.total_msecs_viewed END,
                                                        liked = excluded.liked
    SQL

    DB.exec(<<~SQL, notification_level: NotificationLevels.topic_levels[:watching])
      WITH
        latest_posts AS (
                          SELECT p.topic_id, MAX(p.post_number) AS number
                            FROM posts p
                          WHERE p.deleted_at IS NULL
                            AND NOT p.hidden
                            AND p.user_id > 0
                          GROUP BY p.topic_id
                        )
      UPDATE topic_users tu
        SET last_read_post_number = latest_posts.number
      FROM latest_posts
           JOIN topics t ON t.id = latest_posts.topic_id
      WHERE tu.topic_id = latest_posts.topic_id
        AND tu.notification_level = :notification_level
        AND tu.last_read_post_number IS NULL
        AND t.deleted_at IS NULL
        AND t.visible
    SQL

    puts "  Updated topic users in #{(Time.now - start_time).to_i} seconds."
  end

  def import_bookmarks
    unless @source_db.get_first_value(
             "SELECT 1 FROM sqlite_master WHERE type='table' AND name='bookmarks'",
           )
      return
    end

    puts "", "Importing bookmarks..."

    bookmarks = query(<<~SQL)
      SELECT id, user_id, bookmarkable_id, bookmarkable_type, name,
             reminder_at, reminder_set_at, reminder_last_sent_at,
             auto_delete_preference, pinned, created_at, updated_at
        FROM bookmarks
       ORDER BY user_id, bookmarkable_id
    SQL

    existing_bookmarks = Bookmark.pluck(:user_id, :bookmarkable_type, :bookmarkable_id).to_set

    create_bookmarks(bookmarks) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next unless user_id

      bookmarkable_type = row["bookmarkable_type"].to_s
      bookmarkable_id = row["bookmarkable_id"]

      if bookmarkable_type == "Topic"
        topic_id = topic_id_from_imported_id(bookmarkable_id)
        next unless topic_id
        next unless existing_bookmarks.add?([user_id, bookmarkable_type, topic_id])

        {
          user_id: user_id,
          bookmarkable_id: topic_id,
          bookmarkable_type: bookmarkable_type,
          name: row["name"],
          reminder_at: to_datetime(row["reminder_at"]),
          reminder_set_at: to_datetime(row["reminder_set_at"]),
          reminder_last_sent_at: to_datetime(row["reminder_last_sent_at"]),
          auto_delete_preference: row["auto_delete_preference"]&.to_i,
          pinned: row["pinned"].present? ? to_boolean(row["pinned"]) : false,
          created_at: to_datetime(row["created_at"]),
          updated_at: to_datetime(row["updated_at"]),
        }
      elsif bookmarkable_type == "Post"
        post_id = post_id_from_imported_id(bookmarkable_id)
        next unless post_id
        next unless existing_bookmarks.add?([user_id, bookmarkable_type, post_id])

        {
          user_id: user_id,
          bookmarkable_id: post_id,
          bookmarkable_type: bookmarkable_type,
          name: row["name"],
          reminder_at: to_datetime(row["reminder_at"]),
          reminder_set_at: to_datetime(row["reminder_set_at"]),
          reminder_last_sent_at: to_datetime(row["reminder_last_sent_at"]),
          auto_delete_preference: row["auto_delete_preference"]&.to_i,
          pinned: row["pinned"].present? ? to_boolean(row["pinned"]) : false,
          created_at: to_datetime(row["created_at"]),
          updated_at: to_datetime(row["updated_at"]),
        }
      else
        next
      end
    end

    bookmarks.close

    puts "  Updating topic_users.bookmarked from bookmarks..."
    DB.exec(<<~SQL)
      INSERT INTO topic_users (user_id, topic_id, bookmarked)
      SELECT b.user_id, b.bookmarkable_id, TRUE
        FROM bookmarks b
       WHERE b.bookmarkable_type = 'Topic'
      ON CONFLICT (user_id, topic_id) DO UPDATE SET bookmarked = TRUE
    SQL
  end

  def import_user_stats
    puts "", "Importing user stats..."

    start_time = Time.now

    # TODO Merge with #update_user_stats from import.rake and check if there are privacy concerns
    # E.g. maybe we need to exclude PMs from the calculation?

    DB.exec(<<~SQL)
        WITH
          visible_posts AS (
                             SELECT p.id, p.post_number, p.user_id, p.created_at, p.like_count, p.topic_id
                               FROM posts p
                                    JOIN topics t ON p.topic_id = t.id
                              WHERE t.archetype = 'regular'
                                AND t.deleted_at IS NULL
                                AND t.visible
                                AND p.deleted_at IS NULL
                                AND p.post_type = 1 /* regular_post_type */
                                AND NOT p.hidden
                           ),
          topic_stats AS (
                             SELECT t.user_id, COUNT(t.id) AS topic_count
                               FROM topics t
                              WHERE t.archetype = 'regular'
                                AND t.deleted_at IS NULL
                                AND t.visible
                              GROUP BY t.user_id
                           ),
          post_stats AS (
                             SELECT p.user_id, MIN(p.created_at) AS first_post_created_at, SUM(p.like_count) AS likes_received
                               FROM visible_posts p
                              GROUP BY p.user_id
                           ),
          reply_stats AS (
                             SELECT p.user_id, COUNT(p.id) AS reply_count
                               FROM visible_posts p
                              WHERE p.post_number > 1
                              GROUP BY p.user_id
                           ),
          like_stats AS (
                             SELECT pa.user_id, COUNT(*) AS likes_given
                               FROM post_actions pa
                                    JOIN visible_posts p ON pa.post_id = p.id
                              WHERE pa.post_action_type_id = 2 /* like */
                                AND pa.deleted_at IS NULL
                              GROUP BY pa.user_id
                           ),
          badge_stats AS (
                             SELECT ub.user_id, COUNT(DISTINCT ub.badge_id) AS distinct_badge_count
                               FROM user_badges ub
                                    JOIN badges b ON ub.badge_id = b.id AND b.enabled
                              GROUP BY ub.user_id
                           ),
          post_action_stats AS ( -- posts created by user and likes given by user
                             SELECT p.user_id, p.id AS post_id, p.created_at::DATE, p.topic_id, p.post_number
                               FROM visible_posts p
                              UNION
                             SELECT pa.user_id, pa.post_id, pa.created_at::DATE, p.topic_id, p.post_number
                               FROM post_actions pa
                                    JOIN visible_posts p ON pa.post_id = p.id
                              WHERE pa.post_action_type_id = 2
                           ),
          topic_reading_stats AS (
                             SELECT user_id, COUNT(DISTINCT topic_id) AS topics_entered,
                                    COUNT(DISTINCT created_at) AS days_visited
                               FROM post_action_stats
                              GROUP BY user_id
                           ),
          posts_reading_stats AS (
                             SELECT user_id, SUM(max_post_number) AS posts_read_count
                               FROM (
                                      SELECT user_id, MAX(post_number) AS max_post_number
                                        FROM post_action_stats
                                       GROUP BY user_id, topic_id
                                    ) x
                              GROUP BY user_id
                           )
      INSERT
        INTO user_stats (user_id, new_since, post_count, topic_count, first_post_created_at, likes_received,
                         likes_given, distinct_badge_count, days_visited, topics_entered, posts_read_count, time_read)
      SELECT u.id AS user_id, u.created_at AS new_since, COALESCE(rs.reply_count, 0) AS reply_count,
             COALESCE(ts.topic_count, 0) AS topic_count, ps.first_post_created_at,
             COALESCE(ps.likes_received, 0) AS likes_received, COALESCE(ls.likes_given, 0) AS likes_given,
             COALESCE(bs.distinct_badge_count, 0) AS distinct_badge_count, COALESCE(trs.days_visited, 1) AS days_visited,
             COALESCE(trs.topics_entered, 0) AS topics_entered, COALESCE(prs.posts_read_count, 0) AS posts_read_count,
             COALESCE(prs.posts_read_count, 0) * 30 AS time_read -- assume 30 seconds / post
        FROM users u
             LEFT JOIN topic_stats ts ON u.id = ts.user_id
             LEFT JOIN post_stats ps ON u.id = ps.user_id
             LEFT JOIN reply_stats rs ON u.id = rs.user_id
             LEFT JOIN like_stats ls ON u.id = ls.user_id
             LEFT JOIN badge_stats bs ON u.id = bs.user_id
             LEFT JOIN topic_reading_stats trs ON u.id = trs.user_id
             LEFT JOIN posts_reading_stats prs ON u.id = prs.user_id
          ON CONFLICT DO NOTHING
    SQL

    puts "  Imported user stats in #{(Time.now - start_time).to_i} seconds."
  end

  def import_muted_users
    puts "", "Importing muted users..."

    muted_users = query(<<~SQL)
      SELECT *
        FROM muted_users
    SQL

    existing_user_ids = MutedUser.pluck(:user_id, :muted_user_id).to_set

    create_muted_users(muted_users) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      muted_user_id = user_id_from_imported_id(row["muted_user_id"])
      next unless user_id && muted_user_id
      next if existing_user_ids.include?([user_id, muted_user_id])

      { user_id: user_id, muted_user_id: muted_user_id }
    end

    muted_users.close
  end

  def import_user_histories
    puts "", "Importing user histories..."

    user_histories = query(<<~SQL)
      SELECT id, JSON_EXTRACT(suspension, '$.reason') AS reason
        FROM users
       WHERE suspension IS NOT NULL
    SQL

    action_id = UserHistory.actions[:suspend_user]
    existing_user_ids = UserHistory.where(action: action_id).pluck(:target_user_id).to_set

    create_user_histories(user_histories) do |row|
      user_id = user_id_from_imported_id(row["id"])
      next if user_id && existing_user_ids.include?(user_id)

      {
        action: action_id,
        acting_user_id: Discourse::SYSTEM_USER_ID,
        target_user_id: user_id,
        details: row["reason"],
      }
    end

    user_histories.close
  end

  def import_user_notes
    puts "", "Importing user notes..."

    unless defined?(DiscourseUserNotes)
      puts "  Skipping import of user notes because the plugin is not installed."
      return
    end

    return import_delta_user_notes if delta_import?

    user_notes = query(<<~SQL)
      SELECT un.user_id,
             JSON_GROUP_ARRAY(JSON_OBJECT('raw', un.raw, 'created_by', un.created_by_user_id,
                                          'created_at', un.created_at)) AS note_json_text
        FROM user_notes un
             JOIN users u ON u.id = un.user_id
       WHERE u.anonymized IS NOT TRUE
       GROUP BY un.user_id
       ORDER BY un.user_id, un.id
    SQL

    existing_user_ids =
      PluginStoreRow
        .where(plugin_name: "user_notes")
        .pluck(:key)
        .map { |key| key.delete_prefix("notes:").to_i }
        .to_set

    create_plugin_store_rows(user_notes) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next if !user_id || existing_user_ids.include?(user_id)

      notes = JSON.parse(row["note_json_text"], symbolize_names: true)
      notes.each do |note|
        note[:id] = SecureRandom.hex(16)
        note[:user_id] = user_id
        note[:created_by] = (
          if note[:created_by]
            user_id_from_imported_id(note[:created_by])
          else
            Discourse::SYSTEM_USER_ID
          end
        )
        note[:created_at] = to_datetime(note[:created_at])
      end

      {
        plugin_name: "user_notes",
        key: "notes:#{user_id}",
        type_name: "JSON",
        value: notes.to_json,
      }
    end

    user_notes.close
  end

  def import_delta_user_notes
    notes_by_user = Hash.new { |notes, user_id| notes[user_id] = [] }
    query(<<~SQL) do |rows|
      SELECT un.*
        FROM user_notes un
             JOIN users u ON u.id = un.user_id
       WHERE u.anonymized IS NOT TRUE
       ORDER BY un.user_id, un.id
    SQL
      rows.each do |row|
        source_user_id = row["user_id"].to_i
        next if @delta_unverified_user_source_ids&.include?(source_user_id)

        user_id = user_id_from_imported_id(source_user_id)
        next unless user_id

        author_id = user_id_from_imported_id(row["created_by_user_id"]) if row["created_by_user_id"]
        notes_by_user[user_id] << {
          "user_id" => user_id,
          "raw" => row["raw"],
          "created_by" => author_id || Discourse::SYSTEM_USER_ID,
          "created_at" => row["created_at"],
        }
      end
    end

    notes_by_user.each do |user_id, incoming_notes|
      User.transaction(requires_new: true) do
        next unless User.lock.find_by(id: user_id)

        stored_row =
          PluginStoreRow.find_or_initialize_by(plugin_name: "user_notes", key: "notes:#{user_id}")
        notes = delta_stored_user_notes(stored_row, user_id)
        # Source IDs may be positional; match occurrences of the stored values instead.
        unmatched_counts = notes.map { |note| delta_user_note_key(note, user_id) }.tally
        original_count = notes.size

        incoming_notes.each do |note|
          key = delta_user_note_key(note, user_id)
          if unmatched_counts.fetch(key, 0) > 0
            unmatched_counts[key] -= 1
          else
            notes << note.merge("id" => SecureRandom.hex(16))
          end
        end

        if notes.size != original_count
          stored_row.type_name = "JSON"
          stored_row.value = notes.to_json
          stored_row.save!
        end

        count_field =
          UserCustomField.find_or_initialize_by(user_id: user_id, name: "user_notes_count")
        count_field.value = notes.size.to_s
        count_field.save! if count_field.changed?
      end
    end
  end

  def delta_stored_user_notes(row, user_id)
    return [] if row.new_record?

    notes = JSON.parse(row.value)
    unless row.type_name == "JSON" && notes.is_a?(Array) &&
             notes.all? { |note|
               note.is_a?(Hash) && note["id"].is_a?(String) && note["id"].present? &&
                 note["raw"].is_a?(String)
             }
      raise ArgumentError
    end
    notes
  rescue JSON::ParserError, ArgumentError, TypeError
    raise "Invalid stored user notes for user #{user_id}", cause: nil
  end

  def delta_user_note_key(note, user_id)
    timestamp = to_datetime(note["created_at"])&.new_offset(0)
    author = Integer((note["created_by"] || Discourse::SYSTEM_USER_ID).to_s, 10)
    [note.fetch("raw"), timestamp, author]
  rescue ArgumentError, TypeError, KeyError
    raise "Invalid user-note metadata for user #{user_id}", cause: nil
  end

  def import_user_note_counts
    return if delta_import?

    puts "", "Importing user note counts..."

    unless defined?(DiscourseUserNotes)
      puts "  Skipping import of user notes because the plugin is not installed."
      return
    end

    user_note_counts = query(<<~SQL)
      SELECT un.user_id, COUNT(*) AS count
        FROM user_notes un
             JOIN users u ON u.id = un.user_id
       WHERE u.anonymized IS NOT TRUE
       GROUP BY un.user_id
       ORDER BY un.user_id
    SQL

    existing_user_ids = UserCustomField.where(name: "user_notes_count").pluck(:user_id).to_set

    create_user_custom_fields(user_note_counts) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next if !user_id || existing_user_ids.include?(user_id)

      { user_id: user_id, name: "user_notes_count", value: row["count"].to_s }
    end

    user_note_counts.close
  end

  def import_user_custom_fields
    puts "", "Importing user custom fields..."

    upsert_delta_custom_fields(:user, :users, anonymized_filter: true) if delta_import?

    rows = query(<<~SQL)
      SELECT ucf.user_id, ucf.name, ucf.value, ucf.created_at
        FROM user_custom_fields ucf
             JOIN users u ON u.id = ucf.user_id
       WHERE u.anonymized IS NOT TRUE
    SQL

    existing_names = query(<<~SQL) { |rs| rs.map { |r| r["name"] } }
        SELECT DISTINCT ucf.name
          FROM user_custom_fields ucf
               JOIN users u ON u.id = ucf.user_id
         WHERE u.anonymized IS NOT TRUE
      SQL

    return if existing_names.empty?

    existing = UserCustomField.where(name: existing_names).pluck(:user_id, :name).to_set

    create_user_custom_fields(rows) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next if !user_id
      next if existing.include?([user_id, row["name"]])

      {
        user_id: user_id,
        name: row["name"],
        value: row["value"],
        created_at: to_datetime(row["created_at"]),
      }
    end

    rows.close

    puts "", "Cooking signature_cooked from signature_raw..."

    sig_rows = query(<<~SQL)
      SELECT ucf.user_id, ucf.value
        FROM user_custom_fields ucf
             JOIN users u ON u.id = ucf.user_id
       WHERE ucf.name = 'signature_raw'
         AND u.anonymized IS NOT TRUE
    SQL

    cooked_existing = UserCustomField.where(name: "signature_cooked").pluck(:user_id).to_set

    create_user_custom_fields(sig_rows) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next if !user_id || cooked_existing.include?(user_id)
      raw_sig = row["value"].to_s.strip
      next if raw_sig.empty?
      { user_id: user_id, name: "signature_cooked", value: PrettyText.cook(raw_sig) }
    end
    sig_rows.close
  end

  def import_user_followers
    puts "", "Importing user followers..."

    unless defined?(::Follow)
      puts "  Skipping import of user followers because the plugin is not installed."
      return
    end

    user_followers = query(<<~SQL)
      SELECT *
        FROM user_followers
       ORDER BY user_id, follower_id
    SQL

    existing_followers = UserFollower.pluck(:user_id, :follower_id).to_set
    notification_level = Follow::Notification.levels[:watching]

    create_user_followers(user_followers) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      follower_id = user_id_from_imported_id(row["follower_id"])

      next if !user_id || !follower_id || existing_followers.include?([user_id, follower_id])

      {
        user_id: user_id,
        follower_id: follower_id,
        level: notification_level,
        created_at: to_datetime(row["created_at"]),
      }
    end

    user_followers.close
  end

  def import_uploads
    return if !@uploads_db

    puts "", "Importing uploads..."

    uploads = query(<<~SQL, db: @uploads_db)
      SELECT id, upload
        FROM uploads
       WHERE upload IS NOT NULL
       ORDER BY rowid
    SQL

    create_uploads(uploads) do |row|
      next if upload_id_from_original_id(row["id"]).present?

      upload = JSON.parse(row["upload"], symbolize_names: true)
      upload[:original_id] = row["id"]
      upload
    end

    uploads.close
  end

  def import_optimized_images
    return if !@uploads_db

    puts "", "Importing optimized images..."

    optimized_images = query(<<~SQL, db: @uploads_db)
      SELECT oi.id AS upload_id, x.value AS optimized_image
        FROM optimized_images oi,
             JSON_EACH(oi.optimized_images) x
       WHERE optimized_images IS NOT NULL
       ORDER BY oi.rowid, x.value -> 'id'
    SQL

    DB.exec(<<~SQL) unless delta_import?
        DELETE
          FROM optimized_images oi
         WHERE EXISTS (
                        SELECT 1
                          FROM migration_mappings mm
                         WHERE mm.type = 1
                           AND mm.discourse_id::BIGINT = oi.upload_id
                      )
      SQL

    existing_optimized_images = OptimizedImage.pluck(:upload_id, :height, :width).to_set

    create_optimized_images(optimized_images) do |row|
      upload_id = upload_id_from_original_id(row["upload_id"])
      next unless upload_id

      optimized_image = JSON.parse(row["optimized_image"], symbolize_names: true)

      unless existing_optimized_images.add?(
               [upload_id, optimized_image[:height], optimized_image[:width]],
             )
        next
      end

      optimized_image[:upload_id] = upload_id
      optimized_image
    end

    optimized_images.close
  end

  def import_user_avatars
    return if !@uploads_db

    puts "", "Importing user avatars..."

    update_delta_user_avatars if delta_import?

    avatars = query(<<~SQL)
      SELECT id, avatar_upload_id
        FROM users
       WHERE avatar_upload_id IS NOT NULL
         AND anonymized IS NOT TRUE
       ORDER BY id
    SQL

    existing_user_ids = UserAvatar.pluck(:user_id).to_set

    create_user_avatars(avatars) do |row|
      user_id = user_id_from_imported_id(row["id"])
      upload_id = upload_id_from_original_id(row["avatar_upload_id"])
      next if delta_deduplicated_source_id?(:users, row["id"])
      next if !upload_id || !user_id || existing_user_ids.include?(user_id)

      { user_id: user_id, custom_upload_id: upload_id }
    end

    avatars.close
  end

  def update_delta_user_avatars
    existing_avatars =
      UserAvatar.where(user_id: delta_update_mapping(:users).values).index_by(&:user_id)
    rows = query(<<~SQL)
      SELECT id, avatar_upload_id
        FROM users
       WHERE avatar_upload_id IS NOT NULL
         AND anonymized IS NOT TRUE
       ORDER BY id
    SQL
    updates = []
    rows.each do |row|
      user_id = delta_update_mapping(:users)[row["id"].to_i]
      upload_id = upload_id_from_original_id(row["avatar_upload_id"])
      avatar = existing_avatars[user_id]
      next unless user_id && upload_id && avatar

      updates << { id: avatar.id, user_id: user_id, custom_upload_id: upload_id }
    end
    result = update_records(updates, "user_avatar", [:custom_upload_id])
    users_by_avatar_id = updates.to_h { |update| [update[:id], update[:user_id]] }
    @delta_stats[:users][:updated_ids].merge(
      result[:updated_keys].filter_map { |id| users_by_avatar_id[id.to_i] },
    )

    updated_avatar_ids = result[:updated_keys].map(&:to_i).to_set
    updates.each do |update|
      next if updated_avatar_ids.exclude?(update[:id])

      UploadReference.ensure_exist!(
        upload_ids: [update[:custom_upload_id]],
        target_type: "UserAvatar",
        target_id: update[:id],
      )
    end
  ensure
    rows&.close
  end

  def import_upload_references
    puts "", "Importing upload references for user avatars..."
    start_time = Time.now
    DB.exec(<<~SQL)
      INSERT INTO upload_references (upload_id, target_type, target_id, created_at, updated_at)
      SELECT ua.custom_upload_id, 'UserAvatar', ua.id, ua.created_at, ua.updated_at
        FROM user_avatars ua
       WHERE ua.custom_upload_id IS NOT NULL
         AND NOT EXISTS (
         SELECT 1
           FROM upload_references ur
          WHERE ur.upload_id = ua.custom_upload_id
            AND ur.target_type = 'UserAvatar'
            AND ur.target_id = ua.id
       )
          ON CONFLICT DO NOTHING
    SQL
    puts "  Import took #{(Time.now - start_time).to_i} seconds."

    puts "", "Importing upload references for categories..."
    start_time = Time.now
    DB.exec(<<~SQL)
      INSERT INTO upload_references (upload_id, target_type, target_id, created_at, updated_at)
      SELECT upload_id, 'Category', target_id, created_at, updated_at
        FROM (
               SELECT uploaded_logo_id AS upload_id, id AS target_id, created_at, updated_at
                 FROM categories
                WHERE uploaded_logo_id IS NOT NULL
                UNION
               SELECT uploaded_logo_dark_id AS upload_id, id AS target_id, created_at, updated_at
                 FROM categories
                WHERE uploaded_logo_dark_id IS NOT NULL
                UNION
               SELECT uploaded_background_id AS upload_id, id AS target_id, created_at, updated_at
                 FROM categories
                WHERE uploaded_background_id IS NOT NULL
             ) x
       WHERE NOT EXISTS (
                          SELECT 1
                            FROM upload_references ur
                           WHERE ur.upload_id = x.upload_id
                             AND ur.target_type = 'Category'
                             AND ur.target_id = x.target_id
                        )
          ON CONFLICT DO NOTHING
    SQL
    puts "  Import took #{(Time.now - start_time).to_i} seconds."

    puts "", "Importing upload references for badges..."
    start_time = Time.now
    DB.exec(<<~SQL)
      INSERT INTO upload_references (upload_id, target_type, target_id, created_at, updated_at)
      SELECT image_upload_id, 'Badge', id, created_at, updated_at
        FROM badges b
       WHERE image_upload_id IS NOT NULL
         AND NOT EXISTS (
                          SELECT 1
                            FROM upload_references ur
                           WHERE ur.upload_id = b.image_upload_id
                             AND ur.target_type = 'Badge'
                             AND ur.target_id = b.id
                        )
          ON CONFLICT DO NOTHING
    SQL
    puts "  Import took #{(Time.now - start_time).to_i} seconds."

    import_event_upload_references

    import_content_upload_references("posts")
    import_content_upload_references("chat_messages")
  end

  def import_event_upload_references
    return unless defined?(DiscourseEvents::Events)

    puts "", "Importing upload references for events..."
    DB.exec(<<~SQL, target_type: DiscourseEvents::Events::Event.name)
      INSERT INTO upload_references (upload_id, target_type, target_id, created_at, updated_at)
      SELECT image_upload_id, :target_type, id, NOW(), NOW()
        FROM discourse_post_event_events
       WHERE image_upload_id IS NOT NULL
          ON CONFLICT DO NOTHING
    SQL
  end

  def import_content_upload_references(type)
    if CONTENT_UPLOAD_REFERENCE_TYPES.exclude?(type)
      puts "  Skipping upload references import for #{type} because it's unsupported"

      return
    end

    puts "", "Importing upload references for #{type}..."

    reconcile_delta_post_upload_references if delta_import? && type == "posts"

    content_uploads = query(<<~SQL)
      SELECT t.id AS target_id, u.value AS upload_id
        FROM #{type} t,
             JSON_EACH(t.upload_ids) u
       WHERE upload_ids IS NOT NULL
    SQL

    target_type = type.classify
    existing_upload_references =
      UploadReference.where(target_type: target_type).pluck(:upload_id, :target_id).to_set

    create_upload_references(content_uploads) do |row|
      upload_id = upload_id_from_original_id(row["upload_id"])
      target_id = content_id_from_original_id(type, row["target_id"])

      next unless upload_id && target_id
      next unless existing_upload_references.add?([upload_id, target_id])

      { upload_id: upload_id, target_type: target_type, target_id: target_id }
    end

    content_uploads.close
  end

  def reconcile_delta_post_upload_references
    rows = query("SELECT id, upload_ids FROM posts WHERE upload_ids IS NOT NULL ORDER BY id")
    rows.each do |row|
      post_id = post_id_from_imported_id(row["id"])
      next unless post_id

      original_upload_ids = JSON.parse(row["upload_ids"])
      upload_ids =
        original_upload_ids.filter_map { |original_id| upload_id_from_original_id(original_id) }
      if upload_ids.size != original_upload_ids.size
        log_import_issue(
          "post upload reconciliation skipped (unmapped uploads)",
          "post #{row["id"]}",
        )
        next
      end
      existing_ids =
        UploadReference.where(target_type: "Post", target_id: post_id).pluck(:upload_id).sort
      next if existing_ids == upload_ids.uniq.sort

      UploadReference.ensure_exist!(upload_ids: upload_ids, target_type: "Post", target_id: post_id)
      if delta_update_mapping(:posts).key?(row["id"].to_i)
        @delta_stats[:posts][:updated_ids] << post_id
      end
    end
  ensure
    rows&.close
  end

  def content_id_from_original_id(type, original_id)
    case type
    when "posts"
      post_id_from_imported_id(original_id)
    when "chat_messages"
      chat_message_id_from_original_id(original_id)
    end
  end

  def update_uploaded_avatar_id
    puts "", "Updating user's uploaded_avatar_id column..."

    start_time = Time.now

    if delta_import?
      avatar_predicate = "u.uploaded_avatar_id IS DISTINCT FROM ua.custom_upload_id"
      mapping_predicate = <<~SQL.squish
        AND EXISTS (
          SELECT 1
            FROM user_custom_fields mapping
           WHERE mapping.user_id = u.id
             AND mapping.name = 'import_id'
             AND mapping.value ~ '^[0-9]+$'
        )
      SQL
    else
      avatar_predicate = "u.uploaded_avatar_id IS NULL"
      mapping_predicate = ""
    end
    DB.exec(<<~SQL)
      UPDATE users u
         SET uploaded_avatar_id = ua.custom_upload_id
        FROM user_avatars ua
       WHERE #{avatar_predicate}
         AND u.id = ua.user_id
         AND ua.custom_upload_id IS NOT NULL
         #{mapping_predicate}
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def import_tag_groups
    puts "", "Importing tag groups..."

    SiteSetting.tags_listed_by_group = true

    @tag_group_mapping = {}

    tag_groups = query(<<~SQL)
      SELECT *
        FROM tag_groups
       ORDER BY id
    SQL

    tag_groups.each do |row|
      tag_group = TagGroup.find_or_create_by!(name: row["name"])
      @tag_group_mapping[row["id"]] = tag_group.id

      if (permissions = row["permissions"])
        tag_group.permissions =
          JSON
            .parse(permissions)
            .map do |p|
              group_id = p["existing_group_id"] || group_id_from_imported_id(p["group_id"])
              group_id ? [group_id, p["permission_type"]] : nil
            end
            .compact
        tag_group.save!
      end
    end

    tag_groups.close
  end

  def import_tags
    puts "", "Importing tags..."

    SiteSetting.max_tag_length = 100 if SiteSetting.max_tag_length < 100

    @tag_mapping = {}

    tags = query(<<~SQL)
      SELECT *
        FROM tags
       ORDER BY id
    SQL

    tags.each do |row|
      cleaned_tag_name = DiscourseTagging.clean_tag(row["name"])
      description = row["description"].presence&.truncate(1000)
      tag =
        Tag
          .where("LOWER(name) = ?", cleaned_tag_name.downcase)
          .first_or_create!(name: cleaned_tag_name) { |t| t.description = description }
      if description && tag.description != description
        tag.update_columns(description: sanitize_field(description))
      end
      @tag_mapping[row["id"]] = tag.id

      intermediate_group_ids = []
      if row["tag_group_ids"] && !row["tag_group_ids"].empty?
        intermediate_group_ids = JSON.parse(row["tag_group_ids"])
      elsif row["tag_group_id"].present?
        # Support old single tag_group_id
        intermediate_group_ids = [row["tag_group_id"]]
      end

      if intermediate_group_ids.any?
        intermediate_group_ids.each do |intermediate_group_id|
          intermediate_group_id = intermediate_group_id.to_i
          discourse_tag_group_id = @tag_group_mapping[intermediate_group_id]

          if discourse_tag_group_id
            TagGroupMembership.find_or_create_by!(
              tag_id: tag.id,
              tag_group_id: discourse_tag_group_id,
            )
          else
            log_import_issue(
              "tag group mapping missing",
              "intermediate tag group #{intermediate_group_id} for tag '#{tag.name}'",
            )
          end
        end
      end
    end

    tags.close
  end

  def import_topic_tags
    puts "", "Importing topic tags..."

    if !@tag_mapping
      puts "  Skipping import of topic tags because tags have not been imported."
      return
    end

    topic_tags = query(<<~SQL)
      SELECT *
        FROM topic_tags
       ORDER BY topic_id, tag_id
    SQL

    existing_topic_tags = TopicTag.pluck(:topic_id, :tag_id).to_set

    create_topic_tags(topic_tags) do |row|
      topic_id = topic_id_from_imported_id(row["topic_id"])
      tag_id = @tag_mapping[row["tag_id"]]

      next unless topic_id && tag_id
      next unless existing_topic_tags.add?([topic_id, tag_id])

      { topic_id: topic_id, tag_id: tag_id }
    end

    topic_tags.close
  end

  def import_post_voting_votes
    puts "", "Importing votes for posts..."

    unless defined?(PostVoting)
      puts "  Skipping import of votes for posts because the plugin is not installed."
      return
    end

    votes = query(<<~SQL)
      SELECT *
        FROM post_voting_votes
       WHERE votable_type = 'Post'
    SQL

    votable_type = "Post"
    existing_votes =
      PostVotingVote.where(votable_type: votable_type).pluck(:user_id, :votable_id).to_set

    create_post_voting_votes(votes) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      post_id = post_id_from_imported_id(row["votable_id"])

      next unless user_id && post_id
      next unless existing_votes.add?([user_id, post_id])

      {
        user_id: user_id,
        direction: row["direction"],
        votable_type: votable_type,
        votable_id: post_id,
        created_at: to_datetime(row["created_at"]),
      }
    end

    votes.close

    puts "", "Updating vote counts of posts..."

    start_time = Time.now

    DB.exec(<<~SQL)
        WITH
          votes AS (
                     SELECT votable_id AS post_id, SUM(CASE direction WHEN 'up' THEN 1 ELSE -1 END) AS vote_count
                       FROM post_voting_votes
                      GROUP BY votable_id
                   )
      UPDATE posts
         SET qa_vote_count = votes.vote_count
        FROM votes
       WHERE votes.post_id = posts.id
         AND votes.vote_count <> posts.qa_vote_count
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def import_topic_voting_votes
    unless defined?(DiscourseTopicVoting)
      puts "", "Skipping topic voting votes, because the topic voting plugin is not installed."
      return
    end

    puts "", "Importing votes for topics..."

    topic_votes = query(<<~SQL)
      SELECT *
      FROM topic_voting_votes
    SQL

    existing_topic_votes = DiscourseTopicVoting::Vote.pluck(:topic_id, :user_id).to_set

    create_topic_voting_votes(topic_votes) do |row|
      topic_id = topic_id_from_imported_id(row["topic_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next unless topic_id && user_id
      next unless existing_topic_votes.add?([topic_id, user_id])

      {
        topic_id: topic_id,
        user_id: user_id,
        archive: to_boolean(row["archive"]),
        created_at: to_datetime(row["created_at"]),
        updated_at: to_datetime(row["updated_at"]),
      }
    end

    topic_votes.close

    puts "", "Updating vote counts of topics..."

    start_time = Time.now

    DB.exec(<<~SQL)
        WITH
          votes AS (
                     SELECT topic_id, COUNT(*) AS votes_count
                     FROM topic_voting_votes
                     GROUP BY topic_id
                   )
      UPDATE topic_voting_topic_vote_count
         SET votes_count = votes.votes_count
        FROM votes
       WHERE votes.topic_id = topic_voting_topic_vote_count.topic_id
         AND votes.votes_count <> topic_voting_topic_vote_count.votes_count
    SQL

    DB.exec(<<~SQL)
        WITH
          missing_votes AS (
                             SELECT v.topic_id, COUNT(*) AS votes_count
                               FROM topic_voting_votes v
                              WHERE NOT EXISTS (
                                                 SELECT 1
                                                 FROM topic_voting_topic_vote_count c
                                                 WHERE v.topic_id = c.topic_id
                                               )
                              GROUP BY topic_id
                           )
      INSERT
        INTO topic_voting_topic_vote_count (votes_count, topic_id, created_at, updated_at)
      SELECT votes_count, topic_id, NOW(), NOW()
        FROM missing_votes
          ON CONFLICT DO NOTHING
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  # Runs after the votes are imported because creating a CategorySetting unarchives
  # votes of the category's existing open topics.
  def import_category_topic_voting
    unless defined?(DiscourseTopicVoting)
      puts "", "Skipping topic voting categories, because the topic voting plugin is not installed."
      return
    end

    return if table_column_names("categories").exclude?("topic_voting")

    puts "", "Enabling topic voting on categories..."

    rows = query("SELECT id FROM categories WHERE topic_voting = 1 ORDER BY id")

    rows.each do |row|
      category_id = category_id_from_imported_id(row["id"])
      next unless category_id

      DiscourseTopicVoting::CategorySetting.find_or_create_by!(category_id:)
    end

    rows.close
  end

  def import_answers
    unless defined?(DiscourseSolved)
      puts "  Skipping import of solved topics"
      return
    end

    puts "", "Importing solutions into discourse_solved_solved_topics..."

    solutions = query(<<~SQL)
      SELECT *
        FROM solutions
       ORDER BY topic_id
    SQL

    existing_solved_topics = DiscourseSolved::SolvedTopic.pluck(:topic_id).to_set
    inserted_topic_ids = []

    create_solved_topic(solutions) do |row|
      topic_id = topic_id_from_imported_id(row["topic_id"])
      next unless topic_id && existing_solved_topics.add?(topic_id)

      inserted_topic_ids << topic_id
      { topic_id:, created_at: to_datetime(row["created_at"]) }
    end

    puts "", "Importing solutions into discourse_solved_topic_answers..."

    solved_topic_id_by_topic_id =
      DiscourseSolved::SolvedTopic.where(topic_id: inserted_topic_ids).pluck(:topic_id, :id).to_h

    solutions.reset

    create_topic_answers(solutions) do |row|
      topic_id = topic_id_from_imported_id(row["topic_id"])
      solved_topic_id = solved_topic_id_by_topic_id[topic_id]
      next unless solved_topic_id

      post_id = post_id_from_imported_id(row["post_id"])
      next unless post_id

      {
        solved_topic_id:,
        answer_post_id: post_id,
        accepter_user_id: user_id_from_imported_id(row["acting_user_id"]),
        created_at: to_datetime(row["created_at"]),
      }
    end

    puts "", "Importing solutions into user actions..."

    solutions.reset

    action_type = UserAction::SOLVED
    existing_actions = UserAction.where(action_type: action_type).pluck(:target_post_id).to_set

    create_user_actions(solutions) do |row|
      post_id = post_id_from_imported_id(row["post_id"])
      next unless post_id && existing_actions.add?(post_id)

      topic_id = topic_id_from_imported_id(row["topic_id"])
      user_id = user_id_from_imported_id(row["user_id"])
      next unless topic_id && user_id

      acting_user_id =
        (
          if row["acting_user_id"]
            user_id_from_imported_id(row["acting_user_id"])
          else
            nil
          end
        )

      {
        action_type: action_type,
        user_id: user_id,
        target_topic_id: topic_id,
        target_post_id: post_id,
        acting_user_id: acting_user_id || Discourse::SYSTEM_USER_ID,
      }
    end

    solutions.close
  end

  def import_gamification_scores
    puts "", "Importing gamification scores..."

    unless defined?(DiscourseGamification)
      puts "  Skipping import of gamification scores because the plugin is not installed."
      return
    end

    # TODO Make this configurable
    from_date = Date.today
    DiscourseGamification::GamificationLeaderboard.all.each do |leaderboard|
      leaderboard.update!(from_date: from_date)
    end

    scores = query(<<~SQL)
      SELECT *
        FROM gamification_score_events
       ORDER BY id
    SQL

    # TODO Better way of detecting existing scores?
    existing_scores = DiscourseGamification::GamificationScoreEvent.pluck(:user_id, :date).to_set

    create_gamification_score_events(scores) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      next unless user_id

      date = to_date(row["date"]) || from_date
      next if existing_scores.include?([user_id, date])

      {
        user_id: user_id,
        date: date,
        points: row["points"],
        description: row["description"],
        created_at: to_datetime(row["created_at"]),
      }
    end

    scores.close
  end

  def import_post_events
    puts "", "Importing events..."

    unless defined?(DiscourseEvents::Events)
      puts "  Skipping import of events because the plugin is not installed."
      return
    end

    post_events = query(<<~SQL)
      SELECT *
        FROM events
       ORDER BY id
    SQL

    default_custom_fields = "{}"
    statuses = DiscourseEvents::Events::Event.statuses
    array_encoder = PG::TextEncoder::Array.new

    existing_events = DiscourseEvents::Events::Event.pluck(:id).to_set
    load_event_lookups

    create_post_events(post_events) do |row|
      post_id = post_id_from_imported_id(row["post_id"])
      next if !post_id || existing_events.include?(post_id)

      timezone = event_timezone(row)
      if row["timezone"].present? && timezone != row["timezone"]
        log_import_issue(
          "invalid event timezone, using UTC",
          "#{row["timezone"]} (event #{row["id"]})",
        )
      end

      raw_invitees =
        case row["status"]
        when statuses[:public]
          [DiscourseEvents::Events::Event::PUBLIC_GROUP]
        when statuses[:private]
          event_allowed_group_names(row)
        else
          []
        end

      log_event_allowed_group_issues(row)

      {
        id: post_id,
        status: row["status"],
        original_starts_at: to_datetime(row["starts_at"]),
        original_ends_at: to_datetime(row["ends_at"]),
        name: row["name"],
        url: row["url"] ? row["url"][0..999] : nil,
        location: event_location(row),
        custom_fields: row["custom_fields"] || default_custom_fields,
        timezone:,
        raw_invitees: array_encoder.encode(raw_invitees),
        closed: to_boolean(row["closed"]),
        image_upload_id: event_image_upload_id(row),
      }
    end

    # Like Event#sync_image_to_post_and_topic, without waiting for a rebake.
    DB.exec(<<~SQL)
      UPDATE posts p
         SET image_upload_id = e.image_upload_id
        FROM discourse_post_event_events e
       WHERE e.id = p.id
         AND e.image_upload_id IS NOT NULL
         AND p.image_upload_id IS DISTINCT FROM e.image_upload_id
    SQL
    DB.exec(<<~SQL)
      UPDATE topics t
         SET image_upload_id = p.image_upload_id
        FROM posts p
             JOIN discourse_post_event_events e ON e.id = p.id
       WHERE p.topic_id = t.id
         AND p.post_number = 1
         AND e.image_upload_id IS NOT NULL
         AND t.image_upload_id IS DISTINCT FROM e.image_upload_id
    SQL

    puts "", "Importing event dates..."

    post_events.reset
    existing_events = DiscourseEvents::Events::EventDate.pluck(:event_id).to_set

    create_post_event_dates(post_events) do |row|
      post_id = post_id_from_imported_id(row["post_id"])
      next if !post_id || existing_events.include?(post_id)

      {
        event_id: post_id,
        starts_at: to_datetime(row["starts_at"]),
        ends_at: to_datetime(row["ends_at"]),
      }
    end

    puts "", "Importing topic event custom fields..."

    post_events.reset
    field_name = DiscourseEvents::Events::TOPIC_POST_EVENT_STARTS_AT
    existing_fields = TopicCustomField.where(name: field_name).pluck(:topic_id).to_set

    create_topic_custom_fields(post_events) do |row|
      date = to_datetime(row["starts_at"])
      next unless date

      topic_id = topic_id_from_imported_post_id(row["post_id"])
      next if !topic_id || existing_fields.include?(topic_id)

      { topic_id: topic_id, name: field_name, value: date.utc.strftime("%Y-%m-%d %H:%M:%S") }
    end

    post_events.reset
    field_name = DiscourseEvents::Events::TOPIC_POST_EVENT_ENDS_AT
    existing_fields = TopicCustomField.where(name: field_name).pluck(:topic_id).to_set

    create_topic_custom_fields(post_events) do |row|
      date = to_datetime(row["ends_at"])
      next unless date

      topic_id = topic_id_from_imported_post_id(row["post_id"])
      next if !topic_id || existing_fields.include?(topic_id)

      { topic_id: topic_id, name: field_name, value: date.utc.strftime("%Y-%m-%d %H:%M:%S") }
    end

    post_events.close
  end

  def import_event_invitees
    unless defined?(DiscourseEvents::Events)
      puts "", "Skipping event invitees, because the events plugin is not installed."
      return
    end

    unless @source_db.get_first_value(
             "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'event_invitees'",
           )
      return
    end

    puts "", "Importing event invitees..."

    invitees = query(<<~SQL)
      SELECT event_invitees.*, events.post_id AS event_post_id
        FROM event_invitees
             JOIN events ON events.id = event_invitees.event_id
       ORDER BY event_invitees.event_id, event_invitees.user_id
    SQL

    statuses = DiscourseEvents::Events::Invitee.statuses
    event_statuses = DiscourseEvents::Events::Event.statuses
    events =
      DiscourseEvents::Events::Event
        .pluck(:id, :status, :raw_invitees)
        .to_h { |id, status, groups| [id, { status:, groups: groups || [] }] }
    private_event_groups =
      events.values.filter_map do |event|
        event[:groups] if event[:status] == event_statuses[:private]
      end
    group_members =
      GroupUser
        .joins(:group)
        .where(groups: { name: private_event_groups.flatten.uniq })
        .pluck(:user_id, "groups.name")
        .to_set
    # The default scope hides suspended, silenced and staged users.
    existing_invitees = DiscourseEvents::Events::Invitee.unscoped.pluck(:post_id, :user_id).to_set

    create_event_invitees(invitees) do |row|
      post_id = post_id_from_imported_id(row["event_post_id"])
      user_id = user_id_from_imported_id(row["user_id"])
      event = events[post_id]
      next unless event && user_id

      # The plugin removes invitees from standalone events, and from private events
      # when they aren't in one of the allowed groups.
      case event[:status]
      when event_statuses[:standalone]
        log_import_issue(
          "event invitee skipped (standalone event)",
          "user #{row["user_id"]} of event #{row["event_id"]}",
        )
        next
      when event_statuses[:private]
        if event[:groups].none? { |group| group_members.include?([user_id, group]) }
          log_import_issue(
            "event invitee skipped (not in the event's allowed groups)",
            "user #{row["user_id"]} of event #{row["event_id"]}",
          )
          next
        end
      end

      status = row["status"] || statuses[:going]
      if statuses.values.exclude?(status)
        log_import_issue(
          "event invitee skipped (unknown status)",
          "status #{status} of user #{row["user_id"]} in event #{row["event_id"]}",
        )
        next
      end

      next unless existing_invitees.add?([post_id, user_id])

      created_at = to_datetime(row["created_at"])

      { post_id:, user_id:, status:, created_at:, updated_at: created_at }
    end

    invitees.close
  end

  def import_tag_users
    puts "", "Importing tag users..."

    tag_users = query(<<~SQL)
      SELECT *
        FROM tag_users
       ORDER BY tag_id, user_id
    SQL

    existing_tag_users = TagUser.pluck(:tag_id, :user_id).to_set

    create_tag_users(tag_users) do |row|
      tag_id = @tag_mapping[row["tag_id"]]
      user_id = user_id_from_imported_id(row["user_id"])

      next unless tag_id && user_id
      next if existing_tag_users.include?([tag_id, user_id])

      { tag_id: tag_id, user_id: user_id, notification_level: row["notification_level"] }
    end

    tag_users.close
  end

  def import_badge_groupings
    puts "", "Importing badge groupings..."

    rows = query(<<~SQL)
      SELECT DISTINCT badge_group
        FROM badges
       WHERE badge_group IS NOT NULL
       ORDER BY badge_group
    SQL

    @badge_group_mapping = {}
    max_position = BadgeGrouping.maximum(:position) || 0

    rows.each do |row|
      grouping =
        BadgeGrouping.find_or_create_by!(name: row["badge_group"]) do |bg|
          bg.position = max_position += 1
        end
      @badge_group_mapping[row["badge_group"]] = grouping.id
    end

    rows.close
  end

  def import_badges
    puts "", "Importing badges..."

    badges = query(<<~SQL)
      SELECT *
        FROM badges
       ORDER BY id
    SQL

    existing_badge_names = Badge.pluck(:name).to_set

    create_badges(badges) do |row|
      next if badge_id_from_original_id(row["id"]).present?

      badge_name = row["name"]
      unless existing_badge_names.add?(badge_name)
        badge_name = badge_name + "_1"
        badge_name.next! until existing_badge_names.add?(badge_name)
      end

      {
        original_id: row["id"],
        name: badge_name,
        description: row["description"],
        badge_type_id: row["badge_type_id"],
        badge_grouping_id: @badge_group_mapping[row["badge_group"]] || BadgeGrouping::Other,
        long_description: row["long_description"],
        image_upload_id:
          (
            if row["image_upload_id"]
              upload_id_from_original_id(row["image_upload_id"])
            else
              nil
            end
          ),
        query: row["query"],
        multiple_grant: to_boolean(row["multiple_grant"]),
        allow_title: to_boolean(row["allow_title"]),
        icon: row["icon"],
        listable: to_boolean(row["listable"]),
        target_posts: to_boolean(row["target_posts"]),
        enabled: to_boolean(row["enabled"]),
        auto_revoke: to_boolean(row["auto_revoke"]),
        trigger: row["trigger"],
        show_posts: to_boolean(row["show_posts"]),
      }
    end

    badges.close
  end

  def import_user_badges
    puts "", "Importing user badges..."

    user_badges = query(<<~SQL)
      SELECT user_id, badge_id, granted_at,
             ROW_NUMBER() OVER (PARTITION BY user_id, badge_id ORDER BY granted_at) - 1 AS seq
        FROM user_badges
       ORDER BY user_id, badge_id, granted_at
    SQL

    existing_user_badges = UserBadge.distinct.pluck(:user_id, :badge_id, :seq).to_set

    create_user_badges(user_badges) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      badge_id = badge_id_from_original_id(row["badge_id"])
      seq = row["seq"]

      next unless user_id && badge_id
      next if existing_user_badges.include?([user_id, badge_id, seq])

      { user_id: user_id, badge_id: badge_id, granted_at: to_datetime(row["granted_at"]), seq: seq }
    end

    user_badges.close

    puts "", "Updating badge grant counts..."
    start_time = Time.now

    DB.exec(<<~SQL)
        WITH
          grants AS (
                      SELECT badge_id, COUNT(*) AS grant_count FROM user_badges GROUP BY badge_id
                    )

      UPDATE badges
         SET grant_count = grants.grant_count
        FROM grants
       WHERE badges.id = grants.badge_id
         AND badges.grant_count <> grants.grant_count
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def import_anniversary_user_badges
    unless SiteSetting.enable_badges?
      puts "", "Skipping anniversary user badges because badges are not enabled."
      return
    end

    puts "", "Importing anniversary user badges..."

    start_time = Time.now

    DB.exec(<<~SQL)
      WITH
        eligible_users AS (
                            SELECT u.id, u.created_at
                            FROM users u
                            WHERE u.active
                              AND NOT u.staged
                              AND u.id > 0
                              AND (u.silenced_till IS NULL OR u.silenced_till < CURRENT_TIMESTAMP)
                              AND (u.suspended_till IS NULL OR u.suspended_till < CURRENT_TIMESTAMP)
                              AND NOT EXISTS (SELECT 1 FROM anonymous_users AS au WHERE au.user_id = u.id)
                          ),
        anniversary_dates AS ( -- Series of anniversary dates starting from the user's created_at + 1 year up to the current year
                               SELECT
                                 eu.id AS user_id,
                                 (
                                   eu.created_at +
                                   ((year_num - EXTRACT(YEAR FROM eu.created_at)) || ' years')::interval
                                 )::timestamp AS anniversary_date
                               FROM eligible_users eu,
                                    generate_series(
                                      EXTRACT(YEAR FROM eu.created_at)::int + 1,
                                      EXTRACT(YEAR FROM CURRENT_TIMESTAMP)::int
                                    ) AS year_num
                                WHERE
                                  (
                                    eu.created_at +
                                    ((year_num - EXTRACT(YEAR FROM eu.created_at)) || ' years')::interval
                                  ) < CURRENT_TIMESTAMP
                             ),
        existing_max_seq AS (
                              SELECT user_id, COALESCE(MAX(seq), -1) AS max_seq
                              FROM user_badges
                              WHERE badge_id = #{Badge::Anniversary}
                              GROUP BY user_id
                            )
      INSERT INTO user_badges (granted_at, created_at, granted_by_id, user_id, badge_id, seq)
      SELECT a.anniversary_date,
             CURRENT_TIMESTAMP,
             #{Discourse.system_user.id},
             a.user_id,
             #{Badge::Anniversary},
             COALESCE(ems.max_seq, -1) + ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.anniversary_date) AS seq
      FROM anniversary_dates a
           JOIN eligible_users u ON a.user_id = u.id
           JOIN posts AS p ON p.user_id = u.id
           JOIN topics AS t ON p.topic_id = t.id
           LEFT JOIN existing_max_seq ems ON ems.user_id = a.user_id
      WHERE p.deleted_at IS NULL
        AND NOT p.hidden
        AND p.created_at BETWEEN a.anniversary_date - '1 year'::interval AND a.anniversary_date
        AND t.visible
        AND t.archetype <> 'private_message'
        AND t.deleted_at IS NULL
        AND NOT EXISTS (
            SELECT 1
            FROM user_badges AS ub
            WHERE ub.user_id = u.id
            AND ub.badge_id = #{Badge::Anniversary}
            AND ub.granted_at BETWEEN a.anniversary_date - '1 year'::interval AND a.anniversary_date
        )
      GROUP BY a.user_id, a.anniversary_date, ems.max_seq
      ON CONFLICT DO NOTHING
    SQL

    UserBadge.update_featured_ranks!

    puts "  Anniversary user badges imported in #{(Time.now - start_time).to_i} seconds."
  end

  def update_badge_grant_counts
    puts "", "Updating badge grant counts..."
    start_time = Time.now

    DB.exec(<<~SQL)
        WITH
          grants AS (
                      SELECT badge_id, COUNT(*) AS grant_count FROM user_badges GROUP BY badge_id
                    )

      UPDATE badges
         SET grant_count = grants.grant_count
        FROM grants
       WHERE badges.id = grants.badge_id
         AND badges.grant_count <> grants.grant_count
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def import_permalink_normalizations
    puts "", "Importing permalink normalizations..."

    start_time = Time.now

    rows = query(<<~SQL)
      SELECT normalization
        FROM permalink_normalizations
       ORDER BY normalization
    SQL

    normalizations = SiteSetting.permalink_normalizations
    normalizations = normalizations.blank? ? [] : normalizations.split("|")

    rows.each do |row|
      normalization = row["normalization"]
      normalizations << normalization if normalizations.exclude?(normalization)
    end

    SiteSetting.permalink_normalizations = normalizations.join("|")
    rows.close

    puts "  Import took #{(Time.now - start_time).to_i} seconds."
  end

  def import_permalinks
    puts "", "Importing permalinks..."

    rows = query(<<~SQL)
      SELECT *
        FROM permalinks
       ORDER BY url
    SQL

    existing_permalinks = Permalink.pluck(:url).to_set

    if !@tag_mapping
      puts "Skipping import of permalinks for tags because tags have not been imported."
    end

    create_permalinks(rows) do |row|
      next if existing_permalinks.include?(row["url"])

      if row["topic_id"]
        topic_id = topic_id_from_imported_id(row["topic_id"])
        next unless topic_id
        { url: row["url"], topic_id: topic_id }
      elsif row["post_id"]
        post_id = post_id_from_imported_id(row["post_id"])
        next unless post_id
        { url: row["url"], post_id: post_id }
      elsif row["category_id"]
        category_id = category_id_from_imported_id(row["category_id"])
        next unless category_id
        { url: row["url"], category_id: category_id }
      elsif row["tag_id"]
        next unless @tag_mapping
        tag_id = @tag_mapping[row["tag_id"]]
        next unless tag_id
        { url: row["url"], tag_id: tag_id }
      elsif row["user_id"]
        user_id = user_id_from_imported_id(row["user_id"])
        next unless user_id
        { url: row["url"], user_id: user_id }
      elsif row["external_url"]
        external_url = calculate_external_url(row)
        next unless external_url
        { url: row["url"], external_url: external_url }
      end
    end

    rows.close
  end

  def import_chat_direct_messages
    unless defined?(::Chat)
      puts "", "Skipping chat direct messages, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat direct messages..."

    direct_messages = query(<<~SQL)
      SELECT *
        FROM chat_channels
      WHERE chatable_type = 'DirectMessage'
        ORDER BY id
    SQL

    create_chat_direct_message(direct_messages) do |row|
      next if chat_direct_message_channel_id_from_original_id(row["chatable_id"]).present?

      {
        original_id: row["chatable_id"],
        created_at: to_datetime(row["created_at"]),
        group: to_boolean(row["is_group"]),
      }
    end

    direct_messages.close
  end

  def import_chat_channels
    unless defined?(::Chat)
      puts "", "Skipping chat channels, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat channels..."

    # Ideally, we’d like these to be set in `import_site_settings`,
    # but since there’s no way to enforce that, we're defaulting to keeping all chats
    # indefinitely for now
    SiteSetting.chat_channel_retention_days = 0
    SiteSetting.chat_dm_retention_days = 0

    channels = query(<<~SQL)
      SELECT *
        FROM chat_channels
       ORDER BY id
    SQL

    create_chat_channels(channels) do |row|
      next if chat_channel_id_from_original_id(row["id"]).present?

      case row["chatable_type"]
      when "Category"
        type = "CategoryChannel"
        chatable_id = category_id_from_imported_id(row["chatable_id"])
      when "DirectMessage"
        chatable_id = chat_direct_message_channel_id_from_original_id(row["chatable_id"])
        type = "DirectMessageChannel"
      end

      next if !chatable_id
      # TODO: Add more uniqueness checks
      #       Ensure no channel with same name and category exists?

      {
        original_id: row["id"],
        name: row["name"],
        description: row["description"],
        slug: row["slug"],
        status: row["status"],
        chatable_id: chatable_id,
        chatable_type: row["chatable_type"],
        user_count: row["user_count"],
        messages_count: row["messages_count"],
        type: type,
        created_at: to_datetime(row["created_at"]),
        allow_channel_wide_mentions: to_boolean(row["allow_channel_wide_mentions"]),
        auto_join_users: to_boolean(row["auto_join_users"]),
        threading_enabled: to_boolean(row["threading_enabled"]),
      }
    end

    channels.close
  end

  def import_user_chat_channel_memberships
    unless defined?(::Chat)
      puts "", "Skipping user chat channel memberships, because the chat plugin is not installed."
      return
    end

    puts "", "Importing user chat channel memberships..."

    channel_users = query(<<~SQL)
      SELECT chat_channels.chatable_type, chat_channels.chatable_id, chat_channel_users.*
        FROM chat_channel_users
             JOIN chat_channels ON chat_channels.id = chat_channel_users.chat_channel_id
       ORDER BY chat_channel_users.chat_channel_id
    SQL

    existing_members =
      Chat::UserChatChannelMembership.distinct.pluck(:user_id, :chat_channel_id).to_set

    create_user_chat_channel_memberships(channel_users) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      channel_id = chat_channel_id_from_original_id(row["chat_channel_id"])
      last_read_message_id = chat_message_id_from_original_id(row["last_read_message_id"])

      next if user_id.blank? || channel_id.blank?
      next unless existing_members.add?([user_id, channel_id])

      # `last_viewed_at` is required, if not provided, set a placeholder,
      # it'll be updated in the `update_chat_membership_metadata` step
      last_viewed_at = to_datetime(row["last_viewed_at"].presence || LAST_VIEWED_AT_PLACEHOLDER)

      {
        user_id: user_id,
        chat_channel_id: channel_id,
        created_at: to_datetime(row["created_at"]),
        following: to_boolean(row["following"]),
        muted: to_boolean(row["muted"]),
        desktop_notification_level: row["desktop_notification_level"],
        mobile_notification_level: row["mobile_notification_level"],
        last_read_message_id: last_read_message_id,
        join_mode: row["join_mode"],
        last_viewed_at: last_viewed_at,
      }
    end

    puts "", "Importing chat direct message users..."

    channel_users.reset
    existing_direct_message_users =
      Chat::DirectMessageUser.distinct.pluck(:direct_message_channel_id, :user_id).to_set

    create_direct_message_users(channel_users) do |row|
      next if row["chatable_type"] != "DirectMessage"

      user_id = user_id_from_imported_id(row["user_id"])
      direct_message_channel_id =
        chat_direct_message_channel_id_from_original_id(row["chatable_id"])

      next if user_id.blank? || direct_message_channel_id.blank?
      next unless existing_direct_message_users.add?([direct_message_channel_id, user_id])

      {
        direct_message_channel_id: direct_message_channel_id,
        user_id: user_id,
        created_at: to_datetime(row["created_at"]),
      }
    end

    channel_users.close
  end

  def import_chat_threads
    unless defined?(::Chat)
      puts "", "Skipping chat threads, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat threads..."

    threads = query(<<~SQL)
      SELECT *
      FROM chat_threads
      ORDER BY chat_channel_id, id
    SQL

    create_chat_threads(threads) do |row|
      channel_id = chat_channel_id_from_original_id(row["chat_channel_id"])
      original_message_user_id = user_id_from_imported_id(row["original_message_user_id"])

      next if chat_thread_id_from_original_id(row["id"]).present?
      next if channel_id.blank? || original_message_user_id.blank?

      # Messages aren't imported yet. Use a placeholder `original_message_id` for now.
      # Actual original_message_ids will be set later after messages have been imported
      placeholder_original_message_id = -1

      {
        original_id: row["id"],
        channel_id: channel_id,
        original_message_id: placeholder_original_message_id,
        original_message_user_id: original_message_user_id,
        status: row["status"],
        title: row["title"],
        created_at: to_datetime(row["created_at"]),
        replies_count: row["replies_count"],
      }
    end

    threads.close
  end

  def import_chat_thread_users
    unless defined?(::Chat)
      puts "", "Skipping chat thread users, because the chat plugin is not installed."
      return
    end

    thread_users = query(<<~SQL)
      SELECT *
      FROM chat_thread_users
      ORDER BY chat_thread_id, user_id
    SQL

    puts "", "Importing chat thread users..."

    existing_members = Chat::UserChatThreadMembership.distinct.pluck(:user_id, :thread_id).to_set

    create_thread_users(thread_users) do |row|
      user_id = user_id_from_imported_id(row["user_id"])
      thread_id = chat_thread_id_from_original_id(row["chat_thread_id"])
      last_read_message_id = chat_message_id_from_original_id(row["last_read_message_id"])

      next if user_id.blank? || thread_id.blank?
      next unless existing_members.add?([user_id, thread_id])

      {
        user_id: user_id,
        thread_id: thread_id,
        notification_level: row["notification_level"],
        created_at: to_datetime(row["created_at"]),
        last_read_message_id: last_read_message_id,
      }
    end

    thread_users.close
  end

  def import_chat_messages
    unless defined?(::Chat)
      puts "", "Skipping chat messages, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat messages..."

    messages = query(<<~SQL)
      SELECT *
      FROM chat_messages
      ORDER BY chat_channel_id, created_at, id
    SQL

    create_chat_messages(messages) do |row|
      channel_id = chat_channel_id_from_original_id(row["chat_channel_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next if chat_message_id_from_original_id(row["id"]).present?
      next if channel_id.blank? || user_id.blank?
      next if row["message"].blank? && row["upload_ids"].blank?

      last_editor_id = user_id_from_imported_id(row["last_editor_id"])
      thread_id = chat_thread_id_from_original_id(row["thread_id"])
      deleted_by_id = user_id_from_imported_id(row["deleted_by_id"])
      in_reply_to_id = chat_message_id_from_original_id(row["in_reply_to_id"]) # TODO: this will only work if serial ids are used

      {
        original_id: row["id"],
        chat_channel_id: channel_id,
        user_id: user_id,
        thread_id: thread_id,
        last_editor_id: last_editor_id,
        created_at: to_datetime(row["created_at"]),
        deleted_at: to_datetime(row["deleted_at"]),
        deleted_by_id: deleted_by_id,
        in_reply_to_id: in_reply_to_id,
        message: raw_with_placeholders_interpolated(row["message"], row),
      }
    end

    messages.close
  end

  def import_chat_reactions
    unless defined?(::Chat)
      puts "", "Skipping chat message reactions, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat message reactions..."

    reactions = query(<<~SQL)
      SELECT *
      FROM chat_reactions
      ORDER BY chat_message_id
    SQL

    existing_reactions =
      Chat::MessageReaction.distinct.pluck(:chat_message_id, :user_id, :emoji).to_set

    create_chat_message_reactions(reactions) do |row|
      next if row["emoji"].blank?

      message_id = chat_message_id_from_original_id(row["chat_message_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next if message_id.blank? || user_id.blank?
      next unless existing_reactions.add?([message_id, user_id, row["emoji"]])

      # TODO: Validate emoji

      {
        chat_message_id: message_id,
        user_id: user_id,
        emoji: row["emoji"],
        created_at: to_datetime(row["created_at"]),
      }
    end

    reactions.close
  end

  def import_chat_mentions
    unless defined?(::Chat)
      puts "", "Skipping chat mentions, because the chat plugin is not installed."
      return
    end

    puts "", "Importing chat mentions..."

    mentions = query(<<~SQL)
      SELECT *
      FROM chat_mentions
      ORDER BY chat_message_id
    SQL

    create_chat_mentions(mentions) do |row|
      # TODO: Maybe standardize mention types, instead of requiring converter
      # to set namespaced ruby classes
      chat_message_id = chat_message_id_from_original_id(row["chat_message_id"])
      target_id =
        case row["type"]
        when "Chat::AllMention", "Chat::HereMention"
          nil
        when "Chat::UserMention"
          user_id_from_imported_id(row["target_id"])
        when "Chat::GroupMention"
          group_id_from_imported_id(row["target_id"])
        end

      next if target_id.nil? && %w[Chat::AllMention Chat::HereMention].exclude?(row["type"])

      {
        chat_message_id: chat_message_id,
        target_id: target_id,
        type: row["type"],
        created_at: to_datetime(row["created_at"]),
      }
    end

    mentions.close
  end

  def update_chat_threads
    unless defined?(::Chat)
      puts "", "Skipping chat thread updates, because the chat plugin is not installed."
      return
    end

    puts "", "Updating chat threads..."

    start_time = Time.now

    DB.exec(<<~SQL)
      WITH thread_info AS (
        SELECT
          thread_id,
          MIN(id) AS original_message_id,
          COUNT(id) - 1 AS replies_count,
          MAX(id) AS last_message_id
        FROM
          chat_messages
        WHERE
          thread_id IS NOT NULL
        GROUP BY
          thread_id
      )
      UPDATE chat_threads
      SET
        original_message_id = thread_info.original_message_id,
        replies_count = thread_info.replies_count,
        last_message_id = thread_info.last_message_id
      FROM
        thread_info
      WHERE
        chat_threads.id = thread_info.thread_id;
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def update_chat_membership_metadata
    unless defined?(::Chat)
      puts "",
           "Skipping chat membership metadata updates, because the chat plugin is not installed."
      return
    end

    puts "", "Updating chat membership metadata..."

    start_time = Time.now

    # Ensure the user is caught up on all messages in the channel. The primary aim is to prevent
    # new message indicators from showing up for imported messages. We do this by updating
    # the `last_viewed_at` and `last_read_message_id` columns in `user_chat_channel_memberships`
    # if they were not imported.
    DB.exec(<<~SQL)
      WITH latest_messages AS (
        SELECT
          chat_channel_id,
          MAX(id) AS last_message_id,
          MAX(created_at) AS last_message_created_at
        FROM chat_messages
        WHERE thread_id IS NULL
        GROUP BY chat_channel_id
      )
      UPDATE user_chat_channel_memberships uccm
      SET
        last_read_message_id = COALESCE(uccm.last_read_message_id, lm.last_message_id),
        last_viewed_at = CASE
                           WHEN uccm.last_viewed_at = '#{LAST_VIEWED_AT_PLACEHOLDER}'
                           THEN lm.last_message_created_at + INTERVAL '1 second'
                           ELSE uccm.last_viewed_at
                         END
      FROM latest_messages lm
      WHERE uccm.chat_channel_id = lm.chat_channel_id
    SQL

    # Set `last_read_message_id` in `user_chat_thread_memberships` if none is provided.
    # Similar to the chat channel membership update above, this ensures the user is caught up on messages in the thread.
    DB.exec(<<~SQL)
      WITH latest_thread_messages AS (
        SELECT
            thread_id,
            MAX(id) AS last_message_id
        FROM chat_messages
        WHERE thread_id IS NOT NULL
        GROUP BY thread_id
      )
      UPDATE user_chat_thread_memberships utm
      SET
        last_read_message_id = ltm.last_message_id
      FROM latest_thread_messages ltm
      WHERE utm.thread_id = ltm.thread_id
        AND utm.last_read_message_id IS NULL
    SQL

    puts "  Update took #{(Time.now - start_time).to_i} seconds."
  end

  def import_reaction_users
    unless defined?(DiscourseReactions)
      puts "",
           "Skipping reaction users import, because the Discourse Reactions plugin is not installed."
      return
    end

    puts "", "Importing reaction users..."

    reaction_users = query(<<~SQL)
      SELECT *
      FROM discourse_reactions_reaction_users
      ORDER BY post_id, user_id
    SQL

    existing_reaction_users =
      DiscourseReactions::ReactionUser.pluck(:reaction_id, :user_id, :post_id).to_set

    create_reaction_users(reaction_users) do |row|
      next if row["reaction_id"].blank?

      user_id = user_id_from_imported_id(row["user_id"])
      post_id = post_id_from_imported_id(row["post_id"])
      reaction_id = discourse_reaction_id_from_original_id(row["reaction_id"])

      next if post_id.blank? || user_id.blank? || reaction_id.blank?
      next unless existing_reaction_users.add?([reaction_id, user_id, post_id])

      {
        reaction_id:,
        user_id:,
        created_at: to_datetime(row["created_at"]),
        updated_at: to_datetime(row["updated_at"]),
        post_id:,
      }
    end

    reaction_users.close
  end

  def import_reactions
    unless defined?(DiscourseReactions)
      puts "", "Skipping reactions import, because the Discourse Reactions plugin is not installed."
      return
    end

    puts "", "Importing reactions..."

    reactions = query(<<~SQL)
      SELECT r.*,
            COALESCE((SELECT COUNT(*)
                      FROM discourse_reactions_reaction_users ru
                      WHERE ru.reaction_id = r.id), 0) as count
      FROM discourse_reactions_reactions r
      ORDER BY r.post_id, r.reaction_value
    SQL

    reaction_type_id = DiscourseReactions::Reaction.reaction_types["emoji"]
    existing_reactions = DiscourseReactions::Reaction.pluck(:post_id, :reaction_value).to_set

    create_reactions(reactions) do |row|
      next if row["id"].blank?

      post_id = post_id_from_imported_id(row["post_id"])

      next if post_id.blank? || row["reaction_value"].blank?
      next unless existing_reactions.add?([post_id, row["reaction_value"]])

      {
        original_id: row["id"],
        post_id: post_id,
        reaction_type: reaction_type_id,
        reaction_value: row["reaction_value"],
        reaction_users_count: row["count"],
        created_at: to_datetime(row["created_at"]),
        updated_at: to_datetime(row["updated_at"]),
      }
    end

    reactions.close
  end

  def import_reaction_shadow_likes
    unless defined?(DiscourseReactions)
      puts "",
           "Skipping reaction shadow likes import, because the Discourse Reactions plugin is not installed."
      return
    end

    puts "", "Importing reaction shadow likes..."

    reactions = query(<<~SQL)
      SELECT u.post_id, u.user_id, r.reaction_value, u.created_at
        FROM discourse_reactions_reactions r
             JOIN discourse_reactions_reaction_users u ON r.id = u.reaction_id
       ORDER BY u.post_id, u.user_id
    SQL

    post_action_type_id = PostActionType.types[:like]
    existing_likes =
      PostAction.where(post_action_type_id: post_action_type_id).pluck(:post_id, :user_id).to_set

    create_post_actions(reactions) do |row|
      next if reaction_excluded_from_like?(row["reaction_value"])

      post_id = post_id_from_imported_id(row["post_id"])
      user_id = user_id_from_imported_id(row["user_id"])

      next unless post_id && user_id
      next unless existing_likes.add?([post_id, user_id])

      {
        post_id: post_id,
        user_id: user_id,
        post_action_type_id: post_action_type_id,
        created_at: to_datetime(row["created_at"]),
      }
    end

    reactions.close
  end

  def reaction_excluded_from_like?(reaction_value)
    DiscourseReactions::Reaction.reactions_excluded_from_like.include?(reaction_value)
  end

  def calculate_external_url(row)
    external_url = row["external_url"].dup
    placeholders = row["external_url_placeholders"]&.then { |json| JSON.parse(json) }
    return external_url unless placeholders

    placeholders.each do |placeholder|
      case placeholder["type"]
      when "topic_url"
        topic_id = topic_id_from_imported_id(placeholder["id"])
        topic = topic_id && Topic.find_by(id: topic_id)
        unless topic
          puts "WARNING: Skipping permalink #{row["url"]}: missing topic target for #{placeholder["id"]}"
          return nil
        end
        external_url.gsub!(placeholder["placeholder"], "t/#{topic.slug}/#{topic.id}")
      when "category_url"
        category_id = category_id_from_imported_id(placeholder["id"])
        category = Category.find(category_id)
        external_url.gsub!(
          placeholder["placeholder"],
          "c/#{category.slug_path.join("/")}/#{category.id}",
        )
      when "category_slug_ref"
        category_id = category_id_from_imported_id(placeholder["id"])
        category = Category.find(category_id)
        external_url.gsub!(placeholder["placeholder"], category.slug_ref)
      when "tag_name"
        if @tag_mapping
          tag_id = @tag_mapping[placeholder["id"]]
          tag = Tag.find(tag_id)
          external_url.gsub!(placeholder["placeholder"], tag.name)
        end
      else
        raise "Unknown placeholder type: #{placeholder[:type]}"
      end
    end

    external_url
  end

  def create_connection(path)
    sqlite = SQLite3::Database.new(path, results_as_hash: true)
    sqlite.busy_timeout = 60_000 # 60 seconds
    sqlite.journal_mode = "wal"
    sqlite.synchronous = "normal"
    sqlite
  end

  def query(sql, *bind_vars, db: @source_db)
    result_set = db.prepare(sql).execute(*bind_vars)

    if block_given?
      result = yield result_set
      result_set.close
      result
    else
      result_set
    end
  end

  def table_column_names(table_name, db: @source_db)
    @table_column_names ||= {}
    @table_column_names[[db.object_id, table_name]] ||= query(
      "PRAGMA table_info(#{table_name})",
      db:,
    ) { |rows| rows.map { |row| row["name"] }.to_set }
  end

  def to_date(text)
    text.present? ? Date.parse(text) : nil
  end

  def to_datetime(text)
    text.present? ? DateTime.parse(text) : nil
  end

  def to_boolean(value)
    value == 1
  end

  def to_nullable_boolean(value)
    value.nil? ? nil : to_boolean(value)
  end

  def anon_username_suffix
    while true
      suffix = (SecureRandom.random_number * 100_000_000).to_i
      break if @anonymized_user_suffixes.exclude?(suffix)
    end

    @anonymized_user_suffixes << suffix
    suffix
  end
end

BulkImport::Generic.new(ARGV[0], ARGV[1]).start if $PROGRAM_NAME == __FILE__