#!/usr/bin/python2.4

# This script requires that mplayer be installed...

# Copyright (C) Michael Still (mikal@stoillhq.com) 2006, 2007
# Released under the terms of the GNU GPL

import commands
import datetime
import feedparser
import MySQLdb
import os
import re
import shutil
import sys
import time
import unicodedata
import urllib

from socket import gethostname

##
# IpTvVideo
##

class IpTvVideo:
  """IpTvVideo -- video handling methods"""

  def __init__(self, filename):
    self.filename = filename
    self.length = -1

  def Length(self):
    """Length -- return the length of the video in seconds

    This uses mplayer to determine the length of the video. Specifically, this
    command line does the trick:

    mplayer -frames 0 -identify <filename> 2>&1 | grep ID_LENGTH
    """

    if self.length > 0:
      return self.length

    self.length = float(commands.getoutput('mplayer -frames 0 -identify '
                                           '%s 2>&1 | grep ID_LENGTH | '
                                           'sed s/ID_LENGTH=//' \
                                           % self.filename))
    return self.length


##
# IpTvDatabase
##

class IpTvDatabase:
  """IpTvDatavase -- handle all MySQL details"""

  def __init__(self):
    self.OpenConnection()
    self.CheckSchema()
    self.CleanLog()

  def OpenConnection(self):
    """OpenConnection -- parse the MythTV config file and open a connection
    to the MySQL database"""

    # Load the text configuration file
    self.config_values = {}
    home = os.environ.get('HOME')
    config = open(home + '/.mythtv/mysql.txt')
    for line in config.readlines():
      if not line.startswith('#') and len(line) > 5:
        (key, value) = line.rstrip('\n').split('=')
        self.config_values[key] = value

    # Open the DB connection
    self.db_connection = MySQLdb.connect(
                           host = self.config_values['DBHostName'],
                           user = self.config_values['DBUserName'],
                           passwd = self.config_values['DBPassword'],
                           db = self.config_values['DBName'])

  def CheckSchema(self):
    """CheckSchema -- ensure we're running the latest schema version"""

    # Check if we even have an IPTV set of tables
    for table in ['mythiptv_log', 'mythiptv_settings', 'mythiptv_programs']:
      try:
        cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('describe %s;' % table)
        cursor.close()

      except MySQLdb.Error, (errno, errstr):
        if errno == 1146:
          self.CreateTable(table)
        else:
          print 'Error %d: %s' %(errno, errstr)
          sys.exit(1)

    # Check the schema version
    self.version = self.GetSetting('schema')
    if self.version != '5':
      self.UpdateTables()

    # Make sure we have a chanid
    # TODO(mikal): Hmmm, I need to work out why the channel doesn't display
    #   properly in the UI
    chanid = self.GetSetting('chanid')
    if chanid == None:
      # There is none cached in the settings table
      channels_row = self.GetOneRow('select chanid from channel where '
                                    'name = "MythIPTV";')
      if channels_row != None:
        # There is one in the MythTV Channels table though
        chanid = self.GetSettingWithDefault('chanid', channels_row['chanid'])

      else:
        # There isn't one in the MythTV Channels table
        chanid = self.GetOneRow('select max(chanid) + 1 from channel') \
                                ['max(chanid) + 1']
        self.db_connection.query('insert into channel (chanid, callsign, name, '
                                 'commfree) values (%d, "MythIPTV", '
                                 '"MythIPTV", 1)' % chanid)

        self.Log('Created IPTV channel with chanid %d' % chanid)

        # Redo the selecting to make sure it worked
        channels_row = self.GetOneRow('select chanid from channel where '
                                    'name = "MythIPTV";')
        chanid = self.GetSettingWithDefault('chanid', channels_row['chanid'])

  def GetSetting(self, name):
    """GetSetting -- get the current value of a setting"""

    row = self.GetOneRow('select value from mythiptv_settings where '
                         'name="%s";' % name)
    if row == None:
      return None
    return row['value']

  def GetSettingWithDefault(self, name, default):
    """GetSettingWithDefault -- get a setting with a default value"""

    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('select value from mythiptv_settings where '
                   'name="%s";' % name)
    if cursor.rowcount != 0:
      retval = cursor.fetchone()
      cursor.close()
      return retval['value']
    else:
      self.db_connection.query('insert into mythiptv_settings (name, value) '
                               'values("%s", "%s");' %(name, default))
      self.Log('Settings value %s defaulted to %s' %(name, default))
      return default

  def GetOneRow(self, sql):
    """GetOneRow -- get one row which matches a query"""

    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(sql)
    retval = cursor.fetchone()
    cursor.close()

    if retval == None:
      return retval

    for key in retval.keys():
      if retval[key] == None:
        del retval[key]

    return retval

  def GetWaitingForImport(self):
    """GetWaitingForImport -- return a list of the guids waiting for import"""

    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('select guid from mythiptv_programs where '
                   'download_finished = "1" and imported is NULL')
    guids = []
    while True:
      program = cursor.fetchone()
      if program == None:
        break

      guids.append(program['guid'])

    return guids

  def FormatSqlValue(self, name, value):
    """FormatSqlValue -- some values get escaped for SQL use"""

    if name == 'date':
      return 'STR_TO_DATE("%s", "%s")' %(value, '''%a, %d %b %Y %H:%i:%s''')
    if type(value) == datetime.datetime:
      return 'STR_TO_DATE("%s", "%s")' \
             %(value.strftime('%a, %d %b %Y %H:%M:%S'),
               '''%a, %d %b %Y %H:%i:%s''')
    if type(value) == long:
      return value
    return '"%s"' % value.replace("'", "''")

  def WriteOneRow(self, table, key_col, dict):
    """WriteOneRow -- use a dictionary to write a row to the specified table"""

    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('select %s from %s where %s = "%s"' \
                   %(key_col, table, key_col, dict[key_col]))

    if cursor.rowcount > 0:
      self.Log('Updating %s row with %s of %s' %(table, key_col, dict[key_col]))
      vals = []
      for col in dict:
        val = '%s=%s' %(col, self.FormatSqlValue(col, dict[col]))
        vals.append(val)

      sql = 'update %s set %s where %s="%s";' %(table, ','.join(vals),
                                                key_col, dict[key_col])

    else:
      self.Log('Creating %s row with %s of %s' %(table, key_col, dict[key_col]))
      vals = []
      for col in dict:
        val = self.FormatSqlValue(col, dict[col])
        vals.append(val)

      sql = 'insert into %s (%s) values(%s);' \
             %(table, ','.join(dict.keys()), ','.join(vals))

    cursor.close()
    self.db_connection.query(sql)

  def GetNextLogSequenceNumber(self):
    """GetNextLogSequenceNumber -- ghetto lookup of the highest sequence
    number"""

    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('select max(sequence) + 1 from mythiptv_log;')
    retval = cursor.fetchone()
    cursor.close()
    return retval['max(sequence) + 1']

  def Log(self, message):
    """Log -- write a log message to the database"""

    new_sequence = self.GetNextLogSequenceNumber()
    self.db_connection.query('insert into mythiptv_log (sequence, timestamp, '
                             'message) values(%d, NOW(), "%s");' \
                              %(new_sequence, message))

  def CleanLog(self):
    """CleanLog -- remove all but the newest xxx log messages"""

    min_sequence = self.GetNextLogSequenceNumber() - \
                   int(self.GetSettingWithDefault('loglines', '1000')) - 1
    cursor = self.db_connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('delete from mythiptv_log where sequence < %d' \
                   % min_sequence)
    if cursor.rowcount > 0:
      self.Log('Deleted %d log lines before sequence number %d' \
               %(cursor.rowcount, min_sequence))
    cursor.close()

  def CreateTable(self, tablename):
    """CreateTable -- a table has been found to be missing, create it with
    the current schema"""

    print 'Info: Creating %s table' % tablename
    if tablename == 'mythiptv_log':
      self.db_connection.query('create table mythiptv_log (sequence int, '
                               'timestamp datetime, message text);')
      self.db_connection.query('insert into mythiptv_log (sequence) '
                               'values(0);')

    elif tablename == 'mythiptv_settings':
      self.db_connection.query('create table mythiptv_settings (name text, '
                               'value text);')
      self.db_connection.query('insert into mythiptv_settings (name, value) '
                               'values("schema", 5);')

    elif tablename == 'mythiptv_programs':
      self.db_connection.query('create table mythiptv_programs (guid text, '
                               'url text, title text, subtitle text, '
                               'description text unicode, date datetime, '
                               'unparsed_date text, parsed_date text, '
                               'download_started int, '
                               'download_finished int, '
                               'imported int, transfered int, size int, '
                               'filename text);')

    else:
      self.Log('Error: Don\'t know how to create %s' % tablename)
      print 'Error: Don\'t know how to create %s' % tablename
      sys.exit(1)

    self.Log('Creating %s table' % tablename)

  def UpdateTables(self):
    """UpdateTables -- handle schema upgrades"""

    if self.version == '4':
      self.Log('Upgrading schema from 4 to 5')
      self.db_connection.query('alter table mythiptv_programs '
                               'add parsed_date text;')
      self.version = '5'

    self.db_connection.query('update mythiptv_settings set value = "%s" where '
                             'name = "schema";' % self.version)

  def ExecuteSql(self, sql):
    self.db_connection.query(sql)


