app.js

"use strict";

const CONFIG = {
  researcherPassword: "2030",
  managerPassword: "20302030",
  managerValue: "__manager__",
  apiUrl: "https://script.google.com/macros/s/AKfycbwH2wJA6ASKfm9P5FL4X_AFcVpzIoGaCub2tZ2csdxYDhinwZhToKnxhkCQlKPJi4mXvg/exec",
};

const FIELD_ALIASES = {
  commercial: ["السجل التجاري", "التجاري", "رقم السجل التجاري"],
  facility: ["اسم المنشأة", "اسم المنشاة", "المنشأة"],
  accountManager: ["مدير الحساب"],
  name: ["الاسم", "اسم المسؤول", "اسم الشخص"],
  jobTitle: ["المسمى الوظفي", "المسمى الوظيفي", "الوظيفة"],
  email: ["البريد الإلكتروني", "البريد الالكتروني", "الايميل", "الإيميل"],
  phone: ["رقم التواصل", "الجوال", "رقم الجوال", "الهاتف"],
  x: ["X", "x", "الإحداثيات X", "احداثيات X"],
  y: ["Y", "y", "الإحداثيات Y", "احداثيات Y"],
  region: ["المنطقة"],
  district: ["الحي"],
  researcher: ["اسم الباحث", "الباحث"],
};

const EDITABLE_KEYS = ["name", "jobTitle", "email", "phone"];
const REQUIRED_HEADERS = ["commercial", "facility", "name", "jobTitle", "email", "phone", "x", "y", "region", "district", "researcher"];
const DISPLAY_COLUMNS = [
  ["commercial", "السجل التجاري"],
  ["facility", "اسم المنشأة"],
  ["accountManager", "مدير الحساب"],
  ["name", "الاسم"],
  ["jobTitle", "المسمى الوظيفي"],
  ["email", "البريد الإلكتروني"],
  ["phone", "رقم التواصل"],
  ["region", "المنطقة"],
  ["district", "الحي"],
  ["researcher", "اسم الباحث"],
  ["map", "الموقع"],
  ["status", "الحالة"],
  ["actions", "الإجراءات"],
];

const LABELS = {
  commercial: "السجل التجاري",
  facility: "اسم المنشأة",
  accountManager: "مدير الحساب",
  name: "الاسم",
  jobTitle: "المسمى الوظيفي",
  email: "البريد الإلكتروني",
  phone: "رقم التواصل",
  region: "المنطقة",
  district: "الحي",
  researcher: "اسم الباحث",
};

const els = {
  loginView: document.getElementById("loginView"),
  appView: document.getElementById("appView"),
  loginForm: document.getElementById("loginForm"),
  researcherSelect: document.getElementById("researcherSelect"),
  password: document.getElementById("password"),
  loginError: document.getElementById("loginError"),
  togglePassword: document.getElementById("togglePassword"),
  roleLabel: document.getElementById("roleLabel"),
  welcomeLabel: document.getElementById("welcomeLabel"),
  heroTitle: document.getElementById("heroTitle"),
  heroDescription: document.getElementById("heroDescription"),
  totalCount: document.getElementById("totalCount"),
  enteredCount: document.getElementById("enteredCount"),
  missingCount: document.getElementById("missingCount"),
  progressCount: document.getElementById("progressCount"),
  progressLabel: document.getElementById("progressLabel"),
  progressBar: document.getElementById("progressBar"),
  adminStats: document.getElementById("adminStats"),
  researcherStatsGrid: document.getElementById("researcherStatsGrid"),
  bestResearcher: document.getElementById("bestResearcher"),
  leastResearcher: document.getElementById("leastResearcher"),
  exportSummaryButton: document.getElementById("exportSummaryButton"),
  exportFullButton: document.getElementById("exportFullButton"),
  searchInput: document.getElementById("searchInput"),
  researcherFilterGroup: document.getElementById("researcherFilterGroup"),
  researcherFilter: document.getElementById("researcherFilter"),
  regionFilter: document.getElementById("regionFilter"),
  districtFilter: document.getElementById("districtFilter"),
  statusFilter: document.getElementById("statusFilter"),
  saveAllButton: document.getElementById("saveAllButton"),
  resultsMeta: document.getElementById("resultsMeta"),
  tableHead: document.getElementById("tableHead"),
  tableBody: document.getElementById("tableBody"),
  cardsGrid: document.getElementById("cardsGrid"),
  emptyState: document.getElementById("emptyState"),
  refreshButton: document.getElementById("refreshButton"),
  logoutButton: document.getElementById("logoutButton"),
  toast: document.getElementById("toast"),
};

