# frozen_string_literal: true

module EE
  module Gitlab
    module BackgroundMigration
      # This class updates vulnerability feedback entities with no pipeline id assigned.
      module PopulateVulnerabilityFeedbackPipelineId
        extend ::Gitlab::Utils::Override

        SECURITY_REPORT_FILE_TYPES = {
          sast: 5,
          dependency_scanning: 6,
          container_scanning: 7,
          dast: 8,
          license_management: 10,
          license_scanning: 101,
          secret_detection: 21,
          coverage_fuzzing: 23,
          api_fuzzing: 26
        }.freeze

        override :perform
        def perform(project_ids)
          filtered_project_ids = ::Project.non_archived.without_deleted.where(id: project_ids).pluck(:id)

          update_vulnerability_feedback_with_pipeline_id(pipelines_with_security_reports_subquery(filtered_project_ids))
          update_vulnerability_feedback_with_pipeline_id(legacy_pipelines_with_security_reports_subquery(filtered_project_ids))
        end

        private

        def update_vulnerability_feedback_with_pipeline_id(subquery)
          update_feedback_pipeline_id_sql = <<~SQL
            UPDATE "vulnerability_feedback"
            SET pipeline_id = "pipelines_with_reports"."id"
            FROM (#{subquery}) AS pipelines_with_reports
            WHERE "vulnerability_feedback"."pipeline_id" IS NULL
              AND "vulnerability_feedback"."project_id" = "pipelines_with_reports"."project_id";
          SQL

          connection.execute(update_feedback_pipeline_id_sql)
        end

        def pipelines_with_security_reports_subquery(project_ids)
          <<~SQL
            SELECT "ci_pipelines"."id", "ci_pipelines"."project_id"
            FROM "ci_pipelines"
            WHERE ("ci_pipelines"."project_id" in (#{project_ids.join(', ')}))
              AND ("ci_pipelines"."status" IN ('success'))
              AND (
                EXISTS (
                  SELECT 1
                  FROM "ci_builds"
                  WHERE "ci_builds"."type" = 'Ci::Build'
                    AND (
                      "ci_builds"."retried" = FALSE
                      OR "ci_builds"."retried" IS NULL
                    )
                    AND (
                      EXISTS (
                        SELECT 1
                        FROM "ci_job_artifacts"
                        WHERE ("ci_builds"."id" = "ci_job_artifacts"."job_id")
                          AND "ci_job_artifacts"."file_type" IN (#{SECURITY_REPORT_FILE_TYPES.except(:license_management, :license_scanning).values.join(", ")})
                      )
                    )
                    AND ("ci_pipelines"."id" = "ci_builds"."commit_id")
                )
              )
            ORDER BY "ci_pipelines"."id" DESC
            LIMIT 1
          SQL
        end

        def legacy_pipelines_with_security_reports_subquery(project_ids)
          <<~SQL
            SELECT "ci_pipelines"."id", "ci_pipelines"."project_id"
            FROM "ci_pipelines"
              INNER JOIN "ci_builds" ON "ci_builds"."commit_id" = "ci_pipelines"."id"
                AND "ci_builds"."type" = 'Ci::Build'
                AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL)
              INNER JOIN "ci_job_artifacts" ON "ci_job_artifacts"."file_type" IN (#{SECURITY_REPORT_FILE_TYPES.values.join(", ")})
                AND "ci_job_artifacts"."job_id" = "ci_builds"."id"
            WHERE ("ci_pipelines"."project_id" in (#{project_ids.join(', ')}))
              AND ("ci_pipelines"."status" IN ('success'))
              AND "ci_builds"."name" IN ('sast', 'secret_detection', 'dependency_scanning', 'container_scanning', 'dast')
            ORDER BY "ci_pipelines"."id" DESC
            LIMIT 1
          SQL
        end

        def connection
          @connection ||= ActiveRecord::Base.connection
        end
      end
    end
  end
end
