create function cl_get_call_log_by_callid(filtered_callid integer) returns SETOF cl_call_log_data
    language plpgsql
as
$$


DECLARE
call_info record;
	log_data cl_call_log_data;
	connected_log_data cl_call_log_data;
BEGIN
for call_info in
		--
		with
			calls as (
				select distinct on (cl_calls.id)
					cl_calls.id
				from
					cl_calls
					join cl_segments segments on segments.call_id = cl_calls.id
					join cl_participants src_participants on src_participants.id = segments.src_part_id
					join cl_participants dst_participants on dst_participants.id = segments.dst_part_id
					join cl_party_info_view src_info on src_info.id = src_participants.info_id
					join cl_party_info_view dst_info on dst_info.id = dst_participants.info_id
				where
					cl_calls.id = filtered_callid
				group by cl_calls.id
				),
			segments as (
				select
					calls.id as call_id,
					seq_order,
					seq_group,
					cl_segments.start_time, cl_segments.end_time,
					src_part_id as src_part_id,
					dst_part_id as dst_part_id,
					cl_segments.type,
					action_id as act ,
					action_party_id as act_part_id
				from cl_segments join calls on calls.id = cl_segments.call_id)
select
    segments.call_id,
    seq_order,
    seq_group,
    segments.start_time as start_time,
    segments.end_time as end_time,
    type as seg_type,
    src_info.dn_type as src_dn_type,
    src_info.dn as src_dn,
    src_info.caller_number as src_caller_number,
    src_info.extended_display_name as src_display_name,
    src_participants.recording_url src_recording_url,
    dst_info.dn_type as dst_dn_type,
    dst_info.dn as dst_dn,
    dst_info.caller_number as dst_caller_number,
    dst_info.extended_display_name as dst_display_name,
    dst_participants.recording_url dst_recording_url,
    segments.act as act,
    act_info.dn_type as act_dn_type,
    act_info.dn as act_dn,
    act_info.caller_number as act_caller_number,
    act_info.extended_display_name as act_display_name,
    row_number() over (PARTITION BY segments.call_id order by segments.call_id, segments.seq_order desc nulls last) as subrow_desc_number
from
    segments
        join cl_participants src_participants on src_participants.id = segments.src_part_id
        join cl_participants dst_participants on dst_participants.id = segments.dst_part_id
        join cl_party_info_view src_info on src_info.id = src_participants.info_id
        join cl_party_info_view dst_info on dst_info.id = dst_participants.info_id
        left outer join (cl_participants act_participants join cl_party_info_view act_info on act_info.id = act_participants.info_id) on act_participants.id = segments.act_part_id
order by call_id, seq_order
         --
    loop
		log_data := (
				call_info.call_id, call_info.seq_group, call_info.start_time,
				call_info.src_dn_type, call_info.src_dn, call_info.src_caller_number, call_info.src_display_name,
				call_info.dst_dn_type, call_info.dst_dn, call_info.dst_caller_number, call_info.dst_display_name,
				call_info.act, call_info.act_dn_type, call_info.act_dn, call_info.act_caller_number, call_info.act_display_name,
                case when call_info.seg_type = 1 then call_info.end_time - call_info.start_time end,
                case when call_info.seg_type = 2 then call_info.end_time - call_info.start_time end
                );

log_data.answered := cl_is_answered_common(call_info.seg_type, log_data.source_type, log_data.destination_type, log_data.action_type);

		log_data.recording_url := coalesce(call_info.src_recording_url, call_info.dst_recording_url, '');

		log_data.subrow_desc_number = call_info.subrow_desc_number;
		if log_data.source_type = 4 and log_data.action_type = 12 then
			continue;
end if;
	    if call_info.seg_type = 1 and log_data.action_type = 15 then
			continue;
end if;

		if log_data.action_type = 1 and log_data.destination_type not in (2, 3) then
			connected_log_data := log_data;
else
			if connected_log_data.call_id is not null then
				log_data.ringing_duration := connected_log_data.ringing_duration;
				log_data.start_time = connected_log_data.start_time;

				if log_data.destination_type = 12 then
					log_data.destination_type := connected_log_data.destination_type;
					log_data.destination_dn := connected_log_data.destination_dn;
					log_data.destination_caller_id := connected_log_data.destination_caller_id;
					log_data.destination_display_name := connected_log_data.destination_display_name;
end if;

				connected_log_data.call_id := null;
end if;

			return next log_data;
end if;
end loop;
END;
$$;

alter function cl_get_call_log_by_callid(integer) owner to phonesystem;

