/* 
 * TABLE: [DeviceExtendedProperties] 
 */

CREATE TABLE [DeviceExtendedProperties](
    [DeviceGuid]                    uniqueidentifier    NOT NULL,
    [SupportHibernate]              bit                 NULL,
    [SupportSleep]                  bit                 NULL,
    [IsDetectedAsVM]                bit                 NOT NULL,
    [IsOSPowerManagementSupported]  bit                 NOT NULL,
    CONSTRAINT [PK_DeviceExtendedProperties] PRIMARY KEY  ([DeviceGuid])
)
go



/* 
 * TABLE: [PowerManagedDevices] 
 */

CREATE TABLE [PowerManagedDevices](
    [DeviceGuid]                      uniqueidentifier    NOT NULL,
    [IpAddress]                       nvarchar(64)        NOT NULL,
    [DeviceName]                      nvarchar(512)       NOT NULL,
    [LastPowerDataCollection]         datetime            NOT NULL,
	[LastPowerDataCollectionAttempt]  datetime            NOT NULL,
    [LastEventCollection]             datetime            NOT NULL,
	[LastEventCollectionAttempt]      datetime            NOT NULL,
    [LastEventProcessed]              int                 NULL,
    [LastStateChangeProcessed]        int                 NULL,
    [LastPowerStatisticTime]          datetime            NULL,   
	[IsPowerManaged]                  bit                 NOT NULL,
    [UtcOffset]                       int                 NOT NULL,
    [OSVersion]                       nvarchar(32)        NULL,
    [IsVM]                            bit                 NOT NULL,    
	[WmiError]					      int			      Not NULL,
	[PendingDelete]					  bit				  Not Null,
    CONSTRAINT [PK_PowerManagedDevices] PRIMARY KEY  ([DeviceGuid])
)
go



/* 
 * TABLE: [PowerManagementEvents] 
 */

CREATE TABLE [PowerManagementEvents](
    [DeviceGuid]   uniqueidentifier    NOT NULL,
    [Id]           int                 IDENTITY(1,1),
    [EventCode]         int            NOT NULL,
    [SourceName]   nvarchar(4000)      NOT NULL,
    [Message]      nvarchar(4000)      NOT NULL,
    [TimeWritten]  datetime            NOT NULL,
    CONSTRAINT [PK_PowerManagementEvents] PRIMARY KEY  ([DeviceGuid], [Id])
)
go



/* 
 * TABLE: [PowerPlanConfigurationDeviceLinks] 
 */

CREATE TABLE [PowerPlanConfigurationDeviceLinks](
    [DeviceGuid]                  uniqueidentifier    NOT NULL,
    [PowerPlanConfigurationGuid]  uniqueidentifier    NOT NULL,
    CONSTRAINT [PK_PowerPlanConfigurationDeviceLinks] PRIMARY KEY  ([DeviceGuid], [PowerPlanConfigurationGuid])
)
go



/* 
 * TABLE: [PowerPlanConfigurations] 
 */

CREATE TABLE [PowerPlanConfigurations](
    [PowerPlanConfigurationGuid]  uniqueidentifier    NOT NULL,
    CONSTRAINT [PK_PowerPlanConfigurations] PRIMARY KEY  ([PowerPlanConfigurationGuid])
)
go



/* 
 * TABLE: [PowerPlanConfigurationSettings] 
 */

CREATE TABLE [PowerPlanConfigurationSettings](
    [PowerPlanConfigurationGuid]  uniqueidentifier    NOT NULL,
    [PowerSettingGuid]            uniqueidentifier    NOT NULL,
    [PropertyValueOnBattery] bigint  NOT NULL,
    [PropertyValue] bigint  NOT NULL,
    [IsValueManaged] bit  NOT NULL,
    [IsValueOnBatteryManaged] bit  NOT NULL,
    CONSTRAINT [PK_PowerPlanConfigurationSettings] PRIMARY KEY  ([PowerPlanConfigurationGuid], [PowerSettingGuid])
)
go

/* 
 * TABLE: [PowerPlans] 
 */

CREATE TABLE [PowerPlans](
    [DeviceGuid]     uniqueidentifier    NOT NULL,
    [PowerPlanGuid]  uniqueidentifier    NOT NULL,
    [IsActive]       bit                 NOT NULL,
    [PowerPlanName]  nvarchar(1000)      NOT NULL,
    [Description]    nvarchar(2000)      NULL,
    CONSTRAINT [PK_PowerPlans] PRIMARY KEY  ([DeviceGuid], [PowerPlanGuid])
)
go



/* 
 * TABLE: [PowerSettingDataIndexes] 
 */

CREATE TABLE [PowerSettingDataIndexes](
    [DeviceGuid]         uniqueidentifier    NOT NULL,
    [PowerPlanGuid]      uniqueidentifier    NOT NULL,
    [PowerSettingGuid]   uniqueidentifier    NOT NULL,
    [IsOnBattery]        bit                 NOT NULL,
    [SettingManagedBy]   int                 NOT NULL,
    [SettingIndexValue]  bigint              NOT NULL,
    CONSTRAINT [PK_PowerSettingDataIndexes] PRIMARY KEY  ([DeviceGuid], [PowerPlanGuid], [PowerSettingGuid], [IsOnBattery])
)
go



/* 
 * TABLE: [PowerSettings] 
 */

