#!/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
	rm -f ${TMPOUT}
	exit 1
}

function StrgCExit()
{
	echo " "
	echo "$0 was forcefully terminated" >&2
	rm -f ${TMPOUT}
	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/hawc/Desktop/Mars/"
# The path to your Mars/build directory where the processing macros are stored
readonly MACROS="/home/hawc/Desktop/Mars-src/hawc/processing/"
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}"

TMPOUT="./tmpAuxOut-${TEL}.sql"
ERRCODE=

# 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}

# Set previous fail stats to also be fail here
echo "UPDATE DataOnDisk" 			>> ${OUTPUT}
echo "SET DataOnDisk.auxiliary = 6" 		>> ${OUTPUT}
echo "WHERE (DataOnDisk.calibration != 0" 	>> ${OUTPUT}
echo "OR DataOnDisk.header != 0)"		>> ${OUTPUT}
echo "AND DataOnDisk.Telescope = ${TEL}"	>> ${OUTPUT}
echo "AND DataOnDisk.NIGHT = ${NIGHT};"		>> ${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 RawData.NIGHT,
	RawData.RUNID,
	RawData.TSTARTI + RawData.TSTARTF,
	RawData.TSTOPI + RawData.TSTOPF
FROM RawData
INNER JOIN DataOnDisk
ON DataOnDisk.Telescope = RawData.Telescope
AND DataOnDisk.NIGHT = RawData.NIGHT
AND DataOnDisk.RUNID = RawData.RUNID
WHERE RawData.Telescope=${TEL}
AND RawData.NIGHT=${NIGHT}
AND DataOnDisk.ISDRSFILE = 0
AND DataOnDisk.header = 0
AND DataOnDisk.calibration = 0\
"\
 |  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}

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

		if [ ! -n "${DRS_TEMP}" ]
		then
			DRS_TEMP="NULL, NULL"
			DRS_TEMP_ERR=1
		else
			DRS_TEMP_ERR=0
		fi
		echo ${DRS_TEMP}
	fi

  	# Return corresponding data for currents from BIAS_CONTROL_DYNAMIC_DATA
	BNAME="${FNAME}".BIAS_CONTROL_DYNAMIC_DATA.fits
	if [ ! -f "${BNAME}" ]
	then
		BIAS_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
		BIAS_DATA_ERR=20
	else
		BIAS_DATA=`root -b -q -l \
			"${MACROS}"/currents.C\(\""${BNAME}"\","${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"
			BIAS_DATA_ERR=10
		else
			BIAS_DATA_ERR=0
		fi
		echo ${BIAS_DATA}
	fi

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

		if [ ! -n "${FTM_DATA}" ]
		then
			FTM_DATA="NULL, NULL, NULL, NULL"
			FTM_DATA_ERR=100
		else
			FTM_DATA_ERR=0
		fi
		echo ${FTM_DATA}
	fi

  	# Return corresponding data for threshold seeting from FTU_CONTROL_DATA
	UNAME="${FNAME}".FTU_CONTROL_DATA.fits
	if [ ! -f "${UNAME}" ]
	then
			FTU_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
			FTU_DATA_ERR=2000
	else
		FTU_DATA=`root -b -q -l \
			"${MACROS}"/threshold.C\(\""${UNAME}"\","${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"
			FTU_DATA_ERR=1000
		else
			FTU_DATA_ERR=0
		fi
		echo ${FTU_DATA}
	fi

	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}

	ERRCODE=$((DRS_TEMP_ERR + BIAS_DATA_ERR + FTM_DATA_ERR + FTU_DATA_ERR))

	echo "UPDATE DataOnDisk SET auxiliary=${ERRCODE}" 	>> ${TMPOUT}
	echo "WHERE NIGHT=${NIGHT}" 				>> ${TMPOUT}
	echo "AND Telescope=${TEL}" 				>> ${TMPOUT}
	echo "AND RUNID=${RUNID};" 				>> ${TMPOUT}

done

# Delete last comma and replace it with closing parintheses
if [ -f "${TMPOUT}" ]
then
	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}

else
	head -n -4 "${OUTPUT}" > out.sql.new && mv out.sql.new "${OUTPUT}"
fi

if [ -f "${TMPOUT}" ]
then
	cat ${TMPOUT} >> ${OUTPUT}
fi

rm -f ${TMPOUT}