#!/usr/bin/perl -w
use strict;
use CGI qw/:standard *ul *table *Tr/;
$|=1;

use Time::HiRes qw ( time );
my $begin = time;

my $trust      = $ENV{'HTTP_EVE.TRUSTED'}         || $ENV{'HTTP_EVE_TRUSTED'}         || 'undef';
my $sysname    = $ENV{'HTTP_EVE.SOLARSYSTEMNAME'} || $ENV{'HTTP_EVE_SOLARSYSTEMNAME'} || "";
my $charid     = $ENV{'HTTP_EVE.CHARID'}          || $ENV{'HTTP_EVE_CHARID'}          || "";
my $charname   = $ENV{'HTTP_EVE.CHARNAME'}        || $ENV{'HTTP_EVE_CHARNAME'}        || "";
my $regionname = $ENV{'HTTP_EVE.REGIONNAME'}      || $ENV{'HTTP_EVE_REGIONNAME'}      || "";

$trust = 0 unless $sysname && $charid && $charname && $regionname;

my $itsme = ($ENV{'REMOTE_ADDR'} =~ /^10\./) ? 1 : 0;

print header,"\n",
	start_html("Roid Finder"),"\n";

print h1(a({href=>url,},"Roid Finder")." -- ".($trust?$charname:"untrusted")),"\n";

## TODO: config
my $opt_d = 'dbo';
my $opt_u = 'dbo';
my $opt_p = undef;
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 $debug = param("debug") || url_param("debug") || 0;
my $submit = url({absolute=>1,});
if ($debug) { $submit .= "?debug=1"; }


my $limit = param("limit");
$limit = 50 unless defined $limit && $limit =~ /^\s*(\d+)\s*$/;
param("limit", $limit);

my $minsec = param("minsec");
$minsec = 0.45 unless defined $minsec && $minsec =~ /^\s*(-?\d+(\.\d+)?)\s*$/;
param("minsec", $minsec);

my $maxsec = param("maxsec");
$maxsec = 0.75 unless defined $maxsec && $maxsec =~ /^\s*(-?\d+(\.\d+)?)\s*$/;
param("maxsec", $maxsec);

my $days = param("days");
$days = 3 unless defined $days && $days =~ /^\s*(\d+)\s*$/;
param("days", $days);

my $chs = param("chs");
$chs = 1 unless defined $chs && $chs =~ /^-?[01]$/;
$chs = undef if $chs == -1;

my $sta = param("sta");
$sta = 1 unless defined $sta && $sta =~ /^-?[01]$/;
$sta = undef if $sta == -1;

my $sov = param("sov");
$sov = -1 unless defined $sov && $sov =~ /^(-1|0|5\d\d\d\d\d)$/;
$sov = undef if $sov == -1;

my $reg = param("reg");
$reg = -1 unless defined $reg && $reg =~ /^(-1|1\d{7})$/;
$reg = undef if $reg == -1;

my $ord = param("ord");
$ord = "desc" unless defined $ord && $ord =~ /^(desc|asc)$/;

my $sys = param("sys");
$sys = ($sysname||"Jita") unless defined $sys && $sys =~ /^([-\w ]+)$/;
$sys =~ s/\s+/ /g;
$sys =~ s/(^ | $)//g;
TRYTOFINDSYS:
my $syssth = $dbh->prepare_cached(qq{
	select
		solarSystemID
	from
		dbo.mapSolarSystems
	where
		solarSystemName = ?
	}) or die $dbh->errstr;
$syssth->execute($sys) or die $syssth->errstr;
my $sar = $syssth->fetchall_arrayref();
$syssth->finish();
#die "system $sys not found" unless $sar && @$sar;
unless ($sar && @$sar) {
	my $syslsth = $dbh->prepare_cached(qq{
		select
			solarSystemID,
			solarSystemName
		from
			dbo.mapSolarSystems
		where
			solarSystemName like ?
	}) or die $dbh->errstr;
	$sys = "$sys%" unless $sys =~ /%/;
	$syslsth->execute($sys) or die $syslsth->errstr;
	$sar = $syslsth->fetchall_arrayref();
	unless ($sar && @$sar) {
		$sys = ($sysname||"Jita");
		goto TRYTOFINDSYS;
	}
	$sys = $sar->[0]->[1];
}
param("sys", $sys);
my $sysid = $sar->[0]->[0];
die "no sysid" unless $sysid;

