구글시트 KPI 자동 집계 방법: 야간 트리거로 이력 쌓기 (완성 코드 포함)

// 이 편 전용 KPI 이력 설정 상수
const KPI_HISTORY_CONFIG = {                        // → KPI 이력 설정 묶음
  TZ: 'America/New_York',                           // → 시간대 고정 (전체 시리즈와 동일)
  SHEET_NAME: 'KPI_HISTORY',                        // → 이력 시트 이름
  HEADERS: [                                        // → 헤더 목록
    '날짜',                                         // → YYYY-MM-DD
    '총예약건수',                                   // → 예시 필드
    '정시도착건수',                                 // → 예시 필드
    '정시도착률',                                   // → 예시 필드
    '평균체류시간분',                               // → 예시 필드
    '지연건수',                                     // → 예시 필드
    '지연비율'                                      // → 예시 필드
  ],
  MAX_DAYS: 365                                     // → 이력 보관 일수(예시: 1년)
};

function KPI_getHistorySheet_() {                             // → KPI 이력 시트 반환
  const ss = SpreadsheetApp.getActive();                      // → 현재 스프레드시트
  const sheet = getOrCreateSheet_(ss, KPI_HISTORY_CONFIG.SHEET_NAME); // → 시트 찾거나 생성

  const headerRange = sheet.getRange(
    1, 1, 1, KPI_HISTORY_CONFIG.HEADERS.length                // → 1행 헤더 범위
  );
  const currentValues = headerRange.getValues()[0];           // → 기존 헤더 읽기

  const needUpdate = currentValues.some(
    (v, i) => v !== KPI_HISTORY_CONFIG.HEADERS[i]             // → 원하는 헤더와 비교
  );

  if (needUpdate) {                                           // → 헤더가 다르면
    headerRange.setValues([KPI_HISTORY_CONFIG.HEADERS]);      // → 헤더 새로 쓰기
  }
  return sheet;                                               // → 시트 반환
}

function KPI_testEnsureHistorySheet_() {                      // → 이력 시트 테스트 생성
  const sheet = KPI_getHistorySheet_();                       // → 시트 준비
  Logger.log('KPI_HISTORY 시트 이름: ' + sheet.getName());     // → 이름 로그로 확인
}

/**
 * 'YYYY-MM-DD' 문자열을 엄격 검증 후 Date 객체로 반환
 * - 형식 검증: 정규식
 * - 존재 여부 검증: Date 객체로 변환 후 연/월/일 재비교
 */
function KPI_parseYmdStrict_(ymd) {
  if (!ymd || typeof ymd !== 'string') {
    throw new Error('유효한 날짜 문자열(YYYY-MM-DD)이 필요합니다: ' + ymd);
  }

  const parts = ymd.match(/^(\d{4})-(\d{2})-(\d{2})$/);       // → 형식 검증
  if (!parts) {
    throw new Error('YYYY-MM-DD 형식이어야 합니다: ' + ymd);
  }

  const [, y, m, d] = parts;                                 // → 캡쳐 그룹 구조분해
  // 문자열을 new Date() 에 넣지 않는다. 'T00:00:00' 은 **현지 시각**으로 읽히는데
  // 그것을 getUTC* 로 되읽으면 시간대에 따라 하루가 어긋난다. 숫자로 바로 만든다.
  const dateObj = new Date(Number(y), Number(m) - 1, Number(d));  // → 현지 달력 자정

  if (!Number.isFinite(dateObj.getTime())) {
    throw new Error('잘못된 날짜 값입니다: ' + ymd);
  }

  const fy = dateObj.getFullYear();
  const fm = dateObj.getMonth() + 1;                          // JS 월: 0~11 → +1
  const fd = dateObj.getDate();

  if (fy !== parseInt(y, 10) ||
      fm !== parseInt(m, 10) ||
      fd !== parseInt(d, 10)) {
    throw new Error('존재하지 않는 날짜입니다: ' + ymd);    // → 2월 30일 등 방지
  }

  return dateObj;
}

