/*************** PARTIE STATS COREG ****************/
SET application_name = "COREG - statistiques 1/4";


-- on vide la table de travail
truncate table coregistration.statistiques_coregistration_travail;

-- alimentation de la table de travail
insert into coregistration.statistiques_coregistration_travail (
	statistiques_coregistration_travail_date_reception,
	statistiques_coregistration_travail_site,
	statistiques_coregistration_travail_campagne_annonceur,
	statistiques_coregistration_travail_campagne_affilie,
	statistiques_coregistration_travail_eligible,
	statistiques_coregistration_travail_cout_annonceur,
	statistiques_coregistration_travail_remuneration_affilie,
	statistiques_coregistration_travail_total
)
select 
	date_trunc('day', coregistration_affichage_date) as date_reception,
	coregistration_affichage_site, 
	coregistration_campagne_annonceur,
	coregistration_campagne_affilie,
	coregistration_eligible, 
	sum(case when coregistration_eligible = 6 then coregistration_affichage_cout else 0 end) as cout_annonceur, 
	sum(case when coregistration_eligible = 6 then coregistration_affichage_remuneration else 0 end) as remuneration_affilie, 
	count(*) as total
	
from coregistration.coregistration c
inner join coregistration.coregistration_affichage ca on c.coregistration_coregistration_affichage = ca.coregistration_affichage_id
where coregistration_champ = 1
and coregistration_affichage_site is not null
and coregistration_eligible is not null
and coregistration_affichage_date >= date_trunc('day', now() - INTERVAL :interval)
and coregistration_affichage_production is true
-- and coregistration_campagne_annonceur=999

group by 
	date_trunc('day', coregistration_affichage_date), 
	coregistration_affichage_site, 
	coregistration_campagne_annonceur, 
	coregistration_campagne_affilie,
	coregistration_eligible;



-- mise a jour de l'identifiant
update coregistration.statistiques_coregistration_travail
set statistiques_coregistration_travail_identifiant = statistiques_coregistration_travail_date_reception || '_' || coalesce(statistiques_coregistration_travail_date_livraison::text, '') || '_' || statistiques_coregistration_travail_site || '_' || statistiques_coregistration_travail_campagne_annonceur || '_' || statistiques_coregistration_travail_campagne_affilie || '_' || statistiques_coregistration_travail_eligible;






/*************** PARTIE STATS AFFICHAGE ****************/


-- recuperation des stats d'affichages
truncate table coregistration.statistiques_coregistration_affichage_travail;

-- insertion des stats d'affichages dans la table de travail
insert into coregistration.statistiques_coregistration_affichage_travail (
	statistiques_coregistration_affichage_travail_date_reception,
	statistiques_coregistration_affichage_travail_site,
	statistiques_coregistration_affichage_travail_camp_annonceur,
	statistiques_coregistration_affichage_travail_camp_affilie,
	statistiques_coregistration_affichage_travail_affichage
	
)
select
        date_trunc('day', coregistration_affichage_date) as date_reception,
        coregistration_affichage_site,
        coregistration_affichage_campagne_annonceur,
        coregistration_affichage_campagne_affilie,
        count(*) as total

from coregistration.coregistration_affichage ca 
where coregistration_affichage_site is not null
and coregistration_affichage_date >= date_trunc('day', now() - INTERVAL :interval)
and coregistration_affichage_production is true
-- and coregistration_affichage_campagne_annonceur=999

group by
        date_trunc('day', coregistration_affichage_date),
        coregistration_affichage_site,
        coregistration_affichage_campagne_annonceur,
        coregistration_affichage_campagne_affilie;

-- mise a jour de l'identifiant (attention donnee sensible : ne pas changer)
update coregistration.statistiques_coregistration_affichage_travail
set statistiques_coregistration_affichage_travail_identifiant = statistiques_coregistration_affichage_travail_date_reception || '__' || statistiques_coregistration_affichage_travail_site || '_' || statistiques_coregistration_affichage_travail_camp_annonceur || '_' || statistiques_coregistration_affichage_travail_camp_affilie || '_';