#include <creaImageIOTimestampDatabaseHandler.h>
#include <creaImageIOSystem.h>

#include "CppSQLite3.h"

#include <sys/stat.h>

#include <deque>

#include <boost/filesystem.hpp>
#include <boost/algorithm/string/replace.hpp>

namespace creaImageIO
{
	using namespace tree;
  //=============================================================
  TimestampDatabaseHandler::TimestampDatabaseHandler(const std::string& filename)
    : mFileName(filename)
  {
    mDB = new CppSQLite3DB;
    GimmickMessage(1,"SQLite version : "
		   <<std::string(mDB->SQLiteVersion())<< std::endl);
  }
  //=============================================================

  //=============================================================
  TimestampDatabaseHandler::~TimestampDatabaseHandler()
  {
    delete mDB;
  }
  //=============================================================
  //=====================================================================
  bool TimestampDatabaseHandler::Open()
  {
    return DBOpen();
  }

  //=====================================================================
  bool TimestampDatabaseHandler::Create()
  {
    return DBCreate();
  }
  //=====================================================================


  //=====================================================================
  bool TimestampDatabaseHandler::Close()
  {
    return true;
  }
  //=====================================================================


  //=====================================================================
  bool TimestampDatabaseHandler::Destroy()
  {
    return false;
  }






  //=====================================================================
  // SQLite DB specific methods
  //=====================================================================
  //=====================================================================
#define QUERYTIMESTAMPDB(QUER,RES)						\
    try									\
      {									\
	GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl);		\
	RES = mDB->execQuery(QUER.c_str());				\
      }									\
    catch (CppSQLite3Exception& e)					\
      {									\
	GimmickError("SQLite query '"<<QUER<<"' : "			\
		     << e.errorCode() << ":"				\
		     << e.errorMessage() );				\
      }									\
  //=====================================================================
