from flask import Flask as __flask

from flaskext.mysql import MySQL as __mysql

import os as __os, logger as __log

__app = __flask(__name__)

__app.config['MYSQL_DATABASE_HOST'] = __os.environ['MYSQL_HOST']

__app.config['MYSQL_DATABASE_PORT'] = int(__os.environ['MYSQL_PORT'])

__app.config['MYSQL_DATABASE_USER'] = __os.environ['MYSQL_USR']

__app.config['MYSQL_DATABASE_PASSWORD'] = __os.environ['MYSQL_PWD']

__app.config['MYSQL_DATABASE_DB'] = 'chiayi'

__mysql = __mysql()

__mysql.init_app(__app)

def select(table, columns="*"):
    if isinstance(columns, list):
        for column in columns:
            fields = (fields + ", " if 'fields' in vars() else "") + "`" + column + "`"
    else:
        fields = "`" + columns + "`"
        
    return "SELECT " + fields + " FROM `" + table + "`"

def insert(table, data):
    for key in data.keys():
        fields = (fields + ", " if 'fields' in vars() else "") + "`" + key + "`"

        value = str(data[key])

        values = (values + ", " if 'values' in vars() else "") + "'" + value + "'"

    return "INSERT INTO `" + table + "` (" + fields + ") VALUES (" + values + ")"

def update(table, data):
    for key in data.keys():
        pair = "`" + key + "` = '" + str(data[key]) + "'"

        pairs = (pairs + ", " if 'pairs' in vars() else "") + pair
        
    return "UPDATE `" + table + "` SET " + pairs
    
def delete(table):
    return "DELETE FROM `" + table + "`"
    
def where(query, column, logic, value):
    condition = '`' + column + '` ' + logic + " '" + str(value) + "'"
    
    return query + " WHERE " + condition

def where_in(query, column, values):
    if isinstance(values, list):
        for value in values:
            temp = (temp + ", " if 'temp' in vars() else "") + "'" + value + "'"
    else:
        temp = "'" + values + "'"

    return query + " WHERE `" + column + "` IN (" + temp + ")"

def where_between(query, column, start, end):
    temp = "'" + str(start) + "' AND '" + str(end) + "'"
    
    return query + " WHERE `" + column + "` BETWEEN " + temp
    
def where_like(query, column, like):
    return query + " WHERE `" + column + "` LIKE '" + like + "'"

def where_or(query, column, logic, value):
    condition = '`' + column + '` ' + logic + " '" + str(value) + "'"
    
    return query + " OR " + condition

def where_and(query, column, logic, value):
    condition = '`' + column + '` ' + logic + " '" + str(value) + "'"
    
    return query + " AND " + condition
    
def orderby(query, column, desc=False):
    return query + " ORDER BY `" + column + "` " + ("DESC" if desc else "ASC")

def fetchall(ip, table, query):
    connect = __mysql.connect()
        
    cursor = connect.cursor()
    
    try:
        cursor.execute(query)
        
        rows = cursor.fetchall()
        
        connect.commit()
    except:
        connect.rollback()

        __log.write(ip, "ERROR", query)
        
        return False
        
    tag = str(connect.affected_rows())

    tag = "SELECT " + tag + " data" if "SELECT" in query else tag

    tag = "INSERT " + tag + " data" if "INSERT" in query else tag

    tag = "UPDATE " + tag + " data" if "UPDATE" in query else tag

    tag = "DELETE " + tag + " data" if "DELETE" in query else tag

    __log.write(ip, tag, table, query)

    cursor.close()
    
    connect.close()
          
    return rows if isinstance(rows, tuple) else True
    
def fetchone(ip, table, query):
    connect = __mysql.connect()
        
    cursor = connect.cursor()
    
    try:
        cursor.execute(query)
        
        row = cursor.fetchone()
        
        connect.commit()
    except:
        connect.rollback()
        
        __log.write(ip, "ERROR", query)

        return False
        
    tag = str(connect.affected_rows())

    tag = "SELECT " + tag + " data" if "SELECT" in query else tag

    tag = "INSERT " + tag + " data" if "INSERT" in query else tag

    tag = "UPDATE " + tag + " data" if "UPDATE" in query else tag

    tag = "DELETE " + tag + " data" if "DELETE" in query else tag

    __log.write(ip, tag, table, query)

    cursor.close()
    
    connect.close()
    
    return row if isinstance(row, tuple) else True