DROP TABLE MonitoringEventLogs;
GO
DROP TABLE MonitoringUlsEvents;
GO
DROP TABLE MonitoringServers;
GO
ALTER TABLE Principals ADD ExternalUser [bit] NOT NULL DEFAULT(0)
GO
ALTER TABLE Principals ADD [TenantID] [int] NULL
GO
ALTER TABLE WebApplications ADD Url [nvarchar](255) NULL
GO
ALTER TABLE WebApplications ADD Username [nvarchar](255) NULL
GO
ALTER TABLE WebApplications ADD	[Password] [nvarchar](1024) NULL
GO
ALTER TABLE WebApplications ADD	[Hash] [nvarchar](255) NULL
GO
ALTER TABLE WebApplications ADD [BusinessPlanID] [int] NULL
GO
ALTER TABLE SharePointObjects ADD [ContentTypeID] [int] NULL
GO
ALTER TABLE SharePointObjects ADD [LastUpdated] [datetime] NULL
GO
ALTER TABLE SharePointObjects ADD [CreatedOn] [datetime] NULL
GO
ALTER TABLE SharePointObjects ADD [CreatedBy] [int] NULL
GO
ALTER TABLE SharePointObjects ADD [ModifiedBy] [int] NULL
GO

UPDATE child SET [WebAppID] = parent.WebAppID FROM [dbo].[SharePointObjects] child
INNER JOIN [dbo].[SharePointObjects] parent on child.SPSiteGuid = parent.SPGuid
WHERE child.WebAppID=0 Or child.WebAppID=-1
GO

