import {
  clearSessionCookie,
  getCookie,
  randomUrlToken,
  sessionCookie,
  sha256Base64Url,
  signSession,
  verifySession,
} from './crypto';
import {
  GoogleCalendarError,
  completeGoogleOAuth,
  confirmCalendarHold,
  createCalendarEvent,
  createCalendarHold,
  createGoogleAuthUrl,
  deleteCalendarEvent,
  disconnectGoogle,
  hasGoogleConnection,
  queryBusy,
  rescheduleCalendarEvent,
  sendRescheduleSuggestion,
} from './google';
import { bookingAmountMinor } from './money';
import { DEFAULT_SETTINGS, getSettings, saveSettings } from './settings';
import {
  createStripeCheckoutSession,
  createStripeRefund,
  expireStripeCheckoutSession,
  retrieveStripeCheckoutSession,
  stripeCheckoutSessionMode,
  stripeConfigured,
  stripeMode,
  stripePaymentIntentId,
  stripeReceiptUrl,
  StripeError,
  verifyStripeWebhook,
} from './stripe';
import type { StripeCheckoutSession, StripeRefund } from './stripe';
import {
  addDaysToDateKey,
  addMinutes,
  dateKeyInZone,
  overlaps,
  slotKeys,
  timeToMinutes,
  weekdayForDate,
  zonedDateTimeToUtc,
} from './time';
import type {
  AppSettings,
  AvailabilityBlock,
  BookingRequest,
  BusyRange,
  Env,
  MeetingMode,
  RecurringUnavailablePeriod,
} from './types';

const JSON_HEADERS = {
  'Content-Type': 'application/json; charset=utf-8',
  'Cache-Control': 'no-store',
};

function json(data: unknown, status = 200, headers: HeadersInit = {}): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { ...JSON_HEADERS, ...headers },
  });
}

function redirect(location: string, headers: HeadersInit = {}): Response {
  return new Response(null, { status: 302, headers: { Location: location, ...headers } });
}

function errorMessage(error: unknown): string {
  if (error instanceof GoogleCalendarError) return error.publicMessage;
  return error instanceof Error ? error.message : 'Unexpected error';
}

function isEmail(value: string): boolean {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value);
}

function isTime(value: string): boolean {
  return /^\d{2}:\d{2}$/.test(value) && timeToMinutes(value) >= 0 && timeToMinutes(value) <= 24 * 60;
}

function privateCalendarError(error: unknown): string {
  if (error instanceof GoogleCalendarError) return error.publicMessage;
  return 'Calendar sync failed. Check the calendar connection and try again.';
}

async function enforceBookingRateLimit(request: Request, env: Env): Promise<boolean> {
  const ip = request.headers.get('CF-Connecting-IP');
  if (!ip) return true; // Local development.

  const hourBucket = Math.floor(Date.now() / 3_600_000);
  const material = new TextEncoder().encode(`${ip}:${hourBucket}:${env.SESSION_SECRET}`);
  const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
  const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
  const resetAt = (hourBucket + 1) * 3_600_000;

  await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
  await env.DB.prepare(`
    INSERT INTO rate_limits (key, count, reset_at)
    VALUES (?1, 1, ?2)
    ON CONFLICT(key) DO UPDATE SET count = count + 1
  `).bind(key, resetAt).run();
  const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
    .bind(key)
    .first<{ count: number }>();
  return (row?.count ?? 1) <= 12;
}

async function enforceCancellationRateLimit(request: Request, env: Env): Promise<boolean> {
  const ip = request.headers.get('CF-Connecting-IP');
  if (!ip) return true;

  const hourBucket = Math.floor(Date.now() / 3_600_000);
  const material = new TextEncoder().encode(`cancel:${ip}:${hourBucket}:${env.SESSION_SECRET}`);
  const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
  const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
  const resetAt = (hourBucket + 1) * 3_600_000;

  await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
  await env.DB.prepare(`
    INSERT INTO rate_limits (key, count, reset_at)
    VALUES (?1, 1, ?2)
    ON CONFLICT(key) DO UPDATE SET count = count + 1
  `).bind(key, resetAt).run();
  const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
    .bind(key)
    .first<{ count: number }>();
  return (row?.count ?? 1) <= 10;
}


async function enforceManageRateLimit(request: Request, env: Env): Promise<boolean> {
  const ip = request.headers.get('CF-Connecting-IP');
  if (!ip) return true;

  const hourBucket = Math.floor(Date.now() / 3_600_000);
  const material = new TextEncoder().encode(`manage:${ip}:${hourBucket}:${env.SESSION_SECRET}`);
  const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
  const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
  const resetAt = (hourBucket + 1) * 3_600_000;

  await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
  await env.DB.prepare(`
    INSERT INTO rate_limits (key, count, reset_at)
    VALUES (?1, 1, ?2)
    ON CONFLICT(key) DO UPDATE SET count = count + 1
  `).bind(key, resetAt).run();
  const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
    .bind(key)
    .first<{ count: number }>();
  return (row?.count ?? 1) <= 30;
}

function newPublicBookingId(): string {
  const alphabet = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
  const bytes = crypto.getRandomValues(new Uint8Array(20));
  const chars = Array.from(bytes, (byte) => alphabet[byte % alphabet.length]).join('');
  return `SLT-${chars.slice(0, 4)}-${chars.slice(4, 8)}-${chars.slice(8, 12)}-${chars.slice(12, 16)}-${chars.slice(16, 20)}`;
}

function normalizePublicBookingId(value: unknown): string | null {
  const compact = String(value ?? '').trim().toUpperCase().replace(/[^A-Z0-9]/g, '');
  if (!compact.startsWith('SLT') || compact.length !== 23) return null;
  const chars = compact.slice(3);
  if (!/^[A-Z0-9]{20}$/.test(chars)) return null;
  return `SLT-${chars.slice(0, 4)}-${chars.slice(4, 8)}-${chars.slice(8, 12)}-${chars.slice(12, 16)}-${chars.slice(16, 20)}`;
}

async function requireAdmin(request: Request, env: Env): Promise<string | null> {
  const email = await verifySession(env, getCookie(request, 'meet_session'));
  if (!email || email.toLowerCase() !== env.ADMIN_EMAIL.toLowerCase()) return null;
  return email;
}

function validateAppSettings(input: unknown): AppSettings {
  if (!input || typeof input !== 'object') throw new Error('Invalid settings payload');
  const raw = input as Partial<AppSettings>;
  const allowedModes: MeetingMode[] = ['google_meet', 'phone', 'in_person', 'decide_later'];

  const durations = Array.isArray(raw.durations)
    ? [...new Set(raw.durations.map(Number).filter((value) => value >= 10 && value <= 240))].sort((a, b) => a - b)
    : DEFAULT_SETTINGS.durations;
  if (!durations.length) throw new Error('At least one duration is required');

  const availability: AppSettings['availability'] = {};
  for (let day = 0; day <= 6; day += 1) {
    const intervals = raw.availability?.[String(day)] ?? [];
    availability[String(day)] = intervals
      .filter((interval) => /^\d{2}:\d{2}$/.test(interval.start) && /^\d{2}:\d{2}$/.test(interval.end))
      .filter((interval) => timeToMinutes(interval.end) > timeToMinutes(interval.start))
      .sort((a, b) => timeToMinutes(a.start) - timeToMinutes(b.start))
      .slice(0, 3);
  }

  const allowMeetingModes = Array.isArray(raw.allowMeetingModes)
    ? raw.allowMeetingModes.filter((mode): mode is MeetingMode => allowedModes.includes(mode as MeetingMode))
    : DEFAULT_SETTINGS.allowMeetingModes;
  const normalizedModes = allowMeetingModes.length ? allowMeetingModes : ['google_meet'] as MeetingMode[];
  const defaultMeetingMode = normalizedModes.includes(raw.defaultMeetingMode as MeetingMode)
    ? (raw.defaultMeetingMode as MeetingMode)
    : normalizedModes[0];
  const paymentEnabled = Boolean(raw.paymentEnabled);
  const paymentRatePerMinute = Math.min(
    1_000_000,
    Math.max(0, Math.round(Number(raw.paymentRatePerMinute ?? DEFAULT_SETTINGS.paymentRatePerMinute) * 10000) / 10000),
  );
  const paymentFreeDurations = Array.isArray(raw.paymentFreeDurations)
    ? [...new Set(raw.paymentFreeDurations.map(Number).filter((value) => durations.includes(value)))].sort((a, b) => a - b)
    : DEFAULT_SETTINGS.paymentFreeDurations;
  const hasPaidDuration = durations.some((duration) => !paymentFreeDurations.includes(duration));
  if (paymentEnabled && hasPaidDuration && paymentRatePerMinute <= 0) {
    throw new Error('Set a price per minute greater than zero for paid meeting lengths.');
  }

  return {
    title: String(raw.title ?? DEFAULT_SETTINGS.title).trim().slice(0, 100) || DEFAULT_SETTINGS.title,
    subtitle: String(raw.subtitle ?? DEFAULT_SETTINGS.subtitle).trim().slice(0, 240),
    timezone: String(raw.timezone ?? DEFAULT_SETTINGS.timezone).trim() || DEFAULT_SETTINGS.timezone,
    bookingWindowDays: Math.min(365, Math.max(1, Number(raw.bookingWindowDays ?? DEFAULT_SETTINGS.bookingWindowDays))),
    minimumNoticeMinutes: Math.min(10080, Math.max(0, Number(raw.minimumNoticeMinutes ?? 120))),
    bufferBeforeMinutes: Math.min(180, Math.max(0, Number(raw.bufferBeforeMinutes ?? 0))),
    bufferAfterMinutes: Math.min(180, Math.max(0, Number(raw.bufferAfterMinutes ?? 15))),
    slotIncrementMinutes: [5, 10, 15, 20, 30, 60].includes(Number(raw.slotIncrementMinutes))
      ? Number(raw.slotIncrementMinutes)
      : 15,
    durations,
    calendarId: String(raw.calendarId ?? 'primary').trim() || 'primary',
    busyCalendarIds: Array.isArray(raw.busyCalendarIds)
      ? raw.busyCalendarIds.map(String).map((value) => value.trim()).filter(Boolean).slice(0, 20)
      : ['primary'],
    defaultMeetingMode,
    allowMeetingModes: normalizedModes,
    inPersonLocation: String(raw.inPersonLocation ?? '').trim().slice(0, 240),
    paymentEnabled,
    paymentRatePerMinute,
    paymentFreeDurations,
    paymentCurrency: /^[a-zA-Z]{3}$/.test(String(raw.paymentCurrency ?? ''))
      ? String(raw.paymentCurrency).toLowerCase()
      : DEFAULT_SETTINGS.paymentCurrency,
    paymentLabel: String(raw.paymentLabel ?? DEFAULT_SETTINGS.paymentLabel).trim().slice(0, 120)
      || DEFAULT_SETTINGS.paymentLabel,
    availability,
  };
}

async function d1BusyRanges(env: Env, start: number, end: number): Promise<BusyRange[]> {
  await env.DB.prepare("DELETE FROM slot_locks WHERE expires_at < ?1").bind(Date.now()).run();
  const result = await env.DB.prepare(`
    SELECT start_time, end_time
    FROM bookings
    WHERE (
        status = 'pending'
        OR status = 'confirmed'
      )
      AND start_time < ?1
      AND end_time > ?2
  `).bind(new Date(end).toISOString(), new Date(start).toISOString()).all<{
    start_time: string;
    end_time: string;
  }>();

  return (result.results ?? []).map((row) => ({
    start: Date.parse(row.start_time),
    end: Date.parse(row.end_time),
  }));
}

