// DB-flow logic for loading a universal-import chart (UniversalChartAccount
// rows produced by tools/universal-map-logic.ts's applyChartMapping) into a
// company's chart of accounts. Deliberately a structural mirror of
// tools/lib/chart-load.ts's loadChart() — same conflict rules (an existing
// account with the same name but a DIFFERENT type is a conflict, reported,
// nothing inserted), same idempotency (same name + same type = skipped), and
// the same "caller owns the transaction" contract — but consuming the
// engine's already-mapped shape instead of QuickBooks ListingRows:
//  - `type` is already one of our five kinds (the type value-mapping step
//    resolved it); there is no unknown-type path here.
//  - `subtype` rides along when the engine confidently derived one
//    (0016_account_subtypes.sql's presentation grouping); null otherwise.
//  - `aliases` are the engine's leaf aliases (path-derived, uniqueness
//    already enforced across the whole file) — upserted like chart-load's
//    upsertAliases, ON CONFLICT DO NOTHING.
//  - `code`: the source file's own code when mapped; otherwise generated
//    from chart-logic's bucket scheme (1000 assets / 2000 liabilities /
//    3000 equity / 4000 income / 5000 COGS via subtype / 6000 expenses,
//    step 10), resuming after existing codes so re-runs never collide.
//
// See docs/superpowers/plans/2026-08-02-switch-from-anywhere.md, Task 4.

import type { Client } from "pg";
import { initialBucketCounters, type LedgerType, type AccountSubtype } from "../chart-logic.js";
import type { UniversalChartAccount } from "../universal-map-logic.js";

export type Queryable = Pick<Client, "query">;

const CODE_STEP = 10;

/** Ledger-type -> code bucket base. COGS accounts (recognized via subtype)
 *  get the 5000 range, same as chart-logic's QB_TYPE_CODE_BASE. */
const TYPE_CODE_BASE: Readonly<Record<LedgerType, number>> = Object.freeze({
  asset: 1000,
  liability: 2000,
  equity: 3000,
  income: 4000,
  expense: 6000,
});

function bucketBase(type: LedgerType, subtype: AccountSubtype | undefined): number {
  return subtype === "cost-of-goods-sold" ? 5000 : TYPE_CODE_BASE[type];
}

export interface UniversalTypeConflict {
  name: string;
  existingType: string;
  mappedType: LedgerType;
}

/** English, per-account problems with the file's own codes — found before
 *  any insert so the whole load aborts cleanly instead of half-applying. */
export interface UniversalCodeProblem {
  name: string;
  code: string;
  problem: string;
}

export interface UniversalAliasResult {
  created: number;
  skipped: number;
}

export interface UniversalChartLoadResult {
  totalInFile: number;
  accountsCreated: number;
  accountsSkipped: number;
  conflicts: UniversalTypeConflict[];
  codeProblems: UniversalCodeProblem[];
  aliases: UniversalAliasResult;
}

interface ExistingAccount { id: string; type: string; code: string }

async function loadExistingAccounts(
  client: Queryable, companyId: string,
): Promise<Map<string, ExistingAccount[]>> {
  const r = await client.query<{ id: string; name: string; type: string; code: string }>(
    "SELECT id, name, type, code FROM accounts WHERE company_id = $1", [companyId]);
  const map = new Map<string, ExistingAccount[]>();
  for (const row of r.rows) {
    const list = map.get(row.name) ?? [];
    list.push({ id: row.id, type: row.type, code: row.code });
    map.set(row.name, list);
  }
  return map;
}

/**
 * Loads mapped universal chart accounts into a company, idempotently.
 * Caller must be inside a transaction on `client` (and should hold the same
 * chart:load advisory lock the QB route takes) — on any conflicts or code
 * problems, NOTHING is inserted and the caller is expected to roll back.
 */
