pages/9_Scenario_Planner.py

import streamlit as st
from numerize.numerize import numerize
import numpy as np
from functools import partial
from collections import OrderedDict
from plotly.subplots import make_subplots
import plotly.graph_objects as go
from utilities import (
    format_numbers,
    load_local_css,
    set_header,
    initialize_data,
    load_authenticator,
    send_email,
    channel_name_formating,
)
from classes import class_from_dict, class_to_dict
import pickle
import streamlit_authenticator as stauth
import yaml
from yaml import SafeLoader
import re
import pandas as pd
import plotly.express as px
import logging
from utilities import update_db
import sqlite3


st.set_page_config(layout="wide")
load_local_css("styles.css")
set_header()

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
# ======================================================== #
# ======================= Functions ====================== #
# ======================================================== #


def optimize(key, status_placeholder):
    """
    Optimize the spends for the sales
    """

    channel_list = [
        key
        for key, value in st.session_state["optimization_channels"].items()
        if value
    ]

    if len(channel_list) > 0:
        scenario = st.session_state["scenario"]
        if key.lower() == "media spends":
            with status_placeholder:
                with st.spinner("Optimizing"):
                    result = st.session_state["scenario"].optimize(
                        st.session_state["total_spends_change"], channel_list
                    )
        # elif key.lower() == "revenue":
        else:
            with status_placeholder:
                with st.spinner("Optimizing"):

                    result = st.session_state["scenario"].optimize_spends(
                        st.session_state["total_sales_change"], channel_list
                    )
        for channel_name, modified_spends in result:

            st.session_state[channel_name] = numerize(
                modified_spends
                * scenario.channels[channel_name].conversion_rate,
                1,
            )
            prev_spends = (
                st.session_state["scenario"]
                .channels[channel_name]
                .actual_total_spends
            )
            st.session_state[f"{channel_name}_change"] = round(
                100 * (modified_spends - prev_spends) / prev_spends, 2
            )


def save_scenario(scenario_name):
    """
    Save the current scenario with the mentioned name in the session state

    Parameters
    ----------
    scenario_name
        Name of the scenario to be saved
    """
    if "saved_scenarios" not in st.session_state:
        st.session_state = OrderedDict()

    # st.session_state['saved_scenarios'][scenario_name] = st.session_state['scenario'].save()
    st.session_state["saved_scenarios"][scenario_name] = class_to_dict(
        st.session_state["scenario"]
    )
    st.session_state["scenario_input"] = ""
    # print(type(st.session_state['saved_scenarios']))
    with open("../saved_scenarios.pkl", "wb") as f:
        pickle.dump(st.session_state["saved_scenarios"], f)


def update_sales_abs_slider():
    actual_sales = st.session_state["scenario"].actual_total_sales
    if validate_input(st.session_state["total_sales_change_abs_slider"]):
        modified_sales = extract_number_for_string(
            st.session_state["total_sales_change_abs_slider"]
        )
        st.session_state["total_sales_change"] = round(
            ((modified_sales / actual_sales) - 1) * 100
        )
        st.session_state["total_sales_change_abs"] = numerize(
            modified_sales, 1
        )

        st.session_state["project_dct"]["scenario_planner"][
            "total_sales_change"
        ] = st.session_state.total_sales_change


def update_sales_abs():
    actual_sales = st.session_state["scenario"].actual_total_sales
    if validate_input(st.session_state["total_sales_change_abs"]):
        modified_sales = extract_number_for_string(
            st.session_state["total_sales_change_abs"]
        )
        st.session_state["total_sales_change"] = round(
            ((modified_sales / actual_sales) - 1) * 100
        )
        st.session_state["total_sales_change_abs_slider"] = numerize(
            modified_sales, 1
        )


def update_sales():
    # print("DEBUG: running update_sales")
    # st.session_state["project_dct"]["scenario_planner"][
    #     "total_sales_change"
    # ] = st.session_state.total_sales_change
    # st.session_state["total_spends_change"] = st.session_state[
    #     "total_sales_change"
    # ]

    st.session_state["total_sales_change_abs"] = numerize(
        (1 + st.session_state["total_sales_change"] / 100)
        * st.session_state["scenario"].actual_total_sales,
        1,
    )
    st.session_state["total_sales_change_abs_slider"] = numerize(
        (1 + st.session_state["total_sales_change"] / 100)
        * st.session_state["scenario"].actual_total_sales,
        1,
    )
    # update_spends()


def update_all_spends_abs_slider():
    actual_spends = st.session_state["scenario"].actual_total_spends
    if validate_input(st.session_state["total_spends_change_abs_slider"]):
        modified_spends = extract_number_for_string(
            st.session_state["total_spends_change_abs_slider"]
        )
        st.session_state["total_spends_change"] = round(
            ((modified_spends / actual_spends) - 1) * 100
        )
        st.session_state["total_spends_change_abs"] = numerize(
            modified_spends, 1
        )

        st.session_state["project_dct"]["scenario_planner"][
            "total_spends_change"
        ] = st.session_state.total_spends_change

        update_all_spends()


# def update_all_spends_abs_slider():
#     actual_spends = _scenario.actual_total_spends
#     if validate_input(st.session_state["total_spends_change_abs_slider"]):
#         print("#" * 100)
#         print(st.session_state["total_spends_change_abs_slider"])
#         print("#" * 100)

#         modified_spends = extract_number_for_string(
#             st.session_state["total_spends_change_abs_slider"]
#         )
#         st.session_state["total_spends_change"] = (
#             (modified_spends / actual_spends) - 1
#         ) * 100
#         st.session_state["total_spends_change_abs"] = st.session_state[
#             "total_spends_change_abs_slider"
#         ]

#         update_all_spends()


def update_all_spends_abs():
    print("DEBUG: ", "inside update_all_spends_abs")
    # print(st.session_state["total_spends_change_abs_slider_options"])

    actual_spends = st.session_state["scenario"].actual_total_spends
    if validate_input(st.session_state["total_spends_change_abs"]):
        modified_spends = extract_number_for_string(
            st.session_state["total_spends_change_abs"]
        )
        st.session_state["total_spends_change"] = (
            (modified_spends / actual_spends) - 1
        ) * 100
        st.session_state["total_spends_change_abs_slider"] = numerize(
            extract_number_for_string(
                st.session_state["total_spends_change_abs"]
            ),
            1,
        )

        st.session_state["project_dct"]["scenario_planner"][
            "total_spends_change"
        ] = st.session_state.total_spends_change

        # print(
        #     "DEBUG UPDATE_ALL_SPENDS_ABS: ",
        #     st.session_state["total_spends_change"],
        # )
        update_all_spends()


