# frozen_string_literal: true

module EE
  module Gitlab
    module BackgroundMigration
      module UpdateVulnerabilityOccurrencesLocation
        LIST_SEPARATOR = ', '

        class Occurrence < ActiveRecord::Base
          include ::EachBatch

          self.table_name = 'vulnerability_occurrences'
        end

        def perform(start_id, end_id)
          Occurrence.select(:id, :raw_metadata).where(id: start_id..end_id, location: nil).each_batch(of: 500) do |occurrences|
            location_values = parse_location(occurrences)

            next if location_values.empty?

            batch_update_location(location_values)
            sleep(0.1.seconds)
          end
        end

        def parse_location(occurrences)
          location_values = []
          occurrences.each do |occurrence|
            begin
              raw_metadata = ::Gitlab::Json.parse(occurrence.raw_metadata)
            rescue JSON::ParserError
              next
            end

            next if raw_metadata.empty?

            if raw_metadata['location']
              location_id_pair = list_of([occurrence.id, "'#{raw_metadata['location'].to_json}'::jsonb"])
              location_values << "(#{location_id_pair})"
            end
          end

          location_values
        end

        def batch_update_location(location_values)
          Occurrence.connection.exec_update <<~SQL
            WITH cte(cte_id, cte_location) AS #{::Gitlab::Database::AsWithMaterialized.materialized_if_supported} (VALUES #{list_of(location_values)})
            UPDATE vulnerability_occurrences
            SET
              location = cte_location
            FROM
              cte
            WHERE
              cte_id = id
          SQL
        end

        def list_of(list)
          list.join(LIST_SEPARATOR)
        end
      end
    end
  end
end
