#ifndef DBQUERYCREATOR_H
#define DBQUERYCREATOR_H

#include <string>
#include <fstream>
#include <math.h>
#include <iostream>
#include <sstream>
#include <algorithm>
#include <functional>
#include <cctype>

#include "../Config/FecConfig.h"
#include "../Config/CruConfig.h"
#include "FERODBFieldPositions.h"

using namespace std;

class DBQueryCreator{

 public:

  DBQueryCreator();
  ~DBQueryCreator();

  ///////////////////////////////////////////////////////////////////////////
  // 1) Create Querys
  ///////////////////////////////////////////////////////////////////////////

  string getSqlCreateTable(string tablename);

  string getSqlCreateTableITCORR1();
  string getSqlCreateTableITCORR2();
  string getSqlCreateTableCMCORR();
  string getSqlCreateTableONCHANNEL();
  string getSqlCreateTableZSTHR();
  string getSqlCreateTablePEDESTAL();
  string getSqlCreateTableCRU();
  string getSqlCreateTableFEC();
  string getSqlCreateTableGBTX1();
  string getSqlCreateTablePARTITION();
  string getSqlCreateTableSECTOR();
  string getSqlCreateTableTPC();

  string getSqlCreateTableITCORR1_CRU();
  string getSqlCreateTableITCORR2_CRU();
  string getSqlCreateTableCMCORR_CRU();
  string getSqlCreateTableONCHANNEL_CRU();
  string getSqlCreateTableZSTHR_CRU();
  string getSqlCreateTablePEDESTAL_CRU();
  string getSqlCreateTableCRU_PARTITION();
  string getSqlCreateTableFEC_PARTITION();
  string getSqlCreateTablePARTITION_SECTOR();
  string getSqlCreateTableSECTOR_TPC();
  

  ///////////////////////////////////////////////////////////////////////////
  // 2) Drop Querys
  ///////////////////////////////////////////////////////////////////////////

  string getSqlDropTable(string tablename);
  string getSqlDropView(string Name);

  ///////////////////////////////////////////////////////////////////////////
  // 3) Insert Querys
  ///////////////////////////////////////////////////////////////////////////

  string getSqlInsertGBTX1();   // insert from file

  string getSqlInsertITCORR1(int32_t, int32_t, int32_t);
  string getSqlInsertITCORR2(int32_t, int32_t, int32_t);
  string getSqlInsertCMCORR(int32_t, int32_t, int32_t);
  string getSqlInsertONCHANNEL(int32_t, int32_t, int32_t);
  string getSqlInsertZSTHR(int32_t, int32_t, int32_t);
  string getSqlInsertPEDESTAL(int32_t, int32_t, int32_t);

