// ===== Main Configuration =====
const MAIN_SHEET = 'Sheet1';
const ALIAS_SHEET = 'Sheet2';
const LANDING_SHEET = 'Sheet3';

// ===== UI Menu Setup =====
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('🔄 Domain Sync Manual');

  menu
    .addItem('Map domain', 'syncData')
    .addSeparator()
    // .addItem('📊 Sync Status', 'showSyncStatus')
    // .addItem('⚙️ Setup Sync', 'setupSync')
    // .addSeparator()
    // .addItem('🧪 Test Sync', 'testSync')
    // .addItem('📋 Show Last Sync Time', 'getLastSyncTime')
    .addToUi();
}

// ===== Main Sync Function =====
function syncData() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const mainSheet = ss.getSheetByName(MAIN_SHEET);
    const aliasSheet = ss.getSheetByName(ALIAS_SHEET);
    const landingSheet = ss.getSheetByName(LANDING_SHEET);

    if (!mainSheet || !aliasSheet || !landingSheet) {
      throw new Error('One or more sheets not found. Please check sheet names.');
    }

    SpreadsheetApp.getUi().alert('🔄 Sync in progress...\n\nPlease wait while data is being synchronized.');

    const mainData = mainSheet.getDataRange().getValues();
    if (mainData.length <= 1) {
      SpreadsheetApp.getUi().alert('ℹ️ No data found to sync.');
      return;
    }

    const headerRow = mainData[0];
    const dataRows = mainData.slice(1);
    // const mainColIndices = findMainSheetColumns(headerRow);
    const colIndices = {
      stt: headerRow.indexOf('STT'),
      maDomain: headerRow.indexOf('Mã Domain'),
      tenDomain: headerRow.indexOf('Tên Domain'),
      loaiDomain: headerRow.indexOf('Loại Domain'),
      nhomSanPham: headerRow.indexOf('Nhóm Sản phẩm'),
    };

    // const aliasCount = syncToAliasSheet(dataRows, mainColIndices, aliasSheet);
    // const landingCount = syncToLandingSheet(dataRows, mainColIndices, landingSheet);
    const mappingCon1 = [];
    const mappingCon2 = [];

    dataRows.forEach(row => {
      const maDomain = row[colIndices.maDomain]?.toString().trim();
      if (!maDomain) return;

      const entry = {
        stt: row[colIndices.stt],
        maDomain,
        tenDomain: row[colIndices.tenDomain],
        nhomSanPham: row[colIndices.nhomSanPham],
      };

      const loaiDomain = row[colIndices.loaiDomain]?.toString().trim();
      if (loaiDomain === 'Landing Page') {
        mappingCon1.push(entry);
      } else if (loaiDomain === 'Alias') {
        mappingCon2.push(entry);
      }
    });

  updateTargetSheet(landingSheet, mappingCon1, 'Mapping_Ladipage_NV');
  updateTargetSheet(aliasSheet, mappingCon2, 'Mapping_Domain_Alias');

    PropertiesService.getScriptProperties().setProperty('lastSyncTime', new Date().toString());

    SpreadsheetApp.getUi().alert(`✅ Sync completed successfully!
⏰ Sync completed at: ${new Date().toLocaleString()}`);

// 📊 Results:
// • Alias records: ${aliasCount}
// • Landing Page records: ${landingCount}
// • Total records processed: ${aliasCount + landingCount}
  } catch (error) {
    SpreadsheetApp.getUi().alert('❌ Sync failed!\n\nError: ' + error.toString());
  }
}

