//*-- AUTHOR : Ilse Koenig
//*-- Created : 25/05/2010 by Ilse Koenig

//_HADES_CLASS_DESCRIPTION 
/////////////////////////////////////////////////////////////
// HOra2Conn
//
// Connection class to database Oracle in Hydra2
// (uses the Oracle C/C++ precompiler)
//
/////////////////////////////////////////////////////////////
using namespace std;
#include "hora2conn.h"
#include "TRandom.h"
#include <stdio.h>
#include <iostream>
#include <iomanip>
#include <stddef.h>
#include <stdlib.h>
#include <string.h>
#include <termios.h>
#include <fcntl.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <unistd.h>

// Oracle communication area
#include <oraca.h>
// SQL Communications Area
#include <sqlca.h>
// SQL prototype routines
#include <sqlcpr.h> 

ClassImp(HOra2Conn)

HOra2Conn::HOra2Conn() {
  // default constructor
  // defines default values for user ("hades_ana") and the database
  //   ("db-hades", the HADES Oracle 8 database on Linux at GSI)
  // User "hades" has Readonly access to the Oracle tables.
  // The connection to Oracle is not opened!
  strcpy(dbName,"db-hades");
  strcpy(userName,"hades_ana");
  isConnected=kFALSE;
  actRunId=-1;
  runStart=-1;
  expLocation[0]='\0';
  historyDate[0]='\0';
  needsServerCheck=kTRUE;
}


HOra2Conn::~HOra2Conn() {
  // default destructor (closes connection)
  close();
}


Bool_t HOra2Conn::open() {
  // opens default connection with readonly access
  Char_t* password = new Char_t[9];
  strcpy(password,"hades");
  Bool_t rc=openConnection(password);
  if (!rc) close();
  return rc;
}

Bool_t HOra2Conn::open(Char_t *uName) {
  // opens connection to database Hades for user given by name
  // asks for password
  strncpy(userName,uName,20);
  Char_t* password=getPassword();
  Bool_t rc=openConnection(password);
  if (!rc) close();
  return rc;
}


Bool_t HOra2Conn::open(Char_t *dbN, Char_t *uN) {
  // opens connection to database with name dbName for user given by name
  // asks for password
  strncpy(dbName,dbN,30);
  strncpy(userName,uN,20);
  Char_t* password=getPassword();
  Bool_t rc=openConnection(password);
  if (!rc) close();
  return rc;
}


Bool_t HOra2Conn::reconnect() {
  // opens connection (contains the SQL-statements)
  if (isConnected) return kTRUE;
  if (strcmp(userName,"hades_ana")!=0) return kFALSE;
  Char_t connId[80];
  Char_t password[]={"hades"};
  strcpy(connId,userName);
  strcat(connId,"@");
  strcat(connId,dbName);    // e.g."hades_ana@db-hades"
  EXEC SQL BEGIN DECLARE SECTION;
    char* uName;
    char* pWord;
  EXEC SQL END DECLARE SECTION;
  uName=connId;
  pWord=password;
  EXEC SQL WHENEVER SQLERROR DO
    showSqlError("openConnection(Char_t*)","Wrong user/password");
  EXEC SQL CONNECT :uName IDENTIFIED BY :pWord;
  if (sqlca.sqlcode==0) {
    isConnected=kTRUE;
    setHistoryDate(historyDate);
  } else Error("reconnect()","*** Failed to connect to Oracle ***");
  return isConnected; 
}


void HOra2Conn::close() {
  // disconnects from ORACLE
  // A transaction will be automatically rolled back,
  // that means changes in the database are not stored
  // without an explicit COMMIT
  actRunId=-1;
  runStart=-1;
  expLocation[0]='\0';
  historyDate[0]='\0';
  EXEC SQL WHENEVER SQLERROR DO
    showSqlError("close()");
  if (isConnected) {
    EXEC SQL ROLLBACK RELEASE;
    isConnected=kFALSE;
    cout<<"connection to Oracle closed"<<endl;
  }
  needsServerCheck=kTRUE;
}


void HOra2Conn::disconnect() {
  // disconnects from ORACLE
  // may be opened again via reconnect()
  if (isConnected) {
    actRunId=-1;
    runStart=-1;
    EXEC SQL WHENEVER SQLERROR DO
      showSqlError("disconnect()");
    EXEC SQL ROLLBACK RELEASE;
    isConnected=kFALSE;
    cout<<"connection to Oracle closed"<<endl;
    needsServerCheck=kTRUE;
  }
}