const state = {
  headers: [],
  headerMap: {},
  rows: [],
  visibleRows: [],
  currentUser: "",
  isManager: false,
  dirtyRows: new Map(),
  autosaveTimers: new Map(),
  toastTimer: null,
};

function normalize(value) {
  return String(value ?? "")
    .normalize("NFKD")
    .replace(/[\u064B-\u065F\u0670]/g, "")
    .replace(/[أإآ]/g, "ا")
    .replace(/ى/g, "ي")
    .replace(/ة/g, "ه")
    .replace(/\s+/g, " ")
    .trim()
    .toLowerCase();
}

function uniqueSorted(values) {
  return [...new Set(values.map((value) => String(value ?? "").trim()).filter(Boolean))].sort((a, b) =>
    a.localeCompare(b, "ar", { sensitivity: "base" }),
  );
}

function escapeHtml(value) {
  return String(value ?? "").replace(/[&<>"']/g, (char) => ({
    "&": "&amp;",
    "<": "&lt;",
    ">": "&gt;",
    '"': "&quot;",
    "'": "&#39;",
  })[char]);
}

function showToast(message) {
  clearTimeout(state.toastTimer);
  els.toast.textContent = message;
  els.toast.classList.add("show");
  state.toastTimer = setTimeout(() => els.toast.classList.remove("show"), 2600);
}

function findHeader(headers, aliases) {
  const normalized = new Map(headers.map((header) => [normalize(header), header]));
  for (const alias of aliases) {
    const match = normalized.get(normalize(alias));
    if (match) return match;
  }
  return "";
}

function buildHeaderMap(headers) {
  return Object.fromEntries(Object.entries(FIELD_ALIASES).map(([key, aliases]) => [key, findHeader(headers, aliases)]));
}

function valueOf(row, key) {
  const header = state.headerMap[key];
  return header ? row.raw[header] ?? "" : "";
}

function effectiveValue(row, key) {
  const dirty = state.dirtyRows.get(Number(row.rowNumber));
  return dirty && key in dirty ? dirty[key] : valueOf(row, key);
}

function setValue(row, key, value) {
  const header = state.headerMap[key];
  if (header) row.raw[header] = value;
}

function isEntered(row) {
  return EDITABLE_KEYS.every((key) => String(effectiveValue(row, key)).trim());
}

function rowByNumber(rowNumber) {
  return state.rows.find((row) => Number(row.rowNumber) === Number(rowNumber));
}

function rowsFromApiPayload(payload) {
  const headers = Array.isArray(payload.headers) ? payload.headers.map((header) => String(header ?? "").trim()) : [];
  const rows = Array.isArray(payload.rows) ? payload.rows : [];
  return {
    headers,
    rows: rows.map((row, index) => ({
      rowNumber: Number(row.rowNumber || row._rowNumber || index + 2),
      raw: { ...row },
    })),
  };
}

async function apiRequest(payload) {
  const response = await fetch(CONFIG.apiUrl, {
    method: "POST",
    headers: { "Content-Type": "text/plain;charset=utf-8" },
    body: JSON.stringify(payload),
    cache: "no-store",
  });
  const text = await response.text();
  let data;
  try {
    data = JSON.parse(text);
  } catch {
    throw new Error("استجابة Apps Script ليست JSON. تأكد من نشر doPost و doGet.");
  }
  if (!response.ok || data.ok === false || data.success === false) {
    throw new Error(data.message || "تعذر تنفيذ الطلب عبر Apps Script.");
  }
  return data;
}

async function loadData() {
  const data = await apiRequest({ action: "read" });
  const parsed = rowsFromApiPayload(data);
  if (!parsed.headers.length) throw new Error("لم يتم العثور على صف العناوين في Google Sheet.");
  return parsed;
}

