import pandas as pd
from tqdm import tqdm
from sqlalchemy.orm import sessionmaker
from backend.app.db.connection import db_engine, db_read_session, db_session
from backend.app.db.models.recommendations import (
    Recommendation,
    PlanModel,
    PlanRecommendations,
    RecommendationMaterials,
    InstalledMeasure,
)
from backend.app.db.models.portfolio import PropertyModel, PropertyDetailsEpcModel
from backend.app.utils import sap_to_epc
from typing import Dict, List, Set
from datatypes.epc.domain.epc import Epc
from recommendations.Costs import Costs

pd.set_option("display.max_rows", 500)
pd.set_option("display.max_columns", 500)
pd.set_option("display.width", 1000)


def get_all_data(portfolio_id, scenario_ids):
    session = sessionmaker(bind=db_engine)()
    session.begin()

    # --------------------
    # Properties
    # --------------------
    properties_query = (
        session.query(PropertyModel, PropertyDetailsEpcModel)
        .join(
            PropertyDetailsEpcModel,
            PropertyModel.id == PropertyDetailsEpcModel.property_id,
        )
        .filter(PropertyModel.portfolio_id == portfolio_id)
        .all()
    )

    properties_data = [
        {
            **{
                col.name: getattr(p.PropertyModel, col.name)
                for col in PropertyModel.__table__.columns
            },
            **{
                col.name: getattr(p.PropertyDetailsEpcModel, col.name)
                for col in PropertyDetailsEpcModel.__table__.columns
            },
        }
        for p in properties_query
    ]

    # --------------------
    # Plans
    # --------------------
    plans_query = (
        session.query(PlanModel).filter(PlanModel.scenario_id.in_(scenario_ids)).all()
    )

    plans_data = [
        {col.name: getattr(plan, col.name) for col in PlanModel.__table__.columns}
        for plan in plans_query
    ]

    plan_ids = [p["id"] for p in plans_data]

    # --------------------
    # Recommendations (NO materials yet)
    # --------------------
    recommendations_query = (
        session.query(Recommendation, PlanModel.scenario_id)
        .join(
            PlanRecommendations,
            Recommendation.id == PlanRecommendations.recommendation_id,
        )
        .join(PlanModel, PlanModel.id == PlanRecommendations.plan_id)
        .filter(
            PlanRecommendations.plan_id.in_(plan_ids),
        )
        .all()
    )

    recommendations_data = [
        {
            **{
                col.name: getattr(r.Recommendation, col.name)
                for col in Recommendation.__table__.columns
            },
            "scenario_id": r.scenario_id,
            "materials": [],  # placeholder
        }
        for r in recommendations_query
    ]

    session.close()

    return properties_data, plans_data, recommendations_data


PORTFOLIO_ID = 431  # Peabody - new portfolio
SCENARIOS = [
    891,  # EPC B - No solid floor, ASHP COP 3.0
]

# properties_data, plans_data, recommendations_data = get_all_data(portfolio_id=PORTFOLIO_ID, scenario_ids=SCENARIOS)
# # Store this data as dataframes for analysis
# properties_df = pd.DataFrame(properties_data)
# plans_df = pd.DataFrame(plans_data)
# recommendations_df = pd.DataFrame(recommendations_data)

# Save CSVs
# properties_df.to_csv(
#     "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/"
#     "Final portfolio datasets/v2/peabody_properties_data_20260108.csv",
#     index=False
# )
# plans_df.to_csv(
#     "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/"
#     "Final portfolio datasets/v2/peabody_plans_data_20260108.csv",
#     index=False
# )
# recommendations_df.to_csv(
#     "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/"
#     "Final portfolio datasets/v2/peabody_recommendations_data_20260108.csv",
#     index=False
# )
# Read csvs
properties_df = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
    "Project/Final portfolio datasets/v2/peabody_properties_data_20260108.csv"
)
plans_df = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final portfolio "
    "datasets/v2/peabody_plans_data_20260108.csv"
)
recommendations_df = pd.read_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final portfolio "
    "datasets/v2/peabody_recommendations_data_20260108.csv"
)

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",
)
sustainability_data_with_sap = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/Parity Data "
    "08012026.xlsx",
)

properties_df["uprn"] = properties_df["uprn"].astype(str)
property_data_comparison = properties_df.merge(
    sustainability_data,
    how="inner",
    left_on="uprn",
    right_on="UPRN",
    suffixes=("_prop", "_sust"),
)

property_data_comparison["wall_type"] = (
    property_data_comparison["walls"].str.split(",").str[0].str.strip()
)

column_pairs = {
    "built_form": "Attachment",
    "property_type": "Type",
    "wall_type": "Wall Construction",
    "heating": "Heating",
}
combination_tables = {}

for v1, v2 in column_pairs.items():
    df = property_data_comparison.groupby([v1, v2]).size().reset_index(name="count")
    combination_tables[v1] = df

# We just need all of the measure types, per property
recommendation_measure_types = recommendations_df[
    [
        "property_id",
        "measure_type",
        "sap_points",
        "heat_demand",
        "kwh_savings",
        "co2_equivalent_savings",
        "energy_cost_savings",
    ]
].drop_duplicates()
recommendation_measure_types["flag"] = True

# We pivot
recommendations_measures_pivot = (
    recommendation_measure_types[["property_id", "measure_type", "flag"]]
    .drop_duplicates()
    .pivot(index="property_id", columns="measure_type", values="flag")
)
recommendations_measures_pivot = recommendations_measures_pivot.reset_index()

properties_to_recs = properties_df.rename(columns={"solar_pv": "solar_data"}).merge(
    recommendations_measures_pivot, how="left", on="property_id"
)

sustainability_data["cavity_wall_insulation"] = sustainability_data[
    "Wall Insulation"
].isin(["FilledCavity", "FilledCavityPlusInternal", "FilledCavityPlusExternal"])
sustainability_data["internal_wall_insulation"] = sustainability_data[
    "Wall Insulation"
].isin(["Internal", "FilledCavityPlusInternal"])
sustainability_data["external_wall_insulation"] = sustainability_data[
    "Wall Insulation"
].isin(["External", "FilledCavityPlusExternal"])
sustainability_data["loft_insulation"] = sustainability_data["Roof Insulation"].isin(
    ["mm300", "mm250", "mm350", "mm400", "mm270"]
)
sustainability_data["double_glazing"] = sustainability_data["Glazing"].isin(
    [
        "Double 2002 or later",
        "Double but age unknown",
        "Triple",
        "DoubleKnownData",
        "Secondary",
        "TripleKnownData",
    ]
)
sustainability_data["secondary_glazing"] = sustainability_data["Glazing"].isin(
    ["Secondary"]
)

