# -*- coding: utf-8 -*-
# ------------------------------------------------------------
# smytvshow
# wrapper to sqlite3
# http://smystero.dlinkddns.org/smytvwhow/
# ------------------------------------------------------------

import os
import shutil
import sqlite3 as lite
from xbmcutils import config
from xbmcutils import logger
from traktinterface import traktapi

class SqlManager:
    db_path = os.path.join(config.plugin_dir, 'db')
    db_file = os.path.join(db_path, 'lemieserie.db')

    def __init__(self):
        self.con = None

        self.verify()

    def verify(self):
        if not os.path.exists(config.plugin_dir):
            os.makedirs(config.plugin_dir, 0777)

        if not os.path.exists(self.db_path):
            os.makedirs(self.db_path, 0777)

        if not os.path.isfile(self.db_file):
            src_file = os.path.join(config.plugin_path, "resources", "data", "lemieserie.db")
            shutil.copyfile(src_file, self.db_file)

        self.createMissingTables()

        self.checkVersion()

    def createMissingTables(self):
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute("CREATE TABLE IF NOT EXISTS trakt ("
                            "id INT, "
                            "season INT, "
                            "episode INT)")
                cur.execute("CREATE TABLE IF NOT EXISTS version ("
                            "id INTEGER PRIMARY KEY NOT NULL, "
                            "version INTEGER NOT NULL DEFAULT (0), "
                            "major INTEGER NOT NULL DEFAULT (0), "
                            "minor INTEGER NOT NULL DEFAULT (0))")
                self.con.commit()
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return

    def checkVersion(self):
        version = 0
        major = 0
        minor = 0
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute("SELECT "
                                "version, "
                                "major, "
                                "minor "
                            "FROM "
                                "version "
                            "WHERE "
                                "id = 1")
                row = cur.fetchone()
                if row is None:
                    logger.info("Updating DB to 0.0.0")
                    cur.execute('INSERT into version ('
                                'id) '
                                'values (1)')
                else:
                    version = row[0]
                    major = row[1]
                    minor = row[2]

                if (((version * 100 )+(major * 10) + minor) < 122):
                    logger.info("Updating DB to 1.2.2")
                    cur.execute("ALTER TABLE tvdbserie ADD COLUMN originaltitle TEXT")

                    cur.execute('SELECT '
                                    's.id as id ,'
                                    's.title as title '
                                'FROM '
                                    'tvdbserie s '
                                'ORDER BY '
                                    ' s.title')

                    logger.info("Retrieving original titles...")
                    rows = cur.fetchall()
                    if len(rows) > 0:
                        from tvdb import tvdb_api
                        tv_db = tvdb_api.TvDb()

                        inc = 100 / len(rows)
                        progress = 0

                        #dlg = gui.progress_dialogBG(None, config.get_localized_string(30104))
                        for row in rows:
                            sid = int(row[0])
                            #gui.progress_dialogBG(dlg, originaltitle, progress)
                            originaltitle = tv_db.get_original_title(sid)
                            if originaltitle is None:
                                originaltitle = row[1]
                            cur.execute('UPDATE '
                                            'tvdbserie '
                                        'SET '
                                            'originaltitle = :originaltitle '
                                        'WHERE '
                                            'id = :id'
                                        ,{"originaltitle": originaltitle, "id": sid})
                            progress += inc
                        #gui.progress_dialogBG(dlg, config.get_localized_string(30105), 100)
                        del tvdb_api

                    cur.execute("UPDATE version "
                                "SET "
                                    "version = 1, "
                                    "major = 2, "
                                    "minor = 2 "
                                "WHERE "
                                    "id = 1")

                    logger.info("DB updated to 1.2.2")
                self.con.commit()
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return

    def connect(self):
        if self.con is not None:
            return False
        else:
            self.con = lite.connect(self.db_file)
            return True

        return False

    def disconnect(self, connected):
        if connected and (self.con is not None):
            self.con.close()
            self.con = None

    def get_value(self, dictionary, key, default):
        if key in dictionary:
            if dictionary[key] is not None:
                return dictionary[key]
            else:
                return default
        else:
            return default

    def del_trakt(self):
        ret = False
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('delete from trakt')
                self.con.commit()
                ret = True
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def add_trakt(self):
        ret = False
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                api = traktapi.traktAPI()
                traktShowsWatched = {}
                traktShowsWatched = api.getShowsWatched(traktShowsWatched)
                traktShowsWatched = traktShowsWatched.items()
                for key, show in traktShowsWatched:
                    show_col1 = show.to_dict()
                    tvt_show_id = int(self.get_value(self.get_value(show_col1, 'ids', {}), "tvdb", "0"))
                    for season in show_col1['seasons']:
                        tvt_season = int(self.get_value(season, "number", "0"))
                        for episode in season['episodes']:
                            tvt_episode = int(self.get_value(episode, "number", "0"))
                            cur.execute('insert into trakt ('
                                        '"id", '
                                        '"season", '
                                        '"episode") '
                                        'values (:id, :season, :episode)',
                                            {
                                                "id": tvt_show_id,
                                                "season": tvt_season,
                                                "episode": tvt_episode})
                self.con.commit()
                ret = True
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def add_single_trakt(self, showtitle, season, episode):
        ret = False
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select '
                                's.id as id '
                            'from '
                                'tvdbserie s '
                            'where '
                                's.originaltitle = :showtitle',
                            {"showtitle": showtitle})
                row = cur.fetchone()
                if row is not None:
                    show_id = row[0]
                    cur.execute('select id from trakt where id = :id and season = :season and episode = :episode ',
                                {"id": show_id, "season": season, "episode": episode})
                    row = cur.fetchone()
                    if row is None:
                        cur.execute('insert into trakt ('
                                    'id, '
                                    'season, '
                                    'episode) '
                                    'values (:id, :season, :episode)',
                                        {
                                            "id": show_id,
                                            "season": season,
                                            "episode": episode})
                self.con.commit()
                ret = True
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def get_trakt(self, show_id, season, episode):
        ret = False
        rows = []
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select id from trakt where id = :id and season = :season and episode = :episode ',
                            {"id": show_id, "season": season, "episode": episode})
                row = cur.fetchone()
                if row is not None:
                    ret = True

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def add_show(self, id, serie_title, original_title, pusher, digest, url, action, target_type, target):
        ret = False
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select id from tvdbserie where id = :id',
                            {"id": id})
                row = cur.fetchone()
                if row is None:
                    cur.execute('insert into tvdbserie '
                                    '("id", '
                                    '"title", '
                                    '"originaltitle") '
                                'values '
                                    '(:id, '
                                    ':title, '
                                    ':original_title)',
                                {"id": id,
                                 "title": serie_title,
                                 "original_title": original_title})

                cur.execute('insert into pusherserie ('
                            '"id", '
                            '"pusher", '
                            '"hash", '
                            '"url", '
                            '"action", '
                            '"target_type", '
                            '"target", '
                            '"idtvdbserie") '
                            'values (NULL, :pusher, :hash, :url, :action, :target_type, :target, :id)',
                                {
                                    "pusher": pusher,
                                    "hash": digest,
                                    "url": url,
                                    "action": action,
                                    "target_type": target_type,
                                    "target": target,
                                    "id": id})
                self.con.commit()
                ret = True
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def remove_show(self, pusher, show_id):
        ret = False
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select id from pusherserie where pusher = :pusher and idtvdbserie = :id',
                            {"pusher": pusher, "id": show_id})

                row = cur.fetchone()
                if row is not None:
                    cur.execute('delete from pusherserie where id = :id',
                                {"id": row[0]})

                    cur.execute('select count(*) from pusherserie where idtvdbserie = :id',
                                {"id": show_id})

                    row = cur.fetchone()
                    if row is not None and row[0] == 0:
                        cur.execute('delete from tvdbserie where id = :id',
                                    {"id": show_id})

                self.con.commit()
                ret = True
            except lite.Error, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret

    def get_original_title(self, show_id):
        row = None
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select '
                                's.originaltitle '
                            'from '
                                'tvdbserie s '
                            'where '
                                's.id = :show_id',
                            {"show_id": show_id})
                row = cur.fetchone()

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return row

    def list_pushers_by_show(self, show_id, start=0, max=0):
        rows = []
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select '
                                'p.id as id, '
                                'p.pusher as pusher, '
                                'p.url as url, '
                                'p.action as action, '
                                'p.target_type as target_type, '
                                'p.target as target '
                            'from '
                                'pusherserie p '
                                'join '
                                    'tvdbserie s '
                                'on '
                                    's.id = p.idtvdbserie  '
                            'where '
                                'p.idtvdbserie = :show_id',
                            {"show_id": show_id})
                rows = cur.fetchall()

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return rows

    def list_show_by_pusher(self, pusher, start=0, max=0):
        rows = []
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select '
                                's.id as id, '
                                's.title as title, '
                                'p.url as url, '
                                'p.action as action, '
                                'p.target_type as target_type, '
                                'p.target as target '
                            'from '
                                'pusherserie p '
                                'join '
                                    'tvdbserie s '
                                'on '
                                    's.id = p.idtvdbserie  '
                            'where '
                                'p.pusher = :pusher',
                            {"pusher": pusher})
                rows = cur.fetchall()

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return rows

    def list_shows(self, start=0, max=0):
        rows = []
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select '
                                's.id as id, '
                                's.title as title '
                            'from '
                                'tvdbserie s '
                            'order by s.title'
                            )
                rows = cur.fetchall()

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return rows

    def find_show_by_pusher_digest(self, pusher, digest):
        ret = None
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute('select idtvdbserie as id from pusherserie where pusher = :pusher and hash = :hash',
                            {"pusher": pusher, "hash": digest})
                row = cur.fetchone()
                if row is not None:
                    ret = row

            except lite.Error, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except lite.Error, e:
            logger.error(e.message)

        return ret