[CmdletBinding()]
Param(
   
   [Parameter(Mandatory=$True)]
   [emDash.Workflow.Step.Interface.PowershellContext]$ctx

 )

try
{
    $emdashStepName="BackupDBandLOGforAvailabilityGroup"
    $emdashStepVersion="0.1"
    $loc = Get-Location
    $ErrorActionPreference = 'Stop'
    $VerbosePreference='SilentlyContinue'
    $WarningPreference = 'SilentlyContinue'

    Import-Module Sqlps -DisableNameChecking 3>$null
    
    $WarningPreference = 'Continue'
    $VerbosePreference='Continue'
    Set-Location $loc

    $envName = $ctx.TargetEnvironment.Name
	$endurDatabase = $ctx.TargetEnvironment.DatabaseAllocations | Where-Object {  $_.Databases.Type.ToLower() -eq "endur" } | Select -first 1
	$sqlServerInstance = $endurDatabase.Databases.ServerName
	$ctx.LogProgress("Endur database server is $sqlServerInstance")
	$endurDB = $endurDatabase.Databases.Name
	$ctx.LogProgress("Endur database name is $endurDB")

    $checkAGSQL = "SELECT SERVERPROPERTY ('IsHadrEnabled') as Answer"

    $checkAG = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database master -Query $checkAGSQL -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr
    if ($sqlerr) {
            Write-Error("Error running SQL statement $($checkAGSQL): $($sqlerr.Exception.Message)")
    }

    if($checkAG.Answer -eq 1)
    {
        $ctx.LogProgress("$($sqlServerInstance) is in an Availability Group.  We need to turn it off before the database can be restored.")
    }
    else
    {
        $ctx.LogProgress("$($sqlServerInstance) is NOT in an Availability Group.  No further action required.")
        return $True
    }
    
    $AGDetailsSQL = "SELECT
                       AGC.name -- Availability Group
                     , RCS.replica_server_name -- SQL cluster node name
                     , ARS.role_desc  -- Replica Role
                     , AGL.dns_name  -- Listener Name
                    FROM
                     sys.availability_groups_cluster AS AGC
                      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
                       ON
                        RCS.group_id = AGC.group_id
                      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
                       ON
                        ARS.replica_id = RCS.replica_id
                      INNER JOIN sys.availability_group_listeners AS AGL
                       ON
                        AGL.group_id = ARS.group_id
                    WHERE
                     ARS.role_desc = 'PRIMARY'"

    $ctx.LogProgress("Getting Availability Group Details from SQL Server...")

    $AGDetails = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database master -Query $AGDetailsSQL -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr
    if ($sqlerr) {
            Write-Error("Error running SQL statement $($AGDetailsSQL): $($sqlerr.Exception.Message)")
    }


    $ctx.LogProgress("Availability Group Name is $($AGDetails.name)")
    $availbilityGroupName = $AGDetails.name

    $ctx.LogProgress("Primary Server Name is $($AGDetails.replica_server_name)")
    $primaryAGServerName = $AGDetails.replica_server_name


    $sqlCommands =  "ALTER DATABASE $($endurDB) SET RECOVERY FULL WITH NO_WAIT",
                    "BACKUP DATABASE $($endurDB) TO DISK=N'\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.bak' WITH FORMAT, INIT, COMPRESSION",
                    "BACKUP LOG $($endurDB) TO DISK=N'\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.trn' WITH FORMAT, INIT, COMPRESSION",  
                    "ALTER AVAILABILITY GROUP $($availbilityGroupName) ADD DATABASE $($endurDB)"

    foreach ($sqlCommand in $sqlCommands)
    {
        $ctx.LogProgress("Executing SQL Command: $($sqlCommand)")
        Invoke-sqlcmd -ServerInstance $primaryAGServerName -Database master -Query $sqlCommand -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr
	    if ($sqlerr) {
            Write-Error("Error running SQL statement $($sqlCommand): $($sqlerr.Exception.Message)")
        }
    }
    Return $True
}
catch [Exception]
{
    Write-Error ("Error: {0} {1}" -f $_.Exception.Message, $_.Exception.StackTrace)
	# returning False will set the step status to Failed
    Return $False
}