#!/usr/bin/env perl

use DBI;

my $dbh = DBI->connect('DBI:mysql:dbo', 'eve', '', { RaiseError => 1, AutoCommit => 0});

my %offers;

my $sth_offers = $dbh->prepare(q{
select n.itemName corpName,
       t.typeName rewardName,
       l.quantity,
       l.iskCost,
       l.lpCost,
       group_concat(concat(t2.typeName,'x',i.quantity) ORDER BY t2.typeName SEPARATOR ', ') items,
       l.corporationID,
       concat(l.typeID, 'x', l.quantity, ':', l.lpCost, '-', l.iskCost) offerIDs,
       group_concat(concat(i.typeID,'x',i.quantity) ORDER BY i.typeID SEPARATOR ',') itemIDs
from   oldLpStore l
join   invNames n on (l.corporationID = n.itemID)
join   invTypes t on (l.typeID = t.typeID)
left outer join oldLpRequiredItems i on (l.id = i.parentID)
left outer join invTypes t2 on (i.typeID = t2.typeID)
group by l.corporationID, t.typeName, l.quantity, l.iskCost, l.lpCost
order by l.corporationID, t.typeName, l.quantity, l.iskCost, l.lpCost
});

$sth_offers->execute;

while (my $corp_offer = $sth_offers->fetchrow_hashref) {
	my $offer = $corp_offer->{offerIDs} . "|" . $corp_offer->{itemIDs};
	
	if (!$offers{$offer}) {
		$offers{$offer} = [];
	}
	
	push @{$offers{$offer}}, $corp_offer->{corporationID};
}

$dbh->do("delete from lpStore");
$dbh->do("delete from lpOffer");
$dbh->do("alter table lpOffer AUTO_INCREMENT=1");
$dbh->do("delete from lpOfferRequiredItem");

my $sth_ins_lpoffer = $dbh->prepare("insert into lpOffer (offerID, typeID, quantity, lpCost, iskCost) values (null, ?, ?, ?, ?)");
my $sth_ins_lpofferitems = $dbh->prepare("insert into lpOfferRequiredItem (offerID, typeID, quantity) values (?, ?, ?)");
my $sth_ins_lpstore  = $dbh->prepare("insert into lpStore (corporationID, offerID) values (?, ?)");

foreach my $offerKey (keys %offers) {
	print $offerKey . "\n";

	my ($typeID, $quantity, $lpCost, $iskCost, $items) = $offerKey =~ /^(\d+)x(\d+):(\d+)-(\d+)\|(.*)$/;
	
	$sth_ins_lpoffer->execute($typeID, $quantity, $lpCost, $iskCost);
	$offerID = $dbh->{'mysql_insertid'};

	foreach my $item (split /,/, $items) {
		my ($requiredTypeID, $requiredQuantity) = split /x/, $item;
		$sth_ins_lpofferitems->execute($offerID, $requiredTypeID, $requiredQuantity);
	}
	
	foreach my $corporationID (@{$offers{$offerKey}}) {
		$sth_ins_lpstore->execute($corporationID, $offerID);
	}
}

$dbh->commit;