def update_spends():
    print("update_spends")
    st.session_state["total_spends_change_abs"] = numerize(
        (1 + st.session_state["total_spends_change"] / 100)
        * st.session_state["scenario"].actual_total_spends,
        1,
    )
    st.session_state["total_spends_change_abs_slider"] = numerize(
        (1 + st.session_state["total_spends_change"] / 100)
        * st.session_state["scenario"].actual_total_spends,
        1,
    )

    st.session_state["project_dct"]["scenario_planner"][
        "total_spends_change"
    ] = st.session_state.total_spends_change

    update_all_spends()


def update_all_spends():
    """
    Updates spends for all the channels with the given overall spends change
    """
    percent_change = st.session_state["total_spends_change"]
    print("runs update_all")
    for channel_name in list(
        st.session_state["project_dct"]["scenario_planner"][
            unique_key
        ].channels.keys()
    ):
        st.session_state[f"{channel_name}_percent"] = percent_change
        channel = st.session_state["scenario"].channels[channel_name]
        current_spends = channel.actual_total_spends
        modified_spends = (1 + percent_change / 100) * current_spends
        st.session_state["scenario"].update(channel_name, modified_spends)
        st.session_state[channel_name] = numerize(
            modified_spends * channel.conversion_rate, 1
        )
        st.session_state[f"{channel_name}_change"] = percent_change


def extract_number_for_string(string_input):
    string_input = string_input.upper()
    if string_input.endswith("K"):
        return float(string_input[:-1]) * 10**3
    elif string_input.endswith("M"):
        return float(string_input[:-1]) * 10**6
    elif string_input.endswith("B"):
        return float(string_input[:-1]) * 10**9


def validate_input(string_input):
    pattern = r"\d+\.?\d*[K|M|B]$"
    match = re.match(pattern, string_input)
    if match is None:
        return False
    return True


def update_data_by_percent(channel_name):
    prev_spends = (
        st.session_state["scenario"].channels[channel_name].actual_total_spends
        * st.session_state["scenario"].channels[channel_name].conversion_rate
    )
    modified_spends = prev_spends * (
        1 + st.session_state[f"{channel_name}_percent"] / 100
    )

    st.session_state[channel_name] = numerize(modified_spends, 1)

    st.session_state["scenario"].update(
        channel_name,
        modified_spends
        / st.session_state["scenario"].channels[channel_name].conversion_rate,
    )


def update_data(channel_name):
    """
    Updates the spends for the given channel
    """
    print("tuns update_Data")
    if validate_input(st.session_state[channel_name]):
        modified_spends = extract_number_for_string(
            st.session_state[channel_name]
        )

        prev_spends = (
            st.session_state["scenario"]
            .channels[channel_name]
            .actual_total_spends
            * st.session_state["scenario"]
            .channels[channel_name]
            .conversion_rate
        )
        st.session_state[f"{channel_name}_percent"] = round(
            100 * (modified_spends - prev_spends) / prev_spends, 2
        )
        st.session_state["scenario"].update(
            channel_name,
            modified_spends
            / st.session_state["scenario"]
            .channels[channel_name]
            .conversion_rate,
        )
    # st.session_state['scenario'].update(channel_name, modified_spends)
    # else:
    #     try:
    #         modified_spends = float(st.session_state[channel_name])
    #         prev_spends = st.session_state['scenario'].channels[channel_name].actual_total_spends * st.session_state['scenario'].channels[channel_name].conversion_rate
    #         st.session_state[f'{channel_name}_change'] = round(100*(modified_spends - prev_spends) / prev_spends,2)
    #         st.session_state['scenario'].update(channel_name, modified_spends/st.session_state['scenario'].channels[channel_name].conversion_rate)
    #         st.session_state[f'{channel_name}'] = numerize(modified_spends,1)
    #     except ValueError:
    #         st.write('Invalid input')


def select_channel_for_optimization(channel_name):
    """
    Marks the given channel for optimization
    """
    st.session_state["optimization_channels"][channel_name] = st.session_state[
        f"{channel_name}_selected"
    ]


def select_all_channels_for_optimization():
    """
    Marks all the channel for optimization
    """
    # print(
    #     "DEBUG: select_all_channels_for_opt",
    #     st.session_state["optimze_all_channels"],
    # )

    for channel_name in st.session_state["optimization_channels"].keys():
        st.session_state[f"{channel_name}_selected"] = st.session_state[
            "optimze_all_channels"
        ]
        st.session_state["optimization_channels"][channel_name] = (
            st.session_state["optimze_all_channels"]
        )
    from pprint import pprint


def update_penalty():
    """
    Updates the penalty flag for sales calculation
    """
    st.session_state["scenario"].update_penalty(
        st.session_state["apply_penalty"]
    )


def reset_optimization():
    print("DEBUG: ", "Running reset_optimization")
    for channel_name in list(
        st.session_state["project_dct"]["scenario_planner"][
            unique_key
        ].channels.keys()
    ):
        st.session_state[f"{channel_name}_selected"] = False
        # st.session_state[f"{channel_name}_change"] = 0
    st.session_state["optimze_all_channels"] = False
    st.session_state["initialized"] = False
    del st.session_state["total_sales_change_abs_slider"]
    del st.session_state["total_sales_change_abs"]
    del st.session_state["total_sales_change"]


