DO
$do$
declare
    identityId uuid;
    userId uuid;
begin
    ALTER TABLE VCCC.T_APPEAL_TO_CALL_CENTER ADD "changeduser" uuid NULL;

    -- Обновляем по информации из истории
    update VCCC.T_APPEAL_TO_CALL_CENTER atcc
    set ChangedUser = 
        (
            select ass.Users
            from VCCC.T_APPEAL_STATUSES ass
            where ass.Users is not null
                    and
                ass.AppealToCallCenter = atcc.AppealToCallCenterID
            order by ass.datetime desc, ass.AppealStatusesID
            limit 1
        );

    -- Если есть ошибочные записи, исправляем, со ссылкой на неактивного пользователя
    if exists(select * from VCCC.T_APPEAL_TO_CALL_CENTER atcc where atcc.ChangedUser is null)
    then
    begin
        select i.IdentityID 
            into identityId
        from VCLib.T_IDENTITY i 
        where i.IdentityName = 'Не использовать - неизвестный пользователь' limit 1;
        identityId := COALESCE(identityId, gen_random_uuid());

        select u.UserID 
            into userId
        from VCLib.T_USERS u 
        where u.Identity = identityId limit 1;
        userId := COALESCE(userId, gen_random_uuid());

        -- Личность
        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 d.DivisionID from VCLib.T_DIVISIONS d where d.ParentID is null limit 1),--<DivisionID, uniqueidentifier,>
                   0,--<Flags, int,>
                   identityId,--<Identity, uniqueidentifier,>
                   (select e.EntityID from VCLib.T_ENTITIES e where e.Schema = 'ISZL' and e.EntName = 'USERS' limit 1)--<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 VCCC.T_APPEAL_TO_CALL_CENTER atcc
        set ChangedUser = userId
        where atcc.ChangedUser is null;
    end;
    END IF;


    ALTER TABLE VCCC.T_APPEAL_TO_CALL_CENTER ALTER COLUMN "changeduser" SET NOT NULL;
    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);
end
$do$;
