구글시트 주간 리포트 자동 메일 발송: KPI 4편 코드 포함 (감사 수정 반영 완성본)

// → KPI 주차 계산에 사용할 고정 시간대
const KPI_WEEK_TZ = 'America/New_York';

// → NY 시간대로 날짜를 잘라 연/월/일만 추출합니다.
function KPI_ymdParts_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_ymdParts_: 잘못된 날짜입니다');
  }
  const ymd = Utilities.formatDate(date, KPI_WEEK_TZ, 'yyyy-MM-dd');
  const m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd);
  if (!m) throw new Error('KPI_ymdParts_: 날짜 파싱 실패: ' + ymd);
  return { y: +m[1], m: +m[2], d: +m[3] };
}

// → 기준 날짜가 속한 주의 월요일(ISO week 기준)을 구합니다.
//   NY 시간대의 Y-M-D만 사용해, DST 영향을 제거합니다.
function KPI_getWeekStart_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_getWeekStart_: 잘못된 날짜입니다');
  }

  const p = KPI_ymdParts_(date);
  // → NY 기준 Y-M-D를 로컬 정오 Date로 생성
  const d = new Date(p.y, p.m - 1, p.d, 12, 0, 0, 0);
  const day = d.getDay();           // 0(일)~6(토)
  const isoDay = day === 0 ? 7 : day; // ISO: 월=1 … 일=7
  d.setDate(d.getDate() - (isoDay - 1)); // 해당 주 월요일로 이동
  d.setHours(0, 0, 0, 0);           // 자정으로 정규화
  return d;
}

// → ISO 주차와 ISO 연도를 계산합니다.
//   역시 NY 시간대의 Y-M-D만 쓰고, 로컬 정오 Date로만 연산합니다.
function KPI_getIsoWeekNo_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_getIsoWeekNo_: 잘못된 날짜입니다');
  }

  const p = KPI_ymdParts_(date);
  const d = new Date(p.y, p.m - 1, p.d, 12, 0, 0, 0); // 입력일 NY Y-M-D 기준 정오

  // ISO 기준: 목요일이 포함된 주가 그 해의 주차가 됨
  const day = d.getDay();              // 0~6
  const isoDay = day === 0 ? 7 : day;  // 1~7
  d.setDate(d.getDate() + (4 - isoDay)); // 그 주의 목요일로 이동
  const weekYear = d.getFullYear();     // ISO week-year

  // 해당 ISO week-year의 1주가 되는 주의 목요일(항상 1월 4일이 포함된 주)
  const jan4 = new Date(weekYear, 0, 4, 12, 0, 0, 0);
  const jan4Day = jan4.getDay();
  const jan4IsoDay = jan4Day === 0 ? 7 : jan4Day;
  jan4.setDate(jan4.getDate() + (4 - jan4IsoDay)); // 그 해 첫 목요일

  const msInDay = 24 * 60 * 60 * 1000;
  const weekNo = 1 + Math.round((d - jan4) / (msInDay * 7));

  return { year: weekYear, week: weekNo };
}

// → ISO 주차 헬퍼 함수 테스트
function KPI_testWeekHelpers_() {
  function assertEqual(label, actual, expected) {
    const ok = JSON.stringify(actual) === JSON.stringify(expected);
    if (!ok) {
      const msg = 'FAIL ' + label + ' expected=' +
        JSON.stringify(expected) + ' actual=' +
        JSON.stringify(actual);
      Logger.log(msg);
      throw new Error(msg);
    } else {
      Logger.log('OK ' + label + ' = ' + JSON.stringify(actual));
    }
  }

  // 1) 2026-12-31 → ISO week-year/week (예상: 2026년 53주라고 가정)
  const d1 = new Date('2026-12-31T00:00:00Z');
  const w1 = KPI_getIsoWeekNo_(d1);
  assertEqual('2026-12-31 ISO week', w1, { year: 2026, week: 53 });

  // 2) 2026-01-01 → ISO week-year/week (예상: 2026년 1주라고 가정)
  const d2 = new Date('2026-01-01T00:00:00Z');
  const w2 = KPI_getIsoWeekNo_(d2);
  assertEqual('2026-01-01 ISO week', w2, { year: 2026, week: 1 });

  // 3) weekStart 로직 기본 동작 확인(오늘 기준)
  const today = new Date();
  const wsToday = KPI_getWeekStart_(today);
  const isoToday = KPI_getIsoWeekNo_(today);
  Logger.log('Today weekStart=' + wsToday.toISOString() +
             ' iso=' + JSON.stringify(isoToday));

  // 4) 잘못된 Date 입력 시 에러 나는지 확인
  let threw = false;
  try {
    KPI_getWeekStart_(new Date('')); // Invalid Date
  } catch (e) {
    threw = true;
    Logger.log('OK invalid date for KPI_getWeekStart_ threw error: ' + e);
  }
  if (!threw) {
    throw new Error('FAIL KPI_getWeekStart_ did not throw on invalid date');
  }

  threw = false;
  try {
    KPI_getIsoWeekNo_(new Date('')); // Invalid Date
  } catch (e) {
    threw = true;
    Logger.log('OK invalid date for KPI_getIsoWeekNo_ threw error: ' + e);
  }
  if (!threw) {
    throw new Error('FAIL KPI_getIsoWeekNo_ did not throw on invalid date');
  }
}

