"""
Brentwood sent us a new asset list in July 2025. This script will combine the data in the new asset list with the
old, so we have a single picture
"""

import pandas as pd

new_asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Brentwood/July 2025 New Programme/All Assets "
    "29.05.2025.xlsx",
    sheet_name="Sheet1",
    header=1
)

old_asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Brentwood/July 2025 New Programme/BRENTWOOD Asset "
    "list.xlsx",
    sheet_name="Asset List"
)

# We combine based on the data we want
compiled = new_asset_list.merge(
    old_asset_list[["UPRN", "Asset Type", "Year Built", "Dwelling", "Bedrooms", "Ownership", 'Asbestos Full Survey',
                    'Stock Condition Survey', 'Cat', 'Heating',
                    'WFT Findings', 'ECO Eligibility', 'CIGA Requested', 'CIGA Guarantee',
                    'ECO Survey completed']],
    how="left",
    on="UPRN"
)

compiled["WFT Findings"] = compiled["WFT Findings"].fillna("Not Inspected")

# Store this data
compiled.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Brentwood/July 2025 New Programme/20250710 Asset List "
    "Brentwood.xlsx",
    index=False
)
