create or replace function amountPreflopInvested(tn playerhandscashmisc, pkh_id integer) returns integer AS $$
DECLARE
	amount integer := 0;
	other integer := 0;
BEGIN
	amount := tn.postamountpreflop + tn.betamountpreflop + tn.callamountpreflop;
	select max(pre.postamountpreflop + pre.betamountpreflop + pre.callamountpreflop) into other from pokerhands join 

playerhandscashkeycolumns using (pokerhand_id) join playerhandscashmisc pre using (playerhand_id) where pokerhand_id=pkh_id and not ishero;
	return least(amount, other);
END;
$$ language plpgsql;

create or replace function amountFlopInvested(tn playerhandsflop, pkh_id integer) returns integer AS $$
DECLARE
	amount integer := 0;
	other integer := 0;
BEGIN
	amount := tn.betamount + tn.callamount;
	select max(flop.betamount+flop.callamount) into other from pokerhands join playerhandscashkeycolumns using (pokerhand_id) join 

playerhandscashmisc using (playerhand_id) join playerhandsflop flop using(playerhand_id) where pokerhand_id=pkh_id and not ishero;
	return least(amount, other);
END;
$$ language plpgsql;

create or replace function amountTurnInvested(tn playerhandsturn, pkh_id integer) returns integer AS $$
DECLARE
	amount integer := 0;
	other integer := 0;
BEGIN
	amount := tn.betamount + tn.callamount;
	select max(turn.betamount+turn.callamount) into other from pokerhands join playerhandscashkeycolumns using (pokerhand_id) join 

playerhandscashmisc using (playerhand_id) join playerhandsturn turn using(playerhand_id) where pokerhand_id=pkh_id and not ishero;
	return least(amount, other);
END;
$$ language plpgsql;

create or replace function amountRiverInvested(tn playerhandsriver, pkh_id integer) returns integer AS $$
DECLARE
	amount integer := 0;
	other integer := 0;
BEGIN
	amount := tn.betamount + tn.callamount;
	select max(river.betamount+river.callamount) into other from pokerhands join playerhandscashkeycolumns using (pokerhand_id) join 

playerhandscashmisc using (playerhand_id) join playerhandsriver river using(playerhand_id) where pokerhand_id=pkh_id and not ishero;
	return least(amount, other);
END;
$$ language plpgsql;
	

select count(*) as "Hands", round(sum(a.rakeamount/a.numberofplayers))/100.0 as "Rake dealt",
round ( sum(a.rakeamount * 1.* (
		case when (b.maxstreetseen=0 or b.streetwentallin=1) then (amountPreflopInvested(c, a.pokerhand_id))
		when (b.maxstreetseen=1 or b.streetwentallin=2) then (c.postamountpreflop + c.betamountpreflop + c.callamountpreflop + 

amountFlopInvested(f, a.pokerhand_id))
		when (b.maxstreetseen=2 or b.streetwentallin=3) then (c.postamountpreflop + c.betamountpreflop + c.callamountpreflop + 

f.betamount + f.callamount + amountTurnInvested(t, a.pokerhand_id))
		else (c.postamountpreflop + c.betamountpreflop + c.callamountpreflop + f.betamount + f.callamount + t.betamount + 

t.callamount + amountRiverInvested(r, a.pokerhand_id)) end
	) / (a.potsize))
) / 100.0 as "WC Rake"
from pokerhands a join playerhandscashkeycolumns b using (pokerhand_id) join playerhandscashmisc c using (playerhand_id)
left join playerhandsflop f using(playerhand_id) left join playerhandsturn t using(playerhand_id) left join playerhandsriver r using

(playerhand_id)
where ishero and potsize>0 and a.handtimestamp>'2011-01-01 00:00:00' and a.site_id=2;