package utils
import (
	"fmt"
	"os"
	"github.com/cespare/xxhash/v2"
)
// ComputeXXH64 computes the XXH64 hash of a file with seed=0.
// Returns the hash as a 16-character lowercase hexadecimal string.
func ComputeXXH64(filepath string) (string, error) {
	// Read entire file as binary data
	data, err := os.ReadFile(filepath)
	if err != nil {
		return "", fmt.Errorf("failed to read file: %w", err)
	}
	// Compute XXH64 hash with default seed=0
	hashValue := xxhash.Sum64(data)
	// Format as 16-character lowercase hex with zero-padding
	// %016x = hex, lowercase, zero-padded to 16 chars
	return fmt.Sprintf("%016x", hashValue), nil
}

package utils
import (
	"bytes"
	"encoding/binary"
	"fmt"
	"io"
	"os"
)
// WAVMetadata contains metadata extracted from WAV file headers
type WAVMetadata struct {
	Duration   float64 // Duration in seconds
	SampleRate int     // Sample rate in Hz
	Comment    string  // Comment from INFO chunk (may contain AudioMoth data)
	Artist     string  // Artist from INFO chunk
	Channels   int     // Number of audio channels
	BitsPerSample int  // Bits per sample
}
// ParseWAVHeader efficiently reads only the WAV file header to extract metadata.
// It reads the first 200KB of the file, which should be sufficient for all header chunks.
func ParseWAVHeader(filepath string) (*WAVMetadata, error) {
	file, err := os.Open(filepath)
	if err != nil {
		return nil, fmt.Errorf("failed to open file: %w", err)
	}
	defer file.Close()
	// Read first 200KB for header parsing (more than enough for metadata)
	headerBuf := make([]byte, 200*1024)
	n, err := file.Read(headerBuf)
	if err != nil && err != io.EOF {
		return nil, fmt.Errorf("failed to read header: %w", err)
	}
	headerBuf = headerBuf[:n]
	return parseWAVFromBytes(headerBuf, filepath)
}
// parseWAVFromBytes parses WAV metadata from a byte buffer
func parseWAVFromBytes(data []byte, filepath string) (*WAVMetadata, error) {
	if len(data) < 44 {
		return nil, fmt.Errorf("file too small to be valid WAV")
	}
	// Verify RIFF header
	if string(data[0:4]) != "RIFF" {
		return nil, fmt.Errorf("not a valid WAV file (missing RIFF header)")
	}
	// Verify WAVE format
	if string(data[8:12]) != "WAVE" {
		return nil, fmt.Errorf("not a valid WAV file (missing WAVE format)")
	}
	metadata := &WAVMetadata{}
	// Parse chunks
	offset := 12
	for offset < len(data)-8 {
		// Read chunk ID and size
		chunkID := string(data[offset : offset+4])
		chunkSize := int(binary.LittleEndian.Uint32(data[offset+4 : offset+8]))
		offset += 8
		// Ensure we don't read beyond buffer
		if offset+chunkSize > len(data) {
			break
		}
		switch chunkID {
		case "fmt ":
			// Parse format chunk
			if chunkSize >= 16 {
				// audioFormat := binary.LittleEndian.Uint16(data[offset : offset+2])
				metadata.Channels = int(binary.LittleEndian.Uint16(data[offset+2 : offset+4]))
				metadata.SampleRate = int(binary.LittleEndian.Uint32(data[offset+4 : offset+8]))
				// byteRate := binary.LittleEndian.Uint32(data[offset+8 : offset+12])
				// blockAlign := binary.LittleEndian.Uint16(data[offset+12 : offset+14])
				metadata.BitsPerSample = int(binary.LittleEndian.Uint16(data[offset+14 : offset+16]))
			}
		case "data":
			// Calculate duration from data chunk size
			if metadata.SampleRate > 0 && metadata.Channels > 0 && metadata.BitsPerSample > 0 {
				bytesPerSample := metadata.BitsPerSample / 8
				bytesPerSecond := metadata.SampleRate * metadata.Channels * bytesPerSample
				if bytesPerSecond > 0 {
					metadata.Duration = float64(chunkSize) / float64(bytesPerSecond)
				}
			}
		case "LIST":
			// Parse LIST chunk for INFO metadata
			if chunkSize >= 4 {
				listType := string(data[offset : offset+4])
				if listType == "INFO" {
					// Parse INFO subchunks
					parseINFOChunk(data[offset+4:offset+chunkSize], metadata)
				}
			}
		}
		// Move to next chunk (chunks are word-aligned)
		offset += chunkSize
		if chunkSize%2 != 0 {
			offset++ // Skip padding byte
		}
	}
	// If duration couldn't be calculated from data chunk, try from file size
	if metadata.Duration == 0 {
		if fileInfo, err := os.Stat(filepath); err == nil {
			// Estimate: fileSize - header (assume ~100 bytes header)
			dataSize := fileInfo.Size() - 100
			if metadata.SampleRate > 0 && metadata.Channels > 0 && metadata.BitsPerSample > 0 {
				bytesPerSample := metadata.BitsPerSample / 8
				bytesPerSecond := metadata.SampleRate * metadata.Channels * bytesPerSample
				if bytesPerSecond > 0 {
					metadata.Duration = float64(dataSize) / float64(bytesPerSecond)
				}
			}
		}
	}
	return metadata, nil
}
// parseINFOChunk parses INFO list chunk for comment and artist metadata
func parseINFOChunk(data []byte, metadata *WAVMetadata) {
	offset := 0
	for offset < len(data)-8 {
		// Read subchunk ID and size
		if offset+8 > len(data) {
			break
		}
		subchunkID := string(data[offset : offset+4])
		subchunkSize := int(binary.LittleEndian.Uint32(data[offset+4 : offset+8]))
		offset += 8
		if offset+subchunkSize > len(data) {
			break
		}
		// Extract null-terminated string
		value := extractNullTerminatedString(data[offset : offset+subchunkSize])
		switch subchunkID {
		case "ICMT": // Comment
			metadata.Comment = value
		case "IART": // Artist
			metadata.Artist = value
		}
		// Move to next subchunk (word-aligned)
		offset += subchunkSize
		if subchunkSize%2 != 0 {
			offset++ // Skip padding byte
		}
	}
}
// extractNullTerminatedString extracts a null-terminated string from bytes
func extractNullTerminatedString(data []byte) string {
	nullIdx := bytes.IndexByte(data, 0)
	if nullIdx >= 0 {
		return string(data[:nullIdx])
	}
	return string(data)
}

package utils
import (
	"fmt"
	"path/filepath"
	"regexp"
	"strconv"
	"time"
)
// DateFormat represents the detected filename date format
type DateFormat int
const (
	Format8Digit DateFormat = iota // YYYYMMDD_HHMMSS (e.g., 20230609_103000.wav)
	Format6YYMMDD                  // YYMMDD_HHMMSS (e.g., 201012_123456.wav) - year first
	Format6DDMMYY                  // DDMMYY_HHMMSS (e.g., 121020_123456.wav) - year last
)
var (
	// Pattern to match timestamp filenames
	// Supports: YYYYMMDD_HHMMSS, YYMMDD_HHMMSS, DDMMYY_HHMMSS
	timestampPattern = regexp.MustCompile(`^(\d{6,8})_(\d{6})\.wav$`)
)
// dateParts represents parsed date components for format detection
type dateParts struct {
	x1 int // First 2 digits
	m  int // Middle 2 digits (always month)
	x2 int // Last 2 digits
}
// FilenameTimestamp represents a parsed timestamp from a filename
type FilenameTimestamp struct {
	Filename  string
	Timestamp time.Time
	Format    DateFormat
}
// ParseFilenameTimestamps parses timestamps from a batch of filenames.
// Uses variance-based disambiguation for 6-digit dates (YYMMDD vs DDMMYY).
// Returns timestamps in UTC (timezone must be applied separately).
func ParseFilenameTimestamps(filenames []string) ([]FilenameTimestamp, error) {
	if len(filenames) == 0 {
		return nil, fmt.Errorf("no filenames provided")
	}
	// Detect date format by analyzing all filenames
	format, err := detectDateFormat(filenames)
	if err != nil {
		return nil, err
	}
	// Parse all filenames using detected format
	results := make([]FilenameTimestamp, 0, len(filenames))
	for _, filename := range filenames {
		timestamp, err := parseFilenameWithFormat(filename, format)
		if err != nil {
			return nil, fmt.Errorf("failed to parse %s: %w", filename, err)
		}
		results = append(results, FilenameTimestamp{
			Filename:  filename,
			Timestamp: timestamp,
			Format:    format,
		})
	}
	return results, nil
}
// ApplyTimezoneOffset applies a fixed timezone offset to timestamps
// Uses the first timestamp to determine the offset, then applies it to all
// This matches AudioMoth behavior (no DST adjustment during deployment)
func ApplyTimezoneOffset(timestamps []FilenameTimestamp, timezoneID string) ([]time.Time, error) {
	if len(timestamps) == 0 {
		return nil, fmt.Errorf("no timestamps provided")
	}
	// Load timezone location
	loc, err := time.LoadLocation(timezoneID)
	if err != nil {
		return nil, fmt.Errorf("invalid timezone %s: %w", timezoneID, err)
	}
	// Calculate offset from first timestamp
	firstUTC := timestamps[0].Timestamp
	firstInZone := time.Date(
		firstUTC.Year(), firstUTC.Month(), firstUTC.Day(),
		firstUTC.Hour(), firstUTC.Minute(), firstUTC.Second(),
		0, loc,
	)
	// Get fixed offset (doesn't change for DST)
	_, offsetSeconds := firstInZone.Zone()
	fixedOffset := time.FixedZone("Fixed", offsetSeconds)
	// Apply SAME offset to ALL timestamps
	results := make([]time.Time, len(timestamps))
	for i, ts := range timestamps {
		adjusted := time.Date(
			ts.Timestamp.Year(), ts.Timestamp.Month(), ts.Timestamp.Day(),
			ts.Timestamp.Hour(), ts.Timestamp.Minute(), ts.Timestamp.Second(),
			0, fixedOffset,
		)
		results[i] = adjusted
	}
	return results, nil
}
// detectDateFormat analyzes filenames to determine the date format
func detectDateFormat(filenames []string) (DateFormat, error) {
	// Extract all date parts from filenames
	var parts []dateParts
	var has8Digit bool
	for _, filename := range filenames {
		basename := filepath.Base(filename)
		matches := timestampPattern.FindStringSubmatch(basename)
		if matches == nil {
			continue
		}
		dateStr := matches[1]
		// Check for 8-digit format (YYYYMMDD)
		if len(dateStr) == 8 {
			has8Digit = true
			continue
		}
		// Parse 6-digit format
		if len(dateStr) == 6 {
			x1, _ := strconv.Atoi(dateStr[0:2])
			m, _ := strconv.Atoi(dateStr[2:4])
			x2, _ := strconv.Atoi(dateStr[4:6])
			parts = append(parts, dateParts{x1: x1, m: m, x2: x2})
		}
	}
	// If all files are 8-digit, that's the format
	if has8Digit && len(parts) == 0 {
		return Format8Digit, nil
	}
	// If mixed 8-digit and 6-digit, prefer 8-digit
	if has8Digit {
		return Format8Digit, nil
	}
	// If no 6-digit dates found, cannot determine
	if len(parts) == 0 {
		return 0, fmt.Errorf("no valid timestamp filenames found")
	}
	// Use variance-based disambiguation for 6-digit dates
	// Compare uniqueness of x1 (first 2 digits) vs x2 (last 2 digits)
	// Day values vary more than year values across recordings
	uniqueX1 := countUnique(parts, func(p dateParts) int { return p.x1 })
	uniqueX2 := countUnique(parts, func(p dateParts) int { return p.x2 })
	if uniqueX2 >= uniqueX1 {
		// x2 has more variance → likely day values → YYMMDD format
		return Format6YYMMDD, nil
	} else {
		// x1 has more variance → likely day values → DDMMYY format
		return Format6DDMMYY, nil
	}
}
// parseFilenameWithFormat parses a filename using the specified format
func parseFilenameWithFormat(filename string, format DateFormat) (time.Time, error) {
	basename := filepath.Base(filename)
	matches := timestampPattern.FindStringSubmatch(basename)
	if matches == nil {
		return time.Time{}, fmt.Errorf("filename does not match timestamp pattern: %s", basename)
	}
	dateStr := matches[1]
	timeStr := matches[2]
	var year, month, day int
	switch format {
	case Format8Digit:
		if len(dateStr) != 8 {
			return time.Time{}, fmt.Errorf("expected 8-digit date, got %d digits", len(dateStr))
		}
		year, _ = strconv.Atoi(dateStr[0:4])
		month, _ = strconv.Atoi(dateStr[4:6])
		day, _ = strconv.Atoi(dateStr[6:8])
	case Format6YYMMDD:
		if len(dateStr) != 6 {
			return time.Time{}, fmt.Errorf("expected 6-digit date, got %d digits", len(dateStr))
		}
		yy, _ := strconv.Atoi(dateStr[0:2])
		month, _ = strconv.Atoi(dateStr[2:4])
		day, _ = strconv.Atoi(dateStr[4:6])
		// Convert 2-digit year to 4-digit (assume 2000-2099)
		year = 2000 + yy
	case Format6DDMMYY:
		if len(dateStr) != 6 {
			return time.Time{}, fmt.Errorf("expected 6-digit date, got %d digits", len(dateStr))
		}
		day, _ = strconv.Atoi(dateStr[0:2])
		month, _ = strconv.Atoi(dateStr[2:4])
		yy, _ := strconv.Atoi(dateStr[4:6])
		// Convert 2-digit year to 4-digit (assume 2000-2099)
		year = 2000 + yy
	}
	// Parse time (HHMMSS)
	if len(timeStr) != 6 {
		return time.Time{}, fmt.Errorf("invalid time format: %s", timeStr)
	}
	hour, _ := strconv.Atoi(timeStr[0:2])
	minute, _ := strconv.Atoi(timeStr[2:4])
	second, _ := strconv.Atoi(timeStr[4:6])
	// Construct timestamp in UTC (timezone applied separately)
	timestamp := time.Date(year, time.Month(month), day, hour, minute, second, 0, time.UTC)
	// Validate date
	if timestamp.Month() != time.Month(month) || timestamp.Day() != day {
		return time.Time{}, fmt.Errorf("invalid date: %04d-%02d-%02d", year, month, day)
	}
	return timestamp, nil
}
// countUnique counts unique values using an extractor function
func countUnique(parts []dateParts, extractor func(p dateParts) int) int {
	seen := make(map[int]bool)
	for _, p := range parts {
		seen[extractor(p)] = true
	}
	return len(seen)
}
// HasTimestampFilename checks if a filename matches the timestamp pattern
func HasTimestampFilename(filename string) bool {
	basename := filepath.Base(filename)
	return timestampPattern.MatchString(basename)
}

