/**
 * Maintenance request tracker + weekly summary draft (Google Sheets + Apps Script)
 * Sample project with placeholder data, not a real client.
 * "Sample Property Care" is a sample business.
 *
 * What it does
 *  1. onFormSubmit: every Google Form response gets a request ID, an SLA due time (by urgency)
 *     and an assignee (by category, from the Settings tab), and is added to the Tracker tab.
 *  2. onEdit: when staff change a Status, it stamps "Last update" and "Completed at"
 *     and refreshes that row's SLA flag.
 *  3. refreshTracker (daily, 7am): recalculates "Days open" and the SLA flag for every row.
 *  4. weeklySummary (Mondays, 8am): adds a row to the "Weekly summary" tab and creates a Gmail
 *     DRAFT for the operations manager. Nothing is sent automatically; a person reviews and sends it.
 *
 * Setup: open the response spreadsheet > Extensions > Apps Script, paste this file and
 * appsscript.json, then run setup() once and approve the permissions.
 */

const CONFIG = {
  RESPONSES_SHEET: 'Form responses 1',
  TRACKER_SHEET: 'Tracker',
  SUMMARY_SHEET: 'Weekly summary',
  SETTINGS_SHEET: 'Settings',
  ID_PREFIX: 'CPC-',
  STATUSES: ['New', 'Scheduled', 'In progress', 'Waiting on parts', 'Completed', 'Cancelled'],
  CLOSED: ['Completed', 'Cancelled'],
  SLA_HOURS: { Emergency: 4, Urgent: 24, Routine: 120 },
  DAILY_REFRESH_HOUR: 7,
  WEEKLY_SUMMARY_HOUR: 8,
};

// Tracker columns (1-based). Change the order here if the sheet layout changes.
const COL = {
  ID: 1, RECEIVED: 2, TENANT: 3, EMAIL: 4, PHONE: 5, PROPERTY: 6, CATEGORY: 7, URGENCY: 8,
  DESCRIPTION: 9, ENTRY_OK: 10, ASSIGNED: 11, STATUS: 12, DUE: 13, SCHEDULED: 14,
  COMPLETED: 15, DAYS_OPEN: 16, SLA: 17, LAST_UPDATE: 18,
};
const TRACKER_HEADERS = ['Request ID', 'Received', 'Tenant', 'Email', 'Phone', 'Property', 'Category', 'Urgency',
  'Description', 'Entry OK?', 'Assigned to', 'Status', 'Due by', 'Scheduled for', 'Completed at', 'Days open',
  'SLA', 'Last update'];
const SUMMARY_HEADERS = ['Week starting', 'Received', 'Emergency', 'Urgent', 'Routine', 'Completed',
  'SLA met %', 'Avg days to complete', 'Open at run', 'Overdue at run', 'Draft created'];

/* ---------- menu ---------- */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Maintenance tools')
    .addItem('Refresh tracker now', 'refreshTracker')
    .addItem('Create weekly summary draft now', 'weeklySummary')
    .addSeparator()
    .addItem('Run setup (sheets + triggers)', 'setup')
    .addToUi();
}

/* ---------- one-time setup ---------- */

