NLDETC7D5WRGA4U6FIGBV2KKY4L2TLWOTH3HU4ZCU2SUV63EGAXQC CCML2MSKZBXNILUOG7526T6PMCCNTFZ4B6SBAXUMRG7PCOEFG4EQC NY5P6UPQVIVTLYRKDHSRZPZRM6KTV3VDYGJP6FSEWG6BXOTW6U3AC VT3A2ORJW3CJV4VZEKVQEU6XZ35RNBLMSQJOKYHATXKGMZLAZKQAC YZFIRGIEBHYDZQEX6NPU5N76FQB3KDDPCOYMWNQIYIUD7NJMT2ZQC 47GPFVLW7RWBBHHUZYMEEYWG3KBJBWELR7RDKMJRWMNRWYJUBR7QC J62FGJ3BGFTUWEOUON4ATYNDFBEUIR6FCIOZSHOVHAA7KGFYHW6AC 76377HW6NPWGB77BON4ZDDVMWD4QHQARAMUTJGZCIW7PE2MVTUAAC EBCNGTNVY2YFFHKC4PHDEHNBOWJ4JDCEXTTJ3WKD5VWQZLLDZ65AC RFSUR7ZEXTQNHH3IFJAL2NNOTGRPWOWB3PFIVH7VLI2JPTIBMW5AC IFVRAERTCCDICNTYTG3TX2WASB6RXQQEJWWXQMQZJSQDQ3HLE5OQC NKQAT3RE4IBIWXVMI5LJUINDPHTANNMORZ5N2JFA4AN6UUB72KGAC HA4ZCSOTKGXU2Q22PJVFIBTPUNXQPDSKYBHFFMV74L56UJMVM3PQC }// EntityExists checks if an entity exists in the databasefunc EntityExists(db *sql.DB, table, id string) (bool, error) {var exists boolquery := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ?)", table)err := db.QueryRow(query, id).Scan(&exists)return exists, err}// EntityExistsAndActive checks if an entity exists and is activefunc EntityExistsAndActive(db *sql.DB, table, id string) (bool, error) {var exists boolquery := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ? AND active = true)", table)err := db.QueryRow(query, id).Scan(&exists)return exists, err}// GetEntityActiveStatus returns whether an entity is active (and exists)// Returns: (exists, active, error)func GetEntityActiveStatus(db *sql.DB, table, id string) (bool, bool, error) {var exists boolvar active boolquery := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ?), COALESCE((SELECT active FROM %s WHERE id = ?), false)", table, table)err := db.QueryRow(query, id, id).Scan(&exists, &active)return exists, active, err
// ValidateClusterBelongsToLocation checks that a cluster belongs to a specific location// Returns error if cluster doesn't exist or belongs to a different locationfunc ValidateClusterBelongsToLocation(db *sql.DB, clusterID, locationID string) error {var clusterLocationID stringerr := db.QueryRow("SELECT location_id FROM cluster WHERE id = ? AND active = true", clusterID).Scan(&clusterLocationID)if err == sql.ErrNoRows {return fmt.Errorf("cluster not found or inactive: %s", clusterID)}if err != nil {return fmt.Errorf("failed to query cluster: %w", err)}if clusterLocationID != locationID {return fmt.Errorf("cluster %s does not belong to location %s", clusterID, locationID)}return nil}
// buildCountReferencedQuery builds a count query for referenced tablesfunc buildCountReferencedQuery(tr TableRelationship) string {// Only filter table is referenced via label -> segment -> dataset_idreturn `SELECT COUNT(DISTINCT filter.id) FROM filterINNER JOIN label ON filter.id = label.filter_idINNER JOIN segment ON label.segment_id = segment.idWHERE segment.dataset_id = ?`}
}// buildAviaNZDataFile creates an AviaNZ .data structure from calls (for new files only)func buildAviaNZDataFile(calls []ClusteredCall, filter string, duration float64, sampleRate int) []any {meta, segments := buildAviaNZMetaAndSegments(calls, filter, duration, sampleRate)return buildDataFileFromSegments(meta, segments)
// MarshalJSON implements custom JSON marshaling for File// Formats timestamps as RFC3339func (f File) MarshalJSON() ([]byte, error) {return json.Marshal(&struct {ID string `json:"id"`FileName string `json:"file_name"`Path *string `json:"path"`XXH64Hash string `json:"xxh64_hash"`LocationID string `json:"location_id"`TimestampLocal string `json:"timestamp_local"`ClusterID *string `json:"cluster_id"`Duration float64 `json:"duration"`SampleRate int `json:"sample_rate"`Description *string `json:"description"`MaybeSolarNight *bool `json:"maybe_solar_night"`MaybeCivilNight *bool `json:"maybe_civil_night"`MoonPhase *float64 `json:"moon_phase"`CreatedAt string `json:"created_at"`LastModified string `json:"last_modified"`Active bool `json:"active"`}{ID: f.ID,FileName: f.FileName,Path: f.Path,XXH64Hash: f.XXH64Hash,LocationID: f.LocationID,TimestampLocal: f.TimestampLocal.Format(time.RFC3339),ClusterID: f.ClusterID,Duration: f.Duration,SampleRate: f.SampleRate,Description: f.Description,MaybeSolarNight: f.MaybeSolarNight,MaybeCivilNight: f.MaybeCivilNight,MoonPhase: f.MoonPhase,CreatedAt: f.CreatedAt.Format(time.RFC3339),LastModified: f.LastModified.Format(time.RFC3339),Active: f.Active,})}
// MarshalJSON implements custom JSON marshaling for MothMetadata// Formats timestamps as RFC3339func (m MothMetadata) MarshalJSON() ([]byte, error) {return json.Marshal(&struct {FileID string `json:"file_id"`Timestamp string `json:"timestamp"`RecorderID *string `json:"recorder_id"`Gain *GainLevel `json:"gain"`BatteryV *float64 `json:"battery_v"`TempC *float64 `json:"temp_c"`CreatedAt string `json:"created_at"`LastModified string `json:"last_modified"`Active bool `json:"active"`}{FileID: m.FileID,Timestamp: m.Timestamp.Format(time.RFC3339),RecorderID: m.RecorderID,Gain: m.Gain,BatteryV: m.BatteryV,TempC: m.TempC,CreatedAt: m.CreatedAt.Format(time.RFC3339),LastModified: m.LastModified.Format(time.RFC3339),Active: m.Active,})}
// MarshalJSON implements custom JSON marshaling for FileDataset// Formats timestamps as RFC3339func (fd FileDataset) MarshalJSON() ([]byte, error) {return json.Marshal(&struct {FileID string `json:"file_id"`DatasetID string `json:"dataset_id"`CreatedAt string `json:"created_at"`LastModified string `json:"last_modified"`}{FileID: fd.FileID,DatasetID: fd.DatasetID,CreatedAt: fd.CreatedAt.Format(time.RFC3339),LastModified: fd.LastModified.Format(time.RFC3339),})}
// RunPattern handles the "pattern" subcommandfunc RunPattern(args []string) {if len(args) < 1 {printPatternUsage()os.Exit(1)}switch args[0] {case "create":RunPatternCreate(args[1:])case "update":RunPatternUpdate(args[1:])default:fmt.Fprintf(os.Stderr, "Unknown pattern subcommand: %s\n\n", args[0])printPatternUsage()os.Exit(1)}}
func printPatternUsage() {fmt.Fprintf(os.Stderr, "Usage: skraak pattern <subcommand> [options]\n\n")fmt.Fprintf(os.Stderr, "Subcommands:\n")fmt.Fprintf(os.Stderr, " create Create a new recording pattern\n")fmt.Fprintf(os.Stderr, " update Update an existing recording pattern\n")fmt.Fprintf(os.Stderr, "\nExamples:\n")fmt.Fprintf(os.Stderr, " skraak pattern create --db ./db/skraak.duckdb --record 60 --sleep 1740\n")fmt.Fprintf(os.Stderr, " skraak pattern update --db ./db/skraak.duckdb --id pattern123 --record 30\n")}
// RunLocation handles the "location" subcommandfunc RunLocation(args []string) {if len(args) < 1 {printLocationUsage()os.Exit(1)}switch args[0] {case "create":RunLocationCreate(args[1:])case "update":RunLocationUpdate(args[1:])default:fmt.Fprintf(os.Stderr, "Unknown location subcommand: %s\n\n", args[0])printLocationUsage()os.Exit(1)}}
func printLocationUsage() {fmt.Fprintf(os.Stderr, "Usage: skraak location <subcommand> [options]\n\n")fmt.Fprintf(os.Stderr, "Subcommands:\n")fmt.Fprintf(os.Stderr, " create Create a new location\n")fmt.Fprintf(os.Stderr, " update Update an existing location\n")fmt.Fprintf(os.Stderr, "\nExamples:\n")fmt.Fprintf(os.Stderr, " skraak location create --db ./db/skraak.duckdb --dataset abc123 --name \"Site A\" --lat -36.85 --lon 174.76 --timezone Pacific/Auckland\n")fmt.Fprintf(os.Stderr, " skraak location update --db ./db/skraak.duckdb --id loc123 --name \"Updated Name\"\n")}
switch args[0] {case "create":RunDatasetCreate(args[1:])case "update":RunDatasetUpdate(args[1:])default:fmt.Fprintf(os.Stderr, "Unknown dataset subcommand: %s\n\n", args[0])printDatasetUsage()os.Exit(1)}}func printDatasetUsage() {fmt.Fprintf(os.Stderr, "Usage: skraak dataset <subcommand> [options]\n\n")fmt.Fprintf(os.Stderr, "Subcommands:\n")fmt.Fprintf(os.Stderr, " create Create a new dataset (type defaults to 'structured')\n")fmt.Fprintf(os.Stderr, " update Update an existing dataset\n")fmt.Fprintf(os.Stderr, "\nExamples:\n")fmt.Fprintf(os.Stderr, " skraak dataset create --db ./db/skraak.duckdb --name \"Test Dataset\"\n")fmt.Fprintf(os.Stderr, " skraak dataset create --db ./db/skraak.duckdb --name \"Training Data\" --type train\n")fmt.Fprintf(os.Stderr, " skraak dataset update --db ./db/skraak.duckdb --id abc123 --name \"Updated Name\"\n")}
// RunRecordingCluster handles the "cluster" subcommand (for database cluster records)func RunRecordingCluster(args []string) {if len(args) < 1 {printRecordingClusterUsage()os.Exit(1)}switch args[0] {case "create":RunClusterCreate(args[1:])case "update":RunClusterUpdate(args[1:])default:fmt.Fprintf(os.Stderr, "Unknown cluster subcommand: %s\n\n", args[0])printRecordingClusterUsage()os.Exit(1)}}
func printRecordingClusterUsage() {fmt.Fprintf(os.Stderr, "Usage: skraak cluster <subcommand> [options]\n\n")fmt.Fprintf(os.Stderr, "Subcommands:\n")fmt.Fprintf(os.Stderr, " create Create a new cluster\n")fmt.Fprintf(os.Stderr, " update Update an existing cluster\n")fmt.Fprintf(os.Stderr, "\nExamples:\n")fmt.Fprintf(os.Stderr, " skraak cluster create --db ./db/skraak.duckdb --dataset abc123 --location loc456 --name \"2024-01\" --sample-rate 250000\n")fmt.Fprintf(os.Stderr, " skraak cluster update --db ./db/skraak.duckdb --id clust123 --name \"New Name\"\n")}