// Intune Devices Query
// Retrieves comprehensive device information from Intune Data Warehouse

let
    // Import base connection
    BaseConnection = #shared[BaseConnection],
    GetPaged = BaseConnection[GetPaged],
    
    // Get devices data with pagination
    Source = GetPaged("/data/Devices"),
    
    // Select relevant columns for device management
    SelectedColumns = Table.SelectColumns(
        Source,
        {
            "deviceId",
            "deviceName", 
            "userId",
            "userPrincipalName",
            "osDescription",
            "osVersion",
            "manufacturer",
            "model",
            "enrolledDateTime",
            "lastContact", 
            "complianceState",
            "jailBroken",
            "isEncrypted",
            "encryptionState",
            "ownerType",
            "managementAgent",
            "azureADRegistered",
            "azureADJoinType",
            "clientVersion",
            "serialNumber",
            "imei",
            "phoneNumber",
            "wiFiMacAddress",
            "ethernetMacAddress",
            "totalStorageSpaceInBytes",
            "freeStorageSpaceInBytes",
            "managedDeviceName",
            "partnerReportedThreatState",
            "requireUserEnrollmentApproval",
            "managementCertificateExpirationDate",
            "iccId",
            "udid",
            "notes",
            "ethernetMacAddress",
            "physicalMemoryInBytes",
            "processorArchitecture",
            "specificationVersion",
            "joinType",
            "skuFamily",
            "skuNumber",
            "managementFeatures"
        },
        MissingField.Ignore
    ),
    
    // Transform column types for proper data modeling
    TypedColumns = Table.TransformColumnTypes(
        SelectedColumns,
        {
            {"deviceId", type text},
            {"deviceName", type text}, 
            {"userId", type text},
            {"userPrincipalName", type text},
            {"osDescription", type text},
            {"osVersion", type text},
            {"manufacturer", type text},
            {"model", type text},
            {"enrolledDateTime", type datetime},
            {"lastContact", type datetime},
            {"complianceState", type text},
            {"jailBroken", type text},
            {"isEncrypted", type logical},
            {"encryptionState", type text},
            {"ownerType", type text},
            {"managementAgent", type text},
            {"azureADRegistered", type logical},
            {"azureADJoinType", type text},
            {"clientVersion", type text},
            {"serialNumber", type text},
            {"imei", type text},
            {"phoneNumber", type text},
            {"wiFiMacAddress", type text},
            {"ethernetMacAddress", type text},
            {"totalStorageSpaceInBytes", type number},
            {"freeStorageSpaceInBytes", type number},
            {"managedDeviceName", type text},
            {"partnerReportedThreatState", type text},
            {"requireUserEnrollmentApproval", type logical},
            {"managementCertificateExpirationDate", type datetime},
            {"iccId", type text},
            {"udid", type text},
            {"notes", type text},
            {"physicalMemoryInBytes", type number},
            {"processorArchitecture", type text},
            {"specificationVersion", type text},
            {"joinType", type text},
            {"skuFamily", type text},
            {"skuNumber", type text}
        }
    ),
    
    // Add computed columns for better analysis
    EnhancedData = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(
                    TypedColumns,
                    "DaysSinceLastContact",
                    each if [lastContact] <> null then Duration.Days(DateTime.LocalNow() - [lastContact]) else null,
                    type number
                ),
                "StorageUtilizationPercent", 
                each if [totalStorageSpaceInBytes] <> null and [totalStorageSpaceInBytes] > 0 
                     then (([totalStorageSpaceInBytes] - [freeStorageSpaceInBytes]) / [totalStorageSpaceInBytes]) * 100 
                     else null,
                type number
            ),
            "IsRecentlyActive",
            each if [DaysSinceLastContact] <> null then [DaysSinceLastContact] <= 7 else false,
            type logical
        ),
        "DevicePlatform",
        each if Text.Contains([osDescription], "Windows") then "Windows"
             else if Text.Contains([osDescription], "iOS") then "iOS" 
             else if Text.Contains([osDescription], "Android") then "Android"
             else if Text.Contains([osDescription], "macOS") then "macOS"
             else "Other",
        type text
    ),
    
    // Add compliance status categorization
    FinalData = Table.AddColumn(
        EnhancedData,
        "ComplianceCategory",
        each if [complianceState] = "compliant" then "Compliant"
             else if [complianceState] = "noncompliant" then "Non-Compliant"
             else if [complianceState] = "conflict" then "Conflict"
             else if [complianceState] = "error" then "Error"
             else if [complianceState] = "unknown" then "Unknown"
             else "Not Evaluated",
        type text
    )
in
    FinalData