import logging
from functools import partial

import MySQLdb as mysql

from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD

# upgrade steps register
steps = []


def register(r0, r1, func=None):
    # register upgrade functions
    if func == None:
        return partial(register, r0, r1)
    steps.append((r0, r1, func))
    return func


def upgrade(fromrev=None):
    """run database schema upgrade steps"""

    conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8')
    curs = conn.cursor()
    logger = logging.getLogger(__name__)

    # current schema version
    curs.execute("""SELECT value+0 FROM options WHERE name='version'""")
    schema = curs.fetchone()[0]

    if fromrev is not None:
        logger.info("force upgrade from v%s", schema)
        schema = fromrev

    # filter applicable upgrade steps
    todo = filter(lambda x: x[0] >= schema, steps)

    # iter upgrade steps
    for fromr, tor, step in sorted(todo):

        # run upgrade step
        logger.info("upgrading database schema from v%s to v%s", fromr, tor)
        step(conn)

        # update schema version
        curs.execute("""UPDATE options SET value=%s
                        WHERE name='version'""", str(tor))

    conn.commit()
    conn.close()


@register(2.4, 2.5)
def upgrade_24_to_25(conn):
    curs = conn.cursor()

    # make mysql beheave: use sql strict mode
    curs.execute("""SET GLOBAL sql_mode='STRICT_TRANS_TABLES'""")

    # consolidate privileges: there is no need to specify grants
    # both for 'localhost' and for '%' hosts. drop privileges granted
    # for hosts different than '%'
    curs.execute("""DELETE mysql.db FROM mysql.db, users
                    WHERE User=username AND Host <> '%'""")

    # drop privileges granted explicitly on 'transactions' tables
    curs.execute("""DELETE mysql.tables_priv FROM mysql.tables_priv, users
                    WHERE User=username AND Table_name='transactions'""")

    # drop privileges granted on 'test' database
    curs.execute("""DELETE mysql.db FROM mysql.db, users
                    WHERE User=username AND Db='test'""")

    # consolidate users accounts: there is no need to specify user
    # accounts both for 'localhost' and for '127.0.0.1' hosts. drop
    # user accounts for '127.0.0.1'
    curs.execute("""DELETE mysql.user FROM mysql.user, users
                    WHERE User=username AND Host='127.0.0.1'""")

    # reload grant tables
    curs.execute("FLUSH PRIVILEGES")

    # drop unused tables
    curs.execute("DROP TABLE IF EXISTS user_access")
    curs.execute("DROP TABLE IF EXISTS user_hosts")

    # drop 'password' column from users table in administrative
    # database: authentication is done using mysql database
    curs.execute("ALTER TABLE users DROP COLUMN password")

    # for each registered database
    curs.execute("SELECT db_name FROM available_dbs")
    databases = [row[0] for row in curs.fetchall()]
    for db in databases:
        # replace 'users' table with a view
        curs.execute("""DROP TABLE IF EXISTS `%s`.users""" % db)
        curs.execute("""CREATE VIEW `%s`.users AS
                        SELECT id, username FROM `%s`.users""" % (db, DATABASE))

    conn.commit()


@register(2.5, 2.6)
def upgrade_25_to_26(conn):
    curs = conn.cursor()

    # crerate "queries" table
    curs.execute("""CREATE TABLE IF NOT EXISTS queries (
                      id INT NOT NULL AUTO_INCREMENT,
                      title TEXT NOT NULL,
                      db TEXT NOT NULL,                             
                      query TEXT NOT NULL,
                      PRIMARY KEY (id)) CHARSET=utf8""")

    conn.commit()


@register(2.6, 2.7)
def upgrade_26_to_27(conn):
    logger = logging.getLogger(__name__)
    curs = conn.cursor()

    # store current database name
    curs.execute("SELECT DATABASE()")
    current = curs.fetchone()[0]

    # for each registered database with the old version
    curs.execute("SELECT db_name FROM available_dbs WHERE version < 2")
    databases = [row[0] for row in curs.fetchall()]
    for db in databases:
        try:
            # perform the big bad database schema upgrade
            logger.info("  upgrading database %s", db)
            curs.execute("USE `%s`" % db)
            _transmogrify(conn)
        except Exception:
            import traceback
            traceback.print_exc()
        else:
            # update version information
            curs.execute("USE `%s`" % current)
            curs.execute("UPDATE available_dbs SET version=2")

    # switch back to default database
    curs.execute("USE `%s`" % current)


