# import libraries
import streamlit as st
import pandas as pd
import numpy as np
from datetime import date
# import dbconnect
import json
from st_aggrid import GridOptionsBuilder, AgGrid, GridUpdateMode, DataReturnMode, JsCode
import plotly.express as px
import altair as alt

# page layout
st.set_page_config(page_title="Regulatory Dashboard"
                   ,page_icon="🔎"
                   ,layout="wide"
                   ,initial_sidebar_state="expanded"
                   )

#%% Layout


# # %% Database connect
# engine = dbconnect.get_conn('engine')

# #%% Get Jurisdictions
# sqlQry = """ select Id, Title from Jurisdiction"""
# divisions = pd.read_sql(sqlQry, engine)


# #%% Query
# startDate = '2022-01-01'
# endDate = date.today().strftime("%Y-%m-%d")
# sqlQry = f"SET NOCOUNT ON; EXEC [dbo].[AA_Dashboard_SP] 0 ,'{startDate}','{endDate}'" # 0 as Jurisdiction means all


# #%% All data
# dataList = pd.read_sql_query(sqlQry , engine)
# dataList = dataList.replace({np.nan: None})

# dataList.to_csv("dataList.csv")

dataList = pd.read_csv("dataList.csv")

nTotal = dataList.shape[0]
nNotApplicable = dataList[dataList['Applicable'].values==0].shape[0]

dataApplicable = dataList[dataList['Applicable'].values!=0]
dataApplicable = dataApplicable.drop(columns = ['Applicable'])







dataActioned = dataApplicable[dataApplicable['Type'].isnull()==True]
del dataList # Clear dataframe memory
nApplicable = dataApplicable.shape[0]
nUnderAssessment = dataActioned[dataActioned['Type'].values=='Assess'].shape[0]
nClosed = dataActioned[dataActioned['Type'].values=='Closed'].shape[0]


barDataApplicable = dataApplicable['Regulator'].value_counts().to_frame().reset_index(level=0)
barDataApplicable['Actioned'] = 0
barDataActioned = dataActioned['Regulator'].value_counts().to_frame().reset_index(level=0)
for i in range(0,barDataActioned.shape[0]):
    barDataApplicable.loc[barDataApplicable['index'] == barDataActioned['index'][i], 'Actioned'] = barDataActioned['Regulator'].values[i]
del barDataActioned #Clear dataframe memory

barDataApplicable = barDataApplicable.rename(columns={"index": "Regulator", "Regulator": "TotalCount", "Actioned": "TotalActioned"})

# top 20 rowa
d = barDataApplicable.head(20)

# pyramid chart
chart_m = alt.Chart(d).mark_bar().encode(
    x=alt.X('TotalCount',
            title='TotalCount',
            sort='descending',
           ),
    y=alt.Y('Regulator:N', axis=alt.Axis(title='Regions'), sort='descending'),
    color=alt.Color('TotalCount')
)

chart_f = alt.Chart(d).mark_bar().encode(
    x=alt.X('TotalActioned',
            title='TotalActioned',
            sort='ascending',
           ),
    y=alt.Y('Regulator:N', axis=None, sort='descending'),
    color=alt.Color('TotalActioned')
)

# chart_m | chart_f
st.altair_chart(alt.concat(chart_m, chart_f, spacing=2), use_container_width=True)


#%% Numbers Display

col = st.columns(8)
col[1].metric(label="Total Publications", value=nTotal) #, delta=-0.5, delta_color="inverse")
col[2].metric(label="Closed Publications", value=nClosed)
col[3].metric(label="Under Assessment", value=nUnderAssessment)
col[4].metric(label="Not Applicable", value=nNotApplicable)

#%% Cell types and renderers
colPropName = ['field','headerName','width','type']
typeDate = ["dateColumnFilter", "shortDateTimeFormat"]
typeNumeric = ['numericColumn', 'numberColumnFilter']
typeText = ["textColumn"]
jsCodeLink = JsCode("""function(params) { return `<a href=${params.value} target="_blank">Source Link</a>`}""")

#%% Build grid options
builder = GridOptionsBuilder.from_dataframe(dataApplicable)
builder.configure_pagination(paginationAutoPageSize=True) #Add pagination
builder.configure_default_column(min_column_width=100
                                 ,wrapText=True
                                 ,autoHeight=True)

builder.configure_columns(['Id'], width=100, type = typeNumeric)
builder.configure_columns(['Date'], width=110, type = ["customDateTimeFormat"], custom_format_string='dd MMM yyyy')
builder.configure_column('Regulator', width=200, headerName = 'Regulator', type = typeText)
builder.configure_column('PubType',width=250, headerName = 'Publication Type', type = typeText)
builder.configure_column('Title', width=700, type = typeText)
builder.configure_column('Status', width=250, type = typeText)
builder.configure_column('Type', width=200, type = typeText)
builder.configure_column('UserId', width=250, type = typeText)
builder.configure_column("Hyperlink",
                            headerName="Link",
                            cellRenderer= jsCodeLink,
                            width=100)
builder.configure_side_bar() #Add a sidebar
# gb.configure_selection('multiple', use_checkbox=True, groupSelectsChildren="Group checkbox select children") #Enable multi-row selection
gridOptions = builder.build()

#%% Publish grid with options
grid_response = AgGrid(dataApplicable,
    gridOptions=gridOptions,
    # data_return_mode='AS_INPUT',
    # update_mode='MODEL_CHANGED',
    # fit_columns_on_grid_load=True,
    # enable_enterprise_modules=True,
    allow_unsafe_jscode = True,
    height=500,
    reload_data=True,
)

# data = grid_response['data']
# selected = grid_response['selected_rows']
# df = pd.DataFrame(selected) #Pass the selected rows to a new dataframe df