db_module.js

const DB = {
  // Private variable to cache the spreadsheet instance
  _cachedSpreadsheet: null,
  _cachedSpreadsheetId: null,

  /// Get spreadsheet instance (with caching)
  // Defaults to MAIN_DASHBOARD_SS_ID for cross-spreadsheet compatibility
  getSpreadsheet: (id = null) => {
    // Determine the target spreadsheet ID
    const targetId = id || CACHED_SETTINGS?.VALUES?.MAIN_DASHBOARD_SS_ID;
    
    if (targetId) {
      // Check if we have this specific spreadsheet cached
      if (DB._cachedSpreadsheet && DB._cachedSpreadsheetId === targetId) {
        Logger.log("Using cached spreadsheet instance.");
        return DB._cachedSpreadsheet;
      }
      
      // Fetch and cache the spreadsheet
      Logger.log(`Fetching spreadsheet by ID and caching it.`);
      DB._cachedSpreadsheet = SpreadsheetApp.openById(targetId);
      DB._cachedSpreadsheetId = targetId;
      return DB._cachedSpreadsheet;
    }

    // Fallback to active spreadsheet (legacy behavior, should rarely be needed)
    if (!DB._cachedSpreadsheet) {
      Logger.log("Fetching active spreadsheet and caching it (fallback).");
      DB._cachedSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      DB._cachedSpreadsheetId = null;
    } else {
      Logger.log("Using cached spreadsheet instance.");
    }

    return DB._cachedSpreadsheet;
  },

  // Get sheet by name
  getSheet: (sheetName, spreadsheetId = null) => 
    DB.getSpreadsheet(spreadsheetId).getSheetByName(sheetName),

  // Get all data from sheet (batch operation)
  getAllData: (sheetName, spreadsheetId = null) =>
    DB.getSheet(sheetName, spreadsheetId).getDataRange().getValues(),

  // Sort sheet using cached settings (API optimized)
  sortByColumn: (sheetName, spreadsheetId = null) => {
    try {
      // Get sheet config from cache - inline the getSheetConfig functionality
      let sheetConfig = null;
      try {
        const sheets = CACHED_SETTINGS?.VALUES?.SHEETS;
        if (sheets) {
          // Find sheet config by name (case-insensitive)
          for (const [, config] of Object.entries(sheets)) {
            if (config?.NAME === sheetName) {
              sheetConfig = config;
              break;
            }
          }
        }
      } catch (error) {
        Logger.log(
          `Error getting sheet config for '${sheetName}': ${error.message}`
        );
      }

      if (!sheetConfig || sheetConfig.SORT_COLUMN === undefined) {
        Logger.log(`Skipping sort for '${sheetName}' - no sort config found`);
        return;
      }

      const sheet = DB.getSheet(sheetName, spreadsheetId);
      if (!sheet) {
        throw new Error(`❌Sheet '${sheetName}' not found`);
      }

      // More efficient: use getLastRow() instead of getDataRange() - saves an API call
      const numRows = sheet.getLastRow();

      if (numRows <= 1) {
        Logger.log(`Skipping sort for '${sheetName}' - only ${numRows} rows`);
        return;
      }

      // Use cached sort settings
      const sortColumn = (sheetConfig.SORT_COLUMN || 0) + 1; // Convert to 1-based
      const ascending = (sheetConfig.SORT_ORDER || "desc") === "asc";

      // Skip header row and sort data - get column count efficiently
      const numCols = sheet.getLastColumn();
      const dataRange = sheet.getRange(2, 1, numRows - 1, numCols);
      dataRange.sort({ column: sortColumn, ascending: ascending });

      Logger.log(
        `Sorted '${sheetName}' by column ${sortColumn} (${
          ascending ? "asc" : "desc"
        }) - ${numRows - 1} rows`
      );
    } catch (error) {
      Logger.log(`Error sorting sheet '${sheetName}': ${error.message}`);
      throw error; // Re-throw to let caller handle
    }
  },

  // Mark entry with color and note
  markEntry: (range, bgColor = null, note = null) => {
    if (!range) {
      Logger.log("DB.markEntry: No range provided");
      return;
    }
    if (!bgColor && !note) {
      Logger.log("DB.markEntry: No color or note provided");
      return;
    }

    try {
      if (bgColor) range.setBackground(bgColor);
      if (note) range.setNote(note);
      Logger.log(
        `DB.markEntry: Successfully marked range with color: ${bgColor}, note: ${note}`
      );
    } catch (error) {
      Logger.log(`DB.markEntry: Error marking range - ${error.message}`);
      throw error;
    }
  },

  batchUpdate: (operations, spreadsheetId = null) => {
    if (!operations || operations.length === 0) return [];

    const results = [];

    // Group operations by sheet for true batching
    const operationsBySheet = {};
    operations.forEach((op) => {
      if (!operationsBySheet[op.sheetName]) {
        operationsBySheet[op.sheetName] = {
          sheet: null,
          updates: [],
          appends: [],
        };
      }
      operationsBySheet[op.sheetName].sheet = DB.getSheet(
        op.sheetName,
        spreadsheetId
      );

      if (op.type === "updateByRowIndex") {
        // Use the row index directly from loaded data - NO API CALL NEEDED!
        operationsBySheet[op.sheetName].updates.push({
          rowIndex: op.rowIndex,
          values: op.values,
          operation: op,
        });
      } else if (op.type === "append") {
        operationsBySheet[op.sheetName].appends.push({
          values: op.values,
          operation: op,
        });
      }
    });

    // Execute batch operations per sheet
    Object.keys(operationsBySheet).forEach((sheetName) => {
      const sheetOps = operationsBySheet[sheetName];
      const sheet = sheetOps.sheet;

      try {
        // Batch all updates for this sheet in one API call using known row indices
        if (sheetOps.updates.length > 0) {
          Logger.log(
            `Batch updating ${sheetOps.updates.length} rows in sheet: ${sheetName}`
          );
          const numCols = sheetOps.updates[0].values.length;
          const columnEnd = getColumnLetter(numCols);
          const ranges = sheetOps.updates.map(
            (update) => `A${update.rowIndex}:${columnEnd}${update.rowIndex}`
          );
          const values = sheetOps.updates.map((update) => update.values);

          Logger.log(`Using ranges: ${ranges.join(", ")}`);

          // TRUE batch operation - single API call for all updates
          const rangeList = sheet.getRangeList(ranges);
          rangeList.getRanges().forEach((range, index) => {
            range.setValues([values[index]]);
          });

          Logger.log(
            `Successfully batch updated ${sheetOps.updates.length} rows`
          );

          sheetOps.updates.forEach((update) => {
            results.push({ success: true, operation: update.operation });
          });

          // Handle notes
          const updatesWithNotes = sheetOps.updates.filter(u => u.operation.notes && u.operation.notes.length > 0);
          if (updatesWithNotes.length > 0) {
            const noteRanges = [];
            const noteTexts = [];
            
            updatesWithNotes.forEach(update => {
              const notes = update.operation.notes;
              const rowIndex = update.rowIndex;
              
              notes.forEach(note => {
                const columnLetter = getColumnLetter(note.columnIndex + 1); // Convert 0-based to 1-based
                noteRanges.push(`${columnLetter}${rowIndex}`);
                noteTexts.push(note.text);
              });
            });
            
            // Batch set all notes in a single API call
            const rangeList = sheet.getRangeList(noteRanges);
            rangeList.getRanges().forEach((range, index) => {
              range.setNote(noteTexts[index]);
            });
            
            Logger.log(`Set ${noteTexts.length} notes across ${updatesWithNotes.length} rows in ${sheetName}`);
          }
        }

        // Batch all appends for this sheet
        if (sheetOps.appends.length > 0) {
          Logger.log(
            `Batch appending ${sheetOps.appends.length} rows to sheet: ${sheetName}`
          );
          // For appends, we can use a single range operation if we know the starting row
          const lastRow = sheet.getLastRow();
          const numCols = sheetOps.appends[0].values.length;
          const startRow = lastRow + 1;

          Logger.log(
            `Appending to rows ${startRow} to ${
              startRow + sheetOps.appends.length - 1
            }`
          );

          // Single API call for all appends in this sheet
          const appendValues = sheetOps.appends.map((append) => append.values);
          const appendRange = sheet.getRange(
            startRow,
            1,
            appendValues.length,
            numCols
          );
          appendRange.setValues(appendValues);
          Logger.log(`Successfully appended ${appendValues.length} rows`);

          sheetOps.appends.forEach((append) => {
            results.push({ success: true, operation: append.operation });
          });
        }
      } catch (error) {
        // If batch fails, fall back to individual operations
        Logger.log(
          `Batch operation failed for ${sheetName}, falling back to individual operations: ${error.message}`
        );

        // Fallback for updates - inline the updateRow functionality
        sheetOps.updates.forEach((update) => {
          try {
            sheet
              .getRange(update.rowIndex, 1, 1, update.values.length)
              .setValues([update.values]);
            results.push({ success: true, operation: update.operation });
          } catch (err) {
            results.push({
              success: false,
              error: err.message,
              operation: update.operation,
            });
          }
        });

        // Fallback for appends - inline the appendRow functionality
        sheetOps.appends.forEach((append) => {
          try {
            sheet.appendRow(append.values);
            results.push({ success: true, operation: append.operation });
          } catch (err) {
            results.push({
              success: false,
              error: err.message,
              operation: append.operation,
            });
          }
        });
      }
    });

    return results;
  },

  // Reset database by clearing specified ranges in all configured sheets(rows are not deleted)
  resetDatabase: (spreadsheetId = null) => {
    Logger.log(
      "🔄 Starting database reset (soft reset - clearing content only)"
    );

    if (
      !CACHED_SETTINGS.VALUES.ENABLE_FORCED_RESET ||
      !CACHED_SETTINGS.VALUES.DEBUG_MODE
    ) {
      const error = "Auto reset is disabled or debug mode is off.";
      Logger.log(`❌ Reset failed: ${error}`);
      throw new Error(error);
    }

    // Log which spreadsheet we're working with
    if (spreadsheetId) {
      Logger.log(
        `📊 Resetting database in specific spreadsheet: ${spreadsheetId}`
      );
    } else {
      Logger.log(`📊 Resetting database in active/cached spreadsheet`);
    }

    Logger.log(
      `📋 Processing ${
        Object.keys(CACHED_SETTINGS.VALUES.SHEETS).length
      } sheets for reset`
    );

    let successCount = 0;
    let errorCount = 0;

    for (const key in CACHED_SETTINGS.VALUES.SHEETS) {
      try {
        Logger.log(`🧹 Resetting sheet: ${key}`);
        const sheetConfig = CACHED_SETTINGS.VALUES.SHEETS[key];
        const sheet = DB.getSheet(sheetConfig.NAME, spreadsheetId); // Pass spreadsheetId

        if (!sheet) {
          Logger.log(
            `⚠️ Sheet '${sheetConfig.NAME}' not found for key '${key}' in ${
              spreadsheetId ? "specified spreadsheet" : "active spreadsheet"
            }`
          );
          errorCount++;
          continue;
        }

        if (!sheetConfig.RESET_RANGE) {
          Logger.log(`⚠️ No reset range configured for sheet '${key}'`);
          errorCount++;
          continue;
        }

        const range = sheet.getRange(sheetConfig.RESET_RANGE);
        range.clearContent();
        range.setBackground(null);
        range.setNote("");

        Logger.log(
          `✅ Successfully reset sheet '${key}' (range: ${sheetConfig.RESET_RANGE})`
        );
        successCount++;
      } catch (error) {
        Logger.log(`❌ Error resetting sheet '${key}': ${error.message}`);
        errorCount++;
      }
    }

    Logger.log(
      `🎯 Reset completed - Success: ${successCount}, Errors: ${errorCount}`
    );

    if (errorCount > 0) {
      Logger.log(
        `⚠️ Reset completed with ${errorCount} errors. Check logs above for details.`
      );
    } else {
      Logger.log("✨ All sheets successfully reset!");
    }
  },

  // Hard reset database by deleting rows in all configured sheets
  hardResetDatabase: (spreadsheetId = null) => {
    Logger.log("🔄 Starting hard database reset (deleting rows)");

    if (
      !CACHED_SETTINGS.VALUES.ENABLE_FORCED_RESET ||
      !CACHED_SETTINGS.VALUES.DEBUG_MODE
    ) {
      const error = "Auto reset is disabled or debug mode is off.";
      Logger.log(`❌ Hard reset failed: ${error}`);
      throw new Error(error);
    }

    // Log which spreadsheet we're working with
    if (spreadsheetId) {
      Logger.log(
        `📊 Hard resetting database in specific spreadsheet: ${spreadsheetId}`
      );
    } else {
      Logger.log(`📊 Hard resetting database in active/cached spreadsheet`);
    }

    let successCount = 0;
    let errorCount = 0;
    let skippedCount = 0;

    for (const key in CACHED_SETTINGS.VALUES.SHEETS) {
      if (key === "BIKES_STATUS") {
        Logger.log(`⏭️ Skipping sheet: ${key} (protected from hard reset)`);
        skippedCount++;
        continue;
      }

      try {
        Logger.log(`🗑️ Hard resetting sheet: ${key}`);
        const sheetConfig = CACHED_SETTINGS.VALUES.SHEETS[key];
        const sheet = DB.getSheet(sheetConfig.NAME, spreadsheetId); // Pass spreadsheetId

        if (!sheet) {
          Logger.log(
            `⚠️ Sheet '${sheetConfig.NAME}' not found for key '${key}' in ${
              spreadsheetId ? "specified spreadsheet" : "active spreadsheet"
            }`
          );
          errorCount++;
          continue;
        }

        const lastRow = sheet.getLastRow();
        const startRow = 2; // Keep header row
        const numRows = lastRow - 1;

        if (numRows < 1) {
          Logger.log(`⚪ Sheet ${key} is already empty, no rows to delete`);
          successCount++; // Count as success since desired state is achieved
          continue;
        }

        try {
          sheet.deleteRows(startRow, numRows);
          Logger.log(
            `✅ Successfully deleted rows ${startRow}-${lastRow} in ${key}`
          );
          successCount++;
        } catch (deleteError) {
          Logger.log(
            `❌ Error deleting rows ${startRow}-${lastRow} in ${key}: ${deleteError.message}`
          );
          errorCount++;
        }
      } catch (error) {
        Logger.log(`❌ Error processing sheet '${key}': ${error.message}`);
        errorCount++;
      }
    }

    Logger.log(
      `🎯 Hard reset completed - Success: ${successCount}, Errors: ${errorCount}, Skipped: ${skippedCount}`
    );

    if (errorCount > 0) {
      Logger.log(
        `⚠️ Hard reset completed with ${errorCount} errors. Check logs above for details.`
      );
    } else {
      Logger.log("✨ All applicable sheets successfully hard reset!");
    }
  },

  // Append a row to a sheet
  appendRow: (sheetName, values, spreadsheetId = null) => {
    try {
      Logger.log(`Appending row to sheet: ${sheetName}`);
      const sheet = DB.getSheet(sheetName, spreadsheetId);
      if (!sheet) {
        throw new Error(`Sheet '${sheetName}' not found`);
      }

      // Use the built-in appendRow method
      sheet.appendRow(values);

      Logger.log(`Successfully appended row to '${sheetName}'`);
    } catch (error) {
      Logger.log(`Error appending row to '${sheetName}': ${error.message}`);
      throw error;
    }
  },

  updateRow: (sheetName, rowIndex, values, spreadsheetId = null) => {
    try {
      Logger.log(`Updating row ${rowIndex} in sheet: ${sheetName}`);
      const sheet = DB.getSheet(sheetName, spreadsheetId);
      if (!sheet) {
        throw new Error(`Sheet '${sheetName}' not found`);
      }

      const numCols = values.length;
      const updateRange = sheet.getRange(rowIndex, 1, 1, numCols);
      updateRange.setValues([values]);

      Logger.log(`Successfully updated row ${rowIndex} in '${sheetName}'`);
    } catch (error) {
      Logger.log(
        `Error updating row ${rowIndex} in '${sheetName}': ${error.message}`
      );
      throw error;
    }
  },
};