model.py

import logging
from decimal import Decimal
from enum import Enum
from typing import Optional
from typing_extensions import Annotated

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


logger = logging.getLogger(__name__)

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata_obj = MetaData(naming_convention=convention)

str128 = Annotated[str, 128]
int_pk = Annotated[int, mapped_column(primary_key=True)]
int_pk_fkNREN = Annotated[int, mapped_column(ForeignKey("nren.id"), primary_key=True)]


class Base(DeclarativeBase):
    metadata = metadata_obj
    type_annotation_map = {
        str128: String(128),
    }


class NREN(Base):
    __tablename__ = 'nren'
    id: Mapped[int_pk]
    name: Mapped[str128]


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


class FundingSource(Base):
    __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 FeeType(Enum):
    flat_fee = "flat_fee"
    usage_based_fee = "usage_based_fee"
    combination = "combination"
    no_charge = "no_charge"
    other = "other"


class ChargingStructure(Base):
    __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(Base):
    __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(Base):
    __tablename__ = 'parent_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str128]


class SubOrganization(Base):
    __tablename__ = 'sub_organization'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(lazy='joined')
    year: Mapped[int_pk]
    organization: Mapped[str128] = mapped_column(primary_key=True)
    role: Mapped[str128]


class ECProject(Base):
    __tablename__ = 'ec_project'
    nren_id: Mapped[int_pk_fkNREN]
    nren: Mapped[NREN] = relationship(NREN, lazy='joined')
    year: Mapped[int_pk]
    project: Mapped[str] = mapped_column(String(256), primary_key=True)