USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TaskVision')
BEGIN
DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
	SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'TaskVision' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
	SET @sql = 'KILL ' + CAST(@spid AS varchar)
	EXEC(@sql)
	FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [TaskVision]
END
GO

CREATE DATABASE [TaskVision]
GO


USE [TaskVision]

CREATE TABLE [Priorities] (
	[PriorityID] int IDENTITY (1, 1) NOT NULL,
	[PriorityText] varchar(50) NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Projects] (
	[ProjectID] int IDENTITY (1, 1) NOT NULL,
	[ProjectName] varchar(20) NOT NULL,
	[ProjectDescription] varchar(100) NOT NULL,
	[IsDeleted] bit NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Statuses] (
	[StatusID] int IDENTITY (1, 1) NOT NULL,
	[StatusText] varchar(50) NOT NULL 
) ON [PRIMARY]

CREATE TABLE [TaskHistory] (
	[TaskHistoryID] int IDENTITY (1, 1) NOT NULL,
	[TaskID] int NULL,
	[ProjectID] int NULL,
	[ModifiedBy] int NULL,
	[AssignedTo] int NULL,
	[TaskSummary] varchar(70) NULL,
	[TaskDescription] varchar(500) NULL,
	[PriorityID] int NULL,
	[StatusID] int NULL,
	[Progress] int NULL,
	[IsDeleted] bit NULL,
	[DateDue] datetime NULL,
	[DateModified] datetime NULL,
	[DateCreated] datetime NULL 
) ON [PRIMARY]

CREATE TABLE [Tasks] (
	[TaskID] int IDENTITY (1, 1) NOT NULL,
	[ProjectID] int NOT NULL,
	[ModifiedBy] int NOT NULL,
	[AssignedTo] int NOT NULL,
	[TaskSummary] varchar(70) NOT NULL,
	[TaskDescription] varchar(500) NOT NULL,
	[PriorityID] int NOT NULL,
	[StatusID] int NOT NULL,
	[Progress] int NOT NULL,
	[IsDeleted] bit NOT NULL,
	[DateDue] datetime NOT NULL,
	[DateModified] datetime NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]

CREATE TABLE [Users] (
	[UserID] int IDENTITY (1, 1) NOT NULL,
	[UserName] varchar(16) NOT NULL,
	[UserPassword] varchar(16) NOT NULL,
	[UserFullName] varchar(50) NOT NULL,
	[UserEmail] varchar(50) NOT NULL,
	[IsAccountLocked] bit NOT NULL,
	[IsAdministrator] bit NOT NULL,
	[DateCreated] datetime NOT NULL 
) ON [PRIMARY]


declare @hostName as varchar(25);
set @hostname = HOST_NAME() + '\ASPNET'

if not exists (select * from master.dbo.syslogins where loginname = @hostname)
	exec sp_grantlogin @hostname

if not exists (select * from dbo.sysusers where name = N'ASPNET' and uid < 16382)
	EXEC sp_grantdbaccess @hostname, N'ASPNET'


ALTER TABLE [Priorities] WITH NOCHECK ADD 
	CONSTRAINT [PK_Priorities] PRIMARY KEY  CLUSTERED 
	(
		[PriorityID]
	)  ON [PRIMARY]

ALTER TABLE [Projects] WITH NOCHECK ADD 
	CONSTRAINT [DF_Projects_IsDeleted] DEFAULT (0) FOR [IsDeleted],
	CONSTRAINT [DF_Projects_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_Projects] PRIMARY KEY  CLUSTERED 
	(
		[ProjectID]
	)  ON [PRIMARY] 

ALTER TABLE [Statuses] WITH NOCHECK ADD 
	CONSTRAINT [PK_Statuses] PRIMARY KEY  CLUSTERED 
	(
		[StatusID]
	)  ON [PRIMARY]

ALTER TABLE [TaskHistory] WITH NOCHECK ADD 
	CONSTRAINT [PK_TaskHistory] PRIMARY KEY  CLUSTERED 
	(
		[TaskHistoryID]
	)  ON [PRIMARY]

