#!/bin/bash
#

source `dirname $0`/../Sourcefile.sh
printprocesslog "INFO starting $0"

echo "Sent data."

start=$1
stop=$2
source=$3
bin=$4
email=$5
table=$6
time=$7
expert=$8

query="SELECT fSourceName FROM Source WHERE fSourceKey="$source
#echo $query
sourcename=`sendquery`

function printinfo()
{
   echo "# "
   echo "# Please cite the FACT design paper and the QLA webpage when using these data."
   echo "#   FACT design paper: H. Anderhub et al. JINST 8 (2013) P6008 "
   echo "#                http://iopscience.iop.org/1748-0221/8/06/P06008 "
   echo "#   QLA webpage: http://www.fact-project.org/monitoring"
   echo "# If you intent to use the data, please let us know for reference. "
   echo "# "
   echo "# Remarks:"
   echo "#  * These are the results of a fast quick look analyis "
   echo "#    on site, i.e. they are preliminary. "
   echo "#  * The quick look analysis includes all data, "
   echo "#    i.e. no data selection done."
   echo "#  * The given values are not fluxes but excess rates "
   echo "#    (number of excess events per effective ontime), "
   echo "#    i.e. there is a dependence on trigger threshold and "
   echo "#    zenith distance of the observation (with the current "
   echo "#    analysis for zenith distance > 40 degree and trigger "
   echo "#    threshold > 500 DAC counts)."
   echo "#  * Nights with less than 20 minutes of data are neglected "
   echo "#    for nightly binning. "
   echo "#  * The QLA results are not reprocessed when a new software "
   echo "#    version is introduced. "
   echo "#  * In case, you need further details about the data or a"
   echo "#    different binning, please do not hesitate to contact us."
   echo "#  * The QLA contains all data since 12.12.2012. "
   echo "#    For older data, please contact us. "
   echo "# "
   echo "# Contact: Daniela Dorner dorner@astro.uni-wuerzburg.de "
   echo "# "
   echo "# This file was created at "`date`
   echo "# Source: "$sourcename
   echo "# Timerange: "$start" - "$stop
   if [ $bin -lt 0 ]
   then
      echo "# Binning: nightly "
   else
      echo "# Binning: "$bin" min "
   fi
   echo "# "
   #echo "# start("$time") stop("$time") excess-rate(evts/h) error-excess-rate(evts/h) ontime(min) significance[if Nsig>10 and Nbg>10 (or Nexc>10)] "
   echo "# start("$time") stop("$time") excess-rate(evts/h) error-excess-rate(evts/h) "
   getdbsetup
   printprocesslog "DEBUG sendquery QUERY: "$query
   mysql $sqlpwfile -s -u $us $pw --host=$ho $db -e " $query "
}

ontime1=" TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn "
ontime2=" fOnTimeAfterCuts "
ontimeif=" IF(ISNULL(fEffectiveOn), fOnTimeAfterCuts, TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn) "
from=" FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) "
# time range and source
where=" WHERE fSourceKey="$source" AND fNight BETWEEN "$start" AND "$stop
# some sanity checks
where=$where" AND fRunTypeKey=1 AND NOT ISNULL(fNumSigEvts) AND NOT ISNULL(fNumBgEvts) AND NOT fRunStart='0000-00-00 00:00:00' AND NOT fRunStop='0000-00-00 00:00:00' "

case $time in
   mjd)  timeselect="Mjd(Min(fRunStart)) AS start, Mjd(MAX(fRunStop)) AS stop, "
         timeselect2=" Mjd(MIN(o.start)) AS start, Mjd(MAX(o.stop)) AS stop, "
         ;;
   unix) timeselect="Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')) AS start, Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) AS stop, "
         timeselect2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')) AS start, Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) AS stop, "
         ;;
   *)    timeselect="MIN(fRunStart) AS start, MAX(fRunStop) AS stop, "
         timeselect2=" MIN(o.start) AS start, MAX(o.stop) as stop, "
         ;;
esac

# todo: 
# implement login
# implement for internal use
#   table
#   more binnings (insert box for minutes? period, 10d?, weekly?)
#   zd limit
#   threshold limit
#   datacheck

