// Pure logic for turning a QuickBooks Account Listing export into a company
// chart of accounts. No I/O here on purpose — everything is testable without
// a database or a real xlsx file. See docs/superpowers/plans/2026-07-30-qb-migration-loader.md.

export type LedgerType = "asset" | "liability" | "equity" | "income" | "expense";

// QuickBooks Type -> ledger accounts.type (CLAUDE.md §3: type is one of the
// five enforced by the accounts.type CHECK constraint).
export const QB_TYPE_LEDGER_TYPE: Readonly<Record<string, LedgerType>> = Object.freeze({
  Bank: "asset",
  "Accounts Receivable": "asset",
  "Other Current Asset": "asset",
  "Fixed Asset": "asset",
  "Other Asset": "asset",
  "Accounts Payable": "liability",
  "Credit Card": "liability",
  "Other Current Liability": "liability",
  "Long Term Liability": "liability",
  Equity: "equity",
  Income: "income",
  "Other Income": "income",
  "Cost of Goods Sold": "expense",
  Expense: "expense",
  "Other Expense": "expense",
});

// Presentation subtypes (db/migrations/0016_account_subtypes.sql): the same
// sixteen values the accounts.subtype CHECK matrix allows, keyed here by the
// QuickBooks Type string. QB's Type IS our subtype taxonomy (that is where
// the taxonomy came from), so this mapping is one-to-one with two exceptions:
// QB has no 'line-of-credit' Type (QB users file those under Other Current
// Liability or Long Term Liability, which map through as such), and unknown
// QB types map to null (untagged) rather than guessing — unlike the LEDGER
// type above, subtype is presentational, so an unknown type degrades to
// "grouped under the bare type" instead of aborting the load.
export type AccountSubtype =
  | "bank" | "accounts-receivable" | "other-current-asset" | "fixed-asset" | "other-asset"
  | "credit-card" | "line-of-credit" | "accounts-payable" | "other-current-liability" | "long-term-liability"
  | "equity"
  | "income" | "other-income"
  | "expense" | "cost-of-goods-sold" | "other-expense";

export const QB_TYPE_SUBTYPE: Readonly<Record<string, AccountSubtype>> = Object.freeze({
  Bank: "bank",
  "Accounts Receivable": "accounts-receivable",
  "Other Current Asset": "other-current-asset",
  "Fixed Asset": "fixed-asset",
  "Other Asset": "other-asset",
  "Accounts Payable": "accounts-payable",
  "Credit Card": "credit-card",
  "Other Current Liability": "other-current-liability",
  "Long Term Liability": "long-term-liability",
  Equity: "equity",
  Income: "income",
  "Other Income": "other-income",
  "Cost of Goods Sold": "cost-of-goods-sold",
  Expense: "expense",
  "Other Expense": "other-expense",
});

/** QB Type string -> subtype, or null for anything unrecognized (untagged). */
export function qbTypeToSubtype(qbType: string): AccountSubtype | null {
  return QB_TYPE_SUBTYPE[qbType] ?? null;
}

// Code generation buckets. Note this is a DIFFERENT grouping than
// QB_TYPE_LEDGER_TYPE: Cost of Goods Sold gets its own 5000 range even
// though it maps to ledger type 'expense', per the plan's numbering scheme.
export const QB_TYPE_CODE_BASE: Readonly<Record<string, number>> = Object.freeze({
  Bank: 1000,
  "Accounts Receivable": 1000,
  "Other Current Asset": 1000,
  "Fixed Asset": 1000,
  "Other Asset": 1000,
  "Accounts Payable": 2000,
  "Credit Card": 2000,
  "Other Current Liability": 2000,
  "Long Term Liability": 2000,
  Equity: 3000,
  Income: 4000,
  "Other Income": 4000,
  "Cost of Goods Sold": 5000,
  Expense: 6000,
  "Other Expense": 6000,
});

const CODE_STEP = 10;

export interface ListingRow {
  name: string;
  qbType: string;
}

/**
 * Account Listing xlsx, Sheet1: header row 1; columns 3=Account, 5=Type.
 *
 * `sheetRows` follows readQbSheet's convention: the array is 1-indexed by
 * sheet row number (index 0 is unused/undefined, index 1 is sheet row 1 —
 * the header — index 2 is the first data row, etc.), and each row's own
 * cell values are 1-indexed by column the same way. Skips the header row,
 * blank rows, and any row missing a name or a type.
 */
export function parseListingRows(sheetRows: unknown[][]): ListingRow[] {
  const rows: ListingRow[] = [];
  for (let i = 2; i < sheetRows.length; i++) {
    const row = sheetRows[i];
    if (!row) continue;
    const name = cellText(row[3]);
    const qbType = cellText(row[5]);
    if (!name || !qbType) continue;
    rows.push({ name, qbType });
  }
  return rows;
}

/**
 * Extracts a trimmed string from an xlsx cell value. ExcelJS hands back
 * plain strings/numbers/Dates for ordinary cells, but rich-text and formula
 * cells arrive as objects — a naive `String(value)` turns those into the
 * useless "[object Object]". We handle the shapes we know how to read
 * (rich text runs, formulas with a scalar result) and throw a loud, named
 * error for anything else rather than silently mangling the data.
 */
