ppt_utils.py

import pandas as pd
import numpy as np
import pptx
from pptx import Presentation
from pptx.chart.data import CategoryChartData, ChartData
from pptx.enum.chart import XL_CHART_TYPE, XL_LEGEND_POSITION, XL_LABEL_POSITION
from pptx.enum.chart import XL_TICK_LABEL_POSITION
from pptx.util import Inches, Pt
import os
import pickle
from pathlib import Path
from sklearn.metrics import (
    mean_absolute_error,
    r2_score,
    mean_absolute_percentage_error,
)
import streamlit as st
from collections import OrderedDict
from utilities import get_metrics_names, initialize_data, retrieve_pkl_object_without_warning
from io import BytesIO
from pptx.dml.color import RGBColor
from post_gres_cred import db_cred
schema=db_cred['schema']

from constants import (
    TITLE_FONT_SIZE,
    AXIS_LABEL_FONT_SIZE,
    CHART_TITLE_FONT_SIZE,
    AXIS_TITLE_FONT_SIZE,
    DATA_LABEL_FONT_SIZE,
    LEGEND_FONT_SIZE,
    PIE_LEGEND_FONT_SIZE
)


def format_response_metric(target):
    if target.startswith('response_metric_'):
        target = target.replace('response_metric_', '')
    target = target.replace("_", " ").title()
    return target


def smape(actual, forecast):
    # Symmetric Mape (SMAPE) eliminates shortcomings of MAPE :
    ## 1. MAPE becomes insanely high when actual is close to 0
    ## 2. MAPE is more favourable to underforecast than overforecast
    return (1 / len(actual)) * np.sum(1 * np.abs(forecast - actual) / (np.abs(actual) + np.abs(forecast)))


def safe_num_to_per(num):
    try:
        return "{:.0%}".format(num)
    except:
        return num


# Function to convert numbers to abbreviated format
def convert_number_to_abbreviation(number):
    try:
        number = float(number)
        if number >= 1000000:
            return f'{number / 1000000:.1f} M'
        elif number >= 1000:
            return f'{number / 1000:.1f} K'
        else:
            return str(number)
    except:
        return number


def round_off(x, round_off_decimal=0):
    # round off
    try:
        x = float(x)
        if x < 1 and x > 0:
            round_off_decimal = int(np.floor(np.abs(np.log10(x)))) + max(round_off_decimal, 1)
            x = np.round(x, round_off_decimal)
        elif x < 0 and x > -1:
            round_off_decimal = int(np.floor(np.abs(np.log10(np.abs(x))))) + max(round_off_decimal, 1)
            x = -np.round(x, round_off_decimal)
        else:
            x = np.round(x, round_off_decimal)
        return x
    except:
        return x


def fill_table_placeholder(table_placeholder, slide, df, column_width=None, table_height=None):
    cols = len(df.columns)
    rows = len(df)

    if table_height is None:
        table_height = table_placeholder.height

    x, y, cx, cy = table_placeholder.left, table_placeholder.top, table_placeholder.width, table_height
    table = slide.shapes.add_table(rows + 1, cols, x, y, cx, cy).table

    # Populate the table with data from the DataFrame
    for row_idx, row in enumerate(df.values):
        for col_idx, value in enumerate(row):
            cell = table.cell(row_idx + 1, col_idx)
            cell.text = str(value)
    for col_idx, value in enumerate(df.columns):
        cell = table.cell(0, col_idx)
        cell.text = str(value)

    if column_width is not None:
        for col_idx, column_width in column_width.items():
            table.columns[col_idx].width = Inches(column_width)

    table_placeholder._element.getparent().remove(table_placeholder._element)


def bar_chart(chart_placeholder, slide, chart_data, titles={}, min_y=None, max_y=None, type='V', legend=True,

              label_type=None, xaxis_pos=None):
    x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height
    if type == 'V':
        graphic_frame = slide.shapes.add_chart(
            XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
        )
    if type == 'H':
        graphic_frame = slide.shapes.add_chart(
            XL_CHART_TYPE.BAR_CLUSTERED, x, y, cx, cy, chart_data
        )
    chart = graphic_frame.chart

    category_axis = chart.category_axis
    value_axis = chart.value_axis

    # Add chart title
    if 'chart_title' in titles.keys():
        chart.has_title = True
        chart.chart_title.text_frame.text = titles['chart_title']
        chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0]
        chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE)

    # Add axis titles
    if 'x_axis' in titles.keys():
        category_axis.has_title = True
        category_axis.axis_title.text_frame.text = titles['x_axis']
        category_title = category_axis.axis_title.text_frame.paragraphs[0].runs[0]
        category_title.font.size = Pt(AXIS_TITLE_FONT_SIZE)

    if 'y_axis' in titles.keys():
        value_axis.has_title = True
        value_axis.axis_title.text_frame.text = titles['y_axis']
        value_title = value_axis.axis_title.text_frame.paragraphs[0].runs[0]
        value_title.font.size = Pt(AXIS_TITLE_FONT_SIZE)

    if xaxis_pos == 'low':
        category_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW

    # Customize the chart
    if legend:
        chart.has_legend = True
        chart.legend.position = XL_LEGEND_POSITION.BOTTOM
        chart.legend.font.size = Pt(LEGEND_FONT_SIZE)
        chart.legend.include_in_layout = False

    # Adjust font size for axis labels
    category_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE)
    value_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE)

    if min_y is not None:
        value_axis.minimum_scale = min_y  # Adjust this value as needed

    if max_y is not None:
        value_axis.maximum_scale = max_y  # Adjust this value as needed

    plot = chart.plots[0]
    plot.has_data_labels = True
    data_labels = plot.data_labels

    if label_type == 'per':
        data_labels.number_format = '0"%"'
    elif label_type == '$':
        data_labels.number_format = '$[>=1000000]#,##0.0,,"M";$[>=1000]#,##0.0,"K";$#,##0'
    elif label_type == '$1':
        data_labels.number_format = '$[>=1000000]#,##0,,"M";$[>=1000]#,##0,"K";$#,##0'
    elif label_type == 'M':
        data_labels.number_format = '#0.0,,"M"'
    elif label_type == 'M1':
        data_labels.number_format = '#0.00,,"M"'
    elif label_type == 'K':
        data_labels.number_format = '#0.0,"K"'

    data_labels.font.size = Pt(DATA_LABEL_FONT_SIZE)

    chart_placeholder._element.getparent().remove(chart_placeholder._element)