def reset_scenario():
    print("[DEBUG]: reset_scenario")
    # def reset_scenario(panel_selected, file_selected, updated_rcs):
    # #print(st.session_state['default_scenario_dict'])
    # st.session_state['scenario']  = class_from_dict(st.session_state['default_scenario_dict'])
    # for channel in st.session_state['scenario'].channels.values():
    #     st.session_state[channel.name] = float(channel.actual_total_spends * channel.conversion_rate)
    for channel_name in list(
        st.session_state["project_dct"]["scenario_planner"][
            unique_key
        ].channels.keys()
    ):
        st.session_state[f"{channel_name}_selected"] = False
        # st.session_state[f"{channel_name}_change"] = 0
    st.session_state["optimze_all_channels"] = False
    st.session_state["initialized"] = False

    del st.session_state["optimization_channels"]
    panel_selected = st.session_state.get("panel_selected", 0)
    file_selected = st.session_state["file_selected"]
    update_rcs = st.session_state.get("update_rcs", None)

    # print(f"## [DEBUG] [SCENARIO PLANNER][RESET SCENARIO]: {}")
    del st.session_state["project_dct"]["scenario_planner"][
        f"{st.session_state['metric_selected']}-{st.session_state['panel_selected']}"
    ]
    del st.session_state["total_sales_change_abs_slider"]
    del st.session_state["total_sales_change_abs"]
    del st.session_state["total_sales_change"]
    # if panel_selected == "Aggregated":
    #     initialize_data(
    #         panel=panel_selected,
    #         target_file=file_selected,
    #         updated_rcs=updated_rcs,
    #         metrics=metrics_selected,
    #     )
    #     panel = None
    # else:
    #     initialize_data(
    #         panel=panel_selected,
    #         target_file=file_selected,
    #         updated_rcs=updated_rcs,
    #         metrics=metrics_selected,
    #     )
    # st.session_state["total_spends_change"] = 0
    # update_all_spends()


def format_number(num):
    if num >= 1_000_000:
        return f"{num / 1_000_000:.2f}M"
    elif num >= 1_000:
        return f"{num / 1_000:.0f}K"
    else:
        return f"{num:.2f}"


def summary_plot(data, x, y, title, text_column):
    fig = px.bar(
        data,
        x=x,
        y=y,
        orientation="h",
        title=title,
        text=text_column,
        color="Channel_name",
    )

    # Convert text_column to numeric values
    data[text_column] = pd.to_numeric(data[text_column], errors="coerce")

    # Update the format of the displayed text based on magnitude
    fig.update_traces(
        texttemplate="%{text:.2s}",
        textposition="outside",
        hovertemplate="%{x:.2s}",
    )

    fig.update_layout(
        xaxis_title=x, yaxis_title="Channel Name", showlegend=False
    )
    return fig


def s_curve(x, K, b, a, x0):
    return K / (1 + b * np.exp(-a * (x - x0)))


def find_segment_value(x, roi, mroi):
    start_value = x[0]
    end_value = x[len(x) - 1]

    # Condition for green region: Both MROI and ROI > 1
    green_condition = (roi > 1) & (mroi > 1)
    left_indices = np.where(green_condition)[0]
    left_value = x[left_indices[0]] if left_indices.size > 0 else x[0]

    right_indices = np.where(green_condition)[0]
    right_value = x[right_indices[-1]] if right_indices.size > 0 else x[0]

    return start_value, end_value, left_value, right_value


def calculate_rgba(
    start_value, end_value, left_value, right_value, current_channel_spends
):
    # Initialize alpha to None for clarity
    alpha = None

    # Determine the color and calculate relative_position and alpha based on the point's position
    if start_value <= current_channel_spends <= left_value:
        color = "yellow"
        relative_position = (current_channel_spends - start_value) / (
            left_value - start_value
        )
        alpha = 0.8 - (
            0.6 * relative_position
        )  # Alpha decreases from start to end

    elif left_value < current_channel_spends <= right_value:
        color = "green"
        relative_position = (current_channel_spends - left_value) / (
            right_value - left_value
        )
        alpha = 0.8 - (
            0.6 * relative_position
        )  # Alpha decreases from start to end

    elif right_value < current_channel_spends <= end_value:
        color = "red"
        relative_position = (current_channel_spends - right_value) / (
            end_value - right_value
        )
        alpha = 0.2 + (
            0.6 * relative_position
        )  # Alpha increases from start to end

    else:
        # Default case, if the spends are outside the defined ranges
        return "rgba(136, 136, 136, 0.5)"  # Grey for values outside the range

    # Ensure alpha is within the intended range in case of any calculation overshoot
    alpha = max(0.2, min(alpha, 0.8))

    # Define color codes for RGBA
    color_codes = {
        "yellow": "255, 255, 0",  # RGB for yellow
        "green": "0, 128, 0",  # RGB for green
        "red": "255, 0, 0",  # RGB for red
    }

    rgba = f"rgba({color_codes[color]}, {alpha})"
    return rgba


def debug_temp(x_test, power, K, b, a, x0):
    print("*" * 100)
    # Calculate the count of bins
    count_lower_bin = sum(1 for x in x_test if x <= 2524)
    count_center_bin = sum(1 for x in x_test if x > 2524 and x <= 3377)
    count_ = sum(1 for x in x_test if x > 3377)

    print(
        f"""
            lower : {count_lower_bin}
            center : {count_center_bin}
            upper : {count_}
          """
    )


