#!/usr/bin/perl
package BackupPC::SearchLib;

use strict;
use BackupPC::CGI::Lib qw(:all);
use BackupPC::Attrib qw(:all);
use DBI;
use DateTime;
use vars qw(%In $MyURL);
use Time::HiRes qw/time/;

my $on_page = 100;
my $pager_pages = 10;

my $dsn = $Conf{SearchDSN};
my $db_user = $Conf{SearchUser} || '';

my $index_path = $Conf{HyperEstraierIndex};
if ($index_path) {
	$index_path = $TopDir . '/' . $index_path;
	$index_path =~ s#//#/#g;
}

my $dbh;

sub get_dbh {
	$dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
	return $dbh;
}

sub getUnits() {
	my @ret;

	my $dbh = get_dbh();
	my $sth = $dbh->prepare(qq{
		SELECT
			shares.id	as id,
			hosts.name || ':' || shares.name as share
		FROM shares
		JOIN hosts on hostid = hosts.id
		ORDER BY share
	} );
	$sth->execute();
	push @ret, { 'id' => '', 'share' => '-'};	# dummy any

	while ( my $row = $sth->fetchrow_hashref() ) {
		push @ret, $row;
	}
	return @ret;
}

sub epoch_to_iso {
	my $t = shift || return;
	my $iso = BackupPC::Lib::timeStamp(undef, $t);
	$iso =~ s/\s/&nbsp;/g;
	return $iso;
}

sub dates_from_form($) {
	my $param = shift || return;

	sub mk_epoch_date($$) {
		my ($name,$suffix) = @_;

		my $yyyy = $param->{ $name . '_year_' . $suffix} || return undef;
		my $mm .= $param->{ $name . '_month_' . $suffix} ||
			( $suffix eq 'from' ? 1 : 12);
		my $dd .= $param->{ $name . '_day_' . $suffix} ||
			( $suffix eq 'from' ? 1 : 31);

		$yyyy =~ s/\D//g;
		$mm =~ s/\D//g;
		$dd =~ s/\D//g;

		my $dt = new DateTime(
			year => $yyyy,
			month => $mm,
			day => $dd
		);
		print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n";
		return $dt->epoch || 'NULL';
	}

	my @ret = (
		mk_epoch_date('search_backup', 'from'),
		mk_epoch_date('search_backup', 'to'),
		mk_epoch_date('search', 'from'),
		mk_epoch_date('search', 'to'),
	);

	return @ret;

}


sub getWhere($) {
	my $param = shift || return;

	my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);

	my @conditions;
	push @conditions, qq{ backups.date >= $backup_from } if ($backup_from);
	push @conditions, qq{ backups.date <= $backup_to } if ($backup_to);
	push @conditions, qq{ files.date >= $files_from } if ($files_from);
	push @conditions, qq{ files.date <= $files_to } if ($files_to);

	print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions);

    	push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'});
	push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'});

	return join(" and ", @conditions);
}


sub getFiles($) {
	my ($param) = @_;

	my $offset = $param->{'offset'} || 0;
	$offset *= $on_page;

	my $dbh = get_dbh();

	my $sql_cols = qq{
		files.id			AS fid,
		hosts.name			AS hname,
		shares.name			AS sname,
		files.backupnum			AS backupnum,
		files.path			AS filepath,
		files.date			AS date,
		files.type			AS type,
		files.size			AS size
	};

	my $sql_from = qq{
		FROM files 
			INNER JOIN shares	ON files.shareID=shares.ID
			INNER JOIN hosts	ON hosts.ID = shares.hostID
			INNER JOIN backups	ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = files.shareID
	};

	my $sql_where;
	my $where = getWhere($param);
	$sql_where = " WHERE ". $where if ($where);

	my $sql_order = qq{
		ORDER BY files.date
		LIMIT $on_page
		OFFSET ?
	};

	my $sql_count = qq{ select count(files.id) $sql_from $sql_where };
	my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order };

	my $sth = $dbh->prepare($sql_count);
	$sth->execute();
	my ($results) = $sth->fetchrow_array();

	$sth = $dbh->prepare($sql_results);
	$sth->execute( $offset );

	if ($sth->rows != $results) {
		my $bug = "$0 BUG: [[ $sql_count ]] = $results while [[ $sql_results ]] = " . $sth->rows;
		$bug =~ s/\s+/ /gs;
		print STDERR "$bug\n";
	}

	my @ret;
      
	while (my $row = $sth->fetchrow_hashref()) {
		push @ret, $row;
	}
     
	$sth->finish();
	return ($results, \@ret);
}

