# frozen_string_literal: true

class UpdateInsertOrUpdateVulnerabilityReadsFunction < Gitlab::Database::Migration[2.3]
  milestone '18.3'

  def up
    execute <<~SQL
      CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
          LANGUAGE plpgsql
          AS $$
      DECLARE
        severity smallint;
        state smallint;
        report_type smallint;
        resolved_on_default_branch boolean;
        present_on_default_branch boolean;
        has_issues boolean;
        has_merge_request boolean;
      BEGIN
        IF (SELECT current_setting('vulnerability_management.dont_execute_db_trigger', true) = 'true') THEN
          RETURN NULL;
        END IF;

        IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
          RETURN NULL;
        END IF;

        IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
          RETURN NULL;
        END IF;

        SELECT
          vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
        INTO
          severity, state, report_type, resolved_on_default_branch, present_on_default_branch
        FROM
          vulnerabilities
        WHERE
          vulnerabilities.id = NEW.vulnerability_id;

        IF present_on_default_branch IS NOT true THEN
          RETURN NULL;
        END IF;

        SELECT
          EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.vulnerability_id)
        INTO
          has_issues;

        SELECT
          EXISTS (SELECT 1 FROM vulnerability_merge_request_links WHERE vulnerability_merge_request_links.vulnerability_id = NEW.vulnerability_id)
        INTO
          has_merge_request;

        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, has_issues, has_merge_request)
          VALUES (NEW.vulnerability_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint), has_issues, has_merge_request)
          ON CONFLICT(vulnerability_id) DO NOTHING;
        RETURN NULL;
      END
      $$;
    SQL
  end

  def down
    execute <<~SQL
      CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
          LANGUAGE plpgsql
          AS $$
      DECLARE
        severity smallint;
        state smallint;
        report_type smallint;
        resolved_on_default_branch boolean;
        present_on_default_branch boolean;
        has_issues boolean;
        has_merge_request boolean;
      BEGIN
        IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
          RETURN NULL;
        END IF;

        IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
          RETURN NULL;
        END IF;

        SELECT
          vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
        INTO
          severity, state, report_type, resolved_on_default_branch, present_on_default_branch
        FROM
          vulnerabilities
        WHERE
          vulnerabilities.id = NEW.vulnerability_id;

        IF present_on_default_branch IS NOT true THEN
          RETURN NULL;
        END IF;

        SELECT
          EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.vulnerability_id)
        INTO
          has_issues;

        SELECT
          EXISTS (SELECT 1 FROM vulnerability_merge_request_links WHERE vulnerability_merge_request_links.vulnerability_id = NEW.vulnerability_id)
        INTO
          has_merge_request;

        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, has_issues, has_merge_request)
          VALUES (NEW.vulnerability_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint), has_issues, has_merge_request)
          ON CONFLICT(vulnerability_id) DO NOTHING;
        RETURN NULL;
      END
      $$;
    SQL
  end
end
