// DB-flow logic extracted from tools/load-quickbooks.ts so it can be reused
// by BOTH the qb:load CLI and the server's QuickBooks-restore API endpoints
// (docs/superpowers/plans/2026-07-31-local-app.md, Task 2 refactor; the API
// endpoints themselves are Task 4). Pure of console output — every function
// here returns a structured result; callers decide how to present it.
//
// *** CONNECTION CONTRACT ***
// loadJournalYears() and postYears() take a pg Pool, NOT a client, and
// manage their own connections and transactions internally: for each unit
// of work (one year's load, one year's post) they check out their own
// client, run SELECT set_config('app.company_id', $1, false) — SESSION
// level, mirroring the original CLI — do the work (including the per-year
// BEGIN/COMMIT that loadYear() needs), and release the client back to the
// pool. NEVER call these functions from inside db.withCompany() or any
// other already-open transaction: an earlier version took an open client
// directly, and loadYear()'s internal COMMIT would silently end the
// caller's outer transaction (and the caller's transaction-local
// app.company_id would revert mid-import, right after that first COMMIT).
// The Pool passed in should be a small, dedicated one for the run (the CLI
// builds its own from APP_DATABASE_URL) — not the shared request-serving
// pool a server would use for db.withCompany(), which never enters this
// module at all.

import type { Pool, PoolClient } from "pg";
import { sliceSha, type JournalEntry, type JournalLine } from "../journal-logic.js";

/** Default batch source — the original QuickBooks Journal pipeline. The
 *  universal import (Switch From Anywhere, Task 4) reuses this whole module
 *  with source 'universal:<slug>' instead; every function below that touches
 *  import_batches.source takes it as a parameter with this default so the
 *  QB CLI/API callers are byte-for-byte unaffected. */
export const QUICKBOOKS_JOURNAL_SOURCE = "quickbooks_journal";

/** Builds the staging row's raw jsonb for one line (CLAUDE.md §12: the raw
 *  source row is kept forever). The default reconstructs the QuickBooks
 *  Journal report's own column shape from the mapped line — the convention
 *  the QB pipeline has always used (the uploaded file itself is also kept,
 *  under imports/uploads/). The universal loader passes its own provider
 *  that carries the ORIGINAL parsed spreadsheet row instead. */
export type RawRowProvider = (entry: JournalEntry, line: JournalLine) => unknown;

/** Any already-connected client — what the internal per-unit-of-work
 *  helpers below actually operate on, once loadJournalYears/postYears have
 *  checked one out of the pool and set its session company scope. */
export type Queryable = Pick<PoolClient, "query">;

/**
 * Checks out a client, sets app.company_id for the SESSION (not just one
 * transaction — mirrors the original CLI, and lets `fn` run its own nested
 * BEGIN/COMMIT), runs `fn`, and always releases. This is the only place a
 * client is checked out in this module — every unit of work goes through it.
 */
async function withSessionCompany<T>(
  pool: Pool, companyId: string, fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("SELECT set_config('app.company_id', $1, false)", [companyId]);
    return await fn(client);
  } finally {
    client.release();
  }
}

const DUPLICATE_SHA_CONSTRAINT = "import_batches_company_id_file_sha256_key";
const STAGING_CHUNK_SIZE = 500;

// Statuses resolve_batch/validate_batch will still run against (they RAISE
// on 'posted'/'rolled_back' — see 0003_import_layer.sql). 'loaded'/'resolved'
// means a previous run died mid-pipeline; 'validated'/'failed' means a
// previous run completed but the chart may have changed since (e.g. an
// alias was added to fix an unmapped-account error) and re-running makes
// the report reflect that, not stale history.
const REVALIDATABLE_STATUSES = new Set(["loaded", "resolved", "validated", "failed"]);

export function shouldRevalidate(status: string): boolean {
  return REVALIDATABLE_STATUSES.has(status);
}

export interface CompanyAndOwner {
  companyId: string;
  ownerId: string;
}

/**
 * Resolves the user a company's journal batches are attributed to (the
 * earliest 'owner' member). Shared by resolveCompanyAndOwner (CLI, resolves
 * by company NAME) and the server's import routes (already have a
 * companyId, from X-Company-Id — see server/src/imports.ts). Read-only;
 * never creates anything. `adminClient` bypasses RLS by design — this
 * lookup has to work before any app.company_id scope is established.
 */
