-- search CVEs
-- FIXME: should this be moved to cve/search
SELECT COALESCE(json_group_array(json_object(
         'cve_id', b.cve_id,
         'nvd_cve_id', printf('CVE-%4d-%04d', b.cve_year, b.cve_num),
         'description', d.value,
         'v3_base_score', e.v3_base_score / 10.0,
         'v3_severity', f.name,
         'rank', a.min_rank
       )), '[]')

  FROM (
    SELECT cve_id,
           MIN(rank) AS min_rank
      FROM cve_fts_all
     WHERE cve_fts_all MATCH :q
     GROUP BY cve_id
  ) a
  JOIN cves b
    ON (b.cve_id = a.cve_id)
  JOIN cve_descriptions c
    ON (c.cve_id = b.cve_id)
  JOIN descriptions d
    ON (d.desc_id = c.desc_id)
  JOIN item_cvss_v3_impacts e
    ON (e.item_id = b.item_id)
  JOIN severities f
    ON (f.severity_id = e.v3_severity_id)
 
 WHERE d.lang = 'en'

 ORDER BY a.min_rank;