void HOra2Conn::print() {
  // prints information about the database connection
  if (isConnected)
    cout<<"Oracle-Database: "<<dbName<<"    Username: "<<userName<<'\n';
  else cout<<"*** no connection to Oracle established  ***\n";
  if (strlen(historyDate)==0) cout<<"No history date set\n";
  else cout<<"History date: "<<historyDate<<'\n';
}


void HOra2Conn::showSqlError(const Char_t* fctName,const Char_t* msg) {
  // shows SQL error messages
  Char_t errmsg[513];
  size_t buflen, msglen;
  buflen=sizeof(errmsg)-1;
  sqlglm(errmsg,&buflen,&msglen);
  if ((++msglen)<buflen) {
    errmsg[msglen]='\0';
  } else {
    errmsg[buflen]='\0';
  }
  if (msg) {
    Error(fctName,"\n%s\n%s\n",errmsg,msg);
  } else {
    Error(fctName,"\n%s\n",errmsg);
  }
}


Char_t* HOra2Conn::getPassword() {
  // asks for the password
  Char_t* passwd = new Char_t[20];
  Char_t buf[20];
  Int_t ttyfd = 0;
  struct termios tio, itio;
  if ((ttyfd = ::open("/dev/tty", O_RDWR)) < 0) {
    fprintf(stderr, "cannot open tty, using stdin\n");
    ttyfd = 0;
  }
  if (tcgetattr(ttyfd, &tio) < 0) {
    fprintf (stderr, "\nUnable to get terminal characteristics: ");
    cout<<"enter password for user "<<userName<<" > ";
    scanf("%[^\n]%*c",passwd);
    return passwd; 
  }
  itio = tio;
  tio.c_lflag &= ~(ECHO|ICANON);         
  tcsetattr (ttyfd, TCSAFLUSH, &tio);
  cout<<"enter password for user "<<userName<<" > ";
  fgets(buf, 20, stdin);
  tcsetattr (ttyfd, TCSAFLUSH, &itio);
  //close(ttyfd);
  sscanf(buf, "%s", passwd);
  cout<<endl;
  return passwd;
}


Bool_t HOra2Conn::openConnection(Char_t* password) {
  // opens connection (contains the SQL-statements)
  if (isConnected) close();
  Char_t connId[80];
  strcpy(connId,userName);
  strcat(connId,"@");
  strcat(connId,dbName);    // e.g."hades_ana@hadp"
  EXEC SQL BEGIN DECLARE SECTION;
    char* uName;
    char* pWord;
  EXEC SQL END DECLARE SECTION;
  uName=connId;
  pWord=password;
  EXEC SQL WHENEVER SQLERROR DO
    showSqlError("openConnection(Char_t*)","Wrong user/password");
  EXEC SQL CONNECT :uName IDENTIFIED BY :pWord;
  if (sqlca.sqlcode==0) isConnected=kTRUE;
  else cout<<"*** Failed to connect to Oracle ***"<<endl;
  delete [] password;
  actRunId=-1;
  runStart=0;
  expLocation[0]='\0';
  historyDate[0]='\0';
  alignmentOutVers=-1;
  return isConnected; 
}

Int_t HOra2Conn::checkServerLoad() {
  EXEC SQL BEGIN DECLARE SECTION;
    int retval; 
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO notfound;
  EXEC SQL EXECUTE
    BEGIN
      :retval := hanal.check_analysis_sessions;
    END;
  END-EXEC;
  return retval;
notfound:
  showSqlError("checkServerLoad");
  return -1; 
}

