CREATE PROCEDURE [dbo].[UpdateConversationsByName]
  @CampaignId INT,
  @MeterLevelId INT,
  @UserData VARCHAR(MAX),
  @DATA VARCHAR(MAX),
  @LastModifiedBy VARCHAR(50)
AS
BEGIN
    DECLARE @trancount INT;

    SET @trancount = @@trancount;

    BEGIN TRY
    IF @trancount = 0
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION UpdateConversationsByName;

    DECLARE @C TABLE (
      id INT,
      Name VARCHAR(MAX),
      [ORDER] INT
    );

    DECLARE @COV TABLE (
      C_Id INT,
      InstanceNumber INT,
      VALUE VARCHAR(MAX),
      ConversationOptionId INT
    );

    INSERT INTO @C
    SELECT
      id,
      Name,
      [ORDER]
    FROM
      OPENJSON(@DATA) WITH (
        id INT '$.id',
        Name VARCHAR(MAX) '$.Name',
        [ORDER] INT '$.Order'
      );

    INSERT INTO @COV
    SELECT
      C_Id,
      InstanceNumber,
      VALUE,
      ConversationOptionId
    FROM
      OPENJSON(@DATA) WITH (
        C_Id INT '$.id',
        ConversationOptionValues NVARCHAR(MAX) '$.ConversationOptionValues' AS json
      )
      OUTER APPLY OPENJSON(ConversationOptionValues) WITH (
        InstanceNumber NVARCHAR(MAX) '$.InstanceNumber',
        VALUE VARCHAR(MAX) '$.Value',
        ConversationOptionId INT '$.ConversationOptionId'
      );

    DECLARE @A TABLE (
      C_Id INT,
      id INT,
      Name VARCHAR(MAX),
      Description VARCHAR(MAX),
      Active BIT,
      ActionTypeId INT,
      [ORDER] INT
    );

    DECLARE @AOV TABLE (
      A_Id VARCHAR(MAX),
      CriteriaInstanceNumber INT,
      VALUE VARCHAR(MAX),
      ActionOptionId INT
    );

    INSERT INTO @A
    SELECT
      C_Id,
      id,
      Name,
      Description,
      Active,
      ActionTypeId,
      [ORDER]
    FROM
      OPENJSON(@DATA) WITH (
        C_Id INT '$.id',
        Actions NVARCHAR(MAX) '$.Actions' AS json
      )
      OUTER APPLY OPENJSON(Actions) WITH (
        id INT '$.id',
        Name NVARCHAR(MAX) '$.Name',
        Description NVARCHAR(MAX) '$.Description',
        Active BIT '$.Active',
        ActionTypeId INT '$.ActionTypeId',
        [ORDER] INT '$.Order'
      );

    INSERT INTO @AOV
    SELECT
      A_Id,
      CriteriaInstanceNumber,
      VALUE,
      ActionOptionId
    FROM
      OPENJSON(@DATA) WITH (
        Actions NVARCHAR(MAX) '$.Actions' AS json
      )
      OUTER APPLY OPENJSON(Actions) WITH (
        A_Id INT '$.id',
        ActionOptionValues NVARCHAR(MAX) '$.ActionOptionValues' AS json
      )
      OUTER APPLY OPENJSON(ActionOptionValues) WITH (
        CriteriaInstanceNumber INT '$.CriteriaInstanceNumber',
        VALUE VARCHAR(MAX) '$.Value',
        ActionOptionId INT '$.ActionOptionId'
      );

    DECLARE @UD TABLE (
      Name VARCHAR(MAX),
      Overwrite BIT,
      id INT,
      CurrentMax INT, -- + Offset new for incrementing _New% because we can't get last index in merge/insert
      Offset INT,
      _Order INT IDENTITY(1, 1) -- need for case when multiple update of same action, so we need update only by latest in json (not latest id)
      );

    INSERT INTO @UD
    SELECT
      Name,
      Overwrite,
      id,
      CurrentMax = (
        -- find action with same name + _New% format and parse this number in end. if it not exits set 0
        SELECT
          ISNULL(
            MAX(
              TRY_CONVERT(
                INT,
                SUBSTRING(
                  C.Name,
                  CHARINDEX('_New', C.Name) + LEN('_New'),
                  LEN(C.Name)
                )
              )
            ),
            0
          )
        FROM
          Conversation C
        WHERE
          CampaignId = @CampaignId
          AND MeterLevelId = @MeterLevelId
      ),
      Offset = (
        -- increment in group of actions with same name
        ROW_NUMBER() OVER (
          PARTITION BY Name
          ORDER BY
            Name
        )
      )
    FROM
      OPENJSON(@UserData) WITH (
        Name NVARCHAR(MAX) '$.Name',
        Overwrite BIT '$.Overwrite',
        id INT '$.id'
      );
    -- need for case when action not exist and we try insert multiple with same name
    UPDATE
      @UD
    SET
      Offset = Offset -1
    WHERE
      CurrentMax = 0
      AND Name NOT IN ( -- case when insert 1 by 1 actions with yet existing same name
        SELECT
          Name
        FROM
          Conversation C
        WHERE
          C.CampaignId = @CampaignId
          AND C.MeterLevelId = @MeterLevelId
      );

    -- delete from @AOV, @A, @COV, @C rows which will not be used in multiple update of one row (without latest)
    DELETE FROM
      @AOV
    WHERE
      A_Id IN (
        SELECT
          id
        FROM
          @A
        WHERE
          C_Id IN (
            SELECT
              id
            FROM
              @UD
            WHERE
              Overwrite = 1
              AND _Order NOT IN (
                SELECT
                  MAX(_Order)
                FROM
                  @UD
                GROUP BY
                  Name
              )
          )
      );

    DELETE FROM
      @A
    WHERE
      C_Id IN (
        SELECT
          id
        FROM
          @UD
        WHERE
          Overwrite = 1
          AND _Order NOT IN (
            SELECT
              MAX(_Order)
            FROM
              @UD
            GROUP BY
              Name
          )
      );

    DELETE FROM
      @COV
    WHERE
      C_Id IN (
        SELECT
          id
        FROM
          @UD
        WHERE
          Overwrite = 1
          AND _Order NOT IN (
            SELECT
              MAX(_Order)
            FROM
              @UD
            GROUP BY
              Name
          )
      );

    DELETE FROM
      @C
    WHERE
      id IN (
        SELECT
          id
        FROM
          @UD
        WHERE
          Overwrite = 1
          AND _Order NOT IN (
            SELECT
              MAX(_Order)
            FROM
              @UD
            GROUP BY
              Name
          )
      );

    DECLARE @MatchTableC TABLE (OriginalId INT, InsertedId INT);

    DECLARE @MatchTableA TABLE (OriginalId INT, InsertedId INT);

    MERGE INTO Conversation AS tgt
    USING @C AS src
    ON tgt.Name = src.Name
    AND tgt.CampaignId = @CampaignId
    AND tgt.MeterLevelId = @MeterLevelId
    AND (
      SELECT
        Overwrite
      FROM
        @UD
      WHERE
        id = src.id
    ) = 1
    WHEN MATCHED
    THEN
    UPDATE
    SET
      CampaignId = @CampaignId,
      MeterLevelId = @MeterLevelId,
      [ORDER] = src.[ORDER],
      LastModifiedBy = @LastModifiedBy,
      LastModifiedDate = GETDATE()
    WHEN NOT MATCHED
    THEN
    INSERT (
        CampaignId, MeterLevelId, Name, [ORDER],
        LastModifiedBy, LastModifiedDate
      )
    VALUES
      (
        @CampaignId,
        @MeterLevelId,
        CASE WHEN (
          SELECT
            CurrentMax + Offset -- if 0 - not exist, else increment _New%
          FROM
            @UD
          WHERE
            id = src.id
        ) > 0 THEN CONCAT (
          src.Name,
          '_New',
          (
            SELECT
              CurrentMax + Offset
            FROM
              @UD
            WHERE
              id = src.id
          )
        ) ELSE src.Name END,
        src.[ORDER],
        @LastModifiedBy,
        GETDATE()
      )
      OUTPUT
      src.id,
      inserted.id
      INTO @MatchTableC;

    DELETE FROM
      ConversationOptionValue
    WHERE
      ConversationId IN (
        SELECT
          InsertedId
        FROM
          @MatchTableC
      );

    INSERT INTO ConversationOptionValue (
      ConversationId,
      ConversationOptionId,
      VALUE,
      InstanceNumber,
      LastModifiedBy,
      LastModifiedDate
    )
    SELECT
      m.InsertedId,
      p.ConversationOptionId,
      p.VALUE,
      p.InstanceNumber,
      @LastModifiedBy,
      GETDATE()
    FROM
      @COV p
      LEFT JOIN @MatchTableC m ON p.C_Id = m.OriginalId;

    DELETE FROM
      ActionOptionValue
    WHERE
      ActionId IN (
        SELECT
          id
        FROM
          Action
        WHERE
          ConversationId IN (
            SELECT
              InsertedId
            FROM
              @MatchTableC
          )
      );

    DELETE FROM
      Action
    WHERE
      ConversationId IN (
        SELECT
          InsertedId
        FROM
          @MatchTableC
      );

    MERGE INTO Action AS tgt
    USING @A AS src
    ON 1 = 0
    WHEN NOT MATCHED
    THEN
    INSERT (
      Name,
      Description,
      Active,
      ActionTypeId,
      [ORDER],
      ConversationId,
      LastModifiedBy,
      LastModifiedDate
    )
    VALUES
      (
        src.Name,
        src.Description,
        src.Active,
        src.ActionTypeId,
        src.[ORDER],
        (
          SELECT
            InsertedId
          FROM
            @MatchTableC
          WHERE
            OriginalId = src.C_Id
        ),
        @LastModifiedBy,
        GETDATE()
      )
      OUTPUT
      src.id,
      inserted.id
      INTO @MatchTableA;

    INSERT INTO ActionOptionValue (
      ActionId,
      ActionOptionId,
      VALUE,
      CriteriaInstanceNumber,
      LastModifiedBy,
      LastModifiedDate
    )
    SELECT
      m.InsertedId,
      p.ActionOptionId,
      p.VALUE,
      p.CriteriaInstanceNumber,
      @LastModifiedBy,
      GETDATE()
    FROM
      @AOV p
      LEFT JOIN @MatchTableA m ON p.A_Id = m.OriginalId;

    lbexit:
    IF @trancount = 0
        COMMIT;
    END TRY
    BEGIN CATCH
        DECLARE @error INT,
            @message VARCHAR(4000),
            @xstate INT,
            @errorline VARCHAR(4);

        SELECT @error = ERROR_NUMBER(),
            @message = ERROR_MESSAGE(),
            @xstate = XACT_STATE(),
            @errorline = CAST(ERROR_LINE() AS VARCHAR(16));

        SET @message = @errorline + ': ' + @message

        IF @xstate = - 1
            ROLLBACK;
        IF @xstate = 1
            AND @trancount = 0
            ROLLBACK;
        IF @xstate = 1
            AND @trancount > 0
            ROLLBACK TRANSACTION ImportCampaignsFast;
        RAISERROR (
                'UpdateConversationsByName: %d: %s',
                16,
                1,
                @error,
                @message
                );
    END CATCH
END