// Build spm_panel.csv from HUD "CoC System Performance Measures Data Since FY 2015" (sheets converted to CSV).
// Usage: node build_spm.js <csvDir> <geoMap.csv> <panel.csv> <outPanel.csv> <outJson>
const fs=require('fs');const {read}=require('./csvlib');
const [csvDir,geoPath,panelPath,outCsv,outJson]=process.argv.slice(2);
const geo=read(geoPath);const gh=geo[0];
const cocs=geo.slice(1).filter(r=>r[0]).map(r=>({code:r[0],aliases:(r[1]||'').split(/[;|]/).filter(Boolean),name:r[2]}));
const pan=read(panelPath);const ph=pan[0];const pi=n=>ph.indexOf(n);
const pop={};pan.slice(1).forEach(r=>{if(r[pi('population')])pop[r[0]+'|'+r[pi('year')]]=+r[pi('population')]});
const num=s=>{if(s==null)return null;s=String(s).replace(/[$,%\s]/g,'');if(s===''||s==='-'||/^n\/?a$/i.test(s))return null;const v=+s;return isNaN(v)?null:v};
const rows=[];const notes=[];
for(let y=2015;y<=2024;y++){
 const r=read(`${csvDir}/${y}.csv`);const hi=r.findIndex(x=>x.some(c=>/ES-SH Avg/.test(c)));
 const g=[],h=r[hi].map(s=>s.replace(/\s+/g,' ').trim());let cur='';
 r[hi-1].forEach((c,i)=>{if(c&&/SPM|Bed|HMIS/.test(c))cur=c.trim();g[i]=cur});
 for(let i=r[hi-1].length;i<h.length;i++)g[i]=cur;
 const fh=re=>{const i=h.findIndex(x=>re.test(x));if(i<0)throw new Error(y+' no header '+re);return i};
 const fx=t=>fh({test:x=>x===t});
 const s1=fh(/^ES-SH Avg/),s2=fx('Total Persons Exited (SO+ES+TH+SH+PH)'),s5=fh(/^ES-SH-TH 1st/),s7=fh(/^Total Persons Exiting Street Outreach/),bc=fh(/^Total Non-DV Beds/);
 const CG={'SPM 2 (Street Outreach)':'SO','SPM 2 (Emergency Shelter)':'ES','SPM 2 (Transitional Housing)':'TH','SPM 2 (Safe Haven)':'SH','SPM 2 (Permanent Housing)':'PH'};
 const start=cg=>fx('Total Persons Exited ('+CG[cg]+')');
 // sanity: check header text at offsets
 const chk=(i,re)=>{if(!re.test(h[i]))throw new Error(`${y} col ${i} '${h[i]}' !~ ${re}`)};
 chk(s1,/ES-SH Avg/);chk(s1+3,/ES-SH-TH Median/);chk(s2,/Total Persons Exited/);chk(s2+6,/Percent Returns in 24/);
 chk(s5,/ES-SH-TH 1st/);chk(s5+1,/ES-SH-TH-PH 1st/);chk(s7,/Exiting Street Outreach/);chk(s7+4,/Exiting ES, TH, SH, PH-RRH$/);
 chk(s7+7,/Exiting PH/);chk(s7+9,/Percent/);chk(bc,/Total Non-DV Beds/);chk(bc+2,/Bed coverage Percent/);
 const dq={};h.forEach((x,i)=>{const m=x.match(/HMIS DQ Unknown Destinations - (\w+)/);if(m)dq[m[1]]=i});
 const dqU={};h.forEach((x,i)=>{const m=x.match(/HMIS DQ Exits Universe - (\w+)/);if(m)dqU[m[1]]=i});
 const cocCol=h.findIndex(x=>/HUD CoC Number/.test(x))>=0?h.findIndex(x=>/HUD CoC Number/.test(x)):2;
 const byCode={};r.slice(hi+1).forEach(x=>{const c=(x[2]||'').trim();if(/^[A-Z]{2}-\d{3}/.test(c))byCode[c]=x});
 for(const c of cocs){
  const code=[c.code,...c.aliases,...(c.code=='MO-604'?['MO-604M']:[])].find(k=>byCode[k]);const x=code&&byCode[code];
  const o={coc_code:c.code,short_name:c.name,fy:y,spm_code_used:code||''};
  if(!x){notes.push(`${y} ${c.code} not in SPM sheet`);rows.push(o);continue}
  const v=i=>num(x[i]);
  Object.assign(o,{category:x[4],hic_nondv_beds_es_th:v(bc),hmis_nondv_beds_es_th:v(bc+1),bed_coverage_pct_reported:v(bc+2),
   m1_es_sh_avg:v(s1),m1_es_sh_median:v(s1+1),m1_es_sh_th_avg:v(s1+2),m1_es_sh_th_median:v(s1+3),
   m2_exits_to_ph:v(s2),m2_ret6:v(s2+1),m2_ret12:v(s2+2),m2_ret24:v(s2+3),
   m5_first_time_es_sh_th:v(s5),m5_first_time_es_sh_th_ph:v(s5+1),
   m7a_so_exits:v(s7),m7a_so_temp:v(s7+1),m7a_so_perm:v(s7+2),m7a_pct_reported:v(s7+3),
   m7b1_exits:v(s7+4),m7b1_to_ph:v(s7+5),m7b1_pct_reported:v(s7+6),m7b2_universe:v(s7+7),m7b2_success:v(s7+8),m7b2_pct_reported:v(s7+9)});
  const pct=(a,b)=>a!=null&&b?+(100*a/b).toFixed(2):null;
  o.bed_coverage_pct=pct(o.hmis_nondv_beds_es_th,o.hic_nondv_beds_es_th);
  o.m2_pct6=pct(o.m2_ret6,o.m2_exits_to_ph);o.m2_pct12=pct(o.m2_ret12,o.m2_exits_to_ph);o.m2_pct24=pct(o.m2_ret24,o.m2_exits_to_ph);
  o.m7a_pct=pct((o.m7a_so_temp||0)+(o.m7a_so_perm||0),o.m7a_so_exits);
  o.m7b1_pct=pct(o.m7b1_to_ph,o.m7b1_exits);o.m7b2_pct=pct(o.m7b2_success,o.m7b2_universe);
  let p=pop[c.code+'|'+y];
  if(!p&&y==2020){const a=pop[c.code+'|2019'],b=pop[c.code+'|2021'];if(a&&b){p=Math.round((a+b)/2);o.pop_note='2020 interpolated (mean of 2019,2021 ACS)'}}
  o.population=p||null;
  o.m5_per10k_es_sh_th=p&&o.m5_first_time_es_sh_th!=null?+(1e4*o.m5_first_time_es_sh_th/p).toFixed(2):null;
  o.m5_per10k_es_sh_th_ph=p&&o.m5_first_time_es_sh_th_ph!=null?+(1e4*o.m5_first_time_es_sh_th_ph/p).toFixed(2):null;
  for(const k of ['SO','ES','TH','RRH','PSH']){if(dq[k]!=null){o['dq_unknown_dest_pct_'+k]=pct(num(x[dq[k]]),num(x[dqU[k]]))}}
  // data-quality flags
  const f=[];
  if(o.bed_coverage_pct!=null&&o.bed_coverage_pct<85)f.push('bed_cov<85%');
  if(o.bed_coverage_pct==null)f.push('bed_cov_missing');
  if(o.m2_ret6!=null&&o.m2_ret12!=null&&o.m2_ret24!=null&&(o.m2_ret12<o.m2_ret6||o.m2_ret24<o.m2_ret12))f.push('M2_returns_not_cumulative');
  // per-component returns non-cumulative (e.g. a zero 12/24-mo cell)
  if(y>=2018){const comps=['SPM 2 (Street Outreach)','SPM 2 (Emergency Shelter)','SPM 2 (Transitional Housing)','SPM 2 (Safe Haven)','SPM 2 (Permanent Housing)'];
   for(const cg of comps){const s=start(cg);const a=v(s+1),b=v(s+2),d=v(s+3);if(a!=null&&b!=null&&d!=null&&(b<a||d<b))f.push('M2_'+cg.replace(/SPM 2 \(|\)/g,'').replace(/ /g,'')+'_not_cumulative')}}
  if(o.m1_es_sh_avg===0||o.m1_es_sh_avg==null)f.push('M1_missing_or_zero');
  if(o.m5_first_time_es_sh_th_ph!=null&&o.m5_first_time_es_sh_th!=null&&o.m5_first_time_es_sh_th_ph<o.m5_first_time_es_sh_th)f.push('M5_PH<ESSHTH');
  if(!o.m7a_so_exits)f.push('M7a_no_SO_exits');
  for(const k of ['ES','SO']){const u=o['dq_unknown_dest_pct_'+k];if(u!=null&&u>=30)f.push(`unknown_dest_${k}>=30%`)}
  if([o.m2_pct24,o.m7a_pct,o.m7b1_pct,o.m7b2_pct].some(z=>z!=null&&z>100))f.push('pct>100');
  if(code!==c.code&&code=='MO-604M'){f.push('MO-604M_only_excl_Wyandotte_KS');o.m5_per10k_es_sh_th=null;o.m5_per10k_es_sh_th_ph=null;}
  o.dq_flags=f.join(';');rows.push(o);
 }
}
const cols=[...new Set(rows.flatMap(Object.keys))];
const esc=v=>v==null?'':/[",\n]/.test(String(v))?'"'+String(v).replace(/"/g,'""')+'"':String(v);
fs.writeFileSync(outCsv,[cols.join(','),...rows.map(o=>cols.map(c=>esc(o[c])).join(','))].join('\n')+'\n');
fs.writeFileSync(outJson,JSON.stringify(rows));
console.log('rows',rows.length);console.log(notes.join('\n'));