import numpy as np
import pandas as pd
from asset_list.hubspot.config import HubspotProcessStatus

project_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Hubspot/Ealing Flats Completion Tracker JW "
    "170625.xlsx",
    sheet_name="All_Flats"
)

project_data["hubspot_status"] = None
project_data["hubspot_status"] = np.where(
    (project_data["Status"] == "Submitted") & (project_data["PAS"] == "PAS2023"),
    HubspotProcessStatus.SURVEYED_COMPLETED_SIGNED_OFF.label,
    project_data["hubspot_status"]
)
project_data["hubspot_status"] = np.where(
    (project_data["Status"] == "Submitted") & (project_data["PAS"] == "PAS2019"),
    "SURVEYED UNDER 2019 - NEEDS RE-SURVEY",
    project_data["hubspot_status"]
)
project_data["project_code"] = "EALING-FLATS-" + project_data["Block Ref"].astype(str)

asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Hubspot/20250707 Ealing Flats - Standardised.xlsx",
    sheet_name="Standardised Asset List"
)
asset_list["landlord_property_id"] = asset_list["landlord_property_id"].astype(str)
asset_list["incorrect_landlord_property_id"] = asset_list["incorrect_landlord_property_id"].astype(str)
project_data["Property ref"] = project_data["Property ref"].astype(str)

# We need to update the status of properties that already been surveyed
asset_list2 = asset_list.merge(
    project_data[["Property ref", "hubspot_status", "project_code"]],
    how="left",
    right_on="Property ref",
    left_on="incorrect_landlord_property_id",
    suffixes=("", "_project")
)
asset_list2["hubspot_status"] = np.where(
    ~pd.isna(asset_list2["hubspot_status_project"]),
    asset_list2["hubspot_status_project"],
    asset_list2["hubspot_status"]
)
asset_list2["project_code"] = np.where(
    ~pd.isna(asset_list2["project_code"]),
    asset_list2["project_code"],
    asset_list2["landlord_property_id"]
)

asset_list2 = asset_list2.drop(columns=["hubspot_status_project", "project_code_project"])
asset_list2["cavity_reason"] = np.where(
    pd.isnull(asset_list2["cavity_reason"]),
    "Non-Intrusive Data Shows Empty Cavity: SAP Rating 55-68",
    asset_list2["cavity_reason"]
)
asset_list2["solar_reason"] = None

# Read in block analysis and geographical areas from standardised asset list
block_analysis_df = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Hubspot/20250707 Ealing Flats - Standardised.xlsx",
    sheet_name="Block Analysis"
)
geographical_areas = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Hubspot/20250707 Ealing Flats - Standardised.xlsx",
    sheet_name="Geographical Areas"
)

# Update the new standardised asset list
filename = ("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Hubspot/20250707 Ealing Flats - Prepared "
            "programme.xlsx")
with pd.ExcelWriter(filename) as writer:
    asset_list2.to_excel(writer, sheet_name="Standardised Asset List", index=False)
    block_analysis_df.to_excel(writer, sheet_name="Block Analysis", index=False)
    geographical_areas.to_excel(writer, sheet_name="Geographical Areas", index=False)