ki-backend/ki/models.py

# SPDX-FileCopyrightText: WTF Kooperative eG <https://wtf-eg.de/>
#
# SPDX-License-Identifier: AGPL-3.0-or-later

from datetime import datetime

from sqlalchemy import Boolean, Column, Integer, SmallInteger, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship

from app import db


class User(db.Model):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    auth_id = Column(String(50), nullable=False, unique=True)
    profile_id = Column(Integer, ForeignKey("profile.id"), nullable=True)

    tokens = relationship("Token", back_populates="user")
    profile = relationship("Profile", back_populates="user")

    def to_dict(self):
        return {"id": self.id}


class Profile(db.Model):
    __tablename__ = "profile"

    id = Column(Integer, primary_key=True)
    nickname = Column(String(25), unique=True, nullable=False)
    pronouns = Column(String(25), default="")
    volunteerwork = Column(String(4000), default="")
    freetext = Column(String(4000), default="")

    availability_status = Column(Boolean, default=False)
    availability_text = Column(String(4000), default="")
    availability_hours_per_week = Column(Integer, default=0)

    visible = Column(Boolean, nullable=False, default=False)
    created = Column(DateTime, nullable=False, default=datetime.now)
    updated = Column(DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)

    user = relationship("User", back_populates="profile", uselist=False)
    contacts = relationship("Contact")
    address = relationship("Address", uselist=False, back_populates="profile")
    skills = relationship("ProfileSkill", back_populates="profile")
    searchtopics = relationship("ProfileSearchtopic", back_populates="profile")
    languages = relationship("ProfileLanguage", back_populates="profile")

    def to_dict(self):
        return {
            "user_id": self.user.id,
            "nickname": self.nickname,
            "pronouns": self.pronouns,
            "volunteerwork": self.volunteerwork,
            "availability_status": self.availability_status,
            "availability_text": self.availability_text,
            "availability_hours_per_week": self.availability_hours_per_week,
            "freetext": self.freetext,
            "visible": self.visible,
            "address": self.address.to_dict() if self.address else None,
            "contacts": list(map(lambda contact: contact.to_dict(), self.contacts)),
            "skills": list(map(lambda skill: skill.to_dict(), self.skills)),
            "searchtopics": list(map(lambda searchtopic: searchtopic.to_dict(), self.searchtopics)),
            "languages": list(map(lambda language: language.to_dict(), self.languages))
        }


class Token(db.Model):
    __tablename__ = "token"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)
    token = Column(String(36), nullable=False)

    user = relationship("User", back_populates="tokens")

    def to_dict(self):
        return {"user_id": self.user_id, "token": self.token}


class Contact(db.Model):
    __tablename__ = "contact"

    id = Column(Integer, primary_key=True)
    profile_id = Column(Integer, ForeignKey("profile.id"), nullable=False)
    profile = relationship("Profile", back_populates="contacts")
    contacttype_id = Column(Integer, ForeignKey("contacttype.id"), nullable=False)
    contacttype = relationship("ContactType")
    content = Column(String(200), nullable=False)

    def to_dict(self):
        return {
            "id": self.id,
            "profile_id": self.profile_id,
            "contacttype": self.contacttype.to_dict(),
            "content": self.content
        }


class ContactType(db.Model):
    __tablename__ = "contacttype"

    id = Column(Integer, primary_key=True)
    name = Column(String(25), nullable=False)

    def to_dict(self):
        return {"id": self.id, "name": self.name}


class Address(db.Model):
    __tablename__ = "address"

    id = Column(Integer, primary_key=True)
    name = Column(String(25), default="")
    street = Column(String(25), default="")
    house_number = Column(String(10), default="")
    additional = Column(String(25), default="")
    postcode = Column(String(10), default="")
    city = Column(String(25), default="")
    country = Column(String(25), default="")

    profile_id = Column(Integer, ForeignKey("profile.id"), nullable=False)
    profile = relationship("Profile", back_populates="address")

    def to_dict(self):
        return {
            "id": self.id,
            "name": self.name,
            "street": self.street,
            "house_number": self.house_number,
            "additional": self.additional,
            "postcode": self.postcode,
            "city": self.city,
            "country": self.country,
            "profile_id": self.profile_id
        }


class Skill(db.Model):
    skill_id_php = 1
    skill_id_python = 3
    skill_id_sqlalchemy = 7
    skill_id_mysql = 9
    skill_id_postgresql = 10
    skill_id_sqlite = 11

    __tablename__ = "skill"

    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    profiles = relationship("ProfileSkill", back_populates="skill")
    searchtopics = relationship("ProfileSearchtopic", back_populates="skill")

    def to_dict(self):
        return {"id": self.id, "name": self.name, "icon_url": "/skills/{}/icon".format(self.id)}


class ProfileSkill(db.Model):
    level1_text = "bis 6 Monate"
    level2_text = "bis 1 Jahr"
    level3_text = "bis 3 Jahre"
    level4_text = "bis 5 Jahre"
    level5_text = "mehr als 5 Jahre"
    level_texts = {
        1: level1_text,
        2: level2_text,
        3: level3_text,
        4: level4_text,
        5: level5_text,
    }

    __tablename__ = "profile_skill"

    profile_id = Column(Integer, ForeignKey("profile.id"), primary_key=True)
    skill_id = Column(Integer, ForeignKey("skill.id"), primary_key=True)
    level = Column(SmallInteger, nullable=False)

    profile = relationship("Profile", back_populates="skills")
    skill = relationship("Skill", back_populates="profiles")

    def to_dict(self):
        return {"profile_id": self.profile_id, "skill": self.skill.to_dict(), "level": self.level}


class ProfileSearchtopic(db.Model):
    __tablename__ = "profile_searchtopic"

    profile_id = Column(Integer, ForeignKey("profile.id"), primary_key=True)
    skill_id = Column(Integer, ForeignKey("skill.id"), primary_key=True)

    profile = relationship("Profile", back_populates="searchtopics")
    skill = relationship("Skill", back_populates="searchtopics")

    def to_dict(self):
        return {"profile_id": self.profile_id, "skill": self.skill.to_dict()}


class Language(db.Model):
    __tablename__ = "language"

    id = Column(String(4), primary_key=True)
    name = Column(String(100), nullable=False)

    profiles = relationship("ProfileLanguage", back_populates="language")

    def to_dict(self):
        return {"id": self.id, "name": self.name, "icon_url": "/languages/{}/icon".format(self.id)}


class ProfileLanguage(db.Model):
    level1_text = "keine Angabe"
    level2_text = "Grundkenntnisse"
    level3_text = "Gut"
    level4_text = "Fließend"
    level5_text = "Muttersprache"
    level_texts = {
        1: level1_text,
        2: level2_text,
        3: level3_text,
        4: level4_text,
        5: level5_text,
    }

    __tablename__ = "profile_language"

    profile_id = Column(Integer, ForeignKey("profile.id"), primary_key=True)
    language_id = Column(String(4), ForeignKey("language.id"), primary_key=True)
    level = Column(SmallInteger, nullable=False)

    profile = relationship("Profile", back_populates="languages")
    language = relationship("Language", back_populates="profiles")

    def to_dict(self):
        return {"profile_id": self.profile_id, "language": self.language.to_dict(), "level": self.level}