Code.gs

function doGet(e) {
  // 1. Default to Dashboard
  if (!e.parameter.page && !e.parameter.action) {
    return HtmlService.createHtmlOutputFromFile('Dashboard').setTitle('Admin Dashboard');
  }

  // 2. Submit Page
  if (e.parameter.page === 'submit') {
    let template = HtmlService.createTemplateFromFile('Index');
    template.docTypes = getDocTypes(); 
    template.directory = getDirectory(); 
    return template.evaluate().setTitle('HRMDD-ComBen Document Submission');
  } 
  
  // 3. Update/Manage Page
  else if (e.parameter.action === 'update') {
    const transactionId = e.parameter.id;
    const transactionDetails = getTransactionDetails(transactionId);
    
    let template = HtmlService.createTemplateFromFile('Update');
    template.transactionId = transactionId;
    template.details = transactionDetails; 
    
    // CRITICAL FIX: Pass directory data to the Update page for Autofill to work
    template.directory = getDirectory(); 
    
    return template.evaluate().setTitle('Update Transaction - ' + transactionId);
  } 
  
  // 4. Print Slip
  else if (e.parameter.action === 'print-slip') {
    const transactionId = e.parameter.id;
    const transactionDetails = getTransactionDetails(transactionId);
    
    let template = HtmlService.createTemplateFromFile('PrintSlip');
    template.transactionId = transactionId;
    template.details = transactionDetails;
    template.appUrl = ScriptApp.getService().getUrl();
    
    return template.evaluate().setTitle('Print Slip - ' + transactionId);
  }
  
  return HtmlService.createHtmlOutputFromFile('Dashboard');
}

// SAFE URL GETTER: Prevents crashes if getUrl fails
function getWebAppUrlSafe() {
  try {
    return ScriptApp.getService().getUrl();
  } catch (e) {
    return ""; // Return empty string instead of crashing
  }
}

function getDocTypes() {
  try {
    const docTypesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DocTypes');
    if (!docTypesSheet || docTypesSheet.getLastRow() < 2) return [];
    const types = docTypesSheet.getRange('A2:A' + docTypesSheet.getLastRow()).getValues();
    return types.map(function(row) { return row[0]; }).filter(function(type) { return type; }); 
  } catch (e) {
    console.error("Error fetching DocTypes: " + e.toString());
    return [];
  }
}

function generateTransactionID() {
  const lock = LockService.getScriptLock();
  lock.waitLock(15000); 

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const transactionsSheet = ss.getSheetByName('Transactions');
    if (!transactionsSheet) { throw new Error("CRITICAL ERROR: The sheet named 'Transactions' could not be found."); }

    const today = new Date();
    const currentMonthYear = today.getFullYear() + '-' + ('0' + (today.getMonth() + 1)).slice(-2);
    
    const lastRow = transactionsSheet.getLastRow();
    let maxSerial = 0;
    
    if (lastRow > 1) { 
      // Assuming TransactionID is in Column A (Index 0)
      const allTransactionIDs = transactionsSheet.getRange('A2:A' + lastRow).getValues().flat();
      for (let i = 0; i < allTransactionIDs.length; i++) {
        let id = String(allTransactionIDs[i]);
        if (id && id.startsWith(currentMonthYear)) {
          let serialPart = parseInt(id.substring(8), 10);
          if (serialPart > maxSerial) {
            maxSerial = serialPart;
          }
        }
      }
    }
    
    const newSerial = maxSerial + 1;
    const serialFormatted = ('000' + newSerial).slice(-3);
    return currentMonthYear + '-' + serialFormatted;

  } finally {
    lock.releaseLock();
  }
}

function processForm(formObject) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const transactionsSheet = ss.getSheetByName('Transactions');
  const documentsSheet = ss.getSheetByName('Documents');
  let leaveLedgerSheet = ss.getSheetByName('LeaveLedger');

  if (!transactionsSheet || !documentsSheet) {
    throw new Error("CRITICAL ERROR: Sheets not found.");
  }
  
  if (!leaveLedgerSheet) {
    leaveLedgerSheet = ss.insertSheet('LeaveLedger');
    leaveLedgerSheet.appendRow(['TransactionID', 'EmployeeName', 'LeaveType', 'StartDate', 'EndDate', 'TotalDays', 'InclusiveDates', 'Timestamp']);
  }
  
  const newTransactionID = generateTransactionID();
  const timestamp = new Date();
  const txDetails = formObject.transactionDetails;
  
  transactionsSheet.appendRow([
    newTransactionID, 
    txDetails.contactPerson, 
    txDetails.centerDept, 
    txDetails.officeUnit, 
    txDetails.email, 
    timestamp
  ]);
  
  const initialStatus = "Pending Signature";
  const documentsForEmail = []; 
  const documentsWithOwner = [];

  formObject.documents.forEach(function(doc) {
    const principalName = doc.principalName || "";
    const principalEmail = doc.principalEmail || "";
    
    let inclusiveDatesString = ""; 
    let emailLeaveDetails = []; 

    if (doc.docType === 'Application for Leave (ALA)' && doc.alaData) {
        try {
            const leaves = JSON.parse(doc.alaData);
            const dateSummaries = [];

            leaves.forEach(leave => {
                let startDate = "";
                let endDate = "";
                
                if (leave.dates.includes(' to ')) {
                    const parts = leave.dates.split(' to ');
                    startDate = parts[0];
                    endDate = parts[1];
                } else if (leave.dates.includes(', ')) {
                    const parts = leave.dates.split(', ');
                    startDate = parts[0];
                    endDate = parts[parts.length-1];
                } else {
                    startDate = leave.dates;
                    endDate = leave.dates;
                }

                leaveLedgerSheet.appendRow([
                    newTransactionID,
                    principalName,
                    leave.type,
                    startDate,
                    endDate,
                    leave.days,
                    leave.dates,
                    timestamp
                ]);

                dateSummaries.push(`${leave.type}: ${leave.dates} (${leave.days})`);
                
                emailLeaveDetails.push({
                    type: leave.type,
                    dates: leave.dates,
                    days: leave.days
                });
            });

            inclusiveDatesString = dateSummaries.join(";\n"); 

        } catch (e) {
            console.error("Error parsing ALA JSON: " + e.message);
            inclusiveDatesString = "Error parsing dates";
        }
    } else {
        inclusiveDatesString = doc.inclusiveDates || "";
    }

    documentsSheet.appendRow([
      newTransactionID,
      doc.docType,
      doc.docTitle,
      inclusiveDatesString, 
      initialStatus,
      timestamp, 
      "",  
      "",  
      principalName, 
      principalEmail 
    ]);

    const docData = {
      title: doc.docTitle,
      type: doc.docType,
      datesSummary: inclusiveDatesString, 
      leaveDetails: emailLeaveDetails, 
      owner: principalName || "(Liaison)", 
      principalEmail: principalEmail
    };

    documentsForEmail.push(docData);
    documentsWithOwner.push(docData);
  });

  try {
    sendLiaisonReceiptEmail(txDetails, newTransactionID, documentsForEmail);

    const ownerGroups = {};
    documentsWithOwner.forEach(doc => {
        if (doc.principalEmail && doc.principalEmail !== txDetails.email) {
            if (!ownerGroups[doc.principalEmail]) {
                ownerGroups[doc.principalEmail] = { name: doc.owner, docs: [] };
            }
            ownerGroups[doc.principalEmail].docs.push(doc);
        }
    });

    Object.keys(ownerGroups).forEach(email => {
        const group = ownerGroups[email];
        sendOwnerReceiptEmail(group.name, email, newTransactionID, group.docs, txDetails.contactPerson);
    });
    // HRIS Email removed from here
  } catch(e) {
    console.error("Email failed: " + e.toString());
  }

  return { 
    transactionId: newTransactionID, 
    appUrl: getWebAppUrlSafe(),
    details: {
      ContactPerson: txDetails.contactPerson,
      Timestamp: timestamp.toLocaleString('en-US', { timeZone: 'Asia/Manila' }),
      documents: documentsForEmail
    }
  };
}