##
# IpTvProgram
##

class IpTvProgram:
  """IpTvProgram -- a downloadable program.

  This class embodies everything we can do with a program. The existance
  of this class does not mean that the show has been fully downloaded and
  made available in MythTV yet. Instances of this class persist to the MySQL
  database.
  """

  def __init__(self, db):
    self.persistant = {}
    self.db = db

  def FromUrl(self, url, guid):
    """FromUrl -- start a program based on it's URL"""

    url = url.replace('&amp;', '&')

    # Some URLs have the ampersand escaped
    re_filename = re.compile('.*/([^/\?]*).*')
    m = re_filename.match(url)
    filename = m.group(1)
    
    # Persist what we know now
    self.persistant['url'] = url
    self.persistant['filename'] = filename
    self.persistant['guid'] = guid
    self.Store()
    self.db.Log('Created show from %s with guid %s' %(url, guid))

  def Load(self, guid):
    """Load -- load information based on a GUID from the DB"""
    self.persistant = self.db.GetOneRow('select * from mythiptv_programs '
                                        'where guid="%s";' % guid)

  def Store(self):
    """Store -- persist to MySQL"""

    try:
      self.db.WriteOneRow('mythiptv_programs', 'guid', self.persistant)
    except MySQLdb.Error, (errno, errstr):
      if errno != 1064:
        self.db.Log('Could not store program: %s (%d, %s)' \
                    %(self.persistant['guid'], errno, errstr))

  def SetShowInfo(self, title, subtitle, description, date, date_parsed):
    """SetShowInfo -- set show meta data"""

    self.persistant['title'] = title
    self.persistant['subtitle'] = subtitle
    self.persistant['description'] = \
      unicodedata.normalize('NFKD', description).encode('ascii', 'ignore')
    self.persistant['date'] = date
    self.persistant['unparsed_date'] = date
    self.persistant['parsed_date'] = repr(date_parsed)
    self.Store()
    self.db.Log('Set show info for guid %s' % self.persistant['guid'])
    print 'Show date is %s' % date

  def Download(self, datadir):
    """Download -- download the show"""

    filename = '%s/%s' %(datadir, self.persistant['filename'])
    print 'Destination will be %s' % filename
    self.db.Log('Downloading %s to %s' %(self.persistant['guid'], filename))

    self.persistant['download_started'] = '1'
    self.Store()

    done = self.persistant.get('download_finished', '0')
    if done != '1':
      print 'Downloading'
      remote = urllib.urlopen(self.persistant['url'])
      local = open(filename, 'w')
      total = 0
      count = 0

      while done != '1':
        data = remote.read(1024)
        length = len(data)
        if length < 1024:
          done = '1'
        local.write(data)
        total += len(data)
     
        if count > 1000:
          self.persistant['transfered'] = repr(total)
          # TODO(mikal): Size should be determined beforehand if possible
          self.persistant['size'] = repr(total)
          self.Store()
          count = 0
        count += 1

      print 'Finished'
      remote.close()
      local.close()

    self.persistant['download_finished'] = '1'
    self.persistant['transfered'] = repr(total)
    self.persistant['size'] = repr(total)
    self.Store()
      
    print 'Done'
    self.db.Log('Download of %s done' % self.persistant['guid'])
    return

  def Import(self):
    """Import -- import a downloaded show into the MythTV user interface"""

    # Determine meta data
    self.db.Log('Importing %s' % self.persistant['guid'])
    datadir = db.GetSettingWithDefault('datadir', 'data')
    chanid = self.db.GetSetting('chanid')
    filename = '%s/%s' %(datadir, self.persistant['filename'])
    videodir = self.db.GetOneRow('select * from settings where value = '
                                 '"RecordFilePrefix" and hostname = "%s";' \
                                 % gethostname())['data']

    video = IpTvVideo(filename)

    # Try to use the publish time of the RSS entry as the start time...
    # The tuple will be in the format: 2003, 8, 6, 20, 43, 20
    try:
      tuple = eval(self.persistant['parsed_date'])
      start = datetime.datetime(tuple[0], tuple[1], tuple[2], tuple[3],
                                tuple[4], tuple[5])
    except:
      start = datetime.datetime.now()

    # Ensure uniqueness for the start time
    interval = datetime.timedelta(seconds = 1)
    while not self.db.GetOneRow('select basename from recorded where '
                                'starttime = %s and chanid = %s and ' 
                                'basename != "%s"' \
                                %(self.db.FormatSqlValue('', start),
                                  chanid, filename)) == None:
      start += interval
      

    # Determine the duration of the video
    duration = datetime.timedelta(seconds = video.Length())
    finish = start + duration

    # TODO(mikal): transcode file to a better format if needed

    # Make sure we haven't loaded this file before
    if not self.db.GetOneRow('select * from recorded where basename = "%s"' \
                             % filename) == None:
      print 'Already imported %s (%s)' %(self.persistant['guid'], filename)

    else:
      print 'Importing video %s...' % self.persistant['guid']
      shutil.move(filename, videodir + '/' + self.persistant['filename'])

      print 'Creating row'
      self.db.ExecuteSql('insert into recorded (chanid, starttime, endtime, ' \
                         'title, subtitle, description, hostname, basename, ' \
                         'progstart, progend) values (%s, %s, %s, "%s", ' \
                         '"%s", %s, "%s", "%s", %s, %s)' \
                         %(chanid,
                         self.db.FormatSqlValue('', start),
                         self.db.FormatSqlValue('', finish),
                         self.persistant['title'],
                         self.persistant['subtitle'],
                         self.db.FormatSqlValue('',
                                                self.persistant['description']),
                         gethostname(),
                         self.persistant['filename'],
                         self.db.FormatSqlValue('', start),
                         self.db.FormatSqlValue('', finish)))

      print 'Rebuilding seek table'
      commands.getoutput('mythcommflag --rebuild --file "%s"' \
                         % videodir + '/' + self.persistant['filename'])

      self.persistant['imported'] = '1';
      self.Store()

      print 'Done'
      print

    # And now mark the video as imported
    return