function setup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tracker = getOrCreateSheet_(ss, CONFIG.TRACKER_SHEET, TRACKER_HEADERS);
  getOrCreateSheet_(ss, CONFIG.SUMMARY_SHEET, SUMMARY_HEADERS);
  const settings = ss.getSheetByName(CONFIG.SETTINGS_SHEET) || ss.insertSheet(CONFIG.SETTINGS_SHEET);
  if (settings.getLastRow() === 0) {
    settings.getRange(1, 1, 4, 2).setValues([
      ['Setting', 'Value'],
      ['Summary to', 'operations@sample-property.example'],
      ['Summary cc', 'office@sample-property.example'],
      ['Business name', 'Sample Property Care'],
    ]);
    settings.getRange(1, 4, 7, 2).setValues([
      ['Category', 'Assigned to'],
      ['Plumbing', 'Rafe M. (plumber)'],
      ['Electrical', 'Noor S. (electrician)'],
      ['Heating & cooling', 'Ivan P. (HVAC)'],
      ['Appliances', 'Ivan P. (HVAC)'],
      ['Locks & security', 'Dan T. (handyman)'],
      ['General repairs', 'Dan T. (handyman)'],
    ]);
    settings.getRange(1, 1, 1, 5).setFontWeight('bold');
  }

  // Status drop-down and formats on the tracker
  const rows = tracker.getMaxRows() - 1;
  const statusRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(CONFIG.STATUSES, true).setAllowInvalid(false).build();
  tracker.getRange(2, COL.STATUS, rows, 1).setDataValidation(statusRule);
  tracker.getRange(2, COL.RECEIVED, rows, 1).setNumberFormat('dd/mm/yyyy hh:mm');
  tracker.getRange(2, COL.DUE, rows, 3).setNumberFormat('dd/mm/yyyy hh:mm');
  tracker.getRange(2, COL.LAST_UPDATE, rows, 1).setNumberFormat('dd/mm/yyyy hh:mm');
  const slaRange = tracker.getRange(2, COL.SLA, rows, 1);
  tracker.setConditionalFormatRules([
    SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('Overdue').setBackground('#f4c7c3').setFontColor('#a50e0e').setRanges([slaRange]).build(),
    SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('Missed').setBackground('#fce8b2').setRanges([slaRange]).build(),
    SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('Met').setBackground('#d9ead3').setRanges([slaRange]).build(),
  ]);

  // Triggers: remove any old copies first so setup() can be re-run safely
  const handlers = ['onFormSubmit', 'refreshTracker', 'weeklySummary'];
  ScriptApp.getProjectTriggers()
    .filter(t => handlers.indexOf(t.getHandlerFunction()) !== -1)
    .forEach(t => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('onFormSubmit').forSpreadsheet(ss).onFormSubmit().create();
  ScriptApp.newTrigger('refreshTracker').timeBased().everyDays(1).atHour(CONFIG.DAILY_REFRESH_HOUR).create();
  ScriptApp.newTrigger('weeklySummary').timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(CONFIG.WEEKLY_SUMMARY_HOUR).create();
  Logger.log('Setup complete: 3 triggers installed.');
}

/* ---------- 1. new form response ---------- */

function onFormSubmit(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000); // two responses at the same second must not get the same ID
  try {
    const v = name => ((e.namedValues[name] || [''])[0] || '').toString().trim();
    const received = parseTimestamp_(v('Timestamp')) || new Date();
    const urgency = CONFIG.SLA_HOURS[v('Urgency')] ? v('Urgency') : 'Routine';
    const category = v('Category');
    const id = nextRequestId_();
    const due = new Date(received.getTime() + CONFIG.SLA_HOURS[urgency] * 3600 * 1000);

    const row = [];
    row[COL.ID - 1] = id;
    row[COL.RECEIVED - 1] = received;
    row[COL.TENANT - 1] = v('Your name');
    row[COL.EMAIL - 1] = v('Email address');
    row[COL.PHONE - 1] = v('Best contact phone');
    row[COL.PROPERTY - 1] = v('Property address');
    row[COL.CATEGORY - 1] = category;
    row[COL.URGENCY - 1] = urgency;
    row[COL.DESCRIPTION - 1] = v('Describe the problem');
    row[COL.ENTRY_OK - 1] = v("Can we enter if you're not home?").indexOf('Yes') === 0 ? 'Yes' : 'Call first';
    row[COL.ASSIGNED - 1] = assigneeFor_(category);
    row[COL.STATUS - 1] = 'New';
    row[COL.DUE - 1] = due;
    row[COL.SCHEDULED - 1] = '';
    row[COL.COMPLETED - 1] = '';
    row[COL.DAYS_OPEN - 1] = 0;
    row[COL.SLA - 1] = 'On track';
    row[COL.LAST_UPDATE - 1] = received;

    const tracker = sheet_(CONFIG.TRACKER_SHEET);
    tracker.appendRow(row);
    if (urgency === 'Emergency') {
      tracker.getRange(tracker.getLastRow(), COL.ID, 1, COL.URGENCY).setBackground('#fde7e9');
    }
    Logger.log('%s added: %s, %s, %s, assigned to %s', id, urgency, category, row[COL.PROPERTY - 1], row[COL.ASSIGNED - 1]);
  } finally {
    lock.releaseLock();
  }
}

