#include "classifierdb.h"

static int sqlite_callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++)
    {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
  printf("\n");
  return 0;
}


classifierdb::classifierdb()
{
  db = NULL;
}

classifierdb::~classifierdb()
{
  close();
}

int classifierdb::close()
{
  if( db != NULL )
    sqlite3_close(db);
  db = NULL;
  return 0;
}

int classifierdb::open( std::string fn )
{
  close();
  int rc;
  rc = sqlite3_open( fn.c_str(), &db );
  if( rc )
    {
      sqlite3_close( db );
      db = NULL;
      return rc;
    }
  createtables();
  return 0;
}

int classifierdb::createtables()
{
  std::string sqlcmd;
  char *errormsg;
  errormsg = NULL;
  int rc;
  sqlcmd = "CREATE TABLE IF NOT EXISTS classifiertype (id INTEGER CONSTRAINT id PRIMARY KEY AUTOINCREMENT, name text );";
  rc = sqlite3_exec(db, sqlcmd.c_str(), sqlite_callback, 0, &errormsg);
  if( rc )
    {
      std::cout << "SQLITE ERROR: " << errormsg << "\n";
      sqlite3_free(errormsg);
      close();
      return -1;
    }
  sqlcmd = "CREATE TABLE IF NOT EXISTS featurevectortype (id INTEGER CONSTRAINT id PRIMARY KEY AUTOINCREMENT, desciptor text );";
  rc = sqlite3_exec(db, sqlcmd.c_str(), sqlite_callback, 0, &errormsg);
  if( rc )
    {
      std::cout << "SQLITE ERROR: " << errormsg << "\n";
      sqlite3_free(errormsg);
      close();
      return -1;
    }
  sqlcmd = "CREATE TABLE IF NOT EXISTS model (id INTEGER CONSTRAINT id PRIMARY KEY AUTOINCREMENT, ctid int, fvid int, createtime text, notes text, data blob, FOREIGN KEY (fvid) REFERENCES featurevectortype ( id ) ON DELETE RESTRICT, FOREIGN KEY (ctid) REFERENCES classifiertype ( id ) ON DELETE RESTRICT );";
  rc = sqlite3_exec(db, sqlcmd.c_str(), sqlite_callback, 0, &errormsg);
  if( rc )
    {
      std::cout << "SQLITE ERROR: " << errormsg << "\n";
      sqlite3_free(errormsg);
      close();
      return -1;
    }
  return 0;
}