package utils
import (
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"
	"skraak_mcp/db"
)
// AudioMothData contains parsed data from AudioMoth comment field
type AudioMothData struct {
	Timestamp  time.Time
	RecorderID string
	Gain       db.GainLevel
	BatteryV   float64
	TempC      float64
}
// AudioMoth comment example:
// "Recorded at 21:00:00 24/02/2025 (UTC+13) by AudioMoth 248AB50153AB0549 at medium gain while battery was 4.3V and temperature was 15.8C."
var (
	// Pattern to detect AudioMoth comments
	audiomothPattern = regexp.MustCompile(`(?i)AudioMoth`)
	// Pattern to extract structured data
	// Matches: "Recorded at HH:MM:SS DD/MM/YYYY (UTC±HH) by AudioMoth HEXID at GAIN gain while battery was X.XV and temperature was Y.YC."
	structuredPattern = regexp.MustCompile(
		`Recorded at (\d{2}:\d{2}:\d{2}) (\d{2}/\d{2}/\d{4}) \(UTC([+-]\d+)\) by AudioMoth ([A-F0-9]+) at ([\w-]+) gain while battery was ([\d.]+)V and temperature was ([-\d.]+)C`,
	)
)
// IsAudioMoth checks if the comment or artist field indicates an AudioMoth recording
func IsAudioMoth(comment, artist string) bool {
	return audiomothPattern.MatchString(comment) || audiomothPattern.MatchString(artist)
}
// ParseAudioMothComment parses structured AudioMoth comment field
// Returns parsed data or error if parsing fails
func ParseAudioMothComment(comment string) (*AudioMothData, error) {
	// Try structured parsing first (newer format)
	if data, err := parseStructuredComment(comment); err == nil {
		return data, nil
	}
	// Fallback to legacy space-separated parsing
	return parseLegacyComment(comment)
}
// parseStructuredComment parses newer AudioMoth comment format using regex
func parseStructuredComment(comment string) (*AudioMothData, error) {
	matches := structuredPattern.FindStringSubmatch(comment)
	if matches == nil {
		return nil, fmt.Errorf("comment does not match structured AudioMoth format")
	}
	// Extract matched groups
	timeStr := matches[1]       // HH:MM:SS
	dateStr := matches[2]       // DD/MM/YYYY
	timezoneStr := matches[3]   // ±HH
	recorderID := matches[4]    // Hex ID
	gainStr := matches[5]       // gain level
	batteryStr := matches[6]    // battery voltage
	tempStr := matches[7]       // temperature
	// Parse timestamp
	timestamp, err := parseAudioMothTimestamp(timeStr, dateStr, timezoneStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse timestamp: %w", err)
	}
	// Parse gain
	gain, err := parseGainLevel(gainStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse gain: %w", err)
	}
	// Parse battery voltage
	batteryV, err := strconv.ParseFloat(batteryStr, 64)
	if err != nil {
		return nil, fmt.Errorf("failed to parse battery voltage: %w", err)
	}
	// Parse temperature
	tempC, err := strconv.ParseFloat(tempStr, 64)
	if err != nil {
		return nil, fmt.Errorf("failed to parse temperature: %w", err)
	}
	return &AudioMothData{
		Timestamp:  timestamp,
		RecorderID: recorderID,
		Gain:       gain,
		BatteryV:   batteryV,
		TempC:      tempC,
	}, nil
}
// parseLegacyComment parses older AudioMoth comment format (space-separated)
// Example: "Recorded at 21:00:00 24/02/2025 (UTC+13) by AudioMoth 248AB50153AB0549 at medium gain while battery was 4.3V and temperature was 15.8C."
func parseLegacyComment(comment string) (*AudioMothData, error) {
	parts := strings.Fields(comment)
	if len(parts) < 10 {
		return nil, fmt.Errorf("comment has insufficient parts (got %d, need at least 10)", len(parts))
	}
	// 0-based indices after split by space:
	// parts[2] = "21:00:00" (time HH:MM:SS)
	// parts[3] = "24/02/2025" (date DD/MM/YYYY)
	// parts[4] = "(UTC+13)" (timezone offset)
	// parts[7] = "248AB50153AB0549" (moth ID)
	// parts[9] = "medium" (gain)
	// parts[len-5] = "4.3V" (battery voltage)
	// parts[len-1] = "15.8C." (temperature)
	timeStr := parts[2]
	dateStr := parts[3]
	timezoneStr := strings.Trim(parts[4], "()")
	recorderID := parts[7]
	gainStr := parts[9]
	// Parse timestamp
	timestamp, err := parseAudioMothTimestamp(timeStr, dateStr, timezoneStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse timestamp: %w", err)
	}
	// Parse gain
	gain, err := parseGainLevel(gainStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse gain: %w", err)
	}
	// Parse battery voltage (e.g., "4.3V")
	batteryStr := parts[len(parts)-5]
	batteryStr = strings.TrimSuffix(batteryStr, "V")
	batteryV, err := strconv.ParseFloat(batteryStr, 64)
	if err != nil {
		return nil, fmt.Errorf("failed to parse battery voltage: %w", err)
	}
	// Parse temperature (e.g., "15.8C." or "15.8C")
	tempStr := parts[len(parts)-1]
	tempStr = strings.TrimSuffix(tempStr, ".")
	tempStr = strings.TrimSuffix(tempStr, "C")
	tempC, err := strconv.ParseFloat(tempStr, 64)
	if err != nil {
		return nil, fmt.Errorf("failed to parse temperature: %w", err)
	}
	return &AudioMothData{
		Timestamp:  timestamp,
		RecorderID: recorderID,
		Gain:       gain,
		BatteryV:   batteryV,
		TempC:      tempC,
	}, nil
}
// parseAudioMothTimestamp parses AudioMoth timestamp from time, date, and timezone strings
// timeStr: "HH:MM:SS"
// dateStr: "DD/MM/YYYY"
// timezoneStr: "UTC+13" or "+13"
func parseAudioMothTimestamp(timeStr, dateStr, timezoneStr string) (time.Time, error) {
	// Parse time components
	timeParts := strings.Split(timeStr, ":")
	if len(timeParts) != 3 {
		return time.Time{}, fmt.Errorf("invalid time format: %s", timeStr)
	}
	hour, _ := strconv.Atoi(timeParts[0])
	minute, _ := strconv.Atoi(timeParts[1])
	second, _ := strconv.Atoi(timeParts[2])
	// Parse date components
	dateParts := strings.Split(dateStr, "/")
	if len(dateParts) != 3 {
		return time.Time{}, fmt.Errorf("invalid date format: %s", dateStr)
	}
	day, _ := strconv.Atoi(dateParts[0])
	month, _ := strconv.Atoi(dateParts[1])
	year, _ := strconv.Atoi(dateParts[2])
	// Parse timezone offset
	timezoneStr = strings.TrimPrefix(timezoneStr, "UTC")
	offsetHours, err := strconv.Atoi(timezoneStr)
	if err != nil {
		return time.Time{}, fmt.Errorf("invalid timezone offset: %s", timezoneStr)
	}
	// Create fixed timezone location
	offsetSeconds := offsetHours * 3600
	loc := time.FixedZone(fmt.Sprintf("UTC%+d", offsetHours), offsetSeconds)
	// Construct timestamp
	timestamp := time.Date(year, time.Month(month), day, hour, minute, second, 0, loc)
	return timestamp, nil
}
// parseGainLevel converts string gain level to GainLevel enum
func parseGainLevel(gainStr string) (db.GainLevel, error) {
	gainStr = strings.ToLower(strings.TrimSpace(gainStr))
	switch gainStr {
	case "low":
		return db.GainLow, nil
	case "low-medium":
		return db.GainLowMedium, nil
	case "medium":
		return db.GainMedium, nil
	case "medium-high":
		return db.GainMediumHigh, nil
	case "high":
		return db.GainHigh, nil
	default:
		return "", fmt.Errorf("unknown gain level: %s", gainStr)
	}
}

package utils
import (
	"time"
	"github.com/sixdouglas/suncalc"
)
// AstronomicalData contains calculated astronomical data for a recording
type AstronomicalData struct {
	SolarNight bool    // True if recording midpoint is between sunset and sunrise
	CivilNight bool    // True if recording midpoint is between dusk and dawn (6° below horizon)
	MoonPhase  float64 // 0.00=New Moon, 0.25=First Quarter, 0.50=Full Moon, 0.75=Last Quarter
}
// CalculateAstronomicalData calculates astronomical data for a recording.
// Uses the recording MIDPOINT time (not start time) for calculations.
//
// Parameters:
//   - timestampUTC: Recording start time in UTC
//   - durationSec: Recording duration in seconds
//   - lat, lon: Location coordinates in decimal degrees
//
// Returns:
//   - solarNight: true if recording midpoint is between sunset and sunrise
//   - civilNight: true if recording midpoint is between dusk and dawn
//   - moonPhase: 0.00-1.00 representing moon phase (0=New, 0.5=Full)
func CalculateAstronomicalData(
	timestampUTC time.Time,
	durationSec float64,
	lat, lon float64,
) AstronomicalData {
	// Calculate recording MIDPOINT (not start time)
	midpoint := timestampUTC.Add(time.Duration(durationSec/2) * time.Second)
	// Get solar times for midpoint date
	times := suncalc.GetTimes(midpoint, lat, lon)
	// Solar night: between sunset and sunrise
	// Note: Handle day/night transitions properly
	sunrise := times[suncalc.Sunrise].Value
	sunset := times[suncalc.Sunset].Value
	solarNight := isBetweenSunTimes(midpoint, sunset, sunrise)
	// Civil night: between dusk and dawn (6° below horizon)
	dawn := times[suncalc.Dawn].Value
	dusk := times[suncalc.Dusk].Value
	civilNight := isBetweenSunTimes(midpoint, dusk, dawn)
	// Moon phase: 0.00=New Moon, 0.25=First Quarter, 0.50=Full Moon, 0.75=Last Quarter
	moonIllum := suncalc.GetMoonIllumination(midpoint)
	moonPhase := moonIllum.Phase
	return AstronomicalData{
		SolarNight: solarNight,
		CivilNight: civilNight,
		MoonPhase:  moonPhase,
	}
}
// isBetweenSunTimes determines if a time is between sunset/dusk and sunrise/dawn
// Handles the case where the night period crosses midnight
func isBetweenSunTimes(t, evening, morning time.Time) bool {
	// If evening time is before morning time (normal case: both on same day)
	// Then we're NOT in night period (daytime)
	if evening.Before(morning) {
		return false
	}
	// Otherwise, night period crosses midnight
	// Night is: after evening OR before morning
	return t.After(evening) || t.Before(morning)
}
// CalculateMidpointTime calculates the midpoint time of a recording
func CalculateMidpointTime(startTime time.Time, durationSec float64) time.Time {
	return startTime.Add(time.Duration(durationSec/2) * time.Second)
}

package tools
import (
	"context"
	"fmt"
	"skraak_mcp/db"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// CreateCyclicRecordingPatternInput defines the input parameters for the create_cyclic_recording_pattern tool
type CreateCyclicRecordingPatternInput struct {
	RecordSeconds int `json:"record_seconds" jsonschema:"required,Number of seconds to record (must be positive)"`
	SleepSeconds  int `json:"sleep_seconds" jsonschema:"required,Number of seconds to sleep between recordings (must be positive)"`
}
// CreateCyclicRecordingPatternOutput defines the output structure
type CreateCyclicRecordingPatternOutput struct {
	Pattern db.CyclicRecordingPattern `json:"pattern" jsonschema:"The created recording pattern with generated ID and timestamps"`
	Message string                    `json:"message" jsonschema:"Success message"`
}
// CreateCyclicRecordingPattern implements the create_cyclic_recording_pattern tool handler
// Creates a new cyclic recording pattern with record/sleep cycle in seconds
func CreateCyclicRecordingPattern(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input CreateCyclicRecordingPatternInput,
) (*mcp.CallToolResult, CreateCyclicRecordingPatternOutput, error) {
	var output CreateCyclicRecordingPatternOutput
	// Validate inputs
	if input.RecordSeconds <= 0 {
		return nil, output, fmt.Errorf("record_seconds must be positive (got %d)", input.RecordSeconds)
	}
	if input.SleepSeconds <= 0 {
		return nil, output, fmt.Errorf("sleep_seconds must be positive (got %d)", input.SleepSeconds)
	}
	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return nil, output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin transaction
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return nil, output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()
	// Generate ID
	id, err := db.GenerateID()
	if err != nil {
		return nil, output, fmt.Errorf("failed to generate ID: %w", err)
	}
	// Insert pattern (explicitly set timestamps and active for schema compatibility)
	_, err = tx.ExecContext(ctx,
		"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, created_at, last_modified, active) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, input.RecordSeconds, input.SleepSeconds,
	)
	if err != nil {
		return nil, output, fmt.Errorf("failed to create pattern: %w", err)
	}
	// Fetch the created pattern (gets DB-generated timestamps and defaults)
	var pattern db.CyclicRecordingPattern
	err = tx.QueryRowContext(ctx,
		"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
		id,
	).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active)
	if err != nil {
		return nil, output, fmt.Errorf("failed to fetch created pattern: %w", err)
	}
	// Commit transaction
	if err = tx.Commit(); err != nil {
		return nil, output, fmt.Errorf("failed to commit transaction: %w", err)
	}
	output.Pattern = pattern
	output.Message = fmt.Sprintf("Successfully created cyclic recording pattern with ID %s (record %ds, sleep %ds)",
		pattern.ID, pattern.RecordS, pattern.SleepS)
	return &mcp.CallToolResult{}, output, nil
}

package tools
import (
	"context"
	"fmt"
	"strings"
	"time"
	"skraak_mcp/db"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// CreateLocationInput defines the input parameters for the create_location tool
type CreateLocationInput struct {
	DatasetID   string  `json:"dataset_id" jsonschema:"required,ID of the parent dataset (12-character nanoid)"`
	Name        string  `json:"name" jsonschema:"required,Location name (max 140 characters)"`
	Latitude    float64 `json:"latitude" jsonschema:"required,Latitude in decimal degrees (-90 to 90)"`
	Longitude   float64 `json:"longitude" jsonschema:"required,Longitude in decimal degrees (-180 to 180)"`
	TimezoneID  string  `json:"timezone_id" jsonschema:"required,IANA timezone ID (e.g. 'Pacific/Auckland')"`
	Description *string `json:"description,omitempty" jsonschema:"Optional location description (max 255 characters)"`
}
// CreateLocationOutput defines the output structure
type CreateLocationOutput struct {
	Location db.Location `json:"location" jsonschema:"The created location with generated ID and timestamps"`
	Message  string      `json:"message" jsonschema:"Success message"`
}
// CreateLocation implements the create_location tool handler
// Creates a new location within a dataset with GPS coordinates and timezone
func CreateLocation(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input CreateLocationInput,
) (*mcp.CallToolResult, CreateLocationOutput, error) {
	var output CreateLocationOutput
	// Validate name
	if strings.TrimSpace(input.Name) == "" {
		return nil, output, fmt.Errorf("name cannot be empty")
	}
	if len(input.Name) > 140 {
		return nil, output, fmt.Errorf("name must be 140 characters or less (got %d)", len(input.Name))
	}
	// Validate description length if provided
	if input.Description != nil && len(*input.Description) > 255 {
		return nil, output, fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))
	}
	// Validate coordinates
	if input.Latitude < -90 || input.Latitude > 90 {
		return nil, output, fmt.Errorf("latitude must be between -90 and 90 (got %f)", input.Latitude)
	}
	if input.Longitude < -180 || input.Longitude > 180 {
		return nil, output, fmt.Errorf("longitude must be between -180 and 180 (got %f)", input.Longitude)
	}
	// Validate timezone
	if _, err := time.LoadLocation(input.TimezoneID); err != nil {
		return nil, output, fmt.Errorf("invalid timezone_id '%s': %w", input.TimezoneID, err)
	}
	// Validate dataset_id not empty
	if strings.TrimSpace(input.DatasetID) == "" {
		return nil, output, fmt.Errorf("dataset_id cannot be empty")
	}
	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return nil, output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin transaction
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return nil, output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()
	// Verify dataset exists and is active
	var datasetExists bool
	var datasetActive bool
	var datasetName string
	err = tx.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), active, name FROM dataset WHERE id = ?",
		input.DatasetID, input.DatasetID,
	).Scan(&datasetExists, &datasetActive, &datasetName)
	if err != nil {
		return nil, output, fmt.Errorf("failed to verify dataset: %w", err)
	}
	if !datasetExists {
		return nil, output, fmt.Errorf("dataset with ID '%s' does not exist", input.DatasetID)
	}
	if !datasetActive {
		return nil, output, fmt.Errorf("dataset '%s' (ID: %s) is not active", datasetName, input.DatasetID)
	}
	// Generate ID
	id, err := db.GenerateID()
	if err != nil {
		return nil, output, fmt.Errorf("failed to generate ID: %w", err)
	}
	// Insert location (explicitly set timestamps and active for schema compatibility)
	_, err = tx.ExecContext(ctx,
		"INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, input.DatasetID, input.Name, input.Latitude, input.Longitude, input.TimezoneID, input.Description,
	)
	if err != nil {
		return nil, output, fmt.Errorf("failed to create location: %w", err)
	}
	// Fetch the created location (gets DB-generated timestamps and defaults)
	var location db.Location
	err = tx.QueryRowContext(ctx,
		"SELECT id, dataset_id, name, latitude, longitude, description, created_at, last_modified, active, timezone_id FROM location WHERE id = ?",
		id,
	).Scan(&location.ID, &location.DatasetID, &location.Name, &location.Latitude, &location.Longitude,
		&location.Description, &location.CreatedAt, &location.LastModified, &location.Active, &location.TimezoneID)
	if err != nil {
		return nil, output, fmt.Errorf("failed to fetch created location: %w", err)
	}
	// Commit transaction
	if err = tx.Commit(); err != nil {
		return nil, output, fmt.Errorf("failed to commit transaction: %w", err)
	}
	output.Location = location
	output.Message = fmt.Sprintf("Successfully created location '%s' with ID %s in dataset '%s' (%.6f, %.6f, %s)",
		location.Name, location.ID, datasetName, location.Latitude, location.Longitude, location.TimezoneID)
	return &mcp.CallToolResult{}, output, nil
}

package tools
import (
	"context"
	"fmt"
	"strings"
	"skraak_mcp/db"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// CreateDatasetInput defines the input parameters for the create_dataset tool
type CreateDatasetInput struct {
	Name        string  `json:"name" jsonschema:"required,Dataset name (max 255 characters)"`
	Description *string `json:"description,omitempty" jsonschema:"Optional dataset description (max 255 characters)"`
	Type        *string `json:"type,omitempty" jsonschema:"Dataset type: 'organise'/'test'/'train' (defaults to 'organise')"`
}
// CreateDatasetOutput defines the output structure
type CreateDatasetOutput struct {
	Dataset db.Dataset `json:"dataset" jsonschema:"The created dataset with generated ID and timestamps"`
	Message string     `json:"message" jsonschema:"Success message"`
}
// CreateDataset implements the create_dataset tool handler
// Creates a new dataset with the specified name, description, and type
func CreateDataset(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input CreateDatasetInput,
) (*mcp.CallToolResult, CreateDatasetOutput, error) {
	var output CreateDatasetOutput
	// Validate name
	if strings.TrimSpace(input.Name) == "" {
		return nil, output, fmt.Errorf("name cannot be empty")
	}
	if len(input.Name) > 255 {
		return nil, output, fmt.Errorf("name must be 255 characters or less (got %d)", len(input.Name))
	}
	// Validate description length if provided
	if input.Description != nil && len(*input.Description) > 255 {
		return nil, output, fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))
	}
	// Validate and set type
	datasetType := db.DatasetTypeOrganise // Default
	if input.Type != nil {
		typeStr := strings.ToLower(strings.TrimSpace(*input.Type))
		switch typeStr {
		case "organise":
			datasetType = db.DatasetTypeOrganise
		case "test":
			datasetType = db.DatasetTypeTest
		case "train":
			datasetType = db.DatasetTypeTrain
		default:
			return nil, output, fmt.Errorf("invalid type '%s': must be 'organise', 'test', or 'train'", *input.Type)
		}
	}
	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return nil, output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin transaction
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return nil, output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()
	// Generate ID
	id, err := db.GenerateID()
	if err != nil {
		return nil, output, fmt.Errorf("failed to generate ID: %w", err)
	}
	// Insert dataset (explicitly set timestamps and active for schema compatibility)
	_, err = tx.ExecContext(ctx,
		"INSERT INTO dataset (id, name, description, type, created_at, last_modified, active) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, input.Name, input.Description, string(datasetType),
	)
	if err != nil {
		return nil, output, fmt.Errorf("failed to create dataset: %w", err)
	}
	// Fetch the created dataset (gets DB-generated timestamps and defaults)
	var dataset db.Dataset
	err = tx.QueryRowContext(ctx,
		"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
		id,
	).Scan(&dataset.ID, &dataset.Name, &dataset.Description, &dataset.CreatedAt, &dataset.LastModified, &dataset.Active, &dataset.Type)
	if err != nil {
		return nil, output, fmt.Errorf("failed to fetch created dataset: %w", err)
	}
	// Commit transaction
	if err = tx.Commit(); err != nil {
		return nil, output, fmt.Errorf("failed to commit transaction: %w", err)
	}
	output.Dataset = dataset
	output.Message = fmt.Sprintf("Successfully created dataset '%s' with ID %s (type: %s)",
		dataset.Name, dataset.ID, dataset.Type)
	return &mcp.CallToolResult{}, output, nil
}

