#!/bin/bash

# Recursively finds all .fits.fz and .drs.fits files in DIR and extract 
# NIGHT, RUNID and if the file is a DRS file and writes an SQL query with the 
# columns: (Telescope, NIGHT, RUNID, ISDRSFILE, ISPRECESSED) to OUTPUT.
#
# Example:
# 	bash update-file-table2.sh 1 /data/HE01/raw

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-OnDisk.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 DataOnDisk
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	RUNID mediumint UNSIGNED NOT NULL,
	ISDRSFILE tinyint(1) NOT NULL,
	STATUS tinyint UNSIGNED NOT NULL,
	Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
); 

CREATE TEMPORARY TABLE DataOnDisk_tmp
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	RUNID mediumint UNSIGNED NOT NULL,
	ISDRSFILE tinyint(1) NOT NULL,
	STATUS tinyint UNSIGNED NOT NULL,
	Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
); 

INSERT INTO DataOnDisk_tmp (Telescope, NIGHT, RUNID, ISDRSFILE, STATUS)
VALUES
"\ > ${OUTPUT}

# Find query for .fits.fz files
# Crop filenames from the form /nnnnnnnn_ddd.fits.fz$ to nnnnnnnn_ddd
NRID=`find "${DIR}" -type f \
	| grep -o "/[0-9]\{8\}_[0-9]\{3\}.fits.fz$" \
	| cut -f2 -d "/" \
	| cut -f1 -d "."`

# Format to (Telescope, NIGHT, RUNID, ISDRSFILE, ISPROCESSED),
FZQUERY=`paste <(for i in ${NRID}; do echo ${TEL}; done) \
	<(echo "${NRID}" | cut -f1 -d "_") \
	<(echo "${NRID}" | cut -f2 -d "_") \
	<(for i in ${NRID}; do echo "false"; done) \
	<(for i in ${NRID}; do echo "0"; done) \
	--delimiters "," \
	| sed 's/^/(/' \
	| sed 's/$/),/'`

# Find query for .drs.fits files
# Crop filenames from the form nnnnnnnn_ddd.drs.fits to nnnnnnnn_ddd
DNRID=`find "${DIR}" -type f \
	| grep -o "/[0-9]\{8\}_[0-9]\{3\}.drs.fits$" \
	| cut -f2 -d "/" \
	| cut -f1 -d "."`

# Format to (Telescope, NIGHT, RUNID, ISDRSFILE, ISPROCESSED),
DRSQUERY=`paste <(for i in ${DNRID}; do echo ${TEL}; done) \
	<(echo "${DNRID}" | cut -f1 -d "_") \
	<(echo "${DNRID}" | cut -f2 -d "_") \
	<(for i in ${DNRID}; do echo "true"; done) \
	<(for i in ${DNRID}; do echo "0"; done) \
	--delimiters "," \
	| sed 's/^/(/' \
	| sed 's/$/),/'`

# Write query to OUTPUT file
echo "${FZQUERY}" >> ${OUTPUT}
echo "${DRSQUERY}" >> ${OUTPUT}

# Change last comma to semicolon
sed -i '$s/,$/;/' "${OUTPUT}"

# 1) If there is data in the database that is not on disk then set status = 0
#    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
(
	Telescope tinyint UNSIGNED NOT NULL,
	NIGHT int UNSIGNED NOT NULL,
	RUNID mediumint UNSIGNED NOT NULL,
	ISDRSFILE tinyint(1) NOT NULL,
	STATUS tinyint UNSIGNED NOT NULL,
	Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (Telescope, NIGHT, RUNID, ISDRSFILE)
) SELECT * FROM DataOnDisk
	WHERE (Telescope, NIGHT, RUNID, ISDRSFILE) NOT IN 
	(SELECT Telescope, NIGHT, RUNID, ISDRSFILE FROM DataOnDisk_tmp);

UPDATE DataOnDisk SET STATUS = 0 WHERE NIGHT IN 
	(SELECT NIGHT FROM TableNotDisk);

DELETE FROM DataOnDisk 
	WHERE (Telescope, NIGHT, RUNID, ISDRSFILE) NOT IN 
	(SELECT Telescope, NIGHT, RUNID, ISDRSFILE FROM DataOnDisk_tmp);

${INSERT} DataOnDisk (SELECT * FROM DataOnDisk_tmp);
"\ >> ${OUTPUT}