// Nootie Control Tower - two-way sync for the "ALL PO DATA" tab: sends this
// tab's edits in, then overwrites it with the fresh, full result.
const ENDPOINT_PULL = 'https://api.mynootie.com/webhooks/google-sheets/tracker'; // applies edits (same endpoint as the Tracker sync)
const ENDPOINT_PUSH = 'https://api.mynootie.com/webhooks/google-sheets/po-data'; // returns the current full PO dataset
const SYNC_KEY = 'PASTE_YOUR_SYNC_KEY_HERE';
const TAB_NAME = 'ALL PO DATA';

// Row 1 of the tab is a colored category band (PO INFO / DISPATCH &
// LOGISTICS / ...), row 2 is the real column headers, data starts at row 3.
// A category band color per group name - add one here if a new category
// name ever appears in the pushed data. Two of these are the app's own
// brand colors (Tailwind "nootie" orange-dark / gold-dark).
const CATEGORY_COLORS = {
  'PO INFO': '#334155',
  'DISPATCH & LOGISTICS': '#CC5620',
  APPOINTMENT: '#DB8F13',
  'GRN RECONCILIATION': '#047857',
};
// Light tint of each category's color, used on the header row so every
// column visibly belongs to its group without needing to look back up at
// the band row.
const CATEGORY_LIGHT_COLORS = {
  'PO INFO': '#E2E8F0',
  'DISPATCH & LOGISTICS': '#FDE9DC',
  APPOINTMENT: '#FDF0D5',
  'GRN RECONCILIATION': '#D1FAE5',
};

// Columns to force to plain text so Google Sheets never "smart"-converts a
// date-shaped or long numeric string inconsistently across rows (seen live:
// some PO dates rendered as "2026-09-03", others as "14/9/2026" because
// Sheets silently reinterpreted only the ones matching its locale pattern).
// Must be set BEFORE the values are written - formatting a cell that's
// already been auto-converted only changes its display, not its underlying
// type.
const PLAIN_TEXT_COLUMNS = [
  'PO Number', 'Order Date', 'Expriy Date', 'Dispatch Date', 'Recommended Dispatch Date',
  'Planned Dispatch Date', 'Invoice Number', 'Docket / AWB Number', 'Delivery Date',
  'Appointment Date', "Credit Note #", 'GRN Recorded Date',
];
const CURRENCY_COLUMNS = ['PO Value', 'Invoice Value (₹)', 'Shortage Value (₹)', 'Damage Value (₹)', 'Excess Value (₹)', 'Net Discrepancy (₹)', 'Credit Note Value (₹)'];
// Stored as e.g. 76.21 meaning 76.21% (not a 0-1 fraction) - a plain "append
// a %" format, not Sheets' native percentage type, which would divide by 100.
const PERCENT_COLUMNS = ['Fulfilment %', 'Fill Rate %'];
const QTY_COLUMNS = ['Total Quantity', 'Shortage Qty', 'Damage Qty', 'Excess Qty'];
// Status-like columns read better centered than left-aligned.
const CENTER_COLUMNS = ['PO Status', 'GRN Outcome', 'Dispatch Plan Status', 'Location Type', 'GRN Status', 'Status'];
// Dropdowns for the columns ops actually choose a value from when editing
// the sheet (everything else here is free text or a date).
const DROPDOWN_COLUMNS = {
  'GRN Status': ['', 'Pending', 'Completed'],
  Status: ['', 'Dispatched', 'Invoiced', 'In Transit', 'Delivered', 'RTO', 'Returned', 'Cancelled'],
};

// Colored status "badges" for PO Status and GRN Outcome - a simple 4-tier
// scheme (neutral / in-progress-on-brand / done / negative).
const PO_STATUS_BADGES = {
  RECEIVED: { bg: '#F1F5F9', fg: '#475569' },
  APPOINTMENT_REQUESTED: { bg: '#F1F5F9', fg: '#475569' },
  APPOINTMENT_CONFIRMED: { bg: '#F1F5F9', fg: '#475569' },
  READY_FOR_DISPATCH: { bg: '#F1F5F9', fg: '#475569' },
  DISPATCHED: { bg: '#FDE9DC', fg: '#CC5620' },
  IN_TRANSIT: { bg: '#FDE9DC', fg: '#CC5620' },
  DELIVERED: { bg: '#D1FAE5', fg: '#065F46' },
  GRN_PENDING: { bg: '#D1FAE5', fg: '#065F46' },
  RECONCILED: { bg: '#D1FAE5', fg: '#065F46' },
  CLOSED: { bg: '#D1FAE5', fg: '#065F46' },
  RETURNED: { bg: '#FEE2E2', fg: '#991B1B' },
  CANCELLED: { bg: '#FEE2E2', fg: '#991B1B' },
};
const GRN_OUTCOME_BADGES = {
  MATCHED: { bg: '#D1FAE5', fg: '#065F46' },
  SHORTAGE: { bg: '#FEE2E2', fg: '#991B1B' },
  DAMAGE: { bg: '#FEE2E2', fg: '#991B1B' },
  MISMATCHED: { bg: '#FEE2E2', fg: '#991B1B' },
  OTHER: { bg: '#FEF3C7', fg: '#92400E' },
  NO_GRN: { bg: '#F1F5F9', fg: '#475569' },
};

