survey-extraction/etl/db/hubSpotLoad.py

from etl.db.db import get_db_session, init_db
from etl.models.topLevel import HubspotDealData, HubspotCommpanyData
from sqlmodel import select

class HubspotTodb():
    def __init__(self):
        init_db()

    def new_record_to_hubspot_data(self, deal_data, company, listing):
        print("This has been depreciated using new interface")
        self.upsert_hubspot_deal(self, deal_data, company, listing) 

        
    def new_record_company(self, company_data):
        """
        Adds a new records to the hubspot_compnay_data table
        """
        
        with get_db_session() as session:
            new_record = HubspotCommpanyData(
                company_id=company_data.get("hs_object_id"),
                company_name=company_data.get("name")
            )
            session.add(new_record)
            session.commit()
            session.refresh(new_record)
            return new_record
        
    def find_all_deals_with_company_id(self, company_id):
        """
        Returns a list of records that have a company_id from the hubspot_deal_data table
        """
        """
        Returns a list of records that have a company_id from the hubspot_deal_data table
        """
        with get_db_session() as session:
            results = (
                session.query(HubspotDealData)
                .filter(HubspotDealData.company_id == company_id)
                .all()
            )
            return results
        
    def update_deal(self, deal_in_db, hubspot_client):
        """
        Checks if a deal needs updating and updates it in the db with the latest information.
        Performs soft assertions for field-level consistency between DB and HubSpot data.
        """
        def soft_assert(condition, message="Assertion Failed"):
            if not condition:
                print(f"⚠️ Soft Assert Failed: {message}")
                return False
            return True

        print(f"🔍 Checking if deal needs updating (deal_id={deal_in_db.deal_id})") 

        hs_deal, hs_company_id, hs_listing = hubspot_client.get_deal_info_for_db(deal_in_db.deal_id)

        results = [
            soft_assert(deal_in_db.deal_id == hs_deal.get("hs_object_id"),
                        "deal_id mismatch"),
            soft_assert(deal_in_db.company_id == hs_company_id,
                        "company_id mismatch"),
            soft_assert(deal_in_db.landlord_property_id == hs_listing.get("owner_property_id"),
                        "landlord_property_id mismatch"),
            soft_assert(deal_in_db.outcome == hs_deal.get("outcome"),
                        "outcome mismatch"),
            soft_assert(deal_in_db.dealstage == hs_deal.get("dealstage"),
                        "dealstage mismatch"),
            soft_assert(deal_in_db.dealname == hs_deal.get("dealname"),
                        "dealname mismatch"),
            soft_assert(deal_in_db.project_code == hs_deal.get("project_code"),
                        "project_code mismatch"),
            soft_assert(deal_in_db.uprn == hs_listing.get("national_uprn"),
                        "uprn mismatch"),
            soft_assert(deal_in_db.outcome_notes == hs_deal.get("outcome_notes"),
                        "outcome_notes mismatch"),
        ]

        # if any of the soft asserts failed
        if not all(results):
            print(f"❗ Discrepancies found for deal_id {deal_in_db.deal_id} — database may need updating.")
            self.upsert_hubspot_deal(hs_deal, hs_company_id, hs_listing)
            return False
        else:
            print(f"✅ All checks passed for deal_id {deal_in_db.deal_id}. No need to update.")
            return True
        
    def upsert_hubspot_deal(self, deal_data, company, listing):
        """
        Inserts a new record or updates an existing record in hubspot_deal_data.
        Uses deal_id (hs_object_id) as the unique identifier.
        """
        with get_db_session() as session:
            deal_id = deal_data.get("hs_object_id")

            # Use SQLModel's modern query style
            statement = select(HubspotDealData).where(HubspotDealData.deal_id == deal_id)
            existing = session.exec(statement).first()

            if existing:
                print(f"🔄 Updating existing deal (deal_id={deal_id})")

                existing.dealname = deal_data.get("dealname", existing.dealname)
                existing.dealstage = deal_data.get("dealstage", existing.dealstage)
                existing.landlord_property_id = listing.get("owner_property_id", existing.landlord_property_id)
                existing.uprn = listing.get("national_uprn", existing.uprn)
                existing.outcome = deal_data.get("outcome", existing.outcome)
                existing.outcome_notes = deal_data.get("outcome_notes", existing.outcome_notes)
                existing.project_code = deal_data.get("project_code", existing.project_code)
                existing.company_id = company or existing.company_id

                session.add(existing)
                session.commit()
                session.refresh(existing)
                return existing

            else:
                print(f"🆕 Inserting new deal (deal_id={deal_id})")

                new_record = HubspotDealData(
                    deal_id=deal_id,
                    dealname=deal_data.get("dealname"),
                    dealstage=deal_data.get("dealstage"),
                    landlord_property_id=listing.get("owner_property_id"),
                    uprn=listing.get("national_uprn"),
                    outcome=deal_data.get("outcome"),
                    outcome_notes=deal_data.get("outcome_notes"),
                    project_code=deal_data.get("project_code"),
                    company_id=company,
                )

                session.add(new_record)
                session.commit()
                session.refresh(new_record)
                return new_record