Figma → Torii via Google Sheets (Enterprise Column Set)

/*******************************
 * Torii – Google Sheets → Custom Integration
 * Version: 2025-11-18
 * Notes:
 *  - Dynamic header mapping (order-agnostic).
 *  - Robust date handling: compare Date objects; format at output (UTC).
 *  - License bucketing by seat_type with fallback "No Seat Type Assigned".
 *  - Roles included: member, admin, guest, provisional (normalized).
 *  - Optional utilities: Validate Sheet, Dry Run.
 *  - Rule: If license is "No Seat Type Assigned" and no lastUsedDate → licenseStatus = "inactive".
 *******************************/

let API_KEY;
let APP_ID;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Torii')
    .addItem('Sync now', 'syncNow')
    .addItem('Dry Run (no upload)', 'dryRun')
    .addSeparator()
    .addItem('Validate Sheet', 'validateSheet')
    .addSeparator()
    .addItem('Reconfigure', 'readConfig')
    .addToUi();
}

function readConfig() {
  const ui = SpreadsheetApp.getUi();
  let result = ui.prompt('Please enter Torii API Key');
  if (result.getSelectedButton() == ui.Button.CANCEL) return;
  API_KEY = result.getResponseText().trim();

  result = ui.prompt('Please enter the APP ID');
  if (result.getSelectedButton() == ui.Button.CANCEL) return;
  APP_ID = result.getResponseText().trim();

  const docProperties = PropertiesService.getDocumentProperties();
  docProperties.setProperty('api_key', API_KEY);
  docProperties.setProperty('app_id', APP_ID);

  ui.alert('Configuration saved.');
}

function syncNow() {
  try {
    ensureConfig();
    const file = buildCustomIntegrationFile(); // throws on validation failures
    const fileId = uploadFile(file);
    syncIntegration(fileId);
    SpreadsheetApp.getUi().alert('Sync completed successfully!');
  } catch (err) {
    Logger.log('Error in syncNow: ' + (err && err.stack ? err.stack : err));
    SpreadsheetApp.getUi().alert('Sync failed: ' + err.message);
  }
}

function dryRun() {
  try {
    ensureConfig(/*allowEmpty=*/true); // allow missing config; no network calls
    const file = buildCustomIntegrationFile();
    const usersCount = file.users.length;
    const licenseCount = file.licenses.length;
    const licensedUsers = file.licenses.reduce((acc, l) => acc + (l.users ? l.users.length : 0), 0);
    Logger.log(JSON.stringify(file, null, 2));
    SpreadsheetApp.getUi().alert(
      'Dry Run complete\n' +
      `Users: ${usersCount}\n` +
      `Licenses: ${licenseCount}\n` +
      `Total Licensed Users: ${licensedUsers}\n\n` +
      'See Logs for details (View → Logs).'
    );
  } catch (err) {
    Logger.log('Error in dryRun: ' + (err && err.stack ? err.stack : err));
    SpreadsheetApp.getUi().alert('Dry Run failed: ' + err.message);
  }
}

function validateSheet() {
  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const { headerMap } = getHeaderMap(sheet);

    const required = ['name', 'email', 'role', 'seat_type'];
    const missing = required.filter(k => headerMap[k] == null);

    const anomalies = analyzeAnomalies(sheet, headerMap);

    let msg = 'Validation results:\n';
    msg += missing.length ? `❌ Missing required headers: ${missing.join(', ')}\n` : '✅ All required headers present.\n';

    if (anomalies.missingEmails > 0)
      msg += `⚠️ Rows missing email: ${anomalies.missingEmails}\n`;
    if (anomalies.unknownSeatTypes.length)
      msg += `⚠️ Unknown seat types: ${Array.from(new Set(anomalies.unknownSeatTypes)).join(', ')}\n`;
    if (anomalies.unknownRoles.length)
      msg += `ℹ️ Unrecognized roles (included as-is): ${Array.from(new Set(anomalies.unknownRoles)).join(', ')}\n`;

    SpreadsheetApp.getUi().alert(msg);
  } catch (err) {
    Logger.log('Error in validateSheet: ' + (err && err.stack ? err.stack : err));
    SpreadsheetApp.getUi().alert('Validate failed: ' + err.message);
  }
}

/* -------------------------
   Core build
------------------------- */

