// ─────────────────────────────────────────────────────────────
// HALO TRADER APPLICATION — Apps Script  v2
// Deploy: Extensions → Apps Script → Deploy → New Deployment
//   Type: Web App  |  Execute as: Me  |  Access: Anyone
// ─────────────────────────────────────────────────────────────
// FOLDER STRUCTURE:
//   Halo Trader Applications 2026/
//     ├── May BH Weekend — Hainault/
//     │     └── Martin Tribe — TS-123/
//     │           ├── Compliance/
//     │           └── Unit 1 — Burger Van/Photos/
//     ├── June — Norwich/
//     └── ...
// ─────────────────────────────────────────────────────────────

var ROOT_FOLDER = 'Halo Trader Applications 2026';
var SHEET_NAME  = 'Trader Applications';

// Weekend/site groupings matching the form
var EVENT_GROUPS = {
  'Hainault':   'May BH Weekend — Hainault',
  'Norwich':    'June — Norwich',
  'Morden':     'Morden (multiple dates)',
  'Rochester':  'July — Rochester',
  'Blackheath': 'July — Blackheath',
  'Southend':   'Aug BH Weekend — Southend',
  'Wimbledon':  'Nov Fireworks — Wimbledon',
};
function getSiteFolder(locations) {
  // locations = comma-separated list of location names from the submission
  if (!locations) return 'Uncategorised';
  var locs = locations.split(',').map(function(l){ return l.trim(); });
  if (locs.length === 1) return EVENT_GROUPS[locs[0]] || locs[0];
  return 'Multiple Sites';
}

function doGet(e) {
  try {
    var action = (e && e.parameter && e.parameter.action) || '';
    if (action === 'get_all_rows') return getAllRows();
    if (action === 'get_eho_list') return getEhoList();
    return ok({ error: 'Unknown GET action' });
  } catch(err) {
    return ok({ error: err.toString() });
  }
}

function getAllRows() {
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME) || ss.getActiveSheet();
  var range = sheet.getDataRange();
  var data  = range.getValues();
  var formulas = range.getFormulas();
  if (data.length < 2) return ok({ rows: [] });
  var headers = data[0];
  var rows = data.slice(1).map(function(row, rIdx) {
    var obj = {};
    headers.forEach(function(h, i) {
      var formula = formulas[rIdx + 1] ? formulas[rIdx + 1][i] : '';
      if (formula) {
        // Extract URL from =HYPERLINK("url","label")
        var match = formula.match(/HYPERLINK\("([^"]+)"/);
        if (match) { obj[h] = match[1]; return; }
      }
      obj[h] = row[i];
    });
    return obj;
  });
  return ok({ rows: rows });
}

function doPost(e) {
  try {
    var p = JSON.parse(e.postData.contents);
    if (p.action === 'upload_file') return uploadFile(p);
    if (p.action === 'register_trader') return registerTrader(p);
    if (p.action === 'write_row')   return writeRow(p);
    if (p.action === 'lookup')      return lookupRow(p);
    if (p.action === 'amend_row')   return amendRow(p);
    if (p.action === 'update_doc_urls') return updateDocUrls(p);
    if (p.action === 'ncass_file')    return fileNcassDoc(p);
    if (p.action === 'update_status') return updateStatus(p);
    if (p.action === 'send_invoice')  return sendInvoice(p);
    if (p.action === 'mark_paid')     return markPaid(p);
    return ok({ error: 'Unknown action' });
  } catch(err) {
    return ok({ error: err.toString() });
  }
}

function uploadFile(p) {
  var folder = resolveFolder(p.folderPath);
  var bytes  = Utilities.base64Decode(p.fileData);
  var blob   = Utilities.newBlob(bytes, p.mimeType || 'application/octet-stream', p.fileName);
  var file   = folder.createFile(blob);
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  return ok({ url: 'https://drive.google.com/file/d/' + file.getId() + '/view' });
}

