added searching en->jp

[aoi.git] / src / aoi.cxx

/*
    Copyright 2013 Karel Matas

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU 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 General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
#include <cmath>
#include <set>
#include <FL/fl_ask.H>
#include "aoi.hxx"
#include "utils.hxx"

namespace aoi {

using std::set;
using aoi_ui::TextStyle;
using utils::to_string;

const char *MANAGEDB_ITEM_KANJIDIC        = "Kanjidic";
const char *MANAGEDB_ITEM_JMDICT                  = "JMdict";
const char *MANAGEDB_ITEM_COMPONENTS  = "Components";
const char *TEMP_JMDICT               = "gztemp.jmdict";
const char *TEMP_KANJIDIC             = "gztemp.kanjidic";
const char *TEMP_COMPONENTS           = "gztemp.components";
const char *LOG_FILE                  = "aoi.log";
const char SENSE_SEARCH_CHAR          = ':';

App* App::instance_ = nullptr;

App::App ()
{
  remove(LOG_FILE);
  logger_.filename(LOG_FILE);
  logger_.loglevel(Logger::MSG_DEBUG);

  cfg_  = new aoi_config::Config();
  ui_   = new aoi_ui::GUI(this);
  rmn_  = new Romanization();

  ui_->progress(0,"Opening database...");

  try {
    db_ = new SQLite3::SQLite3( cfg_->get<string>("db/file_main").c_str() );
  }
  catch (SQLite3::CantOpenDatabase &e){
    log_e( "Aoi: Can't open database '"  + string(e.what()) + "'.");
  }

  ui_->progress( 30, "Checking tables..." );
  check_tables();
  ui_->progress( 60, "Checking indexes..." );
  check_indexes();

  load_config();
  init_dicview();

  #ifdef DEBUG
    ui_->progress(90, "DEBUG query..." );
//    parse_dic_input("ana*");
//    cb_kanji_search();
//    ui_->cb_toggle_group(nullptr);
  #endif

  ui_->progress( 98, "Initializing fonts..." );
  ui_->fontname_kanji( cfg_->get("font/kanji") );
  ui_->help_file( cfg_->get("sources/help_index") );

  ui_->progress_hide();


  auto q = query("select val from aoi where key='jmdict_version'");
  string jmdict_version = (q.empty()) ? "NONE":q[0];
  q = query("select val from aoi where key='kanjidic_version'");
  string kanjidic_version = q.empty() ? "NONE":q[0];
  if ( jmdict_version == "NONE" || kanjidic_version == "NONE" )
    cb_manage_db();


  #ifdef DEBUG
    logger_.loglevel(Logger::MSG_DEBUG);
  #endif

  instance_ = this;
}


App::~App () 
{
  delete db_;
  delete rmn_;
  delete ui_;
  delete cfg_;
}


void App::init_dicview ()
{
  // initialize textstyles
  TextStyle style_default(FL_HELVETICA,1.2);
  TextStyle style_reading(aoi_ui::FONT_KANJI,1.5);
  TextStyle style_reading_freq = style_reading;
  style_reading_freq.color = get_config<int>("color/frequent");
  TextStyle style_kanji(aoi_ui::FONT_KANJI,1.7);
  TextStyle style_kanji_freq = style_kanji;
  style_kanji_freq.color = get_config<int>("color/frequent"); 
  TextStyle style_inf(FL_HELVETICA_ITALIC,0.8,get_config<int>("color/pos"));
  style_inf.offset_y = 3;
  TextStyle style_pos = style_inf;
  TextStyle style_misc = style_pos;
  style_misc.color = get_config<int>("color/misc");
  TextStyle style_field = style_pos;
  style_field.color = get_config<int>("color/field");
  TextStyle style_dial = style_pos;

  // register TextStyles
  ui_->register_tag_dicview( "default",       style_default );
  ui_->register_tag_dicview( "reading",       style_reading );
  ui_->register_tag_dicview( "reading_freq",  style_reading_freq );
  ui_->register_tag_dicview( "kanji",         style_kanji );
  ui_->register_tag_dicview( "kanji_freq",    style_kanji_freq );
  ui_->register_tag_dicview( "kinf",          style_inf ); 
  ui_->register_tag_dicview( "rinf",          style_inf ); 
  ui_->register_tag_dicview( "pos",           style_pos ); 
  ui_->register_tag_dicview( "misc",          style_misc ); 
  ui_->register_tag_dicview( "field",         style_field ); 
  ui_->register_tag_dicview( "dial",          style_dial ); 
}


vector<string> App::query ( const char *q, bool log_query, bool replace_separator )
{
  if ( !db_ ){
    log_e("App::query(): Database does not exist.");
    return {};
  }
  vector<string> result;
  try {
    if ( log_query )
      log_d(string(q));
    result = db_->query(q);
  }
  catch (SQLite3::DatabaseError &e){
    log_e("App: DatabaseError:: " + string(e.what()) + string("\nQuery: ") 
      + string(e.query()) );
  }
  string msg = std::to_string(db_->result_rows()) + " results";
  log( "App::query(): " + msg);
  if ( replace_separator )
    for ( string &s: result )
      utils::replace_all(s, parsers::SEPARATOR_SQL, ", ");
  return result;
}


void App::cb_set_components ()
{
  if ( curr_components_.empty() )
    return;

  using aoi_ui::ComponentView;

  // prepare cells
  vector<string> included = ui_->components_include();
  vector<string> excluded = ui_->components_exclude();

  vector<ComponentView::Cell> v;
  for ( string &s: included )
    v.push_back( ComponentView::Cell(s,ComponentView::CELL_SELECTED_1) );
  for ( string &s: excluded )
    v.push_back( ComponentView::Cell(s,ComponentView::CELL_SELECTED_2) );

  // sort by occurences
  if ( !ui_->sort_components_by_strokes() ){
    for ( auto mi = curr_components_.rbegin(); mi!=curr_components_.rend(); ++mi ){
      if ( !utils::is_in( included, mi->second ) 
            && !utils::is_in( excluded, mi->second ) )
        v.push_back( ComponentView::Cell(mi->second) );
    }
  }
  // sort by strokes
  else {
    vector<string> comps_by_strokes;
    for ( auto mi: curr_components_ ){
      if ( mi.first < get_config<int>("knj/min_compo_count") )
        continue;
      comps_by_strokes.push_back(mi.second);
    }
 
    struct SortByStrokes {
      map<string,int> comps;
      SortByStrokes( const map<string,int> &c ): comps(c){};
      bool operator() (const string &c1, const string &c2 ){ 
        return ( this->comps[c1] < this->comps[c2] );
      }
    } sbc(components_);
 
    std::sort( comps_by_strokes.begin(), comps_by_strokes.end(), sbc );

    int prev_strokes=0;
    for ( string &s: comps_by_strokes ){
      int curr_strokes = components_[s];
      if ( prev_strokes != curr_strokes )
        v.push_back( ComponentView::Cell( std::to_string(curr_strokes), 
                                          ComponentView::CELL_LABEL) );
      v.push_back( ComponentView::Cell(s) );
      prev_strokes = curr_strokes;
    }
  }

  ui_->set_components( v );
}


void App::cb_kanji_search ()
{

  if ( components_.empty() ){
    log("Loading components...");
    vector<string> res = query("select component, strokes from components");
    for ( size_t i=0; i<res.size(); i=i+2 ){
      components_[res[i]] = std::stoi(res[i+1]);
    }
  }

  // strokes
  std::pair<int,int> strokes = utils::parse_range( utils::strip( ui_->strokes() ) );
  // jlpt
  std::pair<int,int> jlpt = utils::parse_range( utils::strip( ui_->jlpt() ) );
  // grade
  std::pair<int,int> grade = utils::parse_range( utils::strip( ui_->grade() ) );

  

  // skip
  vector<string> skip = utils::split_string( ui_->skip() ); 
  std::stringstream sskip;
  if ( skip.size() > 0 ){
    string skip1 = utils::strip(skip[0].c_str());
    sskip << " S.skip1=" << skip1; 
  }
  if ( skip.size() > 1 ){
    std::pair<int,int> skip2 = utils::parse_range( utils::strip(skip[1].c_str()) );
    sskip << " and S.skip2>=" << skip2.first << " and S.skip2<=" << skip2.second;
  }
  if ( skip.size() > 2 ){
    std::pair<int,int> skip3 = utils::parse_range( utils::strip(skip[2].c_str()) );
    sskip << " and S.skip3>=" << skip3.first << " and S.skip3<=" << skip3.second;
  }
  if ( !sskip.str().empty() )
    sskip << " and ";

  // ordering  
  string order_by;
  switch ( ui_->sort_mode() ){
    case SORT_FREQ_ASC:
      order_by = "(case freq when 0 then 9999 else freq end) asc";
      break;
    case SORT_FREQ_DESC:
      order_by = "(case freq when 0 then 9999 else freq end) desc";
      break;
    case SORT_STROKES_ASC:
      order_by = "strokes asc";
      break;
    case SORT_STROKES_DESC:
      order_by = "strokes desc";
      break;
  }

  vector<string> components_include = ui_->components_include();
  vector<string> components_exclude = ui_->components_exclude();
  std::stringstream comps;
  for ( auto c: components_include ) 
    comps << " and components like '%" << c << "%'";
  for ( auto c: components_exclude ) 
    comps << " and components not like '%" << c << "%'";
  printf("%s\n",comps.str().c_str());

  string jis208 = "";
  if ( get_config<bool>("knj/jis208_only") )
    jis208 = " flags glob '*jis208*' and ";

  // build query
  std::stringstream ss;
  ss  <<  "select distinct "
      <<    "K.kanji,freq,components,flags "
      <<  " from k_kanji as K, k_skip as S where K.kanji=S.kanji and "
      <<    jis208
      <<    sskip.str()
      <<    " strokes>=" << strokes.first << " and strokes<=" << strokes.second
      <<    " and jlpt>=" << jlpt.first << " and jlpt<=" << jlpt.second
      <<    " and grade>=" << grade.first << " and grade<=" << grade.second
      <<    comps.str()  
      <<  " order by " << order_by;
  

  // perform query
  vector<string> q = query( ss.str().c_str(), true, false );
  vector<aoi_ui::KanjiView::Cell> data;
  vector<string> components;
  set<string> flags;
  for ( size_t i=0; i<q.size(); i+=4 ){
    string kanji = q[i];
    int freq = std::stoi(q[i+1]);
    data.push_back( 
      aoi_ui::KanjiView::Cell( 
        kanji, 
        (freq>0)? get_config<int>("color/frequent"):-1 
      ) 
    );
    components.push_back(q[i+2]);
    for ( string &s: utils::split_string( q[i+3], parsers::SEPARATOR_SQL) )
      flags.insert(s);
  }

  log_d("Groups: " + utils::to_string(flags));
  
  ui_->set_kanjiview( data );
  char b[32];
  sprintf( b, "%d results", db_->result_rows() );
  ui_->set_kanji_results( b );

  utils::Histogram<string> histogram;
  for ( string &s: components )
    histogram.add( utils::str_to_chars(s.c_str()) );
  curr_components_ = histogram.sorted();

  cb_set_components();
}


void App::alert ( const string &msg, const string &desc )
{
  std::stringstream ss;
  ss << msg;
  if ( !desc.empty() )
    ss << "\n\nDetails:\n" << desc;
  log_e(ss);
  ui_->alert(ss.str());
}


int App::run ( int argc, char **argv )
{
  return ui_->run(argc,argv);
}


void App::parse_kanjidic ( const char *fname, bool delete_source )
{
  if ( !utils::file_exists( fname ) ){
    log_e("App::parse_kanjidic(): File does not exist: " + string(fname));
    return;
  }
  ui_->progress(0, "Loading kanjidic...");
  log("Loading kanjidic...");

  const char *SEP = parsers::SEPARATOR_SQL;

  int n_kanji = 0;
  try {
    ui_->progress(1, "Creating parser...");
    std::stringstream msg;
    msg << "Decompressing file: " << fname << " -> " << TEMP_KANJIDIC;
    log_d(msg);
/*
    #ifdef WINDOWS
      log_d("Waiting 3 s before decompressing...");
      usleep(3000);
      log_d("Continue...");
    #endif
*/
    utils::gzip_decompress_file( fname, TEMP_KANJIDIC);
    parsers::KanjidicParser p(TEMP_KANJIDIC);
    auto kanji = p.get_entry();

    query(  "DROP TABLE IF EXISTS k_kanji;"
            "DROP TABLE IF EXISTS components;"
            "DROP TABLE IF EXISTS k_skip;"
            "DELETE FROM aoi WHERE key='kanjidic_version';" );

    check_tables();

    std::stringstream ss;
    ss << "BEGIN TRANSACTION;\n";
    ss << "INSERT INTO aoi (key,val) VALUES ('kanjidic_version','" 
      << p.get_version() << "');\n";
    while ( kanji.kanji() != "" ){
      n_kanji++;
      if ( n_kanji % 100 == 0)
        // percent = 100 * n_kanji/13108
        ui_->progress( n_kanji/14, std::to_string(n_kanji)+string(" kanji loaded"));
      ss << "INSERT INTO k_kanji "
        << "(kanji,ucs,onyomi,kunyomi,meaning,nanori,flags,jlpt,grade,freq,strokes,"
        << "rad_classic,rad_nelson,components)"
        << " VALUES('"
        << kanji.kanji() << "','"
        << kanji.ucs() << "','"
        << to_string(kanji.onyomi(),SEP) << "','"
        << to_string(kanji.kunyomi(),SEP) << "','"
        << SQLite3::escape(to_string(kanji.meaning(),SEP)) << "','"
        << to_string(kanji.nanori(),SEP) << "','"
        << to_string(kanji.flags(),SEP) << "',"
        << kanji.jlpt() << ","
        << kanji.grade() << ","
        << kanji.freq() << ","
        << kanji.strokes() << ","
        << kanji.rad_classic() << ","
        << kanji.rad_nelson() << ","
        << "''" 
        << ");\n";
      for ( SKIP &s: kanji.skip() ) {
        ss << "INSERT INTO k_skip (kanji,skip1,skip2,skip3,misclass) VALUES('"
          << kanji.kanji() << "'," << s.s1 << "," << s.s2 << "," << s.s3 
          << ",'" << s.misclass << "');\n";
      }
      kanji = p.get_entry();
    }
    ss << "END TRANSACTION;\n";
    log("Writing to db ...");