DATE
FACILITY
CARRIER
APPOINTMENT_COUNT
TOTAL_LOADS
ON_TIME_LOADS
LATE_LOADS
CANCELLED_LOADS
AVG_DWELL_MIN
ON_TIME_RATE
LATE_RATE
CANCEL_RATE
CREATED_AT
UPDATED_AT

// → 2편에서 확정한 KPI_HISTORY 헤더 (1행에 이 순서로 있어야 함)
const KPI_HISTORY_HEADERS = [
  'DATE',
  'FACILITY',
  'CARRIER',
  'APPOINTMENT_COUNT',
  'TOTAL_LOADS',
  'ON_TIME_LOADS',
  'LATE_LOADS',
  'CANCELLED_LOADS',
  'AVG_DWELL_MIN',
  'ON_TIME_RATE',
  'LATE_RATE',
  'CANCEL_RATE',
  'CREATED_AT',
  'UPDATED_AT'
];

// → 주간 리포트용 설정
const KPI_WEEKLY_CONFIG = {
  HISTORY_SHEET_NAME: 'KPI_HISTORY',
  TZ: KPI_WEEK_TZ,
  MIN_DAYS_FOR_REPORT: 1  // 지난주에 최소 며칠 이상 데이터가 있어야 발송
};

// → KPI_HISTORY 구조가 기대와 같은지 확인
function KPI_assertHistoryHeaders_(sheet) {
  const lastCol = sheet.getLastColumn();
  const headerRange = sheet.getRange(1, 1, 1, lastCol);
  const actual = headerRange.getValues()[0].map(String);

  const expected = KPI_HISTORY_HEADERS;
  const sameLength = actual.length === expected.length;
  const sameAll = sameLength && actual.every(function (h, i) {
    return h === expected[i];
  });

  if (!sameAll) {
    const msg = 'KPI_HISTORY 헤더가 예상과 다릅니다.\n' +
      'Expected: ' + JSON.stringify(expected) + '\n' +
      'Actual:   ' + JSON.stringify(actual);
    throw new Error(msg);
  }

  // 헤더 이름 → 인덱스 맵
  const colIdx = {};
  expected.forEach(function (h, i) {
    colIdx[h] = i;
  });
  return colIdx;
}

