i want to fix this codes (/**
 * Longeva Healthcare Management System
 * Comprehensive Script to Manage Patients, Appointments, Doctors, Invoices, Payments,
 * Prescriptions, Employees, and Salaries.
 * Author: [Your Name]
 * Date: [Date]
 */

// --------------------- Configuration ---------------------

const CONFIG = {
  MAIN_FOLDER: 'Longeva Healthcare Management System',
  SUBFOLDERS: {
    PATIENTS: 'Patients',
    APPOINTMENTS: 'Appointments',
    DOCTORS: 'Doctors',
    INVOICES: 'Invoices',
    PRESCRIPTIONS: 'Prescriptions',
    EMPLOYEES: 'Employees'
  },
  SHEETS: {
    PATIENT_DETAILS: 'Patient_Details',
    PATIENT_HEALTH_METRICS: 'Patient_Health_Metrics',
    PATIENT_MEDICAL_HISTORY: 'Patient_Medical_History',
    APPOINTMENTS: 'Appointments',
    APPOINTMENT_STATUS: 'Appointment_Status',
    DOCTOR_DETAILS: 'Doctor_Details',
    INVOICES: 'Invoices',
    PAYMENTS: 'Payments',
    PRESCRIPTIONS: 'Prescriptions',
    EMPLOYEE_DETAILS: 'Employee_Details',
    SALARIES: 'Salaries'
  },
  ADMIN_EMAIL: 'admin@longeva.com' // Replace with your admin email
};

// --------------------- Utility Functions ---------------------

/**
 * Get or create a folder by name within a parent folder.
 * @param {string} parentFolderId - ID of the parent folder.
 * @param {string} folderName - Name of the folder to get or create.
 * @return {GoogleAppsScript.Drive.Folder} - The retrieved or created folder.
 */
function getOrCreateFolder(parentFolderId, folderName) {
  const parentFolder = DriveApp.getFolderById(parentFolderId);
  const folders = parentFolder.getFoldersByName(folderName);
  return folders.hasNext() ? folders.next() : parentFolder.createFolder(folderName);
}

/**
 * Get or create a Google Sheet within a folder.
 * @param {GoogleAppsScript.Drive.Folder} folder - The folder to contain the sheet.
 * @param {string} sheetName - Name of the sheet to get or create.
 * @return {GoogleAppsScript.Spreadsheet.Spreadsheet} - The retrieved or created sheet.
 */
function getOrCreateSheet(folder, sheetName) {
  const files = folder.getFilesByName(sheetName);
  if (files.hasNext()) {
    return SpreadsheetApp.open(files.next());
  } else {
    const newSheet = SpreadsheetApp.create(sheetName);
    const file = DriveApp.getFileById(newSheet.getId());
    folder.addFile(file);
    DriveApp.getRootFolder().removeFile(file); // Remove from root to keep it in the subfolder
    return newSheet;
  }
}

/**
 * Initialize the folder structure and sheets.
 * Run this function once to set up the system.
 */
function initializeSystem() {
  try {
    // Create Main Folder
    let mainFolder;
    const mainFolders = DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER);
    if (mainFolders.hasNext()) {
      mainFolder = mainFolders.next();
    } else {
      mainFolder = DriveApp.createFolder(CONFIG.MAIN_FOLDER);
    }

    // Create Subfolders and Sheets
    for (const [subfolderKey, subfolderName] of Object.entries(CONFIG.SUBFOLDERS)) {
      const folder = getOrCreateFolder(mainFolder.getId(), subfolderName);
      for (const [sheetKey, sheetName] of Object.entries(CONFIG.SHEETS)) {
        // Define which sheets go into which subfolders
        const sheetsInSubfolder = {
          PATIENT_DETAILS: CONFIG.SUBFOLDERS.PATIENTS,
          PATIENT_HEALTH_METRICS: CONFIG.SUBFOLDERS.PATIENTS,
          PATIENT_MEDICAL_HISTORY: CONFIG.SUBFOLDERS.PATIENTS,
          APPOINTMENTS: CONFIG.SUBFOLDERS.APPOINTMENTS,
          APPOINTMENT_STATUS: CONFIG.SUBFOLDERS.APPOINTMENTS,
          DOCTOR_DETAILS: CONFIG.SUBFOLDERS.DOCTORS,
          INVOICES: CONFIG.SUBFOLDERS.INVOICES,
          PAYMENTS: CONFIG.SUBFOLDERS.INVOICES,
          PRESCRIPTIONS: CONFIG.SUBFOLDERS.PRESCRIPTIONS,
          EMPLOYEE_DETAILS: CONFIG.SUBFOLDERS.EMPLOYEES,
          SALARIES: CONFIG.SUBFOLDERS.EMPLOYEES
        };

        if (sheetsInSubfolder[sheetKey] === subfolderName) {
          const sheet = getOrCreateSheet(folder, sheetName);
          setupSheetHeaders(sheet, sheetKey);
        }
      }
    }

    // Create Audit Log Sheet
    const auditSheet = getOrCreateSheet(mainFolder, 'Audit_Log');
    setupAuditLogSheet(auditSheet);

    Logger.log('System initialized successfully.');
    MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'System Initialization Completed', 'The Longeva Healthcare Management System has been initialized successfully.');
  } catch (error) {
    Logger.log(`Error in initializeSystem: ${error.message}`);
    MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'System Initialization Failed', `Error in initializeSystem: ${error.message}`);
  }
}

/**
 * Set up headers for each sheet based on the sheet key.
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} sheet - The sheet to set headers for.
 * @param {string} sheetKey - The key identifying the sheet.
 */
