import pandas as pd
import numpy as np
from backend.SearchEpc import SearchEpc
from dotenv import load_dotenv
from tqdm import tqdm
import os

load_dotenv(dotenv_path="backend/.env")
EPC_AUTH_TOKEN = os.getenv("EPC_AUTH_TOKEN")


def clean_colnames(df):
    secondary_cols = ["" if pd.isnull(x) else x for x in df.iloc[0, :].values]
    new_colnames = [
        "+".join([df.columns[i], secondary_cols[i]]) if secondary_cols[i] else df.columns[i]
        for i, c in enumerate(df.columns)
    ]
    # Drop row 0
    df = df.drop(0)
    df.columns = new_colnames
    return df


def lesney_farms():
    """
    Some rough and ready analysis to get a view of what the achetypes could be, ahead of a meeting with Wates
    on the 28th Aug 2024
    :return:
    """

    all_locations = [
        "Forest Road Erith",
        "Lesney Farms",
        "Brook Street 155 - 243",
        "Hazel Drive",
        "Page Crescent",
        "Brook Salmon Roberts and Chapma",
        "Beacon Road"
    ]

    all_assets = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/Bexley Wave 3 Project - external - "
        "reduced.xlsx",
        sheet_name="Full Property List",
        header=1
    )
    all_assets = clean_colnames(all_assets)
    all_assets["Location"] = None

    locations = {
        location_name: clean_colnames(pd.read_excel(
            "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/Bexley Wave 3 Project - external - "
            "reduced.xlsx",
            sheet_name=location_name,
            header=1
        )) for location_name in all_locations
    }

    for loc in all_locations:
        all_assets["Location"] = np.where(
            all_assets["Asset Reference"].isin(locations[loc]["Asset Reference"]),
            loc,
            all_assets["Location"]
        )

    if pd.isnull(all_assets["Location"]).sum():
        raise Exception("something went wrong")

    # 234 properties below EPC C
    below_epc_c = all_assets[all_assets["PRE CALCULATED EPC"].isin(["D", "E", "F", "G"])].copy()

    # We simplify wall type
    below_epc_c["wall_type_simplified"] = below_epc_c["Wall Type"].str.split(" ").str[0]

    known_no_epc = [
        28679,  # These is no EPC for 11 Page Crescent, Erith, Kent, DA8 2HJ, just 11A
        29291,  # No EPC for 225 Slade Green Road, Erith, Kent, DA8 2JW
    ]
    # Get the EPC data
    # epc_data = []
    # for _, home in tqdm(all_assets.iterrows(), total=len(all_assets)):
    #     if home["Asset Reference"] in known_no_epc:
    #         continue
    #
    #     address = home["Address"]
    #     # Spelling error
    #     if "Frinstead" in address:
    #         address = address.replace("Frinstead", "Frinsted")
    #
    #     address1 = address.split(",")[0]
    #
    #     asset_type_map = {
    #         "HOUSE": "House",
    #         "BUNGALOWS": "Bungalow",
    #         "FLATS": "Flat",
    #         "MAISONETTES": "Maisonette",
    #     }
    #
    #     searcher = SearchEpc(
    #         address1=address1,
    #         postcode=home["Address - Postcode"],
    #         auth_token=EPC_AUTH_TOKEN,
    #         os_api_key="",
    #         full_address=address,
    #     )
    #     searcher.ordnance_survey_client.property_type = asset_type_map[home["Asset Type"]]
    #     searcher.ordnance_survey_client.built_form = None
    #
    #     searcher.find_property(skip_os=True)
    #     if searcher.newest_epc is None:
    #         raise Exception("Couldn't find")
    #
    #     epc_data.append(
    #         {
    #             "Asset Reference": home["Asset Reference"],
    #             **searcher.newest_epc.copy()
    #         }
    #     )
    #
    # epc_data = pd.DataFrame(epc_data)
    epc_data = pd.read_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/Bexley EPC data.csv", )
    # epc_data.to_csv(
    #     "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/Bexley EPC data.csv", index=False
    # )

    epc_comparison = all_assets[
        ['Asset Reference', 'Address', 'PRE CALCULATED EPC']
    ].merge(
        epc_data[["Asset Reference", "current-energy-rating", "lodgement-date"]],
        on='Asset Reference',
        how="left"
    )

    # There are a large # of properties (147) that have different pre calcualted EPC rating, to what's on the registry
    # These may be internally held EPRs but this may inform which properties we might want to prioritise for survey
    different_epcs = epc_comparison[
        epc_comparison["PRE CALCULATED EPC"] != epc_comparison["current-energy-rating"]
        ]

    not_c = different_epcs[
        (different_epcs["PRE CALCULATED EPC"] == "C") &
        (different_epcs["current-energy-rating"] != "C")
        ]

    system_builds = below_epc_c[
        below_epc_c["Wall Type"].str.contains("SystemBuilt")
    ].copy()

    combinations = system_builds[
        ['Asset Type', 'Property Type', 'Location', 'PRE CALCULATED EPC', 'Wall Type', ]
    ].drop_duplicates()

    system_build_data_comparison = system_builds.merge(
        epc_data[
            ["Asset Reference", "walls-description", "roof-description", "current-energy-rating", "lodgement-date",
             "current-energy-efficiency"]],
        left_on='Asset Reference',
        right_on='Asset Reference',
        how="left"
    )

    # Apply patches
    patches = {
        25847: {"Property Type": "Semi Detached House"},
    }

    for asset_ref, patch in patches.items():
        for k, v in patch.items():
            system_build_data_comparison.loc[
                system_build_data_comparison["Asset Reference"] == asset_ref,
                k
            ] = v

    archetype_columns = [
        ["Asset Type", "Property Type", "Wall Type", "Location"],
        ["Asset Type", "Property Type", "Location"],
        ["Asset Type", "Property Type", "Wall Type", "Location", "PRE CALCULATED EPC", "roof-description"],
        ["Asset Type", "Property Type", "Location", "PRE CALCULATED EPC"]
    ]

    summary = []
    for cols in archetype_columns:
        combinations = system_build_data_comparison[cols].drop_duplicates()
        summary.append(
            {
                "cols": cols,
                "number_archetypes": len(combinations),
            }
        )

    summary = pd.DataFrame(summary)

    # Let's use this column combination
    chosen_combination = [
        "Asset Type", "Property Type", "Wall Type", "Location", "PRE CALCULATED EPC", "roof-description"
    ]

    # For this combination, let's find the properties
    archetype_combinations = system_build_data_comparison[chosen_combination].drop_duplicates().reset_index(drop=True)
    archetype_combinations["archetype ID"] = archetype_combinations.index

    archetyped_data = system_build_data_comparison.merge(
        archetype_combinations, how="left", on=chosen_combination
    )

    counts = archetyped_data["archetype ID"].value_counts()
    # Archetype 0: Semi D, As built system built, Pre calculated EPC D, flat insulated roof, (Lesney-0)
    # Archetype 1: Semi D, Externally insulated system built, Pre calculated EPC D, flat insulated roof (Lesney-1)
    # Archetype 4: Semi D, System built with unknown insulation, Pre calculated EPC D, flat roof insulated (Lesney-2)
    # Archetype 3: Semi D, Externally insulated system built, Pre calculated EPC D, flat roof uninsulated (assumed) (
    # Lesney-3)
    # 0    21
    # 1    11
    # 4    11
    # 3     3
    # 2     1
    # 5     1
    # 6     1
    # 7     1
    # 8     1
    # 9     1

    # This archetype is the same as 0, apart from the pre calculate EPC being an E. The registry says this is a D
    # This has been added to additonal units
    eg1 = archetyped_data[archetyped_data["archetype ID"] == 2]

    # Semi D, System built with unknown insulation, Pre calculated EPC D, flat roof insulated
    # This looks like it would fit either in archetype
    eg2 = archetyped_data[archetyped_data["archetype ID"] == 5]

    eg3 = archetyped_data[archetyped_data["archetype ID"] == 6]

    # Archetypes 7, 8, 9 are all similar, Semi D, Uninsulated system built, with pitched lofts with up to 200mm
    # insulation in the lofts

    # It's just the three units
    # They're all labelled as
    pitched_system_built_properties = archetyped_data[archetyped_data["archetype ID"].isin([9, 10, 11])]
    pitched_system_built_properties["Address"]

    notes = [
        {
            "Asset Reference": 27445,
            "note": "Confirmed this has a pitched roof on Maps"
        },
        {
            "Asset Reference": 27443,
            "note": "Confirmed this has a pitched roof on Maps"
        },
        {
            "Asset Reference": 27442,
            "note": "Confirmed this has a pitched roof on Maps"
        },
        {
            "Asset Reference": 25847,
            "note": "This is labelled as a mid-terrace but the EPC data + Maps suggest it's a semi-detached"
        }
    ]

    # These are As Built, System Built
    system_built_streets = (
        archetyped_data["Address"].str.split(",").str[0].str.split(" ").str[1].unique()
    )

    all_assets_w_epcs = all_assets.merge(epc_data, on="Asset Reference", how="left")

    # Grab all of the properties on this street that aren't system built
    streets_not_system_builds = all_assets_w_epcs[
        all_assets_w_epcs["Address"].str.split(",").str[0].str.split(" ").str[1].isin(system_built_streets) &
        ~all_assets_w_epcs["Wall Type"].str.contains("SystemBuilt")
        ]

    system_builds = archetyped_data[
        archetyped_data["Wall Type"].str.contains("SystemBuilt")
    ][["Asset Reference", "Address", "Wall Type", "walls-description"]].sort_values("Address")

    birling_street_system_builds = system_builds[system_builds["Address"].str.contains("Birling")]
    halstead_street_system_builds = system_builds[system_builds["Address"].str.contains("Halstead")]
    brasted_street_system_builds = system_builds[system_builds["Address"].str.contains("Brasted")]
    frinstead_street_system_builds = system_builds[
        system_builds["Address"].str.contains("Frinstead") | system_builds["Address"].str.contains("Frinsted")
        ]

    pd.set_option('display.max_rows', 500)
    pd.set_option('display.max_columns', 500)
    pd.set_option('display.width', 1000)
    streets_not_system_builds[["Asset Reference", "Address", "Wall Type", "walls-description"]]

    system_builds[system_builds["Address"].str.contains("Birling")]

    # Possible System Builds

    # Create the proposed sample
    # lesney-0
    archetyped_data["lodgement-date"] = pd.to_datetime(archetyped_data["lodgement-date"])

    lesney_0 = archetyped_data[archetyped_data["archetype ID"] == 0].copy()
    # Get the oldest EPC per postcode
    lesney_0 = lesney_0.sort_values(["Address - Postcode", "lodgement-date"])
    lesney_0[["Address", "Address - Postcode", "lodgement-date"]]

    lesney_1 = archetyped_data[archetyped_data["archetype ID"] == 1].copy()
    lesney_1 = lesney_1.sort_values(["Address - Postcode", "lodgement-date"])
    lesney_1[["Address", "Address - Postcode", "lodgement-date"]]

    lesney_2 = archetyped_data[archetyped_data["archetype ID"] == 4].copy()
    lesney_2 = lesney_2.sort_values(["Address - Postcode", "lodgement-date"])
    lesney_2[["Address", "Address - Postcode", "lodgement-date"]]

    lesney_3 = archetyped_data[archetyped_data["archetype ID"] == 3].copy()
    lesney_3 = lesney_3.sort_values(["Address - Postcode", "lodgement-date"])
    lesney_3[["Address", "Address - Postcode", "lodgement-date", "roof-description"]]

    # Get the pitched roof properties, which are lesney-4
    lesney_4 = archetyped_data[archetyped_data["archetype ID"].isin([7, 8, 9])].copy()
    lesney_4 = lesney_4.sort_values(["Address - Postcode", "lodgement-date"])
    lesney_4[["Address", "Address - Postcode", "lodgement-date", "roof-description"]]

    assigned_archetypes = archetyped_data[
        ["Asset Reference", "archetype ID", "Address", "Address - Postcode"] + chosen_combination +
        ["lodgement-date", "current-energy-rating", "current-energy-efficiency", "walls-description"]
        ].copy()
    # Map the archetype ID to their string representation
    assigned_archetypes["archetype ID"] = assigned_archetypes["archetype ID"].replace(
        {
            0: "Lesney-0",
            1: "Lesney-1",
            4: "Lesney-2",
            3: "Lesney-3",
            7: "Lesney-4",
            8: "Lesney-4",
            9: "Lesney-4",
            2: "Lesney-0",
            5: "Lesney-2",
            6: "Lesney-0",
        }
    )

    assigned_archetypes["Asset Reference"] = assigned_archetypes["Asset Reference"].astype(int)

    assigned_archetypes.to_csv(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/assigned_archetypes.csv", index=False
    )


