#!/bin/bash
#
# ** header v3.0
# This file is a part of the CaosDB Project.
#
# Copyright (C) 2018 Research Group Biomedical Physics,
# Max-Planck-Institute for Dynamics and Self-Organization Göttingen
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
#
# ** end header
#
# this patch enhances the datetime table
# Update mysql schema from version v2.0.1 to version v2.0.2
NEW_VERSION="v2.0.2"
OLD_VERSION="v2.0.1"

if [ -z "$UTILSPATH" ]; then
 UTILSPATH="../utils"
fi

. $UTILSPATH/helpers.sh 
. $UTILSPATH/patch_header.sh $*

check_version $OLD_VERSION

dump_table 'datetime_data'

function add_value_ns {
    mysql_execute "ALTER TABLE datetime_data ADD COLUMN value_ns INT UNSIGNED NULL DEFAULT NULL;"
}

function add_temp_column {
    mysql_execute "ALTER TABLE datetime_data ADD COLUMN value_temp INT UNSIGNED NULL DEFAULT NULL;"
}

function convert_datetime_to_secs {
    leap_secs=('1972-06-30' '1972-12-31' '1973-12-31' '1974-12-31' '1975-12-31' '1976-12-31' '1977-12-31' '1978-12-31' '1979-12-31' '1981-06-30' '1982-06-30' '1983-06-30' '1985-06-30' '1987-12-31' '1989-12-31' '1990-12-31' '1992-06-30' '1993-06-30' '1994-06-30' '1995-12-31' '1997-06-30' '1998-12-31' '2005-12-31' '2008-12-31' '2012-06-30' '2015-06-30')
#    for l in "${leap_secs[@]}"; do
#        mysql_execute "INSERT INTO datetime_data (domain_id, entity_id, property_id, value, pidx, status) VALUES (0,0,0,'$l 23:59:59',0,'FIX');"
#    done
    mysql_execute "UPDATE datetime_data SET value_temp=UNIX_TIMESTAMP(value)-7200 WHERE value is not null;"
    
    # add leap second correction
    for l in "${leap_secs[@]}"; do
        mysql_execute "UPDATE datetime_data SET value_temp=value_temp+1 WHERE value>'$l 23:59:59';"
    done
}

function drop_rename {
    mysql_execute "ALTER TABLE datetime_data DROP COLUMN value;"
    mysql_execute "ALTER TABLE datetime_data CHANGE value_temp value INT UNSIGNED NULL DEFAULT NULL;"
}


add_value_ns
add_temp_column
convert_datetime_to_secs
drop_rename

update_version $NEW_VERSION
success