const baseURL = "{site_url}",
accessToken = "{token}",
order_statuses = "{order_statuses}",
sheetTab = "{sheet_tab}";


function RunOSGSW() {
  if (!osgsw_current_sheet()) return;
  osgsw_add_menus();
  osgsw_apply_styles();
  let sheet_info = SpreadsheetApp.getActive().getSheetByName(sheetTab);
  osgsw_save_headers_to_cache(sheet_info);
}

function onOpen() {
    RunOSGSW();
}

function osgsw_save_headers_to_cache(sheet) {
    const headerRowIndex = 1;
    const properties = PropertiesService.getScriptProperties();
    const currentHeaders = sheet.getRange(headerRowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];
    properties.setProperty('osgsw_previousHeaders', JSON.stringify(currentHeaders));
    osgsw_get_complete_column_mapping();
    Logger.log("Headers saved to cache.");
}

function osgsw_get_headers_from_cache() {
    const properties = PropertiesService.getScriptProperties();
    const previousHeaders = properties.getProperty('osgsw_previousHeaders');
    Logger.log("Retrieving headers from cache...");
    Logger.log("Raw headers from cache: " + previousHeaders);
    return previousHeaders ? JSON.parse(previousHeaders) : null;

}
function osgsw_compare_header_positions(previousHeaders, currentHeaders) {
    const minLength = Math.min(previousHeaders.length, currentHeaders.length);
    for (let i = 0; i < minLength; i++) {
        if (previousHeaders[i] !== currentHeaders[i]) {
            return false;
        }
    }
    return true;
}

function osgsw_get_column_positions() {
    const headers = osgsw_headers();
    const columns = osgsw_columns();
    let columnPositions = {};
    
    headers.forEach((header, index) => {
        if (Object.keys(columns).includes(header)) {
            columnPositions[columns[header]] = index;
        } else {
            columnPositions[header] = index;
        }
    });

    Logger.log("Final column positions mapping: " + JSON.stringify(columnPositions));
    
    return columnPositions;
}

function osgsw_get_complete_column_mapping() {
    const sheet = SpreadsheetApp.getActive().getSheetByName(sheetTab);
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const columns = osgsw_columns();

    let completeMapping = {};

    headers.forEach((header, index) => {
      if (header) {
        const weight = index;
        const columnLetter = osgsw_column_char(index + 1);
        const headerKey = columns[header] || header;
        
        completeMapping[index + 1] = {
          headerValue: header,
          headerKey: headerKey,
          weight: weight,
          columnLetter: columnLetter
        };
      }
    });

    osgsw_save_column_preferences(completeMapping);

    Logger.log("Complete column mapping: " + JSON.stringify(completeMapping));
    return completeMapping;
}

function onEdit(e) {
  if (e == null || e == 'undefined' || e == '') return;
  if (e.triggerUid == null) return;
  if (!osgsw_current_sheet()) return;
  
  var osgs_ui = SpreadsheetApp.getUi();
  const sheet = e.source.getActiveSheet();
  
  const editedRange = e.range;
  const editedRowStart = editedRange.getRow();
  const editedRowEnd = editedRange.getLastRow();
  const editedColStart = editedRange.getColumn();
  const editedColEnd = editedRange.getLastColumn();
  const numRows = editedRowEnd - editedRowStart + 1;
  const numCols = editedColEnd - editedColStart + 1;
  
  const headers = osgsw_headers();
  let orderStatusColIndex = -1;
  
  for (let i = 0; i < headers.length; i++) {
      if (osgsw_columns(headers[i]) === "order_status") {
          orderStatusColIndex = i + 1;
          break;
      }
  }
  
  const isEditingOrderStatus = (editedColStart <= orderStatusColIndex && editedColEnd >= orderStatusColIndex);
  
  const isLargeEdit = (numRows > 10 || (editedRowStart === 1 && numRows > 1)) && !isEditingOrderStatus;
  
  if (isLargeEdit || (editedRowStart === 1 && !isEditingOrderStatus)) {
      return;
  }
  
  const editedCol = editedRange.getColumn();
  
  if (numRows === 1 && numCols === 1 && editedCol !== orderStatusColIndex) {
      osgs_ui.alert("This column is not editable");
      osgsw_toast("Only Order Status column can be edited directly. Do not close or interrupt the sync... Please wait!..", "Not Editable");
      osgsw_fetch_from_WordPress("Data reset from WooCommerce");
      return;
  }
  
  const currentColumn = osgsw_current_column();
  var check_non_edit = false;
  let data = osgsw_get_edited_data(e);
  let key_value = osgsw_columns(currentColumn, true);

  if (typeof key_value === "undefined") {
      key_value = currentColumn;
  }
  
  if (typeof key_value === 'object') {
      key_value = 'ossgw_sheet_extra_note';
  }
  
  let message = " updated successfully!";
  if (check_non_edit) {
      message = " back successfully!";
  }
  osgsw_remove_non_status_dropdowns();
  osgsw_sync_data(data, message, key_value, check_non_edit);
}

