701 OR lower(COALESCE(cp.nom, '') || ' ' || COALESCE(proposed_offer.type_offre, '') || ' ' || 702 COALESCE(proposed_offer.nom, '') || ' ' || COALESCE(proposed_offer.fournisseur, '') || ' ' || COALESCE(proposed_offer.famille, '')) LIKE '%elec%' 703 OR lower(COALESCE(cp.nom, '') || ' ' || COALESCE(proposed_offer.type_offre, '') || ' ' || 704 COALESCE(proposed_offer.nom, '') || ' ' || COALESCE(proposed_offer.fournisseur, '') || ' ' || COALESCE(proposed_offer.famille, '')) LIKE '%gaz%'`); 705 const energyCampaignIds = Array.from(new Set(energyCampaignRows 706 .flatMap((row) => [clean(row.id), clean(row.local_id)]) 707 .filter(Boolean))); 708 const energyCampaignPredicate = energyCampaignIds.length 709 ? `CAST(a.campaign_id AS TEXT) IN (${energyCampaignIds.map(() => '?').join(', ')})` 710 : '0 = 1'; 711 // Start the independent call and sale aggregates together, but never exceed 712 // two concurrent reads. If Tables is already pressured, retry only the 713 // failed read sequentially before running conversion. 714 const queryCallDaily = () => queryRows(context, ` 715 WITH call_candidates AS ( 716 SELECT a.id, a.local_id, a.raw_call_id, a.provider_call_id, 717 a.started_at AS event_at, CAST(a.commercial_id AS TEXT) AS commercial_id, 718 a.direction, a.answered_at, a.status, a.talk_duration_seconds, a.duration_seconds, 719 a.parent_appel_id, a.interaction_id, a.prospect_id, 720 COALESCE(NULLIF(CAST(a.interaction_id AS TEXT), ''), 721 NULLIF(CAST(a.parent_appel_id AS TEXT), ''), 722 NULLIF(CAST(a.local_id AS TEXT), ''), CAST(a.id AS TEXT)) AS opportunity_key, 723 CASE WHEN ${energyCampaignPredicate} THEN 1 ELSE 0 END AS is_energy 724 FROM {appels} a 725 WHERE ${callFilter} AND a.started_at >= ? AND a.started_at < ? 726 ) 727 SELECT ${parisDateSql('event_at', range)} AS date, 728 CAST(commercial_id AS TEXT) AS commercial_id, 729 COUNT(*) AS calls, 730 SUM(CASE WHEN lower(CAST(direction AS TEXT)) IN ('entrant','inbound') THEN 1 ELSE 0 END) AS inbound, 731 SUM(CASE WHEN answered_at IS NOT NULL AND CAST(answered_at AS TEXT) <> '' THEN 1 ELSE 0 END) AS answered, 732 SUM(CASE WHEN lower(CAST(status AS TEXT)) IN ('manque','missed') THEN 1 ELSE 0 END) AS missed, 733 SUM(CASE 734 WHEN COALESCE(CAST(talk_duration_seconds AS REAL), 0) > 0 THEN CAST(talk_duration_seconds AS REAL) 735 WHEN answered_at IS NOT NULL AND CAST(answered_at AS TEXT) <> '' THEN COALESCE(CAST(duration_seconds AS REAL), 0) 736 ELSE 0 737 END) AS talk_seconds, 738 0 AS sales, 0 AS signed, 0 AS prime_amount, 739 0 AS non_energy_signed, 0 AS additional_signed, 740 COUNT(DISTINCT CASE WHEN lower(COALESCE(direction, '')) IN ('entrant','inbound') 741 AND COALESCE(CAST(parent_appel_id AS TEXT), '') = '' 742 AND (answered_at IS NOT NULL AND CAST(answered_at AS TEXT) <> '' 743 OR COALESCE(CAST(talk_duration_seconds AS REAL), 0) > 0) 744 AND is_energy = 1 THEN opportunity_key END) AS energy_opportunities, 745 0 AS energy_converted, 746 0 AS energy_contracts, 0 AS attached_main_sales, 747 0 AS meteore_attached, 0 AS conso_attached 748 FROM call_candidates 749 GROUP BY ${parisDateSql('event_at', range)}, CAST(commercial_id AS TEXT) 750 ORDER BY date ASC, commercial_id ASC`, [...energyCampaignIds, ...callParams]); 751 const callDailyPromise = queryCallDaily(); 752 const querySaleDaily = () => queryRows(context, ` 753 WITH sale_candidates AS ( 754 SELECT normalized_sales.*, 755 lower(COALESCE(o.type_offre, '') || ' ' || COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '') || ' ' || COALESCE(normalized_sales.offre_id, '') || ' ' || COALESCE(normalized_sales.vente_type, '')) AS offer_text, 756 ${metricEnergySaleSql('normalized_sales')} AS is_energy, 757 CASE WHEN 758 lower(COALESCE(normalized_sales.economic_role, '')) IN ('meteore', 'consopilote', 'other_addon') 759 OR lower(COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '') || ' ' || COALESCE(normalized_sales.offre_id, '')) LIKE '%meteore%' 760 OR lower(COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '') || ' ' || COALESCE(normalized_sales.offre_id, '')) LIKE '%météore%' 761 OR lower(COALESCE(o.nom, '') || ' ' || COALESCE(o.fournisseur, '') || ' ' || COALESCE(o.famille, '') || ' ' || COALESCE(normalized_sales.offre_id, '')) LIKE '%consopilote%'