-- This is how the WSUS API evaluates the maximum category value of 100.
-- The main purpose is to evaluate both product and company categories and sum the total updates.

USE SUSDB;

-- Insert the CTE result into a temporary table
SELECT
    cs.DefaultTitle AS Category,
    parent_cs.DefaultTitle AS ParentCategory,
    cs.CategoryType,
    COUNT(ct.UpdateID) AS UpdateCount
INTO #CategoryData
FROM
    [SUSDB].[dbo].[tbUpdate] AS cr
JOIN
    [SUSDB].[dbo].[vwUpdateInCategory] AS ct ON cr.UpdateID = ct.UpdateID
JOIN
    [SUSDB].[PUBLIC_VIEWS].[vCategory] AS cs ON ct.CategoryUpdateID = cs.CategoryID
LEFT JOIN
    [SUSDB].[PUBLIC_VIEWS].[vCategory] AS parent_cs ON cs.ParentCategoryID = parent_cs.CategoryID
WHERE
    cr.IsLocallyPublished = 1
    AND ct.CategoryUpdateID IS NOT NULL
GROUP BY
    cs.DefaultTitle,
    parent_cs.DefaultTitle,
    cs.CategoryType;

-- First result set: Product categories and company-level aggregates
SELECT
    Category,
    ParentCategory,
    CategoryType,
    UpdateCount,
    1 AS TotalCategories
FROM
    #CategoryData
WHERE
    CategoryType = 'Product'

UNION ALL

-- Calculate TotalCategories for each ParentCategory in the company-level rows
SELECT
    ParentCategory AS Category,
    NULL AS ParentCategory,
    'Company' AS CategoryType,
    SUM(UpdateCount) AS UpdateCount,
    (SELECT COUNT(*) FROM #CategoryData AS sub WHERE sub.ParentCategory = main.ParentCategory AND sub.CategoryType = 'Product') + 1 AS TotalCategories
FROM
    #CategoryData AS main
WHERE
    CategoryType = 'Product'
GROUP BY
    ParentCategory
ORDER BY
    CategoryType ASC, Category ASC;

-- Second result set: Display totals for UpdateCount and TotalCategories specifically for 'Company' categories
SELECT
    'UpdateCount' AS Name,
    SUM(UpdateCount) AS Total
FROM
    #CategoryData
WHERE
    CategoryType = 'Company'

UNION ALL

SELECT
    'TotalCategories' AS Name,
    SUM(TotalCategories) AS Total
FROM
    (
        SELECT
            ParentCategory AS Category,
            NULL AS ParentCategory,
            'Company' AS CategoryType,
            SUM(UpdateCount) AS UpdateCount,
            (SELECT COUNT(*) FROM #CategoryData AS sub WHERE sub.ParentCategory = main.ParentCategory AND sub.CategoryType = 'Product') + 1 AS TotalCategories
        FROM
            #CategoryData AS main
        WHERE
            CategoryType = 'Product'
        GROUP BY
            ParentCategory
    ) AS CompanyCategories;

-- Clean up the temporary table
DROP TABLE #CategoryData;