#!/usr/local/bin/perl -w

use strict;
use DBI;
use lib "__INSTALLDIR__/lib";
use BackupPC::Lib;
use BackupPC::View;
use Data::Dumper;
use Getopt::Std;
use constant BPC_FTYPE_DIR => 5;

my $debug = 0;
$|=1;

my $hosts;
my $bpc = BackupPC::Lib->new || die;
my %Conf = $bpc->Conf();
my $TopDir = $bpc->TopDir();
my $beenThere = {};

my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";

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

my %opt;

if ( !getopts("cdm:", \%opt ) ) {
	print STDERR <<EOF;
usage: $0 [-c|-d] [-m num]

Options:
	-c	create database on first use
	-d	delete database before import
	-m num	import just num	increments for one host
EOF
	exit 1;
}

###################################create tables############################3

if ($opt{c}) {
	print "creating tables...\n";
      
	$dbh->do(qq{
		create table hosts (
			ID	INTEGER 	PRIMARY KEY,
			name	VARCHAR(30)	NOT NULL,
			IP	VARCHAR(15)
		);	      
	});
	      
	$dbh->do(qq{
		create table shares (
			ID	INTEGER		PRIMARY KEY,
			hostID	INTEGER		NOT NULL references hosts(id),
			name	VARCHAR(30)	NOT NULL,
			share	VARCHAR(200)	NOT NULL,
			localpath VARCHAR(200)      
		);	      
	});
	
	$dbh->do(qq{
		create table backups (
			hostID	INTEGER		NOT NULL references hosts(id),
			num     INTEGER		NOT NULL,
			date	DATE, 
			type	CHAR(1), 
			PRIMARY KEY(hostID, num) 
		); 	      
	});

	$dbh->do(qq{
		create table dvds (
			ID	INTEGER		PRIMARY KEY, 
			num     INTEGER		NOT NULL,
			name	VARCHAR(255)	NOT NULL,
			mjesto	VARCHAR(255)
		);
	});

	$dbh->do(qq{     
		create table files (
			ID	INTEGER		NOT NULL PRIMARY KEY,  
			shareID	INTEGER		NOT NULL references shares(id),
			backupNum  INTEGER	NOT NULL references backups(num),
			name       VARCHAR(255)	NOT NULL,
			path       VARCHAR(255)	NOT NULL,
			fullpath   VARCHAR(255)	NOT NULL,
			date       TIMESTAMP	NOT NULL,
			type       INTEGER	NOT NULL,
			size       INTEGER	NOT NULL,
			dvdid      INTEGER	references dvds(id)	
		);
	});

	print "creating indexes...\n";

	foreach my $index (qw(
		hosts_name
		backups_hostID
		backups_num
		shares_hostID
		shares_name
		files_shareID
		files_path
		files_name
		files_date
		files_size
	)) {
		my ($table,$col) = split(/_/, $index);
		$dbh->do(qq{ create index $index on $table($col) });
	}


}

if ($opt{d}) {
	print "deleting ";
	foreach my $table (qw(hosts shares files dvds backups)) {
		print "$table ";
		$dbh->do(qq{ DELETE FROM $table });
	}
	print " done...\n";
}

#################################INSERT VALUES#############################

# get hosts
$hosts = $bpc->HostInfoRead();
my $hostID;
my $shareID;

my $sth;

$sth->{insert_hosts} = $dbh->prepare(qq{
INSERT INTO hosts (name, IP) VALUES (?,?)
});

$sth->{hosts_by_name} = $dbh->prepare(qq{
SELECT ID FROM hosts WHERE name=?
});

$sth->{backups_broj} = $dbh->prepare(qq{
SELECT COUNT(*)
FROM backups
WHERE hostID=? AND num=?
});

$sth->{insert_backups} = $dbh->prepare(qq{
INSERT INTO backups (hostID, num, date, type)
VALUES (?,?,?,?)
});

$sth->{insert_files} = $dbh->prepare(qq{
INSERT INTO files
	(shareID, backupNum, name, path, fullpath, date, type, size)
	VALUES (?,?,?,?,?,?,?,?)
});

