select DISTINCT u.ID as customer_id,cl.email as email,cl.first_name,sms.meta_value as billing_phone,meta_notif.meta_value as notif ,meta_rappel.meta_value as rappel ,max(o.date_created),
    GROUP_CONCAT(distinct cp_date_cart.meta_value) as cp_date_cart,
    MAX(cp_distri_cart.meta_value) as cp_distri_cart
    from wp_users u

join wp_usermeta sms on u.ID =sms.user_id and sms.meta_key="billing_phone"
join wp_12_wc_customer_lookup cl on cl.user_id=u.ID
join wp_12_wc_order_stats o on o.customer_id=cl.customer_id and o.date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)

JOIN
    wp_usermeta meta_notif ON u.ID = meta_notif.user_id
    AND meta_notif.meta_key = 'notifsms'
     JOIN
    wp_usermeta meta_rappel ON u.ID = meta_rappel.user_id
    AND meta_rappel.meta_key = 'rappelsms'
    JOIN
    wp_12_woocommerce_order_items oi ON o.order_id = oi.order_id
JOIN
    wp_12_woocommerce_order_itemmeta cp_date_cart ON oi.order_item_id = cp_date_cart.order_item_id
    AND cp_date_cart.meta_key = 'cp_date_cart'
JOIN
    wp_12_woocommerce_order_itemmeta cp_distri_cart ON oi.order_item_id = cp_distri_cart.order_item_id
    AND cp_distri_cart.meta_key = 'cp_distri_cart'
WHERE
    (meta_notif.meta_value = 'Oui' OR meta_rappel.meta_value = 'Oui')
group by u.ID,cl.email,sms.meta_value,meta_notif.meta_value,meta_rappel.meta_value;


-- nb customer
select DISTINCT u.user_id,max(o.date_created) from wp_usermeta u

join wp_usermeta sms on u.user_id=sms.user_id and sms.meta_key="notifsms" and sms.meta_value like "Oui"
join wp_5_wc_customer_lookup cl on cl.user_id=u.user_id
join wp_5_wc_order_stats o on o.customer_id=cl.customer_id and o.date_created>=DATE_SUB(NOW(), INTERVAL 12 MONTH)
where u.meta_key like "%wp_5_ca%"
group by u.user_id;

// REQUETE ALTERNATIVE CHANNELIN //
SELECT DISTINCT 
    u.ID as customer_id,
    cl.email as email,
    cl.first_name,
    sms.meta_value as billing_phone,
    meta_notif.meta_value as notif,
    meta_rappel.meta_value as rappel,
    o.date_created AS last_order_date,
    cp_date_cart.meta_value AS cp_date_cart,
    cp_distri_cart.meta_value AS cp_distri_cart
FROM 
    wp_users u
JOIN 
    wp_usermeta sms ON u.ID = sms.user_id AND sms.meta_key = 'billing_phone'
JOIN 
    wp_12_wc_customer_lookup cl ON cl.user_id = u.ID
JOIN 
    wp_usermeta meta_notif ON u.ID = meta_notif.user_id AND meta_notif.meta_key = 'notifsms'
JOIN 
    wp_usermeta meta_rappel ON u.ID = meta_rappel.user_id AND meta_rappel.meta_key = 'rappelsms'
JOIN 
    wp_12_wc_order_stats o ON o.customer_id = cl.customer_id AND o.date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
JOIN 
    wp_12_woocommerce_order_items oi ON o.order_id = oi.order_id
LEFT JOIN 
    wp_12_woocommerce_order_itemmeta cp_date_cart ON oi.order_item_id = cp_date_cart.order_item_id AND cp_date_cart.meta_key = 'cp_date_cart'
LEFT JOIN 
    wp_12_woocommerce_order_itemmeta cp_distri_cart ON oi.order_item_id = cp_distri_cart.order_item_id AND cp_distri_cart.meta_key = 'cp_distri_cart'
WHERE 
    (meta_notif.meta_value = 'Oui' OR meta_rappel.meta_value = 'Oui') and cp_date_cart.meta_value is not null
  AND o.order_id = (
        SELECT MAX(o2.order_id)
        FROM wp_12_wc_order_stats o2
        WHERE o2.customer_id = o.customer_id
          AND o2.date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    )  
    
    
    ou 
    
    WITH last_orders AS (
    SELECT 
        customer_id,
        MAX(order_id) AS last_order_id
    FROM 
        wp_12_wc_order_stats
    WHERE 
        date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY 
        customer_id
),
item_data AS (
    SELECT 
        lo.customer_id,
        cp_date_cart.meta_value AS cp_date_cart,
        cp_distri_cart.meta_value AS cp_distri_cart,
        o.date_created,
        o.order_id
    FROM 
        last_orders lo
    JOIN 
        wp_12_wc_order_stats o ON lo.last_order_id = o.order_id
    JOIN 
        wp_12_woocommerce_order_items oi ON o.order_id = oi.order_id
    LEFT JOIN 
        wp_12_woocommerce_order_itemmeta cp_date_cart ON oi.order_item_id = cp_date_cart.order_item_id AND cp_date_cart.meta_key = 'cp_date_cart'
    LEFT JOIN 
        wp_12_woocommerce_order_itemmeta cp_distri_cart ON oi.order_item_id = cp_distri_cart.order_item_id AND cp_distri_cart.meta_key = 'cp_distri_cart'
)
SELECT DISTINCT 
    u.ID as customer_id,
    cl.email as email,
    cl.first_name,
    sms.meta_value as billing_phone,
    meta_notif.meta_value as notif,
    meta_rappel.meta_value as rappel,
    o.date_created AS last_order_date,
    item_data.cp_date_cart,
    item_data.cp_distri_cart
FROM 
    wp_users u
JOIN 
    wp_usermeta sms ON u.ID = sms.user_id AND sms.meta_key = 'billing_phone'
JOIN 
    wp_12_wc_customer_lookup cl ON cl.user_id = u.ID
JOIN 
    wp_usermeta meta_notif ON u.ID = meta_notif.user_id AND meta_notif.meta_key = 'notifsms'
JOIN 
    wp_usermeta meta_rappel ON u.ID = meta_rappel.user_id AND meta_rappel.meta_key = 'rappelsms'
JOIN 
    wp_12_wc_order_stats o ON o.customer_id = cl.customer_id AND o.date_created >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
JOIN 
    item_data ON o.customer_id = item_data.customer_id AND o.order_id = item_data.order_id
WHERE 
    (meta_notif.meta_value = 'Oui' OR meta_rappel.meta_value = 'Oui') and item_data.cp_date_cart is not null;
    
    
