#!/usr/bin/perl -w
use DBI;
use strict;
use POSIX;
use FindBin;            	# locate this script
use lib "$FindBin::Bin";    # include script directory
use Config::Simple;

## Dependencies
#
# apt-get install libconfig-simple-perl libdbi-perl libdbd-mysql-perl gnuplot


my $DBHOST = "";
my $DBNAME = "";
my $DBUSER = "";
my $DBPASS = "";

my $config_file_base = "power_db";
my $fileprefix = "spot";
my $plot_max_power = 13000;
my $plot_max_power_detail = 750;

my $gnuplot = "/usr/bin/gnuplot";
my $debug = 1;
my $font = "font '/usr/share/fonts/dejavu/DejaVuSans.ttf' 8";

sub read_config_file;
read_config_file;
my $db = DBI->connect("DBI:mysql:$DBNAME:$DBHOST", $DBUSER, $DBPASS);


# Table, parameter 0
#
my $TABLE = $ARGV[0];

# Plot directory, parameter 1
#
my $plotdir = $ARGV[1];

# Timezone, parameter 3
#
my $tz = "system";
my $time = "time";
if (exists($ARGV[3]) ) { $tz= $ARGV[3]; }

# Date, parameter 2
#
#$ARGV[2] = "+1";
my $day_to_plot = "+1";
if (!exists($ARGV[2])) { $ARGV[2] = "+1"; }
if (exists($ARGV[2]) ) {
    if ( ! ($ARGV[2] eq  "now()")) {
		# "-n" to plat today - n days
		if ($ARGV[2] =~ /^[+-]?\d+\z/) {
			$DBI::result = $db->prepare("SELECT DATE(now() + INTERVAL " . int($ARGV[2]) . " DAY)");
			$DBI::result->execute();
			my ($d) = $DBI::result->fetchrow_array;
			$day_to_plot = "'$d'";
		} else {
	    	$day_to_plot = "'$ARGV[2]'";
		}
    }
}
#print "date: $day_to_plot\n";


my $data_day1 = "$plotdir/$fileprefix" . "_day1_$TABLE.dat";
my $data_day2 = "$plotdir/$fileprefix" . "_day2_$TABLE.dat";
my $data_day3 = "$plotdir/$fileprefix" . "_day3_$TABLE.dat";
my $data_day4 = "$plotdir/$fileprefix" . "_day4_$TABLE.dat";

my $data_week1 = "$plotdir/$fileprefix" . "_week1_$TABLE.dat";
my $data_week2 = "$plotdir/$fileprefix" . "_week2_$TABLE.dat";
my $data_week3 = "$plotdir/$fileprefix" . "_week3_$TABLE.dat";
my $data_week4 = "$plotdir/$fileprefix" . "_week4_$TABLE.dat";

my $data_month1 = "$plotdir/$fileprefix" . "_month1_$TABLE.dat";
my $data_month2 = "$plotdir/$fileprefix" . "_month2_$TABLE.dat";
my $data_month3 = "$plotdir/$fileprefix" . "_month3_$TABLE.dat";
my $data_month4 = "$plotdir/$fileprefix" . "_month4_$TABLE.dat";

my $data_year1 = "$plotdir/$fileprefix" . "_year1_$TABLE.dat";
my $data_year2 = "$plotdir/$fileprefix" . "_year2_$TABLE.dat";
my $data_year3 = "$plotdir/$fileprefix" . "_year3_$TABLE.dat";
my $data_year4 = "$plotdir/$fileprefix" . "_year4_$TABLE.dat";

my $query = "SELECT HOUR($time), HOUR($time), MINUTE($time), SECOND($time), date_format($time,'%d'), date_format($time,'%m'), date_format($time,'%Y'), date_format($time,'%W'), date_format($time,'%M'), DAYOFWEEK($time)-1, YEARWEEK($time,3), DAYOFYEAR($time), DAYOFMONTH(LAST_DAY($time)), DAYNAME($time), SE1/100, SE2/100, SE3/100, SE4/100 ";
my ($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $SE1, $SE2, $SE3, $SE4, $time_index);

