#ifndef DBQUERYCREATOR_H
#define DBQUERYCREATOR_H

#include <string>
#include <fstream>
#include <math.h>
#include <iostream>
#include <sstream>
#include "../Config/AltroConfig.h"
#include "../Config/FecConfig.h"
#include "../Config/RcuConfig.h"
#include "FERODBFieldPositions.h"

using namespace std;

class DBQueryCreator{

 public:

  DBQueryCreator();
  ~DBQueryCreator();

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

  string getSqlCreateTableALTRO();
  string getSqlCreateTableFEC();
  string getSqlCreateTableRCU();
  string getSqlCreateTableSECTOR();
  string getSqlCreateTableTPC();

  string getSqlCreateTableALTRO_FEC();
  string getSqlCreateTableFEC_RCU();
  string getSqlCreateTableRCU_SECTOR();
  string getSqlCreateTableSECTOR_TPC();
  
  string getSqlCreateTableFEE(string side);
  string getSqlCreateTableFEESERVICES(string side);

  string getSqlCreateTableFEE_A()         { return getSqlCreateTableFEE("A");         };
  string getSqlCreateTableFEESERVICES_A() { return getSqlCreateTableFEESERVICES("A"); };
  string getSqlCreateTableFEE_C()         { return getSqlCreateTableFEE("C");         };
  string getSqlCreateTableFEESERVICES_C() { return getSqlCreateTableFEESERVICES("C"); };

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

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

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

  string getSqlInsertALTRO_FEC(int32_t altroIndex, int32_t fecIndex);
  string getSqlInsertFEC_RCU(int32_t fecIndex, int32_t rcuIndex);
  string getSqlInsertRCU_SECTOR(int32_t rcuIndex, int32_t sectorIndex);
  string getSqlInsertSECTOR_TPC(int32_t sectorIndex, int32_t tpcIndex);
  string getSqlInsertTPC(int32_t index);
  string getSqlInsertTPC(int32_t index, string comment);
  string getSqlInsertSECTOR(int32_t index, int32_t position, int32_t sidePosition);
  string getSqlInsertRCU(int32_t index, int32_t globalIndex, int32_t sidePosition, int32_t sectorPosition,
			 int32_t position, int32_t configType);
  string getSqlInsertALTRO(int32_t index, int32_t sidePosition, int32_t sectorPosition, int32_t rcuPosition,
			   int32_t branchPosition, int32_t fecPosition, int32_t position, int32_t configType);
  string getSqlInsertFEC(int32_t index, int32_t sidePosition, int32_t sectorPosition, int32_t rcuPosition,
			 int32_t branchPosition, int32_t position, int32_t configType);

  string getSqlInsertFEE(int32_t sidePosition, int32_t sectorPosition, int32_t rcuPosition );

  string getSqlInsertFEESERVICES(int32_t sidePosition, int32_t sectorPosition, int32_t rcuPosition,
				 int32_t aliceFecPosition, string service, string datatype);

  string getSqlInsertFEESERVICES(int32_t sidePosition, int32_t sectorPosition, int32_t rcuPosition,
				 string state, string datatype); // for RCU services

  // Insert statements using bind variables (better for many consecutive transactions)
  string getSqlBindInsertALTRO();
  string getSqlBindInsertALTRO_FEC();
  string getSqlBindInsertFEC();
  string getSqlBindInsertFEC_RCU();
  string getSqlBindInsertRCU();
  string getSqlBindInsertRCU_SECTOR();

  
  ///////////////////////////////////////////////////////////////////////////
  // 4) Select Querys
  ///////////////////////////////////////////////////////////////////////////
  
  string getSqlSelectTpcData();

  // The TPC queries:
  string getSqlSelectPartitionData(int32_t configID, int32_t side, int32_t sector, int32_t rcu);
  string getSqlSelectPartitionConfigData(int32_t rcuID);
  string getSqlSelectSidePartitionConfigData(int32_t configID, int32_t side);  // get all data for this tag and side
  string getSqlSelectAllPartitionConfigData(int32_t configID);  // get all data for this tag

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

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