//    #ifdef DEBUG
//      log_d("Writing file 'script.kanjidic.sql'...");
//      std::ofstream f ("script.kanjidic.sql");
//      f << ss.str();
//      f.close();
//    #endif
    ui_->progress(95, "Writing to database...");
    query(ss.str().c_str(),false);
  }
  catch ( utils::ParsingError &e ){ 
    ui_->progress_hide();
    std::string msg = "App::parse_jmdict(): ParsingError: ";
    msg += e.what();
    alert(msg);
    return;
  } 
  ui_->progress(98, "Checking indexes...");
  check_indexes();
  ui_->progress_hide();
  remove( TEMP_KANJIDIC );
  if ( delete_source )
    remove( fname );
}


void App::parse_jmdict ( const char *fname, bool delete_source )
{
  if ( !utils::file_exists( fname ) ){
    log_e("App::parse_jmdict(): File does not exist: " + string(fname));
    return;
  }

  ui_->progress( 0, "Loading JMdict..." );
  log("Loading JMdict...");
  std::stringstream ss;
  ss << "BEGIN TRANSACTION;\n";

  // tables
  for ( auto &mi: aoi_config::db_tables ){
    ss << "DROP TABLE IF EXISTS " << mi.first << ";\n"
    << "CREATE TABLE " << mi.first << " ( "; 
    for ( size_t i=0; i<mi.second.size(); i++ )
      ss << mi.second[i].name << " " << mi.second[i].type 
        << ((i==mi.second.size()-1) ? "":",");
    ss << ");\n";
  }

  // create parser
  int n_entries = 0;
  try {
    ui_->progress(1, "Creating parser...");

    utils::gzip_decompress_file( fname, TEMP_JMDICT);
    parsers::JmdictParser jmp(TEMP_JMDICT);
    const char *SEP = parsers::SEPARATOR_SQL;

    // version
    ss << "INSERT INTO aoi (key,val) VALUES ('jmdict_version', '" 
      << jmp.get_version() << "');\n";
    log("jmdict_version: "+jmp.get_version());
    
    // get entities
    for ( std::pair<string,string> elt: jmp.get_entities() ){
      ss << "INSERT INTO entities (abbr,desc) VALUES ('" << SQLite3::escape(elt.first) 
        << "','" << SQLite3::escape(elt.second) << "');\n";
    } 
    
    int n_entries = 1;
    parsers::JmdictEntry entry = jmp.get_entry();
    while ( entry.did != -1 ) {
      if ( n_entries % 1000 == 0 ){
        // entries is about 180 000 -> percent = 100*n/180000 ~ n/2000
        ui_->progress( n_entries/2000, std::to_string(n_entries)+string(" entries loaded") );
        n_entries++;
      }


      // READING
      for ( parsers::ReadingElement &rele: entry.r_ele ){
        ss << "INSERT INTO d_reading (rid,did,reading,inf,nokanji,freq) VALUES ("
          << rele.rid << "," << entry.did << ",'" << rele.reading << "','" 
          << to_string(rele.inf,SEP) << "',"
          << rele.nokanji << "," << rele.freq << ");\n";
        // XXX: d_re_restr
      }
      // KANJI
      for ( parsers::KanjiElement &kele: entry.k_ele ){
        ss << "INSERT INTO d_kanji (kid,did,kanji,inf,freq) VALUES ("
          << kele.kid << "," << entry.did << ",'" << kele.kanji << "','" 
          << to_string(kele.inf,SEP) << "'," << kele.freq << ");\n";  
      }
      // SENSE
      for ( parsers::SenseElement &sele: entry.s_ele ){
        ss << "INSERT INTO d_sense (sid,did,gloss,xref,ant,inf,pos,field,misc,dial) VALUES ("
          << sele.sid << "," << entry.did << ",'"
          << SQLite3::escape(to_string(sele.gloss,SEP)) << "','"
          << to_string(sele.xref,SEP) << "','"
          << to_string(sele.ant,SEP) << "','"
          << SQLite3::escape(to_string(sele.s_inf,SEP)) << "','"
          << to_string(sele.pos,SEP) << "','"
          << to_string(sele.field,SEP) << "','"
          << to_string(sele.misc,SEP) << "','"
          << to_string(sele.dial,SEP) << "'"
        << ");\n";
        // d_stagk, d_stagr
      }
      // tbl: header
      n_entries++;
      entry = jmp.get_entry();
    }
  }   
  catch ( utils::ParsingError &e ){
    ui_->progress_hide();
    std::string msg = "App::parse_jmdict(): ParsingError: ";
    msg += e.what();
    alert(msg);
    return;
  }

  log(std::to_string(n_entries) + " entries processed.");

  ss << "END TRANSACTION;\n";
  // no need for vacuum here - check_index() will perform it eventually (see below)

//  #ifdef DEBUG
//    log_d("Writing file 'script.jmdict.sql'...");
//    std::ofstream f ("script.jmdict.sql");
//    f << ss.str();
//    f.close();
//  #endif

  ui_->progress( 95, "Creating database... ");
  log("Creating database...");
  try {
    // don't log query (ifdef DEBUG then SQL script is already created)
    query(ss.str().c_str(),false);  
  }
  catch ( SQLite3::DatabaseError &e ){
    string msg = "App::parse_jmdict(): DatabaseError: ";
    msg += e.what();
    alert(msg);
    return;
  }
  ui_->progress( 99, "Checking indexes... ");
  log("Creating database...");
  check_indexes();
  ui_->progress_hide();
  log("Deleting temporary file...");
  remove(TEMP_JMDICT);
  if ( delete_source )
    remove( fname );
}


