-- These scripts will not run - just informational only

-- per Karen this should be non-clustered - this should be offline
CREATE NONCLUSTERED INDEX [INX_event_security] ON [dbo].[event_security]
(
	[event_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEXTGEN_INDEX_1]
GO





--Changing primary key to non-clustered - offline
IF  EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_NAME = 'event_audit_mstr' AND CONSTRAINT_NAME = 'PK_event_audit_mstr'
    AND TABLE_SCHEMA ='dbo' )
alter table dbo.event_audit_mstr drop constraint [PK_event_audit_mstr]
GO

-- offline
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_NAME = 'event_audit_mstr' AND CONSTRAINT_NAME = 'PK_event_audit_mstr'
    AND TABLE_SCHEMA ='dbo' )
alter table dbo.event_audit_mstr add constraint [PK_event_audit_mstr] primary key nonclustered (event_id)
GO



-- offline
--New clustered index on event_timestamp
CREATE CLUSTERED INDEX [IX_event_audit_master] ON [dbo].[event_audit_mstr]
(
	[event_timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEXTGEN_INDEX_1]
GO




--This index already exists since 5.7.x.x - needs to be maintained
--IF EXISTS (SELECT * 
--FROM sys.indexes 
--WHERE name='inx_event_audit_mstr_1' AND object_id = OBJECT_ID('event_audit_mstr'))
--DROP INDEX [inx_event_audit_mstr_1] ON [dbo].[event_audit_mstr]
--GO

--CREATE NONCLUSTERED INDEX [inx_event_audit_mstr_1] ON [dbo].[event_audit_mstr]
--(
--	[event_timestamp] ASC,
--	[enterprise_id] ASC,
--	[practice_id] ASC,
--	[created_by] ASC,
--	[action_id] ASC
--)
--INCLUDE ( 	[event_id],
--	[category_id],
--	[station_id],
--	[source_product_description],
--	[person_id],
--	[event_message]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEXTGEN_INDEX_1]
--GO



--This should already be there since 5.7.x.x - needs to be maintained
--IF EXISTS (SELECT * 
--FROM sys.indexes 
--WHERE name='inx_event_data_trail_1' AND object_id = OBJECT_ID('event_data_trail'))
--DROP INDEX [inx_event_data_trail_1]   ON [dbo].[event_data_trail];
--GO

-- should only be on event id
--CREATE NONCLUSTERED INDEX [inx_event_data_trail_1] ON [dbo].[event_data_trail]
--(
--	[event_id] ASC
--)
--GO


--{5.7.2.108}  
----------------------  
-- $Change: xxxxx $  
-- $DateTime: 2012/08/20 23:42:00 $  
-- $Author: bdowling $  
----------------------  

IF EXISTS (SELECT si.NAME FROM sys.indexes si JOIN sys.tables st on si.object_id = st.object_id
WHERE si.name = 'inx_event_audit_mstr_1' AND st.name = 'event_audit_mstr')  
      BEGIN
            DROP INDEX event_audit_mstr.inx_event_audit_mstr_1;
      END

CREATE NONCLUSTERED INDEX inx_event_audit_mstr_1 ON event_audit_mstr 
(enterprise_id, practice_id, created_by, action_id, event_timestamp)
INCLUDE (event_id, category_id, station_id, source_product_description, person_id, event_message)


--{5.7.2.108}  
----------------------  
-- $Change: xxxxx $  
-- $DateTime: 2012/08/20 23:42:00 $  
-- $Author: bdowling $  
----------------------  

IF EXISTS (SELECT si.NAME FROM sys.indexes si JOIN sys.tables st on si.object_id = st.object_id
WHERE si.name = 'inx_event_data_trail_1' AND st.name = 'event_data_trail')  
      BEGIN
            DROP INDEX event_data_trail.inx_event_data_trail_1;
      END

CREATE NONCLUSTERED INDEX inx_event_data_trail_1 ON event_data_trail 
(event_id)
INCLUDE (location, field, old_value, new_value, description)








-- if all succeeds insert version into version table
INSERT INTO [dbo].[version]
        ([product_id]
        ,[version]
        ,[description]
        ,[delete_ind]
        ,[created_by]
        ,[modified_by]
        ,[create_timestamp]
        ,[modify_timestamp])
    VALUES
        ('NGAUDIT'
        ,'5.8.3.100'
        ,'NextGen Advanced Auditing'
        ,'N'
        ,0
        ,0
        ,GETDATE()
        ,GETDATE())
GO