Prüfungsrücktritt

studakt_add(
	st_person_nr,  # numerische ID der Studierenden
	description,  # ID des Attests in DSSNAT
	document_type=15,  # Attestverbuchung (ATTESTVB)
	data=[
			('GRUND', 'Attest'),  # alternativ "Rücktritt mit anerkanntem Grund"
 			('BEGIN', ...),  # Beginn der Prüfungsunfähigkeit
  			('END', ...),  # Ende der Prüfungsunfähigkeit 
  			('ANMERKUNG', ...),  # optional interne Anmerkung der Schriftführung
         ],
)

class StudentAttestType(Base):
	'''StudentAttestType model representing a type of certificate for exam exceptions'''
	__tablename__ = 'students_attesttypes'
	attest_type: Mapped[str] = mapped_column(String, primary_key=True, doc='ID of the attest type')
	attest_type_group: Mapped[str | None] = mapped_column(String, nullable=False, doc='Attest types with the same attest_type_group may not conflict each other')
	sorting: Mapped[int] = mapped_column(Integer, nullable=False)
	attesttype_de: Mapped[str] = mapped_column(String, nullable=False, doc='Name of the attest type in German')
	attesttype_en: Mapped[str] = mapped_column(String, nullable=False, doc='Name of the attest type in English')
	attesttype_description_de: Mapped[str | None] = mapped_column(String, doc='Description of the attest type for student ui in German')
	attesttype_description_en: Mapped[str | None] = mapped_column(String, doc='Description of the attest type for student ui in English')
	document_type_id_tumonline: Mapped[int | None] = mapped_column(Integer, nullable=True, doc='ID of document type in TUMonline')
	attest_type_tumonline: Mapped[str | None] = mapped_column(String, nullable=True, doc='Reason of attest for document type ID')
	student_request: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=true(), doc='Whether students may create this request themselves')
	upload_required: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=true(), doc='Whether students need to upload a file')
	full_exemption: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='Whether this attest type corresponds to full exemption')
	consult_examiner: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='Whether consultation of examiner/supervisor is required by default')
	allow_future: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='Whether request for future dates is allowed')
	semester_aligned: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=true(), doc='Whether request refres to a semester only')


class StudentAttestConsultationType(Base):
	'''StudentAttestConsultationType model representing a type of consultation request to a certificate for exam exceptions'''
	__tablename__ = 'students_attest_consultationtypes'
	consultationtype_id: Mapped[str] = mapped_column(String, primary_key=True, doc='ID of the consultation type')
	consultationtype_de: Mapped[str] = mapped_column(String, nullable=False, doc='Name of the consultation typein German')
	consultationtype_en: Mapped[str] = mapped_column(String, nullable=False, doc='Name of the consultation typein English')