function setupSheetHeaders(sheet, sheetKey) {
  const headers = {
    PATIENT_DETAILS: [
      'ID', 'Name', 'Age', 'Gender', 'Phone', 'Email', 'Address',
      'Date_of_Birth', 'Registration_Date', 'Status', 'Doctor_ID',
      'Created_At', 'Updated_At'
    ],
    PATIENT_HEALTH_METRICS: [
      'Patient_ID', 'Exercise_Frequency', 'Diet', 'Diet_Specify',
      'Sleep_Average', 'Smoking_Status', 'Created_At', 'Updated_At'
    ],
    PATIENT_MEDICAL_HISTORY: [
      'Patient_ID', 'Chronic_Conditions', 'Chronic_Conditions_Specify',
      'Medications', 'Medications_List', 'Major_Surgeries',
      'Major_Surgeries_Specify', 'Created_At', 'Updated_At'
    ],
    APPOINTMENTS: [
      'ID', 'Package_ID', 'Appointment_Date', 'Proposed_Appointment_Date',
      'Patient_Name', 'Patient_Email', 'Patient_Phone', 'Age', 'Gender',
      'Address', 'Preferred_Contact_Method', 'Additional_Notes',
      'Admin_Notes', 'Status', 'Created_At', 'Patient_ID'
    ],
    APPOINTMENT_STATUS: [
      'Appointment_ID', 'Status', 'Updated_At', 'Notes'
    ],
    DOCTOR_DETAILS: [
      'ID', 'Employee_ID', 'Name', 'Username', 'Password', 'Email',
      'Specialization', 'Phone', 'Registration_Date', 'Profile_Picture',
      'Department', 'Date_of_Joining', 'Status', 'Created_At', 'Updated_At'
    ],
    INVOICES: [
      'ID', 'Patient_ID', 'Service_Name', 'Service_Description', 'Cost',
      'Tax_Rate', 'Discount', 'Total_Amount', 'Invoice_Date',
      'Due_Date', 'Notes', 'Tax', 'Created_At', 'Updated_At'
    ],
    PAYMENTS: [
      'ID', 'Patient_ID', 'Invoice_ID', 'Amount_Paid', 'Payment_Date',
      'Payment_Method', 'Notes', 'Created_At', 'Updated_At'
    ],
    PRESCRIPTIONS: [
      'ID', 'Patient_ID', 'Doctor_ID', 'Medications', 'Instructions',
      'Date_Prescribed', 'Status', 'Created_At', 'Updated_At'
    ],
    EMPLOYEE_DETAILS: [
      'ID', 'Name', 'Role', 'Email', 'Phone', 'Department',
      'Date_of_Joining', 'Status', 'Username', 'Password',
      'Created_At', 'Updated_At', 'Profile_Picture',
      'Specialization', 'Is_Online'
    ],
    SALARIES: [
      'ID', 'Employee_ID', 'Basic_Salary', 'Bonuses', 'Deductions',
      'Overtime_Hours', 'Overtime_Rate', 'Tax', 'Payment_Date',
      'Net_Salary', 'Status', 'Notes', 'Payslip_Path',
      'Created_At', 'Updated_At'
    ]
  };

  if (!headers[sheetKey]) return;

  const sheetObject = sheet.getSheets()[0];
  const existingHeaders = sheetObject.getDataRange().getValues();
  if (existingHeaders.length === 0 || existingHeaders[0].join() !== headers[sheetKey].join()) {
    sheetObject.clear(); // Clear existing content
    sheetObject.appendRow(headers[sheetKey]);
  }
}

/**
 * Set up the Audit Log sheet headers.
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} sheet - The Audit Log sheet.
 */
function setupAuditLogSheet(sheet) {
  const headers = [
    'Timestamp', 'User', 'Action', 'Entity', 'Entity_ID', 'Details'
  ];
  const sheetObject = sheet.getSheets()[0];
  const existingHeaders = sheetObject.getDataRange().getValues();
  if (existingHeaders.length === 0 || existingHeaders[0].join() !== headers.join()) {
    sheetObject.clear(); // Clear existing content
    sheetObject.appendRow(headers);
  }
}

/**
 * Generate a unique ID for records.
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} sheet - The sheet to generate ID for.
 * @return {number} - Unique ID.
 */
function generateUniqueId(sheetName) {
  const folder = DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next();
  for (const [key, value] of Object.entries(CONFIG.SUBFOLDERS)) {
    if (value === sheetName) {
      const sheet = getOrCreateSheet(folder, key);
      const lastRow = sheet.getLastRow();
      if (lastRow < 2) return 1; // If only headers exist
      const lastId = sheet.getRange(lastRow, 1).getValue();
      return lastId + 1;
    }
  }
  // Default fallback
  const sheet = getOrCreateSheet(folder, sheetName);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return 1;
  const lastId = sheet.getRange(lastRow, 1).getValue();
  return lastId + 1;
}

/**
 * Log actions to the Audit Log sheet.
 * @param {string} user - User performing the action.
 * @param {string} action - Action performed (Add, Update, Delete).
 * @param {string} entity - Entity affected (Patient, Appointment, etc.).
 * @param {number} entityId - ID of the entity.
 * @param {string} details - Additional details about the action.
 */
function logAudit(user, action, entity, entityId, details = '') {
  try {
    const mainFolder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), '');
    const auditSheet = getOrCreateSheet(mainFolder, 'Audit_Log');
    const timestamp = new Date();
    auditSheet.getSheets()[0].appendRow([timestamp, user, action, entity, entityId, details]);
  } catch (error) {
    Logger.log(`Error in logAudit: ${error.message}`);
  }
}

// --------------------- Patient Management ---------------------

/**
 * Add a new patient.
 * @param {Object} patient - Patient details.
 * Example:
 * {
 *   name: 'John Doe',
 *   age: 30,
 *   gender: 'Male',
 *   phone: '1234567890',
 *   email: 'john.doe@example.com',
 *   address: '123 Main St',
 *   dateOfBirth: '1994-01-01',
 *   registrationDate: '2024-10-17',
 *   status: 'Initial Consultation',
 *   doctorId: 1
 * }
 */
function addNewPatient(patient) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PATIENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PATIENT_DETAILS);
    const newId = generateUniqueId(CONFIG.SHEETS.PATIENT_DETAILS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      patient.name || '',
      patient.age || '',
      patient.gender || '',
      patient.phone || '',
      patient.email || '',
      patient.address || '',
      patient.dateOfBirth ? new Date(patient.dateOfBirth) : '',
      patient.registrationDate ? new Date(patient.registrationDate) : timestamp,
      patient.status || 'Initial Consultation',
      patient.doctorId || '',
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Patient', newId, `Added patient ${patient.name}`);
    Logger.log(`Added new patient with ID: ${newId}`);
  } catch (error) {
    handleError('addNewPatient', error);
  }
}

/**
 * Retrieve patient details by ID.
 * @param {number} patientId - ID of the patient.
 * @return {Object|null} - Patient details or null if not found.
 */
function getPatientById(patientId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PATIENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PATIENT_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === patientId) {
        return {
          ID: data[i][0],
          Name: data[i][1],
          Age: data[i][2],
          Gender: data[i][3],
          Phone: data[i][4],
          Email: data[i][5],
          Address: data[i][6],
          Date_of_Birth: data[i][7],
          Registration_Date: data[i][8],
          Status: data[i][9],
          Doctor_ID: data[i][10],
          Created_At: data[i][11],
          Updated_At: data[i][12]
        };
      }
    }
    Logger.log(`Patient with ID ${patientId} not found.`);
    return null;
  } catch (error) {
    handleError('getPatientById', error);
    return null;
  }
}

/**
 * Update patient details.
 * @param {number} patientId - ID of the patient.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   name: 'Jane Doe',
 *   status: 'Completed'
 * }
 */