void App::check_tables ()
{
  // no need for vacuum here, it will be done by check_indexes()
  log("Checking tables");
  vector<string> existing = query("SELECT name FROM sqlite_master WHERE type='table'");
  // CREATE TABLE name ( column1 TYPE, column2 TYPE, ... )
  for ( auto &table: aoi_config::db_tables ){
    if ( utils::is_in(existing, string(table.first)) )
      continue;
    log("Creating table " + string(table.first));
    vector<string> v;
    for ( auto &column: table.second ){
      std::stringstream sstr;
      sstr << column.name << " " << column.type;
      v.push_back(sstr.str()); 
    }
    std::stringstream ss;
    ss << "CREATE TABLE " << table.first << " (" << to_string(v,",") << ");\n";
    query(ss.str().c_str());
  }
  log("Check done.");
}


void App::check_indexes ()
{
  log("Checking indexes...");
  bool do_vacuum = false;
  vector<string> existing = query("SELECT name FROM sqlite_master WHERE type='index'");
  // CREATE INDEX idx_table_column ON table ( column ASC )
  for ( auto &mi: aoi_config::db_tables ){  // tables
    for ( auto &c: mi.second ){ // columns
      std::stringstream idx_name;
      idx_name << "idx_" << mi.first << "_" << c.name;
      if ( c.index && !utils::is_in( existing, idx_name.str() ) ){
        log(string("Creating index ") + idx_name.str());
        std::stringstream ss;
        ss << "CREATE INDEX " << idx_name.str() << " ON " << mi.first 
          << "(" << c.name << " " << c.sort << ")";
        query(ss.str().c_str());
        do_vacuum = true;
      }
    }
  }
  if ( do_vacuum ){
    query("VACUUM");
  }
  log("Check done.");
}


