"""
This script contains the code to generate the data required to populate the slides
We connect to the database amd extract the data for the portfolio needed so it is recommended to use
a environment akin to the backend to run this script
"""
import pandas as pd
import numpy as np
from backend.app.db.connection import db_engine
from sqlalchemy.orm import sessionmaker
from utils.s3 import read_csv_from_s3
from etl.customers.slide_utils import (
    plot_epc_distribution,
    get_property_details_by_portfolio_id,
    get_plan_by_portfolio_id,
    get_properties_with_default_recommendations,
    create_powerpoint,
    create_recommendations_summary
)
from backend.ml_models.AnnualBillSavings import AnnualBillSavings

USER_ID = 8
PORTFOLIO_ID_1 = 67
PORTFOLIO_ID_2 = 68
EPC_TARGET_1 = "C"
EPC_TARGET_2 = "A"
SAP_TARGET_1 = 69
SAP_TARGET_2 = 100
CUSTOMER_KEY = "gla-demo"

# Sample UPRNS
archetype_1_sample = ['100020604138', '200001192253', '100020581792', '100020576940', '200001187196', '100020618060',
                      '100020625813', '100020578756', '100020618076', '200001187197', '100020619814', '100020617489',
                      '100020588913']

archetype_2_sample = ['100020585002', '100020615603', '100020665652', '100020626800', '100020624347', '100020624348',
                      '100020576459', '10001007455', '100020666716', '100020609610', '100020625451', '100020625597',
                      '100020624351', '100020665634', '100020624350', '100020665640', '100020665632', '100022917303',
                      '100020665656', '10014055968', '100020630285', '100020665638', '100020616325', '100020637405',
                      '100020698027', '100020657902', '100020688226', '100020653786', '100020642337', '100020665643']

archetype_3_sample = ['100020594652', '100020697787', '100020577523', '100020633162', '100020601138', '100020595611',
                      '100020597485', '100020614883', '100020605342', '100020654671', '100020575611', '100020607980',
                      '200001185785', '100020616446', '100020692380']

archetype_4_sample = ['100020596436', '100020610165', '200001187539', '100020655500', '100020582907', '100020598277',
                      '100020650607', '100020605116', '100020650603']