function KPI_saveHistoryForDate_(ymd) {                       // → 특정 날짜 이력 저장
  // 날짜 형식·존재 여부 엄격 검증
  const dateObj = KPI_parseYmdStrict_(ymd);                   // → 검증 통과 시 Date 반환

  const lock = LockService.getScriptLock();                   // → 스크립트 잠금
  lock.waitLock(30000);                                       // → 최대 30초 대기

  try {
    const kpi = computeKpiForDate_(dateObj);                  // → 앞 편 함수로 KPI 계산
    if (!kpi || typeof kpi !== 'object') {
      throw new Error('KPI 계산 결과가 올바르지 않습니다');
    }

    // 필수 KPI 키와 숫자 유효성 검사 (앞 편 구조에 맞게)
    const requiredKeys = [
      'totalAppts', 'onTimeAppts', 'onTimeRate',
      'avgDwTimeMinutes', 'lateAppts', 'lateRate'
    ];
    requiredKeys.forEach(key => {
      if (!(key in kpi)) {
        throw new Error('KPI 필드 누락: ' + key);
      }
      const val = kpi[key];
      if (!Number.isFinite(val)) {
        throw new Error('KPI 값이 숫자가 아닙니다: ' + key + ' = ' + val);
      }
    });

    const sheet = KPI_getHistorySheet_();                     // → 이력 시트 가져오기
    const lastRow = sheet.getLastRow();
    let targetRow = 0;                                        // → 쓸 행 번호

    if (lastRow > 1) {                                        // → 데이터가 있을 때
      const range = sheet.getRange(2, 1, lastRow - 1, 1);     // → 2행부터 날짜 열
      const values = range.getValues();

      for (let i = 0; i < values.length; i++) {
        const cellVal = values[i][0];
        if (!cellVal) continue;

        const cellYmd = (cellVal instanceof Date)
          ? Utilities.formatDate(cellVal, KPI_HISTORY_CONFIG.TZ, 'yyyy-MM-dd')
          : String(cellVal);

        if (cellYmd === ymd) {                                // → 같은 날짜 발견
          targetRow = i + 2;                                  // → 실제 행 번호(헤더 + 오프셋)
          break;
        }
      }
    }

    if (!targetRow) {                                         // → 기존 날짜가 없으면
      targetRow = lastRow >= 1 ? lastRow + 1 : 2;             // → 새 행 번호
    }

    const rowValues = [
      ymd,                                                    // 날짜
      kpi.totalAppts,                                         // 총예약건수
      kpi.onTimeAppts,                                        // 정시도착건수
      kpi.onTimeRate,                                         // 정시도착률
      kpi.avgDwTimeMinutes,                                   // 평균체류시간분
      kpi.lateAppts,                                          // 지연건수
      kpi.lateRate                                            // 지연비율
    ];

    const targetRange = sheet.getRange(
      targetRow,
      1,
      1,
      rowValues.length
    );
    targetRange.setValues([rowValues]);                       // → 이력 저장 또는 덮어쓰기

    Logger.log(
      '[KPI_saveHistoryForDate_] 저장 완료 - 날짜: %s, 행: %s',
      ymd,
      targetRow
    );

    return {
      ok: true,
      row: targetRow,
      ymd
    };
  } finally {
    lock.releaseLock();                                       // → 잠금 해제
  }
}

function KPI_testSaveHistoryForDate_() {                      // → 이력 저장 테스트
  const tz = KPI_HISTORY_CONFIG.TZ;
  const now = new Date();

  const y = Utilities.formatDate(now, tz, 'yyyy');
  const m = Utilities.formatDate(now, tz, 'MM');
  const d = Utilities.formatDate(now, tz, 'dd');
  const ymd = [y, m, d].join('-');                            // → 오늘 날짜 문자열

  const sheet = KPI_getHistorySheet_();

  // 현재 데이터 행 개수 파악 (2행부터 1열, 빈 셀 제외)
  const allValuesBefore = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countBefore = allValuesBefore.filter(v => v[0] !== '').length;

  const result1 = KPI_saveHistoryForDate_(ymd);               // → 첫 번째 저장
  const result2 = KPI_saveHistoryForDate_(ymd);               // → 두 번째 저장(덮어쓰기)

  // 두 번 저장해도 같은 행이어야 함
  if (result1.row !== result2.row) {
    throw new Error('두 번째 저장이 첫 번째 행을 덮어쓰지 않음 (행 불일치)');
  }

  // 저장 이후 다시 데이터 행 개수 확인
  const allValuesAfter = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countAfter = allValuesAfter.filter(v => v[0] !== '').length;

  // 행 개수가 증가하면 안 됨(덮어쓰기 구조 확인)
  if (countAfter !== countBefore && countBefore !== 0 && countAfter !== countBefore + 1) {
    // 단, 처음 테스트해서 오늘 날짜가 새로 추가되는 경우는 +1 허용
    throw new Error('행 개수가 비정상적으로 증가/감소함: before='
      + countBefore + ', after=' + countAfter);
  }

  Logger.log('KPI_testSaveHistoryForDate_ OK - ymd=%s, row=%s', ymd, result1.row);
}

