Untitled

CREATE PROCEDURE [dbo].[spAddOrUpdateMDMTracking]
    @ManufacturerId INT,
    @SiteId INT,
    @Type NVARCHAR(50),
    @Status NVARCHAR(50),
    @StatusCode INT,
    @ApiResult NVARCHAR(50),
    @ApiRetryDateTime DATETIME2 NULL,
    @ResponseErrorMessage NVARCHAR(MAX),
    @CreatedAt DATETIME2 = NULL -- New column for timestamp
AS
BEGIN
    SET NOCOUNT ON;

    -- If CreatedAt is not provided, use the current UTC timestamp
    IF @CreatedAt IS NULL
    BEGIN
        SET @CreatedAt = GETUTCDATE();
    END

    -- Check if the row already exists for the provided ManufacturerId, SiteId, and Type
    IF EXISTS (SELECT 1 FROM MDMRequestTracking 
               WHERE ManufacturerId = @ManufacturerId 
               AND SiteId = @SiteId 
               AND Type = @Type)
    BEGIN
        -- Update the existing record
        UPDATE MDMRequestTracking
        SET
            Status = @Status,
            StatusCode = @StatusCode, -- Added StatusCode
            ApiResult = @ApiResult,
            ApiRetryDateTime = @ApiRetryDateTime,
            ResponseErrorMessage = @ResponseErrorMessage,
            CreatedAt = @CreatedAt -- Update the CreatedAt field
        WHERE
            ManufacturerId = @ManufacturerId
            AND SiteId = @SiteId
            AND Type = @Type;
    END
    ELSE
    BEGIN
        -- Insert a new record
        INSERT INTO MDMRequestTracking (
            ManufacturerId,
            SiteId,
            Type,
            Status,
            StatusCode, -- Added StatusCode
            ApiResult,
            ApiRetryDateTime,
            ResponseErrorMessage,
            CreatedAt -- Insert the CreatedAt field
        )
        VALUES (
            @ManufacturerId,
            @SiteId,
            @Type,
            @Status,
            @StatusCode, -- Added StatusCode
            @ApiResult,
            @ApiRetryDateTime,
            @ResponseErrorMessage,
            @CreatedAt -- Store the CreatedAt field
        );
    END
END