function getSortingPreference() {
  const pref = PropertiesService.getScriptProperties().getProperty('currentSorting');
   Logger.log("Sorting choice: " + pref );
  return pref ? JSON.parse(pref) : null;
}

function setSortingPreference(columnName, ascending) {
  PropertiesService.getScriptProperties().setProperty('currentSorting', JSON.stringify([columnName, ascending]));
}

function osgsw_current_sheet() {
    return SpreadsheetApp.getActiveSheet().getSheetName() === sheetTab;
}

function osgsw_toast2(message = null, title = null) {
    SpreadsheetApp.getActiveSpreadsheet().toast(message, title, -1);
}

function osgsw_toast_for_background_process(message = null, title = null, time_count = 1) {
    SpreadsheetApp.getActiveSpreadsheet().toast(message, title, time_count);
}
function osgsw_add_menus() {
  try {
    SpreadsheetApp.getUi().createMenu("Order Sync").addItem("⟱ Fetch Order from WooCommerce", "osgsw_fetch_from_WordPress").addSeparator().addItem(" Format Styles", "osgsw_apply_styles").addItem(" About Order Sync", "osgsw_about_us").addToUi();

    SpreadsheetApp.getUi().createMenu("Create New Order")
      .addItem("➕ Create Order", "osgsw_create_order_sheets_caller")
      .addSeparator()
      .addItem(" About Order Sync", "osgsw_about_us")
      .addToUi();
    
  }catch (e) {
        Logger.log('Already Setup');
  }

}

function osgsw_apply_styles() {
  try {
    const CurrentSheet = SpreadsheetApp.getActive().getSheetByName(sheetTab);
    osgsw_optimize_column_widths();

    const lastRow = Math.max(CurrentSheet.getLastRow(), 1000);
    const lastColumn = CurrentSheet.getLastColumn();

    if (lastRow > 0 && lastColumn > 0) {
       CurrentSheet.getRange(1, 1, lastRow, lastColumn).setHorizontalAlignment("left");
      Logger.log("Set all cells to left alignment");
    }

    Logger.log("Order Sync Installed!");
  } catch (e) {
    Logger.log('Style Application Error: ' + e.toString());
  }
}
function osgsw_about_us() {
    let htmlOutput = HtmlService.createHtmlOutput(`<h3>Order Sync with Google Sheet for WooCommerce</h3> <p>Sync your WooCommerce Order with Google Sheets.</p> <p><a href="https://wordpress.org/plugins/order-sync-with-google-sheets-for-woocommerce/" target="_blank">Download Free</a> version from WordPress.org</p> <p><a href="https://wcordersync.com/pricing/" target="_blank">Get Ultimate</a> version to enjoy all premium features and official updates.</p> `).setWidth(550).setHeight(200);
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Order Sync with Google Sheet for WooCommerce");
}

function osgsw_headers() {
    let header = SpreadsheetApp.getActive().getSheetByName(sheetTab).getRange("A1:Z1").getValues();
    header = header[0].filter((column) => column.length);
    return header;
}