function updatePatient(patientId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PATIENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PATIENT_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === patientId) {
        // Update fields if provided
        if (updatedData.name) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.name);
        if (updatedData.age) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.age);
        if (updatedData.gender) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.gender);
        if (updatedData.phone) sheet.getSheets()[0].getRange(i + 1, 5).setValue(updatedData.phone);
        if (updatedData.email) sheet.getSheets()[0].getRange(i + 1, 6).setValue(updatedData.email);
        if (updatedData.address) sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.address);
        if (updatedData.dateOfBirth) sheet.getSheets()[0].getRange(i + 1, 8).setValue(new Date(updatedData.dateOfBirth));
        if (updatedData.registrationDate) sheet.getSheets()[0].getRange(i + 1, 9).setValue(new Date(updatedData.registrationDate));
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 10).setValue(updatedData.status);
        if (updatedData.doctorId) sheet.getSheets()[0].getRange(i + 1, 11).setValue(updatedData.doctorId);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 13).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Patient', patientId, `Updated patient ID ${patientId}`);
        Logger.log(`Updated patient with ID: ${patientId}`);
        return;
      }
    }
    Logger.log(`Patient with ID ${patientId} not found.`);
  } catch (error) {
    handleError('updatePatient', error);
  }
}

/**
 * Delete a patient record.
 * @param {number} patientId - ID of the patient.
 */
function deletePatient(patientId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PATIENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PATIENT_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === patientId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Patient', patientId, `Deleted patient ID ${patientId}`);
        Logger.log(`Deleted patient with ID: ${patientId}`);
        return;
      }
    }
    Logger.log(`Patient with ID ${patientId} not found.`);
  } catch (error) {
    handleError('deletePatient', error);
  }
}

// --------------------- Appointment Management ---------------------

/**
 * Add a new appointment.
 * @param {Object} appointment - Appointment details.
 * Example:
 * {
 *   packageId: 101,
 *   appointmentDate: '2024-10-25T10:00:00',
 *   proposedAppointmentDate: '2024-10-26T10:00:00',
 *   patientName: 'John Doe',
 *   patientEmail: 'john.doe@example.com',
 *   patientPhone: '1234567890',
 *   age: 30,
 *   gender: 'Male',
 *   address: '123 Main St',
 *   preferredContactMethod: 'Email',
 *   additionalNotes: 'First-time consultation',
 *   adminNotes: 'N/A',
 *   status: 'Confirmed',
 *   patientId: 1
 * }
 */
function addNewAppointment(appointment) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.APPOINTMENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.APPOINTMENTS);
    const newId = generateUniqueId(CONFIG.SHEETS.APPOINTMENTS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      appointment.packageId || '',
      appointment.appointmentDate ? new Date(appointment.appointmentDate) : '',
      appointment.proposedAppointmentDate ? new Date(appointment.proposedAppointmentDate) : '',
      appointment.patientName || '',
      appointment.patientEmail || '',
      appointment.patientPhone || '',
      appointment.age || '',
      appointment.gender || '',
      appointment.address || '',
      appointment.preferredContactMethod || '',
      appointment.additionalNotes || '',
      appointment.adminNotes || '',
      appointment.status || 'Pending',
      timestamp,
      appointment.patientId || ''
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Appointment', newId, `Added appointment for patient ID ${appointment.patientId}`);
    Logger.log(`Added new appointment with ID: ${newId}`);
  } catch (error) {
    handleError('addNewAppointment', error);
  }
}

/**
 * Retrieve appointment details by ID.
 * @param {number} appointmentId - ID of the appointment.
 * @return {Object|null} - Appointment details or null if not found.
 */
function getAppointmentById(appointmentId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.APPOINTMENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.APPOINTMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === appointmentId) {
        return {
          ID: data[i][0],
          Package_ID: data[i][1],
          Appointment_Date: data[i][2],
          Proposed_Appointment_Date: data[i][3],
          Patient_Name: data[i][4],
          Patient_Email: data[i][5],
          Patient_Phone: data[i][6],
          Age: data[i][7],
          Gender: data[i][8],
          Address: data[i][9],
          Preferred_Contact_Method: data[i][10],
          Additional_Notes: data[i][11],
          Admin_Notes: data[i][12],
          Status: data[i][13],
          Created_At: data[i][14],
          Patient_ID: data[i][15]
        };
      }
    }
    Logger.log(`Appointment with ID ${appointmentId} not found.`);
    return null;
  } catch (error) {
    handleError('getAppointmentById', error);
    return null;
  }
}

/**
 * Update appointment status.
 * @param {number} appointmentId - ID of the appointment.
 * @param {string} newStatus - New status value.
 * @param {string} [adminNotes] - Optional admin notes.
 */
function updateAppointmentStatus(appointmentId, newStatus, adminNotes = '') {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.APPOINTMENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.APPOINTMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === appointmentId) {
        // Update status
        sheet.getSheets()[0].getRange(i + 1, 14).setValue(newStatus); // Status column (N)
        // Update Admin Notes if provided
        if (adminNotes) {
          sheet.getSheets()[0].getRange(i + 1, 13).setValue(adminNotes); // Admin_Notes column (M)
        }
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 15).setValue(timestamp); // Updated_At (Created_At column)
        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Appointment', appointmentId, `Updated status to "${newStatus}"`);
        Logger.log(`Updated appointment ID ${appointmentId} to status "${newStatus}"`);
        return;
      }
    }
    Logger.log(`Appointment with ID ${appointmentId} not found.`);
  } catch (error) {
    handleError('updateAppointmentStatus', error);
  }
}

/**
 * Delete an appointment record.
 * @param {number} appointmentId - ID of the appointment.
 */
function deleteAppointment(appointmentId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.APPOINTMENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.APPOINTMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === appointmentId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Appointment', appointmentId, `Deleted appointment ID ${appointmentId}`);
        Logger.log(`Deleted appointment with ID: ${appointmentId}`);
        return;
      }
    }
    Logger.log(`Appointment with ID ${appointmentId} not found.`);
  } catch (error) {
    handleError('deleteAppointment', error);
  }
}

// --------------------- Doctor Management ---------------------

/**
 * Add a new doctor.
 * @param {Object} doctor - Doctor details.
 * Example:
 * {
 *   employeeId: 201,
 *   name: 'Dr. Emily Clark',
 *   username: 'emily.clark',
 *   password: 'securePassword123', // Consider hashing in production
 *   email: 'emily.clark@example.com',
 *   specialization: 'Cardiology',
 *   phone: '0987654321',
 *   registrationDate: '2024-10-17',
 *   profilePicture: 'URL_or_Path',
 *   department: 'Cardiology',
 *   dateOfJoining: '2024-01-15',
 *   status: 'active'
 * }
 */
