import sqlite3
import vgstash as vgs
import datetime as dt
import argparse

# Migrate VGStash schema v1 to schema v2:
#     + purchase_date : UNIX timestamp, date of a game's purchase
#     + beaten_date   : UNIX timestamp, date a game was beaten
#     + complete_date : UNIX timestamp, date a game was completed 100%

parser = argparse.ArgumentParser(
        prog="schema1-to-2",
        description="Migrate a VGStash database, in-place, from v1 to v2. This adds three columns and moves header data."
        )

parser.add_argument(
        "dbfile",
        help="path to a VGStash database file",
        )

opts = parser.parse_args()

vgdb = vgs.DB(path=opts.dbfile)

# Step 1: Add columns
columns = [
        ["Purchased: ", "p_date"],
        ["Beaten: ", "b_date"],
        ["Completed: ", "c_date"],
        ]
for c in columns:
    try:
        print("Adding '{}' column...".format(c[1]))
        # This is RISKY and RIPE FOR INJECTION. SQLite does not support
        # parameterized column names during creation; this is a workaround.
        vgdb.conn.execute("ALTER TABLE games ADD COLUMN {} INTEGER DEFAULT NULL".format(c[1]))
        vgdb.conn.commit()
        print("Success!")
    except sqlite3.OperationalError:
        print("Column {} already exists in the database. Skipping.".format(c[1]))

# Step 2: Fetch notes
res = vgdb.conn.execute("SELECT rowid,notes FROM games WHERE notes NOT LIKE ''")
targets = res.fetchall()
converted_lines = 0
removed_notes = 0
for row in targets:
    lines = row['notes'].splitlines()
    newlines = []
    # Step 3: Check for headers in notes
    for l in lines:
        has_header = False
        for i in columns:
            if l.startswith(i[0]):
                # Step 4: Convert and add data to new column location
                has_header = True
                isodate = l.split(":")[1].strip()
                unixdate = dt.date.fromisoformat(isodate).strftime("%s")
                # Same warning applies to ''.format usage and SQL queries
                set_ts = vgdb.conn.execute("UPDATE games SET {} = ? WHERE rowid = ?".format(i[1]), (unixdate, row['rowid']))
                vgdb.conn.commit()
                if (set_ts.rowcount > 0):
                    converted_lines+= 1
                continue

        if not has_header:
            newlines.append(l)
    if len(newlines) > 0:
        for l in newlines:
            if len(l) == 0:
                newlines.remove(l)
            else:
                break

    # Step 5: Update notes, if any lines were removed in Step 4.
    if (len(newlines) < len(lines)):
        set_notes = vgdb.conn.execute("UPDATE games SET notes = ? WHERE rowid = ?", ("\n".join(newlines), row['rowid']))
        vgdb.conn.commit()
        if (set_notes.rowcount > 0):
            removed_notes+=1

# Step 6: Summarize what was done.
print("{} games' notes were updated.".format(removed_notes))
print("{} details converted from Notes header to Column.".format(converted_lines))