/**
 * Updatepilot Update Log.
 *
 * SETUP (one time, inside your Google Sheet):
 * 1. Open your Google Sheet.
 * 2. Extensions -> Apps Script.
 * 3. Delete any starter code in the editor and paste this whole file in.
 * 4. Change SHARED_SECRET below to any random string of your own choosing.
 * 5. Click Deploy -> New deployment.
 *    - Select type: Web app.
 *    - Execute as: Me.
 *    - Who has access: Anyone.
 * 6. Click Deploy, authorize the script when Google prompts you, then copy the
 *    "Web app URL" it gives you.
 * 7. Paste that URL, and the same SHARED_SECRET, into the Updatepilot Update Log
 *    settings page in your WordPress admin.
 *
 * BEHAVIOR:
 * - Each incoming row is matched against existing rows in this month's tab by
 *   Name + Type. If found, that row is updated in place (new Update/Previous
 *   Version, checkbox reset to unticked). If not found, a new row is appended.

 */

const SHARED_SECRET = 'change-this-to-a-random-string';

function doPost(e) {
  try {
    const payload = JSON.parse(e.postData.contents);

    if (payload.secret !== SHARED_SECRET) {
      return respond({ status: 'error', message: 'Invalid secret.' });
    }

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const tabTitle = payload.tab;
    let sheet = ss.getSheetByName(tabTitle);

    if (!sheet) {
      sheet = ss.insertSheet(tabTitle);
      sheet.getRange('A1:E1').setValues([[ 'Name', 'Type', 'Update to Version', 'Previous Version', 'Updated' ]]);
      sheet.getRange('A1:E1').setFontWeight('bold');
    }

    removeDefaultBlankSheet(ss, tabTitle);

    const rows = payload.rows || [];
    const checkboxRule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
    let addedCount = 0;
    let updatedCount = 0;

    rows.forEach(function (r) {
      const existingRow = findRow(sheet, r.name, r.type);

      if (existingRow) {
        sheet.getRange(existingRow, 3, 1, 2).setValues([[r.current, r.previous]]);
        const checkboxCell = sheet.getRange(existingRow, 5, 1, 1);
        checkboxCell.setValue(false);
        checkboxCell.setDataValidation(checkboxRule);
        updatedCount++;
      } else {
        const targetRow = sheet.getLastRow() + 1;
        sheet.getRange(targetRow, 1, 1, 5).setValues([[r.name, r.type, r.current, r.previous, false]]);
        sheet.getRange(targetRow, 5, 1, 1).setDataValidation(checkboxRule);
        addedCount++;
      }
    });

    return respond({
      status: 'success',
      message: 'Sheet updated (' + tabTitle + ')'
    });
  } catch (err) {
    return respond({ status: 'error', message: err.message });
  }
}

/**
 * Find an existing row (by 1-based row number) matching this Name + Type,
 * searching only columns A:B below the header. Returns null if not found.
 */
function findRow(sheet, name, type) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return null;

  const data = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === name && data[i][1] === type) {
      return i + 2; // Convert 0-based array index back to an actual sheet row number.
    }
  }
  return null;
}

/**
 * Delete the default blank "Sheet1" Google creates for every new spreadsheet,
 * but only once a real month tab exists, and only if Sheet1 is genuinely empty.
 */
function removeDefaultBlankSheet(ss, currentTabTitle) {
  if (currentTabTitle === 'Sheet1') return;

  const sheet = ss.getSheetByName('Sheet1');
  if (!sheet) return;

  const isEmpty = sheet.getLastRow() === 0 && sheet.getLastColumn() === 0;
  if (isEmpty && ss.getSheets().length > 1) {
    ss.deleteSheet(sheet);
  }
}

function respond(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}