/*

CREATE TABLE public.vipconcours
(
  vipconcours_source character varying(150),
  vipconcours_prenom character varying(50),
  vipconcours_nom character varying(50),
  vipconcours_email character varying(150),
  vipconcours_date_inscription timestamp without time zone,
  vipconcours_ip character varying(15),
  vipconcours_ca_coregistration numeric,
  vipconcours_ca_sponsoring numeric,
  vipconcours_oid4 character varying(50),
  vipconcours_oid5 character varying(50)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.vipconcours
  OWNER TO postgres;
*/

-- truncate table public.vipconcours;

-- stats coreg par internaute
with cte_coreg as (
        select email, sum(coregistration_affichage_remuneration) as ca_coregistration
        from fdw_vipconcours.membre_tmp 
        inner join coregistration.coregistration on coregistration_valeur = email 
        inner join coregistration.coregistration_affichage on coregistration_affichage_id = coregistration_coregistration_affichage
        where  coregistration_eligible = 6
        and coregistration_champ = 1
        and coregistration_affilie = 134
        and coregistration_date >= :date_debut
        and coregistration_date < :date_fin
        and coregistration_date > date_inscription
        and coregistration_campagne_annonceur in ( :coregs )
        group by email
),
-- stats sponso par internaute
cte_sponso as (
        select email, sum(profil_remuneration) as ca_sponsoring
        from fdw_vipconcours.membre_tmp 
        inner join sponsoring.profil on profil_email = email
        where profil_etat = 1
        and profil_eligible = 1
        and profil_site = 26
        and profil_campagne in ( :sponsos )
        and profil_date >= :date_debut
        and profil_date < :date_fin
        and profil_date > date_inscription
        group by email
)
/*
insert into public.vipconcours(
  vipconcours_source,
  vipconcours_prenom,
  vipconcours_nom,
  vipconcours_email,
  vipconcours_date_inscription,
  vipconcours_ip,
  vipconcours_ca_coregistration,
  vipconcours_ca_sponsoring,
  vipconcours_oid4,
  vipconcours_oid5
) */
select 
	source, 
	prenom, 
	nom, 
	m.email, 
	date_inscription, 
	IP, 
	replace(coalesce(ca_coregistration, 0)::text, '.', ',') as ca_coregistration, 
	replace(coalesce(ca_sponsoring, 0)::text, '.', ',') as ca_sponsoring, 
	clic_optionnel_4, 
	clic_optionnel_5
from fdw_vipconcours.membre_tmp m 
inner join affiliation.lead on lead_identifiant = m.email  
inner join affiliation.clic on clic_id = lead_clic
left join cte_coreg c on m.email = c.email
left join cte_sponso s on m.email = s.email
where  lead_programme = 218
-- and clic_programme = 218
and lead_date_insertion >= :date_debut
and lead_date_insertion < :date_fin