export async function loadUniversalChart(
  client: Queryable, companyId: string, accounts: UniversalChartAccount[],
): Promise<UniversalChartLoadResult> {
  const existing = await loadExistingAccounts(client, companyId);
  const existingCodes = new Set([...existing.values()].flat().map((a) => a.code));

  // Pass 1: split into inserts / skips / conflicts (same rules as
  // chart-load's findConflictsAndInserts), and vet file-provided codes.
  const toInsert: UniversalChartAccount[] = [];
  const conflicts: UniversalTypeConflict[] = [];
  const codeProblems: UniversalCodeProblem[] = [];
  let alreadyPresent = 0;
  const providedCodes = new Set<string>();

  for (const account of accounts) {
    const matches = existing.get(account.name);
    if (matches && matches.length > 1) {
      conflicts.push({
        name: account.name,
        existingType: `ambiguous (${matches.length} existing accounts share this name)`,
        mappedType: account.type,
      });
      continue;
    }
    if (matches && matches.length === 1) {
      if (matches[0]!.type !== account.type) {
        conflicts.push({
          name: account.name,
          existingType: matches[0]!.type,
          mappedType: account.type,
        });
      } else {
        alreadyPresent++;
      }
      continue;
    }

    if (account.code !== undefined) {
      if (providedCodes.has(account.code)) {
        codeProblems.push({
          name: account.name, code: account.code,
          problem: `account number ${account.code} appears more than once in the file.`,
        });
        continue;
      }
      if (existingCodes.has(account.code)) {
        codeProblems.push({
          name: account.name, code: account.code,
          problem: `account number ${account.code} is already used by an existing account.`,
        });
        continue;
      }
      providedCodes.add(account.code);
    }
    toInsert.push(account);
  }

  if (conflicts.length > 0 || codeProblems.length > 0) {
    return {
      totalInFile: accounts.length,
      accountsCreated: 0,
      accountsSkipped: alreadyPresent,
      conflicts,
      codeProblems,
      aliases: { created: 0, skipped: 0 },
    };
  }

  // Pass 2: generate codes for accounts the file gave none, resuming after
  // the highest existing code in each bucket and stepping over any code
  // already taken (existing or file-provided).
  const taken = new Set([...existingCodes, ...providedCodes]);
  const counters = initialBucketCounters([...taken]);
  function nextCode(base: number): string {
    let candidate = counters.get(base) ?? base;
    while (taken.has(String(candidate))) candidate += CODE_STEP;
    counters.set(base, candidate + CODE_STEP);
    taken.add(String(candidate));
    return String(candidate);
  }

  const nameToId = new Map<string, string>();
  for (const [name, matches] of existing) {
    if (matches.length === 1) nameToId.set(name, matches[0]!.id);
  }

  for (const account of toInsert) {
    const code = account.code ?? nextCode(bucketBase(account.type, account.subtype));
    const r = await client.query<{ id: string }>(
      `INSERT INTO accounts (company_id, code, name, type, subtype)
       VALUES ($1, $2, $3, $4, $5) RETURNING id`,
      [companyId, code, account.name, account.type, account.subtype ?? null]);
    nameToId.set(account.name, r.rows[0]!.id);
  }

  // Pass 3: leaf aliases (engine-computed) -> account_aliases, idempotent.
  let aliasesCreated = 0;
  let aliasesSkipped = 0;
  for (const account of accounts) {
    const accountId = nameToId.get(account.name);
    if (accountId === undefined) continue; // ambiguous existing name — no alias target
    for (const alias of account.aliases) {
      const r = await client.query(
        `INSERT INTO account_aliases (company_id, alias, account_id)
         VALUES ($1, $2, $3)
         ON CONFLICT (company_id, alias) DO NOTHING
         RETURNING id`,
        [companyId, alias, accountId]);
      if ((r.rowCount ?? 0) > 0) aliasesCreated++; else aliasesSkipped++;
    }
  }

  return {
    totalInFile: accounts.length,
    accountsCreated: toInsert.length,
    accountsSkipped: alreadyPresent,
    conflicts: [],
    codeProblems: [],
    aliases: { created: aliasesCreated, skipped: aliasesSkipped },
  };
}
