@startuml
' PlantUML resources
' - https://plantuml.com/de/ie-diagram
' - https://plantuml.com/de/class-diagram

' hide the spot
hide circle

' avoid problems with angled crows feet
skinparam linetype ortho



' key ------------------------------------------------------------------------------------------------------------------

entity **Key** #lightblue;line:green {
  * Primary Key <<PK>>
  --
  * Foreign Key <<FK(target_entity,column)>>
  * Unique Key <<UK(constraint_1,constraint_2,...)>>
  --
  * Required Field -> NOT NULL
    Optional Field -> NULL
  --
    Field with value auto generated by the DB <<generated>>
    Field with default value <<default>>
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note top of **Key** {
    Default field descriptions:

    **created** a datetime timestamp which is auto-generated by the DB when the record is created.
        <i>DATETIME(3) DEFAULT NOW() NOT NULL</i>

    **modified** a datetime timestamp which is auto-generated by the DB when the record is modified/updated.
        <i>DATETIME(3) DEFAULT NOW() NOT NULL ON UPDATE NOW()</i>
}




' insect tables (data provided by customer) ----------------------------------------------------------------------------

package insects <<Rectangle>> {

    entity insect_species {
      * id : bigint unsigned <<generated>> <<PK>>
      --
      * genus_id : bigint unsigned <<FK(insect_genus,id)>>
      * name : varchar(255)
        gbif_id : bigint unsigned
      --
      * created : datetime <<generated>>
        modified : datetime <<generated>>
    }


    entity insect_genus {
      * id : bigint unsigned <<generated>> <<PK>>
      --
      * family_id : bigint unsigned <<FK(insect_family,id)>>
      * name : varchar(255)
        gbif_id : bigint unsigned
      --
      * created : datetime <<generated>>
        modified : datetime <<generated>>
    }
    insect_genus ||--o{ insect_species


    entity insect_family {
      * id : bigint unsigned <<generated>> <<PK>>
      --
      * order_id : bigint unsigned <<FK(insect_order,id)>>
      * name : varchar(255)
        gbif_id : bigint unsigned
      --
      * created : datetime <<generated>>
        modified : datetime <<generated>>
    }
    insect_family ||--o{ insect_genus


    entity insect_order {
      * id : bigint unsigned <<generated>> <<PK>>
      --
      * name : varchar(255)
        gbif_id : bigint unsigned
      --
      * created : datetime <<generated>>
        modified : datetime <<generated>>
    }
    insect_order ||--o{ insect_family
}
note right of insects {
    The initial data for these tables is provided as a dump by the KInsecta team.

    **created** and **modified** must for sure not be part of the dump as it's generated data
        and gets inserted once the dump is imported.

    **gbif_id** the optional GBIF entity id for this order/family/genus/species:
        https://www.gbif.org/developer/summary
}




' user details and password reset --------------------------------------------------------------------------------------

entity user {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * username : varchar(255) <<UK(username)>>
  * full_name : varchar(255)
  * email : varchar(255) <<UK(email)>>
  * password : varchar(255)
  * role : enum, varchar(20)
  * status : enum, varchar(15)
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note left of user
    The user table. A user is uniquely identified by its username or email address.

    **password** <u>must</u> be hashed with <i>bcrypt</i>

    **role** an enum with the following possible values:
        - ADMIN
        - TEAM_MEMBER
        - DATA_COLLECTOR

    **status** an enum with the following possible values:
        - ACTIVE
        - INACTIVE
end note


entity password_reset_token {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * user_id : bigint unsigned <<FK(user,id)>> <<UK(user_id)>>
  * reset_token : varchar(255)
  * expires : datetime
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note top of password_reset_token
    This table holds password reset tokens which are requested by the user during the password reset process.

    The token (DB row) <u>must</u> be deleted when a token is redeemed successfully.

    **user_id** references the user id; <u>must</u> be unique in this table, a user can only have one
        (valid) password_reset_token at a time.
end note
password_reset_token "user(id)" ||--|| user


entity login_attempt {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * user_id : bigint unsigned <<FK(user,id)>> <<UK(user_id)>>
  * failed_attempts : tinyint
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note top of login_attempt
    This table holds the number of consecutive unsuccessful login attempts by a user and its data is processed
    at login time to block access for this user after a specified number of unsuccessful login attempts.
    This is a safety-meeasure to prevent brute-forcing the password of a user by trying dictionaries, etc.

    **user_id** references the user id; <u>must</u> be unique in this table, a user can only have one
        (valid) password_reset_token at a time.
end note
login_attempt "user(id)" ||--|| user




' files and uploads ----------------------------------------------------------------------------------------------------

entity s3_object {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * key : varchar(255)
  * bucket : varchar(255)
  * filename : varchar(255)
  * media_type : varchar(255)
  * filesize : bigint unsigned
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of s3_object
    This table holds a record of all S3 objects in our S3 object storage.

    **key** the S3 key of this object

    **bucket** the S3 bucket containing the specified object

    **filename** the original filename with file extension

    **media_type** the media type / MIME Type for this object

    **filesize** the filesize for this object in bytes
end note


entity dataset_image {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * dataset_id : bigint unsigned <<FK(dataset,id)>>
  * s3_object_id : bigint unsigned <<FK(s3_object,id)>>
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note left of dataset_image {
    This table for images related to a dataset has its own table (in contrary to the video file)
    because it can be linked with zero or many classifications.

    May have zero or many classifications, identified by the <i>dataset_image_id</i> field
    in the <i>classification</i> entity.
}
dataset_image ||--|| s3_object


entity bounding_box {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * dataset_image_id : bigint unsigned <<FK(dataset_image,id)>>
  * x_coordinate : decimal(7,3)
  * y_coordinate : decimal(7,3)
  * width : decimal(7,3)
  * height : decimal(7,3)
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note bottom of bounding_box {
    **NOTE: This table is designed for a future version and <u>must not</u> be created for the prototype version!**

    May have zero or many classifications, identified by the <i>bounding_box_id</i> field
    in the <i>classification</i> entity.

    **dataset_image_id** the id of the image this bounding box belongs to.
}
dataset_image ||--o{ bounding_box


entity upload {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * user_id : bigint unsigned <<FK(user,id)>>
    s3_object_id : bigint unsigned <<FK(s3_object,id)>>
  * status : enum, varchar(15)
    finished : datetime
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of upload
    A table for tracking all uploaded data.

    An upload <u>must</u> be created with <i>status</i> IN_PROGRESS
    with a separate API call before any data (ZIP) can be send
    to a second "upload" endpoint.

    **status** an enum with the following possible values:
        - IN_PROGRESS
        - SUCCESSFUL
        - ERRONEOUS


    Once the upload process finished successfully or failed,
    the following fields are updated:

    **s3_object_id** the S3 object id of the uploaded ZIP package;
        <u>may be null</u> if upload fails for some reason

    **finished** a datetime timestamp which is only set when
        <i>status</i> transitions to SUCCESSFUL or ERRONEOUS
end note
upload ||--o| s3_object
user ||--o{ upload




' datasets and sensors -------------------------------------------------------------------------------------------------

entity sensor_location {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * name : varchar(255)
  * postcode : smallint unsigned
  * city : varchar(255)
  * latitude : decimal(9,6)
  * longitude : decimal(9,6)
  * uncertainty : tinyint(1) <<0>>
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of sensor_location {
    For now, it's designed as a separate table because we want to use the location as datatable filter in the
    dataset view and as an export filter in the export view.

    The initial data for this and the <i>sensor</i> table is provided as a SQL dump by the KInsecta team.


    **latitude** and **longitude** decimal GPS coordinates with 6 decimal digits

    **uncertainty** a boolean indicating, that the exact lat/long position needs to be / is obfuscated when adding
        a new location. This is done by removing all but 1 decimal digit and results in a GPS position uncertainty
        of approximately 2 miles (see https://www.forensicdjs.com/blog/gps-coordinates-many-decimal-places-need/)
}


entity sensor {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * operator : bigint unsigned <<FK(user,id)>>
  * sensor_location_id : bigint unsigned <<FK(sensor_location,id)>>
  * hardware_platform: varchar(255)
  * os : varchar(255)
  * camera_type : varchar(255)
  * wingbeat_sensor_type : varchar(255)
  * bokeh_version: varchar(255)
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of sensor
    The initial data for this and the <i>sensor_location</i> table is provided as a SQL dump by the KInsecta team.

    A management view for this table is not part of the Prototype phase but may be created afterwards.

    **operator** the user in charge of the sensor

    **hardware_platform** e.g. the Raspberry Pi model

    ...
end note
sensor_location ||--o{ sensor


entity dataset {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * upload_id : bigint unsigned <<FK(upload,id)>>
  * user_id : bigint unsigned <<FK(user,id)>>

  * sensor_id : bigint unsigned <<FK(sensor,id)>>

    video_s3_object_id : bigint unsigned <<FK(s3_object,id)>>

    measurement_wingbeat_id : bigint unsigned <<FK(measurement_wingbeat,id)>>

  * measurement_datetime : datetime

    measurement_size_insect_length : decimal(3,1)
    measurement_size_insect_width : decimal(3,1)

    measurement_temperature_value : decimal(3,1)

    measurement_air_pressure_value : smallint

    measurement_humidity_value : decimal(4,1)

    measurement_luminosity_value : decimal(5,1)

    measurement_spectrum_channel_1 : smallint
    measurement_spectrum_channel_2 : smallint
    measurement_spectrum_channel_3 : smallint
    measurement_spectrum_channel_4 : smallint
    measurement_spectrum_channel_5 : smallint
    measurement_spectrum_channel_6 : smallint
    measurement_spectrum_channel_7 : smallint
    measurement_spectrum_channel_8 : smallint
    measurement_spectrum_channel_IR : smallint
    measurement_spectrum_clear_light : smallint

    measurement_particulate_matter_size_1 : smallint
    measurement_particulate_matter_size_2 : smallint
    measurement_particulate_matter_size_3 : smallint
    measurement_particulate_matter_size_4 : smallint
    measurement_particulate_matter_size_5 : smallint
    measurement_particulate_matter_size_6 : smallint

    measurement_rainfall_quantity: decimal(5,2)

    measurement_wind_sensor_speed: decimal(5,2)
    measurement_wind_sensor_direction : varchar(10)

  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note left of dataset
    **upload_id** the id of the original upload.

    **user_id** the user id of the user who uploaded the data package.
        May also be identified via <i>upload.user_id</i> but because this is used in every dataset details view
        we also save the user here.

    May have zero or many classifications, identified by the <i>dataset_id</i> field
    in the <i>classification</i> entity.

    May have zero or many tags mapped via a linking table.

    --

    Measurement related fields:

    **sensor_id** the id of the sensor that performed this measurement.

    **measurement_datetime** the datetime timestamp of the measurement, <i>not</i> the timestamp of the upload/import.

    **video_s3_object_id** an optional <i>s3_object</i> id for the video data of this dataset.

    **measurement_wingbeat_id** an optional <i>measurement_wingbeat</i> id for the wingbeat data of this dataset.
end note
dataset ||--|| sensor
dataset ||--|| upload
dataset ||--o{ dataset_image
user ||--o{ dataset


entity measurement_wingbeat {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * wav_s3_object_id : bigint unsigned <<FK(s3_object,id)>>
    image_s3_object_id : bigint unsigned <<FK(s3_object,id)>>
  * sample_rate : mediumint
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note top of measurement_wingbeat {
    This table for wingbeat related measurements is the only measurement which has its own table
    because it can be linked with zero or many classifications.

    May have zero or many classifications, identified by the <i>measurement_wingbeat_id</i> field
    in the <i>classification</i> entity.

    The record <u>must</u> specify a WAV <i>s3_object</i> and the related <i>sample_rate</i>
    and it <u>may</u> have an image <i>s3_object</i> (usually a PNG).
}
measurement_wingbeat ||--|| dataset


entity dataset_tag {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * name : varchar(255) <<UK(name)>>
  * auto_tag : tinyint(1) <<0>>
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note bottom of dataset_tag
    A dataset tag can be linked to many datasets (many-to-many relation).
    A linking table <u>must</u> be created for managing this relation.

    Once the last link between a tag and a dataset is removed, we decided together
    that the tag shall be kept in the DB and not deleted.

    A management view for this table is not part of the Prototype phase but may be created afterwards.


    **name** the name of the tag

    **auto_tag** 1/true if this is a tag created and automatically assigned by the app;
        default: 0/false
end note
dataset }o--o{ dataset_tag



' classifications ------------------------------------------------------------------------------------------------------

entity classification {
  * id : bigint unsigned <<generated>> <<PK>>
  --
    dataset_id : bigint unsigned <<FK(dataset,id)>>
    dataset_image_id : bigint unsigned <<FK(dataset_image,id)>>
    bounding_box_id : bigint unsigned <<FK(bounding_box,id)>>
    measurement_wingbeat_id : bigint unsigned <<FK(measurement_wingbeat,id)>>
  * insect_order_id <<FK(inset_order,id)>>
    insect_family_id <<FK(insect_family,id)>>
    insect_genus_id <<FK(insect_genus,id)>>
    insect_species_id <<FK(insect_species,id)>>
  * type : enum, varchar(255)
  * probability: decimal(6,5)
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of classification
    As a classification can be linked to different entities (one-to-one relation), it has several FK constraints which
     <u>may be null</u> . However, at least one FK constraint <u>must</u> be set, otherwise we have a dangling classification.

    **dataset_id** <u>must not</u> be null when the classification belongs to a <i>dataset</i> entity.

    **dataset_image_id** <u>must not</u> be null when the classification belongs to a <i>dataset_image</i> entity.

    **bounding_box_id** <u>must not</u> be null when the classification belongs to a <i>bounding_box</i> entity.
        **NOTE: Bounding boxes are already designed in this diagram for a future version**
        **but <u>must not</u> be created for the prototype version!**

    **measurement_wingbeat_id** <u>must not</u> be null when the classification belongs to a <i>measurement_wingbeat</i> entity.

    **type** an enum with the following possible values:
        - HUMAN
        - MACHINE

    **probability** a decimal representing the classification probability (1.0 means 100% confident).

    **insect_order** is mandatory, otherwise it isn't a classification.

    **insect_family** <u>may be null</u> if species, genus and family are unknown.

    **insect_genus** <u>may be null</u> if species and genus are unknown.

    **insect_species** <u>may be null</u> if species is unknown.
end note
classification ||--|| insect_order
classification ||--o| insect_family
classification ||--o| insect_genus
classification ||--o| insect_species

dataset ||--o{ classification
dataset_image ||--o{ classification
bounding_box ||--o{ classification
measurement_wingbeat ||--o{ classification



' export ---------------------------------------------------------------------------------------------------------------

entity export_file {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * export_id : bigint unsigned <<FK(export,id)>>
  * s3_object_id : bigint unsigned <<FK(s3_object,id)>>
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note bottom of export_file {
    This table for export files is a separate table because in case of large exports the export ZIP files
    may need to be split and eventually an export has multiple files.
}
export_file ||--|| s3_object


entity export {
  * id : bigint unsigned <<generated>> <<PK>>
  --
  * user_id : bigint unsigned <<FK(user,id)>>
    filter_sensor_id : bigint unsigned <<FK(sensor,id)>>
    filter_sensor_location_id : bigint unsigned <<FK(sensor,id)>>
  * filter_start_date : date
  * filter_end_date : date
    filter_tags : varchar(255)
  * status : enum, varchar(15)
    finished : datetime
    download_token : varchar(255)
    expires : datetime
  --
  * created : datetime <<generated>>
    modified : datetime <<generated>>
}
note right of export
    A table for tracking all export processes.

    May have zero or many export files, identified by the <i>export_id</i> field in the <i>export_file</i> entity.
        **NOTE: Normally an export has only one file, but in case of large exports the ZIP files may need to be split.**

    **filter_sensor_id** the id of a <i>sensor</i>; <u>may be null</u> when this filter is not set by the user.

    **filter_sensor_location_id** the id of a <i>sensor_location</i>; <u>may be null</u> when this filter is not set by the user.

    **filter_start_date** a mandatory date specifying the start of a date range for the dataset export.

    **filter_start_end** a mandatory date specifying the end of a date range for the dataset export.

    **filter_tags** a space or comma separated list of <i>dataset_tag</i> id's specifying the tags to filter the dataset export with.

    **status** an enum with the following possible values:
        - IN_PROGRESS
        - SUCCESSFUL
        - ERRONEOUS


    Once the export background task finished collecting the data, zipping the export file and uploading it to S3 storage,
    the following fields are updated:

    **finished** a datetime timestamp which is only set when <i>status</i> transitions to SUCCESSFUL or ERRONEOUS.

    **download_token** a UUID token used in the (public?) export download link as export identifier.

    **expires** a datetime timestamp specifying a timestamp after which the export and its related export_file's
        are no longer accessible for the user and may be deleted by a system cleanup task.
end note
user ||--o{ export
export ||--o{ export_file
export ||--o| sensor
export ||--o| sensor_location

@enduml