// Helper: HR Summary Email (Branded & Optimized for Encoding)
function sendHROfficerSummary(leaveList, transactionId, timestamp) {
  const subject = `[HRMDD] New Leave Application(s) Filed - Ref: ${transactionId}`;
  
  // Build rows with prominent data points
  const rows = leaveList.map(l => 
    `<tr>
       <td style="padding: 12px; border-bottom: 1px solid #eee; font-size: 14px; color: #333;">
         <strong>${l.name}</strong>
       </td>
       <td style="padding: 12px; border-bottom: 1px solid #eee; font-size: 14px; color: #555;">
         ${l.type}
       </td>
       <td style="padding: 12px; border-bottom: 1px solid #eee; text-align: center; font-size: 16px; font-weight: bold; color: #1C2790; background-color: #fcfcfc;">
         ${l.days}
       </td>
       <td style="padding: 12px; border-bottom: 1px solid #eee; font-size: 13px; color: #555; font-family: monospace;">
         ${l.dates}
       </td>
     </tr>`
  ).join('');

  const html = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 750px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        
        <div style="background-color: #1C2790; padding: 25px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 280px; display: block; margin: 0 auto;">
        </div>

        <div style="padding: 40px; border-top: 6px solid #CDAE2C;">
          
          <div style="display: flex; justify-content: space-between; align-items: flex-end; margin-bottom: 30px; border-bottom: 2px solid #f0f0f0; padding-bottom: 20px;">
            <div>
              <h1 style="color: #1C2790; margin: 0; font-size: 22px; text-transform: uppercase; letter-spacing: 0.5px;">For HRIS Encoding</h1>
              <p style="margin: 5px 0 0 0; font-size: 14px; color: #666;">New Leave Applications Received</p>
            </div>
            <div style="text-align: right;">
              <span style="font-size: 11px; color: #999; text-transform: uppercase;">Transaction Ref</span><br>
              <span style="font-size: 18px; font-weight: bold; color: #333;">${transactionId}</span>
            </div>
          </div>

          <table style="width: 100%; border-collapse: collapse; margin-bottom: 30px;">
            <thead style="background-color: #f8f9fa;">
              <tr>
                <th style="padding: 12px; text-align: left; color: #666; font-size: 12px; text-transform: uppercase; border-bottom: 2px solid #ddd; width: 30%;">Employee Name</th>
                <th style="padding: 12px; text-align: left; color: #666; font-size: 12px; text-transform: uppercase; border-bottom: 2px solid #ddd; width: 25%;">Leave Type</th>
                <th style="padding: 12px; text-align: center; color: #666; font-size: 12px; text-transform: uppercase; border-bottom: 2px solid #ddd; width: 15%;">Day[s]</th>
                <th style="padding: 12px; text-align: left; color: #666; font-size: 12px; text-transform: uppercase; border-bottom: 2px solid #ddd; width: 30%;">Inclusive Dates</th>
              </tr>
            </thead>
            <tbody>${rows}</tbody>
          </table>

          <div style="padding: 15px; background-color: #fff9db; border-left: 4px solid #CDAE2C; border-radius: 4px; font-size: 13px; color: #856404;">
            <strong>Encoder's Note:</strong> Please verify leave credit balances in the HRIS before posting. Date filed: ${timestamp.toLocaleString('en-US', { timeZone: 'Asia/Manila' })}.
          </div>

        </div>

        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p style="margin: 0;">&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
          <p style="margin: 5px 0 0 0;">Development Academy of the Philippines</p>
        </div>
      </div>
    </div>`;

  MailApp.sendEmail({ 
    to: 'sabugoj@dap.edu.ph', 
    subject: subject, 
    htmlBody: html, 
    name: 'HRMDD-ComBen System' 
  });
}

function sendLiaisonReceiptEmail(txDetails, transactionId, documents) {
  const subject = `[DMS] Transaction Logged - Ref: ${transactionId}`;
  
  let documentsHtmlList = documents.map(doc => {
    let titleDisplay = doc.title;
    
    // LOGIC CHANGE: Handle Multi-Leave ALA
    // If we have detailed leave info (Array from processForm)
    if (doc.type === 'Application for Leave (ALA)' && doc.leaveDetails && doc.leaveDetails.length > 0) {
        let listItems = doc.leaveDetails.map(l => 
            `<div style="margin-bottom:4px;">
               <strong>${l.type}</strong>: <span style="color:#555;">${l.dates}</span> 
               <span style="background:#eee;padding:1px 4px;border-radius:3px;font-size:11px;">${l.days} day[s]</span>
             </div>`
        ).join('');
        titleDisplay = `<div style="font-size:12px;">${listItems}</div>`;
    } 
    // Fallback logic from previous versions (Composite String)
    else if (doc.datesSummary && doc.datesSummary !== "") {
        // Convert newlines to <br> for HTML display
        let formattedSummary = doc.datesSummary.replace(/\n/g, '<br>');
        titleDisplay += `<br><span style="font-size:12px; color:#666;">${formattedSummary}</span>`;
    }

    return `<tr>
       <td style="padding: 12px; border-bottom: 1px solid #eee;"><b>${doc.owner}</b></td>
       <td style="padding: 12px; border-bottom: 1px solid #eee;">${titleDisplay}</td>
       <td style="padding: 12px; border-bottom: 1px solid #eee;">${doc.type}</td>
     </tr>`;
  }).join('');

  const emailBody = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>
        <div style="padding: 40px; border-top: 6px solid #CDAE2C;">
          <h1 style="color: #1C2790; margin: 0 0 20px 0; text-align: center; font-size: 24px; text-transform: uppercase;">Transaction Logged</h1>
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${transactionId}</span>
          </div>
          <p style="font-size: 16px; color: #333;">Dear <b>${txDetails.contactPerson}</b>,</p>
          <p style="font-size: 16px; color: #333;">We have received the documents you submitted. Here is the summary:</p>
          <table style="width: 100%; border-collapse: collapse; font-size: 14px; margin-top: 20px;">
            <thead style="background-color: #f8f9fa;">
              <tr>
                <th style="padding: 12px; text-align: left;">Owner</th>
                <th style="padding: 12px; text-align: left;">Details</th>
                <th style="padding: 12px; text-align: left;">Type</th>
              </tr>
            </thead>
            <tbody>${documentsHtmlList}</tbody>
          </table>
        </div>
        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p>&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
        </div>
      </div>
    </div>`;
    
  MailApp.sendEmail({ to: txDetails.email, subject: subject, htmlBody: emailBody, name: 'DAP HRMDD-ComBen' });
}


