"""
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

stonewater_asset_list = pd.read_csv("Stonewater asset list with archetypes V2.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 = read_pickle_from_s3(
    bucket_name="retrofit-data-dev",
    s3_file_name="customers/Stonewater/clustering/clustering_dataframe.pkl"
)

# Move property-type and built-form to the first two columns
columns_to_move = ['property-type', 'built-form']

# Get the remaining columns
remaining_columns = [col for col in clustering_features.columns if col not in columns_to_move]

# Create the new column order
new_column_order = columns_to_move + remaining_columns

# Reorder the DataFrame
clustering_features = clustering_features[new_column_order]

archetyped_asset_list = archetyped_asset_list.merge(
    clustering_features,
    on="internal_id",
    how="inner"
)

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
mapping_data = stonewater_asset_list[
    stonewater_asset_list["archetype_representative"]
][["internal_id", "uprn", "standardised_address", "standardised_postcode"]]

mapping_data = mapping_data.merge(
    clustering_features[["internal_id", "LONGITUDE", "LATITUDE"]],
)
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
proportion_of_real_epcs = clustering_features["estimated"].value_counts().to_frame().reset_index()
# Invert the true and false
proportion_of_real_epcs["estimated"] = ~proportion_of_real_epcs["estimated"]
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
epc_rating_breakdown = (
    clustering_features[~clustering_features["estimated"]]["current-energy-rating"]
    .value_counts()
    .to_frame()
    .reset_index()
)

epc_rating_breakdown = epc_rating_breakdown.rename(
    columns={"current-energy-rating": "EPC"}
)

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")))

epc_a_properties = clustering_features[
    (clustering_features["current-energy-rating"] == "A")
    & (~clustering_features["estimated"])
    ]

epc_a_properties = epc_a_properties.merge(
    stonewater_asset_list,
    on="internal_id",
    how="inner"
)
