presentation_models.py

# annotations import is required for sqlalchemy annotations to work properly
from __future__ import annotations

import logging
from decimal import Decimal
from typing import List, Optional
from typing_extensions import Annotated, TypedDict

from sqlalchemy import String, JSON
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.schema import ForeignKey

from compendium_v2.db import db
from compendium_v2.db.presentation_model_enums import CarryMechanism, CommercialCharges, UserCategory, \
    ConnectivityCoverage, ConnectionMethod, YesNoPlanned, MonitoringMethod, CommercialConnectivityCoverage, \
    FeeType, ServiceCategory


logger = logging.getLogger(__name__)

str256 = Annotated[str, mapped_column(String(256))]
str256_pk = Annotated[str, mapped_column(String(256), primary_key=True)]
int_pk = Annotated[int, mapped_column(primary_key=True)]
int_pk_fkNREN = Annotated[int, mapped_column(ForeignKey("nren.id"), primary_key=True)]
user_category_pk = Annotated[UserCategory, mapped_column(primary_key=True)]
json_str_list = Annotated[List[str], mapped_column(JSON)]

ExternalConnection = TypedDict(
    'ExternalConnection',
    {
        'link_name': str,
        'capacity': Optional[Decimal],
        'from_organization': str,
        'to_organization': str,
        'interconnection_method': Optional[ConnectionMethod]
    }
)

RemoteCampus = TypedDict(
    'RemoteCampus',
    {'country': str, 'local_r_and_e_connection': Optional[bool]}
)


# Unfortunately flask-sqlalchemy doesnt fully support DeclarativeBase yet.
# See https://github.com/pallets-eco/flask-sqlalchemy/issues/1140
# mypy: disable-error-code="name-defined"

class PresentationModel(db.Model):
    __abstract__ = True


class PreviewYear(PresentationModel):
    __tablename__ = 'preview_year'
    year: Mapped[int_pk]


class NREN(PresentationModel):
    """
    The NREN model represents a National Research and Education Network.
    They are the primary entities in the Compendium, and the data for every other model is linked to an NREN.

    NRENs are identified by their name and which country they are from.
    """
    __tablename__ = 'nren'
    id: Mapped[int_pk]
    name: Mapped[str256]
    country: Mapped[str256]

    def to_dict(self, download=False):
        return {
            'nren': self.name,
            'nren_country': self.country,
        }

    def to_dict_with_id(self):
        return {
            'id': self.id,
            'name': self.name,
            'country': self.country,
        }

    def __repr__(self):
        return f'<NREN {self.id} | {self.name}>'

    def __hash__(self):
        return hash(self.id)

    def __eq__(self, other):
        return hasattr(other, 'id') and self.id == other.id


class BudgetEntry(PresentationModel):
    """
    The BudgetEntry model represents the budget of an NREN for a specific year.
    The budget is expected to be Millions of Euros.
    """
    __tablename__ = 'budgets'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    budget: Mapped[Decimal]

    def to_dict(self, download=False):
        budget = float(self.budget)
        if download:
            budget = "{:.0f}".format(self.budget * 1_000_000)
        return {
            **self.nren.to_dict(),
            'budget': budget,
            'year': self.year,
        }


class FundingSource(PresentationModel):
    """
    The FundingSource model represents the sources of funding for an NREN for a specific year.
    The various sources of funding are expressed in percentages, and should add up to 100%.

    The possible funding sources are:

    - Client Institutions

    - European Funding

    - Government and Public Bodies

    - Commercial

    - Other
    """
    __tablename__ = 'funding_source'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    client_institutions: Mapped[Decimal]
    european_funding: Mapped[Decimal]
    gov_public_bodies: Mapped[Decimal]
    commercial: Mapped[Decimal]
    other: Mapped[Decimal]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'client_institutions': float(self.client_institutions),
            'european_funding': float(self.european_funding),
            'gov_public_bodies': float(self.gov_public_bodies),
            'commercial': float(self.commercial),
            'other': float(self.other)
        }


class ChargingStructure(PresentationModel):
    """
    The ChargingStructure model represents the charging structure of an NREN for a specific year.

    The charging structure is represented by a FeeType, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.FeeType

    """
    __tablename__ = 'charging_structure'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    fee_type: Mapped[Optional[FeeType]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'fee_type': self.fee_type.value
            if self.fee_type is not None else None,
        }


