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, CommarcialChargingLevel, UserCategory, \
    ConnectivityCoverage, ConnectionMethod, YesNoPlanned, MonitoringMethod, CommercialConnectivityCoverage, \
    FeeType, ServiceCategory


logger = logging.getLogger(__name__)


str128 = Annotated[str, 128]
str128_pk = Annotated[str, mapped_column(String(128), primary_key=True)]
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 PreviewYear(db.Model):
    __tablename__ = 'preview_year'
    year: Mapped[int_pk]


class NREN(db.Model):
    __tablename__ = 'nren'
    id: Mapped[int_pk]
    name: Mapped[str128]
    country: Mapped[str128]


class BudgetEntry(db.Model):
    __tablename__ = 'budgets'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    budget: Mapped[Decimal]


class FundingSource(db.Model):
    __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]


class ChargingStructure(db.Model):
    __tablename__ = 'charging_structure'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    fee_type: Mapped[Optional[FeeType]]


class NrenStaff(db.Model):
    __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]


class ParentOrganization(db.Model):
    __tablename__ = 'parent_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str128]


class SubOrganization(db.Model):
    __tablename__ = 'sub_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str128_pk]
    role: Mapped[str128]


class ECProject(db.Model):
    __tablename__ = 'ec_project'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    project: Mapped[str256_pk]


class Policy(db.Model):
    __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]


class TrafficVolume(db.Model):
    __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]


class InstitutionURLs(db.Model):
    __tablename__ = 'institution_urls'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    urls: Mapped[json_str_list]


class CentralProcurement(db.Model):
    __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]]


class ServiceManagement(db.Model):
    __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]]


class ServiceUserTypes(db.Model):
    __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]


class EOSCListings(db.Model):
    __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]


class Standards(db.Model):
    __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]]


class CrisisExcercises(db.Model):
    __tablename__ = 'crisis_excercises'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    exercise_descriptions: Mapped[json_str_list]


class SecurityControls(db.Model):
    __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]


class ConnectedProportion(db.Model):
    __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]]


class ConnectivityLevel(db.Model):
    __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]]


class ConnectionCarrier(db.Model):
    __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]


class ConnectivityLoad(db.Model):
    __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]]


class ConnectivityGrowth(db.Model):
    __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]


class CommercialConnectivity(db.Model):
    __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]]


class CommercialChargingLevel(db.Model):
    __tablename__ = 'commercial_charging_level'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    collaboration: Mapped[Optional[CommarcialChargingLevel]]
    service_supplier: Mapped[Optional[CommarcialChargingLevel]]
    direct_peering: Mapped[Optional[CommarcialChargingLevel]]


class RemoteCampuses(db.Model):
    __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)


class DarkFibreLease(db.Model):
    __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]]


class DarkFibreInstalled(db.Model):
    __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]]


class FibreLight(db.Model):
    __tablename__ = 'fibre_light'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    light_description: Mapped[str]


class NetworkMapUrls(db.Model):
    __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]


class MonitoringTools(db.Model):
    __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]


class PassiveMonitoring(db.Model):
    __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]]


class TrafficStatistics(db.Model):
    __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]


class SiemVendors(db.Model):
    __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]


class CertificateProviders(db.Model):
    __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]


class WeatherMap(db.Model):
    __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]


class PertTeam(db.Model):
    __tablename__ = 'pert_team'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    pert_team: Mapped[YesNoPlanned]


class AlienWave(db.Model):
    __tablename__ = 'alien_wave'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    alien_wave_third_pary: Mapped[Optional[YesNoPlanned]]
    nr_of_alien_wave_third_party_services: Mapped[Optional[int]]
    alien_wave_internal: Mapped[Optional[bool]]


class Capacity(db.Model):
    __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]]


class ExternalConnections(db.Model):
    __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)


class NonREPeers(db.Model):
    __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]


class TrafficRatio(db.Model):
    __tablename__ = 'traffic_ratio'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    r_and_e_percentage: Mapped[int]
    commodity_percentage: Mapped[int]


class OpsAutomation(db.Model):
    __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]


class NetworkFunctionVirtualisation(db.Model):
    __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]


class NetworkAutomation(db.Model):
    __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]