import pandas as pd
from sqlalchemy.orm import Session
from sqlalchemy import text, select
from backend.app.db.connection import db_read_session
from backend.app.db.models.portfolio import PropertyModel, PropertyDetailsEpcModel
from backend.app.db.models.recommendations import PlanModel

PORTFOLIO_ID = 435

with db_read_session() as session:
    # Get all properties from PropertyDetailsEpcModel, where estimated is True, for portfolio 419
    estimated_epcs = (
        session.query(PropertyDetailsEpcModel)
        .filter(
            # PropertyDetailsEpcModel.estimated == True,
            PropertyDetailsEpcModel.property_id.in_(
                session.query(PropertyModel.id).filter(
                    PropertyModel.portfolio_id == PORTFOLIO_ID
                )
            )
        )
        .all()
    )

    # Get the ids
    estimated_epc_ids = [epc.property_id for epc in estimated_epcs]

# I want to get the UPRNS for these properties, from the property model
with db_read_session() as session:
    estimated_uprns = (
        session.query(PropertyModel.uprn)
        .filter(
            PropertyModel.id.in_(
                session.query(PropertyDetailsEpcModel.property_id).filter(
                    PropertyDetailsEpcModel.id.in_(estimated_epc_ids)
                )
            )
        )
        .all()
    )

    estimated_uprns_list = [uprn for (uprn,) in estimated_uprns]

# Go the the SAL
sal_1 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/20251213 Model "
    "data.xlsx",
    sheet_name="Standardised Asset List",
)
sal_2 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/20260105 - additional "
    "UPRNS.xlsx",
    sheet_name="Standardised Asset List",
)

sal = pd.concat([sal_1, sal_2])
sal = sal.drop_duplicates(subset=["epc_os_uprn"])

estimated_to_refresh = sal[sal["epc_os_uprn"].isin(estimated_uprns_list)].copy()

SCENARIOS = [
    # 871,  # EPC C - fabric first, no solid floor, ashp 3.0
    # 863,  # EPC B, No EWI/IWI, No Solid Floor, ASHP 3.0 COP
    # 862,  # EPC B - No solid floor, ASHP COP 3.0
    # 861,  # EPC C, No EWI/IWI, No Solid Floor, ASHP 3.0 COP
    # 859,  # EPC C - no solid floor, ashp 3.0
    # 885,  # EPC B - fabric first, no solid floor, ashp 3.0
    908,
    909,
    910,
]

# Get all plans, associated to these properties - the property IDs are in estimated_epc_ids
with db_read_session() as session:
    result = session.execute(
        select(PlanModel.id, PlanModel.property_id).where(
            PlanModel.property_id.in_(estimated_epc_ids)
        )
    )
    plans = [
        {
            "plan_id": row.id,
            "property_id": row.property_id,
        }
        for row in result
    ]

df = pd.DataFrame(plans)
df = df.sort_values("property_id", ascending=True)

agg = df.groupby("property_id").size().reset_index(name="n_plans")
agg = agg.sort_values("n_plans", ascending=True)

agg[agg["n_plans"] == 3]
agg[agg["n_plans"] == 2].shape

agg[agg["n_plans"] != 3]
assert all(agg["n_plans"] == 3)


def delete_plan_batch(session: Session, plan_ids: list[int]):
    if not plan_ids:
        return

    session.execute(text("SET LOCAL lock_timeout = '5s'"))

    params = {"plan_ids": plan_ids}

    # ----------------------------
    # recommendation_materials
    # ----------------------------
    session.execute(
        text(
            """
            DELETE FROM recommendation_materials rm
            USING plan_recommendations pr
            WHERE rm.recommendation_id = pr.recommendation_id
              AND pr.plan_id = ANY(:plan_ids)
        """
        ),
        params,
    )

    # ----------------------------
    # plan_recommendations
    # ----------------------------
    session.execute(
        text(
            """
            DELETE FROM plan_recommendations
            WHERE plan_id = ANY(:plan_ids)
        """
        ),
        params,
    )

    # ----------------------------
    # recommendations (only those used by these plans)
    # ----------------------------
    session.execute(
        text(
            """
            DELETE FROM recommendation r
            WHERE r.id IN (
                SELECT DISTINCT recommendation_id
                FROM plan_recommendations
                WHERE plan_id = ANY(:plan_ids)
            )
        """
        ),
        params,
    )

    # ----------------------------
    # plans LAST
    # ----------------------------
    session.execute(
        text(
            """
            DELETE FROM plan
            WHERE id = ANY(:plan_ids)
        """
        ),
        params,
    )


# Store the SAL
filename = (
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/20260101 "
    "sal.xlsx"
)

with pd.ExcelWriter(filename) as writer:
    sal.to_excel(writer, sheet_name="Standardised Asset List", index=False)
    # Top 1000 for testing
    sal.iloc[0:1000, :].to_excel(writer, sheet_name="batch 1", index=False)
    # Batch 2 is the next 20,000
    sal.iloc[1000:21000, :].to_excel(writer, sheet_name="batch 2", index=False)
    # Batch 3 is the next 20,000
    sal.iloc[21000:41000, :].to_excel(writer, sheet_name="batch 3", index=False)

    sal.iloc[41000:61000, :].to_excel(writer, sheet_name="batch 4", index=False)
    sal.iloc[61000:81000, :].to_excel(writer, sheet_name="batch 5", index=False)
    sal.iloc[81000:, :].to_excel(writer, sheet_name="batch 6", index=False)

# TODO - mistake was made when creating the final SAL
b1 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260101 "
    "sal.xlsx",
    sheet_name="batch 1",
)
b2 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260101 "
    "sal.xlsx",
    sheet_name="batch 2",
)
b3 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260101 "
    "sal.xlsx",
    sheet_name="batch 3",
)
b4 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260101 "
    "sal.xlsx",
    sheet_name="batch 4",
)
b5 = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260101 "
    "sal.xlsx",
    sheet_name="batch 5",
)
# Batch 6 should be the remaining
total = pd.concat([b1, b2, b3, b4, b5])
remaining = sal[~sal["epc_os_uprn"].isin(total["epc_os_uprn"].values)]
# Create new output
filename = (
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/"
    "20260107 corrected batch 6 sal.xlsx"
)

with pd.ExcelWriter(filename) as writer:
    sal.to_excel(writer, sheet_name="Standardised Asset List", index=False)
    # Top 1000 for testing
    b1.to_excel(writer, sheet_name="batch 1", index=False)
    # Batch 2 is the next 20,000
    b2.to_excel(writer, sheet_name="batch 2", index=False)
    # Batch 3 is the next 20,000
    b3.to_excel(writer, sheet_name="batch 3", index=False)

    b4.to_excel(writer, sheet_name="batch 4", index=False)
    b5.to_excel(writer, sheet_name="batch 5", index=False)
    remaining.to_excel(writer, sheet_name="batch 6", index=False)

all_together = pd.concat([b1, b2, b3, b4, b5, remaining])
