// DB-flow logic extracted from tools/load-chart.ts so it can be reused by
// BOTH the chart:load CLI and the server's company-creation API endpoint
// (docs/superpowers/plans/2026-07-31-local-app.md, Task 2 refactor). Pure of
// console output — every function here returns a structured result; callers
// (the CLI wrapper or the API route) decide how to present it.
//
// Both functions take an already-connected client (pg Client or PoolClient)
// that the CALLER manages the transaction for — this is what lets
// ensureCompanyAndOwner() + loadChart() run in one atomic transaction from
// the CLI (mirroring the original tools/load-chart.ts behavior byte-for-byte)
// while the API can also call ensureCompanyAndOwner() alone, in its own
// short transaction, when it only needs to create a company (no chart).

import type { Client, PoolClient } from "pg";
import {
  parseListingRows,
  planAccounts,
  computeLeafAliases,
  type ListingRow,
  type PlannedAccount,
} from "../chart-logic.js";

/** Works for both pg.Client and pg.PoolClient — the two connection types
 *  every caller of this module actually has in hand. */
export type Queryable = Pick<Client, "query">;

export interface EnsureCompanyAndOwnerParams {
  name: string;
  ownerEmail?: string;
  ownerName?: string;
}

export interface EnsureCompanyAndOwnerResult {
  companyId: string;
  companyCreated: boolean;
  ownerId: string;
  ownerEmail: string;
  ownerCreated: boolean;
  membershipCreated: boolean;
}

async function upsertCompany(
  client: Queryable, name: string,
): Promise<{ id: string; created: boolean }> {
  const existing = await client.query<{ id: string }>(
    "SELECT id FROM companies WHERE name = $1", [name]);
  if (existing.rowCount === 1) {
    return { id: existing.rows[0]!.id, created: false };
  }
  if (existing.rowCount! > 1) {
    throw new Error(
      `ambiguous company name "${name}": ${existing.rowCount} companies already have this name`,
    );
  }
  const inserted = await client.query<{ id: string }>(
    "INSERT INTO companies (name) VALUES ($1) RETURNING id", [name]);
  return { id: inserted.rows[0]!.id, created: true };
}

async function upsertOwner(
  client: Queryable, email: string, displayName: string,
): Promise<{ id: string; created: boolean }> {
  const existing = await client.query<{ id: string }>(
    "SELECT id FROM users WHERE email = $1", [email]);
  if (existing.rowCount === 1) {
    return { id: existing.rows[0]!.id, created: false };
  }
  const inserted = await client.query<{ id: string }>(
    "INSERT INTO users (email, display_name) VALUES ($1, $2) RETURNING id",
    [email, displayName]);
  return { id: inserted.rows[0]!.id, created: true };
}

async function upsertMembership(
  client: Queryable, userId: string, companyId: string,
): Promise<{ created: boolean }> {
  const r = await client.query(
    `INSERT INTO user_companies (user_id, company_id, role)
     VALUES ($1, $2, 'owner')
     ON CONFLICT (user_id, company_id) DO NOTHING
     RETURNING user_id`,
    [userId, companyId]);
  return { created: (r.rowCount ?? 0) > 0 };
}

/**
 * Ensures a company (by name) and an owner user exist, and that the owner
 * has an 'owner' membership on the company — creating whichever of the
 * three don't already exist. Idempotent and safe to call concurrently for
 * the SAME company name: serialized via a transaction-scoped advisory lock,
 * so the caller MUST already be inside a transaction (BEGIN) on `client`.
 *
 * companies.name is deliberately NOT unique (CLAUDE.md §1: a future
 * multi-tenant SaaS may legitimately have two customers with the same
 * business name) — so instead of a DB constraint, concurrent calls for the
 * same name are serialized by the advisory lock: the second caller blocks
 * here until the first commits (or rolls back), then its own SELECT sees
 * the first call's committed row instead of racing it to INSERT.
 */
export async function ensureCompanyAndOwner(
  client: Queryable, params: EnsureCompanyAndOwnerParams,
): Promise<EnsureCompanyAndOwnerResult> {
  const ownerEmail = params.ownerEmail ?? "owner@local";
  const ownerName = params.ownerName ?? "Owner";

  await client.query(
    "SELECT pg_advisory_xact_lock(hashtext('chart:load:' || $1))", [params.name]);

  const company = await upsertCompany(client, params.name);
  const owner = await upsertOwner(client, ownerEmail, ownerName);
  const membership = await upsertMembership(client, owner.id, company.id);

  return {
    companyId: company.id,
    companyCreated: company.created,
    ownerId: owner.id,
    ownerEmail,
    ownerCreated: owner.created,
    membershipCreated: membership.created,
  };
}

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;
}

