import fs from 'node:fs/promises';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { Workbook, SpreadsheetFile } from '@oai/artifact-tool';

const root = path.dirname(fileURLToPath(import.meta.url));
const out = path.join(root, 'outputs', '20261019');
const analysis = JSON.parse(await fs.readFile(path.join(root, 'analysis.json'), 'utf8'));
const wb = Workbook.create();
const summary = wb.worksheets.add('回撤概览');
const detail = wb.worksheets.add('回撤明细');
const daily = wb.worksheets.add('日线与逐日回本');
const navy = '#193A56', ink = '#172B3A', muted = '#607383', light = '#EDF2F6';
const indices = Object.values(analysis.indices);
const excelDate = s => s ? new Date(s+'T00:00:00Z') : null;
const eventRanges = {}, dailyOffsets = {}, eventRows = new Map();
function put(sheet, cell, value) { sheet.getRange(cell).values = [[value]]; }
function formula(sheet, cell, value) { sheet.getRange(cell).formulas = [[value]]; }
function base(sheet, range) {
  sheet.showGridLines = false;
  sheet.getRange(range).format.font = {name:'Arial',size:10,color:ink};
  sheet.getRange(range).format.rowHeight = 21;
  sheet.getRange(range).format.verticalAlignment = 'center';
}
function header(sheet, range) {
  sheet.getRange(range).format = {fill:navy,font:{name:'Arial',size:10,bold:true,color:'#FFFFFF'},rowHeight:31,horizontalAlignment:'center',verticalAlignment:'center'};
}
function title(sheet, cell, text) {
  put(sheet,cell,text);sheet.getRange(cell).format.font={name:'Arial',size:15,bold:true,color:navy};
}
function number(sheet, range, format='#,##0') { sheet.getRange(range).setNumberFormat(format); }

// One source and daily-results table; event detection and next-breakeven are script snapshots.
const dHeaders=['指数','日期','收盘点位','数据来源','历史最高收盘','距历史最高回撤','首次回本日期','等待自然日','等待交易日','期间曾浮亏','回本状态','已观察自然日','已观察交易日','交易日序号'];
let drow=10;
for(const ix of indices){dailyOffsets[ix.key]=drow; drow+=ix.daily.length;}
base(daily,`A1:N${drow-1}`);
title(daily,'A2','日线数据与逐日买入回本时间');
put(daily,'A4','有效交易观测；日收盘价格指数，不含分红、汇率、税费或通胀。');
put(daily,'A5','回本日期：买入日之后，首个收盘不低于买入价的日期；次日即盈利也会列出，是否浮亏见 J 列。');
put(daily,'A6','无回本日的末尾记录表示后续样本不足；最后观测日单列，不能解释为已发生亏损。');
put(daily,'A7','交易日按有效日线观测序号相减；早期标普日线未逐日对照独立交易所日历，可能不含当年周六交易。');
daily.getRange('A9:N9').values=[dHeaders]; header(daily,'A9:N9');
for(const ix of indices){
  const start=dailyOffsets[ix.key];
  const rows=ix.daily.map(r=>[r[0],excelDate(r[1]),r[2],r[3],r[4],r[5],excelDate(r[6]),...r.slice(7)]);
  for(let offset=0;offset<rows.length;offset+=3000){const block=rows.slice(offset,offset+3000);daily.getRangeByIndexes(start-1+offset,0,block.length,14).values=block;}
}
const dend=drow-1;
daily.getRange(`A9:N${dend}`).format.columnWidth=17;
daily.getRange(`A9:A${dend}`).format.columnWidth=20;
daily.getRange(`J9:K${dend}`).format.columnWidth=18;
number(daily,`B10:B${dend}`,'yyyy-mm-dd');number(daily,`G10:G${dend}`,'yyyy-mm-dd');
number(daily,`C10:C${dend}`,'#,##0.00');number(daily,`E10:E${dend}`,'#,##0.00');number(daily,`F10:F${dend}`,'0.00%');
number(daily,`H10:I${dend}`);number(daily,`L10:N${dend}`);
daily.tables.add(`A9:N${dend}`,true,'DailyPricesAndBreakeven');
daily.freezePanes.freezeRows(9);daily.freezePanes.freezeColumns(2);

