-- SET NAME
SET application_name = 'COREG - ciblage generique';

SELECT localtimestamp;

-- DOMAINES
with aff_domaine as (
	select 
		coregistration_coregistration_affichage
	from coregistration.coregistration
	inner join coregistration.campagne_annonceur on campagne_annonceur_id = coregistration_campagne_annonceur
	inner join coregistration.campagne_annonceur_domaine_exclusion on campagne_annonceur_domaine_exclusion_campagne_annonceur = campagne_annonceur_id
	inner join coregistration.domaine_exclusion on domaine_exclusion_id = campagne_annonceur_domaine_exclusion_domaine_exclusion
	where coregistration_champ = 1
	and coregistration_eligible = -1
	and lower(substring(coregistration_valeur from '@(.*)\.')) = domaine_exclusion_libelle
	group by coregistration_coregistration_affichage
), insert_domaine as (
	insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
	select coregistration_coregistration_affichage
	from aff_domaine
)
update coregistration.coregistration
set coregistration_eligible = 3
where coregistration_coregistration_affichage in (
	select coregistration_coregistration_affichage
	from aff_domaine
);

-- AGE
with aff_age as (
	select 
		coregistration_coregistration_affichage
	from coregistration.coregistration
	inner join coregistration.campagne_annonceur on campagne_annonceur_id = coregistration_campagne_annonceur
	where coregistration_champ = 12
	and coregistration_eligible = -1
	and (
		extract('year' from age(coregistration_date, general.cast_to_date(coregistration_valeur))) < coalesce(campagne_annonceur_age_min, 0)
		or extract('year' from age(coregistration_date, general.cast_to_date(coregistration_valeur))) > coalesce(campagne_annonceur_age_max, 99)
	)
	
), insert_age as (
	insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
	select coregistration_coregistration_affichage
	from aff_age
)
update coregistration.coregistration
set coregistration_eligible = 3
where coregistration_coregistration_affichage in (
	select coregistration_coregistration_affichage
	from aff_age
);

-- CIVILITE
with aff_civilite as (
	select 
		coregistration_coregistration_affichage
	from coregistration.coregistration
	left join coregistration.campagne_annonceur_civilite
		inner join coregistration.civilite on civilite_id = campagne_annonceur_civilite_civilite
	on campagne_annonceur_civilite_campagne_annonceur = coregistration_campagne_annonceur and lower(coregistration_valeur) = lower(civilite_libelle)
	where coregistration_champ = 4
	and coregistration_eligible = -1
	and campagne_annonceur_civilite_campagne_annonceur is null
	group by coregistration_coregistration_affichage, coregistration_valeur
), insert_civilite as (
	insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
	select coregistration_coregistration_affichage
	from aff_civilite
)
update coregistration.coregistration
set coregistration_eligible = 3
where coregistration_coregistration_affichage in (
	select coregistration_coregistration_affichage
	from aff_civilite
);


-- COREGISTRATION
with aff_departement as (
        select
                coregistration_coregistration_affichage
        from coregistration.coregistration
        inner join coregistration.campagne_annonceur on campagne_annonceur_id = coregistration_campagne_annonceur
        inner join coregistration.campagne_annonceur_departement_exclusion on campagne_annonceur_departement_exclusion_campagne_annonceur = campagne_annonceur_id
        inner join coregistration.departement_exclusion on departement_exclusion_id = campagne_annonceur_departement_exclusion_departement_exclusion
        where coregistration_champ = 6
        and coregistration_eligible = -1
        and coregistration_valeur like concat(departement_exclusion_code_postal, '%')
        group by coregistration_coregistration_affichage
), insert_departement as (
        insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
        select coregistration_coregistration_affichage
        from aff_departement
)
update coregistration.coregistration
set coregistration_eligible = 3
where coregistration_coregistration_affichage in (
        select coregistration_coregistration_affichage
        from aff_departement
);