수강료·재등록, 오늘 챙길 것

/**
 * Fee & re-enrollment reminder — minimal teaching version.
 * Roster sheet (dates) -> daily scan -> colored rows + "today" list. A person decides what to do.
 *
 * Rules this file enforces:
 *  1. Columns are found by header name, never by letter.
 *  2. Nothing is sent to parents. The script only colors rows and writes a to-do sheet the owner reads.
 *  3. Exactly one daily trigger exists at a time; turning it on replaces any older one.
 */

var SHEET_ROSTER = '원생';
var SHEET_TODO = '오늘 할 일';
var SHEET_NAMES = '학생명단';
var ROSTER_HEADERS = ['학생ID', '반', '납부예정일', '수강종료일', '메모'];
var NAMES_HEADERS = ['학생ID', '이름'];
var TODO_HEADERS = ['구분', '학생', '반', '날짜', '남은 일수', '메모'];
var HANDLER = 'scanAndMark';
var RUN_HOUR = 7;

var RULES = {
  dueSoonDays: 7,   // payment due within this many days -> yellow
  endSoonDays: 14   // enrollment ends within this many days -> orange
};
var COLORS = { red: '#f8cbc4', yellow: '#fff2a8', orange: '#ffd8a8' };
var LABELS = { red: '납부 지남', yellow: '납부 예정', orange: '재등록 상담' };

// ---------- Menu (simple trigger: menu only) ----------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('오늘 챙길 것')
    .addItem('① 시트 준비', 'setupSheets')
    .addItem('② 지금 점검', 'scanAndMark')
    .addSeparator()
    .addItem('③ 매일 아침 점검 켜기', 'enableDaily')
    .addItem('④ 매일 아침 점검 끄기', 'disableDaily')
    .addToUi();
}

// ---------- Pure helpers (no Apps Script services; testable in Node) ----------

function headerMap(headerRow) {
  var map = {};
  for (var i = 0; i < headerRow.length; i++) {
    var key = String(headerRow[i]).trim();
    if (key) map[key] = i;
  }
  return map;
}

function missingHeaders(map, required) {
  return required.filter(function (h) { return !(h in map); });
}

/** Accepts a Date, a yyyy-mm-dd / yyyy.mm.dd string, or blank. Returns a Date at local midnight, or null. */
function toDate(value) {
  if (value === '' || value === null || value === undefined) return null;
  var d;
  if (Object.prototype.toString.call(value) === '[object Date]') {
    d = new Date(value.getTime());
  } else {
    var m = String(value).trim().match(/^(\d{4})[.\-\/](\d{1,2})[.\-\/](\d{1,2})/);
    if (!m) return null;
    d = new Date(Number(m[1]), Number(m[2]) - 1, Number(m[3]));
  }
  if (isNaN(d.getTime())) return null;
  d.setHours(0, 0, 0, 0);
  return d;
}

/** Whole days from today to the date. Negative means the date has passed. */
function daysUntil(today, date) {
  var t = toDate(today), d = toDate(date);
  if (!t || !d) return null;
  return Math.round((d.getTime() - t.getTime()) / 86400000);
}

/**
 * Decides the row's color and label. Payment rules win over enrollment rules,
 * because an unpaid fee is the more urgent conversation.
 */
function classify(row, today, rules) {
  rules = rules || RULES;
  var due = daysUntil(today, row.dueDate);
  var end = daysUntil(today, row.endDate);
  if (due !== null && due < 0) return { tone: 'red', label: LABELS.red, days: due, date: toDate(row.dueDate) };
  if (due !== null && due <= rules.dueSoonDays) return { tone: 'yellow', label: LABELS.yellow, days: due, date: toDate(row.dueDate) };
  if (end !== null && end >= 0 && end <= rules.endSoonDays) return { tone: 'orange', label: LABELS.orange, days: end, date: toDate(row.endDate) };
  return { tone: null, label: '', days: null, date: null };
}

/** Most urgent first: overdue, then fewest days remaining, then student id. Does not mutate. */
function sortTodo(items) {
  var rank = { red: 0, yellow: 1, orange: 2 };
  return items.slice().sort(function (a, b) {
    if (rank[a.tone] !== rank[b.tone]) return rank[a.tone] - rank[b.tone];
    if (a.days !== b.days) return a.days - b.days;
    return String(a.studentId).localeCompare(String(b.studentId));
  });
}

// ---------- ① Sheets ----------

function setupSheets() {
  var ss = SpreadsheetApp.getActive();
  ensureSheet_(ss, SHEET_ROSTER, ROSTER_HEADERS);
  ensureSheet_(ss, SHEET_NAMES, NAMES_HEADERS);
  ensureSheet_(ss, SHEET_TODO, TODO_HEADERS);
  SpreadsheetApp.getUi().alert('시트 준비가 끝났습니다. 이미 있던 열과 내용은 건드리지 않았습니다.');
}

