pages/2_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 datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import time
import Streamlit_functions as sf
from utilities import (
    format_numbers,
    format_numbers_f,
    load_local_css,
    set_header,
    initialize_data,
    load_authenticator,
    send_email,
    channel_name_formating,
)
from io import BytesIO

# import xlsxwriter
import warnings

# Suppress specific warnings if necessary
warnings.filterwarnings("ignore")
warnings.filterwarnings("ignore", category=UserWarning, message="The widget with key")
# for i in :
# warnings.filterwarnings("ignore",)
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 response_curves_model_quality as rc

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

from pptx import Presentation
from pptx.util import Inches
from io import BytesIO
import plotly.io as pio


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 save_report_forecast(forecasted_table_df, forecasted_table_df2):
    # Convert the DataFrame to an Excel file in memory
    excel_file = BytesIO()
    with pd.ExcelWriter(excel_file, engine="openpyxl") as writer:
        forecasted_table_df.to_excel(writer, index=True, sheet_name="Forecasted Spends")
        forecasted_table_df2.to_excel(
            writer, sheet_name="Monthly Breakdown", index=True
        )
    # Seek to the beginning of the BytesIO buffer
    excel_file.seek(0)
    return excel_file


def save_ppt_file(summary_df_sorted, fig1, fig2, fig3):
    summary_df_sorted.index = summary_df_sorted["Channel_name"]
    # Initialize PowerPoint presentation
    prs = Presentation()

    # Helper function to add Plotly figure to slide
    def add_plotly_chart_to_slide(slide, fig, left, top, width, height):
        img_stream = BytesIO()
        pio.write_image(fig, img_stream, format="png")
        slide.shapes.add_picture(img_stream, left, top, width, height)

    for i in range(0, len(channels_list)):
        # # print(channels_list[i])
        slide_1 = prs.slides.add_slide(prs.slide_layouts[6])
        fig = rc.response_curves(
            channels_list[i],
            summary_df_sorted["Optimized_spend"][channels_list[i]],
            summary_df_sorted["New_sales"][channels_list[i]],
        )
        add_plotly_chart_to_slide(
            slide_1, fig, Inches(0.1), Inches(0.1), width=Inches(9), height=Inches(7)
        )

    # Update layout
    fig1.update_layout(
        legend=dict(
            orientation="h",  # Horizontal orientation
            yanchor="top",  # Anchor the legend at the top
            y=-0.4,  # Position the legend below the plot area
            xanchor="center",  # Center the legend horizontally
            x=0.5,  # Center the legend on the x-axis
        )
    )
    # Update layout
    fig2.update_layout(
        legend=dict(
            orientation="h",  # Horizontal orientation
            yanchor="top",  # Anchor the legend at the top
            y=-0.4,  # Position the legend below the plot area
            xanchor="center",  # Center the legend horizontally
            x=0.5,  # Center the legend on the x-axis
        )
    )
    # Update layout
    fig3.update_layout(
        legend=dict(
            orientation="h",  # Horizontal orientation
            yanchor="top",  # Anchor the legend at the top
            y=-0.4,  # Position the legend below the plot area
            xanchor="center",  # Center the legend horizontally
            x=0.5,  # Center the legend on the x-axis
        )
    )

    slide_1 = prs.slides.add_slide(prs.slide_layouts[6])

    add_plotly_chart_to_slide(
        slide_1, fig1, Inches(0.1), Inches(1), width=Inches(9.5), height=Inches(6)
    )
    slide_1 = prs.slides.add_slide(prs.slide_layouts[6])
    add_plotly_chart_to_slide(
        slide_1, fig2, Inches(0.1), Inches(1), width=Inches(9.5), height=Inches(6)
    )
    slide_1 = prs.slides.add_slide(prs.slide_layouts[6])
    add_plotly_chart_to_slide(
        slide_1, fig3, Inches(0.1), Inches(1), width=Inches(9.5), height=Inches(6)
    )

    # Save to a BytesIO object
    ppt_stream = BytesIO()
    prs.save(ppt_stream)
    ppt_stream.seek(0)

    return ppt_stream.getvalue()


def first_day_of_next_year(date):
    next_year = date.year + 1
    first_day = datetime(next_year, 1, 1).date()

    # Calculate the last day of the next year
    last_day = first_day + relativedelta(years=1, days=-1)

    return first_day, last_day


def first_day_of_next_quarter(date):
    current_quarter = (date.month - 1) // 3 + 1
    next_quarter_first_month = ((current_quarter % 4) * 3) + 1
    next_quarter_year = date.year if next_quarter_first_month > 1 else date.year + 1
    # Ensure month is within valid range
    if next_quarter_first_month < 1 or next_quarter_first_month > 12:
        raise ValueError(
            "Calculated month is out of range: {}".format(next_quarter_first_month)
        )
    # st.write(next_quarter_first_month)
    first_day_next_quarter = datetime(
        next_quarter_year, next_quarter_first_month, 1
    ).date()
    last_day_next_quarter = (
        first_day_next_quarter + relativedelta(months=3)
    ) - relativedelta(days=1)

    return first_day_next_quarter, last_day_next_quarter


def first_day_of_next_month(date):
    next_month_date = date + relativedelta(months=1)
    first_day_next_month = next_month_date.replace(day=1)
    last_day_next_month = (
        first_day_next_month + relativedelta(months=1)
    ) - relativedelta(days=1)
    return first_day_next_month, last_day_next_month


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"):
                    # # # # print(channel_list)
                    # # # # print(st.session_state["total_spends_change"])
                    result = st.session_state["scenario"].optimize(
                        st.session_state["total_spends_change"],
                        channel_list,
                        # result = st.session_state["scenario"].spends_optimisation(
                        # st.session_state["total_spends_change"], channel_list
                    )
                    # # print("")
                    # # print(list(zip(*result)))

        # 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 * 1.0,
                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)


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

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


def update_sales_abs_slider():
    actual_sales = _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)


def update_sales_abs():
    # if (
    #     st.session_state["total_sales_change_abs"]
    #     in st.session_state["total_sales_change_abs_slider_options"]
    # ):
    #     st.session_state["allow_sales_update"] = True
    # else:
    #     st.session_state["allow_sales_update"] = False

    actual_sales = _scenario.actual_total_sales
    # if (
    #     validate_input(st.session_state["total_sales_change_abs"])
    #     and st.session_state["allow_sales_update"]
    # ):
    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():
    st.session_state["total_sales_change_abs"] = numerize(
        (1 + st.session_state["total_sales_change"] / 100)
        * _scenario.actual_total_sales,
        1,
    )
    st.session_state["total_sales_change_abs_slider"] = numerize(
        (1 + st.session_state["total_sales_change"] / 100)
        * _scenario.actual_total_sales,
        1,
    )


# def update_all_spends_abs_slider():
#     actual_spends = _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)

#         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"])C:\Users\PragyaJatav\Downloads\Untitled Folder 2\simulatorAldi\pages\8_Scenario_Planner.py
#         # # # 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():
    # st.write("aon update spends abs")
    # if (
    #     st.session_state["total_spends_change_abs"]
    #     in st.session_state["total_spends_change_abs_slider_options"]
    # ):
    #     st.session_state["allow_spends_update"] = True
    #     # print st.session_state["total_spends_change_abs_slider_options"]
    #     # print("not allowed")
    # else:
    #     st.session_state["allow_spends_update"] = False
    #     # st.warning("Invalid Input")

    actual_spends = _scenario.actual_total_spends
    if (
        "total_spends_change_abs" in st.session_state and validate_input(st.session_state["total_spends_change_abs"])
        and st.session_state["allow_spends_update"]
    ):
        modified_spends = extract_number_for_string(
            st.session_state["total_spends_change_abs"]
        )
        total_spends_change_val = (
            (modified_spends / actual_spends) - 1
        ) * 100

        if "overall_lower_bound" in st.session_state:
            try:
                lower_val = float(st.session_state["overall_lower_bound"])
            except (ValueError, TypeError):
                lower_val = 30.0

        if "overall_upper_bound" in st.session_state:
            try:
                upper_val = float(st.session_state["overall_upper_bound"])
            except (ValueError, TypeError):
                upper_val = 30.0

        if total_spends_change_val > upper_val or total_spends_change_val < -lower_val:
            del st.session_state.total_spends_change_abs
            return 
            
        st.session_state["total_spends_change"] = total_spends_change_val
        st.session_state["total_spends_change_abs_slider"] = st.session_state[
            "total_spends_change_abs"
        ]

        update_all_spends()


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

    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"]

    for channel_name in st.session_state["channels_list"]:
        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 validate_input_lb(string_input):
    pattern = r"\d+\.?\d*[K|M|B]$"
    match = re.match(pattern, string_input)
    if match is None:
        return False
    response_curve_params = pd.read_excel(
        "response_curves_parameters.xlsx", index_col="channel"
    )
    param_dicts = {
        col: response_curve_params[col].to_dict()
        for col in response_curve_params.columns
    }
    if float(string_input) < round(
        param_dicts["x_min"][channel_name]
        * 10400
        / param_dicts["current_spends"][channel_name]
    ):
        return False

    return True


