#include "Database.h"

using namespace GestureDef;
using namespace Ogre;

Database::Database(Ogre::Log* log)
{
	dbOpened = false;
	mLog = log;

	// stmt
	loadOneGestureStmt	= 0;
	loadGesturesStmt	= 0;
	deleteOneGestureStmt= 0;
	getCurrentTrialStmt = 0;	
	loadTestersStmt     = 0;
}  

Database::~Database()
{	
	if (dbOpened){
		mLog->logMessage("Close Database with ~Database()");
		sqlite3_close(db);
	}
}

bool Database::Open()
{    
	rc = sqlite3_open_v2("MotionGesture.db", &db, SQLITE_OPEN_READWRITE, 0);
	Ogre::String msg;
    if(rc){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Cannot open database: " + msg);		
        sqlite3_close(db);
    }
	else{
		mLog->logMessage("Open database...");
        dbOpened = true;
		/*
		// Mingyu: we don't create new table here!
        rc = sqlite3_exec(db,
            "CREATE TABLE IF NOT EXISTS GestureTable(          "
            "   name   TEXT    NOT NULL,                       "
			"   tester TEXT    NOT NULL,                       "
			"   trial     INTEGER NOT NULL,                    "
            "   length    INTEGER NOT NULL,                    "
			"   righthand INTEGER NOT NULL,                    "
            "   data   BLOB    NOT NULL,                       "
			"   noise  BLOB    NOT NULL,                       "
			"   bias   BLOB    NOT NULL,                       "
			"   PRIMARY KEY (name, tester, trial)              "
            ");                                                ",
            0, 0, &zErrMsg);
		if(rc!=SQLITE_OK) mLog->logMessage("Error when CREATE TABLEs " + String(zErrMsg));
		*/

		String saveGestureSQL = "INSERT INTO GestureTable VALUES( :name, :tester, :trial, :length, :righthand, :data, :noise, :bias );";
        rc = sqlite3_prepare_v2(db,saveGestureSQL.c_str(),saveGestureSQL.size()+1,&saveGestureStmt,0);
		if(rc!=SQLITE_OK || saveGestureStmt==0) mLog->logMessage("Error when preparing saveGestureSQL");

		String loadOneGestureSQL = "SELECT length, data, noise, bias, righthand FROM GestureTable "
			"WHERE name=?1 AND tester=?2 AND trial=?3 LIMIT 1;";
		rc = sqlite3_prepare_v2(db,loadOneGestureSQL.c_str(),loadOneGestureSQL.size()+1,&loadOneGestureStmt,0);
		if(rc!=SQLITE_OK || loadOneGestureStmt==0) mLog->logMessage("Error when preparing loadOneGestureSQL");

		String getCurrentTrialSQL = "SELECT count(*) FROM GestureTable WHERE name=?1 AND tester=?2;";
		rc = sqlite3_prepare_v2(db,getCurrentTrialSQL.c_str(),getCurrentTrialSQL.size()+1,&getCurrentTrialStmt,0);
		if(rc!=SQLITE_OK || getCurrentTrialStmt==0) mLog->logMessage("Error when prepraring getCurrentTrialSQL");

		String deleteOneGestureSQL = "DELETE FROM GestureTable WHERE name=?1 AND tester=?2 AND trial=?3;";
		rc = sqlite3_prepare_v2(db,deleteOneGestureSQL.c_str(),deleteOneGestureSQL.size()+1,&deleteOneGestureStmt,0);
		if(rc!=SQLITE_OK || deleteOneGestureStmt==0) mLog->logMessage("Error when preparing deleteOneGestureSQL");

		String loadTestersSQL = "SELECT DISTINCT tester, righthand FROM GestureTable";
		rc = sqlite3_prepare_v2(db,loadTestersSQL.c_str(), loadTestersSQL.size()+1,&loadTestersStmt,0);
		if(rc!=SQLITE_OK || loadTestersStmt==0) mLog->logMessage("Error when preparing loadTestersSQL");
	}
	return dbOpened;
}