Int_t HOra2Conn::getRunStart(Int_t id) {
  // Gets the actual run id from the current event file and compares it with
  // the last used actRunId for fetching data.
  // If they are different, the run start time (converted to a number od seconds) is
  // read from Oracle and stored together with the run id in the data members
  // run_id and runStart
  if (id==actRunId && runStart!=0) return runStart;
  if (needsServerCheck) {
    Int_t l=0;
    UInt_t nTotWait=0;
    l=checkServerLoad();
    while (l==0&&nTotWait<86400000) {
      UInt_t nWait=10000+gRandom->Integer(20000);
      nTotWait+=nWait;
      cout<<"Oracle server busy, retry in "<<nWait/1000<<" seconds"<<endl;
      usleep(nWait*1000);
      l=checkServerLoad();
    }
    if (l>0) needsServerCheck=kFALSE;
    else return -1;
  }
  if (strlen(historyDate)==0) setParamRelease(id);
  actRunId=id;
  alignmentOutVers=-1;
  runStart=-1;
  EXEC SQL BEGIN DECLARE SECTION;
    int ri; 
    int rs;
    varchar exp_loc[11];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO notfound;
  ri = actRunId;
  exp_loc.len=11;
  // run start converted to number of seconds since 01-JAN-1970 local time 
  EXEC SQL EXECUTE
    BEGIN
      hades_oper.run_query_2.get_run_start(:ri,:rs,:exp_loc);
    END;
  END-EXEC;
  if (ri==(Int_t)actRunId) {
    runStart=rs;
    exp_loc.arr[exp_loc.len]='\0';
    if (strlen(expLocation)==0) strcpy(expLocation,(Char_t*)exp_loc.arr);
    else {
      if (strcmp(expLocation,(Char_t*)exp_loc.arr)!=0) {
        Error("getRunStart(Int_t)",
          "\nA switch from beamtime runs to simulations runs and vice versa is not "
          "possible\nwithout the close and reopen of the Oracle connection!\n\n");
        runStart=-1;
      }
    }
    /* cout<<"actRunId: "<<actRunId<<"   runStart: "<<runStart
           <<"   expLocation: "<<expLocation<<endl; */
    return runStart;
  }
notfound:
  Error("getRunStart(Int_t)","Run not found!\n\n");
  return -1; 
}


Bool_t HOra2Conn::setHistoryDate(const Char_t* dateString) {
  // Sets the date to retrieve historic data
  // Returns kFALSE when the date string cannot be converted to a valid date.
  EXEC SQL BEGIN DECLARE SECTION;
    char* d;
    char rd[21];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO notfound;
  d=(Char_t*)dateString;
  rd[0]='\0';
  EXEC SQL EXECUTE
    BEGIN
      hades_oper.run_query_2.set_history_date(:d);
      :rd := to_char(hades_oper.run_query_2.get_history_date,'DD-MON-YYYY HH24:MI:SS');
    END;
  END-EXEC;
  rd[20]='\0';
  strcpy(historyDate,rd);
  cout<<"*************************************************************\n";
  cout<<"     Oracle history date: "<<historyDate<<"\n";
  cout<<"*************************************************************\n";
  return kTRUE;
notfound:
  showSqlError("setHistoryDate(Int_t)");
  return kFALSE;
}

Bool_t HOra2Conn::setParamRelease(const Char_t* release_name) {
  // Sets the history date to the creation date of the parameter release give by name
  EXEC SQL BEGIN DECLARE SECTION;
    char* r;
    char rd[21];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO notfound;
  rd[0]='\0';	
  r=(Char_t*)release_name;
  EXEC SQL EXECUTE
    BEGIN
      hades_oper.run_query_2.set_param_release(:r);
      :rd := to_char(hades_oper.run_query_2.get_history_date,'DD-MON-YYYY HH24:MI:SS');
    END;
  END-EXEC;
  rd[20]='\0';
  strcpy(historyDate,rd);
  cout<<"*************************************************************\n";
  cout<<"     Oracle history date: "<<historyDate<<"\n";
  cout<<"*************************************************************\n";
  return kTRUE;
notfound:
  showSqlError("setParamRelease(const Char_t*)");
  return kFALSE;
}

Bool_t HOra2Conn::setParamRelease(Int_t run) {
  // Sets the history date to the creation date of the parameter release
  // for the corresponding experiment
  EXEC SQL BEGIN DECLARE SECTION;
    int r;
    char rd[21];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO notfound;
  r=run;
  rd[0]='\0';
  EXEC SQL EXECUTE
    BEGIN
      hades_oper.run_query_2.set_history_date_by_run(:r);	
      :rd := to_char(hades_oper.run_query_2.get_history_date,'DD-MON-YYYY HH24:MI:SS');
    END;
  END-EXEC;
  rd[20]='\0';
  strcpy(historyDate,rd);
  cout<<"*************************************************************\n";
  cout<<"     Oracle history date: "<<historyDate<<"\n";
  cout<<"*************************************************************\n";
  return kTRUE;
notfound:
  showSqlError("setParamRelease(Int_t)");
  return kFALSE;
}