function osgsw_available_columns() {
    let columns = osgsw_columns();
    let headers = osgsw_headers();
    let keys = {};
    headers.forEach((header) => {
        if (Object.keys(columns).includes(header)) {
            keys[header] = columns[header];
        }
    });
    return keys;
}

function osgsw_max_column() {
    let maxColumn = SpreadsheetApp.getActive().getSheetByName(sheetTab).getLastColumn();
    return maxColumn;
}

function osgsw_column_char(index = 0) {
    if (index <= 0) return null;
    
    let columnName = '';
    while (index > 0) {
        let remainder = (index - 1) % 26;
        columnName = String.fromCharCode(65 + remainder) + columnName;
        index = Math.floor((index - 1) / 26);
    }
    return columnName;
}

function osgsw_current_row() {
    let currentCell = SpreadsheetApp.getActive().getSheetByName(sheetTab).getCurrentCell().getA1Notation();
    let row = currentCell.replace(/[^0-9]/g, "");
    return row;
}

function osgsw_current_column() {
    let currentCell = SpreadsheetApp.getActive().getSheetByName(sheetTab).getCurrentCell().getA1Notation();
    let rowNotation = currentCell.replace(/[0-9]/g, "");
    rowNotation = "abcdefghijklmnopqrstuvwxyz".toUpperCase().split("").indexOf(rowNotation);
    let column = Object.values(osgsw_available_columns())[rowNotation];
    return column;
}

function osgsw_column_index(columns) {
    let indexes = [];
    let available_columns = osgsw_available_columns();
    columns.forEach((column) => {
        let index = Object.values(available_columns).indexOf(column);
        if (index >= 0) index++;
        indexes.push(index);
    });
    return indexes;
}

function osgsw_format(data) {
    const deletables = ["order_url"];
    const keys = osgsw_ordered_keys();
    data = data.map((row) => {
        return Object.assign.apply({}, keys.map((v, i) => ({
            [v]: row[i],
        })));
    }).map((row) => {
        deletables.forEach((key) => {
            if (key in row) delete row[key];
        });
        return row;
    });
    return data;
}

function osgsw_new_getColumnLetter(columnIndex) {
  var columnLetter = '';
  while (columnIndex > 0) {
    var modulo = (columnIndex - 1) % 26;
    columnLetter = String.fromCharCode(modulo + 'A'.charCodeAt(0)) + columnLetter;
    columnIndex = Math.floor((columnIndex - modulo) / 26);
  }
  return columnLetter;
}

function osgsw_getDynamicColumnRange(staticColumnsEndIndex) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const startColumnIndex = staticColumnsEndIndex + 1;
    const endColumn = sheet.getLastColumn();
    const columns = [];
    const column_header = [];

    for (let col = startColumnIndex; col <= endColumn; col++) {
        const columnLetter = osgsw_column_to_a1(col);
        column_header.push(columnLetter + '1');
        columns.push(columnLetter + '2:' + columnLetter);
    }

    return [columns, column_header];
}

function osgsw_column_to_a1(columnNumber) {
    return osgsw_column_char(columnNumber);
}

function getA1NotationForColumn(columnIndex) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const cell = sheet.getRange(1, columnIndex);
    return cell.getA1Notation();
}

function osgsw_getDynamicColumnIndex() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var endColumn = sheet.getLastColumn();
    const cell = sheet.getRange(1, endColumn);
    const enda1Notation = cell.getA1Notation();
    const alphabetPart = enda1Notation.match(/[A-Z]+/i)[0];
    return alphabetPart;
}

function osgsw_get_all_data() {
    var values = SpreadsheetApp.getActive().getSheetByName(sheetTab).getDataRange().getValues();
    values.shift();
    return osgsw_format(values);
}
function osgsw_save_temp_info(key = 'osgsw_time_storage', data = '') {
    const properties = PropertiesService.getScriptProperties();
    properties.setProperty(key, data);
    Logger.log("Saved" + key + " information");
}