def scenario_1():
    # Connect to database
    session = sessionmaker(bind=db_engine)()

    ########################################################################
    # Get the data we need
    ########################################################################

    portfolio_id = PORTFOLIO_ID_1

    # Get the asset list
    asset_list = read_csv_from_s3(
        "retrofit-plan-inputs-dev", f"{USER_ID}/67/inputs.csv"
    )
    asset_list = pd.DataFrame(asset_list)

    # Get the properties for the portfolio
    properties = get_properties_with_default_recommendations(session, portfolio_id)
    properties_df = pd.DataFrame(properties)

    # We now pull the data for the property details
    property_details = get_property_details_by_portfolio_id(session, portfolio_id)
    property_details_df = pd.DataFrame(property_details)
    # We estimate bills based on the adjusted_energy_consumption
    property_details_df["energy_bill"] = property_details_df["adjusted_energy_consumption"].apply(
        lambda x: AnnualBillSavings.calculate_annual_bill(x)
    )
    # Merge on uprn
    property_details_df = property_details_df.merge(
        properties_df[["uprn", "id"]].rename(columns={"id": "property_id"}),
        on="property_id"
    )

    plans = get_plan_by_portfolio_id(session, portfolio_id)
    plans_df = pd.DataFrame(plans)

    # Unnest the recommendations. Each recommendation is a list of dictionaries
    recommendations_exploded = properties_df["recommendations"].explode().tolist()
    recommendations_df = pd.DataFrame([r for r in recommendations_exploded if not pd.isnull(r)])
    # Add uprn on
    recommendations_df = recommendations_df.merge(
        properties_df[["uprn", "id"]].rename(columns={"id": "property_id"}),
        how="left",
        on="property_id"
    )

    recommendations_summary = create_recommendations_summary(
        recommendations_df,
        properties_df,
        property_details_df,
        SAP_TARGET_1
    )

    # Calculate % changes of energ, co2 and abs
    recommendations_summary["carbon_percent_change"] = (
        recommendations_summary["total_carbon"] / recommendations_summary["current_co2"]
    )

    recommendations_summary["energy_percent_change"] = (
        recommendations_summary["adjusted_heat_demand"] / recommendations_summary["current_energy"]
    )

    recommendations_summary["bills_percent_change"] = (
        recommendations_summary["total_bill_savings"] / recommendations_summary["current_energy_bill"]
    )

    ########################
    # Overview
    ########################
    overview_totals = recommendations_summary.sum()
    overview_means = recommendations_summary.mean()

    ########################
    # Measures
    ########################
    measures_count = recommendations_df.groupby("type")["id"].count().reset_index()
    wall_insulation_measures = measures_count[
        measures_count["type"].isin(["cavity_wall_insulation", "external_wall_insulation", "internal_wall_insulation"])
    ]["id"].sum()
    ventilation_measures = measures_count[
        measures_count["type"].isin(["mechanical_ventilation"])
    ]["id"].sum()
    roof_insulation_measures = measures_count[
        measures_count["type"].isin(["loft_insulation", "flat_roof_insulation"])
    ]["id"].sum()
    floor_insulation_measures = measures_count[
        measures_count["type"].isin(["solid_floor_insulation", "suspended_floor_insulation"])
    ]["id"].sum()
    windows = measures_count[
        measures_count["type"].isin(["windows_glazing"])
    ]["id"].sum()
    heating = measures_count[
        measures_count["type"].isin(["heating"])
    ]["id"].sum()
    heating_controls = measures_count[
        measures_count["type"].isin(["heating_control"])
    ]["id"].sum()
    solar = measures_count[
        measures_count["type"].isin(["solar_pv"])
    ]["id"].sum()
    other = measures_count[
        ~measures_count["type"].isin([
            "cavity_wall_insulation", "external_wall_insulation", "internal_wall_insulation",
            "loft_insulation", "flat_roof_insulation", "solid_floor_insulation",
            "suspended_floor_insulation", "windows_glazing", "heating", "heating_control", "solar_pv",
            "mechanical_ventilation"
        ])
    ]["id"].sum()

    # Summary information by each archetype
    ########################
    # Archetype 1
    ########################
    archetype_1 = asset_list[asset_list["archetype"] == "Archetype 1"]
    recommendations_arch_1_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_1["uprn"].values)
    ]

    arch_1_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_1["uprn"].values)
    ]
    arch_1_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    cols_to_keep = ["total_cost", "total_carbon", "total_bill_savings", "total_sap_points", "adjusted_heat_demand",
                    "energy_percent_change", "carbon_percent_change", "bills_percent_change"]
    arch_1_recommendation_min = recommendations_arch_1_summary.min()[cols_to_keep]
    arch_1_recommendation_max = recommendations_arch_1_summary.max()[cols_to_keep]
    arch_1_recommendation_means = recommendations_arch_1_summary.mean()[cols_to_keep]
    arch_1_totals = recommendations_arch_1_summary.sum()[cols_to_keep]

    annual_total_co2 = recommendations_arch_1_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_1_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_1_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_1["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_1_recommendation_means['total_cost'], 2)}: "
                 f"{arch_1_recommendation_min['total_cost']} - {arch_1_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_1_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_1_recommendation_min['total_sap_points']} - {arch_1_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_1_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_1_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_1_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_1_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_1_recommendation_min['energy_percent_change']} - "
                           f"{arch_1_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_1_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_1_recommendation_min['total_carbon']} - {arch_1_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_1_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_1_recommendation_min['carbon_percent_change']} - "
                           f"{arch_1_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_1_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_1_recommendation_min['total_bill_savings']} - "
                 f"{arch_1_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_1_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_1_recommendation_min['bills_percent_change']} - "
                         f"{arch_1_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 2
    ########################
    archetype_2 = asset_list[asset_list["archetype"] == "Archetype 2"]
    recommendations_arch_2_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_2["uprn"].values)
    ]

    arch_2_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_2["uprn"].values)
    ]
    arch_2_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_2_recommendation_min = recommendations_arch_2_summary.min()
    arch_2_recommendation_max = recommendations_arch_2_summary.max()
    arch_2_recommendation_means = recommendations_arch_2_summary.mean().round(2)

    total_cost = recommendations_arch_2_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_2_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_2_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_2_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_2["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_2_recommendation_means['total_cost'], 2)}: "
                 f"{arch_2_recommendation_min['total_cost']} - {arch_2_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_2_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_2_recommendation_min['total_sap_points']} - {arch_2_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_2_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_2_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_2_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_2_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_2_recommendation_min['energy_percent_change']} - "
                           f"{arch_2_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_2_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_2_recommendation_min['total_carbon']} - {arch_2_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_2_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_2_recommendation_min['carbon_percent_change']} - "
                           f"{arch_2_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_2_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_2_recommendation_min['total_bill_savings']} - "
                 f"{arch_2_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_2_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_2_recommendation_min['bills_percent_change']} - "
                         f"{arch_2_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 3
    ########################
    archetype_3 = asset_list[asset_list["archetype"] == "Archetype 3"]
    recommendations_arch_3_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_3["uprn"].values)
    ]

    arch_3_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_3["uprn"].values)
    ]
    arch_3_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_3_recommendation_min = recommendations_arch_3_summary.min()
    arch_3_recommendation_max = recommendations_arch_3_summary.max()
    arch_3_recommendation_means = recommendations_arch_3_summary.mean()

    total_cost = recommendations_arch_3_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_3_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_3_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_3_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_3["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_3_recommendation_means['total_cost'], 2)}: "
                 f"{arch_3_recommendation_min['total_cost']} - {arch_3_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_3_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_3_recommendation_min['total_sap_points']} - {arch_3_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_3_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_3_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_3_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_3_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_3_recommendation_min['energy_percent_change']} - "
                           f"{arch_3_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_3_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_3_recommendation_min['total_carbon']} - {arch_3_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_3_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_3_recommendation_min['carbon_percent_change']} - "
                           f"{arch_3_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_3_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_3_recommendation_min['total_bill_savings']} - "
                 f"{arch_3_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_3_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_3_recommendation_min['bills_percent_change']} - "
                         f"{arch_3_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 4
    ########################
    archetype_4 = asset_list[asset_list["archetype"] == "Archetype 4"]
    recommendations_arch_4_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_4["uprn"].values)
    ]

    arch_4_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_4["uprn"].values)
    ]
    arch_4_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_4_recommendation_min = recommendations_arch_4_summary.min()
    arch_4_recommendation_max = recommendations_arch_4_summary.max()
    arch_4_recommendation_means = recommendations_arch_4_summary.mean()

    total_cost = recommendations_arch_4_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_4_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_4_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_4_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_4["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_4_recommendation_means['total_cost'], 2)}: "
                 f"{arch_4_recommendation_min['total_cost']} - {arch_4_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_4_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_4_recommendation_min['total_sap_points']} - {arch_4_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_4_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_4_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_4_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_4_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_4_recommendation_min['energy_percent_change']} - "
                           f"{arch_4_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_4_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_4_recommendation_min['total_carbon']} - {arch_4_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_4_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_4_recommendation_min['carbon_percent_change']} - "
                           f"{arch_4_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_4_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_4_recommendation_min['total_bill_savings']} - "
                 f"{arch_4_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_4_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_4_recommendation_min['bills_percent_change']} - "
                         f"{arch_4_recommendation_max['bills_percent_change']}")

    ########################
    # Overview
    ########################
    overview_totals = recommendations_summary.sum()


