1const __authParityEvent = Symbol("verified-invocation-event"); 2// Flexylead authorization adapter for Functions' reserved invocation context. 3// Business identity is never inferred from event.auth, actor flags or headers. 4function requireTrustedPrincipal(event, context) { 5 const principal = event?.requestContext?.authorizer?.principal; 6 const identity = context?.invocation; 7 const tenant = String(process.env.FLEXYLEAD_AUTH_ORGANIZATION_SLUG || 'default'); 8 if (!principal || identity?.kind !== 'authenticated' 9 || principal.issuer !== `apteva:auth:${tenant}` 10 || principal.claims?.tenant_id !== tenant 11 || !principal.project_id || principal.project_id !== identity.project_id 12 || principal.subject !== identity.subject || principal.issuer !== identity.issuer 13 || !/^[1-9]\d*$/.test(String(principal.subject))) { 14 throw __authParityDenied('Authenticated Function context required.', 401); 15 } 16 return principal; 17} 18 19async function trustedActor(event, context, claimed = {}, { required = false } = {}) { 20 if (!event?.requestContext?.authorizer && context?.invocation?.kind !== 'authenticated') { 21 if (required) throw __authParityDenied('Authenticated Function context required.', 401); 22 return null; // Existing explicitly authenticated service workflows remain separate. 23 } 24 const principal = requireTrustedPrincipal(event, context); 25 const organizationId = Number(process.env.FLEXYLEAD_AUTH_ORGANIZATION_ID || 1); 26 const payload = await context.call('auth', 'auth_users_get_context', { 27 organization_id: organizationId, id: Number(principal.subject), 28 }); 29 const user = payload?.user || {}; 30 const id = String(user.id ?? ''); 31 const email = String(user.email ?? '').trim().toLowerCase(); 32 if (id !== String(principal.subject) || !email || user.status !== 'active' 33 || (user.organization_id != null && Number(user.organization_id) !== organizationId)) { ··· 528 ]; 529 const dimensionTables = await loadReferenceTables(context, dimensionNames); 530 const referencesAt = Date.now(); 531 const centreIndex = buildOperationalCentreIndex(array(dimensionTables.centres)); 532 const requestedCentreId = centreId 533 ? centreIndex.canonicalByAlias.get(clean(centreId)) || clean(centreId) 534 : null; 535 const centreIds = centreIndex.options 536 .map((centre) => centre.value) 537 .filter((value) => !requestedCentreId || value === requestedCentreId); 538 const factScope = requestedCentreId 539 ? buildFactScope(dimensionTables, centreIndex, requestedCentreId) 540 : null; 541 const coverage = expandRemunerationCoverage ? remunerationCoverage({ 542 rules: array(dimensionTables.offer_economic_rules), 543 statsPeriods: array(dimensionTables.stats_periods), 544 centreIds, 545 dateFrom, 546 dateTo, 547 }) : { dateFrom, dateTo }; 548 const requiresPerSaleScores = includeRemuneration && remunerationRequiresPerSaleScores( 549 dimensionTables, 550 centreIds, 551 coverage.dateFrom, 552 coverage.dateTo, 553 ); 554 const factTimings = {}; 555 const factEntries = await queryPilotageFacts( 556 context, 557 coverage.dateFrom, 558 coverage.dateTo, 559 { 560 legacy: legacyFacts || context?.legacyFactQueries === true, 561 aggregate: !legacyFacts && context?.legacyFactQueries !== true 562 && context?.disableFactAggregation !== true 563 && !requiresPerSaleScores 564 && (context?.testMode !== true || context?.enableFactAggregation === true), 565 dimensionTables, 566 centreIds, 567 factScope, 568 includeRemuneration: queryRemunerationFacts, 569 granularity: factGranularity, 570 timings: factTimings, 571 }, 572 ); 573 const factsAt = Date.now(); 574 const result = buildPilotageProduction({ 575 tables: { ...dimensionTables, ...Object.fromEntries(factEntries) }, 576 dateFrom, 577 dateTo, 578 centreId, 579 now: serverNow(context), 580 includeRemuneration, 581 remunerationOverrides, 582 }); 583 if (factGranularity === "commercials" || includeCurrentWorkloads) { 584 result.currentWorkloads = await timedFactQuery(factTimings, "current_workloads", () => ( 585 queryCurrentWorkloads(context, factScope) 586 )); 587 } 588 const builtAt = Date.now(); 589 return debugTimings ? { 590 ...result, ··· 640 for (const row of rows) { 641 const name = clean(row.dataset); 642 if (!Object.hasOwn(tables, name)) continue; 643 const payload = typeof row.payload === "string" ? JSON.parse(row.payload) : row.payload; 644 if (!Array.isArray(payload)) continue; 645 const rowCount = row.row_count == null ? null : nonNegative(row.row_count); 646 // SQLite's json_group_array(json(payload)) returns `[null]` for an empty 647 // aggregate on the deployed Tables runtime. The counted envelope is the 648 // authority: an empty optional reference table must decode to zero rows. 649 if (rowCount === 0) continue; 650 // Accept the pre-v2 single-row shape in test/older Tables environments, 651 // while production uses one counted aggregate per dataset. 652 const packedRows = Array.isArray(payload[0]) ? payload : [payload]; 653 const expectedRows = rowCount == null ? packedRows.length : rowCount; 654 if (expectedRows > REFERENCE_QUERY_LIMIT) throw new Error(`La table de référence ${name} dépasse ${REFERENCE_QUERY_LIMIT} lignes.`); 655 if (packedRows.length !== expectedRows) throw new Error(`${name}: agrégat de référence tronqué (${packedRows.length}/${expectedRows}).`); 656 tables[name].push(...packedRows.map((values) => Object.fromEntries( 657 TABLE_SELECTS[name].map((column, index) => [column, values[index] ?? null]), 658 ))); 659 } 660 for (const name of nativeNames) { 661 tables[name] = await queryTable(context, name, OPTIONAL_TABLES.has(name)); 662 } 663 return tables; 664} 665 666function isReferenceBatchCompatibilityError(error) { 667 return /not found|unknown table|does not exist|unresolved \{placeholder\}|no such function: json_object/i.test(clean(error?.message || error)); 668} 669 670let activeRuleCache = null; 671function cachedRule(rules, key, calculate) { 672 if (!activeRuleCache || !rules || typeof rules !== 'object') return calculate(); 673 let cache = activeRuleCache.get(rules); 674 if (!cache) { cache = new Map(); activeRuleCache.set(rules, cache); } 675 if (!cache.has(key)) cache.set(key, calculate()); 676 return cache.get(key); 677} 678export function buildPilotageProduction(input) { 679 // Computation is synchronous. Scope caches to this build, never across users 680 // or refreshes; mutable catalogue updates cannot reuse an earlier result. 681 const previous = activeRuleCache; 682 activeRuleCache = new WeakMap(); 683 try { return buildPilotageProductionUncached(input); } 684 finally { activeRuleCache = previous; } 685} 686function buildPilotageProductionUncached({ tables, dateFrom, dateTo, centreId = null, now = new Date(), includeRemuneration = true, remunerationOverrides = [] }) { 687 const warnings = new Set(); 688 const dataQuality = { 689 excludedUnattributedSales: 0, 690 unvaluedMainSales: 0, 691 unvaluedTelephonySales: 0, 692 unvaluedAdditionalSales: 0, 693 }; 694 const calls = array(tables.appels); 695 const sales = array(tables.ventes); 696 const offers = array(tables.offres); 697 const commercials = array(tables.commerciaux); 698 const centres = array(tables.centres); 699 const prospects = array(tables.prospects); 700 const campaigns = array(tables.campaigns); 701 const partners = array(tables.acquisition_partners); 702 const economicRules = array(tables.offer_economic_rules); 703 const remunerationRules = array(tables.offer_remuneration_rules); 704 const costRules = array(tables.production_cost_rules); 705 const dailyCosts = array(tables.campaign_daily_costs); 706 const objectives = array(tables.commercial_objectives); 707 const reports = array(tables.rapports); 708 const statsPeriods = array(tables.stats_periods); 709 const workAssignments = array(tables.work_routing_assignments); 710 const opportunityFacts = array(tables.opportunity_facts); 711 712 if (!economicRules.length) warnings.add("Aucune règle de chiffre d’affaires n’est configurée."); 713 if (!costRules.length) warnings.add("Aucune règle de coût de production n’est configurée."); 714 715 const offerById = identityMap(offers); ··· 2179 return rules 2180 .filter(activeRow) 2181 .filter((rule) => clean(rule.offre_id) === offerId) 2182 .filter((rule) => clean(rule.vente_type || "standard") === saleType) 2183 .filter((rule) => effectiveOn(rule, date)) 2184 .filter((rule) => clean(rule.scope_type || "global") === "global" || clean(rule.scope_id) === centreId) 2185 .sort((left, right) => scopeRank(right.scope_type) - scopeRank(left.scope_type) 2186 || clean(right.starts_at).slice(0, 10).localeCompare(clean(left.starts_at).slice(0, 10)) 2187 || Number(right.id || 0) - Number(left.id || 0))[0] || null; 2188} 2189 2190function remunerationRuleFor(rules, sale, offer, date, centreId) { 2191 const offerId = clean(sale.offre_id || offer.local_id || offer.id); 2192 const saleType = clean(sale.vente_type) || "standard"; 2193 const candidates = rules 2194 .filter(activeRow) 2195 .filter((rule) => clean(rule.offre_id) === offerId) 2196 .filter((rule) => effectiveOn(rule, date)) 2197 .filter((rule) => clean(rule.scope_type || "global") === "global" || clean(rule.scope_id) === centreId) 2198 .sort((left, right) => scopeRank(right.scope_type) - scopeRank(left.scope_type) 2199 || clean(right.starts_at).slice(0, 10).localeCompare(clean(left.starts_at).slice(0, 10)) 2200 || Number(right.id || 0) - Number(left.id || 0)); 2201 return candidates.find((rule) => clean(rule.vente_type || "standard") === saleType) 2202 || candidates.find((rule) => clean(rule.vente_type || "standard") === "standard") 2203 || null; 2204} 2205 2206function remunerationRuleAmount(rule, supplierCa, factCount = 1) { 2207 const mode = clean(rule?.payout_mode || "fixed").toLowerCase(); 2208 if (mode === "none") return 0; 2209 const value = nonNegative(rule?.prime_montant); 2210 if (mode === "percentage") return nonNegative(supplierCa) * Math.min(100, value) / 100; 2211 return value * nonNegative(factCount); 2212} 2213 2214function costRuleFor(rules, type, unit, row) { 2215 return cachedRule(rules, JSON.stringify(['cost', type, unit, row.date, row.commercialId, row.campaignId, row.centreId]), () => rules 2216 .filter(activeRow) 2217 .filter((rule) => clean(rule.cost_type).toLowerCase() === type) 2218 .filter((rule) => clean(rule.unit).toLowerCase() === unit) 2219 .filter((rule) => effectiveOn(rule, row.date)) 2220 .filter((rule) => scopeMatches(rule, row)) 2221 .sort((left, right) => scopeRank(right.scope_type) - scopeRank(left.scope_type))[0] || null); 2222} 2223 2224function scopeMatches(rule, row) { 2225 const type = clean(rule.scope_type || "global"); 2226 const id = clean(rule.scope_id); 2227 if (type === "global") return true; 2228 if (type === "commercial") return id === row.commercialId; 2229 if (type === "campaign") return id === row.campaignId; 2230 if (type === "centre") return id === row.centreId; 2231 return false; 2232} 2233 2234function scopeRank(type) { 2235 return { global: 0, centre: 1, campaign: 2, commercial: 3 }[clean(type)] || 0; 2236} 2237 2238function economicRole(sale, offer, rule) { 2239 const explicitSaleRole = clean(sale.economic_role).toLowerCase(); ··· 2297 return Math.min(factCount, snapshotCount + (ruleRevenueCents(rule, performance) == null ? 0 : factCount)); 2298} 2299 2300function hasRevenueValue(sale, rule, performance = {}) { 2301 if (sale.revenue_cents_snapshot != null && Number.isFinite(Number(sale.revenue_cents_snapshot))) return true; 2302 return ruleRevenueCents(rule, performance) != null; 2303} 2304 2305function scopedRuleRevenueCents(rule, performance = {}) { 2306 if (!rule || clean(rule.scope_type || "global") === "global") return null; 2307 return ruleRevenueCents(rule, performance); 2308} 2309 2310function ruleRevenueCents(rule, performance = {}) { 2311 if (!rule) return null; 2312 if (clean(rule.calculation_type || "fixed").toLowerCase() !== "performance_matrix") { 2313 return Number.isFinite(Number(rule.revenue_cents)) ? Number(rule.revenue_cents) : null; 2314 } 2315 const tiers = jsonArray(rule.tiers_json); 2316 const transformationRate = nonNegative(performance.transformationRate); 2317 const attachmentRate = nonNegative(performance.attachmentRate); 2318 const tier = tiers.find((candidate) => ( 2319 thresholdMatches(transformationRate, candidate.min_transformation, candidate.max_transformation) 2320 && thresholdMatches(attachmentRate, candidate.min_attachment, candidate.max_attachment) 2321 )); 2322 return tier && Number.isFinite(Number(tier.revenue_cents)) ? Number(tier.revenue_cents) : null; 2323} 2324 2325function monthlyFormulaRuleFor(rules, centreId, date) { 2326 return cachedRule(rules, JSON.stringify(['monthly', clean(centreId), date]), () => array(rules) 2327 .filter(activeRow) 2328 .filter((rule) => clean(rule.calculation_type).toLowerCase() === "monthly_partner_formula") 2329 .filter((rule) => effectiveOn(rule, date)) 2330 .filter((rule) => clean(rule.scope_type || "global").toLowerCase() === "global" || clean(rule.scope_id) === clean(centreId)) 2331 .sort((left, right) => scopeRank(right.scope_type) - scopeRank(left.scope_type))[0] || null); 2332} 2333 2334function monthlyFormulaConfig(rule) { 2335 const parsed = jsonObject(rule?.tiers_json); 2336 const transformationNumerator = clean(parsed.transformation_numerator).toLowerCase(); 2337 const opportunityBasis = clean(parsed.opportunity_basis).toLowerCase(); 2338 return { 2339 enabled: parsed.enabled !== false, 2340 label: clean(parsed.label) || "Rémunération Énergie partenaires", 2341 period_basis: clean(parsed.period_basis) === "calendar_month" ? "calendar_month" : "stats_period", 2342 base_amount: finiteNumber(parsed.base_amount, cents(rule?.revenue_cents)), 2343 eligibility_metric: formulaMetric(parsed.eligibility_metric, "transformation_rate"), 2344 eligibility_operator: formulaOperator(parsed.eligibility_operator, "gte"), 2345 eligibility_threshold: finiteNumber(parsed.eligibility_threshold, 0), 2346 ineligible_amount: finiteNumber(parsed.ineligible_amount, 0), 2347 minimum_amount: finiteNumber(parsed.minimum_amount, 0), 2348 maximum_amount: finiteNumber(parsed.maximum_amount, 100_000), 2349 include_additional_revenue_in_ca: parsed.include_additional_revenue_in_ca === true, 2350 transformation_numerator: transformationNumerator === "energy_contracts" ? "energy_contracts" : "main_sales", 2351 opportunity_basis: ["unique_inbound", "assigned_prospects", "routed_work_items", "mixed"].includes(opportunityBasis) 2352 ? opportunityBasis 2353 : "", 2354 opportunity_sources: unique(array(parsed.opportunity_sources).map((value) => clean(value).toLowerCase()).filter(Boolean)), 2355 adjustments: array(parsed.adjustments).map((adjustment, index) => normalizeFormulaAdjustment(adjustment, index)), 2356 sale_rules: array(parsed.sale_rules).map((saleRule, index) => normalizeSaleRule(saleRule, index)), 2357 }; 2358} 2359 2360export function evaluateMonthlyFormula(formula, performance = {}) { 2361 const eligible = formulaConditionMatches( 2362 finiteNumber(performance[formula.eligibility_metric], 0), 2363 formula.eligibility_operator, 2364 formula.eligibility_threshold, 2365 ); 2366 const breakdown = [{ 2367 id: "base", 2368 label: formula.label || "Base mensuelle", 2369 metric: formula.eligibility_metric, 2370 value: finiteNumber(performance[formula.eligibility_metric], 0), 2371 threshold: formula.eligibility_threshold, 2372 operator: formula.eligibility_operator, 2373 delta: eligible ? formula.base_amount : formula.ineligible_amount, 2374 applied: eligible, 2375 }]; 2376 let amount = eligible ? formula.base_amount : formula.ineligible_amount; 2377 if (eligible) { 2378 const adjustments = array(formula.adjustments).map((adjustment, index) => normalizeFormulaAdjustment(adjustment, index)); 2379 const matched = new Set(adjustments 2380 .map((adjustment, index) => ({ adjustment, index })) 2381 .filter(({ adjustment }) => adjustment.enabled !== false && adjustment.conditions.every((condition) => ( 2382 formulaConditionMatches(finiteNumber(performance[condition.metric], 0), condition.operator, condition.threshold) 2383 ))) 2384 .map(({ index }) => index)); 2385 const selectedByGroup = new Map(); 2386 for (const index of matched) { 2387 const adjustment = adjustments[index]; 2388 if (!adjustment.exclusive_group) continue; 2389 const current = selectedByGroup.get(adjustment.exclusive_group); 2390 if (current == null || adjustment.priority > adjustments[current].priority) { 2391 selectedByGroup.set(adjustment.exclusive_group, index); 2392 } 2393 } 2394 for (let index = 0; index < adjustments.length; index += 1) { 2395 const adjustment = adjustments[index]; 2396 if (adjustment.enabled === false) continue; 2397 const isMatch = matched.has(index); 2398 const applied = isMatch && (!adjustment.exclusive_group || selectedByGroup.get(adjustment.exclusive_group) === index); 2399 const conditionValues = adjustment.conditions.map((condition) => ({ 2400 ...condition, 2401 value: finiteNumber(performance[condition.metric], 0), 2402 })); 2403 breakdown.push({ 2404 ...adjustment, 2405 value: conditionValues[0]?.value ?? 0, 2406 condition_values: conditionValues, 2407 matched: isMatch, 2408 applied, 2409 skipped_reason: isMatch && !applied ? "exclusive_group" : null, 2410 }); 2411 if (!applied) continue; 2412 const effectValue = finiteNumber(adjustment.effect_value, 0); 2413 if (adjustment.effect === "set") amount = effectValue; 2414 else if (adjustment.effect === "cap") amount = Math.min(amount, effectValue); 2415 else if (adjustment.effect === "floor") amount = Math.max(amount, effectValue); 2416 else if (adjustment.effect !== "none") amount += effectValue; 2417 } 2418 } 2419 const minimum = finiteNumber(formula.minimum_amount, 0); 2420 const maximum = Math.max(minimum, finiteNumber(formula.maximum_amount, 100_000)); 2421 return { 2422 amount: Math.min(maximum, Math.max(minimum, Math.round(amount * 100) / 100)), 2423 eligible, 2424 breakdown, 2425 }; 2426} 2427 2428function normalizeFormulaAdjustment(adjustment, index) { 2429 const metric = formulaMetric(adjustment?.metric, "attachment_rate"); 2430 const operator = formulaOperator(adjustment?.operator, "gte"); 2431 const threshold = finiteNumber(adjustment?.threshold, 0); 2432 const effect = ["add", "set", "cap", "floor", "none"].includes(clean(adjustment?.effect).toLowerCase()) 2433 ? clean(adjustment.effect).toLowerCase() 2434 : "add"; 2435 const delta = finiteNumber(adjustment?.delta, 0); 2436 const conditions = array(adjustment?.conditions).length 2437 ? array(adjustment.conditions).slice(0, 8).map((condition) => ({ 2438 metric: formulaMetric(condition?.metric, metric), 2439 operator: formulaOperator(condition?.operator, "gte"), 2440 threshold: finiteNumber(condition?.threshold, 0), 2441 })) 2442 : [{ metric, operator, threshold }]; 2443 return { 2444 id: clean(adjustment?.id) || `adjustment-${index + 1}`, 2445 label: clean(adjustment?.label) || `Ajustement ${index + 1}`, 2446 metric, 2447 operator, 2448 threshold, 2449 conditions, 2450 effect, 2451 effect_value: finiteNumber(adjustment?.value, finiteNumber(adjustment?.effect_value, effect === "add" ? delta : 0)), 2452 delta, 2453 exclusive_group: clean(adjustment?.exclusive_group) || null, 2454 priority: finiteNumber(adjustment?.priority, index), 2455 enabled: adjustment?.enabled !== false, 2456 }; 2457} 2458 2459function normalizeSaleRule(rule, index) { 2460 return { 2461 id: clean(rule?.id) || `sale-rule-${index + 1}`, 2462 label: clean(rule?.label) || `Règle vente ${index + 1}`, 2463 conditions: array(rule?.conditions).slice(0, 8).map((condition) => ({ 2464 metric: clean(condition?.metric).toLowerCase() === "ai_score" ? "ai_score" : "ai_score", 2465 operator: formulaOperator(condition?.operator, "gte"), 2466 threshold: finiteNumber(condition?.threshold, 0), 2467 })), 2468 effect: ["exclude", "multiply", "set"].includes(clean(rule?.effect).toLowerCase()) ? clean(rule.effect).toLowerCase() : "exclude", 2469 value: finiteNumber(rule?.value, clean(rule?.effect).toLowerCase() === "multiply" ? 1 : 0), 2470 missing: clean(rule?.missing).toLowerCase() === "match" ? "match" : "ignore", 2471 enabled: rule?.enabled !== false, 2472 }; 2473} 2474 2475function latestReportScoreIndex(reports) { 2476 const latest = new Map(); 2477 const remember = (key, modified, score) => { 2478 if (!key) return; 2479 const current = latest.get(key); 2480 if (!current || modified >= current.modified) latest.set(key, { modified, score }); 2481 }; 2482 for (const report of array(reports)) { 2483 if (Number(report.aggregate_score) === 1) continue; 2484 const maximum = finiteNumber(report.score_max, 0); 2485 const total = finiteNumber(report.score_total, NaN); 2486 if (!maximum || !Number.isFinite(total)) continue; 2487 const score = total / maximum * 100; 2488 const modified = clean(report.modified_time); 2489 const saleIds = jsonValues(report.vente_ids); 2490 for (const id of saleIds) remember(`sale:${id}`, modified, score); 2491 if (!saleIds.length && clean(report.appel_id)) remember(`call:${clean(report.appel_id)}`, modified, score); 2492 } 2493 return latest; 2494} 2495 2496function reportScoreForSale(sale, reportScoreIndex) { 2497 for (const id of [sale?.id, sale?.local_id].map(clean).filter(Boolean)) { 2498 const entry = reportScoreIndex.get(`sale:${id}`); 2499 if (entry) return entry.score; 2500 } 2501 for (const id of [sale?.appel_id, sale?.appel_source_id].map(clean).filter(Boolean)) { 2502 const entry = reportScoreIndex.get(`call:${id}`); 2503 if (entry) return entry.score; 2504 } 2505 return null; 2506} 2507 2508function saleRuleMatches(rule, score) { 2509 if (!array(rule?.conditions).length) return false; 2510 return rule.conditions.every((condition) => { 2511 if (condition.metric !== "ai_score") return false; 2512 if (!Number.isFinite(Number(score))) return rule.missing === "match"; 2513 return formulaConditionMatches(Number(score), condition.operator, condition.threshold); 2514 }); 2515} 2516 2517function evaluateSaleRemuneration(saleRules, score, baseAmount) { 2518 let amount = finiteNumber(baseAmount, 0); 2519 let excluded = false; 2520 const breakdown = []; 2521 for (const rule of array(saleRules)) { 2522 if (rule?.enabled === false) continue; 2523 const applied = saleRuleMatches(rule, score); 2524 breakdown.push({ ...rule, score: Number.isFinite(Number(score)) ? Number(score) : null, applied }); 2525 if (!applied) continue; 2526 if (rule.effect === "exclude") { 2527 amount = 0; 2528 excluded = true; 2529 } else if (rule.effect === "multiply") { 2530 amount *= finiteNumber(rule.value, 1); 2531 } else if (rule.effect === "set") { 2532 amount = finiteNumber(rule.value, 0); 2533 } 2534 } 2535 return { amount: Math.max(0, Math.round(amount * 100) / 100), excluded, breakdown }; 2536} 2537 2538function saleRuleMetricsForPeriod({ revenueEntries, reportScoreIndex, centreId, period, saleRules }) { 2539 let energyContracts = 0; 2540 let remuneratedEnergyContracts = 0; 2541 let excludedEnergyContracts = 0; 2542 let unscoredEnergyContracts = 0; 2543 for (const entry of array(revenueEntries)) { 2544 if (!entry.energyContract || entry.row.centreId !== centreId || !dateInRange(entry.row.date, period.dateFrom, period.dateTo)) continue; 2545 const count = Number.isFinite(Number(entry.factCount)) ? nonNegative(entry.factCount) : 1; 2546 energyContracts += count; 2547 const score = reportScoreForSale(entry.sale, reportScoreIndex); 2548 if (!Number.isFinite(Number(score))) unscoredEnergyContracts += count; 2549 const evaluation = evaluateSaleRemuneration(saleRules, score, 1); 2550 if (evaluation.excluded) excludedEnergyContracts += count; 2551 else remuneratedEnergyContracts += count; 2552 } 2553 return { energyContracts, remuneratedEnergyContracts, excludedEnergyContracts, unscoredEnergyContracts }; 2554} 2555 2556function saleRemunerationForEntry(entry, monthly, reportScoreIndex) { 2557 const score = reportScoreForSale(entry.sale, reportScoreIndex); 2558 const evaluation = evaluateSaleRemuneration(monthly.saleRules, score, monthly.amount); 2559 const count = Number.isFinite(Number(entry.factCount)) ? nonNegative(entry.factCount) : 1; 2560 return { 2561 total: evaluation.amount * count, 2562 remuneratedCount: evaluation.excluded ? 0 : count, 2563 excludedCount: evaluation.excluded ? count : 0, 2564 }; 2565} 2566 2567function formulaConditionMatches(value, operator, threshold) { 2568 const target = finiteNumber(threshold, 0); 2569 if (operator === "gt") return value > target; 2570 if (operator === "lte") return value <= target; 2571 if (operator === "lt") return value < target; 2572 return value >= target; 2573} 2574 2575function formulaMetric(value, fallback) { 2576 const metric = clean(value).toLowerCase(); 2577 return ["transformation_rate", "attachment_rate", "meteore_attachment_rate", "consopilote_attachment_rate", "average_ai_score"].includes(metric) 2578 ? metric 2579 : fallback; 2580} 2581 2582function formulaOperator(value, fallback) { 2583 const operator = clean(value).toLowerCase(); 2584 return ["gte", "gt", "lte", "lt"].includes(operator) ? operator : fallback; 2585} 2586 2587function remunerationCoverage({ rules, statsPeriods, centreIds, dateFrom, dateTo }) { 2588 let from = dateFrom; 2589 let to = dateTo; 2590 for (const centreId of centreIds) { 2591 for (const date of dateSequence(dateFrom, dateTo)) { 2592 const rule = monthlyFormulaRuleFor(rules, centreId, date); 2593 if (!rule) continue; 2594 const formula = monthlyFormulaConfig(rule); 2595 if (!formula.enabled) continue; 2596 const period = remunerationPeriodForDate(date, centreId, statsPeriods, formula.period_basis); 2597 if (period.dateFrom < from) from = period.dateFrom; 2598 if (period.dateTo > to) to = period.dateTo; 2599 } 2600 } 2601 return { dateFrom: from, dateTo: to }; 2602} 2603 2604function remunerationPeriodForDate(date, centreId, statsPeriods, basis) { 2605 if (basis === "stats_period") { 2606 const selected = array(statsPeriods) 2607 .filter(activeRow) 2608 .filter((period) => !clean(period.archived_at_iso)) 2609 .filter((period) => dateInRange(date, dateOnly(period.starts_at), dateOnly(period.ends_at))) 2610 .filter((period) => clean(period.scope_type || "global").toLowerCase() === "global" || clean(period.scope_id) === clean(centreId)) 2611 .sort((left, right) => scopeRank(right.scope_type) - scopeRank(left.scope_type))[0]; 2612 if (selected) { 2613 return { 2614 id: clean(selected.local_id || selected.id), 2615 label: clean(selected.label) || "Période statistique", 2616 dateFrom: dateOnly(selected.starts_at), 2617 dateTo: dateOnly(selected.ends_at), 2618 }; 2619 } 2620 } 2621 const start = `${date.slice(0, 7)}-01`; 2622 const endDate = new Date(`${start}T12:00:00Z`); 2623 endDate.setUTCMonth(endDate.getUTCMonth() + 1); 2624 endDate.setUTCDate(0); 2625 const end = endDate.toISOString().slice(0, 10); 2626 return { 2627 id: `calendar:${date.slice(0, 7)}`, 2628 label: MONTH_LABEL_FORMATTER.format(new Date(`${start}T12:00:00Z`)), 2629 dateFrom: start, 2630 dateTo: end, 2631 }; 2632} 2633 2634function performanceForPeriod({ rows, reports, saleById, centreId, period, operationalCentreIndex, formula }) { 2635 const entries = rows.filter((row) => row.centreId === centreId && dateInRange(row.date, period.dateFrom, period.dateTo)); 2636 const received = sum(entries, "received"); 2637 const mainSales = sum(entries, "mainSales"); 2638 const energyContracts = sum(entries, "energyContracts"); 2639 const transformationNumerator = formula?.transformation_numerator === "energy_contracts" ? energyContracts : mainSales; 2640 const denominator = energyContracts || mainSales; 2641 const aggregateScores = reports.filter((report) => Number(report.aggregate_score) === 1) 2642 .filter((report) => { 2643 const rawCentre = clean(report.centre_appel_id); 2644 const reportCentre = operationalCentreIndex.canonicalByAlias.get(rawCentre) || rawCentre; 2645 return reportCentre === centreId && dateInRange(clean(report.score_date), period.dateFrom, period.dateTo); 2646 }); 2647 const aggregateScoreCount = sum(aggregateScores, "score_count"); 2648 const aggregateScoreSum = sum(aggregateScores, "score_sum"); 2649 const scores = aggregateScores.length ? [] : latestReportScoresForPeriod({ 2650 reports, 2651 saleById, 2652 centreId, 2653 period, 2654 operationalCentreIndex, 2655 }); 2656 return { 2657 transformation_rate: received ? transformationNumerator / received * 100 : 0, 2658 attachment_rate: denominator ? sum(entries, "attachedMainSales") / denominator * 100 : 0, 2659 meteore_attached_contracts: sum(entries, "meteoreAttachedMainSales"), 2660 consopilote_attached_contracts: sum(entries, "consoPiloteAttachedMainSales"), 2661 attachment_denominator: denominator, 2662 meteore_attachment_rate: denominator ? sum(entries, "meteoreAttachedMainSales") / denominator * 100 : 0, 2663 consopilote_attachment_rate: denominator ? sum(entries, "consoPiloteAttachedMainSales") / denominator * 100 : 0, 2664 average_ai_score: aggregateScoreCount ? aggregateScoreSum / aggregateScoreCount : scores.length ? scores.reduce((total, score) => total + score, 0) / scores.length : 0, 2665 received, 2666 mainSales, 2667 energyContracts, 2668 transformation_numerator: formula?.transformation_numerator || "main_sales", 2669 transformation_numerator_count: transformationNumerator, 2670 opportunity_basis: formula?.opportunity_basis || "", 2671 opportunity_sources: formula?.opportunity_sources || [], 2672 scoredSales: aggregateScoreCount || scores.length, 2673 }; 2674} 2675 2676function latestReportScoresForPeriod({ reports, saleById, centreId, period, operationalCentreIndex }) { 2677 const latest = new Map(); 2678 for (const report of reports) { 2679 const scoreMax = finiteNumber(report.score_max, 0); 2680 const scoreTotal = finiteNumber(report.score_total, NaN); 2681 if (!scoreMax || !Number.isFinite(scoreTotal)) continue; 2682 const saleIds = jsonValues(report.vente_ids); 2683 const linkedSale = saleIds.map((id) => saleById.get(clean(id))).find(Boolean); 2684 const rawCentre = clean(report.centre_appel_id) || clean(linkedSale?.centre_appel_id); 2685 const reportCentre = operationalCentreIndex.canonicalByAlias.get(rawCentre) || rawCentre; 2686 if (reportCentre !== centreId) continue; ··· 2693 } 2694 return [...latest.values()].map((entry) => entry.score); 2695} 2696 2697function thresholdMatches(value, minimum, maximum) { 2698 const min = Number(minimum ?? 0); 2699 const max = maximum == null || maximum === "" ? Infinity : Number(maximum); 2700 return value >= (Number.isFinite(min) ? min : 0) && value < (Number.isFinite(max) ? max : Infinity); 2701} 2702 2703function jsonArray(value) { 2704 if (Array.isArray(value)) return value; 2705 if (value && typeof value === "object" && Array.isArray(value.tiers)) return value.tiers; 2706 if (!clean(value)) return []; 2707 try { 2708 const parsed = JSON.parse(value); 2709 return Array.isArray(parsed) ? parsed : Array.isArray(parsed?.tiers) ? parsed.tiers : []; 2710 } catch { 2711 return []; 2712 } 2713} 2714 2715function jsonObject(value) { 2716 if (value && typeof value === "object" && !Array.isArray(value)) return value; 2717 if (!clean(value)) return {}; 2718 try { 2719 const parsed = JSON.parse(value); 2720 return parsed && typeof parsed === "object" && !Array.isArray(parsed) ? parsed : {}; 2721 } catch { 2722 return {}; 2723 } 2724} 2725 2726function jsonValues(value) { 2727 if (Array.isArray(value)) return value.map(clean).filter(Boolean); 2728 if (!clean(value)) return []; 2729 try { 2730 const parsed = JSON.parse(value); 2731 return (Array.isArray(parsed) ? parsed : [parsed]).map(clean).filter(Boolean); 2732 } catch { 2733 return [clean(value)].filter(Boolean); 2734 } 2735} 2736 2737function finiteNumber(value, fallback = 0) { 2738 const parsed = Number(value); 2739 return Number.isFinite(parsed) ? parsed : fallback; 2740} 2741 2742function performanceByCentre(rows) { 2743 const grouped = groupBy(rows, (row) => row.centreId); 2744 return new Map([...grouped].map(([centreId, entries]) => { 2745 const received = sum(entries, "received"); 2746 const mainSales = sum(entries, "mainSales"); 2747 return [centreId, { 2748 transformationRate: received ? mainSales / received * 100 : 0, 2749 attachmentRate: mainSales ? sum(entries, "attachedMainSales") / mainSales * 100 : 0, 2750 }]; 2751 })); 2752} 2753 2754function saleGroupKey(sale, interaction) { 2755 return clean( 2756 sale.sale_group_id 2757 || sale.parent_vente_id 2758 || sale.reservation_id 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 } ··· 3970 NULLIF(CAST(v.prospect_id AS TEXT), ''), 'sale:' || CAST(v.id AS TEXT) 3971 )`; 3972 const keepCampaign = granularity !== "remuneration"; 3973 const keepCommercial = granularity !== "remuneration"; 3974 const selectedCampaign = keepCampaign ? "CAST(v.campaign_id AS TEXT)" : "''"; 3975 const selectedCommercial = keepCommercial ? "CAST(v.commercial_id AS TEXT)" : "''"; 3976 const selectedCentre = `CAST(v.centre_appel_id AS TEXT)`; 3977 const result = await callTables(context, "tables_query", { 3978 sql: `WITH eligible AS ( 3979 SELECT 3980 ${saleDate} AS sale_date, 3981 ${selectedCampaign} AS campaign_id, 3982 ${selectedCommercial} AS commercial_id, 3983 ${selectedCentre} AS centre_appel_id, 3984 CAST(v.offre_id AS TEXT) AS offre_id, 3985 CAST(v.vente_type AS TEXT) AS vente_type, 3986 ${role} AS economic_role, 3987 ${bundleKey} AS bundle_key, 3988 '' AS supplier, 3989 CAST(v.revenue_cents_snapshot AS REAL) AS revenue_cents_snapshot, 3990 CAST(v.prime_montant AS REAL) AS prime_montant, 3991 NULL AS energy_contract 3992 FROM {ventes} v 3993 WHERE (${predicate}) 3994 AND lower(COALESCE(CAST(v.etat AS TEXT), '')) IN ('signee', 'signée', 'signed', 'validee', 'validée', 'validated', 'active', 'actif') 3995 AND lower(COALESCE(CAST(v.vente_role AS TEXT), '')) NOT LIKE 'non_countable%' 3996 ), sale_groups AS ( 3997 SELECT sale_date, campaign_id, commercial_id, centre_appel_id, offre_id, vente_type, 3998 economic_role, supplier, energy_contract, 3999 COUNT(*) AS fact_count, 4000 SUM(prime_montant) AS bonus_total, 4001 SUM(CASE WHEN prime_montant IS NOT NULL THEN 1 ELSE 0 END) AS prime_count, 4002 SUM(CASE WHEN revenue_cents_snapshot IS NOT NULL THEN revenue_cents_snapshot ELSE 0 END) AS snapshot_total_cents, 4003 SUM(CASE WHEN revenue_cents_snapshot IS NOT NULL THEN 1 ELSE 0 END) AS snapshot_count 4004 FROM eligible 4005 GROUP BY sale_date, campaign_id, commercial_id, centre_appel_id, offre_id, vente_type, economic_role, supplier, energy_contract 4006 ), bundles AS ( 4007 SELECT sale_date, campaign_id, commercial_id, centre_appel_id, bundle_key, 4008 MAX(CASE WHEN economic_role = 'main' THEN 1 ELSE 0 END) AS has_main, 4009 MAX(CASE WHEN economic_role = 'meteore' THEN 1 ELSE 0 END) AS has_meteore, 4010 MAX(CASE WHEN economic_role = 'consopilote' THEN 1 ELSE 0 END) AS has_consopilote 4011 FROM eligible 4012 GROUP BY sale_date, campaign_id, commercial_id, centre_appel_id, bundle_key 4013 ), attachment_groups AS ( 4014 SELECT sale_date, campaign_id, commercial_id, centre_appel_id, 4015 SUM(CASE WHEN has_main = 1 AND (has_meteore = 1 OR has_consopilote = 1) THEN 1 ELSE 0 END) AS attached_main_count, 4016 SUM(CASE WHEN has_main = 1 AND has_meteore = 1 THEN 1 ELSE 0 END) AS meteore_attached_count, 4017 SUM(CASE WHEN has_main = 1 AND has_consopilote = 1 THEN 1 ELSE 0 END) AS consopilote_attached_count 4018 FROM bundles 4019 GROUP BY sale_date, campaign_id, commercial_id, centre_appel_id 4020 ) 4021 SELECT 'sales' AS dataset, COUNT(*) AS row_count, 4022 json_group_array(json_object( 4023 'aggregate_fact', 1, 'date_vente', sale_date, 'campaign_id', campaign_id, 4024 'commercial_id', commercial_id, 'centre_appel_id', centre_appel_id, 4025 'offre_id', offre_id, 'vente_type', vente_type, 'economic_role', economic_role, 4026 'supplier', supplier, 'energy_contract', energy_contract, 'fact_count', fact_count, 4027 'bonus_total', bonus_total, 'prime_count', prime_count, 'snapshot_total_cents', snapshot_total_cents, 4028 'snapshot_count', snapshot_count 4029 )) AS payload 4030 FROM sale_groups