#!/bin/bash
#
# Calculate aux data from auxfiles in directory DIR and write SQL query 
# containing the aux information to OUTPUT
#
# Example:
# 	bash extract-aux-data2.sh cred-file 1 /data/HE01/aux 20201111

set -o nounset

function ErrExit()
{
	echo "ERROR: Line `caller`: ${BASH_COMMAND}" >&2
	exit 1
}

function StrgCExit()
{
	echo " "
	echo "$0 was forcefully terminated" >&2
	exit 1
}

trap ErrExit ERR
trap StrgCExit INT


# File to which the queries are written
OUTPUT="insert-aux.sql"
# The Mars build directory
readonly MARS="/home/frankm/Dateien/Mars/build/"
# The path to your Mars/build directory where the processing macros are stored
# MACROS="/home/frankm/Dateien/Mars/hawc/processing"
readonly MACROS="/home/frankm/Dateien/Mars/build/Masterarbeit/Datenbank/HE01/Mexico/Cfiles/"
readonly PROGRAM=$0
INSERT="INSERT INTO"

usage()
{
	echo "usage: $PROGRAM [-hir] [-o outfile] [Credentials Telescope Dir Night]" 
	echo "	-h 		display help"
	echo "	-i 		query ignores already existing rows"
	echo "	-r 		query replaces already existing rows(overwrites -i)"
	echo "	-d 		delete all entries for a given Night and Telescope"
	echo "	-o outfile	name of the SQL query file"
	exit 1;
}

# Check for flags
while getopts 'hirdo:' flag
do
	case "${flag}" in
		h) usage ;;
		i) INSERT="INSERT IGNORE" ;;
		r) INSERT="REPLACE INTO" ;;
		d) DELETE="true" ;;
		o) OUTPUT="${OPTARG}" ;;
		*) usage ;;
	esac
done
shift $(($OPTIND-1))

# Check if Mars exists
if [ ! -d "${MARS}" ]
then
	echo "Mars does not exists at ${MARS}. Please change in \
		script ${PROGRAM}."
	exit 1
fi

# Check if MACROS exists
if [ ! -d "${MACROS}" ]
then
	echo "Macros directorey does not exists at ${MACROS}. Please change in \
		script ${PROGRAM}."
	exit 1
fi

# Check if at least two arguments are provided
if [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] || [ -z "${4}" ]
then
	echo "Not enough arguments. Check -h for help!"
	exit 1
fi

# File containing the access credentials for the database
CREDENTIALS="${1}"
# Specify the telescope for which to run the script.
TEL="${2}"
# The base directory of the aux-files
DIR="${3}"
# NIGHT
NIGHT="${4}"



# 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)
);
"\ > ${OUTPUT}

if [ ${DELETE} = "true" ]
then
	echo "DELETE FROM AuxData" >> ${OUTPUT}
	echo "WHERE Telescope = ${TEL}" >> ${OUTPUT}
	echo "AND NIGHT = ${NIGHT};" >> ${OUTPUT}
	echo "" >> ${OUTPUT}
fi

echo \
"\
${INSERT} AuxData
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=${TEL}
AND
	NIGHT=${NIGHT}\
"\
 |  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="${DIR}"/${NIGHT:0:4}/${NIGHT:4:2}/${NIGHT:6:2}/${NIGHT}

  echo ${FNAME}

  cd ${MARS}

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

  # 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"
  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 "${FTM_DATA}" ]
  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 " "${TEL}, ${NIGHT}, ${RUNID},	>> ${OUTPUT}
  echo " "${DRS_TEMP},              	>> ${OUTPUT}
  echo " "${BIAS_DATA},          	>> ${OUTPUT}
  echo " "${FTM_DATA},              	>> ${OUTPUT}
  echo " "${FTU_DATA}                	>> ${OUTPUT}
  echo "),"                         	>> ${OUTPUT}

done

# Delete last comma and replace it with closing parintheses
sed -i '$s/,/)/' "${OUTPUT}"

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