package tools
import (
	"context"
	"fmt"
	"strings"
	"skraak_mcp/db"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// CreateClusterInput defines the input parameters for the create_cluster tool
type CreateClusterInput struct {
	DatasetID                string  `json:"dataset_id" jsonschema:"required,ID of the parent dataset (12-character nanoid)"`
	LocationID               string  `json:"location_id" jsonschema:"required,ID of the parent location (12-character nanoid)"`
	Name                     string  `json:"name" jsonschema:"required,Cluster name (max 140 characters)"`
	SampleRate               int     `json:"sample_rate" jsonschema:"required,Sample rate in Hz (must be positive)"`
	CyclicRecordingPatternID *string `json:"cyclic_recording_pattern_id,omitempty" jsonschema:"Optional ID of cyclic recording pattern (12-character nanoid)"`
	Description              *string `json:"description,omitempty" jsonschema:"Optional cluster description (max 255 characters)"`
}
// CreateClusterOutput defines the output structure
type CreateClusterOutput struct {
	Cluster db.Cluster `json:"cluster" jsonschema:"The created cluster with generated ID and timestamps"`
	Message string     `json:"message" jsonschema:"Success message"`
}
// CreateCluster implements the create_cluster tool handler
// Creates a new cluster within a location. Location must belong to the specified dataset.
func CreateCluster(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input CreateClusterInput,
) (*mcp.CallToolResult, CreateClusterOutput, error) {
	var output CreateClusterOutput
	// Validate name
	if strings.TrimSpace(input.Name) == "" {
		return nil, output, fmt.Errorf("name cannot be empty")
	}
	if len(input.Name) > 140 {
		return nil, output, fmt.Errorf("name must be 140 characters or less (got %d)", len(input.Name))
	}
	// Validate description length if provided
	if input.Description != nil && len(*input.Description) > 255 {
		return nil, output, fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))
	}
	// Validate sample rate
	if input.SampleRate <= 0 {
		return nil, output, fmt.Errorf("sample_rate must be positive (got %d)", input.SampleRate)
	}
	// Validate IDs not empty
	if strings.TrimSpace(input.DatasetID) == "" {
		return nil, output, fmt.Errorf("dataset_id cannot be empty")
	}
	if strings.TrimSpace(input.LocationID) == "" {
		return nil, output, fmt.Errorf("location_id cannot be empty")
	}
	// Open writable database connection
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return nil, output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	// Begin transaction
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return nil, output, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()
	// Verify dataset exists and is active
	var datasetExists bool
	var datasetActive bool
	var datasetName string
	err = tx.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), active, name FROM dataset WHERE id = ?",
		input.DatasetID, input.DatasetID,
	).Scan(&datasetExists, &datasetActive, &datasetName)
	if err != nil {
		return nil, output, fmt.Errorf("failed to verify dataset: %w", err)
	}
	if !datasetExists {
		return nil, output, fmt.Errorf("dataset with ID '%s' does not exist", input.DatasetID)
	}
	if !datasetActive {
		return nil, output, fmt.Errorf("dataset '%s' (ID: %s) is not active", datasetName, input.DatasetID)
	}
	// Verify location exists, is active, and belongs to the specified dataset (BUSINESS RULE)
	var locationExists bool
	var locationActive bool
	var locationName string
	var locationDatasetID string
	err = tx.QueryRowContext(ctx,
		"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), active, name, dataset_id FROM location WHERE id = ?",
		input.LocationID, input.LocationID,
	).Scan(&locationExists, &locationActive, &locationName, &locationDatasetID)
	if err != nil {
		return nil, output, fmt.Errorf("failed to verify location: %w", err)
	}
	if !locationExists {
		return nil, output, fmt.Errorf("location with ID '%s' does not exist", input.LocationID)
	}
	if !locationActive {
		return nil, output, fmt.Errorf("location '%s' (ID: %s) is not active", locationName, input.LocationID)
	}
	// CRITICAL BUSINESS RULE: Location must belong to the specified dataset
	if locationDatasetID != input.DatasetID {
		return nil, output, fmt.Errorf("location '%s' (ID: %s) does not belong to dataset '%s' (ID: %s) - it belongs to dataset ID '%s'",
			locationName, input.LocationID, datasetName, input.DatasetID, locationDatasetID)
	}
	// Verify cyclic recording pattern if provided
	if input.CyclicRecordingPatternID != nil && strings.TrimSpace(*input.CyclicRecordingPatternID) != "" {
		var patternExists bool
		var patternActive bool
		err = tx.QueryRowContext(ctx,
			"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?), active FROM cyclic_recording_pattern WHERE id = ?",
			*input.CyclicRecordingPatternID, *input.CyclicRecordingPatternID,
		).Scan(&patternExists, &patternActive)
		if err != nil {
			return nil, output, fmt.Errorf("failed to verify cyclic recording pattern: %w", err)
		}
		if !patternExists {
			return nil, output, fmt.Errorf("cyclic recording pattern with ID '%s' does not exist", *input.CyclicRecordingPatternID)
		}
		if !patternActive {
			return nil, output, fmt.Errorf("cyclic recording pattern with ID '%s' is not active", *input.CyclicRecordingPatternID)
		}
	}
	// Generate ID
	id, err := db.GenerateID()
	if err != nil {
		return nil, output, fmt.Errorf("failed to generate ID: %w", err)
	}
	// Insert cluster (explicitly set timestamps and active for schema compatibility)
	_, err = tx.ExecContext(ctx,
		"INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, cyclic_recording_pattern_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
		id, input.DatasetID, input.LocationID, input.Name, input.SampleRate, input.CyclicRecordingPatternID, input.Description,
	)
	if err != nil {
		return nil, output, fmt.Errorf("failed to create cluster: %w", err)
	}
	// Fetch the created cluster (gets DB-generated timestamps and defaults)
	var cluster db.Cluster
	err = tx.QueryRowContext(ctx,
		"SELECT id, dataset_id, location_id, name, description, created_at, last_modified, active, cyclic_recording_pattern_id, sample_rate FROM cluster WHERE id = ?",
		id,
	).Scan(&cluster.ID, &cluster.DatasetID, &cluster.LocationID, &cluster.Name, &cluster.Description,
		&cluster.CreatedAt, &cluster.LastModified, &cluster.Active, &cluster.CyclicRecordingPatternID, &cluster.SampleRate)
	if err != nil {
		return nil, output, fmt.Errorf("failed to fetch created cluster: %w", err)
	}
	// Commit transaction
	if err = tx.Commit(); err != nil {
		return nil, output, fmt.Errorf("failed to commit transaction: %w", err)
	}
	output.Cluster = cluster
	output.Message = fmt.Sprintf("Successfully created cluster '%s' with ID %s in location '%s' at dataset '%s' (sample rate: %d Hz)",
		cluster.Name, cluster.ID, locationName, datasetName, cluster.SampleRate)
	return &mcp.CallToolResult{}, output, nil
}

package tools
import (
	"context"
	"time"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// GetCurrentTimeInput defines the input parameters for the get_current_time tool
type GetCurrentTimeInput struct {
	// No input parameters needed for basic time query
}
// GetCurrentTimeOutput defines the output structure for the get_current_time tool
type GetCurrentTimeOutput struct {
	Time     string `json:"time" jsonschema:"Current system time in RFC3339 format"`
	Timezone string `json:"timezone" jsonschema:"System timezone"`
	Unix     int64  `json:"unix" jsonschema:"Unix timestamp in seconds"`
}
// GetCurrentTime implements the get_current_time tool handler
// It returns the current system time with timezone information
func GetCurrentTime(ctx context.Context, req *mcp.CallToolRequest, input GetCurrentTimeInput) (
	*mcp.CallToolResult,
	GetCurrentTimeOutput,
	error,
) {
	// Get current time
	now := time.Now()
	// Create output structure
	output := GetCurrentTimeOutput{
		Time:     now.Format(time.RFC3339),
		Timezone: now.Location().String(),
		Unix:     now.Unix(),
	}
	// Return successful result with output
	return &mcp.CallToolResult{}, output, nil
}

package tools
import (
	"context"
	"database/sql"
	"encoding/base64"
	"fmt"
	"regexp"
	"strings"
	"time"
	"skraak_mcp/db"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// Package-level variable to store database path
var dbPath string
// SetDBPath sets the database path for the tools package
// Called from main.go during initialization
func SetDBPath(path string) {
	dbPath = path
}
// ExecuteSQLInput defines the input parameters for the execute_sql tool
type ExecuteSQLInput struct {
	Query      string        `json:"query" jsonschema:"required,SQL SELECT query to execute"`
	Parameters []interface{} `json:"parameters,omitempty" jsonschema:"Optional parameters for parameterized queries (use ? placeholders)"`
	Limit      *int          `json:"limit,omitempty" jsonschema:"Maximum rows to return (default 1000 max 10000)"`
}
// ColumnInfo contains metadata about a result column
type ColumnInfo struct {
	Name         string `json:"name" jsonschema:"Column name"`
	DatabaseType string `json:"database_type" jsonschema:"Database type of the column"`
}
// ExecuteSQLOutput defines the output structure for the execute_sql tool
type ExecuteSQLOutput struct {
	Rows     []map[string]interface{} `json:"rows" jsonschema:"Query result rows"`
	RowCount int                      `json:"row_count" jsonschema:"Number of rows returned"`
	Columns  []ColumnInfo             `json:"columns" jsonschema:"Column metadata"`
	Limited  bool                     `json:"limited" jsonschema:"Whether results were truncated due to row limit"`
	Query    string                   `json:"query_executed" jsonschema:"The actual query executed (with LIMIT applied)"`
}
// Validation patterns
var (
	// Must start with SELECT or WITH (case-insensitive, allows leading whitespace)
	selectPattern = regexp.MustCompile(`(?i)^\s*(SELECT|WITH)\s+`)
	// Check for forbidden keywords that might indicate write operations
	forbiddenPattern = regexp.MustCompile(`(?i)\b(INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|TRUNCATE|GRANT|REVOKE)\b`)
	// Check for existing LIMIT clause (case-insensitive)
	limitPattern = regexp.MustCompile(`(?i)\bLIMIT\s+\d+`)
)
const (
	defaultLimit = 1000
	maxLimit     = 10000
)
// ExecuteSQL implements the execute_sql tool handler
// Executes arbitrary SQL SELECT queries with safety validation
func ExecuteSQL(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input ExecuteSQLInput,
) (*mcp.CallToolResult, ExecuteSQLOutput, error) {
	// Validate query is not empty
	if strings.TrimSpace(input.Query) == "" {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("query cannot be empty")
	}
	// Validate query starts with SELECT or WITH
	if !selectPattern.MatchString(input.Query) {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("only SELECT and WITH queries are allowed")
	}
	// Check for forbidden keywords (defense in depth - database is already read-only)
	if forbiddenPattern.MatchString(input.Query) {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("query contains forbidden keywords (INSERT/UPDATE/DELETE/DROP/CREATE/ALTER)")
	}
	// Determine row limit
	limit := defaultLimit
	if input.Limit != nil {
		if *input.Limit < 1 || *input.Limit > maxLimit {
			return nil, ExecuteSQLOutput{}, fmt.Errorf("limit must be between 1 and %d", maxLimit)
		}
		limit = *input.Limit
	}
	// Add LIMIT clause if not present
	query := input.Query
	if !limitPattern.MatchString(query) {
		query = fmt.Sprintf("%s LIMIT %d", strings.TrimSpace(query), limit)
	}
	// Get database connection (read-only for security)
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close() // Always close when done
	// Execute query with parameters
	var rows *sql.Rows
	if len(input.Parameters) > 0 {
		rows, err = database.QueryContext(ctx, query, input.Parameters...)
	} else {
		rows, err = database.QueryContext(ctx, query)
	}
	if err != nil {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("query execution failed: %w", err)
	}
	defer rows.Close()
	// Get column metadata
	columns, err := rows.Columns()
	if err != nil {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("failed to get columns: %w", err)
	}
	columnTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("failed to get column types: %w", err)
	}
	// Build column info
	columnInfo := make([]ColumnInfo, len(columns))
	for i, col := range columns {
		columnInfo[i] = ColumnInfo{
			Name:         col,
			DatabaseType: columnTypes[i].DatabaseTypeName(),
		}
	}
	// Process rows
	var results []map[string]interface{}
	rowCount := 0
	limited := false
	for rows.Next() {
		// Check if we've hit the limit
		if rowCount >= limit {
			limited = true
			break
		}
		// Create slice to hold column values
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}
		// Scan row
		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, ExecuteSQLOutput{}, fmt.Errorf("row scan failed: %w", err)
		}
		// Convert to map with type conversion
		rowMap := make(map[string]interface{})
		for i, col := range columns {
			rowMap[col] = convertValue(values[i])
		}
		results = append(results, rowMap)
		rowCount++
	}
	// Check for errors during iteration
	if err = rows.Err(); err != nil {
		return nil, ExecuteSQLOutput{}, fmt.Errorf("row iteration failed: %w", err)
	}
	// Handle empty results (return empty array, not error)
	if results == nil {
		results = []map[string]interface{}{}
	}
	// Create output structure
	output := ExecuteSQLOutput{
		Rows:     results,
		RowCount: rowCount,
		Columns:  columnInfo,
		Limited:  limited,
		Query:    query,
	}
	return &mcp.CallToolResult{}, output, nil
}
// convertValue converts database values to JSON-friendly types
func convertValue(val interface{}) interface{} {
	if val == nil {
		return nil
	}
	switch v := val.(type) {
	case time.Time:
		// Format timestamps as RFC3339 strings (consistent with existing code)
		return v.Format(time.RFC3339)
	case []byte:
		// Convert binary data to base64
		return base64.StdEncoding.EncodeToString(v)
	case int64, float64, string, bool:
		// Pass through primitive types
		return v
	default:
		// For unknown types, convert to string
		return fmt.Sprintf("%v", v)
	}
}

