-- Удаляем битые записи
delete ass
from VCCC.T_APPEAL_STATUSES ass
where ass.AppealToCallCenter is null
        or
    ass.[DateTime] is null
        or
    ass.[Status] is null
GO

-- Обязательность полей истории
ALTER TABLE [VCCC].[T_APPEAL_STATUSES] ALTER COLUMN [AppealToCallCenter] uniqueidentifier NOT NULL
GO
ALTER TABLE [VCCC].[T_APPEAL_STATUSES] ALTER COLUMN [DateTime] datetime NOT NULL
GO
ALTER TABLE [VCCC].[T_APPEAL_STATUSES] ALTER COLUMN [Status] int NOT NULL
GO

-- Порядок заведения записи статуса
ALTER TABLE [VCCC].[T_APPEAL_STATUSES] ADD [StatusOrder] int NULL
GO

update ass
set ass.StatusOrder = ordered.StatusOrder
from VCCC.T_APPEAL_STATUSES ass
    join
    (
        select AppealStatusesID, ROW_NUMBER() over (order by AppealToCallCenter, [DateTime]) StatusOrder
        from VCCC.T_APPEAL_STATUSES        
    ) ordered on ordered.AppealStatusesID = ass.AppealStatusesID
GO

ALTER TABLE [VCCC].[T_APPEAL_STATUSES] ALTER COLUMN [StatusOrder] int NOT NULL
GO

-- Генерация записи счётчика порядка статусов
INSERT INTO [ISZL].[T_GEN_NUMBERS]
           ([GenNumbersID]
           ,[EntityID]
           ,[NumberName]
           ,[NextNumber])
SELECT
           NEWID(),--<GenNumbersID, uniqueidentifier,>
           (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.[Schema] = 'VCCC' and e.EntName = 'APPEAL_STATUSES'),--<EntityID, int,>
           'StatusOrder',--<NumberName, varchar(64),>
           ISNULL(MAX(ass.StatusOrder), 0) + 1--<NextNumber, bigint,>)
from [VCCC].[T_APPEAL_STATUSES] ass
GO
