<commit_before>/** \file    translator.cc
 *  \brief   A CGI-tool for translating vufind tokens and keywords.
 *  \author  <NAME> (<EMAIL>)
 *  \author  <NAME>
 */
/*
    Copyright (C) 2016,2017, Library of the University of Tübingen

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Affero General Public License for more details.

    You should have received a copy of the GNU Affero General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

#include <algorithm>
#include <boost/algorithm/string/trim.hpp>
#include <boost/bind.hpp>
#include <iostream>
#include <string>
#include <set>
#include "DbConnection.h"
#include "DbResultSet.h"
#include "DbRow.h"
#include "HtmlUtil.h"
#include "IniFile.h"
#include "MiscUtil.h"
#include "StringUtil.h"
#include "UrlUtil.h"
#include "util.h"
#include "WebUtil.h"


const int ENTRIES_PER_PAGE(50);
const std::string NO_GND_CODE("-1");
const std::string LANGUAGES_SECTION("Languages");
const std::string TRANSLATION_LANGUAGES_SECTION("TranslationLanguages");
const std::string ADDITIONAL_VIEW_LANGUAGES("AdditionalViewLanguages");
const std::string USER_SECTION("Users");
const std::string ALL_SUPPORTED_LANGUAGES("all");
const std::string SYNONYM_COLUMN_DESCRIPTOR("syn");
const std::string TOKEN_COLUMN_DESCRIPTOR("token");
const int NO_INDEX(-1);
const unsigned int LOOKFOR_PREFIX_LIMIT(3);

enum Category {VUFIND, KEYWORDS};

DbResultSet ExecSqlOrDie(const std::string &select_statement, DbConnection &db_connection) {
    if (unlikely(not db_connection.query(select_statement)))
        Error("SQL Statement failed: " + select_statement + " (" + db_connection.getLastErrorMessage() + ")");
    return db_connection.getLastResultSet();
}


std::vector<std::string> GetLanguageCodesFromTable(DbConnection &db_connection, const std::string &table_name) {
    const std::string query("SELECT DISTINCT language_code from " + table_name + " ORDER BY language_code;");
    DbResultSet result_set(ExecSqlOrDie(query, db_connection));
    std::vector <std::string> language_codes;
    while (const DbRow db_row = result_set.getNextRow()) {
        language_codes.emplace_back(db_row["language_code"]);
    }
    return language_codes;
}


std::vector<std::string> GetLanguageCodes(DbConnection &db_connection) {
    std::vector <std::string> language_codes(GetLanguageCodesFromTable(db_connection, "vufind_translations"));
    for (auto &language_code : GetLanguageCodesFromTable(db_connection, "keyword_translations")) {
        if (std::find(language_codes.begin(), language_codes.end(), language_code) == language_codes.end())
            language_codes.emplace_back(language_code);
    }
    return language_codes;
}


void ShowErrorPage(const std::string &title, const std::string &error_message, const std::string &description = "") {
    std::cout << "Content-Type: text/html; charset=utf-8\r\n\r\n";
    std::cout << "<!DOCTYPE html><html><head><title>" + title + "</title></head>"
              << "<body>"
              << "  <h1>" + error_message + "</h1>"
              << "  <h3>" + description + "</h3>"
              << "</body>"
              << "</html>";
}



std::string GetCGIParameterOrDefault(const std::multimap<std::string, std::string> &cgi_args,
                                     const std::string &parameter_name,
                                     const std::string &default_value) {
    const auto key_and_value(cgi_args.find(parameter_name));
    if (key_and_value == cgi_args.cend())
        return default_value;

    return key_and_value->second;
}


const std::string GetTranslatorOrEmptyString() {
    return (std::getenv("REMOTE_USER") != nullptr) ? std::getenv("REMOTE_USER") : "";
}


const std::string AssembleTermIdentifiers(const std::string &category, const std::string &index, 
                                          const std::string &language_code, const std::string &gnd_code = "", const std::string &translation = "") {

       return std::string(" category=\"" +  UrlUtil::UrlEncode(category) + "\" index=\"" + UrlUtil::UrlEncode(index) + "\" language_code=\"" +
                          UrlUtil::UrlEncode(language_code) + "\" gnd_code=\"" + gnd_code + "\" translation=\"" + translation + "\" ");
}


std::string CreateEditableRowEntry(const std::string &token, const std::string &label, const std::string language_code,
                           const std::string &category, const std::string db_translator, 
                           const std::string &status = "UNKNOWN", const std::string &gnd_code = "") {
    (void)status;
    std::string term_identifiers(AssembleTermIdentifiers(category, token, language_code, gnd_code, label));
    std::string background_color((GetTranslatorOrEmptyString() == db_translator) ? "RoyalBlue" : "LightBlue");
    return  "<td contenteditable=\"true\" class=\"editable_translation\"" + term_identifiers + "style=\"background-color:" + background_color + "\">" 
                + HtmlUtil::HtmlEscape(label) +"</td>";
}


void GetDisplayLanguages(std::vector<std::string> *const display_languages, const std::vector<std::string> &translation_languages,
                         const std::vector<std::string> &additional_view_languages, enum Category category = KEYWORDS) {

    display_languages->clear();

    if (category == KEYWORDS) {
        // Insert German as Display language in any case
        if (std::find(translation_languages.begin(), translation_languages.end(), "ger") == translation_languages.end())
            display_languages->emplace_back("ger");
        // Insert German Synonyms in any case
        display_languages->emplace_back(SYNONYM_COLUMN_DESCRIPTOR);
    }
    else if (category == VUFIND)
        display_languages->emplace_back(TOKEN_COLUMN_DESCRIPTOR);
    display_languages->insert(display_languages->end(), translation_languages.begin(), translation_languages.end());
    display_languages->insert(display_languages->end(), additional_view_languages.begin(), additional_view_languages.end());
}


bool IsTranslatorLanguage(const std::vector<std::string> &translator_languages, const std::string &lang) {
    return std::find(translator_languages.cbegin(), translator_languages.cend(), lang) != translator_languages.cend();
}


std::string CreateNonEditableRowEntry(const std::string &value) {
   return  "<td style=\"background-color:lightgrey\">" +  HtmlUtil::HtmlEscape(value) + "</td>";
}


std::string CreateNonEditableSynonymEntry(const std::vector<std::string> &values, const std::string &separator) {
   std::vector<std::string> html_escaped_values(values.size());
   std::for_each(values.cbegin(), values.cend(), boost::bind(&HtmlUtil::HtmlEscape, _1));
   return "<td style=\"background-color:lightgrey; font-size:small\">" +  StringUtil::Join(values, separator) + "</td>";
}


std::string CreateNonEditableHintEntry(const std::string &value, const std::string gnd_code) {
  return "<td style=\"background-color:lightgrey\"><a href = \"/Keywordchainsearch/Results?lookfor=" + HtmlUtil::HtmlEscape(value) +
                                                   "\" target=\"_blank\">" + HtmlUtil::HtmlEscape(value) + "</a>"
                                                   "<a href=\"http://d-nb.info/gnd/" + HtmlUtil::HtmlEscape(gnd_code) + "\""
                                                   " style=\"float:right\" target=\"_blank\">GND</a></td>";
}


void GetSynonymsForGNDCode(DbConnection &db_connection, const std::string &gnd_code, std::vector<std::string> *const synonyms) {
    synonyms->clear();
    const std::string synonym_query("SELECT translation FROM keyword_translations WHERE gnd_code=\'" + gnd_code + "\' AND status=\'reliable_synonym\'");
    DbResultSet result_set(ExecSqlOrDie(synonym_query, db_connection));
    if (result_set.empty())
        return;

    while (auto db_row = result_set.getNextRow())
        synonyms->emplace_back(db_row["translation"]);
}


int GetColumnIndexForColumnHeading(const std::vector<std::string> &column_headings, const std::vector<std::string> &row_values, const std::string &heading) {
    auto heading_pos(std::find(column_headings.cbegin(), column_headings.cend(), heading));
    if (heading_pos == column_headings.end())
        return NO_INDEX;

    auto index(heading_pos - column_headings.cbegin());
    try {
        row_values.at(index);
    } catch (std::out_of_range& x) {
        return NO_INDEX;
    }
    return index;
}

void GetVuFindTranslationsAsHTMLRowsFromDatabase(DbConnection &db_connection, const std::string &lookfor,
                                                 const std::string &offset, std::vector<std::string> *const rows,
                                                 std::string *const headline, const std::vector<std::string> translator_languages,
                                                 const std::vector<std::string> &additional_view_languages) {

    rows->clear();

    const std::string searchpattern(lookfor.size() <= LOOKFOR_PREFIX_LIMIT ? "LIKE \'" + lookfor + "%\'" : "LIKE \'%" + lookfor+ "%\'");
    const std::string token_where_clause(
            lookfor.empty() ? "" : "WHERE token " + searchpattern);
    const std::string token_query(
            "SELECT token FROM vufind_translations " + token_where_clause + " ORDER BY token LIMIT " + offset + ", " +
            std::to_string(ENTRIES_PER_PAGE));
    const std::string query("SELECT token, translation, language_code, translator FROM vufind_translations "
                            "WHERE token IN (SELECT * FROM (" + token_query + ") as t) ORDER BY token, language_code");

    DbResultSet result_set(ExecSqlOrDie(query, db_connection));

    std::vector<std::string> language_codes(GetLanguageCodes(db_connection));
    std::vector<std::string> display_languages;
    GetDisplayLanguages(&display_languages, translator_languages, additional_view_languages, VUFIND);
    *headline = "<th>" + StringUtil::Join(display_languages, "</th><th>") + "</th>";
    if (result_set.empty())
        return;

    std::vector<std::string> row_values(display_languages.size());
    std::string current_token;
    while (auto db_row = result_set.getNextRow()) {

       std::string token(db_row["token"]);
       std::string translation(db_row["translation"]);
       std::string language_code(db_row["language_code"]);
       std::string translator(db_row["translator"]);
       if (current_token != token){
           if (not current_token.empty())
              rows->emplace_back(StringUtil::Join(row_values, ""));

           current_token = token;
           row_values.clear();
           row_values.resize(display_languages.size(), "<td style=\"background-color:lightgrey\"></td>");
           int token_index(GetColumnIndexForColumnHeading(display_languages, row_values, TOKEN_COLUMN_DESCRIPTOR));
           if (token_index == NO_INDEX)
               continue;
           row_values[token_index] = CreateNonEditableRowEntry(token);
           for (auto translator_language : translator_languages) {
               int index(GetColumnIndexForColumnHeading(display_languages, row_values, translator_language));
               if (index != NO_INDEX) {
                   row_values[index] = CreateEditableRowEntry(current_token, "", language_code, "vufind_translations", translator);
               }
           }
       }

       int index(GetColumnIndexForColumnHeading(display_languages, row_values, language_code));
       if (index == NO_INDEX)
           continue;
       if (IsTranslatorLanguage(translator_languages, language_code))
          row_values[index] = CreateEditableRowEntry(current_token, translation, language_code, "vufind_translations", translator, "", "");
       else
          row_values[index] = CreateNonEditableRowEntry(translation);
   }
   rows->emplace_back(StringUtil::Join(row_values, ""));
}


void GetKeyWordTranslationsAsHTMLRowsFromDatabase(DbConnection &db_connection, const std::string &lookfor,
                                                  const std::string &offset, std::vector<std::string> *const rows,
                                                  std::string *const headline, 
                                                  const std::vector<std::string> &translator_languages, 
                                                  const std::vector<std::string> &additional_view_languages) {
    rows->clear();

    // For short strings make a prefix search, otherwise search substring
    const std::string searchpattern(lookfor.size() <= LOOKFOR_PREFIX_LIMIT ? "LIKE \'" + lookfor + "%\'" : "LIKE \'%" + lookfor+ "%\'");

    const std::string ppn_where_clause(lookfor.empty() ? "" : "AND k.translation " + searchpattern);

    const std::string query("SELECT l.ppn, l.translation, l.language_code, l.gnd_code, l.status, l.translator FROM keyword_translations AS k"
                            " INNER JOIN keyword_translations AS l ON k.language_code=\'ger\' AND k.status=\'reliable\'"
                            " AND k.ppn=l.ppn AND l.status!=\'reliable_synonym\' AND l.status != \'unreliable_synonym\'" +
                            ppn_where_clause +
                            " ORDER BY k.translation" +
                            " LIMIT " + offset + ", " + std::to_string(ENTRIES_PER_PAGE));

    DbResultSet result_set(ExecSqlOrDie(query, db_connection));

    std::vector<std::string> language_codes(GetLanguageCodes(db_connection));

    std::vector<std::string> display_languages;
    GetDisplayLanguages(&display_languages, translator_languages, additional_view_languages);
    *headline = "<th>" + StringUtil::Join(display_languages, "</th><th>") + "</th>";
    if (result_set.empty())
        return;

    std::vector<std::string> row_values(display_languages.size());
    std::string current_ppn;
    while (auto db_row = result_set.getNextRow()) {

       // Add new entries as long as there is a single PPN
       std::string ppn(db_row["ppn"]);
       std::string translation(db_row["translation"]);
       std::string language_code(db_row["language_code"]);
       std::string status(db_row["status"]);
       std::string translator(db_row["translator"]);
       std::string gnd_code(db_row["gnd_code"]);
       if (current_ppn != ppn){
           if (not current_ppn.empty())
              rows->emplace_back(StringUtil::Join(row_values, ""));

           current_ppn = ppn;
           row_values.clear();
           row_values.resize(display_languages.size(), "<td style=\"background-color:lightgrey\"></td>");
           for (auto translator_language : translator_languages) {
               int index(GetColumnIndexForColumnHeading(display_languages, row_values, translator_language));
               if (index != NO_INDEX) {
                   row_values[index] = CreateEditableRowEntry(current_ppn, "", language_code, "keyword_translations", "", status, gnd_code);
               }
           }
       }

       int index(GetColumnIndexForColumnHeading(display_languages, row_values, language_code));
       if (index == NO_INDEX)
           continue;
       if (IsTranslatorLanguage(translator_languages, language_code)) 
          row_values[index] = CreateEditableRowEntry(current_ppn, translation, language_code, "keyword_translations", translator, status, gnd_code);
       else
          row_values[index] = (language_code == "ger") ? CreateNonEditableHintEntry(translation, gnd_code) :
                                     CreateNonEditableRowEntry(translation);

       // Insert Synonyms
       std::vector<std::string> synonyms;
       GetSynonymsForGNDCode(db_connection, gnd_code, &synonyms);
       int synonym_index(GetColumnIndexForColumnHeading(display_languages, row_values, SYNONYM_COLUMN_DESCRIPTOR));
       if (synonym_index == NO_INDEX)
           continue;
       row_values[synonym_index] = CreateNonEditableSynonymEntry(synonyms, "<br/>");
   }
   rows->emplace_back(StringUtil::Join(row_values, ""));
}


void GenerateDirectJumpTable(std::vector<std::string> *const jump_table, enum Category category = KEYWORDS) {<commit_msg>Show macs synonyms<commit_after>/** \file    translator.cc
 *  \brief   A CGI-tool for translating vufind tokens and keywords.
 *  \author  <NAME> (<EMAIL>)
 *  \author  <NAME>
 */
