Migrate Kavita v0.8.3 -> v0.8.4 People Metadata

###
### Python 3.8 requirement
### python migrate_data.py
### Use old.db from Kavita v0.8.3 (from the backup in config/backups)
### Point new.db to config/kavita.db


import sqlite3

# Function to migrate data
def migrate_data():
    old_db = "old.db"  # Source database
    new_db = "new.db"  # Target database

    try:
        # Connect to the databases
        old_conn = sqlite3.connect(old_db)
        new_conn = sqlite3.connect(new_db)

        # Fetch data from the old database
        cursor_old = old_conn.cursor()
        old_data_query = """
            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;
        """
        old_data = cursor_old.execute(old_data_query).fetchall()

        # Insert data into the new database
        cursor_new = new_conn.cursor()
        new_conn.execute("BEGIN TRANSACTION")
        insert_person_query = """
            INSERT OR IGNORE INTO Person (Name, NormalizedName)
            VALUES (?, ?);
        """
        select_person_id_query = """
            SELECT Id FROM Person WHERE Name = ? AND NormalizedName = ?;
        """
        insert_series_metadata_people_query = """
            INSERT INTO SeriesMetadataPeople (SeriesMetadataId, PersonId, Role)
            VALUES (?, ?, ?);
        """

        for row in old_data:
            person_name, person_normalized_name, series_id, role = row
            cursor_new.execute(select_person_id_query, (person_name, person_normalized_name))
            person = cursor_new.fetchone()

            if person:
                person_id = person[0]
            else:
                cursor_new.execute(insert_person_query, (person_name, person_normalized_name))
                person_id = cursor_new.lastrowid

            cursor_new.execute(insert_series_metadata_people_query, (series_id, person_id, role))

        new_conn.commit()
        print("Data migration successful!")

    except Exception as e:
        new_conn.rollback()
        print("Error migrating data:", e)
    finally:
        # Close the connections
        old_conn.close()
        new_conn.close()

# Run the migration
if __name__ == "__main__":
    migrate_data()