#!/bin/bash
#
# Write SQL query for calibration for a given telescope TEL and night NIGHT to
# OUTPUT
#
# Example:
#	bash write-calibration-query.sh 1 20201111

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=find-calibration-file2.sql
readonly PROGRAM=$0
INSERT="INSERT INTO"

usage()
{
	echo "usage: $PROGRAM [-hir] [-o outfile] [Telescope 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 at least two arguments are provided
if [ -z "${1}" ] || [ -z "${2}" ]
then
	echo "Not enough arguments. Check -h for help!"
	exit 1
fi

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


echo \
"\
-- You might want to change the table name
CREATE TABLE IF NOT EXISTS Calibration
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	RUNID mediumint UNSIGNED NOT NULL,
	DrsNight double NOT NULL,
	DrsRunId double NOT NULL,
	DeltaT double UNSIGNED NOT NULL,
	PRIMARY KEY (Telescope, NIGHT, RUNID)
);
"\ > ${OUTPUT}

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

echo \
"\
-- Set Status to 6 if there was an error is the extraction of RawData
UPDATE DataOnDisk 
SET DataOnDisk.calibration = 6
WHERE DataOnDisk.header != 0
AND NIGHT=${NIGHT}
AND Telescope=${TEL};

CREATE TEMPORARY TABLE Calibration_tmp
(
	WITH Table1 AS
	(
		SELECT
		-- NIGHT and RUNID of the data file
		R1.Telescope AS Telescope,
		R1.NIGHT     AS NIGHT,
		R1.RUNID     AS RUNID,
		-- NIGHT and RUNID of the DRS file
		R2.NIGHT     AS DrsNight,
		R2.RUNID     AS DrsRunID,
		-- Time difference between the two
		LEAST(
			ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
				- UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
			ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
				- UNIX_TIMESTAMP(R1.\`DATE-END\`)),
			ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
				- UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
			ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
				- UNIX_TIMESTAMP(R1.\`DATE-END\`))
		) AS DeltaT,
		-- Assign a row number to each combination of data-file and 
		-- drs-file after sorting them by time difference
		ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID, R1.Telescope 
			ORDER BY
			LEAST(
				ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
					- UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
				ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
					- UNIX_TIMESTAMP(R1.\`DATE-END\`)),
				ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
					- UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
				ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
					- UNIX_TIMESTAMP(R1.\`DATE-END\`))
			)
		) AS RowNumber
		FROM (
			SELECT RawData.* FROM RawData 
			INNER JOIN DataOnDisk 
			ON DataOnDisk.Telescope = RawData.Telescope
			AND DataOnDisk.NIGHT = RawData.NIGHT
			AND DataOnDisk.RUNID = RawData.RUNID
			AND DataOnDisk.ISDRSFILE = 0
			WHERE DataOnDisk.header = 0
		) AS R1
		-- Join the table with itself to get all possible combinations 
		-- of runs
		CROSS JOIN (
			SELECT RawData.* FROM RawData 
			INNER JOIN DataOnDisk 
			ON DataOnDisk.Telescope = RawData.Telescope
			AND DataOnDisk.NIGHT = RawData.NIGHT
			AND DataOnDisk.RUNID = RawData.RUNID
			AND DataOnDisk.ISDRSFILE = 0
			WHERE DataOnDisk.header = 0
		) AS R2
		-- Join the DRS files with the table that tells you whether
		-- they can be used if the table does not (yet) exists, this
		-- can be omitted
		WHERE
		-- Of course claibration must be from the same telescope
		R1.Telescope=R2.Telescope
		AND
		-- Select all combinations that have a data file in R1 and a
		-- drs file (at step 1) in R2
		ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
		AND 
		-- Only DRS files with 100 events and the correct ROI are
		-- considered
		R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI 
		AND R1.NIGHT = ${NIGHT}
		AND R2.NIGHT = ${NIGHT}
		AND R1.Telescope = ${TEL}
		-- AND
		-- Check if they are valid
		-- IFNULL(DoNotUse.WrongVoltage,false)!=true
		-- This can be used to restrict the number of combinations     
		-- AND ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`))<78*3600
		ORDER BY
		R1.Telescope,
		R1.NIGHT,
		R1.RUNID,
		DeltaT
	)
	SELECT
	Telescope,
	NIGHT,
	RUNID,
	DrsNight,
	DrsRunId,
	DeltaT/3600e0 AS DeltaT
	FROM
	Table1
	WHERE
	-- Select only the rows with the smallest DeltaT
	RowNumber = 1
);

-- Insert Calibration data into Database
INSERT INTO Calibration SELECT * FROM Calibration_tmp;

-- Set Status to 0 for all valid headers
UPDATE DataOnDisk 
SET DataOnDisk.calibration = 0
WHERE DataOnDisk.header = 0
AND NIGHT=${NIGHT}
AND Telescope=${TEL};

-- Set Status to 2 if there was no drs file with step = 1 in the NIGHT
UPDATE DataOnDisk
SET DataOnDisk.calibration = 2
WHERE EXISTS(
	SELECT * FROM Calibration
	WHERE NIGHT=${NIGHT}
	AND Telescope=${TEL}
	) = 0
	AND NIGHT=${NIGHT}
	AND Telescope=${TEL};
"\ >> ${OUTPUT}