3384 windowFrom, windowTo, 3385 windowFrom, windowTo, 3386 ...(factScope?.centreAliases || []), 3387 ], 3388 }; 3389 const result = sqlReference 3390 ? await callTables(context, "tables_query", input) 3391 : await queryReducedPilotageInteractions(context, input); 3392 const envelope = (Array.isArray(result) ? result : result?.rows || [])[0] || {}; 3393 const payload = typeof envelope.payload === "string" ? JSON.parse(envelope.payload) : envelope.payload; 3394 const rows = Array.isArray(payload) ? payload : []; 3395 if (rows.length !== nonNegative(envelope.row_count)) { 3396 throw new Error(`appels: agrégat tronqué (${rows.length}/${nonNegative(envelope.row_count)}).`); 3397 } 3398 return rows.map((row, index) => ({ 3399 source: "inbound", 3400 resource_type: "inbound_call", 3401 opportunity_key: `aggregate:${clean(row.date)}:${clean(row.campaign_id)}:${clean(row.commercial_id)}:${clean(row.centre_appel_id)}:${index}`, 3402 date: clean(row.date), 3403 sort_id: clean(row.sort_id) || String(index + 1), 3404 campaign_id: clean(row.campaign_id), 3405 commercial_id: clean(row.commercial_id), 3406 centre_appel_id: clean(row.centre_appel_id), 3407 prospect_ids: clean(row.prospect_ids), 3408 received_count: nonNegative(row.received_count), 3409 answered_count: nonNegative(row.answered_count), 3410 handled_count: nonNegative(row.handled_count), 3411 })); 3412} 3413 3414async function queryReducedPilotageInteractions(context, input) { 3415 // Group legs, deduplicate callers across the WHOLE period, then aggregate 3416 // dimensions in SQL. Transferring every compact interaction first exceeded 3417 // Tables' 4 MiB result guard on a 53k-call month (4.78 MB vs 1.43 MB grouped). 3418 // The historical lookup is limited to relevant prospect IDs; the second 3419 // exact-text predicate preserves legacy ID matching despite SQLite affinity. 3420 const sql = input.sql.replace( 3421 "WHERE prospect_id IS NOT NULL AND CAST(prospect_id AS TEXT) <> ''", 3422 "WHERE prospect_id IN (SELECT resolved_prospect_id FROM interactions WHERE resolved_prospect_id <> '')" 3423 + " AND CAST(prospect_id AS TEXT) IN (SELECT resolved_prospect_id FROM interactions WHERE resolved_prospect_id <> '')" 3424 + " AND prospect_id IS NOT NULL AND CAST(prospect_id AS TEXT) <> ''", 3425 ); 3426 return callTables(context, "tables_query", {...input, sql}); 3427} 3428 3429// BEGIN shared/pilotage-inbound-reduction.mjs — keep synchronized 3430// Compact interaction facts are reduced inside the Function, never the browser. 3431// Preserve SQL's whole-window identity ranking and two-level DISTINCT lists. 3432export function reducePilotageInteractionFacts(rows, dateRanges, centreAliases = [], granularity = "operational") { 3433 const binary = (a,b) => a === b ? 0 : a == null ? -1 : b == null ? 1 : Buffer.compare(Buffer.from(String(a)),Buffer.from(String(b))); 3434 const integer = value => { const match=String(value??'').match(/^\s*([+-]?\d+)/);return match?BigInt(match[1]):0n; }; 3435 const compareId = (a,b) => {const x=integer(a),y=integer(b);return x<y?-1:x>y?1:binary(a,b);}; 3436 const dateFor = value => { 3437 // Lexical bounds deliberately match the SQL JOIN, including legacy text. 3438 let lo=0,hi=dateRanges.length-1; 3439 while(lo<=hi){const mid=(lo+hi)>>1,r=dateRanges[mid];if(value<r.from)hi=mid-1;else if(value>=r.to)lo=mid+1;else return r.date;} 3440 return null; 3441 }; 3442 const selected=[]; 3443 for(const row of rows){const date=dateFor(row[0]);if(date)selected.push({date,row});} 3444 selected.sort((a,b)=>binary(a.row[1],b.row[1])||binary(a.date,b.date)||compareId(a.row[8],b.row[8]));