import pypyodbc as odbc # MS SQL Server

import os as __os, logger as __log

__drive = "DRIVER={FreeTDS};"

__server = "SERVER=" + __os.environ['MSSQL_HOST'] + "," + __os.environ['MSSQL_PORT'] + ";"

__database = "DATABASE=" + __os.environ['MSSQL_DB'] + ';'

__uid = "UID=" + __os.environ['MSSQL_USR'] + ";"

__pwd = "PWD=" + __os.environ['MSSQL_PWD'] + ";"

__version = "TDS_Version=" + __os.environ['MSSQL_TDS'] + ";"

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
    # 更新特定欄位的內容         `" + table + "` SET " + data_set
    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:
            _in = (_in + ", " if '_in' in vars() else "") + "'" + value + "'"
    else:
        _in = "'" + values + "'"
    # 設定查詢或更新符合條件的資料列
    return query + " WHERE " + column + " IN (" + _in + ")"

def where_between(query, column, start, end):
    _between = "'" + str(start) + "' AND '" + str(end) + "'"
    
    return query + " WHERE " + column + " BETWEEN " + _between
    
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 = odbc.connect(__drive + __server + __database + __uid + __pwd + __version)
        
    cursor = connect.cursor()
    
    try:
        cursor.execute(query)
        
        rows = cursor.fetchall()
        
        connect.commit()
    except:
        connect.rollback()

        __log.write(ip, "ERROR", query)
        
        return False
        
    tag = len(rows) if isinstance(row, tuple) else 0

    tag = "SELECT " + str(tag) + " data." if "SELECT" in query else tag

    tag = "INSERT " + str(tag) + " data." if "INSERT" in query else tag

    tag = "UPDATE " + str(tag) + " data." if "UPDATE" in query else tag

    tag = "DELETE " + str(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(query):
    connect = odbc.connect(__drive + __server + __database + __uid + __pwd + __version)
        
    cursor = connect.cursor()
    
    try:
        cursor.execute(query)
        
        row = cursor.fetchone() if "SELECT" in query else True
        
        connect.commit()
    except:
        connect.rollback()
        
        __log.write(ip, "ERROR", query)
        
        return False
        
    tag = len(row) if isinstance(row, tuple) else 0

    tag = "SELECT " + str(tag) + " data." if "SELECT" in query else tag

    tag = "INSERT " + str(tag) + " data." if "INSERT" in query else tag

    tag = "UPDATE " + str(tag) + " data." if "UPDATE" in query else tag

    tag = "DELETE " + str(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