function nightlyRollup() {                                   // → 야간 KPI 집계
  const tz = KPI_HISTORY_CONFIG.TZ || 'America/New_York';

  const now = new Date();                                    // → 현재 시각

  // 오늘 날짜(뉴욕 기준) 문자열 생성
  const todayY = Utilities.formatDate(now, tz, 'yyyy');
  const todayM = Utilities.formatDate(now, tz, 'MM');
  const todayD = Utilities.formatDate(now, tz, 'dd');
  const todayStr = [todayY, todayM, todayD].join('-');

  // 어제는 **밀리초가 아니라 달력으로** 구한다. 24시간을 빼면 서머타임 시작일
  // 다음 날에 하루가 통째로 사라진다 — 2026-03-09 에 돌리면 '어제'가 3월 8일이
  // 아니라 3월 7일로 나와, 3월 8일 KPI 는 영원히 집계되지 않는다.
  // `setDate()` 는 달력 위에서 움직이므로 그날이 23시간이든 25시간이든 상관없다.
  const yesterdayDate = new Date(
    Number(todayY), Number(todayM) - 1, Number(todayD)       // → 오늘 자정(현지 달력)
  );
  yesterdayDate.setDate(yesterdayDate.getDate() - 1);        // → 하루 전 날짜

  const yY = Utilities.formatDate(yesterdayDate, tz, 'yyyy');
  const yM = Utilities.formatDate(yesterdayDate, tz, 'MM');
  const yD = Utilities.formatDate(yesterdayDate, tz, 'dd');
  const ymd = [yY, yM, yD].join('-');                         // → 어제 YYYY-MM-DD

  // 1) 이력 시트 저장 (날짜 엄격 검증 포함)
  const historyResult = KPI_saveHistoryForDate_(ymd);

  // 2) KPI_DAILY에도 같은 기준으로 저장
  const dateObj = KPI_parseYmdStrict_(ymd);                   // → 어제 Date 재생성
  const kpi = computeKpiForDate_(dateObj);                    // → KPI 계산
  saveDailyKpi_(kpi);                                         // → KPI_DAILY 시트에 저장

  const result = {
    ok: true,
    ymd,
    historyRow: historyResult.row
  };

  Logger.log('[nightlyRollup] 완료 - %s (행=%s)', ymd, historyResult.row);

  return result;
}

function KPI_testNightlyRollup_() {                           // → nightlyRollup 테스트
  const sheet = KPI_getHistorySheet_();

  // 현재 이력 행 수 파악
  const allValuesBefore = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countBefore = allValuesBefore.filter(v => v[0] !== '').length;

  const result1 = nightlyRollup();                            // → 첫 실행
  const result2 = nightlyRollup();                            // → 두 번째 실행 (덮어쓰기 기대)

  if (result1.ymd !== result2.ymd) {
    throw new Error('두 번 실행했을 때 기준 날짜가 다릅니다');
  }

  if (result1.historyRow !== result2.historyRow) {
    throw new Error('두 번째 nightlyRollup이 첫 번째 행을 덮어쓰지 않음');
  }

  // 실행 후 다시 이력 행 수 확인
  const allValuesAfter = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countAfter = allValuesAfter.filter(v => v[0] !== '').length;

  // 어제 날짜가 새로 추가되는 첫 실행인 경우를 제외하면, 행 수가 증가하면 안 됨
  if (countAfter !== countBefore && countBefore !== 0 && countAfter !== countBefore + 1) {
    throw new Error('nightlyRollup 실행 후 행 개수가 비정상적으로 변경됨: before='
      + countBefore + ', after=' + countAfter);
  }

  Logger.log('KPI_testNightlyRollup_ OK - %s, row=%s',
    result1.ymd,
    result1.historyRow
  );
}

