// ─────────────────────────────────────────────────────────────
// MDE Toolkit — Power Query (M) for Azure Government
// ─────────────────────────────────────────────────────────────
// Same as HealthReports-Query.m but uses the Government cloud
// table endpoint suffix (.table.core.usgovcloudapi.net).
// ─────────────────────────────────────────────────────────────

let
    StorageAccountName = "stmdetoolkit",
    TableName = "HealthReports",

    Source = AzureStorage.Tables("https://" & StorageAccountName & ".table.core.usgovcloudapi.net"),
    HealthReports = Source{[Name = TableName]}[Content],

    Expanded = Table.ExpandRecordColumn(HealthReports, "Content", {
        "Hostname", "OsVersion", "OsBuild", "OrganizationId", "DeviceGroup",
        "DomainName", "IpAddress", "ReportId",
        "GeneratedAtUtc", "UploadedAtUtc",
        "IsAzureAdJoined", "IsHybridJoined", "AzureAdDeviceId", "DeviceTrustType",
        "HealthScore", "OverallStatus", "CriticalEventCount", "TopIssues",
        "DefenderEnabled", "RealTimeProtectionEnabled", "BehaviorMonitorEnabled",
        "TamperProtectionEnabled", "DefenderAvStatus", "SignatureAgeHours",
        "IsOnboarded", "SenseIsRunning", "MdeStatus",
        "AsrIsConfigured", "AsrTotalRules", "AsrBlockModeRules", "AsrStatus",
        "FirewallDomainEnabled", "FirewallPrivateEnabled", "FirewallPublicEnabled", "FirewallStatus",
        "DeviceControlEnabled", "DeviceControlRuleCount", "DeviceControlStatus",
        "AppControlIsConfigured", "AppControlIsEnabled", "AppControlStatus",
        "VbsEnabled", "HvciRunning", "CredentialGuardRunning", "VbsStatus",
        "ServiceVersion", "SchemaVersion"
    }),

    Typed = Table.TransformColumnTypes(Expanded, {
        {"Hostname", type text}, {"OsVersion", type text}, {"OsBuild", type text},
        {"OrganizationId", type text}, {"DeviceGroup", type text},
        {"DomainName", type text}, {"IpAddress", type text}, {"ReportId", type text},
        {"OverallStatus", type text}, {"TopIssues", type text},
        {"DefenderAvStatus", type text}, {"MdeStatus", type text},
        {"AsrStatus", type text}, {"FirewallStatus", type text},
        {"DeviceControlStatus", type text}, {"AppControlStatus", type text},
        {"VbsStatus", type text}, {"DeviceTrustType", type text},
        {"AzureAdDeviceId", type text}, {"ServiceVersion", type text},
        {"GeneratedAtUtc", type datetimezone}, {"UploadedAtUtc", type datetimezone},
        {"HealthScore", Int64.Type}, {"CriticalEventCount", Int64.Type},
        {"SignatureAgeHours", Int64.Type},
        {"AsrTotalRules", Int64.Type}, {"AsrBlockModeRules", Int64.Type},
        {"DeviceControlRuleCount", Int64.Type}, {"SchemaVersion", Int64.Type},
        {"DefenderEnabled", type logical}, {"RealTimeProtectionEnabled", type logical},
        {"BehaviorMonitorEnabled", type logical}, {"TamperProtectionEnabled", type logical},
        {"IsOnboarded", type logical}, {"SenseIsRunning", type logical},
        {"IsAzureAdJoined", type logical}, {"IsHybridJoined", type logical},
        {"AsrIsConfigured", type logical},
        {"FirewallDomainEnabled", type logical}, {"FirewallPrivateEnabled", type logical},
        {"FirewallPublicEnabled", type logical},
        {"DeviceControlEnabled", type logical},
        {"AppControlIsConfigured", type logical}, {"AppControlIsEnabled", type logical},
        {"VbsEnabled", type logical}, {"HvciRunning", type logical},
        {"CredentialGuardRunning", type logical}
    }),

    WithHoursSince = Table.AddColumn(Typed, "HoursSinceReport",
        each Duration.TotalHours(DateTimeZone.UtcNow() - [GeneratedAtUtc]), type number),
    WithIsStale = Table.AddColumn(WithHoursSince, "IsStale",
        each [HoursSinceReport] > 24, type logical),
    WithAllFirewallEnabled = Table.AddColumn(WithIsStale, "AllFirewallProfilesEnabled",
        each [FirewallDomainEnabled] and [FirewallPrivateEnabled] and [FirewallPublicEnabled], type logical),
    WithHealthBucket = Table.AddColumn(WithAllFirewallEnabled, "HealthBucket",
        each if [HealthScore] >= 90 then "Excellent"
             else if [HealthScore] >= 70 then "Good"
             else if [HealthScore] >= 50 then "Needs Attention"
             else "Critical", type text),
    Sorted = Table.Sort(WithHealthBucket, {{"GeneratedAtUtc", Order.Descending}})
in
    Sorted