function updateTargetSheet(sheet, newData, sheetType) {
  const existingData = sheet.getDataRange().getValues();
  const headerRow = existingData[0];
  const existingRows = existingData.slice(1);
  const targetColIndices = findTargetSheetColumns(headerRow, sheetType);

  const maDomainToRowIndex = new Map();
  existingRows.forEach((row, idx) => {
    const ma = row[targetColIndices.maDomain];
    if (ma) {
      maDomainToRowIndex.set(ma.toString().trim(), idx + 2); // +2 vì có header + base-1
    }
  });

  const newMaDomains = new Set(newData.map(d => d.maDomain.toString().trim()));

  // Cập nhật hoặc thêm mới
  newData.forEach(item => {
    const maDomain = item.maDomain.toString().trim();
    const rowIndex = maDomainToRowIndex.get(maDomain);
    if (rowIndex) {
      if (targetColIndices.stt !== undefined)
        sheet.getRange(rowIndex, targetColIndices.stt + 1).setValue(item.stt);
      if (targetColIndices.tenDomain !== undefined)
        sheet.getRange(rowIndex, targetColIndices.tenDomain + 1).setValue(item.tenDomain);
      if (targetColIndices.nhomSanPham !== undefined)
        sheet.getRange(rowIndex, targetColIndices.nhomSanPham + 1).setValue(item.nhomSanPham);
    } else {
      const newRow = new Array(headerRow.length).fill('');
      if (targetColIndices.stt !== undefined) newRow[targetColIndices.stt] = item.stt;
      if (targetColIndices.maDomain !== undefined) newRow[targetColIndices.maDomain] = item.maDomain;
      if (targetColIndices.tenDomain !== undefined) newRow[targetColIndices.tenDomain] = item.tenDomain;
      if (targetColIndices.nhomSanPham !== undefined) newRow[targetColIndices.nhomSanPham] = item.nhomSanPham;
      sheet.appendRow(newRow);
    }
  });

  // Xoá dòng không còn trong sheet cha
  for (let [ma, rowIndex] of maDomainToRowIndex.entries()) {
    if (!newMaDomains.has(ma)) {
      sheet.deleteRow(rowIndex);
      for (let [otherMa, idx] of maDomainToRowIndex.entries()) {
        if (idx > rowIndex) {
          maDomainToRowIndex.set(otherMa, idx - 1);
        }
      }
    }
  }
}

// Tìm vị trí cột trong sheet con
function findTargetSheetColumns(headerRow, sheetType) {
  return {
    stt: headerRow.indexOf('STT'),
    maDomain: headerRow.indexOf('Mã Domain'),
    tenDomain: headerRow.indexOf('Tên Domain'),
    nhomSanPham: headerRow.indexOf('Nhóm Sản phẩm'),
  };
}


// // ===== Show Sync Status =====
// function showSyncStatus() {
//   try {
//     const ss = SpreadsheetApp.getActiveSpreadsheet();
//     const mainSheet = ss.getSheetByName(MAIN_SHEET);
//     const aliasSheet = ss.getSheetByName(ALIAS_SHEET);
//     const landingSheet = ss.getSheetByName(LANDING_SHEET);

//     if (!mainSheet || !aliasSheet || !landingSheet) {
//       SpreadsheetApp.getUi().alert('❌ One or more sheets not found!');
//       return;
//     }

//     const mainCount = Math.max(0, mainSheet.getLastRow() - 1);
//     const aliasCount = Math.max(0, aliasSheet.getLastRow() - 1);
//     const landingCount = Math.max(0, landingSheet.getLastRow() - 1);

//     const lastSync = PropertiesService.getScriptProperties().getProperty('lastSyncTime') || 'Never';

//     const statusMessage = `📊 Sync Status Report

// 📋 Record Counts:
// • Main Sheet (${MAIN_SHEET}): ${mainCount} records
// • Alias Sheet (${ALIAS_SHEET}): ${aliasCount} records
// • Landing Sheet (${LANDING_SHEET}): ${landingCount} records

// ⏰ Last Sync: ${lastSync}

// 🔗 Sheets Status:
// • Main Sheet: ${mainSheet ? '✅ Found' : '❌ Missing'}
// • Alias Sheet: ${aliasSheet ? '✅ Found' : '❌ Missing'}
// • Landing Sheet: ${landingSheet ? '✅ Found' : '❌ Missing'}`;

//     SpreadsheetApp.getUi().alert(statusMessage);
//   } catch (error) {
//     SpreadsheetApp.getUi().alert('❌ Error getting status: ' + error.toString());
//   }
// }