# min/max query
#my $range_query  = "SELECT FLOOR(MAX($t_corr/10)-MIN($t_corr/10))*10 AS range1, CEIL(MAX($t_corr/10)-MIN($t_corr/10))*10 AS range2, FLOOR(MIN($t_corr/5))*5 AS min, CEIL(MAX($t_corr/5))*5 AS max FROM $TABLE ";
#my $range_query = "select ceil(max(power)/100)*100 as pmax, floor(min(power)/100)*100 as pmin, ceil(max(counter)/1000) as cmax, floor(min(counter)/1000) as cmin ";
my $range_query = "select ceil(max(SE4/100)/100)*100 as pmax, floor(min(SE4/100)/100)*100 as pmin, ceil(max(SE4/100)/1000) as cmax, floor(min(SE1/100)/1000) as cmin ";



my $clause_daily = ", (unix_timestamp($time)-unix_timestamp(date($day_to_plot)))/3600 AS timeindex FROM $TABLE";
my $clause_daily1 = " $clause_daily WHERE TIMESTAMP($time) > TIMESTAMPADD(MINUTE, -60*2, date($day_to_plot)) AND TIMESTAMP($time) <= TIMESTAMPADD(MINUTE, 60*26, date($day_to_plot)) ";
my $clause_daily2 = " $clause_daily WHERE date(time) = date($day_to_plot)";

my $clause_weekly = ", (unix_timestamp($time)-unix_timestamp(YEARWEEK($day_to_plot,3)))/(3600*7) AS timeindex FROM $TABLE WHERE YEARWEEK($time,3) = YEARWEEK($day_to_plot,3) ";

#my $clause_weekly = "FROM $TABLE WHERE YEARWEEK($time,3) = YEARWEEK($day_to_plot,3) ";

my $clause_monthly = ", (unix_timestamp($time)-unix_timestamp(MONTH($day_to_plot)))/(3600*DAYOFMONTH(LAST_DAY($time))) AS timeindex FROM $TABLE WHERE YEAR($time) = YEAR($day_to_plot) and MONTH($time) = MONTH($day_to_plot)";

my $clause_yearly = ", (unix_timestamp($time)-unix_timestamp(YEAR($day_to_plot)))/3600*365 AS timeindex FROM $TABLE WHERE YEAR($time) = YEAR($day_to_plot) ";

my $end_clause = " ORDER BY $time";

#############################################################################
sub read_config_file;
sub plot_bias;
my @temp_row;
my $line;

read_config_file;

$DBI::result = $db->prepare("SET time_zone = '$tz'");
$DBI::result->execute();
$DBI::result = $db->prepare("SELECT hour(now()), minute(now()), year(now()) ");
$DBI::result->execute();
my ($hour_now, $minute_now) = $DBI::result->fetchrow_array;



### Create day plot
#
open (DAYPLOT1, ">", $data_day1) or die "Can't open $data_day1";
open (DAYPLOT2, ">", $data_day2) or die "Can't open $data_day2";
open (DAYPLOT3, ">", $data_day3) or die "Can't open $data_day3";
open (DAYPLOT4, ">", $data_day4) or die "Can't open $data_day4";
$DBI::result = $db->prepare($query . $clause_daily1 . $end_clause);
#print "$query . $clause_daily . $end_clause \n";
$DBI::result->execute();
#my $day_index = 0;
#my $last_day = 0;
while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $SE1, $SE2, $SE3, $SE4, $time_index) 
= @temp_row;
#	$time_index = $hour + $minute/60 + $second/(60*60) + 0.0000001;
#	if ($last_day && ($last_day != $day)) { $day_index = 24; }
#	$time_index += $day_index;
#	$last_day = $day;
#	$time_index = substr $time_index, 0, 8;
	$line = $time_index . " $t\n";
	print DAYPLOT1 $time_index . " $SE1\n";
	print DAYPLOT2 $time_index . " $SE2\n";
	print DAYPLOT3 $time_index . " $SE3\n";
	print DAYPLOT4 $time_index . " $SE4\n";
}
	print DAYPLOT1 $time_index+1 . " $SE1\n";
	print DAYPLOT2 $time_index+1 . " $SE2\n";
	print DAYPLOT3 $time_index+1 . " $SE3\n";
	print DAYPLOT4 $time_index+1 . " $SE4\n";

if (not defined $t) { exit; }
close DAYPLOT1;
close DAYPLOT2;
if (!($year)) { exit; } # Quit if no data

