#! /usr/bin/perl
#
# Archive extract utility for Vargus
##################
#    Copyright (C) 2010-2012 Michael A. Kangin <mak@complife.ru>
#
#    Copyright: Vargus is under GNU GPL, the GNU General Public License.
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; version 2 dated June, 1991.
#
#    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 General Public License for more details.
#
#    http://www.gnu.org/licenses/gpl-2.0.html
#



use strict;
use warnings;

use Getopt::Long;
use Time::Local qw(timelocal);
use File::Temp qw(tempfile);
use DBI;
use Sys::Syslog qw(LOG_DAEMON LOG_ERR LOG_WARNING LOG_NOTICE LOG_INFO LOG_DEBUG);
use IO::Handle;

use Vargus::Common;
#use Vargus::Objects;


my $tmp_dir = '/var/tmp';
our $conf_dir = "/etc/vargus";
my $config_file = $conf_dir . "/get-archive.cfg";
my $iframe_compensation_time;

my $max_sub_time = 300;
my $sub_sync_factor = 1.0006;
my $cheque_time_before = 2;
my $cheque_time_after = 10;

my @command_line = @ARGV;
my %sql_access;
my (
	$start_time,
	$end_time,
	$object,
	$help_me,
	$query_range,
	$video_type,
	$query_alerts,
	$query_cheques,
	$cheque_no,
	$cheque_id,
	$cheque_text,
	$alert_id
);
my ($dbh, $sth);
my $progress_handler;
my $progress_file;
my $sql_query;
my @events;



sub prepare_to_die {
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	close($progress_handler) if $progress_handler && $progress_handler->opened();
	if ($progress_file && -e $progress_file) {
		unlink $progress_file;
	}
	die;
}

sub progress {
	my $procent = shift;
	truncate($progress_handler, 0);
	seek($progress_handler, 0, 0);
	print($progress_handler $procent);
}

sub get_events {
	my $start_time = shift;
	my $end_time = shift;

	my $start_time_string = sprintf("%04d-%02d-%02d %02d:%02d:%02d", 
		sub {($_[5]+1900, $_[4]+1, $_[3], $_[2], $_[1], $_[0])}->(localtime($start_time)));
	my $end_time_string = sprintf("%04d-%02d-%02d %02d:%02d:%02d", 
		sub {($_[5]+1900, $_[4]+1, $_[3], $_[2], $_[1], $_[0])}->(localtime($end_time)));

	$sql_query = "select id, UNIX_TIMESTAMP(timestamp), time_ms, event from events where camera = '$object'";
	$sql_query .= " and timestamp <= '$end_time_string' and timestamp >= '$start_time_string' order by timestamp, time_ms";

	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or s_log(LOG_WARNING, "Can't execute SQL query $sql_query");


	if ($sth->rows) {
		while (my ($id, $time, $ms, $event) = $sth->fetchrow_array) {
			my %e_record = (
				id => $id,
				time => $time,
				ms => $ms,
				event => $event
			);
			push(@events, {%e_record});
		}
	}
}

sub configure {
	my @cfg_body = ();
	my @main_section = ();

	open(CFG, $config_file) or log_n_die("Error reading config file $config_file");
	chomp(@cfg_body = <CFG>);
	close(CFG);

	@main_section = expand_macroses(get_cfg_section("main", @cfg_body));

	$sql_access{host} = get_option("sql-host", @main_section);
	$sql_access{db} = get_option("sql-db", @main_section) 
		or log_n_die("No SQL database specified");
	$sql_access{user} = get_option("sql-user", @main_section) 
		or log_n_die("No SQL user specified");
	$sql_access{password} = get_option("sql-password", @main_section) 
		or log_n_die("No SQL password specified");

	$sql_access{dsn} = "DBI:mysql:$sql_access{db}";
}


sub usage {
	my $message = shift;
	my $help = << "END";
Usage:
$0 
     --start sec,min,hour,day,month,year --end sec,min,hour,day,month,year 
	{[ --type { flv | mp4 }] | --query-alerts | --query-cheques } 
	--object camera-object
     This give you archive file, or list alerts for specified time range.
 OR
     --query-range --object camera-object 
     This show possible archive range for the camera
 OR
     --query-cheques --object camera-object --cheque cheque-no
     This show all cheques with this number for the camera
 OR
     --cheque cheque-no --object camera-object [--text]
     This give you archive file for cheque with number cheque-no
     or text of this cheque.
 OR
     --cheque-id cheque-id [--text]
     This give you archive file for cheque with id cheque-id
     or text of this cheque.
 OR
     --alert alert-id
     This give you archive file for alert with ID alert-id
 OR
     --help
     This show you this help.

END
	print("\n") if $message;
	s_log(LOG_ERR, "$message") if $message;
	print("\n") if $message;
	print($help);
	die;
}
	