async function recurringUnavailableForDay(env: Env, weekday: number): Promise<RecurringUnavailablePeriod[]> {
  const result = await env.DB.prepare(`
    SELECT id, weekdays, start_time, end_time, label
    FROM recurring_unavailable_periods
    ORDER BY start_time
  `).all<{ id: string; weekdays: string; start_time: string; end_time: string; label: string | null }>();

  return (result.results ?? [])
    .map((row) => ({
      id: row.id,
      weekdays: row.weekdays.split(',').map(Number).filter((day) => day >= 0 && day <= 6),
      start: row.start_time,
      end: row.end_time,
      label: row.label ?? undefined,
    }))
    .filter((period) => period.weekdays.includes(weekday));
}

async function oneOffBlocksForDate(env: Env, dateKey: string): Promise<AvailabilityBlock[]> {
  const result = await env.DB.prepare(`
    SELECT id, date, start_time, end_time, all_day, label
    FROM availability_blocks
    WHERE date = ?1
    ORDER BY all_day DESC, start_time
  `).bind(dateKey).all<{
    id: string;
    date: string;
    start_time: string | null;
    end_time: string | null;
    all_day: number;
    label: string | null;
  }>();

  return (result.results ?? []).map((row) => ({
    id: row.id,
    date: row.date,
    start: row.start_time ?? undefined,
    end: row.end_time ?? undefined,
    allDay: row.all_day === 1,
    label: row.label ?? undefined,
  }));
}

async function slotUnavailableRanges(env: Env, dateKey: string, timezone: string): Promise<BusyRange[]> {
  const weekday = weekdayForDate(dateKey, timezone);
  const [recurring, oneOff] = await Promise.all([
    recurringUnavailableForDay(env, weekday),
    oneOffBlocksForDate(env, dateKey),
  ]);

  const ranges: BusyRange[] = [];
  for (const period of recurring) {
    ranges.push({
      start: zonedDateTimeToUtc(dateKey, period.start, timezone).getTime(),
      end: zonedDateTimeToUtc(dateKey, period.end, timezone).getTime(),
    });
  }
  for (const block of oneOff) {
    if (block.allDay) {
      ranges.push({
        start: zonedDateTimeToUtc(dateKey, '00:00', timezone).getTime(),
        end: zonedDateTimeToUtc(addDaysToDateKey(dateKey, 1), '00:00', timezone).getTime(),
      });
    } else if (block.start && block.end) {
      ranges.push({
        start: zonedDateTimeToUtc(dateKey, block.start, timezone).getTime(),
        end: zonedDateTimeToUtc(dateKey, block.end, timezone).getTime(),
      });
    }
  }
  return ranges;
}

type DurationBlockReason = 'outside_hours' | 'unavailable';
type AvailabilitySlot = {
  start: string;
  time: string;
  durations: number[];
  blockedDurations: Record<string, DurationBlockReason>;
};

async function computeAvailability(env: Env, dateKey: string) {
  const settings = await getSettings(env);
  await cleanupStalePendingBookings(env, settings);
  const connected = await hasGoogleConnection(env);
  if (!connected) {
    return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
  }

  if (!/^\d{4}-\d{2}-\d{2}$/.test(dateKey)) throw new Error('Invalid date');
  const today = dateKeyInZone(new Date(), settings.timezone);
  const maxDate = addDaysToDateKey(today, settings.bookingWindowDays);
  if (dateKey < today || dateKey > maxDate) {
    return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
  }

  const day = weekdayForDate(dateKey, settings.timezone);
  const intervals = settings.availability[String(day)] ?? [];
  if (!intervals.length) {
    return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
  }

  const queryStart = zonedDateTimeToUtc(dateKey, intervals[0].start, settings.timezone).getTime() - 6 * 3600_000;
  const queryEnd = zonedDateTimeToUtc(dateKey, intervals[intervals.length - 1].end, settings.timezone).getTime() + 6 * 3600_000;
  const [googleBusy, storedBusy, slotBusy] = await Promise.all([
    queryBusy(env, settings, new Date(queryStart).toISOString(), new Date(queryEnd).toISOString()),
    d1BusyRanges(env, queryStart, queryEnd),
    slotUnavailableRanges(env, dateKey, settings.timezone),
  ]);

  const busy: BusyRange[] = [
    ...googleBusy.map((range) => ({ start: Date.parse(range.start), end: Date.parse(range.end) })),
    ...storedBusy,
    ...slotBusy,
  ];
  const earliestAllowed = Date.now() + settings.minimumNoticeMinutes * 60_000;
  const slots: AvailabilitySlot[] = [];

  for (const interval of intervals) {
    const intervalStartMinutes = timeToMinutes(interval.start);
    const intervalEndMinutes = timeToMinutes(interval.end);

    for (
      let minute = intervalStartMinutes;
      minute < intervalEndMinutes;
      minute += settings.slotIncrementMinutes
    ) {
      const hh = String(Math.floor(minute / 60)).padStart(2, '0');
      const mm = String(minute % 60).padStart(2, '0');
      const time = `${hh}:${mm}`;
      const start = zonedDateTimeToUtc(dateKey, time, settings.timezone).getTime();
      if (start < earliestAllowed) continue;

      const blockedDurations: Record<string, DurationBlockReason> = {};
      const availableDurations = settings.durations.filter((duration) => {
        const endMinute = minute + duration;
        if (endMinute > intervalEndMinutes) {
          blockedDurations[String(duration)] = 'outside_hours';
          return false;
        }
        const bufferedStart = addMinutes(start, -settings.bufferBeforeMinutes);
        const bufferedEnd = addMinutes(start, duration + settings.bufferAfterMinutes);
        if (overlaps(bufferedStart, bufferedEnd, busy)) {
          blockedDurations[String(duration)] = 'unavailable';
          return false;
        }
        return true;
      });

      if (availableDurations.length) {
        slots.push({
          start: new Date(start).toISOString(),
          time,
          durations: availableDurations,
          blockedDurations,
        });
      }
    }
  }

  return { settings, connected, date: dateKey, slots };
}

type PaymentBookingRow = {
  id: string;
  public_booking_id: string | null;
  name: string;
  email: string;
  phone: string | null;
  message: string | null;
  start_time: string;
  end_time: string;
  duration_minutes: number;
  timezone: string;
  meeting_mode: MeetingMode;
  status: string;
  google_event_id: string | null;
  meet_url: string | null;
  payment_status: string;
  payment_amount: number | null;
  payment_currency: string | null;
  stripe_checkout_session_id: string | null;
  stripe_payment_intent_id: string | null;
  paid_at: string | null;
  rescheduled_at: string | null;
  reschedule_count: number;
  proposed_start_time: string | null;
  proposed_end_time: string | null;
  proposed_at: string | null;
  proposal_status: string | null;
  proposal_token_hash: string | null;
  refund_status: string;
  stripe_refund_id: string | null;
  refunded_amount: number | null;
  refunded_at: string | null;
  updated_at: string | null;
};

function publicConfirmation(booking: PaymentBookingRow, receiptUrl: string | null = null) {
  return {
    id: booking.id,
    bookingId: booking.public_booking_id,
    start: booking.start_time,
    end: booking.end_time,
    duration: booking.duration_minutes,
    timezone: booking.timezone,
    meetingMode: booking.meeting_mode,
    meetUrl: booking.meet_url,
    receiptUrl,
  };
}


function publicManagePayload(booking: PaymentBookingRow) {
  const future = Date.parse(booking.end_time) > Date.now();
  const confirmed = booking.status === 'confirmed';
  return {
    bookingId: booking.public_booking_id,
    name: booking.name,
    start: booking.start_time,
    end: booking.end_time,
    duration: booking.duration_minutes,
    timezone: booking.timezone,
    meetingMode: booking.meeting_mode,
    status: booking.status,
    paymentStatus: booking.payment_status,
    refundStatus: booking.refund_status,
    canCancel: confirmed && future,
    canReschedule: confirmed && future,
  };
}

async function bookingByPublicId(env: Env, value: unknown): Promise<PaymentBookingRow | null> {
  const publicBookingId = normalizePublicBookingId(value);
  if (!publicBookingId) return null;
  const row = await env.DB.prepare('SELECT id FROM bookings WHERE public_booking_id = ?1')
    .bind(publicBookingId)
    .first<{ id: string }>();
  return row ? paymentBookingById(env, row.id) : null;
}

type ActivityMetadata = Record<string, string | number | boolean | null | undefined>;

async function logActivity(
  env: Env,
  bookingId: string | null,
  type: string,
  metadata: ActivityMetadata = {},
  idempotencyKey: string | null = null,
): Promise<void> {
  try {
    const cleanMetadata = Object.fromEntries(
      Object.entries(metadata).filter(([, value]) => value !== undefined),
    );
    await env.DB.prepare(`
      INSERT OR IGNORE INTO activity_events (id, booking_id, type, metadata, idempotency_key)
      VALUES (?1, ?2, ?3, ?4, ?5)
    `).bind(
      crypto.randomUUID(),
      bookingId,
      type,
      Object.keys(cleanMetadata).length ? JSON.stringify(cleanMetadata) : null,
      idempotencyKey,
    ).run();
  } catch {
    // Activity is observational. A missing/new migration or a feed write must
    // never be allowed to break booking, payment, refund, or calendar flows.
  }
}

async function adminActivity(env: Env): Promise<Response> {
  const settings = await getSettings(env);
  await cleanupStalePendingBookings(env, settings);

  const stats = await env.DB.prepare(`
    WITH facts AS (
      SELECT
        *,
        CASE
          WHEN status = 'confirmed' THEN 1
          WHEN status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL) THEN 1
          ELSE 0
        END AS is_booking
      FROM bookings
    )
    SELECT
      COALESCE(SUM(is_booking), 0) AS total_bookings,
      COALESCE(SUM(CASE WHEN status = 'confirmed' AND julianday(end_time) >= julianday('now') THEN 1 ELSE 0 END), 0) AS upcoming,
      COALESCE(SUM(CASE WHEN status = 'confirmed' AND julianday(end_time) < julianday('now') THEN 1 ELSE 0 END), 0) AS completed,
      COALESCE(SUM(CASE WHEN status = 'cancelled' AND is_booking = 1 THEN 1 ELSE 0 END), 0) AS cancelled,
      COALESCE(SUM(CASE WHEN is_booking = 1 THEN reschedule_count ELSE 0 END), 0) AS reschedules,
      COALESCE(SUM(CASE WHEN paid_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS paid_bookings,
      COALESCE(ROUND(AVG(CASE WHEN status = 'confirmed' THEN duration_minutes END), 1), 0) AS average_minutes,
      COALESCE(SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END), 0) AS open_payment_holds
    FROM facts
  `).first<{
    total_bookings: number;
    upcoming: number;
    completed: number;
    cancelled: number;
    reschedules: number;
    paid_bookings: number;
    average_minutes: number;
    open_payment_holds: number;
  }>();

  const money = await env.DB.prepare(`
    SELECT
      lower(payment_currency) AS currency,
      COALESCE(SUM(CASE WHEN paid_at IS NOT NULL THEN payment_amount ELSE 0 END), 0) AS revenue,
      COALESCE(SUM(CASE WHEN refund_status IN ('succeeded', 'pending') THEN refunded_amount ELSE 0 END), 0) AS refunds
    FROM bookings
    WHERE payment_currency IS NOT NULL
    GROUP BY lower(payment_currency)
    ORDER BY lower(payment_currency)
  `).all<{ currency: string; revenue: number; refunds: number }>();

  const feed = await env.DB.prepare(`
    SELECT
      a.id,
      a.booking_id,
      a.type,
      a.occurred_at,
      a.metadata,
      b.name,
      b.email,
      b.start_time,
      b.end_time,
      b.duration_minutes,
      b.payment_amount,
      b.payment_currency
    FROM activity_events a
    LEFT JOIN bookings b ON b.id = a.booking_id
    ORDER BY datetime(a.occurred_at) DESC, a.rowid DESC
    LIMIT 100
  `).all<{
    id: string;
    booking_id: string | null;
    type: string;
    occurred_at: string;
    metadata: string | null;
    name: string | null;
    email: string | null;
    start_time: string | null;
    end_time: string | null;
    duration_minutes: number | null;
    payment_amount: number | null;
    payment_currency: string | null;
  }>();

  return json({
    paymentsEnabled: settings.paymentEnabled,
    stats: stats ?? {
      total_bookings: 0,
      upcoming: 0,
      completed: 0,
      cancelled: 0,
      reschedules: 0,
      paid_bookings: 0,
      average_minutes: 0,
      open_payment_holds: 0,
    },
    money: settings.paymentEnabled ? (money.results ?? []).map((row) => ({
      currency: row.currency,
      revenue: Number(row.revenue ?? 0),
      refunds: Number(row.refunds ?? 0),
      net: Number(row.revenue ?? 0) - Number(row.refunds ?? 0),
    })) : [],
    events: (feed.results ?? []).map((row) => {
      let metadata: Record<string, unknown> = {};
      if (row.metadata) {
        try { metadata = JSON.parse(row.metadata) as Record<string, unknown>; } catch { metadata = {}; }
      }
      return { ...row, metadata };
    }),
  });
}