function addNewDoctor(doctor) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.DOCTORS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.DOCTOR_DETAILS);
    const newId = generateUniqueId(CONFIG.SHEETS.DOCTOR_DETAILS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      doctor.employeeId || '',
      doctor.name || '',
      doctor.username || '',
      doctor.password || '',
      doctor.email || '',
      doctor.specialization || '',
      doctor.phone || '',
      doctor.registrationDate ? new Date(doctor.registrationDate) : timestamp,
      doctor.profilePicture || '',
      doctor.department || '',
      doctor.dateOfJoining ? new Date(doctor.dateOfJoining) : '',
      doctor.status || 'active',
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Doctor', newId, `Added doctor ${doctor.name}`);
    Logger.log(`Added new doctor with ID: ${newId}`);
  } catch (error) {
    handleError('addNewDoctor', error);
  }
}

/**
 * Retrieve doctor details by ID.
 * @param {number} doctorId - ID of the doctor.
 * @return {Object|null} - Doctor details or null if not found.
 */
function getDoctorById(doctorId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.DOCTORS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.DOCTOR_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === doctorId) {
        return {
          ID: data[i][0],
          Employee_ID: data[i][1],
          Name: data[i][2],
          Username: data[i][3],
          Password: data[i][4],
          Email: data[i][5],
          Specialization: data[i][6],
          Phone: data[i][7],
          Registration_Date: data[i][8],
          Profile_Picture: data[i][9],
          Department: data[i][10],
          Date_of_Joining: data[i][11],
          Status: data[i][12],
          Created_At: data[i][13],
          Updated_At: data[i][14]
        };
      }
    }
    Logger.log(`Doctor with ID ${doctorId} not found.`);
    return null;
  } catch (error) {
    handleError('getDoctorById', error);
    return null;
  }
}

/**
 * Update doctor details.
 * @param {number} doctorId - ID of the doctor.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   name: 'Dr. Emily Thompson',
 *   status: 'inactive'
 * }
 */
function updateDoctor(doctorId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.DOCTORS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.DOCTOR_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === doctorId) {
        // Update fields if provided
        if (updatedData.employeeId) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.employeeId);
        if (updatedData.name) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.name);
        if (updatedData.username) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.username);
        if (updatedData.password) sheet.getSheets()[0].getRange(i + 1, 5).setValue(updatedData.password);
        if (updatedData.email) sheet.getSheets()[0].getRange(i + 1, 6).setValue(updatedData.email);
        if (updatedData.specialization) sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.specialization);
        if (updatedData.phone) sheet.getSheets()[0].getRange(i + 1, 8).setValue(updatedData.phone);
        if (updatedData.registrationDate) sheet.getSheets()[0].getRange(i + 1, 9).setValue(new Date(updatedData.registrationDate));
        if (updatedData.profilePicture) sheet.getSheets()[0].getRange(i + 1, 10).setValue(updatedData.profilePicture);
        if (updatedData.department) sheet.getSheets()[0].getRange(i + 1, 11).setValue(updatedData.department);
        if (updatedData.dateOfJoining) sheet.getSheets()[0].getRange(i + 1, 12).setValue(new Date(updatedData.dateOfJoining));
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 13).setValue(updatedData.status);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 15).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Doctor', doctorId, `Updated doctor ID ${doctorId}`);
        Logger.log(`Updated doctor with ID: ${doctorId}`);
        return;
      }
    }
    Logger.log(`Doctor with ID ${doctorId} not found.`);
  } catch (error) {
    handleError('updateDoctor', error);
  }
}

/**
 * Delete a doctor record.
 * @param {number} doctorId - ID of the doctor.
 */
function deleteDoctor(doctorId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.DOCTORS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.DOCTOR_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === doctorId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Doctor', doctorId, `Deleted doctor ID ${doctorId}`);
        Logger.log(`Deleted doctor with ID: ${doctorId}`);
        return;
      }
    }
    Logger.log(`Doctor with ID ${doctorId} not found.`);
  } catch (error) {
    handleError('deleteDoctor', error);
  }
}

// --------------------- Invoice Management ---------------------

/**
 * Add a new invoice.
 * @param {Object} invoice - Invoice details.
 * Example:
 * {
 *   patientId: 1,
 *   serviceName: 'General Checkup',
 *   serviceDescription: 'Comprehensive health examination',
 *   cost: 100.00,
 *   taxRate: 10, // Percentage
 *   discount: 5.00,
 *   invoiceDate: '2024-10-17',
 *   dueDate: '2024-11-17',
 *   notes: 'Paid partially'
 * }
 */
function addNewInvoice(invoice) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.INVOICES);
    const newId = generateUniqueId(CONFIG.SHEETS.INVOICES);
    const taxAmount = (invoice.cost * (invoice.taxRate || 0)) / 100;
    const totalAmount = invoice.cost + taxAmount - (invoice.discount || 0);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      invoice.patientId || '',
      invoice.serviceName || '',
      invoice.serviceDescription || '',
      invoice.cost || 0.00,
      invoice.taxRate || 0.00,
      invoice.discount || 0.00,
      totalAmount || 0.00,
      invoice.invoiceDate ? new Date(invoice.invoiceDate) : timestamp,
      invoice.dueDate ? new Date(invoice.dueDate) : '',
      invoice.notes || '',
      taxAmount || 0.00,
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Invoice', newId, `Added invoice for patient ID ${invoice.patientId}`);
    Logger.log(`Added new invoice with ID: ${newId}`);
  } catch (error) {
    handleError('addNewInvoice', error);
  }
}

/**
 * Retrieve invoice details by ID.
 * @param {number} invoiceId - ID of the invoice.
 * @return {Object|null} - Invoice details or null if not found.
 */
function getInvoiceById(invoiceId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.INVOICES);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === invoiceId) {
        return {
          ID: data[i][0],
          Patient_ID: data[i][1],
          Service_Name: data[i][2],
          Service_Description: data[i][3],
          Cost: data[i][4],
          Tax_Rate: data[i][5],
          Discount: data[i][6],
          Total_Amount: data[i][7],
          Invoice_Date: data[i][8],
          Due_Date: data[i][9],
          Notes: data[i][10],
          Tax: data[i][11],
          Created_At: data[i][12],
          Updated_At: data[i][13]
        };
      }
    }
    Logger.log(`Invoice with ID ${invoiceId} not found.`);
    return null;
  } catch (error) {
    handleError('getInvoiceById', error);
    return null;
  }
}

/**
 * Update invoice details.
 * @param {number} invoiceId - ID of the invoice.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   serviceName: 'Advanced Checkup',
 *   status: 'Paid'
 * }
 */
