/*
 * $Id: schema65.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * Description:
 *
 * SQL Script to build the ManageSoft database on Microsoft SQL Server.
 * It is the first script that has to be run to setup the database.
 *
 * The steps performed by this script are:
 * 1. Create new database
 * 2. Create database table and initialiase data (skip if the table exists)
 *
 * If running the script manually, you might need to substitute some
 * configuration values:
 * DATABASEFILENAME should be set to the pathname (.mdf) for the database file
 * DEFAULT_NAMING_CONTEXT should be set to the DN of your domain controller
 *
 * Protected by US patent 6,006,034; patents pending.
 * Copyright 2000-2002 ManageSoft Corporation. All rights reserved.
 * Reproduction, adaptation, or translation without prior permission
 * is prohibited.
 *
 * Copyright 1995-2002 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */
USE ManageSoft
GO

PRINT '------------------------';
PRINT 'Creating database tables';
PRINT '------------------------';

PRINT 'DatabaseConfiguration table:'
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DatabaseConfiguration'
)
BEGIN
	PRINT 'DatabaseConfiguration table:'
	CREATE TABLE DatabaseConfiguration
	(
		-- Name of the configuration property
		Property	varchar (32)
			NOT NULL
			CONSTRAINT PK_Property PRIMARY KEY CLUSTERED,

		-- Value of the configuration property
		Value		varchar (256)
			NOT NULL,

		-- Date the property was initially set
		Created		datetime
			NOT NULL DEFAULT(GETDATE()),

		-- Date the Property was last set
		LastUpdate	datetime
			NOT NULL DEFAULT(GETDATE()),
	);

	/*
	 * Set database version.
	 *
	 * The SchemaVersion property should be updated when the database
	 * schema is changed; it should be the same as version number of
	 * the product.
	 *
	 * If the product version changes but the database schema
	 * doesn't change, the version column should not be updated.
	 */
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('SchemaVersion', '6.5');
END -- Configuration table
ELSE
	PRINT 'Using existing DatabaseConfiguration table'

PRINT '--- Directory tables ---'

IF NOT EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'Organization'
)
BEGIN
	PRINT 'Organization (Domain/OrganizationalUnit/Container) table:'

	CREATE TABLE Organization (
		-- Auto generated identity number
		OrganizationID		int
			NOT NULL IDENTITY
			CONSTRAINT PK_OrganizationID PRIMARY KEY,

		-- Distinguished name, relative to the domain
		RDN			nvarchar(400)
			NULL,

		-- GUID from Active Directory
		GUID			binary(16)
			NULL,

		-- OrganizationID of the domain in which the entry resides
		-- NOTE: For a domain, must be set to reference self
		DomainID		int
			NOT NULL DEFAULT(0),

		-- Distinguished name, relative to the domain, in reverse order
		RDNReverse		nvarchar(400)
			NULL,

		-- Each Domain has an unknown OU, to store things which
		-- claim to belong to the domain but we don't know where.
		IsUnknown AS (CASE WHEN RDN IS NULL THEN 1 ELSE 0 END),

		-- Each Domain has a domain Organization record with the same ID
		-- These Domain records have an RDN value of empty (not NULL)
		IsDomain AS (CASE WHEN DomainID = OrganizationID
				THEN 1 ELSE 0 END)
	);
END -- Organization table
ELSE
	PRINT 'Using existing Organization table'
GO

-- REVISIT: This index can be dropped when exact match on RDN not used
-- (use RDNReverse instead)
-- We don't want to create OrganizationByName index at this stage
-- when upgrading since we'll have non-unique values that need to be migrated
-- this index will be created at the end of the upgrade process.
PRINT ' OrganizationByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'OrganizationByName'
)
BEGIN
	IF (SELECT Value FROM DatabaseConfiguration WHERE Property = 'SchemaVersion') = '6.5'
	BEGIN
		CREATE UNIQUE INDEX OrganizationByName
		ON Organization(
	       		DomainID,               -- For a given domain
			RDN                     -- Find an RDN
		) WITH DROP_EXISTING;
	END
END
ELSE
BEGIN
	IF (SELECT Value FROM DatabaseConfiguration WHERE Property = 'SchemaVersion') = '6.5'
	BEGIN
		CREATE UNIQUE INDEX OrganizationByName
		ON Organization(
	       		DomainID,               -- For a given domain
			RDN                     -- Find an RDN
		);
	END
END
GO

-- This index supports sub-OU queries using "LIKE 'XX=rdn,%'"
PRINT ' OrganizationByNameReverse index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'OrganizationByNameReverse'
)
	CREATE INDEX OrganizationByNameReverse
		ON Organization(
			DomainID,		-- For a given domain
			RDNReverse		-- Find an RDN (reverse)
		) WITH DROP_EXISTING;
ELSE
	CREATE INDEX OrganizationByNameReverse
		ON Organization(
			DomainID,		-- For a given domain
			RDNReverse		-- Find an RDN (reverse)
		);

-- Not unique, because that would disallow multiple NULL GUIDs
PRINT ' OrganizationByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'OrganizationByGUID'
)
	CREATE INDEX OrganizationByGUID
		ON Organization(GUID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX OrganizationByGUID
		ON Organization(GUID);
GO

IF NOT EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'Domain'
)
BEGIN
	PRINT 'Domain table:'

	CREATE TABLE Domain (
		-- Foreign key to Organization table
		OrganizationID		int
			NOT NULL
			CONSTRAINT FK_Domain_ID
			REFERENCES Organization(OrganizationID),

		-- Fully qualified distinguished name
		DN			nvarchar(100) NULL,

		-- Type of directory service running on the domain:
		-- NULL, 'NT4', 'AD', 'NDS', 'NIS', etc
		--
		-- NOTE: If not 'AD', then ADReconcile and MergePolicies
		-- flags should also be set to FALSE, as they cannot be
		-- performed on non-AD domains.
		DomainType		varchar(4) NULL,

		-- The flat domain name - optional
		FlatName		nvarchar(32) NULL,

		-- Preferred domain controller to query
		PreferredDomainController nvarchar(32) NULL,

		-- Failover if preferred domain controller is out of contact?
		PreferredDomainControllerOnly bit
			NOT NULL DEFAULT(0),

		-- Whether to reconcile the ManageSoft database with AD
		ADReconcile		bit
			NOT NULL DEFAULT(0),

		-- If reconciling the ManageSoft database with Active
		-- Directory, the amount of time in minutes before the
		-- Active Directory data is refreshed in the database
		ADLoadLatency		int
			NOT NULL DEFAULT(60),    -- 1 hour

		-- Whether to generate merged policies
		MergePolicies		bit
			NOT NULL DEFAULT(0),

		-- When AD reconciliation was last done
		LastADReconcile		smalldatetime NULL,

		-- Whether the last AD reconciliation succeeded
		LastADReconcileStatus	bit
			NOT NULL DEFAULT(0),

		-- Date and time of last ADLoad, if NULL means do ADLoad
		-- at next reconcile
		LastADLoad		smalldatetime NULL,

		-- When policy merging was last done
		LastPolicyMerge		smalldatetime NULL,

		-- Whether the last policy merge succeeded
		LastPolicyMergeStatus	bit
			NOT NULL DEFAULT(0),

		-- Fully qualified distinguished name, in reverse order
		-- Populated automatically by a trigger
		DNReverse		nvarchar(100) NULL
	);
END
ELSE
	PRINT 'Using existing Domain table'
GO

PRINT ' DomainByOrganizationID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'DomainByOrganizationID'
)
	CREATE UNIQUE CLUSTERED INDEX DomainByOrganizationID
		ON Domain(OrganizationID) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX DomainByOrganizationID
		ON Domain(OrganizationID);

PRINT ' DomainByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'DomainByName'
)
	CREATE UNIQUE INDEX DomainByName
		ON Domain(DN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX DomainByName
		ON Domain(DN);

PRINT ' DomainByNameReverse index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'DomainByNameReverse'
)
	CREATE INDEX DomainByNameReverse
		ON Domain(DNReverse) WITH DROP_EXISTING;
ELSE
	CREATE INDEX DomainByNameReverse
		ON Domain(DNReverse);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'User'
)
BEGIN
	PRINT 'User table:'
	CREATE TABLE [User] (
		-- Auto-generated identity #
		UserID			int
			NOT NULL IDENTITY
			CONSTRAINT PK_User PRIMARY KEY NONCLUSTERED,

		-- CN of the User
		UserCN			varchar(64) NOT NULL,

		-- OU where User belongs
		UserOUID		int
			NOT NULL
			CONSTRAINT FK_User_OU REFERENCES Organization(OrganizationID),

		-- GUID from Active Directory
		GUID			binary(16) NULL,

		-- Login name, unique in domain
		SAMAccountName		varchar(20) NULL
	);
END -- User table
ELSE
	PRINT 'Using existing User table'
GO

