
--												*** SQL DB MAIl SETUP ***
-----------------------------------------------------------------------------------------------------------------------------------------
-- NOTE: Please read the note before executing the script
-- *** Before executing the script, please ensure that sysmail_profile exists are not ***
------------------------------------------------------------------------------------------------------------------------------------------
--Step 00 : To check sysmail_profile exists are not by executing the below SELECT statement, 

  --select * from msdb.dbo.sysmail_profile
  --select * from msdb.dbo.sysmail_account
  --select * from msdb.dbo.sysmail_profileaccount

  -- *** sysmail_profile exists, No need to run the scrpit  ***
------------------------------------------------------------------------------------------------------------------------------------------
-- Step 01 : sp_configure 'show advanced options', 1;: Enables the advanced configuration options.
-- show advanced options
--EXEC sp_configure 'show advanced options', 1
--GO
--RECONFIGURE
--GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Step 02 : sp_configure 'Database Mail XPs', 1;: Enables the Database Mail Extended Procedures feature. 
-- enable Database Mail XPs
--EXEC sp_configure 'Database Mail XPs', 1
--GO
--RECONFIGURE
--GO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --Step 03 : sp_configure 'Database Mail XPs';:  This checks the current status of the Database Mail XPs option.
-- check if it has been changed
--EXEC sp_configure 'Database Mail XPs'
--GO
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Step 04 : EXEC sp_configure 'show advanced options', 0;  If you no longer need to access or modify advanced options, 
--running this script will disable the ability to view them in sp_configure.
-- hide advanced options
--EXEC sp_configure 'show advanced options', 0
--GO
--RECONFIGURE
--GO
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Step 05 : The SQL script you provided checks whether an email account named CMMS Admin exists in the msdb database's sysmail_account table. 
-- If it doesn't exist, it creates the account with the specified details using the sysmail_add_account_sp stored procedure.

--Use MSDB
--go
--IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = 'CMMS Admin') 
--  BEGIN 
--    --CREATE Account [SQLServer Express] 
--    EXECUTE msdb.dbo.sysmail_add_account_sp 
--    @account_name            = 'CMMS Admin',			-- Name of the mail account.
--    @email_address           = '',					-- Specify the sender email address ( The sender's email address (required for emails to work).
--    @display_name            = 'CMMS Admin', 
--    @replyto_address         = '', 
--    @description             = '', 
--    @mailserver_name         = 'smtp.gmail.com',		-- SMTP server for sending emails (e.g., smtp.gmail.com for Gmail).
--    @mailserver_type         = 'SMTP', 
--    @port                    = '587',					-- Port number for the SMTP server (587 for Gmail with TLS/SSL).
--    @username                = 'CMMS Admin',			-- Authentication credentials for the email account.(Provide the email username)
--    @password                = '',					-- Authentication credentials for the email account.(Provide the email password)
--    @use_default_credentials =  0 , 
--    @enable_ssl              =  1 ;					-- Enable SSL for secure communication
--  END --IF EXISTS  account

  --select * from msdb.dbo.sysmail_account
  --EXECUTE msdb.dbo.sysmail_delete_account_sp  
  --  @account_name = 'CMMS Admin' ;  


  --------------------------------------------------------------------------------------------------------------------------------------------------

  --Step 06 : The script you provided checks if a mail profile named CMMS Admin exists in the msdb.dbo.sysmail_profile table. If it doesn't exist, 
  --it creates the profile using the sysmail_add_profile_sp stored procedure.

--  Use MSDB
--go
--IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = 'CMMS Admin')  
--  BEGIN 
--    --CREATE Profile [SQLServer Express Edition] 
--    EXECUTE msdb.dbo.sysmail_add_profile_sp 
--      @profile_name = 'CMMS Admin', 
--      @description  = 'This db mail account is used by SQL Server.'; 
--  END --IF EXISTS profile

  --select * from msdb.dbo.sysmail_profile


  ----------------------------------------------------------------------------------------------------------------------------------------------------

  --Step 07 : The script ensures that a mail account named CMMS Admin is associated with a mail profile also named CMMS Admin. 
  --If no such association exists, it creates one using the sysmail_add_profileaccount_sp stored procedure. Here's a clarified and polished version of the.

--  Use MSDB
--go
--IF NOT EXISTS(SELECT * 
--              FROM msdb.dbo.sysmail_profileaccount pa 
--                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
--                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
--              WHERE p.name = 'CMMS Admin' 
--                AND a.name = 'CMMS Admin')  
--  BEGIN 
--    -- Associate Account [SQLServer Express] to Profile [SQLServer Express Edition] 
--    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
--      @profile_name = 'CMMS Admin', 
--      @account_name = 'CMMS Admin', 
--      @sequence_number = 1 ; 
--  END

-------------------------------------------------------------------------------------------------------------------

--Step 08 : To ensure that the tables are created correctly before executing the SELECT statement, 

  --select * from msdb.dbo.sysmail_profile
  --select * from msdb.dbo.sysmail_account
  --select * from msdb.dbo.sysmail_profileaccount

 --Step 09 : (in case they were created incorrectly), you can use the following SQL script. However, 
 --be cautious when performing deletion operations, especially in system databases like msdb
  --delete from msdb.dbo.sysmail_profile
  --delete from msdb.dbo.sysmail_account
  --delete from msdb.dbo.sysmail_profileaccount

  --update sysmail_profileaccount set sequence_number = 2 where profile_id = 2 and account_id = 2


-----------------------------------------------------------------------------------------------------------------------
 --Step 10 : Testing email : The commented-out SQL script you provided is for sending an email via SQL Server's Database Mail feature using the stored procedure sp_send_dbmail.
--    @profile_name = 'CMMS Admin',  
--    @recipients = 'daryl@evantage.com.my',  
--    @body = 'Voila..!! This email has been sent from SQL Server Express Edition.',  
--    @subject = 'Voila..!! This email has been sent from SQL Server Express Edition.' ;

---------------------------------------------------------------------------------------------------------------------------


