mark certainty=0 for  
Check                │ rqwTDv3Op63k
Don't Know           │ e-bBi7tasfya
Question             │ lR6WEIWL5CUE
mark certainty = 100 for
  │ Fake Kiwi            │ -6ghUwAPNQ2J │
  │ Haast Tokoeka        │ ANhV8iZPfIh8 │
  │ Kiwi                 │ 7LNNjRcptMev │
  │ Korero Gecko         │ YPFfCCNvDuuK │
  │ Rowi                 │ fDGmXv9c6xEn │
  │ S. Fiordland Tokoeka │ jqEQrViqRFFX │
mark all call types 100 certain
check bulk import gets stdout, stderr right
(base) skraak ➤ ./skraak import bulk --db ./db/skraak.duckdb --dataset la-JpAf2nLKG --csv "/media/david/Misc-2/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/import.csv" --log "/media/david/Misc-2/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/bulk_import.log"
Starting bulk import...
  Database: ./db/skraak.duckdb
  Dataset:  la-JpAf2nLKG
  CSV:      /media/david/Misc-2/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/import.csv
  Log:      /media/david/Misc-2/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/bulk_import.log
Monitor progress: tail -f /media/david/Misc-2/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/bulk_import.log
{
  "total_locations": 39,
  "clusters_created": 39,
  "clusters_existing": 0,
  "total_files_scanned": 24590,
  "files_imported": 24583,
  "files_duplicate": 1,
  "files_error": 6,
  "processing_time": "1h49m40.555152942s"
}

Update tools could allow setting active to false??
look at nulls in schema, problem with updatde tool

Update tools could allow setting active to false?? Currently do not

##### NEED TO MERGE LABELS ###################
SELECT id, label, ebird_code, description, active
  FROM species
  WHERE ebird_code IN ('lotkoe1', 'liskiw1', 'grskiw1', 'okbkiw1', 'sobkiw1', 'nibkiw1');
┌──────────────┬──────────────────────┬────────────┬───────────────────────────────────────────────┬─────────┐
│      id      │        label         │ ebird_code │                  description                  │ active  │
│   varchar    │       varchar        │  varchar   │                    varchar                    │ boolean │
├──────────────┼──────────────────────┼────────────┼───────────────────────────────────────────────┼─────────┤
│ CNi9X-Hkz4-A │ Kiwi_Brown           │ sobkiw1    │ FreebirdCode: 109,  BiowebCode: 10857,  Com…  │ false   │
│ D_pepf6f08kn │ Kiwi_Great_Spotted   │ grskiw1    │ FreebirdCode: 110,  BiowebCode: 10860,  Com…  │ false   │
│ di-jxVg8tcVO │ Kiwi_Little_Spotted  │ liskiw1    │ FreebirdCode: 111,  BiowebCode: 10859,  Com…  │ false   │
│ dZLV3Ue916E- │ Kiwi_Nth_Is_Brown    │ nibkiw1    │ FreebirdCode: 112,  BiowebCode: 10856,  Com…  │ false   │
│ 74yg7hquUKT9 │ Kiwi_Okarito_Brown   │ okbkiw1    │ FreebirdCode: 113,  BiowebCode: 13151,  Com…  │ false   │
│ ac0AgauTMvMT │ Kiwi_spp             │ sobkiw1    │ FreebirdCode: 114,  BiowebCode: 29709,  Com…  │ false   │
│ 4JOC8g7OsLGW │ Cuckoo_long-tailed   │ lotkoe1    │ FreebirdCode: 35,  BiowebCode: 11248, Long-…  │ false   │
│ rqIcB4EkSy2G │ Long-tailed Koel     │ lotkoe1    │ NULL                                          │ true    │
│ eYZ6Ns7XNbwR │ Kiwi pukupuku        │ liskiw1    │ Kiwi pukupuku / Little Spotted Kiwi           │ true    │
│ TSwVuQdE0cEA │ Roroa                │ grskiw1    │ Roroa / Great Spotted Kiwi                    │ true    │
│ fDGmXv9c6xEn │ Rowi                 │ okbkiw1    │ Rowi / Okarito Brown Kiwi                     │ true    │
│ jqEQrViqRFFX │ S. Fiordland Tokoeka │ sobkiw1    │ Southern Fiordland Tokoeka                    │ true    │
│ ANhV8iZPfIh8 │ Haast Tokoeka        │ sobkiw1    │ NULL                                          │ true    │
│ 7LNNjRcptMev │ Kiwi                 │ nibkiw1    │ Kiwi-nui / North Island Brown Kiwi            │ true    │
├──────────────┴──────────────────────┴────────────┴───────────────────────────────────────────────┴─────────┤
│ 14 rows                                                                                          5 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
on the duckdb cli How can i append this description
│ dZLV3Ue916E- │ Kiwi_Nth_Is_Brown    │ nibkiw1    │ FreebirdCode: 112,  BiowebCode: 10856,  Com…  │ false   │
to the following, and also overwrite the Kiwi label with Kiwi_Nth_Is_Brown
7LNNjRcptMev │ Kiwi                 │ nibkiw1    │ Kiwi-nui / North Island Brown Kiwi            │ true
then delete dZLV3Ue916E-
# Does not work.
UPDATE species
SET
    label = 'Kiwi_Nth_Is_Brown',
    description = description || ', ' || (
        SELECT description
        FROM species
        WHERE id = 'dZLV3Ue916E-'
    )
