`src/server/appointments/slots.ts`

130 lines
/**
 * Slot calculator — converts appt_availability rows + busy windows
 * (DB-booked + Google-Calendar busy) into the array of bookable
 * {slot_start, slot_end} ISO pairs for a given date.
 *
 * The owner's local timezone is assumed America/Los_Angeles for v1.
 * (Steve's universe is in PT; a future migration adds a tz column.)
 */
import { query } from '../../../db/pool.ts';
import { busy as gcalBusy } from './calendar.ts';

const OWNER_TZ = 'America/Los_Angeles';

interface AvailRow {
  day_of_week:       number;
  start_min:         number;
  end_min:           number;
  slot_duration_min: number;
}

interface DbBookedRow { slot_start: string; slot_end: string; }

/** Compute the UTC ISO instant for a given local-PT date at H:M minutes-from-midnight. */
function ptLocalToUTC(dateYYYYMMDD: string, minutesFromMidnight: number): Date {
  const hour   = Math.floor(minutesFromMidnight / 60);
  const minute = minutesFromMidnight % 60;
  // We compute the offset for the given local date by formatting an arbitrary
  // instant in the target tz and reading back its offset. This is the cleanest
  // pure-stdlib way to handle DST in Node without a deps tax.
  const parts = new Intl.DateTimeFormat('en-US', {
    timeZone:    OWNER_TZ,
    timeZoneName:'longOffset',
    year: 'numeric', month: '2-digit', day: '2-digit',
    hour: '2-digit', minute: '2-digit', second: '2-digit',
    hour12: false,
  }).formatToParts(new Date(`${dateYYYYMMDD}T${String(hour).padStart(2,'0')}:${String(minute).padStart(2,'0')}:00Z`));
  const tzPart = parts.find(p => p.type === 'timeZoneName')?.value || 'GMT-08:00';
  // tzPart looks like "GMT-07:00" or "GMT-08:00"
  const m = tzPart.match(/GMT([+-])(\d{2}):?(\d{2})/);
  const offsetMin = m ? (m[1] === '-' ? -1 : 1) * (parseInt(m[2],10)*60 + parseInt(m[3],10)) : -480;
  // Now construct: the local wall-clock instant is YYYY-MM-DDTHH:MM:00 in PT.
  // UTC instant = local - offset.
  const localMs = Date.UTC(
    parseInt(dateYYYYMMDD.slice(0,4),10),
    parseInt(dateYYYYMMDD.slice(5,7),10) - 1,
    parseInt(dateYYYYMMDD.slice(8,10),10),
    hour, minute, 0, 0,
  );
  return new Date(localMs - offsetMin * 60_000);
}

/** Day-of-week for a YYYY-MM-DD interpreted in PT. */
function dayOfWeekPT(dateYYYYMMDD: string): number {
  // Use noon-PT so DST edge cases don't bump the date.
  const noon = ptLocalToUTC(dateYYYYMMDD, 12 * 60);
  return new Date(noon).getUTCDay();   // Sun=0..Sat=6 in UTC; since we used noon, == PT day
}

export interface Slot {
  slot_start: string;   // ISO 8601 UTC
  slot_end:   string;   // ISO 8601 UTC
}

export async function computeSlots(
  ownerEmail: string,
  dateYYYYMMDD: string,
): Promise<Slot[]> {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(dateYYYYMMDD)) return [];

  const dow = dayOfWeekPT(dateYYYYMMDD);
  const av = await query<AvailRow>(
    `SELECT day_of_week, start_min, end_min, slot_duration_min
       FROM appt_availability
      WHERE owner_email = $1 AND day_of_week = $2
      ORDER BY start_min ASC`,
    [ownerEmail, dow],
  );
  if (!av.rowCount) return [];

  // Build candidate slots in UTC
  const candidates: Slot[] = [];
  for (const row of av.rows) {
    for (let m = row.start_min; m + row.slot_duration_min <= row.end_min; m += row.slot_duration_min) {
      const s = ptLocalToUTC(dateYYYYMMDD, m);
      const e = ptLocalToUTC(dateYYYYMMDD, m + row.slot_duration_min);
      candidates.push({ slot_start: s.toISOString(), slot_end: e.toISOString() });
    }
  }
  if (!candidates.length) return [];

  // Window for busy lookups = the full local day
  const dayStart = ptLocalToUTC(dateYYYYMMDD, 0).toISOString();
  const dayEnd   = ptLocalToUTC(dateYYYYMMDD, 24 * 60).toISOString();

  // 1. DB-booked slots (active = confirmed | pending; cancelled ignored)
  const booked = await query<DbBookedRow>(
    `SELECT slot_start, slot_end FROM appt_appointments
      WHERE owner_email = $1
        AND status IN ('confirmed','pending')
        AND slot_start < $3 AND slot_end > $2`,
    [ownerEmail, dayStart, dayEnd],
  );

  // 2. Google Calendar busy windows (if owner has connected calendar)
  let gcal: { start: string; end: string }[] = [];
  try {
    gcal = await gcalBusy(ownerEmail, dayStart, dayEnd);
  } catch (e) {
    console.error('[appointments] gcalBusy threw, treating as empty:', e);
  }

  const busyWindows: { s: number; e: number }[] = [
    ...booked.rows.map(b => ({ s: Date.parse(b.slot_start), e: Date.parse(b.slot_end) })),
    ...gcal.map(b      => ({ s: Date.parse(b.start),      e: Date.parse(b.end)       })),
  ];

  // 3. Also drop any slot in the past
  const now = Date.now();

  return candidates.filter(c => {
    const cs = Date.parse(c.slot_start);
    const ce = Date.parse(c.slot_end);
    if (cs < now) return false;
    for (const b of busyWindows) {
      if (cs < b.e && ce > b.s) return false;   // overlap
    }
    return true;
  });
}