async function adminAttendees(env: Env): Promise<Response> {
  const settings = await getSettings(env);
  await cleanupStalePendingBookings(env, settings);

  const result = await env.DB.prepare(`
    WITH facts AS (
      SELECT
        lower(trim(email)) AS email_key,
        status,
        start_time,
        end_time,
        duration_minutes,
        payment_status,
        paid_at,
        refund_status,
        refunded_at,
        reschedule_count,
        created_at,
        CASE
          WHEN status = 'confirmed' THEN 1
          WHEN status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL) THEN 1
          ELSE 0
        END AS is_booking
      FROM bookings
      WHERE trim(email) <> ''
    )
    SELECT
      f.email_key,
      (
        SELECT b2.email FROM bookings b2
        WHERE lower(trim(b2.email)) = f.email_key
          AND (b2.status = 'confirmed' OR (b2.status = 'cancelled' AND (b2.payment_status <> 'cancelled' OR b2.paid_at IS NOT NULL)))
        ORDER BY datetime(b2.created_at) DESC, b2.rowid DESC
        LIMIT 1
      ) AS email,
      (
        SELECT b2.name FROM bookings b2
        WHERE lower(trim(b2.email)) = f.email_key
          AND (b2.status = 'confirmed' OR (b2.status = 'cancelled' AND (b2.payment_status <> 'cancelled' OR b2.paid_at IS NOT NULL)))
        ORDER BY datetime(b2.created_at) DESC, b2.rowid DESC
        LIMIT 1
      ) AS name,
      COALESCE(SUM(f.is_booking), 0) AS total_bookings,
      COALESCE(SUM(CASE WHEN f.status = 'confirmed' AND julianday(f.end_time) >= julianday('now') THEN 1 ELSE 0 END), 0) AS upcoming,
      COALESCE(SUM(CASE WHEN f.status = 'confirmed' AND julianday(f.end_time) < julianday('now') THEN 1 ELSE 0 END), 0) AS completed,
      COALESCE(SUM(CASE WHEN f.status = 'cancelled' AND f.is_booking = 1 THEN 1 ELSE 0 END), 0) AS cancelled,
      COALESCE(SUM(CASE WHEN f.is_booking = 1 THEN f.reschedule_count ELSE 0 END), 0) AS reschedules,
      COALESCE(SUM(CASE WHEN f.is_booking = 1 AND f.paid_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS paid_bookings,
      COALESCE(SUM(CASE WHEN f.is_booking = 1 AND (f.refunded_at IS NOT NULL OR f.refund_status = 'succeeded') THEN 1 ELSE 0 END), 0) AS refunded_bookings,
      COALESCE(SUM(CASE WHEN f.is_booking = 1 THEN f.duration_minutes ELSE 0 END), 0) AS total_minutes,
      COALESCE(ROUND(AVG(CASE WHEN f.is_booking = 1 THEN f.duration_minutes END), 1), 0) AS average_minutes,
      MIN(CASE WHEN f.is_booking = 1 THEN f.created_at END) AS first_booked_at,
      MAX(CASE WHEN f.is_booking = 1 THEN f.created_at END) AS last_booked_at
    FROM facts f
    GROUP BY f.email_key
    HAVING SUM(f.is_booking) > 0
    ORDER BY MAX(CASE WHEN f.is_booking = 1 THEN datetime(f.created_at) END) DESC
    LIMIT 500
  `).all<{
    email_key: string;
    email: string;
    name: string;
    total_bookings: number;
    upcoming: number;
    completed: number;
    cancelled: number;
    reschedules: number;
    paid_bookings: number;
    refunded_bookings: number;
    total_minutes: number;
    average_minutes: number;
    first_booked_at: string | null;
    last_booked_at: string | null;
  }>();

  return json({
    paymentsEnabled: settings.paymentEnabled,
    attendees: (result.results ?? []).map((row) => ({
      email: row.email,
      name: row.name,
      total_bookings: Number(row.total_bookings ?? 0),
      upcoming: Number(row.upcoming ?? 0),
      completed: Number(row.completed ?? 0),
      cancelled: Number(row.cancelled ?? 0),
      reschedules: Number(row.reschedules ?? 0),
      paid_bookings: Number(row.paid_bookings ?? 0),
      refunded_bookings: Number(row.refunded_bookings ?? 0),
      total_minutes: Number(row.total_minutes ?? 0),
      average_minutes: Number(row.average_minutes ?? 0),
      first_booked_at: row.first_booked_at,
      last_booked_at: row.last_booked_at,
    })),
  });
}

async function adminAttendeeBookings(env: Env, email: string): Promise<Response> {
  const normalizedEmail = email.trim().toLowerCase();
  if (!normalizedEmail || normalizedEmail.length > 320 || !normalizedEmail.includes('@')) {
    return json({ error: 'Invalid attendee email.' }, 400);
  }

  const result = await env.DB.prepare(`
    SELECT id, public_booking_id, name, email, start_time, end_time, duration_minutes,
           meeting_mode, status, meet_url, created_at,
           cancelled_at, calendar_provider, calendar_sync_status,
           calendar_sync_error, calendar_sync_updated_at,
           payment_status, payment_amount, payment_currency,
           stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
           rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time,
           proposed_at, proposal_status, refund_status, stripe_refund_id,
           refunded_amount, refunded_at
    FROM bookings
    WHERE lower(trim(email)) = ?1
      AND (
        status = 'confirmed'
        OR (status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL))
      )
    ORDER BY datetime(created_at) DESC, start_time DESC
    LIMIT 100
  `).bind(normalizedEmail).all();
  return json({ bookings: result.results ?? [] });
}

async function paymentBookingById(env: Env, bookingId: string): Promise<PaymentBookingRow | null> {
  return env.DB.prepare(`
    SELECT id, public_booking_id, name, email, phone, message, start_time, end_time, duration_minutes,
           timezone, meeting_mode, status, google_event_id, meet_url, payment_status,
           payment_amount, payment_currency, stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
           rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time, proposed_at,
           proposal_status, proposal_token_hash, refund_status, stripe_refund_id, refunded_amount,
           refunded_at, updated_at
    FROM bookings
    WHERE id = ?1
  `).bind(bookingId).first<PaymentBookingRow>();
}

async function releasePendingBooking(env: Env, settings: AppSettings, bookingId: string): Promise<void> {
  const booking = await paymentBookingById(env, bookingId);
  if (!booking || booking.status !== 'pending') return;
  if (booking.google_event_id) {
    let lastError: unknown = null;
    for (let attempt = 0; attempt < 3; attempt += 1) {
      try {
        await deleteCalendarEvent(env, settings, booking.google_event_id);
        lastError = null;
        break;
      } catch (error) {
        lastError = error;
        if (attempt < 2) await new Promise((resolve) => setTimeout(resolve, 150 * (attempt + 1)));
      }
    }
    if (lastError) throw lastError;
  }
  // Keep a cancelled audit row instead of deleting the booking. Besides making
  // released holds visible in Admin history, this preserves the Stripe session id
  // if a stale Checkout from another Stripe mode ever reports back later.
  await env.DB.batch([
    env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(bookingId),
    env.DB.prepare(`
      UPDATE bookings
      SET status = 'cancelled',
          payment_status = CASE WHEN payment_status = 'paid' THEN 'paid' ELSE 'cancelled' END,
          google_event_id = NULL,
          meet_url = NULL,
          cancelled_at = COALESCE(cancelled_at, datetime('now')),
          calendar_sync_status = 'cancelled',
          calendar_sync_error = NULL,
          calendar_sync_updated_at = datetime('now'),
          updated_at = datetime('now')
      WHERE id = ?1 AND status = 'pending'
    `).bind(bookingId),
  ]);
  await logActivity(env, bookingId, 'payment_hold_released', {
    start: booking.start_time,
    end: booking.end_time,
    amount: booking.payment_amount,
    currency: booking.payment_currency,
  }, `booking:${bookingId}:hold-released`);
}

async function recordPaidSession(
  env: Env,
  bookingId: string,
  session: StripeCheckoutSession,
): Promise<void> {
  await env.DB.prepare(`
    UPDATE bookings
    SET payment_status = 'paid',
        payment_amount = COALESCE(?1, payment_amount),
        payment_currency = COALESCE(?2, payment_currency),
        stripe_checkout_session_id = COALESCE(stripe_checkout_session_id, ?3),
        stripe_payment_intent_id = ?4,
        paid_at = COALESCE(paid_at, datetime('now'))
    WHERE id = ?5
      AND (stripe_checkout_session_id IS NULL OR stripe_checkout_session_id = ?3)
  `).bind(
    session.amount_total ?? null,
    session.currency ?? null,
    session.id,
    stripePaymentIntentId(session),
    bookingId,
  ).run();
  await logActivity(env, bookingId, 'payment_received', {
    amount: session.amount_total ?? null,
    currency: session.currency ?? null,
  }, `booking:${bookingId}:paid`);
}

