#include <creaImageIOSQLiteTreeHandler.h>
#include <creaImageIOSystem.h>

#include "CppSQLite3.h"

#include <sys/stat.h>

//#include <creaImageIOSQLiteTreeHandlerStructure.h>

//#include <creaImageIOUtilities.h>

//#include <icons/database.xpm>

#include <deque>

#include "wx/wx.h"
#include <wx/dir.h>
#include <wx/filename.h>


//#include <icons/close.xpm>

#include <creaWx.h>
using namespace crea;

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

namespace creaImageIO
{
  using namespace tree;


  //=============================================================
  SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
    : mFileName(filename)
  {
    mDB = new CppSQLite3DB;
	mIsAdding=false;
    GimmickMessage(1,"SQLite version : "
		   <<std::string(mDB->SQLiteVersion())<< std::endl);
  }
  //=============================================================

  //=============================================================
  SQLiteTreeHandler::~SQLiteTreeHandler()
  {
    delete mDB;
  }
  //=============================================================
  

  //=============================================================
  //  void SQLiteTreeHandler::Print() const 
  //  {
    /*
    std::cout << "-> '"<<GetName()<< "' - '"
	      << GetFileName()<<"'"<<std::endl;
    ChildrenListType::const_iterator i;
    for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
      {
	(*i)->Print();
      }
    */
  //  }
  //=============================================================
  
  //=====================================================================
  /*
  bool SQLiteTreeHandler::LocationIsValid()
  {
    // TO DO 
    return true;
  }
  */
  //=====================================================================


  //=====================================================================
  bool SQLiteTreeHandler::Open(bool writable)
  {
    //    std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
    SetWritable(writable);
    return DBOpen();
  }

  //=====================================================================
  bool SQLiteTreeHandler::Create(bool writable)
  {
    //    std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
    SetWritable(writable);
    return DBCreate();
  }
  //=====================================================================


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


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

  //===================================================================== 
  int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
  {
    if (parent==0) parent = GetTree().GetTree();
    return DBLoadChildren(parent,maxlevel);
  }
  //===================================================================== 




  //===================================================================== 
  void SQLiteTreeHandler::UnLoad(tree::Node* n)
  {
  }
  //===================================================================== 

  //===================================================================== 
  int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
  {
    //std::cout << "AddBranch" << std::endl;
    tree::Node* parent = DBGetParent(attr);
    //std::cout << " parent = "<<parent->GetLabel()<<" level "<<parent->GetLevel()<<std::endl;
    DBGraftToParent(parent,attr);
	
    return (parent->GetLevel()+1);
	  
  }
  //===================================================================== 
 

  //===================================================================== 
   bool SQLiteTreeHandler::Remove(tree::Node* node)
   {
   DBRecursiveRemoveNode(node);
 
    //    std::cout << "DELETE"<<std::endl;
    if (node->GetParent())
      {
	node->GetParent()->RemoveChildrenFromList(node);
      }
    delete node;
    //    std::cout << "DELETE OK"<<std::endl;
    return true;
   }

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

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

  //===================================================================== 
  /// Sets an attribute of a Node
  bool SQLiteTreeHandler::SetAttribute(tree::Node* n, 
				       const std::string& key,
				       const std::string& value)
  {
    if (n==0) n=GetTree().GetTree();
    return DBSetAttribute(n,key,value);
  }
  //===================================================================== 













  //=====================================================================
  // SQLite DB specific methods
  //=====================================================================




  //=====================================================================
  char* format_sql(const std::string& s)
  { 
    return sqlite3_mprintf("%q",s.c_str());
  }
  //=====================================================================

  //  sqlite3_exec(db, zSQL, 0, 0, 0);
  //  sqlite3_free(zSQL);
  //	char* CHAIN = format_sql(QUER);		\
//  sqlite3_free(CHAIN);				\

