apps-script/Code.gs

const CONFIG = Object.freeze({
  folderId: "1QBsYtQtUp5-uhqjDRugvxHZinozIu6YY",
  spreadsheetId: "1_LiYMMphsJaRRJje0pxKrNSiC1ahBS3GnHM01vPrrCk",
  sheetName: "الورقة1",
  timezone: "Asia/Riyadh",
  maxPhotos: 3,
  maxPhotoBytes: 3 * 1024 * 1024,
});

const HEADERS = [
  "رقم التوثيق",
  "تاريخ ووقت التسجيل",
  "اسم الباحث/ة",
  "اسم المنشأة",
  "السجل التجاري",
  "رقم العقد",
  "المدينة الصناعية",
  "حالة التوثيق",
  "الإفادة الميدانية",
  "روابط الصور",
  "رابط مجلد المنشأة",
  "عدد الصور",
  "مفتاح العينة",
  "آخر تعديل",
];

function doGet(event) {
  try {
    const params = (event && event.parameter) || {};
    const action = String(params.action || "health");
    if (action === "health") {
      return jsonResponse({ ok: true, service: "ICS2 Documentation", version: 4 });
    }

    if (action === "status") {
      validateResearcherAccess(params.accessCode);
      return jsonResponse({
        ok: true,
        generatedAt: new Date().toISOString(),
        records: getDocumentationRecords(true),
      });
    }

    if (action === "admin") {
      validateSupervisorAccess(params.accessCode);
      return jsonResponse({
        ok: true,
        generatedAt: new Date().toISOString(),
        timezone: CONFIG.timezone,
        records: getDocumentationRecords(true),
      });
    }

    throw new Error("الطلب غير معروف.");
  } catch (error) {
    return jsonResponse({ ok: false, error: String(error.message || error) });
  }
}

function authorizeService() {
  DriveApp.getFolderById(CONFIG.folderId).getName();
  SpreadsheetApp.openById(CONFIG.spreadsheetId).getName();
  return "AUTHORIZED";
}

function doPost(event) {
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(20000);
    const request = JSON.parse(event.postData.contents || "{}");
    validateResearcherAccess(request.accessCode);
    validateRequest(request);

    const sheet = getSheet();
    ensureHeaders(sheet);
    if (request.action === "updateEvidence") {
      return jsonResponse(updateEvidence(sheet, request));
    }

    return jsonResponse(createEvidence(sheet, request));
  } catch (error) {
    return jsonResponse({ ok: false, error: String(error.message || error) });
  } finally {
    try {
      lock.releaseLock();
    } catch (_) {}
  }
}

function createEvidence(sheet, request) {
  const existingIds = sheet
    .getRange(1, 1, Math.max(sheet.getLastRow(), 1), 1)
    .getDisplayValues()
    .flat();
  if (existingIds.includes(request.documentationId)) {
    return { ok: true, duplicate: true, documentationId: request.documentationId };
  }

  const now = new Date();
  const folder = getEvidenceFolder(request);
  const photoUrls = request.photos.map((photo, index) => savePhoto(folder, photo, request, index));
  sheet.appendRow([
    request.documentationId,
    now,
    safeText(request.researcher),
    safeText(request.establishmentName),
    safeText(request.commercialRecord),
    safeText(request.contractNumber) || "لا يوجد رقم عقد",
    safeText(request.city),
    safeText(request.fieldStatus),
    safeText(request.statement),
    photoUrls.join("\n"),
    folder.getUrl(),
    photoUrls.length,
    safeText(request.sampleKey),
    now,
  ]);

  return evidenceResponse(request, folder.getUrl(), photoUrls, now, now);
}

