AlwaysON health check alerts

SELECT AGS.name AS AGGroupName, AR.replica_server_name AS InstanceName, 
                   HARS.role_desc, sd.name AS DBName,DRS.database_id, AR.availability_mode_desc AS SyncMode, 
                   DRS.synchronization_state_desc AS SyncState,DRS.last_hardened_lsn,DRS.end_of_log_lsn, 
                   DRS.last_redone_lsn, 
                   DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
                   DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
                   DRS.log_send_queue_size,DRS.redo_queue_size,
                    --Time corresponding to the last commit record.
                    --On the secondary database, this time is the same as on the primary database.
                    --On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database 
                    --   has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database 
                    --   row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress 
                    --   has been reported back to the primary replica by the secondary replica.
                   DRS.last_commit_time
   into ##HA_stats
            FROM  master.sys.dm_hadr_database_replica_states DRS 
            LEFT JOIN master.sys.availability_replicas AR 
            ON DRS.replica_id = AR.replica_id 
            LEFT JOIN master.sys.availability_groups AGS 
            ON AR.group_id = AGS.group_id 
            LEFT JOIN master.sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
            AND AR.replica_id = HARS.replica_id 
INNER JOIN master.sys.databases sd on DRS.database_id = sd.database_id
            
--Alert if not in SYNCHRONIZED status:
if exists (select * from ##HA_stats where SyncState not like ''SYNCHRONIZED'')
begin
  EXEC msdb..sp_send_dbmail         
  @recipients=''XXXX@gmail.com'',           

  @subject = ''Always ON: Issue with Non- prod AlwaysOn Server -ServerName'',        
  @body = ''The following records are dbs that are not currently in a SYNCHRONIZED status.   An automated attempt is being made to resume the data transfer. ',
  @query = ''select cast(InstanceName as varchar(20)) InstanceName, cast(role_desc as varchar(10)) role_desc, cast(DBName as varchar(15)) DBName, cast(SyncState as varchar(20)) SyncState from ##HA_stats where SyncState not like ''''SYNCHRONIZED''''''

  --attempt to resume node
  ---ALTER DATABASE CKOLTP SET HADR RESUME
end

if exists (select * from sys.databases where state_desc!=''ONLINE'')
begin
  DECLARE @TEXT AS VARCHAR(300)
  
  
  select getdate() as State_Date_Time,name AS Database_Name,state_desc AS DB_Status into ##Current_DB_State from sys.databases where state_desc!=''ONLINE''
  insert into master.dbo.DB_Status_History select * from ##Current_DB_State


  SELECT @TEXT=''Secondary Server :ServerName.''+CHAR(13)+''The Following Database is not ONLINE nor in Synchronized State. Please check and assist.''+CHAR(13)

  EXEC msdb..sp_send_dbmail         
  @recipients=''XXXX@gmail.com'',           
  
  @subject = ''Always ON : Issue with  Non- prod database not Online. ServerName '',        
  @body = @TEXT,
  @query = ''select * from ##Current_DB_State''

  --attempt to resume node
  ---ALTER DATABASE CKOLTP SET HADR RESUME

 
end
--Write to history
insert into master..AGHistory, select getdate(), * from ##HA_stats