// Every non-overlapping underwater spell, no minimum depth threshold.
let erow=9;
for(const ix of indices){eventRanges[ix.key]={start:erow,end:erow+ix.events.length-1};erow+=ix.events.length;}
base(detail,`A1:T${erow-1}`);
title(detail,'A2','每一次历史新高回撤与解套');
put(detail,'A4','前高为下跌前最后一个最高收盘日；收盘首次回到或超过前高即解套；同一长回撤内的反弹不另起事件。');
put(detail,'A5','自然日为日期相减，交易日为有效观测序号相减；不含起点、包含终点。谷底重复时取首次。');
put(detail,'A6','未解套事件的总解套及恢复时间留空；已观察天数仅截至本次样本末日。');
put(detail,'A7','事件边界及逐日回本日期是计算快照。更改日线价格后，须重跑分析脚本重新识别事件。');
detail.getRange('A8:T8').values=[['指数','序号','前高日','前高点位','谷底日','谷底点位','解套日','解套收盘','最大回撤','下跌自然日','谷底后恢复自然日','总解套自然日','下跌交易日','谷底后恢复交易日','总解套交易日','状态','观测截止日','已观察自然日','已观察交易日','截止距前高回撤']];header(detail,'A8:T8');
for(const ix of indices){
  const start=eventRanges[ix.key].start;
  for(let k=0;k<ix.events.length;k++){
    const e=ix.events[k],r=start+k;
    eventRows.set(`${ix.key}:${e.peak_date}`,r);
    const p=dailyOffsets[ix.key]+e.peak_i,t=dailyOffsets[ix.key]+e.trough_i,rec=e.recovery_i===null?null:dailyOffsets[ix.key]+e.recovery_i;
    const source=col=>`='日线与逐日回本'!${col}`;
    detail.getRange(`A${r}:T${r}`).values=[[ix.name,k+1,null,null,null,null,null,null,null,null,null,null,e.decline_trading_days,e.rebound_trading_days,e.total_trading_days,e.status,excelDate(e.end_date),null,e.observed_trading_days,null]];
    detail.getRange(`C${r}:L${r}`).formulas=[[
      source(`B${p}`),source(`C${p}`),source(`B${t}`),source(`C${t}`),rec?source(`B${rec}`):null,rec?source(`C${rec}`):null,
      `=F${r}/D${r}-1`,`=E${r}-C${r}`,`=IF(G${r}="","",G${r}-E${r})`,`=IF(G${r}="","",G${r}-C${r})`
    ]];
    formula(detail,`R${r}`,`=Q${r}-C${r}`);
    // Q is recovery day for completed spells, sample end for open spells.
    const end=dailyOffsets[ix.key]+(e.recovery_i===null?ix.daily.length-1:e.recovery_i);
    formula(detail,`T${r}`,`='日线与逐日回本'!C${end}/D${r}-1`);
  }
}
const eend=erow-1;
detail.getRange(`A8:T${eend}`).format.columnWidth=17;
detail.getRange(`A8:A${eend}`).format.columnWidth=20;detail.getRange(`B8:B${eend}`).format.columnWidth=8;
detail.getRange('K8').format.columnWidth=23;detail.getRange('N8').format.columnWidth=23;detail.getRange('T8').format.columnWidth=23;
for(const col of ['C','E','G','Q'])number(detail,`${col}9:${col}${eend}`,'yyyy-mm-dd');
for(const col of ['D','F','H'])number(detail,`${col}9:${col}${eend}`,'#,##0.00');
for(const col of ['I','T'])number(detail,`${col}9:${col}${eend}`,'0.00%');
for(const col of ['J','K','L','M','N','O','R','S'])number(detail,`${col}9:${col}${eend}`);
detail.getRange(`I9:I${eend}`).conditionalFormats.add('colorScale',{colors:['#D88484','#FFFFFF'],thresholds:['min','max']});
detail.getRange(`P9:P${eend}`).conditionalFormats.add('containsText',{text:'未解套',format:{fill:'#FFF0D4',font:{bold:true,color:'#9C6400'}}});
detail.tables.add(`A8:T${eend}`,true,'DrawdownEpisodes');
detail.freezePanes.freezeRows(8);detail.freezePanes.freezeColumns(2);