def line_chart(chart_placeholder, slide, chart_data, titles={}, min_y=None, max_y=None):
    # Add the chart to the slide
    x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height

    chart = slide.shapes.add_chart(
        XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
    ).chart

    chart.has_legend = True
    chart.legend.position = XL_LEGEND_POSITION.BOTTOM
    chart.legend.font.size = Pt(LEGEND_FONT_SIZE)

    category_axis = chart.category_axis
    value_axis = chart.value_axis

    if min_y is not None:
        value_axis.minimum_scale = min_y

    if max_y is not None:
        value_axis.maximum_scale = max_y

    if min_y is not None and max_y is not None:
        value_axis.major_unit = int((max_y - min_y) / 2)

    if 'chart_title' in titles.keys():
        chart.has_title = True
        chart.chart_title.text_frame.text = titles['chart_title']
        chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0]
        chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE)

    if 'x_axis' in titles.keys():
        category_axis.has_title = True
        category_axis.axis_title.text_frame.text = titles['x_axis']
        category_title = category_axis.axis_title.text_frame.paragraphs[0].runs[0]
        category_title.font.size = Pt(AXIS_TITLE_FONT_SIZE)

    if 'y_axis' in titles.keys():
        value_axis.has_title = True
        value_axis.axis_title.text_frame.text = titles['y_axis']
        value_title = value_axis.axis_title.text_frame.paragraphs[0].runs[0]
        value_title.font.size = Pt(AXIS_TITLE_FONT_SIZE)

    # Adjust font size for axis labels
    category_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE)
    value_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE)

    plot = chart.plots[0]
    series = plot.series[1]
    line = series.format.line
    line.color.rgb = RGBColor(141, 47, 0)

    chart_placeholder._element.getparent().remove(chart_placeholder._element)


def pie_chart(chart_placeholder, slide, chart_data, title):
    # Add the chart to the slide
    x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height

    chart = slide.shapes.add_chart(
        XL_CHART_TYPE.PIE, x, y, cx, cy, chart_data
    ).chart

    chart.has_legend = True
    chart.legend.position = XL_LEGEND_POSITION.RIGHT
    chart.legend.include_in_layout = False
    chart.legend.font.size = Pt(PIE_LEGEND_FONT_SIZE)

    chart.plots[0].has_data_labels = True
    data_labels = chart.plots[0].data_labels
    data_labels.number_format = '0%'
    data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
    data_labels.font.size = Pt(DATA_LABEL_FONT_SIZE)

    chart.has_title = True
    chart.chart_title.text_frame.text = title
    chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0]
    chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE)

    chart_placeholder._element.getparent().remove(chart_placeholder._element)


def title_and_table(slide, title, df, column_width=None, custom_table_height=False):
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = title
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    table_placeholder = slide.placeholders[ph_idx[1]]

    table_height = None
    if custom_table_height:
        if len(df) < 4:
            table_height = int(np.ceil(table_placeholder.height / 2))

    fill_table_placeholder(table_placeholder, slide, df, column_width, table_height)

    # try:
    #     font_size = 18  # default for 3*3
    #     if cols < 3:
    #         row_diff = 3 - rows
    #         font_size = font_size + ((row_diff)*2) # 1 row less -> 2 pt font size increase & vice versa
    #     else:
    #         row_diff = 2 - rows
    #         font_size = font_size + ((row_diff)*2)
    #     for row in table.rows:
    #         for cell in row.cells:
    #             cell.text_frame.paragraphs[0].runs[0].font.size = Pt(font_size)
    # except Exception as e :
    #     print("**"*30)
    #     print(e)
    # else:
    # except Exception as e:
    #     print('table', e)
    return slide


def data_import(data, bin_dict):
    import_df = pd.DataFrame(columns=['Category', 'Value'])

    import_df.at[0, 'Category'] = 'Date Range'

    date_start = data['date'].min().date()
    date_end = data['date'].max().date()
    import_df.at[0, 'Value'] = str(date_start) + ' - ' + str(date_end)

    import_df.at[1, 'Category'] = 'Response Metrics'
    import_df.at[1, 'Value'] = ', '.join(bin_dict['Response Metrics'])

    import_df.at[2, 'Category'] = 'Media Variables'
    import_df.at[2, 'Value'] = ', '.join(bin_dict['Media'])

    import_df.at[3, 'Category'] = 'Spend Variables'
    import_df.at[3, 'Value'] = ', '.join(bin_dict['Spends'])

    if bin_dict['Exogenous'] != []:
        import_df.at[4, 'Category'] = 'Exogenous Variables'
        import_df.at[4, 'Value'] = ', '.join(bin_dict['Exogenous'])

    return import_df


def channel_groups_df(channel_groups_dct={}, bin_dict={}):
    df = pd.DataFrame(columns=['Channel', 'Media Variables', 'Spend Variables'])
    i = 0
    for channel, vars in channel_groups_dct.items():
        media_vars = ", ".join(list(set(vars).intersection(set(bin_dict["Media"]))))
        spend_vars = ", ".join(list(set(vars).intersection(set(bin_dict["Spends"]))))
        df.at[i, "Channel"] = channel
        df.at[i, 'Media Variables'] = media_vars
        df.at[i, 'Spend Variables'] = spend_vars
        i += 1

    return df


def transformations(transform_dict):
    transform_df = pd.DataFrame(columns=['Category', 'Transformation', 'Value'])
    i = 0

    for category in ['Media', 'Exogenous']:
        transformations = f'transformation_{category}'
        category_dict = transform_dict[category]
        if transformations in category_dict.keys():
            for transformation in category_dict[transformations]:
                transform_df.at[i, 'Category'] = category
                transform_df.at[i, 'Transformation'] = transformation
                transform_df.at[i, 'Value'] = str(category_dict[transformation][0]) + ' - ' + str(
                    category_dict[transformation][1])
                i += 1
    return transform_df


def model_metrics(model_dict, is_panel):
    metrics_df = pd.DataFrame(
        columns=[
            "Response Metric",
            "Model",
            "R2",
            "ADJR2",
            "Train MAPE",
            "Test MAPE"
        ]
    )
    i = 0
    for key in model_dict.keys():
        target = key.split("__")[1]
        metrics_df.at[i, "Response Metric"] = format_response_metric(target)
        metrics_df.at[i, "Model"] = key.split("__")[0]

        y = model_dict[key]["X_train_tuned"][target]

        feature_set = model_dict[key]["feature_set"]

        if is_panel:
            random_df = get_random_effects(
                media_data, panel_col, model_dict[key]["Model_object"]
            )
            pred = mdf_predict(
                model_dict[key]["X_train_tuned"],
                model_dict[key]["Model_object"],
                random_df,
            )["pred"]
        else:
            pred = model_dict[key]["Model_object"].predict(model_dict[key]["X_train_tuned"][feature_set])

        ytest = model_dict[key]["X_test_tuned"][target]
        if is_panel:

            predtest = mdf_predict(
                model_dict[key]["X_test_tuned"],
                model_dict[key]["Model_object"],
                random_df,
            )["pred"]

        else:
            predtest = model_dict[key]["Model_object"].predict(model_dict[key]["X_test_tuned"][feature_set])

        metrics_df.at[i, "R2"] = np.round(r2_score(y, pred), 2)
        adjr2 = 1 - (1 - metrics_df.loc[i, "R2"]) * (
                len(y) - 1
        ) / (len(y) - len(model_dict[key]["feature_set"]) - 1)
        metrics_df.at[i, "ADJR2"] = np.round(adjr2, 2)
        # y = np.where(np.abs(y) < 0.00001, 0.00001, y)
        metrics_df.at[i, "Train MAPE"] = np.round(smape(y, pred), 2)
        metrics_df.at[i, "Test MAPE"] = np.round(smape(ytest, predtest), 2)
        i += 1
    metrics_df = np.round(metrics_df, 2)

    return metrics_df


