SET application_name = 'COREG - check_hardbounces';

-- ETAPE 1 : mise en eligible = -3 des profils a traiter
update coregistration.coregistration
set coregistration_eligible = -3
where coregistration_eligible = -2;

-- ETAPE 2 : insertion des hardbounces dans la table export_auto_hardbounces
insert into coregistration.export_auto_hardbounces(export_auto_hardbounces_affichage)
select c.coregistration_coregistration_affichage
from coregistration.coregistration c
inner join general.hardbounce h on h.hardbounce_email = c.coregistration_valeur
where c.coregistration_champ = 1
and c.coregistration_eligible = -3;

-- ETAPE 3 : on flag les adresses nouvellement hardbounces comme traitees
update coregistration.coregistration
set coregistration_eligible = 5 -- nouvel etat hardbounce
from coregistration.export_auto_hardbounces
where export_auto_hardbounces_affichage = coregistration_coregistration_affichage
and coregistration_eligible = -3;

-- ETAPE 4 : campagne parametree SANS filtrage hardbounce => on envoie directement pour traitement
update coregistration.coregistration
set coregistration_eligible = -1
from coregistration.campagne_annonceur
where campagne_annonceur_id = coregistration_campagne_annonceur
and campagne_annonceur_hardbounce is false
and coregistration_eligible = -3;

-- ETAPE 5 : campagne parametree AVEC filtrage hardbounce => apres 2h toujours dans le meme "etat", on envoie pour traitement
update coregistration.coregistration
set coregistration_eligible = -1
from coregistration.campagne_annonceur
where campagne_annonceur_id = coregistration_campagne_annonceur
and coregistration_eligible = -3
and campagne_annonceur_hardbounce is true
and coregistration_date < now() - interval '2 HOUR';

-- ETAPE 6 : on remet les adresses restantes en attente de traitement hardbouces (toujours dans la quarantaine de 4h)
update coregistration.coregistration
set coregistration_eligible = -2
where coregistration_eligible = -3;