function osgsw_on_edit_format(data, rowStart, colStart, colEnd) {
    colStart = colStart- 1;
    colEnd = colEnd - 1;
   const keyss = osgsw_ordered_keys();
    return data.map((row, index) => {
        let formattedRow = {};
        let formattedRow_extra = {};
        formattedRow["index_number"] = rowStart + index;
        formattedRow_extra["index_number"] = rowStart + index;
        keyss.forEach((key, i) => {
          if (i == 0 || (i >= colStart && i <= colEnd) ) {
            formattedRow[key] = row[i];
            formattedRow_extra[key] = row[i];
          } else {
            formattedRow_extra[key] = row[i];
          }
        });
        formattedRow["osgsw_extra_data_info"] = JSON.stringify(formattedRow_extra);
        return formattedRow;
    });
}

function osgsw_ordered_keys() {
    let orderedKeys = [];
    osgsw_headers().forEach((header) => {
        orderedKeys.push(osgsw_available_columns()[header]);
    });
    return orderedKeys;
}

function osgsw_sync_all() {
    let data = osgsw_get_all_data();
    osgsw_sync_data(data);
}

function osgsw_toast(message = null, title = null) {
    SpreadsheetApp.getActiveSpreadsheet().toast(message, title);
}


function osgsw_get_temp_information(key = 'osgsw_time_storage') {
    var new_properties = PropertiesService.getScriptProperties();
    return new_properties.getProperty(key) ? new_properties.getProperty(key) : null;
}
function osgsw_get_dynamic_range_data(startIndex = 0, endIndex = 0, start_colmun = 0) {
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetTab);
    if (!sheet) {
        return;
    }
    if (endIndex < startIndex) {
        return;
    }
    if( start_colmun ) {
        var end_column = parseInt(osgsw_get_temp_information('osgsw_end_colmun'));
        var get_combined_range = sheet.getRange(startIndex, 1, endIndex - startIndex + 1, sheet.getLastColumn()); 
        var data = get_combined_range.getValues();
        return osgsw_on_edit_format(data, startIndex, start_colmun, end_column);
    }
    return;
}

function osgsw_delete_trigger_by_id(triggerId) {
    var triggers = ScriptApp.getProjectTriggers();
    if (triggers.length === 0) {
        return;
    }
    for (var i = 0; i < triggers.length; i++) {
        var trigger = triggers[i];
        if (trigger.getUniqueId() === triggerId) {
            ScriptApp.deleteTrigger(trigger);
            return;
        }
    }
}
function osgsw_create_trigger() {
    var get_trigger_id = osgsw_get_temp_information('osgsw_trigger_id');
    if (get_trigger_id) {
        osgsw_delete_trigger_by_id(get_trigger_id);
    }
    var trigger = ScriptApp.newTrigger('osgsw_sync_data_in_background_process').timeBased().after(60000).create();
    var triggerId = trigger.getUniqueId();
    Logger.log(triggerId);
    osgsw_save_temp_info('osgsw_trigger_id', triggerId);
}
function osgsw_progress_bar(totalUpdated, totalOrders) {
    if (totalUpdated > totalOrders) {
        totalUpdated = totalOrders;
    }
    osgsw_toast2("Successfully updated " + totalUpdated + " out of " + totalOrders + " Orders!", 'Success');
}
function osgsw_remove_first_data_from_array(data, total_update_need) {
    return data.slice(total_update_need);
}
function osgsw_calculate_execution_time(totalOrders, excution_time, total_update_need) {
    var totalBatches_time = Math.ceil(totalOrders / total_update_need);
    var total_trigger = Math.ceil(totalOrders / total_update_need);
    var total_trigger_time = total_trigger * 60;
    var total_batch_time = totalBatches_time * excution_time;
    return total_batch_time + total_trigger_time;
}

