if exists (select * from sysdatabases where name='ClickDB')
begin
  raiserror('Dropping existing database ....',0,1)
  DROP database ClickDB
end
GO

CHECKPOINT
GO

raiserror('Creating database....',0,1)
GO

CREATE DATABASE ClickDB
GO

USE ClickDB

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[Member] (
	[userid] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[password] [varchar] (50) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[name] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[email] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[joindate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Message] (
	[msgid] [int] IDENTITY (1, 1) NOT NULL ,
	[fromUser] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[toUser] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[content] [varchar] (200) COLLATE Korean_Wansung_CI_AS NULL ,
	[status] [int] NULL ,
	[sdate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[uid] [varchar] (50) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[pwd] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[email] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[board] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[body] [varchar] (200) COLLATE Korean_Wansung_CI_AS NULL ,
	[dtime] [char] (10) COLLATE Korean_Wansung_CI_AS NULL ,
	[name] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[hit] [int] NULL ,
	[gr_id] [int] NULL ,
	[gr_depth] [int] NULL ,
	[gr_pos] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[board1] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[body] [varchar] (200) COLLATE Korean_Wansung_CI_AS NULL ,
	[dtime] [char] (10) COLLATE Korean_Wansung_CI_AS NULL ,
	[name] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[hit] [int] NULL ,
	[gr_id] [int] NULL ,
	[gr_depth] [int] NULL ,
	[gr_pos] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[board2] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[body] [varchar] (200) COLLATE Korean_Wansung_CI_AS NULL ,
	[dtime] [char] (10) COLLATE Korean_Wansung_CI_AS NULL ,
	[name] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[hit] [int] NULL ,
	[gr_id] [int] NULL ,
	[gr_depth] [int] NULL ,
	[gr_pos] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[fboard] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[body] [varchar] (200) COLLATE Korean_Wansung_CI_AS NULL ,
	[dtime] [char] (10) COLLATE Korean_Wansung_CI_AS NULL ,
	[name] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[hit] [int] NULL ,
	[gr_id] [int] NULL ,
	[gr_depth] [int] NULL ,
	[gr_pos] [int] NULL ,
	[fname] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
	[fsize] [int] NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_AddMember
       @userid varchar(20),
       @password varchar(50),
       @name varchar(20),
       @email varchar(20)
AS
BEGIN
     INSERT INTO Member
     ( 
	userid, password, name, email 
     ) 
     VALUES 
     ( 
	@userid,@password,@name,@email
     ) 
    SELECT userid FROM Member WHERE userid = @userid
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetEmail
       @userid varchar(20),
       @email varchar(20) OUTPUT
AS
BEGIN
    SELECT
        @email = email
    FROM  
        Member
    WHERE
        userid = @userid          
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure sp_GetNewMsgCount
(
     @ToUser varchar(20)
)
AS

SELECT count(*) FROM Message
WHERE status= 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure sp_RegisterUser
(
    @Userid varchar(20),
    @Password varchar(50),
    @Name varchar(20),
    @Email varchar(20)
)
AS

INSERT INTO Member
(
    userid,  password, name, email, joindate
)
VALUES
(  
    @Userid,  @Password,   @Name,   @Email,   getdate()
)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure sp_SendMessage
(
    @FromUser varchar(20),
    @ToUser varchar(20),
    @Content varchar(200),
    @MsgID int OUTPUT
)
AS

INSERT INTO Message
(
    FromUser, ToUser, Content,  SDate, Status
)

VALUES
(
    @FromUser,   @ToUser,  @Content,   getdate(),   0
)
SELECT
    @MsgID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure sp_UserLogin
(
    @Userid        varchar(20),
    @Password   varchar(50),
    @Name         varchar(20) OUTPUT
)
AS

SELECT 
    @Name = name

FROM 
    Member
    
WHERE 
    userid = @Userid
  AND 
    Password = @Password

IF @@Rowcount < 1 
SELECT 
    @Name = ' '
GO
CHECKPOINT

GO

raiserror('Done....',0,1)

GO