my $grp = param("grp");
$grp = -1 unless defined $grp && $grp =~ /^(-1|4\d{2})$/;
$grp = undef if $grp == -1;
my $grpsql = "";
if ($grp) {
	if ($grp == 465) {
		$grpsql = qq{
			select
				distinct solarSystemID
			from
				dbo.mapDenormalize
			where
				typeID = 17774
			};
	} else {
		$grpsql = qq{
			select
				distinct solarSystemID
			from
				dbo.mapSolarSystems sys,
				eve.mapSecurityClassOreGroups ore
			where
				ore.groupID = $grp
				and ore.securityClass = sys.securityClass
			};
	}
}

my $scr = param("scr");
$scr = 1 unless defined $scr && $scr =~ /^(\d)$/;
my $scrsql = "";
if ($scr == 1) {
	$scrsql = "belts/(0.01+(points*security))";
} elsif ($scr == 2) {
	$scrsql = "1/(0.01+points)";
} elsif ($scr == 3) {
	$scrsql = "j.jumps";
}
die "no scrsql" unless $scrsql;

my $pts = param("pts");
$pts = 1 unless defined $pts && $pts =~ /^[012]$/;
my ($ptsq,);
if ($pts == 0) {
	$ptsq = qq{
		select 
			avg(a.shipJumps) as points, 
			a.solarSystemID 
		from 
			eve.apiJumps a
		where 
			a.datatime > NOW() - INTERVAL ? day 
		group by a.solarSystemID };
} elsif ($pts == 1) {
	$ptsq = qq{
		select 
			avg(a.factionKills) as points, 
			a.solarSystemID 
		from 
			eve.apiKills a
		where 
			a.datatime > NOW() - INTERVAL ? day 
		group by a.solarSystemID };
} elsif ($pts == 2) {
	$ptsq = qq{
		select 
			avg(a.shipKills)+avg(a.podKills*5) as points, 
			a.solarSystemID 
		from 
			eve.apiKills a
		where 
			a.datatime > NOW() - INTERVAL ? day 
		group by a.solarSystemID };
} elsif ($pts == 3) {
	$ptsq = qq{
		select 
			(j.jumps/g.gates) as points,
			j.solarSystemID 
		from 
			(
			select 
				avg(a.shipJumps) as jumps, 
				a.solarSystemID 
			from 
				eve.apiJumps a
			where 
				a.datatime > NOW() - INTERVAL ? day 
			group by
				a.solarSystemID
			) j,

			(
			select 
				count(*) as gates,
				fromSolarSystemID as solarSystemID
			from 
				dbo.mapSolarSystemJumps
			group by
				fromSolarSystemID
			) g
		where 
			j.solarSystemID = g.solarSystemID
		};
}
die "missing ptsq" unless $ptsq;

my %factions;
my $far = $dbh->selectall_arrayref(qq{
	SELECT
		factionID,
		factionName
	FROM
		dbo.chrFactions
	}) or die $dbh->errstr;
for (@$far) {
	my ($fid, $fn) = @$_;
	die "bad faction id $fid for $fn" unless
		$fid > 500000 && $fid < 599999;
	die "trying to redefine faction $fid" if
		$factions{$fid};
	$factions{$fid} = $fn;
}

my %regions;
my $rar = $dbh->selectall_arrayref(qq{
	SELECT
		regionID,
		regionName
	FROM
		dbo.mapRegions
	}) or die $dbh->errstr;
for (@$rar) {
	my ($rid, $rn) = @$_;
	die "bad region id $rid for $rn" unless
		$rid > 10000000 && $rid < 19999999;
	die "trying to redefine region $rid" if
		$regions{$rid};
	$regions{$rid} = $rn;
}

my %groups;
my $grq = $dbh->selectall_arrayref(qq{
	SELECT
		distinct g.groupID,
		groupName
	FROM
		dbo.invGroups g,
		eve.mapSecurityClassOreGroups m
	WHERE
		g.groupID=m.groupID
	}) or die $dbh->errstr;
for (@$grq) {
	my ($gid, $gn,) = @$_;
	die "bad gid $gid" unless
		$gid > 400 && $gid < 500;
	die "trying to redefine gid $gid" if
		$groups{$gid};
	$groups{$gid} = $gn;
}
$groups{465} = "Ice";



print hr, "\n",
	start_form,
	start_table,"\n";

print Tr(td([ "Min Security: ",
		textfield(-name => 'minsec',
			-default => $minsec,
			-size => 5,
			),
                ],),), "\n";