class NrenStaff(PresentationModel):
    """
    The NrenStaff model represents the number of staff for an NREN for a specific year.
    Staff are divided into the following categories:

    - Permanent FTE (Full Time Equivalent)

    - Subcontracted FTE

    - Technical FTE

    - Non-Technical FTE

    Permanent + Subcontracted FTE should add up to the total number of staff
    Technical + Non-Technical FTE should add up to the total number of staff
    """
    __tablename__ = 'nren_staff'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    permanent_fte: Mapped[Decimal]
    subcontracted_fte: Mapped[Decimal]
    technical_fte: Mapped[Decimal]
    non_technical_fte: Mapped[Decimal]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'permanent_fte': float(self.permanent_fte),
            'subcontracted_fte': float(self.subcontracted_fte),
            'technical_fte': float(self.technical_fte),
            'non_technical_fte': float(self.non_technical_fte)
        }


class ParentOrganization(PresentationModel):
    """
    The ParentOrganization model represents the parent organization of an NREN for a specific year.

    The parent organization is the organization that the NREN is a part of, if any.
    """
    __tablename__ = 'parent_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str256]

    def compare_dict(self):
        return {
            'nren_id': self.nren_id,
            **self.nren.to_dict(),
            'year': self.year,
            'organization': self.organization
        }

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'name': self.organization
        }


class SubOrganization(PresentationModel):
    """
    The SubOrganization model represents the sub organizations of an NREN for a specific year.

    The sub organizations are the organizations that are part of the NREN, if any.

    The role field is a description of the role of the sub organization, e.g. "CERT team" or "Network Operations".
    """
    __tablename__ = 'sub_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str256_pk]
    role: Mapped[str256_pk]

    def compare_dict(self):
        return {
            'nren_id': self.nren_id,
            **self.nren.to_dict(),
            'year': self.year,
            'organization': self.organization,
            'role': self.role
        }

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'name': self.organization,
            'role': self.role
        }


class ECProject(PresentationModel):
    """
    The ECProject model represents involvement in European Commission projects for an NREN for a specific year.
    An NREN can be involved in several EC projects in a given year.
    """
    __tablename__ = 'ec_project'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    project: Mapped[str256_pk]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'project': self.project
        }


class Policy(PresentationModel):
    """
    The Policy model represents various policies of an NREN for a specific year.

    Usually these are provided as links to the actual policy documents.
    """
    __tablename__ = 'policy'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    strategic_plan: Mapped[str]
    environmental: Mapped[str]
    equal_opportunity: Mapped[str]
    connectivity: Mapped[str]
    acceptable_use: Mapped[str]
    privacy_notice: Mapped[str]
    data_protection: Mapped[str]
    gender_equality: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'strategic_plan': self.strategic_plan,
            'environmental': self.environmental,
            'equal_opportunity': self.equal_opportunity,
            'connectivity': self.connectivity,
            'acceptable_use': self.acceptable_use,
            'privacy_notice': self.privacy_notice,
            'data_protection': self.data_protection,
            'gender_equality': self.gender_equality
        }


class TrafficVolume(PresentationModel):
    """
    The TrafficVolume model represents the traffic volume of an NREN for a specific year.

    The traffic volume data is for each of the following categories:

    - Traffic to customers

    - Traffic from customers

    - Traffic to external networks

    - Traffic from external networks

    The volume numbers are expected to be in Terabytes.
    """
    __tablename__ = 'traffic_volume'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    to_customers: Mapped[Decimal]
    from_customers: Mapped[Decimal]
    to_external: Mapped[Decimal]
    from_external: Mapped[Decimal]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'to_customers': float(self.to_customers),
            'from_customers': float(self.from_customers),
            'to_external': float(self.to_external),
            'from_external': float(self.from_external)
        }


class InstitutionURLs(PresentationModel):
    """
    The InstitutionURLs model represents links to a page on their website listing user institutions.
    """
    __tablename__ = 'institution_urls'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    urls: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'urls': self.urls
        }


