#!/usr/bin/python
#
# Find Plex movies that are NOT in a collection.
#
# USAGE: plex_unmarked.py DBFILE
#
# NOTES
#
# List of collections:
#   SELECT * FROM tags WHERE tag_type=2
#
# The database files are located at:
#   /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases
# We want:
#   .../com.plexapp.plugins.library.db
#
# WARNING: that file must be copied elsewhere, as it remains opened
#          by the server can cannot be read. (??? unsure; just cp it)
#
# HINT: to find where a file ended up in the UI:
#   select media_item_id from media_parts where file like '%name.mp4';
#   (note that LIKE is case-insensitive)
#   select metadata_item_id from media_items where id=$ABOVE
#   select * from metadata_items where id=$ABOVE
#

import sys
import sqlite3


def main(fname):
  conn = sqlite3.connect(fname)

  print _DIVIDER
  find_empty_coll(conn)
  print _DIVIDER
  check_workout(conn)
  print _DIVIDER

_DIVIDER = '-'*75


def find_empty_coll(conn):
  "Find all movies that are not in a collection."
  print 'Finding movies not in a collection...'
  print
  for row in conn.execute(_FIND_EMPTY_COLL, (_MOVIES_LIBRARY,)):
    print row
_FIND_EMPTY_COLL = '''SELECT title FROM metadata_items
                      WHERE tags_collection="" AND library_section_id=?
                      ORDER BY title
                   '''
_MOVIES_LIBRARY = 3  # for gstein's plex server


def check_workout(conn):
  "All workout videos should have the right genre, too."
  print 'Checking workout videos...'
  print
  for row in conn.execute(_CHECK_WORKOUT):
    print row
_CHECK_WORKOUT = '''SELECT title, tags_genre FROM metadata_items
                    WHERE tags_genre!="Workout"
                          AND tags_collection="Workout Videos"
                 '''


if __name__ == '__main__':
  main(sys.argv[1])