print Tr(td([ "Max Security: ",
		textfield(-name => 'maxsec',
			-default => $maxsec,
			-size => 5,
			),
                ],),), "\n";

print Tr(td([ "Connected Highsec: ",
		popup_menu(-name => 'chs',
			-values  => [-1,0,1,],
                        -default => 1,
                        -labels  => { -1 => "n/a", 0 => 'NO', 1 => 'YES',},
                        ),
		],),), "\n";

print Tr(td([ "Has Station(s): ",
		popup_menu(-name => 'sta',
			-values  => [-1,0,1,],
                        -default => 1,
                        -labels  => { -1 => "n/a", 0 => 'NO', 1 => 'YES',},
                        ),
		],),), "\n";

print Tr(td([ "Sovereignty: ",
		popup_menu(-name => 'sov',
			-values  => [-1,0,(sort {$factions{$a} cmp $factions{$b}} keys %factions),],
                        -default => -1,
                        -labels  => { -1 => "n/a", 0 => 'NONE', (%factions),},
                        ),
		],),), "\n";

print Tr(td([ "Region: ",
		popup_menu(-name => 'reg',
			-values  => [-1,(sort {$regions{$a} cmp $regions{$b}} keys %regions),],
                        -default => -1,
                        -labels  => { -1 => "n/a", (%regions),},
                        ),
		],),), "\n";

print Tr(td([ "Location: ",
		textfield(-name => 'sys',
			-default => $sys,
			-size => 15,
			),
                ],),), "\n";

print Tr(td([ "Ressource: ",
		popup_menu(-name => 'grp',
			-values  => [-1,(sort {$groups{$a} cmp $groups{$b}} keys %groups),],
                        -default => -1,
                        -labels  => { -1 => "n/a", (%groups),},
                        ),
		],),), "\n";

print Tr(td([ "Points: ",
		popup_menu(-name => 'pts',
			-values  => [0..3],
                        -default => 1,
                        -labels  => { 0 => 'Jumps', 1 => 'NPC Kills', 2 => 'Player Kills', 3 => 'Jumps/GateCount', },
                        ),
#		"score = belts/(0.01+(points*security))",
		],),), "\n";

print Tr(td([ "Score: ",
		popup_menu(-name => 'scr',
			-values  => [1..3],
                        -default => 1,
                        -labels  => { 1 => 'belts/(0.01+(points*security))', 2 => '1/(0.01+points)', 3 => 'distance', },
                        ),
		],),), "\n";

print Tr(td([ "Order: ",
		popup_menu(-name => 'ord',
			-values  => [ "desc", "asc", ],
                        -default => "desc",
                        -labels  => { "desc" => 'descending', "asc" => 'ascending', },
                        ),
		],),), "\n";

print Tr(td([ "Time Scope: ",
		textfield(-name => 'days',
			-default => $days,
			-size => 5,
			),
                "days",],),), "\n";

print Tr(td([ "Max Results: ",
		textfield(-name => 'limit',
			-default => $limit,
			-size => 5,
			),
                ],),), "\n";


print	end_table, "\n";

print	submit, "\n",
	end_form, "\n",
	hr, "\n";


my $sth = $dbh->prepare_cached(qq{
	select 
		s.*,
		r.regionName,
		belts,
		points,
		j.jumps,
		j.isConnectedHighsec,
		$scrsql as score
	from 
		dbo.mapSolarSystems s, 
		eve.mapJumps j,
		${\(defined $chs ? "eve.mapHighSec h," : "")}
		${\(defined $sov ? "eve.apiSovereignty sov," : "")}
		dbo.mapRegions r, 
		(
			select 
				count(*) as belts,
				solarSystemID 
			from 
				dbo.mapDenormalize 
			where 
				typeID=15 
			group by solarSystemID
		) b, 
		(
			$ptsq
		) k 
	WHERE 
		s.solarSystemID = k.solarSystemID 
		and s.solarSystemID = b.solarSystemID 
		and s.regionID = r.regionID 
		and s.security >= ? 
		and s.security <= ?
		and s.solarSystemID = j.toSolarSystemID
		and j.fromSolarSystemID = ?
		${\(defined $chs ? "and s.solarSystemID = h.solarSystemID and h.isConnectedHighSec = ?" : "")}
		${\(defined $sov ? "and s.solarSystemID = sov.solarSystemID and sov.factionID = ?" : "")}
		${\(defined $reg ? "and r.regionID = ?" : "")}
		${\(defined $grp ? "and s.solarSystemID in ($grpsql)" : "")}
	order by $scrsql $ord
	}) or die $dbh->errstr;