// Main output: compact overview, long-duration spells, depth thresholds and method.
base(summary,'A1:H90');summary.tabColor=navy;
summary.getRange('A1:H90').format.columnWidth=18;summary.getRange('A1:A90').format.columnWidth=27;
summary.getRange('B1:C90').format.columnWidth=21;
title(summary,'A2','纳指与标普500历史回撤解套时间');
put(summary,'A4','数据截止 2026-10-16；抓取日期 2026-10-19（北京时间）。');
put(summary,'A5','NASDAQ = 纳斯达克综合指数 ^IXIC；S&P 500 = ^GSPC。');
summary.getRange('A7:C7').values=[['指标',indices[0].name,indices[1].name]];header(summary,'A7:C7');
const labels=['首个观测日','最后观测日','有效日线条数','全部回撤段数','已解套段数','未解套段数','历史最大回撤','最长解套自然日','最长解套年数','解套自然日中位数','最长回撤前高日','该次解套日'];
summary.getRange('A8:A19').values=labels.map(x=>[x]);
for(let k=0;k<indices.length;k++){
  const ix=indices[k],c=k?'C':'B',rr=eventRanges[ix.key],lr=eventRows.get(`${ix.key}:${ix.longest.peak_date}`),dr=eventRows.get(`${ix.key}:${ix.deepest.peak_date}`);
  put(summary,`${c}8`,excelDate(ix.start));put(summary,`${c}9`,excelDate(ix.end));put(summary,`${c}10`,ix.observations);
  formula(summary,`${c}11`,`=COUNTA('回撤明细'!B${rr.start}:B${rr.end})`);
  formula(summary,`${c}12`,`=COUNTIFS('回撤明细'!P${rr.start}:P${rr.end},"已解套")`);
  formula(summary,`${c}13`,`=COUNTIFS('回撤明细'!P${rr.start}:P${rr.end},"未解套")`);
  formula(summary,`${c}14`,`='回撤明细'!I${dr}`);formula(summary,`${c}15`,`='回撤明细'!L${lr}`);
  formula(summary,`${c}16`,`=${c}15/365.2425`);formula(summary,`${c}17`,`=MEDIAN('回撤明细'!L${rr.start}:L${rr.end})`);
  formula(summary,`${c}18`,`='回撤明细'!C${lr}`);formula(summary,`${c}19`,`='回撤明细'!G${lr}`);
}
for(const r of [8,9,18,19])number(summary,`B${r}:C${r}`,'yyyy-mm-dd');
number(summary,'B10:C13');number(summary,'B14:C14','0.00%');number(summary,'B15:C15');number(summary,'B16:C16','0.00');number(summary,'B17:C17','0.0');
put(summary,'E8','统计范围各自从最早观测日起。');put(summary,'E9','不同起点的均值与中位数不能直接比较。');
put(summary,'E11','全部回撤包括只有一天的微小回撤。');put(summary,'E12','中位数仅含已解套事件。');
put(summary,'E14','标普1957-03-04之前为前身／回溯序列。');put(summary,'E15','早期日期为数据商提供的历史日线口径。');
put(summary,'E17','价格回本不含分红、税费、汇率和通胀。');
summary.getRange('E8:H19').format.font={name:'Arial',size:10,color:muted};

