Untitled

from typing import List
from sqlalchemy.orm import Session
from fastapi import Depends

from property_rater.database.session import get_database_session, get_engine
from property_rater.valuations_tool.models import Property, PropertyCalculated


from property_rater.database.session import get_database_session

session = next(get_database_session())


property1 = Property(id=1,
                     street_name="London Bridge 1",
                     country="US",
                     state_code="FL",
                     air_construction="Unknown",
                     occupancy_type="302",
                     current=True, )
property2 = Property(id=2,
                     street_name="London Bridge 2",
                     country="US", state_code="FL",
                     air_construction="Unknown",
                     occupancy_type="300",
                     current=True, )
property3 = Property(id=3,
                     street_name="Leaden Hall 5",
                     country="US",
                     state_code="TX",
                     air_construction="Unknown",
                     occupancy_type="302",
                     current=True, )

properties = [property1, property2, property3]


class Helpers:
    @staticmethod
    def get_suggested_tiv_per_square_foot(properties_list: List[Property], session: Session = Depends(get_database_session)):
        statement = """SELECT country.alpha2_code AS country_name,
                              state.state_code AS state_name,
                              air_occ.air_occ_code AS air_occ_code,
                              air_con.air_construction as air_construction,
                              occ.q2 AS occ_q2
                        FROM occ_con_state_quartile AS occ
                        JOIN air_occupancy AS air_occ
                              ON occ.air_occupancy_id = air_occ.air_occupancy_id
                        JOIN country AS country
                              ON occ.country_id = country.country_id
                        JOIN state AS state
                              ON occ.state_id = state.state_id
                        JOIN air_construction AS air_con
                              ON occ.air_construction_id = air_con.air_construction_id
                        """
        occ_con_state_quartile_ref = session.execute(statement).all()
        occ_con_state_quartile_ref = [item for item in occ_con_state_quartile_ref]

        statement = """SELECT country.alpha2_code AS country_name,
                              air.air_occ_code AS air_code,
                              occ.q2 AS occ_q2
                        FROM occ_quartile AS occ
                        JOIN air_occupancy AS air
                              ON occ.air_occupancy_id = air.air_occupancy_id
                        JOIN country AS country
                              ON occ.country_id = country.country_id
                        """
        occ_quartile_ref = session.execute(statement).all()
        occ_quartile_ref = [item for item in occ_quartile_ref]

        properties_list_with_suggested_tiv = []
        for _ in properties_list:
            suggested_tiv = None
            for item in occ_con_state_quartile_ref:
                if _.country == item[0] and _.state_code == item[1] and _.occupancy_type == item[3] and \
                        _.air_construction == item[3]:
                    suggested_tiv = item[4]
            if suggested_tiv is None:
                for item in occ_quartile_ref:
                    if _.country == item[0] and _.occupancy_type == item[1]:
                        suggested_tiv = item[2]

            properties_list_with_suggested_tiv.append({"street_name": _.street_name,
                                                       "country": _.country,
                                                       "state": _.state_code,
                                                       "occ": _.occupancy_type,
                                                       "con": _.air_construction,
                                                       "suggested_tiv": suggested_tiv})
            # properties_list_with_suggested_tiv.append(PropertyCalculated(current_property_id=_.id,
            #                                                              suggested_tiv_per_sqft=suggested_tiv))

        return properties_list_with_suggested_tiv