def model_result(slide, model_key, model_dict, model_metrics_df, date_col):
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = model_key.split('__')[0]
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)
    target = model_key.split('__')[1]

    metrics_table_placeholder = slide.placeholders[ph_idx[1]]
    metrics_df = model_metrics_df[model_metrics_df['Model'] == model_key.split('__')[0]].reset_index(drop=True)

    # Accuracy = 1-mape
    metrics_df['Accuracy'] = 100 * (1 - metrics_df['Train MAPE'])
    metrics_df['Accuracy'] = metrics_df['Accuracy'].apply(lambda x: f'{np.round(x, 0)}%')

    ## Removing metrics as requested by Ioannis

    metrics_df = metrics_df.drop(columns=['R2', 'ADJR2', 'Train MAPE', 'Test MAPE'])
    fill_table_placeholder(metrics_table_placeholder, slide, metrics_df)

    # coeff_table_placeholder = slide.placeholders[ph_idx[2]]
    # coeff_df = pd.DataFrame(model_dict['Model_object'].params)
    # coeff_df.reset_index(inplace=True)
    # coeff_df.columns = ['Feature', 'Coefficent']
    # fill_table_placeholder(coeff_table_placeholder, slide, coeff_df)

    chart_placeholder = slide.placeholders[ph_idx[2]]
    full_df = pd.concat([model_dict['X_train_tuned'], model_dict['X_test_tuned']])
    full_df['Predicted'] = model_dict['Model_object'].predict(full_df[model_dict['feature_set']])
    pred_df = full_df[[date_col, target, 'Predicted']]
    pred_df.rename(columns={target: 'Actual'}, inplace=True)

    # Create chart data
    chart_data = CategoryChartData()
    chart_data.categories = pred_df[date_col]
    chart_data.add_series('Actual', pred_df['Actual'])
    chart_data.add_series('Predicted', pred_df['Predicted'])

    # Set range for y axis
    min_y = np.floor(min(pred_df['Actual'].min(), pred_df['Predicted'].min()))
    max_y = np.ceil(max(pred_df['Actual'].max(), pred_df['Predicted'].max()))

    # Create the chart
    line_chart(chart_placeholder=chart_placeholder,
               slide=slide,
               chart_data=chart_data,
               titles={'chart_title': 'Actual VS Predicted',
                       'x_axis': 'Date',
                       'y_axis': target.title().replace('_', ' ')
                       },
               min_y=min_y,
               max_y=max_y
               )

    return slide


def metrics_contributions(slide, contributions_excels_dict, panel_col):
    # Create data for metrics contributions
    all_contribution_df = pd.DataFrame(columns=['Channel'])
    target_sum_dict = {}
    sort_support_dct = {}
    for target in contributions_excels_dict.keys():
        contribution_df = contributions_excels_dict[target]['CONTRIBUTION MMM'].copy()
        if 'Date' in contribution_df.columns:
            contribution_df.drop(columns=['Date'], inplace=True)
        if panel_col in contribution_df.columns:
            contribution_df.drop(columns=[panel_col], inplace=True)

        contribution_df = pd.DataFrame(np.sum(contribution_df, axis=0)).reset_index()
        contribution_df.columns = ['Channel', target]
        target_sum = contribution_df[target].sum()
        target_sum_dict[target] = target_sum
        contribution_df[target] = 100 * contribution_df[target] / target_sum

        all_contribution_df = pd.merge(all_contribution_df, contribution_df, on='Channel', how='outer')

    sorted_target_sum_dict = sorted(target_sum_dict.items(), key=lambda kv: kv[1], reverse=True)
    sorted_target_sum_keys = [kv[0] for kv in sorted_target_sum_dict]
    if len([metric for metric in sorted_target_sum_keys if metric.lower() == 'revenue']) == 1:
        rev_metric = [metric for metric in sorted_target_sum_keys if metric.lower() == 'revenue'][0]
        sorted_target_sum_keys.remove(rev_metric)
        sorted_target_sum_keys.append(rev_metric)
    all_contribution_df = all_contribution_df[['Channel'] + sorted_target_sum_keys]

    # for col in all_contribution_df.columns:
    #     all_contribution_df[col]=all_contribution_df[col].apply(lambda x: round_off(x,1))

    # Sort Data by Average contribution of the channels keeping base first <Removed>
    # all_contribution_df['avg'] = np.mean(all_contribution_df[list(contributions_excels_dict.keys())],axis=1)
    # all_contribution_df['rank'] = all_contribution_df['avg'].rank(ascending=False)

    # Sort data by contribution of bottom funnel metric
    bottom_funnel_metric = sorted_target_sum_keys[-1]
    all_contribution_df['rank'] = all_contribution_df[bottom_funnel_metric].rank(ascending=False)
    all_contribution_df.loc[all_contribution_df[all_contribution_df['Channel'] == 'base'].index, 'rank'] = 0
    all_contribution_df = all_contribution_df.sort_values(by='rank')
    all_contribution_df.drop(columns=['rank'], inplace=True)

    # Add title
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = "Response Metrics Contributions"
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    for target in contributions_excels_dict.keys():
        all_contribution_df[target] = all_contribution_df[target].astype(float)


    # Create chart data
    chart_data = CategoryChartData()
    chart_data.categories = all_contribution_df['Channel']
    for target in sorted_target_sum_keys:
        chart_data.add_series(format_response_metric(target), all_contribution_df[target])
    chart_placeholder = slide.placeholders[ph_idx[1]]

    if isinstance(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])), float):

        # Add the chart to the slide
        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Response Metrics Contributions',
                          # 'x_axis':'Channels',
                          'y_axis': 'Contributions'},
                  min_y=np.floor(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime']))),
                  max_y=np.ceil(np.max(all_contribution_df.select_dtypes(exclude=['object', 'datetime']))),
                  type='V',
                  label_type='per'
                  )
    else:

        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Response Metrics Contributions',
                          # 'x_axis':'Channels',
                          'y_axis': 'Contributions'},
                  min_y=np.floor(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])).values[0]),
                  max_y=np.ceil(np.max(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])).values[0]),
                  type='V',
                  label_type='per'
                  )

    return slide