function sendOwnerReceiptEmail(ownerName, ownerEmail, transactionId, documents, liaisonName) {
  const subject = `[DMS] Document Received - Ref: ${transactionId}`;
  
  let documentsHtmlList = documents.map(doc => {
    let titleDisplay = doc.title;
    
    // LOGIC CHANGE: Handle Multi-Leave ALA
    if (doc.type === 'Application for Leave (ALA)' && doc.leaveDetails && doc.leaveDetails.length > 0) {
        let listItems = doc.leaveDetails.map(l => 
            `<div style="margin-bottom:4px; border-bottom:1px dashed #eee; padding-bottom:2px;">
               <div style="font-weight:bold; color:#1C2790;">${l.type}</div>
               <div style="font-size:12px; color:#555;">${l.dates} (${l.days} day[s])</div>
             </div>`
        ).join('');
        titleDisplay = listItems;
    } 
    else if (doc.datesSummary && doc.datesSummary !== "") {
        let formattedSummary = doc.datesSummary.replace(/\n/g, '<br>');
        titleDisplay += `<br><span style="font-size:12px; color:#666;">${formattedSummary}</span>`;
    }

    return `<tr>
       <td style="padding: 12px; border-bottom: 1px solid #eee;">${titleDisplay}</td>
       <td style="padding: 12px; border-bottom: 1px solid #eee;">${doc.type}</td>
     </tr>`;
  }).join('');

  const emailBody = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>
        <div style="padding: 40px; border-top: 6px solid #CDAE2C;">
          <h1 style="color: #1C2790; margin: 0 0 20px 0; text-align: center; font-size: 24px; text-transform: uppercase;">Document Received</h1>
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${transactionId}</span>
          </div>
          <p style="font-size: 16px; color: #333;">Dear <b>${ownerName}</b>,</p>
          <p style="font-size: 16px; color: #333;">This email confirms that HRMDD-ComBen has received the following document(s) submitted on your behalf by <b>${liaisonName}</b>:</p>
          <table style="width: 100%; border-collapse: collapse; font-size: 14px; margin-top: 20px;">
            <thead style="background-color: #f8f9fa;">
              <tr>
                <th style="padding: 12px; text-align: left;">Details</th>
                <th style="padding: 12px; text-align: left;">Type</th>
              </tr>
            </thead>
            <tbody>${documentsHtmlList}</tbody>
          </table>
          <p style="margin-top: 30px; font-size: 14px; color: #666; font-style: italic;">You will receive another notification once your document is processed.</p>
        </div>
        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p>&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
        </div>
      </div>
    </div>`;
    
  MailApp.sendEmail({ to: ownerEmail, subject: subject, htmlBody: emailBody, name: 'DAP HRMDD-ComBen' });
}

function getDashboardData() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const transactionsSheet = ss.getSheetByName('Transactions');
    const documentsSheet = ss.getSheetByName('Documents');
    const leaveSheet = ss.getSheetByName('LeaveLedger');

    if (!transactionsSheet || !documentsSheet) { 
      return { transactions: [], docTypes: [], leaveData: [], appUrl: getWebAppUrlSafe() }; 
    }

    const docsByTxId = {};
    const statusMap = {}; 
    
    if (documentsSheet.getLastRow() > 1) {
      const docRange = documentsSheet.getRange(2, 1, documentsSheet.getLastRow() - 1, documentsSheet.getLastColumn());
      const docValues = docRange.getValues();
      const docHeaders = documentsSheet.getRange(1, 1, 1, documentsSheet.getLastColumn()).getValues()[0];
      
      docValues.forEach(function(row, index) {
        if (!row[0]) return; 
        let docObject = { rowNumber: index + 2 }; 
        docHeaders.forEach(function(header, i) { 
          if(header) {
            const cleanHeader = header.trim(); 
            if (row[i] instanceof Date) {
               docObject[cleanHeader] = row[i].toLocaleString('en-US', { timeZone: 'Asia/Manila' });
            } else {
               docObject[cleanHeader] = String(row[i]); 
            }
          }
        });
        
        if (docObject.TransactionID) {
            if (docObject.DocumentType === 'Application for Leave (ALA)') {
                statusMap[docObject.TransactionID] = docObject.Status;
            }
            if (!docsByTxId[docObject.TransactionID]) { docsByTxId[docObject.TransactionID] = []; }
            docsByTxId[docObject.TransactionID].push(docObject);
        }
      });
    }

    const allTransactions = [];
    const docTypes = new Set();
    
    if (transactionsSheet.getLastRow() > 1) {
      const txRange = transactionsSheet.getRange(2, 1, transactionsSheet.getLastRow() - 1, transactionsSheet.getLastColumn());
      const txValues = txRange.getValues();
      const txHeaders = transactionsSheet.getRange(1, 1, 1, transactionsSheet.getLastColumn()).getValues()[0];

      txValues.forEach(function(row) {
        if (!row[0]) return;
        let txObject = {};
        txHeaders.forEach(function(header, i) {
          if(!header) return;
          const cleanHeader = header.trim();
          if (row[i] instanceof Date) {
            txObject[cleanHeader] = row[i].toLocaleString('en-US', { timeZone: 'Asia/Manila' });
          } else {
            txObject[cleanHeader] = String(row[i]);
          }
        });

        if (txObject.TransactionID) {
          const relatedDocs = docsByTxId[txObject.TransactionID] || [];
          txObject.documents = relatedDocs;
          relatedDocs.forEach(function(d) { if(d.DocumentType) docTypes.add(d.DocumentType); });
          allTransactions.push(txObject);
        }
      });
    }

    // --- LEAVE DATA & COMMUTABLE SUMMARY ---
    const allLeaveData = [];
    const commutableSummary = {}; // { "Name": { VL: 5, SL: 2 } }
    const currentYear = new Date().getFullYear();

    if (leaveSheet && leaveSheet.getLastRow() > 1) {
        const lRange = leaveSheet.getRange(2, 1, leaveSheet.getLastRow() - 1, leaveSheet.getLastColumn());
        const lValues = lRange.getValues();
        const lHeaders = leaveSheet.getRange(1, 1, 1, leaveSheet.getLastColumn()).getValues()[0];

        lValues.forEach(function(row) {
            if (!row[0]) return;
            let leaveObj = {};
            let timestampDate = null;

            lHeaders.forEach(function(header, i) {
                if (header) {
                    const cleanHeader = header.trim();
                    if (row[i] instanceof Date) {
                        leaveObj[cleanHeader] = row[i].toLocaleString('en-US', { timeZone: 'Asia/Manila' });
                        leaveObj[cleanHeader + "_ISO"] = row[i].toISOString(); 
                        if (cleanHeader === 'Timestamp') timestampDate = row[i];
                    } else {
                        leaveObj[cleanHeader] = String(row[i]);
                    }
                }
            });

            const currentStatus = statusMap[leaveObj.TransactionID] || "Pending Signature"; 
            leaveObj.CurrentStatus = currentStatus;
            allLeaveData.push(leaveObj);

            // Calculate Commutable Summary (Active Only, Current Year)
            if (currentStatus !== 'Pulled Out' && timestampDate && timestampDate.getFullYear() === currentYear) {
                const name = leaveObj.EmployeeName || "Unknown";
                const type = leaveObj.LeaveType;
                const days = parseInt(leaveObj.TotalDays) || 0;

                if (!commutableSummary[name]) commutableSummary[name] = { VL: 0, SL: 0 };
                
                if (type === 'Vacation Leave') commutableSummary[name].VL += days;
                if (type === 'Sick Leave') commutableSummary[name].SL += days;
            }
        });
    }
    
    return {
      transactions: allTransactions.reverse(),
      docTypes: Array.from(docTypes),
      leaveData: allLeaveData.reverse(),
      commutableSummary: commutableSummary, // Return the pre-calculated summary
      appUrl: getWebAppUrlSafe()
    };

  } catch (e) {
    return { transactions: [], docTypes: [], leaveData: [], appUrl: getWebAppUrlSafe() };
  }
}

function getTransactionDetails(transactionId) {
  try {
    if (!transactionId) return null;
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const transactionsSheet = ss.getSheetByName('Transactions');
    const documentsSheet = ss.getSheetByName('Documents');
    
    let transactionInfo = null;
    
    // Fetch Header
    if (transactionsSheet.getLastRow() > 1) {
      const txData = transactionsSheet.getDataRange().getValues();
      const txHeaders = txData.shift();
      
      for (let i = 0; i < txData.length; i++) {
        if (String(txData[i][0]) === String(transactionId)) { 
          transactionInfo = {};
          txHeaders.forEach(function(header, idx) { 
            if(header) {
                const cleanHeader = header.trim();
                const cellValue = txData[i][idx];
                // Added Safety: Convert Dates to Strings like in Dashboard
                if (cellValue instanceof Date) {
                    transactionInfo[cleanHeader] = cellValue.toLocaleString('en-US', { timeZone: 'Asia/Manila' });
                } else {
                    transactionInfo[cleanHeader] = String(cellValue);
                }
            }
          });
          break;
        }
      }
    }
    
    if (!transactionInfo) return null;

    // Fetch Related Documents
    const relatedDocuments = [];
    if (documentsSheet.getLastRow() > 1) {
      const docData = documentsSheet.getDataRange().getValues();
      const docHeaders = docData.shift();
      
      for (let i = 0; i < docData.length; i++) {
        if (String(docData[i][0]) === String(transactionId)) { 
          let docObject = {};
          docHeaders.forEach(function(header, idx) { 
            if(header) {
                const cleanHeader = header.trim(); // This captures 'PrincipalName'
                const cellValue = docData[i][idx];
                
                // Added Safety: Convert Dates to Strings
                if (cellValue instanceof Date) {
                   docObject[cleanHeader] = cellValue.toLocaleString('en-US', { timeZone: 'Asia/Manila' });
                } else {
                   docObject[cleanHeader] = String(cellValue);
                }
            }
          });
          docObject.rowNumber = i + 2; 
          relatedDocuments.push(docObject);
        }
      }
    }
    
    transactionInfo.documents = relatedDocuments;
    return transactionInfo;
  } catch (e) {
    console.error("Error in getTransactionDetails: " + e.toString());
    return null;
  }
}

function updateDocumentStatus(updateData) {
  const documentsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Documents');
  const lastRow = documentsSheet.getLastRow();
  const affectedDocs = []; 
  
  // A. BATCH UPDATE
  if (updateData.isBatch) {
    if (lastRow < 2) return "No documents to update.";
    const data = documentsSheet.getRange(2, 1, lastRow - 1, 1).getValues();
    
    for (let i = 0; i < data.length; i++) {
      if (String(data[i][0]) === String(updateData.transactionId)) {
        const currentRow = i + 2;
        const currentStatus = documentsSheet.getRange(currentRow, 5).getValue();
        
        if (currentStatus !== 'Claimed/Released' && currentStatus !== 'Pulled Out') {
           documentsSheet.getRange(currentRow, 5).setValue(updateData.newStatus); 
           documentsSheet.getRange(currentRow, 6).setValue(new Date()); 
           if (updateData.notes) documentsSheet.getRange(currentRow, 8).setValue(updateData.notes);
           
           const pName = documentsSheet.getRange(currentRow, 9).getValue();
           const pEmail = documentsSheet.getRange(currentRow, 10).getValue();
           const datesSummary = documentsSheet.getRange(currentRow, 4).getValue(); // Col D

           affectedDocs.push({
             Title: documentsSheet.getRange(currentRow, 3).getValue(),
             Type: documentsSheet.getRange(currentRow, 2).getValue(),
             Status: updateData.newStatus,
             PrincipalName: pName,
             PrincipalEmail: pEmail,
             DatesSummary: datesSummary 
           });
        }
      }
    }
  } 
  // B. INDIVIDUAL UPDATE
  else {
    try {
      const row = parseInt(updateData.documentRow);
      documentsSheet.getRange(row, 5).setValue(updateData.newStatus); 
      documentsSheet.getRange(row, 6).setValue(new Date()); 
      if (updateData.notes) documentsSheet.getRange(row, 8).setValue(updateData.notes); 
      
      const pName = documentsSheet.getRange(row, 9).getValue();
      const pEmail = documentsSheet.getRange(row, 10).getValue();
      const datesSummary = documentsSheet.getRange(row, 4).getValue();

      affectedDocs.push({
         Title: documentsSheet.getRange(row, 3).getValue(),
         Type: documentsSheet.getRange(row, 2).getValue(),
         Status: updateData.newStatus,
         PrincipalName: pName,
         PrincipalEmail: pEmail,
         DatesSummary: datesSummary
      });
    } catch (e) { return "Error: " + e.message; }
  }

  // C. SEND EMAILS (If updates occurred)
  if (affectedDocs.length > 0) {
     const txDetails = getTransactionDetails(updateData.transactionId);
     const leavesForHR = []; // Store leaves for HRIS email

     // 1. Prepare HRIS Data if Signed
     if (updateData.newStatus === 'Signed') {
        affectedDocs.forEach(doc => {
            if (doc.Type === 'Application for Leave (ALA)' && doc.DatesSummary) {
                // Parse the composite string: "VL: Date (1); SL: Date (2)"
                const entries = doc.DatesSummary.split(';');
                entries.forEach(entry => {
                    // entry example: "Vacation Leave: 2025-01-01 (1)"
                    // Simple parsing logic
                    const parts = entry.trim().split(':');
                    if (parts.length > 1) {
                        const lType = parts[0].trim();
                        const rest = parts[1].trim(); // "2025-01-01 (1)"
                        const daysMatch = rest.match(/\((\d+)\)$/);
                        const days = daysMatch ? daysMatch[1] : "1";
                        const dateStr = rest.replace(/\s*\(\d+\)$/, "");
                        
                        leavesForHR.push({
                            name: doc.PrincipalName,
                            type: lType,
                            days: days,
                            dates: dateStr
                        });
                    }
                });
            }
        });
     }

     // 2. Send Principals Emails
     const principalGroups = {};
     affectedDocs.forEach(doc => {
       if (doc.PrincipalEmail && doc.PrincipalEmail.trim() !== "") {
         if (!principalGroups[doc.PrincipalEmail]) {
           principalGroups[doc.PrincipalEmail] = { Name: doc.PrincipalName, Docs: [] };
         }
         principalGroups[doc.PrincipalEmail].Docs.push(doc);
       }
     });

     Object.keys(principalGroups).forEach(email => {
       const group = principalGroups[email];
       const principalTxDetails = {
         TransactionID: txDetails.TransactionID,
         ContactPerson: group.Name || "Document Owner",
         ContactEmail: email
       };

       if (updateData.newStatus === 'Signed') {
          sendReadyForPickupEmail(principalTxDetails, group.Docs, updateData.notes, false); 
       } else if (updateData.newStatus === 'For pick up, but with comments') {
          sendPickupWithCommentsEmail(principalTxDetails, group.Docs, updateData.notes, false);
       }
     });

     // 3. Send Liaison Summary
     if (updateData.newStatus === 'Signed') {
        sendReadyForPickupEmail(txDetails, affectedDocs, updateData.notes, true);
     } else if (updateData.newStatus === 'For pick up, but with comments') {
        sendPickupWithCommentsEmail(txDetails, affectedDocs, updateData.notes, true);
     }
     
     // 4. Send HRIS Summary (NEW TRIGGER)
     if (leavesForHR.length > 0) {
        sendHROfficerSummary(leavesForHR, updateData.transactionId, new Date());
     }
     
     return `Success: Updated ${affectedDocs.length} document(s).`;
  }
  
  return "No documents were updated.";
}

function sendReadyForPickupEmail(txDetails, docsList, notes, isBatch) {
  const subject = `[DMS] Ready for Pickup - Ref: ${txDetails.TransactionID}`;
  
  const rows = docsList.map(d => `<tr><td style="padding:12px;border-bottom:1px solid #eee;">${d.Title}</td><td style="padding:12px;border-bottom:1px solid #eee;color:#555;">${d.Type}</td></tr>`).join('');
  
  let remarksHtml = '';
  if (notes) {
    remarksHtml = `
      <div style="margin-top: 25px; padding: 20px; background-color: #f0fcf4; border-left: 5px solid #198754; border-radius: 4px;">
        <h4 style="margin:0 0 5px 0; color: #198754; font-size: 14px; text-transform: uppercase;">Remarks</h4>
        <p style="margin:0; font-size: 15px; color: #333;">${notes}</p>
      </div>`;
  }

  const docContext = isBatch 
    ? `All pending documents in this transaction have been signed.`
    : `The document listed below has been signed.`;

  const html = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>

        <div style="padding: 40px; border-top: 6px solid #198754;">
          <h1 style="color: #198754; margin: 0 0 20px 0; text-align: center; font-size: 26px; text-transform: uppercase; letter-spacing: 1px;">Ready for Pickup</h1>
          
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px; letter-spacing: 1px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${txDetails.TransactionID}</span>
          </div>

          <p style="font-size: 16px; color: #333; margin-bottom: 20px;">Dear <b>${txDetails.ContactPerson}</b>,</p>
          <p style="font-size: 16px; color: #333; line-height: 1.6;">Good news! ${docContext}</p>

          <table style="width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px;">
            <thead style="background-color: #198754; color: white;">
              <tr>
                <th style="padding: 12px; text-align: left;">Document Title</th>
                <th style="padding: 12px; text-align: left;">Type</th>
              </tr>
            </thead>
            <tbody>${rows}</tbody>
          </table>

          ${remarksHtml}

          <div style="margin-top: 30px; padding-top: 20px; border-top: 1px solid #eee; text-align: center;">
            <p style="font-size: 15px; font-weight: bold; color: #555;">You may now proceed to the HRMDD-ComBen office or have someone claim the document(s) on your behalf.</p>
          </div>
        </div>

        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p style="margin: 0;">&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
          <p style="margin: 5px 0 0 0;">All Rights Reserved.</p>
        </div>
      </div>
    </div>`;
  
  MailApp.sendEmail({ to: txDetails.ContactEmail, subject: subject, htmlBody: html, name: 'DAP HRMDD-ComBen' });
}

