CREATE OR REPLACE FUNCTION insert_proxy_bid(p_auction_company_id int, p_auction_id bigint, p_person_id bigint, p_bid numeric) RETURNS integer AS $$
	DECLARE
		t_person_id bigint;
		t_high_bidder_id bigint;
		t_high_bidder_proxy_bid numeric(15,5);
		t_auction_proxy_bids_id bigint;
		t_total_bids int;
		t_bid_increment numeric(15,5);
		t_high_bid numeric(15,5);
		t_starting_price numeric(15,5);
		t_proxy_loop boolean;
		t_snipe_check int;
		t_expire_check boolean;
	BEGIN
		-- run checks to determine whether we should proceed with this proxy bid placement
		SELECT INTO 
			t_high_bidder_id,
			t_expire_check
			auction.high_bidder_id,
			CASE WHEN current_timestamp >= ends THEN 't' ELSE 'f' END
		FROM 
			auction,
			person
		WHERE auction.seller_id = person.person_id
		AND auction_id = p_auction_id
		AND auction_company_id = p_auction_company_id
		AND auction_type_id = 1
		AND p_person_id != auction.seller_id;
		IF FOUND AND t_expire_check = 'f' THEN
			SELECT INTO 
				t_person_id 
				person_id
			FROM person
			WHERE auction_company_id = p_auction_company_id
			AND person_id = p_person_id;
			IF FOUND THEN
				-- acquire exclusive row level lock for this transaction
				SELECT INTO 
					t_total_bids,
					t_bid_increment,
					t_starting_price,
					t_high_bid
					total_bids, (
						SELECT bid_increment
						FROM auction_bid_increment_level
						WHERE auction_company_id = p_auction_company_id
						AND ( 
							( high_bid BETWEEN lower_limit AND upper_limit ) 
							OR ( high_bid >= lower_limit AND upper_limit = -1 )
						)
					) AS bid_increment,
					starting_price,
					high_bid
				FROM auction
				WHERE auction_id = p_auction_id
				FOR UPDATE;
				IF t_total_bids > 0 THEN
					-- bids exist so run the proxy bidding
					IF p_bid >= t_high_bid + t_bid_increment THEN
						-- first insert the incoming bid into proxy bid queue
						INSERT INTO auction_proxy_bids ( auction_id, person_id, bid ) 
						VALUES ( p_auction_id, p_person_id, p_bid );
						SELECT INTO t_auction_proxy_bids_id currval('auction_proxy_bids_serial');
		
						-- only start if incoming bid is not high bidder
						IF t_person_id <> t_high_bidder_id THEN 
							-- get the max proxy bid for high bidder
							SELECT INTO
								t_high_bidder_proxy_bid
								MAX(bid)
							FROM auction_proxy_bids 
							WHERE auction_id = p_auction_id
							AND person_id = t_high_bidder_id;

							-- now process the proxy bids ( high bidder vs incoming proxy bid )
							t_proxy_loop := 't';
							WHILE t_proxy_loop LOOP
								-- get the current bid increment for this level
								SELECT INTO
									t_bid_increment
									bid_increment
								FROM auction_bid_increment_level
								WHERE auction_company_id = p_auction_company_id
								AND ( 
									( t_high_bid BETWEEN lower_limit AND upper_limit ) 
									OR ( t_high_bid >= lower_limit AND upper_limit = -1 ) 
								);
								t_high_bid := t_high_bid + t_bid_increment;
								IF t_high_bidder_proxy_bid >= t_high_bid AND p_bid >= t_high_bid THEN
									INSERT INTO auction_bids ( auction_id, person_id, bid )
									VALUES ( p_auction_id, t_high_bidder_id, t_high_bid );
									SELECT INTO t_snipe_check snipe_check(p_auction_id);
									UPDATE auction 
									SET
										high_bidder_id = t_high_bidder_id,
										high_bid = t_high_bid,
										total_bids = total_bids + 1
									WHERE auction_id = p_auction_id;
								ELSIF p_bid >= t_high_bid THEN
									INSERT INTO auction_bids ( auction_id, person_id, bid )
									VALUES ( p_auction_id, p_person_id, t_high_bid );
									SELECT INTO t_snipe_check snipe_check(p_auction_id);
									UPDATE auction 
									SET
										high_bidder_id = p_person_id,
										high_bid = t_high_bid,
										total_bids = total_bids + 1
									WHERE auction_id = p_auction_id;
									t_proxy_loop = 'f';
								ELSE 
									t_proxy_loop = 'f';
								END IF;
								-- determine who the winner was and send losing party email, etc...
							END LOOP;
						END IF;
						RETURN t_auction_proxy_bids_id;
					END IF;
				ELSIF p_bid >= t_starting_price THEN
					-- no bids have been placed yet, so adjust the first bid to match the starting price
					INSERT INTO auction_proxy_bids ( auction_id, person_id, bid ) 
					VALUES ( p_auction_id, p_person_id, p_bid );
					SELECT INTO t_auction_proxy_bids_id currval('auction_proxy_bids_serial');
					INSERT INTO auction_bids ( auction_id, person_id, bid )
					VALUES ( p_auction_id, p_person_id, t_starting_price );
					SELECT INTO t_snipe_check snipe_check(p_auction_id);
					UPDATE auction 
					SET
						high_bidder_id = p_person_id,
						high_bid = t_starting_price,
						total_bids = total_bids + 1
					WHERE auction_id = p_auction_id;
					RETURN t_auction_proxy_bids_id;
				END IF;
			END IF;
		END IF;
		RETURN 0;
	END;
$$ LANGUAGE plpgsql;