function validateHeaders() {
  const missing = REQUIRED_HEADERS.filter((key) => !state.headerMap[key]);
  if (missing.length) {
    const labels = missing.map((key) => FIELD_ALIASES[key][0]).join("، ");
    throw new Error(`أعمدة مطلوبة غير موجودة في Google Sheet: ${labels}`);
  }
}

function applyLoadedData(data) {
  state.headers = data.headers;
  state.headerMap = buildHeaderMap(state.headers);
  validateHeaders();
  state.rows = data.rows;
  state.autosaveTimers.forEach((timer) => clearTimeout(timer));
  state.autosaveTimers.clear();
  state.dirtyRows.clear();
  updateSaveAllState();
  populateSelects();
}

function fillSelect(select, placeholder, values) {
  select.replaceChildren(new Option(placeholder, ""));
  values.forEach((value) => select.add(new Option(value, value)));
}

function populateSelects() {
  const researchers = uniqueSorted(state.rows.map((row) => valueOf(row, "researcher")));
  els.researcherSelect.replaceChildren(new Option("اختر اسم الباحث", ""));
  researchers.forEach((name) => els.researcherSelect.add(new Option(name, name)));
  els.researcherSelect.add(new Option("دخول المدير", CONFIG.managerValue));
  els.researcherSelect.disabled = false;

  fillSelect(els.researcherFilter, "كل الباحثين", researchers);
  fillSelect(els.regionFilter, "كل المناطق", uniqueSorted(state.rows.map((row) => valueOf(row, "region"))));
  fillSelect(els.districtFilter, "كل الأحياء", uniqueSorted(state.rows.map((row) => valueOf(row, "district"))));
}

function scopedRows() {
  if (state.isManager) return state.rows;
  return state.rows.filter((row) => valueOf(row, "researcher") === state.currentUser);
}

function applyFilters() {
  const query = normalize(els.searchInput.value);
  const researcher = els.researcherFilter.value;
  const region = els.regionFilter.value;
  const district = els.districtFilter.value;
  const status = els.statusFilter.value;

  state.visibleRows = scopedRows()
    .filter((row) => {
      const searchText = normalize([
        valueOf(row, "commercial"),
        valueOf(row, "facility"),
        valueOf(row, "accountManager"),
        valueOf(row, "researcher"),
        valueOf(row, "region"),
        valueOf(row, "district"),
        valueOf(row, "name"),
        valueOf(row, "phone"),
      ].join(" "));
      const entered = isEntered(row);
      return (
        (!query || searchText.includes(query)) &&
        (!researcher || valueOf(row, "researcher") === researcher) &&
        (!region || valueOf(row, "region") === region) &&
        (!district || valueOf(row, "district") === district) &&
        (!status || (status === "entered" ? entered : !entered))
      );
    })
    .sort((a, b) => Number(isEntered(a)) - Number(isEntered(b)) || String(valueOf(a, "facility")).localeCompare(String(valueOf(b, "facility")), "ar"));
  render();
}

function computeStats(rows) {
  const total = rows.length;
  const entered = rows.filter(isEntered).length;
  const missing = total - entered;
  const progress = total ? Math.round((entered / total) * 100) : 0;
  return { total, entered, missing, progress };
}

function researcherStats() {
  return uniqueSorted(state.rows.map((row) => valueOf(row, "researcher"))).map((name) => {
    const rows = state.rows.filter((row) => valueOf(row, "researcher") === name);
    return { name, ...computeStats(rows) };
  });
}

function renderSummary() {
  const stats = computeStats(scopedRows());
  els.totalCount.textContent = stats.total;
  els.enteredCount.textContent = stats.entered;
  els.missingCount.textContent = stats.missing;
  els.progressCount.textContent = `${stats.progress}%`;
  els.progressLabel.textContent = `نسبة الإنجاز ${stats.progress}%`;
  els.progressBar.style.width = `${stats.progress}%`;
  els.resultsMeta.textContent = `${state.visibleRows.length} من أصل ${stats.total} منشأة`;
}