function sendPickupWithCommentsEmail(txDetails, docsList, notes, isBatch) {
  const subject = `[DMS] Action Required - Ref: ${txDetails.TransactionID}`;
  
  const rows = docsList.map(d => `<tr><td style="padding:12px;border-bottom:1px solid #e0e0e0;">${d.Title}</td><td style="padding:12px;border-bottom:1px solid #e0e0e0;">${d.Type}</td></tr>`).join('');
  
  const html = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>

        <div style="padding: 40px; border-top: 6px solid #ffc107;">
          <h1 style="color: #bfa006; margin: 0 0 20px 0; text-align: center; font-size: 26px; text-transform: uppercase; letter-spacing: 1px;">Action Required</h1>
          
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px; letter-spacing: 1px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${txDetails.TransactionID}</span>
          </div>

          <p style="font-size: 16px; color: #333;">Dear <b>${txDetails.ContactPerson}</b>,</p>
          <p style="font-size: 16px; color: #333; line-height: 1.6;">The following documents have been reviewed but require your attention:</p>

          <table style="width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px;">
            <thead style="background-color: #fff3cd; color: #856404;">
              <tr>
                <th style="padding: 12px; text-align: left;">Document Title</th>
                <th style="padding: 12px; text-align: left;">Type</th>
              </tr>
            </thead>
            <tbody>${rows}</tbody>
          </table>

          <div style="margin-top: 25px; padding: 20px; background-color: #fff9db; border: 1px solid #ffeeba; border-radius: 4px; color: #856404;">
            <h4 style="margin:0 0 10px 0; font-size: 14px; text-transform: uppercase;">Requirements / Comments:</h4>
            <p style="margin:0; font-size: 15px; font-weight: 500;">${notes}</p>
          </div>

          <p style="margin-top: 30px; text-align: center; font-weight: bold; color: #555;">Please visit the office to address these items.</p>
        </div>

        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p style="margin: 0;">&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
          <p style="margin: 5px 0 0 0;">All Rights Reserved.</p>
        </div>
      </div>
    </div>`;
  
  MailApp.sendEmail({ to: txDetails.ContactEmail, subject: subject, htmlBody: html, name: 'DAP HRMDD-ComBen' });
}



function sendClaimedEmail(txDetails, docsList, claimantName, timestamp, isThirdParty) {
  const subject = `[DMS] Document Claimed - Ref: ${txDetails.TransactionID}`;
  const formattedTimestamp = timestamp.toLocaleString('en-US', { timeZone: 'Asia/Manila' });

  const rows = docsList.map(d => {
      // Logic for Claimed Email: The data comes from the sheet (via processClaim or getTransactionDetails).
      // We don't have the granular 'leaveDetails' array here because that was only in processForm scope.
      // However, we saved the composite string into the sheet column "InclusiveDates" (or Title, depending on implementation).
      // If the Title contains newlines (from processForm logic), we need to replace them with <br> for HTML display.
      
      let displayTitle = d.Title;
      // Safety check if Title has newlines from the multi-leave summary
      if (displayTitle && typeof displayTitle === 'string') {
          displayTitle = displayTitle.replace(/\n/g, '<br>');
      }

      return `<tr>
        <td style="padding:12px;border-bottom:1px solid #eee;">${displayTitle}</td>
        <td style="padding:12px;border-bottom:1px solid #eee;color:#555;">${d.Type}</td>
      </tr>`;
  }).join('');

  let thirdPartyWarning = "";
  if (isThirdParty) {
      thirdPartyWarning = `
      <div style="margin-top: 20px; padding: 15px; background-color: #fff3cd; border-left: 4px solid #ffc107; color: #856404; font-size: 13px;">
        <strong>Note:</strong> Documents were claimed by an authorized representative: <strong>${claimantName}</strong>.
      </div>`;
  }

  const html = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>

        <div style="padding: 40px; border-top: 6px solid #1C2790;">
          <h1 style="color: #1C2790; margin: 0 0 20px 0; text-align: center; font-size: 26px; text-transform: uppercase; letter-spacing: 1px;">Transaction Complete</h1>
          
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px; letter-spacing: 1px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${txDetails.TransactionID}</span>
          </div>
          
          <p style="font-size: 16px; color: #333;">Dear <b>${txDetails.ContactPerson}</b>,</p>
          <p style="font-size: 16px; color: #333; line-height: 1.6;">This email serves as an official confirmation that the following documents were successfully claimed:</p>

          <table style="width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px;">
            <thead style="background-color: #f2f4f8;">
              <tr>
                <th style="padding: 12px; text-align: left; color: #333;">Document Title / Details</th>
                <th style="padding: 12px; text-align: left; color: #333;">Type</th>
              </tr>
            </thead>
            <tbody>${rows}</tbody>
          </table>

          ${thirdPartyWarning}

          <div style="margin-top: 30px; border: 1px solid #eee; padding: 20px; border-radius: 6px; text-align: center; background-color: #fafafa;">
            <p style="font-size: 12px; color: #999; text-transform: uppercase; margin-bottom: 10px;">Claimed By</p>
            <p style="font-size: 18px; font-weight: bold; color: #333; margin: 0;">${claimantName}</p>
            <p style="font-size: 13px; color: #555; margin-top: 5px;"><strong>Date:</strong> ${formattedTimestamp}</p>
          </div>

          <div style="margin-top: 40px; padding: 0; background-color: #ffffff; border: 1px solid #e0e0e0; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 5px rgba(0,0,0,0.02);">
             
             <div style="padding: 30px 30px 25px 30px; text-align: center;">
                 <h3 style="margin: 0 0 10px 0; color: #1C2790; font-size: 20px;">How was your transaction?</h3>
                 <p style="margin: 0 0 25px 0; color: #555; font-size: 15px; line-height: 1.5;">We're always working to make our services faster and more convenient. We would like to hear from you!</p>
                 
                 <a href="https://ee.kobotoolbox.org/single/qWDFihHz?return%20_url=https:dap.edu.ph" target="_blank" style="display: inline-block; padding: 14px 28px; background-color: #CDAE2C; color: #1C2790; text-decoration: none; border-radius: 5px; font-weight: bold; font-size: 15px; letter-spacing: 0.5px; border: 1px solid #B59820; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">Share Your Experience (1-Min Survey)</a>
             </div>

             <div style="background-color: #f8f9fa; padding: 25px; text-align: left; border-top: 1px solid #eee; font-size: 13px; color: #444; line-height: 1.6;">
                 <p style="margin: 0 0 15px 0; color: #1C2790;"><strong>On the survey form, please follow these steps:</strong></p>
                 
                 <div style="margin-bottom: 12px;">
                    <span style="font-weight: bold; color: #333;">1. Select Office:</span> <br>
                    Human Resource Management Division (HRMD) - HRMDD
                 </div>

                 <div style="margin-bottom: 12px;">
                    <span style="font-weight: bold; color: #333;">2. Service Availed:</span> <span style="color:#1C2790;">&rarr;</span> Then choose whether:
                    <ul style="margin: 5px 0 5px 20px; padding: 0; color: #555;">
                        <li>Application for Leave</li>
                        <li>Request for Monetization of Leave Credits</li>
                        <li>Issuance of Certificate of Employment/Engagement Service Record</li>
                    </ul>
                 </div>

                 <div>
                    <span style="font-weight: bold; color: #333;">3. Particulars of the service availed:</span> <br>
                    Then cite Transaction Number: <span style="background-color: #fff3cd; padding: 2px 6px; font-weight: bold; border: 1px solid #ffeeba; color: #1C2790; border-radius: 3px;">${txDetails.TransactionID}</span>
                 </div>
             </div>
          </div>

        </div>

        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p style="margin: 0;">&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
          <p style="margin: 5px 0 0 0;">All Rights Reserved.</p>
        </div>
      </div>
    </div>`;
  
  MailApp.sendEmail({
    to: txDetails.ContactEmail, subject: subject, htmlBody: html, name: 'DAP HRMDD-ComBen'
  });
}