sustainability_data["suspended_floor_insulation"] = sustainability_data[
    "Floor Insulation"
].isin(["RetroFitted"])

sustainability_data["boiler_upgrade"] = sustainability_data["Heating"].isin(
    ["Boilers"]
) & sustainability_data["Boiler Efficiency"].isin(["A"])
sustainability_data["air_source_heat_pump"] = sustainability_data["Heating"].isin(
    ["Heat pumps (wet)"]
)

sustainability_data["time_temperature_zone_control"] = sustainability_data[
    "Controls Adequacy"
].isin(["Top Spec"])

sustainability_data["roomstat_programmer_trvs"] = sustainability_data[
    "Controls Adequacy"
].isin(["Optimal"])
sustainability_data["flat_roof_insulation"] = (
    sustainability_data["Roof Construction"] == "Flat"
) & (sustainability_data["Roof Insulation"].isin(["mm50", "mm150", "mm100"]))

properties_to_recs["uprn"] = properties_to_recs["uprn"].astype(str)
comparison = sustainability_data.merge(
    properties_to_recs[
        [
            "uprn",
            "cavity_wall_insulation",
            "external_wall_insulation",
            "internal_wall_insulation",
            "loft_insulation",
            "double_glazing",
            "secondary_glazing",
            "suspended_floor_insulation",
            "boiler_upgrade",
            "air_source_heat_pump",
            "time_temperature_zone_control",
            "roomstat_programmer_trvs",
            "flat_roof_insulation",
            "room_roof_insulation",
        ]
    ],
    left_on="UPRN",
    right_on="uprn",
    how="left",
    suffixes=("", "_from_recs"),
)

# Flag entries where we've been told that walls are already insulated, but we have recommendations for wall insulation
# ------------ Walls ------------
cwi_conflicting = comparison[
    (comparison["cavity_wall_insulation"])
    & (pd.isnull(comparison["cavity_wall_insulation_from_recs"]) == False)
].copy()
cwi_conflicting["conflict_cavity_wall_insulation"] = True
iwi_conflicting = comparison[
    (comparison["internal_wall_insulation"])
    & (pd.isnull(comparison["internal_wall_insulation_from_recs"]) == False)
].copy()
iwi_conflicting["conflict_iwi_wall_insulation"] = True

ewi_conflicting = comparison[
    (comparison["external_wall_insulation"])
    & (pd.isnull(comparison["external_wall_insulation_from_recs"]) == False)
].copy()
ewi_conflicting["conflict_ewi_wall_insulation"] = True

# ------------ Roof ------------
loft_conflicting = comparison[
    (comparison["loft_insulation"])
    & (pd.isnull(comparison["loft_insulation_from_recs"]) == False)
].copy()
loft_conflicting["conflict_loft_insulation"] = True

# ------------ Windows ------------
double_glazing_conflicting = comparison[
    (comparison["double_glazing"] | comparison["secondary_glazing"])
    & (pd.isnull(comparison["double_glazing_from_recs"]) == False)
    & (pd.isnull(comparison["secondary_glazing_from_recs"]) == True)
].copy()
double_glazing_conflicting["conflict_double_glazing"] = True
secondary_glazing_conflicting = comparison[
    (comparison["secondary_glazing"])
    & (pd.isnull(comparison["secondary_glazing_from_recs"]) == False)
].copy()
secondary_glazing_conflicting["conflict_secondary_glazing"] = True

# ------------ Floors ------------
floors_conflicting = comparison[
    (comparison["suspended_floor_insulation"])
    & (pd.isnull(comparison["suspended_floor_insulation_from_recs"]) == False)
].copy()
floors_conflicting["conflict_suspended_floor_insulation"] = True

# ------------ Boiler Upgrade ------------
boiler_conflicting = comparison[
    (comparison["boiler_upgrade"])
    & (pd.isnull(comparison["boiler_upgrade_from_recs"]) == False)
].copy()
boiler_conflicting["conflict_boiler_upgrade"] = True

# ------------ ASHP ------------
ashp_conflicting = comparison[
    (comparison["air_source_heat_pump"])
    & (pd.isnull(comparison["air_source_heat_pump_from_recs"]) == False)
].copy()
ashp_conflicting["conflict_air_source_heat_pump"] = True

# ------------ heat controls ------------
ttzc_conflicting = comparison[
    (comparison["time_temperature_zone_control"])
    & (pd.isnull(comparison["time_temperature_zone_control_from_recs"]) == False)
].copy()
ttzc_conflicting["conflict_time_temperature_zone_control"] = True
rst_conflicting = comparison[
    (comparison["roomstat_programmer_trvs"])
    & (pd.isnull(comparison["roomstat_programmer_trvs_from_recs"]) == False)
].copy()
rst_conflicting["conflict_roomstat_programmer_trvs"] = True

# ------------ Flat Roof Insulation -----------
flat_roof_conflicting = comparison[
    (comparison["flat_roof_insulation"])
    & (pd.isnull(comparison["flat_roof_insulation_from_recs"]) == False)
].copy()
flat_roof_conflicting["conflict_flat_roof_insulation"] = True

# All properties with conflicts
all_conflicts = pd.concat(
    [
        cwi_conflicting,
        iwi_conflicting,
        ewi_conflicting,
        loft_conflicting,
        double_glazing_conflicting,
        secondary_glazing_conflicting,
        floors_conflicting,
        boiler_conflicting,
        ashp_conflicting,
        ttzc_conflicting,
        rst_conflicting,
        flat_roof_conflicting,
    ]
)

all_conflicts = all_conflicts[
    [
        "uprn",
        "conflict_cavity_wall_insulation",
        "conflict_iwi_wall_insulation",
        "conflict_ewi_wall_insulation",
        "conflict_loft_insulation",
        "conflict_double_glazing",
        "conflict_secondary_glazing",
        "conflict_suspended_floor_insulation",
        "conflict_boiler_upgrade",
        "conflict_air_source_heat_pump",
        "conflict_time_temperature_zone_control",
        "conflict_roomstat_programmer_trvs",
        "conflict_flat_roof_insulation",
    ]
]