function renderAdminStats() {
  if (!state.isManager) return;
  const stats = researcherStats();
  els.researcherStatsGrid.replaceChildren(...stats.map((item) => {
    const card = document.createElement("article");
    card.className = "researcher-card";
    card.innerHTML = `
      <h3>${escapeHtml(item.name)}</h3>
      <span>الإجمالي: ${item.total} | المكتملة: ${item.entered} | غير المكتملة: ${item.missing}</span>
      <div class="bar" aria-label="نسبة الإنجاز ${item.progress}%"><i style="width:${item.progress}%"></i></div>
      <span>${item.progress}% إنجاز</span>
    `;
    return card;
  }));
  const sorted = [...stats].sort((a, b) => b.progress - a.progress || b.entered - a.entered || a.name.localeCompare(b.name, "ar"));
  els.bestResearcher.textContent = sorted[0] ? `${sorted[0].name} (${sorted[0].progress}%)` : "-";
  els.leastResearcher.textContent = sorted.at(-1) ? `${sorted.at(-1).name} (${sorted.at(-1).progress}%)` : "-";
}

function renderTableHead() {
  els.tableHead.replaceChildren(...DISPLAY_COLUMNS.map(([, label]) => {
    const th = document.createElement("th");
    th.textContent = label;
    return th;
  }));
}

function createInput(row, key) {
  const input = document.createElement("input");
  input.className = "cell-input";
  input.value = valueOf(row, key);
  input.dataset.row = row.rowNumber;
  input.dataset.key = key;
  input.placeholder = LABELS[key];
  if (key === "email") input.type = "email";
  if (key === "phone") {
    input.inputMode = "numeric";
    input.maxLength = 10;
    input.pattern = "05[0-9]{8}";
  }
  input.addEventListener("input", () => handleFieldInput(row, key, input.value));
  return input;
}

function mapUrl(row) {
  const x = String(valueOf(row, "x")).trim();
  const y = String(valueOf(row, "y")).trim();
  if (!x || !y) return "";
  return `https://www.google.com/maps?q=${encodeURIComponent(`${y},${x}`)}`;
}

function phoneDigits(value) {
  const digits = String(value ?? "").replace(/\D/g, "");
  if (!digits) return "";
  if (digits.startsWith("966")) return digits;
  if (digits.startsWith("05") && digits.length === 10) return `966${digits.slice(1)}`;
  if (digits.startsWith("5") && digits.length === 9) return `966${digits}`;
  return digits;
}

function handleFieldInput(row, key, value) {
  const rowNumber = Number(row.rowNumber);
  const current = state.dirtyRows.get(rowNumber) || Object.fromEntries(EDITABLE_KEYS.map((item) => [item, valueOf(row, item)]));
  current[key] = value;
  state.dirtyRows.set(rowNumber, current);
  syncInputs(rowNumber, key, value);
  markDirty(rowNumber, true);
  updateSaveAllState();
  updateRowShellStatus(row);
  scheduleAutosave(row);
}

function syncInputs(rowNumber, key, value) {
  document.querySelectorAll(`[data-row="${rowNumber}"][data-key="${key}"]`).forEach((input) => {
    if (input.value !== value) input.value = value;
  });
}

function markDirty(rowNumber, isDirty) {
  document.querySelectorAll(`[data-row-shell="${rowNumber}"]`).forEach((node) => node.classList.toggle("is-dirty", isDirty));
}

function markSaving(rowNumber, isSaving) {
  document.querySelectorAll(`[data-row-shell="${rowNumber}"]`).forEach((node) => node.classList.toggle("is-saving", isSaving));
}

function updateRowShellStatus(row) {
  const complete = isEntered(row);
  document.querySelectorAll(`[data-row-shell="${row.rowNumber}"]`).forEach((node) => {
    node.classList.toggle("complete", complete);
    node.classList.toggle("incomplete", !complete);
    const badge = node.querySelector(".status-badge");
    if (badge) {
      badge.className = `status-badge ${complete ? "status-entered" : "status-missing"}`;
      badge.textContent = complete ? "مكتملة" : "غير مكتملة";
    }
  });
}

function updateSaveAllState() {
  const count = state.dirtyRows.size;
  els.saveAllButton.disabled = count === 0;
  els.saveAllButton.textContent = count ? `حفظ الكل (${count})` : "حفظ الكل";
}

function scheduleAutosave(row) {
  const rowNumber = Number(row.rowNumber);
  clearTimeout(state.autosaveTimers.get(rowNumber));
  const timer = setTimeout(() => {
    if (!state.dirtyRows.has(rowNumber)) return;
    saveRow(row, null, { silent: true, autosave: true });
  }, 1200);
  state.autosaveTimers.set(rowNumber, timer);
}

