IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PV_IRSaveBadgeQuestionsWithAnswersAndContent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].PV_IRSaveBadgeQuestionsWithAnswersAndContent
GO
PRINT 'Creating procedure PV_IRSaveBadgeQuestionsWithAnswersAndContent';
GO

CREATE PROCEDURE [dbo].PV_IRSaveBadgeQuestionsWithAnswersAndContent
    @BadgeDefinitionID BIGINT,
    @ContentDataXML XML,
    @BadgeContentType TINYINT,
    @Result INT OUTPUT
AS
BEGIN
    SET @Result = 0

    DECLARE @ContentData TABLE
    (
        QuestionID BIGINT,
        QuestionText NVARCHAR(MAX),
        QuestionType TINYINT,
        QuestionImgID BIGINT
    )

    DECLARE @ContentAnswersData TABLE
    (
        QuestionID BIGINT,
        AnswerID BIGINT NULL,
        AnswerText NVARCHAR(MAX) NULL,
        AnswerPoints INT NULL
    )

    INSERT INTO @ContentData (QuestionID, QuestionText, QuestionType, QuestionImgID)
    SELECT Tab.Col.value('ID[1]', 'BIGINT') as QuestionID,
        Tab.Col.value('Text[1]', 'NVARCHAR(MAX)') as QuestionText,
        Tab.Col.value('Type[1]', 'TINYINT') as QuestionType,
        Tab.Col.value('ImgID[1]', 'BIGINT') as QuestionImgID
    FROM @ContentDataXML.nodes('/ArrayOfBadgeQuestionAnswersDto/BadgeQuestionAnswersDto') as Tab(Col)

    UPDATE cd --cd.QuestionImgID = 0 przy parsowaniu null przekształca na 0 i trzeba odwrucic
    SET cd.QuestionImgID = NULL
    FROM @ContentData as cd
    WHERE cd.QuestionImgID = 0

    INSERT INTO @ContentAnswersData (QuestionID, AnswerID, AnswerText, AnswerPoints)
    SELECT Tab.Col.value('ID[1]', 'BIGINT') as QuestionID,
        Tab1.Col1.value('ID[1]', 'BIGINT') as AnswerID,
        Tab1.Col1.value('Text[1]', 'NVARCHAR(MAX)') as AnswerText,
        Tab1.Col1.value('Points[1]', 'INT') as AnswerPoints
    FROM @ContentDataXML.nodes('/ArrayOfBadgeQuestionAnswersDto/BadgeQuestionAnswersDto') as Tab(Col)
        OUTER APPLY Tab.Col.nodes('Answers/BadgeQuestionAnswerDto') as Tab1(Col1)

    -- delete not existing rows -------------------------------------------------------------------------

    UPDATE bq
    SET bq.IsDeleted = 1,
        bq.DeleteDateUTC = GETUTCDATE(),
        bq.ModificationDateUTC = GETUTCDATE()
    FROM IRBadgeQuestion as bq
        LEFT JOIN @ContentData as cd ON cd.QuestionID = bq.ID
    WHERE cd.QuestionID IS NULL
        AND bq.IsDeleted = 0
        AND bq.IRBadgeDefinitionID = @BadgeDefinitionID
        AND bq.[Type] = @BadgeContentType

    IF @@ERROR<>0 GOTO ERROR_PROC;

    UPDATE baq
    SET baq.IsDeleted = 1,
        baq.DeleteDateUTC = GETUTCDATE(),
        baq.ModificationDateUTC = GETUTCDATE()
    FROM IRBadgeAnswerQuestion as baq
        INNER JOIN IRBadgeQuestion as bq ON baq.IRBadgeQuestionID = bq.ID
    WHERE baq.IsDeleted = 0
        AND NOT EXISTS (SELECT 1 FROM @ContentAnswersData as cad WHERE cad.QuestionID = baq.IRBadgeQuestionID AND cad.AnswerID = baq.ID)
        AND bq.IRBadgeDefinitionID = @BadgeDefinitionID
        AND @BadgeContentType = 1 /*GameQuestion = 1*/
        AND bq.[Type] = @BadgeContentType

    IF @@ERROR<>0 GOTO ERROR_PROC;

    -- insert new rows --------------------------------------------------------------------------------

    IF EXISTS (SELECT 1 FROM @ContentAnswersData) AND @BadgeContentType = 1 /*GameQuestion = 1*/
    BEGIN
        DECLARE @NewContentData TABLE
        (
            Idx INT IDENTITY(1,1),
            QuestionID BIGINT,
            QuestionText NVARCHAR(MAX),
            QuestionType TINYINT,
           QuestionImgID BIGINT,
            NewQuestionID BIGINT
        )
        INSERT INTO @NewContentData (QuestionID, QuestionText, QuestionType, QuestionImgID, NewQuestionID)
        SELECT QuestionID, QuestionText, QuestionType, QuestionImgID, NULL
        FROM @ContentData
        WHERE QuestionID < 1

        DECLARE @i INT = 1
        DECLARE @max INT = (SELECT COUNT(1) FROM @NewContentData)
        DECLARE @id BIGINT

        WHILE @i <= @max
        BEGIN
            -- insert new question
            INSERT INTO IRBadgeQuestion (IRBadgeDefinitionID, [Text], ModificationDateUTC, CreateDateUTC, IsDeleted, DeleteDateUTC, [Type], IRFileID)
            SELECT @BadgeDefinitionID, cd.QuestionText, GETUTCDATE(), GETUTCDATE(), 0, NULL, cd.QuestionType, cd.QuestionImgID
            FROM @NewContentData as cd
            WHERE cd.Idx = @i

            IF @@ERROR<>0 GOTO ERROR_PROC;

            SET @id = @@IDENTITY

            -- get new ID
            UPDATE @NewContentData
            SET NewQuestionID = @id
            WHERE Idx = @i

            SET @i = @i + 1
        END

        IF @@ERROR<>0 GOTO ERROR_PROC;

        -- insert new answers
        INSERT INTO IRBadgeAnswerQuestion (IRBadgeQuestionID, [Text], Points, ModificationDateUTC, CreateDateUTC, IsDeleted, DeleteDateUTC)
        SELECT ncd.NewQuestionID, cad.AnswerText, cad.AnswerPoints, GETUTCDATE(), GETUTCDATE(), 0, NULL
        FROM @ContentAnswersData as cad
            INNER JOIN @NewContentData as ncd ON cad.QuestionID = ncd.QuestionID
        WHERE cad.AnswerID < 1
            AND cad.QuestionID < 1
            AND ncd.NewQuestionID IS NOT NULL

        IF @@ERROR<>0 GOTO ERROR_PROC;

        INSERT INTO IRBadgeAnswerQuestion (IRBadgeQuestionID, [Text], Points, ModificationDateUTC, CreateDateUTC, IsDeleted, DeleteDateUTC)
        SELECT cad.QuestionID, cad.AnswerText, cad.AnswerPoints, GETUTCDATE(), GETUTCDATE(), 0, NULL
        FROM @ContentAnswersData as cad
        WHERE cad.AnswerID < 1
            AND cad.QuestionID > 0

        IF @@ERROR<>0 GOTO ERROR_PROC;
    END
    ELSE
    BEGIN
        INSERT INTO IRBadgeQuestion (IRBadgeDefinitionID, [Text], ModificationDateUTC, CreateDateUTC, IsDeleted, DeleteDateUTC, [Type], IRFileID)
        SELECT @BadgeDefinitionID, cd.QuestionText, GETUTCDATE(), GETUTCDATE(), 0, NULL, cd.QuestionType, cd.QuestionImgID
        FROM @ContentData as cd
            LEFT JOIN IRBadgeQuestion bq ON bq.IRBadgeDefinitionID = @BadgeDefinitionID
                                                     AND bq.Text = cd.QuestionText
                                                     AND bq.Type = @BadgeContentType
                                                     AND bq.IsDeleted = 0
        WHERE cd.QuestionID < 1
                AND bq.ID IS NULL
                AND ISNULL(cd.QuestionText, '') != ''

        IF @@ERROR<>0 GOTO ERROR_PROC;
    END

    -- update existing rows -----------------------------------------------------------------------------

    UPDATE bq
    SET bq.[Text] = cd.QuestionText,
        bq.[Type] = cd.QuestionType,
        bq.ModificationDateUTC = GETUTCDATE(),
        bq.IRFileID = cd.QuestionImgID
    FROM IRBadgeQuestion as bq
        INNER JOIN @ContentData as cd ON cd.QuestionID = bq.ID
    WHERE (
            ISNULL(bq.[Text], '') <> cd.QuestionText
            OR bq.[Type] <> cd.QuestionType
            OR bq.IRFileID <> cd.QuestionImgID
        )
        AND bq.IsDeleted = 0

    IF @@ERROR<>0 GOTO ERROR_PROC;

    UPDATE bq
    SET bq.ModificationDateUTC = GETUTCDATE()
    FROM IRBadgeAnswerQuestion as baq
        INNER JOIN @ContentAnswersData as cad ON cad.AnswerID = baq.ID
        INNER JOIN IRBadgeQuestion as bq ON bq.ID = baq.IRBadgeQuestionID
    WHERE (
            ISNULL(baq.[Text], '') <> cad.AnswerText
            OR baq.Points <> cad.AnswerPoints
        )
        AND baq.IsDeleted = 0

    IF @@ERROR<>0 GOTO ERROR_PROC;

    UPDATE baq
    SET baq.[Text] = cad.AnswerText,
        baq.Points = cad.AnswerPoints,
        baq.ModificationDateUTC = GETUTCDATE()
    FROM IRBadgeAnswerQuestion as baq
        INNER JOIN @ContentAnswersData as cad ON cad.AnswerID = baq.ID
    WHERE (
            ISNULL(baq.[Text], '') <> cad.AnswerText
            OR baq.Points <> cad.AnswerPoints
        )
        AND baq.IsDeleted = 0

    IF @@ERROR<>0 GOTO ERROR_PROC;

    SET @Result = 1

    --------------------------------------------------------------------------
    -- exit point of the store proc
    EXIT_PROC:
    RETURN(0);

    ERROR_PROC:
    RETURN(99);
END;
GO
-- end PV_IRSaveBadgeQuestionsWithAnswersAndContent