/* James Haley */

CREATE OR REPLACE PROCEDURE Bid_on_it
	(
		xUSERID		IN	VARCHAR2,
		xBIDMAX		IN	NUMBER,
		xBIDID		IN	INTEGER,
		xITEMID		IN	INTEGER
	)
AS
	/* variables here */
	itemOpen	CHAR(1);
	curHighBid	NUMBER(9, 2);
	curNumBids	INTEGER;
	startPrice	NUMBER(9, 2);
	incrAmount	NUMBER(9, 2);
	curMaxBid	NUMBER(9, 2);

	CURSOR		bidcursor IS
			SELECT *
			FROM BID
			WHERE ITEMID = xITEMID;
BEGIN
	/* assumptions: user id and item id are legal */
	
	/* check that the specified item is open for bidding */
	SELECT OPEN INTO itemOpen
	FROM ITEM
	WHERE ITEMID = xITEMID;
	
	IF itemOpen = 'N' THEN
		BEGIN
			DBMS_OUTPUT.PUT_LINE('Error: item is not open for bidding!');
			RETURN;
		END;
	END IF;
		
	/* make sure bid is greater than starting price */
	SELECT ST_PRICE INTO startPrice
	FROM ITEM
	WHERE ITEMID = xITEMID;
	
	IF xBIDMAX < startPrice THEN
		BEGIN
			DBMS_OUTPUT.PUT_LINE('Error: bid is below minimum price for item');
			RETURN;
		END;
	END IF;

	/* get number of bids on item */
	SELECT NOFBIDS INTO curNumBids
	FROM ITEM
	WHERE ITEMID = xITEMID;

	IF curNumBids != 0 THEN
		BEGIN
			/* get value of the current high bid */
			SELECT HBID INTO curHighBid
			FROM ITEM
			WHERE ITEM.ITEMID = xITEMID;
			      
			/* calculate increment */
			IF curHighBid < 1.00 THEN
				BEGIN
					incrAmount := 0.05;
				END;
			ELSIF curHighBid < 5.00 THEN
				BEGIN
					incrAmount := 0.25;
				END;
			ELSIF curHighBid < 25.00 THEN
				BEGIN
					incrAmount := 0.50;
				END;
			ELSIF curHighBid < 100.00 THEN
				BEGIN
					incrAmount := 1.00;
				END;
			ELSIF curHighBid < 250.00 THEN
				BEGIN
					incrAmount := 2.50;
				END;
			ELSE
				BEGIN
					incrAmount := 10.00;
				END;
			END IF;
			      
			/* make sure bid is not less than one increment more */
			IF xBIDMAX < curHighBid + incrAmount THEN
				BEGIN
					DBMS_OUTPUT.PUT_LINE('Error: bid does not meet increment');
					RETURN;
				END;
			END IF;
			
			curMaxBid := 0.0;
			
			/* see if this user is outbid */
			FOR bid IN bidcursor
				LOOP
					IF bid.bidmax > curMaxBid THEN
						BEGIN
							curMaxBid := bid.bidmax;
						END;
					END IF;
				END LOOP;
			
			IF xBIDMAX < curMaxBid THEN
				BEGIN
					/* update the current winner's bid and exit */
					UPDATE ITEM
					SET HBID = xBIDMAX + 0.01
					WHERE ITEMID = xITEMID;
					
					DBMS_OUTPUT.PUT_LINE('User was outbid by current winner');
					RETURN;
				END;
			END IF;
			
			/* user should now be winning the auction */
			/* create the new bid */
			INSERT INTO BID VALUES (xUSERID, xBIDMAX, xBIDID, xITEMID);
			
			/* update the item to refer to this bid */
			UPDATE ITEM
			SET NOFBIDS = NOFBIDS + 1,
			HBID = HBID + incrAmount,
			HBIDDER = xUSERID
			WHERE ITEMID = xITEMID;
			
			DBMS_OUTPUT.PUT_LINE('User is now high bidder for item');
			RETURN;
		END;
	END IF;
	
	/* curNumBids is zero, so the user is automatically winning */
	INSERT INTO BID VALUES (xUSERID, xBIDMAX, xBIDID, xITEMID);
			
	/* update the item to refer to this bid */
	UPDATE ITEM
	SET NOFBIDS = NOFBIDS + 1,
	HBID = startPrice,
	HBIDDER = xUSERID
	WHERE ITEMID = xITEMID;
			
	DBMS_OUTPUT.PUT_LINE('User is now high bidder for item');			
END;