SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ***********************
-- **   CREATE TABLES   **
-- ***********************

-- CREATE THE ENUMERATIONS TABLE
CREATE TABLE [dbo].[Enumerations](
	[id] [int] NOT NULL IDENTITY,
	[type] [nvarchar](50) NOT NULL,
    [value] [nvarchar](100) NOT NULL
 CONSTRAINT [PK_Enumerations] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX idx_EnumerationType ON dbo.Enumerations ([type])
GO

-- CREATE NOTIFICATIONS TABLE
CREATE TABLE [dbo].[Notifications](
	[id] [uniqueidentifier] NOT NULL,
    [correlationId] [uniqueidentifier] NULL,
	[userId] [uniqueidentifier] NOT NULL,
    [companyId] [uniqueidentifier] NOT NULL,
	[deliverytype] [int] NOT NULL,
    [priority] [int] NOT NULL,
    [subject] [nvarchar](200) NULL,
    [message] [nvarchar](4000) NOT NULL,
    [source] [nvarchar] (1000) NOT NULL,
    [email] [nvarchar](1000) NULL,
    [phone] [nvarchar](50) NULL,
    [postedTime] [datetime] NOT NULL,
    [sentTime] [datetime] NULL,
    [deliveredTime] [datetime] NULL,
    [readTime] [datetime] NULL,
    [deletedTime] [datetime] NULL,
    [status] [int] NOT NULL,
    [smsFK] [nvarchar](1000) NULL,
    [emailFK] [nvarchar](1000) NULL
 CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Notifications] ADD  CONSTRAINT [DF_Notifications_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations] FOREIGN KEY([status])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations2] FOREIGN KEY([priority])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations3] FOREIGN KEY([deliverytype])
REFERENCES [dbo].[Enumerations] ([id])
GO
CREATE INDEX idx_NotificationsUserId ON dbo.Notifications ([userId])
GO
CREATE INDEX idx_NotificationsCompanyId ON dbo.Notifications ([companyId])
GO
CREATE INDEX idx_NotificationsByCorrelationId ON dbo.Notifications ([correlationId])
GO
CREATE INDEX idx_NotificationsBySmsFK ON dbo.Notifications ([smsFK])
GO
CREATE INDEX idx_NotificationsByEmailFK ON dbo.Notifications ([emailFK])
GO
-- CREATE THE DELETED NOTIFICATIONS TABLE
CREATE TABLE [dbo].[DeletedNotifications](
	[id] [uniqueidentifier] NOT NULL,
    [correlationId] [uniqueidentifier] NULL,
	[userId] [uniqueidentifier] NOT NULL,
    [companyId] [uniqueidentifier] NOT NULL,
	[deliverytype] [int] NOT NULL,
    [priority] [int] NOT NULL,
    [subject] [nvarchar](200) NULL,
    [message] [nvarchar](4000) NOT NULL,
    [source] [nvarchar] (1000) NOT NULL,
    [email] [nvarchar](1000) NULL,
    [phone] [nvarchar](50) NULL,
    [postedTime] [datetime] NOT NULL,
    [sentTime] [datetime] NULL,
    [deliveredTime] [datetime] NULL,
    [readTime] [datetime] NULL,
    [deletedTime] [datetime] NULL,
    [status] [int] NOT NULL,
    [smsFK] [nvarchar](1000) NULL,
    [emailFK] [nvarchar](1000) NULL
 CONSTRAINT [PK_DeletedNotifications] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations] FOREIGN KEY([status])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations2] FOREIGN KEY([priority])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations3] FOREIGN KEY([deliverytype])
REFERENCES [dbo].[Enumerations] ([id])
GO
CREATE INDEX idx_DeletedNotificationsUserId ON dbo.DeletedNotifications ([userId])
GO
CREATE INDEX idx_DeletedNotificationsCompanyId ON dbo.DeletedNotifications ([companyId])
GO
CREATE INDEX idx_DeletedNotificationsCorrelationId ON dbo.DeletedNotifications ([correlationId])
GO
CREATE INDEX idx_DeletedNotificationsBySmsFK ON dbo.DeletedNotifications ([smsFK])
GO
CREATE INDEX idx_DeletedNotificationsByEmailFK ON dbo.DeletedNotifications ([emailFK])
GO

-- ***********************
-- ** CREATE PROCEDURES **
-- ***********************