export function cellText(value: unknown): string {
  if (value === undefined || value === null) return "";
  if (typeof value === "string") return value.trim();
  if (typeof value === "number" || value instanceof Date) return String(value).trim();

  if (typeof value === "object") {
    const obj = value as Record<string, unknown>;

    if (Array.isArray(obj["richText"])) {
      return (obj["richText"] as Array<{ text?: unknown }>)
        .map((run) => (typeof run?.text === "string" ? run.text : ""))
        .join("")
        .trim();
    }

    if ("formula" in obj || "sharedFormula" in obj) {
      const result = obj["result"];
      if (typeof result === "string" || typeof result === "number") {
        return String(result).trim();
      }
      throw new Error(
        "unsupported cell value in Account/Type column: formula cell without a " +
          `usable string/number result (got ${describeCellType(result)})`,
      );
    }

    throw new Error(
      `unsupported cell value in Account/Type column: ${describeCellType(value)}`,
    );
  }

  throw new Error(
    `unsupported cell value in Account/Type column: ${describeCellType(value)}`,
  );
}

function describeCellType(value: unknown): string {
  if (value === undefined) return "undefined";
  if (value === null) return "null";
  if (typeof value !== "object") return typeof value;
  const keys = Object.keys(value as object);
  return `object {${keys.join(", ")}}`;
}

/**
 * Names that appear more than once in the parsed listing itself. A
 * QuickBooks export should never contain the same account name twice, and
 * inserting it twice would silently create a duplicate account with a
 * different code — so this is a loud-abort condition, checked before any
 * database work.
 */
export function findDuplicateNames(rows: ListingRow[]): string[] {
  const seen = new Set<string>();
  const duplicates = new Set<string>();
  for (const row of rows) {
    if (seen.has(row.name)) {
      duplicates.add(row.name);
    } else {
      seen.add(row.name);
    }
  }
  return [...duplicates].sort();
}

export interface PlannedAccount {
  name: string;
  type: LedgerType;
  /** Presentation subtype from the QB Type (see QB_TYPE_SUBTYPE); null = untagged. */
  subtype: AccountSubtype | null;
  code: string;
  qbType: string;
}

export interface UnknownTypeRow {
  name: string;
  qbType: string;
}

export interface ChartPlan {
  accounts: PlannedAccount[];
  unknownTypes: UnknownTypeRow[];
}

const CODE_BUCKET_BASES = [1000, 2000, 3000, 4000, 5000, 6000];
const CODE_BUCKET_WIDTH = 999; // bucket range = base..base+999

/**
 * Given the codes already assigned to a company's existing accounts, works
 * out where each bucket's counter should resume so newly-generated codes
 * never collide with codes assigned on an earlier run (e.g. when the
 * upstream listing has gained new rows between imports). A bucket with no
 * existing codes starts at its base, same as a first-ever run.
 */
export function initialBucketCounters(existingCodes: readonly string[]): Map<number, number> {
  const counters = new Map<number, number>();
  const numericCodes = existingCodes
    .map((c) => Number(c))
    .filter((n) => Number.isInteger(n));

  for (const base of CODE_BUCKET_BASES) {
    const inBucket = numericCodes.filter((n) => n >= base && n <= base + CODE_BUCKET_WIDTH);
    counters.set(base, inBucket.length > 0 ? Math.max(...inBucket) + CODE_STEP : base);
  }
  return counters;
}

/**
 * Maps QB types to ledger types and generates stable, deterministic codes in
 * listing order. Unknown QuickBooks types are never guessed — they're
 * collected (ALL of them, not just the first) so the caller can abort loudly.
 *
 * `existingCodes` should be every code already assigned to the company's
 * accounts (from a prior run); omit it (or pass []) to plan a chart from
 * scratch, in which case every bucket starts at its base.
 */
export function planAccounts(
  rows: ListingRow[], existingCodes: readonly string[] = [],
): ChartPlan {
  const accounts: PlannedAccount[] = [];
  const unknownTypes: UnknownTypeRow[] = [];
  const counters = initialBucketCounters(existingCodes);

  for (const row of rows) {
    const type = QB_TYPE_LEDGER_TYPE[row.qbType];
    const base = QB_TYPE_CODE_BASE[row.qbType];
    if (type === undefined || base === undefined) {
      unknownTypes.push({ name: row.name, qbType: row.qbType });
      continue;
    }
    const code = counters.get(base) ?? base;
    counters.set(base, code + CODE_STEP);
    accounts.push({
      name: row.name, type, subtype: qbTypeToSubtype(row.qbType),
      code: String(code), qbType: row.qbType,
    });
  }

  return { accounts, unknownTypes };
}

export interface LeafAlias {
  leaf: string;
  accountName: string;
}

export interface LeafAliasResult {
  aliases: LeafAlias[];
  ambiguous: string[];
}

/**
 * For every "Parent:Leaf" account name, derive the leaf (text after the LAST
 * colon, so multi-level paths work too). If a leaf is unique across the
 * whole chart, it becomes an alias to that account. If two or more different
 * full names share a leaf, the leaf is ambiguous and gets NO alias — the
 * journal loader will flag any journal row that references it, loudly,
 * rather than us guessing which account it means.
 */
export function computeLeafAliases(names: string[]): LeafAliasResult {
  const leafToNames = new Map<string, Set<string>>();
  for (const name of names) {
    const idx = name.lastIndexOf(":");
    if (idx === -1) continue;
    const leaf = name.slice(idx + 1);
    if (!leaf) continue;
    const set = leafToNames.get(leaf) ?? new Set<string>();
    set.add(name);
    leafToNames.set(leaf, set);
  }

  const aliases: LeafAlias[] = [];
  const ambiguous: string[] = [];
  for (const [leaf, fullNames] of leafToNames) {
    if (fullNames.size === 1) {
      aliases.push({ leaf, accountName: [...fullNames][0]! });
    } else {
      ambiguous.push(leaf);
    }
  }
  return { aliases, ambiguous };
}