class StudentAttest(Versioned, Base):
	'''StudentAttest model representing a certificate for exam exceptions'''
	__tablename__ = 'students_attests'
	attest_id: Mapped[UUID] = mapped_column(postgresql.UUID(as_uuid=True), primary_key=True, server_default=text('gen_random_uuid()'), doc='ID of the attest.')
	matrikel: Mapped[str] = mapped_column(String(length=8), nullable=False, doc=Student.matrikel.__doc__)
	study_id: Mapped[str] = mapped_column(String, nullable=False, doc=Program.study_id.__doc__)
	semester_key: Mapped[SemesterKey] = mapped_column(String(length=5), ForeignKey(Semester.semester_key), nullable=False, doc='Semester of the exception.')
	attest_type: Mapped[str] = mapped_column(String, ForeignKey(StudentAttestType.attest_type), nullable=False, server_default='withdrawal_attest', doc='Type of attest record')
	valid_from: Mapped[datetime.date] = mapped_column(postgresql.DATE, server_default=func.current_date(), nullable=False, doc='This exception is valid from.')
	valid_through: Mapped[datetime.date | None] = mapped_column(postgresql.DATE, doc='This exception is valid through.')
	request_date: Mapped[datetime.datetime] = mapped_column(postgresql.TIMESTAMP(timezone=True), server_default=func.now(), doc='Timestamp of request by the student.', nullable=False)
	deny_date: Mapped[datetime.datetime | None] = mapped_column(postgresql.TIMESTAMP(timezone=True), doc='Timestamp of the denial of the request.')
	denyfinal_date: Mapped[datetime.datetime | None] = mapped_column(postgresql.TIMESTAMP(timezone=True), doc='Timestamp of the final denial of the request.')
	accept_date: Mapped[datetime.datetime | None] = mapped_column(postgresql.TIMESTAMP(timezone=True), doc='Timestamp of the acceptance of the request.')
	apv_date: Mapped[datetime.datetime | None] = mapped_column(postgresql.TIMESTAMP(timezone=True), doc='Timestamp of notification of CST/GO.')
	tumonline_document_id: Mapped[int | None] = mapped_column(Integer, doc='ID of the document in TUMonline')
	full_exemption: Mapped[bool] = mapped_column(Boolean, nullable=False, doc='Student is fully exempt from any duty to take exams.')
	extension: Mapped[Decimal | None] = mapped_column(Numeric, doc='Student should have exam time extended by this fraction.')
	remark_student: Mapped[str | None] = mapped_column(String, doc='Remark the student gave with their request.')
	remark_internal: Mapped[str | None] = mapped_column(String, doc='Internal remark for the student office.')
	remark_examiner: Mapped[str | None] = mapped_column(String, doc='Note for the examiner(s) in German.')
	remark_examiner_en: Mapped[str | None] = mapped_column(String, doc='Note for the examiner(s) in English.')
	deny_reason: Mapped[str | None] = mapped_column(String, doc='Reason for denial by student office in German.')
	deny_reason_en: Mapped[str | None] = mapped_column(String, doc='Reason for denial by student office in English.')
	gop: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='The certificate concerns GOP exams.')
	digital_copy: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='The student handed in the documents digitally.')
	specialneeds: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='The student might have a longterm condition.')
	longterm: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='The attest refers to a longterm condition.')
	accept_deficient: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=false(), doc='The attest does not fulfill the requirements but is accepted as an exception.')
	repeat_mode: Mapped[str | None] = mapped_column(String, doc='When the exams need to be repeated')
	additional_note: Mapped[str | None] = mapped_column(String, doc='Additional note to the student in German')
	additional_note_en: Mapped[str | None] = mapped_column(String, doc='Additional note to the student in English')
	modified_username: Mapped[TUMKennung | None] = mapped_column(String(7), ForeignKey(PersonLocal.username, onupdate='CASCADE'), doc='Username of the last modification.')
	modified: Mapped[datetime.datetime] = mapped_column(postgresql.TIMESTAMP(timezone=True), server_default=func.now(), doc='Timestamp of last modification of the database entry.', nullable=False)
	program: Mapped[ProgramCombined] = relationship(ProgramCombined, foreign_keys=[study_id], primaryjoin=study_id==ProgramCombined.study_id, uselist=False, viewonly=True)
	semester: Mapped[Semester] = relationship(Semester)
	attesttype: Mapped[StudentAttestType] = relationship(StudentAttestType)
	modified_user: Mapped[Person | None] = relationship(Person, foreign_keys=[modified_username], viewonly=True)
	theses: Mapped[list[Thesis]] = relationship(Thesis,
			foreign_keys=[matrikel, study_id, valid_from, valid_through],
			secondary=StatisticsStudent.__table__,
			secondaryjoin=(StatisticsStudent.__table__.c.matrikel==matrikel)&(Thesis.student_username==StatisticsStudent.__table__.c.username),
			primaryjoin=(StatisticsStudent.__table__.c.matrikel==matrikel)&(Thesis.student_username==StatisticsStudent.__table__.c.username)&(Thesis.study_id==study_id)&(Thesis.start_date<=valid_through)&(func.coalesce(Thesis.cancel_date, Thesis.end_date, Thesis.due_date)>=valid_from),
			backref=backref('attests', order_by='StudentAttest.valid_from'),
			order_by=Thesis.start_date,
			viewonly=True, uselist=True)
	consultations: Mapped[list[StudentAttestConsultation]] = relationship('StudentAttestConsultation', back_populates='attest')

	@hybrid_property
	def open_consultation_count(self) -> int:
		return sum(1 for e in self.consultations if e.answer_date is None)

	@open_consultation_count.inplace.expression
	@classmethod
	def _open_consultation_count_expression(cls) -> ColumnElement[int]:
		return select(func.count()).where((StudentAttestConsultation.attest_id==cls.attest_id)&(StudentAttestConsultation.answer_date==None)).correlate_except(StudentAttestConsultation).scalar_subquery()


