ALTER TABLE [VCCC].[T_APPEAL_TO_CALL_CENTER] ADD [ChangedUser] uniqueidentifier NULL
GO

-- Обновляем по информации из истории
update atcc
set atcc.ChangedUser = uass.Users
from VCCC.T_APPEAL_TO_CALL_CENTER atcc
    cross apply
    (
        select top 1 *
        from VCCC.T_APPEAL_STATUSES ass
        where ass.Users is not null
                and
            ass.AppealToCallCenter = atcc.AppealToCallCenterID
        order by ass.[DateTime] desc, ass.AppealStatusesID
    ) uass
GO

-- Если есть ошибочные записи, исправляем, со ссылкой на неактивного пользователя
if exists(select * from VCCC.T_APPEAL_TO_CALL_CENTER atcc where atcc.ChangedUser is null)
begin
    declare @identityId uniqueidentifier = ISNULL((select top 1 i.IdentityID from [VCLib].[T_IDENTITY] i where i.IdentityName = 'Не использовать - неизвестный пользователь'), NEWID())
    declare @userId uniqueidentifier = ISNULL((select top 1 u.UserID from VCLib.T_USERS u where u.[Identity] = @identityId), NEWID())

    -- Личность
    INSERT INTO [VCLib].[T_IDENTITY]
               ([IdentityID]
               ,[IdentityName])
    select
               @identityId,--<IdentityID, uniqueidentifier,>
               'Не использовать - неизвестный пользователь'--<IdentityName, varchar(255),>)
    where not exists
    (
        select *
        from [VCLib].[T_IDENTITY] i where i.IdentityID = @identityId
    )

    -- Основная запись пользователя
    INSERT INTO [VCLib].[T_USERS]
               ([UserID]
               ,[DivisionID]
               ,[Flags]
               ,[Identity]
               ,[type_])
    SELECT
               @userId,--<UserID, uniqueidentifier,>
               (select top 1 d.DivisionID from VCLib.T_DIVISIONS d where d.ParentID is null),--<DivisionID, uniqueidentifier,>
               0,--<Flags, int,>
               @identityId,--<Identity, uniqueidentifier,>
               (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.[Schema] = 'ISZL' and e.EntName = 'USERS')--<type_, int,>)
    where not exists
        (
            select *
            from VCLib.T_USERS u where u.UserID = @userId
        )

    -- Запись в VCCC схеме
    INSERT INTO [VCCC].[T_USERS]
               ([UserID])
    SELECT
               @userId--<UserID, uniqueidentifier,>
    where not exists
        (
            select *
            from VCCC.T_USERS u where u.UserID = @userId
        )

    -- Запись в ISZL схеме
    INSERT INTO [ISZL].[T_USERS]
               ([UserID])
    SELECT
               @userId--<UserID, uniqueidentifier,>
    where not exists
        (
            select *
            from ISZL.T_USERS u where u.UserID = @userId
        )

    update atcc
    set atcc.ChangedUser = @userId
    from VCCC.T_APPEAL_TO_CALL_CENTER atcc
    where atcc.ChangedUser is null
end
GO

ALTER TABLE [VCCC].[T_APPEAL_TO_CALL_CENTER] ALTER COLUMN [ChangedUser] uniqueidentifier NOT NULL
GO

ALTER TABLE [VCCC].[T_APPEAL_TO_CALL_CENTER] ADD CONSTRAINT [FK_T_USERS_T_APPEAL_TO_CALL_CENTER_ChangedUser]
FOREIGN KEY([ChangedUser]) REFERENCES [VCCC].[T_USERS] ([UserID])
GO
