Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
13 kB
11
Indexable
Never
class Quote(Base):
    __tablename__ = 'quote'
    id = Column(UUIDType, primary_key=True)
    eclipse_reference = Column(String(80), nullable=True)
    insured_name = Column(String(80), nullable=True)
    reinsured_name = Column(String(80), nullable=True)
    broker_name = Column(String(80), nullable=True)
    inception_date = Column(Date, nullable=True)
    expiry_date = Column(Date, nullable=True)
    policy_term = Column(Integer, nullable=True)  # [days]
    status = Column(String(80), nullable=True)  # e.g. 'draft', 'quotable'
    year_of_account = Column(String(4), nullable=True)
    preceding_quote_id = Column(UUIDType, nullable=True)  # if NOT NULL then the quote is a renewal
    file_id = Column(String(80), nullable=True)  # a unique identifier of the SoR file in the blob storage, maybe URL?

    asset_quote = relationship("Asset", back_populates="quote_asset")  # 1 quote - N assets


class Asset(Base):
    __tablename__ = 'asset'
    id = Column(UUIDType, primary_key=True)
    loc_id = Column(Integer, nullable=True)  # if NULL then asset has been input through UI, and not through SoV upload
    quote_id = Column(UUIDType, ForeignKey('quote.id'), index=True)  # Can't be NULL - an asset needs to be attached
                                                                     # to a quote
    address_id = Column(UUIDType, ForeignKey('address.id'), index=True)

    #  Potential split to a separate 'SoV' table could happen here, the relationship between Asset & SoV would be 1-1:
    floor_area = Column(Float, nullable=True)
    number_of_buildings = Column(Integer, nullable=True)
    number_of_stories = Column(Integer, nullable=True)
    year_built = Column(String(4), nullable=True)  # ensure '0', '-', ' ' and '' are sanitised to NULL

    occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
    construction_type_id = Column(Integer, ForeignKey('construction_type.id'), index=True)

    tiv_building = Column(Float, nullable=True)
    tiv_contents = Column(Float, nullable=True)
    tiv_business_interruption = Column(Float, nullable=True)

    quote_asset = relationship("Quote", back_populates="asset_quote")  # N assets - 1 quote
    address_asset = relationship("Address", back_populates="asset_address")  # N assets - 1 address
    occupancy_asset = relationship("OccupancyType", back_populates="asset_occupancy")  # N assets - 1 occ type
    construction_asset = relationship("ConstructionType", back_populates="asset_construction")  # N assets - 1 con type


class Address(Base):
    __tablename__ = 'address'
    id = Column(UUIDType, primary_key=True)  # does this need to be a UUID, maybe an INT would be enough?
    street_name = Column(String(80), nullable=True)  # both street name and number, e.g. '122 Leadenhall Street'
    postal_code = Column(String(20), nullable=True)
    city_id = Column(Integer, ForeignKey('city.id'), index=True)
    territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)
                                                                                                     # e.g. county
    territorial_entity_high_level_id = Column(Integer, ForeignKey('territorial_entity_high_level.id'), index=True)
                                                                                                # e.g. state, province
    country_id = Column(Integer, ForeignKey('country.id'), index=True)
    latitude = Column(String(80), nullable=True)
    longitude = Column(String(80), nullable=True)

    asset_address = relationship("Asset", back_populates="address_asset")  # 1 address - N assets
    city_address = relationship("City", back_populates="address_city")  # N addresses - 1 city
    territorial_entity_low_level_address = relationship("TerritorialEntityLowLevel",
                                                        back_populates="address_territorial_entity_low_level")
                                                        # N addresses - 1 entity
    territorial_entity_high_level_address = relationship("TerritorialEntityHighLevel",
                                                         back_populates="address_territorial_entity_high_level")
                                                         # N addresses - 1 entity
    country_address = relationship("Country", back_populates="address_country")  # N addresses - 1 country


class OccupancyType(Base):
    __tablename__ = 'occupancy_type'
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(20), nullable=False)  # e.g. '420'
    scheme = Column(String(20), nullable=True)  # e.g. 'AIR'
    category = Column(String(80), nullable=True)  # e.g. 'Industrial Facilities Model'
    description = Column(Text, nullable=True)  # e.g. 'Electronic and other electrical equipment (except computer
                                               # equipment)'

    asset_occupancy = relationship("Asset", back_populates="occupancy_asset")  # 1 occ type - N assets
    quartile_occupancy = relationship("OccupancyCountryQuartile", back_populates="occupancy_quartile")
                                                                                                    # 1 occ type - N q
    quartile_construction_occupancy = relationship("OccupancyConstructionStateCountryQuartile",  # 1 occ type - N q
                                                   back_populates="occupancy_construction_quartile")


class ConstructionType(Base):
    __tablename__ = 'construction_type'
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(20), nullable=False)  # e.g. '2274'
    scheme = Column(String(20), nullable=False)  # e.g. 'AIR'
    category = Column(String(80), nullable=True)  # e.g. 'Pipelines'
    description_short = Column(String(80), nullable=True)  # e.g. 'Underground Concrete Pipelines'
    description_full = Column(Text, nullable=True)  # e.g. 'Pipelines located under the surface of the ground and made
                                                    # of concrete material'

    asset_construction = relationship("Asset", back_populates="construction_asset")  # 1 con type - N assets
    quartile_occupancy_construction = relationship("OccupancyConstructionStateCountryQuartile",   # 1 con type - N q
                                                   back_populates="construction_occupancy_quartile")


