/**
 * PowerVoltage shared test-log backend.
 * Deploy this file as a Google Apps Script Web App:
 *   Execute as: Me
 *   Who has access: Anyone
 * Then paste the /exec URL into the Test Log setup wizard.
 */
const HEADERS = [
  'id','createdAt','updatedAt','testedAt','operator','runName','missions',
  'attachment','codeVersion','plannedScore','actualScore','runTimeSeconds',
  'result','failureCause','observations','nextChange','deleted'
];

function doGet() {
  return json_({ok:true, service:'PowerVoltage Test Logs', configured:!!props_().getProperty('spreadsheetId')});
}

function doPost(e) {
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(20000);
    const request = JSON.parse((e && e.postData && e.postData.contents) || '{}');
    const action = String(request.action || '');
    if (action === 'setup') return json_(setup_(request));
    requireAuth_(request.password);
    if (action === 'list') return json_(list_());
    if (action === 'save') return json_(save_(request.item));
    if (action === 'delete') return json_(delete_(request.id));
    return json_({ok:false,error:'Unknown action.'});
  } catch (error) {
    return json_({ok:false,error:error && error.message ? error.message : String(error)});
  } finally {
    try { lock.releaseLock(); } catch (_) {}
  }
}

function setup_(request) {
  const properties = props_();
  if (properties.getProperty('spreadsheetId')) throw new Error('This Apps Script is already connected to a spreadsheet. Use its team connection code instead.');
  const password = String(request.teamPassword || '');
  if (password.length < 6) throw new Error('Team password must be at least 6 characters.');
  const spreadsheetName = cleanName_(request.spreadsheetName, 'PowerVoltage Test Logs', 120);
  const worksheetName = cleanSheetName_(request.worksheetName || 'Test Logs');
  const spreadsheet = SpreadsheetApp.create(spreadsheetName);
  const sheet = spreadsheet.getSheets()[0];
  sheet.setName(worksheetName);
  sheet.getRange(1,1,1,HEADERS.length).setValues([HEADERS]).setBackground('#173d2a').setFontColor('#ffffff').setFontWeight('bold').setHorizontalAlignment('center').setWrap(true);
  sheet.setFrozenRows(1);
  [150,135,135,135,110,210,125,125,125,100,100,110,90,145,260,260,75].forEach((width,i)=>sheet.setColumnWidth(i+1,width));
  const folderId = extractFolderId_(request.folder);
  if (folderId) {
    const folder = DriveApp.getFolderById(folderId);
    DriveApp.getFileById(spreadsheet.getId()).moveTo(folder);
  }
  const salt = Utilities.getUuid() + Utilities.getUuid();
  properties.setProperties({spreadsheetId:spreadsheet.getId(),worksheetName,passwordSalt:salt,passwordHash:hash_(salt,password),createdAt:new Date().toISOString()});
  return {ok:true,spreadsheetUrl:spreadsheet.getUrl(),spreadsheetName,worksheetName};
}

function list_() {
  const sheet = sheet_();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return {ok:true,items:[],spreadsheetUrl:sheet.getParent().getUrl()};
  const rows = sheet.getRange(2,1,lastRow-1,HEADERS.length).getValues();
  const items = rows.filter(row=>row[0] && row[16] !== true).map(row=>({
    id:String(row[0]),createdAt:value_(row[1]),updatedAt:value_(row[2]),testedAt:value_(row[3]),operator:String(row[4]||''),runName:String(row[5]||''),
    missions:parseMissions_(row[6]),attachment:String(row[7]||''),codeVersion:String(row[8]||''),plannedScore:blankOrNumber_(row[9]),actualScore:blankOrNumber_(row[10]),
    runTime:blankOrNumber_(row[11]),result:String(row[12]||''),failureCause:String(row[13]||''),observations:String(row[14]||''),nextChange:String(row[15]||'')
  })).sort((a,b)=>String(b.testedAt).localeCompare(String(a.testedAt)));
  return {ok:true,items,spreadsheetUrl:sheet.getParent().getUrl()};
}

