
/****** Object:  StoredProcedure [dbo].[sp_cmms_reset_password]    Script Date: 09/02/2026 9:25:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[sp_cmms_reset_password]
    @Site       VARCHAR(4),
    @LoginID    NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NewPassword NVARCHAR(50),
            @Email NVARCHAR(255),
            @Subject NVARCHAR(200),
            @Body NVARCHAR(MAX),
            @SQL NVARCHAR(MAX),
            @Now DATETIME = GETDATE(),
            @SendStatus NVARCHAR(50),
            @SendMsg NVARCHAR(MAX),
            @dbmail_profile NVARCHAR(100);

    -- 1️⃣ Generate a complex 10-character password
    ;WITH RandomChars AS (
        SELECT TOP 10 
            CHAR(
                CASE 
                    WHEN number % 4 = 0 THEN 48 + ABS(CHECKSUM(NEWID())) % 10       -- Numbers
                    WHEN number % 4 = 1 THEN 65 + ABS(CHECKSUM(NEWID())) % 26       -- Uppercase
                    WHEN number % 4 = 2 THEN 97 + ABS(CHECKSUM(NEWID())) % 26       -- Lowercase
                    ELSE 33 + ABS(CHECKSUM(NEWID())) % 15                           -- Special chars
                END
            ) AS ch
        FROM master.dbo.spt_values
        WHERE type = 'P'
    )
    SELECT @NewPassword = STRING_AGG(ch, '') 
    FROM RandomChars;

    -- 2️⃣ Change the SQL Login password
    SET @SQL = N'ALTER LOGIN [' + @LoginID + N'] WITH PASSWORD = ''' + @NewPassword + N'''';
    EXEC sp_executesql @SQL;

    -- 3️⃣ Get the email and site_cd
    SELECT  @Email  = emp_det_email_id
    FROM    emp_mst (NOLOCK)
            JOIN emp_det (NOLOCK)
              ON emp_mst.site_cd = emp_det.site_cd
             AND emp_mst.rowid   = emp_det.mst_rowid
    WHERE   emp_mst_login_id = @LoginID
	AND		emp_mst.site_cd = @Site;

    IF @Email IS NULL
    BEGIN
        RAISERROR('No email found for the given Login ID in emp_mst.', 16, 1);
        RETURN;
    END

    -- 4️⃣ Check if dft_mst_dbmail_profile column exists
    IF EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'dft_mst'
          AND COLUMN_NAME = 'dft_mst_dbmail_profile'
    )
    BEGIN
        SELECT @dbmail_profile = COALESCE(dft_mst_dbmail_profile, 'CAFMS Administrator')
        FROM dft_mst (NOLOCK)
        WHERE site_cd = @Site;
    END
    ELSE
    BEGIN
        SET @dbmail_profile = 'CMMS Admin';
    END

    -- 5️⃣ Prepare HTML email content
    SET @Subject = 'CMMS Login Password Reset Notification';
    SET @Body = 
    '<html>
        <body style="font-family: Arial, sans-serif; font-size: 14px; color: #333;">
            <h2 style="color: #2E86C1;">CMMS Password Reset</h2>
            <p>Dear User,</p>
            <p>Your Login password has been reset as per your request. Please find the details below:</p>
            <table border="0" cellpadding="5" cellspacing="0" style="border-collapse: collapse;">
                <tr>
                    <td><strong>Login ID:</strong></td>
                    <td>' + @LoginID + '</td>
                </tr>
                <tr>
                    <td><strong>New Password:</strong></td>
                    <td style="color: red; font-weight: bold;">' + @NewPassword + '</td>
                </tr>
            </table>
            <p>For security reasons, please change your password upon your next login.</p>
            <p>If you did not request this change, contact the CMMS administrator immediately.</p>
            <br/>
            <p>Regards,<br/><strong>CMMS Administrator</strong></p>
            <hr/>
            <small>This is an automated email. Please do not reply.</small>
        </body>
    </html>';

    -- 6️⃣ Send email
    BEGIN TRY
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = @dbmail_profile,
            @recipients   = @Email,
            @subject      = @Subject,
            @body         = @Body,
            @body_format  = 'HTML';

        SET @SendStatus = 'S';
        SET @SendMsg = 'The mail has been successfully sent.';
    END TRY
    BEGIN CATCH
        SET @SendStatus = 'FAILED';
        SET @SendMsg = ERROR_MESSAGE();
    END CATCH

	SELECT @Body = REPLACE(@Body, @NewPassword, '<**New Password**>')

    -- 7️⃣ Log into eml_log
    INSERT eml_log (
        site_cd,
        eml_log_from,
        eml_log_to_email,
        eml_log_cc_email,
        eml_log_bcc_email,
        eml_log_subject,
        eml_log_content,
        eml_log_date,
        eml_log_event,
        eml_log_msg,
        eml_log_status,
        audit_user,
        audit_date
    )
    VALUES (
        @Site,
        @dbmail_profile,
        @Email,
        NULL,
        NULL,
        @Subject,
        @Body,
        @Now,
        'SQL Password Reset',
        @SendMsg,
        @SendStatus,
        SYSTEM_USER,
        @Now
    );
END
GO