class CentralProcurement(PresentationModel):
    """
    The CentralProcurement model represents the central procurement of an NREN for a specific year.

    The amount field is expected to be in Euros.
    """
    __tablename__ = 'central_procurement'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    central_procurement: Mapped[bool]
    amount: Mapped[Optional[Decimal]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'central_procurement': self.central_procurement,
            'amount': float(self.amount) if self.amount else None,
        }


class ServiceManagement(PresentationModel):
    """
    The ServiceManagement model represents the service management of an NREN for a specific year.

    NRENs provide information about whether they have a service management framework and/or service level targets.
    """
    __tablename__ = 'service_management'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    service_management_framework: Mapped[Optional[bool]]
    service_level_targets: Mapped[Optional[bool]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'service_management_framework': self.service_management_framework,
            'service_level_targets': self.service_level_targets
        }


class ServiceUserTypes(PresentationModel):
    """
    The ServiceUserTypes model represents the service user types of an NREN for a specific year.

    It provides information about the user categories that the NREN serves, separated by service category.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory
        :no-index:

    The service category field contains service categories, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.ServiceCategory
    """
    __tablename__ = 'service_user_types'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    service_category: Mapped[ServiceCategory] = mapped_column(primary_key=True)

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value,
            'service_category': self.service_category.value
            if self.service_category is not None else None,
        }


class EOSCListings(PresentationModel):
    """
    The EOSCListings model represents the names of EOSC services that an NREN lists for a specific year.
    """
    __tablename__ = 'eosc_listings'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    service_names: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'service_names': self.service_names,
        }


class Standards(PresentationModel):
    """
    The Standards model represents whether an NREN has implemented various standards for a specific year.

    The standards are:

    - Audits

    - Business Continuity Plans

    - Crisis Management Procedures

    They are invited to provide additional information about how they implement these standards.
    """
    __tablename__ = 'standards'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    audits: Mapped[Optional[bool]]
    audit_specifics: Mapped[str]
    business_continuity_plans: Mapped[Optional[bool]]
    business_continuity_plans_specifics: Mapped[str]
    crisis_management_procedure: Mapped[Optional[bool]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'audits': self.audits,
            'audit_specifics': self.audit_specifics,
            'business_continuity_plans': self.business_continuity_plans,
            'business_continuity_plans_specifics': self.business_continuity_plans_specifics,
            'crisis_management_procedure': self.crisis_management_procedure,
        }


class CrisisExercises(PresentationModel):
    """
    The CrisisExercises model represents the crisis exercises an NREN performs for a specific year.
    """
    __tablename__ = 'crisis_exercises'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    exercise_descriptions: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'exercise_descriptions': self.exercise_descriptions,
        }


class SecurityControls(PresentationModel):
    """
    The SecurityControls model represents the security controls an NREN has in place for a specific year.
    """
    __tablename__ = 'security_controls'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    security_control_descriptions: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'security_control_descriptions': self.security_control_descriptions,
        }


class ConnectedProportion(PresentationModel):
    """
    The ConnectedProportion model represents whether a particular category of institution
    falls within the connectivity remit of the NREN, the actual number of such institutions connected,
    the % market share this represents, and the actual number of end users served in the category.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory

    The coverage field contains the connectivity remit, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.ConnectivityCoverage

    The number_connected field is the actual number of institutions connected.

    The market_share field is the % market share this represents.

    The users_served field is the actual number of end users served in the category.
    """
    __tablename__ = 'connected_proportion'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    coverage: Mapped[Optional[ConnectivityCoverage]]
    number_connected: Mapped[Optional[int]]
    market_share: Mapped[Optional[Decimal]]
    users_served: Mapped[Optional[int]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value if self.user_category is not None else None,
            'coverage': self.coverage.value if self.coverage is not None else None,
            'number_connected': self.number_connected,
            'market_share': float(self.market_share) if self.market_share else None,
            'users_served': self.users_served
        }


