Code.js

/**
 * Main Google Apps Script file to handle web app logic, Google Drive operations, and Google Sheets data.
 * Enhanced with secret string authentication for teacher portal, class/roll number organization,
 * task image/description support, overwrite submission functionality, email input validation, and download all submissions.
 */

/** Constants */
const SPREADSHEET_ID = "1ubIczrq20LqQUGIxwTcMnZgDSONUHjX7LujA_CtsUos";
const ASSIGNMENTS_SHEET = "Assignments";
const SUBMISSIONS_SHEET = "Submissions";
const DRIVE_FOLDER_ID = "1_MasnPgBVP1rmk4lI7yqi7broOOCBN16";
const APP_TITLE = "Assignment Submission System";
const SECRET_STRING = "@12@/!Zxqwe"; // Replace with a strong, unique secret string
const VALID_CLASSES = ["BSIT", "BSCS"];
const MAX_FILE_SIZE = 10 * 1024 * 1024; // 10MB

/** Initialize Spreadsheet and Drive */
const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
const assignmentSheet = ss.getSheetByName(ASSIGNMENTS_SHEET);
const submissionSheet = ss.getSheetByName(SUBMISSIONS_SHEET);

/**
 * doGet function to serve the appropriate HTML page based on role parameter and secret string.
 * @param {Object} e - Event object from HTTP request.
 * @return {HtmlOutput} - HTML page to display.
 */
function doGet(e) {
  Logger.log('doGet called with event: ' + JSON.stringify(e));
  const params = e ? e.parameter : {};
  const role = params.role || 'student';
  const secret = params.secret || '';
  Logger.log('Role requested: ' + role + ', Secret provided: ' + secret);

  if (role === "teacher") {
    if (secret !== SECRET_STRING) {
      Logger.log('Access denied: Invalid secret string');
      return HtmlService.createHtmlOutput('<p>Access denied. Invalid secret string.</p>')
        .setTitle(APP_TITLE + " - Access Denied");
    }
    Logger.log('Access granted to Teacher Portal with valid secret');
    return HtmlService.createHtmlOutputFromFile("Teacher")
      .setTitle(APP_TITLE + " - Teacher Portal")
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  } else if (role === "test") {
    return HtmlService.createHtmlOutputFromFile("TestFetch")
      .setTitle("Test Fetch Assignments")
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  } else {
    return HtmlService.createHtmlOutputFromFile("Student")
      .setTitle(APP_TITLE + " - Student Portal")
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  }
}

/**
 * Get user email for client-side use
 * @return {string} - User's email
 */
function getUserEmail() {
  try {
    const email = Session.getActiveUser().getEmail();
    Logger.log('User email fetched: ' + email);
    return email;
  } catch (e) {
    Logger.log('Error fetching user email: ' + e.message);
    throw new Error('Unable to fetch user email: ' + e.message);
  }
}

/**
 * Creates a new assignment with image and description, callable directly from client.
 * @param {Object} params - Parameters (title, deadline, description, imageData, imageName, imageMimeType).
 * @return {Object} - JSON response.
 */
function createAssignmentDirect(params) {
  Logger.log('createAssignmentDirect called with params: ' + JSON.stringify(params));
  return createAssignment(params);
}

/**
 * Creates a new assignment with image and description, stores in Google Sheet and Drive.
 * @param {Object} params - Parameters (title, deadline, description, imageData, imageName, imageMimeType).
 * @return {Object} - JSON response.
 */
function createAssignment(params) {
  Logger.log('createAssignment called with params: ' + JSON.stringify(params));
  const title = params.title ? params.title.trim() : "";
  let deadline = params.deadline;
  const description = params.description || "";
  const imageData = params.imageData;
  const imageName = params.imageName;
  const imageMimeType = params.imageMimeType;
  const assignmentId = Utilities.getUuid();

  // Validate required fields
  if (!title || !deadline) {
    Logger.log('Missing title or deadline: ' + title + ', ' + deadline);
    return { status: "error", message: "Title and deadline are required" };
  }

  // Parse and store deadline with time (set to end of day if no time specified)
  if (isValidDate(deadline)) {
    const date = new Date(deadline);
    if (isNaN(date.getTime())) {
      Logger.log('Invalid deadline format: ' + deadline);
      return { status: "error", message: "Invalid deadline format" };
    }
    // If no time is specified, set to end of day (23:59:59)
    if (deadline.indexOf(':') === -1) {
      date.setHours(23, 59, 59, 999);
    }
    deadline = date.toISOString();
  } else {
    Logger.log('Invalid deadline format: ' + deadline);
    return { status: "error", message: "Invalid deadline format" };
  }

  // Check for duplicate title
  const data = assignmentSheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][1].toString().trim() === title) {
      Logger.log('Assignment title already exists: ' + title);
      return { status: "error", message: "An assignment with this title already exists" };
    }
  }

  const rootFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
  const assignmentFolder = rootFolder.createFolder(assignmentId + "_" + title);
  let imageUrl = "";

  // Handle image upload
  if (imageData && imageName && imageMimeType) {
    try {
      const base64Data = imageData.replace(/^data:[^;]+;base64,/, '');
      const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), imageMimeType, imageName);
      const imageFile = assignmentFolder.createFile(blob);
      
      // Set sharing to public view
      imageFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      
      // Store only the Drive thumbnail URL to avoid cell size limits
      imageUrl = `https://drive.google.com/thumbnail?id=${imageFile.getId()}&sz=w400`;
      Logger.log('Image uploaded to Drive, storing thumbnail URL: ' + imageUrl);
    } catch (e) {
      Logger.log('Error uploading image: ' + e.message);
      // Continue with sheet update even if image fails
    }
  }

  try {
    assignmentSheet.appendRow([assignmentId, title, deadline, description, assignmentFolder.getId(), imageUrl]);
    Logger.log('Assignment created: ' + title + ', Deadline: ' + deadline);
    return { status: "success", message: "Assignment created successfully", assignmentId: assignmentId };
  } catch (e) {
    Logger.log('Error appending to sheet: ' + e.message);
    return { status: "error", message: "Failed to save assignment: " + e.message };
  }
}