function updateInvoice(invoiceId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.INVOICES);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === invoiceId) {
        if (updatedData.patientId) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.patientId);
        if (updatedData.serviceName) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.serviceName);
        if (updatedData.serviceDescription) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.serviceDescription);
        if (updatedData.cost !== undefined) {
          const newCost = parseFloat(updatedData.cost);
          const taxRate = parseFloat(sheet.getSheets()[0].getRange(i + 1, 6).getValue());
          const newTaxAmount = (newCost * taxRate) / 100;
          const newTotalAmount = newCost + newTaxAmount - (parseFloat(updatedData.discount) || 0);
          sheet.getSheets()[0].getRange(i + 1, 5).setValue(newCost);
          sheet.getSheets()[0].getRange(i + 1, 11).setValue(newTaxAmount);
          sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.discount || 0);
          sheet.getSheets()[0].getRange(i + 1, 8).setValue(newTotalAmount);
        }
        if (updatedData.taxRate !== undefined) {
          const newTaxRate = parseFloat(updatedData.taxRate);
          const cost = parseFloat(sheet.getSheets()[0].getRange(i + 1, 5).getValue());
          const newTaxAmount = (cost * newTaxRate) / 100;
          const discount = parseFloat(sheet.getSheets()[0].getRange(i + 1, 7).getValue());
          const newTotalAmount = cost + newTaxAmount - discount;
          sheet.getSheets()[0].getRange(i + 1, 6).setValue(newTaxRate);
          sheet.getSheets()[0].getRange(i + 1, 11).setValue(newTaxAmount);
          sheet.getSheets()[0].getRange(i + 1, 8).setValue(newTotalAmount);
        }
        if (updatedData.discount !== undefined) {
          const discount = parseFloat(updatedData.discount);
          const cost = parseFloat(sheet.getSheets()[0].getRange(i + 1, 5).getValue());
          const taxRate = parseFloat(sheet.getSheets()[0].getRange(i + 1, 6).getValue());
          const taxAmount = (cost * taxRate) / 100;
          const newTotalAmount = cost + taxAmount - discount;
          sheet.getSheets()[0].getRange(i + 1, 7).setValue(discount);
          sheet.getSheets()[0].getRange(i + 1, 8).setValue(newTotalAmount);
        }
        if (updatedData.invoiceDate) sheet.getSheets()[0].getRange(i + 1, 9).setValue(new Date(updatedData.invoiceDate));
        if (updatedData.dueDate) sheet.getSheets()[0].getRange(i + 1, 10).setValue(new Date(updatedData.dueDate));
        if (updatedData.notes) sheet.getSheets()[0].getRange(i + 1, 11).setValue(updatedData.notes);
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 10).setValue(updatedData.status);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 14).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Invoice', invoiceId, `Updated invoice ID ${invoiceId}`);
        Logger.log(`Updated invoice with ID: ${invoiceId}`);
        return;
      }
    }
    Logger.log(`Invoice with ID ${invoiceId} not found.`);
  } catch (error) {
    handleError('updateInvoice', error);
  }
}

/**
 * Delete an invoice record.
 * @param {number} invoiceId - ID of the invoice.
 */
function deleteInvoice(invoiceId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.INVOICES);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === invoiceId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Invoice', invoiceId, `Deleted invoice ID ${invoiceId}`);
        Logger.log(`Deleted invoice with ID: ${invoiceId}`);
        return;
      }
    }
    Logger.log(`Invoice with ID ${invoiceId} not found.`);
  } catch (error) {
    handleError('deleteInvoice', error);
  }
}

// --------------------- Payment Management ---------------------

/**
 * Add a new payment.
 * @param {Object} payment - Payment details.
 * Example:
 * {
 *   patientId: 1,
 *   invoiceId: 101,
 *   amountPaid: 50.00,
 *   paymentDate: '2024-10-20',
 *   paymentMethod: 'Card',
 *   notes: 'Partial payment'
 * }
 */
function addNewPayment(payment) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PAYMENTS);
    const newId = generateUniqueId(CONFIG.SHEETS.PAYMENTS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      payment.patientId || '',
      payment.invoiceId || '',
      payment.amountPaid || 0.00,
      payment.paymentDate ? new Date(payment.paymentDate) : timestamp,
      payment.paymentMethod || '',
      payment.notes || '',
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Payment', newId, `Added payment for invoice ID ${payment.invoiceId}`);
    Logger.log(`Added new payment with ID: ${newId}`);
  } catch (error) {
    handleError('addNewPayment', error);
  }
}

/**
 * Retrieve payment details by ID.
 * @param {number} paymentId - ID of the payment.
 * @return {Object|null} - Payment details or null if not found.
 */
function getPaymentById(paymentId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PAYMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === paymentId) {
        return {
          ID: data[i][0],
          Patient_ID: data[i][1],
          Invoice_ID: data[i][2],
          Amount_Paid: data[i][3],
          Payment_Date: data[i][4],
          Payment_Method: data[i][5],
          Notes: data[i][6],
          Created_At: data[i][7],
          Updated_At: data[i][8]
        };
      }
    }
    Logger.log(`Payment with ID ${paymentId} not found.`);
    return null;
  } catch (error) {
    handleError('getPaymentById', error);
    return null;
  }
}

/**
 * Update payment details.
 * @param {number} paymentId - ID of the payment.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   amountPaid: 100.00,
 *   status: 'Paid'
 * }
 */
function updatePayment(paymentId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PAYMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === paymentId) {
        if (updatedData.patientId) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.patientId);
        if (updatedData.invoiceId) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.invoiceId);
        if (updatedData.amountPaid !== undefined) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.amountPaid);
        if (updatedData.paymentDate) sheet.getSheets()[0].getRange(i + 1, 5).setValue(new Date(updatedData.paymentDate));
        if (updatedData.paymentMethod) sheet.getSheets()[0].getRange(i + 1, 6).setValue(updatedData.paymentMethod);
        if (updatedData.notes) sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.notes);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 9).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Payment', paymentId, `Updated payment ID ${paymentId}`);
        Logger.log(`Updated payment with ID: ${paymentId}`);
        return;
      }
    }
    Logger.log(`Payment with ID ${paymentId} not found.`);
  } catch (error) {
    handleError('updatePayment', error);
  }
}

/**
 * Delete a payment record.
 * @param {number} paymentId - ID of the payment.
 */
function deletePayment(paymentId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.INVOICES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PAYMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === paymentId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Payment', paymentId, `Deleted payment ID ${paymentId}`);
        Logger.log(`Deleted payment with ID: ${paymentId}`);
        return;
      }
    }
    Logger.log(`Payment with ID ${paymentId} not found.`);
  } catch (error) {
    handleError('deletePayment', error);
  }
}

// --------------------- Prescription Management ---------------------

/**
 * Add a new prescription.
 * @param {Object} prescription - Prescription details.
 * Example:
 * {
 *   patientId: 1,
 *   doctorId: 2,
 *   medications: 'Amoxicillin 500mg',
 *   instructions: 'Take one tablet three times a day',
 *   datePrescribed: '2024-10-17',
 *   status: 'active'
 * }
 */
