# frozen_string_literal: true

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

  TRIGGER_ON_INSERT = 'trigger_update_has_issues_on_vulnerability_issue_links_update'
  INSERT_FUNCTION_NAME = 'set_has_issues_on_vulnerability_reads'

  TRIGGER_ON_DELETE = 'trigger_update_has_issues_on_vulnerability_issue_links_delete'
  DELETE_FUNCTION_NAME = 'unset_has_issues_on_vulnerability_reads'

  def up
    create_trigger_function(INSERT_FUNCTION_NAME, replace: true) do
      <<~SQL
        UPDATE
          vulnerability_reads
        SET
          has_issues = true
        WHERE
          vulnerability_id = NEW.vulnerability_id AND has_issues IS FALSE;
        RETURN NULL;
      SQL
    end

    execute(<<~SQL)
      CREATE OR REPLACE FUNCTION #{DELETE_FUNCTION_NAME}()
      RETURNS TRIGGER
      LANGUAGE plpgsql
      AS $$
      DECLARE
        has_issue_links integer;
      BEGIN
        PERFORM 1
        FROM
          vulnerability_reads
        WHERE
          vulnerability_id = OLD.vulnerability_id
        FOR UPDATE;

        SELECT 1 INTO has_issue_links FROM vulnerability_issue_links WHERE vulnerability_id = OLD.vulnerability_id LIMIT 1;

        IF (has_issue_links = 1) THEN
          RETURN NULL;
        END IF;

        UPDATE
          vulnerability_reads
        SET
          has_issues = false
        WHERE
          vulnerability_id = OLD.vulnerability_id;

        RETURN NULL;
      END
      $$;
    SQL

    execute(<<~SQL)
      CREATE TRIGGER #{TRIGGER_ON_INSERT}
      AFTER INSERT ON vulnerability_issue_links
      FOR EACH ROW
      EXECUTE FUNCTION #{INSERT_FUNCTION_NAME}();
    SQL

    execute(<<~SQL)
      CREATE TRIGGER #{TRIGGER_ON_DELETE}
      AFTER DELETE ON vulnerability_issue_links
      FOR EACH ROW
      EXECUTE FUNCTION #{DELETE_FUNCTION_NAME}();
    SQL
  end

  def down
    drop_trigger(:vulnerability_issue_links, TRIGGER_ON_INSERT)
    drop_function(INSERT_FUNCTION_NAME)
    drop_trigger(:vulnerability_issue_links, TRIGGER_ON_DELETE)
    drop_function(DELETE_FUNCTION_NAME)
  end
end