/**
 * Edits an existing assignment.
 * @param {Object} params - Parameters (assignmentId, title, deadline, description, imageData, imageName, imageMimeType).
 * @return {Object} - JSON response.
 */
function editAssignmentDirect(params) {
  Logger.log('editAssignmentDirect called with params: ' + JSON.stringify(params));
  const assignmentId = params.assignmentId;
  const title = params.title ? params.title.trim() : "";
  let deadline = params.deadline;
  const description = params.description || "";
  const imageData = params.imageData;
  const imageName = params.imageName;
  const imageMimeType = params.imageMimeType;

  // Validate required fields
  if (!assignmentId || !title || !deadline) {
    Logger.log('Missing required fields: ' + assignmentId + ', ' + title + ', ' + deadline);
    return { status: "error", message: "Assignment ID, title, and deadline are required" };
  }

  // Parse and store deadline with time (set to end of day if no time specified)
  if (isValidDate(deadline)) {
    const date = new Date(deadline);
    if (isNaN(date.getTime())) {
      Logger.log('Invalid deadline format: ' + deadline);
      return { status: "error", message: "Invalid deadline format" };
    }
    // If no time is specified, set to end of day (23:59:59)
    if (deadline.indexOf(':') === -1) {
      date.setHours(23, 59, 59, 999);
    }
    deadline = date.toISOString();
  } else {
    Logger.log('Invalid deadline format: ' + deadline);
    return { status: "error", message: "Invalid deadline format" };
  }

  // Check if assignment exists and get folder ID
  const data = assignmentSheet.getDataRange().getValues();
  let rowIndex = -1;
  let oldFolderId = null;
  let oldDeadline = null;
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === assignmentId) {
      rowIndex = i + 1;
      oldFolderId = data[i][4];
      oldDeadline = new Date(data[i][2]);
      break;
    }
  }

  if (rowIndex === -1) {
    Logger.log('Assignment not found: ' + assignmentId);
    return { status: "error", message: "Assignment not found" };
  }

  // Check if deadline has passed
  const now = new Date();
  const oldDeadlineDate = new Date(oldDeadline);
  if (now > oldDeadlineDate) {
    Logger.log('Cannot edit assignment, deadline passed: ' + assignmentId + ' (Current: ' + now.toISOString() + ', Deadline: ' + oldDeadlineDate.toISOString() + ')');
    return { status: "error", message: "Cannot edit assignment after deadline" };
  }

  // Check for duplicate title (excluding current assignment)
  for (let i = 1; i < data.length; i++) {
    if (i !== rowIndex - 1 && data[i][1].toString().trim() === title) {
      Logger.log('Assignment title already exists: ' + title);
      return { status: "error", message: "An assignment with this title already exists" };
    }
  }

  const rootFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
  let assignmentFolder;
  try {
    assignmentFolder = DriveApp.getFolderById(oldFolderId);
    if (title !== data[rowIndex - 1][1]) {
      assignmentFolder.setName(assignmentId + "_" + title);
    }
  } catch (e) {
    Logger.log('Error accessing folder, creating new: ' + e.message);
    assignmentFolder = rootFolder.createFolder(assignmentId + "_" + title);
    oldFolderId = assignmentFolder.getId();
  }

  let imageUrl = data[rowIndex - 1][5] || "";
  if (imageData && imageName && imageMimeType) {
    try {
      const base64Data = imageData.replace(/^data:[^;]+;base64,/, '');
      const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), imageMimeType, imageName);
      const imageFile = assignmentFolder.createFile(blob);
      
      // Set sharing to public view
      imageFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      
      // Store only the Drive file ID to avoid cell size limits
      imageUrl = `https://drive.google.com/thumbnail?id=${imageFile.getId()}&sz=w400`;
      Logger.log('Image uploaded to Drive, storing thumbnail URL: ' + imageUrl);
    } catch (e) {
      Logger.log('Error uploading image: ' + e.message);
      // Continue with sheet update even if image fails
    }
  }

  try {
    assignmentSheet.getRange(rowIndex, 1, 1, 6).setValues([[assignmentId, title, deadline, description, assignmentFolder.getId(), imageUrl]]);
    Logger.log('Assignment edited: ' + title + ', Deadline: ' + deadline);
    return { status: "success", message: "Assignment edited successfully", assignmentId: assignmentId };
  } catch (e) {
    Logger.log('Error updating sheet: ' + e.message);
    return { status: "error", message: "Failed to edit assignment: " + e.message };
  }
}

/**
 * Handles file upload from student, callable directly from client. Overwrites existing submission if found.
 * @param {Object} params - Parameters (assignmentId, className, studentName, rollNumber, email, filename, mimetype, data, filesize).
 * @return {Object} - JSON response.
 */
