// ================================================================
// 자산관리 포트폴리오 구글 시트 - 증권사별 일반 계좌 5개
// 사용법: 구글 시트 → 확장 프로그램 → Apps Script → 붙여넣기 → 저장 → onOpen 실행
// ================================================================

// ================================================================
// onOpen — 메뉴 등록
// ================================================================
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('📊 포트폴리오')
    .addItem('🏗️ 시트 초기 설정', 'setupAllSheets')
    .addSeparator()
    .addItem('🔄 전체 시세 업데이트', 'updateAllPrices')
    .addItem('📈 대시보드 새로고침', 'refreshDashboard')
    .addSeparator()
    .addItem('📅 지금 분기 수동 기록', 'manualQuarterRecord')
    .addItem('⏰ 분기 자동기록 트리거 등록 (최초 1회)', 'registerDailyTrigger')
    .addSeparator()
    .addItem('🔢 KRX 종목코드 앞자리 0 복원', 'fixTickerFormat')
    .addItem('➕ 종목 추가 (현재 시트)', 'addStockRow')
    .addToUi();
}

// ================================================================
// onEdit — A열 종목코드 or C열 시장 입력 시 현재가 수식 자동 삽입
// ================================================================
function onEdit(e) {
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();
  var validSheets = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];
  if (validSheets.indexOf(sheetName) === -1) return;

  var col = e.range.getColumn();
  var row = e.range.getRow();
  if ((col !== 1 && col !== 3) || row < 3 || row > 52) return;

  var tickerCell = sheet.getRange(row, 1);
  var mktCell    = sheet.getRange(row, 3);

  if (col === 1) {
    var raw = tickerCell.getValue();
    if (!raw) {
      sheet.getRange(row, 6, 1, 7).clearContent();
      return;
    }
    if (typeof raw === 'number') {
      tickerCell.setNumberFormat('@');
      tickerCell.setValue(String(Math.floor(raw)).padStart(6, '0'));
    } else {
      tickerCell.setNumberFormat('@');
    }
  }

  var ticker = tickerCell.getValue();
  var mkt    = mktCell.getValue();
  if (!ticker || !mkt) return;

  insertPriceFormulas(sheet, row, ticker, mkt);
}

// ================================================================
// 현재가 수식 삽입 (KRX / 해외 공통)
// ================================================================
function insertPriceFormulas(sheet, row, ticker, mkt) {
  if (mkt === 'KRX') {
    sheet.getRange(row, 6).setFormula(
      '=IFERROR(GOOGLEFINANCE("KRX:"&TEXT(A' + row + ',"000000"),"price"),' +
      'IFERROR(GOOGLEFINANCE("KOSPI:"&TEXT(A' + row + ',"000000"),"price"),' +
      'IFERROR(GOOGLEFINANCE("KOSDAQ:"&TEXT(A' + row + ',"000000"),"price"),"조회불가")))'
    );
    sheet.getRange(row, 7).setFormula('=IF(F' + row + '="조회불가","",IF(F' + row + '=0,"",F' + row + '*D' + row + '))');
    sheet.getRange(row, 8).setFormula('=IF(D' + row + '*E' + row + '=0,"",D' + row + '*E' + row + ')');
  } else {
    sheet.getRange(row, 6).setFormula(
      '=IFERROR(GOOGLEFINANCE("' + ticker + '","price"),"조회불가")'
    );
    sheet.getRange(row, 7).setFormula('=IF(F' + row + '="조회불가","",F' + row + '*D' + row + '*대시보드!$F$2)');
    sheet.getRange(row, 8).setFormula('=IF(D' + row + '*E' + row + '=0,"",D' + row + '*E' + row + '*대시보드!$F$2)');
  }
  sheet.getRange(row, 9).setFormula('=IF(OR(G' + row + '="",H' + row + '=""),"",G' + row + '-H' + row + ')');
  sheet.getRange(row, 10).setFormula('=IFERROR(I' + row + '/H' + row + ',0)');
  sheet.getRange(row, 11).setFormula('=IFERROR(G' + row + '/G53,0)');
  sheet.getRange(row, 12).setFormula('=IFERROR(G' + row + '/대시보드!C11,0)');
}

