`lib/services/bookings.js`

207 lines
// lib/services/bookings.js
//
// Pure booking service. Extracts the slot-conflict-safe transaction pattern
// out of routes/api.js so it can be reused (admin-side reschedule, partner
// API, batch backfills) without going through Express.
//
// Phase 1: NOT yet imported from routes/api.js. Available for Phase 2 cutover.
//
// Errors are thrown as plain Error objects with `.code` set to one of:
//   not_found · installer_not_on_calendar · missing_field · invalid_email
//   invalid_range · past_slot · slot_taken
// The route layer maps these to HTTP statuses.

'use strict';

const db = require('../db');
const bookingToken = require('../booking-token');

const REQUIRED = ['customer_name', 'customer_email', 'scheduled_start', 'scheduled_end'];
const PAID_TIERS = new Set(['pro', 'signature', 'enterprise']);

function err(code, status = 400) {
  const e = new Error(code);
  e.code = code;
  e.status = status;
  return e;
}

// ─────────────────────────────────────────────────────────────────────────
// Lookup
// ─────────────────────────────────────────────────────────────────────────

async function getByUuid(uuid) {
  if (!uuid) return null;
  return db.one(
    `SELECT b.*, i.slug AS installer_slug, i.business_name AS installer_business_name
       FROM bookings b
       JOIN installers i ON i.id = b.installer_id
      WHERE b.uuid = $1`,
    [uuid]
  );
}

async function listForInstaller(installerId, { from, to, limit = 200 } = {}) {
  if (!installerId) return [];
  const fromTs = from || new Date().toISOString();
  const toTs = to || new Date(Date.now() + 90 * 24 * 3600 * 1000).toISOString();
  return db.many(
    `SELECT id, uuid, customer_name, customer_email, project_type, status,
            scheduled_start, scheduled_end, city, state
       FROM bookings
      WHERE installer_id = $1
        AND scheduled_end >= $2
        AND scheduled_start <= $3
      ORDER BY scheduled_start
      LIMIT $4`,
    [installerId, fromTs, toTs, limit]
  );
}

// ─────────────────────────────────────────────────────────────────────────
// Create — race-safe via SELECT … FOR UPDATE on the installer row.
//
// Returns: { id, uuid, view_token, installer }
// Caller is responsible for sending confirmation emails.
// ─────────────────────────────────────────────────────────────────────────

async function createBooking(slug, fields) {
  // 1. Validate installer + tier (no transaction yet).
  const installer = await db.one(
    `SELECT id, slug, business_name, email, tier
       FROM installers WHERE slug = $1 AND status = 'active'`,
    [slug]
  );
  if (!installer) throw err('not_found', 404);
  if (!PAID_TIERS.has(installer.tier)) {
    throw err('installer_not_on_calendar', 402);
  }

  const f = fields || {};
  for (const k of REQUIRED) {
    if (!f[k]) throw err('missing_' + k, 400);
  }
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(f.customer_email))) {
    throw err('invalid_customer_email', 400);
  }

  const start = new Date(f.scheduled_start);
  const end   = new Date(f.scheduled_end);
  if (!(end > start)) throw err('invalid_range', 400);
  if (start < new Date()) throw err('past_slot', 400);

  // 2. Race-safe insert.
  const client = await db.pool.connect();
  let booked;
  try {
    await client.query('BEGIN');
    await client.query('SELECT id FROM installers WHERE id = $1 FOR UPDATE', [installer.id]);

    const conflict = await client.query(
      `SELECT 1 FROM bookings
        WHERE installer_id = $1
          AND status IN ('pending','confirmed')
          AND tstzrange(scheduled_start, scheduled_end) && tstzrange($2::timestamptz, $3::timestamptz)
        LIMIT 1`,
      [installer.id, start.toISOString(), end.toISOString()]
    );
    if (conflict.rowCount > 0) {
      await client.query('ROLLBACK');
      throw err('slot_taken', 409);
    }

    // Per-buyer Google sign-in identity, if attached. Pulled from the express
    // session by the route caller and forwarded as f.consumer_account_id.
    var customerRole = ['homeowner','designer','architect','contractor','property_mgr','other']
      .includes(String(f.customer_role || '').toLowerCase()) ? f.customer_role : null;
    var productSourced = f.product_sourced === true || f.product_sourced === 'true' ? true
                       : f.product_sourced === false || f.product_sourced === 'false' ? false
                       : null;

    const ins = await client.query(
      `INSERT INTO bookings
         (installer_id, customer_name, customer_email, customer_phone,
          project_type, market_segment, material, brand, brand_sku,
          surfaces, rooms, square_feet, roll_count_estimate, ceiling_height_ft,
          surface_state, access_constraints, budget_band,
          address_line1, address_line2, city, state, zip,
          customer_role, product_sourced, consumer_account_id,
          scheduled_start, scheduled_end, customer_notes, status, source)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,'pending','web')
       RETURNING id, uuid`,
      [
        installer.id,
        f.customer_name, f.customer_email, f.customer_phone || null,
        f.project_type || 'consultation', f.market_segment || null, f.material || null, f.brand || null, f.brand_sku || null,
        f.surfaces || null, f.rooms || null,
        f.square_feet ? parseInt(f.square_feet, 10) : null,
        f.roll_count_estimate ? parseInt(f.roll_count_estimate, 10) : null,
        f.ceiling_height_ft ? parseFloat(f.ceiling_height_ft) : null,
        f.surface_state || null, f.access_constraints || null, f.budget_band || null,
        f.address_line1 || null, f.address_line2 || null, f.city || null,
        (f.state || '').toUpperCase() || null, f.zip || null,
        customerRole, productSourced, f.consumer_account_id || null,
        start.toISOString(), end.toISOString(), f.customer_notes || null
      ]
    );

    await client.query('COMMIT');
    booked = ins.rows[0];
  } catch (e) {
    try { await client.query('ROLLBACK'); } catch {}
    throw e;
  } finally {
    client.release();
  }

  return {
    id: booked.id,
    uuid: booked.uuid,
    view_token: bookingToken.sign(booked.uuid),
    installer
  };
}

// ─────────────────────────────────────────────────────────────────────────
// State transitions (installer-scoped — caller must verify ownership)
// ─────────────────────────────────────────────────────────────────────────

async function confirmBooking(bookingId, installerId) {
  const r = await db.query(
    `UPDATE bookings
        SET status = 'confirmed', confirmed_at = now()
      WHERE id = $1 AND installer_id = $2 AND status = 'pending'`,
    [bookingId, installerId]
  );
  return r.rowCount > 0;
}

async function declineBooking(bookingId, installerId, reason = null) {
  const r = await db.query(
    `UPDATE bookings
        SET status = 'declined', canceled_at = now(), cancel_reason = $3
      WHERE id = $1 AND installer_id = $2 AND status IN ('pending','confirmed')`,
    [bookingId, installerId, reason]
  );
  return r.rowCount > 0;
}

async function completeBooking(bookingId, installerId) {
  const r = await db.query(
    `UPDATE bookings
        SET status = 'completed', completed_at = now()
      WHERE id = $1 AND installer_id = $2 AND status = 'confirmed'`,
    [bookingId, installerId]
  );
  return r.rowCount > 0;
}

module.exports = {
  getByUuid,
  listForInstaller,
  createBooking,
  confirmBooking,
  declineBooking,
  completeBooking
};