// PaperType for Google Sheets — Apps Script (MIT licence, https://papertype.dev/guides/pdf-from-google-sheets)
//
// Setup (once):
//   1. In the spreadsheet: Extensions → Apps Script. Replace the editor contents with this file. Save.
//   2. Project Settings (gear icon) → Script properties → Add script property:
//        PAPERTYPE_API_KEY = dp_your_key      (free key from https://papertype.dev/signup)
//   3. Reload the spreadsheet. A "PDF" menu appears next to Help.
//
// The functions reportBody() and invoiceBody() build the request; renderPdf_() sends it.
// Every request body these functions produce was posted to the live API before this file was published.

const PAPERTYPE_URL = 'https://papertype.dev/v1/pdf';

// ---------- menu ----------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('PDF')
    .addItem('Save selected row as report (Drive)', 'exportSelectedRowAsReport')
    .addItem('Save selected row as invoice (Drive)', 'exportSelectedRowAsInvoice')
    .addToUi();
}

function exportSelectedRowAsReport() {
  const row = selectedRow_();
  const pdf = renderPdf_(reportBody(row.headers, row.values));
  const file = DriveApp.createFile(pdf);
  SpreadsheetApp.getUi().alert('Saved to Drive: ' + file.getUrl());
}

function exportSelectedRowAsInvoice() {
  const row = selectedRow_();
  const pdf = renderPdf_(invoiceBody(row.headers, row.values));
  const file = DriveApp.createFile(pdf);
  SpreadsheetApp.getUi().alert('Saved to Drive: ' + file.getUrl());
}

// Installable trigger (Triggers → Add Trigger → function onFormSubmit, source "From spreadsheet", type "On form submit").
// Emails the submitted row as a PDF to the address in the form's "Email" question.
function onFormSubmit(e) {
  const sheet = e.range.getSheet();
  const lastCol = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const values = sheet.getRange(e.range.getRow(), 1, 1, lastCol).getValues()[0];
  const pdf = renderPdf_(reportBody(headers, values));
  const to = (e.namedValues['Email'] || [])[0];
  if (!to) throw new Error('No "Email" question in this form');
  GmailApp.sendEmail(to, 'Your submission as a PDF', 'Attached is a copy of what you submitted.', { attachments: [pdf] });
}

// ---------- request bodies (plain functions, no Google services, so they can be tested outside Sheets) ----------

// One row → a one-page Markdown report: the first column is the title, the other columns become a Field | Value table.
function reportBody(headers, values) {
  const title = cellText(values[0]) || 'Row report';
  const lines = ['# ' + title, '', '| Field | Value |', '|---|---|'];
  for (let i = 1; i < headers.length; i++) {
    if (cellText(headers[i]) === '') continue;
    lines.push('| ' + mdCell(headers[i]) + ' | ' + mdCell(values[i]) + ' |');
  }
  return {
    markdown: lines.join('\n'),
    title: title,
    theme: 'clean',
    page_numbers: true,
    filename: safeName(title) + '.pdf',
  };
}

// One row → an invoice with the hosted "invoice" template.
// Expected headers (case-insensitive): Number, Date, Due date, Client, Client address, Items, Tax rate, Currency.
// "Items" holds one line per item: description, quantity, unit price   (e.g. "Design, October, 10, 120").
function invoiceBody(headers, values) {
  const row = {};
  headers.forEach(function (h, i) { row[cellText(h).toLowerCase()] = values[i]; });
  const items = cellText(row['items']).split(/\r?\n/).filter(function (l) { return l.trim() !== ''; }).map(function (l) {
    const parts = l.split(',').map(function (s) { return s.trim(); });
    const unitPrice = Number(parts.pop());
    const quantity = Number(parts.pop());
    return { description: parts.join(', '), quantity: quantity, unit_price: unitPrice };
  });
  const number = cellText(row['number']);
  return {
    template: 'invoice',
    data: {
      number: number,
      date: isoDate(row['date']),
      due_date: isoDate(row['due date']),
      currency: cellText(row['currency']) || 'USD',
      from: { name: 'Northwind Studio', address_lines: ['12 Example Road', 'Springfield'] },   // ← your details
      to: { name: cellText(row['client']), address_lines: cellText(row['client address']).split(/\r?\n/) },
      items: items,
      tax_rate: Number(row['tax rate'] || 0),
      tax_label: 'Sales tax',
    },
    page_size: 'Letter',
    filename: (number || 'invoice') + '.pdf',
  };
}

// ---------- helpers ----------

function renderPdf_(body) {
  const key = PropertiesService.getScriptProperties().getProperty('PAPERTYPE_API_KEY');
  if (!key) throw new Error('Add PAPERTYPE_API_KEY under Project Settings → Script properties');
  const res = UrlFetchApp.fetch(PAPERTYPE_URL, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + key },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,   // so a 4xx returns the JSON error instead of throwing a generic exception
  });
  if (res.getResponseCode() !== 200) {
    throw new Error('PaperType ' + res.getResponseCode() + ': ' + res.getContentText());
  }
  return res.getBlob().setName(body.filename || 'document.pdf');
}

function selectedRow_() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const r = sheet.getActiveRange().getRow();
  if (r === 1) throw new Error('Select a data row, not the header row');
  const lastCol = sheet.getLastColumn();
  return {
    headers: sheet.getRange(1, 1, 1, lastCol).getValues()[0],
    values: sheet.getRange(r, 1, 1, lastCol).getValues()[0],
  };
}

function isDate(v) {
  return Object.prototype.toString.call(v) === '[object Date]';
}

function cellText(v) {
  if (v === null || v === undefined) return '';
  if (isDate(v)) return isoDate(v);
  return String(v);
}

// Sheets hands date cells to the script as Date objects; the invoice template wants yyyy-MM-dd strings.
function isoDate(v) {
  if (isDate(v)) return Utilities.formatDate(v, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  return cellText(v);
}

// Markdown table cells cannot contain "|" or line breaks; escape the pipe, turn breaks into <br>.
function mdCell(v) {
  return cellText(v).replace(/\|/g, '\\|').replace(/\r?\n/g, '<br>');
}

function safeName(s) {
  return String(s).replace(/[^A-Za-z0-9._-]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 60) || 'document';
}