#!/bin/bash

# ---------------------------------------------------------------- #
#  README README README README README README README README README  #
# ---------------------------------------------------------------- #
#                                                                  #
# To use this script, you need                                     #
#   - a computer with access to the FACT database in La Palma      #
#   - a file with the password of a valid mysql-user               #
#   - to define the setup below for                                #
#     a) the DB access                                             #
#     b) the data you want to have                                 #
#                                                                  #
# To define the setup, search for SETUP in this script and         #
#   read the details there                                         #
#                                                                  #
# Per data request, you get up to 3 files:                         #
#   *_internal.dat                                                 #
#   *_collaborators.dat                                            #
#   *_external.dat (only if binning is 20min or nightly)           #
#                                                                  #
# Please have in mind that this started as a tool for myself, then #
# others started using it. Also the script is not yet finalized.   #
# In case you find problems and/or have a feature request, please  #
# send and email to dorner@astro.uni-wuerzburg.de                  #
#                                                                  #
# ---------------------------------------------------------------- #
#  README README README README README README README README README  #
# ---------------------------------------------------------------- #





# ToDo (notes DD): 
# ----------------
# - limit creation of file for externals to nightly and 20 min
# - add zd, th for internal for minute binning (daily done)
# - update function for correction
# - update CU for QLA
# - add CU for ISDC analysis
# - add < 20121212 data for QLA
# - check crab flux
# - add E2dNdE?
# - functionality to determine start time for seaon-binning
# - can get_data.sh / Send_Data*.sh be combined? 
#   get_data.sh should be able to run stand-alone and be kept simple for any user

#
# content of files (wish list):
# -----------------------------
# REMARK: keep order of columns to allow for reading with TGraph directly from file: X Y EX EY
# 
# internal 
# --------
# time: time, delta time, start, stop, ontime
# flux: excrate, excerr, corrate, corerr, CU CUerr, flux, fluxerr, 
# other info on flux: signif, cu-factor, num exc, num sig, num bg
# other info: zd th R750cor R750ref
# 
# external (allow only 20min and nightly binning)
# --------
# time: time, delta time, start, stop
# flux: excrate, excerr
#
# collaborators
# -------------
# time: time, delta time, start, stop, ontime
# flux: excrate, excerr, corrate, corerr, flux, flux-err, significance
# 
# additional information to put:
# ------------------------------
# timestamp of creation
# query (for debugging / answering questions) 
# policy (adapted for internal/collaborators/external) [define in files to be used also by Send_Data*.sh
# 



