#!/usr/bin/perl -w
#
# Get a list of files in dirs, submit to the db if they dont already exist
# dir1/*
# dir2/*
#
# select * from packs where filename='dir1/file1';
# if it matches, skip. it not, enter into the db
# then continue on to the next one

use strict;
use DBI;

my $dbuser = "username";
my $dbpass = "password";

my $dbh = DBI->connect('DBI:mysql:iroffer:localhost', $dbuser, $dbpass) or die $DBI::errstr;

my $base = "./";
my $count = 0;

# 'directory' => 'cat_id'
my %category = (
	'nes'			=> '1',
	'atari800'		=> '2',
	'tg16'			=> '3',
	'ti'			=> '4',
	'astrocade'		=> '5',
	'colecovision'	=> '6',
	'vic20'			=> '7',
	'intellivision'	=> '8',
	'mtx512'		=> '9',
	'mo5'			=> '10',
	'ngp'			=> '11',
	'atari2600'		=> '12',
	'genesis'		=> '13',
	'gamegear'		=> '14',
	'gameboy'		=> '15',
	'c64cart'		=> '16',
	'atari5200'		=> '17',
	'snes'			=> '18',
	'cpc'			=> '19',
	'jaguar'		=> '20',
	'lynx'			=> '21',
	'c64d64'		=> '22',
	'c64prg'		=> '23',
	'c64t64'		=> '24',
	'c64p00'		=> '25',
	'vectrex'		=> '26',
	'coco'			=> '27',
	'sms'			=> '28',
	'atari7800'		=> '29',
	'oric'			=> '30',
	'wonderswan'	=> '31',
	'samc'			=> '32',
	'gba'			=> '33'
);

opendir(ROMDIR, $base) or die "Can't open $base\n";
while ( defined(my $dir = readdir ROMDIR) ) {
	next if $dir =~ /^\.\.?$/; # skip . and ..
	next if $dir eq "uploads"; # skip the upload directory
	next if $dir eq "new";     # skip the new directory

	if (-d $dir) {
		# This is a directory, so check the files in here
		opendir(ROMS, $base.$dir) or die "Can't open $base.$dir\n";
		while ( defined(my $file = readdir ROMS) ) {
			next if $file =~ /^\.\.?$/;	# skip . and ..
			next if -d "$dir/$file";	# skip any dirs in here (shouldn't exist anyway)

			if (-f "$dir/$file") { # make sure its just a file
				my $romfile = "$dir/$file";

				# check to see if this is in the database already
				my $sql = "SELECT * FROM packs WHERE filename=\"$romfile\"";
				my $cursor = $dbh->prepare($sql);
				$cursor->execute;

				next if ($cursor->fetchrow); # It's already there, go to the next file

				(my $romdesc = $romfile) =~ s/\.zip$//; # Remove the .zip from the description
				$romdesc =~ s!^.*/!!; # Remove the directory from the description
				my $romsize = (stat("$base/$romfile"))[7];
				my $romcat = $category{$dir};

				my $sqlvalues = "(\"$romcat\", \"$romfile\", \"$romsize\", \"$romdesc\")";
				$sql = "INSERT INTO packs (cat_id, filename, filesize, description) VALUES $sqlvalues";
				
				$cursor = $dbh->prepare($sql);
				$cursor->execute;

				$cursor->finish;
				$count++;
			}
		}
		closedir(ROMS);
	}
}
closedir(ROMDIR);

print "$count new items found and added.\n";

$dbh->disconnect;

exit;