PRINT '	UserByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'UserByName'
)
	CREATE UNIQUE CLUSTERED INDEX UserByName
		ON [User] (UserOUID, UserCN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX UserByName
		ON [User] (UserOUID, UserCN);

PRINT '	UserByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'UserByGUID'
)
	CREATE INDEX UserByGUID
		ON [User] (GUID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX UserByGUID
		ON [User] (GUID);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Computer'
)
BEGIN
	PRINT 'Computer table:'
	CREATE TABLE Computer (
		-- Auto-generated identity #
		ComputerID		int
			NOT NULL IDENTITY
			CONSTRAINT PK_Computer PRIMARY KEY NONCLUSTERED,

		-- CN of the Computer
		ComputerCN		varchar(64) NOT NULL,

		-- OU where Computer belongs
		ComputerOUID		int
			NOT NULL
			CONSTRAINT FK_Computer_OU REFERENCES Organization(OrganizationID),

		OperatingSystem		varchar(128) NULL,

		-- GUID from Active Directory
		GUID			binary(16) NULL
	);
END -- Computer table
ELSE
	PRINT 'Using existing Computer table'
GO

PRINT '	ComputerByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ComputerByName'
)
	CREATE UNIQUE CLUSTERED INDEX ComputerByName
		ON Computer (ComputerOUID, ComputerCN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX ComputerByName
		ON Computer (ComputerOUID, ComputerCN);

PRINT '	ComputerByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ComputerByGUID'
)
	CREATE INDEX ComputerByGUID
		ON Computer (GUID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX ComputerByGUID
		ON Computer (GUID);
GO

/* name mapping for TargetTypes */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TargetType'
)
BEGIN
	PRINT 'TargetType table:'
	CREATE TABLE TargetType
	(
		-- Unique Identifier of TargetType
		TargetTypeID		int PRIMARY KEY NOT NULL,

		-- Name of TargetType
		TargetTypeName		nvarchar(256) NOT NULL
			CONSTRAINT UN_TargetTypeName UNIQUE
	);

	-- populate table with initial target types
	INSERT INTO TargetType VALUES (1, 'Computer');
	INSERT INTO TargetType VALUES (2, 'User');
	INSERT INTO TargetType VALUES (3, 'Group');
	INSERT INTO TargetType VALUES (4, 'DistributionLocation');
	INSERT INTO TargetType VALUES (5, 'DistributionServer');
END /* TargetType */
ELSE
	PRINT 'Using existing TargetType table'

GO

PRINT '--- Launcher event logging tables ---'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InstallationStatus'
)
BEGIN
	PRINT 'InstallationStatus Table:'
	CREATE TABLE InstallationStatus (
		-- Computer WHERE the package install occured, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_InstallationStatus_Computer
				REFERENCES Computer(ComputerID),

		/*
		 * The next two will become a reference to the PackageVersion
		 * table in a future release of ManageSoft, so beware.
		 */
		-- Package Name
		PackageName varchar (64) NOT NULL,

		-- Package Version Number
		Version varchar (32) NOT NULL,

		-- User who performed the action, FK to User
		UserID int NOT NULL
			CONSTRAINT FK_InstallationStatus_User
				REFERENCES [User](UserID),

		/*
		 * FK to OU of User or Computer. This is denormalised, since
		 * it's dependent on other data in this row, but useful for
		 * clustering!	If UserId = 1 (SYSTEM), the ComputerOUID will
		 * be used, else UserOUID
		 */
		OUID int NOT NULL
			CONSTRAINT FK_InstallationStatus_OU
				REFERENCES Organization(OrganizationID),

		-- Install or Uninstall; in future downgrade, selfheal, etc
		Action varchar (10) NOT NULL,

		-- Date/Time reported
		Reported datetime NULL,

		-- DateTime incorporated
		Received datetime NULL DEFAULT GETDATE(),

		-- Empty if no failure
		FailReason text NULL,

		-- Installation result (success, failure)
		Result varchar(16) NULL,

		-- Concatenated field for convenience
		PackageNameAndVersion
			AS PackageName + ' ' + Version
	);
END -- InstallationStatus
ELSE
	PRINT 'Using existing InstallationStatus table'
GO

PRINT '	InstallationStatusByCompPkgVer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InstallationStatusByCompPkgVer'
)
	CREATE UNIQUE CLUSTERED INDEX InstallationStatusByCompPkgVer
		ON InstallationStatus(
			OUID,		-- group by OU the targets with package
			ComputerID,
			PackageName,
			Version,
			UserID
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX InstallationStatusByCompPkgVer
		ON InstallationStatus(
			OUID,		-- group by OU the targets with package
			ComputerID,
			PackageName,
			Version,
			UserID
		);

PRINT '	InstallationStatusByPackage index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InstallationStatusByPackage'
)
	CREATE INDEX InstallationStatusByPackage
		ON InstallationStatus(
			PackageName,
			OUID,		-- group by OU the targets with package
			ComputerID
		) WITH DROP_EXISTING;
ELSE
	CREATE INDEX InstallationStatusByPackage
		ON InstallationStatus(
			PackageName,
			OUID,		-- group by OU the targets with package
			ComputerID
		);

PRINT '	InstallationStatusByUser index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InstallationStatusByUser'
)
	CREATE INDEX InstallationStatusByUser
		ON InstallationStatus(
			OUID,	-- group users by OU
			UserID
		) WITH DROP_EXISTING;
ELSE
	CREATE INDEX InstallationStatusByUser
		ON InstallationStatus(
			OUID,	-- group users by OU
			UserID
		);

PRINT '	InstallationStatusByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InstallationStatusByComputer'
)
	CREATE INDEX InstallationStatusByComputer
		ON InstallationStatus(ComputerID) WITH DROP_EXISTING
ELSE
	CREATE INDEX InstallationStatusByComputer
		ON InstallationStatus(ComputerID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InstallationHistory'
)
BEGIN
	PRINT 'InstallationHistory table:'
	CREATE TABLE InstallationHistory (
		UID			uniqueidentifier
			NULL DEFAULT newid(),

		-- Computer where the package install occured, FK to Computer
		ComputerID		int
			NOT NULL
			CONSTRAINT FK_InstallationHistory_Computer
				REFERENCES Computer(ComputerID),

		-- Package Name
		PackageName		varchar (64) NOT NULL,

		-- Package Version
		Version			varchar (32) NOT NULL,

		-- User who performed the action, FK to User
		UserID			int
			NOT NULL
			CONSTRAINT FK_InstallationHistory_User
				REFERENCES [User](UserID),

		-- REVISIT; do we need to add OUID's here?

		-- Install/Uninstall
		Action			varchar (10) NOT NULL,

		-- Date/Time reported
		Reported		datetime NULL,

		-- DateTime incorporated
		Received		datetime
			NULL DEFAULT GETDATE(),

		-- Empty if no failure
		FailReason		text NULL,

		-- Installation result
		Result			varchar(16) NULL,

		-- Concatenated field for convenience
		PackageNameAndVersion
			AS PackageName + ' ' + Version
	);
END -- InstallationHistory
ELSE
	PRINT 'Using existing InstallationHistory table'
GO

PRINT '	InstallationHistoryByCompPkgWhen index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InstallationHistoryByCompPkgWhen'
)
	CREATE UNIQUE CLUSTERED INDEX InstallationHistoryByCompPkgWhen
		ON InstallationHistory(
			UID,
			ComputerID,
			PackageName,
			Reported
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX InstallationHistoryByCompPkgWhen
		ON InstallationHistory(
			UID,
			ComputerID,
			PackageName,
			Reported
		);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InstallationNotification'
)
BEGIN
	PRINT 'InstallationNotification table:'
	CREATE TABLE InstallationNotification (
		-- Boolean, NULL if false
		Enabled char (1) NULL,

		-- email address or logon for console messages.
		Recipient varchar (128) NULL,

		-- Notification name.
		NotifName varchar (128) NOT NULL,

		-- install or uninstall
		Action varchar (16) NULL,

		-- Date when last sent.
		LastNotification datetime
			DEFAULT (GETDATE()) NULL,

		-- Used for optimising lookups.
		NotifyIfAfter datetime
			DEFAULT (GETDATE()) NULL,

		-- email or console
		NotificationType varchar (16)
			DEFAULT ('email') NOT NULL,

		-- Notifications are sent at most this many times.
		MaxFrequency integer
			DEFAULT (30) NOT NULL,

		-- 'days', 'hours' or 'minutes'
		MaxFrequencyUnit varchar (10)
			DEFAULT ('minutes') NOT NULL,

		-- File name (no path) of the results html template file.
		ResultsPageTemplate varchar (256)
			DEFAULT ('restmplt.htm') NULL,

		-- Boolean, NULL if false
		IncludeResults char (1) NULL,

		-- text or html
		MsgBodyFormat varchar (5)
			DEFAULT ('text') NULL,

		-- Message text proforma.
		Proforma varchar (1024) NULL,

		-- Limiting Criteria (NULL values means ignore this criteria)

		-- success of failure
		Result varchar (16) NULL,

		-- Boolean, NULL if false
		ByUserOU varchar (512) NULL,

		IncludeSubByUserOUs char (1) NULL,

		-- Boolean, NULL if false
		ComputerOU varchar (512) NULL,

		IncludeSubComputerOUs char (1) NULL,

		ByUserCN varchar (64) NULL,

		ComputerCN varchar (64) NULL,

		PackageName varchar (64) NULL,

		Version varchar (32) NULL,

		CONSTRAINT PK_InstallationNotification PRIMARY KEY NONCLUSTERED
		(
			NotifName
		)
	);

	/*
	 * Add a default InstallationNotification Entry.
	 * Most of the notification criteria below are omitted
	 * so this Notification will notify on any launcher event
	 */
	INSERT INTO InstallationNotification
	(
		Recipient,	-- Logon or email address of recipient
		NotifName,	-- Notification name
		Result,		-- For any result type: failure or success
		NotificationType, -- Send this type of notification:
		MaxFrequency	-- Maximum Frequency of notifications
	)
	VALUES
	(
		'admin@my.org',
		'All failed installations',
		'failure',
		'email',
		'0'
	);