/* ---------- 2. staff edit a status ---------- */

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() !== CONFIG.TRACKER_SHEET || e.range.getRow() < 2) return;
  const col = e.range.getColumn();
  if (col !== COL.STATUS && col !== COL.SCHEDULED) return;
  const r = e.range.getRow();
  const now = new Date();
  const status = sh.getRange(r, COL.STATUS).getValue();
  if (col === COL.STATUS) {
    const completedCell = sh.getRange(r, COL.COMPLETED);
    if (status === 'Completed' && !completedCell.getValue()) completedCell.setValue(now);
    if (status !== 'Completed' && completedCell.getValue()) completedCell.setValue(''); // re-opened
  }
  sh.getRange(r, COL.LAST_UPDATE).setValue(now);
  const values = sh.getRange(r, 1, 1, TRACKER_HEADERS.length).getValues()[0];
  sh.getRange(r, COL.DAYS_OPEN, 1, 2).setValues([[daysOpen_(values, now), slaFlag_(values, now)]]);
}

/* ---------- 3. daily refresh ---------- */

function refreshTracker() {
  const sh = sheet_(CONFIG.TRACKER_SHEET);
  const n = sh.getLastRow() - 1;
  if (n < 1) return;
  const now = new Date();
  const data = sh.getRange(2, 1, n, TRACKER_HEADERS.length).getValues();
  const out = data.map(row => [daysOpen_(row, now), slaFlag_(row, now)]);
  sh.getRange(2, COL.DAYS_OPEN, n, 2).setValues(out); // one write for the whole column pair
  const overdue = out.filter(o => o[1] === 'Overdue').length;
  Logger.log('Tracker refreshed: %s rows, %s overdue', n, overdue);
}

/* ---------- 4. weekly summary draft ---------- */