# @st.cache
def plot_response_curves():
    cols = 4
    rows = (
        len(channels_list) // cols
        if len(channels_list) % cols == 0
        else len(channels_list) // cols + 1
    )
    rcs = st.session_state["rcs"]
    shapes = []
    fig = make_subplots(rows=rows, cols=cols, subplot_titles=channels_list)
    for i in range(0, len(channels_list)):
        col = channels_list[i]
        x_actual = st.session_state["scenario"].channels[col].actual_spends
        # x_modified = st.session_state["scenario"].channels[col].modified_spends

        power = np.ceil(np.log(x_actual.max()) / np.log(10)) - 3

        K = rcs[col]["K"]
        b = rcs[col]["b"]
        a = rcs[col]["a"]
        x0 = rcs[col]["x0"]

        x_plot = np.linspace(0, 5 * x_actual.sum(), 50)

        x, y, marginal_roi = [], [], []
        for x_p in x_plot:
            x.append(x_p * x_actual / x_actual.sum())

        for index in range(len(x_plot)):
            y.append(s_curve(x[index] / 10**power, K, b, a, x0))

        for index in range(len(x_plot)):
            marginal_roi.append(
                a
                * y[index]
                * (1 - y[index] / np.maximum(K, np.finfo(float).eps))
            )

        x = (
            np.sum(x, axis=1)
            * st.session_state["scenario"].channels[col].conversion_rate
        )
        y = np.sum(y, axis=1)
        marginal_roi = (
            np.average(marginal_roi, axis=1)
            / st.session_state["scenario"].channels[col].conversion_rate
        )

        roi = y / np.maximum(x, np.finfo(float).eps)

        fig.add_trace(
            go.Scatter(
                x=x,
                y=y,
                name=col,
                customdata=np.stack((roi, marginal_roi), axis=-1),
                hovertemplate="Spend:%{x:$.2s}<br>Sale:%{y:$.2s}<br>ROI:%{customdata[0]:.3f}<br>MROI:%{customdata[1]:.3f}",
                line=dict(color="blue"),
            ),
            row=1 + (i) // cols,
            col=i % cols + 1,
        )

        x_optimal = (
            st.session_state["scenario"].channels[col].modified_total_spends
            * st.session_state["scenario"].channels[col].conversion_rate
        )
        y_optimal = (
            st.session_state["scenario"].channels[col].modified_total_sales
        )

        # if col == "Paid_social_others":
        #     debug_temp(x_optimal * x_actual / x_actual.sum(), power, K, b, a, x0)

        fig.add_trace(
            go.Scatter(
                x=[x_optimal],
                y=[y_optimal],
                name=col,
                legendgroup=col,
                showlegend=False,
                marker=dict(color=["black"]),
            ),
            row=1 + (i) // cols,
            col=i % cols + 1,
        )

        shapes.append(
            go.layout.Shape(
                type="line",
                x0=0,
                y0=y_optimal,
                x1=x_optimal,
                y1=y_optimal,
                line_width=1,
                line_dash="dash",
                line_color="black",
                xref=f"x{i+1}",
                yref=f"y{i+1}",
            )
        )

        shapes.append(
            go.layout.Shape(
                type="line",
                x0=x_optimal,
                y0=0,
                x1=x_optimal,
                y1=y_optimal,
                line_width=1,
                line_dash="dash",
                line_color="black",
                xref=f"x{i+1}",
                yref=f"y{i+1}",
            )
        )

        start_value, end_value, left_value, right_value = find_segment_value(
            x,
            roi,
            marginal_roi,
        )

        # Adding background colors
        y_max = y.max() * 1.3  # 30% extra space above the max

        # Yellow region
        shapes.append(
            go.layout.Shape(
                type="rect",
                x0=start_value,
                y0=0,
                x1=left_value,
                y1=y_max,
                line=dict(width=0),
                fillcolor="rgba(255, 255, 0, 0.3)",
                layer="below",
                xref=f"x{i+1}",
                yref=f"y{i+1}",
            )
        )

        # Green region
        shapes.append(
            go.layout.Shape(
                type="rect",
                x0=left_value,
                y0=0,
                x1=right_value,
                y1=y_max,
                line=dict(width=0),
                fillcolor="rgba(0, 255, 0, 0.3)",
                layer="below",
                xref=f"x{i+1}",
                yref=f"y{i+1}",
            )
        )

        # Red region
        shapes.append(
            go.layout.Shape(
                type="rect",
                x0=right_value,
                y0=0,
                x1=end_value,
                y1=y_max,
                line=dict(width=0),
                fillcolor="rgba(255, 0, 0, 0.3)",
                layer="below",
                xref=f"x{i+1}",
                yref=f"y{i+1}",
            )
        )

    fig.update_layout(
        # height=1000,
        # width=1000,
        title_text=f"Response Curves (X: Spends Vs Y: {target})",
        showlegend=False,
        shapes=shapes,
    )
    fig.update_annotations(font_size=10)
    # fig.update_xaxes(title="Spends")
    # fig.update_yaxes(title=target)
    fig.update_yaxes(
        gridcolor="rgba(136, 136, 136, 0.5)", gridwidth=0.5, griddash="dash"
    )

    return fig


# ======================================================== #
# ==================== HTML Components =================== #
# ======================================================== #


def generate_spending_header(heading):
    return st.markdown(
        f"""<h2 class="spends-header">{heading}</h2>""", unsafe_allow_html=True
    )


def save_checkpoint():
    project_dct_path = os.path.join(
        st.session_state["project_path"], "project_dct.pkl"
    )

    try:
        pickle.dumps(st.session_state["project_dct"])
        with open(project_dct_path, "wb") as f:
            pickle.dump(st.session_state["project_dct"], f)
    except Exception:
        # with warning_placeholder:
        st.toast("Unknown Issue, please reload the page.")


def reset_checkpoint():
    st.session_state["project_dct"]["scenario_planner"] = {}
    save_checkpoint()


# ======================================================== #
# =================== Session variables ================== #
# ======================================================== #

with open("config.yaml") as file:
    config = yaml.load(file, Loader=SafeLoader)
    st.session_state["config"] = config

authenticator = stauth.Authenticate(
    config["credentials"],
    config["cookie"]["name"],
    config["cookie"]["key"],
    config["cookie"]["expiry_days"],
    config["preauthorized"],
)
st.session_state["authenticator"] = authenticator
name, authentication_status, username = authenticator.login("Login", "main")
auth_status = st.session_state.get("authentication_status")

import os
import glob


def get_excel_names(directory):
    # Create a list to hold the final parts of the filenames
    last_portions = []

    # Patterns to match Excel files (.xlsx and .xls) that contain @#
    patterns = [
        os.path.join(directory, "*@#*.xlsx"),
        os.path.join(directory, "*@#*.xls"),
    ]

    # Process each pattern
    for pattern in patterns:
        files = glob.glob(pattern)

        # Extracting the last portion after @# for each file
        for file in files:
            base_name = os.path.basename(file)
            last_portion = base_name.split("@#")[-1]
            last_portion = last_portion.replace(".xlsx", "").replace(
                ".xls", ""
            )  # Removing extensions
            last_portions.append(last_portion)

    return last_portions


def name_formating(channel_name):
    # Replace underscores with spaces
    name_mod = channel_name.replace("_", " ")

    # Capitalize the first letter of each word
    name_mod = name_mod.title()

    return name_mod


