#include "ISlowControlMySQLInterface.hxx"
#include "IOARuntimeParameters.hxx"
#include <climits>
#include <iostream>
#include <iomanip>

#include <TMath.h>

#define GSC_ERROR_CODE -9999;

   
ISlowControlMySQLInterface::ISlowControlMySQLInterface(const char *username,const char *passwd, TUrl url){

  fUrl = url;
  fUsername = std::string(username);
  fPasswd = std::string(passwd);


  fFlagAlwaysDisconnect = COMET::IOARuntimeParameters::Get().GetParameterI("oaSlowControlDatabase.AlwaysDisconnectFromServer");  
  fFlagAlwaysRetry = COMET::IOARuntimeParameters::Get().GetParameterI("oaSlowControlDatabase.AlwaysRetry");  
  fMaxRetryTime = COMET::IOARuntimeParameters::Get().GetParameterI("oaSlowControlDatabase.MaxRetryTime");
  
  fServer = 0;
  COMETVerbose("Opening connection to MySQL database.");
  GetServer();

  lastAccessedTable = 0;

  // Disconnect the connection right away, if requested.
  if(fFlagAlwaysDisconnect)
    fServer->Close();


}

TSQLServer* ISlowControlMySQLInterface::GetServer(){
  
  bool success = false;
 
  // Check if the database connected is initialized, opened and still valid.
  if(!fServer || !fServer->IsConnected() || fServer->Ping())
    success = false;

  int AttemptNumber = 0;

  // If not connected and active, then try to connect.
  while(!success){

    if(fServer && !fFlagAlwaysDisconnect) // This message is unnecessary if we are always disconnecting.
      COMETLog("Connnection to GSC MySQL server went away... try to reconnect.");

    int waittime = 0;
    if(AttemptNumber){
      waittime = (int)TMath::Power(3.0, AttemptNumber);
      COMETLog("Connection still not established... will try to reconnect in " << waittime << " seconds.");
      sleep(waittime);
    }      

    // Connect to server
    delete fServer;
    fServer = TSQLServer::Connect(fUrl.GetUrl(),fUsername.c_str(),fPasswd.c_str()); 

    // Check if connection was successful.
    if(fServer && fServer->PingVerify()){
      success = true;
      if(AttemptNumber > 0)
         COMETLog("Success; connection reopened.");
    }
    
    // If we failed and have exceeded the allowed re-try time, then throw exception.
    if(!success && (!fFlagAlwaysRetry || waittime > fMaxRetryTime)){
      if(fFlagAlwaysRetry) 
        COMETError("Tried to connect to database for longer than allowed maximum (" << fMaxRetryTime << " seconds).");
      COMETError("No luck connecting to GSC MySQL server... throwing exception.");
      throw COMET::ELostGSCDatabaseConnection();
    }

    AttemptNumber++;
  }
  
  return fServer;

}

ISlowControlMySQLInterface::~ISlowControlMySQLInterface(){

}

void ISlowControlMySQLInterface::ShowTables(){
  
  TSQLResult* result = GetServer()->Query("Show Tables");

  if(!result) return;
  
  COMETLog("List of tables in database: ");
  
  // Loop over rows of result.
  TSQLRow* row = result->Next();
  while(row){
    COMETLog(row->GetField(0));
    delete row;
    row = result->Next();
  }

  delete result;

  
}

int ISlowControlMySQLInterface::GetNumberCurrentConnections(){
  
  // Start by printing the maximum number of used connections.
  TSQLResult* result1 = GetServer()->Query("Show status");
  TSQLRow* row1 = result1->Next();
  while(row1){

    std::string variable = row1->GetField(0);
    if(variable.find("Max_used_connections") != std::string::npos)
      COMETVerbose("Maximum number of used connections is: " << row1->GetField(1));
    delete row1;
    row1 = result1->Next();
  }

  delete result1;


  // Now get the number of active connections.
  TSQLResult* result = GetServer()->Query("Show processlist");

  if(!result) return -1;
  
  COMETVerbose("List of active connections: ");
  COMETVerbose("User            Host                                     Database      Command         Exec-Time(s)");

  // Loop over rows of result.
  TSQLRow* row = result->Next();
  while(row){

    std::string db("XXXX");
    if(row->GetField(3)) db = row->GetField(3);
    std::string cmd("XXXX");
    if(row->GetField(4)) cmd = row->GetField(4);
    std::string time("XXXX");
    if(row->GetField(5)) time = row->GetField(5);
    COMETVerbose(std::setiosflags(std::ios::left) << std::setw(15) << row->GetField(1) 
                 << " " << std::setw(40) << row->GetField(2)                 
                 << " " << std::setw(13) << db
                << " " << std::setw(15) << cmd
                 << " " << std::setw(15) << time);
    delete row;
    row = result->Next();
  }
  int count = result->GetRowCount();
  delete result;

  return count; 
}