def culworth_court():
    """
    Some rough works on Cuthwork Court

    They're looking at an ASHP/GSHP

    :return:
    """

    asset_list = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Orbit - Wates/001 - EPC CULWORTH COURT.xlsx",
        sheet_name="EPC C",
        header=1
    )
    asset_list = clean_colnames(asset_list)

    # Let's get the EPC data
    # Get the EPC data
    epc_data = []
    for _, home in tqdm(asset_list.iterrows(), total=len(asset_list)):

        address = home["Address"]
        # Spelling error
        if "Frinstead" in address:
            address = address.replace("Frinstead", "Frinsted")

        address1 = address.split(",")[0]

        asset_type_map = {
            "HOUSE": "House",
            "BUNGALOWS": "Bungalow",
            "FLATS": "Flat",
            "MAISONETTES": "Maisonette",
        }

        searcher = SearchEpc(
            address1=address1,
            postcode=home["Address - Postcode"],
            auth_token=EPC_AUTH_TOKEN,
            os_api_key="",
            full_address=address,
        )
        searcher.ordnance_survey_client.property_type = asset_type_map[home["Asset Type"]]
        searcher.ordnance_survey_client.built_form = None

        searcher.find_property(skip_os=True)
        if searcher.newest_epc is None:
            raise Exception("Couldn't find")

        epc_data.append(
            {
                "Asset Reference": home["Asset Reference"],
                **searcher.newest_epc.copy()
            }
        )
    epc_data = pd.DataFrame(epc_data)

    asset_list = asset_list.merge(epc_data, on="Asset Reference", how="left")
    asset_list["floor-level"] = np.where(
        asset_list["floor-level"] == "NODATA!",
        "",
        asset_list["floor-level"]
    )

    asset_list["built-form"] = np.where(
        asset_list["built-form"] == "Enclosed End-Terrace",
        "End-Terrace",
        asset_list["built-form"]
    )

    archetype_combinations = asset_list[
        ["Asset Type", "Property Type", "built-form", "floor-level"]
    ].drop_duplicates()

    z = asset_list[asset_list["built-form"] == "Enclosed End-Terrace"]
