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

my $opt_d = 'dbo';
my $opt_u = 'dbo';
my $opt_r;
my $opt_p;
my $opt_l;
my $opt_v = 0;
my $opt_j = 0;
my $opt_J = 0;
my $opt_S = 0.3;

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
	'r'   => \$opt_r, # whole region
	'v=i' => \$opt_v, # verbose
	'j=i' => \$opt_j, # jump range for selection
	'J=i' => \$opt_J, # jump range for trade
	'd=s' => \$opt_d, # db name
	'u=s' => \$opt_u, # db user
	'p=s' => \$opt_p, # db pass
	'S=f' => \$opt_S, # minimum supply/demand absolute value
	);



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


my %keys=();
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);
		for (@keyid) {
			printf "JUMPS(%i): %s == %s\n", $opt_j, $_, &name_by_id($_);
		}
	} elsif ($opt_r) {
		@keyid = &systems_by_region($keyid[0]);
	}

	for (@keyid) {
		$keys{$_}++;
	}
}
&do_systems(keys %keys);

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_type_by_id,);
my %typecache;
sub type_by_id($) {
	my $id = shift;
	die "non-numeric ID: $id" unless $id =~ /^\d+$/;

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

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

	$sth->execute($id) or die "exec failed: ".$dbh->errstr;
	my $row = $sth->fetchrow_hashref();
	return ($typecache{$id} = $row);
}

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_trades_by_corpid,);
sub trades_by_corpid($) {
	my ($corpid,) = @_;
	die "non-numeric sysid: $corpid" unless $corpid =~ /^\d+$/;

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

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

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) {
		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{$_};
		if ($range) {
			for (&systems_by_range($_,$range-1)) {
				$jmps{$_}++;
			}
		}
		$jmps{$_}++;
	}

	return keys %jmps;	
}


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

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

	$sth->execute($sysid) or die "exec failed: ".$dbh->errstr;
	my $jmps = $sth->fetchall_hashref("solarSystemID");

	return keys %$jmps;	
}



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



	my %trades = ();
	for my $cid (sort keys %corps) {
		my $trd = &trades_by_corpid($cid);
		for my $tid (keys %$trd) {
			my $t = $trd->{$tid};
			my $sd = $t->{supplyDemand};
			$trades{$tid}{$cid} = $sd;
		}
	}

	my @goodones = ();
	for my $tid (sort keys %trades) {
		my $trd = $trades{$tid};
		next unless scalar keys %$trd > 1;
		my @crp = sort { $trd->{$b} <=> $trd->{$a} } keys %$trd;
		my $hi = shift @crp;
		my $lo = pop @crp;
		next unless $trd->{$hi} > $opt_S and $trd->{$lo} < -1*$opt_S;

		my $typ = &type_by_id($tid);
		printf "TRADE: %i %s, %s m3, %s isk ... %i corps\n", $tid, 
			$typ->{typeName}, $typ->{volume}, $typ->{basePrice},
			scalar keys %$trd;
		printf "  FROM '%s' \@ %f // %s\n", &name_by_id($hi), $trd->{$hi}, join(" ", sort keys %{$corps{$hi}});
		printf "  TO '%s' \@ %f // %s\n", &name_by_id($lo), $trd->{$lo}, join(" ", sort keys %{$corps{$lo}});
#		printf "HI: %f // LO: %f\n", $trd->{$hi}, $trd->{$lo};
#		exit;
	}



}

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