Notification endpoint now filters for participant_id and fixed parsing of event type enumeration array

Release pool connection handle und all circumstances especially also when a query fails
before the call is finished. This should avoid connection starvation.

fix prod. link

production fix

Fixed application path

.vscode/launch.json

@@ -12,7 +12,8 @@
             "env": {
                 "FLASK_APP": "src/server/BreCal",
                 "FLASK_DEBUG": "1",
-                "SECRET_KEY" : "zdiTz8P3jXOc7jztIQAoelK4zztyuCpJ" // https://randomkeygen.com/
+                "SECRET_KEY" : "zdiTz8P3jXOc7jztIQAoelK4zztyuCpJ", // https://randomkeygen.com/
+                "FLASK_RUN_PORT": "5000"
             },
             "args": [
                 "run",

misc/BreCalApi.md

@@ -0,0 +1,537 @@
+# Bremen calling API
+Version: _1.7.0_
+Last change: _Nov 14, 2025_
+
+## Introduction
+
+This API allows users to interact with "Bremen calling" without an UI. Apart vom querying data via _GET_ endpoints users may create and update shipcalls, assign participants and update participant times for shipcalls. 
+
+Creating and updating times and shipcalls depend on the participant roles a user is assigned to. For example, if a participant has the role "AGENCY" they may change assignments _and_ create and update agency times. A participant with the role "PILOT" on the other hand may not change the assigments and only create/update times for the pilot.
+
+### Authentication
+- **ApiKey**: API key in `header` header named `Authorization`. This is a JWT Token that the caller receives upon login.
+
+### Notes on this version
+
+This version refers to _1.7_ whereas the public client currently has version _1.6_. This means that there is some functionality available in the API that cannot be accessed through the UI yet, specifically notifications.
+
+There is no documentation for the structures returned by _GET_ requests but these can easily be determined via a single query.
+
+## Ship Endpoints
+
+### `DELETE /ships`
+**Summary:** Delete a ship (logically).
+A ship can only be logically deleted, since it is possible to have been used in previous shipcalls. On logical delete, the ship can no longer be selected in a new ship call.
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| id | query | integer | Yes | **Id of ship**. *Example: 42*. Id of ship to be deleted. |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `GET /ships`
+**Summary:** gets a list of ships
+Gets a list of ships including logically deleted ships to be used with shipcalls
+
+#### Responses
+
+- **200**: list of ships
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `POST /ships`
+**Summary:** create a new ship entry
+adds a new non-existing ship to the database. The ships IMO number is the unique identifier.
+
+#### Request Body
+Ship details. **Do not** provide id parameter.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| name | string | No |  |
+| imo | integer | No |  |
+| callsign | string | No |  |
+| participant_id | integer | No | Optional reference to participant (tug role) |
+| length | number | No |  |
+| width | number | No |  |
+| is_tug | boolean | No |  |
+| bollard_pull | integer | No |  |
+| eni | integer | No | BSMD internal use |
+| created | string | No | Readonly field set by the database when ship was created |
+| modified | string | No | Readonly field set by the database when ship was last modified |
+| deleted | boolean | No | marks the ship as logically deleted |
+
+#### Responses
+
+- **201**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `PUT /ships`
+**Summary:** Update a ship entry
+Updating a ship entry. Please do not modify the IMO number. In that case please add a new entry.
+
+#### Request Body
+Updated ship entry. The id parameter is **required**.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| name | string | No |  |
+| imo | integer | No |  |
+| callsign | string | No |  |
+| participant_id | integer | No | Optional reference to participant (tug role) |
+| length | number | No |  |
+| width | number | No |  |
+| is_tug | boolean | No |  |
+| bollard_pull | integer | No |  |
+| eni | integer | No | BSMD internal use |
+| created | string | No | Readonly field set by the database when ship was created |
+| modified | string | No | Readonly field set by the database when ship was last modified |
+| deleted | boolean | No | marks the ship as logically deleted |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+## Shipcall Endpoints
+
+### `GET /shipcalls`
+**Summary:** Gets a list of ship calls
+Get current ship calls
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| past_days | query | integer | No | number of days in the past to include in the result. *Example: 7*. |
+
+#### Responses
+
+- **200**: ship call list
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `POST /shipcalls`
+**Summary:** Create a new ship call
+A new shipcall is created without times at this point. This is ususally done by the BSMD or a participant with that particular role.
+
+#### Request Body
+Creates a new ship call. **Do not** provide id parameter.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| ship_id | integer | Yes |  |
+| port_id | integer | No |  |
+| type | string | Yes | Type of ship call |
+| eta | string | No |  |
+| voyage | string | No |  |
+| etd | string | No |  |
+| arrival_berth_id | integer | No |  |
+| departure_berth_id | integer | No |  |
+| tug_required | boolean | No |  |
+| pilot_required | boolean | No |  |
+| flags | integer | No |  |
+| pier_side | boolean | No |  |
+| bunkering | boolean | No |  |
+| replenishing_terminal | boolean | No |  |
+| replenishing_lock | boolean | No |  |
+| draft | number | No |  |
+| tidal_window_from | string | No |  |
+| tidal_window_to | string | No |  |
+| rain_sensitive_cargo | boolean | No |  |
+| recommended_tugs | integer | No |  |
+| anchored | boolean | No |  |
+| moored_lock | boolean | No |  |
+| canceled | boolean | No |  |
+| evaluation | string | No | Evaluation of the ship call |
+| evaluation_message | string | No |  |
+| time_ref_point | integer | No | Physical reference point for all times given in shipcall and depending times entries |
+| participants | array<object> | No |  |
+| created | string | No | Readonly field set by the database when shipcall was created |
+| modified | string | No | Readonly field set by the database when shipcall was last modified |
+
+#### Responses
+
+- **201**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `PUT /shipcalls`
+**Summary:** Updates a ship call
+Updates a shipcall. Usually done if the participant assignments change.
+
+#### Request Body
+Creates a new ship call. The id parameter is **required**.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| ship_id | integer | Yes |  |
+| port_id | integer | No |  |
+| type | string | Yes | Type of ship call |
+| eta | string | No |  |
+| voyage | string | No |  |
+| etd | string | No |  |
+| arrival_berth_id | integer | No |  |
+| departure_berth_id | integer | No |  |
+| tug_required | boolean | No |  |
+| pilot_required | boolean | No |  |
+| flags | integer | No |  |
+| pier_side | boolean | No |  |
+| bunkering | boolean | No |  |
+| replenishing_terminal | boolean | No |  |
+| replenishing_lock | boolean | No |  |
+| draft | number | No |  |
+| tidal_window_from | string | No |  |
+| tidal_window_to | string | No |  |
+| rain_sensitive_cargo | boolean | No |  |
+| recommended_tugs | integer | No |  |
+| anchored | boolean | No |  |
+| moored_lock | boolean | No |  |
+| canceled | boolean | No |  |
+| evaluation | string | No | Evaluation of the ship call |
+| evaluation_message | string | No |  |
+| time_ref_point | integer | No | Physical reference point for all times given in shipcall and depending times entries |
+| participants | array<object> | No |  |
+| created | string | No | Readonly field set by the database when shipcall was created |
+| modified | string | No | Readonly field set by the database when shipcall was last modified |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+## Static Endpoints
+
+### `GET /berths`
+**Summary:** Gets a list of all berths registered
+Returns a list of berths, including berths that are (logically) deleted
+
+#### Responses
+
+- **200**: list of berths
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `GET /history`
+**Summary:** History data
+This endpoint returns a list of changes made to the specific shipcall
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| shipcall_id | query | integer | Yes | **Id of ship call**. *Example: 3*. Id given in ship call list |
+
+#### Responses
+
+- **200**: list of history entries
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `GET /notifications`
+**Summary:** Gets a list of notifications pursuant to a specified participant and ship call
+List of notifications (tbd)
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| shipcall_id | query | integer | No | **Id of ship call**. *Example: 52*. Id given in ship call list |
+
+#### Responses
+
+- **200**: notification list
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `GET /participants`
+**Summary:** gets one or all participants
+If no parameter is given, all participants are returned. The list can be used to display participant information in the context of ship calls.
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| user_id | query | integer | No | **Id of user**. *Example: 2*. User id returned by verify call. |
+
+#### Responses
+
+- **200**: one or all participants as list
+- **400**
+- **401**
+- **404**
+- **500**
+- **503**
+---
+
+### `GET /ports`
+**Summary:** Your GET endpoint
+Returns a list of ports
+
+#### Responses
+
+- **200**: list of ports
+- **401**
+- **403**
+- **500**
+- **503**
+---
+
+## Times Endpoints
+
+### `DELETE /times`
+**Summary:** Delete a times entry for a ship call.
+A times entry is typically deleted if the agent for example changes or removes the participant assignment for a particular role.
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| id | query | integer | Yes | **Id of times**. *Example: 42*. Id of times entry to be deleted. |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `GET /times`
+**Summary:** Gets list of times
+Get all times assigned to a shipcall. These might not be complete.
+
+#### Parameters
+
+| Name | In | Type | Required | Description |
+|------|----|------|----------|-------------|
+| shipcall_id | query | integer | No | **Id**. *Example: 42*. Id of referenced ship call. |
+
+#### Responses
+
+- **200**: list of recorded times
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `POST /times`
+**Summary:** Create a new times entry for a ship call
+The times entry for a shipcall is created with reference to a participant. For each participant type there should be only one times data record.
+
+#### Request Body
+Times entry that will be added to the ship call. **Do not** provide id parameter.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| eta_berth | string | No | Arrival time at berth |
+| eta_berth_fixed | boolean | No | If true, the eta is fixed and cannot be changed |
+| etd_berth | string | No | departure time from berth |
+| etd_berth_fixed | boolean | No | If true, the etd is fixed and cannot be changed |
+| lock_time | string | No | arrival time at lock |
+| lock_time_fixed | boolean | No | If true, the lock time is fixed and cannot be changed |
+| zone_entry | string | No | Expected time of entry into the zone |
+| zone_entry_fixed | boolean | No | If true, the zone entry time is fixed and cannot be changed |
+| operations_start | string | No | Start time for terminal operations |
+| operations_end | string | No | End time for terminal operations |
+| remarks | string | No | Additional remarks |
+| shipcall_id | integer | Yes | Reference to a shipcall id |
+| participant_id | integer | Yes | Reference to a participant id |
+| berth_id | integer | No | Reference to a berth id |
+| berth_info | string | No | Additional info text for berth |
+| pier_side | boolean | No | true if ship is rotated, false otherwise |
+| participant_type | integer | No |  |
+| ata | string | No | ata can be set by mooring if actual times are different from planned |
+| atd | string | No | atd can be set by mooring if actual times are different from planned |
+| eta_interval_end | string | No | Optional end of the interval for the times eta entry |
+| etd_interval_end | string | No | Optional end of the interval for the times etd entry |
+| created | string | No | Readonly field set by the database when times record was created |
+| modified | string | No | Readonly field set by the database when times record was last modified |
+
+#### Responses
+
+- **201**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+### `PUT /times`
+**Summary:** Update a times entry for a ship call
+Updating a times entry for a ship for a particular participant. The times entries are required for a shipcall to pass the validation rules.
+
+#### Request Body
+Times entry that will be added to the ship call. The id parameter is **required**.
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| eta_berth | string | No | Arrival time at berth |
+| eta_berth_fixed | boolean | No | If true, the eta is fixed and cannot be changed |
+| etd_berth | string | No | departure time from berth |
+| etd_berth_fixed | boolean | No | If true, the etd is fixed and cannot be changed |
+| lock_time | string | No | arrival time at lock |
+| lock_time_fixed | boolean | No | If true, the lock time is fixed and cannot be changed |
+| zone_entry | string | No | Expected time of entry into the zone |
+| zone_entry_fixed | boolean | No | If true, the zone entry time is fixed and cannot be changed |
+| operations_start | string | No | Start time for terminal operations |
+| operations_end | string | No | End time for terminal operations |
+| remarks | string | No | Additional remarks |
+| shipcall_id | integer | Yes | Reference to a shipcall id |
+| participant_id | integer | Yes | Reference to a participant id |
+| berth_id | integer | No | Reference to a berth id |
+| berth_info | string | No | Additional info text for berth |
+| pier_side | boolean | No | true if ship is rotated, false otherwise |
+| participant_type | integer | No |  |
+| ata | string | No | ata can be set by mooring if actual times are different from planned |
+| atd | string | No | atd can be set by mooring if actual times are different from planned |
+| eta_interval_end | string | No | Optional end of the interval for the times eta entry |
+| etd_interval_end | string | No | Optional end of the interval for the times etd entry |
+| created | string | No | Readonly field set by the database when times record was created |
+| modified | string | No | Readonly field set by the database when times record was last modified |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---
+
+## User Endpoints
+
+### `POST /login`
+**Summary:** Returns a JWT session token and user data if successful
+Perform login
+
+#### Request Body
+Login credentials
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| username | string | Yes |  |
+| password | string | Yes |  |
+
+#### Responses
+
+- **200**: Successful response
+- **400**
+- **403**
+- **500**
+- **503**
+
+**Response 200 JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| participant_id | integer | No |  |
+| first_name | string | No |  |
+| last_name | string | No |  |
+| user_name | string | No |  |
+| user_phone | string | No |  |
+| user_email | string | No |  |
+| notify_email | boolean | No |  |
+| notify_whatsapp | boolean | No |  |
+| notify_signal | boolean | No |  |
+| notify_popup | boolean | No |  |
+| exp | number | No |  |
+| token | string | No |  |
+| notify_on | array<string> | No |  |
+
+---
+
+### `PUT /user`
+**Summary:** Update user details (first/last name, phone, password)
+Update user information
+
+#### Request Body
+User details
+
+**JSON Schema**
+
+| Field | Type | Required | Description |
+|-------|------|----------|-------------|
+| id | integer | No |  |
+| old_password | string | No |  |
+| new_password | string | No |  |
+| first_name | string | No |  |
+| last_name | string | No |  |
+| user_phone | string | No |  |
+| user_email | string | No |  |
+| notify_email | boolean | No |  |
+| notify_popup | boolean | No |  |
+| notify_whatsapp | boolean | No |  |
+| notify_signal | boolean | No |  |
+| notify_on | array<string> | No |  |
+
+#### Responses
+
+- **200**
+- **400**
+- **401**
+- **500**
+- **503**
+---

misc/BreCalApi.yaml

@@ -22,6 +22,8 @@ tags:
   - name: times
   - name: static
   - name: ship
+  - name: notification
+  - name: history
 paths:
   /login:
     post:
@@ -30,6 +32,7 @@ paths:
       tags:
         - user
       operationId: login
+      security: []
       requestBody:
         description: Login credentials
         required: true
@@ -256,7 +259,7 @@ paths:
         - shipcall
       operationId: shipcallUpdate
       requestBody:
-        description: Creates a new ship call. The id parameter is **required**.
+        description: Updates a ship call. The id parameter is **required**.
         required: true
         content:
           application/json:
@@ -443,7 +446,7 @@ paths:
         - name: user_id
           in: query
           required: false
-          description: '**Id of user**. *Example: 2*. User id returned by verify call.'
+          description: '**Id of user**. *Example: 2*. User id returned by login call.'
           schema:
             type: integer
             example: 2
@@ -581,7 +584,7 @@ paths:
         - times
       operationId: timesUpdate
       requestBody:
-        description: Times entry that will be added to the ship call. The id parameter is **required**.
+        description: Times entry that will be updated for the ship call. The id parameter is **required**.
         required: true
         content:
           application/json:
@@ -649,18 +652,18 @@ paths:
           $ref: '#/components/responses/503'
   /notifications:
     get:
-      summary: Gets a list of notifications pursuant to a specified participant and ship call
+      summary: Gets a list of notifications pursuant to a specified ship call
       description: List of notifications (tbd)
       tags:
-        - static
+        - notification
       operationId: notificationsGet
       parameters:
-        - name: shipcall_id
+        - name: participant_id
           in: query
           required: false
-          description: '**Id of ship call**. *Example: 52*. Id given in ship call list'
+          description: '**Id of participant**. *Example: 7*. Id of logged in participant.'
           schema:
-            $ref: '#/components/schemas/shipcallId'
+            $ref: '#/components/schemas/participant_id'
       responses:
         '200':
           description: notification list
@@ -723,7 +726,7 @@ paths:
       description: This endpoint returns a list of changes made to the specific shipcall
       summary: History data
       tags:
-        - static
+        - history
       operationId: historyGet
       parameters:
         - name: shipcall_id
@@ -1756,10 +1759,11 @@ components:
         token: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c
     user_details:
       type: object
-      description: fields that a user may change
+      description: user metadata and editable fields
       properties:
         id:
           type: integer
+          readOnly: true
           example: 42
         old_password:
           type: string

misc/Readme.md

@@ -48,3 +48,156 @@ DROP TABLE IF EXISTS `shipcall`;
 SET FOREIGN_KEY_CHECKS = 1;
 ```
 
+## Schema
+
+```mermaid
+erDiagram
+    participant {
+        INT id PK
+        VARCHAR name
+        VARCHAR street
+        VARCHAR postal_code
+        VARCHAR city
+        INT type
+        INT flags
+    }
+    port {
+        INT id PK
+        VARCHAR name
+        CHAR locode
+    }
+    berth {
+        INT id PK
+        VARCHAR name
+        BIT lock
+        INT owner_id FK
+        INT authority_id FK
+        INT port_id FK
+        BIT deleted
+    }
+    ship {
+        INT id PK
+        VARCHAR name
+        INT imo
+        VARCHAR callsign
+        INT participant_id FK
+        FLOAT length
+        FLOAT width
+        BIT is_tug
+        INT bollard_pull
+        INT eni
+        BIT deleted
+    }
+    shipcall {
+        INT id PK
+        INT ship_id FK
+        TINYINT type
+        DATETIME eta
+        DATETIME etd
+        INT arrival_berth_id FK
+        INT departure_berth_id FK
+        INT port_id FK
+        INT flags
+        BIT tug_required
+        BIT pilot_required
+    }
+    times {
+        INT id PK
+        INT shipcall_id FK
+        INT participant_id FK
+        INT berth_id FK
+        INT participant_type
+        DATETIME eta_berth
+        DATETIME etd_berth
+        DATETIME lock_time
+        DATETIME zone_entry
+    }
+    notification {
+        INT id PK
+        INT shipcall_id FK
+        INT participant_id FK
+        TINYINT level
+        TINYINT type
+    }
+    history {
+        INT id PK
+        INT participant_id FK
+        INT user_id FK
+        INT shipcall_id FK
+        DATETIME timestamp
+        DATETIME eta
+        INT type
+        INT operation
+    }
+    shipcall_participant_map {
+        INT id PK
+        INT shipcall_id FK
+        INT participant_id FK
+        INT type
+    }
+    shipcall_tug_map {
+        INT id PK
+        INT shipcall_id FK
+        INT ship_id FK
+    }
+    participant_port_map {
+        INT id PK
+        INT participant_id FK
+        INT port_id FK
+    }
+    user {
+        INT id PK
+        INT participant_id FK
+        VARCHAR first_name
+        VARCHAR last_name
+        VARCHAR user_name
+        VARCHAR user_email
+    }
+    role {
+        INT id PK
+        VARCHAR name
+        VARCHAR description
+    }
+    securable {
+        INT id PK
+        VARCHAR name
+    }
+    role_securable_map {
+        INT id PK
+        INT role_id FK
+        INT securable_id FK
+    }
+    user_role_map {
+        INT id PK
+        INT user_id FK
+        INT role_id FK
+    }
+
+    participant ||--o{ berth : owner_id
+    participant ||--o{ berth : authority_id
+    port ||--o{ berth : port_id
+    participant ||--o{ ship : participant_id
+    ship ||--o{ shipcall : ship_id
+    berth ||--o{ shipcall : arrival_berth_id
+    berth ||--o{ shipcall : departure_berth_id
+    port ||--o{ shipcall : port_id
+    shipcall ||--|| times : shipcall_id
+    participant ||--|| times : participant_id
+    berth ||--o{ times : berth_id
+    shipcall ||--o{ notification : shipcall_id
+    participant ||--o{ notification : participant_id
+    participant ||--o{ history : participant_id
+    user ||--o{ history : user_id
+    shipcall ||--o{ history : shipcall_id
+    shipcall ||--o{ shipcall_participant_map : shipcall_id
+    participant ||--o{ shipcall_participant_map : participant_id
+    shipcall ||--o{ shipcall_tug_map : shipcall_id
+    ship ||--o{ shipcall_tug_map : ship_id
+    participant ||--o{ participant_port_map : participant_id
+    port ||--o{ participant_port_map : port_id
+    participant ||--o{ user : participant_id
+    user ||--o{ user_role_map : user_id
+    role ||--o{ user_role_map : role_id
+    role ||--o{ role_securable_map : role_id
+    securable ||--o{ role_securable_map : securable_id
+```

misc/check_clear_double_times_entries.sql

@@ -0,0 +1,46 @@
+-- Inspect duplicates first
+WITH duplicate_participants AS (
+    SELECT
+        shipcall_id,
+        participant_type,
+        COUNT(*) AS cnt
+    FROM times
+    GROUP BY shipcall_id, participant_type
+    HAVING COUNT(*) > 1
+)
+SELECT
+    t.*
+FROM times AS t
+JOIN duplicate_participants AS d
+    ON d.shipcall_id = t.shipcall_id
+   AND (d.participant_type <=> t.participant_type)
+ORDER BY t.shipcall_id, t.participant_type, t.id;
+
+-- Delete all but the highest-id entry per (shipcall_id, participant_type)
+WITH ordered_times AS (
+    SELECT
+        id,
+        ROW_NUMBER() OVER (
+            PARTITION BY shipcall_id, participant_type
+            ORDER BY id DESC
+        ) AS rn
+    FROM times
+)
+DELETE FROM times
+WHERE id IN (
+    SELECT id
+    FROM ordered_times
+    WHERE rn > 1
+);
+
+-- Optional: re-check that no duplicates remain
+WITH duplicate_participants AS (
+    SELECT
+        shipcall_id,
+        participant_type,
+        COUNT(*) AS cnt
+    FROM times
+    GROUP BY shipcall_id, participant_type
+    HAVING COUNT(*) > 1
+)
+SELECT COUNT(*) AS remaining_duplicates FROM duplicate_participants;

misc/clear_data.sql

@@ -1,11 +1,37 @@
-use bremen_calling_test;
+-- This script clears all data from the database tables related to the port management system.
 
 DELETE FROM notification WHERE id > 0;
 
 DELETE FROM history WHERE id > 0;
 
+DELETE FROM notification WHERE id > 0;
+
 DELETE FROM shipcall_participant_map WHERE id > 0;
 
+DELETE FROM participant_port_map WHERE id > 0;
+
+DELETE FROM shipcall_tug_map WHERE id > 0;
+
 DELETE FROM times WHERE id > 0;
 
 DELETE FROM shipcall WHERE id > 0;
+
+DELETE FROM user_role_map WHERE id > 0;
+
+DELETE FROM role_securable_map WHERE id > 0;
+
+DELETE FROM user_role_map WHERE id > 0;
+
+DELETE FROM securable WHERE id > 0;
+
+DELETE FROM role WHERE id > 0;
+
+DELETE FROM user WHERE id > 0;
+
+delete FROM ship WHERE id > 0;
+
+DELETE FROM berth WHERE id > 0;
+
+DELETE FROM participant WHERE id > 0;
+
+DELETE FROM port WHERE id > 0;

misc/create_schema.sql

@@ -1,8 +1,8 @@
--- MySQL dump 10.13  Distrib 8.0.33, for Win64 (x86_64)
+-- MySQL dump 10.13  Distrib 8.0.43, for Win64 (x86_64)
 --
 -- Host: localhost    Database: bremen_calling_test
 -- ------------------------------------------------------
--- Server version	8.0.34-0ubuntu0.22.04.1
+-- Server version	8.0.42-0ubuntu0.24.10.1
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@@ -28,17 +28,65 @@ CREATE TABLE `berth` (
   `lock` bit(1) DEFAULT NULL COMMENT 'The lock must be used',
   `owner_id` int unsigned DEFAULT NULL,
   `authority_id` int unsigned DEFAULT NULL,
+  `port_id` int unsigned DEFAULT NULL,
   `created` datetime DEFAULT CURRENT_TIMESTAMP,
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   `deleted` bit(1) DEFAULT b'0',
   PRIMARY KEY (`id`),
   KEY `FK_OWNER_PART_idx` (`owner_id`),
-  KEY `FK_AUTHORITY_PART_idx` (`authority_id`),
+  KEY `FK_AUTHORITY_PART_idx` (`authority_id`) /*!80000 INVISIBLE */,
+  KEY `FK_PORT_PART_idx` (`port_id`),
   CONSTRAINT `FK_AUTHORITY_PART` FOREIGN KEY (`authority_id`) REFERENCES `participant` (`id`),
-  CONSTRAINT `FK_OWNER_PART` FOREIGN KEY (`owner_id`) REFERENCES `participant` (`id`)
+  CONSTRAINT `FK_OWNER_PART` FOREIGN KEY (`owner_id`) REFERENCES `participant` (`id`),
-) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Berth of ship for a ship call';
+  CONSTRAINT `FK_PORT` FOREIGN KEY (`port_id`) REFERENCES `port` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Berth of ship for a ship call';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+--
+-- Table structure for table `history`
+--
+
+DROP TABLE IF EXISTS `history`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `history` (
+  `id` int unsigned NOT NULL AUTO_INCREMENT,
+  `participant_id` int unsigned NOT NULL,
+  `user_id` int unsigned DEFAULT NULL,
+  `shipcall_id` int unsigned NOT NULL,
+  `timestamp` datetime NOT NULL COMMENT 'Time of saving',
+  `eta` datetime DEFAULT NULL COMMENT 'Current ETA / ETD value (depends if shipcall or times were saved)',
+  `type` int NOT NULL COMMENT 'shipcall or times',
+  `operation` int NOT NULL COMMENT 'insert, update or delete',
+  PRIMARY KEY (`id`),
+  KEY `FK_HISTORY_PARTICIPANT_idx` (`participant_id`),
+  KEY `FK_HISTORY_SHIPCALL_idx` (`shipcall_id`),
+  KEY `FK_HISTORY_USER` (`user_id`),
+  CONSTRAINT `FK_HISTORY_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`),
+  CONSTRAINT `FK_HISTORY_SHIPCALL` FOREIGN KEY (`shipcall_id`) REFERENCES `shipcall` (`id`),
+  CONSTRAINT `FK_HISTORY_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=23537 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='This table stores a history of changes made to shipcalls so that everyone can see who changed what and when';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+CREATE TABLE `history` (
+  `id` int unsigned NOT NULL AUTO_INCREMENT,
+  `participant_id` int unsigned NOT NULL,
+  `user_id` int unsigned DEFAULT NULL,
+  `shipcall_id` int unsigned NOT NULL,
+  `timestamp` datetime NOT NULL COMMENT 'Time of saving',
+  `eta` datetime DEFAULT NULL COMMENT 'Current ETA / ETD value (depends if shipcall or times were saved)',
+  `type` int NOT NULL COMMENT 'shipcall or times',
+  `operation` int NOT NULL COMMENT 'insert, update or delete',
+  PRIMARY KEY (`id`),
+  KEY `FK_HISTORY_PARTICIPANT_idx` (`participant_id`),
+  KEY `FK_HISTORY_SHIPCALL_idx` (`shipcall_id`),
+  KEY `FK_HISTORY_USER` (`user_id`),
+  CONSTRAINT `FK_HISTORY_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`),
+  CONSTRAINT `FK_HISTORY_SHIPCALL` FOREIGN KEY (`shipcall_id`) REFERENCES `shipcall` (`id`),
+  CONSTRAINT `FK_HISTORY_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=29292 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='This table stores a history of changes made to shipcalls so that everyone can see who changed what and when';
+
+
 --
 -- Table structure for table `notification`
 --
@@ -48,20 +96,19 @@ DROP TABLE IF EXISTS `notification`;
 /*!50503 SET character_set_client = utf8mb4 */;
 CREATE TABLE `notification` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
-  `times_id` int unsigned NOT NULL COMMENT 'times record that caused the notification',
+  `shipcall_id` int unsigned DEFAULT NULL,
-  `participant_id` int unsigned NOT NULL COMMENT 'participant ref',
+  `participant_id` int unsigned DEFAULT NULL,
-  `acknowledged` bit(1) DEFAULT b'0' COMMENT 'true if UI acknowledged',
   `level` tinyint DEFAULT NULL COMMENT 'severity of the notification',
   `type` tinyint DEFAULT NULL COMMENT 'Email/UI/Other',
-  `message` varchar(256) DEFAULT NULL COMMENT 'individual message',
+  `message` varchar(512) DEFAULT NULL COMMENT 'individual message',
   `created` datetime DEFAULT CURRENT_TIMESTAMP,
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
-  KEY `FK_NOT_TIMES` (`times_id`),
+  KEY `FK_NOTIFICATION_SHIPCALL_idx` (`shipcall_id`),
-  KEY `FK_NOT_PART` (`participant_id`),
+  KEY `FK_NOTIFICATION_PARTICIPANT_idx` (`participant_id`),
-  CONSTRAINT `FK_NOT_PART` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`),
+  CONSTRAINT `FK_NOTIFICATION_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
-  CONSTRAINT `FK_NOT_TIMES` FOREIGN KEY (`times_id`) REFERENCES `times` (`id`)
+  CONSTRAINT `FK_NOTIFICATION_SHIPCALL` FOREIGN KEY (`shipcall_id`) REFERENCES `shipcall` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='An entry corresponds to an alarm given by a violated rule during times update';
+) ENGINE=InnoDB AUTO_INCREMENT=10398 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='An entry corresponds to an alarm given by a violated rule during times update';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -83,7 +130,46 @@ CREATE TABLE `participant` (
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   `deleted` bit(1) DEFAULT b'0',
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='An organization taking part';
+) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='An organization taking part';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `participant_port_map`
+--
+
+DROP TABLE IF EXISTS `participant_port_map`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `participant_port_map` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  `participant_id` int unsigned NOT NULL COMMENT 'Ref to participant',
+  `port_id` int unsigned NOT NULL COMMENT 'Ref to port',
+  `created` datetime DEFAULT CURRENT_TIMESTAMP,
+  `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (`id`),
+  KEY `FK_PP_PARTICIPANT` (`participant_id`),
+  KEY `FK_PP_PORT` (`port_id`),
+  CONSTRAINT `FK_PP_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`),
+  CONSTRAINT `FK_PP_PORT` FOREIGN KEY (`port_id`) REFERENCES `port` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Mapping table that assigns participants to a port';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `port`
+--
+
+DROP TABLE IF EXISTS `port`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!50503 SET character_set_client = utf8mb4 */;
+CREATE TABLE `port` (
+  `id` int unsigned NOT NULL AUTO_INCREMENT,
+  `name` varchar(128) NOT NULL COMMENT 'Name of port',
+  `locode` char(5) DEFAULT NULL COMMENT 'UNECE locode',
+  `created` datetime DEFAULT CURRENT_TIMESTAMP,
+  `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  `deleted` bit(1) DEFAULT b'0',
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Port as reference for shipcalls and berths';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -166,7 +252,7 @@ CREATE TABLE `ship` (
   PRIMARY KEY (`id`),
   KEY `FK_SHIP_PARTICIPANT` (`participant_id`),
   CONSTRAINT `FK_SHIP_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
+) ENGINE=InnoDB AUTO_INCREMENT=485 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -202,16 +288,25 @@ CREATE TABLE `shipcall` (
   `canceled` bit(1) DEFAULT NULL,
   `evaluation` int unsigned DEFAULT NULL,
   `evaluation_message` varchar(512) DEFAULT NULL,
+  `evaluation_time` datetime DEFAULT NULL,
+  `evaluation_notifications_sent` bit(1) DEFAULT NULL,
+  `port_id` int unsigned NOT NULL DEFAULT '1' COMMENT 'Selected port for this shipcall',
+  `time_ref_point` int DEFAULT '0' COMMENT 'Index of a location which is the reference point for all time value entries, e.g. berth or Geeste',
   `created` datetime DEFAULT CURRENT_TIMESTAMP,
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `FK_SHIPCALL_SHIP` (`ship_id`),
   KEY `FK_SHIPCALL_BERTH_ARRIVAL` (`arrival_berth_id`),
   KEY `FK_SHIPCALL_BERTH_DEPARTURE` (`departure_berth_id`),
+  KEY `idx_shipcall_type` (`type`),
+  KEY `idx_shipcall_eta` (`eta`),
+  KEY `idx_shipcall_etd` (`etd`),
+  KEY `FK_SHIPCALL_PORT_idx` (`port_id`),
   CONSTRAINT `FK_SHIPCALL_BERTH_ARRIVAL` FOREIGN KEY (`arrival_berth_id`) REFERENCES `berth` (`id`),
   CONSTRAINT `FK_SHIPCALL_BERTH_DEPARTURE` FOREIGN KEY (`departure_berth_id`) REFERENCES `berth` (`id`),
+  CONSTRAINT `FK_SHIPCALL_PORT` FOREIGN KEY (`port_id`) REFERENCES `port` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   CONSTRAINT `FK_SHIPCALL_SHIP` FOREIGN KEY (`ship_id`) REFERENCES `ship` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Incoming, outgoing or moving to another berth';
+) ENGINE=InnoDB AUTO_INCREMENT=2789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Incoming, outgoing or moving to another berth';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -225,15 +320,15 @@ CREATE TABLE `shipcall_participant_map` (
   `id` int NOT NULL AUTO_INCREMENT,
   `shipcall_id` int unsigned DEFAULT NULL,
   `participant_id` int unsigned DEFAULT NULL,
-  `type` int unsigned DEFAULT NULL COMMENT 'Type of participant role',
+  `type` int unsigned DEFAULT NULL,
   `created` datetime DEFAULT CURRENT_TIMESTAMP,
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `FK_MAP_PARTICIPANT_SHIPCALL` (`shipcall_id`),
   KEY `FK_MAP_SHIPCALL_PARTICIPANT` (`participant_id`),
-  CONSTRAINT `FK_MAP_PARTICIPANT_SHIPCALL` FOREIGN KEY (`shipcall_id`) REFERENCES `shipcall` (`id`),
+  CONSTRAINT `FK_MAP_PARTICIPANT_SHIPCALL` FOREIGN KEY (`shipcall_id`) REFERENCES `shipcall` (`id`) ON DELETE SET NULL,
   CONSTRAINT `FK_MAP_SHIPCALL_PARTICIPANT` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Associates a participant with a shipcall';
+) ENGINE=InnoDB AUTO_INCREMENT=8933 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Associates a participant with a shipcall';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -285,13 +380,20 @@ CREATE TABLE `times` (
   `berth_info` varchar(512) DEFAULT NULL,
   `pier_side` bit(1) DEFAULT NULL,
   `participant_type` int unsigned DEFAULT NULL,
+  `ata` datetime DEFAULT NULL COMMENT 'Relevant only for mooring, this field can be used to record actual ATA',
+  `atd` datetime DEFAULT NULL COMMENT 'Relevant only for mooring, this field can be used to record actual ATD',
+  `eta_interval_end` datetime DEFAULT NULL COMMENT 'If this value is set the times are given as interval instead of a single point in time. The start time value depends on the participant type.',
+  `etd_interval_end` datetime DEFAULT NULL COMMENT 'If this value is set the times are given as interval instead of a single point in time. The start time value depends on the participant type.',
   PRIMARY KEY (`id`),
+  UNIQUE KEY `uniq_shipcall_participant` (`shipcall_id`,`participant_type`),
   KEY `FK_TIME_SHIPCALL` (`shipcall_id`),
   KEY `FK_TIME_PART` (`participant_id`) /*!80000 INVISIBLE */,
   KEY `FK_TIME_BERTH` (`berth_id`) /*!80000 INVISIBLE */,
+  KEY `idx_times_eta_berth` (`eta_berth`),
+  KEY `idx_times_etd_berth` (`etd_berth`),
   CONSTRAINT `FK_TIME_BERTH` FOREIGN KEY (`berth_id`) REFERENCES `berth` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
   CONSTRAINT `FK_TIME_PART` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='the planned time for the participants work';
+) ENGINE=InnoDB AUTO_INCREMENT=7863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='the planned time for the participants work';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -303,7 +405,7 @@ DROP TABLE IF EXISTS `user`;
 /*!50503 SET character_set_client = utf8mb4 */;
 CREATE TABLE `user` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
-  `participant_id` int unsigned DEFAULT NULL,
+  `participant_id` int unsigned NOT NULL,
   `first_name` varchar(45) DEFAULT NULL,
   `last_name` varchar(45) DEFAULT NULL,
   `user_name` varchar(45) DEFAULT NULL,
@@ -311,12 +413,17 @@ CREATE TABLE `user` (
   `user_phone` varchar(128) DEFAULT NULL,
   `password_hash` varchar(128) DEFAULT NULL,
   `api_key` varchar(256) DEFAULT NULL,
+  `notify_email` bit(1) DEFAULT NULL,
+  `notify_whatsapp` bit(1) DEFAULT NULL,
+  `notify_signal` bit(1) DEFAULT NULL,
+  `notify_popup` bit(1) DEFAULT NULL,
+  `notify_event` int DEFAULT NULL COMMENT 'Bitflag of selected notification event types that the user wants to be notified of',
   `created` datetime DEFAULT CURRENT_TIMESTAMP,
   `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `FK_USER_PART` (`participant_id`),
   CONSTRAINT `FK_USER_PART` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='member of a participant';
+) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='member of a participant';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -339,6 +446,57 @@ CREATE TABLE `user_role_map` (
   CONSTRAINT `FK_USER_ROLE` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Assigns a user to a role';
 /*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping routines for database 'bremen_calling_test'
+--
+/*!50003 DROP PROCEDURE IF EXISTS `delete_data` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8mb4 */ ;
+/*!50003 SET character_set_results = utf8mb4 */ ;
+/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+CREATE DEFINER=`ds`@`localhost` PROCEDURE `delete_data`()
+BEGIN
+	DECLARE shipcall_id_var int;
+    DECLARE done INT DEFAULT FALSE;
+
+	DECLARE shipcall_iter CURSOR FOR
+		SELECT shipcall.id FROM shipcall
+			LEFT JOIN times ON
+				times.shipcall_id = shipcall.id AND times.participant_type = 8
+			WHERE
+				-- ARRIVAL
+				(type = 1 AND GREATEST(shipcall.eta, COALESCE(times.eta_berth, 0)) <= CURRENT_DATE() - INTERVAL 1 MONTH) OR
+                -- DEPARTURE / SHIFTING
+                (type != 1 AND GREATEST(shipcall.etd, COALESCE(times.etd_berth, 0)) <= CURRENT_DATE() - INTERVAL 1 MONTH);
+
+    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+	OPEN shipcall_iter;
+
+    delete_loop: LOOP
+		FETCH shipcall_iter INTO shipcall_id_var;
+		IF done THEN
+			LEAVE delete_loop;
+		END IF;
+        DELETE FROM shipcall_participant_map WHERE shipcall_id = shipcall_id_var;
+		DELETE FROM shipcall_tug_map WHERE shipcall_id = shipcall_id_var;
+        DELETE FROM times WHERE shipcall_id = shipcall_id_var;
+        DELETE FROM history WHERE shipcall_id = shipcall_id_var;
+		DELETE FROM shipcall WHERE id = shipcall_id_var;
+    END LOOP;
+	CLOSE shipcall_iter;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -349,4 +507,4 @@ CREATE TABLE `user_role_map` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2023-10-06 14:52:04
+-- Dump completed on 2025-11-17  8:26:36

misc/index.html

@@ -0,0 +1,11 @@
+<!doctype html>
+<html>
+    <head>
+        <meta charset="UTF-8">
+        <title>Bremen Calling</title>
+        <link rel="shortcut icon" type="image/x-icon" href="favicon.ico">
+    </head>
+    <body>
+
+    </body>
+</html>

misc/version.txt

@@ -1 +1 @@
-1.7.0.6
+1.8.0.0

src/BreCalClient/App.config

@@ -32,7 +32,7 @@
                 <value>#751D1F</value>
             </setting>
             <setting name="APP_TITLE" serializeAs="String">
-                <value>!!Bremen calling Testversion!!</value>
+                <value>!!Bremen calling Entwicklungsversion!!</value>
             </setting>
             <setting name="LOGO_IMAGE_URL" serializeAs="String">
                 <value>https://www.textbausteine.net/</value>

src/BreCalClient/BreCalClient.csproj

@@ -8,8 +8,8 @@
     <SignAssembly>True</SignAssembly>
     <StartupObject>BreCalClient.App</StartupObject>
     <AssemblyOriginatorKeyFile>..\..\misc\brecal.snk</AssemblyOriginatorKeyFile>
-    <AssemblyVersion>1.7.0.6</AssemblyVersion>
+    <AssemblyVersion>1.8.0.0</AssemblyVersion>
-    <FileVersion>1.7.0.6</FileVersion>
+    <FileVersion>1.8.0.0</FileVersion>
     <Title>Bremen calling client</Title>
     <Description>A Windows WPF client for the Bremen calling API.</Description>
     <ApplicationIcon>containership.ico</ApplicationIcon>

src/BreCalClient/EditTimesControl.xaml.cs

@@ -260,8 +260,8 @@ namespace BreCalClient
             else
             {
                 this.rowt1.Height = new(0);
-                this.rowt1.Height = new(0);
+                this.rowt2.Height = new(0);
-                this.rowt1.Height = new(0);
+                this.rowt3.Height = new(0);
             }
 
             this.SetLockButton(this.Times.EtaBerthFixed ?? false);

src/BreCalClient/Properties/PublishProfiles/ClickOnceDevelProfile.pubxml

@@ -4,8 +4,8 @@ https://go.microsoft.com/fwlink/?LinkID=208121.
 -->
 <Project>
   <PropertyGroup>
-    <ApplicationRevision>1</ApplicationRevision>
+    <ApplicationRevision>0</ApplicationRevision>
-    <ApplicationVersion>1.7.0.6</ApplicationVersion>
+    <ApplicationVersion>1.8.0.0</ApplicationVersion>
     <BootstrapperEnabled>True</BootstrapperEnabled>
     <Configuration>Debug</Configuration>
     <CreateDesktopShortcut>True</CreateDesktopShortcut>
@@ -21,7 +21,7 @@ https://go.microsoft.com/fwlink/?LinkID=208121.
     <OpenBrowserOnPublish>False</OpenBrowserOnPublish>
     <Platform>Any CPU</Platform>
     <ProductName>Bremen calling development client</ProductName>
-    <PublishDir>bin\Debug\net6.0-windows\win-x64\app.publish\</PublishDir>
+    <PublishDir>bin\Debug\net8.0-windows7.0\win-x64\app.publish\</PublishDir>
     <PublishUrl>bin\publish.devel\</PublishUrl>
     <PublisherName>Informatikbüro Daniel Schick</PublisherName>
     <PublishProtocol>ClickOnce</PublishProtocol>
@@ -33,12 +33,12 @@ https://go.microsoft.com/fwlink/?LinkID=208121.
     <SignManifests>False</SignManifests>
     <SuiteName>Bremen calling</SuiteName>
     <SupportUrl>https://www.textbausteine.net/</SupportUrl>
-    <TargetFramework>net6.0-windows</TargetFramework>
+    <TargetFramework>net8.0-windows7.0</TargetFramework>
     <UpdateEnabled>True</UpdateEnabled>
     <UpdateMode>Foreground</UpdateMode>
     <UpdateRequired>True</UpdateRequired>
     <WebPageFileName>Publish.html</WebPageFileName>
-    <MinimumRequiredVersion>1.7.0.6</MinimumRequiredVersion>
+    <MinimumRequiredVersion>1.8.0.0</MinimumRequiredVersion>
     <SkipPublishVerification>false</SkipPublishVerification>
   </PropertyGroup>
   <ItemGroup>

src/BreCalClient/Properties/PublishProfiles/ClickOnceTestProfile.pubxml

@@ -5,7 +5,7 @@ https://go.microsoft.com/fwlink/?LinkID=208121.
 <Project>
   <PropertyGroup>
     <ApplicationRevision>5</ApplicationRevision>
-    <ApplicationVersion>1.7.0.6</ApplicationVersion>
+    <ApplicationVersion>1.7.0.7</ApplicationVersion>
     <BootstrapperEnabled>True</BootstrapperEnabled>
     <Configuration>Debug</Configuration>
     <CreateDesktopShortcut>True</CreateDesktopShortcut>
@@ -41,7 +41,7 @@ https://go.microsoft.com/fwlink/?LinkID=208121.
     <PublishDir>bin\Debug\net8.0-windows7.0\win-x64\app.publish\</PublishDir>
     <RuntimeIdentifier>win-x64</RuntimeIdentifier>
     <SkipPublishVerification>false</SkipPublishVerification>
-    <MinimumRequiredVersion>1.7.0.6</MinimumRequiredVersion>
+    <MinimumRequiredVersion>1.7.0.7</MinimumRequiredVersion>
   </PropertyGroup>
   <ItemGroup>
     <PublishFile Include="containership.ico">

src/BreCalClient/Properties/Settings.Designer.cs

@@ -12,7 +12,7 @@ namespace BreCalClient.Properties {
 
 
     [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
-    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "17.10.0.0")]
+    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "17.12.0.0")]
     internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase {
 
         private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));
@@ -34,7 +34,7 @@ namespace BreCalClient.Properties {
 
         [global::System.Configuration.ApplicationScopedSettingAttribute()]
         [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
-        [global::System.Configuration.DefaultSettingValueAttribute("!!Bremen calling Testversion!!")]
+        [global::System.Configuration.DefaultSettingValueAttribute("!!Bremen calling Entwicklungsversion!!")]
         public string APP_TITLE {
             get {
                 return ((string)(this["APP_TITLE"]));

src/BreCalClient/Properties/Settings.settings

@@ -6,7 +6,7 @@
       <Value Profile="(Default)">#751D1F</Value>
     </Setting>
     <Setting Name="APP_TITLE" Type="System.String" Scope="Application">
-      <Value Profile="(Default)">!!Bremen calling Testversion!!</Value>
+      <Value Profile="(Default)">!!Bremen calling Entwicklungsversion!!</Value>
     </Setting>
     <Setting Name="LOGO_IMAGE_URL" Type="System.String" Scope="Application">
       <Value Profile="(Default)">https://www.textbausteine.net/</Value>

src/BreCalClient/Resources/Resources.Designer.cs

@@ -624,6 +624,15 @@ namespace BreCalClient.Resources {
             }
         }
         
+        /// <summary>
+        ///   Looks up a localized string similar to Draft.
+        /// </summary>
+        public static string textDraftNoUnit {
+            get {
+                return ResourceManager.GetString("textDraftNoUnit", resourceCulture);
+            }
+        }
+        
         /// <summary>
         ///   Looks up a localized string similar to Edit.
         /// </summary>
@@ -1146,6 +1155,15 @@ namespace BreCalClient.Resources {
             }
         }
         
+        /// <summary>
+        ///   Looks up a localized string similar to Position.
+        /// </summary>
+        public static string textPosition {
+            get {
+                return ResourceManager.GetString("textPosition", resourceCulture);
+            }
+        }
+        
         /// <summary>
         ///   Looks up a localized string similar to Rain sensitive cargo.
         /// </summary>

src/BreCalClient/Resources/Resources.de.resx

@@ -235,6 +235,9 @@
   <data name="textDraft" xml:space="preserve">
     <value>Tiefgang (m)</value>
   </data>
+  <data name="textDraftNoUnit" xml:space="preserve">
+    <value>Tiefgang</value>
+  </data>
   <data name="textEdit" xml:space="preserve">
     <value>Bearbeiten</value>
   </data>

src/BreCalClient/Resources/Resources.resx

@@ -649,4 +649,10 @@
   <data name="textNotifyOn" xml:space="preserve">
     <value>Notify on</value>
   </data>
+  <data name="textPosition" xml:space="preserve">
+    <value>Position</value>
+  </data>
+  <data name="textDraftNoUnit" xml:space="preserve">
+    <value>Draft</value>
+  </data>
 </root>

src/BreCalClient/ShipcallControl.xaml

@@ -29,7 +29,6 @@
                     <RowDefinition Height=".125*"/>
                     <RowDefinition Height=".125*"/>
                     <RowDefinition Height=".125*"/>
-
                     <RowDefinition Height=".125*"/>
                     <RowDefinition Height=".125*"/>
                     <RowDefinition Height=".125*"/>
@@ -64,17 +63,17 @@
                     <TextBlock x:Name="textBlockIMO" Padding="0" FontWeight="DemiBold" />
                 </Viewbox>
                 <Viewbox Grid.Row="2" Grid.Column="0" HorizontalAlignment="Left">
-                    <TextBlock Text="{x:Static p:Resources.textCallsign}" />
-                </Viewbox>
-                <Viewbox Grid.Row="2" Grid.Column="1" HorizontalAlignment="Left">
-                    <TextBlock x:Name="textBlockCallsign" Padding="0"/>
-                </Viewbox>
-                <Viewbox Grid.Row="3" Grid.Column="0" HorizontalAlignment="Left">
                     <TextBlock Text="{x:Static p:Resources.textLengthWidth}" Padding="0" />
                 </Viewbox>
-                <Viewbox Grid.Row="3" Grid.Column="1" HorizontalAlignment="Left">
+                <Viewbox Grid.Row="2" Grid.Column="1" HorizontalAlignment="Left">
                     <TextBlock x:Name="textBlockLengthWidth" Padding="0"/>
                 </Viewbox>
+                <Viewbox Grid.Row="3" Grid.Column="0" HorizontalAlignment="Left">
+                    <TextBlock Text="{x:Static p:Resources.textDraftNoUnit}" />
+                </Viewbox>
+                <Viewbox Grid.Row="3" Grid.Column="1" HorizontalAlignment="Left">
+                    <TextBlock x:Name="textBlockDraft" Padding="0"/>
+                </Viewbox>
                 <Viewbox Grid.Row="4" Grid.Column="0" HorizontalAlignment="Left">
                     <TextBlock Text="ETA" x:Name="labelETA"/>
                 </Viewbox>
@@ -90,7 +89,6 @@
                 <Viewbox Grid.Row="6" Grid.Column="0" Grid.ColumnSpan="2" HorizontalAlignment="Left">
                     <TextBlock x:Name="textBlockHarbour" Padding="0" FontWeight="DemiBold" />
                 </Viewbox>
-
             </Grid>
             <Grid Grid.Row="0" Grid.Column="1">
                 <Grid.RowDefinitions>

src/BreCalClient/ShipcallControl.xaml.cs

@@ -269,7 +269,7 @@ namespace BreCalClient
                         this.imageEvaluation.ToolTip = null;
 
                     this.textBlockBerth.Text = this.ShipcallControlModel?.GetBerthText(null);
-                    this.textBlockCallsign.Text = this.ShipcallControlModel?.Ship?.Callsign;
+                    this.textBlockDraft.Text = (this.ShipcallControlModel?.Shipcall?.Draft != null) ? $"{this.ShipcallControlModel?.Shipcall?.Draft.Value.ToString("N2")} m" : "-";
                     this.textBlockETA.Text = this.ShipcallControlModel?.GetETAETD(true);
 
                     this.textBlockIMO.Text = this.ShipcallControlModel?.Ship?.Imo.ToString();

src/RoleEditor/App.config

@@ -8,7 +8,7 @@
     <applicationSettings>
         <RoleEditor.Properties.Settings>
             <setting name="ConnectionString" serializeAs="String">
-                <value>Server=localhost;User ID=ds;Password=HalloWach_2323XXL!!;Database=bremen_calling_test;Port=33306</value>
+                <value>Server=localhost;User ID=ds;Password=HalloWach_2323XXL!!;Database=bremen_calling_test;Port=33307</value>
             </setting>
         </RoleEditor.Properties.Settings>
     </applicationSettings>

src/RoleEditor/MainWindow.xaml

@@ -59,7 +59,7 @@
                             <Label Content="Street" Grid.Row="1" Grid.Column="1" HorizontalAlignment="Right"/>
                             <Label Content="Postal code" Grid.Row="2" Grid.Column="1" HorizontalAlignment="Right"/>
                             <Label Content="City" Grid.Row="3" Grid.Column="1" HorizontalAlignment="Right"/>
-                            <Label Content="Active" Grid.Row="4" Grid.Column="1" HorizontalAlignment="Right"/>
+                            <Label Content="Deleted" Grid.Row="4" Grid.Column="1" HorizontalAlignment="Right"/>
                             <Label Content="Type" Grid.Row="5" Grid.Column="1" HorizontalAlignment="Right"/>
                             <Label Content="Created" Grid.Row="6" Grid.Column="1" HorizontalAlignment="Right"/>
                             <Label Content="Modified" Grid.Row="7" Grid.Column="1" HorizontalAlignment="Right"/>
@@ -67,7 +67,7 @@
                             <TextBox x:Name="textBoxParticipantStreet" Grid.Row="1" Grid.Column="2" Margin="2" VerticalContentAlignment="Center" />
                             <TextBox x:Name="textBoxParticipantPostalCode" Grid.Row="2" Grid.Column="2" Margin="2" VerticalContentAlignment="Center" />
                             <TextBox x:Name="textBoxParticipantCity" Grid.Row="3" Grid.Column="2" Margin="2" VerticalContentAlignment="Center" />
-                            <CheckBox x:Name="checkboxParticipantActive" Grid.Row="4" Grid.Column="2" VerticalAlignment="Center" />
+                            <CheckBox x:Name="checkboxParticipantDeleted" Grid.Row="4" Grid.Column="2" VerticalAlignment="Center" IsEnabled="False" />
                             <xctk:CheckComboBox x:Name="comboBoxParticipantType" Grid.Row="5" Grid.Column="2" Margin="2" SelectedValue="Key" DisplayMemberPath="Value" />
                             <TextBox x:Name="textBoxParticipantCreated" Grid.Row="6" IsReadOnly="True" IsEnabled="False" Grid.Column="2" Margin="2" VerticalContentAlignment="Center" />
                             <StackPanel Orientation="Horizontal" Grid.Row="7" Grid.Column="0">
@@ -167,7 +167,7 @@
                             </Grid.ColumnDefinitions>
                             <ListBox x:Name="listBoxUser" Margin="2" Grid.RowSpan="9" SelectionChanged="listBoxUser_SelectionChanged">
                                 <ListBox.ContextMenu>
-                                    <ContextMenu>
+                                    <ContextMenu Name="contextMenuUser">
                                         <MenuItem x:Name="menuItemNewUser" Header="New.." Click="menuItemNewUser_Click">
                                             <MenuItem.Icon>
                                                 <Image Source="Resources/add.png" />

src/RoleEditor/MainWindow.xaml.cs

@@ -61,6 +61,8 @@ namespace RoleEditor
 
                 // load all participants
                 List<Participant> participants = await Participant.LoadAll(_dbManager);
+                participants.Sort((x, y) => string.Compare(x.Name, y.Name));
+
                 foreach (Participant p in participants)
                 {
                     _participants.Add(p);
@@ -465,8 +467,8 @@ namespace RoleEditor
             this.textBoxParticipantStreet.Text = (p != null) ? p.Street : string.Empty;
             this.textBoxParticipantPostalCode.Text = (p != null) ? p.PostalCode : string.Empty;
             this.textBoxParticipantCity.Text = (p != null) ? p.City : string.Empty;            
-            //  this.checkboxParticipantActive.Checked = (p != null) ? p.
             this.textBoxParticipantCreated.Text = (p != null) ? p.Created.ToString() : string.Empty;
+            this.checkboxParticipantDeleted.IsChecked = (p != null) ? p.Deleted : null;
             this.textBoxParticipantModified.Text = (p != null) ? p.Modified.ToString() : string.Empty;
             this.checkBoxParticipantAllowBSMD.IsChecked = (p != null) ? p.IsFlagSet(Participant.ParticipantFlags.ALLOW_BSMD) : null;
             this.comboBoxParticipantType.SelectedItems.Clear();
@@ -510,6 +512,11 @@ namespace RoleEditor
                     _assignedPorts.Add(pa);
                 }
             }
+
+            this.contextMenuUser.IsEnabled = !p.Deleted;
+            this.buttonAddPortAssignment.IsEnabled = !p.Deleted;
+            this.buttonRemovePortAssignment.IsEnabled = !p.Deleted;
+
         }
 
         private async void listBoxRoles_SelectionChanged(object sender, SelectionChangedEventArgs e)
@@ -594,7 +601,7 @@ namespace RoleEditor
                 if(this.listBoxParticipant.SelectedItem is Participant p)
                 {
                     await p.Delete(_dbManager);
-                    this._participants.Remove(p);
+                    p.Deleted = true;
                 }
             }
             catch (Exception ex)
@@ -628,6 +635,7 @@ namespace RoleEditor
             {
                 if (this.listBoxUser.SelectedItem is User u)
                 {
+                    await u.ExecuteNonQuery(_dbManager); // extra history delete happens here
                     await u.Delete(_dbManager);
                     this._users.Remove(u);
                 }

src/RoleEditor/RoleEditor.csproj

@@ -2,12 +2,12 @@
 
   <PropertyGroup>
     <OutputType>WinExe</OutputType>
-    <TargetFramework>net6.0-windows</TargetFramework>
+    <TargetFramework>net8.0-windows7.0</TargetFramework>
     <Nullable>enable</Nullable>
     <UseWPF>true</UseWPF>
     <ApplicationIcon>Resources\lock_preferences.ico</ApplicationIcon>
-    <FileVersion>1.7.0.6</FileVersion>
+    <FileVersion>1.8.0.0</FileVersion>
-    <AssemblyVersion>1.7.0.6</AssemblyVersion>
+    <AssemblyVersion>1.8.0.0</AssemblyVersion>
   </PropertyGroup>
 
   <ItemGroup>
@@ -30,8 +30,8 @@
 
   <ItemGroup>
     <PackageReference Include="BCrypt.Net-Next" Version="4.0.3" />
-    <PackageReference Include="ExcelDataReader" Version="3.7.0-develop00385" />
+    <PackageReference Include="ExcelDataReader" Version="3.8.0" />
-    <PackageReference Include="Extended.Wpf.Toolkit" Version="4.5.0" />
+    <PackageReference Include="Extended.Wpf.Toolkit" Version="5.0.0" />
   </ItemGroup>
 
   <ItemGroup>

src/Setup/Setup.vdproj

@@ -13,6 +13,12 @@
 "SccProvider" = "8:"
     "Hierarchy"
     {
+        "Entry"
+        {
+        "MsmKey" = "8:_1E7663DCE02A4D848349229A724E961A"
+        "OwnerKey" = "8:_UNDEFINED"
+        "MsmSig" = "8:_UNDEFINED"
+        }
         "Entry"
         {
         "MsmKey" = "8:_3E48B6E716164CC1826E094025517B3F"
@@ -25,6 +31,24 @@
         "OwnerKey" = "8:_UNDEFINED"
         "MsmSig" = "8:_UNDEFINED"
         }
+        "Entry"
+        {
+        "MsmKey" = "8:_CD20A468610C42B89F66B4D3367A5A6A"
+        "OwnerKey" = "8:_UNDEFINED"
+        "MsmSig" = "8:_UNDEFINED"
+        }
+        "Entry"
+        {
+        "MsmKey" = "8:_UNDEFINED"
+        "OwnerKey" = "8:_CD20A468610C42B89F66B4D3367A5A6A"
+        "MsmSig" = "8:_UNDEFINED"
+        }
+        "Entry"
+        {
+        "MsmKey" = "8:_UNDEFINED"
+        "OwnerKey" = "8:_1E7663DCE02A4D848349229A724E961A"
+        "MsmSig" = "8:_UNDEFINED"
+        }
     }
     "Configurations"
     {
@@ -76,6 +100,14 @@
         {
             "LaunchCondition"
             {
+                "{A06ECF26-33A3-4562-8140-9B0E340D4F24}:_3415D375792A4611BF998D78F56CD22C"
+                {
+                "Name" = "8:.NET Framework"
+                "Message" = "8:[VSDNETMSG]"
+                "FrameworkVersion" = "8:.NETFramework,Version=v4.7.2"
+                "AllowLaterVersions" = "11:FALSE"
+                "InstallUrl" = "8:http://go.microsoft.com/fwlink/?LinkId=863262"
+                }
                 "{A06ECF26-33A3-4562-8140-9B0E340D4F24}:_7C5ED856EDF94532A041DBACD5D5C09E"
                 {
                 "Name" = "8:.NET Core"
@@ -90,6 +122,37 @@
         }
         "File"
         {
+            "{9F6F8455-1EF1-4B85-886A-4223BCC8E7F7}:_1E7663DCE02A4D848349229A724E961A"
+            {
+            "AssemblyRegister" = "3:1"
+            "AssemblyIsInGAC" = "11:FALSE"
+            "AssemblyAsmDisplayName" = "8:Xceed.Wpf.AvalonDock.resources, Version=4.6.0.0, Culture=de, PublicKeyToken=3e4669d2f30244f4, processorArchitecture=MSIL"
+                "ScatterAssemblies"
+                {
+                    "_1E7663DCE02A4D848349229A724E961A"
+                    {
+                    "Name" = "8:Xceed.Wpf.AvalonDock.resources.dll"
+                    "Attributes" = "3:512"
+                    }
+                }
+            "SourcePath" = "8:..\\BreCalClient\\bin\\Debug\\net6.0-windows\\de\\Xceed.Wpf.AvalonDock.resources.dll"
+            "TargetName" = "8:"
+            "Tag" = "8:"
+            "Folder" = "8:_F64284776BC0480CBF6C33B1FE00C374"
+            "Condition" = "8:"
+            "Transitive" = "11:FALSE"
+            "Vital" = "11:TRUE"
+            "ReadOnly" = "11:FALSE"
+            "Hidden" = "11:FALSE"
+            "System" = "11:FALSE"
+            "Permanent" = "11:FALSE"
+            "SharedLegacy" = "11:FALSE"
+            "PackageAs" = "3:1"
+            "Register" = "3:1"
+            "Exclude" = "11:FALSE"
+            "IsDependency" = "11:FALSE"
+            "IsolateTo" = "8:"
+            }
             "{1FB2D0AE-D3B9-43D4-B9DD-F88EC61E35DE}:_4EE484EAA4A246CBBB283030A6054BC0"
             {
             "SourcePath" = "8:..\\BreCalClient\\Resources\\containership.ico"
@@ -110,6 +173,37 @@
             "IsDependency" = "11:FALSE"
             "IsolateTo" = "8:"
             }
+            "{9F6F8455-1EF1-4B85-886A-4223BCC8E7F7}:_CD20A468610C42B89F66B4D3367A5A6A"
+            {
+            "AssemblyRegister" = "3:1"
+            "AssemblyIsInGAC" = "11:FALSE"
+            "AssemblyAsmDisplayName" = "8:BreCalClient.resources, Version=1.6.2.0, Culture=de, PublicKeyToken=9ce7b6b354e08ac9, processorArchitecture=MSIL"
+                "ScatterAssemblies"
+                {
+                    "_CD20A468610C42B89F66B4D3367A5A6A"
+                    {
+                    "Name" = "8:BreCalClient.resources.dll"
+                    "Attributes" = "3:512"
+                    }
+                }
+            "SourcePath" = "8:..\\BreCalClient\\bin\\Debug\\net6.0-windows\\de\\BreCalClient.resources.dll"
+            "TargetName" = "8:"
+            "Tag" = "8:"
+            "Folder" = "8:_F64284776BC0480CBF6C33B1FE00C374"
+            "Condition" = "8:"
+            "Transitive" = "11:FALSE"
+            "Vital" = "11:TRUE"
+            "ReadOnly" = "11:FALSE"
+            "Hidden" = "11:FALSE"
+            "System" = "11:FALSE"
+            "Permanent" = "11:FALSE"
+            "SharedLegacy" = "11:FALSE"
+            "PackageAs" = "3:1"
+            "Register" = "3:1"
+            "Exclude" = "11:FALSE"
+            "IsDependency" = "11:FALSE"
+            "IsolateTo" = "8:"
+            }
         }
         "FileType"
         {
@@ -137,6 +231,17 @@
             "Property" = "8:TARGETDIR"
                 "Folders"
                 {
+                    "{9EF0B969-E518-4E46-987F-47570745A589}:_F64284776BC0480CBF6C33B1FE00C374"
+                    {
+                    "Name" = "8:de"
+                    "AlwaysCreate" = "11:FALSE"
+                    "Condition" = "8:"
+                    "Transitive" = "11:FALSE"
+                    "Property" = "8:_319F0FD8E72443BFA3AE5E1F3F42523B"
+                        "Folders"
+                        {
+                        }
+                    }
                 }
             }
             "{1525181F-901A-416C-8A58-119130FE478E}:_8BBC7FE2F38E4B41A71D26CCED7D0BCB"

src/brecal.model/DbEntity.cs

@@ -1,8 +1,4 @@
-using System;
+using System.Data;
-using System.Collections.Generic;
-using System.Data;
-using System.Linq;
-using System.Text;
 using System.Threading.Tasks;
 
 namespace brecal.model
@@ -42,6 +38,11 @@ namespace brecal.model
         /// <param name="cmd">CMD created by DB manager</param>
         public abstract void SetDelete(IDbCommand cmd);
 
+        public virtual void SetNonQuery(IDbCommand cmd)
+        {
+            // default: do nothing
+        }
+
         /// <summary>
         /// Each database entity must be able to save itself to the database
         /// </summary>
@@ -65,5 +66,10 @@ namespace brecal.model
             await manager.ExecuteNonQuery(this.SetDelete);
         }
 
+        public async Task ExecuteNonQuery(IDBManager manager)
+        {
+            await manager.ExecuteNonQuery(this.SetNonQuery);
+        }
+
     }
 }

src/brecal.model/Participant.cs

@@ -55,6 +55,8 @@ namespace brecal.model
 
         public uint Flags { get; set; }
 
+        public bool Deleted { get; set; } = false;
+
         #endregion
 
         #region public static methods
@@ -83,6 +85,7 @@ namespace brecal.model
                 if (!reader.IsDBNull(6)) p.Flags = (uint)reader.GetInt32(6);
                 if (!reader.IsDBNull(7)) p.Created = reader.GetDateTime(7);
                 if (!reader.IsDBNull(8)) p.Modified = reader.GetDateTime(8);
+                if (!reader.IsDBNull(9)) p.Deleted = reader.GetBoolean(9);
                 result.Add(p);
             }
             return result;
@@ -90,7 +93,7 @@ namespace brecal.model
 
         public static void SetLoadQuery(IDbCommand cmd, params object?[] list)
         {
-            cmd.CommandText = "SELECT id, name, street, postal_code, city, type, flags, created, modified FROM participant";
+            cmd.CommandText = "SELECT id, name, street, postal_code, city, type, flags, created, modified, deleted FROM participant";
         }
 
         #endregion
@@ -111,7 +114,7 @@ namespace brecal.model
 
         public override void SetDelete(IDbCommand cmd)
         {
-            cmd.CommandText = "DELETE FROM participant WHERE id = @ID";
+            cmd.CommandText = "UPDATE participant SET deleted = 1 WHERE id = @ID";
 
             IDataParameter idParam = cmd.CreateParameter();
             idParam.ParameterName = "ID";

src/brecal.model/User.cs

@@ -101,6 +101,16 @@ namespace brecal.model
             return this.Username ?? $"{base.Id} - {this.GetType().Name}";
         }
 
+        public override void SetNonQuery(IDbCommand cmd)
+        {
+            cmd.CommandText = "UPDATE history set user_id = NULL WHERE user_id = @ID";
+
+            IDataParameter idParam = cmd.CreateParameter();
+            idParam.ParameterName = "ID";
+            idParam.Value = this.Id;
+            cmd.Parameters.Add(idParam);
+        }
+
         #endregion
 
         #region private methods

src/brecal.model/brecal.model.csproj

@@ -1,7 +1,7 @@
 <Project Sdk="Microsoft.NET.Sdk">
 
   <PropertyGroup>
-    <TargetFramework>net6.0</TargetFramework>
+    <TargetFramework>net8.0</TargetFramework>
     <ImplicitUsings>enable</ImplicitUsings>
     <Nullable>enable</Nullable>
   </PropertyGroup>

src/brecal.mysql/brecal.mysql.csproj

@@ -1,13 +1,13 @@
 <Project Sdk="Microsoft.NET.Sdk">
 
   <PropertyGroup>
-    <TargetFramework>net6.0</TargetFramework>
+    <TargetFramework>net8.0</TargetFramework>
     <ImplicitUsings>enable</ImplicitUsings>
     <Nullable>enable</Nullable>
   </PropertyGroup>
 
   <ItemGroup>
-    <PackageReference Include="MySqlConnector" Version="2.3.0-beta.1" />
+    <PackageReference Include="MySqlConnector" Version="2.4.0" />
   </ItemGroup>
 
   <ItemGroup>

src/server/BreCal/__init__.py

@@ -1,6 +1,7 @@
 from flask import Flask
 
 import os
+import sys
 import logging
 from . import local_db
 
@@ -36,7 +37,6 @@ from BreCal.stubs.df_times import get_df_times
 from BreCal.services.schedule_routines import setup_schedule, run_schedule_permanently_in_background
 
 def create_app(test_config=None, instance_path=None):
-
     app = Flask(__name__, instance_relative_config=True)
     app.config.from_mapping(
         SECRET_KEY='dev'
@@ -48,6 +48,8 @@ def create_app(test_config=None, instance_path=None):
 
     if instance_path is not None:
         app.instance_path = instance_path
+    elif app.config.get("INSTANCE_PATH"):
+        app.instance_path = app.config["INSTANCE_PATH"]
 
     try:
         import os
@@ -69,13 +71,23 @@ def create_app(test_config=None, instance_path=None):
     app.register_blueprint(history.bp)
     app.register_blueprint(ports.bp)
 
-    logging.basicConfig(filename='brecaltest.log', level=logging.DEBUG, format='%(asctime)s | %(name)s | %(levelname)s | %(message)s')
+    log_level = getattr(logging, app.config.get("LOG_LEVEL", "DEBUG"))
-    local_db.initPool(os.path.dirname(app.instance_path))
+    log_kwargs = {"format": "%(asctime)s | %(name)s | %(levelname)s | %(message)s"}
+    if app.config.get("LOG_TO_STDERR"):
+        log_kwargs["stream"] = sys.stderr
+    else:
+        log_kwargs["filename"] = app.config.get("LOG_FILE", "brecaltest.log")
+    logging.basicConfig(level=log_level, **log_kwargs)
+
+    if app.config.get("SECRET_KEY"):
+        os.environ["SECRET_KEY"] = app.config["SECRET_KEY"]
+
+    local_db.initPool(os.path.dirname(app.instance_path), config=app.config)
     logging.info('App started')
 
     # Setup Routine jobs (e.g., reevaluation of shipcalls)
-    setup_schedule(update_shipcalls_interval_in_minutes=60)
+    setup_schedule(update_shipcalls_interval_in_minutes=app.config.get("SCHEDULE_UPDATE_SHIPCALLS_MINUTES", 60))
-    run_schedule_permanently_in_background(latency=30)
+    run_schedule_permanently_in_background(latency=app.config.get("SCHEDULE_BACKGROUND_LATENCY_SECONDS", 30))
     logging.info('Routine Jobs are defined.')
 
     return app

src/server/BreCal/api/notifications.py

@@ -14,7 +14,14 @@ def GetNotifications():
     try:
         if 'Authorization' in request.headers:
             token = request.headers.get('Authorization')
-            return impl.notifications.GetNotifications(token)
+            participant_id = None
+            if 'participant_id' in request.args:
+                try:
+                    participant_id = int(request.args.get('participant_id'))
+                except (TypeError, ValueError):
+                    return create_dynamic_exception_response(ex=None, status_code=400, message="participant_id must be an integer")
+
+            return impl.notifications.GetNotifications(token, participant_id=participant_id)
         else:
             return create_dynamic_exception_response(ex=None, status_code=403, message="not authenticated")
 

src/server/BreCal/api/shipcalls.py

@@ -89,7 +89,7 @@ def PutShipcalls():
 
         # validate the PUT shipcall data and the user's authority
         InputValidationShipcall.evaluate_put_data(user_data, loadedModel, content)
-        return impl.shipcalls.PutShipcalls(loadedModel)
+        return impl.shipcalls.PutShipcalls(loadedModel, content)
 
     except ValidationError as ex:
         return create_validation_error_response(ex=ex, status_code=400)

src/server/BreCal/api/times.py

@@ -72,7 +72,7 @@ def PutTimes():
 
         # validate the request
         InputValidationTimes.evaluate_put_data(user_data, loadedModel, content)
-        return impl.times.PutTimes(loadedModel)
+        return impl.times.PutTimes(loadedModel, content)
 
     except ValidationError as ex:
         return create_validation_error_response(ex=ex, status_code=400)

src/server/BreCal/api/user.py

@@ -7,12 +7,17 @@ from marshmallow import ValidationError
 from . import verify_if_request_is_json
 from BreCal.validators.validation_error import create_dynamic_exception_response, create_validation_error_response
 
+import json
+import logging
+import traceback
+
 bp = Blueprint('user', __name__)
 
 @bp.route('/user', methods=['put'])
 @auth_guard() # no restriction by role
 def PutUser():
 
+    content = None
     try:
         verify_if_request_is_json(request)
 
@@ -21,9 +26,11 @@ def PutUser():
         return impl.user.PutUser(loadedModel)
 
     except ValidationError as ex:
+        logging.warning("UserSchema validation failed. Payload=%s", json.dumps(content, default=str))
         return create_validation_error_response(ex=ex, status_code=400)
 
     except Exception as ex:
+        logging.error("UserSchema load failed. Payload=%s\n%s", json.dumps(content, default=str), traceback.format_exc())
         return create_dynamic_exception_response(ex=None, status_code=400, message="bad format")
 
 

src/server/BreCal/database/sql_queries.py

@@ -281,10 +281,8 @@ class SQLQuery():
 
     def get_next24hrs_shipcalls()->str:
         query = ("SELECT s.id as id, ship.name as name FROM shipcall s INNER JOIN ship ON s.ship_id = ship.id LEFT JOIN times t on t.shipcall_id = s.id AND t.participant_type = 8 " + \
-                 "WHERE (type = 1 AND ((t.id IS NOT NULL AND t.eta_berth >= NOW() AND t.eta_berth < (NOW() + INTERVAL 1 DAY))" + \
+                 "WHERE (type = 1 AND (COALESCE(t.eta_berth, eta) >= NOW() AND COALESCE(t.eta_berth, eta) < (NOW() + INTERVAL 1 DAY)))" + \
-                 "OR (eta >= NOW() AND eta < (NOW() + INTERVAL 1 DAY)))) OR " + \
+                 "OR ((type = 2 OR type = 3) AND (COALESCE(t.etd_berth, etd) >= NOW() AND COALESCE(t.etd_berth, etd) < (NOW() + INTERVAL 1 DAY)))"
-                 "((type = 2 OR type = 3) AND ((t.id IS NOT NULL AND t.etd_berth >= NOW() AND " + \
-                 "t.etd_berth < (NOW() + INTERVAL 1 DAY)) OR (etd >= NOW() AND etd < (NOW() + INTERVAL 1 DAY))))" + \
                  "AND s.canceled = 0")
         return query
 

src/server/BreCal/impl/berths.py

@@ -10,6 +10,7 @@ def GetBerths(options):
     No parameters, gets all entries
     """
 
+    pooledConnection = None
     try:
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)

src/server/BreCal/impl/history.py

@@ -16,6 +16,8 @@ def GetHistory(options):
         options["shipcall_id"]: **Id of shipcall**.
     """
 
+    pooledConnection = None
+    data = []
     try:
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
@@ -26,10 +28,6 @@ def GetHistory(options):
             data = commands.query("SELECT id, participant_id, shipcall_id, timestamp, eta, type, operation FROM history WHERE shipcall_id = ?shipcallid?",
                                   model=History.from_query_row,
                                   param={"shipcallid" : options["shipcall_id"]})
-
-
-        pooledConnection.close()
-
     except Exception as ex:
         pdb.pm()
         logging.error(ex)
@@ -37,6 +35,9 @@ def GetHistory(options):
         result = {}
         result["error_field"] = "call failed"
         return json.dumps("call failed"), 500
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
     return json.dumps(data, default=model.obj_dict), 200, {'Content-Type': 'application/json; charset=utf-8'}
 

src/server/BreCal/impl/login.py

@@ -6,14 +6,16 @@ import bcrypt
 from ..schemas import model
 from .. import local_db
 from ..services import jwt_handler
-from BreCal.database.sql_queries import SQLQuery
+
 
 
 def GetUser(options):
 
+    pooledConnection = None
+    
     try:
         if "password" in options and "username" in options:
-            hash = bcrypt.hashpw(options["password"].encode('utf-8'), bcrypt.gensalt( 12 )).decode('utf8')
+            
             pooledConnection = local_db.getPoolConnection()
             commands = pydapper.using(pooledConnection)
             # query = SQLQuery.get_user()
@@ -37,7 +39,7 @@ def GetUser(options):
                         "notify_whatsapp": data[0].notify_whatsapp,
                         "notify_signal": data[0].notify_signal,
                         "notify_popup": data[0].notify_popup,
-                        "notify_on": model.bitflag_to_list(data[0].notify_event)
+                        "notify_on": model.notification_types_to_names(model.bitflag_to_list(data[0].notify_event))
                     }
                     token = jwt_handler.generate_jwt(payload=result, lifetime=120) # generate token valid 60 mins
                     result["token"] = token # add token to user data
@@ -63,7 +65,3 @@ def GetUser(options):
     finally:
         if pooledConnection is not None:
             pooledConnection.close()
-
-# $2b$12$uWLE0r32IrtCV30WkMbVwOdltgeibymZyYAf4ZnQb2Bip8hrkGGwG
-# $2b$12$.vEapj9xU8z0RK0IpIGeYuRIl0ktdMt4XdJQBhVn.3K2hmvm7qD3y
-# $2b$12$yL3PiseU70ciwEuMVM4OtuMwR6tNuIT9vvBiBG/uyMrPxa16E2Zqu

src/server/BreCal/impl/notifications.py

@@ -6,18 +6,22 @@ from ..schemas import model
 from .. import local_db
 from BreCal.database.sql_queries import SQLQuery
 
-def GetNotifications(token):
+def GetNotifications(token, participant_id=None):
     """
-    No parameters, gets all entries
+    Optional filtering by participant_id. Returns delivered (level=2) notifications.
     """
 
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
-        data = commands.query("SELECT id, shipcall_id, participant_id, level, type, message, created, modified FROM notification " +
+        query = "SELECT id, shipcall_id, participant_id, level, type, message, created, modified FROM notification WHERE level = 2"
-                              "WHERE level = 2", model=model.Notification.from_query_row)
+        params = {}
-        pooledConnection.close()
+        if participant_id is not None:
+            query += " AND participant_id = ?participant_id?"
+            params["participant_id"] = participant_id
+
+        data = commands.query(query, model=model.Notification.from_query_row, param=params if params else None)
 
     except Exception as ex:
         logging.error(ex)
@@ -25,6 +29,9 @@ def GetNotifications(token):
         result = {}
         result["error_field"] = "call failed"
         return json.dumps(result), 500, {'Content-Type': 'application/json; charset=utf-8'}
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
     return json.dumps(data, default=model.obj_dict), 200, {'Content-Type': 'application/json; charset=utf-8'}
 

src/server/BreCal/impl/participant.py

@@ -12,6 +12,7 @@ def GetParticipant(options):
         options["user_id"]: **Id of user**. *Example: 2*. User id returned by login call.
     """
 
+    pooledConnection = None
     try:
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)

src/server/BreCal/impl/ports.py

@@ -11,6 +11,7 @@ def GetPorts(token):
     No parameters, gets all entries
     """
 
+    pooledConnection = None
     try:
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)

src/server/BreCal/impl/shipcalls.py

@@ -18,8 +18,8 @@ def GetShipcalls(options):
     No parameters, gets all entries
     """
 
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
         # query = SQLQuery.get_shipcalls(options)
@@ -70,8 +70,8 @@ def PostShipcalls(schemaModel):
     """
 
     # This creates a *new* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -185,15 +185,15 @@ def PostShipcalls(schemaModel):
             pooledConnection.close()
 
 
-def PutShipcalls(schemaModel):
+def PutShipcalls(schemaModel, original_payload=None):
     """
 
     :param schemaModel: The deserialized dict of the request
     """
 
      # This updates an *existing* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -205,15 +205,16 @@ def PutShipcalls(schemaModel):
 
         theshipcall = commands.query_single_or_default("SELECT * FROM shipcall where id = ?id?", sentinel, param={"id" : schemaModel["id"]})
         if theshipcall is sentinel:
-            pooledConnection.close()
             return json.dumps("no such record"), 404, {'Content-Type': 'application/json; charset=utf-8'}
         
         was_canceled = theshipcall["canceled"]
 
-        # query = SQLQuery.get_shipcall_put(schemaModel)
+        provided_keys = set(original_payload.keys()) if isinstance(original_payload, dict) else None
-        query = "UPDATE shipcall SET "
+
-        isNotFirst = False
+        update_clauses = []
         for key in schemaModel.keys():
+            if provided_keys is not None and key not in provided_keys:
+                continue
             param_key = key
             if key == "id":
                 continue
@@ -235,19 +236,19 @@ def PutShipcalls(schemaModel):
                 param_key = "evaluation_value"
             if key == "evaluation_value":
                 continue
-            if isNotFirst:
+            update_clauses.append(f"{key} = ?{param_key}?")
-                 query += ", "
-            isNotFirst = True
-            query += key + " = ?" + param_key + "? "
 
-        query += "WHERE id = ?id?"
+        if update_clauses:
+            query = "UPDATE shipcall SET " + ", ".join(update_clauses) + " WHERE id = ?id?"
+            commands.execute(query, param=schemaModel)
 
-        affected_rows = commands.execute(query, param=schemaModel)
+        ship_id_value = schemaModel.get("ship_id") if (provided_keys is None or "ship_id" in provided_keys) else theshipcall["ship_id"]
 
-        shipdata = get_ship_data_for_id(schemaModel["ship_id"])
+        shipdata = get_ship_data_for_id(ship_id_value)
         message = shipdata['name']
-        if "type_value" in schemaModel:
+        type_value = schemaModel.get("type_value") if (provided_keys is None or "type" in provided_keys) else theshipcall["type"]
-            match schemaModel["type_value"]:
+        if type_value is not None:
+            match type_value:
                 case 1:
                     message += " [ARRIVAL]"
                 case 2:
@@ -259,6 +260,9 @@ def PutShipcalls(schemaModel):
         pquery = "SELECT id, participant_id, type FROM shipcall_participant_map where shipcall_id = ?id?"
         pdata = commands.query(pquery,param={"id" : schemaModel["id"]}) # existing list of assignments
 
+        if schemaModel.get("participants") is None:
+            schemaModel["participants"] = []
+
         # loop across passed participant ids, creating entries for those not present in pdata        
 
         existing_notifications = get_notification_for_shipcall_and_type(schemaModel["id"], 1) # type = 1 is assignment
@@ -307,8 +311,9 @@ def PutShipcalls(schemaModel):
                             commands.execute(nquery, param={"shipcall_id" : schemaModel["id"], "participant_id" : elem["participant_id"], "message" : message})
                         break
         
-        if schemaModel["canceled"] is not None:
+        canceled_value = schemaModel.get("canceled")
-            if schemaModel["canceled"] and not was_canceled:
+        if canceled_value is not None:
+            if canceled_value and not was_canceled:
                 # create a canceled notification for all currently assigned participants
                 stornoNotificationQuery = "INSERT INTO notification (shipcall_id, participant_id, level, type, message) VALUES (?shipcall_id?, ?participant_id?, 0, 7, ?message?)"
                 for participant_assignment in schemaModel["participants"]:

src/server/BreCal/impl/ships.py

@@ -11,8 +11,8 @@ def GetShips(token):
     No parameters, gets all entries
     """
 
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
         # query = SQLQuery.get_ships()
@@ -44,8 +44,8 @@ def PostShip(schemaModel):
     # TODO: Validate the incoming data
 
     # This creates a *new* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -83,8 +83,6 @@ def PostShip(schemaModel):
         # new_id = commands.execute_scalar(nquery)
         new_id = commands.execute_scalar("select last_insert_id()")
 
-        pooledConnection.close()
-
         return json.dumps({"id" : new_id}), 201, {'Content-Type': 'application/json; charset=utf-8'}
 
     except Exception as ex:
@@ -93,6 +91,9 @@ def PostShip(schemaModel):
         result = {}
         result["error_field"] = "call failed"
         return json.dumps(result), 500, {'Content-Type': 'application/json; charset=utf-8'}
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
 
 def PutShip(schemaModel):
@@ -101,8 +102,8 @@ def PutShip(schemaModel):
     """
 
     # This updates an *existing* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -125,8 +126,6 @@ def PutShip(schemaModel):
 
         affected_rows = commands.execute(query, param=schemaModel)
 
-        pooledConnection.close()
-
         return json.dumps({"id" : schemaModel["id"]}), 200, {'Content-Type': 'application/json; charset=utf-8'}
 
     except Exception as ex:
@@ -135,6 +134,9 @@ def PutShip(schemaModel):
         result = {}
         result["error_field"] = "call failed"
         return json.dumps(result), 500, {'Content-Type': 'application/json; charset=utf-8'}
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
 
 def DeleteShip(options):
@@ -143,16 +145,14 @@ def DeleteShip(options):
         options["id"]
 
     """
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
         # query = SQLQuery.get_ship_delete_by_id()
         # affected_rows = commands.execute(query, param={"id" : options["id"]})
         affected_rows = commands.execute("UPDATE ship SET deleted = 1 WHERE id = ?id?", param={"id" : options["id"]})
 
-        pooledConnection.close()
-
         if affected_rows == 1:
             return json.dumps({"id" : options["id"]}), 200, {'Content-Type': 'application/json; charset=utf-8'}
 
@@ -166,3 +166,6 @@ def DeleteShip(options):
         result = {}
         result["error_field"] = "call failed"
         return json.dumps(result), 500, {'Content-Type': 'application/json; charset=utf-8'}
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()

src/server/BreCal/impl/times.py

@@ -18,8 +18,8 @@ def GetTimes(options):
 
     """
 
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
         # query = SQLQuery.get_times()
@@ -28,7 +28,6 @@ def GetTimes(options):
                               "zone_entry, zone_entry_fixed, operations_start, operations_end, remarks, shipcall_id, participant_id, " +
                               "berth_id, berth_info, pier_side, participant_type, created, modified, ata, atd, eta_interval_end, etd_interval_end FROM times " +
                               "WHERE times.shipcall_id = ?scid?", model=model.Times, param={"scid" : options["shipcall_id"]})
-        pooledConnection.close()
 
     except Exception as ex:
         logging.error(traceback.format_exc())
@@ -38,6 +37,10 @@ def GetTimes(options):
         result["error_field"] = "call failed"
         return json.dumps(result), 500, {'Content-Type': 'application/json; charset=utf-8'}
 
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
+
     return json.dumps(data, default=model.obj_dict), 200, {'Content-Type': 'application/json; charset=utf-8'}
 
 
@@ -51,8 +54,8 @@ def PostTimes(schemaModel):
     # TODO: Validate the upload data
 
     # This creates a *new* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -112,36 +115,45 @@ def PostTimes(schemaModel):
             pooledConnection.close()
 
 
-def PutTimes(schemaModel):
+def PutTimes(schemaModel, original_payload=None):
     """
 
     :param schemaModel: The deserialized model of the record to be inserted
     """
 
     # This updates an *existing* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
-        query = "UPDATE times SET "
+        sentinel = object()
-        isNotFirst = False
+        existing_times = commands.query_single_or_default("SELECT * FROM times WHERE id = ?id?", sentinel, param={"id": schemaModel["id"]})
+        if existing_times is sentinel:
+            return json.dumps("no such record"), 404, {'Content-Type': 'application/json; charset=utf-8'}
+
+        provided_keys = set(original_payload.keys()) if isinstance(original_payload, dict) else None
+
+        if "shipcall_id" not in schemaModel or (provided_keys is not None and "shipcall_id" not in provided_keys):
+            schemaModel["shipcall_id"] = existing_times["shipcall_id"]
+
+        schemaModel = {k:v.value if isinstance(v, (Enum, Flag)) else v for k,v in schemaModel.items()}
+
+        update_clauses = []
         for key in schemaModel.keys():
+            if provided_keys is not None and key not in provided_keys:
+                continue
             if key == "id":
                 continue
             if key == "created":
                 continue
             if key == "modified":
                 continue
-            if isNotFirst:
+            update_clauses.append(f"{key} = ?{key}?")
-                 query += ", "
-            isNotFirst = True
-            query += key + " = ?" + key + "? "
 
-        query += "WHERE id = ?id?"
+        if update_clauses:
-
+            query = "UPDATE times SET " + ", ".join(update_clauses) + " WHERE id = ?id?"
-        schemaModel = {k:v.value if isinstance(v, (Enum, Flag)) else v for k,v in schemaModel.items()}
+            commands.execute(query, param=schemaModel)
-        affected_rows = commands.execute(query, param=schemaModel)
 
         # apply 'Traffic Light' evaluation to obtain 'GREEN', 'YELLOW' or 'RED' evaluation state. The function internally updates the mysql database 'shipcall'
         evaluate_shipcall_state(mysql_connector_instance=pooledConnection, shipcall_id=schemaModel["shipcall_id"]) # every times data object refers to the 'shipcall_id'
@@ -177,8 +189,8 @@ def DeleteTimes(options):
         options["id"]
 
     """
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
         shipcall_id = commands.execute_scalar("SELECT shipcall_id FROM times WHERE id = ?id?", param={"id" : options["id"]})

src/server/BreCal/impl/user.py

@@ -14,8 +14,8 @@ def PutUser(schemaModel):
     """
 
      # This updates an *existing* entry
+    pooledConnection = None
     try:
-
         pooledConnection = local_db.getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
@@ -26,7 +26,6 @@ def PutUser(schemaModel):
         # theuser = commands.query_single_or_default(query, sentinel, param={"id" : schemaModel["id"]}, model=model.User)
         theuser = commands.query_single_or_default("SELECT * FROM user where id = ?id?", sentinel, param={"id" : schemaModel["id"]}, model=model.User)
         if theuser is sentinel:
-            pooledConnection.close()
             # #TODO: result = {"message":"no such record"} -> json.dumps
             return json.dumps("no such record"), 404, {'Content-Type': 'application/json; charset=utf-8'}
 

src/server/BreCal/local_db.py

@@ -1,58 +1,90 @@
 import mysql.connector
+from mysql.connector import pooling
 import pydapper
 import logging
 import json
 import os
 import sys
 from BreCal.schemas import defs
+
 config_path = None
+secure_dir = None
+_connection_pool = None
 
-def initPool(instancePath, connection_filename="connection_data_test.json"):
+
+def _load_json(path):
+    with open(path, encoding="utf-8") as fh:
+        return json.load(fh)
+
+
+def _build_pool_config(connection_data, pool_name, pool_size):
+    pool_config = dict(connection_data)
+    pool_config.setdefault("pool_name", pool_name)
+    pool_config.setdefault("pool_size", pool_size)
+    return pool_config
+
+
+def initPool(instancePath, config=None, connection_filename="connection_data_prod.json",
+             credentials_file="email_credentials_test.json", pool_name="brecal_pool", pool_size=10,
+             secure_directory=None):
+    """
+    Initialize the MySQL connection pool and load email credentials.
+    """
+    global config_path, secure_dir, _connection_pool
     try:
-        global config_path
+        if config:
-        if(config_path == None):
+            connection_filename = config.get("DB_CONNECTION_FILE", connection_filename)
-            config_path = os.path.join(instancePath,f'../../../secure/{connection_filename}') #connection_data_test.json');
+            credentials_file = config.get("EMAIL_CREDENTIALS_FILE", credentials_file)
+            pool_name = config.get("DB_POOL_NAME", pool_name)
+            pool_size = config.get("DB_POOL_SIZE", pool_size)
+            secure_directory = config.get("SECURE_DIR", secure_directory)
 
-        # config_path = "E:/temp/connection_data.json"
+        if secure_dir is None:
-        print (config_path)
+            secure_dir = secure_directory if secure_directory else os.path.join(instancePath, '../../../secure')
 
+        if config_path is None:
+            config_path = os.path.join(secure_dir, connection_filename)
+
+        print(config_path)
         if not os.path.exists(config_path):
-            print ('cannot find ' + os.path.abspath(config_path))
+            print('cannot find ' + os.path.abspath(config_path))
             print("instance path", instancePath)
             sys.exit(1)
 
-        f = open(config_path);
+        connection_data = _load_json(config_path)
-        connection_data = json.load(f)
+        if _connection_pool is None:
-        f.close()
+            pool_config = _build_pool_config(connection_data, pool_name, pool_size)
+            _connection_pool = pooling.MySQLConnectionPool(**pool_config)
 
-        conn_from_pool = mysql.connector.connect(**connection_data)
+        conn_from_pool = _connection_pool.get_connection()
+        try:
+            commands = pydapper.using(conn_from_pool)
+            commands.query("SELECT id from `user` LIMIT 1")
+            print("DB connection successful")
+        finally:
+            conn_from_pool.close()
 
-        commands = pydapper.using(conn_from_pool)
+        credentials_path = os.path.join(secure_dir, credentials_file)
-        data = commands.query("SELECT id from `user`")
-        print("DB connection successful")
-        conn_from_pool.close()
-
-        credentials_file = "email_credentials_test.json"
-        credentials_path = os.path.join(instancePath,f'../../../secure/{credentials_file}')
-
-        # credentials_path = "E:/temp/email_credentials_devel.json"
 
         if not os.path.exists(credentials_path):
-            print ('cannot find ' + os.path.abspath(credentials_path))
+            print('cannot find ' + os.path.abspath(credentials_path))
             sys.exit(1)
 
-        f = open(credentials_path);
+        defs.email_credentials = _load_json(credentials_path)
-        defs.email_credentials = json.load(f)
-        f.close()
 
     except mysql.connector.PoolError as e:
         logging.error(f"Failed to create connection pool: {e}")
         print(e)
     except Exception as e:
+        logging.error("Failed to initialize DB pool: %s", e)
         print(e)
 
+
 def getPoolConnection():
-    global config_path
+    if _connection_pool is None:
-    f = open(config_path);
+        raise RuntimeError("Connection pool not initialized. Call initPool first.")
-    connection_data = json.load(f)
+    try:
-    return mysql.connector.connect(**connection_data)
+        return _connection_pool.get_connection()
+    except mysql.connector.PoolError as exc:
+        logging.error("Connection pool exhausted: %s", exc)
+        raise

src/server/BreCal/msg/msg_types.json

@@ -3,49 +3,42 @@
         "type" : 1,
         "color" : "#0867ec",
         "name" : "assignment",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Nominierung"
     },
     {
         "type" : 2,
         "color" : "#ea5c00",
         "name" : "next24h",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Morgenrunde relevant"
     },
     {
         "type" : 3,
         "color" : "#f34336",
         "name" : "time_conflict",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Zeitlicher Konflikt"
     },
     {
         "type" : 4,
         "color" : "#28b532",
         "name" : "time_conflict_resolved",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Zeitlicher Konflikt gelöst"
     },
     {
         "type" : 5,
         "color" : "#a8a8a8",
         "name" : "unassigned",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Nominierung abgewählt"
     },
     {
         "type" : 6,
         "color" : "#a8a800",
         "name" : "missing_data",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Fehlende Daten"
     },
     {
         "type" : 7,
         "color" : "#808070",
         "name" : "cancelled",
-        "link" : "https://www.bremen-calling.de/",
         "msg_text" : "Storno"
     }
 ]

src/server/BreCal/schemas/model.py

@@ -5,7 +5,7 @@ from marshmallow_enum import EnumField
 from enum import IntEnum
 
 from marshmallow_dataclass import dataclass
-from typing import List
+from typing import Iterable, List
 
 import json
 import re
@@ -85,19 +85,31 @@ class NotificationType(IntEnum):
     def _missing_(cls, value):
         return cls.undefined
 
-def bitflag_to_list(bitflag: int) -> list[NotificationType]:
+def bitflag_to_list(bitflag: int | None) -> list[NotificationType]:
+    """Converts an integer bitflag to a list of NotificationType enums."""
     if bitflag is None:
         return []
-    """Converts an integer bitflag to a list of NotificationType enums."""
     return [nt for nt in NotificationType if bitflag & (1 << (nt.value - 1))]
 
-def list_to_bitflag(notifications: fields.List) -> int:
+def list_to_bitflag(notifications: Iterable[NotificationType | str | int] | None) -> int:
-    """Converts a list of NotificationType enums to an integer bitflag."""
+    """Converts a list of NotificationType enums (or their names/values) to an integer bitflag."""
-    try:
+    if not notifications:
-        iter(notifications)
-        return sum(1 << (nt.value - 1) for nt in notifications)
-    except TypeError as te:
         return 0
+    bitflag = 0
+    for nt in notifications:
+        enum_val = None
+        if isinstance(nt, NotificationType):
+            enum_val = nt
+        elif isinstance(nt, str):
+            enum_val = NotificationType[nt]
+        else:
+            enum_val = NotificationType(nt)
+        bitflag |= 1 << (enum_val.value - 1)
+    return bitflag
+
+def notification_types_to_names(notifications: Iterable[NotificationType]) -> list[str]:
+    """Render NotificationType values as their names for API responses."""
+    return [nt.name for nt in notifications]
 
 
 class ShipcallType(IntEnum):
@@ -201,7 +213,7 @@ class Participant(Schema):
     ports: List[int] = field(default_factory=list)
 
     @validates("type")
-    def validate_type(self, value):
+    def validate_type(self, value, **kwargs):
         # e.g., when an IntFlag has the values 1,2,4; the maximum valid value is 7
         max_int = sum([int(val) for val in list(ParticipantType._value2member_map_.values())])
         min_int = 0
@@ -212,7 +224,7 @@ class Participant(Schema):
 
 
     @validates("flags")
-    def validate_flags(self, value):
+    def validate_flags(self, value, **kwargs):
         # e.g., when an IntFlag has the values 1,2,4; the maximum valid value is 7
         max_int = sum([int(val) for val in list(ParticipantFlag._value2member_map_.values())])
         min_int = 0
@@ -237,7 +249,7 @@ class ShipcallSchema(Schema):
     id = fields.Integer(required=True)
     ship_id = fields.Integer(required=True)
     port_id = fields.Integer(required=True)
-    type = fields.Enum(ShipcallType, default=ShipcallType.undefined)
+    type = fields.Enum(ShipcallType, load_default=ShipcallType.undefined, dump_default=ShipcallType.undefined)
     eta = fields.DateTime(required=False, allow_none=True)
     voyage = fields.String(allow_none=True, required=False, validate=[validate.Length(max=16)])
     etd = fields.DateTime(required=False, allow_none=True)
@@ -258,7 +270,7 @@ class ShipcallSchema(Schema):
     anchored = fields.Bool(required=False, allow_none=True)
     moored_lock = fields.Bool(required=False, allow_none=True)
     canceled = fields.Bool(required=False, allow_none=True)
-    evaluation = fields.Enum(EvaluationType,  required=False, allow_none=True, default=EvaluationType.undefined)
+    evaluation = fields.Enum(EvaluationType,  required=False, allow_none=True, load_default=EvaluationType.undefined, dump_default=ShipcallType.undefined)
     evaluation_message = fields.Str(allow_none=True, required=False)
     evaluation_time = fields.DateTime(required=False, allow_none=True)
     evaluation_notifications_sent = fields.Bool(required=False, allow_none=True)
@@ -281,7 +293,7 @@ class ShipcallSchema(Schema):
         return data
 
     @validates("type")
-    def validate_type(self, value):
+    def validate_type(self, value, **kwargs):
         valid_shipcall_type = int(value) in [item.value for item in ShipcallType]
 
         if not valid_shipcall_type:
@@ -418,7 +430,7 @@ class TimesSchema(Schema):
     berth_info = fields.String(required=False, allow_none=True, validate=[validate.Length(max=512)])
     pier_side = fields.Bool(required=False, allow_none = True)
     shipcall_id = fields.Integer(required=True)
-    participant_type = fields.Integer(Required = False, allow_none=True)# TODO: could become Enum. # participant_type = fields.Enum(ParticipantType, required=False, allow_none=True, default=ParticipantType.undefined) #fields.Integer(required=False, allow_none=True)
+    participant_type = fields.Integer(required = False, allow_none=True) # TODO: could become Enum
     ata = fields.DateTime(required=False, allow_none=True)
     atd = fields.DateTime(required=False, allow_none=True)
     eta_interval_end = fields.DateTime(required=False, allow_none=True)
@@ -427,7 +439,7 @@ class TimesSchema(Schema):
     modified = fields.DateTime(required=False, allow_none=True)
 
     @validates("participant_type")
-    def validate_participant_type(self, value):
+    def validate_participant_type(self, value, **kwargs):
         # #TODO: it may also make sense to block multi-assignments, whereas a value could be BSMD+AGENCY
         # while the validation fails when one of those multi-assignments is BSMD, it passes in cases,
         # such as AGENCY+PILOT
@@ -440,56 +452,56 @@ class TimesSchema(Schema):
             raise ValidationError({"participant_type":f"the participant_type must not be .BSMD"})
 
     @validates("eta_berth")
-    def validate_eta_berth(self, value):
+    def validate_eta_berth(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("etd_berth")
-    def validate_etd_berth(self, value):
+    def validate_etd_berth(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("lock_time")
-    def validate_lock_time(self, value):
+    def validate_lock_time(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("zone_entry")
-    def validate_zone_entry(self, value):
+    def validate_zone_entry(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("operations_start")
-    def validate_operations_start(self, value):
+    def validate_operations_start(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("operations_end")
-    def validate_operations_end(self, value):
+    def validate_operations_end(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("eta_interval_end")
-    def validate_eta_interval_end(self, value):
+    def validate_eta_interval_end(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
         return
 
     @validates("etd_interval_end")
-    def validate_etd_interval_end(self, value):
+    def validate_etd_interval_end(self, value, **kwargs):
         # violation when time is not in the future, but also does not exceed a threshold for the 'reasonable' future
         # when 'value' is 'None', a ValidationError is not issued.
         valid_time = validate_time_is_in_not_too_distant_future(raise_validation_error=True, value=value, months=12)
@@ -516,14 +528,14 @@ class UserSchema(Schema):
     notify_on = fields.List(fields.Enum(NotificationType), required=False, allow_none=True)
 
     @validates("user_phone")
-    def validate_user_phone(self, value):
+    def validate_user_phone(self, value, **kwargs):
         if value is not None:
             valid_characters = list(map(str,range(0,10)))+["+", " "]
             if not all([v in valid_characters for v in value]):
                 raise ValidationError({"user_phone":f"one of the phone number values is not valid."})
 
     @validates("user_email")
-    def validate_user_email(self, value):
+    def validate_user_email(self, value, **kwargs):
         if value and not re.match(r"[^@]+@[^@]+\.[^@]+", value):
             raise ValidationError({"user_email":f"invalid email address"})
 
@@ -573,14 +585,14 @@ class User:
     notify_popup: bool
     created: datetime
     modified: datetime
-    ports: List[NotificationType] = field(default_factory=list)
+    notify_event: int | None = 0
-    notify_event: List[NotificationType] = field(default_factory=list)
 
     def __hash__(self):
         return hash(id)
 
-    def wants_notification(self, notification_type: NotificationType):
+    def wants_notifications(self, notification_type: NotificationType):
-        return notification_type in self.notify_event
+        events = bitflag_to_list(self.notify_event)
+        return notification_type in events
 
 @dataclass
 class Ship:
@@ -611,15 +623,15 @@ class ShipSchema(Schema):
     participant_id = fields.Int(allow_none=True, required=False)
     length = fields.Float(allow_none=True, required=False, validate=[validate.Range(min=0, max=1000, min_inclusive=False, max_inclusive=False)])
     width = fields.Float(allow_none=True, required=False, validate=[validate.Range(min=0, max=100, min_inclusive=False, max_inclusive=False)])
-    is_tug = fields.Bool(allow_none=True, required=False, default=False)
+    is_tug = fields.Bool(allow_none=True, required=False, load_default=False, dump_default=False)
     bollard_pull = fields.Int(allow_none=True, required=False)
     eni = fields.Int(allow_none=True, required=False)
     created = fields.DateTime(allow_none=True, required=False)
     modified = fields.DateTime(allow_none=True, required=False)
-    deleted = fields.Bool(allow_none=True, required=False, default=False)
+    deleted = fields.Bool(allow_none=True, required=False, load_default=False, dump_default=False)
 
     @validates("name")
-    def validate_name(self, value):
+    def validate_name(self, value, **kwargs):
         character_length = len(str(value))
         if character_length<1:
             raise ValidationError({"name":f"'name' argument should have at least one character"})
@@ -631,7 +643,7 @@ class ShipSchema(Schema):
         return
 
     @validates("imo")
-    def validate_imo(self, value):
+    def validate_imo(self, value, **kwargs):
         value = str(value).zfill(7) # 1 becomes '0000001' (7 characters). 12345678 becomes '12345678' (8 characters)
         imo_length = len(value)
         if imo_length != 7:
@@ -639,7 +651,7 @@ class ShipSchema(Schema):
         return
 
     @validates("callsign")
-    def validate_callsign(self, value):
+    def validate_callsign(self, value, **kwargs):
         if value is not None:
             callsign_length = len(str(value))
             if callsign_length>8:

src/server/BreCal/services/email_handling.py

@@ -38,6 +38,9 @@ class EmailHandler():
 
         self.server = smtplib.SMTP_SSL(self.mail_server, self.mail_port) # alternatively, SMTP
 
+        # set the following to 0 to avoid log spamming
+        self.server.set_debuglevel(1) # 0: no debug, 1: debug
+
     def check_state(self):
         """check, whether the server login took place and is open."""
         try:

src/server/BreCal/services/schedule_routines.py

@@ -31,6 +31,7 @@ def UpdateShipcalls(options:dict = {'past_days':2}):
     options:
         key: 'past_days'. Is used to execute a filtered query of all available shipcalls. Defaults to 2 (days)
     """
+    pooledConnection = None
     try:
         pooledConnection = getPoolConnection()
         commands = pydapper.using(pooledConnection)
@@ -49,10 +50,11 @@ def UpdateShipcalls(options:dict = {'past_days':2}):
             # apply 'Traffic Light' evaluation to obtain 'GREEN', 'YELLOW' or 'RED' evaluation state. The function internally updates the mysql database
             evaluate_shipcall_state(mysql_connector_instance=pooledConnection, shipcall_id=shipcall_id) # new_id (last insert id) refers to the shipcall id
 
-        pooledConnection.close()
-
     except Exception as ex:
         logging.error(ex)
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
     return
 
 def UpdateNotifications(cooldown_in_mins:int=10):
@@ -61,6 +63,7 @@ def UpdateNotifications(cooldown_in_mins:int=10):
     notification is updated to state 1 and a notification is received by the user
     """
 
+    pooledConnection = None
     try:
         pooledConnection = getPoolConnection()
         commands = pydapper.using(pooledConnection)
@@ -70,32 +73,39 @@ def UpdateNotifications(cooldown_in_mins:int=10):
         for notification in data:
             commands.execute("UPDATE notification SET level = 1 WHERE id = ?id?", param={"id":notification.id})
 
-        pooledConnection.close()
     except Exception as ex:
         logging.error(ex)
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
 def ClearNotifications(max_age_in_days:int=3):
     """
     This function clears all notifications in state ("level") 2 that are older than x days
     """
 
+    pooledConnection = None
     try:
         pooledConnection = getPoolConnection()
         commands = pydapper.using(pooledConnection)
 
         query = f"DELETE FROM notification WHERE level = 2 and created < TIMESTAMP(NOW() - INTERVAL {max_age_in_days} DAY)"
         result = commands.execute(query)
-        pooledConnection.close()
         if(result > 0):
             logging.info(f"Deleted {result} notifications")
 
     except Exception as ex:
         logging.error(ex)
+    finally:
+        if pooledConnection is not None:
+            pooledConnection.close()
 
 def SendEmails(email_dict):
     """
     This function sends emails to all users in the emaildict
     """
+    pooledConnection = None
+    conn = None
     try:
         pooledConnection = getPoolConnection()
         commands = pydapper.using(pooledConnection)
@@ -114,11 +124,11 @@ def SendEmails(email_dict):
             defs.message_types = json.load(f)
             f.close()
 
-        for user, notifications in email_dict.items():
+        for user_email, notifications in email_dict.items():
             msg = EmailMessage()
             msg["Subject"] = '[Bremen calling] Notification'
             msg["From"] = defs.email_credentials["sender"]
-            msg["To"] = user.user_email
+            msg["To"] = user_email
 
             with open(os.path.join(current_path,'../msg/notification_template.html'), mode="r", encoding="utf-8") as file:
                 body = file.read()
@@ -135,7 +145,8 @@ def SendEmails(email_dict):
                 with open(os.path.join(current_path,'../msg/notification_element.html'), mode="r", encoding="utf-8") as file:
                     element = file.read()
                 element = element.replace("[[color]]", message_type["color"])
-                element = element.replace("[[link]]", message_type["link"])
+                linktext = defs.email_credentials["url_template"] + str(notification.shipcall_id)
+                element = element.replace("[[link]]", linktext)
 
                 # We want to show the following information for each notification:
                 # Ship-name, Arr/Dep/Shift, ETA/ETD, berth
@@ -170,17 +181,20 @@ def SendEmails(email_dict):
             body = body.replace("[[NOTIFICATION_ELEMENTS]]", replacement)
             msg.set_content(body, subtype='html', charset='utf-8', cte='8bit')
 
-            conn.sendmail(defs.email_credentials["sender"], user.user_email, msg.as_string())
+            conn.sendmail(defs.email_credentials["sender"], user_email, msg.as_string())
 
     except Exception as ex:
         logging.error(ex)
     finally:
         if conn is not None:
             conn.quit()
+        if pooledConnection is not None:
+            pooledConnection.close()
 
 
 def SendNotifications():
     # perhaps this will be moved somewhere else later
+    pooledConnection = None
     try:
         # find all notifications in level 1
         pooledConnection = getPoolConnection()
@@ -197,10 +211,10 @@ def SendNotifications():
         email_dict = dict()
         users_dict = dict()
         user_query = "SELECT * from user"
-        users = commands.query(user_query, model=model.User)
+        users = commands.query(user_query)
         for participant in participants:
             for user in users:
-                if user.participant_id == participant.id:
+                if user["participant_id"] == participant.id:
                     if not participant.id in users_dict:
                         users_dict[participant.id] = []
                     users_dict[participant.id].append(user)
@@ -212,33 +226,39 @@ def SendNotifications():
                 p_query = "SELECT * from shipcall_participant_map where shipcall_id = ?id?"
                 assigned_participants = commands.query(p_query, model=model.ShipcallParticipantMap, param={"id":notification.shipcall_id})
                 for assigned_participant in assigned_participants:
+                    if not assigned_participant.participant_id in users_dict:
+                        continue
                     users = users_dict[assigned_participant.participant_id]
                     for user in users:
                         # send notification to user
-                        if user.notify_email:
+                        if user["notify_email"]:
-                            if user not in email_dict:
+                            if user["user_email"] not in email_dict:
-                                email_dict[user] = []
+                                email_dict[user["user_email"]] = []
-                            email_dict[user].append(notification)
+                            if notification not in email_dict[user["user_email"]]:
-                        if user.notify_whatsapp:
+                                email_dict[user["user_email"]].append(notification)
+                        if user["notify_whatsapp"]:
                             # TBD
                             pass
-                        if user.notify_signal:
+                        if user["notify_signal"]:
                             # TBD
                             pass
             else:
-                users = users_dict[notification.participant_id]
+                if notification.participant_id in users_dict:
-                for user in users:
+                    users = users_dict[notification.participant_id]
-                    # send notification to user
+                    for user in users:
-                    if user.notify_email and user.wants_notifications(notification.type):
+                        user_notifications = model.bitflag_to_list(user["notify_event"])
-                        if user not in email_dict:
+                        # send notification to user
-                            email_dict[user] = []
+                        if user["notify_email"] and notification.type in user_notifications:
-                        email_dict[user].append(notification)
+                            if user["user_email"] not in email_dict:
-                    if user.notify_whatsapp and user.wants_notifications(notification.type):
+                                email_dict[user["user_email"]] = []
-                        # TBD
+                            if notification not in email_dict[user["user_email"]]:
-                        pass
+                                email_dict[user["user_email"]].append(notification)
-                    if user.notify_signal and user.wants_notifications(notification.type):
+                        if user["notify_whatsapp"] and notification.type in user_notifications:
-                        # TBD
+                            # TBD
-                        pass
+                            pass
+                        if user["notify_signal"] and notification.type in user_notifications:
+                            # TBD
+                            pass
 
             # mark as sent
             commands.execute("UPDATE notification SET level = 2 WHERE id = ?id?", param={"id":notification.id})
@@ -271,6 +291,7 @@ def add_function_to_schedule_send_notifications(interval_in_minutes:int=1):
     return
 
 def eval_next_24_hrs():
+    pooledConnection = None
     try:
         pooledConnection = getPoolConnection()
         commands = pydapper.using(pooledConnection)

src/server/BreCal/validators/input_validation_base.py

@@ -0,0 +1,31 @@
+import typing
+from marshmallow import ValidationError
+
+class InputValidationBase:
+    """
+    Base class for input validators. Common validation methods are grouped here.
+    """
+
+    @staticmethod
+    def check_required_fields(content:dict, required_fields:list[str]):
+        missing_fields = [field for field in required_fields if content.get(field) is None]
+        if missing_fields:
+            raise ValidationError({"required_fields": f"Missing required fields: {missing_fields}"})
+
+    @staticmethod
+    def check_if_entry_is_already_deleted(entry:dict):
+        """
+        Checks if the entry has 'deleted' set to True/1.
+        """
+        if entry.get("deleted") in [True, 1, "1"]:
+            raise ValidationError({"deleted": "The selected entry is already deleted."})
+
+    @staticmethod
+    def check_deleted_flag_on_post(content:dict):
+        """
+        Checks if 'deleted' is set to 1/True in a POST (Create) request.
+        """
+        if content.get("deleted") in [True, 1, "1"]:
+            raise ValidationError({"deleted": "Cannot create an entry with 'deleted' set to 1."})
+
+

src/server/BreCal/validators/input_validation_ship.py

@@ -18,10 +18,11 @@ from BreCal.validators.input_validation_utils import check_if_user_is_bsmd_type,
 from BreCal.database.sql_handler import execute_sql_query_standalone
 from BreCal.validators.validation_base_utils import check_if_int_is_valid_flag
 from BreCal.validators.validation_base_utils import check_if_string_has_special_characters
+from BreCal.validators.input_validation_base import InputValidationBase
 
 import werkzeug
 
-class InputValidationShip():
+class InputValidationShip(InputValidationBase):
     """
     This class combines a complex set of individual input validation functions into a joint object.
     It uses static methods, so the object does not need to be instantiated, but functions can be called immediately.
@@ -55,6 +56,13 @@ class InputValidationShip():
 
         # 3.) Check for reasonable Values (see BreCal.schemas.model.ShipSchema)
         InputValidationShip.optionally_evaluate_bollard_pull_value(content)
+
+        # 4.) No deleted flag may be set on POST
+        InputValidationShip.check_deleted_flag_on_post(content)
+
+        # 5.) Check if is_tug is null
+        InputValidationShip.check_is_tug_null(content)
+
         return
 
     @staticmethod
@@ -70,6 +78,10 @@ class InputValidationShip():
 
         # 4.) Check for reasonable Values (see BreCal.schemas.model.ShipSchema)
         InputValidationShip.optionally_evaluate_bollard_pull_value(content)
+
+        # 5.) Check if tug is null
+        InputValidationShip.check_is_tug_null(content)
+
         return
 
     @staticmethod
@@ -159,5 +171,11 @@ class InputValidationShip():
             raise ValidationError({"deleted":f"The selected ship entry is already deleted."})
         return
 
+    @staticmethod
+    def check_is_tug_null(content:dict):
+        is_tug = content.get("is_tug", None)
+        if is_tug is None:
+            raise ValidationError({"is_tug":f"The 'is_tug' property must be set to either True or False."})
+        return
 
 

src/server/BreCal/validators/input_validation_shipcall.py

@@ -17,12 +17,13 @@ from BreCal.database.sql_handler import get_assigned_participant_of_type
 from BreCal.database.sql_handler import execute_sql_query_standalone
 from BreCal.validators.validation_base_utils import check_if_int_is_valid_flag
 from BreCal.validators.validation_base_utils import check_if_string_has_special_characters
+from BreCal.validators.input_validation_base import InputValidationBase
 from BreCal.database.sql_queries import SQLQuery
 import werkzeug
 
 
 
-class InputValidationShipcall():
+class InputValidationShipcall(InputValidationBase):
     """
     This class combines a complex set of individual input validation functions into a joint object.
     It uses static methods, so the object does not need to be instantiated, but functions can be called immediately.
@@ -60,7 +61,11 @@ class InputValidationShipcall():
         InputValidationShipcall.check_participant_list_not_empty_when_user_is_agency(loadedModel)
 
         # check for reasonable values in the shipcall fields
-        InputValidationShipcall.check_shipcall_values(loadedModel, content, forbidden_keys=["evaluation", "evaluation_message"]) # "canceled"
+        InputValidationShipcall.check_shipcall_values(loadedModel, content, forbidden_keys=["evaluation", "evaluation_message", "canceled"]) # "canceled"
+
+        # check for deleted flag on POST
+        InputValidationShipcall.check_deleted_flag_on_post(content)
+
         return
 
     @staticmethod
@@ -548,12 +553,16 @@ class InputValidationShipcall():
             assigned_agency = get_assigned_participant_of_type(shipcall_id, participant_type=ParticipantType.AGENCY)
             assigned_pilot = get_assigned_participant_of_type(shipcall_id, participant_type=ParticipantType.PILOT)
             an_agency_is_assigned = True if assigned_agency is not None else False
+            a_pilot_is_assigned = True if assigned_pilot is not None else False
 
         else:
             # Agency assigned? User must belong to the assigned agency or be a BSMD user, in case the flag is set
             assigned_agency = [spm for spm in shipcall_participant_map if int(spm.type) == int(ParticipantType.AGENCY)]
             assigned_pilot = [spm for spm in shipcall_participant_map if int(spm.type) == int(ParticipantType.PILOT)]
             an_agency_is_assigned = len(assigned_agency)==1
+            a_pilot_is_assigned = len(assigned_pilot)==1
+            if a_pilot_is_assigned:
+                assigned_pilot = assigned_pilot[0]
 
             if len(assigned_agency)>1:
                 raise ValidationError({"internal_error":f"Internal error? Found more than one assigned agency for the shipcall with ID {shipcall_id}. Found: {assigned_agency}"})
@@ -569,7 +578,7 @@ class InputValidationShipcall():
             ### USER authority ###
             # determine, whether the user is a) the assigned agency or b) a BSMD participant
             user_is_assigned_agency = (user_participant_id == assigned_agency.id)
-            user_is_assigned_pilot = (user_participant_id == assigned_pilot.id)
+            user_is_assigned_pilot = a_pilot_is_assigned and (user_participant_id == assigned_pilot.id)
 
             # when the BSMD flag is set: the user must be either BSMD or the assigned agency
             # when the BSMD flag is not set: the user must be the assigned agency

src/server/BreCal/validators/input_validation_times.py

@@ -17,6 +17,7 @@ from BreCal.database.sql_queries import SQLQuery
 from BreCal.database.sql_handler import execute_sql_query_standalone
 from BreCal.database.sql_handler import get_assigned_participant_of_type
 from BreCal.database.sql_utils import get_times_data_for_id
+from BreCal.validators.input_validation_base import InputValidationBase
 from BreCal.validators.validation_base_utils import check_if_int_is_valid_flag, check_if_string_has_special_characters
 import werkzeug
 
@@ -63,7 +64,7 @@ def build_post_data_type_dependent_required_fields_dict()->dict[ShipcallType,dic
 
 
 
-class InputValidationTimes():
+class InputValidationTimes(InputValidationBase):
     """
     This class combines a complex set of individual input validation functions into a joint object.
     It uses static methods, so the object does not need to be instantiated, but functions can be called immediately.
@@ -92,6 +93,10 @@ class InputValidationTimes():
 
         # 4.) Value checking
         InputValidationTimes.check_dataset_values(user_data, loadedModel, content)
+
+        # 5.) Deleted flag may not be set on POST
+        InputValidationTimes.check_deleted_flag_on_post(content)
+
         return
 
     @staticmethod

src/server/BreCal/validators/validation_base_utils.py

@@ -1,20 +1,19 @@
 import typing
-from string import ascii_letters, digits
+import re
 
 
+_VALID = re.compile(r'^[\w &-]+$') # \w == Unicode letters+digits+underscore
+
 def check_if_string_has_special_characters(text:typing.Optional[str]):
     """
     check, whether there are any characters within the provided string, which are not found in the ascii letters or digits
-        ascii_letters: abcd (...) and ABCD (...)
-        digits: 0123 (...)
 
+    Formerly, this solution was used but was found to be too restrictive:
     Source: https://stackoverflow.com/questions/57062794/is-there-a-way-to-check-if-a-string-contains-special-characters
     User: https://stackoverflow.com/users/10035985/andrej-kesely
     returns bool
     """
-    if text is None:
+    return not _VALID.fullmatch(text) if text else False
-        return False
-    return bool(set(text).difference(ascii_letters + digits + ' '))
 
 
 def check_if_int_is_valid_flag(value, enum_object):

src/server/BreCal/validators/validation_error.py

@@ -59,7 +59,7 @@ def create_validation_error_response(ex:ValidationError, status_code:int=400, cr
 
     if create_log:
         logging.warning(ex) if ex is not None else logging.warning(message)
-        print(ex) if ex is not None else print(message)
+        # print(ex) if ex is not None else print(message)
     return (serialized_response, status_code)
 
 def create_werkzeug_error_response(ex:Forbidden, status_code:int=403, create_log:bool=True)->typing.Tuple[str,int]:
@@ -71,7 +71,7 @@ def create_werkzeug_error_response(ex:Forbidden, status_code:int=403, create_log
 
     if create_log:
         logging.warning(ex) if ex is not None else logging.warning(message)
-        print(ex) if ex is not None else print(message)
+        # print(ex) if ex is not None else print(message)
     return serialized_response, status_code
 
 def create_dynamic_exception_response(ex, status_code:int=400, message:typing.Optional[str]=None, create_log:bool=True):
@@ -83,5 +83,5 @@ def create_dynamic_exception_response(ex, status_code:int=400, message:typing.Op
 
     if create_log:
         logging.warning(ex) if ex is not None else logging.warning(message)
-        print(ex) if ex is not None else print(message)
+        # print(ex) if ex is not None else print(message)
     return (serialized_response, status_code)

src/server/BreCal/validators/validation_rules.py

@@ -52,7 +52,8 @@ class ValidationRules(ValidationRuleFunctions):
         # 'translate' all error codes into readable, human-understandable format.
         evaluation_results = [(state, self.describe_error_message(msg)) for (state, msg) in evaluation_results]
 
-        logging.info(f"Validation results for shipcall {shipcall.id}: {evaluation_results}")
+        if evaluation_results:
+            logging.info(f"Validation results for shipcall {shipcall.id}: {evaluation_results}")
 
         # check, what the maximum state flag is and return it
         evaluation_state = np.max(np.array([result[0].value for result in evaluation_results])) if len(evaluation_results)>0 else StatusFlags.GREEN.value
@@ -90,40 +91,43 @@ class ValidationRules(ValidationRuleFunctions):
         if evaluation_states_old is not None and evaluation_states_new is not None:
             if len(evaluation_states_old) == 1 and len(evaluation_states_new) == 1:
                 if evaluation_states_old[0] != evaluation_states_new[0]:
-                    pooledConnection = getPoolConnection()
+                    pooledConnection = None
-                    commands = pydapper.using(pooledConnection)
+                    try:
-                    notification_type = 3 # RED (mapped to time_conflict) 
+                        pooledConnection = getPoolConnection()
-                    if evaluation_states_new[0] == 2:
+                        commands = pydapper.using(pooledConnection)
-                        match evaluation_states_old[0]:
+                        notification_type = 3 # RED (mapped to time_conflict) 
-                            case 0:
+                        if evaluation_states_new[0] == 2:
-                                send_notification = True
+                            match evaluation_states_old[0]:
-                            case 1:
+                                case 0:
-                                send_notification = True
+                                    send_notification = True
-                        notification_type = 6 # YELLOW (mapped to missing_data)
+                                case 1:
-                    if evaluation_states_new[0] == 3:
+                                    send_notification = True
-                        match evaluation_states_old[0]:
+                            notification_type = 6 # YELLOW (mapped to missing_data)
-                            case 0:
+                        if evaluation_states_new[0] == 3:
-                                send_notification = True
+                            match evaluation_states_old[0]:
-                            case 1:
+                                case 0:
-                                send_notification = True
+                                    send_notification = True
-                            case 2:
+                                case 1:
-                                send_notification = True
+                                    send_notification = True
+                                case 2:
+                                    send_notification = True
 
-                    if send_notification:
+                        if send_notification:
-                        query = f"INSERT INTO notification (shipcall_id, type, level, message) VALUES (?shipcall_id?, {notification_type}, 0, ?message?)"
+                            query = f"INSERT INTO notification (shipcall_id, type, level, message) VALUES (?shipcall_id?, {notification_type}, 0, ?message?)"
-                        commands.execute(query, param={"shipcall_id" : int(shipcall_df.index[0]), "message" : violations[0]})
+                            commands.execute(query, param={"shipcall_id" : int(shipcall_df.index[0]), "message" : violations[0]})
 
-                    if evaluation_states_new[0] == 1 and evaluation_states_old[0] != 0: # this resolves the conflict
+                        if evaluation_states_new[0] == 1 and evaluation_states_old[0] != 0: # this resolves the conflict
-                        query = f"SELECT * from notification where shipcall_id = ?shipcall_id? and type = {notification_type} and level = 0"
+                            query = f"SELECT * from notification where shipcall_id = ?shipcall_id? and type = {notification_type} and level = 0"
-                        existing_notification = commands.query(query, param={"shipcall_id" : int(shipcall_df.index[0])})
+                            existing_notification = commands.query(query, param={"shipcall_id" : int(shipcall_df.index[0])})
-                        if len(existing_notification) > 0:
+                            if len(existing_notification) > 0:
-                            query = "DELETE from notification where id = ?id?"
+                                query = "DELETE from notification where id = ?id?"
-                            commands.execute(query, param={"id" : existing_notification[0]["id"]})
+                                commands.execute(query, param={"id" : existing_notification[0]["id"]})
-                        else:
+                            else:
-                            query = "INSERT INTO notification (shipcall_id, type, level) VALUES (?shipcall_id?, 4, 0)"
+                                query = "INSERT INTO notification (shipcall_id, type, level) VALUES (?shipcall_id?, 4, 0)"
-                            commands.execute(query, param={"shipcall_id" : int(shipcall_df.index[0])})
+                                commands.execute(query, param={"shipcall_id" : int(shipcall_df.index[0])})
-
+                    finally:
-                    pooledConnection.close()
+                        if pooledConnection is not None:
+                            pooledConnection.close()
 
 
         # build the list of 'evaluation_notifications_sent'. The value is 'False', when a notification should be created

src/server/config.py.sample

@@ -0,0 +1,40 @@
+"""
+Sample configuration for the Flask instance.
+
+Copy this file to `src/server/instance/config.py` (the instance folder is git-ignored)
+and adjust the values for each deployment target.
+"""
+
+# Flask
+SECRET_KEY = "change-me"
+
+# Python path adjustments used by the WSGI entrypoint (flaskapp.wsgi)
+APP_ROOT = "/var/www/brecal/src/server"
+SITE_PACKAGES = "/var/www/venv/lib/python3.12/site-packages/"
+
+# Paths to environment-specific secrets and instance data
+SECURE_DIR = "/var/www/secure"  # directory that holds connection/email JSON files
+INSTANCE_PATH = "/var/www/brecal/src/server/instance"
+
+# Logging
+LOG_FILE = "brecal.log"
+LOG_LEVEL = "INFO"  # e.g. DEBUG, INFO, WARNING
+LOG_TO_STDERR = False
+
+# Database pool setup
+DB_CONNECTION_FILE = "connection_data_prod.json"
+DB_POOL_NAME = "brecal_pool"
+DB_POOL_SIZE = 10
+
+# Email + notifications
+EMAIL_CREDENTIALS_FILE = "email_credentials_prod.json"
+EMAIL_URL_TEMPLATE = "https://brecal.example.com/shipcalls/"  # base URL for links in emails
+SMTP_DEBUG_LEVEL = 0  # 0 = quiet, 1 = verbose
+
+# Scheduler cadence
+SCHEDULE_UPDATE_SHIPCALLS_MINUTES = 60
+SCHEDULE_BACKGROUND_LATENCY_SECONDS = 30
+
+# Notification cleanup / escalation windows
+NOTIFICATION_COOLDOWN_MINS = 10
+NOTIFICATION_MAX_AGE_DAYS = 3

src/server/flaskapp.wsgi

@@ -1,20 +1,29 @@
-import os
-import sys
 import logging
+import os
+import runpy
+import sys
+from pathlib import Path
 
-sys.path.insert(0, '/var/www/brecal_test/src/server')
+BASE_DIR = Path(__file__).resolve().parent
-sys.path.insert(0, '/var/www/venv/lib/python3.12/site-packages/')
+INSTANCE_DIR = BASE_DIR / "instance"
+CONFIG_PATH = INSTANCE_DIR / "config.py"
 
-import schedule
+config = {}
+if CONFIG_PATH.exists():
+    config = runpy.run_path(str(CONFIG_PATH))
 
-# set the key
+app_root = config.get("APP_ROOT", str(BASE_DIR))
-os.environ['SECRET_KEY'] = 'zdiTz8P3jXOc7jztIQAoelK4zztyuCpJ'
+site_packages = config.get("SITE_PACKAGES")
 
-# Set up logging
+sys.path.insert(0, app_root)
-logging.basicConfig(stream=sys.stderr, level=logging.DEBUG)
+if site_packages:
+    sys.path.insert(0, site_packages)
 
-# Set up Scheduled Jobs
+if config.get("SECRET_KEY"):
+    os.environ["SECRET_KEY"] = config["SECRET_KEY"]
+
+log_kwargs = {"level": getattr(logging, config.get("LOG_LEVEL", "DEBUG")), "stream": sys.stderr}
+logging.basicConfig(**log_kwargs)
 
-# Import and run the Flask app
 from BreCal import create_app
-application = create_app()
+application = create_app(instance_path=config.get("INSTANCE_PATH"))