DROP FUNCTION IF EXISTS stor.fn_GetUnpackMarkMessageForSSCC;
DROP PROCEDURE IF EXISTS stor.sp_GetUnpackMarkMessageForSSCC;

CREATE PROCEDURE sp_GetUnpackMarkMessageForSSCC
(
	"sc" uuid,
   INOUT _ret uuid
)
    language plpgsql
as
$$
Begin 
	with recursive ShippingContainer
	as
	(
		SELECT	
			sh.ShippingContainerID
		,	sh.SSCC_Parent
		,	sh.StorageInvoice
		,	sh.SSCC
		,	sh.Recursive
		,	0 as Lvl
		FROM STOR.T_SHIPPING_CONTAINER sh
		where sh.ShippingContainerID = "sc"
		union all
		SELECT
			sh.ShippingContainerID
		,	sh.SSCC_Parent
		,	sh.StorageInvoice
		,	sh.SSCC
		,	sh.Recursive
		,	scR.Lvl + 1
		FROM STOR.T_SHIPPING_CONTAINER sh
		join ShippingContainer scR on sh.ShippingContainerID = scr.SSCC_Parent
	)
	, sc as
	(
		select 
			sc.ShippingContainerID
		,	sc.StorageInvoice
		,	sc.SSCC
		,	sc.Recursive
		,	mm1.MarkMessageID as sc_MarkMessage
		,	mm2.MarkMessageID as si_MarkMessage
		,	row_number() over(order by sc.lvl desc) as rn
		from ShippingContainer sc
		left join lateral
		(
			select mm.MarkMessageID from STOR.T_MARK_MESSAGE mm where mm.MessageType = 0 and mm.STATUS = 1 and mm.Schema = 912 and mm.ObjId = sc.ShippingContainerID limit 1
		) mm1 ON TRUE
		left join lateral
		(
			select mm.MarkMessageID from STOR.T_MARK_MESSAGE mm where mm.MessageType = 0 and mm.STATUS = 1 and mm.Schema = 912 and mm.ObjId = sc.StorageInvoice and mm.Message like '%' || sc.SSCC || '%' limit 1
		) mm2 ON TRUE
	) 
	select into _ret
		coalesce(case when t2.rn < t1.rn or (t2.rn is not null and t1.rn is null) then t2.si_markmessage else t1.sc_markmessage end, sc.sc_markmessage, sc.si_markmessage)	
	from sc sc
	left join lateral
	(
		select mm.sc_MarkMessage, mm.rn from sc mm where mm.Recursive = '1' and mm.sc_MarkMessage is not null and sc.rn >= mm.rn order by sc.rn limit 1
	) t1 on true
	left join lateral
	(
		select mm.si_MarkMessage, mm.rn from sc mm where mm.si_MarkMessage is not null and sc.rn >= mm.rn order by mm.rn limit 1
	) t2 on true
	limit 1;
End;
$$;