// ================================================================
// 전체 시트 초기 설정
// ================================================================
function setupAllSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  var response = ui.alert('초기 설정', '기존 시트를 모두 삭제하고 새로 설정합니까?\n(기존 데이터가 삭제됩니다)', ui.ButtonSet.YES_NO);
  if (response !== ui.Button.YES) return;

  var existing = ss.getSheets();
  existing.forEach(function(s) { try { ss.deleteSheet(s); } catch(e) {} });

  var accounts = [
    { id: '계좌1_키움', label: '키움증권',   bg: '#E3F2FD', hd: '#1565C0' },
    { id: '계좌2_미래', label: '미래에셋',   bg: '#E8F5E9', hd: '#2E7D32' },
    { id: '계좌3_삼성', label: '삼성증권',   bg: '#FFF8E1', hd: '#F57F17' },
    { id: '계좌4_NH',  label: 'NH투자증권', bg: '#FCE4EC', hd: '#AD1457' },
    { id: '계좌5_토스', label: '토스증권',   bg: '#F3E5F5', hd: '#6A1B9A' }
  ];

  createDashboardSheet(ss, accounts);
  accounts.forEach(function(a) { createAccountSheet(ss, a); });

  ss.setActiveSheet(ss.getSheetByName('대시보드'));
  ss.moveActiveSheet(1);

  updateAllPrices();

  ui.alert('✅ 설정 완료',
    '포트폴리오 시트가 생성되었습니다!\n\n' +
    '각 계좌 시트에서 종목을 입력한 뒤\n[포트폴리오 > 전체 시세 업데이트]를 클릭하세요.\n\n' +
    '💡 증권사명은 각 시트 A1셀을 직접 수정할 수 있습니다.',
    ui.ButtonSet.OK);
}

// ================================================================
// 계좌 시트 생성
// ================================================================
function createAccountSheet(ss, acct) {
  var sheet = ss.getSheetByName(acct.id);
  if (!sheet) sheet = ss.insertSheet(acct.id);
  else sheet.clear();

  var widths = [120, 180, 90, 100, 120, 120, 130, 130, 120, 95, 95, 95];
  widths.forEach(function(w, i) { sheet.setColumnWidth(i + 1, w); });

  sheet.setRowHeight(1, 42);
  var t = sheet.getRange('A1:L1');
  t.merge();
  t.setValue('💼 ' + acct.label + ' 포트폴리오');
  t.setFontSize(14).setFontWeight('bold').setFontColor('#FFFFFF')
   .setBackground(acct.hd).setHorizontalAlignment('center').setVerticalAlignment('middle');

  sheet.setRowHeight(2, 30);
  var headers = ['종목코드','종목명','시장','보유수량','평균매입가','현재가','평가금액(₩)','매입금액(₩)','손익(₩)','수익률','계좌비중','전체비중'];
  var hr = sheet.getRange(2, 1, 1, 12);
  hr.setValues([headers]);
  hr.setFontWeight('bold').setFontColor('#FFFFFF').setBackground(acct.hd).setHorizontalAlignment('center');

  for (var r = 3; r <= 52; r++) {
    var bg = (r % 2 === 0) ? acct.bg : '#FFFFFF';
    sheet.getRange(r, 1, 1, 12).setBackground(bg);
    sheet.getRange(r, 1).setNumberFormat('@');
    sheet.getRange(r, 4).setNumberFormat('#,##0');
    sheet.getRange(r, 5).setNumberFormat('#,##0.00');
    sheet.getRange(r, 6).setNumberFormat('#,##0.00');
    sheet.getRange(r, 7).setNumberFormat('#,##0');
    sheet.getRange(r, 8).setNumberFormat('#,##0');
    sheet.getRange(r, 9).setNumberFormat('[Blue]#,##0;[Red](#,##0);"-"');
    sheet.getRange(r, 10).setNumberFormat('[Blue]0.00%;[Red](0.00%);"-"');
    sheet.getRange(r, 11).setNumberFormat('0.00%');
    sheet.getRange(r, 12).setNumberFormat('0.00%');
  }

  var samples = getSampleData(acct.id);
  if (samples.length > 0) {
    sheet.getRange(3, 1, samples.length, 5).setValues(samples);
  }

  sheet.setRowHeight(53, 32);
  var sr = sheet.getRange(53, 1, 1, 12);
  sr.setBackground(acct.hd).setFontColor('#FFFFFF').setFontWeight('bold');
  sheet.getRange(53, 1).setValue('합계');
  sheet.getRange(53, 7).setFormula('=SUMPRODUCT((G3:G52<>"")*G3:G52)');
  sheet.getRange(53, 8).setFormula('=SUMPRODUCT((H3:H52<>"")*H3:H52)');
  sheet.getRange(53, 9).setFormula('=G53-H53');
  sheet.getRange(53, 10).setFormula('=IFERROR(I53/H53,0)');
  sheet.getRange(53, 7).setNumberFormat('#,##0');
  sheet.getRange(53, 8).setNumberFormat('#,##0');
  sheet.getRange(53, 9).setNumberFormat('[Blue]#,##0;[Red](#,##0)');
  sheet.getRange(53, 10).setNumberFormat('[Blue]0.00%;[Red](0.00%)');

  sheet.getRange(55, 1, 1, 8).merge();
  sheet.getRange(55, 1).setValue('💡 시장(C열): KRX(한국) / NYSE / NASDAQ 중 하나 입력하면 현재가 자동 조회');
  sheet.getRange(55, 1).setFontColor('#757575').setFontStyle('italic');
  sheet.getRange(56, 1, 1, 8).merge();
  sheet.getRange(56, 1).setValue('💡 평균매입가: KRX는 원화(₩), 해외 종목은 달러($)로 입력 — 원화 환산은 대시보드 환율 자동 적용');
  sheet.getRange(56, 1).setFontColor('#757575').setFontStyle('italic');

  sheet.getRange(2, 1, 51, 12).setBorder(true, true, true, true, true, true, '#BDBDBD', SpreadsheetApp.BorderStyle.SOLID);
}

