#!/bin/bash
# script for transfering UBA ozone data (old DB: o3_hourly) 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-07-05

while read -a line
do
    OLD_TIMESERIESID=${line[0]}
    NEW_TIMESERIESID=${line[1]}
    VARNAME=${line[2]}
    SAMPLING=${line[3]}
    echo "extracting data for timeseries ${OLD_TIMESERIESID} --> ${NEW_TIMESERIESID}"
    psql -h zam10131.zam.kfa-juelich.de -U s.schroeder surface_observations_toar -c "\COPY (select '$NEW_TIMESERIESID', CONCAT(datetime, '+00'), value, CONCAT(flag,'+',preliminary) from ${VARNAME}_${SAMPLING} WHERE id=${OLD_TIMESERIESID}) TO 'data_export.txt'"

    # convert old flags to new system of controlled vocabulary
    # --------------------------------------------------------
    # old flags: Definition of the data quality flag values according to WMO code table 033 020.
    #            See WMO manual on codes
    #            (http://www.wmo.int/pages/prog/www/WMOCodes/WMO306_vI2/VolumeI.2.html).
    #            FLAG[0] = "OK"
    #            FLAG[1] = "inconsistent"
    #            FLAG[2] = "doubtful"
    #            FLAG[3] = "wrong"
    #            FLAG[4] = "not_checked"
    #            FLAG[5] = "changed"    # not quite clear what this means wrt quality...
    #            FLAG[6] = "estimated"
    #            FLAG[7] = "missing_value" # we do not store missing values in DB
    #
    # ==> (in combination with preliminary flag):
    # 0+f --> OK (0)
    # 0+t --> OKPreliminary (1)
    #     --> OKModified (2)
    #     --> OKPreliminaryModified (3)
    # 1+t --> Inconsistent (4)
    # 1+f --> InconsistentPreliminary (5)
    # 2+t --> Doubtful (6)
    # 2+f --> DoubtfulPreliminary (7)
    #     --> DoubtfulModified (8)
    #     --> DoubtfulPreliminaryModified (9)
    # 3+t --> Wrong (10)
    # 3+f --> WrongPreliminary (11)
    # 4+f --> NotCheckedPreliminary (12)
    # 5+t --> Changed (13)
    # 6+t --> Estimated (14)
    # 7+  --> MissingValue (15) --> will never happen


    sed -i 's/0+f/0/; s/0+t/1/; s/1+t/4/; s/1+f/5/; s/2+t/6/; s/2+f/7/; s/3+t/10/; s/3+f/11/; s/4+f/12/; s/5+t/13/; s/6+t/14/' data_export.txt
    psql -h zam10116.zam.kfa-juelich.de -U s.schroeder -d toardb_v2 -c "\COPY data (timeseries_id, datetime, value, flags) FROM 'data_export.txt'"
done < timeseries_oldID_newID_var_samp.txt