parser.ts

/**
 * Low-level spreadsheet helpers plus the Zerodha equity extractor.
 *
 * A broker sheet is not a clean table. It is a stack of sections, each shaped
 * like:
 *
 *   Equity - Short Term          <- section title (single populated cell)
 *   Symbol  ISIN  Entry Date ... <- column header row
 *   SIRCA   ...                  <- data rows
 *
 * `scanSections` turns that shape into `RawSection[]` (title + resolved column
 * map + data rows) so the format-specific extractors in `formats.ts` can read
 * any broker layout without re-implementing the row scanning. Column indices
 * are resolved from header labels at runtime - no positional assumptions.
 */

import * as XLSX from "xlsx";

import { makeDay, type Day } from "./periods";

const HEADER_LABEL = "symbol";
const EXIT_DATE_COLUMN = "exit date";
const BUY_VALUE_COLUMN = "buy value";
const SELL_VALUE_COLUMN = "sell value";
export const DEFAULT_VALUE_COLUMN = "Profit";

const SHEET_DATE_RE = /(\d{4})-(\d{2})-(\d{2})/;

/** A single realized trade extracted from a statement. */
export interface Trade {
  section: string;
  /** Table this trade belongs to (e.g. "Stocks", "MF Equity"); undefined = default. */
  category?: string;
  symbol: string;
  exitDate: Day;
  value: number;
  buyValue: number;
  sellValue: number;
}

export interface ParseResult {
  trades: Trade[];
  sheetName: string | null;
  /** All worksheet names in the workbook (for a sheet picker). */
  sheetNames: string[];
  /** Distinct column header labels seen, preserving original casing. */
  valueColumns: string[];
  /** FY start year parsed from the statement, if the format can infer it. */
  fyStartYear?: number | null;
  /** Detected format id, if parsed through the format layer. */
  formatId?: string;
  /** Non-fatal warnings (e.g. unclassified rows, reconciliation mismatches). */
  warnings?: string[];
}

export type Cell = string | number | boolean | Date | null | undefined;
export type Row = Cell[];

export function clean(cell: Cell): string | null {
  if (cell === null || cell === undefined) return null;
  const text = String(cell).trim();
  return text.length ? text : null;
}

export function nonEmptyCount(row: Row): number {
  let count = 0;
  for (const cell of row) if (clean(cell) !== null) count += 1;
  return count;
}

export function buildColumnMap(row: Row): {
  byKey: Map<string, number>;
  labels: string[];
} {
  const byKey = new Map<string, number>();
  const labels: string[] = [];
  row.forEach((cell, idx) => {
    const label = clean(cell);
    if (label !== null) {
      const key = label.toLowerCase();
      if (!byKey.has(key)) byKey.set(key, idx);
      labels.push(label);
    }
  });
  return { byKey, labels };
}

export function parseDate(value: Cell): Day | null {
  if (value === null || value === undefined) return null;
  if (value instanceof Date) {
    return makeDay(value.getFullYear(), value.getMonth() + 1, value.getDate());
  }
  const text = String(value).trim();
  if (!text) return null;

  // ISO first (e.g. "2025-09-05" or "2025-09-05T00:00:00").
  const iso = text.slice(0, 10).match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (iso) return makeDay(+iso[1], +iso[2], +iso[3]);

  // dd-mm-yyyy / mm-dd-yyyy with 2- or 4-digit year (text fallback).
  const parts = text.match(/^(\d{1,2})[-/](\d{1,2})[-/](\d{2,4})$/);
  if (parts) {
    const a = +parts[1];
    const b = +parts[2];
    let year = +parts[3];
    if (year < 100) year += year >= 70 ? 1900 : 2000;
    // Disambiguate day vs month where possible, else assume day-month.
    if (a > 12 && b <= 12) return makeDay(year, b, a);
    if (b > 12 && a <= 12) return makeDay(year, a, b);
    return makeDay(year, b, a);
  }
  return null;
}

export function parseFloatCell(value: Cell): number | null {
  if (value === null || value === undefined) return null;
  if (typeof value === "number") return Number.isFinite(value) ? value : null;
  const text = String(value).trim().replace(/,/g, "");
  if (!text) return null;
  const n = Number(text);
  return Number.isFinite(n) ? n : null;
}

/** A stack section: its title, resolved column map, and raw data rows. */
export interface RawSection {
  title: string;
  /** label(lowercased) -> column index. */
  columns: Map<string, number>;
  /** Header labels in original casing. */
  labels: string[];
  rows: Row[];
}

/**
 * Split an array-of-arrays worksheet into sections by row shape. A row whose
 * first cell (lowercased) is in `headerLabels` starts a column header; any other
 * single-populated-cell row is a section title; everything else is data for the
 * current section.
 */
