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

 )

try
{
    $emdashStepName="RestoreAvailabilityGroupOnSecondary"
    $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 back on again")
    }
    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 = 'SECONDARY'"

    $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("Secondary Server Name is $($AGDetails.replica_server_name)")
    $secondaryAGServerName = $AGDetails.replica_server_name


    $sqlCommands =  "RESTORE DATABASE $($endurDB) FROM DISK= N'\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.bak' WITH FILE=1, REPLACE, NORECOVERY",
                    "RESTORE LOG $($endurDB) FROM DISK= N'\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.trn' WITH FILE=1, REPLACE, NORECOVERY",
                    "begin try 
                       declare @conn bit 
                       declare @count int 
                       declare @replica_id uniqueidentifier 
                       declare @group_id uniqueidentifier 
                       set @conn = 0 
                       set @count = 30 -- wait for 5 minutes 
  
                       if (serverproperty('IsHadrEnabled') = 1) 
                          and (isnull((select member_state 
                                       from master.sys.dm_hadr_cluster_members 
                                       where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0) 
                          and (isnull((select state 
                                       from master.sys.database_mirroring_endpoints), 1) = 0) 
                       begin 
                          select @group_id = ags.group_id 
                          from master.sys.availability_groups as ags 
                          where name = N'$($availbilityGroupName)' 

                          select @replica_id = replicas.replica_id 
                          from master.sys.availability_replicas as replicas 
                          where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id 

                          while @conn <> 1 and @count > 0 
                          begin 
                             set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1) 
                             if @conn = 1 
                             begin 
                                -- exit loop when the replica is connected, or if the query cannot find the replica status 
                                break 
                             end 
                             waitfor delay '00:00:10' 
                             set @count = @count - 1 
                          end 
                       end 
                    end try 

                    begin catch 
                       -- If the wait loop fails, do not stop execution of the alter database statement 
                    end catch",
                    "ALTER DATABASE $($endurDB) SET HADR AVAILABILITY GROUP = $($availbilityGroupName)"

    foreach ($sqlCommand in $sqlCommands)
    {
        $ctx.LogProgress("Executing SQL Command: $($sqlCommand)")
        Invoke-sqlcmd -ServerInstance $secondaryAGServerName -Database master -Query $sqlCommand -ConnectionTimeout 0 -QueryTimeout 65334 -ErrorAction SilentlyContinue -ErrorVariable sqlerr
	    if ($sqlerr) {
            Write-Error("Error running SQL statement $($sqlCommand): $($sqlerr.Exception.Message)")
        }
    }
    $ctx.LogProgress("Removing temporary backup files...")
    Remove-Item -Path "\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.bak"
    Remove-Item -Path "\\pi-lon-nas02\SQL_emDash\NOTBackedUp-NONProd\$($endurDB)_postAG_Restore.trn"
    $ctx.LogProgress("Temporary backup files removed.")

    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
}