---

USE [replTransVas];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

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

IF ISNULL(
    HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', N'VIEW DEFINITION'),
    0
) <> 1
    THROW 51901,
        N'Требуется право VIEW DEFINITION на replTransVas.',
        1;

IF OBJECT_ID(
    N'myNewVasya.dbo.mts_daemon_mail',
    N'U'
) IS NULL
    THROW 51902,
        N'Таблица myNewVasya.dbo.mts_daemon_mail отсутствует или недоступна.',
        1;


-- ============================================================
-- ОБЪЕКТЫ ДЛЯ ИСПРАВЛЕНИЯ
-- ============================================================

DECLARE @work TABLE
(
    id                  int IDENTITY(1,1) PRIMARY KEY,
    oldName             sysname NOT NULL,
    newName             sysname NOT NULL,
    expectedType        char(2) NOT NULL,
    objectId            int NULL,
    sourceDefinition    nvarchar(max) NULL,
    newDefinition       nvarchar(max) NULL,
    usesAnsiNulls       bit NULL,
    usesQuotedIdentifier bit NULL
);

INSERT INTO @work
(
    oldName,
    newName,
    expectedType
)
VALUES
(
    N'dbo.MTS_AdminReportState_v15',
    N'dbo.MTS_AdminReportState',
    'V'
),
(
    N'dbo.K_view_No_sert',
    N'dbo.K_view_No_sert',
    'V'
),
(
    N'dbo.K_view_members_METOD',
    N'dbo.K_view_members_METOD',
    'V'
),
(
    N'dbo.View_reportLastWave',
    N'dbo.View_reportLastWave',
    'V'
),
(
    N'dbo.view_reports',
    N'dbo.view_reports',
    'V'
),
(
    N'dbo.View_answers_metod',
    N'dbo.View_answers_metod',
    'V'
),
(
    N'dbo.View_reports_MTS',
    N'dbo.View_reports_MTS',
    'V'
),
(
    N'dbo.View_reportLastWave_v16_test',
    N'dbo.View_reportLastWave_test',
    'V'
),
(
    N'dbo.sp_K_SendOtzyvSert',
    N'dbo.sp_K_SendOtzyvSert',
    'P'
),
(
    N'dbo.sp_K_upd_Stasik',
    N'dbo.sp_K_upd_Stasik',
    'P'
);


-- ============================================================
-- ТОЧНЫЕ СООТВЕТСТВИЯ ИМЁН
-- ============================================================

DECLARE @names TABLE
(
    id      int IDENTITY(1,1) PRIMARY KEY,
    oldName sysname NOT NULL,
    newName sysname NOT NULL
);

INSERT INTO @names(oldName, newName)
VALUES
(
    N'mts_daemon_mail_v12',
    N'mts_daemon_mail'
),
(
    N'MTS_AdminReportState_v15',
    N'MTS_AdminReportState'
),
(
    N'View_reportLastWave_v16_test',
    N'View_reportLastWave_test'
);


-- ============================================================
-- РАБОЧИЕ ПЕРЕМЕННЫЕ
-- ============================================================

DECLARE
    @i                  int,
    @last               int,
    @old                sysname,
    @new                sysname,
    @type               char(2),
    @objectId           int,
    @definition         nvarchar(max),
    @source             nvarchar(max),
    @current            nvarchar(max),
    @ansi               bit,
    @quoted             bit,
    @message            nvarchar(2048),
    @sql                nvarchar(max),

    @pos                int,
    @depth              int,
    @textLength         int,

    @keyword            nvarchar(20),
    @keywordLength      int,
    @prefix             nvarchar(40),

    @declaredPlain      nvarchar(517),
    @declaredQuoted     nvarchar(517),
    @headerMatched      bit,

    @mapId              int,
    @mapLast            int,
    @oldToken           sysname,
    @newToken           sysname,
    @oldTokenLength     int,
    @newTokenLength     int,
    @searchFrom         int,
    @found              int,
    @before             nchar(1),
    @after              nchar(1),
    @beforeIsIdentifier bit,
    @afterIsIdentifier  bit;

SELECT @last = MAX(id) FROM @work;
SELECT @mapLast = MAX(id) FROM @names;