int ISlowControlMySQLInterface::MySQLQuery(int time, std::string table, bool fast_query){

  // _______________________________________________________
  // Start by getting the last row with _i_time <= time;
  // (If making fast query, also require _i_time >= time - 3600).
  std::stringstream command;
  
  command <<"SELECT *,ABS(_i_time-"<< time <<") as distance "
	  << " FROM " << table << " WHERE _i_time <= "<< time <<" "; 	
  if(fast_query) 
    command  << " AND _i_time >= "<< time <<" - 3600 ";      
  command << " ORDER BY distance LIMIT 1";
  TSQLResult* result = GetServer()->Query(command.str().c_str());

  if(!result){
    COMETWarn("ISlowControlMySQLInterface::MySQLQuery : strange."
              << " MySQL query failed to  return results.");
    if(fFlagAlwaysDisconnect) GetServer()->Close();
    return GSC_ERROR_CODE;
  }

  // Get first row.  There should only be one.
  TSQLRow* srow = result->Next();
  if(!srow){
    delete result;
    if(fFlagAlwaysDisconnect) GetServer()->Close();
    return GSC_ERROR_CODE;
  }

  // Create the object that we are going to use to save this result and row.
  ISlowControlMySQLRow *saved_row = new ISlowControlMySQLRow(result,srow,table);
  

  // ______________________________________________________
  // Add this saved_row to the map.  Do this even before figuring out 
  // what the end time for this interval is (since the interval might not have 
  // an end time).
  std::string tmp(table);
  if(fCachedTables.find(tmp) != fCachedTables.end()){
    if ( lastAccessedTable!=0 ){
      if(lastAccessedTable->GetTableName() == tmp)
        lastAccessedTable = 0;
    }
    delete fCachedTables[tmp];
  }
  fCachedTables[tmp] = saved_row;



  // _______________________________________________________
  // Now get the next row with _i_time > time
  // (If making fast query, also require _i_time <= time + 3600).
  std::stringstream command2;
  command2 <<"SELECT *,ABS(_i_time-"<< time <<") as distance "
              << " FROM " << table << " WHERE _i_time > "<< time <<" "; 
  if(fast_query)
    command2  << " AND _i_time <= "<< time <<" + 3600 ";
  command2    << " ORDER BY distance LIMIT 1";
  TSQLResult* result_end = GetServer()->Query(command2.str().c_str());

  if(!result_end){
    if(fFlagAlwaysDisconnect) GetServer()->Close();
    return GSC_ERROR_CODE;
  }
  // Get first row.  There should only be one.
  // If there is not, that is okay too.  Just means we are 
  // in last interval.
  TSQLRow* srow2 = result_end->Next();
  if(!srow2){
    delete result_end;
    if(fFlagAlwaysDisconnect) GetServer()->Close();
    return 0;
  }
  
  if(result->GetFieldCount() < 2){
    delete result_end;
    delete srow2;
    if(fFlagAlwaysDisconnect) GetServer()->Close();
    return GSC_ERROR_CODE;  
  }
  int endTime = 0;
  for(int i = 0; i < result_end->GetFieldCount(); i++){    
    std::string tmp(result_end->GetFieldName(i));
    if(tmp == "_i_time") endTime = atoi(srow2->GetField(i));    
  }

  saved_row->SetEndTime(endTime);
  
  // Close database connection, if this is requested behaviour.
  if(fFlagAlwaysDisconnect)
    GetServer()->Close();

  delete result_end;
  delete srow2;
  return 0;

}

