--Creating the database
USE Master
GO
CREATE DATABASE SalesLeads
GO
USE SalesLeads
GO

--Creating the table
CREATE TABLE dbo.Leads
(
OrganizationID 		int IDENTITY PRIMARY KEY NONCLUSTERED,
OrganizationName	nvarchar(50) NOT NULL,
DateDiscovered		datetime NOT NULL,
ContactPerson		nvarchar(25) NOT NULL,
ContactEmail		nvarchar(50) NOT NULL,
ContactPhone		nvarchar(20) NOT NULL,
Comments		nvarchar(1000) NULL,
DateLastContacted	datetime NULL,
LeadGeneratedBy		varchar(30) NOT NULL
)
GO

--Creating the required indexes
CREATE CLUSTERED INDEX Leads_LeadGenerator_NUCI ON dbo.Leads(LeadGeneratedBy)
GO

--Creating the view
CREATE VIEW dbo.MyLeads
AS
SELECT 
OrganizationID, OrganizationName, DateDiscovered, ContactPerson, 
ContactEmail, ContactPhone, Comments, DateLastContacted, LeadGeneratedBy
FROM dbo.Leads
WHERE LeadGeneratedBy = SUSER_SNAME()
WITH CHECK OPTION
GO

--Creating stored procedures
CREATE PROC dbo.ShowMyLeads
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
	OrganizationID, OrganizationName, DateDiscovered, ContactPerson, 
	ContactEmail, ContactPhone, Comments, DateLastContacted, LeadGeneratedBy 
	FROM dbo.MyLeads

	IF @@ERROR = 0
		RETURN 0
	ELSE
		RETURN -1
	
END
GO
CREATE PROC dbo.InsertNewLead
(
	@OrganizationID		int OUT,
	@OrganizationName	nvarchar(50),
	@DateDiscovered		datetime = NULL,
	@ContactPerson		nvarchar(25),
	@ContactEmail		nvarchar(50),
	@ContactPhone		nvarchar(20),
	@Comments		nvarchar(1000),
	@DateLastContacted	datetime = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @err int

	INSERT INTO dbo.MyLeads
	(
		OrganizationName, DateDiscovered, ContactPerson, ContactEmail,
		ContactPhone, Comments, DateLastContacted, LeadGeneratedBy
	)
	VALUES
	(
		@OrganizationName, COALESCE(@DateDiscovered, CURRENT_TIMESTAMP), @ContactPerson, @ContactEmail,
		@ContactPhone, @Comments, COALESCE(@DateLastContacted, CURRENT_TIMESTAMP), SUSER_SNAME()
	)
	
	SELECT @OrganizationID = @@IDENTITY, @err = @@ERROR

	IF @Err = 0 
		RETURN 0
	ELSE
		RETURN -1
END
GO
CREATE PROC dbo.UpdateLead
(
	@OrganizationID		int,
	@OrganizationName	nvarchar(50),
	@DateDiscovered		datetime = NULL,
	@ContactPerson		nvarchar(25),
	@ContactEmail		nvarchar(50),
	@ContactPhone		nvarchar(20),
	@Comments		nvarchar(1000),
	@DateLastContacted	datetime = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	UPDATE dbo.MyLeads
	SET
		OrganizationName = @OrganizationName,	
		DateDiscovered = COALESCE(@DateDiscovered, CURRENT_TIMESTAMP),
		ContactPerson = @ContactPerson,
		ContactEmail = @ContactEmail,
		ContactPhone = @ContactPhone,
		Comments = @Comments,
		DateLastContacted = COALESCE(@DateLastContacted, CURRENT_TIMESTAMP)
	WHERE
		OrganizationID = @OrganizationID

	IF @@ERROR = 0 AND @@ROWCOUNT = 1
	BEGIN
		PRINT 'Lead Updated'
		RETURN 0
	END
	ELSE
	BEGIN
		PRINT 'Lead not found or error occured'
		RETURN -1
	END
END
GO
CREATE PROC DeleteLead
(
	@OrganizationID int
)
AS
BEGIN
	SET NOCOUNT ON

	DELETE 	dbo.MyLeads
	WHERE	OrganizationID = @OrganizationID

	
	IF @@ERROR = 0 AND @@ROWCOUNT = 1
	BEGIN
		PRINT 'Lead Deleted'
		RETURN 0
	END
	ELSE
	BEGIN
		PRINT 'Lead not found or error occured'
		RETURN -1
	END
END
GO

--Denying permissions on tables and views to public role
DENY ALL on Leads TO public
GO
DENY ALL on MyLeads TO public
GO

--Creating a database role and assigning EXECUTE permissions to it on stored procedures
sp_addrole 'SalesReps'
GO

GRANT EXEC ON ShowMyLeads TO SalesReps
GRANT EXEC ON InsertNewLead TO SalesReps
GRANT EXEC ON UpdateLead TO SalesReps
GRANT EXEC ON DeleteLead TO SalesReps
GO

--Creating logins and granting them access to database
EXEC sp_addlogin @loginame = 'SalesRep1', @passwd = 'pwd1', @defdb = 'SalesLeads'
EXEC sp_addlogin @loginame = 'SalesRep2', @passwd = 'pwd2', @defdb = 'SalesLeads'
EXEC sp_addlogin @loginame = 'SalesRep3', @passwd = 'pwd3', @defdb = 'SalesLeads'
GO
EXEC sp_grantdbaccess 'SalesRep1'
EXEC sp_grantdbaccess 'SalesRep2'
EXEC sp_grantdbaccess 'SalesRep3'
GO

--Adding database users to the database role
EXEC sp_addrolemember 'SalesReps', 'SalesRep1'
EXEC sp_addrolemember 'SalesReps', 'SalesRep2'
EXEC sp_addrolemember 'SalesReps', 'SalesRep3'
GO