export async function findOwnerId(adminClient: Queryable, companyId: string): Promise<string> {
  const owners = await adminClient.query<{ id: string }>(
    `SELECT u.id FROM user_companies uc
       JOIN users u ON u.id = uc.user_id
      WHERE uc.company_id = $1 AND uc.role = 'owner'
      ORDER BY u.created_at, u.id
      LIMIT 1`,
    [companyId]);
  if (owners.rowCount === 0) {
    throw new Error(
      `company ${companyId} has no owner user — journal batches need a user to attribute created_by to`);
  }
  return owners.rows[0]!.id;
}

/**
 * Resolves a company by name (must already exist — run chart:load / the
 * company-creation API first) and its owner (see findOwnerId). Read-only;
 * never creates anything. `adminClient` bypasses RLS by design — this
 * lookup has to work before any app.company_id scope is established.
 */
export async function resolveCompanyAndOwner(
  adminClient: Queryable, companyName: string,
): Promise<CompanyAndOwner> {
  const companies = await adminClient.query<{ id: string }>(
    "SELECT id FROM companies WHERE name = $1", [companyName]);
  if (companies.rowCount === 0) {
    throw new Error(
      `no company named "${companyName}" — run chart:load first to create it`);
  }
  if (companies.rowCount! > 1) {
    throw new Error(
      `ambiguous company name "${companyName}": ${companies.rowCount} companies have this name`);
  }
  const companyId = companies.rows[0]!.id;

  try {
    const ownerId = await findOwnerId(adminClient, companyId);
    return { companyId, ownerId };
  } catch {
    throw new Error(
      `company "${companyName}" has no owner user — run chart:load first ` +
        `(journal batches need a user to attribute created_by to)`);
  }
}

interface ErrorGroup { error: string; count: number }

export interface YearResult {
  year: number;
  batchId: number;
  isNew: boolean;
  status: string;
  rowCount: number;
  entryCount: number;
  errorCount: number;
  errorGroups: ErrorGroup[];
  /** well-formed zero-amount lines (voided txn or capped payroll tax) */
  voidCount: number;
  /** entry_keys whose lines are ALL void — no journal entry gets created */
  fullyVoidEntryCount: number;
}

function isDuplicateShaViolation(err: unknown): boolean {
  const e = err as { code?: string; constraint?: string };
  return e?.code === "23505" && e?.constraint === DUPLICATE_SHA_CONSTRAINT;
}

async function insertBatch(
  client: Queryable, companyId: string, source: string, filename: string, sha: string,
  userId: string,
): Promise<number> {
  const r = await client.query<{ id: string }>(
    `INSERT INTO import_batches (company_id, source, filename, file_sha256, created_by)
     VALUES ($1, $2, $3, $4, $5) RETURNING id`,
    [companyId, source, filename, sha, userId]);
  return Number(r.rows[0]!.id);
}

/** The QB pipeline's original raw shape — reconstructs the Journal report's
 *  column names from the mapped line (see RawRowProvider above). */
function qbRawRow(entry: JournalEntry, line: JournalLine): unknown {
  return {
    "Trans #": line.entryKey,
    Type: line.type,
    Date: entry.dateIso,
    Num: line.num,
    Name: line.name,
    Memo: line.memo,
    Account: line.account,
    Debit: line.debit,
    Credit: line.credit,
  };
}

/** Multi-row INSERT in chunks — thousands of single-row awaits would be slow. */
async function insertStagingRows(
  client: Queryable, companyId: string, batchId: number, entries: JournalEntry[],
  rawForLine: RawRowProvider = qbRawRow,
): Promise<number> {
  const rows: unknown[][] = [];
  for (const entry of entries) {
    for (const line of entry.lines) {
      rows.push([
        companyId, batchId, line.entryKey, line.type, entry.dateIso,
        line.num, line.name, line.memo, line.account, line.debit, line.credit,
        JSON.stringify(rawForLine(entry, line)),
      ]);
    }
  }

  const COLS = 12;
  for (let offset = 0; offset < rows.length; offset += STAGING_CHUNK_SIZE) {
    const chunk = rows.slice(offset, offset + STAGING_CHUNK_SIZE);
    const placeholders: string[] = [];
    const params: unknown[] = [];
    chunk.forEach((row, ri) => {
      const base = ri * COLS;
      const cells = Array.from({ length: COLS }, (_, ci) => {
        const p = `$${base + ci + 1}`;
        return ci === COLS - 1 ? `${p}::jsonb` : p; // raw column is jsonb
      });
      placeholders.push(`(${cells.join(", ")})`);
      params.push(...row);
    });
    await client.query(
      `INSERT INTO staging_journal
         (company_id, batch_id, entry_key, txn_type, txn_date, doc_num, name, memo,
          account_raw, debit_raw, credit_raw, raw)
       VALUES ${placeholders.join(", ")}`,
      params,
    );
  }
  return rows.length;
}