// // ===== Setup Function =====
// function setupSync() {
//   try {
//     const ss = SpreadsheetApp.getActiveSpreadsheet();
//     const mainSheet = ss.getSheetByName(MAIN_SHEET);
//     const aliasSheet = ss.getSheetByName(ALIAS_SHEET);
//     const landingSheet = ss.getSheetByName(LANDING_SHEET);

//     let missingSheets = [];
//     if (!mainSheet) missingSheets.push(MAIN_SHEET);
//     if (!aliasSheet) missingSheets.push(ALIAS_SHEET);
//     if (!landingSheet) missingSheets.push(LANDING_SHEET);

//     if (missingSheets.length > 0) {
//       SpreadsheetApp.getUi().alert(`❌ Missing Required Sheets:
// ${missingSheets.map(sheet => '• ' + sheet).join('\n')}

// Please create these sheets before running setup.`);
//       return;
//     }

//     syncData();

//     SpreadsheetApp.getUi().alert(`✅ Domain Sync Setup Completed!

// 🎯 What's Ready:
// • Custom menu "🔄 Domain Sync" added to menu bar
// • All required sheets detected
// • Initial sync completed`);
//   } catch (error) {
//     SpreadsheetApp.getUi().alert('❌ Setup failed: ' + error.toString());
//   }
// }

// // ===== Column Index Helper =====
// function findMainSheetColumns(headerRow) {
//   const indices = {};
//   headerRow.forEach((header, index) => {
//     const headerStr = header.toString().toLowerCase().trim();
//     if (headerStr.includes('stt')) indices.stt = index;
//     else if (headerStr.includes('mã domain') || headerStr.includes('ma domain')) indices.maDomain = index;
//     else if (headerStr.includes('tên domain') || headerStr.includes('ten domain')) indices.tenDomain = index;
//     else if (headerStr.includes('loại domain') || headerStr.includes('loai domain')) indices.loaiDomain = index;
//     else if (headerStr.includes('nhóm sản phẩm') || headerStr.includes('nhom san pham')) indices.nhomSanPham = index;
//   });
//   return indices;
// }

// // ===== Sync to Alias Sheet =====
// function syncToAliasSheet(dataRows, mainColIndices, aliasSheet) {
//   const aliasData = [];
//   let sttCounter = 1;
//   dataRows.forEach(row => {
//     const loaiDomain = row[mainColIndices.loaiDomain] || '';
//     if (loaiDomain.toString().toLowerCase().trim() === 'alias') {
//       const maDomain = row[mainColIndices.maDomain] || '';
//       const tenDomain = row[mainColIndices.tenDomain] || '';
//       const nhomSanPham = row[mainColIndices.nhomSanPham] || '';
//       if (!maDomain && !tenDomain) return;
//       aliasData.push({ stt: sttCounter++, maDomain, tenDomain, nhomSanPham });
//     }
//   });
//   if (aliasData.length > 0) {
//     updateTargetSheet(aliasSheet, aliasData, 'alias');
//   }
//   return aliasData.length;
// }

// // ===== Sync to Landing Sheet =====
// function syncToLandingSheet(dataRows, mainColIndices, landingSheet) {
//   const landingData = [];
//   let sttCounter = 1;
//   dataRows.forEach(row => {
//     const loaiDomain = row[mainColIndices.loaiDomain] || '';
//     if (loaiDomain.toString().toLowerCase().trim() === 'landing page') {
//       const maDomain = row[mainColIndices.maDomain] || '';
//       const tenDomain = row[mainColIndices.tenDomain] || '';
//       const nhomSanPham = row[mainColIndices.nhomSanPham] || '';
//       if (!maDomain && !tenDomain) return;
//       landingData.push({ stt: sttCounter++, maDomain, tenDomain, nhomSanPham });
//     }
//   });
//   if (landingData.length > 0) {
//     updateTargetSheet(landingSheet, landingData, 'landing');
//   }
//   return landingData.length;
// }

