---

USE myNewVasya;

UPDATE r
SET r.otzyv = s.scoreOtzyv
FROM dbo.sb_reports AS r
JOIN dbo.dat_mts_scores AS s
    ON s.kodReport = r.kod
   AND s.kodMember = r.kodMember
JOIN dbo.dat_members AS m
    ON m.kod = s.kodMember
   AND m.kodMerop = s.kodMerop
JOIN dbo.dat_activity AS a
    ON a.kod = s.kodMerop
WHERE a.dtMerop >= CONVERT(datetime, '20260922', 112)
  AND r.a1 LIKE N'otzyvMTS[_]%'
  AND NULLIF(LTRIM(RTRIM(s.scoreOtzyv)), N'') IS NOT NULL
  AND (
      r.otzyv IS NULL
      OR r.otzyv <> s.scoreOtzyv
      OR DATALENGTH(r.otzyv) <> DATALENGTH(s.scoreOtzyv)
  );

SELECT @@ROWCOUNT AS updatedReports;   

USE [myNewVasya];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE [dbo].[VI5_0_1pr8_1]
    @BatchSize int = 200
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @BatchSize < 1 OR @BatchSize > 5000
        THROW 51010, 'BatchSize must be between 1 and 5000', 1;

    -- Сопоставляем ответы с участниками.
    -- UNION удаляет повторное совпадение по двум ключам.
    SELECT a.kod AS answerKod, m.kod AS kodMember
    INTO #Pairs
    FROM dbo.sb_answer AS a
    JOIN dbo.dat_members AS m
        ON m.kod = a.kodMember

    UNION

    SELECT a.kod, m.kod
    FROM dbo.sb_answer AS a
    JOIN dbo.dat_members AS m
        ON m.participantId = a.memberId;

    CREATE UNIQUE CLUSTERED INDEX IX_Pairs
        ON #Pairs(kodMember, answerKod);

    -- Сохраняем прежний порядок:
    -- сначала последний ответ, затем отбор открытых волн.
    ;WITH ranked AS
    (
        SELECT
            p.kodMember,
            a.kod AS answerKod,
            a.kodMerop,
            a.labelF,
            a.answer,
            ROW_NUMBER() OVER
            (
                PARTITION BY p.kodMember, a.labelF
                ORDER BY a.kod DESC
            ) AS rn
        FROM #Pairs AS p
        JOIN dbo.sb_answer AS a
            ON a.kod = p.answerKod
    )
    SELECT
        r.kodMember,
        r.answerKod,
        r.labelF,
        r.answer
    INTO #Latest
    FROM ranked AS r
    JOIN dbo.dat_activity AS a
        ON a.kod = r.kodMerop
    JOIN dbo.spr_wave AS w
        ON w.kod = a.kodWave
    WHERE r.rn = 1
      AND (w.kodProject = 8 OR w.kodProject >= 11)
      AND w.[close] = 0;

    CREATE CLUSTERED INDEX IX_Latest
        ON #Latest(kodMember, answerKod);

    SELECT DISTINCT
        l.kodMember,
        l.answerKod,
        s.nmSubject
    INTO #Mapped
    FROM #Latest AS l
    JOIN dbo.spr_pollsToMind AS p
        ON p.textPoll = l.labelF
    JOIN dbo.spr_pollsSubject AS s
        ON s.kod = p.kodSubject;

    CREATE CLUSTERED INDEX IX_Mapped
        ON #Mapped(kodMember, answerKod);

    SELECT DISTINCT kodMember
    INTO #Members
    FROM #Mapped;

    CREATE UNIQUE CLUSTERED INDEX IX_Members
        ON #Members(kodMember);

    CREATE TABLE #Batch
    (
        kodMember int NOT NULL PRIMARY KEY
    );

    DECLARE
        @lastMember int = NULL,
        @ID int,
        @nmSubject nvarchar(50),
        @nmField nvarchar(50),
        @targetTable nvarchar(128),
        @valueExpression nvarchar(100),
        @sql nvarchar(max),
        @batchNo int = 0,
        @memberCount int,
        @changed int,
        @rows int,
        @copiedReviews int;

    WHILE 1 = 1
    BEGIN
        TRUNCATE TABLE #Batch;

        INSERT INTO #Batch(kodMember)
        SELECT TOP (@BatchSize) kodMember
        FROM #Members
        WHERE @lastMember IS NULL
           OR kodMember > @lastMember
        ORDER BY kodMember;

        SET @memberCount = @@ROWCOUNT;

        IF @memberCount = 0
            BREAK;

        SELECT @lastMember = MAX(kodMember)
        FROM #Batch;

        SET @batchNo = @batchNo + 1;
        SET @ID = 0;
        SET @changed = 0;

        WHILE 1 = 1
        BEGIN
            SELECT TOP (1)
                @ID = kod,
                @nmField = nmField,
                @nmSubject = nmSubject,
                @targetTable = targetTable
            FROM dbo.spr_pollsSubject
            WHERE kod > @ID
            ORDER BY kod;

            IF @@ROWCOUNT = 0
                BREAK;

            IF NOT EXISTS
            (
                SELECT 1
                FROM #Mapped AS m
                JOIN #Batch AS b
                    ON b.kodMember = m.kodMember
                WHERE m.nmSubject = @nmSubject
            )
                CONTINUE;

            -- Отзывы сохраняем целиком и в Unicode.
            -- Для остальных полей оставляем прежнее поведение.
            SET @valueExpression =
                CASE
                    WHEN
                        (@targetTable = N'dat_mts_scores'
                         AND @nmField = N'scoreOtzyv')
                        OR
                        (@targetTable = N'sb_reports'
                         AND @nmField = N'otzyv')
                    THEN N'CONVERT(nvarchar(max), l.answer)'
                    ELSE N'CAST(l.answer AS varchar(240))'
                END;

            SET @sql = N'
                UPDATE t
                SET ' + QUOTENAME(@nmField) + N' = '
                    + @valueExpression + N'
                FROM dbo.' + QUOTENAME(@targetTable) + N' AS t
                JOIN #Batch AS b
                    ON b.kodMember = t.kodMember
                JOIN #Mapped AS m
                    ON m.kodMember = b.kodMember
                   AND m.nmSubject = @subject
                JOIN #Latest AS l
                    ON l.kodMember = m.kodMember
                   AND l.answerKod = m.answerKod;

                SET @affected = @@ROWCOUNT;';

            SET @rows = 0;

            EXEC sys.sp_executesql
                @sql,
                N'@subject nvarchar(50), @affected int OUTPUT',
                @subject = @nmSubject,
                @affected = @rows OUTPUT;

            SET @changed = @changed + @rows;
        END;

        RAISERROR
        (
            N'PR8 batch=%d members=%d updated_field_rows=%d',
            10, 1,
            @batchNo, @memberCount, @changed
        ) WITH NOWAIT;
    END;

    -- Копирование отзывов выполняется даже при пустом sb_answer.
    -- Это позволяет перенести ранее обработанные отзывы.
    -- Источник: dat_mts_scores.scoreOtzyv.
    -- Связь строго по отчёту, участнику и мероприятию.
    UPDATE r
    SET r.otzyv = s.scoreOtzyv
    FROM dbo.sb_reports AS r
    JOIN dbo.dat_mts_scores AS s
        ON s.kodReport = r.kod
       AND s.kodMember = r.kodMember
    JOIN dbo.dat_members AS m
        ON m.kod = s.kodMember
       AND m.kodMerop = s.kodMerop
    JOIN dbo.dat_activity AS a
        ON a.kod = s.kodMerop
    WHERE a.dtMerop >= CONVERT(datetime, '20260922', 112)
      AND r.a1 LIKE N'otzyvMTS[_]%'
      AND NULLIF(LTRIM(RTRIM(s.scoreOtzyv)), N'') IS NOT NULL
      AND
      (
          r.otzyv IS NULL
          OR r.otzyv <> s.scoreOtzyv
          OR DATALENGTH(r.otzyv) <> DATALENGTH(s.scoreOtzyv)
      );

    SET @copiedReviews = @@ROWCOUNT;

    RAISERROR
    (
        N'PR8 copied_reviews=%d',
        10, 1,
        @copiedReviews
    ) WITH NOWAIT;
END;
GO

USE [myNewVasya];
GO

DECLARE @kodMember int = 10773311;
DECLARE @kodMerop int = 16461;
DECLARE @participantId nvarchar(250);

SELECT @participantId = m.participantId
FROM dbo.dat_members AS m
WHERE m.kod = @kodMember
  AND m.kodMerop = @kodMerop;

-- 1. Участник, отчёты и состояние сертификата.
SELECT
    m.kod AS kodMember,
    m.kodMerop,
    m.participantId,
    m.profileId,
    m.beInOn,
    m.timeInMerop,
    a.dtMerop,
    w.kodProject,
    w.[close] AS waveClosed,
    r.kod AS kodReport,
    r.a1,
    r.tipReport,
    r.formatCon2,
    r.listenerCntInteger,
    r.manFIO,
    r.nmOrg,
    r.vidOrg,
    r.otzyv,
    c.kodReport AS certificateReport,
    c.mtsCertNumber,
    c.formReceived,
    c.lastCheckAt,
    c.lastError
FROM dbo.dat_members AS m
JOIN dbo.dat_activity AS a
    ON a.kod = m.kodMerop
JOIN dbo.spr_wave AS w
    ON w.kod = a.kodWave
LEFT JOIN dbo.sb_reports AS r
    ON r.kodMember = m.kod