void Database::Close()
{
	if (dbOpened){
		mLog->logMessage("Close Database with Close()");
		sqlite3_close(db);
		dbOpened = false;
	}
}


// main functions
bool Database::SaveGesture(const Gesture& gesture)
{
	Ogre::String msg;
    bool ok = true;
	//binding values
	// SQL: "INSERT INTO GestureTable VALUES( :name, :tester, :trial, :length, :righthand, :data, :noise, :bias );"
	rc = sqlite3_bind_text(saveGestureStmt, 1, gesture.name.c_str(), gesture.name.size(), SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){		
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding name in SaveGesture() " + msg);
	}
    
	rc = sqlite3_bind_text(saveGestureStmt, 2, gesture.tester.c_str(), gesture.tester.size(), SQLITE_TRANSIENT);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding tester in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_int(saveGestureStmt, 3, gesture.trial);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding trial in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_int(saveGestureStmt, 4, gesture.length);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding length in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_int(saveGestureStmt, 5, gesture.rightHand);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when binding righthand in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_blob(saveGestureStmt, 6, &(gesture.data[0]), sizeof(Sample)*gesture.data.size(), SQLITE_TRANSIENT);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding data in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_blob(saveGestureStmt, 7, &gesture.noise, sizeof(YPR), SQLITE_TRANSIENT);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding noise in SaveGesture() " + msg);
	}

	rc = sqlite3_bind_blob(saveGestureStmt, 8, &gesture.bias, sizeof(YPR), SQLITE_TRANSIENT);
	if (rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding calibration in SaveGesture() " + msg);
	}
		
    //evaluating
    rc = sqlite3_step(saveGestureStmt);
	if(rc!=SQLITE_DONE){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Error when evaluating in SaveGesture() " + msg);
	}			

    //reset statement for next evaluation
    sqlite3_reset(saveGestureStmt);
    sqlite3_clear_bindings(saveGestureStmt);
	if (ok) mLog->logMessage("SaveGesture() success: " + gesture.name + 
		" by "+ gesture.tester + " no." + Ogre::StringConverter::toString(gesture.trial));
	return ok;
}

bool Database::LoadOneGesture(const Ogre::String name, const Ogre::String tester, int trial, Gesture& gesture)
{	
	Ogre::String msg;
	bool ok = true;

	// binding values
	// SQL:loadOneGestureSQL = "SELECT length, data, noise, bias, righthand FROM GestureTable "
	//			"WHERE name=?1 AND tester=?2 AND trial=?3 LIMIT 1";
	rc = sqlite3_bind_text(loadOneGestureStmt,1,name.c_str(),-1,SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding name in LoadOneGesture() " + msg);
	}

	rc = sqlite3_bind_text(loadOneGestureStmt,2,tester.c_str(),tester.size(),SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when binding tester in LoadOneGesture() " + msg);
	}

	rc = sqlite3_bind_int(loadOneGestureStmt,3,trial);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when binding trial in LoadOneGesture() " + msg);
	}

	// evaluation
	const GestureDef::YPR* pYpr = 0;
	const GestureDef::Sample* pSample = 0;
	rc = sqlite3_step(loadOneGestureStmt);        
    while(rc==SQLITE_ROW){
		// Mingyu: this loop should only be executed once!
		gesture.length = sqlite3_column_int(loadOneGestureStmt, 0);		
		pSample = (const GestureDef::Sample*)sqlite3_column_blob(loadOneGestureStmt, 1); // data
		gesture.data.assign(pSample, pSample + gesture.length);
		pYpr = (const GestureDef::YPR*)sqlite3_column_blob(loadOneGestureStmt, 2); // noise
		gesture.noise = *pYpr;		
		pYpr = (const GestureDef::YPR*)sqlite3_column_blob(loadOneGestureStmt, 3); // bias
		gesture.bias = *pYpr;
		gesture.rightHand = sqlite3_column_int(loadOneGestureStmt, 4); // rightHand
        rc = sqlite3_step(loadOneGestureStmt);
    }
	if(rc!=SQLITE_DONE){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when reading data in LoadOneGesture() " + msg);
	}

	// append other fields of this gesture
	gesture.name   = name;
	gesture.tester = tester;
	gesture.trial  = trial;

	// reset
    sqlite3_reset(loadOneGestureStmt);
    sqlite3_clear_bindings(loadOneGestureStmt);
	return ok;
}