function buildCustomIntegrationFile() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  if (!data || data.length <= 1) {
    throw new Error('No data found in spreadsheet (need a header row + at least one data row).');
  }

  const { headerMap } = getHeaderMap(sheet);

  // Required headers
  const required = ['name', 'email', 'role', 'seat_type'];
  const missing = required.filter(k => headerMap[k] == null);
  if (missing.length) {
    throw new Error('Missing required headers: ' + missing.join(', '));
  }

  // Remove header row
  data.shift();

  const usersOut = [];
  const userSeen = new Set();

  const licensesMap = {}; // licenseName -> [{email, licenseStatus, lastUsedDate?}]

  // --- seat-type specific status rule
  const computeLicenseStatus = (licenseName, lastUsedDate) => {
    if (licenseName === 'No Seat Type Assigned' && !lastUsedDate) {
      return 'inactive';
    }
    return 'active';
  };

  const pushLicenseUser = (licenseName, email, lastUsedDate) => {
    if (!licensesMap[licenseName]) licensesMap[licenseName] = [];
    const licenseStatus = computeLicenseStatus(licenseName, lastUsedDate);
    const entry = { email: email, licenseStatus: licenseStatus };
    if (lastUsedDate) entry.lastUsedDate = lastUsedDate; // only include when present
    licensesMap[licenseName].push(entry);
  };

  // Analyze per-row
  for (let i = 0; i < data.length; i++) {
    const row = data[i];

    const get = (key) => {
      const idx = headerMap[key];
      return idx != null ? (row[idx] !== '' ? row[idx] : null) : null;
    };

    const fullNameRaw = (get('name') || '').toString().trim();
    const emailRaw = (get('email') || '').toString().trim();
    const roleRaw = (get('role') || '').toString().trim();
    const seatTypeRaw = (get('seat_type') || '').toString().trim();

    if (!emailRaw) {
      Logger.log(`Row ${i + 2}: missing email — skipping`);
      continue;
    }
    if (!fullNameRaw) {
      Logger.log(`Row ${i + 2}: missing name (email=${emailRaw}) — using email local part as firstName`);
    }

    // Normalize role & seat type (role value is informational for now)
    const normalizedRole = normalizeRole(roleRaw);
    const { normalized: normalizedSeat, warning: seatWarn } = normalizeSeatType(seatTypeRaw);

    if (seatWarn) Logger.log(`Row ${i + 2} (${emailRaw}): ${seatWarn}`);
    if (normalizedRole.warning) Logger.log(`Row ${i + 2} (${emailRaw}): ${normalizedRole.warning}`);

    // Name split: last token -> lastName; rest -> firstName
    let firstName = '';
    let lastName = '';
    if (fullNameRaw) {
      const parts = fullNameRaw.split(/\s+/);
      if (parts.length === 1) {
        firstName = parts[0];
        lastName = '';
      } else {
        lastName = parts.pop();
        firstName = parts.join(' ');
      }
    } else {
      firstName = (emailRaw.split('@')[0] || '').replace(/[._-]+/g, ' ');
      lastName = '';
    }

    // Dates (compare as Date objects, format only for output)
    const lastSeenFigma  = toDateOrNull(get('last_seen_figma'));
    const lastSeenDev    = toDateOrNull(get('last_seen_dev_mode'));
    const lastSeenJam    = toDateOrNull(get('last_seen_figjam'));
    const lastSeenSlides = toDateOrNull(get('last_seen_slides'));

    const lastEditFigma  = toDateOrNull(get('last_edit_figma'));
    const lastEditJam    = toDateOrNull(get('last_edit_figjam'));
    const lastEditSlides = toDateOrNull(get('last_edit_slides'));

    const figmaUsage     = mostRecentDate(lastSeenFigma, lastEditFigma);
    const figjamUsage    = mostRecentDate(lastSeenJam, lastEditJam);
    const slidesUsage    = mostRecentDate(lastSeenSlides, lastEditSlides);
    const devModeUsage   = mostRecentDate(lastSeenDev);

    const lastUsed       = mostRecentDate(figmaUsage, figjamUsage, slidesUsage, devModeUsage);
    const lastUsedOut    = lastUsed ? formatDateUTC(lastUsed) : null;

    // Users array (dedupe by email)
    if (!userSeen.has(emailRaw.toLowerCase())) {
      userSeen.add(emailRaw.toLowerCase());
      usersOut.push({
        email: emailRaw,
        firstName: firstName,
        lastName: lastName,
        status: 'active'
      });
    }

    // License assignment
    const licenseName = normalizedSeat || 'No Seat Type Assigned';
    pushLicenseUser(licenseName, emailRaw, lastUsedOut);
  }

  // Build licenses array
  const licensesOut = Object.keys(licensesMap).map(name => ({
    name,
    users: licensesMap[name]
  }));

  const file = {
    users: usersOut,
    licenses: licensesOut
  };

  Logger.log(`Build complete: users=${usersOut.length}, licenses=${licensesOut.length}`);
  return file;
}