all_conflicts = all_conflicts.rename(
    columns={
        "conflict_cavity_wall_insulation": "cavity_wall_insulation",
        "conflict_iwi_wall_insulation": "internal_wall_insulation",
        "conflict_ewi_wall_insulation": "external_wall_insulation",
        "conflict_loft_insulation": "loft_insulation",
        "conflict_double_glazing": "double_glazing",
        "conflict_secondary_glazing": "secondary_glazing",
        "conflict_suspended_floor_insulation": "suspended_floor_insulation",
        "conflict_boiler_upgrade": "boiler_upgrade",
        "conflict_air_source_heat_pump": "air_source_heat_pump",
        "conflict_time_temperature_zone_control": "time_temperature_zone_control",
        "conflict_roomstat_programmer_trvs": "roomstat_programmer_trvs",
        "conflict_flat_roof_insulation": "flat_roof_insulation",
    }
)

# Reshape by UPRN by melting
all_conflicts = all_conflicts.melt(
    id_vars=["uprn"], var_name="measure_type", value_name="already_installed"
)

recommendations_df["property_id"] = (
    recommendations_df["property_id"].astype(int).astype(str)
)
properties_df["property_id"] = properties_df["property_id"].astype(int).astype(str)

recs_with_uprn = recommendations_df.merge(
    properties_df[["property_id", "uprn"]],
    on="property_id",
    how="left",
    suffixes=("", "_prop"),
)

recs_with_uprn = (
    recs_with_uprn.sort_values("sap_points", ascending=False)
    .groupby(["uprn", "measure_type"], as_index=False)
    .first()
)

recs_with_uprn["uprn"] = recs_with_uprn["uprn"].astype(str)

installed_measures_df = all_conflicts.merge(
    recs_with_uprn[
        [
            "uprn",
            "measure_type",
            "sap_points",
            "heat_demand",
            "kwh_savings",
            "co2_equivalent_savings",
            "energy_cost_savings",
        ]
    ],
    how="left",
    on=["uprn", "measure_type"],
)

installed_measures_df = installed_measures_df[
    installed_measures_df["already_installed"] == True
]

## --- Sense checking ----

FABRIC_MEASURES = {
    "external_wall_insulation",
    "internal_wall_insulation",
    "cavity_wall_insulation",
}


def add_mechanical_ventilation_for_fabric(installed_measures_df, recs_with_uprn):
    """
    If a property has fabric insulation installed, also mark
    mechanical ventilation as installed using recommendation metrics.
    """

    # Properties with fabric installed
    fabric_uprns = installed_measures_df[
        installed_measures_df["measure_type"].isin(FABRIC_MEASURES)
    ]["uprn"].unique()

    # Get MV recommendation metrics (pick max SAP per property as you decided)
    mv_recs = (
        recs_with_uprn[
            (recs_with_uprn["measure_type"] == "mechanical_ventilation")
            & (recs_with_uprn["uprn"].isin(fabric_uprns))
        ]
        .sort_values("sap_points", ascending=False)
        .drop_duplicates(subset=["uprn"])
    )

    mv_installed = mv_recs[
        [
            "uprn",
            "measure_type",
            "sap_points",
            "heat_demand",
            "kwh_savings",
            "co2_equivalent_savings",
            "energy_cost_savings",
        ]
    ].copy()

    mv_installed["already_installed"] = True

    return pd.concat([installed_measures_df, mv_installed], ignore_index=True)


# installed_measures_df = add_mechanical_ventilation_for_fabric(
#     installed_measures_df,
#     recs_with_uprn
# )

assert installed_measures_df[["uprn", "measure_type"]].duplicated().sum() == 0

for col in [
    "sap_points",
    "heat_demand",
    "kwh_savings",
    "co2_equivalent_savings",
    "energy_cost_savings",
]:
    print(
        f"n missings for {col}: {pd.isnull(installed_measures_df[col]).sum()}",
    )

# Do some calcs on SAP impact
sap_impact = installed_measures_df.groupby(["uprn"])["sap_points"].sum().reset_index()
properties_sap = properties_df[
    ["uprn", "current_sap_points", "current_epc_rating"]
].copy()
properties_sap["uprn"] = properties_sap["uprn"].astype(str)

old_sap_vs_new = properties_sap.merge(sap_impact, how="inner", on="uprn")
old_sap_vs_new["new_sap_points"] = (
    old_sap_vs_new["current_sap_points"] + old_sap_vs_new["sap_points"]
)
old_sap_vs_new["new_epc_rating"] = old_sap_vs_new["new_sap_points"].apply(
    lambda x: sap_to_epc(x)
)
# How many properties go from below C to above
old_sap_vs_new[old_sap_vs_new["current_sap_points"] < 69][
    "new_epc_rating"
].value_counts()
changed = old_sap_vs_new[
    (old_sap_vs_new["current_sap_points"] < 69)
    & (old_sap_vs_new["new_sap_points"] >= 69)
]

# What do I need to do:
# TODO: - need to get a view of "all" measures for the property, not just recommended. We can do this but just looking
#         at one scenario
# 1) I should store the current recommendations table, for the portfolio as a backup
# 2) I need a total of already installed SAP points for each property. This should probably be stored on the
# property_details_epc tabe
# 3) For anything already installed, I should mark already installed as True, and set the cost to zero
# 4) I need to update the plan cost to remove the cost of the installed measures

# TODO:
# 1) Need to push the already installed measures to the database
from sqlalchemy.orm import sessionmaker
from datetime import datetime

BATCH_SIZE = 5000
SOURCE = "peabody_import_2026_01"

Session = sessionmaker(bind=db_engine)


def bulk_insert_installed_measures(installed_measures_df):
    session = Session()

    records = []
    now = datetime.utcnow()

    for _, row in installed_measures_df.iterrows():
        records.append(
            {
                "uprn": int(row["uprn"]),
                "measure_type": row["measure_type"],
                "installed_at": now,
                "sap_points": (
                    float(row["sap_points"]) if pd.notna(row["sap_points"]) else None
                ),
                "carbon_savings": (
                    float(row["co2_equivalent_savings"])
                    if pd.notna(row["co2_equivalent_savings"])
                    else None
                ),
                "kwh_savings": (
                    float(row["kwh_savings"]) if pd.notna(row["kwh_savings"]) else None
                ),
                "bill_savings": (
                    float(row["energy_cost_savings"])
                    if pd.notna(row["energy_cost_savings"])
                    else None
                ),
                "heat_demand_savings": (
                    float(row["heat_demand"]) if pd.notna(row["heat_demand"]) else None
                ),
                "source": SOURCE,
                "is_active": True,
            }
        )

    try:
        for i in range(0, len(records), BATCH_SIZE):
            batch = records[i : i + BATCH_SIZE]
            session.bulk_insert_mappings(InstalledMeasure, batch)
            session.commit()
            print(f"✅ Inserted {i + len(batch)} / {len(records)}")

    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


