acmcc/supabase/functions/sync-source-submissions/index.ts

import { createClient } from "https://esm.sh/@supabase/supabase-js@2.49.1";

const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers":
    "authorization, x-client-info, apikey, content-type",
};

function jsonResponse(body: unknown, status = 200) {
  return new Response(JSON.stringify(body), {
    status,
    headers: { ...corsHeaders, "Content-Type": "application/json" },
  });
}

function pick<T = unknown>(obj: Record<string, any>, keys: string[]): T | null {
  for (const k of keys) {
    if (obj[k] !== undefined && obj[k] !== null && obj[k] !== "") return obj[k] as T;
  }
  return null;
}

function buildTitle(row: Record<string, any>): string {
  const explicit = pick<string>(row, ["title", "subject", "form_name", "form_title", "type"]);
  if (explicit) return String(explicit);
  const name = pick<string>(row, ["submitter_name", "name", "full_name", "from_name"]);
  return name ? `Submission from ${name}` : "New submission";
}

function buildSummary(row: Record<string, any>): string | null {
  const summary = pick<string>(row, ["summary", "message", "description", "body", "notes"]);
  if (summary) return String(summary).slice(0, 2000);
  // Fallback: stringify the payload field if present
  const payload = row.data ?? row.payload ?? row.fields ?? row.submission_data;
  if (payload && typeof payload === "object") {
    try {
      return JSON.stringify(payload).slice(0, 2000);
    } catch {
      return null;
    }
  }
  return null;
}

Deno.serve(async (req) => {
  if (req.method === "OPTIONS") return new Response("ok", { headers: corsHeaders });

  try {
    const targetUrl = Deno.env.get("SUPABASE_URL")!;
    const targetKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
    const sourceUrl = Deno.env.get("SOURCE_SUPABASE_URL");
    const sourceKey = Deno.env.get("SOURCE_SUPABASE_SERVICE_ROLE_KEY");

    if (!sourceUrl || !sourceKey) {
      return jsonResponse({ success: false, error: "SOURCE_SUPABASE_URL / SOURCE_SUPABASE_SERVICE_ROLE_KEY not configured" }, 400);
    }

    const body = await req.json().catch(() => ({}));
    const action = String(body.action || "sync");
    const sourceTable = String(body.source_table || "form_submissions");
    const limit = Math.min(Number(body.limit || 500), 1000);

    const source = createClient(sourceUrl, sourceKey);
    const target = createClient(targetUrl, targetKey);

    // Probe: just return one row + columns so we can see the schema.
    if (action === "probe") {
      const { data, error } = await source.from(sourceTable).select("*").limit(3);
      if (error) return jsonResponse({ success: false, error: error.message }, 500);
      return jsonResponse({
        success: true,
        sample: data,
        columns: data && data.length > 0 ? Object.keys(data[0]) : [],
      });
    }

    // Determine high-water mark from existing inbox rows for this source
    const { data: lastRow } = await target
      .from("form_inbox")
      .select("created_at")
      .eq("source_type", "external_form")
      .order("created_at", { ascending: false })
      .limit(1);
    const since = body.since || lastRow?.[0]?.created_at || null;

    let query = source.from(sourceTable).select("*").order("created_at", { ascending: true }).limit(limit);
    if (since) query = query.gt("created_at", since);
    const { data: rows, error } = await query;
    if (error) return jsonResponse({ success: false, error: error.message }, 500);

    if (!rows || rows.length === 0) {
      return jsonResponse({ success: true, inserted: 0, skipped: 0, since });
    }

    // Dedupe against existing source_ids
    const sourceIds = rows.map((r: any) => r.id).filter(Boolean);
    const { data: existing } = await target
      .from("form_inbox")
      .select("source_id")
      .eq("source_type", "external_form")
      .in("source_id", sourceIds);
    const existingSet = new Set((existing || []).map((r: any) => r.source_id));

    const toInsert = rows
      .filter((r: any) => r.id && !existingSet.has(r.id))
      .map((r: any) => ({
        source_type: "external_form",
        source_id: r.id,
        title: buildTitle(r),
        submitter_name: pick<string>(r, ["submitter_name", "name", "full_name", "from_name"]),
        submitter_email: pick<string>(r, ["submitter_email", "email", "from_email", "contact_email"]),
        summary: buildSummary(r),
        status: "new",
        created_at: r.created_at || new Date().toISOString(),
      }));

    if (toInsert.length === 0) {
      return jsonResponse({ success: true, inserted: 0, skipped: rows.length, since });
    }

    const { error: insErr } = await target.from("form_inbox").insert(toInsert);
    if (insErr) return jsonResponse({ success: false, error: insErr.message }, 500);

    return jsonResponse({
      success: true,
      inserted: toInsert.length,
      skipped: rows.length - toInsert.length,
      since,
      latest: rows[rows.length - 1]?.created_at,
    });
  } catch (e) {
    return jsonResponse({ success: false, error: e instanceof Error ? e.message : String(e) }, 500);
  }
});