// // ===== Update Sheet While Preserving Other Columns =====
// function updateTargetSheet(sheet, newData, sheetType) {
//   const existingData = sheet.getDataRange().getValues();
//   const headerRow = existingData[0];
//   const existingRows = existingData.slice(1);
//   const targetColIndices = findTargetSheetColumns(headerRow, sheetType);

//   const existingMap = new Map();
//   existingRows.forEach((row, idx) => {
//     const maDomain = row[targetColIndices.maDomain];
//     if (maDomain) {
//       existingMap.set(maDomain.toString(), { index: idx + 2, row }); // +2 because data starts from row 2
//     }
//   });

//   const updatedMaDomainSet = new Set();

//   newData.forEach(item => {
//     const maDomain = item.maDomain.toString();
//     updatedMaDomainSet.add(maDomain);

//     if (existingMap.has(maDomain)) {
//       const { index, row } = existingMap.get(maDomain);
//       if (targetColIndices.stt !== undefined) sheet.getRange(index, targetColIndices.stt + 1).setValue(item.stt);
//       if (targetColIndices.tenDomain !== undefined) sheet.getRange(index, targetColIndices.tenDomain + 1).setValue(item.tenDomain);
//       if (targetColIndices.nhomSanPham !== undefined) sheet.getRange(index, targetColIndices.nhomSanPham + 1).setValue(item.nhomSanPham);
//     } else {
//       // Add new row
//       const newRow = new Array(headerRow.length).fill('');
//       if (targetColIndices.stt !== undefined) newRow[targetColIndices.stt] = item.stt;
//       if (targetColIndices.maDomain !== undefined) newRow[targetColIndices.maDomain] = item.maDomain;
//       if (targetColIndices.tenDomain !== undefined) newRow[targetColIndices.tenDomain] = item.tenDomain;
//       if (targetColIndices.nhomSanPham !== undefined) newRow[targetColIndices.nhomSanPham] = item.nhomSanPham;
//       sheet.appendRow(newRow);
//     }
//   });

//   // Xóa các dòng không còn tồn tại trong dữ liệu mới
//   for (let i = existingRows.length - 1; i >= 0; i--) {
//     const row = existingRows[i];
//     const maDomain = row[targetColIndices.maDomain];
//     if (maDomain && !updatedMaDomainSet.has(maDomain.toString())) {
//       sheet.deleteRow(i + 2); // +2 to account for header and 0-based index
//     }
//   }
// }


// // ===== Column Mapping in Target Sheet =====
// function findTargetSheetColumns(headerRow, sheetType) {
//   const indices = {};
//   headerRow.forEach((header, index) => {
//     const headerStr = header.toString().toLowerCase().trim();
//     if (headerStr.includes('stt')) indices.stt = index;
//     else if (headerStr.includes('mã domain') || headerStr.includes('ma domain')) indices.maDomain = index;
//     else if (headerStr.includes('tên domain') || headerStr.includes('ten domain')) indices.tenDomain = index;
//     else if (headerStr.includes('nhóm sản phẩm') || headerStr.includes('nhom san pham')) indices.nhomSanPham = index;
//   });
//   return indices;
// }

// // ===== Test Sync Trigger =====
// function testSync() {
//   SpreadsheetApp.getUi().alert('🧪 Running test sync...\n\nCheck the console logs for detailed information.');
//   syncData();
// }

// // ===== Last Sync Time Info =====
// function getLastSyncTime() {
//   const lastSync = PropertiesService.getScriptProperties().getProperty('lastSyncTime');
//   if (lastSync) {
//     SpreadsheetApp.getUi().alert(`⏰ Last Sync Information

// 📅 Date: ${new Date(lastSync).toLocaleDateString()}
// 🕐 Time: ${new Date(lastSync).toLocaleTimeString()}
// 📊 Full Timestamp: ${lastSync}`);
//   } else {
//     SpreadsheetApp.getUi().alert('ℹ️ No sync has been performed yet.\n\nRun your first sync from the "🔄 Domain Sync" menu.');
//   }
// }