// → 지난 한 주(월~일) 운송사 KPI 요약을 계산합니다.
function KPI_getWeeklyCarrierReport(baseDate) {
  const today = baseDate instanceof Date && !isNaN(baseDate)
    ? new Date(baseDate)
    : new Date();

  // 지난주 기준일: 오늘에서 7일 전
  const lastWeekRef = new Date(today);
  lastWeekRef.setDate(lastWeekRef.getDate() - 7);

  // 지난주 월요일~일요일
  const weekStart = KPI_getWeekStart_(lastWeekRef);
  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekEnd.getDate() + 6);

  const startYmd = Utilities.formatDate(
    weekStart, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
  );
  const endYmd = Utilities.formatDate(
    weekEnd, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
  );

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_CONFIG.HISTORY_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_HISTORY 시트를 찾을 수 없습니다');
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    throw new Error('KPI_HISTORY 시트에 데이터가 없습니다');
  }

  const lastCol = sheet.getLastColumn();
  // 감사 지적: 반드시 마지막 행까지 포함해야 함
  const range = sheet.getRange(2, 1, lastRow - 1, lastCol);
  const values = range.getValues();

  // 헤더 검증 + 인덱스 맵 생성
  const colIdx = KPI_assertHistoryHeaders_(sheet);

  const carrierStats = {};          // { CARRIER: { total, onTime, late } }
  const daysInRange = new Set();    // 포함된 날짜 수 확인용

  values.forEach(function (row) {
    const dateVal = row[colIdx['DATE']];
    if (!(dateVal instanceof Date) || isNaN(dateVal)) {
      return;
    }
    const ymd = Utilities.formatDate(
      dateVal, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
    );
    if (ymd < startYmd || ymd > endYmd) return;

    const carrier = String(row[colIdx['CARRIER']] || '').trim();
    if (!carrier) return;

    daysInRange.add(ymd);

    const total = row[colIdx['TOTAL_LOADS']];
    const onTime = row[colIdx['ON_TIME_LOADS']];
    const late = row[colIdx['LATE_LOADS']];

    // TOTAL_LOADS 숫자형이 아니면 스킵 + 로그
    if (!Number.isFinite(Number(total))) {
      Logger.log('SKIP TOTAL_LOADS not numeric: ' +
        ymd + ' / ' + carrier + ' / value=' + total);
      return;
    }

    const nTotal = Number(total);
    const nOnTime = Number(onTime);
    const nLate = Number(late);

    if (
      !Number.isFinite(nOnTime) || nOnTime < 0 ||
      !Number.isFinite(nLate) || nLate < 0 ||
      nTotal < 0
    ) {
      Logger.log('SKIP invalid KPI numbers: ' +
        ymd + ' / ' + carrier +
        ' total=' + total + ' onTime=' + onTime + ' late=' + late);
      return;
    }

    if (!carrierStats[carrier]) {
      carrierStats[carrier] = { total: 0, onTime: 0, late: 0 };
    }
    carrierStats[carrier].total += nTotal;
    carrierStats[carrier].onTime += nOnTime;
    carrierStats[carrier].late += nLate;
  });

  if (daysInRange.size < KPI_WEEKLY_CONFIG.MIN_DAYS_FOR_REPORT) {
    throw new Error('지난주 범위에 KPI 데이터가 부족합니다. daysInRange=' +
      daysInRange.size + ' (min=' + KPI_WEEKLY_CONFIG.MIN_DAYS_FOR_REPORT + ')');
  }

  // 3편에서 정의한 운송사 점수 계산 함수 호출
  // 실제 반환 구조: { '운송사명': { score: number, grade: string, ... }, ... }
  const scoreInfo = KPI_getCarrierScores();

  const rows = [];
  rows.push([
    '운송사',
    '총 입고건수',
    '정시 도착',
    '지연 도착',
    '정시율(%)',
    '점수',
    '등급'
  ]);

  Object.keys(carrierStats).sort().forEach(function (carrier) {
    const st = carrierStats[carrier];
    const onTimeRate = st.total > 0
      ? Math.round((st.onTime / st.total) * 1000) / 10
      : 0;

    const sc = scoreInfo[carrier] || { score: 0, grade: '-' };

    rows.push([
      carrier,
      String(st.total),
      String(st.onTime),
      String(st.late),
      onTimeRate.toFixed(1),
      (typeof sc.score === 'number'
        ? sc.score.toFixed(1)
        : String(sc.score)),
      sc.grade
    ]);
  });

  let html = '<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse;">';
  rows.forEach(function (r, idx) {
    html += '<tr>';
    r.forEach(function (cell) {
      const tag = idx === 0 ? 'th' : 'td';
      html += '<' + tag + '>' + String(cell) + '</' + tag + '>';
    });
    html += '</tr>';
  });
  html += '</table>';

  const iso = KPI_getIsoWeekNo_(weekStart);

  return {
    startYmd: startYmd,
    endYmd: endYmd,
    isoYear: iso.year,
    isoWeek: iso.week,
    htmlTable: html,
    carrierCount: Object.keys(carrierStats).length
  };
}

function KPI_testWeeklyCarrierReport_() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_CONFIG.HISTORY_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_HISTORY 시트가 없어 테스트를 진행할 수 없습니다');
  }

  // 1) lastRow === 2 (헤더 + 데이터 1행) 인지 확인하는 테스트 경고
  const lastRow = sheet.getLastRow();
  if (lastRow === 2) {
    Logger.log('INFO lastRow===2 (헤더 + 1행). 단일 행 범위 처리 경계 테스트에 적합한 상태입니다.');
  } else {
    Logger.log('INFO lastRow=' + lastRow +
      '. 단일 행 경계 테스트를 하려면 임시로 데이터 한 행만 남겨 두는 것이 좋습니다.');
  }

  // 2) TOTAL_LOADS 비정상 값이 있을 때 스킵 + 로그 확인
  // 실제로 특정 셀을 'ABC' 로 바꾸는 자동 테스트는 데이터 손상을 막기 위해 하지 않습니다.
  Logger.log('INFO TOTAL_LOADS가 숫자가 아닌 행이 있다면, ' +
    'KPI_getWeeklyCarrierReport 실행 시 "SKIP TOTAL_LOADS not numeric" 로그가 남는지 확인하세요.');

  // 3) 기본 실행 결과 확인
  try {
    const rep = KPI_getWeeklyCarrierReport(new Date());
    Logger.log('OK KPI_getWeeklyCarrierReport: ' + JSON.stringify(rep, null, 2));
  } catch (e) {
    Logger.log('FAIL KPI_getWeeklyCarrierReport: ' + e);
    throw e;
  }
}