def model_media_performance(slide, target, contributions_excels_dict, date_col='Date', is_panel=False,

                            panel_col='panel'):
    # Add title
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = "Media Performance - " + target.title().replace("_", " ")
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    # CONTRIBUTION CHART
    # Create contribution data
    contribution_df = contributions_excels_dict[target]['CONTRIBUTION MMM']
    if panel_col in contribution_df.columns:
        contribution_df.drop(columns=[panel_col], inplace=True)
    # contribution_df.drop(columns=[date_col], inplace=True)
    contribution_df = pd.DataFrame(np.sum(contribution_df, axis=0)).reset_index()
    contribution_df.columns = ['Channel', format_response_metric(target)]
    contribution_df['Channel'] = contribution_df['Channel'].apply(lambda x: x.title())
    target_sum = contribution_df[format_response_metric(target)].sum()
    contribution_df[format_response_metric(target)] = contribution_df[format_response_metric(target)] / target_sum
    contribution_df.sort_values(by=['Channel'], ascending=False, inplace=True)

    # for col in contribution_df.columns:
    #     contribution_df[col] = contribution_df[col].apply(lambda x : round_off(x))

    # Create Chart Data
    chart_data = ChartData()
    chart_data.categories = contribution_df['Channel']
    chart_data.add_series('Contribution', contribution_df[format_response_metric(target)])

    chart_placeholder = slide.placeholders[ph_idx[2]]
    pie_chart(chart_placeholder=chart_placeholder,
              slide=slide,
              chart_data=chart_data,
              title='Contribution')

    # SPENDS CHART

    initialize_data(panel='aggregated', metrics=target)
    scenario = st.session_state["scenario"]
    spends_values = {
        channel_name: round(
            scenario.channels[channel_name].actual_total_spends
            * scenario.channels[channel_name].conversion_rate,
            1,
        )
        for channel_name in st.session_state["channels_list"]
    }
    spends_df = pd.DataFrame(columns=['Channel', 'Media Spend'])
    spends_df['Channel'] = list(spends_values.keys())
    spends_df['Media Spend'] = list(spends_values.values())
    spends_sum = spends_df['Media Spend'].sum()
    spends_df['Media Spend'] = spends_df['Media Spend'] / spends_sum
    spends_df['Channel'] = spends_df['Channel'].apply(lambda x: x.title())
    spends_df.sort_values(by='Channel', ascending=False, inplace=True)
    # for col in spends_df.columns:
    #     spends_df[col] = spends_df[col].apply(lambda x : round_off(x))

    # Create Chart Data
    spends_chart_data = ChartData()
    spends_chart_data = ChartData()
    spends_chart_data.categories = spends_df['Channel']
    spends_chart_data.add_series('Media Spend', spends_df['Media Spend'])

    spends_chart_placeholder = slide.placeholders[ph_idx[1]]
    pie_chart(chart_placeholder=spends_chart_placeholder,
              slide=slide,
              chart_data=spends_chart_data,
              title='Media Spend')
    # spends_values.append(0)
    return contribution_df, spends_df


# def get_saved_scenarios_dict(project_path):
#     # Path to the saved scenarios file
#     saved_scenarios_dict_path = os.path.join(
#         project_path, "saved_scenarios.pkl"
#     )
#
#     # Load existing scenarios if the file exists
#     if os.path.exists(saved_scenarios_dict_path):
#         with open(saved_scenarios_dict_path, "rb") as f:
#             saved_scenarios_dict = pickle.load(f)
#     else:
#         saved_scenarios_dict = OrderedDict()
#
#     return saved_scenarios_dict