# bulk_insert_installed_measures(installed_measures_df)

### Rebaselining
from typing import Dict
from sqlalchemy import func

from typing import Dict
from sqlalchemy import func, case

REBASING_EXCLUDED_MEASURES = {
    "mechanical_ventilation",
}


def get_installed_measure_adjustments_by_uprn_for_portfolio(
    session,
    portfolio_id: int,
) -> Dict[int, dict]:
    """
    Returns per-UPRN installed-measure adjustments for PROPERTY / EPC rebasing.

    IMPORTANT:
    - Mechanical ventilation is EXCLUDED from rebasing calculations
      (drag-along measure; should not alter baseline EPC/SAP).
    - All other installed measures are fully applied.

    Output shape:
    {
        uprn: {
            "sap_points": float,
            "co2": float,
            "energy_kwh": float,
            "energy_bill": float,
            "heat_demand": float,
        }
    }
    """

    # --------------------------------------------
    # Limit to UPRNs that belong to this portfolio
    # --------------------------------------------
    uprn_subquery = (
        session.query(PropertyModel.uprn)
        .filter(PropertyModel.portfolio_id == portfolio_id)
        .filter(PropertyModel.uprn.isnot(None))
        .subquery()
    )

    # --------------------------------------------
    # CASE helper: exclude ventilation from rebasing
    # --------------------------------------------
    def exclude_ventilation(column):
        return case(
            (
                InstalledMeasure.measure_type.notin_(REBASING_EXCLUDED_MEASURES),
                column,
            ),
            else_=0.0,
        )

    # --------------------------------------------
    # Aggregate installed-measure impacts per UPRN
    # --------------------------------------------
    rows = (
        session.query(
            InstalledMeasure.uprn.label("uprn"),
            func.coalesce(
                func.sum(exclude_ventilation(InstalledMeasure.sap_points)),
                0.0,
            ).label("sap_points"),
            func.coalesce(
                func.sum(exclude_ventilation(InstalledMeasure.carbon_savings)),
                0.0,
            ).label("co2"),
            func.coalesce(
                func.sum(exclude_ventilation(InstalledMeasure.kwh_savings)),
                0.0,
            ).label("energy_kwh"),
            func.coalesce(
                func.sum(exclude_ventilation(InstalledMeasure.bill_savings)),
                0.0,
            ).label("energy_bill"),
            func.coalesce(
                func.sum(exclude_ventilation(InstalledMeasure.heat_demand_savings)),
                0.0,
            ).label("heat_demand"),
        )
        .filter(InstalledMeasure.is_active.is_(True))
        .filter(InstalledMeasure.uprn.in_(uprn_subquery))
        .group_by(InstalledMeasure.uprn)
        .all()
    )

    # --------------------------------------------
    # Shape result for downstream consumers
    # --------------------------------------------
    return {
        row.uprn: {
            "sap_points": float(row.sap_points),
            "co2": float(row.co2),
            "energy_kwh": float(row.energy_kwh),
            "energy_bill": float(row.energy_bill),
            "heat_demand": float(row.heat_demand),
        }
        for row in rows
    }


def get_installed_measure_types_by_uprn(
    session,
    uprn: int,
) -> Set[str]:
    rows = (
        session.query(InstalledMeasure.measure_type)
        .filter(InstalledMeasure.uprn == uprn)
        .filter(InstalledMeasure.is_active.is_(True))
        .all()
    )

    # Convert enums → strings
    return {r[0].value if hasattr(r[0], "value") else r[0] for r in rows}


# ------------------------------------------------------------
# PROPERTY REBASING (READ-ONLY)
# ------------------------------------------------------------


def compute_property_sap_updates(
    properties: List[PropertyModel],
    sap_adjustments: Dict[int, float],  # keyed by uprn
) -> List[dict]:
    """
    Returns property SAP rebasing results.
    ONLY returns rows where installed measures exist.
    """

    updates = []

    for prop in properties:
        if prop.uprn is None:
            continue

        # 🚨 gatekeeper
        if prop.uprn not in sap_adjustments:
            continue

        if prop.original_sap_points is None:
            continue

        sap_delta = sap_adjustments[prop.uprn]
        new_sap = prop.original_sap_points + sap_delta

        updates.append(
            {
                "property_id": prop.id,
                "uprn": prop.uprn,
                "original_sap_points": prop.original_sap_points,
                "installed_sap_delta": sap_delta,
                "new_sap_points": new_sap,
                "is_adjusted": True,
            }
        )

    return updates


# ------------------------------------------------------------
# PLAN RECOMPUTATION HELPERS
# ------------------------------------------------------------


def get_effective_plan_recommendations(
    session, plan_id: int, excluded_measure_types: Set[str]
) -> List[Recommendation]:
    q = (
        session.query(Recommendation)
        .join(PlanRecommendations)
        .filter(PlanRecommendations.plan_id == plan_id)
        .filter(Recommendation.default.is_(True))
    )
    if excluded_measure_types:
        q = q.filter(~Recommendation.measure_type.in_(excluded_measure_types))

    return q.all()


def aggregate_plan_metrics(recommendations: list[Recommendation]):
    agg = {
        "sap_points": 0.0,
        "co2_savings": 0.0,
        "energy_bill_savings": 0.0,
        "energy_consumption_savings": 0.0,
        "valuation_increase": 0.0,
        "cost_of_works": 0.0,
        "contingency_cost": 0.0,
    }

    for r in recommendations:
        agg["sap_points"] += r.sap_points or 0.0
        agg["co2_savings"] += r.co2_equivalent_savings or 0.0
        agg["energy_bill_savings"] += r.energy_cost_savings or 0.0
        agg["energy_consumption_savings"] += r.kwh_savings or 0.0
        agg["valuation_increase"] += r.property_valuation_increase or 0.0

        base_cost = r.estimated_cost or 0.0
        agg["cost_of_works"] += base_cost
        agg["contingency_cost"] += calculate_contingency_for_recommendation(r)

    return agg


# ------------------------------------------------------------
# PLAN REBASING (READ-ONLY)
# ------------------------------------------------------------

# session,plans,properties_by_id,epcs_by_property_id, property_sap_updates = session, plans, properties_by_id, epcs,
# property_updates_by_id

from collections import defaultdict


