// SqlResultTable_t.cxx

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

using std::string;
using std::cout;
using std::endl;
using dset::SelectQuery;
using dset::InsertQuery;
using dset::DeleteQuery;
using dset::SqlTable;
using dset::SqlResultTable;
using dset::SqlResult;

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

void create_test_table() {

  string names[14] = { "id", "name", "vidh", "vidl", "aminame", 
    "amistate", "amiextractime", "amiplanpartitions", "amiplannumevents", 
    "content", "physicsprocess", "physworkgrp", "includedpartitions", 
    "numevents"};

  SqlResult::NameList cols(names, names+14);

  SqlResult res(cols);

  SqlResult::Row row1;
  row1["id"] = "40";
  row1["name"] = "higgs.002505.h130_zz_4e_filt4e.recon";
  row1["vidh"] = "0";
  row1["vidl"] = "0";
  row1["amiextractime"] = "2003";
  row1["amiplanpartitions"] = "69878";
  row1["amiplannumevents"] = "0";
  row1["content"] = "cbnt";
  row1["includedpartitions"] = "656";
  row1["numevents"] = "83339";
  res.insert_row(row1);
  assert( res.num_rows() == 1 );

  SqlResult::Row row2;
  row2["id"] = "254";
  row2["name"] = "higgs.002505.h130_zz_4e.dc1.lumi10.recon.010";
  row2["vidh"] = "10003";
  row2["vidl"] = "10764";
  row2["aminame"] = "higgs.002505.h130_zz_4e.dc1.lumi10.recon.010";
  row2["amistate"] = "validated";
  row2["amiextractime"] = "2004";
  row2["amiplanpartitions"] = "60";
  row2["amiplannumevents"] = "10000";
  row2["content"] = "cbnt";
  row2["physicsprocess"] = "h130_zz_4e";
  row2["physworkgrp"] = "Higgs,Top";
  row2["includedpartitions"] = "60";
  row2["numevents"] = "9570";
  assert( res.insert_row(row2) == 0 );
  assert( res.num_rows() == 2 );

  SqlResultTable table(res);
  table.write("sqltab.dat");
}

//**********************************************************************

int SqlResultTable_t() {

  msg("Begin testing SqlResultTable");

  // Constructing a Result set to construct a SqlResultTable.

  string names[14] = {
    "id",
    "name",
    "vidh",
    "vidl",
    "aminame",
    "amistate",
    "amiextractime",
    "amiplanpartitions",
    "amiplannumevents",
    "content",
    "physicsprocess",
    "physworkgrp",
    "includedpartitions",
    "numevents"
  };
  SqlResult::NameList cols(names, names+14);

  SqlResult res(cols);

  msg("Insert a row");
  SqlResult::Row row1;
  row1["id"] = "40";
  row1["name"] = "higgs.002505.h130_zz_4e_filt4e.recon";
  row1["vidh"] = "0";
  row1["vidl"] = "0";
  row1["aminame"] = "higgs.002505.h130_zz_4e.filt4e.recon";
  row1["amiextractime"] = "2003";
  row1["amiplanpartitions"] = "69878";
  row1["amiplannumevents"] = "0";
  row1["content"] = "cbnt";
  row1["includedpartitions"] = "656";
  row1["numevents"] = "83339";
  res.insert_row(row1);
  assert( res.num_rows() == 1 );

  msg("Insert another row");
  SqlResult::Row row2;
  row2["id"] = "254";
  row2["name"] = "higgs.002505.h130_zz_4e.dc1.lumi10.recon.010";
  row2["vidh"] = "10003";
  row2["vidl"] = "10764";
  row2["aminame"] = "higgs.002505.h130_zz_4e.dc1.lumi10.recon.010";
  row2["amistate"] = "validated";
  row2["amiextractime"] = "2004";
  row2["amiplanpartitions"] = "60";
  row2["amiplannumevents"] = "10000";
  row2["content"] = "cbnt";
  row2["physicsprocess"] = "h130_zz_4e";
  row2["physworkgrp"] = "Higgs";
  row2["includedpartitions"] = "60";
  row2["numevents"] = "9570";
  assert( res.insert_row(row2) == 0 );
  assert( res.num_rows() == 2 );

  msg("Insert another row");
  SqlResult::Row row3;
  row3["id"] = "27";
  row3["name"] = "higgs.002505.h130_zz_4e.dc1.lumi10.recon.010";
  row3["vidh"] = "100";
  row3["vidl"] = "107";
  row3["numevents"] = "1234";
  assert( res.insert_row(row3) == 0 );

  SqlResultTable table(res);

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

  msg("Testing get_schema");
  SqlResult out = 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 << out << endl;
  
  msg("Fetch number of cols in table");
  SelectQuery query;
  query.set("","");
  cout << "Number of cols = " << table.get_col_count(query) << endl;
  ecode = table.error();
  if( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(ecode) << endl;
  }
  
  msg("Fetch number of rows in table"); 
  cout << "Number of rows = " << 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("Write test table");
  system("rm -f mytable.dat");
  int wstat = table.write("mytable.dat");
  if ( wstat ) {
    cout << "error " << wstat << endl;
    assert( false );
  }

  msg("Read the test table");
  SqlTable* ptab = SqlTable::create("SQLRESULT", "mytable.dat");
  assert( ptab != 0 );
  assert( ptab->is_valid() );
  assert( ptab->get_col_count() == table.get_col_count() );
  assert( ptab->get_row_count() == table.get_row_count() );

  msg("Testing execute_insert_query");
  SqlResult::Row insrow;
  insrow["id"] = "2000";
  insrow["name"] = "higgs.dummyinsert";
  insrow["vidh"] = "10000";
  insrow["vidl"] = "10000";
  insrow["aminame"] = "higgs.dummyinsert";
  insrow["amistate"] = "validated";
  insrow["amiextractime"] = "2000";
  insrow["amiplanpartitions"] = "2000";
  insrow["amiplannumevents"] = "2000";
  insrow["content"] = "cbnt";
  insrow["physicsprocess"] = "dummy";
  insrow["physworkgrp"] = "Higgs";
  insrow["includedpartitions"] = "2000";
  insrow["numevents"] = "2000";

  InsertQuery iquery(insrow);
  if( table.execute_insert_query(iquery) != 0 ) {
    cout << "Query returned error:" << table.error() << endl;
    cout << table.error_string(table.error()) << endl;
  }
  cout << "Row Count after insertion is = "
       << table.get_row_count(query) << endl;

  msg("Testing execute_select_query");
  SelectQuery squery;
  squery.reset();
  squery.set("aminame,id,vidh,vidl", "");
  squery.andconstraint("name = 'higgs.002505.h130_zz_4e.dc1.lumi10.recon.010'");
  squery.andconstraint("numevents = 9570");
  out = table.execute_select_query(squery);
  ecode = table.error();
  if( ecode != 0 ) {
    cout << "Query returned error:" << ecode << endl;
    cout << table.error_string(ecode) << endl;
  }
  else {
    msg("Result after execution of select query");
    cout << out;
  }
  
  cout << "Row Count before deletion is = "
       << table.get_row_count(query) << endl;
  msg("Testing execute_delete_query");
  DeleteQuery dquery;
  dquery.andconstraint("id = 2000");
  if( table.execute_delete_query(dquery) != 0 ) {
    cout << "Query returned error:" << table.error() << endl;
    cout << table.error_string(table.error()) << endl;
  }
  cout << "Row Count after deletion is = "
       << table.get_row_count(query) << endl;

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

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