function weeklySummary() {
  refreshTracker();
  const tz = Session.getScriptTimeZone();
  const now = new Date();
  const weekEnd = startOfWeek_(now);                       // Monday 00:00 this week
  const weekStart = new Date(weekEnd.getFullYear(), weekEnd.getMonth(), weekEnd.getDate() - 7); // calendar maths, safe across daylight-saving changes
  const inWeek = d => d instanceof Date && d >= weekStart && d < weekEnd;

  const sh = sheet_(CONFIG.TRACKER_SHEET);
  const n = sh.getLastRow() - 1;
  const rows = n > 0 ? sh.getRange(2, 1, n, TRACKER_HEADERS.length).getValues() : [];
  const received = rows.filter(r => inWeek(r[COL.RECEIVED - 1]));
  const completed = rows.filter(r => r[COL.STATUS - 1] === 'Completed' && inWeek(r[COL.COMPLETED - 1]));
  const open = rows.filter(r => CONFIG.CLOSED.indexOf(r[COL.STATUS - 1]) === -1);
  const overdue = open.filter(r => r[COL.SLA - 1] === 'Overdue')
    .sort((a, b) => a[COL.DUE - 1] - b[COL.DUE - 1]);
  const waiting = open.filter(r => r[COL.STATUS - 1] === 'Waiting on parts');
  const met = completed.filter(r => r[COL.COMPLETED - 1] <= r[COL.DUE - 1]).length;
  const slaPct = completed.length ? met / completed.length : null;
  const avgDays = completed.length
    ? completed.reduce((s, r) => s + (r[COL.COMPLETED - 1] - r[COL.RECEIVED - 1]), 0) / completed.length / 86400000
    : null;
  const countBy = (list, col) => list.reduce((m, r) => { m[r[col - 1]] = (m[r[col - 1]] || 0) + 1; return m; }, {});
  const byUrgency = countBy(received, COL.URGENCY);
  const byCategory = countBy(received, COL.CATEGORY);

  // Settings
  const settings = settingsMap_();
  const label = Utilities.formatDate(weekStart, tz, 'EEE d MMM') + ' to ' +
    Utilities.formatDate(new Date(weekEnd.getTime() - 1), tz, 'EEE d MMM yyyy');
  const subject = settings['Business name'] + ' maintenance summary: ' + label;

  const fmtPct = p => p === null ? 'n/a' : Math.round(p * 100) + '%';
  const fmtDays = d => d === null ? 'n/a' : d.toFixed(1) + ' days';
  const when = d => Utilities.formatDate(d, tz, 'EEE d MMM HH:mm');
  const catLines = Object.keys(byCategory).sort((a, b) => byCategory[b] - byCategory[a] || a.localeCompare(b));

  const text = [
    'Hi team,', '',
    'Maintenance summary for ' + label + '.', '',
    'New requests: ' + received.length + ' (' + ['Emergency', 'Urgent', 'Routine'].map(u => (byUrgency[u] || 0) + ' ' + u.toLowerCase()).join(', ') + ')',
    'Completed: ' + completed.length + ' (' + met + ' within SLA, ' + fmtPct(slaPct) + ')',
    'Average time to complete: ' + fmtDays(avgDays),
    'Open now: ' + open.length + ', of which ' + overdue.length + ' overdue and ' + waiting.length + ' waiting on parts', '',
    'New requests by category:',
    ...catLines.map(c => '  - ' + c + ': ' + byCategory[c]), '',
    'Overdue (oldest due first):',
    ...(overdue.length ? overdue.map(r => '  - ' + r[COL.ID - 1] + ' | ' + r[COL.PROPERTY - 1] + ' | ' + r[COL.CATEGORY - 1] +
      ' | ' + r[COL.URGENCY - 1] + ' | due ' + when(r[COL.DUE - 1]) + ' | ' + r[COL.STATUS - 1] + ' | ' + r[COL.ASSIGNED - 1]) : ['  - none']), '',
    'The full list is on the Tracker tab.', '',
    '(Draft created automatically on ' + when(now) + '. Check before sending.)',
  ].join('\n');

  const cell = 'padding:6px 10px;border-bottom:1px solid #e0e0e0;';
  const html =
    '<div style="font-family:Arial,sans-serif;font-size:14px;color:#202124">' +
    '<p>Hi team,</p><p>Maintenance summary for <b>' + label + '</b>.</p>' +
    '<table style="border-collapse:collapse;margin:8px 0 14px">' +
    [['New requests', received.length + ' &nbsp;<span style="color:#5f6368">(' + ['Emergency', 'Urgent', 'Routine'].map(u => (byUrgency[u] || 0) + ' ' + u.toLowerCase()).join(', ') + ')</span>'],
     ['Completed', completed.length + ' &nbsp;<span style="color:#5f6368">(' + met + ' within SLA)</span>'],
     ['SLA met', fmtPct(slaPct)], ['Average time to complete', fmtDays(avgDays)],
     ['Open now', open.length], ['Overdue now', '<b style="color:#a50e0e">' + overdue.length + '</b>'], ['Waiting on parts', waiting.length]]
      .map(kv => '<tr><td style="' + cell + 'color:#5f6368">' + kv[0] + '</td><td style="' + cell + 'font-weight:bold">' + kv[1] + '</td></tr>').join('') +
    '</table><p style="margin-bottom:4px"><b>New requests by category</b></p><p style="margin-top:0">' +
    catLines.map(c => esc_(c) + ': ' + byCategory[c]).join(' &middot; ') + '</p>' +
    '<p style="margin-bottom:4px"><b>Overdue, oldest due first</b></p>' +
    (overdue.length ? '<table style="border-collapse:collapse;font-size:13px"><tr>' +
      ['ID', 'Property', 'Category', 'Urgency', 'Due', 'Status', 'Assigned to'].map(h => '<th style="' + cell + 'text-align:left;background:#f1f3f4">' + h + '</th>').join('') + '</tr>' +
      overdue.map(r => '<tr>' + [r[COL.ID - 1], r[COL.PROPERTY - 1], r[COL.CATEGORY - 1], r[COL.URGENCY - 1], when(r[COL.DUE - 1]), r[COL.STATUS - 1], r[COL.ASSIGNED - 1]]
        .map(x => '<td style="' + cell + 'white-space:nowrap">' + esc_(x) + '</td>').join('') + '</tr>').join('') + '</table>' : '<p>None.</p>') +
    '<p>The full list is on the Tracker tab.</p>' +
    '<p style="color:#5f6368;font-size:12px">Draft created automatically on ' + when(now) + '. Check before sending.</p></div>';

  const draft = GmailApp.createDraft(settings['Summary to'], subject, text, { htmlBody: html, cc: settings['Summary cc'] || '' });

  sheet_(CONFIG.SUMMARY_SHEET).appendRow([weekStart, received.length, byUrgency.Emergency || 0, byUrgency.Urgent || 0,
    byUrgency.Routine || 0, completed.length, slaPct === null ? '' : slaPct, avgDays === null ? '' : Math.round(avgDays * 10) / 10,
    open.length, overdue.length, now]);
  Logger.log('Weekly summary draft created (%s): %s', draft.getId(), subject);
  return draft.getId();
}

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