function get_results()
{
   # some basic query parts

   # data check based on artificial trigger rate
   #dch=" AND fR750Cor/fR750Ref >0.93 "
   dch=" AND fR750Cor/fR750Ref BETWEEN 0.93 AND 1.3 "
   # ontime 
   ontime1=" TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn "
   ontime2=" fOnTimeAfterCuts "
   ontimeif=" IF(ISNULL(fEffectiveOn), "$ontime2", "$ontime1") "
   from=" FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) "
   # time range and source
   where=" WHERE fSourceKey="$source" AND fNight BETWEEN "$nightmin" AND "$nightmax
   where=$where" AND NOT ISNULL(fNumExcEvts) "
   # some sanity checks
   where=$where" AND fRunTypeKey=1 "
   # zd cut
   where=$where" AND fZenithDistanceMax < "$zdmax
   # th cut
   where=$where" AND fThresholdMedian < "$thmax
   where=$where" "$dch
   
   # 
   cufactor=" Avg(CUQLA(fNight)) "
   crabflux="3.37e-11"
   fluxprec=13
   crabflux="3.37"
   fluxprec=2
   
   case $timeunit in
      mjd)  delta="(Mjd(MAX(fRunStop))-Mjd(Min(fRunStart)))/2"
            start=" Mjd(Min(fRunStart)) AS start"
            stop=" Mjd(MAX(fRunStop)) AS stop"
            deltat=$delta" AS deltat"
            time=" Mjd(Min(fRunStart))+"$delta" AS time"
            delta2="(Mjd(MAX(o.stop))-Mjd(MIN(o.start)))/2"
            start2=" Mjd(MIN(o.start)) AS start"
            stop2=" Mjd(MAX(o.stop)) AS stop"
            deltat2=$delta2" AS deltat"
            time2=" Mjd(MIN(o.start))+"$delta2" AS time"
            ;;
      unix) delta="(Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')))/2"
            start="Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')) AS start"
            stop="Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) AS stop"
            deltat=$delta" AS deltat"
            time=" Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM'))+"$delta" AS time"
            delta2="(Unix_timestamp(CONVERT_TZ(Max(o.stop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(Min(o.start), '+00:00', 'SYSTEM')))/2"
            startstop2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')) AS start"
            startstop2=$starstop2" Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) AS stop"
            deltat2=$delta2" AS deltat"
            time2=" Unix_timestamp(CONVERT_TZ(Min(o.start), '+00:00', 'SYSTEM'))+"$delta2" AS time"
            ;;
      *)    delta="sec_to_time(time_to_sec(timediff(MAX(fRunStop), Min(fRunStart)))/2)"
            start=" MIN(fRunStart) AS start"
            stop=" MAX(fRunStop) AS stop"
            deltat=$delta" AS deltat"
            time=" addtime(Min(fRunStart), "$delta") AS time"
            delta2="sec_to_time(time_to_sec(timediff(MAX(o.stop), Min(o.start)))/2)"
            start2=" MIN(o.start) AS start"
            stop2=" MAX(o.stop) AS stop"
            deltat2=$delta" AS deltat"
            time2=" addtime(Min(o.start), "$delta2") AS time"
            ;;
   esac
   zenith="fZenithDistance"
   thresh="IF(ISNULL(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)"
   # thomas correction factor
   correvts=" fNumExcEvts*(pow(cos("$zenith"Mean*PI()/180),3)+14.8/21.9*pow(sin(2*"$zenith"Mean*PI()/180),5))/((1-0.00124/1.21*("$thresh"-500)*("$thresh">=500))) "
   excerr="ExcErr(Sum(fNumSigEvts), SUM(fNumBgEvts))"
   CU="SUM("$correvts"/CUQLA(fNight))/SUM("$ontimeif")*3600"
   CUerr=$excerr"/SUM("$ontimeif")*3600*SUM("$correvts"/CUQLA(fNight))/SUM(fNumExcEvts)"
   excerr2="ExcErr(SUM(o.sigevts),SUM(o.bgevts))"
   CU2="SUM(o.corevts/o.cu)/SUM(o.ot)*3600"
   CUerr2=$excerr2"/SUM(o.ot)*3600*SUM(o.corevts/o.cu)/(SUM(o.sigevts)-SUM(o.bgevts))"
   
   ontime=" ROUND(SUM("$ontimeif")/60., 1) AS ontime"
   excrate=" ROUND(SUM(fNumExcEvts)/SUM("$ontimeif")*3600, 1) AS excrate"
   significance="ROUND(LiMa(Sum(fNumSigEvts), SUM(fNumBgEvts)), 1) AS significance"
   numexc="Sum(fNumExcEvts) AS numexc"
   numsig="Sum(fNumSigEvts) AS numsig"
   numbg="Sum(fNumBgEvts) AS numbg"
   excrateerr=" ROUND("$excerr"/SUM("$ontimeif")*3600, 1) AS excrateerr"
   correxcrate=" ROUND(SUM("$correvts")/SUM("$ontimeif")*3600, 1) AS correxcrate"
   # corerr = MMath::ErrorExc(excevtssum+bgevtssum, bgevtssum*5, 0.2)/ontimesum*3600.*corrate/excrate;
   correxcrateerr=" ROUND("$excerr"/SUM("$ontimeif")*3600*SUM("$correvts")/SUM(fNumExcEvts), 1) AS correxcrateerr"
   # correction on run basis
   cu=" ROUND("$CU", 2) AS cu"
   cuerr=" ROUND("$CUerr", 2) AS cuerr"
   flux="ROUND("$CU" * "$crabflux", 2) AS flux"
   fluxerr="ROUND("$CUerr" * "$crabflux", 2) AS fluxerr"
   
   ontime2=" ROUND(SUM(o.ot)/60., 1) AS ontime"
   excrate2=" ROUND((SUM(o.sigevts)-SUM(o.bgevts))/SUM(o.ot)*3600, 1) AS excrate"
   significance2=" ROUND(LiMa(SUM(o.sigevts),SUM(o.bgevts)), 1) AS significance"
   numexc2="Sum(o.sigevts-o.bgevts) AS numexc"
   numsig2="Sum(o.sigevts) AS numsig"
   numbg2="Sum(o.bgevts) AS numbg"
   excrateerr2=" ROUND("$excerr2"/SUM(o.ot)*3600, 1) AS excrateerr"
   correxcrate2=" ROUND(SUM(o.corevts)/SUM(o.ot)*3600, 1) AS correxcrate"
   correxcrateerr2=" ROUND("$excerr2"/SUM(o.ot)*3600*SUM(o.corevts)/(SUM(o.sigevts)-SUM(o.bgevts)), 1) AS correxcrateerr"
   cu2=" ROUND("$CU2", 2) AS cu"
   cuerr2=" ROUND("$CUerr2", 2) AS cuerr"
   flux2="ROUND("$CU2" * "$crabflux", "$fluxprec") AS flux"
   fluxerr2="ROUND("$CUerr2" *"$crabflux", "$fluxprec") AS fluxerr"

   
   if [ $bin -le 0 ]
   then 
      num="#bin"
      # first part of the query
      querystart="SELECT "
      if [ $bin -eq 0 ]
      then 
         querystart=$querystart" fPeriod AS num, "
      else
         querystart=$querystart" FLOOR((Mjd(fRunStart)-Mjd("$nightmin")-0.5)/"`echo $bin | sed -e 's/-//'`".) AS num, "
      fi
      querystart=$querystart" "$time", "$start", "$stop", "
      
      # final part of the query
      querybase=$from$where
      querybase=$querybase" GROUP BY num "
      if [ "$ontimelimit" = "" ]
      then 
         querybase=$querybase" HAVING SUM("$ontimeif")>1200 ORDER BY num " # 20 min
      else
         querybase=$querybase" HAVING SUM("$ontimeif")>"$ontimelimit" ORDER BY num "
      fi
      
      # internal
      queryint=$querystart
      queryint=$queryint" "$excrate", "$correxcrate", "$cu", "$flux", "
      queryint=$queryint" "$deltat", "$ontime", "
      queryint=$queryint" "$excrateerr", "$correxcrateerr", "$cuerr", "$fluxerr", "
      queryint=$queryint" "$significance", "
      queryint=$queryint" Min(fNight) AS nightmin, Max(fNight) AS nightmax, "
      queryint=$queryint" "$numexc", "$numsig", "$numbg", "
      queryint=$queryint" Min("$zenith"Min) AS zdmin, Max("$zenith"Max) AS zdmax, "
      queryint=$queryint" Min("$thresh") AS thmin, Max("$thresh") AS thmax "
      queryint=$queryint" "$querybase
      
      # for collaborators
      querycol=$querystart
      querycol=$querycol" "$excrate", "$correxcrate", "$cu", "$flux", "
      querycol=$querycol" "$deltat", "$ontime", "
      querycol=$querycol" "$excrateerr", "$correxcrateerr", "$cuerr", "$fluxerr", "
      querycol=$querycol" "$significance
      querycol=$querycol" "$querybase
      
      # external
      queryext=$querystart" "$excrate", "$deltat", "$excrateerr" "$querybase
      
   else
      num=
      # first part of the query
      querystart="SELECT "
      querystart=$querystart" "$time2", "$start2", "$stop2", "
      
      # final part of the query
      querybase=" FROM (SELECT fNight, @ot:="$ontimeif" AS ot, fRunStart AS start, fRunStop AS stop, fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, "
      querybase=$querybase" "$correvts" AS corevts, CUQLA(fNight) AS cu, "
      querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @bl, @bl := @bl + 1) AS block, "
      querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @night :=fNight AS night "
      querybase=$querybase$from" CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS "
      querybase=$querybase$where" ORDER BY fRunStart) o GROUP BY block HAVING ontime>0.75*"$bin" ORDER BY 'time'"
      
      # internal
      queryint=$querystart
      queryint=$queryint" "$excrate2", "$correxcrate2", "$cu2", "$flux2", "
      queryint=$queryint" "$deltat2", "$ontime2", "
      queryint=$queryint" "$excrateerr2", "$correxcrateerr2", "$cuerr2", "$fluxerr2", "
      queryint=$queryint" "$significance2", "
      queryint=$queryint" avg(o.night) AS night, "
      queryint=$queryint" "$numexc2", "$numsig2", "$numbg2" "
      queryint=$queryint" "$querybase
      
      # for collaborators
      querycol=$querystart
      querycol=$querycol" "$excrate2", "$correxcrate2", "$cu2", "$flux2", "
      querycol=$querycol" "$deltat2", "$ontime2", "
      querycol=$querycol" "$excrateerr2", "$correxcrateerr2", "$cuerr2", "$fluxerr2", "
      querycol=$querycol" "$significance2
      querycol=$querycol" "$querybase
      
      # external
      queryext=$querystart" "$excrate2", "$deltat2", "$ontime2", "$excrateerr2" "$querybase
      
   fi
   
   
   fileint=$datapath"/FACT_preliminary_"$name"_internal.dat"
   if [ "$overwrite" = "yes" ]
   then 
      echo "internal: "$fileint
      echo "# This file was created at "`date` > $fileint
      # add query and policy here
   fi
   headerint="# "$num" time["$timeunit"] start["$timeunit"] stop["$timeunit"] excrate[evts/h] corr.excrate[evts/h] flux[CU] flux[e-11/cm2/s] delta_time["$timeunit"] ontime[min]"
   headerint=$headerint" excrate_err[evts/h] corr.excrate_err[evts/h] flux_err[CU] flux_err[e-11/cm2/s] significance night num_exc num_sig num_bg zdmin zdmax thmin thmax"
   echo $headerint >> $fileint
   #echo "$queryint"
   mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -s -e "$queryint" >> $fileint
   #mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -e "$queryint" 
   
   
   fileext=$datapath"/FACT_preliminary_"$name"_external.dat"
   if [ "$overwrite" = "yes" ]
   then 
      echo "external: "$fileext
      echo "# This file was created at "`date` > $fileext
      # add query and policy here
   fi
   headerext="# "$num" time["$timeunit"] start["$timeunit"] stop["$timeunit"] excrate[evts/h] delta_time["$timeunit"] excrate_err[evts/h] "
   echo $headerext >> $fileext
   #echo "$queryext"
   mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -s -e "$queryext" >> $fileext
   #mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -e "$queryext"

   filecol=$datapath"/FACT_preliminary_"$name"_collaborators.dat"
   if [ "$overwrite" = "yes" ]
   then 
      echo "collaborators: "$filecol
      echo "# This file was created at "`date` > $filecol
      # add query and policy here
   fi
   headercol="# "$num" time["$timeunit"] start["$timeunit"] stop["$timeunit"] excrate[evts/h] corr.excrate[evts/h] flux[CU] flux[e-11/cm2/s] delta_time["$timeunit"] ontime[min]"
   headercol=$headercol" excrate_err[evts/h] corr.excrate_err[evts/h] flux_err[CU] flux_err[e-11/cm2/s] significance "
   echo $headercol >> $filecol
   #echo "$querycol"
   mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -s -e "$querycol" >> $filecol
   #mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -e "$querycol
}

