#!/bin/bash

today=`date +%F`
logfile=/home/`whoami`/DataCheck/log/FillAuxData$today.log

# steps: 
#  select run# from DB - no, date is given
#  get daily files
#  select file# from DB
#  select start and stop from DB
#  get info from files
#  fill into into DB


doupdate="yes" # update all entries
doupdate="no" # fill only entries which are not yet existing

echo ""  >> $logfile 2>&1
echo ""  >> $logfile 2>&1
echo ""  >> $logfile 2>&1
echo `date`"executing "$0"..." >> $logfile 2>&1
echo "=====> doupdate: "$doupdate >> $logfile 2>&1

password=`cat /home/fact/DataCheck/.pw`
if [ "$password" == "" ]
then
   echo "please insert password in .pw file"
fi

# setup to use ftools
export HEADAS=/opt/heasoft-6.11/x86_64-unknown-linux-gnu-libc2.13-0/
source $HEADAS/headas-init.sh

# get last 2 nights
dates=( `date +%Y/%m/%d --date="-12hour"` `date +%Y/%m/%d --date="-36hour"` `date +%Y/%m/%d --date="-60hour"` )
#dates=( "2011/11/29" )
# problems with 2011/11/29

# do rsync for rawfiles of these dates
for date in ${dates[@]}
do 
   echo "" >> $logfile 2>&1
   echo "" >> $logfile 2>&1
   echo "" >> $logfile 2>&1
   auxdir=/loc_data/aux/$date
   runnumber=`echo $date | sed -e 's/\///g'`
   echo `date`": processing files in "$auxdir >> $logfile 2>&1
   # check if data are available from that night
   if ! [ -d $auxdir ]
   then
      echo `date`": no data available in "$auxdir >> $logfile 2>&1
      continue
   fi

   # get daily fits files 
   trackingfile=$auxdir/$runnumber.DRIVE_CONTROL_TRACKING_POSITION.fits
   if ! [ -e $trackingfile ]
   then 
      echo $trackingfile" not found"
   fi
   triggerratefile=$auxdir/$runnumber.FTM_CONTROL_TRIGGER_RATES.fits
   if ! [ -e $triggerratefile ]
   then 
      echo $triggerratefile" not found"
   fi
   thresholdfile=$auxdir/$runnumber.FTM_CONTROL_STATIC_DATA.fits
   if ! [ -e $thresholdfile ]
   then 
      echo $thresholdfile" not found"
   fi
   biasvoltagefile=$auxdir/$runnumber.BIAS_CONTROL_VOLTAGE.fits
   if ! [ -e $biasvoltagefile ]
   then 
      echo $biasvoltagefile" not found"
   fi
   biascurrentsfile=$auxdir/$runnumber.BIAS_CONTROL_CURRENT.fits
   if ! [ -e $biascurrentsfile ]
   then 
      echo $biascurrentsfile" not found"
   fi

   # get file numbers from DB
   query1="SELECT fFileNumber from RunInfo WHERE fRunNumber="$runnumber
   filenumbers=( `/usr/bin/mysql -u operator --host=fact01.fact.local --password=$password FactData -s -e "$query1" 2>> $logfile` )
   for filenum in ${filenumbers[@]}
   do
      #echo $filenum
      #query2="SELECT fRunStart from RunInfo WHERE fRunNumber="$runnumber" AND fFileNumber="$filenum
      #starttime=`/usr/bin/mysql -u operator --host=fact01.fact.local --password=$password FactData -s -e "$query2" 2>> $logfile`
      #query3="SELECT fRunStop from RunInfo WHERE fRunNumber="$runnumber" AND fFileNumber="$filenum
      #stoptime=`/usr/bin/mysql -u operator --host=fact01.fact.local --password=$password FactData -s -e "$query3" 2>> $logfile`
      #echo "start: "$starttime 
      #echo "stop: "$stoptime 
      rawfile=/loc_data/raw/$date/$runnumber"_"`printf %03d $filenum`.fits
      if ! [ -e $trackingfile ]
      then 
         echo "ERROR: "$rawfile" not found."
         continue
      #else
      #   echo $rawfile
      fi
      checkfitsfile=`fverify $rawfile  2>> $logfile | grep '0 error(s)'`
      if [ "$checkfitsfile" == "" ]
      then
         numfitserrors=1
         echo "WARN: "$rawfile" probably corrupted."
         continue
      fi
      runtype=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep RUNTYPE | grep -E -o "['][a-z-]+[']" | sed -e "s/'//g"`
      #echo " runtype: "$runtype
      #/home/fact/FACT++/fitsdump -h -t Events $rawfile 
      mjdref=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'MJDREF' | grep -E -o '[0-9]{5}'`
      tstarti=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'TSTARTI' | grep -E -o '[0-9]{5}'`
      tstartf=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'TSTARTF' | grep -E -o '0[.][0-9]+'`
      tstopi=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'TSTOPI' | grep -E -o '[0-9]{5}'`
      tstopf=`/home/fact/FACT++/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'TSTOPF' | grep -E -o '0[.][0-9]+'`
      if [ "$tstarti" == "" ] || [ "$tstopi" == "" ]
      then 
         echo "WARN: "$rawfile" has start or stop time = 0."
         continue
      fi
      # calculate start and stop times
      tstart=`echo " $tstarti + $mjdref + $tstartf " | bc -l`
      tstart2=`echo " $tstarti + $mjdref + $tstartf - 0.00011574 " | bc -l`  # 10 sec
      #tstart2=`echo " $tstarti + $mjdref + $tstartf - 0.000023148 " | bc -l` # 2 sec
      tstop=`echo " $tstopi + $mjdref + $tstopf " | bc -l`
      #echo " tstart in mjd: "$tstart
      #echo " tstop in mjd: "$tstop
      query0="UPDATE RunInfo SET "
      queryend=" WHERE fRunNumber="$runnumber" AND fFileNumber="$filenum
      
      # get information from tracking
      #echo $trackingfile
      ramin=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Ra;Time]' - | ftcopy -'[col Ra]' - | ftstat - | grep 'min' | grep -E -o '[0-9][.][0-9]+'`
      ramax=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Ra;Time]' - | ftcopy -'[col Ra]' - | ftstat - | grep 'max' | grep -E -o '[0-9][.][0-9]+'`
      ramean=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Ra;Time]' - | ftcopy -'[col Ra]' - | ftstat - | grep 'mean' | grep -E -o '[0-9][.][0-9]+'`
      decmin=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Dec;Time]' - | ftcopy -'[col Dec]' - | ftstat - | grep 'min' | grep -E -o '[0-9][.][0-9]+'`
      decmax=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Dec;Time]' - | ftcopy -'[col Dec]' - | ftstat - | grep 'max' | grep -E -o '[0-9][.][0-9]+'`
      decmean=`ftcopy $trackingfile'[Time> '${tstart}' && Time< '${tstop}'][col Dec;Time]' - | ftcopy -'[col Dec]' - | ftstat - | grep 'mean' | grep -E -o '[0-9][.][0-9]+'`
      #echo " ra: "$ramin" - "$ramean" - "$ramax
      if [ "$ramin" == "$ramax" ] && [ "$ramean" != "" ]
      then
         query0=$query0" fRightAscension="$ramean
      else
         query0=$query0" fRightAscension=NULL"
      fi
      #echo " dec: "$decmin" - "$decmean" - "$decmax
      if [ "$decmin" == "$decmax" ] && [ "$decmean" != "" ]
      then
         query0=$query0", fDeclination="$decmean
      else
         query0=$query0", fDeclination=NULL"
      fi

      # get information from trigger
      #echo $triggerratefile
      #ftcopy $triggerratefile'[Time> '${tstart}' && Time< '${tstop}' && TriggerRate!=-1][col TriggerRate;Time]' - | ftcopy -'[col TriggerRate]' - | ftstat -
      ratemin=`ftcopy $triggerratefile'[Time> '${tstart}' && Time< '${tstop}' && TriggerRate!=-1][col TriggerRate;Time]' - | ftcopy -'[col TriggerRate]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
      ratemax=`ftcopy $triggerratefile'[Time> '${tstart}' && Time< '${tstop}' && TriggerRate!=-1][col TriggerRate;Time]' - | ftcopy -'[col TriggerRate]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
      ratemean=`ftcopy $triggerratefile'[Time> '${tstart}' && Time< '${tstop}' && TriggerRate!=-1][col TriggerRate;Time]' - | ftcopy -'[col TriggerRate]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
      ratemedian=`ftcopy $triggerratefile'[Time> '${tstart}' && Time< '${tstop}' && TriggerRate!=-1][col TriggerRate;Time]' - | ftcopy -'[col TriggerRate]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
      #echo " rate: "$ratemin" - "$ratemedian" - "$ratemean" - "$ratemax
      if [ "$ratemedian" != "0" ]
      then
         query0=$query0", fTriggerRateMedian="$ratemedian
      else
         query0=$query0", fTriggerRateMedian=NULL"
      fi

      # get information from trigger
      #echo $thresholdfile
      #echo "ftcopy $thresholdfile'[Time> '${tstart}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat -"
      threshmin=`ftcopy $thresholdfile'[Time> '${tstart}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
      threshmax=`ftcopy $thresholdfile'[Time> '${tstart}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
      threshmean=`ftcopy $thresholdfile'[Time> '${tstart}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
      threshmedian=`ftcopy $thresholdfile'[Time> '${tstart}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
      #echo " threshold: "$threshmin" - "$threshmedian" - "$threshmean" - "$threshmax
      if [ "$threshmean" == "0" ]
      then
         #echo "ftcopy $thresholdfile'[Time> '${tstart2}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat -"
         threshmin=`ftcopy $thresholdfile'[Time> '${tstart2}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
         threshmax=`ftcopy $thresholdfile'[Time> '${tstart2}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
         threshmean=`ftcopy $thresholdfile'[Time> '${tstart2}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
         threshmedian=`ftcopy $thresholdfile'[Time> '${tstart2}' && Time< '${tstop}'][col PatchThresh;Time]' - | ftcopy -'[col PatchThresh]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
         #echo " threshold: "$threshmin" - "$threshmedian" - "$threshmean" - "$threshmax
         # is this taking into account the 160 values in a row?
      fi
      query0=$query0", fThresholdMedian="$threshmedian

