Apps y automatización.

export function assign(request: Request, url: URL, cookies: AstroCookies): Assignment {
  const secure = url.protocol === 'https:';
  const forced = url.searchParams.get('variant');
  if (isVariant(forced)) return { variant: forced, track: false, forced: true };
  if (isBot(request)) return { variant: VARIANT_KEYS[0], track: false, forced: false };

  visitorId(cookies, secure);
  const name = cookieVariant(heroTest.id);
  const saved = cookies.get(name)?.value;
  if (isVariant(saved)) return { variant: saved, track: true, forced: false };

  const variant = VARIANT_KEYS[Math.floor(Math.random() * VARIANT_KEYS.length)];
  cookies.set(name, variant, { path: '/', maxAge: YEAR, httpOnly: true, sameSite: 'lax', secure });
  return { variant, track: true, forced: false };
}

export const POST: APIRoute = async ({ request, cookies }) => {
  if (isBot(request)) return new Response(null, { status: 204 });
  let kind: unknown;
  try {
    kind = (JSON.parse(await request.text()) as { kind?: unknown }).kind;
  } catch {
    return json({ error: 'Send a JSON body like {"kind":"exposure"}.' }, { status: 400 });
  }
  if (kind !== 'exposure' && kind !== 'conversion') return json({ error: 'kind must be exposure or conversion.' }, { status: 400 });

  const visitor = cookies.get(COOKIE_VISITOR)?.value;
  const variant = cookies.get(cookieVariant(heroTest.id))?.value;
  if (!visitor || !isVariant(variant)) return new Response(null, { status: 204 });

  const now = Date.now();
  try {
    if (kind === 'exposure') {
      await env.DB.prepare(
        'INSERT OR IGNORE INTO xp_events (experiment, visitor, kind, variant, created_at) VALUES (?1, ?2, ?3, ?4, ?5)',
      ).bind(heroTest.id, visitor, kind, variant, now).run();
    } else {
      // A conversion only counts for visitors who were exposed, attributed to the variant they saw
      await env.DB.prepare(
        `INSERT OR IGNORE INTO xp_events (experiment, visitor, kind, variant, created_at)
         SELECT ?1, ?2, 'conversion', variant, ?3 FROM xp_events
         WHERE experiment = ?1 AND visitor = ?2 AND kind = 'exposure'`,
      ).bind(heroTest.id, visitor, now).run();
    }
  } catch (e) {
    console.error('xp event failed', e);
  }
  return new Response(null, { status: 204 });
};

-- One row per visitor and event kind, so exposures and conversions are counted once.
CREATE TABLE IF NOT EXISTS xp_events (
  experiment TEXT NOT NULL,
  visitor    TEXT NOT NULL,
  kind       TEXT NOT NULL CHECK (kind IN ('exposure', 'conversion')),
  variant    TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (experiment, visitor, kind)
);
CREATE INDEX IF NOT EXISTS xp_events_by_variant ON xp_events (experiment, variant, kind);

-- Contact form submissions. The IP is stored hashed, only to rate-limit.
CREATE TABLE IF NOT EXISTS messages (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL,
  topic      TEXT NOT NULL,
  body       TEXT NOT NULL,
  ip_hash    TEXT NOT NULL,
  emailed    INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS messages_by_ip ON messages (ip_hash, created_at);