# frozen_string_literal: true

class CreateVulnerabilityReadsForAnExistingVulnerabilityRecord < Gitlab::Database::Migration[2.0]
  include Gitlab::Database::SchemaHelpers

  FUNCTION_NAME = 'insert_vulnerability_reads_from_vulnerability'
  TRIGGER_NAME = 'trigger_insert_vulnerability_reads_from_vulnerability'

  enable_lock_retries!

  def up
    execute(<<~SQL)
      CREATE OR REPLACE FUNCTION #{FUNCTION_NAME}() RETURNS trigger
          LANGUAGE plpgsql
          AS $$
      DECLARE
        scanner_id bigint;
        uuid uuid;
        location_image text;
        cluster_agent_id text;
        casted_cluster_agent_id bigint;
      BEGIN
        SELECT
          v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint)
        INTO
          scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id
        FROM
           vulnerability_occurrences v_o
        WHERE
          v_o.vulnerability_id = NEW.id
        LIMIT 1;

        INSERT INTO vulnerability_reads (vulnerability_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
          VALUES (NEW.id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
          ON CONFLICT(vulnerability_id) DO NOTHING;
        RETURN NULL;
      END
      $$;
    SQL

    execute(<<~SQL)
      DROP TRIGGER IF EXISTS #{TRIGGER_NAME} ON vulnerabilities;
    SQL

    execute(<<~SQL)
      CREATE TRIGGER #{TRIGGER_NAME}
      AFTER UPDATE ON vulnerabilities
      FOR EACH ROW
      WHEN (
        OLD.present_on_default_branch IS NOT true AND NEW.present_on_default_branch IS true
      )
      EXECUTE PROCEDURE #{FUNCTION_NAME}();
    SQL
  end

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