/* -------------------------
   Upload & Sync (Torii API)
------------------------- */

function syncIntegration(fileId) {
  const resp = UrlFetchApp.fetch('https://api.toriihq.com/v1.0/services/sync/custom', {
    muteHttpExceptions: false,
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify({
      idFile: fileId,
      idAppAccount: APP_ID
    }),
    headers: { Authorization: `Bearer ${API_KEY}` }
  });
  Logger.log(resp.getResponseCode() + ' ' + resp.getContentText());
}

function uploadFile(file) {
  // 1) Get signed URL
  let response = UrlFetchApp.fetch('https://api.toriihq.com/v1.0/files/url?name=custom_integration.json&type=customIntegrationData', {
    headers: { Authorization: `Bearer ${API_KEY}` }
  });
  let json = JSON.parse(response.getContentText());
  const s3url = json.uploadFileParameters.url;
  const filePath = json.uploadFileParameters.filePath;

  // 2) PUT to S3 (use a real MIME type)
  response = UrlFetchApp.fetch(s3url, {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(file)
  });

  // 3) Register file
  response = UrlFetchApp.fetch('https://api.toriihq.com/v1.0/files', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: `Bearer ${API_KEY}` },
    payload: JSON.stringify({
      path: filePath,
      type: 'customIntegrationData'
    })
  });

  json = JSON.parse(response.getContentText());
  const fileId = json.id;
  Logger.log('Uploaded fileId: ' + fileId);
  return fileId;
}

/* -------------------------
   Helpers – headers & dates
------------------------- */

// Build a normalized header map: logicalKey -> column index
function getHeaderMap(sheet) {
  const values = sheet.getDataRange().getValues();
  if (!values || values.length === 0) throw new Error('Sheet appears empty.');
  const rawHeaders = values[0].map(h => (h == null ? '' : String(h)));

  // Normalize a header (lowercase, trim, collapse spaces, convert to underscores)
  const norm = (s) => s.toLowerCase().trim().replace(/\s+/g, '_').replace(/[^a-z0-9_]/g, '_');

  const normalized = rawHeaders.map(h => norm(h));
  const idxByHeader = {};
  normalized.forEach((h, i) => { idxByHeader[h] = i; });

  // Map logical keys to likely header variants
  const findCol = (aliases) => {
    for (const a of aliases) {
      if (idxByHeader[a] != null) return idxByHeader[a];
    }
    return null;
  };

  const headerMap = {
    // required
    name: findCol(['name', 'full_name']),
    email: findCol(['email', 'e_mail']),
    role: findCol(['role', 'user_role']),
    seat_type: findCol(['seat_type', 'seat', 'license', 'seattype']),
    // optional (used when present)
    paid_seat_date_assigned: findCol(['paid_seat_date_assigned']),
    job_title: findCol(['job_title', 'title']),
    date_added: findCol(['date_added']),
    figma_date_upgraded: findCol(['figma_date_upgraded']),
    figjam_date_upgraded: findCol(['figjam_date_upgraded']),
    dev_mode_date_upgraded: findCol(['dev_mode_date_upgraded']),
    last_seen_figma: findCol(['last_seen_figma']),
    last_seen_dev_mode: findCol(['last_seen_dev_mode', 'last_seen_dev']),
    last_seen_figjam: findCol(['last_seen_figjam']),
    last_seen_slides: findCol(['last_seen_slides']),
    last_edit_figma: findCol(['last_edit_figma']),
    last_edit_figjam: findCol(['last_edit_figjam']),
    last_edit_slides: findCol(['last_edit_slides']),
    scim_billing_model_schema: findCol(['scim_billing_model_schema'])
  };

  return { headerMap };
}

// Convert any cell value to Date or null (logs invalids)
function toDateOrNull(value) {
  if (value == null || value === '') return null;

  // Accept Date objects (from Sheets) directly
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return isNaN(value.getTime()) ? null : value;
  }

  // Try parsing strings/numbers
  const s = String(value).trim();
  if (!s) return null;

  const d = new Date(s);
  if (isNaN(d.getTime())) {
    // Try DD/MM/YYYY or MM/DD/YYYY variants as a fallback
    const m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2,4})$/);
    if (m) {
      const dd = parseInt(m[1], 10);
      const mm = parseInt(m[2], 10) - 1;
      const yyyy = parseInt(m[3].length === 2 ? '20' + m[3] : m[3], 10);
      const alt = new Date(Date.UTC(yyyy, mm, dd));
      return isNaN(alt.getTime()) ? null : alt;
    }
    Logger.log('Invalid date: ' + s);
    return null;
  }
  return d;
}