/// This method is used to query for a slow control variable in a particular table
/// at a particular time.  The method first checks if a cached version of this table 
/// exists which has a valid interval.  If true, then use variable from cached table;
/// otherwise reconnect to database.
ISlowControlMySQLRow* ISlowControlMySQLInterface::GetTable(int time,const char *table){

  // Check if this was the last table accessed; saves us 
  // looping over table map; also check if still in time 
  // interval.
  if(lastAccessedTable 
     && lastAccessedTable->GetTableName().compare(table) == 0){
    if(time < lastAccessedTable->GetEndTime() &&
       time >= lastAccessedTable->GetStartTime()){       
      return lastAccessedTable;
    }
  }


  // Check if we have a cached version of this table.
  // If true, then check if we are still within interval 
  // of the cached version.  If this is also true, then 
  // don't need to reconnect to database.
  if(fCachedTables.find(table) != fCachedTables.end()){
     if(time < fCachedTables[table]->GetEndTime() &&
         time >= fCachedTables[table]->GetStartTime()){
       lastAccessedTable = fCachedTables[table];
       return fCachedTables[table];
     }
  }
  

  // Didn't find table, so need to query database.
  // First we try a fast query, where we limit the 
  // expected range of the row time.
  int status = MySQLQuery(time,table,true);
  if(status < 0){
    COMETTrace("GSC Database - Fast Query Failed... trying Slow Query : " << status);
    // If fast query failed, maybe we are dealing with a table that is only sporadically
    // filled.  In that case, try slow query. 
    status = MySQLQuery(time,table,false);
    if(status < 0){
      COMETLog("GSC Database - Database Query Failed: " << status);
      return 0;
    }
  }
  
  // We still might not have found table, so need to check again.
  if(fCachedTables.find(table) != fCachedTables.end())
      if(time < fCachedTables[table]->GetEndTime() &&
         time >= fCachedTables[table]->GetStartTime()){
        lastAccessedTable = fCachedTables[table];
        return fCachedTables[table];
      }


  return 0;
}

 
/// This method is used to query for a slow control variable in a particular table
/// at a particular time. 
double ISlowControlMySQLInterface::QueryShowField( int time, const char *table, const char *fieldname){
  
  ISlowControlMySQLRow* row = GetTable(time, table);

  if(row)
    return row->GetValueFloat(fieldname);

  return GSC_ERROR_CODE;

}

/// This method is used to query for a slow control variable in a particular table
/// at a particular time. 
int ISlowControlMySQLInterface::QueryShowFieldInteger( int time, const char *table, const char *fieldname){
  
  ISlowControlMySQLRow* row = GetTable(time, table);

  if(row)
    return row->GetValueInteger(fieldname);

  return GSC_ERROR_CODE;

}

/// This method is used to query for a slow control variable in a particular table
/// at a particular time. 
std::string ISlowControlMySQLInterface::QueryShowFieldString( int time, const char *table, const char *fieldname){
  
  ISlowControlMySQLRow* row = GetTable(time, table);

  if(row)
    return row->GetValueString(fieldname);

  return std::string("GSC_ERROR_CODE");

}



 
/// This method is used to query for the valid time interval of a slow
/// control variable in a particular table / at a particular time.  The
/// method first checks if a cached version of this table / exists which
/// has a valid interval.  If true, then use variable from cached table;
/// otherwise reconnect to database.
int ISlowControlMySQLInterface::GetFieldInterval( int time, const char *table, 
                                                  const char *fieldname, int &start_time, int &end_time){

  ISlowControlMySQLRow* row = GetTable(time, table);

  if(row){
    start_time = fCachedTables[table]->GetStartTime(); 
    end_time = fCachedTables[table]->GetEndTime(); 
    return 0;
  }

  start_time=time-1;
  end_time=time+1;
  return -9999;
}



/// This method is used to query for a slow control variable in a particular table
/// at a particular time.  The method first checks if a cached version of this table 
/// exists which has a valid interval.  If true, then use variable from cached table;
/// otherwise reconnect to database.
void ISlowControlMySQLInterface::PrintTable( int time, const char *table){

    QueryShowField(time, table,"unix_time");    
    
    if(fCachedTables.find(table) != fCachedTables.end())
        return fCachedTables[table]->Print();

}

