import {additionalIncomeSources} from './financial-goals';
export type ImportRow = { id: number; name: string; amount: number | null; kind: 'income' | 'expense' | 'skip'; category: string; include: boolean; note: string };
export const categories = ['Debt repayments','Housing','Utilities','Medical aid','Groceries','Transport','Internet & Phone','Insurance','Personal','Other'];
export function parseAmount(value: unknown): number | null {
  if(typeof value==='number')return Number.isFinite(value)?value:null;
  if(typeof value!=='string'||!value.trim())return null;
  let s=value.trim().replace(/\u00a0/g,' ').replace(/(?:ZAR|rand|R|£|\$|€)/gi,'').replace(/\s/g,'');
  const negative=/^\(.*\)$/.test(s);s=s.replace(/[()]/g,'');
  if(!/^-?\d[\d.,]*$/.test(s))return null;
  if(s.includes('.')&&s.includes(',')){const decimal=s.lastIndexOf('.')>s.lastIndexOf(',')?'.':',';s=s.split(decimal==='.'?',':'.').join('').replace(decimal,'.');}
  else if(s.includes(',')){s=/,\d{1,2}$/.test(s)?s.replace(/,/g,(c,index)=>index===s.lastIndexOf(',')?'.':''):s.replace(/,/g,'');}
  else if((s.match(/\./g)||[]).length>1)s=s.replace(/\./g,'');
  const n=Number(s);return Number.isFinite(n)?(negative?-n:n):null;
}
export function suggestCategory(name:string){const s=name.toLowerCase();if(/loan|bond|mortgage|credit card|repayment|instalment|installment|car payment|vehicle finance|debt/.test(s))return 'Debt repayments';if(/rent|levy|rates|housing|home|municipality/.test(s))return 'Housing';if(/water|electric|utility|utilities|gas/.test(s))return 'Utilities';if(/medical|health|doctor|pharmacy/.test(s))return 'Medical aid';if(/grocery|groceries|food|supermarket/.test(s))return 'Groceries';if(/fuel|petrol|diesel|transport|uber|taxi/.test(s))return 'Transport';if(/internet|wifi|fibre|phone|mobile|data|telkom|webafrica/.test(s))return 'Internet & Phone';if(/insurance|premium|funeral|avbob/.test(s))return 'Insurance';if(/clothes|salon|beauty|personal/.test(s))return 'Personal';return 'Other';}
/** Detect labelled tables and simple title-plus-budget layouts without mistaking a title for an amount header. */
export function detectBudgetColumns(grid:unknown[][]){
 const width=Math.max(0,...grid.map(row=>row.length));
 const nameHeader=/^(description|item|name|category|expense|income|expenses|income source)$/i;
 const amountHeader=/^(amount|monthly(?: amount)?|budget(?: amount)?|cost|value|planned|actual)$/i;
 const labelled=grid.findIndex(row=>row.some(v=>nameHeader.test(String(v??'').trim()))&&row.some(v=>amountHeader.test(String(v??'').trim())));
 let header=labelled,nameCol=-1,amountCol=-1,typeCol=-1,categoryCol=-1,frequencyCol=-1;
 if(labelled>=0){const cols=grid[labelled].map(v=>String(v??'').trim());nameCol=cols.findIndex(v=>nameHeader.test(v));amountCol=cols.findIndex((v,i)=>i!==nameCol&&amountHeader.test(v));typeCol=cols.findIndex(v=>/^type$|income.*expense/i.test(v));categoryCol=cols.findIndex(v=>/^category$/i.test(v));frequencyCol=cols.findIndex(v=>/frequency|period/i.test(v));}
 else {
  const counts=Array.from({length:width},(_,col)=>grid.reduce((n,row)=>n+(parseAmount(row[col])!==null?1:0),0));
  amountCol=counts.indexOf(Math.max(...counts));
  const texts=Array.from({length:width},(_,col)=>col===amountCol?-1:grid.reduce((n,row)=>n+(parseAmount(row[amountCol])!==null&&typeof row[col]==='string'&&String(row[col]).trim()&&parseAmount(row[col])===null?1:0),0));
  nameCol=texts.indexOf(Math.max(...texts));
  const first=grid.findIndex(row=>parseAmount(row[amountCol])!==null&&String(row[nameCol]??'').trim());header=first>=0?first-1:0;
  categoryCol=Array.from({length:width},(_,col)=>col).find(col=>col!==nameCol&&col!==amountCol&&grid.filter(row=>categories.some(category=>String(row[col]??'').trim().toLowerCase()===category.toLowerCase())).length>=2)??-1;
 }
 if(amountCol===nameCol||amountCol<0){amountCol=nameCol===0?1:0;}
 return {header,nameCol:Math.max(0,nameCol),amountCol,typeCol,categoryCol,frequencyCol};
}
export function monthlyFactor(frequency:string){const s=frequency.trim().toLowerCase();if(/annual|year/.test(s))return 1/12;if(/quarter/.test(s))return 1/3;if(/fortnight|biweekly|bi-weekly/.test(s))return 26/12;if(/week/.test(s))return 52/12;return 1;}
export function prepareRows(grid: unknown[][], header:number, nameCol:number, amountCol:number, typeCol:number, categoryCol:number, frequencyCol:number, frequency:string):ImportRow[]{
 return grid.slice(header+1).map((cells,i)=>{const name=String(cells[nameCol]??'').trim().slice(0,120);const raw=parseAmount(cells[amountCol]);const type=typeCol>=0?String(cells[typeCol]??'').toLowerCase():'';const skip=/\b(total|subtotal|balance|surplus|remaining|difference|available|left)\b/i.test(name)||/^net (?:income|profit|cash)/i.test(name)||/total|subtotal/.test(type);const income=/income|credit|revenue/.test(type)?true:/expense|debit|cost/.test(type)?false:/income|salary|wage|revenue|contribution/.test(name.toLowerCase());const rowFrequency=frequencyCol>=0&&cells[frequencyCol]?String(cells[frequencyCol]):frequency;const amount=raw===null?null:Math.abs(raw)*monthlyFactor(rowFrequency);const category=categoryCol>=0?String(cells[categoryCol]??'').trim():'';return {id:header+i+2,name,amount,kind:skip?'skip':income?'income':'expense',category:categories.includes(category)?category:suggestCategory(category+' '+name),include:!!name&&!skip&&amount!==null,note:skip?'Summary row excluded to avoid double counting.':raw===null?'No numeric amount. Enter a value to include this row.':raw<0?'Negative source amount treated as an outflow. Review its type.':rowFrequency.toLowerCase()!=='monthly'?'Converted to a monthly amount.':''} as ImportRow}).filter(r=>r.name||r.amount!==null);
}
export function aggregateRows(rows:ImportRow[]){const chosen=rows.filter(r=>r.include&&r.kind!=='skip');if(!chosen.length)throw Error('Select at least one income or expense row.');if(chosen.some(r=>!r.name.trim()||r.amount===null||!Number.isFinite(r.amount)||r.amount<0))throw Error('Check the name and amount on every selected row.');const income=chosen.filter(r=>r.kind==='income').reduce((s,r)=>s+r.amount!,0);const expenses=chosen.filter(r=>r.kind==='expense');const totals=new Map<string,number>();for(const r of expenses)totals.set(r.category,(totals.get(r.category)||0)+r.amount!);return {income,hasIncome:chosen.some(r=>r.kind==='income'),budget:[{name:'Debt repayments',amount:totals.get('Debt repayments')||0},...Array.from(totals).filter(([n])=>n!=='Debt repayments').map(([name,amount])=>({name,amount}))],expenseTotal:expenses.reduce((s,r)=>s+r.amount!,0)};}
export function parseCsv(text:string):string[][]{const rows:string[][]=[];let row:string[]=[],cell='',quoted=false;const delimiter=(text.split(/\r?\n/)[0].match(/;/g)||[]).length>(text.split(/\r?\n/)[0].match(/,/g)||[]).length?';':',';for(let i=0;i<text.length;i++){const c=text[i];if(c==='"'){if(quoted&&text[i+1]==='"'){cell+='"';i++;}else quoted=!quoted;}else if(c===delimiter&&!quoted){row.push(cell);cell='';}else if((c==='\n'||c==='\r')&&!quoted){if(c==='\r'&&text[i+1]==='\n')i++;row.push(cell);rows.push(row);row=[];cell='';}else cell+=c;}if(quoted)throw Error('CSV contains an unclosed quoted value.');if(cell||row.length){row.push(cell);rows.push(row);}return rows;}