function getKpiHistory(days) {                               // → 최근 N일 이력 조회
  const n = Number(days);
  if (!Number.isFinite(n) || n <= 0) {
    throw new Error('조회할 일수는 1 이상의 숫자여야 합니다: ' + days);
  }

  const sheet = KPI_getHistorySheet_();
  const lastRow = sheet.getLastRow();
  if (lastRow <= 1) {
    return [];                                               // → 데이터 없음
  }

  const dataRange = sheet.getRange(
    2,
    1,
    lastRow - 1,
    KPI_HISTORY_CONFIG.HEADERS.length
  );
  const values = dataRange.getValues();
  const tz = KPI_HISTORY_CONFIG.TZ;
  const rows = [];

  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const cellVal = row[0];                                  // → 날짜 값
    if (!cellVal) continue;

    let ymd;
    if (cellVal instanceof Date) {
      ymd = Utilities.formatDate(cellVal, tz, 'yyyy-MM-dd');
    } else {
      ymd = String(cellVal);
    }

    // 날짜 형식/존재 여부 검증 (유효하지 않으면 건너뜀)
    let d;
    try {
      d = KPI_parseYmdStrict_(ymd);
    } catch (e) {
      continue;
    }

    rows.push({
      ymd,
      date: d,
      totalAppts: row[1],
      onTimeAppts: row[2],
      onTimeRate: row[3],
      avgDwTimeMinutes: row[4],
      lateAppts: row[5],
      lateRate: row[6]
    });
  }

  if (rows.length === 0) return [];

  // 날짜 오름차순 정렬
  rows.sort((a, b) => a.date.getTime() - b.date.getTime());

  const startIndex = Math.max(0, rows.length - n);
  const recent = rows.slice(startIndex);                     // → 최근 N일

  return recent.map(r => ({
    ymd: r.ymd,
    totalAppts: r.totalAppts,
    onTimeAppts: r.onTimeAppts,
    onTimeRate: r.onTimeRate,
    avgDwTimeMinutes: r.avgDwTimeMinutes,
    lateAppts: r.lateAppts,
    lateRate: r.lateRate
  }));
}

function KPI_testGetKpiHistory_() {                          // → 이력 조회 테스트
  const history7 = getKpiHistory(7);                         // → 최근 7일
  const history30 = getKpiHistory(30);                       // → 최근 30일

  // 간단 기대값 검증: 7일 이력이 30일 이력보다 많을 수는 없음
  if (history7.length > history30.length) {
    throw new Error('최근 7일 이력이 30일 이력보다 많을 수 없습니다');
  }

  // 날짜가 오름차순인지 검증
  const isSorted = arr => arr.every((r, i) =>
    i === 0 || r.ymd >= arr[i - 1].ymd
  );
  if (!isSorted(history7) || !isSorted(history30)) {
    throw new Error('이력 데이터가 날짜 오름차순으로 정렬되지 않았습니다');
  }

  Logger.log('최근 7일: ' + JSON.stringify(history7));
  Logger.log('최근 30일: ' + JSON.stringify(history30));
}

function KPI_cleanupOldHistory_() {                          // → 오래된 이력 정리
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const sheet = KPI_getHistorySheet_();
    const lastRow = sheet.getLastRow();
    if (lastRow <= 1) {
      return 0;                                              // → 데이터 없음
    }

    const range = sheet.getRange(2, 1, lastRow - 1, 1);      // → 날짜 열만
    const values = range.getValues();
    const tz = KPI_HISTORY_CONFIG.TZ;
    const now = new Date();

    // 오늘(뉴욕 기준) 자정
    const todayY = Utilities.formatDate(now, tz, 'yyyy');
    const todayM = Utilities.formatDate(now, tz, 'MM');
    const todayD = Utilities.formatDate(now, tz, 'dd');
    // 보관 기준일도 같은 이유로 달력으로 뺀다(24시간 × N 이 아니다)
    const cutoff = new Date(
      Number(todayY), Number(todayM) - 1, Number(todayD)     // → 오늘 자정(현지 달력)
    );
    cutoff.setDate(cutoff.getDate() - KPI_HISTORY_CONFIG.MAX_DAYS);
    const cutoffMs = cutoff.getTime();                       // → 이 시각 이전 데이터는 삭제 대상

    const rowsToDelete = [];

    for (let i = 0; i < values.length; i++) {
      const cellVal = values[i][0];
      if (!cellVal) continue;

      let ymd;
      if (cellVal instanceof Date) {
        ymd = Utilities.formatDate(cellVal, tz, 'yyyy-MM-dd');
      } else {
        ymd = String(cellVal);
      }

      let d;
      try {
        d = KPI_parseYmdStrict_(ymd);
      } catch (e) {
        continue;                                            // → 이상한 날짜는 조용히 패스
      }

      if (d.getTime() < cutoffMs) {
        rowsToDelete.push(i + 2);                            // → 실제 행 번호
      }
    }

    // 뒤에서부터 지워야 행 번호가 깨지지 않음
    rowsToDelete.sort((a, b) => b - a);
    rowsToDelete.forEach(row => {
      sheet.deleteRow(row);
    });

    Logger.log('[KPI_cleanupOldHistory_] 삭제된 행 수: ' + rowsToDelete.length);
    return rowsToDelete.length;
  } finally {
    lock.releaseLock();
  }
}

function KPI_testCleanupOldHistory_() {                      // → 정리 테스트
  const deleted = KPI_cleanupOldHistory_();
  if (!Number.isFinite(deleted) || deleted < 0) {
    throw new Error('삭제된 행 수가 올바르지 않습니다: ' + deleted);
  }
  Logger.log('삭제된 행 수: ' + deleted);
}