DROP PROCEDURE IF EXISTS public.sp_reg_4_1();

CREATE PROCEDURE public.sp_reg_4_1()
LANGUAGE 'plpgsql'
AS $BODY$
	
BEGIN


--=============================
--Изменение у кого поменялось прикрепление
--=============================
DROP TABLE IF EXISTS temp_attachment_changed;

CREATE TEMP TABLE temp_attachment_changed(
    insuredid UUID,
    attachmentactual UUID,
    newmo CHARACTER VARYING(6),
    attachdate TIMESTAMP WITHOUT TIME ZONE,
    newattachmentid UUID,
	enp CHARACTER VARYING(20)
) TABLESPACE pg_default;	

INSERT INTO temp_attachment_changed(insuredid, attachmentactual, newmo, attachdate, newattachmentid, enp)
select i.insuredid, i.attachmentactual, ppl.lpu, ppl.lpudt, gen_random_uuid(), i.enp
from ies.t_insured i
join ies.t_attachment_info ai on i.attachmentactual = ai.attachmentinfoid and ai.attacmenttype = 0 
join ies.tmp_people ppl on cast(i.tfoms_id as int) = ppl.id and ppl.lpu <> ai.mo --прикрепление изменилось
where ppl.lpu is not null --есть прикрепление в атлантике
	and ppl.lpudt is not null
	and ppl.lpudx is null
	and ppl.lpudt > ai.attachdate; 

--открепление
INSERT INTO ies.t_attachment_info
    (attachmentinfoid
    ,insured
    ,attachdate
    ,mo
    ,attacmenttype
    ,motype
    ,cause
    ,datecreate
	,enp)
SELECT 
	gen_random_uuid()
	,insured
	,a.attachdate - INTERVAL '1 day'
	,mo
	,1 as attacmenttype --открепление
	,0
	,1
	,now()
	,a.enp
FROM temp_attachment_changed a
JOIN ies.t_attachment_info ai ON a.attachmentactual = ai.attachmentinfoid;

--поставим дату открепления(служебное)
UPDATE ies.t_attachment_info ai
SET detachdate = a.attachdate - INTERVAL '1 day'
FROM temp_attachment_changed a
WHERE a.attachmentactual = ai.attachmentinfoid;

--Создаём новое прикрепление
INSERT INTO ies.t_attachment_info
           (attachmentinfoid
           ,insured
           ,attachdate
           ,mo
           ,attacmenttype
           ,motype
           ,cause
           ,datecreate
           ,enp)
SELECT	 a.newattachmentid
		,a.insuredid
		,a.attachdate 
		,a.newmo 
		,0  --прикрепление
		,0  --терапия
		,1  --смена адреса
		,now() 
		,a.enp
from temp_attachment_changed a;

--обновим актуальное прикрепление
UPDATE ies.t_insured i
SET attachmentactual = a.newattachmentid
FROM temp_attachment_changed a
WHERE i.insuredid = a.insuredid;

--=============================
--у кого нет прикрепления добавим
--=============================
DROP TABLE IF EXISTS temp_no_attachment;

CREATE TEMP TABLE temp_no_attachment(
    insuredid UUID,
    attachmentactual UUID,
    newmo CHARACTER VARYING(6),
    attachdate TIMESTAMP WITHOUT TIME ZONE,
    newattachmentid UUID,
	enp CHARACTER VARYING(20)
) TABLESPACE pg_default;

INSERT INTO temp_no_attachment(insuredid, attachmentactual, newmo, attachdate, newattachmentid, enp)
SELECT i.insuredid, i.attachmentactual, ppl.lpu AS newmo, ppl.lpudt AS attachdate, gen_random_uuid(), i.enp
FROM ies.t_insured i
JOIN ies.tmp_people ppl ON  cast(i.tfoms_id as int) = ppl.id
WHERE ppl.lpu IS NOT NULL --есть прикрепление в атлантике
	AND ppl.lpudt IS NOT NULL
	AND (ppl.lpudx is null or (ppl.lpudx is not null and ppl.lpudx>= '20220101' and ppl.lpudx >=ppl.lpudt))
	AND i.attachmentactual is NULL --нет прикрепления в ies
	AND i.insuredid not in (select ai.insured from ies.t_attachment_info ai where ai.insured = i.insuredid);

INSERT INTO ies.t_attachment_info
           (attachmentinfoid
           ,insured
           ,attachdate
           ,mo
           ,attacmenttype
           ,motype
           ,cause
           ,datecreate
           ,enp)
SELECT	 newattachmentid
		,insuredid
		,attachdate
		,newmo AS mo
		,0 AS attacmenttype
		,0 AS motype
		,1 AS cause
		,now() AS datecreate
		,enp
FROM temp_no_attachment;

--обновим актуальное прикрепление
UPDATE ies.t_insured i
SET attachmentactual = a.newattachmentid
FROM temp_no_attachment a
WHERE i.insuredid = a.insuredid;

--=============================
--Удалим прикрепление, у кого больше нет
--=============================
DROP TABLE IF EXISTS temp_drop_attachment;

CREATE TEMP TABLE temp_drop_attachment(
    insuredid UUID,
    attachmentactual UUID,
    mo CHARACTER VARYING(6),
    attachdate TIMESTAMP WITHOUT TIME ZONE,
    newattachmentid UUID,
	enp CHARACTER VARYING(20)
) TABLESPACE pg_default;

INSERT INTO temp_drop_attachment(insuredid, attachmentactual, mo, attachdate, newattachmentid, enp)
SELECT i.insuredid, i.attachmentactual, ai.mo, ppl.lpudx, gen_random_uuid(), i.enp
FROM ies.t_insured i
JOIN ies.t_attachment_info ai ON i.attachmentactual = ai.attachmentinfoid AND ai.attacmenttype = 0 
JOIN ies.tmp_people ppl ON cast(i.tfoms_id as int) = ppl.id
WHERE ppl.lpu IS NULL 
	OR (ppl.lpu IS NOT NULL AND ppl.lpudt IS NOT NULL AND ppl.lpudx is not null and ppl.lpudx>=  ai.attachdate and ppl.lpudx >=ppl.lpudt);

--поставим дату открепления(служебное)
UPDATE ies.t_attachment_info ai
SET detachdate = a.attachdate 
FROM temp_drop_attachment a
WHERE a.attachmentactual = ai.attachmentinfoid;

INSERT INTO ies.t_attachment_info
           (attachmentinfoid
           ,insured
           ,attachdate
           ,mo
           ,attacmenttype
           ,motype
           ,cause
           ,datecreate)
SELECT	 newattachmentid
		,insuredid
		,attachdate
		,mo
		,1 AS attacmenttype
		,0 AS motype
		,1 AS cause
		,now()
FROM temp_drop_attachment;



--=============================
--обновим актуальное прикрепление на всех записях, если что-то сломалось
--=============================
-- ;with ai as(
-- 			select row_number() over (partition by ai.insured order by ai.AttachDate desc) as cnt, *
-- 			from ies.T_ATTACHMENT_INFO ai),
-- ai2 as (
-- 		select *
-- 		from ai
-- 		where attacmenttype = 0 and cnt = 1)
-- update ies.t_insured t1 
-- set AttachmentActual = AttachmentInfoid
-- from ies.t_insured i
-- join ai2 on i.insuredid = ai2.insured
-- where t1.insuredid=i.insuredid;


END
$BODY$;