int Database::GetCurrentTrial(const String name, const String tester)
{
	Ogre::String msg;

	// SQL: "SELECT count(*) FROM GestureTable WHERE name=?1 AND tester=?2;"
	rc = sqlite3_bind_text(getCurrentTrialStmt,1,name.c_str(),-1,SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Error when binding name in GetCurrentTrial() " + msg);
	}

	rc = sqlite3_bind_text(getCurrentTrialStmt,2,tester.c_str(),tester.size(),SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Error when binding tester in GetCurrentTrial() " + msg);
	}

	// evaluation
	int storedTrials = -1;	
	rc = sqlite3_step(getCurrentTrialStmt);
    while(rc==SQLITE_ROW){
		// Mingyu: this loop should only be executed once!
		storedTrials = sqlite3_column_int(getCurrentTrialStmt, 0);		
        rc = sqlite3_step(getCurrentTrialStmt);
    }
	if(rc!=SQLITE_DONE){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Error when reading data in GetCurrentTrial() " + msg);
	}

	// reset
    sqlite3_reset(getCurrentTrialStmt);
    sqlite3_clear_bindings(getCurrentTrialStmt);

	return storedTrials+1;
}

bool Database::DeleteOneGesture(const Ogre::String name, const Ogre::String tester, int trial)
{	
	Ogre::String msg;
	bool ok = true;

	// binding values
	// SQL:deleteOneGestureSQL = "DELETE FROM GestureTable WHERE name=?1 AND tester=?2 AND trial=?3;";"	
	rc = sqlite3_bind_text(deleteOneGestureStmt,1,name.c_str(),-1,SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db));	ok = false;
		mLog->logMessage("Error when binding name in DeleteOneGesture() " + msg);
	}

	rc = sqlite3_bind_text(deleteOneGestureStmt,2,tester.c_str(),tester.size(),SQLITE_TRANSIENT);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when binding tester in DeleteOneGesture() " + msg);
	}

	rc = sqlite3_bind_int(deleteOneGestureStmt,3,trial);
	if(rc!=SQLITE_OK){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when binding trial in DeleteOneGesture() " + msg);
	}

	 //evaluating
    rc = sqlite3_step(deleteOneGestureStmt);
	if(rc!=SQLITE_DONE){
		msg.assign(sqlite3_errmsg(db));
		mLog->logMessage("Error when evaluating in DeleteOneGesture() " + msg);
	}			

    //reset statement for next evaluation
    sqlite3_reset(deleteOneGestureStmt);
    sqlite3_clear_bindings(deleteOneGestureStmt);
	if (ok) mLog->logMessage("DeleteOneGesture() success: " + name + 
		" by "+ tester + " no." + Ogre::StringConverter::toString(trial) + " is deleted");
	return ok;
}

std::vector<TesterInfo> Database::LoadTesters()
{	
	Ogre::String msg;
	std::vector<TesterInfo> testers;
	bool ok = true;
	
	// SQL:loadOneGestureSQL = "SELECT DISTINCT tester FROM GestureTable"	
	
	// evaluation
	rc = sqlite3_step(loadTestersStmt);        
    while(rc==SQLITE_ROW){		
		TesterInfo tester;
		tester.name.assign((const char*)sqlite3_column_text(loadTestersStmt,0));
		tester.rightHand = sqlite3_column_int(loadTestersStmt,1);
		testers.push_back(tester);	
        rc = sqlite3_step(loadTestersStmt);
    }
	if(rc!=SQLITE_DONE){
		msg.assign(sqlite3_errmsg(db)); ok = false;
		mLog->logMessage("Error when reading data in loadTesters() " + msg);
	}
	else{
		mLog->logMessage("Finish LoadTesters()");
	}

	// reset
    sqlite3_reset(loadTestersStmt);    
	return testers;
}