function renderTable() {
  els.tableBody.replaceChildren(...state.visibleRows.map((row) => {
    const tr = document.createElement("tr");
    tr.dataset.rowShell = row.rowNumber;
    tr.classList.toggle("is-dirty", state.dirtyRows.has(row.rowNumber));
    DISPLAY_COLUMNS.forEach(([key]) => {
      const td = document.createElement("td");
      if (EDITABLE_KEYS.includes(key)) {
        td.append(createInput(row, key));
      } else if (key === "map") {
        td.append(createMapLink(row));
      } else if (key === "status") {
        td.append(createStatusBadge(row));
      } else if (key === "actions") {
        const button = document.createElement("button");
        button.type = "button";
        button.className = "secondary-button save-button";
        button.textContent = "حفظ";
        button.addEventListener("click", () => saveRow(row, button));
        td.append(button);
      } else {
        td.textContent = valueOf(row, key) || "-";
        if (key === "commercial") td.className = "ltr-value";
      }
      tr.append(td);
    });
    return tr;
  }));
}

function createStatusBadge(row) {
  const badge = document.createElement("span");
  badge.className = `status-badge ${isEntered(row) ? "status-entered" : "status-missing"}`;
  badge.textContent = isEntered(row) ? "مكتملة" : "غير مكتملة";
  return badge;
}

function createMapLink(row) {
  const href = mapUrl(row);
  if (!href) return document.createTextNode("-");
  const link = document.createElement("a");
  link.className = "map-link";
  link.href = href;
  link.target = "_blank";
  link.rel = "noopener";
  link.textContent = "الموقع";
  return link;
}

function renderCards() {
  els.cardsGrid.replaceChildren(...state.visibleRows.map((row) => {
    const card = document.createElement("article");
    const entered = isEntered(row);
    card.className = `facility-card ${entered ? "complete" : "incomplete"}`;
    card.dataset.rowShell = row.rowNumber;
    card.classList.toggle("is-dirty", state.dirtyRows.has(row.rowNumber));

    const phone = valueOf(row, "phone");
    const phoneNumber = phoneDigits(phone);
    const actions = document.createElement("div");
    actions.className = "card-actions";
    actions.append(createMapLink(row));
    if (phoneNumber) {
      const call = document.createElement("a");
      call.className = "secondary-button card-action-button";
      call.href = `tel:${phoneNumber}`;
      call.textContent = "اتصال";
      const whatsapp = document.createElement("a");
      whatsapp.className = "secondary-button card-action-button";
      whatsapp.href = `https://wa.me/${phoneNumber}`;
      whatsapp.target = "_blank";
      whatsapp.rel = "noopener";
      whatsapp.textContent = "واتساب";
      actions.append(call, whatsapp);
    }

    const fields = document.createElement("div");
    fields.className = "card-fields";
    EDITABLE_KEYS.forEach((key) => {
      const group = document.createElement("label");
      group.textContent = LABELS[key];
      group.append(createInput(row, key));
      fields.append(group);
    });

    const save = document.createElement("button");
    save.type = "button";
    save.className = "primary-button card-save-button";
    save.textContent = "حفظ";
    save.addEventListener("click", () => saveRow(row, save));

    card.innerHTML = `
      <div class="facility-card-head">
        <div>
          <span class="facility-card-label">السجل التجاري: ${escapeHtml(valueOf(row, "commercial") || "-")}</span>
          <h3>${escapeHtml(valueOf(row, "facility") || "منشأة دون اسم")}</h3>
        </div>
      </div>
      <div class="card-info-grid">
        <div><span>مدير الحساب</span><strong>${escapeHtml(valueOf(row, "accountManager") || "-")}</strong></div>
        <div><span>المنطقة</span><strong>${escapeHtml(valueOf(row, "region") || "-")}</strong></div>
        <div><span>الحي</span><strong>${escapeHtml(valueOf(row, "district") || "-")}</strong></div>
        <div><span>الباحث</span><strong>${escapeHtml(valueOf(row, "researcher") || "-")}</strong></div>
      </div>
    `;
    card.querySelector(".facility-card-head").append(createStatusBadge(row));
    card.append(actions, fields, save);
    return card;
  }));
}