# -------------------------------------------------------------------------------------- #
#  SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP   #
# -------------------------------------------------------------------------------------- #
#                                                                                        #
# The lines below define the basic setup for the database and give examples and          #
# explanations for the various options available.                                        #
# The request of the data itself is done with a smaller setup further down.              # 
#                                                                                        #
# -------------------------------------------------------------------------------------- #
#
# ----------
#  DB SETUP
# ----------
# path to file with mysql password
sqlpw=/home/$USER/.mysql.pw 
# host of mysql server with FACT DB
#host=lp-fact # ISDC
host=10.0.100.21 # LP or LP via vpn
#host=localhost # your local machine in case you have a copy of DB
# name of database
dbname=factdata 
#
# -------------
#  BASIC SETUP
# -------------
# output path
path=`dirname $0`
datapath=$path"/data"
# create directory for data files 
if ! [ -e $datapath ]
then
   mkdir $datapath
fi
# time unit 
#timeunit=timestamp # default
#timeunit=unix
timeunit=mjd
# time binning 
#  positive values: minutes
#  negative values: days
#  special case 0: period
#  for season binning choose -365 and according start date
#bin=20 # minutes
#bin=0 # period
bin=-1 # nightly
#bin=-365 # yearly
# choose analysis 
#table="AnalysisResultsAllQLA" # N/A
table="AnalysisResultsRunLP" # QLA 
#table="AnalysisResultsRunISDC"  # ISDC
# time range
nightmin=20111115
nightmax=20201231
# defaults for zd and threshold
zdmax=90 # all data
thmax=1500 # all data
# overwrite dataset file? 
# (useful to combine different binnings in one file -> set to "no")
overwrite="yes"
# optional: require minimal ontime per bin (default 20 min)
#ontimelimit=30 # 30 min
ontimelimit= # default 20 min