void App::cb_dic_input ()
{
  parse_dic_input( ui_->get_dic_input() );
  ui_->reset_filters();
}


void App::on_dic_selected ( int id )
{
  log_d("App::on_dic_selected()");
}

void App::cb_edit_word ( int id )
{
  std::stringstream ss;
  ss << "App::edit_word( " << id << " )";
  log_d(ss);
  ui_->edit_word();
}


void App::cb_popup_kanji ( const string &kanji )
{
  std::stringstream ss;
  ss << "App::on_kanji_clicked()" << kanji;
  Kanji k = db_get_kanji(kanji);
  ui_->popup_kanji( k );
  // XXX: this should be somewhere else (it is not logical here)
  ui_->highlight_components( k.components() );
  log(ss);
}


void App::set_listview ( const vector<string> &v )
{
  // TODO: nokanji
  if ( !listview_items_.empty() ) listview_items_.clear();
  vector<string> d;
  vector<int> cell_ids;
  size_t i = 0;
  while ( i < v.size() ) {
    int cell_id = std::stoi(v[i]);  // jmdict id
    cell_ids.push_back(cell_id);
    cell_ids.push_back(cell_id);
    cell_ids.push_back(cell_id);
    // pos
    set<string> pos;
    for ( string &elt: utils::split_string( v[i+1], ",") )
      if ( elt.size() > 0 )
        pos.insert(utils::strip(elt.c_str()));
    d.push_back( v[i+2] );            // reading
    d.push_back( v[i+3] );            // kanji
    d.push_back( v[i+4] );            // sense
//    printf("%s%s%s\n", v[i+2].c_str(), v[i+3].c_str(), v[i+4].c_str() );
    listview_items_.push_back( {cell_id, pos, v[i+2], v[i+3], v[i+4]} );
    i += 5;
  }
  char buff[32];
  sprintf( buff, "%d results", db_->result_rows() );
  ui_->set_dic_results( buff );
  ui_->set_listview(d,cell_ids);
}


