DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10391
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Number of Cloud Libraries] int,[Total Size] bigint,[Min Retention] int,[Max Retention] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @tblLib TABLE (LibId INT, sizeOnLibGB BIGINT, MinRetention INT, MaxRetention INT)

INSERT INTO @tblLib
SELECT DISTINCT CLOUD_MP.LibraryId, 0, 0, 0
FROM MMSDSStoragePool SDS WITH (READUNCOMMITTED)
       INNER JOIN MMMountPath MP WITH (READUNCOMMITTED) ON SDS.LibraryId = MP.LibraryId
       INNER JOIN MMMountPathToStorageDevice MPSD WITH (READUNCOMMITTED) ON MP.MountPathId = MPSD.MountPathId
       INNER JOIN MMDeviceController DEVC WITH (READUNCOMMITTED) ON MPSD.DeviceId = DEVC.DeviceId
       INNER JOIN MMDeviceController DEVCLOUD WITH (READUNCOMMITTED) ON DEVC.ClientId = DEVCLOUD.ClientId
       INNER JOIN MMDevice CLOUD_DEV WITH (READUNCOMMITTED) ON DEVCLOUD.DeviceId = CLOUD_DEV.DeviceId
       INNER JOIN MMMountPathToStorageDevice CLOUD_MPSD WITH (READUNCOMMITTED) ON CLOUD_DEV.DeviceId = CLOUD_MPSD.DeviceId
       INNER JOIN MMMountPath CLOUD_MP WITH (READUNCOMMITTED) ON CLOUD_MPSD.MountPathId = CLOUD_MP.MountPathId 
WHERE DEVCLOUD.DeviceId <> MPSD.DeviceId
AND CLOUD_DEV.DeviceTypeId >=  2 -- TYPE_SAN_MAGNETIC_REMOTE_HOST_START 2
AND CLOUD_DEV.DeviceTypeId <=  1000 -- TYPE_SAN_MAGNETIC_REMOTE_HOST_END   1000
AND CLOUD_MP.Attribute & 32 > 0 --MNTPTH_ATTRIB_ENABLE_CLOUD_PRUNING  
AND DEVC.DeviceAccessType & 16 = 0 --DATASERVER_IP_DEVICEACCESS
AND DEVCLOUD.UseCount & 1 > 0 --MM_DEVCTRL_PREFERRED_PRUNING
AND EXISTS(SELECT 1 FROM MMVolume V WITH(READUNCOMMITTED) WHERE V.MediaSideId = CLOUD_MP.MediaSideId AND V.SIDBStoreId > 0)

-- Skip archive libraries
IF OBJECT_ID(''tempdb.dbo.#tmpArchNumDevType'') is not null DROP TABLE #tmpArchNumDevType
CREATE TABLE #tmpArchNumDevType(
	DeviceId		INTEGER,
	ArchiveNumber	INTEGER,
	DeviceTypeId	INTEGER,
	LibraryId		INTEGER
)

INSERT INTO #tmpArchNumDevType
SELECT MDC.DeviceId,
	CASE WHEN MDC.UserName LIKE ''%@%//%'' THEN
		CAST(SUBSTRING(MDC.UserName, CHARINDEX(''@'', MDC.UserName, 0)+1, (CHARINDEX(''//'', MDC.UserName, 0)-1 - CHARINDEX(''@'', MDC.UserName, 0))) AS int)
	ELSE
		0
	END AS ArchiveNumber,
	MD.DeviceTypeId,
	MP.LibraryId
FROM MMDeviceController MDC WITH (READUNCOMMITTED)
	INNER JOIN MMDevice MD WITH (READUNCOMMITTED) ON MD.DeviceId = MDC.DeviceId
	INNER JOIN MMMountPathToStorageDevice MPSD WITH (READUNCOMMITTED) ON MPSD.DeviceId = MD.DeviceId
	INNER JOIN MMMountPath MP WITH (READUNCOMMITTED) ON MP.MountPathId = MPSD.MountPathId
WHERE ((MDC.UserName like ''%@%//%''AND MD.DeviceTypeId in (23, 2, 3)) OR (MD.DeviceTypeId in (22, 28)))

DELETE T
FROM @tblLib T WHERE 
EXISTS 
(
	SELECT 1 FROM #tmpArchNumDevType TMP WITH (READUNCOMMITTED)
	WHERE TMP.LibraryId = T.LibId AND
	(
		(TMP.ArchiveNumber = 8 AND TMP.DeviceTypeId = 23)
		OR ((TMP.ArchiveNumber = 8 OR TMP.ArchiveNumber = 16) AND TMP.DeviceTypeId = 2)
		OR (TMP.ArchiveNumber = 3 AND TMP.DeviceTypeId = 3)
		OR (TMP.DeviceTypeId = 28)
		OR (TMP.DeviceTypeId = 22)	
	)
)

IF OBJECT_ID(''tempdb.dbo.#tmpArchNumDevType'') is not null DROP TABLE #tmpArchNumDevType

UPDATE T
SET sizeOnLibGB = TMP.sizeInGB
FROM @tblLib T
     INNER JOIN
     (
		SELECT T.LibId, SUM(V.PhysicalBytesMB) / (1024.0) AS sizeInGB
        FROM @tblLib T
			INNER JOIN MMMountPath MP WITH (READUNCOMMITTED) ON T.LibId = MP.LibraryId
            INNER JOIN MMVolume V WITH (READUNCOMMITTED) ON MP.MediaSideId = V.MediaSideId
		WHERE	V.SIDBStoreId > 0
		GROUP BY T.LibId
	 ) AS TMP ON T.LibId = TMP.LibId



UPDATE T
SET MinRetention = TMP.MinRetention,
	MaxRetention = TMP.MaxRetention
FROM @tblLib T
     INNER JOIN
     (
		SELECT T.LibId, MIN(case when R.retentionDays = -1 then 2147483647 else R.retentionDays end) AS MinRetention, 
			MAX(case when R.retentionDays = -1 then 2147483647 else R.retentionDays end) AS MaxRetention
        FROM @tblLib T
			INNER JOIN MMMasterPool MP WITH (READUNCOMMITTED) ON T.LibId = MP.LibraryId
            INNER JOIN MMDrivePool DP WITH (READUNCOMMITTED) ON DP.MasterPoolId = MP.MasterPoolId
			INNER JOIN MMDataPath D WITH(READUNCOMMITTED) ON DP.DrivePoolId = D.DrivePoolId
			INNER JOIN ArchGroupCopy C WITH(READUNCOMMITTED) ON D.CopyId = C.id AND C.dedupeFlags & (268435456 | 262144)  = 262144 
			INNER JOIN archAgingRule R WITH(READUNCOMMITTED) ON C.id = R.copyId
		GROUP BY T.LibId
	 ) AS TMP ON T.LibId = TMP.LibId

SELECT ISNULL(COUNT(1), 0) [Number of Cloud Libraries], ISNULL(SUM(sizeOnLibGB), 0) AS [Total Size], ISNULL(MIN(MinRetention), 0) [Min Retention], ISNULL(MAX(MaxRetention), 0) [Max Retention]
FROM @tblLib ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Number of Cloud Libraries] AS '@Col_101009404',[Total Size] AS '@Col_1228910493',[Min Retention] AS '@Col_-1922319728',[Max Retention] AS '@Col_550341410' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10391'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))