export function scanSections(
  rows: Row[],
  headerLabels: string[],
): RawSection[] {
  const headerSet = new Set(headerLabels.map((l) => l.toLowerCase()));

  const isHeader = (row: Row): boolean => {
    const first = row.length ? clean(row[0]) : null;
    return first !== null && headerSet.has(first.toLowerCase());
  };
  const isTitle = (row: Row): boolean => {
    const first = row.length ? clean(row[0]) : null;
    return first !== null && !isHeader(row) && nonEmptyCount(row) === 1;
  };

  const sections: RawSection[] = [];
  let current: RawSection | null = null;

  for (const row of rows) {
    if (nonEmptyCount(row) === 0) continue;
    if (isTitle(row)) {
      current = {
        title: clean(row[0]) ?? "",
        columns: new Map(),
        labels: [],
        rows: [],
      };
      sections.push(current);
      continue;
    }
    if (isHeader(row)) {
      const built = buildColumnMap(row);
      if (!current) {
        current = { title: "", columns: new Map(), labels: [], rows: [] };
        sections.push(current);
      }
      current.columns = built.byKey;
      current.labels = built.labels;
      continue;
    }
    if (current) current.rows.push(row);
  }
  return sections;
}

/** Read the array-of-arrays for a worksheet (1-based index or exact name). */
export function sheetRows(
  workbook: XLSX.WorkBook,
  sheet: number | string = 1,
): { sheetName: string | null; rows: Row[] } {
  const sheetNames = workbook.SheetNames;
  let sheetName: string | null;
  if (typeof sheet === "number") {
    sheetName = sheetNames[sheet - 1] ?? sheetNames[0] ?? null;
  } else {
    sheetName = sheetNames.includes(sheet) ? sheet : (sheetNames[0] ?? null);
  }
  if (sheetName === null) return { sheetName: null, rows: [] };

  const ws = workbook.Sheets[sheetName];
  // raw: true keeps numbers at full precision and, combined with cellDates on
  // read, yields real Date objects - avoiding locale-dependent text formats
  // like "9/19/25" that break parsing.
  const rows = XLSX.utils.sheet_to_json<Row>(ws, {
    header: 1,
    raw: true,
    defval: null,
    blankrows: false,
  });
  return { sheetName, rows };
}

/** Parse an FY start year from a sheet name like "Tradewise Exits from 2025-04-01". */
export function sheetFyStartYear(sheetName: string | null): number | null {
  if (!sheetName) return null;
  const match = sheetName.match(SHEET_DATE_RE);
  if (!match) return null;
  const year = +match[1];
  const month = +match[2];
  return month >= 4 ? year : year - 1;
}

/**
 * Extract all realized trades from a Zerodha "Tradewise Exits" worksheet.
 * `sheet` is a 1-based index or an exact sheet name.
 */
export function extractTrades(
  workbook: XLSX.WorkBook,
  sheet: number | string = 1,
  valueColumn: string = DEFAULT_VALUE_COLUMN,
): ParseResult {
  const { sheetName, rows } = sheetRows(workbook, sheet);
  const sheetNames = workbook.SheetNames;
  if (sheetName === null) {
    return { trades: [], sheetName: null, sheetNames, valueColumns: [] };
  }

  const sections = scanSections(rows, [HEADER_LABEL]);
  const valueKey = valueColumn.trim().toLowerCase();
  const trades: Trade[] = [];
  const valueColumns: string[] = [];
  const seenLabels = new Set<string>();

  for (const sec of sections) {
    if (sec.columns.size === 0) continue;
    for (const label of sec.labels) {
      const key = label.toLowerCase();
      if (!seenLabels.has(key)) {
        seenLabels.add(key);
        valueColumns.push(label);
      }
    }

    const exitIdx = sec.columns.get(EXIT_DATE_COLUMN);
    const valueIdx = sec.columns.get(valueKey);
    if (exitIdx === undefined || valueIdx === undefined) continue;
    const buyIdx = sec.columns.get(BUY_VALUE_COLUMN);
    const sellIdx = sec.columns.get(SELL_VALUE_COLUMN);
    const symbolIdx = sec.columns.get("symbol") ?? 0;

    for (const row of sec.rows) {
      const exitDate = exitIdx < row.length ? parseDate(row[exitIdx]) : null;
      const value =
        valueIdx < row.length ? parseFloatCell(row[valueIdx]) : null;
      if (exitDate === null || value === null) continue;

      const buyValue =
        (buyIdx !== undefined && buyIdx < row.length
          ? parseFloatCell(row[buyIdx])
          : null) ?? 0;
      const sellValue =
        (sellIdx !== undefined && sellIdx < row.length
          ? parseFloatCell(row[sellIdx])
          : null) ?? 0;
      const symbol = clean(row[symbolIdx]) ?? "";

      trades.push({
        section: sec.title,
        symbol,
        exitDate,
        value,
        buyValue,
        sellValue,
      });
    }
  }

  return {
    trades,
    sheetName,
    sheetNames,
    valueColumns,
    fyStartYear: sheetFyStartYear(sheetName),
  };
}

/** Read an uploaded file into a SheetJS workbook (fully client-side). */
export async function readWorkbook(file: File): Promise<XLSX.WorkBook> {
  const buffer = await file.arrayBuffer();
  return XLSX.read(buffer, { type: "array", cellDates: true });
}