def make_sample():
    # sample_proportion = 67 / 102
    # Get the asset list
    asset_list = read_csv_from_s3(
        "retrofit-plan-inputs-dev", f"{USER_ID}/67/inputs.csv"
    )
    asset_list = pd.DataFrame(asset_list)

    # From the asset list, we deduce how many properties we need
    # Need to figure out the sizes
    archetype_1_sample_size = 13
    archetype_2_sample_size = 30
    archetype_3_sample_size = 15
    archetype_4_sample_size = 9

    # We take the sample and we'll keep the uprns static
    archetype_1_sample = asset_list[
        asset_list["archetype"] == "Archetype 1"
        ].sample(archetype_1_sample_size)["uprn"].to_list()

    archetype_2_sample = asset_list[
        asset_list["archetype"] == "Archetype 2"
        ].sample(archetype_2_sample_size)["uprn"].to_list()

    archetype_3_sample = asset_list[
        asset_list["archetype"] == "Archetype 3"
        ].sample(archetype_3_sample_size)["uprn"].to_list()

    archetype_4_sample = asset_list[
        asset_list["archetype"] == "Archetype 4"
        ].sample(archetype_4_sample_size)["uprn"].to_list()


def scenario_2():
    # Connect to database
    session = sessionmaker(bind=db_engine)()

    ########################################################################
    # Get the data we need
    ########################################################################

    portfolio_id = PORTFOLIO_ID_2

    # Get the asset list
    asset_list = read_csv_from_s3(
        "retrofit-plan-inputs-dev", f"{USER_ID}/67/inputs.csv"
    )
    asset_list = pd.DataFrame(asset_list)

    sample_uprns = archetype_1_sample + archetype_2_sample + archetype_3_sample + archetype_4_sample

    # Filter on sample uprns
    asset_list = asset_list[asset_list["uprn"].astype(str).isin(sample_uprns)]

    # Get the properties for the portfolio
    properties = get_properties_with_default_recommendations(session, portfolio_id)
    properties_df = pd.DataFrame(properties)
    properties_df = properties_df[properties_df["uprn"].astype(str).isin(sample_uprns)]

    # We now pull the data for the property details
    property_details = get_property_details_by_portfolio_id(session, portfolio_id)
    property_details_df = pd.DataFrame(property_details)
    property_details_df = property_details_df[property_details_df["property_id"].isin(properties_df["id"].values)]
    # We estimate bills based on the adjusted_energy_consumption
    property_details_df["energy_bill"] = property_details_df["adjusted_energy_consumption"].apply(
        lambda x: AnnualBillSavings.calculate_annual_bill(x)
    )
    # Merge on uprn
    property_details_df = property_details_df.merge(
        properties_df[["uprn", "id"]].rename(columns={"id": "property_id"}),
        on="property_id"
    )

    plans = get_plan_by_portfolio_id(session, portfolio_id)
    plans_df = pd.DataFrame(plans)

    # Unnest the recommendations. Each recommendation is a list of dictionaries
    recommendations_exploded = properties_df["recommendations"].explode().tolist()
    recommendations_df = pd.DataFrame([r for r in recommendations_exploded if not pd.isnull(r)])
    # Add uprn on
    recommendations_df = recommendations_df.merge(
        properties_df[["uprn", "id"]].rename(columns={"id": "property_id"}),
        how="left",
        on="property_id"
    )

    recommendations_summary = create_recommendations_summary(
        recommendations_df,
        properties_df,
        property_details_df,
        SAP_TARGET_1
    )

    # Calculate % changes of energ, co2 and abs
    recommendations_summary["carbon_percent_change"] = (
        recommendations_summary["total_carbon"] / recommendations_summary["current_co2"]
    )

    recommendations_summary["energy_percent_change"] = (
        recommendations_summary["adjusted_heat_demand"] / recommendations_summary["current_energy"]
    )

    recommendations_summary["bills_percent_change"] = (
        recommendations_summary["total_bill_savings"] / recommendations_summary["current_energy_bill"]
    )

    ########################
    # Overview
    ########################
    overview_totals = recommendations_summary.sum()
    overview_means = recommendations_summary.mean()

    ########################
    # Measures
    ########################
    measures_count = recommendations_df.groupby("type")["id"].count().reset_index()
    wall_insulation_measures = measures_count[
        measures_count["type"].isin(["cavity_wall_insulation", "external_wall_insulation", "internal_wall_insulation"])
    ]["id"].sum()
    ventilation_measures = measures_count[
        measures_count["type"].isin(["mechanical_ventilation"])
    ]["id"].sum()
    roof_insulation_measures = measures_count[
        measures_count["type"].isin(["loft_insulation", "flat_roof_insulation"])
    ]["id"].sum()
    floor_insulation_measures = measures_count[
        measures_count["type"].isin(["solid_floor_insulation", "suspended_floor_insulation"])
    ]["id"].sum()
    windows = measures_count[
        measures_count["type"].isin(["windows_glazing"])
    ]["id"].sum()
    heating = measures_count[
        measures_count["type"].isin(["heating"])
    ]["id"].sum()
    heating_controls = measures_count[
        measures_count["type"].isin(["heating_control"])
    ]["id"].sum()
    solar = measures_count[
        measures_count["type"].isin(["solar_pv"])
    ]["id"].sum()
    other = measures_count[
        ~measures_count["type"].isin([
            "cavity_wall_insulation", "external_wall_insulation", "internal_wall_insulation",
            "loft_insulation", "flat_roof_insulation", "solid_floor_insulation",
            "suspended_floor_insulation", "windows_glazing", "heating", "heating_control", "solar_pv",
            "mechanical_ventilation"
        ])
    ]["id"].sum()

    z = recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_3_sample)]

    recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_3_sample)]["type"].value_counts()

    # Summary information by each archetype
    ########################
    # Archetype 1
    ########################
    archetype_1 = asset_list[asset_list["archetype"] == "Archetype 1"]
    recommendations_arch_1_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_1["uprn"].values)
    ]

    arch_1_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_1["uprn"].values)
    ]
    arch_1_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_1_recommendation_min = recommendations_arch_1_summary.min()
    arch_1_recommendation_max = recommendations_arch_1_summary.max()
    arch_1_recommendation_means = recommendations_arch_1_summary.mean()

    arch_1_totals = recommendations_arch_1_summary.sum()

    annual_total_co2 = recommendations_arch_1_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_1_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_1_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_1["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_1_recommendation_means['total_cost'], 2)}: "
                 f"{arch_1_recommendation_min['total_cost']} - {arch_1_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_1_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_1_recommendation_min['total_sap_points']} - {arch_1_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_1_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_1_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_1_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_1_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_1_recommendation_min['energy_percent_change']} - "
                           f"{arch_1_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_1_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_1_recommendation_min['total_carbon']} - {arch_1_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_1_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_1_recommendation_min['carbon_percent_change']} - "
                           f"{arch_1_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_1_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_1_recommendation_min['total_bill_savings']} - "
                 f"{arch_1_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_1_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_1_recommendation_min['bills_percent_change']} - "
                         f"{arch_1_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 2
    ########################
    archetype_2 = asset_list[asset_list["archetype"] == "Archetype 2"]
    recommendations_arch_2_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_2["uprn"].values)
    ]

    arch_2_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_2["uprn"].values)
    ]
    arch_2_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_2_recommendation_min = recommendations_arch_2_summary.min()
    arch_2_recommendation_max = recommendations_arch_2_summary.max()
    arch_2_recommendation_means = recommendations_arch_2_summary.mean().round(2)

    total_cost = recommendations_arch_2_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_2_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_2_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_2_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_2["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_2_recommendation_means['total_cost'], 2)}: "
                 f"{arch_2_recommendation_min['total_cost']} - {arch_2_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_2_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_2_recommendation_min['total_sap_points']} - {arch_2_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_2_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_2_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_2_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_2_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_2_recommendation_min['energy_percent_change']} - "
                           f"{arch_2_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_2_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_2_recommendation_min['total_carbon']} - {arch_2_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_2_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_2_recommendation_min['carbon_percent_change']} - "
                           f"{arch_2_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_2_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_2_recommendation_min['total_bill_savings']} - "
                 f"{arch_2_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_2_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_2_recommendation_min['bills_percent_change']} - "
                         f"{arch_2_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 3
    ########################
    archetype_3 = asset_list[asset_list["archetype"] == "Archetype 3"]
    recommendations_arch_3_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_3["uprn"].values)
    ]

    arch_3_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_3["uprn"].values)
    ]
    arch_3_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_3_recommendation_min = recommendations_arch_3_summary.min()
    arch_3_recommendation_max = recommendations_arch_3_summary.max()
    arch_3_recommendation_means = recommendations_arch_3_summary.mean()

    total_cost = recommendations_arch_3_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_3_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_3_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_3_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_3["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_3_recommendation_means['total_cost'], 2)}: "
                 f"{arch_3_recommendation_min['total_cost']} - {arch_3_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_3_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_3_recommendation_min['total_sap_points']} - {arch_3_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_3_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_3_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_3_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_3_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_3_recommendation_min['energy_percent_change']} - "
                           f"{arch_3_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_3_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_3_recommendation_min['total_carbon']} - {arch_3_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_3_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_3_recommendation_min['carbon_percent_change']} - "
                           f"{arch_3_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_3_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_3_recommendation_min['total_bill_savings']} - "
                 f"{arch_3_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_3_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_3_recommendation_min['bills_percent_change']} - "
                         f"{arch_3_recommendation_max['bills_percent_change']}")

    ########################
    # Archetype 4
    ########################
    archetype_4 = asset_list[asset_list["archetype"] == "Archetype 4"]
    recommendations_arch_4_summary = recommendations_summary[
        recommendations_summary["uprn"].astype(str).isin(archetype_4["uprn"].values)
    ]

    arch_4_property_details = property_details_df[
        property_details_df["uprn"].astype(str).isin(archetype_4["uprn"].values)
    ]
    arch_4_property_details["co2_emissions"].sum() / property_details_df["co2_emissions"].sum()

    # Take the mean, median and maximum of each value
    arch_4_recommendation_min = recommendations_arch_4_summary.min()
    arch_4_recommendation_max = recommendations_arch_4_summary.max()
    arch_4_recommendation_means = recommendations_arch_4_summary.mean()

    total_cost = recommendations_arch_4_summary["total_cost"].sum()
    annual_total_co2 = recommendations_arch_4_summary["total_carbon"].sum()
    annual_total_bills = recommendations_arch_4_summary["total_bill_savings"].sum()
    annual_total_energy_savings = recommendations_arch_4_summary["adjusted_heat_demand"].sum()
    archetype_measures = \
        recommendations_df[recommendations_df["uprn"].astype(str).isin(archetype_4["uprn"].values)].groupby("type")[
            "id"].count().reset_index()

    cost_text = (f"{round(arch_4_recommendation_means['total_cost'], 2)}: "
                 f"{arch_4_recommendation_min['total_cost']} - {arch_4_recommendation_max['total_cost']}")

    sap_text = (f"{round(arch_4_recommendation_means['total_sap_points'], 2)}: "
                f"{arch_4_recommendation_min['total_sap_points']} - {arch_4_recommendation_max['total_sap_points']}")

    energy_text = (f"{round(arch_4_recommendation_means['adjusted_heat_demand'], 2)}: "
                   f"{arch_4_recommendation_min['adjusted_heat_demand']} - "
                   f"{arch_4_recommendation_max['adjusted_heat_demand']}")

    energy_percent_text = (f"{round(arch_4_recommendation_means['energy_percent_change'], 2)}: "
                           f"{arch_4_recommendation_min['energy_percent_change']} - "
                           f"{arch_4_recommendation_max['energy_percent_change']}")

    carbon_text = (f"{round(arch_4_recommendation_means['total_carbon'], 2)}: "
                   f"{arch_4_recommendation_min['total_carbon']} - {arch_4_recommendation_max['total_carbon']}")

    carbon_percent_text = (f"{round(arch_4_recommendation_means['carbon_percent_change'], 2)}: "
                           f"{arch_4_recommendation_min['carbon_percent_change']} - "
                           f"{arch_4_recommendation_max['carbon_percent_change']}")

    bill_text = (f"{round(arch_4_recommendation_means['total_bill_savings'], 2)}: "
                 f"{arch_4_recommendation_min['total_bill_savings']} - "
                 f"{arch_4_recommendation_max['total_bill_savings']}")

    bill_percent_text = (f"{round(arch_4_recommendation_means['bills_percent_change'], 2)}: "
                         f"{arch_4_recommendation_min['bills_percent_change']} - "
                         f"{arch_4_recommendation_max['bills_percent_change']}")