export interface TypeConflict {
  name: string;
  existingType: string;
  mappedType: string;
  qbType: string;
}

function findConflictsAndInserts(
  planned: PlannedAccount[], existing: Map<string, ExistingAccount[]>,
): { toInsert: PlannedAccount[]; conflicts: TypeConflict[]; alreadyPresent: number } {
  const toInsert: PlannedAccount[] = [];
  const conflicts: TypeConflict[] = [];
  let alreadyPresent = 0;

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

  return { toInsert, conflicts, alreadyPresent };
}

async function insertAccounts(
  client: Queryable, companyId: string, toInsert: PlannedAccount[],
): Promise<void> {
  for (const account of toInsert) {
    // subtype comes from the QB Type column (tools/chart-logic.ts's
    // QB_TYPE_SUBTYPE); null = untagged, taggable later in the chart screen.
    await client.query(
      `INSERT INTO accounts (company_id, code, name, type, subtype) VALUES ($1, $2, $3, $4, $5)`,
      [companyId, account.code, account.name, account.type, account.subtype]);
  }
}

export interface AliasResult {
  created: number;
  skipped: number;
  ambiguous: string[];
  missing: string[];
}

async function upsertAliases(
  client: Queryable, companyId: string, allAccountNames: string[],
): Promise<AliasResult> {
  const { aliases, ambiguous } = computeLeafAliases(allAccountNames);

  const nameRows = await client.query<{ id: string; name: string }>(
    "SELECT id, name FROM accounts WHERE company_id = $1", [companyId]);
  const nameToId = new Map(nameRows.rows.map((r) => [r.name, r.id]));

  let created = 0;
  let skipped = 0;
  const missing: string[] = [];
  for (const alias of aliases) {
    const accountId = nameToId.get(alias.accountName);
    if (accountId === undefined) {
      // Should not happen — every alias is derived from a name we just
      // ensured exists — but never silently drop data if it does.
      missing.push(alias.accountName);
      continue;
    }
    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.leaf, accountId]);
    if ((r.rowCount ?? 0) > 0) created++; else skipped++;
  }

  return { created, skipped, ambiguous, missing };
}

export interface ChartLoadResult {
  /** Every account name in the listing, mapped/coded — before conflict filtering. */
  totalInListing: number;
  accountsCreated: number;
  accountsSkipped: number;
  conflicts: TypeConflict[];
  aliases: AliasResult;
}

/**
 * Loads a parsed QuickBooks Account Listing into a company's chart of
 * accounts, idempotently. Never inserts an account whose name already
 * exists with a DIFFERENT type — those are reported as conflicts instead
 * (the caller decides whether that means aborting/rolling back). Caller
 * must have already validated the listing (no unknown types, no in-file
 * duplicate names — see chart-logic.ts) and must be inside a transaction
 * on `client` if atomicity with ensureCompanyAndOwner() is wanted.
 */
export async function loadChart(
  client: Queryable, companyId: string, listingRows: ListingRow[],
): Promise<ChartLoadResult> {
  const existing = await loadExistingAccounts(client, companyId);
  const existingCodes = [...existing.values()].flat().map((a) => a.code);
  // Authoritative plan: codes for NEW accounts resume after the highest
  // existing code in each bucket, so they can't collide with codes assigned
  // on an earlier run even if the listing gained rows.
  const plan = planAccounts(listingRows, existingCodes);
  const { toInsert, conflicts, alreadyPresent } = findConflictsAndInserts(plan.accounts, existing);

  if (conflicts.length === 0) {
    await insertAccounts(client, companyId, toInsert);
  }

  const allNames = plan.accounts.map((a) => a.name);
  const aliases = conflicts.length === 0
    ? await upsertAliases(client, companyId, allNames)
    : { created: 0, skipped: 0, ambiguous: [], missing: [] };

  return {
    totalInListing: plan.accounts.length,
    accountsCreated: toInsert.length,
    accountsSkipped: alreadyPresent,
    conflicts,
    aliases,
  };
}

// Re-exported so callers can parse a listing without importing chart-logic.js
// directly (keeps "one import for the whole flow" convenient); not required,
// but harmless and mirrors journal-load.ts's re-export of parsing helpers.
export { parseListingRows };
export type { ListingRow };
