-- Удаляем битые записи
delete from VCCC.T_APPEAL_STATUSES ass
where ass.AppealToCallCenter is null
        or
    ass."datetime" is null
        or
    ass."status" is null;

-- Обязательность полей истории
ALTER TABLE VCCC.T_APPEAL_STATUSES ALTER COLUMN "appealtocallcenter" SET NOT NULL;
ALTER TABLE VCCC.T_APPEAL_STATUSES ALTER COLUMN "datetime" SET NOT NULL;
ALTER TABLE VCCC.T_APPEAL_STATUSES ALTER COLUMN "status" SET NOT NULL;

-- Порядок заведения записи статуса
ALTER TABLE VCCC.T_APPEAL_STATUSES ADD "statusorder" integer NULL;

update VCCC.T_APPEAL_STATUSES ass
set StatusOrder = ordered.StatusOrder
from (
        select AppealStatusesID, ROW_NUMBER() over (order by AppealToCallCenter, "datetime") StatusOrder
        from VCCC.T_APPEAL_STATUSES        
    ) ordered 
where ordered.AppealStatusesID = ass.AppealStatusesID;

ALTER TABLE VCCC.T_APPEAL_STATUSES ALTER COLUMN "statusorder" SET NOT NULL;

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