function uploadSubmissionDirect(params) {
  Logger.log('uploadSubmissionDirect called with params: ' + JSON.stringify(params));
  const assignmentId = params.assignmentId;
  const className = params.className;
  const studentName = params.studentName ? params.studentName.trim() : "";
  const rollNumber = params.rollNumber ? params.rollNumber.trim() : "";
  const email = params.email ? params.email.trim().toLowerCase() : "";
  const fileName = params.filename;
  const mimeType = params.mimetype;
  const data = params.data;
  const fileSize = parseInt(params.filesize) || 0;
  const studentEmail = Session.getActiveUser().getEmail();

  // Validate inputs
  if (!VALID_CLASSES.includes(className)) {
    Logger.log('Invalid class: ' + className);
    return { status: "error", message: "Invalid class selected" };
  }
  const rollNumberPattern = className === 'BSIT' ? /^BIT\d{5}$/ : /^BCS\d{5}$/;
  if (!rollNumber.match(rollNumberPattern)) {
    Logger.log('Invalid roll number: ' + rollNumber);
    return { status: "error", message: `Invalid roll number format (e.g., ${className}22001)` };
  }
  if (!studentName) {
    Logger.log('Missing student name');
    return { status: "error", message: "Student name is required" };
  }
  if (!email || !email.match(/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/)) {
    Logger.log('Invalid email: ' + email);
    return { status: "error", message: "Invalid email format" };
  }
  // Email validation removed for flexibility
  // if (email !== studentEmail) {
  //   Logger.log('Email mismatch: ' + email + ' vs ' + studentEmail);
  //   return { status: "error", message: "Email does not match logged-in user" };
  // }
  if (!fileName || !mimeType || !data) {
    Logger.log('Missing file data');
    return { status: "error", message: "File upload data is missing" };
  }
  if (fileSize > MAX_FILE_SIZE) {
    Logger.log('File too large: ' + fileSize);
    return { status: "error", message: "File size exceeds 10MB limit" };
  }

  // Find assignment
  const assignmentData = assignmentSheet.getDataRange().getValues();
  let assignmentFolderId = null, assignmentTitle = null, deadline = null;

  for (let i = 1; i < assignmentData.length; i++) {
    if (assignmentData[i][0] === assignmentId) {
      assignmentFolderId = assignmentData[i][4];
      assignmentTitle = assignmentData[i][1];
      deadline = new Date(assignmentData[i][2]);
      break;
    }
  }

  if (!assignmentFolderId) {
    Logger.log('Assignment not found: ' + assignmentId);
    return { status: "error", message: "Assignment not found" };
  }

  // Check deadline
  const now = new Date();
  const deadlineDate = new Date(deadline);
  if (now > deadlineDate) {
    Logger.log('Deadline passed for assignment: ' + assignmentId + ' (Current: ' + now.toISOString() + ', Deadline: ' + deadlineDate.toISOString() + ')');
    return { status: "error", message: "Submission deadline has passed" };
  }

  // Check for existing submission
  const submissionData = submissionSheet.getDataRange().getValues();
  let existingSubmissionRow = -1;
  let existingFileId = null;
  for (let i = 1; i < submissionData.length; i++) {
    if (submissionData[i][0] === assignmentId && submissionData[i][4] === rollNumber && submissionData[i][2] === className) {
      existingSubmissionRow = i + 1;
      const fileUrl = submissionData[i][6];
      const fileIdMatch = fileUrl.match(/\/d\/([a-zA-Z0-9_-]+)/);
      if (fileIdMatch && fileIdMatch[1]) {
        existingFileId = fileIdMatch[1];
      }
      break;
    }
  }

  // Organize folder structure
  const rootFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
  const classFolder = getOrCreateFolder(rootFolder, className);
  const studentFolder = getOrCreateFolder(classFolder, rollNumber);
  const assignmentFolder = getOrCreateFolder(studentFolder, assignmentTitle);

  // Upload file
  try {
    const base64Data = data.replace(/^data:[^;]+;base64,/, '');
    const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), mimeType, fileName);
    const uploadedFile = assignmentFolder.createFile(blob);
    uploadedFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    const fileUrl = uploadedFile.getUrl();
    Logger.log('File uploaded: ' + fileUrl);

    // Delete existing file if it exists
    if (existingFileId) {
      try {
        const oldFile = DriveApp.getFileById(existingFileId);
        oldFile.setTrashed(true);
        Logger.log('Deleted existing file: ' + existingFileId);
      } catch (e) {
        Logger.log('Error deleting existing file: ' + e.message);
      }
    }

    // Update or append submission
    if (existingSubmissionRow !== -1) {
      // For single file, still create the folder structure and return folder URL
      submissionSheet.getRange(existingSubmissionRow, 1, 1, 9).setValues([[assignmentId, email, className, studentName, rollNumber, fileName, assignmentFolder.getUrl(), new Date(), "Edited"]]);
      Logger.log('Submission updated: ' + fileName);
      return { status: "success", message: "Submission updated successfully", fileUrl: fileUrl, folderUrl: assignmentFolder.getUrl() };
    } else {
      submissionSheet.appendRow([assignmentId, email, className, studentName, rollNumber, fileName, assignmentFolder.getUrl(), new Date(), "Submitted"]);
      Logger.log('Submission created: ' + fileName);
      return { status: "success", message: "File uploaded successfully", fileUrl: fileUrl, folderUrl: assignmentFolder.getUrl() };
    }
  } catch (e) {
    Logger.log('Error uploading file: ' + e.message);
    return { status: "error", message: "Failed to upload file: " + e.message };
  }
}

/**
 * Handles multiple file uploads from student, callable directly from client. Overwrites existing submission if found.
 * @param {Object} params - Parameters (assignmentId, className, studentName, rollNumber, email, files array).
 * @return {Object} - JSON response.
 */
