#!/bin/bash

# Recursively finds all FAD_CONTROL_TEMPERATURE, BIAS_CONTROL_DYNAMIC_DATA,
# FTM_CONTROL_DATA, FTU_CONTROL_DATA files in DIR and their NIGHT and writes 
# an SQL query with the columns: 
# (Telescope,
#  NIGHT, 
#  FAD_CONTROL_TEMPERATURE, 
#  BIAS_CONTROL_DYNAMIC_DATA,
#  FTM_CONTROL_DATA,
#  FTU_CONTROL_DATA)
# to OUTPUT.
#
# Example:
# 	bash update-aux-table.sh 1 /data/HE01/auxil/

set -o errexit
set -o errtrace
set -o nounset
set -o pipefail

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

# define variables
OUTPUT="insert-AuxOnDisk.sql"
INSERT="INSERT INTO"
readonly PROGRAM=$0

usage()
{
	echo "usage: $PROGRAM [-hir] [-o outfile] [Telescope Dir]"
	echo "	-h 		display help"
	echo "	-i 		query ignores already existing rows"
	echo "	-r 		query replaces already existing rows(overwrites -i)"
	echo "	-o outfile	name of the SQL query file"
	exit 1;
}

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

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

TEL="${1}"
DIR="${2}"


# Initialize the Table
echo \
"\
CREATE TABLE IF NOT EXISTS AuxOnDisk
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
	BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
	FTM_CONTROL_DATA tinyint UNSIGNED,
	FTU_CONTROL_DATA tinyint UNSIGNED,
	Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (Telescope, NIGHT)
);
"\ > ${OUTPUT}


echo \
"\
CREATE TEMPORARY TABLE AuxOnDisk_tmp
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
	BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
	FTM_CONTROL_DATA tinyint UNSIGNED,
	FTU_CONTROL_DATA tinyint UNSIGNED,
	Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (Telescope, NIGHT)
); 
"\ >> ${OUTPUT}

FAD="FAD_CONTROL_TEMPERATURE"
BIAS="BIAS_CONTROL_DYNAMIC_DATA"
FTM="FTM_CONTROL_DATA"
FTU="FTU_CONTROL_DATA"

AUXIS=($FAD $BIAS $FTM $FTU)

for AUX in ${AUXIS[@]}
do
	echo ${AUX}


	EXT=0
	NIGHT=`find "${DIR}"/[0-9][0-9][0-9][0-9]/ -type f \
		| grep -o "/[0-9]\{8\}.${AUX}.fits" \
		| cut -f2 -d "/" \
		| cut -f1 -d "."`|| EXT=1

	if [ ${EXT} = "0" ]
	then

		echo \
		"\
INSERT INTO AuxOnDisk_tmp (Telescope, NIGHT, ${AUX})
VALUES
"\ >> ${OUTPUT}


		# Format to (Telescope, NIGHT, FAD_CONTROL_TEMPERATURE),
		QUERY=`paste <(for i in ${NIGHT}; do echo ${TEL}; done) \
			<(echo "${NIGHT}") \
			<(for i in ${NIGHT}; do echo "0"; done) \
			--delimiters "," \
			| sed 's/^/(/' \
			| sed 's/$/),/'`

		# Write query to OUTPUT file
		echo "${QUERY}" >> ${OUTPUT}
		# Change last comma to semicolon
		sed -i '$s/,$//' "${OUTPUT}"

		echo "ON DUPLICATE KEY UPDATE ${AUX} = 0;" >> ${OUTPUT}

		# 1) If there is data in the database that is not on disk then set 
		#	${AUX} = NULL for that night
		# 2) Delete all entries from the database that are not on disk
		# 3) ${INSERT} all entries into the database that are on disk
		echo \
		"\
		CREATE TEMPORARY TABLE TableNotDisk_${AUX}
		(
			Telescope tinyint UNSIGNED NOT NULL,
			NIGHT int UNSIGNED NOT NULL,
			FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
			BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
			FTM_CONTROL_DATA tinyint UNSIGNED,
			FTU_CONTROL_DATA tinyint UNSIGNED,
			Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
				ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (Telescope, NIGHT)
		) SELECT * FROM AuxOnDisk
			WHERE (Telescope, NIGHT) NOT IN 
			(SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		UPDATE DataOnDisk SET auxiliary = NULL
			WHERE DataOnDisk.NIGHT IN 
			(SELECT NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		DELETE FROM AuxOnDisk 
			WHERE (Telescope, NIGHT) NOT IN 
			(SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		${INSERT} AuxOnDisk (SELECT * FROM AuxOnDisk_tmp);
		"\ >> ${OUTPUT}

	else
		# 1) If there is data in the database that is not on disk then set 
		#	${AUX} = NULL for that night
		# 2) Delete all entries from the database that are not on disk
		# 3) ${INSERT} all entries into the database that are on disk
		echo \
		"\
		CREATE TEMPORARY TABLE TableNotDisk_${AUX}
		(
			Telescope tinyint UNSIGNED NOT NULL,
			NIGHT int UNSIGNED NOT NULL,
			FAD_CONTROL_TEMPERATURE tinyint UNSIGNED,
			BIAS_CONTROL_DYNAMIC_DATA tinyint UNSIGNED,
			FTM_CONTROL_DATA tinyint UNSIGNED,
			FTU_CONTROL_DATA tinyint UNSIGNED,
			Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
				ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (Telescope, NIGHT)
		) SELECT * FROM AuxOnDisk
			WHERE (Telescope, NIGHT) NOT IN 
			(SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		UPDATE DataOnDisk SET auxiliary = NULL
			WHERE DataOnDisk.NIGHT IN 
			(SELECT NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		DELETE FROM AuxOnDisk 
			WHERE (Telescope, NIGHT) NOT IN 
			(SELECT Telescope, NIGHT FROM AuxOnDisk_tmp)
			AND Telescope=${TEL};

		${INSERT} AuxOnDisk (SELECT * FROM AuxOnDisk_tmp);
		"\ >> ${OUTPUT}


		echo "No Auxiliary files."
		continue;
	fi



done