import pandas as pd

tabs = [
    "Straight Fill", "Solar PV - Straight Fill", "RDF CIGA checks", "Solar PV - RDF CIGA Checks",
    "AT BUILD", "Solar PV - AT BUILD"
]

programme_revisions = []
for tab in tabs:
    original_list = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Westward/Route March/WESTWARD - Route March Prep.xlsx",
        sheet_name=tab,
    )

    revised_list = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Westward/Route March/WESTWARD - GBIS List revised for "
        "Domna.xlsx",
        sheet_name=tab,
    )
    revised_list["Client Review"] = "Retain in programme"

    df = original_list[["Place ref"]].copy()
    df["Tab"] = tab

    df = df.merge(revised_list[["Place ref", "Client Review"]], how="left", on="Place ref")
    df["Client Review"] = df["Client Review"].fillna("Remove from programme")

    programme_revisions.append(df)

programme_revisions = pd.concat(programme_revisions)

# Read in the standardised asset list and create the column to append to that
al = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Westward/WESTWARD - completed list - "
    "08.05.2025 - Standardised - Client Review.xlsx",
    sheet_name="Standardised Asset List",
)

client_revisions = al[["landlord_property_id"]].merge(
    programme_revisions[["Place ref", "Client Review"]],
    how="left",
    left_on="landlord_property_id",
    right_on="Place ref",
)

client_revisions["Client Review"] = client_revisions["Client Review"].fillna("Needs Review")
client_revisions["Client Review Date"] = "08/05/2025"

client_revisions.to_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Westward/Route March/client_revisions.csv", index=False
)