function slaFlag_(row, now) {
  const status = row[COL.STATUS - 1], due = row[COL.DUE - 1], done = row[COL.COMPLETED - 1];
  if (status === 'Cancelled') return '';
  if (status === 'Completed') return done instanceof Date && done <= due ? 'Met' : 'Missed';
  return now > due ? 'Overdue' : 'On track';
}

function daysOpen_(row, now) {
  const start = row[COL.RECEIVED - 1];
  if (!(start instanceof Date)) return '';
  const end = row[COL.STATUS - 1] === 'Completed' && row[COL.COMPLETED - 1] instanceof Date ? row[COL.COMPLETED - 1] : now;
  if (row[COL.STATUS - 1] === 'Cancelled') return '';
  return Math.round((end - start) / 86400000 * 10) / 10;
}

function nextRequestId_() {
  const props = PropertiesService.getScriptProperties();
  const n = Number(props.getProperty('LAST_REQUEST_NO') || 0) + 1;
  props.setProperty('LAST_REQUEST_NO', String(n));
  return CONFIG.ID_PREFIX + ('000' + n).slice(-4);
}

function assigneeFor_(category) {
  const sh = sheet_(CONFIG.SETTINGS_SHEET);
  const map = sh.getRange(2, 4, Math.max(sh.getLastRow() - 1, 1), 2).getValues();
  const hit = map.filter(r => r[0] === category)[0];
  return hit ? hit[1] : 'Office (unassigned)';
}

function settingsMap_() {
  const sh = sheet_(CONFIG.SETTINGS_SHEET);
  return sh.getRange(2, 1, Math.max(sh.getLastRow() - 1, 1), 2).getValues()
    .reduce((m, r) => { if (r[0]) m[r[0]] = r[1]; return m; }, {});
}

function startOfWeek_(d) {
  const x = new Date(d.getFullYear(), d.getMonth(), d.getDate());
  const back = (x.getDay() + 6) % 7; // Monday = 0
  return new Date(x.getFullYear(), x.getMonth(), x.getDate() - back);
}

function parseTimestamp_(s) {
  // Form timestamps arrive as text like "21/09/2026 10:42:26" (spreadsheet locale en_AU)
  const m = /^(\d{1,2})\/(\d{1,2})\/(\d{4})\s+(\d{1,2}):(\d{2}):(\d{2})/.exec(s);
  return m ? new Date(+m[3], +m[2] - 1, +m[1], +m[4], +m[5], +m[6]) : null;
}

function sheet_(name) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if (!sh) throw new Error('Sheet "' + name + '" not found. Run setup() first.');
  return sh;
}

function getOrCreateSheet_(ss, name, headers) {
  const sh = ss.getSheetByName(name) || ss.insertSheet(name);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#e8eaed');
    sh.setFrozenRows(1);
  }
  return sh;
}

function esc_(x) {
  return String(x).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
}