function addBadgeRules(rules, sheet, headers, columnName, badgeMap, lastRow) {
  var col = headers.indexOf(columnName) + 1;
  if (col === 0 || lastRow < 3) return;
  const range = sheet.getRange(3, col, lastRow - 2, 1);
  Object.keys(badgeMap).forEach(function (value) {
    const badge = badgeMap[value];
    rules.push(
      SpreadsheetApp.newConditionalFormatRule()
        .whenTextEqualTo(value)
        .setBackground(badge.bg)
        .setFontColor(badge.fg)
        .setBold(true)
        .setRanges([range])
        .build(),
    );
  });
}

function syncAllPoData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(TAB_NAME);
  if (!sheet) throw new Error('Tab not found: ' + TAB_NAME);
  const tz = ss.getSpreadsheetTimeZone();

  // 1) Send whatever's in the tab right now (including edits since the last
  // sync) so they get applied before the refresh below overwrites anything.
  // The backend finds the real header row on its own even with the band row
  // above it, so the whole sheet (as-is) is sent unchanged.
  if (sheet.getLastRow() > 2) {
    const rows = sheet.getDataRange().getValues().map(function (row) {
      return row.map(function (v) {
        return v instanceof Date ? Utilities.formatDate(v, tz, 'yyyy-MM-dd') : v;
      });
    });
    const applyRes = UrlFetchApp.fetch(ENDPOINT_PULL, {
      method: 'post',
      contentType: 'application/json',
      headers: { 'x-sync-key': SYNC_KEY },
      payload: JSON.stringify({ rows: rows }),
      muteHttpExceptions: true,
    });
    Logger.log('Apply: ' + applyRes.getResponseCode() + ' ' + applyRes.getContentText());
    // Give a small sheet's background apply a moment to finish before pulling
    // the refreshed data below. A very large sheet may still be applying when
    // this runs - any edits still mid-apply land on the NEXT sync instead.
    Utilities.sleep(5000);
  }

  // 2) Pull the full, current dataset and overwrite the tab with it.
  const refreshRes = UrlFetchApp.fetch(ENDPOINT_PUSH, {
    method: 'get',
    headers: { 'x-sync-key': SYNC_KEY },
    muteHttpExceptions: true,
  });
  if (refreshRes.getResponseCode() !== 200) {
    Logger.log('Refresh failed: ' + refreshRes.getResponseCode() + ' ' + refreshRes.getContentText());
    return;
  }
  const data = JSON.parse(refreshRes.getContentText());
  const colCount = data.headers.length;
  const lastRow = data.rows.length ? 2 + data.rows.length : 2;

  sheet.clear();
  sheet.clearConditionalFormatRules();

  // Force plain text on these columns BEFORE any values are written - must
  // happen first, see the comment on PLAIN_TEXT_COLUMNS above.
  PLAIN_TEXT_COLUMNS.forEach(function (name) {
    const c = data.headers.indexOf(name) + 1;
    if (c > 0) sheet.getRange(1, c, sheet.getMaxRows(), 1).setNumberFormat('@');
  });

  // Band row: category name in its first column, blank cells for the rest of
  // its span (about to be merged) - this low non-empty-cell count is exactly
  // what lets the backend's header-row detector skip past it automatically.
  const bandRow = [];
  data.categories.forEach(function (cat) {
    bandRow.push(cat.name);
    for (var i = 1; i < cat.span; i++) bandRow.push('');
  });
  sheet.getRange(1, 1, 1, colCount).setValues([bandRow]);
  sheet.getRange(2, 1, 1, colCount).setValues([data.headers]);
  if (data.rows.length) sheet.getRange(3, 1, data.rows.length, colCount).setValues(data.rows);

  // Number formats for currency / percent / quantity columns.
  CURRENCY_COLUMNS.forEach(function (name) {
    const c = data.headers.indexOf(name) + 1;
    if (c > 0 && data.rows.length) sheet.getRange(3, c, data.rows.length, 1).setNumberFormat('"₹"#,##0.00;[RED]-"₹"#,##0.00');
  });
  PERCENT_COLUMNS.forEach(function (name) {
    const c = data.headers.indexOf(name) + 1;
    if (c > 0 && data.rows.length) sheet.getRange(3, c, data.rows.length, 1).setNumberFormat('0.0"%"');
  });
  QTY_COLUMNS.forEach(function (name) {
    const c = data.headers.indexOf(name) + 1;
    if (c > 0 && data.rows.length) sheet.getRange(3, c, data.rows.length, 1).setNumberFormat('#,##0');
  });

  // Alignment: numbers/money right, statuses centered (everything else -
  // text, dates - stays left, the default).
  if (data.rows.length) {
    CURRENCY_COLUMNS.concat(PERCENT_COLUMNS, QTY_COLUMNS).forEach(function (name) {
      const c = data.headers.indexOf(name) + 1;
      if (c > 0) sheet.getRange(3, c, data.rows.length, 1).setHorizontalAlignment('right');
    });
    CENTER_COLUMNS.forEach(function (name) {
      const c = data.headers.indexOf(name) + 1;
      if (c > 0) sheet.getRange(3, c, data.rows.length, 1).setHorizontalAlignment('center');
    });
  }

  // Dropdowns on the columns ops picks a value from when editing. Delivery
  // Partner's list comes live from the backend (the real transporter
  // master), not a hardcoded array, so it can't go stale.
  if (data.rows.length) {
    Object.keys(DROPDOWN_COLUMNS).forEach(function (name) {
      const c = data.headers.indexOf(name) + 1;
      if (c === 0) return;
      const rule = SpreadsheetApp.newDataValidation().requireValueInList(DROPDOWN_COLUMNS[name], true).setAllowInvalid(true).build();
      sheet.getRange(3, c, data.rows.length, 1).setDataValidation(rule);
    });
    const deliveryPartnerCol = data.headers.indexOf('Delivery Partner') + 1;
    if (deliveryPartnerCol > 0 && data.transporters && data.transporters.length) {
      const partnerRule = SpreadsheetApp.newDataValidation().requireValueInList(data.transporters, true).setAllowInvalid(true).build();
      sheet.getRange(3, deliveryPartnerCol, data.rows.length, 1).setDataValidation(partnerRule);
    }
  }

  // Style the band: merge each category's span, color it, white bold
  // centered text, taller row.
  var col = 1;
  data.categories.forEach(function (cat) {
    const range = sheet.getRange(1, col, 1, cat.span);
    if (cat.span > 1) range.merge();
    range.setBackground(CATEGORY_COLORS[cat.name] || '#374151').setFontColor('#FFFFFF').setFontWeight('bold').setFontSize(10).setHorizontalAlignment('center');
    col += cat.span;
  });
  sheet.setRowHeight(1, 30);

  // Header row: each column tinted with a light version of its own
  // category's color, so the grouping reads clearly column-by-column, not
  // just from the band above. Bold dark text, bold orange underline across
  // the whole row ties it back to the brand.
  sheet.getRange(2, 1, 1, colCount).setFontWeight('bold').setFontColor('#334155').setBorder(null, null, true, null, null, null, '#EA6A2D', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  var headerCol = 1;
  data.categories.forEach(function (cat) {
    sheet.getRange(2, headerCol, 1, cat.span).setBackground(CATEGORY_LIGHT_COLORS[cat.name] || '#F3F4F6');
    headerCol += cat.span;
  });

  // Vertical dividers between categories, spanning the band + header + data
  // rows, so the grouping reads clearly even without the top band in view.
  var boundary = 0;
  data.categories.slice(0, -1).forEach(function (cat) {
    boundary += cat.span;
    sheet.getRange(1, boundary, lastRow, 1).setBorder(null, null, null, true, null, null, '#CBD5E1', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  });

  // Colored status badges, then a zebra-stripe rule last so it only fills in
  // cells the badge rules above didn't already color - earlier rules in this
  // list win ties, so order matters. Done via a formula rule rather than the
  // Banding API's row-banding, which turned out to silently ignore the
  // custom colors and keep its built-in grey theme.
  sheet.getBandings().forEach(function (b) {
    b.remove();
  });
  const rules = [];
  addBadgeRules(rules, sheet, data.headers, 'PO Status', PO_STATUS_BADGES, lastRow);
  addBadgeRules(rules, sheet, data.headers, 'GRN Outcome', GRN_OUTCOME_BADGES, lastRow);
  if (data.rows.length) {
    rules.push(
      SpreadsheetApp.newConditionalFormatRule()
        .whenFormulaSatisfied('=ISEVEN(ROW())')
        .setBackground('#FFF8EF')
        .setRanges([sheet.getRange(3, 1, data.rows.length, colCount)])
        .build(),
    );
  }
  sheet.setConditionalFormatRules(rules);

  // Freeze the band + header rows, hide gridlines, and size columns to fit
  // (with a floor so a narrow status/qty column isn't squeezed unreadably
  // thin). Can't also freeze a column here - the band row's merged cells
  // span multiple columns, and Sheets refuses to freeze a column that would
  // split a merge.
  sheet.setFrozenRows(2);
  sheet.setHiddenGridlines(true);
  sheet.autoResizeColumns(1, colCount);
  for (var i = 1; i <= colCount; i++) {
    if (sheet.getColumnWidth(i) < 90) sheet.setColumnWidth(i, 90);
  }

  Logger.log('Refreshed ' + data.rows.length + ' POs');
}