# -*- coding: utf-8 -*-
"""
Created on Fri Jan 19 12:49:10 2024

@author: CHEO001
"""
import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
# import pandas as pd
# import mysql.connector

# may be required to work as a dialect for sqlalchemy: (need to confirm 2/1/24)
#import pymysql.cursors

import pandas as pd


load_dotenv()

msUser = os.getenv('MYSQLUSER')
msPswd = os.getenv('MYSQLORCHPW')

# https://docs.sqlalchemy.org/en/20/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnector
# pip install PyMySQL

hostlist = {'concepts':'dev-art-relations-db.dev.theorchard.io:3306'
            ,'build':'dev-art-relations-db.dev.theorchard.io:3306'
            ,'dev':'dev-art-relations-db.dev.theorchard.io:3306'
            ,'qa':'qa.db.qaorch.com:3306'
            ,'reportsar02':'reportsar02.theorchard.com:3306'}

# use with pd.read_sql
def getEngine(host,database):
    engine = create_engine(
    'mysql+pymysql://{user}:{password}@{host}/?database={database}'.format(
        user = msUser,
        password = msPswd,
        host = host,
        database = database
    )
)
    return engine

# sql = '''select * from `ROLLBACK-INT-1686-Label-70073-Serhii-Voronov-set-subaccount`;'''
# database = 'art_relations'

# engine = getEngine(hostlist['concepts'],database)
# connection = engine.connect()

# df = pd.read_sql(sql, con=connection)

def msDF(SQLquery, env='qa', database='art_relations'):
    # adds exception catch for connection or other generic errors
    try:    
        df = pd.read_sql(SQLquery, getEngine(hostlist[env], database))
        return df
    
    except Exception as e:
        print(f'Error occurred - {e}')
        return None