---

USE [replTransVas];
SET NOCOUNT ON;

DECLARE @old_lock_timeout int = @@LOCK_TIMEOUT;
SET LOCK_TIMEOUT 5000;

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

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

    DECLARE @obj int = OBJECT_ID(N'dbo.sb_answer', N'U');
    DECLARE @kod int;

    IF @obj IS NULL
        THROW 50003, N'Таблица replTransVas.dbo.sb_answer не найдена.', 1;

    SELECT @kod = column_id
    FROM sys.columns
    WHERE object_id = @obj
      AND name = N'kod';

    IF @kod IS NULL
        THROW 50004, N'Столбец kod не найден.', 1;

    -- 1. Исправляем только подтвержденное отсутствие
    -- действующего нефильтрованного индекса с первым ключом kod.
    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.indexes AS i
        JOIN sys.index_columns AS ic
          ON ic.object_id = i.object_id
         AND ic.index_id = i.index_id
        WHERE i.object_id = @obj
          AND i.type IN (1, 2)
          AND i.is_disabled = 0
          AND i.is_hypothetical = 0
          AND i.has_filter = 0
          AND ic.key_ordinal = 1
          AND ic.column_id = @kod
    )
    BEGIN
        IF EXISTS
        (
            SELECT 1
            FROM sys.indexes
            WHERE object_id = @obj
              AND name = N'IX_sb_answer_kod_repl_fix'
        )
            THROW 50005,
                N'Имя IX_sb_answer_kod_repl_fix уже занято. Требуется проверка существующего индекса.',
                1;

        RAISERROR(
            N'Нет индекса с первым ключом kod. Создаю индекс на подписчике...',
            10, 1
        ) WITH NOWAIT;

        CREATE NONCLUSTERED INDEX [IX_sb_answer_kod_repl_fix]
            ON dbo.sb_answer ([kod])
            WITH (MAXDOP = 1);

        SELECT
            N'01_ACTION' AS section,
            N'CREATED_INDEX' AS result,
            N'IX_sb_answer_kod_repl_fix' AS index_name;
    END
    ELSE
    BEGIN
        SELECT
            N'01_ACTION' AS section,
            N'INDEX_ALREADY_EXISTS' AS result,
            N'Дополнительный индекс не создавался.' AS details;
    END;

    -- 2. Состав индексов: одна строка на столбец индекса.
    SELECT
        N'02_INDEXES' AS section,
        i.name AS index_name,
        i.type_desc,
        i.is_unique,
        i.is_primary_key,
        i.is_disabled,
        i.has_filter,
        i.filter_definition,
        c.name AS column_name,
        ic.key_ordinal,
        ic.is_included_column
    FROM sys.indexes AS i
    LEFT JOIN sys.index_columns AS ic
      ON ic.object_id = i.object_id
     AND ic.index_id = i.index_id
    LEFT JOIN sys.columns AS c
      ON c.object_id = ic.object_id
     AND c.column_id = ic.column_id
    WHERE i.object_id = @obj
    ORDER BY i.index_id, ic.index_column_id;

    -- 3. Статистика индексов, используемых для поиска по kod.
    SELECT
        N'03_KOD_STATISTICS' AS section,
        i.name AS index_name,
        p.last_updated,
        p.rows,
        p.rows_sampled,
        p.modification_counter
    FROM sys.indexes AS i
    JOIN sys.index_columns AS ic
      ON ic.object_id = i.object_id
     AND ic.index_id = i.index_id
    OUTER APPLY sys.dm_db_stats_properties(
        i.object_id, i.index_id
    ) AS p
    WHERE i.object_id = @obj
      AND i.type IN (1, 2)
      AND ic.key_ordinal = 1
      AND ic.column_id = @kod;

    -- 4. Триггеры и накопленное время их выполнения.
    -- Ничего автоматически не отключаем:
    -- триггер может содержать необходимую бизнес-логику.
    SELECT
        N'04_TRIGGERS' AS section,
        t.name AS trigger_name,
        t.is_disabled,
        t.is_not_for_replication,
        ts.execution_count,
        CAST(
            ts.total_worker_time / 1000.0
            / NULLIF(ts.execution_count, 0)
            AS decimal(18, 3)
        ) AS average_cpu_ms,
        CAST(
            ts.total_elapsed_time / 1000.0
            / NULLIF(ts.execution_count, 0)
            AS decimal(18, 3)
        ) AS average_elapsed_ms,
        m.definition AS trigger_definition
    FROM sys.triggers AS t
    LEFT JOIN sys.sql_modules AS m
      ON m.object_id = t.object_id
    LEFT JOIN sys.dm_exec_trigger_stats AS ts
      ON ts.database_id = DB_ID()
     AND ts.object_id = t.object_id
    WHERE t.parent_id = @obj;

    -- 5. Текущий запрос агента и его кешированный план.
    -- session_id определяется заново: номер 58 не зашит.
    SELECT
        N'05_CURRENT_REPLICATION_REQUEST' AS section,
        s.session_id,
        r.status,
        r.command,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.cpu_time,
        r.total_elapsed_time,
        r.logical_reads,
        r.reads,
        r.writes,
        OBJECT_SCHEMA_NAME(txt.objectid, txt.dbid) AS procedure_schema,
        OBJECT_NAME(txt.objectid, txt.dbid) AS procedure_name,
        SUBSTRING(
            txt.text,
            r.statement_start_offset / 2 + 1,
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(txt.text)
                    ELSE r.statement_end_offset
                END - r.statement_start_offset
            ) / 2 + 1
        ) AS current_statement,
        qp.query_plan
    FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r
      ON r.session_id = s.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
    OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
    WHERE r.database_id = DB_ID()
      AND s.program_name = N'WIN-5ODCFIGQRP3_myNewVasya_V3Trans';

    SET LOCK_TIMEOUT @old_lock_timeout;
END TRY
BEGIN CATCH
    SET LOCK_TIMEOUT @old_lock_timeout;
    THROW;
END CATCH;

-- 6. Состояние доставки после проверки/исправления.
SELECT TOP (5)
    N'06_DISTRIBUTION_HISTORY' AS section,
    h.[time],
    h.runstatus,
    h.delivered_commands,
    h.current_delivery_rate,
    h.comments
FROM distribution.dbo.MSdistribution_agents AS a
JOIN distribution.dbo.MSdistribution_history AS h
  ON h.agent_id = a.id
WHERE a.publisher_db = N'myNewVasya'
  AND a.publication = N'V3Trans'
  AND a.subscriber_db = N'replTransVas'
ORDER BY h.[time] DESC;