def get_installed_measure_types_by_property_id_for_portfolio(
    session,
    portfolio_id: int,
) -> dict[int, set[str]]:
    """
    Returns:
      { property_id: {measure_type, ...} }

    Includes drag-along measures (e.g. ventilation).
    """

    rows = (
        session.query(
            PropertyModel.id.label("property_id"),
            InstalledMeasure.measure_type,
        )
        .join(
            InstalledMeasure,
            InstalledMeasure.uprn == PropertyModel.uprn,
        )
        .filter(PropertyModel.portfolio_id == portfolio_id)
        .filter(InstalledMeasure.is_active.is_(True))
        .all()
    )

    installed_by_property: dict[int, set[str]] = defaultdict(set)

    for property_id, measure_type in rows:
        mt = measure_type.value if hasattr(measure_type, "value") else measure_type
        installed_by_property[property_id].add(mt)

        # drag-along rules
        if mt in {
            "cavity_wall_insulation",
            "internal_wall_insulation",
            "external_wall_insulation",
        }:
            installed_by_property[property_id].add("mechanical_ventilation")

    return installed_by_property


def get_all_default_plan_recommendations(
    session,
    plan_ids: list[int],
) -> dict[int, list[Recommendation]]:
    """
    Returns {plan_id: [Recommendation, ...]} for ALL plans in one query.
    """

    rows = (
        session.query(
            PlanRecommendations.plan_id,
            Recommendation,
        )
        .join(
            Recommendation, Recommendation.id == PlanRecommendations.recommendation_id
        )
        .filter(PlanRecommendations.plan_id.in_(plan_ids))
        .filter(Recommendation.default.is_(True))
        .all()
    )

    by_plan: dict[int, list[Recommendation]] = {}

    for plan_id, rec in rows:
        by_plan.setdefault(plan_id, []).append(rec)

    return by_plan


def filter_remaining_recommendations(
    recommendations: list[Recommendation],
    installed_types: set[str],
) -> list[Recommendation]:
    """
    Removes recommendations whose measure_type is already installed.
    """
    if not installed_types:
        return recommendations

    return [
        r
        for r in recommendations
        if (
            (
                r.measure_type.value
                if hasattr(r.measure_type, "value")
                else r.measure_type
            )
            not in installed_types
        )
    ]


def compute_plan_updates(
    session,
    plans: List[PlanModel],
    properties_by_id: Dict[int, PropertyModel],
    epcs_by_property_id: Dict[int, PropertyDetailsEpcModel],
    installed_types_by_property_id,
    all_ventilation_measures,
) -> List[dict]:
    """
    Computes plan metrics after marking some recommendations as already installed.

    Rules:
    - Baseline post metrics remain unchanged
    - Savings + costs are recomputed excluding installed measures
    - ONLY mechanical ventilation alters post metrics
    """

    all_plan_recs = get_all_default_plan_recommendations(
        session,
        [p.id for p in plans],
    )

    updates = []
    property_to_installed_types = {}
    for plan in tqdm(plans, total=len(plans)):
        prop = properties_by_id.get(plan.property_id)
        epc = epcs_by_property_id.get(plan.property_id)

        if not prop or not epc:
            continue

        installed_types = installed_types_by_property_id.get(prop.id, set())

        if not installed_types:
            continue

        plan_recs = all_plan_recs.get(plan.id, [])

        remaining_recs = filter_remaining_recommendations(
            plan_recs,
            installed_types,
        )

        remaining = aggregate_plan_metrics(remaining_recs)

        # Detect ventilation removal
        ventilation_removed = "mechanical_ventilation" in installed_types

        # -------------------------------
        # Start from the previous plan
        # -------------------------------
        post_sap = plan.post_sap_points
        post_co2 = plan.post_co2_emissions
        post_bill = plan.post_energy_bill
        post_kwh = plan.post_energy_consumption

        # -------------------------------
        # Undo ventilation ONLY
        # -------------------------------
        ventilation_impact = all_ventilation_measures.get(prop.uprn, None)

        if ventilation_removed and ventilation_impact is not None:
            # ventilation impact = baseline - remaining

            post_sap -= ventilation_impact["sap_points"]

            post_co2 += ventilation_impact["co2"]  # We save more with ventilation

            post_bill += ventilation_impact["energy_bill"]
            post_kwh += ventilation_impact["energy_kwh"]

        #     # Skip if nothing changes at all
        # if (
        #     remaining["cost_of_works"] == baseline["cost_of_works"]
        #     and not ventilation_removed
        # ):
        #     continue

        updates.append(
            {
                "plan_id": plan.id,
                "property_id": plan.property_id,
                # SAP / EPC
                "post_sap_points": post_sap,
                "post_epc_rating": sap_to_epc(post_sap),
                # Carbon
                "co2_savings": remaining["co2_savings"],
                "post_co2_emissions": post_co2,
                # Energy bills
                "energy_bill_savings": remaining["energy_bill_savings"],
                "post_energy_bill": post_bill,
                # Energy consumption
                "energy_consumption_savings": remaining["energy_consumption_savings"],
                "post_energy_consumption": post_kwh,
                # Valuation (safe)
                "valuation_increase": remaining["valuation_increase"],
                "valuation_post_retrofit": (
                    prop.current_valuation + remaining["valuation_increase"]
                    if prop.current_valuation is not None
                    else None
                ),
                # Costs
                "cost_of_works": remaining["cost_of_works"],
                "contingency_cost": remaining["contingency_cost"],
            }
        )

        property_to_installed_types[prop.id] = installed_types

    return updates


def build_installed_recommendation_pairs(
    installed_types_by_property_id: dict[int, set[str]],
) -> list[tuple[int, str]]:
    """
    Returns:
      [(property_id, measure_type), ...]
    """
    pairs = []

    for property_id, measure_types in installed_types_by_property_id.items():
        for mt in measure_types:
            pairs.append((property_id, mt))

    return pairs


def calculate_contingency_for_recommendation(
    recommendation,
) -> float:
    """
    Recompute contingency for a recommendation using the same
    logic as the costing engine.

    Assumptions:
    - recommendation.estimated_cost is the 'total' cost
    - contingency is a percentage of total
    """

    if recommendation.estimated_cost is None:
        return 0.0

    # Normalise measure_type (Enum → str)
    measure_type = (
        recommendation.measure_type.value
        if hasattr(recommendation.measure_type, "value")
        else recommendation.measure_type
    )

    # Measure-specific contingency if defined, else global fallback
    contingency_rate = Costs.CONTINGENCIES.get(
        measure_type,
        Costs.CONTINGENCY,  # default (e.g. 10%)
    )

    return recommendation.estimated_cost * contingency_rate


