// insert_metadata_version.C
// This file is used to update the metadata table. It looks through every system tests
// histogram file for a particular version. For every histogram in that file the number 
// of entries, the mean, the rms value and the Ks value(compared with the default standard) 
// is calculated and stored in the metadata table 


#include <TSQLServer.h>
#include <TSQLRow.h>
#include <TSQLResult.h> 
#include <TFrame.h>
#include <TH1.h>
#include <string.h>
#include <iostream.h>
#include <stdio.h>
#include <TString.h>

void insert_metadata_version_sequence(char *version){

  cout<<"Version:"<<version<<endl;
  
  // Used in the for loop 
  const int intENTRIES = 0;
  const int intMEAN = 1;
  const int intRMS = 2;
  const int intKS = 3;

  // These are the constants for the label in the info_labels table
  char *METADATA[4];
  METADATA[intENTRIES] = "ENTRIES";
  METADATA[intMEAN] = "MEAN";
  METADATA[intRMS] = "RMS"; 
  METADATA[intKS] = "KS";
  
  // Set up the structure to hold the systest_id, histogram file, standardtest_id and
  // the histogram file name of the standard test
  struct Aaa{
    int systest_id;
    char hist_filename[256];
    int stdtest_id;
    char std_hist_filename[256];
    char test_result[64];
 };

  struct Aaa *hist;
    
  // To get the histogram file and the systest_id
  char* sql12="select systest_id, histogram_file, standardtest_id, test_result from system_tests where version =\'%s\'";
  char sql11[4096];
  std::sprintf(sql11, sql12, version);
  TString sql1 = sql11;
  cout<<sql1.Data()<<endl;

  // This connects to the database
  char *connection="oracle://sage/SLAC_TCP";
  char *user="glast_data";
  char *pass="flight06";

  // Connect to oracle server
  TSQLServer *db = TSQLServer::Connect(connection, user, pass);
  if(db == NULL) {
    cout<<"Error! No database connection."<<endl;
    return;
  }

  // Get the number of systest_id histogram_file pairs to determine size of array
  char *countsql_temp = "select count(systest_id) from system_tests where version = \'%s\'";
  char countsql[4096];
  sprintf(countsql, countsql_temp, version);
  TSQLResult *s = db->Query(countsql);    
  if(s == NULL){
    cout<<"Error! No systems tests for this version"<<endl;
    return;
  }

  TSQLRow *rows = s->Next();
  if(rows == NULL){
    cout<<"Error! No value returned for number of system_tests"<<endl;
    return;
  }

  int num_systests = std::atoi(rows->GetField(0));  
  delete s;
  delete rows;
  
  // assign array size
  hist = (struct Aaa *)malloc(num_systests*sizeof(struct Aaa));
  
  
  // To get systest_id and histogram_file
  TSQLResult *res=db->Query(sql1);   
  if(res==NULL){
    cout<<"Error! No value returned for systest_id and histogram_file"<<endl;
    return;
  }
  TSQLRow *row1=res->Next();  
  if(row1==NULL){
    cout<<"Error! No rows returned for systest_id and histogram_file"<<endl;
    return;
  }
    

  // Try and stick the systest_id, the histogram file and the standardtest_id 
  // into an array of structures
  int i = 0;
  do{
    TString systestid = row1->GetField(0);
    TString hist_file = row1->GetField(1);
    TString stdtestid = row1->GetField(2);
    TString test_result = row1->GetField(3);
    hist[i].systest_id = std::atoi(systestid.Data());
    hist[i].stdtest_id = std::atoi(stdtestid.Data());
    TString hf = hist_file.Strip();
    TString tr = test_result.Strip();
    sprintf(hist[i].hist_filename,hf.Data());
    sprintf(hist[i].test_result,tr.Data());
    i++;

  }while(res->Next());

  // Here we query the database again to get the name of the standard
  // histogram file for each of the system tests

  const char *tempsql2 = "select histogram_file from system_tests syst, standard_tests stdt where syst.systest_id = stdt.systest_id and stdt.standardtest_id = %d";
  

  for(i=0; i<num_systests; i++){
    cout<<i<<" "<<hist[i].stdtest_id<<endl;
    char sql[4096];
    std::sprintf(sql, tempsql2, hist[i].stdtest_id);
    TSQLResult *res = db->Query(sql);
    if(res==NULL){
      cout<<"Error! No value returned standard histogram file name"<<endl;
      return;
    }

    TSQLRow *row1=res->Next();    
    if(row1==NULL){
      cout<<"Error! No rows returned for standard histogram_file"<<endl;
      return;
    }

    TString stdhistfile = row1->GetField(0);
    TString shf = stdhistfile.Strip();
    std::sprintf(hist[i].std_hist_filename,shf.Data());
    std::strcpy(sql, "");
    delete res;
    delete row1;
  }// end for i

  
  

  for(i=0; i<num_systests; i++){
    // The histogram file for each of the system tests
    // The standard file is opened first and the original file second so that the current file
    // would be the original one
    if(!strcmp(hist[i].hist_filename,"NA")) continue;
    if(!strcmp(hist[i].test_result,"Failed")) continue;
    
    TFile *std_histfile = new TFile(hist[i].std_hist_filename);
    if(std_histfile==NULL){
      cout<<"Error! The standard file does not point to anything"<<endl;
      return;
    }
    TFile *histfile = new TFile(hist[i].hist_filename);
    if(std_histfile==NULL){
      cout<<"Error! The original file does not point to anything"<<endl;
      return;
    }

    TList *pKeyList = gDirectory->GetListOfKeys();
    if(pKeyList==NULL){
      cout<<"Error! Not able to get keys"<<endl;
      return;
    }

    TObject *pObj; 
    TKey *pKey ; 
    TIter it(pKeyList); 
    
    while (pKey = (TKey*)it()) { 
      TString pClassName(pKey->GetClassName());
       if(pClassName.BeginsWith("TH1") ||    // Cheap way of 
	  pClassName.BeginsWith("TH2") ||    // not reading objects 
	  pClassName.BeginsWith("TH3"))      // of types other than histos 
	 
	 {  
 	  pObj = pKey->ReadObj();
	  if(pObj == NULL){
	    cout<<"Error! The object read was NULL"<<endl;
	    return;
	  }

 	  if (pObj->InheritsFrom("TH1")){
	      
	    // Get mean, # of entries and RMS value for histograms
	    double entries = ((TH1*)pObj)->GetEntries();
	    double mean = ((TH1*)pObj)->GetMean();
	    double rms = ((TH1*)pObj)->GetRMS();
	    char str_metadata[4][100];

	    // Get the name of the histogram for performing KS test
	    char *histoname  =((TH1*)pObj)->GetName();
	    if(histoname == NULL){
	      cout<<"Error! The object's name was NULL"<<endl;
	      return;
	    }
  
	    TH1 *hpx = (TH1 *)histfile->Get(histoname);
	    if(hpx == NULL){
	      cout<<"Error! The histogram from the file was NULL"<<endl;
	      return;
	    }

	    double ks = hpx->KolmogorovTest((TH1 *)std_histfile->Get(histoname), "UO");
	      
	    // This hold the info_value which has to be inserted in the metadata table
	    std::sprintf(str_metadata[intENTRIES], "%f", entries);
	    std::sprintf(str_metadata[intMEAN], "%f", mean);
	    std::sprintf(str_metadata[intRMS], "%f", rms);
	    std::sprintf(str_metadata[intKS], "%f", ks);
	    
	    
	    // Before inserting anything check if that systest_id and 
	    // info exists in the metadata table. If it does then 
	    // we simply do an update statement to put in new values.
	    
	    // If it doesn't exist then check if that label(CALDIGI_MEAN)
	    // has an entry in the info_labels table. If yes then we simply
	    // get the info value and do an insert in the metadata table
	    // with that info value.
	    
	    // If it doesn't exist then create a new unique info for that
	    // label and insert into metadata table
	    
	    // This loop goes through all the metadata values
	    for(int j=0; j<4; j++){
	      
	      // Step 1: Check if for a particular systest_id and label.
	      // If rows exist in the database. We need to access the 
	      // info_labels table as the label exists there
	      
	      const char *info_query_temp = "select count(meta_id) from metadata m, info_labels i "
		"where m.systest_id = %d and m.info=i.info and i.label = %s";
	      
	      // Generate the label
	      char *histo_name = ((TH1*)pObj)->GetName();
	      if(histo_name == NULL){
		cout<<"Error! The histogram name read was NULL"<<endl;
		return;
	      }
	      
	      const char *label = "\'%s_%s\'";
	      //char info_label_metadata[50];
	      char *info_label_metadata;
	      info_label_metadata = new char[50];
	      std::sprintf(info_label_metadata, label, histo_name, METADATA[j]);
	      	      
	      // Generate the query to check if a particular info, systest_id pair exists 
	      char *info_query_metadata = new char[4096];
	      std::sprintf(info_query_metadata, info_query_temp, hist[i].systest_id, info_label_metadata);
		
	      // To actually query the database
	      TSQLResult *exists_in_meta_res = db->Query(info_query_metadata);    
	      if(exists_in_meta_res == NULL){
		cout<<"Error! The metadata query did not return any value"<<endl;
		return;
	      }
	      
	      TSQLRow *exists_in_meta_row = exists_in_meta_res->Next();
	      if(exists_in_meta_row == NULL){
		cout<<"The metadata query did not return any rows"<<endl;
		return;
	      }
	      
	      int exists_in_meta = std::atoi(exists_in_meta_row->GetField(0));
	      delete exists_in_meta_res;
	      delete exists_in_meta_row;
	      
	      // Before we update we need to know the info for that particular label
	      // This is the update part when the info for that systest_id already
	      // exists in the database
	      
	      // To update the row in the metadata table
	      if(exists_in_meta > 0){
		
		const char *q_for_info_temp = "select info from info_labels where label = %s";
		char *q_for_info;
		q_for_info = new char[4096];
		std::sprintf(q_for_info, q_for_info_temp, info_label_metadata);
		TSQLResult *info_res = db->Query(q_for_info);    
		if(info_res == NULL){
		  cout<<"Error! There was no such label in the info labels table"<<endl;
		  return;
		}
		
		TSQLRow *info_row = info_res->Next();
		if(info_row == NULL){
		  cout<<"Error! The histogram name read was NULL"<<endl;
		}
		
		int info_metadata = std::atoi(info_row->GetField(0));
				
		// Now if the systest_id, info pair exists in the metadata table then
		// we simply update the values of that entry with our new values
		
		const char *update_metadata_temp = "update metadata set info_value = %s where "
		  "systest_id = %d and info = %d";
		char *update_metadata = new char[4096];
		std::sprintf(update_metadata, update_metadata_temp, str_metadata[j], hist[i].systest_id, info_metadata);
		TSQLResult *update_res = db->Query(update_metadata);    
		
		
		TSQLResult *com = db->Query("commit");
		
		delete update_res;
		delete com;
		delete[] q_for_info;
		delete[] update_metadata;
 		}// end if exists_in_meta (we just did an update)
	      else{
		
		// We first check to see if the info and label pair exists in the  info_labels table
		// If it does we get that info else we create a new entry in the table
		
		const char *q_for_infolabel_pair_temp = "select count(info) from info_labels where label = %s";
		char *q_for_infolabel_pair;
		q_for_infolabel_pair = new char[4096];
		std::sprintf(q_for_infolabel_pair, q_for_infolabel_pair_temp, info_label_metadata);
		TSQLResult *exists_in_infolabel_res = db->Query(q_for_infolabel_pair);    
		if(exists_in_infolabel_res == NULL){
		  cout<<"Error! The infolabel table read returned NULL"<<endl;
		  return;
		}

		TSQLRow *exists_in_infolabel_row = exists_in_infolabel_res->Next();
		if(exists_in_infolabel_row == NULL){
		  cout<<"Error! No rows returned from info labels"<<endl;
		  return;
		  }
		int exists_in_infolabel = std::atoi(exists_in_infolabel_row->GetField(0));
		
		
		// There is no  entry in info_label we have to create a new one
		if(exists_in_infolabel == 0){
		  //------------------------------
		  TSQLResult *max_info_res = db->Query("select max(info) from info_labels");    
		  if(max_info_res == NULL){
		    cout<<"Error! Max info not returned"<<endl;
		    return;
		  }
		  
		  TSQLRow *max_info_row = max_info_res->Next();
		  if(max_info_row == NULL){
		    cout<<"Error! No rows returned from max info query"<<endl;
		    return;
		  }
		  
		  int max_info = std::atoi(max_info_row->GetField(0));
		  
		  // Choose the next value of info
		  max_info++;
		  //-------------------------------
		  const char *insert_infolabel_temp = "insert into info_labels(info, label) values(info_seq.nextval, %s)";
		  char *insert_infolabel;
		  insert_infolabel = new char[4096];
		  std::sprintf(insert_infolabel, insert_infolabel_temp, info_label_metadata);
		  
		  TSQLResult *insert_infolabel_res = db->Query(insert_infolabel);    
		  // cout << "System Test id = " << hist[i].systest_id << endl;
		  if(insert_infolabel_res == NULL){
		    cout<<"Error! Error insertin into the info labels table"<<endl;
		    return
		  }
		  
		  TSQLResult *com = db->Query("commit");
		  if(com == NULL){
		    cout<<"Error! Could not commit"<<endl;
		    return;
		  }
		  
		  delete insert_infolabel_res;
		  delete com;
		  delete[] insert_infolabel;
		}// end if exists in infolabel == 0
		
		// If the info-label pair existed in info_labels we don't do anything
		// At this point we have a valid entry in info_labels for our label
		
		// Now we can insert into the metadata table
		const char *q_for_info_temp = "select info from info_labels where label = %s";
		//char q_for_info[4096];
		char *q_for_info = new char[4096];
		std::sprintf(q_for_info, q_for_info_temp, info_label_metadata);
		TSQLResult *info_res = db->Query(q_for_info);    
		if(info_res == NULL){
		  cout<<"Error! The info for the corresponding label was not found"<<endl;
		  return;
		}
		
		TSQLRow *info_row = info_res->Next();
		if(info_row == NULL){
		  cout<<"Error! No rows for the info query was returned"<<endl;
		  return;
		}
		
		int info = std::atoi(info_row->GetField(0));
		//----------------------
		// We have got the info value
		// Increment the meta_id
		//meta_id++;
		// -------------------
		const char *insert_meta_temp = "insert into metadata(meta_id, systest_id, info,"
		  "info_value) values(meta_seq.nextval, %d, %d, %s)";
		char *insert_meta = new char[4096];
		std::sprintf(insert_meta, insert_meta_temp, hist[i].systest_id, info, str_metadata[j]);
		
		
		TSQLResult *insert_meta_res = db->Query(insert_meta);
		TSQLResult *com = db->Query("commit");
		
		delete insert_meta_res;
		delete com; 
		delete[] q_for_infolabel_pair;
		delete[] q_for_info;
		delete[] insert_meta;
	      }// end else 
	      
	      delete[] info_label_metadata;
	      delete[] info_query_metadata;
	      
	    }// end for j
	    
	  }// end if object is histogram
	 }// end if 

       
    }// end while pkey..
    
    
    std_histfile->Close();
    histfile->Close();
  }// end for (which loops through every systest_id and histogram_file)
  
}// end of insert_metadata_version_sequence