-- search CVEs
-- FIXME: should this be moved to cve/search
SELECT COALESCE(json_group_array(json_object(
         'cve_id', b.cve_id,
         'nvd_id', c.nvd_id,
         'description', e.value,
         'v3_base_score', f.v3_base_score / 10.0,
         'v3_severity', g.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_nvd_ids c
    ON (c.cve_id = b.cve_id)
  JOIN cve_descriptions d
    ON (d.cve_id = b.cve_id)
  JOIN descriptions e
    ON (e.desc_id = d.desc_id)
  JOIN item_cvss_v3_impacts f
    ON (f.item_id = b.item_id)
  JOIN severities g
    ON (g.severity_id = f.v3_severity_id)
 
 WHERE e.lang LIKE 'en%'

 ORDER BY a.min_rank;