def update_data_bound_min(channel_name):
    """
    Updates the bounds for the given channel
    """
    if validate_input_lb(st.session_state[f"{channel_name}_lower_bound"]):
        modified_bounds = st.session_state[f"{channel_name}_lower_bound"]

        # st.session_state['scenario']['channels'][channel_name].channel_bounds_min = st.session_state[f"{channel_name}_lower_bound"]
        st.session_state["scenario"].update_bounds_min(channel_name, modified_bounds)
        st.write(st.session_state["scenario"].channels[channel_name].channel_bounds_min)
        # st.write(st.session_state["scenario"].channels[channel_name]


def update_data_bound_max(channel_name):
    """
    Updates the bounds for the given channel
    """
    modified_bounds = st.session_state[f"{channel_name}_upper_bound"]

    # st.session_state['scenario']['channels'][channel_name].channel_bounds_min = st.session_state[f"{channel_name}_lower_bound"]
    st.session_state["scenario"].update_bounds_max(channel_name, modified_bounds)
    # st.write(st.session_state["scenario"].channels[channel_name].channel_bounds_max)
    # st.write(st.session_state["scenario"].channels[channel_name])


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}_change"] / 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
    """

    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
        )


        channel_spends_change = round(
            100 * (modified_spends - prev_spends) / prev_spends, 2
        )

        if "overall_lower_bound" in st.session_state:
            try:
                lower_val = float(st.session_state["overall_lower_bound"])
            except (ValueError, TypeError):
                lower_val = 30.0

        if "overall_upper_bound" in st.session_state:
            try:
                upper_val = float(st.session_state["overall_upper_bound"])
            except (ValueError, TypeError):
                upper_val = 30.0

        if channel_spends_change > upper_val or channel_spends_change < -lower_val:
            del st.session_state[channel_name]
            return 

        st.session_state[f"{channel_name}_change"] = channel_spends_change
        st.session_state["scenario"].update(
            channel_name,
            modified_spends
            / st.session_state["scenario"].channels[channel_name].conversion_rate,
        )


# st.write(hasattr(st.session_state["scenario"], 'update_bounds_min'))
# 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"
    ]

    # if not all(st.session_state["optimization_channels"].values()):
    #     st.session_state["optimize_all_channels"] = False


def select_all_channels_for_optimization():
    """
    Marks all the channel for optimization
    """
    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"
        ]


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


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)
    # initialize_data()

    if panel_selected == "Total Market":
        initialize_data(
            target_file=file_selected,
            panel=panel_selected,
            updated_rcs=updated_rcs,
            metrics=metrics_selected,
        )
        panel = None
    else:
        initialize_data(
            target_file=file_selected,
            panel=panel_selected,
            updated_rcs=updated_rcs,
            metrics=metrics_selected,
        )

    for channel_name in st.session_state["channels_list"]:
        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["total_sales_change"] = 0

    if "overall_lower_bound" in st.session_state and "overall_upper_bound" in st.session_state:
        del st.session_state["overall_lower_bound"]
        del st.session_state["overall_upper_bound"]

        st.session_state["overall_lower_bound"] = 30.0
        st.session_state["overall_upper_bound"] = 30.0

    update_spends()
    update_sales()

    reset_inputs()

    # st.rerun()


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(summary_df_sorted):
    # 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)
    channel_cols = [
        "BroadcastTV",
        "CableTV",
        "Connected&OTTTV",
        "DisplayProspecting",
        "DisplayRetargeting",
        "Video",
        "SocialProspecting",
        "SocialRetargeting",
        "SearchBrand",
        "SearchNon-brand",
        "DigitalPartners",
        "Audio",
        "Email",
    ]
    summary_df_sorted.index = summary_df_sorted["Channel_name"]
    figures = [
        rc.response_curves(
            channels_list[i],
            summary_df_sorted["Optimized_spend"][channels_list[i]],
            summary_df_sorted["New_sales"][channels_list[i]],
        )
        for i in range(13) if channels_list[i] != "Email"
    ]


    # for i in range()

    # Display figures in a grid layout
    col_num = 3
    cols = st.columns(col_num)  # 4 columns for the grid

    for idx, fig in enumerate(figures):
        col = cols[idx % col_num]
        with col:
            # Get the current title and replace 'Response Curve' with a space
            current_title = fig.layout.title.text if fig.layout.title else ""
            updated_title = current_title.replace("Response Curve", "").strip().replace("Connected & OTTTV", "Connected & OTT TV")

            fig.update_layout(height=350, width=350, yaxis_title="Revenue", title=updated_title)
            st.plotly_chart(fig, use_container_width=True)


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


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


# ======================================================== #
# =================== 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 upload_file_prospects_calc(df):
    df["Prospects"] = 0

    params = pd.read_excel(r"response_curves_parameters.xlsx", index_col="channel")
    param_dicts = {col: params[col].to_dict() for col in params.columns}
    df.index = df.channel
    #     # # # print(param_dicts)
    for col in df.channel:
        x = df["Spends"][col]
        dividing_rate = 104
        # st.write(x)
        x_inp = (x / dividing_rate - param_dicts["x_min"][col]) / (
            param_dicts["x_max"][col] - param_dicts["x_min"][col]
        )
        x_out = x_inp ** param_dicts["n"][col] / (
            param_dicts["Kd"][col] ** param_dicts["n"][col]
            + x_inp ** param_dicts["n"][col]
        )  # self.hill_equation(x_inp,Kd, n)
        # # # # print("x_out",x_out)

        x_val_inv = (
            x_out * param_dicts["x_max"][col] + (1 - x_out) * param_dicts["x_min"][col]
        )
        sales = (
            x_val_inv * param_dicts["y_min"][col] / param_dicts["y_max"][col]
        ) * dividing_rate
        # sales = ((x_max - x_min)*x_out + x_min)*dividing_rate

        # x = (df["Spends"][col]/104 - param_dicts["x_min"][col])/(param_dicts["x_max"][col]-param_dicts["x_min"][col])
        # x_out = x**param_dicts["n"][col]/(param_dicts["Kd"][col]**param_dicts["n"][col]+ x**param_dicts["n"][col])
        # x_out_inv = (x_out*(param_dicts["y_max"][col]-param_dicts["y_min"][col])+param_dicts["y_min"][col])*104
        df["Prospects"][col] = sales
    # # # # print(df)
    return df


def upload_file_format(df):
    # key_df = pd.DataFrame()
    # key_df["channel"] = ["Broadcast TV","Cable TV","Connected & OTT TV","Display Prospecting","Display Retargeting","Video","Social Prospecting","Social Retargeting","Search Brand","Search Non-brand","Digital Partners","Audio","Email"]

    # key_df["channels"] = ["BroadcastTV","CableTV","Connected&OTTTV","DisplayProspecting","DisplayRetargeting","\xa0Video","SocialProspecting","SocialRetargeting","SearchBrand","SearchNon-brand","DigitalPartners","Audio","Email"]
    # df = df.merge(key_df,on = "channel", how = "inner")
    # # st.dataframe(df)
    # df["channel"] = df["channels"]
    # df.drop(columns = ["channel"])

    df1 = df.transpose()
    df1.reset_index(inplace=True)
    df1.columns = df1.iloc[0]
    df1 = df1[1:]
    df1["channel"] = pd.to_datetime("1999-08-06").date()
    df1.rename(columns={"channel": "Date"}, inplace=True)

    df2 = df1.rename(columns={"Date": "Week"})

    df3 = upload_file_prospects_calc(df)
    df3 = df3[["Prospects"]].transpose().reset_index()
    df3["index"] = pd.to_datetime("1999-08-06").date()
    df3.rename(columns={"index": "Date"}, inplace=True)
    df3.insert(1, "const", [0])

    # st.dataframe(df3)

    # Create a buffer to hold the Excel file
    import io

    output = io.BytesIO()

    # Write the dataframes to an Excel file
    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        df1.to_excel(writer, index=False, sheet_name="RAW DATA MMM")
        df2.to_excel(writer, index=False, sheet_name="SPEND INPUT")
        df3.to_excel(writer, index=False, sheet_name="CONTRIBUTION MMM")

    # Seek to the beginning of the stream
    output.seek(0)

    with open("Overview_data_uploaded.xlsx", "wb") as f:
        f.write(output.getvalue())

    return


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_data(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
    # raw_data_mmm_df = None
    panel = None
    return panel


def reset_inputs():
    if "total_spends_change_abs" in st.session_state:
        del st.session_state.total_spends_change_abs
    if "total_spends_change" in st.session_state:
        del st.session_state.total_spends_change
    if "total_spends_change_abs_slider" in st.session_state:
        del st.session_state.total_spends_change_abs_slider

    if "total_sales_change_abs" in st.session_state:
        del st.session_state.total_sales_change_abs
    if "total_sales_change" in st.session_state:
        del st.session_state.total_sales_change
    if "total_sales_change_abs_slider" in st.session_state:
        del st.session_state.total_sales_change_abs_slider

    st.session_state["initialized"] = False


def scenario_planner_plots2():
    import plotly.graph_objects as go
    from plotly.subplots import make_subplots

    with open("summary_df.pkl", "rb") as file:
        summary_df_sorted = pickle.load(file)
        # st.write(summary_df_sorted)

    # selected_scenario= st.selectbox('Select Saved Scenarios',['S1','S2'])
    summary_df_sorted = summary_df_sorted.sort_values(
        by=["Optimized_spend"], ascending=False
    )
    summary_df_sorted["old_efficiency"] = (
        summary_df_sorted["Old_sales"] / summary_df_sorted["Old_sales"].sum()
    ) / (summary_df_sorted["Actual_spend"] / summary_df_sorted["Actual_spend"].sum())
    summary_df_sorted["new_efficiency"] = (
        summary_df_sorted["New_sales"] / summary_df_sorted["New_sales"].sum()
    ) / (
        summary_df_sorted["Optimized_spend"]
        / summary_df_sorted["Optimized_spend"].sum()
    )
    summary_df_sorted["old_roi"] = (
        summary_df_sorted["Old_sales"] / summary_df_sorted["Actual_spend"]
    )
    summary_df_sorted["new_roi"] = (
        summary_df_sorted["New_sales"] / summary_df_sorted["Optimized_spend"]
    )

    total_actual_spend = summary_df_sorted["Actual_spend"].sum()
    total_optimized_spend = summary_df_sorted["Optimized_spend"].sum()
    actual_spend_percentage = (
        summary_df_sorted["Actual_spend"] / total_actual_spend
    ) * 100
    optimized_spend_percentage = (
        summary_df_sorted["Optimized_spend"] / total_optimized_spend
    ) * 100

    light_blue = "rgba(0, 31, 120, 0.7)"
    light_orange = "rgba(0, 181, 219, 0.7)"
    light_green = "rgba(240, 61, 20, 0.7)"
    light_red = "rgba(250, 110, 10, 0.7)"
    light_purple = "rgba(255, 191, 69, 0.7)"

    fig1 = go.Figure()
    # Add actual vs optimized spend bars

    fig1.add_trace(
        go.Bar(
            x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted["Actual_spend"],
            name="Actual",
            text=summary_df_sorted["Actual_spend"].apply(format_number) + " "
            #  +
            #  ' '+
            # '</br> (' + actual_spend_percentage.astype(int).astype(str) + '%)'
            ,
            textposition="outside",  # textfont=dict(size=30),
            marker_color=light_blue,
        )
    )

    fig1.add_trace(
        go.Bar(
            x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted["Optimized_spend"],
            name="Optimized",
            text=summary_df_sorted["Optimized_spend"].apply(format_number) + " "
            #    +
            #  '</br> (' + optimized_spend_percentage.astype(int).astype(str) + '%)'
            ,
            textposition="outside",  # textfont=dict(size=30),
            marker_color=light_orange,
        )
    )

    fig1.update_xaxes(title_text="Channels")
    fig1.update_yaxes(title_text="Spends ($)")
    fig1.update_layout(
        title="Actual vs. Optimized Spends", margin=dict(t=40, b=40, l=40, r=40)
    )

    # st.plotly_chart(fig1,use_container_width=True)

    # Add actual vs optimized Contribution
    fig2 = go.Figure()
    fig2.add_trace(
        go.Bar(
            x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted["Old_sales"],
            name="Actual Contribution",
            text=summary_df_sorted["Old_sales"].apply(format_number),
            textposition="outside",
            marker_color=light_blue,
            showlegend=True,
        )
    )

    fig2.add_trace(
        go.Bar(
            x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted["New_sales"],
            name="Optimized Contribution",
            text=summary_df_sorted["New_sales"].apply(format_number),
            textposition="outside",
            marker_color=light_orange,
            showlegend=True,
        )
    )

    fig2.update_yaxes(title_text="Contribution")
    fig2.update_xaxes(title_text="Channels")
    fig2.update_layout(
        title="Actual vs. Optimized Contributions",
        margin=dict(t=40, b=40, l=40, r=40),
        # yaxis=dict(range=[0, 0.002]),
    )
    # st.plotly_chart(fig2,use_container_width=True)

    # Add actual vs optimized Efficiency bars
    fig3 = go.Figure()
    summary_df_sorted_p = summary_df_sorted[
        summary_df_sorted["Channel_name"] != "Panel"
    ]
    fig3.add_trace(
        go.Bar(
            x=summary_df_sorted_p["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted_p["old_efficiency"],
            name="Actual Efficiency",
            text=summary_df_sorted_p["old_efficiency"].apply(format_number),
            textposition="outside",
            marker_color=light_blue,
            showlegend=True,
        )
    )
    fig3.add_trace(
        go.Bar(
            x=summary_df_sorted_p["Channel_name"].apply(channel_name_formating),
            y=summary_df_sorted_p["new_efficiency"],
            name="Optimized Efficiency",
            text=summary_df_sorted_p["new_efficiency"].apply(format_number),
            textposition="outside",
            marker_color=light_orange,
            showlegend=True,
        )
    )

    fig3.update_xaxes(title_text="Channels")
    fig3.update_yaxes(title_text="Efficiency")
    fig3.update_layout(
        title="Actual vs. Optimized Efficiency",
        margin=dict(t=40, b=40, l=40, r=40),
        # yaxis=dict(range=[0, 0.002]),
    )

    # st.plotly_chart(fig3,use_container_width=True)
    return fig1, fig2, fig3


def scenario_planner_plots():
    with st.expander("Actual vs. Optimized Comparison"):
        # if st.button('Refresh'):
        #     st.experimental_rerun()

        import plotly.graph_objects as go
        from plotly.subplots import make_subplots

        # Define light colors for bars
        import plotly.graph_objects as go
        from plotly.subplots import make_subplots

        st.empty()
        # st.header('Model Result Analysis')
        spends_data = pd.read_excel("Overview_data_test.xlsx")

        with open("summary_df.pkl", "rb") as file:
            summary_df_sorted = pickle.load(file).copy()
            # st.write(summary_df_sorted)

        # selected_scenario= st.selectbox('Select Saved Scenarios',['S1','S2'])
        summary_df_sorted = summary_df_sorted.sort_values(
            by=["Optimized_spend"], ascending=False
        )
        summary_df_sorted["old_efficiency"] = (
            summary_df_sorted["Old_sales"] / summary_df_sorted["Old_sales"].sum()
        ) / (
            summary_df_sorted["Actual_spend"] / summary_df_sorted["Actual_spend"].sum()
        )
        summary_df_sorted["new_efficiency"] = (
            summary_df_sorted["New_sales"] / summary_df_sorted["New_sales"].sum()
        ) / (
            summary_df_sorted["Optimized_spend"]
            / summary_df_sorted["Optimized_spend"].sum()
        )

        summary_df_sorted["old_roi"] = (
            summary_df_sorted["Old_sales"] / summary_df_sorted["Actual_spend"]
        )
        summary_df_sorted["new_roi"] = (
            summary_df_sorted["New_sales"] / summary_df_sorted["Optimized_spend"]
        )

        total_actual_spend = summary_df_sorted["Actual_spend"].sum()
        total_optimized_spend = summary_df_sorted["Optimized_spend"].sum()

        actual_spend_percentage = (
            summary_df_sorted["Actual_spend"] / total_actual_spend
        ) * 100
        optimized_spend_percentage = (
            summary_df_sorted["Optimized_spend"] / total_optimized_spend
        ) * 100

        light_blue = "rgba(0, 31, 120, 0.7)"
        light_orange = "rgba(0, 181, 219, 0.7)"
        light_green = "rgba(240, 61, 20, 0.7)"
        light_red = "rgba(250, 110, 10, 0.7)"
        light_purple = "rgba(255, 191, 69, 0.7)"

        fig1 = go.Figure()
        # Add actual vs optimized spend bars

        fig1.add_trace(
            go.Bar(
                x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted["Actual_spend"],
                name="Actual",
                text=summary_df_sorted["Actual_spend"].apply(format_number) + " "
                #  +
                #  ' '+
                # '</br> (' + actual_spend_percentage.astype(int).astype(str) + '%)'
                ,
                textposition="outside",  # textfont=dict(size=30),
                marker_color=light_blue,
            )
        )

        fig1.add_trace(
            go.Bar(
                x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted["Optimized_spend"],
                name="Optimized",
                text=summary_df_sorted["Optimized_spend"].apply(format_number) + " "
                #    +
                #  '</br> (' + optimized_spend_percentage.astype(int).astype(str) + '%)'
                ,
                textposition="outside",  # textfont=dict(size=30),
                marker_color=light_orange,
            )
        )

        fig1.update_xaxes(title_text="Channels")
        fig1.update_yaxes(title_text="Spends ($)")
        fig1.update_layout(
            title="Spends", margin=dict(t=40, b=40, l=40, r=40)
        )

        st.plotly_chart(fig1, use_container_width=True)

        # Add actual vs optimized Contribution
        fig2 = go.Figure()
        fig2.add_trace(
            go.Bar(
                x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted["Old_sales"],
                name="Actual Contribution",
                text=summary_df_sorted["Old_sales"].apply(format_number),
                textposition="outside",
                marker_color=light_blue,
                showlegend=True,
            )
        )

        fig2.add_trace(
            go.Bar(
                x=summary_df_sorted["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted["New_sales"],
                name="Optimized Contribution",
                text=summary_df_sorted["New_sales"].apply(format_number),
                textposition="outside",
                marker_color=light_orange,
                showlegend=True,
            )
        )

        fig2.update_yaxes(title_text="Revenue ($)")
        fig2.update_xaxes(title_text="Channels")
        fig2.update_layout(
            title="Revenue",
            margin=dict(t=40, b=40, l=40, r=40),
            # yaxis=dict(range=[0, 0.002]),
        )
        st.plotly_chart(fig2, use_container_width=True)

        # Add actual vs optimized Efficiency bars
        fig3 = go.Figure()
        summary_df_sorted_p = summary_df_sorted[
            summary_df_sorted["Channel_name"] != "Panel"
        ]

        # Ensure there are no division-by-zero errors
        summary_df_sorted_p["Actual_spend"].replace(0, np.nan, inplace=True)
        summary_df_sorted_p["Optimized_spend"].replace(0, np.nan, inplace=True)

        summary_df_sorted_p["old_roi"] = summary_df_sorted_p["Old_sales"] / summary_df_sorted_p["Actual_spend"]
        summary_df_sorted_p["new_roi"] = summary_df_sorted_p["New_sales"] / summary_df_sorted_p["Optimized_spend"]


        fig3.add_trace(
            go.Bar(
                x=summary_df_sorted_p["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted_p["old_roi"],
                name="Actual ROI",
                text=summary_df_sorted_p["old_roi"].apply(format_number),
                textposition="outside",
                marker_color=light_blue,
                showlegend=True,
            )
        )

        fig3.add_trace(
            go.Bar(
                x=summary_df_sorted_p["Channel_name"].apply(channel_name_formating),
                y=summary_df_sorted_p["new_roi"],
                name="Optimized ROI",
                text=summary_df_sorted_p["new_roi"].apply(format_number),
                textposition="outside",
                marker_color=light_orange,
                showlegend=True,
            )
        )

        fig3.update_xaxes(title_text="Channels")
        fig3.update_yaxes(title_text="ROI")
        fig3.update_layout(
            title="ROI",
            margin=dict(t=40, b=40, l=40, r=40),
            # yaxis=dict(range=[0, 0.002]),
        )

        st.plotly_chart(fig3, use_container_width=True)
        return fig1, fig2, fig3


def give_demo():
    def get_file_bytes(file_path):
        with open(file_path, "rb") as file:
            return file.read()

    # Path to the existing Excel file
    file_path = "input_data_example.xlsx"

    # Create a download button
    st.download_button(
        label="Download Input File Format",
        data=get_file_bytes(file_path),
        file_name=file_path,
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )


if auth_status == True:
    authenticator.logout("Logout", "main")
    st.header("Scenario Planner")

    data_selected = st.selectbox(
        "Select base data for optimisation",
        options=["Optimise Actual Spends", "Optimise Uploaded Spends"],
        key="data_upload_key",
        index=1,
    )
    # st.text_input("")

    # 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=0,
    #     on_change=reset_inputs,
    # )

    metrics_selected = "prospects"
    # Target
    target = name_formating(metrics_selected)

    # file_selected = (
    #     f"Overview_data_test_panel@#{metrics_selected}.xlsx"
    # )
    file_selected = None
    if data_selected == "Optimise Uploaded Spends":
        give_demo()
        st.write("Select a file to upload")

        uploaded_file = st.file_uploader("Choose an Excel file", type=["xlsx", "xls"])
        # give_demo()
        if uploaded_file:
            try:
                # Read the Excel file using pandas
                df = pd.read_excel(uploaded_file, engine="openpyxl")
                upload_file_format(df)
                file_selected = "Overview_data_uploaded.xlsx"

                # initialize_data(

                #     target_file=file_selected,
                #     panel="Total Market",
                #     updated_rcs=None,
                #     metrics=metrics_selected,
                # )
            except Exception as e:
                st.error(f"Error reading the file: {e}")

    elif data_selected == "Optimise Actual Spends":
        file_selected = f"Overview_data_test_panel@#{metrics_selected}.xlsx"
        # initialize_data(

        #             target_file=file_selected,
        #             panel="Total Market",
        #             updated_rcs=None,
        #             metrics=metrics_selected,
        #         )

    else:
        st.write("")

    if file_selected:
        st.session_state["file_selected"] = file_selected
        # Panel List
        panel_list = panel_fetch(file_selected)

        # # Panel Selected
        # panel_selected = st.selectbox(
        #     "Markets",
        #     ["Total Market"] + panel_list,
        #     index=0,
        #     on_change=reset_inputs,
        # )

        # st.write(panel_selected)
        panel_selected = "Total Market"
        st.session_state["selected_markets"] = panel_selected

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

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

        # Check if state is initiaized
        is_state_initiaized = st.session_state.get("initialized", False)
        if not is_state_initiaized or st.session_state["first_time"]:
            initialize_data(
                target_file=file_selected,
                panel=panel_selected,
                updated_rcs=updated_rcs,
                metrics=metrics_selected,
            )
            st.session_state["initialized"] = True
            st.session_state["first_time"] = False
            save_scenario("current scenario")

        # 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 = st.session_state["channels_list"]

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

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

        if "total_spends_change" not in st.session_state:
            st.session_state.total_spends_change = 0

        if "total_sales_change" not in st.session_state:
            st.session_state.total_sales_change = 0

        if "total_spends_change_abs" not in st.session_state:
            st.session_state["total_spends_change_abs"] = numerize(
                _scenario.actual_total_spends, 1
            )

        # st.write(_scenario.actual_total_sales)
        if "total_sales_change_abs" not in st.session_state:
            st.session_state["total_sales_change_abs"] = numerize(
                _scenario.actual_total_sales, 1
            )

        if "total_spends_change_abs_slider" not in st.session_state:
            st.session_state.total_spends_change_abs_slider = numerize(
                _scenario.actual_total_spends, 1
            )

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

        if "lower_bound_key" not in st.session_state:
            st.session_state["lower_bound_key"] = 10

        if "upper_bound_key" not in st.session_state:
            st.session_state["upper_bound_key"] = 100

        # st.write(_scenario.modified_total_sales)
        header_df = pd.DataFrame(
            index=["Actual", "Simulated", "Change", "Percent Change"],
            columns=["Spends", "Prospects"],
        )
        header_df["Spends"]["Actual"] = format_numbers(_scenario.actual_total_spends)
        header_df["Spends"]["Simulated"] = format_numbers(
            _scenario.modified_total_spends
        )
        header_df["Spends"]["Change"] = format_numbers(
            _scenario.delta_spends
        )  # _scenario.modified_total_spends -_scenario.actual_total_spends
        header_df["Spends"]["Percent Change"] = (
            numerize(100 * (_scenario.delta_spends / _scenario.actual_total_spends))
            + "%"
        )

        header_df["Prospects"]["Actual"] = format_numbers_f(
            float(_scenario.actual_total_sales)
        )
        header_df["Prospects"]["Simulated"] = format_numbers_f(
            float(_scenario.modified_total_sales)
        )
        header_df["Prospects"]["Change"] = format_numbers_f(_scenario.delta_sales)
        header_df["Prospects"]["Percent Change"] = (
            numerize(100 * (_scenario.delta_sales / _scenario.actual_total_sales), 1)
            + "%"
        )

        st.markdown("""<hr class="spends-heading-seperator">""", unsafe_allow_html=True)
        _columns = st.columns((1, 1, 1, 1, 1))
        st.markdown(
            """
                <style>
                .custom-text_head {
                    font-size: 24px; /* Adjust font size */
                    color: 'blue' ; /* Adjust text color */
                
                    font-weight: bold;
                }
                </style>
                """,
            unsafe_allow_html=True,
        )
        with _columns[0]:
            st.markdown(
                f'<p class="custom-text_head">{"Metrics"}</p>', unsafe_allow_html=True
            )
            # generate_spending_header("Metric")
        with _columns[1]:
            st.markdown(
                f'<p class="custom-text_head">{"Actual"}</p>', unsafe_allow_html=True
            )
            # generate_spending_header("Actual")
        with _columns[2]:
            st.markdown(
                f'<p class="custom-text_head">{"Simulated"}</p>', unsafe_allow_html=True
            )
            # generate_spending_header("Optimised")
        with _columns[3]:
            st.markdown(
                f'<p class="custom-text_head">{"Change"}</p>', unsafe_allow_html=True
            )
            # generate_spending_header("Change")
        with _columns[4]:
            st.markdown(
                f'<p class="custom-text_head">{"Change Percent"}</p>',
                unsafe_allow_html=True,
            )
            # generate_spending_header("Change Percent")
        st.markdown("""<hr class="spends-heading-seperator">""", unsafe_allow_html=True)

        _columns = st.columns((1, 1, 1, 1, 1))
        with _columns[0]:
            st.markdown("""<h4>Spends</h4>""", unsafe_allow_html=True)
            # st.write("Spends")
        with _columns[1]:
            st.markdown(
                f"""<h4>{header_df["Spends"]["Actual"]}</h4>""", unsafe_allow_html=True
            )
            # st.metric(label="", value=header_df["Spends"]["Actual"])
        with _columns[2]:
            st.markdown(
                f"""<h4>{header_df["Spends"]["Simulated"]}</h4>""",
                unsafe_allow_html=True,
            )
        if _scenario.delta_spends < 0:
            st.markdown(
                """
                <style>
                .custom-text {
                    font-size: 24px; /* Adjust font size */
                    color: #6bbf6b ; /* Adjust text color */
                }
                </style>
                """,
                unsafe_allow_html=True,
            )
        else:
            st.markdown(
                """
                <style>
                .custom-text {
                    font-size: 24px; /* Adjust font size */
                    color: #ff6868; /* Adjust text color */
                }
                </style>
                """,
                unsafe_allow_html=True,
            )
        # st.metric(label="", value=header_df["Spends"]["Simulated"])
        with _columns[3]:

            # Apply custom styles to text
            st.markdown(
                f'<h4 class="custom-text">{header_df["Spends"]["Change"]}</h4>',
                unsafe_allow_html=True,
            )
        with _columns[4]:
            # Apply custom styles to text
            # st.markdown(f'<p></hr></p>', unsafe_allow_html=True)
            st.markdown(
                f'<h4 class="custom-text">{header_df["Spends"]["Percent Change"]}</h4>',
                unsafe_allow_html=True,
            )
        st.markdown(
            """<hr class="spends-child-seperator">""",
            unsafe_allow_html=True,
        )
        _columns = st.columns((1, 1, 1, 1, 1))
        with _columns[0]:
            # st.header("Prospects")
            st.markdown("""<h4>Revenue</h4>""", unsafe_allow_html=True)
        with _columns[1]:
            st.markdown(
                f"""<h4>$ {header_df["Prospects"]["Actual"]}</h4>""",
                unsafe_allow_html=True,
            )
            # st.metric(label="", value=header_df["Prospects"]["Actual"])
        with _columns[2]:
            st.markdown(
                f"""<h4>$ {header_df["Prospects"]["Simulated"]}</h4>""",
                unsafe_allow_html=True,
            )
            # st.metric(label="", value=header_df["Prospects"]["Simulated"])

        if _scenario.delta_sales >= 0:
            st.markdown(
                """
                <style>
                .custom-text {
                    font-size: 24px; /* Adjust font size */
                    color:#6bbf6b ; /* Adjust text color */
                }
                </style>
                """,
                unsafe_allow_html=True,
            )
        else:
            st.markdown(
                """<style>.custom-text {font-size: 24px; /* Adjust font size */color: #ff6868; /* Adjust text color */}</style>""",
                unsafe_allow_html=True,
            )
        with _columns[3]:
            # Apply custom styles to text
            st.markdown(
                f'<h4 class="custom-text">$ {header_df["Prospects"]["Change"]}</h4>',
                unsafe_allow_html=True,
            )
            # st.markdown(f'<p style="color: red;">{st.metric(label="", value=header_df["Prospects"]["Change"])}</p>', unsafe_allow_html=True)
            # st.markdown(f'<p style="color: red;">{header_df["Prospects"]["Change"]}</p>', unsafe_allow_html=True)

        with _columns[4]:
            # st.markdown(f'<p></hr></p>', unsafe_allow_html=True)
            # Apply custom styles to text
            st.markdown(
                f'<h4 class="custom-text">{header_df["Prospects"]["Percent Change"]}</h4>',
                unsafe_allow_html=True,
            )
        st.markdown(
            """<hr class="spends-child-seperator">""",
            unsafe_allow_html=True,
        )

        _columns = st.columns((1, 1, 1, 1, 1))

        ef1 = _scenario.actual_total_sales/ _scenario.actual_total_spends
        ef2 = _scenario.modified_total_sales/ _scenario.modified_total_spends
        with _columns[0]:
            st.markdown("""<h4>ROI</h4>""", unsafe_allow_html=True)
            # st.header("Cost Per Prospect")
        with _columns[1]:
            st.markdown(f"""<h4>{numerize(ef1, 2)}</h4>""", unsafe_allow_html=True)
            # st.metric(label="", value='$ '+numerize(ef1,0))
        with _columns[2]:
            st.markdown(f"""<h4>{numerize(ef2, 2)}</h4>""", unsafe_allow_html=True)
            # st.metric(label="", value='$ '+numerize(ef2,0))

        if ef2 >= ef1:
            st.markdown(
                """
                <style>
                .custom-text1 {
                    font-size: 24px; /* Adjust font size */
                    color:#6bbf6b ; /* Adjust text color */
                }
                </style>
                """,
                unsafe_allow_html=True,
            )
        else:
            st.markdown(
                """
                <style>
                .custom-text1 {
                    font-size: 24px; /* Adjust font size */
                    color: #ff6868; /* Adjust text color */
                }
                </style>
                """,
                unsafe_allow_html=True,
            )

        with _columns[3]:

            # Apply custom styles to text
            st.markdown(
                f'<h4 class="custom-text1">{numerize(ef2-ef1, 2)}</h4>',
                unsafe_allow_html=True,
            )
            # st.markdown(f'<p style="color: red;">{st.metric(label="", value=header_df["Prospects"]["Change"])}</p>', unsafe_allow_html=True)
            # st.markdown(f'<p style="color: red;">{header_df["Prospects"]["Change"]}</p>', unsafe_allow_html=True)

        with _columns[4]:
            # st.markdown(f'<p></hr></p>', unsafe_allow_html=True)
            # Apply custom styles to text
            st.markdown(
                f'<h4 class="custom-text1">{round((ef2-ef1)/ef1*100, 2)}%</h4>',
                unsafe_allow_html=True,
            )
        st.markdown("""<hr class="spends-child-seperator">""", unsafe_allow_html=True)

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

        # header_df.reset_index(inplace=True)
        # # Function to color the index
        # def highlight_index(s):
        #     return ['background-color: lightblue' for _ in s]

        # # Function to color the header
        # def highlight_header(s):
        #     return ['background-color: lightgreen' for _ in s]

        # # Applying the styles
        # styled_df = header_df.style \
        #     .apply(highlight_index, axis=0, subset=pd.IndexSlice[:, :]) \
        #     .set_table_styles({
        #         'A': [{'selector': 'th', 'props': [('background-color', 'lightgreen')]}],
        #         'B': [{'selector': 'th', 'props': [('background-color', 'lightgreen')]}],
        #         'C': [{'selector': 'th', 'props': [('background-color', 'lightgreen')]}]
        #     })

        # # Function to apply arrows based on value
        # def format_arrows(val):
        #     if val > 0:
        #         return '<span style="color: green;">&#9650;</span>'  # Green up arrow
        #     elif val < 0:
        #         return '<span style="color: red;">&#9660;</span>'  # Red down arrow
        #     return ''  # No arrow for zero

        # # Function to format specific rows and exclude the first column
        # def apply_row_formatting(df, rows):
        #     def format_cell(val, row_idx, col_idx):
        #         if row_idx in rows and col_idx > 0:  # Exclude the first column (col_idx > 0)
        #             return format_arrows(val)
        #         return ''  # No formatting for other cells

        #     return df.style.apply(lambda x: [format_cell(val, i, col) for i, (val, col) in enumerate(zip(x, range(len(x))))], axis=1)

        # # Apply formatting to 3rd and 4th rows (index 2 and 3)
        # styled_df = apply_row_formatting(header_df, [2, 3])

        # st.markdown(styled_df.to_html(escape=False), unsafe_allow_html=True)

        # st.markdown(header_df.style.set_table_styles
        #             ([{'selector': 'th',
        #                 'props': [('background-color', '#D3D3D3'),
        #      ('font-size', '25px')]},
        #             {
        #                 'selector' : 'td:first-child',
        #                 'props' : [('background-color', '#D3D3D3'),
        #      ('font-size', '25px')]
        #             }
        #             ,
        #             {'selector': 'tbody td',
        #  'props': [('font-size', '20px')]}

        #             ]).to_html(),unsafe_allow_html=True)

        # styled_df = header_df.style.apply(highlight_first_col, axis=1)

        # st.table(styled_df)

        # 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(_scenario.actual_total_spends))

        # with sub_header[1]:
        #     st.metric(
        #         label=target,
        #         value=format_numbers_f(
        #             float(_scenario.actual_total_sales)
        #         ),
        #     )

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

        # with sub_header[3]:
        #     st.metric(
        #         label=target,
        #         value=format_numbers_f(
        #             float(_scenario.modified_total_sales)
        #         ),
        #         delta=numerize(_scenario.delta_sales, 1),
        #     )

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

            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.write("#")
                st.checkbox(
                    label="Optimize all Channels",
                    key="optimze_all_channels",
                    value=False,
                    on_change=select_all_channels_for_optimization,
                )

            with _columns1[2]:
                st.write("####")
                optimize_placeholder = st.empty()

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

            _columns2 = st.columns((2, 2, 2, 2))
            if st.session_state["optimization_key"] == "Media Spends":
                # st.write(overall_lower_bound,overall_upper_bound)

                with _columns2[2]:
                    overall_lower_bound = st.number_input(
                        "Overall Lower Bound for Spends",
                        value=30.0,
                        min_value=0.0,
                        max_value=30.0,
                        key="overall_lower_bound",
                        # on_change=partial(update_data_bound_min_overall)
                    )
                with _columns2[3]:
                    overall_upper_bound = st.number_input(
                        "Overall Upper Bound for Spends",
                        value=30.0,
                        min_value=0.0,
                        max_value=30.0,
                        key="overall_upper_bound",
                        # on_change=partial(update_data_bound_max_overall)
                    )

                min_value = round(
                    _scenario.actual_total_spends * (1 - overall_lower_bound / 100)
                )
                max_value = round(
                    _scenario.actual_total_spends * (1 - overall_upper_bound / 100)
                )
                with _columns2[0]:
                    spend_input = st.text_input(
                        "Absolute",
                        key="total_spends_change_abs",
                        # label_visibility="collapsed",
                        on_change=update_all_spends_abs,
                    )
                # st.write(min_value,max_value)
                # overall_lower_bound = 50.0
                # overall_upper_bound = 50.0
                with _columns2[1]:
                    st.number_input(
                        "Percent Change",
                        key="total_spends_change",
                        # min_value=-1 * overall_lower_bound,
                        # max_value=overall_upper_bound,
                        min_value=-overall_lower_bound,
                        max_value=overall_upper_bound,
                        step=0.01,
                        value=0.00,
                        on_change=update_spends,
                    )

                    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"] == target:
                # st.write(target)
                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 aaChange",
                        key="total_sales_change",
                        min_value=-50.00,
                        max_value=50.00,
                        step=0.01,
                        value=0.00,
                        on_change=update_sales,
                    )
                with _columns2[2]:
                    overall_lower_bound = st.number_input(
                        "Overall Lower Bound for Spends", value=50
                    )
                with _columns2[3]:

                    overall_upper_bound = st.number_input(
                        "Overall Upper Bound for Spends", value=50
                    )

                    min_value = round(
                        _scenario.actual_total_sales * (1 - overall_lower_bound / 100)
                    )
                    max_value = round(
                        _scenario.actual_total_sales * (1 + overall_upper_bound / 100)
                    )
                    # st.write(min_value)
                    # st.write(max_value)
                    # for value in range(min_value, max_value + 1, int(100)):
                    #     st.write(numerize(value, 1))
                    # st.session_state["total_sales_change_abs_slider_options"] = [
                    #     numerize(value, 1)
                    #     for value in range(min_value, max_value + 1, int(100))
                    # ]

                    # 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,
                    #     # value=numerize(min_value, 1)
                    # )

            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"], status_placeholder)
            #     st.rerun()

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

            st.markdown(
                """<hr class="spends-heading-seperator">""", unsafe_allow_html=True
            )
            _columns = st.columns((2, 1.5, 3, 3, 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("Revenue" if target == "Prospects" else 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": [],
                    "New_sales": [],
                    "Old_sales": [],
                }
            for i, channel_name in enumerate(channels_list):

                if channel_name == "Email":
                    continue
                
                # st.write(channel_name)
                _channel_class = st.session_state["scenario"].channels[channel_name]
                # st.write(st.session_state["scenario"].channels[channel_name])
                # st.write(st.session_state["scenario"].channels[channel_name].actual_total_sales)
                # st.write(st.session_state["scenario"].channels[channel_name].actual_total_spends)
                # st.write(st.session_state["scenario"].channels[channel_name].modified_total_sales)
                # st.write(st.session_state["scenario"].channels[channel_name].modified_total_spends)
                # st.write(st.session_state["scenario"].channels[channel_name].bounds)
                # st.write(st.session_state["scenario"].channels[channel_name].channel_bounds_min)

                _columns = st.columns((2, 1.5, 3, 3, 1))
                response_curve_params = pd.read_excel(
                    "response_curves_parameters.xlsx", index_col="channel"
                )
                param_dicts = {
                    col: response_curve_params[col].to_dict()
                    for col in response_curve_params.columns
                }

                with _columns[0]:
                    st.write(
                        channel_name_formating(channel_name).replace(
                            "Connected & OTTTV", "Connected & OTT TV"
                        )
                    )
                    bin_placeholder = st.container()

                with _columns[1]:
                    channel_bounds = _channel_class.bounds
                    # st.write(channel_bounds)
                    channel_spends = float(_channel_class.actual_total_spends)
                    channel_bounds_min = float(_channel_class.channel_bounds_min)
                    channel_bounds_max = float(_channel_class.channel_bounds_max)
                    min_value = float((1 - channel_bounds_min / 100) * channel_spends)
                    max_value = float((1 + channel_bounds_max / 100) * channel_spends)
                    # st.write(channel_spends)
                    # st.write(min_value)
                    # st.write(max_value)
                    ### # # print(st.session_state[channel_name])
                    # st.write(_channel_class.channel_bounds_min,channel_bounds_min)
                    # st.write(_channel_class.channel_bounds_max,channel_bounds_max)
                    _columns_min = st.columns(1)
                    with _columns_min[0]:
                        spend_input = st.text_input(
                            "Absolute",
                            key=channel_name,
                            # label_visibility="collapsed",
                            on_change=partial(update_data, channel_name),
                        )
                        channel_name_lower_bound = f"{channel_name}_lower_bound"

                        if channel_name_lower_bound not in st.session_state:
                            st.session_state[channel_name_lower_bound] = str(
                                round(
                                    param_dicts["x_min"][channel_name]
                                    * 10400
                                    / param_dicts["current_spends"][channel_name]
                                )
                            )
                        # # st.write(st.session_state[channel_name_lower_bound])
                        # channel_bounds_min = st.text_input(
                        #     "Lower Bound Percentage",
                        #     key=channel_name_lower_bound,
                        #     on_change=partial(update_data_bound_min, channel_name),
                        # )

                        channel_bounds_min = 30.0
                        # st.write(st.session_state[channel_name_lower_bound])
                        # if not validate_input_lb(str(channel_bounds_min)):
                        #     st.error("Invalid input")
                        if not validate_input(spend_input):
                            st.error("Invalid input")

                    def calc_min_value():
                        return float(st.session_state[channel_name_upper_bound])

                    channel_name_current = f"{channel_name}_change"
                    with _columns_min[0]:
                        channel_name_upper_bound = f"{channel_name}_upper_bound"
                        if channel_name_upper_bound not in st.session_state:
                            st.session_state[channel_name_upper_bound] = str(100)
                        # st.write(float(st.session_state[channel_name_lower_bound]),float(st.session_state[channel_name_upper_bound]),)
                        st.number_input(
                            "Percent Change",
                            key=channel_name_current,
                            step=1.00,
                            value=0.00,
                            on_change=partial(update_data_by_percent, channel_name),
                            max_value=st.session_state["overall_upper_bound"],
                            min_value=-st.session_state["overall_lower_bound"],
                        )

                        # channel_bounds_max = st.text_input(
                        #     "Upper Bound Percentage",
                        #     key=channel_name_upper_bound,
                        #     on_change=partial(update_data_bound_max, channel_name),
                        # )
                        # if not validate_input(channel_bounds_max):
                        #     st.error("Invalid input")
                        channel_bounds_max = 30.0

                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
                        _channel_class.delta_spends
                    )
                    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)
                    _spend_cols = st.columns(2)
                    with _spend_cols[0]:
                        # st.write("Actual")
                        st.markdown(
                            f"<p> Actual Spends<h5>{format_numbers(actual_channel_spends)}</h5> </p>",
                            unsafe_allow_html=True,
                        )
                        # st.metric(
                        #     label="Actual Spends",
                        #     value=format_numbers(actual_channel_spends),
                        #     # delta=numerize(spends_delta, 1),
                        #     # label_visibility="collapsed",
                        # )

                        # st.write("Actual")
                        st.markdown(
                            f"<p>Spends Change<h5>{format_numbers(spends_delta)}</h5> </p>",
                            unsafe_allow_html=True,
                        )
                        # st.markdown(f'<h4 class="custom-text1">{format_numbers(spends_delta)}%</h4>', unsafe_allow_html=True)

                        # st.metric(
                        #     label="Change",
                        #     value= format_numbers_f(spends_delta),
                        #     delta=numerize(spends_delta, 1),
                        #     # label_visibility="collapsed",
                        # )
                    with _spend_cols[1]:
                        st.markdown(
                            f"<p>Simulated Spends<h5>{format_numbers(current_channel_spends)}</h5> </p>",
                            unsafe_allow_html=True,
                        )
                        st.markdown(
                            f'<p>Spends Percent<h5>{numerize(( spends_delta/actual_channel_spends)*100,0) +"%"}</h5> </p>',
                            unsafe_allow_html=True,
                        )
                        # st.metric(
                        #     label="Simulated Spends",
                        #     value=format_numbers(current_channel_spends),
                        #     # delta=numerize(spends_delta, 1),
                        #     # label_visibility="collapsed",
                        # )

                        # st.metric(
                        #     label="Percent Change",
                        #     value=  numerize(( spends_delta/actual_channel_spends)*100,0) +"%",
                        #     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.session_state["acutual_predicted"]["Old_sales"].append(
                        actual_channel_sales
                    )
                    st.session_state["acutual_predicted"]["New_sales"].append(
                        current_channel_sales
                    )

                    # st.write(actual_channel_sales)

                    _prospect_cols = st.columns(2)
                    with _prospect_cols[0]:
                        # st.write("Actual")
                        st.markdown(
                            f"<p>Actual Revenue<h5>$ {format_numbers_f(actual_channel_sales)}</h5> </p>",
                            unsafe_allow_html=True,
                        )
                        st.markdown(
                            f"<p>Revenue Change<h5>$ {format_numbers_f(sales_delta)}</h5> </p>",
                            unsafe_allow_html=True,
                        )

                    #     st.metric(
                    #         # target,
                    #         label="Actual Prospects",
                    #         value= format_numbers_f(actual_channel_sales),
                    #         # delta=numerize(sales_delta, 1),
                    #         # label_visibility="collapsed",
                    #     )
                    #     st.metric(
                    #         label="Change",
                    #         value= format_numbers_f(_channel_class.delta_sales),
                    #         delta=numerize(sales_delta, 1),
                    #         # label_visibility="collapsed",
                    #     )
                    with _prospect_cols[1]:
                        st.markdown(
                            f"<p>Simulated Revenue<h5>$ {format_numbers_f(current_channel_sales)}</h5> </p>",
                            unsafe_allow_html=True,
                        )
                        st.markdown(
                            f'<p>Revenue Percent<h5>{numerize(( _channel_class.delta_sales/actual_channel_sales)*100,0) +"%"}</h5> </p>',
                            unsafe_allow_html=True,
                        )

                    #     st.metric(
                    #     label="Simulated Prospects",
                    #     value= format_numbers_f(current_channel_sales),
                    #     # delta=numerize(sales_delta, 1),
                    #     # label_visibility="collapsed",
                    #     )

                    #     st.metric(
                    #         label="Percent Change",
                    #         value=  numerize((_channel_class.delta_sales/actual_channel_sales)*100,0) +"%",
                    #         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_modified_total = 0
                # for c in channels_list:
                #     # st.write(c)
                #     # st.write(st.session_state["scenario"].channels[c].modified_spends)
                #     x_modified_total = x_modified_total + st.session_state["scenario"].channels[c].modified_spends.sum()
                # st.write(x_modified_total)

                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 = (y/np.sum(y))/(x/np.sum(x))
                # # st.write(x)
                # # st.write(y)
                # # st.write(roi)

                # # st.write(roi[-1])

                # 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

                # # roi_current =

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

                # st.write(roi_current)

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

                # # # # print(st.session_state["acutual_predicted"])
                summary_df = pd.DataFrame(st.session_state["acutual_predicted"])
                # (pd.DataFrame(st.session_state["acutual_predicted"])).to_excel("test.xlsx")
                # st.dataframe(summary_df)
                summary_df.drop_duplicates(
                    subset="Channel_name", keep="last", inplace=True
                )
                # st.dataframe(summary_df)

                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,
                )

                summary_df_sorted = summary_df_sorted.sort_values(
                    by=["Optimized_spend"], ascending=False
                )
                summary_df_sorted["old_efficiency"] = (
                    summary_df_sorted["Old_sales"]
                    / summary_df_sorted["Actual_spend"].sum()
                ) / (
                    summary_df_sorted["Actual_spend"]
                    / summary_df_sorted["Actual_spend"].sum()
                )
                summary_df_sorted["new_efficiency"] = (
                    summary_df_sorted["New_sales"]
                    / summary_df_sorted["Optimized_spend"].sum()
                ) / (
                    summary_df_sorted["Optimized_spend"]
                    / summary_df_sorted["Optimized_spend"].sum()
                )

                a = (
                    summary_df_sorted[summary_df_sorted["Channel_name"] == col]
                ).reset_index()["new_efficiency"][0]
                b = (
                    summary_df_sorted[summary_df_sorted["Channel_name"] == col]
                ).reset_index()["old_efficiency"][0]
                # st.write(a)
                # # print(a)
                # # print(summary_df_sorted['Actual_spend'].sum())
                # # print(summary_df_sorted['Actual_spend'])
                # # print(col,summary_df_sorted)
                # # print(summary_df_sorted['Old_sales'])
                # # print(col, "old efficiency ", a)
                with bin_placeholder:
                    if a > 1:
                        fill_color_box = "#6bbf6b"
                    elif a <= 1:
                        fill_color_box = "#ff6868"
                    else:
                        fill_color_box = "#ff6868"
                    st.markdown(
                        f"""
                        <div style="
                            border-radius: 12px;
                            background-color: {fill_color_box};
                            padding: 10px;
                            text-align: center;
                            color: {'black'};
                            ">
                            <p style="margin: 0; font-size: 16px;">Simulated ROI: {round(a,2)} </br> Actual ROI: {round(b,2)} </p>
                            <!--<p style="margin: 0; font-size: 16px;">Marginal ROI: {round(b,1)}</p>-->
                        </div>
                        """,
                        unsafe_allow_html=True,
                    )
                    # <p style="margin: 0; font-size: 16px;">Simulated Efficiency: {round(a,2)} </br> Actual Efficiency: {round(b,2)} </p>
                    #         <!--<p style="margin: 0; font-size: 16px;">Marginal ROI: {round(b,1)}</p>-->

        with st.expander("See Response Curves", expanded=True):
            fig = plot_response_curves(summary_df_sorted)
            # st.plotly_chart(rc.response_curves(col))
            # st.plotly_chart(fig, use_container_width=True)

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

        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)

        scenario_planner_plots()

        # with st.expander ("View Forecasted spends"):
        #     # st.write("Select Time Period")

        #     options = ["Next Month","Next Quarter","Next Year","Custom Time Period"]

        #     # # Create the radio button
        #     forecast_btn_op = st.radio("Select Time Period", options)

        #     # List of 12 months
        #     months_start = ["January", "February", "March", "April", "May", "June",
        #                 "July", "August", "September", "October", "November", "December"]
        #     years_start = range(2022,2025)
        #     months_end = ["January", "February", "March", "April", "May", "June",
        #                 "July", "August", "September", "October", "November", "December"]
        #     years_end = range(2022,2025)

        #     if forecast_btn_op == "Custom Time Period":
        #         col1, col2, col3 = st.columns([1,1,0.75])

        #         with col1:
        #             from datetime import date
        #             st.write ("Select Start Time Period")
        #             sc1,sc2 = st.columns([1,1])

        #             with sc1:
        #             # Create a dropdown (selectbox) for months
        #                 start_date_mon = st.selectbox("Select Start Month:", months_start)
        #             with sc2:
        #                 start_date_year = st.selectbox("Select Start Year:", years_start,index=2)
        #             start_date1 = date(start_date_year, months_start.index(start_date_mon)+1, 1)
        #             # -  relativedelta(years=1)
        #             # st.write(start_date1)
        #             # default_Month = "January"
        #             # start_date_mon = st.text_input("Select Start Month: ",value=default_Month)

        #             # default_Year = 2024
        #             # start_date_year = st.number_input("Select Start Year: ",value=default_Year)

        #         with col2:
        #             st.write ("Select End Time Period")
        #             ec1,ec2 = st.columns([1,1])
        #             with ec1:
        #                 end_date_mon = st.selectbox("Select End Month:", months_end,index=1)
        #             with ec2:
        #                 end_date_year = st.selectbox("Select End Year:", years_end,index=2)
        #             end_date1 = date(end_date_year, months_end.index(end_date_mon)+1, 1)+ relativedelta(months=1) - relativedelta(days=1)
        #             # -  relativedelta(years=1)
        #             # st.write(end_date1)
        #             # default_Month = "February"
        #             # end_date_mon = st.text_input("Select End Month: ",value=default_Month)

        #             # default_Year = 2024
        #             # end_date_year = st.number_input("Select End Year: ",value=default_Year)
        #             # end_date1 = st.date_input("Select End Date: ",value=default_date) -  relativedelta(years=1)
        #     elif forecast_btn_op == 'Next Month':
        #         # current_date = datetime.now()
        #         # start_date1  = current_date-  relativedelta(years=1)
        #         # end_date1 = current_date + relativedelta(months=1)-  relativedelta(years=1)
        #         start_date1,end_date1 = first_day_of_next_month(datetime.now())
        #         # start_date1 = start_date1-  relativedelta(years=1)
        #         # end_date1 = end_date1 - relativedelta(years=1)
        #     elif forecast_btn_op == 'Next Quarter':
        #         # current_date = datetime.now()
        #         # start_date1  = current_date-  relativedelta(years=1)
        #         # end_date1 = current_date + relativedelta(months = 3)-  relativedelta(years=1)
        #         start_date1,end_date1 = first_day_of_next_quarter(datetime.now())
        #         # start_date1 = start_date1-  relativedelta(years=1)
        #         # end_date1 = end_date1 - relativedelta(years=1)
        #     elif forecast_btn_op == 'Next Year':
        #         # current_date = datetime.now()
        #         # start_date1  = current_date-  relativedelta(years=1)
        #         # end_date1 = current_date + relativedelta(months = 12)-  relativedelta(years=1)
        #         start_date1,end_date1 = first_day_of_next_year(datetime.now())
        #         # start_date1 = start_date1-  relativedelta(years=1)
        #         # end_date1 = end_date1 - relativedelta(years=1)
        #     if st.button('Generate Forecasts'):
        #         st.write(f"Forecasted Spends Time Period : {start_date1.strftime('%m-%d-%Y')} to {end_date1.strftime('%m-%d-%Y')}")
        #         if end_date1 < start_date1 :
        #             st.error("End date cannot be less than start date")
        #         forecasted_table_df2 = pd.DataFrame()
        #         try:

        #             st.write("Forecasted Spends wrt. Channels ")
        #             output_df1, output_df2 = sf.scenario_spend_forecasting(summary_df_sorted,start_date1-  relativedelta(years=1),end_date1-  relativedelta(years=1))
        #             forecasted_table_df   = output_df1.copy()
        #             # forecasted_table_df.iloc[:2] = forecasted_table_df.iloc[:2].applymap(lambda x: "{:,.0f}".format(x))
        #             # forecasted_table_df.iloc[-1] = forecasted_table_df.iloc[-1].apply(lambda x: "{:.1f}%".format(x))
        #             st.dataframe(forecasted_table_df)

        #             st.write("Monthly Breakdown Of Forecasted Spends wrt. Channels ")
        #             # forecasted_table_df2 = output_df2.applymap(lambda x: "{:,.0f}".format(x))
        #             st.dataframe(output_df2)

        #             st.subheader("Download Report")
        #             report_name = st.text_input(
        #                     "Report name",
        #                     key="report_input",
        #                     placeholder="Report name",
        #                     label_visibility="collapsed",
        #                 )
        #             st.download_button(
        #             "Download Report",
        #             data = save_report_forecast(output_df1,output_df2),
        #             file_name = report_name+".xlsx",
        #             mime="application/vnd.ms-excel",
        #             # on_click=lambda: save_report_forecast(forecasted_table_df,report_name),
        #             disabled=len(st.session_state["report_input"]) == 0,#use_container_width=True
        #             )

        #         except:
        #             st.warning("Please make sure the base data is updated")

        #     # filename = st.text_input("Save Report: ",placeholder="Report name")
        #     # if st.button("Download Report",disabled= (filename != "Report name")):
        #     #     excel_file_path = filename+ '.xlsx'
        #     #     forecasted_table_df.to_excel(excel_file_path, index=False)
        #     #     message_container = st.empty()

        #     #     with message_container:
        #     #         st.write(f'<div class="yellow-container">{"Report Saved!"}</div>', unsafe_allow_html=True)
        #     #         time.sleep(0.5)
        #     #         st.empty()

        #     # on_click=lambda: save_scenario(scenario_name),
        #     # disabled=len(st.session_state["scenario_input"]) == 0,#use_container_width=True

        _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,  # use_container_width=True
        )
        # def prepare_download_func():

        #     fig1,fig2,fig3 = scenario_planner_plots()

        #     ppt_file =  save_ppt_file(summary_df_sorted,fig1,fig2,fig3)

        if st.button("Prepare Analysis Download"):
            fig1, fig2, fig3 = scenario_planner_plots2()
            ppt_file = save_ppt_file(summary_df_sorted, fig1, fig2, fig3)
            # Add a download button
            try:
                # ppt_file = prepare_download_func()
                st.download_button(
                    label="Download Response Curves And Optimised Spends Overview",
                    data=ppt_file,
                    file_name="MMM_Scenario_Planner_Presentation.pptx",
                    mime="application/vnd.openxmlformats-officedocument.presentationml.presentation",
                )
            except:
                st.write("")
            # ppt_file =  save_ppt_file()
            #     # Add a download button
            # st.download_button(
            #         label="Download Analysis",
            #         data=ppt_file,
            #         file_name="MMM_Model_Quality_Presentation.pptx",
            #         mime="application/vnd.openxmlformats-officedocument.presentationml.presentation"
            #     )


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)