if [ $bin -eq -1 ]
then 
   #query="SELECT Min(fRunStart), Max(fRunStop), Sum(fNumExcEvts)/Sum(fOnTimeAfterCuts)*3600, if ((Sum(fNumSigEvts) + 0.2*0.2*Sum(fNumBgEvts))<0, 0, Sqrt(Sum(fNumSigEvts) + 0.2*0.2*Sum(fNumBgEvts))) FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) WHERE fSourceKey="$source" AND fNight BETWEEN "$start" AND "$stop" GROUP BY fNight HAVING SUM(fOnTimeAfterCuts)*60>20"
   query="SELECT "$timeselect" SUM(fNumExcEvts)/SUM("$ontimeif")*3600 AS excrate, "
   query=$query" ExcErr(Sum(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontimeif")*3600 AS excerr "
   #query=$query" IF (SUM(fNumSigEvts)>10 AND SUM(fNumBgEvts)>10, LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)), IF (SUM(fNumSigEvts)-SUM(fNumBgEvts)>10, concat('(', LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)), ')'), '-')) AS significance "
   #query=$query" Avg(fThresholdMedian) as th "
   query=$query$from$where" GROUP BY fNight HAVING SUM("$ontimeif")/60>20 ORDER BY fNight "
else
   # query to get excess rate and significance from DB
   query="SELECT "$timeselect2" ROUND((SUM(o.sigevts)-SUM(o.bgevts))/SUM(o.ot)*3600, 2) AS excrate, "
   query=$query" ROUND(ExcErr(SUM(o.sigevts),SUM(o.bgevts))/SUM(o.ot)*3600,2) AS err "
   #query=$query" ROUND(SUM(o.ot)/60.,1) AS 'ontime [min]' "
   #query=$query" IF (SUM(o.sigevts)>10 AND SUM(o.bgevts)>10, LiMa(SUM(o.sigevts), SUM(o.bgevts)), IF (SUM(o.sigevts)-SUM(o.bgevts)>10, CONCAT('(', LiMa(SUM(o.sigevts), SUM(o.bgevts)), ')'), '-')) AS significance  "
   query=$query" FROM (SELECT fNight, @ot:="$ontimeif" AS ot, fRunStart AS start, fRunStop AS stop, fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, "
   query=$query" IF (@night=fNight AND FLOOR((@os+@ot)/20./60.)<1, @bl, @bl := @bl + 1) AS block, "
   query=$query" IF (@night=fNight AND FLOOR((@os+@ot)/20./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @night :=fNight AS night "
   query=$query$from" CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS "
   query=$query$where" ORDER BY fRunStart) o GROUP BY block ORDER BY start"
fi
#echo $query

#printinfo | mail -a "From: dorner@astro.uni-wuerzburg.de" -s 'test downloading data ' -b dorner@astro.uni-wuerzburg.de $email
printinfo | mail -s 'FACT data (test download)' -b dorner@astro.uni-wuerzburg.de -r dorner@astro.uni-wuerzburg.de $email
#printinfo

finish

# new query:
# select  o.night, Min(o.start), Max(o.stop), Sum(o.sigevts), Sum(o.bgevts), Round(Sum(o.ot)/60.,1)  FROM (Select fNight, fRunID, fOnTimeAfterCuts as ot, fRunStart as start, fRunStop as stop, fNumSigEvts as sigevts, fNumBgEvts as bgevts, if (@night=fNight AND floor((@os+fOnTimeAfterCuts)/20./60.)<1, @bl, @bl := @bl + 1) as block, if (@night=fNight AND floor((@os+fOnTimeAfterCuts)/20./60.)<1, @os:=@os + fOnTimeAfterCuts, @os := fOnTimeAfterCuts) as os,  @os/20./60. as a, floor(@os/20./60.) as b, @night :=fNight as night from RunInfo left join AnalysisResultsRunLP using(fNight,fRunID) CROSS JOIN (SELECT @night :=0, @os :=0, @bl:=0) PARAMS where fNight between 20130414 and 20130415 and fRunTypeKey=1 and fSourceKey=1) o group by block;

# input
#  - binning
#  - source
#  - email address
#  - night range 

# binning
bin=20

# get date
# add funtion
night=20160102

# get sources for current night from DB (RunInfo)
sourcekey=1

# get limits from DB
siglimit=3.0
exclimit=20

