where ishero

where playername = '<insert username>'

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 playername, gt.gametypedescription as "Stakes" ,count(*) as "Hands", round(sum(a.rakeamount/a.numberofplayers))/100.0 as "Dealt Rake",
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 left join gametypes gt using(gametype_id) 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) left join players using(player_id)
where ishero and potsize>0 and a.handtimestamp>'2011-01-01 00:00:00' and a.site_id=2 GROUP BY Playername, "Stakes" ORDER BY Playername, "Hands" DESC;

CREATE LANGUAGE plpgsql;