from flask import Flask
from flaskext.mysql import MySQL as mysql

class Query(object):
    class Script(object):
        def __init__(self, sql, script, log):
            self.sql = sql
            self.script = script
            self.log = log
        def show(self):
            self.log(self.script)
    class Select(Script):
        def Where(self, column, logic, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} {1} {2}".format(column, logic, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereIn(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} IN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereLike(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} LIKE {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereBetween(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} BETWEEN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def FetchOne(self):
            return Query.Fetch(self.sql, self.script, self.log).One()
        def FetchAll(self):
            return Query.Fetch(self.sql, self.script, self.log).All()
    class Insert(Script):
        def Add(self, value):
            value = ",".join([ ("'{0}'".format(x) if isinstance(x, str) else "NULL" if x is None else str(x)) for x in value ])
            script = "{0}, ({1})".format(self.script, value)
            return Query.Insert(self.sql, script, self.log)
        def Fetch(self):
            return Query.Fetch(self.sql, self.script, self.log).One()
    class Update(Script):
        def Add(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            script = "{0}, {1}={2}".format(self.script, column, value)
            return Query.Update(self.sql, script, self.log)
        def Where(self, column, logic, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} {1} {2}".format(column, logic, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereIn(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} IN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereLike(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} LIKE {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereBetween(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} BETWEEN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def Fetch(self):
            return Query.Fetch(self.sql, self.script, self.log).One()
    class Delete(Script):
        def Where(self, column, logic, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} {1} {2}".format(column, logic, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereIn(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} IN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereLike(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} LIKE {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def WhereBetween(self, column, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} BETWEEN {1}".format(column, value)
            script = "{0} WHERE {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def Fetch(self):
            return Query.Fetch(self.sql, self.script, self.log).One()
    class Where(Script):
        def And(self, column, logic, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} {1} {2}".format(column, logic, value)
            script = "{0} AND {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def Or(self, column, logic, value):
            column = "`{0}`".format(column)
            value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
            where = "{0} {1} {2}".format(column, logic, value)
            script = "{0} OR {1}".format(self.script, where)
            return Query.Where(self.sql, script, self.log)
        def FetchOne(self):
            return Query.Fetch(self.sql, self.script, self.log).One()
        def FetchAll(self):
            return Query.Fetch(self.sql, self.script, self.log).All()
    class Fetch(Script):
        def One(self):
            connect = self.sql.connect()
            cursor = connect.cursor()
            try:
                cursor.execute(self.script)
                row = cursor.fetchone()
                connect.commit()
            except:
                self.show()
                connect.rollback()
                return False
            cursor.close()
            connect.close()
            return row if isinstance(row, tuple) and len(row) > 0 else True
        def All(self):
            connect = self.sql.connect()
            cursor = connect.cursor()
            try:
                cursor.execute(self.script)
                rows = cursor.fetchall()
                connect.commit()
            except:
                self.show()
                connect.rollback()
                return False
            cursor.close()
            connect.close()
            return rows if isinstance(rows, tuple) and len(rows) > 0 else ()
class MySQL(object):
    def __init__(self, host, port, usr, pwd, db, log=None):
        app = Flask(__name__)
        app.config['MYSQL_DATABASE_HOST']     = host
        app.config['MYSQL_DATABASE_PORT']     = int(port)
        app.config['MYSQL_DATABASE_USER']     = usr
        app.config['MYSQL_DATABASE_PASSWORD'] = pwd
        app.config['MYSQL_DATABASE_DB']       = db
        self.db = db
        self.sql = mysql()
        self.sql.init_app(app)
        self.log = self.Log if log is None else log
    def Select(self, table, columns="*"):
        column = "`{0}`".format("`,`".join(columns)) if isinstance(columns, list) else "*"
        script = "SELECT {0} FROM `{1}`".format(column, table)
        return Query.Select(self.sql, script, self.log)
    def Insert(self, table, columns, value):
        column = "`{0}`".format("`,`".join(columns))
        value = ",".join([ ("'{0}'".format(x) if isinstance(x, str) else "NULL" if x is None else str(x)) for x in value ])
        script = "INSERT INTO `{0}`({1}) VALUES ({2})".format(table, column, value)
        return Query.Insert(self.sql, script, self.log)
    def Update(self, table, column, value):
        column = "`{0}`".format(column)
        value = "'{0}'".format(value) if isinstance(value, str) else "NULL" if value is None else str(value)
        script = "UPDATE `{0}` SET {1}={2}".format(table, column, value)
        return Query.Update(self.sql, script, self.log)
    def Delete(self, table):
        script = "DELETE FROM `{0}`".format(table)
        return Query.Delete(self.sql, script, self.log)
    def Log(self, message):
        print(message)
    def SetLog(self, log):
        self.log = log if log is not None else self.Log