import time

import pandas as pd

from backend.SearchEpc import SearchEpc
from dotenv import load_dotenv
import os
from tqdm import tqdm
from utils.s3 import save_csv_to_s3, read_excel_from_s3

# Read in the .env file in backend
load_dotenv(dotenv_path="backend/.env")
EPC_AUTH_TOKEN = os.getenv("EPC_AUTH_TOKEN")
# Stored in my notes
ORDNANCE_SURVEY_API_KEY = ""

PORTFOLIO_ID = 80
USER_ID = 8


def extract_mds_measures(config):
    measures = []
    if not pd.isnull(config["EWI (Trad Const)"]):
        measures.append({"external_wall_insulation": "EWI (Trad Const)"})

    if not pd.isnull(config["EWI (Non Trad Const)"]):
        measures.append({"external_wall_insulation": "EWI (Non Trad Const)"})

    if not pd.isnull(config["CWI"]):
        measures.append({"cavity_wall_insulation": "CWI"})

    if not pd.isnull(config["LI"]):
        measures.append({"loft_insulation": "LI"})

    if not pd.isnull(config["Party Wall Insu"]):
        measures.append({"party_wall_insulation": "Party Wall Insu"})

    if not pd.isnull(config["IWI (POA - Prov Sum Only)"]):
        measures.append({"internal_wall_insulation": "IWI (POA - Prov Sum Only)"})

    if not pd.isnull(config["U/F Insu (Manual install)"]):
        measures.append({"suspended_floor_insulation": "U/F Insu (Manual install)"})

    if not pd.isnull(config["U/F insu (Qbot)"]):
        measures.append({"suspended_floor_insulation": "U/F insu (Qbot)"})

    if not pd.isnull(config["Solid floor insl (Out of scope - Prov sum only)"]):
        measures.append({"solid_floor_insulation": "Solid floor insl (Out of scope - Prov sum only)"})

    if not pd.isnull(config["ASHP Htg"]):
        measures.append({"air_source_heat_pump": "ASHP Htg"})

    if not pd.isnull(config["GSHP Htg"]):
        measures.append({"ground_source_heat_pump": "GSHP Htg"})

    if not pd.isnull(config["Shared ground loops"]):
        measures.append({"shared_ground_loops": "Shared ground loops"})

    if not pd.isnull(config["Communal heat networks"]):
        measures.append({"communal_heat_networks": "Communal heat networks"})

    if not pd.isnull(config["District heating networks"]):
        measures.append({"district_heating_networks": "District heating networks"})

    if not pd.isnull(config["Elec Storage Htrs (Out of scope -Prov sum only)"]):
        measures.append({"high_heat_retention_storage_heaters": "Elec Storage Htrs (Out of scope -Prov sum only)"})

    if not pd.isnull(config["Low Energy Bulbs"]):
        measures.append({"low_energy_lighting": "Low Energy Bulbs"})

    if not pd.isnull(config["Cyl Insulation"]):
        measures.append({"cylinder_insulation": "Cyl Insulation"})

    if not pd.isnull(config["Smart controls"]):
        measures.append({"smart_controls": "Smart controls"})

    if not pd.isnull(config["Zone controls"]):
        measures.append({"zone_controls": "Zone controls"})

    if not pd.isnull(config["Upgrade TRV's"]):
        measures.append({"trvs": "Upgrade TRV's"})

    if not pd.isnull(config["Solar PV"]):
        measures.append({"solar_pv": "Solar PV"})

    if not pd.isnull(config["Solar Thermal"]):
        measures.append({"solar_thermal": "Solar Thermal"})

    if not pd.isnull(config["Double Glazing (POA - Prov sum only)"]):
        measures.append({"double_glazing": "Double Glazing (POA - Prov sum only)"})

    if not pd.isnull(config["Draught Proofing"]):
        measures.append({"draught_proofing": "Draught Proofing"})

    if not pd.isnull(config["Ventilation upgrade"]):
        measures.append({"mechanical_ventilation": "Ventilation upgrade"})

    if not pd.isnull(config["Gas Boiler Replacement"]):
        measures.append({"gas_boiler": "Gas Boiler Replacement"})

    if not pd.isnull(config["Flat roof (Out of scope - prov sum only)"]):
        measures.append({"flat_roof_insulation": "Flat roof (Out of scope - prov sum only)"})

    if not pd.isnull(config["RIR (POA - Prov sum only)"]):
        measures.append({"room_in_roof_insulation": "RIR (POA - Prov sum only)"})

    if not pd.isnull(config["EV Charging"]):
        measures.append({"ev_charging": "EV Charging"})

    if not pd.isnull(config["Battery"]):
        measures.append({"battery": "Battery"})

    return measures


def parse_property_type(config):
    # This should come from the ordnance survey api eventually

    # array(['Detached', 'Semi-detached', 'Bungalow', 'Mid Terrace',
    #        'End Terrace', 'Top Flat', 'Mid Flat',
    #        'Low rise flat (1-2 storey)', nan], dtype=object)

    if config["Address"] == "Flat Central Garage":
        return {"property_type": "Bungalow", "built_form": "Mid-Terrace"}

    if pd.isnull(config["Property Type"]):
        return {"property_type": None, "built_form": None}

    lookup = {
        "Detached": {"property_type": "House", "built_form": "Detached"},
        "Semi-detached": {"property_type": "House", "built_form": "Semi-detached"},
        "Bungalow": {"property_type": "Bungalow", "built_form": "Detached"},
        "Mid Terrace": {"property_type": "House", "built_form": "Mid-Terrace"},
        "End Terrace": {"property_type": "House", "built_form": "End-Terrace"},
        "Top Flat": {"property_type": "Flat", "built_form": None},
        "Mid Flat": {"property_type": "Flat", "built_form": None},
        "Low rise flat (1-2 storey)": {"property_type": "Flat", "built_form": None},
    }

    return lookup[config["Property Type"]]