CREATE TABLE [PowerSettings](
    [PowerSettingGuid]          uniqueidentifier    NOT NULL,
    [ElementName]               nvarchar(1000)      NOT NULL,
    [Description]               nvarchar(3000)      NOT NULL,
    [MinWinNTVersionSupported]  nvarchar(64)        NOT NULL,
    CONSTRAINT [PK_PowerSettings] PRIMARY KEY  ([PowerSettingGuid])
)
go



/* 
 * TABLE: [PowerStateChanges] 
 */

CREATE TABLE [PowerStateChanges](
    [DeviceGuid]            uniqueidentifier    NOT NULL,
    [Id]                    int                 IDENTITY(1,1),
    [PowerState]            int                 NOT NULL,
    [IsUnExpectedShutdown]  bit                 NOT NULL,
    [StateChangeTime]       datetime            NOT NULL,
    CONSTRAINT [PK_PowerStateChanges] PRIMARY KEY  ([DeviceGuid], [Id])
)
go



/* 
 * TABLE: [PowerStatistics] 
 */

CREATE TABLE [PowerStatistics](
    [DeviceGuid]          uniqueidentifier    NOT NULL,
    [DateOfData]          datetime            NOT NULL,
    [LocalHourOfData]     int                 NOT NULL,
    [SecondsUp]           int                 NOT NULL,
    [SecondsDown]         int                 NOT NULL,
    [SecondsAsleep]       int                 NOT NULL,
    [SecondsUnknown]      int                 NOT NULL,
    [UnexpectedShutdown]  int                 NOT NULL,
    CONSTRAINT [PK_PowerStatistics] PRIMARY KEY  ([DeviceGuid], [DateOfData])
)
go



/* 
 * INDEX: [PowerPlanConfigurationDeviceLinks_PowerPlanConfigurationGuid_IX] 
 */

CREATE INDEX [PowerPlanConfigurationDeviceLinks_PowerPlanConfigurationGuid_IX] ON [PowerPlanConfigurationDeviceLinks]([PowerPlanConfigurationGuid])
go
/* 
 * INDEX: [PowerPlanConfigurationSettings_PowerSettingGuid_IX] 
 */

CREATE INDEX [PowerPlanConfigurationSettings_PowerSettingGuid_IX] ON [PowerPlanConfigurationSettings]([PowerSettingGuid])
go
/* 
 * INDEX: [PowerSettingDataIndexes_PowerSettingGuid_IX] 
 */

CREATE INDEX [PowerSettingDataIndexes_PowerSettingGuid_IX] ON [PowerSettingDataIndexes]([PowerSettingGuid])
go
/* 
 * TABLE: [DeviceExtendedProperties] 
 */

ALTER TABLE [DeviceExtendedProperties] ADD CONSTRAINT [DeviceExtendedProperties_PowerManagedDevices_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [PowerManagedDevices]([DeviceGuid])
go


/* 
 * TABLE: [PowerManagementEvents] 
 */

ALTER TABLE [PowerManagementEvents] ADD CONSTRAINT [PowerManagementEvents_PowerManagedDevices_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [PowerManagedDevices]([DeviceGuid])
go


ALTER TABLE [PowerPlanConfigurationDeviceLinks] ADD CONSTRAINT [PowerPlanConfigurationDeviceLinks_PowerPlanConfigurations_FK] 
    FOREIGN KEY ([PowerPlanConfigurationGuid])
    REFERENCES [PowerPlanConfigurations]([PowerPlanConfigurationGuid])
go


/* 
 * TABLE: [PowerPlanConfigurationSettings] 
 */

ALTER TABLE [PowerPlanConfigurationSettings] ADD CONSTRAINT [PowerPlanConfigurationSettings_PowerPlanConfigurations_FK] 
    FOREIGN KEY ([PowerPlanConfigurationGuid])
    REFERENCES [PowerPlanConfigurations]([PowerPlanConfigurationGuid])
go

ALTER TABLE [PowerPlanConfigurationSettings] ADD CONSTRAINT [PowerPlanConfigurationSettings_PowerSettings_FK] 
    FOREIGN KEY ([PowerSettingGuid])
    REFERENCES [PowerSettings]([PowerSettingGuid])
go


/* 
 * TABLE: [PowerPlans] 
 */

ALTER TABLE [PowerPlans] ADD CONSTRAINT [PowerPlans_PowerManagedDevices_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [PowerManagedDevices]([DeviceGuid])
go


/* 
 * TABLE: [PowerSettingDataIndexes] 
 */

ALTER TABLE [PowerSettingDataIndexes] ADD CONSTRAINT [PowerSettingDataIndexes_PowerPlans_FK] 
    FOREIGN KEY ([DeviceGuid], [PowerPlanGuid])
    REFERENCES [PowerPlans]([DeviceGuid], [PowerPlanGuid])
	ON DELETE CASCADE
go

ALTER TABLE [PowerSettingDataIndexes] ADD CONSTRAINT [PowerSettingDataIndexes_PowerSettings_FK] 
    FOREIGN KEY ([PowerSettingGuid])
    REFERENCES [PowerSettings]([PowerSettingGuid])
go


/* 
 * TABLE: [PowerStateChanges] 
 */

ALTER TABLE [PowerStateChanges] ADD CONSTRAINT [PowerStateChanges_PowerManagedDevices_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [PowerManagedDevices]([DeviceGuid])
go


/* 
 * TABLE: [PowerStatistics] 
 */

ALTER TABLE [PowerStatistics] ADD CONSTRAINT [PowerStatistics_PowerManagedDevices_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [PowerManagedDevices]([DeviceGuid])
go