# -------------------------------------------------------------------------------------- #
#  SETUP - GET YOUR DATA HERE - SETUP - GET YOUR DATA HERE - SETUP - GET YOUR DATA HERE  #
# -------------------------------------------------------------------------------------- #
#                                                                                        #
# Adapt the lines below to your needs.                                                   #
# Overwrite default settings above.                                                      #
# The  data-request is sent with the line 'get_results.'                                 #
# Minumum setup: Define source key and name for file.                                    #
# The list of source keys can be found at                                                #
#   https://fact-project.org/run_db/db/printtable.php?fTable=Source&fSortBy=fSourceKEY+  #
# More examples can be found further down.                                               #
#                                                                                        #
# REMARKS:                                                                               #
# - correction of effect of zd and threshold not yet finalized and only valid for QLA    #
# - no CU-conversion available for ISDC-analysis so far (that for QLA is used instead)   #
#                                                                                        #
# -------------------------------------------------------------------------------------- #

# 501 MAGIC 
source=2
name="Mrk501_2014_forMAGIC"
bin=-1
nightmin=20140714
nightmax=20140805
get_results
#bin=20
#name="Mrk501_2014_forMAGIC20"
#get_results



# end script here
exit



#
# more examples
#

# Mrk 421
source=1
name="Mrk421_nightly"
bin=-1
get_results
name="Mrk421_20min"
bin=20
get_results
name="Mrk421_3d"
bin=-3
get_results
name="Mrk421_10d"
bin=-10
get_results
name="Mrk421_period"
bin=0
get_results



