// backfill-subtypes-from-qb — one-time helper for charts loaded from a
// QuickBooks Account Listing BEFORE subtypes existed (migration 0016).
// Reads the same Account Listing xlsx the chart loader used, maps each row's
// QB Type to a subtype (tools/chart-logic.ts's QB_TYPE_SUBTYPE), and UPDATEs
// accounts.subtype ONLY — no other column, no ledger data, and never a row
// whose subtype is already set (a manual tag in the chart screen always
// wins over a re-run of this script).
//
// Accounts are matched the way the chart/journal loaders match them: by
// exact full name first ("Parent:Leaf" paths included), then by
// account_aliases (the leaf aliases chart:load created). Anything unmatched
// is reported, never guessed. --dry-run runs the full transaction and rolls
// it back, so the printed counts are exactly what a real run would do.
//
// Usage:
//   npx tsx tools/backfill-subtypes-from-qb.ts \
//     --company "Blue Heron Bakery" \
//     --file "imports/account-listing.xlsx" [--dry-run]

import { parseArgs } from "node:util";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { Client } from "pg";
import "dotenv/config";
import { resolveDatabaseUrls } from "../server/src/db.js";
import { readQbSheet } from "./qb-xlsx.js";
import {
  parseListingRows,
  qbTypeToSubtype,
  type ListingRow,
  type AccountSubtype,
} from "./chart-logic.js";

// Resolved when needed, not at import — same reasoning as tools/load-chart.ts:
// the old hardcoded localhost:5432 default now names a developer's own
// PostgreSQL rather than ours, and this tool rewrites account subtypes.
async function databaseUrl(): Promise<string> {
  return (await resolveDatabaseUrls()).admin;
}

interface Args {
  file: string;
  company: string;
  dryRun: boolean;
}

function parseCliArgs(argv: string[]): Args {
  const { values } = parseArgs({
    args: argv,
    options: {
      file: { type: "string" },
      company: { type: "string" },
      "dry-run": { type: "boolean", default: false },
    },
  });
  if (!values.file || !values.company) {
    throw new Error(
      "usage: backfill-subtypes-from-qb --company <name> --file <xlsx> [--dry-run]",
    );
  }
  return { file: values.file, company: values.company, dryRun: values["dry-run"]! };
}

// ---------------------------------------------------------------------------
// pure planning — unit-tested against a fictional fixture xlsx
// (tests/tools/backfill-subtypes.test.ts)
// ---------------------------------------------------------------------------

export interface SubtypeTag {
  name: string;
  qbType: string;
  subtype: AccountSubtype;
}

export interface SubtypePlan {
  /** Rows whose QB Type maps to a subtype — the candidate UPDATEs. */
  tags: SubtypeTag[];
  /** Rows whose QB Type is unrecognized — reported, never guessed. */
  unknownTypes: Array<{ name: string; qbType: string }>;
}

/** Maps every listing row's QB Type to a subtype (or reports it unknown). */
export function planSubtypeTags(rows: ListingRow[]): SubtypePlan {
  const tags: SubtypeTag[] = [];
  const unknownTypes: Array<{ name: string; qbType: string }> = [];
  for (const row of rows) {
    const subtype = qbTypeToSubtype(row.qbType);
    if (subtype === null) {
      unknownTypes.push({ name: row.name, qbType: row.qbType });
    } else {
      tags.push({ name: row.name, qbType: row.qbType, subtype });
    }
  }
  return { tags, unknownTypes };
}

// ---------------------------------------------------------------------------
// DB flow
// ---------------------------------------------------------------------------

export interface ApplyResult {
  updatedBySubtype: Map<AccountSubtype, number>;
  updatedTotal: number;
  alreadyTagged: number;
  unmatched: SubtypeTag[];
}

/** Exported for tests (tests/tools/backfill-subtypes.test.ts) — the caller
 *  owns the transaction, which is how run() implements --dry-run (BEGIN,
 *  applyTags, ROLLBACK). */