function updateEvidence(sheet, request) {
  const rowNumber = findDocumentationRow(sheet, request.documentationId);
  if (!rowNumber) throw new Error("تعذر العثور على التوثيق المطلوب تعديله.");

  const row = sheet.getRange(rowNumber, 1, 1, HEADERS.length).getValues()[0];
  if (
    safeText(row[12]) &&
    safeText(row[12]) !== safeText(request.sampleKey) &&
    safeText(row[4]) !== safeText(request.commercialRecord)
  ) {
    throw new Error("لا يمكن تعديل توثيق عينة أخرى.");
  }

  const oldPhotoUrls = splitUrls(row[9]);
  const keptPhotoUrls = Array.isArray(request.keptPhotoUrls)
    ? request.keptPhotoUrls.map(safeText).filter((url) => oldPhotoUrls.includes(url))
    : oldPhotoUrls;
  if (keptPhotoUrls.length + request.photos.length > CONFIG.maxPhotos) {
    throw new Error("الحد الأعلى 3 صور.");
  }

  const folder = row[10] ? getFolderByUrl(row[10], request) : getEvidenceFolder(request);
  const newPhotoUrls = request.photos.map((photo, index) =>
    savePhoto(folder, photo, request, keptPhotoUrls.length + index),
  );
  const updatedAt = new Date();
  const photoUrls = [...keptPhotoUrls, ...newPhotoUrls];
  sheet.getRange(rowNumber, 8, 1, 7).setValues([[
    safeText(request.fieldStatus),
    safeText(request.statement),
    photoUrls.join("\n"),
    folder.getUrl(),
    photoUrls.length,
    safeText(request.sampleKey),
    updatedAt,
  ]]);
  oldPhotoUrls
    .filter((url) => !keptPhotoUrls.includes(url))
    .forEach(trashDriveFileByUrl);

  const documentedAt = row[1] instanceof Date ? row[1] : updatedAt;
  return evidenceResponse(request, folder.getUrl(), photoUrls, documentedAt, updatedAt, true);
}

function evidenceResponse(request, folderUrl, photoUrls, documentedAt, updatedAt, updated) {
  return {
    ok: true,
    updated: Boolean(updated),
    documentationId: request.documentationId,
    sampleKey: safeText(request.sampleKey),
    researcher: safeText(request.researcher),
    establishmentName: safeText(request.establishmentName),
    commercialRecord: safeText(request.commercialRecord),
    contractNumber: safeText(request.contractNumber),
    city: safeText(request.city),
    fieldStatus: safeText(request.fieldStatus),
    statement: safeText(request.statement),
    documentedAt: documentedAt.toISOString(),
    documentedDate: Utilities.formatDate(documentedAt, CONFIG.timezone, "yyyy-MM-dd"),
    updatedAt: updatedAt.toISOString(),
    folderUrl,
    photoUrls,
    photoCount: photoUrls.length,
  };
}

function findDocumentationRow(sheet, documentationId) {
  if (sheet.getLastRow() < 2) return 0;
  const ids = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getDisplayValues().flat();
  const index = ids.indexOf(String(documentationId));
  return index < 0 ? 0 : index + 2;
}

function getFolderByUrl(url, request) {
  const id = driveIdFromUrl(url);
  return id ? DriveApp.getFolderById(id) : getEvidenceFolder(request);
}

function trashDriveFileByUrl(url) {
  const id = driveIdFromUrl(url);
  if (!id) return;
  try {
    DriveApp.getFileById(id).setTrashed(true);
  } catch (_) {}
}

function driveIdFromUrl(url) {
  const value = String(url || "");
  const match =
    value.match(/\/folders\/([a-zA-Z0-9_-]+)/) ||
    value.match(/\/d\/([a-zA-Z0-9_-]+)/) ||
    value.match(/[?&]id=([a-zA-Z0-9_-]+)/);
  return match ? match[1] : "";
}

function validateResearcherAccess(accessCode) {
  const expected = PropertiesService.getScriptProperties().getProperty("ACCESS_CODE") || "20302030";
  if (String(accessCode || "") !== expected) throw new Error("غير مصرح بالوصول.");
}

function validateSupervisorAccess(accessCode) {
  const expected = PropertiesService.getScriptProperties().getProperty("SUPERVISOR_CODE") || "1448";
  if (String(accessCode || "") !== expected) throw new Error("غير مصرح بالوصول.");
}

function validateRequest(request) {
  if (!request.documentationId) throw new Error("رقم التوثيق مفقود.");
  if (!safeText(request.sampleKey)) throw new Error("مفتاح العينة مفقود.");
  if (!safeText(request.researcher)) throw new Error("اسم الباحث مفقود.");
  if (!safeText(request.establishmentName)) throw new Error("اسم المنشأة مفقود.");
  if (!safeText(request.fieldStatus)) throw new Error("حالة التوثيق مطلوبة.");
  if (!safeText(request.statement)) throw new Error("الإفادة الميدانية مطلوبة.");
  if (!Array.isArray(request.photos)) request.photos = [];
  if (request.photos.length > CONFIG.maxPhotos) throw new Error("الحد الأعلى 3 صور.");
}

