import pandas as pd

### Prepare
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"
)

# Data we want to remove:
missing_uprns = sustainability_data[pd.isnull(sustainability_data['UPRN'])].copy()

# Any non-numeric UPRNS or leading with 0s are invalid
non_numeric_uprns = sustainability_data[
    ~sustainability_data['UPRN'].astype(str).str.match(r'^[1-9][0-9]*$') & ~pd.isnull(sustainability_data['UPRN'])
    ].copy()
# 70 properties
leading_zero_uprns = sustainability_data[
    sustainability_data['UPRN'].astype(str).str.startswith('0')
].copy()

# Flag duplicates
duplicate_uprns = sustainability_data[
    sustainability_data.duplicated(subset=['UPRN'], keep=False) & ~pd.isnull(sustainability_data['UPRN'])
    ].copy()

# Read in the UPRNs that were not valid based on the OS data
os_missed_uprns = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
    "Project/data_validation/os_missed_uprns.csv",
)

modelling_data = sustainability_data[
    ~sustainability_data["Org Ref"].isin(
        missing_uprns["Org Ref"].unique().tolist() + non_numeric_uprns["Org Ref"].unique().tolist() +
        leading_zero_uprns["Org Ref"].unique().tolist() + duplicate_uprns["Org Ref"].unique().tolist() +
        os_missed_uprns["Org Ref"].unique().tolist()
    )
].copy()

# Need to prepare for upload
# Variables:


modelling_data["landlord_property_id"] = sustainability_data["Org Ref"].copy()
modelling_data["domna_property_id"] = sustainability_data["Org Ref"].copy()

modelling_data = modelling_data.rename(
    columns={
        "Address 1": "domna_address_1",
        "Postcode": "postcode",
        "Type": "landlord_property_type",
        "Attachment": "landlord_built_form",
        "Heating": "landlord_heating_system",
        "UPRN": "epc_os_uprn"
    }
)


def make_full_address(x):
    to_join = [x['domna_address_1'], x['Address 2'], x['Address 3']]
    to_join = [x for x in to_join if not pd.isnull(x) and x != '']
    return ", ".join(to_join)


modelling_data["domna_full_address"] = modelling_data.apply(lambda x: make_full_address(x), axis=1)

modelling_data = modelling_data[
    [
        "domna_address_1", "Address 2", "Address 3", "postcode", "landlord_property_type",
        "landlord_built_form", "landlord_heating_system", "epc_os_uprn", "Total Floor Area (m2)",
        "domna_property_id", "domna_full_address"
    ]
]

modelling_data["landlord_built_form"] = modelling_data["landlord_built_form"].map(
    {
        "MidTerrace": "Mid-Terrace",
        "EndTerrace": "End-Terrace",
        "SemiDetached": "Semi-Detached",
        "Detached": "Detached",
        "EnclosedEndTerrace": "Enclosed End-Terrace",
        "EnclosedMidTerrace": "Enclosed Mid-Terrace",
    }
)

filename = ("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/20251213 Model "
            "data.xlsx")
with pd.ExcelWriter(filename) as writer:
    modelling_data.to_excel(writer, sheet_name="Standardised Asset List", index=False)
    # Store the three sections
    modelling_data[0:30000].to_excel(writer, sheet_name="Part 1", index=False)
    modelling_data[30000:60000].to_excel(writer, sheet_name="Part 2", index=False)
    modelling_data[60000:].to_excel(writer, sheet_name="Part 3", index=False)
    modelling_data.sample(60).to_excel(writer, sheet_name="Random testing sample", index=False)