class StudentAttestConsultation(Base):
	'''StudentAttestConsultation model representing a consultation request to a certificate for exam exceptions'''
	__tablename__ = 'students_attest_consultations'
	attest_id: Mapped[UUID] = mapped_column(postgresql.UUID(as_uuid=True), ForeignKey(StudentAttest.attest_id), nullable=False, doc=StudentAttest.attest_id.__doc__)
	consultationtype_id: Mapped[str] = mapped_column(String, ForeignKey(StudentAttestConsultationType.consultationtype_id), nullable=False, doc=StudentAttestConsultationType.consultationtype_id.__doc__)
	consultation_id: Mapped[UUID] = mapped_column(postgresql.UUID(as_uuid=True), primary_key=True, server_default=text('gen_random_uuid()'), doc='Sequence number of the consultation')
	consulted_username: Mapped[TUMKennung | None] = mapped_column(String(7), ForeignKey(PersonLocal.username, onupdate='CASCADE'), doc='Username of the consulted.')
	consulted_team_id: Mapped[str | None] = mapped_column(String, ForeignKey(ProgramTeam.team_id, onupdate='CASCADE'), doc='ID of the consulted team.')
	request_de: Mapped[str | None] = mapped_column(String, doc='Additional note in the consultation request in German')
	request_en: Mapped[str | None] = mapped_column(String, doc='Additional note in the consultation request in English')
	request_date: Mapped[datetime.datetime] = mapped_column(postgresql.TIMESTAMP(timezone=True), server_default=func.now(), doc='Date of the request')
	answer_de: Mapped[str | None] = mapped_column(String, doc='Additional note in the answer in German')
	answer_en: Mapped[str | None] = mapped_column(String, doc='Additional note in the answer in English')
	answer_date: Mapped[datetime.datetime | None] = mapped_column(postgresql.TIMESTAMP(timezone=True), doc='Date of the answer')
	approved: Mapped[bool | None] = mapped_column(Boolean, doc='Whether the request was approved')
	consultationtype: Mapped[StudentAttestConsultationType] = relationship(StudentAttestConsultationType, foreign_keys=[consultationtype_id])
	modified_username: Mapped[TUMKennung | None] = mapped_column(String(7), ForeignKey(PersonLocal.username, onupdate='CASCADE'), doc='Username of the last modification.')
	modified: Mapped[datetime.datetime] = mapped_column(postgresql.TIMESTAMP(timezone=True), server_default=func.now(), doc='Timestamp of last modification of the database entry.', nullable=False)
	__table_args__ = (CheckConstraint((consulted_username != None)|(consulted_team_id != None), name='consulted_username_or_consulted_team_id'), {})
	consulted_user: Mapped[Person | None] = relationship(Person, foreign_keys=[consulted_username], viewonly=True)
	consulted_team: Mapped[ProgramTeam | None] = relationship(ProgramTeam, foreign_keys=[consulted_team_id], viewonly=True)
	modified_user: Mapped[Person | None] = relationship(Person, foreign_keys=[modified_username], viewonly=True)
	attest: Mapped[StudentAttest] = relationship(StudentAttest, back_populates='consultations')