// MySqlTable_t.cxx

#include "dataset_sql/SqlTable.h"
#include "dataset_sql/SqlResult.h"
#include "dataset_sql/SelectQuery.h"
#include "dataset_sql/InsertQuery.h"
#include "dataset_sql/DeleteQuery.h"
#include "dataset_mysqlpp/MySqlTable.h"
#include <string>
#include <map>
#include <iostream>

using std::string;
using std::map;
using std::cout;
using std::endl;
using dset::SqlRow;
using dset::SqlResult;
using dset::SelectQuery;
using dset::InsertQuery;
using dset::DeleteQuery;
using dset::SqlTable;
using dset::MySqlTable;

namespace {

// Display messsage.
void msg(string txt) {
  cout << "--- " << txt << " ---" << endl;
}

}

int MySqlTable_t() {
  msg("Begin testing MySqlTable");

  MySqlTable table("dbds","db1.usatlas.bnl.gov","selection","dbds","wdi");

  msg("Testing is_valid");
  assert( table.is_valid() );

  msg("Testing get_schema");
  SqlResult res = table.get_schema();
  int ecode = table.error();
  if( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(ecode) << endl;
    assert(false);
  }
  cout << res;

  msg("Testing get_row_count");
  SelectQuery query;
  query.set("","numevents > 2000");
  cout << "Row Count = " << table.get_row_count(query) << endl;
  ecode = table.error();
  if( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(ecode) << endl;
  }

  msg("Testing get_col_count");
  cout << "Column Count = " << table.get_col_count(query) << endl;
  ecode = table.error();
  if( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(ecode) << endl;
  }

  SelectQuery base("","");
  msg("Testing execute_select_query");
  SelectQuery selectquery;
  selectquery.reset();
  selectquery.set("aminame, numevents, id", ""); 
  selectquery.andconstraint("limit 2"); 
  res =  table.execute_select_query(selectquery);
  ecode = table.error();
  if ( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(table.error()) << endl;
  }
  else cout << res;
  
  int before_insert = (table.get_row_count(base));

  msg("Testing execute_insert_query");
  SqlRow row1;
  row1["id"] = "2005";
  row1["name"] = "test_atlasreco_20";
  row1["aminame"] = "test_atlasreco_20";
  row1["vidh"] = "10005";
  row1["vidl"] = "12345";
  InsertQuery insertquery(row1);
  if(table.execute_insert_query(insertquery) != 0) {
    cout << "Query returned error:" << table.error() << endl;
    cout << table.error_string(table.error()) << endl;
  }
  assert(table.get_row_count(base) == (before_insert+1));

  msg("Testing execute_update_query");
  SqlRow updaterow;
  updaterow["aminame"] = "test_atlasreco_30";
  updaterow["id"] = "2005";
  updaterow["vidh"] = "10005";
  updaterow["vidl"] = "12345";
  InsertQuery ins(updaterow);
  SelectQuery sel("","vidh = 10005 and vidl = 12345");
  if(table.execute_update_query(ins,sel) != 0) {
    cout << "Query returned error:" << table.error() << endl;
    cout << table.error_string(table.error()) << endl;
  }  
  res =  table.execute_select_query(sel);
  SqlRow row = res.fetch_row(0);
  assert(row["aminame"] == "test_atlasreco_30");

  msg("Testing execute_delete_query");
  DeleteQuery deletequery;
  deletequery.set("aminame = 'test_atlasreco_30'");
  deletequery.set("id = 2005"); 
  if(table.execute_delete_query(deletequery) != 0) {
    cout << "Query returned error:" << table.error() << endl;
    cout << table.error_string(table.error()) << endl;
  }
  assert(table.get_row_count(base) == before_insert);

  msg("End testing MySqlTable");
  return 0;
}

#ifdef CTEST_MAIN
int main() {
  return MySqlTable_t();
}
#endif