function render() {
  renderSummary();
  renderAdminStats();
  renderTableHead();
  renderTable();
  renderCards();
  els.emptyState.hidden = state.visibleRows.length !== 0;
}

function valuesForRow(row) {
  return state.dirtyRows.get(Number(row.rowNumber)) || Object.fromEntries(EDITABLE_KEYS.map((key) => [key, valueOf(row, key)]));
}

function validateSaveValues(values) {
  if (EDITABLE_KEYS.every((key) => !String(values[key] || "").trim())) {
    return "أدخل بيانات التواصل قبل الحفظ.";
  }
  const phone = String(values.phone || "").trim();
  if (phone && !/^05\d{8}$/.test(phone)) {
    return "رقم الجوال يجب أن يكون 10 أرقام ويبدأ بـ 05.";
  }
  return "";
}

async function saveRow(row, button, options = {}) {
  const values = valuesForRow(row);
  const validation = validateSaveValues(values);
  if (validation) {
    if (!options.silent) showToast(validation);
    return;
  }
  clearTimeout(state.autosaveTimers.get(Number(row.rowNumber)));
  const updates = Object.fromEntries(EDITABLE_KEYS.map((key) => [state.headerMap[key], values[key] || ""]));
  let originalText = "";
  if (button) {
    button.disabled = true;
    originalText = button.textContent;
    button.textContent = "جاري الحفظ...";
  }
  markSaving(row.rowNumber, true);
  try {
    await apiRequest({
      action: "update",
      rowNumber: row.rowNumber,
      commercialRecord: valueOf(row, "commercial"),
      facilityName: valueOf(row, "facility"),
      researcher: valueOf(row, "researcher"),
      updates,
    });
    EDITABLE_KEYS.forEach((key) => setValue(row, key, values[key] || ""));
    state.dirtyRows.delete(Number(row.rowNumber));
    markDirty(row.rowNumber, false);
    updateRowShellStatus(row);
    updateSaveAllState();
    showToast(options.autosave ? "تم الحفظ تلقائيًا" : "تم الحفظ بنجاح");
    applyFilters();
  } catch (error) {
    showToast(error.message || "تعذر الحفظ");
  } finally {
    markSaving(row.rowNumber, false);
    if (button) {
      button.disabled = false;
      button.textContent = originalText;
    }
  }
}

async function saveAllDirtyRows() {
  const dirtyNumbers = [...state.dirtyRows.keys()];
  if (!dirtyNumbers.length) return;
  els.saveAllButton.disabled = true;
  els.saveAllButton.textContent = "جاري حفظ الكل...";
  try {
    for (const rowNumber of dirtyNumbers) {
      const row = rowByNumber(rowNumber);
      if (!row) continue;
      const values = valuesForRow(row);
      const validation = validateSaveValues(values);
      if (validation) throw new Error(`الصف ${rowNumber}: ${validation}`);
      const updates = Object.fromEntries(EDITABLE_KEYS.map((key) => [state.headerMap[key], values[key] || ""]));
      clearTimeout(state.autosaveTimers.get(rowNumber));
      await apiRequest({ action: "update", rowNumber, updates });
      EDITABLE_KEYS.forEach((key) => setValue(row, key, values[key] || ""));
      state.dirtyRows.delete(rowNumber);
      markDirty(rowNumber, false);
    }
    showToast("تم حفظ جميع التعديلات بنجاح");
    applyFilters();
  } catch (error) {
    showToast(error.message || "تعذر حفظ جميع التعديلات");
  } finally {
    updateSaveAllState();
  }
}

function aggregateCount(rows, key) {
  const map = new Map();
  rows.forEach((row) => {
    const value = valueOf(row, key) || "غير محدد";
    map.set(value, (map.get(value) || 0) + 1);
  });
  return [...map.entries()].map(([label, count]) => [label, count]);
}

function ensureExcelLibrary() {
  if (!window.XLSX) {
    showToast("تعذر تحميل مكتبة Excel. تحقق من اتصال الإنترنت ثم حاول مرة أخرى.");
    return false;
  }
  return true;
}

