pages/3_Saved_Scenarios.py

import streamlit as st
from numerize.numerize import numerize
import io
import pandas as pd
from utilities import (format_numbers,decimal_formater,
                       channel_name_formating,
                       load_local_css,set_header,
                       initialize_data,
                       load_authenticator)
from openpyxl import Workbook
from openpyxl.styles import Alignment,Font,PatternFill
import pickle
import streamlit_authenticator as stauth
import yaml
from yaml import SafeLoader
from classes import class_from_dict
import plotly.graph_objects as go

st.set_page_config(layout='wide')
load_local_css('styles.css')
set_header()
scenarios_to_compare = []
st.title("Saved Scenarios")
# for k, v in st.session_state.items():
#     if k not in ['logout', 'login','config'] and not k.startswith('FormSubmitter'):
#         st.session_state[k] = v
def comparison_scenarios_df():
    
    ## create summary page
    if len(scenarios_to_compare) == 0:
        return
    summary_df_spend = None
    summary_df_prospect = None
    # summary_df_efficiency = None
    #=# print(scenarios_to_download)
    for scenario_name in scenarios_to_compare:
        scenario_dict =  st.session_state['saved_scenarios'][scenario_name]
        _spends = []
        column_names = ['Date']
        _sales = None
        dates = None
        summary_rows_spend = []
        summary_rows_prospects = []
        for channel in scenario_dict['channels']:
            if dates is None:
                dates = channel.get('dates')
                _spends.append(dates)
            if _sales is None:
                _sales = channel.get('modified_sales')
            else:
                _sales += channel.get('modified_sales')
            _spends.append(channel.get('modified_spends') * channel.get('conversion_rate'))
            column_names.append(channel.get('name'))
            
            name_mod = channel_name_formating(channel['name'])
            summary_rows_spend.append([name_mod,
                                channel.get('modified_total_spends') * channel.get('conversion_rate')])
            summary_rows_prospects.append([name_mod,
                                channel.get('modified_total_sales')])

        _spends.append(_sales)
        # column_names.append('NRPU')
        # scenario_df = pd.DataFrame(_spends).T
        # scenario_df.columns = column_names
           
        # summary_rows.append(['Total',
                        # scenario_dict.get('modified_total_spends') ,
                        # scenario_dict.get('modified_total_sales'),
                        # scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
                        # '-',
                        # scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')])
        # columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
        # columns_index = columns_index.append(pd.MultiIndex.from_product([[scenario_name],['Spends','NRPU','ROI','MROI','Spends per NRPU']], names=["first", "second"]))
        columns_index = ['Channel',scenario_name]
        if summary_df_spend is None:
            summary_df_spend = pd.DataFrame(summary_rows_spend, columns = columns_index)
            summary_df_spend = summary_df_spend.set_index('Channel')
        else:
            _df = pd.DataFrame(summary_rows_spend, columns = columns_index)
            _df = _df.set_index('Channel')
            summary_df_spend = summary_df_spend.merge(_df, left_index=True, right_index=True)

        if summary_df_prospect is None:
            summary_df_prospect = pd.DataFrame(summary_rows_prospects, columns = columns_index)
            summary_df_prospect = summary_df_prospect.set_index('Channel')
        else:
            _df = pd.DataFrame(summary_rows_prospects, columns = columns_index)
            _df = _df.set_index('Channel')
            summary_df_prospect = summary_df_prospect.merge(_df, left_index=True, right_index=True)
    st.session_state['disable_download_button'] = False

    efficiency_df = pd.DataFrame(index = summary_df_prospect.index)

    for c in summary_df_spend.columns:
        efficiency_df[c] = (summary_df_prospect[c]/summary_df_prospect[c].sum())/(summary_df_spend[c]/summary_df_spend[c].sum())
        efficiency_df[c] = efficiency_df[c].round(2)
        
    return summary_df_spend,summary_df_prospect,efficiency_df



def plot_comparison_chart(df,metric):
    
    # Create traces for each column
    traces = []
    for column in df.columns:
        traces.append(go.Bar(
            x=df.index,
            y=df[column],
            name=column,
            text=df[column].apply(numerize),  # Adding text for each point
            textposition='outside', 
            hoverinfo='x+y+text',
        ))

    # Create the layout
    layout = go.Layout(
        title='Comparing '+ metric,
        xaxis_title="Channels",
        yaxis_title=metric,
        barmode='group'
    )

    # Create the figure
    fig = go.Figure(data=traces, layout=layout)

    return fig