def persist_property_sap_updates(
    property_updates_by_id: dict[int, dict],
):
    """
    Writes adjusted SAP values back to property table.
    Safe to re-run.
    """

    with db_session() as session:
        properties = (
            session.query(PropertyModel)
            .filter(PropertyModel.id.in_(property_updates_by_id.keys()))
            .all()
        )

        for prop in properties:
            update = property_updates_by_id[prop.id]

            prop.installed_measures_sap_point_adjustment = update["installed_sap_delta"]
            prop.is_sap_points_adjusted_for_installed_measures = update["is_adjusted"]
            prop.current_sap_points = update["new_sap_points"]
            prop.current_epc_rating = sap_to_epc(update["new_sap_points"])

        print(f"✅ Updated {len(properties)} properties")


def compute_epc_rebasing_updates(
    epcs: Dict[int, PropertyDetailsEpcModel],
    properties_by_id: Dict[int, PropertyModel],
    installed_adjustments_by_uprn: Dict[int, dict],
) -> Dict[int, dict]:
    """
    Computes EPC rebasing updates without mutating DB objects.
    Keyed by property_id.
    """

    updates: Dict[int, dict] = {}

    for property_id, epc in epcs.items():
        prop = properties_by_id.get(property_id)
        if not prop or prop.uprn is None:
            continue

        adj = installed_adjustments_by_uprn.get(prop.uprn)
        if not adj:
            continue

        # if (
        #     adj["sap_points"] == 0
        #     and adj["co2"] == 0
        #     and adj["energy_kwh"] == 0
        #     and adj["energy_bill"] == 0
        #     and adj["heat_demand"] == 0
        # ):
        #     continue

        updates[property_id] = {
            "property_id": property_id,
            # Originals (only set once)
            "original_co2_emissions": (
                epc.original_co2_emissions
                if epc.original_co2_emissions is not None
                else epc.co2_emissions
            ),
            "original_primary_energy_consumption": (
                epc.original_primary_energy_consumption
                if epc.original_primary_energy_consumption is not None
                else epc.primary_energy_consumption
            ),
            "original_current_energy_demand": (
                epc.original_current_energy_demand
                if epc.original_current_energy_demand is not None
                else epc.current_energy_demand
            ),
            "original_current_energy_demand_heating_hotwater": (
                epc.original_current_energy_demand_heating_hotwater
                if epc.original_current_energy_demand_heating_hotwater is not None
                else epc.current_energy_demand_heating_hotwater
            ),
            # Adjustments (always re-applied from originals)
            "installed_measures_co2_adjustment": adj["co2"],
            "installed_measures_energy_demand_adjustment": adj["energy_kwh"],
            "installed_measures_total_energy_bill_adjustment": adj["energy_bill"],
            "installed_measures_heat_demand_adjustment": adj["heat_demand"],
        }

    return updates


def persist_plan_updates(plan_updates: list[dict]):
    """
    Writes recalculated plan metrics.
    Safe to re-run.
    """

    with db_session() as session:
        plans = (
            session.query(PlanModel)
            .filter(PlanModel.id.in_([u["plan_id"] for u in plan_updates]))
            .all()
        )

        plans_by_id = {p.id: p for p in plans}

        for update in plan_updates:
            plan = plans_by_id.get(update["plan_id"])
            if not plan:
                continue

            # SAP / EPC
            plan.post_sap_points = update["post_sap_points"]
            plan.post_epc_rating = Epc(update["post_epc_rating"])

            # Carbon
            plan.co2_savings = update["co2_savings"]
            plan.post_co2_emissions = update["post_co2_emissions"]

            # Energy
            plan.energy_bill_savings = update["energy_bill_savings"]
            plan.post_energy_bill = update["post_energy_bill"]

            plan.energy_consumption_savings = update["energy_consumption_savings"]
            plan.post_energy_consumption = update["post_energy_consumption"]

            # Valuation
            plan.valuation_increase = update["valuation_increase"]
            plan.valuation_post_retrofit = update["valuation_post_retrofit"]

            # Costs
            plan.cost_of_works = update["cost_of_works"]
            plan.contingency_cost = update["contingency_cost"]

        print(f"✅ Updated {len(plans)} plans")


def persist_epc_rebasing_updates(
    epc_updates_by_property_id: Dict[int, dict],
):
    """
    Overwrites EPC metrics using installed-measure rebasing.
    Safe to re-run.
    """

    with db_session() as session:
        epcs = (
            session.query(PropertyDetailsEpcModel)
            .filter(
                PropertyDetailsEpcModel.property_id.in_(
                    epc_updates_by_property_id.keys()
                )
            )
            .all()
        )

        for epc in epcs:
            u = epc_updates_by_property_id[epc.property_id]

            # Store originals once
            epc.original_co2_emissions = u["original_co2_emissions"]
            epc.original_primary_energy_consumption = u[
                "original_primary_energy_consumption"
            ]
            epc.original_current_energy_demand = u["original_current_energy_demand"]
            epc.original_current_energy_demand_heating_hotwater = u[
                "original_current_energy_demand_heating_hotwater"
            ]

            # Apply rebased values
            epc.co2_emissions = (
                u["original_co2_emissions"] - u["installed_measures_co2_adjustment"]
            )

            epc.primary_energy_consumption = (
                u["original_primary_energy_consumption"]
                - u["installed_measures_heat_demand_adjustment"]
            )

            epc.current_energy_demand = (
                u["original_current_energy_demand"]
                - u["installed_measures_energy_demand_adjustment"]
            )

            # Flags + audit fields
            epc.installed_measures_co2_adjustment = u[
                "installed_measures_co2_adjustment"
            ]
            epc.installed_measures_energy_demand_adjustment = u[
                "installed_measures_energy_demand_adjustment"
            ]
            epc.installed_measures_total_energy_bill_adjustment = u[
                "installed_measures_total_energy_bill_adjustment"
            ]
            epc.installed_measures_heat_demand_adjustment = u[
                "installed_measures_heat_demand_adjustment"
            ]
            epc.is_epc_adjusted_for_installed_measures = True

        print(f"✅ Updated {len(epcs)} EPC records")


