Enum "dataset_type" {
  "organise"
  "test"
  "train"
}

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

Table "dataset" {
  "id" VARCHAR(12) [pk]
  "name" VARCHAR(255) [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: 'organise']

  Indexes {
    name [name: "idx_dataset_name"]
    active [name: "idx_dataset_active"]
    public [name: "idx_dataset_public"]
  }
}

Table "location" {
  "id" VARCHAR(12) [pk]
  "dataset_id" VARCHAR(12) [not null]
  "name" VARCHAR(140) [not null]
  "latitude" DECIMAL(10,7) [not null]
  "longitude" DECIMAL(10,7) [not null]
  "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 {
    name [name: "idx_location_name"]
    dataset_id [name: "idx_location_dataset"]
    active [name: "idx_location_active"]
    (dataset_id, active) [name: "idx_location_dataset_active"]
  }
}

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 {
    active [name: "idx_cyclic_recording_pattern_active"]
  }
}

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]

  Indexes {
    dataset_id [name: "idx_cluster_dataset"]
    active [name: "idx_cluster_active"]
    (dataset_id, active) [name: "idx_cluster_dataset_active"]
    cyclic_recording_pattern_id [name: "idx_cluster_recording_pattern"]
    location_id [name: "idx_cluster_location_id"]
  }
}

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

  Indexes {
    location_id [name: "idx_file_location"]
    active [name: "idx_file_active"]
    timestamp_local [name: "idx_file_timestamp_local"]
    cluster_id [name: "idx_file_cluster"]
    maybe_solar_night [name: "idx_file_maybe_solar_night"]
    maybe_civil_night [name: "idx_file_maybe_civil_night"]
  }
}

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

  Indexes {
    active [name: "idx_moth_metadata_active"]
  }
}

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]

  Indexes {
    active [name: "idx_file_metadata_active"]
  }
}

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]
    file_id [name: "idx_file_dataset_file"]
    dataset_id [name: "idx_file_dataset_dataset"]
  }
}

Table "selection" {
  "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)
  "freq_high" DECIMAL(9,3)
  "description" VARCHAR(255)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [default: TRUE]

  Indexes {
    file_id [name: "idx_selection_file"]
    dataset_id [name: "idx_selection_dataset"]
    active [name: "idx_selection_active"]
  }
}

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

  Indexes {
    active [name: "idx_selection_metadata_active"]
  }
}

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

  Indexes {
    (species_code, taxonomy_version) [unique]
  }
}

Table "species" {
  "id" VARCHAR(12) [pk]
  "label" VARCHAR(100) [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]

  Indexes {
    label [name: "idx_species_label"]
    ebird_code [name: "idx_species_ebird"]
  }
}

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]

  Indexes {
    species_id [name: "idx_call_type_species"]
    label [name: "idx_call_type_label"]
  }
}

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]
  "selection_id" VARCHAR(21) [not null]
  "species_id" VARCHAR(12) [not null]
  "filter_id" VARCHAR(12)
  "certainty" DECIMAL(5,2)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [not null, default: true]

  Indexes {
    selection_id [name: "idx_label_selection_id"]
    species_id [name: "idx_label_species_id"]
  }
}

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)
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "active" BOOLEAN [not null, default: true]
}

Table "species_dataset" {
  "species_id" VARCHAR(12) [not null]
  "dataset_id" VARCHAR(12) [not null]
  "created_at" TIMESTAMP [default: `CURRENT_TIMESTAMP`]
  "last_modified" TIMESTAMP [default: `CURRENT_TIMESTAMP`]

  Indexes {
    (species_id, dataset_id) [pk]
    species_id [name: "idx_species_dataset_species"]
    dataset_id [name: "idx_species_dataset_dataset"]
  }
}

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" < "selection"."file_id"

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

Ref:"selection"."id" < "selection_metadata"."selection_id"

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

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

Ref:"selection"."id" < "label"."selection_id"

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

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

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

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

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

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

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