Enum "dataset_type" {
  "structured"
  "unstructured"
  "test"
  "train"
}

Enum "gain_level" {
  "low"
  "low-medium"
  "medium"
  "medium-high"
  "high"
}

Table "dataset" {
  "id" VARCHAR(12) [pk]
  "name" VARCHAR(255) [unique, not null]
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
  "type" dataset_type [not null, default: 'structured']
}

Table "location" {
  "id" VARCHAR(12) [pk]
  "dataset_id" VARCHAR(12) [not null]
  "name" VARCHAR(140) [not null]
  "latitude" DECIMAL(10,7) [not null, check: `latitude BETWEEN -90.0 AND 90.0`]
  "longitude" DECIMAL(10,7) [not null, check: `longitude BETWEEN -180.0 AND 180.0`]
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
  "timezone_id" VARCHAR(40) [not null]

  Indexes {
    (dataset_id, name) [unique]
  }
}

Table "cyclic_recording_pattern" {
  "id" VARCHAR(12) [pk]
  "record_s" INTEGER [not null]
  "sleep_s" INTEGER [not null]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]

  Indexes {
    (record_s, sleep_s) [unique]
  }
}

Table "cluster" {
  "id" VARCHAR(12) [pk]
  "dataset_id" VARCHAR(12) [not null]
  "location_id" VARCHAR(12) [not null]
  "name" VARCHAR(140) [not null]
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
  "cyclic_recording_pattern_id" VARCHAR(12)
  "sample_rate" INTEGER [not null]
  "path" VARCHAR(255)

  Indexes {
    (location_id, name) [unique]
  }
}

Table "file" {
  "id" VARCHAR(21) [pk]
  "file_name" VARCHAR(255) [not null]
  "xxh64_hash" VARCHAR(16) [unique, not null]
  "location_id" VARCHAR(12)
  "timestamp_local" TIMESTAMP [not null]
  "cluster_id" VARCHAR(12)
  "duration" DECIMAL(7,3) [not null, check: `duration > 0`]
  "sample_rate" INTEGER [not null]
  "description" VARCHAR(255)
  "maybe_solar_night" BOOLEAN
  "maybe_civil_night" BOOLEAN
  "moon_phase" DECIMAL(3,2) [check: `moon_phase BETWEEN 0.00 AND 1.00`]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]

  Indexes {
    location_id [name: "idx_file_location"]
    cluster_id [name: "idx_file_cluster"]
    timestamp_local [name: "idx_file_timestamp_local"]
  }
}

Table "moth_metadata" {
  "file_id" VARCHAR(21) [pk]
  "timestamp" TIMESTAMP [not null]
  "recorder_id" VARCHAR(16)
  "gain" gain_level
  "battery_v" DECIMAL(2,1) [check: `battery_v >= 0`]
  "temp_c" DECIMAL(3,1)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
}

Table "file_metadata" {
  "file_id" VARCHAR(21) [pk]
  "json" JSON
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
}

Table "file_dataset" {
  "file_id" VARCHAR(21) [not null]
  "dataset_id" VARCHAR(12) [not null]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]

  Indexes {
    (file_id, dataset_id) [pk]
    dataset_id [name: "idx_file_dataset_dataset"]
  }
}

Table "segment" {
  "id" VARCHAR(21) [pk]
  "file_id" VARCHAR(21) [not null]
  "dataset_id" VARCHAR(12) [not null]
  "start_time" DECIMAL(7,3) [not null]
  "end_time" DECIMAL(7,3) [not null]
  "freq_low" DECIMAL(9,3) [check: `freq_low < 300000`]
  "freq_high" DECIMAL(9,3) [check: `freq_high < 300000`]
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]

  Indexes {
    file_id [name: "idx_segment_file"]
    dataset_id [name: "idx_segment_dataset"]
  }
}

