"""
This is the list of properties, based on the EPC data, that look eligible for WHLG
"""
import pandas as pd
from etl.epc.settings import EARLIEST_EPC_DATE
from etl.spatial.OpenUprnClient import OpenUprnClient

epc_data = pd.read_csv(
    "/Users/khalimconn-kowlessar/Downloads/all-domestic-certificates/domestic-E09000031-Waltham-Forest/certificates.csv"
)
epc_data.columns = [c.replace("_", "-").lower() for c in epc_data.columns]
epc_data = epc_data[epc_data["lodgement-date"] >= EARLIEST_EPC_DATE]

epc_data = epc_data[~pd.isnull(epc_data["uprn"])]
epc_data["uprn"] = epc_data["uprn"].astype(int)

epc_data = epc_data[epc_data["current-energy-rating"].isin(["D", "E", "F", "G"])]
epc_data = epc_data[epc_data["tenure"].isin(
    ["rental (private)", "Rented (private)", "owner-occupied", "Owner-occupied"])
]

whlg_eligible_postcodes = pd.read_excel(
    "/Users/khalimconn-kowlessar/Downloads/WHLG-eligible-postcodes.xlsx",
    sheet_name="Eligible postcodes",
    header=1
)
# Format:
whlg_eligible_postcodes = whlg_eligible_postcodes[['Postcode', 'Local Authority']]

uprns = epc_data["uprn"].unique()
# Get data
ca_data = OpenUprnClient.get_spatial_data(uprns, "retrofit-data-dev")
epc_data = epc_data.merge(
    ca_data[["UPRN", "conservation_status", "is_listed_building", "is_heritage_building"]].rename(
        columns={"UPRN": "uprn"}
    ),
    how="left",
    on="uprn",
)

epc_data["has_conservation_restrictions"] = (
    (epc_data["conservation_status"] == True)
    | (epc_data["is_listed_building"] == True)
    | (epc_data["is_heritage_building"] == True)
)

whlg_eligible_postcodes["Local Authority"].value_counts()

whlg_eligible_postcodes = whlg_eligible_postcodes[whlg_eligible_postcodes["Local Authority"] == "Waltham Forest"]

# Pathway 1:
# Match based on eligible postcodes
pathway1 = epc_data[epc_data["postcode"].isin(whlg_eligible_postcodes["Postcode"].values)]
pathway1 = pathway1[
    [
        "uprn", "address", "address1", "postcode", "current-energy-rating", "current-energy-efficiency",
        "lodgement-date",
        "has_conservation_restrictions", "walls-description", "roof-description", "mainheat-description"
    ]
]

pathway1 = pathway1.rename(
    columns={
        "current-energy-rating": "EPC Rating", "current-energy-efficiency": "SAP Score",
        "lodgement-date": "EPC Date", "has_conservation_restrictions": "Conservation Area Restrictions",
        "walls-description": "Wall Type", "roof-description": "Roof Type", "mainheat-description": "Main Heating"
    }
)

pathway1["EPC Date"] = pd.to_datetime(pathway1["EPC Date"]).dt.strftime("%Y-%m-%d")
# Create a year EPC was lodged
pathway1["EPC Year"] = pd.to_datetime(pathway1["EPC Date"]).dt.year

low_epc = pathway1[pathway1["EPC Rating"].isin(["F", "G"])]
low_epc["EPC Rating"].value_counts()
low_epc.tail(1)[["address", "postcode"]]

pathway1.to_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Waltham Forest WHLG - Pathway 1 Eligibility.csv",
    index=False
)

# Pathway 2 or 3
# The household will need to be means tested
pathway2 = epc_data[~epc_data["uprn"].isin(pathway1["uprn"].values)]
