~~NOCACHE~~ ====== 연습장 ====== ====== 사진첩(러시아) ======

function openBuyForm(){
  SpreadsheetApp.getUi()
    .showModalDialog(HtmlService.createHtmlOutputFromFile("buyForm")
    .setWidth(400).setHeight(520), "매수 입력");
}



function getNaverStockName(ticker){
  try{
    const url = "https://m.stock.naver.com/api/stock/" + ticker + "/basic";

    const res = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      headers: {
        "User-Agent": "Mozilla/5.0",
        "Referer": "https://m.stock.naver.com/"
      }
    });

    const data = JSON.parse(res.getContentText());
    return data.stockName || "";
  }catch(e){
    return "";
  }
}

function saveBuy(d){
  const sh = SpreadsheetApp.getActive().getSheetByName("매수");
  const r = sh.getLastRow()+1;

  // 종목명 찾기
  const name = findStockNameFromAll(d.ticker);

  // 날짜 형식 통일
  const formattedDate = formatDateYMD(d.date);

  const row = [
    formattedDate,
    d.acc,
    name,
    "'" + d.ticker,
    d.market,
    d.price,
    d.qty,
    d.total,
    d.target,
    d.stopp,
    "",
    d.type,
    d.reason,
    ""
  ];

  sh.getRange(r,1,1,row.length).setValues([row]);

  // 비중 계산 (기존 로직)
  const lastDataRow = sh.getLastRow();
  if(lastDataRow >= 5){
    const hRange = sh.getRange(5,8,lastDataRow-4,1);
    const hValues = hRange.getValues().flat().map(v => Number(v)||0);
    const totalH = hValues.reduce((a,b)=>a+b,0);
    if(totalH>0){
      for(let i=0; i r[1].toString().includes(keyword))
    .slice(0, 20);
}


function findStockName(ticker, type){
  const ss = SpreadsheetApp.getActive();
  const sh = (type === "ETF")
      ? ss.getSheetByName("ETF")
      : ss.getSheetByName("주식종목");

  const data = sh.getRange(2,1,sh.getLastRow()-1,2).getValues();

  // 숫자/문자/앞자리 0 전부 제거 → 6자리 통일
  const t = String(ticker).replace(/[^0-9A-Z]/gi,'').padStart(6,'0');

  for(let i=0;i= 5){
    const hRange = sh.getRange(5,8,lastDataRow-4,1); // H5~마지막 행
    const hValues = hRange.getValues().flat().map(v => Number(v)||0);
    const totalH = hValues.reduce((a,b)=>a+b,0);

    if(totalH>0){
      for(let i=0; i= 5
        ? sellSheet.getRange(5,1,sellLast-4,7).getValues()
        : [];

  // 상태 초기화 (A~K)
  if(status.getLastRow() >= 2){
    status.getRange(2,1,status.getLastRow()-1,11).clearContent();
  }

  const map = {};

  // =========================
  // 매수 반영
  // =========================
  buyData.forEach(r=>{
    const acc    = r[1];
    const name   = r[2];
    const ticker = String(r[3]).replace("'","");
    const qty    = Number(r[6]);
    const total  = Number(r[7]);
    const type   = r[11];
    if(!acc || !ticker || !qty) return;

    const key = acc+"|"+ticker;

    if(!map[key]){
      map[key] = {acc,name,ticker,qty:0,total:0,type};
    }
    map[key].qty   += qty;
    map[key].total += total;
  });

  // =========================
  // 매도 반영 (수량 차감)
  // =========================
  sellData.forEach(r=>{
    const acc    = r[1];
    const name   = r[2];
    const ticker = String(r[3]).replace("'","");
    const qty    = Number(r[6]);
    if(!acc || !ticker || !qty) return;

    const key = acc+"|"+ticker;

    if(!map[key]){
      map[key] = {acc,name,ticker,qty:0,total:0,type:""};
    }
    map[key].qty -= qty;   // ⭐ 핵심
  });

  const out = [];

  Object.values(map).forEach(v=>{
    if(v.qty <= 0) return; // 전량 매도면 상태에서 제거

    const avg = v.qty ? Math.round(v.total / v.qty) : "";
    const accShort = v.acc === "위탁종합" ? "위" : v.acc === "ISA" ? "I" : v.acc;

    out.push([
      accShort,   // A
      v.name,     // B
      "",         // C 손실률
      "",         // D 매도여부
      "",         // E 현재가
      "",         // F 시간
      avg,        // G 평균매수가
      "",         // H 최고가
      v.type,     // I 구분
      v.ticker,   // J 티커
      v.qty       // ⭐ K 수량
    ]);
  });

  if(out.length){
    status.getRange(2,1,out.length,11).setValues(out);
  }
}




function getNaverRealtimePrice(ticker){
  try{
    const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/" + ticker;

    const res = UrlFetchApp.fetch(url, {
      headers: {
        "User-Agent": "Mozilla/5.0",
        "Referer": "https://finance.naver.com"
      },
      muteHttpExceptions: true
    });

    const json = JSON.parse(res.getContentText());

    if(!json.datas || json.datas.length === 0) return null;

    const price = json.datas[0].closePrice;
    return Number(price.replace(/,/g,""));
  }catch(e){
    return null;
  }
}



function updateStatusRealtime(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("상태");
  if(!sh) return;

  const lastRow = sh.getLastRow();
  if(lastRow < 2) return;

  const tickers = sh.getRange(2,10,lastRow-1,1).getValues().flat(); // J열
  const avgs    = sh.getRange(2,7,lastRow-1,1).getValues().flat();  // G열

  for(let i=0;i{
    const acc = r[1];
    const name = r[2];
    const ticker = r[3];
    const price = Number(String(r[5]).replace(/,/g,""));
    const qty   = Number(r[6]);
    const type  = r[11];
    if(!ticker || !qty) return;

    const key = acc+"_"+ticker;
    if(!map[key]){
      map[key] = {acc,name,ticker,type,sum:0,qty:0};
    }
    map[key].sum += price*qty;
    map[key].qty += qty;
  });

  st.getRange(2,1,st.getLastRow(),10).clearContent();

  let row=2;
  Object.values(map).forEach(o=>{
    const avg = Math.round(o.sum/o.qty);
    const accShort = o.acc=="위탁종합" ? "위" : (o.acc=="ISA"?"I":o.acc);

    st.getRange(row,1,1,10).setValues([[
      accShort,o.name,"","", "", "", avg,"",o.type,o.ticker
    ]]);
    row++;
  });
}

function getNaverPrice(t){
  try{
    const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/"+t;
    const res = UrlFetchApp.fetch(url,{muteHttpExceptions:true});
    const json = JSON.parse(res.getContentText());
    if(!json.datas || !json.datas.length) return null;
    return Number(json.datas[0].closePrice.replace(/,/g,""));
  }catch(e){
    return null;
  }
}


function updateStatusPrice(){
  const sh = SpreadsheetApp.getActive().getSheetByName("상태");
  const last = sh.getLastRow();
  if(last<2) return;

  for(let i=2;i<=last;i++){
    const t = sh.getRange(i,10).getValue();
    const avg = sh.getRange(i,7).getValue();
    if(!t || !avg) continue;

    const price = getNaverPrice(t);
    if(!price) continue;

    const loss = Math.round((price-avg)/avg*1000)/10;

    sh.getRange(i,5).setValue(price);
    sh.getRange(i,3).setValue(loss);
    sh.getRange(i,6).setValue(new Date());
  }
}


function updateStatusRealtimePrice() {
  const sh = SpreadsheetApp.getActive().getSheetByName("상태");
  const lastRow = sh.getLastRow();
  if (lastRow < 2) return;

  const tickers = sh.getRange("J2:J" + lastRow).getValues();
  const priceRange = sh.getRange("E2:E" + lastRow);   // 현재가
  const lossRange  = sh.getRange("C2:C" + lastRow);   // 손실률
  const avgRange   = sh.getRange("G2:G" + lastRow);   // 평균매수가
  const highRange  = sh.getRange("H2:H" + lastRow);   // 최고가
  const timeRange  = sh.getRange("F2:F" + lastRow);   // 업데이트시간

  let prices = priceRange.getValues();
  let losses = lossRange.getValues();
  let avgs   = avgRange.getValues();
  let highs  = highRange.getValues();
  let times  = timeRange.getValues();

  for (let i = 0; i < tickers.length; i++) {
    const t = tickers[i][0];
    if (!t) continue;

    try {
      const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/" + t;
      const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      const data = JSON.parse(res.getContentText());
      if (!data || !data.datas || data.datas.length === 0) continue;

      const row = data.datas[0];

      const price = Number(row.closePrice.replace(/,/g, ""));
      const todayHigh = Number(row.highPrice.replace(/,/g, ""));
      const naverTimeStr = parseNaverTime(data.time);


      if (price) prices[i][0] = price;
      if (naverTimeStr) times[i][0] = naverTimeStr;

      // 손실률
      if (price && avgs[i][0]) {
        losses[i][0] = Math.round(((price - avgs[i][0]) / avgs[i][0]) * 1000) / 10;
      }

      // 최고가
      if (!highs[i][0] || todayHigh > highs[i][0]) {
        highs[i][0] = todayHigh;
      }

      // 업데이트 시간
      

    } catch (e) {
      Logger.log("FAIL : " + t);
    }
  }

  priceRange.setValues(prices);
  lossRange.setValues(losses);
  highRange.setValues(highs);
  timeRange.setValues(times);
}

function parseNaverTime(t){
  if(!t || t.length !== 14) return "";

  const h  = t.substr(8,2);
  const mi = t.substr(10,2);
  const s  = t.substr(12,2);

  return `${h}:${mi}:${s}`;
}


function openSellForm(){
  SpreadsheetApp.getUi()
    .showModalDialog(
      HtmlService.createHtmlOutputFromFile("sellForm")
        .setWidth(420)
        .setHeight(620),
      "매도 입력"
    );
}


function getSellCandidates(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("상태");
  const lastRow = sh.getLastRow();
  if(lastRow < 2) return [];

  // A:계좌 B:종목명 G:평균매수가 J:티커 K:수량
  const data = sh.getRange(2,1,lastRow-1,11).getValues();
  const result = [];

  data.forEach(r=>{
    const acc    = r[0];   // 위 / I
    const name   = r[1];
    const avg    = r[6];
    const ticker = r[9];   // J열
    const qty    = r[10];  // K열 ⭐

    if(name && ticker && qty){
      const accFull = acc === "위" ? "위탁종합" : acc === "I" ? "ISA" : acc;
      result.push([accFull, name, ticker, avg, qty]);
    }
  });

  return result;
}



function saveSell(d){
  const sh = SpreadsheetApp.getActive().getSheetByName("매도");
  const r = Math.max(sh.getLastRow()+1, 5);

  const profitRate = ((d.sell - d.buy) / d.buy * 100).toFixed(1);
  const profitAmt = (d.sell - d.buy) * d.qty;

  const row = [
    d.date,
    d.acc,
    d.name,
    "'" + d.ticker,
    d.buy,
    d.sell,
    d.qty,
    profitRate,
    profitAmt,
    d.reason,
    d.buyReview,
    d.sellReview,
    d.lesson
  ];

  sh.getRange(r,1,1,row.length).setValues([row]);

  updateStatusSheet(); 

}