deepstock/cloudflare-d1-report-restore-worker-patch.js

/*
Paste these changes into the deployed Cloudflare Worker.

Goal:
- Store a private report_token against each D1 customers row.
- Return { report_id, report_token } from /submit.
- Add GET /report?id=<id>&token=<token> to restore the report from D1.
- Pass report_id/report_token into buildEmailTemplate so email links can use:
  https://plan-b.now/?r=<id>&t=<token>#panel-budget
*/

// 1) In fetch(request, env, ctx), create url before the method guard and add GET /report.
// Replace the beginning of fetch with this shape:
async function fetch(request, env, ctx) {
  const CORS = {
    "Access-Control-Allow-Origin": "*",
    "Access-Control-Allow-Methods": "POST, GET, OPTIONS",
    "Access-Control-Allow-Headers": "Content-Type, Accept, Origin",
    "Access-Control-Max-Age": "86400"
  };
  if (request.method === "OPTIONS") return new Response(null, { status: 204, headers: CORS });

  const JSON_H = { ...CORS, "Content-Type": "application/json" };
  const url = new URL(request.url);

  if (request.method === "GET" && url.pathname === "/report") {
    return handleReport(request, env, JSON_H);
  }

  if (request.method !== "POST") return new Response("Method not allowed", { status: 405, headers: CORS });

  let data;
  try {
    data = await request.json();
    data._ip = request.headers.get("CF-Connecting-IP") || "unknown";
  } catch {
    return new Response(JSON.stringify({ ok: false, error: "Invalid JSON" }), { status: 400, headers: JSON_H });
  }

  if (url.pathname === "/narrative") return handleNarrative(data, env, JSON_H);
  return handleSubmit(data, env, ctx, JSON_H);
}

// 2) Add these helpers anywhere near the other helper functions.
function generateReportToken() {
  const bytes = new Uint8Array(24);
  crypto.getRandomValues(bytes);
  return Array.from(bytes, (byte) => byte.toString(16).padStart(2, "0")).join("");
}

async function ensureReportTokenColumn(env) {
  if (!env.DB) return;
  const migrations = [
    ["report_token", "ALTER TABLE customers ADD COLUMN report_token TEXT"],
    ["protein_preference", "ALTER TABLE customers ADD COLUMN protein_preference TEXT"],
    ["protein_security", "ALTER TABLE customers ADD COLUMN protein_security TEXT"]
  ];
  for (const [name, sql] of migrations) {
    try {
      await env.DB.exec(sql);
    } catch (err) {
      if (!String(err && err.message || err).includes("duplicate column")) {
        console.error(`D1 ${name} migration error:`, err);
      }
    }
  }
  try {
    await env.DB.exec("CREATE INDEX IF NOT EXISTS idx_report_token ON customers(report_token)");
  } catch (err) {
    console.error("D1 report_token index error:", err);
  }
}

async function handleReport(request, env, headers) {
  if (!env.DB) {
    return new Response(JSON.stringify({ ok: false, error: "Database not configured" }), { status: 503, headers });
  }

  const url = new URL(request.url);
  const id = parseInt(url.searchParams.get("id") || "0", 10);
  const token = String(url.searchParams.get("token") || "").trim();

  if (!id || !token || token.length < 32) {
    return new Response(JSON.stringify({ ok: false, error: "Invalid report link" }), { status: 400, headers });
  }

  await initDB(env);
  await ensureReportTokenColumn(env);

  const report = await env.DB.prepare(`
    SELECT
      id, submitted_at, first_name, last_name, email, city, country, phone,
      preferred_language, newsletter, risk_level, risk_score,
      location, household_size, water_access, food_reserves, medical_needs,
      sanitation, budget_eur, scenarios, priorities, protein_access,
      protein_detail, language_used, protein_preference, protein_security
    FROM customers
    WHERE id = ? AND report_token = ?
    LIMIT 1
  `).bind(id, token).first();

  if (!report) {
    return new Response(JSON.stringify({ ok: false, error: "Report not found" }), { status: 404, headers });
  }

  return new Response(JSON.stringify({ ok: true, report }), { headers });
}

// 3) In initDB(env), after the CREATE TABLE exec finishes, call:
// await ensureReportTokenColumn(env);

// 4) In storeCustomer(env, data), after the INSERT .run(), update the new restore/profile columns.
// Replace the return block after stmt.bind(...).run() with:
async function storeCustomerReturnBlockExample(env, data, result) {
  const rowId = result.meta && result.meta.last_row_id || null;
  if (rowId) {
    await env.DB.prepare(`
      UPDATE customers
      SET report_token = ?, protein_preference = ?, protein_security = ?
      WHERE id = ?
    `).bind(
      data.report_token || "",
      data.protein_preference || "",
      data.protein_security || "",
      rowId
    ).run();
  }
  return rowId;
}

// 5) In handleSubmit(data, env, ctx, headers), generate a token before storing/sending:
// const reportToken = generateReportToken();
// data.report_token = reportToken;
//
// Then store the customer synchronously before building the email:
// await initDB(env);
// await ensureReportTokenColumn(env);
// const reportId = await storeCustomer(env, data);
// data.report_id = reportId;
// data.report_token = reportToken;
//
// Remove or adjust the old ctx.waitUntil DB store block so it does not insert a duplicate row.
//
// Finally, return the report link fields:
// return new Response(JSON.stringify({
//   ok: true,
//   report_id: reportId,
//   report_token: reportToken
// }), { headers });

// 6) In buildEmailTemplate(bodyText, data, isDE), use the updated
// cloudflare-buildEmailTemplate-replacement.js file. It already creates durable links
// when data.report_id and data.report_token are present.