function getSampleData(id) {
  var map = {
    '계좌1_키움': [['005930','삼성전자','KRX',10,72000],['035420','NAVER','KRX',5,185000],['AAPL','Apple Inc.','NASDAQ',3,185]],
    '계좌2_미래': [['069500','KODEX 200','KRX',20,32000],['VOO','Vanguard S&P500','NYSE',2,420]],
    '계좌3_삼성': [['005380','현대차','KRX',5,210000],['MSFT','Microsoft','NASDAQ',2,380]],
    '계좌4_NH':  [['035720','카카오','KRX',8,45000],['QQQ','Invesco QQQ','NASDAQ',1,430]],
    '계좌5_토스': [['000660','SK하이닉스','KRX',6,135000],['SCHD','Schwab Dividend','NYSE',10,78]]
  };
  return map[id] || [];
}

// ================================================================
// 대시보드 시트 생성
// ================================================================
function createDashboardSheet(ss, accounts) {
  var sheet = ss.getSheetByName('대시보드');
  if (!sheet) sheet = ss.insertSheet('대시보드');
  else sheet.clear();

  [30,200,160,160,130,110,110,30,180,140].forEach(function(w, i) { sheet.setColumnWidth(i+1, w); });

  sheet.setRowHeight(1, 52);
  var t = sheet.getRange('B1:G1');
  t.merge().setValue('📊 전체 자산 포트폴리오 대시보드');
  t.setFontSize(18).setFontWeight('bold').setFontColor('#1A237E')
   .setBackground('#E8EAF6').setHorizontalAlignment('center').setVerticalAlignment('middle');

  sheet.setRowHeight(2, 26);
  sheet.getRange('B2').setValue('🕐 마지막 업데이트:');
  sheet.getRange('C2').setValue('업데이트 필요').setFontColor('#E53935').setFontWeight('bold');
  sheet.getRange('E2').setValue('💱 USD/KRW 환율:');
  sheet.getRange('F2').setValue(1380).setNumberFormat('#,##0 "원"').setFontColor('#1565C0').setFontWeight('bold');
  sheet.getRange('G2').setValue('← 직접 입력').setFontColor('#9E9E9E').setFontStyle('italic');

  sheet.setRowHeight(3, 12);
  sheet.setRowHeight(4, 36);
  var sh = sheet.getRange('B4:G4');
  sh.merge().setValue('   💼 계좌별 요약');
  sh.setFontSize(13).setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#283593').setVerticalAlignment('middle');

  sheet.setRowHeight(5, 28);
  var hdr = sheet.getRange(5, 2, 1, 6);
  hdr.setValues([['증권사 / 계좌명','평가금액(₩)','매입금액(₩)','손익(₩)','수익률','비중']]);
  hdr.setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#3949AB').setHorizontalAlignment('center');

  var colors   = ['#E3F2FD','#E8F5E9','#FFF8E1','#FCE4EC','#F3E5F5'];
  var labels   = ['키움증권','미래에셋','삼성증권','NH투자증권','토스증권'];
  var sheetIds = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];

  for (var i = 0; i < 5; i++) {
    var row = 6 + i;
    sheet.setRowHeight(row, 28);
    sheet.getRange(row, 2).setValue(labels[i]).setFontWeight('bold');
    sheet.getRange(row, 3).setFormula("='" + sheetIds[i] + "'!G53").setNumberFormat('#,##0');
    sheet.getRange(row, 4).setFormula("='" + sheetIds[i] + "'!H53").setNumberFormat('#,##0');
    sheet.getRange(row, 5).setFormula("='" + sheetIds[i] + "'!I53").setNumberFormat('[Blue]#,##0;[Red](#,##0)');
    sheet.getRange(row, 6).setFormula('=IFERROR(E' + row + '/D' + row + ',0)').setNumberFormat('[Blue]0.00%;[Red](0.00%)');
    sheet.getRange(row, 7).setNumberFormat('0.00%');
    sheet.getRange(row, 2, 1, 6).setBackground(colors[i]);
    sheet.getRange(row, 2, 1, 6).setBorder(true,true,true,true,true,true,'#BDBDBD',SpreadsheetApp.BorderStyle.SOLID);
  }

  sheet.setRowHeight(11, 36);
  var tr = sheet.getRange(11, 2, 1, 6);
  tr.setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#1A237E');
  sheet.getRange(11, 2).setValue('📌 전체 합계');
  sheet.getRange(11, 3).setFormula('=SUM(C6:C10)').setNumberFormat('#,##0');
  sheet.getRange(11, 4).setFormula('=SUM(D6:D10)').setNumberFormat('#,##0');
  sheet.getRange(11, 5).setFormula('=SUM(E6:E10)').setNumberFormat('[Blue]#,##0;[Red](#,##0)');
  sheet.getRange(11, 6).setFormula('=IFERROR(E11/D11,0)').setNumberFormat('[Blue]0.00%;[Red](0.00%)');
  sheet.getRange(11, 7).setValue(1).setNumberFormat('0.00%');

  for (var j = 0; j < 5; j++) {
    sheet.getRange(6+j, 7).setFormula('=IFERROR(C' + (6+j) + '/C11,0)');
  }

  sheet.setRowHeight(12, 14);
  sheet.setRowHeight(13, 36);
  var ch = sheet.getRange('B13:G13');
  ch.merge().setValue('   📈 계좌별 자산 비중 & 수익률 차트');
  ch.setFontSize(13).setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#283593').setVerticalAlignment('middle');

  // 차트용 데이터 (J~L열)
  sheet.getRange('J4').setValue('차트용 데이터').setFontWeight('bold').setBackground('#E8EAF6');
  sheet.getRange('J5').setValue('계좌명');
  sheet.getRange('K5').setValue('평가금액(₩)');
  sheet.getRange('L5').setValue('수익률(%)');
  for (var k = 0; k < 5; k++) {
    sheet.getRange(6+k, 10).setValue(labels[k]);
    sheet.getRange(6+k, 11).setFormula('=C' + (6+k)).setNumberFormat('#,##0');
    sheet.getRange(6+k, 12).setFormula('=F' + (6+k) + '*100').setNumberFormat('0.00');
  }
}

