/**
 * GOOGLE APPS SCRIPT: AMA Sync (Auto-Update Version)
 * Paste this into Google Sheets > Extensions > Apps Script
 */

var API_URL = "https://ama.doughrear.com/api/sync_google_sheets.php";

/**
 * SETUP FUNCTION: Run this ONCE to enable auto-update every 20 mins
 */
function setupAutoUpdate() {
  // Delete existing triggers to avoid duplicates
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  // Create a new trigger: Run syncData every 15 minutes
  ScriptApp.newTrigger('syncData')
      .timeBased()
      .everyMinutes(15)
      .create();

  SpreadsheetApp.getActiveSpreadsheet().toast("স্বয়ংক্রিয় আপডেট চালু হয়েছে! এখন থেকে প্রতি ১৫ মিনিট পর পর ডাটা অটো আপডেট হবে।", "Success 🔄", 10);
}

function doGet() {
  try {
    var result = syncData();
    return ContentService.createTextOutput("Sync Completed Successfully! " + result.message)
      .setMimeType(ContentService.MimeType.TEXT);
  } catch (e) {
    return ContentService.createTextOutput("Sync Failed: " + e.toString())
      .setMimeType(ContentService.MimeType.TEXT);
  }
}

function syncData() {
  var response = UrlFetchApp.fetch(API_URL);
  var json = JSON.parse(response.getContentText());

  if (json.status !== "success") {
    throw new Error(json.message || "API returned error status.");
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = json.data;

  var tables = ["customers", "bills", "payments", "loads", "chalan_distributions", "employees"];

  tables.forEach(function(tableName) {
    if (data[tableName] && data[tableName].length > 0) {
      updateTab(ss, tableName, data[tableName]);
    }
  });

  return { message: "Updated " + tables.length + " tabs." };
}

function updateTab(ss, sheetName, rows) {
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
  }

  sheet.clear();
  sheet.clearFormats();

  var headers = Object.keys(rows[0]);
  sheet.appendRow(headers);

  var dataValues = rows.map(function(row) {
    return headers.map(function(header) {
      return row[header];
    });
  });

  var dataRange = sheet.getRange(2, 1, dataValues.length, headers.length);
  dataRange.setValues(dataValues);

  // Header Styling
  var headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setFontWeight("bold").setBackground("#1e293b").setFontColor("#ffffff");
  headerRange.setVerticalAlignment("middle").setHorizontalAlignment("center");

  // Zebra Stripes
  dataRange.setBackground("#ffffff");
  for (var i = 1; i <= dataValues.length; i++) {
    if (i % 2 === 0) {
      sheet.getRange(i + 1, 1, 1, headers.length).setBackground("#f8fafc");
    }
  }

  sheet.getRange(1, 1, dataValues.length + 1, headers.length).setBorder(true, true, true, true, true, true, "#e2e8f0", SpreadsheetApp.BorderStyle.SOLID);
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, headers.length);
  sheet.setRowHeights(1, dataValues.length + 1, 30);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('🔄 AMA Sync')
      .addItem('Sync Now (সব তথ্য আপডেট করুন)', 'syncData')
      .addSeparator()
      .addItem('Enable Auto-Update (২০ মিনিট পর পর অটো আপডেট)', 'setupAutoUpdate')
      .addToUi();
}