##
# Syncing helpers
##

re_attributeparser = re.compile('([^=]*)="([^"]*)" *(.*)')
def ParseAttributes(inputline):
  """ParseAttributes -- used to unmangle XML entity attributes"""
  line = inputline
  result = {}

  m = re_attributeparser.match(line)
  while m:
    result[m.group(1)] = m.group(2)
    line = m.group(3)
    m = re_attributeparser.match(line)

  return result

def Download(db, url, guid, title, subtitle, description, date, date_parsed):
  """Download -- add a program to the list of waiting downloads"""
  print 'Creating program for %s: %s from %s' %(title, subtitle, guid)
  program = IpTvProgram(db)
  program.FromUrl(url, guid)
  program.SetShowInfo(title, subtitle, description, date, date_parsed)

def Sync(db, xmlfile, title):
  """Sync -- sync up with an RSS feed"""
  # Grab the XML
  xmllines = xmlfile.readlines()

  # Modify the XML to work around namespace handling bugs in FeedParser
  print 'Apply media:content work around'
  lines = []
  re_mediacontent = re.compile('(.*)<media:content([^>]*)/ *>(.*)')

  for line in xmllines:
    m = re_mediacontent.match(line)
    count = 1
    while m:
      line = '%s<media:wannabe%d>%s</media:wannabe%d>%s' %(m.group(1), count,
                                                         m.group(2),
                                                         count, m.group(3))
      m = re_mediacontent.match(line)
      count = count + 1

    lines.append(line)

  # Parse the modified XML
  xml = ''.join(lines)
  parser = feedparser.parse(xml)
  if parser.feed.has_key('title'):
    print parser.feed.title
  elif parser.feed.has_key('description'):
    print parser.feed.description
  else:
    print '\tUrl: %s'%(url)

  print ''

  # Find the media:content entries
  print 'Info: Processing feed %s (%d entries)' %(title, len(parser.entries))
  for entry in parser.entries:
    videos = {}
    description = entry.description
    subtitle = entry.title

    if entry.has_key('media_description'):
      description = entry['media_description']

    # Enclosures
    if entry.has_key('enclosures'):
      for enclosure in entry.enclosures:
        videos[enclosure.type] = enclosure

    # Media:RSS
    for key in entry.keys():
      if key.startswith('media_wannabe'):
        attrs = ParseAttributes(entry[key])
        if attrs.has_key('type'):
          videos[attrs['type']] = attrs
        if attrs.has_key('title'):
          subtitle = attrs['title']

    if videos.has_key('video/x-msvideo'):
      Download(db,
               videos['video/x-msvideo']['url'],
               entry.guid,
               title,
               subtitle,
               description,
               entry.date,
               entry.date_parsed)
    elif videos.has_key('video/mp4'):
      Download(db,
               videos['video/mp4']['url'],
               entry.guid,
               title,
               subtitle,
               description,
               entry.date,
               entry.date_parsed)
    else:
      print 'Error: Unsure which to prefer from: %s for %s' \
            %(repr(videos.keys()), subtitle)


