ระบบแจ้งซ่อม/บำรุงอาคารสถานที่

// 🎯 Google Apps Script Code สำหรับระบบแจ้งซ่อม
// ✅ ตั้งค่า Google Sheets ID แล้ว: 1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4

function doGet(e) {
  return handleRequest(e);
}

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  try {
    // เปิด Google Sheets - ใช้ ID ที่ตั้งค่าแล้ว
    const SHEET_ID = '1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4';
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    
    // ตั้งค่า Headers ถ้ายังไม่มี
    if (sheet.getLastRow() === 0) {
      sheet.getRange(1, 1, 1, 7).setValues([
        ['ID', 'Reporter Name', 'Issue', 'Location', 'Status', 'Created At', 'Completed At']
      ]);
      SpreadsheetApp.flush();
    }
    
    const action = e.parameter.action || e.parameters.action?.[0];
    
    // Log ข้อมูลที่ได้รับเพื่อ debug
    console.log('=== REQUEST START ===');
    console.log('Received action:', action);
    console.log('All parameters:', e.parameter);
    console.log('All parameters array:', e.parameters);
    console.log('Sheet ID:', SHEET_ID);
    console.log('Sheet name:', sheet.getName());
    console.log('Current rows:', sheet.getLastRow());
    
    if (action === 'addReport') {
      // เพิ่มรายการใหม่
      const newRow = [
        e.parameter.id || e.parameters.id?.[0],
        e.parameter.reporterName || e.parameters.reporterName?.[0],
        e.parameter.issue || e.parameters.issue?.[0],
        e.parameter.location || e.parameters.location?.[0],
        e.parameter.status || e.parameters.status?.[0] || 'pending',
        e.parameter.createdAt || e.parameters.createdAt?.[0],
        e.parameter.completedAt || e.parameters.completedAt?.[0] || ''
      ];
      
      console.log('Adding new row:', newRow);
      
      // ตรวจสอบว่าข้อมูลครบถ้วน
      if (!newRow[0] || !newRow[1] || !newRow[2] || !newRow[3]) {
        console.log('ERROR: Missing required data');
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Missing required data (ID, Reporter Name, Issue, or Location)',
            received: newRow
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      // เพิ่มข้อมูลและบังคับบันทึก
      sheet.appendRow(newRow);
      SpreadsheetApp.flush();
      Utilities.sleep(100); // รอให้แน่ใจว่าบันทึกแล้ว
      
      console.log('Row added successfully. New total rows:', sheet.getLastRow());
      
      return ContentService
        .createTextOutput(JSON.stringify({
          success: true, 
          action: 'addReport',
          addedId: newRow[0],
          totalRows: sheet.getLastRow()
        }))
        .setMimeType(ContentService.MimeType.JSON);
        
    } else if (action === 'getReports' || action === 'load') {
      // โหลดข้อมูลทั้งหมด
      console.log('Loading all reports...');
      const data = sheet.getDataRange().getValues();
      console.log('Raw data from sheet:', data.length, 'rows');
      
      if (data.length <= 1) {
        console.log('No data rows found (only headers or empty)');
        return ContentService
          .createTextOutput(JSON.stringify([]))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      const headers = data[0];
      const rows = data.slice(1);
      console.log('Headers:', headers);
      console.log('Data rows:', rows.length);
      
      const reports = rows.map((row, index) => {
        const report = {
          id: String(row[0] || ''), // แปลงเป็น string เสมอ
          reporterName: String(row[1] || ''),
          issue: String(row[2] || ''),
          location: String(row[3] || ''),
          status: String(row[4] || 'pending'),
          createdAt: row[5] ? String(row[5]) : '',
          completedAt: row[6] ? String(row[6]) : ''
        };
        console.log(`Row ${index + 1}:`, report);
        return report;
      });
      
      console.log('Returning', reports.length, 'reports');
      return ContentService
        .createTextOutput(JSON.stringify(reports))
        .setMimeType(ContentService.MimeType.JSON);
        
    } else if (action === 'updateStatus') {
      // อัพเดทสถานะ - ปรับปรุงให้แข็งแกร่งขึ้น
      console.log('=== UPDATE STATUS REQUEST ===');
      
      const reportId = e.parameter.reportId || e.parameters.reportId?.[0] || 
                      e.parameter.id || e.parameters.id?.[0] ||
                      e.parameter.update_id || e.parameters.update_id?.[0];
      const newStatus = e.parameter.status || e.parameters.status?.[0] ||
                       e.parameter.newStatus || e.parameters.newStatus?.[0];
      const completedAt = e.parameter.completedAt || e.parameters.completedAt?.[0];
      
      console.log('Update parameters:');
      console.log('- reportId:', reportId, '(type:', typeof reportId, ')');
      console.log('- newStatus:', newStatus, '(type:', typeof newStatus, ')');
      console.log('- completedAt:', completedAt);
      
      if (!reportId || !newStatus) {
        console.log('ERROR: Missing required parameters');
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Missing reportId or status',
            received: {reportId, newStatus},
            debug: {
              hasReportId: !!reportId,
              hasNewStatus: !!newStatus,
              allParams: e.parameter,
              allParamsArray: e.parameters
            }
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      console.log('Getting current data from sheet...');
      const data = sheet.getDataRange().getValues();
      console.log('Sheet has', data.length, 'total rows (including header)');
      
      let updateDetails = {};
      let matchingRowFound = false;
      let rowIndex = -1;
      
      // แสดงข้อมูลทั้งหมดใน sheet เพื่อ debug
      console.log('=== CURRENT SHEET DATA ===');
      for (let i = 0; i < Math.min(data.length, 10); i++) { // แสดงแค่ 10 แถวแรก
        if (i === 0) {
          console.log('Header row:', data[i]);
        } else {
          console.log(`Data row ${i} (sheet row ${i + 1}):`, data[i]);
          console.log(`- ID: "${data[i][0]}" (type: ${typeof data[i][0]})`);
          console.log(`- Status: "${data[i][4]}" (type: ${typeof data[i][4]})`);
        }
      }
      
      console.log('=== SEARCHING FOR MATCHING ROW ===');
      console.log('Looking for ID:', reportId, '(type:', typeof reportId, ')');
      
      // ค้นหาแถวที่ตรงกัน
      for (let i = 1; i < data.length; i++) {
        const currentId = String(data[i][0] || ''); // แปลงเป็น string เสมอ
        const targetId = String(reportId || '');
        
        console.log(`Checking row ${i} (sheet row ${i + 1}):`);
        console.log(`- Current ID: "${currentId}"`);
        console.log(`- Target ID: "${targetId}"`);
        console.log(`- Match: ${currentId === targetId}`);
        
        if (currentId === targetId) {
          matchingRowFound = true;
          rowIndex = i;
          console.log('*** MATCH FOUND! ***');
          console.log('Row index:', i, '(sheet row:', i + 1, ')');
          break;
        }
      }
      
      if (!matchingRowFound) {
        console.log('=== NO MATCH FOUND ===');
        console.log('Searched ID:', reportId);
        console.log('Available IDs in sheet:');
        const availableIds = data.slice(1).map((row, index) => {
          const id = String(row[0] || '');
          console.log(`- Row ${index + 2}: "${id}"`);
          return id;
        });
        
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Record not found',
            searchedId: String(reportId),
            availableIds: availableIds,
            debug: {
              totalRows: data.length,
              dataRows: data.length - 1,
              searchPerformed: true
            }
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      // ทำการอัพเดท
      console.log('=== UPDATING STATUS ===');
      const oldStatus = String(data[rowIndex][4] || '');
      const oldCompletedAt = String(data[rowIndex][6] || '');
      
      console.log('Before update:');
      console.log('- Status (column E, index 4):', oldStatus);
      console.log('- CompletedAt (column G, index 6):', oldCompletedAt);
      
      try {
        // อัพเดทสถานะ
        const statusCell = sheet.getRange(rowIndex + 1, 5); // Column E (Status)
        console.log('Setting status in cell E' + (rowIndex + 1) + ' to:', newStatus);
        statusCell.setValue(newStatus);
        
        // อัพเดทวันที่เสร็จสิ้น
        const completedAtCell = sheet.getRange(rowIndex + 1, 7); // Column G (Completed At)
        let newCompletedAtValue = '';
        
        if (newStatus === 'completed') {
          if (completedAt) {
            newCompletedAtValue = completedAt;
          } else {
            newCompletedAtValue = new Date().toISOString();
          }
          console.log('Setting completedAt in cell G' + (rowIndex + 1) + ' to:', newCompletedAtValue);
          completedAtCell.setValue(newCompletedAtValue);
        } else if (newStatus === 'pending') {
          // ถ้าเปลี่ยนกลับเป็น pending ให้ลบวันที่เสร็จสิ้น
          newCompletedAtValue = '';
          console.log('Clearing completedAt in cell G' + (rowIndex + 1) + ' for pending status');
          completedAtCell.setValue('');
        }
        
        // บังคับบันทึกและรอ
        console.log('=== FORCE SAVE AND VERIFICATION ===');
        SpreadsheetApp.flush();
        Utilities.sleep(200); // รอให้แน่ใจ
        SpreadsheetApp.flush();
        console.log('Double flush completed');
        
        // ตรวจสอบผลลัพธ์
        const verificationData = sheet.getDataRange().getValues();
        const actualStatus = String(verificationData[rowIndex][4] || '');
        const actualCompletedAt = String(verificationData[rowIndex][6] || '');
        
        console.log('=== VERIFICATION RESULTS ===');
        console.log('After update verification:');
        console.log('- Status in sheet:', actualStatus, '(expected:', newStatus, ')');
        console.log('- CompletedAt in sheet:', actualCompletedAt, '(expected:', newCompletedAtValue, ')');
        
        const statusMatch = actualStatus === newStatus;
        const completedAtMatch = actualCompletedAt === newCompletedAtValue;
        const updateSuccess = statusMatch && (newStatus !== 'completed' || completedAtMatch);
        
        console.log('- Status match:', statusMatch);
        console.log('- CompletedAt match:', completedAtMatch);
        console.log('- Overall success:', updateSuccess);
        
        // ถ้าอัพเดทไม่สำเร็จ ลองอีกครั้ง
        if (!updateSuccess) {
          console.log('*** RETRY UPDATE - First attempt failed ***');
          statusCell.setValue(newStatus);
          if (newStatus === 'completed' && newCompletedAtValue) {
            completedAtCell.setValue(newCompletedAtValue);
          } else if (newStatus === 'pending') {
            completedAtCell.setValue('');
          }
          SpreadsheetApp.flush();
          Utilities.sleep(300); // รอนานขึ้น
          SpreadsheetApp.flush();
          
          // ตรวจสอบอีกครั้ง
          const retryData = sheet.getDataRange().getValues();
          const retryStatus = String(retryData[rowIndex][4] || '');
          const retryCompletedAt = String(retryData[rowIndex][6] || '');
          
          console.log('After retry verification:');
          console.log('- Status in sheet:', retryStatus, '(expected:', newStatus, ')');
          console.log('- CompletedAt in sheet:', retryCompletedAt, '(expected:', newCompletedAtValue, ')');
          
          const retryStatusMatch = retryStatus === newStatus;
          const retryCompletedAtMatch = retryCompletedAt === newCompletedAtValue;
          const retrySuccess = retryStatusMatch && (newStatus !== 'completed' || retryCompletedAtMatch);
          
          updateDetails = {
            rowIndex: rowIndex + 1,
            oldStatus: oldStatus,
            newStatus: newStatus,
            actualStatusInSheet: retryStatus,
            oldCompletedAt: oldCompletedAt,
            newCompletedAt: newCompletedAtValue,
            actualCompletedAtInSheet: retryCompletedAt,
            updateSuccess: retrySuccess,
            statusMatch: retryStatusMatch,
            completedAtMatch: retryCompletedAtMatch,
            retryAttempted: true
          };
        } else {
          updateDetails = {
            rowIndex: rowIndex + 1,
            oldStatus: oldStatus,
            newStatus: newStatus,
            actualStatusInSheet: actualStatus,
            oldCompletedAt: oldCompletedAt,
            newCompletedAt: newCompletedAtValue,
            actualCompletedAtInSheet: actualCompletedAt,
            updateSuccess: updateSuccess,
            statusMatch: statusMatch,
            completedAtMatch: completedAtMatch,
            retryAttempted: false
          };
        }
        
      } catch (updateError) {
        console.log('ERROR during update:', updateError.message);
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Update operation failed: ' + updateError.message,
            reportId: reportId,
            newStatus: newStatus
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      console.log('=== UPDATE COMPLETE ===');
      const response = {
        success: updateDetails.updateSuccess, 
        action: 'updateStatus',
        updatedId: reportId,
        newStatus: newStatus,
        updateDetails: updateDetails,
        message: updateDetails.updateSuccess ? 
          'Status updated successfully in Google Sheets' : 
          'Status update failed - please check sheet manually and try again',
        verification: {
          statusUpdated: updateDetails.statusMatch,
          completedAtUpdated: updateDetails.completedAtMatch,
          overallSuccess: updateDetails.updateSuccess,
          retryAttempted: updateDetails.retryAttempted
        }
      };
      
      console.log('Sending response:', response);
      return ContentService
        .createTextOutput(JSON.stringify(response))
        .setMimeType(ContentService.MimeType.JSON);
        
    } else if (action === 'deleteReport') {
      // ลบรายการ - ปรับปรุงให้แข็งแกร่งขึ้น
      console.log('=== DELETE REQUEST ===');
      const reportId = e.parameter.reportId || e.parameters.reportId?.[0] ||
                      e.parameter.id || e.parameters.id?.[0];
      
      console.log('Delete reportId:', reportId);
      
      if (!reportId) {
        console.log('ERROR: No reportId provided for delete');
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Missing reportId for delete',
            received: e.parameter
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      const data = sheet.getDataRange().getValues();
      console.log('Sheet has', data.length, 'rows for delete operation');
      
      let deleted = false;
      let deletedRowIndex = -1;
      
      for (let i = 1; i < data.length; i++) {
        const currentId = String(data[i][0] || '');
        const targetId = String(reportId || '');
        
        console.log(`Checking row ${i} for delete: "${currentId}" vs "${targetId}"`);
        
        if (currentId === targetId) {
          console.log('Match found! Deleting row', i + 1);
          deletedRowIndex = i + 1;
          
          try {
            sheet.deleteRow(i + 1);
            SpreadsheetApp.flush();
            Utilities.sleep(100); // รอให้แน่ใจ
            deleted = true;
            console.log('Row deleted successfully');
          } catch (deleteError) {
            console.log('ERROR during delete:', deleteError.message);
            return ContentService
              .createTextOutput(JSON.stringify({
                success: false, 
                error: 'Delete operation failed: ' + deleteError.message,
                reportId: reportId
              }))
              .setMimeType(ContentService.MimeType.JSON);
          }
          break;
        }
      }
      
      if (!deleted) {
        console.log('No matching row found for delete');
        const availableIds = data.slice(1).map(row => String(row[0] || ''));
        return ContentService
          .createTextOutput(JSON.stringify({
            success: false, 
            error: 'Record not found for delete',
            searchedId: String(reportId),
            availableIds: availableIds
          }))
          .setMimeType(ContentService.MimeType.JSON);
      }
      
      return ContentService
        .createTextOutput(JSON.stringify({
          success: true, 
          action: 'deleteReport', 
          deletedId: reportId,
          deletedRowIndex: deletedRowIndex,
          remainingRows: sheet.getLastRow()
        }))
        .setMimeType(ContentService.MimeType.JSON);
        
    } else if (action === 'test') {
      // ทดสอบการเชื่อมต่อ
      console.log('Test connection request received');
      const testResponse = {
        success: true, 
        message: 'Google Apps Script connection successful!',
        timestamp: new Date().toISOString(),
        sheetInfo: {
          sheetId: SHEET_ID,
          totalRows: sheet.getLastRow(),
          totalColumns: sheet.getLastColumn(),
          sheetName: sheet.getName(),
          url: SpreadsheetApp.openById(SHEET_ID).getUrl()
        },
        systemInfo: {
          timezone: Session.getScriptTimeZone(),
          user: Session.getActiveUser().getEmail(),
          locale: Session.getActiveUserLocale()
        }
      };
      console.log('Test response:', testResponse);
      return ContentService
        .createTextOutput(JSON.stringify(testResponse))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    // Default response
    console.log('No specific action matched, returning default response');
    return ContentService
      .createTextOutput(JSON.stringify({
        success: true, 
        message: 'Google Apps Script is working!',
        receivedAction: action,
        availableActions: ['addReport', 'getReports', 'load', 'updateStatus', 'deleteReport', 'test'],
        sheetInfo: {
          sheetId: SHEET_ID,
          totalRows: sheet.getLastRow(),
          sheetName: sheet.getName()
        }
      }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    console.log('=== ERROR OCCURRED ===');
    console.log('Error message:', error.message);
    console.log('Error stack:', error.stack);
    
    return ContentService
      .createTextOutput(JSON.stringify({
        success: false, 
        error: error.toString(),
        errorMessage: error.message,
        errorStack: error.stack,
        message: 'Error in Google Apps Script - check logs for details',
        timestamp: new Date().toISOString()
      }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}