void App::parse_dic_input ( const char *str )
{
  log_d(string("App::parse_dic_input: \"") +string(str) + string("\""));

  string stripped = utils::strip(str);

  if ( stripped.empty() )
    return;

  const char *s = stripped.c_str();

  string qq;
  if ( s[0] != SENSE_SEARCH_CHAR ){
    printf("Search sense.");
    DictionaryInputParser p;
    qq = p.parse(s);
    
    if ( p.warning() ){
      int res = fl_choice( 
        "Too broad search.\n"\
        "Your computer may become unresponsible for a long time.\n"\
        "Proceed?",
        "Proceed",
        "Cancel",
        0
        );
        if ( res == 1 ) // Cancel
          return;
    }
 
    if ( !strchr(s,'*') && !strchr(s,'?') && !strchr(s,'[') && !strchr(s,'{') 
        && !strchr(s,'(') )
      qq += "*";
  }

  std::stringstream q; 
  if ( s[0] == SENSE_SEARCH_CHAR ){
    printf("Search: %s\n", stripped.substr(1).c_str() );
    q << "select did,"
      << "group_concat(pos) as pos," 
      << q_reading("d_sense.did")
      << q_kanji("d_sense.did")
      << q_sense()
      << " from d_sense where gloss glob '*" << stripped.substr(1) << "*'"
      << " group by did";
  }
  else if ( rmn_->contains_kanji( qq.c_str() ) ) {
    q << "select d_kanji.did as did,"
      << "(select group_concat(pos) from d_sense where d_sense.did = d_kanji.did) as pos," 
      << q_kanji() 
      << q_reading("d_kanji.did")
      << q_sense("d_kanji.did")
      << "from d_kanji where "
        << "kanji glob '" << rmn_->romaji_to_hiragana(qq.c_str()) << "' "
        << " or kanji glob '" << rmn_->romaji_to_katakana(qq.c_str()) << "' "
      << " group by did order by d_kanji.freq desc, d_kanji.kanji asc";
  }
  else {
    q << "select d_reading.did as did,"
      << "(select group_concat(pos) from d_sense where d_sense.did = d_reading.did) as pos," 
      << q_reading()
      << q_kanji("d_reading.did") 
      << q_sense("d_reading.did")
      << "from d_reading where "
        << "reading glob '" << rmn_->romaji_to_hiragana(qq.c_str()) 
        << "' or reading glob '"<< rmn_->romaji_to_katakana(qq.c_str()) << "' "
      << " group by did order by d_reading.freq desc, d_reading.reading asc";
  }
  set_listview(query(q.str().c_str()));
}



