#!/usr/bin/perl
use warnings;
use strict;
use autodie;
use Data::Dump qw(dump);

my $filter = join(' ',@ARGV);

my $offsets;

open(my $o, '<', 'gnuplot/offset.txt');
while(<$o>) {
	chomp;
	my ( $pn, $o_x, $o_y ) = split(/ /,$_);
	$offsets->{$pn} = [ $o_x, $o_y ];
}
close($o);

my $time_limit;

open(my $o, '<', 'gnuplot/time.txt');
while(<$o>) {
	chomp;
	my ( $pn, $time ) = split(/ /,$_, 2);
	$time_limit->{$pn} = $time;
}
close($o);

my $sql;
my @sql_location;
my $view2number;

open(my $n, '<', 'subvision-numbers.txt');
while(<$n>) {
	chomp;
	my ( $number, $pn, $imei, $name ) = split(/ /,$_);

	if ( $filter && $name !~ m/$filter/i ) {
		warn "SKIP $number $pn\n";
		next;
	}

	my $view = 'sub_' . $number;
	$view =~ s/\W+/_/g;

	$view2number->{$view} = $number;

	my $offset_x = $offsets->{$pn}->[0] // 0;
	my $offset_y = $offsets->{$pn}->[1] // 0;

	my $time = $time_limit->{$pn} // '';

	if ( ! defined( $sql->{$view} ) ) {
		$sql->{$view} = qq{
create or replace view $view as
select time, pn, x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, power_source_voltage, sensor_temperature, signal_strength
from zc
where pn = $pn $time
};
	} else {
		$sql->{$view} .= qq{
union
select time, pn, x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, power_source_voltage, sensor_temperature, signal_strength
from zc
where pn = $pn $time
};
	}

	push @sql_location, qq{
select 
	to_char(time, 'YYYY-MM-DD HH24:MI:SS') as time, x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, sensor_temperature, power_source_voltage, zc.pn
from zc
where pn = $pn $time
};

}
close($n);

open(my $sql_fh, '>', "/tmp/$filter.sql");
open(my $shell_fh, '>', "/tmp/$filter.sh");
open(my $html_fh, '>', "/tmp/$filter.html");

print $html_fh qq{<html>
<head>
	<meta charset=utf-8 />
	<title>$filter</title>
</head>
<body>

	<p>
	<img src=logo-color.png>

};

foreach my $view ( sort keys %$sql ) {
	print $sql_fh "drop view $view ;\n";
	print $sql_fh $sql->{$view} . ";\n";
	print $sql_fh "grant select on $view to grafana ;\n\n";

	print $shell_fh "ln -s sub.cgi $view.cgi\n";

	print $html_fh qq{
	<h1>$view2number->{$view}</h1>
	<img src="cgi-bin/$view.cgi" width="100%">
};
}

print $html_fh qq{
	</p>

</body>
};

print $sql_fh qq{
drop view sub_$filter ;
create or replace view sub_$filter as
}, join(' union ', @sql_location) . qq{
	order by 1 asc
};
