Employee Sync Azure Function

using namespace System.Net

param($Request, $TriggerMetadata)

# =========================================================================
# Config (app settings — set these on the Function App)
# =========================================================================
# CiaEtlSecret             -> Key Vault reference to kv-cia-employee-sync/ciaETLsecret
#                             e.g. @Microsoft.KeyVault(SecretUri=https://kv-cia-employee-sync.vault.azure.net/secrets/ciaETLsecret/)
# TableStorageAccountName  -> plain string, e.g. "mystorageacct"
# EmployeeTableName        -> plain string, e.g. "Employees" (defaults below if unset)
# OuMappingTableName       -> plain string, e.g. "OrganisationUnitMappings" (defaults below if unset)
# =========================================================================

$expectedToken       = $env:CiaEtlSecret
$storageAccountName  = $env:TableStorageAccountName
$tableName           = $env:EmployeeTableName
if (-not $tableName) { $tableName = 'Employees' }
$ouMappingTableName  = $env:OuMappingTableName
if (-not $ouMappingTableName) { $ouMappingTableName = 'OrganisationUnitMappings' }

function New-JsonResponse {
    param(
        [int]$StatusCode,
        [hashtable]$Body
    )
    [HttpResponseContext]@{
        StatusCode = $StatusCode
        Headers    = @{ 'Content-Type' = 'application/json' }
        Body       = ($Body | ConvertTo-Json -Compress -Depth 5)
    }
}

# -------------------------------------------------------------------------
# 1. Verify the bearer token before doing anything else with the payload.
#    This is a static shared secret (from Key Vault), compared in constant
#    time — not a signature, so there's nothing to compute, just compare.
# -------------------------------------------------------------------------
if (-not $expectedToken) {
    Write-Error 'CiaEtlSecret app setting is not configured.'
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 500 -Body @{ error = 'Server misconfigured' })
    return
}

$authHeader = $Request.Headers['Authorization']
if (-not $authHeader -or $authHeader -notmatch '^Bearer\s+(.+)$') {
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 401 -Body @{ error = 'Missing or malformed Authorization header' })
    return
}
$providedToken = $Matches[1].Trim()

$providedBytes = [System.Text.Encoding]::UTF8.GetBytes($providedToken)
$expectedBytes = [System.Text.Encoding]::UTF8.GetBytes($expectedToken)
$isValid = ($providedBytes.Length -eq $expectedBytes.Length) -and
           [System.Security.Cryptography.CryptographicOperations]::FixedTimeEquals($providedBytes, $expectedBytes)

if (-not $isValid) {
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 401 -Body @{ error = 'Invalid token' })
    return
}

$rawBody = [string]$Request.Body

# -------------------------------------------------------------------------
# 2. Parse the XML generically — walks whatever the repeating element is
#    named, so it doesn't break if the wrapper/record element names differ
#    from what we assumed.
# -------------------------------------------------------------------------
try {
    [xml]$xmlDoc = $rawBody
}
catch {
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 400 -Body @{ error = "Body is not valid XML: $($_.Exception.Message)" })
    return
}

$recordNodes = $xmlDoc.DocumentElement.ChildNodes | Where-Object { $_.NodeType -eq 'Element' }

$records = foreach ($node in $recordNodes) {
    $entity = [ordered]@{}
    foreach ($child in $node.ChildNodes) {
        if ($child.NodeType -eq 'Element') {
            $entity[$child.Name] = $child.InnerText
        }
    }
    [pscustomobject]$entity
}

if (-not $records) {
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 400 -Body @{ error = 'No records found in payload' })
    return
}

# -------------------------------------------------------------------------
# 3. Get a Table Storage data-plane token via the Function App's managed
#    identity (Az context was already established in profile.ps1)
# -------------------------------------------------------------------------
$tokenResult = Get-AzAccessToken -ResourceUrl 'https://storage.azure.com'
$accessToken = $tokenResult.Token
if ($accessToken -is [System.Security.SecureString]) {
    $accessToken = [System.Net.NetworkCredential]::new('', $accessToken).Password
}

$tableEndpoint = "https://$storageAccountName.table.core.windows.net"
$apiVersion    = '2020-12-06'

$headers = @{
    Authorization  = "Bearer $accessToken"
    Accept         = 'application/json;odata=nometadata'
    'x-ms-version' = $apiVersion
}