if __name__ == "__main__":
  db = IpTvDatabase()

  # TODO(mikal): This command line processing is ghetto
  if sys.argv[1] == '--url':
    xmlfile = urllib.urlopen(sys.argv[2])
    Sync(db, xmlfile, sys.argv[3])

  elif sys.argv[1] == '--file':
    xmlfile = open(sys.argv[2])
    Sync(db, xmlfile, sys.argv[3])

  elif sys.argv[1] == '--download':
    for i in range(int(sys.argv[2])):
      guid = db.GetOneRow('select guid from mythiptv_programs where '
                          'download_finished is NULL and title is not NULL '
                          'order by date asc limit 1;')['guid']
      print 'Downloading %s' % guid
      program = IpTvProgram(db)
      program.Load(guid)
      program.Download(db.GetSettingWithDefault('datadir', 'data'))
      program.Import()

    # And now make sure there aren't any straglers
    for guid in db.GetWaitingForImport():
      program = IpTvProgram(db)
      program.Load(guid)
      program.Import()

  else:
    print 'Unknown command line. Try one of:'
    print '  --url <url>     : to download an RSS feed and load the shows from'
    print '                    it into the TODO list'
    print '  --file <url>    : to do the same, but from a file'
    print '  --download <num>: to download that number of shows from the TODO'
    print '                    list. We download oldest first.'
    print