async function fulfillPaidBooking(env: Env, bookingId: string): Promise<PaymentBookingRow> {
  let booking = await paymentBookingById(env, bookingId);
  if (!booking) throw new Error('Paid booking record was not found.');
  if (booking.status === 'confirmed') return booking;
  if (booking.status === 'confirming') {
    await env.DB.prepare(`
      UPDATE bookings
      SET status = 'pending', updated_at = datetime('now')
      WHERE id = ?1
        AND status = 'confirming'
        AND updated_at < datetime('now', '-2 minutes')
    `).bind(booking.id).run();
    booking = await paymentBookingById(env, booking.id);
    if (booking?.status === 'confirmed') return booking;
    if (booking?.status === 'confirming') {
      throw new Error('Paid booking confirmation is already in progress.');
    }
    if (!booking) throw new Error('Paid booking record was not found.');
  }
  if (booking.status !== 'pending' || booking.payment_status !== 'paid') {
    throw new Error('Booking is not ready for paid confirmation.');
  }

  // Claim fulfillment before touching Google. This keeps the Stripe webhook and
  // the browser return-page fallback from both sending the attendee invitation.
  const claim = await env.DB.prepare(`
    UPDATE bookings
    SET status = 'confirming',
        calendar_sync_status = 'confirming',
        calendar_sync_error = NULL,
        calendar_sync_updated_at = datetime('now'),
        updated_at = datetime('now')
    WHERE id = ?1
      AND status = 'pending'
      AND payment_status = 'paid'
  `).bind(booking.id).run();
  const claimed = ((claim as unknown as { meta?: { changes?: number } }).meta?.changes ?? 0) > 0;
  if (!claimed) {
    booking = await paymentBookingById(env, booking.id);
    if (booking?.status === 'confirmed') return booking;
    throw new Error('Paid booking confirmation is already in progress.');
  }

  const settings = await getSettings(env);
  try {
    const created = booking.google_event_id
      ? await confirmCalendarHold(env, settings, booking.google_event_id, {
          id: booking.id,
          publicBookingId: booking.public_booking_id ?? booking.id,
          name: booking.name,
          email: booking.email,
          phone: booking.phone ?? undefined,
          message: booking.message ?? undefined,
          start: booking.start_time,
          end: booking.end_time,
          meetingMode: booking.meeting_mode,
        })
      : await createCalendarEvent(env, settings, {
          id: booking.id,
          publicBookingId: booking.public_booking_id ?? booking.id,
          name: booking.name,
          email: booking.email,
          phone: booking.phone ?? undefined,
          message: booking.message ?? undefined,
          start: booking.start_time,
          end: booking.end_time,
          meetingMode: booking.meeting_mode,
        });

    await env.DB.batch([
      env.DB.prepare(`
        UPDATE bookings
        SET status = 'confirmed',
            google_event_id = ?1,
            meet_url = ?2,
            calendar_sync_status = 'synced',
            calendar_sync_error = NULL,
            calendar_sync_updated_at = datetime('now'),
            updated_at = datetime('now')
        WHERE id = ?3
      `).bind(created.eventId, created.meetUrl, booking.id),
      env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
        .bind(Date.now() + 10 * 60_000, booking.id),
    ]);
    await logActivity(env, booking.id, 'booking_confirmed', {
      start: booking.start_time,
      end: booking.end_time,
      duration: booking.duration_minutes,
      paid: true,
    }, `booking:${booking.id}:confirmed`);
  } catch (error) {
    await env.DB.prepare(`
      UPDATE bookings
      SET status = 'pending',
          calendar_sync_status = 'failed',
          calendar_sync_error = ?1,
          calendar_sync_updated_at = datetime('now'),
          updated_at = datetime('now')
      WHERE id = ?2
        AND status = 'confirming'
    `).bind(privateCalendarError(error), booking.id).run().catch(() => undefined);
    throw error;
  }

  const confirmed = await paymentBookingById(env, booking.id);
  if (!confirmed) throw new Error('Confirmed booking record disappeared.');
  return confirmed;
}

async function cleanupStalePendingBookings(env: Env, settings: AppSettings): Promise<void> {
  const stale = await env.DB.prepare(`
    SELECT id, stripe_checkout_session_id
    FROM bookings
    WHERE status = 'pending'
      AND created_at < datetime('now', '-35 minutes')
    ORDER BY created_at
    LIMIT 5
  `).all<{ id: string; stripe_checkout_session_id: string | null }>();

  for (const row of stale.results ?? []) {
    if (row.stripe_checkout_session_id && stripeConfigured(env)) {
      const sessionMode = stripeCheckoutSessionMode(row.stripe_checkout_session_id);
      const configuredMode = stripeMode(env);
      // These rows are already older than 35 minutes and Slot creates Checkout
      // sessions with a 30-minute expires_at. If the Worker has since switched
      // Stripe modes, the current key cannot retrieve the old session, but it is
      // safe to release the expired local hold instead of keeping it forever.
      const modeMismatch = sessionMode !== 'unknown'
        && configuredMode !== 'unknown'
        && sessionMode !== configuredMode;
      if (!modeMismatch) {
        try {
          const session = await retrieveStripeCheckoutSession(env, row.stripe_checkout_session_id);
          if (session.payment_status === 'paid') {
            await recordPaidSession(env, row.id, session);
            await fulfillPaidBooking(env, row.id);
            continue;
          }
          if (session.status === 'open') continue;
        } catch {
          // Do not destroy a reservation when Stripe cannot be checked safely.
          continue;
        }
      }
    }
    await releasePendingBooking(env, settings, row.id).catch(() => undefined);
  }
}

async function createBooking(env: Env, httpRequest: Request, request: BookingRequest): Promise<Response> {
  if (request.website) return json({ ok: true, message: 'Booked' }); // honeypot
  if (!(await enforceBookingRateLimit(httpRequest, env))) {
    return json({ error: 'Too many booking attempts from this connection. Try again later.' }, 429);
  }
  const name = String(request.name ?? '').trim().slice(0, 120);
  const email = String(request.email ?? '').trim().toLowerCase().slice(0, 254);
  const phone = String(request.phone ?? '').trim().slice(0, 80);
  const message = String(request.message ?? '').trim().slice(0, 2000);
  const duration = Number(request.duration);
  const startMs = Date.parse(request.start);

  if (!name || !isEmail(email) || !Number.isFinite(startMs)) {
    return json({ error: 'Name, a valid email, and a start time are required.' }, 400);
  }

  const settings = await getSettings(env);
  if (!settings.durations.includes(duration)) return json({ error: 'That duration is not available.' }, 400);
  if (!settings.allowMeetingModes.includes(request.meetingMode)) {
    return json({ error: 'That meeting type is not available.' }, 400);
  }
  if (request.meetingMode === 'phone' && !phone) {
    return json({ error: 'A phone number is required for a phone meeting.' }, 400);
  }

  const paymentRequired = settings.paymentEnabled && !settings.paymentFreeDurations.includes(duration);
  if (paymentRequired && settings.paymentRatePerMinute <= 0) {
    return json({ error: 'Payment is required, but the owner has not configured a per-minute price.' }, 503);
  }
  if (paymentRequired && !stripeConfigured(env)) {
    return json({ error: 'Payment is required, but Stripe is not configured. The owner needs to fix the Stripe setup.' }, 503);
  }

  const amountMinor = paymentRequired
    ? bookingAmountMinor(settings.paymentRatePerMinute, duration, settings.paymentCurrency)
    : 0;
  if (paymentRequired && amountMinor <= 0) {
    return json({ error: 'The configured per-minute price is too small for this currency.' }, 409);
  }

  const dateKey = dateKeyInZone(new Date(startMs), settings.timezone);
  const availability = await computeAvailability(env, dateKey);
  const matchingSlot = availability.slots.find((slot) => slot.start === new Date(startMs).toISOString());
  if (!matchingSlot || !matchingSlot.durations.includes(duration)) {
    return json({ error: 'That slot is no longer available. Pick another time.' }, 409);
  }

  const endMs = addMinutes(startMs, duration);
  const lockStart = addMinutes(startMs, -settings.bufferBeforeMinutes);
  const lockEnd = addMinutes(endMs, settings.bufferAfterMinutes);
  const id = crypto.randomUUID();
  const publicBookingId = newPublicBookingId();
  const lockExpiry = Date.now() + (paymentRequired ? 35 : 3) * 60_000;
  const keys = slotKeys(lockStart, lockEnd, settings.slotIncrementMinutes);

  await env.DB.prepare('DELETE FROM slot_locks WHERE expires_at < ?1').bind(Date.now()).run();

  const statements: D1PreparedStatement[] = [
    env.DB.prepare(`
      INSERT INTO bookings (
        id, public_booking_id, name, email, phone, message, start_time, end_time,
        duration_minutes, timezone, meeting_mode, status,
        payment_status, payment_amount, payment_currency
      ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, 'pending', ?12, ?13, ?14)
    `).bind(
      id,
      publicBookingId,
      name,
      email,
      phone || null,
      message || null,
      new Date(startMs).toISOString(),
      new Date(endMs).toISOString(),
      duration,
      settings.timezone,
      request.meetingMode,
      paymentRequired ? 'awaiting_checkout' : 'not_requested',
      paymentRequired ? amountMinor : null,
      paymentRequired ? settings.paymentCurrency : null,
    ),
    ...keys.map((key) =>
      env.DB.prepare('INSERT INTO slot_locks (slot_key, booking_id, expires_at) VALUES (?1, ?2, ?3)')
        .bind(key, id, lockExpiry),
    ),
  ];

  try {
    await env.DB.batch(statements);
  } catch {
    return json({ error: 'Someone just took that time. Please choose another slot.' }, 409);
  }

  let holdEventId: string | null = null;
  try {
    // Recheck Google after the D1 reservation. Paid bookings then get a private
    // owner-only calendar hold, so the visitor receives no invitation or Meet link
    // until Stripe says the payment succeeded.
    const fresh = await queryBusy(
      env,
      settings,
      new Date(lockStart).toISOString(),
      new Date(lockEnd).toISOString(),
    );
    const conflicts = fresh.some((range) =>
      lockStart < Date.parse(range.end) && lockEnd > Date.parse(range.start),
    );
    if (conflicts) throw new Error('That slot became busy in Google Calendar.');

    if (paymentRequired) {
      const hold = await createCalendarHold(env, settings, {
        id,
        start: new Date(startMs).toISOString(),
        end: new Date(endMs).toISOString(),
        meetingMode: request.meetingMode,
      });
      holdEventId = hold.eventId;
      await env.DB.prepare(`
        UPDATE bookings
        SET google_event_id = ?1,
            meet_url = ?2,
            calendar_sync_status = 'held',
            calendar_sync_updated_at = datetime('now'),
            updated_at = datetime('now')
        WHERE id = ?3
      `).bind(hold.eventId, hold.meetUrl, id).run();

      const session = await createStripeCheckoutSession(env, {
        id,
        email,
        durationMinutes: duration,
        amountMinor,
        currency: settings.paymentCurrency,
        label: settings.paymentLabel,
      });
      if (!session.id || !session.url) throw new StripeError('Stripe did not return a Checkout URL.');

      await env.DB.prepare(`
        UPDATE bookings
        SET payment_status = 'checkout_open',
            stripe_checkout_session_id = ?1,
            updated_at = datetime('now')
        WHERE id = ?2
      `).bind(session.id, id).run();
      await logActivity(env, id, 'payment_checkout_started', {
        start: new Date(startMs).toISOString(),
        end: new Date(endMs).toISOString(),
        duration,
        amount: amountMinor,
        currency: settings.paymentCurrency,
      }, `checkout:${session.id}:opened`);

      return json({
        ok: true,
        requiresPayment: true,
        checkoutUrl: session.url,
        bookingId: id,
        amountMinor,
        currency: settings.paymentCurrency,
      }, 201);
    }

    const created = await createCalendarEvent(env, settings, {
      id,
      publicBookingId,
      name,
      email,
      phone: phone || undefined,
      message: message || undefined,
      start: new Date(startMs).toISOString(),
      end: new Date(endMs).toISOString(),
      meetingMode: request.meetingMode,
    });

    await env.DB.batch([
      env.DB.prepare(`
        UPDATE bookings
        SET status = 'confirmed', google_event_id = ?1, meet_url = ?2,
            calendar_sync_status = 'synced', calendar_sync_updated_at = datetime('now'),
            updated_at = datetime('now')
        WHERE id = ?3
      `).bind(created.eventId, created.meetUrl, id),
      env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
        .bind(Date.now() + 10 * 60_000, id),
    ]);
    await logActivity(env, id, 'booking_confirmed', {
      start: new Date(startMs).toISOString(),
      end: new Date(endMs).toISOString(),
      duration,
      paid: false,
    }, `booking:${id}:confirmed`);

    return json({
      ok: true,
      requiresPayment: false,
      booking: {
        id,
        bookingId: publicBookingId,
        start: new Date(startMs).toISOString(),
        end: new Date(endMs).toISOString(),
        duration,
        timezone: settings.timezone,
        meetingMode: request.meetingMode,
        meetUrl: created.meetUrl,
      },
    }, 201);
  } catch (error) {
    if (holdEventId) {
      await deleteCalendarEvent(env, settings, holdEventId).catch(() => undefined);
    }
    await env.DB.batch([
      env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(id),
      env.DB.prepare('DELETE FROM bookings WHERE id = ?1').bind(id),
    ]).catch(() => undefined);
    if (error instanceof StripeError) return json({ error: error.message }, error.status);
    const message = errorMessage(error);
    const status = message.includes('became busy') ? 409 : 502;
    return json({ error: message }, status);
  }
}