# query to get excess rate and significance from DB
query="SELECT o.* FROM"
query=$query" (SELECT fRunID, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @bg := fNumBgEvts, @bg := @bg + fNumBgEvts) AS bgevts,  "
query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @exc := fNumExcEvts, @exc := @exc + fNumExcEvts) AS excevts, "
query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @os := fOnTimeAfterCuts/60., @os := @os + fOnTimeAfterCuts/60.) AS ontime, "
query=$query" @exc/@os*60 AS excrate, "
query=$query" IF (@sig>0 AND @bg*5>0 AND (@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))> 0, Sqrt((@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))*2), 0) AS sig "
query=$query" FROM AnalysisResultsRunLP O LEFT JOIN RunInfo USING (fNight, fRunID) "
query=$query" CROSS JOIN (SELECT @os :=0, @sig :=0, @bg := 0, @exc :=0, @rate :=0) PARAMS "
query=$query" WHERE fNight="$night" AND fSourceKey="$sourcekey") o HAVING ontime > "$bin"*0.9"
results=( `sendquery` )
#echo ${results[@]}

i=0
while [ 0 -lt 1 ]
do 
   num=`echo "$i * 7" | bc -l`
   if [ "${results[$num]}" = "" ]
   then 
      break
   fi
   runid=${results[$num]}
   sig=${results[$num+6]}
   exc=${results[$num+5]}
   
   trigger=`echo " $exc > $exclimit && $sig > $siglimit " | bc -l`
   
#   echo $i" "$num" "$exc" "$sig
   if [ $trigger -eq 1 ]
   then
      # do whatever to be done to trigger
      #  - send email/sms / call
      #  - create amon file
      #  - prepare email for alert
      echo $runid" "$exc" "$sig" -> "$trigger
      triggerfile="/home/fact/flare.alerts/"$night"_"$runid"_"$sourcekey".trigger"
      touch $triggerfile
   fi
   # counter
   i=`echo $i +1 | bc -l`
done

finish 


# old query:
#   #query="SELECT "$timeselect2", Round(o.excrate,2), Round(ExcErr(o.sigevts, o.bgevts),2), IF (o.sigevts>10 AND o.bgevts>10, LiMa(o.sigevts, o.bgevts), '-'), Round(o.ontime,1) as ontime FROM"
#   query="SELECT "$timeselect2" AS time, ROUND((o.sigevts-o.bgevts)/o.ontime*60,2) AS excrate, ROUND(ExcErr(o.sigevts, o.bgevts)/o.ontime*60,2) AS excerr, ROUND(o.ontime,1) AS ontime, IF (o.sigevts>10 AND o.bgevts>10, LiMa(o.sigevts, o.bgevts), IF (o.sigevts-o.bgevts>10, CONCAT('(', LiMa(o.sigevts, o.bgevts), ')'), '-')) AS significance FROM"
#   query=$query" (SELECT IF (@os+fOnTimeAfterCuts/60.>20, @start:=fRunStart, IF (@os=0, @start:=fRunStart, @start)) as start, fRunStop as stop, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
#   #query=$query" (SELECT fRunID, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @bg := fNumBgEvts, @bg := @bg + fNumBgEvts) AS bgevts,  "
#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @exc := fNumExcEvts, @exc := @exc + fNumExcEvts) AS excevts, "
#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @os := fOnTimeAfterCuts/60., @os := @os + fOnTimeAfterCuts/60.) AS ontime "
#   #query=$query" @exc/@os*60 AS excrate, "
#   #query=$query" ExcErr(@sig, @bg) AS excerr, "
#   #query=$query" IF (@sig>0 AND @bg*5>0 AND (@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))> 0, Sqrt((@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))*2), 0) AS sig "
#   #query=$query" LiMa(@sig,@bg) AS signif "
#   query=$query" FROM AnalysisResultsRunLP O LEFT JOIN RunInfo USING (fNight, fRunID) "
#   query=$query" CROSS JOIN (SELECT @os :=0, @sig :=0, @bg := 0, @exc :=0, @rate :=0, @start :=0) PARAMS "
#   query=$query" WHERE fNight between "$start" AND "$stop" AND fSourceKey="$source" ORDER BY fNight, fRunID) o HAVING ontime > "$bin"*0.75"
#