---

USE [myNewVasya];
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @Apply bit = 0; -- 0: просмотр; 1: резервная копия и удаление

DECLARE @Questions TABLE (
    labelF nvarchar(4000) NOT NULL
);

INSERT INTO @Questions (labelF)
VALUES
    (N'Вид организации'),
    (N'Интерактивные задания'),
    (N'Польза знаний'),
    (N'Работа ведущего'),
    (N'Поле для комментариев и предложений');

-- 1. Показываем объём удаления.
SELECT
    N'sb_answerData' AS sourceTable,
    a.kodMerop,
    a.labelF,
    COUNT_BIG(*) AS rowsToDelete,
    COUNT(DISTINCT a.kodMember) AS membersAffected
FROM dbo.sb_answerData a
JOIN @Questions q ON q.labelF = a.labelF
WHERE a.pollId = N'post_form'
GROUP BY a.kodMerop, a.labelF

UNION ALL

SELECT
    N'sb_answer',
    a.kodMerop,
    a.labelF,
    COUNT_BIG(*),
    COUNT(DISTINCT a.kodMember)
FROM dbo.sb_answer a
JOIN @Questions q ON q.labelF = a.labelF
WHERE a.pollId = N'post_form'
GROUP BY a.kodMerop, a.labelF
ORDER BY kodMerop, sourceTable, labelF;

IF @Apply = 0 RETURN;

-- 2. Копирование и удаление выполняются одной транзакцией.
BEGIN TRY
    BEGIN TRANSACTION;

    -- Фиксированные имена защищают резервную копию от перезаписи.
    IF OBJECT_ID(N'dbo.bak_mts_answers_20260925_archive', N'U') IS NOT NULL
       OR OBJECT_ID(N'dbo.bak_mts_answers_20260925_pending', N'U') IS NOT NULL
    BEGIN
        THROW 51010,
            'Backup tables already exist. Keep them; use new backup names for another cleanup.',
            1;
    END;

    SELECT a.*
    INTO dbo.bak_mts_answers_20260925_archive
    FROM dbo.sb_answerData a WITH (UPDLOCK, HOLDLOCK)
    JOIN @Questions q ON q.labelF = a.labelF
    WHERE a.pollId = N'post_form';

    SELECT a.*
    INTO dbo.bak_mts_answers_20260925_pending
    FROM dbo.sb_answer a WITH (UPDLOCK, HOLDLOCK)
    JOIN @Questions q ON q.labelF = a.labelF
    WHERE a.pollId = N'post_form';

    -- Удаляем именно скопированные строки.
    DELETE a
    FROM dbo.sb_answer a
    JOIN dbo.bak_mts_answers_20260925_pending b ON b.kod = a.kod;

    DECLARE @pendingDeleted int = @@ROWCOUNT;

    DELETE a
    FROM dbo.sb_answerData a
    JOIN dbo.bak_mts_answers_20260925_archive b ON b.kod = a.kod;

    DECLARE @archiveDeleted int = @@ROWCOUNT;

    COMMIT;

    SELECT
        @archiveDeleted AS deletedFromArchive,
        @pendingDeleted AS deletedFromPending;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;