/**
 * 見本：Googleドライブのフォルダにある CSV（Shift_JIS / UTF-8）を整形してシートに出力（Kaname / AI制作）
 *
 * 【目的】
 *  EC注文データなどの CSV を、列の並べ替え・全角半角の統一・日付形式の統一・重複行の削除をして
 *  スプレッドシートにまとめます。
 *
 * 【使い方】
 *  1. SETTINGS.inputFolderId に CSV を置くフォルダのIDを入れる
 *  2. スプレッドシートを開き、メニュー「CSV整形」→「新しいCSVを取り込む」を実行
 *  2'. 先に「試し実行」を使うと、シートにもファイルにも何も書かずに、件数と最初の3行だけを表示します
 *  3. 取り込んだファイルはシート「取込記録」にIDで記録し、次回以降は読み込みません（二重取り込み防止）。
 *     元のCSVファイルは、名前も中身も変更しません。
 *
 * 【設定項目】SETTINGS を参照
 *  - outputColumns : 出力する列の順番（見出し名）
 *  - aliases       : CSV側の見出しの別名（例：「購入者名」→「氏名」）
 *  - dedupeKeys    : 重複判定に使う列（既にシートにある行とも照合します）
 *  - notifyEmail   : 結果をメールで受け取る場合のアドレス（空なら送らない。送信前に残り送信数を確認）
 *
 * 【注意】日付が読めない行は削除せず、元の文字のまま「備考」列に理由を書いて残します。
 */
const SETTINGS = {
  inputFolderId: 'ここにCSVフォルダID',
  outputSheetName: '整形後',
  logSheetName: '取込記録',
  outputColumns: ['注文ID', '注文日', '氏名', 'メール', '電話', '商品名', '数量', '金額'],
  aliases: {
    '注文ID': ['注文番号', 'オーダーID', 'order_id'],
    '注文日': ['注文日時', '購入日', 'order_date'],
    '氏名': ['購入者名', 'お名前', '名前'],
    'メール': ['メールアドレス', 'email', 'E-mail'],
    '電話': ['電話番号', 'TEL', 'tel'],
    '商品名': ['品名', '商品'],
    '数量': ['個数', '購入数'],
    '金額': ['合計金額', '価格', '小計'],
  },
  dedupeKeys: ['注文ID', '商品名'],
  numberColumns: ['数量', '金額'],
  textColumns: ['電話'],       // 先頭の0が消えないよう文字列として書き込む
  noteColumn: '備考',
  notifyEmail: '',
  tz: 'Asia/Tokyo',
};

function onOpen() {
  SpreadsheetApp.getUi().createMenu('CSV整形')
    .addItem('試し実行（書き込まない）', 'previewCsvFiles')
    .addItem('新しいCSVを取り込む', 'importCsvFiles')
    .addToUi();
}

/** メイン：フォルダ内の未処理CSVを全部取り込む */
function importCsvFiles() { return runImport_(false); }

/** 試し実行：何も書き込まず、件数と最初の3行を表示する */
function previewCsvFiles() { return runImport_(true); }