# Get date info for plot, last data point is next day
$DBI::result = $db->prepare($query . $clause_daily2 . $end_clause);
$DBI::result->execute();
@temp_row = $DBI::result->fetchrow_array;
($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth) = @temp_row;

my ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_daily2, $plot_max_power);
#$pmin = 100;
#$pmax = 500;
#$y1tic = 10;

open (GPLOT, ">$plotdir/day.gplot") or die "Can't open $plotdir/day.gplot";
print GPLOT "set terminal png $font size 768, 500 \n";
print GPLOT "set xtics 1, 1, 23\n";
print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
#print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
#print GPLOT "set y2range[$cmin:$cmax]\n"; 
print GPLOT "set xrange [0:24]\n";
print GPLOT "set yrange [" , $pmin , ":" , $pmax , "]\n";
print GPLOT "set grid xtics ytics\n";
print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month$day.png\"\n";
print GPLOT "set title \"Nordpool Spot Prices SEK/MWh $dayname $monthname $day $year\"\n";
print GPLOT "plot \"$data_day1\" axes x1y1 ti \"SE1\" with steps, ";
print GPLOT " \"$data_day2\" axes x1y1 ti \"SE2\" with steps, ";
print GPLOT " \"$data_day3\" axes x1y1 ti \"SE3\" with steps, ";
print GPLOT " \"$data_day4\" axes x1y1 ti \"SE4\" with steps\n";
close (GPLOT);
`$gnuplot $plotdir/day.gplot &>/dev/null`;



# Weekly plot
#
if ( ($minute_now < 5 or $minute_now > 55) or $debug) {
	open (WEEKPLOT1, ">", $data_week1) or die "Can't open $data_week1";
	open (WEEKPLOT2, ">", $data_week2) or die "Can't open $data_week2";
	open (WEEKPLOT3, ">", $data_week3) or die "Can't open $data_week3";
	open (WEEKPLOT4, ">", $data_week4) or die "Can't open $data_week4";
	$DBI::result = $db->prepare($query . $clause_weekly . $end_clause);
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $SE1, $SE2, $SE3, $SE4, $time_index) = @temp_row;
	    if ($dow == 0) {$dow = 7;}
	    $time_index = $dow + $hour/24 + $minute/(60*24) + $second/(60*60*24) + 0.0000001;
	    $time_index = substr $time_index, 0, 8;
	    $line = $time_index . " $t\n";
#	    print WEEKPLOT $line;
#		print WEEKPLOT2 $time_index . " $counter\n";
		print WEEKPLOT1 $time_index . " $SE1\n";
		print WEEKPLOT2 $time_index . " $SE2\n";
		print WEEKPLOT3 $time_index . " $SE3\n";
		print WEEKPLOT4 $time_index . " $SE4\n";

	}
	close WEEKPLOT1;
	close WEEKPLOT2;

	my $xlegend = "";
	$DBI::result = $db->prepare($query . $clause_weekly . $end_clause);
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
		my ($dn, $d, $wd) = @temp_row;
		$wd++;
		my $wd2 = $wd + 0.5;
		$xlegend = $xlegend . "'$dn $d' $wd, '' $wd2" ;
		if ($wd < 7) { $xlegend .= ", "; }		
	}
	my ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_weekly, $plot_max_power);