// ================================================================
// 시세 업데이트
// ================================================================
function updateAllPrices() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetIds = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];

  sheetIds.forEach(function(id) {
    var s = ss.getSheetByName(id);
    if (s) updateSheetPrices(s);
  });

  updateGlobalWeights();

  var dash = ss.getSheetByName('대시보드');
  if (dash) {
    dash.getRange('C2')
      .setValue(Utilities.formatDate(new Date(), 'Asia/Seoul', 'yyyy-MM-dd HH:mm:ss'))
      .setFontColor('#2E7D32').setFontWeight('bold');
  }

  refreshDashboard();

  SpreadsheetApp.getUi().alert('✅ 업데이트 완료',
    '모든 계좌 시세가 업데이트되었습니다!\n\n' +
    '• KRX 종목 → KRX / KOSPI / KOSDAQ 순 자동 시도\n' +
    '• 해외 종목 → 달러 × 대시보드 환율 자동 환산\n\n' +
    '⚠️ 일부 ETF는 구글 파이낸스 미지원 → F열에 직접 입력',
    SpreadsheetApp.getUi().ButtonSet.OK);
}

function updateSheetPrices(sheet) {
  for (var row = 3; row <= 52; row++) {
    var ticker = sheet.getRange(row, 1).getValue();
    var qty    = sheet.getRange(row, 4).getValue();
    var avg    = sheet.getRange(row, 5).getValue();
    var mkt    = sheet.getRange(row, 3).getValue();
    if (!ticker || !qty || !avg) continue;
    insertPriceFormulas(sheet, row, ticker, mkt);
  }
}