@st.cache_resource(show_spinner=False)
def panel_fetch(file_selected):
    raw_data_mmm_df = pd.read_excel(file_selected, sheet_name="RAW DATA MMM")

    if "Panel" in raw_data_mmm_df.columns:
        panel = list(set(raw_data_mmm_df["Panel"]))
    else:
        raw_data_mmm_df = None
        panel = None

    return panel


if auth_status is True:
    authenticator.logout("Logout", "main")

    if "project_dct" not in st.session_state:
        st.error("Please load a project from home")
        st.stop()

    database_file = r"DB\User.db"

    conn = sqlite3.connect(
        database_file, check_same_thread=False
    )  # connection with sql db
    c = conn.cursor()

    with st.sidebar:
        st.button("Save checkpoint", on_click=save_checkpoint)
        st.button("Reset Checkpoint", on_click=reset_checkpoint)

    warning_placeholder = st.empty()
    
    st.header("Scenario Planner")

    st.markdown("**Simulation**")

    # st.subheader("Simulation")
    col1, col2 = st.columns([1, 1])

    # Get metric and panel from last saved state
    if "last_saved_metric" not in st.session_state:
        st.session_state["last_saved_metric"] = st.session_state[
            "project_dct"
        ]["scenario_planner"].get("metric_selected", 0)
        # st.session_state["last_saved_metric"] = st.session_state[
        #     "project_dct"
        # ]["scenario_planner"].get("metric_selected", 0)

    if "last_saved_panel" not in st.session_state:
        st.session_state["last_saved_panel"] = st.session_state["project_dct"][
            "scenario_planner"
        ].get("panel_selected", 0)
        # st.session_state["last_saved_panel"] = st.session_state["project_dct"][
        #     "scenario_planner"
        # ].get("panel_selected", 0)

    # Response Metrics
    directory = "metrics_level_data"
    metrics_list = get_excel_names(directory)
    metrics_selected = col1.selectbox(
        "Response Metrics",
        metrics_list,
        format_func=name_formating,
        index=st.session_state["last_saved_metric"],
        on_change=reset_optimization,
        key="metric_selected",
    )

    # Target
    target = name_formating(metrics_selected)

    file_selected = f"./metrics_level_data/Overview_data_test_panel@#{metrics_selected}.xlsx"
    # print(f"[DEBUG]: {metrics_selected}")
    # print(f"[DEBUG]: {file_selected}")
    st.session_state["file_selected"] = file_selected
    # Panel List
    panel_list = panel_fetch(file_selected)
    panel_list_final = ["Aggregated"] + panel_list

    # Panel Selected
    panel_selected = col2.selectbox(
        "Panel",
        panel_list_final,
        on_change=reset_optimization,
        key="panel_selected",
        index=st.session_state["last_saved_panel"],
    )

    unique_key = f"{st.session_state['metric_selected']}-{st.session_state['panel_selected']}"

    if "update_rcs" in st.session_state:
        updated_rcs = st.session_state["update_rcs"]
    else:
        updated_rcs = None

    if unique_key not in st.session_state["project_dct"]["scenario_planner"]:
        if panel_selected == "Aggregated":
            initialize_data(
                panel=panel_selected,
                target_file=file_selected,
                updated_rcs=updated_rcs,
                metrics=metrics_selected,
            )
            panel = None
        else:
            initialize_data(
                panel=panel_selected,
                target_file=file_selected,
                updated_rcs=updated_rcs,
                metrics=metrics_selected,
            )
        st.session_state["project_dct"]["scenario_planner"][unique_key] = (
            st.session_state["scenario"]
        )

    else:
        st.session_state["scenario"] = st.session_state["project_dct"][
            "scenario_planner"
        ][unique_key]
        st.session_state["rcs"] = {}
        st.session_state["powers"] = {}
        if "optimization_channels" not in st.session_state:
            st.session_state["optimization_channels"] = {}

        for channel_name, _channel in st.session_state["project_dct"][
            "scenario_planner"
        ][unique_key].channels.items():
            st.session_state[channel_name] = numerize(
                _channel.modified_total_spends, 1
            )
            st.session_state["rcs"][
                channel_name
            ] = _channel.response_curve_params
            st.session_state["powers"][channel_name] = _channel.power
            if channel_name not in st.session_state["optimization_channels"]:
                st.session_state["optimization_channels"][channel_name] = False

    if "first_time" not in st.session_state:
        st.session_state["first_time"] = True
        st.session_state["first_run_scenario"] = True

    # Check if state is initiaized
    is_state_initiaized = st.session_state.get("initialized", False)

    # if not is_state_initiaized:
    #     print("running initialize...")
    #     # initialize_data()
    #     if panel_selected == "Aggregated":
    #         initialize_data(
    #             panel=panel_selected,
    #             target_file=file_selected,
    #             updated_rcs=updated_rcs,
    #             metrics=metrics_selected,
    #         )
    #         panel = None
    #     else:
    #         initialize_data(
    #             panel=panel_selected,
    #             target_file=file_selected,
    #             updated_rcs=updated_rcs,
    #             metrics=metrics_selected,
    #         )
    #     st.session_state["initialized"] = True
    #     st.session_state["first_time"] = False

    # Channels List
    channels_list = list(
        st.session_state["project_dct"]["scenario_planner"][
            unique_key
        ].channels.keys()
    )

    # ======================================================== #
    # ========================== UI ========================== #
    # ======================================================== #

    main_header = st.columns((2, 2))
    sub_header = st.columns((1, 1, 1, 1))
    # _scenario = st.session_state["scenario"]

    st.session_state.total_spends_change = round(
        (
            st.session_state["scenario"].modified_total_spends
            / st.session_state["scenario"].actual_total_spends
            - 1
        )
        * 100
    )

    if "total_sales_change" not in st.session_state:
        st.session_state.total_sales_change = round(
            (
                st.session_state["scenario"].modified_total_sales
                / st.session_state["scenario"].actual_total_sales
                - 1
            )
            * 100
        )

    st.session_state["total_spends_change_abs"] = numerize(
        st.session_state["scenario"].modified_total_spends,
        1,
    )
    if "total_sales_change_abs" not in st.session_state:
        st.session_state["total_sales_change_abs"] = numerize(
            st.session_state["scenario"].modified_total_sales,
            1,
        )

    # if "total_spends_change_abs_slider" not in st.session_state:
    st.session_state.total_spends_change_abs_slider = numerize(
        st.session_state["scenario"].modified_total_spends, 1
    )

    if "total_sales_change_abs_slider" not in st.session_state:
        st.session_state.total_sales_change_abs_slider = numerize(
            st.session_state["scenario"].actual_total_sales, 1
        )

    st.session_state["allow_sales_update"] = True

    st.session_state["allow_spends_update"] = True

    # if "panel_selected" not in st.session_state:
    #     st.session_state["panel_selected"] = 0

    with main_header[0]:
        st.subheader("Actual")

    with main_header[-1]:
        st.subheader("Simulated")

    with sub_header[0]:
        st.metric(
            label="Spends",
            value=format_numbers(
                st.session_state["scenario"].actual_total_spends
            ),
        )

    with sub_header[1]:
        st.metric(
            label=target,
            value=format_numbers(
                float(st.session_state["scenario"].actual_total_sales),
                include_indicator=False,
            ),
        )

    with sub_header[2]:
        st.metric(
            label="Spends",
            value=format_numbers(
                st.session_state["scenario"].modified_total_spends
            ),
            delta=numerize(st.session_state["scenario"].delta_spends, 1),
        )

    with sub_header[3]:
        st.metric(
            label=target,
            value=format_numbers(
                float(st.session_state["scenario"].modified_total_sales),
                include_indicator=False,
            ),
            delta=numerize(st.session_state["scenario"].delta_sales, 1),
        )

    with st.expander("Channel Spends Simulator", expanded=True):
        _columns1 = st.columns((2, 2, 1, 1))
        with _columns1[0]:
            optimization_selection = st.selectbox(
                "Optimize",
                options=["Media Spends", target],
                key="optimization_key_value",
            )

        with _columns1[1]:
            st.markdown("#")
            # if st.checkbox(
            #     label="Optimize all Channels",
            #     key="optimze_all_channels",
            #     value=False,
            #     # on_change=select_all_channels_for_optimization,
            # ):
            #     select_all_channels_for_optimization()

            st.checkbox(
                label="Optimize all Channels",
                key="optimze_all_channels",
                on_change=select_all_channels_for_optimization,
            )

        with _columns1[2]:
            st.markdown("#")
            # st.button(
            #     "Optimize",
            #     on_click=optimize,
            #     args=(st.session_state["optimization_key_value"]),
            #     use_container_width=True,
            # )

            optimize_placeholder = st.empty()

        with _columns1[3]:
            st.markdown("#")
            st.button(
                "Reset",
                on_click=reset_scenario,
                # args=(panel_selected, file_selected, updated_rcs),
                use_container_width=True,
            )

        _columns2 = st.columns((2, 2, 2))
        if st.session_state["optimization_key_value"] == "Media Spends":

            # update_spends()

            with _columns2[0]:
                spend_input = st.text_input(
                    "Absolute",
                    key="total_spends_change_abs",
                    # label_visibility="collapsed",
                    on_change=update_all_spends_abs,
                )

            with _columns2[1]:
                st.number_input(
                    "Percent Change",
                    key="total_spends_change",
                    min_value=-50,
                    max_value=50,
                    step=1,
                    on_change=update_spends,
                )

            with _columns2[2]:
                scenario = st.session_state["project_dct"]["scenario_planner"][
                    unique_key
                ]
                min_value = round(scenario.actual_total_spends * 0.5)
                max_value = round(scenario.actual_total_spends * 1.5)
                st.session_state["total_spends_change_abs_slider_options"] = [
                    numerize(value, 1)
                    for value in range(min_value, max_value + 1, int(1e4))
                ]

                st.select_slider(
                    "Absolute Slider",
                    options=st.session_state[
                        "total_spends_change_abs_slider_options"
                    ],
                    key="total_spends_change_abs_slider",
                    on_change=update_all_spends_abs_slider,
                )

        elif st.session_state["optimization_key_value"] == target:
            # update_sales()

            with _columns2[0]:
                sales_input = st.text_input(
                    "Absolute",
                    key="total_sales_change_abs",
                    on_change=update_sales_abs,
                )

            with _columns2[1]:
                st.number_input(
                    "Percent Change",
                    key="total_sales_change",
                    min_value=-50,
                    max_value=50,
                    step=1,
                    on_change=update_sales,
                )

            with _columns2[2]:
                min_value = round(
                    st.session_state["scenario"].actual_total_sales * 0.5
                )
                max_value = round(
                    st.session_state["scenario"].actual_total_sales * 1.5
                )
                st.session_state["total_sales_change_abs_slider_options"] = [
                    numerize(value, 1)
                    for value in range(min_value, max_value + 1, int(1e5))
                ]

                st.select_slider(
                    "Absolute Slider",
                    options=st.session_state[
                        "total_sales_change_abs_slider_options"
                    ],
                    key="total_sales_change_abs_slider",
                    on_change=update_sales_abs_slider,
                )

        if (
            not st.session_state["allow_sales_update"]
            and optimization_selection == target
        ):
            st.warning("Invalid Input")

        if (
            not st.session_state["allow_spends_update"]
            and optimization_selection == "Media Spends"
        ):
            st.warning("Invalid Input")

        status_placeholder = st.empty()

        # if optimize_placeholder.button("Optimize", use_container_width=True):
        #     optimize(st.session_state["optimization_key_value"], status_placeholder)
        #     st.rerun()

        optimize_placeholder.button(
            "Optimize",
            on_click=optimize,
            args=(
                st.session_state["optimization_key_value"],
                status_placeholder,
            ),
            use_container_width=True,
        )

        st.markdown(
            """<hr class="spends-heading-seperator">""", unsafe_allow_html=True
        )
        _columns = st.columns((2.5, 2, 1.5, 1.5, 1))
        with _columns[0]:
            generate_spending_header("Channel")
        with _columns[1]:
            generate_spending_header("Spends Input")
        with _columns[2]:
            generate_spending_header("Spends")
        with _columns[3]:
            generate_spending_header(target)
        with _columns[4]:
            generate_spending_header("Optimize")

        st.markdown(
            """<hr class="spends-heading-seperator">""", unsafe_allow_html=True
        )

        if "acutual_predicted" not in st.session_state:
            st.session_state["acutual_predicted"] = {
                "Channel_name": [],
                "Actual_spend": [],
                "Optimized_spend": [],
                "Delta": [],
            }
        for i, channel_name in enumerate(channels_list):
            _channel_class = st.session_state["scenario"].channels[
                channel_name
            ]

            st.session_state[f"{channel_name}_percent"] = round(
                (
                    _channel_class.modified_total_spends
                    / _channel_class.actual_total_spends
                    - 1
                )
                * 100
            )

            _columns = st.columns((2.5, 1.5, 1.5, 1.5, 1))
            with _columns[0]:
                st.write(channel_name_formating(channel_name))
                bin_placeholder = st.container()

            with _columns[1]:
                channel_bounds = _channel_class.bounds
                channel_spends = float(_channel_class.actual_total_spends)
                min_value = float(
                    (1 + channel_bounds[0] / 100) * channel_spends
                )
                max_value = float(
                    (1 + channel_bounds[1] / 100) * channel_spends
                )
                # print("##########", st.session_state[channel_name])
                spend_input = st.text_input(
                    channel_name,
                    key=channel_name,
                    label_visibility="collapsed",
                    on_change=partial(update_data, channel_name),
                )
                if not validate_input(spend_input):
                    st.error("Invalid input")

                channel_name_current = f"{channel_name}_change"

                st.number_input(
                    "Percent Change",
                    key=f"{channel_name}_percent",
                    step=1,
                    on_change=partial(update_data_by_percent, channel_name),
                )

            with _columns[2]:
                # spends
                current_channel_spends = float(
                    _channel_class.modified_total_spends
                    * _channel_class.conversion_rate
                )
                actual_channel_spends = float(
                    _channel_class.actual_total_spends
                    * _channel_class.conversion_rate
                )
                spends_delta = float(
                    _channel_class.delta_spends
                    * _channel_class.conversion_rate
                )
                st.session_state["acutual_predicted"]["Channel_name"].append(
                    channel_name
                )
                st.session_state["acutual_predicted"]["Actual_spend"].append(
                    actual_channel_spends
                )
                st.session_state["acutual_predicted"][
                    "Optimized_spend"
                ].append(current_channel_spends)
                st.session_state["acutual_predicted"]["Delta"].append(
                    spends_delta
                )
                ## REMOVE
                st.metric(
                    "Spends",
                    format_numbers(current_channel_spends),
                    delta=numerize(spends_delta, 1),
                    label_visibility="collapsed",
                )

            with _columns[3]:
                # sales
                current_channel_sales = float(
                    _channel_class.modified_total_sales
                )
                actual_channel_sales = float(_channel_class.actual_total_sales)
                sales_delta = float(_channel_class.delta_sales)
                st.metric(
                    target,
                    format_numbers(
                        current_channel_sales, include_indicator=False
                    ),
                    delta=numerize(sales_delta, 1),
                    label_visibility="collapsed",
                )

            with _columns[4]:

                # if st.checkbox(
                #     label="select for optimization",
                #     key=f"{channel_name}_selected",
                #     value=False,
                #     # on_change=partial(select_channel_for_optimization, channel_name),
                #     label_visibility="collapsed",
                # ):
                #     select_channel_for_optimization(channel_name)

                st.checkbox(
                    label="select for optimization",
                    key=f"{channel_name}_selected",
                    value=False,
                    on_change=partial(
                        select_channel_for_optimization, channel_name
                    ),
                    label_visibility="collapsed",
                )

            st.markdown(
                """<hr class="spends-child-seperator">""",
                unsafe_allow_html=True,
            )

            # Bins
            col = channels_list[i]
            x_actual = st.session_state["scenario"].channels[col].actual_spends
            x_modified = (
                st.session_state["scenario"].channels[col].modified_spends
            )

            x_total = x_modified.sum()
            power = np.ceil(np.log(x_actual.max()) / np.log(10)) - 3

            updated_rcs_key = (
                f"{metrics_selected}#@{panel_selected}#@{channel_name}"
            )

            if updated_rcs and updated_rcs_key in list(updated_rcs.keys()):
                K = updated_rcs[updated_rcs_key]["K"]
                b = updated_rcs[updated_rcs_key]["b"]
                a = updated_rcs[updated_rcs_key]["a"]
                x0 = updated_rcs[updated_rcs_key]["x0"]
            else:
                K = st.session_state["rcs"][col]["K"]
                b = st.session_state["rcs"][col]["b"]
                a = st.session_state["rcs"][col]["a"]
                x0 = st.session_state["rcs"][col]["x0"]

            x_plot = np.linspace(0, 5 * x_actual.sum(), 200)

            # Append current_channel_spends to the end of x_plot
            x_plot = np.append(x_plot, current_channel_spends)

            x, y, marginal_roi = [], [], []
            for x_p in x_plot:
                x.append(x_p * x_actual / x_actual.sum())

            for index in range(len(x_plot)):
                y.append(s_curve(x[index] / 10**power, K, b, a, x0))

            for index in range(len(x_plot)):
                marginal_roi.append(
                    a
                    * y[index]
                    * (1 - y[index] / np.maximum(K, np.finfo(float).eps))
                )

            x = (
                np.sum(x, axis=1)
                * st.session_state["scenario"].channels[col].conversion_rate
            )
            y = np.sum(y, axis=1)
            marginal_roi = (
                np.average(marginal_roi, axis=1)
                / st.session_state["scenario"].channels[col].conversion_rate
            )

            roi = y / np.maximum(x, np.finfo(float).eps)

            roi_current, marginal_roi_current = roi[-1], marginal_roi[-1]
            x, y, roi, marginal_roi = (
                x[:-1],
                y[:-1],
                roi[:-1],
                marginal_roi[:-1],
            )  # Drop data for current spends

            start_value, end_value, left_value, right_value = (
                find_segment_value(
                    x,
                    roi,
                    marginal_roi,
                )
            )

            rgba = calculate_rgba(
                start_value,
                end_value,
                left_value,
                right_value,
                current_channel_spends,
            )

            with bin_placeholder:
                st.markdown(
                    f"""
                    <div style="
                        border-radius: 12px;
                        background-color: {rgba};
                        padding: 10px;
                        text-align: center;
                        color: #006EC0;
                        ">
                        <p style="margin: 0; font-size: 20px;">ROI: {round(roi_current,1)}</p>
                        <p style="margin: 0; font-size: 20px;">Marginal ROI: {round(marginal_roi_current,1)}</p>
                    </div>
                    """,
                    unsafe_allow_html=True,
                )

    st.session_state["project_dct"]["scenario_planner"]["scenario"] = (
        st.session_state["scenario"]
    )

    with st.expander("See Response Curves", expanded=True):
        fig = plot_response_curves()
        st.plotly_chart(fig, use_container_width=True)

    def update_optimization_bounds(channel_name, bound_type):
        index = 0 if bound_type == "lower" else 1
        update_key = (
            f"{channel_name}_b_lower"
            if bound_type == "lower"
            else f"{channel_name}_b_upper"
        )
        st.session_state["project_dct"]["scenario_planner"][
            unique_key
        ].channels[channel_name].bounds[index] = st.session_state[update_key]

    def update_optimization_bounds_all(bound_type):
        index = 0 if bound_type == "lower" else 1
        update_key = (
            f"all_b_lower" if bound_type == "lower" else f"all_b_upper"
        )

        for channel_name, _channel in st.session_state["project_dct"][
            "scenario_planner"
        ][unique_key].channels.items():
            _channel.bounds[index] = st.session_state[update_key]

    with st.expander("Optimization setup"):
        bounds_placeholder = st.container()
        with bounds_placeholder:
            st.subheader("Optimization Bounds")
            with st.container():
                bounds_columns = st.columns((1, 0.35, 0.35, 1))
                with bounds_columns[0]:
                    st.write("##")
                    st.write("Update all channels")

                with bounds_columns[1]:
                    st.number_input(
                        "Lower",
                        min_value=-100,
                        max_value=500,
                        key=f"all_b_lower",
                        # label_visibility="hidden",
                        on_change=update_optimization_bounds_all,
                        args=("lower",),
                        step=5,
                        value=-10,
                    )

                with bounds_columns[2]:
                    st.number_input(
                        "Higher",
                        value=10,
                        min_value=-100,
                        max_value=500,
                        key=f"all_b_upper",
                        # label_visibility="hidden",
                        on_change=update_optimization_bounds_all,
                        args=("upper",),
                        step=5,
                    )
                st.divider()

            st.write("#### Channel wise bounds")
            # st.divider()
            # bounds_columns = st.columns((1, 0.35, 0.35, 1))

            # with bounds_columns[0]:
            #     st.write("Channel")
            # with bounds_columns[1]:
            #     st.write("Lower")
            # with bounds_columns[2]:
            #     st.write("Upper")
            # st.divider()

        for channel_name, _channel in st.session_state["project_dct"][
            "scenario_planner"
        ][unique_key].channels.items():
            st.session_state[f"{channel_name}_b_lower"] = _channel.bounds[0]
            st.session_state[f"{channel_name}_b_upper"] = _channel.bounds[1]
            with bounds_placeholder:
                with st.container():
                    bounds_columns = st.columns((1, 0.35, 0.35, 1))
                    with bounds_columns[0]:
                        st.write("##")
                        st.write(channel_name)
                    with bounds_columns[1]:
                        st.number_input(
                            "Lower",
                            min_value=-100,
                            max_value=500,
                            key=f"{channel_name}_b_lower",
                            label_visibility="hidden",
                            on_change=update_optimization_bounds,
                            args=(
                                channel_name,
                                "lower",
                            ),
                        )

                    with bounds_columns[2]:
                        st.number_input(
                            "Higher",
                            min_value=-100,
                            max_value=500,
                            key=f"{channel_name}_b_upper",
                            label_visibility="hidden",
                            on_change=update_optimization_bounds,
                            args=(
                                channel_name,
                                "upper",
                            ),
                        )

                    st.divider()
    _columns = st.columns(2)
    with _columns[0]:
        st.subheader("Save Scenario")
        scenario_name = st.text_input(
            "Scenario name",
            key="scenario_input",
            placeholder="Scenario name",
            label_visibility="collapsed",
        )
        st.button(
            "Save",
            on_click=lambda: save_scenario(scenario_name),
            disabled=len(st.session_state["scenario_input"]) == 0,
        )

    summary_df = pd.DataFrame(st.session_state["acutual_predicted"])
    summary_df.drop_duplicates(
        subset="Channel_name", keep="last", inplace=True
    )

    summary_df_sorted = summary_df.sort_values(by="Delta", ascending=False)
    summary_df_sorted["Delta_percent"] = np.round(
        (
            (
                summary_df_sorted["Optimized_spend"]
                / summary_df_sorted["Actual_spend"]
            )
            - 1
        )
        * 100,
        2,
    )

    with open("summary_df.pkl", "wb") as f:
        pickle.dump(summary_df_sorted, f)
        # st.dataframe(summary_df_sorted)
        # ___columns=st.columns(3)
        # with ___columns[2]:
        #     fig=summary_plot(summary_df_sorted, x='Delta_percent', y='Channel_name', title='Delta', text_column='Delta_percent')
        #     st.plotly_chart(fig,use_container_width=True)
        # with ___columns[0]:
        #     fig=summary_plot(summary_df_sorted, x='Actual_spend', y='Channel_name', title='Actual Spend', text_column='Actual_spend')
        #     st.plotly_chart(fig,use_container_width=True)
        # with ___columns[1]:
        #     fig=summary_plot(summary_df_sorted, x='Optimized_spend', y='Channel_name', title='Planned Spend', text_column='Optimized_spend')
        #     st.plotly_chart(fig,use_container_width=True)

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.session_state["config"]["credentials"]["usernames"][
                username_forgot_pw
            ]["password"] = stauth.Hasher([random_password]).generate()[0]
            send_email(email_forgot_password, random_password)
            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)

update_db("9_Scenario_Planner.py")