END -- InstallationNotification
ELSE
	PRINT 'Using existing InstallationNotification table'
GO

/*
 * Inventory tables start here
 *
 * Software is inventoried by Software name, not by package.
 * This will change in future, when Package Names will be added.
 */
PRINT '--- Inventory tables ---'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InventoryReport'
)
BEGIN
	PRINT 'InventoryReport table:'
	/*
	 * InventoryReport contains a record for each machine
	 * that has either hardware or software reports.
	 */
	CREATE TABLE InventoryReport (
		-- Computer where the inventory was gathered, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_InventoryReport_Computer
				REFERENCES Computer(ComputerID),

		-- REVISIT: Do we need ComputerOUID here?

		-- User for whom the inventory was gathered, FK to User
		UserID int NOT NULL
			CONSTRAINT FK_InventoryReport_User
				REFERENCES [User](UserID),

		-- When was software tracked?
		SWDate datetime NULL,

		-- When was hardware tracked?
		HWDate datetime NULL,

		-- When did we track files?
		FilesDate datetime NULL,

		-- What is the sequence number (differential inventory only)?
		SequenceNumber int NULL
	);
END -- InventoryReport table
ELSE
	PRINT 'Using existing InventoryReport table'
GO

PRINT '	InventoryReport index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'InventoryReportByComputer'
)
	CREATE UNIQUE INDEX InventoryReportByComputer
		ON InventoryReport(
			ComputerID,
			UserID
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX InventoryReportByComputer
		ON InventoryReport(
			ComputerID,
			UserID
		);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareVersion'
)
BEGIN
	PRINT 'SoftwareVersion table:'
	/*
	 * SoftwareVersion contains a record for each distinct software
	 * name/version number combination, and associates a unique Id
	 * with each record.
	 */
	CREATE TABLE SoftwareVersion (
		-- Auto-generated identity #
		SoftwareID int NOT NULL IDENTITY
			CONSTRAINT PK_SoftwareVersion PRIMARY KEY NONCLUSTERED,

		-- Which software (was application)?
		SoftwareName varchar (128) NOT NULL,

		-- Software Version?
		Version varchar (32) NOT NULL,

		-- Concatenated field for convenience
		SoftwareNameAndVersion
			AS SoftwareName + ' ' + Version
	);
END
ELSE
	PRINT 'Using existing SoftwareVersion table'
GO

PRINT '	SoftwareVersionByName index'
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareVersionByName'
)
	CREATE UNIQUE CLUSTERED INDEX SoftwareVersionByName
		ON SoftwareVersion (SoftwareName, Version) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX SoftwareVersionByName
		ON SoftwareVersion (SoftwareName, Version);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareOccurrence'
)
BEGIN
	PRINT 'SoftwareOccurrence table:'
	/*
	 * SoftwareOccurrence contains a record for each application on each
	 * machine.  It says what Version is installed, and how we know.
	 */
	CREATE TABLE SoftwareOccurrence (
		-- SoftwareVersion
		SoftwareID int NOT NULL
			CONSTRAINT FK_SoftwareOccurrence_Software
				REFERENCES SoftwareVersion(SoftwareID),

		-- Computer where the SoftwareVersion was found, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_SoftwareOccurrence_Computer
				REFERENCES Computer(ComputerID),

		/*
		 * FK to OU of Computer. This is denormalised, since it's
		 * dependent on other data in this row, but useful for
		 * clustering!
		 */
		ComputerOUID int NOT NULL
			CONSTRAINT FK_SoftwareOccurrence_ComputerOU
				REFERENCES Organization(OrganizationID),

		-- User for whom the SoftwareVersion was installed, FK to User
		UserID int NOT NULL
			CONSTRAINT FK_SoftwareOccurrence_User
				REFERENCES [User](UserID),

		/*
		 * FK to OU of User. This is denormalised, since it's dependent
		 * on other data in this row, but useful for clustering!
		 */
		UserOUID int NOT NULL
			CONSTRAINT FK_SoftwareOccurrence_UserOU
				REFERENCES Organization(OrganizationID),

		-- How do we know the app is there?
		Evidence char (32) NULL,

		-- ManageSoft PackageFullName if known
		PackageFullName varchar (256) NULL,

		-- ManageSoft Policy GUID if known
		-- REVISIT: should be binary(16), must fix ndtrkres
		PolicyGUID varchar (40) NULL
	);
END -- SoftwareOccurrence table
ELSE
	PRINT 'Using existing SoftwareOccurrence table'
GO

PRINT '	SoftwareOccurrenceBySWComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareOccurrenceBySWComputer'
)
	CREATE UNIQUE CLUSTERED INDEX SoftwareOccurrenceBySWComputer
		ON SoftwareOccurrence(
			SoftwareId,
			ComputerOUID,
			ComputerID,
			UserID
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX SoftwareOccurrenceBySWComputer
		ON SoftwareOccurrence(
			SoftwareId,
			ComputerOUID,
			ComputerID,
			UserID
		);

PRINT '	SoftwareByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareByComputer'
)
	CREATE INDEX SoftwareByComputer
		ON SoftwareOccurrence(
			ComputerOUID,
			ComputerID,
			UserID,
			SoftwareId
		) WITH DROP_EXISTING;
ELSE
	CREATE INDEX SoftwareByComputer
		ON SoftwareOccurrence(
			ComputerOUID,
			ComputerID,
			UserID,
			SoftwareId
		);

PRINT '	SoftwareByComputerID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareByComputerID'
)
	CREATE INDEX SoftwareByComputerID
		ON SoftwareOccurrence(ComputerID, UserID) WITH DROP_EXISTING
ELSE
	CREATE INDEX SoftwareByComputerID
		ON SoftwareOccurrence(ComputerID, UserID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareFile'
)
BEGIN
	PRINT 'SoftwareFile table:'
	/*
	 * SoftwareFile contains a record for each file associated
	 * with an application on each machine.
	 */
	CREATE TABLE SoftwareFile (
		-- Computer where the SoftwareFile was found, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_SoftwareFile_Computer
				REFERENCES Computer(ComputerID),

		/*
		 * FK to OU of Computer. This is denormalised, since it's
		 * dependent on other data in this row, but useful for
		 * clustering!
		 */
		ComputerOUID int NOT NULL
			CONSTRAINT FK_SoftwareFile_ComputerOU
				REFERENCES Organization(OrganizationID),

		-- SoftwareVersion
		SoftwareID int NOT NULL
			CONSTRAINT FK_SoftwareFile_Software
				REFERENCES SoftwareVersion(SoftwareID),

		-- MD5 of file
		MD5 varchar (32) NOT NULL,

		-- Size in bytes
		[Size] int NOT NULL,

		-- Path where file was found
		[Filename] varchar (800) NOT NULL,

		-- Last-Modify time of file
		[DateTime] datetime NULL
	);
END -- SoftwareFile table
ELSE
	PRINT 'Using existing SoftwareFile table'
GO

PRINT '	SoftwareFileByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareFileByComputer'
)
	CREATE CLUSTERED INDEX SoftwareFileByComputer
		ON SoftwareFile(
			ComputerOUID,
			ComputerID,
			SoftwareId
		) WITH DROP_EXISTING
ELSE
	CREATE CLUSTERED INDEX SoftwareFileByComputer
		ON SoftwareFile(
			ComputerOUID,
			ComputerID,
			SoftwareId
		)

PRINT '	SoftwareFileByComputerID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareFileByComputerID'
)
	CREATE INDEX SoftwareFileByComputerID
		ON SoftwareFile(ComputerID) WITH DROP_EXISTING