async function createPaymentCheckout(env: Env, bookingId: string): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
  if (!stripeConfigured(env)) return json({ error: 'Stripe is not configured for this deployment.' }, 503);
  const booking = await paymentBookingById(env, bookingId);
  if (!booking) return json({ error: 'Booking not found.' }, 404);
  if (booking.status === 'confirmed' && booking.payment_status === 'paid') return json({ ok: true, paid: true, booking: publicConfirmation(booking) });
  if (booking.status !== 'pending' || !booking.stripe_checkout_session_id) {
    return json({ error: 'This booking does not have an active payment checkout.' }, 409);
  }

  try {
    const session = await retrieveStripeCheckoutSession(env, booking.stripe_checkout_session_id);
    if (session.payment_status === 'paid') {
      await recordPaidSession(env, booking.id, session);
      const confirmed = await fulfillPaidBooking(env, booking.id);
      return json({ ok: true, paid: true, booking: publicConfirmation(confirmed) });
    }
    if (session.status === 'open' && session.url) return json({ ok: true, url: session.url });
    if (session.status === 'complete') return json({ ok: true, processing: true });
    return json({ error: 'That Stripe checkout is no longer active. Please book the slot again.' }, 409);
  } catch (error) {
    if (error instanceof StripeError) return json({ error: error.message }, error.status);
    throw error;
  }
}

async function paymentStatus(env: Env, sessionId: string): Promise<Response> {
  if (!stripeConfigured(env)) return json({ error: 'Stripe is not configured for this deployment.' }, 503);
  try {
    const session = await retrieveStripeCheckoutSession(env, sessionId);
    const bookingId = session.metadata?.booking_id || session.client_reference_id;
    if (!bookingId || !/^[0-9a-f-]{36}$/i.test(bookingId)) {
      return json({ state: 'missing' }, 404);
    }

    const existing = await paymentBookingById(env, bookingId);
    if (existing?.status === 'confirmed') {
      const receiptUrl = session.payment_status === 'paid'
        ? await stripeReceiptUrl(env, session).catch(() => null)
        : null;
      return json({ state: 'confirmed', booking: publicConfirmation(existing, receiptUrl) });
    }

    if (session.payment_status === 'paid') {
      await recordPaidSession(env, bookingId, session);
      try {
        const confirmed = await fulfillPaidBooking(env, bookingId);
        const receiptUrl = await stripeReceiptUrl(env, session).catch(() => null);
        return json({ state: 'confirmed', booking: publicConfirmation(confirmed, receiptUrl) });
      } catch {
        return json({ state: 'confirming', message: 'Payment received. Finalizing your calendar invitation…' });
      }
    }

    if (session.status === 'complete') {
      return json({ state: 'processing', message: 'Stripe is still processing the payment.' });
    }
    if (session.status === 'expired') {
      const settings = await getSettings(env);
      await releasePendingBooking(env, settings, bookingId).catch(() => undefined);
      return json({ state: 'expired', message: 'Payment expired. No booking was confirmed.' });
    }
    return json({ state: 'awaiting_payment', message: 'Payment has not completed yet.' });
  } catch (error) {
    if (error instanceof StripeError) return json({ error: error.message }, error.status);
    throw error;
  }
}

async function cancelPendingPayment(
  env: Env,
  bookingId: string,
  options: { allowStaleStripeRelease?: boolean } = {},
): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
  const booking = await paymentBookingById(env, bookingId);
  if (!booking) return json({ ok: true, state: 'cancelled' });
  if (booking.status === 'confirmed') return json({ ok: true, state: 'confirmed', booking: publicConfirmation(booking) });
  if (booking.status === 'confirming') return json({ ok: true, state: 'processing' });
  if (booking.status !== 'pending') return json({ ok: true, state: booking.status });

  const settings = await getSettings(env);
  let releaseWarning: string | null = null;
  const sessionId = booking.stripe_checkout_session_id;

  if (sessionId && stripeConfigured(env)) {
    const sessionMode = stripeCheckoutSessionMode(sessionId);
    const configuredMode = stripeMode(env);
    const modeMismatch = sessionMode !== 'unknown'
      && configuredMode !== 'unknown'
      && sessionMode !== configuredMode;

    if (modeMismatch) {
      if (!options.allowStaleStripeRelease) {
        return json({
          error: `This payment hold was created in Stripe ${sessionMode} mode, but this deployment is using ${configuredMode} mode. Switch back to the ${sessionMode} key to expire that Checkout session, or release it from Admin.`,
          code: 'stripe_mode_mismatch',
          sessionMode,
          configuredMode,
        }, 409);
      }
      releaseWarning = `Released locally. The hold belongs to Stripe ${sessionMode} mode while this Worker uses ${configuredMode} mode, so Slot could not expire the old Stripe Checkout session.`;
    } else {
      try {
        const session = await retrieveStripeCheckoutSession(env, sessionId);
        if (session.payment_status === 'paid') {
          await recordPaidSession(env, booking.id, session);
          const confirmed = await fulfillPaidBooking(env, booking.id);
          return json({ ok: true, state: 'confirmed', booking: publicConfirmation(confirmed) });
        }
        if (session.status === 'complete') {
          return json({ ok: true, state: 'processing' });
        }
        if (session.status === 'open') await expireStripeCheckoutSession(env, session.id);
      } catch (error) {
        if (error instanceof StripeError) {
          // A resource_missing error is what Stripe returns when a Checkout
          // session belongs to another account/mode. Admin has already made an
          // explicit release decision, so let it clean up the local/calendar hold
          // instead of trapping the slot behind a stale Stripe id.
          if (options.allowStaleStripeRelease && (error.code === 'resource_missing' || /No such checkout\.session/i.test(error.message))) {
            releaseWarning = 'Released locally because Stripe could not find the stored Checkout session with the currently configured key.';
          } else {
            return json({ error: error.message }, error.status);
          }
        } else {
          throw error;
        }
      }
    }
  } else if (sessionId && options.allowStaleStripeRelease) {
    releaseWarning = 'Released locally. Stripe is not configured on this deployment, so Slot could not expire the stored Checkout session.';
  }

  await releasePendingBooking(env, settings, booking.id);
  return json({ ok: true, state: 'cancelled', warning: releaseWarning });
}

async function paymentCancelReturn(env: Env, bookingId: string): Promise<Response> {
  const appUrl = env.APP_URL.replace(/\/+$/, '');
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) {
    return redirect(`${appUrl}/?payment=cancelled&release_error=invalid_booking`);
  }

  const before = await paymentBookingById(env, bookingId);
  const sessionId = before?.stripe_checkout_session_id ?? null;
  const response = await cancelPendingPayment(env, bookingId);
  const payload = await response.clone().json().catch(() => ({})) as { state?: string };

  if (response.ok && (payload.state === 'confirmed' || payload.state === 'processing') && sessionId) {
    return redirect(`${appUrl}/?payment=success&session_id=${encodeURIComponent(sessionId)}`);
  }
  if (response.ok && payload.state === 'cancelled') {
    return redirect(`${appUrl}/?payment=cancelled&released=1`);
  }

  // Keep the booking id only on the error fallback so the browser can retry the
  // release through the normal POST endpoint and show a useful error if needed.
  return redirect(`${appUrl}/?payment=cancelled&booking_id=${encodeURIComponent(bookingId)}&release_error=1`);
}

async function handleStripeWebhook(request: Request, env: Env): Promise<Response> {
  const rawBody = await request.text();
  try {
    const event = await verifyStripeWebhook(env, rawBody, request.headers.get('Stripe-Signature'));
    const object = event.data?.object;
    if (!object) return json({ received: true });

    if (object.object === 'refund') {
      const refund = object as StripeRefund;
      const bookingId = refund.metadata?.booking_id;
      if (bookingId && /^[0-9a-f-]{36}$/i.test(bookingId)) {
        const status = event.type === 'refund.failed' ? 'failed' : (refund.status ?? 'pending');
        await env.DB.prepare(`
          UPDATE bookings
          SET refund_status = ?1,
              stripe_refund_id = COALESCE(stripe_refund_id, ?2),
              refunded_amount = COALESCE(?3, refunded_amount),
              refunded_at = CASE WHEN ?1 = 'succeeded' THEN COALESCE(refunded_at, datetime('now')) ELSE refunded_at END,
              updated_at = datetime('now')
          WHERE id = ?4
        `).bind(status, refund.id, refund.amount ?? null, bookingId).run();
        await logActivity(env, bookingId,
          status === 'succeeded' ? 'refund_succeeded' : status === 'failed' ? 'refund_failed' : 'refund_started',
          { amount: refund.amount ?? null, currency: refund.currency ?? null, refundStatus: status },
          `refund:${refund.id}:${status}`,
        );
      }
      return json({ received: true });
    }

    if (object.object !== 'checkout.session') return json({ received: true });
    const session = object;
    const bookingId = session.metadata?.booking_id || session.client_reference_id;
    if (!bookingId || !/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ received: true });

    if (event.type === 'checkout.session.completed' || event.type === 'checkout.session.async_payment_succeeded') {
      const paid = session.payment_status === 'paid' || event.type === 'checkout.session.async_payment_succeeded';
      if (paid) {
        await recordPaidSession(env, bookingId, session);
        await fulfillPaidBooking(env, bookingId);
      } else {
        await env.DB.prepare(`
          UPDATE bookings
          SET payment_status = CASE WHEN payment_status = 'paid' THEN 'paid' ELSE 'processing' END,
              payment_amount = COALESCE(?1, payment_amount),
              payment_currency = COALESCE(?2, payment_currency),
              stripe_payment_intent_id = COALESCE(?3, stripe_payment_intent_id),
              updated_at = datetime('now')
          WHERE id = ?4
            AND stripe_checkout_session_id = ?5
        `).bind(
          session.amount_total ?? null,
          session.currency ?? null,
          stripePaymentIntentId(session),
          bookingId,
          session.id,
        ).run();
      }
    } else if (event.type === 'checkout.session.async_payment_failed' || event.type === 'checkout.session.expired') {
      const settings = await getSettings(env);
      await releasePendingBooking(env, settings, bookingId);
    }

    return json({ received: true });
  } catch (error) {
    if (error instanceof StripeError) return json({ error: error.message }, error.status);
    throw error;
  }
}


