570 } catch (error) { 571 counters.booking_integrity_errors.push( 572 `booking ${rowReference(reservation) || reservation.id}: ${errorMessage(error)}`, 573 ); 574 } 575 } 576 } catch (error) { 577 counters.booking_integrity_errors.push(`bookings: ${errorMessage(error)}`); 578 } 579 counters.booking_integrity_errors = counters.booking_integrity_errors.slice(0, 20); 580 return counters; 581} 582 583async function findOrCreateProspectByPhone(context, phone, bridgePhones, eventAt) { 584 const normalized = normalizePhone(phone); 585 if (!normalized || isBridgePhone(normalized, bridgePhones)) return ""; 586 const existing = await findProspectByPhone(context, normalized, bridgePhones); 587 if (existing) return existing; 588 const saleLock = await recentSaleCentreLock(context, normalized, eventAt); 589 try { 590 const inserted = await callTable(context, "rows_insert", { 591 table: "prospects", 592 rows: [{ 593 telephone: normalized, 594 statut: "qualifie", 595 source: "telephony_reconciliation", 596 notes: "Prospect créé après réconciliation du transfert Ringover vers l’agent IA.", 597 ...(saleLock ? { 598 centre_lock_id: saleLock.centreId, 599 centre_lock_until: saleLock.until, 600 centre_lock_source: "sale", 601 } : {}), 602 created_at_iso: eventAt, 603 updated_at_iso: eventAt, 604 }], 605 }); 606 const id = Number(inserted?.ids?.[0] || inserted?.rows?.[0]?.id || 0); 607 if (id) return String(id); 608 } catch (error) { 609 const concurrent = await findProspectByPhone(context, normalized, bridgePhones); 610 if (concurrent) return concurrent; 611 throw error; 612 } 613 return ""; 614} 615 616async function prospectCentreLockFields(context, prospect, centreId, eventAt) { 617 const nowMs = Date.parse(clean(eventAt)); 618 const existingUntil = Date.parse(clean(prospect?.centre_lock_until)); 619 if (!centreId || !Number.isFinite(nowMs)) return {}; 620 if (clean(prospect?.centre_lock_id) && Number.isFinite(existingUntil) && existingUntil > nowMs) return {}; 621 const saleLock = await recentSaleCentreLock(context, prospect?.telephone, eventAt); 622 if (saleLock) return { 623 centre_lock_id: saleLock.centreId, 624 centre_lock_until: saleLock.until, 625 centre_lock_source: "sale", 626 }; 627 return { 628 centre_lock_id: clean(centreId), 629 centre_lock_until: new Date(nowMs + 90 * 24 * 60 * 60 * 1000).toISOString(), 630 centre_lock_source: "work_assignment", 631 }; 632} 633 634async function recentSaleCentreLock(context, telephone, nowValue = new Date()) { 635 const variants = salePhoneVariants(telephone); 636 if (!variants.length) return null; 637 const now = new Date(nowValue); 638 if (!Number.isFinite(now.getTime())) return null; 639 const placeholders = variants.map(() => "?").join(","); 640 const cutoff = new Date(now.getTime() - 90 * 24 * 60 * 60 * 1000); 641 const result = await callTable(context, "tables_query", { 642 sql: `SELECT v.id, v.centre_appel_id, v.date_vente 643 FROM {ventes} v 644 WHERE CASE WHEN json_valid(v.client) THEN COALESCE( 645 json_extract(v.client, '$.telephone'), json_extract(v.client, '$.phone'), 646 json_extract(v.client, '$.tel'), json_extract(v.client, '$.mobile'), '' 647 ) ELSE '' END IN (${placeholders}) 648 AND COALESCE(v.centre_appel_id, '') <> '' 649 AND COALESCE(v.vente_role, '') NOT LIKE 'non_countable%' 650 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%annul%' 651 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%cancel%' 652 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%refus%' 653 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%retract%' 654 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%test%' 655 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%duplicate%' 656 AND LOWER(COALESCE(v.etat, '')) NOT LIKE '%doublon%' 657 AND COALESCE(CASE WHEN json_valid(v.extra) THEN json_extract(v.extra, '$.is_test') END, 0) NOT IN (1, 'true') 658 AND COALESCE(CASE WHEN json_valid(v.extra) THEN json_extract(v.extra, '$.airtable.is_test') END, 0) NOT IN (1, 'true') 659 AND v.date_vente >= ? AND v.date_vente <= ? 660 ORDER BY v.date_vente ASC, v.id ASC LIMIT 1`,