USE master;
CREATE DATABASE ssb_ShoppingCart;
GO

USE ssb_ShoppingCart;
GO

CREATE MESSAGE TYPE AddItem VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE RemoveItem VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE Confirm VALIDATION = Empty;
CREATE MESSAGE TYPE Invoice VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT CartContract (
	AddItem SENT BY INITIATOR,
	RemoveItem SENT BY INITIATOR,
	Confirm SENT BY INITIATOR,
	Invoice SENT BY TARGET
);
GO

/*
CREATE ASSEMBLY BrokerAssembly
FROM 'C:\Demo\SDV09 - Asynchronicity\Demo 1 - Service Broker\ServiceBrokerInterface\cs\ServiceBrokerInterface\bin\Debug\ServiceBrokerInterface.dll'
GO
*/

CREATE ASSEMBLY ShoppingCartAssembly
FROM 'C:\Demo\SDV09 - Asynchronicity\Demo 1 - Service Broker\ShoppingCartService\CS\ShoppingCartService\bin\debug\ShoppingCartService.dll'
GO

CREATE PROC ServiceProc
AS
EXTERNAL NAME ShoppingCartAssembly.ShoppingCartService.ServiceProc
GO


CREATE QUEUE ServiceQueue
	WITH
	STATUS = ON,
	RETENTION = ON,
	ACTIVATION (
		STATUS = ON,
		PROCEDURE_NAME = ServiceProc,
		MAX_QUEUE_READERS = 4,
		EXECUTE AS SELF
	)
	ON [default];
CREATE QUEUE ClientQueue;
GO

CREATE SERVICE ShoppingCartService ON QUEUE ServiceQueue (CartContract);
CREATE SERVICE ShoppingCartClient ON QUEUE ClientQueue (CartContract);
GO

CREATE TABLE ShoppingList (
	cgid UNIQUEIDENTIFIER NOT NULL,
	itemnumber INT,
	quantity INT,
	unitprice FLOAT,
	name VARCHAR(255)
);

CREATE TABLE StateTable (
	cgid UNIQUEIDENTIFIER NOT NULL,
	state INT,
	PRIMARY KEY(cgid)
);
GO

CREATE PROC LoadState (@cgid UNIQUEIDENTIFIER)
AS
SELECT @cgid;
SELECT state FROM StateTable WHERE cgid=@cgid;
SELECT itemnumber, quantity, unitprice, name 
	FROM ShoppingList
	WHERE cgid = @cgid;

GO
USE ssb_ShoppingCart;
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO
