---

USE [myNewVasya];
SET NOCOUNT ON;

DECLARE @subscriber sysname =
    N'WIN-5ODCFIGQRP3\SQL2016SQL';
DECLARE @destination sysname = N'replTransVas';
DECLARE @publication sysname = N'V3Trans';

DECLARE @jobName sysname =
    N'WIN-5ODCFIGQRP3\SQL20-myNewVasya-V3Trans-WIN-5ODCFIGQRP3\SQL20-3';

DECLARE @program nvarchar(128) =
    N'WIN-5ODCFIGQRP3_myNewVasya_V3Trans';

DECLARE @pubId int, @subscriberId int;
DECLARE @jobId uniqueidentifier;
DECLARE @deadline datetime2;
DECLARE @rc int;

IF CONVERT(sysname, SERVERPROPERTY('ServerName')) <> @subscriber
BEGIN
    ;THROW 50001, N'Неверный экземпляр SQL Server.', 1;
END;

IF @@TRANCOUNT <> 0
BEGIN
    ;THROW 50002, N'Запустите блок вне открытой транзакции.', 1;
END;

SELECT @pubId = pubid
FROM dbo.syspublications
WHERE name = @publication
  AND immediate_sync = 0
  AND allow_anonymous = 0;

SELECT @subscriberId = server_id
FROM sys.servers
WHERE name = @subscriber;

SELECT @jobId = job_id
FROM msdb.dbo.sysjobs
WHERE name = @jobName;

IF @pubId IS NULL OR @subscriberId IS NULL OR @jobId IS NULL
BEGIN
    ;THROW 50003, N'Настройки публикации, подписчика или задания отличаются от ожидаемых.', 1;
END;

-- Подтверждаем соответствие двух статей исходным таблицам.
IF
(
    SELECT COUNT(*)
    FROM dbo.sysarticles
    WHERE pubid = @pubId
      AND
      (
          (artid = 142 AND name = N'sb_answer'
           AND objid = OBJECT_ID(N'dbo.sb_answer'))
          OR
          (artid = 143 AND name = N'sb_answerData'
           AND objid = OBJECT_ID(N'dbo.sb_answerData'))
      )
) <> 2
BEGIN
    ;THROW 50004, N'Статьи отличаются от показанных на снимке.', 1;
END;

-- Сохраняем состав и состояние остальных подписок публикации.
DECLARE @otherSubscriptions TABLE
(
    artid int,
    srvid int,
    dest_db sysname,
    subscription_type int,
    status int
);

INSERT INTO @otherSubscriptions
SELECT s.artid, s.srvid, s.dest_db,
       s.subscription_type, s.status
FROM dbo.syssubscriptions AS s
JOIN dbo.sysarticles AS a ON a.artid = s.artid
WHERE a.pubid = @pubId
  AND NOT
  (
      a.artid IN (142, 143)
      AND s.srvid = @subscriberId
      AND s.dest_db = @destination
  );

IF NOT EXISTS
(
    SELECT 1
    FROM @otherSubscriptions
    WHERE srvid = @subscriberId
      AND dest_db = @destination
)
BEGIN
    ;THROW 50005, N'Не найдены остальные статьи нужной подписки.', 1;
END;