// --- 1. PROCESS CLAIM (Text-Based, No Signature) ---
function processClaim(claimData) {
  try {
    const documentsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Documents');
    const claimTimestamp = new Date();
    const affectedDocs = [];

    const lastRow = documentsSheet.getLastRow();
    // A:TxID(0), B:Type(1), C:Title(2), D:Dates(3), E:Status(4), F:Updated(5), G:ClaimedBy(6), H:Comments(7), I:PName(8), J:PEmail(9)
    const data = documentsSheet.getRange(2, 1, lastRow - 1, 10).getValues();

    for (let i = 0; i < data.length; i++) {
      if (String(data[i][0]) === String(claimData.transactionId)) {
        const row = i + 2;
        const isTarget = claimData.isBatch || (String(row) === String(claimData.documentRow));
        const currentStatus = data[i][4]; // Column E (Status) - FIXED INDEX

        if (isTarget && (currentStatus === 'Signed' || currentStatus === 'For pick up, but with comments')) {
           // Update Sheet
           documentsSheet.getRange(row, 5).setValue('Claimed/Released'); // Col E: Status
           documentsSheet.getRange(row, 6).setValue(claimTimestamp);     // Col F: LastUpdated
           documentsSheet.getRange(row, 7).setValue(claimData.claimedBy); // Col G: ClaimedBy (Text)
           
           const pName = data[i][8]; // Col I: PrincipalName
           const pEmail = data[i][9]; // Col J: PrincipalEmail

           affectedDocs.push({
             Title: data[i][2], 
             Type: data[i][1], 
             Status: 'Claimed/Released',
             PrincipalName: pName,
             PrincipalEmail: pEmail
           });
        }
      }
    }

    if (affectedDocs.length > 0) {
      const txDetails = getTransactionDetails(claimData.transactionId);
      
      const principalGroups = {};
      affectedDocs.forEach(doc => {
        if (doc.PrincipalEmail && doc.PrincipalEmail.trim() !== "") {
          if (!principalGroups[doc.PrincipalEmail]) {
            principalGroups[doc.PrincipalEmail] = { Name: doc.PrincipalName, Docs: [] };
          }
          principalGroups[doc.PrincipalEmail].Docs.push(doc);
        }
      });

      // 1. Notify Owners
      Object.keys(principalGroups).forEach(email => {
        const group = principalGroups[email];
        const claimant = claimData.claimedBy.toLowerCase().trim();
        const owner = (group.Name || "").toLowerCase().trim();
        const liaison = (txDetails.ContactPerson || "").toLowerCase().trim();
        const isThirdParty = (claimant !== owner && claimant !== liaison);

        const principalTxDetails = {
          TransactionID: txDetails.TransactionID,
          ContactPerson: group.Name || "Document Owner",
          ContactEmail: email
        };
        
        sendClaimedEmail(principalTxDetails, group.Docs, claimData.claimedBy, claimTimestamp, isThirdParty);
      });

      // 2. Notify Liaison
      const claimant = claimData.claimedBy.toLowerCase().trim();
      const liaison = (txDetails.ContactPerson || "").toLowerCase().trim();
      const isThirdPartyForLiaison = (claimant !== liaison); 

      sendClaimedEmail(txDetails, affectedDocs, claimData.claimedBy, claimTimestamp, isThirdPartyForLiaison);

      return `Success: Released ${affectedDocs.length} document(s).`;
    } 
    return "No eligible documents found to release.";

  } catch (e) { return "Error: " + e.message; }
}



