#Cell 1: Prepare P&L data
import pandas as pd

#Prepare revenue data
revenue_spark_df = spark.table("finmgmtcourse.finmgmtdata.revenue")
revenue_raw = revenue_spark_df.toPandas()
#Load business segment structure
segment_spark_df = spark.table("finmgmtcourse.finmgmtdata.business_segment_mapping")
segment_mapping = segment_spark_df.toPandas()
#Add business segment mapping
revenue_table = pd.merge(revenue_raw, segment_mapping, left_on='Segment', right_on='Seg_Leaf_Code', how='left')


revenue_final = revenue_table.groupby(['Seg_Leaf_Desc', 'Month'], as_index=False)['Revenue'].sum()
revenue_final["Type"] = "Revenue"
revenue_final = revenue_final.rename(columns={'Seg_Leaf_Desc': 'Segment_name'})

#Load cost data
cost_spark_df = spark.table("finmgmtcourse.finmgmtdata.cost_summary")
cost_raw = cost_spark_df.toPandas()
cost_raw["Type"] = "Cost"
union_all = pd.concat([revenue_final, cost_raw], ignore_index=True)
display(union_all)

=====================================================================================================================
#Cell 2: Prepare allocation metric
revenue_final = revenue_table.groupby(['Seg_Leaf_Desc', 'Month'], as_index=False)['Q'].sum()
revenue_final_month = revenue_table.groupby([ 'Month'], as_index=False)['Q'].sum()
revenue_alloc = pd.merge(revenue_final, revenue_final_month, left_on='Month', right_on='Month', how='left')
revenue_alloc['Alloc%'] = revenue_alloc['Q_x']/revenue_alloc['Q_y']

=====================================================================================================================

#Cell3: Prepare credit table
cost_total = cost_raw.groupby(['Segment_name', 'Month'], as_index=False)['TotCost'].sum()
totcost_delivery = cost_total[cost_total["Segment_name"] == "Delivery"]  #filters the data
credit_tbl = totcost_delivery[['Segment_name','Month','TotCost']]
credit_tbl['TotCost'] = credit_tbl['TotCost']*-1
credit_tbl['Type'] = 'Recharge'
credit_tbl['Account_name'] = 'Recharge credit'
=====================================================================================================================

#Cell4 Prepare recharge table
recharge_tbl = pd.merge(totcost_delivery, revenue_alloc, left_on='Month', right_on='Month', how='left')
recharge_tbl['TotCost'] = recharge_tbl['TotCost']*recharge_tbl['Alloc%']
recharge_tbl['Type'] = "Recharge"
recharge_tbl['Account_name'] = "Recharge"
recharge_tbl['Segment_name'] = recharge_tbl['Seg_Leaf_Desc']
recharge_tbl = recharge_tbl.drop(["Q_x","Q_y","Alloc%","Seg_Leaf_Desc"],axis=1)
union_all_recharged = pd.concat([union_all, recharge_tbl,credit_tbl], ignore_index=True)
display(union_all_recharged)