# frozen_string_literal: true

module Gitlab
  module BackgroundMigration
    class BackfillArchivedAndTraversalIdsToVulnerabilityStatistics < BatchedMigrationJob
      operation_name :backfill_archived_and_traversal_ids_in_vulnerability_statistics_table
      feature_category :vulnerability_management

      UPDATE_SQL = <<~SQL
      WITH project_to_archived_traversal_ids (project_id, archived, traversal_ids) AS (
          %{with_values}
      )
        UPDATE
          vulnerability_statistics
        SET
          traversal_ids = project_to_archived_traversal_ids.traversal_ids,
          archived = project_to_archived_traversal_ids.archived
        FROM
          project_to_archived_traversal_ids
        WHERE
          project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id AND
          vulnerability_statistics.id IN (%{vulnerability_statistics_ids})
      SQL

      def perform
        each_sub_batch do |sub_batch|
          # rubocop:disable CodeReuse/ActiveRecord -- specific for this backfill
          vulnerability_statistics_ids,  affected_project_ids = sub_batch.pluck(:id, :project_id).transpose
          # rubocop:enable CodeReuse/ActiveRecord
          values_sql = project_ids_to_values_sql(affected_project_ids)

          next if values_sql.blank?

          ::SecApplicationRecord.connection.execute(update_sql(vulnerability_statistics_ids,
            values_sql))
        end
      end

      private

      class Project < ::Gitlab::Database::Migration[2.2]::MigrationRecord
        self.table_name = 'projects'
        belongs_to :namespace
        scope :joins_namespace, -> { joins(:namespace) }
        # rubocop: disable CodeReuse/ActiveRecord -- redefining to avoid using application code in migration
        scope :archived_and_traversal_ids, ->(project_ids) {
          where(id: project_ids).joins_namespace.limit(project_ids.length)
                                .pluck(:id, :archived, :traversal_ids)
        }
        # rubocop: enable CodeReuse/ActiveRecord
      end

      def project_ids_to_values_sql(project_ids)
        # Query main DB for namespace and traversal_ids for affected projects by id
        project_info = Project.archived_and_traversal_ids(project_ids)
        return if project_info.blank?

        values = project_info.map do |row|
          [
            row[0],
            row[1],
            Arel.sql("ARRAY#{row[2]}::bigint[]")
          ]
        end

        Arel::Nodes::ValuesList.new(values).to_sql
      end

      def update_sql(vulnerability_statistics_ids, with_project_information)
        format(UPDATE_SQL,
          vulnerability_statistics_ids: vulnerability_statistics_ids.map { |id| Integer(id) }.join(', '),
          with_values: with_project_information)
      end
    end
  end
end
