#!/usr/bin/env node

import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import mysql from "mysql2/promise";
import config from "../../src/config/dev.js";

const taskDir = path.dirname(fileURLToPath(import.meta.url));
const outputPath = path.join(taskDir, "research_db_snapshot_2026_fall.json");
const miaoguoBookIds = [13, 15, 17, 19, 21, 23, 24, 54, 56, 58, 60, 61, 62, 63, 64];
const hanziBookIds = [25, 27, 29, 31, 33, 35, 36, 74, 76, 78, 80, 81, 82, 83, 84];

const connection = await mysql.createConnection({
  ...config.database,
  charset: "utf8mb4",
});

try {
  const [books] = await connection.query(
    `SELECT ID, BookIDOld, BookName, UnitNum, WordNum
       FROM MiaoguoBook
      WHERE ID IN (?)
      ORDER BY ID`,
    [miaoguoBookIds],
  );
  const [targetRows] = await connection.query(
    `SELECT ID, HanziBookID, Sort, Category, Tag, Title, ImgUrl,
            Author, Dynasty, PeomContent, Translation, Flag
       FROM AncientPoetry
      WHERE HanziBookID IN (?)
      ORDER BY HanziBookID, Sort, ID`,
    [hanziBookIds],
  );
  const [allRows] = await connection.query(
    `SELECT ID, HanziBookID, Sort, Category, Tag, Title, Author, Dynasty,
            PeomContent, Translation, Flag
       FROM AncientPoetry
      ORDER BY ID`,
  );

  fs.writeFileSync(
    outputPath,
    `${JSON.stringify({ capturedAt: new Date().toISOString(), books, targetRows, allRows }, null, 2)}\n`,
    "utf8",
  );
  console.log(JSON.stringify({ outputPath, books: books.length, targetRows: targetRows.length, allRows: allRows.length }));
} finally {
  await connection.end();
}