Build an Automated Booking System with Google Services and Loxone

function onFormSubmit(e) {
  if (!e || !e.namedValues) { Logger.log('Run via form submit (trigger).'); return; }
  try {
    Logger.log('Keys: ' + Object.keys(e.namedValues).join(' | ')); // helps diagnose

    // ---- Read values with fuzzy key matching ----
    const name    = getValFuzzy_(e, ['Full Name','Name']);
    const email   = getValFuzzy_(e, ['Email Address','Email']);
    const room    = getValFuzzy_(e, ['Select Room','Room']);
    const dateStr = getValFuzzy_(e, ['Booking Date','Date']);
    const startStr= getValFuzzy_(e, ['Start Time','Start']);
    const endStr  = getValFuzzy_(e, ['End Time','End']);

    // ---- Build start/end Date objects in the Sheet's timezone ----
    const dateOnly = new Date(dateStr);
    const start = mergeDateAndTime_(dateOnly, startStr);
    const end   = mergeDateAndTime_(dateOnly, endStr);
    if (!(start < end)) throw new Error('End time must be after start time.');

    // ---- Room → Calendar mapping (one line each) ----
    const calendarMap = {
      'Room 1': '6318f6562440f2441310c5686fe9cf4ddc511f915a940e87dc191230096dd142@group.calendar.google.com',
      'Room 2': 'ec7ddd1333d01bd8ed6023f02ff37c4b5016f81a30cc980ed623c9e274638c32@group.calendar.google.com'
    };
    const calId = calendarMap[room] || calendarMap[room.trim()];
    if (!calId) throw new Error('Unknown room: ' + room);

    // ---- Prevent double booking ----
    const cal = CalendarApp.getCalendarById(calId);
    const conflicts = cal.getEvents(start, end);
    if (conflicts.length) {
      Logger.log(`Conflict in ${room}: ${start}–${end}. Skipping.`);
      return;
    }

    // ---- Create event & invite guest ----
    cal.createEvent(name, start, end, { guests: email, sendInvites: true });
    Logger.log(`Created event in ${room}: ${start} → ${end} for ${email}`);

  } catch (err) {
    Logger.log('onFormSubmit error: ' + (err && err.stack ? err.stack : err));
    throw err;
  }
}

/* ===== Helpers ===== */

// fuzzy key lookup: ignores case, spaces, punctuation
function getValFuzzy_(e, aliases) {
  const map = e.namedValues || {};
  const want = aliases.map(norm_);
  for (const key of Object.keys(map)) {
    if (want.includes(norm_(key))) {
      const v = map[key];
      if (v && v[0]) return String(v[0]).trim();
    }
  }
  throw new Error('Missing field (expected one of): ' + aliases.join(', '));
}

function norm_(s) { return String(s).toLowerCase().replace(/[\s_\-:;/\\.,()\[\]{}]+/g,''); }

// Accepts "14:30", "14:30:00", "14", "2:30 PM", "2 PM"
function mergeDateAndTime_(dateOnly, timeStr) {
  const tz = Session.getScriptTimeZone(); // set your Sheet to Europe/London
  const base = new Date(Utilities.formatDate(dateOnly, tz, 'yyyy-MM-dd') + 'T00:00:00');
  const s = String(timeStr).replace(/\s+/g, ' ').trim().toUpperCase();

  // 24h HH or HH:MM or HH:MM:SS
  let m = s.match(/^(\d{1,2})(?::(\d{2}))?(?::(\d{2}))?$/);
  if (m) {
    const h = clamp_(parseInt(m[1],10), 0, 23);
    const min = clamp_(m[2] ? parseInt(m[2],10) : 0, 0, 59);
    base.setHours(h, min, 0, 0);
    return base;
  }

  // 12h (H|H:MM)(:SS)? AM/PM
  m = s.match(/^(\d{1,2})(?::(\d{2}))?(?::(\d{2}))?\s*(AM|PM)$/);
  if (m) {
    let h = clamp_(parseInt(m[1],10), 1, 12);
    const min = clamp_(m[2] ? parseInt(m[2],10) : 0, 0, 59);
    const ampm = m[4];
    if (ampm === 'PM' && h !== 12) h += 12;
    if (ampm === 'AM' && h === 12) h = 0;
    base.setHours(h, min, 0, 0);
    return base;
  }

  // Fallback
  const fallback = new Date(`${dateOnly.toDateString()} ${timeStr}`);
  if (!isNaN(fallback.getTime())) return fallback;

  throw new Error('Unparsable time: ' + timeStr);
}

function clamp_(n, min, max) { return Math.max(min, Math.min(max, n)); }

