#!/bin/bash

# File containing the access credentials for the database
CREDENTIALS=credentials-read-only.cnf

# Specify the telescope for which to run the script. Replace '1' 
# by $1 if the telesocpe number is supplied as an option to the script
TELESCOPE=1

# The base directory of the aux-files
AUX=/home/tbretz/data/aux
# The Mars build directory
MARS=~/Mars/build
# The path (relative to your Mars/build directory where the processing macros are stored
MACROS=../hawc/processing
# File to which the queries are written
OUTPUT="insert-aux.sql"

# Open the query-file with the create table statement
echo \
"\
CREATE TABLE IF NOT EXISTS AuxData
(
   Telescope       TINYINT   UNSIGNED  NOT NULL,
   NIGHT           INT       UNSIGNED  NOT NULL,
   RUNID           MEDIUMINT UNSIGNED  NOT NULL,
   TavgDRS         FLOAT,
   TrmsDRS         FLOAT,
   Umed            FLOAT,
   Uavg            FLOAT,
   Udev            FLOAT,
   Urms            FLOAT,
   Imed            FLOAT,
   Iavg            FLOAT,
   Idev            FLOAT,
   Irms            FLOAT,
   Tmed            FLOAT,
   Tavg            FLOAT,
   Tdev            FLOAT,
   Trms            FLOAT,
   TavgPSU         FLOAT,
   TotalOnTime     FLOAT,
   TotalDeadTime   FLOAT,
   TavgFTM         FLOAT,
   ThresholdChMin  MEDIUMINT UNSIGNED,
   ThresholdChMed  FLOAT,
   ThresholdChMax  MEDIUMINT UNSIGNED,
   ThresholdSumMin MEDIUMINT UNSIGNED,
   ThresholdSumMax MEDIUMINT UNSIGNED,
   RateCh0         FLOAT,
   RateCh1         FLOAT,
   RateCh2         FLOAT,
   RateCh3         FLOAT,
   RateCh4         FLOAT,
   RateCh5         FLOAT,
   RateCh6         FLOAT,
   RateCh7         FLOAT,
   RateSum0        FLOAT,
   RateSum1        FLOAT,
   PRIMARY KEY(Telescope, NIGHT, RUNID)
) 
SELECT * FROM 
( VALUES
"\ > ${OUTPUT}

# Get all available runs and their start and stop time
# note that no check is done on the stop time!
echo \
"\
SELECT 
    NIGHT, RUNID, TSTARTI+TSTARTF, TSTOPI+TSTOPF
FROM 
    RawData
WHERE
    Telescope=${TELESCOPE}\
"\
 |  mysql \
       --defaults-file=${CREDENTIALS} \
       --skip-column-names \
       --batch --raw \
       --compress \
 | \
while read -r -a LINE
do
  # Extract night, runid, begin and end
  NIGHT=${LINE[0]}
  RUNID=${LINE[1]}
  BEG=${LINE[2]}
  END=${LINE[3]}

  # Split night into year, month, day
  # Base path for the night
  FNAME="${AUX}"/${NIGHT:0:4}/${NIGHT:4:2}/${NIGHT:6:2}/${NIGHT}

  echo ""
  echo ${FNAME}

  cd ${MARS}

  # Return corresponding data for DRS temperatures from FAD_CONTROL_TEMPEREATURE
  DRSTEMP=`root -b -q -l '${MACROS}/drstemp.C("'${FNAME}'.FAD_CONTROL_TEMPERATURE.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${DRSTEMP}" ]
  then
  	DRSTEMP="NULL, NULL"
  fi
  echo ${DRSTEMP}

  # Return corresponding data for currents from BIAS_CONTROL_DYNAMIC_DATA
  BIAS_DATA=`root -b -q -l '${MACROS}/currents.C("'${FNAME}'.BIAS_CONTROL_DYNAMIC_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${BIAS_DATA}" ]
  then  
  	BIAS_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
  fi
  echo ${BIAS_DATA}

  # Return corresponding data for trigger information from FTM_CONTROL_DATA
  FTM_DATA=`root -b -q -l '${MACROS}/trigger.C("'${FNAME}'.FTM_CONTROL_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${RESULT}" ]
  then
  	FTM_DATA="NULL, NULL, NULL, NULL"
  fi
  echo ${FTM_DATA}

  # Return corresponding data for threshold seeting from FTU_CONTROL_DATA
  FTU_DATA=`root -b -q -l '${MACROS}/threshold.C("'${FNAME}'.FTU_CONTROL_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${FTU_DATA}" ]
  then
	FTU_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
  fi
  echo ${FTU_DATA}

  cd -

  # Write the result as one row to the query file
  echo "/* "${FNAME}" */"                   >> ${OUTPUT}
  echo "ROW("                               >> ${OUTPUT}
  echo " "${TELESCOPE}, ${NIGHT}, ${RUNID}, >> ${OUTPUT}
  echo " "${DRS_TEMP},                      >> ${OUTPUT}
  echo " "${BIAS_DATA},                     >> ${OUTPUT}
  echo " "${FTU_DATA},                      >> ${OUTPUT}
  echo " "${FTM_DATA}                       >> ${OUTPUT}
  echo "),"                                 >> ${OUTPUT}

done

# Finish the query file with defining the column names
echo "\
AS
(
   Telescope, NIGHT, RUNID,
   TavgDRS, TrmsDRS,
   Umed, Uavg, Udev, Urms,
   Imed, Iavg, Idev, Irms,
   Tmed, Tavg, Tdev, Trms,
   TavgPSU,
   TotalOnTime, TotalDeadTime, Efficiency, TavgFTM,
   ThresholdChMin, ThresholdChMed, ThresholdChMax, 
   ThresholdSumMin, ThresholdSumMax, 
   RateCh0, RateCh1, RateCh2, RateCh3, RateCh4, RateCh5, RateCh6, RateCh7,
   RateSum0, RateSum1
)\ 
" >> ${OUTPUT}