function makeWorksheet(rows, columnWidths = []) {
  const sheet = XLSX.utils.aoa_to_sheet(rows);
  sheet["!cols"] = columnWidths.map((width) => ({ wch: width }));
  sheet["!autofilter"] = { ref: XLSX.utils.encode_range({ s: { r: 0, c: 0 }, e: { r: Math.max(rows.length - 1, 0), c: Math.max((rows[0] || []).length - 1, 0) } }) };
  return sheet;
}

function appendSheet(workbook, name, rows, widths) {
  const sheet = makeWorksheet(rows, widths);
  XLSX.utils.book_append_sheet(workbook, sheet, name.slice(0, 31));
}

function writeWorkbook(filename, workbook) {
  workbook.Workbook = { Views: [{ RTL: true }] };
  XLSX.writeFile(workbook, filename, { bookType: "xlsx", compression: true });
}

function fullDataRows() {
  const exportHeaders = [
    "السجل التجاري",
    "اسم المنشأة",
    "مدير الحساب",
    "الاسم",
    "المسمى الوظيفي",
    "البريد الإلكتروني",
    "رقم التواصل",
    "X",
    "Y",
    "المنطقة",
    "الحي",
    "اسم الباحث",
    "حالة الإدخال",
  ];
  const rows = state.rows.map((row) => [
    valueOf(row, "commercial"),
    valueOf(row, "facility"),
    valueOf(row, "accountManager"),
    valueOf(row, "name"),
    valueOf(row, "jobTitle"),
    valueOf(row, "email"),
    valueOf(row, "phone"),
    valueOf(row, "x"),
    valueOf(row, "y"),
    valueOf(row, "region"),
    valueOf(row, "district"),
    valueOf(row, "researcher"),
    isEntered(row) ? "مكتملة" : "غير مكتملة",
  ]);
  return [exportHeaders, ...rows];
}

function exportSummary() {
  if (!ensureExcelLibrary()) return;
  const total = computeStats(state.rows);
  const workbook = XLSX.utils.book_new();
  appendSheet(workbook, "الملخص", [
    ["تقرير إحصائي لمشروع رصد بيانات المنشآت"],
    ["تاريخ التصدير", new Date().toLocaleString("ar-SA")],
    [],
    ["إجمالي العينات", "المكتملة", "غير المكتملة", "نسبة الإنجاز"],
    [total.total, total.entered, total.missing, `${total.progress}%`],
  ], [26, 16, 16, 16]);
  appendSheet(workbook, "حسب الباحث", [
    ["الباحث", "عدد العينات", "مكتملة", "غير مكتملة", "نسبة الإنجاز"],
    ...researcherStats().map((item) => [item.name, item.total, item.entered, item.missing, `${item.progress}%`]),
  ], [28, 16, 16, 16, 16]);
  appendSheet(workbook, "حسب المنطقة", [
    ["المنطقة", "عدد العينات"],
    ...aggregateCount(state.rows, "region"),
  ], [30, 16]);
  appendSheet(workbook, "حسب الحي", [
    ["الحي", "عدد العينات"],
    ...aggregateCount(state.rows, "district"),
  ], [34, 16]);
  writeWorkbook("تقرير-إحصائي-رصد-المنشآت.xlsx", workbook);
}

function exportFull() {
  if (!ensureExcelLibrary()) return;
  const workbook = XLSX.utils.book_new();
  appendSheet(workbook, "جميع البيانات", fullDataRows(), [15, 46, 22, 20, 22, 28, 16, 16, 16, 22, 28, 22, 16]);
  appendSheet(workbook, "إحصائيات الباحثين", [
    ["الباحث", "عدد العينات", "مكتملة", "غير مكتملة", "نسبة الإنجاز"],
    ...researcherStats().map((item) => [item.name, item.total, item.entered, item.missing, `${item.progress}%`]),
  ], [28, 16, 16, 16, 16]);
  writeWorkbook("تقرير-تفصيلي-كامل-رصد-المنشآت.xlsx", workbook);
}

async function bootstrap() {
  els.researcherSelect.disabled = true;
  els.loginError.textContent = "";
  try {
    applyLoadedData(await loadData());
  } catch (error) {
    els.researcherSelect.replaceChildren(new Option("تعذر تحميل الباحثين", ""));
    els.loginError.textContent = error.message || "تعذر الاتصال بـ Apps Script.";
  }
}

