script.js

Office.onReady(() => {
  // This function runs when the Office host is ready.

  // --- GLOBAL STATE & CONSTANTS ---
  let tableData = {};
  const MONTH_NAMES = ["Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre"];
  const ATTENDANCE_VALUES = ["A", "D", "V", "I", "P", "E", "F"];

  const COLOR_MAP = {
    "E": "#c55812",
    "A": "#a8ce8c",
    "V": "#fbfb00",
    "D": "#04aeef",
    "I": "#f8cbad",
    "P": "#c8c8c8",
    "F": "#808080"
  };

  // --- DOM ELEMENTS ---
  const processButton = document.getElementById("process-button");
  const updateButton = document.getElementById("update-button");
  const monthSelect = document.getElementById("month-select");
  const dayInput = document.getElementById("day-input");
  const prevDayBtn = document.getElementById("prev-day-btn");
  const nextDayBtn = document.getElementById("next-day-btn");
  const nameFilterInput = document.getElementById("name-filter");

  // --- INITIALIZE ---
  initializeDateControls();

  // --- EVENT LISTENERS ---
  processButton.addEventListener("click", processSheet);
  updateButton.addEventListener("click", updateAllAttendance);
  monthSelect.addEventListener("change", handleDateChange);
  dayInput.addEventListener("change", handleDateChange);
  prevDayBtn.addEventListener("click", () => handleDayArrowClick(-1));
  nextDayBtn.addEventListener("click", () => handleDayArrowClick(1));
  nameFilterInput.addEventListener("input", renderPeopleList);

  function initializeDateControls() {
    MONTH_NAMES.forEach((name, index) => {
      monthSelect.add(new Option(name, index));
    });
    const today = new Date();
    monthSelect.value = today.getMonth();
    dayInput.value = today.getDate();
  }

  function handleDateChange() {
    processSheet();
  }

  function handleDayArrowClick(direction) {
    const currentMonth = parseInt(monthSelect.value, 10);
    const currentDay = parseInt(dayInput.value, 10);
    const currentDate = new Date(new Date().getFullYear(), currentMonth, currentDay);
    currentDate.setDate(currentDate.getDate() + direction);

    monthSelect.value = currentDate.getMonth();
    dayInput.value = currentDate.getDate();

    handleDateChange();
  }

  async function processSheet() {
    const messageContainer = document.getElementById("message-container");
    messageContainer.innerHTML = '';
    processButton.disabled = true;

    let allValues = [];
    const CHUNK_SIZE = 1000;
    let currentRow = 0;

    // --- CHUNK 1: Read data from the sheet ---
    try {
      console.clear();

      while (true) {
        processButton.textContent = `Leyendo Fila ${currentRow + 1}...`;
        let chunkValues;
        await Excel.run(async (context) => {
          const sheet = context.workbook.worksheets.getActiveWorksheet();
          const range = sheet.getRangeByIndexes(currentRow, 0, CHUNK_SIZE, 370);
          range.load("values");
          await context.sync();
          chunkValues = range.values;
        });

        const isChunkEmpty = chunkValues.every(row => row.every(cell => cell === "" || cell === null));
        if (isChunkEmpty && allValues.length > 0) break;
        allValues.push(...chunkValues);
        currentRow += CHUNK_SIZE;
        if (currentRow > 20000) throw new Error("Se detuvo la lectura en la fila 20,000.");
      }
    } catch (error) {
      handleError(error, "CHUNK 1: Reading Sheet Data");
      processButton.disabled = false;
      processButton.textContent = "Leer y Procesar Hoja";
      return;
    }

    processButton.textContent = "Procesando Datos...";
    const values = allValues;

    // --- CHUNK 2: Find headers and key columns ---
    try {
      document.getElementById("attendance-ui").style.display = 'none';
      tableData = {
        coordinators: {},
        dateColumnIndex: -1,
        headerRowIndex: -1,
        nameColIndex: -1,
        coordColIndex: -1,
        rawRows: values
      };

      const COORDINATOR_HEADER_TEXT = "COORDINADOR DE PERSONAL";
      const NAME_HEADER_TEXT = "NOMBRE";
      const DATE_HEADER_ROW_INDEX = 5;

      const mainHeaderRowIndex = values.findIndex((row) =>
        row.some((cell) => typeof cell === "string" && cell.toUpperCase().includes(COORDINATOR_HEADER_TEXT))
      );
      if (mainHeaderRowIndex === -1) throw new Error("No se pudo encontrar la fila de encabezado de 'COORDINADOR'.");
      tableData.headerRowIndex = mainHeaderRowIndex;
      const mainHeaderRow = values[mainHeaderRowIndex];

      if (values.length <= DATE_HEADER_ROW_INDEX) throw new Error("La hoja no tiene suficientes filas para encontrar la fila de fecha (Fila 6).");
      const dateHeaderRow = values[DATE_HEADER_ROW_INDEX];

      tableData.coordColIndex = mainHeaderRow.findIndex(
        (cell) => typeof cell === "string" && cell.toUpperCase().includes(COORDINATOR_HEADER_TEXT)
      );

      const nombreIndices = [];
      mainHeaderRow.forEach((cell, index) => {
        if (typeof cell === "string" && cell.toUpperCase() === NAME_HEADER_TEXT) {
          nombreIndices.push(index);
        }
      });
      if (nombreIndices.length === 0) throw new Error('No se encontró ninguna columna "Nombre".');

      tableData.nameColIndex = nombreIndices.length === 1 ? nombreIndices[0] : (() => {
        let bestIndex = -1, maxAvgLength = -1;
        nombreIndices.forEach(index => {
          let totalLength = 0, dataRowCount = 0;
          for (let i = mainHeaderRowIndex + 1; i < Math.min(values.length, mainHeaderRowIndex + 21); i++) {
            const cellContent = values[i][index] ?.toString().trim();
            if (cellContent) totalLength += cellContent.length;
            dataRowCount++;
          }
          const avgLength = dataRowCount > 0 ? totalLength / dataRowCount : 0;
          if (avgLength > maxAvgLength) {
            maxAvgLength = avgLength;
            bestIndex = index;
          }
        });
        return bestIndex;
      })();

      if (tableData.coordColIndex === -1 || tableData.nameColIndex === -1) {
        throw new Error("No se encontraron las columnas de Coordinador o Nombre.");
      }

      const selected_month = parseInt(monthSelect.value, 10);
      const selected_day = parseInt(dayInput.value, 10);

      tableData.dateColumnIndex = dateHeaderRow.findIndex((cell) => {
        let cellDate;
        if (typeof cell === "number" && cell > 25569) {
          const utcMilliseconds = (cell - 25569) * 86400 * 1000;
          cellDate = new Date(utcMilliseconds);
          return cellDate.getUTCMonth() === selected_month && cellDate.getUTCDate() === selected_day;
        }
        if (typeof cell === 'string' && cell.trim().length > 4) {
          try {
            const dateParts = cell.split('/');
            if (dateParts.length === 3) {
              const month = parseInt(dateParts[0], 10) - 1;
              const day = parseInt(dateParts[1], 10);
              return month === selected_month && day === selected_day;
            }
          } catch (e) { }
        }
        return false;
      });

      if (tableData.dateColumnIndex === -1) {
        messageContainer.innerHTML = `<div class="message error">ATENCIÓN: La columna para la fecha seleccionada no existe en la Fila 6.</div>`;
        document.getElementById("update-button").disabled = true;
      } else {
        document.getElementById("update-button").disabled = false;
      }

    } catch (error) {
      handleError(error, "CHUNK 2: Finding Headers");
      processButton.disabled = false;
      processButton.textContent = "Leer y Procesar Hoja";
      return;
    }

    // --- CHUNK 3: Parse People Data (with corrected logic) ---
    try {
      for (let i = tableData.headerRowIndex + 1; i < values.length; i++) {
        const row = values[i];
        if (!row) continue;

        const nameInRow = row[tableData.nameColIndex] ?.toString().trim();

        // If there's no name, it's a blank row, so skip it.
        if (!nameInRow) {
          continue;
        }

        // Check for a coordinator in the current row. If blank, use the default group.
        let coordInRow = row[tableData.coordColIndex] ?.toString().trim() || "Sin Coordinador";

        if (!tableData.coordinators[coordInRow]) {
          tableData.coordinators[coordInRow] = [];
        }

        let currentState = "";
        if (tableData.dateColumnIndex !== -1) {
          currentState = row[tableData.dateColumnIndex] ?.toString().trim() || "";
        }
        tableData.coordinators[coordInRow].push({ name: nameInRow, currentState, rowIndex: i });
      }
    } catch (error) {
      handleError(error, "CHUNK 3: Parsing People Data");
      return;
    }

    // --- CHUNK 4: Build the UI ---
    try {
      buildUI();
    } catch (error) {
      handleError(error, "CHUNK 4: Building UI");
    } finally {
      processButton.disabled = false;
      processButton.textContent = "Leer y Procesar Hoja";
    }
  }

  function buildUI() {
    const coordinatorSelect = document.getElementById("coordinator-select");
    coordinatorSelect.innerHTML = '';
    const DEFAULT_COORDINATOR = "Nelly";
    const sortedCoordinators = Object.keys(tableData.coordinators).sort((a, b) => {
      if (a === "Sin Coordinador") return 1;
      if (b === "Sin Coordinador") return -1;
      return a.localeCompare(b);
    });

    sortedCoordinators.forEach((name) => {
      coordinatorSelect.add(new Option(name, name));
    });

    if (tableData.coordinators[DEFAULT_COORDINATOR]) {
      coordinatorSelect.value = DEFAULT_COORDINATOR;
    }
    coordinatorSelect.addEventListener("change", () => {
      document.getElementById('name-filter').value = '';
      renderPeopleList();
    });
    renderPeopleList();
    document.getElementById("attendance-ui").style.display = 'block';
  }

  function renderPeopleList() {
    const peopleList = document.getElementById("people-list");
    peopleList.innerHTML = '';
    const selectedCoordinator = document.getElementById("coordinator-select").value;
    const filterText = document.getElementById("name-filter").value.toLowerCase();

    let people = tableData.coordinators[selectedCoordinator] || [];

    if (filterText) {
      people = people.filter(person => person.name.toLowerCase().includes(filterText));
    }

    people.forEach((person) => {
      const personRow = document.createElement("tr");

      const currentState = person.currentState.toUpperCase();
      const defaultValue = ATTENDANCE_VALUES.includes(currentState) ? currentState : "A";

      const optionsHTML = ATTENDANCE_VALUES.map(
        (s) => `<option value="${s}" ${s === defaultValue ? "selected" : ""}>${s}</option>`
      ).join("");

      personRow.innerHTML = `
              <td>
                <a href="#" class="person-name-link" data-row-index="${person.rowIndex}">${person.name}</a>
                <span style="color:#718096;font-size:12px;margin-left:8px;">(Valor actual: ${person.currentState || "N/A"})</span>
              </td>
              <td>
                <div class="actions-container">
                  <select data-row-index="${person.rowIndex}" style="padding:5px;border-radius:4px;border:1px solid #ccc;">
                      ${optionsHTML}
                  </select>
                  <button class="apply-single-btn" title="Aplicar asistencia seleccionada">Aplicar</button>
                </div>
              </td>
          `;
      personRow.querySelector(".person-name-link").addEventListener("click", goToCell);
      personRow.querySelector(".apply-single-btn").addEventListener("click", applySingleUpdate);
      peopleList.appendChild(personRow);
    });
  }

  async function goToCell(event) {
    event.preventDefault();
    const rowIndex = parseInt(event.target.dataset.rowIndex, 10);
    try {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const cell = sheet.getCell(rowIndex, tableData.nameColIndex);
        cell.select();
        await context.sync();
      });
    } catch (error) {
      handleError(error, `Navigating to row ${rowIndex + 1}`);
    }
  }

  async function applySingleUpdate(event) {
    const button = event.target;
    const select = button.previousElementSibling;
    const rowIndex = parseInt(select.dataset.rowIndex, 10);
    const newState = select.value;

    const originalText = button.textContent;
    button.textContent = '...';
    button.disabled = true;

    try {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const cell = sheet.getCell(rowIndex, tableData.dateColumnIndex);

        cell.select();
        await context.sync();

        cell.values = [[newState]];
        cell.numberFormat = [["General"]];

        if (COLOR_MAP[newState]) {
          cell.format.fill.color = COLOR_MAP[newState];
        } else {
          cell.format.fill.clear();
        }

        await context.sync();
      });

      const personData = tableData.coordinators[document.getElementById("coordinator-select").value].find(p => p.rowIndex === rowIndex);
      if (personData) personData.currentState = newState;
      renderPeopleList();

    } catch (error) {
      handleError(error, `Updating row ${rowIndex + 1}`);
    } finally {
      const rowElement = document.querySelector(`select[data-row-index='${rowIndex}']`);
      if (rowElement && rowElement.nextElementSibling) {
        const newButton = rowElement.nextElementSibling;
        newButton.textContent = originalText;
        newButton.disabled = false;
      }
    }
  }

  async function updateAllAttendance() {
    const messageContainer = document.getElementById("message-container");
    const updateButton = document.getElementById("update-button");
    messageContainer.innerHTML = '';
    updateButton.disabled = true;
    updateButton.textContent = "Actualizando...";

    try {
      const selects = Array.from(document.querySelectorAll("#people-list select"));
      let updatesCount = 0;

      for (const select of selects) {
        const rowIndex = parseInt(select.dataset.rowIndex, 10);
        const newState = select.value;

        await Excel.run(async (context) => {
          const sheet = context.workbook.worksheets.getActiveWorksheet();
          const cell = sheet.getCell(rowIndex, tableData.dateColumnIndex);

          cell.select();
          await context.sync();

          cell.values = [[newState]];
          cell.numberFormat = [["General"]];

          if (COLOR_MAP[newState]) {
            cell.format.fill.color = COLOR_MAP[newState];
          } else {
            cell.format.fill.clear();
          }

          await context.sync();
        });
        updatesCount++;
        updateButton.textContent = `Actualizando... (${updatesCount}/${selects.length})`;

        await new Promise(resolve => setTimeout(resolve, 50));
      }

      messageContainer.innerHTML = `<div class="message success">${updatesCount} registros actualizados directamente en la hoja.</div>`;

      updateButton.textContent = "Actualizar Asistencias (Todo)";
      updateButton.disabled = false;
      await processSheet();

    } catch (error) {
      handleError(error, 'CHUNK 5: Updating Sheet');
      updateButton.disabled = false;
      updateButton.textContent = "Actualizar Asistencias (Todo)";
    }
  }

  function handleError(error, stage = "Unknown Stage") {
    console.error(`Error during ${stage}:`, error);
    const messageContainer = document.getElementById("message-container");
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug Info:", error.debugInfo);
      messageContainer.innerHTML = `<div class="message error"><b>Error en: ${stage}</b><br>Office.js: ${error.message} (Code: ${error.code})</div>`;
    } else {
      messageContainer.innerHTML = `<div class="message error"><b>Error en: ${stage}</b><br>Unexpected: ${error.message}</div>`;
    }
  }
});