// Package examples demonstrates advanced query patterns with cool-mysql
package examples

import (
	"encoding/json"
	"fmt"
	"html/template"
	"log"
	"strings"
	"time"

	mysql "github.com/StirlingMarketingGroup/cool-mysql"
)

// AdvancedQueryExamples demonstrates advanced query patterns
func AdvancedQueryExamples() {
	db, err := setupDatabase()
	if err != nil {
		log.Fatalf("Failed to setup database: %v", err)
	}

	// Template queries
	fmt.Println("=== TEMPLATE QUERY EXAMPLES ===")
	templateExamples(db)

	// Channel streaming
	fmt.Println("\n=== CHANNEL STREAMING EXAMPLES ===")
	channelExamples(db)

	// Function receivers
	fmt.Println("\n=== FUNCTION RECEIVER EXAMPLES ===")
	functionReceiverExamples(db)

	// JSON handling
	fmt.Println("\n=== JSON HANDLING EXAMPLES ===")
	jsonExamples(db)

	// Raw SQL
	fmt.Println("\n=== RAW SQL EXAMPLES ===")
	rawSQLExamples(db)

	// Complex queries
	fmt.Println("\n=== COMPLEX QUERY EXAMPLES ===")
	complexQueryExamples(db)
}

// templateExamples demonstrates Go template syntax in queries
func templateExamples(db *mysql.Database) {
	// Example 1: Conditional WHERE clause
	fmt.Println("1. Conditional WHERE clause")

	type SearchParams struct {
		MinAge int
		Status string
		Name   string
	}

	// Search with all parameters
	params := SearchParams{
		MinAge: 25,
		Status: "active",
		Name:   "Alice",
	}

	query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`" +
		" WHERE 1=1" +
		" {{ if .MinAge }}AND `age` >= @@MinAge{{ end }}" +
		" {{ if .Status }}AND `status` = @@Status{{ end }}" +
		" {{ if .Name }}AND `name` LIKE CONCAT('%', @@Name, '%'){{ end }}"

	var users []User
	err := db.Select(&users, query, 0, params)
	if err != nil {
		log.Printf("Template query failed: %v", err)
	} else {
		fmt.Printf("✓ Found %d users with filters\n", len(users))
	}

	// Example 2: Dynamic ORDER BY (with validation)
	fmt.Println("\n2. Dynamic ORDER BY with whitelisting")

	type SortParams struct {
		SortBy    string
		SortOrder string
	}

	// Whitelist allowed columns - identifiers can't be marshaled
	allowedColumns := map[string]bool{
		"created_at": true,
		"name":       true,
		"age":        true,
	}

	sortParams := SortParams{
		SortBy:    "created_at",
		SortOrder: "DESC",
	}

	// Validate before using in query
	if !allowedColumns[sortParams.SortBy] {
		log.Printf("Invalid sort column: %s", sortParams.SortBy)
		return
	}

	sortQuery := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`" +
		" WHERE `active` = 1" +
		" {{ if .SortBy }}" +
		" ORDER BY {{ .SortBy }} {{ .SortOrder }}" +
		" {{ end }}"

	err = db.Select(&users, sortQuery, 0, sortParams)
	if err != nil {
		log.Printf("Sort query failed: %v", err)
	} else {
		fmt.Printf("✓ Users sorted by %s %s\n", sortParams.SortBy, sortParams.SortOrder)
	}

	// Example 3: Conditional JOINs
	fmt.Println("\n3. Conditional JOINs")

	type JoinParams struct {
		IncludeOrders   bool
		IncludeAddress  bool
		IncludeMetadata bool
	}

	joinParams := JoinParams{
		IncludeOrders:  true,
		IncludeAddress: false,
	}

	joinQuery := "SELECT `users`.`id`, `users`.`name`, `users`.`email`, `users`.`age`, `users`.`active`, `users`.`created_at`, `users`.`updated_at`" +
		" {{ if .IncludeOrders }}, COUNT(`orders`.`id`) as `order_count`{{ end }}" +
		" {{ if .IncludeAddress }}, `addresses`.`city`{{ end }}" +
		" FROM `users`" +
		" {{ if .IncludeOrders }}" +
		" LEFT JOIN `orders` ON `users`.`id` = `orders`.`user_id`" +
		" {{ end }}" +
		" {{ if .IncludeAddress }}" +
		" LEFT JOIN `addresses` ON `users`.`id` = `addresses`.`user_id`" +
		" {{ end }}" +
		" GROUP BY `users`.`id`"

	err = db.Select(&users, joinQuery, 0, joinParams)
	if err != nil {
		log.Printf("Join query failed: %v", err)
	} else {
		fmt.Println("✓ Query with conditional joins executed")
	}

	// Example 4: Custom template functions
	fmt.Println("\n4. Custom template functions")

	// Add custom functions
	db.AddTemplateFuncs(template.FuncMap{
		"upper": strings.ToUpper,
		"lower": strings.ToLower,
		"quote": func(s string) string { return fmt.Sprintf("'%s'", s) },
	})

	type CaseParams struct {
		SearchTerm     string
		CaseSensitive  bool
		UseWildcard    bool
	}

	caseParams := CaseParams{
		SearchTerm:    "alice",
		CaseSensitive: false,
		UseWildcard:   true,
	}

	// IMPORTANT: Template values must be marshaled with | marshal
	caseQuery := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`" +
		" WHERE {{ if not .CaseSensitive }}UPPER(`name`){{ else }}`name`{{ end }} LIKE CONCAT('%', {{ .SearchTerm | marshal }}, '%')"

	err = db.Select(&users, caseQuery, 0, caseParams)
	if err != nil {
		log.Printf("Custom function query failed: %v", err)
	} else {
		fmt.Println("✓ Query with custom template functions executed")
	}

	// Example 5: Complex conditional logic
	fmt.Println("\n5. Complex conditional logic")

	type FilterParams struct {
		AgeRange    []int
		Statuses    []string
		DateFrom    time.Time
		DateTo      time.Time
		ActiveOnly  bool
	}

	filterParams := FilterParams{
		AgeRange:   []int{25, 40},
		ActiveOnly: true,
		DateFrom:   time.Now().Add(-30 * 24 * time.Hour),
	}

	filterQuery := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`" +
		" WHERE 1=1" +
		" {{ if .AgeRange }}" +
		" AND `age` BETWEEN @@AgeMin AND @@AgeMax" +
		" {{ end }}" +
		" {{ if .ActiveOnly }}" +
		" AND `active` = 1" +
		" {{ end }}" +
		" {{ if not .DateFrom.IsZero }}" +
		" AND `created_at` >= @@DateFrom" +
		" {{ end }}" +
		" {{ if not .DateTo.IsZero }}" +
		" AND `created_at` <= @@DateTo" +
		" {{ end }}"

	queryParams := mysql.Params{
		"AgeMin":   filterParams.AgeRange[0],
		"AgeMax":   filterParams.AgeRange[1],
		"DateFrom": filterParams.DateFrom,
	}

	err = db.Select(&users, filterQuery, 0, queryParams, filterParams)
	if err != nil {
		log.Printf("Complex filter query failed: %v", err)
	} else {
		fmt.Printf("✓ Found %d users with complex filters\n", len(users))
	}
}

