---

источник данных для заполнения полей "Предыдущий страховщик" и "Следующий страховщик"

-- DROP FUNCTION ourpension.get_contract_fields_main(int4);

CREATE OR REPLACE FUNCTION ourpension.get_contract_fields_main(v_contract_id integer)
 RETURNS TABLE(id integer, number json, date text, investor character varying, fund_name character varying, fund_name_short character varying, service_type_rus text, invest_type_text text, contract_life_cicle character varying, sale_channel_id integer, sale_channel_text character varying, comment text, action_date text, agent_code character varying, agent_name text, previous_insurer_name character varying, new_insurer_name character varying, annulled_date text, create_date text, fund_branch_name character varying, contract_class_name character varying, name_in_pfr character varying, contract_type text, affiliate_program_contract_id integer, affiliate_program_contract_text text, family_member boolean, department_type character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
-- Функция выводит поля Общей информации по договору. Выделено для облегчения запроса в модели Карточки договора.
-- Создано: 01.2026 dv.pakhomov
-- Изменения:
-- 24.03.2026 dv.pakhomov Убрал задвоение записей из-за неправильного подключения ourpension.contract_class_history
-- 24.04.2026 dv.pakhomov Получение названий фондов из справочника ourpension.fund      
    RETURN query
    WITH s AS(
        SELECT         
            c.id, 
            CASE
                WHEN c.service_type = 'NPO' AND c.individual_id IS NOT NULL THEN 'ИПП'
                WHEN c.service_type = 'NPO' AND c.entity_id IS NOT NULL THEN 'КПП'
                WHEN c.service_type = 'PDS' THEN 'ДДС'
                ELSE ''
            END as contract_type,
            to_json(c.number) as number, 
            c.date, 
            coalesce(e.short_name, i.last_name || ' ' || i.first_name || ' ' || i.middle_name) as investor,
            coalesce(uat.activity_rus_type, 'ОПС') as service_type_rus, 
            case c.invest_type
                when 1 then 'Инвестиционный'
                when 2 then 'Страховой'
            end as invest_type_text,
            c.sale_channel_id, 
            c.comment,
            ha.short_name as department_type,
            ag.code as agent_code,
            coalesce(ag.last_name,'') || ' ' || left(coalesce(ag.first_name, ''), 1) || '.' || coalesce(left(ag.middle_name, 1) || '.', '') as agent_name,
            pi.previous_insurer_pfr_name previous_insurer_name,
            ni.new_insurer_pfr_name new_insurer_name,
            cch.begin_date as create_date,
            fb.name as fund_branch_name,
            cch.class_name, 
            c.affiliate_program_contract_id,
            to_json( CONCAT( ce.number, ' (', ea.short_name, ')' ) )::text as affiliate_program_contract_text,
            fsg.name_full as fund_name,
            fsg.name_short as fund_name_short,
            fsr.name_in_pfr as name_in_pfr,
            c.family_member
        
        FROM ourpension.contract c        
            LEFT JOIN ourpension.individual i ON c.individual_id = i.id
            LEFT JOIN ourpension.fund fsg ON fsg.id = c.sign_fund_id    -- Заключивший фонд
            LEFT JOIN ourpension.fund fsr ON fsr.id = c.fund_id          -- Фонд-источник
            LEFT JOIN ourpension.agent ag 
            ON ag.id = c.agent_id
            LEFT JOIN ourpension.fund_branch fb 
            ON fb.id = ag.fund_branch_id
            LEFT JOIN ourpension.uv_activity_type uat ON c.client_type = uat.client_type AND c.service_type = uat.activity_eng_type
            LEFT JOIN ourpension.ops_get_contract_new_insurer(c.id) ni ON c.service_type = 'OPS'
            LEFT JOIN ourpension.ops_get_contract_previous_insurer(c.id) pi ON c.service_type = 'OPS'
            LEFT JOIN ourpension.contract ce 
            ON ce.id = c.affiliate_program_contract_id
            LEFT JOIN ourpension.entity ea 
            ON ea.id = ce.entity_id        
            LEFT JOIN ourpension.holding ha
            ON ha.id = ea.holding_id
            LEFT JOIN LATERAL 
            (
                SELECT 
                    cch1.begin_date,
                    cc1."name" AS class_name
                    FROM ourpension.contract_class_history cch1
                        INNER JOIN ourpension.contract_classes cc1 ON cch1.class_id = cc1.id
                    WHERE cch1.contract_id = c.id
                    ORDER BY cch1.begin_date DESC
                    LIMIT 1
                ) cch ON true
            LEFT JOIN ourpension.entity e ON e.id = c.entity_id
    
        WHERE c.id = v_contract_id        
    ),
    cte_statuses_l AS
    (   -- Последний (актуальный) статус
        SELECT
            cs1."name" AS status_name,
            CASE WHEN sh1.code IN ( 'EA0', 'JA0', 'PA0' ) THEN sh1.status_date END as annulled_date
        FROM ourpension.contract_statuses_history sh1
            INNER JOIN ourpension.contract_statuses cs1 on cs1.code = sh1.code
        WHERE sh1.contract_id = v_contract_id
            AND CURRENT_TIMESTAMP BETWEEN 
                sh1.status_date AND COALESCE( sh1.status_end_date, 'infinity' )
        LIMIT 1
    ),
    cte_statuses_a AS
    (   -- Статус введения в действие
        SELECT sh.status_date
        FROM ourpension.contract_statuses_history sh        
        WHERE sh.contract_id = v_contract_id
            AND sh.code IN ( 'CA0', 'HA0', 'MA0' )
        ORDER BY sh.status_date
        LIMIT 1
    )
    SELECT
        s.id,
        s.number,
        to_char(s.date, 'dd.MM.yyyy') as "date",
        s.investor,
        s.fund_name,
        s.fund_name_short,
        s.service_type_rus,
        s.invest_type_text,
        csl.status_name as contract_life_cicle,
        s.sale_channel_id,
        sc.name as sale_channel_text,
        s.comment,
        to_char(csa.status_date, 'dd.MM.yyyy') as action_date,
        s.agent_code,
        s.agent_name,
        s.previous_insurer_name,
        s.new_insurer_name,
        to_char(csl.annulled_date, 'dd.MM.yyyy') as annulled_date,
        to_char(s.create_date, 'dd.MM.yyyy') as create_date,
        s.fund_branch_name,
        s.class_name as contract_class_name,
        s.name_in_pfr,
        s.contract_type,
        s.affiliate_program_contract_id, 
        s.affiliate_program_contract_text,
        s.family_member,
        s.department_type
    FROM s
        INNER JOIN cte_statuses_l csl ON true
        LEFT JOIN ourpension.information_source sc on sc.id = s.sale_channel_id
        LEFT JOIN cte_statuses_a csa ON true;
   
END;
$function$
;



-- DROP FUNCTION ourpension.ops_get_contract_previous_insurer(int4);

CREATE OR REPLACE FUNCTION ourpension.ops_get_contract_previous_insurer(p_contract_id integer)
 RETURNS TABLE(previous_insurer_pfr_name character varying, previous_insurer_inn character varying, fund_id integer, privious_insurer_source character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
v_previous_insurer_pfr_name varchar(500) = null;
v_previous_insurer_inn varchar(100) = null;
v_previous_insurer_fund_id int = null::int;
v_count_ops int =0; 
begin

select count(*)
into v_count_ops
from ourpension.contract c
where c.id = p_contract_id
and c.service_type = 'OPS';

if v_count_ops = 0 then
	return query
    	select null::varchar, null::varchar, null::int, 'Не определен'::varchar(255);  
else 		


	    select su.previos_insurer_pfr_name, su.previos_insurer_inn, f.id fund_id
	    into v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id
	from report.smev_uier su  
	  join ourpension.fund f on f.inn = su.previos_insurer_inn    
	  join ourpension.contract c on su.contract_number = c.number and su.contract_date = c.date
	where su.uier_document_type_id in (1, 4) and c.id = p_contract_id;  
	
	if not (v_previous_insurer_pfr_name is null or v_previous_insurer_inn is null or v_previous_insurer_fund_id is null) then
	  return query
	    select v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id, 'ЕРЗЛ'::varchar(255);    
	else
	  
	  select f.name_in_pfr, f.inn, f.id
	  into v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id  
	  from ourpension.court_decision_data cdd 
	join ourpension.court_decision cd on cd.id = cdd.court_decision_id 
	  join ourpension.fund f on f.id = cd.proper_insurer_id 
	  where cdd.contract_id = p_contract_id;
	  if not (v_previous_insurer_pfr_name is null or v_previous_insurer_inn is null or v_previous_insurer_fund_id is null) then
	    return query
	      select v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id, 'Решение суда'::varchar(255);    
	      else
	        select f.name_in_pfr, f.inn, f.id
	        into v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id  
	from ourpension.sharer s
	  join back_office."account" a on a.owner_sharer_id = s.id 
	  
	join back_office.transaction t on t.credit_account_id = a.id
	  join back_office.operation o on o.id = t.operation_id 
	        join back_office.operation_document od on o.operation_document_id = od.id
	          join ourpension.enums e on e.id = od.distribution_reason_enums_id and e.enum_value_abbr = 'ПереходВФонд1ыйТранш'
	          
	        join back_office.incoming_order_operation ioo on ioo.operation_id = o.id  
	          join back_office.incoming_order io on io.id = ioo.incoming_order_id
	            join ourpension.fund f on f.entity_id = io.payer_entity_id
	where s.contract_id = p_contract_id
	order by io.date asc
	limit 1;
	if not (v_previous_insurer_pfr_name is null or v_previous_insurer_inn is null or v_previous_insurer_fund_id is null) then
	  return query
	    select v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id, 'Первый транш'::varchar(255);    
	    else
	          return query
	            select v_previous_insurer_pfr_name, v_previous_insurer_inn, v_previous_insurer_fund_id, 'Не определен'::varchar(255);   
	        end if;
	  end if;
	end if; 

end if; 
end
$function$
;



-- DROP FUNCTION ourpension.ops_get_contract_new_insurer(int4);

CREATE OR REPLACE FUNCTION ourpension.ops_get_contract_new_insurer(contract_id integer)
 RETURNS TABLE(new_insurer_pfr_name character varying, new_insurer_inn character varying, fund_id integer, new_insurer_source character varying)
 LANGUAGE plpgsql
AS $function$

DECLARE
	v_new_insurer_pfr_name varchar(500) = null;
	v_new_insurer_inn varchar(100) = null;
	v_new_insurer_fund_id int = null::int;
begin
	--поиск в документах ЕРЗЛ. исходим из того, что связка номер и дата договора в ЕРЗЛах уникальна
	select su.new_insurer_pfr_name, su.new_insurer_inn, f.id fund_id, 'ЕРЗЛ'::varchar(255)
 	into v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id
	from report.smev_uier su  
	  join ourpension.fund f on f.inn = su.previos_insurer_inn	    
	  join ourpension.contract c on su.contract_number = c.number and su.contract_date = c.date
	where su.uier_document_type_id in (2, 3) and c.id = ops_get_contract_new_insurer.contract_id;  --2 -	Возврат в CФР, 3 - Переход из НПФ в другие НПФ

	if not (v_new_insurer_pfr_name is null or v_new_insurer_inn is null or v_new_insurer_fund_id is null) then
		return query	
		select v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id, 'ЕРЗЛ'::varchar(255);  	  
	else
		--поиск по решениям суда
		select f.name_in_pfr, f.inn, f.id
		into v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id  
		from ourpension.court_decision_data cdd 
		join ourpension.court_decision cd on cd.id = cdd.court_decision_id 
		  join ourpension.fund f on f.id = cd.proper_insurer_id 
		where cdd.contract_id = ops_get_contract_new_insurer.contract_id
		--and f.id <> ( select main_fund_id from tools.settings limit 1)
		;
		
		if not (v_new_insurer_pfr_name is null or v_new_insurer_inn is null or v_new_insurer_fund_id is null) then
			return query	
			select v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id, 'Решение суда'::varchar(255);  	  	
		else	
	        --поиск Выплата трансферта (ОПС) в другой НПФ
	        select f.name_in_pfr, f.inn, f.id
	        into v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id  
			from ourpension.sharer s
			  	join back_office."account" a on a.owner_sharer_id = s.id 
				join back_office.account_type at on a.account_type_id = at.id and at.mnemonics = 'В Т ОПС'  
				join back_office.transaction t on t.debit_account_id = a.id
				join back_office.outgoing_order_operation ooo on ooo.operation_id = t.operation_id  
				join back_office.outgoing_order oo on oo.id = ooo.outgoing_order_id
				join ourpension.fund f on f.entity_id = oo.recipient_entity_id
			where s.contract_id = ops_get_contract_new_insurer.contract_id
			order by oo.date asc
			limit 1;
      
			if not (v_new_insurer_pfr_name is null or v_new_insurer_inn is null or v_new_insurer_fund_id is null) then
			  return query	
				select v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id, 'Выплата трансферта (ОПС)'::varchar(255);  	  
			else	
			  return query	
				select v_new_insurer_pfr_name, v_new_insurer_inn, v_new_insurer_fund_id, 'Не определен'::varchar(255);   
			end if; 
		end if;
	end if;
end
$function$
;