sub getHyperEstraier_url($) {
	my ($use_hest) = @_;

	return unless $use_hest;

	use HyperEstraier;
	my ($index_path, $index_node_url);

	if ($use_hest =~ m#^http://#) {
		$index_node_url = $use_hest;
	} else {
		$index_path = $TopDir . '/' . $index_path;
		$index_path =~ s#//#/#g;
	}
	return ($index_path, $index_node_url);
}

sub getFilesHyperEstraier($) {
	my ($param) = @_;

	my $offset = $param->{'offset'} || 0;
	$offset *= $on_page;

	die "no index_path?" unless ($index_path);

	use HyperEstraier;

	my ($index_path, $index_node_url) = getHyperEstraier_url($index_path);

	# open the database
	my $db;
	if ($index_path) {
		$db = HyperEstraier::Database->new();
		$db->open($index_path, $HyperEstraier::ESTDBREADER);
	} elsif ($index_node_url) {
		$db ||= HyperEstraier::Node->new($index_node_url);
		$db->set_auth('admin', 'admin');
	} else {
		die "BUG: unimplemented";
	}

	# create a search condition object
	my $cond = HyperEstraier::Condition->new();

	my $q = $param->{'search_filename'};
	my $shareid = $param->{'search_share'};

	if (length($q) > 0) {
		# exact match
		$cond->add_attr("filepath ISTRINC $q");

		$q =~ s/(.)/$1 /g;
		# set the search phrase to the search condition object
		$cond->set_phrase($q);
	}

	my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);

	$cond->add_attr("backup_date NUMGE $backup_from") if ($backup_from);
	$cond->add_attr("backup_date NUMLE $backup_to") if ($backup_to);

	$cond->add_attr("date NUMGE $files_from") if ($files_from);
	$cond->add_attr("date NUMLE $files_to") if ($files_to);

	$cond->add_attr("shareid NUMEQ $shareid") if ($shareid);

#	$cond->set_max( $offset + $on_page );
	$cond->set_options( $HyperEstraier::Condition::SURE );
	$cond->set_order( 'date NUMA' );

	# get the result of search
	my @res;
	my ($result, $hits);

	if ($index_path) {
		$result = $db->search($cond, 0);
		$hits = $result->size;
	} elsif ($index_node_url) {
		$result = $db->search($cond, 0);
		$hits = $result->doc_num;
	} else {
		die "BUG: unimplemented";
	}

	# for each document in result
	for my $i ($offset .. ($offset + $on_page - 1)) {
		last if ($i >= $hits);

		my $doc;
		if ($index_path) {
			my $id = $result->get($i);
			$doc = $db->get_doc($id, 0);
		} elsif ($index_node_url) {
			$doc = $result->get_doc($i);
		} else {
			die "BUG: unimplemented";
		}

		my $row;
		foreach my $c (qw/fid hname sname backupnum fiilename filepath date type size/) {
			$row->{$c} = $doc->attr($c);
		}
		push @res, $row;
	}

	return ($hits, \@res);
}

sub getGzipName($$$)
{
	my ($host, $share, $backupnum) = @_;
	my $ret = $Conf{GzipSchema};
	
	$share =~ s/\//_/g;
	$ret =~ s/\\h/$host/ge;
	$ret =~ s/\\s/$share/ge;
	$ret =~ s/\\n/$backupnum/ge;
	
	return $ret;
	
}

