https://dc-connected.ai/assets/index-BEUrkrRO.js

1const e=`// backfill-vehicle-mileage-from-emails — Scan inbound_emails for mileage values
2// and append rows to vehicle_telemetry_events. Idempotent (unique index on
3// org+plate+source+source_ref). Internal/admin only.
4//
5// Classification: admin-only (super-admin manual trigger) + internal-cron.
6// The fleet file is overwritten by Javi every few days, so emails are the
7// authoritative historical source for mileage at the moment a case happened.
8
9// deno-lint-ignore-file no-explicit-any
10import { createClient } from "https://esm.sh/@supabase/[email protected]";
11import { validateAdminOrInternal } from "../_shared/admin-or-internal-auth.ts";
12import { normalizePlate } from "../_shared/license-plate.ts";
13import { callGeminiWithFallback } from "../_shared/gemini.ts";
14
15const SUPABASE_URL = Deno.env.get("SUPABASE_URL")!;
16const SERVICE_KEY = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
17
18const corsHeaders = {
19  "Access-Control-Allow-Origin": "*",
20  "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type, x-internal-trigger",
21};
22
23// ── Mileage extraction ──────────────────────────────────────────────────
24
25const MILEAGE_PATTERNS: { re: RegExp; source: "adac_email" | "msa_email" | "workshop_email"; conf: number }[] = [
26  // ADAC: "Kilometerstand: 15.308 km" / "Kilometerstand 15308"
27  { re: /Kilometerstand[:\\s]*([0-9][0-9\\.\\s]{2,9})\\s*(?:km)?/i, source: "adac_email", conf: 0.95 },
28  // German: "Laufleistung: 15.308 km"
29  { re: /Laufleistung[:\\s]*([0-9][0-9\\.\\s]{2,9})\\s*km/i, source: "workshop_email", conf: 0.9 },
30  // JSON-ish: "mileage": 15308   or   mileage: 15308
31  { re: /"?mileage"?\\s*[:=]\\s*"?([0-9][0-9\\.\\s]{2,9})"?/i, source: "msa_email", conf: 0.85 },
32  // Generic German: "15.308 km"  (lowest confidence, last resort)
33  { re: /\\b([0-9]{2,3}\\.[0-9]{3}|[0-9]{4,6})\\s*km\\b/i, source: "workshop_email", conf: 0.6 },
34];
35
36function normaliseKm(raw: string): number | null {
37  const cleaned = raw.replace(/[\\.\\s]/g, "");
38  const n = parseInt(cleaned, 10);
39  if (!Number.isFinite(n)) return null;
40  if (n < 100 || n >= 2_000_000) return null;
41  return n;
42}
43
44function extractMileage(body: string | null): { km: number; source: "adac_email" | "msa_email" | "workshop_email"; conf: number; excerpt: string } | null {
45  if (!body) return null;
46  for (const { re, source, conf } of MILEAGE_PATTERNS) {
47    const m = body.match(re);
48    if (m && m[1]) {
49      const km = normaliseKm(m[1]);
50      if (km !== null) {
51        const start = Math.max(0, (m.index ?? 0) - 40);
52        const excerpt = body.slice(start, start + 200).replace(/\\s+/g, " ").trim();
53        return { km, source, conf, excerpt };
54      }
55    }
56  }
57  return null;
58}
59
60async function extractMileageWithGemini(body: string): Promise<{ km: number; excerpt: string } | null> {
61  try {
62    const trimmed = body.slice(0, 4000);
63    const { content } = await callGeminiWithFallback(
64      "You extract vehicle mileage from emails. Reply with strict JSON only.",
65      \`Find the vehicle mileage (Kilometerstand) in this email. Reply with {"km": <integer>} or {"km": null}. No prose, no markdown.\\n\\nEMAIL:\\n\${trimmed}\`,
66      { temperature: 0, maxOutputTokens: 64 },
67    );
68    const m = content.match(/\\{[^}]*\\}/);
69    if (!m) return null;
70    const parsed = JSON.parse(m[0]);
71    if (typeof parsed.km !== "number") return null;
72    if (parsed.km < 100 || parsed.km >= 2_000_000) return null;
73    return { km: parsed.km, excerpt: trimmed.slice(0, 200) };
74  } catch (err) {
75    console.warn("[backfill-mileage] gemini fallback failed:", (err as Error).message);
76    return null;
77  }
78}
79
80// ── Main ────────────────────────────────────────────────────────────────
81
82Deno.serve(async (req) => {
83  if (req.method === "OPTIONS") return new Response("ok", { headers: corsHeaders });
84
85  const auth = await validateAdminOrInternal(req);
86  if (!auth.valid) {
87    return new Response(JSON.stringify({ error: auth.error || "unauthorized" }), {
88      status: 401,
89      headers: { ...corsHeaders, "content-type": "application/json" },
90    });
91  }
92
93  const supabase = createClient(SUPABASE_URL, SERVICE_KEY);
94
95  let body: any = {};
96  try { body = await req.json(); } catch { /* allow empty */ }
97
98  const orgId: string | null = body.organization_id ?? null;
99  const limit: number = Math.min(Number(body.limit ?? 500), 5000);
100  const useGemini: boolean = body.use_gemini !== false; // default on
101  const sinceIso: string | null = body.since ?? null;
102  const beforeIso: string | null = body.before ?? null;
103

104  // Pull candidate emails. We require matched_case_id so we can resolve a license plate.
105  let q = supabase
106    .from("inbound_emails")
107    .select("id, organization_id, body_text, body_html, received_at, matched_case_id, subject")
108    .not("matched_case_id", "is", null)
109    .order("received_at", { ascending: false })
110    .limit(limit);
111  if (orgId) q = q.eq("organization_id", orgId);
112  if (sinceIso) q = q.gte("received_at", sinceIso);
113  if (beforeIso) q = q.lt("received_at", beforeIso);
114
115
116  const { data: emails, error: emailErr } = await q;
117  if (emailErr) {
118    return new Response(JSON.stringify({ error: emailErr.message }), {
119      status: 500, headers: { ...corsHeaders, "content-type": "application/json" },
120    });
121  }
122
123  // Resolve plate per case (chunked to keep PostgREST URLs reasonable).
124  const caseIds = Array.from(new Set((emails ?? []).map((e) => e.matched_case_id).filter(Boolean)));
125  const caseMap = new Map<string, { plate: string | null; org: string }>();
126  const CHUNK = 150;
127  for (let i = 0; i < caseIds.length; i += CHUNK) {
128    const slice = caseIds.slice(i, i + CHUNK);
129    const { data: caseRows, error: caseErr } = await supabase
130      .from("cases")
131      .select("id, vehicle_license_plate, organization_id")
132      .in("id", slice);
133    if (caseErr) {
134      console.warn("[backfill-mileage] case lookup chunk failed:", caseErr.message);
135      continue;
136    }
137    for (const c of caseRows ?? []) {
138      caseMap.set(c.id, { plate: normalizePlate(c.vehicle_license_plate), org: c.organization_id });
139    }
140  }
141
142  let scanned = 0, regexMatches = 0, geminiMatches = 0, inserted = 0, skippedNoPlate = 0, errors = 0;
143  const samples: Array<{ email_id: string; km: number; source: string; via: string }> = [];
144
145  for (const e of emails ?? []) {
146    scanned++;
147    const meta = caseMap.get(e.matched_case_id);
148    if (!meta?.plate) { skippedNoPlate++; continue; }
149
150    const text = e.body_text || e.body_html || "";
151    let extracted = extractMileage(text);
152    let via: "regex" | "gemini" = "regex";
153    if (extracted) regexMatches++;
154
155    if (!extracted && useGemini && text && text.length > 50) {
156      const g = await extractMileageWithGemini(text);
157      if (g) {
158        // Default source for Gemini-rescued matches: classify by sender heuristic
159        const lowSubj = (e.subject || "").toLowerCase();
160        const src: "adac_email" | "msa_email" | "workshop_email" =
161          lowSubj.includes("adac") ? "adac_email"
162          : lowSubj.includes("msa") || lowSubj.includes("schlepp") ? "msa_email"
163          : "workshop_email";
164        extracted = { km: g.km, source: src, conf: 0.7, excerpt: g.excerpt };
165        via = "gemini";
166        geminiMatches++;
167      }
168    }
169
170    if (!extracted) continue;
171
172    const { error: insErr } = await supabase.from("vehicle_telemetry_events").insert({
173      organization_id: e.organization_id,
174      license_plate_normalized: meta.plate,
175      mileage_km: extracted.km,
176      observed_at: e.received_at,
177      source: extracted.source,
178      source_ref: e.id,
179      case_id: e.matched_case_id,
180      raw_excerpt: extracted.excerpt.slice(0, 500),
181      confidence: extracted.conf,
182    });
183
184    if (insErr) {
185      if (String(insErr.message).includes("duplicate")) {
186        // already ingested — skip silently
187      } else {
188        errors++;
189        console.warn("[backfill-mileage] insert failed:", insErr.message);
190      }
191    } else {
192      inserted++;
193      if (samples.length < 10) samples.push({ email_id: e.id, km: extracted.km, source: extracted.source, via });
194    }
195  }
196
197  const oldestReceivedAt = (emails ?? []).length
198    ? (emails as any[])[(emails as any[]).length - 1].received_at
199    : null;
200
201  const summary = {
202    scanned,
203    regex_matches: regexMatches,
204    gemini_matches: geminiMatches,
205    inserted,
206    skipped_no_plate: skippedNoPlate,
207    errors,
208    samples,
209    organization_id: orgId,
210    limit,
211    oldest_received_at: oldestReceivedAt,
212    next_before: oldestReceivedAt,
213  };
214
215
216  // Lightweight audit log (best-effort, schema differences tolerated)
217  try {
218    await supabase.from("background_sync_logs").insert({
219      sync_type: "vehicle_mileage_backfill",
220      status: errors > 0 ? "partial" : "success",
221      synced_count: inserted,
222      error_count: errors,
223      skipped_count: skippedNoPlate,
224      errors: { samples },
225      triggered_by: auth.userId ?? "internal",
226    });
227  } catch { /* non-fatal */ }
228
229  return new Response(JSON.stringify(summary), {
230    headers: { ...corsHeaders, "content-type": "application/json" },
231  });
232});
233`;export{e as default};