GetOptions(     'start=s' => \$start_time,
		'end=s' => \$end_time,
		'type=s' => \$video_type,
		'object=s' => \$object,
		'query-range' => \$query_range,
		'query-alerts' => \$query_alerts,
		'query-cheques' => \$query_cheques,
		'alert=i' => \$alert_id,
		'cheque=s' => \$cheque_no,
		'cheque-id=i' => \$cheque_id,
		'text' => \$cheque_text,
		'help'	=> \$help_me);

usage if $help_me;

configure;	

unless ($query_range || $alert_id || $cheque_no || $cheque_id || ($query_cheques && $cheque_no)) {
	usage("No start time (" . join(" ", @command_line) . ")") unless $start_time;
	usage("No end time (" . join(" ", @command_line) . ")") unless $end_time;
	usage("No object (" . join(" ", @command_line) . ")") unless $object;
	usage("Wrong parameters (" . join(" ", @command_line) . ")") unless $start_time && $end_time && $object;

	my @date;

	for ($start_time, $end_time) {
		$_ = int($_ + 0.5) if /^\d+\.\d+$/;
		/^(\d+,){5}\d+$/ or /^\d+$/ or usage("Possibly bad time value: $_");
		unless (/^\d+$/) {
			@date = ();
			@date = split(',');
			$date[4]--;
			$_ = timelocal(@date);
		}
	}

	usage("Wrong parameters (recognize: start_time=$start_time; end_time=$end_time; object=$object") unless $start_time && $end_time && $object;
	log_n_die("--start value is greater than --end value") if $start_time > $end_time;
}

if ($video_type) {
	$video_type eq "flv" or $video_type eq "mp4" or usage("Wrong video type $video_type");
} else {
	$video_type = "mp4";
}


$dbh = DBI->connect($sql_access{dsn}, $sql_access{user}, $sql_access{password}) 
	or log_n_die("Can't connect to mysql base");


if ($query_range) {
	$sql_query = "select UNIX_TIMESTAMP(start_time) from archive where status >= 8 and camera = '$object' " . 
		"ORDER BY UNIX_TIMESTAMP(start_time) LIMIT 1";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	log_n_die("No results ($sql_query)") unless $sth->rows;
	my ($start) = $sth->fetchrow_array;

	$sql_query = "select UNIX_TIMESTAMP(end_time) from archive where status >=8 and camera = '$object' " . 
		"ORDER BY UNIX_TIMESTAMP(end_time) DESC LIMIT 1";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	log_n_die("No results ($sql_query)") unless $sth->rows;
	my ($end) = $sth->fetchrow_array;
	
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	print($start . "\n");
	print($end . "\n");
	exit;
}

if ($query_alerts) {
	$sql_query = "select id, UNIX_TIMESTAMP(start_time), message from alerts where camera = '$object' " .
		"and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time" . 
		" ORDER BY UNIX_TIMESTAMP(start_time);";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No results ($sql_query)") unless $sth->rows;

	while (my ($id, $start, $message) = $sth->fetchrow_array) {
		print("$id;$start;$message\n");
	}
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}


