// sheetsdata.jsx — 案件資料的 Google Sheets 讀寫層
// 取代 main.jsx 原本 seed() 用的寫死 SEED_CASES：改成從共用 Google 試算表載入／寫回。
// 試算表結構對應「案件追蹤及工作排程」這份真實表單（見「案件詳細資料(App)」分頁註解）。

const SHEET_TABS = {
  biz:      '案件整理與追蹤-商業合作',
  bizDev:   '「案件整理與追蹤-商業合作開發」',
  schedule: '工作排程',
  detail:   '案件詳細資料(App)',
};

function getSheetId(){ return (localStorage.getItem('gm_sheet_id')||'').trim(); }
function setSheetId(id){ localStorage.setItem('gm_sheet_id', (id||'').trim()); }

async function sheetsApi(method, pathAndQuery, body){
  const token = window.getGoogleAccessToken && window.getGoogleAccessToken();
  if (!token) throw new Error('尚未連結 Google 帳號');
  const res = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${pathAndQuery}`, {
    method,
    headers:{ 'Authorization':`Bearer ${token}`, 'Content-Type':'application/json' },
    body: body ? JSON.stringify(body) : undefined,
  });
  if (!res.ok){ const e = await res.json().catch(()=>({})); throw new Error(e.error?.message || `Sheets API 錯誤 (${res.status})`); }
  return res.json();
}

const moneyToNum = (s) => { if (s==null||s==='') return null; const n = String(s).replace(/[^\d.-]/g,''); return n!=='' ? +n : null; };
const numToMoney = (n) => (n==null||n==='') ? '' : 'NT$' + Math.round(+n).toLocaleString('en-US');
function safeParseJSON(s, fallback){ if (!s) return fallback; try { return JSON.parse(s); } catch { return fallback; } }
function j(v){ return v==null ? '' : JSON.stringify(v); }

// ── 讀取：把 4 個分頁 join 成 app 用的 case 陣列 ──────────────
async function loadCasesFromSheet(){
  const sheetId = getSheetId();
  if (!sheetId) throw new Error('尚未設定 Spreadsheet ID（請到 AI 自動化設定填入）');
  const ranges = [
    `${SHEET_TABS.biz}!A2:M1000`,
    `${SHEET_TABS.bizDev}!A2:L1000`,
    `${SHEET_TABS.schedule}!A2:S1000`,
    `${SHEET_TABS.detail}!A2:Q1000`,
  ];
  const q = ranges.map(r=>'ranges='+encodeURIComponent(r)).join('&');
  const res = await sheetsApi('GET', `${sheetId}/values:batchGet?${q}`);
  const [bizRows, bizDevRows, schedRows, detailRows] = res.valueRanges.map(v=>v.values||[]);

  const cases = {};
  const rowIndex = {}; // id -> { biz, bizDev, schedule, detail } (1-based sheet row number, header=1)
  const ensure = (id) => { if (!cases[id]) { cases[id] = { id }; rowIndex[id] = {}; } return cases[id]; };

  bizRows.forEach((r, i) => {
    const id = (r[12]||'').trim(); if (!id) return;
    const c = ensure(id); rowIndex[id].biz = i + 2;
    Object.assign(c, {
      source:'自來信', received:r[0]||'', caseType:r[1]||'純業配', product:r[2]||'', vendor:r[3]||'',
      scheduleHint:r[4]||'', willing:r[5]||'有興趣', value: moneyToNum(r[6]), progress:r[7]||'洽談', deal:r[8]||'',
      contact:r[9]||'', note:r[10]||'',
    });
  });
  bizDevRows.forEach((r, i) => {
    const id = (r[11]||'').trim(); if (!id) return;
    const c = ensure(id); rowIndex[id].bizDev = i + 2;
    Object.assign(c, {
      source:'開發信', received:r[0]||'', caseType:r[1]||'純業配', product: c.product||r[2]||'', vendor: c.vendor||r[3]||'',
      scheduleHint:r[4]||'', value: c.value!=null?c.value:moneyToNum(r[5]), progress:r[6]||'洽談', deal:r[7]||'',
      contact:r[8]||'', note:r[9]||'',
    });
  });
  schedRows.forEach((r, i) => {
    const id = (r[18]||'').trim(); if (!id) return;
    const c = ensure(id); rowIndex[id].schedule = i + 2;
    c.signedDate = r[0]||'';
    c.prod = { draft:r[4]||'', draftFix:r[5]||'', script:r[6]||'', scriptFix:r[7]||'', shoot:r[8]||'', post:r[9]||'',
      deliver:r[10]||'', finalFix:r[11]||'', online:r[12]||'' };
    c.schedule = { shoot:r[8]||'', online:r[12]||'' };
    c.schedProgress = r[15]||'';
    c.pay = { dep:{ paid: !!(r[16]||'').trim(), date:r[16]||'' }, bal:{ paid: !!(r[17]||'').trim(), date:r[17]||'' } };
  });
  detailRows.forEach((r, i) => {
    const id = (r[0]||'').trim(); if (!id) return;
    const c = ensure(id); rowIndex[id].detail = i + 2;
    const extra = safeParseJSON(r[16], {});
    Object.assign(c, {
      product: c.product || r[1] || '', vendor: c.vendor || r[2] || '',
      email:r[3]||'', riskKind:r[4]||'general', risk:r[5]||'mid',
      riskFindings: safeParseJSON(r[6], null),
      intentStatus: r[7]||'draft',
      quote: safeParseJSON(r[8], { items:[], situation:'s1', rush:false, groupFee:8000, share:15, adjusts:{} }),
      quoteSentDate: r[9]||'',
      comm: safeParseJSON(r[10], null),
      io: safeParseJSON(r[11], null),
      invoices: safeParseJSON(r[12], {}),
      aiDraftStatus: r[13]||'', updatedAt: r[14]||'', appNote: r[15]||'',
      contactRole: extra.contactRole||'', budgetHint: extra.budgetHint||'—', wants: extra.wants||['待確認'],
      riskStatus: extra.riskStatus||'idle', avatarBg: extra.avatarBg||'var(--clay-2)',
      intent: extra.intent || { vendor: c.vendor, product: c.product, nature:[], schedule:'', link:'', ref:'' },
      productLink: extra.productLink||'',
    });
  });

  const list = Object.values(cases).map(c => {
    // 案件如果只存在「案件詳細資料(App)」分頁、商業合作/商業合作開發分頁沒有對應列（孤兒案件，
    // 通常是資料輸入錯誤造成案件ID對不上），這幾個欄位就不會被設到，給預設值避免後面畫面噴錯
    c.source = c.source || '自來信'; c.received = c.received || '';
    c.caseType = c.caseType || '純業配'; c.willing = c.willing || '有興趣';
    c.progress = c.progress || '洽談'; c.deal = c.deal || ''; c.contact = c.contact || ''; c.note = c.note || '';
    c.riskLabel = c.riskLabel || ({high:'需注意',mid:'待評估',low:'低風險'}[c.risk] || '待評估');
    c.stage = window.stageFromCase(c);
    if (!c.quote) c.quote = { items:[], situation:'s1', rush:false, groupFee:8000, share:15, adjusts:{} };
    if (!c.schedule) c.schedule = { shoot:'', online:'' };
    if (!c.io) c.io = window.makeIO(c);
    if (!c.comm) c.comm = window.makeComm(c);
    if (!c.prod) c.prod = window.makeProd(c);
    if (!c.pay) c.pay = window.makePay(c);
    if (!c.invoices) c.invoices = {};
    if (!c.intentStatus) c.intentStatus = c.progress==='婉拒' ? 'declined' : 'draft';
    return c;
  });
  return { cases: list, rowIndex };
}

// ── 寫回：單一案件更新到對應分頁的對應列 ──────────────────────
async function saveCaseToSheet(c, rowIndex){
  const sheetId = getSheetId();
  if (!sheetId) return; // 沒設定就靜默跳過，不影響本機操作
  const idx = rowIndex[c.id] || {};
  const data = [];
  const bizTab = c.source==='開發信' ? SHEET_TABS.bizDev : SHEET_TABS.biz;
  const bizRow = c.source==='開發信' ? idx.bizDev : idx.biz;
  if (bizRow){
    const row = c.source==='開發信'
      ? [c.received, c.caseType, c.product, c.vendor, c.scheduleHint||'', numToMoney(c.value), c.progress, c.deal||'', c.contact||'', c.note||'']
      : [c.received, c.caseType, c.product, c.vendor, c.scheduleHint||'', c.willing, numToMoney(c.value), c.progress, c.deal||'', c.contact||'', c.note||''];
    data.push({ range: `${bizTab}!A${bizRow}`, values: [row] });
  }
  if (idx.schedule && c.prod){
    const P = c.prod;
    data.push({ range: `${SHEET_TABS.schedule}!E${idx.schedule}`, values: [[P.draft||'', P.draftFix||'', P.script||'', P.scriptFix||'', P.shoot||'', P.post||'', P.deliver||'', P.finalFix||'', P.online||'']] });
    if (c.pay) data.push({ range: `${SHEET_TABS.schedule}!Q${idx.schedule}`, values: [[c.pay.dep?.date||'', c.pay.bal?.date||'']] });
  }
  if (idx.detail){
    const extra = { contactRole:c.contactRole, budgetHint:c.budgetHint, wants:c.wants, riskStatus:c.riskStatus, avatarBg:c.avatarBg, intent:c.intent, productLink:c.productLink };
    data.push({ range: `${SHEET_TABS.detail}!D${idx.detail}`, values: [[
      c.email||'', c.riskKind||'general', c.risk||'mid', j(c.riskFindings), c.intentStatus||'draft',
      j(c.quote), c.quoteSentDate||'', j(c.comm), j(c.io), j(c.invoices), c.aiDraftStatus||'',
      new Date().toISOString(), c.appNote||'', j(extra),
    ]] });
  }
  if (data.length) await sheetsApi('POST', `${sheetId}/values:batchUpdate`, { valueInputOption:'RAW', data });

  // 孤兒案件（案件詳細資料(App)有資料，但商業合作/商業合作開發分頁從沒有對應列）：
  // 補一列上去，這樣進度/備註等異動才有地方寫，不會每次都靜默跳過
  if (!bizRow){
    const appendRes = c.source==='開發信'
      ? await sheetsApi('POST', `${sheetId}/values/${encodeURIComponent(SHEET_TABS.bizDev+'!A1')}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`, {
          values: [[c.received, c.caseType, c.product, c.vendor, c.scheduleHint||'', numToMoney(c.value), c.progress, c.deal||'', c.contact||'', c.note||'', '', c.id]],
        })
      : await sheetsApi('POST', `${sheetId}/values/${encodeURIComponent(SHEET_TABS.biz+'!A1')}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`, {
          values: [[c.received, c.caseType, c.product, c.vendor, c.scheduleHint||'', c.willing, numToMoney(c.value), c.progress, c.deal||'', c.contact||'', c.note||'', '', c.id]],
        });
    const newRow = parseAppendedRow(appendRes);
    return { newRowIndex: c.source==='開發信' ? { bizDev: newRow } : { biz: newRow } };
  }
}

// ── 新增：建立全新案件，append 進商業合作 + 案件詳細資料(App) ──
function nextCaseId(rowIndex){
  let max = 0;
  Object.keys(rowIndex).forEach(id=>{ const m = id.match(/^C-(\d{4})-(\d+)$/); if (m) max = Math.max(max, +m[2]); });
  const year = new Date().getFullYear();
  return `C-${year}-${String(max+1).padStart(3,'0')}`;
}
// values:append 回應的 updates.updatedRange 形如 "'分頁名'!A23:M23"，從中取出實際寫入的列號
function parseAppendedRow(res){
  const m = (res?.updates?.updatedRange || '').match(/![A-Z]+(\d+)/);
  return m ? +m[1] : null;
}
async function appendCaseToSheet(c, rowIndex){
  const sheetId = getSheetId();
  if (!sheetId) return null;
  const id = c.id || nextCaseId(rowIndex);
  const bizRes = await sheetsApi('POST', `${sheetId}/values/${encodeURIComponent(SHEET_TABS.biz+'!A1')}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`, {
    values: [[c.received, c.caseType, c.product, c.vendor, '', c.willing, numToMoney(c.value), c.progress, '', c.contact||'', c.note||'', '', id]],
  });
  const extra = { contactRole:c.contactRole, budgetHint:c.budgetHint, wants:c.wants, riskStatus:c.riskStatus, avatarBg:c.avatarBg, intent:c.intent, productLink:c.productLink };
  const detailRes = await sheetsApi('POST', `${sheetId}/values/${encodeURIComponent(SHEET_TABS.detail+'!A1')}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`, {
    values: [[id, c.product, c.vendor, c.email||'', c.riskKind||'general', c.risk||'mid', j(c.riskFindings), c.intentStatus||'draft',
      j(c.quote), '', j(c.comm), j(c.io), j(c.invoices), '', new Date().toISOString(), '', j(extra)]],
  });
  return { id, rowIndex: { biz: parseAppendedRow(bizRes), detail: parseAppendedRow(detailRes) } };
}

Object.assign(window, { getSheetId, setSheetId, loadCasesFromSheet, saveCaseToSheet, appendCaseToSheet, nextCaseId });