# 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: vitalAperiodic
The vitalAperiodic table provides invasive vital sign data which is interfaced into eCareManager at irregular intervals. Unlike most tables in eICU-CRD, vitalAperiodic does not use an entity-attribute-value model, but rather has an individual column to capture each data element. Columns available include:

Blood pressures: nonInvasiveSystolic, nonInvasiveDiastolic, nonInvasiveMean
Cardiac output measures: cardiacOutput, cardiacInput
Systemic circulation measures: svr, svri, pvr, pvri
Pulmonary pressures: pulmonary artery occlusion pressure (paop)
End of explanation
"""


patientunitstayid = 145467

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

df = pd.read_sql_query(query, con)
df.set_index('observationoffset', inplace=True)
df.sort_index(inplace=True)
df.head()

# list of columns to plot
vitals = ['noninvasivesystolic', 'noninvasivediastolic', 'noninvasivemean',
          #'paop',
          'cardiacoutput', 'cardiacinput',
          'pvr', 'pvri']
# we exclude  'svr', 'svri' from the plot as their scale is too high
# we exclude 'paop' as it's all none
df[vitals].vgplot.line()

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


query = query_schema + """
with t as
(
select distinct patientunitstayid
from vitalaperiodic
)
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: Hospitals with data available
End of explanation
"""


