// =============================================================================
string numtobindstr(int32_t number) {
  //
  // Convert an integer to a string
  //
  ostringstream oss;
  oss << number;
  return ":" + oss.str();
}

// =============================================================================
bool contains(int value, vector<int> list) {
  //
  // test if the given integer <value> is contained in the given vector<int>
  //
  bool contains = false;
  for ( uint32_t i=0; i<list.size(); i++ )
    if ( list.at(i) == value ) { contains = true; break; }
  return contains;
}


// =============================================================================
// =============================================================================
int updateULfromfile(string tablename) {
  //
  // Update values in the given UL table from a file
  //

  if ( arguments.pTest == true ) {

    ofstream *seperateOutFile = new ofstream();
    seperateOutFile->open("UpdateFromFileUL.sql");
    *seperateOutFile << dbq->getSqlBindUpdateUlRow(tablename, arguments.pSelectTagList) << endl;
    seperateOutFile->close();
    delete seperateOutFile;
    return 1;
    
  }

  bool tableOfFloats = false;
  if ( tablename.compare("CMCORR")  == 0 ) tableOfFloats = true;
  if ( tablename.compare("ITCORR1") == 0 ) tableOfFloats = true;
  if ( tablename.compare("ITCORR2") == 0 ) tableOfFloats = true;
  if ( tablename.compare("cmcorr")  == 0 ) tableOfFloats = true;
  if ( tablename.compare("itcorr1") == 0 ) tableOfFloats = true;
  if ( tablename.compare("itcorr2") == 0 ) tableOfFloats = true;

  int32_t side=0, sector=0, partition=0, cru=0, ep=0, link=0;
  string sline, sdata;

  int32_t linectr = 0;

  vector < vector<int32_t> > vValsI;
  vValsI = vector < vector<int32_t> >(80);
  vector < vector<float> > vValsF;
  vValsF = vector < vector<float> >(80);
  vector<int> vSide, vSector, vPartition, vCru, vBranch, vFec;

  // Filter for a certain position.
  // If no coordinates specified, update all positions that are found in the file
  if ( arguments.pSelectSideList.size() == 0 )
    for (int i=0; i<2; i++) arguments.pSelectSideList.push_back(i);
  if ( arguments.pSelectSectorList.size() == 0 )
    for (int i=0; i<18; i++) arguments.pSelectSectorList.push_back(i);
  if ( arguments.pSelectPartitionList.size() == 0 )
    for (int i=0; i<6; i++) arguments.pSelectPartitionList.push_back(i);
  if ( arguments.pSelectCruList.size() == 0 )
    for (int i=0; i<2; i++) arguments.pSelectCruList.push_back(i);
  if ( arguments.pSelectBranchList.size() == 0 )
    for (int i=0; i<2; i++) arguments.pSelectBranchList.push_back(i);
  if ( arguments.pSelectFecList.size() == 0 )
    for (int i=0; i<13; i++) arguments.pSelectFecList.push_back(i);

  sw->start();

  for ( uint32_t ifile = 0; ifile < arguments.pFileNameList.size(); ifile++ ) {

    // open file
    ifstream ifs(arguments.pFileNameList.at(ifile).c_str(), ifstream::in);
    
    if ( !ifs.good() ) {
      cerr << endl << "*File " << arguments.pFileNameList.at(ifile).c_str() << " can not be opened!" << endl;
      continue;
    }
    
    if (ifs.is_open()) {
    
      // Loop over file content
      while ( getline(ifs, sline) ) {  // Get each line
      
	stringstream sdata(sline);
	string ss;
	int ctr = 0;
	getline(sdata, ss, ' ');  // get first value: CRU number (0..359)
	cru = atoi(ss.c_str());
	if ( (cru < 0) || (cru>=360) ) {
	  cerr << "*Error in function updateULfromfile(). Strange CRU number (0..359): " << cru << endl;
	  return 0;
	}
	side = (cru>=180) ? 1 : 0;
	sector = cru%180/10;
	partition = cru%10/2;
	cru = cru%10%2;

	getline(sdata, ss, ' '); // get second value: link number (0..9, 12..21)
	link = atoi(ss.c_str());
	ep = link/12;
	if ( (link < 0) || (link>=24) ) {
	  cerr << "*Error in function updateULfromfile(). Strange link number (0..23): " << link << endl;
	  return 0;
	}
	link = link%12;

	/*
	cout << "Side " << side << ", sector " << sector << ", partition " << partition << ", cru " << cru
	     << ", EP " << ep << ", link " << link << endl;
	*/
	
	bool updatethisline = false;
	
	if ( contains(side, arguments.pSelectSideList) && contains(sector, arguments.pSelectSectorList) &&
	     contains(partition, arguments.pSelectPartitionList) && contains(cru, arguments.pSelectCruList) &&
	     contains(ep, arguments.pSelectBranchList) && contains(link, arguments.pSelectFecList) ) {	
	  
	  updatethisline = true;
	  
	  vSide.push_back(side);
	  vSector.push_back(sector);
	  vPartition.push_back(partition);
	  vCru.push_back(cru);
	  vBranch.push_back(ep);
	  vFec.push_back(link);
	}
	
	while ( getline(sdata, ss, ',') ) {
	  if ( updatethisline ) {
	    // Change ZSTHR from 12 bit to 8 bit in 2023. Max threshold is 256.
	    if ( (tablename.compare("ZSTHR") == 0) || (tablename.compare("zsthr") == 0) ) {
	      int tval = atoi(ss.c_str());
	      if (tval > 0xff) tval = 0xff;
	      vValsI[ctr].push_back(tval);
	    } else if (tableOfFloats) vValsF[ctr].push_back(atof(ss.c_str()));
	    else                      vValsI[ctr].push_back(atoi(ss.c_str()));
	    
	  }
	  //cout << valFloat[ctr] << " ";
	  ctr++;
	}
	//cout << endl;
	
	if ( updatethisline ) linectr++;

      } // end loop through lines in file

      ifs.close();

    } // end if file open
    
  } // end file loop
  /*
  for (int i=0; i<linectr; i++) {
    cout << "Side " << vSide[i] << ", sector " << vSector[i] << ", partition " << vPartition[i] << ", cru " << vCru[i]
	 << ", EP " << vBranch[i] << ", link " << vFec[i] << " :: " << vValsI[0][i] << ", " << vValsI[1][i] << endl;
  }
  */  
  if ( vSide.size()>0 ) {
  
    // The Update
    try {
      Statement st(con);
      cout << "Updating " << tablename << " ... please wait ... " << flush;
      st.Prepare(dbq->getSqlBindUpdateUlRow(tablename, arguments.pSelectTagList));
      st.SetBindArraySize(linectr);
      
      // bind variables:
      // 1-80: new values
      // 81: side
      // 82: sector
      // 83: partition
      // 84: cru
      // 85: fec branch
      // 86: fec

      for (uint32_t col = 0; col < 80; col++) {
	if (tableOfFloats) st.Bind(numtobindstr(col+1), vValsF[col], BindInfo::In);
	else               st.Bind(numtobindstr(col+1), vValsI[col], BindInfo::In);
      }
      st.Bind(":81", vSide, BindInfo::In);
      st.Bind(":82", vSector, BindInfo::In);
      st.Bind(":83", vPartition, BindInfo::In);
      st.Bind(":84", vCru, BindInfo::In);
      st.Bind(":85", vBranch, BindInfo::In);
      st.Bind(":86", vFec, BindInfo::In);
      
      st.ExecutePrepared();
      cout << "Done, " << st.GetAffectedRows() << " rows processed." << endl;
    
      con.Commit();
    
      st.Execute(dbq->getSqlUpdateUpdated(arguments.pSelectTagList));   // Update these tags as updated

    } catch (exception &ex) {
      cout << ex.what() << endl;
    }
  }
  
  sw->stop();
  cout << "Finished with Update | " << sw->formatRealTime() << " s, " << sw->formatSysTime() << "s" << endl;

  return 1;
}