if ($query_cheques) {
	usage("No object (" . join(" ", @command_line) . ")") if $cheque_no && ! $object;
	my $like_search;

	if ($cheque_no and $like_search = grep(/[*?]/, $cheque_no)) {
		$cheque_no =~ tr/*?/%_/;
	}

	my $select_part = $cheque_no ? 
			($like_search ? 
				"and number like '$cheque_no'" :
				"and number='$cheque_no'") . " and end_time is not null" :
			"and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time";

	$sql_query = "select id, number, UNIX_TIMESTAMP(start_time), sum from cheques where camera = '$object' " .
		$select_part . " ORDER BY UNIX_TIMESTAMP(start_time);";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No results ($sql_query)") unless $sth->rows;

	while (my ($id, $cheque_no, $start, $sum) = $sth->fetchrow_array) {
		print("$id;$cheque_no;$start;$sum\n");
	}
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}


if ($alert_id) {
	$sql_query = "select camera, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) from alerts " .
		"where id=$alert_id;";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("Alert with ID $alert_id not found") unless $sth->rows;

	($object, $start_time, $end_time) = $sth->fetchrow_array;
	$progress_file = "/tmp/progress.alert-$alert_id.tmp";
} elsif ($cheque_no || $cheque_id) {
	usage("No object (" . join(" ", @command_line) . ")") if $cheque_no && ! $object;

	if ($cheque_id) {
		$sql_query = "select id,camera,UNIX_TIMESTAMP(start_time),UNIX_TIMESTAMP(end_time) from cheques " .
			"where id='$cheque_id' and end_time is not null";
	} elsif ($cheque_no) {
		$sql_query = "select id,camera,UNIX_TIMESTAMP(start_time),UNIX_TIMESTAMP(end_time) from cheques " .
			"where number='$cheque_no' and camera='$object' and end_time is not null order by id desc limit 1";
	}


	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	unless ($sth->rows) {
		log_n_die("Cheque number $cheque_no not found for camera $object") if $cheque_no;
		log_n_die("Cheque id $cheque_id not found") if $cheque_id;
	}


	($cheque_id, $object, $start_time, $end_time) = $sth->fetchrow_array;
	$sth->finish if $sth;

	$sql_query = "select UNIX_TIMESTAMP(start_time) from cheques where camera='$object' and id > $cheque_id " .
		"and start_time < FROM_UNIXTIME(" . ($end_time + $cheque_time_after) . ") order by id limit 1";

	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	my $next_start_time = 0;
	($next_start_time) = $sth->fetchrow_array;
	$sth->finish if $sth;

	$start_time -= $cheque_time_before;
	$end_time = $next_start_time ? $next_start_time - 2 : $end_time + $cheque_time_after;

	if ($cheque_text) {
		get_events($start_time, $end_time);
		unless (@events) {
			log_n_die("No events found for cheque $cheque_no and camera $object") if $cheque_no;
			log_n_die("No events found for cheque id $cheque_id") unless @events;
		}
		print sprintf("%04d-%02d-%02d", sub {($_[5]+1900, $_[4]+1, $_[3])}->(localtime($start_time))) . "\n";
		foreach (@events) {
			print sprintf("%02d:%02d:%02d", sub {($_[2], $_[1], $_[0])}->(localtime(${$_}{time})));
			print "   ${$_}{event}\n";
		}
		$sth->finish if $sth;
		$dbh->disconnect if $dbh;
		exit;
	}



	$progress_file = "/tmp/progress.cheque-$cheque_id.tmp";
} else {
	$progress_file = "/tmp/progress.$start_time-$end_time.tmp";
}

open($progress_handler, "> $progress_file");
$progress_handler->autoflush();
progress(0);

$sql_query = "select filename, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) from archive where status >= 8";
$sql_query .=  " and camera = '$object'";
$sql_query .=  " and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time" unless $query_range;
$sql_query .=  " ORDER BY UNIX_TIMESTAMP(start_time);";

$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
$sth->execute or log_n_die("Can't execute SQL query $sql_query");

log_n_die("No results ($sql_query)") unless $sth->rows;

my ($results_start, $results_end, @results_files); 
while (my ($filename, $start, $end) = $sth->fetchrow_array) {
	push(@results_files, $filename);
	$results_start = $start unless $results_start;
	$results_end = $end;
}

progress(5);

#if ($query_range) {
#	print($results_start . "\n");
#	print($results_end . "\n");
#	exit;
#}

get_events($start_time - 20, $end_time);

$sth->finish if $sth;
$dbh->disconnect if $dbh;

progress(10);

my ($tmp1, $tmp2, $tmp3);

$tmp1 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
$tmp2 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
$tmp3 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
#$tmp2 .= ".$video_type";

my ($oldout, $olderr);
open($oldout, ">&STDOUT");
open($olderr, ">&STDERR");
open(STDOUT, '>>', $tmp1);
open(STDERR, '>', "/dev/null");
select STDERR; 
$| = 1;
select STDOUT; 
$| = 1;

my @ffmpeg_parameters;

# First step: join all needed video fragments into ts container
my $progress_weight = @results_files ? 20 / ($#results_files + 1) : 0;
my $vfile_counter;
foreach (@results_files) {
	@ffmpeg_parameters = split(' ', "-i $_ -vcodec copy -bsf h264_mp4toannexb -f mpegts -");
	system('/usr/bin/avconv', @ffmpeg_parameters);
	print($olderr "Processing file: $_\n");
	$vfile_counter++;
	progress(10 + int($vfile_counter * $progress_weight));
	s_log(LOG_DEBUG, "Processing file: $_");
}

open(STDOUT, ">&", $oldout);

progress(30);
# Second step - assemble TS stream into MP4 container
@ffmpeg_parameters = split(' ', "-i $tmp1 -vcodec copy -f mp4 $tmp2");
system('/usr/bin/avconv', @ffmpeg_parameters);

progress(50);
# Third step - calculate iframe_compensation_time for video always start from i_frame;
my $skip = $start_time - $results_start;

my $ffmpeg_summary;
open(STDERR, ">", $tmp1);
do {
	truncate(STDERR, 0);
	seek(STDERR,0,0);
	@ffmpeg_parameters = split(' ', "-y -i $tmp2 -vcodec copy -ss $skip -t 1.5 -f mp4 $tmp3");
	system('/usr/bin/avconv', @ffmpeg_parameters);
	open(FFMPEG, $tmp1);
	my @ffmpeg_output = <FFMPEG>;
	close(FFMPEG);
	$ffmpeg_summary = $ffmpeg_output[$#ffmpeg_output];
} while (--$skip > 0 and $ffmpeg_summary =~ /inf%$/);

if ($skip > 0) {
	$skip++;
	do {
		truncate(STDERR, 0);
		seek(STDERR,0,0);
		$skip += 0.1;
		@ffmpeg_parameters = split(' ', "-y -i $tmp2 -vcodec copy -ss $skip -t 1.5 -f mp4 $tmp3");
		system('/usr/bin/avconv', @ffmpeg_parameters);
		open(FFMPEG, $tmp1);
		my @ffmpeg_output = <FFMPEG>;
		close(FFMPEG);
		$ffmpeg_summary = $ffmpeg_output[$#ffmpeg_output];
	} until ($ffmpeg_summary =~ /inf%$/);
	$skip -= 0.1;
}

close(STDERR);

$iframe_compensation_time = $start_time - $results_start - $skip;

progress(60);

# Fourth step - cut desired video fragment end put it into $video_type container

my $duration = $end_time - $start_time + $iframe_compensation_time;
@ffmpeg_parameters = split(' ', "-y -i $tmp2 -vcodec copy -ss $skip -t $duration -f $video_type $tmp3");
system('/usr/bin/avconv', @ffmpeg_parameters);

open(STDERR, ">&", $olderr);
close($oldout);
close($olderr);

unlink($tmp1);
unlink($tmp2);

my $real_start = $results_start + $skip;
my $export_file = "${object}_(date_of:$real_start)-(time_of:$real_start)";
$export_file .= "_(date_of:$end_time)-(time_of:$end_time).$video_type";
$export_file = expand_macroses($export_file);

rename($tmp3, "$tmp_dir/$export_file");

progress(80);
if (@events) {
	my ($sub_cnt, $sub_time, $sub_next_time, $sub_duration, $sub_end_time, 
		$sub_start, $sub_end, $sub_offset, $sub_timestamp,
		@sub_text, $sub_file, $sub_repeat);

	my $tmp4 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
	open(EVENTS, "> $tmp4");
	if ($video_type eq "flv") {
		print(EVENTS '<?xml version="1.0" encoding="UTF-8"?>' . "\n");
		print(EVENTS '<tt xml:lang="en" xmlns="http://www.w3.org/2006/10/ttaf1" xmlns:tts="http://www.w3.org/2006/10/ttaf1#styling">' . "\n");
		print(EVENTS "<body><div xml:lang='ru'>\n");
	}

	$progress_weight = @events ? 10 / ($#events + 1) : 0;
	for (my $e_cnt = 0; $e_cnt <= $#events; $e_cnt++) {
		progress(80 + int($e_cnt * $progress_weight));
		next until ${$events[$e_cnt]}{time};
		$sub_time = ${$events[$e_cnt]}{time} + ${$events[$e_cnt]}{ms} / 1000000 - $real_start;

		$sub_next_time = ${$events[$e_cnt + 1]}{time} ? ${$events[$e_cnt + 1]}{time} : 0;
		$sub_next_time += ${$events[$e_cnt + 1]}{ms} ? ${$events[$e_cnt + 1]}{ms} / 1000000 : 0;

		$sub_next_time = $end_time until $sub_next_time;
		$sub_next_time -= $real_start;

		next if $sub_time < 0;
		next if $sub_next_time < $sub_time or $sub_next_time < 0;

		$sub_timestamp = sprintf("%02d:%02d:%02d,%03d", 
			sub {($_[2], $_[1], $_[0])}->(localtime(int($sub_time + $real_start))),
			($sub_time + $real_start - int($sub_time + $real_start)) * 1000);


		${$events[$e_cnt]}{event} =~ s/(.*?) ([^\dx ]{6}.*)/$1\n$2\n&nbsp;/ if ${$events[$e_cnt]}{event} =~ /^[+-]/;
		push(@sub_text, "$sub_timestamp  " . ${$events[$e_cnt]}{event});
		shift @sub_text if $#sub_text > 5;

#		print "<=== $sub_time : " . ($sub_time + $sub_duration) . " ($sub_duration) " . ${$events[$e_cnt]}{event} . "\n";

		$sub_time *= $sub_sync_factor;
		$sub_next_time *= $sub_sync_factor;
		$sub_time -= 0.5;
		$sub_next_time -= 0.5;

		$sub_duration = $sub_next_time - $sub_time;
		next if $sub_duration < 0.1;

		$sub_offset = $sub_time;
		$sub_offset = 0 if $sub_offset < 0;
		$sub_end_time = $sub_next_time;

		while ($sub_offset < $sub_end_time) {
			last if $sub_offset + $real_start > $end_time;

			$sub_cnt++;
			$sub_start = sprintf("%02d:%02d:%02d,%03d",
				sub {($_[2], $_[1], $_[0])}->(gmtime(int($sub_offset))), 
				($sub_offset - int($sub_offset)) * 1000);
			$sub_duration = $sub_end_time - $sub_offset > 1 ? 1 : $sub_end_time - $sub_offset;

#			$sub_duration -= 0.001;

			if ($video_type eq "mp4") {
				$sub_end = sprintf("%02d:%02d:%02d,%03d",
					sub {($_[2], $_[1], $_[0])}->(gmtime($sub_offset + $sub_duration)), 
					($sub_offset + $sub_duration - int($sub_offset + $sub_duration)) * 1000);

				print(EVENTS "$sub_cnt\n");
				print(EVENTS "$sub_start --> $sub_end\n");
				foreach (@sub_text) {
					print(EVENTS "$_\n");
				}
				print(EVENTS "\n");
		
			} elsif ($video_type eq "flv") {
				$sub_duration = sprintf("%02d:%02d:%02d,%03d",
					sub {($_[2], $_[1], $_[0])}->(gmtime($sub_duration)),
					($sub_duration - int($sub_duration)) * 1000);
				$sub_duration =~ s/^[0:]*//g;
				print(EVENTS "<p begin='$sub_offset' dur='$sub_duration'>\n");
				foreach (@sub_text) {
					my $sub_element = $_;
					$sub_element =~ s/</{/g;
					$sub_element =~ s/>/}/g;
					$sub_element =~ s/&/*/g;
					$sub_element =~ s/\*nbsp;//g;
					$sub_element =~ s/\n/<br \/>/g;
					print(EVENTS "$sub_element<br />\n");
				}
				print(EVENTS "</p>\n");
			}
			
			$sub_offset++;
		}
	}

	print(EVENTS "</div></body></tt>\n") if $video_type eq "flv";
	close(EVENTS);

	$sub_file = $export_file;

	if ($video_type eq "flv") {
		$sub_file =~ s/$video_type$/xml/;
		rename($tmp4, "$tmp_dir/$sub_file");
	}

	progress(90);
	if ($video_type eq "mp4") {
		$sub_file =~ s/$video_type$/srt/;
		rename($tmp4, "$tmp_dir/$sub_file");
		system("cd $tmp_dir; MP4Box -noprog $export_file -add $sub_file >&2") == 0 
			or s_log(LOG_ERR, "Error embeding subtitles into videocontainer");
		unlink "$tmp_dir/$sub_file";
	}
}

progress(100);
close($progress_handler);

s_log(LOG_INFO, "Requested archive records: $export_file");
print("$export_file" . "\n");