function addNewPrescription(prescription) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PRESCRIPTIONS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PRESCRIPTIONS);
    const newId = generateUniqueId(CONFIG.SHEETS.PRESCRIPTIONS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      prescription.patientId || '',
      prescription.doctorId || '',
      prescription.medications || '',
      prescription.instructions || '',
      prescription.datePrescribed ? new Date(prescription.datePrescribed) : timestamp,
      prescription.status || 'active',
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Prescription', newId, `Added prescription for patient ID ${prescription.patientId}`);
    Logger.log(`Added new prescription with ID: ${newId}`);
  } catch (error) {
    handleError('addNewPrescription', error);
  }
}

/**
 * Retrieve prescription details by ID.
 * @param {number} prescriptionId - ID of the prescription.
 * @return {Object|null} - Prescription details or null if not found.
 */
function getPrescriptionById(prescriptionId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PRESCRIPTIONS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PRESCRIPTIONS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === prescriptionId) {
        return {
          ID: data[i][0],
          Patient_ID: data[i][1],
          Doctor_ID: data[i][2],
          Medications: data[i][3],
          Instructions: data[i][4],
          Date_Prescribed: data[i][5],
          Status: data[i][6],
          Created_At: data[i][7],
          Updated_At: data[i][8]
        };
      }
    }
    Logger.log(`Prescription with ID ${prescriptionId} not found.`);
    return null;
  } catch (error) {
    handleError('getPrescriptionById', error);
    return null;
  }
}

/**
 * Update prescription details.
 * @param {number} prescriptionId - ID of the prescription.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   medications: 'Ibuprofen 200mg',
 *   status: 'inactive'
 * }
 */
function updatePrescription(prescriptionId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PRESCRIPTIONS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PRESCRIPTIONS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === prescriptionId) {
        if (updatedData.patientId) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.patientId);
        if (updatedData.doctorId) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.doctorId);
        if (updatedData.medications) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.medications);
        if (updatedData.instructions) sheet.getSheets()[0].getRange(i + 1, 5).setValue(updatedData.instructions);
        if (updatedData.datePrescribed) sheet.getSheets()[0].getRange(i + 1, 6).setValue(new Date(updatedData.datePrescribed));
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.status);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 9).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Prescription', prescriptionId, `Updated prescription ID ${prescriptionId}`);
        Logger.log(`Updated prescription with ID: ${prescriptionId}`);
        return;
      }
    }
    Logger.log(`Prescription with ID ${prescriptionId} not found.`);
  } catch (error) {
    handleError('updatePrescription', error);
  }
}

/**
 * Delete a prescription record.
 * @param {number} prescriptionId - ID of the prescription.
 */
function deletePrescription(prescriptionId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.PRESCRIPTIONS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.PRESCRIPTIONS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === prescriptionId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Prescription', prescriptionId, `Deleted prescription ID ${prescriptionId}`);
        Logger.log(`Deleted prescription with ID: ${prescriptionId}`);
        return;
      }
    }
    Logger.log(`Prescription with ID ${prescriptionId} not found.`);
  } catch (error) {
    handleError('deletePrescription', error);
  }
}

// --------------------- Employee Management ---------------------

/**
 * Add a new employee.
 * @param {Object} employee - Employee details.
 * Example:
 * {
 *   name: 'Alice Johnson',
 *   role: 'admin',
 *   email: 'alice.johnson@example.com',
 *   phone: '555-1234',
 *   department: 'Administration',
 *   dateOfJoining: '2024-01-10',
 *   status: 'active',
 *   username: 'alice.johnson',
 *   password: 'securePass!', // Consider hashing in production
 *   profilePicture: 'URL_or_Path',
 *   specialization: '', // If applicable
 *   isOnline: 1
 * }
 */
function addNewEmployee(employee) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.EMPLOYEE_DETAILS);
    const newId = generateUniqueId(CONFIG.SHEETS.EMPLOYEE_DETAILS);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      employee.name || '',
      employee.role || '',
      employee.email || '',
      employee.phone || '',
      employee.department || '',
      employee.dateOfJoining ? new Date(employee.dateOfJoining) : timestamp,
      employee.status || 'active',
      employee.username || '',
      employee.password || '',
      timestamp,
      timestamp,
      employee.profilePicture || '',
      employee.specialization || '',
      employee.isOnline !== undefined ? employee.isOnline : 0
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Employee', newId, `Added employee ${employee.name}`);
    Logger.log(`Added new employee with ID: ${newId}`);
  } catch (error) {
    handleError('addNewEmployee', error);
  }
}

/**
 * Retrieve employee details by ID.
 * @param {number} employeeId - ID of the employee.
 * @return {Object|null} - Employee details or null if not found.
 */
function getEmployeeById(employeeId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.EMPLOYEE_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === employeeId) {
        return {
          ID: data[i][0],
          Name: data[i][1],
          Role: data[i][2],
          Email: data[i][3],
          Phone: data[i][4],
          Department: data[i][5],
          Date_of_Joining: data[i][6],
          Status: data[i][7],
          Username: data[i][8],
          Password: data[i][9],
          Created_At: data[i][10],
          Updated_At: data[i][11],
          Profile_Picture: data[i][12],
          Specialization: data[i][13],
          Is_Online: data[i][14]
        };
      }
    }
    Logger.log(`Employee with ID ${employeeId} not found.`);
    return null;
  } catch (error) {
    handleError('getEmployeeById', error);
    return null;
  }
}

/**
 * Update employee details.
 * @param {number} employeeId - ID of the employee.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   status: 'inactive',
 *   phone: '555-5678'
 * }
 */
function updateEmployee(employeeId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.EMPLOYEE_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === employeeId) {
        // Update fields if provided
        if (updatedData.name) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.name);
        if (updatedData.role) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.role);
        if (updatedData.email) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.email);
        if (updatedData.phone) sheet.getSheets()[0].getRange(i + 1, 5).setValue(updatedData.phone);
        if (updatedData.department) sheet.getSheets()[0].getRange(i + 1, 6).setValue(updatedData.department);
        if (updatedData.dateOfJoining) sheet.getSheets()[0].getRange(i + 1, 7).setValue(new Date(updatedData.dateOfJoining));
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 8).setValue(updatedData.status);
        if (updatedData.username) sheet.getSheets()[0].getRange(i + 1, 9).setValue(updatedData.username);
        if (updatedData.password) sheet.getSheets()[0].getRange(i + 1, 10).setValue(updatedData.password);
        if (updatedData.profilePicture) sheet.getSheets()[0].getRange(i + 1, 13).setValue(updatedData.profilePicture);
        if (updatedData.specialization) sheet.getSheets()[0].getRange(i + 1, 14).setValue(updatedData.specialization);
        if (updatedData.isOnline !== undefined) sheet.getSheets()[0].getRange(i + 1, 15).setValue(updatedData.isOnline);
        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 16).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Employee', employeeId, `Updated employee ID ${employeeId}`);
        Logger.log(`Updated employee with ID: ${employeeId}`);
        return;
      }
    }
    Logger.log(`Employee with ID ${employeeId} not found.`);
  } catch (error) {
    handleError('updateEmployee', error);
  }
}

