#!/usr/bin/perl -w
use strict;
$|=1;

my $opt_d = 'dbo';
my $opt_u = 'dbo';
my $opt_p;
my $opt_l;
my $opt_v = 0;
my $opt_j = 0;

my ($opt_D,);
use Getopt::Long qw(:config no_ignore_case bundling);
GetOptions(
	'D'   => \$opt_D, # generate full drops
	'l'   => \$opt_l, # disable like-matching
	'v=i' => \$opt_v, # verbose
	'j=i' => \$opt_j, # jump range
	'd=s' => \$opt_d, # db name
	'u=s' => \$opt_u, # db user
	'p=s' => \$opt_p, # db pass
	);



use DBI;
my $dbh = DBI->connect("DBI:mysql:database=$opt_d", $opt_u, $opt_p);
die "cant connect to db: ".DBI->errstr unless $dbh;



for (@ARGV) {
	printf "MATCHING: '%s'\n", $_;
	my $key = &find_keys($_);

	my @keyid = keys %$key;
	die "no matches" unless @keyid;
	if (scalar @keyid > 1) {
		for (sort @keyid) {
			#printf "CANDIDATE: %i / %s\n", $_, $sys->{$_}{solarSystemName};
			printf "CANDIDATE: %i / %s\n", $_, &name_by_id($_);
		}
		next;
	}

	&hashprint($key->{$keyid[0]});

	printf "FOUND: %i %s\n", $keyid[0], &name_by_id($keyid[0]);

	# TODO expand-by-jumps 
	if ($opt_j) {
		@keyid = &systems_by_range($keyid[0], $opt_j);
	}
	
	&do_systems(@keyid);
}

exit 0;

##########

my ($sth_find_key_by_id, $sth_find_key_by_name,);
sub find_keys($) {
	my ($hint,) = @_;

	$sth_find_key_by_id ||= $dbh->prepare(qq{
			select
				*
			from
				eveNames,
				invGroups
			where
				itemID = ?
				and eveNames.groupID = invGroups.groupID
			});

	$sth_find_key_by_name ||= $dbh->prepare(qq{
			select
				*
			from
				eveNames
			where
				itemName like ?
			});

	my $sth;
	if ($hint =~ /^\d+$/) {
		$sth = $sth_find_key_by_id;
	} else {
		$sth = $sth_find_key_by_name;
	}
	die "failed query prep: ".$dbh->errstr unless $sth;

	$sth->execute($hint) or die "exec failed: ".$dbh->errstr;
	my $res = $sth->fetchall_hashref("itemID");
	return $res if $res;

	if ($hint =~ /^\d+$/ || $hint !~ /%/) {
		$hint = "%".$hint unless $hint =~ /^%/;
		$hint = $hint."%" unless $hint =~ /%$/;
		$sth = $sth_find_key_by_name;
		$sth->execute($hint) or die "exec failed: ".$dbh->errstr;
		return $sth->fetchall_hashref("itemID");
	}
}

my ($sth_name_by_id,);
my %idcache;
sub name_by_id($) {
	my $id = shift;
	die "non-numeric ID: $id" unless $id =~ /^\d+$/;

	return $idcache{$id} if defined $idcache{$id};

	my $sth = $sth_name_by_id ||= $dbh->prepare(qq{
			select
				itemName
			from
				eveNames
			where
				itemID = ?
			});
	die "failed query prep: ".$dbh->errstr unless $sth;

	$sth->execute($id) or die "exec failed: ".$dbh->errstr;
	my $row = $sth->fetchrow_arrayref();
	return ($idcache{$id} = (@$row ? $row->[0] : "NULL"));
}

my ($sth_station_by_sysid,);
my %stacache;
sub stations_by_sysid($) {
	my ($sysid,) = @_;
	die "non-numeric sysid: $sysid" unless $sysid =~ /^\d+$/;

	my $sth = $sth_station_by_sysid ||= $dbh->prepare(qq{
			select
				*
			from
				staStations
			where
				solarSystemID = ?
			});
	die "failed query prep: ".$dbh->errstr unless $sth;

	$sth->execute($sysid) or die "exec failed: ".$dbh->errstr;
	my $sta = $sth->fetchall_hashref("stationID");
	for (keys %$sta) {
		$stacache{$_} = $sta->{$_};
	}
	return $sta;
}

my ($sth_station_by_id,);
sub station_by_id($) {
	my ($staid,) = @_;
	die "non-numeric sysid: $staid" unless $staid =~ /^\d+$/;
	return $stacache{$staid} if defined $stacache{$staid};

	my $sth = $sth_station_by_id ||= $dbh->prepare(qq{
			select
				*
			from
				staStations
			where
				stationID = ?
			});
	die "failed query prep: ".$dbh->errstr unless $sth;

	$sth->execute($staid) or die "exec failed: ".$dbh->errstr;
	return $stacache{$staid} ||= $sth->fetchrow_hashref();
}