package tools
import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"time"
	gonanoid "github.com/matoous/go-nanoid/v2"
	"github.com/modelcontextprotocol/go-sdk/mcp"
	"skraak_mcp/db"
	"skraak_mcp/utils"
)
// ImportAudioFilesInput defines the input parameters for the import_audio_files tool
type ImportAudioFilesInput struct {
	FolderPath string `json:"folder_path" jsonschema:"required,Absolute path to folder containing WAV files"`
	DatasetID  string `json:"dataset_id" jsonschema:"required,Dataset ID (12 characters)"`
	LocationID string `json:"location_id" jsonschema:"required,Location ID (12 characters)"`
	ClusterID  string `json:"cluster_id" jsonschema:"required,Cluster ID (12 characters)"`
	Recursive  *bool  `json:"recursive,omitempty" jsonschema:"Scan subfolders recursively (default: true)"`
}
// ImportAudioFilesOutput defines the output structure for the import_audio_files tool
type ImportAudioFilesOutput struct {
	Summary ImportSummary       `json:"summary" jsonschema:"Import summary with counts and statistics"`
	FileIDs []string            `json:"file_ids" jsonschema:"List of successfully imported file IDs"`
	Errors  []FileImportError   `json:"errors,omitempty" jsonschema:"Errors encountered during import (if any)"`
}
// ImportSummary provides summary statistics for the import operation
type ImportSummary struct {
	TotalFiles     int     `json:"total_files"`
	ImportedFiles  int     `json:"imported_files"`
	SkippedFiles   int     `json:"skipped_files"`  // Duplicates
	FailedFiles    int     `json:"failed_files"`
	AudioMothFiles int     `json:"audiomoth_files"`
	TotalDuration  float64 `json:"total_duration_seconds"`
	ProcessingTime string  `json:"processing_time"`
}
// FileImportError records errors encountered during file processing
type FileImportError struct {
	FileName string `json:"file_name"`
	Error    string `json:"error"`
	Stage    string `json:"stage"` // "scan", "hash", "parse", "validate", "insert"
}
// fileData holds all data for a single file to be imported
type fileData struct {
	FileName       string
	FilePath       string
	Hash           string
	Duration       float64
	SampleRate     int
	TimestampLocal time.Time
	IsAudioMoth    bool
	MothData       *utils.AudioMothData
	AstroData      utils.AstronomicalData
}
// ImportAudioFiles implements the import_audio_files MCP tool
func ImportAudioFiles(
	ctx context.Context,
	req *mcp.CallToolRequest,
	input ImportAudioFilesInput,
) (*mcp.CallToolResult, ImportAudioFilesOutput, error) {
	startTime := time.Now()
	var output ImportAudioFilesOutput
	// Default recursive to true
	recursive := true
	if input.Recursive != nil {
		recursive = *input.Recursive
	}
	// Phase 1: Validate input
	if err := validateImportInput(input, dbPath); err != nil {
		return nil, output, fmt.Errorf("validation failed: %w", err)
	}
	// Phase 2: Scan folder for WAV files
	wavFiles, err := scanWAVFiles(input.FolderPath, recursive)
	if err != nil {
		return nil, output, fmt.Errorf("failed to scan folder: %w", err)
	}
	if len(wavFiles) == 0 {
		output = ImportAudioFilesOutput{
			Summary: ImportSummary{
				TotalFiles:     0,
				ProcessingTime: time.Since(startTime).String(),
			},
			FileIDs: []string{},
			Errors:  []FileImportError{},
		}
		return &mcp.CallToolResult{}, output, nil
	}
	// Phase 3: Get location data for astronomical calculations
	locationData, err := getLocationData(dbPath, input.LocationID)
	if err != nil {
		return nil, output, fmt.Errorf("failed to get location data: %w", err)
	}
	// Phase 4: Process all files (extract metadata, calculate hashes, etc.)
	filesData, errors := processFiles(wavFiles, locationData)
	// Phase 5: Insert into database
	importedFiles, skippedFiles, insertErrors := insertFilesIntoDB(
		dbPath,
		filesData,
		input.DatasetID,
		input.ClusterID,
		input.LocationID,
	)
	// Combine all errors
	allErrors := append(errors, insertErrors...)
	// Calculate summary
	audiomothCount := 0
	totalDuration := 0.0
	for _, fd := range filesData {
		if fd.IsAudioMoth {
			audiomothCount++
		}
		totalDuration += fd.Duration
	}
	summary := ImportSummary{
		TotalFiles:     len(wavFiles),
		ImportedFiles:  importedFiles,
		SkippedFiles:   skippedFiles,
		FailedFiles:    len(allErrors),
		AudioMothFiles: audiomothCount,
		TotalDuration:  totalDuration,
		ProcessingTime: time.Since(startTime).String(),
	}
	// Collect file IDs
	fileIDs := make([]string, 0, len(filesData))
	// Note: File IDs are generated during insert, not tracked here
	// This would require refactoring insertFilesIntoDB to return IDs
	output = ImportAudioFilesOutput{
		Summary: summary,
		FileIDs: fileIDs,
		Errors:  allErrors,
	}
	return &mcp.CallToolResult{}, output, nil
}
// validateImportInput validates all input parameters and database relationships
func validateImportInput(input ImportAudioFilesInput, dbPath string) error {
	// Verify folder exists
	info, err := os.Stat(input.FolderPath)
	if err != nil {
		return fmt.Errorf("folder not accessible: %w", err)
	}
	if !info.IsDir() {
		return fmt.Errorf("path is not a directory: %s", input.FolderPath)
	}
	// Open database for validation queries
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return fmt.Errorf("failed to open database: %w", err)
	}
	defer database.Close()
	// Verify dataset exists
	var datasetExists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)", input.DatasetID).Scan(&datasetExists)
	if err != nil {
		return fmt.Errorf("failed to query dataset: %w", err)
	}
	if !datasetExists {
		return fmt.Errorf("dataset not found or inactive: %s", input.DatasetID)
	}
	// Verify location exists and belongs to dataset
	var locationDatasetID string
	err = database.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", input.LocationID).Scan(&locationDatasetID)
	if err == sql.ErrNoRows {
		return fmt.Errorf("location not found or inactive: %s", input.LocationID)
	}
	if err != nil {
		return fmt.Errorf("failed to query location: %w", err)
	}
	if locationDatasetID != input.DatasetID {
		return fmt.Errorf("location %s does not belong to dataset %s", input.LocationID, input.DatasetID)
	}
	// Verify cluster exists and belongs to location
	var clusterLocationID string
	err = database.QueryRow("SELECT location_id FROM cluster WHERE id = ? AND active = true", input.ClusterID).Scan(&clusterLocationID)
	if err == sql.ErrNoRows {
		return fmt.Errorf("cluster not found or inactive: %s", input.ClusterID)
	}
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}
	if clusterLocationID != input.LocationID {
		return fmt.Errorf("cluster %s does not belong to location %s", input.ClusterID, input.LocationID)
	}
	return nil
}
// locationData holds location information needed for processing
type locationData struct {
	Latitude   float64
	Longitude  float64
	TimezoneID string
}
// getLocationData retrieves location coordinates and timezone
func getLocationData(dbPath, locationID string) (*locationData, error) {
	database, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return nil, err
	}
	defer database.Close()
	var loc locationData
	err = database.QueryRow(
		"SELECT latitude, longitude, timezone_id FROM location WHERE id = ?",
		locationID,
	).Scan(&loc.Latitude, &loc.Longitude, &loc.TimezoneID)
	if err != nil {
		return nil, fmt.Errorf("failed to query location data: %w", err)
	}
	return &loc, nil
}
// scanWAVFiles recursively scans a folder for WAV files, excluding Clips_* subfolders
func scanWAVFiles(rootPath string, recursive bool) ([]string, error) {
	var wavFiles []string
	if recursive {
		err := filepath.Walk(rootPath, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}
			// Skip "Clips_*" directories
			if info.IsDir() && strings.HasPrefix(info.Name(), "Clips_") {
				return filepath.SkipDir
			}
			// Check for WAV files
			if !info.IsDir() {
				ext := strings.ToLower(filepath.Ext(path))
				if ext == ".wav" && info.Size() > 0 {
					wavFiles = append(wavFiles, path)
				}
			}
			return nil
		})
		if err != nil {
			return nil, err
		}
	} else {
		// Non-recursive: scan only top level
		entries, err := os.ReadDir(rootPath)
		if err != nil {
			return nil, err
		}
		for _, entry := range entries {
			if !entry.IsDir() {
				name := entry.Name()
				ext := strings.ToLower(filepath.Ext(name))
				if ext == ".wav" {
					path := filepath.Join(rootPath, name)
					if info, err := os.Stat(path); err == nil && info.Size() > 0 {
						wavFiles = append(wavFiles, path)
					}
				}
			}
		}
	}
	// Sort for consistent processing order
	sort.Strings(wavFiles)
	return wavFiles, nil
}
// processFiles extracts metadata and calculates hashes for all files
func processFiles(wavFiles []string, location *locationData) ([]*fileData, []FileImportError) {
	var filesData []*fileData
	var errors []FileImportError
	// Step 1: Extract WAV metadata from all files
	type wavInfo struct {
		path     string
		metadata *utils.WAVMetadata
		err      error
	}
	wavInfos := make([]wavInfo, len(wavFiles))
	for i, path := range wavFiles {
		metadata, err := utils.ParseWAVHeader(path)
		wavInfos[i] = wavInfo{path: path, metadata: metadata, err: err}
	}
	// Step 2: Collect filenames for batch timestamp parsing
	var filenamesForParsing []string
	var filenameIndices []int
	for i, info := range wavInfos {
		if info.err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    info.err.Error(),
				Stage:    "parse",
			})
			continue
		}
		// Check if file has timestamp filename format
		if utils.HasTimestampFilename(info.path) {
			filenamesForParsing = append(filenamesForParsing, filepath.Base(info.path))
			filenameIndices = append(filenameIndices, i)
		}
	}
	// Step 3: Parse filename timestamps in batch (if any)
	filenameTimestampMap := make(map[int]time.Time) // Maps file index to timestamp
	if len(filenamesForParsing) > 0 {
		filenameTimestamps, err := utils.ParseFilenameTimestamps(filenamesForParsing)
		if err != nil {
			// If batch parsing fails, record error for all files
			for _, idx := range filenameIndices {
				errors = append(errors, FileImportError{
					FileName: filepath.Base(wavInfos[idx].path),
					Error:    fmt.Sprintf("filename timestamp parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		} else {
			// Apply timezone offset
			adjustedTimestamps, err := utils.ApplyTimezoneOffset(filenameTimestamps, location.TimezoneID)
			if err != nil {
				for _, idx := range filenameIndices {
					errors = append(errors, FileImportError{
						FileName: filepath.Base(wavInfos[idx].path),
						Error:    fmt.Sprintf("timezone offset failed: %v", err),
						Stage:    "parse",
					})
				}
			} else {
				// Build map from file index to timestamp
				for j, idx := range filenameIndices {
					filenameTimestampMap[idx] = adjustedTimestamps[j]
				}
			}
		}
	}
	// Step 4: Process each file
	for i, info := range wavInfos {
		if info.err != nil {
			continue // Already recorded error
		}
		// Calculate hash
		hash, err := utils.ComputeXXH64(info.path)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    fmt.Sprintf("hash calculation failed: %v", err),
				Stage:    "hash",
			})
			continue
		}
		// Determine timestamp
		var timestampLocal time.Time
		var isAudioMoth bool
		var mothData *utils.AudioMothData
		// Try AudioMoth comment first
		if utils.IsAudioMoth(info.metadata.Comment, info.metadata.Artist) {
			isAudioMoth = true
			mothData, err = utils.ParseAudioMothComment(info.metadata.Comment)
			if err == nil {
				timestampLocal = mothData.Timestamp
			} else {
				// AudioMoth detected but parsing failed - try filename
				errors = append(errors, FileImportError{
					FileName: filepath.Base(info.path),
					Error:    fmt.Sprintf("AudioMoth comment parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		}
		// If no AudioMoth timestamp, use filename timestamp
		if timestampLocal.IsZero() {
			if ts, ok := filenameTimestampMap[i]; ok {
				timestampLocal = ts
			}
		}
		// If still no timestamp, skip file
		if timestampLocal.IsZero() {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    "no timestamp available (not AudioMoth and filename not parseable)",
				Stage:    "parse",
			})
			continue
		}
		// Calculate astronomical data
		astroData := utils.CalculateAstronomicalData(
			timestampLocal.UTC(),
			info.metadata.Duration,
			location.Latitude,
			location.Longitude,
		)
		// Add to results
		filesData = append(filesData, &fileData{
			FileName:       filepath.Base(info.path),
			FilePath:       info.path,
			Hash:           hash,
			Duration:       info.metadata.Duration,
			SampleRate:     info.metadata.SampleRate,
			TimestampLocal: timestampLocal,
			IsAudioMoth:    isAudioMoth,
			MothData:       mothData,
			AstroData:      astroData,
		})
	}
	return filesData, errors
}
// insertFilesIntoDB inserts all file data into database in a single transaction
func insertFilesIntoDB(
	dbPath string,
	filesData []*fileData,
	datasetID, clusterID, locationID string,
) (imported, skipped int, errors []FileImportError) {
	// Open writable database
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to open database: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer database.Close()
	// Begin transaction
	ctx := context.Background()
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to begin transaction: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer tx.Rollback() // Rollback if not committed
	// Prepare statements
	fileStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file (
			id, file_name, path, xxh64_hash, location_id, timestamp_local,
			cluster_id, duration, sample_rate, maybe_solar_night, maybe_civil_night,
			moon_phase, created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'), true)
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare file statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer fileStmt.Close()
	datasetStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file_dataset (file_id, dataset_id, created_at, last_modified)
		VALUES (?, ?, datetime('now'), datetime('now'))
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare dataset statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer datasetStmt.Close()
	mothStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO moth_metadata (
			file_id, timestamp, recorder_id, gain, battery_v, temp_c,
			created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'), true)
	`)
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("failed to prepare moth statement: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	defer mothStmt.Close()
	// Insert each file
	for _, fd := range filesData {
		// Check for duplicate hash
		var exists bool
		err = tx.QueryRowContext(ctx,
			"SELECT EXISTS(SELECT 1 FROM file WHERE xxh64_hash = ?)",
			fd.Hash,
		).Scan(&exists)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("duplicate check failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		if exists {
			skipped++
			continue
		}
		// Generate file ID
		fileID, err := gonanoid.Generate("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz", 21)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("ID generation failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file record
		_, err = fileStmt.ExecContext(ctx,
			fileID, fd.FileName, fd.FilePath, fd.Hash, locationID,
			fd.TimestampLocal, clusterID, fd.Duration, fd.SampleRate,
			fd.AstroData.SolarNight, fd.AstroData.CivilNight, fd.AstroData.MoonPhase,
		)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// Insert file_dataset junction
		_, err = datasetStmt.ExecContext(ctx, fileID, datasetID)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file_dataset insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}
		// If AudioMoth, insert moth_metadata
		if fd.IsAudioMoth && fd.MothData != nil {
			_, err = mothStmt.ExecContext(ctx,
				fileID,
				fd.MothData.Timestamp,
				&fd.MothData.RecorderID,
				&fd.MothData.Gain,
				&fd.MothData.BatteryV,
				&fd.MothData.TempC,
			)
			if err != nil {
				errors = append(errors, FileImportError{
					FileName: fd.FileName,
					Error:    fmt.Sprintf("moth_metadata insert failed: %v", err),
					Stage:    "insert",
				})
				continue
			}
		}
		imported++
	}
	// Commit transaction
	err = tx.Commit()
	if err != nil {
		errors = append(errors, FileImportError{
			FileName: "",
			Error:    fmt.Sprintf("transaction commit failed: %v", err),
			Stage:    "insert",
		})
		return 0, 0, errors
	}
	return imported, skipped, errors
}

{"jsonrpc":"2.0","id":1,"result":{"capabilities":{"logging":{},"prompts":{"listChanged":true},"resources":{"listChanged":true},"tools":{"listChanged":true}},"protocolVersion":"2024-11-05","serverInfo":{"name":"skraak_mcp","version":"v1.0.0"}}}
{"jsonrpc":"2.0","method":"notifications/tools/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/resources/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/prompts/list_changed","params":{}}
{"jsonrpc":"2.0","id":2,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"ENUM\",\"name\":\"type\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000\",\"row_count\":8,\"rows\":[{\"id\":\"wAJk9wuZN15x\",\"name\":\"Bluemine - Kiwi\",\"type\":\"organise\"},{\"id\":\"QZ0tlUrX4Nyi\",\"name\":\"Friends of Cobb - Kiwi\",\"type\":\"organise\"},{\"id\":\"RxajkKXz-w48\",\"name\":\"Lisa Whittle\",\"type\":\"organise\"},{\"id\":\"vgIr9JSH_lFj\",\"name\":\"MOK call site 1\",\"type\":\"organise\"},{\"id\":\"la-JpAf2nLKG\",\"name\":\"Manu o Kahurangi - Kiwi\",\"type\":\"organise\"},{\"id\":\"Yx0oNUDmP5ch\",\"name\":\"Pomona - Kiwi\",\"type\":\"organise\"},{\"id\":\"jWS-sw5RvM-j\",\"name\":\"Pure Salt - Kiwi\",\"type\":\"organise\"},{\"id\":\"gljgxDbfasva\",\"name\":\"Twenty Four Seven\",\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"ENUM","name":"type"}],"limited":false,"query_executed":"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000","row_count":8,"rows":[{"id":"wAJk9wuZN15x","name":"Bluemine - Kiwi","type":"organise"},{"id":"QZ0tlUrX4Nyi","name":"Friends of Cobb - Kiwi","type":"organise"},{"id":"RxajkKXz-w48","name":"Lisa Whittle","type":"organise"},{"id":"vgIr9JSH_lFj","name":"MOK call site 1","type":"organise"},{"id":"la-JpAf2nLKG","name":"Manu o Kahurangi - Kiwi","type":"organise"},{"id":"Yx0oNUDmP5ch","name":"Pomona - Kiwi","type":"organise"},{"id":"jWS-sw5RvM-j","name":"Pure Salt - Kiwi","type":"organise"},{"id":"gljgxDbfasva","name":"Twenty Four Seven","type":"organise"}]}}}
{"jsonrpc":"2.0","id":3,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"}],\"limited\":false,\"query_executed\":\"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5\",\"row_count\":5,\"rows\":[{\"id\":\"EwyxfYPFMflt\",\"name\":\"A01\"},{\"id\":\"w5zig0ALH6a5\",\"name\":\"A05\"},{\"id\":\"GouXwoyjeFiq\",\"name\":\"A11\"},{\"id\":\"OS6xbBytkk_I\",\"name\":\"AC21\"},{\"id\":\"tcE-bZ0tcmFB\",\"name\":\"AC34\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"}],"limited":false,"query_executed":"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5","row_count":5,"rows":[{"id":"EwyxfYPFMflt","name":"A01"},{"id":"w5zig0ALH6a5","name":"A05"},{"id":"GouXwoyjeFiq","name":"A11"},{"id":"OS6xbBytkk_I","name":"AC21"},{"id":"tcE-bZ0tcmFB","name":"AC34"}]}}}
{"jsonrpc":"2.0","id":4,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"latitude\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"longitude\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000\",\"row_count\":1,\"rows\":[{\"id\":\"0t9JyiuGID4w\",\"latitude\":\"-40.826344\",\"longitude\":\"172.585079\",\"name\":\"call site 1 1.2 test\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"DECIMAL(10,7)","name":"latitude"},{"database_type":"DECIMAL(10,7)","name":"longitude"}],"limited":false,"query_executed":"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000","row_count":1,"rows":[{"id":"0t9JyiuGID4w","latitude":"-40.826344","longitude":"172.585079","name":"call site 1 1.2 test"}]}}}
{"jsonrpc":"2.0","id":5,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"dataset\"},{\"database_type\":\"BIGINT\",\"name\":\"location_count\"}],\"limited\":false,\"query_executed\":\"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20\",\"row_count\":8,\"rows\":[{\"dataset\":\"Bluemine - Kiwi\",\"location_count\":11},{\"dataset\":\"Friends of Cobb - Kiwi\",\"location_count\":0},{\"dataset\":\"Lisa Whittle\",\"location_count\":15},{\"dataset\":\"MOK call site 1\",\"location_count\":1},{\"dataset\":\"Manu o Kahurangi - Kiwi\",\"location_count\":23},{\"dataset\":\"Pomona - Kiwi\",\"location_count\":48},{\"dataset\":\"Pure Salt - Kiwi\",\"location_count\":6},{\"dataset\":\"Twenty Four Seven\",\"location_count\":35}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"dataset"},{"database_type":"BIGINT","name":"location_count"}],"limited":false,"query_executed":"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20","row_count":8,"rows":[{"dataset":"Bluemine - Kiwi","location_count":11},{"dataset":"Friends of Cobb - Kiwi","location_count":0},{"dataset":"Lisa Whittle","location_count":15},{"dataset":"MOK call site 1","location_count":1},{"dataset":"Manu o Kahurangi - Kiwi","location_count":23},{"dataset":"Pomona - Kiwi","location_count":48},{"dataset":"Pure Salt - Kiwi","location_count":6},{"dataset":"Twenty Four Seven","location_count":35}]}}}
{"jsonrpc":"2.0","id":6,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"ENUM\",\"name\":\"type\"},{\"database_type\":\"BIGINT\",\"name\":\"count\"}],\"limited\":false,\"query_executed\":\"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000\",\"row_count\":1,\"rows\":[{\"count\":8,\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"ENUM","name":"type"},{"database_type":"BIGINT","name":"count"}],"limited":false,"query_executed":"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000","row_count":1,"rows":[{"count":8,"type":"organise"}]}}}
{"jsonrpc":"2.0","id":7,"result":{"content":[{"type":"text","text":"only SELECT and WITH queries are allowed"}],"isError":true}}
{"jsonrpc":"2.0","id":8,"result":{"content":[{"type":"text","text":"query contains forbidden keywords (INSERT/UPDATE/DELETE/DROP/CREATE/ALTER)"}],"isError":true}}

#!/bin/bash
# Test script for write tools (create_dataset, create_location, create_cluster, create_cyclic_recording_pattern)
# Tests both valid and invalid inputs
# USES TEST DATABASE BY DEFAULT to preserve production data integrity
DB_PATH="${1:-../db/test.duckdb}"
SERVER_PATH="../skraak_mcp"
echo "=== Testing Write Tools for Skraak MCP Server ==="
echo "Database: $DB_PATH"
echo ""
# Initialize connection
echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'
echo ""
echo "=== Test 1: Create Cyclic Recording Pattern (Valid) ==="
echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"create_cyclic_recording_pattern","arguments":{"record_seconds":30,"sleep_seconds":90}}}'
echo ""
echo "=== Test 2: Create Cyclic Recording Pattern (Invalid - negative values) ==="
echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"create_cyclic_recording_pattern","arguments":{"record_seconds":-10,"sleep_seconds":90}}}'
echo ""
echo "=== Test 3: Create Dataset (Valid - organise type) ==="
echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"Test Dataset 2026","description":"Created by automated test script","type":"organise"}}}'
echo ""
echo "=== Test 4: Create Dataset (Valid - test type, no description) ==="
echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"Test Dataset ML","type":"test"}}}'
echo ""
echo "=== Test 5: Create Dataset (Invalid - empty name) ==="
echo '{"jsonrpc":"2.0","id":6,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"  ","type":"test"}}}'
echo ""
echo "=== Test 6: Create Dataset (Invalid - bad type) ==="
echo '{"jsonrpc":"2.0","id":7,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"Bad Type Dataset","type":"invalid_type"}}}'
echo ""
echo "=== Test 7: Query recently created datasets to get IDs ==="
echo '{"jsonrpc":"2.0","id":8,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, type FROM dataset WHERE name LIKE '\''Test Dataset%'\'' ORDER BY created_at DESC LIMIT 2"}}}'
echo ""
echo "=== Test 8: Create Location (Valid) ==="
echo "NOTE: Replace DATASET_ID_HERE with actual ID from Test 7 results"
echo '{"jsonrpc":"2.0","id":9,"method":"tools/call","params":{"name":"create_location","arguments":{"dataset_id":"DATASET_ID_HERE","name":"Test Location Auckland","latitude":-36.8485,"longitude":174.7633,"timezone_id":"Pacific/Auckland","description":"Test location in Auckland"}}}'
echo ""
echo "=== Test 9: Create Location (Invalid - bad coordinates) ==="
echo '{"jsonrpc":"2.0","id":10,"method":"tools/call","params":{"name":"create_location","arguments":{"dataset_id":"DATASET_ID_HERE","name":"Invalid Coords","latitude":999,"longitude":174.7633,"timezone_id":"Pacific/Auckland"}}}'
echo ""
echo "=== Test 10: Create Location (Invalid - bad timezone) ==="
echo '{"jsonrpc":"2.0","id":11,"method":"tools/call","params":{"name":"create_location","arguments":{"dataset_id":"DATASET_ID_HERE","name":"Bad Timezone","latitude":-36.8485,"longitude":174.7633,"timezone_id":"Invalid/Timezone"}}}'
echo ""
echo "=== Test 11: Create Location (Invalid - non-existent dataset) ==="
echo '{"jsonrpc":"2.0","id":12,"method":"tools/call","params":{"name":"create_location","arguments":{"dataset_id":"NONEXISTENT1","name":"Orphan Location","latitude":-36.8485,"longitude":174.7633,"timezone_id":"Pacific/Auckland"}}}'
echo ""
echo "=== Test 12: Query recently created locations to get IDs ==="
echo '{"jsonrpc":"2.0","id":13,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, dataset_id FROM location WHERE name LIKE '\''Test Location%'\'' ORDER BY created_at DESC LIMIT 1"}}}'
echo ""
echo "=== Test 13: Create Cluster (Valid) ==="
echo "NOTE: Replace DATASET_ID_HERE and LOCATION_ID_HERE with actual IDs from previous results"
echo '{"jsonrpc":"2.0","id":14,"method":"tools/call","params":{"name":"create_cluster","arguments":{"dataset_id":"DATASET_ID_HERE","location_id":"LOCATION_ID_HERE","name":"Test Cluster Alpha","sample_rate":44100,"description":"Test cluster with 44.1kHz sample rate"}}}'
echo ""
echo "=== Test 14: Create Cluster (Invalid - sample rate zero) ==="
echo '{"jsonrpc":"2.0","id":15,"method":"tools/call","params":{"name":"create_cluster","arguments":{"dataset_id":"DATASET_ID_HERE","location_id":"LOCATION_ID_HERE","name":"Bad Sample Rate","sample_rate":0}}}'
echo ""
echo "=== Test 15: Create Cluster (Invalid - location/dataset mismatch) ==="
echo "NOTE: Uses wrong dataset_id for the location"
echo '{"jsonrpc":"2.0","id":16,"method":"tools/call","params":{"name":"create_cluster","arguments":{"dataset_id":"WRONG_DATASET","location_id":"LOCATION_ID_HERE","name":"Mismatched Cluster","sample_rate":48000}}}'
echo ""
echo "=== Test 16: Query recently created clusters ==="
echo '{"jsonrpc":"2.0","id":17,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT c.id, c.name, c.sample_rate, l.name as location_name, d.name as dataset_name FROM cluster c JOIN location l ON c.location_id = l.id JOIN dataset d ON c.dataset_id = d.id WHERE c.name LIKE '\''Test Cluster%'\'' ORDER BY c.created_at DESC LIMIT 1"}}}'
echo ""
echo "=== End of Write Tools Tests ==="
echo ""
echo "MANUAL STEPS REQUIRED:"
echo "1. Run this script and capture output: ./test_write_tools.sh > test_write_output.txt 2>&1"
echo "2. Extract IDs from Test 7 results (dataset IDs)"
echo "3. Extract ID from Test 12 results (location ID)"
echo "4. Edit Tests 8-16 to replace DATASET_ID_HERE and LOCATION_ID_HERE with actual IDs"
echo "5. Run individual tests with correct IDs to verify write operations"
echo ""
echo "VERIFICATION COMMANDS:"
echo "  rg '\"result\"' test_write_output.txt | wc -l    # Count successful responses"
echo "  rg 'error' test_write_output.txt                # Check for errors"
echo "  rg 'Successfully created' test_write_output.txt # Check success messages"

#!/bin/bash
# Simple test for write tools - tests happy path only
# Uses test.duckdb to preserve production data
DB_PATH="${1:-../db/test.duckdb}"
SERVER_PATH="../skraak_mcp"
echo "=== Simple Write Tools Test (Happy Path) ===" >&2
echo "Database: $DB_PATH" >&2
echo "" >&2
{
  # Initialize
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'
  sleep 0.2
  # Test 1: Create pattern
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"create_cyclic_recording_pattern","arguments":{"record_seconds":30,"sleep_seconds":90}}}'
  sleep 0.2
  # Test 2: Create dataset
  echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"Test Dataset 2026-01-27","description":"Automated test dataset","type":"test"}}}'
  sleep 0.2
  # Test 3: Invalid dataset (empty name) - should fail
  echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"  ","type":"test"}}}'
  sleep 0.2
  # Test 4: Invalid pattern (negative value) - should fail
  echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"create_cyclic_recording_pattern","arguments":{"record_seconds":-10,"sleep_seconds":90}}}'
  sleep 0.2
} | "$SERVER_PATH" "$DB_PATH" 2>/dev/null

=== Simple Write Tools Test (Happy Path) ===
Database: ../db/test.duckdb
2026/01/27 12:22:48 Server error: server is closing: EOF

#!/bin/bash
# End-to-end test: Create complete hierarchy (pattern → dataset → location → cluster)
# Uses test.duckdb to preserve production data
DB_PATH="${1:-../db/test.duckdb}"
SERVER_PATH="../skraak_mcp"
echo "=== End-to-End Write Tools Test ===" >&2
echo "Database: $DB_PATH" >&2
echo "" >&2
{
  # Initialize
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'
  sleep 0.2
  # Step 1: Create recording pattern
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"create_cyclic_recording_pattern","arguments":{"record_seconds":120,"sleep_seconds":300}}}'
  sleep 0.2
  # Step 2: Create dataset
  echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"create_dataset","arguments":{"name":"E2E Test Dataset","description":"End-to-end test","type":"test"}}}'
  sleep 0.2
  # Step 3: Create location (using dataset ID from step 2)
  # NOTE: You need to extract the dataset ID from step 2 and use it here
  echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"create_location","arguments":{"dataset_id":"REPLACE_WITH_DATASET_ID","name":"Test Location Wellington","latitude":-41.2865,"longitude":174.7762,"timezone_id":"Pacific/Auckland","description":"Test location"}}}'
  sleep 0.2
  # Step 4: Create cluster (using dataset ID and location ID)
  # NOTE: You need to extract both IDs and use them here
  echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"create_cluster","arguments":{"dataset_id":"REPLACE_WITH_DATASET_ID","location_id":"REPLACE_WITH_LOCATION_ID","name":"Test Cluster Alpha","sample_rate":48000,"cyclic_recording_pattern_id":"REPLACE_WITH_PATTERN_ID","description":"Test cluster"}}}'
  sleep 0.2
} | "$SERVER_PATH" "$DB_PATH" 2>/dev/null

{"jsonrpc":"2.0","id":1,"result":{"capabilities":{"logging":{},"prompts":{"listChanged":true},"resources":{"listChanged":true},"tools":{"listChanged":true}},"protocolVersion":"2024-11-05","serverInfo":{"name":"skraak_mcp","version":"v1.0.0"}}}
{"jsonrpc":"2.0","method":"notifications/prompts/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/tools/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/resources/list_changed","params":{}}
{"jsonrpc":"2.0","id":2,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"ENUM\",\"name\":\"type\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000\",\"row_count\":8,\"rows\":[{\"id\":\"wAJk9wuZN15x\",\"name\":\"Bluemine - Kiwi\",\"type\":\"organise\"},{\"id\":\"QZ0tlUrX4Nyi\",\"name\":\"Friends of Cobb - Kiwi\",\"type\":\"organise\"},{\"id\":\"RxajkKXz-w48\",\"name\":\"Lisa Whittle\",\"type\":\"organise\"},{\"id\":\"vgIr9JSH_lFj\",\"name\":\"MOK call site 1\",\"type\":\"organise\"},{\"id\":\"la-JpAf2nLKG\",\"name\":\"Manu o Kahurangi - Kiwi\",\"type\":\"organise\"},{\"id\":\"Yx0oNUDmP5ch\",\"name\":\"Pomona - Kiwi\",\"type\":\"organise\"},{\"id\":\"jWS-sw5RvM-j\",\"name\":\"Pure Salt - Kiwi\",\"type\":\"organise\"},{\"id\":\"gljgxDbfasva\",\"name\":\"Twenty Four Seven\",\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"ENUM","name":"type"}],"limited":false,"query_executed":"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000","row_count":8,"rows":[{"id":"wAJk9wuZN15x","name":"Bluemine - Kiwi","type":"organise"},{"id":"QZ0tlUrX4Nyi","name":"Friends of Cobb - Kiwi","type":"organise"},{"id":"RxajkKXz-w48","name":"Lisa Whittle","type":"organise"},{"id":"vgIr9JSH_lFj","name":"MOK call site 1","type":"organise"},{"id":"la-JpAf2nLKG","name":"Manu o Kahurangi - Kiwi","type":"organise"},{"id":"Yx0oNUDmP5ch","name":"Pomona - Kiwi","type":"organise"},{"id":"jWS-sw5RvM-j","name":"Pure Salt - Kiwi","type":"organise"},{"id":"gljgxDbfasva","name":"Twenty Four Seven","type":"organise"}]}}}
{"jsonrpc":"2.0","id":3,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"}],\"limited\":false,\"query_executed\":\"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5\",\"row_count\":5,\"rows\":[{\"id\":\"EwyxfYPFMflt\",\"name\":\"A01\"},{\"id\":\"w5zig0ALH6a5\",\"name\":\"A05\"},{\"id\":\"GouXwoyjeFiq\",\"name\":\"A11\"},{\"id\":\"OS6xbBytkk_I\",\"name\":\"AC21\"},{\"id\":\"tcE-bZ0tcmFB\",\"name\":\"AC34\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"}],"limited":false,"query_executed":"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5","row_count":5,"rows":[{"id":"EwyxfYPFMflt","name":"A01"},{"id":"w5zig0ALH6a5","name":"A05"},{"id":"GouXwoyjeFiq","name":"A11"},{"id":"OS6xbBytkk_I","name":"AC21"},{"id":"tcE-bZ0tcmFB","name":"AC34"}]}}}
{"jsonrpc":"2.0","id":4,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"latitude\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"longitude\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000\",\"row_count\":1,\"rows\":[{\"id\":\"0t9JyiuGID4w\",\"latitude\":\"-40.826344\",\"longitude\":\"172.585079\",\"name\":\"call site 1 1.2 test\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"DECIMAL(10,7)","name":"latitude"},{"database_type":"DECIMAL(10,7)","name":"longitude"}],"limited":false,"query_executed":"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000","row_count":1,"rows":[{"id":"0t9JyiuGID4w","latitude":"-40.826344","longitude":"172.585079","name":"call site 1 1.2 test"}]}}}
{"jsonrpc":"2.0","id":5,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"dataset\"},{\"database_type\":\"BIGINT\",\"name\":\"location_count\"}],\"limited\":false,\"query_executed\":\"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20\",\"row_count\":8,\"rows\":[{\"dataset\":\"Bluemine - Kiwi\",\"location_count\":11},{\"dataset\":\"Friends of Cobb - Kiwi\",\"location_count\":0},{\"dataset\":\"Lisa Whittle\",\"location_count\":15},{\"dataset\":\"MOK call site 1\",\"location_count\":1},{\"dataset\":\"Manu o Kahurangi - Kiwi\",\"location_count\":23},{\"dataset\":\"Pomona - Kiwi\",\"location_count\":48},{\"dataset\":\"Pure Salt - Kiwi\",\"location_count\":6},{\"dataset\":\"Twenty Four Seven\",\"location_count\":35}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"dataset"},{"database_type":"BIGINT","name":"location_count"}],"limited":false,"query_executed":"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20","row_count":8,"rows":[{"dataset":"Bluemine - Kiwi","location_count":11},{"dataset":"Friends of Cobb - Kiwi","location_count":0},{"dataset":"Lisa Whittle","location_count":15},{"dataset":"MOK call site 1","location_count":1},{"dataset":"Manu o Kahurangi - Kiwi","location_count":23},{"dataset":"Pomona - Kiwi","location_count":48},{"dataset":"Pure Salt - Kiwi","location_count":6},{"dataset":"Twenty Four Seven","location_count":35}]}}}
{"jsonrpc":"2.0","id":6,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"ENUM\",\"name\":\"type\"},{\"database_type\":\"BIGINT\",\"name\":\"count\"}],\"limited\":false,\"query_executed\":\"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000\",\"row_count\":1,\"rows\":[{\"count\":8,\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"ENUM","name":"type"},{"database_type":"BIGINT","name":"count"}],"limited":false,"query_executed":"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000","row_count":1,"rows":[{"count":8,"type":"organise"}]}}}
{"jsonrpc":"2.0","id":7,"result":{"content":[{"type":"text","text":"only SELECT and WITH queries are allowed"}],"isError":true}}
{"jsonrpc":"2.0","id":8,"result":{"content":[{"type":"text","text":"query contains forbidden keywords (INSERT/UPDATE/DELETE/DROP/CREATE/ALTER)"}],"isError":true}}

#!/bin/bash
# Test suite for execute_sql tool
# Tests various SQL queries including safety validation
DB_PATH="${1:-../db/skraak.duckdb}"
{
  # Test 1: Initialize MCP connection
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}'
  sleep 0.2
  # Test 2: Simple SELECT without LIMIT (should auto-append LIMIT 1000)
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name"}}}'
  sleep 0.2
  # Test 3: SELECT with explicit limit parameter (5 rows)
  echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name FROM location WHERE active = true ORDER BY name","limit":5}}}'
  sleep 0.2
  # Test 4: Parameterized query with ? placeholder
  echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true","parameters":["vgIr9JSH_lFj"]}}}'
  sleep 0.2
  # Test 5: Complex JOIN query across multiple tables
  echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name","limit":20}}}'
  sleep 0.2
  # Test 6: Aggregate query with GROUP BY
  echo '{"jsonrpc":"2.0","id":6,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type"}}}'
  sleep 0.2
  # Test 7: INSERT attempt - should FAIL with validation error
  echo '{"jsonrpc":"2.0","id":7,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"INSERT INTO dataset (id, name) VALUES (\"test\", \"test\")"}}}'
  sleep 0.2
  # Test 8: SQL injection attempt with forbidden keywords - should FAIL
  echo '{"jsonrpc":"2.0","id":8,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT * FROM dataset; DROP TABLE dataset;"}}}'
  sleep 0.2
} | ../skraak_mcp "$DB_PATH" 2>/dev/null

#!/bin/bash
# Test script for MCP server resources and prompts
DB_PATH="${1:-../db/skraak.duckdb}"
if [ ! -f "$DB_PATH" ]; then
  echo "Error: Database not found at $DB_PATH" >&2
  echo "Usage: $0 [path-to-database]" >&2
  exit 1
fi
echo "=== Testing MCP Resources and Prompts ===" >&2
echo "Database: $DB_PATH" >&2
echo "" >&2
# Start the server and send test messages
{
  echo "=== 1. Initialize ===" >&2
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test-client","version":"1.0.0"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 2. List Resources ===" >&2
  echo '{"jsonrpc":"2.0","id":2,"method":"resources/list","params":{}}'
  sleep 0.2
  echo "" >&2
  echo "=== 3. List Resource Templates ===" >&2
  echo '{"jsonrpc":"2.0","id":3,"method":"resources/templates/list","params":{}}'
  sleep 0.2
  echo "" >&2
  echo "=== 4. Read Full Schema (first 50 lines only) ===" >&2
  echo '{"jsonrpc":"2.0","id":4,"method":"resources/read","params":{"uri":"schema://full"}}'
  sleep 0.2
  echo "" >&2
  echo "=== 5. Read Dataset Table Schema ===" >&2
  echo '{"jsonrpc":"2.0","id":5,"method":"resources/read","params":{"uri":"schema://table/dataset"}}'
  sleep 0.2
  echo "" >&2
  echo "=== 6. Read Location Table Schema ===" >&2
  echo '{"jsonrpc":"2.0","id":6,"method":"resources/read","params":{"uri":"schema://table/location"}}'
  sleep 0.2
  echo "" >&2
  echo "=== 7. Try Invalid Table (should error) ===" >&2
  echo '{"jsonrpc":"2.0","id":7,"method":"resources/read","params":{"uri":"schema://table/invalid_table"}}'
  sleep 0.2
  echo "" >&2
  echo "=== 8. List Prompts ===" >&2
  echo '{"jsonrpc":"2.0","id":8,"method":"prompts/list","params":{}}'
  sleep 0.2
  echo "" >&2
  echo "=== 9. Get query_active_datasets Prompt ===" >&2
  echo '{"jsonrpc":"2.0","id":9,"method":"prompts/get","params":{"name":"query_active_datasets"}}'
  sleep 0.2
  echo "" >&2
  echo "=== 10. Get explore_database_schema Prompt (focus: dataset) ===" >&2
  echo '{"jsonrpc":"2.0","id":10,"method":"prompts/get","params":{"name":"explore_database_schema","arguments":{"focus_area":"dataset"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 11. Get system_status_check Prompt ===" >&2
  echo '{"jsonrpc":"2.0","id":11,"method":"prompts/get","params":{"name":"system_status_check"}}'
  sleep 0.2
} | ../skraak_mcp "$DB_PATH" 2>/dev/null | jq '.'

{"jsonrpc":"2.0","id":1,"result":{"capabilities":{"logging":{},"prompts":{"listChanged":true},"resources":{"listChanged":true},"tools":{"listChanged":true}},"protocolVersion":"2024-11-05","serverInfo":{"name":"skraak_mcp","version":"v1.0.0"}}}
{"jsonrpc":"2.0","method":"notifications/tools/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/resources/list_changed","params":{}}
{"jsonrpc":"2.0","method":"notifications/prompts/list_changed","params":{}}
{"jsonrpc":"2.0","id":2,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"ENUM\",\"name\":\"type\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000\",\"row_count\":8,\"rows\":[{\"id\":\"wAJk9wuZN15x\",\"name\":\"Bluemine - Kiwi\",\"type\":\"organise\"},{\"id\":\"QZ0tlUrX4Nyi\",\"name\":\"Friends of Cobb - Kiwi\",\"type\":\"organise\"},{\"id\":\"RxajkKXz-w48\",\"name\":\"Lisa Whittle\",\"type\":\"organise\"},{\"id\":\"vgIr9JSH_lFj\",\"name\":\"MOK call site 1\",\"type\":\"organise\"},{\"id\":\"la-JpAf2nLKG\",\"name\":\"Manu o Kahurangi - Kiwi\",\"type\":\"organise\"},{\"id\":\"Yx0oNUDmP5ch\",\"name\":\"Pomona - Kiwi\",\"type\":\"organise\"},{\"id\":\"jWS-sw5RvM-j\",\"name\":\"Pure Salt - Kiwi\",\"type\":\"organise\"},{\"id\":\"gljgxDbfasva\",\"name\":\"Twenty Four Seven\",\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"ENUM","name":"type"}],"limited":false,"query_executed":"SELECT id, name, type FROM dataset WHERE active = true ORDER BY name LIMIT 1000","row_count":8,"rows":[{"id":"wAJk9wuZN15x","name":"Bluemine - Kiwi","type":"organise"},{"id":"QZ0tlUrX4Nyi","name":"Friends of Cobb - Kiwi","type":"organise"},{"id":"RxajkKXz-w48","name":"Lisa Whittle","type":"organise"},{"id":"vgIr9JSH_lFj","name":"MOK call site 1","type":"organise"},{"id":"la-JpAf2nLKG","name":"Manu o Kahurangi - Kiwi","type":"organise"},{"id":"Yx0oNUDmP5ch","name":"Pomona - Kiwi","type":"organise"},{"id":"jWS-sw5RvM-j","name":"Pure Salt - Kiwi","type":"organise"},{"id":"gljgxDbfasva","name":"Twenty Four Seven","type":"organise"}]}}}
{"jsonrpc":"2.0","id":3,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"}],\"limited\":false,\"query_executed\":\"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5\",\"row_count\":5,\"rows\":[{\"id\":\"EwyxfYPFMflt\",\"name\":\"A01\"},{\"id\":\"w5zig0ALH6a5\",\"name\":\"A05\"},{\"id\":\"GouXwoyjeFiq\",\"name\":\"A11\"},{\"id\":\"OS6xbBytkk_I\",\"name\":\"AC21\"},{\"id\":\"tcE-bZ0tcmFB\",\"name\":\"AC34\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"}],"limited":false,"query_executed":"SELECT id, name FROM location WHERE active = true ORDER BY name LIMIT 5","row_count":5,"rows":[{"id":"EwyxfYPFMflt","name":"A01"},{"id":"w5zig0ALH6a5","name":"A05"},{"id":"GouXwoyjeFiq","name":"A11"},{"id":"OS6xbBytkk_I","name":"AC21"},{"id":"tcE-bZ0tcmFB","name":"AC34"}]}}}
{"jsonrpc":"2.0","id":4,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"id\"},{\"database_type\":\"VARCHAR\",\"name\":\"name\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"latitude\"},{\"database_type\":\"DECIMAL(10,7)\",\"name\":\"longitude\"}],\"limited\":false,\"query_executed\":\"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000\",\"row_count\":1,\"rows\":[{\"id\":\"0t9JyiuGID4w\",\"latitude\":\"-40.826344\",\"longitude\":\"172.585079\",\"name\":\"call site 1 1.2 test\"}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"id"},{"database_type":"VARCHAR","name":"name"},{"database_type":"DECIMAL(10,7)","name":"latitude"},{"database_type":"DECIMAL(10,7)","name":"longitude"}],"limited":false,"query_executed":"SELECT id, name, latitude, longitude FROM location WHERE dataset_id = ? AND active = true LIMIT 1000","row_count":1,"rows":[{"id":"0t9JyiuGID4w","latitude":"-40.826344","longitude":"172.585079","name":"call site 1 1.2 test"}]}}}
{"jsonrpc":"2.0","id":5,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"VARCHAR\",\"name\":\"dataset\"},{\"database_type\":\"BIGINT\",\"name\":\"location_count\"}],\"limited\":false,\"query_executed\":\"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20\",\"row_count\":8,\"rows\":[{\"dataset\":\"Bluemine - Kiwi\",\"location_count\":11},{\"dataset\":\"Friends of Cobb - Kiwi\",\"location_count\":0},{\"dataset\":\"Lisa Whittle\",\"location_count\":15},{\"dataset\":\"MOK call site 1\",\"location_count\":1},{\"dataset\":\"Manu o Kahurangi - Kiwi\",\"location_count\":23},{\"dataset\":\"Pomona - Kiwi\",\"location_count\":48},{\"dataset\":\"Pure Salt - Kiwi\",\"location_count\":6},{\"dataset\":\"Twenty Four Seven\",\"location_count\":35}]}"}],"structuredContent":{"columns":[{"database_type":"VARCHAR","name":"dataset"},{"database_type":"BIGINT","name":"location_count"}],"limited":false,"query_executed":"SELECT d.name as dataset, COUNT(l.id) as location_count FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name ORDER BY d.name LIMIT 20","row_count":8,"rows":[{"dataset":"Bluemine - Kiwi","location_count":11},{"dataset":"Friends of Cobb - Kiwi","location_count":0},{"dataset":"Lisa Whittle","location_count":15},{"dataset":"MOK call site 1","location_count":1},{"dataset":"Manu o Kahurangi - Kiwi","location_count":23},{"dataset":"Pomona - Kiwi","location_count":48},{"dataset":"Pure Salt - Kiwi","location_count":6},{"dataset":"Twenty Four Seven","location_count":35}]}}}
{"jsonrpc":"2.0","id":6,"result":{"content":[{"type":"text","text":"{\"columns\":[{\"database_type\":\"ENUM\",\"name\":\"type\"},{\"database_type\":\"BIGINT\",\"name\":\"count\"}],\"limited\":false,\"query_executed\":\"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000\",\"row_count\":1,\"rows\":[{\"count\":8,\"type\":\"organise\"}]}"}],"structuredContent":{"columns":[{"database_type":"ENUM","name":"type"},{"database_type":"BIGINT","name":"count"}],"limited":false,"query_executed":"SELECT type, COUNT(*) as count FROM dataset WHERE active = true GROUP BY type LIMIT 1000","row_count":1,"rows":[{"count":8,"type":"organise"}]}}}
{"jsonrpc":"2.0","id":7,"result":{"content":[{"type":"text","text":"only SELECT and WITH queries are allowed"}],"isError":true}}
{"jsonrpc":"2.0","id":8,"result":{"content":[{"type":"text","text":"query contains forbidden keywords (INSERT/UPDATE/DELETE/DROP/CREATE/ALTER)"}],"isError":true}}

#!/bin/bash
# Test script for import_audio_files tool
# Tests tool registration and basic validation
# Database path - USE TEST DATABASE
DB_PATH="${1:-../db/test.duckdb}"
echo "=== Testing import_audio_files Tool ==="
echo "Database: $DB_PATH"
echo ""
# Test 1: List available tools (should include import_audio_files)
echo "Test 1: List available tools"
echo '{"jsonrpc":"2.0","method":"tools/list","id":1}' | ../skraak_mcp "$DB_PATH" | jq -r '.result.tools[] | select(.name == "import_audio_files") | "✓ Found: \(.name) - \(.description)"'
echo ""
# Test 2: Get tool schema
echo "Test 2: Get import_audio_files tool schema"
echo '{"jsonrpc":"2.0","method":"tools/list","id":2}' | ../skraak_mcp "$DB_PATH" | jq '.result.tools[] | select(.name == "import_audio_files") | .inputSchema.properties'
echo ""
# Test 3: Test validation with invalid folder path
echo "Test 3: Test validation - invalid folder path"
echo '{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "import_audio_files",
    "arguments": {
      "folder_path": "/nonexistent/folder",
      "dataset_id": "test123",
      "location_id": "loc456",
      "cluster_id": "clust789"
    }
  },
  "id": 3
}' | ../skraak_mcp "$DB_PATH" | jq -r '.error.message // "Validation passed (unexpected!)"'
echo ""
# Test 4: Test validation with invalid dataset ID
echo "Test 4: Test validation - invalid dataset_id"
echo '{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "import_audio_files",
    "arguments": {
      "folder_path": "/tmp",
      "dataset_id": "invalidXXXXXX",
      "location_id": "invalidXXXXXX",
      "cluster_id": "invalidXXXXXX"
    }
  },
  "id": 4
}' | ../skraak_mcp "$DB_PATH" | jq -r '.error.message // "No error (unexpected!)"'
echo ""
echo "=== Test Complete ==="
echo ""
echo "Note: For full functional testing with actual WAV files:"
echo "1. Create a test dataset, location, and cluster in the database"
echo "2. Place WAV files in a test folder"
echo "3. Run import with valid IDs and folder path"

#!/bin/bash
# Simple test of import_audio_files tool registration
# Just checks if the server can start and the tool is registered
DB_PATH="${1:-../db/test.duckdb}"
echo "=== Testing import_audio_files Tool Registration ==="
echo "Database: $DB_PATH"
echo ""
# Create a test script that sends proper MCP initialization + tools/list
cat > /tmp/test_import_mcp.txt << 'EOF'
{"jsonrpc":"2.0","method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}},"id":1}
{"jsonrpc":"2.0","method":"tools/list","id":2}
EOF
echo "Sending MCP commands..."
cat /tmp/test_import_mcp.txt | ../skraak_mcp "$DB_PATH" 2>&1 | grep -A 20 '"method":"tools/list"' | jq -r 'select(.result != null) | .result.tools[] | select(.name == "import_audio_files") | "✓ Tool registered: \(.name)\n  Description: \(.description)\n  Required inputs: \(.inputSchema.required | join(", "))"'
echo ""
echo "=== Test Complete ==="
# Cleanup
rm -f /tmp/test_import_mcp.txt

#!/bin/bash
# Test script for all MCP prompts
# Tests all 6 prompts including the new location/cluster/file prompts
DB_PATH="${1:-../db/skraak.duckdb}"
if [ ! -f "$DB_PATH" ]; then
  echo "Error: Database not found at $DB_PATH" >&2
  exit 1
fi
echo "=== Testing All MCP Prompts ===" >&2
echo "Database: $DB_PATH" >&2
echo "" >&2
{
  echo "=== 1. Initialize ===" >&2
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test-client","version":"1.0.0"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 2. List Prompts ===" >&2
  echo '{"jsonrpc":"2.0","id":2,"method":"prompts/list","params":{}}'
  sleep 0.2
  echo "" >&2
  echo "=== 3. Get query_active_datasets ===" >&2
  echo '{"jsonrpc":"2.0","id":3,"method":"prompts/get","params":{"name":"query_active_datasets","arguments":{}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 4. Get explore_database_schema (overview) ===" >&2
  echo '{"jsonrpc":"2.0","id":4,"method":"prompts/get","params":{"name":"explore_database_schema","arguments":{"focus_area":"overview"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 5. Get explore_location_hierarchy (no args) ===" >&2
  echo '{"jsonrpc":"2.0","id":5,"method":"prompts/get","params":{"name":"explore_location_hierarchy","arguments":{}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 6. Get explore_location_hierarchy (with dataset_id) ===" >&2
  echo '{"jsonrpc":"2.0","id":6,"method":"prompts/get","params":{"name":"explore_location_hierarchy","arguments":{"dataset_id":"vgIr9JSH_lFj"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 7. Get query_location_data ===" >&2
  echo '{"jsonrpc":"2.0","id":7,"method":"prompts/get","params":{"name":"query_location_data","arguments":{}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 8. Get analyze_cluster_files (with cluster_id) ===" >&2
  echo '{"jsonrpc":"2.0","id":8,"method":"prompts/get","params":{"name":"analyze_cluster_files","arguments":{"cluster_id":"oNI9jqszP4Bk"}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 9. Get system_status_check ===" >&2
  echo '{"jsonrpc":"2.0","id":9,"method":"prompts/get","params":{"name":"system_status_check","arguments":{}}}'
  sleep 0.2
  echo "" >&2
  echo "=== 10. Test Error Handling (analyze_cluster_files without cluster_id) ===" >&2
  echo '{"jsonrpc":"2.0","id":10,"method":"prompts/get","params":{"name":"analyze_cluster_files","arguments":{}}}'
  sleep 0.2
} | ../skraak_mcp "$DB_PATH" 2>/dev/null

#!/bin/bash
# Simple script to get current time from MCP server
DB_PATH="${1:-../db/skraak.duckdb}"
{
  # Initialize
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"cli-test","version":"1.0.0"}}}'
  sleep 0.2
  # Call get_current_time
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"get_current_time","arguments":{}}}'
  sleep 0.2
} | ../skraak_mcp "$DB_PATH" 2>/dev/null | grep '"id":2' | jq '.result.structuredContent'

# Testing the Skraak MCP Server
## Quick Testing with Shell Scripts
The easiest way to test the server is using the provided shell scripts:
### Comprehensive Test (All Tools)
```bash
./test_mcp.sh [path-to-database]
```
Tests all functionality:
1. Server initialization
2. Tool listing
3. `get_current_time` tool
4. `query_datasets` tool
Default database path: `./db/skraak.duckdb`
### Quick Tool Tests
**Get Current Time:**
```bash
./get_time.sh [path-to-database]
```
**Query Datasets:**
```bash
./query_datasets.sh [path-to-database]
```
Both scripts output clean JSON using `jq`.
## Manual JSON-RPC Testing
You can send messages manually via stdin:
```bash
./skraak_mcp ./db/skraak.duckdb
```
Then type these JSON-RPC messages (one per line):
### 1. Initialize
```json
{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"test","version":"1.0"}}}
```
### 2. List Tools
```json
{"jsonrpc":"2.0","id":2,"method":"tools/list","params":{}}
```
### 3. Call get_current_time
```json
{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"get_current_time","arguments":{}}}
```
### 4. Call query_datasets
```json
{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"query_datasets","arguments":{}}}
```
## Expected Responses
### Initialize Response
```json
{
  "jsonrpc":"2.0",
  "id":1,
  "result":{
    "capabilities":{"logging":{},"tools":{"listChanged":true}},
    "protocolVersion":"2024-11-05",
    "serverInfo":{"name":"skraak_mcp","version":"v1.0.0"}
  }
}
```
### List Tools Response
```json
{
  "jsonrpc":"2.0",
  "id":2,
  "result":{
    "tools":[
      {
        "name":"get_current_time",
        "description":"Get the current system time with timezone information",
        "inputSchema":{"type":"object","additionalProperties":false},
        "outputSchema":{
          "type":"object",
          "required":["time","timezone","unix"],
          "properties":{
            "time":{"type":"string","description":"Current system time in RFC3339 format"},
            "timezone":{"type":"string","description":"System timezone"},
            "unix":{"type":"integer","description":"Unix timestamp in seconds"}
          }
        }
      },
      {
        "name":"query_datasets",
        "description":"Query all datasets from the database. Returns dataset information including ID, name, description, timestamps, active status, and type (organise/test/train).",
        "inputSchema":{"type":"object","additionalProperties":false},
        "outputSchema":{
          "type":"object",
          "required":["datasets","count"],
          "properties":{
            "datasets":{"type":"array","description":"Array of dataset records from the database"},
            "count":{"type":"integer","description":"Total number of datasets returned"}
          }
        }
      }
    ]
  }
}
```
### Get Current Time Response
```json
{
  "jsonrpc":"2.0",
  "id":3,
  "result":{
    "structuredContent":{
      "time":"2026-01-25T16:30:00+13:00",
      "timezone":"Local",
      "unix":1769311800
    }
  }
}
```
### Query Datasets Response
```json
{
  "jsonrpc":"2.0",
  "id":4,
  "result":{
    "structuredContent":{
      "count":10,
      "datasets":[
        {
          "id":"U1khPsIN_r9-",
          "name":"sorted data test",
          "description":null,
          "created_at":"2025-08-26T09:01:04Z",
          "last_modified":"2025-08-26T09:03:05Z",
          "active":false,
          "type":"organise"
        }
      ]
    }
  }
}
```
## Testing with Claude Desktop
Configure the server in Claude Desktop:
1. Edit your MCP config file:
   - **Linux**: `~/.config/Claude/claude_desktop_config.json`
   - **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
   - **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
2. Add this configuration:
```json
{
  "mcpServers": {
    "skraak_mcp": {
      "command": "/home/david/go/src/skraak_mcp/skraak_mcp",
      "args": ["/home/david/go/src/skraak_mcp/db/skraak.duckdb"]
    }
  }
}
```
3. Restart Claude Desktop
4. Test by asking:
   - "What time is it?"
   - "Query all datasets"
   - "List the available datasets"
## Troubleshooting
- **Server immediately exits**: Normal - it waits for stdin input
- **"Usage: ./skraak_mcp <path>"**: You must provide database path argument
- **JSON parsing errors**: Each JSON message must be on a single line
- **No response**: Server outputs to stdout; notifications may appear between responses
- **Tool not found**: Initialize the connection first before calling tools
- **Database connection failed**: Check the database path exists and is readable

package resources
import (
	"context"
	"fmt"
	"os"
	"strings"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
var schemaPath string
// Table names available in the database
var tableNames = []string{
	"dataset",
	"location",
	"cyclic_recording_pattern",
	"cluster",
	"file",
	"moth_metadata",
	"file_metadata",
	"file_dataset",
	"selection",
	"selection_metadata",
	"ebird_taxonomy",
	"species",
	"call_type",
	"filter",
	"label",
	"label_subtype",
	"ebird_taxonomy_v2024",
	"species_dataset",
}
// SetSchemaPath sets the path to the schema.sql file
func SetSchemaPath(path string) {
	schemaPath = path
}
// GetSchemaResources returns the resource definitions for registration
func GetSchemaResources() (*mcp.Resource, *mcp.ResourceTemplate) {
	// Direct resource for full schema
	fullSchemaResource := &mcp.Resource{
		URI:         "schema://full",
		Name:        "Database Schema",
		Description: "Complete SQL schema for the skraak database including all tables, indexes, and types",
		MIMEType:    "application/sql",
	}
	// Template resource for individual tables
	tableTemplate := &mcp.ResourceTemplate{
		URITemplate: "schema://table/{table_name}",
		Name:        "Table Schema",
		Description: "SQL schema for a specific table. Available tables: dataset, location, cyclic_recording_pattern, cluster, file, moth_metadata, file_metadata, file_dataset, selection, selection_metadata, ebird_taxonomy, species, call_type, filter, label, label_subtype, ebird_taxonomy_v2024, species_dataset",
		MIMEType:    "application/sql",
	}
	return fullSchemaResource, tableTemplate
}
// SchemaResourceHandler handles resource read requests for schema
func SchemaResourceHandler(ctx context.Context, req *mcp.ReadResourceRequest) (*mcp.ReadResourceResult, error) {
	uri := req.Params.URI
	// Handle full schema request
	if uri == "schema://full" {
		return readFullSchema()
	}
	// Handle table-specific request
	if strings.HasPrefix(uri, "schema://table/") {
		tableName := strings.TrimPrefix(uri, "schema://table/")
		return readTableSchema(tableName)
	}
	return nil, fmt.Errorf("unknown resource URI: %s", uri)
}
// readFullSchema reads and returns the complete schema file
func readFullSchema() (*mcp.ReadResourceResult, error) {
	if schemaPath == "" {
		return nil, fmt.Errorf("schema path not set")
	}
	content, err := os.ReadFile(schemaPath)
	if err != nil {
		return nil, fmt.Errorf("failed to read schema file: %w", err)
	}
	return &mcp.ReadResourceResult{
		Contents: []*mcp.ResourceContents{
			{
				URI:      "schema://full",
				MIMEType: "application/sql",
				Text:     string(content),
			},
		},
	}, nil
}
// readTableSchema extracts and returns the schema for a specific table
func readTableSchema(tableName string) (*mcp.ReadResourceResult, error) {
	if schemaPath == "" {
		return nil, fmt.Errorf("schema path not set")
	}
	// Validate table name
	if !isValidTableName(tableName) {
		return nil, fmt.Errorf("invalid table name: %s. Valid tables: %s", tableName, strings.Join(tableNames, ", "))
	}
	content, err := os.ReadFile(schemaPath)
	if err != nil {
		return nil, fmt.Errorf("failed to read schema file: %w", err)
	}
	// Extract table definition
	tableDef, err := extractTableDefinition(string(content), tableName)
	if err != nil {
		return nil, err
	}
	return &mcp.ReadResourceResult{
		Contents: []*mcp.ResourceContents{
			{
				URI:      fmt.Sprintf("schema://table/%s", tableName),
				MIMEType: "application/sql",
				Text:     tableDef,
			},
		},
	}, nil
}
// isValidTableName checks if the table name is in the list of valid tables
func isValidTableName(name string) bool {
	for _, validName := range tableNames {
		if name == validName {
			return true
		}
	}
	return false
}
// extractTableDefinition extracts the CREATE TABLE statement for a specific table
// Uses simple line-based parsing to find the table definition
func extractTableDefinition(schema string, tableName string) (string, error) {
	lines := strings.Split(schema, "\n")
	var tableLines []string
	inTable := false
	parenCount := 0
	// Special handling for views (CREATE TABLE ... AS)
	isView := false
	for _, line := range lines {
		// Look for CREATE TABLE or CREATE TYPE statements for this table/type
		if strings.Contains(line, "CREATE TABLE "+tableName) ||
			strings.Contains(line, "CREATE TABLE "+tableName+" AS") ||
			strings.Contains(line, "CREATE TYPE "+tableName) {
			inTable = true
			tableLines = append(tableLines, line)
			// Check if it's a view (CREATE TABLE ... AS)
			if strings.Contains(line, " AS") {
				isView = true
			}
			// Count parentheses
			parenCount += strings.Count(line, "(") - strings.Count(line, ")")
			// For views with AS SELECT, check if statement ends with semicolon
			if isView && strings.HasSuffix(strings.TrimSpace(line), ";") {
				break
			}
			continue
		}
		if inTable {
			tableLines = append(tableLines, line)
			parenCount += strings.Count(line, "(") - strings.Count(line, ")")
			// End of table definition
			if isView {
				// For views, look for semicolon
				if strings.HasSuffix(strings.TrimSpace(line), ";") {
					break
				}
			} else {
				// For regular tables, check for closing parenthesis and semicolon
				if parenCount == 0 && strings.Contains(line, ");") {
					break
				}
			}
		}
	}
	if len(tableLines) == 0 {
		return "", fmt.Errorf("table definition not found: %s", tableName)
	}
	// Also include related indexes and constraints
	tableLines = append(tableLines, "")
	for _, line := range lines {
		trimmed := strings.TrimSpace(line)
		if strings.Contains(trimmed, "CREATE INDEX") && strings.Contains(trimmed, " "+tableName+"(") {
			tableLines = append(tableLines, line)
		}
		if strings.Contains(trimmed, "ALTER TABLE "+tableName) {
			tableLines = append(tableLines, line)
		}
	}
	return strings.Join(tableLines, "\n"), nil
}

package prompts
import (
	"context"
	"fmt"
	"github.com/modelcontextprotocol/go-sdk/mcp"
)
// GetQueryDatasetsPrompt returns the prompt definition for querying active datasets
func GetQueryDatasetsPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "query_active_datasets",
		Description: "Guide for using SQL to query and summarize datasets by type (organise/test/train)",
	}
}
// QueryDatasetsPromptHandler returns prompt messages for the query_active_datasets workflow
func QueryDatasetsPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: `# Query Active Datasets Workflow
This workflow helps you query and summarize datasets using SQL.
## Step 1: Query All Active Datasets
Use the execute_sql tool with this query:
` + "```sql" + `
SELECT id, name, type, active, created_at, last_modified, description
FROM dataset
WHERE active = true
ORDER BY type, name;
` + "```" + `
This returns all active datasets with their metadata.
## Step 2: Count Datasets by Type
To summarize dataset distribution by type:
` + "```sql" + `
SELECT type, COUNT(*) as count
FROM dataset
WHERE active = true
GROUP BY type
ORDER BY type;
` + "```" + `
## Step 3: Find Most Recently Modified
Get the 5 most recently modified datasets:
` + "```sql" + `
SELECT name, type, last_modified
FROM dataset
WHERE active = true
ORDER BY last_modified DESC
LIMIT 5;
` + "```" + `
## Example Analysis Output
After running these queries, present a summary like:
"""
Dataset Summary:
- Total: 8 active datasets
- Organise: 8 datasets
- Test: 0 datasets
- Train: 0 datasets
Most Recently Modified:
1. "Twenty Four Seven" (organise, 2024-06-05)
2. "Pomona - Kiwi" (organise, 2024-06-05)
3. "Pure Salt - Kiwi" (organise, 2024-05-15)
"""
## SQL Tips
- Use WHERE active = true to filter inactive datasets
- GROUP BY type to count by category
- ORDER BY to sort results
- LIMIT to restrict result count`,
				},
			},
		},
	}, nil
}
// GetExploreSchemaPrompt returns the prompt definition for exploring database schema
func GetExploreSchemaPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "explore_database_schema",
		Description: "Interactive guide for exploring the database schema using resources. Optional focus_area argument: overview, dataset, locations, files, labels, or taxonomy",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "focus_area",
				Description: "Area to focus on: overview, dataset, locations, files, labels, or taxonomy",
				Required:    false,
			},
		},
	}
}
// ExploreSchemaPromptHandler returns context-aware prompt messages for schema exploration
func ExploreSchemaPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	focusArea := "overview"
	if req.Params.Arguments != nil {
		if fa, ok := req.Params.Arguments["focus_area"]; ok && fa != "" {
			focusArea = fa
		}
	}
	var promptText string
	switch focusArea {
	case "overview":
		promptText = `# Database Schema Overview
Explore the skraak database schema to understand its structure.
## Step 1: Read Full Schema
Use the schema://full resource to see the complete database structure:
- Resource URI: schema://full
- Returns: Complete SQL schema with all tables
## Step 2: Identify Major Components
The database has these main areas:
1. **Datasets & Organization**: dataset, location, cluster
2. **Audio Files**: file, file_dataset, moth_metadata
3. **Selections & Labels**: selection, label, label_subtype
4. **Taxonomy**: ebird_taxonomy, species, call_type
## Step 3: Explore Relationships
Look for:
- Foreign key relationships between tables
- Junction tables (many-to-many relationships)
- Enum types (dataset_type, gain_level)
## Next Steps
Re-run this prompt with a specific focus_area:
- "dataset" - Dataset and location structure
- "files" - Audio file organization
- "labels" - Labeling and taxonomy system
- "taxonomy" - eBird taxonomy integration`
	case "dataset":
		promptText = `# Dataset & Location Schema
Explore how datasets, locations, and clusters are organized.
## Step 1: Read Dataset Table
Use: schema://table/dataset
Key fields:
- id, name, description
- type: ENUM('organise', 'test', 'train')
- active: BOOLEAN
## Step 2: Read Location Table
Use: schema://table/location
Key fields:
- dataset_id: Links to dataset
- latitude, longitude: Geographic coordinates
- timezone_id: IANA timezone identifier
## Step 3: Read Cluster Table
Use: schema://table/cluster
Represents a collection of files (e.g., one SD card):
- location_id: Where recordings were made
- sample_rate: Audio sample rate
- cyclic_recording_pattern_id: Recording schedule
## Relationships
dataset (1) -> (many) location (1) -> (many) cluster (1) -> (many) file`
	case "locations":
		promptText = `# Location & Cluster Schema
Explore geographic and recording organization.
## Step 1: Location Details
Use: schema://table/location
- Geographic coordinates with validation checks
- Timezone support for accurate timestamps
- Links to parent dataset
## Step 2: Recording Patterns
Use: schema://table/cyclic_recording_pattern
Defines recording schedules:
- record_s: Recording duration in seconds
- sleep_s: Sleep duration between recordings
## Step 3: Cluster Organization
Use: schema://table/cluster
Groups files from one deployment:
- Linked to specific location
- Has recording pattern
- Consistent sample rate
## Use Case
Locations organize multiple recording deployments (clusters) at geographic coordinates.`
	case "files":
		promptText = `# Audio File Schema
Explore how audio files are stored and organized.
## Step 1: File Table Structure
Use: schema://table/file
Core fields:
- file_name, path: File identification
- xxh64_hash: Content hash for deduplication
- timestamp_local: Recording time (timezone-aware)
- duration, sample_rate: Audio properties
- maybe_solar_night, maybe_civil_night: Night detection
- moon_phase: Lunar phase (0.00-1.00)
## Step 2: File-to-Dataset Junction
Use: schema://table/file_dataset
Many-to-many relationship:
- Files can belong to multiple datasets
- Datasets can contain many files
## Step 3: AudioMoth Metadata
Use: schema://table/moth_metadata
Hardware-specific data:
- recorder_id: Device identifier
- gain: Recording gain level (ENUM)
- battery_v, temp_c: Environmental conditions
## Workflow
file (1) -> (1) location
file (many) <-> (many) dataset (via file_dataset junction)
file (1) -> (many) selection -> (many) label`
	case "labels":
		promptText = `# Selection & Label Schema
Explore how audio segments are labeled for species identification.
## Step 1: Selection Table
Use: schema://table/selection
Defines time/frequency regions:
- file_id: Source audio file
- start_time, end_time: Temporal bounds (seconds)
- freq_low, freq_high: Frequency bounds (Hz)
- dataset_id: Context for this selection
## Step 2: Label Table
Use: schema://table/label
Species identification:
- selection_id: The labeled region
- species_id: Identified species
- certainty: Confidence (0-100)
- filter_id: Optional processing filter
## Step 3: Label Subtype (Call Types)
Use: schema://table/label_subtype
Optional call classification:
- label_id: Parent label
- calltype_id: Type of call (from call_type table)
- certainty: Subtype confidence
## Workflow
file (1) -> (many) selection (1) -> (many) label (1) -> (0-many) label_subtype
Labels are specific to a dataset context (selection.dataset_id).`
	case "taxonomy":
		promptText = `# Taxonomy Schema
Explore eBird taxonomy integration and species management.
## Step 1: eBird Taxonomy Table
Use: schema://table/ebird_taxonomy
Immutable reference data:
- species_code: eBird identifier
- taxonomy_version: Year version
- primary_com_name, sci_name: Names
- bird_order, family: Classification
## Step 2: Species Table (Mutable)
Use: schema://table/species
User-managed species list:
- label: Display name
- ebird_code: Links to eBird taxonomy
- Can be customized per project
## Step 3: Call Type Table
Use: schema://table/call_type
Call classifications for each species:
- species_id: Parent species
- label: Call type (e.g., "male", "female", "duet")
## Step 4: Materialized View
Use: schema://table/ebird_taxonomy_v2024
Fast access to 2024 taxonomy:
- Pre-filtered for current version
- Full-text search enabled
- Used for species lookup
## Relationships
ebird_taxonomy (reference) <- species (mutable) (1) -> (many) call_type`
	default:
		return nil, fmt.Errorf("unknown focus_area: %s. Valid options: overview, dataset, locations, files, labels, taxonomy", focusArea)
	}
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: promptText,
				},
			},
		},
	}, nil
}
// GetExploreLocationHierarchyPrompt returns the prompt for exploring the data hierarchy
func GetExploreLocationHierarchyPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "explore_location_hierarchy",
		Description: "Guide for navigating the dataset→location→cluster→file data hierarchy using SQL JOINs. Optional starting point: dataset_id to focus on a specific dataset",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "dataset_id",
				Description: "Optional dataset ID to focus the exploration (e.g., 'vgIr9JSH_lFj')",
				Required:    false,
			},
		},
	}
}
// ExploreLocationHierarchyPromptHandler returns workflow for exploring the data hierarchy
func ExploreLocationHierarchyPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	datasetID := ""
	if req.Params.Arguments != nil {
		if id, ok := req.Params.Arguments["dataset_id"]; ok && id != "" {
			datasetID = id
		}
	}
	var promptText string
	if datasetID != "" {
		promptText = fmt.Sprintf(`# Explore Location Hierarchy (Dataset: %s)
This workflow helps you explore the complete data hierarchy using SQL JOINs.
## Step 1: Get Dataset Information
` + "```sql" + `
SELECT id, name, type, active, created_at, last_modified
FROM dataset
WHERE id = '%s' AND active = true;
` + "```" + `
## Step 2: Get Locations in Dataset
` + "```sql" + `
SELECT id, name, latitude, longitude, timezone_id
FROM location
WHERE dataset_id = '%s' AND active = true
ORDER BY name;
` + "```" + `
Review the geographic distribution and timezone information.
## Step 3: Get Complete Hierarchy with Counts
Use a JOIN query to see the full hierarchy:
` + "```sql" + `
SELECT
    d.name as dataset_name,
    l.name as location_name,
    l.latitude,
    l.longitude,
    COUNT(DISTINCT c.id) as cluster_count,
    COUNT(f.id) as file_count
FROM dataset d
LEFT JOIN location l ON d.id = l.dataset_id
LEFT JOIN cluster c ON l.id = c.location_id
LEFT JOIN file f ON c.id = f.cluster_id
WHERE d.id = '%s' AND d.active = true
GROUP BY d.name, l.name, l.latitude, l.longitude
ORDER BY l.name;
` + "```" + `
This shows how many clusters and files exist at each location.
## Step 4: Examine Files in a Specific Cluster
First, find an interesting cluster from Step 3, then:
` + "```sql" + `
SELECT
    file_name,
    timestamp_local,
    duration,
    maybe_solar_night,
    maybe_civil_night,
    moon_phase
FROM file
WHERE cluster_id = ? AND active = true
ORDER BY timestamp_local
LIMIT 100;
` + "```" + `
Use parameterized query with the cluster_id you want to explore.
## Example Summary Output
"""
Dataset: [name] (type: organise)
├── Locations: 1 active location
│   └── "call site 1 1.2 test" at (-40.826344, 172.585079)
│       ├── Clusters: X recording deployments
│       │   └── Files: Y audio recordings
"""
## Data Hierarchy
dataset (1) → (many) locations → (many) clusters → (many) files
This structure allows:
- Multiple recording locations per dataset
- Multiple recording deployments (clusters) per location
- Multiple audio files per deployment`, datasetID, datasetID, datasetID, datasetID)
	} else {
		promptText = `# Explore Location Hierarchy
This workflow helps you explore the complete data hierarchy using SQL.
## Step 1: Start with Datasets
` + "```sql" + `
SELECT id, name, type, active
FROM dataset
WHERE active = true
ORDER BY type, name;
` + "```" + `
Pick a dataset_id for focused exploration, or re-run this prompt with the dataset_id parameter.
## Step 2: Understand the Hierarchy
The database organizes data in four levels:
1. **Dataset** - A project or collection (e.g., "Summer Survey 2024")
2. **Location** - Geographic recording site with GPS coordinates
3. **Cluster** - A recording deployment (e.g., one SD card's recordings)
4. **File** - Individual audio recording files
## Step 3: Query the Full Hierarchy with JOINs
Get an overview of all datasets with location/cluster/file counts:
` + "```sql" + `
SELECT
    d.name as dataset,
    d.type,
    COUNT(DISTINCT l.id) as location_count,
    COUNT(DISTINCT c.id) as cluster_count,
    COUNT(f.id) as file_count
FROM dataset d
LEFT JOIN location l ON d.id = l.dataset_id
LEFT JOIN cluster c ON l.id = c.location_id
LEFT JOIN file f ON c.id = f.cluster_id
WHERE d.active = true
GROUP BY d.name, d.type
ORDER BY d.name;
` + "```" + `
## Step 4: Filter by Specific Dataset
To explore a specific dataset:
` + "```sql" + `
SELECT
    l.name as location,
    l.latitude,
    l.longitude,
    COUNT(DISTINCT c.id) as clusters,
    COUNT(f.id) as files
FROM location l
LEFT JOIN cluster c ON l.id = c.location_id
LEFT JOIN file f ON c.id = f.cluster_id
WHERE l.dataset_id = ? AND l.active = true
GROUP BY l.name, l.latitude, l.longitude
ORDER BY l.name;
` + "```" + `
Use parameterized query: ` + "```json" + `{"parameters": ["your_dataset_id"]}` + "```" + `
## Use Case Examples
- **Count recordings per location**: Use GROUP BY with COUNT
- **Analyze temporal coverage**: Query file timestamps across clusters
- **Geographic analysis**: Select latitude/longitude with aggregates
- **Quality assessment**: Check sample rates and night detection flags
## SQL Tips
- Use LEFT JOIN to include locations even if they have no clusters
- Use COUNT(DISTINCT) to avoid double-counting
- Use GROUP BY to aggregate data at different levels
- Use parameterized queries (?) for safe filtering`
	}
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: promptText,
				},
			},
		},
	}, nil
}
// GetQueryLocationDataPrompt returns the prompt for querying location data
func GetQueryLocationDataPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "query_location_data",
		Description: "Workflow for finding and analyzing recording locations using SQL queries",
	}
}
// QueryLocationDataPromptHandler returns workflow for querying location data
func QueryLocationDataPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: `# Query Location Data Workflow
This workflow helps you analyze recording locations using SQL.
## Step 1: Get All Locations
` + "```sql" + `
SELECT id, name, latitude, longitude, timezone_id, dataset_id
FROM location
WHERE active = true
ORDER BY name;
` + "```" + `
This returns all active locations with coordinates and timezones.
## Step 2: Analyze Location Distribution by Dataset
Group locations by their parent dataset:
` + "```sql" + `
SELECT
    d.name as dataset,
    COUNT(l.id) as location_count,
    AVG(l.latitude) as avg_latitude,
    AVG(l.longitude) as avg_longitude
FROM dataset d
LEFT JOIN location l ON d.id = l.dataset_id
WHERE d.active = true
GROUP BY d.name
ORDER BY location_count DESC;
` + "```" + `
This shows which datasets have the most recording sites.
## Step 3: Find Locations Within Geographic Bounds
Filter by latitude/longitude ranges:
` + "```sql" + `
SELECT name, latitude, longitude, timezone_id
FROM location
WHERE active = true
  AND latitude BETWEEN -42.0 AND -40.0
  AND longitude BETWEEN 172.0 AND 174.0
ORDER BY latitude, longitude;
` + "```" + `
Adjust the BETWEEN ranges to match your area of interest.
## Step 4: Get Recording Counts by Location
Use JOINs to count clusters and files at each location:
` + "```sql" + `
SELECT
    l.name as location,
    l.latitude,
    l.longitude,
    d.name as dataset,
    COUNT(DISTINCT c.id) as clusters,
    COUNT(f.id) as total_files
FROM location l
LEFT JOIN dataset d ON l.dataset_id = d.id
LEFT JOIN cluster c ON l.id = c.location_id
LEFT JOIN file f ON c.id = f.cluster_id
WHERE l.active = true
GROUP BY l.name, l.latitude, l.longitude, d.name
ORDER BY total_files DESC
LIMIT 20;
` + "```" + `
This shows the 20 most productive recording locations.
## Step 5: Analyze Specific Location
Deep dive on a specific location using parameterized query:
` + "```sql" + `
SELECT
    c.name as cluster,
    c.sample_rate,
    COUNT(f.id) as file_count,
    MIN(f.timestamp_local) as first_recording,
    MAX(f.timestamp_local) as last_recording,
    SUM(f.duration) as total_duration_seconds
FROM cluster c
LEFT JOIN file f ON c.id = f.cluster_id
WHERE c.location_id = ? AND c.active = true
GROUP BY c.name, c.sample_rate
ORDER BY first_recording;
` + "```" + `
Use: ` + "```json" + `{"parameters": ["location_id_here"]}` + "```" + `
## Example Analysis Output
"""
Location Analysis:
Total Locations: 139 active sites
Distribution by Dataset:
- Pomona - Kiwi: 48 locations
- Twenty Four Seven: 35 locations
- Manu o Kahurangi - Kiwi: 23 locations
Top Recording Sites:
1. "Homer Point" - 5 clusters, 12,450 files
2. "Kahurangi Ridge" - 3 clusters, 8,230 files
3. "Cobb Valley" - 4 clusters, 7,890 files
"""
## SQL Tips
- Use LEFT JOIN to include locations even without recordings
- Use COUNT(DISTINCT) to avoid counting duplicates
- Use BETWEEN for geographic bounding boxes
- Use GROUP BY for aggregations at location level
- Use ORDER BY with LIMIT for top-N queries`,
				},
			},
		},
	}, nil
}
// GetAnalyzeClusterFilesPrompt returns the prompt for analyzing cluster files
func GetAnalyzeClusterFilesPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "analyze_cluster_files",
		Description: "Guide for examining audio files within a recording cluster using SQL aggregates. Requires cluster_id parameter",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "cluster_id",
				Description: "Cluster ID to analyze (e.g., 'oNI9jqszP4Bk')",
				Required:    true,
			},
		},
	}
}
// AnalyzeClusterFilesPromptHandler returns workflow for analyzing files in a cluster
func AnalyzeClusterFilesPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	clusterID := ""
	if req.Params.Arguments != nil {
		if id, ok := req.Params.Arguments["cluster_id"]; ok && id != "" {
			clusterID = id
		}
	}
	if clusterID == "" {
		return nil, fmt.Errorf("cluster_id argument is required. Find cluster IDs using SQL: SELECT id, name FROM cluster WHERE active = true")
	}
	promptText := fmt.Sprintf(`# Analyze Cluster Files (Cluster: %s)
This workflow helps you examine audio files using SQL aggregates.
## Step 1: Get All Files in Cluster
` + "```sql" + `
SELECT
    file_name,
    timestamp_local,
    duration,
    sample_rate,
    maybe_solar_night,
    maybe_civil_night,
    moon_phase,
    xxh64_hash
FROM file
WHERE cluster_id = '%s' AND active = true
ORDER BY timestamp_local
LIMIT 100;
` + "```" + `
Review individual file details (first 100 files).
## Step 2: Get Summary Statistics
Use aggregate functions to summarize the cluster:
` + "```sql" + `
SELECT
    COUNT(*) as total_files,
    SUM(duration) as total_duration_seconds,
    AVG(duration) as avg_duration,
    MIN(timestamp_local) as first_recording,
    MAX(timestamp_local) as last_recording,
    SUM(CASE WHEN maybe_solar_night THEN 1 ELSE 0 END) as night_files,
    SUM(CASE WHEN NOT maybe_solar_night THEN 1 ELSE 0 END) as day_files,
    AVG(moon_phase) as avg_moon_phase,
    COUNT(DISTINCT sample_rate) as unique_sample_rates
FROM file
WHERE cluster_id = '%s' AND active = true;
` + "```" + `
This provides an overview of recording coverage and characteristics.
## Step 3: Analyze Temporal Distribution
Group files by hour to see recording pattern:
` + "```sql" + `
SELECT
    DATE_TRUNC('hour', timestamp_local) as recording_hour,
    COUNT(*) as file_count,
    SUM(duration) as total_seconds
FROM file
WHERE cluster_id = '%s' AND active = true
GROUP BY recording_hour
ORDER BY recording_hour
LIMIT 50;
` + "```" + `
This shows when recordings were made throughout the deployment.
## Step 4: Moon Phase Analysis
Analyze distribution across lunar cycle:
` + "```sql" + `
SELECT
    ROUND(moon_phase, 1) as moon_phase_bin,
    COUNT(*) as file_count,
    AVG(moon_phase) as avg_phase
FROM file
WHERE cluster_id = '%s' AND active = true AND moon_phase IS NOT NULL
GROUP BY moon_phase_bin
ORDER BY moon_phase_bin;
` + "```" + `
Shows recording coverage across moon phases (0.0 = new moon, 1.0 = full moon).
## Step 5: Check for Data Quality Issues
Detect duplicates and gaps:
` + "```sql" + `
-- Find duplicate hashes (potential duplicate files)
SELECT xxh64_hash, COUNT(*) as count
FROM file
WHERE cluster_id = '%s' AND active = true
GROUP BY xxh64_hash
HAVING COUNT(*) > 1;
` + "```" + `
` + "```sql" + `
-- Check sample rate consistency
SELECT sample_rate, COUNT(*) as file_count
FROM file
WHERE cluster_id = '%s' AND active = true
GROUP BY sample_rate;
` + "```" + `
## Example Analysis Output
"""
Cluster Analysis: %s
Recording Period:
- Start: 2023-12-10 20:00:00
- End: 2023-12-11 10:00:00
- Duration: 14 hours
Files: 840 recordings
- Night: 650 files (77.4%%)
- Day: 190 files (22.6%%)
Audio Properties:
- Sample Rate: 250kHz (consistent)
- Avg Duration: 60s per file
- Total Audio: 14.0 hours
Moon Phase:
- Range: 0.92-0.95 (near full moon)
- Average: 0.93
Data Quality:
- Unique hashes: 840 (no duplicates)
- Sample rates: 1 (consistent)
"""
## SQL Tips for Analysis
- Use COUNT(*) to count files
- Use SUM(duration) for total recording time
- Use CASE WHEN for conditional counts (night vs day)
- Use DATE_TRUNC to group by time periods
- Use ROUND() to bin continuous values like moon_phase
- Use HAVING with GROUP BY to filter aggregated results
## Next Steps
- Cross-reference with selection/label data for species detections
- Compare temporal patterns across different clusters
- Use file_name and path to locate actual audio files`, clusterID, clusterID, clusterID, clusterID, clusterID, clusterID, clusterID, clusterID)
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: promptText,
				},
			},
		},
	}, nil
}
// GetSystemStatusPrompt returns the prompt definition for system health check
func GetSystemStatusPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "system_status_check",
		Description: "Comprehensive workflow to verify MCP server health using tools, resources, and prompts",
	}
}
// SystemStatusPromptHandler returns prompt messages for the system status check workflow
func SystemStatusPromptHandler(ctx context.Context, req *mcp.GetPromptRequest) (*mcp.GetPromptResult, error) {
	return &mcp.GetPromptResult{
		Messages: []*mcp.PromptMessage{
			{
				Role: "user",
				Content: &mcp.TextContent{
					Text: `# System Status Check Workflow
This workflow verifies all MCP server primitives are functioning correctly.
## Step 1: Verify Tools
Test both available tools:
### 1a. Time Tool
- Call: get_current_time
- Expected: Current system time with timezone
- Validates: Tool execution, time handling
### 1b. Generic SQL Tool
Test with a simple query:
` + "```json" + `
{
  "name": "execute_sql",
  "arguments": {
    "query": "SELECT COUNT(*) as dataset_count FROM dataset WHERE active = true"
  }
}
` + "```" + `
Expected: Row count result
Validates: Database connectivity, SQL execution
### 1c. Parameterized Query Test
` + "```json" + `
{
  "name": "execute_sql",
  "arguments": {
    "query": "SELECT id, name FROM dataset WHERE id = ? AND active = true",
    "parameters": ["vgIr9JSH_lFj"]
  }
}
` + "```" + `
Expected: Filtered dataset result
Validates: Parameterized query support
### 1d. JOIN Query Test
` + "```json" + `
{
  "name": "execute_sql",
  "arguments": {
    "query": "SELECT d.name, COUNT(l.id) as locations FROM dataset d LEFT JOIN location l ON d.id = l.dataset_id WHERE d.active = true GROUP BY d.name",
    "limit": 10
  }
}
` + "```" + `
Expected: Aggregated results with JOINs
Validates: Complex SQL support
### 1e. Security Test (Should Fail)
` + "```json" + `
{
  "name": "execute_sql",
  "arguments": {
    "query": "INSERT INTO dataset (id, name) VALUES ('test', 'test')"
  }
}
` + "```" + `
Expected: Error about forbidden keywords
Validates: Security validation working
## Step 2: Verify Resources
Test schema resources:
### 2a. Full Schema Resource
- Read: schema://full
- Expected: Complete SQL schema (~348 lines)
- Validates: File I/O, resource serving
### 2b. Table Template Resource
- Read: schema://table/dataset
- Expected: Dataset table CREATE statement with indexes
- Validates: Template parsing, SQL extraction
### 2c. Additional Table Templates
Test a few more tables:
- Read: schema://table/location
- Read: schema://table/cluster
- Read: schema://table/file
- Expected: Individual table schemas
- Validates: Template system works for all tables
### 2d. Invalid Resource (Error Handling)
- Read: schema://table/invalid_table
- Expected: Error with list of valid table names
- Validates: Error handling, validation
## Step 3: Verify Prompts
Test all 6 prompt types:
### 3a. Dataset Query Prompt
- Get: query_active_datasets
- Expected: SQL-based workflow guide for dataset querying
- Validates: Basic prompt retrieval
### 3b. Schema Exploration Prompt
- Get: explore_database_schema (with focus_area: "overview")
- Expected: Schema exploration guide
- Validates: Parameterized prompts, context switching
### 3c. Location Hierarchy Prompt
- Get: explore_location_hierarchy
- Expected: SQL JOIN-based hierarchy navigation guide
- Validates: New SQL workflow prompts
### 3d. Location Data Prompt
- Get: query_location_data
- Expected: SQL location analysis workflow
- Validates: Location-focused SQL prompts
### 3e. Cluster Analysis Prompt
- Get: analyze_cluster_files (with cluster_id)
- Expected: SQL aggregate-based file analysis workflow
- Validates: Required parameter prompts
### 3f. System Status Prompt
- Get: system_status_check
- Expected: This current workflow
- Validates: Meta-prompt functionality
## Step 4: Query All Major Tables
Verify database access across all tables:
` + "```sql" + `
-- Datasets
SELECT COUNT(*) as count FROM dataset WHERE active = true;
-- Locations
SELECT COUNT(*) as count FROM location WHERE active = true;
-- Clusters
SELECT COUNT(*) as count FROM cluster WHERE active = true;
-- Files
SELECT COUNT(*) as count FROM file WHERE active = true;
` + "```" + `
All queries should return counts without errors.
## Step 5: Summary Report
Generate a comprehensive status report:
### Health Check Results
"""
✓ Tools: 2/2 operational
  - get_current_time: OK
  - execute_sql: OK
    ✓ Simple queries work
    ✓ Parameterized queries work
    ✓ JOIN queries work
    ✓ Aggregates work
    ✓ Security validation active
✓ Resources: 2 types operational
  - schema://full: OK (348 lines)
  - schema://table/{name}: OK (tested: dataset, location, cluster, file)
✓ Prompts: 6/6 operational
  - query_active_datasets: OK (SQL-based)
  - explore_database_schema: OK
  - explore_location_hierarchy: OK (SQL JOIN-based)
  - query_location_data: OK (SQL-based)
  - analyze_cluster_files: OK (SQL aggregate-based)
  - system_status_check: OK (current)
✓ Database: Read-only mode verified
  - INSERT/UPDATE/DELETE blocked
  - All tables accessible
System Status: HEALTHY
Architecture:
- Generic SQL tool (infinite flexibility)
- Schema resources (context for LLM)
- Workflow prompts (teach SQL patterns)
- Read-only database (security enforced)
"""
## Troubleshooting
If any check fails:
- **Tools**: Check database path and connectivity
- **Resources**: Verify schema.sql file exists and is readable
- **Prompts**: Check prompt handler registration in main.go
- **SQL errors**: Check query syntax and table names
## Next Steps
After verifying system health:
1. Use explore_location_hierarchy to understand data structure
2. Use query_location_data to analyze recording sites
3. Use analyze_cluster_files to examine specific recordings
4. Construct custom SQL queries for your analysis needs`,
				},
			},
		},
	}, nil
}

package main
import (
	"context"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"github.com/modelcontextprotocol/go-sdk/mcp"
	"skraak_mcp/prompts"
	"skraak_mcp/resources"
	"skraak_mcp/tools"
)
// dbPath stores the path to the DuckDB database
var dbPath string
func main() {
	// Parse command line arguments
	// os.Args[0] is the program name
	// os.Args[1] should be the database path
	if len(os.Args) != 2 {
		fmt.Fprintf(os.Stderr, "Usage: %s <path-to-duckdb-database>\n", os.Args[0])
		fmt.Fprintf(os.Stderr, "Example: %s ./data/mydb.duckdb\n", os.Args[0])
		os.Exit(1)
	}
	dbPath = os.Args[1]
	// Set database path for tools package
	tools.SetDBPath(dbPath)
	// Set schema path for resources package
	schemaPath := filepath.Join(filepath.Dir(os.Args[0]), "db", "schema.sql")
	resources.SetSchemaPath(schemaPath)
	// Create MCP server with metadata
	server := mcp.NewServer(&mcp.Implementation{
		Name:    "skraak_mcp",
		Version: "v1.0.0",
	}, nil)
	// Register the get_current_time tool
	mcp.AddTool(server, &mcp.Tool{
		Name:        "get_current_time",
		Description: "Get the current system time with timezone information",
	}, tools.GetCurrentTime)
	// Register the generic SQL query tool
	mcp.AddTool(server, &mcp.Tool{
		Name:        "execute_sql",
		Description: "Execute arbitrary SQL SELECT queries against the database. Supports parameterized queries with ? placeholders. Database is read-only. Results limited to 1000 rows by default (max 10000). Use with schema resources to construct queries.",
	}, tools.ExecuteSQL)
	// Register write tools
	mcp.AddTool(server, &mcp.Tool{
		Name:        "create_dataset",
		Description: "Create a new dataset. Returns the created dataset with generated ID and timestamps.",
	}, tools.CreateDataset)
	mcp.AddTool(server, &mcp.Tool{
		Name:        "create_location",
		Description: "Create a new location within a dataset. Requires valid dataset_id, GPS coordinates, and IANA timezone.",
	}, tools.CreateLocation)
	mcp.AddTool(server, &mcp.Tool{
		Name:        "create_cluster",
		Description: "Create a new cluster within a location. Location must belong to the specified dataset.",
	}, tools.CreateCluster)
	mcp.AddTool(server, &mcp.Tool{
		Name:        "create_cyclic_recording_pattern",
		Description: "Create a reusable recording pattern with record/sleep cycle in seconds.",
	}, tools.CreateCyclicRecordingPattern)
	mcp.AddTool(server, &mcp.Tool{
		Name:        "import_audio_files",
		Description: "Batch import WAV files from a folder into the database. Automatically parses AudioMoth and filename timestamps, calculates hashes, extracts metadata, and computes astronomical data. Files are imported in a single transaction. Duplicate files (by hash) are skipped.",
	}, tools.ImportAudioFiles)
	// Register schema resources
	schemaResource, schemaTemplate := resources.GetSchemaResources()
	server.AddResource(schemaResource, resources.SchemaResourceHandler)
	server.AddResourceTemplate(schemaTemplate, resources.SchemaResourceHandler)
	// Register prompts
	server.AddPrompt(prompts.GetQueryDatasetsPrompt(), prompts.QueryDatasetsPromptHandler)
	server.AddPrompt(prompts.GetExploreSchemaPrompt(), prompts.ExploreSchemaPromptHandler)
	server.AddPrompt(prompts.GetExploreLocationHierarchyPrompt(), prompts.ExploreLocationHierarchyPromptHandler)
	server.AddPrompt(prompts.GetQueryLocationDataPrompt(), prompts.QueryLocationDataPromptHandler)
	server.AddPrompt(prompts.GetAnalyzeClusterFilesPrompt(), prompts.AnalyzeClusterFilesPromptHandler)
	server.AddPrompt(prompts.GetSystemStatusPrompt(), prompts.SystemStatusPromptHandler)
	// Run the server on stdio transport
	if err := server.Run(context.Background(), &mcp.StdioTransport{}); err != nil {
		log.Fatalf("Server error: %v", err)
	}
}