CREATE TABLE [dbo].[last] (
	[username] [varchar] (250) NOT NULL,
	[seconds] [varchar] (50) NOT NULL,
	[state] [varchar] (100) NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rostergroups] (
	[username] [varchar] (250) NOT NULL,
	[jid] [varchar] (250) NOT NULL,
	[grp] [varchar] (100) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rosterusers] (
	[username] [varchar] (250) NOT NULL,
	[jid] [varchar] (250) NOT NULL,
	[nick] [varchar] (50) NULL,
	[subscription] [char] (1) NOT NULL,
	[ask] [char] (1) NOT NULL,
	[server] [char] (1) NOT NULL,
	[subscribe] [varchar] (200) NULL,
	[type] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[spool] (
	[id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL ,
	[username] [varchar] (250) NOT NULL,
	[xml] [text] NULL,
	[created] [datetime] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
	[username] [varchar] (250) NOT NULL,
	[password] [varchar] (50) NOT NULL,
	[created] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[vcard] (
	[username] [varchar] (250) NOT NULL,
	[full_name] [varchar] (250) NULL,
	[first_name] [varchar] (50) NULL,
	[last_name] [varchar] (50) NULL,
	[nick_name] [varchar] (50) NULL,
	[url] [varchar] (1024) NULL,
	[address1] [varchar] (50) NULL,
	[address2] [varchar] (50) NULL,
	[locality] [varchar] (50) NULL,
	[region] [varchar] (50) NULL,
	[pcode] [varchar] (50) NULL,
	[country] [varchar] (50) NULL,
	[telephone] [varchar] (50) NULL,
	[email] [varchar] (250) NULL,
	[orgname] [varchar] (50) NULL,
	[orgunit] [varchar] (50) NULL,
	[title] [varchar] (50) NULL,
	[role] [varchar] (50) NULL,
	[b_day] [datetime] NULL ,
	[descr] [varchar] (500) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[last] WITH NOCHECK ADD 
	CONSTRAINT [PK_last] PRIMARY KEY  CLUSTERED 
	(
		[username]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD 
	CONSTRAINT [PK_rostergroups] PRIMARY KEY  CLUSTERED 
	(
		[username],
		[grp],
		[jid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[spool] WITH NOCHECK ADD 
	CONSTRAINT [PK_spool] PRIMARY KEY  CLUSTERED 
	(
		[username],
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[users] WITH NOCHECK ADD 
	CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED 
	(
		[username]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD 
	CONSTRAINT [PK_vcard] PRIMARY KEY  CLUSTERED 
	(
		[username]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[rosterusers] ADD 
	CONSTRAINT [PK_rosterusers] PRIMARY KEY  NONCLUSTERED 
	(
		[username],
		[jid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[spool] ADD 
	CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created]
GO

ALTER TABLE [dbo].[users] ADD 
	CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
GO

CREATE PROCEDURE [dbo].[add_roster]
	@Username		varchar(250),
	@JID			varchar(250),
	@Nick			varchar(50),
	@Subscription	char(1),
	@Ask			char(1),
	@Server			char(1),
	@Subscribe		varchar(200),
	@Type			varchar(50),
	@Grp			varchar(100)
AS
BEGIN
	BEGIN TRANSACTION
		--- Update Roster Users
		IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
			BEGIN
				UPDATE rosterusers
				SET	rosterusers.username=@username,
					rosterusers.jid=@JID,
					rosterusers.nick=@Nick,
					rosterusers.subscription=@Subscription,
					rosterusers.ask=@Ask,
					rosterusers.server=@Server,
					rosterusers.subscribe=@Subscribe,
					rosterusers.type=@Type
				WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
			END
		ELSE
			BEGIN
				INSERT INTO rosterusers
					(	rosterusers.username,
						rosterusers.jid,
						rosterusers.nick,
						rosterusers.subscription,
						rosterusers.ask,
						rosterusers.server,
						rosterusers.subscribe,
						rosterusers.type
					)
				VALUES
					(	@username,
						@JID,
						@Nick,
						@Subscription,
						@Ask,
						@Server,
						@Subscribe,
						@Type
					);
			END
		
		--- Update Roster Groups
		IF EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
			BEGIN
				UPDATE rostergroups
				SET	rostergroups.username=@username,
					rostergroups.jid=@JID,
					rostergroups.grp=@Grp
				WHERE (rostergroups.username=@Username) AND (rostergroups.jid=@JID);
			END
		ELSE
			BEGIN
				INSERT INTO rostergroups
					(	rostergroups.username,
						rostergroups.jid,
						rostergroups.grp
					)
				VALUES
					(	@username,
						@JID,
						@Grp
					);
			END
	COMMIT
END
GO

CREATE PROCEDURE [dbo].[add_roster_group]
	@Username		varchar(250),
	@JID			varchar(250),
	@Grp			varchar(100)
AS
BEGIN
    --- Update Roster Groups
	IF EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
		BEGIN
			UPDATE rostergroups
			WITH (ROWLOCK)
			SET	rostergroups.username=@username,
				rostergroups.jid=@JID,
				rostergroups.grp=@Grp
			WHERE (rostergroups.username=@Username) AND (rostergroups.jid=@JID)
		END
	ELSE
		BEGIN
			INSERT INTO rostergroups
				(	rostergroups.username,
					rostergroups.jid,
					rostergroups.grp
				)
			VALUES
				(	@username,
					@JID,
					@Grp
				)
		END
END
GO

CREATE PROCEDURE [dbo].[add_roster_user]
	@Username		varchar(250),
	@JID			varchar(250),
	@Nick			varchar(50),
	@Subscription		char(1),
	@Ask			char(1),
	@Server		        char(1),
	@Subscribe		varchar(200),
	@Type			varchar(50),
	@Grp			varchar(100) = Null
AS
BEGIN
	BEGIN TRANSACTION
		--- Update Roster Users
		IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
			BEGIN
				UPDATE rosterusers
				SET	rosterusers.username=@username,
					rosterusers.jid=@JID,
					rosterusers.nick=@Nick,
					rosterusers.subscription=@Subscription,
					rosterusers.ask=@Ask,
					rosterusers.server=@Server,
					rosterusers.subscribe=@Subscribe,
					rosterusers.type=@Type
				WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
			END
		ELSE
			BEGIN
				INSERT INTO rosterusers
					(	rosterusers.username,
						rosterusers.jid,
						rosterusers.nick,
						rosterusers.subscription,
						rosterusers.ask,
						rosterusers.server,
						rosterusers.subscribe,
						rosterusers.type
					)
				VALUES
					(	@username,
						@JID,
						@Nick,
						@Subscription,
						@Ask,
						@Server,
						@Subscribe,
						@Type
					);
			END
		--- Update Roster Group
		IF @Grp IS NOT NULL
			EXECUTE [dbo].[add_roster_group] @username, @JID, @Grp
	COMMIT
END
GO

CREATE PROCEDURE [dbo].[add_spool]
	@Username varchar(250),
	@XML varchar(8000)
AS
BEGIN
	INSERT INTO spool
		(	spool.username,
			spool.xml
		)
	VALUES
		(	@Username,
			@XML
		)
END
GO

CREATE PROCEDURE [dbo].[add_user]
	@Username varchar(200),
	@Password varchar(50)
AS
BEGIN
    INSERT INTO users 
			(	username, 
				[password]
			) 
	VALUES 
			(	@Username,
				@Password
			);
END
GO

CREATE PROCEDURE [dbo].[del_last]
	@Username	varchar(250)
AS
BEGIN
	IF EXISTS (SELECT username FROM last with (nolock) WHERE last.username=@Username)
	BEGIN
		DELETE FROM [last]
		WITH (ROWLOCK)
		WHERE [last].username=@Username;
	END
END
GO


CREATE PROCEDURE [dbo].[del_roster]
	@Username varchar(200),
	@JID varchar(250)
AS
BEGIN
	BEGIN TRANSACTION
		IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
		BEGIN
			DELETE FROM rosterusers 
				WITH (ROWLOCK)
				WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
		END
		IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID))
		BEGIN
			DELETE FROM rostergroups 
				WITH (ROWLOCK)
				WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
		END
	COMMIT
END
GO


CREATE PROCEDURE [dbo].[del_spool_msg]
	@Username varchar(250)
AS
BEGIN
	IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
	BEGIN
		DELETE FROM spool
			WITH (ROWLOCK)
			WHERE spool.username=@Username;
	END
END
GO


CREATE PROCEDURE [dbo].[del_user]
	@Username varchar(200)
AS
BEGIN
	IF EXISTS (SELECT username FROM users with (nolock) WHERE username=@Username)
	BEGIN
		DELETE FROM users 
			WITH (ROWLOCK)
			WHERE username=@Username;
	END
END
GO

CREATE PROCEDURE [dbo].[del_user_return_password]
	@Username varchar(200)
AS
DECLARE
	@Pwd varchar(50)
BEGIN
	EXECUTE @Pwd = dbo.get_password @Username
	IF EXISTS (SELECT username FROM users with (nolock) WHERE username=@Username)
	BEGIN
		DELETE FROM users 
			WITH (ROWLOCK)
			--- Sql server 2005 optimization in one transaction
			---		OUTPUT deleted.password as password
			WHERE username=@Username
	END
	SELECT @Pwd;
END
GO

CREATE PROCEDURE [dbo].[del_user_roster]
	@Username varchar(250)
AS
BEGIN
	BEGIN TRANSACTION
		IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
		BEGIN
			DELETE FROM rosterusers 
				WITH (ROWLOCK)
				WHERE rosterusers.username = @Username;
		END
		
		IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
		BEGIN
			DELETE FROM rostergroups 
				WITH (ROWLOCK)
				WHERE rostergroups.username = @Username;
		END
	COMMIT
END
GO

CREATE PROCEDURE [dbo].[get_and_del_spool_msg]
	@Username varchar(250)
AS
DECLARE
	@vSpool table(	
        username varchar(1),
	xml varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
		BEGIN
			SELECT	spool.username AS username,
					spool.xml AS xml
			FROM	spool WITH (NOLOCK)
			WHERE	spool.username=@Username;
			DELETE spool 
			 WITH (ROWLOCK)
			 WHERE spool.username=@Username		
		END
	ELSE
		BEGIN
			SELECT * FROM @vSpool;
		END
END
GO

CREATE PROCEDURE [dbo].[get_last]
	@Username varchar(250)
AS
BEGIN
	SELECT	last.seconds AS seconds,
			last.state AS state
	FROM last WITH (NOLOCK)
	WHERE last.username=@Username;
END
GO

CREATE PROCEDURE [dbo].[get_password]
	@Username varchar(200)
AS
BEGIN
    SELECT users.password as password 
	FROM users WITH (NOLOCK)
	WHERE username=@Username;
END
GO

CREATE PROCEDURE [dbo].[get_roster]
	@Username varchar(200)
AS
DECLARE
	@vRosterusers table(	username		varchar(1), 
							jid				varchar(1), 
							nick			varchar(1), 
							subscription	varchar(1), 
							ask				varchar(1),
							server			varchar(1), 
							subscribe		varchar(1), 
							type			varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
		BEGIN
			SELECT	rosterusers.username AS username, 
					rosterusers.jid AS jid, 
					rosterusers.nick AS nick, 
					rosterusers.subscription AS subscription, 
					rosterusers.ask AS ask,
					rosterusers.server AS server, 
					rosterusers.subscribe AS subscribe, 
					rosterusers.type AS type
			FROM rosterusers WITH (NOLOCK)
			WHERE rosterusers.username = @Username;
		END
	ELSE
		BEGIN
			SELECT * FROM @vRosterusers
		END
END
GO

CREATE PROCEDURE [dbo].[get_roster_by_jid]
	@Username varchar(200),
	@JID varchar(250)
AS
DECLARE
	@vRosterusers table(	username		varchar(1), 
						jid				varchar(1), 
						nick			varchar(1), 
						subscription	varchar(1),
						ask				varchar(1),
						server			varchar(1), 
						subscribe		varchar(1), 
						type			varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
		BEGIN
			SELECT	rosterusers.username AS username, 
					rosterusers.jid AS jid, 
					rosterusers.nick AS nick, 
					rosterusers.subscription AS subscription, 
					rosterusers.ask AS ask,
					rosterusers.server AS server, 
					rosterusers.subscribe AS subscribe, 
					rosterusers.type AS type
			FROM rosterusers WITH (NOLOCK)
			WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
		END
	ELSE
		BEGIN
			SELECT * FROM @vRosterusers
		END
END
GO

CREATE PROCEDURE [dbo].[get_roster_groups]
	@Username varchar(200)
AS
DECLARE
	@vrostergroups table(	jid		varchar(1), 
							grp		varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
		BEGIN
			SELECT	rostergroups.jid AS jid, 
					rostergroups.grp AS grp
			FROM rostergroups WITH (NOLOCK)
			WHERE rostergroups.username = @Username;
		END
	ELSE
		BEGIN
			SELECT * FROM @vrostergroups
		END
END
GO

CREATE PROCEDURE [dbo].[get_rostergroup_by_jid]
	@Username varchar(250),
	@JID varchar(250)
AS
DECLARE
	@vrostergroups table(grp varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
		BEGIN
			SELECT rostergroups.grp AS grp
			FROM rostergroups WITH (NOLOCK)
			WHERE rostergroups.username=@Username AND rostergroups.jid=@JID;
		END
	ELSE
		BEGIN
			SELECT * FROM @vrostergroups
		END
END
GO

CREATE PROCEDURE [dbo].[get_subscription]
	@Username varchar(250),
	@JID varchar(250)
AS
DECLARE
	@vrosterusers table(subscription varchar(1))
BEGIN
	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
		BEGIN
			SELECT rosterusers.subscription AS subscription 
			FROM rosterusers WITH (NOLOCK)
			WHERE rosterusers.username=@Username AND rosterusers.jid=@JID;
		END
	ELSE
		BEGIN
			SELECT * FROM @vrosterusers
		END
END
GO

CREATE PROCEDURE [dbo].[list_users]
AS
BEGIN
    SELECT users.username AS username FROM users WITH (NOLOCK);
END
GO

CREATE PROCEDURE [dbo].[set_last]
	@Username	varchar(250),
	@Seconds	varchar(50), 
	@State		varchar(100)
AS
BEGIN
	IF EXISTS (SELECT username FROM [last] WITH (NOLOCK) WHERE username=@Username)
		BEGIN
			UPDATE [last] SET
				[last].username = @Username,
				[last].seconds = @Seconds,
				[last].state = @State
			WHERE last.username=@Username;
		END
	ELSE
		BEGIN
			INSERT INTO [last]
				(	[last].username,
					[last].seconds,
					[last].state
				)
			VALUES
				(	@Username,
					@Seconds,
					@State
				)
		END
END
GO

CREATE PROCEDURE [dbo].[set_password]
	@Username varchar(200), 
	@Password varchar(50)
AS
BEGIN
    IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username)
		BEGIN
			UPDATE users SET username=@Username, password=@Password WHERE username=@Username;
		END
    ELSE
		BEGIN
			INSERT INTO users (username, password) VALUES (@Username, @Password);
		END
END
