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: "Basic SQL query for datasets",
	}
}

// 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 Datasets

Use execute_sql to query the dataset table:

` + "```sql" + `
SELECT id, name, type, description
FROM dataset
WHERE active = true
ORDER BY name;
` + "```" + `

Use WHERE, GROUP BY, and JOIN as needed for your analysis.`,
				},
			},
		},
	}, nil
}

// GetExploreSchemaPrompt returns the prompt definition for exploring database schema
func GetExploreSchemaPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "explore_database_schema",
		Description: "Guide to database schema resources",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "focus_area",
				Description: "Area to focus on: overview, dataset, 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

Read schema resources to understand the database structure:

- **schema://full** - Complete SQL schema
- **schema://table/{table_name}** - Individual table definitions

Main tables:
- dataset, location, cluster → Organizational hierarchy
- file, moth_metadata → Audio files and metadata
- selection, label, label_subtype → Species identifications
- species, call_type, ebird_taxonomy → Taxonomy

Use the schema to construct SQL queries with execute_sql.`

	case "dataset":
		promptText = `# Dataset Schema

Hierarchy: dataset (1) → (many) location (1) → (many) cluster (1) → (many) file

Key tables:
- **dataset** - Projects (organise/test/train types)
- **location** - Recording sites with GPS coordinates and timezones
- **cluster** - Recording deployments at locations
- **cyclic_recording_pattern** - Recording schedules

Read schema://table/dataset, schema://table/location, schema://table/cluster for details.`

	case "files":
		promptText = `# File Schema

Key tables:
- **file** - Audio recordings with timestamps, duration, sample_rate, night detection, moon_phase
- **file_dataset** - Junction table (files ↔ datasets many-to-many)
- **moth_metadata** - AudioMoth hardware metadata

Read schema://table/file for field details. Use execute_sql to query files.`

	case "labels":
		promptText = `# Label Schema

Workflow: file (1) → (many) selection (1) → (many) label (1) → (0-many) label_subtype

Key tables:
- **selection** - Time/frequency regions in files
- **label** - Species identifications
- **label_subtype** - Call type classifications

Read schema://table/selection, schema://table/label for details.`

	case "taxonomy":
		promptText = `# Taxonomy Schema

Key tables:
- **ebird_taxonomy** - eBird reference data (immutable)
- **species** - User-managed species list (links to eBird)
- **call_type** - Call classifications per species
- **ebird_taxonomy_v2024** - Materialized view for fast lookup

Read schema://table/species, schema://table/call_type for details.`

	default:
		return nil, fmt.Errorf("unknown focus_area: %s. Valid: overview, dataset, 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: "Understanding the dataset→location→cluster→file hierarchy",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "dataset_id",
				Description: "Optional dataset ID to focus on",
				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 Hierarchy (Dataset: %s)

Query the hierarchy with JOINs:

` + "```sql" + `
SELECT
    d.name as dataset,
    l.name as location,
    COUNT(DISTINCT c.id) as clusters,
    COUNT(f.id) as files
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;
` + "```" + `

Hierarchy: dataset → locations → clusters → files`, datasetID, datasetID)
	} else {
		promptText = `# Data Hierarchy

Structure: dataset (1) → (many) location (1) → (many) cluster (1) → (many) file

Example query:

` + "```sql" + `
SELECT
    d.name,
    COUNT(DISTINCT l.id) as locations,
    COUNT(DISTINCT c.id) as clusters,
    COUNT(f.id) as files
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;
` + "```" + `

Use WHERE dataset_id = ? for specific datasets.`
	}

	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: "Query recording locations",
	}
}

// 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 Locations

Basic query:

` + "```sql" + `
SELECT id, name, latitude, longitude, timezone_id, dataset_id
FROM location
WHERE active = true
ORDER BY name;
` + "```" + `

Use JOINs to include cluster/file counts, WHERE for filtering by coordinates or dataset.`,
				},
			},
		},
	}, nil
}

// GetAnalyzeClusterFilesPrompt returns the prompt for analyzing cluster files
func GetAnalyzeClusterFilesPrompt() *mcp.Prompt {
	return &mcp.Prompt{
		Name:        "analyze_cluster_files",
		Description: "Query files in a cluster",
		Arguments: []*mcp.PromptArgument{
			{
				Name:        "cluster_id",
				Description: "Cluster ID to analyze",
				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 required. Find clusters: SELECT id, name FROM cluster WHERE active = true")
	}

	promptText := fmt.Sprintf(`# Analyze Cluster Files (Cluster: %s)

Basic query:

` + "```sql" + `
SELECT
    file_name,
    timestamp_local,
    duration,
    maybe_solar_night,
    moon_phase
FROM file
WHERE cluster_id = '%s' AND active = true
ORDER BY timestamp_local
LIMIT 100;
` + "```" + `

Use aggregate functions (COUNT, SUM, AVG) and GROUP BY for analysis.`, 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: "Verify MCP server health",
	}
}

// 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

## Tools
- **get_current_time** - Current system time
- **execute_sql** - Generic SQL queries (read-only)

Test: SELECT COUNT(*) FROM dataset WHERE active = true

## Resources
- **schema://full** - Complete database schema
- **schema://table/{name}** - Individual table schemas

Test: Read schema://full and schema://table/dataset

## Prompts
All 6 prompts available:
- query_active_datasets
- explore_database_schema
- explore_location_hierarchy
- query_location_data
- analyze_cluster_files
- system_status_check (this prompt)

## Database
Read-only mode enforced. All major tables accessible (dataset, location, cluster, file, selection, label, species).`,
				},
			},
		},
	}, nil
}