/** Idempotent: creates the sheet if absent and appends only the headers that are missing. */
function ensureSheet_(ss, name, headers) {
  var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  var lastCol = Math.max(sheet.getLastColumn(), 1);
  var existing = headerMap(sheet.getRange(1, 1, 1, lastCol).getValues()[0]);
  var toAdd = missingHeaders(existing, headers);
  if (toAdd.length) {
    var start = Object.keys(existing).length ? sheet.getLastColumn() + 1 : 1;
    sheet.getRange(1, start, 1, toAdd.length).setValues([toAdd]).setFontWeight('bold');
    sheet.setFrozenRows(1);
  }
  return sheet;
}

/** { studentId: name } from the names sheet. Names appear only on the owner-facing to-do sheet. */
function readNames_() {
  var sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAMES);
  var byId = {};
  if (!sheet || sheet.getLastRow() < 2) return byId;
  var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  var map = headerMap(data[0]);
  for (var r = 1; r < data.length; r++) {
    var id = String(data[r][map['학생ID']]).trim();
    if (id) byId[id] = String(data[r][map['이름']]).trim();
  }
  return byId;
}

// ---------- ② Scan: color rows, rebuild the to-do sheet ----------

function scanAndMark() {
  var ss = SpreadsheetApp.getActive();
  var roster = ss.getSheetByName(SHEET_ROSTER);
  var todo = ss.getSheetByName(SHEET_TODO);
  if (!roster || !todo) throw new Error('메뉴 ① 시트 준비를 먼저 실행하세요.');

  var lastRow = roster.getLastRow(), lastCol = roster.getLastColumn();
  var map = headerMap(roster.getRange(1, 1, 1, lastCol).getValues()[0]);
  var missing = missingHeaders(map, ROSTER_HEADERS);
  if (missing.length) throw new Error('빠진 열이 있습니다: ' + missing.join(', ') + ' — 메뉴 ①을 먼저 실행하세요.');

  var names = readNames_();
  var tz = Session.getScriptTimeZone();
  var today = toDate(Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'));
  var items = [];
  var colors = [];

  if (lastRow >= 2) {
    var values = roster.getRange(2, 1, lastRow - 1, lastCol).getValues();
    for (var i = 0; i < values.length; i++) {
      var v = values[i];
      var row = {
        studentId: String(v[map['학생ID']]).trim(),
        className: String(v[map['반']]).trim(),
        dueDate: v[map['납부예정일']],
        endDate: v[map['수강종료일']],
        memo: String(v[map['메모']]).trim()
      };
      var c = row.studentId ? classify(row, today) : { tone: null };
      var fill = c.tone ? COLORS[c.tone] : null;
      var rowColors = [];
      for (var k = 0; k < lastCol; k++) rowColors.push(fill);
      colors.push(rowColors);
      if (c.tone) {
        items.push({
          tone: c.tone, label: c.label, days: c.days, date: c.date,
          studentId: row.studentId, name: names[row.studentId] || row.studentId,
          className: row.className, memo: row.memo
        });
      }
    }
    roster.getRange(2, 1, lastRow - 1, lastCol).setBackgrounds(colors);
  }

  var sorted = sortTodo(items);
  if (todo.getLastRow() > 1) {
    todo.getRange(2, 1, todo.getLastRow() - 1, todo.getLastColumn()).clearContent().setBackground(null);
  }
  if (sorted.length) {
    var out = sorted.map(function (it) {
      return [it.label, it.name, it.className, Utilities.formatDate(it.date, tz, 'yyyy-MM-dd'), it.days, it.memo];
    });
    var block = todo.getRange(2, 1, out.length, TODO_HEADERS.length);
    block.setValues(out);
    block.setBackgrounds(sorted.map(function (it) {
      return TODO_HEADERS.map(function () { return COLORS[it.tone]; });
    }));
  }
  todo.getRange(1, TODO_HEADERS.length + 1).setValue('점검 ' + Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm'));
}

// ---------- ③ ④ Daily trigger: never more than one ----------

function enableDaily() {
  deleteOurTriggers_();
  ScriptApp.newTrigger(HANDLER).timeBased().everyDays(1).atHour(RUN_HOUR).create();
  SpreadsheetApp.getUi().alert('매일 아침 ' + RUN_HOUR + '시에서 ' + (RUN_HOUR + 1) + '시 사이에 점검합니다. 시간대: ' + Session.getScriptTimeZone());
}

function disableDaily() {
  var n = deleteOurTriggers_();
  SpreadsheetApp.getUi().alert(n ? '매일 점검을 껐습니다.' : '켜져 있는 점검이 없습니다.');
}

/** Removes every trigger that points at scanAndMark. Returns how many were removed. */
function deleteOurTriggers_() {
  var n = 0;
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === HANDLER) { ScriptApp.deleteTrigger(t); n++; }
  });
  return n;
}