function runImport_(dryRun) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  const summary = { files: 0, added: 0, dup: 0, empty: 0, warn: 0, errors: [], preview: [] };
  try {
    const ss = SpreadsheetApp.getActive();
    const folder = DriveApp.getFolderById(SETTINGS.inputFolderId);
    const sheet = dryRun ? ss.getSheetByName(SETTINGS.outputSheetName) : prepareOutputSheet_();
    const log = ss.getSheetByName(SETTINGS.logSheetName) || (dryRun ? null : createLogSheet_(ss));
    const seen = sheet ? loadExistingKeys_(sheet) : {};
    const doneIds = loadDoneIds_(log);
    const files = folder.getFiles();
    while (files.hasNext()) {
      const file = files.next();
      const name = file.getName();
      if (doneIds[file.getId()] || !/\.csv$/i.test(name)) continue;
      try {
        const r = processFile_(file, dryRun ? null : sheet, seen);
        summary.files++; summary.added += r.added; summary.dup += r.dup; summary.empty += r.empty; summary.warn += r.warn;
        if (dryRun) summary.preview = summary.preview.concat(r.rows).slice(0, 3);
        else log.appendRow([file.getId(), name, r.added, Utilities.formatDate(new Date(), SETTINGS.tz, 'yyyy/MM/dd HH:mm')]); // 成功後にだけ記録
      } catch (e) {
        summary.errors.push(name + ': ' + e.message); // 1ファイルの失敗で全体を止めない
      }
    }
  } finally {
    lock.releaseLock();
  }
  if (dryRun) {
    const msg = `【試し実行・書き込みなし】CSV ${summary.files}件：追加予定 ${summary.added}行 / 重複 ${summary.dup}行 / 空行 ${summary.empty}行 / 要確認 ${summary.warn}行` +
      (summary.preview.length ? '\n最初の行：\n' + summary.preview.map(r => r.join(' | ')).join('\n') : '') +
      (summary.errors.length ? `\nエラー：\n${summary.errors.join('\n')}` : '');
    try { SpreadsheetApp.getUi().alert(msg); } catch (e) { Logger.log(msg); }
    return summary;
  }
  const msg = `CSV ${summary.files}件を取り込み：追加 ${summary.added}行 / 重複 ${summary.dup}行 / 空行 ${summary.empty}行 / 要確認 ${summary.warn}行` +
    (summary.errors.length ? `\nエラー：\n${summary.errors.join('\n')}` : '');
  Logger.log(msg);
  notify_(msg);
  try { SpreadsheetApp.getUi().alert(msg); } catch (e) { /* トリガー実行時はUIなし */ }
  return summary;
}

function processFile_(file, sheet, seen) {
  const text = decodeCsv_(file.getBlob());
  const table = Utilities.parseCsv(text);
  const r = { added: 0, dup: 0, empty: 0, warn: 0, rows: [] };
  if (table.length === 0) return r;

  const idx = buildColumnIndex_(table[0]);
  const out = [];
  for (let i = 1; i < table.length; i++) {
    const raw = table[i];
    if (raw.every(c => String(c).trim() === '')) { r.empty++; continue; }

    const rec = {};
    const notes = [];
    SETTINGS.outputColumns.forEach(col => {
      let v = idx[col] === undefined ? '' : raw[idx[col]];
      v = normalizeText_(v);
      if (col === '注文日') {
        const d = normalizeDate_(v);
        if (v !== '' && d === null) notes.push('日付を解釈できません');
        else if (d !== null) v = d;
      } else if (SETTINGS.numberColumns.indexOf(col) >= 0) {
        const n = normalizeNumber_(v);
        if (v !== '' && n === null) notes.push(col + 'が数値ではありません');
        else if (n !== null) v = n;
      } else if (col === 'メール') {
        v = v.toLowerCase();
      }
      rec[col] = v;
    });

    const key = SETTINGS.dedupeKeys.map(k => rec[k]).join('\u0001');
    if (seen[key]) { r.dup++; continue; }
    seen[key] = true;

    if (notes.length) r.warn++;
    out.push(SETTINGS.outputColumns.map(c => rec[c]).concat([notes.join(' / ')]));
    r.added++;
  }
  r.rows = out;
  if (out.length && sheet) {
    const start = sheet.getLastRow() + 1;
    sheet.getRange(start, 1, out.length, out[0].length).setValues(out);
  }
  return r;
}

/** UTF-8 を先に試し、文字化け（U+FFFD）が出たら Shift_JIS として読み直す */
function decodeCsv_(blob) {
  let text = blob.getDataAsString('UTF-8');
  if (text.indexOf('�') >= 0) text = blob.getDataAsString('Shift_JIS');
  return text.replace(/^/, '');
}

