"""
This script prepares some outputs for the stonewater project, 27th June 2024

The work done so far has been data cleaning and clustering.
In this script, we do the following things:

1) Match the clustering data to the archetypes
2) Do some basic analysis on the data
3) Mapping of the archetypes
"""
import pandas as pd
import json
from utils.s3 import read_pickle_from_s3
from backend.app.utils import sap_to_epc

stonewater_asset_list = pd.read_csv("Stonewater asset list with archetypes V3.csv")
archetyped_asset_list = stonewater_asset_list[
    [
        "internal_id", "customer_asset_id", "external_address_id", "udprn", "uprn", "cluster",
        "archetype_representative", "rank"
    ]
].copy()
archetyped_asset_list = archetyped_asset_list[archetyped_asset_list["rank"] != "NO ARCHETYPE"]
archetyped_asset_list["rank"] = archetyped_asset_list["rank"].astype(int)
# Sort
archetyped_asset_list = archetyped_asset_list.sort_values(by=["cluster", "rank"])

# Read in and merge on clustering features
clustering_features = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Stonewater/Stonewater archetyping features V3.csv",
)

archetyped_asset_list = archetyped_asset_list.merge(
    clustering_features.drop(columns=['uprn', 'Address ID', "rank", "cluster", "archetype_representative"]),
    left_on="internal_id",
    right_on="Osm. ID"
).drop(columns=["Osm. ID"])

archetyped_asset_list = archetyped_asset_list.rename(
    columns={
        "internal_id": "Osm. ID",
        "customer_asset_id": "Org. ref.",
        "external_address_id": "Address ID",
        "cluster": "Archetype ID",
        "archetype_representative": "Archetype Representative",
        "rank": "Archetype Group Rank",
    }
)
archetyped_asset_list["uprn"] = archetyped_asset_list["uprn"].astype('Int64')
# Create an extract of the features


# Look at number of combinations
# - If we look at the number of combinations of property type & built form, we have 25 unique combinations
# - If we look at the number of combinations of property type, built form, and walls description, this jumps
# massively to 237 unique combinations
# - Adding roof description to the mix, we have 857 unique combinations
# - Adding floor description, we have 1278 unique combinations
# This doesn't even begin to consider the other variables that we have in the dataset, such as the property dimensions,
# location, and other factors.
# Ideally, we would perfectly separate these variables but this is not possible, given the constraint of needing ~450
# archetypes. We will need to make some compromises here. This is where a clustering algorithm can help us.
# We don't end up with perfect separation but we can get a good enough separation to make the archetypes useful, and can
# base the archetypes on a number of energy performance metrics, as well as location and other factors.
# archetyped_asset_list[
#     ["property-type", "built-form", "walls-description", "roof-description",
#      "floor-description"]].drop_duplicates().shape

# Save this as an excel
# archetyped_asset_list.to_excel("Stonewater Archetyping Features.xlsx", index=False)

# We store the location data, which will be used for the mapping. We just need the longitude and latitude
stonewater_asset_list["uprn"] = stonewater_asset_list["uprn"].astype('Int64')

mapping_data = stonewater_asset_list[
    stonewater_asset_list["archetype_representative"]
][["internal_id", "uprn", "standardised_address", "standardised_postcode"]].merge(
    archetyped_asset_list[["uprn", "Walls", "Roofs", "Main Fuel", "Heating", "Age", "Property Type"]],
    how="left",
    on="uprn"
)

# We need to merge on longitude and latitude
spatial_data_to_uprn = read_pickle_from_s3(
    s3_file_name="scustomers/Stonewater/clustering/spatial_data_to_uprn.pkl",
    bucket_name="retrofit-data-dev"
)


# Function to convert specific columns to bool dtype
def convert_specific_columns_to_bool(df, columns):
    for column in columns:
        if column in df.columns:
            df[column] = df[column].astype(bool)
    return df


spatial_data_to_uprn = [convert_specific_columns_to_bool(
    df, ['conservation_status', 'is_listed_building', 'is_heritage_building']
) for df in spatial_data_to_uprn]

spatial_data_to_uprn = pd.concat(spatial_data_to_uprn)
spatial_data_to_uprn = spatial_data_to_uprn.drop(
    columns=["partition", "filename"]
).rename(columns={"UPRN": "uprn"})
spatial_data_to_uprn["uprn"] = spatial_data_to_uprn["uprn"].astype(str).astype("Int64")

mapping_data = mapping_data.merge(
    spatial_data_to_uprn[
        ["uprn", "LONGITUDE", "LATITUDE", "conservation_status", "is_listed_building", "is_heritage_building"]
    ],
    how="left",
    on="uprn"
)
mapping_data = mapping_data.drop(columns=["internal_id"])

with open("etl/customers/stonewater/map_app/Stonewater Mapping Data.json", "w") as f:
    f.write(json.dumps(mapping_data.to_dict(orient="records")))

# We also include some data for visualising the breakdown of EPCS
# Invert the true and false
# proportion_of_real_epcs = (~clustering_features["estimated"]).value_counts().to_frame().reset_index()
# proportion_of_real_epcs = proportion_of_real_epcs.rename(
#     columns={"estimated": "is_real_epc"}
# )
#
# with open("etl/customers/stonewater/map_app/Stonewater real EPC breakdown.json", "w") as f:
#     f.write(json.dumps(proportion_of_real_epcs.to_dict(orient="records")))

# Produce the breakdown of EPC ratings for properties to be surveyed
clustering_features["representative_epc"] = clustering_features["representative_sap"].apply(sap_to_epc)

epc_rating_breakdown = (
    clustering_features[clustering_features["archetype_representative"]]["representative_epc"]
    .value_counts()
    .to_frame()
    .reset_index()
)

epc_rating_breakdown = epc_rating_breakdown.rename(
    columns={"index": "EPC", "representative_epc": "count"}
)

with open("etl/customers/stonewater/map_app/Stonewater EPC rating breakdown.json", "w") as f:
    f.write(json.dumps(epc_rating_breakdown.to_dict(orient="records")))