/******************************************************
 * Loxone Booking Integration — Calendar → Loxone (FINAL++)
 * - CloudDNS (HTTPS, follow redirects)
 * - Token auth first, fallback to Basic over HTTPS
 * - Endpoints used: createuser • assignusertogroup • addoredituser • updateuseraccesscode • deleteuser • getgrouplist
 * - Multi‑room calendars
 * - Group assign FIX: /getgrouplist "value" may be a JSON STRING → parse twice
 *   Maps "Room 1" → "AccessRoom1", "Room 2" → "AccessRoom2" (or use ROOM_GROUPS override)
 * - PIN conflict handling, email, auto-delete after booking
 ******************************************************/

/* ====== SCRIPT PROPERTIES (Project Settings → Script properties) ======
CALENDARS   = [{"id":"<calendarID_room1>","room":"Room 1"}, {"id":"<calendarID_room2>","room":"Room 2"}]
LOX_HOST    = https://dns.loxonecloud.com/504F94A1242E
LOX_USER    = BookingIntegration
LOX_PASS    = Calendar123
// Optional (recommended): ROOM_GROUPS = {"Room 1":"1f3cca00-0299-70ac-ffff51962a78da81","Room 2":"1f3cd3fe-039a-ac0b-ffff51962a78da81"}
================================================================================= */

const PROP         = PropertiesService.getScriptProperties();
const CALENDARS    = JSON.parse(PROP.getProperty('CALENDARS') || '[]');
const ROOM_GROUPS  = safeJson_(PROP.getProperty('ROOM_GROUPS')) || {};
const LOX_HOST     = PROP.getProperty('LOX_HOST');
const LOX_USER     = PROP.getProperty('LOX_USER');
const LOX_PASS     = PROP.getProperty('LOX_PASS');

// Event private props keys
const FIELD_CODE = 'access_code';
const FIELD_UUID = 'loxone_uuid';
const FIELD_PUSH = 'pushed_to_loxone';

// Caches
const CACHE = CacheService.getScriptCache();
const CK_GROUPS = 'groups_cache_json';   // list from getgrouplist (1 day)
const CK_MAP    = 'room_group_map_json'; // room→uuid (1 day)

/* ================= ENTRY POINTS ================= */

function syncUpcoming() {
  const session = loxLogin_();
  if (!session) { Logger.log('Login failed'); return; }

  const now = new Date();
  const future = new Date(now.getTime() + 14 * 86400000);

  for (const cfg of CALENDARS) {
    const cal = CalendarApp.getCalendarById(cfg.id);
    if (!cal) { Logger.log('Calendar not found: ' + cfg.id); continue; }
    const events = cal.getEvents(now, future);
    for (const ev of events) processEvent_(session, ev, cfg.room);
  }
  loxLogout_(session);
}

function cleanupExpired() {
  const session = loxLogin_();
  if (!session) { Logger.log('Login failed'); return; }

  const past = new Date(Date.now() - 7 * 86400000);
  const now  = new Date();

  for (const cfg of CALENDARS) {
    const cal = CalendarApp.getCalendarById(cfg.id);
    if (!cal) { Logger.log('Calendar not found: ' + cfg.id); continue; }
    const events = cal.getEvents(past, now);
    for (const ev of events) cleanupEvent_(session, ev);
  }
  loxLogout_(session);
}

/* ================= EVENT FLOW ================= */

function processEvent_(session, ev, room) {
  try {
    if (ev.isAllDayEvent()) return;

    const props = _getProps(ev);
    if (props[FIELD_PUSH] === 'yes' && props[FIELD_UUID]) return;

    // GROUP
    const grpUuid = resolveGroupForRoom_(session, room); // '' if not found

    // 1) Create user → UUID
    const userName = `${room} ${ev.getStartTime().toISOString()}`;
    const create = loxGetJSON_(session, `/jdev/sps/createuser/${encodeURIComponent(userName)}`);
    const uuid = extractUuid_(create);
    if (!uuid) { Logger.log('Create user returned no UUID'); return; }

    // 2) Assign group (if we have one)
    if (grpUuid) {
      const a = loxGetJSON_(session, `/jdev/sps/assignusertogroup/${encodeURIComponent(uuid)}/${encodeURIComponent(grpUuid)}`);
      Logger.log(`assignusertogroup → LL.code=${getLLCode_(a)} (user ${uuid} → group ${grpUuid})`);
    } else {
      Logger.log('Group not resolved; skipping assignment');
    }

    // 3) Validity + auto-delete
    const startSec = _loxTime(ev.getStartTime());
    const endSec   = _loxTime(ev.getEndTime());
    const payload  = {
      uuid: uuid,
      userState: 4,
      validFrom: startSec,
      validUntil: endSec,
      expirationAction: 1
    };
    loxGetJSON_(session, `/jdev/sps/addoredituser/${encodeURIComponent(JSON.stringify(payload))}`);

    // 4) PIN (retry on 409)
    let code = props[FIELD_CODE] || _generateCode();
    for (let i = 0; i < 6; i++) {
      const set = loxGetJSON_(session, `/jdev/sps/updateuseraccesscode/${encodeURIComponent(uuid)}/${encodeURIComponent(code)}`);
      const status = getLLCode_(set);
      if (status === 200 || status === 201) break;
      if (status === 409) { code = _generateCode(); continue; }
      if (i === 5) { Logger.log('Failed to set PIN'); return; }
      Utilities.sleep(80);
    }

    // Save + notify
    props[FIELD_UUID] = uuid;
    props[FIELD_CODE] = code;
    props[FIELD_PUSH] = 'yes';
    _setProps(ev, props);
    sendPinEmails_(ev, room, code);

    Logger.log(`Provisioned user ${uuid} for "${room}" PIN=${code} group=${grpUuid || 'none'}`);
  } catch (err) {
    Logger.log('processEvent_ error: ' + err);
  }
}