// channelExamples demonstrates streaming with channels
func channelExamples(db *mysql.Database) {
	// Example 1: Stream SELECT results
	fmt.Println("1. Stream SELECT results to channel")

	userCh := make(chan User, 10) // Buffered channel

	go func() {
		defer close(userCh)
		err := db.Select(userCh, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `active` = 1", 0)
		if err != nil {
			log.Printf("Channel select failed: %v", err)
		}
	}()

	count := 0
	for user := range userCh {
		fmt.Printf("  Received: %s (%s)\n", user.Name, user.Email)
		count++
		if count >= 5 {
			fmt.Println("  (showing first 5 results)")
			// Drain remaining
			for range userCh {
			}
			break
		}
	}

	// Example 2: Stream INSERT from channel
	fmt.Println("\n2. Stream INSERT from channel")

	insertCh := make(chan User, 10)

	go func() {
		defer close(insertCh)
		for i := 0; i < 100; i++ {
			insertCh <- User{
				Name:   fmt.Sprintf("StreamUser%d", i),
				Email:  fmt.Sprintf("stream%d@example.com", i),
				Age:    20 + (i % 50),
				Active: i%2 == 0,
			}
		}
	}()

	err := db.Insert("users", insertCh)
	if err != nil {
		log.Printf("Channel insert failed: %v", err)
	} else {
		fmt.Println("✓ Streamed 100 users for insertion")
	}

	// Example 3: Transform while streaming
	fmt.Println("\n3. Transform data while streaming")

	type EnrichedUser struct {
		User
		Category string
		Priority int
	}

	rawUserCh := make(chan User, 10)
	enrichedCh := make(chan EnrichedUser, 10)

	// Producer: fetch users
	go func() {
		defer close(rawUserCh)
		db.Select(rawUserCh, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` LIMIT @@limit", 0, 50)
	}()

	// Transformer: enrich data
	go func() {
		defer close(enrichedCh)
		for user := range rawUserCh {
			enriched := EnrichedUser{
				User: user,
			}

			// Add category based on age
			if user.Age < 25 {
				enriched.Category = "Young"
				enriched.Priority = 1
			} else if user.Age < 40 {
				enriched.Category = "Middle"
				enriched.Priority = 2
			} else {
				enriched.Category = "Senior"
				enriched.Priority = 3
			}

			enrichedCh <- enriched
		}
	}()

	// Consumer: process enriched data
	processed := 0
	for enriched := range enrichedCh {
		processed++
		_ = enriched // Process enriched user
	}

	fmt.Printf("✓ Processed %d enriched users\n", processed)
}

// functionReceiverExamples demonstrates function receivers
func functionReceiverExamples(db *mysql.Database) {
	// Example 1: Process each row with function
	fmt.Println("1. Process rows with function")

	count := 0
	err := db.Select(func(u User) {
		count++
		if count <= 3 {
			fmt.Printf("  Processing: %s (Age: %d)\n", u.Name, u.Age)
		}
	}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `active` = 1", 0)

	if err != nil {
		log.Printf("Function receiver failed: %v", err)
	} else {
		fmt.Printf("✓ Processed %d users\n", count)
	}

	// Example 2: Aggregate data with function
	fmt.Println("\n2. Aggregate data with function")

	var totalAge int
	var userCount int

	err = db.Select(func(u User) {
		totalAge += u.Age
		userCount++
	}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)

	if err != nil {
		log.Printf("Aggregation failed: %v", err)
	} else if userCount > 0 {
		avgAge := float64(totalAge) / float64(userCount)
		fmt.Printf("✓ Average age: %.2f (%d users)\n", avgAge, userCount)
	}

	// Example 3: Conditional processing
	fmt.Println("\n3. Conditional processing with function")

	type Stats struct {
		YoungCount  int
		MiddleCount int
		SeniorCount int
	}

	stats := Stats{}

	err = db.Select(func(u User) {
		switch {
		case u.Age < 25:
			stats.YoungCount++
		case u.Age < 40:
			stats.MiddleCount++
		default:
			stats.SeniorCount++
		}
	}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)

	if err != nil {
		log.Printf("Stats failed: %v", err)
	} else {
		fmt.Printf("✓ Age distribution: Young=%d, Middle=%d, Senior=%d\n",
			stats.YoungCount, stats.MiddleCount, stats.SeniorCount)
	}

	// Example 4: Early termination pattern
	fmt.Println("\n4. Early termination with function")

	found := false
	targetEmail := "alice@example.com"

	err = db.Select(func(u User) {
		if u.Email == targetEmail {
			found = true
			fmt.Printf("✓ Found user: %s\n", u.Name)
			// Note: Can't actually stop iteration early
			// This is a limitation of function receivers
		}
	}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)

	if err != nil {
		log.Printf("Search failed: %v", err)
	} else if !found {
		fmt.Println("✗ User not found")
	}
}

// jsonExamples demonstrates JSON handling
func jsonExamples(db *mysql.Database) {
	// Example 1: Store JSON in struct field
	fmt.Println("1. Store JSON column in struct")

	type UserWithMeta struct {
		ID       int             `mysql:"id"`
		Name     string          `mysql:"name"`
		Email    string          `mysql:"email"`
		Metadata json.RawMessage `mysql:"metadata"` // JSON column
	}

	userMeta := UserWithMeta{
		Name:  "JSONUser",
		Email: "json@example.com",
		Metadata: json.RawMessage(`{
			"theme": "dark",
			"language": "en",
			"notifications": true
		}`),
	}

	err := db.Insert("users", userMeta)
	if err != nil {
		log.Printf("JSON insert failed: %v", err)
	} else {
		fmt.Println("✓ User with JSON metadata inserted")
	}

	// Example 2: Select JSON as RawMessage
	fmt.Println("\n2. Select JSON column")

	var usersWithMeta []UserWithMeta
	err = db.Select(&usersWithMeta,
		"SELECT `id`, `name`, `email`, metadata FROM `users` WHERE metadata IS NOT NULL LIMIT @@limit",
		0,
		5)

	if err != nil {
		log.Printf("JSON select failed: %v", err)
	} else {
		fmt.Printf("✓ Retrieved %d users with metadata\n", len(usersWithMeta))
		for _, u := range usersWithMeta {
			fmt.Printf("  %s: %s\n", u.Name, string(u.Metadata))
		}
	}

	// Example 3: SelectJSON for JSON result
	fmt.Println("\n3. SelectJSON for JSON object")

	var jsonResult json.RawMessage
	err = db.SelectJSON(&jsonResult,
		"SELECT JSON_OBJECT("+
		" 'id', `id`,"+
		" 'name', `name`,"+
		" 'email', `email`,"+
		" 'age', `age`"+
		" ) FROM `users` WHERE `id` = @@id",
		0,
		1)

	if err != nil {
		log.Printf("SelectJSON failed: %v", err)
	} else {
		fmt.Printf("✓ JSON result: %s\n", string(jsonResult))
	}

	// Example 4: SelectJSON for JSON array
	fmt.Println("\n4. SelectJSON for JSON array")

	var jsonArray json.RawMessage
	err = db.SelectJSON(&jsonArray,
		"SELECT JSON_ARRAYAGG("+
		" JSON_OBJECT("+
		" 'name', `name`,"+
		" 'email', `email`"+
		" )"+
		" ) FROM `users` WHERE `active` = 1 LIMIT @@limit",
		0,
		5)

	if err != nil {
		log.Printf("SelectJSON array failed: %v", err)
	} else {
		fmt.Printf("✓ JSON array result: %s\n", string(jsonArray))
	}
}

// rawSQLExamples demonstrates using mysql.Raw for literal SQL
func rawSQLExamples(db *mysql.Database) {
	// Example 1: Raw SQL in WHERE clause
	fmt.Println("1. Raw SQL for complex condition")

	var users []User
	err := db.Select(&users,
		"SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE @@condition",
		0,
		mysql.Raw("created_at > NOW() - INTERVAL 7 DAY"))

	if err != nil {
		log.Printf("Raw SQL query failed: %v", err)
	} else {
		fmt.Printf("✓ Found %d users from last 7 days\n", len(users))
	}

	// Example 2: Raw SQL for CASE statement
	fmt.Println("\n2. Raw SQL for CASE statement")

	type UserWithLabel struct {
		Name  string `mysql:"name"`
		Label string `mysql:"label"`
	}

	caseSQL := mysql.Raw(`
		CASE
			WHEN age < 25 THEN 'Young'
			WHEN age < 40 THEN 'Middle'
			ELSE 'Senior'
		END
	`)

	var labeled []UserWithLabel
	err = db.Select(&labeled,
		"SELECT name, @@ageCase as `label` FROM `users`",
		0,
		caseSQL)

	if err != nil {
		log.Printf("CASE query failed: %v", err)
	} else {
		fmt.Printf("✓ Retrieved %d users with age labels\n", len(labeled))
		for i, u := range labeled {
			if i < 3 {
				fmt.Printf("  %s: %s\n", u.Name, u.Label)
			}
		}
	}

	// Example 3: Raw SQL for subquery
	fmt.Println("\n3. Raw SQL for subquery")

	subquery := mysql.Raw("(SELECT AVG(age) FROM `users` WHERE `active` = 1)")

	err = db.Select(&users,
		"SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE age > @@avgAge",
		0,
		subquery)

	if err != nil {
		log.Printf("Subquery failed: %v", err)
	} else {
		fmt.Printf("✓ Found %d users above average age\n", len(users))
	}

	// Example 4: WARNING - Never use Raw with user input!
	fmt.Println("\n4. WARNING: Raw SQL security example")

	// DANGEROUS - SQL injection risk!
	// userInput := "'; DROP TABLE users; --"
	// db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = @@name", 0,
	//     mysql.Params{"name": mysql.Raw(userInput)})

	// SAFE - use regular parameter
	safeInput := "Alice'; DROP TABLE users; --"
	err = db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = @@name", 0, safeInput) // Properly escaped

	if err != nil {
		log.Printf("Safe query failed: %v", err)
	} else {
		fmt.Println("✓ User input safely escaped (no SQL injection)")
	}
}

// complexQueryExamples demonstrates complex query patterns
func complexQueryExamples(db *mysql.Database) {
	// Example 1: Subquery with named parameters
	fmt.Println("1. Subquery with parameters")

	type UserWithOrderCount struct {
		User
		OrderCount int `mysql:"order_count"`
	}

	var usersWithOrders []UserWithOrderCount
	err := db.Select(&usersWithOrders,
		"SELECT `users`.`id`, `users`.`name`, `users`.`email`, `users`.`age`, `users`.`active`, `users`.`created_at`, `users`.`updated_at`,"+
		" (SELECT COUNT(*) FROM `orders` WHERE `orders`.`user_id` = `users`.`id`) as `order_count`"+
		" FROM `users`"+
		" WHERE `users`.`created_at` > @@since"+
		" AND `users`.`active` = @@active",
		5*time.Minute,
		mysql.Params{
			"since":  time.Now().Add(-30 * 24 * time.Hour),
			"active": true,
		})

	if err != nil {
		log.Printf("Subquery failed: %v", err)
	} else {
		fmt.Printf("✓ Retrieved %d active users with order counts\n", len(usersWithOrders))
	}

	// Example 2: JOIN with aggregation
	fmt.Println("\n2. JOIN with aggregation")

	query := "SELECT" +
		" `users`.`id`," +
		" `users`.`name`," +
		" `users`.`email`," +
		" COUNT(`orders`.`id`) as `order_count`," +
		" SUM(`orders`.`total`) as `total_spent`" +
		" FROM `users`" +
		" LEFT JOIN `orders` ON `users`.`id` = `orders`.`user_id`" +
		" WHERE `users`.`active` = @@active" +
		" GROUP BY `users`.`id`, `users`.`name`, `users`.`email`" +
		" HAVING COUNT(`orders`.`id`) > @@minOrders" +
		" ORDER BY total_spent DESC" +
		" LIMIT @@limit"

	type UserStats struct {
		ID         int     `mysql:"id"`
		Name       string  `mysql:"name"`
		Email      string  `mysql:"email"`
		OrderCount int     `mysql:"order_count"`
		TotalSpent float64 `mysql:"total_spent"`
	}

	var stats []UserStats
	err = db.Select(&stats, query, 10*time.Minute,
		mysql.Params{
			"active":    true,
			"minOrders": 5,
			"limit":     10,
		})

	if err != nil {
		log.Printf("Aggregation query failed: %v", err)
	} else {
		fmt.Printf("✓ Top %d spenders retrieved\n", len(stats))
	}

	// Example 3: Window function
	fmt.Println("\n3. Window function query")

	windowQuery := "SELECT" +
		" `id`," +
		" `name`," +
		" `age`," +
		" RANK() OVER (ORDER BY `age` DESC) as `age_rank`," +
		" AVG(`age`) OVER () as `avg_age`" +
		" FROM `users`" +
		" WHERE `active` = @@active" +
		" LIMIT @@limit"

	type UserWithRank struct {
		ID      int     `mysql:"id"`
		Name    string  `mysql:"name"`
		Age     int     `mysql:"age"`
		AgeRank int     `mysql:"age_rank"`
		AvgAge  float64 `mysql:"avg_age"`
	}

	var ranked []UserWithRank
	err = db.Select(&ranked, windowQuery, 5*time.Minute,
		mysql.Params{
			"active": true,
			"limit":  20,
		})

	if err != nil {
		log.Printf("Window function query failed: %v", err)
	} else {
		fmt.Printf("✓ Retrieved %d users with age ranking\n", len(ranked))
	}

	// Example 4: CTE (Common Table Expression)
	fmt.Println("\n4. CTE query")

	cteQuery := "WITH recent_users AS (" +
		" SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`" +
		" WHERE `created_at` > @@since" +
		" )," +
		" active_recent AS (" +
		" SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM recent_users" +
		" WHERE `active` = @@active" +
		" )" +
		" SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM active_recent" +
		" ORDER BY `created_at` DESC" +
		" LIMIT @@limit"

	var cteUsers []User
	err = db.Select(&cteUsers, cteQuery, 5*time.Minute,
		mysql.Params{
			"since":  time.Now().Add(-7 * 24 * time.Hour),
			"active": true,
			"limit":  10,
		})

	if err != nil {
		log.Printf("CTE query failed: %v", err)
	} else {
		fmt.Printf("✓ Retrieved %d recent active users via CTE\n", len(cteUsers))
	}
}