"""Utility functions for Oracle.

This file contains functions for retrieving data from Oracle, specifically the elements database, magnet polynomial
database, additional data, and SCORE.
"""

import os

import cx_Oracle


def connect(wallet, user):
    """Connects to Oracle.

    Args:
        wallet (str): The Oracle wallet, which contains credentials
        user (str): The username of the Oracle database

    Returns:
        A connection object on which one can execute Oracle queries.
    """
    # If on dev, use afs directories
    wallet_dir = '/usr/local/lcls/tools/oracle/wallets'
    if not os.path.isdir(wallet_dir):
        wallet_dir = '/afs/slac/g/lcls/tools/oracle/wallets'
        os.environ['ORACLE_HOME'] = '/afs/slac/g/lcls/package/oracle/product/11.2.0.4/linux-x86_64'

    else:
        os.environ['ORACLE_HOME'] = '/usr/local/lcls/package/oracle/product/11.2.0.4/linux-x86_64'

    os.environ['TNS_ADMIN'] = os.path.join(wallet_dir, wallet)
    os.environ['TWO_TASK'] = user
    con = cx_Oracle.connect('/@' + user)
    return con


def run_query(wallet, user, query):
    """Executes a query on Oracle.

    Args:
        wallet (str): The Oracle wallet, which contains credentials
        user (str): The username of the Oracle database
        query (str): The SQL query.

    Returns:
        A list of the rows returned by Oracle. Each row is a tuple.

    """
    con = connect(wallet, user)
    cur = con.cursor()
    cur.execute(query)
    res = cur.fetchall()
    cur.close()
    con.close()
    return res


def get_score_regions_and_areas():
    """Returns the SCORE regions and areas for all magnets.

    Returns:
        A list containing tuples of the form (Element name, SCORE region, SCORE area).
    """
    wallet = 'epics_mon_user'
    user = 'MCCQA'
    sql_script_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'mgnt_get_score_info.sql')
    with open(sql_script_path) as f:
        query = f.read()

    return run_query(wallet, user, query)
