constexpr auto SEARCH_SIMPLIFIED_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT rowid FROM entries WHERE simplified GLOB ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT definition_id, definition "
      "    FROM definitions "
      "    WHERE fk_entry_id IN matching_entry_ids "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT definition_id, fk_chinese_sentence_id "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON mdi.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_object( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        json(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      definition, "
      "      label "
      "    FROM definitions "
      "    WHERE "
      "      definitions.definition_id IN ( "
      "        SELECT definition_id FROM matching_definition_ids "
      "      ) "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      json_object( "
      "        'definition', "
      "        definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        json_group_array(json(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      json_object( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        json_group_array(json(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      json_group_array(json(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY frequency DESC "
      "  ) "
      "SELECT "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto SEARCH_TRADITIONAL_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT rowid FROM entries WHERE traditional GLOB ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT definition_id, definition "
      "    FROM definitions "
      "    WHERE fk_entry_id IN matching_entry_ids "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT definition_id, fk_chinese_sentence_id "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON mdi.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_object( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        json(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      definition, "
      "      label "
      "    FROM definitions "
      "    WHERE "
      "      definitions.definition_id IN ( "
      "        SELECT definition_id FROM matching_definition_ids "
      "      ) "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      json_object( "
      "        'definition', "
      "        definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        json_group_array(json(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      json_object( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        json_group_array(json(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      json_group_array(json(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY frequency DESC "
      "  ) "
      "SELECT "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto GLOB_STR = "GLOB";
constexpr auto REGEXP_STR = "REGEXP";

constexpr auto SEARCH_JYUTPING_EXISTS_QUERY = "SELECT EXISTS ( "
                                              "  SELECT "
                                              "    rowid "
                                              "  FROM entries "
                                              "  WHERE jyutping %1 ? "
                                              ") AS existence ";

constexpr auto SEARCH_JYUTPING_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT rowid FROM entries WHERE jyutping %1 ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT definition_id, definition "
      "    FROM definitions "
      "    WHERE fk_entry_id IN matching_entry_ids "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT definition_id, fk_chinese_sentence_id "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON mdi.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_object( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        json(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      definition, "
      "      label "
      "    FROM definitions "
      "    WHERE "
      "      definitions.definition_id IN ( "
      "        SELECT definition_id FROM matching_definition_ids "
      "      ) "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      json_object( "
      "        'definition', "
      "        definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        json_group_array(json(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      json_object( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        json_group_array(json(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      json_group_array(json(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY frequency DESC "
      "  ) "
      "SELECT "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto SEARCH_PINYIN_EXISTS_QUERY = "SELECT EXISTS ( "
                                            "  SELECT "
                                            "    rowid "
                                            "  FROM entries "
                                            "  WHERE pinyin %1 ? "
                                            ") AS existence ";

constexpr auto SEARCH_PINYIN_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT "
      "      rowid "
      "    FROM entries "
      "    WHERE pinyin %1 ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT "
      "      definition_id, "
      "      definition "
      "    FROM definitions "
      "    WHERE fk_entry_id IN matching_entry_ids "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_chinese_sentence_id "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON mdi.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT "
      "          fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_object( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        json(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      definition, "
      "      label "
      "    FROM definitions "
      "    WHERE "
      "      definitions.definition_id IN ( "
      "        SELECT "
      "          definition_id "
      "        FROM matching_definition_ids "
      "      ) "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      json_object( "
      "        'definition', "
      "        definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        json_group_array(json(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      json_object( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        json_group_array(json(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      json_group_array(json(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY frequency DESC "
      "  ) "
      "SELECT "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto SEARCH_ENGLISH_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      rowid AS definition_id, "
      "      bm25(definitions_fts, 0, 1) AS RANK "
      "    FROM definitions_fts "
      "    WHERE definitions_fts MATCH ? AND definition LIKE ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT "
      "      definition_id, "
      "      definition "
      "    FROM definitions "
      "    WHERE "
      "      fk_entry_id IN ( "
      "        SELECT "
      "          fk_entry_id "
      "        FROM matching_entry_ids "
      "      ) "
      "  ), "
      "  definitions_and_ranks AS ( "
      "    SELECT "
      "      mdi.definition_id AS definition_id, "
      "      mdi.definition AS definition, "
      "      mei.rank AS RANK "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      LEFT JOIN matching_entry_ids AS mei "
      "        ON mdi.definition_id = mei.definition_id "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_chinese_sentence_id "
      "    FROM "
      "      definitions_and_ranks AS dar "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON dar.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      JSON_GROUP_ARRAY( "
      "        DISTINCT JSON_OBJECT( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT "
      "          fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      JSON_OBJECT( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        JSON(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      dar.definition_id, "
      "      d.fk_entry_id, "
      "      d.fk_source_id, "
      "      d.definition, "
      "      d.label, "
      "      RANK "
      "    FROM "
      "      definitions_and_ranks AS dar "
      "      JOIN definitions AS d "
      "        ON dar.definition_id = d.definition_id "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      md.rank AS RANK, "
      "      JSON_OBJECT( "
      "        'definition', "
      "        definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        JSON_GROUP_ARRAY(JSON(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      CASE sourceshortname "
      "        WHEN 'ABY' THEN AVG(mdws.rank) * 3 "
      "        WHEN 'CCY' THEN AVG(mdws.rank) * 3 "
      "        WHEN 'WHK' THEN AVG(mdws.rank) * 3 "
      "        ELSE AVG(mdws.rank) "
      "      END AS RANK, "
      "      JSON_OBJECT( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        JSON_GROUP_ARRAY(JSON(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      SUM(RANK) AS RANK, "
      "      JSON_GROUP_ARRAY(JSON(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY RANK ASC, frequency DESC "
      "  ) "
      "SELECT "
      "  RANK, "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto SEARCH_UNIQUE_QUERY
    = "WITH "
      "  matching_entry_ids AS ( "
      "    SELECT rowid "
      "    FROM entries "
      "    WHERE "
      "      simplified LIKE ? "
      "      AND traditional LIKE ? "
      "      AND jyutping LIKE ? "
      "      AND pinyin LIKE ? "
      "  ), "
      "  matching_definition_ids AS ( "
      "    SELECT definition_id, definition "
      "    FROM definitions "
      "    WHERE fk_entry_id IN matching_entry_ids "
      "  ), "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT definition_id, fk_chinese_sentence_id "
      "    FROM "
      "      matching_definition_ids AS mdi "
      "      JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON mdi.definition_id = dcsl.fk_definition_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      mcsi.fk_chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      JOIN sentence_links AS sl "
      "        ON mcsi.fk_chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "    GROUP BY mcsi.fk_chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT fk_chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_object( "
      "        'traditional', "
      "        traditional, "
      "        'simplified', "
      "        simplified, "
      "        'pinyin', "
      "        pinyin, "
      "        'jyutping', "
      "        jyutping, "
      "        'language', "
      "        language, "
      "        'translations', "
      "        json(translation) "
      "      ) AS sentence "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.fk_chinese_sentence_id "
      "  ), "
      "  matching_definitions AS ( "
      "    SELECT "
      "      definition_id, "
      "      fk_entry_id, "
      "      fk_source_id, "
      "      definition, "
      "      label "
      "    FROM definitions "
      "    WHERE "
      "      definitions.definition_id IN ( "
      "        SELECT definition_id FROM matching_definition_ids "
      "      ) "
      "  ), "
      "  matching_definitions_with_sentences AS ( "
      "    SELECT "
      "      md.fk_entry_id, "
      "      md.fk_source_id, "
      "      json_object( "
      "        'definition', "
      "        md.definition, "
      "        'label', "
      "        label, "
      "        'sentences', "
      "        json_group_array(json(sentence)) "
      "      ) AS definition "
      "    FROM "
      "      matching_definitions AS md "
      "      LEFT JOIN matching_chinese_sentence_ids AS mcsi "
      "        ON md.definition_id = mcsi.definition_id "
      "      LEFT JOIN matching_sentences_with_translations AS mswt "
      "        ON mcsi.fk_chinese_sentence_id = mswt.chinese_sentence_id "
      "    GROUP BY md.definition_id "
      "  ), "
      "  matching_definition_groups AS ( "
      "    SELECT "
      "      fk_entry_id, "
      "      json_object( "
      "        'source', "
      "        sourcename, "
      "        'definitions', "
      "        json_group_array(json(definition)) "
      "      ) AS definitions "
      "    FROM "
      "      matching_definitions_with_sentences AS mdws "
      "      LEFT JOIN sources "
      "        ON sources.source_id = mdws.fk_source_id "
      "    GROUP BY fk_entry_id, fk_source_id "
      "  ), "
      "  matching_entries AS ( "
      "    SELECT "
      "      simplified, "
      "      traditional, "
      "      jyutping, "
      "      pinyin, "
      "      json_group_array(json(definitions)) AS definitions "
      "    FROM "
      "      matching_definition_groups AS mdg "
      "      LEFT JOIN entries "
      "        ON entries.entry_id = mdg.fk_entry_id "
      "    GROUP BY entry_id "
      "    ORDER BY frequency DESC "
      "  ) "
      "SELECT "
      "  simplified, "
      "  traditional, "
      "  jyutping, "
      "  pinyin, "
      "  definitions "
      "FROM matching_entries; ";

constexpr auto SEARCH_TRADITIONAL_SENTENCES_QUERY
    = "WITH "
      "  matching_chinese_sentence_ids AS ( "
      "    SELECT chinese_sentence_id "
      "    FROM chinese_sentences "
      "    WHERE traditional LIKE ? ESCAPE '\\' "
      "  ), "
      "  translations_with_source AS ( "
      "    SELECT "
      "      s.sourcename AS source, "
      "      mcsi.chinese_sentence_id AS chinese_sentence_id, "
      "      json_group_array( "
      "        DISTINCT json_object( "
      "          'sentence', "
      "          sentence, "
      "          'language', "
      "          language, "
      "          'direct', "
      "          direct "
      "        ) "
      "      ) AS translation "
      "    FROM "
      "      matching_chinese_sentence_ids AS mcsi "
      "      LEFT JOIN sentence_links AS sl "
      "        ON mcsi.chinese_sentence_id = sl.fk_chinese_sentence_id "
      "      LEFT JOIN nonchinese_sentences AS ncs "
      "        ON ncs.non_chinese_sentence_id = sl.fk_non_chinese_sentence_id "
      "      LEFT JOIN sources AS s "
      "        ON s.source_id = sl.fk_source_id "
      "    GROUP BY s.sourcename, mcsi.chinese_sentence_id "
      "  ), "
      "  matching_translations AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      json_group_array( "
      "        json_object( "
      "          'source', "
      "          source, "
      "          'translations', "
      "          json(translation) "
      "        ) "
      "      ) AS translations "
      "    FROM translations_with_source AS tws "
      "    GROUP BY chinese_sentence_id "
      "  ), "
      "  matching_sentences AS ( "
      "    SELECT "
      "      chinese_sentence_id, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language "
      "    FROM chinese_sentences AS cs "
      "    WHERE "
      "      chinese_sentence_id IN ( "
      "        SELECT chinese_sentence_id "
      "        FROM matching_chinese_sentence_ids "
      "      ) "
      "  ), "
      "  matching_sentences_with_translations AS ( "
      "    SELECT "
      "      max(sourcename) AS sourcename, "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language, "
      "      translations "
      "    FROM "
      "      matching_sentences AS ms "
      "      LEFT JOIN matching_translations AS mt "
      "        ON ms.chinese_sentence_id = mt.chinese_sentence_id "
      "      LEFT JOIN definitions_chinese_sentences_links AS dcsl "
      "        ON ms.chinese_sentence_id = dcsl.fk_chinese_sentence_id "
      "      LEFT JOIN definitions AS d "
      "        ON dcsl.fk_definition_id = d.definition_id "
      "      LEFT JOIN sources AS s ON d.fk_source_id = s.source_id "
      "    GROUP BY "
      "      traditional, "
      "      simplified, "
      "      pinyin, "
      "      jyutping, "
      "      language, "
      "      translations "
      "    ORDER BY ms.chinese_sentence_id "
      "  ) "
      "SELECT "
      "  sourcename, "
      "  traditional, "
      "  simplified, "
      "  pinyin, "
      "  jyutping, "
      "  language, "
      "  translations "
      "FROM matching_sentences_with_translations; ";