#$pmin = 100;
#$pmax = 500;
#$y1tic = 10;

	open (GPLOT, ">$plotdir/week.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500 \n";
	print GPLOT "set xtics ('Mon' 1, '' 1.5, 'Tue' 2, '' 2.5, 'Wed' 3, '' 3.5, 'Thu' 4, '' 4.5, 'Fri' 5, '' 5.5, 'Sat' 6, '' 6.5, 'Sun' 7, '' 7.5)\n";
	print GPLOT "set xrange [1:7+1]\n";
	print GPLOT "set yrange[$pmin:$pmax]\n";
	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
#	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
#	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_" . $week . "w.png\"\n";
	print GPLOT "set title \"Nordpool Spot Prices SEK/MWh " . substr($week,0,4) . " ". substr($week,4,2) . "\"\n";
	print GPLOT "plot \"$data_week1\" axes x1y1 ti \"SE1\" with steps, ";
	print GPLOT " \"$data_week2\" axes x1y1 ti \"SE2\" with steps, ";
	print GPLOT " \"$data_week3\" axes x1y1 ti \"SE3\" with steps, ";
	print GPLOT " \"$data_week4\" axes x1y1 ti \"SE4\" with steps\n";

	close (GPLOT);
	`$gnuplot $plotdir/week.gplot &>/dev/null`;
#	`rm -f $plotdir/week.gplot $data_week`;




# Monthly plot
#
	open (MONTHPLOT1, ">", $data_month1) or die "Can't open $data_month1";
	open (MONTHPLOT2, ">", $data_month2) or die "Can't open $data_month2";
	open (MONTHPLOT3, ">", $data_month3) or die "Can't open $data_month1";
	open (MONTHPLOT4, ">", $data_month4) or die "Can't open $data_month2";
	$DBI::result = $db->prepare($query . $clause_monthly . $end_clause);
	$DBI::result->execute();
	while(@temp_row = $DBI::result->fetchrow_array) {
	($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $SE1, $SE2, $SE3, $SE4, $time_index) = @temp_row;
	    $time_index = $day + $hour/24 + $minute/(60*24) + $second/(60*60*24) + 0.0000001;
	    $time_index = substr $time_index, 0, 8;
	    $line = $time_index . " $t\n";
#	    print MONTHPLOT1 $line;
		print MONTHPLOT1 $time_index . " $SE1\n";
		print MONTHPLOT2 $time_index . " $SE2\n";
		print MONTHPLOT3 $time_index . " $SE3\n";
		print MONTHPLOT4 $time_index . " $SE4\n";

	}
	close MONTHPLOT1;
	close MONTHPLOT2;
	($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_monthly, $plot_max_power);
#$pmin = 100;
#$pmax = 500;
#$y1tic = 10;

	open (GPLOT, ">$plotdir/month.gplot") or die "Can't open $plotdir/month.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
	print GPLOT "set xtics 0, 1, $daysinmonth\n";
	print GPLOT "set xrange [1:$daysinmonth+1]\n";
#	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
#	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set yrange[$pmin:$pmax]\n";
	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year$month.png\"\n";
	print GPLOT "set title \"Nordpool Spot Prices SEK/MWh $monthname $year\"\n";
	print GPLOT "set pointsize 0.3\n";
	print GPLOT "plot \"$data_month1\" axes x1y1 ti \"SE1\" with steps, ";
	print GPLOT " \"$data_month2\" axes x1y1 ti \"SE2\" with lines, ";
	print GPLOT " \"$data_month3\" axes x1y1 ti \"SE3\" with lines, ";
	print GPLOT " \"$data_month4\" axes x1y1 ti \"SE4\" with lines\n";
	close (GPLOT);
	`$gnuplot $plotdir/month.gplot &>/dev/null`;

}