LEFT JOIN dbo.dat_mts_certificates AS c
    ON c.kodMember = m.kod
   AND c.kodMerop = m.kodMerop
WHERE m.kod = @kodMember
  AND m.kodMerop = @kodMerop
ORDER BY r.kod DESC;

-- Собираем ответы только этого мероприятия.
-- Два источника не суммируем как уникальные ответы:
-- одна запись может присутствовать в обоих.
SELECT
    CAST(N'sb_answer' AS nvarchar(30)) AS sourceTable,
    a.kod AS answerKod,
    a.kodMerop,
    a.kodMember,
    a.memberId,
    a.pollId,
    a.labelF AS savedQuestion,
    a.answer,
    a.srvsDT
INTO #PostformCheck
FROM dbo.sb_answer AS a
WHERE a.kodMerop = @kodMerop
  AND
  (
      a.kodMember = @kodMember
      OR
      (
          NULLIF(@participantId, N'') IS NOT NULL
          AND a.memberId = @participantId
      )
  )

UNION ALL

SELECT
    N'sb_answerData',
    a.kod,
    a.kodMerop,
    a.kodMember,
    a.memberId,
    a.pollId,
    a.labelF,
    a.answer,
    a.srvsDT
FROM dbo.sb_answerData AS a
WHERE a.kodMerop = @kodMerop
  AND
  (
      a.kodMember = @kodMember
      OR
      (
          NULLIF(@participantId, N'') IS NOT NULL
          AND a.memberId = @participantId
      )
  );

-- 2. Есть ли post_form в каждом источнике.
SELECT
    src.sourceTable,
    COUNT(p.answerKod) AS allAnswerRows,
    SUM(CASE WHEN p.pollId = N'post_form'
             THEN 1 ELSE 0 END) AS postFormAnswerRows,
    MAX(CASE WHEN p.pollId = N'post_form'
             THEN p.srvsDT END) AS lastPostFormSavedAt
FROM
(
    VALUES
        (N'sb_answer'),
        (N'sb_answerData')
) AS src(sourceTable)
LEFT JOIN #PostformCheck AS p
    ON p.sourceTable = src.sourceTable
GROUP BY src.sourceTable
ORDER BY src.sourceTable;

-- 3. Какие типы ответов фактически сохранены.
SELECT
    sourceTable,
    pollId,
    COUNT(*) AS answerRows,
    MIN(srvsDT) AS firstSavedAt,
    MAX(srvsDT) AS lastSavedAt
FROM #PostformCheck
GROUP BY sourceTable, pollId
ORDER BY sourceTable, pollId;

-- 4. Ответы и действующие привязки вопросов.
-- Показываем все типы, чтобы увидеть также сертификат
-- и записи, у которых pollId уже изменён очисткой.
SELECT
    p.sourceTable,
    p.answerKod,
    p.srvsDT,
    p.kodMerop,
    p.kodMember,
    p.memberId,
    p.pollId,
    p.savedQuestion,
    p.answer,
    s.nmSubject,
    s.targetTable,
    s.nmField,
    CASE
        WHEN pm.kodSubject IS NULL
            THEN N'Нет привязки вопроса'
        WHEN s.kod IS NULL
            THEN N'Не найдено описание поля'
        ELSE N'Привязка найдена'
    END AS mappingState
FROM #PostformCheck AS p
LEFT JOIN dbo.spr_pollsToMind AS pm
    ON pm.textPoll = p.savedQuestion
LEFT JOIN dbo.spr_pollsSubject AS s
    ON s.kod = pm.kodSubject
ORDER BY p.sourceTable, p.answerKod;

DROP TABLE #PostformCheck;
GO