foreach my $host_key (keys %{$hosts}) {

	my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";

	$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});

	unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
		$sth->{insert_hosts}->execute(
			$hosts->{$host_key}->{'host'},
			$hosts->{$host_key}->{'ip'}
		);

		$hostID = $dbh->func('last_insert_rowid');
	}

	print("host ".$hosts->{$host_key}->{'host'}.": ");
 
	# get backups for a host
	my @backups = $bpc->BackupInfoRead($hostname);
	print scalar @backups, " increments\n";

	my $inc_nr = 0;

	foreach my $backup (@backups) {
		$inc_nr++;
		last if ($opt{m} && $inc_nr > $opt{m});

		my $backupNum = $backup->{'num'};
		my @backupShares = ();

		print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";

		$sth->{backups_broj}->execute($hostID, $backupNum);
		my ($broj) = $sth->{backups_broj}->fetchrow_array();
		next if ($broj > 0);

		my $files = BackupPC::View->new($bpc, $hostname, \@backups);
		foreach my $share ($files->shareList($backupNum)) {

			print "\t$share";
			$shareID = getShareID($share, $hostID, $hostname);
		
			my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
			print " $nf/$f files $nd/$d dirs\n";
			$dbh->commit();
		}

		$sth->{insert_backups}->execute(
			$hostID,
			$backupNum,
			$backup->{'endTime'},
			$backup->{'type'}
		);
		$dbh->commit();

	}
}
undef $sth;
$dbh->commit();
$dbh->disconnect();

sub getShareID() {

	my ($share, $hostID, $hostname) = @_;

	$sth->{share_id} ||= $dbh->prepare(qq{
		SELECT ID FROM shares WHERE hostID=? AND name=?
	});

	$sth->{share_id}->execute($hostID,$share);

	my ($id) = $sth->{share_id}->fetchrow_array();

	return $id if (defined($id));

	$sth->{insert_share} ||= $dbh->prepare(qq{
		INSERT INTO shares 
			(hostID,name,share,localpath) 
		VALUES (?,?,?,?)
	});

	my $drop_down = $hostname . '/' . $share;
	$drop_down =~ s#//+#/#g;

	$sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
	return $dbh->func('last_insert_rowid');      	
}

sub found_in_db {

	my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;

	$sth->{file_in_db} ||= $dbh->prepare(qq{
		SELECT count(*) FROM files
		WHERE shareID = ? and
			path = ? and 
			name = ? and
			date = ? and
			size = ?
	});

	my @param = ($shareID,$path,$name,$date,$size);
	$sth->{file_in_db}->execute(@param);
	my ($rows) = $sth->{file_in_db}->fetchrow_array();
#	print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
	return $rows;
}

####################################################
# recursing through filesystem structure and       #
# and returning flattened files list               #
####################################################
sub recurseDir($$$$$$$$) {

	my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;

	print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);

	my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);

	{ # scope
		my @stack;

		my $files = BackupPC::View->new($bpc, $hostname, $backups);             
		my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);

		# first, add all the entries in current directory
		foreach my $path_key (keys %{$filesInBackup}) {
			my @data = (
				$shareID,
				$backupNum,
				$path_key,
				$filesInBackup->{$path_key}->{'relPath'},
				$filesInBackup->{$path_key}->{'fullPath'},
	#			$filesInBackup->{$path_key}->{'sharePathM'},
				$filesInBackup->{$path_key}->{'mtime'},
				$filesInBackup->{$path_key}->{'type'},
				$filesInBackup->{$path_key}->{'size'}
			);

			my $key = join(" ", (
				$shareID,
				$dir,
				$path_key,
				$filesInBackup->{$path_key}->{'mtime'},
				$filesInBackup->{$path_key}->{'size'}
			));


			if (! $beenThere->{$key} && ! found_in_db(@data)) {
				print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
				$sth->{'insert_files'}->execute(@data);
				if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
					$new_dirs++;
					print STDERR " dir\n" if ($debug >= 2);
				} else {
					$new_files++;
					print STDERR " file\n" if ($debug >= 2);
				}
			}
			$beenThere->{$key}++;

			if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
				$nr_dirs++;

				my $full_path = $dir . '/' . $path_key;
				push @stack, $full_path;
				print STDERR "### store to stack: $full_path\n" if ($debug >= 3);

#				my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
#
#				$nr_files += $f;
#				$new_files += $nf;
#				$nr_dirs += $d;
#				$new_dirs += $nd;

			} else {
				$nr_files++;
			}
		}

		print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);

		while ( my $dir = shift @stack ) {
			my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID);
			print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
			$nr_files += $f;
			$new_files += $nf;
			$nr_dirs += $d;
			$new_dirs += $nd;
		}
	}

	return ($nr_files, $new_files, $nr_dirs, $new_dirs);
}