// PLAN-03 · 주간 KPI 자동 리포트
// 난이도 ★★ · 예상 60분 · 전제: KPI 적재된 Google Sheet + Gemini API 키
// 출처: https://www.nedabah.org/resources/automation/planning/03-weekly-kpi-report.html
// Apps Script 편집기에 붙여넣은 뒤 스크립트 속성에 PROPS 항목을 등록하세요.

const PROPS = PropertiesService.getScriptProperties();
const GEMINI_KEY = PROPS.getProperty('GEMINI_API_KEY');
const SHEET_ID   = PROPS.getProperty('SHEET_ID');
const EMAIL_TO   = PROPS.getProperty('RECIPIENT_EMAIL');
const SLACK_HOOK = PROPS.getProperty('SLACK_WEBHOOK') || '';

function weeklyReport() {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const daily = ss.getSheetByName('kpi_daily').getDataRange().getValues();
  const meta  = ss.getSheetByName('kpi_meta').getDataRange().getValues();

  const metaMap = {};
  for (let i = 1; i < meta.length; i++) {
    metaMap[meta[i][0]] = {direction: meta[i][1], target: meta[i][2], unit: meta[i][3]};
  }

  const now = new Date();
  const thisStart = startOfWeek(now);
  const lastStart = new Date(thisStart.getTime() - 7*86400000);
  const lastEnd   = new Date(thisStart.getTime() - 1);

  const stats = {};
  for (let i = 1; i < daily.length; i++) {
    const [date, indicator, value] = daily[i];
    if (!indicator) continue;
    const d = new Date(date);
    stats[indicator] = stats[indicator] || {thisWeek:[], lastWeek:[]};
    if (d >= thisStart && d < now)        stats[indicator].thisWeek.push(Number(value));
    else if (d >= lastStart && d <= lastEnd) stats[indicator].lastWeek.push(Number(value));
  }

  const summary = Object.entries(stats).map(([ind, v]) => {
    const tAvg = avg(v.thisWeek);
    const lAvg = avg(v.lastWeek);
    const wow  = lAvg ? ((tAvg - lAvg) / lAvg * 100) : 0;
    const m = metaMap[ind] || {};
    const status = judgeStatus(tAvg, m);
    return {indicator: ind, thisAvg: tAvg, lastAvg: lAvg, wow, target: m.target, direction: m.direction, unit: m.unit, status};
  });

  const insight = aiCommentary(summary);
  const html = buildReportHTML(summary, insight, thisStart);

  if (EMAIL_TO) GmailApp.sendEmail(EMAIL_TO, `[주간] ${fmt(thisStart)} KPI 리포트`, '', {htmlBody: html});
  if (SLACK_HOOK) UrlFetchApp.fetch(SLACK_HOOK, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify({text: buildSlack(summary, insight)})
  });
}

function startOfWeek(d) {
  const x = new Date(d);
  x.setHours(0,0,0,0);
  x.setDate(x.getDate() - x.getDay() + (x.getDay() === 0 ? -6 : 1)); // 월요일 시작
  return x;
}
function avg(arr) { return arr.length ? arr.reduce((a,b)=>a+b,0)/arr.length : 0; }
function fmt(d) { return d.toISOString().slice(0,10); }

function judgeStatus(val, meta) {
  if (!meta.target) return '—';
  const reached = meta.direction === 'up' ? val >= meta.target : val <= meta.target;
  return reached ? '✅ 달성' : '⚠️ 미달';
}

function aiCommentary(summary) {
  const prompt = `
당신은 경영진 보고를 작성하는 시니어 분석가입니다.
다음 주간 KPI를 보고 한국어로 간결하게 작성하세요.

규칙:
- 사실만, 추측 금지
- 5문장 이내
- 1) 가장 좋은 변화 1건  2) 가장 우려되는 변화 1건  3) 다음 주 권고 1건
- "~합니다"체

데이터:
${JSON.stringify(summary, null, 2)}

JSON으로 반환:
{"highlight":"...","concern":"...","recommendation":"..."}
`;
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${GEMINI_KEY}`;
  const res = UrlFetchApp.fetch(url, {
    method:'post', contentType:'application/json',
    payload: JSON.stringify({
      contents:[{parts:[{text:prompt}]}],
      generationConfig:{responseMimeType:'application/json'}
    })
  });
  return JSON.parse(JSON.parse(res.getContentText()).candidates[0].content.parts[0].text);
}

function buildReportHTML(summary, insight, weekStart) {
  let rows = summary.map(s => {
    const arrow = s.wow > 1 ? '🔺' : s.wow < -1 ? '🔻' : '➖';
    const wowStr = s.wow ? `${s.wow.toFixed(1)}%` : '—';
    return `<tr>
      <td style="padding:.5rem;border-bottom:1px solid #eee;">${s.indicator}</td>
      <td style="padding:.5rem;border-bottom:1px solid #eee;text-align:right;">${s.thisAvg.toFixed(1)} ${s.unit||''}</td>
      <td style="padding:.5rem;border-bottom:1px solid #eee;text-align:right;">${s.lastAvg.toFixed(1)}</td>
      <td style="padding:.5rem;border-bottom:1px solid #eee;text-align:right;">${arrow} ${wowStr}</td>
      <td style="padding:.5rem;border-bottom:1px solid #eee;text-align:right;">${s.target||'—'}</td>
      <td style="padding:.5rem;border-bottom:1px solid #eee;">${s.status}</td>
    </tr>`;
  }).join('');

  return `<div style="font-family:'Noto Sans KR',sans-serif;max-width:760px;line-height:1.6;color:#222;">
    <h2 style="border-bottom:2px solid #b45309;padding-bottom:.5rem;">주간 KPI 리포트</h2>
    <p style="color:#6a604f;">기준 주: ${fmt(weekStart)} ~ ${fmt(new Date())}</p>

    <h3>📌 핵심 코멘트</h3>
    <p><b>가장 좋은 변화 — </b>${insight.highlight}</p>
    <p><b>우려 포인트 — </b>${insight.concern}</p>
    <p><b>다음 주 권고 — </b>${insight.recommendation}</p>

    <h3 style="margin-top:1.5rem;">📊 지표별 표</h3>
    <table style="width:100%;border-collapse:collapse;font-size:.95em;">
      <thead style="background:#fbf6ec;">
        <tr><th style="padding:.5rem;text-align:left;">지표</th>
            <th style="padding:.5rem;text-align:right;">이번주 평균</th>
            <th style="padding:.5rem;text-align:right;">지난주 평균</th>
            <th style="padding:.5rem;text-align:right;">WoW</th>
            <th style="padding:.5rem;text-align:right;">목표</th>
            <th style="padding:.5rem;text-align:left;">상태</th></tr>
      </thead>
      <tbody>${rows}</tbody>
    </table>
    <p style="color:#999;font-size:.85em;margin-top:2rem;">자동 생성 · 원천 데이터: 회사 KPI 시트</p>
  </div>`;
}

function buildSlack(summary, insight) {
  const top = summary.map(s => `• ${s.indicator}: ${s.thisAvg.toFixed(1)}${s.unit||''} (${s.wow>0?'+':''}${s.wow.toFixed(1)}% WoW) ${s.status}`).join('\n');
  return `*📊 주간 KPI 리포트*\n\n${top}\n\n*하이라이트*: ${insight.highlight}\n*우려*: ${insight.concern}\n*권고*: ${insight.recommendation}`;
}