import xbmc
import xbmcaddon, os, time
import subprocess
import sqlite3


addon       = xbmcaddon.Addon('script.tvguide')
datapath    = addon.getAddonInfo('profile').decode('utf-8')
wd          = xbmc.translatePath(os.path.join(datapath, 'xmltv'))
wb          = xbmc.translatePath(os.path.join(datapath, 'xmltv/mk_xmltv.bat'))
startupinfo = subprocess.STARTUPINFO()
startupinfo.dwFlags |= subprocess.STARTF_USESHOWWINDOW


# if the xmltv.xml file is out of date, execute the mk_xmltv.bat to rebuild xmltv.xml
build_xml=True
if (os.path.exists(wd+'\\xmltv.xml')):
  if (time.time() - (os.path.getmtime(wd+'\\xmltv.xml')) < 60*60*24*7):
    build_xml=False

if (build_xml):
    p = subprocess.Popen( wb, startupinfo=startupinfo, cwd=wd)
    stdout, stderr = p.communicate()

# drop the tvguide source.db programs table only, create again and add indexes - this speeds up the update from xmltv.xml significantly and avoids duplicate entries
    try:
      databasePath = xbmc.translatePath(datapath)+'\\source.db'
      conn = sqlite3.connect(databasePath, detect_types=sqlite3.PARSE_DECLTYPES)
      conn.execute('PRAGMA foreign_keys = ON')
      conn.row_factory = sqlite3.Row
      c = conn.cursor()
      c.execute('DROP TABLE programs')
      c.execute('CREATE TABLE programs(channel TEXT, title TEXT, start_date TIMESTAMP, end_date TIMESTAMP, description TEXT, image_large TEXT, image_small TEXT, source TEXT, updates_id INTEGER, FOREIGN KEY(channel, source) REFERENCES channels(id, source) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY(updates_id) REFERENCES updates(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)')
      c.execute('CREATE INDEX program_list_idx ON programs(source, channel, start_date, end_date)')
      c.execute('CREATE INDEX start_date_idx ON programs(start_date)')
      c.execute('CREATE INDEX end_date_idx ON programs(end_date)')
      conn.commit()
      c.close()
    except sqlite3.OperationalError, ex:
      pass