function uploadMultipleSubmissionsDirect(params) {
  Logger.log('uploadMultipleSubmissionsDirect called with params: ' + JSON.stringify({...params, files: params.files ? params.files.length + ' files' : 'no files'}));
  const assignmentId = params.assignmentId;
  const className = params.className;
  const studentName = params.studentName ? params.studentName.trim() : "";
  const rollNumber = params.rollNumber ? params.rollNumber.trim() : "";
  const email = params.email ? params.email.trim().toLowerCase() : "";
  const files = params.files || [];
  const studentEmail = Session.getActiveUser().getEmail();

  // Validate inputs
  if (!VALID_CLASSES.includes(className)) {
    Logger.log('Invalid class: ' + className);
    return { status: "error", message: "Invalid class selected" };
  }
  const rollNumberPattern = className === 'BSIT' ? /^BIT\d{5}$/ : /^BCS\d{5}$/;
  if (!rollNumber.match(rollNumberPattern)) {
    Logger.log('Invalid roll number: ' + rollNumber);
    return { status: "error", message: `Invalid roll number format (e.g., ${className}22001)` };
  }
  if (!studentName) {
    Logger.log('Missing student name');
    return { status: "error", message: "Student name is required" };
  }
  if (!email || !email.match(/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/)) {
    Logger.log('Invalid email: ' + email);
    return { status: "error", message: "Invalid email format" };
  }
  // Email validation removed for flexibility
  // if (email !== studentEmail) {
  //   Logger.log('Email mismatch: ' + email + ' vs ' + studentEmail);
  //   return { status: "error", message: "Email does not match logged-in user" };
  // }
  if (!files || files.length === 0) {
    Logger.log('No files provided');
    return { status: "error", message: "At least one file is required" };
  }

  // Check total file size
  let totalSize = 0;
  for (let i = 0; i < files.length; i++) {
    totalSize += parseInt(files[i].size) || 0;
  }
  if (totalSize > 50 * 1024 * 1024) { // 50MB total limit
    Logger.log('Total file size too large: ' + totalSize);
    return { status: "error", message: "Total file size exceeds 50MB limit" };
  }

  // Find assignment
  const assignmentData = assignmentSheet.getDataRange().getValues();
  let assignmentFolderId = null, assignmentTitle = null, deadline = null;

  for (let i = 1; i < assignmentData.length; i++) {
    if (assignmentData[i][0] === assignmentId) {
      assignmentFolderId = assignmentData[i][4];
      assignmentTitle = assignmentData[i][1];
      deadline = new Date(assignmentData[i][2]);
      break;
    }
  }

  if (!assignmentFolderId) {
    Logger.log('Assignment not found: ' + assignmentId);
    return { status: "error", message: "Assignment not found" };
  }

  // Check deadline
  const now = new Date();
  const deadlineDate = new Date(deadline);
  if (now > deadlineDate) {
    Logger.log('Deadline passed for assignment: ' + assignmentId + ' (Current: ' + now.toISOString() + ', Deadline: ' + deadlineDate.toISOString() + ')');
    return { status: "error", message: "Submission deadline has passed" };
  }

  // Check for existing submission and delete old files
  const submissionData = submissionSheet.getDataRange().getValues();
  let existingSubmissionRow = -1;
  const existingFileIds = [];
  
  for (let i = 1; i < submissionData.length; i++) {
    if (submissionData[i][0] === assignmentId && submissionData[i][4] === rollNumber && submissionData[i][2] === className) {
      existingSubmissionRow = i + 1;
      const fileUrl = submissionData[i][6];
      const fileIdMatch = fileUrl.match(/\/d\/([a-zA-Z0-9_-]+)/);
      if (fileIdMatch && fileIdMatch[1]) {
        existingFileIds.push(fileIdMatch[1]);
      }
      break;
    }
  }

  // Organize folder structure
  const rootFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
  const classFolder = getOrCreateFolder(rootFolder, className);
  const studentFolder = getOrCreateFolder(classFolder, rollNumber);
  const assignmentFolder = getOrCreateFolder(studentFolder, assignmentTitle);

  // Delete existing files if they exist
  existingFileIds.forEach(fileId => {
    try {
      const oldFile = DriveApp.getFileById(fileId);
      oldFile.setTrashed(true);
      Logger.log('Deleted existing file: ' + fileId);
    } catch (e) {
      Logger.log('Error deleting existing file: ' + e.message);
    }
  });

  // Upload all files
  const uploadedFiles = [];
  let uploadErrors = [];
  
  try {
    for (let i = 0; i < files.length; i++) {
      const file = files[i];
      try {
        const base64Data = file.data.replace(/^data:[^;]+;base64,/, '');
        const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), file.type, file.name);
        const uploadedFile = assignmentFolder.createFile(blob);
        uploadedFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
        uploadedFiles.push({
          name: file.name,
          url: uploadedFile.getUrl(),
          id: uploadedFile.getId()
        });
        Logger.log('File uploaded: ' + file.name);
      } catch (e) {
        Logger.log('Error uploading file ' + file.name + ': ' + e.message);
        uploadErrors.push(file.name);
      }
    }

    if (uploadedFiles.length === 0) {
      return { status: "error", message: "Failed to upload any files" };
    }

    // Create submission record with folder URL and file count
    const folderUrl = assignmentFolder.getUrl();
    const fileNames = uploadedFiles.map(f => f.name).join(', ');
    const submissionText = `${uploadedFiles.length} file(s): ${fileNames}`;

    // Update or append submission
    if (existingSubmissionRow !== -1) {
      submissionSheet.getRange(existingSubmissionRow, 1, 1, 9).setValues([
        [assignmentId, email, className, studentName, rollNumber, 
        submissionText, folderUrl, new Date(), "Edited"]
      ]);
      Logger.log('Multiple files submission updated');
    } else {
      submissionSheet.appendRow([
        assignmentId, email, className, studentName, rollNumber, 
        submissionText, folderUrl, new Date(), "Submitted"
      ]);
      Logger.log('Multiple files submission created');
    }

    let message = `Successfully uploaded ${uploadedFiles.length} file(s)`;
    if (uploadErrors.length > 0) {
      message += `. Failed to upload: ${uploadErrors.join(', ')}`;
    }

    return { 
      status: "success", 
      message: message,
      uploadedCount: uploadedFiles.length,
      failedCount: uploadErrors.length,
      folderUrl: folderUrl
    };
    
  } catch (e) {
    Logger.log('Error in multiple file upload: ' + e.message);
    return { status: "error", message: "Failed to upload files: " + e.message };
  }
}