ROLE
EMAIL
ACTIVE

// → 주간 리포트 메일 발송 설정
const KPI_WEEKLY_MAIL_CONFIG = {
  RECIPIENT_SHEET_NAME: 'KPI_RECIPIENTS',
  SUBJECT_PREFIX: '[입고 KPI] 주간 리포트',
  TZ: KPI_WEEK_TZ
};

const KPI_RECIPIENT_HEADERS = [
  'ROLE',
  'EMAIL',
  'ACTIVE'
];

// → KPI_RECIPIENTS 헤더 검증
function KPI_assertRecipientHeaders_(sheet) {
  const lastCol = sheet.getLastColumn();
  const headerRange = sheet.getRange(1, 1, 1, lastCol);
  const actual = headerRange.getValues()[0].map(String);

  const expected = KPI_RECIPIENT_HEADERS;
  const sameLength = actual.length === expected.length;
  const sameAll = sameLength && actual.every(function (h, i) {
    return h === expected[i];
  });

  if (!sameAll) {
    const msg = 'KPI_RECIPIENTS 헤더가 예상과 다릅니다.\n' +
      'Expected: ' + JSON.stringify(expected) + '\n' +
      'Actual:   ' + JSON.stringify(actual);
    throw new Error(msg);
  }

  const colIdx = {};
  expected.forEach(function (h, i) {
    colIdx[h] = i;
  });
  return colIdx;
}

// → KPI_RECIPIENTS 시트에서 ACTIVE=TRUE 인 이메일 목록을 가져옵니다.
function KPI_loadActiveRecipients_() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_MAIL_CONFIG.RECIPIENT_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_RECIPIENTS 시트를 찾을 수 없습니다');
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    throw new Error('KPI_RECIPIENTS 시트에 데이터가 없습니다');
  }

  const lastCol = sheet.getLastColumn();
  const colIdx = KPI_assertRecipientHeaders_(sheet);

  const range = sheet.getRange(2, 1, lastRow - 1, lastCol);
  const values = range.getValues();

  const emails = [];

  values.forEach(function (row) {
    const active = row[colIdx['ACTIVE']];
    const isActive = (typeof active === 'boolean') ? active : Boolean(active);
    if (!isActive) return;

    const email = String(row[colIdx['EMAIL']] || '').trim();
    if (!email) return;
    if (email.indexOf('@') === -1) {
      Logger.log('SKIP invalid email: ' + email);
      return;
    }
    emails.push(email);
  });

  if (emails.length === 0) {
    throw new Error('활성 수신자 이메일이 없습니다');
  }

  return emails;
}

// → 지난주 운송사 KPI 리포트를 HTML 메일로 발송합니다.
function KPI_sendWeeklyCarrierReport() {
  const recipients = KPI_loadActiveRecipients_();
  const report = KPI_getWeeklyCarrierReport(new Date());

  const subject = Utilities.formatString(
    '%s %s~%s (ISO %d주차)',
    KPI_WEEKLY_MAIL_CONFIG.SUBJECT_PREFIX,
    report.startYmd,
    report.endYmd,
    report.isoWeek
  );

  let body = '';
  body += '<p>안녕하세요.</p>';
  body += Utilities.formatString(
    '<p>%s부터 %s까지(ISO %d주차) 입고 예약 기준 운송사 KPI 요약입니다.</p>',
    report.startYmd,
    report.endYmd,
    report.isoWeek
  );
  body += report.htmlTable;
  body += '<p>※ 이 메일은 구글시트 Apps Script 주간 리포트 자동화로 발송되었습니다.</p>';

  const options = {
    htmlBody: body
  };

  const to = recipients.join(',');
  MailApp.sendEmail(to, subject, '', options);

  return {
    sentTo: recipients,
    subject: subject,
    carrierCount: report.carrierCount
  };
}

function KPI_testSendWeeklyCarrierReport_() {
  try {
    const result = KPI_sendWeeklyCarrierReport();
    Logger.log('OK KPI_sendWeeklyCarrierReport: ' +
      JSON.stringify(result, null, 2));
  } catch (e) {
    Logger.log('FAIL KPI_sendWeeklyCarrierReport: ' + e);
    throw e;
  }
}