spq-v2/pb_migrations/1739999000008_align_appointments_schema.js

/// <reference path="../pb_data/types.d.ts" />
//
// M8 — Align appointments with the frontend's structured field model.
//
// Older databases used these fields:
//   appointmentDateTime, duration, type
// and often embedded VIN/duration inside notes.
//
// The current frontend expects first-class fields:
//   date, time, vin, durationMinutes, advisorName, serviceType
//
// This migration adds any missing fields and backfills them from the legacy
// columns while preserving existing data.
//

function ensureTextField(col, name) {
  if (!col.fields.find((f) => f.name === name)) {
    col.fields.push(new TextField({ name, required: false }));
    return true;
  }
  return false;
}

function ensureNumberField(col, name) {
  if (!col.fields.find((f) => f.name === name)) {
    col.fields.push(new NumberField({ name, required: false, min: 0 }));
    return true;
  }
  return false;
}

function splitAppointmentDateTime(value) {
  const raw = String(value || '').trim();
  const match = raw.match(/^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2})/);
  if (match) {
    return { date: match[1], time: match[2] };
  }
  return { date: '', time: '' };
}

function extractVinFromNotes(notes) {
  const match = String(notes || '').match(/(?:^|\|)\s*VIN:\s*([^|]+?)\s*(?=\||$)/i);
  return match?.[1]?.trim().toUpperCase() || '';
}

function extractDurationFromNotes(notes) {
  const match = String(notes || '').match(/(?:^|\|)\s*Duration:\s*(\d+)\s*min\s*(?=\||$)/i);
  return match?.[1] ? Number(match[1]) : null;
}

function stripStructuredAppointmentNotes(notes) {
  return String(notes || '')
    .split('|')
    .map((part) => part.trim())
    .filter((part) => part && !/^VIN:\s*/i.test(part) && !/^Duration:\s*\d+\s*min$/i.test(part))
    .join(' | ');
}

migrate(
  (db) => {
    const col = db.findCollectionByNameOrId('appointments');
    if (!col) {
      console.warn('[M8] appointments collection not found — skipping');
      return;
    }

    let schemaChanged = false;
    schemaChanged = ensureTextField(col, 'date') || schemaChanged;
    schemaChanged = ensureTextField(col, 'time') || schemaChanged;
    schemaChanged = ensureTextField(col, 'vin') || schemaChanged;
    schemaChanged = ensureTextField(col, 'advisorName') || schemaChanged;
    schemaChanged = ensureTextField(col, 'serviceType') || schemaChanged;
    schemaChanged = ensureNumberField(col, 'durationMinutes') || schemaChanged;

    if (schemaChanged) {
      db.save(col);
      console.log('[M8] aligned appointments schema');
    }

    const records = Array.from(db.findRecordsByFilter('appointments', '1=1', 'id', 1000));
    let backfilled = 0;

    for (const rec of records) {
      const notes = String(rec.get('notes') || '');
      const legacyDateTime = String(rec.get('appointmentDateTime') || '');
      const split = splitAppointmentDateTime(legacyDateTime);
      const currentDate = String(rec.get('date') || '').trim();
      const currentTime = String(rec.get('time') || '').trim();
      const currentVin = String(rec.get('vin') || '').trim();
      const currentServiceType = String(rec.get('serviceType') || '').trim();
      const rawDurationMinutes = rec.get('durationMinutes');
      const currentDurationMinutes = rawDurationMinutes == null || rawDurationMinutes === ''
        ? null
        : Number(rawDurationMinutes);
      const legacyDuration = rec.get('duration');
      const numericLegacyDuration = legacyDuration == null || legacyDuration === ''
        ? null
        : Number(legacyDuration);

      let changed = false;

      if (!currentDate && split.date) {
        rec.set('date', split.date);
        changed = true;
      }

      if (!currentTime && split.time) {
        rec.set('time', split.time);
        changed = true;
      }

      if (!currentVin) {
        const noteVin = extractVinFromNotes(notes);
        if (noteVin) {
          rec.set('vin', noteVin);
          changed = true;
        }
      }

      if (!currentServiceType) {
        const legacyType = String(rec.get('type') || '').trim();
        if (legacyType) {
          rec.set('serviceType', legacyType);
          changed = true;
        }
      }

      if (currentDurationMinutes == null || Number.isNaN(currentDurationMinutes) || (currentDurationMinutes <= 0 && numericLegacyDuration > 0)) {
        const noteDuration = extractDurationFromNotes(notes);
        const nextDuration = noteDuration ?? numericLegacyDuration;
        if (nextDuration != null && !Number.isNaN(nextDuration)) {
          rec.set('durationMinutes', nextDuration);
          changed = true;
        }
      }

      const cleanedNotes = stripStructuredAppointmentNotes(notes);
      if (cleanedNotes !== notes.trim()) {
        rec.set('notes', cleanedNotes);
        changed = true;
      }

      if (changed) {
        db.saveRecord(rec);
        backfilled++;
      }
    }

    console.log(`[M8] backfilled ${backfilled} appointment record(s)`);
  },
  (db) => {
    const col = db.findCollectionByNameOrId('appointments');
    if (!col) return;

    for (const name of ['durationMinutes', 'serviceType', 'advisorName', 'vin', 'time', 'date']) {
      const idx = col.fields.findIndex((f) => f.name === name);
      if (idx >= 0) {
        col.fields.splice(idx, 1);
      }
    }

    db.save(col);
  }
);