# After going back to Lincs rural, they gave us some additional data that we can use to try to fetch missed UPRNs again
import pandas as pd

# missed = pd.read_excel(
#     "/Users/khalimconn-kowlessar/Downloads/lincs_rural_missed_nov_2025.xlsx",
#     sheet_name="Missed Properties"
# )
# missed = missed[~pd.isnull(missed["rrn"])]

prepared = pd.read_excel(
    "/Users/khalimconn-kowlessar/Downloads/lincs_rural_standardised_ara_nov_2025.xlsx",
    sheet_name="Standardised Asset List"
)

updated_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Downloads/MASTER LIST EPCS UPDATED November 2025 Domna Homes - Copy.xlsx",
    sheet_name="PROPERTY EPC RATINGS"
)
updated_data = updated_data[~pd.isnull(updated_data["Property Ref."])]

missed = updated_data[~updated_data["Property Ref."].isin(prepared["landlord_property_id"].values.tolist())].copy()
# missed.to_csv("/Users/khalimconn-kowlessar/Downloads/lincs_rural_missed_uprn.csv")
# We'll grab the UPRNs manually and then pull them in, and prepare for ARA

missing_uprns = pd.read_csv("/Users/khalimconn-kowlessar/Downloads/lincs_rural_missed_uprn.csv")

missing_uprns["landlord_property_id"] = missing_uprns["Property Ref."].copy()
missing_uprns["domna_property_id"] = missing_uprns["Property Ref."].copy()
missing_uprns["domna_address_1"] = missing_uprns['Unnamed: 1'].str.split(",").str[0].str.strip()
missing_uprns["postcode"] = missing_uprns['Unnamed: 1'].str.split(",").str[-1].str.strip()
missing_uprns["landlord_property_type"] = "unknown"
missing_uprns["landlord_built_form"] = "unknown"
missing_uprns["domna_full_address"] = missing_uprns['Unnamed: 1'].copy()

missed_standardised_for_ara = missing_uprns[
    ['landlord_property_id', 'domna_address_1', 'landlord_property_type', 'landlord_built_form', 'postcode',
     'domna_property_id', 'UPRN']
].rename(
    columns={"UPRN": "epc_os_uprn"}
)

# Store
missed_standardised_for_ara.to_excel(
    "/Users/khalimconn-kowlessar/Downloads/lincs_rural_missed_standardised_ara_nov_2025.xlsx",
    index=False,
    sheet_name="Standardised Asset List"
)