-- GET ENUMERATIONS BY TYPE
CREATE PROCEDURE usp_getEnum_ByType
(
    @type nvarchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        *
    FROM [dbo].[Enumerations]
    WHERE [type] = @type
    ORDER BY id
END
GO

-- GET ALL MESSAGES WITH a SPECIFIC NotificationID
CREATE PROCEDURE usp_getNotifications_ById
(
    @limit INT = 10,
    @skip INT = 0,
    @id UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE id = @id 
        UNION 
        SELECT * FROM DeletedNotifications WHERE id = @id
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [id] = @id)
        +
        (SELECT COUNT(*) as totalcount2 
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [id] = @id)
	RETURN @TOTALROWS
END 
GO

-- GET ALL ACTIVE MESSAGES FOR A USER
CREATE PROCEDURE usp_getNotifications_Active_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT
        *
    FROM [dbo].[Notifications]
    WHERE [userId] = @userId
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES WITH a SPECIFIC CORRELATIONID
CREATE PROCEDURE usp_getNotifications_ByCorrelationId
(
    @limit INT = 10,
    @skip INT = 0,
    @correlationId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE correlationId = @correlationId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE correlationId = @correlationId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [correlationId] = @correlationId)
        +
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [correlationId] = @correlationId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES WITH a SPECIFIC COMPANYID
CREATE PROCEDURE usp_getNotifications_ByCompanyId
(
    @limit INT = 10,
    @skip INT = 0,
    @companyId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE companyId = @companyId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE companyId = @companyId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [companyId] = @companyId)
        + 
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [companyId] = @companyId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES FOR A USER (BOTH ACTIVE AND DELETED)
CREATE PROCEDURE usp_getNotifications_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE userId = @userId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE userId = @userId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
        + 
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- GET ONLY DELETED MESSAGES FOR A USER
CREATE PROCEDURE usp_getNotifications_Deleted_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM DeletedNotifications WHERE userId = @userId
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- INSERT/ADD A NOTIFICATION
CREATE PROCEDURE [dbo].[usp_Notifications_Insert]
(
    @correlationId UNIQUEIDENTIFIER,
    @userId UNIQUEIDENTIFIER,
    @email NVARCHAR(1000),
    @phone NVARCHAR(50),
    @companyId UNIQUEIDENTIFIER,
    @priority INT,
    @deliverytype INT,
    @subject NVARCHAR(200),
    @message NVARCHAR(4000),
    @source NVARCHAR(1000),
    @postedTime DATETIME,
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @out_id UNIQUEIDENTIFIER
	SET @out_id = NEWID()
    INSERT INTO [dbo].[Notifications]
    (
		[id],
        [correlationId],
        [userId],
        [email],
        [phone],
        [companyId],
        [deliverytype],
        [priority],
        [subject],
        [message],
        [source],
        [postedTime],
        [status]
    )
    VALUES
    (
        @out_id,
        @correlationId,
		@userId,
        @email,
        @phone,
        @companyId,
        @deliverytype,
        @priority,
        @subject,
        @message,
        @source,
        @postedTime,
        @status
    )
	SELECT @out_id as id
END 
GO

-- UPDATE AN EXISTING NOTIFICATION - INTERNAL USER BY SNEDING PROVIDER
CREATE PROCEDURE [dbo].[usp_Notifications_UpdateFK]
(
    @id UNIQUEIDENTIFIER,
    @smsFK nvarchar (1000),
    @emailFK nvarchar (1000)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[Notifications]
    SET
        [smsFK] = @smsFK,
        [emailFK] = @emailFK
    WHERE
    (
        ([id] = @id) 
    )
END 
GO
-- UPDATE AN EXISTING NOTIFICATION - ONLY TIMESTAMPS ALLOWED
CREATE PROCEDURE [dbo].[usp_Notifications_Update]
(
    @id UNIQUEIDENTIFIER,
    @sentTime DATETIME,
    @deliveredTime DATETIME,
    @readTime DATETIME,
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[Notifications]
    SET
        [sentTime] = @sentTime,
        [deliveredTime] = @deliveredTime,
        [readTime] = @readTime,
        [status] = @status
    WHERE
    (
        ([id] = @id) 
    )
END 
GO
-- UPDATE AN EXISTING NOTIFICATION STATUS BY FK - 
CREATE PROCEDURE [dbo].[usp_Notifications_UpdateByFK]
(
    @fk NVARCHAR(1000),
    @timestamp DATETIME,
    @type NVARCHAR(20),
    @source NVARCHAR(20),
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @statement NVARCHAR(4000);
    DECLARE @paramDefinition NVARCHAR(4000)
    DECLARE @col NVARCHAR(20);
    DECLARE @keyCol NVARCHAR(20);
    SELECT @col = 
        CASE @type
            WHEN 'sent' THEN 'sentTime'
            WHEN 'delivered' THEN 'deliveredTime'
            WHEN 'read' THEN 'readTime'
            WHEN 'deleted' THEN 'deletedTime'
            ELSE 'invalid'
        END
    SELECT @keyCol = 
        CASE @source
            WHEN 'email' THEN 'emailFK'
            WHEN 'sms' THEN 'smsFK'
            ELSE 'invalid'
        END
    SET @statement = 'UPDATE [dbo].[Notifications] SET [' + @col + ']=''' + CAST( @timestamp AS NVARCHAR )+ ''', [status]='+ CAST(@status AS NVARCHAR) + ' WHERE ( [' + @keyCol + ']=''' + @fk + ''')'
    EXECUTE sp_executesql @statement
END 
GO
CREATE PROCEDURE [dbo].[usp_Notifications_Delete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;
    -- update the original entry to set deleted flag
    UPDATE [dbo].[Notifications]
    SET
        [deletedTime] = getutcdate()
    WHERE
    (
        ([id] = @id) 
    )
    -- create row in DeletedNotifications
    INSERT INTO [dbo].[DeletedNotifications]
    SELECT * FROM [dbo].[Notifications] WHERE [id] = @id
    -- remove original row
    DELETE FROM [dbo].[Notifications]
    WHERE [id] = @id
END 
GO
CREATE PROCEDURE [dbo].[usp_Notifications_DeleteByCorrelation]
(
    @correlationId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;
    -- update the original entry to set deleted flag
    UPDATE [dbo].[Notifications]
    SET
        [deletedTime] = getutcdate()
    WHERE
    (
        ([correlationId] = @correlationId) 
    )
    -- create row in DeletedNotifications
    INSERT INTO [dbo].[DeletedNotifications]
    SELECT * FROM [dbo].[Notifications] WHERE [correlationId] = @correlationId
    -- remove original row
    DELETE FROM [dbo].[Notifications]
    WHERE [correlationId] = @correlationId
END 
GO
-- ***************************************
-- **   SET ENUMERATIONS TABLE VALUES   **
-- ***************************************
SET IDENTITY_INSERT dbo.Enumerations ON;  
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (1, 'PRIORITY', 'INFO')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (2, 'PRIORITY', 'WARNING')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (3, 'PRIORITY', 'ALERT')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (4, 'PRIORITY', 'CRITICAL')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (5, 'DELIVERYTYPE', 'SMS')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (6, 'DELIVERYTYPE', 'EMAIL')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (7, 'DELIVERYTYPE', 'NONE')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (8, 'STATUS', 'UNPROCESSED')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (9, 'STATUS', 'PROCESSING')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (10, 'STATUS', 'SENT')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (11, 'STATUS', 'DELIVERED')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (12, 'STATUS', 'READ')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (13, 'STATUS', 'DELETED')
GO
SET IDENTITY_INSERT dbo.Enumerations OFF;  
GO

-- ****************************************
-- **   SET NOTIFICATIONS TABLE VALUES   **
-- ****************************************
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('8fc9bacb-65f3-4178-807e-7312a583a695', 'd9e30bfa-4377-4e07-aaf5-6a7a60db06ce', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject', 'message', 'SC.Test', null, null, '2020-10-01 06:00:00.000', null, null, null, null, 9)
GO
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('1ff3c44f-5f6d-4f9c-9045-8188d56a12f8', 'fbf9c5a0-a69f-4226-bf43-1a657624540c', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject2', 'message2', 'SC.Test', null, null, '2020-10-01 06:01:00.000', '2020-10-01 06:01:00.000', null, null, null, 10)
GO
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('c113bfd3-48bc-413f-82d3-88d3f243080b', '267f4117-1800-47de-90d2-8e93c2cf1f3e', '2a839f9e-834b-4d50-af09-a3a97757ba68', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject3', 'message3', 'SC.Test', null, null, '2020-10-01 06:05:00.000', null, null, null, null, 9)
GO
INSERT dbo.DeletedNotifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('9fc9bacb-65f3-4178-807e-7312a583a695', 'd9e30bfa-4377-4e07-aaf5-6a7a60db06ce', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subjectD', 'messageD', 'SC.Test', null, null, '2020-10-01 05:00:00.000', '2020-10-01 05:01:00.000', '2020-10-01 05:02:00.000', '2020-10-01 05:03:00.000', '2020-10-01 05:04:00.000', 13)
GO
