import sqlite3 from "sqlite3";

const oldDb = new sqlite3.Database("old.db");
const newDb = new sqlite3.Database("new.db");

async function migrateData() {
  try {
    const oldData = await new Promise((resolve, reject) => {
      oldDb.all(
        `
        SELECT
          p.Name AS PersonName,
          p.NormalizedName AS PersonNormalizedName,
          s.ID AS SeriesId,
          p.Role
        FROM Person AS p
        JOIN PersonSeriesMetadata AS psm
          ON p.Id = psm.PeopleId
        JOIN Series AS s
          ON psm.SeriesMetadatasId = s.ID
      `,
        (err, rows) => {
          if (err) reject(err);
          resolve(rows);
        },
      );
    });

    await new Promise((resolve, reject) => {
      newDb.serialize(() => {
        newDb.run("BEGIN TRANSACTION");

        const insertPersonStmt = newDb.prepare(`
          INSERT OR IGNORE INTO Person (Name, NormalizedName)
          VALUES (?, ?)
        `);

        const selectPersonIdStmt = newDb.prepare(`
          SELECT Id FROM Person WHERE Name = ? AND NormalizedName = ?;
        `);

        const insertSeriesMetadataPeopleStmt = newDb.prepare(`
          INSERT INTO SeriesMetadataPeople (SeriesMetadataId, PersonId, Role)
          VALUES (?, ?, ?)
        `);

        let remaining = 0;

        for (const row of oldData) {
          ++remaining;

          const updateSeries = (Id) => {
            insertSeriesMetadataPeopleStmt.run(
              row.SeriesId,
              Id,
              row.Role,
              () => {
                --remaining;

                if (!remaining) {
                  insertPersonStmt.finalize();
                  insertSeriesMetadataPeopleStmt.finalize();
                  selectPersonIdStmt.finalize();
                }
              },
            );
          };

          selectPersonIdStmt.get(
            row.PersonName,
            row.PersonNormalizedName,
            (err, personRow) => {
              if (personRow) updateSeries(personRow.Id);
              else {
                insertPersonStmt.run(
                  row.PersonName,
                  row.PersonNormalizedName,
                  function () {
                    updateSeries(this.lastId);
                  },
                );
              }
            },
          );
        }

        newDb.run("COMMIT", (err) => {
          if (err) reject(err);
          resolve();
        });
      });
    });

    console.log("Data migration successful!");
  } catch (error) {
    newDb.run("ROLLBACK");
    console.error("Error migrating data:", error);
  } finally {
    oldDb.close();
    newDb.close();
  }
}

migrateData();