# 1) Need to get all already installed measures
# 2) get the unique uprns for these properties
# 3) Create a re-fresh SAL for these properties
# 4) re-trigger EPC C w/o EWI/IWI + the EPC B scenario

from backend.app.db.models.recommendations import InstalledMeasure
from backend.app.db.connection import db_session
from etl.customers.cambridge.surveys import current_epc

# Get all installed measures from the installedMeasure table
with db_session() as session:
    # We need installed measures, where the measure type is ewi or iwi
    installed_measures = session.query(InstalledMeasure).filter(
    ).all()
    # Get the uprns
    installed_uprns = [x.uprn for x in installed_measures]

installed_uprns = list(set(installed_uprns))

# We then create a portfolio of properties we need to re-run
import pandas as pd

sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260113 - "
    "final asset list.xlsx",
    sheet_name="Standardised Asset List"
)

needing_retry = sal[sal["epc_os_uprn"].isin(installed_uprns)]

# Store
needing_retry.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final "
    "SAL/properties_needing_retry_20260115 - all already installed.xlsx",
    sheet_name="Standardised Asset List",
    index=False
)

#### Testing
with_ewi = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC C - no "
    "solid floor, ashp 3.0 - 20250113 final.xlsx"
)
without_ewi = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC C - no "
    "solid floor, no EWI or IWI, ashp 3.0 - 20250113 final.xlsx"
)

comparison = with_ewi.merge(
    without_ewi,
    left_on="uprn",
    right_on="uprn",
    suffixes=("_with_ewi", "_without_ewi")
)

with_ewi = comparison[comparison["total_retrofit_cost_with_ewi"] > 0]
with_ewi["current_epc_rating_with_ewi"].value_counts()
with_ewi["current_epc_rating_with_ewi"].value_counts()

without_ewi = comparison[comparison["total_retrofit_cost_without_ewi"] > 0]
with_ewi = comparison[comparison["total_retrofit_cost_with_ewi"] > 0]

with_ewi[with_ewi["current_epc_rating_with_ewi"] == "Epc.C"]["uprn"]

to_fix = with_ewi[with_ewi["current_epc_rating_with_ewi"] == "Epc.C"]
to_fix = to_fix[["uprn", "address_with_ewi", "postcode_with_ewi", "property_type_with_ewi"]].rename(
    columns={
        "address_with_ewi": "address",
        "postcode_with_ewi": "postcode",
        "property_type_with_ewi": "property_type"
    }
).merge(
    sal[["epc_os_uprn", "landlord_built_form"]],
    left_on="uprn",
    right_on="epc_os_uprn",
    how="left"
).drop(columns=["epc_os_uprn"])

to_fix = to_fix.to_dict("records")