#define UPDATETIMESTAMPDB(UP)							\
  try									\
    {									\
      GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl);		\
      mDB->execDML(UP.c_str());						\
    }									\
  catch (CppSQLite3Exception& e)					\
    {									\
      GimmickError("SQLite update '"<<UP<<"' Error : "			\
		   << e.errorCode() << ":"				\
		   << e.errorMessage() );				\
    }									
  //=====================================================================


   //=====================================================================
  bool TimestampDatabaseHandler::DBOpen()
  {
    GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
		   <<"' ... "<<std::endl);
    // OPENING FILE
    if (!boost::filesystem::exists(GetFileName())) 
      {
	return false;
      }

    try
      {
	mDB->open(GetFileName().c_str());
      }
    catch (CppSQLite3Exception& e)
      {
	GimmickError("Opening '"<<GetFileName()<<"' : "
		     << e.errorCode() << ":" 
		     << e.errorMessage());
	return false;
      }

    GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
		   <<"' ... OK"<<std::endl);
    return true;
  }
  //=====================================================================

  //=====================================================================
  bool TimestampDatabaseHandler::DBCreate()
  {
    GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
		   <<"' ... "<<std::endl);

    if (boost::filesystem::exists(GetFileName())) 
      {
	GimmickError(GetFileName()<<"' : "
		     << "file already exists");
	return false;
      }
    
    // OPENING
    try
      {
	mDB->open(GetFileName().c_str());
      }
    catch (CppSQLite3Exception& e)
      {
	GimmickError(e.errorCode() << ":" 
		     << e.errorMessage() <<std::endl);
	return false;
      }
    
     
    // CREATING TABLES
    
    std::string command;
  
    
	    command = "CREATE TABLE ";
	    command += "FILES";
	    command += "\n(\nID INTEGER PRIMARY KEY";
		command += ",\nPARENT_ID int not null";	
	    command += ",\nPATH text";
		command += ",\nLastModified datetext";
		command += ",\nLastRead datetext";
		command += ",\nTopLevelNodeId text";
		command += ",\nReferencedDB text";
		command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
		command += "FILES";
		command += "(ID) on delete restrict on update restrict";
	      
	    command += "\n)";
	    UPDATETIMESTAMPDB(command);
	    
    return true;
  }



  //=====================================================================
  void TimestampDatabaseHandler::CleanPath(std::string& str) const
  {
	 size_t pos;
	 do
     {
         pos = str.find('\\');
         if ((int)pos!=-1)  
		 {
			 str.replace(pos, 1, "/");
		 }
     }
     while ((int)pos!=-1);
  }
  //=====================================================================

  bool TimestampDatabaseHandler::AddDirectory(const std::string& parent,
										   const std::string& path, 
										   const time_t lastModif, 
										   const time_t lastRead,
										   const std::string& refdb)
  {
	 bool valid=false;
	 std::string par=parent.c_str();
	 std::string pat=path.c_str();
	 CleanPath(par);
	 CleanPath(pat);

	 std::string pathId=IsIndexed(pat,refdb);
	 //Case: It is a root parent
	 if(parent.compare("")==0)
	 {
		 if(pathId.compare("")==0)
		 {
			AddFile(pat,lastModif,lastRead,refdb);
			valid=true;
		 }
		 else
		 {
			 valid=CheckTimestamp(pathId, lastModif, refdb);
		 }
	 }
	 else 
	 {
		 std::string parentId=IsIndexed(par,refdb);
		 //Case: Parent is not in database
		 if(parentId.compare("")==0)
		{
			AddFile(par,lastModif,lastRead,refdb);
			parentId=IsIndexed(par,refdb);
		}

		//Case path is not in database
		if(pathId.compare("")==0)
		{
		    AddFile(parentId,pat,lastModif,lastRead,refdb);
			valid=true;
		}
		//Parent and path are in the database
		else
		{
			SetAttribute("PARENT_ID",parentId,"ID", pathId);
			valid=CheckTimestamp(pathId, lastModif, refdb);
		}
	 }
	 return valid;
	
  }

  //=====================================================================

  void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead,  const std::string& refdb)
  {
	std::stringstream out;
	out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES(0,'"<<path<<"',";
	out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
    UPDATETIMESTAMPDB(out.str());
	
  }

   //=====================================================================

  void TimestampDatabaseHandler::AddFile(const std::string& parentId, 
										 const std::string& path, 
										 const time_t lastModif, 
										 const time_t lastRead,  
										 const std::string& refdb)
  {
	std::stringstream out;
	out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES("<<parentId<<",'"<<path<<"',";
	out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
    UPDATETIMESTAMPDB(out.str());
  }

  //=====================================================================
  std::string TimestampDatabaseHandler::IsIndexed(const std::string& path, const std::string& refdb)
  {
	std::string pat=path.c_str();
	CleanPath(pat);
	std::stringstream out;
	std::stringstream result;
	out<<"SELECT ID FROM FILES WHERE PATH='"<<pat<<"' AND REFERENCEDDB='"<<refdb<<"';";
		
	CppSQLite3Query q;
	QUERYTIMESTAMPDB(out.str(),q);
	
	
	while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
			  result<<q.getStringField(fld);
	      }
	    q.nextRow();
	  }

	  return result.str();
  }

  //=====================================================================
  void TimestampDatabaseHandler::SetAttribute(const std::string& attName, 
											const std::string& attValue,
											const std::string& searchParam,
											const std::string& searchValue)
  {
	std::string av=attValue.c_str();
	std::string sv=searchValue.c_str();
	CleanPath(av);
	CleanPath(sv);

	std::string sql = "UPDATE FILES SET ";
    sql += attName;
    sql += " = '";
    sql += av;
    sql += "' WHERE ";
	sql += searchParam;
	sql += " = '";
    sql += sv;
	sql += "'";
    UPDATETIMESTAMPDB(sql);
  }
 
  //=====================================================================
  void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node, const std::string& refdb)
  {
	  int n=node->GetNumberOfChildren();
	  if(n>0)
	  {
		  std::vector<tree::Node*> children=node->GetChildrenList();
		  std::vector<tree::Node*>::iterator it;
		  for(it=children.begin();it!=children.end();++it)
		  {
			  RemoveNode(searchAtt,(*it),refdb);
		  }
	  }
	  else if(node->GetLevel()==3)
	  {
		  RemoveFile(searchAtt,node->GetAttribute("FullFileName"),refdb);
	  }
	  else
	  {
		  DBRemove("TopLevelNodeId",node->GetAttribute("ID"),refdb);
	  }


  }
  //=====================================================================
  void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb )
  {
	  
	  std::stringstream result;
	  std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"' AND REFERENCEDDB='"+refdb+"';";
		
	  CppSQLite3Query q;
	  QUERYTIMESTAMPDB(sel,q);
	
	  while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
			  result<<q.getStringField(fld);
	      }
	    q.nextRow();
	  }
	  DBRemove(searchAtt,searchVal,refdb);
	  
		  int nChildren=0;
		  sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
		  CppSQLite3Query q2;
		  QUERYTIMESTAMPDB(sel,q2);
		  while (!q2.eof())
			{
				nChildren++;
				q2.nextRow();
			}
			if(nChildren<1)
			{
				if(!result.str().compare("0"))
				{
				RemoveFile("ID",result.str(),refdb);
				}
				else
				{
				DBRemove("ID",result.str(),refdb);
				}
			}
  }

  //=====================================================================
  void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb)
  {
       
    std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "' AND REFERENCEDDB='"+refdb+"';";
    UPDATETIMESTAMPDB(query);
  }

   //=====================================================================
  bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif, const std::string& refdb)
  {
	std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"' AND REFERENCEDDB='"+refdb+"';";
	CppSQLite3Query q;
	QUERYTIMESTAMPDB(sel,q);
	double timestamp;
	
	while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
			  timestamp=q.getFloatField(fld);
	      }
	    q.nextRow();
	  }

	  
	  std::stringstream lm;
	  lm<<lastModif;
	  double modif=atof((lm.str()).c_str());
	  if(timestamp<modif)
	  {
		  SetAttribute("LastModified",lm.str(),"ID",pathId);
		  return true;
	  }
	  return false;	 
  }

  //=====================================================================
  void TimestampDatabaseHandler::RemoveEntries(const std::string i_table, 
		const std::string i_attribute, 
		const std::string i_operand, 
		const std::string i_val)
    {
        std::stringstream query;
		query<<"DELETE  FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
        UPDATETIMESTAMPDB(query.str());
	}

}// namespace creaImageIO