Kanji App::db_get_kanji ( const string &kanji )
{
  log("App::db_get_kanji()");
  std::stringstream q;
  q << "select kanji,strokes,ucs, rad_classic, rad_nelson, "
          << "jlpt, grade, freq, onyomi, kunyomi, nanori, meaning,flags,components "
    << "from k_kanji where kanji='" << kanji << "';";

  vector<string> res = query(q.str().c_str());
  Kanji kk(res[0]);
  kk.strokes(std::stoi(res[1]));
  kk.ucs(res[2]);
  kk.rad_classic(std::stoi(res[3]));
  kk.rad_nelson( (res[4].empty()) ? -1:std::stoi(res[4]));
  kk.jlpt(std::stoi(res[5]));
  kk.grade(std::stoi(res[6]));
  kk.freq(std::stoi(res[7]));
  kk.onyomi( utils::split_string(res[8],parsers::SEPARATOR_SQL) );
  kk.kunyomi( utils::split_string(res[9],parsers::SEPARATOR_SQL) );
  kk.nanori( utils::split_string(res[10],parsers::SEPARATOR_SQL) );
  kk.meaning( utils::split_string(res[11],parsers::SEPARATOR_SQL) );
  kk.flags( utils::split_string(res[12],parsers::SEPARATOR_SQL) );
  kk.components( res[13] );

  string qq = "select skip1,skip2,skip3,misclass from k_skip where kanji='"+kanji+"';";
  vector<string> res2 = query(qq.c_str());
  if ( res2.size() % 4 != 0 ){
    std::stringstream ss;
    ss << "Wrong SKIP count. Kanji: " << kanji 
      << "Query result size: " << res2.size() 
      << " (should be 4,8 or 12).  SKIP not loaded.";
    log_e(ss);
    return kk;
  }
  for ( size_t i=0; i < res2.size(); i+=4 )
    kk.skip( res2[i], res2[i+1], res2[i+2], res2[i+3] );
  return kk;
}