function save_(item) {
  if (!item || !item.id) throw new Error('Test ID is required.');
  const sheet = sheet_();
  const now = new Date().toISOString();
  const found = findRow_(sheet,String(item.id));
  const createdAt = found ? String(sheet.getRange(found,2).getValue() || now) : String(item.createdAt || now);
  const row = [String(item.id),createdAt,now,String(item.testedAt||''),String(item.operator||''),String(item.runName||''),JSON.stringify(item.missions||[]),String(item.attachment||''),String(item.codeVersion||''),numberOrBlank_(item.plannedScore),numberOrBlank_(item.actualScore),numberOrBlank_(item.runTime),String(item.result||''),String(item.failureCause||''),String(item.observations||''),String(item.nextChange||''),false];
  if (found) sheet.getRange(found,1,1,HEADERS.length).setValues([row]); else sheet.appendRow(row);
  return {ok:true,id:String(item.id),updatedAt:now};
}

function delete_(id) {
  const sheet = sheet_();
  const row = findRow_(sheet,String(id||''));
  if (!row) throw new Error('Test record was not found.');
  sheet.getRange(row,3).setValue(new Date().toISOString());
  sheet.getRange(row,17).setValue(true);
  return {ok:true,id:String(id)};
}

function requireAuth_(password) {
  const properties = props_();
  const salt = properties.getProperty('passwordSalt');
  const expected = properties.getProperty('passwordHash');
  if (!salt || !expected) throw new Error('This backend has not been set up yet.');
  if (hash_(salt,String(password||'')) !== expected) throw new Error('Incorrect team password.');
}

function sheet_() {
  const properties = props_();
  const id = properties.getProperty('spreadsheetId');
  const name = properties.getProperty('worksheetName');
  if (!id || !name) throw new Error('This backend has not been set up yet.');
  const sheet = SpreadsheetApp.openById(id).getSheetByName(name);
  if (!sheet) throw new Error('The configured worksheet was renamed or deleted.');
  return sheet;
}

function findRow_(sheet,id) {
  if (!id || sheet.getLastRow() < 2) return 0;
  const found = sheet.getRange(2,1,sheet.getLastRow()-1,1).createTextFinder(id).matchEntireCell(true).findNext();
  return found ? found.getRow() : 0;
}
function props_(){ return PropertiesService.getScriptProperties(); }
function hash_(salt,password){ return Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256,salt+'\u0000'+password).map(b=>('0'+(b&255).toString(16)).slice(-2)).join(''); }
function extractFolderId_(value){ const text=String(value||'').trim(); if(!text)return ''; const match=text.match(/\/folders\/([A-Za-z0-9_-]+)/); const id=match?match[1]:text; if(!/^[A-Za-z0-9_-]{10,}$/.test(id))throw new Error('Google Drive folder link or ID is not valid.'); return id; }
function cleanName_(value,fallback,max){ const text=String(value||fallback).trim(); if(!text)throw new Error('Spreadsheet name is required.'); return text.slice(0,max); }
function cleanSheetName_(value){ const text=String(value||'').trim(); if(!text)throw new Error('Worksheet name is required.'); if(/[\[\]*?:\\/]/.test(text)||text.length>100)throw new Error('Worksheet name contains an invalid character or is too long.'); return text; }
function parseMissions_(value){ try{ const a=JSON.parse(String(value||'[]')); return Array.isArray(a)?a.map(Number).filter(Number.isFinite):[]; }catch(_){ return String(value||'').split(/[, ]+/).map(Number).filter(Number.isFinite); } }
function value_(value){ return value instanceof Date ? value.toISOString() : String(value||''); }
function numberOrBlank_(value){ return value===''||value===null||value===undefined ? '' : Number(value); }
function blankOrNumber_(value){ return value===''||value===null||value===undefined ? '' : Number(value); }
function json_(value){ return ContentService.createTextOutput(JSON.stringify(value)).setMimeType(ContentService.MimeType.JSON); }