# frozen_string_literal: true

class Vulnerability < ApplicationRecord
  enum severity: %i[low medium high critical]

  has_and_belongs_to_many :releases, counter_cache: true

  scope :order_by, ->(col = 'cve_id', order = :desc) { order("#{col}": order) }
  scope :unknown_severity, -> { where(severity: nil) }

  DESC_LENGTH = 150

  class << self
    def severity_exists?(name)
      severities.key?(name)
    end

    def details(release_id, filter: {}, sort: {})
      ids = release_vulnerability_ids(release_id)
      joins(:releases).where(id: ids).where(type_filter(filter))
                      .where.not(cve_id: excluded_bdsa_ids(ids))
                      .where(severity_filter(filter)).group('vulnerabilities.id').order(data_order(sort))
                      .select("vulnerabilities.id, cve_id, severity, published_on, description,
        ARRAY_TO_STRING((ARRAY_AGG(releases.version ORDER BY releases.released_on DESC))[1:10], ', ')
        AS recent_releases")
    end

    def ransackable_attributes(_auth_object = nil)
      authorizable_ransackable_attributes
    end

    def ransackable_associations(_auth_object = nil)
      authorizable_ransackable_associations
    end

    private

    def release_vulnerability_ids(release_id)
      query = sanitize_sql("SELECT RVT.vulnerability_id FROM releases_vulnerabilities RVT
                            WHERE RVT.release_id= #{release_id}")
      vulnerability_ids = connection.execute(query).values.flatten
      return ['null'] if vulnerability_ids.blank?

      vulnerability_ids.map(&:to_i)
    end

    def severity_filter(filter)
      severity = Vulnerability.severities[filter[:severity]]
      condition = sanitize_sql "vulnerabilities.severity=#{severity}" if severity
      condition ||= 'vulnerabilities.severity is null' if filter[:severity]
      condition
    end

    def type_filter(filter)
      condition = sanitize_sql "cve_id like '#{filter[:type]}%'" if filter[:type].present?
      condition ||= 'cve_id is not null'
      condition
    end

    def data_order(sort)
      order = sanitize_sql "vulnerabilities.#{sort[:col]} #{sort[:direction]}" if sort[:col]
      order ||= 'vulnerabilities.cve_id DESC'
      order
    end

    def excluded_bdsa_ids(ids)
      cve_ids = Vulnerability.where(id: ids).pluck(:cve_id)
      CveBdsa.where(cve_id: cve_ids).pluck(:bdsa_id)
    end
  end

  def years_ago?(year = 1)
    published_on.to_date <= year.to_i.years.ago.to_date
  end
end