void App::cb_file_jmdict ( bool download )
{
  string fname;
  const char *path = nullptr;

  if ( download ){
    fname = ui_->download_dialog( get_config("sources/url_jmdict") ); 
    log_d("Download finished...");
    path = fname.c_str();
  }
  else
    path = ui_->open_file_dialog();

  if ( !path )
    return;

  log("Selected file: " + string(path));
  parse_jmdict(path, download);
  auto label = ui()->dlg_manage_db()->item(MANAGEDB_ITEM_JMDICT);
        if ( label )
                label->version("Changed",FL_BLUE);
        else
                log_w("Can't find item " + string(MANAGEDB_ITEM_JMDICT) + " in ManageDBDialog.");
}


void App::cb_file_kanjidic ( bool download )
{
  string fname;
  const char *path = nullptr;

  if ( download ){
    fname = ui_->download_dialog( get_config("sources/url_kanjidic") ); 
    log_d("Download finished...");
    path = fname.c_str();
  }
  else
    path = ui_->open_file_dialog();

  if ( !path )
    return;

  log("Selected file: " + string(path));
  parse_kanjidic(path,download);
  auto label = ui_->dlg_manage_db()->item(MANAGEDB_ITEM_KANJIDIC);
        if ( label )
                label->version("Changed",FL_BLUE);
        else
                log_w("Can't find item "+ string(MANAGEDB_ITEM_KANJIDIC) +" in ManageDBDialog.");
}


void App::cb_file_components ( bool download )
{
  string fname;
  const char *path = nullptr;

  if ( download ){
    fname = ui_->download_dialog( get_config("sources/url_components") ); 
    log_d("Download finished...");
    path = fname.c_str();
  }
  else
    path = ui_->open_file_dialog();

  if ( !path )
    return;

  log("Selected file: " + string(path));
  utils::gzip_decompress_file( path, TEMP_COMPONENTS);
  try {
    db_->script( TEMP_COMPONENTS );
  }
  catch ( SQLite3::DatabaseError &e ) {
    alert(e.what(), e.query() );
  }
  catch ( std::exception &e ) {
    alert( e.what() );
  }

  auto label = ui_->dlg_manage_db()->item(MANAGEDB_ITEM_COMPONENTS);
        if ( label )
                label->version("Changed",FL_BLUE);
        else
                log_w("Can't find item " + string(MANAGEDB_ITEM_COMPONENTS) 
      + " in ManageDBDialog.");
  remove(TEMP_COMPONENTS);
  remove(path);
}


void App::cb_filter_listview ()
{
  vector<string> pos = ui_->listview_filters();
  log_d("App::cb_filter_listview(): pos: " + utils::to_string(pos));
  bool filter_expr  = ( utils::is_in( pos, string("expr") ) );
  bool filter_noun  = ( utils::is_in( pos, string("noun") ) );
  bool filter_verb  = ( utils::is_in( pos, string("verb") ) );
  bool filter_adj   = ( utils::is_in( pos, string("adj")  ) );
  vector<string> data;
  vector<int> ids;
  size_t n = 0;
  for ( auto &elt: listview_items_ ){
    auto start = elt.pos.begin();
    auto end = elt.pos.end();
    if ( filter_expr && std::find( start, end, "exp") == end )
      continue;
    if ( filter_noun && std::find( start, end, "n" ) == end )
      continue;
    if ( filter_adj && std::find_if( start, end, 
      [](const string &s){ return strncmp(s.c_str(),"adj",3)==0;} ) == end )
      continue;
    if ( filter_verb && std::find_if( start, end, 
      [](const string &s){ return strncmp(s.c_str(),"v",1)==0;} ) == end )
      continue;
    ids.push_back( elt.did );
    ids.push_back( elt.did );
    ids.push_back( elt.did );
    data.push_back ( elt.reading );
    data.push_back ( elt.kanji );
    data.push_back ( elt.sense );
    n++;
  } 
  ui_->set_listview( data, ids );
  std::stringstream ss;
  ss << n << " results";
  if (  listview_items_.size() != n )
    ss << " (" <<  listview_items_.size()-n << " hidden)"; 
  log(ss.str());
  ui_->set_dic_results( ss.str() );
}
    