  // Select one row from table
  string getSqlSelectTpcRow(int32_t configID);

  string getSqlSelectRcuRow(int32_t configID, int32_t side, int32_t sector, int32_t rcu);
  string getSqlSelectRcuRow(int32_t rcuID);

  string getSqlSelectFecRow(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec);
  string getSqlSelectFecRow(int32_t fecID);

  string getSqlSelectAltroRow(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec,
			   int32_t altro);
  string getSqlSelectAltroRow(int32_t altroID);

  // Select one field from table
  string getSqlSelectTpcField(int32_t configID, string colName);

  string getSqlSelectRcuField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, string col);
  string getSqlSelectRcuField(int32_t rcuID, string col);

  string getSqlSelectFecField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec, string col);
  string getSqlSelectFecField(int32_t fecID, string col);

  string getSqlSelectAltroField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec, int32_t altro,
				string col);
  string getSqlSelectAltroField(int32_t altroID, string col);


  // More:  
  string getSqlMaxIdFEE_A()         { return getSqlMaxIdFEE("A");         };
  string getSqlMaxIdFEESERVICES_A() { return getSqlMaxIdFEESERVICES("A"); };
  string getSqlMaxIdFEE_C()         { return getSqlMaxIdFEE("C");         };
  string getSqlMaxIdFEESERVICES_C() { return getSqlMaxIdFEESERVICES("C"); };
  
  string getSqlMaxId(string table);
  string getSqlMaxIdFEE(string side);
  string getSqlMaxIdFEESERVICES(string side);

  string getSqlFindDisabledRCUs(int32_t configID);
  string getSqlFindDisabledFECs(int32_t configID);
  string getSqlFindDisabledALTROs(int32_t configID);
  string getSqlFindDisabledCHANNELs(int32_t configID);
 
  ///////////////////////////////////////////////////////////////////////////
  // 5) Delete Querys
  ///////////////////////////////////////////////////////////////////////////
  
  // Delete something
  string getSqlDeleteTPC(int32_t tpcID);
  string getSqlDeleteTPCID(int32_t tpcID);
  string getSqlDeleteSECTOR(int32_t *sectorIDs);
  string getSqlDeleteSECTORID(int32_t *sectorIDs);
  string getSqlDeleteRCU(int32_t *rcuIDs);
  string getSqlDeleteRCUID(int32_t *rcuIDs);
  string getSqlDeleteFEC(int32_t *fecIDs);
  string getSqlDeleteFECID(int32_t *fecIDs);
  string getSqlDeleteALTRO(int32_t *altroIDs);
  
  // Delete all entries in a table
  string getSqlDeleteTable(string tablename);
  
  ///////////////////////////////////////////////////////////////////////////
  // 6) Update Querys
  ///////////////////////////////////////////////////////////////////////////
  
  // Update Fields:
  string getSqlUpdateUpdated(int32_t configID);
  string getSqlUpdateUpdated(vector<int> configIDs);
  //
  string getSqlUpdateTpcField(int32_t configID, string col, int32_t newValue);
  string getSqlUpdateTpcField(int32_t configID, string col, string newString);
  string getSqlUpdateTpcField(vector<int> configIDs, string col, int32_t newValue);
  string getSqlUpdateTpcField(vector<int> configIDs, string col, string newString);
  //
  string getSqlUpdateRcuField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, string col, int32_t newValue);
  string getSqlUpdateRcuField(int32_t rcuID, string col, int32_t newValue);
  //
  string getSqlUpdateFecField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec,
			      string col, int32_t newValue);
  string getSqlUpdateFecField(int32_t fecID, string col, int32_t newValue);
  //
  string getSqlUpdateAltroField(int32_t configID, int32_t side, int32_t sector, int32_t rcu, int32_t branch, int32_t fec,
				int32_t altro, string col, int32_t newValue);
  string getSqlUpdateAltroField(int32_t altroID, string col, int32_t newValue);
  //
  string getSqlUpdateFieldAllAltro(int32_t configID, string col, int32_t newValue);
  string getSqlUpdateFieldAllFec(int32_t configID, string col, int32_t newValue);
  string getSqlUpdateFieldAllRcu(int32_t configID, string col, int32_t newValue);
  //
  string getSqlUpdateFieldAllAltro(vector<int> configIDs, string col, int32_t newValue);
  string getSqlUpdateFieldAllFec(vector<int> configIDs, string col, int32_t newValue);
  string getSqlUpdateFieldAllRcu(vector<int> configIDs, string col, int32_t newValue);

  // One update query instead of many consecutive transactions
  string getSqlUpdateRcuFields(vector<int> configIDs, vector<int> side, vector<int> sector,
			       vector<int> rcu, string col, int32_t newValue);
  string getSqlUpdateRcuFields(vector<int> rcuIDs, string col, int32_t newValue);
  //
  string getSqlUpdateFecFields(vector<int> configIDs, vector<int> side, vector<int> sector,
			       vector<int> rcu, vector<int> branch, vector<int> fec,
			       string col, int32_t newValue);
  string getSqlUpdateFecFields(vector<int> fecIDs, string col, int32_t newValue);
  //
  string getSqlUpdateAltroFields(vector<int> configIDs, vector<int> side, vector<int> sector,
				 vector<int> rcu, vector<int> branch, vector<int> fec,
				 vector<int> altro, string col, int32_t newValue);
  string getSqlUpdateAltroFields(vector<int> altroID, string col, int32_t newValue);

  // Update Fields using bind variables (better for many consecutive transactions)
  string getSqlBindUpdateAltroField(string col);
  string getSqlBindUpdateAltroFieldId(string col);// specify Altro by id
  string getSqlBindUpdateAltroField(vector<int> configIDs, string col);
  string getSqlBindUpdateAltro2Fields(vector<int> configIDs, string col0, string col1);
  string getSqlBindUpdateAltro3Fields(vector<int> configIDs, string col0, string col1, string col2);
  string getSqlBindUpdateAltroFieldAllSectors(vector<int> configIDs, string col);
  string getSqlBindUpdateRcuField(string col);
  string getSqlBindUpdateRcuFieldId(string col);  // specify Rcu by id
  string getSqlBindUpdateRcuField(vector<int> configIDs, string col);
  string getSqlBindUpdateFecField(string col);
  string getSqlBindUpdateFecFieldId(string col);  // specify Fec by id
  string getSqlBindUpdateFecField(vector<int> configIDs, string col);
  string getSqlBindUpdateChannelField(vector<int> configIDs, string col, int32_t newValue);
  string getSqlBindUpdateChannelFieldAllOnRcu(vector<int> configIDs, string col, int32_t newValue);


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

  string getSqlQueryTableExists(string tableName);  // does this table exist?
  string getSqlQueryTableEmpty(string tableName);   // is table empty?
  string getSqlQueryInfoDisabledFECs(int32_t configID); // which fecs are disabled for this config?
 
 private:
  
  string GetSqlDBIntegerString(int32_t length);
  string GetSqlCreateTable(string Name);
  string GetSqlCreateTableEnd();
  string GetSqlCreateVar(string Name, int32_t length, string parameters, bool seperator);
  string GetSqlCreateDateTime(string Name, string defaultVal, bool seperator);
  string GetSqlCreateText(string Name, bool seperator);
  string GetSqlCreateBlob(string ColumnName, string parameters, bool seperator);
  string GetSqlCreateConstraint(string KeyName, string LinkName, string LinkTableName,
				string LinkTableID, bool seperator);
  string GetSqlCreateDefineKey(string Name, bool seperator);
  
  string GetSqlInsertRandomValue(int32_t numofBits, bool seperator);
  
  string getSqlSelectInArray(int32_t *array, int32_t length);
    
  string getSqlDelete(string table, int32_t *IDs, int32_t length);
  string getSqlDelete(string table, string name, int32_t *IDs, int32_t length);
  string getSqlDeleteInArray(int32_t *array, int32_t length);
  
  string numtostr(uint32_t number);
  
  int32_t fDBProduct;
  int32_t fFecPerRCUBranch[6][2];
  int32_t fRevision ;
  
};

#endif  // DBQUERYCREATOR_H