ELSE
	CREATE INDEX SoftwareFileByComputerID
		ON SoftwareFile(ComputerID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'UnknownFile'
)
BEGIN
	PRINT 'UnknownFile table:'
	/*
	 * UnknownFile contains a record for each file NOT associated
	 * with an application on each machine.
	 */
	CREATE TABLE UnknownFile (
		-- Computer where the UnknownFile was found, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_UnknownFile_Computer
				REFERENCES Computer(ComputerID),

		-- MD5 of file
		MD5 varchar (32) NOT NULL,

		-- Size in bytes
		[Size] int  NOT NULL,

		-- Path where file was found
		[Filename] varchar (800) NOT NULL,

		-- Last-Modify time of file
		[DateTime] datetime NULL,

		CONSTRAINT PK_UnknownFile PRIMARY KEY NONCLUSTERED
		(
			ComputerID,
			[Filename]
		)
	);
END -- UnknownFile table
ELSE
	PRINT 'Using existing UnknownFile table'
GO

PRINT '	UnknownFileByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'UnknownFileByComputer'
)
	CREATE CLUSTERED INDEX UnknownFileByComputer
		ON UnknownFile(
			ComputerID,
			MD5
		) WITH DROP_EXISTING;
ELSE
	CREATE CLUSTERED INDEX UnknownFileByComputer
		ON UnknownFile(
			ComputerID,
			MD5
		);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareObject'
)
BEGIN
	PRINT 'HardwareObject table:'
	/*
	 * HardwareObject's are normally as reported through WMI.
	 * Sometimes a Hardware Class has no key, so duplicate names may occur,
	 * so we allocate a meaningless Occurrence number to distinguish them.
	 */
	CREATE TABLE HardwareObject (
		-- Computer where the HardwareObject was found, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_HardwareObject_Computer
				REFERENCES Computer(ComputerID),

		/*
		 * FK to OU of Computer. This is denormalised, since it's
		 * dependent on other data in this row, but useful for
		 * clustering!
		 */
		ComputerOUID int NOT NULL
			CONSTRAINT FK_HardwareObject_ComputerOU
				REFERENCES Organization(OrganizationID),

		-- Hardware object name
		HardwareName varchar (256) NOT NULL,

		-- In case of duplicate names
		Occurrence int NOT NULL,

		-- Hardware object class
		Class varchar (256) NULL
	);
END -- HardwareObject table
ELSE
	PRINT 'Using existing HardwareObject table'
GO

PRINT '	HardwareObjByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'HardwareObjByComputer'
)
	CREATE UNIQUE CLUSTERED INDEX HardwareObjByComputer
		ON HardwareObject(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX HardwareObjByComputer
		ON HardwareObject(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence
		);

PRINT '	HardwareObjectByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'HardwareObjectByComputer'
)
	CREATE INDEX HardwareObjectByComputer
		ON HardwareObject(ComputerID) WITH DROP_EXISTING
ELSE
	CREATE INDEX HardwareObjectByComputer
		ON HardwareObject(ComputerID)

PRINT '	HardwareObjByClassName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'HardwareObjByClassName'
)
	CREATE INDEX HardwareObjByClassName
		ON HardwareObject(
			Class,
			HardwareName,
			Occurrence
		) WITH DROP_EXISTING
ELSE
	CREATE INDEX HardwareObjByClassName
		ON HardwareObject(
			Class,
			HardwareName,
			Occurrence
		)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareProperty'
)
BEGIN
	PRINT 'HardwareProperty table:'
	-- HardwareProperty's are Name/Value pairs attached to HardwareObjects:
	CREATE TABLE HardwareProperty (
		-- Computer where the HardwareObject was found, FK to Computer
		ComputerID int NOT NULL
			CONSTRAINT FK_HardwareProperty_Computer
				REFERENCES Computer(ComputerID),

		/*
		 * FK to OU of Computer. This is denormalised, since it's
		 * dependent on other data in this row, but useful for
		 * clustering!
		 */
		ComputerOUID int NOT NULL
			CONSTRAINT FK_HardwareProperty_ComputerOU
				REFERENCES Organization(OrganizationID),

		-- Hardware object name
		HardwareName varchar (256) NOT NULL,

		-- In case of duplicate names
		Occurrence int NOT NULL,

		-- REVISIT: Down to here could be replaced by a HardwareObjectID

		-- Property name
		Property varchar (64) NOT NULL,

		-- Property value
		Value varchar (256) NULL
	);
END -- HardwareProperty table
ELSE
	PRINT 'Using existing HardwareProperty table'
GO

PRINT '	HardwarePropByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'HardwarePropByName'
)
	CREATE UNIQUE CLUSTERED INDEX HardwarePropByName
		ON HardwareProperty(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence,
			Property
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX HardwarePropByName
		ON HardwareProperty(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence,
			Property
		);

PRINT '	HardwarePropertyByComputer index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'HardwarePropertyByComputer'
)
	CREATE INDEX HardwarePropertyByComputer
		ON HardwareProperty(ComputerID) WITH DROP_EXISTING
ELSE
	CREATE INDEX HardwarePropertyByComputer
		ON HardwareProperty(ComputerID)
GO

PRINT ''
PRINT '--- Distribution tables ---'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackageVersion'
)
BEGIN
	PRINT 'PackageVersion table:'
	CREATE TABLE PackageVersion (
		PackageFullName nvarchar (256) NOT NULL
			CONSTRAINT PK_PackageVersion PRIMARY KEY,

		-- The short name of the package, used mainly on managed devices
		PackageName nvarchar (64) NULL,

		-- REVISIT: Should be varchar not nvarchar
		Version nvarchar (32) NULL,

		Title nvarchar (64) NULL,

		MD5 nvarchar (40) NULL,

		Size integer NULL,

		-- Concatenated field for convenience
		PackageNameAndVersion
			AS PackageName + ' ' + Version
	);
END -- PackageVersion table
ELSE
	PRINT 'Using existing PackageVersion table'
GO

PRINT '	PackageVersionByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'PackageVersionByName'
)
	CREATE INDEX PackageVersionByName
		ON PackageVersion(PackageName, Version) WITH DROP_EXISTING;
ELSE
	CREATE INDEX PackageVersionByName
		ON PackageVersion(PackageName, Version);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionServer'
)
BEGIN
	PRINT 'DistributionServer table:'
	CREATE TABLE DistributionServer (
		ServerUID binary(16) NOT NULL
			CONSTRAINT PK_DistributionServer PRIMARY KEY,

		ServerType smallint NOT NULL DEFAULT(0),

		ServerName nvarchar (64) NULL,

		PrimaryParentUID binary(16) NULL,

		-- Configuration information:
		ConfigState nvarchar (20) NULL,

		LastConfigStart datetime NULL,

		LastConfigJobId nvarchar (40) NULL,

		ConfigFailReason ntext NULL
	);
END -- DistributionServer table
ELSE
	PRINT 'Using existing DistributionServer table'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributedPackage'
)
BEGIN
	PRINT 'DistributedPackage table:'
	CREATE TABLE DistributedPackage (
		ServerUID binary (16) NOT NULL
			CONSTRAINT FK_DistributedPackage_ServerUID
				REFERENCES DistributionServer(ServerUID),

		PackageFullName nvarchar (256) NOT NULL
		--	CONSTRAINT FK_DistributedPackage_PackageFullName
		--		REFERENCES PackageVersion(PackageFullName)
			,

		ConfirmedState nvarchar (16) NULL,

		ConfirmedUpdate datetime NULL,

		-- REVISIT: Should be varchar not nvarchar
		ConfirmedVersion nvarchar (32) NULL,

		ConfirmedReason ntext NULL,

		RequestState nvarchar (16) NULL,

		RequestUpdate datetime NULL,

		-- REVISIT: Should be varchar not nvarchar
		RequestVersion nvarchar (32) NULL,

		CONSTRAINT PK_DistributedPackage PRIMARY KEY CLUSTERED
		(
			ServerUID,
			PackageFullName
		)
	);
END -- DistributedPackage table
ELSE
	PRINT 'Using existing DistributedPackage table'

GO

/* name mapping for DistributionServerTypes */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionServerType'
)
BEGIN
	PRINT 'DistributionServerType table:'
	CREATE TABLE DistributionServerType
	(
		-- Unique Identifier of TargetType
		TargetTypeID int PRIMARY KEY NOT NULL,

		-- Name of TargetType
		TargetTypeName nvarchar(256) NOT NULL
			CONSTRAINT UN_DistributionServerTypeName UNIQUE
	);

	-- populate table with initial target types,
	-- try not to overlap these with the values in TargetType
	INSERT INTO DistributionServerType VALUES (10, 'DistributionGroup');
	INSERT INTO DistributionServerType VALUES (11, 'DistributionLocation');
	INSERT INTO DistributionServerType VALUES (12, 'DistributionServer');
END /* DistributionServerType */
ELSE
	PRINT 'Using existing DistributionServerType table'

GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionGroup'
)
BEGIN
	PRINT 'DistributionGroup table:'
	CREATE TABLE DistributionGroup (
		GroupUID binary(16) NOT NULL 
			CONSTRAINT PK_DistributionGroup PRIMARY KEY,

		-- Name of the group
		GroupName nvarchar(128) NOT NULL
	);
END -- DistributionGroup table
ELSE
	PRINT 'Using existing DistributionGroup table'
