# -*- coding: utf-8 -*-
"""
Created on Wed Nov  8 23:40:39 2023

@author: CHEO001
"""

from sqlalchemy.engine import create_engine
import os
from dotenv import load_dotenv

load_dotenv()

# https://stackoverflow.com/questions/28453545/connecting-to-an-oracle-database-using-sqlalchemy
# https://docs.sqlalchemy.org/en/20/dialects/oracle.html#module-sqlalchemy.dialects.oracle.oracledb
# oracledb requires sqlalchemy 2.0+ "pip install SQLAlchemy --upgrade"
# pip install oracledb required
# current oracle DB config requires thick client mode - download to local drive

# initializes oracle thick client to use due to case insensitive logon enabled
# thick client requires local download of oracle client
# https://www.oracle.com/database/technologies/instant-client/downloads.html
# https://stackoverflow.com/questions/73853023/dpy-3015-password-verifier-type-0x939-is-not-supported-by-python-oracledb-in-th

d=r"C:\instantclient_21_12"
thick_mode = {"lib_dir": d}

# userpwd = "hub_red"

# service = 'DBOPSMA1_TUNNEL_OCI'

# #reads TNSNAMES.ORA file
# engine = create_engine(f"oracle+oracledb://{userpwd}:{userpwd}@{service}", thick_mode=thick_mode)


USER = os.getenv('ORACLEUSER')
PASSWORD= os.getenv('ORACLEPASSWORD')
SERVICE = os.getenv('ORACLESERVICE')

# utilizes TNSNAMES.ORA file to generate connection
def getEngine(user=USER, pw=PASSWORD, service=SERVICE):
    user = user
    pw = pw
    service = service

    #reads TNSNAMES.ORA file
    engine = create_engine(f"oracle+oracledb://{user}:{pw}@{service}", thick_mode=thick_mode)
    print('Opening connection {}'.format(engine.engine))
    
    return engine


def closeEngine(engine):
    print('Closing connection {}'.format(engine.engine))
    engine.dispose()