-- creation d'une fonction qui met à jour les données si elles sont valables et plus récentes
drop function if exists public.maj_lea_agbio_op_identification(); 
create function public.maj_lea_agbio_op_identification() 
returns varchar as $maj_lea_agbio_op_identification$
	begin  

------------------- LISTE DES ACTIONS A REALISER	---------------
-------------------------- UPDATE DU NOUVEAU SIRET SI IL EST DIFFERENT ET VALIDE ------------------
		-- verifie s'il y a un siret dans la table op_identification qui est different du siret valide le plus recent
		if exists (select distinct siret_rec.id, siret_rec.siret, translate(siret_rec.siret,'0123456789','@@@@@@@@@@')
						from siret_rec 
							left join lea_agbio_operateur_identification on siret_rec.id = lea_agbio_operateur_identification.id_operateur 
																		and lea_agbio_operateur_identification.siret = siret_rec.siret 
						where lea_agbio_operateur_identification.id_operateur is null)
		
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set siret = j.siret  
			 		from 
			 			export_br_oper
					 	left join (select distinct siret_rec.id, siret_rec.siret
						from export_br_oper 
							left join lea_agbio_operateur_identification on export_br_oper.id = lea_agbio_operateur_identification.id_operateur and lea_agbio_operateur_identification.siret = export_br_oper.siret 
							left join siret_rec on export_br_oper.id = siret_rec.id
						where translate(export_br_oper.siret,'0123456789','@@@@@@@@@@')='@@@@@@@@@@@@@@'
							and export_br_oper.siret<>'00000000000000'
							and lea_agbio_operateur_identification.id_operateur is null
							and export_br_oper.id = siret_rec.id
						 ) j on export_br_oper.id = j.id
						where j.id = lea_agbio_operateur_identification.id_operateur ;
		end if;
	
	---------------------- UPDATE DU NOUVEAU RS SI IL EST DIFFERENT ------------------
		-- verifie s'il y a un rs dans la table op_identification qui est different du rs valide le plus recent
		if exists (select distinct rs_rec.id, rs_rec.rs
						from rs_rec 
							left join lea_agbio_operateur_identification on rs_rec.id = lea_agbio_operateur_identification.id_operateur 
																		and lea_agbio_operateur_identification.rs = rs_rec.rs 
						where lea_agbio_operateur_identification.id_operateur is null)
		
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set rs = j.rs  
			 		from 
			 			export_br_oper
					 	left join (select distinct rs_rec.id, rs_rec.rs
						from export_br_oper 
							left join lea_agbio_operateur_identification on export_br_oper.id = lea_agbio_operateur_identification.id_operateur and lea_agbio_operateur_identification.rs = export_br_oper.rs 
							left join rs_rec on export_br_oper.id = rs_rec.id
						where lea_agbio_operateur_identification.id_operateur is null
							and export_br_oper.id = rs_rec.id
						 ) j on export_br_oper.id = j.id
						where j.id = lea_agbio_operateur_identification.id_operateur ;
		end if;
		return null;
	
------------ LISTE DES TABLES AVEC LES INFOS VALIDES ET PLUS RECENTES --------
		-- enregistre dans une "table" provisoire le siret valide le plus récent
		with siret_rec as(
			select i.id, 
				max(i.annee) as "annee",
				max(case
						when i.row_no = 1 then i.siret
					end) as "siret"
			from (	select export_br_oper.id, export_br_oper.annee, export_br_oper.siret,
						row_number() over (partition by id order by annee desc) as "row_no"
					from export_br_oper
					where translate(export_br_oper.siret,'0123456789','@@@@@@@@@@')='@@@@@@@@@@@@@@'
						and export_br_oper.siret<>'00000000000000') i
			group by i.id),
			
		-- enregistre dans une "table" provisoire le rs valide le plus récent
		rs_rec as(
			select i.id, 
				max(i.annee) as "annee",
				max(case
						when i.row_no = 1 then i.rs
					end) as "rs"
			from (	select export_br_oper.id, export_br_oper.annee, export_br_oper.rs,
						row_number() over (partition by id order by annee desc) as "row_no"
					from export_br_oper
					where rs is not null) i
			group by i.id)
			
	end;
$maj_lea_agbio_op_identification$ language plpgsql;