-- Extrage lead-urile pe zile și surse din Dolibarr
SELECT 
    data, 
    sursa_lead, 
    COUNT(*) AS numar_leads,
    SUM(COUNT(*)) OVER (PARTITION BY data) AS total_leads_pe_zi, 
    DATE_FORMAT(data, '%M.%Y') AS luna,  -- Corectat formatul lunii
    CASE 
        WHEN sursa_lead REGEXP 'EMAG MARKETPLACE' 
        THEN 'EMAG' 
        WHEN sursa_lead REGEXP 'COS ONLINE|TELEFON|CERERE MAIL EPARDOSELI|VIZITA IN SHOWROOM|WHATSAPP EPARDOSELI|WHATSAPP TDF|Din vizita pe website|Din apel telefonic fix' 
        THEN 'ONLINE' 
        ELSE 'OFFLINE' 
    END AS sursa  
FROM (
    SELECT 
        DATE(p.datec) AS data, 
        r.label AS sursa_lead
    FROM llx_propal p
    LEFT JOIN llx_c_input_reason r ON r.rowid = p.fk_input_reason
    WHERE YEAR(p.datec) = 2025 
        AND p.ref NOT LIKE '%PROV%'

    UNION ALL

    SELECT 
        DATE(c.date_commande) AS data, 
        r.label AS sursa_lead
    FROM llx_commande c
    LEFT JOIN llx_c_input_reason r ON r.rowid = c.fk_input_reason
    WHERE YEAR(c.date_commande) = 2025 
        AND c.ref NOT LIKE '%PROV%'
) AS rezultate_combinate
GROUP BY data, sursa_lead
ORDER BY data ASC;