def create_comparison_plots():
    # comparison_scenarios_df()
    spends_df, prospects_df, efficiency_df = comparison_scenarios_df()
    # st.dataframe(spends_df)
    st.plotly_chart(plot_comparison_chart(spends_df,"Spends"),use_container_width=True)
    st.plotly_chart(plot_comparison_chart(prospects_df,"Contributions"),use_container_width=True)
    st.plotly_chart(plot_comparison_chart(efficiency_df,"Efficiency"),use_container_width=True)
    
def create_scenario_summary(scenario_dict):
    summary_rows = []
    actual_total_spends = scenario_dict.get('actual_total_spends'), 
    modified_total_spends = scenario_dict.get('modified_total_spends'),
    actual_total_sales = scenario_dict.get('actual_total_sales'), 
    modified_total_sales = scenario_dict.get('modified_total_sales')
    # st.write(modified_total_spends[0])
    # st.write(actual_total_spends[0])
    # st.write(modified_total_sales)
    # st.write(actual_total_sales[0])
    # st.write(modified_total_spends[0])
    for channel_dict in scenario_dict['channels']:
        # st.write(channel_dict['name'])
        name_mod = channel_name_formating(channel_dict['name'])
        summary_rows.append([name_mod,
                             channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate'),
                             channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate'),
                             channel_dict.get('actual_total_sales') ,
                             channel_dict.get('modified_total_sales'),
                            #  channel_dict.get('modified_total_sales')/modified_total_spends[0],
                            #  channel_dict.get('modified_total_sales')/modified_total_spends[0]

                            #  1,2
                            (channel_dict.get('actual_total_sales') /actual_total_sales[0])/(channel_dict.get('actual_total_spends') /actual_total_spends[0] ),
                            (channel_dict.get('modified_total_sales') /modified_total_sales )/(channel_dict.get('modified_total_spends') /modified_total_spends[0] )      
                            #   #                       #  channel_dict.get('actual_mroi'), 
                            #  channel_dict.get('modified_mroi'),
                            #  channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('actual_total_sales'),
                            #  channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('modified_total_sales')
                            ])
        
    summary_rows.append(['Total',
                         scenario_dict.get('actual_total_spends'), 
                         scenario_dict.get('modified_total_spends'),
                         scenario_dict.get('actual_total_sales'), 
                         scenario_dict.get('modified_total_sales'),
                         1.0,
                         1.0
                        #  scenario_dict.get('actual_total_sales') / scenario_dict.get('actual_total_spends'),
                        #  scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
                        #  '-',
                        #  '-',
                        #  scenario_dict.get('actual_total_spends') / scenario_dict.get('actual_total_sales'),
                        #  scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')
                        ])
    
    adf = pd.DataFrame(summary_rows)
    # st.write(adf.columns)
    
    adf.columns = ["1","2","3","4","5","6","7"]
    adf.index = adf["1"].to_list() #["1","2","3","4","5","6","7","8","9","10","11","12","13","14"]
    adf.drop(columns= ["1"],inplace= True)
    # columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["",""])
    # columns_index = columns_index.append(pd.MultiIndex.from_product([['Spends','Prospects',"Efficiency"],['Actual','Simulated']], names=["",""]))
    columns_index = pd.MultiIndex.from_product([['Spends','Prospects',"Efficiency"],['Actual','Simulated']], names=["",""])
    adf.columns = columns_index
    return  adf # pd.DataFrame(summary_rows, columns=columns_index)
    

   
def summary_df_to_worksheet(df, ws):
    heading_fill = PatternFill(fill_type='solid',start_color='FFFFFFFF',end_color='FFFFFFFF')

    # Define border style
    border_style = Border(
        left=Side(border_style='thin', color='00000000'),
        right=Side(border_style='thin', color='00000000'),
        top=Side(border_style='thin', color='00000000'),
        bottom=Side(border_style='thin', color='00000000')
    )
    number_format = '0.00'  
    for j,header in enumerate(df.columns.values):
        col = j + 1
        for i in range(1,3):
            ws.cell(row=i, column=j + 1, value=header[i - 1]).font = Font(bold=True, color='00000000')
            ws.cell(row=i,column=j+1).fill = heading_fill
            # ws.cell.border = border_style
        # if  col > 1 and (col - 6)%5==0:    
        #     ws.merge_cells(start_row=1, end_row=1, start_column = col-3, end_column=col)
        #     ws.cell(row=1,column=col).alignment = Alignment(horizontal='center')
        #     # ws.cell.border = border_style
    # Apply borders to all cells, including empty cells
    for row in ws.iter_rows():
        for cell in row:
            cell.border = border_style

    for i,row in enumerate(df.itertuples()):
        for j,value in enumerate(row):
            if j == 0:
                continue
            # elif (j-2)%4 == 0 or (j-3)%4 == 0:
            #     ws.cell(row=i+3, column = j, value=value)
            #     # cell.border = border_style
            #     # .number_format = '$#,##0.0' 
            #     if isinstance(value, (int, float)):
            #         cell.number_format = number_format
            else:
                ws.cell(row=i+3, column = j, value=value)
                # cell.border = border_style
                if isinstance(value, (int, float)):
                    cell.number_format = '$#,##0.0'
                    # cell.number_format = number_format
    # Auto-size columns
    for col in ws.columns:
        max_length = 15
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2)
        ws.column_dimensions[column].width = adjusted_width
   
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
import logging

