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

import os
from mysql import connector
from xbmcutils import config
from xbmcutils import logger
from traktinterface import traktapi


'''
CREATE TABLE `tvdbserie` (
`id` int NOT NULL,
`title` text,
PRIMARY KEY (`id`)
)
CREATE TABLE `pusherserie` (
`id` int NOT NULL AUTO_INCREMENT,
`pusher` text NOT NULL,
`hash` text NOT NULL,
`url` text NOT NULL,
`action` text NOT NULL,
`target` text NOT NULL,
`target_type` text NOT NULL,
`idtvdbserie` int NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `trakt` (
`id` int NOT NULL,
`season` int NOT NULL,
`episode` int NOT NULL,
PRIMARY KEY (`id`,`season`,`episode`)
)
'''

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):
        self.createMissingTables()

        self.checkVersion()

    def createMissingTables(self):
        try:
            connected = self.connect()
            try:
                cur = self.con.cursor()
                cur.execute("CREATE TABLE IF NOT EXISTS version ("
                            "id int NOT NULL, "
                            "version int NOT NULL DEFAULT 0, "
                            "major int NOT NULL DEFAULT 0, "
                            "minor int NOT NULL DEFAULT 0, "
                            "PRIMARY KEY (id) )")
                self.con.commit()
            except Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 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 = %s '
                                        'WHERE '
                                            'id = %s'
                                        ,(originaltitle, 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 Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, e:
            logger.error(e.message)

        return

    def connect(self):
        if self.con is not None:
            return False
        else:
            mysqlconfig = MySqlConfig.dbinfo().copy()
            self.con = connector.Connect(**mysqlconfig)
            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 Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 (%s, %s, %s)',
                                        (
                                            tvt_show_id,
                                            tvt_season,
                                            tvt_episode,
                                        ))
                self.con.commit()
                ret = True
            except Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s',
                            (
                                showtitle,
                            ))
                row = cur.fetchone()
                if row is not None:
                    show_id = row[0]
                    cur.execute('SELECT '
                                    'id '
                                'FROM '
                                    'trakt '
                                'WHERE '
                                    'id = %s '
                                    'and season = %s '
                                    'and episode = %s',
                                (show_id, season, episode,))
                    row = cur.fetchone()
                    if row is None:
                        cur.execute('INSERT INTO trakt ('
                                    'id, '
                                    'season, '
                                    'episode) '
                                    'VALUES (%s, %s, %s)',
                                    (
                                        show_id,
                                        season,
                                        episode,
                                    ))
                self.con.commit()
                ret = True
            except Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s '
                                'and season = %s '
                                'and episode = %s ',
                            (
                                show_id,
                                season,
                                episode,
                            ))
                row = cur.fetchone()
                if row is not None:
                    ret = True

            except Exception, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s',
                            (
                                id,
                            ))
                row = cur.fetchone()
                if row is None:
                    cur.execute('INSERT INTO tvdbserie ('
                                'id, '
                                'title, '
                                'originaltitle ) '
                                'VALUES (%s, %s, %s)',
                                (
                                    id,
                                    serie_title,
                                    original_title,
                                ))

                cur.execute('INSERT INTO pusherserie ('
                            'id, '
                            'pusher, '
                            'hash, '
                            'url, '
                            'action, '
                            'target_type, '
                            'target, '
                            'idtvdbserie) '
                            'VALUES (null, %s, %s, %s, %s, %s, %s, %s)',
                            (
                                pusher,
                                digest,
                                url,
                                action,
                                target_type,
                                target,
                                id,
                            ))
                self.con.commit()
                ret = True
            except Exception, e:
                logger.error(e.message)
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s '
                                'and idtvdbserie = %s',
                            (
                                pusher,
                                show_id,
                            ))

                row = cur.fetchone()
                if row is not None:
                    cur.execute('DELETE '
                                'FROM '
                                    'pusherserie '
                                'WHERE '
                                    'id = %s',
                                (
                                    row[0],
                                ))

                    cur.execute('SELECT '
                                    'count(*) '
                                'FROM '
                                    'pusherserie '
                                'WHERE '
                                    'idtvdbserie = %s',
                                (
                                    show_id,
                                ))

                    row = cur.fetchone()
                    if row is not None and row[0] == 0:
                        cur.execute('DELETE '
                                    'FROM '
                                        'tvdbserie '
                                    'WHERE '
                                        'id = %s',
                                    (
                                        show_id,
                                    ))

                self.con.commit()
                ret = True
            except Exception, e:
                self.con.rollback()
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s',
                            (show_id, ))
                row = cur.fetchone()

            except Exception, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s',
                            (
                                show_id,
                            ))
                rows = cur.fetchall()

            except Exception, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s '
                            'ORDER BY '
                                ' s.title',
                            (
                                pusher,
                            ))
                rows = cur.fetchall()

            except Exception, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 Exception, e:
                logger.error(e.message)
            finally:
                self.disconnect(connected)
        except Exception, 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 = %s '
                                'and hash = %s',
                            (
                                pusher,
                                digest,
                            ))
                row = cur.fetchone()
                if row is not None:
                    ret = row

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

        return ret


class MySqlConfig(object):
    """Configure me so examples work

    Use me like this:

        mysqlinterface.connector.Connect(**Config.dbinfo())
    """

    HOST = config.get_setting("mysql_host")
    DATABASE = config.get_setting("mysql_db")
    USER = config.get_setting("mysql_user")
    PASSWORD = config.get_setting("mysql_password")
    PORT = int(config.get_setting("mysql_port"))

    CHARSET = 'utf8'
    UNICODE = True
    WARNINGS = True

    @classmethod
    def dbinfo(cls):
        return {
            'host': cls.HOST,
            'port': cls.PORT,
            'database': cls.DATABASE,
            'user': cls.USER,
            'password': cls.PASSWORD,
            'charset': cls.CHARSET,
            'use_unicode': cls.UNICODE,
            'get_warnings': cls.WARNINGS,
            }