// Return most recent (max) of Date args; null if none
function mostRecentDate() {
  const dates = Array.prototype.slice.call(arguments).filter(Boolean);
  if (!dates.length) return null;
  let maxTs = -Infinity;
  let maxDate = null;
  for (const d of dates) {
    const ts = d.getTime();
    if (!isNaN(ts) && ts > maxTs) {
      maxTs = ts;
      maxDate = d;
    }
  }
  return maxDate;
}

// Format to YYYY-MM-dd UTC
function formatDateUTC(date) {
  if (!date) return null;
  return Utilities.formatDate(date, 'UTC', 'yyyy-MM-dd');
}

/* -------------------------
   Helpers – normalization & analysis
------------------------- */

function normalizeSeatType(seatRaw) {
  if (!seatRaw) return { normalized: null, warning: null };
  const v = seatRaw.toString().trim().toLowerCase();

  // Known synonyms → canonical
  const synonyms = {
    'viewer': 'view',
    'read': 'view',
    'dev mode': 'dev',
    'developer': 'dev',
    'collaborator': 'collab',
    'full seat': 'full'
  };

  const allowed = new Set(['view', 'dev', 'collab', 'full', 'empty']);

  const mapped = synonyms[v] || v;

  if (mapped === '' || mapped == null) return { normalized: null, warning: null };

  if (allowed.has(mapped)) return { normalized: mapped, warning: null };

  // Unknown value: pass-through but warn
  return {
    normalized: mapped, // used as license name so you can spot upstream data issues
    warning: `Unknown seat_type "${seatRaw}" → using "${mapped}" as license name`
  };
}

function normalizeRole(roleRaw) {
  if (!roleRaw) return { value: null, warning: null };
  const v = roleRaw.toString().trim().toLowerCase();
  const allowed = new Set(['member', 'admin', 'guest', 'provisional']);
  if (allowed.has(v)) return { value: v, warning: null };
  return { value: v, warning: `Unrecognized role "${roleRaw}" (included as-is)` };
}

function analyzeAnomalies(sheet, headerMap) {
  const data = sheet.getDataRange().getValues();
  if (!data || data.length <= 1) return { missingEmails: 0, unknownSeatTypes: [], unknownRoles: [] };

  const rows = data.slice(1);
  let missingEmails = 0;
  const unknownSeatTypes = [];
  const unknownRoles = [];

  const seatIdx = headerMap.seat_type;
  const roleIdx = headerMap.role;
  const emailIdx = headerMap.email;

  const allowedSeat = new Set(['view', 'dev', 'collab', 'full', 'empty']);
  const roleAllowed = new Set(['member', 'admin', 'guest', 'provisional']);

  rows.forEach((row, i) => {
    const email = emailIdx != null ? String(row[emailIdx] || '').trim() : '';
    if (!email) missingEmails++;

    if (seatIdx != null) {
      const raw = String(row[seatIdx] || '').trim();
      if (raw) {
        const { normalized } = normalizeSeatType(raw);
        if (normalized && !allowedSeat.has(normalized) &&
            normalized !== 'No Seat Type Assigned') {
          unknownSeatTypes.push(raw);
        }
      }
    }
    if (roleIdx != null) {
      const rawR = String(row[roleIdx] || '').trim();
      if (rawR) {
        const r = rawR.toLowerCase();
        if (!roleAllowed.has(r)) unknownRoles.push(rawR);
      }
    }
  });

  return { missingEmails, unknownSeatTypes, unknownRoles };
}

/* -------------------------
   Config helpers
------------------------- */

function ensureConfig(allowEmpty) {
  let docProperties = PropertiesService.getDocumentProperties();
  API_KEY = docProperties.getProperty('api_key');
  APP_ID  = docProperties.getProperty('app_id');

  if (!API_KEY || !APP_ID) {
    if (allowEmpty) return; // for Dry Run without network
    readConfig(); // will prompt
    docProperties = PropertiesService.getDocumentProperties();
    API_KEY = docProperties.getProperty('api_key');
    APP_ID  = docProperties.getProperty('app_id');

    if (!API_KEY || !APP_ID) {
      throw new Error('Missing API Key or App ID; please run "Reconfigure" from the Torii menu.');
    }
  }
}