async function assertRescheduleSlotAvailable(
  env: Env,
  booking: PaymentBookingRow,
  startIso: string,
): Promise<{ settings: AppSettings; startMs: number; endMs: number }> {
  const startMs = Date.parse(startIso);
  if (!Number.isFinite(startMs) || startMs <= Date.now()) throw new Error('Choose a future time.');
  const settings = await getSettings(env);
  const dateKey = dateKeyInZone(new Date(startMs), settings.timezone);
  const availability = await computeAvailability(env, dateKey);
  const matching = availability.slots.find((slot) => slot.start === new Date(startMs).toISOString());
  if (!matching || !matching.durations.includes(booking.duration_minutes)) {
    throw new Error('That suggested time is no longer available. Choose another time.');
  }
  return { settings, startMs, endMs: addMinutes(startMs, booking.duration_minutes) };
}

async function rescheduleConfirmedBooking(
  env: Env,
  booking: PaymentBookingRow,
  startIso: string,
  source: 'admin' | 'guest_suggestion' | 'guest_self' = 'admin',
): Promise<PaymentBookingRow> {
  if (booking.status !== 'confirmed') throw new Error('Only confirmed bookings can be rescheduled.');
  if (!booking.google_event_id) throw new Error('This booking is missing its Google Calendar event.');
  const { settings, startMs, endMs } = await assertRescheduleSlotAvailable(env, booking, startIso);
  const lockStart = addMinutes(startMs, -settings.bufferBeforeMinutes);
  const lockEnd = addMinutes(endMs, settings.bufferAfterMinutes);
  const keys = slotKeys(lockStart, lockEnd, settings.slotIncrementMinutes);

  await env.DB.prepare('DELETE FROM slot_locks WHERE expires_at < ?1').bind(Date.now()).run();
  await env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(booking.id).run();
  try {
    await env.DB.batch(keys.map((key) =>
      env.DB.prepare('INSERT INTO slot_locks (slot_key, booking_id, expires_at) VALUES (?1, ?2, ?3)')
        .bind(key, booking.id, Date.now() + 3 * 60_000),
    ));
  } catch {
    throw new Error('Someone just took that time. Choose another slot.');
  }

  try {
    const fresh = await queryBusy(env, settings, new Date(lockStart).toISOString(), new Date(lockEnd).toISOString());
    const conflicts = fresh.some((range) => lockStart < Date.parse(range.end) && lockEnd > Date.parse(range.start));
    if (conflicts) throw new Error('That time became busy in Google Calendar. Choose another slot.');

    const updated = await rescheduleCalendarEvent(env, settings, booking.google_event_id, {
      phone: booking.phone ?? undefined,
      message: booking.message ?? undefined,
      paid: booking.payment_status === 'paid',
      publicBookingId: booking.public_booking_id ?? undefined,
      start: new Date(startMs).toISOString(),
      end: new Date(endMs).toISOString(),
    });

    try {
      await env.DB.batch([
        env.DB.prepare(`
          UPDATE bookings
          SET start_time = ?1,
              end_time = ?2,
              meet_url = COALESCE(?3, meet_url),
              rescheduled_at = datetime('now'),
              reschedule_count = reschedule_count + 1,
              proposed_start_time = NULL,
              proposed_end_time = NULL,
              proposed_at = NULL,
              proposal_status = NULL,
              proposal_token_hash = NULL,
              calendar_sync_status = 'synced',
              calendar_sync_error = NULL,
              calendar_sync_updated_at = datetime('now'),
              updated_at = datetime('now')
          WHERE id = ?4 AND status = 'confirmed'
        `).bind(new Date(startMs).toISOString(), new Date(endMs).toISOString(), updated.meetUrl, booking.id),
        env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
          .bind(Date.now() + 10 * 60_000, booking.id),
      ]);
    } catch (error) {
      await rescheduleCalendarEvent(env, settings, booking.google_event_id, {
        phone: booking.phone ?? undefined,
        message: booking.message ?? undefined,
        paid: booking.payment_status === 'paid',
        publicBookingId: booking.public_booking_id ?? undefined,
        start: booking.start_time,
        end: booking.end_time,
      }).catch(() => undefined);
      throw error;
    }
  } catch (error) {
    await env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(booking.id).run().catch(() => undefined);
    throw error;
  }

  const result = await paymentBookingById(env, booking.id);
  if (!result) throw new Error('Rescheduled booking record was not found.');
  await logActivity(env, booking.id, source === 'guest_suggestion' ? 'time_suggestion_accepted' : 'booking_rescheduled', {
    source,
    oldStart: booking.start_time,
    oldEnd: booking.end_time,
    newStart: result.start_time,
    newEnd: result.end_time,
    duration: booking.duration_minutes,
  }, `booking:${booking.id}:reschedule:${result.reschedule_count}`);
  return result;
}

async function adminRescheduleBooking(env: Env, bookingId: string, start: string): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
  const booking = await paymentBookingById(env, bookingId);
  if (!booking) return json({ error: 'Booking not found.' }, 404);
  try {
    const updated = await rescheduleConfirmedBooking(env, booking, start, 'admin');
    return json({ ok: true, booking: publicConfirmation(updated) });
  } catch (error) {
    if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
    return json({ error: errorMessage(error) }, /available|busy|future|took/i.test(errorMessage(error)) ? 409 : 400);
  }
}

async function adminSuggestNewTime(env: Env, bookingId: string, start: string): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
  const booking = await paymentBookingById(env, bookingId);
  if (!booking) return json({ error: 'Booking not found.' }, 404);
  if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings can receive a new-time suggestion.' }, 409);
  if (!booking.google_event_id) return json({ error: 'This booking is missing its Google Calendar event.' }, 409);

  try {
    const { settings, startMs, endMs } = await assertRescheduleSlotAvailable(env, booking, start);
    const token = randomUrlToken();
    const tokenHash = await sha256Base64Url(token);
    const suggestionUrl = `${env.APP_URL.replace(/\/+$/, '')}/reschedule/${encodeURIComponent(token)}`;
    await env.DB.prepare(`
      UPDATE bookings
      SET proposed_start_time = ?1,
          proposed_end_time = ?2,
          proposed_at = datetime('now'),
          proposal_status = 'pending',
          proposal_token_hash = ?3,
          updated_at = datetime('now')
      WHERE id = ?4 AND status = 'confirmed'
    `).bind(new Date(startMs).toISOString(), new Date(endMs).toISOString(), tokenHash, booking.id).run();

    try {
      await sendRescheduleSuggestion(env, settings, booking.google_event_id, {
        phone: booking.phone ?? undefined,
        message: booking.message ?? undefined,
        paid: booking.payment_status === 'paid',
        publicBookingId: booking.public_booking_id ?? undefined,
      }, {
        start: new Date(startMs).toISOString(),
        end: new Date(endMs).toISOString(),
        url: suggestionUrl,
      });
    } catch (error) {
      await env.DB.prepare(`
        UPDATE bookings
        SET proposed_start_time = NULL, proposed_end_time = NULL, proposed_at = NULL,
            proposal_status = NULL, proposal_token_hash = NULL, updated_at = datetime('now')
        WHERE id = ?1
      `).bind(booking.id).run().catch(() => undefined);
      throw error;
    }

    await logActivity(env, booking.id, 'time_suggested', {
      currentStart: booking.start_time,
      currentEnd: booking.end_time,
      proposedStart: new Date(startMs).toISOString(),
      proposedEnd: new Date(endMs).toISOString(),
      duration: booking.duration_minutes,
    }, `suggest:${tokenHash}`);

    return json({
      ok: true,
      suggestionUrl,
      proposedStart: new Date(startMs).toISOString(),
      proposedEnd: new Date(endMs).toISOString(),
    });
  } catch (error) {
    if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
    return json({ error: errorMessage(error) }, /available|busy|future/i.test(errorMessage(error)) ? 409 : 400);
  }
}

async function proposalBookingByToken(env: Env, token: string): Promise<PaymentBookingRow | null> {
  if (!/^[A-Za-z0-9_-]{20,200}$/.test(token)) return null;
  const hash = await sha256Base64Url(token);
  return env.DB.prepare(`
    SELECT id, public_booking_id, name, email, phone, message, start_time, end_time, duration_minutes,
           timezone, meeting_mode, status, google_event_id, meet_url, payment_status,
           payment_amount, payment_currency, stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
           rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time, proposed_at,
           proposal_status, proposal_token_hash, refund_status, stripe_refund_id, refunded_amount,
           refunded_at, updated_at
    FROM bookings
    WHERE proposal_token_hash = ?1
      AND proposal_status = 'pending'
  `).bind(hash).first<PaymentBookingRow>();
}

async function getRescheduleProposal(env: Env, token: string): Promise<Response> {
  const booking = await proposalBookingByToken(env, token);
  if (!booking || !booking.proposed_start_time || !booking.proposed_end_time) {
    return json({ error: 'This reschedule suggestion is invalid or no longer active.' }, 404);
  }
  const settings = await getSettings(env);
  return json({
    title: settings.title,
    name: booking.name,
    currentStart: booking.start_time,
    currentEnd: booking.end_time,
    proposedStart: booking.proposed_start_time,
    proposedEnd: booking.proposed_end_time,
    duration: booking.duration_minutes,
    timezone: booking.timezone,
    meetingMode: booking.meeting_mode,
  });
}

async function acceptRescheduleProposal(env: Env, token: string): Promise<Response> {
  const booking = await proposalBookingByToken(env, token);
  if (!booking || !booking.proposed_start_time) {
    return json({ error: 'This reschedule suggestion is invalid or no longer active.' }, 404);
  }
  try {
    const updated = await rescheduleConfirmedBooking(env, booking, booking.proposed_start_time, 'guest_suggestion');
    return json({ ok: true, booking: publicConfirmation(updated) });
  } catch (error) {
    if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
    return json({ error: errorMessage(error) }, /available|busy|future|took/i.test(errorMessage(error)) ? 409 : 400);
  }
}

