#! /usr/bin/perl -CS

use strict;
use utf8;
use CGI qw(-utf8);
use Template;
use DBI;
use JSON::XS;
use POSIX;
use Time::Local;
use Encode;
use HTML::Entities;
use IPC::Shareable;



my $cgi = CGI->new();
my $dbh = DBI->connect(
                        'DBI:mysql:powerlog:localhost',
                        'powerlogger', 
                        'somepassword', {
                            RaiseError => 1,
                            AutoCommit => 1,
                            mysql_enable_utf8 =>1
                        }
                    ) || response('fail', 'unable to connect to database');;
$dbh->do("SET NAMES UTF8");

my $glue = 'zpwr';
my %options = (
    create    => 0,
    exclusive => 0,
    mode      => 0666,
    destroy   => 0,
    );
my %cur_pzem_data;
tie %cur_pzem_data, 'IPC::Shareable', $glue, { %options } or
    die "pwrlogger client: tie failed\n";



my $cmd = $cgi->param('cmd');

if( !$cmd ){

    initial_drawing($cgi, $dbh);

}elsif( $cmd eq 'get_curvalues' ){
   

  
    my ($ts, $js_ts, $v, $p, $e, $u, $d) = @cur_pzem_data{'timestamp', 'epoch', 'voltage','power','energy', 'uptime', 'timediff'};


    $js_ts = timegm(localtime(int($js_ts/1000))) * 1000;

    my $statement = 'SELECT nrg_value FROM energy_by_date WHERE nrg_date = DATE(NOW())';
    my ($today_energy) = $dbh->selectrow_array($statement);

    $statement = 'SELECT SUM(nrg_value) FROM energy_by_date WHERE nrg_date >= DATE_FORMAT(NOW(), "%Y-%m-01")';
    my ($curmonth_energy) = $dbh->selectrow_array($statement);

    send_json_data({
                        "ts"        => $ts, 
                        "js_ts"     => $js_ts,
                        "voltage"   => sprintf("%.1f", $v+0), 
                        "power"     => $p+0, 
                        "energy"    => sprintf("%.3f", ($e/1000)),  
                        "daily_e"   => sprintf("%.3f", $today_energy/1000), 
                        "monthly_e" => sprintf("%.3f", $curmonth_energy/1000), 
                        "uptime"    => $u, 
                        "timediff"  => $d
                    }
    );
}elsif( $cmd eq 'get_pv_history' ){
    my $rangebeg = $cgi->param('trangebeg');
    my $rangeend = $cgi->param('trangend');

    my $statement = '   SELECT pwr_timestamp, pwr_voltage, pwr_power 
                        FROM powerdata
                        WHERE pwr_timestamp >= ? AND pwr_timestamp <= ? ORDER BY pwr_timestamp ASC';
    my $rows = $dbh->selectall_arrayref( $statement, undef, ($rangebeg . ' 00:00:00'), ($rangeend . ' 23:59:59'));   

    my @data = ();
    foreach my $row (@$rows){
        my ($year, $month, $day, $hour, $min, $sec) = $$row[0] =~ /^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/;
        $month--;
        my $utctime = timegm($sec,$min,$hour,$day,$month,$year)*1000;
        push(@data, [$utctime, $$row[1]+0, $$row[2]+0]);
    }

$dbh->disconnect();
send_json_data( \@data );


}elsif( $cmd eq 'get_energy_for_period' ){

    my $rangebeg = $cgi->param('trangebeg');
    my $rangeend = $cgi->param('trangend');

    my $statement = '   SELECT SUM(nrg_value)/1000
                        FROM energy_by_date
                        WHERE nrg_date >= ? AND nrg_date <= ?';
    my( $energy_for_period ) = $dbh->selectrow_array($statement, undef, $rangebeg, $rangeend);
	
	
    $dbh->disconnect();
    send_json_data( {'energy' => sprintf('%.3f', $energy_for_period)} );

}elsif( $cmd eq 'get_en_history' ){
    my $rangebeg 	= $cgi->param('trangebeg');
    my $rangeend 	= $cgi->param('trangend');
    my $grouping	= $cgi->param('grouping'); #stepday, stepweek, stepmonth

    my($time, $where, $group, $order ) = do{
        if( $grouping eq 'stepday' ){
            (' nrg_date ', ' nrg_date >= ? AND nrg_date <= ? ', ' nrg_date ', ' nrg_date ')
        }elsif( $grouping eq 'stepweek'){
            (' CONCAT(
                STR_TO_DATE( CONCAT(Year(nrg_date), " ", Week(nrg_date,3), " ", "Monday"), "%x %v %W"), 
                " - ",
                STR_TO_DATE( CONCAT(Year(nrg_date), " ", Week(nrg_date,3), " ", "Sunday"), "%x %v %W")
            ) AS yw ', ' nrg_date >= ? AND nrg_date <= ? ', ' yw ', ' yw ')
        }elsif($grouping eq 'stepmonth'){
            (' SUBSTR(nrg_date, 1,7) AS yearmonth ', ' nrg_date >= ? AND nrg_date <= ? ', ' yearmonth ', ' yearmonth ')
        }elsif($grouping eq 'stepyear'){
            (' SUBSTR(nrg_date, 1,4) AS yearonly ', ' nrg_date >= ? AND nrg_date <= ? ', ' yearonly ', ' yearonly ')
        }else{
            response('fail',"unknown grouping");
        }
    };
    my $statement = "   SELECT $time, SUM(nrg_value)/1000 
                        FROM energy_by_date 
                        WHERE $where
                        GROUP BY $group ORDER BY $order";
    my $rows = $dbh->selectall_arrayref($statement, undef, $rangebeg, $rangeend);
    
    my @outdata = ();
    foreach my $row (@$rows){
        push @outdata, [$$row[0], $$row[1]+0];
    }
    $dbh->disconnect();
    send_json_data(\@outdata);

}else{
    response('fail',"unknown command");
} 



