IF EXISTS (SELECT name FROM sysobjects WHERE name = 'lab8_yipdw_reset' AND type = 'P')
BEGIN
DROP PROCEDURE lab8_yipdw_reset
END
GO

CREATE PROCEDURE lab8_yipdw_reset
AS
PRINT 'Resetting...'
PRINT 'Resetting stock for product 22 to 104 units.'
UPDATE Products SET UnitsInStock=104 WHERE ProductID=22;
PRINT 'Removing order (OrderID=11077, ProductID=22).'
DELETE FROM [Order Details] WHERE OrderID=11077 AND ProductID=22;
GO

DECLARE @t1 AS INTEGER, @fail AS TINYINT
SELECT @fail=0;
EXECUTE lab8_yipdw_reset
PRINT 'Adding order to Order Details table for 50 units of product 22.'
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (11077, 22, 21.00, 50, 0.0) 
SELECT @t1=UnitsInStock FROM [Products] WHERE ProductID=22;

-- Check if UnitsInStock count was properly decremented.
PRINT 'Test 1: Was UnitsInStock properly decremented for product 22?'
IF @t1 = 54 
BEGIN
PRINT 'Test 1 passed.'
END
ELSE
BEGIN
PRINT 'Test 1 failed - I inserted an order for 50 units of Product 22, but the change was not made in the Products table.'
SELECT @fail = 1;
END

-- Check to see if the order actually exists in the Order Details table.
PRINT 'Test 2: Is the order actually in Order Details?'
SELECT @t1=COUNT(ProductID) FROM [Order Details] WHERE OrderID=11077 AND ProductID=22
IF @t1 = 1
BEGIN
PRINT 'Test 2 passed.'
END
ELSE
BEGIN
PRINT 'Test 2 failed - the order does not appear in the Order Details table.'
SELECT @fail = 1;
END

-- Ensure that the order does not go through if we order too much.
EXECUTE lab8_yipdw_reset
PRINT 'Test 3: Are unfillable orders rejected?'
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (11077, 22, 21.00, 9999, 0.0) 
SELECT @t1=COUNT(ProductID) FROM [Order Details] WHERE OrderID=11077 AND ProductID=22
IF @t1 = 0
BEGIN
PRINT 'Test 3 passed.'
END
ELSE
BEGIN
PRINT 'Test 3 failed - the order was placed even though there are not enough units in stock to fill the order.'
SELECT @fail = 1;
END

-- Ensure that the original UnitsInStock remains unaffected after unsuccessful order.
PRINT 'Test 4: Are unfillable orders prevented from modifying the number of units in stock?'
SELECT @t1=UnitsInStock FROM [Products] WHERE ProductID=22;
IF @t1 = 104
BEGIN
PRINT 'Test 4 passed.'
END
ELSE
BEGIN
PRINT 'Test 4 failed -- an order for product 22 that could not be filled has somehow changed the number of units in stock for product 22.'
SELECT @fail = 1;
END

EXECUTE lab8_yipdw_reset
DROP PROCEDURE lab8_yipdw_reset

IF @fail = 0
BEGIN
PRINT 'All tests passed.'
END
ELSE
BEGIN
PRINT 'Not all tests were successful.'
END