  //=====================================================================
#define QUERYDB(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 UPDATEDB(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 SQLiteTreeHandler::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;
      }
    // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
    if (!DBImportTreeDescription())
      {
	return false;
      }

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

  //=====================================================================
  bool SQLiteTreeHandler::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;
    // Create LEVELS table
    command = "create table LEVELS\n";
    command += "( Name text )\n";
    UPDATEDB(command);
    
    // Iterate the Levels
    for (int l=0; l<GetTree().GetNumberOfLevels(); ++l)
      {
	command = "INSERT INTO LEVELS (Name) VALUES ('";
	command += GetTree().GetLevelDescriptor(l).GetName();
	command += "')";
	UPDATEDB(command);
	
	// Create table of level (for level>0, i.e. not Root)
	if (l>=0)
	  {
	    command = "CREATE TABLE ";
	    command += GetTree().GetLevelDescriptor(l).GetName();
	    command += "\n(\nID INTEGER PRIMARY KEY";
	    if (l>1) 
	      {
		command += ",\nPARENT_ID int not null";	
	      }
	    SQLAppendAttributesDefinition(l,command);
	    if (l>1) 
	      {
		command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
		command += GetTree().GetLevelDescriptor(l-1).GetName();
		command += "(ID) on delete restrict on update restrict";
	      }
	    command += "\n)";
	    UPDATEDB(command);
	    
	    
	    // Add Attribute 'ID' to Description
	    GetTree().GetDescriptor().Add
	      (AttributeDescriptor( "ID",
				    "Database Identifier",
				    0,0,
				    AttributeDescriptor::PRIVATE
				    ),l);
	    
	    if (l>1) 
	      {
		// Add Attribute 'PARENT_ID' to Description
		GetTree().GetDescriptor().Add
		  (AttributeDescriptor( "PARENT_ID",
					"Database Parent Identifier",
					0,0,
					AttributeDescriptor::PRIVATE
					),l);
	      }
	    
	  }
	
	// Create table *_ATTRIBUTES
	
	command = "CREATE TABLE ";
	command += GetTree().GetLevelDescriptor(l).GetName();
	command += "_Attributes\n(\n";
	command += "Key text,\n";
	command += "Name text,\n";	    
	command += "DicomGroup int,\n";
	command += "DicomElement int,\n";	    
	command += "Flags int\n";	    
	command += "\n)";
	UPDATEDB(command);
	
	
	// Fill the table *_ATTRIBUTES
	LevelDescriptor::AttributeDescriptorListType::const_iterator i;
	for (i  = GetTree().GetAttributeDescriptorList(l).begin();
	     i != GetTree().GetAttributeDescriptorList(l).end();
	     ++i)
	  {
	    
	    std::stringstream insert;
	    insert << "INSERT INTO "
		   << GetTree().GetLevelDescriptor(l).GetName()
		   << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
		   << "VALUES ('"
		   << i->GetKey() << "','"
		   << i->GetName() << "',"
		   << i->GetGroup() << ","
		   << i->GetElement() << ","
		   << i->GetFlags() << ");";
	    UPDATEDB(insert.str());
	  }

      } // For l=0...

    // Initialize the root attributes
    GetTree().InitializeAttributeMap();
    // Insert the root in the level 0 table 
    DBInsert(GetTree().GetTree());
    
    
    GetTree().SetChildrenLoaded(true);
    GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
		   <<"' ... OK"<<std::endl);
    return true;
  }
  //=====================================================================
  
  //=====================================================================
  void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
							std::string& s)
  {
    LevelDescriptor::AttributeDescriptorListType::const_iterator i;
    for (i  = GetTree().GetAttributeDescriptorList(level).begin();
	 i != GetTree().GetAttributeDescriptorList(level).end();
	 ++i)
      {
	//	if (i->second.flags==1) continue;
	s += ",\n";
	s += i->GetKey();
	s += " text";
      }
  }
  //=====================================================================
  
  
  //=====================================================================
  bool SQLiteTreeHandler::DBImportTreeDescription()
  {
    GimmickMessage(1,"Importing tree description from database ..."
		   <<std::endl);

    // Test table 'LEVELS' existence
    if ( ! mDB->tableExists("LEVELS") )
      {
	GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
		       <<std::endl);
	return false;
      }

    tree::Descriptor& desc = GetTree().GetDescriptor();
    // clears the existing one
    desc.Clear();
     
    int nblevel = 0;
    std::string query = "SELECT * FROM LEVELS";
    CppSQLite3Query q;
    QUERYDB(query,q);

    while (!q.eof())
      {
	std::string name = q.getStringField(0);
	GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
	desc.Add(LevelDescriptor(name));
	nblevel++;
	q.nextRow();
      }   
    
    for (int level = 0; level < nblevel; ++level )
      {
	std::string table = GetTree().GetLevelDescriptor(level).GetName();
	table += "_Attributes";
	// Test table 'LEVELS' existence
	if ( ! mDB->tableExists(table.c_str()) )
	  {
	    GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
			   <<std::endl);
	    return false;
	  }
	
	std::string query = "SELECT * FROM ";
	query += table;
	CppSQLite3Query q;
	QUERYDB(query,q);
	
	GimmickMessage(2," * Level '"
		       <<GetTree().GetLevelDescriptor(level).GetName()
		       <<"'"<<std::endl);

	// Test that ID and PARENT_ID mandatory attributes exist
	bool ID_found = false;
	bool PARENT_ID_found = false;
	if (level==0) ID_found = true;
	if (level<=1) PARENT_ID_found = true;

	while (!q.eof())
	  {
	    std::string key(q.getStringField(0));
	    std::string name(q.getStringField(1));
	    GimmickMessage(2,"  - Importing attribute '"<<key<<"' '"<<name
			   <<"'"<<std::endl);
	    desc.Add
	      (AttributeDescriptor( key, // Key
				    name, // Name
				    q.getIntField(2), // Group
				    q.getIntField(3), // Element 
				    q.getIntField(4) // Flags
				    ),level);
	    if ( key == "ID" ) 
	      {
		ID_found = true;
	      }
	    if ( key == "PARENT_ID" ) 
	      {
		PARENT_ID_found = true;
	      }
	    q.nextRow();
	  }
	
	if ( ! (ID_found || PARENT_ID_found ) )
	  {
	    GimmickMessage(1,"!! ERROR : Table '"<<table
			   <<"' does not contain mandatory attribute ID or PARENT_ID"
			   <<std::endl);
	    return false;
 
	  }
      }


    // Create the attributes table for Root (i.e. Tree)
    LevelDescriptor::AttributeDescriptorListType::const_iterator a;
    for (a = GetTree().GetAttributeDescriptorList(0).begin();
	 a!= GetTree().GetAttributeDescriptorList(0).end();
	 ++a)
      {
	/*
	std::string v;
	AttributeMapType::const_iterator i = attr.find(a->GetKey());
	if ( i != attr.end() )  
	  {
	    v = i->second;
	  }
	*/
	GetTree().UnsafeSetAttribute( a->GetKey(), "" );
      }

    // Reading Root attributes
    // Query DB
    query = "SELECT * FROM ";
    query += GetTree().GetLevelDescriptor(0).GetName();
    QUERYDB(query,q);

    for (int fld = 0; fld < q.numFields(); fld++)
      {
	GetTree().UnsafeSetAttribute(q.fieldName(fld),
				     q.getStringField(fld));	    
      }

    GimmickMessage(1,"Importing tree description from database ... OK"
		   <<std::endl);
    return true;
  }
  //=====================================================================

















  //========================================================================
  /// 
  std::string& SQLformat(std::string& str)
  {
    // quote must be doubled
    boost::algorithm::replace_all(str,"'","''");
    // Found strange strings which contained NULL char INSIDE string 
    int i,size=str.size();
    for (i=0;i<size;++i) 
      {
	if (str[i]==0) 
	  {
	    str = str.substr(0,i);
	    break;
	  }
      }
    //    if (i<str.size())
    return str;
  }
  //========================================================================
  
  //=====================================================================
  void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n, 
						    std::string& str)
  {
    GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
    std::string atts="";
    std::string values="";
    tree::Node::AttributeMapType::iterator i;
    for (i =  n->GetAttributeMap().begin();
	 i != n->GetAttributeMap().end();
	 i++)
      {
	if (i->first=="ID") 
	  {
	    continue;
	  }
	//	std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
	atts += "'" + i->first + "'";
	values += "'" + SQLformat(i->second) + "'"; 
	atts += ",";
	values += ",";
	GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
      }
    atts[atts.size()-1]=' ';
    values[values.size()-1]=' ';

    str = "("+atts+") VALUES ("+values+")";
    GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
  }
  //=====================================================================

  //=====================================================================
  tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr) 
  {
    Node* parent = GetTree().GetTree();
    bool go_down;
    do 
      {
	go_down = false;
	// Load the children of the current parent
	DBLoadChildren(parent);
	// Iterate the children 
	tree::Node::ChildrenListType::const_iterator i;
	for (i = parent->GetChildrenList().begin();
	     i!= parent->GetChildrenList().end();
	     ++i)
	  {
	    if ( (*i)->Matches( attr ) ) 
	      {
		go_down = true;
		parent = *i;
		break;
	      }
	  }	
      }
    while (go_down);
    return parent;
  }
  //=====================================================================


  //=====================================================================
  int SQLiteTreeHandler::DBLoadChildren(tree::Node* node, 
					int numberoflevels)
  {
    if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() ) 
      return 0;

    GimmickMessage(2,"Loading children of '"<<node->GetLabel()
		   <<"'"<<std::endl);

    int nbloaded = 0;
    // If children loaded we do not have to do it but we need to recurse
    // in order to load the children's children if necessary, and so on...
    if (node->GetChildrenLoaded()||mIsAdding) 
      {
	// Iterate the children 

	tree::Node::ChildrenListType::iterator i;
	for (i = node->GetChildrenList().begin();
	     i!= node->GetChildrenList().end();
	     ++i)
	  {
	    nbloaded += DBLoadChildren(*i,numberoflevels-1);
	  }
	node->SetChildrenLoaded(true);
	return nbloaded;

      }
	else
	{
    /// If children not loaded : do it and recurse

    // Query DB
    int level = node->GetLevel();
    std::string query = "SELECT * FROM ";
	
    query += GetTree().GetLevelDescriptor(level+1).GetName();
    if (level>0)
      {
	query += " WHERE PARENT_ID='" + node->UnsafeGetAttribute("ID") 
	  + "'";
      }
    CppSQLite3Query q;
    QUERYDB(query,q);

	int p=0;
    while (!q.eof())
      {

	//	std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;

	nbloaded++;
	Node* n = new Node(node);
	for (int fld = 0; fld < q.numFields(); fld++)
	  {
	    n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));	    
	  }
	/*
	// Index 
	TypeId ti;
	ti.type = type;
	ti.id = n->GetFieldValue("ID");    
	mTypeIdToNodeMap[ti] = n;
	*/
	// recurse 
	if ( numberoflevels != 1 ) 
	  {
	    //  msw[2].Pause();
	    nbloaded += DBLoadChildren(n, numberoflevels-1);
	    //	    msw[2].Resume();
	  }
	// next entry in db
	q.nextRow();
      }

    node->SetChildrenLoaded(true);
	
    
    //    msw[2].Pause();
    return nbloaded;
	}
  }
  //=====================================================================

  //======================================================================
  void SQLiteTreeHandler::DBInsert(tree::Node* n)
  {
    GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
		   <<"'"<<std::endl);
    std::string val;
    SQLAppendAttributesValues(n,val);
    std::string insert("INSERT INTO ");
    insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
    insert += " " + val + ";";

    UPDATEDB(insert);
	
    // Store DB id of newly created node;
    long lastrow = mDB->lastRowId();
    std::stringstream ri;
    ri << mDB->lastRowId();
    n->SetAttribute("ID",ri.str());
  }
  //======================================================================

  //======================================================================
  /// Graft the branch defined by the attributes to the parent
  void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent, 
					    const AttributeMapType& attr)
  {
    //    std::cout <<"Grafting to parent '"<<parent->GetLabel()
    //		   <<"'"<<std::endl;

    for (int level = parent->GetLevel()+1;
	 level < GetTree().GetNumberOfLevels();
	 level++)
      {
	// Create Node
	tree::Node* child = new tree::Node(parent,attr);
	child->SetChildrenLoaded(true);
	//	std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
	// Set PARENT_ID if necessary 
	if ( parent->GetLevel()>0 )
	  child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
	
	// Insert in DB
	DBInsert(child);
	/*
	std::string val;
	SQLAppendAttributesValues(child,val);
	std::string insert("INSERT INTO ");
	insert += GetTree().GetLevelDescriptor(child->GetLevel()).GetName();
	insert += " " + val + ";";
	UPDATEDB(insert);
	
	// Store DB id of newly created node;
	long lastrow = mDB->lastRowId();
	std::stringstream ri;
	ri << mDB->lastRowId();
	child->SetAttribute("ID",ri.str());
	*/
	// Down one level
	parent = child;
	
	/*
	// Insert in TypeId map
	TypeId ti;
	ti.type = node->GetType();
	ti.id = node_id;
	mTypeIdToNodeMap[ti] = node;
	//    std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl; 
	// 
	msw[2].Pause();
	
	if (node->GetType()==Node::Patient) summary.added_patients++;
	if (node->GetType()==Node::Study) summary.added_studies++;
	if (node->GetType()==Node::Series) summary.added_series++;
	if (node->GetType()==Node::Image) summary.added_images++;
	*/
      }
  }
  //======================================================================


  //===================================================================== 
  /// Sets an attribute of a Node
  bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n, 
					 const std::string& key,
					 const std::string& value)
  {
    GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
		   "' "<<key<<"='"<<value<<"'"<<std::endl);

    n->SetAttribute(key,value);
    std::string sql = "UPDATE ";
    sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
    sql += " SET ";
    sql += key;
    sql += "='";
    sql += value;
    sql += "' WHERE ID=";
    sql += n->GetAttribute("ID");
    //    sql += " LIMIT 1";
    UPDATEDB(sql);
  }
  //===================================================================== 


  //=====================================================================
  void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
  {
       
    std::string query = "DELETE FROM ";
   
    query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
    query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
 
    UPDATEDB(query);
	GimmickDebugMessage(2,
			    " Deleting '"
				<<node->GetLabel()<<"' with ID '"
			    <<node->GetAttribute("ID")
			    <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
			    <<std::endl);

    Node::ChildrenListType::iterator i;
    for (i  = node->GetChildrenList().begin();
	 i != node->GetChildrenList().end();
	 i++)
      {
	DBRecursiveRemoveNode((*i));
      }
  }

  //===================================================================== 
  unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n) 
  { 
	  // Query DB
	int nb=0;
    int level = n->GetLevel();
	if(GetTree().GetNumberOfLevels()!=(level+1))
	{
	std::string query = "SELECT * FROM ";
    query += GetTree().GetLevelDescriptor(level+1).GetName();
    if (level>0)
      {
	query += " WHERE PARENT_ID='" + n->UnsafeGetAttribute("ID") 
	  + "'";
      }

    CppSQLite3Query q;
    QUERYDB(query,q);
	

		while (!q.eof())
		{
			nb++;
			q.nextRow();
		}
	}
    return nb; 
  }

  /*
  //=====================================================================
  bool SQLiteTreeHandler::DBInsert(Node* alien_node,
				   UpdateSummary& summary)
  {
    //    std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
    //	      <<"')"<<std::endl;
    
    //    if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
    
    // Find parent
    Node* parent;
    std::string parent_id; 
    parent = DBGetOrCreateParent(alien_node,parent_id,summary);
    
    // Insert 
    DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
   return true;
  }
  //=====================================================================


  //=====================================================================
  Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
						std::string& parent_id,
						UpdateSummary& summary)
  {
    //    std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
    //	      << std::endl;
    // Load the patients if not already done
    DBLoadChildren(this,Node::Patient);

    parent_id = "";
    Node* parent = this;

    // The chain of ancestors
    std::deque<Node*> chain;
    Node* cur = alien_node->GetParent();
    for (int type=Node::Patient; 
	 type<alien_node->GetType();++type)
      {
	chain.push_front(cur);
	cur = cur->GetParent();
      }

    // create the nodes if do not exist
    std::deque<Node*>::iterator i;
    for (i=chain.begin();i!=chain.end();++i)
      {
	//	std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
	//	std::string cur_id = DBGetNodeId(*i,parent_id);
	//	if (cur_id.size()==0)
	//	  {
	// Node does not exist : create it
	std::string cur_id;
	parent = DBGetOrCreateNode(*i, 
					parent,
					parent_id,
					cur_id,
					summary
					);
	DBLoadChildren(parent,parent->GetType()+1);

	parent_id = cur_id;
      }
    return parent;
  }
  //=====================================================================



  //=====================================================================
  void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node, 
						      Node* parent, 
						      const std::string& parent_id,
						      UpdateSummary& summary)
  {
    //    std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
    //	      <<alien_node->GetLabel()
    //	      <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
    if (parent != 0) 
      {
	//	std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
      }   
    std::string new_id;
    Node* new_node = DBGetOrCreateNode(alien_node, 
						 parent,
						 parent_id,
						 new_id,
						 summary);
    Node::ChildrenListType::iterator i;
    for (i  = alien_node->GetChildrenList().begin();
	 i != alien_node->GetChildrenList().end();
	 i++)
      {
	DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
      }
  }
  //=====================================================================


  //=====================================================================
  Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node, 
						   Node* internal_parent,
						   std::string parent_id,
						   std::string& node_id,
						   UpdateSummary& summary)
  {
    //   std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
    //	      << internal_parent << "','"<< parent_id<<"')"<<std::endl;
    if (internal_parent != 0) 
      {
	//	std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
      }
    // Node Exists ? return it 
    // First try among children of internal parent 
    Node* node = GetChildrenLike(internal_parent,alien_node);
    if (node>0)
      {
	node_id = node->UnsafeGetFieldValue("ID");
	return node;
      }
    // Second : try in DB 
   
    // Does not exist : Create new one
    node = new Node(alien_node->GetType(),this,internal_parent);
    node->SetChildrenLoaded(true);
    // Copy fields values from alien
    Node::FieldValueMapType::iterator i,j;
    for (i =  node->GetFieldValueMap().begin();
	 i != node->GetFieldValueMap().end();
	 i++)
      {
	j = alien_node->GetFieldValueMap().find(i->first);
	if (j != alien_node->GetFieldValueMap().end() )
	  {
	    i->second = j->second;
	  }
      }

    msw[2].Resume();
    if (node->GetType()!=Node::Patient) 
      node->SetFieldValue("PARENT_ID",parent_id);

    // Insert in DB
    std::string val;
    BuildSQLFieldsValues(node,val);
    std::string insert("INSERT INTO ");
    insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType())) 
      + " " + val + ";";
    //    std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
    UPDATEDB(insert);
    //    std::cout << "** SQL OK"<<std::endl;

    // Store DB id of newly created node;
    long lastrow = mDB->lastRowId();
    std::stringstream ri;
    ri << mDB->lastRowId();
    node_id = ri.str();
    //    std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;

    node->SetFieldValue("ID",node_id);
    // Insert in TypeId map
    TypeId ti;
    ti.type = node->GetType();
    ti.id = node_id;
    mTypeIdToNodeMap[ti] = node;
    //    std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl; 
    // 
    msw[2].Pause();

    if (node->GetType()==Node::Patient) summary.added_patients++;
    if (node->GetType()==Node::Study) summary.added_studies++;
    if (node->GetType()==Node::Series) summary.added_series++;
    if (node->GetType()==Node::Image) summary.added_images++;

    return node;
  }
  //=====================================================================

  //=====================================================================
  Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
					    Node* alien_node)
  {
    Node::ChildrenListType::iterator i;
    for (i  = parent->GetChildrenList().begin();
	 i != parent->GetChildrenList().end();
	 i++)
      {
	Node::Type type = alien_node->GetType();
	bool ok = true;
	for (int j=0;
	     j<SQLiteTreeHandlerStructure::NbQueryFields(type);
	     j++) 
	  {
	    if ( 
		alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
				    QueryField(type,j).key )   !=
		(*i)->GetFieldValue(SQLiteTreeHandlerStructure::
				    QueryField(type,j).key ) )
	      {
		ok = false;
		break;
	      }
	  }
	if (ok) 
	  {
	    return (*i);
	  }
      }
    return 0;    
  }
  //=====================================================================

  //=====================================================================
  std::string SQLiteTreeHandler::DBGetNodeId(Node* node, 
					      const std::string& parent_id)
  {
    //    std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
    //	      <<"','"<<parent_id<<"')"
    //	      <<std::endl;
    msw[2].Resume();
    int type = node->GetType();

    std::string table = SQLiteTreeHandlerStructure::Table(type);
    std::string where = "WHERE ";
    
    if (type!=Node::Patient)
      {
	where += "PARENT_ID='" + parent_id 
	  //node->GetFieldValue("PARENT_ID") 
	  + "' AND ";
      }

    for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++) 
      {
	where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
	  + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
	if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
	  where += "AND ";
    }

    std::string query = "SELECT ID FROM " + table + " " + where + ";";							  
    //    std::cout << "** SQL = '"<<query<<"'"<<std::endl;
    CppSQLite3Query q;
    QUERYDB(query,q);

    if (!q.eof())
      {
	
	//	std::cout << " - Node exists " << std::endl;
	std::string id = q.getStringField(0);
	//	std::cout << " id = '"<<id<<"'"<<std::endl;
	msw[2].Pause();
	return id;
      }
    msw[2].Pause();
    return "";
  }
  //=====================================================================



  //=====================================================================
  Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
				       const std::string& id)
  {
    //    std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;

    TypeId ti;
    ti.type = type;
    ti.id = id;
    
    TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
    if (i == mTypeIdToNodeMap.end())
      {

	    std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
		      << std::endl;
	    creaError("Internal error : mTypeIdToNodeMap does not contain key");
	    // }
      }

    //    std::cout << " ** Node = "<<i->second<<std::endl;
    return i->second;
  }
 
  //=====================================================================

  //=====================================================================
  bool SQLiteTreeHandler::Remove(Node* node)
  {

    //DBRecursiveRemoveNode(node);
 
    //    std::cout << "DELETE"<<std::endl;
    if (node->GetParent())
      {
	node->GetParent()->RemoveChildrenFromList(node);
      }
    delete node;
    //    std::cout << "DELETE OK"<<std::endl;
    return true;
  }
  
  
  //========================================================================

  //=====================================================================
  void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
  {
    //    std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
    //	      <<node->GetLabel()<<"')"<<std::endl;

    std::string query = "DELETE FROM ";
    query += SQLiteTreeHandlerStructure::Table(node->GetType());
    query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
 
    UPDATEDB(query);

    Node::ChildrenListType::iterator i;
    for (i  = node->GetChildrenList().begin();
	 i != node->GetChildrenList().end();
	 i++)
      {
	DBRecursiveRemoveNode((*i));
      }
  }
  
  //=====================================================================
  
  //=====================================================================
  int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
  {
    std::string query = "SELECT COUNT (ID) FROM ";
    query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
    if (node->GetType() != Node::Database) 
      {
	query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
      }
    query  += ";";
    
    //   std::cout << "**SQL = "<< query << std::endl;
    
    CppSQLite3Query q;
    QUERYDB(query,q);
   
     //    std::cout << "**RES = "<< q.getIntField(0) <<std::endl;

    return q.getIntField(0);
  }
  //=====================================================================
 
 

*/



} // namespace creaImageIO