def optimization_summary(slide, scenario, scenario_name):
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = 'Optimization Summary'  # + ' (Scenario: ' + scenario_name + ')'
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    multiplier = 1 / float(scenario['multiplier'])
    # st.write(scenario['multiplier'], multiplier)
    ## Multiplier is an indicator of selected time fram
    ## Doesn't effect CPA

    opt_on = scenario['optimization']
    if opt_on.lower() == 'spends':
        opt_on = 'Media Spend'

    details_ph = slide.placeholders[ph_idx[3]]
    details_ph.text = 'Scenario Name: ' + scenario_name + \
                      '\nResponse Metric: ' + str(scenario['metrics_selected']).replace("_", " ").title() + \
                      '\nOptimized on: ' + str(opt_on).replace("_", " ").title()

    scenario_df = pd.DataFrame(columns=['Category', 'Actual', 'Simulated', 'Change'])
    scenario_df.at[0, 'Category'] = 'Media Spend'

    scenario_df.at[0, 'Actual'] = scenario['actual_total_spends'] * multiplier
    scenario_df.at[0, 'Simulated'] = scenario['modified_total_spends'] * multiplier
    scenario_df.at[0, 'Change'] = (scenario['modified_total_spends'] - scenario['actual_total_spends']) * multiplier

    scenario_df.at[1, 'Category'] = scenario['metrics_selected'].replace("_", " ").title()
    scenario_df.at[1, 'Actual'] = scenario['actual_total_sales'] * multiplier
    scenario_df.at[1, 'Simulated'] = (scenario['modified_total_sales']) * multiplier
    scenario_df.at[1, 'Change'] = (scenario['modified_total_sales'] - scenario['actual_total_sales']) * multiplier

    scenario_df.at[2, 'Category'] = 'CPA'
    actual_cpa = scenario['actual_total_spends'] / scenario['actual_total_sales']
    modified_cpa = scenario['modified_total_spends'] / scenario['modified_total_sales']
    scenario_df.at[2, 'Actual'] = actual_cpa
    scenario_df.at[2, 'Simulated'] = modified_cpa
    scenario_df.at[2, 'Change'] = modified_cpa - actual_cpa

    scenario_df.at[3, 'Category'] = 'ROI'
    act_roi = scenario['actual_total_sales'] / scenario['actual_total_spends']
    opt_roi = scenario['modified_total_sales'] / scenario['modified_total_spends']
    scenario_df.at[3, 'Actual'] = act_roi
    scenario_df.at[3, 'Simulated'] = opt_roi
    scenario_df.at[3, 'Change'] = opt_roi - act_roi

    for col in scenario_df.columns:
        scenario_df[col] = scenario_df[col].apply(lambda x: round_off(x, 1))
        scenario_df[col] = scenario_df[col].apply(lambda x: convert_number_to_abbreviation(x))

    table_placeholder = slide.placeholders[ph_idx[1]]
    fill_table_placeholder(table_placeholder, slide, scenario_df)

    channel_spends_df = pd.DataFrame(columns=['Channel', 'Actual Spends', 'Optimized Spends'])
    for i, channel in enumerate(scenario['channels'].values()):
        channel_spends_df.at[i, 'Channel'] = channel['name']
        channel_conversion_rate = channel[
            "conversion_rate"
        ]
        channel_spends_df.at[i, 'Actual Spends'] = (
                                                           channel["actual_total_spends"]
                                                           * channel_conversion_rate
                                                   ) * multiplier
        channel_spends_df.at[i, 'Optimized Spends'] = (
                                                              channel["modified_total_spends"]
                                                              * channel_conversion_rate
                                                      ) * multiplier
    channel_spends_df['Actual Spends'] = channel_spends_df['Actual Spends'].astype('float')
    channel_spends_df['Optimized Spends'] = channel_spends_df['Optimized Spends'].astype('float')

    for col in channel_spends_df.columns:
        channel_spends_df[col] = channel_spends_df[col].apply(lambda x: round_off(x, 0))

    # Sort data on Actual Spends
    channel_spends_df.sort_values(by='Actual Spends', inplace=True, ascending=False)

    # Create chart data
    chart_data = CategoryChartData()
    chart_data.categories = channel_spends_df['Channel']
    for col in ['Actual Spends', 'Optimized Spends']:
        chart_data.add_series(col, channel_spends_df[col])

    chart_placeholder = slide.placeholders[ph_idx[2]]

    # Add the chart to the slide
    if isinstance(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime'])),float):
        bar_chart(chart_placeholder=chart_placeholder,
              slide=slide,
              chart_data=chart_data,
              titles={'chart_title': 'Channel Wise Spends',
                      # 'x_axis':'Channels',
                      'y_axis': 'Spends'},
              # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
              min_y=0,
              max_y=np.ceil(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
              label_type='$'
              )
    else:
        # Add the chart to the slide
        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Channel Wise Spends',
                          # 'x_axis':'Channels',
                          'y_axis': 'Spends'},
                  # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
                  min_y=0,
                  max_y=np.ceil(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime'])).values[0]),
                  label_type='$'
                  )


def channel_wise_spends(slide, scenario):
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = 'Channel Spends and Impact'
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)
    # print(scenario.keys())

    multiplier = 1 / float(scenario['multiplier'])
    channel_spends_df = pd.DataFrame(columns=['Channel', 'Actual Spends', 'Optimized Spends'])
    for i, channel in enumerate(scenario['channels'].values()):
        channel_spends_df.at[i, 'Channel'] = channel['name']
        channel_conversion_rate = channel["conversion_rate"]
        channel_spends_df.at[i, 'Actual Spends'] = (channel[
                                                        "actual_total_spends"] * channel_conversion_rate) * multiplier
        channel_spends_df.at[i, 'Optimized Spends'] = (channel[
                                                           "modified_total_spends"] * channel_conversion_rate) * multiplier
    channel_spends_df['Actual Spends'] = channel_spends_df['Actual Spends'].astype('float')
    channel_spends_df['Optimized Spends'] = channel_spends_df['Optimized Spends'].astype('float')

    actual_sum = channel_spends_df['Actual Spends'].sum()
    opt_sum = channel_spends_df['Optimized Spends'].sum()

    for col in channel_spends_df.columns:
        channel_spends_df[col] = channel_spends_df[col].apply(lambda x: round_off(x, 0))

    channel_spends_df['Actual Spends %'] = 100 * (channel_spends_df['Actual Spends'] / actual_sum)
    channel_spends_df['Optimized Spends %'] = 100 * (channel_spends_df['Optimized Spends'] / opt_sum)
    channel_spends_df['Actual Spends %'] = np.round(channel_spends_df['Actual Spends %'])
    channel_spends_df['Optimized Spends %'] = np.round(channel_spends_df['Optimized Spends %'])

    # Sort Data based on Actual Spends %
    channel_spends_df.sort_values(by='Actual Spends %', inplace=True)

    # Create chart data
    chart_data = CategoryChartData()
    chart_data.categories = channel_spends_df['Channel']
    for col in ['Actual Spends %', 'Optimized Spends %']:
        # for col in ['Actual Spends %']:
        chart_data.add_series(col, channel_spends_df[col])
    chart_placeholder = slide.placeholders[ph_idx[1]]

    # Add the chart to the slide
    if isinstance(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']]), float):
        bar_chart(chart_placeholder=chart_placeholder,
              slide=slide,
              chart_data=chart_data,
              titles={'chart_title': 'Spend Split %',
                      # 'x_axis':'Channels',
                      'y_axis': 'Spend %'},
              min_y=0,
              max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']])),
              type='H',
              legend=True,
              label_type='per',
              xaxis_pos='low'
              )
    else:
        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Spend Split %',
                          # 'x_axis':'Channels',
                          'y_axis': 'Spend %'},
                  min_y=0,
                  max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']]).values[0]),
                  type='H',
                  legend=True,
                  label_type='per',
                  xaxis_pos='low'
                  )
    #
    # # Create chart data
    # chart_data_1 = CategoryChartData()
    # chart_data_1.categories = channel_spends_df['Channel']
    # # for col in ['Actual Spends %', 'Optimized Spends %']:
    # for col in ['Optimized Spends %']:
    #     chart_data_1.add_series(col, channel_spends_df[col])
    # chart_placeholder_1 = slide.placeholders[ph_idx[3]]
    #
    # # Add the chart to the slide
    # bar_chart(chart_placeholder=chart_placeholder_1,
    #           slide=slide,
    #           chart_data=chart_data_1,
    #           titles={'chart_title': 'Optimized Spends Split %',
    #                   # 'x_axis':'Channels',
    #                   'y_axis': 'Spends %'},
    #           min_y=0,
    #           max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']])),
    #           type='H',
    #           legend=False,
    #           label_type='per'
    #           )

    channel_spends_df['Delta %'] = 100 * (channel_spends_df['Optimized Spends'] - channel_spends_df['Actual Spends']) / \
                                   channel_spends_df['Actual Spends']
    channel_spends_df['Delta %'] = channel_spends_df['Delta %'].apply(lambda x: round_off(x, 0))

    # Create chart data
    delta_chart_data = CategoryChartData()
    delta_chart_data.categories = channel_spends_df['Channel']
    col = 'Delta %'
    delta_chart_data.add_series(col, channel_spends_df[col])
    delta_chart_placeholder = slide.placeholders[ph_idx[3]]

    # Add the chart to the slide
    if isinstance(np.min(channel_spends_df['Delta %']), float):
        bar_chart(chart_placeholder=delta_chart_placeholder,
                  slide=slide,
                  chart_data=delta_chart_data,
                  titles={'chart_title': 'Spend Delta %',
                          'y_axis': 'Spend Delta %'},
                  min_y=np.floor(np.min(channel_spends_df['Delta %'])),
                  max_y=np.ceil(np.max(channel_spends_df['Delta %'])),
                  type='H',
                  legend=False,
                  label_type='per',
                  xaxis_pos='low'

                  )
    else:
        bar_chart(chart_placeholder=delta_chart_placeholder,
                  slide=slide,
                  chart_data=delta_chart_data,
                  titles={'chart_title': 'Spend Delta %',
                          'y_axis': 'Spend Delta %'},
                  min_y=np.floor(np.min(channel_spends_df['Delta %']).values[0]),
                  max_y=np.ceil(np.max(channel_spends_df['Delta %']).values[0]),
                  type='H',
                  legend=False,
                  label_type='per',
                  xaxis_pos='low'

                  )

    # Incremental Impact
    channel_inc_df = pd.DataFrame(columns=['Channel', 'Increment'])
    for i, channel in enumerate(scenario['channels'].values()):
        channel_inc_df.at[i, 'Channel'] = channel['name']
        act_impact = channel['actual_total_sales']
        opt_impact = channel['modified_total_sales']
        impact = opt_impact - act_impact
        impact = round_off(impact, 0)
        impact = impact if abs(impact) > 0.0001 else 0
        channel_inc_df.at[i, 'Increment'] = impact

    channel_inc_df_1 = pd.merge(channel_spends_df, channel_inc_df, how='left', on='Channel')

    # Create chart data
    delta_chart_data = CategoryChartData()
    delta_chart_data.categories = channel_inc_df_1['Channel']
    col = 'Increment'
    delta_chart_data.add_series(col, channel_inc_df_1[col])
    delta_chart_placeholder = slide.placeholders[ph_idx[2]]

    label_req = True
    if min(np.abs(channel_inc_df_1[col])) > 100000:  # 0.1M
        label_type = 'M'
    elif min(np.abs(channel_inc_df_1[col])) > 10000 and max(np.abs(channel_inc_df_1[col])) > 1000000:
        label_type = 'M1'
    elif min(np.abs(channel_inc_df_1[col])) > 100 and max(np.abs(channel_inc_df_1[col])) > 1000:
        label_type = 'K'
    else:
        label_req = False
    # Add the chart to the slide
    if label_req:
        bar_chart(chart_placeholder=delta_chart_placeholder,
                  slide=slide,
                  chart_data=delta_chart_data,
                  titles={'chart_title': 'Incremental Impact',
                          'y_axis': format_response_metric(scenario['metrics_selected'])},
                  # min_y=np.floor(np.min(channel_inc_df_1['Delta %'])),
                  # max_y=np.ceil(np.max(channel_inc_df_1['Delta %'])),
                  type='H',
                  label_type=label_type,
                  legend=False,
                  xaxis_pos='low'
                  )
    else:
        bar_chart(chart_placeholder=delta_chart_placeholder,
                  slide=slide,
                  chart_data=delta_chart_data,
                  titles={'chart_title': 'Increment',
                          'y_axis': scenario['metrics_selected']},
                  # min_y=np.floor(np.min(channel_inc_df_1['Delta %'])),
                  # max_y=np.ceil(np.max(channel_inc_df_1['Delta %'])),
                  type='H',
                  legend=False,
                  xaxis_pos='low'
                  )


