# This page allows the user to view a list of members
# By default, the output will be a streamlit rendered pandas dataframe
# But there is also an option to render the output in raw HTML so that
# the member's profile URL can be visited by clicking on the
# "Profile" hyperlink that is only available in the raw HTML version.

import psycopg2
import os
import pandas as pd
import streamlit as st

# Obtain username/password saved as environment variables
user = 'project'
pwd = 'project'

# @st.cache prevents the streamlit app from executing redundant processes
# repeatedly that are expensive such as database connections or reading input
@st.cache(allow_output_mutation=True)
def get_query_results():
    """ A function that returns a table of members.
        Until streamlit provides a way to pageinate results,
        maximum of 1000 rows will be returned.
        Output can be either a streamlit rendered dataframe
        or raw HTML version.  Raw HTML version will provide
        a hyperlink that will take you directly to the person's
        company profile page.  This can be used to double-check
        that the profile URL has been correctly generated.
    """

    # Connect to the PostgreSQL database server
    with psycopg2.connect(host='127.0.0.1',
                          port='5433',
                          database='project',
                          user=user,
                          password=pwd) as conn:

        sql = """
        SELECT
            *
        FROM
            public.basic_member_info
        ORDER BY
            last_name
        LIMIT 1000
        """

        # Execute query and return results as a pandas dataframe
        df = pd.read_sql(sql, conn, index_col=None)

        # Define a function to create a "Profile" hyperlink
        def createProfileHref(url: str):
            """ Function to create a new column that converts URL as HTML hyperlink """
    
            value = '<a href="' + url + '"' + "/>Profile</a>"
    
            return value

        # Apply the function we created above and create our new hyperlink column
        df['profile_href'] = df['web_url'].apply(createProfileHref)

        # Change order of dataframe columns
        df = df[['profile_href', 'first_name', 'last_name', 'web_id', 
                 'web_url', 'is_ahm']]

    return df
##
def write():
    """ Writes content to the app """

    st.title("Get Members Data from PostgreSQL")

    # Check to see if checkbox was checked or not (boolean) and will be used to
    # determine if the output should be a streamlit dataframe or raw HTML.
    html = st.checkbox(
        'OPTIONAL: Render output as raw html to access the \"Profile\" hyperlink. ' +
         'Otherwise, just click on Execute botton.',
        False)

    # Define what happens when user clicks on the "Execute" button
    if st.button("Execute"):
        '''
        ### Query results:
        '''
        if html:
            # Render or display the raw HTML version of the dataframe
            st.write(get_query_results().to_html(escape=False, index=False), unsafe_allow_html=True)
        else:
            # Render or display the streamlit dataframe
            st.dataframe(get_query_results())

if __name__ == "__main__":
    write()