WHERE id = '7LNNjRcptMev';
DELETE FROM my_table
WHERE id = 'dZLV3Ue916E-';

bluduc1       Duck_Blue_Whio  Blue Duck
comcha        Chaffinch       Common Chaffinch
dunnoc1       Dunnock_Hedge_Sparrow   Dunnock
fernbi1       Fernbird        New Zealand Fernbird
fernbi1       Fernbird_Nth Is New Zealand Fernbird
fernbi1       Fernbird_Sth Is New Zealand Fernbird                                   
grskiw1       Kiwi_Great_Spotted      Great Spotted Kiwi
grskiw1       Roroa   Great Spotted Kiwi
gryger1       Warbler_Grey    Gray Gerygone
kea1  Kea     Kea
liskiw1       Kiwi pukupuku   Little Spotted Kiwi
liskiw1       Kiwi_Little_Spotted     Little Spotted Kiwi                            
lotkoe1       Cuckoo_long-tailed      Long-tailed Koel
lotkoe1       Long-tailed Koel        Long-tailed Koel                               
morepo2       Morepork        Morepork                                               
nezbel1       Bellbird        New Zealand Bellbird
nezbel1       Bellbird_Poor_Knights   New Zealand Bellbird                           
nezbel1       Bellbird_Three_Kings    New Zealand Bellbird
nezfan1       Fantail New Zealand Fantail
nezfan1       Fantail_Chatham_Is      New Zealand Fantail
nezfan1       Fantail_Nth_Is  New Zealand Fantail
nezfan1       Fantail_Sth_Is  New Zealand Fantail                                    
nezkak1       Kaka    New Zealand Kaka
nezrob3       Robin_Stewart_Is        South Island Robin
nezrob3       Robin_Sth_Is    South Island Robin
nibkiw1       Kiwi    North Island Brown Kiwi                                        
nibkiw1       Kiwi_Nth_Is_Brown       North Island Brown Kiwi
okbkiw1       Kiwi_Okarito_Brown      Okarito Brown Kiwi                             
okbkiw1       Rowi    Okarito Brown Kiwi
riflem1       Rifleman        Rifleman
riflem1       Rifleman_Nth_Is Rifleman                                               
riflem1       Rifleman_Sth_Is Rifleman                                               
saddle3       Saddleback_Sth_Is       South Island Saddleback                        
shbcuc1       Cuckoo_Shining  Shining Bronze-Cuckoo
silver3       Silvereye       Silvereye
sobkiw2       Kiwi_Tokoeka_Haast      Southern Brown Kiwi (South I.)                 
sonthr1       Thrush_Song     Song Thrush                                            
spocra2       Crake_Spotless  Spotless Crake
tomtit1       Tomtit  Tomtit
tomtit1       Tomtit_Auckland_Is      Tomtit
tomtit1       Tomtit_Chatham_Is       Tomtit
tomtit1       Tomtit_Nth_Is   Tomtit
tomtit1       Tomtit_Snares_Is_Black  Tomtit
tomtit1       Tomtit_Sth_Is   Tomtit                                                 
tui1  Tui     Tui
tui1  Tui_Chatham_Is  Tui
varoys1       Oystercatcher_Variable  Variable Oystercatcher
weka1 Weka_Buff       Weka
weka1 Weka_Nth_Is     Weka
weka1 Weka_Stewart_Is Weka
weka1 Weka_Western    Weka
weka1 Weka_spp        Weka                                                           
yellow2       Yellowhammer    Yellowhammer
#######################################
Not in species table: ausbit1, comred, eurbla, eursta, nezpig2, parake, pipipi1, soioys1, soiwre1, weta