function updateGlobalWeights() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetIds = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];
  sheetIds.forEach(function(id) {
    var s = ss.getSheetByName(id);
    if (!s) return;
    for (var row = 3; row <= 52; row++) {
      if (!s.getRange(row, 1).getValue()) continue;
      s.getRange(row, 12).setFormula('=IFERROR(G' + row + '/대시보드!C11,0)');
    }
  });
}

// ================================================================
// 대시보드 차트 새로고침
// ================================================================
function refreshDashboard() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dash = ss.getSheetByName('대시보드');
  if (!dash) return;

  dash.getCharts().forEach(function(c) { dash.removeChart(c); });

  var pieChart = dash.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(dash.getRange('J5:K10'))
    .setOption('title', '계좌별 자산 비중')
    .setOption('titleTextStyle', {fontSize:14, bold:true, color:'#1A237E'})
    .setOption('pieHole', 0.42)
    .setOption('legend', {position:'right', textStyle:{fontSize:11}})
    .setOption('slices', {
      0:{color:'#1565C0'}, 1:{color:'#2E7D32'},
      2:{color:'#F57F17'}, 3:{color:'#AD1457'}, 4:{color:'#6A1B9A'}
    })
    .setOption('backgroundColor', '#FAFAFA')
    .setNumHeaders(1)
    .setPosition(14, 2, 10, 10)
    .build();
  dash.insertChart(pieChart);

  var barChart = dash.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(dash.getRange('J5:J10'))
    .addRange(dash.getRange('L5:L10'))
    .setOption('title', '계좌별 수익률 (%)')
    .setOption('titleTextStyle', {fontSize:14, bold:true, color:'#1A237E'})
    .setOption('hAxis', {title:'수익률 (%)', format:'0.00'})
    .setOption('colors', ['#3949AB'])
    .setOption('backgroundColor', '#FAFAFA')
    .setOption('legend', {position:'none'})
    .setNumHeaders(1)
    .setPosition(14, 9, 10, 10)
    .build();
  dash.insertChart(barChart);
}

