"""
This script is to calculate the ABS for the Places for People London project
"""

import os
import pandas as pd

# London
pfp_london_cav = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_areas_surrounding_london_reviewed_standardised_15052025.xlsx",
    sheet_name="Cav Route",
    header=1
)
pfp_london_cav = pfp_london_cav.rename(columns={"Route": "Route March"})
pfp_london_pv = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_areas_surrounding_london_reviewed_standardised_15052025.xlsx",
    sheet_name="PV Route",
    header=1
)
pfp_london_pv = pfp_london_pv.rename(columns={"Route": "Route March"})
pfp_london_cav["location"] = "London"
pfp_london_pv["location"] = "London"
# East
pfp_east_cav = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_east_reviewed_standarised_15052025.xlsx",
    sheet_name="Cav Route",
    header=1
)
pfp_east_cav = pfp_east_cav.rename(columns={"Route": "Route March"})
pfp_east_pv = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_east_reviewed_standarised_15052025.xlsx",
    sheet_name="PV Route",
    header=1
)
pfp_east_pv = pfp_east_pv.rename(columns={"Route": "Route March"})
pfp_east_cav["location"] = "East"
pfp_east_pv["location"] = "East"
# North east
pfp_north_east_cav = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_north_east_reviewed_standardised_15052025.xlsx",
    sheet_name="Cav Route",
    header=1
)
pfp_north_east_cav = pfp_north_east_cav.rename(columns={"Route": "Route March"})
pfp_north_east_pv = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_north_east_reviewed_standardised_15052025.xlsx",
    sheet_name="PV Route",
    header=1
)
pfp_north_east_pv = pfp_north_east_pv.rename(columns={"Route": "Route March"})
pfp_north_east_cav["location"] = "North East"
pfp_north_east_pv["location"] = "North East"
# North West
pfp_north_west_cav = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_north_west_reviewed_standardised_15052025.xlsx",
    sheet_name="Cav Route",
    header=1
)
pfp_north_west_cav = pfp_north_west_cav.rename(columns={"Route": "Route March"})
pfp_north_west_pv = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs "
    "rates/PFP_north_west_reviewed_standardised_15052025.xlsx",
    sheet_name="PV Route",
    header=1
)
pfp_north_west_pv = pfp_north_west_pv.rename(columns={"Route": "Route March"})
pfp_north_west_cav["location"] = "North West"
pfp_north_west_pv["location"] = "North West"

cav_route = pd.concat(
    [
        pfp_london_cav,
        pfp_east_cav,
        pfp_north_east_cav,
        pfp_north_west_cav
    ]
)
solar_route = pd.concat(
    [
        pfp_london_pv,
        pfp_east_pv,
        pfp_north_east_pv,
        pfp_north_west_pv
    ]
)


def get_band(sap_score_number):
    bands = [
        ("High_A", 96, float("inf")),
        ("Low_A", 92, 96),
        ("High_B", 86, 92),
        ("Low_B", 81, 86),
        ("High_C", 74.5, 81),
        ("Low_C", 69, 74.5),
        ("High_D", 61.5, 69),
        ("Low_D", 55, 61.5),
        ("High_E", 46.5, 55),
        ("Low_E", 39, 46.5),
        ("High_F", 29.5, 39),
        ("Low_F", 21, 29.5),
        ("High_G", 10.5, 21),
        ("Low_G", 1, 10.5),
    ]

    for band, lower, upper in bands:
        if lower <= sap_score_number < upper:
            return band

    return None


def classify_floor_area(floor_area):
    if floor_area <= 72:
        return "0-72"

    if floor_area <= 97:
        return "73-97"

    if floor_area <= 199:
        return "98-199"

    return "200+"


# We classify the abs bounds
solar_route["starting_abs_band"] = solar_route["epc_sap_score_on_register"].apply(get_band)
solar_route["ending_abs_band_scenario1"] = "High_C"
solar_route["ending_abs_band_scenario2"] = "Low_B"
solar_route["epc_total_floor_area"] = solar_route["epc_total_floor_area"].fillna(90)
solar_route["floor_area_band"] = solar_route["epc_total_floor_area"].apply(classify_floor_area)

# We classify the abs bounds
cav_route["epc_sap_score_on_register"] = cav_route["epc_sap_score_on_register"].fillna(68)
cav_route["starting_abs_band"] = cav_route["epc_sap_score_on_register"].apply(get_band)
cav_route["floor_area_band"] = cav_route["epc_total_floor_area"].apply(classify_floor_area)
cav_route["ending_abs_band"] = "Low_C"

abs_matrix = pd.read_csv(
    "/Users/khalimconn-kowlessar/Downloads/ECO4 Full Project Scores Matrix.csv"
)

cav_route = cav_route.merge(
    abs_matrix.rename(columns={"Cost Savings": "ABS Rate"}),
    how="left",
    left_on=["starting_abs_band", "ending_abs_band", "floor_area_band"],
    right_on=["Starting Band", "Finishing Band", "Floor Area Segment"],
)
solar_route = solar_route.merge(
    abs_matrix.rename(columns={"Cost Savings": "ABS Rate"}),
    how="left",
    left_on=["starting_abs_band", "ending_abs_band_scenario1", "floor_area_band"],
    right_on=["Starting Band", "Finishing Band", "Floor Area Segment"],
)
cav_route["ABS Rate"] = cav_route["ABS Rate"].fillna(0)
solar_route["ABS Rate"] = solar_route["ABS Rate"].fillna(0)

cav_abs_agg = (
    cav_route.groupby("Route March").agg(
        {
            "ABS Rate": "sum",
            "landlord_property_id": "count",
        }
    ).reset_index()
)
cav_abs_agg["Week Number"] = cav_abs_agg["Route March"].str.extract(r"(\d+)").astype(int)
cav_abs_agg = cav_abs_agg.sort_values("Week Number", ascending=True)
cav_abs_agg = cav_abs_agg.rename(columns={"landlord_property_id": "Number of Properties"})

solar_abs_agg = (
    solar_route.groupby("Route March").agg(
        {
            "ABS Rate": "sum",
            "landlord_property_id": "count",
        }
    ).reset_index()
)
solar_abs_agg["Week Number"] = solar_abs_agg["Route March"].str.extract(r"(\d+)").astype(int)
solar_abs_agg = solar_abs_agg.rename(columns={"landlord_property_id": "Number of Properties"})
solar_abs_agg = solar_abs_agg.sort_values("Week Number", ascending=True)

# We store the data
# Store as an excel
filename = "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Places For People/abs rates/pfp programme rates.xlsx"
# Store the data in two tabs. One for the asset list with the EPC data and the second with the flat data

with pd.ExcelWriter(filename) as writer:
    solar_abs_agg.to_excel(writer, sheet_name="Solar ABS", index=False)
    cav_abs_agg.to_excel(writer, sheet_name="Cav ABS", index=False)

    cav_route.to_excel(writer, sheet_name="Cavity data", index=False)
    solar_route.to_excel(writer, sheet_name="Solar data", index=False)
