// ══════════════════════════════════════════════════════
//  IBI Multi-Platform Pricing Calculator — GAS v3.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
// ══════════════════════════════════════════════════════

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.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());
}