import os
import pymysql.cursors

from src.app.database.utility.QueryExceptions import InvalidRequest


def getPyMySqlConnection(useProductionDatabase):
    return pymysql.connect(host = os.getenv('DB_HOST_NAME'),
                           user = os.getenv('MY_SQL_USER'),
                           password = os.getenv('<PASSWORD>'),
                           db = os.getenv('PRODUCTION_DB_NAME'))


def executeSelectRequest(sqlSelectRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    tuples = ()
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlSelectRequest)
            tuples = cur.fetchall()

    except pymysql.Error as e:
        print(e)
    finally:
        dbConnection.close()
        return tuples


def executeInsertRequest(sqlInsertRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    try:
        affectedRowCount = 0
        with dbConnection.cursor() as cur:
            cur.execute(sqlInsertRequest)
            affectedRowCount = cur.rowcount
            if affectedRowCount > 0:
                dbConnection.commit()

    finally:
        dbConnection.close()
        return affectedRowCount


def executeUpdateRequest(sqlUpdateRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    updatedRowCount = 0
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlUpdateRequest)
            updatedRowCount = cur.rowcount
            if updatedRowCount > 0:
                dbConnection.commit()
    except pymysql.Error as e:
        print(e)
    finally:
        dbConnection.close()
        return updatedRowCount


def executeDeleteRequest(sqlDeleteRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    deletedRowCount = 0
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlDeleteRequest)
            deletedRowCount = cur.rowcount

            if deletedRowCount > 0:
                dbConnection.commit()
    finally:
        dbConnection.close()
        return deletedRowCount


def executeProcedureRequest(sqlProcedureRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlProcedureRequest)
            dbConnection.commit()

    except Exception:
        raise InvalidRequest('Could not complete procedure execution.')

    finally:
        dbConnection.close()

import os
import pymysql.cursors

from src.app.database.utility.QueryExceptions import InvalidRequest


def getPyMySqlConnection(useProductionDatabase):
    return pymysql.connect(host = os.getenv('DB_HOST_NAME'),
                           user = os.getenv('MY_SQL_USER'),
                           password = os.getenv('<PASSWORD>'),
                           db = os.getenv('PRODUCTION_DB_NAME'))


def executeSelectRequest(sqlSelectRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    tuples = ()
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlSelectRequest)
            tuples = cur.fetchall()

    except pymysql.Error as e:
        print(e)
    finally:
        dbConnection.close()
        return tuples


def executeInsertRequest(sqlInsertRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    try:
        affectedRowCount = 0
        with dbConnection.cursor() as cur:
            cur.execute(sqlInsertRequest)
            affectedRowCount = cur.rowcount
            if affectedRowCount > 0:
                dbConnection.commit()

    finally:
        dbConnection.close()
        return affectedRowCount


def executeUpdateRequest(sqlUpdateRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    updatedRowCount = 0
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlUpdateRequest)
            updatedRowCount = cur.rowcount
            if updatedRowCount > 0:
                dbConnection.commit()
    except pymysql.Error as e:
        print(e)
    finally:
        dbConnection.close()
        return updatedRowCount


def executeDeleteRequest(sqlDeleteRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    deletedRowCount = 0
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlDeleteRequest)
            deletedRowCount = cur.rowcount

            if deletedRowCount > 0:
                dbConnection.commit()
    finally:
        dbConnection.close()
        return deletedRowCount


def executeProcedureRequest(sqlProcedureRequest, useProductionDatabase = True):
    dbConnection = getPyMySqlConnection(useProductionDatabase)
    try:
        with dbConnection.cursor() as cur:
            cur.execute(sqlProcedureRequest)
            dbConnection.commit()

    except Exception:
        raise InvalidRequest('Could not complete procedure execution.')

    finally:
        dbConnection.close()