# -------------------------------------------------------------------------
# 3b. Load the organisationUnit -> GUID lookup table ONCE per invocation
#     (not once per record). Stored as its own table:
#       PartitionKey = 'Mapping' (fixed — keeps it a single-partition query)
#       RowKey       = 3-char prefix, e.g. 'GRO'
#       MappedGuid   = the GUID that prefix resolves to
# -------------------------------------------------------------------------
$ouLookup = @{}
try {
    $filterExpr = [Uri]::EscapeDataString("PartitionKey eq 'Mapping'")
    $selectExpr = [Uri]::EscapeDataString('RowKey,MappedGuid')
    $mappingUri = "$tableEndpoint/$ouMappingTableName()?`$filter=$filterExpr&`$select=$selectExpr"
    $mappingResult = Invoke-RestMethod -Method Get -Uri $mappingUri -Headers $headers -ErrorAction Stop
    foreach ($row in $mappingResult.value) {
        $ouLookup[$row.RowKey.ToUpperInvariant()] = $row.MappedGuid
    }
}
catch {
    Write-Error "Failed to load organisationUnit mapping table '$ouMappingTableName': $($_.Exception.Message)"
    Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 502 -Body @{ error = 'Could not load organisationUnit mapping table' })
    return
}

# -------------------------------------------------------------------------
# 4. Upsert each record — PartitionKey = paylocation, RowKey = employeeid.
#    whenmodified is checked first so we skip writing rows that haven't
#    actually changed since the last sync.
# -------------------------------------------------------------------------
$upserted = 0
$skipped  = 0
$failed   = 0

foreach ($record in $records) {

    $partitionKey = $record.paylocation
    $rowKey       = $record.employeeid

    if (-not $partitionKey -or -not $rowKey) {
        Write-Warning "Skipping record missing paylocation/employeeid: $($record | ConvertTo-Json -Compress)"
        $failed++
        continue
    }

    $encodedPK = [Uri]::EscapeDataString($partitionKey)
    $encodedRK = [Uri]::EscapeDataString($rowKey)
    $entityUri = "$tableEndpoint/$tableName(PartitionKey='$encodedPK',RowKey='$encodedRK')"

    # Check whether the stored row is already current
    $existingModified = $null
    try {
        $existing = Invoke-RestMethod -Method Get -Uri "$entityUri`?`$select=whenmodified" -Headers $headers -ErrorAction Stop
        $existingModified = $existing.whenmodified
    }
    catch {
        $statusCode = $_.Exception.Response.StatusCode.value__
        if ($statusCode -ne 404) {
            Write-Warning "Lookup failed for RowKey '$rowKey': $($_.Exception.Message)"
        }
    }

    if ($existingModified -and $record.whenmodified) {
        try {
            if ([datetime]$existingModified -ge [datetime]$record.whenmodified) {
                $skipped++
                continue
            }
        }
        catch {
            # Unparsable date on either side — fall through and overwrite rather than silently drop the update
        }
    }

    # Map organisationUnit's first 3 characters to a fixed GUID via the lookup
    # table loaded above (e.g. GROVES / GROVES OSHC / GROVE / GROCC all -> 'GRO'
    # -> the same guid). Unmatched prefixes are logged and left unmapped rather
    # than written as blank/null.
    if ($record.organisationunit -and $record.organisationunit.Length -ge 3) {
        $ouPrefix = $record.organisationunit.Substring(0, 3).ToUpperInvariant()
        if ($ouLookup.ContainsKey($ouPrefix)) {
            $record | Add-Member -NotePropertyName 'organisationUnitId' -NotePropertyValue $ouLookup[$ouPrefix] -Force
        }
        else {
            Write-Warning "No organisationUnit mapping for prefix '$ouPrefix' (RowKey '$rowKey', value '$($record.organisationunit)')"
        }
    }

    # Table Storage is schemaless per-entity, so any property present here —
    # including new fields that show up in the XML in the future, or the
    # organisationUnitId we just added — becomes a column automatically.
    # No code change needed when the upstream data adds a new field.
    $entityHash = [ordered]@{
        PartitionKey = $partitionKey
        RowKey       = $rowKey
    }
    foreach ($prop in $record.PSObject.Properties) {
        if ($prop.Name -notin @('PartitionKey', 'RowKey')) {
            $entityHash[$prop.Name] = $prop.Value
        }
    }
    $entityBody = $entityHash | ConvertTo-Json -Compress

    try {
        # PUT = insert-or-replace (upsert); no If-Match header needed
        Invoke-RestMethod -Method Put -Uri $entityUri -Headers ($headers + @{ 'Content-Type' = 'application/json' }) -Body $entityBody -ErrorAction Stop | Out-Null
        $upserted++
    }
    catch {
        Write-Error "Upsert failed for RowKey '$rowKey': $($_.Exception.Message)"
        $failed++
    }
}

$summary = @{
    totalRecords = $records.Count
    upserted     = $upserted
    skipped      = $skipped
    failed       = $failed
}

Push-OutputBinding -Name Response -Value (New-JsonResponse -StatusCode 200 -Body $summary)