from .preferences import Preferences
from .comments import Comments
from .spam import Guard
from .threads import Threads
import pymysql
import sqlite3
import logging
import os.path
import operator
from collections import defaultdict

logger = logging.getLogger("isso")

from isso.compat import buffer


class SQLite3:
    """DB-dependend wrapper around SQLite3.

    Runs migration if `user_version` is older than `MAX_VERSION` and register
    a trigger for automated orphan removal.
    """

    MAX_VERSION = 3

    def __init__(self, path, conf):

        self.path = os.path.expanduser(path)
        self.conf = conf

        rv = self.execute([
            "SELECT name FROM sqlite_master"
            "   WHERE type='table' AND name IN ('threads', 'comments', 'preferences')"]
        ).fetchone()

        self.preferences = Preferences(self)
        self.threads = Threads(self)
        self.comments = Comments(self)
        self.guard = Guard(self)

        if rv is None:
            self.execute("PRAGMA user_version = %i" % SQLite3.MAX_VERSION)
        else:
            self.migrate(to=SQLite3.MAX_VERSION)

        self.execute([
            'CREATE TRIGGER IF NOT EXISTS remove_stale_threads',
            'AFTER DELETE ON comments',
            'BEGIN',
            '    DELETE FROM threads WHERE id NOT IN (SELECT tid FROM comments);',
            'END'])

    def execute(self, sql, args=()):

        if isinstance(sql, (list, tuple)):
            sql = ' '.join(sql)

        with sqlite3.connect(self.path) as con:
            return con.execute(sql, args)

    @property
    def version(self):
        return self.execute("PRAGMA user_version").fetchone()[0]

    def migrate(self, to):

        if self.version >= to:
            return

        logger.info("migrate database from version %i to %i", self.version, to)

        # re-initialize voters blob due a bug in the bloomfilter signature
        # which added older commenter's ip addresses to the current voters blob
        if self.version == 0:

            from isso.utils import Bloomfilter
            bf = buffer(Bloomfilter(iterable=["127.0.0.0"]).array)

            with sqlite3.connect(self.path) as con:
                con.execute('UPDATE comments SET voters=?', (bf, ))
                con.execute('PRAGMA user_version = 1')
                logger.info("%i rows changed", con.total_changes)

        # move [general] session-key to database
        if self.version == 1:

            with sqlite3.connect(self.path) as con:
                if self.conf.has_option("general", "session-key"):
                    con.execute('UPDATE preferences SET value=? WHERE key=?', (
                        self.conf.get("general", "session-key"), "session-key"))

                con.execute('PRAGMA user_version = 2')
                logger.info("%i rows changed", con.total_changes)

        # limit max. nesting level to 1
        if self.version == 2:

            def first(rv):
                return list(map(operator.itemgetter(0), rv))

            with sqlite3.connect(self.path) as con:
                top = first(con.execute(
                    "SELECT id FROM comments WHERE parent IS NULL").fetchall())
                flattened = defaultdict(set)

                for id in top:

                    ids = [id, ]

                    while ids:
                        rv = first(con.execute(
                            "SELECT id FROM comments WHERE parent=?", (ids.pop(), )))
                        ids.extend(rv)
                        flattened[id].update(set(rv))

                for id in flattened.keys():
                    for n in flattened[id]:
                        con.execute(
                            "UPDATE comments SET parent=? WHERE id=?", (id, n))

                con.execute('PRAGMA user_version = 3')
                logger.info("%i rows changed", con.total_changes)


class MySQL:
    """DB-dependend wrapper around MySQL.
    """

    def __init__(self, conf):
        self.conf = conf
        self.host = self.conf.get("mysql", "host")
        self.user = self.conf.get("mysql", "user")
        self.pwd = self.conf.get("mysql", "pwd")
        self.db = self.conf.get("mysql", "db")

        connection = pymysql.connect(self.host, self.user, self.pwd)
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SHOW DATABASES;")
        dbexists = False
        for database in cursor.fetchall():
            if database["Database"] == self.db:
                dbexists = True
                break
        if not dbexists:
            cursor.execute("CREATE DATABASE %s;" % (self.db,))
            connection.commit()
        cursor.close()
        connection.close()

        self.preferences = Preferences(self)
        self.threads = Threads(self)
        self.comments = Comments(self)
        self.guard = Guard(self)
        # self.execute("DROP TRIGGER IF EXISTS remove_stale_threads")
        # self.execute([
        #     'CREATE TRIGGER remove_stale_threads',
        #     'AFTER DELETE ON comments FOR EACH ROW',
        #     'BEGIN',
        #     '    DELETE FROM threads WHERE id NOT IN (SELECT tid FROM comments);',
        #     'END'])

    def execute(self, sql, args=()):
        if isinstance(sql, (list, tuple)):
            sql = ' '.join(sql)
        sql = sql.replace("?", "%s")
        for i in range(len(args)):
            if isinstance(args[i], memoryview):
                args = list(args)
                args[i] = args[i].tobytes()
                args = tuple(args)

        connection = pymysql.connect(self.host, self.user, self.pwd, self.db)
        cursor = connection.cursor(pymysql.cursors.Cursor)
        cursor.execute(sql, args)
        connection.commit()
        return cursor
