/*
 * $Id: schema60.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)
 *
 * 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.0');
END -- Configuration table
ELSE
	PRINT 'Using existing DatabaseConfiguration table'

PRINT '--- Directory tables ---'

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

	CREATE TABLE SDOU (
		-- Auto generated identity number
		OUID int NOT NULL IDENTITY
			CONSTRAINT PK_SDOU PRIMARY KEY CLUSTERED,

		-- OU String (DistinguishedName)
		OU varchar(800) NULL
	);

	CREATE UNIQUE INDEX SDOUByName
		ON SDOU(OU);

	INSERT INTO SDOU (OU) VALUES (NULL);
END -- SDOU table
ELSE
	PRINT 'Using existing SDOU table'

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 SDOU(OUID)
	);

	PRINT '	UserByName index'
	CREATE UNIQUE CLUSTERED INDEX UserByName
		ON [User] (UserOUID, UserCN);

	INSERT INTO [User] (UserOUID, UserCN) VALUES (1, 'SYSTEM');
END -- User table
ELSE
	PRINT 'Using existing User table'

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 SDOU(OUID),

		OperatingSystem varchar(128) NULL
	);

	PRINT '	ComputerByName index'
	CREATE UNIQUE CLUSTERED INDEX ComputerByName
		ON Computer (ComputerOUID, ComputerCN);

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

/* 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');
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 SDOU(OUID),

		-- 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
	);

	PRINT '	InstallationStatusByCompPkgVer index'
	CREATE UNIQUE CLUSTERED INDEX InstallationStatusByCompPkgVer
		ON InstallationStatus(
			OUID,		-- group by OU the targets with package
			ComputerID,
			PackageName,
			Version,
			UserID
		);

	PRINT '	InstallationStatusByPackage index'
	CREATE INDEX InstallationStatusByPackage
		ON InstallationStatus(
			PackageName,
			OUID,		-- group by OU the targets with package
			ComputerID
		);

	PRINT '	InstallationStatusByUser index'
	CREATE INDEX InstallationStatusByUser
		ON InstallationStatus(
			OUID,	-- group users by OU
			UserID
		);
END -- InstallationStatus
ELSE
	PRINT 'Using existing InstallationStatus table'

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
	);

	PRINT '	InstallationHistoryByCompPkgWhen index'
	CREATE UNIQUE CLUSTERED INDEX InstallationHistoryByCompPkgWhen
		ON InstallationHistory(
			UID,
			ComputerID,
			PackageName,
			Reported
		);
END -- InstallationHistory
ELSE
	PRINT 'Using existing InstallationHistory table'

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
	);

	PRINT '	InventoryReport index'
	CREATE UNIQUE INDEX InventoryReportByComputer
		ON InventoryReport(
			ComputerID,
			UserID
		);
END -- InventoryReport table
ELSE
	PRINT 'Using existing InventoryReport table'

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
	);

	PRINT '	SoftwareVersionByName index'
	CREATE UNIQUE CLUSTERED INDEX SoftwareVersionByName
		ON SoftwareVersion (SoftwareName, Version);
END
ELSE
	PRINT 'Using existing SoftwareVersion table'

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 SDOU(OUID),

		-- 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 SDOU(OUID),

		-- 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
	);

	PRINT '	SoftwareBySWComputer index'
	CREATE UNIQUE CLUSTERED INDEX SoftwareOccurrenceBySWComputer
		ON SoftwareOccurrence(
			SoftwareId,
			ComputerOUID,
			ComputerID,
			UserID
		);

	PRINT '	SoftwareByComputer index'
	CREATE INDEX [SoftwareByComputer]
		ON SoftwareOccurrence(
			ComputerOUID,
			ComputerID,
			UserID,
			SoftwareId
		);

	-- REVISIT: Do we need indices by User or UserOU here?

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

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 SDOU(OUID),

		-- 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
	);

	PRINT '	SoftwareFileByComputer index'
	CREATE CLUSTERED INDEX SoftwareFileByComputer
		ON SoftwareFile(
			ComputerOUID,
			ComputerID,
			SoftwareId
		)
END -- SoftwareFile table
ELSE
	PRINT 'Using existing SoftwareFile table'

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]
		)
	);

	PRINT '	UnknownFileByComputer index'
	CREATE CLUSTERED INDEX UnknownFileByComputer
		ON UnknownFile(
			ComputerID,
			MD5
		);
END -- UnknownFile table
ELSE
	PRINT 'Using existing UnknownFile table'

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 SDOU(OUID),

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

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

		-- Hardware object class
		Class varchar (256) NULL
	);

	PRINT '	HardwareObjByComputer index'
	CREATE UNIQUE CLUSTERED INDEX HardwareObjByComputer
		ON HardwareObject(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence
		);

	PRINT '	HardwareObjByClassName index'
	CREATE INDEX HardwareObjByClassName
		ON HardwareObject(
			Class,
			HardwareName,
			Occurrence
		)
END -- HardwareObject table
ELSE
	PRINT 'Using existing HardwareObject table'

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 SDOU(OUID),

		-- 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
	);

	PRINT '	HardwarePropByName index'
	CREATE UNIQUE CLUSTERED INDEX HardwarePropByName
		ON HardwareProperty(
			ComputerOUID,
			ComputerID,
			HardwareName,
			Occurrence,
			Property
		);
END -- HardwareProperty table
ELSE
	PRINT 'Using existing HardwareProperty table'

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
	);

	PRINT '	PackageVersionByName index'
	CREATE INDEX PackageVersionByName
		ON PackageVersion(PackageName, Version);
END -- PackageVersion table
ELSE
	PRINT 'Using existing PackageVersion table'

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

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 LicensePricing, 'NVARCHAR(256)', 'NOT NULL'
EXEC sp_addtype LicenseModel, 'NVARCHAR(256)', 'NOT NULL'
EXEC sp_addtype LicenseAgreement, 'NVARCHAR(256)', 'NOT NULL'
EXEC sp_addtype SoftwarePublisher, 'NVARCHAR(256)', 'NOT NULL'
EXEC sp_addtype SoftwareReseller, 'NVARCHAR(256)', 'NOT NULL'
*/