# Create year plot
#
if ( 1 ) {
	open (YEARPLOT1, ">", $data_year1) or die "Can't open $data_year1";
	open (YEARPLOT2, ">", $data_year2) or die "Can't open $data_year2";
	open (YEARPLOT3, ">", $data_year3) or die "Can't open $data_year1";
	open (YEARPLOT4, ">", $data_year4) or die "Can't open $data_year2";
	$DBI::result = $db->prepare($query . $clause_yearly . $end_clause);
	$DBI::result->execute();

	while(@temp_row = $DBI::result->fetchrow_array) {
		($t, $hour, $minute, $second, $day, $month, $year, $dayname, $monthname, $dow, $week, $doy, $daysinmonth, $dayname2, $SE1, $SE2, $SE3, $SE4, $time_index) = @temp_row;
	    $time_index = $doy + $hour/24 + $minute/(60*24) + $second/(60*60*24) + 0.0000001;
	    $time_index = substr $time_index, 0, 8;
	    $line = $time_index . " $t\n";
#	    print YEARPLOT1 $line;
		print YEARPLOT1 $time_index . " $SE1\n";
		print YEARPLOT2 $time_index . " $SE2\n";
		print YEARPLOT3 $time_index . " $SE3\n";
		print YEARPLOT4 $time_index . " $SE4\n";
	}
	close YEARPLOT1;
	close YEARPLOT2;
	($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic) = plot_bias($range_query . $clause_monthly, $plot_max_power);
	my $leap = 0;
	if ( !($year%4) && ($year%100) || !($year%400) )  { $leap=1; }
#$pmin = 100;
#$pmax = 500;
#$y1tic = 10;

	open (GPLOT, ">$plotdir/year.gplot") or
		die "Can't open $plotdir/year.gplot";
	print GPLOT "set terminal png $font size 768, 500\n";
	print GPLOT "set xtics ('Jan' 1, 'Feb' 32, 'Mar' 60, 'Apr' 91, 'May' 121, 'Jun' 152, 'jul' 182, 'Aug' 213, 'Sep' 244, 'Okt' 274, 'Nov' 305, 'Dec' 335, '' 366+$leap)\n";
    print GPLOT "set xrange [1:366+$leap]\n";
	print GPLOT "set ytics $pmin+$y1tic, $y1tic, $pmax-$y1tic\n";
#	print GPLOT "set y2tics $cmin+$y2tic, $y2tic, $cmax-$y2tic\n";
	print GPLOT "set yrange[$pmin:$pmax]\n";
#	print GPLOT "set y2range[$cmin:$cmax]\n"; 
	print GPLOT "set grid xtics ytics\n";
	print GPLOT "set output \"$plotdir/$fileprefix" . "_$year.png\"\n";
	print GPLOT "set title \"Nordpool Spot Prices SEK/MWh $year\"\n";
#	print GPLOT "set pointsize 0.3\n";
	print GPLOT "plot \"$data_year1\" axes x1y1 ti \"SE1\" with steps, ";
	print GPLOT " \"$data_year2\" axes x1y1 ti \"SE2\" with steps, ";
	print GPLOT " \"$data_year3\" axes x1y1 ti \"SE3\" with steps, ";
	print GPLOT " \"$data_year4\" axes x1y1 ti \"SE4\" with steps\n";
	close (GPLOT);
	`$gnuplot $plotdir/year.gplot &>/dev/null`;
#	`rm -f $plotdir/year.gplot $plotdir/temp_year.dat`;
}

$DBI::result->finish();
$db->disconnect;
undef $db; 



sub plot_bias {
	my ($sql, $plot_pmax)  = @_;
    $DBI::result = $db->prepare($sql);
    $DBI::result->execute();
	my ($pmax, $pmin, $cmax, $cmin) = $DBI::result->fetchrow_array;
	$DBI::result->finish();
	my $y1tic = 10;
	if ( ($pmax-$pmin) > 300) { $y1tic = 50; }

    my $y2tic = 1;
	return ($pmax, $pmin, $y1tic, $cmax, $cmin, $y2tic);
}


# Read config file
#
sub read_config_file {
	my $configfile;
	if (-e "$FindBin::Bin/$config_file_base.conf") {
		$configfile = "$FindBin::Bin/$config_file_base.conf";
	} else {
		if (-e "~/.$config_file_base") {
			$configfile = "~/.$config_file_base";
		} else {
			if (-e "/etc/$config_file_base.conf") {
				$configfile = "/etc/$config_file_base.conf";
			}
		}
	}

	if (!$configfile) { 
		print "Missing config file, the program will look for a file in the following order:\n\n";
		print " 1: Same directory as the program, $FindBin::Bin/$config_file_base.conf\n";
		print " 2: ~/.$config_file_base\n";
		print " 3: /etc/$config_file_base.conf\n\n";
		print "A sample config file would look like this:\n\n";
		print "DBHOST\tlocalhost\nDBNAME\tpower\nDBUSER\tpower_user\nDBPASS\tpower_pass\n\n";
		exit 1;
	}
	my $cfg = new Config::Simple();
	$cfg->read($configfile);
	if ($cfg->param('DBHOST'))  { $DBHOST  = $cfg->param('DBHOST'); }
	if ($cfg->param('DBNAME'))  { $DBNAME  = $cfg->param('DBNAME'); }
	if ($cfg->param('DBUSER'))  { $DBUSER  = $cfg->param('DBUSER'); }
	if ($cfg->param('DBPASS'))  { $DBPASS  = $cfg->param('DBPASS'); }
#	if ($cfg->param('DBTABLE')) { $DBTABLE = $cfg->param('DBTABLE'); }
    undef $cfg;
}