title(summary,'A22','各指数解套最慢的五次回撤');
summary.getRange('A24:H24').values=[['指数','前高日','谷底日','解套日','最大回撤','总自然日','总交易日','约合年数']];header(summary,'A24:H24');
let sr=25;
for(const ix of indices){
  const top=ix.events.filter(e=>e.recovery_i!==null).sort((a,b)=>b.total_calendar_days-a.total_calendar_days).slice(0,5);
  for(const e of top){const r=eventRows.get(`${ix.key}:${e.peak_date}`);summary.getRange(`A${sr}:H${sr}`).formulas=[[`='回撤明细'!A${r}`,`='回撤明细'!C${r}`,`='回撤明细'!E${r}`,`='回撤明细'!G${r}`,`='回撤明细'!I${r}`,`='回撤明细'!L${r}`,`='回撤明细'!O${r}`,`=F${sr}/365.2425`]];sr++;}
}
number(summary,'B25:D34','yyyy-mm-dd');number(summary,'E25:E34','0.00%');number(summary,'F25:G34');number(summary,'H25:H34','0.00');
title(summary,'A37','按最大跌幅筛选回撤');
put(summary,'A38','各行是累计阈值，不能相加；时长中位数和最长值只含已解套事件。');
summary.getRange('A40:G40').values=[['指数','最大跌幅至少','全部段数','已解套','未解套','中位自然日','最长自然日']];header(summary,'A40:G40');
sr=41;
for(const ix of indices){
  const rr=eventRanges[ix.key];
  for(const b of ix.buckets){
    put(summary,`A${sr}`,ix.name);put(summary,`B${sr}`,b.cutoff);
    formula(summary,`C${sr}`,`=COUNTIFS('回撤明细'!I${rr.start}:I${rr.end},"<="&-B${sr})`);
    formula(summary,`D${sr}`,`=COUNTIFS('回撤明细'!I${rr.start}:I${rr.end},"<="&-B${sr},'回撤明细'!P${rr.start}:P${rr.end},"已解套")`);
    formula(summary,`E${sr}`,`=C${sr}-D${sr}`);
    const ids=ix.events.filter(e=>-e.max_drawdown>=b.cutoff&&e.recovery_i!==null).map(e=>eventRows.get(`${ix.key}:${e.peak_date}`));
    // Coalesce adjacent rows to stay below Excel's 255-function-argument limit.
    const spans=[];
    for(const id of ids){const tail=spans.at(-1);if(tail&&id===tail[1]+1)tail[1]=id;else spans.push([id,id]);}
    const included=spans.map(([a,z])=>`'回撤明细'!L${a}${z===a?'':`:L${z}`}`);
    if(included.length){formula(summary,`F${sr}`,`=MEDIAN(${included.join(',')})`);formula(summary,`G${sr}`,`=MAX(${included.join(',')})`);}
    sr++;
  }
}
number(summary,'B41:B54','0%');number(summary,'C41:E54');number(summary,'F41:F54','0.0');number(summary,'G41:G54');
title(summary,'A57','截至样本末仍未解套');
summary.getRange('A59:G59').values=[['指数','前高日','截止日','最大回撤','已观察自然日','已观察交易日','截止距前高回撤']];header(summary,'A59:G59');
sr=60;
for(const ix of indices)for(const e of ix.open_events){const r=eventRows.get(`${ix.key}:${e.peak_date}`);summary.getRange(`A${sr}:G${sr}`).formulas=[[`='回撤明细'!A${r}`,`='回撤明细'!C${r}`,`='回撤明细'!Q${r}`,`='回撤明细'!I${r}`,`='回撤明细'!R${r}`,`='回撤明细'!S${r}`,`='回撤明细'!T${r}`]];sr++;}
number(summary,'B60:C61','yyyy-mm-dd');number(summary,'D60:D61','0.00%');number(summary,'G60:G61','0.00%');number(summary,'E60:F61');
title(summary,'A64','数据来源与计算口径');
const notes=[
  '来源优先级：FRED转发的指数公司收盘优先；FRED缺失区间以Yahoo Finance同指数收盘补足。',
  `纳指：${indices[0].fred_rows.toLocaleString()}条FRED；${indices[0].yahoo_rows}条Yahoo（2026-10-16）。`,
  `标普：${indices[1].fred_rows.toLocaleString()}条FRED；${indices[1].yahoo_rows.toLocaleString()}条Yahoo。`,
  `两源重叠区按0.01点比较，纳指${indices[0].source_mismatches}条、标普${indices[1].source_mismatches}条不一致；最终均选FRED。`,
  '日期以Yahoo对应指数的有效交易观测为基准；排除FRED纳指2019-04-19休市日异常观测。',
  '为去除Yahoo浮点表示误差，比较价格统一到0.01点；未填充周末或休市日，未插值。',
  '回撤率 = 当日收盘 / 截至当日最高收盘 - 1；每次回撤只在重新收复历史高点后结束。',
  '解套天数 = 解套日 - 前高日；谷底后恢复天数 = 解套日 - 谷底日。',
  '最近未解套事件属于右删失，真实总解套时长未知，不能把已观察天数当作最终结果。',
  '逐日买入后若次日直接不低于买入价，也有首次回本日期；是否实际浮亏见日线表。',
  '事件识别和逐日首次回本日期为脚本快照，更新行情或修改价格须重跑分析。',
  'FRED NASDAQCOM：https://fred.stlouisfed.org/series/NASDAQCOM',
  'FRED SP500：https://fred.stlouisfed.org/series/SP500 （仅提供最近10年）',
  'Yahoo ^IXIC：https://finance.yahoo.com/quote/%5EIXIC/history/',
  'Yahoo ^GSPC：https://finance.yahoo.com/quote/%5EGSPC/history/',
  '标普指数定义与推出日期：https://www.spglobal.com/spdji/en/indices/equity/sp-500/',
  '2019休市日：https://ir.theice.com/press/news-details/2018/NYSE-Group-Announces-2019-2020-and-2021-Holiday-and-Early-Closings-Calendar/default.aspx',
];
notes.forEach((text,i)=>put(summary,`A${66+i}`,text));
summary.getRange('A66:H83').format.font={name:'Arial',size:10,color:muted};
// A reversible source-price change verifies that linked arithmetic recalculates.
{
  const ix=indices[0],e=ix.longest;
  const sourceCell=daily.getRange(`C${dailyOffsets[ix.key]+e.trough_i}`);
  const resultCell=detail.getRange(`I${eventRows.get(`${ix.key}:${e.peak_date}`)}`);
  const original=sourceCell.values[0][0];
  sourceCell.values=[[original+1]];
  const expected=(original+1)/e.peak_close-1;
  if(Math.abs(resultCell.values[0][0]-expected)>1e-12)throw Error('Linked-price recalculation failed');
  sourceCell.values=[[original]];
}
// Long notes are placed on otherwise empty rows, so they can extend horizontally.
wb.recalculate();
console.log((await wb.inspect({kind:'table',range:'回撤概览!A7:C19',include:'values,formulas',tableMaxRows:13,tableMaxCols:3,maxChars:4500})).ndjson);
console.log((await wb.inspect({kind:'match',searchTerm:'#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A|#NUM!|#NULL!|#SPILL!|#CALC!',options:{useRegex:true,maxResults:30},maxChars:1500})).ndjson);
// Verify representative formula outputs against independently calculated Python results.
for(let k=0;k<indices.length;k++){
  const c=k?'C':'B',ix=indices[k];
  if(summary.getRange(`${c}11`).values[0][0]!==ix.events.length)throw Error('Event count mismatch');
  if(summary.getRange(`${c}15`).values[0][0]!==ix.longest.total_calendar_days)throw Error('Longest duration mismatch');
}
let checkRow=41;
for(const ix of indices)for(const bucket of ix.buckets){
  const values=summary.getRange(`C${checkRow}:G${checkRow}`).values[0];
  const expected=[bucket.all_events,bucket.recovered,bucket.open,bucket.median_calendar_days,bucket.max_calendar_days];
  if(values.some((v,i)=>Math.abs(v-expected[i])>1e-10))throw Error(`Threshold result mismatch ${ix.key} row ${checkRow}`);
  checkRow++;
}
for(const [sheet,range,name] of [['回撤概览','A7:H19','overview'],['回撤概览','A24:H34','longest'],['回撤明细','A8:L15','episodes'],['日线与逐日回本','A9:N16','daily']]){
  try{await fs.access(path.join(out,`${name}_preview.png`));continue;}catch{}
  const preview=await wb.render({sheetName:sheet,range,scale:1.3,format:'png'});
  await fs.writeFile(path.join(out,`${name}_preview.png`),new Uint8Array(await preview.arrayBuffer()));
}
await (await SpreadsheetFile.exportXlsx(wb)).save(path.join(out,'nasdaq_sp500_drawdown_recovery.xlsx'));
console.log(JSON.stringify({output:path.join(out,'nasdaq_sp500_drawdown_recovery.xlsx'),dailyRows:dend-9,eventRows:eend-8,sheets:3}));