def channel_wise_roi(slide, scenario):
    channel_roi_mroi = scenario['channel_roi_mroi']

    # Add title
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = 'Channel ROIs'
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    channel_roi_df = pd.DataFrame(columns=['Channel', 'Actual ROI', 'Optimized ROI'])
    for i, channel in enumerate(channel_roi_mroi.keys()):
        channel_roi_df.at[i, 'Channel'] = channel
        channel_roi_df.at[i, 'Actual ROI'] = channel_roi_mroi[channel]['actual_roi']
        channel_roi_df.at[i, 'Optimized ROI'] = channel_roi_mroi[channel]['optimized_roi']
    channel_roi_df['Actual ROI'] = channel_roi_df['Actual ROI'].astype('float')
    channel_roi_df['Optimized ROI'] = channel_roi_df['Optimized ROI'].astype('float')

    for col in channel_roi_df.columns:
        channel_roi_df[col] = channel_roi_df[col].apply(lambda x: round_off(x, 2))

    # Create chart data
    chart_data = CategoryChartData()
    chart_data.categories = channel_roi_df['Channel']
    for col in ['Actual ROI', 'Optimized ROI']:
        chart_data.add_series(col, channel_roi_df[col])

    chart_placeholder = slide.placeholders[ph_idx[1]]

    # Add the chart to the slide
    if isinstance(channel_roi_df.select_dtypes(exclude=['object', 'datetime']), float):
        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Channel Wise ROI',
                          # 'x_axis':'Channels',
                          'y_axis': 'ROI'},
                  # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
                  min_y=0,
                  max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime']))
                  )
    else:
        bar_chart(chart_placeholder=chart_placeholder,
                  slide=slide,
                  chart_data=chart_data,
                  titles={'chart_title': 'Channel Wise ROI',
                          # 'x_axis':'Channels',
                          'y_axis': 'ROI'},
                  # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
                  min_y=0,
                  max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])).values[0]
                  )
    # act_roi = scenario['actual_total_sales']/scenario['actual_total_spends']
    # opt_roi = scenario['modified_total_sales']/scenario['modified_total_spends']
    #
    # act_roi_ph = slide.placeholders[ph_idx[2]]
    # act_roi_ph.text = 'Actual ROI: ' + str(round_off(act_roi,2))
    # opt_roi_ph = slide.placeholders[ph_idx[3]]
    # opt_roi_ph.text = 'Optimized ROI: ' + str(round_off(opt_roi, 2))

    ## Removing mroi chart as per Ioannis' feedback
    # channel_mroi_df = pd.DataFrame(columns=['Channel', 'Actual mROI', 'Optimized mROI'])
    # for i, channel in enumerate(channel_roi_mroi.keys()):
    #     channel_mroi_df.at[i, 'Channel'] = channel
    #     channel_mroi_df.at[i, 'Actual mROI'] = channel_roi_mroi[channel]['actual_mroi']
    #     channel_mroi_df.at[i, 'Optimized mROI'] = channel_roi_mroi[channel]['optimized_mroi']
    # channel_mroi_df['Actual mROI']=channel_mroi_df['Actual mROI'].astype('float')
    # channel_mroi_df['Optimized mROI']=channel_mroi_df['Optimized mROI'].astype('float')
    #
    # for col in channel_mroi_df.columns:
    #     channel_mroi_df[col]=channel_mroi_df[col].apply(lambda x: round_off(x))
    #
    # # Create chart data
    # mroi_chart_data = CategoryChartData()
    # mroi_chart_data.categories = channel_mroi_df['Channel']
    # for col in ['Actual mROI', 'Optimized mROI']:
    #     mroi_chart_data.add_series(col, channel_mroi_df[col])
    #
    # mroi_chart_placeholder=slide.placeholders[ph_idx[2]]
    #
    # # Add the chart to the slide
    # bar_chart(chart_placeholder=mroi_chart_placeholder,
    #           slide=slide,
    #           chart_data=mroi_chart_data,
    #           titles={'chart_title':'Channel Wise mROI',
    #                   # 'x_axis':'Channels',
    #                   'y_axis':'mROI'},
    #           # min_y=np.floor(np.min(channel_mroi_df.select_dtypes(exclude=['object', 'datetime']))),
    #           min_y=0,
    #           max_y=np.ceil(np.max(channel_mroi_df.select_dtypes(exclude=['object', 'datetime'])))
    #           )


