Model/backend/app/db/functions/energy_assessment_functions.py

from backend.app.db.models.energy_assessments import EnergyAssessment
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from typing import Optional
from sqlalchemy import desc


def bulk_insert_energy_assessments(session: Session, data_list):
    """
    This function inserts or updates multiple energy assessment records into the database.

    :param session: The SQLAlchemy session.
    :param data_list: A list of dictionaries containing energy assessment data.
    """
    try:
        for data in data_list:
            uprn = data.get('uprn')
            inspection_date = data.get('inspection_date')

            # Check if a record with the same uprn and inspection_date exists
            existing_record = session.query(EnergyAssessment).filter_by(
                uprn=uprn,
                inspection_date=inspection_date
            ).first()

            if existing_record:
                # Update the existing record with new data
                for key, value in data.items():
                    setattr(existing_record, key, value)
                session.add(existing_record)
            else:
                # Insert a new record
                new_assessment = EnergyAssessment(**data)
                session.add(new_assessment)

        # Commit the transaction
        session.commit()
        print("All records inserted or updated successfully.")

    except IntegrityError as e:
        # Rollback the session in case of error
        session.rollback()
        print(f"Error occurred: {e}")


def get_latest_assessment_by_uprn(session: Session, uprn: int) -> Optional[EnergyAssessment]:
    """
    Retrieve the latest energy assessment for a given UPRN based on the inspection date.

    :param session: The database session
    :param uprn: The unique property reference number
    :return: The latest EnergyAssessment object or None if not found
    """
    try:
        # Query the EnergyAssessment model, filter by uprn, order by inspection_date in descending order
        latest_assessment = session.query(EnergyAssessment).filter_by(uprn=uprn).order_by(
            desc(EnergyAssessment.inspection_date)).first()

        return latest_assessment.to_dict() if latest_assessment else EnergyAssessment.empty_response()
    except Exception as e:
        print(f"An error occurred: {e}")
        return None