function getDocumentationRecords(includeDetails) {
  const sheet = getSheet();
  ensureHeaders(sheet);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return [];

  const values = sheet.getRange(2, 1, lastRow - 1, HEADERS.length).getValues();
  return values
    .filter((row) => row.some((cell) => cell !== ""))
    .map((row) => {
      const date = row[1] instanceof Date ? row[1] : new Date(row[1]);
      const validDate = !Number.isNaN(date.getTime());
      const base = {
        documentationId: safeText(row[0]),
        documentedAt: validDate ? date.toISOString() : "",
        documentedDate: validDate ? Utilities.formatDate(date, CONFIG.timezone, "yyyy-MM-dd") : "",
        researcher: safeText(row[2]),
        establishmentName: safeText(row[3]),
        commercialRecord: safeText(row[4]),
        contractNumber: safeText(row[5]),
        city: safeText(row[6]),
        fieldStatus: safeText(row[7]),
        photoCount: Number(row[11]) || splitUrls(row[9]).length,
        sampleKey: safeText(row[12]),
        updatedAt: row[13] instanceof Date ? row[13].toISOString() : "",
      };
      if (!includeDetails) return base;
      return Object.assign(base, {
        statement: safeText(row[8]),
        photoUrls: splitUrls(row[9]),
        folderUrl: safeText(row[10]),
      });
    })
    .sort((a, b) =>
      String(b.updatedAt || b.documentedAt).localeCompare(String(a.updatedAt || a.documentedAt)),
    );
}

function splitUrls(value) {
  return safeText(value).split(/\s+/).filter((item) => /^https?:\/\//.test(item));
}

function getSheet() {
  const spreadsheet = SpreadsheetApp.openById(CONFIG.spreadsheetId);
  return spreadsheet.getSheetByName(CONFIG.sheetName) || spreadsheet.insertSheet(CONFIG.sheetName);
}

function ensureHeaders(sheet) {
  const current = sheet.getRange(1, 1, 1, HEADERS.length).getDisplayValues()[0];
  if (current.join("") === "") {
    sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
    sheet.setFrozenRows(1);
    sheet.getRange(1, 1, 1, HEADERS.length)
      .setFontWeight("bold")
      .setBackground("#4137A8")
      .setFontColor("#FFFFFF");
    sheet.autoResizeColumns(1, HEADERS.length);
    return;
  }
  if (!current[12]) sheet.getRange(1, 13).setValue(HEADERS[12]);
  if (!current[13]) sheet.getRange(1, 14).setValue(HEADERS[13]);
}

function getEvidenceFolder(request) {
  const root = DriveApp.getFolderById(CONFIG.folderId);
  const researcherFolder = getOrCreateFolder(root, safeFileName(request.researcher));
  const cityFolder = getOrCreateFolder(researcherFolder, safeFileName(request.city || "مدينة غير محددة"));
  const entityName = `${safeText(request.commercialRecord) || "دون سجل"} - ${safeText(request.establishmentName)}`;
  return getOrCreateFolder(cityFolder, safeFileName(entityName));
}

function getOrCreateFolder(parent, name) {
  const folders = parent.getFoldersByName(name);
  return folders.hasNext() ? folders.next() : parent.createFolder(name);
}

function savePhoto(folder, photo, request, index) {
  const mimeType = String(photo.mimeType || "");
  if (!/^image\/(jpeg|png|webp)$/.test(mimeType)) throw new Error("نوع الصورة غير مدعوم.");
  const bytes = Utilities.base64Decode(String(photo.base64 || ""));
  if (bytes.length > CONFIG.maxPhotoBytes) throw new Error("إحدى الصور أكبر من الحد المسموح.");
  const extension = mimeType === "image/png" ? "png" : mimeType === "image/webp" ? "webp" : "jpg";
  const timestamp = Utilities.formatDate(new Date(), CONFIG.timezone, "yyyyMMdd-HHmmss");
  const name = safeFileName(
    `${request.commercialRecord || "دون-سجل"}-${timestamp}-${index + 1}.${extension}`,
  );
  return folder.createFile(Utilities.newBlob(bytes, mimeType, name)).getUrl();
}

function safeText(value) {
  return String(value == null ? "" : value).trim().slice(0, 2000);
}

function safeFileName(value) {
  return safeText(value).replace(/[\\/:*?"<>|#%{}[\]]/g, "-").slice(0, 140) || "غير محدد";
}

function jsonResponse(payload) {
  return ContentService.createTextOutput(JSON.stringify(payload))
    .setMimeType(ContentService.MimeType.JSON);
}