sub initial_drawing{
    my($cgi, $dbh) = @_;
    
    my $vars = {};

    my $tt = Template->new({
        INCLUDE_PATH	=> '/var/www/pwrstat/tmpl',
        INTERPOLATE 	=> 1,
        ENCODING		=> 'utf8'
    }) || die "$Template::ERROR\n";

    my $statement = "SELECT DATE_FORMAT(NOW(), '%Y-%m-01'),
                            DATE_SUB(
                                DATE_ADD(
                                    STR_TO_DATE(
                                        DATE_FORMAT(
                                            NOW(),
                                            '%Y-%m-01'
                                        ),
                                        '%Y-%m-%d'
                                    ),
                                    INTERVAL 1 MONTH
                                ),
                                INTERVAL 1 DAY
                            )";
    ($$vars{m_range_begin}, $$vars{m_range_end}) = $dbh->selectrow_array( $statement);
    
    
    $statement = "SELECT 	DATE_SUB( 
                                STR_TO_DATE( 
                                    DATE_FORMAT( 
                                        NOW(), 
                                        '%Y-%m-01'
                                    ), 
                                    '%Y-%m-%d'
                                ), 
                                INTERVAL 3 YEAR
                            ),
                            DATE_SUB(
                                DATE_ADD(
                                    STR_TO_DATE(
                                        DATE_FORMAT(
                                            NOW(),
                                            '%Y-%m-01'
                                        ),
                                        '%Y-%m-%d'
                                    ),
                                    INTERVAL 1 MONTH
                                ),
                                INTERVAL 1 DAY
                            )";
    ($$vars{y3_range_begin}, $$vars{y3_range_end}) = $dbh->selectrow_array( $statement);
    
    
    $statement = 'SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), "%Y-%m-%d"), DATE_FORMAT(NOW(), "%Y-%m-%d")';
    ($$vars{d_range_begin}, $$vars{d_range_end}) = $dbh->selectrow_array( $statement);

    $tt->process('powerstat.tt', $vars) || die $tt->error();
  
    exit 0;
  
}




sub send_json_data{	
    # ссылка на массив или хэш
    my $ref = shift;

    my $json_xs = JSON::XS->new();
    $json_xs->ascii(1);
    
    my $outdata = $json_xs->encode( $ref );
    
    print "Status: 200 OK\n";
    print "Content-Length: " . length($outdata) . ";\n";
    print "Content-type: application/json\n\n";
    
    print $outdata;
    
    exit(0);
}

sub get_data_from_json{
    my $jsontext = shift;
 
    decode_entities($jsontext);
 
    my $json_xs = JSON::XS->new();
    $json_xs->ascii(1);
  
    return $json_xs->decode($jsontext);
}

sub response{
    my ($type, $message) =@_;
    
    
    if( $type eq 'fail'){
        print "Status: 400 Error\n";
        print "Content-type: text/html\n\n";
    }
    elsif( grep{ $type eq $_ }('ok', 'lastmsg', 'setdefault')){
        print "Status: 200 OK\n";
        print "Content-type: text/html\n\n";
    }
    elsif( $type eq 'warn' ){
        print "Status: 444 OK\n"; # why not 444? I like that!
        print "Content-type: text/html\n\n";
    }
    elsif( $type eq 'logonly'){
        return;
    }
    else{
        return;
    }
    
    if( $type eq 'setdefault' ){
        print $message;
    }else{
        print qq^<span>$message</span>^;
    }
    
    exit if grep{/^$type$/}(qw(fail warn lastmsg setdefault)) ;
}