def app():
    """
    Create the initial asset list for the E.ON pilot
    :return:
    """

    raw_asset_list = read_excel_from_s3(
        bucket_name="retrofit-datalake-dev",
        file_key="customers/E.ON/sample SHDF Information MDS Template Vr3.0.xlsx",
        header_row=11,
        drop_all_na=False
    )

    # Keep just the columns we need
    raw_asset_list_base = raw_asset_list[
        [
            "Address", "Postcode", "No Bedrooms"
        ]
    ].copy().rename(
        columns={
            "Address": "address",
            "Postcode": "postcode",
            "No Bedrooms": "n_bedrooms"
        }
    )

    # For each property, retrieve UPRN with from the Ordnance Survey API. To do this, I have created a free
    # trial with Ordnance Survey with my personal account as a temporary solution.
    # Let's just pull the full EPC data for this
    asset_list_with_uprn = []
    for row, property_meta in tqdm(raw_asset_list_base.iterrows(), total=raw_asset_list_base.shape[0]):
        searcher = SearchEpc(
            address1=property_meta["address"],
            postcode=property_meta["postcode"],
            auth_token=EPC_AUTH_TOKEN,
            os_api_key=ORDNANCE_SURVEY_API_KEY,
            full_address=", ".join([property_meta["address"], property_meta["postcode"]])
        )

        searcher.find_property(skip_os=True)
        if searcher.newest_epc["uprn-source"] == SearchEpc.UPRN_SOURCE_SIMULATED:
            uprn = None
        else:
            uprn = searcher.uprn
        # searcher.find_property(skip_os=False)

        asset_list_with_uprn.append(
            {
                **property_meta,
                "uprn": uprn,
                "matched_address": searcher.address1,
                "matched_postcode": searcher.postcode
            }
        )

    # Store this as a backup
    # import pandas as pd
    # asset_list_with_uprn_df = pd.DataFrame(asset_list_with_uprn)
    # asset_list_with_uprn_df.to_csv("eon_asset_list_with_uprn_2.csv", index=False)
    # Read in
    # asset_list_with_uprn = pd.read_csv("eon_asset_list_with_uprn.csv").to_dict(orient="records")

    # Store the asset list and create the portfolio payload
    asset_list_with_uprn_df = pd.DataFrame(asset_list_with_uprn)
    asset_list_with_uprn_df["uprn"] = asset_list_with_uprn_df["uprn"].astype(str).astype(int)

    # We now determine which measures we need for each property
    finalised_asset_list = []
    for i, config in raw_asset_list.iterrows():

        asset_config = asset_list_with_uprn_df[
            (asset_list_with_uprn_df["address"] == config["Address"]) &
            (asset_list_with_uprn_df["postcode"] == config["Postcode"])
            ]
        if asset_config.shape[0] != 1:
            raise ValueError("Could not find a unique match for the property")

        measures = extract_mds_measures(config)

        # Get the property type
        pt = parse_property_type(config)

        if config["Address"] in [
            "28 Hermitage Lane",
            "35a High Street",
            "35b High Street",
            "Flat Over 20 Holborough Road",
            "Flat above 7 Malling Road",
            "Cobnut Barn",
        ]:
            print(config["Address"])
            uprn = None
        else:
            uprn = asset_config["uprn"].values[0]

        finalised_asset_list.append(
            {
                "address": config["Address"],
                "postcode": config["Postcode"],
                "uprn": uprn,
                "n_bedrooms": config["No Bedrooms"],
                "measures": measures,
                **pt
            }
        )
    finalised_asset_list = pd.DataFrame(finalised_asset_list)

    # Store the asset list in s3
    filename = f"{USER_ID}/{PORTFOLIO_ID}/pilot.csv"
    save_csv_to_s3(
        dataframe=finalised_asset_list,
        bucket_name="retrofit-plan-inputs-dev",
        file_name=filename
    )

    # EPC C portoflio
    body = {
        "portfolio_id": str(PORTFOLIO_ID),
        "housing_type": "Social",
        "goal": "Increase EPC",
        "goal_value": "C",
        "trigger_file_path": filename,
        "already_installed_file_path": "",
        "patches_file_path": "",
        "non_invasive_recommendations_file_path": "",
        "budget": None,
    }
    print(body)

    # Optimised version where we specify the measures
    measures = [
        "external_wall_insulation",
        "cavity_wall_insulation",
        "loft_insulation",
        "air_source_heat_pump",
        "high_heat_retention_storage_heaters",
        "solar_pv"
    ]

    body = {
        "portfolio_id": str(PORTFOLIO_ID),
        "housing_type": "Social",
        "goal": "Increase EPC",
        "goal_value": "C",
        "trigger_file_path": filename,
        "already_installed_file_path": "",
        "patches_file_path": "",
        "non_invasive_recommendations_file_path": "",
        "measures": measures,
        "budget": None,
    }


output = []
for r in self.results:
    output.append(r["DPA"])

output = pd.DataFrame(output)
