#!/usr/bin/python

import os.path
import sqlite3
import sys
import urllib.parse

plex_query = r"""
	SELECT
		media_parts.file,
		metadata_item_settings.rating,
		media_items.id
	FROM
		media_items
		INNER JOIN metadata_items
			ON media_items.metadata_item_id = metadata_items.id
		LEFT JOIN metadata_item_settings
			ON metadata_items.guid = metadata_item_settings.guid
		LEFT JOIN media_parts
			ON media_items.id = media_parts.media_item_id
	WHERE
		metadata_item_settings.rating IS NOT NULL
		AND metadata_items.metadata_type = 10
		AND file != '';
"""

strawberry_query = r"""
	UPDATE songs SET
		rating = ?
	WHERE
		url = ?
"""

path_transformers = (
	lambda x: "/precisely"+x,
	lambda x: urllib.parse.quote(x, "/()',&!+$=*;:@"),
	lambda x: "file://"+x,
)

#plex_path = "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
plex_path = os.path.expanduser("~/com.plexapp.plugins.library.db")
strawberry_path = os.path.expanduser("~/.local/share/strawberry/strawberry/strawberry.db")





pdb = sqlite3.connect(plex_path)
sdb = sqlite3.connect(strawberry_path, autocommit=True)

for file, rating, plex_id in pdb.execute(plex_query):
	print(file)
	for pt in path_transformers:
		file = pt(file)
	rc = sdb.execute(strawberry_query, (rating/10, file)).rowcount
	if rc != 1:
		print("rowcount %s! %s (%s)" % (rc, file, plex_id), file=sys.stderr)

pdb.close()
sdb.close()