class ConnectivityLevel(PresentationModel):
    """
    The ConnectivityLevel model represents the average connectivity level of an NREN, per user category,
    for a specific year. The connectivity level is represented by the typical speed, highest speed, and the proportion
    of users that are connected at the highest speed.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory
        :no-index:
    """
    __tablename__ = 'connectivity_level'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    typical_speed: Mapped[Optional[int]]
    highest_speed: Mapped[Optional[int]]
    highest_speed_proportion: Mapped[Optional[Decimal]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value,
            'typical_speed': self.typical_speed,
            'highest_speed': self.highest_speed,
            'highest_speed_proportion': float(self.highest_speed_proportion) if self.highest_speed_proportion else None,
        }


class ConnectionCarrier(PresentationModel):
    """

    The ConnectionCarrier model represents the methods of carrying IP traffic to users for an NREN for a specific year.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory
        :no-index:

    The carry_mechanism field describes the methods of carrying IP traffic to users, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.CarryMechanism
    """
    __tablename__ = 'connection_carrier'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    carry_mechanism: Mapped[CarryMechanism]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value,
            'carry_mechanism': self.carry_mechanism.value
            if self.carry_mechanism is not None else CarryMechanism.other.value,
        }


class ConnectivityLoad(PresentationModel):
    """
    The ConnectivityLoad model represents the traffic load (in Mbits/s) on the NREN's network for a specific year.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory
        :no-index:

    Each of the load fields represents the average and peak load from and to connected institutions.
    """
    __tablename__ = 'connectivity_load'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    average_load_from_institutions: Mapped[Optional[int]]
    average_load_to_institutions: Mapped[Optional[int]]
    peak_load_from_institutions: Mapped[Optional[int]]
    peak_load_to_institutions: Mapped[Optional[int]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value,
            'average_load_from_institutions': self.average_load_from_institutions,
            'average_load_to_institutions': self.average_load_to_institutions,
            'peak_load_from_institutions': self.peak_load_from_institutions,
            'peak_load_to_institutions': self.peak_load_to_institutions,
        }


class ConnectivityGrowth(PresentationModel):
    """
    The ConnectivityGrowth model represents the expected growth of connectivity, in %, for an NREN for a specific year.

    The user category field describes the user category the values are for, which can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.UserCategory
        :no-index:
    """
    __tablename__ = 'connectivity_growth'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    user_category: Mapped[user_category_pk]
    growth: Mapped[Decimal]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'user_category': self.user_category.value,
            'growth': float(self.growth),
        }


class CommercialConnectivity(PresentationModel):
    """
    The CommercialConnectivity model represents the type of connectivity provided to commercial entities by an NREN
    for a specific year.

    The possible connectivity types for each commercial category are:

    .. autoenum:: compendium_v2.db.presentation_model_enums.CommercialConnectivityCoverage
    """
    __tablename__ = 'commercial_connectivity'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    commercial_r_and_e: Mapped[Optional[CommercialConnectivityCoverage]]
    commercial_general: Mapped[Optional[CommercialConnectivityCoverage]]
    commercial_collaboration: Mapped[Optional[CommercialConnectivityCoverage]]
    commercial_service_provider: Mapped[Optional[CommercialConnectivityCoverage]]
    university_spin_off: Mapped[Optional[CommercialConnectivityCoverage]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'commercial_r_and_e': self.commercial_r_and_e.value
            if self.commercial_r_and_e is not None else None,
            'commercial_general': self.commercial_general.value
            if self.commercial_general is not None else None,
            'commercial_collaboration': self.commercial_collaboration.value
            if self.commercial_collaboration is not None else None,
            'commercial_service_provider': self.commercial_service_provider.value
            if self.commercial_service_provider is not None else None,
            'university_spin_off': self.university_spin_off.value
            if self.university_spin_off is not None else None,
        }


class CommercialChargingLevel(PresentationModel):
    """
    The CommercialChargingLevel model represents the charging level for commercial entities by an NREN
    for a specific year. This means whether the charges are higher than, the same as, or lower than the charges
    for e.g. research and education entities. The charging levels are given for various types of commercial connections.

    The possible charging levels are:

    .. autoenum:: compendium_v2.db.presentation_model_enums.CommercialCharges
    """
    __tablename__ = 'commercial_charging_level'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    collaboration: Mapped[Optional[CommercialCharges]]
    service_supplier: Mapped[Optional[CommercialCharges]]
    direct_peering: Mapped[Optional[CommercialCharges]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'collaboration': self.collaboration.value
            if self.collaboration is not None else None,
            'service_supplier': self.service_supplier.value
            if self.service_supplier is not None else None,
            'direct_peering': self.direct_peering.value
            if self.direct_peering is not None else None,
        }