CREATE TABLE [dbo].[SystemJobs](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Enabled] [bit] NOT NULL,
	[JobStepID] [int] NOT NULL,
	[ForceStart] [bit] NOT NULL,
	[StartArgument] [nvarchar](max) NOT NULL,
	[MaintenancePeriodID] [int] NOT NULL,
	[MaintenancePeriodValue] [int] NOT NULL,
	[MaintenanceStartTime] [datetime] NOT NULL,
	[LastOperationTime] [datetime] NOT NULL,
	[OperationInProgressOn] [nvarchar](255) NOT NULL,
	[OperationStartedOn] [datetime] NOT NULL,
	[LastSeenAlive] [datetime] NOT NULL,
	[StatisticsXml] [xml] NULL,
 CONSTRAINT [PK_SystemJobs] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[SystemJobWorkItems](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[JobID] [int] NOT NULL,
	[WorkToDo] [nvarchar](max) NOT NULL,
	[StateID] [int] NOT NULL,
	[LastSeenAlive] [datetime] NOT NULL,
 CONSTRAINT [PK_SystemJobWorkItems] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[JobMaintenancePeriods](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_JobMaintenancePeriod] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[JobSteps](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_JobSteps] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[JobWorkItemStates](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_JobWorkItemStates] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SystemJobsLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[SystemJob] [nvarchar](255) NOT NULL,
	[Status] [nvarchar](255) NOT NULL,
	[Message] [nvarchar](max) NOT NULL,
	[CreatedOn] [datetime] NOT NULL,	
	[MemoryUsage] [float] NULL,
 CONSTRAINT [PK_SystemJobsLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


CREATE TABLE [dbo].[AlertThrottlingIntervalTypes](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_AlertThrottlingIntervalTypes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[AlertRollingWindowIntervalTypes](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_AlertRollingWindowIntervalTypes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SavedSearches](
	[SavedSearchID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[SearchText] [nvarchar](max) NOT NULL,
	[IsAlert] [bit] NOT NULL,
	[AlertLastExecuted] [datetime] NOT NULL,
	[ThrottlingEnabled] [bit] NOT NULL,
	[ThrottlingInterval] [int] NOT NULL,
	[ThrotlingIntervalTypeID] [int] NOT NULL,
	[AlertCondition] [nvarchar](max) NULL,
	[EmailEnabled] [bit] NOT NULL,
	[EmailSubject] [nvarchar](500) NULL,
	[EmailBody] [nvarchar](max) NULL,
	[EmailReceivers] [nvarchar](1000) NULL,
	[ScriptEnabled] [bit] NOT NULL,
	[ScriptPath] [nvarchar](1000) NULL,
	[RollingWindowIntervalTypeID] [int] NOT NULL,
	[RollingWindowValue] [int] NOT NULL,
 CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED 
(
	[SavedSearchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


ALTER TABLE [dbo].[SavedSearches]  WITH CHECK ADD  CONSTRAINT [FK_SavedSearches_AlertThrottlingIntervalTypes] FOREIGN KEY([ThrotlingIntervalTypeID])
REFERENCES [dbo].[AlertThrottlingIntervalTypes] ([ID])
GO
ALTER TABLE [dbo].[SavedSearches] CHECK CONSTRAINT [FK_SavedSearches_AlertThrottlingIntervalTypes]
GO
ALTER TABLE [dbo].[SavedSearches] ADD  CONSTRAINT [DF_SavedSearches_RollingWindowIntervalTypeID]  DEFAULT ((1)) FOR [RollingWindowIntervalTypeID]
GO
ALTER TABLE [dbo].[SavedSearches] ADD  CONSTRAINT [DF_SavedSearches_RollingWindowValue]  DEFAULT ((0)) FOR [RollingWindowValue]
GO
ALTER TABLE [dbo].[SavedSearches]  WITH CHECK ADD  CONSTRAINT [FK_SavedSearches_AlertRollingWindowIntervalTypes] FOREIGN KEY([RollingWindowIntervalTypeID])
REFERENCES [dbo].[AlertRollingWindowIntervalTypes] ([ID])
GO
ALTER TABLE [dbo].[SavedSearches] CHECK CONSTRAINT [FK_SavedSearches_AlertRollingWindowIntervalTypes]
GO
ALTER TABLE [dbo].[SystemJobs]  WITH CHECK ADD  CONSTRAINT [FK_SystemJobs_JobMaintenancePeriods] FOREIGN KEY([MaintenancePeriodID])
REFERENCES [dbo].[JobMaintenancePeriods] ([ID])
GO
ALTER TABLE [dbo].[SystemJobs] CHECK CONSTRAINT [FK_SystemJobs_JobMaintenancePeriods]
GO
ALTER TABLE [dbo].[SystemJobs]  WITH CHECK ADD  CONSTRAINT [FK_SystemJobs_JobSteps] FOREIGN KEY([JobStepID])
REFERENCES [dbo].[JobSteps] ([ID])
GO
ALTER TABLE [dbo].[SystemJobs] CHECK CONSTRAINT [FK_SystemJobs_JobSteps]
GO

ALTER TABLE [dbo].[SystemJobWorkItems]  WITH CHECK ADD  CONSTRAINT [FK_SystemJobWorkItems_JobWorkItemStates] FOREIGN KEY([StateID])
REFERENCES [dbo].[JobWorkItemStates] ([ID])
GO
ALTER TABLE [dbo].[SystemJobWorkItems] CHECK CONSTRAINT [FK_SystemJobWorkItems_JobWorkItemStates]
GO

ALTER TABLE [dbo].[SystemJobWorkItems]  WITH CHECK ADD  CONSTRAINT [FK_SystemJobWorkItems_SystemJobs] FOREIGN KEY([JobID])
REFERENCES [dbo].[SystemJobs] ([ID])
GO
ALTER TABLE [dbo].[SystemJobWorkItems] CHECK CONSTRAINT [FK_SystemJobWorkItems_SystemJobs]
GO

ALTER TABLE [dbo].[SharePointObjects]  WITH CHECK ADD  CONSTRAINT [FK_SharePointObjects_Principals] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[SharePointObjects] CHECK CONSTRAINT [FK_SharePointObjects_Principals]
GO

ALTER TABLE [dbo].[SharePointObjects]  WITH CHECK ADD  CONSTRAINT [FK_SharePointObjects_Principals1] FOREIGN KEY([ModifiedBy])
REFERENCES [dbo].[Principals] ([PrincipalID])
GO
ALTER TABLE [dbo].[SharePointObjects] CHECK CONSTRAINT [FK_SharePointObjects_Principals1]
GO

CREATE TABLE [dbo].[Rules](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RuleTypeID] [int] NULL,
	[Name] [nvarchar](max) NOT NULL,
	[IsContainer] [bit] NOT NULL,
	[IsBuiltin] [bit] NOT NULL,
	[Configuration] [xml] NULL,
	[LastRunTime] [datetime] NOT NULL,
	[ParentID] [int] NULL,
	[Enabled] [bit] NOT NULL,
	[RuleStatusID] [int] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RuleHistory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RuleID] [int] NOT NULL,
	[RuleStatusID] [int] NOT NULL,
	[StartTime] [datetime] NOT NULL,
	[EndTime] [datetime] NULL,
	[Details] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RuleHistory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RuleTypes](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_RuleTypes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[RuleHistory]  WITH CHECK ADD  CONSTRAINT [FK_RuleHistory_Rules] FOREIGN KEY([RuleID])
REFERENCES [dbo].[Rules] ([ID])
GO
ALTER TABLE [dbo].[RuleHistory] CHECK CONSTRAINT [FK_RuleHistory_Rules]
GO
ALTER TABLE [dbo].[Rules]  WITH CHECK ADD  CONSTRAINT [FK_Rule_RuleTypes] FOREIGN KEY([RuleTypeID])
REFERENCES [dbo].[RuleTypes] ([ID])
GO
ALTER TABLE [dbo].[Rules] CHECK CONSTRAINT [FK_Rule_RuleTypes]
GO

INSERT INTO PrincipalTypes (PrincipalTypeID, Name) VALUES (4, 'Security Group')

INSERT INTO JobMaintenancePeriods VALUES (1,'Day');
INSERT INTO JobMaintenancePeriods VALUES (2,'Hour');
INSERT INTO JobMaintenancePeriods VALUES (3,'Minute');
INSERT INTO JobMaintenancePeriods VALUES (4,'Manual');

INSERT INTO JobSteps VALUES (1,'Idle');
INSERT INTO JobSteps VALUES (2,'CheckingRunConditions');
INSERT INTO JobSteps VALUES (3,'Initializing');
INSERT INTO JobSteps VALUES (4,'InProgress');

INSERT INTO JobWorkItemStates VALUES (1,'NotStarted');
INSERT INTO JobWorkItemStates VALUES (2,'InProgress');
INSERT INTO JobWorkItemStates VALUES (3,'Finished');

INSERT INTO AlertThrottlingIntervalTypes VALUES (1,'Minutes');
INSERT INTO AlertThrottlingIntervalTypes VALUES (2,'Hours');
INSERT INTO AlertThrottlingIntervalTypes VALUES (3,'Days');

INSERT INTO AlertRollingWindowIntervalTypes VALUES (1,'None');
INSERT INTO AlertRollingWindowIntervalTypes VALUES (2,'Minutes');
INSERT INTO AlertRollingWindowIntervalTypes VALUES (3,'Hours');
INSERT INTO AlertRollingWindowIntervalTypes VALUES (4,'Days');


INSERT INTO SystemJobs Values(1, 'Send e-mails', 1, 1, 0, '', 1, 1, '2000-01-01 12:00:00.000', '2000-01-01 00:00:00.000', '', '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.000', NULL);
INSERT INTO SystemJobs Values(2, 'Event Collection', 1, 1, 0, '', 2, 4, '2000-01-01 02:00:00.000', '2000-01-01 00:00:00.000', '', '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.000', NULL);
INSERT INTO SystemJobs Values(3, 'Data Retention', 1, 1, 0, '', 1, 1, '2000-01-01 03:00:00.000', '2000-01-01 00:00:00.000', '', '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.000', NULL);
INSERT INTO SystemJobs Values(4, 'Governance Agent', 1, 1, 0, '', 3, 5, '2000-01-01 04:00:00.000', '2000-01-01 00:00:00.000', '', '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.000', NULL);

INSERT INTO [RuleTypes] ([ID], [Name], [Description]) VALUES (1, 'Document Version History' , 'Specify whether a version is created each time you edit a file in a document library.')
INSERT INTO [RuleTypes] ([ID], [Name], [Description]) VALUES (2, 'Require Check Out' , 'Specify whether users must check out documents before making changes in this document library.')

GO
ALTER TABLE [dbo].[Principals]  WITH CHECK ADD  CONSTRAINT [FK_Principals_WebApplications] FOREIGN KEY([TenantID])
REFERENCES [dbo].[WebApplications] ([ID])

GO
ALTER TABLE [dbo].[SharePointObjects]  WITH CHECK ADD  CONSTRAINT [FK_SharePointObjects_WebApplications] FOREIGN KEY([WebAppID])
REFERENCES [dbo].[WebApplications] ([ID])
GO

UPDATE [dbo].[Options] SET [OptionValue] = '3.3.0' WHERE [OptionID] = 14