export async function applyTags(
  client: Pick<Client, "query">, companyId: string, tags: SubtypeTag[],
): Promise<ApplyResult> {
  const accountsRes = await client.query<{ id: string; name: string; subtype: string | null }>(
    "SELECT id, name, subtype FROM accounts WHERE company_id = $1", [companyId]);
  const byName = new Map(accountsRes.rows.map((r) => [r.name, r]));

  const aliasRes = await client.query<{ alias: string; account_id: string }>(
    "SELECT alias, account_id FROM account_aliases WHERE company_id = $1", [companyId]);
  const idToAccount = new Map(accountsRes.rows.map((r) => [r.id, r]));
  const byAlias = new Map(
    aliasRes.rows
      .map((r) => [r.alias, idToAccount.get(r.account_id)] as const)
      .filter((pair): pair is [string, { id: string; name: string; subtype: string | null }] =>
        pair[1] !== undefined),
  );

  const updatedBySubtype = new Map<AccountSubtype, number>();
  let updatedTotal = 0;
  let alreadyTagged = 0;
  const unmatched: SubtypeTag[] = [];

  for (const tag of tags) {
    const account = byName.get(tag.name) ?? byAlias.get(tag.name);
    if (!account) {
      unmatched.push(tag);
      continue;
    }
    if (account.subtype !== null) {
      alreadyTagged++;
      continue;
    }
    // subtype ONLY, and only where still NULL — the WHERE re-checks both so
    // a concurrent tag can never be overwritten. The type/subtype CHECK
    // matrix (0016) refuses a subtype that doesn't fit the account's type.
    const r = await client.query(
      `UPDATE accounts SET subtype = $3
        WHERE company_id = $1 AND id = $2 AND subtype IS NULL`,
      [companyId, account.id, tag.subtype]);
    if ((r.rowCount ?? 0) > 0) {
      updatedTotal++;
      updatedBySubtype.set(tag.subtype, (updatedBySubtype.get(tag.subtype) ?? 0) + 1);
      account.subtype = tag.subtype; // keep the in-memory view honest
    } else {
      alreadyTagged++;
    }
  }

  return { updatedBySubtype, updatedTotal, alreadyTagged, unmatched };
}

export async function run(argv: string[]): Promise<number> {
  const args = parseCliArgs(argv);

  // Read and parse the file BEFORE touching the database.
  const sheetRows = await readQbSheet(args.file);
  const listingRows = parseListingRows(sheetRows);
  if (listingRows.length === 0) {
    console.error(`empty listing: no account rows found in "${args.file}"`);
    return 1;
  }

  const plan = planSubtypeTags(listingRows);

  const client = new Client({ connectionString: await databaseUrl() });
  await client.connect();
  try {
    const companyRes = await client.query<{ id: string }>(
      "SELECT id FROM companies WHERE name = $1", [args.company]);
    if (companyRes.rowCount === 0) {
      console.error(`no company named "${args.company}"`);
      return 1;
    }
    if (companyRes.rowCount! > 1) {
      console.error(`ambiguous company name "${args.company}": ${companyRes.rowCount} matches`);
      return 1;
    }
    const companyId = companyRes.rows[0]!.id;

    await client.query("BEGIN");
    const result = await applyTags(client, companyId, plan.tags);
    if (args.dryRun) {
      await client.query("ROLLBACK");
    } else {
      await client.query("COMMIT");
    }

    const mode = args.dryRun ? "DRY RUN — rolled back, nothing saved" : "applied";
    console.log(`company "${args.company}" (${companyId}) — ${mode}`);
    console.log(
      `listing: ${listingRows.length} rows, ${plan.tags.length} with a mappable QB type, ` +
        `${plan.unknownTypes.length} unrecognized`,
    );
    console.log(`updated: ${result.updatedTotal} account(s) tagged`);
    for (const [subtype, n] of [...result.updatedBySubtype.entries()].sort()) {
      console.log(`  ${subtype}: ${n}`);
    }
    console.log(`already tagged (left untouched): ${result.alreadyTagged}`);
    if (result.unmatched.length > 0) {
      console.log(`unmatched (no account with this name or alias — NOT tagged):`);
      for (const tag of result.unmatched) {
        console.log(`  "${tag.name}" (QB type "${tag.qbType}")`);
      }
    }
    if (plan.unknownTypes.length > 0) {
      console.log(`unrecognized QB types (left untagged):`);
      for (const row of plan.unknownTypes) {
        console.log(`  "${row.name}": "${row.qbType}"`);
      }
    }
    return 0;
  } catch (err) {
    await client.query("ROLLBACK").catch(() => {});
    throw err;
  } finally {
    await client.end();
  }
}

const isMain = process.argv[1] &&
  path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);
if (isMain) {
  run(process.argv.slice(2))
    .then((code) => { process.exitCode = code; })
    .catch((err) => { console.error(err); process.exitCode = 1; });
}