def scenario_df_to_worksheet(df, ws):
    heading_fill = PatternFill(start_color='FF11B6BD', end_color='FF11B6BD', fill_type='solid')
    
    for j, header in enumerate(df.columns.values):
        cell = ws.cell(row=1, column=j + 1, value=header)
        cell.font = Font(bold=True, color='FF11B6BD')
        cell.fill = heading_fill

    for i, row in enumerate(df.itertuples()):
        for j, value in enumerate(row[1:], start=1):  # Start from index 1 to skip the index column
            try:
                cell = ws.cell(row=i + 2, column=j, value=value)
                if isinstance(value, (int, float)):
                    cell.number_format = '$#,##0.0'
                elif isinstance(value, str):
                    cell.value = value[:32767]
                else:
                    cell.value = str(value)
            except ValueError as e:
                logging.error(f"Error assigning value '{value}' to cell {get_column_letter(j)}{i+2}: {e}")
                cell.value = None  # Assign None to the cell where the error occurred

    return ws





   
def download_scenarios():
    """
    Makes a excel with all saved scenarios and saves it locally
    """
    ## create summary page
    if len(scenarios_to_download) == 0:
        return
    wb = Workbook()
    wb.iso_dates = True
    wb.remove(wb.active)
    st.session_state['xlsx_buffer'] = io.BytesIO()
    summary_df = None
    ## print(scenarios_to_download)
    for scenario_name in scenarios_to_download:
        scenario_dict =  st.session_state['saved_scenarios'][scenario_name]
        _spends = []
        column_names = ['Date']
        _sales = None
        dates = None
        summary_rows = []
        for channel in scenario_dict['channels']:
            if dates is None:
                dates = channel.get('dates')
                _spends.append(dates)
            if _sales is None:
                _sales = channel.get('modified_sales')
            else:
                _sales += channel.get('modified_sales')
            _spends.append(channel.get('modified_spends') * channel.get('conversion_rate'))
            column_names.append(channel.get('name'))
            
            name_mod = channel_name_formating(channel['name'])
            summary_rows.append([name_mod,
                                channel.get('modified_total_spends') * channel.get('conversion_rate') ,
                                channel.get('modified_total_sales'),
                                # channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate'),
                                # channel.get('modified_mroi'),
                                # channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate')
                                ])
        _spends.append(_sales)
        column_names.append('NRPU')
        scenario_df = pd.DataFrame(_spends).T
        scenario_df.columns = column_names
        ## write to sheet
        # ws = wb.create_sheet(scenario_name)
        # scenario_df_to_worksheet(scenario_df, ws)    
        summary_rows.append(['Total',
                        scenario_dict.get('modified_total_spends') ,
                        scenario_dict.get('modified_total_sales'),
                        # scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
                        # '-',
                        # scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')
                        ])
        columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
        columns_index = columns_index.append(pd.MultiIndex.from_product([[scenario_name],['Spends','Prospects',
                                                                                        #   'ROI','MROI','Spends per NRPU'
                                                                                        ]], names=["first", "second"]))
        if summary_df is None:
            summary_df = pd.DataFrame(summary_rows, columns = columns_index)
            summary_df = summary_df.set_index(('','Channel'))
        else:
            _df = pd.DataFrame(summary_rows, columns = columns_index)
            _df = _df.set_index(('','Channel'))
            summary_df = summary_df.merge(_df, left_index=True, right_index=True)
    ws = wb.create_sheet('Summary',0)
    summary_df_to_worksheet(summary_df.reset_index(), ws)
    wb.save(st.session_state['xlsx_buffer'])
    st.session_state['disable_download_button'] = False

def disable_download_button():
    st.session_state['disable_download_button'] =True

def transform(x):
    if x.name == ("",'Channel'):
        return x
    elif x.name[0] == 'Efficiency' or x.name[0] == 'MROI':
        return x.apply(lambda y : y if isinstance(y,str) else decimal_formater(format_numbers(y,include_indicator=False,n_decimals=2),n_decimals=2))
    else:
        return x.apply(lambda y : y if isinstance(y,str) else format_numbers(y))