class RemoteCampuses(PresentationModel):
    """
    The RemoteCampuses model represents the connectivity of remote campuses by an NREN for a specific year.

    Remote campuses are connected to the NREN via various methods, and the NREN can provide information about
    the countries where remote campuses are located, and whether they have a local research and education connection.
    """
    __tablename__ = 'remote_campuses'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    remote_campus_connectivity: Mapped[bool]
    connections: Mapped[List[RemoteCampus]] = mapped_column(JSON)

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'remote_campus_connectivity': self.remote_campus_connectivity,
            'connections': self.connections
        }

    def __iter__(self):
        if self.connections:
            for connection in self.connections:
                yield {
                    **self.nren.to_dict(),
                    'year': self.year,
                    'remote_campus_connectivity': self.remote_campus_connectivity,
                    'country': connection['country'],
                    'local_r_and_e_connection': connection['local_r_and_e_connection']
                }
        else:
            yield {
                **self.nren.to_dict(),
                'year': self.year,
                'remote_campus_connectivity': self.remote_campus_connectivity,
            }

    def to_list(self, download=False):
        return list(self)


class DarkFibreLease(PresentationModel):
    """
    The DarkFibreLease model represents the dark fibre leases of an NREN for a specific year.

    The IRU (Indefeasible Right of Use) or lease field describes whether the dark fibre is leased or owned.

    In case of IRU, the duration of the IRU is given.
    The collected data also includes the length of the fibre both within and outside the country
    """
    __tablename__ = 'dark_fibre_lease'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    iru_or_lease: Mapped[bool]
    fibre_length_in_country: Mapped[Optional[int]]
    fibre_length_outside_country: Mapped[Optional[int]]
    iru_duration: Mapped[Optional[Decimal]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'iru_or_lease': self.iru_or_lease,
            'fibre_length_in_country': self.fibre_length_in_country,
            'fibre_length_outside_country': self.fibre_length_outside_country,
            'iru_duration': float(self.iru_duration) if self.iru_duration else None,
        }


class DarkFibreInstalled(PresentationModel):
    """
    The DarkFibreInstalled model represents the dark fibre installed by an NREN for a specific year.

    The installed field describes whether the dark fibre is installed or not, in addition to the length of the fibre
    """
    __tablename__ = 'dark_fibre_installed'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    installed: Mapped[bool]
    fibre_length_in_country: Mapped[Optional[int]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': int(self.year),
            'installed': self.installed,
            'fibre_length_in_country': self.fibre_length_in_country,
        }


class FibreLight(PresentationModel):
    """
    The FibreLight model represents the different ways in which an NREN lights their fibre networks for a specific year.
    """
    __tablename__ = 'fibre_light'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    light_description: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'light_description': self.light_description
        }


class NetworkMapUrls(PresentationModel):
    """
    The NetworkMapUrls model represents the URLs to online network maps of an NREN for a specific year.
    """
    __tablename__ = 'network_map_urls'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    urls: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'urls': self.urls
        }


class MonitoringTools(PresentationModel):
    """
    The MonitoringTools model represents the monitoring tools used by an NREN for a specific year.

    Descriptions are provided for the tools used.
    """
    __tablename__ = 'monitoring_tools'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    tool_descriptions: Mapped[json_str_list]
    netflow_processing_description: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'tool_descriptions': self.tool_descriptions,
            'netflow_processing_description': self.netflow_processing_description
        }


class PassiveMonitoring(PresentationModel):
    """
    The PassiveMonitoring model represents whether an NREN performs passive monitoring for a specific year.

    The possible passive monitoring methods are (in addition to no passive monitoring):

    .. autoenum:: compendium_v2.db.presentation_model_enums.MonitoringMethod
    """
    __tablename__ = 'passive_monitoring'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    monitoring: Mapped[bool]
    method: Mapped[Optional[MonitoringMethod]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'monitoring': self.monitoring,
            'method': self.method.value if self.method is not None else None,
        }


