# frozen_string_literal: true

class AddUpdateVulnerabilityReadsLocationTrigger < Gitlab::Database::Migration[1.0]
  include Gitlab::Database::SchemaHelpers

  TRIGGER_NAME = 'trigger_update_location_on_vulnerability_occurrences_update'
  FUNCTION_NAME = 'update_location_from_vulnerability_occurrences'

  def up
    create_trigger_function(FUNCTION_NAME, replace: true) do
      <<~SQL
        UPDATE
          vulnerability_reads
        SET
          location_image = NEW.location->>'image',
          cluster_agent_id = NEW.location->'kubernetes_resource'->>'agent_id'
        WHERE
          vulnerability_id = NEW.vulnerability_id;
        RETURN NULL;
      SQL
    end

    execute(<<~SQL)
      CREATE TRIGGER #{TRIGGER_NAME}
      AFTER UPDATE ON vulnerability_occurrences
      FOR EACH ROW
      WHEN (
        NEW.report_type IN (2, 7) AND (
          OLD.location->>'image' IS DISTINCT FROM NEW.location->>'image' OR
          OLD.location->'kubernetes_resource'->>'agent_id' IS DISTINCT FROM NEW.location->'kubernetes_resource'->>'agent_id'
        )
      )
      EXECUTE PROCEDURE #{FUNCTION_NAME}();
    SQL
  end

  def down
    drop_trigger(:vulnerability_occurrences, TRIGGER_NAME)
    drop_function(FUNCTION_NAME)
  end
end