GO

PRINT '	DistributionGroupByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'DistributionGroupByName'
)
	CREATE INDEX DistributionGroupByName
		ON DistributionGroup (GroupName) WITH DROP_EXISTING;
ELSE
	CREATE INDEX DistributionGroupByName
		ON DistributionGroup (GroupName);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionGroupMember'
)
BEGIN
	PRINT 'DistributionGroupMember table:'
	CREATE TABLE DistributionGroupMember (
		-- Auto-generated identity #
		GroupUID binary(16) NOT NULL 
			CONSTRAINT FK_DistributionGroupMember_GroupUID
				REFERENCES DistributionGroup(GroupUID),

		-- ID of the member
		MemberID binary(16) NOT NULL,

		-- type of the member - comes from targettype table and
		-- must be a distributionlocation or a group
		MemberType int NOT NULL
			CONSTRAINT FK_DistributionGroupMember_TargetType
			REFERENCES DistributionServerType(TargetTypeID),
	);
END -- DistributionGroupMember table
ELSE
	PRINT 'Using existing DistributionGroupMember table'
GO

PRINT ' DistributionGroupMemberByType index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'DistributionGroupMemberByType'
)
	CREATE CLUSTERED INDEX DistributionGroupMemberByType
		ON DistributionGroupMember (MemberType, MemberID) WITH DROP_EXISTING;
ELSE
	CREATE CLUSTERED INDEX DistributionGroupMemberByType
		ON DistributionGroupMember (MemberType, MemberID);
GO

PRINT '--- Licensing tables ---';

/* stored procedure calls to add our types */
/*
EXEC sp_addtype TrackedBy, 'INTEGER', 'NOT NULL'
EXEC sp_bindrule 'TrackedBy_Rule', 'TrackedBy'
EXEC sp_addtype LicenseAgreement, 'NVARCHAR(256)', 'NOT NULL'
*/

/* name mapping for License Models */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'LicenseModel'
)
BEGIN
	PRINT 'LicenseModel table:'
	CREATE TABLE LicenseModel
	(
		-- Unique Identifier of LicenseModel
		ModelID int IDENTITY PRIMARY KEY NOT NULL,

		-- Name of License Model
		Name nvarchar(256) NOT NULL
			CONSTRAINT UN_LicenseModelName UNIQUE
	);

	-- populate table with initial target types
	INSERT INTO LicenseModel VALUES ('');
END /* TargetType */
ELSE
	PRINT 'Using existing LicenseModel table'

/* name mapping for Software Publisher */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwarePublisher'
)
BEGIN
	PRINT 'SoftwarePublisher table:'
	CREATE TABLE SoftwarePublisher
	(
		-- Unique Identifier of Publisher
		PublisherID int IDENTITY PRIMARY KEY NOT NULL,

		-- Name of Publisher
		Name nvarchar(256) NOT NULL
			CONSTRAINT UN_SoftwarePublisherName UNIQUE
	);

	-- populate table with initial target types
	INSERT INTO SoftwarePublisher VALUES ('');
END /* TargetType */
ELSE
	PRINT 'Using existing SoftwarePublisher table'


/* name mapping for Software Reseller */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareReseller'
)
BEGIN
	PRINT 'SoftwareReseller table:'
	CREATE TABLE SoftwareReseller
	(
		-- Unique Identifier of Reseller
		ResellerID int IDENTITY PRIMARY KEY NOT NULL,

		-- Name of Reseller
		Name nvarchar(256) NOT NULL
			CONSTRAINT UN_SoftwareResellerName UNIQUE
	);

	-- populate table with initial target types
	INSERT INTO SoftwareReseller VALUES ('');
END /* TargetType */
ELSE
	PRINT 'Using existing SoftwareReseller table'


/* the "license" details */
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareProduct'
)
BEGIN
	PRINT 'SoftwareProduct table:'
	CREATE TABLE SoftwareProduct
	(
		-- Unique identifier for SoftwareProducts
		SoftwareProductID int IDENTITY PRIMARY KEY NOT NULL,

		-- Product Name assigned by the author/vendor
		ProductName nvarchar(256) NOT NULL,

		-- Text describing the Licensing model for the Product
		Model integer NOT NULL
			CONSTRAINT FK_SoftwareProduct_Model
				REFERENCES LicenseModel(ModelID),

		-- In what units are Licences counted?
		TrackedBy integer NOT NULL
			CONSTRAINT FK_SoftwareProduct_TrackedBy
				REFERENCES TargetType(TargetTypeID),

		-- Name of publisher (author)
		Publisher integer NOT NULL
			CONSTRAINT FK_SoftwareProduct_Publisher
				REFERENCES SoftwarePublisher(PublisherID),

		-- Name of vendor/reseller, to correlate with purchase/order
		Reseller integer NOT NULL
			CONSTRAINT FK_SoftwareProduct_Reseller
				REFERENCES SoftwareReseller(ResellerID),

		-- Name of License agreement
		Agreement nvarchar(256) NOT NULL
	);
END /* SoftwareProduct */
ELSE
	PRINT 'Using existing SoftwareProduct table'
GO

PRINT '	SoftwareProductByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareProductByName'
)
	CREATE UNIQUE INDEX SoftwareProductByName
		ON SoftwareProduct (ProductName) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX SoftwareProductByName
		ON SoftwareProduct (ProductName);

PRINT '	SoftwareProductByModel index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareProductByModel'
)
	CREATE INDEX SoftwareProductByModel
		ON SoftwareProduct (Model) WITH DROP_EXISTING;
ELSE
	CREATE INDEX SoftwareProductByModel
		ON SoftwareProduct (Model);

PRINT '	SoftwareProductByPublisher index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareProductByPublisher'
)
	CREATE INDEX SoftwareProductByPublisher
		ON SoftwareProduct (Publisher) WITH DROP_EXISTING;
ELSE
	CREATE INDEX SoftwareProductByPublisher
		ON SoftwareProduct (Publisher);

PRINT '	SoftwareProductByReseller index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareProductByReseller'
)
	CREATE INDEX SoftwareProductByReseller
		ON SoftwareProduct (Reseller) WITH DROP_EXISTING;
ELSE
	CREATE INDEX SoftwareProductByReseller
		ON SoftwareProduct (Reseller);
GO

/* Allocation of licenses to OU's table*/
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'LicenseAllocation'
)
BEGIN
	PRINT 'LicenseAllocation table:'
	CREATE TABLE LicenseAllocation
	(
		-- Unique Identifier of the Product Ownership table
		AllocationID int PRIMARY KEY IDENTITY NOT NULL,

		-- ID of the SoftwareProduct owned (FK)
		SoftwareProductID int NOT NULL
				CONSTRAINT FK_LicenseAllocation_SoftwareProductID
				REFERENCES SoftwareProduct(SoftwareProductID),

		-- ID of the OrganisationalUnit that owns the product
		OUID int NOT NULL
			CONSTRAINT FK_LicenseAllocation_OUID REFERENCES Organization(OrganizationID),

		-- How many licensing units of the product are owned?
		UnitsAllocated int,

		-- When do these licenses expire?
		Expiry		DateTime
	);
END /* allocation of licenses by OU's table */
ELSE
	PRINT 'Using existing LicenseAllocation table'
GO

PRINT '	LicenseAllocationByOU index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'LicenseAllocationByOU'
)
	CREATE INDEX LicenseAllocationByOU
		ON LicenseAllocation (OUID, SoftwareProductID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX LicenseAllocationByOU
		ON LicenseAllocation (OUID, SoftwareProductID);

PRINT '	LicenseAllocationBySoftwareProduct index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'LicenseAllocationBySoftwareProduct'
)
	CREATE INDEX LicenseAllocationBySoftwareProduct
		ON LicenseAllocation (SoftwareProductID, OUID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX LicenseAllocationBySoftwareProduct
		ON LicenseAllocation (SoftwareProductID, OUID);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ProductContainsSoftware'
)
BEGIN
	PRINT 'ProductContainsSoftware table:'
	CREATE TABLE ProductContainsSoftware
	(
		-- This licensed SoftwareProduct...
		SoftwareProductID int NOT NULL
			CONSTRAINT FK_ProductContains_SoftwareProductID
				REFERENCES SoftwareProduct(SoftwareProductID),

		-- Contains or covers this SoftwareVersion:
		SoftwareVersionID int NOT NULL
			CONSTRAINT FK_ProductContains_SoftwareVersionID
				REFERENCES SoftwareVersion(SoftwareID)
	)
END
ELSE
	PRINT 'Using existing ProductContainsSoftware table'
GO

IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ProductBySoftwareVersion'
)
	CREATE UNIQUE CLUSTERED INDEX ProductBySoftwareVersion
		ON ProductContainsSoftware (SoftwareVersionID, SoftwareProductID) 
		WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX ProductBySoftwareVersion
		ON ProductContainsSoftware (SoftwareVersionID, SoftwareProductID);

IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareVersionByProduct'
)
	CREATE INDEX SoftwareVersionByProduct
		ON ProductContainsSoftware (SoftwareProductID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX SoftwareVersionByProduct
		ON ProductContainsSoftware (SoftwareProductID);
GO

PRINT '--- Policy tables ---';

IF NOT EXISTS (
	SELECT *
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Policy'
	)
BEGIN
	PRINT 'Policy Table:'
	CREATE TABLE Policy (
		GUID		binary(16)
			PRIMARY KEY NOT NULL,
		DomainID	int
			CONSTRAINT FK_Policy_Domain
				REFERENCES Domain(OrganizationID),
		DisplayName	varchar(512) NULL
	)

	/*
	REVISIT: Populate Policy GUIDs and add foreign key constraint:
	-- Populate initial policy GUIDs (without names) from any existing data
	INSERT Policy
		SELECT DISTINCT PolicyGUID, 2, NULL FROM TargetedPackage AS t
			WHERE NOT EXISTS(
				SELECT * FROM Policy AS p WHERE p.GUID = t.GUID
			)
	 */

END -- Policy table
ELSE
	PRINT 'Using existing Policy table'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TargetedPackage'
)
BEGIN
	PRINT 'TargetedPackage table:'
	CREATE TABLE TargetedPackage
	(
		-- Clustered-index fields first:

		-- User or Computer Policy?
		TargetTypeID int NOT NULL
			CONSTRAINT FK_TargetedPackage_TargetType
			REFERENCES TargetType(TargetTypeID),

		-- OU where Target belongs
		TargetOUID int NOT NULL
			CONSTRAINT FK_Target_OU REFERENCES Organization(OrganizationID),

		-- Target Id, FK to UserID or ComputerId (not constrained, sigh)
		TargetID int NOT NULL,

		-- the Policy that applies (not constrained to AD!)
		PolicyGUID binary(16) NOT NULL,

		-- what install action to take (Optional, Install, Remove, etc)
		Action varchar(16) NULL,

		-- The Package applied by the Policy
		PackageFullName nvarchar (256) NOT NULL
			CONSTRAINT FK_TargetedPackage_PackageFullName
			REFERENCES PackageVersion(PackageFullName),

		-- Now the remaining fields:

		-- date and time the policy should be applied
		EffectiveFrom DateTime NULL,

		-- use local time for EffectiveFrom if TRUE. FALSE if 0
		EffectiveFromIsLocal bit DEFAULT(1),

		-- determines whether the target machine should be woken
		-- boolean, FALSE if 0 
		Wake bit DEFAULT(0),

		-- Precedence (order of application)
		Precedence int NOT NULL DEFAULT 0,

		-- Policy Version number from AD
		PolicyVersion int NOT NULL DEFAULT 0,

		-- determines whether to uninstall the package 
		-- when removed from policy. Value from AD.
		-- TRUE if 1. FALSE if 0
		Exclusive bit NOT NULL DEFAULT(0)
	);
END
ELSE
	PRINT 'Using existing TargetedPackage table'
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_TargetedPackage_Policy'
)
BEGIN
	ALTER TABLE TargetedPackage
		ADD CONSTRAINT FK_TargetedPackage_Policy
		FOREIGN KEY (PolicyGUID) REFERENCES Policy(GUID)
END
ELSE
	PRINT 'Using existing FK_TargetedPackage_Policy constraint'
GO

PRINT '	TargetedPackageByOUPackage index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'TargetedPackageByOUPackage'
)
	CREATE UNIQUE CLUSTERED INDEX TargetedPackageByOUPackage
		ON TargetedPackage(
			TargetOUID,		-- For a given OU
			TargetTypeID,		-- Find Computers or Users
			PackageFullName,	-- With a certain package
			TargetID,		-- list the targets
			PolicyGUID		-- Same Package in two policies?
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX TargetedPackageByOUPackage
		ON TargetedPackage(
			TargetOUID,		-- For a given OU
			TargetTypeID,		-- Find Computers or Users
			PackageFullName,	-- With a certain package
			TargetID,		-- list the targets
			PolicyGUID		-- Same Package in two policies?
		);

-- Add this index when it's needed:
--	PRINT '	TargetedPackageByTarget index'
--	IF EXISTS (
--	    SELECT *
--	    FROM sysindexes
--	    WHERE name = 'TargetedPackageByTarget'
--	)
--		CREATE UNIQUE INDEX TargetedPackageByTarget
--			ON TargetedPackage(
--				TargetTypeID,		-- For Computer or User
--				TargetOUID,		-- In a given OU
--				TargetID,		-- With this identity
--				PackageFullName,	-- List the Packages
--				PolicyGUID		-- Same Package in two policies?
--			) WITH DROP_EXISTING;
--	ELSE
--		CREATE UNIQUE INDEX TargetedPackageByTarget
--			ON TargetedPackage(
--				TargetTypeID,		-- For Computer or User
--				TargetOUID,		-- In a given OU
--				TargetID,		-- With this identity
--				PackageFullName,	-- List the Packages
--				PolicyGUID		-- Same Package in two policies?
--			);
GO

-- For some reason SQL Server doesn't allow CREATE VIEW inside an IF statement
PRINT 'Creating ComputerWithDN view:'
IF EXISTS (
	SELECT * FROM sysobjects
	WHERE name = 'ComputerWithDN' AND xtype = 'V'
)
	DROP VIEW ComputerWithDN
GO
CREATE VIEW ComputerWithDN AS
	SELECT ComputerID,
		ComputerCN,
		ComputerOUID,
		OperatingSystem,
		c.GUID,
		('CN='+c.ComputerCN+','+ISNULL(o.RDN,'OU=unknown')+','+d.DN)
			AS DN,
		(ISNULL(o.RDN,'OU=unknown')+','+d.DN) AS OU
	FROM Computer AS c, Organization AS o, Domain AS d
	WHERE c.ComputerOUID = o.OrganizationID
	  AND o.OrganizationID = d.OrganizationID
GO

-- DEFAULT_NAMING_CONTEXT should be substituted, e.g. 'DC=company,DC=com'
PRINT 'Saving Domain Controller name into DatabaseConfiguration table:'
IF EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DomainController'
)
	UPDATE DatabaseConfiguration SET Value = 'DEFAULT_NAMING_CONTEXT'
		WHERE Property = 'DomainController'
ELSE
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DomainController', 'DEFAULT_NAMING_CONTEXT')

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADSDOU'
)
BEGIN
	PRINT 'Active Directory SDOU (Site/Domain/OrganisationalUnit) mirror table:'
	CREATE TABLE ADSDOU (
		-- Domain
		DomainID		int
			NOT NULL,

		-- OU String (DistinguishedName)
		RDN nvarchar(400) NOT NULL,

		-- GUID from Active Directory
		GUID binary(16) NOT NULL
	)
END
ELSE
	PRINT 'Using existing ADSDOU table'
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_ADSDOU_Domain'
)
BEGIN
	PRINT 'Create FK_ADSDOU_Domain constraint' 

	ALTER TABLE ADSDOU
		ADD CONSTRAINT FK_ADSDOU_Domain
			FOREIGN KEY (DomainID) 
				REFERENCES Domain(OrganizationID)
END
ELSE
	PRINT 'Using existing FK_ADSDOU_Domain constraint'
GO

PRINT '	ADSDOUByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADSDOUByName'
)
	CREATE UNIQUE INDEX ADSDOUByName
		ON ADSDOU(DomainID, RDN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX ADSDOUByName
		ON ADSDOU(DomainID, RDN);

PRINT '	ADSDOUByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADSDOUByGUID'
)
	CREATE INDEX ADSDOUByGUID
		ON ADSDOU(GUID) WITH DROP_EXISTING;
ELSE
	CREATE INDEX ADSDOUByGUID
		ON ADSDOU(GUID);
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADComputer'
)
BEGIN
	PRINT 'Active Directory Computer mirror table:'
	CREATE TABLE ADComputer (
		-- Domain
		DomainID		int
			NOT NULL,

		-- RDN of the Computer
		RDN nvarchar(400) NOT NULL,

		-- GUID from Active Directory
		GUID binary(16) NOT NULL
	);
END -- ADComputer table
ELSE
	PRINT 'Using existing ADComputer table'
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_ADComputer_Domain'
)
BEGIN
	PRINT 'Create FK_ADComputer_Domain constraint' 

	ALTER TABLE ADComputer
		ADD CONSTRAINT FK_ADComputer_Domain
			FOREIGN KEY (DomainID) 
				REFERENCES Domain(OrganizationID)
END
ELSE
	PRINT 'Using existing FK_ADComputer_Domain constraint'
GO

PRINT '	ADComputerByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADComputerByName'
)
	CREATE UNIQUE INDEX ADComputerByName
		ON ADComputer(DomainID, RDN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX ADComputerByName
		ON ADComputer(DomainID, RDN);

PRINT '	ComputerByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADComputerByGUID'
)
	CREATE INDEX ADComputerByGUID
		ON ADComputer(GUID) WITH DROP_EXISTING