function cleanupEvent_(session, ev) {
  try {
    const props = _getProps(ev);
    if (props[FIELD_PUSH] === 'yes' && props[FIELD_UUID]) {
      loxGetJSON_(session, `/jdev/sps/deleteuser/${encodeURIComponent(props[FIELD_UUID])}`);
      props[FIELD_PUSH] = 'deleted';
      _setProps(ev, props);
      Logger.log('Deleted user ' + props[FIELD_UUID]);
    }
  } catch (err) {
    Logger.log('cleanupEvent_ error: ' + err);
  }
}

/* ================= GROUP RESOLUTION (PARSE TWICE) ================= */

function resolveGroupForRoom_(session, room) {
  // 0) Explicit mapping wins
  if (ROOM_GROUPS && ROOM_GROUPS[room]) return ROOM_GROUPS[room];

  // cache
  const cachedMap = safeJson_(CACHE.get(CK_MAP));
  if (cachedMap && cachedMap[room]) return cachedMap[room];

  const list = getGroupList_(session); // [{name,uuid},...]

  // If your naming is AccessRoom1 / AccessRoom2, infer from room number:
  const num = (room.match(/\d+/) || [''])[0]; // "1" or "2"
  if (num) {
    const wanted = `AccessRoom${num}`;
    const exact = list.find(g => (g.name || '').toLowerCase() === wanted.toLowerCase());
    if (exact && exact.uuid) {
      const map = cachedMap || {};
      map[room] = exact.uuid;
      CACHE.put(CK_MAP, JSON.stringify(map), 86400);
      Logger.log(`Resolved group by exact name "${wanted}" → ${exact.uuid}`);
      return exact.uuid;
    }
  }

  // Fallbacks (rare):
  const roomNorm = (room || '').toLowerCase().replace(/\s+/g,'');
  let found = list.find(g => (g.name || '').toLowerCase().replace(/\s+/g,'') === roomNorm);
  if (!found && num) found = list.find(g => /accessroom\d+/i.test(g.name) && g.name.replace(/[^0-9]/g,'') === num);

  if (found && found.uuid) {
    const map = cachedMap || {};
    map[room] = found.uuid;
    CACHE.put(CK_MAP, JSON.stringify(map), 86400);
          Logger.log(`Resolved group by fallback "${found.name}" → ${found.uuid}`);
    return found.uuid;
  }

  Logger.log(`No matching group for "${room}". Groups: ${list.map(g=>`${g.name}(${g.uuid})`).join(', ')}`);
  return '';
}

function getGroupList_(session) {
  // Cache first
  const cached = safeJson_(CACHE.get(CK_GROUPS));
  if (Array.isArray(cached) && cached.length) return cached;

  const res = loxGetJSON_(session, `/jdev/sps/getgrouplist`);
  // Your box returns: {LL:{value:"[ {...},{...} ]", Code:"200"}}
  let raw = (res && res.LL && typeof res.LL.value !== 'undefined') ? res.LL.value : (res && res.value);
  if (typeof raw === 'string') {
    // parse the JSON string
    try { raw = JSON.parse(raw); } catch (_) { raw = []; }
  }
  const list = (Array.isArray(raw) ? raw : []).map(x => ({
    name: String(x.name || ''),
    uuid: String(x.uuid || '')
  })).filter(x => x.uuid);

  CACHE.put(CK_GROUPS, JSON.stringify(list), 86400);
  Logger.log(`Loaded groups: ${list.map(g=>`${g.name}(${g.uuid})`).join(', ')}`);
  return list;
}

/* ================= AUTH & HTTP ================= */