Table "ebird_taxonomy" {
  "id" VARCHAR(12) [pk]
  "taxonomy_version" VARCHAR(4) [not null]
  "taxon_order" INTEGER [not null]
  "category" VARCHAR(15) [not null]
  "species_code" VARCHAR(15) [not null]
  "taxon_concept_id" VARCHAR(15)
  "primary_com_name" VARCHAR(100) [not null]
  "sci_name" VARCHAR(100) [not null]
  "bird_order" VARCHAR(30)
  "family" VARCHAR(100)
  "species_group" VARCHAR(100)
  "report_as" VARCHAR(15)
  "valid_from" DATE [not null]
  "valid_to" DATE
  "active" BOOLEAN [default: TRUE]

  Indexes {
    (species_code, taxonomy_version) [unique]
    (species_code, taxonomy_version) [name: "idx_ebird_taxonomy_species_code"]
  }
}

Table "species" {
  "id" VARCHAR(12) [pk]
  "label" VARCHAR(100) [unique, not null]
  "ebird_code" VARCHAR(12)
  "taxonomy_version" VARCHAR(4)
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
}

Table "call_type" {
  "id" VARCHAR(12) [pk]
  "species_id" VARCHAR(12) [not null]
  "label" VARCHAR(100) [not null]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
}

Table "filter" {
  "id" VARCHAR(12) [pk]
  "name" VARCHAR(140) [not null]
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [not null, default: true]
}

Table "label" {
  "id" VARCHAR(21) [pk]
  "segment_id" VARCHAR(21) [not null]
  "species_id" VARCHAR(12) [not null]
  "filter_id" VARCHAR(12) [not null]
  "certainty" DECIMAL(5,2) [check: `certainty <= 100 AND certainty >= 0`]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [not null, default: true]

  Indexes {
    segment_id [name: "idx_label_segment_id"]
    species_id [name: "idx_label_species_id"]
  }
}

Table "label_metadata" {
  "label_id" VARCHAR(21) [pk]
  "json" JSON
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]
}

Table "label_subtype" {
  "id" VARCHAR(21) [pk]
  "label_id" VARCHAR(21) [not null]
  "calltype_id" VARCHAR(12) [not null]
  "filter_id" VARCHAR(12)
  "certainty" DECIMAL(5,2) [check: `certainty <= 100 AND certainty >= 0`]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [not null, default: true]

  Indexes {
    label_id [name: "idx_label_subtype_label_id"]
    calltype_id [name: "idx_label_subtype_calltype_id"]
    filter_id [name: "idx_label_subtype_filter_id"]
  }
}

Ref:"dataset"."id" < "location"."dataset_id"

Ref:"dataset"."id" < "cluster"."dataset_id"

Ref:"location"."id" < "cluster"."location_id"

Ref:"cyclic_recording_pattern"."id" < "cluster"."cyclic_recording_pattern_id"

Ref:"location"."id" < "file"."location_id"

Ref:"cluster"."id" < "file"."cluster_id"

Ref:"file"."id" < "moth_metadata"."file_id"

Ref:"file"."id" < "file_metadata"."file_id"

Ref:"file"."id" < "file_dataset"."file_id"

Ref:"dataset"."id" < "file_dataset"."dataset_id"

Ref:"file"."id" < "segment"."file_id"

Ref:"dataset"."id" < "segment"."dataset_id"

Ref:"file_dataset".("file_id", "dataset_id") < "segment".("file_id", "dataset_id")

Ref:"ebird_taxonomy".("species_code", "taxonomy_version") < "species".("ebird_code", "taxonomy_version")

Ref:"species"."id" < "call_type"."species_id"

Ref:"segment"."id" < "label"."segment_id"

Ref:"species"."id" < "label"."species_id"

Ref:"filter"."id" < "label"."filter_id"

Ref:"label"."id" < "label_metadata"."label_id"

Ref:"label"."id" < "label_subtype"."label_id"

Ref:"call_type"."id" < "label_subtype"."calltype_id"

Ref:"filter"."id" < "label_subtype"."filter_id"