/// <reference lib="deno.ns" />
// Answers "did SimpleFIN send it, or did we drop it?" from the raw archive.
//
// Usage:
//   deno run -A scripts/diagnose-sync.ts [path-to-quantum.db]
//   deno run -A scripts/diagnose-sync.ts [path] --dump <account-id-or-name-substring>
//     prints the first few VERBATIM transaction objects the Bridge sent for
//     matching accounts (shows whether `extra` carries payee/memo detail).
//
// Against a deployed container, copy the file out first:
//   podman cp <container>:/data/quantum.db ./quantum.db
//   deno run -A scripts/diagnose-sync.ts ./quantum.db

import { DatabaseSync } from "node:sqlite";

const args = [...Deno.args];
const dumpIndex = args.indexOf("--dump");
const dumpFilter = dumpIndex >= 0
	? (args.splice(dumpIndex, 2)[1] ?? "").toLowerCase()
	: null;
const path = args[0] ?? "./data/quantum.db";
const db = new DatabaseSync(path);

const fmtDate = (unix: number | null) =>
	unix ? new Date(unix * 1000).toISOString().slice(0, 10) : "—";

console.log(`\n=== Sync attempts (latest 5) ===`);
const syncs = db
	.prepare(
		`SELECT id, fetched_at, ok, error, LENGTH(payload) AS bytes
		 FROM raw_syncs ORDER BY id DESC LIMIT 5`,
	)
	.all() as Record<string, unknown>[];
for (const s of syncs) {
	console.log(
		`#${s.id}  ${s.fetched_at}  ${s.ok ? "ok" : "FAILED"}  ${
			s.bytes ?? 0
		} bytes${s.error ? `  error: ${s.error}` : ""}`,
	);
}

const latest = db
	.prepare(
		"SELECT id, fetched_at, payload FROM raw_syncs WHERE ok = 1 ORDER BY id DESC LIMIT 1",
	)
	.get() as { id: number; fetched_at: string; payload: string } | undefined;

if (!latest?.payload) {
	console.log(
		"\nNo successful sync payload archived yet — nothing to compare.",
	);
	Deno.exit(0);
}

console.log(
	`\n=== Latest successful payload (raw_syncs #${latest.id}, ${latest.fetched_at}) ===`,
);
const parsed = JSON.parse(latest.payload) as {
	errors?: unknown[];
	accounts?: Record<string, unknown>[];
};

if (parsed.errors?.length) {
	console.log(`Connection errors reported by the Bridge:`);
	for (const e of parsed.errors) console.log(`  ! ${e}`);
} else {
	console.log("No connection errors in payload.");
}

console.log("\nWhat the Bridge sent, per account:");
for (const account of parsed.accounts ?? []) {
	const txns = (account.transactions ?? []) as Record<string, unknown>[];
	const posted = txns
		.map((t) => (typeof t.posted === "number" ? t.posted : null))
		.filter((p): p is number => p != null && p > 0);
	const range = posted.length
		? `${fmtDate(Math.min(...posted))} … ${fmtDate(Math.max(...posted))}`
		: "no posted dates";
	console.log(
		`  ${String(account.id).padEnd(28)} ${String(account.name).padEnd(24)} ` +
			`${String(txns.length).padStart(4)} txns in payload  (${range})`,
	);
}

console.log("\nWhat Quantum has normalized, per account:");
const rows = db
	.prepare(
		`SELECT a.id, COALESCE(a.display_name, a.name) AS name, a.state,
		        COUNT(t.id) AS n, MIN(t.posted) AS min_posted, MAX(t.posted) AS max_posted
		 FROM accounts a LEFT JOIN transactions t ON t.account_id = a.id AND t.removed_at IS NULL
		 GROUP BY a.id ORDER BY a.id`,
	)
	.all() as Record<string, unknown>[];
for (const r of rows) {
	console.log(
		`  ${String(r.id).padEnd(28)} ${String(r.name).padEnd(24)} ` +
			`${String(r.n).padStart(4)} txns in db       ` +
			`(${fmtDate(r.min_posted as number | null)} … ${
				fmtDate(r.max_posted as number | null)
			})  [${r.state}]`,
	);
}

if (dumpFilter) {
	console.log(
		`\n=== Verbatim transactions for accounts matching "${dumpFilter}" ===`,
	);
	for (const account of parsed.accounts ?? []) {
		const label = `${account.id} ${account.name}`.toLowerCase();
		if (!label.includes(dumpFilter)) continue;
		console.log(`\n--- ${account.name} (${account.id}) ---`);
		const txns = (account.transactions ?? []) as Record<string, unknown>[];
		for (const txn of txns.slice(0, 5)) {
			console.log(JSON.stringify(txn, null, 2));
		}
		if (txns.length > 5) console.log(`… and ${txns.length - 5} more`);
	}
}

console.log(`\nReading the result:
  - Account has txns in payload but 0 in db  -> Quantum bug; please report.
  - Account has 0 txns in payload            -> the provider isn't delivering
    transactions for that institution (common for investment/savings accounts
    via some aggregators). Check the connection at SimpleFIN Bridge.
  - FAILED sync rows with a normalization error -> a malformed amount or
    payload; the error text says which.`);
db.close();