# For setting the original SAP, carbon, etc to the current values
def initialise_original_property_and_epc_values(portfolio_id: int):
    """
    Initialise original_* columns for SAP + EPC.
    Safe to re-run. Only fills NULL originals.
    """

    with db_session() as session:
        # -------------------------
        # PROPERTY (SAP)
        # -------------------------
        properties = (
            session.query(PropertyModel)
            .filter(PropertyModel.portfolio_id == portfolio_id)
            .filter(PropertyModel.original_sap_points.is_(None))
            .all()
        )

        for prop in properties:
            prop.original_sap_points = prop.current_sap_points

        print(f"✅ Initialised original_sap_points for {len(properties)} properties")

        # -------------------------
        # EPC (energy / carbon)
        # -------------------------
        epcs = (
            session.query(PropertyDetailsEpcModel)
            .filter(PropertyDetailsEpcModel.portfolio_id == portfolio_id)
            .all()
        )

        epc_updates = 0

        for epc in epcs:
            updated = False

            if epc.original_co2_emissions is None:
                epc.original_co2_emissions = epc.co2_emissions
                updated = True

            if epc.original_primary_energy_consumption is None:
                epc.original_primary_energy_consumption = epc.primary_energy_consumption
                updated = True

            if epc.original_current_energy_demand is None:
                epc.original_current_energy_demand = epc.current_energy_demand
                updated = True

            if epc.original_current_energy_demand_heating_hotwater is None:
                epc.original_current_energy_demand_heating_hotwater = (
                    epc.current_energy_demand_heating_hotwater
                )
                updated = True

            if updated:
                epc_updates += 1

        print(f"✅ Initialised EPC originals for {epc_updates} EPC records")

        session.commit()


from typing import Set, Dict
from sqlalchemy import distinct

from typing import Dict
from sqlalchemy import func


def get_installed_ventilation_adjustments_by_uprn_for_portfolio(
    session,
    portfolio_id: int,
) -> Dict[int, dict]:
    """
    Returns per-UPRN aggregated impact metrics for
    already-installed MECHANICAL VENTILATION.

    {
        uprn: {
            sap_points: float,
            co2: float,
            energy_kwh: float,
            energy_bill: float,
            heat_demand: float,
        }
    }
    """

    # Only consider UPRNs that belong to this portfolio
    uprn_subquery = (
        session.query(PropertyModel.uprn)
        .filter(PropertyModel.portfolio_id == portfolio_id)
        .filter(PropertyModel.uprn.isnot(None))
        .subquery()
    )

    rows = (
        session.query(
            InstalledMeasure.uprn.label("uprn"),
            func.coalesce(func.sum(InstalledMeasure.sap_points), 0.0).label(
                "sap_points"
            ),
            func.coalesce(func.sum(InstalledMeasure.carbon_savings), 0.0).label("co2"),
            func.coalesce(func.sum(InstalledMeasure.kwh_savings), 0.0).label(
                "energy_kwh"
            ),
            func.coalesce(func.sum(InstalledMeasure.bill_savings), 0.0).label(
                "energy_bill"
            ),
            func.coalesce(func.sum(InstalledMeasure.heat_demand_savings), 0.0).label(
                "heat_demand"
            ),
        )
        .filter(InstalledMeasure.is_active.is_(True))
        .filter(InstalledMeasure.measure_type == "mechanical_ventilation")
        .filter(InstalledMeasure.uprn.in_(uprn_subquery))
        .group_by(InstalledMeasure.uprn)
        .all()
    )

    return {
        row.uprn: {
            "sap_points": float(row.sap_points),
            "co2": float(row.co2),
            "energy_kwh": float(row.energy_kwh),
            "energy_bill": float(row.energy_bill),
            "heat_demand": float(row.heat_demand),
        }
        for row in rows
    }


from sqlalchemy import update, tuple_


def mark_recommendations_as_installed(
    session,
    property_measure_pairs: list[tuple[int, str]],
    dry_run: bool = True,
):
    if not property_measure_pairs:
        print("No recommendations to update")
        return

    print(f"{len(property_measure_pairs)} recommendation matches found")

    if dry_run:
        print("DRY RUN — no database changes")
        return

    stmt = (
        update(Recommendation)
        .where(
            tuple_(Recommendation.property_id, Recommendation.measure_type).in_(
                property_measure_pairs
            )
        )
        .values(already_installed=True)
    )

    result = session.execute(stmt)
    session.commit()

    print(f"✅ Updated {result.rowcount} recommendations")


# ------------------------------------------------------------
# EXECUTION (DRY RUN)
# ------------------------------------------------------------

PORTFOLIO_ID = 431
# TODO - run the original sap points update on the peabody portfolio

# Initialising
# initialise_original_property_and_epc_values(PORTFOLIO_ID)


with db_read_session() as session:
    properties = (
        session.query(PropertyModel)
        .filter(PropertyModel.portfolio_id == PORTFOLIO_ID)
        .all()
    )

    all_ventilation_measures = (
        get_installed_ventilation_adjustments_by_uprn_for_portfolio(
            session, PORTFOLIO_ID
        )
    )
    installed_types_by_property_id = (
        get_installed_measure_types_by_property_id_for_portfolio(session, PORTFOLIO_ID)
    )

    plans = (
        session.query(PlanModel).filter(PlanModel.portfolio_id == PORTFOLIO_ID).all()
    )

    epcs = {
        e.property_id: e
        for e in (
            session.query(PropertyDetailsEpcModel)
            .join(PropertyModel)
            .filter(PropertyModel.portfolio_id == PORTFOLIO_ID)
            .all()
        )
    }

    installed_adjustments = get_installed_measure_adjustments_by_uprn_for_portfolio(
        session,
        PORTFOLIO_ID,
    )

    property_updates = compute_property_sap_updates(
        properties, {uprn: v["sap_points"] for uprn, v in installed_adjustments.items()}
    )

    properties_by_id = {p.id: p for p in properties}
    property_updates_by_id = {u["property_id"]: u for u in property_updates}

    epc_updates = compute_epc_rebasing_updates(
        epcs,
        properties_by_id,
        installed_adjustments,
    )

    plan_updates = compute_plan_updates(
        session,
        plans,
        properties_by_id,
        epcs,
        installed_types_by_property_id,
        all_ventilation_measures,
    )

    # Used to mark recommendations
    pairs = build_installed_recommendation_pairs(installed_types_by_property_id)

from copy import deepcopy

plan_updates_comparison = deepcopy(plan_updates)
plans_by_planid = {p.id: p for p in plans}
for u in plan_updates_comparison:
    before = plans_by_planid.get(u["plan_id"])
    if not before:
        continue

    u.update(
        {
            # SAP
            "before_sap_points": before.post_sap_points,
            "after_sap_points": u["post_sap_points"],
            # Carbon
            "before_post_co2_emissions": before.post_co2_emissions,
            "after_post_co2_emissions": u["post_co2_emissions"],
            # Costs
            "before_cost_of_works": before.cost_of_works,
            "after_cost_of_works": u["cost_of_works"],
            "before_contingency_cost": before.contingency_cost,
            "after_contingency_cost": u["contingency_cost"],
        }
    )

