// ══════════════════════════════════════════════════════
//  IBI Multi-Platform Pricing Calculator — GAS v4.0
//  India Business International · Dr. T. Sasimurugan
// ══════════════════════════════════════════════════════
// FEATURES:
// • Auto-creates separate tabs per platform
// • Master "📊 Summary" tab with live aggregates
// • Platform-wise headers with branded purple styling
// • Auto-colored Net Profit cells (green/red)
// • Validates incoming data, logs errors
// • v4: AI product-name extraction proxy (DeepSeek /
//   Gemini / OpenAI / Claude) — keys stored in Script
//   Properties on YOUR Google account, never in browser
// ══════════════════════════════════════════════════════

var SUMMARY_TAB  = '📊 Summary';
var BRAND_COLOR  = '#7c3aed';
var BRAND_LIGHT  = '#ede9fe';
var HEADER_FONT  = '#ffffff';
var PROFIT_GREEN = '#16a34a';
var LOSS_RED     = '#dc2626';

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    if (data && data.action) return handleAiAction(data);
    if (!data || !data.headers || !data.values) {
      return jsonResp({ status: 'error', message: 'Missing data' });
    }
    var platform = (data.platform || 'Unknown').toString().trim();
    var ss       = SpreadsheetApp.getActiveSpreadsheet();
    var sheet    = getOrCreatePlatformSheet(ss, platform, data.headers);

    sheet.appendRow(data.values);
    var newRow = sheet.getLastRow();
    styleDataRow(sheet, newRow, data.values.length);
    colorProfitCell(sheet, newRow, data.headers, data.values);
    sheet.autoResizeColumns(1, data.headers.length);
    updateSummaryTab(ss);

    return jsonResp({ status:'ok', tab: sheet.getName(), row: newRow });
  } catch(err) {
    return jsonResp({ status:'error', message: err.toString() });
  }
}

function doGet(e) {
  return jsonResp({ status:'ok', message:'IBI Calculator endpoint active' });
}

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

function getOrCreatePlatformSheet(ss, platform, headers) {
  var sheet = ss.getSheetByName(platform);
  if (!sheet) {
    var summary = ss.getSheetByName(SUMMARY_TAB);
    sheet = summary
      ? ss.insertSheet(platform, summary.getIndex())
      : ss.insertSheet(platform);
  }
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(headers);
    sheet.getRange(1, 1, 1, headers.length)
      .setFontWeight('bold').setBackground(BRAND_COLOR)
      .setFontColor(HEADER_FONT).setFontFamily('Roboto')
      .setFontSize(10).setHorizontalAlignment('center')
      .setVerticalAlignment('middle').setWrap(true);
    sheet.setFrozenRows(1);
    sheet.setRowHeight(1, 40);
    sheet.setTabColor(BRAND_COLOR);
  }
  return sheet;
}

function styleDataRow(sheet, row, cols) {
  var rng = sheet.getRange(row, 1, 1, cols);
  rng.setFontFamily('Roboto Mono').setFontSize(10);
  if (row % 2 === 0) rng.setBackground(BRAND_LIGHT);
}

function colorProfitCell(sheet, row, headers, values) {
  var idx = -1;
  for (var i = 0; i < headers.length; i++) {
    if (headers[i] && headers[i].toString().indexOf('Net Profit') !== -1) {
      idx = i + 1; break;
    }
  }
  if (idx === -1) return;
  var v = parseFloat(values[idx - 1]);
  if (isNaN(v)) return;
  sheet.getRange(row, idx)
    .setFontColor(v < 0 ? LOSS_RED : PROFIT_GREEN)
    .setFontWeight('bold');
}