/**
 * Delete an employee record.
 * @param {number} employeeId - ID of the employee.
 */
function deleteEmployee(employeeId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.EMPLOYEE_DETAILS);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === employeeId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Employee', employeeId, `Deleted employee ID ${employeeId}`);
        Logger.log(`Deleted employee with ID: ${employeeId}`);
        return;
      }
    }
    Logger.log(`Employee with ID ${employeeId} not found.`);
  } catch (error) {
    handleError('deleteEmployee', error);
  }
}

// --------------------- Salary Management ---------------------

/**
 * Add a new salary record.
 * @param {Object} salary - Salary details.
 * Example:
 * {
 *   employeeId: 201,
 *   basicSalary: 3000.00,
 *   bonuses: 500.00,
 *   deductions: 200.00,
 *   overtimeHours: 10,
 *   overtimeRate: 20.00,
 *   tax: 500.00,
 *   paymentDate: '2024-10-31',
 *   status: 'Pending',
 *   notes: 'October salary',
 *   payslipPath: 'URL_or_Path'
 * }
 */
function addNewSalary(salary) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.SALARIES);
    const newId = generateUniqueId(CONFIG.SHEETS.SALARIES);
    const netSalary = (salary.basicSalary || 0) + (salary.bonuses || 0) - (salary.deductions || 0) +
                      ((salary.overtimeHours || 0) * (salary.overtimeRate || 0)) - (salary.tax || 0);
    const timestamp = new Date();
    sheet.getSheets()[0].appendRow([
      newId,
      salary.employeeId || '',
      salary.basicSalary || 0.00,
      salary.bonuses || 0.00,
      salary.deductions || 0.00,
      salary.overtimeHours || 0,
      salary.overtimeRate || 0.00,
      salary.tax || 0.00,
      salary.paymentDate ? new Date(salary.paymentDate) : timestamp,
      netSalary || 0.00,
      salary.status || 'Pending',
      salary.notes || '',
      salary.payslipPath || '',
      timestamp,
      timestamp
    ]);
    logAudit(Session.getActiveUser().getEmail(), 'Add', 'Salary', newId, `Added salary for employee ID ${salary.employeeId}`);
    Logger.log(`Added new salary record with ID: ${newId}`);
  } catch (error) {
    handleError('addNewSalary', error);
  }
}

/**
 * Retrieve salary details by ID.
 * @param {number} salaryId - ID of the salary record.
 * @return {Object|null} - Salary details or null if not found.
 */
function getSalaryById(salaryId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.SALARIES);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) { // Skip header
      if (data[i][0] === salaryId) {
        return {
          ID: data[i][0],
          Employee_ID: data[i][1],
          Basic_Salary: data[i][2],
          Bonuses: data[i][3],
          Deductions: data[i][4],
          Overtime_Hours: data[i][5],
          Overtime_Rate: data[i][6],
          Tax: data[i][7],
          Payment_Date: data[i][8],
          Net_Salary: data[i][9],
          Status: data[i][10],
          Notes: data[i][11],
          Payslip_Path: data[i][12],
          Created_At: data[i][13],
          Updated_At: data[i][14]
        };
      }
    }
    Logger.log(`Salary record with ID ${salaryId} not found.`);
    return null;
  } catch (error) {
    handleError('getSalaryById', error);
    return null;
  }
}

/**
 * Update salary details.
 * @param {number} salaryId - ID of the salary record.
 * @param {Object} updatedData - Data to update.
 * Example:
 * {
 *   bonuses: 600.00,
 *   status: 'Paid'
 * }
 */
function updateSalary(salaryId, updatedData) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.SALARIES);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const timestamp = new Date();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === salaryId) {
        if (updatedData.employeeId) sheet.getSheets()[0].getRange(i + 1, 2).setValue(updatedData.employeeId);
        if (updatedData.basicSalary !== undefined) sheet.getSheets()[0].getRange(i + 1, 3).setValue(updatedData.basicSalary);
        if (updatedData.bonuses !== undefined) sheet.getSheets()[0].getRange(i + 1, 4).setValue(updatedData.bonuses);
        if (updatedData.deductions !== undefined) sheet.getSheets()[0].getRange(i + 1, 5).setValue(updatedData.deductions);
        if (updatedData.overtimeHours !== undefined) sheet.getSheets()[0].getRange(i + 1, 6).setValue(updatedData.overtimeHours);
        if (updatedData.overtimeRate !== undefined) sheet.getSheets()[0].getRange(i + 1, 7).setValue(updatedData.overtimeRate);
        if (updatedData.tax !== undefined) sheet.getSheets()[0].getRange(i + 1, 8).setValue(updatedData.tax);
        if (updatedData.paymentDate) sheet.getSheets()[0].getRange(i + 1, 9).setValue(new Date(updatedData.paymentDate));
        if (updatedData.status) sheet.getSheets()[0].getRange(i + 1, 11).setValue(updatedData.status);
        if (updatedData.notes) sheet.getSheets()[0].getRange(i + 1, 12).setValue(updatedData.notes);
        if (updatedData.payslipPath) sheet.getSheets()[0].getRange(i + 1, 13).setValue(updatedData.payslipPath);

        // Recalculate Net_Salary
        const basicSalary = parseFloat(sheet.getSheets()[0].getRange(i + 1, 3).getValue());
        const bonuses = parseFloat(sheet.getSheets()[0].getRange(i + 1, 4).getValue());
        const deductions = parseFloat(sheet.getSheets()[0].getRange(i + 1, 5).getValue());
        const overtimeHours = parseFloat(sheet.getSheets()[0].getRange(i + 1, 6).getValue());
        const overtimeRate = parseFloat(sheet.getSheets()[0].getRange(i + 1, 7).getValue());
        const tax = parseFloat(sheet.getSheets()[0].getRange(i + 1, 8).getValue());
        const netSalary = (basicSalary + bonuses - deductions) + (overtimeHours * overtimeRate) - tax;
        sheet.getSheets()[0].getRange(i + 1, 10).setValue(netSalary);

        // Update the 'Updated_At' timestamp
        sheet.getSheets()[0].getRange(i + 1, 15).setValue(timestamp);

        logAudit(Session.getActiveUser().getEmail(), 'Update', 'Salary', salaryId, `Updated salary ID ${salaryId}`);
        Logger.log(`Updated salary with ID: ${salaryId}`);
        return;
      }
    }
    Logger.log(`Salary record with ID ${salaryId} not found.`);
  } catch (error) {
    handleError('updateSalary', error);
  }
}

/**
 * Delete a salary record.
 * @param {number} salaryId - ID of the salary record.
 */