async function cancelBooking(env: Env, bookingId: string, allowStaleStripeRelease = false): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
  const booking = await paymentBookingById(env, bookingId);

  if (!booking) return json({ error: 'Booking not found.' }, 404);
  if (booking.status === 'cancelled') {
    return json({
      ok: true,
      refunded: booking.refund_status === 'succeeded' || booking.refund_status === 'pending',
      refundStatus: booking.refund_status,
    });
  }
  if (booking.status === 'pending' && booking.payment_status !== 'not_requested') {
    return cancelPendingPayment(env, bookingId, { allowStaleStripeRelease });
  }
  if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings or pending payment holds can be cancelled.' }, 409);

  let refundStatus = booking.refund_status;
  let refunded = refundStatus === 'succeeded' || refundStatus === 'pending';
  let refundWarning: string | null = null;

  if (booking.payment_status === 'paid' && !refunded) {
    if (!stripeConfigured(env)) {
      return json({ error: 'This booking was paid, but Stripe is not configured. Slot will not cancel it without attempting the refund.' }, 503);
    }
    const sessionMode = stripeCheckoutSessionMode(booking.stripe_checkout_session_id);
    const configuredMode = stripeMode(env);
    if (sessionMode !== 'unknown' && configuredMode !== 'unknown' && sessionMode !== configuredMode) {
      return json({
        error: `This booking was paid in Stripe ${sessionMode} mode, but Slot is currently using ${configuredMode} mode. Switch to the ${sessionMode} Stripe key before cancelling so Slot can refund the payment.`,
        code: 'stripe_mode_mismatch',
      }, 409);
    }

    let paymentIntentId = booking.stripe_payment_intent_id;
    if (!paymentIntentId && booking.stripe_checkout_session_id) {
      try {
        const session = await retrieveStripeCheckoutSession(env, booking.stripe_checkout_session_id);
        paymentIntentId = stripePaymentIntentId(session);
        if (paymentIntentId) {
          await env.DB.prepare(`
            UPDATE bookings
            SET stripe_payment_intent_id = ?1, updated_at = datetime('now')
            WHERE id = ?2
          `).bind(paymentIntentId, booking.id).run();
        }
      } catch (error) {
        if (error instanceof StripeError) return json({ error: error.message }, error.status);
        throw error;
      }
    }
    if (!paymentIntentId) {
      return json({ error: 'This paid booking is missing its Stripe PaymentIntent, so Slot cannot safely refund it.' }, 409);
    }

    try {
      const refund = await createStripeRefund(env, paymentIntentId, booking.id);
      refundStatus = refund.status ?? 'pending';
      if (refundStatus === 'failed' || refundStatus === 'canceled' || refundStatus === 'requires_action') {
        await env.DB.prepare(`
          UPDATE bookings
          SET refund_status = ?1,
              stripe_refund_id = ?2,
              refunded_amount = ?3,
              updated_at = datetime('now')
          WHERE id = ?4
        `).bind(refundStatus, refund.id, refund.amount, booking.id).run();
        await logActivity(env, booking.id, 'refund_failed', {
          amount: refund.amount,
          currency: booking.payment_currency,
          refundStatus,
        }, `refund:${refund.id}:${refundStatus}`);
        return json({ error: `Stripe created refund ${refund.id}, but its status is ${refundStatus}. The booking has not been cancelled.` }, 502);
      }
      refunded = true;
      await env.DB.prepare(`
        UPDATE bookings
        SET refund_status = ?1,
            stripe_refund_id = ?2,
            refunded_amount = ?3,
            refunded_at = CASE WHEN ?1 = 'succeeded' THEN datetime('now') ELSE refunded_at END,
            updated_at = datetime('now')
        WHERE id = ?4
      `).bind(refundStatus, refund.id, refund.amount, booking.id).run();
      await logActivity(env, booking.id, refundStatus === 'succeeded' ? 'refund_succeeded' : 'refund_started', {
        amount: refund.amount,
        currency: booking.payment_currency,
        refundStatus,
      }, `refund:${refund.id}:${refundStatus}`);
      if (refundStatus === 'pending') {
        refundWarning = 'Stripe accepted the refund, but it is still pending.';
      }
    } catch (error) {
      if (error instanceof StripeError) return json({ error: `Stripe refund failed: ${error.message}` }, error.status);
      throw error;
    }
  }

  const settings = await getSettings(env);
  let calendarWarning: string | null = null;
  try {
    if (booking.google_event_id) await deleteCalendarEvent(env, settings, booking.google_event_id);
  } catch (error) {
    const message = privateCalendarError(error);
    if (!refunded) {
      await env.DB.prepare(`
        UPDATE bookings
        SET calendar_sync_status = 'failed',
            calendar_sync_error = ?1,
            calendar_sync_updated_at = datetime('now'),
            updated_at = datetime('now')
        WHERE id = ?2
      `).bind(message, bookingId).run();
      return json({ error: 'Slot could not cancel the Google Calendar event. The booking has not been cancelled.' }, 502);
    }
    calendarWarning = 'The Stripe refund was created, but Google Calendar could not be updated. Remove the calendar event manually.';
  }

  await env.DB.batch([
    env.DB.prepare(`
      UPDATE bookings
      SET status = 'cancelled',
          cancelled_at = datetime('now'),
          proposed_start_time = NULL,
          proposed_end_time = NULL,
          proposed_at = NULL,
          proposal_status = NULL,
          proposal_token_hash = NULL,
          calendar_sync_status = ?1,
          calendar_sync_error = ?2,
          calendar_sync_updated_at = datetime('now'),
          updated_at = datetime('now')
      WHERE id = ?3
    `).bind(calendarWarning ? 'failed' : 'synced', calendarWarning, bookingId),
    env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(bookingId),
  ]);
  await logActivity(env, bookingId, 'booking_cancelled', {
    start: booking.start_time,
    end: booking.end_time,
    duration: booking.duration_minutes,
    refunded,
    refundStatus,
  }, `booking:${bookingId}:cancelled`);

  return json({
    ok: true,
    refunded,
    refundStatus,
    warning: [refundWarning, calendarWarning].filter(Boolean).join(' ') || undefined,
  });
}

async function publicCancelBooking(request: Request, env: Env, value: unknown): Promise<Response> {
  if (!(await enforceCancellationRateLimit(request, env))) {
    return json({ error: 'Too many cancellation attempts. Try again later.' }, 429);
  }

  const publicBookingId = normalizePublicBookingId(value);
  if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);

  const row = await env.DB.prepare(`
    SELECT id, status, end_time
    FROM bookings
    WHERE public_booking_id = ?1
  `).bind(publicBookingId).first<{ id: string; status: string; end_time: string }>();

  if (!row) return json({ error: 'Booking ID not found.' }, 404);
  if (row.status === 'cancelled') return json({ ok: true, alreadyCancelled: true });
  if (row.status !== 'confirmed') return json({ error: 'This booking is not currently confirmed.' }, 409);
  if (Date.parse(row.end_time) <= Date.now()) return json({ error: 'Past bookings cannot be cancelled.' }, 409);

  return cancelBooking(env, row.id);
}


async function publicManageBooking(request: Request, env: Env, value: unknown): Promise<Response> {
  if (!(await enforceManageRateLimit(request, env))) {
    return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
  }
  const publicBookingId = normalizePublicBookingId(value);
  if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
  const booking = await bookingByPublicId(env, publicBookingId);
  if (!booking) return json({ error: 'Booking ID not found.' }, 404);
  return json({ booking: publicManagePayload(booking) });
}

async function publicManageAvailability(
  request: Request,
  env: Env,
  value: unknown,
  date: unknown,
): Promise<Response> {
  if (!(await enforceManageRateLimit(request, env))) {
    return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
  }
  const publicBookingId = normalizePublicBookingId(value);
  if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
  const booking = await bookingByPublicId(env, publicBookingId);
  if (!booking) return json({ error: 'Booking ID not found.' }, 404);
  if (booking.status !== 'confirmed' || Date.parse(booking.end_time) <= Date.now()) {
    return json({ error: 'Only upcoming confirmed bookings can be rescheduled.' }, 409);
  }
  const dateKey = String(date ?? '').trim();
  try {
    const availability = await computeAvailability(env, dateKey);
    const slots = availability.slots
      .filter((slot) => slot.durations.includes(booking.duration_minutes))
      .map((slot) => ({ start: slot.start, time: slot.time }));
    return json({ date: dateKey, timezone: availability.settings.timezone, duration: booking.duration_minutes, slots });
  } catch (error) {
    if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
    return json({ error: errorMessage(error) }, 400);
  }
}

async function publicRescheduleBooking(
  request: Request,
  env: Env,
  value: unknown,
  start: unknown,
): Promise<Response> {
  if (!(await enforceManageRateLimit(request, env))) {
    return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
  }
  const publicBookingId = normalizePublicBookingId(value);
  if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
  const booking = await bookingByPublicId(env, publicBookingId);
  if (!booking) return json({ error: 'Booking ID not found.' }, 404);
  if (booking.status !== 'confirmed' || Date.parse(booking.end_time) <= Date.now()) {
    return json({ error: 'Only upcoming confirmed bookings can be rescheduled.' }, 409);
  }
  try {
    const updated = await rescheduleConfirmedBooking(env, booking, String(start ?? ''), 'guest_self');
    return json({ ok: true, booking: publicManagePayload(updated) });
  } catch (error) {
    if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
    const message = errorMessage(error);
    return json({ error: message }, /available|busy|future|took/i.test(message) ? 409 : 400);
  }
}

function cleanLabel(value: unknown): string | null {
  const label = String(value ?? '').trim().slice(0, 80);
  return label || null;
}

async function listAvailabilityRules(env: Env): Promise<Response> {
  const recurring = await env.DB.prepare(`
    SELECT id, weekdays, start_time, end_time, label
    FROM recurring_unavailable_periods
    ORDER BY start_time
  `).all<{ id: string; weekdays: string; start_time: string; end_time: string; label: string | null }>();
  const blocks = await env.DB.prepare(`
    SELECT id, date, start_time, end_time, all_day, label
    FROM availability_blocks
    ORDER BY date DESC, all_day DESC, start_time
    LIMIT 100
  `).all<{ id: string; date: string; start_time: string | null; end_time: string | null; all_day: number; label: string | null }>();

  return json({
    recurring: (recurring.results ?? []).map((row) => ({
      id: row.id,
      weekdays: row.weekdays.split(',').map(Number).filter((day) => day >= 0 && day <= 6),
      start: row.start_time,
      end: row.end_time,
      label: row.label,
    })),
    blocks: (blocks.results ?? []).map((row) => ({
      id: row.id,
      date: row.date,
      start: row.start_time,
      end: row.end_time,
      allDay: row.all_day === 1,
      label: row.label,
    })),
  });
}

async function createRecurringUnavailable(env: Env, input: unknown): Promise<Response> {
  const raw = input as Partial<{ weekdays: unknown[]; start: string; end: string; label: string }>;
  const weekdays = Array.isArray(raw.weekdays)
    ? [...new Set(raw.weekdays.map(Number).filter((day) => Number.isInteger(day) && day >= 0 && day <= 6))].sort((a, b) => a - b)
    : [];
  const start = String(raw.start ?? '');
  const end = String(raw.end ?? '');
  if (!weekdays.length || !isTime(start) || !isTime(end) || timeToMinutes(end) <= timeToMinutes(start)) {
    return json({ error: 'Choose weekdays plus a valid start and end time.' }, 400);
  }

  const id = crypto.randomUUID();
  await env.DB.prepare(`
    INSERT INTO recurring_unavailable_periods (id, weekdays, start_time, end_time, label)
    VALUES (?1, ?2, ?3, ?4, ?5)
  `).bind(id, weekdays.join(','), start, end, cleanLabel(raw.label)).run();
  return json({ ok: true, id }, 201);
}

async function createAvailabilityBlock(env: Env, input: unknown): Promise<Response> {
  const raw = input as Partial<{ date: string; start: string; end: string; allDay: boolean; label: string }>;
  const date = String(raw.date ?? '');
  const allDay = Boolean(raw.allDay);
  const start = String(raw.start ?? '');
  const end = String(raw.end ?? '');
  if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) return json({ error: 'Choose a valid date.' }, 400);
  if (!allDay && (!isTime(start) || !isTime(end) || timeToMinutes(end) <= timeToMinutes(start))) {
    return json({ error: 'Choose a valid start and end time, or mark the block all day.' }, 400);
  }

  const id = crypto.randomUUID();
  await env.DB.prepare(`
    INSERT INTO availability_blocks (id, date, start_time, end_time, all_day, label)
    VALUES (?1, ?2, ?3, ?4, ?5, ?6)
  `).bind(id, date, allDay ? null : start, allDay ? null : end, allDay ? 1 : 0, cleanLabel(raw.label)).run();
  return json({ ok: true, id }, 201);
}