async function loadYear(
  appClient: Queryable, companyId: string, ownerId: string,
  basename: string, year: number, entries: JournalEntry[],
  source: string, rawForLine?: RawRowProvider, shaBasename?: string,
): Promise<{ batchId: number; isNew: boolean }> {
  const filename = `${basename}#${year}`;
  // shaBasename decouples batch identity from the uploaded FILENAME: the
  // universal loader passes a constant ("universal") so the sha is derived
  // from year + entry CONTENT only — the same export re-uploaded under a
  // different name (or a different import label) collides here instead of
  // silently posting the books twice. The QB pipeline keeps its original
  // basename-salted sha (its idempotency tests and prior batches depend on
  // it, and its external_ref uniqueness already catches renamed re-imports).
  const sha = sliceSha(shaBasename ?? basename, year, entries);

  await appClient.query("BEGIN");
  try {
    const batchId = await insertBatch(appClient, companyId, source, filename, sha, ownerId);
    await insertStagingRows(appClient, companyId, batchId, entries, rawForLine);
    await appClient.query("COMMIT");
    return { batchId, isNew: true };
  } catch (err) {
    await appClient.query("ROLLBACK").catch(() => {});
    if (!isDuplicateShaViolation(err)) throw err;

    // Recovery is SOURCE-FILTERED on purpose: a sha held by a batch of a
    // DIFFERENT source must never be silently adopted (it would mislabel the
    // batch and orphan the caller's post step) — in that case the duplicate
    // violation is rethrown and surfaces as "this file was already imported".
    const existing = await appClient.query<{ id: string }>(
      `SELECT id FROM import_batches
        WHERE company_id = $1 AND file_sha256 = $2 AND source = $3`,
      [companyId, sha, source]);
    if (existing.rowCount !== 1) throw err;
    return { batchId: Number(existing.rows[0]!.id), isNew: false };
  }
}

async function getBatchStatus(
  appClient: Queryable, companyId: string, batchId: number,
): Promise<string> {
  const r = await appClient.query<{ status: string }>(
    `SELECT status FROM import_batches WHERE company_id = $1 AND id = $2`,
    [companyId, batchId]);
  if (r.rowCount !== 1) throw new Error(`batch ${batchId} not found`);
  return r.rows[0]!.status;
}

async function reportBatch(
  appClient: Queryable, companyId: string, year: number, batchId: number, isNew: boolean,
): Promise<YearResult> {
  const status = await getBatchStatus(appClient, companyId, batchId);
  const totals = await appClient.query<
    { rows: string; entries: string; errors: string; voids: string }
  >(
    `SELECT count(*) AS rows, count(DISTINCT entry_key) AS entries,
            count(*) FILTER (WHERE status = 'error') AS errors,
            count(*) FILTER (WHERE status = 'void') AS voids
       FROM staging_journal WHERE company_id = $1 AND batch_id = $2`,
    [companyId, batchId]);
  const groups = await appClient.query<{ error: string; n: string }>(
    `SELECT error, count(*) AS n FROM staging_journal
      WHERE company_id = $1 AND batch_id = $2 AND status = 'error'
      GROUP BY error ORDER BY n DESC, error`,
    [companyId, batchId]);
  // fully-voided entries: entry_keys with no 'valid' rows but at least one
  // 'void' row — the batch will create no journal entry for these.
  const fullyVoid = await appClient.query<{ n: string }>(
    `SELECT count(*) AS n FROM (
       SELECT entry_key FROM staging_journal
        WHERE company_id = $1 AND batch_id = $2
        GROUP BY entry_key
       HAVING count(*) FILTER (WHERE status = 'valid') = 0
          AND count(*) FILTER (WHERE status = 'void') > 0
     ) fully_void_entries`,
    [companyId, batchId]);

  return {
    year, batchId, isNew, status,
    rowCount: Number(totals.rows[0]!.rows),
    entryCount: Number(totals.rows[0]!.entries),
    errorCount: Number(totals.rows[0]!.errors),
    errorGroups: groups.rows.map((r) => ({ error: r.error, count: Number(r.n) })),
    voidCount: Number(totals.rows[0]!.voids),
    fullyVoidEntryCount: Number(fullyVoid.rows[0]!.n),
  };
}

