# get all properties that have an IWI recommendation
import pandas as pd

r1 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC B - no "
    "solid floor, no EWI, ashp 3.0 - 20250113 final.xlsx"
)

r2 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC C - no "
    "solid floor, ashp 3.0 - 20250113 final.xlsx"
)

r3 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC C - no "
    "solid floor, no EWI or IWI, ashp 3.0 - 20250113 final.xlsx"
)

s1 = r1[~pd.isnull(r1["internal_wall_insulation"])]
s2 = r2[~pd.isnull(r2["internal_wall_insulation"])]

# Combined uprns
uprns = s1["uprn"].tolist() + s2["uprn"].tolist()
uprns = list(set(uprns))

# Create SAL of these uprns
sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260113 - "
    "final asset list.xlsx",
    sheet_name="Standardised Asset List"
)

needing_retry = sal[sal["epc_os_uprn"].isin(uprns)]

# Store
needing_retry.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final "
    "SAL/properties_needing_retry_20260115 - internal wall insulation.xlsx",
    sheet_name="Standardised Asset List",
    index=False
)