def _transmogrify(conn):
    """the big bad database schema upgrade"""

    logger = logging.getLogger(__name__)
    curs = conn.cursor()

    # upgrade "objmeta" table
    curs.execute("ALTER TABLE objmeta DROP COLUMN id")
    curs.execute("ALTER TABLE objmeta MODIFY obj_id INT(11) UNSIGNED NOT NULL")

    # list of object types in obtained with
    # grep classdef classes/\@* -r | grep ltpda_ | grep -v Hidden | awk '{print $2}' | sort
    curs.execute("""ALTER TABLE objmeta MODIFY obj_type ENUM('ao', 'collection',
                    'filterbank', 'matrix', 'mfir', 'miir', 'parfrac', 'pest',
                    'plist', 'pzmodel', 'rational', 'smodel', 'ssm', 'timespan') NOT NULL""")

    # upgrade "bobjs" table
    curs.execute("ALTER TABLE bobjs DROP COLUMN id")
    curs.execute("ALTER TABLE bobjs MODIFY obj_id INT(11) UNSIGNED NOT NULL")
    curs.execute("ALTER TABLE bobjs DROP INDEX object_index")

    # upgrade "xxdata" tables
    for table in ('cdata', 'fsdata', 'tsdata', 'xydata'):
        # construct new table name
        newtable = '%s%d' % (table, 2)

        # upgrade
        curs.execute("DROP TABLE IF EXISTS %s" % newtable)
        curs.execute("CREATE TABLE %s SELECT * FROM %s" % (newtable, table))
        curs.execute("ALTER TABLE %s ADD COLUMN obj_id INT(11) UNSIGNED NOT NULL FIRST" % newtable)
        curs.execute("UPDATE %s AS new, ao SET new.obj_id=ao.obj_id WHERE new.id=ao.data_id" % newtable)

        # remove columns
        curs.execute("ALTER TABLE %s DROP COLUMN id" % newtable)

        # drop old table
        curs.execute("DROP TABLE %s" % table)
        curs.execute("CREATE TABLE %s SELECT * FROM %s" % (table, newtable))
        curs.execute("DROP TABLE %s" % newtable)

    # upgrade "cdata" table
    curs.execute("ALTER TABLE cdata DROP COLUMN xunits")

    # upgrade "ao" table
    curs.execute("ALTER TABLE ao DROP COLUMN id")
    curs.execute("ALTER TABLE ao MODIFY obj_id INT(11) UNSIGNED NOT NULL")
    curs.execute("ALTER TABLE ao DROP COLUMN mfilename")
    curs.execute("ALTER TABLE ao DROP COLUMN mdlfilename")
    curs.execute("ALTER TABLE ao DROP COLUMN data_id")
    curs.execute("""ALTER TABLE ao MODIFY data_type
                    ENUM('cdata', 'tsdata', 'fsdata', 'xydata') NOT NULL""")

    # upgrade "xxir" tables
    for table in ('mfir', 'miir'):
        curs.execute("ALTER TABLE %s MODIFY obj_id INT(11) UNSIGNED NOT NULL" % table)
        curs.execute("ALTER TABLE %s DROP COLUMN id" % table)

    # use "utf8" encoding
    curs.execute("SHOW TABLES")
    for table in [d[0] for d in curs.fetchall()]:
        dcurs = conn.cursor(mysql.cursors.DictCursor)
        dcurs.execute("DESCRIBE %s" % table)
        columns = [d['Field'] for d in dcurs.fetchall() if d['Type'] == 'text']
        for col in columns:
            curs.execute("ALTER TABLE %s MODIFY %s TEXT CHARACTER SET utf8" % (table, col))

    # upgrade to innodb storage
    curs.execute("SHOW TABLES")
    tables = [t[0] for t in curs.fetchall()]
    conn.autocommit(False)
    for table in tables:
        # "users" is a view and not a table
        if table in ('users', ):
            continue
        curs = conn.cursor()
        curs.execute("ALTER TABLE %s ENGINE = InnoDB" % table)
        # commit after each table conversion
        conn.commit()

    # for all tables
    curs.execute("SHOW TABLES")
    tables = [t[0] for t in curs.fetchall()]
    for table in tables:
        # except "objs", "collections" and "transactions"
        if table in ('collections', 'objs', 'transactions', 'users'):
            continue
        # add primary key
        curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table)
        # add foreign keys
        logger.info("    add foreign key to table %s", table)
        curs.execute("""ALTER TABLE %s ADD FOREIGN KEY (obj_id)
                        REFERENCES objs(id) ON DELETE CASCADE""" % table)
        conn.commit()


@register(2.7, 2.8)
def upgrade_27_to_28(conn):
    curs = conn.cursor()

    # for each registered database
    curs.execute("SELECT db_name FROM available_dbs")
    databases = [row[0] for row in curs.fetchall()]
    for db in databases:
        # add 'toffset' field to 'tsdata' table
        curs.execute("""ALTER TABLE `%s`.tsdata
                        ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db)

    conn.commit()