---

/*пока только для УОПС-П:
	ищем руководителя сотрудника, создающего выгрузку, с доверенностью;
	если его нет, то берем гендира из настроек фонда*/
with recursive d as
(
	select dv.id, dv.parent_id, dv.name as division, dv.id_manager_department_employee as chief_id, 
	ch.last_name, ch.first_name, ch.middle_name, pos.id as position_id, pos.position, 1 as level_id
	from ourpension.agent a 
		join ourpension.subdivision dv on a.code_subdivision = dv.id
		join ourpension.agent ch on dv.id_manager_department_employee = ch.id
		join ourpension.agent_position pos on pos.agent_id = ch.id
	where a.user_id = :user_id
		and :registerType in('УОПС-П') 
	union all
	select dv.id, dv.parent_id, dv.name as division, dv.id_manager_department_employee as chief_id, 
	ch.last_name, ch.first_name, ch.middle_name, pos.id as position_id, pos.position, d.level_id + 1 as level_id
	from d
		join ourpension.subdivision dv on d.parent_id = dv.id
		join ourpension.agent ch on dv.id_manager_department_employee = ch.id
		join ourpension.agent_position pos on pos.agent_id = ch.id
),
trusted_chief as
(
	select level_id, row_number() over(order by level_id desc) as rn, chief_id, 
		last_name as chief_last_name, first_name as chief_first_name, middle_name as chief_middle_name, 
		position_id, position as chief_position
	from d
	where position = 'Директор департамента'
		and exists
		(select 1
			from ourpension.letter_of_attorney l
				join ourpension.letter_of_attorney_document_type ladt on l.id = ladt.loa_id
				join ourpension.document_type_for_sign dt on dt.id = ladt.dt_id 
					and dt.id = 9 --Руководитель для выгрузкок XML ОПС ПФР
			where l.agent_position_id = d.position_id
				and l.expire_date >= current_date
		)
)
insert into report.smev_register
	(id,  number, 
	 date, year, 
	 smev_register_type_code, username, guid, 
	 fund_pfr_name, juridical_form, short_name, inn, kpp, 
	 email, fax_number, address, postcode, 																				
	 city_name, city_prefix, street_name, street_prefix, house,  
	 director_position, director_last_name, director_first_name, director_middle_name, phone_number, 
	 agent_last_name, agent_first_name, agent_middle_name, repeated, accepted, description)
select  :registerId,
	 case when :registrationNumberSequence is not null THEN cast (nextval(:registrationNumberSequence) as varchar (10)) 
				else(select 	 
							cast ((serial_number + 1) as varchar (10)) 
						 from dev.unique_number where code = 'smev_register')
	 end as number,
   now() as date, date_part('year', now()) as year,
	 :registerType,	:username,  :guid,
	 f.name_in_pfr, 'Акционерное общество' as juridical_form, e.name, e.inn, e.kpp,
	 e.email, regexp_replace(e.fax_number, '[^0-9]', '', 'g'),  aj.address_text, aj.postcode,
   aj.city_name, aj.city_prefix, aj.street_name, aj.street_prefix, aj.house,
	 case when ch.chief_id is not null then chief_position else f.director_position end as director_position, 
	 case when ch.chief_id is not null then chief_last_name else split_part(f.director, ' ', 1) end,
   case when ch.chief_id is not null then chief_first_name else split_part(f.director, ' ', 2) end,
   case when ch.chief_id is not null then chief_middle_name else split_part(f.director, ' ', 3) end,
	 regexp_replace(e.phone_number, '[^0-9]', '', 'g'),
	 a.last_name, a.first_name, a.middle_name, 	
	 cast( :repeated as boolean), now() as accepted,  :description
from ourpension.fund f
	join ourpension.entity e on e.id = f.entity_id
	join ourpension.address aj on aj.id = e.registration_address_id
	join ourpension.fund_branch fb on f.id = fb.fund_id
	join ourpension.agent a on a.fund_branch_id = fb.id
	left join trusted_chief ch on ch.rn = 1
where a.user_id = :user_id



update dev.unique_number 
	set serial_number = serial_number+1
where code = 'smev_register'