thptqg2017/scripts/build-database.js

// Build DB from data/ (baotintuc.vn .xls source).
// Quirks: .xls has a 65,536-row-per-sheet limit. Hà Nội and HCM overflow into
// Sheet2, so we MUST iterate every sheet. Header row may or may not be
// present on each sheet.
import XLSX from "xlsx";
import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import {
  createDb,
  parseScores,
  isHeaderRow,
  buildRow,
} from "./build-lib.js";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const SRC_DIR = path.join(__dirname, "..", "data");
const DB_PATH = path.join(__dirname, "..", "public", "thptqg2017.db");

function collectFiles() {
  return fs
    .readdirSync(SRC_DIR)
    .filter((f) => f.endsWith(".xls") || f.endsWith(".xlsx"))
    .map((f) => path.join(SRC_DIR, f));
}

function main() {
  const { db, insert } = createDb(DB_PATH);

  const files = collectFiles();
  let sourceRows = 0;  // total data rows observed across ALL sheets (post-header)
  let skipped = 0;     // empty/invalid rows skipped
  let errors = 0;

  const run = db.transaction(() => {
    for (const file of files) {
      const base = path.basename(file);
      let fileRows = 0;
      const wb = XLSX.readFile(file);

      for (const sheetName of wb.SheetNames) {
        const rows = XLSX.utils.sheet_to_json(wb.Sheets[sheetName], {
          header: 1,
        });
        for (let i = 0; i < rows.length; i++) {
          if (i === 0 && isHeaderRow(rows[i])) continue;
          sourceRows++;
          const r = rows[i];
          const hoTen = String(r?.[0] || "").trim();
          const ngaySinh = String(r?.[1] || "").trim();
          const soBaoDanh = String(r?.[2] || "").trim();
          const diemThi = String(r?.[3] || "");
          if (!soBaoDanh || !hoTen) { skipped++; continue; }
          try {
            insert.run(buildRow({ hoTen, ngaySinh, soBaoDanh, scores: parseScores(diemThi) }));
            fileRows++;
          } catch (err) {
            errors++;
            if (errors <= 5) console.warn(`  [warn] ${base}: ${err.message}`);
          }
        }
      }
      console.log(`  ${base}: ${fileRows} rows`);
    }
  });

  console.log(`[build] data/ → ${DB_PATH}  (${files.length} files)`);
  run();
  db.exec("VACUUM");

  const dbCount = db.prepare("SELECT COUNT(*) c FROM student").get().c;
  const distinctSbd = sourceRows - skipped;
  console.log(`\nSource data rows (post-header):  ${sourceRows}`);
  console.log(`  skipped (empty/invalid):        ${skipped}`);
  console.log(`  insertable:                     ${distinctSbd}`);
  console.log(`  insert errors:                  ${errors}`);
  console.log(`DB rows (distinct SBD):           ${dbCount}`);
  // data/ comes from a single source, so every SBD should be unique after
  // header skip — no duplicates expected.
  if (dbCount !== distinctSbd - 0 && errors === 0) {
    const gap = distinctSbd - dbCount;
    if (gap === 0) console.log(`Audit: OK — every source row made it in.`);
    else console.log(`Audit: ${gap} row(s) collapsed (duplicate SBDs overwriting).`);
  }
  const sz = fs.statSync(DB_PATH).size;
  console.log(`Size: ${(sz / 1024 / 1024).toFixed(1)} MB`);
  db.close();
}

main();