export function budgetSnapshot(p:any){return {additionalIncome:structuredClone(additionalIncomeSources(p)),budget:structuredClone(p.budget),incomeOther:structuredClone(p.incomeOther??{name:'',amount:0}),incomeFields:Object.fromEntries([5,11,12,13].map(k=>[k,p.assumptions[k]]))};}
export function applyImportedBudget(p:any,data:ReturnType<typeof aggregateRows>,mode:string){if(data.hasIncome){p.assumptions[5]=mode==='merge'?p.assumptions[5]+data.income:data.income;if(mode==='replace'){p.incomeOther={name:'',amount:0};p.additionalIncome=[];}for(const key of [11,12,13])p.assumptions[key]=0;}if(mode==='replace')p.budget=structuredClone(data.budget);else for(const b of data.budget){const found=p.budget.find((v:any)=>v.name===b.name);if(found)found.amount+=b.amount;else p.budget.push({...b});}}
export function restoreBudget(p:any,snapshot:ReturnType<typeof budgetSnapshot>){p.budget=structuredClone(snapshot.budget);p.incomeOther=structuredClone(snapshot.incomeOther);p.additionalIncome=structuredClone(snapshot.additionalIncome);for(const [key,value] of Object.entries(snapshot.incomeFields))p.assumptions[key]=value;}