/**
 * Reconstructs the per-year report (YearResult[]) for batches ALREADY
 * loaded for this company, by inspecting import_batches/staging_journal
 * directly — no re-parsing of any file. Needed because the server's
 * import routes are stateless across requests (server/src/imports.ts):
 * the journal-upload endpoint loads+validates and returns a report, then a
 * SEPARATE /import/post request has to rebuild that same report from the
 * database to know which years are clean before posting. The CLI doesn't
 * need this — one process holds the YearResult[] in memory across load and
 * --post in the same run.
 *
 * Batches don't have a `year` column (see 0003_import_layer.sql); loadYear()
 * names each batch's filename `${basename}#${year}`, so the year is
 * recovered from the text after the LAST '#'. Only batches of the given
 * `source` (default 'quickbooks_journal') are considered, and rolled-back
 * batches are excluded (a rolled
 * back year is treated as not-loaded — nothing to post). If more than one
 * batch maps to the same year (re-uploaded under a different filename), the
 * most recently created one wins.
 *
 * Manages its own connection (see the CONNECTION CONTRACT note at the top
 * of this file) — NEVER call this from inside db.withCompany() or any other
 * open transaction; pass a Pool, not a client.
 */
export async function loadExistingYearResults(
  pool: Pool, companyId: string, years?: number[],
  source: string = QUICKBOOKS_JOURNAL_SOURCE,
): Promise<YearResult[]> {
  return withSessionCompany(pool, companyId, async (client) => {
    const batchesRes = await client.query<{ id: string; filename: string }>(
      `SELECT id, filename FROM import_batches
        WHERE company_id = $1 AND source = $2 AND status <> 'rolled_back'
        ORDER BY created_at ASC`,
      [companyId, source],
    );

    const latestBatchForYear = new Map<number, string>();
    for (const row of batchesRes.rows) {
      const idx = row.filename.lastIndexOf("#");
      if (idx === -1) continue;
      const year = Number(row.filename.slice(idx + 1));
      if (!Number.isInteger(year)) continue;
      latestBatchForYear.set(year, row.id); // rows ordered ASC — last write wins
    }

    const targetYears = years ?? [...latestBatchForYear.keys()].sort((a, b) => a - b);
    const results: YearResult[] = [];
    for (const year of targetYears) {
      const batchId = latestBatchForYear.get(year);
      if (batchId === undefined) {
        throw new Error(
          `no loaded journal batch found for year ${year} — upload and validate the journal first`,
        );
      }
      results.push(await reportBatch(client, companyId, year, Number(batchId), false));
    }
    return results;
  });
}

export interface LoadJournalYearsParams {
  companyId: string;
  ownerId: string;
  /** basename of the source file — batches are named `${filename}#${year}`. */
  filename: string;
  entriesByYear: Map<number, JournalEntry[]>;
  /** Restrict to specific years; defaults to every year present, ascending. */
  years?: number[];
  /** Batch source string; defaults to the QuickBooks Journal pipeline's. */
  source?: string;
  /** Per-line raw jsonb provider; defaults to the QB report reconstruction. */
  rawForLine?: RawRowProvider;
  /** Overrides the basename used in the per-year batch sha (NOT the batch
   *  filename). Pass a constant to make batch identity depend on year +
   *  entry content only — see the note inside loadYear(). */
  shaBasename?: string;
}

/**
 * Loads (or re-validates an already-loaded) batch for each requested year,
 * through the existing hardened import pipeline (resolve_batch/validate_batch),
 * and returns a structured per-year report.
 *
 * Manages its own connection per year (see the CONNECTION CONTRACT note at
 * the top of this file) — NEVER call this from inside db.withCompany() or
 * any other open transaction; pass a Pool, not a client.
 */