def effictiveness_efficiency(slide, final_data, bin_dct, scenario):
    # Add title
    placeholders = slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = slide.placeholders[ph_idx[0]]
    title_ph.text = 'Effectiveness and Efficiency'
    title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE)

    response_metrics = bin_dct['Response Metrics']

    kpi_df = final_data[response_metrics].sum(axis=0).reset_index()
    kpi_df.columns = ['Response Metric', 'Effectiveness']
    kpi_df['Efficiency'] = kpi_df['Effectiveness'] / scenario['modified_total_spends']
    kpi_df['Efficiency'] = kpi_df['Efficiency'].apply(lambda x: round_off(x, 1))
    kpi_df.sort_values(by='Effectiveness', inplace=True)
    kpi_df['Response Metric'] = kpi_df['Response Metric'].apply(lambda x: format_response_metric(x))

    # Create chart data for effectiveness
    chart_data = CategoryChartData()
    chart_data.categories = kpi_df['Response Metric']
    chart_data.add_series('Effectiveness', kpi_df['Effectiveness'])

    chart_placeholder = slide.placeholders[ph_idx[1]]

    # Add the chart to the slide
    bar_chart(chart_placeholder=chart_placeholder,
              slide=slide,
              chart_data=chart_data,
              titles={'chart_title': 'Effectiveness',
                      # 'x_axis':'Channels',
                      # 'y_axis': 'ROI'
                      },
              # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
              min_y=0,
              # max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])),
              type='H',
              label_type='M'
              )

    # Create chart data for efficiency
    chart_data_1 = CategoryChartData()
    chart_data_1.categories = kpi_df['Response Metric']
    chart_data_1.add_series('Efficiency', kpi_df['Efficiency'])

    chart_placeholder_1 = slide.placeholders[ph_idx[2]]

    # Add the chart to the slide
    bar_chart(chart_placeholder=chart_placeholder_1,
              slide=slide,
              chart_data=chart_data_1,
              titles={'chart_title': 'Efficiency',
                      # 'x_axis':'Channels',
                      # 'y_axis': 'ROI'
                      },
              # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))),
              min_y=0,
              # max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])),
              type='H'
              )

    definition_ph_1 = slide.placeholders[ph_idx[3]]
    definition_ph_1.text = 'Effectiveness is measured as the total sum of the Response Metric'
    definition_ph_2 = slide.placeholders[ph_idx[4]]
    definition_ph_2.text = 'Efficiency is measured as the ratio of sum of the Response Metric and sum of Media Spend'


def load_pickle(path):
    with open(path, "rb") as f:
        file_data = pickle.load(f)
    return file_data


def read_all_files():
    files=[]

    # Read data and bin dictionary
    if st.session_state["project_dct"]["data_import"]["imputed_tool_df"] is not None:
        final_df_loaded = st.session_state["project_dct"]["data_import"]["imputed_tool_df"].copy()
        bin_dict_loaded = st.session_state["project_dct"]["data_import"]["category_dict"].copy()

        files.append(final_df_loaded)
        files.append(bin_dict_loaded)

        if "group_dict" in st.session_state["project_dct"]["data_import"].keys():
            channels = st.session_state["project_dct"]["data_import"]["group_dict"]
            files.append(channels)


        if st.session_state["project_dct"]["transformations"]["final_df"] is not None:
            transform_dict = st.session_state["project_dct"]["transformations"]
            files.append(transform_dict)
            if retrieve_pkl_object_without_warning(st.session_state['project_number'], "Model_Tuning", "tuned_model", schema) is not None:
                tuned_model_dict = retrieve_pkl_object_without_warning(st.session_state['project_number'], "Model_Tuning",
                                                       "tuned_model", schema)  # db

                files.append(tuned_model_dict)
            else:
                files.append(None)
        else:
            files.append(None)

        if len(list(st.session_state["project_dct"]["current_media_performance"]["model_outputs"].keys()))>0: # check if there are model outputs for at least one metric
            metrics_list = list(st.session_state["project_dct"]["current_media_performance"]["model_outputs"].keys())
            contributions_excels_dict = {}
            for metrics in metrics_list:
                # raw_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["raw_data"]
                # spend_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["spends_data"]
                contribution_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["contribution_data"]
                contributions_excels_dict[metrics] = {'CONTRIBUTION MMM':contribution_df}
            files.append(contributions_excels_dict)

            # Get Saved Scenarios
            if len(list(st.session_state["project_dct"]["saved_scenarios"]["saved_scenarios_dict"].keys()))>0:
                files.append(st.session_state["project_dct"]["saved_scenarios"]["saved_scenarios_dict"])

                    # saved_scenarios_loaded = get_saved_scenarios_dict(project_path)


    return files



'''



    Template Layout



    0 : Title

    1 : Data Details Section {no changes required}

    2 : Data Import

    3 : Data Import - Channel Groups

    4 : Model Results {Duplicate for each model}  

    5 : Metrics Contribution

    6 : Media performance {Duplicate for each model} 

    7 : Media performance Tabular View {Duplicate for each model} 

    8 : Optimization Section {no changes}

    9 : Optimization Summary {Duplicate for each section}

    10 : Channel Spends {Duplicate for each model}

    11 : Channel Wise ROI {Duplicate for each model}

    12 : Efficiency & Efficacy

    13 : Appendix

    14 : Transformations

    15 : Model Summary

    16 : Thank You Slide



'''