ELSE
	CREATE INDEX ADComputerByGUID
		ON ADComputer(GUID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADUser'
)
BEGIN
	PRINT 'Active Directory User mirror table:'
	CREATE TABLE ADUser (
		-- Domain
		DomainID		int
			NOT NULL,

		-- RDN of the User
		RDN nvarchar(400) NOT NULL,

		-- GUID from Active Directory
		GUID binary(16) NULL,

		-- Login name, unique in domain
		SAMAccountName varchar(20) NULL
	);
END -- ADUser table
ELSE
	PRINT 'Using existing ADUser table'
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_ADUser_Domain'
)
BEGIN
	PRINT 'Create FK_ADUser_Domain constraint' 

	ALTER TABLE ADUser
		ADD CONSTRAINT FK_ADUser_Domain
			FOREIGN KEY (DomainID) 
				REFERENCES Domain(OrganizationID)
END
ELSE
	PRINT 'Using existing FK_ADUser_Domain constraint'
GO

PRINT '	ADUserByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADUserByName'
)
	CREATE UNIQUE INDEX ADUserByName
		ON ADUser(DomainID, RDN) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE INDEX ADUserByName
		ON ADUser(DomainID, RDN);

PRINT '	ADUserByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ADUserByGUID'
)
	CREATE INDEX ADUserByGUID
		ON ADUser(GUID) WITH DROP_EXISTING
ELSE
	CREATE INDEX ADUserByGUID
		ON ADUser(GUID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareUsagePerWeek'
)
BEGIN
	PRINT 'Application SoftwareUsagePerWeek table:'
	CREATE TABLE SoftwareUsagePerWeek (
		-- The computer this entry refers to. FK to Computer 
		ComputerID int NOT NULL
			CONSTRAINT FK_SoftwareUsagePerWeek_Computer
				REFERENCES Computer(ComputerID),

		-- User context of the session. FK to User 
		UserID int NOT NULL
			CONSTRAINT FK_SoftwareUsagePerWeek_User
				REFERENCES [User](UserID),

		-- ID of the referenced software. FK to SoftwareVersion 
		SoftwareID int NOT NULL
			CONSTRAINT FK_SoftwareUsagePerWeek_Software
				REFERENCES SoftwareVersion(SoftwareID),

		-- The date of the first day of this (this row) week.
		StartOfWeek	datetime NOT NULL,

		-- Duration of usage, in seconds
		Duration	integer,

		-- Forground time seconds
		ActiveTime	integer,

		-- The number of sessions in this week
		Sessions	integer,

		-- The number of distinct days a session was found in this week 
		Days	integer
	);
END -- SoftwareUsagePerWeek table
ELSE
	PRINT 'Using existing SoftwareUsagePerWeek table'
GO

PRINT 'SoftwareUsagePerWeekByCompUser	index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SoftwareUsagePerWeekByCompUser'
)
	CREATE UNIQUE CLUSTERED INDEX SoftwareUsagePerWeekByCompUser
		ON SoftwareUsagePerWeek(
			ComputerID,
			UserID,
			SoftwareID,
			StartOfWeek
		) WITH DROP_EXISTING;
ELSE
	CREATE UNIQUE CLUSTERED INDEX SoftwareUsagePerWeekByCompUser
		ON SoftwareUsagePerWeek(
			ComputerID,
			UserID,
			SoftwareID,
			StartOfWeek
		);
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'UsageDataObsoletePeriod'
)
BEGIN
	PRINT 'Setting default period to obsolete old software usage data'
	-- Default to 26 weeks, 6 months
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('UsageDataObsoletePeriod', '26')
END
ELSE
	PRINT 'Found existing UsageDataObsoletePeriod property in the DatabaseConfiguration table. Using that instead.'
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'UsageDataObsoleteScope'
)
BEGIN
	PRINT 'Setting default scope of deletion when obsoleting old software usage data'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('UsageDataObsoleteScope', '%')
END
ELSE
	PRINT 'Found existing UsageDataObsoleteScope property in the DatabaseConfiguration table. Using that instead.'

GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DNListingDelimiter'
)
BEGIN
	PRINT 'Setting default delimiter for DN listing in UsageDataObsoleteScope'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DNListingDelimiter', ';')
END
ELSE
	PRINT 'Found existing DNListingDelimiter property in the DatabaseConfiguration table. Using that instead.'

GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DNExactMatch'
)
BEGIN
	PRINT 'Setting default DN match option for processing of UsageDataObsoleteScope'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DNExactMatch', '0')
END
ELSE
	PRINT 'Found existing DNExactMatch property in the DatabaseConfiguration table. Using that instead.'

GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ComputerUsage'
)
BEGIN
	PRINT 'Application ComputerUsage table:'
	CREATE TABLE ComputerUsage(
		-- The last time the warehouse received usage
		-- information from a user or managed device.
		-- NULL if never reported.
		LastReported 	datetime,

		-- Computer where the usage was reported.
		-- FK to Computer 
		ComputerID int NOT NULL
			CONSTRAINT FK_ComputerUsage_Computer
				REFERENCES Computer(ComputerID),

		-- User context of the last reported usage.
		-- FK to User 
		UserID int NOT NULL
			CONSTRAINT FK_ComputerUsage_User
				REFERENCES [User](UserID)
	);
END -- ComputerUsage table
ELSE
	PRINT 'Using existing ComputerUsage table'
GO

PRINT '	ReportedUsageByCompUser index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'ReportedUsageByCompUser'
)
	CREATE UNIQUE INDEX ReportedUsageByCompUser
		ON ComputerUsage(ComputerID, UserID) WITH DROP_EXISTING
ELSE
	CREATE UNIQUE INDEX ReportedUsageByCompUser
		ON ComputerUsage(ComputerID, UserID)
GO

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANDistributionJob'
)
BEGIN
	PRINT 'WakeOnLANDistributionJob table:'
	CREATE TABLE WakeOnLANDistributionJob (
		DistJobUID binary (16) PRIMARY KEY NOT NULL,

		TaskUID binary (16) NOT NULL,

		ServerUID binary (16) 
			NOT NULL
			REFERENCES DistributionServer(ServerUID),

		State varchar (16) NOT NULL
	);
END -- WakeOnLANDistributionJob table
ELSE
	PRINT 'Using existing WakeOnLANDistributionJob table'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANStatus'
)
BEGIN
	PRINT 'WakeOnLANStatus table:'
	CREATE TABLE WakeOnLANStatus (
		DistJobUID binary (16) NOT NULL,

		ComputerID int NOT NULL REFERENCES Computer(ComputerID),

		State varchar (16) NOT NULL, 

		CONSTRAINT PK_WakeOnLANStatus PRIMARY KEY CLUSTERED
		(
			DistJobUID,
			ComputerID
		)
	);
END -- WakeOnLANStatus table
ELSE
	PRINT 'Using existing WakeOnLANStatus table'

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANTask'
)
BEGIN
	PRINT 'WakeOnLANTask table:'
	CREATE TABLE WakeOnLANTask (
		TaskUID binary (16) PRIMARY KEY NOT NULL,

		FriendlyName nvarchar (400) NOT NULL,

		StartTime datetime NULL 
	);
END -- WakeOnLANTask table
ELSE
	PRINT 'Using existing WakeOnLANTask table'

PRINT 'Creating WakeOnLANHostInfo view:'

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE name = 'WakeOnLANHostInfo' AND xtype = 'V'
)
	DROP VIEW WakeOnLANHostInfo
GO

CREATE VIEW WakeOnLANHostInfo AS
	SELECT 	Computer.ComputerCN, 
		ip.Value as IP, subnet.Value as NetMask, mac.Value AS MAC
	FROM	Computer,
		HardwareProperty as ip,
		HardwareProperty as mac,
		HardwareProperty as subnet,
		HardwareProperty as gateway
	WHERE 	ip.Property='IPAddress' 
  	AND 	mac.Property='MACAddress'
  	AND 	mac.ComputerID = ip.ComputerID
  	AND 	mac.HardwareName = ip.HardwareName
  	AND 	mac.Occurrence = ip.Occurrence
  	AND 	subnet.Property='IPSubnet'
  	AND 	subnet.ComputerID = ip.ComputerID
  	AND 	subnet.HardwareName = ip.HardwareName
  	AND 	subnet.Occurrence = ip.Occurrence
  	AND 	gateway.Property='DefaultIPGateway'
  	AND 	gateway.ComputerID = ip.ComputerID
  	AND 	gateway.HardwareName = ip.HardwareName
  	AND 	gateway.Occurrence = ip.Occurrence
  	AND 	Computer.ComputerID = ip.ComputerID
GO

-- For some reason SQL Server doesn't allow CREATE VIEW inside an IF statement
PRINT 'Creating SDOU view:'
IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'SDOU'
)
	DROP VIEW SDOU
GO