export async function loadJournalYears(
  pool: Pool, params: LoadJournalYearsParams,
): Promise<YearResult[]> {
  const years = params.years ?? [...params.entriesByYear.keys()].sort((a, b) => a - b);
  const results: YearResult[] = [];

  for (const year of years) {
    const entriesForYear = params.entriesByYear.get(year);
    if (!entriesForYear) {
      throw new Error(`no entries found for year ${year} in "${params.filename}"`);
    }

    const result = await withSessionCompany(pool, params.companyId, async (client) => {
      const { batchId, isNew } = await loadYear(
        client, params.companyId, params.ownerId, params.filename, year, entriesForYear,
        params.source ?? QUICKBOOKS_JOURNAL_SOURCE, params.rawForLine, params.shaBasename);

      if (isNew) {
        await client.query("SELECT resolve_batch($1, $2)", [params.companyId, batchId]);
        await client.query("SELECT validate_batch($1, $2)", [params.companyId, batchId]);
      } else {
        // A pre-existing batch from an earlier run: only 'loaded'/'resolved'
        // (died mid-pipeline) or 'validated'/'failed' (completed, but the
        // chart may have changed since) are worth re-running. 'posted'/
        // 'rolled_back' batches are left alone — the pipeline functions
        // reject them anyway.
        const status = await getBatchStatus(client, params.companyId, batchId);
        if (shouldRevalidate(status)) {
          await client.query("SELECT resolve_batch($1, $2)", [params.companyId, batchId]);
          await client.query("SELECT validate_batch($1, $2)", [params.companyId, batchId]);
        }
      }

      return reportBatch(client, params.companyId, year, batchId, isNew);
    });

    results.push(result);
  }

  return results;
}

export interface PostYearResult {
  year: number;
  batchId: number;
  /** null when alreadyPosted is true, or when posting this year failed. */
  posted: number | null;
  alreadyPosted: boolean;
  reconciliation: Record<string, unknown> | null;
  error?: string;
  /** The original thrown error (a pg error object, usually) — kept so API
   *  callers can run it through their own English mapping (mapPgError)
   *  instead of surfacing the raw message. Never serialized directly. */
  errorCause?: unknown;
}

export interface PostYearsResult {
  ok: boolean;
  /** Years with validation errors — posting refuses to run at all when non-empty. */
  dirtyYears: number[];
  years: PostYearResult[];
}

/**
 * Posts every result's batch (via post_batch) unless ANY targeted year still
 * has validation errors, in which case nothing is posted at all — mirrors
 * the original CLI's "refuse to post" behavior. Stops at the first year
 * whose post_batch call itself throws, returning what succeeded so far.
 *
 * Manages its own connection per year (see the CONNECTION CONTRACT note at
 * the top of this file) — NEVER call this from inside db.withCompany() or
 * any other open transaction; pass a Pool, not a client.
 */
export async function postYears(
  pool: Pool, companyId: string, ownerId: string, results: YearResult[],
): Promise<PostYearsResult> {
  const dirtyYears = results.filter((r) => r.errorCount > 0).map((r) => r.year);
  if (dirtyYears.length > 0) {
    return { ok: false, dirtyYears, years: [] };
  }

  const years: PostYearResult[] = [];
  for (const result of results) {
    if (result.status === "posted") {
      years.push({
        year: result.year, batchId: result.batchId,
        posted: null, alreadyPosted: true, reconciliation: null,
      });
      continue;
    }
    try {
      const yearResult = await withSessionCompany(pool, companyId, async (client) => {
        const posted = await client.query<{ n: string }>(
          "SELECT post_batch($1, $2, $3) AS n", [companyId, result.batchId, ownerId]);
        const rec = await client.query(
          "SELECT * FROM batch_reconciliation($1, $2)", [companyId, result.batchId]);
        return {
          year: result.year, batchId: result.batchId,
          posted: Number(posted.rows[0]!.n), alreadyPosted: false,
          reconciliation: rec.rows[0] ?? null,
        };
      });
      years.push(yearResult);
    } catch (err) {
      years.push({
        year: result.year, batchId: result.batchId,
        posted: null, alreadyPosted: false, reconciliation: null,
        error: (err as Error).message,
        errorCause: err,
      });
      return { ok: false, dirtyYears: [], years };
    }
  }
  return { ok: true, dirtyYears: [], years };
}

export function printYearReport(result: YearResult): void {
  const label = result.isNew
    ? `year ${result.year}: batch ${result.batchId}`
    : `year ${result.year}: already loaded (batch ${result.batchId}, status ${result.status})`;
  console.log(
    `${label} — ${result.entryCount} entries, ${result.rowCount} rows, ` +
      `${result.errorCount} errors`,
  );
  for (const g of result.errorGroups) {
    console.log(`  [${g.count}x] ${g.error}`);
  }
  if (result.voidCount > 0) {
    console.log(
      `  ${result.voidCount} zero-amount lines skipped (void); ` +
        `${result.fullyVoidEntryCount} fully-voided entries not posted`,
    );
  }
}

