#!/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} || '';

sub getUnits() {
	my @ret;

	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
	my $sth = $dbh->prepare(qq{ SELECT id, share FROM shares ORDER BY share} );
	$sth->execute();
	push @ret, { 'id' => '', 'share' => '-'};	# dummy any

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

sub epoch_to_iso {
	my $t = shift || return;
	my $iso = BackupPC::Lib::timeStamp($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;
		my $mm .= $param->{ $name . '_month_' . $suffix} ||
			( $suffix eq 'from' ? 1 : 12);
		my $dd .= $param->{ $name . '_day_' . $suffix} ||
			( $suffix eq 'from' ? 1 : 31);
		my $dt = new DateTime(
			year => $yyyy,
			month => $mm,
			day => $dd
		);
		return $dt->epoch || 'NULL';
	}

	return (
		mk_epoch_date('search_backup', 'from'),
		mk_epoch_date('search_backup', 'to'),
		mk_epoch_date('search', 'from'),
		mk_epoch_date('search', 'to'),
	);
}


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, $offset) = @_;

	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );

	my $sql_cols = qq{
		files.id			AS fid,
		hosts.name			AS hname,
		shares.name			AS sname,
		shares.share			AS sharename,
		files.backupNum			AS backupNum,
		files.name			AS filename,
		files.path			AS filepath,
		files.date			AS date,
		files.type			AS filetype,
		files.size			AS size,
		-- dvds.name			AS dvd
		null				AS dvd
	};

	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 = shares.ID
	};

	my $sql_dvd_from = qq{
			-- LEFT  JOIN dvds		ON dvds.ID = files.dvdid
	};

	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_dvd_from $sql_where $sql_order };

	$offset ||= 0;
	$offset = ($offset * $on_page);

	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, { 
			'hname'		=> $row->{'hname'}, 
			'sname'		=> $row->{'sname'},
			'sharename'	=> $row->{'sharename'},
			'backupno'	=> $row->{'backupnum'},
			'fname'		=> $row->{'filename'},
			'fpath'		=> $row->{'filepath'},
			'networkpath'	=> $row->{'networkpath'},
			'date'		=> $row->{'date'},
			'type'		=> $row->{'filetype'},
			'size'		=> $row->{'size'},
			'id'		=> $row->{'fid'},
			'dvd'		=> $row->{'dvd'}
		});
	}
     
	$sth->finish();
	$dbh->disconnect();
	return ($results, \@ret);
}

sub getBackupsNotBurned() {

	my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
	my $sql = q{ 
	SELECT
		backups.hostID		AS hostid,
		min(hosts.name)		AS host,
		backups.num		AS backupno,
		min(backups.type)	AS type,
		min(backups.date)	AS date,
		min(backups.size)	AS size
	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 = shares.ID
	WHERE 
		files.dvdid	IS NULL
	GROUP BY 
		backups.hostID, backups.num
	ORDER BY min(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);
		push @ret, $row;
	}
      
	return @ret;      
}

sub displayBackupsGrid()
  {
      my $retHTML = "";
      my $addForm = 1;
      
      if ($addForm) {

	    $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;
            }
        }
      }
    }
//-->
</script>      
EOF3
	      $retHTML .= q{<form name="forma" method="GET" action="}."$MyURL"."?action=burn\"";
              $retHTML.= q{<input type="hidden" value="burn" name="action">};
              $retHTML .= q{<input type="hidden" value="results" name="search_results">};
	}
	$retHTML .= qq{
		<table style="fview" border="1" cellspacing="0" cellpadding="3">
		<tr class="tableheader">
	};

	if ($addForm) {
	    $retHTML .= "<td class=\"tableheader\"><input type=\"checkbox\" name=\"allFiles\" onClick=\"checkAll('allFiles');\"></td>";
	}
	$retHTML .=  qq{
		<td align="center">Host</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>
		</tr>
	};

	my @backups = getBackupsNotBurned();
	my $backup;

	if ($addForm) {
		$retHTML .= qq{
			<tr><td colspan=7 style="tableheader">
			<input type="submit" value="Burn selected backups on medium" name="submitBurner">
			</td></tr>
		};
	}

	foreach $backup(@backups) {

		my $ftype = "";
	    
		$retHTML .= "<tr>";
		if ($addForm) {
			$retHTML .= '<td class="fview"><input type="checkbox" name="fcb' .
				$backup->{'hostid'}.'_'.$backup->{'backupno'} . 
				'" value="' . $backup->{'hostid'}.'_'.$backup->{'backupno'} .
				'"></td>';
		}	    
	    
		$retHTML .= '<td class="fviewborder">' . $backup->{'host'} . '</td>' .
			'<td class="fviewborder">' . $backup->{'backupno'} . '</td>' .
			'<td class="fviewborder">' . $backup->{'type'} . '</td>' .
			'<td class="fviewborder">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
			'<td class="fviewborder">' . $backup->{'age'} . '</td>' .
			'<td class="fviewborder">' . $backup->{'size'} . '</td>' .
			'</tr>';
	}

	$retHTML .= "</table>";

	if ($addForm) {
		$retHTML .= "</form>";
	}
      
	return $retHTML;
}      

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

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

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

	my ($results, $files) = getFiles($param, $offset);

	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>';
	}


	if ($addForm) {
		$retHTML .= qq{<form name="forma" method="GET" action="$MyURL">};
		$retHTML.= qq{<input type="hidden" value="search" name="action">};
		$retHTML .= qq{<input type="hidden" value="results" name="search_results">};
	}


	$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 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, @_);
	}

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

		$retHTML .=
			qq{<td class="fviewborder" align="right">} . $file->{'sharename'} . qq{</td>} .
			qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle">&nbsp;} . hilite_html( $file->{'fpath'}, $hilite ) . qq{</td>} .
			qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupno'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'fpath'} )}, $file->{'backupno'} ) . 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">} . $file->{'dvd'} . 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;

	my $link_fmt = '<a href = "#" onclick="document.forma.offset.value=%d;document.forma.submit();">%s</a>';

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

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

	while ($page <= $max_page) {
		if ($page == $offset) {
			$retHTML .= $del . '<b>' . ($page + 1) . '</b>';
		} else {
			$retHTML .= $del . sprintf($link_fmt, $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 .= ' ' . sprintf($link_fmt, $offset + 1, '&gt;&gt;');
	}

	$retHTML .= "</div>";

	$retHTML .= "</form>" if ($addForm);

	return $retHTML;
}

1;