void App::cb_dicview_rightclick ( int did )
{
  printf("%d\n",did);
  string q = "select group_concat(kanji,'') from d_kanji where did="+std::to_string(did);
  vector<string> res = query( q.c_str() );
  set<string> kanji;
  for ( string &c: utils::str_to_chars(res[0].c_str()) )
    if ( App::get()->rmn()->is_kanji(c.c_str()) )
      kanji.insert(c);
  ui_->menu_copy( vector<string>(kanji.begin(),kanji.end()) );
}


void App::set_config ( const string &key, const string &val )
{ 
  cfg_->set( key, val );
  if ( key == "font/base_size")
    ui_->font_base_size(std::stoi(val));
  else if ( key == "log/level" )
    logger_.loglevel(val);
  else if ( key == "color/foreground" || key == "color/background" || 
            key == "color/background2" || key == "color/selection")
    ui_->init_colors();
  Fl::check();
}


void App::cb_manage_db ()
{
  auto q = query("select val from aoi where key='jmdict_version'");
  string jmdict_version = (q.empty()) ? "NONE":q[0];
  q = query("select val from aoi where key='kanjidic_version'");
  string kanjidic_version = q.empty() ? "NONE":q[0];
  q = query("select val from aoi where key='components_version'");
  string components_version = q.empty() ? "NONE":q[0];
  auto *d = ui_->dlg_manage_db();
  Fl_Color col;
  if ( !d->item(MANAGEDB_ITEM_JMDICT) ){
    col = ( jmdict_version == "NONE" ) ? FL_RED:FL_BLUE;
    d->add_item( MANAGEDB_ITEM_JMDICT, jmdict_version, 
      "Japanese-English dictionary. Needed component.", 
      col, scb_local_file_jmdict, scb_download_file_jmdict, 
      (void*)this );
  }
  if ( !d->item(MANAGEDB_ITEM_KANJIDIC) ){
    col = ( kanjidic_version == "NONE" ) ? FL_RED:FL_BLUE;
    d->add_item( MANAGEDB_ITEM_KANJIDIC, kanjidic_version, 
      "Kanji dictionary. Needed component.", 
      col, scb_local_file_kanjidic, scb_download_file_kanjidic, 
      (void*)this  );
  }
  if ( !d->item(MANAGEDB_ITEM_COMPONENTS) ){
    col = ( components_version == "NONE" ) ? FL_RED:FL_BLUE;
    d->add_item( MANAGEDB_ITEM_COMPONENTS, components_version, 
      "Graphical components of kanji.", 
      col, scb_local_file_components, scb_download_file_components, 
      (void*)this );
  }
  d->show();
}


void App::load_config ()
{
  log("Loading config...");
  vector<string> res = query("select key,val from config;");
  for ( size_t i=0; i < res.size(); i+=2 ){
    set_config( res[i], res[i+1] );
  }
}


void App::save_config ( const std::map<string,aoi_config::Config::Value> &newmap)
{
  log("Saving config...");

  typedef std::pair<string,aoi_config::Config::Value> cfg_pair;

  // merge new and old config
  for ( const cfg_pair &p: newmap )
    set_config( p.first, p.second.val );

  // prepare SQL script
  std::stringstream ss;
  ss << "BEGIN TRANSACTION;\n";
  for ( const cfg_pair &p: get_config_map() )
    ss << "REPLACE INTO config (key,val) VALUES('" 
      << p.first << "','" << p.second.val << "');\n";
  ss << "END TRANSACTION;\n";

  query(ss.str().c_str());

  // apply new fonts and colors
  init_dicview();  

  // TODO:
  //  check new keys
  //  check keys in oldconfig not present in newmap
  //  set oldkeys
  //  set newkeys
  //  mnoziny...
}

} // namespace aoi