plan_updates_df = pd.DataFrame(plan_updates_comparison)

plan_updates_df["delta_sap_points"] = (
    plan_updates_df["after_sap_points"] - plan_updates_df["before_sap_points"]
)
plan_updates_df["delta_carbon"] = (
    plan_updates_df["after_post_co2_emissions"]
    - plan_updates_df["before_post_co2_emissions"]
)
plan_updates_df["delta_cost_of_works"] = (
    plan_updates_df["after_cost_of_works"] - plan_updates_df["before_cost_of_works"]
)
plan_updates_df["delta_contingency_cost"] = (
    plan_updates_df["after_contingency_cost"]
    - plan_updates_df["before_contingency_cost"]
)

# High-level sanity checks
summary = plan_updates_df[
    [
        "delta_sap_points",
        "delta_carbon",
        "delta_cost_of_works",
        "delta_contingency_cost",
    ]
].sum()

print(summary)

# Grab some random samples
example = plan_updates_df[plan_updates_df["delta_cost_of_works"] < -1000].sample(1)
# example = plan_updates_df[plan_updates_df["delta_sap_points"] == 0].sample(1)
example = plan_updates_df[plan_updates_df["property_id"] == 434936].squeeze()

print(example["property_id"])
# Go the the db and get the UPRN
uprn_example = 202149883
installed_adjustments[uprn_example]

[x for x in plan_updates if x["property_id"] == example["property_id"].values[0]]

installed_measures_example = {}

example.squeeze()
# When ready to run!
# persist_property_sap_updates(property_updates_by_id)
# persist_plan_updates(plan_updates)
# persist_epc_rebasing_updates(epc_updates)
# BATCH_SIZE = 1000
#
# with db_session() as session:
#     for i in range(0, len(pairs), BATCH_SIZE):
#         batch = pairs[i:i + BATCH_SIZE]
#
#         mark_recommendations_as_installed(
#             session,
#             batch,
#             dry_run=False,
#         )
#
#         session.commit()

# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/435084/
# Current EPC rating should go to 68.6 - no it shouldn't! less

# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/434930/
# Should now be a C72,
# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/434930
# Carbon should be 2.02, energy_kwh should be, 12311.5

# We need a follow-up query which switches off ventilation if ewi, iwi or cwi are already installed
# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/435154/plans/1024673
# Should go to C73
# This is a good one to test also, marking the recommendation as non-default

# Good example to check:
# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/434936/plans/1024455
# Should go down by these:
# {'sap_points': 11.299999, 'co2': 1.85, 'energy_kwh': 7882.1997, 'energy_bill': 549.89935, 'heat_demand': 77.7}
# Before SAP: 55
# Carbon 7.56
# kwh: 28207

# Good example to check:
# https://assessment-model-git-main-hestiahomes.vercel.app/portfolio/430/building-passport/434444/plans/1024063
# SHould change by these
# {'sap_points': 10.3, 'co2': 2.54, 'energy_kwh': 3713.5, 'energy_bill': 1028.2682, 'heat_demand': 151.61}
# Current: SAP 54
# Carbon: 4.45
# kwh: 10307

# There's one final thing to do - we had an error in post carbon so we need to increase it by the appliances
# amount for all units
from backend.ml_models.AnnualBillSavings import AnnualBillSavings


# Need to add this on to the plan for each property
def calculate_appliance_carbon_tonnes(total_floor_area: float) -> float:
    """
    Returns appliance carbon emissions in tonnes CO2.
    """
    appliance_energy_kwh = AnnualBillSavings.estimate_appliances_energy_use(
        total_floor_area=total_floor_area
    )

    # kgCO2 → tonnes CO2
    appliance_carbon_tonnes = (appliance_energy_kwh * 0.232) / 1000
    return appliance_carbon_tonnes


from sqlalchemy.orm import joinedload
from tqdm import tqdm

from tqdm import tqdm


def apply_appliance_carbon_to_plans(
    session,
    portfolio_id: int,
    dry_run: bool = True,
):
    """
    Adds appliance-related carbon emissions to plan.post_co2_emissions
    using EPC total_floor_area.
    """

    # --------------------------------------------
    # Load EPCs (floor area source of truth)
    # --------------------------------------------
    epcs = (
        session.query(PropertyDetailsEpcModel)
        .filter(PropertyDetailsEpcModel.portfolio_id == portfolio_id)
        .filter(PropertyDetailsEpcModel.total_floor_area.isnot(None))
        .all()
    )

    epc_by_property_id = {e.property_id: e for e in epcs}

    # --------------------------------------------
    # Load plans with post carbon
    # --------------------------------------------
    plans = (
        session.query(PlanModel)
        .filter(PlanModel.portfolio_id == portfolio_id)
        .filter(PlanModel.post_co2_emissions.isnot(None))
        .all()
    )

    updates = []
    total_delta = 0.0

    for plan in tqdm(plans, total=len(plans)):
        epc = epc_by_property_id.get(plan.property_id)
        if not epc:
            continue

        floor_area = epc.total_floor_area
        if not floor_area or floor_area <= 0:
            continue

        delta = float(calculate_appliance_carbon_tonnes(floor_area))

        if delta == 0:
            continue

        updates.append((plan, delta))
        total_delta += delta

    # --------------------------------------------
    # Reporting
    # --------------------------------------------
    print(f"Plans affected: {len(updates)}")
    print(f"Total appliance carbon added (tCO2): {total_delta:.4f}")

    if dry_run:
        print("🟡 DRY RUN — no updates applied")
        return

    # --------------------------------------------
    # Apply updates
    # --------------------------------------------
    for plan, delta in updates:
        plan.post_co2_emissions += delta

    session.commit()
    print("✅ Appliance carbon successfully applied")


# with db_session() as session:
#     apply_appliance_carbon_to_plans(
#         session,
#         portfolio_id=PORTFOLIO_ID,
#         dry_run=False,
#     )

# Get all uprns for entries in already installed, from the database
with db_read_session() as session:
    db_uprns = {str(r[0]) for r in (session.query(InstalledMeasure.uprn).all())}

# What is the overlap of these properties and the properties in portfolo 430
sal_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260107 "
    "corrected batch 6 sal.xlsx",
    sheet_name="batch 1",
)

len(sal_data[sal_data["epc_os_uprn"].astype(str).isin(db_uprns)]["epc_os_uprn"])

# len([uprn for uprn, v in installed_adjustments.items() if str(uprn) in sal_data["epc_os_uprn"].astype(str).tolist()])
