#!/bin/bash
# script for transfering data (old DB: spec_samplingInterval) to table data(new DB)
#
# new design of data:
#                                       Table "public.data"
#   Column     |           Type           | Collation | Nullable |             Default
#--------------+--------------------------+-----------+----------+----------------------------------
#datetime      | timestamp with time zone |           | not null |
#value         | double precision         |           | not null |
#flags         | bigint                   |           | not null |
#timeseries_id | integer                  |           | not null |
#
# new (not available in old DB):
# - flags -- this is a combination of former flag, preliminary flag, ... (at the moment hardwired in this script!)
# - datetime -- with time zone included ==> This means copy within UTC!
#
# author: s.schroeder@fz-juelich.de
# date:   2020-06-05

HOURLY_SPEC="co no pm1 o3 no2 so2 ox aswdir pm10 rn ch4 wdir pm2p5 nox temp wspeed press cloudcover pblheight relhum totprecip u v albedo aswdifu humidity irradiance"
EVENT_SPEC="benzene toluene ethane propane mpxylene oxylene"
MONTHLY_SPEC="ch4 co"

for sampling in hourly event monthly
do
  if [ "$sampling" = "hourly" ]
  then
    speclist=$HOURLY_SPEC
  elif [ "$sampling" = "event" ]
  then
    speclist=$EVENT_SPEC
  else
    speclist=$MONTHLY_SPEC
  fi
  for SPEC in $speclist
  do
    for ID in ... # here id-list (SELECT all!)
    echo "extracting data for timeseries ${ID}"
    psql -h zam10131.zam.kfa-juelich.de -U s.schroeder surface_observations_toar -W -c "\COPY (select id, CONCAT(datetime, '+00'), value, flag from ${SPEC}_${sampling} WHERE id=${ID}) TO 'data_export.txt'"

    #do some adaptions to the data (especially flags=flag+prelimary+... (see above))
    #...

    psql -h localhost -U django toar2 -W -c "\COPY data (timeseries_id, date_time, value, flags) FROM 'data_export.txt'"
  done
done