// --- 3. PROCESS PULL OUT (Text-Based, No Signature) ---
function processPullOut(pullOutData) {
  try {
    const documentsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Documents');
    const timestamp = new Date();
    const affectedDocs = [];

    const lastRow = documentsSheet.getLastRow();
    // Fetch Columns A to I
    const data = documentsSheet.getRange(2, 1, lastRow - 1, 9).getValues();

    for (let i = 0; i < data.length; i++) {
      if (String(data[i][0]) === String(pullOutData.transactionId)) {
        const row = i + 2;
        const isTarget = pullOutData.isBatch || (String(row) === String(pullOutData.documentRow));
        const currentStatus = data[i][3]; // Col D

        if (isTarget && currentStatus !== 'Claimed/Released' && currentStatus !== 'Pulled Out') {
           documentsSheet.getRange(row, 4).setValue('Pulled Out'); // Col D
           documentsSheet.getRange(row, 5).setValue(timestamp);    // Col E
           documentsSheet.getRange(row, 6).setValue('Pulled Out'); // Col F (ClaimedBy placeholder)
           documentsSheet.getRange(row, 7).setValue(pullOutData.comments); // Col G (Notes)
           
           const pName = data[i][7]; // Col H
           const pEmail = data[i][8]; // Col I

           affectedDocs.push({
             Title: data[i][2],
             Type: data[i][1],
             Status: 'Pulled Out',
             PrincipalName: pName,
             PrincipalEmail: pEmail
           });
        }
      }
    }

    if (affectedDocs.length > 0) {
      const txDetails = getTransactionDetails(pullOutData.transactionId);
      
      const principalGroups = {};
      affectedDocs.forEach(doc => {
        if (doc.PrincipalEmail && doc.PrincipalEmail.trim() !== "") {
          if (!principalGroups[doc.PrincipalEmail]) {
            principalGroups[doc.PrincipalEmail] = { Name: doc.PrincipalName, Docs: [] };
          }
          principalGroups[doc.PrincipalEmail].Docs.push(doc);
        }
      });

      Object.keys(principalGroups).forEach(email => {
        const group = principalGroups[email];
        const principalTxDetails = {
          TransactionID: txDetails.TransactionID,
          ContactPerson: group.Name || "Document Owner",
          ContactEmail: email
        };
        sendPulledOutEmail(principalTxDetails, group.Docs, timestamp, pullOutData.comments);
      });

      sendPulledOutEmail(txDetails, affectedDocs, timestamp, pullOutData.comments);

      return `Success: Pulled out ${affectedDocs.length} document(s).`;
    }
    return "No eligible documents found to pull out.";

  } catch (e) { return "Error: " + e.message; }
}