function osgsw_sync_data(data, message = "Orders synced successfully", time_triven = false, non_edit = false) {
  Logger.log("Successfully osgsw_sync_data called");
    try {
        const columnPositions = osgsw_get_column_positions();
        
        let orders = data.filter((row) => {
          return row[2] !== "" && row[0] !== "order_Id";
        });
        Logger.log(orders);
        if (!orders.length) return;
        var chunkSize = 5000;
        var total_update_need = 10000;
        let totalUpdated = 0;
        let total_orders = orders.length;
        if (!time_triven) {
            osgsw_save_temp_info('osgsw_total_of_orders', total_orders);
        }

        if (!non_edit) {
            osgsw_toast2("Updating " + total_orders + " orders... Please wait!", 'Loading...');
        }
        for (let i = 0; i < total_orders; i += chunkSize) {
            let orderChunk = orders.slice(i, i + chunkSize);
            const arrayLength = orderChunk.length;
            const firstIndex = orderChunk[0]['index_number'];
            const lastIndex = orderChunk[arrayLength - 1]['index_number'];
            const newapp = true;
            if (!time_triven) {
                var start_time = new Date().getTime();
            }
            let response = UrlFetchApp.fetch(baseURL + "/wp-json/osgsw/v1/update", {
                method: "POST",
                payload: JSON.stringify({
                    orders: orderChunk,
                }),
                contentType: "application/json",
                muteHttpExceptions: true,
                headers: {
                    OSGSWKEY: "Bearer " + accessToken,
                }
            });

            if (response.getResponseCode() == 200) {
                response = JSON.parse(response.getContentText());
                if (response.success) {
                    totalUpdated += arrayLength;

                    osgsw_progress_bar(totalUpdated, total_orders);
                    if (totalUpdated > total_update_need - 1 && total_update_need < total_orders) {
                        var new_data = osgsw_remove_first_data_from_array(orders, total_update_need);
                        var new_data_length = new_data.length;
                        if (new_data_length) {
                           
                            if (!time_triven) {
                                var end_time = new Date().getTime();
                                var execution_mili_second = end_time - start_time;
                                var excution_time = execution_mili_second / 1000;
                                var popup_stay_time = osgsw_calculate_execution_time(total_orders, excution_time, total_update_need);
                                osgsw_toast_for_background_process("We're processing your request. An email will be sent to the admin when complete.", '!Notice', popup_stay_time);
                            }
                        }
                        return;
                    }
                    
                } else {
                    osgsw_toast(response.message, "Ops error!");
                    return;
                }
            } else {
                if (response.getResponseCode() == 401 || response.getResponseCode() == 403) {
                    osgsw_toast('Authentication Failed: REST API is not supported on your system', "Ops!");
                    return;
                } else {
                    osgsw_toast("Something went wrong, please try again", "Oopss!");
                    return;
                }
            }
        }
    } catch (error) {
        Logger.log('something');
        osgsw_toast("Something went wrong, please try again", "Oopss!");
        return;
    }
    const sortingPref = getSortingPreference();
    if (sortingPref) 
      {
          const [columnName, ascending] = sortingPref;
          sortSheet(columnName, ascending);
      }
    osgsw_toast(message, "Success!");
    if (time_triven) {
        osgsw_order_update_email();
    }
     Logger.log("Successfully osgsw_sync_data called end");
    return true;
}


function osgsw_order_update_email() {
    var totalOrders = osgsw_get_temp_information('osgsw_total_of_orders'); 
    var subject = "FlexOrder Notice: Your Order Data Update/Sync is Complete";
    var body = `
        <p>Hi,</p>
        <p>Your order data update and sync process has been successfully completed!</p>
        <p><strong>Summary of the Process:</strong></p>
        <ul>
            <li><strong>Orders Processed:</strong> ${totalOrders}</li>
        </ul>
        <p>You can review the updated data in WooCommerce or Google Sheets.</p>
        <p>We appreciate your patience while the batch processing was in progress.</p>
        <p>Thank you for using FlexOrder!</p>
        <p>Best regards,<br>FlexOrder Team</p>
    `;
    var email = Session.getActiveUser().getEmail();
    
    MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: body
    });

    Logger.log("Email sent to: " + email + " with total orders: " + totalOrders);
}
    