CREATE VIEW SDOU AS
	SELECT	o.OrganizationID as OUID,
		CASE
		    WHEN RDN IS NULL THEN	-- Unknown OU in this domain
			'<NULL>,'+d.DN		-- or the universal domain
		    WHEN RDN = '' THEN
		    	d.DN			-- DomainDN for the domain
		    ELSE
		    	o.RDN+','+d.DN		-- Ordinary OU or container
		END AS OU,
		GUID
	FROM Organization AS o, Domain AS d
	WHERE o.DomainID = d.OrganizationID
GO



PRINT 'Creating ReverseDN stored procedure'
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
        AND [name] = 'ReverseDN'
)
        DROP PROCEDURE ReverseDN
GO
CREATE PROCEDURE ReverseDN 
	@dn nvarchar(400),
	@dn_reverse nvarchar(400) OUTPUT
AS

	DECLARE @not_end_of_dn bit
	DECLARE @end int
	DECLARE @start int
	DECLARE @dnreverse nvarchar(400)

	SET @dnreverse = ''
	SET @not_end_of_dn = 1
	SET @start = 1 

	WHILE (@not_end_of_dn = 1)
	BEGIN
		-- search for comma
		SET @end = CHARINDEX(',', @dn, @start)
		IF @end = 0
		BEGIN
			-- reach the end of string
			SET @not_end_of_dn = 0
			IF @start = 1
				SET @dnreverse = SUBSTRING(
						@dn, @start, LEN(@dn)-@start+1
					) + @dnreverse
			ELSE
				SET @dnreverse = SUBSTRING(
						@dn, @start, LEN(@dn)-@start+1
					) + ',' + @dnreverse

			BREAK
		END

		-- if there is a \ before comma, keep searching for comma
		-- that indicate the actual separator
		WHILE (SUBSTRING(@dn, @end - 1, 1) = '\')
		BEGIN
			SET @end = CHARINDEX(',', @dn, @end + 1)
			IF @end = 0
			BEGIN
				-- reach the end of string
				SET @not_end_of_dn = 0

				-- set @end to length+1, as it is used as
				-- the index of the separator which logically
				-- appears one character after the end
				SET @end = LEN(@dn) + 1
				BREAK
			END
		END

		-- retrieve the string between the last search and the comma
		IF @start = 1 
			SET @dnreverse = SUBSTRING(@dn, @start, @end-@start)
					+ @dnreverse
		ELSE
			SET @dnreverse = SUBSTRING(@dn, @start, @end-@start)
					+ ',' + @dnreverse

		SET @start = @end + 1
	END

	SET @dn_reverse = @dnreverse
GO

PRINT 'Creating Organization table triggers'
PRINT '	ReverseOrganizationDN'
IF EXISTS (SELECT name FROM sysobjects WHERE type = 'TR'
      AND name = 'ReverseOrganizationDN'
)
	DROP TRIGGER ReverseOrganizationDN
GO
CREATE TRIGGER ReverseOrganizationDN 
	ON Organization FOR INSERT, UPDATE
AS 
	DECLARE @rdn nvarchar(400)
	DECLARE @rdnreverse nvarchar(400)

	IF @@ROWCOUNT = 1
	BEGIN
		IF UPDATE (RDN) 
		BEGIN
			SELECT @rdn = RDN FROM inserted
			IF @rdn IS NOT NULL
			BEGIN
				EXEC ReverseDN @rdn, @rdnreverse OUTPUT

				UPDATE Organization 
				SET RDNReverse = @rdnreverse
				WHERE RDN = @rdn
			END
		END
	END
	ELSE
	BEGIN
		DECLARE rdn_cursor CURSOR FOR 
		SELECT RDN
		FROM inserted
		WHERE RDN IS NOT NULL

		OPEN rdn_cursor

		FETCH NEXT FROM rdn_cursor 
		INTO @rdn

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC ReverseDN @rdn, @rdnreverse OUTPUT

			UPDATE Organization 
			SET RDNReverse = @rdnreverse
			WHERE RDN = @rdn

			FETCH NEXT FROM rdn_cursor 
			INTO @rdn
		END

		CLOSE rdn_cursor
		DEALLOCATE rdn_cursor
	END
GO

PRINT 'Creating Domain table triggers:'
PRINT '	ReverseDomainDN'
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ReverseDomainDN' AND type = 'TR'
)
	DROP TRIGGER ReverseDomainDN
GO
CREATE TRIGGER ReverseDomainDN 
	ON Domain FOR INSERT, UPDATE
AS 
	DECLARE @dn nvarchar(100)
	DECLARE @dnreverse nvarchar(100)

	IF @@ROWCOUNT = 1
	BEGIN
		IF UPDATE (DN) 
		BEGIN
			SELECT @dn = DN FROM inserted

			IF @dn IS NOT NULL
			BEGIN
				EXEC ReverseDN @dn, @dnreverse OUTPUT

				UPDATE Domain 
				SET DNReverse = @dnreverse
				WHERE DN = @dn
			END
		END
	END
	ELSE
	BEGIN
		DECLARE dn_cursor CURSOR FOR 
		SELECT DN
		FROM inserted
		WHERE DN IS NOT NULL

		OPEN dn_cursor

		FETCH NEXT FROM dn_cursor 
		INTO @dn

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC ReverseDN @dn, @dnreverse OUTPUT

			UPDATE Domain 
			SET DNReverse = @dnreverse
			WHERE DN = @dn

			FETCH NEXT FROM dn_cursor 
			INTO @dn
		END

		CLOSE dn_cursor
		DEALLOCATE dn_cursor
	END	

GO

-- This constraint has be created after Domain entry with DomainID = 1
-- is created for upgrade purposes. DomainID is a new column and during
-- upgrade, we initialise it to 1 since Organization.DomainID is a
-- foreign key of Domain.OrganizationID, an entry with DomainID = 1 has to exist.
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_Organization_Domain'
)
BEGIN
	PRINT 'Create FK_Organization_Domain constraint' 

	ALTER TABLE Organization WITH NOCHECK
		ADD CONSTRAINT FK_Organization_Domain
			FOREIGN KEY (DomainID) 
				REFERENCES Domain(OrganizationID)
END
ELSE
	PRINT 'Using existing FK_Organization_Domain constraint'
GO

PRINT '	DomainCreate'
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
        AND [name] = 'DomainCreate'
)
        DROP PROCEDURE DomainCreate
GO
CREATE PROCEDURE DomainCreate 
	@dn nvarchar(100),
	@domain_type varchar(4) = NULL
AS

	DECLARE @OrganizationID int

	-- Disable the foreign key constraint so we can create the new entry:
	ALTER TABLE Organization NOCHECK CONSTRAINT FK_Organization_Domain

	-- Make an Organization entry for the Domain (allocate the ID):
	INSERT INTO Organization (RDN) VALUES ('')

	-- Find the ID and set the DomainID to the same:
	SELECT @OrganizationID = OrganizationID 
		FROM Organization 
		WHERE RDN = '' AND DomainID = 0
	UPDATE Organization
		SET DomainID = @OrganizationID 
		WHERE OrganizationID = @OrganizationID

	-- Insert the matching DomainID
	INSERT INTO Domain (OrganizationID, DN, DomainType)
		VALUES (@OrganizationID, @dn, @domain_type)

	-- Re-enable the constraint
	ALTER TABLE Organization CHECK CONSTRAINT FK_Organization_Domain

	-- Insert the "unknown" OU for the domain, if not first domain
	IF (@dn IS NOT NULL)
		INSERT INTO Organization (DomainID) VALUES (@OrganizationID)

	RETURN @OrganizationID
GO

PRINT 'Creating the universal domain/organization and the SYSTEM user'
-- Organization:
IF NOT EXISTS(SELECT * FROM Organization WHERE OrganizationID = 1)
BEGIN	-- During migration, the unknown OU will be used, only create the Domain
	ALTER TABLE Organization NOCHECK CONSTRAINT FK_Organization_Domain
	INSERT INTO Organization (RDN, DomainID) VALUES ('', 1)
	ALTER TABLE Organization CHECK CONSTRAINT FK_Organization_Domain
END

-- Create the universal Domain:
IF NOT EXISTS(SELECT * FROM Domain WHERE DN IS NULL)
	INSERT INTO Domain (OrganizationID, DN) VALUES (1, NULL)

-- Create the SYSTEM User if we have none already
IF NOT EXISTS(SELECT * FROM [User] WHERE UserCN = 'SYSTEM')
	INSERT INTO [User] (UserOUID, UserCN) VALUES (1, 'SYSTEM')

IF 'DEFAULT_NAMING_CONTEXT' <> ('DEFAULT_'+'NAMING_CONTEXT')
AND NOT EXISTS(SELECT * FROM Domain WHERE DN = 'DEFAULT_NAMING_CONTEXT')
BEGIN
	PRINT 'Creating the default domain'
	EXEC DomainCreate 'DEFAULT_NAMING_CONTEXT', 'AD'
	UPDATE Domain
		SET	ADReconcile = 1,
			MergePolicies = 1
		WHERE DN = 'DEFAULT_NAMING_CONTEXT'
END

USE master
GO
