220 lines
7.6 KiB
JavaScript
220 lines
7.6 KiB
JavaScript
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; });
|
|
}
|
|
}
|