import pandas as pd

initial_asset_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/2025_11_11 - Peabody "
    "- Data Extracts for Domna.xlsx",
    sheet_name="Properties"
)

sustainability_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/2025_11_11 - Peabody "
    "- Data Extracts for Domna.xlsx",
    sheet_name="Sustainability"
)

asset_data_v2 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/2025_11_11 "
    "- Peabody "
    "- Data Extracts for Domna v2.xlsx",
    sheet_name="Properties"
)

desired_ownerships = asset_data_v2[
    ~asset_data_v2["AH Tenure"].isin(
        {"Commercial",
         "Freeholder",
         "HOMEBUY / EQUITY LOAN",
         "Leaseholder",
         "Outright Sale",
         "SHARED EQUITY",
         "Shared Ownership"}
    )
]

desired_ownerships["Ownership Type"].value_counts()

removed_ownerships = initial_asset_data[
    ~initial_asset_data["UPRN"].isin(desired_ownerships["UPRN"].values)
]["Ownership Type"].value_counts()

sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260112 - "
    "ownership filtered sal.xlsx",
    sheet_name="Standardised Asset List"
)

# What did we include, that we shouldn't have?
should_have_been_dropped = sal[
    ~sal["landlord_property_id"].isin(desired_ownerships["UPRN"].values)
]

needs_to_be_added = desired_ownerships[
    ~desired_ownerships["UPRN"].isin(sal["landlord_property_id"].values)
]

# Merge on ownership types
sal = sal.merge(
    initial_asset_data[["UPRN", "Ownership Type"]],
    left_on="domna_property_id",
    right_on="UPRN",
)

# Remove the irrelevant ownership types
sal = sal[
    ~sal["Ownership Type"].isin(
        [
            # Commercial # Everything is resi - based on the Residential Indicator variable - all are true
            # Freeholder
            "FREEHOLDER",  # 19517 properties
            # HOMEBUY / EQUITY LOAN
            "Rent to Homebuy",  # 1 property
            # Leaseholder
            "LEASEHOLD 100%",  # 8455 properties
            "Owned and Managed - 999 year lease",  # 2076 properties
            "Managed but not Owned-Private Lease",  # 159 properties
            "Owned and managed LEASEHOLD",  # 26 properties
            # Outright Sale - can't find anything matching
            # SHARED EQUITY
            "Shared Ownership",  # 4065 properties
            "Shared Ownership Owned Not Managed",  # 23 properties
            # Extra categories which seem sensible to exclude
            "NOT MANAGED AND NOT OWNED"
        ]
    )
]

sal["landlord_property_id"] = sal["domna_property_id"].copy()

# Store this SAL in three batches
filename = (
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260112 - "
    "ownership filtered sal.xlsx"
)
with pd.ExcelWriter(filename) as writer:
    sal.to_excel(writer, sheet_name="Standardised Asset List", index=False)
    # Store the three sections
    sal[0:20000].to_excel(writer, sheet_name="Batch 1", index=False)
    sal[20000:40000].to_excel(writer, sheet_name="Batch 2", index=False)
    sal[40000:].to_excel(writer, sheet_name="Batch 3", index=False)

# Test reading back in and assembling
# b1 = pd.read_excel(
#     filename,
#     sheet_name="Batch 1"
# )
# b2 = pd.read_excel(
#     filename,
#     sheet_name="Batch 2"
# )
# b3 = pd.read_excel(
#     filename,
#     sheet_name="Batch 3"
# )
# assembled_sal = pd.concat([b1, b2, b3])
# # Make sure we have the right # of UPRNs
# assert assembled_sal["epc_os_uprn"].nunique() == sal["epc_os_uprn"].nunique()