#      # get information from bias: Uref
#      #echo $biasvoltagefile
#      #echo "ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat -"
#      biasvoltrefmin=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
#      biasvoltrefmax=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
#      biasvoltrefmean=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
#      biasvoltrefmedian=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
#      echo " bias: "$biasvoltrefmin" - "$biasvoltrefmedian" - "$biasvoltrefmean" - "$biasvoltrefmax
#      if [ "$biasvoltmean" == "0" ]
#      then
#         #echo "ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat -"
#         biasvoltrefmin=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
#         biasvoltrefmax=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
#         biasvoltrefmean=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
#         biasvoltrefmedian=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col Uref;Time]' - | ftcopy -'[col Uref]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
#         echo " bias: "$biasvoltrefmin" - "$biasvoltrefmedian" - "$biasvoltrefmean" - "$biasvoltrefmax
#         # is this taking into account the 160 values in a row?
#      fi

      # get information from bias: U
      #echo $biasvoltagefile
      #echo "ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat -"
      biasvoltmin=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
      biasvoltmax=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
      biasvoltmean=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
      biasvoltmedian=`ftcopy $biasvoltagefile'[Time> '${tstart}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
      #echo " bias: "$biasvoltmin" - "$biasvoltmedian" - "$biasvoltmean" - "$biasvoltmax
      if [ "$biasvoltmean" == "0" ]
      then
         #echo "ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat -"
         biasvoltmin=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'min' | grep -E -o '[0-9]+[.]?[0-9]*'`
         biasvoltmax=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'max' | grep -E -o '[0-9]+[.]?[0-9]*'`
         biasvoltmean=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'mean' | grep -E -o '[0-9]+[.]?[0-9]*'`
         biasvoltmedian=`ftcopy $biasvoltagefile'[Time> '${tstart2}' && Time< '${tstop}'][col U;Time]' - | ftcopy -'[col U]' - | ftstat - | grep 'median' | grep -E -o '[0-9]+[.]?[0-9]*'`
         #echo " bias: "$biasvoltmin" - "$biasvoltmedian" - "$biasvoltmean" - "$biasvoltmax
         # is this taking into account the 160 values in a row?
      fi
      query0=$query0", fBiasVoltageMedian="$biasvoltmedian
      
      query0=$query0" "$queryend
      
      if ! /usr/bin/mysql -u operator --host=fact01.fact.local --password=$password FactData -e "$query0" >> $logfile 2>&1
      then
         echo "insert/update of "$runnumber" "$filenum" to mysql failed" >> $logfile 2>&1
         echo "Q0: "$query0" --- query failed."
      fi
   done
done