-- 1. Штатная остановка агента.
IF EXISTS
(
    SELECT 1
    FROM msdb.dbo.sysjobactivity
    WHERE job_id = @jobId
      AND session_id =
          (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
      AND start_execution_date IS NOT NULL
      AND stop_execution_date IS NULL
)
BEGIN
    EXEC @rc = msdb.dbo.sp_stop_job @job_id = @jobId;

    IF @rc <> 0
    BEGIN
        ;THROW 50006, N'Не удалось запросить остановку агента.', 1;
    END;
END;

-- 2. Ждем остановки задания и завершения SQL-сессий.
SET @deadline = DATEADD(SECOND, 60, SYSDATETIME());

WHILE
    EXISTS
    (
        SELECT 1
        FROM msdb.dbo.sysjobactivity
        WHERE job_id = @jobId
          AND session_id =
              (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
          AND start_execution_date IS NOT NULL
          AND stop_execution_date IS NULL
    )
    OR EXISTS
    (
        SELECT 1
        FROM sys.dm_exec_sessions
        WHERE program_name IN (@program, @jobName)
    )
BEGIN
    IF SYSDATETIME() >= @deadline
    BEGIN
        SELECT
            N'WAITING_FOR_STOP' AS result,
            N'Изменение подписок еще не выполнялось. Повторите блок позже.'
                AS next_action;

        SELECT
            s.session_id,
            s.program_name,
            r.command,
            r.wait_type,
            r.blocking_session_id,
            r.percent_complete
        FROM sys.dm_exec_sessions AS s
        LEFT JOIN sys.dm_exec_requests AS r
            ON r.session_id = s.session_id
        WHERE s.program_name IN (@program, @jobName);

        RETURN;
    END;

    WAITFOR DELAY '00:00:02';
END;

BEGIN TRY
    -- 3. Исключаем только sb_answer из конкретной подписки.
    IF EXISTS
    (
        SELECT 1
        FROM dbo.syssubscriptions
        WHERE artid = 142
          AND srvid = @subscriberId
          AND dest_db = @destination
    )
    BEGIN
        EXEC @rc = sys.sp_dropsubscription
            @publication = @publication,
            @article = N'sb_answer',
            @subscriber = @subscriber,
            @destination_db = @destination;

        IF @rc <> 0
        BEGIN
            ;THROW 50007, N'Не удалось исключить sb_answer.', 1;
        END;
    END;

    -- 4. Исключаем только sb_answerData.
    IF EXISTS
    (
        SELECT 1
        FROM dbo.syssubscriptions
        WHERE artid = 143
          AND srvid = @subscriberId
          AND dest_db = @destination
    )
    BEGIN
        EXEC @rc = sys.sp_dropsubscription
            @publication = @publication,
            @article = N'sb_answerData',
            @subscriber = @subscriber,
            @destination_db = @destination;

        IF @rc <> 0
        BEGIN
            ;THROW 50008, N'Не удалось исключить sb_answerData.', 1;
        END;
    END;

    -- 5. Обе целевые подписки должны отсутствовать.
    IF EXISTS
    (
        SELECT 1
        FROM dbo.syssubscriptions
        WHERE artid IN (142, 143)
          AND srvid = @subscriberId
          AND dest_db = @destination
    )
    BEGIN
        ;THROW 50009, N'Подписка на одну из двух таблиц сохранилась.', 1;
    END;

    -- Остальные подписки должны сохраниться в прежнем состоянии.
    IF EXISTS
    (
        SELECT artid, srvid, dest_db, subscription_type, status
        FROM @otherSubscriptions

        EXCEPT

        SELECT s.artid, s.srvid, s.dest_db,
               s.subscription_type, s.status
        FROM dbo.syssubscriptions AS s
        JOIN dbo.sysarticles AS a ON a.artid = s.artid
        WHERE a.pubid = @pubId
    )
    BEGIN
        ;THROW 50010, N'Изменилось состояние другой подписки. Агент оставлен остановленным.', 1;
    END;

    -- 6. Запускаем доставку оставшихся статей.
    EXEC @rc = msdb.dbo.sp_start_job @job_id = @jobId;

    IF @rc <> 0
    BEGIN
        ;THROW 50011, N'Статьи исключены, но запрос запуска агента не выполнен.', 1;
    END;

    SELECT
        N'SUCCESS' AS result,
        N'Исключены только sb_answer и sb_answerData из подписки replTransVas. Запуск агента запрошен.'
            AS details,
        SYSDATETIME() AS completed_at;
END TRY
BEGIN CATCH
    SELECT
        N'STOPPED_ON_ERROR' AS result,
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message,
        N'Изменения могли выполниться частично. Пришлите этот результат.'
            AS next_action;

    SELECT
        a.name AS article_name,
        s.dest_db,
        s.status
    FROM dbo.sysarticles AS a
    JOIN dbo.syssubscriptions AS s ON s.artid = a.artid
    WHERE a.pubid = @pubId
      AND a.artid IN (142, 143)
      AND s.srvid = @subscriberId
      AND s.dest_db = @destination;

    THROW;
END CATCH;