function deleteSalary(salaryId) {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.EMPLOYEES);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.SALARIES);
    const data = sheet.getSheets()[0].getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === salaryId) {
        sheet.getSheets()[0].deleteRow(i + 1);
        logAudit(Session.getActiveUser().getEmail(), 'Delete', 'Salary', salaryId, `Deleted salary ID ${salaryId}`);
        Logger.log(`Deleted salary with ID: ${salaryId}`);
        return;
      }
    }
    Logger.log(`Salary record with ID ${salaryId} not found.`);
  } catch (error) {
    handleError('deleteSalary', error);
  }
}

// --------------------- Additional Features ---------------------

/**
 * Send an email notification to a patient.
 * @param {string} email - Recipient's email address.
 * @param {string} subject - Email subject.
 * @param {string} body - Email body content.
 */
function sendEmailNotification(email, subject, body) {
  try {
    MailApp.sendEmail(email, subject, body);
    Logger.log(`Email sent to ${email} with subject "${subject}"`);
  } catch (error) {
    handleError('sendEmailNotification', error);
  }
}

/**
 * Send appointment reminders for the next day.
 * Schedule this function with a time-driven trigger (e.g., daily at 9 AM).
 */
function sendAppointmentReminders() {
  try {
    const folder = getOrCreateFolder(DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER).next().getId(), CONFIG.SUBFOLDERS.APPOINTMENTS);
    const sheet = getOrCreateSheet(folder, CONFIG.SHEETS.APPOINTMENTS);
    const data = sheet.getSheets()[0].getDataRange().getValues();
    const tomorrow = new Date();
    tomorrow.setDate(tomorrow.getDate() + 1);
    const tomorrowStr = Utilities.formatDate(tomorrow, Session.getScriptTimeZone(), 'yyyy-MM-dd');

    data.slice(1).forEach(row => {
      const appointmentDate = new Date(row[2]);
      const appointmentStr = Utilities.formatDate(appointmentDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
      const status = row[13];
      
      if (appointmentStr === tomorrowStr && status.toLowerCase() === 'confirmed') {
        const email = row[5];
        const name = row[4];
        const time = Utilities.formatDate(appointmentDate, Session.getScriptTimeZone(), 'HH:mm');
        const subject = 'Appointment Reminder';
        const body = `Dear ${name},\n\nThis is a reminder for your appointment scheduled on ${appointmentStr} at ${time}.\n\nBest regards,\nLongeva Healthcare`;
        sendEmailNotification(email, subject, body);
      }
    });

    Logger.log('Appointment reminders sent successfully.');
  } catch (error) {
    handleError('sendAppointmentReminders', error);
  }
}

/**
 * Watch for patient status changes to send notifications.
 * Example: When a patient's status is updated to 'Completed'.
 * This function can be set as an onEdit trigger.
 * @param {GoogleAppsScript.Events.SpreadsheetOnEdit} e - The onEdit event.
 */
function onEdit(e) {
  try {
    const range = e.range;
    const sheet = range.getSheet();
    const sheetName = sheet.getName();

    // Monitor Patient_Details sheet
    if (sheetName === CONFIG.SHEETS.PATIENT_DETAILS) {
      const statusColumn = 10; // 'Status' is the 10th column (J)
      if (range.getColumn() === statusColumn) {
        const newStatus = range.getValue().toLowerCase();
        const patientId = sheet.getRange(range.getRow(), 1).getValue();
        const patient = getPatientById(patientId);
        if (newStatus === 'completed') {
          const email = patient.Email;
          const name = patient.Name;
          const subject = 'Program Completion';
          const body = `Dear ${name},\n\nCongratulations on completing your program!\n\nBest regards,\nLongeva Healthcare`;
          sendEmailNotification(email, subject, body);
        }
      }
    }

    // Similar monitoring can be added for other sheets/entities as needed.

  } catch (error) {
    handleError('onEdit', error);
  }
}

// --------------------- Audit Log Setup ---------------------

/**
 * Set up the Audit Log sheet headers.
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} sheet - The Audit Log sheet.
 */
function setupAuditLogSheet(sheet) {
  const headers = [
    'Timestamp', 'User', 'Action', 'Entity', 'Entity_ID', 'Details'
  ];
  const sheetObject = sheet.getSheets()[0];
  const existingHeaders = sheetObject.getDataRange().getValues();
  if (existingHeaders.length === 0 || existingHeaders[0].join() !== headers.join()) {
    sheetObject.clear(); // Clear existing content
    sheetObject.appendRow(headers);
  }
}

// --------------------- Initialization Help ---------------------

/**
 * Provide instructions if the system is not initialized.
 */
function checkInitialization() {
  const mainFolders = DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER);
  if (!mainFolders.hasNext()) {
    MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'System Not Initialized', 'Please run the initializeSystem() function to set up the Longeva Healthcare Management System.');
  }
}

// --------------------- Deployment Instructions ---------------------

/**
 * Instructions for setting up triggers and other configurations.
 * Run these functions as needed.
 */

// To set up time-driven triggers programmatically (optional):
function createTimeDrivenTriggers() {
  try {
    // Delete existing triggers to prevent duplicates
    const allTriggers = ScriptApp.getProjectTriggers();
    allTriggers.forEach(trigger => {
      ScriptApp.deleteTrigger(trigger);
    });

    // Create a daily trigger for sending appointment reminders at 9 AM
    ScriptApp.newTrigger('sendAppointmentReminders')
      .timeBased()
      .everyDays(1)
      .atHour(9)
      .create();

    // Optional: Add more triggers as needed

    Logger.log('Time-driven triggers created successfully.');
  } catch (error) {
    handleError('createTimeDrivenTriggers', error);
  }
}

/**
 * Reset the system by deleting all created folders and sheets.
 * Use with caution.
 */
function resetSystem() {
  try {
    const folders = DriveApp.getFoldersByName(CONFIG.MAIN_FOLDER);
    if (folders.hasNext()) {
      const mainFolder = folders.next();
      mainFolder.setTrashed(true);
      logAudit(Session.getActiveUser().getEmail(), 'Reset', 'System', 0, 'System reset by admin.');
      Logger.log('System reset successfully.');
      MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'System Reset', 'The Longeva Healthcare Management System has been reset.');
    } else {
      Logger.log('Main folder not found. Nothing to reset.');
    }
  } catch (error) {
    handleError('resetSystem', error);
  }
}) and this error (/**
 * Get or create a folder by name within a parent folder.
 * @param {string} parentFolderId - ID of the parent folder.
 * @param {string} folderName - Name of the folder to get or create.
 * @return {GoogleAppsScript.Drive.Folder} - The retrieved or created folder.
 */
function getOrCreateFolder(parentFolderId, folderName) {
  const parentFolder = DriveApp.getFolderById(parentFolderId);
  const folders = parentFolder.getFoldersByName(folderName);
  return folders.hasNext() ? folders.next() : parentFolder.createFolder(folderName);
}
) and in this (1:41:28 PM	Error	
Exception: Invalid argument: id
getOrCreateFolder	@ Code.gs:46 )) and type full codes