ALTER TABLE [Tasks] WITH NOCHECK ADD 
	CONSTRAINT [DF_Tasks_PercentComplete] DEFAULT (0) FOR [Progress],
	CONSTRAINT [DF_Tasks_Deleted] DEFAULT (0) FOR [IsDeleted],
	CONSTRAINT [DF_ChangeNotes_DateModified] DEFAULT (getdate()) FOR [DateModified],
	CONSTRAINT [DF_ChangeNotes_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_ChangeNotes] PRIMARY KEY  CLUSTERED 
	(
		[TaskID]
	)  ON [PRIMARY]

ALTER TABLE [Users] WITH NOCHECK ADD 
	CONSTRAINT [DF_Users_Disabled] DEFAULT (0) FOR [IsAccountLocked],
	CONSTRAINT [DF_Users_Administrator] DEFAULT (0) FOR [IsAdministrator],
	CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY]

ALTER TABLE [TaskHistory] ADD 
	CONSTRAINT [FK_TaskHistory_Tasks] FOREIGN KEY 
	(
		[TaskID]
	) REFERENCES [dbo].[Tasks] (
		[TaskID]
	) ON DELETE CASCADE 

ALTER TABLE [Tasks] ADD 
	CONSTRAINT [FK_ChangeNotes_Priorities] FOREIGN KEY 
	(
		[PriorityID]
	) REFERENCES [dbo].[Priorities] (
		[PriorityID]
	),
	CONSTRAINT [FK_ChangeNotes_Statuses] FOREIGN KEY 
	(
		[StatusID]
	) REFERENCES [dbo].[Statuses] (
		[StatusID]
	),
	CONSTRAINT [FK_TaskChanges_Projects] FOREIGN KEY 
	(
		[ProjectID]
	) REFERENCES [dbo].[Projects] (
		[ProjectID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_Tasks_Users_AssignedTo] FOREIGN KEY 
	(
		[AssignedTo]
	) REFERENCES [dbo].[Users] (
		[UserID]
	),
	CONSTRAINT [FK_Tasks_Users_ModifiedBy] FOREIGN KEY 
	(
		[ModifiedBy]
	) REFERENCES [dbo].[Users] (
		[UserID]
	)
	
GO



CREATE PROCEDURE [AuthenticateUser]
(
@UserName varchar(16),
@Password varchar(16)
)
AS  
SELECT  UserID
FROM    Users
WHERE   (UserName = @UserName AND cast(UserPassword as varbinary) = cast(@Password as varbinary) AND IsAccountLocked = 0)
GO
GRANT  EXECUTE  ON [AuthenticateUser]  TO [ASPNET];
GO


CREATE PROCEDURE [DeleteUser]
(
@UserID int
)
AS
DELETE Users WHERE UserID = @UserID
GO
GRANT  EXECUTE  ON [DeleteUser]  TO [ASPNET];
GO


CREATE PROCEDURE [GetOneTask]
(
@TaskID integer
)
AS
SELECT Tasks.TaskID, Tasks.ProjectID, Tasks.ModifiedBy, Tasks.AssignedTo, Tasks.TaskSummary, Tasks.TaskDescription, Tasks.PriorityID, Tasks.StatusID, Tasks.Progress, Tasks.IsDeleted, Tasks.DateDue, Tasks.DateModified, Tasks.DateCreated, Priorities.PriorityText, Statuses.StatusText, Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText 
FROM Tasks 
JOIN Priorities ON Tasks.PriorityID = Priorities.PriorityID 
JOIN Users ON Tasks.AssignedTo = Users.UserID 
JOIN Users u ON Tasks.ModifiedBy = u.UserID 
JOIN Statuses ON Tasks.StatusID = Statuses.StatusID 
WHERE Tasks.TaskID = @TaskID And Tasks.IsDeleted = 0
GO
GRANT  EXECUTE  ON [GetOneTask]  TO [ASPNET];
GO


CREATE PROCEDURE [GetPriorities] 
AS
SET NOCOUNT ON;
SELECT PriorityID, PriorityText FROM Priorities
GO