
#include <sstream>

#include "DBManagerMySQL.h"

namespace smartcomparator
{
	namespace simfunctions
	{


SynsetClassSetPtrS DBManagerMySQL::loadSynsetClassesFromMySQL(){

	LexicalUnitByIdMapPtrS 	lus 	= loadLUConnectedWithSynsets();

	SynsetByIdMapPtrS 		roots 	= loadHyperonymSynsets( lus );
	SynsetByIdMapPtrS 		nodes 	= loadHyponymSynsets( lus );
	SynsetRelationSetPtrS 	rels 	= loadSynsetsRelations();

	SynsetByIdMapPtrS both( new SynsetByIdMap() );
	both->insert( roots->begin(), roots->end() );
	both->insert( nodes->begin(), nodes->end() );

	Synset::createSynsetTrees( rels, both );

	return SynsetClassSetPtrS( new SynsetClassSet( roots ) );
}


DBManagerMySQL::DBManagerMySQL(){

}

DBManagerMySQL::~DBManagerMySQL(){

}

bool DBManagerMySQL::connect( ConnectionInfo const& pConnectionInfo ){
	mConnectionInfo = pConnectionInfo;

	bool ret = mConnection.connect(
		pConnectionInfo.dbname.c_str(),
		pConnectionInfo.hostaddr.c_str(),
		pConnectionInfo.user.c_str(),
		pConnectionInfo.password.c_str(),
		pConnectionInfo.port );

	if( ret ){
		mysqlpp::Query query = mConnection.query();
		query << "SET NAMES 'utf8'";
		query.execute();
	}

	return ret;
}


void DBManagerMySQL::disconnect(){
	mConnection.disconnect();
}

/*
SELECT l.ID, l.lemma, l.domain
FROM lexicalunit l
WHERE l.pos = 2 AND l.ID IN ( SELECT r.LEX_ID FROM unitandsynset r );
*/
LexicalUnitByIdMapPtrS DBManagerMySQL::loadLUConnectedWithSynsets(){
	std::stringstream queryString;
	queryString <<
		"SELECT l.ID, l.lemma, l.domain "
		"FROM lexicalunit l "
		"WHERE l.pos = " << mConnectionInfo.language <<
			" AND l.ID IN ( SELECT r.LEX_ID FROM unitandsynset r );";

	mysqlpp::Query query = mConnection.query(queryString.str());

	LexicalUnitByIdMapPtrS lexicalUnitsMap( new LexicalUnitByIdMap() );

	mysqlpp::StoreQueryResult res = query.store();
	if( res ){
		mysqlpp::StoreQueryResult::iterator iter( res.begin() );
		for(; iter != res.end(); ++iter ){
			size_t id 			= static_cast<size_t>( (*iter)[0] );
			std::string lemma	= (*iter)[1].data();
			size_t domain		= static_cast<size_t>( (*iter)[2] );
			lexicalUnitsMap->insert( std::pair<size_t, LexicalUnitPtrS>(
				id, LexicalUnitPtrS( new LexicalUnit( id, lemma, domain ) ) ) );
		}
	}

	return lexicalUnitsMap;
}


void DBManagerMySQL::parseSynset(
	LexicalUnitByIdMapPtrS& 	pLexicalUnitsMap,
	mysqlpp::StoreQueryResult& 	pResult,
	SynsetByIdMapPtrS&			pSynsetMapToFill )
{
	size_t		synsetId, lexicalId;
	SynsetPtrS 	lastSynset;
	size_t		lastSynsetId = -1UL;

	mysqlpp::StoreQueryResult::iterator iter( pResult.begin() );
	for(; iter != pResult.end(); ++iter ){
		synsetId 				= static_cast<size_t>( (*iter)[0] );
		lexicalId 				= static_cast<size_t>( (*iter)[1] );
		LexicalUnitPtrS lexPtr 	= pLexicalUnitsMap->find( lexicalId )->second;

		if( synsetId != lastSynsetId ){
			lastSynset.reset( new Synset( synsetId, lexPtr->getDomain() ) );
			pSynsetMapToFill->insert( std::pair<size_t, SynsetPtrS>( synsetId, lastSynset ) );
			lastSynsetId = synsetId;
		}

		lastSynset->getLemmasEditable().insert(
			std::pair<size_t, LexicalUnitPtrS>( lexicalId, lexPtr ) );
	}
}

/*
SELECT a.SYN_ID, a.LEX_ID
FROM unitandsynset a, lexicalunit lu
WHERE a.LEX_ID = lu.ID AND lu.pos = 2 AND a.SYN_ID NOT IN (
SELECT r.CHILD_ID FROM synsetrelation r
WHERE r.REL_ID = ( SELECT rr.ID FROM relationtype rr WHERE rr.name = 'hiperonimia' ) )
ORDER BY a.SYN_ID ASC, a.unitindex ASC;
*/


SynsetByIdMapPtrS DBManagerMySQL::loadHyperonymSynsets( LexicalUnitByIdMapPtrS& pLexicalUnitsMap ){
	std::stringstream queryString;
	queryString <<
		"SELECT a.SYN_ID, a.LEX_ID "
		"FROM unitandsynset a LEFT JOIN lexicalunit lu ON a.LEX_ID = lu.ID "
		"WHERE lu.pos = " << mConnectionInfo.language <<
			" AND a.SYN_ID NOT IN ( "
		"SELECT r.CHILD_ID FROM synsetrelation r "
		"WHERE r.REL_ID = ( SELECT rr.ID FROM relationtype rr WHERE rr.name = 'hiperonimia' ) ) "
		"ORDER BY a.SYN_ID ASC, a.unitindex ASC;";

	mysqlpp::Query query = mConnection.query(queryString.str());


	SynsetByIdMapPtrS synsetMap( new SynsetByIdMap() );
	mysqlpp::StoreQueryResult res = query.store();

	if( res )
		parseSynset( pLexicalUnitsMap, res, synsetMap );

	return synsetMap;
}

/*
 SELECT a.SYN_ID, a.LEX_ID
FROM unitandsynset a, lexicalunit lu
WHERE a.LEX_ID = lu.ID AND lu.pos = 2 AND a.SYN_ID IN (
SELECT r.CHILD_ID FROM synsetrelation r
WHERE r.REL_ID = ( SELECT rr.ID FROM relationtype rr WHERE rr.name = 'hiperonimia' ) )
ORDER BY a.SYN_ID ASC, a.unitindex ASC
*/
SynsetByIdMapPtrS DBManagerMySQL::loadHyponymSynsets( LexicalUnitByIdMapPtrS& pLexicalUnitsMap ){
	std::stringstream queryString;
	queryString <<
		"SELECT a.SYN_ID, a.LEX_ID "
		"FROM unitandsynset a LEFT JOIN lexicalunit lu ON a.LEX_ID = lu.ID "
		"WHERE lu.pos = " << mConnectionInfo.language <<
			" AND a.SYN_ID IN ( "
		"SELECT r.CHILD_ID FROM synsetrelation r "
		"WHERE r.REL_ID = ( SELECT rr.ID FROM relationtype rr WHERE rr.name = 'hiperonimia' ) ) "
		"ORDER BY a.SYN_ID ASC, a.unitindex ASC;";

	mysqlpp::Query query = mConnection.query(queryString.str());

	SynsetByIdMapPtrS synsetMap( new SynsetByIdMap() );
	mysqlpp::StoreQueryResult res = query.store();

	if( res )
		parseSynset( pLexicalUnitsMap, res, synsetMap );

	return synsetMap;
}


SynsetRelationSetPtrS DBManagerMySQL::loadSynsetsRelations(){
	mysqlpp::Query query = mConnection.query(
		"SELECT r.PARENT_ID, r.CHILD_ID FROM synsetrelation r "
		"WHERE r.REL_ID = ( SELECT rr.ID FROM relationtype rr WHERE name = 'hiperonimia' ) "
		"AND PARENT_ID IN ( SELECT SYN_ID FROM unitandsynset ) "
		"AND CHILD_ID IN ( SELECT SYN_ID FROM unitandsynset ) "
		"ORDER BY r.PARENT_ID;"
	);

	SynsetRelationSetPtrS pairs( new SynsetRelationSet() );

	mysqlpp::StoreQueryResult res = query.store();
	if( res ){
		mysqlpp::StoreQueryResult::iterator iter( res.begin() );
		for(; iter != res.end(); ++iter ){
			pairs->insert( SynsetRelation( (*iter)[0], (*iter)[1] ) );
		}
	}

	return pairs;
}

	}
}