// ================================================================
// 종목 추가
// ================================================================
function addStockRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var valid = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];
  if (valid.indexOf(sheet.getName()) === -1) {
    SpreadsheetApp.getUi().alert('⚠️','계좌 시트에서만 종목을 추가할 수 있습니다.',SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
  var last = 2;
  for (var r = 52; r >= 3; r--) {
    if (sheet.getRange(r, 1).getValue()) { last = r; break; }
  }
  if (last >= 52) {
    SpreadsheetApp.getUi().alert('⚠️','최대 50종목까지 입력 가능합니다.',SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
  sheet.getRange(last + 1, 1).activate();
  SpreadsheetApp.getUi().alert('✅', (last+1) + '행에 입력하세요\n\nA: 종목코드  B: 종목명  C: 시장(KRX/NYSE/NASDAQ)\nD: 보유수량  E: 평균매입가', SpreadsheetApp.getUi().ButtonSet.OK);
}

// ================================================================
// KRX 종목코드 앞자리 0 복원
// ================================================================
function fixTickerFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetIds = ['계좌1_키움','계좌2_미래','계좌3_삼성','계좌4_NH','계좌5_토스'];
  var fixed = 0;

  sheetIds.forEach(function(id) {
    var s = ss.getSheetByName(id);
    if (!s) return;
    for (var row = 3; row <= 52; row++) {
      var cell = s.getRange(row, 1);
      var mkt  = s.getRange(row, 3).getValue();
      var val  = cell.getValue();
      if (!val || mkt !== 'KRX') continue;
      if (typeof val === 'number') {
        var padded = String(Math.floor(val)).padStart(6, '0');
        cell.setNumberFormat('@');
        cell.setValue(padded);
        fixed++;
      }
    }
  });

  SpreadsheetApp.getUi().alert('✅ 완료', fixed + '개 종목코드 앞자리 0 복원\n\n[전체 시세 업데이트]를 다시 실행해주세요.', SpreadsheetApp.getUi().ButtonSet.OK);
}

// ================================================================
// 분기별 자산 자동 기록 시스템
// ================================================================

function isLastDayOfQuarter(date) {
  var month = date.getMonth() + 1;
  var day   = date.getDate();
  return (month === 3  && day === 31) ||
         (month === 6  && day === 30) ||
         (month === 9  && day === 30) ||
         (month === 12 && day === 31);
}

function getQuarterLabel(date) {
  var year  = date.getFullYear();
  var month = date.getMonth() + 1;
  var q = month <= 3 ? 1 : month <= 6 ? 2 : month <= 9 ? 3 : 4;
  return year + ' Q' + q;
}

function dailyQuarterCheck() {
  var today = new Date();
  if (!isLastDayOfQuarter(today)) return;
  recordQuarterSnapshot(today);
}

function recordQuarterSnapshot(date) {
  var ss      = SpreadsheetApp.getActiveSpreadsheet();
  var label   = getQuarterLabel(date);
  var dateStr = Utilities.formatDate(date, 'Asia/Seoul', 'yyyy-MM-dd');

  var dash = ss.getSheetByName('대시보드');
  if (!dash) return;

  var accountValues = [];
  for (var i = 0; i < 5; i++) {
    accountValues.push(dash.getRange(6 + i, 3).getValue() || 0);
  }
  var total = dash.getRange(11, 3).getValue() || 0;

  var recSheet = ss.getSheetByName('분기별_기록');
  if (!recSheet) {
    recSheet = ss.insertSheet('분기별_기록');
    setupQuarterSheet(recSheet);
  }

  var lastRow    = recSheet.getLastRow();
  var targetRow  = lastRow + 1;
  for (var r = 3; r <= lastRow; r++) {
    if (recSheet.getRange(r, 1).getValue() === label) {
      targetRow = r;
      break;
    }
  }

  var prevTotal = (targetRow > 3) ? (recSheet.getRange(targetRow - 1, 8).getValue() || 0) : 0;
  var change     = prevTotal > 0 ? total - prevTotal : 0;
  var changeRate = prevTotal > 0 ? change / prevTotal : 0;

  var rowData = [label, dateStr].concat(accountValues).concat([total, change, changeRate]);
  recSheet.getRange(targetRow, 1, 1, rowData.length).setValues([rowData]);

  recSheet.getRange(targetRow, 1).setFontWeight('bold');
  recSheet.getRange(targetRow, 3, 1, 6).setNumberFormat('#,##0');
  recSheet.getRange(targetRow, 8).setNumberFormat('#,##0');
  recSheet.getRange(targetRow, 9).setNumberFormat('[Blue]#,##0;[Red](#,##0);"-"');
  recSheet.getRange(targetRow, 10).setNumberFormat('[Blue]0.00%;[Red](0.00%);"-"');

  var q  = parseInt(label.split('Q')[1]);
  var bg = (q % 2 === 0) ? '#E8F5E9' : '#E3F2FD';
  recSheet.getRange(targetRow, 1, 1, 10).setBackground(bg);

  updateQuarterChart(recSheet);
}

function setupQuarterSheet(sheet) {
  sheet.clear();

  var widths = [100, 110, 120, 120, 120, 120, 120, 140, 130, 110];
  widths.forEach(function(w, i) { sheet.setColumnWidth(i + 1, w); });

  sheet.setRowHeight(1, 44);
  var title = sheet.getRange('A1:J1');
  title.merge().setValue('📅 분기별 자산 변화 기록');
  title.setFontSize(15).setFontWeight('bold').setFontColor('#FFFFFF')
       .setBackground('#283593').setHorizontalAlignment('center').setVerticalAlignment('middle');

  sheet.setRowHeight(2, 30);
  var headers = ['분기','기록일','키움증권(₩)','미래에셋(₩)','삼성증권(₩)','NH투자증권(₩)','토스증권(₩)','전체 자산(₩)','전분기 대비(₩)','증감률'];
  var hr = sheet.getRange(2, 1, 1, headers.length);
  hr.setValues([headers]);
  hr.setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#3949AB').setHorizontalAlignment('center');

  sheet.getRange('A55').setValue('💡 매 분기 마지막 날(3/31, 6/30, 9/30, 12/31) 자동 기록됩니다.');
  sheet.getRange('A55').setFontColor('#757575').setFontStyle('italic');
  sheet.getRange('A56').setValue('💡 [포트폴리오 > 📅 지금 분기 수동 기록]으로 즉시 저장할 수 있습니다.');
  sheet.getRange('A56').setFontColor('#757575').setFontStyle('italic');
}

function updateQuarterChart(sheet) {
  sheet.getCharts().forEach(function(c) { sheet.removeChart(c); });

  var lastRow = sheet.getLastRow();
  if (lastRow < 3) return;

  var lineChart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(sheet.getRange(2, 1, lastRow - 1, 1))
    .addRange(sheet.getRange(2, 8, lastRow - 1, 1))
    .setOption('title', '분기별 전체 자산 추이')
    .setOption('titleTextStyle', {fontSize:14, bold:true, color:'#1A237E'})
    .setOption('vAxis', {title:'자산(₩)', format:'#,##0'})
    .setOption('hAxis', {slantedText:true, slantedTextAngle:30})
    .setOption('colors', ['#1565C0'])
    .setOption('pointSize', 6)
    .setOption('lineWidth', 3)
    .setOption('backgroundColor', '#FAFAFA')
    .setOption('legend', {position:'none'})
    .setNumHeaders(1)
    .setPosition(3, 12, 10, 10)
    .build();
  sheet.insertChart(lineChart);

  if (lastRow >= 4) {
    var barChart = sheet.newChart()
      .setChartType(Charts.ChartType.COLUMN)
      .addRange(sheet.getRange(3, 1, lastRow - 2, 1))
      .addRange(sheet.getRange(3, 9, lastRow - 2, 1))
      .setOption('title', '전분기 대비 증감(₩)')
      .setOption('titleTextStyle', {fontSize:14, bold:true, color:'#1A237E'})
      .setOption('vAxis', {title:'증감(₩)', format:'#,##0'})
      .setOption('hAxis', {slantedText:true, slantedTextAngle:30})
      .setOption('colors', ['#2E7D32'])
      .setOption('backgroundColor', '#FAFAFA')
      .setOption('legend', {position:'none'})
      .setNumHeaders(1)
      .setPosition(22, 12, 10, 10)
      .build();
    sheet.insertChart(barChart);
  }
}

function manualQuarterRecord() {
  var ui    = SpreadsheetApp.getUi();
  var today = new Date();
  var label = getQuarterLabel(today);

  var res = ui.alert(
    '📅 분기 수동 기록',
    '현재 시점(' + Utilities.formatDate(today,'Asia/Seoul','yyyy-MM-dd') + ')의 자산을\n[' + label + '] 분기로 기록합니다.\n\n계속하시겠습니까?',
    ui.ButtonSet.YES_NO
  );
  if (res !== ui.Button.YES) return;

  recordQuarterSnapshot(today);
  ui.alert('✅ 기록 완료', '[' + label + '] 분기 자산이 기록되었습니다.\n[분기별_기록] 시트에서 확인하세요.', ui.ButtonSet.OK);
}

function registerDailyTrigger() {
  ScriptApp.getProjectTriggers().forEach(function(t) {
    if (t.getHandlerFunction() === 'dailyQuarterCheck') {
      ScriptApp.deleteTrigger(t);
    }
  });

  ScriptApp.newTrigger('dailyQuarterCheck')
    .timeBased()
    .everyDays(1)
    .atHour(23)
    .create();

  SpreadsheetApp.getUi().alert(
    '✅ 자동 트리거 등록 완료',
    '매일 밤 23시에 날짜를 확인합니다.\n분기 마지막 날(3/31, 6/30, 9/30, 12/31)에\n자동으로 자산이 기록됩니다.\n\n※ 최초 1회만 등록하면 됩니다.',
    SpreadsheetApp.getUi().ButtonSet.OK
  );
}