/*
HELIO SHEETS INTEGRATION v0.2.6
AUTHOR: [email protected]
HOW TO USE:
Add your...
HELIO_API_KEY (Get this from the Helio Dash | Settings | API | Secret API Key)
PUBLIC_KEY (Get this from the Helio Dash | Settings | API | Public API Key)
Then select the main function at the top and press Run!
HOW IT WORKS: This script gets your recent Helio transactions filtered aafter the 
specified date, checks if they are not already on the sheet and then appends the
data below the headers, sorting by most recently purchased.
*/
// Add your values here

const PUBLIC_KEY = "";
const HELIO_API_KEY = "";
const FILTER_DATE = new Date("2024-09-15T00:00:00Z"); // Set this to your desired start date
const HEADERS = [
  'Time Purchased (UTC)',
  'Time stopped',
  'Total time charged',
  'Sender',
  'Product',
  'Quantity',
  'Paid',
  'Currency',
  'Payment Link',
  'Payment Link (Short)',
  'SolScan URL',
  'Transaction ID',
  'E-mail Address',
  'Twitter Username',
  'Discord Username',
  'Phone Number',
  'Name',
  'Street Number',
  'Street',
  'Delivery Address',
  'City',
  'Area Code',
  'State',
  'Country',
  'Additional Info',
];

function main() {
  const helioTransactions = getRecentTransactions(HELIO_API_KEY, PUBLIC_KEY);
  if (helioTransactions != null) {
    console.log('RECEIVED: Transaction data');
    const filteredTransactions = filterTransactions(helioTransactions, FILTER_DATE);
    if (filteredTransactions.length > 0) {
      writeNewTransactions(filteredTransactions, HEADERS);
    } else {
      console.log('No new transactions to add after the specified date');
    }
  } else {
    throw new Error('ERROR: could not get helio transactions');
  }
}

function getRecentTransactions(API_KEY, PK) {
  try {
    const response = UrlFetchApp.fetch('https://api.hel.io/v1/export/payments?publicKey='+PK, {
      method: 'get',
      headers: {
        'Authorization': 'Bearer '+ API_KEY,
      }
    });
    if (response.getResponseCode() == 200) {
      return JSON.parse(response.getContentText());
    }
  } catch (e) {
    console.log(e);
  }
}

function filterTransactions(helioTransactions, filterDate) {
  var sheet = SpreadsheetApp.getActiveSheet();
  const existingTransactionIDs = getColumn(sheet, 12); 
  
  return helioTransactions.filter(transaction => {
    const transactionDate = new Date(transaction.time);
    return transactionDate >= filterDate && !existingTransactionIDs.includes(transaction.id);
  });
}

function writeNewTransactions(newTransactions, headers) {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Ensure headers are present and correct
  const firstRow = sheet.getRange(1, 1, 1, headers.length);
  const firstRowValues = firstRow.getValues()[0];
  if (!arraysEqual(headers, firstRowValues)) {
    firstRow.setValues([headers]);
    firstRow.setFontWeight("bold");
  }
  
  // Prepare the data for batch insert
  const newData = newTransactions.map(item => [
    item.active ? `Live (${item.time})` : item.time,
    item.active || !item.cancelAt ? '' : item.cancelDate,
    item.timeCharged,
    item.from,
    item.paymentRequestName,
    item.quantity ?? 1,
    item.convertedAmount + " " + item.currency,
    item.currency,
    item.paymentRequestUrl,
    item.paymentRequestUrlWithSlug ?? '',
    item.solScanLink,
    item.id,
    item.email,
    item.twitterUsername,
    item.discordUsername,
    item.phoneNumber,
    item.fullName ?? '',
    item.streetNumber ? item.streetNumber.replace(',', ';') : '',
    item.street ? item.street.replace(',', ';') : '',
    item.deliveryAddress ? item.deliveryAddress.replace(',', ';') : '',
    item.city ? item.city.replace(',', ';') : '',
    item.areaCode ? item.areaCode.replace(',', ';') : '',
    item.state ? item.state.replace(',', ';') : '',
    item.country,
    item.value && item.name ? `${item.value.replace(',', ';')} (${item.name})` : ''
  ]);

  // Insert all new transactions at once
  if (newData.length > 0) {
    sheet.getRange(2, 1, newData.length, headers.length).insertCells(SpreadsheetApp.Dimension.ROWS);
    sheet.getRange(2, 1, newData.length, headers.length).setValues(newData);
    console.log(`ADDED ${newData.length} NEW TRANSACTIONS`);
  }
  
  SpreadsheetApp.flush();
}

function arraysEqual(a, b) {
  if (a === b) return true;
  if (a == null || b == null) return false;
  if (a.length !== b.length) return false;
  for (var i = 0; i < a.length; ++i) {
    if (a[i] !== b[i]) return false;
  }
  return true;
}

function getColumn(activeSheet, columnIndex) {
  return activeSheet.getRange(1, columnIndex)
    .getDataRegion(SpreadsheetApp.Dimension.ROWS)
    .getValues()
    .flat();
}