/**
 * Gets list of active assignments (not past deadline).
 * @return {Array} - List of assignments.
 */
function getActiveAssignments() {
  try {
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheet = ss.getSheetByName(ASSIGNMENTS_SHEET);
    
    if (!sheet) {
      Logger.log("Assignments sheet not found");
      throw new Error("Assignments sheet not found");
    }
    
    const range = sheet.getDataRange();
    const values = range.getValues();
    const assignments = [];
    const now = new Date();
    
    for (let i = 1; i < values.length; i++) {
      const row = values[i];
      
      if (!row[0] && !row[1]) continue;
      
      let deadline;
      if (row[2] instanceof Date) {
        deadline = row[2];
      } else if (typeof row[2] === 'number') {
        const excelEpoch = new Date(1899, 11, 30);
        deadline = new Date(excelEpoch.getTime() + row[2] * 24 * 60 * 60 * 1000);
      } else {
        deadline = new Date(row[2]);
      }
      
      if (isNaN(deadline.getTime())) {
        Logger.log(`Invalid date in row ${i+1}: ${row[2]}`);
        continue;
      }
      
      // Check if deadline has passed (now includes time comparison)
      if (deadline > now) {
        assignments.push({
          id: row[0],
          title: row[1],
          deadline: Utilities.formatDate(deadline, Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm"),
          deadlineDate: deadline.toISOString().split('T')[0],
          description: row[3] || '',
          imageUrl: convertToViewableImageUrl(row[5] || '')
        });
      }
    }
    
    Logger.log(`Found ${assignments.length} active assignments`);
    return assignments;
  } catch (error) {
    Logger.log("Critical error in getActiveAssignments: " + error.message);
    throw new Error("Failed to fetch assignments: " + error.message);
  }
}

/**
 * Gets list of all assignments regardless of deadline status (for submissions view).
 * @return {Array} - List of all assignments.
 */
function getAllAssignments() {
  try {
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheet = ss.getSheetByName(ASSIGNMENTS_SHEET);
    
    if (!sheet) {
      Logger.log("Assignments sheet not found");
      throw new Error("Assignments sheet not found");
    }
    
    const range = sheet.getDataRange();
    const values = range.getValues();
    const assignments = [];
    
    for (let i = 1; i < values.length; i++) {
      const row = values[i];
      
      if (!row[0] && !row[1]) continue;
      
      let deadline;
      if (row[2] instanceof Date) {
        deadline = row[2];
      } else if (typeof row[2] === 'number') {
        const excelEpoch = new Date(1899, 11, 30);
        deadline = new Date(excelEpoch.getTime() + row[2] * 24 * 60 * 60 * 1000);
      } else {
        deadline = new Date(row[2]);
      }
      
      if (isNaN(deadline.getTime())) {
        Logger.log(`Invalid date in row ${i+1}: ${row[2]}`);
        continue;
      }
      
      const now = new Date();
      const status = deadline > now ? "Active" : "Expired";
      
      assignments.push({
        id: row[0],
        title: row[1],
        deadline: Utilities.formatDate(deadline, Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm"),
        deadlineDate: deadline.toISOString().split('T')[0],
        description: row[3] || '',
        imageUrl: convertToViewableImageUrl(row[5] || ''),
        status: status
      });
    }
    
    // Sort by deadline (most recent first)
    assignments.sort((a, b) => new Date(b.deadlineDate) - new Date(a.deadlineDate));
    
    Logger.log(`Found ${assignments.length} total assignments`);
    return assignments;
  } catch (error) {
    Logger.log("Critical error in getAllAssignments: " + error.message);
    throw new Error("Failed to fetch all assignments: " + error.message);
  }
}

/**
 * Gets list of submissions for a given assignment with counts per class.
 * @param {string} assignmentId - Assignment ID.
 * @return {Object} - Submissions and class counts.
 */
function getSubmissions(assignmentId) {
  Logger.log('getSubmissions called for assignmentId: ' + assignmentId);
  try {
    if (!submissionSheet) {
      Logger.log("Submissions sheet not found");
      return { submissions: [], classCounts: { BSIT: 0, BSCS: 0 } };
    }
    const data = submissionSheet.getDataRange().getValues();
    const submissions = [];
    const classCounts = { BSIT: 0, BSCS: 0 };

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === assignmentId) {
        submissions.push({
          studentEmail: data[i][1],
          className: data[i][2],
          studentName: data[i][3],
          rollNumber: data[i][4],
          fileName: data[i][5],
          fileUrl: data[i][6],
          timestamp: Utilities.formatDate(new Date(data[i][7]), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm:ss"),
          status: data[i][8] || "Submitted"
        });
        classCounts[data[i][2]] = (classCounts[data[i][2]] || 0) + 1;
      }
    }
    Logger.log('Submissions for ' + assignmentId + ': ' + JSON.stringify({ submissions, classCounts }));
    return { submissions, classCounts };
  } catch (e) {
    Logger.log('Error in getSubmissions: ' + e.message);
    return { submissions: [], classCounts: { BSIT: 0, BSCS: 0 } };
  }
}

/**
 * Downloads all submissions for an assignment as a ZIP file.
 * @param {string} assignmentId - Assignment ID.
 * @return {Object} - Base64-encoded ZIP file and filename.
 */
function downloadSubmissions(assignmentId) {
  Logger.log('downloadSubmissions called for assignmentId: ' + assignmentId);
  try {
    if (!submissionSheet) {
      Logger.log("Submissions sheet not found");
      return { status: "error", message: "Submissions sheet not found" };
    }
    const data = submissionSheet.getDataRange().getValues();
    const zipArchive = Utilities.zip([]);
    let fileCount = 0;

    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === assignmentId) {
        try {
          const fileUrl = data[i][6];
          const fileIdMatch = fileUrl.match(/\/d\/([a-zA-Z0-9_-]+)/);
          if (!fileIdMatch || !fileIdMatch[1]) {
            Logger.log(`Invalid file URL for submission at row ${i + 1}: ${fileUrl}`);
            continue;
          }
          const fileId = fileIdMatch[1];
          const file = DriveApp.getFileById(fileId);
          const fileName = `${data[i][4]}_${data[i][5]}`; // RollNumber_FileName
          zipArchive.addFile(file.getBlob().setName(fileName));
          fileCount++;
        } catch (e) {
          Logger.log(`Error adding file to ZIP at row ${i + 1}: ${e.message}`);
          continue;
        }
      }
    }

    if (fileCount === 0) {
      Logger.log('No valid submissions found for assignment: ' + assignmentId);
      return { status: "error", message: "No valid submissions found for this assignment" };
    }

    const zipBlob = zipArchive.getAs('application/zip');
    return {
      status: "success",
      data: Utilities.base64Encode(zipBlob.getBytes()),
      filename: `submissions_${assignmentId}.zip`
    };
  } catch (e) {
    Logger.log('Error in downloadSubmissions: ' + e.message);
    return { status: "error", message: "Failed to download submissions: " + e.message };
  }
}

