import { readFile } from "node:fs/promises";
import path from "node:path";
import process from "node:process";
import { createInterface } from "node:readline";
import pg from "pg";

const { Client } = pg;
const EXPECTED_DATABASE = "booking_test";
const DEFAULT_BATCH = path.resolve(
  process.cwd(),
  "../.planning/data_import_audit/import_batch_2026_07_31"
);

async function readStandardInput() {
  const lines = createInterface({ input: process.stdin, terminal: false });
  for await (const line of lines) {
    lines.close();
    return line.trim();
  }
  throw new Error("Missing connection input");
}

async function readBatch(batchDirectory) {
  const read = async name => JSON.parse(
    await readFile(path.join(batchDirectory, name), "utf8")
  );
  return {
    manifest: await read("manifest.json"),
    owners: await read("owners.json"),
    usage: await read("usage_legacy.json")
  };
}

function same(left, right) {
  return JSON.stringify(left) === JSON.stringify(right);
}

const batchDirectory = path.resolve(process.argv[2] ?? DEFAULT_BATCH);
let client;
let transactionStarted = false;

try {
  const batch = await readBatch(batchDirectory);
  const input = JSON.parse(await readStandardInput());
  const importBatch = `legacy-${batch.manifest.source_sha256.slice(0, 16)}`;
  client = new Client({
    host: input.host,
    port: input.port,
    user: input.user,
    password: input.password,
    database: input.database,
    ssl: input.ssl ? { rejectUnauthorized: false } : undefined,
    application_name: "condon_import_readonly_verification",
    connectionTimeoutMillis: 10_000,
    query_timeout: 30_000,
    options: "-c default_transaction_read_only=on -c statement_timeout=30000 -c lock_timeout=2000"
  });
  await client.connect();
  await client.query("BEGIN TRANSACTION READ ONLY");
  transactionStarted = true;

  const identity = (await client.query(`
    SELECT current_database() AS database_name, current_user AS role_name
  `)).rows[0];
  const ownerRows = (await client.query(`
    SELECT account_no, transfer_date::text, owner_name, room_no,
           purchased_room_type_code, unit_no, member_no
    FROM condon.owner_accounts
    ORDER BY room_no
  `)).rows;
  const usageRows = (await client.query(`
    SELECT
      ur.source_sheet,
      ur.source_row,
      oa.room_no AS owner_room_no,
      ur.confirmation_no,
      ur.check_in::text,
      ur.check_out::text,
      ur.night_count,
      ur.room_count,
      ur.balance_before,
      ur.use_nights,
      ur.balance_after,
      ur.raw_used_room_type,
      ur.used_room_type_code,
      ur.remark,
      ur.applied_multiplier,
      ur.rule_version,
      ur.import_batch
    FROM condon.usage_records ur
    JOIN condon.owner_accounts oa ON oa.id = ur.owner_account_id
    WHERE ur.import_batch = $1
    ORDER BY ur.source_sheet, ur.source_row
  `, [importBatch])).rows;
  const counts = (await client.query(`
    SELECT
      (SELECT count(*)::integer FROM condon.owner_accounts) AS owners,
      (SELECT count(*)::integer FROM condon.entitlement_periods) AS periods,
      (SELECT count(*)::integer FROM condon.bookings) AS bookings,
      (SELECT count(*)::integer FROM condon.usage_records) AS usage,
      (SELECT count(*)::integer FROM condon.entitlement_ledger) AS ledger,
      (SELECT coalesce(sum(use_nights),0)::integer FROM condon.usage_records) AS use_sum,
      (SELECT count(*)::integer FROM condon.usage_records WHERE rule_version='legacy-source' AND applied_multiplier IS NULL) AS legacy_count
  `)).rows[0];
  const carry = (await client.query(`
    SELECT ep.period_year, ep.annual_grant, ep.carry_forward, ep.current_balance
    FROM condon.entitlement_periods ep
    JOIN condon.owner_accounts oa ON oa.id=ep.owner_account_id
    WHERE oa.room_no='4311'
    ORDER BY ep.period_year
  `)).rows;

  const ownerMap = new Map(ownerRows.map(row => [row.room_no, row]));
  const expectedOwnerMap = new Map(batch.owners.map(owner => [owner.room_no, owner]));
  const ownerMismatches = [];
  for (const owner of batch.owners) {
    const actual = ownerMap.get(owner.room_no);
    const expected = {
      account_no: owner.account_no,
      transfer_date: owner.transfer_date,
      owner_name: owner.name,
      room_no: owner.room_no,
      purchased_room_type_code: owner.purchased_room_type_code,
      unit_no: owner.unit_no,
      member_no: owner.member_no
    };
    if (!actual || !same(actual, expected)) {
      ownerMismatches.push({ room_no: owner.room_no, expected, actual: actual ?? null });
    }
  }

  const expectedUsageMap = new Map(
    batch.usage.map(row => [`${row.source_sheet}!${row.source_row}`, row])
  );
  const actualUsageMap = new Map(
    usageRows.map(row => [`${row.source_sheet}!${row.source_row}`, row])
  );
  const usageMismatches = [];
  for (const expected of batch.usage) {
    const key = `${expected.source_sheet}!${expected.source_row}`;
    const actual = actualUsageMap.get(key);
    const normalizedExpected = {
      source_sheet: expected.source_sheet,
      source_row: expected.source_row,
      owner_room_no: expected.owner_room_no,
      confirmation_no: expected.confirmation_no,
      check_in: expected.check_in,
      check_out: expected.check_out,
      night_count: expected.night,
      room_count: expected.room,
      balance_before: expected.total,
      use_nights: expected.use,
      balance_after: expected.balance,
      raw_used_room_type: expected.raw_used_room_type,
      used_room_type_code: expected.canonical_used_room_type_code,
      remark: expected.remark,
      applied_multiplier: null,
      rule_version: "legacy-source",
      import_batch: importBatch
    };
    if (!actual || !same(actual, normalizedExpected)) {
      usageMismatches.push({ key, expected: normalizedExpected, actual: actual ?? null });
    }
  }

  const checks = {
    databaseVerified: identity.database_name === EXPECTED_DATABASE
      && identity.role_name === input.user,
    counts: Number(counts.owners) === batch.manifest.counts.owner_count
      && Number(counts.periods) === 389
      && Number(counts.bookings) === 155
      && Number(counts.usage) === batch.manifest.counts.accepted_usage_count
      && Number(counts.ledger) === 547
      && Number(counts.use_sum) === batch.manifest.counts.usage_use_sum,
    ownersMatchBatch: ownerRows.length === expectedOwnerMap.size && ownerMismatches.length === 0,
    usagesMatchBatch: usageRows.length === batch.usage.length && usageMismatches.length === 0,
    legacyValuesPreserved: Number(counts.legacy_count) === batch.usage.length,
    carryForward4311: same(carry, [
      {
        period_year: 2025,
        annual_grant: 15,
        carry_forward: 0,
        current_balance: 12
      },
      {
        period_year: 2026,
        annual_grant: 15,
        carry_forward: 12,
        current_balance: 27
      }
    ])
  };

  process.stdout.write(`${JSON.stringify({
    ok: Object.values(checks).every(Boolean),
    batchDirectory,
    importBatch,
    checks,
    counts,
    ownerMismatchCount: ownerMismatches.length,
    usageMismatchCount: usageMismatches.length,
    ownerMismatches: ownerMismatches.slice(0, 5),
    usageMismatches: usageMismatches.slice(0, 5),
    carry4311: carry
  }, null, 2)}\n`);
  if (Object.values(checks).some(value => value === false)) process.exitCode = 1;
} catch (error) {
  process.stdout.write(`${JSON.stringify({
    ok: false,
    errorCode: typeof error?.code === "string" ? error.code : "VERIFY_IMPORT_FAILED",
    errorMessage: typeof error?.message === "string" ? error.message : "Import verification failed"
  }, null, 2)}\n`);
  process.exitCode = 1;
} finally {
  if (client) {
    if (transactionStarted) await client.query("ROLLBACK").catch(() => undefined);
    await client.end().catch(() => { process.exitCode = 1; });
  }
}