"""
For the Peabody project, there were a number of subtasks that failed due to issues, with the most
prominent being errors with the property address and ID data.

This script will fetch those failed subtasks, get the associated properties and delete the properties
from the database so that the subtasks can be restarted cleanly.

Additionally, we wil find the problematic records and remove them

Given we ran an EPC C scenario, we should check how many properties, below EPC C we have, that have no plan
or recommendations in case something went wrong
"""

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


def get_uprns_for_portfolio(session: Session, portfolio_id: int) -> list[int]:
    return [
        uprn
        for (uprn,) in session.query(PropertyModel.uprn)
        .filter(PropertyModel.portfolio_id == portfolio_id)
        .all()
        if uprn is not None
    ]


with db_session() as session:
    completed_uprns = get_uprns_for_portfolio(session, 419)

# We now find the portfolio of the SAL, which we did not set off
sal = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/20251213 Model "
    "data.xlsx",
    sheet_name="Standardised Asset List",
)

missed_properties = sal[~sal["epc_os_uprn"].isin(completed_uprns)]

# Store
missed_properties.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/"
    "d_failed_properties_to_restart_20260102.xlsx",
    sheet_name="Standardised Asset List",
    index=False,
)

# Fixing an error - triggered jobs without removing EWI/IWI so need to delete all plans associated to these scenarios:
scenario_id = None

from sqlalchemy import select, func
from sqlalchemy.orm import Session
from backend.app.db.models.recommendations import PlanModel


def count_plans_for_scenario(session: Session, scenario_id: int) -> int:
    return session.execute(
        select(func.count())
        .select_from(PlanModel)
        .where(PlanModel.scenario_id == scenario_id)
    ).scalar_one()


with db_session() as session:
    n_plans = count_plans_for_scenario(session, scenario_id)


def get_plan_ids_for_scenario(session: Session, scenario_id: int) -> list[int]:
    result = session.execute(
        select(PlanModel.id).where(PlanModel.scenario_id == scenario_id)
    )
    return [row.id for row in result]


with db_session() as session:
    plan_ids = get_plan_ids_for_scenario(session, scenario_id)

from sqlalchemy import text
from sqlalchemy.orm import Session


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")
