import { Client } from "pg";
import { expect, inject } from "vitest";
import {
  createTestDatabaseFromTemplate,
  dbUrl,
  dropTestDatabase,
  readFixture,
} from "./test-db-admin.js";
import "dotenv/config";

export interface TestDb {
  dbName: string;
  companyA: string;
  companyB: string;
  userA: string;
  userB: string;
  /** account ids by code, per company */
  accounts: Record<string, { A: number; B: number }>;
  /** superuser connection to the test db (setup/verification only) */
  admin: Client;
  /** app-role connection with app.company_id set (RLS enforced) */
  appClient(companyId: string | null): Promise<Client>;
  /**
   * Superuser connection with app.company_id set. Stands in for a SECURITY
   * DEFINER function calling an INTERNAL routine the app role is deliberately
   * not granted EXECUTE on (record_movement, allocate_*). Session-company
   * guards still apply — this is about EXECUTE privilege, not about tenancy.
   */
  sysClient(companyId: string | null): Promise<Client>;
  close(): Promise<void>;
}

/**
 * Name of this run's migrated+seeded template database, published by
 * tests/helpers/global-setup.ts. Resolved lazily and cached: `inject` is only
 * meaningful once a worker is running a test file.
 */
let cachedTemplate: string | null = null;
function templateDb(): string {
  if (cachedTemplate) return cachedTemplate;
  let name = process.env.ACCOUNTING_TEST_TEMPLATE_DB ?? "";
  if (!name) {
    try {
      name = inject("templateDb") ?? "";
    } catch {
      name = "";
    }
  }
  if (!name) {
    throw new Error(
      "no test template database — vitest globalSetup (tests/helpers/global-setup.ts) " +
      "did not run. Run tests through vitest.config.ts.",
    );
  }
  cachedTemplate = name;
  return name;
}

/**
 * A throwaway database for one test file, copied from the per-run template.
 *
 * The template already carries every migration and the standard fixture
 * (companies Alpha/Beta, users a@/b@, and the eight-account chart on each
 * company), so this is a file copy plus a couple of id lookups rather than a
 * full schema build. The returned shape is unchanged from when this function
 * did the migrating and seeding itself.
 */
/**
 * The test file asking for a database, stamped onto it so that if this worker
 * dies before its afterAll, globalSetup's teardown can name the file whose
 * results went missing. See DatabaseStamp in test-db-admin.ts — the whole point
 * is that "Worker exited unexpectedly" currently names nothing at all.
 *
 * Best effort by construction: `testPath` is only populated once a worker is
 * running a file, and a missing name must never be able to stop a test from
 * getting its database. Hence the try/catch and the undefined.
 */
function callingTestFile(): string | undefined {
  try {
    return expect.getState().testPath ?? undefined;
  } catch {
    return undefined;
  }
}

export async function createTestDb(): Promise<TestDb> {
  const dbName = await createTestDatabaseFromTemplate(templateDb(), callingTestFile());

  const adminUrl = await dbUrl(dbName);
  const admin = new Client({ connectionString: adminUrl });
  // A pg Client with no 'error' listener turns a server-side disconnect into
  // an unhandled error that kills the whole worker. Teardown deliberately
  // drops this database out from under any straggling connection, so every
  // client this helper owns swallows that event.
  admin.on("error", () => { /* connection torn down by teardown */ });
  const appClients: Client[] = [];

  async function destroy(): Promise<void> {
    for (const c of appClients) {
      try { await c.end(); } catch { /* already closed */ }
    }
    try { await admin.end(); } catch { /* best effort */ }
    // Leak-proof: WITH (FORCE) evicts anything still attached, and a retry
    // covers a connection that is mid-teardown. Never silently gives up.
    let lastErr: unknown;
    for (let attempt = 0; attempt < 3; attempt++) {
      try {
        await dropTestDatabase(dbName);
        return;
      } catch (err) {
        lastErr = err;
        await new Promise((r) => setTimeout(r, 200 * (attempt + 1)));
      }
    }
    throw lastErr;
  }

  try {
    await admin.connect();
    const { companyA, companyB, userA, userB, accounts } = await readFixture(admin);

    async function openClient(
      companyId: string | null, connectionString: string,
    ): Promise<Client> {
      const c = new Client({ connectionString });
      c.on("error", () => { /* connection torn down by teardown */ });
      await c.connect();
      appClients.push(c);
      if (companyId !== null) {
        await c.query("SELECT set_config('app.company_id', $1, false)", [companyId]);
      }
      return c;
    }

    async function appClient(companyId: string | null): Promise<Client> {
      return openClient(companyId, await dbUrl(dbName, "app", "app"));
    }

    async function sysClient(companyId: string | null): Promise<Client> {
      return openClient(companyId, adminUrl);
    }

    return {
      dbName, companyA, companyB, userA, userB, accounts, admin,
      appClient, sysClient, close: destroy,
    };
  } catch (err) {
    await destroy().catch(() => { /* the original error matters more */ });
    throw err;
  }
}

export interface EntrySpec {
  date?: string;
  memo?: string;
  lines: Array<{ accountId: number; debit?: string; credit?: string }>;
}

/** Insert a draft entry with its lines in one transaction; returns entry id. */
export async function insertDraftEntry(
  c: Client, companyId: string, userId: string, spec: EntrySpec,
): Promise<number> {
  await c.query("BEGIN");
  try {
    const er = await c.query(
      `INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
       VALUES ($1, $2, $3, $4) RETURNING id`,
      [companyId, spec.date ?? "2024-01-15", spec.memo ?? "test entry", userId]);
    const entryId: number = Number(er.rows[0].id);
    for (const l of spec.lines) {
      await c.query(
        `INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
         VALUES ($1, $2, $3, $4, $5)`,
        [companyId, entryId, l.accountId, l.debit ?? "0", l.credit ?? "0"]);
    }
    await c.query("COMMIT");
    return entryId;
  } catch (err) {
    await c.query("ROLLBACK").catch(() => {});
    throw err;
  }
}

/** Post a draft entry via the DB function; returns the assigned entry_no. */
export async function postEntry(
  c: Client, companyId: string, entryId: number,
): Promise<number> {
  const r = await c.query("SELECT post_entry($1, $2) AS entry_no", [companyId, entryId]);
  return Number(r.rows[0].entry_no);
}