/* 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 how the Product is priced
		Pricing nvarchar(256) NOT NULL,

		-- Text describing the Licensing model for the Product
		Model nvarchar(256) NOT NULL,

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

		-- Name of publisher (author)
		Publisher nvarchar(256) NOT NULL,

		-- Name of vendor/reseller, to correlate with purchase/order
		Reseller nvarchar(256) NOT NULL,

		-- Name of License agreement
		Agreement nvarchar(256) NOT NULL
	);

	PRINT '	SoftwareProductByName index'
	CREATE UNIQUE INDEX SoftwareProductByName
	ON SoftwareProduct (ProductName);

	PRINT '	SoftwareProductByPublisher index'
	CREATE INDEX SoftwareProductByPublisher
	ON SoftwareProduct (Publisher);
END /* SoftwareProduct */
ELSE
	PRINT 'Using existing SoftwareProduct table'

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

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

		-- ID of the OrganisationalUnit that owns the product
		OUID int NOT NULL
			CONSTRAINT FK_Ownership_OUID REFERENCES SDOU(OUID),

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

		-- When do these licenses expire?
		Expiry		DateTime
	);

	PRINT '	OwnershipByOU index'
	CREATE INDEX OwnershipByOU
	ON Ownership (OUID, SoftwareProductID);

	PRINT '	OwnershipBySoftwareProduct index'
	CREATE INDEX OwnershipBySoftwareProduct
	ON Ownership (SoftwareProductID, OUID);

END /* ownership of licenses by OU's table */
ELSE
	PRINT 'Using existing Ownership table'

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)
	)

	CREATE UNIQUE CLUSTERED INDEX ProductBySoftwareVersion
	ON ProductContainsSoftware (SoftwareVersionID, SoftwareProductID);

	CREATE INDEX SoftwareVersionByProduct
	ON ProductContainsSoftware (SoftwareProductID);

END
ELSE
	PRINT 'Using existing ProductContainsSoftware table'

GO

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

IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TargetedPackage'
)
BEGIN
	PRINT 'TargetedPackage table:'
	CREATE TABLE TargetedPackage
	(
		-- 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 SDOU(OUID),

		-- 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)
	);

	PRINT '	TargetedPackageByOUPackage index'
	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'
--	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?
--		);

END
ELSE
	PRINT 'Using existing TargetedPackage table'

GO

USE master
GO