class City(Base):
    __tablename__ = 'city'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(80), nullable=False)
    territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)

    address_city = relationship("Address", back_populates="city_address")  # 1 city - N addresses
    territorial_entity_low_level_city = relationship("TerritorialEntityLowLevel",  # N cities - 1 low level territory
                                                     back_populates="city_territorial_entity_low_level")


class TerritorialEntityLowLevel(Base):
    __tablename__ = 'territorial_entity_low_level'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(80), nullable=False)
    territorial_entity_high_level_id = Column(Integer, ForeignKey('territorial_entity_high_level.id'), index=True)

    address_territorial_entity_low_level = relationship("Address",
                                                        back_populates="territorial_entity_low_level_address")
                                                        # 1 low level territory - N addresses
    city_territorial_entity_low_level = relationship("City", back_populates="territorial_entity_low_level_city")
                                                     # 1 low level territory - N cities
    territorial_entity_high_low_level = relationship("TerritorialEntityHighLevel",
                                                     back_populates="territorial_entity_low_high_level")
                                                     # N low level territories - 1 high level territory


class TerritorialEntityHighLevel(Base):
    __tablename__ = 'territorial_entity_high_level'
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(20), nullable=False)
    name = Column(String(80), nullable=False)
    country_id = Column(Integer, ForeignKey('country.country_id'), index=True)

    address_territorial_entity_high_level = relationship("Address",
                                                         back_populates="territorial_entity_high_level_address")
                                                         # 1 high level territory - N addresses
    territorial_entity_low_high_level = relationship("TerritorialEntityLowLevel",
                                                     back_populates="territorial_entity_high_low_level")
                                                     # 1 high level territory - N low level territories
    country_territorial_entity_high_level = relationship("Country",
                                                         back_populates="territorial_entity_high_level_country")
                                                         # N high level territories - 1 country
    quartile_country_state = relationship("OccupancyConstructionStateCountryQuartile",
                                          back_populates="state_country_quartile")
                                          # 1 high level territory - N q


class Country(Base):
    __tablename__ = 'country'
    id = Column(Integer, primary_key=True, index=True)
    code = Column(String(10), nullable=True)
    name = Column(String(80), nullable=False)

    address_country = relationship("Address", back_populates="country_address")  # 1 country - N addresses
    territorial_entity_high_level_country = relationship("TerritorialEntityHighLevel",
                                                         back_populates="country_territorial_entity_high_level")
                                                         # N high level territories - 1 country
    quartile_country = relationship("OccupancyCountryQuartile", back_populates="country_quartile")  # 1 country - N q
    quartile_state_country = relationship("OccupancyConstructionStateCountryQuartile",
                                          back_populates="country_state_quartile")  # 1 country - N q


class OccupancyCountryQuartile(Base):
    __tablename__ = 'occupancy_country_quartile'
    id = Column(Integer, primary_key=True, index=True)
    country_id = Column(Integer, ForeignKey('country.id'), index=True)
    occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
    mean = Column(Float, nullable=True)
    number = Column(Float, nullable=True)
    q1 = Column(Float, nullable=True)
    q2 = Column(Float, nullable=True)
    q3 = Column(Float, nullable=True)
    effective_from = Column(Date, nullable=True)
    effective_to = Column(Date, nullable=True)

    occupancy_quartile = relationship("OccupancyType", back_populates="quartile_occupancy")  # N q - 1 occ type
    country_quartile = relationship("Country", back_populates="quartile_country")  # N q - 1 country


class OccupancyConstructionStateCountryQuartile(Base):
    __tablename__ = 'occupancy_construction_state_country_quartile'
    id = Column(Integer, primary_key=True, index=True)
    occupancy_type_id = Column(Integer, ForeignKey('occupancy_type.id'), index=True)
    construction_type_id = Column(Integer, ForeignKey('construction_type.id'), index=True)
    territorial_entity_low_level_id = Column(Integer, ForeignKey('territorial_entity_low_level.id'), index=True)
    country_id = Column(Integer, ForeignKey('country.country_id'), index=True)
    mean = Column(Float, nullable=True)
    number = Column(Integer, nullable=True)
    q1 = Column(Float, nullable=True)
    q2 = Column(Float, nullable=True)
    q3 = Column(Float, nullable=True)
    effective_from = Column(Date, nullable=True)
    effective_to = Column(Date, nullable=True)

    occupancy_construction_quartile = relationship("OccupancyType",  #  N q - 1 occ type
                                                   back_populates="quartile_construction_occupancy")
    construction_occupancy_quartile = relationship("ConstructionType",   # N q - 1 con type
                                                   back_populates="quartile_occupancy_construction")
    state_country_quartile = relationship("TerritorialEntityHighLevel",
                                          back_populates="quartile_country_state")  # N q - 1 high level territory
    country_state_quartile = relationship("Country", back_populates="quartile_state_country")  # N q - 1 country