function hasUnsavedChanges() {
  return state.dirtyRows.size > 0;
}

function confirmIfDirty() {
  return !hasUnsavedChanges() || window.confirm("توجد بيانات معدلة لم يتم حفظها. هل تريد المتابعة بدون حفظ؟");
}

function login() {
  const selected = els.researcherSelect.value;
  if (!selected) {
    els.loginError.textContent = "اختر اسم الباحث أو دخول المدير.";
    return;
  }
  const expectedPassword = selected === CONFIG.managerValue ? CONFIG.managerPassword : CONFIG.researcherPassword;
  if (els.password.value !== expectedPassword) {
    els.loginError.textContent = "الرقم السري غير صحيح.";
    els.password.value = "";
    els.password.focus();
    return;
  }
  state.isManager = selected === CONFIG.managerValue;
  state.currentUser = state.isManager ? "المدير" : selected;
  els.roleLabel.textContent = state.isManager ? "لوحة المدير" : "لوحة الباحث";
  els.welcomeLabel.textContent = state.isManager ? "دخول المدير" : `مرحبًا ${state.currentUser}`;
  els.heroTitle.textContent = state.isManager ? "لوحة شاملة لجميع الباحثين والبيانات" : "العينات المسندة لك فقط";
  els.heroDescription.textContent = state.isManager
    ? "يمكنك مشاهدة جميع المنشآت والإحصائيات والتصدير والتعديل."
    : "يمكنك فتح البطاقة، تعبئة بيانات التواصل، فتح الموقع، ثم الحفظ مباشرة.";
  els.adminStats.hidden = !state.isManager;
  els.researcherFilterGroup.hidden = !state.isManager;
  els.exportSummaryButton.hidden = !state.isManager;
  els.exportFullButton.hidden = !state.isManager;
  els.researcherFilter.value = "";
  els.loginView.hidden = true;
  els.appView.hidden = false;
  applyFilters();
}

function logout() {
  if (!confirmIfDirty()) return;
  state.currentUser = "";
  state.isManager = false;
  state.autosaveTimers.forEach((timer) => clearTimeout(timer));
  state.autosaveTimers.clear();
  state.dirtyRows.clear();
  updateSaveAllState();
  els.password.value = "";
  els.searchInput.value = "";
  els.researcherFilter.value = "";
  els.regionFilter.value = "";
  els.districtFilter.value = "";
  els.statusFilter.value = "";
  els.appView.hidden = true;
  els.loginView.hidden = false;
}

els.loginForm.addEventListener("submit", (event) => {
  event.preventDefault();
  login();
});

els.togglePassword.addEventListener("click", () => {
  const showing = els.password.type === "text";
  els.password.type = showing ? "password" : "text";
  els.togglePassword.setAttribute("aria-label", showing ? "إظهار الرقم السري" : "إخفاء الرقم السري");
});

[els.searchInput, els.researcherFilter, els.regionFilter, els.districtFilter, els.statusFilter].forEach((control) => {
  control.addEventListener("input", applyFilters);
  control.addEventListener("change", applyFilters);
});

document.querySelectorAll(".quick-filter").forEach((button) => {
  button.addEventListener("click", () => {
    document.querySelectorAll(".quick-filter").forEach((item) => item.classList.remove("active"));
    button.classList.add("active");
    els.statusFilter.value = button.dataset.status || "";
    applyFilters();
  });
});

els.exportSummaryButton.addEventListener("click", exportSummary);
els.exportFullButton.addEventListener("click", exportFull);
els.saveAllButton.addEventListener("click", saveAllDirtyRows);
els.logoutButton.addEventListener("click", logout);
els.refreshButton.addEventListener("click", async () => {
  if (!confirmIfDirty()) return;
  els.refreshButton.disabled = true;
  try {
    applyLoadedData(await loadData());
    applyFilters();
    showToast("تم تحديث البيانات من Google Sheet");
  } catch (error) {
    showToast(error.message || "تعذر تحديث البيانات");
  } finally {
    els.refreshButton.disabled = false;
  }
});

window.addEventListener("beforeunload", (event) => {
  if (!hasUnsavedChanges()) return;
  event.preventDefault();
  event.returnValue = "";
});

bootstrap();