"""
This script prepares the asset list for modelling the properties from the L&Q dataset, for their January IC
"""

import pandas as pd
import numpy as np

from etl.route_march_data_pull.app import get_data
from utils.s3 import save_csv_to_s3

PORTFOLIO_ID = 124
USER_ID = 8


def app():
    asset_data = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/L&G/Basildon information for Domna/Basildon MDS v1.4 "
        "(1).xlsx",
        sheet_name="Basildon",
        header=5
    )

    asset_data = asset_data.head(-3)

    asset_data["address1"] = np.where(
        pd.isnull(asset_data["Address 1"]),
        asset_data["Address 2"],
        asset_data["Address 1"]
    )

    asset_data["full_address"] = np.where(
        pd.isnull(asset_data["Address 1"]),
        asset_data["Address 2"] + ", " + asset_data["Address 3"],
        asset_data["Address 1"] + ", " + asset_data["Address 2"] + ", " + asset_data["Address 3"],
    )

    asset_list = asset_data[["address1", "PostCode", "full_address", "Bedrooms"]]

    asset_list = asset_list.reset_index(drop=True)

    asset_list["row_id"] = asset_list.index

    # L&G's focus:
    # Measures: loft and cavity insulation, replacement thermally efficient windows, PV cells, AS heat pumps.

    epc_data, errors, no_epc = get_data(
        asset_list=asset_list,
        fulladdress_column="full_address",
        address1_column="address1",
        postcode_column="PostCode",
        manual_uprn_map={}
    )

    missed = asset_list[
        asset_list["row_id"].isin(no_epc)
    ]

    # We merge on the property types, where we have them
    missed = missed.merge(
        asset_data[["address1", "PostCode", "Property Type"]],
        how="left",
        on=["address1", "PostCode"]
    )
    # Remap Block: Residential to Flat
    missed["Property Type"] = np.where(
        missed["Property Type"] == "Block: Residential",
        "Flat",
        missed["Property Type"]
    )

    # We create the asset list - we have some properties that genuninely never had an EPC

    epc_df = pd.DataFrame(epc_data)
    fetched_asset_list = epc_df[["address1", "postcode", "uprn", "row_id"]]
    fetched_asset_list = fetched_asset_list.merge(
        asset_list[["row_id", "Bedrooms"]],
        how="left",
        on=["row_id"]
    )

    missed = missed.rename(columns={"PostCode": "postcode"}).drop(columns=["row_id"])

    # missed.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/L&G/missed_epcs.csv")
    missed_uprns = pd.read_csv(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/L&G/missed_epcs_uprn.csv",
    )

    missed = missed.merge(
        missed_uprns[["address1", "postcode", "UPRN"]].rename(
            columns={"UPRN": "uprn"},
        ),
        how="left",
        on=["address1", "postcode"]
    )

    fetched_asset_list = fetched_asset_list.drop(columns=["row_id"])
    # We concatename them
    final_asset_list = pd.concat(
        [fetched_asset_list, missed[["address1", "postcode", "Property Type", "Bedrooms", "uprn"]]]
    )

    final_asset_list = final_asset_list.rename(
        columns={
            "address1": "address",
            "Property Type": "property_type",
            "Bedrooms": "n_bedrooms"
        }
    )

    # Finally, we merge on the numeber of bedrooms

    # Extract the non-invasive recommendations:
    non_invasive_recommendations = []
    for x in epc_data:
        non_invasive_recommendations.append(
            {
                "uprn": x["uprn"],
                "recommendations": x["find_my_epc_data"]["recommendations"]
            }
        )

    filename = f"{USER_ID}/{PORTFOLIO_ID}/asset_list.csv"
    save_csv_to_s3(
        dataframe=pd.DataFrame(final_asset_list),
        bucket_name="retrofit-plan-inputs-dev",
        file_name=filename
    )

    # Store the non-invasive recommendations in s3
    non_invasive_recommendations_filename = f"{USER_ID}/{PORTFOLIO_ID}/non_invasive_recommendations.csv"
    save_csv_to_s3(
        dataframe=pd.DataFrame(non_invasive_recommendations),
        bucket_name="retrofit-plan-inputs-dev",
        file_name=non_invasive_recommendations_filename
    )

    # Store the valuations data in s3
    # valuations_filename = f"{USER_ID}/{PORTFOLIO_ID}/valuations.csv"
    # save_csv_to_s3(
    #     dataframe=pd.DataFrame(valuations_data),
    #     bucket_name="retrofit-plan-inputs-dev",
    #     file_name=valuations_filename
    # )

    body = {
        "portfolio_id": str(PORTFOLIO_ID),
        "housing_type": "Private",
        "goal": "Increasing EPC",
        "goal_value": "A",
        "trigger_file_path": filename,
        "already_installed_file_path": "",
        "patches_file_path": "",
        "non_invasive_recommendations_file_path": non_invasive_recommendations_filename,
        "valuation_file_path": "",
        "scenario_name": "Retrofit Packages",
        "multi_plan": True,
        "budget": None,
        "inclusions": [
            "cavity_wall_insulation",
            "loft_insulation",
            "windows",
            "solar_pv",
            "air_source_heat_pump"
        ]
    }
    print(body)
