#!/usr/bin/perl -w

#################### main pod documentation begin ###################

=head1 NAME

  Zymonic - Timezone adjustment script 

=head1 SYNOPSIS

=head1 DESCRIPTION

This script will convert all the zzlu fields in all tables as
requested by the script arguments:

zymonic_adjust_timezone.pl [system] [diff] [from] [to]

Optionally from/to may be set to limit the date range of the
records that values are updated.

=head1 SUPPORT

As in the license, Zymonic is provided without warranty or support
unless purchased separately, however... If you email
zymonic-support@zednax.com your issue will   be noted and may
receive a response.

For security issues, please contact zymonic-security@zednax.com and
someone will respond within 8 working hours.


=head1 AUTHOR

    Alex Masidlover et al.
    CPAN ID: MODAUTHOR
    Zednax Limited
    alex.masidlover@zednax.com
    http://www.zednax.com

=head1 COPYRIGHT

This program is free software licensed under the...

	Zymonic Public License 1.0

The full text of the license can be found in the
LICENSE file included with this module.

Other licenses may be acceptable if including
parts of Zymonic in larger projects, please
contact Zednax for details. 

=head1 SEE ALSO

perl(1).

=cut

#################### main pod documentation end ###################

use strict;

package main;

BEGIN
{
    use Zymonic::Utils qw(clean debug death_handler);
    $main::SIG{__DIE__} = \&death_handler;
}

use Zymonic::Config;

# Call main()
main();
exit 0;

#################### subroutine header begin ####################

=head2 main 

 Usage     : main();
 Purpose   : Main function 
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : nothing 


See Also   :

=cut

#################### subroutine header end ###################

sub main
{

    # Deal with options
    $Zymonic::system = clean( $ARGV[0], '_' );
    my $difference = $ARGV[1];
    unless ( $Zymonic::system && $difference )
    {
        print "Usage: zymonic_adjust_timezone.pl [system] [difference] [from date (optional)] [to date (optional)]\n"
          . "\te.g. zymonic_adjust_timezone.pl zymonic_system_1 -1\n"
          . "\te.g. zymonic_adjust_timezone.pl zymonic_system_2 +2 \"2012-03-25 01:00:00\"\n";
        exit 1;
    }

    my $from = $ARGV[2];
    my $to   = $ARGV[3];

    # load config and db
    $Zymonic::ZCONFIG{$Zymonic::system} = Zymonic::Config->new(
        system_name => $Zymonic::system,
        config_dir  => '/etc/zymonic',
        ip_address  => '127.0.0.1',
        protocol    => 'http'
    );
    my $db = $Zymonic::ZCONFIG{$Zymonic::system}->{DB};

    # get all tables with zzlu field
    my $tables = $db->run_query(
        {
            string =>
              'SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name = ? and table_schema = ?',
            params => [ 'zzlu', $Zymonic::ZCONFIG{$Zymonic::system}->{dbname} ]
        }
    );
    for my $table_record ( @{$tables} )
    {
        my $table = $table_record->{table_name};
        my $res   = $db->run_statement(
            {
                string => "UPDATE $table SET zzlu = DATE_ADD(zzlu, INTERVAL ? HOUR)"
                  . (
                    $from || $to
                    ? ' WHERE '
                      . ( $from ? 'zzlu >= ? ' : '' )
                      . ( $from && $to ? ' AND ' : '' )
                      . ( $to ? 'zzlu <= ? ' : '' )
                    : ''
                  ),
                params => [ $difference, ( $from ? $from : () ), ( $to ? $to : () ) ]
            }
        );
        print "Updated Table $table, result: $res\n";
    }

    # update occurred at
    map {
        my $table = $_;
        my $res   = $db->run_statement(
            {
                string => "UPDATE $table SET occurred_at = zzlu, zzlu = zzlu",
                params => []
            }
        );
        print "Updated Table $table occurred_at, result: $res\n";
    } qw(zz_transition_history zz_filter_history);

    print "Done.\n";
}