import json
import os
import sqlite3
from dotenv import load_dotenv

load_dotenv()

remap = json.load(open(file="../Migration & Backup/CHN to 39.06/unalteredRemap.json", encoding="utf-8"))
wordlist = json.load(open(file="../08.18 & CHN/Data/x64/datatable/dec/wordlist.json", encoding="utf-8"))
musicinfo = json.load(open(file="../08.18 & CHN/Data/x64/datatable/dec/musicinfo.json", encoding="utf-8"))

# Connect to the database
conn = sqlite3.connect("./Data_exported/Server/wwwroot/taiko.db3")
cursor = conn.cursor()

sql_update_query = f"SELECT SongId, COUNT(SongId) AS count FROM SongPlayData GROUP BY SongId ORDER BY SongId;"
cursor.execute(sql_update_query)
results = cursor.fetchall()

id_counts = {row[0]: row[1] for row in results}

omniPlays = {}
for remapped in remap:
    remappedId = int(remapped["uniqueIdOriginal"])
    id_count = 0
    try:
        id_count = id_counts[remappedId]
    except:
        pass
    omniPlays[remappedId] = id_count

omniPlays = dict(sorted(omniPlays.items(), key=lambda item: item[1], reverse=True))

plays = {
    "Omnimix": [],
    "Regular": [],
}
for key in omniPlays:
    value = next((item for item in remap if item["uniqueIdOriginal"] == key), None)
    nameKey = next((item for item in wordlist["items"] if item["key"] == "song_" + value["id"]), None)

    print(omniPlays[key], ": ", value["id"], "=>", nameKey["englishUsText"])
    plays["Omnimix"].append(
        {
            "id": value["id"],
            "plays": omniPlays[key],
            "nameJp": nameKey["japaneseText"],
            "nameUs": nameKey["englishUsText"],
        }
    )

regularPlays = {}
for entry in musicinfo["items"]:
    id_count = 0
    try:
        id_count = id_counts[entry["uniqueId"]]
    except:
        pass
    if id_count < 1599:
        if next((item for item in remap if item["uniqueIdRemap"] == entry["uniqueId"]), None) is None:
            regularPlays[int(entry["uniqueId"])] = id_count

regularPlays = dict(sorted(regularPlays.items(), key=lambda item: item[1], reverse=True))

for key in regularPlays:
    value = next((item for item in musicinfo["items"] if item["uniqueId"] == key), None)
    nameKey = next(
        (item for item in wordlist["items"] if item["key"] == "song_" + value["id"]),
        {"japaneseText": "", "englishUsText": ""},
    )

    print(regularPlays[key], "=>", value["id"], "=>", nameKey["englishUsText"])
    plays["Regular"].append(
        {
            "id": value["id"],
            "plays": regularPlays[key],
            "nameJp": nameKey["japaneseText"],
            "nameUs": nameKey["englishUsText"],
        }
    )

with open("./temp/listPlays.json", "w", encoding="utf8") as outfile:
    outfile.write(json.dumps(plays, indent="\t", ensure_ascii=False))

conn.close()

print(len(plays["Regular"]), "regular songs,", len(plays["Omnimix"]), "omnimix songs.")