125 production_cost_rules: [ 126 "id", "local_id", "scope_type", "scope_id", "cost_type", "amount_cents", "unit", 127 "cost_owner", "active", "starts_at", "ends_at", 128 ], 129 campaign_daily_costs: [ 130 "id", "date", "campaign_local_id", "campaign_id", "provider", "spend_micros", 131 "currency", "mapping_status", 132 ], 133 commercial_objectives: [ 134 "id", "scope_type", "scope_id", "period_type", "metric", "target_value", "active", 135 "starts_at", "ends_at", 136 ], 137 rapports: [ 138 "id", "appel_id", "vente_ids", "centre_appel_id", "score_total", "score_max", 139 "modified_time", 140 ], 141 stats_periods: [ 142 "id", "local_id", "label", "scope_type", "scope_id", "starts_at", "ends_at", 143 "timezone", "active", "archived_at_iso", 144 ], 145}; 146const OPPORTUNITY_FACT_SELECT = [ 147 "source", "resource_type", "opportunity_key", "date", "sort_id", "campaign_id", 148 "commercial_id", "centre_appel_id", "source_centre_id", "answered", "handled", "prospect_ids", "status", 149 "received_count", "answered_count", "handled_count", 150]; 151const PAGE_SIZE = 500; 152const FACT_QUERY_LIMIT = 25_000; 153const FACT_QUERY_PAGE_SIZE = 800; 154const REFERENCE_QUERY_LIMIT = 10_000; 155const TRANSIENT_READ_RETRIES = 2; 156const IN_FLIGHT_PILOTAGE = new Map(); 157 158export default async function handler(event = {}, context = null) { 159 context = { ...context, [__authParityEvent]: event }; 160 161 if (!context?.call) return response(500, { error: "app_context_unavailable" }); 162 if (context?.testMode === true) { 163 const body = event?.body && typeof event.body === "object" ? event.body : event; 164 const actor = await resolveGatewayActor(event, context); 165 return executePilotageQuery(context, actor, body || {}); 166 } 167 try { 168 const body = event?.body && typeof event.body === "object" ? event.body : {}; 169 const actor = await resolveGatewayActor(event, context); 170 const result = await executePilotageQuery(context, actor, body); 171 if (body.stream === true && context?.sse?.send) return; 172 return response(200, result); 173 } catch (error) { 174 const statusCode = error?.statusCode || 500; 175 return response(statusCode, { 176 error: statusCode === 403 ? "forbidden" : statusCode === 401 ? "unauthorized" : "pilotage_query_failed", 177 message: error instanceof Error ? error.message : String(error), 178 }); 179 } 180} 181 182async function executePilotageQuery(context, actor, body) { 183 assertPilotageAccess(actor); 184 const { dateFrom, dateTo } = validatedRange(body); 185 const access = await resolvePilotageAccess(context, actor, body || {}); ··· 2759 || interaction?.key 2760 || sale.appel_id 2761 || sale.prospect_id, 2762 ); 2763} 2764 2765function objectiveFor(objectives, dateFrom, dateTo, centreId) { 2766 const days = dateSequence(dateFrom, dateTo).length; 2767 const matching = objectives.filter(activeRow) 2768 .filter((row) => ["revenue", "ca", "chiffre_affaires"].includes(clean(row.metric).toLowerCase())) 2769 .filter((row) => effectiveRangesOverlap(row, dateFrom, dateTo)) 2770 .filter((row) => clean(row.scope_type || "global") === "global" || clean(row.scope_id) === clean(centreId)); 2771 return matching.reduce((total, row) => { 2772 const value = nonNegative(row.target_value); 2773 return total + (clean(row.period_type).toLowerCase() === "daily" ? value * days : value); 2774 }, 0); 2775} 2776 2777function interactionForSale(sale, interactionByAlias) { 2778 for (const id of [sale.appel_id, sale.appel_source_id]) { 2779 const interaction = interactionByAlias.get(clean(id)); 2780 if (interaction) return interaction; 2781 } 2782 return null; 2783} 2784 2785function isRootInboundCall(call) { 2786 return clean(call.direction).toLowerCase() === "entrant" && !clean(call.parent_appel_id); 2787} 2788 2789function hasAnswerEvidence(call) { 2790 if (clean(call.answered_at)) return true; 2791 if (nonNegative(call.talk_duration_seconds) > 0) return true; 2792 return ["answered", "completed"].includes(clean(call.call_outcome).toLowerCase()); 2793} 2794 2795function hasBusinessTreatment(call) { 2796 const value = clean(call.issue || call.motif || call.business_result).toLowerCase(); 2797 return Boolean(value && !["appel_manque", "missed", "no_answer"].includes(value)); 2798} 2799 2800function isCountableSale(sale) { 2801 if (clean(sale.vente_role).toLowerCase().startsWith("non_countable")) return false; 2802 const state = clean(sale.etat || sale.status).toLowerCase(); 2803 return ["signee", "signée", "signed", "validee", "validée", "validated", "active", "actif"].includes(state); 2804} 2805 2806function saleDateOnly(sale) { 2807 for (const value of [sale.date_vente, sale.validated_at, sale.created_at, sale.created_at_iso, sale.updated_at]) { 2808 const parsed = dateOnly(value); 2809 if (parsed) return parsed; 2810 } 2811 return ""; 2812} 2813 2814function dateOnly(value) { 2815 const text = clean(value); 2816 if (!text) return ""; 2817 const french = text.match(/^(\d{2})\/(\d{2})\/(\d{4})/); 2818 if (french) return `${french[3]}-${french[2]}-${french[1]}`; 2819 if (/^\d{4}-\d{2}-\d{2}$/.test(text)) return text; 2820 if (/^\d{4}-\d{2}-\d{2}T/.test(text)) { 2821 const parsed = Date.parse(text); 2822 if (Number.isFinite(parsed)) return dateInBusinessTimezone(new Date(parsed)); 2823 } ··· 3483 windowFrom, windowTo, 3484 windowFrom, windowTo, 3485 ...(factScope?.centreAliases || []), 3486 ], 3487 }; 3488 const result = sqlReference 3489 ? await callTables(context, "tables_query", input) 3490 : await queryReducedPilotageInteractions(context, input, dateRanges, factScope?.centreAliases || [], granularity); 3491 const envelope = (Array.isArray(result) ? result : result?.rows || [])[0] || {}; 3492 const payload = typeof envelope.payload === "string" ? JSON.parse(envelope.payload) : envelope.payload; 3493 const rows = Array.isArray(payload) ? payload : []; 3494 if (rows.length !== nonNegative(envelope.row_count)) { 3495 throw new Error(`appels: agrégat tronqué (${rows.length}/${nonNegative(envelope.row_count)}).`); 3496 } 3497 return rows.map((row, index) => ({ 3498 source: "inbound", 3499 resource_type: "inbound_call", 3500 opportunity_key: `aggregate:${clean(row.date)}:${clean(row.campaign_id)}:${clean(row.commercial_id)}:${clean(row.centre_appel_id)}:${index}`, 3501 date: clean(row.date), 3502 sort_id: clean(row.sort_id) || String(index + 1), 3503 campaign_id: clean(row.campaign_id), 3504 commercial_id: clean(row.commercial_id), 3505 centre_appel_id: clean(row.centre_appel_id), 3506 prospect_ids: clean(row.prospect_ids), 3507 received_count: nonNegative(row.received_count), 3508 answered_count: nonNegative(row.answered_count), 3509 handled_count: nonNegative(row.handled_count), 3510 })); 3511} 3512 3513async function queryReducedPilotageInteractions(context, input, dateRanges, centreAliases, granularity) { 3514 // Group legs in bounded reads, then deduplicate callers across the WHOLE 3515 // period in the Function. One unpartitioned compact response exceeded 3516 // Tables' 4 MiB result guard on the populated month. 3517 // The historical lookup is limited to relevant prospect IDs; the second 3518 // exact-text predicate preserves legacy ID matching despite SQLite affinity. 3519 const sql = input.sql.replace( 3520 "WHERE prospect_id IS NOT NULL AND CAST(prospect_id AS TEXT) <> ''", 3521 "WHERE prospect_id IN (SELECT resolved_prospect_id FROM interactions WHERE resolved_prospect_id <> '')" 3522 + " AND CAST(prospect_id AS TEXT) IN (SELECT resolved_prospect_id FROM interactions WHERE resolved_prospect_id <> '')" 3523 + " AND prospect_id IS NOT NULL AND CAST(prospect_id AS TEXT) <> ''", 3524 ); 3525 // Partition by the logical call group, never by date/centre: every leg stays 3526 // together. Deduplicate caller identities only AFTER collecting all partitions. 3527 // Sequential reads bound per-request database pressure and the result-size cap. 3528 const prefix = (sql.slice(0, sql.indexOf('), ranked AS (')) + ')') 3529 .replace('window_calls AS (', 'window_calls AS NOT MATERIALIZED ('); 3530 const compact = []; 3531 for (let partition = 0; partition < 8; partition++) { 3532 const partitioned = prefix.replace('FROM normalized\n GROUP BY call_group', 3533 `FROM normalized WHERE (COALESCE(unicode(substr(call_group,-1)),0) 3534 + COALESCE(unicode(substr(call_group,-2,1)),0) * 31) % 8 = ${partition}\n GROUP BY call_group`); 3535 const query = partitioned + ` SELECT COUNT(*) AS row_count, json_group_array(json_array( 3536 root_at, identity_key, campaign_id, commercial_id, centre_appel_id, 3537 prospect_ids, answered, handled, sort_id)) AS payload FROM eligible`; 3538 const result = await callTables(context, 'tables_query', { 3539 sql: query, params: centreAliases.length ? input.params.slice(0, -centreAliases.length) : input.params, 3540 }); 3541 const envelope = (Array.isArray(result) ? result : result?.rows || [])[0] || {}; 3542 const rows = typeof envelope.payload === 'string' ? JSON.parse(envelope.payload) : envelope.payload; 3543 if (!Array.isArray(rows) || rows.length !== nonNegative(envelope.row_count)) throw new Error('appels: partition tronquée'); 3544 compact.push(...rows); 3545 } 3546 const rows = reducePilotageInteractionFacts(compact, dateRanges, centreAliases, granularity); 3547 return {rows: [{row_count: rows.length, payload: rows}]}; 3548} 3549 3550// BEGIN shared/pilotage-inbound-reduction.mjs — keep synchronized 3551// Compact interaction facts are reduced inside the Function, never the browser. 3552// Preserve SQL's whole-window identity ranking and two-level DISTINCT lists. 3553export function reducePilotageInteractionFacts(rows, dateRanges, centreAliases = [], granularity = "operational") { 3554 const binary = (a,b) => a === b ? 0 : a == null ? -1 : b == null ? 1 : Buffer.compare(Buffer.from(String(a)),Buffer.from(String(b))); 3555 const integer = value => { const match=String(value??'').match(/^\s*([+-]?\d+)/);return match?BigInt(match[1]):0n; }; 3556 const compareId = (a,b) => {const x=integer(a),y=integer(b);return x<y?-1:x>y?1:binary(a,b);}; 3557 const dateFor = value => { 3558 // Lexical bounds deliberately match the SQL JOIN, including legacy text. 3559 let lo=0,hi=dateRanges.length-1; 3560 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;} 3561 return null; ··· 3752 categoryPhones.get(category).add(phone); 3753 totalAttempts += 1; 3754 } 3755 const uniqueRows = [...byPhone.values()]; 3756 const connectedUnique = uniqueRows.filter((row) => row.maxTalkSeconds >= threshold).length; 3757 const categoryBreakdown = [...categoryPhones.entries()].map(([category, phones]) => { 3758 const connected = [...phones].filter((phone) => byPhone.get(phone)?.maxTalkSeconds >= threshold).length; 3759 return { 3760 category, 3761 unique: phones.size, 3762 connected, 3763 belowThreshold: Math.max(0, phones.size - connected), 3764 answerRate: phones.size ? Math.round((connected / phones.size) * 1000) / 10 : 0, 3765 }; 3766 }); 3767 return { 3768 thresholdSeconds: threshold, 3769 totalAttempts, 3770 unique: uniqueRows.length, 3771 connected: connectedUnique, 3772 belowThreshold: Math.max(0, uniqueRows.length - connectedUnique), 3773 answerRate: uniqueRows.length ? Math.round((connectedUnique / uniqueRows.length) * 1000) / 10 : 0, 3774 categories: categoryBreakdown, 3775 }; 3776} 3777 3778// TKT-97 — cohort distinct from the ordinary period-unique opportunity count. 3779// A caller belongs to exactly one campaign: the campaign of their first-ever 3780// inbound root call. Looking for prior calls in SQL keeps the definition 3781// stable even when the selected dashboard period starts long after go-live. 3782export async function queryCampaignNewUniqueCallers(context, options = {}) { 3783 const merged = new Map(); 3784 // Each normalized caller belongs to exactly one partition, so counts add 3785 // without changing first-ever attribution or whole-period deduplication. 3786 for (let partition = 0; partition < 4; partition++) { 3787 for (const row of await queryCampaignCallerPartition(context, options, partition)) { 3788 const key = JSON.stringify([row.campaignId, row.centreId]); 3789 const existing = merged.get(key); 3790 if (existing) existing.newUniqueCallers += row.newUniqueCallers; 3791 else merged.set(key, {...row}); 3792 } 3793 } 3794 return [...merged.values()].sort((a,b) => Buffer.compare(Buffer.from(a.campaignId), Buffer.from(b.campaignId)) 3795 || Buffer.compare(Buffer.from(a.centreId), Buffer.from(b.centreId))); 3796} 3797async function queryCampaignCallerPartition(context, { 3798 dateFrom, 3799 dateTo, 3800 centreIds = [], 3801 campaignId = "", 3802 commercialId = "", 3803} = {}, partition = 0) { 3804 const from = businessDayUtcRange(dateFrom).from; 3805 const to = businessDayUtcRange(shiftDate(dateTo, 1)).from; 3806 const centres = unique(array(centreIds).map(clean).filter(Boolean)); 3807 if (!centres.length) return []; 3808 const filters = [`CAST(COALESCE(a.centre_appel_id, '') AS TEXT) IN (${centres.map(() => "?").join(", ")})`]; 3809 const params = [from, to, ...centres]; 3810 if (clean(campaignId)) { 3811 filters.push("CAST(COALESCE(c.local_id, c.id, a.campaign_id, '') AS TEXT) = ?"); 3812 params.push(clean(campaignId)); ··· 4502 ["email", clean(actor.email).toLowerCase()], 4503 ].filter(([, value]) => value); 4504 for (const [column, value] of lookups) { 4505 const result = await callTables(context, "rows_search", { 4506 table: "commerciaux", 4507 where: [{ col: column, op: "eq", value }], 4508 select: ["id", "auth_user_id", "email", "active", "centre_appel_id"], 4509 limit: 20, 4510 offset: 0, 4511 }); 4512 const row = (result?.rows || []).find((candidate) => clean(candidate[column]).toLowerCase() === value.toLowerCase()); 4513 if (row) return row; 4514 } 4515 return null; 4516} 4517 4518export async function callTables(context, tool, input) { 4519 for (let attempt = 0; ; attempt += 1) { 4520 try { 4521 const result = await context.call("tables", tool, input); 4522 // tables_query has no cursor contract, so truncation is always a fatal 4523 // correctness error. rows_search is intentionally page-based; its 4524 // callers advance offset using rows/total and therefore consume it fully. 4525 if (tool === "tables_query" && result?.truncated === true) { 4526 throw new Error(`${tool}: résultat tronqué par Tables; la requête doit être agrégée ou paginée.`); 4527 } 4528 return result; 4529 } catch (error) { 4530 // An execution deadline is not an admission/queue timeout. Re-running 4531 // the same expensive SELECT multiplies load without changing its plan. 4532 if (attempt >= TRANSIENT_READ_RETRIES || !/read_queue.*deadline exceeded|deadline exceeded.*read_queue/i.test(clean(error?.message || error))) { 4533 throw error; 4534 } 4535 const backoffMs = 100 * ((attempt + 1) ** 2) + Math.floor(Math.random() * 150); 4536 await new Promise((resolve) => setTimeout(resolve, backoffMs)); 4537 } 4538 } 4539} 4540 4541function identityMap(rows) { 4542 const map = new Map(); 4543 for (const row of rows) { 4544 for (const key of [row.id, row.local_id]) { 4545 if (clean(key)) map.set(clean(key), row); 4546 } 4547 } 4548 return map; 4549} 4550 4551function callAliases(call) { 4552 return unique([call.id, call.local_id, call.provider_call_id, call.raw_call_id].map(clean).filter(Boolean)); 4553} 4554 4555function groupBy(rows, keyFor) { 4556 const map = new Map(); 4557 for (const row of rows) { 4558 const key = keyFor(row); 4559 const current = map.get(key) || []; 4560 current.push(row); 4561 map.set(key, current); 4562 }