/**
 * Deletes an assignment and all its associated data (folder and submissions).
 * @param {string} assignmentId - Assignment ID to delete.
 * @return {Object} - JSON response.
 */
function deleteAssignmentDirect(assignmentId) {
  Logger.log('deleteAssignmentDirect called with assignmentId: ' + assignmentId);
  
  if (!assignmentId) {
    Logger.log('Missing assignment ID');
    return { status: "error", message: "Assignment ID is required" };
  }

  try {
    // Find assignment in the sheet
    const assignmentData = assignmentSheet.getDataRange().getValues();
    let assignmentRowIndex = -1;
    let assignmentTitle = '';
    let assignmentFolderId = '';
    
    for (let i = 1; i < assignmentData.length; i++) {
      if (assignmentData[i][0] === assignmentId) {
        assignmentRowIndex = i + 1; // +1 because getRange is 1-indexed
        assignmentTitle = assignmentData[i][1];
        assignmentFolderId = assignmentData[i][4];
        break;
      }
    }

    if (assignmentRowIndex === -1) {
      Logger.log('Assignment not found: ' + assignmentId);
      return { status: "error", message: "Assignment not found" };
    }

    // Delete assignment folder from Drive (this will delete all files inside)
    if (assignmentFolderId) {
      try {
        const assignmentFolder = DriveApp.getFolderById(assignmentFolderId);
        assignmentFolder.setTrashed(true);
        Logger.log('Assignment folder deleted: ' + assignmentFolderId);
      } catch (e) {
        Logger.log('Error deleting assignment folder: ' + e.message);
        // Continue with deletion even if folder deletion fails
      }
    }

    // Delete all submissions for this assignment from the submissions sheet
    const submissionData = submissionSheet.getDataRange().getValues();
    const submissionsToDelete = [];
    
    // Find all rows with this assignment ID (going backwards to avoid index issues)
    for (let i = submissionData.length - 1; i >= 1; i--) {
      if (submissionData[i][0] === assignmentId) {
        submissionsToDelete.push(i + 1); // +1 because deleteRow is 1-indexed
      }
    }
    
    // Delete submission rows
    submissionsToDelete.forEach(rowIndex => {
      submissionSheet.deleteRow(rowIndex);
    });
    
    Logger.log('Deleted ' + submissionsToDelete.length + ' submissions for assignment: ' + assignmentId);

    // Delete assignment row from assignments sheet
    assignmentSheet.deleteRow(assignmentRowIndex);
    
    Logger.log('Assignment deleted successfully: ' + assignmentTitle);
    return { 
      status: "success", 
      message: "Assignment '" + assignmentTitle + "' and all its submissions have been deleted successfully",
      deletedSubmissions: submissionsToDelete.length
    };
    
  } catch (e) {
    Logger.log('Error deleting assignment: ' + e.message);
    return { status: "error", message: "Failed to delete assignment: " + e.message };
  }
}

/**
 * Gets all submissions for a specific student based on class, roll number, and email.
 * @param {Object} params - Parameters (className, rollNumber, email).
 * @return {Object} - Student submissions with assignment details and stats.
 */
