if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_delUserPerChat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_delUserPerChat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_del_SessionUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_del_SessionUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getChatDescription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getChatDescription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getChatMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getChatMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getChatUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getChatUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getChatUserByLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getChatUserByLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getNewChatMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getNewChatMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getNewChatMessageNS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getNewChatMessageNS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getNewChatMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getNewChatMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getNewChatMessagesNS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getNewChatMessagesNS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_getUserPerChat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getUserPerChat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_insChatMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_insChatMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_insChatSession]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_insChatSession]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_insChatUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_insChatUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_insUserPerChat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_insUserPerChat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChatMessages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChatMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChatSessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChatSessions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChatUserPerChat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChatUserPerChat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChatUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChatUsers]
GO

CREATE TABLE [dbo].[ChatMessages] (
	[MessageID] [int] IDENTITY (1, 1) NOT NULL ,
	[ChatID] [int] NULL ,
	[UserID] [int] NULL ,
	[MessageText] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MessageDateTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChatSessions] (
	[ChatSessionID] [int] IDENTITY (1, 1) NOT NULL ,
	[ChatDescription] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ChatStartDateTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChatUserPerChat] (
	[ChatID] [int] NULL ,
	[UserID] [int] NULL ,
	[SessionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChatUsers] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserPW] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ScreenName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



/****** Object:  Stored Procedure dbo.sp_delUserPerChat    Script Date: 4/7/2003 11:50:29 PM ******/
CREATE PROCEDURE dbo.sp_delUserPerChat

	(
		@chatID int,
		@userID int
		 
	)

AS


	delete from ChatUserPerChat
	where ChatID = @chatID and UserID = @userID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_del_SessionUsers
(
	@SessionID varchar(50)

)

 AS

DELETE FROM	ChatUserPerChat
WHERE		SessionID = @SessionID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getChatDescription
	(
		@ChatID int = 1
	)
AS

	SELECT  	ChatDescription 
	FROM 		ChatSessions 
	WHERE	ChatSessionID = @ChatID

	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getChatMessages
	(
		@ChatID int = 1
	)

AS
	SELECT  	* 
	FROM  		ChatSessions 
	WHERE 	ChatSessionID = @ChatID
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_getChatUser
	(
		@UserID int
	)

AS
	Select 		* 
	FROM 		ChatUsers 
	WHERE 	UserID =   @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_getChatUserByLogin
	(
		@UserName varchar(50),
		@PassWord varchar(50)
		 
	)

	
AS
declare @UserID int


	SELECT	*
	FROM		ChatUsers
	WHERE	UserName = @UserName
	AND		UserPW = @PassWord
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getNewChatMessage

	(
		@ChatID int = 1,
		@MessageID int = 1 
	)

AS
	SELECT 	UserName, FirstName, LastName, ScreenName, MessageText, MessageID 
	FROM 		ChatMessages JOIN ChatUsers on ChatMessages.UserID = ChatUsers.UserID
	WHERE 	ChatMessages.ChatID = @ChatID 
	AND 		MessageID > @MessageID
	ORDER BY 	MessageID  

	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getNewChatMessageNS
(
		@ChatID int = 1,
		@MessageID int = 1 
	)
	
AS
	SELECT	UserName, FirstName, LastName, MessageText, MessageID 
	FROM 		ChatMessages JOIN ChatUsers on ChatMessages.UserID = ChatUsers.UserID
	WHERE 	ChatMessages.ChatID = @ChatID
	AND 		MessageID > @MessageID
	ORDER BY 	MessageID DESC
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_getNewChatMessages

	(
		@ChatID int = 1
	)

AS
	SELECT 	UserName, FirstName, Lastname, MessageText 
	FROM 		ChatMessages JOIN ChatUsers on ChatMessages.UserID = ChatUsers.UserID
	WHERE 	ChatMessages.ChatID = @ChatID
	ORDER BY 	MessageID 

	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getNewChatMessagesNS
(
		@ChatID int = 1,
		@MessageID int = 1 
	)
	
AS
	SELECT	UserName, FirstName, LastName, MessageText, MessageID 
	FROM		ChatMessages JOIN ChatUsers on ChatMessages.UserID = ChatUsers.UserID
	WHERE	ChatMessages.ChatID = @ChatID
	AND 		MessageID > @MessageID
	ORDER BY	MessageID DESC
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_getUserPerChat

	(
		@ChatID int		 
	)

AS

	SELECT 	ChatUsers.UserID, FirstName, LastName, ScreenName  
	FROM		ChatUserPerChat, ChatUsers
	WHERE	chatID = @ChatID AND ChatUserPerChat.UserID = ChatUsers.UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_insChatMessage

	(
		@ChatID int,
		@UserID int,
		@MessageText  varchar(8000)
		 
	)

AS


	INSERT INTO ChatMessages(ChatID, UserID, MessageText)
	VALUES( @ChatID, @UserID,@MessageText)

	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_insChatSession

	(
		@ChatDescription varchar(50)
		 
	)

AS


	INSERT INTO ChatSessions(ChatDescription)
	VALUES( @ChatDescription)

	SELECT @@IDENTITY AS 'Identity'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_insChatUser

	(
		@UserName 	varchar(25),
		@UserPW 	varchar(10),
		@FirstName	varchar(30),
		@LastName	varchar(50),
		@ScreenName varchar(20)

		 
	)

AS
	declare @@ChatUserID int,
		@@ChatScreenNameID int

	--Check to see if the user already exists
	SELECT @@ChatUserID = UserID
	FROM ChatUsers
	WHERE UserName = @UserName and UserPW = @UserPW

	--Check to see if the screen name already exists
	SELECT @@ChatScreenNameID = UserID
	FROM ChatUsers
	WHERE ScreenName = @ScreenName


	IF @@ChatUserID <> null
		BEGIN
			SELECT -1 AS 'UserID'	
		END
	ELSE IF @@ChatScreenNameID <> null
		BEGIN

			SELECT -2 AS  'UserID'
		END
	ELSE

		BEGIN
			INSERT INTO ChatUsers(UserName, UserPW, FirstName, LastName, ScreenName)
			VALUES( @UserName, @UserPW, @FirstName, @LastName, @ScreenName)
			SELECT @@IDENTITY AS 'UserID'
		END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE dbo.sp_insUserPerChat

	(
		@ChatID int,
		@UserID int,
		@SessionID varchar(50) = ''
		 
	)

AS
 

	INSERT INTO ChatUserPerChat(ChatID, UserID, SessionID)
	VALUES( @ChatID, @UserID, @SessionID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