class TrafficStatistics(PresentationModel):
    """
    The TrafficStatistics model represents whether an NREN provided traffic statistics in a given year,
    and the URLs to the statistics.
    """
    __tablename__ = 'traffic_statistics'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    traffic_statistics: Mapped[bool]
    urls: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'traffic_statistics': self.traffic_statistics,
            'urls': self.urls
        }


class SiemVendors(PresentationModel):
    """
    The SiemVendors model represents the SIEM (Security Information and Event Management) vendors used by an NREN
    for a specific year.
    """
    __tablename__ = 'siem_vendors'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    vendor_names: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'vendor_names': self.vendor_names
        }


class CertificateProviders(PresentationModel):
    """
    The CertificateProviders model represents the certificate providers used by an NREN for a specific year.
    """
    __tablename__ = 'certificate_providers'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    provider_names: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'provider_names': self.provider_names
        }


class WeatherMap(PresentationModel):
    """
    The WeatherMap model represents links to online weather maps provided by an NREN for a specific year, if any.
    """
    __tablename__ = 'weather_map'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    weather_map: Mapped[bool]
    url: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'weather_map': self.weather_map,
            'url': self.url
        }


class PertTeam(PresentationModel):
    """
    The PertTeam model represents whether an NREN has a Performance Enhancement and Response Team (PERT)
    for a specific year, and whether it is planned if not yet implemented.
    """
    __tablename__ = 'pert_team'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    pert_team: Mapped[YesNoPlanned]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'pert_team': self.pert_team.value,
        }


class AlienWave(PresentationModel):
    """
    The AlienWave model represents NREN usage of alien wavelength or lightpath services provided by third parties

    Usage can be either from third parties or internal, and the number of services from third parties can be provided.
    """
    __tablename__ = 'alien_wave'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    alien_wave_third_party: Mapped[Optional[YesNoPlanned]]
    nr_of_alien_wave_third_party_services: Mapped[Optional[int]]
    alien_wave_internal: Mapped[Optional[bool]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'alien_wave_third_party': self.alien_wave_third_party.value
            if self.alien_wave_third_party is not None else None,
            'nr_of_alien_wave_third_party_services': self.nr_of_alien_wave_third_party_services,
            'alien_wave_internal': self.alien_wave_internal
        }


class Capacity(PresentationModel):
    """
    The Capacity model represents the capacity of an NREN's network for a specific year.

    The largest link capacity and typical backbone capacity are provided in Gbps.
    """
    __tablename__ = 'capacity'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    largest_link_capacity: Mapped[Optional[Decimal]]
    typical_backbone_capacity: Mapped[Optional[Decimal]]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'largest_link_capacity': float(self.largest_link_capacity) if self.largest_link_capacity else None,
            'typical_backbone_capacity': float(self.typical_backbone_capacity)
            if self.typical_backbone_capacity else None,
        }


class ExternalConnections(PresentationModel):
    """
    The ExternalConnections model represents the various external connections an NREN has for a specific year.
    It includes the link name, capacity, from and to organizations, and the interconnection method.

    The interconnection method can be one of the following:

    .. autoenum:: compendium_v2.db.presentation_model_enums.ConnectionMethod

    The capacity is expected to be in Gbps.
    """
    __tablename__ = 'external_connections'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    connections: Mapped[List[ExternalConnection]] = mapped_column(JSON)

    def compare_dict(self):
        connections = []

        for connection in self.connections:
            connections.append({
                'link_name': connection['link_name'],
                'capacity': float(connection['capacity']) if connection['capacity'] else None,
                'from_organization': connection['from_organization'],
                'to_organization': connection['to_organization'],
                'interconnection_method': connection['interconnection_method']
                if connection['interconnection_method'] is not None else None
            })

        return {
            'nren_id': self.nren_id,
            'year': self.year,
            'connections': connections
        }

    def to_dict(self, download=False):
        connections = []

        for connection in self.connections:
            connections.append({
                'link_name': connection['link_name'],
                'capacity': float(connection['capacity']) if connection['capacity'] else None,
                'from_organization': connection['from_organization'],
                'to_organization': connection['to_organization'],
                'interconnection_method': ConnectionMethod[connection['interconnection_method']].value
                if connection['interconnection_method'] is not None else None
            })

        return {
            **self.nren.to_dict(),
            'year': self.year,
            'connections': connections
        }

    def __iter__(self):
        for connection in self.connections:
            yield {
                **self.nren.to_dict(),
                'year': self.year,
                'link_name': connection['link_name'],
                'capacity': float(connection['capacity']) if connection['capacity'] else None,
                'from_organization': connection['from_organization'],
                'to_organization': connection['to_organization'],
                'interconnection_method': ConnectionMethod[connection['interconnection_method']].value
                if connection['interconnection_method'] is not None else None
            }

    def to_list(self, download=False):
        return list(self)