function updateSummaryTab(ss) {
  var summary = ss.getSheetByName(SUMMARY_TAB);
  if (!summary) {
    summary = ss.insertSheet(SUMMARY_TAB, 0);
    summary.setTabColor('#16a34a');
  }
  summary.clear();
  summary.getRange('A1:F1').merge()
    .setValue('📊 IBI Pricing Calculator — Live Summary')
    .setFontWeight('bold').setFontSize(14)
    .setBackground(BRAND_COLOR).setFontColor(HEADER_FONT)
    .setHorizontalAlignment('center');
  summary.setRowHeight(1, 32);
  summary.getRange('A2:F2').merge()
    .setValue('Last refreshed: ' + new Date().toLocaleString('en-IN', {timeZone:'Asia/Kolkata'}))
    .setFontSize(9).setFontColor('#666').setHorizontalAlignment('center');
  var hdrs = ['Platform','# Calcs','Avg Profit (₹)','Total Profit (₹)','Avg ROI (%)','Last Updated'];
  summary.getRange(4,1,1,hdrs.length).setValues([hdrs])
    .setFontWeight('bold').setBackground('#f3eeff').setFontColor(BRAND_COLOR)
    .setFontFamily('Roboto').setFontSize(10).setHorizontalAlignment('center');

  var sheets = ss.getSheets(), rowIdx = 5;
  var gC = 0, gP = 0, gR = 0, gN = 0;

  for (var s = 0; s < sheets.length; s++) {
    var sh = sheets[s], name = sh.getName();
    if (name === SUMMARY_TAB || sh.getLastRow() < 2) continue;
    var hRow = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
    var pIdx = findCol(hRow,'Net Profit'),
        rIdx = findCol(hRow,'ROI'),
        dIdx = findCol(hRow,'Date');
    if (pIdx === -1) continue;
    var data = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
    var c = 0, p = 0, rs = 0, rc = 0, ld = '';
    for (var r = 0; r < data.length; r++) {
      var pv = parseFloat(data[r][pIdx]), rv = parseFloat(data[r][rIdx]);
      if (!isNaN(pv)) { c++; p += pv; }
      if (!isNaN(rv)) { rs += rv; rc++; }
      if (dIdx !== -1 && data[r][dIdx]) ld = data[r][dIdx].toString();
    }
    if (c === 0) continue;
    var avgP = p/c, avgR = rc>0 ? rs/rc : 0;
    summary.getRange(rowIdx,1,1,6).setValues([[name,c,avgP.toFixed(2),p.toFixed(2),avgR.toFixed(1)+'%',ld]]);
    summary.getRange(rowIdx,4).setFontColor(p>=0?PROFIT_GREEN:LOSS_RED).setFontWeight('bold');
    rowIdx++; gC += c; gP += p; gR += rs; gN += rc;
  }

  if (gC > 0) {
    summary.getRange(rowIdx,1,1,6).setValues([['── TOTAL ──',gC,(gP/gC).toFixed(2),gP.toFixed(2),(gN>0?(gR/gN).toFixed(1):'0.0')+'%','']])
      .setFontWeight('bold').setBackground('#f0f1f6').setFontColor(BRAND_COLOR);
    summary.getRange(rowIdx,4).setFontColor(gP>=0?PROFIT_GREEN:LOSS_RED);
  }
  summary.autoResizeColumns(1,6);
  summary.setColumnWidth(1,160);
  summary.setColumnWidth(6,180);
}

function findCol(hdr, key) {
  var s = key.toLowerCase();
  for (var i = 0; i < hdr.length; i++)
    if (hdr[i] && hdr[i].toString().toLowerCase().indexOf(s) !== -1) return i;
  return -1;
}

function refreshSummaryNow() {
  updateSummaryTab(SpreadsheetApp.getActiveSpreadsheet());
}

// ══════════ AI EXTRACTION PROXY (v4) ══════════
// Keys live in Script Properties on YOUR Google
// account — the browser never sees them.
var AI_PROVIDERS = {
  deepseek: 'AI_KEY_DEEPSEEK',
  gemini  : 'AI_KEY_GEMINI',
  openai  : 'AI_KEY_OPENAI',
  claude  : 'AI_KEY_CLAUDE'
};

// RUN THIS ONCE from the editor (▶ Run) after pasting this code —
// it forces Google's "Connect to an external service" permission
// prompt, which AI extraction needs.
function authorizeExternal() {
  UrlFetchApp.fetch('https://www.google.com');
  Logger.log('External request permission granted');
}

function handleAiAction(data) {
  try {
    if (data.action === 'save_ai_key') return saveAiKey_(data);
    if (data.action === 'ai_status')   return aiStatus_();
    if (data.action === 'ai_extract')  return aiExtract_(data);
    return jsonResp({ status:'error', message:'Unknown action' });
  } catch (err) {
    return jsonResp({ status:'error', message: err.toString() });
  }
}

function saveAiKey_(d) {
  var prop = AI_PROVIDERS[d.provider];
  if (!prop) return jsonResp({ status:'error', message:'Unknown provider' });
  var sp = PropertiesService.getScriptProperties();
  if (!d.key) { sp.deleteProperty(prop); return jsonResp({ status:'ok', message:d.provider + ' key removed' }); }
  sp.setProperty(prop, String(d.key).trim());
  return jsonResp({ status:'ok', message:d.provider + ' key saved securely in Apps Script' });
}

