"""
July 2025, this script prepares the asset list for Plus Dane
"""
import pandas as pd

oldest_asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Plus Dane/New Programme July 2025/PLUS DANE Asset List.xlsx"
)
solar_asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Plus Dane/New Programme July 2025/Plus Dane - potential "
    "PV List 04.03.2025.xlsx"
)
newest_asset_list = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Plus Dane/New Programme July 2025/Sava Intelligent Energy "
    "- Property List - March 2025.xlsx"
)

old_missed = oldest_asset_list[~oldest_asset_list["UPRN"].isin(newest_asset_list["UPRN"])]
solar_missed = solar_asset_list[~solar_asset_list["UPRN"].isin(newest_asset_list["UPRN"])]  # Empty

# Build new asset list
# NEWEST
# 'UPRN', 'Address', 'Postcode', 'Town', 'EPC SAP Band', 'SAP Rating',
#        'CO₂ Emissions', 'EPC EI Band', 'Data Quality Indicator',
#        'Results Calculated', 'Property Age', 'Property Type', 'Built Form',
#        'Wall Construction', 'Wall Insulation', 'Roof Construction',
#        'Joist Insulation', 'Space Heating System', 'Space Heating Fuel'
#
# SOlAR

df = newest_asset_list.merge(
    solar_asset_list, how="left", on="UPRN", suffixes=("", "_solar"),
).merge(
    oldest_asset_list, how="left", on="UPRN", suffixes=("", "_old")
)
df["asset_list_versiion"] = "July 2025"
old_missed["asset_list_versiion"] = "Historic"

# Append on the old missed?
df = pd.concat(
    [df, old_missed], ignore_index=True, sort=False
)
# Store excel
df.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Plus Dane/New Programme July 2025/Plus Dane Asset List "
    "July 2025.xlsx",
    index=False,
)