int classifierdb::add_classifiertype( std::string &name )
{
  std::string sqlcmd = "INSERT INTO classifiertype (name) VALUES( ? );";
  sqlite3_stmt *stmt;
  int rc;
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_bind_text( stmt, 1, name.c_str(), name.length(), SQLITE_STATIC );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_classifiertype() unexpected error code ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize( stmt );
  if( rc != SQLITE_OK )
    {
      std::cout << "add_classifiertype() unexpected error code on finalize ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  sqlcmd = "SELECT max(id) FROM classifiertype;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_ROW )
    {
      std::cout << "add_classifiertype() unexpected error code on get maxid ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  int ret;
  ret = sqlite3_column_bytes(stmt, 0);
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_classifiertype() unexpected error code on get maxid1 ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize(stmt);
  return ret;
}

int classifierdb::add_featurevectortype( std::string &des )
{
  std::string sqlcmd = "INSERT INTO featurevectortype (desciptor) VALUES( ? );";
  sqlite3_stmt *stmt;
  int rc;
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  if( rc != SQLITE_OK )
    {
      std::cout << "add_featurevectortype prepare ERROR ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      std::cout << sqlite3_errmsg(db);
      exit(0);
    }
  rc = sqlite3_bind_text( stmt, 1, des.c_str(), des.length(), SQLITE_STATIC );
  if( rc != SQLITE_OK )
    {
      std::cout << "add_featurevectortype bind ERROR ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_featurevectortype() unexpected error code ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize( stmt );
  if( rc != SQLITE_OK )
    {
      std::cout << "add_featurevectortype() unexpected error code on finalize ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }

  sqlcmd = "SELECT max(id) FROM featurevectortype;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_ROW )
    {
      std::cout << "add_featurevectortype() unexpected error code on get maxid ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  int ret;
  ret = sqlite3_column_int(stmt, 0);
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_featurevectortype() unexpected error code on get maxid1 ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize(stmt);
  return ret;
}

int classifierdb::add_model( int ctid, int fvid, std::string &createdate )
{
  std::string sqlcmd;
  sqlcmd = "INSERT INTO model (ctid, fvid, createtime, notes, data) VALUES(?, ?, ?, \"\", \"\");";
  int rc;
  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  if( rc != SQLITE_OK )
    {
      std::cout << "create_new_model() unexpected error code on prep ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      std::cout << sqlcmd << "\n";
      exit(0);
    }
  rc = sqlite3_bind_int( stmt, 1, ctid );
  rc = sqlite3_bind_int( stmt, 2, fvid );
  rc = sqlite3_bind_text( stmt, 3, createdate.c_str(), createdate.length(), SQLITE_STATIC );
  if( rc != SQLITE_OK )
    {
      std::cout << "create_new_model() unexpected error code on bind ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      std::cout << sqlcmd << "\n";
      exit(0);
    }
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "create_new_model() unexpected error code on insert ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      std::cout << sqlcmd << "\n";
      exit(0);
    }
  sqlite3_finalize(stmt );

  sqlcmd = "SELECT max(id) FROM model;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_ROW )
    {
      std::cout << "create_new_model() unexpected error code on get maxid ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  int ret;
  ret = sqlite3_column_int(stmt, 0);
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "create_new_model() unexpected error code on get maxid1 ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize(stmt);
  return ret;
}

int classifierdb::modify_model_data( int mid, char *data, int data_size )
{
  std::string sqlcmd;
  int rc;
  sqlite3_stmt *stmt;
  std::cout << "updateing model to size: " << data_size << "\n";
  sqlcmd = "UPDATE model SET data = ? WHERE id = ?;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_bind_blob( stmt, 1, data, data_size, SQLITE_STATIC );
  rc = sqlite3_bind_int( stmt, 2, mid );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "modify_model() unexpected error code on insert ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  sqlite3_finalize(stmt );
  return 0;
}

int classifierdb::get_model( int id, model &m )
{
  std::string sqlcmd;
  int rc;
  sqlite3_stmt *stmt;
  sqlcmd = "SELECT ctid, fvid, createtime, notes, data FROM model WHERE id = ?;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_bind_int( stmt, 1, id );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_ROW )
    {
      std::cout << "get_model() unexpected error code on get step ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      std::cout << sqlite3_errmsg( db );
      std::cout << sqlcmd << "\n";
      exit(0);
    } 
  m.id = id;
  m.ctid = sqlite3_column_int(stmt, 0);
  m.fvid = sqlite3_column_int(stmt, 1);
  
  const unsigned char *dt = sqlite3_column_text(stmt, 2);
  m.createdate = (char*) dt;
  const unsigned char *tt = sqlite3_column_text(stmt, 3);
  m.notes = (char*) tt;
  
  m.datasize = sqlite3_column_bytes(stmt, 4);
  if( m.datasize == 0 )
    {
      std::cout << "ERROR unexpeced size\n";
      exit(0);
    }
  else
    {
      std::cout << "got data size of " << m.datasize << "\n";
    }
  m.data = new char [m.datasize];
  memcpy( m.data, sqlite3_column_blob(stmt, 4), m.datasize );
          
  rc = sqlite3_step( stmt);
  if( rc != SQLITE_DONE )
    {
      std::cout << "get_model() unexpected error code on get step done ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    } 
  rc = sqlite3_finalize(stmt);
  return 0;
}


int classifierdb::add_model( int ctid, int fvid, std::string &createdate,
                             std::string &notes, char *data, int data_size )
{
  std::string sqlcmd;
  sqlcmd = "INSERT INTO model (ctid, fvid, createtime, notes, data) VALUES(?, ?, ?, ?, ?);";
  int rc;
  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_bind_int( stmt, 1, ctid );
  rc = sqlite3_bind_int( stmt, 2, fvid );
  rc = sqlite3_bind_text( stmt, 3, createdate.c_str(), createdate.length(), SQLITE_STATIC );
  rc = sqlite3_bind_text( stmt, 4, notes.c_str(), notes.length(), SQLITE_STATIC );
  rc = sqlite3_bind_blob( stmt, 5, data, data_size, SQLITE_STATIC );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_model() unexpected error code on insert ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  sqlite3_finalize(stmt );

  sqlcmd = "SELECT max(id) FROM model;";
  rc = sqlite3_prepare_v2( db, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL );
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_ROW )
    {
      std::cout << "add_model() unexpected error code on get maxid ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  int ret;
  ret = sqlite3_column_int(stmt, 0);
  rc = sqlite3_step( stmt );
  if( rc != SQLITE_DONE )
    {
      std::cout << "add_model() unexpected error code on get maxid1 ("
                << rc << ") ";
      printerror( rc );
      std::cout << "\n";
      exit(0);
    }
  rc = sqlite3_finalize(stmt);
  return ret;
}

int classifierdb::printerror( int rc )
{
  if( rc == SQLITE_MISUSE )
    std::cout << "MISUSE";
  else if( rc == SQLITE_BUSY )
    std::cout << "BUSY";
  else if(rc == SQLITE_ROW )
    std::cout << "ROW";
  else if(rc == SQLITE_ERROR) 
    std::cout << "ERROR";
  else
    std::cout << "UNKNOWN";
  return 0;
}


