# Authenticate to Azure using Managed Identity
$AzureContext = (Connect-AzAccount -Identity).context
$AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription -DefaultProfile $AzureContext

# Connect to state management table
$storageAccount = Get-AzStorageAccount -Name $StateStorageAccountName -ResourceGroupName $AzureContext.Subscription.Name
$tableContext = $storageAccount.Context
$stateTable = Get-AzTable -Name $StateTableName -Context $tableContext

# Iterate through each target VM
$vmResourceIds = $TargetVMs.Split(',')
foreach ($vmResourceId in $vmResourceIds) {
    # ... (get VM details)

    # Retrieve last known state from Table Storage
    $currentStateEntity = Get-AzTableRow -Table $stateTable -PartitionKey $partitionKey -RowKey $rowKey
    $lastKnownState = $currentStateEntity.CurrentState

    # Define the in-guest script to check the SQL replica role
    $scriptToRun = "Invoke-Sqlcmd -Query `"SELECT IIF(role_desc = 'PRIMARY', 'Primary', 'Secondary') FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1`" -ServerInstance 'localhost'"

    # Execute the script inside the VM
    $runCommandResult = Invoke-AzVMRunCommand -ResourceGroupName $resourceGroupName -VMName $vmName -CommandId 'RunPowerShellScript' -ScriptString $scriptToRun
    $liveRole = ($runCommandResult.Value.Message).Trim()

    # State Comparison and Logic
    if ($liveRole -eq "Primary" -and $lastKnownState -eq "Passive") {
        # FAILOVER DETECTED: Trigger Failover Logic App
        Invoke-WebRequest -Uri $FailoverLogicAppUri -Method Post -Body $payload
        # Update state table to 'FailoverDetected'
        Add-AzTableRow -Table $stateTable -Entity $entity -Force
    }
    elseif ($liveRole -eq "Secondary" -and ($lastKnownState -eq "Primary" -or $lastKnownState -eq "FailoverDetected")) {
        # FAILBACK DETECTED: Trigger Failback Logic App
        Invoke-WebRequest -Uri $FailbackLogicAppUri -Method Post -Body $payload
    }
}