# Mrk 501
source=2
name="Mrk501_nightly"
bin=-1
get_results
name="Mrk501_20min"
bin=20
get_results
name="Mrk501_3d"
bin=-3
get_results
name="Mrk501_10d"
bin=-10
get_results
name="Mrk501_period"
bin=0
get_results



# 2344
source=3
name="2344_nightly"
bin=-1
get_results
name="2344_20min"
bin=20
get_results
name="2344_period"
bin=0
get_results



# 1959
source=7
name="1959_nightly"
bin=-1
get_results
name="1959_20min"
bin=20
get_results
name="1959_period"
bin=0
get_results



# 0323
source=12
name="0323_nightly"
bin=-1
get_results
name="0323_20min"
bin=20
get_results
name="0323_period"
bin=0
get_results



# crab
source=5
name="Crab_nightly"
bin=-1
get_results
name="Crab_20min"
bin=20
get_results
name="Crab_period"
bin=0
get_results
name="Crab_season"
bin=-365
nightmin=20110716
nightmax=20180716
get_results



name="1959_2016"
source=7
bin=-1
nightmin=20160201
nightmax=20161105
get_results

name="1959_all_variable"
overwrite="no"
source=7
bin=-365
nightmin=20120201
nightmax=20130131
get_results
nightmin=20130201
nightmax=20140131
get_results
nightmin=20140201
nightmax=20150131
get_results
bin=0
nightmin=20150201
nightmax=20160131
get_results
bin=-1
nightmin=20160201
nightmax=20170131
get_results
bin=0
nightmin=20170201
nightmax=20180131
get_results



overwrite="yes"
name="1959_all_variable2"
overwrite="no"
source=7
bin=-365
nightmin=20120201
nightmax=20130131
get_results
nightmin=20130201
nightmax=20140131
get_results
nightmin=20140201
nightmax=20150131
get_results
bin=0
nightmin=20150201
nightmax=20160131
get_results
bin=-1
nightmin=20160201
nightmax=20160817
get_results
bin=0	
nightmin=20160818
nightmax=20180131
get_results



overwrite="yes"
bin=0
source=3
name="2344period"
get_results



# flare night (HESS)
name="Mrk501_10min_flarenight"
source=2
bin=10
nightmin=20140623
nightmax=20140623
get_results



# flare night (HESS)
name="Mrk501_5min_flarenight"
source=2
bin=5
nightmin=20140623
nightmax=20140623
get_results




# full sample 
name="Mrk421_all_nightly"
source=1
get_results

name="Mrk501_all_nightly"
source=2
get_results

name="1959_all_nightly"
source=7
get_results

name="2344_all_nightly"
source=3
get_results



name="HESE20160427"
source=19
nightmin=20160425
bin=-10
get_results

name="AMON20160731"
source=21
nightmin=20160730
bin=-10
get_results