function getStudentSubmissions(params) {
  Logger.log('getStudentSubmissions called with params: ' + JSON.stringify(params));
  
  try {
    // Check if params exist
    if (!params) {
      Logger.log('No params provided');
      return { status: "error", message: "No parameters provided", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }
    
    const className = params.className;
    const rollNumber = params.rollNumber ? params.rollNumber.trim() : "";
    const email = params.email ? params.email.trim().toLowerCase() : "";
    
    Logger.log('Processing request for: ' + rollNumber + ' in class ' + className);
    
    let studentEmail;
    try {
      studentEmail = Session.getActiveUser().getEmail();
      Logger.log('Student email from session: ' + studentEmail);
    } catch (e) {
      Logger.log('Error getting user email: ' + e.message);
      return { status: "error", message: "Unable to verify user identity", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }

    // Validate inputs
    if (!VALID_CLASSES.includes(className)) {
      Logger.log('Invalid class: ' + className);
      return { status: "error", message: "Invalid class selected", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }
    
    const rollNumberPattern = className === 'BSIT' ? /^BIT\d{5}$/ : /^BCS\d{5}$/;
    if (!rollNumber.match(rollNumberPattern)) {
      Logger.log('Invalid roll number: ' + rollNumber);
      return { status: "error", message: `Invalid roll number format (e.g., ${className}22001)`, submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }
    
    if (!email || !email.match(/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/)) {
      Logger.log('Invalid email: ' + email);
      return { status: "error", message: "Invalid email format", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }
    
    // Email validation removed for flexibility
    // if (email !== studentEmail) {
    //   Logger.log('Email mismatch: ' + email + ' vs ' + studentEmail);
    //   return { status: "error", message: "Email does not match logged-in user", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    // }

    // Check if sheets exist
    if (!assignmentSheet) {
      Logger.log('Assignment sheet not found');
      return { status: "error", message: "Assignment data not available", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }
    
    if (!submissionSheet) {
      Logger.log('Submission sheet not found');
      return { status: "error", message: "Submission data not available", submissions: [], stats: {total: 0, submitted: 0, pending: 0} };
    }

    // Get all assignments
    Logger.log('Fetching assignment data...');
    const assignmentData = assignmentSheet.getDataRange().getValues();
    const assignmentMap = new Map();
    
    for (let i = 1; i < assignmentData.length; i++) {
      if (assignmentData[i][0]) { // Make sure assignment ID exists
        assignmentMap.set(assignmentData[i][0], {
          id: assignmentData[i][0],
          title: assignmentData[i][1],
          deadline: assignmentData[i][2],
          description: assignmentData[i][3] || ''
        });
      }
    }
    
    Logger.log('Found ' + assignmentMap.size + ' assignments');

    // Get student submissions
    Logger.log('Fetching submission data...');
    const submissionData = submissionSheet.getDataRange().getValues();
    const studentSubmissions = [];
    
    for (let i = 1; i < submissionData.length; i++) {
      const row = submissionData[i];
      if (row[0] && row[2] === className && row[4] === rollNumber && row[1] === email) {
        const assignmentId = row[0];
        const assignment = assignmentMap.get(assignmentId);
        
        if (assignment) {
          let deadline;
          try {
            if (assignment.deadline instanceof Date) {
              deadline = assignment.deadline;
            } else if (typeof assignment.deadline === 'number') {
              const excelEpoch = new Date(1899, 11, 30);
              deadline = new Date(excelEpoch.getTime() + assignment.deadline * 24 * 60 * 60 * 1000);
            } else {
              deadline = new Date(assignment.deadline);
            }
            
            if (isNaN(deadline.getTime())) {
              Logger.log('Invalid deadline for assignment: ' + assignmentId);
              deadline = new Date(); // Use current date as fallback
            }
          } catch (e) {
            Logger.log('Error parsing deadline for assignment ' + assignmentId + ': ' + e.message);
            deadline = new Date(); // Use current date as fallback
          }

          studentSubmissions.push({
            assignmentId: assignmentId,
            assignmentTitle: assignment.title,
            description: assignment.description,
            deadline: Utilities.formatDate(deadline, Session.getScriptTimeZone(), "dd/MM/yyyy"),
            deadlineDate: deadline,
            fileName: row[5] || 'Unknown file',
            fileUrl: row[6] || '',
            submittedOn: Utilities.formatDate(new Date(row[7]), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm"),
            submittedDate: new Date(row[7]),
            status: row[8] || "Submitted"
          });
        }
      }
    }

    // Sort by submission date (most recent first)
    studentSubmissions.sort((a, b) => new Date(b.submittedDate) - new Date(a.submittedDate));

    // Calculate stats
    const totalAssignments = assignmentMap.size;
    const submittedAssignments = studentSubmissions.length;
    
    const stats = {
      total: totalAssignments,
      submitted: submittedAssignments,
      pending: totalAssignments - submittedAssignments
    };

    Logger.log(`Found ${submittedAssignments} submissions for student ${rollNumber} out of ${totalAssignments} total assignments`);
    
    const result = {
      status: "success",
      submissions: studentSubmissions,
      stats: stats
    };
    
    Logger.log('Returning result: ' + JSON.stringify({...result, submissions: result.submissions.length + ' submissions'}));
    return result;
    
  } catch (e) {
    Logger.log('Critical error in getStudentSubmissions: ' + e.message);
    Logger.log('Error stack: ' + e.stack);
    return { 
      status: "error", 
      message: "Failed to fetch submissions: " + e.message,
      submissions: [],
      stats: {total: 0, submitted: 0, pending: 0}
    };
  }
}

/**
 * Utility function to fix existing assignments that have large base64 image data
 * This should be run once to clean up any assignments causing the 50k character limit error
 * @return {Object} - Cleanup results
 */
function fixExistingImageData() {
  Logger.log('Starting cleanup of existing base64 image data...');
  
  try {
    const data = assignmentSheet.getDataRange().getValues();
    let fixedCount = 0;
    let errorCount = 0;
    
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const assignmentId = row[0];
      const imageUrl = row[5];
      
      // Check if this row has a large base64 image
      if (imageUrl && imageUrl.startsWith('data:image/') && imageUrl.length > 10000) {
        try {
          Logger.log(`Fixing large image data for assignment: ${row[1]}`);
          
          // Extract image data
          const base64Data = imageUrl.replace(/^data:[^;]+;base64,/, '');
          const mimeMatch = imageUrl.match(/^data:([^;]+);base64,/);
          const mimeType = mimeMatch ? mimeMatch[1] : 'image/jpeg';
          
          // Get or create assignment folder
          let assignmentFolder;
          try {
            assignmentFolder = DriveApp.getFolderById(row[4]);
          } catch (e) {
            // If folder doesn't exist, create it
            const rootFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
            assignmentFolder = rootFolder.createFolder(assignmentId + "_" + row[1]);
            // Update folder ID in the sheet
            assignmentSheet.getRange(i + 1, 5).setValue(assignmentFolder.getId());
          }
          
          // Create image file in Drive
          const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), mimeType, 'assignment_image.jpg');
          const imageFile = assignmentFolder.createFile(blob);
          imageFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
          
          // Update sheet with thumbnail URL instead of base64
          const thumbnailUrl = `https://drive.google.com/thumbnail?id=${imageFile.getId()}&sz=w400`;
          assignmentSheet.getRange(i + 1, 6).setValue(thumbnailUrl);
          
          fixedCount++;
          Logger.log(`Fixed assignment: ${row[1]} - replaced ${imageUrl.length} characters with Drive URL`);
          
        } catch (e) {
          Logger.log(`Error fixing assignment ${row[1]}: ${e.message}`);
          errorCount++;
        }
      }
    }
    
    Logger.log(`Cleanup completed. Fixed: ${fixedCount}, Errors: ${errorCount}`);
    return {
      status: "success",
      message: `Cleanup completed. Fixed ${fixedCount} assignments with large image data. Errors: ${errorCount}`,
      fixed: fixedCount,
      errors: errorCount
    };
    
  } catch (e) {
    Logger.log('Error in fixExistingImageData: ' + e.message);
    return {
      status: "error",
      message: "Failed to fix existing image data: " + e.message
    };
  }
}

/**
 * Helper function to get or create a folder.
 * @param {Folder} parentFolder - Parent folder.
 * @param {string} folderName - Name of the folder to get or create.
 * @return {Folder} - The folder object.
 */
function getOrCreateFolder(parentFolder, folderName) {
  try {
    const folders = parentFolder.getFoldersByName(folderName);
    if (folders.hasNext()) return folders.next();
    return parentFolder.createFolder(folderName);
  } catch (e) {
    Logger.log('Error in getOrCreateFolder: ' + e.message);
    throw new Error('Failed to access or create folder: ' + e.message);
  }
}

/**
 * Validates date format (more flexible).
 * @param {string} dateStr - Date string to validate.
 * @return {boolean} - True if valid, false otherwise.
 */
function isValidDate(dateStr) {
  if (!dateStr) return false;
  const date = new Date(dateStr);
  const isValid = date instanceof Date && !isNaN(date);
  Logger.log('Validating date ' + dateStr + ': ' + isValid);
  return isValid;
}

/**
 * Converts Google Drive URL to a direct viewable format or returns base64 data URL as-is
 * @param {string} imageUrl - Original image URL or base64 data URL
 * @return {string} - Direct viewable URL or base64 data URL
 */
function convertToViewableImageUrl(imageUrl) {
  if (!imageUrl) return "";
  
  // If it's a base64 data URL, return as-is (most reliable)
  if (imageUrl.startsWith('data:image/')) {
    return imageUrl;
  }
  
  // If it's already in the correct thumbnail format, return as is
  if (imageUrl.includes('drive.google.com/thumbnail?id=')) {
    return imageUrl;
  }
  
  // If it's in the uc export format, convert to thumbnail
  if (imageUrl.includes('drive.google.com/uc?export=view&id=')) {
    const fileId = imageUrl.match(/id=([a-zA-Z0-9_-]+)/);
    if (fileId && fileId[1]) {
      return `https://drive.google.com/thumbnail?id=${fileId[1]}&sz=w400`;
    }
  }
  
  // Extract file ID from various Google Drive URL formats
  let fileId = "";
  
  // Format: https://drive.google.com/file/d/FILE_ID/view
  const viewMatch = imageUrl.match(/\/file\/d\/([a-zA-Z0-9_-]+)\/view/);
  if (viewMatch) {
    fileId = viewMatch[1];
  }
  
  // Format: https://drive.google.com/open?id=FILE_ID
  const openMatch = imageUrl.match(/[?&]id=([a-zA-Z0-9_-]+)/);
  if (openMatch) {
    fileId = openMatch[1];
  }
  
  // Format: https://docs.google.com/spreadsheets/d/FILE_ID/edit
  const docsMatch = imageUrl.match(/\/d\/([a-zA-Z0-9_-]+)/);
  if (docsMatch) {
    fileId = docsMatch[1];
  }
  
  if (fileId) {
    return `https://drive.google.com/thumbnail?id=${fileId}&sz=w400`;
  }
  
  // If we can't parse it, return the original URL
  return imageUrl;
}