-- =============================================
-- Author:		Vitacore
-- Create date: 2023/06/09
-- Description:	Процедура апдейтит два поля у DOCTOR_INSPECTION LastVisit и VisitCount.
-- =============================================
drop procedure if exists public.sp_UpdateLastVisit;
CREATE procedure public.sp_UpdateLastVisit
(
    "DoctorInspection" uuid
)
AS
$BODY$
Begin
    update AKUZ.T_DOCTOR_INSPECTION di
	set LastVisit = coalesce(lv.VisitID, di.VisitID)
    ,   VisitCount = coalesce(lv.VisitCount, 1)
	from AKUZ.T_DOCTOR_INSPECTION tdi
    left join lateral
    (
        select tt.VisitID, tt.VisitCount
        from
        (
            select t1.VisitID, 1 + count(*) over (partition by t1.DoctorInspection) as VisitCount
            from AKUZ.T_DOCTOR_INSPECTION_RE t1
            where t1.DoctorInspection = "DoctorInspection"
        ) tt
        inner join AKUZ.T_VISIT t2  on tt.VisitID = t2.VisitID  --di.VisitID
        order by t2.VisitDate desc
        limit 1
    ) lv on true
    where di.VisitID = tdi.VisitID
    and di.VisitID = "DoctorInspection";
    return;
End;
$BODY$
  LANGUAGE plpgsql;