import pandas as pd

houses_list = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Ealing BC - HOUSES(UNCHECKED).csv"
)

features = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Ealing BC - HOUSES(IGNORE - FULL MAIN).csv"
)
features = features.drop(
    columns=[
        'Archetype', 'Construction', 'Insulated', 'Material',
        'CIGA Check Required', 'PV, ACCESS ISSUE, SEE NOTES',
        'OFF GAS - ROOF ORIENTATION', 'Any further surveyor notes', 'Surveyors Name',
        'Unnamed: 30', 'Unnamed: 31'
    ]
)

demolitions = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Ealing - Demolished or due to be.xlsx",
    sheet_name="Demolished or due to be"
)

inspections_data = houses_list[
    [
        "Property ref", "Postcode", 'Archetype', 'Construction', 'Insulated', 'Material',
        'CIGA Check Required', 'PV, ACCESS ISSUE, SEE NOTES',
        'OFF GAS - ROOF ORIENTATION', 'Any further surveyor notes', 'YET TO BE SURVEYED'
    ]
].rename(columns={"YET TO BE SURVEYED": "Surveyors Name"})

asset_list = features.drop(
    columns=[
        'Archetype', 'Construction', 'Insulated', 'Material', 'CIGA Check Required',
        'PV, ACCESS ISSUE, SEE NOTES', 'OFF GAS - ROOF ORIENTATION',
        'Any further surveyor notes', 'Surveyors Name', "Postcode"
    ]
).merge(
    inspections_data,
    how="inner",
    on="Property ref",
)

asset_list.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Ealing/Ealing_rechecked_cleaned_05042025.csv",
                  index=False)