def delete_scenario():
    if selected_scenario in st.session_state['saved_scenarios']:
        del st.session_state['saved_scenarios'][selected_scenario]
        with open('../saved_scenarios.pkl', 'wb') as f:
            pickle.dump(st.session_state['saved_scenarios'],f)     
            
def load_scenario():
    if selected_scenario in st.session_state['saved_scenarios']:
        st.session_state['scenario'] = class_from_dict(selected_scenario_details)
        


authenticator = st.session_state.get('authenticator')
if authenticator is None:
    authenticator = load_authenticator()

name, authentication_status, username = authenticator.login('Login', 'main')
auth_status = st.session_state.get('authentication_status')

if auth_status == True:
    is_state_initiaized = st.session_state.get('initialized',False)
    if not is_state_initiaized:
        ## print("Scenario page state reloaded")
        initialize_data(target_file = "Overview_data_test_panel@#prospects.xlsx")


    saved_scenarios = st.session_state['saved_scenarios']


    if len(saved_scenarios) ==0:
        st.header('No saved scenarios')
        
    else:
        
        with st.sidebar:
            with st.expander('View Scenario Details'):
                st.markdown("""<hr>""", unsafe_allow_html=True)
                selected_scenario = st.selectbox('Select the scenario',list(saved_scenarios.keys()))
                # selected_scenario = st.radio(
                #     'Pick a scenario to view details',
                #     list(saved_scenarios.keys())
                # )
                st.button('Delete scenario', on_click=delete_scenario)
            with st.expander('Download Scenario'):
                st.markdown("""<hr>""", unsafe_allow_html=True)
                scenarios_to_download = st.multiselect('Select scenarios to download',
                            list(saved_scenarios.keys()))
            
                st.button('Prepare download',on_click=download_scenarios)
                st.download_button(
                        label="Download Scenarios",
                        data=st.session_state['xlsx_buffer'].getvalue(),
                        file_name="scenarios.xlsx",
                        mime="application/vnd.ms-excel",
                        disabled= st.session_state['disable_download_button'],
                        on_click= disable_download_button
                    )
            with st.expander('Compare Scenarios'):
                st.markdown("""<hr>""", unsafe_allow_html=True)
                scenarios_to_compare = st.multiselect('Select scenarios to compare',
                            list(saved_scenarios.keys()))                
                st.button('Compare')
                
            
        column_1, column_2,column_3 = st.columns((6,1,1))
        with column_1:
            st.markdown(f'<span style="font-size:28px"><strong>Selected Scenario:</strong> {selected_scenario}</span>', unsafe_allow_html=True)
            # st.header(f"Selected Scenario: {selected_scenario}")
        # with column_3:
        #     st.write("")
            # st.button('Delete scenario', on_click=delete_scenario)
        # with column_3:
            # st.button('Load Scenario', on_click=load_scenario)
        
        selected_scenario_details = saved_scenarios[selected_scenario]
        
        pd.set_option('display.max_colwidth', 100)
        # st.table(create_scenario_summary(selected_scenario_details))
        # st.table(create_scenario_summary(selected_scenario_details).transform(transform))
        adf = create_scenario_summary(selected_scenario_details).transform(transform)
        # adf1 = adf[('Spends', 'Actual'),
        #     ( 'Spends', 'Simulated'),
        #     ( 'Prospects','Actual'),
        #     ( 'Prospects', 'Simulated')].transform(transform)
        # adf2 = adf[('Efficiency',    'Actual'),
        #     ('Efficiency', 'Simulated')].round(2)
        # st.write(adf.columns)
        # adf = adf.set_index([('',   'Channel')])#, inplace=True)
        # st.table(adf)
        st.markdown(adf.style.set_table_styles(
        [
        #     {
        #     'selector': 'th',
        #     'props': [('background-color', '#1167bd')]
        # },
            # {
            # 'selector' : 'tr:nth-child(even)',
            # 'props' : [('background-color', '#11B6BD')]
            # }
            ]).to_html(),unsafe_allow_html=True)
        st.markdown("<br><br>", unsafe_allow_html=True)
        
    with st.expander('Scenario comparison'):
            st.header("Scenario comparison")
            if len(scenarios_to_compare)== 0:
                st.write("")
            else:
                create_comparison_plots()
        
elif auth_status == False:
    st.error('Username/Password is incorrect')
    
if auth_status != True:
    try:
        username_forgot_pw, email_forgot_password, random_password = authenticator.forgot_password('Forgot password')
        if username_forgot_pw:
            st.success('New password sent securely')
            # Random password to be transferred to user securely
        elif username_forgot_pw == False:
            st.error('Username not found')
    except Exception as e:
        st.error(e)





# create_comparison_plots()