def create_ppt(project_name, username, panel_col):
    # Read saved files
    files = read_all_files()
    transform_dict, tuned_model_dict, contributions_excels_dict, saved_scenarios_loaded = None, None, None, None

    if len(files)>0:
        # saved_data = files[0]
        data = files[0]
        bin_dict = files[1]

        channel_groups_dct = files[2]
        try:
            transform_dict = files[3]
            tuned_model_dict = files[4]
            contributions_excels_dict = files[5]
            saved_scenarios_loaded = files[6]
        except Exception as e:
            print(e)

    else:
        return False

    is_panel = True if data[panel_col].nunique()>1 else False

    template_path = 'ppt/template.pptx'
    # ppt_path = os.path.join('ProjectSummary.pptx')

    prs = Presentation(template_path)
    num_slides = len(prs.slides)
    slides = prs.slides

    # Title Slide
    title_slide_layout = slides[0].slide_layout
    title_slide = prs.slides.add_slide(title_slide_layout)

    # Add title & project name
    placeholders = title_slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = title_slide.placeholders[ph_idx[0]]
    title_ph.text = 'Media Mix Optimization Summary'
    txt_ph = title_slide.placeholders[ph_idx[1]]
    txt_ph.text = 'Project Name: ' + project_name + '\nCreated By: ' + username

    # Model Details Section
    model_section_slide_layout = slides[1].slide_layout
    model_section_slide = prs.slides.add_slide(model_section_slide_layout)

    ## Add title
    placeholders = model_section_slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = model_section_slide.placeholders[ph_idx[0]]
    title_ph.text = 'Model Details'
    section_ph = model_section_slide.placeholders[ph_idx[1]]
    section_ph.text = 'Section 1'

    # Data Import
    data_import_slide_layout = slides[2].slide_layout
    data_import_slide = prs.slides.add_slide(data_import_slide_layout)
    data_import_slide = title_and_table(slide=data_import_slide,
                                        title='Data Import',
                                        df=data_import(data, bin_dict),
                                        column_width={0: 2, 1: 7}
                                        )

    # Channel Groups
    channel_group_slide_layout = slides[3].slide_layout
    channel_group_slide = prs.slides.add_slide(channel_group_slide_layout)
    channel_group_slide = title_and_table(slide=channel_group_slide,
                                          title='Channels - Media and Spend',
                                          df=channel_groups_df(channel_groups_dct, bin_dict),
                                          column_width={0: 2, 1: 5, 2: 2}
                                          )

    if tuned_model_dict is not None:
        model_metrics_df = model_metrics(tuned_model_dict, False)

        # Model Results
        for model_key, model_dict in tuned_model_dict.items():
            model_result_slide_layout = slides[4].slide_layout
            model_result_slide = prs.slides.add_slide(model_result_slide_layout)
            model_result_slide = model_result(slide=model_result_slide,
                                              model_key=model_key,
                                              model_dict=model_dict,
                                              model_metrics_df=model_metrics_df,
                                              date_col='date')

    if contributions_excels_dict is not None:

        # Metrics Contributions
        metrics_contributions_slide_layout = slides[5].slide_layout
        metrics_contributions_slide = prs.slides.add_slide(metrics_contributions_slide_layout)
        metrics_contributions_slide = metrics_contributions(slide=metrics_contributions_slide,
                                                            contributions_excels_dict=contributions_excels_dict,
                                                            panel_col=panel_col
                                                            )

        # Media Performance
        for target in contributions_excels_dict.keys():

            # Chart
            model_media_perf_slide_layout = slides[6].slide_layout
            model_media_perf_slide = prs.slides.add_slide(model_media_perf_slide_layout)
            contribution_df, spends_df = model_media_performance(slide=model_media_perf_slide,
                                                                 target=target,
                                                                 contributions_excels_dict=contributions_excels_dict
                                                                 )

            # Tabular View
            contri_spends_df = pd.merge(spends_df, contribution_df, on='Channel', how='outer')
            contri_spends_df.fillna(0, inplace=True)

            for col in [c for c in contri_spends_df.columns if c != 'Channel']:
                contri_spends_df[col] = contri_spends_df[col].apply(lambda x: safe_num_to_per(x))

            media_performance_table_slide_layout = slides[7].slide_layout
            media_performance_table_slide = prs.slides.add_slide(media_performance_table_slide_layout)
            media_performance_table_slide = title_and_table(slide=media_performance_table_slide,
                                                            title='Media and Spends Channels Tabular View',
                                                            df=contri_spends_df,
                                                            # column_width={0:2, 1:5, 2:2}
                                                            )

    if saved_scenarios_loaded is not None:
        # Optimization Details
        opt_section_slide_layout = slides[8].slide_layout
        opt_section_slide = prs.slides.add_slide(opt_section_slide_layout)

        ## Add title
        placeholders = opt_section_slide.placeholders
        ph_idx = [ph.placeholder_format.idx for ph in placeholders]
        title_ph = opt_section_slide.placeholders[ph_idx[0]]
        title_ph.text = 'Optimizations Details'
        section_ph = opt_section_slide.placeholders[ph_idx[1]]
        section_ph.text = 'Section 2'

        # Optimization
        for scenario_name, scenario in saved_scenarios_loaded.items():
            opt_summary_slide_layout = slides[9].slide_layout
            opt_summary_slide = prs.slides.add_slide(opt_summary_slide_layout)
            optimization_summary(opt_summary_slide, scenario, scenario_name)

            channel_spends_slide_layout = slides[10].slide_layout
            channel_spends_slide = prs.slides.add_slide(channel_spends_slide_layout)
            channel_wise_spends(channel_spends_slide, scenario)

            channel_roi_slide_layout = slides[11].slide_layout
            channel_roi_slide = prs.slides.add_slide(channel_roi_slide_layout)
            channel_wise_roi(channel_roi_slide, scenario)

            effictiveness_efficiency_slide_layout = slides[12].slide_layout
            effictiveness_efficiency_slide = prs.slides.add_slide(effictiveness_efficiency_slide_layout)
            effictiveness_efficiency(effictiveness_efficiency_slide,
                                     data,
                                     bin_dict,
                                     scenario)

    # Appendix Section
    appendix_section_slide_layout = slides[13].slide_layout
    appendix_section_slide = prs.slides.add_slide(appendix_section_slide_layout)

    if tuned_model_dict is not None:

        ## Add title
        placeholders = appendix_section_slide.placeholders
        ph_idx = [ph.placeholder_format.idx for ph in placeholders]
        title_ph = appendix_section_slide.placeholders[ph_idx[0]]
        title_ph.text = 'Appendix'
        section_ph = appendix_section_slide.placeholders[ph_idx[1]]
        section_ph.text = 'Section 3'

        # Add transformations
        # if transform_dict is not None:
        #     # Transformations
        #     transformation_slide_layout = slides[14].slide_layout
        #     transformation_slide = prs.slides.add_slide(transformation_slide_layout)
        #     transformation_slide = title_and_table(slide=transformation_slide,
        #                                            title='Transformations',
        #                                            df=transformations(transform_dict),
        #                                            custom_table_height=True
        #                                            )

        # Add model summary
        # Model Summary
        model_metrics_df = model_metrics(tuned_model_dict, False)
        model_summary_slide_layout = slides[15].slide_layout
        model_summary_slide = prs.slides.add_slide(model_summary_slide_layout)
        model_summary_slide = title_and_table(slide=model_summary_slide,
                                              title='Model Summary',
                                              df=model_metrics_df,
                                              custom_table_height=True
                                              )

    # Last Slide
    last_slide_layout = slides[num_slides - 1].slide_layout
    last_slide = prs.slides.add_slide(last_slide_layout)

    # Add title
    placeholders = last_slide.placeholders
    ph_idx = [ph.placeholder_format.idx for ph in placeholders]
    title_ph = last_slide.placeholders[ph_idx[0]]
    title_ph.text = 'Thank You'

    # Remove template slides
    xml_slides = prs.slides._sldIdLst
    slides = list(xml_slides)
    for index in range(num_slides):
        xml_slides.remove(slides[index])

    # prs.save(ppt_path)

    # save the output into binary form
    binary_output = BytesIO()
    prs.save(binary_output)

    return binary_output