---

USE [replTransVas];
SET NOCOUNT ON;

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

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

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

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

DECLARE @jobId uniqueidentifier;
DECLARE @obj int = OBJECT_ID(N'dbo.sb_answer', N'U');
DECLARE @deadline datetime2;
DECLARE @running bit;
DECLARE @sessions bit;

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

IF @jobId IS NULL
BEGIN
    ;THROW 50003, N'Задание Distribution Agent не найдено.', 1;
END;

IF NOT EXISTS
(
    SELECT 1
    FROM distribution.dbo.MSdistribution_agents
    WHERE name = @jobName
      AND publisher_db = N'myNewVasya'
      AND publication = N'V3Trans'
      AND subscriber_db = N'replTransVas'
)
BEGIN
    ;THROW 50004, N'Не подтверждена нужная подписка. Остановка отменена.', 1;
END;

-- До остановки проверяем наличие и тип обоих индексов.
IF
(
    SELECT COUNT(*)
    FROM sys.indexes
    WHERE object_id = @obj
      AND name IN
      (
          N'IX_sb_answer_MtsMemberLatest',
          N'IX_sb_answer_MtsParticipantLatest'
      )
      AND type = 2
      AND is_unique = 0
      AND is_primary_key = 0
      AND is_unique_constraint = 0
      AND is_hypothetical = 0
) <> 2
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
    BEGIN TRY
        EXEC msdb.dbo.sp_stop_job @job_id = @jobId;
    END TRY
    BEGIN CATCH
        -- Задание могло завершиться между проверкой и остановкой.
        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
            THROW;
        END;
    END CATCH;
END;

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

WHILE 1 = 1
BEGIN
    SET @running = 0;
    SET @sessions = 0;

    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
    )
        SET @running = 1;

    IF EXISTS
    (
        SELECT 1
        FROM sys.dm_exec_sessions
        WHERE program_name IN (@program, @jobName)
    )
        SET @sessions = 1;

    IF @running = 0 AND @sessions = 0
        BREAK;

    IF SYSDATETIME() >= @deadline
    BEGIN
        SELECT
            N'WAITING_FOR_STOP' AS result,
            @running AS job_still_running,
            @sessions AS agent_sessions_exist,
            N'Индексы не изменены этим запуском. Повторите блок позже.'
                AS next_action;

        SELECT
            s.session_id,
            s.program_name,
            s.status AS session_status,
            r.command,
            r.status AS request_status,
            r.wait_type,
            r.blocking_session_id,
            r.percent_complete,
            r.estimated_completion_time AS estimated_remaining_ms
        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;

-- 3. Отключаем оба индекса в одной транзакции.
-- При ошибке изменения этого этапа откатываются.
BEGIN TRY
    EXEC sys.sp_executesql N'
        SET LOCK_TIMEOUT 5000;
        SET XACT_ABORT ON;

        BEGIN TRY
            BEGIN TRANSACTION;

            IF EXISTS
            (
                SELECT 1 FROM sys.indexes
                WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
                  AND name = N''IX_sb_answer_MtsMemberLatest''
                  AND is_disabled = 0
            )
                ALTER INDEX [IX_sb_answer_MtsMemberLatest]
                    ON dbo.sb_answer DISABLE;

            IF EXISTS
            (
                SELECT 1 FROM sys.indexes
                WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
                  AND name = N''IX_sb_answer_MtsParticipantLatest''
                  AND is_disabled = 0
            )
                ALTER INDEX [IX_sb_answer_MtsParticipantLatest]
                    ON dbo.sb_answer DISABLE;

            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() <> 0
                ROLLBACK TRANSACTION;
            THROW;
        END CATCH;
    ';
END TRY
BEGIN CATCH
    SELECT
        N'INDEX_CHANGE_FAILED' AS result,
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message;

    -- Возобновляем доставку, даже если эксперимент не удался.
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_id = @jobId;

        SELECT
            N'AGENT_RESTART_REQUESTED_AFTER_FAILURE' AS result;
    END TRY
    BEGIN CATCH
        SELECT
            N'AGENT_START_FAILED' AS result,
            ERROR_MESSAGE() AS error_message,
            @jobName AS job_to_start;
    END CATCH;

    SELECT name, type_desc, is_disabled
    FROM sys.indexes
    WHERE object_id = @obj;

    RETURN;
END CATCH;

-- 4. Запускаем доставку с отключенными индексами.
BEGIN TRY
    EXEC msdb.dbo.sp_start_job @job_id = @jobId;
END TRY
BEGIN CATCH
    SELECT
        N'INDEXES_DISABLED_BUT_AGENT_START_FAILED' AS result,
        ERROR_MESSAGE() AS error_message,
        @jobName AS job_to_start;

    RETURN;
END CATCH;

SELECT
    N'INDEXES_DISABLED_AGENT_START_REQUESTED' AS result,
    SYSDATETIME() AS experiment_started_at;

SELECT name, type_desc, is_disabled
FROM sys.indexes
WHERE object_id = @obj
ORDER BY index_id;