async function deleteAvailabilityRule(env: Env, kind: string, id: string): Promise<Response> {
  if (!/^[0-9a-f-]{36}$/i.test(id)) return json({ error: 'Invalid rule id.' }, 400);
  if (kind === 'recurring') {
    await env.DB.prepare('DELETE FROM recurring_unavailable_periods WHERE id = ?1').bind(id).run();
    return json({ ok: true });
  }
  if (kind === 'block') {
    await env.DB.prepare('DELETE FROM availability_blocks WHERE id = ?1').bind(id).run();
    return json({ ok: true });
  }
  return json({ error: 'Invalid availability rule type.' }, 400);
}

async function handleApi(request: Request, env: Env, url: URL): Promise<Response> {
  if (request.method === 'GET' && url.pathname === '/api/config') {
    const settings = await getSettings(env);
    const connected = await hasGoogleConnection(env);
    const paymentRequired = settings.paymentEnabled;
    return json({
      title: settings.title,
      subtitle: settings.subtitle,
      timezone: settings.timezone,
      bookingWindowDays: settings.bookingWindowDays,
      durations: settings.durations,
      defaultMeetingMode: settings.defaultMeetingMode,
      allowMeetingModes: settings.allowMeetingModes,
      availableWeekdays: Object.entries(settings.availability)
        .filter(([, intervals]) => intervals.length > 0)
        .map(([day]) => Number(day)),
      connected,
      payment: paymentRequired ? {
        enabled: true,
        required: settings.paymentFreeDurations.length === 0,
        ready: stripeConfigured(env) && settings.paymentRatePerMinute > 0,
        ratePerMinute: settings.paymentRatePerMinute,
        freeDurations: settings.paymentFreeDurations,
        currency: settings.paymentCurrency,
        label: settings.paymentLabel,
      } : undefined,
    });
  }

  if (request.method === 'GET' && url.pathname === '/api/availability') {
    const date = url.searchParams.get('date') ?? '';
    try {
      const result = await computeAvailability(env, date);
      return json({
        date: result.date,
        timezone: result.settings.timezone,
        connected: result.connected,
        slots: result.slots,
      });
    } catch (error) {
      if (error instanceof GoogleCalendarError) {
        const settings = await getSettings(env);
        return json({
          date,
          timezone: settings.timezone,
          connected: true,
          slots: [],
          calendarError: error.publicMessage,
        });
      }
      throw error;
    }
  }

  if (request.method === 'POST' && url.pathname === '/api/book') {
    const body = (await request.json()) as BookingRequest;
    return createBooking(env, request, body);
  }

  if (request.method === 'POST' && url.pathname === '/api/bookings/cancel') {
    const body = await request.json().catch(() => ({})) as { bookingId?: string };
    return publicCancelBooking(request, env, body.bookingId);
  }

  if (request.method === 'POST' && url.pathname === '/api/bookings/manage') {
    const body = await request.json().catch(() => ({})) as { bookingId?: string };
    return publicManageBooking(request, env, body.bookingId);
  }

  if (request.method === 'POST' && url.pathname === '/api/bookings/manage/availability') {
    const body = await request.json().catch(() => ({})) as { bookingId?: string; date?: string };
    return publicManageAvailability(request, env, body.bookingId, body.date);
  }

  if (request.method === 'POST' && url.pathname === '/api/bookings/reschedule') {
    const body = await request.json().catch(() => ({})) as { bookingId?: string; start?: string };
    return publicRescheduleBooking(request, env, body.bookingId, body.start);
  }

  if (request.method === 'POST' && url.pathname === '/api/payments/checkout') {
    const body = (await request.json()) as { bookingId?: string };
    return createPaymentCheckout(env, String(body.bookingId ?? ''));
  }

  if (request.method === 'GET' && url.pathname === '/api/payments/status') {
    return paymentStatus(env, String(url.searchParams.get('session_id') ?? ''));
  }

  if (request.method === 'GET' && url.pathname === '/api/payments/cancel-return') {
    return paymentCancelReturn(env, String(url.searchParams.get('booking_id') ?? ''));
  }

  if (request.method === 'POST' && url.pathname === '/api/payments/cancel') {
    const body = (await request.json()) as { bookingId?: string };
    return cancelPendingPayment(env, String(body.bookingId ?? ''));
  }

  if (request.method === 'POST' && url.pathname === '/api/stripe/webhook') {
    return handleStripeWebhook(request, env);
  }

  const proposalMatch = url.pathname.match(/^\/api\/reschedule-suggestions\/([^/]+)$/);
  if (request.method === 'GET' && proposalMatch) {
    return getRescheduleProposal(env, decodeURIComponent(proposalMatch[1]));
  }
  const proposalAcceptMatch = url.pathname.match(/^\/api\/reschedule-suggestions\/([^/]+)\/accept$/);
  if (request.method === 'POST' && proposalAcceptMatch) {
    return acceptRescheduleProposal(env, decodeURIComponent(proposalAcceptMatch[1]));
  }

  if (url.pathname.startsWith('/api/admin/')) {
    const admin = await requireAdmin(request, env);
    if (!admin) return json({ error: 'Unauthorized' }, 401);

    if (request.method === 'GET' && url.pathname === '/api/admin/session') {
      const connected = await hasGoogleConnection(env);
      return json({ authenticated: true, email: admin, connected });
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/settings') {
      return json({
        settings: await getSettings(env),
        stripeConfigured: stripeConfigured(env),
        stripeMode: stripeMode(env),
      });
    }

    if (request.method === 'PUT' && url.pathname === '/api/admin/settings') {
      const settings = validateAppSettings(await request.json());
      await saveSettings(env, settings);
      return json({ ok: true, settings });
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/activity') {
      return adminActivity(env);
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/attendees') {
      return adminAttendees(env);
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/attendee-bookings') {
      return adminAttendeeBookings(env, url.searchParams.get('email') ?? '');
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/bookings') {
      const settings = await getSettings(env);
      await cleanupStalePendingBookings(env, settings);
      const query = (url.searchParams.get('q') ?? '').trim().toLowerCase();
      const select = `
        SELECT id, public_booking_id, name, email, start_time, end_time, duration_minutes,
               meeting_mode, status, meet_url, created_at,
               cancelled_at, calendar_provider, calendar_sync_status,
               calendar_sync_error, calendar_sync_updated_at,
               payment_status, payment_amount, payment_currency,
               stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
               rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time,
               proposed_at, proposal_status, refund_status, stripe_refund_id,
               refunded_amount, refunded_at
        FROM bookings
      `;
      const result = query
        ? await env.DB.prepare(`${select}
            WHERE lower(COALESCE(public_booking_id, '')) LIKE ?1
               OR lower(id) LIKE ?1
               OR lower(name) LIKE ?1
               OR lower(email) LIKE ?1
            ORDER BY start_time DESC
            LIMIT 100
          `).bind(`%${query}%`).all()
        : await env.DB.prepare(`${select}
            ORDER BY start_time DESC
            LIMIT 100
          `).all();
      return json({ bookings: result.results ?? [] });
    }

    const cancelMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/cancel$/);
    if (request.method === 'POST' && cancelMatch) {
      const body = await request.json().catch(() => ({})) as { allowStaleStripeRelease?: boolean };
      return cancelBooking(env, cancelMatch[1], body.allowStaleStripeRelease === true);
    }

    const bookingAvailabilityMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/availability$/);
    if (request.method === 'GET' && bookingAvailabilityMatch) {
      const booking = await paymentBookingById(env, bookingAvailabilityMatch[1]);
      if (!booking) return json({ error: 'Booking not found.' }, 404);
      if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings can be rescheduled.' }, 409);
      const date = url.searchParams.get('date') ?? '';
      try {
        const result = await computeAvailability(env, date);
        return json({
          date: result.date,
          timezone: result.settings.timezone,
          duration: booking.duration_minutes,
          slots: result.slots.filter((slot) => slot.durations.includes(booking.duration_minutes)),
        });
      } catch (error) {
        return json({ error: errorMessage(error) }, 400);
      }
    }

    const rescheduleMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/reschedule$/);
    if (request.method === 'POST' && rescheduleMatch) {
      const body = await request.json().catch(() => ({})) as { start?: string };
      return adminRescheduleBooking(env, rescheduleMatch[1], String(body.start ?? ''));
    }

    const suggestMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/suggest$/);
    if (request.method === 'POST' && suggestMatch) {
      const body = await request.json().catch(() => ({})) as { start?: string };
      return adminSuggestNewTime(env, suggestMatch[1], String(body.start ?? ''));
    }

    if (request.method === 'GET' && url.pathname === '/api/admin/availability-rules') {
      return listAvailabilityRules(env);
    }

    if (request.method === 'POST' && url.pathname === '/api/admin/availability-rules/recurring') {
      return createRecurringUnavailable(env, await request.json());
    }

    if (request.method === 'POST' && url.pathname === '/api/admin/availability-rules/blocks') {
      return createAvailabilityBlock(env, await request.json());
    }

    const availabilityDeleteMatch = url.pathname.match(/^\/api\/admin\/availability-rules\/(recurring|block)\/([^/]+)$/);
    if (request.method === 'DELETE' && availabilityDeleteMatch) {
      return deleteAvailabilityRule(env, availabilityDeleteMatch[1], availabilityDeleteMatch[2]);
    }

    if (request.method === 'POST' && url.pathname === '/api/admin/disconnect-google') {
      await disconnectGoogle(env);
      return json({ ok: true });
    }

    if (request.method === 'POST' && url.pathname === '/api/admin/logout') {
      return json({ ok: true }, 200, { 'Set-Cookie': clearSessionCookie(env.APP_URL.startsWith('https://')) });
    }
  }

  return json({ error: 'Not found' }, 404);
}

async function handleAuth(request: Request, env: Env, url: URL): Promise<Response> {
  if (request.method === 'GET' && url.pathname === '/auth/google/start') {
    return redirect(await createGoogleAuthUrl(env, url.searchParams.get('force') === '1'));
  }

  if (request.method === 'GET' && url.pathname === '/auth/google/callback') {
    const error = url.searchParams.get('error');
    if (error) return redirect(`/admin?oauth_error=${encodeURIComponent(error)}`);
    const state = url.searchParams.get('state');
    const code = url.searchParams.get('code');
    if (!state || !code) return redirect('/admin?oauth_error=missing_callback_parameters');

    try {
      const email = await completeGoogleOAuth(env, state, code);
      const token = await signSession(env, email);
      return redirect('/admin?connected=1', {
        'Set-Cookie': sessionCookie(token, 7 * 86400, env.APP_URL.startsWith('https://')),
      });
    } catch (error) {
      return redirect(`/admin?oauth_error=${encodeURIComponent(errorMessage(error))}`);
    }
  }

  return new Response('Not found', { status: 404 });
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);
    try {
      if (url.pathname.startsWith('/api/')) return await handleApi(request, env, url);
      if (url.pathname.startsWith('/auth/')) return await handleAuth(request, env, url);
      return new Response('Not found', { status: 404 });
    } catch (error) {
      console.error(error);
      if (url.pathname.startsWith('/api/')) return json({ error: errorMessage(error) }, 500);
      return new Response(errorMessage(error), { status: 500 });
    }
  },
} satisfies ExportedHandler<Env>;