skraak calls summarise --folder ./recordings | jq 'del(.segments)'

skraak calls summarise --folder --brief ./recordings 

#compile for windows
Prerequisites:
- sudo apt install gcc-mingw-w64-x86-64 g++-mingw-w64-x86-64
- MinGW set to posix variant:
sudo update-alternatives --set x86_64-w64-mingw32-gcc
/usr/bin/x86_64-w64-mingw32-gcc-posix
sudo update-alternatives --set x86_64-w64-mingw32-g++
/usr/bin/x86_64-w64-mingw32-g++-posix
echo 'extern "C" { void* _ZNSt15basic_streambufIcSt11char_traitsIcEE7seekposESt4fposI9_MbstatetESt13_Ios_Openmode() { return (void*)-1; } }' | tee /tmp/stub_seekpos.cpp
x86_64-w64-mingw32-g++ -c /tmp/stub_seekpos.cpp -o /tmp/stub_seekpos.o && \
CGO_ENABLED=1 \
CC=x86_64-w64-mingw32-gcc \
CXX=x86_64-w64-mingw32-g++ \
GOOS=windows GOARCH=amd64 \
go build -ldflags '-extldflags "/tmp/stub_seekpos.o -lucrt"' -o skraak.exe

Manually classify calls
./skraak calls classify --folder "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51" \
  --reviewer David \
  --bind k=GSK \
  --bind d="GSK+Duet" \
  --bind f="GSK+Female" \
  --bind m="GSK+Male" \
  --bind n="Don't Know" \
  --bind p=Morepork \
  --bind w=Weka \
  --color
./skraak calls summarise --folder "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51" > "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51/summary_2026-03-15.json"
claude Skill(skraak-data-mapping): please help me make an import mapping for "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51/"
./skraak import segments \
  --db ./db/test.duckdb \
  --dataset dataset_id \
  --location location_id \
  --cluster cluster_id \
  --folder "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51" \
  --mapping "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51/mapping_2026-03-15.json"
claude: please fill out the preceeding command to import data into test.duckdb. I need dataset id, location id, cluster id for the file path already filled in. use the skraak cli on test.duckdb to get this data
./skraak import segments \
    --db ./db/test.duckdb \
    --dataset la-JpAf2nLKG \
    --location lrTAO7rA7CIm \
    --cluster 2x2VZ5yNKQwA \
    --folder "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51" \
    --mapping "/media/david/SSD4/Manu o Kahurangi kiwi survey (10)/NEW - MOK kiwi survey 20260306/tx51/mapping_2026-03-15.json"
