DROP VIEW ComplianceMeterView
GO

CREATE VIEW ComplianceMeterView AS
SELECT
	ComplianceMeter.ID AS ID,
	ComplianceMeter.MeterID AS MeterID,
	ComplianceMeter.Active AS Active,
    ComplianceMeter.Reviewed AS Reviewed,
	Meter.AssetKey AS AssetKey,
    Meter.Name AS Name,
	Meter.Make AS Make,
	Meter.Model AS Model,
	(
    SELECT CASE WHEN ComplianceMeter.Active = 1 AND ComplianceMeter.Reviewed = 1 THEN
		ISNULL((SELECT CS.ID FROM ComplianceState CS WHERE CS.Priority = (SELECT MAX(CS1.Priority) FROM ComplianceRecordView CR LEFT JOIN ComplianceState CS1 ON CS1.ID = CR.Status AND CR.MeterId = ComplianceMeter.ID)),(SELECT ID FROM ComplianceState WHERE Description = 'In Compliance'))
	ELSE
		(SELECT ID FROM ComplianceState WHERE Description = 'Inactive')
	END) AS StatusID,
	(
	SELECT CASE WHEN ComplianceMeter.Active = 1 AND ComplianceMeter.Reviewed = 1 THEN
		ISNULL((SELECT CS.Description FROM ComplianceState CS WHERE CS.Priority = (SELECT MAX(CS1.Priority) FROM ComplianceRecordView CR LEFT JOIN ComplianceState CS1 ON CS1.ID = CR.Status AND CR.MeterId = ComplianceMeter.ID)),'In Compliance')
	ELSE
		(SELECT 'Inactive')
	END
	) AS Status,
	(SELECT MAX(ComplianceRecordView.Timer) FROM ComplianceRecordView WHERE Status NOT IN (SELECT ID FROM ComplianceState WHERE Description = 'In Compliance') AND ComplianceRecordView.MeterId = ComplianceMeter.ID) AS Timer
	FROM ComplianceMeter LEFT JOIN 
	Meter ON Meter.ID = ComplianceMeter.MeterID
GO

CREATE TABLE ComplianceOperation
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FilePattern VARCHAR(500) NOT NULL,
    AssemblyName VARCHAR(200) NOT NULL,
    TypeName VARCHAR(200) NOT NULL,
    LoadOrder INT NOT NULL
)
GO

INSERT INTO ComplianceOperation(FilePattern, AssemblyName, TypeName, LoadOrder) VALUES('**\Config\*.ini','MiMD.exe', 'MiMD.FileParsing.ComplianceOperation.INIParser', 0)
GO

INSERT INTO ComplianceOperation(FilePattern, AssemblyName, TypeName, LoadOrder) VALUES('**\Config\*.par','MiMD.exe', 'MiMD.FileParsing.ComplianceOperation.PARParser', 0)
GO

INSERT INTO DataOperation(AssemblyName, TypeName, LoadOrder) VALUES('MiMD.exe', 'MiMD.FileParsing.DataOperations.PRC002Operation', 6)
GO