BEGIN TRY
    BEGIN TRANSACTION;


    -- ========================================================
    -- ПРОВЕРКА ОБЪЕКТОВ И ЧТЕНИЕ ТЕКУЩИХ ОПРЕДЕЛЕНИЙ
    -- ========================================================

    SET @i = 1;

    WHILE @i <= @last
    BEGIN
        SELECT
            @old = oldName,
            @new = newName,
            @type = expectedType
        FROM @work
        WHERE id = @i;

        SET @objectId = OBJECT_ID(@old);

        IF @objectId IS NULL
        BEGIN
            SET @message =
                N'Исходный объект отсутствует: ' + @old
                + N'. Возможно, переход уже выполнен частично.';

            THROW 51903, @message, 1;
        END;

        IF NOT EXISTS
        (
            SELECT 1
            FROM sys.objects
            WHERE object_id = @objectId
              AND type = @type
        )
        BEGIN
            SET @message =
                N'Неожиданный тип объекта: ' + @old;

            THROW 51904, @message, 1;
        END;

        IF @old <> @new
           AND OBJECT_ID(@new) IS NOT NULL
        BEGIN
            SET @message =
                N'Целевое имя уже занято: ' + @new;

            THROW 51905, @message, 1;
        END;

        IF EXISTS
        (
            SELECT 1
            FROM sys.crypt_properties
            WHERE major_id = @objectId
        )
        BEGIN
            SET @message =
                N'Объект имеет подпись, требуется отдельная миграция: '
                + @old;

            THROW 51906, @message, 1;
        END;

        IF EXISTS
        (
            SELECT 1
            FROM sys.sql_modules
            WHERE object_id = @objectId
              AND is_schema_bound = 1
        )
        BEGIN
            SET @message =
                N'Объект использует SCHEMABINDING: ' + @old;

            THROW 51907, @message, 1;
        END;

        SET @definition = NULL;

        SELECT
            @definition = definition,
            @ansi = uses_ansi_nulls,
            @quoted = uses_quoted_identifier
        FROM sys.sql_modules
        WHERE object_id = @objectId;

        IF @definition IS NULL
        BEGIN
            SET @message =
                N'Определение объекта недоступно: ' + @old;

            THROW 51908, @message, 1;
        END;

        SET @source = @definition;


        -- ====================================================
        -- УДАЛЕНИЕ ТОЛЬКО ВСТУПИТЕЛЬНЫХ КОММЕНТАРИЕВ
        -- ====================================================

        WHILE 1 = 1
        BEGIN
            WHILE DATALENGTH(@definition) > 0
              AND UNICODE(LEFT(@definition, 1)) IN (9, 10, 13, 32)
            BEGIN
                SET @definition = SUBSTRING(
                    @definition,
                    2,
                    DATALENGTH(@definition) / 2
                );
            END;

            IF LEFT(@definition, 2) = N'--'
            BEGIN
                SET @pos = CHARINDEX(NCHAR(10), @definition);

                IF @pos = 0
                BEGIN
                    SET @message =
                        N'После комментария отсутствует определение: '
                        + @old;

                    THROW 51909, @message, 1;
                END;

                SET @definition = SUBSTRING(
                    @definition,
                    @pos + 1,
                    DATALENGTH(@definition) / 2
                );

                CONTINUE;
            END;

            IF LEFT(@definition, 2) = N'/*'
            BEGIN
                SET @depth = 1;
                SET @pos = 3;
                SET @textLength = DATALENGTH(@definition) / 2;

                WHILE @pos <= @textLength AND @depth > 0
                BEGIN
                    IF SUBSTRING(@definition, @pos, 2) = N'/*'
                    BEGIN
                        SET @depth += 1;
                        SET @pos += 2;
                    END
                    ELSE IF SUBSTRING(@definition, @pos, 2) = N'*/'
                    BEGIN
                        SET @depth -= 1;
                        SET @pos += 2;
                    END
                    ELSE
                    BEGIN
                        SET @pos += 1;
                    END;
                END;

                IF @depth <> 0
                BEGIN
                    SET @message =
                        N'Незакрытый начальный комментарий: ' + @old;

                    THROW 51910, @message, 1;
                END;

                SET @definition = SUBSTRING(
                    @definition,
                    @pos,
                    DATALENGTH(@definition) / 2
                );

                CONTINUE;
            END;

            BREAK;
        END;


        -- ====================================================
        -- ПРИВЕДЕНИЕ ЗАГОЛОВКА К ALTER
        -- ====================================================

        IF LEFT(@definition, 15)
               COLLATE Latin1_General_100_CI_AS = N'CREATE OR ALTER'
           AND UNICODE(SUBSTRING(@definition, 16, 1))
               IN (9, 10, 13, 32)
        BEGIN
            SET @definition = STUFF(
                @definition,
                1,
                15,
                N'ALTER'
            );
        END
        ELSE IF LEFT(@definition, 6)
                    COLLATE Latin1_General_100_CI_AS = N'CREATE'
                AND UNICODE(SUBSTRING(@definition, 7, 1))
                    IN (9, 10, 13, 32)
        BEGIN
            SET @definition = STUFF(
                @definition,
                1,
                6,
                N'ALTER'
            );
        END
        ELSE IF NOT
        (
            LEFT(@definition, 5)
                COLLATE Latin1_General_100_CI_AS = N'ALTER'
            AND UNICODE(SUBSTRING(@definition, 6, 1))
                IN (9, 10, 13, 32)
        )
        BEGIN
            SET @message =
                N'Неизвестный заголовок определения: ' + @old;

            THROW 51911, @message, 1;
        END;


        -- ====================================================
        -- ПРОВЕРКА ИМЕНИ В ЗАГОЛОВКЕ
        -- ====================================================

        SET @pos = 6;

        WHILE UNICODE(SUBSTRING(@definition, @pos, 1))
              IN (9, 10, 13, 32)
        BEGIN
            SET @pos += 1;
        END;

        SET @keyword = NULL;

        IF @type = 'V'
           AND SUBSTRING(@definition, @pos, 4)
               COLLATE Latin1_General_100_CI_AS = N'VIEW'
        BEGIN
            SET @keyword = N'VIEW';
        END;

        IF @type = 'P'
        BEGIN
            IF SUBSTRING(@definition, @pos, 9)
                   COLLATE Latin1_General_100_CI_AS = N'PROCEDURE'
                SET @keyword = N'PROCEDURE';
            ELSE IF SUBSTRING(@definition, @pos, 4)
                        COLLATE Latin1_General_100_CI_AS = N'PROC'
                SET @keyword = N'PROC';
        END;

        IF @keyword IS NULL
        BEGIN
            SET @message =
                N'Тип в заголовке не соответствует каталогу: ' + @old;

            THROW 51912, @message, 1;
        END;

        SET @keywordLength = LEN(@keyword);
        SET @pos += @keywordLength;

        IF UNICODE(SUBSTRING(@definition, @pos, 1))
           NOT IN (9, 10, 13, 32)
        BEGIN
            SET @message =
                N'Неожиданный формат заголовка: ' + @old;

            THROW 51913, @message, 1;
        END;

        WHILE UNICODE(SUBSTRING(@definition, @pos, 1))
              IN (9, 10, 13, 32)
        BEGIN
            SET @pos += 1;
        END;

        SET @declaredPlain =
            PARSENAME(@old, 2) + N'.' + PARSENAME(@old, 1);

        SET @declaredQuoted =
            QUOTENAME(PARSENAME(@old, 2))
            + N'.'
            + QUOTENAME(PARSENAME(@old, 1));

        SET @headerMatched = 0;

        IF SUBSTRING(@definition, @pos, LEN(@declaredQuoted))
               COLLATE Latin1_General_100_CI_AS = @declaredQuoted
           AND UNICODE(SUBSTRING(
                   @definition,
                   @pos + LEN(@declaredQuoted),
                   1
               )) IN (9, 10, 13, 32, 40)
        BEGIN
            SET @headerMatched = 1;
        END;

        IF SUBSTRING(@definition, @pos, LEN(@declaredPlain))
               COLLATE Latin1_General_100_CI_AS = @declaredPlain
           AND UNICODE(SUBSTRING(
                   @definition,
                   @pos + LEN(@declaredPlain),
                   1
               )) IN (9, 10, 13, 32, 40)
        BEGIN
            SET @headerMatched = 1;
        END;

        IF @headerMatched = 0
        BEGIN
            SET @message =
                N'Имя в заголовке отличается от каталога '
                + N'или использует неподдержанный формат: ' + @old;

            THROW 51914, @message, 1;
        END;


        -- ====================================================
        -- ЗАМЕНА ИЗВЕСТНЫХ ИМЁН
        -- Не заменяет совпадение внутри более длинного имени.
        -- Также обновляет упоминания в комментариях.
        -- ====================================================

        SET @mapId = 1;

        WHILE @mapId <= @mapLast
        BEGIN
            SELECT
                @oldToken = oldName,
                @newToken = newName
            FROM @names
            WHERE id = @mapId;

            SET @oldTokenLength = LEN(@oldToken);
            SET @newTokenLength = LEN(@newToken);
            SET @searchFrom = 1;

            WHILE 1 = 1
            BEGIN
                SET @found = CHARINDEX(
                    @oldToken COLLATE Latin1_General_100_CI_AS,
                    @definition COLLATE Latin1_General_100_CI_AS,
                    @searchFrom
                );

                IF @found = 0
                    BREAK;

                SET @before =
                    CASE
                        WHEN @found > 1
                        THEN SUBSTRING(@definition, @found - 1, 1)
                        ELSE N' '
                    END;

                SET @after = SUBSTRING(
                    @definition,
                    @found + @oldTokenLength,
                    1
                );

                SET @beforeIsIdentifier =
                    CASE
                        WHEN @before COLLATE Latin1_General_100_BIN2
                             LIKE N'[A-Za-z0-9_@$#А-Яа-яЁё]'
                        THEN 1
                        ELSE 0
                    END;

                SET @afterIsIdentifier =
                    CASE
                        WHEN @after COLLATE Latin1_General_100_BIN2
                             LIKE N'[A-Za-z0-9_@$#А-Яа-яЁё]'
                        THEN 1
                        ELSE 0
                    END;

                IF @beforeIsIdentifier = 0
                   AND @afterIsIdentifier = 0
                BEGIN
                    SET @definition = STUFF(
                        @definition,
                        @found,
                        @oldTokenLength,
                        @newToken
                    );

                    SET @searchFrom = @found + @newTokenLength;
                END
                ELSE
                BEGIN
                    SET @searchFrom = @found + @oldTokenLength;
                END;
            END;

            SET @mapId += 1;
        END;

        UPDATE @work
        SET
            objectId = @objectId,
            sourceDefinition = @source,
            newDefinition = @definition,
            usesAnsiNulls = @ansi,
            usesQuotedIdentifier = @quoted
        WHERE id = @i;

        SET @i += 1;
    END;


    -- ========================================================
    -- ПОВТОРНАЯ ПРОВЕРКА ПЕРЕД ПЕРЕИМЕНОВАНИЕМ
    -- Сравниваем с определением, прочитанным в этом запуске.
    -- ========================================================

    SET @i = 1;

    WHILE @i <= @last
    BEGIN
        SELECT
            @old = oldName,
            @new = newName,
            @objectId = objectId,
            @source = sourceDefinition
        FROM @work
        WHERE id = @i;

        IF ISNULL(OBJECT_ID(@old), -1) <> @objectId
        BEGIN
            SET @message =
                N'Объект изменился во время подготовки: ' + @old;

            THROW 51915, @message, 1;
        END;

        IF @old <> @new
           AND OBJECT_ID(@new) IS NOT NULL
        BEGIN
            SET @message =
                N'Целевое имя занято во время подготовки: ' + @new;

            THROW 51916, @message, 1;
        END;

        SET @current = NULL;

        SELECT @current = definition
        FROM sys.sql_modules
        WHERE object_id = @objectId;

        IF @current IS NULL
           OR CONVERT(varbinary(max), @current)
              <> CONVERT(varbinary(max), @source)
        BEGIN
            SET @message =
                N'Определение изменилось во время подготовки: ' + @old;

            THROW 51917, @message, 1;
        END;

        SET @i += 1;
    END;


    -- ========================================================
    -- ПЕРЕИМЕНОВАНИЕ ДВУХ ПРЕДСТАВЛЕНИЙ
    -- ========================================================

    SET @i = 1;

    WHILE @i <= @last
    BEGIN
        SELECT
            @old = oldName,
            @new = newName
        FROM @work
        WHERE id = @i;

        IF @old <> @new
        BEGIN
            SET @new = PARSENAME(@new, 1);

            EXEC sys.sp_rename
                @objname = @old,
                @newname = @new,
                @objtype = N'OBJECT';
        END;

        SET @i += 1;
    END;


    -- ========================================================
    -- ОБНОВЛЕНИЕ ОПРЕДЕЛЕНИЙ
    -- ========================================================

    SET @i = 1;

    WHILE @i <= @last
    BEGIN
        SELECT
            @definition = newDefinition,
            @ansi = usesAnsiNulls,
            @quoted = usesQuotedIdentifier
        FROM @work
        WHERE id = @i;

        SET @sql =
            N'SET ANSI_NULLS '
            + CASE WHEN @ansi = 1 THEN N'ON' ELSE N'OFF' END
            + N'; SET QUOTED_IDENTIFIER '
            + CASE WHEN @quoted = 1 THEN N'ON' ELSE N'OFF' END
            + N'; EXEC sys.sp_executesql @body;';

        EXEC sys.sp_executesql
            @sql,
            N'@body nvarchar(max)',
            @body = @definition;

        SET @i += 1;
    END;


    -- ========================================================
    -- КОНТРОЛЬ ИМЁН ПОСЛЕ ИЗМЕНЕНИЯ
    -- ========================================================

    IF EXISTS
    (
        SELECT 1
        FROM @work AS w
        WHERE ISNULL(OBJECT_ID(w.newName), -1) <> w.objectId
    )
        THROW 51918,
            N'Не совпадают идентификаторы объектов после переименования.',
            1;

    IF OBJECT_ID(N'dbo.MTS_AdminReportState_v15') IS NOT NULL
       OR OBJECT_ID(N'dbo.View_reportLastWave_v16_test') IS NOT NULL
        THROW 51919,
            N'После перехода сохранились старые имена представлений.',
            1;

    COMMIT TRANSACTION;

    SELECT
        N'COMMITTED' AS result,
        oldName,
        newName,
        objectId
    FROM @work
    ORDER BY id;


    -- Исходные определения для сохранения перед дальнейшими изменениями.
    SELECT
        oldName,
        newName,
        usesAnsiNulls,
        usesQuotedIdentifier,
        sourceDefinition
    FROM @work
    ORDER BY id;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;
GO