my @param = ($days, $minsec, $maxsec, $sysid,);
push @param, $chs if defined $chs;
push @param, $sov if defined $sov;
push @param, $reg if defined $reg;

print start_table({border=>1,},),
	Tr(th(["#","SolarSystem", "Jumps", "Sec", "Faction", "Region", "Belts", "Stations", "Points", "Score",])),
	"\n";

$sth->execute(@param) or die $sth->errstr;
my $cnt = 1;
while ((my $sys = $sth->fetchrow_hashref) && ($cnt <= $limit)) {
	my $sid = $sys->{solarSystemID};
	die "no sid" unless $sid;

	my $bsth = $dbh->prepare_cached(qq{
			select 
				count(*)
			from 
				eve.apiStations 
			where
				solarSystemID = ?
			}) or die $dbh->errstr;
	$bsth->execute($sid) or die $bsth->errstr;
	my $s = $bsth->fetchrow_arrayref();
	$bsth->finish;
	my ($sc,) = defined $s ? @$s : (0,);
	if (defined $sta) {
		next if $sc xor $sta;
	}

	my $color;
#	if ($sys->{security} >= 0.75) {
#		$color = "lightblue";
#	} elsif ($sys->{security} >= 0.55) {
#		$color = "green";
#	} elsif ($sys->{security} >= 0.45) {
	if ($sys->{security} >= 0.45) {
		$color = "green";
	} elsif ($sys->{security} >= 0) {
		$color = "orange";
	} else {
		die "bad sec" unless $sys->{security} < 0;
		$color = "red";
	}

	my $jmpscolor = $sys->{isConnectedHighsec} ? "green" : "red";

	print Tr(td([
		$cnt++,
		a({href => "showinfo:5//$sid",}, $sys->{solarSystemName}),
		span({style=>"color: $jmpscolor",}, $sys->{jumps}),
		span({style=>"color: $color", title=>$sys->{security},}, sprintf("%.1f",$sys->{security})),
		&faction($sid),
		a({href => "showinfo:3//".$sys->{regionID},}, $sys->{regionName}),
		span({title=>&ores($sys->{securityClass}),}, $sys->{belts}),
		$sc,
		sprintf("%.2f",$sys->{points}),
		sprintf("%.5f",$sys->{score}),
		])), "\n";

}
$sth->finish;

print end_table,"\n";


EXITHTML:
if ($debug) {
	print hr,"\n";
	for (sort keys %ENV) {
		printf "%s: %s%s\n", $_, $ENV{$_}, br;
	}
}

my $runtime = time - $begin;
print hr,
	"Hint: mouseover the security and beltcount.", br, "\n",
	$runtime ? sprintf "Completed in %.3f sec.", $runtime : "",
	end_html,"\n";
exit 0;

# tools

my %factcache;
sub faction ($) {
	my ($sid,) = @_;
	return $factcache{$sid} if defined $factcache{$sid};

	my $sth = $dbh->prepare_cached(qq{
		select
			s.factionID,
			f.factionName
		from
			dbo.chrFactions f,
			eve.apiSovereignty s
		where
			s.solarSystemID = ?
			and s.factionID = f.factionID
		}) or die $dbh->errstr;
	$sth->execute($sid) or die $sth->errstr;
	my $r = $sth->fetchrow_arrayref;
	$sth->finish;

	my ($fid, $fn,) = defined $r ? @$r : ("", "",);
	$fn =~ s/^the\s//ig;
	$fn =~ s/\W+.*$//g;

	my $sov = $fid
			? a({href=>"showinfo:30//$fid",}, $fn)
			: $fn;

	$factcache{$sid} = $sov;
	return $factcache{$sid};
}

my %orecache;
sub ores ($) {
	my ($sc,) = @_;
	return $orecache{$sc} if defined $orecache{$sc};

	my $sth = $dbh->prepare_cached(qq{
		select
			g.groupName
		from
			dbo.invGroups g,
			eve.mapSecurityClassOreGroups o
		where
			o.securityClass = ?
			and o.groupID = g.groupID
		}) or die $dbh->errstr;
	$sth->execute($sc) or die $sth->errstr;
	my $ores = "$sc -";
	while (my $r = $sth->fetchrow_arrayref) {
		my ($ore,) = @$r;
		$ores .= " ".$ore;
	}
	$sth->finish;

	$orecache{$sc} = $ores;
	return $orecache{$sc};
}