# frozen_string_literal: true

module Gitlab
  module BackgroundMigration
    class BackfillIdentifierNamesOfVulnerabilityReads < BatchedMigrationJob
      operation_name :backfill_identifier_names
      feature_category :vulnerability_management

      UPDATE_SQL = <<~SQL
        UPDATE vulnerability_reads AS vr
        SET identifier_names = selected_ids.names
        FROM (?) AS selected_ids
        WHERE vr.id = selected_ids.id
      SQL

      class VulnerabilitiesRead < ::SecApplicationRecord
        self.table_name = 'vulnerability_reads'
      end

      def perform
        each_sub_batch do |sub_batch|
          cte = Gitlab::SQL::CTE.new(:batched_relation, sub_batch.limit(40))

          filtered_results = cte
            .apply_to(VulnerabilitiesRead.all)
            .joins(
              'INNER JOIN vulnerability_occurrences vo ' \
                'ON vulnerability_reads.vulnerability_id = vo.vulnerability_id'
            )
            .joins('INNER JOIN vulnerability_occurrence_identifiers voi ON vo.id = voi.occurrence_id')
            .joins('INNER JOIN vulnerability_identifiers vi ON voi.identifier_id = vi.id')
            .group("vulnerability_reads.id")
            .select(
              'vulnerability_reads.id AS id',
              'ARRAY_AGG(vi.name ORDER BY vi.name) AS names'
            )

          update_query = VulnerabilitiesRead.sanitize_sql([UPDATE_SQL, filtered_results])

          connection.execute(update_query)
        end
      end
    end
  end
end
