# connect to Oracle using oracledb
# !pip install oracledb 

import oracledb

db_user = 'system'
db_connect_string = 'localhost:1521/XEPDB1'
db_pass = 'oracle'

# db_connect_string = 'dbserver:1521/orcl.mydomain.com'
# import getpass
# db_pass = getpass.getpass()


"""
Explanation: How to generate histograms using Oracle SQL
This provides and example of how to generate frequency histograms using the Oracle SQL.
Disambiguation: we refer here to computing histograms of table data, rather than histograms of the columns statistics used by the cost based optimizer.
Dependencies: needs an Oracle client installation and cx_Oracle
Author and contacts: Luca.Canali@cern.ch
Setup and prerequisites
This is how you can setup an Oracle instance for testing using a docker image for oracle-xe
run oracle xe on a container from gvenzl dockerhub repo https://github.com/gvenzl/oci-oracle-xe
docker run -d --name mydb1 -e ORACLE_PASSWORD=oracle -p 1521:1521 gvenzl/oracle-xe:latest # or use :slim
wait till the DB is started, check logs at:
docker logs -f mydb1
oracledb library: This uses oracledb to connect to oracle, so no need to install the Oracle client.
Note: oracledb can also work with the oracle client as cx_Oracle did,
see documentation for details.
Query Oracle from Python with oracledb
oracledb is the next version of cx_Oracle
End of explanation
"""


with oracledb.connect(user=db_user, password=db_pass, dsn=db_connect_string) as ora_conn:

    cursor = ora_conn.cursor()
    
    # use this drop statement if you need to recreate the table
    # cursor.execute("drop table data")

    cursor.execute("begin dbms_random.seed(4242); end;")

    cursor.execute("""
        create table data as 
        select dbms_random.value * 100 random_value 
        from dual connect by level <=100
        """)



"""
Explanation: Create the test table
End of explanation
"""


table_name = "data" # table or temporary view containing the data
value_col = "random_value" # column name on which to compute the histogram
min = -20  # min: minimum value in the histogram
max = 90   # maximum value in the histogram
bins = 11  # number of histogram buckets to compute
step = (max - min) / bins
        

query = f"""
with bucketized as (
  select width_bucket({value_col}, {min}, {max}, {bins}) as bucket
  from {table_name}
),
hist as (
  select bucket, count(*) as cnt
  from bucketized
  group by bucket
),
buckets as (
  select rownum as bucket from dual connect by level <= {bins}
)
select
    bucket, {min} + (bucket - 1/2) * {step} as value,
    nvl(cnt, 0) as count
from hist right outer join buckets using(bucket)
order by bucket
"""

"""
Explanation: Define the query to compute the histogram
End of explanation
"""


import pandas as pd

# query Oracle using ora_conn and put the result into a pandas Dataframe
with oracledb.connect(user=db_user, password=db_pass, dsn=db_connect_string) as ora_conn:
    hist_pandasDF = pd.read_sql(query, con=ora_conn)    

# Decription
#
# BUCKET: the bucket number, range from 1 to bins (included)
# VALUE: midpoint value of the given bucket
# COUNT: number of values in the bucket 
            
hist_pandasDF

# Optionally normalize the event count into a frequency
# dividing by the total number of events
 
hist_pandasDF["FREQUENCY"] = hist_pandasDF["COUNT"] / sum(hist_pandasDF["COUNT"]) 
              
hist_pandasDF

"""
Explanation: Fetch the histogram data into a pandas dataframe
End of explanation
"""


import matplotlib.pyplot as plt 
plt.style.use('seaborn-darkgrid')
plt.rcParams.update({'font.size': 20, 'figure.figsize': [14,10]})

f, ax = plt.subplots()

# histogram data
x = hist_pandasDF["VALUE"]
y = hist_pandasDF["COUNT"]

# bar plot
ax.bar(x, y, width = 3.0, color='red')

ax.set_xlabel("Bucket values")
ax.set_ylabel("Event count")
ax.set_title("Distribution of event counts")

# Label for the resonances spectrum peaks
txt_opts = {'horizontalalignment': 'center',
            'verticalalignment': 'center',
            'transform': ax.transAxes}

plt.show()

import matplotlib.pyplot as plt 
plt.style.use('seaborn-darkgrid')
plt.rcParams.update({'font.size': 20, 'figure.figsize': [14,10]})

f, ax = plt.subplots()

# histogram data
x = hist_pandasDF["VALUE"]
y = hist_pandasDF["FREQUENCY"]

# bar plot
ax.bar(x, y, width = 3.0, color='blue')

ax.set_xlabel("Bucket values")
ax.set_ylabel("Event frequency")
ax.set_title("Distribution of event frequencies")

# Label for the resonances spectrum peaks
txt_opts = {'horizontalalignment': 'center',
            'verticalalignment': 'center',
            'transform': ax.transAxes}

plt.show()



"""
Explanation: Histogram plotting
The first plot is a histogram with the event counts (number of events per bin).
The second plot is a histogram of the events frequencies (number of events per bin normalized by the sum of the events).
End of explanation
"""