/*
    Copyright (C) 2016,2017, Library of the University of Tübingen

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Affero General Public License for more details.

    You should have received a copy of the GNU Affero General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

#include <algorithm>
#include <boost/algorithm/string/trim.hpp>
#include <boost/bind.hpp>
#include <iostream>
#include <string>
#include <set>
#include "DbConnection.h"
#include "DbResultSet.h"
#include "DbRow.h"
#include "HtmlUtil.h"
#include "IniFile.h"
#include "MiscUtil.h"
#include "StringUtil.h"
#include "UrlUtil.h"
#include "util.h"
#include "WebUtil.h"


const int ENTRIES_PER_PAGE(50);
const std::string NO_GND_CODE("-1");
const std::string LANGUAGES_SECTION("Languages");
const std::string TRANSLATION_LANGUAGES_SECTION("TranslationLanguages");
const std::string ADDITIONAL_VIEW_LANGUAGES("AdditionalViewLanguages");
const std::string USER_SECTION("Users");
const std::string ALL_SUPPORTED_LANGUAGES("all");
const std::string SYNONYM_COLUMN_DESCRIPTOR("syn");
const std::string TOKEN_COLUMN_DESCRIPTOR("token");
const std::string MACS_COLUMN_DESCRIPTOR("macs");
const int NO_INDEX(-1);
const unsigned int LOOKFOR_PREFIX_LIMIT(3);

enum Category {VUFIND, KEYWORDS};

DbResultSet ExecSqlOrDie(const std::string &select_statement, DbConnection &db_connection) {
    if (unlikely(not db_connection.query(select_statement)))
        Error("SQL Statement failed: " + select_statement + " (" + db_connection.getLastErrorMessage() + ")");
    return db_connection.getLastResultSet();
}


std::vector<std::string> GetLanguageCodesFromTable(DbConnection &db_connection, const std::string &table_name) {
    const std::string query("SELECT DISTINCT language_code from " + table_name + " ORDER BY language_code;");
    DbResultSet result_set(ExecSqlOrDie(query, db_connection));
    std::vector <std::string> language_codes;
    while (const DbRow db_row = result_set.getNextRow()) {
        language_codes.emplace_back(db_row["language_code"]);
    }
    return language_codes;
}


std::vector<std::string> GetLanguageCodes(DbConnection &db_connection) {
    std::vector <std::string> language_codes(GetLanguageCodesFromTable(db_connection, "vufind_translations"));
    for (auto &language_code : GetLanguageCodesFromTable(db_connection, "keyword_translations")) {
        if (std::find(language_codes.begin(), language_codes.end(), language_code) == language_codes.end())
            language_codes.emplace_back(language_code);
    }
    return language_codes;
}


void ShowErrorPage(const std::string &title, const std::string &error_message, const std::string &description = "") {
    std::cout << "Content-Type: text/html; charset=utf-8\r\n\r\n";
    std::cout << "<!DOCTYPE html><html><head><title>" + title + "</title></head>"
              << "<body>"
              << "  <h1>" + error_message + "</h1>"
              << "  <h3>" + description + "</h3>"
              << "</body>"
              << "</html>";
}



std::string GetCGIParameterOrDefault(const std::multimap<std::string, std::string> &cgi_args,
                                     const std::string &parameter_name,
                                     const std::string &default_value) {
    const auto key_and_value(cgi_args.find(parameter_name));
    if (key_and_value == cgi_args.cend())
        return default_value;

    return key_and_value->second;
}


const std::string GetTranslatorOrEmptyString() {
    return (std::getenv("REMOTE_USER") != nullptr) ? std::getenv("REMOTE_USER") : "";
}


const std::string AssembleTermIdentifiers(const std::string &category, const std::string &index, 
                                          const std::string &language_code, const std::string &gnd_code = "", const std::string &translation = "") {

       return std::string(" category=\"" +  UrlUtil::UrlEncode(category) + "\" index=\"" + UrlUtil::UrlEncode(index) + "\" language_code=\"" +
                          UrlUtil::UrlEncode(language_code) + "\" gnd_code=\"" + gnd_code + "\" translation=\"" + translation + "\" ");
}


std::string CreateEditableRowEntry(const std::string &token, const std::string &label, const std::string language_code,
                           const std::string &category, const std::string db_translator, 
                           const std::string &status = "UNKNOWN", const std::string &gnd_code = "") {
    (void)status;
    std::string term_identifiers(AssembleTermIdentifiers(category, token, language_code, gnd_code, label));
    std::string background_color((GetTranslatorOrEmptyString() == db_translator) ? "RoyalBlue" : "LightBlue");
    return  "<td contenteditable=\"true\" class=\"editable_translation\"" + term_identifiers + "style=\"background-color:" + background_color + "\">" 
                + HtmlUtil::HtmlEscape(label) +"</td>";
}


void GetDisplayLanguages(std::vector<std::string> *const display_languages, const std::vector<std::string> &translation_languages,
                         const std::vector<std::string> &additional_view_languages, enum Category category = KEYWORDS) {

    display_languages->clear();

    if (category == KEYWORDS) {
        // Insert German as Display language in any case
        if (std::find(translation_languages.begin(), translation_languages.end(), "ger") == translation_languages.end())
            display_languages->emplace_back("ger");
        // Insert German Synonyms in any case
        display_languages->emplace_back(SYNONYM_COLUMN_DESCRIPTOR);
    }
    else if (category == VUFIND)
        display_languages->emplace_back(TOKEN_COLUMN_DESCRIPTOR);
    display_languages->insert(display_languages->end(), translation_languages.begin(), translation_languages.end());
    display_languages->insert(display_languages->end(), additional_view_languages.begin(), additional_view_languages.end());

    // For Keywords show also MACS
    if (category == KEYWORDS)
        display_languages->emplace_back(MACS_COLUMN_DESCRIPTOR);
}


bool IsTranslatorLanguage(const std::vector<std::string> &translator_languages, const std::string &lang) {
    return std::find(translator_languages.cbegin(), translator_languages.cend(), lang) != translator_languages.cend();
}


std::string CreateNonEditableRowEntry(const std::string &value) {
   return  "<td style=\"background-color:lightgrey\">" +  HtmlUtil::HtmlEscape(value) + "</td>";
}


std::string CreateNonEditableSynonymEntry(const std::vector<std::string> &values, const std::string &separator) {
   std::vector<std::string> html_escaped_values(values.size());
   std::for_each(values.cbegin(), values.cend(), boost::bind(&HtmlUtil::HtmlEscape, _1));
   return "<td style=\"background-color:lightgrey; font-size:small\">" +  StringUtil::Join(values, separator) + "</td>";
}


std::string CreateNonEditableHintEntry(const std::string &value, const std::string gnd_code) {
  return "<td style=\"background-color:lightgrey\"><a href = \"/Keywordchainsearch/Results?lookfor=" + HtmlUtil::HtmlEscape(value) +
                                                   "\" target=\"_blank\">" + HtmlUtil::HtmlEscape(value) + "</a>"
                                                   "<a href=\"http://d-nb.info/gnd/" + HtmlUtil::HtmlEscape(gnd_code) + "\""
                                                   " style=\"float:right\" target=\"_blank\">GND</a></td>";
}


void GetSynonymsForGNDCode(DbConnection &db_connection, const std::string &gnd_code, std::vector<std::string> *const synonyms) {
    synonyms->clear();
    const std::string synonym_query("SELECT translation FROM keyword_translations WHERE gnd_code=\'" + gnd_code + "\' AND status=\'reliable_synonym\'");
    DbResultSet result_set(ExecSqlOrDie(synonym_query, db_connection));
    if (result_set.empty())
        return;

    while (auto db_row = result_set.getNextRow())
        synonyms->emplace_back(db_row["translation"]);
}


void GetMACSTranslationsForGNDCode(DbConnection &db_connection, const std::string &gnd_code, std::vector<std::string> *const translations) {
    translations->clear();
    const std::string macs_query("SELECT translation FROM keyword_translations WHERE gnd_code=\'" + gnd_code + "\' AND status=\'macs\'");
    DbResultSet result_set(ExecSqlOrDie(macs_query, db_connection));
    if (result_set.empty())
        return;

    while (auto db_row = result_set.getNextRow())
        translations->emplace_back(db_row["translation"]);
}

int GetColumnIndexForColumnHeading(const std::vector<std::string> &column_headings, const std::vector<std::string> &row_values, const std::string &heading) {
    auto heading_pos(std::find(column_headings.cbegin(), column_headings.cend(), heading));
    if (heading_pos == column_headings.end())
        return NO_INDEX;

    auto index(heading_pos - column_headings.cbegin());
    try {
        row_values.at(index);
    } catch (std::out_of_range& x) {
        return NO_INDEX;
    }
    return index;
}

void GetVuFindTranslationsAsHTMLRowsFromDatabase(DbConnection &db_connection, const std::string &lookfor,
                                                 const std::string &offset, std::vector<std::string> *const rows,
                                                 std::string *const headline, const std::vector<std::string> translator_languages,
                                                 const std::vector<std::string> &additional_view_languages) {

    rows->clear();

    const std::string searchpattern(lookfor.size() <= LOOKFOR_PREFIX_LIMIT ? "LIKE \'" + lookfor + "%\'" : "LIKE \'%" + lookfor+ "%\'");
    const std::string token_where_clause(
            lookfor.empty() ? "" : "WHERE token " + searchpattern);
    const std::string token_query(
            "SELECT token FROM vufind_translations " + token_where_clause + " ORDER BY token LIMIT " + offset + ", " +
            std::to_string(ENTRIES_PER_PAGE));
    const std::string query("SELECT token, translation, language_code, translator FROM vufind_translations "
                            "WHERE token IN (SELECT * FROM (" + token_query + ") as t) ORDER BY token, language_code");

    DbResultSet result_set(ExecSqlOrDie(query, db_connection));

    std::vector<std::string> language_codes(GetLanguageCodes(db_connection));
    std::vector<std::string> display_languages;
    GetDisplayLanguages(&display_languages, translator_languages, additional_view_languages, VUFIND);
    *headline = "<th>" + StringUtil::Join(display_languages, "</th><th>") + "</th>";
    if (result_set.empty())
        return;

    std::vector<std::string> row_values(display_languages.size());
    std::string current_token;
    while (auto db_row = result_set.getNextRow()) {

       std::string token(db_row["token"]);
       std::string translation(db_row["translation"]);
       std::string language_code(db_row["language_code"]);
       std::string translator(db_row["translator"]);
       if (current_token != token){
           if (not current_token.empty())
              rows->emplace_back(StringUtil::Join(row_values, ""));

           current_token = token;
           row_values.clear();
           row_values.resize(display_languages.size(), "<td style=\"background-color:lightgrey\"></td>");
           int token_index(GetColumnIndexForColumnHeading(display_languages, row_values, TOKEN_COLUMN_DESCRIPTOR));
           if (token_index == NO_INDEX)
               continue;
           row_values[token_index] = CreateNonEditableRowEntry(token);
           for (auto translator_language : translator_languages) {
               int index(GetColumnIndexForColumnHeading(display_languages, row_values, translator_language));
               if (index != NO_INDEX) {
                   row_values[index] = CreateEditableRowEntry(current_token, "", language_code, "vufind_translations", translator);
               }
           }
       }

       int index(GetColumnIndexForColumnHeading(display_languages, row_values, language_code));
       if (index == NO_INDEX)
           continue;
       if (IsTranslatorLanguage(translator_languages, language_code))
          row_values[index] = CreateEditableRowEntry(current_token, translation, language_code, "vufind_translations", translator, "", "");
       else
          row_values[index] = CreateNonEditableRowEntry(translation);
   }
   rows->emplace_back(StringUtil::Join(row_values, ""));
}


void GetKeyWordTranslationsAsHTMLRowsFromDatabase(DbConnection &db_connection, const std::string &lookfor,
                                                  const std::string &offset, std::vector<std::string> *const rows,
                                                  std::string *const headline, 
                                                  const std::vector<std::string> &translator_languages, 
                                                  const std::vector<std::string> &additional_view_languages) {
    rows->clear();

    // For short strings make a prefix search, otherwise search substring
    const std::string searchpattern(lookfor.size() <= LOOKFOR_PREFIX_LIMIT ? "LIKE \'" + lookfor + "%\'" : "LIKE \'%" + lookfor+ "%\'");

    const std::string ppn_where_clause(lookfor.empty() ? "" : "AND k.translation " + searchpattern);

    const std::string query("SELECT l.ppn, l.translation, l.language_code, l.gnd_code, l.status, l.translator FROM keyword_translations AS k"
                            " INNER JOIN keyword_translations AS l ON k.language_code=\'ger\' AND k.status=\'reliable\'"
                            " AND k.ppn=l.ppn AND l.status!=\'reliable_synonym\' AND l.status != \'unreliable_synonym\'" +
                            ppn_where_clause +
                            " ORDER BY k.translation" +
                            " LIMIT " + offset + ", " + std::to_string(ENTRIES_PER_PAGE));

    DbResultSet result_set(ExecSqlOrDie(query, db_connection));

    std::vector<std::string> language_codes(GetLanguageCodes(db_connection));

    std::vector<std::string> display_languages;
    GetDisplayLanguages(&display_languages, translator_languages, additional_view_languages);
    *headline = "<th>" + StringUtil::Join(display_languages, "</th><th>") + "</th>";
    if (result_set.empty())
        return;

    std::vector<std::string> row_values(display_languages.size());
    std::string current_ppn;
    while (auto db_row = result_set.getNextRow()) {

       // Add new entries as long as there is a single PPN
       std::string ppn(db_row["ppn"]);
       std::string translation(db_row["translation"]);
       std::string language_code(db_row["language_code"]);
       std::string status(db_row["status"]);
       std::string translator(db_row["translator"]);
       std::string gnd_code(db_row["gnd_code"]);
       if (current_ppn != ppn){
           if (not current_ppn.empty())
              rows->emplace_back(StringUtil::Join(row_values, ""));

           current_ppn = ppn;
           row_values.clear();
           row_values.resize(display_languages.size(), "<td style=\"background-color:lightgrey\"></td>");
           for (auto translator_language : translator_languages) {
               int index(GetColumnIndexForColumnHeading(display_languages, row_values, translator_language));
               if (index != NO_INDEX) {
                   row_values[index] = CreateEditableRowEntry(current_ppn, "", language_code, "keyword_translations", "", status, gnd_code);
               }
           }
       }

       int index(GetColumnIndexForColumnHeading(display_languages, row_values, language_code));
       if (index == NO_INDEX)
           continue;
       if (IsTranslatorLanguage(translator_languages, language_code)) 
          row_values[index] = CreateEditableRowEntry(current_ppn, translation, language_code, "keyword_translations", translator, status, gnd_code);
       else
          row_values[index] = (language_code == "ger") ? CreateNonEditableHintEntry(translation, gnd_code) :
                                     CreateNonEditableRowEntry(translation);

       // Insert Synonyms
       std::vector<std::string> synonyms;
       GetSynonymsForGNDCode(db_connection, gnd_code, &synonyms);
       int synonym_index(GetColumnIndexForColumnHeading(display_languages, row_values, SYNONYM_COLUMN_DESCRIPTOR));
       if (synonym_index == NO_INDEX)
           continue;
       row_values[synonym_index] = CreateNonEditableSynonymEntry(synonyms, "<br/>");

       // Insert MACS Translations display table
       std::vector<std::string> macs_translations;
       GetMACSTranslationsForGNDCode(db_connection, gnd_code, &macs_translations);
       int macs_index(GetColumnIndexForColumnHeading(display_languages, row_values, MACS_COLUMN_DESCRIPTOR));
       if (macs_index == NO_INDEX)
           continue;
       row_values[macs_index] = CreateNonEditableSynonymEntry(macs_translations, "<br/>");
   }
   rows->emplace_back(StringUtil::Join(row_values, ""));
}


void GenerateDirectJumpTable(std::vector<std::string> *const jump_table, enum Category category = KEYWORDS) {
{'fraction_non_alphanumeric': 0.1299474811489599, 'fraction_numerical': 0.0011736056098348151, 'mean_word_length': 3.4800210304942167, 'pattern_counts': {'":': 0, '<': 190, '<?xml version=': 0, '>': 195, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 52, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5170386', 'n_tokens_mistral': 10077, 'n_tokens_neox': 9812, 'n_words': 1965}