// Run setup in YOUR Google account. Deploy as Web app: Execute as Me; access Anyone.
// Requests require an HMAC signature. Do not publish spreadsheet access.
const FOLDER_ID='17wKitAnNScqwYJFQJyyNxk7uO9RXckDG';
const HEADERS=['ID','Created UTC','Product','Grade','Quantity','Unit','Destination','Delivery basis','Delivery window','Company','Contact','Email','Phone','Specification','Customer notes','Status','Assignee','Next action','Due date','Updated UTC','CRM comments','Source','CRM revision'];
function setup(){
  const lock=LockService.getScriptLock();lock.waitLock(30000);
  try{
    const props=PropertiesService.getScriptProperties(),folder=DriveApp.getFolderById(FOLDER_ID);
    let id=props.getProperty('ABEX_SHEET_ID');
    if(!id){const book=SpreadsheetApp.create('ABEX — Заявки');id=book.getId();props.setProperty('ABEX_SHEET_ID',id);}
    DriveApp.getFileById(id).moveTo(folder);
    const book=SpreadsheetApp.openById(id);let sheet=book.getSheetByName('ABEX');
    if(!sheet){sheet=book.getSheets()[0];if(sheet.getLastRow()===0)sheet.setName('ABEX');else sheet=book.insertSheet('ABEX');}
    if(sheet.getLastRow()===0){sheet.getRange(1,1,1,HEADERS.length).setValues([HEADERS]).setFontWeight('bold').setBackground('#004054').setFontColor('#ffffff');sheet.setFrozenRows(1);sheet.hideColumns(23);}
    if(!props.getProperty('ABEX_SECRET'))props.setProperty('ABEX_SECRET',Utilities.getUuid()+Utilities.getUuid());
    console.log('Table: https://docs.google.com/spreadsheets/d/'+id+'/edit');
    // View ABEX_SECRET privately in Project Settings > Script Properties.
  }finally{lock.releaseLock();}
}
function response_(value){return ContentService.createTextOutput(JSON.stringify(value)).setMimeType(ContentService.MimeType.JSON);}
function doGet(){return response_({ok:false,error:'Signed POST required'});}
function doPost(e){
  let lock;
  try{
    if(!e||!e.postData||e.postData.contents.length>1000000)return response_({ok:false});
    const body=JSON.parse(e.postData.contents),props=PropertiesService.getScriptProperties(),secret=props.getProperty('ABEX_SECRET');
    if(!secret||typeof body.payload!=='string'||!/^[a-f0-9]{64}$/.test(body.signature||''))return response_({ok:false});
    const expected=Utilities.computeHmacSha256Signature(body.payload,secret,Utilities.Charset.UTF_8).map(x=>('0'+((x+256)%256).toString(16)).slice(-2)).join('');
    let mismatch=0;for(let i=0;i<64;i++)mismatch|=expected.charCodeAt(i)^body.signature.charCodeAt(i);
    if(mismatch)return response_({ok:false});
    const payload=JSON.parse(body.payload);
    if(!Number.isInteger(payload.timestamp)||Math.abs(Date.now()/1000-payload.timestamp)>300||!Array.isArray(payload.rows)||payload.rows.length>20)return response_({ok:false});
    for(const r of payload.rows)if(!/^ABEX-\d{6}-[A-F0-9]{8}$/.test(r.id)||!Number.isInteger(r.revision)||r.revision<1||!Array.isArray(r.values)||r.values.length!==22||r.values[0]!==r.id||r.values.some(v=>typeof v!=='string'&&typeof v!=='number'))return response_({ok:false});
    lock=LockService.getScriptLock();lock.waitLock(30000);
    const sid=props.getProperty('ABEX_SHEET_ID');if(!sid)return response_({ok:false});
    const file=DriveApp.getFileById(sid),parents=file.getParents();let inFolder=false;
    while(parents.hasNext())if(parents.next().getId()===FOLDER_ID)inFolder=true;
    if(!inFolder)return response_({ok:false});
    const sheet=SpreadsheetApp.openById(sid).getSheetByName('ABEX');
    if(!sheet||sheet.getRange(1,1).getValue()!=='ID')return response_({ok:false});
    const all=sheet.getDataRange().getValues(),ids=new Map();
    for(let i=1;i<all.length;i++)if(all[i][0])ids.set(String(all[i][0]),{row:i+1,revision:Number(all[i][22])||0});
    const ack=[];
    for(const r of payload.rows){
      const old=ids.get(r.id);
      if(!old||old.revision<r.revision){
        const values=r.values.map(v=>typeof v==='string'&&/^[\s]*[=+@-]/.test(v)?"'"+v:v);values.push(r.revision);
        const row=old?old.row:sheet.getLastRow()+1;
        sheet.getRange(row,1,1,23).setValues([values]);ids.set(r.id,{row:row,revision:r.revision});
      }
      ack.push({id:r.id,revision:r.revision});
    }
    SpreadsheetApp.flush();return response_({ok:true,spreadsheet_id:sid,folder_id:FOLDER_ID,ack:ack});
  }catch(error){return response_({ok:false,error:'Write not confirmed'});}
  finally{if(lock)lock.releaseLock();}
}