class NonREPeers(PresentationModel):
    """
    The NonREPeers model represents the number of non-R&E peers an NREN has for a specific year.
    """
    __tablename__ = 'non_r_and_e_peers'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    nr_of_non_r_and_e_peers: Mapped[int]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'nr_of_non_r_and_e_peers': self.nr_of_non_r_and_e_peers
        }


class TrafficRatio(PresentationModel):
    """
    The TrafficRatio model represents the ratio of research and education traffic to commodity traffic for an NREN
    for a specific year.
    """
    __tablename__ = 'traffic_ratio'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    r_and_e_percentage: Mapped[Decimal]
    commodity_percentage: Mapped[Decimal]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'r_and_e_percentage': float(self.r_and_e_percentage),
            'commodity_percentage': float(self.commodity_percentage),
        }


class OpsAutomation(PresentationModel):
    """
    The OpsAutomation model represents the level of automation of operational processes by an NREN for a specific year.

    The ops_automation field describes the level of automation, and the ops_automation_specifics field provides
    additional information about the automation performed.
    """
    __tablename__ = 'ops_automation'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    ops_automation: Mapped[YesNoPlanned]
    ops_automation_specifics: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'ops_automation': self.ops_automation.value,
            'ops_automation_specifics': self.ops_automation_specifics,
        }


class NetworkFunctionVirtualisation(PresentationModel):
    """
    The NetworkFunctionVirtualisation model represents the types of network function virtualisation an NREN uses
    in a specific year.
    """
    __tablename__ = 'network_function_virtualisation'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    nfv: Mapped[YesNoPlanned]
    nfv_specifics: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'nfv': self.nfv.value,
            'nfv_specifics': self.nfv_specifics
        }


class NetworkAutomation(PresentationModel):
    """
    The NetworkAutomation model represents the level of network automation an NREN has in place for a specific year.
    """
    __tablename__ = 'network_automation'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    network_automation: Mapped[YesNoPlanned]
    network_automation_specifics: Mapped[json_str_list]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'network_automation': self.network_automation.value,
            'network_automation_specifics': self.network_automation_specifics,
        }


class Service(PresentationModel):
    """
    The Service model represents a number of services and groups them by category.

    The possible service categories are:

    .. autoenum:: compendium_v2.db.presentation_model_enums.ServiceCategory
    """
    __tablename__ = 'service'
    name_key: Mapped[str256_pk]
    name: Mapped[str256]
    category: Mapped[ServiceCategory]
    description: Mapped[str]


class NRENService(PresentationModel):
    """
    The NRENService model represents a service provided by an NREN for a specific year.

    NRENs provide information specific to the service they offer.

    """
    __tablename__ = 'nren_service'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    service_key: Mapped[str256] = mapped_column(ForeignKey("service.name_key"), primary_key=True)
    service: Mapped[Service] = relationship(lazy='joined')
    product_name: Mapped[str256]
    additional_information: Mapped[str]
    official_description: Mapped[str]

    def to_dict(self, download=False):
        return {
            **self.nren.to_dict(),
            'year': self.year,
            'service_name': self.service.name,
            'service_category': self.service.category.value,
            'service_description': self.service.description,
            'product_name': self.product_name,
            'additional_information': self.additional_information,
            'official_description': self.official_description,
        }

    def compare_dict(self):
        return {
            'nren_id': self.nren_id,
            **self.nren.to_dict(),
            'year': self.year,
            'service_key': self.service_key,
            'product_name': self.product_name,
            'additional_information': self.additional_information,
            'official_description': self.official_description,
        }