DROP PROCEDURE IF EXISTS public.sp_dropschetitemfacts;
DROP FUNCTION IF EXISTS public.sp_dropschetitemfacts;

CREATE PROCEDURE public.sp_dropschetitemfacts(IN year int, IN  quarter int, IN month int, IN ids xml = '')
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_year int := year;
    v_quarter int := quarter;
    v_month int := month;
    v_ids XML := ids;
BEGIN
    --RAISE NOTICE 'Парсим идентификаторы из XML %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    drop table if exists temp_ids;
    IF COALESCE(v_ids::text, '') != '' THEN        
    create temp table temp_ids as
    SELECT 
        id
    FROM XMLTABLE ('/ids/id' PASSING v_ids COLUMNS
         id uuid path '.'
    );
    ELSE
        create temp table temp_ids(id uuid);
    END IF;
    
    --RAISE NOTICE 'Помечаем связанные группы факта, что они требуют пересчёта %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    update IES.T_ITEM_FACT f
    set NeedValuesUpdate = true
    from IES.T_SCHET_ITEM_FAKT sif
        left join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.ObjectID
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.ItemFact is not null
            and
        sif.PeriodYear = v_year
            and
        (v_quarter = 0 or sif.PlanQuarter = v_quarter)
            and
        (v_month = 0 or sif.PlanMonth = v_month)
            and
        (not exists(select * from temp_ids) or sz.Schet in (select id from temp_ids))
            and 
        sif.ItemFact = f.ItemFactID;
         
    --RAISE NOTICE 'Удаляем элементы среза принятых вручную случаев %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete FROM IES.T_PLAN_CUT_ITEM pci
    using IES.T_SCHET_ITEM_FAKT sif
        left join IES.T_SCHET_SLUCH ss on ss.SchetSluchID = sif.ObjectID
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.PeriodYear = v_year
            and
        (v_quarter = 0 or sif.PlanQuarter = v_quarter)
            and
        (v_month = 0 or sif.PlanMonth = v_month)
            and
        (not exists(select * from temp_ids) or sz.Schet in (select id from temp_ids))
            and
        sif.IsMek = false
           and
       pci.SchetItemFakt = sif.SchetItemFaktID;

       --RAISE NOTICE 'Удаляем все записи факта без элементов среза %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
    delete from IES.T_SCHET_ITEM_FAKT sif
    using IES.T_SCHET_SLUCH ss
        left join IES.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
    where sif.PeriodYear = v_year
            and
        (v_quarter = 0 or sif.PlanQuarter = v_quarter)
            and
        (v_month = 0 or sif.PlanMonth = v_month)
            and
        (not exists(select * from temp_ids) or sz.Schet in (select id from temp_ids))
            and
        not exists
        (
            select *
            from IES.T_PLAN_CUT_ITEM pci
            where pci.SchetItemFakt = sif.SchetItemFaktID
        )
            and 
        ss.SchetSluchID = sif.ObjectID;

        drop table temp_ids;

    -- Удаляем все записи факта по случаю без валидной ссылки на случай
    delete from IES.T_SCHET_ITEM_FAKT sif
    where sif.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e."schema" = 'IES' and e.EntName = 'SCHET_ITEM_FAKT_SLUCH' limit 1)
            and
        not exists
        (
            select *
            from IES.T_SCHET_SLUCH ss
            where ss.SchetSluchID = sif.ObjectID
        );

--*/
end;
$procedure$;
