btrc-hub/backend/app/services/counts_candidates.rb

# Bounded, authorised candidates at the same recorded point as the Counts query.
class CountsCandidates
  KEYS = ['resource', 'field', 'query', 'category', 'page', 'locale', 'at',
          'execution_at', 'timezone'].freeze
  LIMIT = 20

  def initialize(params)
    QueryState.validate!(params, KEYS)
    definition = CountsResourceDefinition.new(params.fetch('resource'))
    fields = ['resource_id', 'user']
    fields += ['tags', 'tags_json', 'tags.id'] if definition.resource == 'posts'
    fields << 'members' if definition.resource == 'tag_groups'
    if definition.resource == 'materials'
      fields += ['updated_by_user_id', 'file_blob_id', 'source_file_id', 'drive_file_id']
    end
    raise ArgumentError unless fields.include?(params.fetch('field'))

    @params = params
    @page = Integer(params.fetch('page', '1'), 10)
    raise ArgumentError unless @page.between?(1, 100_000)
    raise ArgumentError if params.fetch('query', '').bytesize > 1000
    category = params['category']
    raise ArgumentError if category.present? && !Tag.categories.key?(category)
  end

  def call
    references = ['tags', 'tags_json', 'tags.id', 'members']
    return resource_candidates unless references.include?(@params['field'])

    options = @params.slice('locale', 'at', 'execution_at', 'timezone')
        .merge('definition' => 'latest', 'resource' => 'tags')
    internal = CountsPopulation.new(options)
    groups_flg = @params['field'] == 'members'
    external = CountsPopulation.new(options.merge(
        'resource' => groups_flg ? 'tag_groups' : 'nico_tags',
        'execution_at' => internal.at.iso8601(6)))
    connection = TagVersion.connection
    sources = [[internal, 'internal', 'v.category', 'v.names'],
        [external, groups_flg ? 'group' : 'external', 'NULL', 'JSON_OBJECT()']]
    sources = sources.map do |population, kind, category, names|
      scope = population.scope.except(:select)
      identity = "v.#{ population.definition.keys.first }"
      scope = search(scope, identity, 'v.name', names)
      value = ['tags.id', 'members'].include?(@params['field']) ?
          "CAST(#{ identity } AS CHAR)" : "CONCAT('#{ kind }:', #{ identity })"
      scope.select(Arel.sql(
          "#{ value } AS value, #{ identity } AS id, " +
          "v.name AS name, #{ names } AS names, #{ category } AS category, '#{ kind }' AS kind"))
          .to_sql
    end
    clauses = []
    if @params['category'].present?
      clauses << ApplicationRecord.sanitize_sql_array(['category = ?', @params['category']])
    end
    where = clauses.empty? ? '' : "WHERE #{ clauses.join(' AND ') }"
    sql = <<~SQL
      SELECT /*+ MAX_EXECUTION_TIME(10000) */ * FROM (#{ sources.join(' UNION ALL ') }) choices
      #{ where } ORDER BY kind, id LIMIT #{ LIMIT + 1 } OFFSET #{ (@page - 1) * LIMIT }
    SQL
    # Sources already contain quoted search terms. Do not reinterpret their % or ? as binds.
    rows = connection.select_all(sql).to_a
    rows.each { _1['names'] = JSON.parse(_1['names']) if _1['names'].is_a?(String) }
    { rows: rows.first(LIMIT), page: @page, more: rows.length > LIMIT,
      execution_at: internal.at.iso8601(6) }
  end

  private

  def search(scope, identity, name, names)
    return scope if @params['query'].blank?

    pattern = "%#{ ApplicationRecord.sanitize_sql_like(@params['query']) }%"
    scope.where("(#{ name } LIKE ? OR CAST(#{ identity } AS CHAR) = ? OR EXISTS (" +
        "SELECT 1 FROM JSON_TABLE(COALESCE(JSON_EXTRACT(#{ names }, '$**.name'), " +
        "JSON_ARRAY()), '$[*]' COLUMNS (name TEXT PATH '$')) candidate_name " +
        'WHERE candidate_name.name LIKE ?))', pattern, @params['query'], pattern)
  end

  def resource_candidates
    options = @params.slice('at', 'execution_at', 'timezone')
        .merge('resource' => @params.fetch('resource'), 'definition' => 'latest')
    actor_flg = ['user', 'updated_by_user_id'].include?(@params['field'])
    options['definition'] = 'history' if actor_flg
    population = CountsPopulation.new(options)
    scope = population.scope.except(:select)
    if actor_flg
      point = population.timestamp(@params['at']) || population.at
      raise ArgumentError if point > population.at

      column = @params['field'] == 'user' ? 'created_by_user_id' : 'updated_by_user_id'
      identities = scope.where('v.created_at <= ?', point).select("v.#{ column }")
      scope = User.where(id: identities)
      identity = 'users.id'
      name = 'users.name'
      names = 'JSON_OBJECT()'
    else
      definition = population.definition
      column = @params['field'] == 'resource_id' ? definition.keys.first : @params['field']
      if column == 'drive_file_id'
        column = 'source_file_id'
        scope = scope.where('v.source_kind' => 'google_drive_file')
      end
      identity = "v.#{ column }"
      label = ['title', 'name', 'url'].find { definition.fields.key?(_1) }
      name = label ? "v.#{ label }" : 'NULL'
      names = definition.resource == 'tags' ? 'v.names' : 'JSON_OBJECT()'
      if ['file_blob_id', 'source_file_id', 'drive_file_id'].include?(@params['field'])
        scope = scope.where.not("v.#{ column }" => nil).group(Arel.sql(identity))
        name = 'v.file_filename'
      end
    end
    scope = search(scope, identity, name, names)
    title = ['file_blob_id', 'source_file_id', 'drive_file_id'].include?(@params['field']) ?
        "MIN(#{ name })" : name
    query = scope.optimizer_hints('MAX_EXECUTION_TIME(10000)').distinct
        .select(Arel.sql("#{ identity } AS id, #{ title } AS name, #{ names } AS names"))
        .order(Arel.sql(identity)).limit(LIMIT + 1).offset((@page - 1) * LIMIT)
    rows = scope.klass.connection.select_all(query.to_sql).to_a.map do |values|
      values['names'] = JSON.parse(values['names']) if values['names'].is_a?(String)
      values.merge('value' => values.fetch('id').to_s)
    end
    { rows: rows.first(LIMIT), page: @page, more: rows.length > LIMIT,
      execution_at: population.at.iso8601(6) }
  end
end