// ── REGISTER TRADER (new multi-sheet structure) ──
// ── REGISTER TRADER — writes to existing Trader Applications sheet ──
function registerTrader(p) {
  var trader = p.trader;
  var units  = p.units || [];
  if (!trader || !trader.trader_id) return ok({ error: 'Missing trader data' });

  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME) || ss.getActiveSheet();

  // One row per unit (same format as the old form)
  for (var j = 0; j < units.length; j++) {
    var combined = {};
    // Copy trader fields
    var tKeys = Object.keys(trader);
    for (var tk = 0; tk < tKeys.length; tk++) combined[tKeys[tk]] = trader[tKeys[tk]];
    // Map trader_id → submission_id for compat with admin panel
    combined['submission_id'] = trader.trader_id;
    combined['submitted_at'] = trader.registered_at;
    combined['total_units'] = units.length;
    // Copy unit fields with unit_ prefix
    var u = units[j];
    var uKeys = Object.keys(u);
    for (var uk = 0; uk < uKeys.length; uk++) {
      if (uKeys[uk] === 'trader_id') continue;
      combined['unit_' + uKeys[uk]] = u[uKeys[uk]];
    }
    writeObjToSheet(sheet, combined);
  }

  return ok({ success: true, traderId: trader.trader_id });
}

// Helper: write an object as a row, creating/expanding headers as needed
function writeObjToSheet(sheet, obj) {
  var lastCol = sheet.getLastColumn();
  var headers = lastCol > 0 ? sheet.getRange(1, 1, 1, lastCol).getValues()[0] : [];
  var keys = Object.keys(obj);

  // Find or create columns for each key
  var newHeaders = [];
  for (var k = 0; k < keys.length; k++) {
    if (headers.indexOf(keys[k]) === -1) {
      newHeaders.push(keys[k]);
    }
  }
  if (newHeaders.length) {
    var startCol = headers.length + 1;
    for (var n = 0; n < newHeaders.length; n++) {
      sheet.getRange(1, startCol + n).setValue(newHeaders[n]);
      var hdr = sheet.getRange(1, startCol + n);
      hdr.setFontWeight('bold').setBackground('#0c0c14').setFontColor('#C8A96E');
      headers.push(newHeaders[n]);
    }
  }

  // Build aligned row
  var row = headers.map(function(h) {
    if (!obj.hasOwnProperty(h)) return '';
    return obj[h];
  });

  // Write row with HYPERLINK formulas for URLs
  var newRow = sheet.getLastRow() + 1;
  for (var c = 0; c < row.length; c++) {
    var cell = sheet.getRange(newRow, c + 1);
    var val = row[c];
    if (typeof val === 'string' && val.indexOf('http') === 0 && headers[c].indexOf('url') > -1) {
      var fname = val.split('/').pop() || headers[c];
      cell.setFormula('=HYPERLINK("' + val + '","' + fname + '")');
    } else {
      cell.setValue(val);
    }
  }
}