DELETE FROM cluster
WHERE dataset_id = 'la-JpAf2nLKG'
AND location_id IN (
  SELECT l.id FROM location l WHERE l.dataset_id = 'QZ0tlUrX4Nyi'
)
AND id NOT IN (SELECT cluster_id FROM file);
SELECT c.id, c.name as cluster_name, l.name as
location_name, l.dataset_id as location_dataset_id, (SELECT COUNT(*)
FROM file f WHERE f.cluster_id = c.id) as file_count FROM cluster c
JOIN location l ON c.location_id = l.id WHERE c.dataset_id =
'la-JpAf2nLKG' AND l.dataset_id != 'la-JpAf2nLKG';
D UPDATE file_dataset SET dataset_id = 'QZ0tlUrX4Nyi' WHERE file_id IN (SELECT id FROM file WHERE
    cluster_id = 'wnrpJmhumf6n') AND dataset_id = 'la-JpAf2nLKG';
D UPDATE cluster SET dataset_id = 'QZ0tlUrX4Nyi' WHERE id = 'wnrpJmhumf6n';
Constraint Error:
Violates foreign key constraint because key "cluster_id: wnrpJmhumf6n" is still referenced by a foreign key in a different table. If this is an unexpected constraint violation, please refer to our foreign key limitations in the documentation

