/*
-- On nous cree un ensemble de travaille
UPDATE coregistration.coregistration
SET coregistration_eligible = -10
WHERE coregistration_campagne_annonceur = 936
AND coregistration_eligible = 1;



-- duplique les ligne dans coregistration_affichage
with campagnes as (
        select unnest(array[ 82, 367, 805]) as id
)
INSERT INTO coregistration.coregistration_affichage(
            coregistration_affichage_campagne_affilie, 
            coregistration_affichage_campagne_annonceur, coregistration_affichage_date, 
            coregistration_affichage_ip, coregistration_affichage_email, 
            coregistration_affichage_affilie, coregistration_affichage_site, 
            coregistration_affichage_production, coregistration_affichage_cout, 
            coregistration_affichage_remuneration)
SELECT coregistration_affichage_campagne_affilie, id, now(), coregistration_affichage_ip, coregistration_affichage_email, coregistration_affichage_affilie, coregistration_affichage_site,
coregistration_affichage_production, coregistration_affichage_cout, coregistration_affichage_remuneration
FROM coregistration.coregistration
INNER JOIN coregistration.coregistration_affichage on coregistration_coregistration_affichage = coregistration_affichage_id and coregistration_affichage_campagne_annonceur = coregistration_campagne_annonceur
CROSS JOIN campagnes
WHERE coregistration_campagne_annonceur = 936
AND coregistration_champ = 1
AND coregistration_eligible = -10;



--duplique la table coregistration_reponse
with campagnes as (
        SELECT unnest(array[ 82, 367, 805]) as id
),
ancien_coregistration_reponse as(
        SELECT 
                c1.coregistration_coregistration_affichage,
                id as campagne_annonceur,
                c1.coregistration_valeur
        FROM coregistration.coregistration c1
        CROSS JOIN campagnes
        LEFT JOIN coregistration c2 on c2.coregistration_champ = c1.coregistration_champ AND c2.coregistration_campagne_annonceur = id and c1.coregistration_valeur = c2.coregistration_valeur
        WHERE c2.coregistration_champ is null
        AND c1.coregistration_campagne_annonceur = 936
        AND c1.coregistration_eligible = -10
        AND c1.coregistration_champ = 1

),
nouveau_coregistration_reponse as(
        SELECT 
                coregistration_affichage_id as coregistration_reponse_affichage,
                c1.coregistration_valeur,
                now() as coregistration_reponse_date, 
                id as campagne_annonceur,
               row_number() over(partition by c1.coregistration_valeur, id order by coregistration_affichage_id desc) as ordre
        FROM coregistration.coregistration_affichage
        CROSS JOIN campagnes
        INNER JOIN coregistration c1 on coregistration_affichage_email =c1.coregistration_valeur
        LEFT JOIN coregistration c2 on c2.coregistration_champ = c1.coregistration_champ AND c2.coregistration_campagne_annonceur = id and c1.coregistration_valeur = c2.coregistration_valeur
        WHERE c2.coregistration_champ is null
        AND coregistration_affichage_campagne_annonceur = id
        AND c1.coregistration_eligible = -10
        AND c1.coregistration_campagne_annonceur = 936
        AND c1.coregistration_champ = 1
)
INSERT INTO coregistration.coregistration_reponse(
            coregistration_reponse_affichage, 
            coregistration_reponse_id_question, 
            coregistration_reponse_date, 
            coregistration_reponse_id_reponse)
SELECT 
        cn.coregistration_reponse_affichage,
        coregistration_reponse_id_question, 
        cn.coregistration_reponse_date, 
        coregistration_reponse_id_reponse
  FROM coregistration.coregistration_reponse
inner join ancien_coregistration_reponse ca on coregistration_reponse_affichage = ca.coregistration_coregistration_affichage
inner join nouveau_coregistration_reponse cn
        on ca.campagne_annonceur = cn.campagne_annonceur
        and ca.coregistration_valeur = cn.coregistration_valeur
where ordre = 1;



-- dublique la table coregistration
with campagnes as (
        SELECT unnest(array[ 82, 367, 805]) as id
),
ancien_lead_coregistration as(
        SELECT 
                c1.coregistration_coregistration_affichage, 
                c1.coregistration_campagne_affilie, 
                id as coregistration_campagne_annonceur,
                c1.coregistration_champ, 
                c1.coregistration_valeur, 
                c1.coregistration_affilie
        FROM coregistration.coregistration c1
        CROSS JOIN campagnes
        LEFT JOIN coregistration c2 on c2.coregistration_champ = c1.coregistration_champ AND c2.coregistration_campagne_annonceur = id and c1.coregistration_valeur = c2.coregistration_valeur
        WHERE c2.coregistration_champ is null
        AND c1.coregistration_campagne_annonceur = 936
        AND c1.coregistration_eligible = -10
        AND c1.coregistration_champ = 1
),
nouveau_lead_coregistration as(
        SELECT 
                coregistration_affichage_id, 
                c1.coregistration_valeur,
                now() as coregistration_date, 
                id as coregistration_campagne_annonceur,
                -2 as coregistration_eligible,
               row_number() over(partition by c1.coregistration_valeur, id order by coregistration_affichage_id desc) as ordre
        FROM coregistration.coregistration_affichage
        CROSS JOIN campagnes
        INNER JOIN coregistration c1 on coregistration_affichage_email = coregistration_valeur
        LEFT JOIN coregistration c2 on c2.coregistration_champ = c1.coregistration_champ AND c2.coregistration_campagne_annonceur = id and c1.coregistration_valeur = c2.coregistration_valeur
        WHERE c2.coregistration_champ is null
        AND coregistration_affichage_campagne_annonceur = id
        AND c1.coregistration_eligible = -10
        AND c1.coregistration_campagne_annonceur = 936
        AND c1.coregistration_champ = 1
)
INSERT INTO coregistration.coregistration(
            coregistration_campagne_affilie, 
            coregistration_campagne_annonceur, 
            coregistration_coregistration_affichage, 
            coregistration_champ, 
            coregistration_valeur, 
            coregistration_date, 
            coregistration_affilie, 
            coregistration_eligible)
select 
        ca.coregistration_campagne_affilie, 
        ca.coregistration_campagne_annonceur, 
        cn.coregistration_affichage_id, 
        c.coregistration_champ, 
        c.coregistration_valeur, 
        cn.coregistration_date, 
        ca.coregistration_affilie, 
        cn.coregistration_eligible
from coregistration c
inner join ancien_lead_coregistration ca on c.coregistration_coregistration_affichage = ca.coregistration_coregistration_affichage
inner join nouveau_lead_coregistration cn
        on ca.coregistration_campagne_annonceur = cn.coregistration_campagne_annonceur 
        and ca.coregistration_valeur = cn.coregistration_valeur
where ordre = 1;

-- On les passe en Hors quotas
UPDATE coregistration.coregistration
SET coregistration_eligible = 4
WHERE coregistration_campagne_annonceur = 936
AND coregistration_eligible = -10;
*/