/** 見出し名（別名含む）→ 列番号 */
function buildColumnIndex_(header) {
  const names = header.map(h => normalizeText_(h));
  const idx = {};
  SETTINGS.outputColumns.forEach(col => {
    const cands = [col].concat(SETTINGS.aliases[col] || []).map(normalizeText_);
    for (let i = 0; i < names.length; i++) {
      if (cands.indexOf(names[i]) >= 0) { idx[col] = i; break; }
    }
  });
  return idx;
}

/** 全角英数記号→半角、全角スペース→半角、前後空白除去（NFKCで半角カナは全角カナに揃う） */
function normalizeText_(v) {
  return String(v == null ? '' : v).normalize('NFKC').replace(/[‐-―−－ー](?=\d)|(?<=\d)[‐-―−－]/g, '-').replace(/\s+/g, ' ').trim();
}

/** 2026年10月5日 / 2026-10-05 / 2026/10/5 10:30 / 20261005 → 2026/10/05。読めなければ null */
function normalizeDate_(s) {
  if (s === '') return null;
  let m = /^(\d{4})[\/\-.年](\d{1,2})[\/\-.月](\d{1,2})日?(?:\s+\d{1,2}:\d{2}(?::\d{2})?)?$/.exec(s) || /^(\d{4})(\d{2})(\d{2})$/.exec(s);
  if (!m) return null;
  const y = +m[1], mo = +m[2], d = +m[3];
  const dt = new Date(Date.UTC(y, mo - 1, d));
  if (dt.getUTCFullYear() !== y || dt.getUTCMonth() !== mo - 1 || dt.getUTCDate() !== d) return null; // 2/30 など
  return `${y}/${String(mo).padStart(2, '0')}/${String(d).padStart(2, '0')}`;
}

/** "1,200円" "¥1200" → 1200。読めなければ null */
function normalizeNumber_(s) {
  const t = String(s).replace(/[,円¥\s]/g, '');
  if (t === '' || !/^-?\d+(\.\d+)?$/.test(t)) return null;
  return Number(t);
}

function prepareOutputSheet_() {
  const ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(SETTINGS.outputSheetName) || ss.insertSheet(SETTINGS.outputSheetName);
  const header = SETTINGS.outputColumns.concat([SETTINGS.noteColumn]);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, header.length).setValues([header]);
    sheet.setFrozenRows(1);
  }
  SETTINGS.outputColumns.forEach((c, i) => {
    if (c === '注文日' || SETTINGS.textColumns.indexOf(c) >= 0) sheet.getRange(2, i + 1, sheet.getMaxRows() - 1, 1).setNumberFormat('@');
  });
  return sheet;
}

function createLogSheet_(ss) {
  const log = ss.insertSheet(SETTINGS.logSheetName);
  log.appendRow(['ファイルID', 'ファイル名', '追加行数', '取込日時']);
  return log;
}

function loadDoneIds_(log) {
  const ids = {};
  if (!log || log.getLastRow() < 2) return ids;
  log.getRange(2, 1, log.getLastRow() - 1, 1).getValues().forEach(([id]) => { ids[id] = true; });
  return ids;
}

/** 既にシートにある行の重複キーを集める（取り込み済みデータとの重複も防ぐ） */
function loadExistingKeys_(sheet) {
  const seen = {};
  const last = sheet.getLastRow();
  if (last < 2) return seen;
  const data = sheet.getRange(2, 1, last - 1, SETTINGS.outputColumns.length).getValues();
  data.forEach(row => {
    const rec = {};
    SETTINGS.outputColumns.forEach((c, i) => { rec[c] = String(row[i]); });
    seen[SETTINGS.dedupeKeys.map(k => rec[k]).join('\u0001')] = true;
  });
  return seen;
}

/** 結果メール（任意）。1日の送信上限を確認してから送る */
function notify_(msg) {
  if (!SETTINGS.notifyEmail) return;
  if (MailApp.getRemainingDailyQuota() < 1) { Logger.log('メール送信上限のため通知を省略'); return; }
  try {
    MailApp.sendEmail(SETTINGS.notifyEmail, '[CSV整形] 取り込み結果', msg);
  } catch (e) {
    Logger.log('通知メール失敗: ' + e.message);
  }
}