sub getBackupsNotBurned() {

	my $dbh = get_dbh();

	my $sql = q{ 
		SELECT 
			backups.hostID AS hostID,
			hosts.name AS host,
			shares.name AS share,
			backups.id AS backupnum,
			backups.type AS type,
			backups.date AS date,
			backups.size AS size
		FROM backups 
		WHERE id not in( select id from backups,archive_backup where archive_backup.backup_id=backups.id)
		ORDER BY backups.date
	};
	my $sth = $dbh->prepare( $sql );
	my @ret;
	$sth->execute();

	while ( my $row = $sth->fetchrow_hashref() ) {
		$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
		$row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024);
		my (undef,undef,undef,undef,undef,undef,undef,$fs_size,undef,undef,undef,undef,undef) = 
	        	stat( $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'.
	        		getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'}));
		$row->{'fs_size'} = $fs_size;
		push @ret, $row;
	}
      
	return @ret;      
}

sub displayBackupsGrid()
  {
      my $retHTML = "";
      
	$retHTML .= <<EOF3;
<script language="javascript" type="text/javascript">
<!--

    function checkAll(location)
    {
      for (var i=0;i<document.forma.elements.length;i++)
      {
        var e = document.forma.elements[i];
        if ((e.checked || !e.checked) && e.name != \'all\') {
            if (eval("document.forma."+location+".checked")) {
                e.checked = true;
            } else {
                e.checked = false;
            }
        }
      }
    }
    
    function sumiraj()
    {
    	var suma = 0;
    	for (var i = 0; i < document.forma.elements.length; i++)    
   		{
   			var e = document.forma.elements[i];
   			if ((e.checked || !e.checked) && e.name != \'all\')
   			{
				if (e.checked)
				{
					var ret = e.name.match("fcb(.*)");
					suma += parseInt(eval("document.forma.fss"+ret[1]+".value"));
					
				}
   			}
    	}
    	document.forma.totalsize.value = suma;
    	return suma;
    }
//-->
</script>      
EOF3
	$retHTML .= q{
		<form name="forma" method="GET" action=};
		$retHTML .= "\"".$MyURL."\"";
		$retHTML .= q{?action=burn>
			<input type="hidden" value="burn" name="action">
			<input type="hidden" value="results" name="search_results">
			<table style="fview" border="0" cellspacing="0" cellpadding="2">
			<tr class="tableheader">
			<td class="tableheader">
				<input type="checkbox" name="allFiles" onClick="checkAll('allFiles');">
			</td>
			<td align="center">Share</td>
			<td align="center">Backup no</td>
			<td align="center">Type</td>
			<td align="center">date</td>
			<td align="center">age/days</td>
			<td align="center">size/MB</td>
			<td align="center">gzip size</td>
			</tr>

			<tr><td colspan=7 style="tableheader">
			<input type="submit" value="Burn selected backups on medium" name="submitBurner">
			</td></tr>
	};

	my @color = (' bgcolor="#e0e0e0"', '');

	my $i = 0;
	my $host = '';

	foreach my $backup ( getBackupsNotBurned() ) {

		if ($host ne $backup->{'host'}) {
			$i++;
			$host = $backup->{'host'};
		}
		my $ftype = "";
	    
		$retHTML .= "<tr" . $color[$i %2 ] . ">";
		$retHTML .= '<td class="fview"><input type="checkbox" name="fcb' .
											$backup->{'hostid'}.'_'.$backup->{'backupnum'} . 
											'" value="' . $backup->{'hostid'}.'_'.$backup->{'backupnum'} .
											'" onClick="sumiraj();"></td>';
	    
		$retHTML .=
			'<td align="right">' . $backup->{'host'} . ':' . $backup->{'share'} . '</td>' .
			'<td align="center">' . $backup->{'backupnum'} . '</td>' .
			'<td align="center">' . $backup->{'type'} . '</td>' .
			'<td align="center">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
			'<td align="center">' . $backup->{'age'} . '</td>' .
			'<td align="right">' . $backup->{'size'} . '</td>' .
			'<td align="right">' . $backup->{'fs_size'} .
			'<input type="hidden" name="fss'.$backup->{'hostid'}.'_'.$backup->{'backupnum'} . '"'.
			'value="'. $backup->{'fs_size'} .'"'.'</td>' .
			"</tr>\n";


	}

	$retHTML .= "</table>";
	$retHTML .= "total gzip size:<input type=\"text\" name=\"totalsize\"><br>";
	$retHTML .= "Note:<input type=\"text\" name=\"note\">";
	$retHTML .= "</form>";
      
	return $retHTML;
}      

sub displayGrid($) {
	my ($param) = @_;

	my $offset = $param->{'offset'};
	my $hilite = $param->{'search_filename'};

	my $retHTML = "";
 
	my $start_t = time();

	my ($results, $files);
	if ($param->{'use_hest'} && length($hilite) > 0) {
		($results, $files) = getFilesHyperEstraier($param);
	} else {
		($results, $files) = getFiles($param);
	}

	my $dur_t = time() - $start_t;
	my $dur = sprintf("%0.4fs", $dur_t);

	my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);

	if ($results <= 0) {
		$retHTML .= qq{
			<p style="color: red;">No results found...</p>
		};
		return $retHTML;
	} else {
		# DEBUG
		#use Data::Dumper;
		#$retHTML .= '<pre>' . Dumper($files) . '</pre>';
	}


	$retHTML .= qq{
	<div>
	Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
	</div>
      	<table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
		<tr class="fviewheader"> 
		<td></td>
		<td align="center">Share</td>
		<td align="center">Type and Name</td>
		<td align="center">#</td>
		<td align="center">Size</td>
		<td align="center">Date</td>
		<td align="center">Media</td>
		</tr>
	};

	my $file;

	sub hilite_html($$) {
		my ($html, $search) = @_;
		$html =~ s#($search)#<b>$1</b>#gis;
		return $html;
	}

	sub restore_link($$$$$$) {
		my $type = shift;
		my $action = 'RestoreFile';
		$action = 'browse' if (lc($type) eq 'dir');
		return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
	}

	my $i = $offset * $on_page;

	foreach $file (@{ $files }) {
		$i++;

		my $typeStr  = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
		$retHTML .= qq{<tr class="fviewborder">};

		$retHTML .= qq{<td class="fviewborder">$i</td>};

		$retHTML .=
			qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
			qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle">&nbsp;} . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
			qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
			qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
			qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
			qq{<td class="fviewborder">} . '?' . qq{</td>};

		$retHTML .= "</tr>";
	}
	$retHTML .= "</table>";

	# all variables which has to be transfered
	foreach my $n (qw/search_day_from search_month_from search_year_from search_day_to search_month_to search_year_to search_backup_day_from search_backup_month_from search_backup_year_from search_backup_day_to search_backup_month_to search_backup_year_to search_filename offset/) {
		$retHTML .= qq{<INPUT TYPE="hidden" NAME="$n" VALUE="$In{$n}">\n};
	}

	my $del = '';
	my $max_page = int( $results / $on_page );
	my $page = 0;

	sub page_link($$$) {
		my ($param,$page,$display) = @_;

		$param->{'offset'} = $page;

		my $html = '<a href = "' . $MyURL;
		my $del = '?';
		foreach my $k (keys %{ $param }) {
			if ($param->{$k}) {
				$html .= $del . $k . '=' . ${EscURI( $param->{$k} )};
				$del = '&';
			}
		}
		$html .= '">' . $display . '</a>';
	}

	$retHTML .= '<div style="text-align: center;">';

	if ($offset > 0) {
		$retHTML .= page_link($param, $offset - 1, '&lt;&lt;') . ' ';
	}

	while ($page <= $max_page) {
		if ($page == $offset) {
			$retHTML .= $del . '<b>' . ($page + 1) . '</b>';
		} else {
			$retHTML .= $del . page_link($param, $page, $page + 1);
		}

		if ($page < $offset - $pager_pages && $page != 0) {
			$retHTML .= " ... ";
			$page = $offset - $pager_pages;
			$del = '';
		} elsif ($page > $offset + $pager_pages && $page != $max_page) {
			$retHTML .= " ... ";
			$page = $max_page;
			$del = '';
		} else {
			$del = ' | ';
			$page++;
		}
	}

	if ($offset < $max_page) {
		$retHTML .= ' ' . page_link($param, $offset + 1, '&gt;&gt;');
	}

	$retHTML .= "</div>";

	return $retHTML;
}

1;