my ($sth_agents_by_corpid,);
sub agents_by_corpid($) {
	my ($corpid,) = @_;
	die "non-numeric sysid: $corpid" unless $corpid =~ /^\d+$/;

	my $sth = $sth_agents_by_corpid ||= $dbh->prepare(qq{
			select
				*
			from
				agtAgents
			where
				corporationID = ?
			});
	die "failed query prep: ".$dbh->errstr unless $sth;

	$sth->execute($corpid) or die "exec failed: ".$dbh->errstr;
	return $sth->fetchall_hashref("agentID");
}

my ($sth_jumps_by_system,);
my %jumpcache;
sub systems_by_range($$) {
	my ($sysid, $range,) = @_;
	die "non-numeric sysid: $sysid" unless $sysid =~ /^\d+$/;
	die "non-numeric range: $range" unless $range =~ /^\d+$/;
	return () unless $range;

	my $jmps = $jumpcache{$sysid};
	unless ($jmps) {
    printf "JMPS: loading %i %s\n", $sysid, &name_by_id($sysid);
		my $sth = $sth_jumps_by_system ||= $dbh->prepare(qq{
			select
				*
			from
				mapSolarSystemJumps
			where
				fromSolarSystemID = ?
			});
		die "failed query prep: ".$dbh->errstr unless $sth;

		$sth->execute($sysid) or die "exec failed: ".$dbh->errstr;
		$jmps = $sth->fetchall_hashref("toSolarSystemID");
		$jumpcache{$sysid} = $jmps;
	}

	my %jmps = ($sysid => 1,);
	for (keys %$jmps) {
#		next if $jmps{$_};
    printf "JMPS: %i checking %i %s\n", $sysid, $_, &name_by_id($_);
		if ($range) {
			for (&systems_by_range($_,$range-1)) {
				$jmps{$_}++;
			}
		}
		$jmps{$_}++;
	}

	return keys %jmps;	
}



sub do_systems(@) {
	my (%stations, %corps,);
	for (@_) {
		printf " SYSTEM: %i %s ...", $_, &name_by_id($_);
		my $sta = &stations_by_sysid($_);
		printf " %i stations", scalar keys %$sta;
		my %scorps = ();
		for (keys %$sta) {
			$stations{$_} = $sta->{$_};
			$scorps{$sta->{$_}{corporationID}}++;
			$corps{$sta->{$_}{corporationID}}++;
		}
		printf " %i corps\n", scalar keys %scorps;
	}

	printf "  CORP: %10s %6s %s%6s %6s %6s %6s %s\n", "corpid", "#agt", ($opt_v ? "  l1 l2 l3 l4 l5 ": ""),
       					"#sta", "#sys", "#cst", "#reg", "corpname"; 
	for (sort keys %corps) {
		&do_corp($_);
	}
}

sub do_corp($) {
	my ($cid,) = @_;
	die "non-numeric corpid: $cid" unless $cid =~ /^\d+$/;

	my (%stations, %systems, %consts, %regions,);
	my @levels = (undef, 0, 0, 0, 0, 0,);
	my $agt = &agents_by_corpid($cid);
	for (keys %$agt) {
		my $lev = $agt->{$_}{level};
		printf("FUNNY: agent %i '%s' has level %i\n", $_, &name_by_id($_), $lev) if $opt_v > 5 && $lev > 5;
		my $staid = $agt->{$_}{stationID};
		unless ($staid) {
			if (8 == $agt->{$_}{agentTypeID}) {
				printf("FUNNY: agent %i '%s' has no station\n", $_, &name_by_id($_)) if $opt_v > 5;
				next;
			}
#			die "agent $_ without station id".&hashprint($agt->{$_}) unless $staid;
			next unless $staid;
		}
		$stations{$staid}[$lev]++;
		$levels[$lev]++;

		my $sta = &station_by_id($staid);
		$systems{$sta->{solarSystemID}}[$lev]++;
		$consts{$sta->{constellationID}}[$lev]++;
		$regions{$sta->{regionID}}[$lev]++;
	}

	# TODO more sanity checks
	for (0..$#levels) {
		if (($_ < 1 || $_ > 5) && defined $levels[$_] && $levels[$_] > 0) {
			printf "WARN: funny level $_ => %i\n", $levels[$_];
		}
	}

	printf "  CORP: %10i %6i %s%6i %6i %6i %6i %s\n", $cid, scalar keys %$agt, 
			($opt_v? sprintf "  %2i %2i %2i %2i %2i ", $levels[1], $levels[2], 
						$levels[3], $levels[4], $levels[5] : ""),
			scalar keys %stations, scalar keys %systems, scalar keys %consts,  
			scalar keys %regions, &name_by_id($cid); 



}

sub hashprint($) {
	my $h = shift;
	for (keys %$h) {
		printf "HP -- '%s': '%s'\n", $_, (defined $h->{$_} ? $h->{$_} : "UNDEF");
	}
}