-- Add ResetToken column to Users table if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND name = 'ResetToken')
BEGIN
    ALTER TABLE [dbo].[Users]
    ADD [ResetToken] nvarchar(100) NULL,
        [ResetTokenExpiry] datetime NULL
END

-- Create stored procedure for storing reset tokens
CREATE OR ALTER PROCEDURE [dbo].[USERS_spSTORE_RESET_TOKEN]
    @UserEmail nvarchar(100),
    @ResetToken nvarchar(100),
    @ExpiryDate datetime
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE [dbo].[Users]
    SET [ResetToken] = @ResetToken,
        [ResetTokenExpiry] = @ExpiryDate
    WHERE [UserEmail] = @UserEmail;
    
    SELECT @@ROWCOUNT AS AffectedRows;
END

-- Modify existing USERS_spACTIVATE to return user details
CREATE OR ALTER PROCEDURE [dbo].[USERS_spACTIVATE]
(
    @activationcode uniqueidentifier
)
AS
    DECLARE @userid int = 0;
    DECLARE @active bit = 0;
    DECLARE @UserEmail nvarchar(100);
    DECLARE @FirstName nvarchar(50);
    DECLARE @LastName nvarchar(50);

BEGIN
    SET NOCOUNT ON;

    SELECT @userid = [userid],
           @active = active,
           @UserEmail = [UserEmail],
           @FirstName = [ContactFirstName],
           @LastName = [ContactLastName]
    FROM [USERS]
    WHERE [activationcode] = @activationcode;

    IF @userid > 0 AND ISNULL(@active,0) = 0 
    BEGIN
        --ACTIVATE USER
        UPDATE USERS 
        SET [active] = 1 
        WHERE [activationcode] = @activationcode;

        -- Return success message and user details for email
        SELECT 
            'Successfully activated' AS 'Status',
            @UserEmail AS 'UserEmail',
            @FirstName AS 'FirstName',
            @LastName AS 'LastName';
    END
    ELSE IF @userid > 0 AND ISNULL(@active,0) = 1
    BEGIN
        SELECT 'User already active' as 'ERROR'
    END
    ELSE
    BEGIN
        SELECT 'No account found to activate' as 'ERROR'
    END
END

-- Modify USERS_spFORGOT to work with EmailService
CREATE OR ALTER PROCEDURE [dbo].[USERS_spFORGOT]
    @EMAIL VARCHAR(250)
AS
DECLARE @USER_ID AS INT;
DECLARE @USERNAME AS VARCHAR(150);
DECLARE @ACTIVE AS BIT;
DECLARE @RESET_TOKEN AS VARCHAR(50);
DECLARE @RESET_EXPIRY AS DATETIME;
BEGIN
    SET NOCOUNT ON;
    
    SELECT @USER_ID = [UserId],
           @ACTIVE = ACTIVE,
           @USERNAME = [ContactFirstName]
    FROM   [USERS]
    WHERE  [UserEmail] = @EMAIL;

    IF @USER_ID IS NULL
    BEGIN
        SELECT 'False' AS 'result', 'Email address does not exist' AS 'message';
    END
    ELSE IF @ACTIVE IS NULL OR @ACTIVE = 0
    BEGIN
        SELECT 'False' AS 'result', 'Account is not active' AS 'message';
    END
    ELSE
    BEGIN
        SET @RESET_TOKEN = CAST(NEWID() AS VARCHAR(50));
        SET @RESET_EXPIRY = DATEADD(HOUR, 24, GETDATE());
        
        -- Store reset token
        UPDATE [USERS]
        SET    [ResetToken] = @RESET_TOKEN,
               [ResetTokenExpiry] = @RESET_EXPIRY
        WHERE  UserId = @USER_ID;
        
        -- Return data needed for email
        SELECT 
            'True' AS 'result',
            @USERNAME AS 'FirstName',
            @EMAIL AS 'UserEmail',
            @RESET_TOKEN AS 'ResetToken';
    END
END

-- Modify USERS_spREGISTER to remove email sending
CREATE OR ALTER PROCEDURE [dbo].[USERS_spREGISTER]
(
    @ContactFirstName varchar(50),
    @ContactLastName varchar(50),
    @CompanyName varchar(255),
    @DSVAccount varchar(50),
    @UserEmail varchar(150),
    @Password varchar(50),
    @SalesRegion varchar(50) = null
)
AS
    DECLARE @userid int = 0;
    DECLARE @activationcode uniqueidentifier;
    DECLARE @URL varchar(150) = '';

BEGIN
    SET NOCOUNT ON;

    --Check if @DSVAccount is IHS account.
    IF EXISTS(SELECT 1 FROM [DEBDRM50] WHERE [Imp_Link] = @DSVAccount)
    BEGIN
        SELECT @DSVAccount = [DSVCode] FROM [DEBDRM50] WHERE [Imp_Link] = @DSVAccount
    END

    IF @DSVAccount NOT LIKE '%/%'
    BEGIN
        SELECT @DSVAccount = @DSVAccount + '/000'
    END

    SELECT @userid=[userid] FROM [USERS]
    WHERE [UserEmail] = @UserEmail;

    IF @userid > 0 
    BEGIN
        -- USER EXIST
        SELECT 'Email address already registered' as 'ERROR'
    END
    ELSE
    BEGIN
        SET @activationcode = NEWID();

        IF EXISTS(SELECT 1 FROM [USERS] WHERE [DSVAccount] = @DSVAccount and Imported = 1)
        BEGIN
            --UPDATE
            SELECT @userid = [userid] FROM [USERS] WHERE [DSVAccount] = @DSVAccount

            UPDATE [USERS] SET 
                [ContactFirstName] = @ContactFirstName,
                [ContactLastName] = @ContactLastName,
                [CompanyName] = @CompanyName,
                [DSVAccount] = @DSVAccount,
                [UserEmail]  = @UserEmail,
                [Password] = @Password,
                [activationcode] = @activationcode,
                [accesslevel] = 2,
                [signupdate] = GETDATE(),
                [SalesRegion] = @SalesRegion,
                [active] = 0
            WHERE [userid] = @userid
        END
        ELSE
        BEGIN
            --REGISTER
            INSERT INTO [USERS] ([ContactFirstName],[ContactLastName],[CompanyName],[DSVAccount],[UserEmail],[Password],[activationcode],[accesslevel],[signupdate],[SalesRegion]) 
            VALUES (@ContactFirstName,@ContactLastName,@CompanyName,@DSVAccount,@UserEmail,@Password,@activationcode,2, GETDATE(),@SalesRegion);
            
            SELECT @userid = SCOPE_IDENTITY();
        END

        IF @userid > 0 
        BEGIN
            -- Get activation URL
            SELECT TOP 1 @URL = dbo.[fnConfig]('URL') + '/activate.aspx?g=' + CAST(@activationcode as varchar(50));

            -- Return data needed for email
            SELECT 
                @userid AS 'UserId',
                @UserEmail AS 'UserEmail',
                @ContactFirstName AS 'FirstName',
                @ContactLastName AS 'LastName',
                @URL AS 'ActivationUrl',
                @activationcode AS 'ActivationCode';
        END
        ELSE
        BEGIN
            SELECT 'Unable to register new account' as 'ERROR';
        END
    END
END 