function loxLogin_() {
  // Token first
  try {
    const url = `${LOX_HOST}/jdev/cfg/apiKeyLogin/${encodeURIComponent(LOX_USER)}/${encodeURIComponent(LOX_PASS)}`;
    const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true, followRedirects: true, validateHttpsCertificates: true });
    const data = safeJson_(res.getContentText());
    if (data && data.LL && data.LL.value) {
      Logger.log('Token login OK');
      return { mode: 'token', token: data.LL.value, base: LOX_HOST };
    }
  } catch (e) {
    Logger.log('Token login failed: ' + e);
  }
  // Basic over HTTPS
  Logger.log('Using Basic auth fallback');
  return { mode: 'basic', basic: 'Basic ' + Utilities.base64Encode(LOX_USER + ':' + LOX_PASS), base: LOX_HOST };
}

function loxGetJSON_(session, path) {
  const res1 = loxGetRaw_(session, path);
  const j1 = safeJson_(res1.body);
  if (j1) return j1;

  Utilities.sleep(120);
  const res2 = loxGetRaw_(session, path);
  const j2 = safeJson_(res2.body);
  if (j2) return j2;

  throw new Error('Invalid JSON from Loxone for ' + path);
}

function loxGetRaw_(session, path) {
  let url = `${session.base}${path}`;
  if (session.mode === 'token') {
    url += (url.includes('?') ? '&' : '?') + 'token=' + encodeURIComponent(session.token);
  }
  const opts = { muteHttpExceptions: true, followRedirects: true, validateHttpsCertificates: true };
  if (session.mode === 'basic') opts.headers = { Authorization: session.basic };

  for (let attempt = 0; attempt < 2; attempt++) {
    try {
      const res = UrlFetchApp.fetch(url, opts);
      Logger.log(`GET ${url} -> ${res.getResponseCode()}`);
      return { body: res.getContentText(), code: res.getResponseCode() };
    } catch (e) {
      if (attempt === 1) throw e;
      Utilities.sleep(100);
    }
  }
  return { body: '', code: 0 };
}

function loxLogout_(session) {
  if (!session || session.mode !== 'token') return;
  try {
    UrlFetchApp.fetch(`${session.base}/jdev/cfg/logout/${encodeURIComponent(session.token)}`, {
      muteHttpExceptions: true, followRedirects: true, validateHttpsCertificates: true
    });
  } catch (_) {}
}

/* ================= EMAIL ====================== */

function sendPinEmails_(ev, room, code) {
  const recipients = getRecipientEmails_(ev);
  if (!recipients.length) return;

  const tz = Session.getScriptTimeZone();
  const start = Utilities.formatDate(ev.getStartTime(), tz, 'yyyy-MM-dd HH:mm');
  const end   = Utilities.formatDate(ev.getEndTime(),   tz, 'yyyy-MM-dd HH:mm');

  const subj = `Access code for ${room}`;
  const body = `Hello,

Your access code for ${room} is: ${code}

Valid (local time):
Start: ${start}
End:   ${end}

The code only works during the booked window. Please don't share it.

Thanks!`;

  for (const to of recipients) {
    try { GmailApp.sendEmail(to, subj, body, { name: 'Bookings (no-reply)' }); } catch (_) {}
  }
}

function getRecipientEmails_(ev) {
  const list = ev.getGuestList().map(g => g.getEmail()).filter(Boolean);
  if (!list.length) {
    const m = (ev.getDescription() || '').match(/email:\s*([^\s,;]+)/i);
    if (m) list.push(m[1]);
  }
  return Array.from(new Set(list));
}

/* ================= HELPERS ==================== */

function _loxTime(date) {
  const base = new Date(Date.UTC(2009, 0, 1));
  return Math.floor((date.getTime() - base.getTime()) / 1000);
}

function _generateCode() {
  // Configurable: Change 4 to 6, 8, etc. for different PIN lengths
  const PIN_LENGTH = 4;
  const maxValue = Math.pow(10, PIN_LENGTH) - 1;
  return ('0'.repeat(PIN_LENGTH) + Math.floor(Math.random() * maxValue)).slice(-PIN_LENGTH);
}

function _getProps(ev) {
  try { return JSON.parse(ev.getTag('privateProperties')) || {}; }
  catch (e) { return {}; }
}

function _setProps(ev, props) {
  ev.setTag('privateProperties', JSON.stringify(props));
}

function safeJson_(txt) {
  try { return JSON.parse(txt); } catch (_) { return null; }
}

function getLLCode_(resp) {
  if (!resp || !resp.LL) return 0;
  if (typeof resp.LL.Code !== 'undefined') return Number(resp.LL.Code);
  if (typeof resp.LL.code !== 'undefined') return Number(resp.LL.code);
  return 0;
}

function extractUuid_(resp) {
  if (!resp || !resp.LL) return '';
  const v = resp.LL.value;
  if (!v) return '';
  if (typeof v === 'string') return v;
  if (v.uuid) return v.uuid;
  return '';
}