-- NOTE: DBML does not like functions and materialised views
-- from this: $npm install -g @dbml/cli
-- sql2dbml schema.sql --postgres -o schema.dbml
-- from this: $npm install -g @softwaretechnik/dbml-renderer
-- dbml-renderer -i schema.dbml -o schema.svg
-- junction table for files-dataset relationship
-- (file can be a member of many datasets)
-- (selections apply only to 1 dataset)
-- use suncalc.js
-- could use a function for night, on client not db, but want to filter on night
-- in file table use mid point of file as time
-- all times must be zoned to utc
-- dataset type enum
CREATE TYPE dataset_type AS ENUM ('organise', 'test', 'train');
-- ALTER TYPE dataset_type ADD VALUE 'miscellaneous';
-- Dataset Table
-- Add type column to the dataset table, so that I do not ever mix testing data with training data.
CREATE TABLE dataset (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    type dataset_type NOT NULL DEFAULT 'organise'
);
-- Not implemented but desirable, used uniqe index instead
-- ALTER TABLE dataset ADD CONSTRAINT uq_dataset_name UNIQUE (name);
-- Not implemented Error:
-- No support for that ALTER TABLE option yet!
CREATE UNIQUE INDEX idx_dataset_name ON dataset(name);
CREATE INDEX idx_dataset_active ON dataset(active);
CREATE INDEX idx_dataset_public ON dataset(public);
-- Locations Table
CREATE TABLE location (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    dataset_id VARCHAR(12) NOT NULL, -- nanoid, link to dataset
    name VARCHAR(140) NOT NULL,
    latitude DECIMAL(10, 7) NOT NULL CHECK (latitude BETWEEN -90.0 AND 90.0), -- -45.5027
    longitude DECIMAL(10, 7) NOT NULL CHECK (longitude BETWEEN -180.0 AND 180.0), -- 167.48406
    description VARCHAR(255), -- Limited to 255 characters for efficiency
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    timezone_id VARCHAR(40) NOT NULL, -- XXnot required as may need to auto generate cluster, can't always ask or check
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)
);
-- N
--ALTER TABLE location ADD CONSTRAINT uq_location_dataset_name UNIQUE (dataset_id, name);
CREATE UNIQUE INDEX idx_location_dataset_name ON location(dataset_id, name);
CREATE INDEX idx_location_name ON location(name);
CREATE INDEX idx_location_dataset ON location(dataset_id); -- ??
CREATE INDEX idx_location_active ON location(active); -- ??
CREATE INDEX idx_location_dataset_active ON location(dataset_id, active);
-- Add recording pattern, mainly so it can be searchable,
-- this is an optional field, audio moth needs this, to help
-- with searching and filtering
-- i have 24/7: 1 in 30 and 1 in 40
CREATE TABLE cyclic_recording_pattern (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    record_s INTEGER NOT NULL,
    sleep_s INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    -- UNIQUE (record_s, sleep_s) -- not implemented as duckdb won't allow an alter table, should be though.
);
-- Could not do this as duckdb refuses, the unique index is meant to do it.
-- Add unique constraint
-- ALTER TABLE cyclic_recording_pattern
-- ADD CONSTRAINT unique_record_sleep UNIQUE (record_s, sleep_s);
CREATE INDEX idx_cyclic_recording_pattern_active ON cyclic_recording_pattern(active);
-- Ensure no duplicate record/sleep combinations
CREATE UNIQUE INDEX idx_cyclic_recording_pattern_unique_combo ON cyclic_recording_pattern(record_s, sleep_s);
-- Cluster Table (think of a cluster of files as all the files on 1 SD Card)
-- a statistical unit with no major time gaps, to enable call rate stats
-- See changes below, added foreign key on location, added recording pattern
-- added timezone_id for iana timezone id
CREATE TABLE cluster (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    dataset_id VARCHAR(12) NOT NULL, -- nanoid, link to dataset
    location_id VARCHAR(12) NOT NULL, -- A cluster must have a location, as well as a dataset
    name VARCHAR(140) NOT NULL,
    description VARCHAR(255), -- Limited to 255 characters for efficiency
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    cyclic_recording_pattern_id VARCHAR(12),
    sample_rate INTEGER NOT NULL,
    path VARCHAR(255) NULL, -- optional hint for local access
    FOREIGN KEY (dataset_id) REFERENCES dataset(id),
    FOREIGN KEY (location_id) REFERENCES location(id),
    FOREIGN KEY (cyclic_recording_pattern_id) REFERENCES cyclic_recording_pattern(id)
);
-- Not implemented but desirable, using unique index instead
-- ALTER TABLE cluster ADD CONSTRAINT uq_cluster_location_name UNIQUE (location_id, name);
CREATE UNIQUE INDEX idx_cluster_location_name ON cluster(location_id, name);
CREATE INDEX idx_cluster_dataset ON cluster(dataset_id);
CREATE INDEX idx_cluster_active ON cluster(active);
CREATE INDEX idx_cluster_dataset_active ON cluster(dataset_id, active);
CREATE INDEX idx_cluster_recording_pattern ON cluster(cyclic_recording_pattern_id);
CREATE INDEX idx_cluster_location_id ON cluster(location_id);
-- values in my data is medium and medium-high
CREATE TYPE gain_level AS ENUM ('low', 'low-medium', 'medium', 'medium-high', 'high');
-- Files Table (removed dataset_id as now use junction table)
-- Note: timestamp_local should reflect local time, not timestamp in filename, this is important
-- duration must not have more than 3 decimal places, check in ui
CREATE TABLE file (
    id VARCHAR(21) PRIMARY KEY, -- nanoid
    file_name VARCHAR(255) NOT NULL,
    xxh64_hash VARCHAR(16) NOT NULL, -- hash of original file cbe675a69a5fef1c
    location_id VARCHAR(12) NOT NULL, -- nanoid, from locations table
    timestamp_local TIMESTAMP WITH TIME ZONE NOT NULL, -- parsed from filename, adjust for daylight saving
    cluster_id VARCHAR(12), -- nanoid(12), optional if imported one by one
    duration DECIMAL(7, 3) NOT NULL CHECK (duration > 0), -- in seconds, allowing for millisecond precision (9999.999s)
    sample_rate INTEGER NOT NULL,
    description VARCHAR(255), -- Limited to 255 characters for efficiency
    maybe_solar_night BOOLEAN, --calculate with function on client. this is a more accurate value to file table
    maybe_civil_night BOOLEAN, --calculate with function on client. this is a more accurate value to file table
    moon_phase DECIMAL(3,2) CHECK (moon_phase BETWEEN 0.00 AND 1.00), -- 0.00 to 1.00 (new moon to full moon)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (location_id) REFERENCES location(id),
    FOREIGN KEY (cluster_id) REFERENCES cluster(id)
);
CREATE INDEX idx_file_location ON file(location_id);
CREATE INDEX idx_file_active ON file(active);
CREATE INDEX idx_file_timestamp_local ON file(timestamp_local);
CREATE INDEX idx_file_cluster ON file(cluster_id);
CREATE INDEX idx_file_maybe_solar_night ON file(maybe_solar_night);
CREATE INDEX idx_file_maybe_civil_night ON file(maybe_civil_night);
-- ALTER TABLE file
--     ALTER COLUMN location_id DROP NOT NULL;
-- UNIMPLEMENTED
-- Unique constraint on xxh64_hash to prevent duplicate file hashes
-- ALTER TABLE file ADD CONSTRAINT unique_xxh64_hash UNIQUE (xxh64_hash);
CREATE TABLE moth_metadata (
    file_id VARCHAR(21) PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    recorder_id VARCHAR(16), -- 24F31901603710CD (16)
    gain gain_level NULL, -- low, medium, high or null
    battery_v DECIMAL(2, 1) CHECK (battery_v >= 0), -- for values from 0 to 9.9
    temp_c DECIMAL(3, 1), -- e.g., 24.2
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id)
);
CREATE INDEX idx_moth_metadata_active ON moth_metadata(active);
CREATE TABLE file_metadata (
    file_id VARCHAR(21) PRIMARY KEY,
    json JSON, -- For noise levels and other file-level metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id)
);
-- CREATE INDEX idx_file_metadata_json ON file_metadata USING gin(json); -- unimplemented
CREATE INDEX idx_file_metadata_active ON file_metadata(active);
-- Junction Table for Files to Dataset (many-to-many)
CREATE TABLE file_dataset (
    file_id VARCHAR(21) NOT NULL,
    dataset_id VARCHAR(12) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (file_id, dataset_id),
    FOREIGN KEY (file_id) REFERENCES file(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)
);
-- indexes for the junction table
CREATE INDEX idx_file_dataset_file ON file_dataset(file_id);
CREATE INDEX idx_file_dataset_dataset ON file_dataset(dataset_id);
-- Selection Table
CREATE TABLE selection(
    id VARCHAR(21) PRIMARY KEY, -- nanoid
    file_id VARCHAR(21) NOT NULL, -- nanoid
    dataset_id VARCHAR(12) NOT NULL, -- nanoid, link to dataset
    start_time DECIMAL(7,3) NOT NULL, --up to 9999.999 seconds
    end_time DECIMAL(7,3) NOT NULL, -- up to 9999.999 seconds
    freq_low DECIMAL(9,3) CHECK (freq_low < 300000), -- LOOK AT CHECK
    freq_high DECIMAL(9,3) CHECK (freq_high < 300000), -- LOOK AT CHECK
    description VARCHAR(255), -- Limited to 255 characters for efficiency
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)
);
CREATE INDEX idx_selection_file ON selection(file_id);
CREATE INDEX idx_selection_dataset ON selection(dataset_id);
CREATE INDEX idx_selection_active ON selection(active);
CREATE TABLE selection_metadata (
    selection_id VARCHAR(21) PRIMARY KEY,
    json JSON, -- for loudness, noise, and other selection-level metadaata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (selection_id) REFERENCES selection(id)
);
-- CREATE INDEX idx_selection_metadata_json ON selection_metadata USING gin(json); -- unimplemensed
CREATE INDEX idx_selection_metadata_active ON selection_metadata(active);
-- eBird Taxonomy Table
-- will need to update INDEX too when introducing a new version
-- see working with ebird taxonomies, aichat, deepseek, macbook
-- see materialised view and index on it
-- see alter table stuff below, modifications
CREATE TABLE ebird_taxonomy (
    id VARCHAR(12) PRIMARY KEY,
    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,
    UNIQUE (species_code, taxonomy_version)
);
-- Species Table (mutable)
CREATE TABLE species (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    label VARCHAR(100) NOT NULL, -- display label for the species
    ebird_code VARCHAR(12), -- link to ebird taxonomy
    taxonomy_version VARCHAR(4),
    description VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (ebird_code, taxonomy_version) REFERENCES ebird_taxonomy(species_code, taxonomy_version)
);
CREATE INDEX idx_species_label ON species(label);
CREATE INDEX idx_species_ebird ON species(ebird_code);
-- Call Types Table (mutable)
CREATE TABLE call_type (
    id VARCHAR(12) PRIMARY KEY, -- nanoid(12)
    species_id VARCHAR(12) NOT NULL, -- link to parent species
    label VARCHAR(100) NOT NULL, -- display name like "male", "female", "duet"
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (species_id) REFERENCES species(id)
);
CREATE INDEX idx_call_type_species ON call_type(species_id);
CREATE INDEX idx_call_type_label ON call_type(label);
CREATE TABLE filter (
    id VARCHAR(12) PRIMARY KEY, -- nanoid
    name VARCHAR(140) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN NOT NULL DEFAULT true
);
-- Label Table, many to 1 relationship with
CREATE TABLE label (
    id VARCHAR(21) PRIMARY KEY, -- nanoid
    selection_id VARCHAR(21) NOT NULL, -- link to selection table
    species_id VARCHAR(12) NOT NULL, -- link to species table
    filter_id VARCHAR(12),
    certainty DECIMAL(5,2) CHECK (certainty <= 100 AND certainty >= 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (selection_id) REFERENCES selection(id),
    FOREIGN KEY (species_id) REFERENCES species(id),
    FOREIGN KEY (filter_id) REFERENCES filter(id)
);
CREATE INDEX idx_label_selection_id ON label(selection_id);
CREATE INDEX idx_label_species_id ON label(species_id);
-- Label Sub-type Table (optional 1:1 relationship with label)
CREATE TABLE label_subtype (
    id VARCHAR(21) PRIMARY KEY, -- nanoid
    label_id VARCHAR(21) NOT NULL, -- link to parent label
    calltype_id VARCHAR(12) NOT NULL, -- link to call_type table
    filter_id VARCHAR(12),
    certainty DECIMAL(5,2) CHECK (certainty <= 100 AND certainty >= 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (label_id) REFERENCES label(id),
    FOREIGN KEY (calltype_id) REFERENCES call_type(id),
    FOREIGN KEY (filter_id) REFERENCES filter(id)
    -- UNIQUE (label_id) -- ensures 1:1 relationship with label. how do i handle multiple call type filters? needs 1:many relation
);
-- UNIMPLEMENTED
-- file_dataset must exist first
-- Referential Integrity for Selections
-- To ensure `selections.dataset_id` is valid for the associated file:
-- Add composite foreign key (requires file_dataset to exist first)
-- ALTER TABLE selection ADD CONSTRAINT fk_selection_file_dataset
-- FOREIGN KEY (file_id, dataset_id) REFERENCES file_dataset(file_id, dataset_id);
-- 2024 Taxonomy View, after populating underlying table
-- see working with ebird taxonomies, aichat, deepseek, macbook
-- I think I still need this to display the options when someone creates a species
CREATE TABLE ebird_taxonomy_v2024 AS
SELECT
    id,
    species_code,
    primary_com_name,
    sci_name,
    bird_order, -- AS "order",  order is reserved word in pgsql
    family
FROM ebird_taxonomy
WHERE taxonomy_version = '2024';
-- to help with plain text search on common name and scientific name
CREATE INDEX idx_ebird_name_search ON ebird_taxonomy_v2024 USING gin(to_tsvector('english', primary_com_name || ' ' || sci_name));
CREATE INDEX ebird_taxonomy_species_code ON ebird_taxonomy_v2024(species_code);
-- Junction Table for Species to Dataset (many-to-many)
CREATE TABLE species_dataset (
    species_id VARCHAR(12) NOT NULL,
    dataset_id VARCHAR(12) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (species_id, dataset_id),
    FOREIGN KEY (species_id) REFERENCES species(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)
);
-- indexes for the junction table
CREATE INDEX idx_species_dataset_species ON species_dataset(species_id);
CREATE INDEX idx_species_dataset_dataset ON species_dataset(dataset_id);

    FOREIGN KEY (dataset_id) REFERENCES dataset(id),

    -- FOREIGN KEY (dataset_id) REFERENCES dataset(id),

    FOREIGN KEY (dataset_id) REFERENCES dataset(id),
    FOREIGN KEY (location_id) REFERENCES location(id),
    FOREIGN KEY (cyclic_recording_pattern_id) REFERENCES cyclic_recording_pattern(id),

    -- FOREIGN KEY (dataset_id) REFERENCES dataset(id),
    -- FOREIGN KEY (location_id) REFERENCES location(id),
    -- FOREIGN KEY (cyclic_recording_pattern_id) REFERENCES cyclic_recording_pattern(id),

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (location_id) REFERENCES location(id),
    FOREIGN KEY (cluster_id) REFERENCES cluster(id)

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (location_id) REFERENCES location(id),
    -- FOREIGN KEY (cluster_id) REFERENCES cluster(id)

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id)

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (file_id) REFERENCES file(id)

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id)

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (file_id) REFERENCES file(id)

    PRIMARY KEY (file_id, dataset_id),
    FOREIGN KEY (file_id) REFERENCES file(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)

    PRIMARY KEY (file_id, dataset_id) -- ,
    -- FOREIGN KEY (file_id) REFERENCES file(id),
    -- FOREIGN KEY (dataset_id) REFERENCES dataset(id)

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (file_id) REFERENCES file(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id),
    FOREIGN KEY (file_id, dataset_id) REFERENCES file_dataset(file_id, dataset_id) 

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (file_id) REFERENCES file(id),
    -- FOREIGN KEY (dataset_id) REFERENCES dataset(id),
    -- FOREIGN KEY (file_id, dataset_id) REFERENCES file_dataset(file_id, dataset_id) 

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (ebird_code, taxonomy_version) REFERENCES ebird_taxonomy(species_code, taxonomy_version)

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (ebird_code, taxonomy_version) REFERENCES ebird_taxonomy(species_code, taxonomy_version)

    active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (species_id) REFERENCES species(id)

    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (species_id) REFERENCES species(id)

    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (segment_id) REFERENCES segment(id),
    FOREIGN KEY (species_id) REFERENCES species(id),
    FOREIGN KEY (filter_id) REFERENCES filter(id)

    active BOOLEAN NOT NULL DEFAULT true -- ,
    -- FOREIGN KEY (segment_id) REFERENCES segment(id),
    -- FOREIGN KEY (species_id) REFERENCES species(id),
    -- FOREIGN KEY (filter_id) REFERENCES filter(id)

 label_id VARCHAR(21) PRIMARY KEY,
 json JSON,
 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
 last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
 active BOOLEAN DEFAULT TRUE,
 FOREIGN KEY (label_id) REFERENCES label(id)

    label_id VARCHAR(21) PRIMARY KEY,
    json JSON,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE -- ,
    -- FOREIGN KEY (label_id) REFERENCES label(id)

    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (label_id) REFERENCES label(id),
    FOREIGN KEY (calltype_id) REFERENCES call_type(id),
    FOREIGN KEY (filter_id) REFERENCES filter(id)

    active BOOLEAN NOT NULL DEFAULT true -- ,
    -- FOREIGN KEY (label_id) REFERENCES label(id),
    -- FOREIGN KEY (calltype_id) REFERENCES call_type(id),
    -- FOREIGN KEY (filter_id) REFERENCES filter(id)