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

my ($opt_d, $opt_u, $opt_p, );
my ($opt_l, );

use Getopt::Long qw(:config no_ignore_case bundling);
GetOptions(
	'd=s' => \$opt_d, # db name
	'u=s' => \$opt_u, # db user
	'p=s' => \$opt_p, # db pass
        'l=i' => \$opt_l, # batchsize limit
        );

$opt_d ||= 'eve';
$opt_u ||= 'eve';

$opt_l ||= 5000;

use EVE::Db;
my $dbh = EVE::Db::dbh;
die "cant connect to db: ".DBI->errstr unless $dbh;

my $version = 2;

my $sth = $dbh->prepare_cached(qq{
	SELECT
		distinct orderid
	FROM
		eve.marketRaw 
	WHERE
		processed = 0
	LIMIT $opt_l
	}) or die $dbh->errstr;
printf "Selecting up to %i orders ...", $opt_l;
$sth->execute() or die $sth->errstr;
printf " fetching ...";
my $orders = $sth->fetchall_arrayref();
$sth->finish();
die " no orders" unless @$orders;
my $cnt = scalar @$orders;
printf " loaded %i ...\n", $cnt;


my $upd_proc = $dbh->prepare_cached(qq{
	UPDATE
		eve.marketRaw
	SET
		processed = ?
	WHERE
		id = ?
	}) or die $dbh->errstr;

my %stats = ();
for my $o (@$orders) {
	my ($orderid,) = @$o;
#	printf "DBG: order %i\n", $orderid;
	$stats{"loaded orders"}++;

	my $sth = $dbh->prepare_cached(qq{
		SELECT
			*
		FROM
			eve.marketRaw
		WHERE
			orderID = ?
		ORDER BY
			orderID, volremain
			DESC
		}) or die $dbh->errstr;
	$sth->execute($orderid) or die $sth->errstr;

	my $last = undef;
	while (my $r = $sth->fetchrow_hashref) {
#		printf "DBG: order %i rawid %i\n", $orderid, $r->{id};
		$stats{"rows loaded"}++;
		unless ($stats{"rows loaded"} % ($cnt*2)) {
			printf "DBG: %i/%i orders (%i%%), %i rows\n", 
				$stats{"loaded orders"},
				$cnt,
				($stats{"loaded orders"}/$cnt)*100,
				$stats{"rows loaded"};
		}

		if (!$last) {
			$last = $r;
			$upd_proc->execute($version, $r->{id}) or die $upd_proc->errstr;
			next;
		}
		if (defined $r->{processed} && $r->{processed} >= $version &&
		    defined $last->{processed} && $last->{processed} >= $version) {
			$stats{"rows skipped"}++;
			$last = $r;
			next;
		}

		unless ($last->{stationid} == $r->{stationid}) {
			warn "location changed for order ".$r->{orderid};
			&zap_order($r->{orderid});
			$stats{"zapped location"}++;
			last;
		}
		unless ($last->{typeid} == $r->{typeid}) {
			warn "type changed for order ".$r->{orderid};
			&zap_order($r->{orderid});
			$stats{"zapped type"}++;
			last;
		}

		if ($last->{price} != $r->{price}) {
			$stats{"changed price"}++;
		} elsif ($last->{issued} ne $r->{issued}) {
			$stats{"changed issued"}++;
		} elsif ($last->{volremain} != $r->{volremain}) {
			my $pu = ($last->{price} + $r->{price}) / 2; 
			my $vd = $last->{volremain} - $r->{volremain};
			die "negative volume change for order ".$r->{orderid}
				unless $vd > 0;

			my ($sys, $reg,) = &get_location($r->{stationid});

			my $ins_trd = $dbh->prepare_cached(qq{
				INSERT INTO
					eve.marketTrades
				SET
					rawid = ?,

					orderid = ?,
					datatime = ?,

					stationid = ?,
					solarsystemid = ?,
					regionid = ?,

					typeid = ?,
					bid = ?,
					price = ?,
					volume = ?,

					processed = ?

				ON DUPLICATE KEY UPDATE
					volume = ?,
					processed = ?
					
				}) or die $dbh->errstr;

			$ins_trd->execute(
					$r->{id},
					$r->{orderid}, $r->{first},
					$r->{stationid}, $sys, $reg,
					$r->{typeid}, $r->{bid}, $pu, $vd,
					$version,
					$vd, $version
				) or die $ins_trd->errstr;
						
			$stats{"trades"}++;
			$stats{"moved isk"} += ($vd*$pu);

		} else {
			$stats{"changed nothing"}++;
		}

		$last = $r;
		$upd_proc->execute($version, $r->{id}) or die $upd_proc->errstr;
	}
	$sth->finish;
}

for (sort keys %stats) {
	printf "STATS: %s -> %s\n", 
		$_, 
		/(vol|isk)$/ ? &prettynum($stats{$_}) : $stats{$_};
}

if ($stats{"loaded orders"} < $opt_l) {
	die "bored";
}
exit 0;

sub zap_order ($) {
	my ($oid,) = @_;

	my $sth = $dbh->prepare_cached(qq{
		UPDATE
			eve.marketRaw
		SET
			processed = 255
		WHERE
			orderid = ?
		}) or die $dbh->errstr;
	$sth->execute($oid) or die $sth->errstr;


	$sth = $dbh->prepare_cached(qq{
		DELETE FROM
			eve.marketTrades
		WHERE
			orderid = ?
		}) or die $dbh->errstr;
	my $rc = $sth->execute($oid) or die $sth->errstr;

	if ($rc > 0) {
		warn "deleted $rc rows for bad orderid $oid";
	}
}

my %refcache;
my %loccache;
sub get_location ($) {
	my ($sta,) = @_;
	$stats{"stations asked"}++;

	if ($loccache{$sta}) {
		return @{$loccache{$sta}};
	}

	$stats{"stations loaded"}++;
	my $sth = $dbh->prepare_cached(qq{
		SELECT
			sys.solarSystemID,
			sys.regionID
		FROM
			eve.apiStations sta,
			dbo.mapSolarSystems sys
		WHERE
			sta.stationID = ?
			and sta.solarSystemID = sys.solarSystemID
		}) or die $dbh->errstr;
	$sth->execute($sta) or die $sth->errstr;
	my $r = $sth->fetchrow_arrayref();
	$sth->finish;
	die "no location for station $sta" unless $r;

#	if ($refcache{$r}) {
#		warn "reusing $r for $sta after ".$refcache{$r};
#	}
#	$refcache{$r} = $sta;

	# wtf rotate array
	my ($sys, $reg,) = @$r;
	my $loc = [$sys, $reg,];

	$loccache{$sta} = $loc;
	return @{$loccache{$sta}};
}

sub prettynum ($) {
	my $val = shift;


	if ($val > 10**9) {
		return sprintf "%.2f G", $val/10**9;
	} elsif ($val > 10**6) {
		return sprintf "%.2f M", $val/10**6;
	} elsif ($val > 10**3) {
		return sprintf "%.2f K", $val/10**3;
	} else {
		return $val;
	}
	die "never here";
}