  string getSqlInsertCRU(int32_t, int32_t, int32_t,
			 int32_t, int32_t, int32_t);
  string getSqlInsertFEC(int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlInsertPARTITION(int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlInsertSECTOR(int32_t, int32_t, int32_t);
  string getSqlInsertTPC(int32_t);
  
  string getSqlInsertITCORR1_CRU(int32_t, int32_t);
  string getSqlInsertITCORR2_CRU(int32_t, int32_t);
  string getSqlInsertCMCORR_CRU(int32_t, int32_t);
  string getSqlInsertONCHANNEL_CRU(int32_t, int32_t);
  string getSqlInsertZSTHR_CRU(int32_t, int32_t);
  string getSqlInsertPEDESTAL_CRU(int32_t, int32_t);
  string getSqlInsertCRU_PARTITION(int32_t, int32_t);
  string getSqlInsertFEC_PARTITION(int32_t, int32_t);
  string getSqlInsertPARTITION_SECTOR(int32_t, int32_t);
  string getSqlInsertSECTOR_TPC(int32_t, int32_t);

  // Insert statements using bind variables (better for many consecutive transactions)
  string getSqlBindInsert(string, int32_t);
  //
  string getSqlBindInsertITCORR2();
  string getSqlBindInsertITCORR1();
  string getSqlBindInsertCMCORR();
  string getSqlBindInsertONCHANNEL();
  string getSqlBindInsertZSTHR();
  string getSqlBindInsertPEDESTAL();
  string getSqlBindInsertCRU();
  //
  string getSqlBindInsertFEC();
  string getSqlBindInsertPARTITION();
  string getSqlBindInsertSECTOR();
  string getSqlBindInsertTPC();
  //
  string getSqlBindInsertITCORR2_CRU();
  string getSqlBindInsertITCORR1_CRU();
  string getSqlBindInsertCMCORR_CRU();
  string getSqlBindInsertONCHANNEL_CRU();
  string getSqlBindInsertZSTHR_CRU();
  string getSqlBindInsertPEDESTAL_CRU();
  string getSqlBindInsertCRU_PARTITION();
  //
  string getSqlBindInsertFEC_PARTITION();
  string getSqlBindInsertPARTITION_SECTOR();
  string getSqlBindInsertSECTOR_TPC();

  ///////////////////////////////////////////////////////////////////////////
  // 4) Select Querys
  ///////////////////////////////////////////////////////////////////////////

  // TPC table
  string getSqlSelectTpcData();
  string getSqlSelectTpcRow(int32_t);
  string getSqlSelectTpcField(int32_t, string);

  // Paerition table
  string getSqlSelectPartitionRow(int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectPartitionField(int32_t, int32_t, int32_t, int32_t, string);

  // FEC table
  string getSqlSelectFecRow(int32_t, int32_t, int32_t);
  string getSqlSelectFecRow(int32_t, int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectFecConfigData(int32_t);
  string getSqlSelectFecConfigData(int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectFecField(int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, string);

  // CRU table
  string getSqlSelectCruRow(int32_t, int32_t);
  string getSqlSelectCruRow(int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectCruConfigData(int32_t);
  string getSqlSelectCruConfigData(int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectCruField(int32_t, int32_t, int32_t, int32_t, int32_t, string);
  
  // UL tables
  string getSqlSelectUlField(string, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, string);
  string getSqlSelectUlRow(string, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectUlRow(string, int32_t, int32_t, int32_t);
  string getSqlSelectUlConfigData(string, int32_t);
  string getSqlSelectUlConfigData(string, int32_t, int32_t, int32_t, int32_t, int32_t);

  // More tables
  string getSqlSelectGbtx1ConfigData();

  string getSqlSelectCruID(int32_t, int32_t, int32_t, int32_t, int32_t);
  string getSqlSelectPartitionID(int32_t, int32_t, int32_t, int32_t);

  // Tpc queries using bind variables
  string getSqlBindSelectPartitionData();
  string getSqlBindSelectPartitionConfigData();
  string getSqlBindSelectSidePartitionConfigData();
  string getSqlBindSelectAllPartitionConfigData();

  ////////////////////////////////////////////////////////////////////////////

  // More:  

  string getSqlMaxId(string table);

  string getSqlFindDisabledPARTITIONs(int32_t);
  string getSqlFindDisabledFECs(int32_t);
  string getSqlFindDisabledCHANNELs(int32_t);
 
  ///////////////////////////////////////////////////////////////////////////
  // 5) Delete Querys
  ///////////////////////////////////////////////////////////////////////////
  
  // Delete a table
  string getSqlDeleteTPC(int32_t tpcID);
  string getSqlDeleteTPCID(int32_t tpcID);
  string getSqlDeleteSECTOR(int32_t *sectorIDs);
  string getSqlDeleteSECTORID(int32_t *sectorIDs);
  string getSqlDeletePARTITION(int32_t *partitionIDs);
  string getSqlDeletePARTITIONID(int32_t *partitionIDs);
  string getSqlDeleteFEC(int32_t *fecIDs);
  string getSqlDeleteFECID(int32_t *fecIDs);
  
  // Delete all entries in a table
  string getSqlDeleteTable(string tablename);
  
  ///////////////////////////////////////////////////////////////////////////
  // 6) Update Querys
  ///////////////////////////////////////////////////////////////////////////
  
  // Update Fields:
  string getSqlUpdateUpdated(int32_t);
  string getSqlUpdateUpdated(vector<int>);
  //
  string getSqlUpdateTpcField(int32_t, string, int32_t);
  string getSqlUpdateTpcField(int32_t, string, string);
  string getSqlUpdateTpcField(vector<int>, string, int32_t);
  string getSqlUpdateTpcField(vector<int>, string, string);
  //
  string getSqlUpdatePartitionField(int32_t, int32_t, int32_t, int32_t, string, int32_t);
  string getSqlUpdatePartitionField(int32_t, string, int32_t);
  //
  string getSqlUpdateFecField(int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, string, int32_t);
  string getSqlUpdateFecField(int32_t, string, int32_t);
  //
  string getSqlUpdateUlField(string, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, string, int32_t, float);
  string getSqlUpdateUlField(string, int32_t, string, int32_t, float);
  //
  string getSqlUpdateFieldAllUl(string, int32_t, string, int32_t, float);
  string getSqlUpdateFieldAllFec(int32_t, string, int32_t);
  string getSqlUpdateFieldAllCru(int32_t, string, int32_t, float);
  string getSqlUpdateFieldAllPartitions(int32_t, string, int32_t);
  //
  string getSqlUpdateFieldAllUl(string, vector<int>, string, int32_t, float);
  string getSqlUpdateFieldAllFec(vector<int>, string, int32_t);
  string getSqlUpdateFieldAllCru(vector<int>, string, int32_t, float);
  string getSqlUpdateFieldAllPartitions(vector<int>, string, int32_t);

  // One update query instead of many consecutive transactions
  string getSqlUpdatePartitionFieldVectors(vector<int>, vector<int>, vector<int>, vector<int>, string, int32_t);
  string getSqlUpdatePartitionFieldVectors(vector<int>IDs, string, int32_t);
  //
  string getSqlUpdateCruFieldVectors(vector<int>, vector<int>, vector<int>, vector<int>, vector<int>, string, int32_t, float);
  string getSqlUpdateCruFieldVectors(vector<int>, string, int32_t, float);
  //
  string getSqlUpdateFecFieldVectors(vector<int>, vector<int>, vector<int>, vector<int>, vector<int>, vector<int>,
				     string, int32_t);
  string getSqlUpdateFecFieldVectors(vector<int>, string, int32_t);
  //
  string getSqlUpdateUlFieldVectors(string, vector<int>, vector<int>, vector<int>, vector<int>, vector<int>, vector<int>,
				    vector<int>, string, int32_t, float);
  string getSqlUpdateUlFieldVectors(string, vector<int>, string, int32_t, float);
  //
  string getSqlUpdateUlRowInt(string, vector<int>, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, vector<int>);
  string getSqlUpdateUlRowFloat(string, vector<int>, int32_t, int32_t, int32_t, int32_t, int32_t, int32_t, vector<float>);
  //
  //string getSqlUpdateSampaFieldVectors(vector<int>, string, int32_t);

  // Update Fields using bind variables (better for many consecutive transactions)
  string getSqlBindUpdatePartitionField(string);
  string getSqlBindUpdatePartitionField(vector<int>, string);
  string getSqlBindUpdateCruField(string);
  string getSqlBindUpdateCruField(vector<int>, string);
  string getSqlBindUpdateFecField(string);
  string getSqlBindUpdateFecField(vector<int>, string);
  string getSqlBindUpdateUlField(string, string);
  string getSqlBindUpdateUlField(string, vector<int>, string);
  string getSqlBindUpdateUlField(string, vector<int>);
  //
  string getSqlBindUpdateUlRow(string);
  string getSqlBindUpdateUlRow(string, vector<int>);
  //

  ///////////////////////////////////////////////////////////////////////////
  // 7) More Functions
  ///////////////////////////////////////////////////////////////////////////

  string getSqlQueryTableExists(string);  // does this table exist?
  string getSqlQueryTableEmpty(string);   // is table empty?
  string getSqlQueryInfoDisabledFECs(int32_t); // which FECs are disabled for this config?

  string getSqlQueryShowTables();              // Show all user tables in DB
 
 private:

  string GetSqlDBNumberString(int32_t, int32_t);
  string GetSqlCreateTableStart(string);
  string GetSqlCreateTableEnd();
  string GetSqlCreateVar(string, int32_t, int32_t, string, bool);
  string GetSqlCreateDateTime(string, string, bool);
  string GetSqlCreateText(string, bool);
  string GetSqlCreateBlob(string, string, bool);
  string GetSqlCreateConstraint(string, string, string, string, bool);
  string GetSqlCreateDefineKey(string, bool);
  
  string GetSqlInsertRandomValue(int32_t, bool);
  
  string getSqlSelectInArray(int32_t*, int32_t);
    
  string getSqlDelete(string, int32_t*, int32_t);
  string getSqlDelete(string, string, int32_t*, int32_t);
  string getSqlDeleteInArray(int32_t*, int32_t);
  
  string numtostr(int);
  string floattostr(float);
  
  int32_t fDBProduct;
  int32_t fFecPerPartitionBranch[6][2];
  int32_t fRevision ;
  
};

#endif  // DBQUERYCREATOR_H