survey-extraction/etl/surveyedData/surveryedData.py

from etl.pdfReader.pdfReaderToText import pdfReaderToText
from etl.pdfReader.reportType import ReportType
import math
from etl.load.preSiteNoteTypes import (
    AssessorInfo, CompanyInfo, 
    PreSiteNotesSummaryInfo,
    PreSiteNote,
    PropertyDescription, Dimension, HeatingType, Heating, HeatingSystemControls,
    OtherDetails, WindTurbine, PhotovoltaicPanel, FlueGasHeatRecoverySystem, ShowerAndBaths,
    SolarWaterHeating, HotWaterCylinder, WaterHeating, Lighting, VentilationAndCooling,
    Door, Walls, Roofs, Floors, PropertyDetail, Windows
)
from etl.load.topLevel import(
    Buildings, Documents
)
import uuid

class surveyedDataProcessor():
    def __init__(self, address, files):
        self.address = address
        self.files = files
        self.pre_site_note = None
        self.csr = None
        self.identify_files()
        self.hubspot_deal_id = None


    def identify_files(self):
        for file in self.files:
            pdf = pdfReaderToText(file)
            if pdf:
                if pdf.type == ReportType.QUIDOS_PRESITE_NOTE:
                    self.pre_site_note = pdf.get_reader()
                    self.address = self.pre_site_note.survey_information.address
                elif pdf.type == ReportType.CHARTED_SURVEYOR_REPORT:
                    self.csr = pdf.get_reader()

    def load_pre_site_notes_summary_table(self, db_session):
        summary_data = self.pre_site_note.survey_information.model_dump()
        return self.upsert_record(
            db_session=db_session,
            model_class=PreSiteNotesSummaryInfo,
            data_dict=summary_data,
            lookup_field="reference_number"
        )
    
    def create_building_table(self, db_session):
        return self.upsert_record(
            db_session=db_session,
            model_class=Buildings,
            data_dict={
                "address":"foo",
                "potcode": "foobar",
                "UPRN": self.pre_site_note.survey_information.uprn,
                "landlord_id": "landlord_id",
                "domna_id": "landlord_id",
            },
            lookup_field="UPRN",
        )
    
    def get_attribute_and_load(self, obj, attr_string, pydanticModel, db_session):
        found = getattr(obj, attr_string, None)
        if found:
            print(f"Uploading to data base {found}")
            print(f"Uploaded to database with this dict {found.model_dump()}")
            if found.model_dump():
                db = self.upsert_record(
                    db_session=db_session,
                    model_class=pydanticModel,
                    data_dict=found.model_dump(),
                    lookup_field=None
                )
                return db
        return None
    
    def load_property_description(self, db_session):
        def check_if_attribute_exists(obj, attribute):
            a = getattr(obj, attribute, None)
            if a:
                return True
            else:
                return False
        property_des = self.pre_site_note.property_description.model_dump()

        # Seconday Heating
        secondary_heating = self.get_attribute_and_load(
            self.pre_site_note.property_description, 
            "secondaryHeatingType",
            HeatingType,
            db_session
        )

        # main heating 2 and main heating 2 controls
        mainheating2 = None
        mainheating2controls = None
        if check_if_attribute_exists(self.pre_site_note.property_description, "mainHeating2"):
            if check_if_attribute_exists(self.pre_site_note.property_description.mainHeating2, "controls"):
                mainheating2controls = self.get_attribute_and_load(self.pre_site_note.property_description.mainHeating2, "controls", HeatingSystemControls, db_session)
                data = self.pre_site_note.property_description.mainHeating2.model_dump()
                if data:
                    mainheating2 = self.upsert_record(
                        db_session=db_session,
                        model_class=Heating,
                        data_dict=data,
                        lookup_field=None,
                        additional_fields= {"controls_id": mainheating2controls.id},
                    )

        # main heating and main heating control
        mainheating = None
        mainheatingcontrols = None
        if check_if_attribute_exists(self.pre_site_note.property_description, "mainHeating"):
            if check_if_attribute_exists(self.pre_site_note.property_description.mainHeating, "controls"):
                print(self.pre_site_note.property_description.mainHeating)
                mainheatingcontrols = self.get_attribute_and_load(self.pre_site_note.property_description.mainHeating, 'controls', HeatingSystemControls, db_session)
                data = self.pre_site_note.property_description.mainHeating.model_dump()
                if data:
                    mainheating = self.upsert_record(
                        db_session=db_session,
                        model_class=Heating,
                        data_dict=data,
                        lookup_field=None,
                        additional_fields={"controls_id": mainheatingcontrols.id}
                    )

        # Other details
        otherDetails = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "otherDetails",
            OtherDetails,
            db_session
        )

        # windTurbine
        windTurbine = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "windTurbine", 
            WindTurbine,
            db_session
        )

        #photo_volatic_panel
        photo_volatic_panel = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "photovoltaicPanel",
            PhotovoltaicPanel,
            db_session,
        )

        #fluegasheatrecoverysystem
        flue_gas_heat_recovery_system = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "flueGasHeatRecoverySystem",
            FlueGasHeatRecoverySystem,
            db_session,
        )

        #shower and baths
        shower_and_baths = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "showerAndBaths",
            ShowerAndBaths,
            db_session,
        )

        #solar water heating
        solar_water_heating = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "solarWaterHeating",
            SolarWaterHeating,
            db_session, 
        )

        # hotwatercycling
        hot_water_cylinder = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "hotWaterCylinder",
            HotWaterCylinder,
            db_session,
        )

        # water heating
        water_heating = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "waterHeating",
            WaterHeating,
            db_session,
        )

        # lighting
        lighting = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "lighting",
            Lighting,
            db_session,
        )

        # ventilation and cooling
        ventilation_and_cooling = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "ventilationAndCooling",
            VentilationAndCooling,
            db_session,
        )

        # door
        door = self.get_attribute_and_load(
            self.pre_site_note.property_description,
            "door",
            Door,
            db_session,
        )

        def upload_property_detail(property_part="main_property"):
            if check_if_attribute_exists(self.pre_site_note.property_description, property_part):
                wall = None
                obj = getattr(self.pre_site_note.property_description, property_part)
                if check_if_attribute_exists(obj, "wall"):
                    wall = self.get_attribute_and_load(obj, "wall", Walls, db_session)
                roof = None
                if check_if_attribute_exists(obj, "roof"):
                    roof = self.get_attribute_and_load(obj, "roof", Roofs, db_session)
                floor = None
                if check_if_attribute_exists(obj, "floor"):
                    floor = self.get_attribute_and_load(obj, "floor", Floors, db_session)

                property_detail = self.upsert_record(
                    db_session=db_session,
                    model_class=PropertyDetail,
                    data_dict={
                        "age_band": obj.age_band,
                        "floor_id": floor.id if floor else None,
                        "roof_id": roof.id if roof else None,
                        "wall_id": wall.id if wall else None,
                    },
                    lookup_field=None,
                )
                
                dimensions = []
                if check_if_attribute_exists(obj, "dimensions"):
                    dimension_obj = getattr(obj, "dimensions")
                    for eachDimension in dimension_obj:
                        data = eachDimension.model_dump()
                        dimension = self.upsert_record(
                            db_session=db_session,
                            model_class=Dimension,
                            data_dict=data,
                            lookup_field=None,
                            additional_fields={"property_detail_id": property_detail.id},
                        )
                        dimensions.append(dimension.id)

                windows = []
                if check_if_attribute_exists(obj, "windows"):
                    windows_obj = getattr(obj, "windows")
                    for eachWindow in windows_obj:
                        data = eachWindow.model_dump()
                        window = self.upsert_record(
                            db_session=db_session,
                            model_class=Windows,
                            data_dict=data,
                            lookup_field=None,
                            additional_fields={"property_detail_id": property_detail.id},
                        )
                        windows.append(window.id)

                return property_detail

        # main_property
        main_property = upload_property_detail("main_property")
        ex1_property = upload_property_detail("ex1_property")
        ex2_property = upload_property_detail("ex2_property")
        ex3_property = upload_property_detail("ex3_property")
        ex4_property = upload_property_detail("ex4_property")

        data = self.pre_site_note.property_description.model_dump()
        def remove_dicts_and_lists(data):
            if isinstance(data, dict):
                # Create a new dict with only primitive types (ignore dicts/lists)
                return {
                    k: remove_dicts_and_lists(v)
                    for k, v in data.items()
                    if not isinstance(v, (dict, list))
                }
            elif isinstance(data, list):
                # Remove lists entirely
                return None
            else:
                return data
        data = remove_dicts_and_lists(data)
        property_description = self.upsert_record(
            db_session=db_session,
            model_class=PropertyDescription,
            data_dict=data,
            lookup_field=None,
            additional_fields={
                "main_heating_id": mainheating.id if mainheating else None,
                "main_heating_controls_id": mainheatingcontrols.id if mainheatingcontrols else None,
                "main_heating2_id": mainheating2.id if mainheating2 else None,
                "main_heating2_controls_id": mainheating2controls.id if mainheating2controls else None,
                "secondary_heating_type_id": secondary_heating.id if secondary_heating else None,
                "other_details_id": otherDetails.id if otherDetails else None,
                "wind_turbine_id": windTurbine.id if windTurbine else None,
                "photovoltaic_panel_id": photo_volatic_panel.id if photo_volatic_panel else None,
                "flue_gas_heat_recovery_system_id": flue_gas_heat_recovery_system.id if flue_gas_heat_recovery_system else None,
                "shower_and_baths_id": shower_and_baths.id if shower_and_baths else None,
                "solar_water_heating_id": solar_water_heating.id if solar_water_heating else None,
                "hot_water_cylinder_id": hot_water_cylinder.id if hot_water_cylinder else None,
                "water_heating_id": water_heating.id if water_heating else None,
                "lighting_id": lighting.id if lighting else None,
                "ventilation_and_cooling_id": ventilation_and_cooling.id if ventilation_and_cooling else None,
                "door_id": door.id if door else None,
                "main_property_id": main_property.id if main_property else None,
                "ex1_property_id": ex1_property.id if ex1_property else None,
                "ex2_property_id": ex2_property.id if ex2_property else None,
                "ex3_property_id": ex3_property.id if ex3_property else None,
                "ex4_property_id": ex4_property.id if ex4_property else None,
            }
        )
        return property_description
            


    def load_company_table(self, db_session):
        company_data = self.pre_site_note.company_information.model_dump()
        return self.upsert_record(
            db_session=db_session,
            model_class=CompanyInfo,
            data_dict=company_data,
            lookup_field="trading_name"
        )
    

    def create_document_table_via_pre_site_note(self, db_session, pre_site_note, assessor, building):
        data = {
            "assessor_id": assessor.id,
            "created_at": self.pre_site_note.survey_information.inspection_date,
            "document_type": ReportType.QUIDOS_PRESITE_NOTE,
            "building_id": building.id,
            "target_table": "pre_site_note",
            "target_id": pre_site_note.id
        }
        return self.upsert_record(
            db_session=db_session,
            model_class=Documents,
            data_dict=data,
            lookup_field=None, 
        )

    def create_buildings_table(
        self,
        db_session,
        landlord_id,
        domna_id,
    ):
        
        data = {
            "address": self.pre_site_note.survey_information.address,
            "postcode": self.pre_site_note.survey_information.postcode,
            "UPRN": self.pre_site_note.survey_information.uprn,
            "landlord_id": landlord_id,
            "domna_id": domna_id
        }
        building = self.upsert_record(
            db_session=db_session,
            model_class=Buildings,
            data_dict=data,
            lookup_field="UPRN",
        )

        return building

    
    def create_pre_site_note_table(
        self, 
        db_session,
        assessor,
        summary_info,
        pre_site_note_description,
    ):
        preSiteNote = PreSiteNote(
            summary_info_id=summary_info.id,
            assessor_id=assessor.id,
            pre_site_note_description_id=pre_site_note_description.id,
        )
        db_session.add(preSiteNote)
        db_session.commit()

        return preSiteNote
        

        
    def upsert_record(
        self,
        db_session,
        model_class,
        data_dict,
        lookup_field,
        update_if_exists: bool = False,
        additional_fields: dict = None
    ):
        clean_data = data_dict

        # Merge additional fields if provided
        if additional_fields:
            clean_data.update(additional_fields)

        if lookup_field is not None:
            lookup_value = clean_data.get(lookup_field)
            if not lookup_value:
                raise ValueError(f"Missing lookup field '{lookup_field}' in data.")

            # Try to find existing record
            existing_record = db_session.query(model_class).filter(
                getattr(model_class, lookup_field) == lookup_value
            ).first()

            if existing_record:
                # Update existing record if update_if_exists is True
                if update_if_exists:
                    for key, value in clean_data.items():
                        setattr(existing_record, key, value)
                    db_session.commit()
                return existing_record

        # Filter out invalid fields that don't exist in the model class
        valid_fields = [field for field in clean_data if hasattr(model_class, field)]
        clean_data = {field: clean_data[field] for field in valid_fields}

        print(f'clean data is {clean_data}')

        # Handle Pydantic models (with model_validate or parse_obj)
        new_record = model_class(**clean_data)

        # Add the new record to the session and commit
        db_session.add(new_record)
        db_session.commit()

        return new_record
    


        
    def load_assessor_table(self, db_session):
        company = self.load_company_table(db_session)
        assessor_data = self.pre_site_note.assessor_information.model_dump()
        return self.upsert_record(
            db_session=db_session,
            model_class=AssessorInfo,
            data_dict=assessor_data,
            lookup_field="accreditation_number",
            additional_fields={"company_id": company.id}
        )

    def get_insulation_info(self):
        if self.csr:
            if self.csr.insulation_info:
                insultation = self.csr.insulation_info.type.upper()
                return insultation
        return None
    
    @staticmethod
    def get_band(sap_score_number):
        bands = [
            ("HIGH A", 96, float("inf")),
            ("LOW A", 92, 96),
            ("HIGH B", 86, 92),
            ("LOW B", 81, 86),
            ("HIGH C", 74.5, 81),
            ("LOW C", 69, 74.5),
            ("HIGH D", 61.5, 69),
            ("LOW D", 55, 61.5),
            ("HIGH E", 46.5, 55),
            ("LOW E", 39, 46.5),
            ("HIGH F", 29.5, 39),
            ("LOW F", 21, 29.5),
            ("HIGH G", 10.5, 21),
            ("LOW G", 1, 10.5),
        ]

        for band, lower, upper in bands:
            if lower <= sap_score_number < upper:
                return band

        return None

    @staticmethod 
    def gbis_or_eco4_scheme(presap_letter, postsap_letter):
        """
        *ECO4 (minimum movement)* 
        D to C 
        E to C 
        F to D 
        G to D 

        G -> ABCD
        F -> ABCD
        E -> ABC
        D -> ABC

        *GBIS - Cavity Wall Insulation ONLY* 

        D-D 
        E-D 
        E-E 
        F-E 
        F-F 
        G-E 
        G-F 
        G-G 
        """
        eco4 = {
            "G": ['A', 'B', 'C', 'D'],
            "F": ['A', 'B', 'C', 'D'],
            "E": ['A', 'B', 'C'],
            "D": ['A', 'B', 'C'],
        }

        gbis ={
            'D': ['D'],
            'E': ['E', 'D'],
            'F': ['E', 'F'],
            'G': ['E', 'F', 'G'],
        }

        if presap_letter.upper() in eco4:
            if postsap_letter.upper() in eco4[presap_letter.upper()]:
                return "ECO4"
        if presap_letter.upper() in gbis:
            if postsap_letter.upper() in gbis[presap_letter.upper()]:
                return "GBIS"

        return None



    def work_out_total_floor_area(self):

        total = 0
        def add_all_floors(floor_list):
            total = 0
            for floor in floor_list:
                total += floor.floor_area_m2

            return total
            
        main = True if self.pre_site_note.property_description.no_of_main_property > 0 else False
        ext1 = True if self.pre_site_note.property_description.no_of_extension_1 > 0 else False
        ext2 = True if self.pre_site_note.property_description.no_of_extension_2 > 0 else False
        ext3 = True if self.pre_site_note.property_description.no_of_extension_3 > 0 else False
        ext4 = True if self.pre_site_note.property_description.no_of_extension_4 > 0 else False

        total += add_all_floors(self.pre_site_note.property_description.main_property.dimensions) if main is True else 0
        total += add_all_floors(self.pre_site_note.property_description.ex1_property.dimensions) if ext1 is True else 0
        total += add_all_floors(self.pre_site_note.property_description.ex2_property.dimensions) if ext2 is True else 0
        total += add_all_floors(self.pre_site_note.property_description.ex3_property.dimensions) if ext3 is True else 0
        total += add_all_floors(self.pre_site_note.property_description.ex4_proprerty.dimensions) if ext4 is True else 0


        floor_area = math.ceil(total) if total%1 >=0.5 else math.floor(total)
        if 0 <= floor_area <= 72:
            return '0-72m', floor_area
        elif 72 < floor_area <= 97:
            return '73-97m', floor_area
        elif 97 < floor_area <= 199:
            return '98-199m', floor_area
        elif 199 <= floor_area:
            return 'over 200m', floor_area
        else:
            raise NotImplementedError(f"unknown floor area {floor_area} {self.pre_site_note.summary_information.address}")
        
    def get_current_sap_score(self):
        score_list = self.pre_site_note.survey_information.current_sap.split(" ")
        score = int(score_list[1])
        return score