function writeRow(p) {
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = p.sheet || SHEET_NAME;
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) sheet = ss.insertSheet(sheetName);
  var row   = p.row;
  var keys  = Object.keys(row);

  // ── First row ever: create headers ──
  if (sheet.getLastRow() === 0 || sheet.getRange(1,1).getValue() === '') {
    sheet.appendRow(keys);
    var hdr = sheet.getRange(1, 1, 1, keys.length);
    hdr.setFontWeight('bold').setBackground('#0c0c14').setFontColor('#C8A96E');
    sheet.setFrozenRows(1);
  }

  // ── Read existing headers and map incoming data to correct columns ──
  var existingHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(function(h) { return String(h).trim(); });

  // Find any new keys not yet in headers and append them
  var newKeys = [];
  keys.forEach(function(k) {
    if (existingHeaders.indexOf(k) === -1) newKeys.push(k);
  });
  if (newKeys.length > 0) {
    var startCol = existingHeaders.length + 1;
    for (var nk = 0; nk < newKeys.length; nk++) {
      sheet.getRange(1, startCol + nk).setValue(newKeys[nk]).setFontWeight('bold').setBackground('#0c0c14').setFontColor('#C8A96E');
    }
    existingHeaders = existingHeaders.concat(newKeys);
  }

  // Build a row array aligned to the header order
  var alignedRow = existingHeaders.map(function(h) {
    return row.hasOwnProperty(h) ? row[h] : '';
  });
  sheet.appendRow(alignedRow);

  // Convert URL columns to clickable HYPERLINK formulas
  var newRow = sheet.getLastRow();
  existingHeaders.forEach(function(key, colIdx) {
    var val = row[key];
    if (!val || typeof val !== 'string') return;
    if (!key.match(/_url$/)) return; // only single-url columns (not _urls)
    if (val.indexOf('upload_failed') !== -1 || !val.match(/^https?:\/\//)) return;
    var label = val.split('/').pop() || 'View File';
    try { label = decodeURIComponent(label); } catch(e) {}
    if (label.length > 40) label = label.substring(0, 37) + '...';
    sheet.getRange(newRow, colIdx + 1).setFormula('=HYPERLINK("' + val + '","📎 ' + label.replace(/"/g, '\\"') + '")');
  });

  // For multi-url columns (_urls), make each URL on its own line with labels
  existingHeaders.forEach(function(key, colIdx) {
    var val = row[key];
    if (!val || typeof val !== 'string') return;
    if (!key.match(/_urls$/)) return;
    var urls = val.split(' | ').filter(function(u) { return u.match(/^https?:\/\//) && u.indexOf('upload_failed') === -1; });
    if (urls.length === 0) return;
    if (urls.length === 1) {
      var lbl = urls[0].split('/').pop() || 'View File';
      try { lbl = decodeURIComponent(lbl); } catch(e) {}
      if (lbl.length > 40) lbl = lbl.substring(0, 37) + '...';
      sheet.getRange(newRow, colIdx + 1).setFormula('=HYPERLINK("' + urls[0] + '","📎 ' + lbl.replace(/"/g, '\\"') + '")');
    } else {
      // Multiple files: write count + first link; put all URLs in a note
      var lbl1 = urls[0].split('/').pop() || 'File 1';
      try { lbl1 = decodeURIComponent(lbl1); } catch(e) {}
      sheet.getRange(newRow, colIdx + 1)
        .setFormula('=HYPERLINK("' + urls[0] + '","📎 ' + urls.length + ' files — click for first")')
        .setNote(urls.join('\n'));
    }
  });
  // Send confirmation email to trader
  try {
    var traderEmail = row.primary_email || row.email || '';
    var traderName  = row.trading_name  || row.first_name || 'Trader';
    var subId       = row.submission_id || '';
    if (traderEmail) {
      var subject = 'We Are Halo — Application Received (' + subId + ')';
      var body    = 'Dear ' + traderName + ',\n\n'
        + 'Thank you for submitting your trader application to We Are Halo for the 2026 season.\n\n'
        + 'YOUR SUBMISSION ID: ' + subId + '\n\n'
        + 'Please save this ID — you will need it if you want to amend your application later at:\n'
        + 'https://halo-trader-portal.thom-9d8.workers.dev/portal.html\n\n'
        + 'What happens next:\n'
        + 'We will review your application and contact you individually for each event you are approved for. '
        + 'Approval is not guaranteed and is subject to availability and suitability.\n\n'
        + 'If you have any questions, please contact us at info@halo-halo.co.uk\n\n'
        + 'We Are Halo Team\n'
        + 'info@halo-halo.co.uk | halo-halo.co.uk';
      MailApp.sendEmail(traderEmail, subject, body);
    }
  } catch(mailErr) {
    // Email failure non-fatal — log and continue
    Logger.log('Email error: ' + mailErr.toString());
  }
  return ok({ success: true });
}

function lookupRow(p) {
  // Find a submission row by email + submission_id
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME) || ss.getActiveSheet();
  var range = sheet.getDataRange();
  var data  = range.getValues();
  var formulas = range.getFormulas();
  if (data.length < 2) return ok({ ok: false, row: null });
  var headers = data[0];
  var emailIdx = headers.indexOf('primary_email');
  if (emailIdx === -1) emailIdx = headers.indexOf('email');
  var idIdx = headers.indexOf('submission_id');
  var searchEmail = (p.email || '').toLowerCase().trim();
  var searchId    = (p.submissionId || '').trim();
  for (var i = 1; i < data.length; i++) {
    var rowEmail = (data[i][emailIdx] || '').toLowerCase().trim();
    var rowId    = (data[i][idIdx] || '').trim();
    if (rowEmail === searchEmail && rowId === searchId) {
      var obj = {};
      headers.forEach(function(h, j) {
        var formula = formulas[i] ? formulas[i][j] : '';
        if (formula) {
          var match = formula.match(/HYPERLINK\("([^"]+)"/);
          if (match) { obj[h] = match[1]; return; }
        }
        obj[h] = data[i][j];
      });
      return ok({ ok: true, row: obj });
    }
  }
  return ok({ ok: false, row: null });
}

function amendRow(p) {
  // Append an amendment record to a dedicated Amendments sheet tab
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Amendments');
  if (!sheet) {
    sheet = ss.insertSheet('Amendments');
    sheet.appendRow(['Timestamp','Submission ID','Email','Amendment Ref','Changes JSON']);
    sheet.getRange(1,1,1,5).setFontWeight('bold').setBackground('#1a1a3e').setFontColor('#C8A96E');
  }
  sheet.appendRow([
    new Date().toISOString(),
    p.submissionId || '',
    p.email || '',
    p.amendRef || '',
    JSON.stringify(p.changes || {})
  ]);
  // Notify admin by email
  try {
    MailApp.sendEmail(
      Session.getEffectiveUser().getEmail(),
      '[Halo Trader Portal] Amendment received — ' + (p.submissionId || ''),
      'Amendment submitted by ' + (p.email || '') + '\n\n'
      + 'Submission ID: ' + (p.submissionId || '') + '\n'
      + 'Amendment Ref: ' + (p.amendRef || '') + '\n\n'
      + 'Changes:\n' + JSON.stringify(p.changes || {}, null, 2)
    );
  } catch(e) { Logger.log(e); }
  return ok({ success: true });
}

function resolveFolder(pathArray) {
  var roots = DriveApp.getFoldersByName(ROOT_FOLDER);
  var cur   = roots.hasNext() ? roots.next() : DriveApp.createFolder(ROOT_FOLDER);
  (pathArray || []).forEach(function(name) {
    if (!name || name.toString().trim() === '') return;
    var sub = cur.getFoldersByName(name);
    cur = sub.hasNext() ? sub.next() : cur.createFolder(name);
  });
  return cur;
}

// ── UPDATE DOC URLS IN ORIGINAL SHEET ROW ──
// Called by the portal when a trader re-uploads compliance docs.
// Updates the _url columns in ALL rows matching the submission_id.
function updateDocUrls(p) {
  var submissionId = p.submissionId;
  var updates      = p.updates; // { "doc_pat_url": "https://...", "doc_pli_url": "https://..." }
  if (!submissionId || !updates) return ok({ error: 'Missing submissionId or updates' });

  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME) || ss.getActiveSheet();
  var data  = sheet.getDataRange().getValues();
  if (data.length < 2) return ok({ error: 'Sheet empty' });

  var headers = data[0];
  var idIdx   = headers.indexOf('submission_id');
  if (idIdx === -1) return ok({ error: 'No submission_id column' });

  // Build colMap — create any missing columns first
  var colMap = {}; // column name → column index (0-based)
  var lastCol = headers.length;
  Object.keys(updates).forEach(function(colName) {
    var ci = headers.indexOf(colName);
    if (ci > -1) {
      colMap[colName] = ci;
    } else {
      // Column doesn't exist yet — append it
      var newColIdx = lastCol;
      lastCol++;
      sheet.getRange(1, newColIdx + 1).setValue(colName);
      // Style the new header
      var hdr = sheet.getRange(1, newColIdx + 1);
      hdr.setFontWeight('bold').setBackground('#0c0c14').setFontColor('#C8A96E');
      headers.push(colName);
      colMap[colName] = newColIdx;
    }
  });

  var updatedRows = 0;
  for (var i = 1; i < data.length; i++) {
    if (data[i][idIdx] == submissionId) {
      Object.keys(colMap).forEach(function(colName) {
        var colIdx = colMap[colName];
        var val = updates[colName];
        // If it looks like a URL, wrap it in a HYPERLINK formula
        if (val && val.indexOf('http') === 0) {
          var fname = val.split('/').pop() || colName;
          sheet.getRange(i + 1, colIdx + 1).setFormula('=HYPERLINK("' + val + '","📎 ' + fname + '")');
        } else {
          sheet.getRange(i + 1, colIdx + 1).setValue(val);
        }
      });
      updatedRows++;
    }
  }

  return ok({ success: true, updatedRows: updatedRows });
}

// ── NCASS DOC FILER ──
// Fetches a URL and saves the file into the correct trader folder
function fileNcassDoc(p) {
  // p.url = public URL of the document
  // p.traderName = trading name
  // p.locations  = locations string (e.g. "Hainault,Rochester")
  // p.docType    = e.g. "COSHH Risk Assessment"
  // p.submissionId = optional TS-xxx
  try {
    var siteFolder  = getSiteFolder(p.locations || '');
    var traderLabel = (p.traderName || 'Unknown Trader') + (p.submissionId ? ' — ' + p.submissionId : '');
    var folder = resolveFolder([siteFolder, traderLabel, 'Compliance']);
    var resp   = UrlFetchApp.fetch(p.url, { muteHttpExceptions: true });
    if (resp.getResponseCode() !== 200) {
      return ok({ error: 'Fetch failed: HTTP ' + resp.getResponseCode() });
    }
    var blob = resp.getBlob();
    // Use doc type as filename if sensible, otherwise URL basename
    var ext = '';
    var ct  = blob.getContentType() || '';
    if (ct.indexOf('pdf') > -1) ext = '.pdf';
    else if (ct.indexOf('word') > -1 || ct.indexOf('docx') > -1) ext = '.docx';
    else if (ct.indexOf('png') > -1) ext = '.png';
    else if (ct.indexOf('jpeg') > -1 || ct.indexOf('jpg') > -1) ext = '.jpg';
    var fileName = (p.docType || 'Document').replace(/[\/:*?"<>|]/g,'_') + ext;
    blob.setName(fileName);
    var file = folder.createFile(blob);
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    return ok({ success: true, url: 'https://drive.google.com/file/d/' + file.getId() + '/view', folder: siteFolder + '/' + traderLabel });
  } catch(err) {
    return ok({ error: err.toString() });
  }
}


// ─────────────────────────────────────────────────────────────
// GMAIL AUTO-PARSER FOR NCASS EMAILS
// Set up: In Apps Script → Triggers → Add Trigger
//   Function: checkNcassEmails
//   Event: Time-driven → Every hour (or every 15 min)
// Label any NCASS emails "ncass-docs" in Gmail to process them.
// ─────────────────────────────────────────────────────────────

var NCASS_GMAIL_LABEL = 'ncass-docs';      // Gmail label to watch
var NCASS_DONE_LABEL  = 'ncass-filed';     // Label applied after filing

// Known doc types from NCASS email subject/body keywords
var NCASS_DOC_MAP = [
  { keyword: 'coshh',            type: 'COSHH Risk Assessment' },
  { keyword: 'h&s policy',       type: 'H&S Policy' },
  { keyword: 'h & s policy',     type: 'H&S Policy' },
  { keyword: 'h&s risk',         type: 'H&S Risk Assessment' },
  { keyword: 'food safety risk', type: 'Food Safety Risk Assessment' },
  { keyword: 'fire risk',        type: 'Fire Risk Assessment' },
  { keyword: 'public liability', type: 'Public Liability Insurance' },
  { keyword: 'employers',        type: 'Employers Liability Insurance' },
  { keyword: 'pat ',             type: 'PAT / EICR Certificate' },
  { keyword: 'food hygiene',     type: 'Food Hygiene Rating Certificate' },
  { keyword: 'gas safety',       type: 'Gas Safety Certificate' },
  { keyword: 'method statement', type: 'Method Statement' },
];

function guessDocType(text) {
  var lower = (text || '').toLowerCase();
  for (var i = 0; i < NCASS_DOC_MAP.length; i++) {
    if (lower.indexOf(NCASS_DOC_MAP[i].keyword) > -1) return NCASS_DOC_MAP[i].type;
  }
  return 'Compliance Document';
}

function extractLinks(body) {
  // Extract all http/https links from the email body
  var links = [];
  var re = /https?:\/\/[^\s<>"]+/g;
  var m;
  while ((m = re.exec(body)) !== null) {
    var url = m[0].replace(/[.,;)]+$/, ''); // strip trailing punctuation
    // Skip Google tracking links and unsubscribe links
    if (url.indexOf('unsubscribe') > -1) continue;
    if (url.indexOf('mail.google') > -1) continue;
    links.push(url);
  }
  return links;
}

function getSenderName(message) {
  var from = message.getFrom() || '';
  // "Mr. Martin Tribe " → "Martin Tribe"
  var match = from.match(/^(.+?)\s*]+>/, '').trim() || 'Unknown';
}

function checkNcassEmails() {
  var labelIn  = GmailApp.getUserLabelByName(NCASS_GMAIL_LABEL);
  if (!labelIn) {
    Logger.log('Label "' + NCASS_GMAIL_LABEL + '" not found. Create it in Gmail and apply it to NCASS emails.');
    return;
  }
  var labelDone = GmailApp.getUserLabelByName(NCASS_DONE_LABEL)
                || GmailApp.createLabel(NCASS_DONE_LABEL);

  var threads = labelIn.getThreads(0, 20);
  var filed = 0;
  
  threads.forEach(function(thread) {
    var messages = thread.getMessages();
    messages.forEach(function(msg) {
      if (msg.isStarred()) return; // skip already-starred (manual hold)
      
      var subject  = msg.getSubject() || '';
      var body     = msg.getPlainBody() || '';
      var bodyHtml = msg.getBody() || '';
      var sender   = getSenderName(msg);
      
      // Try to find NCASS membership number in body
      var memberMatch = body.match(/membership\s*(?:number|no\.?|#)?\s*:?\s*(\d{4,6})/i);
      var memberNum = memberMatch ? memberMatch[1] : '';
      
      // Extract all doc links from email body
      var links = extractLinks(bodyHtml || body);
      
      if (!links.length) {
        Logger.log('No links found in email from ' + sender);
        return;
      }
      
      // Log to a Filings sheet
      var ss    = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('NCASS Filings');
      if (!sheet) {
        sheet = ss.insertSheet('NCASS Filings');
        sheet.appendRow(['Timestamp','From','Subject','Member#','Links Found','Status']);
        sheet.getRange(1,1,1,6).setFontWeight('bold').setBackground('#1a1a3e').setFontColor('#C8A96E');
      }
      
      var docResults = [];
      links.forEach(function(url) {
        try {
          var docType = guessDocType(url + ' ' + subject + ' ' + body);
          // Determine folder — default to sender name, location TBD (admin can move)
          var folder = resolveFolder(['_NCASS Inbox — Review', sender + (memberNum ? ' (' + memberNum + ')' : '')]);
          var resp   = UrlFetchApp.fetch(url, { muteHttpExceptions: true, followRedirects: true });
          if (resp.getResponseCode() === 200) {
            var blob = resp.getBlob();
            var ct   = blob.getContentType() || '';
            var ext  = ct.indexOf('pdf') > -1 ? '.pdf' : ct.indexOf('word') > -1 ? '.docx' : '';
            blob.setName(docType.replace(/[\/:*?"<>|]/g,'_') + ext);
            var file = folder.createFile(blob);
            file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
            docResults.push('✓ ' + docType);
            filed++;
          } else {
            docResults.push('✗ HTTP ' + resp.getResponseCode() + ' ' + url.substring(0,40));
          }
        } catch(err) {
          docResults.push('✗ ' + err.message.substring(0,60));
        }
      });
      
      sheet.appendRow([
        new Date().toISOString(),
        sender,
        subject,
        memberNum,
        links.length,
        docResults.join(' | ')
      ]);
      
      // Move thread to done label
      thread.removeLabel(labelIn);
      thread.addLabel(labelDone);
    });
  });
  Logger.log('Filed ' + filed + ' documents from ' + threads.length + ' email threads.');
}

// ── STATUS MANAGEMENT ──────────────────────────────────────────
var STATUS_SHEET = 'Application Status';

function getStatusSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName(STATUS_SHEET);
  if (!sh) {
    sh = ss.insertSheet(STATUS_SHEET);
    sh.appendRow(['submission_id','trading_name','primary_email','status',
                  'approved_at','invoice_ref','invoice_amount','invoice_sent_at',
                  'paid_at','eho_cleared','notes']);
    sh.getRange(1,1,1,11).setFontWeight('bold').setBackground('#0c0c14').setFontColor('#C8A96E');
    sh.setFrozenRows(1);
  }
  return sh;
}

function updateStatus(p) {
  var sh   = getStatusSheet();
  var data = sh.getDataRange().getValues();
  var headers = data[0];
  var idIdx = headers.indexOf('submission_id');
  // Find existing row
  for (var i = 1; i < data.length; i++) {
    if (data[i][idIdx] === p.submissionId) {
      // Update fields
      if (p.status)        sh.getRange(i+1, headers.indexOf('status')+1).setValue(p.status);
      if (p.approved_at)   sh.getRange(i+1, headers.indexOf('approved_at')+1).setValue(p.approved_at);
      if (p.invoice_ref)   sh.getRange(i+1, headers.indexOf('invoice_ref')+1).setValue(p.invoice_ref);
      if (p.invoice_amount!==undefined) sh.getRange(i+1, headers.indexOf('invoice_amount')+1).setValue(p.invoice_amount);
      if (p.invoice_sent_at) sh.getRange(i+1, headers.indexOf('invoice_sent_at')+1).setValue(p.invoice_sent_at);
      if (p.paid_at)       sh.getRange(i+1, headers.indexOf('paid_at')+1).setValue(p.paid_at);
      if (p.eho_cleared!==undefined) sh.getRange(i+1, headers.indexOf('eho_cleared')+1).setValue(p.eho_cleared);
      if (p.notes)         sh.getRange(i+1, headers.indexOf('notes')+1).setValue(p.notes);
      return ok({ success: true, updated: true });
    }
  }
  // New row
  sh.appendRow([
    p.submissionId||'', p.tradingName||'', p.email||'',
    p.status||'Applied',
    p.approved_at||'', p.invoice_ref||'', p.invoice_amount||'',
    p.invoice_sent_at||'', p.paid_at||'', p.eho_cleared||false, p.notes||''
  ]);
  return ok({ success: true, created: true });
}

function sendInvoice(p) {
  // p.submissionId, p.email, p.tradingName, p.invoiceRef, p.invoiceAmount, p.invoiceHtml, p.invoiceLines
  try {
    var subject = 'We Are Halo — Invoice ' + (p.invoiceRef||'') + ' for ' + (p.tradingName||'');
    var lineText = (p.invoiceLines||[]).map(function(l){ return l.desc + ': £' + l.amount; }).join('\n');
    var body = 'Dear ' + (p.tradingName||'Trader') + ',\n\n'
      + 'Please find your invoice for your approved trading space(s) at We Are Halo 2026 events.\n\n'
      + 'INVOICE REFERENCE: ' + (p.invoiceRef||'') + '\n'
      + 'TOTAL DUE: £' + (p.invoiceAmount||'0') + '\n\n'
      + 'BREAKDOWN:\n' + lineText + '\n\n'
      + 'PAYMENT DETAILS:\n'
      + 'Bank transfer preferred. Payment due within 14 days of this invoice.\n'
      + 'Please use your invoice reference as payment reference.\n\n'
      + 'If you have any questions please contact info@halo-halo.co.uk\n\n'
      + 'We Are Halo Team';
    MailApp.sendEmail({
      to: p.email,
      subject: subject,
      body: body,
      htmlBody: p.invoiceHtml || body.replace(/\n/g,'
')
    });
    // Update status
    updateStatus({
      submissionId: p.submissionId,
      tradingName:  p.tradingName,
      email:        p.email,
      status:       'Invoiced',
      invoice_ref:  p.invoiceRef,
      invoice_amount: p.invoiceAmount,
      invoice_sent_at: new Date().toISOString()
    });
    return ok({ success: true });
  } catch(err) {
    return ok({ error: err.toString() });
  }
}

function markPaid(p) {
  updateStatus({
    submissionId: p.submissionId,
    status:       'Paid',
    paid_at:      new Date().toISOString()
  });
  // Optionally email trader confirmation
  if (p.email && p.tradingName) {
    try {
      MailApp.sendEmail(
        p.email,
        'We Are Halo — Payment Received — ' + (p.invoiceRef||p.submissionId),
        'Dear ' + p.tradingName + ',\n\nThank you — your payment has been received and your trading space is confirmed.\n\nWe look forward to having you at our events. Your event pack will follow closer to the date.\n\nWe Are Halo Team'
      );
    } catch(e) { Logger.log(e); }
  }
  return ok({ success: true });
}

function getEhoList() {
  // Return only Paid traders with their compliance doc URLs
  var statusSh = getStatusSheet();
  var mainSh   = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME)
               || SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var statusData = statusSh.getDataRange().getValues();
  var sHeaders   = statusData[0];
  var sIdIdx     = sHeaders.indexOf('submission_id');
  var sStatusIdx = sHeaders.indexOf('status');
  
  // Build set of paid submission IDs
  var paidIds = {};
  for (var i = 1; i < statusData.length; i++) {
    var st = (statusData[i][sStatusIdx] || '').toString();
    if (st === 'Paid' || st === 'Approved') {
      paidIds[statusData[i][sIdIdx]] = st;
    }
  }
  
  // Get main rows for paid traders
  var mainRange = mainSh.getDataRange();
  var mainData = mainRange.getValues();
  var mainFormulas = mainRange.getFormulas();
  if (mainData.length < 2) return ok({ rows: [] });
  var mHeaders = mainData[0];
  var mIdIdx   = mHeaders.indexOf('submission_id');
  
  var rows = [];
  for (var j = 1; j < mainData.length; j++) {
    var subId = mainData[j][mIdIdx];
    if (paidIds[subId]) {
      var obj = { _status: paidIds[subId] };
      mHeaders.forEach(function(h, k) {
        var formula = mainFormulas[j] ? mainFormulas[j][k] : '';
        if (formula) {
          var match = formula.match(/HYPERLINK\("([^"]+)"/);
          if (match) { obj[h] = match[1]; return; }
        }
        obj[h] = mainData[j][k];
      });
      rows.push(obj);
    }
  }
  return ok({ rows: rows });
}


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