# ------ Pull in the full SAL sample ------
import pandas as pd

full_sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final "
    "SAL/Depracated/20260107 corrected batch 6 sal.xlsx",
    sheet_name="Standardised Asset List",
)

# ------Pull in the reduced sample ------
# This has a slightly incorrect mix of ownership types. Some properties will need to be dropped and others, added
reduced_sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260112 - "
    "ownership filtered sal.xlsx",
    sheet_name="Standardised Asset List",
)

# ------ Pull in the confirmed ownership column from Peabody ------
new_asset_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/2025_11_11 "
    "- Peabody "
    "- Data Extracts for Domna v2.xlsx",
    sheet_name="Properties",
)

correct_sample = new_asset_data[
    ~new_asset_data["AH Tenure"].isin(
        [
            "Commercial",
            "Freeholder",
            "HOMEBUY / EQUITY LOAN",
            "Leaseholder",
            "Outright Sale",
            "SHARED EQUITY",
            "Shared Ownership",
        ]
    )
].copy()

# ------- Stuff to add -------
# These are properties that need to be added to the reduced sample, from the SAL
stuff_to_add = correct_sample[
    ~correct_sample["UPRN"].isin(reduced_sal["landlord_property_id"].values)
]["UPRN"].values

sal_to_add = full_sal[full_sal["domna_property_id"].isin(stuff_to_add)].copy()

# ------- Stuff to remove -------
stuff_to_remove = reduced_sal[
    ~reduced_sal["landlord_property_id"].isin(correct_sample["UPRN"].values)
]["landlord_property_id"].values

to_delete = reduced_sal[
    reduced_sal["landlord_property_id"].isin(stuff_to_remove)
].copy()

# ------- Create the correctly formatted SAL, with an individual batch for properties we need to add -------

# This is what is correct, from the reduced sample, after removing the incorrect ownership types
reduced_sal_final = reduced_sal[
    ~reduced_sal["landlord_property_id"].isin(stuff_to_remove)
].copy()

sal_to_add["landlord_property_id"] = sal_to_add["domna_property_id"].copy()

full_sal = pd.concat(
    [reduced_sal_final, sal_to_add],
)

# filename = (
#     "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/20260113 - "
#     "final asset list.xlsx"
# )
# with pd.ExcelWriter(filename) as writer:
#     full_sal.to_excel(writer, sheet_name="Standardised Asset List", index=False)
#     # Store the three sections
#     reduced_sal_final[0:25000].to_excel(writer, sheet_name="Batch 1 - was correct", index=False)
#     reduced_sal_final[25000:].to_excel(writer, sheet_name="Batch 2 - was correct", index=False)
#     sal_to_add.to_excel(writer, sheet_name="Batch 3 - needs adding", index=False)

# We now prepare the process of getting the associated
# We have the properties we need to delete. We can get their associated plans for all scenario IDs
scenario_ids = [908, 909, 910]

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

uprns_to_be_deleted = to_delete["epc_os_uprn"].values.tolist()

# PORTFOLIO_ID = 435

# SCENARIO_ID_WITH_PLANS_TO_DELETE = 910


# Get the property IDs for these UPRNs
# def get_property_ids_for_uprns(session: Session, uprns: list[int], portfolio_id) -> list[int]:
#     return [
#         property_id
#         for (property_id,) in
#         session.query(PropertyModel.id)
#         .filter(
#             PropertyModel.uprn.in_(uprns),
#             PropertyModel.portfolio_id == portfolio_id
#         )
#         .all()
#     ]
#
#
# with db_read_session() as session:
#     property_ids_to_delete = get_property_ids_for_uprns(
#         session, uprns_to_be_deleted, portfolio_id=PORTFOLIO_ID
#     )
#
#
# def count_plans_for_scenario(session: Session, scenario_id: int, portfolio_id, property_ids) -> int:
#     return session.execute(
#         select(func.count())
#         .select_from(Plan)
#         .where(
#             Plan.scenario_id == scenario_id,
#             Plan.portfolio_id == portfolio_id,
#             Plan.property_id.in_(property_ids)
#         )
#     ).scalar_one()
#
#
# with db_session() as session:
#     n_plans = count_plans_for_scenario(
#         session,
#         scenario_id=SCENARIO_ID_WITH_PLANS_TO_DELETE,
#         portfolio_id=PORTFOLIO_ID,
#         property_ids=property_ids_to_delete
#     )
#
#
# def get_plan_ids_for_scenario(
#     session: Session, scenario_id: int, portfolio_id, property_ids
# ) -> list[int]:
#     result = session.execute(
#         select(Plan.id, Plan.property_id)
#         .where(
#             Plan.scenario_id == scenario_id,
#             Plan.portfolio_id == portfolio_id,
#             Plan.property_id.in_(property_ids)
#         )
#     )
#     return [{"plan_id": row.id, "property_id": row.property_id} for row in result]
#
#
# with db_session() as session:
#     plan_ids_to_property = get_plan_ids_for_scenario(
#         session,
#         scenario_id=SCENARIO_ID_WITH_PLANS_TO_DELETE,
#         portfolio_id=PORTFOLIO_ID,
#         property_ids=property_ids_to_delete
#     )
#
# df = pd.DataFrame(plan_ids_to_property)
# df[df["property_id"].duplicated()].shape
#
# plan_ids = [row["plan_id"] for row in plan_ids_to_property]
#
#
# def chunked(iterable, size):
#     for i in range(0, len(iterable), size):
#         yield iterable[i:i + size]
#
#
# from sqlalchemy import text
# from sqlalchemy.orm import Session
#
#
# 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,
#     )
#
#
# batch_size = 25
# total = (len(plan_ids) + batch_size - 1) // batch_size
#
# for i, batch in enumerate(chunked(plan_ids, batch_size), start=1):
#     print(f"Deleting plan batch {i}/{total} ({len(batch)} plans)")
#
#     with db_session() as session:
#         delete_plan_batch(session, batch)
#
#     print(f"Batch {i} committed")
#
# # Now, we delete the associated properties in batch and associated objects. It should
# # largely be property, property details
# property_ids_to_delete
#
# from sqlalchemy import text
# from sqlalchemy.orm import Session
#
#
# def move_properties_between_portfolios(
#     session: Session,
#     property_ids: list[int],
#     from_portfolio_id: int,
#     to_portfolio_id: int,
# ):
#     if not property_ids:
#         return 0
#
#     result = session.execute(
#         text("""
#             UPDATE property
#             SET portfolio_id = :to_portfolio_id
#             WHERE portfolio_id = :from_portfolio_id
#               AND id = ANY(:property_ids)
#         """),
#         {
#             "property_ids": property_ids,
#             "from_portfolio_id": from_portfolio_id,
#             "to_portfolio_id": to_portfolio_id,
#         },
#     )
#
#     return result.rowcount
#
#
# # Moved?
# # 573476, 586011
#
# property_ids_to_delete2 = [x for x in property_ids_to_delete if x not in [573476, 586011]]
#
# with db_session() as session:
#     n_moved = move_properties_between_portfolios(
#         session,
#         property_ids=property_ids_to_delete2,
#         from_portfolio_id=PORTFOLIO_ID,
#         to_portfolio_id=32,  # Archive portfolio
#     )
