import pandas as pd

comments_df = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/CBH_RetroTeamList_amended_25-06-05.xlsx",
)

cavity_route = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/20250708 Colchester Borough Homes- Standardised.xlsx",
    sheet_name="July 2025 Route - Cavity"
)

solar_route = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/20250708 Colchester Borough Homes- Standardised.xlsx",
    sheet_name="July 2025 Route - Solar"
)

# Merge on the comments
comments = comments_df[
    ["URPN", 'Unnamed: 6', 'SHDF Live', 'SHDF Removed', 'SHDF Reserve', '25-26 List (138 to EPC)']
].copy()

cavity_route = cavity_route.merge(
    comments, left_on="landlord_property_id", right_on="URPN", how="left"
)
solar_route = solar_route.merge(
    comments, left_on="landlord_property_id", right_on="URPN", how="left"
)

# Get properties that are not on either route
not_on_routes = comments_df[
    ~comments_df["URPN"].isin(cavity_route["landlord_property_id"]) &
    ~comments_df["URPN"].isin(solar_route["landlord_property_id"])
    ]

# Store
not_on_routes.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/Properties not on routes.xlsx",
    index=False
)
# Save the routes
cavity_route.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/Cavity Route.xlsx",
    index=False
)
solar_route.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Colchester/July Finalised "
    "Project/Solar Route.xlsx",
    index=False
)
