backend/flexylead-app-acquisition-query.mjs

32  if (id !== String(principal.subject) || !email || user.status !== 'active'
33    || (user.organization_id != null && Number(user.organization_id) !== organizationId)) {
34    throw __authParityDenied('User is inactive or outside the authenticated organization.');
35  }
36  if ((claimed?.auth_user_id && String(claimed.auth_user_id).trim() !== id)
37    || (claimed?.email && String(claimed.email).trim().toLowerCase() !== email)) {
38    throw __authParityDenied('Command actor does not match the authenticated user.');
39  }
40  const names = value => (Array.isArray(value) ? value : []).map(String);
41  const roles = names(payload?.authorization?.roles);
42  return { id, auth_user_id: id, email, organization_id: String(organizationId),
43    roles, role: roles[0] || '', permissions: names(payload?.authorization?.permissions) };
44}
45
46function __authParityDenied(message, statusCode = 403) {
47  const error = new Error(message); error.statusCode = statusCode; return error;
48}
49
50const AUTH_ORGANIZATION_ID = Number(process.env.FLEXYLEAD_AUTH_ORGANIZATION_ID || 1);
51const ALL_CENTRES_PERMISSION = 'commercial:all_centres';
52const READ_PERMISSIONS = new Set([
53  'commercial:administration:manage',
54  'commercial:pilotage:read',
55  'commercial:team:supervise',
56]);
57const BUSINESS_TIMEZONE = 'Europe/Paris';
58
59export function confirmedBusinessAnswerSql(alias = '') {
60  const column = (name) => alias ? `${alias}.${name}` : name;
61  return `(
62    lower(COALESCE(CAST(${column('handled_by')} AS TEXT), '')) != 'ivr'
63    AND
64    (COALESCE(CAST(${column('answered_at')} AS TEXT), '') != ''
65      OR COALESCE(CAST(${column('talk_duration_seconds')} AS REAL), 0) > 0)
66    AND (
67      COALESCE(CAST(${column('commercial_id')} AS TEXT), '') != ''
68      OR lower(COALESCE(CAST(${column('handled_by')} AS TEXT), '')) IN ('ai', 'external')
69    )
70  )`;
71}
72
73export function acquisitionNewCallerPredicate() {
74  const branch = (fallback) => `NOT EXISTS (
75    SELECT 1 FROM {appels} prior
76    WHERE prior.from_number IN (candidate.caller_number, candidate.caller_digits,
77      '+' || candidate.caller_digits, '0' || candidate.caller_tail)
78      AND lower(COALESCE(prior.direction, '')) IN ('entrant', 'inbound')
79      AND COALESCE(CAST(prior.parent_appel_id AS TEXT), '') = ''
80      AND ${fallback ? "(prior.started_at IS NULL OR prior.started_at = '') AND prior.created_at" : "prior.started_at != '' AND prior.started_at"} < candidate.event_at
81  )`;
82  // Separate the timestamp fallback so the caller/timestamp indexes can bound
83  // each historical lookup. Do not change caller identity or centre scope.
84  return `(${branch(false)} AND ${branch(true)})`;
85}
86
87export default async function handler(event = {}, context = null) {
88  context = { ...context, [__authParityEvent]: event };
89
90  if (!context?.call) return response(500, { error: 'app_context_unavailable' });
91  try {
92    const body = event?.body && typeof event.body === 'object' ? event.body : event;