function sendPulledOutEmail(txDetails, docsList, timestamp, reason) {
  const subject = `[DMS] Document Retrieved - Ref: ${txDetails.TransactionID}`;
  const formattedTimestamp = timestamp.toLocaleString('en-US', { timeZone: 'Asia/Manila' });

  const rows = docsList.map(d => {
      let displayTitle = d.Title;
      if (displayTitle && typeof displayTitle === 'string') {
          displayTitle = displayTitle.replace(/\n/g, '<br>');
      }
      return `<tr>
        <td style="padding:12px;border-bottom:1px solid #f8d7da;">${displayTitle}</td>
        <td style="padding:12px;border-bottom:1px solid #f8d7da;color:#721c24;">${d.Type}</td>
      </tr>`;
  }).join('');

  const html = `
    <div style="background-color: #f4f6f8; padding: 40px 0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;">
      <div style="max-width: 650px; margin: 0 auto; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 10px rgba(0,0,0,0.05);">
        
        <div style="background-color: #1C2790; padding: 30px; text-align: center;">
          <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 320px; display: block; margin: 0 auto;">
        </div>

        <div style="padding: 40px; border-top: 6px solid #dc3545;">
          <h1 style="color: #dc3545; margin: 0 0 20px 0; text-align: center; font-size: 24px; text-transform: uppercase; letter-spacing: 1px;">Document Retrieval Notice</h1>
          
          <div style="background-color: #f0f4ff; padding: 20px; text-align: center; margin: 0 0 30px 0; border-radius: 4px; border: 1px dashed #1C2790;">
            <span style="font-size: 11px; color: #555; text-transform: uppercase; display: block; margin-bottom: 5px; letter-spacing: 1px;">Transaction Reference</span>
            <span style="font-size: 24px; font-weight: bold; color: #1C2790;">${txDetails.TransactionID}</span>
          </div>

          <p style="font-size: 16px; color: #333;">Dear <b>${txDetails.ContactPerson}</b>,</p>
          <p style="font-size: 16px; color: #333; line-height: 1.6;">The following documents have been retrieved/pulled out from our custody:</p>

          <table style="width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px;">
            <thead style="background-color: #f8d7da; color: #721c24;">
              <tr>
                <th style="padding: 12px; text-align: left;">Document Title / Details</th>
                <th style="padding: 12px; text-align: left;">Type</th>
              </tr>
            </thead>
            <tbody>${rows}</tbody>
          </table>

          <div style="margin-top: 25px; padding: 20px; background-color: #fff5f5; border: 1px solid #f5c6cb; border-radius: 4px; color: #721c24;">
            <h4 style="margin:0 0 10px 0; font-size: 14px; text-transform: uppercase;">Reason for Retrieval:</h4>
            <p style="margin:0; font-size: 15px; font-style: italic;">"${reason}"</p>
          </div>

          <div style="margin-top: 30px; text-align: center;">
            <p style="font-size: 12px; color: #999; text-transform: uppercase; margin-bottom: 10px;">Timestamp</p>
            <p style="font-size: 13px; color: #555; margin-top: 15px;"><strong>${formattedTimestamp}</strong></p>
          </div>
        </div>

        <div style="background-color: #eeeeee; padding: 20px; text-align: center; font-size: 12px; color: #888;">
          <p style="margin: 0;">&copy; ${new Date().getFullYear()} HRMDD-ComBen Document Management System.</p>
          <p style="margin: 5px 0 0 0;">All Rights Reserved.</p>
        </div>
      </div>
    </div>`;
  
  MailApp.sendEmail({
    to: txDetails.ContactEmail, subject: subject, htmlBody: html, name: 'DAP HRMDD-ComBen',
    inlineImages: null // Explicitly null to show intent
  });
}

