main.ts

import knex from "knex";
import fs from "node:fs/promises";
import { insertTrackAndGet, insertTransitionTypeAndGet } from "./lib.js";

const db = knex({
	client: "better-sqlite3",
	connection: {
		filename: "./mydb.sqlite",
	},
	useNullAsDefault: true,
});

try {
	// const tables = await db("sqlite_master")
	//   .where("type", "table")
	//   .filter((t) => t.name !== "sqlite_sequence");

	// await Promise.all(
	//   tables.map(tableName => db.schema.table(tableName, table => {
	//     table.foreign
	//   })
	// )

	// await Promise.all(
	//   tables
	//     .filter((t) => t.name !== "sqlite_sequence")
	//     .map((table) => {
	//       return db.schema.dropTable(table.name);
	//     })
	// );

	// await db.schema
	//   .createTable("track", (table) => {
	//     table.increments();
	//     table.string("name").unique();
	//   })
	//   .createTable("transition_type", (table) => {
	//     table.increments();
	//     table.string("name").unique();
	//   })
	//   .createTable("transition", (table) => {
	//     table.increments();
	//     table.integer("track_a_id").references("track.id");
	//     table.string("type_id").references("transition_type.id");
	//     table.integer("track_b_id").references("track.id");
	//     table.unique(["track_a_id", "type_id", "track_b_id"]);
	//     table.string("note");
	//   });

	await db("transition").del();
	await Promise.all([db("transition_type").del(), db("track").del()]);

	const csvString = await fs.readFile("./import.csv", "utf-8");
	const [headerString, ...rows] = csvString.split("\n");

	const header = headerString.split(",").map((s) => s.trim());

	const data = rows.map((r) =>
		r.split(",").reduce<Record<string, string>>(
			(prev, cur, i) => ({
				...prev,
				[header[i]]: cur,
			}),
			{}
		)
	);

	for (const transition of data.slice(2)) {
		const [track_a, track_b, transition_type] = await Promise.all([
			insertTrackAndGet(db, transition.track_a),
			insertTrackAndGet(db, transition.track_b),
			insertTransitionTypeAndGet(db, transition.type),
		]);

		console.log(track_a, track_b, transition_type);

		await db("transition").insert({
			track_a_id: track_a.id,
			type_id: transition_type.id,
			track_b_id: track_b.id,
			note: transition.Note,
		});
	}
} catch (e) {
	console.error(e);
} finally {
	db.destroy();
}