/**
 * MASTER API GATEWAY
 */
function doGet() {
  try {
    const data = {
      lastUpdated: new Date().toLocaleString(),
      projects: getProjectData(),
      announcements: getAnnouncements(),
      calendar: getCalendarData()
    };
    return ContentService.createTextOutput(JSON.stringify(data))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (e) {
    return ContentService.createTextOutput(JSON.stringify({ error: e.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

/**
 * PAGE 1 & 2: Project Retrieval & Filtering
 */
function getProjectData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const active = [];
  const completed = [];
  const seen = new Set(); 

  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (["Announcements", "Calendar"].includes(sheetName)) return;
    
    const rows = sheet.getDataRange().getValues();
    if (rows.length < 2) return;

    rows.slice(1).forEach(row => {
      const name = row[2]; // Column C
      if (!name || seen.has(name)) return;
      seen.add(name);

      const completionVal = row[6]; // Column G
      let progress = 0;
      let isDone = false;

      // Logic: Date object = Done, Number (0-99) = Progress, 100 = Done
      if (completionVal instanceof Date) {
        isDone = true;
      } else if (typeof completionVal === 'number') {
        progress = Math.min(Math.max(completionVal, 0), 100);
        if (progress >= 100) isDone = true;
      }

      const p = {
        name: name,
        type: row[1] || "General",
        owner: row[4] || "Unassigned",
        due: row[5] instanceof Date ? Utilities.formatDate(row[5], Session.getScriptTimeZone(), "MMM dd") : (row[5] || "TBD"),
        progress: progress,
        isOverdue: (row[5] instanceof Date && row[5] < new Date() && !isDone)
      };

      if (isDone) completed.push(p);
      else active.push(p);
    });
  });

  return { active: active, completedCount: completed.length };
}

/**
 * PAGE 3: Announcements
 */
function getAnnouncements() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Announcements");
    if (!sheet) return [];
    const rows = sheet.getDataRange().getValues().slice(1);
    return rows.map(r => ({
      date: r[0] instanceof Date ? Utilities.formatDate(r[0], Session.getScriptTimeZone(), "MMM dd") : r[0],
      title: r[1] || "",
      content: r[2] || ""
    })).filter(a => a.title !== "");
  } catch(e) { return []; }
}

/**
 * PAGE 4: Google Calendar
 */
function getCalendarData() {
  try {
    const now = new Date();
    const endOfMonth = new Date(now.getFullYear(), now.getMonth() + 1, 0);
    const events = CalendarApp.getDefaultCalendar().getEvents(now, endOfMonth);
    
    return events.map(e => ({
      title: e.getTitle(),
      start: Utilities.formatDate(e.getStartTime(), Session.getScriptTimeZone(), "MMM dd"),
      isBirthday: e.getTitle().toLowerCase().includes("birthday")
    })).slice(0, 6); 
  } catch(e) { return []; }
}