/**
 * HELPER: Generates the HTML Email Body with Transaction/Document Tables
 */
function generateEmailBody(title, mainMessage, txDetails, documentsList, remarks, colorCode) {
  // 1. Build Documents Rows
  let docsRows = documentsList.map(doc => 
    `<tr>
       <td style="padding: 8px; border: 1px solid #ddd;">${doc.DocumentTitle}</td>
       <td style="padding: 8px; border: 1px solid #ddd;">${doc.DocumentType}</td>
       <td style="padding: 8px; border: 1px solid #ddd;">${doc.Status}</td>
     </tr>`
  ).join('');

  // 2. Remarks Section
  let remarksHtml = '';
  if (remarks) {
    remarksHtml = `
      <div style="margin-top: 20px; padding: 15px; background-color: #f9f9f9; border-left: 5px solid ${colorCode};">
        <h4 style="margin:0 0 5px 0; color: ${colorCode};">Remarks / Comments:</h4>
        <p style="margin:0;">${remarks}</p>
      </div>`;
  }

  // 3. Construct Full HTML
  return `
    <div style="font-family: Arial, sans-serif; font-size: 15px; color: #333; max-width: 700px; margin: auto; border: 1px solid #e0e0e0; background-color: #fff;">
      
      <div style="background-color: #1C2790; padding: 25px; text-align: center;">
        <img src="https://i.imgur.com/jaEbfAR.png" alt="DAP Logo" style="width: 300px;">
      </div>

      <div style="padding: 30px; border-top: 6px solid #CDAE2C;">
        <h2 style="color: ${colorCode}; margin-top: 0;">${title}</h2>
        <p>Dear <b>${txDetails.ContactPerson}</b>,</p>
        <p>${mainMessage}</p>

        <table style="width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px; background-color: #f8f9fa;">
          <tr>
            <td style="padding: 10px; font-weight: bold; border-bottom: 1px solid #eee; width: 140px;">Transaction ID:</td>
            <td style="padding: 10px; border-bottom: 1px solid #eee; color: #1C2790; font-weight: bold;">${txDetails.TransactionID}</td>
          </tr>
          <tr>
            <td style="padding: 10px; font-weight: bold; border-bottom: 1px solid #eee;">Department:</td>
            <td style="padding: 10px; border-bottom: 1px solid #eee;">${txDetails.CenterDept}</td>
          </tr>
          <tr>
            <td style="padding: 10px; font-weight: bold;">Date Logged:</td>
            <td style="padding: 10px;">${txDetails.SubmissionTimestamp}</td>
          </tr>
        </table>

        <h3 style="margin-top: 25px; font-size: 16px; color: #555;">Document Details</h3>
        <table style="width: 100%; border-collapse: collapse; font-size: 13px;">
          <thead>
            <tr style="background-color: #1C2790; color: white;">
              <th style="padding: 10px; text-align: left; border: 1px solid #1C2790;">Title</th>
              <th style="padding: 10px; text-align: left; border: 1px solid #1C2790;">Type</th>
              <th style="padding: 10px; text-align: left; border: 1px solid #1C2790;">Status</th>
            </tr>
          </thead>
          <tbody>${docsRows}</tbody>
        </table>

        ${remarksHtml}

        <p style="margin-top: 30px; font-size: 13px; color: #777;">Thank you,<br><b>HRMDD-ComBen Document Management System</b></p>
      </div>
    </div>
  `;
}

/**
 * Fetches the Directory list (Name | Email) for the dropdown.
 */
function getDirectory() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Directory');
    if (!sheet || sheet.getLastRow() < 2) return [];
    
    // Get all data from Row 2 down
    const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
    
    // Return array of objects: [{name: "Juan", email: "juan@dap.edu.ph"}]
    return data.map(row => ({ name: row[0], email: row[1] })).filter(d => d.name);
  } catch (e) {
    console.error("Error fetching directory: " + e.toString());
    return [];
  }
}

// --- NEW: FETCH LEAVE STATS FOR VALIDATION ---
function getLeaveStats(employeeName) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const leaveSheet = ss.getSheetByName('LeaveLedger');
    const documentsSheet = ss.getSheetByName('Documents'); // Needed to check status
    
    if (!leaveSheet || !documentsSheet) return {};

    // 1. Build Status Map (TxID -> Status)
    // We need this to exclude "Pulled Out" leaves from the count
    const statusMap = {};
    if (documentsSheet.getLastRow() > 1) {
       const docData = documentsSheet.getRange(2, 1, documentsSheet.getLastRow()-1, 5).getValues(); // A to E
       docData.forEach(row => {
          // Row[0] = TxID, Row[1] = Type, Row[4] = Status
          if (row[0] && row[1] === 'Application for Leave (ALA)') {
             statusMap[row[0]] = row[4]; 
          }
       });
    }

    const currentYear = new Date().getFullYear();
    const stats = {}; // { "Special Privilege Leave": 2, "Wellness Leave": 5 }

    if (leaveSheet.getLastRow() > 1) {
      // Columns: A=TxID, B=Name, C=Type, F=TotalDays, H=Timestamp
      const data = leaveSheet.getRange(2, 1, leaveSheet.getLastRow() - 1, 8).getValues();
      
      data.forEach(row => {
        const txId = row[0];
        const name = row[1];
        const type = row[2];
        const days = parseInt(row[5]) || 0;
        const timestamp = row[7];
        
        // Filter: Match Name + Current Year + Active Status (Not Pulled Out)
        if (name === employeeName && timestamp instanceof Date && timestamp.getFullYear() === currentYear) {
            const status = statusMap[txId] || "Pending Signature";
            if (status !== 'Pulled Out') {
                if (!stats[type]) stats[type] = 0;
                stats[type] += days;
            }
        }
      });
    }
    
    return stats;

  } catch (e) {
    console.error("getLeaveStats Error: " + e.toString());
    return {};
  }
}