#!/usr/bin/perl
use DBI qw(:sql_types);
use Cwd;
$| = 1;

# We want to trap control-c to ensure a clean shutdown
$SIG{'INT'} = 'shutdown';

my $storenumber = 0;
my $store;
my $lookup;
my $here = getcwd;

# Set maximum size for a database file
my $max_size = 3865470566;


# Connect to both database files and create the schemas (if necessary)
my $needcreate = 0;
unless(-f "lookup.sqlite")
{
	$needcreate = 1;
}
$store = DBI->connect("dbi:SQLite:dbname=store.sqlite.0","","",{AutoCommit => 0}) or die($!);
$lookup = DBI->connect("dbi:SQLite:dbname=lookup.sqlite","","",{AutoCommit => 0}) or die($!);
if($needcreate)
{
	$lookup->do("CREATE TABLE tiles (z integer, x integer, y integer, store_filename string, extension varchar(10), unix_epoch_timestamp integer, primary key (z, x, y, extension))");
	$lookup->do("CREATE TABLE version (v integer)");
	$lookup->do("insert into version values(1)");
	create_store_schema();
}
my $storeinsert = prepare_query();
my $lookupinsert = $lookup->prepare("insert into tiles values(?,?,?,?,?,?)");
my $check_exists = $lookup->prepare("select store_filename from tiles where z=? and x=? and y=?");

# Get a list of zoom levels
my @zooms = <[0-9]*>;

# Work through each zoom level

my $filecount = `find [0-9]* -type f|wc -l`;
chomp($filecount);
my $filenum = 0;
my $transaction = 0;
my ($z,$x,@x,$y,$file,@files,$files,@data,$data,$stamp,$extension,$existingstore);
foreach $z(@zooms)
{
	chdir($z);
	# Get a list of x co-ordinates
	@x = <*>;
	foreach $x(@x)
	{
		chdir($x);
		# Get a list of files
		@files = <*>;
		# Work through the files and add them to the database
		foreach $file(@files)
		{
			chomp($file);
			$filenum++;
			# Get timestamp
			$stamp = (stat($file))[9];
			# Get file data
			$data = `cat $file`;
			# get extension and filename
			($y,$extension) = split(/\./,$file);
			# Check if the tile already exists
			$check_exists->execute($z,$x,$y);
			$existingstore = $check_exists->fetchrow_array();
			if($existingstore eq "")
			{
				# Push into databases
				$storeinsert->bind_param(4,$data,SQL_BLOB);
				$storeinsert->bind_param(1,$z,SQL_INTEGER);
				$storeinsert->bind_param(2,$x,SQL_INTEGER);
				$storeinsert->bind_param(3,$y,SQL_INTEGER);
				$storeinsert->bind_param(5,$extension,SQL_VARCHAR);
				$storeinsert->bind_param(6,$stamp,SQL_INTEGER);
				$storeinsert->execute();
				$lookupinsert->execute($z,$x,$y,'store.sqlite.' . $storenumber,$extension,$stamp);
				# Only commit every 50 transactions to save time
				$transaction++;
				if($transaction == 50)
				{
					print(" Processed $filenum / $filecount tiles\r");
					flush();
	
					# Check the store filesize
					my $size = (stat("$here/store.sqlite.$storenumber"))[7];
					if($size > $max_size)
					{
						# Need to open a new store file
						$store->disconnect();
						$storenumber++;
						$store = DBI->connect("dbi:SQLite:dbname=$here/store.sqlite.$storenumber","","",{AutoCommit => 0}) or die($!);
						create_store_schema();
						$storeinsert = prepare_query();
					}
				}
			}
		}
		chdir("..");
	}
	chdir("..");
}
# We're done, so shut down tidily
flush();
$store->disconnect();
$lookup->disconnect();


sub prepare_query
{
	my $storeinsert = $store->prepare("insert into tiles values(?,?,?,?,?,?)");
	return($storeinsert);
}
sub create_store_schema
{
	$store->do("CREATE TABLE tiles (z integer, x integer, y integer, tile blob, extension varchar(10), unix_epoch_timestamp integer, primary key (z, x, y, extension))");
	$store->do("CREATE TABLE version (v integer)");
	$store->do("insert into version values(1)");
}
sub shutdown
{
	# called when Ctrl-C is hit
	
	# Make sure another click doesn't abort us
	$SIG{'INT'} = 'IGNORE';
	print("\nShutting down\n");
	flush();
	$store->disconnect();
	$lookup->disconnect();
	exit;
}
sub flush
{
	# Commits the database.
	$store->commit();
	$lookup->commit();
	$transaction = 0;
}