function aiStatus_() {
  var sp = PropertiesService.getScriptProperties(), keys = {};
  for (var k in AI_PROVIDERS) keys[k] = !!sp.getProperty(AI_PROVIDERS[k]);
  return jsonResp({ status:'ok', keys: keys });
}

function aiExtract_(d) {
  var prop = AI_PROVIDERS[d.provider];
  if (!prop) return jsonResp({ status:'error', message:'Unknown provider' });
  var key = PropertiesService.getScriptProperties().getProperty(prop);
  if (!key) return jsonResp({ status:'error', message:'No ' + d.provider + ' API key saved. Save it in the AI settings first.' });
  var prompt = 'You are given OCR text captured from an Indian e-commerce seller dashboard ' +
    '(Amazon Seller Central inventory or order page, Flipkart/Shopsy Seller Hub, Meesho ' +
    'Supplier Panel, or ShopClues Store Manager). The OCR interleaves table columns, so ' +
    'product names may be broken across lines and mixed with other data. Extract EVERY ' +
    'distinct product listing. The seller brands are exactly "iINTELLIGENCEi" and "IBI" - ' +
    'preserve that exact spelling in titles. Respond with ONLY a JSON array - no markdown fences, no ' +
    'commentary. Each element: {"title":"full clean product name, OCR errors fixed",' +
    '"id":"ASIN / FSN / Product ID if visible, else empty string",' +
    '"price": main selling or listing price as number or null,' +
    '"fees": total fees / charges as number or null,' +
    '"settlement": bank settlement / net payout as number or null}' +
    '\n\nOCR TEXT:\n' + String(d.text || '').slice(0, 6000);
  var out;
  if (d.provider === 'gemini')      out = callGemini_(key, prompt);
  else if (d.provider === 'claude') out = callClaude_(key, prompt);
  else if (d.provider === 'deepseek')
    // 'deepseek-flash' = DeepSeek V4.1 Flash, the canonical ID (the older
    // 'deepseek-chat' / 'deepseek-v4-flash' aliases still resolve here).
    // thinking:disabled because v4 defaults to THINKING at high effort, and
    // this call only lifts already-OCR'd fields into a JSON array — there is
    // nothing to reason about, and the reasoning pass runs before the first
    // character of the answer. (Reasoning tokens also share the output budget.)
    out = callOpenAiCompat_('https://api.deepseek.com/chat/completions', 'deepseek-flash', key, prompt,
                            { thinking: { type: 'disabled' } });
  else
    out = callOpenAiCompat_('https://api.openai.com/v1/chat/completions', 'gpt-4o-mini', key, prompt);
  var m = String(out).match(/\[[\s\S]*\]/);
  if (!m) return jsonResp({ status:'error', message:'AI response contained no JSON array' });
  return jsonResp({ status:'ok', provider:d.provider, products: JSON.parse(m[0]) });
}

// extra: optional provider-specific body fields (DeepSeek passes thinking here).
// Kept as a parameter so the shared OpenAI path is not sent a field it would reject.
function callOpenAiCompat_(url, model, key, prompt, extra) {
  var body = { model:model, temperature:0, messages:[{ role:'user', content:prompt }] };
  if (extra) { for (var k in extra) body[k] = extra[k]; }
  var res = UrlFetchApp.fetch(url, {
    method:'post', contentType:'application/json', muteHttpExceptions:true,
    headers:{ Authorization:'Bearer ' + key },
    payload: JSON.stringify(body)
  });
  var j = JSON.parse(res.getContentText());
  if (j.error) throw new Error(j.error.message || 'API error');
  return j.choices[0].message.content;
}

function callGemini_(key, prompt) {
  var res = UrlFetchApp.fetch(
    'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=' + encodeURIComponent(key), {
    method:'post', contentType:'application/json', muteHttpExceptions:true,
    payload: JSON.stringify({ contents:[{ parts:[{ text:prompt }] }] })
  });
  var j = JSON.parse(res.getContentText());
  if (j.error) throw new Error(j.error.message || 'Gemini API error');
  return j.candidates[0].content.parts[0].text;
}

function callClaude_(key, prompt) {
  var res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method:'post', contentType:'application/json', muteHttpExceptions:true,
    headers:{ 'x-api-key': key, 'anthropic-version':'2023-06-01' },
    payload: JSON.stringify({ model:'claude-haiku-4-5-20251001', max_tokens:2048, messages:[{ role:'user', content:prompt }] })
  });
  var j = JSON.parse(res.getContentText());
  if (j.error) throw new Error(j.error.message || 'Claude API error');
  return j.content[0].text;
}