# Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import getpass
import pdvega

# for configuring connection 
from configobj import ConfigObj
import os

%matplotlib inline

# Create a database connection using settings from config file
config='../db/config.ini'

# connection info
conn_info = dict()
if os.path.isfile(config):
    config = ConfigObj(config)
    conn_info["sqluser"] = config['username']
    conn_info["sqlpass"] = config['password']
    conn_info["sqlhost"] = config['host']
    conn_info["sqlport"] = config['port']
    conn_info["dbname"] = config['dbname']
    conn_info["schema_name"] = config['schema_name']
else:
    conn_info["sqluser"] = 'postgres'
    conn_info["sqlpass"] = ''
    conn_info["sqlhost"] = 'localhost'
    conn_info["sqlport"] = 5432
    conn_info["dbname"] = 'eicu'
    conn_info["schema_name"] = 'public,eicu_crd'
    
# Connect to the eICU database
print('Database: {}'.format(conn_info['dbname']))
print('Username: {}'.format(conn_info["sqluser"]))
if conn_info["sqlpass"] == '':
    # try connecting without password, i.e. peer or OS authentication
    try:
        if (conn_info["sqlhost"] == 'localhost') & (conn_info["sqlport"]=='5432'):
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   user=conn_info["sqluser"])            
        else:
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   host=conn_info["sqlhost"],
                                   port=conn_info["sqlport"],
                                   user=conn_info["sqluser"])
    except:
        conn_info["sqlpass"] = getpass.getpass('Password: ')

        con = psycopg2.connect(dbname=conn_info["dbname"],
                               host=conn_info["sqlhost"],
                               port=conn_info["sqlport"],
                               user=conn_info["sqluser"],
                               password=conn_info["sqlpass"])
query_schema = 'set search_path to ' + conn_info['schema_name'] + ';'

"""
Explanation: treatment
The treatment table allows users to document, in a structured format, specific active treatments for the patient. The treatment table can only be populated directly into eCareManager as structured text. Absence of a treatment documented in this table should not be used as evidence a specific treatment was not administered. Data includes patient treatment information such as when the treatment occurred, whether the treatment was active upon patient discharge, and the path of the treatment.
End of explanation
"""


patientunitstayid = 242040

query = query_schema + """
select *
from treatment
where patientunitstayid = {}
order by treatmentoffset
""".format(patientunitstayid)

df = pd.read_sql_query(query, con)
df.head()

plt.figure()

treatments = df['treatmentstring'].unique()

for i, t in enumerate(treatments):
    idx = df['treatmentstring'] == t
    df_plt = df.loc[idx, :]
    plt.plot( df_plt['treatmentoffset'], i*np.ones(df_plt.shape[0]), 'o',
             label=t)
plt.xlabel('Minutes since ICU admission')
plt.ylabel('Treatments provided')

plt.yticks(np.arange(len(treatments)), treatments)

plt.show()

"""
Explanation: Examine a single patient
End of explanation
"""


query = query_schema + """
with t as
(
select distinct patientunitstayid
from treatment
)
select 
  pt.hospitalid
  , count(distinct pt.patientunitstayid) as number_of_patients
  , count(distinct t.patientunitstayid) as number_of_patients_with_tbl
from patient pt
left join t
  on pt.patientunitstayid = t.patientunitstayid
group by pt.hospitalid
""".format(patientunitstayid)

df = pd.read_sql_query(query, con)
df['data completion'] = df['number_of_patients_with_tbl'] / df['number_of_patients'] * 100.0
df.sort_values('number_of_patients_with_tbl', ascending=False, inplace=True)
df.head(n=10)

df[['data completion']].vgplot.hist(bins=10,
                                    var_name='Number of hospitals',
                                    value_name='Percent of patients with data')

"""
Explanation: Above we can see that most of these treatments were documented as being given twice (or perhaps continuously given over the duration - the interpretation is not clear). Only milrinone and cardioversion were given once around 620 minutes after ICU admission.
Hospitals with data available
End of explanation
"""