/// Helper method, to clean up these long query statements
std::vector<std::pair<double,std::vector<double> > > 
ISlowControlMySQLInterface::ExecuteLongQuery(std::string command, std::vector<std::string> table_fields){

  std::vector<std::pair<double,std::vector<double> > > result;
  
  TSQLResult* sqlresult = GetServer()->Query(command.c_str());
  
  if(!sqlresult) return result;
  
  TSQLRow* row = sqlresult->Next();
  while(row){
    unsigned int colnum = (unsigned int)sqlresult->GetFieldCount();
    
    if(colnum != table_fields.size()+1){
      COMETError("ISlowControlMySQLInterface::QueryGetFieldNoCache: "
                 << "input fields doesn't match database result.");
      continue;
    }
    
    std::vector<double> current_fields;
    std::pair<double,std::vector<double> > current_data;

    current_data.first = atof(row->GetField(0));
    
    for(unsigned int counter=1; counter < colnum; counter++){
      double value = atof(row->GetField(counter));
      current_fields.push_back(value);
    }
    
    current_data.second = current_fields;        
    result.push_back(current_data);      
    
    delete row;
    row = sqlresult->Next();
  }
  
  // Close database connection, if this is requested behaviour.
  if(fFlagAlwaysDisconnect)
    GetServer()->Close();

  // Delete the ROOT SQL result
  delete sqlresult;
  
  return result;
}


/// 
std::vector<std::pair<double,std::vector<double> > > 
ISlowControlMySQLInterface::QueryGetFieldNoCache(int time, const char *table_name, std::vector<std::string> table_fields, int nrows){
    
   
    std::stringstream alt_command;
    alt_command << "SELECT _i_time,";

    // add the requested fields
    for(unsigned int i = 0; i < table_fields.size(); i++){
        alt_command << table_fields[i];
        if(i+1 < table_fields.size()) alt_command << ",";
    }
    alt_command <<" FROM "   << table_name << " WHERE _i_time >= " << time << " ORDER BY _i_time  limit " << nrows << ";";

    return ExecuteLongQuery(alt_command.str(),table_fields);


}


std::vector<std::pair<double,std::vector<double> > > 
ISlowControlMySQLInterface::QueryGetFieldStartEndTime(const char *table_name, std::vector<std::string> table_fields, int start_time, int end_time){
    
    std::stringstream alt_command;
    alt_command << "SELECT _i_time,";

    // add the requested fields
    for(unsigned int i = 0; i < table_fields.size(); i++){
        alt_command << table_fields[i];
        if(i+1 < table_fields.size()) alt_command << ",";
    }
    
    alt_command <<" FROM "   << table_name << " WHERE _i_time >= "<< start_time << " AND _i_time < " << end_time;

    return ExecuteLongQuery(alt_command.str(),table_fields);

}

std::pair<double,std::vector<double> > ISlowControlMySQLInterface::QueryGetFieldNoCache(int time, const char *table_name, std::vector<std::string> table_fields){
  
    
  std::vector<std::pair<double,std::vector<double> > > result = QueryGetFieldNoCache(time, table_name, table_fields, 1);
  
  if(result.size() == 1)
    return result[0];
  
  return std::pair<double,std::vector<double> >();
  
}

std::vector<double> ISlowControlMySQLInterface::GetLastEntryNoCache(const char *table_name, std::vector<std::string> table_fields){


    std::vector<double> result;
   
    std::stringstream alt_command;
    alt_command << "SELECT ";

    // add the requested fields
    for(unsigned int i = 0; i < table_fields.size(); i++){
        alt_command << table_fields[i];
	
	if(i <  table_fields.size() -1)
	  alt_command << ",";
    }
    
    alt_command << " FROM " << table_name << " ORDER BY _i_time DESC LIMIT 0,1;";

    TSQLResult* sqlresult = GetServer()->Query(alt_command.str().c_str());
    
    if(!sqlresult) return result;
    
    TSQLRow* row = sqlresult->Next();
    if(row){

      unsigned int colnum = (unsigned int)sqlresult->GetFieldCount();
      
      if(colnum != table_fields.size()){
        COMETError("ISlowControlMySQLInterface::QueryGetFieldNoCache: "
                   << "input fields doesn't match database result.");
        return result;
      }
    
      for(unsigned int counter=0; counter < colnum; counter++){
        double value = atof(row->GetField(counter));
        result.push_back(value);
      }
    
      delete row;
    }
    
    // Close database connection, if this is requested behaviour.
    if(fFlagAlwaysDisconnect)
      GetServer()->Close();

    // Delete the ROOT SQL result
    delete sqlresult;
    
    return result;

}